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

   1
   2 -- Remove the comment.read column, and create a new comment_reply table,
   3 -- similar to the person_mention table.
   4 --
   5 -- This is necessary because self-joins using ltrees would be too tough with SQL views
   6 --
   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)
  16 );
  17
  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;
  23
  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;
  28
  29 -- Drop comment_alias view
  30 drop view comment_alias_1;
  31
  32 alter table comment drop column read;
  33
  34 create extension ltree;
  35
  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;
  38
  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
  42
  43 create temporary table comment_temp as
  44 WITH RECURSIVE q AS (
  45         SELECT  h, 1 AS level, ARRAY[id] AS breadcrumb
  46         FROM    comment h
  47         WHERE   parent_id is null
  48         UNION ALL
  49         SELECT  hi, q.level + 1 AS level, breadcrumb || id
  50         FROM    q
  51         JOIN    comment hi
  52         ON      hi.parent_id = (q.h).id
  53 )
  54 SELECT  (q.h).id,
  55         (q.h).parent_id,
  56         level,
  57         breadcrumb::VARCHAR AS path,
  58         text2ltree('0.' || array_to_string(breadcrumb, '.')) as ltree_path
  59 FROM    q
  60 ORDER BY
  61         breadcrumb;
  62
  63 -- Add the ltree column
  64 update comment c
  65 set path = ct.ltree_path
  66 from comment_temp ct
  67 where c.id = ct.id;
  68
  69 -- Update the child counts
  70 update comment_aggregates ca set child_count = c2.child_count
  71 from (
  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
  74   group by c.id
  75 ) as c2
  76 where ca.comment_id = c2.id;
  77
  78 -- Create the index
  79 create index idx_path_gist on comment using gist (path);
  80
  81 -- Drop the parent_id column
  82 alter table comment drop column parent_id cascade;
  83