ylrz_saas_his_scrm/sql/run_organize_menu.py

# -*- coding: utf-8 -*-
import pymysql

DB = dict(
    host='cq-cdb-8fjmemkb.sql.tencentcdb.com',
    port=27220,
    user='root',
    password='Ylrz_1q2w3e4r5t6y',
    database='ylrz_saas',
    charset='utf8mb4',
    autocommit=False,
)

FILES = [
    r'f:\project\Saas\ylrz_saas_his_scrm\sql\organize_tenant_sys_menu.sql',
    r'f:\project\Saas\ylrz_saas_his_scrm\sql\organize_tenant_sys_menu_subtree.sql',
]

RESTORE_SQL = """
INSERT INTO tenant_sys_menu
SELECT b.*
FROM tenant_sys_menu_bak b
LEFT JOIN tenant_sys_menu t ON t.menu_id = b.menu_id
WHERE t.menu_id IS NULL
"""


def load_statements(path):
    for enc in ('utf-8', 'utf-8-sig', 'gbk'):
        try:
            with open(path, 'r', encoding=enc) as f:
                content = f.read()
            break
        except UnicodeDecodeError:
            content = None
    if content is None:
        with open(path, 'r', encoding='utf-8', errors='ignore') as f:
            content = f.read()
    lines = []
    for line in content.splitlines():
        if line.strip().startswith('--'):
            continue
        lines.append(line)
    return [p.strip() for p in '\n'.join(lines).split(';') if p.strip()]


def main():
    conn = pymysql.connect(**DB)
    cur = conn.cursor()
    try:
        cur.execute('SELECT COUNT(*) FROM tenant_sys_menu')
        before = cur.fetchone()[0]
        cur.execute(RESTORE_SQL)
        restored = cur.rowcount
        conn.commit()
        cur.execute('SELECT COUNT(*) FROM tenant_sys_menu')
        after_restore = cur.fetchone()[0]
        print('restore: before=%s restored=%s after=%s' % (before, restored, after_restore))

        for fp in FILES:
            stmts = load_statements(fp)
            affected = 0
            for stmt in stmts:
                cur.execute(stmt)
                affected += cur.rowcount
            conn.commit()
            print('FILE %s: statements=%s rowcount_sum=%s' % (fp.split('\\')[-1], len(stmts), affected))

        checks = [
            ('total', 'SELECT COUNT(*) FROM tenant_sys_menu'),
            ('visible_roots', "SELECT COUNT(*) FROM tenant_sys_menu WHERE parent_id=0 AND visible='0' AND menu_type='M'"),
            ('bad_admin_visible', "SELECT COUNT(*) FROM tenant_sys_menu WHERE component LIKE 'admin/%' AND visible='0'"),
            ('sys_35100_children', "SELECT COUNT(*) FROM tenant_sys_menu WHERE parent_id=35100 AND visible='0' AND menu_type<>'F'"),
            ('sys_35101_children', "SELECT COUNT(*) FROM tenant_sys_menu WHERE parent_id=35101 AND visible='0' AND menu_type<>'F'"),
            ('sys_35106_children', "SELECT COUNT(*) FROM tenant_sys_menu WHERE parent_id=35106 AND visible='0' AND menu_type<>'F'"),
            ('qw_35001_children', "SELECT COUNT(*) FROM tenant_sys_menu WHERE parent_id=35001 AND visible='0' AND menu_type<>'F'"),
            ('qw_35002_children', "SELECT COUNT(*) FROM tenant_sys_menu WHERE parent_id=35002 AND visible='0' AND menu_type<>'F'"),
            ('store_35040_children', "SELECT COUNT(*) FROM tenant_sys_menu WHERE parent_id=35040 AND visible='0' AND menu_type<>'F'"),
            ('hidden_hisstore', "SELECT COUNT(*) FROM tenant_sys_menu WHERE menu_id BETWEEN 32591 AND 32643 AND visible='1'"),
            ('qw_path_dup', """
                SELECT COUNT(*) FROM (
                  SELECT parent_id, path FROM tenant_sys_menu
                  WHERE parent_id IN (35001,35002,35003,35004,35005,35006,35007)
                    AND menu_type <> 'F' AND visible='0'
                  GROUP BY parent_id, path HAVING COUNT(*)>1
                ) t
            """),
            ('store_path_dup', """
                SELECT COUNT(*) FROM (
                  SELECT parent_id, path FROM tenant_sys_menu
                  WHERE parent_id IN (35040,35041,35042)
                    AND menu_type <> 'F' AND visible='0'
                  GROUP BY parent_id, path HAVING COUNT(*)>1
                ) t
            """),
        ]
        print('VERIFY:')
        for name, sql in checks:
            cur.execute(sql)
            print('  %s=%s' % (name, cur.fetchone()[0]))

        print('EXEC_OK')
    except Exception as e:
        conn.rollback()
        print('EXEC_FAIL: %s' % e)
        raise
    finally:
        cur.close()
        conn.close()


if __name__ == '__main__':
    main()