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;
8 ALTER SEQUENCE user__id_seq
9 RENAME TO person_id_seq;
11 -- create a new table local_user
12 CREATE TABLE local_user (
13 id serial PRIMARY KEY,
14 person_id int REFERENCES person ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
15 password_encrypted text NOT NULL,
17 admin boolean DEFAULT FALSE NOT NULL,
18 show_nsfw boolean DEFAULT FALSE NOT NULL,
19 theme character varying(20) DEFAULT 'darkly' ::character varying NOT NULL,
20 default_sort_type smallint DEFAULT 0 NOT NULL,
21 default_listing_type smallint DEFAULT 1 NOT NULL,
22 lang character varying(20) DEFAULT 'browser' ::character varying NOT NULL,
23 show_avatars boolean DEFAULT TRUE NOT NULL,
24 send_notifications_to_email boolean DEFAULT FALSE NOT NULL,
29 -- Copy the local users over to the new table
30 INSERT INTO local_user (person_id, password_encrypted, email, admin, show_nsfw, theme, default_sort_type, default_listing_type, lang, show_avatars, send_notifications_to_email, matrix_user_id)
42 send_notifications_to_email,
49 -- Drop those columns from person
51 DROP COLUMN password_encrypted,
54 DROP COLUMN show_nsfw,
56 DROP COLUMN default_sort_type,
57 DROP COLUMN default_listing_type,
59 DROP COLUMN show_avatars,
60 DROP COLUMN send_notifications_to_email,
61 DROP COLUMN matrix_user_id;
64 ALTER INDEX user__pkey RENAME TO person__pkey;
66 ALTER INDEX idx_user_actor_id RENAME TO idx_person_actor_id;
68 ALTER INDEX idx_user_inbox_url RENAME TO idx_person_inbox_url;
70 ALTER INDEX idx_user_lower_actor_id RENAME TO idx_person_lower_actor_id;
72 ALTER INDEX idx_user_published RENAME TO idx_person_published;
75 ALTER TRIGGER site_aggregates_user_delete ON person RENAME TO site_aggregates_person_delete;
77 ALTER TRIGGER site_aggregates_user_insert ON person RENAME TO site_aggregates_person_insert;
79 -- Rename the trigger functions
80 ALTER FUNCTION site_aggregates_user_delete () RENAME TO site_aggregates_person_delete;
82 ALTER FUNCTION site_aggregates_user_insert () RENAME TO site_aggregates_person_insert;
85 CREATE VIEW person_alias_1 AS
91 CREATE VIEW person_alias_2 AS
97 -- Redo user aggregates into person_aggregates
98 ALTER TABLE user_aggregates RENAME TO person_aggregates;
100 ALTER SEQUENCE user_aggregates_id_seq
101 RENAME TO person_aggregates_id_seq;
103 ALTER TABLE person_aggregates RENAME COLUMN user_id TO person_id;
106 ALTER INDEX user_aggregates_pkey RENAME TO person_aggregates_pkey;
108 ALTER INDEX idx_user_aggregates_comment_score RENAME TO idx_person_aggregates_comment_score;
110 ALTER INDEX user_aggregates_user_id_key RENAME TO person_aggregates_person_id_key;
112 ALTER TABLE person_aggregates RENAME CONSTRAINT user_aggregates_user_id_fkey TO person_aggregates_person_id_fkey;
114 -- Drop all the old triggers and functions
115 DROP TRIGGER user_aggregates_user ON person;
117 DROP TRIGGER user_aggregates_post_count ON post;
119 DROP TRIGGER user_aggregates_post_score ON post_like;
121 DROP TRIGGER user_aggregates_comment_count ON comment;
123 DROP TRIGGER user_aggregates_comment_score ON comment_like;
125 DROP FUNCTION user_aggregates_user, user_aggregates_post_count, user_aggregates_post_score, user_aggregates_comment_count, user_aggregates_comment_score;
128 CREATE FUNCTION person_aggregates_person ()
133 IF (TG_OP = 'INSERT') THEN
134 INSERT INTO person_aggregates (person_id)
136 ELSIF (TG_OP = 'DELETE') THEN
137 DELETE FROM person_aggregates
138 WHERE person_id = OLD.id;
144 CREATE TRIGGER person_aggregates_person
145 AFTER INSERT OR DELETE ON person
147 EXECUTE PROCEDURE person_aggregates_person ();
150 CREATE FUNCTION person_aggregates_post_count ()
155 IF (TG_OP = 'INSERT') THEN
159 post_count = post_count + 1
161 person_id = NEW.creator_id;
162 ELSIF (TG_OP = 'DELETE') THEN
166 post_count = post_count - 1
168 person_id = OLD.creator_id;
169 -- If the post gets deleted, the score calculation trigger won't fire,
170 -- so you need to re-calculate
174 post_score = pd.score
178 coalesce(0, sum(pl.score)) AS score
179 -- User join because posts could be empty
182 LEFT JOIN post p ON u.id = p.creator_id
183 LEFT JOIN post_like pl ON p.id = pl.post_id
187 ua.person_id = OLD.creator_id;
193 CREATE TRIGGER person_aggregates_post_count
194 AFTER INSERT OR DELETE ON post
196 EXECUTE PROCEDURE person_aggregates_post_count ();
199 CREATE FUNCTION person_aggregates_post_score ()
204 IF (TG_OP = 'INSERT') THEN
205 -- Need to get the post creator, not the voter
209 post_score = post_score + NEW.score
213 ua.person_id = p.creator_id
214 AND p.id = NEW.post_id;
215 ELSIF (TG_OP = 'DELETE') THEN
219 post_score = post_score - OLD.score
223 ua.person_id = p.creator_id
224 AND p.id = OLD.post_id;
230 CREATE TRIGGER person_aggregates_post_score
231 AFTER INSERT OR DELETE ON post_like
233 EXECUTE PROCEDURE person_aggregates_post_score ();
236 CREATE FUNCTION person_aggregates_comment_count ()
241 IF (TG_OP = 'INSERT') THEN
245 comment_count = comment_count + 1
247 person_id = NEW.creator_id;
248 ELSIF (TG_OP = 'DELETE') THEN
252 comment_count = comment_count - 1
254 person_id = OLD.creator_id;
255 -- If the comment gets deleted, the score calculation trigger won't fire,
256 -- so you need to re-calculate
260 comment_score = cd.score
264 coalesce(0, sum(cl.score)) AS score
265 -- User join because comments could be empty
268 LEFT JOIN comment c ON u.id = c.creator_id
269 LEFT JOIN comment_like cl ON c.id = cl.comment_id
273 ua.person_id = OLD.creator_id;
279 CREATE TRIGGER person_aggregates_comment_count
280 AFTER INSERT OR DELETE ON comment
282 EXECUTE PROCEDURE person_aggregates_comment_count ();
285 CREATE FUNCTION person_aggregates_comment_score ()
290 IF (TG_OP = 'INSERT') THEN
291 -- Need to get the post creator, not the voter
295 comment_score = comment_score + NEW.score
299 ua.person_id = c.creator_id
300 AND c.id = NEW.comment_id;
301 ELSIF (TG_OP = 'DELETE') THEN
305 comment_score = comment_score - OLD.score
309 ua.person_id = c.creator_id
310 AND c.id = OLD.comment_id;
316 CREATE TRIGGER person_aggregates_comment_score
317 AFTER INSERT OR DELETE ON comment_like
319 EXECUTE PROCEDURE person_aggregates_comment_score ();
322 ALTER TABLE user_mention RENAME TO person_mention;
324 ALTER SEQUENCE user_mention_id_seq
325 RENAME TO person_mention_id_seq;
327 ALTER INDEX user_mention_pkey RENAME TO person_mention_pkey;
329 ALTER INDEX user_mention_recipient_id_comment_id_key RENAME TO person_mention_recipient_id_comment_id_key;
331 ALTER TABLE person_mention RENAME CONSTRAINT user_mention_comment_id_fkey TO person_mention_comment_id_fkey;
333 ALTER TABLE person_mention RENAME CONSTRAINT user_mention_recipient_id_fkey TO person_mention_recipient_id_fkey;
336 ALTER TABLE user_ban RENAME TO person_ban;
338 ALTER SEQUENCE user_ban_id_seq
339 RENAME TO person_ban_id_seq;
341 ALTER INDEX user_ban_pkey RENAME TO person_ban_pkey;
343 ALTER INDEX user_ban_user_id_key RENAME TO person_ban_person_id_key;
345 ALTER TABLE person_ban RENAME COLUMN user_id TO person_id;
347 ALTER TABLE person_ban RENAME CONSTRAINT user_ban_user_id_fkey TO person_ban_person_id_fkey;
350 ALTER TABLE comment_like RENAME COLUMN user_id TO person_id;
352 ALTER INDEX idx_comment_like_user RENAME TO idx_comment_like_person;
354 ALTER TABLE comment_like RENAME CONSTRAINT comment_like_comment_id_user_id_key TO comment_like_comment_id_person_id_key;
356 ALTER TABLE comment_like RENAME CONSTRAINT comment_like_user_id_fkey TO comment_like_person_id_fkey;
359 ALTER TABLE comment_saved RENAME COLUMN user_id TO person_id;
361 ALTER TABLE comment_saved RENAME CONSTRAINT comment_saved_comment_id_user_id_key TO comment_saved_comment_id_person_id_key;
363 ALTER TABLE comment_saved RENAME CONSTRAINT comment_saved_user_id_fkey TO comment_saved_person_id_fkey;
365 -- community_follower
366 ALTER TABLE community_follower RENAME COLUMN user_id TO person_id;
368 ALTER TABLE community_follower RENAME CONSTRAINT community_follower_community_id_user_id_key TO community_follower_community_id_person_id_key;
370 ALTER TABLE community_follower RENAME CONSTRAINT community_follower_user_id_fkey TO community_follower_person_id_fkey;
372 -- community_moderator
373 ALTER TABLE community_moderator RENAME COLUMN user_id TO person_id;
375 ALTER TABLE community_moderator RENAME CONSTRAINT community_moderator_community_id_user_id_key TO community_moderator_community_id_person_id_key;
377 ALTER TABLE community_moderator RENAME CONSTRAINT community_moderator_user_id_fkey TO community_moderator_person_id_fkey;
379 -- community_user_ban
380 ALTER TABLE community_user_ban RENAME TO community_person_ban;
382 ALTER SEQUENCE community_user_ban_id_seq
383 RENAME TO community_person_ban_id_seq;
385 ALTER TABLE community_person_ban RENAME COLUMN user_id TO person_id;
387 ALTER TABLE community_person_ban RENAME CONSTRAINT community_user_ban_pkey TO community_person_ban_pkey;
389 ALTER TABLE community_person_ban RENAME CONSTRAINT community_user_ban_community_id_fkey TO community_person_ban_community_id_fkey;
391 ALTER TABLE community_person_ban RENAME CONSTRAINT community_user_ban_community_id_user_id_key TO community_person_ban_community_id_person_id_key;
393 ALTER TABLE community_person_ban RENAME CONSTRAINT community_user_ban_user_id_fkey TO community_person_ban_person_id_fkey;
396 ALTER TABLE mod_add RENAME COLUMN mod_user_id TO mod_person_id;
398 ALTER TABLE mod_add RENAME COLUMN other_user_id TO other_person_id;
400 ALTER TABLE mod_add RENAME CONSTRAINT mod_add_mod_user_id_fkey TO mod_add_mod_person_id_fkey;
402 ALTER TABLE mod_add RENAME CONSTRAINT mod_add_other_user_id_fkey TO mod_add_other_person_id_fkey;
405 ALTER TABLE mod_add_community RENAME COLUMN mod_user_id TO mod_person_id;
407 ALTER TABLE mod_add_community RENAME COLUMN other_user_id TO other_person_id;
409 ALTER TABLE mod_add_community RENAME CONSTRAINT mod_add_community_mod_user_id_fkey TO mod_add_community_mod_person_id_fkey;
411 ALTER TABLE mod_add_community RENAME CONSTRAINT mod_add_community_other_user_id_fkey TO mod_add_community_other_person_id_fkey;
414 ALTER TABLE mod_ban RENAME COLUMN mod_user_id TO mod_person_id;
416 ALTER TABLE mod_ban RENAME COLUMN other_user_id TO other_person_id;
418 ALTER TABLE mod_ban RENAME CONSTRAINT mod_ban_mod_user_id_fkey TO mod_ban_mod_person_id_fkey;
420 ALTER TABLE mod_ban RENAME CONSTRAINT mod_ban_other_user_id_fkey TO mod_ban_other_person_id_fkey;
423 ALTER TABLE mod_ban_from_community RENAME COLUMN mod_user_id TO mod_person_id;
425 ALTER TABLE mod_ban_from_community RENAME COLUMN other_user_id TO other_person_id;
427 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;
429 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;
432 ALTER TABLE mod_lock_post RENAME COLUMN mod_user_id TO mod_person_id;
434 ALTER TABLE mod_lock_post RENAME CONSTRAINT mod_lock_post_mod_user_id_fkey TO mod_lock_post_mod_person_id_fkey;
436 -- mod_remove_comment
437 ALTER TABLE mod_remove_comment RENAME COLUMN mod_user_id TO mod_person_id;
439 ALTER TABLE mod_remove_comment RENAME CONSTRAINT mod_remove_comment_mod_user_id_fkey TO mod_remove_comment_mod_person_id_fkey;
441 -- mod_remove_community
442 ALTER TABLE mod_remove_community RENAME COLUMN mod_user_id TO mod_person_id;
444 ALTER TABLE mod_remove_community RENAME CONSTRAINT mod_remove_community_mod_user_id_fkey TO mod_remove_community_mod_person_id_fkey;
447 ALTER TABLE mod_remove_post RENAME COLUMN mod_user_id TO mod_person_id;
449 ALTER TABLE mod_remove_post RENAME CONSTRAINT mod_remove_post_mod_user_id_fkey TO mod_remove_post_mod_person_id_fkey;
452 ALTER TABLE mod_sticky_post RENAME COLUMN mod_user_id TO mod_person_id;
454 ALTER TABLE mod_sticky_post RENAME CONSTRAINT mod_sticky_post_mod_user_id_fkey TO mod_sticky_post_mod_person_id_fkey;
456 -- password_reset_request
457 DELETE FROM password_reset_request;
459 ALTER TABLE password_reset_request
462 ALTER TABLE password_reset_request
463 ADD COLUMN local_user_id integer NOT NULL REFERENCES local_user (id) ON UPDATE CASCADE ON DELETE CASCADE;
466 ALTER TABLE post_like RENAME COLUMN user_id TO person_id;
468 ALTER INDEX idx_post_like_user RENAME TO idx_post_like_person;
470 ALTER TABLE post_like RENAME CONSTRAINT post_like_post_id_user_id_key TO post_like_post_id_person_id_key;
472 ALTER TABLE post_like RENAME CONSTRAINT post_like_user_id_fkey TO post_like_person_id_fkey;
475 ALTER TABLE post_read RENAME COLUMN user_id TO person_id;
477 ALTER TABLE post_read RENAME CONSTRAINT post_read_post_id_user_id_key TO post_read_post_id_person_id_key;
479 ALTER TABLE post_read RENAME CONSTRAINT post_read_user_id_fkey TO post_read_person_id_fkey;
482 ALTER TABLE post_saved RENAME COLUMN user_id TO person_id;
484 ALTER TABLE post_saved RENAME CONSTRAINT post_saved_post_id_user_id_key TO post_saved_post_id_person_id_key;
486 ALTER TABLE post_saved RENAME CONSTRAINT post_saved_user_id_fkey TO post_saved_person_id_fkey;
488 -- redo site aggregates trigger
489 CREATE OR REPLACE FUNCTION site_aggregates_activity (i text)
503 INNER JOIN person u ON c.creator_id = u.id
505 c.published > ('now'::timestamp - i::interval)
512 INNER JOIN person u ON p.creator_id = u.id
514 p.published > ('now'::timestamp - i::interval)
515 AND u.local = TRUE) a;