diff options
author | Yorhel <git@yorhel.nl> | 2015-09-07 01:35:48 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2015-09-07 01:35:48 +0200 |
commit | 438d4df64d950f8905bd31bddc203d408f42f125 (patch) | |
tree | d2ebd6f9ea86238773cfdc35ac41033dcb9baffa /util/sql | |
parent | 19ce5fcf536ed478ad34b6b1014bf6f44841d25d (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.sql | 4 | ||||
-rw-r--r-- | util/sql/func.sql | 6 |
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 $$ |