okami.mom/web-extract-go

git clone https://git.nkp.pet/okami.mom/web-extract-go

nekomimi.petweb-extract: the Go service running on the Mac mini (vassago)5a90548

main
7.5 KiB167 linesraw
1#!/usr/bin/env python3
2"""Append an SQLite log snapshot without replacing rows or their training bodies.
3
4Dry-run by default. Apply to a stopped deployment with an exclusive backup path.
5The stable source-id + original row id ledger makes retries/incremental snapshots
6idempotent while keeping legitimately identical calls as distinct training rows.
7"""
8import argparse
9import hashlib
10import json
11import os
12from pathlib import Path
13import sqlite3
14
15TABLES = ("completions", "requests", "decisions", "evidence")
16
17
18def connect_readonly(path):
19    uri = Path(path).resolve().as_uri()
20    db = sqlite3.connect(uri + "?mode=ro", uri=True)
21    try:
22        db.execute("PRAGMA schema_version").fetchone()
23        return db
24    except sqlite3.OperationalError as error:
25        db.close()
26        if str(error) != "unable to open database file":
27            raise
28    # macOS SQLite can require a writable VFS handle to create missing WAL
29    # sidecars, even for reads. Allow that housekeeping, but prohibit SQL writes.
30    # mode=rw also refuses to create a missing database.
31    db = sqlite3.connect(uri + "?mode=rw", uri=True)
32    try:
33        db.execute("PRAGMA query_only=ON")
34        db.execute("PRAGMA schema_version").fetchone()
35        return db
36    except BaseException:
37        db.close()
38        raise
39
40
41def tables(db):
42    return {row[0] for row in db.execute("SELECT name FROM sqlite_master WHERE type='table'")}
43
44
45def columns(db, table):
46    return [row[1] for row in db.execute('PRAGMA table_info("%s")' % table)]
47
48
49def fingerprint(values):
50    return hashlib.sha256(json.dumps(values, ensure_ascii=False, separators=(",", ":")).encode()).hexdigest()
51
52
53def snapshot(source, destination):
54    destination = Path(destination)
55    destination.parent.mkdir(parents=True, exist_ok=True)
56    fd = os.open(destination, os.O_CREAT | os.O_EXCL | os.O_WRONLY, 0o600)
57    os.close(fd)
58    src, dst = connect_readonly(source), sqlite3.connect(destination)
59    try:
60        src.backup(dst)
61        # A transferred snapshot must be self-contained. WAL-mode headers can
62        # require missing -wal/-shm files on a read-only open on another host.
63        if dst.execute("PRAGMA journal_mode=DELETE").fetchone()[0] != "delete":
64            raise RuntimeError("could not make snapshot self-contained")
65        if dst.execute("PRAGMA integrity_check").fetchone()[0] != "ok":
66            raise RuntimeError("snapshot integrity check failed")
67    finally:
68        src.close()
69        dst.close()
70
71
72def merge(source, target, source_id, backup=None, apply=False):
73    if Path(source).resolve() == Path(target).resolve():
74        raise ValueError("source and target must differ")
75    if not source_id.strip():
76        raise ValueError("use a stable, nonempty source identity")
77    src, dst = connect_readonly(source), connect_readonly(target)
78    try:
79        for db in (src, dst):
80            if db.execute("PRAGMA integrity_check").fetchone()[0] != "ok":
81                raise ValueError("database integrity check failed")
82        existing = tables(dst)
83        report = {}
84        for table in TABLES:
85            if table not in tables(src):
86                continue
87            if table not in existing:
88                raise ValueError("initialize the target with the go service first: missing " + table)
89            if columns(src, table) != columns(dst, table):
90                raise ValueError("source/target column mismatch in " + table)
91            report[table] = {
92                "sourceRows": src.execute('SELECT count(*) FROM "%s"' % table).fetchone()[0],
93                "targetBefore": dst.execute('SELECT count(*) FROM "%s"' % table).fetchone()[0],
94                "inserted": 0, "alreadyImported": 0,
95            }
96    except BaseException:
97        src.close()
98        raise
99    finally:
100        dst.close()
101    if not apply:
102        src.close()
103        return {"dryRun": True, "tables": report}
104    if not backup:
105        src.close()
106        raise ValueError("apply requires a new backup path")
107    try:
108        snapshot(target, backup)
109        dst = sqlite3.connect(target, timeout=30)
110    except BaseException:
111        src.close()
112        raise
113    try:
114        dst.execute("BEGIN IMMEDIATE")
115        dst.execute("""CREATE TABLE IF NOT EXISTS log_imports (
116            source_id TEXT NOT NULL, table_name TEXT NOT NULL, source_row_id INTEGER NOT NULL,
117            target_row_id INTEGER NOT NULL, row_sha256 TEXT NOT NULL,
118            PRIMARY KEY(source_id,table_name,source_row_id))""")
119        for table, counts in report.items():
120            names = columns(src, table)
121            id_index = names.index("id")
122            payload_names = [name for name in names if name != "id"]
123            projection = ",".join('"%s"' % name for name in payload_names)
124            insert = 'INSERT INTO "%s" (%s) VALUES (%s)' % (table, projection, ",".join("?" for _ in payload_names))
125            for row in src.execute('SELECT * FROM "%s" ORDER BY id' % table):
126                row_id = row[id_index]
127                values = tuple(value for i, value in enumerate(row) if i != id_index)
128                digest = fingerprint(values)
129                imported = dst.execute("SELECT target_row_id,row_sha256 FROM log_imports WHERE source_id=? AND table_name=? AND source_row_id=?", (source_id, table, row_id)).fetchone()
130                if imported:
131                    saved = dst.execute('SELECT %s FROM "%s" WHERE id=?' % (projection, table), (imported[0],)).fetchone()
132                    if imported[1] != digest or saved is None or fingerprint(saved) != digest:
133                        raise ValueError("previously imported row changed: %s:%s" % (table, row_id))
134                    counts["alreadyImported"] += 1
135                    continue
136                # A matching request_id already in the destination is ambiguous;
137                # do not silently merge two correlated episodes or guess a dedupe.
138                request_id = values[payload_names.index("request_id")]
139                if request_id and dst.execute('SELECT 1 FROM "%s" WHERE request_id=? AND id NOT IN (SELECT target_row_id FROM log_imports WHERE source_id=? AND table_name=?) LIMIT 1' % table, (request_id, source_id, table)).fetchone():
140                    raise ValueError("request_id overlaps a different origin in " + table)
141                target_id = dst.execute(insert, values).lastrowid
142                dst.execute("INSERT INTO log_imports VALUES(?,?,?,?,?)", (source_id, table, row_id, target_id, digest))
143                counts["inserted"] += 1
144            counts["targetAfter"] = dst.execute('SELECT count(*) FROM "%s"' % table).fetchone()[0]
145            if counts["targetAfter"] != counts["targetBefore"] + counts["inserted"]:
146                raise ValueError("unexpected target row count in " + table)
147        if dst.execute("PRAGMA integrity_check").fetchone()[0] != "ok":
148            raise ValueError("merged database integrity check failed")
149        dst.commit()
150        return {"dryRun": False, "sourceId": source_id, "backup": str(backup), "tables": report}
151    except BaseException:
152        dst.rollback()
153        raise
154    finally:
155        src.close()
156        dst.close()
157
158
159if __name__ == "__main__":
160    parser = argparse.ArgumentParser(description=__doc__)
161    parser.add_argument("--source", required=True)
162    parser.add_argument("--target", required=True)
163    parser.add_argument("--source-id", required=True)
164    parser.add_argument("--backup")
165    parser.add_argument("--apply", action="store_true")
166    args = parser.parse_args()
167    print(json.dumps(merge(args.source, args.target, args.source_id, args.backup, args.apply), indent=2))