# -*- coding: utf-8 -*- """Regenerate restore SQL: only current (status=0) image per mes_icons id.""" from __future__ import print_function from collections import Counter, defaultdict import os import sys sys.path.insert(0, r"D:\IdeaProjects\mes-ahdy\mescloud\_apk_work") from extract_mes_icons_files import ( # noqa: E402 DUMP, OUT_DIR, find_offsets, iter_value_rows, parse_create_columns, read_until_stmt_end, row_to_dict, sql_quote, unquote, ) def pick_latest(lst): return sorted( lst, key=lambda x: ( x.get("update_date") or "", x.get("create_date") or "", x.get("id") or "", ), reverse=True, )[0] def main(): hits = find_offsets(DUMP, ["mes_icons", "js_sys_file_upload", "js_sys_file_entity"]) create_cols = {} inserts = {} for off, name in hits: if name.startswith("CREATE:"): t = name.split(":", 1)[1] stmt = read_until_stmt_end(DUMP, off, max_bytes=200 * 1024) create_cols[t] = parse_create_columns(stmt) else: inserts[name] = off icons_cols = create_cols["mes_icons"] upload_cols = create_cols["js_sys_file_upload"] entity_cols = create_cols["js_sys_file_entity"] icons = [row_to_dict(f, icons_cols) for f in iter_value_rows(DUMP, inserts["mes_icons"])] icon_by_id = {i["id"]: i for i in icons} biz_type_idx = upload_cols.index("biz_type") icon_uploads = [] for fields in iter_value_rows(DUMP, inserts["js_sys_file_upload"]): if unquote(fields[biz_type_idx]) == "mesIcons_image": icon_uploads.append(row_to_dict(fields, upload_cols)) print("upload status:", Counter(u.get("status") for u in icon_uploads)) by_biz = defaultdict(list) for u in icon_uploads: if (u.get("status") or "0") == "0": by_biz[u.get("biz_key")].append(u) current = {} for k, lst in by_biz.items(): if k: current[k] = pick_latest(lst) print( "modules with active image=%d, multi-active=%d" % (len(current), sum(1 for v in by_biz.values() if len(v) > 1)) ) needed = set(u["file_id"] for u in current.values() if u.get("file_id")) file_id_idx = entity_cols.index("file_id") entities = {} for fields in iter_value_rows(DUMP, inserts["js_sys_file_entity"]): fid = unquote(fields[file_id_idx]) if fid in needed: entities[fid] = row_to_dict(fields, entity_cols) if len(entities) == len(needed): break print("entities=%d" % len(entities)) def disk_rel(e): if not e: return "" path = (e.get("file_path") or "").replace("\\", "/") md5 = e.get("file_md5") or "" ext = e.get("file_extension") or "" return ("%s%s.%s" % (path, md5, ext)) if ext else ("%s%s" % (path, md5)) map2 = os.path.join(OUT_DIR, "mes_icons_image_map_current_only.txt") with open(map2, "w", encoding="utf-8") as wf: wf.write( "icon_id\ttitle\tmode\ticon_status\tupload_id\tfile_id\tfile_name\t" "file_path\tfile_md5\text\tdisk_rel\tupdate_date\n" ) for biz_key in sorted(current.keys()): u = current[biz_key] e = entities.get(u.get("file_id") or {}, {}) ic = icon_by_id.get(biz_key, {}) wf.write( "\t".join( [ biz_key, ic.get("title") or "", ic.get("mode") or "", ic.get("status") or "", u.get("id") or "", u.get("file_id") or "", u.get("file_name") or "", (e.get("file_path") or "").replace("\\", "/"), e.get("file_md5") or "", e.get("file_extension") or "", disk_rel(e), u.get("update_date") or "", ] ) + "\n" ) print("Wrote", map2) sql_path = os.path.join(OUT_DIR, "restore_mes_icons_image_mapping.sql") with open(sql_path, "w", encoding="utf-8") as wf: wf.write("-- ============================================================\n") wf.write("-- Restore PDA功能模块图片映射（仅当前有效图 status=0，每模块取最新一条）\n") wf.write("-- 来源: mes_cloud_20260919.sql\n") wf.write("-- 关联: biz_key=mes_icons.id, biz_type=mesIcons_image\n") wf.write("-- 务必同步拷贝磁盘文件到新服务器 {baseDir}/userfiles/\n") wf.write("-- ============================================================\n") wf.write("SET NAMES utf8mb4;\nSTART TRANSACTION;\n\n") wf.write("-- 1) 文件实体\n") for fid in sorted(needed): e = entities[fid] size = e.get("file_size") size_sql = size if size is not None else "NULL" wf.write( "INSERT INTO `js_sys_file_entity` " "(`file_id`,`file_md5`,`file_path`,`file_content_type`,`file_extension`," "`file_size`,`file_meta`,`file_preview`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s) " "ON DUPLICATE KEY UPDATE " "`file_md5`=VALUES(`file_md5`),`file_path`=VALUES(`file_path`)," "`file_content_type`=VALUES(`file_content_type`)," "`file_extension`=VALUES(`file_extension`),`file_size`=VALUES(`file_size`)," "`file_meta`=VALUES(`file_meta`),`file_preview`=VALUES(`file_preview`);\n" % ( sql_quote(e.get("file_id")), sql_quote(e.get("file_md5")), sql_quote(e.get("file_path")), sql_quote(e.get("file_content_type")), sql_quote(e.get("file_extension")), size_sql, sql_quote(e.get("file_meta")), sql_quote(e.get("file_preview")), ) ) wf.write("\n-- 2) 按模块 id 替换绑定\n") for biz_key in sorted(current.keys()): u = current[biz_key] ic = icon_by_id.get(biz_key, {}) e = entities.get(u.get("file_id") or {}, {}) wf.write( "\n-- id=%s title=%s mode=%s disk=%s\n" % (biz_key, ic.get("title") or "", ic.get("mode") or "", disk_rel(e)) ) wf.write( "DELETE FROM `js_sys_file_upload` " "WHERE `biz_type`='mesIcons_image' AND `biz_key`=%s;\n" % sql_quote(biz_key) ) sortv = u.get("file_sort") sort_sql = sortv if sortv is not None else "NULL" wf.write( "INSERT INTO `js_sys_file_upload` " "(`id`,`file_id`,`file_name`,`file_type`,`file_sort`,`biz_key`,`biz_type`," "`status`,`create_by`,`create_date`,`update_by`,`update_date`,`remarks`) " "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);\n" % ( sql_quote(u.get("id")), sql_quote(u.get("file_id")), sql_quote(u.get("file_name")), sql_quote(u.get("file_type")), sort_sql, sql_quote(u.get("biz_key")), sql_quote(u.get("biz_type")), sql_quote(u.get("status") or "0"), sql_quote(u.get("create_by") or "system"), sql_quote(u.get("create_date")), sql_quote(u.get("update_by") or "system"), sql_quote(u.get("update_date")), sql_quote(u.get("remarks")), ) ) missing = [ i for i in icons if i.get("id") not in current and (i.get("status") or "0") == "0" ] if missing: wf.write("\n-- 老库正常模块但无有效图片:\n") for i in missing: wf.write( "-- id=%s title=%s mode=%s\n" % (i.get("id"), i.get("title"), i.get("mode")) ) wf.write("\nCOMMIT;\n") wf.write( "\n-- 校验:\n" "-- SELECT u.biz_key, i.title, i.mode, u.file_name, e.file_path, e.file_md5, e.file_extension\n" "-- FROM js_sys_file_upload u\n" "-- LEFT JOIN js_sys_file_entity e ON e.file_id=u.file_id\n" "-- LEFT JOIN mes_icons i ON i.id=u.biz_key\n" "-- WHERE u.biz_type='mesIcons_image' AND u.status='0'\n" "-- ORDER BY i.title;\n" ) print("Wrote", sql_path) files_list = os.path.join(OUT_DIR, "mes_icons_image_files_to_copy.txt") seen = set() with open(files_list, "w", encoding="utf-8") as wf: wf.write("# 拷到新服务器 {file.baseDir}/userfiles/ 下，保持相对路径\n") for u in current.values(): rel = disk_rel(entities.get(u.get("file_id") or {})) if not rel or rel in seen: continue seen.add(rel) wf.write(rel + "\n") print("files=%d Wrote %s" % (len(seen), files_list)) print("--- preview (first 15) ---") with open(map2, encoding="utf-8") as rf: for i, line in enumerate(rf): if i <= 15: print(line.rstrip()) else: break print("DONE") if __name__ == "__main__": main()