117 lines
4.5 KiB
Python
117 lines
4.5 KiB
Python
import re
|
||
import os
|
||
|
||
# 读取 SQL 文件内容
|
||
def read_sql_file(file_path):
|
||
with open(file_path, 'r', encoding='utf-8') as f:
|
||
return f.read()
|
||
|
||
# 解析 database.sql，提取表结构和注释
|
||
def parse_database_sql(sql_content):
|
||
tables = {}
|
||
|
||
# 匹配 CREATE TABLE 语句，更灵活的格式
|
||
# 匹配所有 CREATE TABLE 语句，不依赖于 ENGINE、CHARACTER SET 等子句的顺序
|
||
table_pattern = re.compile(r"CREATE TABLE\s+`?([^`\s]+)`?\s*\(([^;]+)\)\s*(?:[^;]+COMMENT\s*=\s*'([^']+)'[^;]*)?\s*;", re.DOTALL | re.IGNORECASE)
|
||
|
||
matches = table_pattern.findall(sql_content)
|
||
|
||
for table_name, table_def, table_comment in matches:
|
||
if not table_comment:
|
||
# 如果没有匹配到表注释，尝试从其他位置获取
|
||
comment_match = re.search(r"COMMENT\s*=\s*'([^']+)'", table_def, re.IGNORECASE)
|
||
if comment_match:
|
||
table_comment = comment_match.group(1)
|
||
else:
|
||
table_comment = ''
|
||
|
||
# 解析列定义和注释
|
||
columns = {}
|
||
# 匹配列定义，包括 COMMENT
|
||
column_lines = table_def.split('\n')
|
||
for line in column_lines:
|
||
# 匹配列名、类型和注释
|
||
column_match = re.search(r"\s*([^\s,]+)\s+([^\s,]+)\s*(?:[^,]+COMMENT\s*=\s*'([^']+)'[^,]*|[^,]*)", line)
|
||
if column_match:
|
||
column_name = column_match.group(1)
|
||
column_comment = column_match.group(3) or ''
|
||
if column_comment:
|
||
columns[column_name] = column_comment
|
||
|
||
tables[table_name] = {
|
||
'comment': table_comment,
|
||
'columns': columns
|
||
}
|
||
|
||
return tables
|
||
|
||
# 更新 init_v2.0.sql 文件中的注释
|
||
def update_init_sql(init_sql_path, database_tables):
|
||
# 读取 init_v2.0.sql 内容
|
||
init_content = read_sql_file(init_sql_path)
|
||
|
||
# 匹配 CREATE TABLE 语句，适应 init_v2.0.sql 的格式
|
||
table_pattern = re.compile(r"(create table if not exists lucky_([^\s]+)\s*\(([^;]+)\)\s*comment\s*=\s*'[^']*'\s*(.*?);)", re.DOTALL | re.IGNORECASE)
|
||
|
||
def replace_table(match):
|
||
full_match = match.group(0)
|
||
table_name = match.group(2)
|
||
table_def = match.group(3)
|
||
table_suffix = match.group(4)
|
||
|
||
if table_name in database_tables:
|
||
# 获取数据库表的注释和列注释
|
||
db_table = database_tables[table_name]
|
||
table_comment = db_table['comment']
|
||
columns = db_table['columns']
|
||
|
||
# 更新列注释
|
||
new_table_def = table_def
|
||
for column_name, column_comment in columns.items():
|
||
# 匹配列定义，替换注释
|
||
# 格式：列名 类型 default 默认值 not null comment '注释'
|
||
column_pattern = re.compile(r"(\s*" + column_name + r"\s+[^\s,]+\s*(?:default\s+[^\s,]+\s*)?(?:not null\s*)?comment\s*=\s*')([^']*)'([^,]*)", re.IGNORECASE)
|
||
new_table_def = column_pattern.sub(r"\1" + column_comment + r"'\3", new_table_def)
|
||
|
||
# 重新构建 CREATE TABLE 语句
|
||
new_full_match = f"create table if not exists lucky_{table_name} ({new_table_def}) comment = '{table_comment}' {table_suffix};"
|
||
return new_full_match
|
||
|
||
return full_match
|
||
|
||
# 替换所有表
|
||
updated_content = table_pattern.sub(replace_table, init_content)
|
||
|
||
# 写回文件
|
||
with open(init_sql_path, 'w', encoding='utf-8') as f:
|
||
f.write(updated_content)
|
||
|
||
print(f"Updated {init_sql_path}")
|
||
|
||
# 主函数
|
||
def main():
|
||
# 文件路径
|
||
database_sql_path = r'./niushop_database.sql'
|
||
init_v20_sql_path = r'./init_v2.0.sql'
|
||
init_v20_with_data_sql_path = r'./init_v2.0_with_data.sql'
|
||
|
||
# 解析 database.sql
|
||
print("Parsing database.sql...")
|
||
database_content = read_sql_file(database_sql_path)
|
||
database_tables = parse_database_sql(database_content)
|
||
print(f"Found {len(database_tables)} tables in database.sql")
|
||
|
||
# 更新 init_v2.0.sql
|
||
if os.path.exists(init_v20_sql_path):
|
||
print("Updating init_v2.0.sql...")
|
||
update_init_sql(init_v20_sql_path, database_tables)
|
||
|
||
# 更新 init_v2.0_with_data.sql
|
||
if os.path.exists(init_v20_with_data_sql_path):
|
||
print("Updating init_v2.0_with_data.sql...")
|
||
update_init_sql(init_v20_with_data_sql_path, database_tables)
|
||
|
||
print("All files updated successfully!")
|
||
|
||
if __name__ == "__main__":
|
||
main() |