summaryrefslogtreecommitdiff
path: root/util/sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2015-09-07 01:35:48 +0200
committerYorhel <git@yorhel.nl>2015-09-07 01:35:48 +0200
commit438d4df64d950f8905bd31bddc203d408f42f125 (patch)
treed2ebd6f9ea86238773cfdc35ac41033dcb9baffa /util/sql
parent19ce5fcf536ed478ad34b6b1014bf6f44841d25d (diff)
Implement discussion board search function
Inspired by wakaranai's implementation at https://github.com/morkt/vndb/commit/b852c87ad145fdaaa09c79b6378dd819b46f7e87 This version is different in a number of aspects: - Separate search functions for title search and fulltext post search. Perhaps not the most convenient option, but the downside of a combined search is that if the query matches the threads' title, then all of the posts in that thread will show up in the results. This didn't seem very useful. - Sorting is based purely on post date. Rank-based sort is slow without a separate caching column, and in my opinion not all that useful. Implementation differences: - Integrated in the existing DB::Discussions functions, so less code to maintain and more code reuse. - No separate caching column for the tsvector, a functional index is used instead. This is a bit slower (index results need to be re-checked against the actual messages, hence the slowdown), but has the advantage of smaller database dumps and less complexity in updating the cache. Things to fix or look at: - Highlighting of the search query in message contents. - Allow or-style query matching
Diffstat (limited to 'util/sql')
-rw-r--r--util/sql/all.sql4
-rw-r--r--util/sql/func.sql6
2 files changed, 10 insertions, 0 deletions
diff --git a/util/sql/all.sql b/util/sql/all.sql
index 833f5890..a1689149 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -123,6 +123,10 @@ CREATE SEQUENCE covers_seq;
CREATE SEQUENCE charimg_seq;
+-- Discussion board search index (can't put this in schema.sql because it refers to func.sql)
+CREATE INDEX threads_posts_ts ON threads_posts USING gin(to_tsvector('english', strip_bb_tags(msg)));
+
+
-- Rows that are assumed to be available
INSERT INTO users (id, username, mail, perm) VALUES (0, 'deleted', 'del@vndb.org', 0);
INSERT INTO users (username, mail, perm) VALUES ('multi', 'multi@vndb.org', 0);
diff --git a/util/sql/func.sql b/util/sql/func.sql
index f7125607..6bd483de 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -11,6 +11,12 @@
-- with that, either.
+-- strip_bb_tags(text) - simple utility function to aid full-text searching
+CREATE OR REPLACE FUNCTION strip_bb_tags(t text) RETURNS text AS $$
+ SELECT regexp_replace(t, '\[(?:url=[^\]]+|/?(?:spoiler|quote|raw|code|url))\]', ' ', 'g');
+$$ LANGUAGE sql IMMUTABLE;
+
+
-- update_vncache(id) - updates the c_* columns in the vn table
CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$