105 lines
3.2 KiB
Python
105 lines
3.2 KiB
Python
|
#!/usr/bin/env python3
|
|||
|
|
"""
|
|||
|
|
Migrate MariaDB export to SQLite for qaffee.
|
|||
|
|
|
|||
|
|
Usage:
|
|||
|
|
python3 db/migrate_to_sqlite.py
|
|||
|
|
|
|||
|
|
Creates data/qaffee.db ready for use with the Quarkus backend.
|
|||
|
|
Flyway will baseline at V4 on first startup (no re-migration needed).
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
import re
|
|||
|
|
import sqlite3
|
|||
|
|
import os
|
|||
|
|
import sys
|
|||
|
|
|
|||
|
|
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
|
|||
|
|
REPO_ROOT = os.path.join(SCRIPT_DIR, '..')
|
|||
|
|
MIGRATION_DIR = os.path.join(REPO_ROOT, 'backend', 'src', 'main', 'resources', 'db', 'migration')
|
|||
|
|
EXPORT_FILE = os.path.join(SCRIPT_DIR, 'mariadb_export.sql')
|
|||
|
|
OUTPUT_FILE = os.path.join(REPO_ROOT, 'data', 'qaffee.db')
|
|||
|
|
|
|||
|
|
# V2 (seed data) is intentionally skipped – it inserts dev/test rows that
|
|||
|
|
# conflict with production IDs in the export.
|
|||
|
|
MIGRATIONS = [
|
|||
|
|
'V1__initial_schema.sql',
|
|||
|
|
'V3__company_logo.sql',
|
|||
|
|
'V4__tally_price_at_booking.sql',
|
|||
|
|
]
|
|||
|
|
|
|||
|
|
# Tables to import in FK-safe order (parents before children)
|
|||
|
|
DATA_TABLES = ['company', 'employee', 'product', 'access_link', 'tally_entry']
|
|||
|
|
|
|||
|
|
|
|||
|
|
def apply_migrations(conn: sqlite3.Connection) -> None:
|
|||
|
|
for filename in MIGRATIONS:
|
|||
|
|
path = os.path.join(MIGRATION_DIR, filename)
|
|||
|
|
with open(path, 'r', encoding='utf-8') as f:
|
|||
|
|
sql = f.read()
|
|||
|
|
conn.executescript(sql)
|
|||
|
|
print(f' schema {filename}')
|
|||
|
|
|
|||
|
|
|
|||
|
|
def mariadb_to_sqlite_sql(sql: str) -> str:
|
|||
|
|
# Remove MariaDB sandbox-mode comment at the top
|
|||
|
|
sql = re.sub(r'/\*M!.*?\*/', '', sql, flags=re.DOTALL)
|
|||
|
|
# Strip backtick identifier quotes
|
|||
|
|
sql = sql.replace('`', '')
|
|||
|
|
# Convert 0xABCD hex literals to SQLite X'ABCD' blob literals
|
|||
|
|
sql = re.sub(r'\b0x([0-9A-Fa-f]+)', r"X'\1'", sql)
|
|||
|
|
return sql
|
|||
|
|
|
|||
|
|
|
|||
|
|
def extract_insert(sql: str, table: str) -> str | None:
|
|||
|
|
"""Return the full INSERT INTO <table> VALUES ...; statement, or None."""
|
|||
|
|
pattern = rf'(INSERT INTO {re.escape(table)} VALUES\s*.*?;)'
|
|||
|
|
match = re.search(pattern, sql, re.DOTALL)
|
|||
|
|
return match.group(1) if match else None
|
|||
|
|
|
|||
|
|
|
|||
|
|
def main() -> None:
|
|||
|
|
os.makedirs(os.path.dirname(OUTPUT_FILE), exist_ok=True)
|
|||
|
|
|
|||
|
|
if os.path.exists(OUTPUT_FILE):
|
|||
|
|
os.remove(OUTPUT_FILE)
|
|||
|
|
print(f'Removed existing {OUTPUT_FILE}')
|
|||
|
|
|
|||
|
|
print('Reading MariaDB export ...')
|
|||
|
|
with open(EXPORT_FILE, 'r', encoding='utf-8') as f:
|
|||
|
|
raw_sql = f.read()
|
|||
|
|
|
|||
|
|
sqlite_sql = mariadb_to_sqlite_sql(raw_sql)
|
|||
|
|
|
|||
|
|
print('Creating SQLite database ...')
|
|||
|
|
conn = sqlite3.connect(OUTPUT_FILE)
|
|||
|
|
|
|||
|
|
print('Applying schema migrations:')
|
|||
|
|
apply_migrations(conn)
|
|||
|
|
|
|||
|
|
print('Importing data:')
|
|||
|
|
for table in DATA_TABLES:
|
|||
|
|
stmt = extract_insert(sqlite_sql, table)
|
|||
|
|
if stmt is None:
|
|||
|
|
print(f' skip {table} (no data in export)')
|
|||
|
|
continue
|
|||
|
|
conn.executescript(stmt)
|
|||
|
|
# Count rows for feedback
|
|||
|
|
count = conn.execute(f'SELECT COUNT(*) FROM {table}').fetchone()[0]
|
|||
|
|
print(f' import {table} ({count} rows)')
|
|||
|
|
|
|||
|
|
conn.commit()
|
|||
|
|
conn.close()
|
|||
|
|
|
|||
|
|
size_kb = os.path.getsize(OUTPUT_FILE) // 1024
|
|||
|
|
print(f'\nDone – {OUTPUT_FILE} ({size_kb} KB)')
|
|||
|
|
print()
|
|||
|
|
print('Next steps:')
|
|||
|
|
print(' 1. docker compose up --build')
|
|||
|
|
print(' Flyway will baseline at V4 and skip all migrations.')
|
|||
|
|
print(' 2. Verify at https://qaffee.cloud.aquantico.de')
|
|||
|
|
|
|||
|
|
|
|||
|
|
if __name__ == '__main__':
|
|||
|
|
main()
|