89 lines
2.6 KiB
Python
89 lines
2.6 KiB
Python
|
#!/usr/bin/env python3
|
||
|
|
"""Migrate: Create FTS5 virtual table for full-text search.
|
||
|
|
|
||
|
|
Usage:
|
||
|
|
python scripts/migrate_fts5.py [path/to/tracker.db]
|
||
|
|
"""
|
||
|
|
import sqlite3
|
||
|
|
import sys
|
||
|
|
from pathlib import Path
|
||
|
|
|
||
|
|
|
||
|
|
def migrate(db_path: str):
|
||
|
|
conn = sqlite3.connect(db_path)
|
||
|
|
conn.execute("PRAGMA journal_mode = WAL")
|
||
|
|
|
||
|
|
# Check if FTS table already exists
|
||
|
|
existing = conn.execute(
|
||
|
|
"SELECT name FROM sqlite_master WHERE type='table' AND name='vorlagen_fts'"
|
||
|
|
).fetchone()
|
||
|
|
if existing:
|
||
|
|
print("⚠️ vorlagen_fts existiert bereits — wird neu aufgebaut")
|
||
|
|
conn.execute("DROP TABLE IF EXISTS vorlagen_fts")
|
||
|
|
|
||
|
|
# Create FTS5 table (content-sync'd with vorlagen + ki_bewertungen)
|
||
|
|
conn.execute("""
|
||
|
|
CREATE VIRTUAL TABLE vorlagen_fts USING fts5(
|
||
|
|
aktenzeichen,
|
||
|
|
betreff,
|
||
|
|
volltext,
|
||
|
|
zusammenfassung,
|
||
|
|
content='',
|
||
|
|
contentless_delete=1,
|
||
|
|
tokenize='unicode61 remove_diacritics 2'
|
||
|
|
)
|
||
|
|
""")
|
||
|
|
|
||
|
|
# Populate from vorlagen + ki_bewertungen
|
||
|
|
cursor = conn.execute("""
|
||
|
|
SELECT v.id, v.aktenzeichen, v.betreff, v.volltext_clean,
|
||
|
|
kb.begruendung as zusammenfassung
|
||
|
|
FROM vorlagen v
|
||
|
|
LEFT JOIN ki_bewertungen kb ON kb.vorlage_id = v.id AND kb.typ = 'zusammenfassung'
|
||
|
|
""")
|
||
|
|
|
||
|
|
batch = []
|
||
|
|
count = 0
|
||
|
|
for row in cursor:
|
||
|
|
batch.append((
|
||
|
|
row[0], # rowid = vorlage.id
|
||
|
|
row[1] or '', # aktenzeichen
|
||
|
|
row[2] or '', # betreff
|
||
|
|
row[3] or '', # volltext
|
||
|
|
row[4] or '', # zusammenfassung
|
||
|
|
))
|
||
|
|
if len(batch) >= 1000:
|
||
|
|
conn.executemany(
|
||
|
|
"INSERT INTO vorlagen_fts(rowid, aktenzeichen, betreff, volltext, zusammenfassung) VALUES (?, ?, ?, ?, ?)",
|
||
|
|
batch
|
||
|
|
)
|
||
|
|
count += len(batch)
|
||
|
|
print(f" {count} Vorlagen indexiert...")
|
||
|
|
batch = []
|
||
|
|
|
||
|
|
if batch:
|
||
|
|
conn.executemany(
|
||
|
|
"INSERT INTO vorlagen_fts(rowid, aktenzeichen, betreff, volltext, zusammenfassung) VALUES (?, ?, ?, ?, ?)",
|
||
|
|
batch
|
||
|
|
)
|
||
|
|
count += len(batch)
|
||
|
|
|
||
|
|
conn.commit()
|
||
|
|
print(f"✅ FTS5-Index erstellt: {count} Vorlagen indexiert")
|
||
|
|
|
||
|
|
# Test
|
||
|
|
test = conn.execute(
|
||
|
|
"SELECT COUNT(*) FROM vorlagen_fts WHERE vorlagen_fts MATCH 'Klimaschutz'"
|
||
|
|
).fetchone()[0]
|
||
|
|
print(f" Test 'Klimaschutz': {test} Treffer")
|
||
|
|
|
||
|
|
conn.close()
|
||
|
|
|
||
|
|
|
||
|
|
if __name__ == "__main__":
|
||
|
|
db = sys.argv[1] if len(sys.argv) > 1 else str(
|
||
|
|
Path(__file__).resolve().parents[1] / "data" / "tracker.db"
|
||
|
|
)
|
||
|
|
print(f"🔄 FTS5-Migration: {db}")
|
||
|
|
migrate(db)
|