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

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