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 alter sequence user__id_seq rename to person_id_seq;
   8
   9 -- create a new table local_user
  10 create table local_user (
  11   id serial primary key,
  12   person_id int references person on update cascade on delete cascade not null,
  13   password_encrypted text not null,
  14   email text unique,
  15   admin boolean default false not null,
  16   show_nsfw boolean default false not null,
  17   theme character varying(20) default 'darkly'::character varying not null,
  18   default_sort_type smallint default 0 not null,
  19   default_listing_type smallint default 1 not null,
  20   lang character varying(20) default 'browser'::character varying not null,
  21   show_avatars boolean default true not null,
  22   send_notifications_to_email boolean default false not null,
  23   matrix_user_id text,
  24   unique (person_id)
  25 );
  26
  27 -- Copy the local users over to the new table
  28 insert into local_user
  29 (
  30   person_id,
  31   password_encrypted,
  32   email,
  33   admin,
  34   show_nsfw,
  35   theme,
  36   default_sort_type,
  37   default_listing_type,
  38   lang,
  39   show_avatars,
  40   send_notifications_to_email,
  41   matrix_user_id
  42 )
  43 select
  44   id,
  45   password_encrypted,
  46   email,
  47   admin,
  48   show_nsfw,
  49   theme,
  50   default_sort_type,
  51   default_listing_type,
  52   lang,
  53   show_avatars,
  54   send_notifications_to_email,
  55   matrix_user_id
  56 from person
  57 where local = true;
  58
  59 -- Drop those columns from person
  60 alter table person
  61   drop column password_encrypted,
  62   drop column email,
  63   drop column admin,
  64   drop column show_nsfw,
  65   drop column theme,
  66   drop column default_sort_type,
  67   drop column default_listing_type,
  68   drop column lang,
  69   drop column show_avatars,
  70   drop column send_notifications_to_email,
  71   drop column matrix_user_id;
  72
  73 -- Rename indexes
  74 alter index user__pkey rename to person__pkey;
  75 alter index idx_user_actor_id rename to idx_person_actor_id;
  76 alter index idx_user_inbox_url rename to idx_person_inbox_url;
  77 alter index idx_user_lower_actor_id rename to idx_person_lower_actor_id;
  78 alter index idx_user_published rename to idx_person_published;
  79
  80 -- Rename triggers
  81 alter trigger site_aggregates_user_delete on person rename to site_aggregates_person_delete;
  82 alter trigger site_aggregates_user_insert on person rename to site_aggregates_person_insert;
  83
  84 -- Rename the trigger functions
  85 alter function site_aggregates_user_delete() rename to site_aggregates_person_delete;
  86 alter function site_aggregates_user_insert() rename to site_aggregates_person_insert;
  87
  88 -- Create views
  89 create view person_alias_1 as select * from person;
  90 create view person_alias_2 as select * from person;
  91
  92 -- Redo user aggregates into person_aggregates
  93 alter table user_aggregates rename to person_aggregates;
  94 alter sequence user_aggregates_id_seq rename to person_aggregates_id_seq;
  95 alter table person_aggregates rename column user_id to person_id;
  96
  97 -- index
  98 alter index user_aggregates_pkey rename to person_aggregates_pkey;
  99 alter index idx_user_aggregates_comment_score rename to idx_person_aggregates_comment_score;
 100 alter index user_aggregates_user_id_key rename to person_aggregates_person_id_key;
 101 alter table person_aggregates rename constraint user_aggregates_user_id_fkey to person_aggregates_person_id_fkey;
 102
 103
 104 -- Drop all the old triggers and functions
 105 drop trigger user_aggregates_user on person;
 106 drop trigger user_aggregates_post_count on post;
 107 drop trigger user_aggregates_post_score on post_like;
 108 drop trigger user_aggregates_comment_count on comment;
 109 drop trigger user_aggregates_comment_score on comment_like;
 110 drop function
 111   user_aggregates_user,
 112   user_aggregates_post_count,
 113   user_aggregates_post_score,
 114   user_aggregates_comment_count,
 115   user_aggregates_comment_score;
 116
 117 -- initial user add
 118 create function person_aggregates_person()
 119 returns trigger language plpgsql
 120 as $$
 121 begin
 122   IF (TG_OP = 'INSERT') THEN
 123     insert into person_aggregates (person_id) values (NEW.id);
 124   ELSIF (TG_OP = 'DELETE') THEN
 125     delete from person_aggregates where person_id = OLD.id;
 126   END IF;
 127   return null;
 128 end $$;
 129
 130 create trigger person_aggregates_person
 131 after insert or delete on person
 132 for each row
 133 execute procedure person_aggregates_person();
 134
 135 -- post count
 136 create function person_aggregates_post_count()
 137 returns trigger language plpgsql
 138 as $$
 139 begin
 140   IF (TG_OP = 'INSERT') THEN
 141     update person_aggregates
 142     set post_count = post_count + 1 where person_id = NEW.creator_id;
 143
 144   ELSIF (TG_OP = 'DELETE') THEN
 145     update person_aggregates
 146     set post_count = post_count - 1 where person_id = OLD.creator_id;
 147
 148     -- If the post gets deleted, the score calculation trigger won't fire,
 149     -- so you need to re-calculate
 150     update person_aggregates ua
 151     set post_score = pd.score
 152     from (
 153       select u.id,
 154       coalesce(0, sum(pl.score)) as score
 155       -- User join because posts could be empty
 156       from person u
 157       left join post p on u.id = p.creator_id
 158       left join post_like pl on p.id = pl.post_id
 159       group by u.id
 160     ) pd
 161     where ua.person_id = OLD.creator_id;
 162
 163   END IF;
 164   return null;
 165 end $$;
 166
 167 create trigger person_aggregates_post_count
 168 after insert or delete on post
 169 for each row
 170 execute procedure person_aggregates_post_count();
 171
 172 -- post score
 173 create function person_aggregates_post_score()
 174 returns trigger language plpgsql
 175 as $$
 176 begin
 177   IF (TG_OP = 'INSERT') THEN
 178     -- Need to get the post creator, not the voter
 179     update person_aggregates ua
 180     set post_score = post_score + NEW.score
 181     from post p
 182     where ua.person_id = p.creator_id and p.id = NEW.post_id;
 183
 184   ELSIF (TG_OP = 'DELETE') THEN
 185     update person_aggregates ua
 186     set post_score = post_score - OLD.score
 187     from post p
 188     where ua.person_id = p.creator_id and p.id = OLD.post_id;
 189   END IF;
 190   return null;
 191 end $$;
 192
 193 create trigger person_aggregates_post_score
 194 after insert or delete on post_like
 195 for each row
 196 execute procedure person_aggregates_post_score();
 197
 198 -- comment count
 199 create function person_aggregates_comment_count()
 200 returns trigger language plpgsql
 201 as $$
 202 begin
 203   IF (TG_OP = 'INSERT') THEN
 204     update person_aggregates
 205     set comment_count = comment_count + 1 where person_id = NEW.creator_id;
 206   ELSIF (TG_OP = 'DELETE') THEN
 207     update person_aggregates
 208     set comment_count = comment_count - 1 where person_id = OLD.creator_id;
 209
 210     -- If the comment gets deleted, the score calculation trigger won't fire,
 211     -- so you need to re-calculate
 212     update person_aggregates ua
 213     set comment_score = cd.score
 214     from (
 215       select u.id,
 216       coalesce(0, sum(cl.score)) as score
 217       -- User join because comments could be empty
 218       from person u
 219       left join comment c on u.id = c.creator_id
 220       left join comment_like cl on c.id = cl.comment_id
 221       group by u.id
 222     ) cd
 223     where ua.person_id = OLD.creator_id;
 224   END IF;
 225   return null;
 226 end $$;
 227
 228 create trigger person_aggregates_comment_count
 229 after insert or delete on comment
 230 for each row
 231 execute procedure person_aggregates_comment_count();
 232
 233 -- comment score
 234 create function person_aggregates_comment_score()
 235 returns trigger language plpgsql
 236 as $$
 237 begin
 238   IF (TG_OP = 'INSERT') THEN
 239     -- Need to get the post creator, not the voter
 240     update person_aggregates ua
 241     set comment_score = comment_score + NEW.score
 242     from comment c
 243     where ua.person_id = c.creator_id and c.id = NEW.comment_id;
 244   ELSIF (TG_OP = 'DELETE') THEN
 245     update person_aggregates ua
 246     set comment_score = comment_score - OLD.score
 247     from comment c
 248     where ua.person_id = c.creator_id and c.id = OLD.comment_id;
 249   END IF;
 250   return null;
 251 end $$;
 252
 253 create trigger person_aggregates_comment_score
 254 after insert or delete on comment_like
 255 for each row
 256 execute procedure person_aggregates_comment_score();
 257
 258 -- person_mention
 259 alter table user_mention rename to person_mention;
 260 alter sequence user_mention_id_seq rename to person_mention_id_seq;
 261 alter index user_mention_pkey rename to person_mention_pkey;
 262 alter index user_mention_recipient_id_comment_id_key rename to person_mention_recipient_id_comment_id_key;
 263 alter table person_mention rename constraint user_mention_comment_id_fkey to person_mention_comment_id_fkey;
 264 alter table person_mention rename constraint user_mention_recipient_id_fkey to person_mention_recipient_id_fkey;
 265
 266 -- user_ban
 267 alter table user_ban rename to person_ban;
 268 alter sequence user_ban_id_seq rename to person_ban_id_seq;
 269 alter index user_ban_pkey rename to person_ban_pkey;
 270 alter index user_ban_user_id_key rename to person_ban_person_id_key;
 271 alter table person_ban rename column user_id to person_id;
 272 alter table person_ban rename constraint user_ban_user_id_fkey to person_ban_person_id_fkey;
 273
 274 -- comment_like
 275 alter table comment_like rename column user_id to person_id;
 276 alter index idx_comment_like_user rename to idx_comment_like_person;
 277 alter table comment_like rename constraint comment_like_comment_id_user_id_key to comment_like_comment_id_person_id_key;
 278 alter table comment_like rename constraint comment_like_user_id_fkey to comment_like_person_id_fkey;
 279
 280 -- comment_saved
 281 alter table comment_saved rename column user_id to person_id;
 282 alter table comment_saved rename constraint comment_saved_comment_id_user_id_key to comment_saved_comment_id_person_id_key;
 283 alter table comment_saved rename constraint comment_saved_user_id_fkey to comment_saved_person_id_fkey;
 284
 285 -- community_follower
 286 alter table community_follower rename column user_id to person_id;
 287 alter table community_follower rename constraint community_follower_community_id_user_id_key to community_follower_community_id_person_id_key;
 288 alter table community_follower rename constraint community_follower_user_id_fkey to community_follower_person_id_fkey;
 289
 290 -- community_moderator
 291 alter table community_moderator rename column user_id to person_id;
 292 alter table community_moderator rename constraint community_moderator_community_id_user_id_key to community_moderator_community_id_person_id_key;
 293 alter table community_moderator rename constraint community_moderator_user_id_fkey to community_moderator_person_id_fkey;
 294
 295 -- community_user_ban
 296 alter table community_user_ban rename to community_person_ban;
 297 alter sequence community_user_ban_id_seq rename to community_person_ban_id_seq;
 298 alter table community_person_ban rename column user_id to person_id;
 299 alter table community_person_ban rename constraint community_user_ban_pkey to community_person_ban_pkey;
 300 alter table community_person_ban rename constraint community_user_ban_community_id_fkey to community_person_ban_community_id_fkey;
 301 alter table community_person_ban rename constraint community_user_ban_community_id_user_id_key to community_person_ban_community_id_person_id_key;
 302 alter table community_person_ban rename constraint community_user_ban_user_id_fkey to community_person_ban_person_id_fkey;
 303
 304 -- mod_add
 305 alter table mod_add rename column mod_user_id to mod_person_id;
 306 alter table mod_add rename column other_user_id to other_person_id;
 307 alter table mod_add rename constraint mod_add_mod_user_id_fkey to mod_add_mod_person_id_fkey;
 308 alter table mod_add rename constraint mod_add_other_user_id_fkey to mod_add_other_person_id_fkey;
 309
 310 -- mod_add_community
 311 alter table mod_add_community rename column mod_user_id to mod_person_id;
 312 alter table mod_add_community rename column other_user_id to other_person_id;
 313 alter table mod_add_community rename constraint mod_add_community_mod_user_id_fkey to mod_add_community_mod_person_id_fkey;
 314 alter table mod_add_community rename constraint mod_add_community_other_user_id_fkey to mod_add_community_other_person_id_fkey;
 315
 316 -- mod_ban
 317 alter table mod_ban rename column mod_user_id to mod_person_id;
 318 alter table mod_ban rename column other_user_id to other_person_id;
 319 alter table mod_ban rename constraint mod_ban_mod_user_id_fkey to mod_ban_mod_person_id_fkey;
 320 alter table mod_ban rename constraint mod_ban_other_user_id_fkey to mod_ban_other_person_id_fkey;
 321
 322 -- mod_ban_community
 323 alter table mod_ban_from_community rename column mod_user_id to mod_person_id;
 324 alter table mod_ban_from_community rename column other_user_id to other_person_id;
 325 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;
 326 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;
 327
 328 -- mod_lock_post
 329 alter table mod_lock_post rename column mod_user_id to mod_person_id;
 330 alter table mod_lock_post rename constraint mod_lock_post_mod_user_id_fkey to mod_lock_post_mod_person_id_fkey;
 331
 332 -- mod_remove_comment
 333 alter table mod_remove_comment rename column mod_user_id to mod_person_id;
 334 alter table mod_remove_comment rename constraint mod_remove_comment_mod_user_id_fkey to mod_remove_comment_mod_person_id_fkey;
 335
 336 -- mod_remove_community
 337 alter table mod_remove_community rename column mod_user_id to mod_person_id;
 338 alter table mod_remove_community rename constraint mod_remove_community_mod_user_id_fkey to mod_remove_community_mod_person_id_fkey;
 339
 340 -- mod_remove_post
 341 alter table mod_remove_post rename column mod_user_id to mod_person_id;
 342 alter table mod_remove_post rename constraint mod_remove_post_mod_user_id_fkey to mod_remove_post_mod_person_id_fkey;
 343
 344 -- mod_sticky_post
 345 alter table mod_sticky_post rename column mod_user_id to mod_person_id;
 346 alter table mod_sticky_post rename constraint mod_sticky_post_mod_user_id_fkey to mod_sticky_post_mod_person_id_fkey;
 347
 348 -- password_reset_request
 349 delete from password_reset_request;
 350 alter table password_reset_request drop column user_id;
 351 alter table password_reset_request add column local_user_id integer not null references local_user(id) on update cascade on delete cascade;
 352
 353 -- post_like
 354 alter table post_like rename column user_id to person_id;
 355 alter index idx_post_like_user rename to idx_post_like_person;
 356 alter table post_like rename constraint post_like_post_id_user_id_key to post_like_post_id_person_id_key;
 357 alter table post_like rename constraint post_like_user_id_fkey to post_like_person_id_fkey;
 358
 359 -- post_read
 360 alter table post_read rename column user_id to person_id;
 361 alter table post_read rename constraint post_read_post_id_user_id_key to post_read_post_id_person_id_key;
 362 alter table post_read rename constraint post_read_user_id_fkey to post_read_person_id_fkey;
 363
 364 -- post_saved
 365 alter table post_saved rename column user_id to person_id;
 366 alter table post_saved rename constraint post_saved_post_id_user_id_key to post_saved_post_id_person_id_key;
 367 alter table post_saved rename constraint post_saved_user_id_fkey to post_saved_person_id_fkey;
 368
 369 -- redo site aggregates trigger
 370 create or replace function site_aggregates_activity(i text) returns integer
 371     language plpgsql
 372     as $$
 373 declare
 374    count_ integer;
 375 begin
 376   select count(*)
 377   into count_
 378   from (
 379     select c.creator_id from comment c
 380     inner join person u on c.creator_id = u.id
 381     where c.published > ('now'::timestamp - i::interval)
 382     and u.local = true
 383     union
 384     select p.creator_id from post p
 385     inner join person u on p.creator_id = u.id
 386     where p.published > ('now'::timestamp - i::interval)
 387     and u.local = true
 388   ) a;
 389   return count_;
 390 end;
 391 $$;