migrations/2021-03-09-171136_split_user_table_2/down.sql

   1 -- post_saved
   2 ALTER TABLE post_saved RENAME COLUMN person_id TO user_id;
   3
   4 ALTER TABLE post_saved RENAME CONSTRAINT post_saved_post_id_person_id_key TO post_saved_post_id_user_id_key;
   5
   6 ALTER TABLE post_saved RENAME CONSTRAINT post_saved_person_id_fkey TO post_saved_user_id_fkey;
   7
   8 -- post_read
   9 ALTER TABLE post_read RENAME COLUMN person_id TO user_id;
  10
  11 ALTER TABLE post_read RENAME CONSTRAINT post_read_post_id_person_id_key TO post_read_post_id_user_id_key;
  12
  13 ALTER TABLE post_read RENAME CONSTRAINT post_read_person_id_fkey TO post_read_user_id_fkey;
  14
  15 -- post_like
  16 ALTER TABLE post_like RENAME COLUMN person_id TO user_id;
  17
  18 ALTER INDEX idx_post_like_person RENAME TO idx_post_like_user;
  19
  20 ALTER TABLE post_like RENAME CONSTRAINT post_like_post_id_person_id_key TO post_like_post_id_user_id_key;
  21
  22 ALTER TABLE post_like RENAME CONSTRAINT post_like_person_id_fkey TO post_like_user_id_fkey;
  23
  24 -- password_reset_request
  25 DELETE FROM password_reset_request;
  26
  27 ALTER TABLE password_reset_request
  28     DROP COLUMN local_user_id;
  29
  30 ALTER TABLE password_reset_request
  31     ADD COLUMN user_id integer NOT NULL REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE;
  32
  33 -- mod_sticky_post
  34 ALTER TABLE mod_sticky_post RENAME COLUMN mod_person_id TO mod_user_id;
  35
  36 ALTER TABLE mod_sticky_post RENAME CONSTRAINT mod_sticky_post_mod_person_id_fkey TO mod_sticky_post_mod_user_id_fkey;
  37
  38 -- mod_remove_post
  39 ALTER TABLE mod_remove_post RENAME COLUMN mod_person_id TO mod_user_id;
  40
  41 ALTER TABLE mod_remove_post RENAME CONSTRAINT mod_remove_post_mod_person_id_fkey TO mod_remove_post_mod_user_id_fkey;
  42
  43 -- mod_remove_community
  44 ALTER TABLE mod_remove_community RENAME COLUMN mod_person_id TO mod_user_id;
  45
  46 ALTER TABLE mod_remove_community RENAME CONSTRAINT mod_remove_community_mod_person_id_fkey TO mod_remove_community_mod_user_id_fkey;
  47
  48 -- mod_remove_comment
  49 ALTER TABLE mod_remove_comment RENAME COLUMN mod_person_id TO mod_user_id;
  50
  51 ALTER TABLE mod_remove_comment RENAME CONSTRAINT mod_remove_comment_mod_person_id_fkey TO mod_remove_comment_mod_user_id_fkey;
  52
  53 -- mod_lock_post
  54 ALTER TABLE mod_lock_post RENAME COLUMN mod_person_id TO mod_user_id;
  55
  56 ALTER TABLE mod_lock_post RENAME CONSTRAINT mod_lock_post_mod_person_id_fkey TO mod_lock_post_mod_user_id_fkey;
  57
  58 -- mod_add_community
  59 ALTER TABLE mod_ban_from_community RENAME COLUMN mod_person_id TO mod_user_id;
  60
  61 ALTER TABLE mod_ban_from_community RENAME COLUMN other_person_id TO other_user_id;
  62
  63 ALTER TABLE mod_ban_from_community RENAME CONSTRAINT mod_ban_from_community_mod_person_id_fkey TO mod_ban_from_community_mod_user_id_fkey;
  64
  65 ALTER TABLE mod_ban_from_community RENAME CONSTRAINT mod_ban_from_community_other_person_id_fkey TO mod_ban_from_community_other_user_id_fkey;
  66
  67 -- mod_ban
  68 ALTER TABLE mod_ban RENAME COLUMN mod_person_id TO mod_user_id;
  69
  70 ALTER TABLE mod_ban RENAME COLUMN other_person_id TO other_user_id;
  71
  72 ALTER TABLE mod_ban RENAME CONSTRAINT mod_ban_mod_person_id_fkey TO mod_ban_mod_user_id_fkey;
  73
  74 ALTER TABLE mod_ban RENAME CONSTRAINT mod_ban_other_person_id_fkey TO mod_ban_other_user_id_fkey;
  75
  76 -- mod_add_community
  77 ALTER TABLE mod_add_community RENAME COLUMN mod_person_id TO mod_user_id;
  78
  79 ALTER TABLE mod_add_community RENAME COLUMN other_person_id TO other_user_id;
  80
  81 ALTER TABLE mod_add_community RENAME CONSTRAINT mod_add_community_mod_person_id_fkey TO mod_add_community_mod_user_id_fkey;
  82
  83 ALTER TABLE mod_add_community RENAME CONSTRAINT mod_add_community_other_person_id_fkey TO mod_add_community_other_user_id_fkey;
  84
  85 -- mod_add
  86 ALTER TABLE mod_add RENAME COLUMN mod_person_id TO mod_user_id;
  87
  88 ALTER TABLE mod_add RENAME COLUMN other_person_id TO other_user_id;
  89
  90 ALTER TABLE mod_add RENAME CONSTRAINT mod_add_mod_person_id_fkey TO mod_add_mod_user_id_fkey;
  91
  92 ALTER TABLE mod_add RENAME CONSTRAINT mod_add_other_person_id_fkey TO mod_add_other_user_id_fkey;
  93
  94 -- community_user_ban
  95 ALTER TABLE community_person_ban RENAME TO community_user_ban;
  96
  97 ALTER SEQUENCE community_person_ban_id_seq
  98     RENAME TO community_user_ban_id_seq;
  99
 100 ALTER TABLE community_user_ban RENAME COLUMN person_id TO user_id;
 101
 102 ALTER TABLE community_user_ban RENAME CONSTRAINT community_person_ban_pkey TO community_user_ban_pkey;
 103
 104 ALTER TABLE community_user_ban RENAME CONSTRAINT community_person_ban_community_id_fkey TO community_user_ban_community_id_fkey;
 105
 106 ALTER TABLE community_user_ban RENAME CONSTRAINT community_person_ban_community_id_person_id_key TO community_user_ban_community_id_user_id_key;
 107
 108 ALTER TABLE community_user_ban RENAME CONSTRAINT community_person_ban_person_id_fkey TO community_user_ban_user_id_fkey;
 109
 110 -- community_moderator
 111 ALTER TABLE community_moderator RENAME COLUMN person_id TO user_id;
 112
 113 ALTER TABLE community_moderator RENAME CONSTRAINT community_moderator_community_id_person_id_key TO community_moderator_community_id_user_id_key;
 114
 115 ALTER TABLE community_moderator RENAME CONSTRAINT community_moderator_person_id_fkey TO community_moderator_user_id_fkey;
 116
 117 -- community_follower
 118 ALTER TABLE community_follower RENAME COLUMN person_id TO user_id;
 119
 120 ALTER TABLE community_follower RENAME CONSTRAINT community_follower_community_id_person_id_key TO community_follower_community_id_user_id_key;
 121
 122 ALTER TABLE community_follower RENAME CONSTRAINT community_follower_person_id_fkey TO community_follower_user_id_fkey;
 123
 124 -- comment_saved
 125 ALTER TABLE comment_saved RENAME COLUMN person_id TO user_id;
 126
 127 ALTER TABLE comment_saved RENAME CONSTRAINT comment_saved_comment_id_person_id_key TO comment_saved_comment_id_user_id_key;
 128
 129 ALTER TABLE comment_saved RENAME CONSTRAINT comment_saved_person_id_fkey TO comment_saved_user_id_fkey;
 130
 131 -- comment_like
 132 ALTER TABLE comment_like RENAME COLUMN person_id TO user_id;
 133
 134 ALTER INDEX idx_comment_like_person RENAME TO idx_comment_like_user;
 135
 136 ALTER TABLE comment_like RENAME CONSTRAINT comment_like_comment_id_person_id_key TO comment_like_comment_id_user_id_key;
 137
 138 ALTER TABLE comment_like RENAME CONSTRAINT comment_like_person_id_fkey TO comment_like_user_id_fkey;
 139
 140 -- user_ban
 141 ALTER TABLE person_ban RENAME TO user_ban;
 142
 143 ALTER SEQUENCE person_ban_id_seq
 144     RENAME TO user_ban_id_seq;
 145
 146 ALTER INDEX person_ban_pkey RENAME TO user_ban_pkey;
 147
 148 ALTER INDEX person_ban_person_id_key RENAME TO user_ban_user_id_key;
 149
 150 ALTER TABLE user_ban RENAME COLUMN person_id TO user_id;
 151
 152 ALTER TABLE user_ban RENAME CONSTRAINT person_ban_person_id_fkey TO user_ban_user_id_fkey;
 153
 154 -- user_mention
 155 ALTER TABLE person_mention RENAME TO user_mention;
 156
 157 ALTER SEQUENCE person_mention_id_seq
 158     RENAME TO user_mention_id_seq;
 159
 160 ALTER INDEX person_mention_pkey RENAME TO user_mention_pkey;
 161
 162 ALTER INDEX person_mention_recipient_id_comment_id_key RENAME TO user_mention_recipient_id_comment_id_key;
 163
 164 ALTER TABLE user_mention RENAME CONSTRAINT person_mention_comment_id_fkey TO user_mention_comment_id_fkey;
 165
 166 ALTER TABLE user_mention RENAME CONSTRAINT person_mention_recipient_id_fkey TO user_mention_recipient_id_fkey;
 167
 168 -- User aggregates table
 169 ALTER TABLE person_aggregates RENAME TO user_aggregates;
 170
 171 ALTER SEQUENCE person_aggregates_id_seq
 172     RENAME TO user_aggregates_id_seq;
 173
 174 ALTER TABLE user_aggregates RENAME COLUMN person_id TO user_id;
 175
 176 -- Indexes
 177 ALTER INDEX person_aggregates_pkey RENAME TO user_aggregates_pkey;
 178
 179 ALTER INDEX idx_person_aggregates_comment_score RENAME TO idx_user_aggregates_comment_score;
 180
 181 ALTER INDEX person_aggregates_person_id_key RENAME TO user_aggregates_user_id_key;
 182
 183 ALTER TABLE user_aggregates RENAME CONSTRAINT person_aggregates_person_id_fkey TO user_aggregates_user_id_fkey;
 184
 185 -- Redo the user_aggregates table
 186 DROP TRIGGER person_aggregates_person ON person;
 187
 188 DROP TRIGGER person_aggregates_post_count ON post;
 189
 190 DROP TRIGGER person_aggregates_post_score ON post_like;
 191
 192 DROP TRIGGER person_aggregates_comment_count ON comment;
 193
 194 DROP TRIGGER person_aggregates_comment_score ON comment_like;
 195
 196 DROP FUNCTION person_aggregates_person, person_aggregates_post_count, person_aggregates_post_score, person_aggregates_comment_count, person_aggregates_comment_score;
 197
 198 -- user_ table
 199 -- Drop views
 200 DROP VIEW person_alias_1, person_alias_2;
 201
 202 -- Rename indexes
 203 ALTER INDEX person__pkey RENAME TO user__pkey;
 204
 205 ALTER INDEX idx_person_actor_id RENAME TO idx_user_actor_id;
 206
 207 ALTER INDEX idx_person_inbox_url RENAME TO idx_user_inbox_url;
 208
 209 ALTER INDEX idx_person_lower_actor_id RENAME TO idx_user_lower_actor_id;
 210
 211 ALTER INDEX idx_person_published RENAME TO idx_user_published;
 212
 213 -- Rename triggers
 214 ALTER TRIGGER site_aggregates_person_delete ON person RENAME TO site_aggregates_user_delete;
 215
 216 ALTER TRIGGER site_aggregates_person_insert ON person RENAME TO site_aggregates_user_insert;
 217
 218 -- Rename the trigger functions
 219 ALTER FUNCTION site_aggregates_person_delete () RENAME TO site_aggregates_user_delete;
 220
 221 ALTER FUNCTION site_aggregates_person_insert () RENAME TO site_aggregates_user_insert;
 222
 223 -- Rename the table back to user_
 224 ALTER TABLE person RENAME TO user_;
 225
 226 ALTER SEQUENCE person_id_seq
 227     RENAME TO user__id_seq;
 228
 229 -- Add the columns back in
 230 ALTER TABLE user_
 231     ADD COLUMN password_encrypted text NOT NULL DEFAULT 'changeme',
 232     ADD COLUMN email text,
 233     ADD COLUMN admin boolean DEFAULT FALSE NOT NULL,
 234     ADD COLUMN show_nsfw boolean DEFAULT FALSE NOT NULL,
 235     ADD COLUMN theme character varying(20) DEFAULT 'darkly'::character varying NOT NULL,
 236     ADD COLUMN default_sort_type smallint DEFAULT 0 NOT NULL,
 237     ADD COLUMN default_listing_type smallint DEFAULT 1 NOT NULL,
 238     ADD COLUMN lang character varying(20) DEFAULT 'browser'::character varying NOT NULL,
 239     ADD COLUMN show_avatars boolean DEFAULT TRUE NOT NULL,
 240     ADD COLUMN send_notifications_to_email boolean DEFAULT FALSE NOT NULL,
 241     ADD COLUMN matrix_user_id text;
 242
 243 -- Update the user_ table with the local_user data
 244 UPDATE
 245     user_ u
 246 SET
 247     password_encrypted = lu.password_encrypted,
 248     email = lu.email,
 249     admin = lu.admin,
 250     show_nsfw = lu.show_nsfw,
 251     theme = lu.theme,
 252     default_sort_type = lu.default_sort_type,
 253     default_listing_type = lu.default_listing_type,
 254     lang = lu.lang,
 255     show_avatars = lu.show_avatars,
 256     send_notifications_to_email = lu.send_notifications_to_email,
 257     matrix_user_id = lu.matrix_user_id
 258 FROM
 259     local_user lu
 260 WHERE
 261     lu.person_id = u.id;
 262
 263 CREATE VIEW user_alias_1 AS
 264 SELECT
 265     *
 266 FROM
 267     user_;
 268
 269 CREATE VIEW user_alias_2 AS
 270 SELECT
 271     *
 272 FROM
 273     user_;
 274
 275 DROP TABLE local_user;
 276
 277 -- Add the user_aggregates table triggers
 278 -- initial user add
 279 CREATE FUNCTION user_aggregates_user ()
 280     RETURNS TRIGGER
 281     LANGUAGE plpgsql
 282     AS $$
 283 BEGIN
 284     IF (TG_OP = 'INSERT') THEN
 285         INSERT INTO user_aggregates (user_id)
 286             VALUES (NEW.id);
 287     ELSIF (TG_OP = 'DELETE') THEN
 288         DELETE FROM user_aggregates
 289         WHERE user_id = OLD.id;
 290     END IF;
 291     RETURN NULL;
 292 END
 293 $$;
 294
 295 CREATE TRIGGER user_aggregates_user
 296     AFTER INSERT OR DELETE ON user_
 297     FOR EACH ROW
 298     EXECUTE PROCEDURE user_aggregates_user ();
 299
 300 -- post count
 301 CREATE FUNCTION user_aggregates_post_count ()
 302     RETURNS TRIGGER
 303     LANGUAGE plpgsql
 304     AS $$
 305 BEGIN
 306     IF (TG_OP = 'INSERT') THEN
 307         UPDATE
 308             user_aggregates
 309         SET
 310             post_count = post_count + 1
 311         WHERE
 312             user_id = NEW.creator_id;
 313     ELSIF (TG_OP = 'DELETE') THEN
 314         UPDATE
 315             user_aggregates
 316         SET
 317             post_count = post_count - 1
 318         WHERE
 319             user_id = OLD.creator_id;
 320         -- If the post gets deleted, the score calculation trigger won't fire,
 321         -- so you need to re-calculate
 322         UPDATE
 323             user_aggregates ua
 324         SET
 325             post_score = pd.score
 326         FROM (
 327             SELECT
 328                 u.id,
 329                 coalesce(0, sum(pl.score)) AS score
 330                 -- User join because posts could be empty
 331             FROM
 332                 user_ u
 333             LEFT JOIN post p ON u.id = p.creator_id
 334             LEFT JOIN post_like pl ON p.id = pl.post_id
 335         GROUP BY
 336             u.id) pd
 337     WHERE
 338         ua.user_id = OLD.creator_id;
 339     END IF;
 340     RETURN NULL;
 341 END
 342 $$;
 343
 344 CREATE TRIGGER user_aggregates_post_count
 345     AFTER INSERT OR DELETE ON post
 346     FOR EACH ROW
 347     EXECUTE PROCEDURE user_aggregates_post_count ();
 348
 349 -- post score
 350 CREATE FUNCTION user_aggregates_post_score ()
 351     RETURNS TRIGGER
 352     LANGUAGE plpgsql
 353     AS $$
 354 BEGIN
 355     IF (TG_OP = 'INSERT') THEN
 356         -- Need to get the post creator, not the voter
 357         UPDATE
 358             user_aggregates ua
 359         SET
 360             post_score = post_score + NEW.score
 361         FROM
 362             post p
 363         WHERE
 364             ua.user_id = p.creator_id
 365             AND p.id = NEW.post_id;
 366     ELSIF (TG_OP = 'DELETE') THEN
 367         UPDATE
 368             user_aggregates ua
 369         SET
 370             post_score = post_score - OLD.score
 371         FROM
 372             post p
 373         WHERE
 374             ua.user_id = p.creator_id
 375             AND p.id = OLD.post_id;
 376     END IF;
 377     RETURN NULL;
 378 END
 379 $$;
 380
 381 CREATE TRIGGER user_aggregates_post_score
 382     AFTER INSERT OR DELETE ON post_like
 383     FOR EACH ROW
 384     EXECUTE PROCEDURE user_aggregates_post_score ();
 385
 386 -- comment count
 387 CREATE FUNCTION user_aggregates_comment_count ()
 388     RETURNS TRIGGER
 389     LANGUAGE plpgsql
 390     AS $$
 391 BEGIN
 392     IF (TG_OP = 'INSERT') THEN
 393         UPDATE
 394             user_aggregates
 395         SET
 396             comment_count = comment_count + 1
 397         WHERE
 398             user_id = NEW.creator_id;
 399     ELSIF (TG_OP = 'DELETE') THEN
 400         UPDATE
 401             user_aggregates
 402         SET
 403             comment_count = comment_count - 1
 404         WHERE
 405             user_id = OLD.creator_id;
 406         -- If the comment gets deleted, the score calculation trigger won't fire,
 407         -- so you need to re-calculate
 408         UPDATE
 409             user_aggregates ua
 410         SET
 411             comment_score = cd.score
 412         FROM (
 413             SELECT
 414                 u.id,
 415                 coalesce(0, sum(cl.score)) AS score
 416                 -- User join because comments could be empty
 417             FROM
 418                 user_ u
 419             LEFT JOIN comment c ON u.id = c.creator_id
 420             LEFT JOIN comment_like cl ON c.id = cl.comment_id
 421         GROUP BY
 422             u.id) cd
 423     WHERE
 424         ua.user_id = OLD.creator_id;
 425     END IF;
 426     RETURN NULL;
 427 END
 428 $$;
 429
 430 CREATE TRIGGER user_aggregates_comment_count
 431     AFTER INSERT OR DELETE ON comment
 432     FOR EACH ROW
 433     EXECUTE PROCEDURE user_aggregates_comment_count ();
 434
 435 -- comment score
 436 CREATE FUNCTION user_aggregates_comment_score ()
 437     RETURNS TRIGGER
 438     LANGUAGE plpgsql
 439     AS $$
 440 BEGIN
 441     IF (TG_OP = 'INSERT') THEN
 442         -- Need to get the post creator, not the voter
 443         UPDATE
 444             user_aggregates ua
 445         SET
 446             comment_score = comment_score + NEW.score
 447         FROM
 448             comment c
 449         WHERE
 450             ua.user_id = c.creator_id
 451             AND c.id = NEW.comment_id;
 452     ELSIF (TG_OP = 'DELETE') THEN
 453         UPDATE
 454             user_aggregates ua
 455         SET
 456             comment_score = comment_score - OLD.score
 457         FROM
 458             comment c
 459         WHERE
 460             ua.user_id = c.creator_id
 461             AND c.id = OLD.comment_id;
 462     END IF;
 463     RETURN NULL;
 464 END
 465 $$;
 466
 467 CREATE TRIGGER user_aggregates_comment_score
 468     AFTER INSERT OR DELETE ON comment_like
 469     FOR EACH ROW
 470     EXECUTE PROCEDURE user_aggregates_comment_score ();
 471
 472 -- redo site aggregates trigger
 473 CREATE OR REPLACE FUNCTION site_aggregates_activity (i text)
 474     RETURNS integer
 475     LANGUAGE plpgsql
 476     AS $$
 477 DECLARE
 478     count_ integer;
 479 BEGIN
 480     SELECT
 481         count(*) INTO count_
 482     FROM (
 483         SELECT
 484             c.creator_id
 485         FROM
 486             comment c
 487             INNER JOIN user_ u ON c.creator_id = u.id
 488         WHERE
 489             c.published > ('now'::timestamp - i::interval)
 490             AND u.local = TRUE
 491         UNION
 492         SELECT
 493             p.creator_id
 494         FROM
 495             post p
 496             INNER JOIN user_ u ON p.creator_id = u.id
 497         WHERE
 498             p.published > ('now'::timestamp - i::interval)
 499             AND u.local = TRUE) a;
 500     RETURN count_;
 501 END;
 502 $$;
 503