Repository navigation
Expand file tree
/
Copy pathfts.ts
More file actions
97 lines (84 loc) · 2.85 KB
/
Copy pathfts.ts
File metadata and controls
97 lines (84 loc) · 2.85 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
/**
* SQLite FTS5 virtual table for fast full-text search across bookmarks.
* FTS5 uses Porter stemming and tokenization — much faster than LIKE '%keyword%' table scans.
*
* The table is rebuilt after enrichment runs. At search time it provides ranked ID lists
* that replace the LIKE-based keyword conditions in the search route.
*/
import prisma from '@/lib/db'
const FTS_TABLE = 'bookmark_fts'
export async function ensureFtsTable(): Promise<void> {
await prisma.$executeRawUnsafe(`
CREATE VIRTUAL TABLE IF NOT EXISTS ${FTS_TABLE} USING fts5(
bookmark_id UNINDEXED,
text,
semantic_tags,
entities,
image_tags,
tokenize='porter unicode61'
)
`)
}
/**
* Rebuild the FTS5 table from all bookmarks. Fast (local SQLite) and idempotent.
* Call after import or enrichment runs.
*/
export async function rebuildFts(): Promise<void> {
await ensureFtsTable()
await prisma.$executeRawUnsafe(`DELETE FROM ${FTS_TABLE}`)
const bookmarks = await prisma.bookmark.findMany({
select: {
id: true,
text: true,
semanticTags: true,
entities: true,
mediaItems: { select: { imageTags: true } },
},
})
if (bookmarks.length === 0) return
// Insert in batches of 200 to stay within SQLite variable limits
const BATCH = 200
for (let i = 0; i < bookmarks.length; i += BATCH) {
const batch = bookmarks.slice(i, i + BATCH)
await prisma.$transaction(
batch.map((b) => {
const imageTagsText = b.mediaItems
.map((m) => m.imageTags ?? '')
.filter(Boolean)
.join(' ')
return prisma.$executeRaw`
INSERT INTO bookmark_fts(bookmark_id, text, semantic_tags, entities, image_tags)
VALUES (${b.id}, ${b.text}, ${b.semanticTags ?? ''}, ${b.entities ?? ''}, ${imageTagsText})
`
}),
)
}
}
/**
* Search FTS5 table for bookmarks matching the given keywords.
* Returns bookmark IDs ordered by relevance rank.
* Returns [] on error (caller should fall back to LIKE queries).
*/
export async function ftsSearch(keywords: string[]): Promise<string[]> {
if (keywords.length === 0) return []
try {
await ensureFtsTable()
// Sanitize each keyword: remove FTS5 special chars, wrap in quotes for phrase safety
const terms = keywords
.map((kw) => kw.replace(/["*()]/g, ' ').trim())
.filter((kw) => kw.length >= 2)
if (terms.length === 0) return []
// Build FTS5 MATCH query with OR between terms
const matchQuery = terms.join(' OR ')
const results = await prisma.$queryRaw<{ bookmark_id: string }[]>`
SELECT bookmark_id FROM bookmark_fts
WHERE bookmark_fts MATCH ${matchQuery}
ORDER BY rank
LIMIT 150
`
return results.map((r) => r.bookmark_id)
} catch {
// FTS table may not be populated yet or query has syntax error — fall back gracefully
return []
}
}