diff options
Diffstat (limited to 'util')
-rwxr-xr-x | util/devdump.pl | 2 | ||||
-rw-r--r-- | util/updates/2020-08-07-threads.sql | 30 |
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 |