migrations/2021-02-14-041356_split_user_table/down.sql

   1 -- user_ban
   2 alter table person_ban rename to user_ban;
   3 alter sequence person_ban_id_seq rename to user_ban_id_seq;
   4 alter index person_ban_pkey rename to user_ban_pkey;
   5 alter index person_ban_person_id_key rename to user_ban_user_id_key;
   6 alter table user_ban rename constraint person_ban_person_id_fkey to user_ban_user_id_fkey;
   7
   8 -- user_mention
   9 alter table person_mention rename to user_mention;
  10 alter sequence person_mention_id_seq rename to user_mention_id_seq;
  11 alter index person_mention_pkey rename to user_mention_pkey;
  12 alter index person_mention_recipient_id_comment_id_key rename to user_mention_recipient_id_comment_id_key;
  13 alter table user_mention rename constraint person_mention_comment_id_fkey to user_mention_comment_id_fkey;
  14 alter table user_mention rename constraint person_mention_recipient_id_fkey to user_mention_recipient_id_fkey;
  15
  16 -- User aggregates table
  17 alter table person_aggregates rename to user_aggregates;
  18 alter sequence person_aggregates_id_seq rename to user_aggregates_id_seq;
  19 alter table user_aggregates rename column person_id to user_id;
  20
  21 -- Indexes
  22 alter index person_aggregates_pkey rename to user_aggregates_pkey;
  23 alter index idx_person_aggregates_comment_score rename to idx_user_aggregates_comment_score;
  24 alter index person_aggregates_person_id_key rename to user_aggregates_user_id_key;
  25 alter table user_aggregates rename constraint person_aggregates_person_id_fkey to user_aggregates_user_id_fkey;
  26
  27 -- Redo the user_aggregates table
  28 drop trigger person_aggregates_person on person;
  29 drop trigger person_aggregates_post_count on post;
  30 drop trigger person_aggregates_post_score on post_like;
  31 drop trigger person_aggregates_comment_count on comment;
  32 drop trigger person_aggregates_comment_score on comment_like;
  33 drop function
  34   person_aggregates_person,
  35   person_aggregates_post_count,
  36   person_aggregates_post_score,
  37   person_aggregates_comment_count,
  38   person_aggregates_comment_score;
  39
  40 -- user_ table
  41 -- Drop views
  42 drop view person_alias_1, person_alias_2;
  43
  44 -- Rename indexes
  45 alter index person__pkey rename to user__pkey;
  46 alter index idx_person_actor_id rename to idx_user_actor_id;
  47 alter index idx_person_inbox_url rename to idx_user_inbox_url;
  48 alter index idx_person_lower_actor_id rename to idx_user_lower_actor_id;
  49 alter index idx_person_published rename to idx_user_published;
  50
  51 -- Rename triggers
  52 alter trigger site_aggregates_person_delete on person rename to site_aggregates_user_delete;
  53 alter trigger site_aggregates_person_insert on person rename to site_aggregates_user_insert;
  54
  55 -- Rename the trigger functions
  56 alter function site_aggregates_person_delete() rename to site_aggregates_user_delete;
  57 alter function site_aggregates_person_insert() rename to site_aggregates_user_insert;
  58
  59 -- Rename the table back to user_
  60 alter table person rename to user_;
  61 alter sequence person_id_seq rename to user__id_seq;
  62
  63 -- Add the columns back in
  64 alter table user_
  65   add column password_encrypted text not null default 'changeme',
  66   add column email text,
  67   add column admin boolean default false not null,
  68   add column show_nsfw boolean default false not null,
  69   add column theme character varying(20) default 'darkly'::character varying not null,
  70   add column default_sort_type smallint default 0 not null,
  71   add column default_listing_type smallint default 1 not null,
  72   add column lang character varying(20) default 'browser'::character varying not null,
  73   add column show_avatars boolean default true not null,
  74   add column send_notifications_to_email boolean default false not null,
  75   add column matrix_user_id text;
  76
  77 -- Update the user_ table with the local_user data
  78 update user_ u set
  79   password_encrypted = lu.password_encrypted,
  80   email = lu.email,
  81   admin = lu.admin,
  82   show_nsfw = lu.show_nsfw,
  83   theme = lu.theme,
  84   default_sort_type = lu.default_sort_type,
  85   default_listing_type = lu.default_listing_type,
  86   lang = lu.lang,
  87   show_avatars = lu.show_avatars,
  88   send_notifications_to_email = lu.send_notifications_to_email,
  89   matrix_user_id = lu.matrix_user_id
  90 from local_user lu
  91 where lu.user_id = u.id;
  92
  93 create view user_alias_1 as select * from user_;
  94 create view user_alias_2 as select * from user_;
  95
  96 drop table local_user;
  97
  98 -- Add the user_aggregates table triggers
  99
 100 -- initial user add
 101 create function user_aggregates_user()
 102 returns trigger language plpgsql
 103 as $$
 104 begin
 105   IF (TG_OP = 'INSERT') THEN
 106     insert into user_aggregates (user_id) values (NEW.id);
 107   ELSIF (TG_OP = 'DELETE') THEN
 108     delete from user_aggregates where user_id = OLD.id;
 109   END IF;
 110   return null;
 111 end $$;
 112
 113 create trigger user_aggregates_user
 114 after insert or delete on user_
 115 for each row
 116 execute procedure user_aggregates_user();
 117
 118 -- post count
 119 create function user_aggregates_post_count()
 120 returns trigger language plpgsql
 121 as $$
 122 begin
 123   IF (TG_OP = 'INSERT') THEN
 124     update user_aggregates
 125     set post_count = post_count + 1 where user_id = NEW.creator_id;
 126
 127   ELSIF (TG_OP = 'DELETE') THEN
 128     update user_aggregates
 129     set post_count = post_count - 1 where user_id = OLD.creator_id;
 130
 131     -- If the post gets deleted, the score calculation trigger won't fire,
 132     -- so you need to re-calculate
 133     update user_aggregates ua
 134     set post_score = pd.score
 135     from (
 136       select u.id,
 137       coalesce(0, sum(pl.score)) as score
 138       -- User join because posts could be empty
 139       from user_ u
 140       left join post p on u.id = p.creator_id
 141       left join post_like pl on p.id = pl.post_id
 142       group by u.id
 143     ) pd
 144     where ua.user_id = OLD.creator_id;
 145
 146   END IF;
 147   return null;
 148 end $$;
 149
 150 create trigger user_aggregates_post_count
 151 after insert or delete on post
 152 for each row
 153 execute procedure user_aggregates_post_count();
 154
 155 -- post score
 156 create function user_aggregates_post_score()
 157 returns trigger language plpgsql
 158 as $$
 159 begin
 160   IF (TG_OP = 'INSERT') THEN
 161     -- Need to get the post creator, not the voter
 162     update user_aggregates ua
 163     set post_score = post_score + NEW.score
 164     from post p
 165     where ua.user_id = p.creator_id and p.id = NEW.post_id;
 166
 167   ELSIF (TG_OP = 'DELETE') THEN
 168     update user_aggregates ua
 169     set post_score = post_score - OLD.score
 170     from post p
 171     where ua.user_id = p.creator_id and p.id = OLD.post_id;
 172   END IF;
 173   return null;
 174 end $$;
 175
 176 create trigger user_aggregates_post_score
 177 after insert or delete on post_like
 178 for each row
 179 execute procedure user_aggregates_post_score();
 180
 181 -- comment count
 182 create function user_aggregates_comment_count()
 183 returns trigger language plpgsql
 184 as $$
 185 begin
 186   IF (TG_OP = 'INSERT') THEN
 187     update user_aggregates
 188     set comment_count = comment_count + 1 where user_id = NEW.creator_id;
 189   ELSIF (TG_OP = 'DELETE') THEN
 190     update user_aggregates
 191     set comment_count = comment_count - 1 where user_id = OLD.creator_id;
 192
 193     -- If the comment gets deleted, the score calculation trigger won't fire,
 194     -- so you need to re-calculate
 195     update user_aggregates ua
 196     set comment_score = cd.score
 197     from (
 198       select u.id,
 199       coalesce(0, sum(cl.score)) as score
 200       -- User join because comments could be empty
 201       from user_ u
 202       left join comment c on u.id = c.creator_id
 203       left join comment_like cl on c.id = cl.comment_id
 204       group by u.id
 205     ) cd
 206     where ua.user_id = OLD.creator_id;
 207   END IF;
 208   return null;
 209 end $$;
 210
 211 create trigger user_aggregates_comment_count
 212 after insert or delete on comment
 213 for each row
 214 execute procedure user_aggregates_comment_count();
 215
 216 -- comment score
 217 create function user_aggregates_comment_score()
 218 returns trigger language plpgsql
 219 as $$
 220 begin
 221   IF (TG_OP = 'INSERT') THEN
 222     -- Need to get the post creator, not the voter
 223     update user_aggregates ua
 224     set comment_score = comment_score + NEW.score
 225     from comment c
 226     where ua.user_id = c.creator_id and c.id = NEW.comment_id;
 227   ELSIF (TG_OP = 'DELETE') THEN
 228     update user_aggregates ua
 229     set comment_score = comment_score - OLD.score
 230     from comment c
 231     where ua.user_id = c.creator_id and c.id = OLD.comment_id;
 232   END IF;
 233   return null;
 234 end $$;
 235
 236 create trigger user_aggregates_comment_score
 237 after insert or delete on comment_like
 238 for each row
 239 execute procedure user_aggregates_comment_score();
 240
 241
 242