summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--elm/Discussions/Edit.elm4
-rw-r--r--elm/Discussions/Reply.elm2
-rw-r--r--lib/Multi/Feed.pm4
-rw-r--r--lib/Multi/IRC.pm6
-rw-r--r--lib/VNDB/Handler/Misc.pm2
-rw-r--r--lib/VNWeb/Discussions/Edit.pm17
-rw-r--r--lib/VNWeb/Discussions/Lib.pm4
-rw-r--r--lib/VNWeb/Discussions/Search.pm4
-rw-r--r--lib/VNWeb/Discussions/Thread.pm30
-rw-r--r--lib/VNWeb/Discussions/UPosts.pm4
-rw-r--r--lib/VNWeb/Prelude.pm4
-rw-r--r--sql/schema.sql14
-rw-r--r--sql/tableattrs.sql5
-rw-r--r--sql/triggers.sql6
-rwxr-xr-xutil/devdump.pl2
-rw-r--r--util/updates/2020-08-07-threads.sql30
16 files changed, 85 insertions, 53 deletions
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{(?<id>t$num)},
gid => qr{g$id},
imgid=> qr{(?<id>(?: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>$num)},
+ postid => qr{(?<id>t$num)\.(?<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
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-19 01:35:06 +0000