1 -- Remove the comment.read column, and create a new comment_reply table,
2 -- similar to the person_mention table.
4 -- This is necessary because self-joins using ltrees would be too tough with SQL views
6 -- Every comment should have a row here, because all comments have a recipient,
7 -- either the post creator, or the parent commenter.
8 create table comment_reply(
10 recipient_id int references person on update cascade on delete cascade not null,
11 comment_id int references comment on update cascade on delete cascade not null,
12 read boolean default false not null,
13 published timestamp not null default now(),
14 unique(recipient_id, comment_id)
17 -- Ones where parent_id is null, use the post creator recipient
18 insert into comment_reply (recipient_id, comment_id, read)
19 select p.creator_id, c.id, c.read from comment c
20 inner join post p on c.post_id = p.id
21 where c.parent_id is null;
23 -- Ones where there is a parent_id, self join to comment to get the parent comment creator
24 insert into comment_reply (recipient_id, comment_id, read)
25 select c2.creator_id, c.id, c.read from comment c
26 inner join comment c2 on c.parent_id = c2.id;
28 -- Drop comment_alias view
29 drop view comment_alias_1;
31 alter table comment drop column read;
33 create extension ltree;
35 alter table comment add column path ltree not null default '0';
36 alter table comment_aggregates add column child_count integer not null default 0;
38 -- The ltree path column should be the comment_id parent paths, separated by dots.
39 -- Stackoverflow: building an ltree from a parent_id hierarchical tree:
40 -- https://stackoverflow.com/a/1144848/1655478
42 create temporary table comment_temp as
44 SELECT h, 1 AS level, ARRAY[id] AS breadcrumb
46 WHERE parent_id is null
48 SELECT hi, q.level + 1 AS level, breadcrumb || id
51 ON hi.parent_id = (q.h).id
56 breadcrumb::VARCHAR AS path,
57 text2ltree('0.' || array_to_string(breadcrumb, '.')) as ltree_path
62 -- Remove indexes and foreign key constraints, and disable triggers for faster updates
63 alter table comment disable trigger user;
65 alter table comment drop constraint if exists comment_creator_id_fkey;
66 alter table comment drop constraint if exists comment_parent_id_fkey;
67 alter table comment drop constraint if exists comment_post_id_fkey;
68 alter table comment drop constraint if exists idx_comment_ap_id;
70 drop index if exists idx_comment_creator;
71 drop index if exists idx_comment_parent;
72 drop index if exists idx_comment_post;
73 drop index if exists idx_comment_published;
75 -- Add the ltree column
77 set path = ct.ltree_path
81 -- Update the child counts
82 update comment_aggregates ca set child_count = c2.child_count
84 select c.id, c.path, count(c2.id) as child_count from comment c
85 left join comment c2 on c2.path <@ c.path and c2.path != c.path
88 where ca.comment_id = c2.id;
90 -- Delete comments at a depth of > 150, otherwise the index creation below will fail
91 delete from comment where nlevel(path) > 150;
93 -- Delete from comment where there is a missing post
94 delete from comment c where not exists (
95 select from post p where p.id = c.post_id
98 -- Delete from comment where there is a missing creator_id
99 delete from comment c where not exists (
100 select from person p where p.id = c.creator_id
103 -- Re-enable old constraints and indexes
104 alter table comment add constraint "comment_creator_id_fkey" FOREIGN KEY (creator_id) REFERENCES person(id) ON UPDATE CASCADE ON DELETE CASCADE;
105 alter table comment add constraint "comment_post_id_fkey" FOREIGN KEY (post_id) REFERENCES post(id) ON UPDATE CASCADE ON DELETE CASCADE;
106 alter table comment add constraint "idx_comment_ap_id" unique (ap_id);
108 create index idx_comment_creator on comment (creator_id);
109 create index idx_comment_post on comment (post_id);
110 create index idx_comment_published on comment (published desc);
113 create index idx_path_gist on comment using gist (path);
115 -- Drop the parent_id column
116 alter table comment drop column parent_id cascade;
118 alter table comment enable trigger user;