aparker.io
an atproto based link aggregator
1/**
2 * Set up FTS5 full-text search tables and triggers
3 * Run with: pnpm db:fts
4 */
5
6import { createClient } from '@libsql/client';
7
8const CONTENT_DB_PATH = process.env.CONTENT_DB_PATH || './data/content.db';
9
10// FTS setup statements - each is a complete statement
11const statements = [
12 // Posts FTS table
13 `CREATE VIRTUAL TABLE IF NOT EXISTS posts_fts USING fts5(
14 uri UNINDEXED,
15 title,
16 text,
17 url,
18 content='posts',
19 content_rowid='rowid'
20 )`,
21
22 // Posts triggers
23 `CREATE TRIGGER IF NOT EXISTS posts_fts_insert AFTER INSERT ON posts BEGIN
24 INSERT INTO posts_fts(rowid, uri, title, text, url)
25 VALUES (new.rowid, new.uri, new.title, new.text, new.url);
26 END`,
27
28 `CREATE TRIGGER IF NOT EXISTS posts_fts_delete AFTER DELETE ON posts BEGIN
29 INSERT INTO posts_fts(posts_fts, rowid, uri, title, text, url)
30 VALUES ('delete', old.rowid, old.uri, old.title, old.text, old.url);
31 END`,
32
33 `CREATE TRIGGER IF NOT EXISTS posts_fts_update AFTER UPDATE ON posts BEGIN
34 INSERT INTO posts_fts(posts_fts, rowid, uri, title, text, url)
35 VALUES ('delete', old.rowid, old.uri, old.title, old.text, old.url);
36 INSERT INTO posts_fts(rowid, uri, title, text, url)
37 VALUES (new.rowid, new.uri, new.title, new.text, new.url);
38 END`,
39
40 // Comments FTS table
41 `CREATE VIRTUAL TABLE IF NOT EXISTS comments_fts USING fts5(
42 uri UNINDEXED,
43 post_uri UNINDEXED,
44 text,
45 content='comments',
46 content_rowid='rowid'
47 )`,
48
49 // Comments triggers
50 `CREATE TRIGGER IF NOT EXISTS comments_fts_insert AFTER INSERT ON comments BEGIN
51 INSERT INTO comments_fts(rowid, uri, post_uri, text)
52 VALUES (new.rowid, new.uri, new.post_uri, new.text);
53 END`,
54
55 `CREATE TRIGGER IF NOT EXISTS comments_fts_delete AFTER DELETE ON comments BEGIN
56 INSERT INTO comments_fts(comments_fts, rowid, uri, post_uri, text)
57 VALUES ('delete', old.rowid, old.uri, old.post_uri, old.text);
58 END`,
59
60 `CREATE TRIGGER IF NOT EXISTS comments_fts_update AFTER UPDATE ON comments BEGIN
61 INSERT INTO comments_fts(comments_fts, rowid, uri, post_uri, text)
62 VALUES ('delete', old.rowid, old.uri, old.post_uri, old.text);
63 INSERT INTO comments_fts(rowid, uri, post_uri, text)
64 VALUES (new.rowid, new.uri, new.post_uri, new.text);
65 END`,
66
67 // Rebuild FTS indexes from existing data
68 `INSERT INTO posts_fts(posts_fts) VALUES('rebuild')`,
69 `INSERT INTO comments_fts(comments_fts) VALUES('rebuild')`
70];
71
72async function setupFTS() {
73 console.log(`Setting up FTS5 for ${CONTENT_DB_PATH}...`);
74
75 const client = createClient({
76 url: `file:${CONTENT_DB_PATH}`
77 });
78
79 for (const sql of statements) {
80 const preview = sql.slice(0, 60).replace(/\s+/g, ' ').trim();
81 try {
82 await client.execute(sql);
83 console.log('✓', preview + '...');
84 } catch (err) {
85 if (err instanceof Error && err.message.includes('already exists')) {
86 console.log('⊘', preview + '... (already exists)');
87 } else {
88 console.error('✗', preview);
89 console.error(' Error:', err instanceof Error ? err.message : err);
90 }
91 }
92 }
93
94 console.log('\nFTS5 setup complete!');
95 process.exit(0);
96}
97
98setupFTS();