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