mirror of
https://github.com/LemmyNet/lemmy.git
synced 2025-09-02 11:13:51 +00:00
644a448aa9
* [0.19] Fixing active counts slow queries. (#5907) * Fixing active counts slow queries. * Simplify back to str tuple * Batch site and community updates * Using update from temp table * Making aggs temp table use interval name. * Make dev setup use optimized postgres. * Addressing PR comments. * Use ref * Removing system custom info from customPostgresql.conf * Forgot to remove old scheduled tasks. * Making sure migrations aren't missing anything from release/v0.19 Checked using git diff --diff-filter D --stat release/v0.19 migrations * Rename all migrations to come after release/v0.19 migrations * Add liked_at is not null check.
98 lines
3.3 KiB
SQL
98 lines
3.3 KiB
SQL
CREATE TABLE multi_community (
|
|
id serial PRIMARY KEY,
|
|
creator_id int NOT NULL REFERENCES person ON UPDATE CASCADE ON DELETE CASCADE,
|
|
instance_id int NOT NULL REFERENCES instance ON UPDATE CASCADE ON DELETE CASCADE,
|
|
name varchar(255) NOT NULL,
|
|
title varchar(255),
|
|
description varchar(255),
|
|
local bool NOT NULL DEFAULT TRUE,
|
|
deleted bool NOT NULL DEFAULT FALSE,
|
|
ap_id text UNIQUE NOT NULL DEFAULT generate_unique_changeme (),
|
|
public_key text NOT NULL,
|
|
private_key text,
|
|
inbox_url text NOT NULL DEFAULT generate_unique_changeme (),
|
|
last_refreshed_at timestamptz NOT NULL DEFAULT now(),
|
|
following_url text NOT NULL DEFAULT generate_unique_changeme (),
|
|
published_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz
|
|
);
|
|
|
|
CREATE TABLE multi_community_entry (
|
|
multi_community_id int NOT NULL REFERENCES multi_community ON UPDATE CASCADE ON DELETE CASCADE,
|
|
community_id int NOT NULL REFERENCES community ON UPDATE CASCADE ON DELETE CASCADE,
|
|
PRIMARY KEY (multi_community_id, community_id)
|
|
);
|
|
|
|
CREATE TABLE multi_community_follow (
|
|
multi_community_id int NOT NULL REFERENCES multi_community ON UPDATE CASCADE ON DELETE CASCADE,
|
|
person_id int NOT NULL REFERENCES person ON UPDATE CASCADE ON DELETE CASCADE,
|
|
follow_state community_follower_state NOT NULL,
|
|
PRIMARY KEY (person_id, multi_community_id)
|
|
);
|
|
|
|
ALTER TABLE local_site
|
|
ADD COLUMN suggested_communities int REFERENCES multi_community ON UPDATE CASCADE ON DELETE CASCADE,
|
|
ADD COLUMN multi_comm_follower int REFERENCES person ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
-- generate new account with randomized name (max 20 chars) and set it
|
|
-- as local_site.multi_comm_follower
|
|
WITH x AS (
|
|
INSERT INTO person (name, public_key, private_key, instance_id, inbox_url, bot_account)
|
|
SELECT
|
|
'multicomm' || substr(gen_random_uuid ()::text, 0, 11),
|
|
public_key,
|
|
private_key,
|
|
instance_id,
|
|
inbox_url,
|
|
TRUE
|
|
FROM
|
|
site,
|
|
local_site
|
|
WHERE
|
|
site.id = local_site.id
|
|
RETURNING
|
|
person.id)
|
|
UPDATE
|
|
local_site
|
|
SET
|
|
multi_comm_follower = x.id
|
|
FROM
|
|
x;
|
|
|
|
ALTER TABLE local_site
|
|
ALTER COLUMN multi_comm_follower SET NOT NULL;
|
|
|
|
-- set ap_id for multicomm follower account (should use r.local_url but thats not defined here)
|
|
UPDATE
|
|
person
|
|
SET
|
|
ap_id = current_setting('lemmy.protocol_and_hostname') || '/u/' || person.name
|
|
FROM
|
|
local_site
|
|
WHERE
|
|
person.id = local_site.multi_comm_follower;
|
|
|
|
ALTER TYPE listing_type_enum
|
|
ADD VALUE 'Suggested';
|
|
|
|
CREATE INDEX idx_multi_community_read_from_name ON multi_community (local)
|
|
WHERE
|
|
local AND NOT deleted;
|
|
|
|
CREATE INDEX idx_multi_community_ap_id ON multi_community (ap_id);
|
|
|
|
CREATE INDEX idx_multi_creator_id ON multi_community (creator_id);
|
|
|
|
CREATE INDEX idx_multi_community_follow_multi_id ON multi_community_follow (multi_community_id);
|
|
|
|
CREATE INDEX idx_multi_community_entry_community_id ON multi_community_entry (community_id);
|
|
|
|
ALTER TABLE search_combined
|
|
ADD COLUMN multi_community_id int REFERENCES multi_community (id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
ALTER TABLE search_combined
|
|
DROP CONSTRAINT search_combined_check;
|
|
|
|
ALTER TABLE search_combined
|
|
ADD CONSTRAINT search_combined_check CHECK (num_nonnulls (post_id, comment_id, community_id, person_id, multi_community_id) = 1);
|
|
|