diff options
author | Yorhel <git@yorhel.nl> | 2015-09-07 13:58:01 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2015-09-07 13:58:01 +0200 |
commit | f51c7276fc65c2d1d46d3c8f915fb1e1412693e8 (patch) | |
tree | 6d5b2beaf8d913303f586dac28dd57d45bd41e6c /util | |
parent | f6dcfd8fcf094bd0872fb491135c58ce50f0674b (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.sql | 8 | ||||
-rw-r--r-- | util/updates/update_2.25.sql | 8 |
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; |