max / goingson

5.2 KB · 133 lines History Blame Raw

1	-- Full-text search using FTS5 (SQLite)
2	-- Creates virtual tables for searching across emails, tasks, projects, and events
3
4	-- FTS5 virtual table for emails
5	CREATE VIRTUAL TABLE IF NOT EXISTS emails_fts USING fts5(
6	id UNINDEXED,
7	user_id UNINDEXED,
8	subject,
9	body,
10	from_address,
11	to_address,
12	content='emails',
13	content_rowid='rowid'
14	);
15
16	-- FTS5 virtual table for tasks
17	CREATE VIRTUAL TABLE IF NOT EXISTS tasks_fts USING fts5(
18	id UNINDEXED,
19	user_id UNINDEXED,
20	description,
21	tags,
22	content='tasks',
23	content_rowid='rowid'
24	);
25
26	-- FTS5 virtual table for projects
27	CREATE VIRTUAL TABLE IF NOT EXISTS projects_fts USING fts5(
28	id UNINDEXED,
29	user_id UNINDEXED,
30	name,
31	description,
32	content='projects',
33	content_rowid='rowid'
34	);
35
36	-- FTS5 virtual table for events
37	CREATE VIRTUAL TABLE IF NOT EXISTS events_fts USING fts5(
38	id UNINDEXED,
39	user_id UNINDEXED,
40	title,
41	description,
42	location,
43	content='events',
44	content_rowid='rowid'
45	);
46
47	-- Triggers to keep FTS tables in sync with main tables
48
49	-- Email triggers
50	CREATE TRIGGER IF NOT EXISTS emails_ai AFTER INSERT ON emails BEGIN
51	INSERT INTO emails_fts(rowid, id, user_id, subject, body, from_address, to_address)
52	VALUES (NEW.rowid, NEW.id, NEW.user_id, NEW.subject, NEW.body, NEW.from_address, NEW.to_address);
53	END;
54
55	CREATE TRIGGER IF NOT EXISTS emails_ad AFTER DELETE ON emails BEGIN
56	INSERT INTO emails_fts(emails_fts, rowid, id, user_id, subject, body, from_address, to_address)
57	VALUES ('delete', OLD.rowid, OLD.id, OLD.user_id, OLD.subject, OLD.body, OLD.from_address, OLD.to_address);
58	END;
59
60	CREATE TRIGGER IF NOT EXISTS emails_au AFTER UPDATE ON emails BEGIN
61	INSERT INTO emails_fts(emails_fts, rowid, id, user_id, subject, body, from_address, to_address)
62	VALUES ('delete', OLD.rowid, OLD.id, OLD.user_id, OLD.subject, OLD.body, OLD.from_address, OLD.to_address);
63	INSERT INTO emails_fts(rowid, id, user_id, subject, body, from_address, to_address)
64	VALUES (NEW.rowid, NEW.id, NEW.user_id, NEW.subject, NEW.body, NEW.from_address, NEW.to_address);
65	END;
66
67	-- Task triggers
68	CREATE TRIGGER IF NOT EXISTS tasks_ai AFTER INSERT ON tasks BEGIN
69	INSERT INTO tasks_fts(rowid, id, user_id, description, tags)
70	VALUES (NEW.rowid, NEW.id, NEW.user_id, NEW.description, NEW.tags);
71	END;
72
73	CREATE TRIGGER IF NOT EXISTS tasks_ad AFTER DELETE ON tasks BEGIN
74	INSERT INTO tasks_fts(tasks_fts, rowid, id, user_id, description, tags)
75	VALUES ('delete', OLD.rowid, OLD.id, OLD.user_id, OLD.description, OLD.tags);
76	END;
77
78	CREATE TRIGGER IF NOT EXISTS tasks_au AFTER UPDATE ON tasks BEGIN
79	INSERT INTO tasks_fts(tasks_fts, rowid, id, user_id, description, tags)
80	VALUES ('delete', OLD.rowid, OLD.id, OLD.user_id, OLD.description, OLD.tags);
81	INSERT INTO tasks_fts(rowid, id, user_id, description, tags)
82	VALUES (NEW.rowid, NEW.id, NEW.user_id, NEW.description, NEW.tags);
83	END;
84
85	-- Project triggers
86	CREATE TRIGGER IF NOT EXISTS projects_ai AFTER INSERT ON projects BEGIN
87	INSERT INTO projects_fts(rowid, id, user_id, name, description)
88	VALUES (NEW.rowid, NEW.id, NEW.user_id, NEW.name, NEW.description);
89	END;
90
91	CREATE TRIGGER IF NOT EXISTS projects_ad AFTER DELETE ON projects BEGIN
92	INSERT INTO projects_fts(projects_fts, rowid, id, user_id, name, description)
93	VALUES ('delete', OLD.rowid, OLD.id, OLD.user_id, OLD.name, OLD.description);
94	END;
95
96	CREATE TRIGGER IF NOT EXISTS projects_au AFTER UPDATE ON projects BEGIN
97	INSERT INTO projects_fts(projects_fts, rowid, id, user_id, name, description)
98	VALUES ('delete', OLD.rowid, OLD.id, OLD.user_id, OLD.name, OLD.description);
99	INSERT INTO projects_fts(rowid, id, user_id, name, description)
100	VALUES (NEW.rowid, NEW.id, NEW.user_id, NEW.name, NEW.description);
101	END;
102
103	-- Event triggers
104	CREATE TRIGGER IF NOT EXISTS events_ai AFTER INSERT ON events BEGIN
105	INSERT INTO events_fts(rowid, id, user_id, title, description, location)
106	VALUES (NEW.rowid, NEW.id, NEW.user_id, NEW.title, NEW.description, NEW.location);
107	END;
108
109	CREATE TRIGGER IF NOT EXISTS events_ad AFTER DELETE ON events BEGIN
110	INSERT INTO events_fts(events_fts, rowid, id, user_id, title, description, location)
111	VALUES ('delete', OLD.rowid, OLD.id, OLD.user_id, OLD.title, OLD.description, OLD.location);
112	END;
113
114	CREATE TRIGGER IF NOT EXISTS events_au AFTER UPDATE ON events BEGIN
115	INSERT INTO events_fts(events_fts, rowid, id, user_id, title, description, location)
116	VALUES ('delete', OLD.rowid, OLD.id, OLD.user_id, OLD.title, OLD.description, OLD.location);
117	INSERT INTO events_fts(rowid, id, user_id, title, description, location)
118	VALUES (NEW.rowid, NEW.id, NEW.user_id, NEW.title, NEW.description, NEW.location);
119	END;
120
121	-- Rebuild FTS indexes with existing data
122	INSERT INTO emails_fts(rowid, id, user_id, subject, body, from_address, to_address)
123	SELECT rowid, id, user_id, subject, body, from_address, to_address FROM emails;
124
125	INSERT INTO tasks_fts(rowid, id, user_id, description, tags)
126	SELECT rowid, id, user_id, description, tags FROM tasks;
127
128	INSERT INTO projects_fts(rowid, id, user_id, name, description)
129	SELECT rowid, id, user_id, name, description FROM projects;
130
131	INSERT INTO events_fts(rowid, id, user_id, title, description, location)
132	SELECT rowid, id, user_id, title, description, COALESCE(location, '') FROM events;
133