75 lines
3.6 KiB
Python
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()
|