2 ALTER TABLE post_saved RENAME COLUMN person_id TO user_id;
4 ALTER TABLE post_saved RENAME CONSTRAINT post_saved_post_id_person_id_key TO post_saved_post_id_user_id_key;
6 ALTER TABLE post_saved RENAME CONSTRAINT post_saved_person_id_fkey TO post_saved_user_id_fkey;
9 ALTER TABLE post_read RENAME COLUMN person_id TO user_id;
11 ALTER TABLE post_read RENAME CONSTRAINT post_read_post_id_person_id_key TO post_read_post_id_user_id_key;
13 ALTER TABLE post_read RENAME CONSTRAINT post_read_person_id_fkey TO post_read_user_id_fkey;
16 ALTER TABLE post_like RENAME COLUMN person_id TO user_id;
18 ALTER INDEX idx_post_like_person RENAME TO idx_post_like_user;
20 ALTER TABLE post_like RENAME CONSTRAINT post_like_post_id_person_id_key TO post_like_post_id_user_id_key;
22 ALTER TABLE post_like RENAME CONSTRAINT post_like_person_id_fkey TO post_like_user_id_fkey;
24 -- password_reset_request
25 DELETE FROM password_reset_request;
27 ALTER TABLE password_reset_request
28 DROP COLUMN local_user_id;
30 ALTER TABLE password_reset_request
31 ADD COLUMN user_id integer NOT NULL REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE;
34 ALTER TABLE mod_sticky_post RENAME COLUMN mod_person_id TO mod_user_id;
36 ALTER TABLE mod_sticky_post RENAME CONSTRAINT mod_sticky_post_mod_person_id_fkey TO mod_sticky_post_mod_user_id_fkey;
39 ALTER TABLE mod_remove_post RENAME COLUMN mod_person_id TO mod_user_id;
41 ALTER TABLE mod_remove_post RENAME CONSTRAINT mod_remove_post_mod_person_id_fkey TO mod_remove_post_mod_user_id_fkey;
43 -- mod_remove_community
44 ALTER TABLE mod_remove_community RENAME COLUMN mod_person_id TO mod_user_id;
46 ALTER TABLE mod_remove_community RENAME CONSTRAINT mod_remove_community_mod_person_id_fkey TO mod_remove_community_mod_user_id_fkey;
49 ALTER TABLE mod_remove_comment RENAME COLUMN mod_person_id TO mod_user_id;
51 ALTER TABLE mod_remove_comment RENAME CONSTRAINT mod_remove_comment_mod_person_id_fkey TO mod_remove_comment_mod_user_id_fkey;
54 ALTER TABLE mod_lock_post RENAME COLUMN mod_person_id TO mod_user_id;
56 ALTER TABLE mod_lock_post RENAME CONSTRAINT mod_lock_post_mod_person_id_fkey TO mod_lock_post_mod_user_id_fkey;
59 ALTER TABLE mod_ban_from_community RENAME COLUMN mod_person_id TO mod_user_id;
61 ALTER TABLE mod_ban_from_community RENAME COLUMN other_person_id TO other_user_id;
63 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;
65 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;
68 ALTER TABLE mod_ban RENAME COLUMN mod_person_id TO mod_user_id;
70 ALTER TABLE mod_ban RENAME COLUMN other_person_id TO other_user_id;
72 ALTER TABLE mod_ban RENAME CONSTRAINT mod_ban_mod_person_id_fkey TO mod_ban_mod_user_id_fkey;
74 ALTER TABLE mod_ban RENAME CONSTRAINT mod_ban_other_person_id_fkey TO mod_ban_other_user_id_fkey;
77 ALTER TABLE mod_add_community RENAME COLUMN mod_person_id TO mod_user_id;
79 ALTER TABLE mod_add_community RENAME COLUMN other_person_id TO other_user_id;
81 ALTER TABLE mod_add_community RENAME CONSTRAINT mod_add_community_mod_person_id_fkey TO mod_add_community_mod_user_id_fkey;
83 ALTER TABLE mod_add_community RENAME CONSTRAINT mod_add_community_other_person_id_fkey TO mod_add_community_other_user_id_fkey;
86 ALTER TABLE mod_add RENAME COLUMN mod_person_id TO mod_user_id;
88 ALTER TABLE mod_add RENAME COLUMN other_person_id TO other_user_id;
90 ALTER TABLE mod_add RENAME CONSTRAINT mod_add_mod_person_id_fkey TO mod_add_mod_user_id_fkey;
92 ALTER TABLE mod_add RENAME CONSTRAINT mod_add_other_person_id_fkey TO mod_add_other_user_id_fkey;
95 ALTER TABLE community_person_ban RENAME TO community_user_ban;
97 ALTER SEQUENCE community_person_ban_id_seq
98 RENAME TO community_user_ban_id_seq;
100 ALTER TABLE community_user_ban RENAME COLUMN person_id TO user_id;
102 ALTER TABLE community_user_ban RENAME CONSTRAINT community_person_ban_pkey TO community_user_ban_pkey;
104 ALTER TABLE community_user_ban RENAME CONSTRAINT community_person_ban_community_id_fkey TO community_user_ban_community_id_fkey;
106 ALTER TABLE community_user_ban RENAME CONSTRAINT community_person_ban_community_id_person_id_key TO community_user_ban_community_id_user_id_key;
108 ALTER TABLE community_user_ban RENAME CONSTRAINT community_person_ban_person_id_fkey TO community_user_ban_user_id_fkey;
110 -- community_moderator
111 ALTER TABLE community_moderator RENAME COLUMN person_id TO user_id;
113 ALTER TABLE community_moderator RENAME CONSTRAINT community_moderator_community_id_person_id_key TO community_moderator_community_id_user_id_key;
115 ALTER TABLE community_moderator RENAME CONSTRAINT community_moderator_person_id_fkey TO community_moderator_user_id_fkey;
117 -- community_follower
118 ALTER TABLE community_follower RENAME COLUMN person_id TO user_id;
120 ALTER TABLE community_follower RENAME CONSTRAINT community_follower_community_id_person_id_key TO community_follower_community_id_user_id_key;
122 ALTER TABLE community_follower RENAME CONSTRAINT community_follower_person_id_fkey TO community_follower_user_id_fkey;
125 ALTER TABLE comment_saved RENAME COLUMN person_id TO user_id;
127 ALTER TABLE comment_saved RENAME CONSTRAINT comment_saved_comment_id_person_id_key TO comment_saved_comment_id_user_id_key;
129 ALTER TABLE comment_saved RENAME CONSTRAINT comment_saved_person_id_fkey TO comment_saved_user_id_fkey;
132 ALTER TABLE comment_like RENAME COLUMN person_id TO user_id;
134 ALTER INDEX idx_comment_like_person RENAME TO idx_comment_like_user;
136 ALTER TABLE comment_like RENAME CONSTRAINT comment_like_comment_id_person_id_key TO comment_like_comment_id_user_id_key;
138 ALTER TABLE comment_like RENAME CONSTRAINT comment_like_person_id_fkey TO comment_like_user_id_fkey;
141 ALTER TABLE person_ban RENAME TO user_ban;
143 ALTER SEQUENCE person_ban_id_seq
144 RENAME TO user_ban_id_seq;
146 ALTER INDEX person_ban_pkey RENAME TO user_ban_pkey;
148 ALTER INDEX person_ban_person_id_key RENAME TO user_ban_user_id_key;
150 ALTER TABLE user_ban RENAME COLUMN person_id TO user_id;
152 ALTER TABLE user_ban RENAME CONSTRAINT person_ban_person_id_fkey TO user_ban_user_id_fkey;
155 ALTER TABLE person_mention RENAME TO user_mention;
157 ALTER SEQUENCE person_mention_id_seq
158 RENAME TO user_mention_id_seq;
160 ALTER INDEX person_mention_pkey RENAME TO user_mention_pkey;
162 ALTER INDEX person_mention_recipient_id_comment_id_key RENAME TO user_mention_recipient_id_comment_id_key;
164 ALTER TABLE user_mention RENAME CONSTRAINT person_mention_comment_id_fkey TO user_mention_comment_id_fkey;
166 ALTER TABLE user_mention RENAME CONSTRAINT person_mention_recipient_id_fkey TO user_mention_recipient_id_fkey;
168 -- User aggregates table
169 ALTER TABLE person_aggregates RENAME TO user_aggregates;
171 ALTER SEQUENCE person_aggregates_id_seq
172 RENAME TO user_aggregates_id_seq;
174 ALTER TABLE user_aggregates RENAME COLUMN person_id TO user_id;
177 ALTER INDEX person_aggregates_pkey RENAME TO user_aggregates_pkey;
179 ALTER INDEX idx_person_aggregates_comment_score RENAME TO idx_user_aggregates_comment_score;
181 ALTER INDEX person_aggregates_person_id_key RENAME TO user_aggregates_user_id_key;
183 ALTER TABLE user_aggregates RENAME CONSTRAINT person_aggregates_person_id_fkey TO user_aggregates_user_id_fkey;
185 -- Redo the user_aggregates table
186 DROP TRIGGER person_aggregates_person ON person;
188 DROP TRIGGER person_aggregates_post_count ON post;
190 DROP TRIGGER person_aggregates_post_score ON post_like;
192 DROP TRIGGER person_aggregates_comment_count ON comment;
194 DROP TRIGGER person_aggregates_comment_score ON comment_like;
196 DROP FUNCTION person_aggregates_person, person_aggregates_post_count, person_aggregates_post_score, person_aggregates_comment_count, person_aggregates_comment_score;
200 DROP VIEW person_alias_1, person_alias_2;
203 ALTER INDEX person__pkey RENAME TO user__pkey;
205 ALTER INDEX idx_person_actor_id RENAME TO idx_user_actor_id;
207 ALTER INDEX idx_person_inbox_url RENAME TO idx_user_inbox_url;
209 ALTER INDEX idx_person_lower_actor_id RENAME TO idx_user_lower_actor_id;
211 ALTER INDEX idx_person_published RENAME TO idx_user_published;
214 ALTER TRIGGER site_aggregates_person_delete ON person RENAME TO site_aggregates_user_delete;
216 ALTER TRIGGER site_aggregates_person_insert ON person RENAME TO site_aggregates_user_insert;
218 -- Rename the trigger functions
219 ALTER FUNCTION site_aggregates_person_delete () RENAME TO site_aggregates_user_delete;
221 ALTER FUNCTION site_aggregates_person_insert () RENAME TO site_aggregates_user_insert;
223 -- Rename the table back to user_
224 ALTER TABLE person RENAME TO user_;
226 ALTER SEQUENCE person_id_seq
227 RENAME TO user__id_seq;
229 -- Add the columns back in
231 ADD COLUMN password_encrypted text NOT NULL DEFAULT 'changeme',
232 ADD COLUMN email text,
233 ADD COLUMN admin boolean DEFAULT FALSE NOT NULL,
234 ADD COLUMN show_nsfw boolean DEFAULT FALSE NOT NULL,
235 ADD COLUMN theme character varying(20) DEFAULT 'darkly'::character varying NOT NULL,
236 ADD COLUMN default_sort_type smallint DEFAULT 0 NOT NULL,
237 ADD COLUMN default_listing_type smallint DEFAULT 1 NOT NULL,
238 ADD COLUMN lang character varying(20) DEFAULT 'browser'::character varying NOT NULL,
239 ADD COLUMN show_avatars boolean DEFAULT TRUE NOT NULL,
240 ADD COLUMN send_notifications_to_email boolean DEFAULT FALSE NOT NULL,
241 ADD COLUMN matrix_user_id text;
243 -- Update the user_ table with the local_user data
247 password_encrypted = lu.password_encrypted,
250 show_nsfw = lu.show_nsfw,
252 default_sort_type = lu.default_sort_type,
253 default_listing_type = lu.default_listing_type,
255 show_avatars = lu.show_avatars,
256 send_notifications_to_email = lu.send_notifications_to_email,
257 matrix_user_id = lu.matrix_user_id
263 CREATE VIEW user_alias_1 AS
269 CREATE VIEW user_alias_2 AS
275 DROP TABLE local_user;
277 -- Add the user_aggregates table triggers
279 CREATE FUNCTION user_aggregates_user ()
284 IF (TG_OP = 'INSERT') THEN
285 INSERT INTO user_aggregates (user_id)
287 ELSIF (TG_OP = 'DELETE') THEN
288 DELETE FROM user_aggregates
289 WHERE user_id = OLD.id;
295 CREATE TRIGGER user_aggregates_user
296 AFTER INSERT OR DELETE ON user_
298 EXECUTE PROCEDURE user_aggregates_user ();
301 CREATE FUNCTION user_aggregates_post_count ()
306 IF (TG_OP = 'INSERT') THEN
310 post_count = post_count + 1
312 user_id = NEW.creator_id;
313 ELSIF (TG_OP = 'DELETE') THEN
317 post_count = post_count - 1
319 user_id = OLD.creator_id;
320 -- If the post gets deleted, the score calculation trigger won't fire,
321 -- so you need to re-calculate
325 post_score = pd.score
329 coalesce(0, sum(pl.score)) AS score
330 -- User join because posts could be empty
333 LEFT JOIN post p ON u.id = p.creator_id
334 LEFT JOIN post_like pl ON p.id = pl.post_id
338 ua.user_id = OLD.creator_id;
344 CREATE TRIGGER user_aggregates_post_count
345 AFTER INSERT OR DELETE ON post
347 EXECUTE PROCEDURE user_aggregates_post_count ();
350 CREATE FUNCTION user_aggregates_post_score ()
355 IF (TG_OP = 'INSERT') THEN
356 -- Need to get the post creator, not the voter
360 post_score = post_score + NEW.score
364 ua.user_id = p.creator_id
365 AND p.id = NEW.post_id;
366 ELSIF (TG_OP = 'DELETE') THEN
370 post_score = post_score - OLD.score
374 ua.user_id = p.creator_id
375 AND p.id = OLD.post_id;
381 CREATE TRIGGER user_aggregates_post_score
382 AFTER INSERT OR DELETE ON post_like
384 EXECUTE PROCEDURE user_aggregates_post_score ();
387 CREATE FUNCTION user_aggregates_comment_count ()
392 IF (TG_OP = 'INSERT') THEN
396 comment_count = comment_count + 1
398 user_id = NEW.creator_id;
399 ELSIF (TG_OP = 'DELETE') THEN
403 comment_count = comment_count - 1
405 user_id = OLD.creator_id;
406 -- If the comment gets deleted, the score calculation trigger won't fire,
407 -- so you need to re-calculate
411 comment_score = cd.score
415 coalesce(0, sum(cl.score)) AS score
416 -- User join because comments could be empty
419 LEFT JOIN comment c ON u.id = c.creator_id
420 LEFT JOIN comment_like cl ON c.id = cl.comment_id
424 ua.user_id = OLD.creator_id;
430 CREATE TRIGGER user_aggregates_comment_count
431 AFTER INSERT OR DELETE ON comment
433 EXECUTE PROCEDURE user_aggregates_comment_count ();
436 CREATE FUNCTION user_aggregates_comment_score ()
441 IF (TG_OP = 'INSERT') THEN
442 -- Need to get the post creator, not the voter
446 comment_score = comment_score + NEW.score
450 ua.user_id = c.creator_id
451 AND c.id = NEW.comment_id;
452 ELSIF (TG_OP = 'DELETE') THEN
456 comment_score = comment_score - OLD.score
460 ua.user_id = c.creator_id
461 AND c.id = OLD.comment_id;
467 CREATE TRIGGER user_aggregates_comment_score
468 AFTER INSERT OR DELETE ON comment_like
470 EXECUTE PROCEDURE user_aggregates_comment_score ();
472 -- redo site aggregates trigger
473 CREATE OR REPLACE FUNCTION site_aggregates_activity (i text)
487 INNER JOIN user_ u ON c.creator_id = u.id
489 c.published > ('now'::timestamp - i::interval)
496 INNER JOIN user_ u ON p.creator_id = u.id
498 p.published > ('now'::timestamp - i::interval)
499 AND u.local = TRUE) a;