blob: 1eeb747b05fabcac16b6523cfec7a6137ff3d6d8 [file] [edit]
#!/usr/bin/env python3
"""Convert an old send-receive.py database to the new SQLite file.
send-receive.py used to keep its auth table in any database SQLAlchemy
could reach (MySQL in production). It now uses a plain sqlite3 file.
This tool reads main.dburl from the old config and copies every record,
including pending challenges, into a new file at main.dbpath:
send-receive-migrate.py -c /path/to/old.conf -o /path/to/auth.sqlite3
Supported dburl schemes are mysql+pymysql:// and sqlite:///.
"""
import argparse
import datetime
import os
import sqlite3
import sys
import tempfile
from configparser import ConfigParser, ExtendedInterpolation
from contextlib import closing
from typing import Any, List, Sequence, Tuple
from urllib.parse import parse_qs, unquote, urlsplit
import pymysql
DB_VERSION = 1
# Must stay the same as SCHEMA in send-receive.py
SCHEMA = """
CREATE TABLE IF NOT EXISTS auth (
auth_id INTEGER PRIMARY KEY AUTOINCREMENT,
created TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
identity TEXT NOT NULL,
selector TEXT NOT NULL,
pubkey TEXT NOT NULL,
challenge TEXT,
verified INTEGER NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_identity_selector ON auth (identity, selector);
"""
COLUMNS = (
'auth_id',
'created',
'identity',
'selector',
'pubkey',
'challenge',
'verified',
)
Row = Tuple[Any, ...]
SELECT_AUTH = f'SELECT {", ".join(COLUMNS)} FROM auth ORDER BY auth_id'
class MigrationError(Exception):
pass
def read_mysql(dburl: str) -> Tuple[List[Row], int]:
"""Return the auth rows and the last auth_id MySQL handed out."""
parts = urlsplit(dburl)
charset = parse_qs(parts.query).get('charset', ['utf8mb4'])[0]
conn = pymysql.connect(
host=parts.hostname or 'localhost',
port=parts.port or 3306,
user=unquote(parts.username or ''),
password=unquote(parts.password or ''),
database=parts.path.lstrip('/'),
charset=charset,
)
with conn, conn.cursor() as cur:
cur.execute(SELECT_AUTH)
rows = list(cur.fetchall())
# The counter can be above max(auth_id) if the newest records were
# deleted, and those ids may already be in published headers.
# MySQL 8 caches it for a day unless told otherwise. Older servers
# don't know the variable, but a stale value is only ever too low,
# and then max(auth_id) still holds.
try:
cur.execute('SET SESSION information_schema_stats_expiry = 0')
except pymysql.err.OperationalError:
pass
cur.execute(
'SELECT AUTO_INCREMENT FROM information_schema.TABLES'
" WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'auth'"
)
row = cur.fetchone()
lastid = int(row[0]) - 1 if row and row[0] else 0
return rows, lastid
def read_sqlite(dburl: str) -> Tuple[List[Row], int]:
# SQLAlchemy writes sqlite:///relative and sqlite:////absolute
path = urlsplit(dburl).path[1:]
if not path or path == ':memory:':
raise MigrationError(f'Nothing to migrate from {dburl}')
if not os.path.exists(path):
raise MigrationError(f'No such database: {path}')
with closing(sqlite3.connect(f'file:{path}?mode=ro', uri=True)) as conn:
rows = conn.execute(SELECT_AUTH).fetchall()
# SQLAlchemy did not use AUTOINCREMENT, so ids were already reusable
return rows, 0
def normalize(row: Row) -> Row:
auth_id, created, identity, selector, pubkey, challenge, verified = row
# CURRENT_TIMESTAMP format, so old and new records sort together
if isinstance(created, datetime.datetime):
created = created.strftime('%Y-%m-%d %H:%M:%S')
return (
int(auth_id),
str(created),
identity,
selector,
pubkey,
challenge,
int(verified),
)
def write_sqlite(outfile: str, rows: List[Row], lastid: int) -> None:
if os.path.exists(outfile):
raise MigrationError(f'Refusing to overwrite {outfile}')
# Build in a temporary file, so a failed run leaves nothing that
# send-receive.py could mistake for a finished database
fd, tmpfile = tempfile.mkstemp(
dir=os.path.dirname(os.path.abspath(outfile)), suffix='.tmp'
)
os.close(fd)
try:
with closing(sqlite3.connect(tmpfile)) as conn:
with conn:
conn.executescript(SCHEMA)
placeholders = ', '.join(['?'] * len(COLUMNS))
try:
conn.executemany(
f'INSERT INTO auth ({", ".join(COLUMNS)})'
f' VALUES ({placeholders})',
[normalize(row) for row in rows],
)
except sqlite3.IntegrityError as ex:
raise MigrationError(f'Source data does not fit: {ex}') from ex
# The inserts above set seq to max(auth_id), but an empty
# table leaves no row at all. sqlite_sequence has no unique
# key, so this can't be a single upsert.
if conn.execute(
"SELECT 1 FROM sqlite_sequence WHERE name = 'auth'"
).fetchone():
conn.execute(
"UPDATE sqlite_sequence SET seq = ? WHERE name = 'auth' AND seq < ?",
(lastid, lastid),
)
else:
conn.execute(
"INSERT INTO sqlite_sequence (name, seq) VALUES ('auth', ?)",
(lastid,),
)
conn.execute(f'PRAGMA user_version = {DB_VERSION}')
# Unlike a rename, link fails if outfile showed up in the meantime
os.link(tmpfile, outfile)
finally:
os.unlink(tmpfile)
def main(argv: Sequence[str] | None = None) -> int:
ap = argparse.ArgumentParser(
description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter
)
ap.add_argument(
'-c', '--config', required=True, help='Old send-receive.py config with dburl'
)
ap.add_argument('-o', '--output', required=True, help='New SQLite file to create')
args = ap.parse_args(argv)
# Read dburl from the config, so the password never shows up in ps
parser = ConfigParser(interpolation=ExtendedInterpolation())
if not parser.read(args.config):
sys.stderr.write(f'Could not read {args.config}\n')
return 1
dburl = parser['main'].get('dburl') if parser.has_section('main') else None
if not dburl:
sys.stderr.write(f'No main.dburl in {args.config}\n')
return 1
scheme = urlsplit(dburl).scheme
try:
if scheme in ('mysql', 'mysql+pymysql'):
rows, lastid = read_mysql(dburl)
elif scheme == 'sqlite':
rows, lastid = read_sqlite(dburl)
else:
raise MigrationError(f'Unsupported database scheme: {scheme}')
write_sqlite(args.output, rows, lastid)
except MigrationError as ex:
sys.stderr.write(f'{ex}\n')
return 1
verified = sum(1 for row in rows if int(row[6]))
print(
f'Wrote {len(rows)} records ({verified} verified,'
f' {len(rows) - verified} pending) to {args.output}'
)
print('The file is only readable by you, chown it to the web user.')
return 0
if __name__ == '__main__':
sys.exit(main())