summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
Diffstat (limited to 'util')
-rw-r--r--util/sql/all.sql4
-rw-r--r--util/sql/func.sql6
-rw-r--r--util/updates/update_2.25.sql8
3 files changed, 18 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 $$
diff --git a/util/updates/update_2.25.sql b/util/updates/update_2.25.sql
index 8207adc6..bba0662b 100644
--- a/util/updates/update_2.25.sql
+++ b/util/updates/update_2.25.sql
@@ -49,3 +49,11 @@ ALTER TYPE prefs_key ADD VALUE 'traits_sexual';
CREATE TYPE board_type AS ENUM ('an', 'db', 'ge', 'v', 'p', 'u');
ALTER TABLE threads_boards ALTER COLUMN type DROP DEFAULT;
ALTER TABLE threads_boards ALTER COLUMN type TYPE board_type USING trim(type)::board_type;
+
+
+-- Full-text board search
+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;
+
+CREATE INDEX threads_posts_ts ON threads_posts USING gin(to_tsvector('english', strip_bb_tags(msg)));