2 -- Drop the 2 views user_alias_1, user_alias_2
3 drop view user_alias_1, user_alias_2;
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;
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,
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,
27 -- Copy the local users over to the new table
28 insert into local_user
40 send_notifications_to_email,
54 send_notifications_to_email,
59 -- Drop those columns from person
61 drop column password_encrypted,
64 drop column show_nsfw,
66 drop column default_sort_type,
67 drop column default_listing_type,
69 drop column show_avatars,
70 drop column send_notifications_to_email,
71 drop column matrix_user_id;
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;
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;
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;
89 create view person_alias_1 as select * from person;
90 create view person_alias_2 as select * from person;
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;
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;
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;
111 user_aggregates_user,
112 user_aggregates_post_count,
113 user_aggregates_post_score,
114 user_aggregates_comment_count,
115 user_aggregates_comment_score;
118 create function person_aggregates_person()
119 returns trigger language plpgsql
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;
130 create trigger person_aggregates_person
131 after insert or delete on person
133 execute procedure person_aggregates_person();
136 create function person_aggregates_post_count()
137 returns trigger language plpgsql
140 IF (TG_OP = 'INSERT') THEN
141 update person_aggregates
142 set post_count = post_count + 1 where person_id = NEW.creator_id;
144 ELSIF (TG_OP = 'DELETE') THEN
145 update person_aggregates
146 set post_count = post_count - 1 where person_id = OLD.creator_id;
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
154 coalesce(0, sum(pl.score)) as score
155 -- User join because posts could be empty
157 left join post p on u.id = p.creator_id
158 left join post_like pl on p.id = pl.post_id
161 where ua.person_id = OLD.creator_id;
167 create trigger person_aggregates_post_count
168 after insert or delete on post
170 execute procedure person_aggregates_post_count();
173 create function person_aggregates_post_score()
174 returns trigger language plpgsql
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
182 where ua.person_id = p.creator_id and p.id = NEW.post_id;
184 ELSIF (TG_OP = 'DELETE') THEN
185 update person_aggregates ua
186 set post_score = post_score - OLD.score
188 where ua.person_id = p.creator_id and p.id = OLD.post_id;
193 create trigger person_aggregates_post_score
194 after insert or delete on post_like
196 execute procedure person_aggregates_post_score();
199 create function person_aggregates_comment_count()
200 returns trigger language plpgsql
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;
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
216 coalesce(0, sum(cl.score)) as score
217 -- User join because comments could be empty
219 left join comment c on u.id = c.creator_id
220 left join comment_like cl on c.id = cl.comment_id
223 where ua.person_id = OLD.creator_id;
228 create trigger person_aggregates_comment_count
229 after insert or delete on comment
231 execute procedure person_aggregates_comment_count();
234 create function person_aggregates_comment_score()
235 returns trigger language plpgsql
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
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
248 where ua.person_id = c.creator_id and c.id = OLD.comment_id;
253 create trigger person_aggregates_comment_score
254 after insert or delete on comment_like
256 execute procedure person_aggregates_comment_score();
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
369 -- redo site aggregates trigger
370 create or replace function site_aggregates_activity(i text) returns integer
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)
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)