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 user_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 constraint user_ban_user_id_fkey to person_ban_person_id_fkey;