2 alter table person_ban rename to user_ban;
3 alter sequence person_ban_id_seq rename to user_ban_id_seq;
4 alter index person_ban_pkey rename to user_ban_pkey;
5 alter index person_ban_person_id_key rename to user_ban_user_id_key;
6 alter table user_ban rename constraint person_ban_person_id_fkey to user_ban_user_id_fkey;
9 alter table person_mention rename to user_mention;
10 alter sequence person_mention_id_seq rename to user_mention_id_seq;
11 alter index person_mention_pkey rename to user_mention_pkey;
12 alter index person_mention_recipient_id_comment_id_key rename to user_mention_recipient_id_comment_id_key;
13 alter table user_mention rename constraint person_mention_comment_id_fkey to user_mention_comment_id_fkey;
14 alter table user_mention rename constraint person_mention_recipient_id_fkey to user_mention_recipient_id_fkey;
16 -- User aggregates table
17 alter table person_aggregates rename to user_aggregates;
18 alter sequence person_aggregates_id_seq rename to user_aggregates_id_seq;
19 alter table user_aggregates rename column person_id to user_id;
22 alter index person_aggregates_pkey rename to user_aggregates_pkey;
23 alter index idx_person_aggregates_comment_score rename to idx_user_aggregates_comment_score;
24 alter index person_aggregates_person_id_key rename to user_aggregates_user_id_key;
25 alter table user_aggregates rename constraint person_aggregates_person_id_fkey to user_aggregates_user_id_fkey;
27 -- Redo the user_aggregates table
28 drop trigger person_aggregates_person on person;
29 drop trigger person_aggregates_post_count on post;
30 drop trigger person_aggregates_post_score on post_like;
31 drop trigger person_aggregates_comment_count on comment;
32 drop trigger person_aggregates_comment_score on comment_like;
34 person_aggregates_person,
35 person_aggregates_post_count,
36 person_aggregates_post_score,
37 person_aggregates_comment_count,
38 person_aggregates_comment_score;
42 drop view person_alias_1, person_alias_2;
45 alter index person__pkey rename to user__pkey;
46 alter index idx_person_actor_id rename to idx_user_actor_id;
47 alter index idx_person_inbox_url rename to idx_user_inbox_url;
48 alter index idx_person_lower_actor_id rename to idx_user_lower_actor_id;
49 alter index idx_person_published rename to idx_user_published;
52 alter trigger site_aggregates_person_delete on person rename to site_aggregates_user_delete;
53 alter trigger site_aggregates_person_insert on person rename to site_aggregates_user_insert;
55 -- Rename the trigger functions
56 alter function site_aggregates_person_delete() rename to site_aggregates_user_delete;
57 alter function site_aggregates_person_insert() rename to site_aggregates_user_insert;
59 -- Rename the table back to user_
60 alter table person rename to user_;
61 alter sequence person_id_seq rename to user__id_seq;
63 -- Add the columns back in
65 add column password_encrypted text not null default 'changeme',
66 add column email text,
67 add column admin boolean default false not null,
68 add column show_nsfw boolean default false not null,
69 add column theme character varying(20) default 'darkly'::character varying not null,
70 add column default_sort_type smallint default 0 not null,
71 add column default_listing_type smallint default 1 not null,
72 add column lang character varying(20) default 'browser'::character varying not null,
73 add column show_avatars boolean default true not null,
74 add column send_notifications_to_email boolean default false not null,
75 add column matrix_user_id text;
77 -- Update the user_ table with the local_user data
79 password_encrypted = lu.password_encrypted,
82 show_nsfw = lu.show_nsfw,
84 default_sort_type = lu.default_sort_type,
85 default_listing_type = lu.default_listing_type,
87 show_avatars = lu.show_avatars,
88 send_notifications_to_email = lu.send_notifications_to_email,
89 matrix_user_id = lu.matrix_user_id
91 where lu.user_id = u.id;
93 create view user_alias_1 as select * from user_;
94 create view user_alias_2 as select * from user_;
96 drop table local_user;
98 -- Add the user_aggregates table triggers
101 create function user_aggregates_user()
102 returns trigger language plpgsql
105 IF (TG_OP = 'INSERT') THEN
106 insert into user_aggregates (user_id) values (NEW.id);
107 ELSIF (TG_OP = 'DELETE') THEN
108 delete from user_aggregates where user_id = OLD.id;
113 create trigger user_aggregates_user
114 after insert or delete on user_
116 execute procedure user_aggregates_user();
119 create function user_aggregates_post_count()
120 returns trigger language plpgsql
123 IF (TG_OP = 'INSERT') THEN
124 update user_aggregates
125 set post_count = post_count + 1 where user_id = NEW.creator_id;
127 ELSIF (TG_OP = 'DELETE') THEN
128 update user_aggregates
129 set post_count = post_count - 1 where user_id = OLD.creator_id;
131 -- If the post gets deleted, the score calculation trigger won't fire,
132 -- so you need to re-calculate
133 update user_aggregates ua
134 set post_score = pd.score
137 coalesce(0, sum(pl.score)) as score
138 -- User join because posts could be empty
140 left join post p on u.id = p.creator_id
141 left join post_like pl on p.id = pl.post_id
144 where ua.user_id = OLD.creator_id;
150 create trigger user_aggregates_post_count
151 after insert or delete on post
153 execute procedure user_aggregates_post_count();
156 create function user_aggregates_post_score()
157 returns trigger language plpgsql
160 IF (TG_OP = 'INSERT') THEN
161 -- Need to get the post creator, not the voter
162 update user_aggregates ua
163 set post_score = post_score + NEW.score
165 where ua.user_id = p.creator_id and p.id = NEW.post_id;
167 ELSIF (TG_OP = 'DELETE') THEN
168 update user_aggregates ua
169 set post_score = post_score - OLD.score
171 where ua.user_id = p.creator_id and p.id = OLD.post_id;
176 create trigger user_aggregates_post_score
177 after insert or delete on post_like
179 execute procedure user_aggregates_post_score();
182 create function user_aggregates_comment_count()
183 returns trigger language plpgsql
186 IF (TG_OP = 'INSERT') THEN
187 update user_aggregates
188 set comment_count = comment_count + 1 where user_id = NEW.creator_id;
189 ELSIF (TG_OP = 'DELETE') THEN
190 update user_aggregates
191 set comment_count = comment_count - 1 where user_id = OLD.creator_id;
193 -- If the comment gets deleted, the score calculation trigger won't fire,
194 -- so you need to re-calculate
195 update user_aggregates ua
196 set comment_score = cd.score
199 coalesce(0, sum(cl.score)) as score
200 -- User join because comments could be empty
202 left join comment c on u.id = c.creator_id
203 left join comment_like cl on c.id = cl.comment_id
206 where ua.user_id = OLD.creator_id;
211 create trigger user_aggregates_comment_count
212 after insert or delete on comment
214 execute procedure user_aggregates_comment_count();
217 create function user_aggregates_comment_score()
218 returns trigger language plpgsql
221 IF (TG_OP = 'INSERT') THEN
222 -- Need to get the post creator, not the voter
223 update user_aggregates ua
224 set comment_score = comment_score + NEW.score
226 where ua.user_id = c.creator_id and c.id = NEW.comment_id;
227 ELSIF (TG_OP = 'DELETE') THEN
228 update user_aggregates ua
229 set comment_score = comment_score - OLD.score
231 where ua.user_id = c.creator_id and c.id = OLD.comment_id;
236 create trigger user_aggregates_comment_score
237 after insert or delete on comment_like
239 execute procedure user_aggregates_comment_score();