migrations/2022-07-07-182650_comment_ltrees/up.sql

   1 -- Remove the comment.read column, and create a new comment_reply table,
   2 -- similar to the person_mention table.
   3 --
   4 -- This is necessary because self-joins using ltrees would be too tough with SQL views
   5 --
   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 (
   9     id serial PRIMARY KEY,
  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)
  15 );
  16
  17 -- Ones where parent_id is null, use the post creator recipient
  18 INSERT INTO comment_reply (recipient_id, comment_id, read)
  19 SELECT
  20     p.creator_id,
  21     c.id,
  22     c.read
  23 FROM
  24     comment c
  25     INNER JOIN post p ON c.post_id = p.id
  26 WHERE
  27     c.parent_id IS NULL;
  28
  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)
  31 SELECT
  32     c2.creator_id,
  33     c.id,
  34     c.read
  35 FROM
  36     comment c
  37     INNER JOIN comment c2 ON c.parent_id = c2.id;
  38
  39 -- Drop comment_alias view
  40 DROP VIEW comment_alias_1;
  41
  42 ALTER TABLE comment
  43     DROP COLUMN read;
  44
  45 CREATE EXTENSION IF NOT EXISTS ltree;
  46
  47 ALTER TABLE comment
  48     ADD COLUMN path ltree NOT NULL DEFAULT '0';
  49
  50 ALTER TABLE comment_aggregates
  51     ADD COLUMN child_count integer NOT NULL DEFAULT 0;
  52
  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
  57 WITH RECURSIVE q AS (
  58     SELECT
  59         h,
  60         1 AS level,
  61         ARRAY[id] AS breadcrumb
  62     FROM
  63         comment h
  64     WHERE
  65         parent_id IS NULL
  66     UNION ALL
  67     SELECT
  68         hi,
  69         q.level + 1 AS level,
  70         breadcrumb || id
  71     FROM
  72         q
  73         JOIN comment hi ON hi.parent_id = (q.h).id
  74 )
  75 SELECT
  76     (q.h).id,
  77     (q.h).parent_id,
  78     level,
  79     breadcrumb::varchar AS path,
  80     text2ltree ('0.' || array_to_string(breadcrumb, '.')) AS ltree_path
  81 FROM
  82     q
  83 ORDER BY
  84     breadcrumb;
  85
  86 -- Remove indexes and foreign key constraints, and disable triggers for faster updates
  87 ALTER TABLE comment DISABLE TRIGGER USER;
  88
  89 ALTER TABLE comment
  90     DROP CONSTRAINT IF EXISTS comment_creator_id_fkey;
  91
  92 ALTER TABLE comment
  93     DROP CONSTRAINT IF EXISTS comment_parent_id_fkey;
  94
  95 ALTER TABLE comment
  96     DROP CONSTRAINT IF EXISTS comment_post_id_fkey;
  97
  98 ALTER TABLE comment
  99     DROP CONSTRAINT IF EXISTS idx_comment_ap_id;
 100
 101 DROP INDEX IF EXISTS idx_comment_creator;
 102
 103 DROP INDEX IF EXISTS idx_comment_parent;
 104
 105 DROP INDEX IF EXISTS idx_comment_post;
 106
 107 DROP INDEX IF EXISTS idx_comment_published;
 108
 109 -- Add the ltree column
 110 UPDATE
 111     comment c
 112 SET
 113     path = ct.ltree_path
 114 FROM
 115     comment_temp ct
 116 WHERE
 117     c.id = ct.id;
 118
 119 -- Update the child counts
 120 UPDATE
 121     comment_aggregates ca
 122 SET
 123     child_count = c2.child_count
 124 FROM (
 125     SELECT
 126         c.id,
 127         c.path,
 128         count(c2.id) AS child_count
 129     FROM
 130         comment c
 131     LEFT JOIN comment c2 ON c2.path <@ c.path
 132         AND c2.path != c.path
 133 GROUP BY
 134     c.id) AS c2
 135 WHERE
 136     ca.comment_id = c2.id;
 137
 138 -- Delete comments at a depth of > 150, otherwise the index creation below will fail
 139 DELETE FROM comment
 140 WHERE nlevel (path) > 150;
 141
 142 -- Delete from comment where there is a missing post
 143 DELETE FROM comment c
 144 WHERE NOT EXISTS (
 145         SELECT
 146         FROM
 147             post p
 148         WHERE
 149             p.id = c.post_id);
 150
 151 -- Delete from comment where there is a missing creator_id
 152 DELETE FROM comment c
 153 WHERE NOT EXISTS (
 154         SELECT
 155         FROM
 156             person p
 157         WHERE
 158             p.id = c.creator_id);
 159
 160 -- Re-enable old constraints and indexes
 161 ALTER TABLE comment
 162     ADD CONSTRAINT "comment_creator_id_fkey" FOREIGN KEY (creator_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE;
 163
 164 ALTER TABLE comment
 165     ADD CONSTRAINT "comment_post_id_fkey" FOREIGN KEY (post_id) REFERENCES post (id) ON UPDATE CASCADE ON DELETE CASCADE;
 166
 167 ALTER TABLE comment
 168     ADD CONSTRAINT "idx_comment_ap_id" UNIQUE (ap_id);
 169
 170 CREATE INDEX idx_comment_creator ON comment (creator_id);
 171
 172 CREATE INDEX idx_comment_post ON comment (post_id);
 173
 174 CREATE INDEX idx_comment_published ON comment (published DESC);
 175
 176 -- Create the index
 177 CREATE INDEX idx_path_gist ON comment USING gist (path);
 178
 179 -- Drop the parent_id column
 180 ALTER TABLE comment
 181     DROP COLUMN parent_id CASCADE;
 182
 183 ALTER TABLE comment ENABLE TRIGGER USER;
 184