from pathlib import Path import re, sqlite3, tempfile, json, sys APP = Path(__file__).resolve().parents[1] BASE = Path(sys.argv[1]).resolve() if len(sys.argv) > 1 else None def exec_db_exec_blocks(conn, source): text = Path(source).read_text(encoding='utf-8') blocks = re.findall(r"db\.exec\(`(.*?)`\)", text, flags=re.S) applied = 0 for sql in blocks: if '${' in sql: continue try: conn.executescript(sql) applied += 1 except sqlite3.DatabaseError: # db.mjs intentionally catches additive ALTER errors. pass if 'operations-center-1.30.0' in text: conn.execute("INSERT OR IGNORE INTO system_migrations (migration_key, app_version, description) VALUES ('operations-center-1.30.0','1.30.0','Backup, Restore, Update Center und Extension-Diagnose')") return applied def columns(conn, table): return {row[1] for row in conn.execute(f'PRAGMA table_info({table})')} def verify(conn): required_tables = { 'flux_images', 'flux_prompt_jobs', 'flux_prompt_job_variants', 'image_edit_versions', 'image_batch_profiles', 'image_batch_jobs', 'image_batch_job_items', 'quality_reports', 'publishing_jobs', 'publishing_events', 'operation_backups', 'operation_settings', 'update_operations', 'system_migrations', 'extension_clients', 'users', 'sessions', 'audit_log', 'resource_locks', 'theme_profiles', 'user_theme_preferences' } existing_tables = {row[0] for row in conn.execute("SELECT name FROM sqlite_master WHERE type='table'")} missing_tables = required_tables - existing_tables checks = { 'flux_images': {'favorite','job_id','variant_index','prompt_mode','parameters_json'}, 'flux_prompt_jobs': {'archived_at'}, 'image_edit_versions': {'root_path','source_path','output_path','context','operations_json','width','height','format','quality','file_size','user_id','created_at'}, 'image_batch_jobs': {'name','status','phase','source_count','profile_count','total_items','completed_items','failed_items','cancelled_items','pause_requested','cancel_requested','settings_json','created_at','updated_at'}, 'image_batch_job_items': {'job_id','source_path','profile_id','profile_key','profile_name','status','phase','settings_json','output_path','error_message','created_at','updated_at'}, 'quality_reports': {'listing_id','score','grade','ready','blockers','warnings','recommendations','report_json','analyzed_at'}, 'publishing_jobs': {'id','listing_id','platform','mode','action','status','phase','idempotency_key','external_id','external_url','offer_id','retries','max_retries','next_attempt_at','claimed_by','claimed_at','error_code','error_message','payload_json','result_json','created_at','started_at','completed_at','updated_at'}, 'publishing_events': {'id','job_id','listing_id','platform','level','event_type','message','details_json','created_at'}, 'publish_log': {'offer_id','sync_status','last_synced_at','ended_at','updated_at'}, 'operation_backups': {'id','kind','label','file_name','file_path','file_size','sha256','status','verified_at','options_json','manifest_json','created_at'}, 'operation_settings': {'key','value','updated_at'}, 'update_operations': {'id','version_from','version_to','source_name','archive_path','sha256','status','details_json','created_at','applied_at','error_message'}, 'system_migrations': {'id','migration_key','app_version','description','applied_at'}, 'extension_clients': {'client_id','browser','version','platform','server_url','user_agent','capabilities_json','first_seen','last_seen'}, 'users': {'id','email','name','password_hash','role','active','avatar_color','last_active_at','failed_login_count','locked_until','password_changed_at','created_at','invited_by'}, 'sessions': {'id','user_id','token_hash','ip','user_agent','expires_at','last_seen_at','revoked_at','created_at'}, 'audit_log': {'id','user_id','user_email','action','target_type','target_id','details','ip','created_at'}, 'resource_locks': {'id','resource_type','resource_id','user_id','token_hash','acquired_at','renewed_at','expires_at','client_label'}, 'theme_profiles': {'id','name','base_theme','values_json','created_by','created_at','updated_at'}, 'user_theme_preferences': {'user_id','theme_id','density','reduced_motion','updated_at'}, } missing_columns = {table: sorted(required - columns(conn, table)) for table, required in checks.items() if required - columns(conn, table)} if missing_tables or missing_columns: raise AssertionError({'missing_tables': sorted(missing_tables), 'missing_columns': missing_columns}) with tempfile.TemporaryDirectory() as temp: fresh = sqlite3.connect(Path(temp) / 'fresh.db') fresh.executescript((APP / 'db/schema.sql').read_text(encoding='utf-8')) fresh_blocks = exec_db_exec_blocks(fresh, APP / 'lib/db.mjs') verify(fresh) existing = sqlite3.connect(Path(temp) / 'existing.db') existing.executescript((APP / 'db/schema.sql').read_text(encoding='utf-8')) if BASE: exec_db_exec_blocks(existing, BASE / 'lib/db.mjs') existing.execute("INSERT INTO flux_images (prompt,image_path,width,height,seed,steps,style) VALUES ('Bestand','ai-generated/existing.png',768,768,42,4,'none')") existing.commit() existing_blocks = exec_db_exec_blocks(existing, APP / 'lib/db.mjs') verify(existing) row = existing.execute("SELECT prompt,image_path,favorite FROM flux_images WHERE image_path='ai-generated/existing.png'").fetchone() if row != ('Bestand','ai-generated/existing.png',0): raise AssertionError(f'Bestandsdaten verändert: {row!r}') jobs = [('job-complete', 'completed'), ('job-failed', 'failed'), ('job-active', 'running')] insert_job = """INSERT INTO flux_prompt_jobs (id,prompt,final_prompt,width,height,steps,style,profile,prompt_mode,assistant,variants,seed_base,seed_lock,status,phase,settings_json) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""" for job_id, status in jobs: existing.execute(insert_job, (job_id,'Test','Test',768,768,6,'none','balanced','precise',1,1,42,0,status,status,'{}')) existing.commit() existing.execute("UPDATE flux_prompt_jobs SET archived_at=datetime('now') WHERE id IN ('job-complete','job-failed','job-active') AND status NOT IN ('queued','running','cancelling')") archived = {row[0] for row in existing.execute("SELECT id FROM flux_prompt_jobs WHERE archived_at IS NOT NULL")} if archived != {'job-complete','job-failed'}: raise AssertionError(f'Archivschutz inkorrekt: {archived!r}') existing.execute("INSERT INTO listings (title, platform, ai_provider, photos, tags) VALUES ('Publishing Test', 'ebay-de', 'openai', '[]', '[]')") listing_id = existing.execute("SELECT last_insert_rowid()").fetchone()[0] existing.execute("""INSERT INTO publishing_jobs (id, listing_id, platform, mode, action, status, phase, idempotency_key) VALUES ('pub-job-1', ?, 'ebay-de', 'api', 'publish', 'queued', 'queued', 'key-1')""", (listing_id,)) existing.execute("""INSERT INTO publishing_events (job_id, listing_id, platform, level, event_type, message) VALUES ('pub-job-1', ?, 'ebay-de', 'info', 'created', 'Auftrag erstellt')""", (listing_id,)) duplicate_guard = False try: existing.execute("""INSERT INTO publishing_jobs (id, listing_id, platform, mode, action, status, phase, idempotency_key) VALUES ('pub-job-2', ?, 'ebay-de', 'api', 'publish', 'queued', 'queued', 'key-1')""", (listing_id,)) except sqlite3.IntegrityError: duplicate_guard = True if not duplicate_guard: raise AssertionError('Idempotency-Key verhindert keinen doppelten Auftrag') existing.execute("UPDATE publishing_jobs SET status='running', phase='publish' WHERE id='pub-job-1'") existing.execute("UPDATE publishing_jobs SET status='queued', phase='recovered' WHERE status='running'") recovered = existing.execute("SELECT status, phase FROM publishing_jobs WHERE id='pub-job-1'").fetchone() if recovered != ('queued', 'recovered'): raise AssertionError(f'Publishing-Recovery fehlgeschlagen: {recovered!r}') existing.execute("INSERT INTO operation_settings (key,value) VALUES ('auto_backup_enabled','1')") existing.execute("INSERT INTO operation_backups (id,kind,file_name,file_path,status) VALUES ('backup-1','manual','test.zip','/tmp/test.zip','verified')") existing.execute("INSERT INTO update_operations (id,version_from,version_to,archive_path,status) VALUES ('update-1','1.29.0','1.30.0','/tmp/update.zip','staged')") existing.execute("INSERT INTO extension_clients (client_id,browser,version,platform) VALUES ('client-1','chrome','1.9.0','popup')") migration = existing.execute("SELECT app_version FROM system_migrations WHERE migration_key='operations-center-1.30.0'").fetchone() if migration != ('1.30.0',): raise AssertionError(f'Operations-Migration fehlt: {migration!r}') extension = existing.execute("SELECT browser,version FROM extension_clients WHERE client_id='client-1'").fetchone() if extension != ('chrome','1.9.0'): raise AssertionError(f'Extension-Client fehlerhaft: {extension!r}') print(json.dumps({ 'ok': True, 'fresh_db_exec_blocks': fresh_blocks, 'existing_db_exec_blocks': existing_blocks, 'existing_row_preserved': True, 'publishing_hardening': { 'tables': ['publishing_jobs','publishing_events'], 'idempotency_key_unique': True, 'restart_recovery': True, 'events_persisted': True, }, 'operations_center': {'backup_tables': True, 'update_tables': True, 'extension_heartbeat': True, 'migration_recorded': True}, 'tables': ['flux_images','flux_prompt_jobs','flux_prompt_job_variants','image_edit_versions','image_batch_profiles','image_batch_jobs','image_batch_job_items','quality_reports','publishing_jobs','publishing_events','operation_backups','operation_settings','update_operations','system_migrations','extension_clients','theme_profiles','user_theme_preferences'], }, indent=2))