From 96084614898f30f06b63fe121b36ba1a25c4f9e7 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Wed, 5 Aug 2020 16:48:25 +0200 Subject: SQL: Use vndbid for thread identifiers Also drop the thread_poll_votes.tid column, as it's not really necessary. And add ON DELETE CASCADEs to threads_posts and threads_boards to simplify thread deletion. Relatively invasive change, prolly broke something. --- elm/Discussions/Edit.elm | 4 ++-- elm/Discussions/Reply.elm | 2 +- lib/Multi/Feed.pm | 4 ++-- lib/Multi/IRC.pm | 6 +++--- lib/VNDB/Handler/Misc.pm | 2 +- lib/VNWeb/Discussions/Edit.pm | 17 +++++++---------- lib/VNWeb/Discussions/Lib.pm | 4 ++-- lib/VNWeb/Discussions/Search.pm | 4 ++-- lib/VNWeb/Discussions/Thread.pm | 30 ++++++++++++++++++------------ lib/VNWeb/Discussions/UPosts.pm | 4 ++-- lib/VNWeb/Prelude.pm | 4 ++-- sql/schema.sql | 14 +++++++------- sql/tableattrs.sql | 5 ++--- sql/triggers.sql | 6 +++--- util/devdump.pl | 2 +- util/updates/2020-08-07-threads.sql | 30 ++++++++++++++++++++++++++++++ 16 files changed, 85 insertions(+), 53 deletions(-) create mode 100644 util/updates/2020-08-07-threads.sql diff --git a/elm/Discussions/Edit.elm b/elm/Discussions/Edit.elm index f8873fa7..c825506e 100644 --- a/elm/Discussions/Edit.elm +++ b/elm/Discussions/Edit.elm @@ -25,7 +25,7 @@ main = Browser.element type alias Model = { state : Api.State - , tid : Maybe Int + , tid : Maybe String , num : Maybe Int , can_mod : Bool , can_private : Bool @@ -215,7 +215,7 @@ view model = , table [ class "formtable" ] <| [ if thread then formField "title::Thread title" [ inputText "title" (Maybe.withDefault "" model.title) Title (style "width" "400px" :: required True :: GDE.valTitle) ] - else formField "Topic" [ a [ href <| "/t" ++ String.fromInt (Maybe.withDefault 0 model.tid) ] [ text (Maybe.withDefault "" model.title) ] ] + else formField "Topic" [ a [ href <| "/" ++ Maybe.withDefault "" model.tid ] [ text (Maybe.withDefault "" model.title) ] ] , if thread && model.can_mod then formField "" [ label [] [ inputCheck "" model.locked Locked, text " Locked" ] ] else text "" diff --git a/elm/Discussions/Reply.elm b/elm/Discussions/Reply.elm index a8d25434..3581c91f 100644 --- a/elm/Discussions/Reply.elm +++ b/elm/Discussions/Reply.elm @@ -22,7 +22,7 @@ main = Browser.element type alias Model = { state : Api.State - , tid : Int + , tid : String , old : Bool , msg : TP.Model } diff --git a/lib/Multi/Feed.pm b/lib/Multi/Feed.pm index 626e837b..8110bee8 100644 --- a/lib/Multi/Feed.pm +++ b/lib/Multi/Feed.pm @@ -31,7 +31,7 @@ sub run { sub generate { # announcements pg_cmd q{ - SELECT '/t'||t.id AS id, t.title, extract('epoch' from tp.date) AS published, + SELECT '/'||t.id AS id, t.title, extract('epoch' from tp.date) AS published, extract('epoch' from tp.edited) AS updated, u.username, u.id AS uid, tp.msg AS summary FROM threads t JOIN threads_posts tp ON tp.tid = t.id AND tp.num = 1 @@ -65,7 +65,7 @@ sub generate { # posts pg_cmd q{ - SELECT '/t'||t.id||'.'||tp.num AS id, t.title||' (#'||tp.num||')' AS title, extract('epoch' from tp.date) AS published, + SELECT '/'||t.id||'.'||tp.num AS id, t.title||' (#'||tp.num||')' AS title, extract('epoch' from tp.date) AS published, extract('epoch' from tp.edited) AS updated, u.username, u.id AS uid, tp.msg AS summary FROM threads_posts tp JOIN threads t ON t.id = tp.tid diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm index 6c86d2f9..d4fb3781 100644 --- a/lib/Multi/IRC.pm +++ b/lib/Multi/IRC.pm @@ -293,7 +293,7 @@ sub handleid { $t eq 'p' ? 'p.name AS title FROM producers p WHERE p.id = $2' : $t eq 'c' ? 'c.name AS title FROM chars c WHERE c.id = $2' : $t eq 's' ? 'sa.name AS title FROM staff s JOIN staff_alias sa ON sa.aid = s.aid AND sa.id = s.id WHERE s.id = $2' : - $t eq 't' ? 'title, '.$GETBOARDS.' FROM threads t WHERE NOT t.hidden AND NOT t.private AND t.id = $2' : + $t eq 't' ? 'title, '.$GETBOARDS.' FROM threads t WHERE NOT t.hidden AND NOT t.private AND t.id = vndbid(\'t\',$2)' : $t eq 'g' ? 'name AS title FROM tags WHERE id = $2' : $t eq 'i' ? 'name AS title FROM traits WHERE id = $2' : $t eq 'd' ? 'title FROM docs WHERE id = $2' : @@ -308,7 +308,7 @@ sub handleid { $t eq 'c' ? 'ch.name AS title, u.username, c.comments FROM changes c JOIN chars_hist ch ON c.id = ch.chid JOIN users u ON u.id = c.requester WHERE c.type = \'c\' AND c.itemid = $2 AND c.rev = $3' : $t eq 's' ? 'sah.name AS title, u.username, c.comments FROM changes c JOIN staff_hist sh ON c.id = sh.chid JOIN users u ON u.id = c.requester JOIN staff_alias_hist sah ON sah.chid = c.id AND sah.aid = sh.aid WHERE c.type = \'s\' AND c.itemid = $2 AND c.rev = $3' : $t eq 'd' ? 'dh.title, u.username, c.comments FROM changes c JOIN docs_hist dh ON c.id = dh.chid JOIN users u ON u.id = c.requester WHERE c.type = \'d\' AND c.itemid = $2 AND c.rev = $3' : - 't.title, u.username, '.$GETBOARDS.' FROM threads t JOIN threads_posts tp ON tp.tid = t.id JOIN users u ON u.id = tp.uid WHERE NOT t.hidden AND NOT t.private AND t.id = $2 AND tp.num = $3'), + 't.title, u.username, '.$GETBOARDS.' FROM threads t JOIN threads_posts tp ON tp.tid = t.id JOIN users u ON u.id = tp.uid WHERE NOT t.hidden AND NOT t.private AND t.id = vndbid(\'t\',$2) AND tp.num = $3'), [ $t, $id, $rev], $c if $rev && $t =~ /[dvprtcs]/; } @@ -351,7 +351,7 @@ sub notify { WHERE c.id > $1 AND c.requester <> 1 ORDER BY c.id}, post => q{ - SELECT 't' AS type, tp.tid AS id, tp.num AS rev, t.title, u.username, tp.date AS lastid, }.$GETBOARDS.q{ + SELECT 't' AS type, vndbid_num(tp.tid) AS id, tp.num AS rev, t.title, u.username, tp.date AS lastid, }.$GETBOARDS.q{ FROM threads_posts tp JOIN threads t ON t.id = tp.tid JOIN users u ON u.id = tp.uid diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm index 25d10c39..36c18d08 100644 --- a/lib/VNDB/Handler/Misc.pm +++ b/lib/VNDB/Handler/Misc.pm @@ -102,7 +102,7 @@ sub homepage { for (@$an) { my $post = $self->dbPostGet(tid => $_->{id}, num => 1)->[0]; h2; - a href => "/t$_->{id}", $_->{title}; + a href => "/$_->{id}", $_->{title}; end; p; lit bb2html $post->{msg}, 150; diff --git a/lib/VNWeb/Discussions/Edit.pm b/lib/VNWeb/Discussions/Edit.pm index 7dc2a0c5..d954dbb0 100644 --- a/lib/VNWeb/Discussions/Edit.pm +++ b/lib/VNWeb/Discussions/Edit.pm @@ -5,7 +5,7 @@ use VNWeb::Discussions::Lib; my $FORM = { - tid => { required => 0, id => 1 }, # Thread ID, only when editing a post + tid => { required => 0, vndbid => 't' }, # Thread ID, only when editing a post num => { required => 0, id => 1 }, # Post number, only when editing # Only when num = 1 || tid = undef @@ -51,24 +51,21 @@ elm_api DiscussionsEdit => $FORM_OUT, $FORM_IN, sub { return elm_Unauth if !can_edit t => $t; if($data->{delete} && auth->permBoardmod) { - auth->audit($t->{user_id}, 'post delete', "deleted t$tid.$num"); + auth->audit($t->{user_id}, 'post delete', "deleted $tid.$num"); if($num == 1) { - # (This could be a single query if there were proper ON DELETE CASCADE in the DB, though that's hard for notifications...) - tuwf->dbExeci('DELETE FROM threads_posts WHERE tid =', \$tid); - tuwf->dbExeci('DELETE FROM threads_boards WHERE tid =', \$tid); tuwf->dbExeci('DELETE FROM threads WHERE id =', \$tid); - tuwf->dbExeci(q{DELETE FROM notifications WHERE ltype = 't' AND iid =}, \$tid); + tuwf->dbExeci(q{DELETE FROM notifications WHERE ltype = 't' AND iid = vndbid_num(}, \$tid, ')'); return elm_Redirect '/t'; } else { tuwf->dbExeci('DELETE FROM threads_posts WHERE tid =', \$tid, 'AND num =', \$num); tuwf->dbExeci('UPDATE threads_posts SET num = num - 1 WHERE tid =', \$tid, 'AND num >', \$num); tuwf->dbExeci('UPDATE threads SET count = count - 1 WHERE id =', \$tid); - tuwf->dbExeci(q{DELETE FROM notifications WHERE ltype = 't' AND iid =}, \$tid, 'AND subid =', \$num); - tuwf->dbExeci(q{UPDATE notifications SET subid = subid - 1 WHERE ltype = 't' AND iid =}, \$tid, 'AND subid >', \$num); - return elm_Redirect "/t$tid"; + tuwf->dbExeci(q{DELETE FROM notifications WHERE ltype = 't' AND iid = vndbid_num(}, \$tid, ') AND subid =', \$num); + tuwf->dbExeci(q{UPDATE notifications SET subid = subid - 1 WHERE ltype = 't' AND iid = vndbid_num(}, \$tid, ') AND subid >', \$num); + return elm_Redirect "/$tid"; } } - auth->audit($t->{user_id}, 'post edit', "edited t$tid.$num") if $tid && $t->{user_id} != auth->uid; + auth->audit($t->{user_id}, 'post edit', "edited $tid.$num") if $tid && $t->{user_id} != auth->uid; my $pollchanged = !$data->{tid} && $data->{poll}; if($num == 1) { diff --git a/lib/VNWeb/Discussions/Lib.pm b/lib/VNWeb/Discussions/Lib.pm index 9f77397e..a43df50a 100644 --- a/lib/VNWeb/Discussions/Lib.pm +++ b/lib/VNWeb/Discussions/Lib.pm @@ -12,7 +12,7 @@ our $BOARD_RE = join '|', map $_.($BOARD_TYPE{$_}{dbitem}?'(?:[1-9][0-9]{0,5})?' # Returns the URL to the thread page holding the given post (with optional location.hash) sub post_url { my($id, $num, $hash) = @_; - "/t$id".($num > 25 ? '/'.ceil($num/25) : '').($hash ? "#$hash" : ''); + "/$id".($num > 25 ? '/'.ceil($num/25) : '').($hash ? "#$hash" : ''); } @@ -92,7 +92,7 @@ sub threadlist_ { tr_ sub { my $l = $_; td_ class => 'tc1', sub { - a_ mkclass(locked => $l->{locked}), href => "/t$l->{id}", sub { + a_ mkclass(locked => $l->{locked}), href => "/$l->{id}", sub { span_ class => 'pollflag', '[poll]' if $l->{haspoll}; span_ class => 'pollflag', '[private]' if $l->{private}; span_ class => 'pollflag', '[hidden]' if $l->{hidden}; diff --git a/lib/VNWeb/Discussions/Search.pm b/lib/VNWeb/Discussions/Search.pm index 73deba04..366b3c67 100644 --- a/lib/VNWeb/Discussions/Search.pm +++ b/lib/VNWeb/Discussions/Search.pm @@ -99,8 +99,8 @@ sub posts_ { }}; tr_ sub { my $l = $_; - my $link = "/t$l->{tid}.$l->{num}"; - td_ class => 'tc1_1', sub { a_ href => $link, 't'.$l->{tid} }; + my $link = "/$l->{tid}.$l->{num}"; + td_ class => 'tc1_1', sub { a_ href => $link, $l->{tid} }; td_ class => 'tc1_2', sub { a_ href => $link, '.'.$l->{num} }; td_ class => 'tc2', fmtdate $l->{date}; td_ class => 'tc3', sub { user_ $l }; diff --git a/lib/VNWeb/Discussions/Thread.pm b/lib/VNWeb/Discussions/Thread.pm index c49c7e1c..b3536680 100644 --- a/lib/VNWeb/Discussions/Thread.pm +++ b/lib/VNWeb/Discussions/Thread.pm @@ -10,7 +10,7 @@ my $POLL_OUT = form_compile any => { num_votes => { uint => 1 }, can_vote => { anybool => 1 }, preview => { anybool => 1 }, - tid => { id => 1 }, + tid => { vndbid => 't' }, options => { aoh => { id => { id => 1 }, option => {}, @@ -20,7 +20,7 @@ my $POLL_OUT = form_compile any => { }; my $POLL_IN = form_compile any => { - tid => { id => 1 }, + tid => { vndbid => 't' }, options => { type => 'array', values => { id => 1 } }, }; @@ -32,10 +32,11 @@ elm_api DiscussionsPoll => $POLL_OUT, $POLL_IN, sub { return tuwf->resNotFound if !$t->{poll_question}; die 'Too many options' if $data->{options}->@* > $t->{poll_max_options}; - validate_dbid sql('SELECT id FROM threads_poll_options WHERE tid =', \$data->{tid}, 'AND id IN'), $data->{options}->@*; + my %opt = map +($_->{id},1), tuwf->dbAlli('SELECT id FROM threads_poll_options WHERE tid =', \$data->{tid})->@*; + die 'Invalid option' if grep !$opt{$_}, $data->{options}->@*; - tuwf->dbExeci('DELETE FROM threads_poll_votes WHERE tid =', \$data->{tid}, 'AND uid =', \auth->uid); - tuwf->dbExeci('INSERT INTO threads_poll_votes', { tid => $data->{tid}, uid => auth->uid, optid => $_ }) for $data->{options}->@*; + tuwf->dbExeci('DELETE FROM threads_poll_votes WHERE optid IN', [ keys %opt ], 'AND uid =', \auth->uid); + tuwf->dbExeci('INSERT INTO threads_poll_votes', { uid => auth->uid, optid => $_ }) for $data->{options}->@*; elm_Success }; @@ -43,7 +44,7 @@ elm_api DiscussionsPoll => $POLL_OUT, $POLL_IN, sub { my $REPLY = { - tid => { id => 1 }, + tid => { vndbid => 't' }, old => { _when => 'out', anybool => 1 }, msg => { _when => 'in', maxlength => 32768 } }; @@ -96,14 +97,14 @@ sub metabox_ { sub posts_ { my($t, $posts, $page) = @_; - my sub url { "/t$t->{id}".($_?"/$_":'') } + my sub url { "/$t->{id}".($_?"/$_":'') } paginate_ \&url, $page, [ $t->{count}, 25 ], 't'; div_ class => 'mainbox thread', sub { table_ class => 'stripe', sub { tr_ mkclass(deleted => $_->{hidden}), id => $_->{num}, sub { td_ class => 'tc1', $t->{count} == $_->{num} ? (id => 'last') : (), sub { - a_ href => "/t$t->{id}.$_->{num}", "#$_->{num}"; + a_ href => "/$t->{id}.$_->{num}", "#$_->{num}"; if(!$_->{hidden} || auth->permBoard) { txt_ ' by '; user_ $_; @@ -115,10 +116,10 @@ sub posts_ { i_ class => 'edit', sub { txt_ '< '; if(can_edit t => $_) { - a_ href => "/t$t->{id}.$_->{num}/edit", 'edit'; + a_ href => "/$t->{id}.$_->{num}/edit", 'edit'; txt_ ' - '; } - a_ href => "/report/t/t$t->{id}.$_->{num}", 'report'; + a_ href => "/report/t/$t->{id}.$_->{num}", 'report'; txt_ ' >'; }; if($_->{hidden}) { @@ -188,7 +189,7 @@ TUWF::get qr{/$RE{tid}(?:/$RE{num})?}, sub { # Mark a notification for this thread as read, if there is one. tuwf->dbExeci( - 'UPDATE notifications SET read = NOW() WHERE uid =', \auth->uid, 'AND ltype = \'t\' AND iid = ', \$id, 'AND read IS NULL' + 'UPDATE notifications SET read = NOW() WHERE uid =', \auth->uid, 'AND ltype = \'t\' AND iid = vndbid_num(', \$id, ') AND read IS NULL' ) if auth && $t->{count} <= $page*25; framework_ title => $t->{title}, sub { @@ -196,7 +197,12 @@ TUWF::get qr{/$RE{tid}(?:/$RE{num})?}, sub { elm_ 'Discussions.Poll' => $POLL_OUT, { question => $t->{poll_question}, max_options => $t->{poll_max_options}, - num_votes => tuwf->dbVali('SELECT COUNT(DISTINCT tpv.uid) FROM threads_poll_votes tpv JOIN users u ON tpv.uid = u.id WHERE NOT u.ign_votes AND tid =', \$id), + num_votes => tuwf->dbVali( + 'SELECT COUNT(DISTINCT tpv.uid) + FROM threads_poll_votes tpv + JOIN threads_poll_options tpo ON tpo.id = tpv.optid + JOIN users u ON tpv.uid = u.id + WHERE NOT u.ign_votes AND tpo.tid =', \$id), preview => !!tuwf->reqGet('pollview'), # Old non-Elm way to preview poll results can_vote => !!auth, tid => $id, diff --git a/lib/VNWeb/Discussions/UPosts.pm b/lib/VNWeb/Discussions/UPosts.pm index 45be3f0b..771fb3a9 100644 --- a/lib/VNWeb/Discussions/UPosts.pm +++ b/lib/VNWeb/Discussions/UPosts.pm @@ -18,8 +18,8 @@ sub listing_ { td_ class => 'tc4', 'Title'; }}; tr_ sub { - my $url = "/t$_->{tid}.$_->{num}"; - td_ class => 'tc1', sub { a_ href => $url, 't'.$_->{tid} }; + my $url = "/$_->{tid}.$_->{num}"; + td_ class => 'tc1', sub { a_ href => $url, $_->{tid} }; td_ class => 'tc2', sub { a_ href => $url, '.'.$_->{num} }; td_ class => 'tc3', fmtdate $_->{date}; td_ class => 'tc4', sub { diff --git a/lib/VNWeb/Prelude.pm b/lib/VNWeb/Prelude.pm index 3bd0720b..79012c8b 100644 --- a/lib/VNWeb/Prelude.pm +++ b/lib/VNWeb/Prelude.pm @@ -91,7 +91,7 @@ our %RE = ( pid => qr{p$id}, iid => qr{i$id}, did => qr{d$id}, - tid => qr{t$id}, + tid => qr{(?t$num)}, gid => qr{g$id}, imgid=> qr{(?(?:ch|cv|sf)$num)}, vrev => qr{v$id$rev?}, @@ -100,7 +100,7 @@ our %RE = ( srev => qr{s$id$rev?}, crev => qr{c$id$rev?}, drev => qr{d$id$rev?}, - postid => qr{t$id\.(?$num)}, + postid => qr{(?t$num)\.(?$num)}, ); diff --git a/sql/schema.sql b/sql/schema.sql index fce40881..8fd52943 100644 --- a/sql/schema.sql +++ b/sql/schema.sql @@ -69,9 +69,10 @@ CREATE TYPE tag_category AS ENUM('cont', 'ero', 'tech'); CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig'); CREATE TYPE session_type AS ENUM ('web', 'pass', 'mail'); --- Sequences used for ID generation of items not in the DB +-- Sequences used for ID generation CREATE SEQUENCE covers_seq; CREATE SEQUENCE charimg_seq; +CREATE SEQUENCE threads_id_seq; CREATE SEQUENCE screenshots_seq; @@ -673,7 +674,7 @@ CREATE TABLE tags_vn_inherit ( -- threads CREATE TABLE threads ( - id SERIAL NOT NULL PRIMARY KEY, + id vndbid PRIMARY KEY CONSTRAINT threads_id_check CHECK(vndbid_type(id) = 't'), title varchar(50) NOT NULL DEFAULT '', locked boolean NOT NULL DEFAULT FALSE, hidden boolean NOT NULL DEFAULT FALSE, @@ -688,22 +689,21 @@ CREATE TABLE threads ( -- threads_poll_options CREATE TABLE threads_poll_options ( id SERIAL PRIMARY KEY, - tid integer NOT NULL, + tid vndbid NOT NULL, option varchar(100) NOT NULL ); -- threads_poll_votes CREATE TABLE threads_poll_votes ( - tid integer NOT NULL, uid integer NOT NULL, optid integer NOT NULL, date timestamptz DEFAULT NOW(), - PRIMARY KEY (tid, uid, optid) + PRIMARY KEY (optid, uid) ); -- threads_posts CREATE TABLE threads_posts ( - tid integer NOT NULL DEFAULT 0, + tid vndbid NOT NULL, num smallint NOT NULL DEFAULT 0, uid integer NOT NULL DEFAULT 0, date timestamptz NOT NULL DEFAULT NOW(), @@ -715,7 +715,7 @@ CREATE TABLE threads_posts ( -- threads_boards CREATE TABLE threads_boards ( - tid integer NOT NULL DEFAULT 0, + tid vndbid NOT NULL, type board_type NOT NULL, iid integer NOT NULL DEFAULT 0, PRIMARY KEY(tid, type, iid) diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql index 3ee1299f..a0ff9737 100644 --- a/sql/tableattrs.sql +++ b/sql/tableattrs.sql @@ -60,12 +60,11 @@ ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_vid_fkey ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT; ALTER TABLE threads ADD CONSTRAINT threads_id_fkey FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE threads_poll_options ADD CONSTRAINT threads_poll_options_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE; -ALTER TABLE threads_poll_votes ADD CONSTRAINT threads_poll_votes_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE; ALTER TABLE threads_poll_votes ADD CONSTRAINT threads_poll_votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE threads_poll_votes ADD CONSTRAINT threads_poll_votes_optid_fkey FOREIGN KEY (optid) REFERENCES threads_poll_options (id) ON DELETE CASCADE; -ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id); +ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE; ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT; -ALTER TABLE threads_boards ADD CONSTRAINT threads_boards_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id); +ALTER TABLE threads_boards ADD CONSTRAINT threads_boards_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE; ALTER TABLE traits ADD CONSTRAINT traits_addedby_fkey FOREIGN KEY (addedby) REFERENCES users (id) ON DELETE SET DEFAULT; ALTER TABLE traits ADD CONSTRAINT traits_group_fkey FOREIGN KEY ("group") REFERENCES traits (id); ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_trait_fkey FOREIGN KEY (trait) REFERENCES traits (id); diff --git a/sql/triggers.sql b/sql/triggers.sql index b7ade0c4..28b882a0 100644 --- a/sql/triggers.sql +++ b/sql/triggers.sql @@ -230,7 +230,7 @@ CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.c_se CREATE OR REPLACE FUNCTION notify_pm() RETURNS trigger AS $$ BEGIN INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) - SELECT 'pm', 't', tb.iid, t.id, NEW.num, t.title, NEw.uid + SELECT 'pm', 't', tb.iid, vndbid_num(t.id), NEW.num, t.title, NEW.uid FROM threads t JOIN threads_boards tb ON tb.tid = t.id WHERE t.id = NEW.tid @@ -241,7 +241,7 @@ BEGIN FROM notifications n WHERE n.uid = tb.iid AND n.ntype = 'pm' - AND n.iid = t.id + AND n.iid = vndbid_num(t.id) AND n.read IS NULL ); RETURN NULL; @@ -258,7 +258,7 @@ CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROC CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$ BEGIN INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) - SELECT 'announce', 't', u.id, t.id, 1, t.title, NEW.uid + SELECT 'announce', 't', u.id, vndbid_num(t.id), 1, t.title, NEW.uid FROM threads t JOIN threads_boards tb ON tb.tid = t.id -- get the users who want this announcement diff --git a/util/devdump.pl b/util/devdump.pl index af7bd776..73c79566 100755 --- a/util/devdump.pl +++ b/util/devdump.pl @@ -137,7 +137,7 @@ sub copy_entry { copy image_votes => "SELECT DISTINCT ON (id,uid%10) * FROM image_votes WHERE id IN($image_ids)", { uid => 'user' }; # Threads (announcements) - my $threads = join ',', @{ $db->selectcol_arrayref("SELECT tid FROM threads_boards b WHERE b.type = 'an'") }; + my $threads = join ',', map "'$_'", @{ $db->selectcol_arrayref("SELECT tid FROM threads_boards b WHERE b.type = 'an'") }; copy threads => "SELECT * FROM threads WHERE id IN($threads)"; copy threads_boards => "SELECT * FROM threads_boards WHERE tid IN($threads)"; copy threads_posts => "SELECT * FROM threads_posts WHERE tid IN($threads)", { uid => 'user' }; diff --git a/util/updates/2020-08-07-threads.sql b/util/updates/2020-08-07-threads.sql new file mode 100644 index 00000000..612ecaf0 --- /dev/null +++ b/util/updates/2020-08-07-threads.sql @@ -0,0 +1,30 @@ +-- * Convert thread identifiers to vndbids +-- * Remove threads_poll_votes.tid +-- * Add two ON DELETE CASCADE's + +ALTER TABLE threads_poll_votes DROP COLUMN tid; +ALTER TABLE threads_poll_votes ADD PRIMARY KEY (optid,uid); + +ALTER TABLE threads_poll_options DROP CONSTRAINT threads_poll_options_tid_fkey; +ALTER TABLE threads_poll_options ALTER COLUMN tid TYPE vndbid USING vndbid('t', tid); + +ALTER TABLE threads_boards DROP CONSTRAINT threads_boards_tid_fkey; +ALTER TABLE threads_boards ALTER COLUMN tid DROP DEFAULT; +ALTER TABLE threads_boards ALTER COLUMN tid TYPE vndbid USING vndbid('t', tid); + +ALTER TABLE threads DROP CONSTRAINT threads_id_fkey; +ALTER TABLE threads_posts DROP CONSTRAINT threads_posts_tid_fkey; +ALTER TABLE threads_posts ALTER COLUMN tid DROP DEFAULT; +ALTER TABLE threads_posts ALTER COLUMN tid TYPE vndbid USING vndbid('t', tid); + +ALTER TABLE threads ALTER COLUMN id DROP DEFAULT; +ALTER TABLE threads ALTER COLUMN id TYPE vndbid USING vndbid('t', id); +ALTER TABLE threads ALTER COLUMN id SET DEFAULT vndbid('t', nextval('threads_id_seq')::int); +ALTER TABLE threads ADD CONSTRAINT threads_id_check CHECK(vndbid_type(id) = 't'); + +ALTER TABLE threads ADD CONSTRAINT threads_id_fkey FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE threads_poll_options ADD CONSTRAINT threads_poll_options_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE; +ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE; +ALTER TABLE threads_boards ADD CONSTRAINT threads_boards_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE; + +\i sql/triggers.sql -- cgit v1.2.3