1 -- Add unique ap_id for private_message, comment, and post
3 -- Need to delete the possible dupes for ones that don't start with the fake one
4 delete from private_message a using (
5 select min(id) as id, ap_id
7 group by ap_id having count(*) > 1
9 where a.ap_id = b.ap_id
12 delete from post a using (
13 select min(id) as id, ap_id
15 group by ap_id having count(*) > 1
17 where a.ap_id = b.ap_id
20 delete from comment a using (
21 select min(id) as id, ap_id
23 group by ap_id having count(*) > 1
25 where a.ap_id = b.ap_id
28 -- Replacing the current default on the columns, to the unique one
29 update private_message
30 set ap_id = generate_unique_changeme()
31 where ap_id = 'http://fake.com';
34 set ap_id = generate_unique_changeme()
35 where ap_id = 'http://fake.com';
38 set ap_id = generate_unique_changeme()
39 where ap_id = 'http://fake.com';
41 -- Add the unique indexes
42 alter table private_message alter column ap_id set not null;
43 alter table private_message alter column ap_id set default generate_unique_changeme();
45 alter table post alter column ap_id set not null;
46 alter table post alter column ap_id set default generate_unique_changeme();
48 alter table comment alter column ap_id set not null;
49 alter table comment alter column ap_id set default generate_unique_changeme();
51 -- Add the uniques, for user_ and community too
52 alter table private_message add constraint idx_private_message_ap_id unique (ap_id);
53 alter table post add constraint idx_post_ap_id unique (ap_id);
54 alter table comment add constraint idx_comment_ap_id unique (ap_id);
55 alter table user_ add constraint idx_user_actor_id unique (actor_id);
56 alter table community add constraint idx_community_actor_id unique (actor_id);