summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--lib/VNDB/DB/Discussions.pm22
-rw-r--r--util/sql/func.sql8
-rw-r--r--util/sql/tableattrs.sql4
-rw-r--r--util/updates/update_2.25-sqlsplit.sql3
4 files changed, 27 insertions, 10 deletions
diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm
index 16d33c4a..79cf7b39 100644
--- a/lib/VNDB/DB/Discussions.pm
+++ b/lib/VNDB/DB/Discussions.pm
@@ -23,11 +23,11 @@ sub dbThreadGet {
!$o{id} ? (
't.hidden = FALSE' => 0 ) : (),
$o{type} && !$o{iid} ? (
- 't.id IN(SELECT tid FROM threads_boards WHERE type IN(!l))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (),
+ 'EXISTS(SELECT 1 FROM threads_boards WHERE tid = t.id AND type IN(!l))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (),
$o{type} && $o{iid} ? (
'tb.type = ?' => $o{type}, 'tb.iid = ?' => $o{iid} ) : (),
$o{notusers} ? (
- 't.id NOT IN(SELECT tid FROM threads_boards WHERE type = \'u\')' => 1) : (),
+ 't.id <> ANY(ARRAY(SELECT tid FROM threads_boards WHERE type = \'u\'))' => 1) : (),
);
if($o{search}) {
@@ -39,15 +39,10 @@ sub dbThreadGet {
my @select = (
qw|t.id t.title t.count t.locked t.hidden|,
- $o{what} =~ /firstpost/ ? ('tpf.uid AS fuid', q|EXTRACT('epoch' from tpf.date) AS fdate|, 'uf.username AS fusername') : (),
$o{what} =~ /lastpost/ ? ('tpl.uid AS luid', q|EXTRACT('epoch' from tpl.date) AS ldate|, 'ul.username AS lusername') : (),
);
my @join = (
- $o{what} =~ /firstpost/ ? (
- 'JOIN threads_posts tpf ON tpf.tid = t.id AND tpf.num = 1',
- 'JOIN users uf ON uf.id = tpf.uid'
- ) : (),
$o{what} =~ /lastpost/ ? (
'JOIN threads_posts tpl ON tpl.tid = t.id AND tpl.num = t.count',
'JOIN users ul ON ul.id = tpl.uid'
@@ -84,6 +79,17 @@ sub dbThreadGet {
[ keys %r ]
)});
}
+
+ if($o{what} =~ /firstpost/) {
+ do { my $x = $r->[$r{$_->{tid}}]; $x->{fuid} = $_->{uid}; $x->{fdate} = $_->{date}; $x->{fusername} = $_->{username} } for (@{$self->dbAll(q|
+ SELECT tpf.tid, tpf.uid, EXTRACT('epoch' from tpf.date) AS date, uf.username
+ FROM threads_posts tpf
+ JOIN users uf ON tpf.uid = uf.id
+ WHERE tpf.num = 1 AND tpf.tid IN(!l)|,
+ [ keys %r ]
+ )});
+ }
+
if($o{what} =~ /boardtitles/) {
push(@{$r->[$r{$_->{tid}}]{boards}}, $_) for (@{$self->dbAll(q|
SELECT tb.tid, tb.type, tb.iid, COALESCE(u.username, v.title, p.name) AS title, COALESCE(u.username, v.original, p.original) AS original
@@ -185,7 +191,7 @@ sub dbPostGet {
$o{hide} && $o{what} =~ /thread/ ? (
't.hidden = FALSE' => 1 ) : (),
$o{search} ? (
- q{to_tsvector('english', strip_bb_tags(msg)) @@ to_tsquery(?)} => $o{search}) : (),
+ 'bb_tsvector(msg) @@ to_tsquery(?)' => $o{search}) : (),
$o{type} ? (
'tp.tid = ANY(ARRAY(SELECT tid FROM threads_boards WHERE type IN(!l)))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (),
);
diff --git a/util/sql/func.sql b/util/sql/func.sql
index e5e12eab..125455a2 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -15,6 +15,14 @@ CREATE OR REPLACE FUNCTION strip_bb_tags(t text) RETURNS text AS $$
SELECT regexp_replace(t, '\[(?:url=[^\]]+|/?(?:spoiler|quote|raw|code|url))\]', ' ', 'gi');
$$ LANGUAGE sql IMMUTABLE;
+-- Wrapper around to_tsvector() and strip_bb_tags(), implemented in plpgsql and
+-- with an associated cost function to make it opaque to the query planner and
+-- ensure the query planner realizes that this function is _slow_.
+CREATE OR REPLACE FUNCTION bb_tsvector(t text) RETURNS tsvector AS $$
+BEGIN
+ RETURN to_tsvector('english', strip_bb_tags(t));
+END;
+$$ LANGUAGE plpgsql IMMUTABLE COST 500;
-- 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 $$
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index d9b0f4c5..5b9ca1ae 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -101,7 +101,9 @@ CREATE INDEX staff_alias_id ON staff_alias (id);
CREATE INDEX tags_vn_date ON tags_vn (date);
CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid);
CREATE INDEX tags_vn_vid ON tags_vn (vid);
-CREATE INDEX threads_posts_ts ON threads_posts USING gin(to_tsvector('english', strip_bb_tags(msg)));
+CREATE INDEX threads_posts_ts ON threads_posts USING gin(bb_tsvector(msg));
+CREATE INDEX threads_posts_date ON threads_posts (date);
+CREATE INDEX threads_posts_uid ON threads_posts (uid); -- Only really used on /u+ pages to get stats
CREATE INDEX vn_staff_aid ON vn_staff (aid);
CREATE INDEX vn_seiyuu_aid ON vn_seiyuu (aid);
CREATE UNIQUE INDEX changes_itemrev ON changes (type, itemid, rev);
diff --git a/util/updates/update_2.25-sqlsplit.sql b/util/updates/update_2.25-sqlsplit.sql
index 9104972a..b7ffa91f 100644
--- a/util/updates/update_2.25-sqlsplit.sql
+++ b/util/updates/update_2.25-sqlsplit.sql
@@ -230,7 +230,7 @@ DROP TABLE vn_screenshots_old CASCADE;
DROP TABLE vn_seiyuu_old CASCADE;
DROP TABLE vn_staff_old CASCADE;
-\i util/sql/tableattrs.sql
+DROP INDEX threads_posts_ts;
DROP FUNCTION edit_revtable(dbentry_type, integer);
DROP FUNCTION edit_vn_init(integer);
@@ -254,4 +254,5 @@ CREATE TYPE edit_rettype AS (itemid integer, chid integer, rev integer);
\i util/sql/func.sql
\i util/sql/editfunc.sql
+\i util/sql/tableattrs.sql
\i util/sql/triggers.sql