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 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;
  22
  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;
  27
  28 -- Drop comment_alias view
  29 drop view comment_alias_1;
  30
  31 alter table comment drop column read;
  32
  33 create extension ltree;
  34
  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;
  37
  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
  41
  42 create temporary table comment_temp as
  43 WITH RECURSIVE q AS (
  44         SELECT  h, 1 AS level, ARRAY[id] AS breadcrumb
  45         FROM    comment h
  46         WHERE   parent_id is null
  47         UNION ALL
  48         SELECT  hi, q.level + 1 AS level, breadcrumb || id
  49         FROM    q
  50         JOIN    comment hi
  51         ON      hi.parent_id = (q.h).id
  52 )
  53 SELECT  (q.h).id,
  54         (q.h).parent_id,
  55         level,
  56         breadcrumb::VARCHAR AS path,
  57         text2ltree('0.' || array_to_string(breadcrumb, '.')) as ltree_path
  58 FROM    q
  59 ORDER BY
  60         breadcrumb;
  61
  62 -- Remove indexes and foreign key constraints, and disable triggers for faster updates
  63 alter table comment disable trigger all;
  64
  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;
  69
  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;
  74
  75 -- Add the ltree column
  76 update comment c
  77 set path = ct.ltree_path
  78 from comment_temp ct
  79 where c.id = ct.id;
  80
  81 -- Update the child counts
  82 update comment_aggregates ca set child_count = c2.child_count
  83 from (
  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
  86   group by c.id
  87 ) as c2
  88 where ca.comment_id = c2.id;
  89
  90 -- Delete comments at a depth of > 150, otherwise the index creation below will fail
  91 delete from comment where nlevel(path) > 150;
  92
  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
  96 );
  97
  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
 101 );
 102
 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);
 107
 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);
 111
 112 -- Create the index
 113 create index idx_path_gist on comment using gist (path);
 114
 115 -- Drop the parent_id column
 116 alter table comment drop column parent_id cascade;
 117
 118 alter table comment enable trigger all;