253 lines
9.4 KiB
Python
253 lines
9.4 KiB
Python
"""
|
||
为 gateway 系统初始化数据库：系统、菜单、角色、角色菜单绑定、用户绑定、放行接口
|
||
"""
|
||
import pymysql
|
||
|
||
conn = pymysql.connect(
|
||
host='db1.prod.zgitm.com', port=3306,
|
||
user='mokeegateway', password='mokee.',
|
||
database='mokeegateway', charset='utf8mb4'
|
||
)
|
||
cur = conn.cursor()
|
||
|
||
sc = 'gateway'
|
||
|
||
# ========== 1.1 插入 gateway 系统 ==========
|
||
# 使用 INSERT ... ON DUPLICATE KEY UPDATE 防止重复
|
||
cur.execute("""
|
||
INSERT INTO sys_system (system_code, system_name, front_url, backend_url, icon, sort_order, status, description, doc_token)
|
||
VALUES ('gateway', '网关管理平台', 'http://127.0.0.1:5001', 'http://127.0.0.1:9000', '', 0, 1, '统一登录网关管理后台', 'gateway1234567890')
|
||
ON DUPLICATE KEY UPDATE
|
||
system_name = VALUES(system_name),
|
||
front_url = VALUES(front_url),
|
||
backend_url = VALUES(backend_url),
|
||
icon = VALUES(icon),
|
||
sort_order = VALUES(sort_order),
|
||
status = VALUES(status),
|
||
description = VALUES(description),
|
||
doc_token = VALUES(doc_token)
|
||
""")
|
||
conn.commit()
|
||
print("[1.1] gateway 系统已创建/更新")
|
||
|
||
# 获取 gateway 系统 ID
|
||
cur.execute("SELECT id FROM sys_system WHERE system_code = %s", (sc,))
|
||
system_id = cur.fetchone()[0]
|
||
print(f" gateway system_id = {system_id}")
|
||
|
||
|
||
# ========== 1.2 创建菜单 ==========
|
||
# 先删除 gateway 系统已有的菜单（重新创建）
|
||
cur.execute("DELETE FROM sys_role_menu WHERE menu_id IN (SELECT id FROM sys_menu WHERE system_id = %s)", (system_id,))
|
||
cur.execute("DELETE FROM sys_menu WHERE system_id = %s", (system_id,))
|
||
conn.commit()
|
||
|
||
# 1.2.1 顶级菜单（目录）
|
||
top_menus = [
|
||
('首页', '/admin/dashboard', '', 'HomeFilled', 1, 1, 0), # parent_id=0, 先插再用子查询
|
||
('系统管理', '/admin/system', '', 'Monitor', 1, 2, 0),
|
||
('用户权限', None, '', 'User', 1, 3, 0),
|
||
('日志审计', None, '', 'Notebook', 1, 4, 0),
|
||
]
|
||
for menu_name, menu_path, component, icon, menu_type, sort_order, parent_name in top_menus:
|
||
# 对于 parent_name=0，直接传当前菜单名（后续用子查询）
|
||
cur.execute("""
|
||
INSERT INTO sys_menu (system_id, parent_id, menu_name, menu_path, component, icon, menu_type, sort_order, status)
|
||
SELECT %s, 0, %s, %s, %s, %s, %s, %s, 1
|
||
""", (system_id, menu_name, menu_path, component, icon, menu_type, sort_order))
|
||
conn.commit()
|
||
|
||
# 获取父级菜单 ID
|
||
def get_menu_id(menu_name):
|
||
cur.execute(
|
||
"SELECT id FROM sys_menu WHERE menu_name = %s AND system_id = %s ORDER BY id LIMIT 1",
|
||
(menu_name, system_id)
|
||
)
|
||
row = cur.fetchone()
|
||
return row[0] if row else None
|
||
|
||
sys_pid = get_menu_id('系统管理')
|
||
user_pid = get_menu_id('用户权限')
|
||
log_pid = get_menu_id('日志审计')
|
||
|
||
# 子菜单
|
||
sub_menus = [
|
||
# 系统管理子菜单
|
||
('系统列表', '/admin/system', '', 'List', 2, 1, sys_pid),
|
||
('接口管理', '/admin/api', '', 'Link', 2, 2, sys_pid),
|
||
('数据字典', '/admin/dict', '', 'Collection', 2, 3, sys_pid),
|
||
# 用户权限子菜单
|
||
('用户管理', '/admin/user', '', 'UserFilled', 2, 1, user_pid),
|
||
('角色管理', '/admin/role', '', 'Avatar', 2, 2, user_pid),
|
||
('菜单管理', '/admin/menu', '', 'Menu', 2, 3, user_pid),
|
||
# 日志审计子菜单
|
||
('登录日志', '/admin/log/login', '', 'Document', 2, 1, log_pid),
|
||
('操作日志', '/admin/log/operation', '', 'Tickets', 2, 2, log_pid),
|
||
('Token日志', '/admin/log/token', '', 'Key', 2, 3, log_pid),
|
||
]
|
||
for menu_name, menu_path, component, icon, menu_type, sort_order, parent_id in sub_menus:
|
||
cur.execute("""
|
||
INSERT INTO sys_menu (system_id, parent_id, menu_name, menu_path, component, icon, menu_type, sort_order, status)
|
||
SELECT %s, %s, %s, %s, %s, %s, %s, %s, 1
|
||
""", (system_id, parent_id, menu_name, menu_path, component, icon, menu_type, sort_order))
|
||
conn.commit()
|
||
print("[1.2] 菜单已创建")
|
||
|
||
# 验证菜单数量
|
||
cur.execute("SELECT COUNT(*) FROM sys_menu WHERE system_id = %s", (system_id,))
|
||
menu_count = cur.fetchone()[0]
|
||
print(f" 菜单数量: {menu_count}")
|
||
|
||
|
||
# ========== 1.3 创建角色 ==========
|
||
cur.execute("DELETE FROM sys_user_role WHERE role_id IN (SELECT id FROM sys_role WHERE system_id = %s)", (system_id,))
|
||
cur.execute("DELETE FROM sys_role_menu WHERE role_id IN (SELECT id FROM sys_role WHERE system_id = %s)", (system_id,))
|
||
cur.execute("DELETE FROM sys_role WHERE system_id = %s", (system_id,))
|
||
conn.commit()
|
||
|
||
cur.execute("""
|
||
INSERT INTO sys_role (system_id, role_name, role_code, description, status)
|
||
VALUES (%s, '超级管理员', 'super_admin', '网关平台超级管理员，拥有全部权限', 1)
|
||
""", (system_id,))
|
||
cur.execute("""
|
||
INSERT INTO sys_role (system_id, role_name, role_code, description, status)
|
||
VALUES (%s, '普通管理员', 'admin', '网管平台普通管理员', 1)
|
||
""", (system_id,))
|
||
conn.commit()
|
||
print("[1.3] 角色已创建")
|
||
|
||
# 验证角色数量
|
||
cur.execute("SELECT COUNT(*) FROM sys_role WHERE system_id = %s", (system_id,))
|
||
role_count = cur.fetchone()[0]
|
||
print(f" 角色数量: {role_count}")
|
||
|
||
# 获取角色 ID
|
||
def get_role_id(role_code):
|
||
cur.execute(
|
||
"SELECT id FROM sys_role WHERE role_code = %s AND system_id = %s",
|
||
(role_code, system_id)
|
||
)
|
||
row = cur.fetchone()
|
||
return row[0] if row else None
|
||
|
||
super_admin_id = get_role_id('super_admin')
|
||
admin_id = get_role_id('admin')
|
||
|
||
|
||
# ========== 1.4 角色菜单绑定 ==========
|
||
# super_admin 绑定所有菜单
|
||
cur.execute("""
|
||
INSERT INTO sys_role_menu (role_id, menu_id)
|
||
SELECT %s, m.id FROM sys_menu m WHERE m.system_id = %s
|
||
""", (super_admin_id, system_id))
|
||
conn.commit()
|
||
|
||
# admin 也绑定所有菜单（简化）
|
||
cur.execute("""
|
||
INSERT INTO sys_role_menu (role_id, menu_id)
|
||
SELECT %s, m.id FROM sys_menu m WHERE m.system_id = %s
|
||
""", (admin_id, system_id))
|
||
conn.commit()
|
||
print("[1.4] 角色菜单已绑定")
|
||
|
||
# 验证绑定数
|
||
cur.execute("SELECT COUNT(*) FROM sys_role_menu WHERE role_id IN (%s, %s)", (super_admin_id, admin_id))
|
||
binding_count = cur.fetchone()[0]
|
||
print(f" 角色菜单绑定数: {binding_count}")
|
||
|
||
|
||
# ========== 1.5 绑定 admin 用户到 gateway 系统 + 分配 super_admin 角色 ==========
|
||
# 获取 admin 用户 ID
|
||
cur.execute("SELECT id FROM sys_user WHERE username = 'admin'")
|
||
admin_user = cur.fetchone()
|
||
if admin_user:
|
||
admin_user_id = admin_user[0]
|
||
|
||
# 绑定用户到 gateway 系统
|
||
cur.execute("""
|
||
INSERT IGNORE INTO sys_user_system (user_id, system_id)
|
||
VALUES (%s, %s)
|
||
""", (admin_user_id, system_id))
|
||
conn.commit()
|
||
|
||
# 给 admin 用户分配 super_admin 角色
|
||
cur.execute("""
|
||
INSERT IGNORE INTO sys_user_role (user_id, role_id)
|
||
VALUES (%s, %s)
|
||
""", (admin_user_id, super_admin_id))
|
||
conn.commit()
|
||
print(f"[1.5] admin 用户(user_id={admin_user_id})已绑定到 gateway 系统 + super_admin 角色")
|
||
else:
|
||
print("[1.5] 警告：未找到 admin 用户！")
|
||
|
||
|
||
# ========== 1.6 创建 gateway 系统的放行接口 ==========
|
||
cur.execute("DELETE FROM sys_api WHERE system_id = %s", (system_id,))
|
||
conn.commit()
|
||
|
||
gateway_apis = [
|
||
# admin 接口（全部 HTTP 方法）
|
||
('/zgapi/v1/admin/**', 'GET', '管理接口-GET'),
|
||
('/zgapi/v1/admin/**', 'POST', '管理接口-POST'),
|
||
('/zgapi/v1/admin/**', 'PUT', '管理接口-PUT'),
|
||
('/zgapi/v1/admin/**', 'DELETE', '管理接口-DELETE'),
|
||
# auth 接口
|
||
('/zgapi/v1/auth/**', 'GET', '认证接口-GET'),
|
||
('/zgapi/v1/auth/**', 'POST', '认证接口-POST'),
|
||
('/zgapi/v1/auth/**', 'PUT', '认证接口-PUT'),
|
||
]
|
||
for api_path, api_method, api_name in gateway_apis:
|
||
cur.execute("""
|
||
INSERT INTO sys_api (system_id, api_path, api_method, api_name, status)
|
||
VALUES (%s, %s, %s, %s, 1)
|
||
""", (system_id, api_path, api_method, api_name))
|
||
conn.commit()
|
||
print("[1.6] gateway 放行接口已创建")
|
||
|
||
|
||
# ========== 验证 ==========
|
||
print("\n" + "=" * 60)
|
||
print(" 验 证 结 果")
|
||
print("=" * 60)
|
||
|
||
cur.execute("SELECT COUNT(*) FROM sys_menu WHERE system_id = %s", (system_id,))
|
||
print(f" 菜单数量: {cur.fetchone()[0]}")
|
||
|
||
cur.execute("SELECT COUNT(*) FROM sys_role WHERE system_id = %s", (system_id,))
|
||
print(f" 角色数量: {cur.fetchone()[0]}")
|
||
|
||
cur.execute("SELECT COUNT(*) FROM sys_role_menu rm JOIN sys_role r ON rm.role_id = r.id WHERE r.system_id = %s", (system_id,))
|
||
print(f" 角色菜单绑定数: {cur.fetchone()[0]}")
|
||
|
||
cur.execute("SELECT COUNT(*) FROM sys_user_system WHERE system_id = %s", (system_id,))
|
||
print(f" admin用户绑定系统数: {cur.fetchone()[0]}")
|
||
|
||
cur.execute("SELECT COUNT(*) FROM sys_api WHERE system_id = %s", (system_id,))
|
||
print(f" 放行接口数: {cur.fetchone()[0]}")
|
||
|
||
# 列出菜单
|
||
print("\n--- 菜单列表 ---")
|
||
cur.execute(
|
||
"SELECT id, parent_id, menu_name, menu_path, menu_type, sort_order FROM sys_menu WHERE system_id = %s ORDER BY sort_order, id",
|
||
(system_id,)
|
||
)
|
||
for r in cur.fetchall():
|
||
print(f" id={r[0]} parent={r[1]} {r[2]:8s} path={r[3]:25s} type={r[4]} sort={r[5]}")
|
||
|
||
print("\n--- 角色列表 ---")
|
||
cur.execute("SELECT id, role_name, role_code FROM sys_role WHERE system_id = %s", (system_id,))
|
||
for r in cur.fetchall():
|
||
print(f" id={r[0]} {r[1]} ({r[2]})")
|
||
|
||
print("\n--- 放行接口列表 ---")
|
||
cur.execute("SELECT api_path, api_method, api_name FROM sys_api WHERE system_id = %s", (system_id,))
|
||
for r in cur.fetchall():
|
||
print(f" {r[0]:30s} {r[1]:8s} {r[2]}")
|
||
|
||
print("\n" + "=" * 60)
|
||
print("初始化完成！")
|
||
print("=" * 60)
|
||
|
||
cur.close()
|
||
conn.close()
|