#!/usr/bin/env python3 """ USLaP Schema Consolidation — Final Clean Script Reduces 186 tables → ~130 by merging RU duplicates into EN tables and dropping retired tables. Strategy: Phase 1: Backup triggers + views SQL, then drop all views Phase 2: Names of Allah — add RU columns to EN table, UPDATE from RU Phase 3: A3/A4/A5/A6 — add lang column to EN, INSERT RU rows with new PKs Phase 4: Foundation F1-F7 — add lang column, INSERT RU rows Phase 5: Mechanism M1-M5 — add lang column, INSERT RU rows Phase 6: Drop retired tables Phase 7: Recreate views with correct definitions Phase 8: Restore contamination triggers """ import sqlite3, os, sys, json from datetime import datetime DB = os.path.join(os.path.dirname(__file__), "uslap_database_v3.db") # Column mappings: RU column → EN column (for Foundation/Mechanism merges) F1_MAP = [ ("ориг_id", "orig_id"), ("название", "name"), ("арабское_имя", "arabic_name"), ("авторитет", "authority"), ("буквы", "letters"), ("тип_письма", "script_type"), ("самоназвание", "self_naming"), ("коран_ссылка", "qur_ref"), ("тип_доказательства", "proof_type"), ] F2_DS_MAP = [ ("письмо_id", "script_id"), ("название", "name"), ("источник", "source"), ("дата", "date"), ("статус", "status"), ("механизм", "mechanism"), ("доказательства", "evidence"), ("уровень_деградации", "decay_level"), ("ссылка", "lattice_ref"), ] F2_DC_MAP = [ ("уровень", "level"), ("критерии", "criteria"), ("пример_ds", "example_ds"), ("измеримый_тест", "measurable_test"), ] F3_MAP = [ ("стадия", "stage"), ("эволюция_амб", "asb_evolution"), ("восточная_граница", "eastern_terminus"), ("значение", "meaning"), ("статус", "status"), ] F4_MAP = [ ("ранг", "rank"), ("язык", "language"), ("пример", "example"), ("морфемы", "morphemes"), ("русский_эквивалент", "english_equiv"), ("расстояние_от_источника", "distance_from_source"), ("тип", "type"), ] F5_MAP = [ ("дата", "date"), ("уничтожитель", "destroyer"), ("что_произошло", "what_happened"), ("что_утрачено", "what_lost"), ("чем_заменено", "replaced_with"), ("примечание_uslap", "uslap_note"), ] F6_MAP = [ ("свид_id", "evidence_id"), ("тип", "type"), ("местоположение", "location"), ("расст_мин_км", "distance_min_km"), ("расст_макс_км", "distance_max_km"), ("дата", "date"), ("описание", "description"), ("контаминация", "contamination"), ("ссылка", "lattice_ref"), ] F7_MAP = [ ("домен", "domain"), ("реальный_поток", "actual_flow"), ("перевёрнутое_утверждение", "reversed_claim"), ("опровержение", "refutation"), ("ссылка", "lattice_ref"), ] M5_MAP = [ ("кв_id", "qv_id"), ("название", "name"), ("механизм", "mechanism"), ("описание", "description"), ("маркеры", "markers"), ("коран_ссылки", "qur_refs"), ("контрастные_ссылки", "contrast_refs"), ("основание", "foundation_ref"), ] # M1 RU has different schema than shift_lookup — it's a full descriptions table # M2, M3, M4 have no EN base tables (phonetic_shifts, detection_patterns, scholars, networks don't exist) # These RU tables ARE the only copy of the data — keep them as-is but rename to EN names FOUNDATION_MERGES = [ ("f1_один_источник_два_коридора", "f1_two_originals", F1_MAP), ("f2_нисходящий_поток", "f2_script_downstream", F2_DS_MAP), ("f2_критерии_деградации", "f2_decay_criteria", F2_DC_MAP), ("f3_модель_застывших_стадий", "f3_frozen_stage_model", F3_MAP), ("f4_градиент_деградации", "f4_decay_gradient", F4_MAP), ("f5_хронология_уничтожения", "f5_destruction_timeline", F5_MAP), ("f6_рукописные_свидетельства", "f6_manuscript_evidence", F6_MAP), ("f7_направление_потока", "f7_direction_of_flow", F7_MAP), ] M5_MERGE = ("m5_коранич_верификация", "m5_qur_verification", M5_MAP) # RU tables to rename (no EN counterpart exists) RU_RENAME = { "m1_фонетические_сдвиги": "m1_shift_descriptions", "m2_паттерны_обнаружения": "m2_detection_patterns", "m3_учёные": "m3_scholars", "m4_сети": "m4_networks", } # Retired tables to drop RETIRED = [ "_retired_xlsx_att_terms", "_retired_xlsx_child_schema", "_retired_xlsx_dp_register", "_retired_xlsx_phonetic_reversal", "_retired_xlsx_protocol_corrections", "_retired_xlsx_scholar_warnings", "_retired_xlsx_session_index", "_retired_xlsx_umd_operations", ] def run(dry=False): conn = sqlite3.connect(DB) conn.execute("PRAGMA foreign_keys = OFF;") cur = conn.cursor() dropped_tables = [] # ─── Phase 1: Save and drop ALL views + ALL triggers ─── print("\n=== Phase 1: Drop all views and triggers ===") views = cur.execute("SELECT name, sql FROM sqlite_master WHERE type='view' ORDER BY name").fetchall() triggers = cur.execute("SELECT name, sql, tbl_name FROM sqlite_master WHERE type='trigger' ORDER BY name").fetchall() print(f" Saving {len(views)} views, {len(triggers)} triggers...") if not dry: for vname, vsql in views: cur.execute(f'DROP VIEW IF EXISTS [{vname}]') print(f" Dropped {len(views)} views") for tname, tsql, ttbl in triggers: cur.execute(f'DROP TRIGGER IF EXISTS [{tname}]') print(f" Dropped {len(triggers)} triggers") else: print(f" [DRY] Would drop {len(views)} views and {len(triggers)} triggers") # ─── Phase 2: Names of Allah — add RU columns ─── print("\n=== Phase 2: Names of Allah — merge RU into EN ===") # RU a2 has: имя_id, арабское_имя, транслитерация, значение, коран_ссылка, записи_id, корень_id + QUF # EN has: allah_id, arabic_name, transliteration, meaning, qur_ref, entry_ids, root_id + QUF # Add ru_meaning, ru_transliteration to EN table ru_names = cur.execute("SELECT имя_id, транслитерация, значение FROM a2_имена_аллаха").fetchall() print(f" RU Names: {len(ru_names)} rows") if not dry: try: cur.execute("ALTER TABLE names_of_allah ADD COLUMN ru_transliteration TEXT") print(" Added ru_transliteration column") except sqlite3.OperationalError: print(" ru_transliteration column already exists") try: cur.execute("ALTER TABLE names_of_allah ADD COLUMN ru_meaning TEXT") print(" Added ru_meaning column") except sqlite3.OperationalError: print(" ru_meaning column already exists") updated = 0 for имя_id, translit, meaning in ru_names: cur.execute("""UPDATE names_of_allah SET ru_transliteration = ?, ru_meaning = ? WHERE allah_id = ?""", (translit, meaning, имя_id)) if cur.rowcount > 0: updated += 1 print(f" Updated {updated}/{len(ru_names)} names") cur.execute("DROP TABLE IF EXISTS a2_имена_аллаха") dropped_tables.append("a2_имена_аллаха") print(" Dropped a2_имена_аллаха") else: print(f" [DRY] Would add ru_transliteration, ru_meaning columns and update {len(ru_names)} rows") # ─── Phase 3: A3/A4/A5/A6 — add lang column, INSERT RU rows ─── print("\n=== Phase 3: A3-A6 — merge RU into EN ===") # A3: quran_refs (EN=226 rows) ← a3_коранические_ссылки (RU=32 rows) # RU cols: ссылка_id, сура, аят, арабский_текст, релевантность, записи_id, сеть_id, слой, кв_id, quf* # EN cols: ref_id, surah, ayah, arabic_text, transliteration, translation, relevance, entry_ids, network_id, layer_ref, qv_id, created_at, quf* a3_ru = cur.execute("""SELECT ссылка_id, сура, аят, арабский_текст, релевантность, записи_id, сеть_id, слой, кв_id, quf_q, quf_u, quf_f, quf_pass, quf_date FROM a3_коранические_ссылки""").fetchall() print(f" A3 quran_refs: EN={cur.execute('SELECT COUNT(*) FROM quran_refs').fetchone()[0]}, RU={len(a3_ru)}") if not dry: try: cur.execute("ALTER TABLE quran_refs ADD COLUMN lang TEXT DEFAULT 'EN'") print(" Added lang column to quran_refs") except sqlite3.OperationalError: print(" lang column already exists on quran_refs") max_pk = cur.execute("SELECT MAX(CAST(REPLACE(ref_id,'QR','') AS INTEGER)) FROM quran_refs").fetchone()[0] or 0 inserted = 0 for row in a3_ru: max_pk += 1 new_pk = f"QR{max_pk:04d}" # Map: сура→surah, аят→ayah, арабский_текст→arabic_text, релевантность→relevance, # записи_id→entry_ids, сеть_id→network_id, слой→layer_ref, кв_id→qv_id vals = (new_pk, row[1], row[2], row[3], None, None, row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], "RU") try: cur.execute("""INSERT INTO quran_refs (ref_id, surah, ayah, arabic_text, transliteration, translation, relevance, entry_ids, network_id, layer_ref, qv_id, quf_q, quf_u, quf_f, quf_pass, quf_date, lang) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""", vals) inserted += 1 except Exception as e: print(f" Skip A3 row {row[0]}: {e}") print(f" Inserted {inserted} RU rows into quran_refs") cur.execute("DROP TABLE IF EXISTS a3_коранические_ссылки") dropped_tables.append("a3_коранические_ссылки") else: print(f" [DRY] Would merge {len(a3_ru)} RU rows into quran_refs") # A4: a4_derivatives (EN=6293) ← a4_производные (RU=171) a4_ru = cur.execute("SELECT * FROM a4_производные").fetchall() print(f" A4 derivatives: EN={cur.execute('SELECT COUNT(*) FROM a4_derivatives').fetchone()[0]}, RU={len(a4_ru)}") if not dry: try: cur.execute("ALTER TABLE a4_derivatives ADD COLUMN lang TEXT DEFAULT 'EN'") print(" Added lang column to a4_derivatives") except sqlite3.OperationalError: print(" lang column already exists on a4_derivatives") max_d = cur.execute("SELECT MAX(CAST(REPLACE(deriv_id,'D','') AS INTEGER)) FROM a4_derivatives").fetchone()[0] or 0 inserted = 0 for row in a4_ru: max_d += 1 new_pk = f"D{max_d:05d}" # RU schema: произв_id, запись_id, рус_термин, производное, тип_связи, quf_q..quf_date # EN schema: deriv_id, entry_id, en_term, derivative, link_type, col_5, col_6, quf_token, quf_q..quf_date ru_data = list(row[1:]) # skip RU PK # Map: запись_id→entry_id, рус_термин→en_term, производное→derivative, тип_связи→link_type en_vals = [ new_pk, # deriv_id ru_data[0], # entry_id (запись_id) ru_data[1], # en_term (рус_термин) ru_data[2], # derivative (производное) ru_data[3], # link_type (тип_связи) None, # col_5 None, # col_6 None, # quf_token ru_data[4] if len(ru_data) > 4 else None, # quf_q ru_data[5] if len(ru_data) > 5 else None, # quf_u ru_data[6] if len(ru_data) > 6 else None, # quf_f ru_data[7] if len(ru_data) > 7 else None, # quf_pass ru_data[8] if len(ru_data) > 8 else None, # quf_date "RU", # lang ] try: cur.execute("""INSERT INTO a4_derivatives (deriv_id, entry_id, en_term, derivative, link_type, col_5, col_6, quf_token, quf_q, quf_u, quf_f, quf_pass, quf_date, lang) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)""", en_vals) inserted += 1 except Exception as e: print(f" Skip A4 row {row[0]}: {e}") print(f" Inserted {inserted} RU rows into a4_derivatives") cur.execute("DROP TABLE IF EXISTS a4_производные") dropped_tables.append("a4_производные") else: print(f" [DRY] Would merge {len(a4_ru)} RU rows into a4_derivatives") # A5: a5_cross_refs (EN=892) ← a5_перекрёстные_ссылки (RU=184) a5_ru = cur.execute("SELECT * FROM a5_перекрёстные_ссылки").fetchall() print(f" A5 cross_refs: EN={cur.execute('SELECT COUNT(*) FROM a5_cross_refs').fetchone()[0]}, RU={len(a5_ru)}") if not dry: try: cur.execute("ALTER TABLE a5_cross_refs ADD COLUMN lang TEXT DEFAULT 'EN'") print(" Added lang column to a5_cross_refs") except sqlite3.OperationalError: print(" lang column already exists on a5_cross_refs") max_x = cur.execute("SELECT MAX(CAST(REPLACE(xref_id,'X','') AS INTEGER)) FROM a5_cross_refs").fetchone()[0] or 0 inserted = 0 for row in a5_ru: max_x += 1 new_pk = f"X{max_x:05d}" ru_data = list(row[1:]) # skip RU PK # RU: перекр_id, от_id, к_id, тип_связи, описание, слой, quf_q..quf_date # EN: xref_id, from_id, to_id, link_type, description, layer_ref, col_6, # from_entry_id, from_bitig_id, from_child_id, from_term, # to_entry_id, to_bitig_id, to_child_id, to_term, quf_q..quf_date en_vals = [ new_pk, # xref_id ru_data[0], # from_id ru_data[1], # to_id ru_data[2], # link_type ru_data[3], # description ru_data[4], # layer_ref None, # col_6 None, None, None, None, # from_entry_id..from_term None, None, None, None, # to_entry_id..to_term ru_data[5] if len(ru_data) > 5 else None, # quf_q ru_data[6] if len(ru_data) > 6 else None, # quf_u ru_data[7] if len(ru_data) > 7 else None, # quf_f ru_data[8] if len(ru_data) > 8 else None, # quf_pass ru_data[9] if len(ru_data) > 9 else None, # quf_date "RU", # lang ] try: cur.execute("""INSERT INTO a5_cross_refs (xref_id, from_id, to_id, link_type, description, layer_ref, col_6, from_entry_id, from_bitig_id, from_child_id, from_term, to_entry_id, to_bitig_id, to_child_id, to_term, quf_q, quf_u, quf_f, quf_pass, quf_date, lang) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""", en_vals) inserted += 1 except Exception as e: print(f" Skip A5 row {row[0]}: {e}") print(f" Inserted {inserted} RU rows into a5_cross_refs") cur.execute("DROP TABLE IF EXISTS a5_перекрёстные_ссылки") dropped_tables.append("a5_перекрёстные_ссылки") else: print(f" [DRY] Would merge {len(a5_ru)} RU rows into a5_cross_refs") # A6: country_names (EN=46) ← a6_названия_стран (RU=3) # RU: страна_id, название_страны, корень_al, корень_id, слово_al, коранич_значение, # фонетическая_цепь, основание_названия, запись_ids, примечания, quf* # EN: country_id, country_name, al_root, root_id, al_word, qur_meaning, # phonetic_chain, naming_basis, entry_ids, notes, created_at, quf* a6_ru = cur.execute("""SELECT страна_id, название_страны, корень_al, корень_id, слово_al, коранич_значение, фонетическая_цепь, основание_названия, запись_ids, примечания, quf_q, quf_u, quf_f, quf_pass, quf_date FROM a6_названия_стран""").fetchall() print(f" A6 country_names: EN={cur.execute('SELECT COUNT(*) FROM country_names').fetchone()[0]}, RU={len(a6_ru)}") if not dry: try: cur.execute("ALTER TABLE country_names ADD COLUMN lang TEXT DEFAULT 'EN'") print(" Added lang column to country_names") except sqlite3.OperationalError: print(" lang column already exists on country_names") max_c = cur.execute("SELECT MAX(CAST(REPLACE(country_id,'CN','') AS INTEGER)) FROM country_names").fetchone()[0] or 0 inserted = 0 for row in a6_ru: max_c += 1 new_pk = f"CN{max_c:03d}" vals = (new_pk, row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], "RU") try: cur.execute("""INSERT INTO country_names (country_id, country_name, al_root, root_id, al_word, qur_meaning, phonetic_chain, naming_basis, entry_ids, notes, quf_q, quf_u, quf_f, quf_pass, quf_date, lang) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""", vals) inserted += 1 except Exception as e: print(f" Skip A6 row {row[0]}: {e}") print(f" Inserted {inserted} RU rows into country_names") cur.execute("DROP TABLE IF EXISTS a6_названия_стран") dropped_tables.append("a6_названия_стран") else: print(f" [DRY] Would merge {len(a6_ru)} RU rows into country_names") # ─── Phase 4: Foundation F1-F7 — add lang column, INSERT RU rows ─── print("\n=== Phase 4: Foundation F1-F7 — merge RU into EN ===") for ru_table, en_table, col_map in FOUNDATION_MERGES: ru_count = cur.execute(f"SELECT COUNT(*) FROM [{ru_table}]").fetchone()[0] en_count = cur.execute(f"SELECT COUNT(*) FROM [{en_table}]").fetchone()[0] print(f" {en_table}: EN={en_count}, RU={ru_count}") if not dry: try: cur.execute(f"ALTER TABLE [{en_table}] ADD COLUMN lang TEXT DEFAULT 'EN'") except sqlite3.OperationalError: pass # Get RU data ru_cols = [m[0] for m in col_map] en_cols = [m[1] for m in col_map] ru_col_str = ", ".join([f"[{c}]" for c in ru_cols]) rows = cur.execute(f"SELECT {ru_col_str}, quf_q, quf_u, quf_f, quf_pass, quf_date FROM [{ru_table}]").fetchall() en_col_str = ", ".join(en_cols) + ", quf_q, quf_u, quf_f, quf_pass, quf_date, lang" placeholders = ", ".join(["?"] * (len(en_cols) + 6)) inserted = 0 for row in rows: vals = list(row) + ["RU"] try: cur.execute(f"INSERT INTO [{en_table}] ({en_col_str}) VALUES ({placeholders})", vals) inserted += 1 except Exception as e: print(f" Skip {ru_table} row: {e}") print(f" Merged {inserted} RU rows → {en_table}") cur.execute(f"DROP TABLE IF EXISTS [{ru_table}]") dropped_tables.append(ru_table) else: print(f" [DRY] Would merge {ru_count} RU rows into {en_table}") # ─── Phase 5: Mechanism M1-M5 ─── print("\n=== Phase 5: Mechanism tables ===") # M5: merge like Foundation ru_table, en_table, col_map = M5_MERGE ru_count = cur.execute(f"SELECT COUNT(*) FROM [{ru_table}]").fetchone()[0] en_count = cur.execute(f"SELECT COUNT(*) FROM [{en_table}]").fetchone()[0] print(f" {en_table}: EN={en_count}, RU={ru_count}") if not dry: try: cur.execute(f"ALTER TABLE [{en_table}] ADD COLUMN lang TEXT DEFAULT 'EN'") except sqlite3.OperationalError: pass ru_cols = [m[0] for m in col_map] en_cols = [m[1] for m in col_map] ru_col_str = ", ".join([f"[{c}]" for c in ru_cols]) rows = cur.execute(f"SELECT {ru_col_str}, quf_q, quf_u, quf_f, quf_pass, quf_date FROM [{ru_table}]").fetchall() en_col_str = ", ".join(en_cols) + ", quf_q, quf_u, quf_f, quf_pass, quf_date, lang" placeholders = ", ".join(["?"] * (len(en_cols) + 6)) inserted = 0 for row in rows: vals = list(row) + ["RU"] try: cur.execute(f"INSERT INTO [{en_table}] ({en_col_str}) VALUES ({placeholders})", vals) inserted += 1 except Exception as e: print(f" Skip {ru_table} row: {e}") print(f" Merged {inserted} RU rows → {en_table}") cur.execute(f"DROP TABLE IF EXISTS [{ru_table}]") dropped_tables.append(ru_table) else: print(f" [DRY] Would merge {ru_count} RU rows into {en_table}") # M1-M4: rename RU tables to EN names (no EN counterpart exists) for ru_name, en_name in RU_RENAME.items(): count = cur.execute(f"SELECT COUNT(*) FROM [{ru_name}]").fetchone()[0] print(f" Rename [{ru_name}] → [{en_name}] ({count} rows)") if not dry: # Check if target already exists exists = cur.execute(f"SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?", (en_name,)).fetchone()[0] if exists: print(f" Target {en_name} already exists — adding lang column and merging") try: cur.execute(f"ALTER TABLE [{en_name}] ADD COLUMN lang TEXT DEFAULT 'EN'") except sqlite3.OperationalError: pass # Can't easily merge different schemas — just keep both for now print(f" Skipping merge (different schemas) — keeping [{ru_name}] as-is") else: cur.execute(f"ALTER TABLE [{ru_name}] RENAME TO [{en_name}]") print(f" Renamed successfully") # ─── Phase 6: Drop retired tables ─── print("\n=== Phase 6: Drop retired tables ===") for tbl in RETIRED: if not dry: cur.execute(f"DROP TABLE IF EXISTS [{tbl}]") dropped_tables.append(tbl) print(f" Dropped {tbl}") else: print(f" [DRY] Would drop {tbl}") # Also drop _qwr_new if empty qwr_count = cur.execute("SELECT COUNT(*) FROM _qwr_new").fetchone()[0] if qwr_count == 0: if not dry: cur.execute("DROP TABLE IF EXISTS _qwr_new") dropped_tables.append("_qwr_new") print(f" Dropped _qwr_new (empty)") else: print(f" [DRY] Would drop _qwr_new (empty)") # ─── Phase 7: Recreate views ─── print("\n=== Phase 7: Recreate views ===") view_defs = { "a1_entries": """CREATE VIEW a1_entries AS SELECT entry_id, score, en_term, ru_term, fa_term, ar_word, root_id, root_letters, qur_refs, pattern, inversion_type, network_id, allah_name_id, phonetic_chain, source_form, ds_corridor, decay_level, dp_codes, ops_applied, foundation_refs, notes, qur_meaning, quf_q, quf_u, quf_f, quf_pass FROM entries""", "a1_записи": """CREATE VIEW [a1_записи] AS SELECT entry_id AS запись_id, score AS балл, ru_term AS рус_термин, ar_word AS ар_слово, root_id AS корень_id, root_letters AS корневые_буквы, qur_meaning AS коранич_значение, pattern AS паттерн, allah_name_id AS имя_аллаха_id, network_id AS сеть_id, phonetic_chain AS фонетическая_цепь, inversion_type AS тип_инверсии, source_form AS исходная_форма, foundation_refs AS основание, quf_q, quf_u, quf_f, quf_pass, quf_date, quf_token FROM entries WHERE ru_term IS NOT NULL""", "a2_names_of_allah": "CREATE VIEW a2_names_of_allah AS SELECT * FROM names_of_allah", "a3_quran_refs": "CREATE VIEW a3_quran_refs AS SELECT * FROM quran_refs", "a6_country_names": "CREATE VIEW a6_country_names AS SELECT * FROM country_names", "child_schema": """CREATE VIEW child_schema AS SELECT child_id AS entry_id, shell_name, shell_language, orig_class, orig_root, orig_lemma, orig_meaning, operation_role, shell_meaning, inversion_direction, phonetic_chain, qur_anchors, dp_codes, nt_code, pattern, parent_op, gate_status, notes FROM child_entries""", # Fix m1_phonetic_shifts — point to shift_lookup (the actual table) "m1_phonetic_shifts": "CREATE VIEW m1_phonetic_shifts AS SELECT * FROM shift_lookup", # m2-m4: point to the newly renamed tables "m2_detection_patterns": None, # Will check if table exists "m3_scholars": None, "m4_networks": None, "persian_a1_mad_khil": """CREATE VIEW persian_a1_mad_khil AS SELECT entry_id, fa_term, ar_word, root_letters, qur_meaning, pattern, allah_name_id, network_id, phonetic_chain, inversion_type, source_form, foundation_refs, score, root_id, quf_q, quf_u, quf_f, quf_pass, quf_date, quf_token FROM entries WHERE fa_term IS NOT NULL""", } if not dry: for vname, vsql in view_defs.items(): if vsql is None: # Check if the table exists to create a pass-through view # m2_detection_patterns → check if renamed table exists if vname == "m2_detection_patterns": exists = cur.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='m2_detection_patterns'").fetchone()[0] if exists: print(f" {vname} is now a TABLE — no view needed") continue elif vname == "m3_scholars": exists = cur.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='m3_scholars'").fetchone()[0] if exists: print(f" {vname} is now a TABLE — no view needed") continue elif vname == "m4_networks": exists = cur.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='m4_networks'").fetchone()[0] if exists: print(f" {vname} is now a TABLE — no view needed") continue print(f" Skipping {vname} — no valid target") continue try: cur.execute(vsql) print(f" Created view: {vname}") except Exception as e: print(f" ERROR creating view {vname}: {e}") else: for vname in view_defs: print(f" [DRY] Would create view: {vname}") # ─── Phase 8: Restore triggers ─── print("\n=== Phase 8: Restore triggers ===") if not dry: # Get current table names to filter triggers current_tables = set(r[0] for r in cur.execute( "SELECT name FROM sqlite_master WHERE type='table'").fetchall()) restored = 0 skipped = 0 errors = 0 for tname, tsql, ttbl in triggers: if tsql is None: skipped += 1 continue # Skip triggers for dropped tables if ttbl not in current_tables: skipped += 1 continue # Fix triggers that reference a1_entries (now a view that doesn't exist yet) # These will be recreated after views are recreated if 'a1_entries' in tsql and 'a1_entries' not in current_tables: # Replace a1_entries reference with entries table tsql = tsql.replace('a1_entries', 'entries') # Fix triggers with NEW.relationship (column doesn't exist) if 'NEW.relationship' in tsql: skipped += 1 continue try: cur.execute(tsql) restored += 1 except Exception as e: errors += 1 if errors <= 5: print(f" Trigger error {tname}: {e}") print(f" Restored {restored} triggers, skipped {skipped}, errors {errors}") else: print(f" [DRY] Would restore valid triggers from {len(triggers)} saved") # ─── Phase 9: Commit and verify ─── print("\n=== Phase 9: Verify ===") if not dry: conn.commit() final_tables = cur.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table'").fetchone()[0] final_views = cur.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='view'").fetchone()[0] final_triggers = cur.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='trigger'").fetchone()[0] print(f" Tables: 186 → {final_tables} (dropped {len(dropped_tables)})") print(f" Views: {final_views}") print(f" Triggers: {final_triggers}") print(f"\n Dropped tables: {', '.join(dropped_tables)}") # Verify key table counts checks = [ ("entries", None), ("names_of_allah", None), ("a4_derivatives", None), ("a5_cross_refs", None), ("quran_refs", None), ("country_names", None), ] print("\n Row counts:") for tbl, _ in checks: try: count = cur.execute(f"SELECT COUNT(*) FROM [{tbl}]").fetchone()[0] print(f" {tbl}: {count}") except: print(f" {tbl}: ERROR") else: print(" [DRY RUN COMPLETE — no changes made]") conn.close() print("\nDone.") if __name__ == "__main__": dry = "--dry" in sys.argv if dry: print("=== DRY RUN ===") else: print("=== LIVE RUN ===") run(dry=dry)