mescloud/_apk_work/extract_mes_icons_files.py

# -*- coding: utf-8 -*-
"""Extract mes_icons image mappings from large mysqldump; generate restore SQL."""
from __future__ import print_function

import os
import re
import sys

DUMP = r"d:\产线\大洋\mes_cloud_20260919.sql\mes_cloud_20260919.sql"
OUT_DIR = r"D:\IdeaProjects\mes-ahdy\mescloud\_apk_work\extracted"
os.makedirs(OUT_DIR, exist_ok=True)

CHUNK = 16 * 1024 * 1024


def find_offsets(path, names):
    needles = [(("INSERT INTO `%s`" % n).encode("ascii"), n) for n in names]
    needles += [(("CREATE TABLE `%s`" % n).encode("ascii"), "CREATE:" + n) for n in names]
    keep = max(len(n) for n, _ in needles)
    buf = b""
    pos = 0
    found = []
    with open(path, "rb") as f:
        while True:
            chunk = f.read(CHUNK)
            if not chunk:
                break
            data = buf + chunk
            base = pos - len(buf)
            for needle, name in needles:
                start = 0
                while True:
                    i = data.find(needle, start)
                    if i < 0:
                        break
                    found.append((base + i, name))
                    start = i + len(needle)
            buf = data[-keep:]
            pos += len(chunk)
            if pos % (512 * 1024 * 1024) < CHUNK:
                print("scanned %d MB, hits=%d" % (pos // (1024 * 1024), len(found)), flush=True)
    seen = set()
    out = []
    for p, name in sorted(found):
        if p in seen:
            continue
        seen.add(p)
        out.append((p, name))
    return out


def read_until_stmt_end(path, offset, max_bytes=500 * 1024 * 1024):
    """Read SQL statement starting at offset until top-level ');' end (string-aware)."""
    with open(path, "rb") as f:
        f.seek(offset)
        parts = []
        total = 0
        in_str = False
        prev = None
        # We need to detect end: ) then ; outside string
        # Process incrementally with a small rolling window of decoded... keep as bytes, track quote state for '
        # MySQL dump uses \' escapes inside strings.
        while total < max_bytes:
            chunk = f.read(4 * 1024 * 1024)
            if not chunk:
                break
            parts.append(chunk)
            total += len(chunk)
            data = b"".join(parts)
            i = 0
            # Re-scan whole each time is O(n^2) — better keep state across chunks
            # Restart parse from 0 each time is bad for GB. Use stateful scan on new chunk only.
            break
        # Stateful:
    with open(path, "rb") as f:
        f.seek(offset)
        out = bytearray()
        in_str = False
        total = 0
        while total < max_bytes:
            chunk = f.read(1024 * 1024)
            if not chunk:
                break
            i = 0
            while i < len(chunk):
                c = chunk[i]
                out.append(c)
                total += 1
                if in_str:
                    if c == 0x5C and i + 1 < len(chunk):  # backslash
                        out.append(chunk[i + 1])
                        i += 2
                        total += 1
                        continue
                    if c == 0x5C and i + 1 >= len(chunk):
                        # escape at boundary: pull next byte
                        nxt = f.read(1)
                        if nxt:
                            out.append(nxt[0])
                            total += 1
                        i += 1
                        continue
                    if c == 0x27:  # '
                        in_str = False
                    i += 1
                    continue
                else:
                    if c == 0x27:
                        in_str = True
                        i += 1
                        continue
                    # end of statement: );
                    if c == 0x3B and len(out) >= 2 and out[-2] == 0x29:  # );
                        return out.decode("utf-8", "replace")
                    i += 1
        return out.decode("utf-8", "replace")


def parse_create_columns(stmt):
    m = re.search(r"CREATE TABLE `[^`]+`\s*\((.*)\)\s*ENGINE", stmt, re.S | re.I)
    if not m:
        return []
    cols = []
    for line in m.group(1).split("\n"):
        cm = re.match(r"\s*`([^`]+)`\s+", line)
        if cm:
            cols.append(cm.group(1))
    return cols


def iter_value_rows(path, insert_offset):
    """
    Yield list-of-field-strings for each VALUES row of an INSERT statement.
    Does not load whole statement into memory.
    """
    with open(path, "rb") as f:
        f.seek(insert_offset)
        # skip to VALUES
        buf = b""
        while True:
            chunk = f.read(1024 * 1024)
            if not chunk:
                return
            buf += chunk
            m = re.search(br"\bVALUES\b", buf, re.I)
            if m:
                # position after VALUES
                start_in_buf = m.end()
                # leftover after VALUES
                leftover = buf[start_in_buf:]
                break
            # keep tail
            if len(buf) > 64:
                buf = buf[-64:]

        # Now stream leftover + rest of file for rows
        def byte_iter(first):
            for b in first:
                yield b
            while True:
                ch = f.read(1024 * 1024)
                if not ch:
                    break
                for b in ch:
                    yield b

        it = byte_iter(leftover)
        # parse rows
        try:
            while True:
                # skip whitespace and commas
                c = next(it)
                while c in (0x20, 0x09, 0x0A, 0x0D, 0x2C):
                    c = next(it)
                if c == 0x3B:  # ;
                    return
                if c != 0x28:  # (
                    # unexpected
                    return
                fields = []
                cur = bytearray()
                in_str = False
                while True:
                    c = next(it)
                    if in_str:
                        if c == 0x5C:
                            cur.append(c)
                            cur.append(next(it))
                            continue
                        cur.append(c)
                        if c == 0x27:
                            in_str = False
                        continue
                    if c == 0x27:
                        in_str = True
                        cur.append(c)
                        continue
                    if c == 0x2C:  # ,
                        fields.append(cur.decode("utf-8", "replace").strip())
                        cur = bytearray()
                        continue
                    if c == 0x29:  # )
                        fields.append(cur.decode("utf-8", "replace").strip())
                        yield fields
                        break
                    cur.append(c)
        except StopIteration:
            return


def unquote(s):
    if s is None:
        return None
    s = s.strip()
    if s.upper() == "NULL":
        return None
    if len(s) >= 2 and s[0] == "'" and s[-1] == "'":
        inner = s[1:-1]
        inner = (
            inner.replace("\\\\", "\\")
            .replace("\\'", "'")
            .replace('\\"', '"')
            .replace("\\n", "\n")
            .replace("\\r", "\r")
            .replace("\\t", "\t")
            .replace("\\0", "\0")
            .replace("\\Z", "\x1a")
        )
        return inner
    return s


def sql_quote(s):
    if s is None:
        return "NULL"
    return "'" + str(s).replace("\\", "\\\\").replace("'", "''") + "'"


def row_to_dict(fields, cols):
    d = {}
    for i, v in enumerate(fields):
        key = cols[i] if i < len(cols) else "col%d" % i
        d[key] = unquote(v)
    return d


def main():
    size_gb = os.path.getsize(DUMP) / (1024.0 ** 3)
    print("Dump size: %.2f GB" % size_gb, flush=True)
    names = ["mes_icons", "js_sys_file_upload", "js_sys_file_entity"]
    hits = find_offsets(DUMP, names)
    for off, name in hits:
        print("  %s @ %d" % (name, off), flush=True)

    create_cols = {}
    inserts = {}
    for off, name in hits:
        if name.startswith("CREATE:"):
            t = name.split(":", 1)[1]
            print("Reading CREATE %s..." % t, flush=True)
            stmt = read_until_stmt_end(DUMP, off, max_bytes=200 * 1024)
            create_cols[t] = parse_create_columns(stmt)
            print("  cols=%d: %s" % (len(create_cols[t]), create_cols[t][:12]), flush=True)
        else:
            inserts[name] = off

    icons_cols = create_cols.get("mes_icons") or []
    upload_cols = create_cols.get("js_sys_file_upload") or []
    entity_cols = create_cols.get("js_sys_file_entity") or []

    # 1) Load mes_icons (small)
    print("Loading mes_icons...", flush=True)
    icons = []
    for fields in iter_value_rows(DUMP, inserts["mes_icons"]):
        icons.append(row_to_dict(fields, icons_cols))
    print("  mes_icons rows=%d" % len(icons), flush=True)

    # 2) Stream file_upload, keep only mesIcons_image
    print("Scanning js_sys_file_upload for mesIcons_image...", flush=True)
    biz_type_idx = upload_cols.index("biz_type") if "biz_type" in upload_cols else None
    icon_uploads = []
    n = 0
    for fields in iter_value_rows(DUMP, inserts["js_sys_file_upload"]):
        n += 1
        if n % 200000 == 0:
            print("  scanned upload rows=%d kept=%d" % (n, len(icon_uploads)), flush=True)
        if biz_type_idx is not None:
            bt = unquote(fields[biz_type_idx] if biz_type_idx < len(fields) else None)
            if bt != "mesIcons_image":
                continue
        else:
            # fallback: any field equals mesIcons_image
            if not any(unquote(x) == "mesIcons_image" for x in fields):
                continue
        icon_uploads.append(row_to_dict(fields, upload_cols))
    print("  upload total scanned=%d, mesIcons_image=%d" % (n, len(icon_uploads)), flush=True)

    needed_ids = set(u["file_id"] for u in icon_uploads if u.get("file_id"))
    print("  needed file_entity ids=%d" % len(needed_ids), flush=True)

    # 3) Stream file_entity, keep needed
    print("Scanning js_sys_file_entity for needed ids...", flush=True)
    file_id_idx = entity_cols.index("file_id") if "file_id" in entity_cols else 0
    entities = {}
    n = 0
    for fields in iter_value_rows(DUMP, inserts["js_sys_file_entity"]):
        n += 1
        if n % 200000 == 0:
            print("  scanned entity rows=%d kept=%d" % (n, len(entities)), flush=True)
        fid = unquote(fields[file_id_idx] if file_id_idx < len(fields) else None)
        if fid in needed_ids:
            entities[fid] = row_to_dict(fields, entity_cols)
            if len(entities) == len(needed_ids):
                print("  all needed entities found early at row %d" % n, flush=True)
                break
    print("  entity scanned=%d kept=%d / needed=%d" % (n, len(entities), len(needed_ids)), flush=True)

    icon_by_id = {i["id"]: i for i in icons}

    # Mapping TSV
    map_path = os.path.join(OUT_DIR, "mes_icons_image_map_from_old.txt")
    with open(map_path, "w", encoding="utf-8") as wf:
        wf.write("icon_id\ttitle\tmode\tstatus\tupload_id\tfile_id\tfile_name\tfile_path\tfile_md5\text\tdisk_rel\n")
        for u in sorted(icon_uploads, key=lambda x: (x.get("biz_key") or "", x.get("id") or "")):
            e = entities.get(u.get("file_id") or "", {})
            ic = icon_by_id.get(u.get("biz_key") or "", {})
            path = (e.get("file_path") or "").replace("\\", "/")
            md5 = e.get("file_md5") or ""
            ext = e.get("file_extension") or ""
            disk = ("%s%s.%s" % (path, md5, ext)) if ext else ("%s%s" % (path, md5))
            wf.write(
                "\t".join(
                    [
                        u.get("biz_key") or "",
                        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 "",
                        path,
                        md5,
                        ext,
                        disk,
                    ]
                )
                + "\n"
            )
    print("Wrote", map_path, flush=True)

    # Restore SQL
    sql_path = os.path.join(OUT_DIR, "restore_mes_icons_image_mapping.sql")
    by_biz = {}
    for u in icon_uploads:
        by_biz.setdefault(u.get("biz_key"), []).append(u)

    with open(sql_path, "w", encoding="utf-8") as wf:
        wf.write("-- ============================================================\n")
        wf.write("-- Restore PDA功能模块(mes_icons) 图片映射，来源: mes_cloud_20260919.sql\n")
        wf.write("-- 关联: js_sys_file_upload.biz_key = mes_icons.id\n")
        wf.write("--       js_sys_file_upload.biz_type = 'mesIcons_image'\n")
        wf.write("--       js_sys_file_upload.file_id  = js_sys_file_entity.file_id\n")
        wf.write("-- 注意: 还须把磁盘文件拷到新服务器 userfiles 下（见同目录 files_to_copy 列表）\n")
        wf.write("-- ============================================================\n")
        wf.write("SET NAMES utf8mb4;\nSTART TRANSACTION;\n\n")

        wf.write("-- 1) 写入/覆盖老库中的文件实体\n")
        for fid in sorted(needed_ids):
            e = entities.get(fid)
            if not e:
                wf.write("-- WARN missing entity file_id=%s\n" % fid)
                continue
            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(k for k in by_biz if k):
            ic = icon_by_id.get(biz_key, {})
            wf.write(
                "\n-- id=%s title=%s mode=%s\n"
                % (biz_key, ic.get("title") or "", ic.get("mode") or "")
            )
            wf.write(
                "DELETE FROM `js_sys_file_upload` WHERE `biz_type`='mesIcons_image' AND `biz_key`=%s;\n"
                % sql_quote(biz_key)
            )
            for u in by_biz[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 by_biz 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'\n"
            "-- ORDER BY i.title;\n"
        )
    print("Wrote", sql_path, flush=True)

    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")
        wf.write("# 实际文件名一般为: {file_path}{file_md5}.{file_extension}\n")
        for u in icon_uploads:
            e = entities.get(u.get("file_id") or "")
            if not e:
                continue
            path = (e.get("file_path") or "").replace("\\", "/")
            md5 = e.get("file_md5") or ""
            ext = e.get("file_extension") or ""
            rel = ("%s%s.%s" % (path, md5, ext)) if ext else ("%s%s" % (path, md5))
            if rel in seen:
                continue
            seen.add(rel)
            wf.write(rel + "\n")
    print("Wrote %s (%d unique files)" % (files_list, len(seen)), flush=True)
    print("DONE", flush=True)


if __name__ == "__main__":
    main()