267 lines
12 KiB
Python
267 lines
12 KiB
Python
"""
|
||
统一登录网关平台 — 数据库初始化脚本
|
||
连接 MySQL db1.prod.zgitm.com 创建 11 张表
|
||
"""
|
||
import pymysql
|
||
|
||
# ===== 数据库连接配置 =====
|
||
DB_CONFIG = {
|
||
'host': 'db1.prod.zgitm.com',
|
||
'port': 3306,
|
||
'user': 'mokeegateway',
|
||
'password': 'mokee.',
|
||
'database': 'mokeegateway',
|
||
'charset': 'utf8mb4',
|
||
}
|
||
|
||
# ===== 建表 DDL =====
|
||
DDL_STATEMENTS = [
|
||
# 1. 业务系统表
|
||
"""
|
||
CREATE TABLE IF NOT EXISTS sys_system (
|
||
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
system_code VARCHAR(64) NOT NULL COMMENT '系统编码，如 msg、oa',
|
||
system_name VARCHAR(128) NOT NULL COMMENT '系统名称',
|
||
front_url VARCHAR(256) NOT NULL COMMENT '系统前端地址',
|
||
backend_url VARCHAR(256) NOT NULL COMMENT '系统后端真实地址',
|
||
gateway_url VARCHAR(256) NOT NULL COMMENT '网关代理地址',
|
||
icon VARCHAR(512) DEFAULT NULL COMMENT '系统图标URL',
|
||
sort_order INT DEFAULT 0 COMMENT '排序',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-禁用 1-启用',
|
||
description VARCHAR(512) DEFAULT NULL COMMENT '系统描述',
|
||
created_by BIGINT DEFAULT NULL COMMENT '创建人ID',
|
||
updated_by BIGINT DEFAULT NULL COMMENT '更新人ID',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除：0-正常 1-已删除',
|
||
PRIMARY KEY (id),
|
||
-- uk_system_code/uk_gateway_url 唯一性由应用层代码校验
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='业务系统表';
|
||
""",
|
||
|
||
# 2. 系统放行接口表
|
||
"""
|
||
CREATE TABLE IF NOT EXISTS sys_api (
|
||
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
system_id BIGINT NOT NULL COMMENT '关联系统ID',
|
||
api_path VARCHAR(256) NOT NULL COMMENT '接口路径',
|
||
api_method VARCHAR(10) NOT NULL COMMENT '请求方式：GET/POST/PUT/DELETE',
|
||
api_name VARCHAR(128) DEFAULT NULL COMMENT '接口名称',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-禁用 1-启用',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (id),
|
||
KEY idx_system_id (system_id),
|
||
UNIQUE KEY uk_system_path_method (system_id, api_path, api_method)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统放行接口表';
|
||
""",
|
||
|
||
# 3. 用户表
|
||
"""
|
||
CREATE TABLE IF NOT EXISTS sys_user (
|
||
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
username VARCHAR(64) NOT NULL COMMENT '登录账号',
|
||
password VARCHAR(256) NOT NULL COMMENT 'BCrypt加密密码',
|
||
real_name VARCHAR(64) DEFAULT NULL COMMENT '真实姓名',
|
||
email VARCHAR(128) DEFAULT NULL COMMENT '邮箱',
|
||
phone VARCHAR(32) DEFAULT NULL COMMENT '手机号',
|
||
avatar VARCHAR(512) DEFAULT NULL COMMENT '头像URL',
|
||
post VARCHAR(64) DEFAULT NULL COMMENT '岗位',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-禁用 1-正常 2-锁定',
|
||
lock_until DATETIME DEFAULT NULL COMMENT '锁定截止时间',
|
||
login_fail_count INT NOT NULL DEFAULT 0 COMMENT '连续登录失败次数',
|
||
last_login_ip VARCHAR(64) DEFAULT NULL COMMENT '最后登录IP',
|
||
last_login_time DATETIME DEFAULT NULL COMMENT '最后登录时间',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除：0-正常 1-已删除',
|
||
PRIMARY KEY (id),
|
||
-- 用户名/邮箱唯一性由应用层代码校验
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';
|
||
""",
|
||
|
||
# 4. 用户系统绑定表
|
||
"""
|
||
CREATE TABLE IF NOT EXISTS sys_user_system (
|
||
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
user_id BIGINT NOT NULL COMMENT '用户ID',
|
||
system_id BIGINT NOT NULL COMMENT '系统ID',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '绑定时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_user_system (user_id, system_id),
|
||
KEY idx_system_id (system_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户系统绑定表';
|
||
""",
|
||
|
||
# 5. 角色表
|
||
"""
|
||
CREATE TABLE IF NOT EXISTS sys_role (
|
||
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
system_id BIGINT NOT NULL COMMENT '所属系统ID',
|
||
role_name VARCHAR(64) NOT NULL COMMENT '角色名称',
|
||
role_code VARCHAR(64) NOT NULL COMMENT '角色编码',
|
||
description VARCHAR(256) DEFAULT NULL COMMENT '角色描述',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-禁用 1-启用',
|
||
created_by BIGINT DEFAULT NULL COMMENT '创建人ID',
|
||
updated_by BIGINT DEFAULT NULL COMMENT '更新人ID',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除：0-正常 1-已删除',
|
||
PRIMARY KEY (id),
|
||
-- uk_system_role_code 唯一性由应用层代码校验
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色表';
|
||
""",
|
||
|
||
# 6. 用户角色表
|
||
"""
|
||
CREATE TABLE IF NOT EXISTS sys_user_role (
|
||
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
user_id BIGINT NOT NULL COMMENT '用户ID',
|
||
role_id BIGINT NOT NULL COMMENT '角色ID',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '绑定时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_user_role (user_id, role_id),
|
||
KEY idx_role_id (role_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户角色表';
|
||
""",
|
||
|
||
# 7. 菜单表
|
||
"""
|
||
CREATE TABLE IF NOT EXISTS sys_menu (
|
||
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
system_id BIGINT NOT NULL COMMENT '所属系统ID',
|
||
parent_id BIGINT NOT NULL DEFAULT 0 COMMENT '父菜单ID，0表示顶级',
|
||
menu_name VARCHAR(64) NOT NULL COMMENT '菜单名称',
|
||
menu_path VARCHAR(256) DEFAULT NULL COMMENT '路由路径',
|
||
component VARCHAR(256) DEFAULT NULL COMMENT '前端组件路径',
|
||
icon VARCHAR(128) DEFAULT NULL COMMENT '菜单图标',
|
||
menu_type TINYINT NOT NULL COMMENT '菜单类型：1-目录 2-菜单 3-按钮',
|
||
permission VARCHAR(128) DEFAULT NULL COMMENT '权限标识（按钮用）',
|
||
sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-禁用 1-启用',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (id),
|
||
KEY idx_system_id (system_id),
|
||
KEY idx_parent_id (parent_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='菜单表';
|
||
""",
|
||
|
||
# 8. 角色菜单表
|
||
"""
|
||
CREATE TABLE IF NOT EXISTS sys_role_menu (
|
||
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
role_id BIGINT NOT NULL COMMENT '角色ID',
|
||
menu_id BIGINT NOT NULL COMMENT '菜单ID',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_role_menu (role_id, menu_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色菜单表';
|
||
""",
|
||
|
||
# 9. 登录日志表
|
||
"""
|
||
CREATE TABLE IF NOT EXISTS sys_login_log (
|
||
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
user_id BIGINT DEFAULT NULL COMMENT '用户ID',
|
||
username VARCHAR(64) NOT NULL COMMENT '登录账号',
|
||
system_id BIGINT DEFAULT NULL COMMENT '登录目标系统ID',
|
||
login_ip VARCHAR(64) DEFAULT NULL COMMENT '登录IP',
|
||
user_agent VARCHAR(512) DEFAULT NULL COMMENT '浏览器User-Agent',
|
||
status TINYINT NOT NULL COMMENT '登录状态：0-失败 1-成功',
|
||
fail_reason VARCHAR(256) DEFAULT NULL COMMENT '失败原因',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登录时间',
|
||
PRIMARY KEY (id),
|
||
KEY idx_user_id (user_id),
|
||
KEY idx_created_at (created_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='登录日志表';
|
||
""",
|
||
|
||
# 10. 操作日志表
|
||
"""
|
||
CREATE TABLE IF NOT EXISTS sys_operation_log (
|
||
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
user_id BIGINT DEFAULT NULL COMMENT '操作人ID',
|
||
username VARCHAR(64) DEFAULT NULL COMMENT '操作人名称',
|
||
system_id BIGINT DEFAULT NULL COMMENT '所属系统ID',
|
||
module VARCHAR(64) DEFAULT NULL COMMENT '操作模块',
|
||
action VARCHAR(32) DEFAULT NULL COMMENT '操作类型：CREATE/UPDATE/DELETE/QUERY/IMPORT/EXPORT',
|
||
request_url VARCHAR(256) DEFAULT NULL COMMENT '请求地址',
|
||
request_method VARCHAR(10) DEFAULT NULL COMMENT '请求方式',
|
||
request_body TEXT DEFAULT NULL COMMENT '请求体',
|
||
response_status INT DEFAULT NULL COMMENT '响应状态码',
|
||
error_msg TEXT DEFAULT NULL COMMENT '错误信息',
|
||
cost_time INT DEFAULT NULL COMMENT '耗时（毫秒）',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
||
PRIMARY KEY (id),
|
||
KEY idx_user_id (user_id),
|
||
KEY idx_created_at (created_at),
|
||
KEY idx_system_id (system_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志表';
|
||
""",
|
||
|
||
# 11. 数据字典表
|
||
"""
|
||
CREATE TABLE IF NOT EXISTS sys_dict (
|
||
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
dict_type VARCHAR(64) NOT NULL COMMENT '字典类型',
|
||
dict_label VARCHAR(128) NOT NULL COMMENT '字典标签',
|
||
dict_value VARCHAR(128) NOT NULL COMMENT '字典值',
|
||
sort_order INT DEFAULT 0 COMMENT '排序',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-禁用 1-启用',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_type_value (dict_type, dict_value)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='数据字典表';
|
||
""",
|
||
]
|
||
|
||
|
||
def main():
|
||
"""连接数据库，执行建表"""
|
||
conn = None
|
||
try:
|
||
print(f"[INFO] Connecting to MySQL {DB_CONFIG['host']}:{DB_CONFIG['port']} ...")
|
||
conn = pymysql.connect(**DB_CONFIG)
|
||
cursor = conn.cursor()
|
||
print(f"[OK] Connected, database: {DB_CONFIG['database']}")
|
||
|
||
# 先查现有表
|
||
cursor.execute("SHOW TABLES")
|
||
existing = {row[0] for row in cursor.fetchall()}
|
||
print(f"\n[INFO] Existing tables: {existing if existing else '(empty)'}")
|
||
|
||
# 逐个执行 DDL
|
||
created = 0
|
||
skipped = 0
|
||
for ddl in DDL_STATEMENTS:
|
||
# 提取表名
|
||
table_name = ddl.split("TABLE IF NOT EXISTS")[1].strip().split("(")[0].strip().strip('`')
|
||
if table_name in existing:
|
||
print(f" [SKIP] {table_name} already exists")
|
||
skipped += 1
|
||
else:
|
||
cursor.execute(ddl.strip())
|
||
conn.commit()
|
||
print(f" [OK] {table_name} created")
|
||
created += 1
|
||
|
||
print(f"\n===== Done: {created} created, {skipped} skipped =====")
|
||
|
||
# 展示所有表
|
||
cursor.execute("SHOW TABLES")
|
||
tables = [row[0] for row in cursor.fetchall()]
|
||
print(f"Current tables ({len(tables)}): {tables}")
|
||
|
||
except pymysql.Error as e:
|
||
print(f"[ERROR] Database error: {e}")
|
||
finally:
|
||
if conn:
|
||
conn.close()
|
||
print("\n[INFO] Connection closed.")
|
||
|
||
|
||
if __name__ == '__main__':
|
||
main()
|