desertthunder.dev
a love letter to tangled (android, iOS, and a search API)
1package search
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "strings"
8)
9
10type SQLiteRepository struct {
11 db *sql.DB
12}
13
14func (r *SQLiteRepository) Ping(ctx context.Context) error {
15 return r.db.PingContext(ctx)
16}
17
18func (r *SQLiteRepository) Keyword(ctx context.Context, p Params) (*Response, error) {
19 ftsQuery := toFTS5Query(p.Query)
20
21 var filters []string
22 var filterArgs []any
23
24 if p.Collection != "" {
25 filters = append(filters, "d.collection = ?")
26 filterArgs = append(filterArgs, p.Collection)
27 }
28 if p.Type != "" {
29 filters = append(filters, "d.record_type = ?")
30 filterArgs = append(filterArgs, p.Type)
31 }
32 if p.Author != "" {
33 filters = append(filters, "(d.author_handle = ? OR d.did = ?)")
34 filterArgs = append(filterArgs, p.Author, p.Author)
35 }
36 if p.Repo != "" {
37 filters = append(filters, "(d.repo_name = ? OR d.repo_did = ?)")
38 filterArgs = append(filterArgs, p.Repo, p.Repo)
39 }
40 if p.Language != "" {
41 filters = append(filters, "d.language = ?")
42 filterArgs = append(filterArgs, p.Language)
43 }
44 if p.From != "" {
45 filters = append(filters, "d.created_at >= ?")
46 filterArgs = append(filterArgs, p.From)
47 }
48 if p.To != "" {
49 filters = append(filters, "d.created_at <= ?")
50 filterArgs = append(filterArgs, p.To)
51 }
52
53 var join string
54 if p.State != "" {
55 join = "JOIN record_state rs ON rs.subject_uri = d.at_uri"
56 filters = append(filters, "rs.state = ?")
57 filterArgs = append(filterArgs, p.State)
58 }
59
60 where := "documents_fts MATCH ? AND d.deleted_at IS NULL"
61 if len(filters) > 0 {
62 where += " AND " + strings.Join(filters, " AND ")
63 }
64
65 countSQL := fmt.Sprintf(
66 "SELECT COUNT(*) FROM documents_fts JOIN documents d ON d.id = documents_fts.id %s WHERE %s",
67 join, where,
68 )
69 countArgs := append([]any{ftsQuery}, filterArgs...)
70
71 var total int
72 if err := r.db.QueryRowContext(ctx, countSQL, countArgs...).Scan(&total); err != nil {
73 return nil, explainSQLiteSearchError("count", err)
74 }
75
76 resultsSQL := fmt.Sprintf(`
77 SELECT d.id, d.title, d.summary, d.repo_name, repo_owner.handle, d.author_handle,
78 d.did, d.at_uri, d.web_url, d.collection, d.record_type, d.created_at, d.updated_at,
79 -bm25(documents_fts, 0.0, 3.0, 1.0, 1.5, 2.5, 2.0, 1.2) AS score,
80 snippet(documents_fts, 2, '<mark>', '</mark>', '...', 20) AS body_snippet
81 FROM documents_fts
82 JOIN documents d ON d.id = documents_fts.id
83 LEFT JOIN identity_handles repo_owner ON repo_owner.did = d.repo_did AND repo_owner.is_active = 1
84 %s
85 WHERE %s
86 ORDER BY score DESC
87 LIMIT ? OFFSET ?`, join, where)
88
89 resultsArgs := make([]any, 0, 1+len(filterArgs)+2)
90 resultsArgs = append(resultsArgs, ftsQuery)
91 resultsArgs = append(resultsArgs, filterArgs...)
92 resultsArgs = append(resultsArgs, p.Limit, p.Offset)
93
94 rows, err := r.db.QueryContext(ctx, resultsSQL, resultsArgs...)
95 if err != nil {
96 return nil, explainSQLiteSearchError("search", err)
97 }
98 defer rows.Close()
99
100 results := make([]Result, 0)
101 for rows.Next() {
102 res, err := scanResult(rows)
103 if err != nil {
104 return nil, err
105 }
106 results = append(results, *res)
107 }
108 if err := rows.Err(); err != nil {
109 return nil, fmt.Errorf("rows: %w", err)
110 }
111
112 return &Response{
113 Query: p.Query,
114 Mode: "keyword",
115 Total: total,
116 Limit: p.Limit,
117 Offset: p.Offset,
118 Results: results,
119 }, nil
120}
121
122func explainSQLiteSearchError(op string, err error) error {
123 msg := err.Error()
124 if strings.Contains(msg, "no such table: documents_fts") ||
125 strings.Contains(msg, "no such module: fts5") {
126 return fmt.Errorf("%s: SQLite FTS5 is unavailable on this database: %w", op, err)
127 }
128 return fmt.Errorf("%s: %w", op, err)
129}
130
131func toFTS5Query(raw string) string {
132 parts := strings.Fields(raw)
133 if len(parts) == 0 {
134 return `""`
135 }
136
137 quoted := make([]string, 0, len(parts))
138 for _, part := range parts {
139 part = strings.ReplaceAll(part, `"`, `""`)
140 quoted = append(quoted, `"`+part+`"`)
141 }
142 return strings.Join(quoted, " OR ")
143}