summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2020-08-05 16:48:25 +0200
committerYorhel <git@yorhel.nl>2020-08-06 13:30:16 +0200
commit96084614898f30f06b63fe121b36ba1a25c4f9e7 (patch)
tree068424a36fd3afa66857e0925a5c353419002323 /util
parentad0819cc292b25190233e837b8b653ba77710ddd (diff)
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.
Diffstat (limited to 'util')
-rwxr-xr-xutil/devdump.pl2
-rw-r--r--util/updates/2020-08-07-threads.sql30
2 files changed, 31 insertions, 1 deletions
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