2 -- Remove the comment.read column, and create a new comment_reply table,
3 -- similar to the person_mention table.
5 -- This is necessary because self-joins using ltrees would be too tough with SQL views
7 -- Every comment should have a row here, because all comments have a recipient,
8 -- either the post creator, or the parent commenter.
9 create table comment_reply(
10 id serial primary key,
11 recipient_id int references person on update cascade on delete cascade not null,
12 comment_id int references comment on update cascade on delete cascade not null,
13 read boolean default false not null,
14 published timestamp not null default now(),
15 unique(recipient_id, comment_id)
18 -- Ones where parent_id is null, use the post creator recipient
19 insert into comment_reply (recipient_id, comment_id, read)
20 select p.creator_id, c.id, c.read from comment c
21 inner join post p on c.post_id = p.id
22 where c.parent_id is null;
24 -- Ones where there is a parent_id, self join to comment to get the parent comment creator
25 insert into comment_reply (recipient_id, comment_id, read)
26 select c2.creator_id, c.id, c.read from comment c
27 inner join comment c2 on c.parent_id = c2.id;
29 -- Drop comment_alias view
30 drop view comment_alias_1;
32 alter table comment drop column read;
34 create extension ltree;
36 alter table comment add column path ltree not null default '0';
37 alter table comment_aggregates add column child_count integer not null default 0;
39 -- The ltree path column should be the comment_id parent paths, separated by dots.
40 -- Stackoverflow: building an ltree from a parent_id hierarchical tree:
41 -- https://stackoverflow.com/a/1144848/1655478
43 create temporary table comment_temp as
45 SELECT h, 1 AS level, ARRAY[id] AS breadcrumb
47 WHERE parent_id is null
49 SELECT hi, q.level + 1 AS level, breadcrumb || id
52 ON hi.parent_id = (q.h).id
57 breadcrumb::VARCHAR AS path,
58 text2ltree('0.' || array_to_string(breadcrumb, '.')) as ltree_path
63 -- Add the ltree column
65 set path = ct.ltree_path
69 -- Update the child counts
70 update comment_aggregates ca set child_count = c2.child_count
72 select c.id, c.path, count(c2.id) as child_count from comment c
73 left join comment c2 on c2.path <@ c.path and c2.path != c.path
76 where ca.comment_id = c2.id;
79 create index idx_path_gist on comment using gist (path);
81 -- Drop the parent_id column
82 alter table comment drop column parent_id cascade;