86 lines
3.7 KiB
Python
86 lines
3.7 KiB
Python
#!/usr/bin/env python3
|
||
"""Renumber the current history import to its source application IDs."""
|
||
import argparse
|
||
import gzip
|
||
import json
|
||
import re
|
||
import sys
|
||
from datetime import datetime
|
||
from pathlib import Path
|
||
|
||
ROOT = Path(__file__).resolve().parents[1]
|
||
sys.path.insert(0, str(ROOT))
|
||
|
||
|
||
def run(local=False):
|
||
if local:
|
||
import test_wsgi # noqa: F401
|
||
from services.db import get_db
|
||
|
||
conn = get_db()
|
||
foreign_keys_disabled = False
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute("""SELECT a.id,a.status,a.product_name,a.image_path
|
||
FROM spot_applications a ORDER BY a.id FOR UPDATE""")
|
||
applications = cur.fetchall()
|
||
imported = []
|
||
for row in applications:
|
||
match = re.fullmatch(r'/static/uploads/spot_application/history_(\d+)_[0-9a-f]{16}\.[A-Za-z0-9]+',
|
||
row.get('image_path') or '')
|
||
if match:
|
||
imported.append((row['id'], int(match.group(1))))
|
||
if len(imported) != 547 or {target for _, target in imported} != set(range(1, 548)):
|
||
raise RuntimeError('导入记录不是完整且唯一的1-547，停止重排')
|
||
imported_ids = {old for old, _ in imported}
|
||
retained = [row for row in applications if row['id'] not in imported_ids]
|
||
max_id = max(row['id'] for row in applications)
|
||
retained_moves = []
|
||
next_id = max_id + 1000
|
||
targets = {target for _, target in imported}
|
||
for row in retained:
|
||
if row['id'] in targets:
|
||
retained_moves.append((row['id'], next_id))
|
||
next_id += 1
|
||
backup = ROOT / 'var/manual-updates' / ('spot-confirmation-renumber-' + datetime.now().strftime('%Y%m%dT%H%M%S') + '.json.gz')
|
||
backup.parent.mkdir(parents=True, exist_ok=True)
|
||
with gzip.open(backup, 'wt', encoding='utf-8') as handle:
|
||
json.dump({'imported': imported, 'retained_moves': retained_moves}, handle, ensure_ascii=False)
|
||
|
||
offset = next_id + 1000
|
||
cur.execute('SET FOREIGN_KEY_CHECKS=0')
|
||
foreign_keys_disabled = True
|
||
for old, new in retained_moves:
|
||
cur.execute('UPDATE spot_confirmations SET application_id=%s WHERE application_id=%s', (new, old))
|
||
cur.execute('UPDATE spot_applications SET id=%s WHERE id=%s', (new, old))
|
||
for old, target in imported:
|
||
temporary = offset + target
|
||
cur.execute('UPDATE spot_confirmations SET application_id=%s WHERE application_id=%s', (temporary, old))
|
||
cur.execute('UPDATE spot_applications SET id=%s WHERE id=%s', (temporary, old))
|
||
for _, target in imported:
|
||
temporary = offset + target
|
||
cur.execute('UPDATE spot_applications SET id=%s WHERE id=%s', (target, temporary))
|
||
cur.execute('UPDATE spot_confirmations SET application_id=%s WHERE application_id=%s', (target, temporary))
|
||
cur.execute('SET FOREIGN_KEY_CHECKS=1')
|
||
foreign_keys_disabled = False
|
||
conn.commit()
|
||
print(json.dumps({'renumbered': len(imported), 'retained_moved': retained_moves,
|
||
'backup': str(backup.relative_to(ROOT))}, ensure_ascii=False))
|
||
except Exception:
|
||
conn.rollback()
|
||
raise
|
||
finally:
|
||
if foreign_keys_disabled:
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute('SET FOREIGN_KEY_CHECKS=1')
|
||
except Exception:
|
||
pass
|
||
conn.close()
|
||
|
||
|
||
if __name__ == '__main__':
|
||
parser = argparse.ArgumentParser()
|
||
parser.add_argument('--local', action='store_true')
|
||
run(parser.parse_args().local)
|