migrations/2021-02-14-041356_split_user_table/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   user_id int references person on update cascade on delete cascade not null,
  13   password_encrypted text not null,
  14   email text,
  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 (user_id)
  25 );
  26
  27 -- Copy the local users over to the new table
  28 insert into local_user
  29 (
  30   user_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 constraint user_ban_user_id_fkey to person_ban_person_id_fkey;
 272
 273