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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
130 person_aggregates_person,
131 person_aggregates_post_count,
132 person_aggregates_post_score,
133 person_aggregates_comment_count,
134 person_aggregates_comment_score;
138 drop view person_alias_1, person_alias_2;
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;
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;
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;
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;
159 -- Add the columns back in
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;
173 -- Update the user_ table with the local_user data
175 password_encrypted = lu.password_encrypted,
178 show_nsfw = lu.show_nsfw,
180 default_sort_type = lu.default_sort_type,
181 default_listing_type = lu.default_listing_type,
183 show_avatars = lu.show_avatars,
184 send_notifications_to_email = lu.send_notifications_to_email,
185 matrix_user_id = lu.matrix_user_id
187 where lu.person_id = u.id;
189 create view user_alias_1 as select * from user_;
190 create view user_alias_2 as select * from user_;
192 drop table local_user;
194 -- Add the user_aggregates table triggers
197 create function user_aggregates_user()
198 returns trigger language plpgsql
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;
209 create trigger user_aggregates_user
210 after insert or delete on user_
212 execute procedure user_aggregates_user();
215 create function user_aggregates_post_count()
216 returns trigger language plpgsql
219 IF (TG_OP = 'INSERT') THEN
220 update user_aggregates
221 set post_count = post_count + 1 where user_id = NEW.creator_id;
223 ELSIF (TG_OP = 'DELETE') THEN
224 update user_aggregates
225 set post_count = post_count - 1 where user_id = OLD.creator_id;
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
233 coalesce(0, sum(pl.score)) as score
234 -- User join because posts could be empty
236 left join post p on u.id = p.creator_id
237 left join post_like pl on p.id = pl.post_id
240 where ua.user_id = OLD.creator_id;
246 create trigger user_aggregates_post_count
247 after insert or delete on post
249 execute procedure user_aggregates_post_count();
252 create function user_aggregates_post_score()
253 returns trigger language plpgsql
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
261 where ua.user_id = p.creator_id and p.id = NEW.post_id;
263 ELSIF (TG_OP = 'DELETE') THEN
264 update user_aggregates ua
265 set post_score = post_score - OLD.score
267 where ua.user_id = p.creator_id and p.id = OLD.post_id;
272 create trigger user_aggregates_post_score
273 after insert or delete on post_like
275 execute procedure user_aggregates_post_score();
278 create function user_aggregates_comment_count()
279 returns trigger language plpgsql
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;
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
295 coalesce(0, sum(cl.score)) as score
296 -- User join because comments could be empty
298 left join comment c on u.id = c.creator_id
299 left join comment_like cl on c.id = cl.comment_id
302 where ua.user_id = OLD.creator_id;
307 create trigger user_aggregates_comment_count
308 after insert or delete on comment
310 execute procedure user_aggregates_comment_count();
313 create function user_aggregates_comment_score()
314 returns trigger language plpgsql
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
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
327 where ua.user_id = c.creator_id and c.id = OLD.comment_id;
332 create trigger user_aggregates_comment_score
333 after insert or delete on comment_like
335 execute procedure user_aggregates_comment_score();
337 -- redo site aggregates trigger
338 create or replace function site_aggregates_activity(i text) returns integer
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)
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)