Initialize SQLite database with optimized schema and indexes.
(self)
| 25 | self.init_database() |
| 26 | |
| 27 | def init_database(self): |
| 28 | """Initialize SQLite database with optimized schema and indexes.""" |
| 29 | conn = sqlite3.connect(self.db_path) |
| 30 | conn.execute("PRAGMA journal_mode=WAL") # Write-ahead logging for performance |
| 31 | conn.execute("PRAGMA synchronous=NORMAL") |
| 32 | conn.execute("PRAGMA cache_size=10000") |
| 33 | conn.execute("PRAGMA temp_store=MEMORY") |
| 34 | |
| 35 | # Create main workflows table |
| 36 | conn.execute(""" |
| 37 | CREATE TABLE IF NOT EXISTS workflows ( |
| 38 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 39 | filename TEXT UNIQUE NOT NULL, |
| 40 | name TEXT NOT NULL, |
| 41 | workflow_id TEXT, |
| 42 | active BOOLEAN DEFAULT 0, |
| 43 | description TEXT, |
| 44 | trigger_type TEXT, |
| 45 | complexity TEXT, |
| 46 | node_count INTEGER DEFAULT 0, |
| 47 | integrations TEXT, -- JSON array |
| 48 | tags TEXT, -- JSON array |
| 49 | created_at TEXT, |
| 50 | updated_at TEXT, |
| 51 | file_hash TEXT, |
| 52 | file_size INTEGER, |
| 53 | analyzed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP |
| 54 | ) |
| 55 | """) |
| 56 | |
| 57 | # Create FTS5 table for full-text search |
| 58 | conn.execute(""" |
| 59 | CREATE VIRTUAL TABLE IF NOT EXISTS workflows_fts USING fts5( |
| 60 | filename, |
| 61 | name, |
| 62 | description, |
| 63 | integrations, |
| 64 | tags, |
| 65 | content=workflows, |
| 66 | content_rowid=id |
| 67 | ) |
| 68 | """) |
| 69 | |
| 70 | # Create indexes for fast filtering |
| 71 | conn.execute( |
| 72 | "CREATE INDEX IF NOT EXISTS idx_trigger_type ON workflows(trigger_type)" |
| 73 | ) |
| 74 | conn.execute( |
| 75 | "CREATE INDEX IF NOT EXISTS idx_complexity ON workflows(complexity)" |
| 76 | ) |
| 77 | conn.execute("CREATE INDEX IF NOT EXISTS idx_active ON workflows(active)") |
| 78 | conn.execute( |
| 79 | "CREATE INDEX IF NOT EXISTS idx_node_count ON workflows(node_count)" |
| 80 | ) |
| 81 | conn.execute("CREATE INDEX IF NOT EXISTS idx_filename ON workflows(filename)") |
| 82 | |
| 83 | # Create triggers to keep FTS table in sync |
| 84 | conn.execute(""" |