okami.mom/web-extract-go
git clone https://git.nkp.pet/okami.mom/web-extract-go
5a90548
main
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 ): 19uri = Path (path ).resolve ().as_uri () 20db = sqlite3 .connect (uri + "?mode=ro" ,uri = True ) 21try : 22db .execute ("PRAGMA schema_version" ).fetchone () 23return db 24except sqlite3 .OperationalError as error : 25db .close () 26if str (error )!= "unable to open database file" : 27raise 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. 31db = sqlite3 .connect (uri + "?mode=rw" ,uri = True ) 32try : 33db .execute ("PRAGMA query_only=ON" ) 34db .execute ("PRAGMA schema_version" ).fetchone () 35return db 36except BaseException : 37db .close () 38raise 39 40 41def tables (db ): 42return {row [0 ]for row in db .execute ("SELECT name FROM sqlite_master WHERE type='table'" )} 43 44 45def columns (db ,table ): 46return [row [1 ]for row in db .execute ('PRAGMA table_info("%s")' % table )] 47 48 49def fingerprint (values ): 50return hashlib .sha256 (json .dumps (values ,ensure_ascii = False ,separators = ("," ,":" )).encode ()).hexdigest () 51 52 53def snapshot (source ,destination ): 54destination = Path (destination ) 55destination .parent .mkdir (parents = True ,exist_ok = True ) 56fd = os .open (destination ,os .O_CREAT | os .O_EXCL | os .O_WRONLY ,0o600 ) 57os .close (fd ) 58src ,dst = connect_readonly (source ),sqlite3 .connect (destination ) 59try : 60src .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. 63if dst .execute ("PRAGMA journal_mode=DELETE" ).fetchone ()[0 ]!= "delete" : 64raise RuntimeError ("could not make snapshot self-contained" ) 65if dst .execute ("PRAGMA integrity_check" ).fetchone ()[0 ]!= "ok" : 66raise RuntimeError ("snapshot integrity check failed" ) 67finally : 68src .close () 69dst .close () 70 71 72def merge (source ,target ,source_id ,backup = None ,apply = False ): 73if Path (source ).resolve ()== Path (target ).resolve (): 74raise ValueError ("source and target must differ" ) 75if not source_id .strip (): 76raise ValueError ("use a stable, nonempty source identity" ) 77src ,dst = connect_readonly (source ),connect_readonly (target ) 78try : 79for db in (src ,dst ): 80if db .execute ("PRAGMA integrity_check" ).fetchone ()[0 ]!= "ok" : 81raise ValueError ("database integrity check failed" ) 82existing = tables (dst ) 83report = {} 84for table in TABLES : 85if table not in tables (src ): 86continue 87if table not in existing : 88raise ValueError ("initialize the target with the go service first: missing " + table ) 89if columns (src ,table )!= columns (dst ,table ): 90raise ValueError ("source/target column mismatch in " + table ) 91report [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 } 96except BaseException : 97src .close () 98raise 99finally : 100dst .close () 101if not apply : 102src .close () 103return {"dryRun" :True ,"tables" :report } 104if not backup : 105src .close () 106raise ValueError ("apply requires a new backup path" ) 107try : 108snapshot (target ,backup ) 109dst = sqlite3 .connect (target ,timeout = 30 ) 110except BaseException : 111src .close () 112raise 113try : 114dst .execute ("BEGIN IMMEDIATE" ) 115dst .execute ("""CREATE TABLE IF NOT EXISTS log_imports ( 116source_id TEXT NOT NULL, table_name TEXT NOT NULL, source_row_id INTEGER NOT NULL, 117target_row_id INTEGER NOT NULL, row_sha256 TEXT NOT NULL, 118PRIMARY KEY(source_id,table_name,source_row_id))""" ) 119for table ,counts in report .items (): 120names = columns (src ,table ) 121id_index = names .index ("id" ) 122payload_names = [name for name in names if name != "id" ] 123projection = "," .join ('"%s"' % name for name in payload_names ) 124insert = 'INSERT INTO "%s" (%s) VALUES (%s)' % (table ,projection ,"," .join ("?" for _ in payload_names )) 125for row in src .execute ('SELECT * FROM "%s" ORDER BY id' % table ): 126row_id = row [id_index ] 127values = tuple (value for i ,value in enumerate (row )if i != id_index ) 128digest = fingerprint (values ) 129imported = 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 () 130if imported : 131saved = dst .execute ('SELECT %s FROM "%s" WHERE id=?' % (projection ,table ), (imported [0 ],)).fetchone () 132if imported [1 ]!= digest or saved is None or fingerprint (saved )!= digest : 133raise ValueError ("previously imported row changed: %s:%s" % (table ,row_id )) 134counts ["alreadyImported" ]+= 1 135continue 136# A matching request_id already in the destination is ambiguous; 137# do not silently merge two correlated episodes or guess a dedupe. 138request_id = values [payload_names .index ("request_id" )] 139if 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 (): 140raise ValueError ("request_id overlaps a different origin in " + table ) 141target_id = dst .execute (insert ,values ).lastrowid 142dst .execute ("INSERT INTO log_imports VALUES(?,?,?,?,?)" , (source_id ,table ,row_id ,target_id ,digest )) 143counts ["inserted" ]+= 1 144counts ["targetAfter" ]= dst .execute ('SELECT count(*) FROM "%s"' % table ).fetchone ()[0 ] 145if counts ["targetAfter" ]!= counts ["targetBefore" ]+ counts ["inserted" ]: 146raise ValueError ("unexpected target row count in " + table ) 147if dst .execute ("PRAGMA integrity_check" ).fetchone ()[0 ]!= "ok" : 148raise ValueError ("merged database integrity check failed" ) 149dst .commit () 150return {"dryRun" :False ,"sourceId" :source_id ,"backup" :str (backup ),"tables" :report } 151except BaseException : 152dst .rollback () 153raise 154finally : 155src .close () 156dst .close () 157 158 159if __name__ == "__main__" : 160parser = argparse .ArgumentParser (description = __doc__ ) 161parser .add_argument ("--source" ,required = True ) 162parser .add_argument ("--target" ,required = True ) 163parser .add_argument ("--source-id" ,required = True ) 164parser .add_argument ("--backup" ) 165parser .add_argument ("--apply" ,action = "store_true" ) 166args = parser .parse_args () 167json .dumps (merge (args .source ,args .target ,args .source_id ,args .backup ,args .apply ),indent = 2 ))