45 lines
1.5 KiB
Python
45 lines
1.5 KiB
Python
|
#!/usr/bin/env python3
|
||
|
|
"""Migration: Erstellt die fristen-Tabelle für Fristen-Tracking (Issue #17)."""
|
||
|
|
|
||
|
|
import sqlite3
|
||
|
|
import sys
|
||
|
|
from pathlib import Path
|
||
|
|
|
||
|
|
DB_PATH = Path(__file__).resolve().parent.parent / "data" / "tracker.db"
|
||
|
|
|
||
|
|
|
||
|
|
def migrate(db_path: Path = DB_PATH):
|
||
|
|
conn = sqlite3.connect(str(db_path))
|
||
|
|
conn.execute("PRAGMA journal_mode = WAL")
|
||
|
|
conn.execute("PRAGMA foreign_keys = ON")
|
||
|
|
|
||
|
|
conn.execute("""
|
||
|
|
CREATE TABLE IF NOT EXISTS fristen (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
kette_id INTEGER REFERENCES ketten(id),
|
||
|
|
vorlage_id INTEGER REFERENCES vorlagen(id),
|
||
|
|
typ TEXT NOT NULL,
|
||
|
|
datum DATE NOT NULL,
|
||
|
|
beschreibung TEXT,
|
||
|
|
quelle TEXT DEFAULT 'manuell',
|
||
|
|
status TEXT DEFAULT 'offen',
|
||
|
|
erstellt_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
aktualisiert_at TIMESTAMP
|
||
|
|
)
|
||
|
|
""")
|
||
|
|
|
||
|
|
# Indices for common queries
|
||
|
|
conn.execute("CREATE INDEX IF NOT EXISTS idx_fristen_kette_id ON fristen(kette_id)")
|
||
|
|
conn.execute("CREATE INDEX IF NOT EXISTS idx_fristen_status ON fristen(status)")
|
||
|
|
conn.execute("CREATE INDEX IF NOT EXISTS idx_fristen_datum ON fristen(datum)")
|
||
|
|
conn.execute("CREATE INDEX IF NOT EXISTS idx_fristen_status_datum ON fristen(status, datum)")
|
||
|
|
|
||
|
|
conn.commit()
|
||
|
|
conn.close()
|
||
|
|
print(f"✅ Migration erfolgreich: fristen-Tabelle in {db_path}")
|
||
|
|
|
||
|
|
|
||
|
|
if __name__ == "__main__":
|
||
|
|
path = Path(sys.argv[1]) if len(sys.argv) > 1 else DB_PATH
|
||
|
|
migrate(path)
|