lemmy/migrations/2024-12-02-181601_add_report_combined_table/up.sql

43 lines
1.2 KiB
MySQL
Raw Permalink Normal View History

Adding `report_combined` table. (#5231) * Combined tables try 2 * Finishing up combined report table. * Fix ts optionals. * Adding tests, triggers, and history updates for report_combined. * Adding profile. * Add cursor pagination to report_combined view (#5244) * add pagination cursor * store timestamp instead of id in cursor (partial) * Revert "store timestamp instead of id in cursor (partial)" This reverts commit 89359dde4bc5fee39fdd2840828330f398444a36. * use paginated query builder * Fixing migration and paged API. * Using dullbananas trigger procedure * Removing pointless list routes, reorganizing tests. * Fixing column XOR check. * Forgot to remove list report actions. * Cleanup. * Use internal tagging. * Fixing api tests. * Adding a few indexes. * Fixing migration name. * Fixing unique constraints. * Addressing PR comments. * Fixing api tests. * Update crates/db_views/src/report_combined_view.rs Co-authored-by: dullbananas <dull.bananas0@gmail.com> * Update crates/db_views/src/report_combined_view.rs Co-authored-by: dullbananas <dull.bananas0@gmail.com> * Update crates/db_views/src/report_combined_view.rs Co-authored-by: dullbananas <dull.bananas0@gmail.com> * Update migrations/2024-12-02-181601_add_report_combined_table/up.sql Co-authored-by: dullbananas <dull.bananas0@gmail.com> * Update migrations/2024-12-02-181601_add_report_combined_table/up.sql Co-authored-by: dullbananas <dull.bananas0@gmail.com> * Fixing import and fmt. * Fixing null types in postgres. * Comment out err. * Addressing PR comments. * Removing serialization --------- Co-authored-by: dullbananas <dull.bananas0@gmail.com>
2024-12-19 22:22:31 +00:00
-- Creates combined tables for
-- Reports: (comment, post, and private_message)
CREATE TABLE report_combined (
id serial PRIMARY KEY,
published timestamptz NOT NULL,
post_report_id int UNIQUE REFERENCES post_report ON UPDATE CASCADE ON DELETE CASCADE,
comment_report_id int UNIQUE REFERENCES comment_report ON UPDATE CASCADE ON DELETE CASCADE,
private_message_report_id int UNIQUE REFERENCES private_message_report ON UPDATE CASCADE ON DELETE CASCADE,
-- Make sure only one of the columns is not null
CHECK (num_nonnulls (post_report_id, comment_report_id, private_message_report_id) = 1)
);
CREATE INDEX idx_report_combined_published ON report_combined (published DESC, id DESC);
CREATE INDEX idx_report_combined_published_asc ON report_combined (reverse_timestamp_sort (published) DESC, id DESC);
-- Updating the history
INSERT INTO report_combined (published, post_report_id, comment_report_id, private_message_report_id)
SELECT
published,
id,
NULL::int,
NULL::int
FROM
post_report
UNION ALL
SELECT
published,
NULL::int,
id,
NULL::int
FROM
comment_report
UNION ALL
SELECT
published,
NULL::int,
NULL::int,
id
FROM
private_message_report;