Files

75 lines
3.6 KiB
Python

"""Explicit, atomic migration from a consistent SQLite snapshot into an empty MySQL database."""
import argparse
import json
import sqlite3
from pathlib import Path
from studio.domain import normalized_name, is_special_score
from studio.mysql_database import MySQLDatabase, score_key
TABLES = ('catalog', 'scores', 'schools', 'jobs', 'job_units', 'job_logs')
def migrate_sqlite(source, target):
source = Path(source).resolve()
conn = sqlite3.connect(source.as_uri() + '?mode=ro', uri=True)
conn.row_factory = sqlite3.Row
counts = {}
try:
conn.execute('BEGIN')
with target.connection() as output:
# Serialize attempted imports to the same database.
acquired = output.execute("SELECT GET_LOCK('kaoyan_studio_migration',10)").fetchone()[0]
if acquired != 1:
raise ValueError('已有数据迁移正在进行')
try:
for table in TABLES:
if output.execute(f'SELECT count(*) FROM {table}').fetchone()[0]:
raise ValueError(f'目标数据库非空({table}),拒绝覆盖;请使用空数据库')
for table in TABLES:
counts[table] = 0
reader = conn.execute(f'SELECT * FROM {table}')
while batch := reader.fetchmany(500):
rows = []
for original in batch:
row = dict(original)
if table in ('catalog', 'scores'):
payload = json.loads(row['data'])
row['department_norm'] = normalized_name(row.get('department_name'))
if table == 'scores':
row['note'] = row.get('note') or payload.get('note') or ''
row['raw_code'] = row.get('raw_code') or payload.get('raw_code') or ''
row['score_key'] = score_key(row)
row['special_line'] = int(is_special_score(row['note']))
if table == 'jobs' and row['status'] in ('running', 'queued'):
row.update(status='paused', message='已迁移到服务器,验证登录后可从断点恢复')
if table == 'jobs':
row['resume_pending'] = 0
rows.append(row)
fields = list(rows[0])
output.executemany(f"INSERT INTO {table}({','.join(fields)}) VALUES({','.join('?' for _ in fields)})", [[row[field] for field in fields] for row in rows])
counts[table] += len(rows)
for table, count in counts.items():
if output.execute(f'SELECT count(*) FROM {table}').fetchone()[0] != count:
raise ValueError(f'迁移行数校验失败:{table}')
finally:
# The private connection closes after commit/rollback, releasing its
# advisory lock only after the entire import becomes visible.
pass
return counts
finally:
conn.rollback()
conn.close()
def main():
parser = argparse.ArgumentParser()
parser.add_argument('snapshot', type=Path)
parser.add_argument('--config', type=Path, required=True)
args = parser.parse_args()
config = json.loads(args.config.read_text())
print(json.dumps(migrate_sqlite(args.snapshot, MySQLDatabase(config)), ensure_ascii=False))
if __name__ == '__main__':
main()