summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2015-09-07 13:58:01 +0200
committerYorhel <git@yorhel.nl>2015-09-07 13:58:01 +0200
commitf51c7276fc65c2d1d46d3c8f915fb1e1412693e8 (patch)
tree6d5b2beaf8d913303f586dac28dd57d45bd41e6c /util
parentf6dcfd8fcf094bd0872fb491135c58ce50f0674b (diff)
Handler::Discussions: Use ts_headline() to format search results
And also fix strip_bb_tags() to be case-insensitive and fix a bug in converting the query into a tsquery.
Diffstat (limited to 'util')
-rw-r--r--util/sql/func.sql8
-rw-r--r--util/updates/update_2.25.sql8
2 files changed, 14 insertions, 2 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 6bd483de..6f675479 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -13,10 +13,16 @@
-- 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');
+ SELECT regexp_replace(t, '\[(?:url=[^\]]+|/?(?:spoiler|quote|raw|code|url))\]', ' ', 'gi');
$$ LANGUAGE sql IMMUTABLE;
+-- BUG: Since this isn't a full bbcode parser, [spoiler] tags inside [raw] or [code] are still considered spoilers.
+CREATE OR REPLACE FUNCTION strip_spoilers(t text) RETURNS text AS $$
+ -- The website doesn't require the [spoiler] tag to be closed, the outer replace catches that case.
+ SELECT regexp_replace(regexp_replace(t, '\[spoiler\].*?\[/spoiler\]', ' ', 'ig'), '\[spoiler\].*', ' ', 'i');
+$$ 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 bba0662b..138ec7a0 100644
--- a/util/updates/update_2.25.sql
+++ b/util/updates/update_2.25.sql
@@ -53,7 +53,13 @@ ALTER TABLE threads_boards ALTER COLUMN type TYPE board_type USING trim(type)::b
-- 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');
+ SELECT regexp_replace(t, '\[(?:url=[^\]]+|/?(?:spoiler|quote|raw|code|url))\]', ' ', 'gi');
$$ LANGUAGE sql IMMUTABLE;
CREATE INDEX threads_posts_ts ON threads_posts USING gin(to_tsvector('english', strip_bb_tags(msg)));
+
+-- BUG: Since this isn't a full bbcode parser, [spoiler] tags inside [raw] or [code] are still considered spoilers.
+CREATE OR REPLACE FUNCTION strip_spoilers(t text) RETURNS text AS $$
+ -- The website doesn't require the [spoiler] tag to be closed, the outer replace catches that case.
+ SELECT regexp_replace(regexp_replace(t, '\[spoiler\].*?\[/spoiler\]', ' ', 'ig'), '\[spoiler\].*', ' ', 'i');
+$$ LANGUAGE sql IMMUTABLE;