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)
25 INNER JOIN post p ON c.post_id = p.id
29 -- Ones where there is a parent_id, self join to comment to get the parent comment creator
30 INSERT INTO comment_reply (recipient_id, comment_id, read)
37 INNER JOIN comment c2 ON c.parent_id = c2.id;
39 -- Drop comment_alias view
40 DROP VIEW comment_alias_1;
45 CREATE EXTENSION IF NOT EXISTS ltree;
48 ADD COLUMN path ltree NOT NULL DEFAULT '0';
50 ALTER TABLE comment_aggregates
51 ADD COLUMN child_count integer NOT NULL DEFAULT 0;
53 -- The ltree path column should be the comment_id parent paths, separated by dots.
54 -- Stackoverflow: building an ltree from a parent_id hierarchical tree:
55 -- https://stackoverflow.com/a/1144848/1655478
56 CREATE TEMPORARY TABLE comment_temp AS
61 ARRAY[id] AS breadcrumb
73 JOIN comment hi ON hi.parent_id = (q.h).id
79 breadcrumb::varchar AS path,
80 text2ltree ('0.' || array_to_string(breadcrumb, '.')) AS ltree_path
86 -- Remove indexes and foreign key constraints, and disable triggers for faster updates
87 ALTER TABLE comment DISABLE TRIGGER USER;
90 DROP CONSTRAINT IF EXISTS comment_creator_id_fkey;
93 DROP CONSTRAINT IF EXISTS comment_parent_id_fkey;
96 DROP CONSTRAINT IF EXISTS comment_post_id_fkey;
99 DROP CONSTRAINT IF EXISTS idx_comment_ap_id;
101 DROP INDEX IF EXISTS idx_comment_creator;
103 DROP INDEX IF EXISTS idx_comment_parent;
105 DROP INDEX IF EXISTS idx_comment_post;
107 DROP INDEX IF EXISTS idx_comment_published;
109 -- Add the ltree column
119 -- Update the child counts
121 comment_aggregates ca
123 child_count = c2.child_count
128 count(c2.id) AS child_count
131 LEFT JOIN comment c2 ON c2.path <@ c.path
132 AND c2.path != c.path
136 ca.comment_id = c2.id;
138 -- Delete comments at a depth of > 150, otherwise the index creation below will fail
140 WHERE nlevel (path) > 150;
142 -- Delete from comment where there is a missing post
143 DELETE FROM comment c
151 -- Delete from comment where there is a missing creator_id
152 DELETE FROM comment c
158 p.id = c.creator_id);
160 -- Re-enable old constraints and indexes
162 ADD CONSTRAINT "comment_creator_id_fkey" FOREIGN KEY (creator_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE;
165 ADD CONSTRAINT "comment_post_id_fkey" FOREIGN KEY (post_id) REFERENCES post (id) ON UPDATE CASCADE ON DELETE CASCADE;
168 ADD CONSTRAINT "idx_comment_ap_id" UNIQUE (ap_id);
170 CREATE INDEX idx_comment_creator ON comment (creator_id);
172 CREATE INDEX idx_comment_post ON comment (post_id);
174 CREATE INDEX idx_comment_published ON comment (published DESC);
177 CREATE INDEX idx_path_gist ON comment USING gist (path);
179 -- Drop the parent_id column
181 DROP COLUMN parent_id CASCADE;
183 ALTER TABLE comment ENABLE TRIGGER USER;