about summary refs log tree commit diff
path: root/web/panettone/src/migrations
diff options
context:
space:
mode:
authorAspen Smith <root@gws.fyi>2024-03-24T18·31-0400
committeraspen <root@gws.fyi>2024-03-31T19·22+0000
commita80c0ce95f98ae826789d0161fded4dfd2999820 (patch)
tree6e4a12c53a2ec072f9072a948385979e8aa5615a /web/panettone/src/migrations
parent7f3d93942a6db69f98faa390f49673a2fd09df53 (diff)
feat(web/panettone): Support full-text search of issues r/7828
Support basic full text search of issues using postgresql's built-in
text search. There's a new column on the issues table called `tsv`,
which contains a tsvector of the title concatenated with the
description, and a new search form on both the index and closed issues
page which allows searching that tsvector with a user-supplied query.
Results are ranked according to that text query in the case of a search.

This works fine for now, but next up I'd also like to highlight the
results according to the bits that matched the user's query.

Change-Id: I25170bedbbbcdc3ed29a047962e9fcfe280d763a
Reviewed-on: https://cl.tvl.fyi/c/depot/+/11258
Autosubmit: aspen <root@gws.fyi>
Tested-by: BuildkiteCI
Reviewed-by: sterni <sternenseemann@systemli.org>
Diffstat (limited to 'web/panettone/src/migrations')
-rw-r--r--web/panettone/src/migrations/3920286378-add-issue-tsv.lisp5
1 files changed, 5 insertions, 0 deletions
diff --git a/web/panettone/src/migrations/3920286378-add-issue-tsv.lisp b/web/panettone/src/migrations/3920286378-add-issue-tsv.lisp
new file mode 100644
index 000000000000..2a965a7bba80
--- /dev/null
+++ b/web/panettone/src/migrations/3920286378-add-issue-tsv.lisp
@@ -0,0 +1,5 @@
+"Add tsvector for full-text search of issues"
+
+(defun up ()
+  (query "ALTER TABLE issues ADD COLUMN tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', subject || ' ' || body)) STORED")
+  (query "CREATE INDEX issues_tsv_index ON issues USING GIN (tsv);"))