lemmy/migrations/2024-02-24-034523_replaceable-schema/up.sql
dullbananas 4ba6221e04
Move SQL triggers from migrations into reusable sql file (#4333)
* stuff

* stuff including batch_upsert function

* stuff

* do things

* stuff

* different timestamps

* stuff

* Revert changes to comment.rs

* Update comment.rs

* Update comment.rs

* Update post_view.rs

* Update utils.rs

* Update up.sql

* Update up.sql

* Update down.sql

* Update up.sql

* Update main.rs

* use anyhow macro

* Create down.sql

* Create up.sql

* Create replaceable_schema.sql

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Update utils.rs

* Update .woodpecker.yml

* Update sql_format_check.sh

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Create dump_schema.sh

* Update start_dev_db.sh

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* stuff

* Update replaceable_schema.sql

* Update .pg_format

* fmt

* stuff

* stuff (#21)

* Update replaceable_schema.sql

* Update up.sql

* Update replaceable_schema.sql

* fmt

* update cargo.lock

* stuff

* Update replaceable_schema.sql

* Remove truncate trigger because truncate is already restricted by foreign keys

* Update replaceable_schema.sql

* fix some things

* Update replaceable_schema.sql

* Update replaceable_schema.sql

* Update .woodpecker.yml

* stuff

* fix TG_OP

* Psql env vars

* try to fix combine_transition_tables parse error

* Revert "try to fix combine_transition_tables parse error"

This reverts commit 75d00a4626.

* refactor combine_transition_tables

* try to fix create_triggers

* fix some things

* try to fix combined_transition_tables

* fix sql errors

* update comment count in post trigger

* fmt

* Revert "fmt"

This reverts commit a5bcd0834b.

* Revert "update comment count in post trigger"

This reverts commit 0066a4b42b.

* fix everything

* Update replaceable_schema.sql

* actually fix everything

* refactor create_triggers

* fix

* add semicolons

* add is_counted function and fix incorrect bool operator in update_comment_count_from_post

* refactor comment trigger

* refactor post trigger

* fix

* Delete crates/db_schema/src/utils/series.rs

* subscribers_local

* edit migrations

* move migrations

* remove utils::series module declaration

* fix everything

* stuff

* Move sql to schema_setup dir

* utils.sql

* delete .pg_format

* Update .woodpecker.yml

* Update sql_format_check.sh

* Update .woodpecker.yml

* Merge remote-tracking branch 'upstream/main' into bliss

* fmt

* Create main.rs

* Update lib.rs

* Update main.rs

* Update .woodpecker.yml

* Update main.rs

* Update Cargo.toml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update triggers.sql

* YAY

* Update mod.rs

* Update Cargo.toml

* a

* Update Cargo.toml

* Update Cargo.toml

* Delete crates/db_schema/src/main.rs

* Update Cargo.toml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update utils.sql

* Update utils.sql

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update down.sql

* Update up.sql

* Update triggers.sql

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update triggers.sql

* Update down.sql

* Update .woodpecker.yml

* Update Cargo.toml

* Update .woodpecker.yml

* Update Cargo.toml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update .woodpecker.yml

* Update mod.rs

* Update Cargo.toml

* Update mod.rs

* make dump_schema.sh executable

* fix dump_schema.sh

* defer

* diff dumps

* fmt

* Update utils.sql

* Update .woodpecker.yml

* use correct version for pg_dump

* Update .woodpecker.yml

* Update .woodpecker.yml

* change migration date

* atomic site_aggregates insert

* temporarily repeat tests in CI

* drop r schema in CI migration check

* show ReceivedActivity::create error

* move check_diesel_migration CI step

* Update .woodpecker.yml

* Update scheduled_tasks.rs

* Update scheduled_tasks.rs

* update cargo.lock

* move sql files

* move rank functions

* filter post_aggregates update

* fmt

* cargo fmt

* replace post_id with id

* update cargo.lock

* avoid locking rows that need no change in up.sql

* only run replaceable_schema if migrations were run

* debug ci test failure

* make replaceable_schema work in CI

* Update .woodpecker.yml

* remove println

* Use migration revert and git checkout

* Update schema_setup.rs

* Fix

* Update schema_setup.rs

* Update schema_setup.rs

* Update .woodpecker.yml

---------

Co-authored-by: Nutomic <me@nutomic.com>
Co-authored-by: Dessalines <dessalines@users.noreply.github.com>
2024-04-17 20:58:44 -04:00

81 lines
3.1 KiB
SQL

CREATE UNIQUE INDEX idx_site_aggregates_1_row_only ON site_aggregates ((TRUE));
-- Drop functions and use `CASCADE` to drop the triggers that use them
DROP FUNCTION comment_aggregates_comment, comment_aggregates_score, community_aggregates_comment_count, community_aggregates_community, community_aggregates_post_count, community_aggregates_post_count_insert, community_aggregates_subscriber_count, delete_follow_before_person, person_aggregates_comment_count, person_aggregates_comment_score, person_aggregates_person, person_aggregates_post_count, person_aggregates_post_insert, person_aggregates_post_score, post_aggregates_comment_count, post_aggregates_featured_community, post_aggregates_featured_local, post_aggregates_post, post_aggregates_score, site_aggregates_comment_delete, site_aggregates_comment_insert, site_aggregates_community_delete, site_aggregates_community_insert, site_aggregates_person_delete, site_aggregates_person_insert, site_aggregates_post_delete, site_aggregates_post_insert, site_aggregates_post_update, site_aggregates_site, was_removed_or_deleted, was_restored_or_created CASCADE;
-- Drop rank functions
DROP FUNCTION controversy_rank, scaled_rank, hot_rank;
-- Defer constraints
ALTER TABLE comment_aggregates
ALTER CONSTRAINT comment_aggregates_comment_id_fkey INITIALLY DEFERRED;
ALTER TABLE community_aggregates
ALTER CONSTRAINT community_aggregates_community_id_fkey INITIALLY DEFERRED;
ALTER TABLE person_aggregates
ALTER CONSTRAINT person_aggregates_person_id_fkey INITIALLY DEFERRED;
ALTER TABLE post_aggregates
ALTER CONSTRAINT post_aggregates_community_id_fkey INITIALLY DEFERRED,
ALTER CONSTRAINT post_aggregates_creator_id_fkey INITIALLY DEFERRED,
ALTER CONSTRAINT post_aggregates_instance_id_fkey INITIALLY DEFERRED,
ALTER CONSTRAINT post_aggregates_post_id_fkey INITIALLY DEFERRED;
ALTER TABLE site_aggregates
ALTER CONSTRAINT site_aggregates_site_id_fkey INITIALLY DEFERRED;
-- Fix values that might be incorrect because of the old triggers
UPDATE
post_aggregates
SET
featured_local = post.featured_local,
featured_community = post.featured_community
FROM
post
WHERE
post_aggregates.post_id = post.id
AND (post_aggregates.featured_local,
post_aggregates.featured_community) != (post.featured_local,
post.featured_community);
UPDATE
community_aggregates
SET
comments = counted.comments
FROM (
SELECT
community_id,
count(*) AS comments
FROM
comment,
LATERAL (
SELECT
*
FROM
post
WHERE
post.id = comment.post_id
LIMIT 1) AS post
WHERE
NOT (comment.deleted
OR comment.removed
OR post.deleted
OR post.removed)
GROUP BY
community_id) AS counted
WHERE
community_aggregates.community_id = counted.community_id
AND community_aggregates.comments != counted.comments;
UPDATE
site_aggregates
SET
communities = (
SELECT
count(*)
FROM
community
WHERE
local);