migrations/2020-12-02-152437_create_site_aggregates/up.sql

   1 -- Add site aggregates
   2 CREATE TABLE site_aggregates (
   3     id serial PRIMARY KEY,
   4     site_id int REFERENCES site ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
   5     users bigint NOT NULL DEFAULT 1,
   6     posts bigint NOT NULL DEFAULT 0,
   7     comments bigint NOT NULL DEFAULT 0,
   8     communities bigint NOT NULL DEFAULT 0
   9 );
  10
  11 INSERT INTO site_aggregates (site_id, users, posts, comments, communities)
  12 SELECT
  13     id AS site_id,
  14     (
  15         SELECT
  16             coalesce(count(*), 0)
  17         FROM
  18             user_
  19         WHERE
  20             local = TRUE) AS users,
  21     (
  22         SELECT
  23             coalesce(count(*), 0)
  24         FROM
  25             post
  26         WHERE
  27             local = TRUE) AS posts,
  28     (
  29         SELECT
  30             coalesce(count(*), 0)
  31         FROM
  32             comment
  33         WHERE
  34             local = TRUE) AS comments,
  35     (
  36         SELECT
  37             coalesce(count(*), 0)
  38         FROM
  39             community
  40         WHERE
  41             local = TRUE) AS communities
  42 FROM
  43     site;
  44
  45 -- initial site add
  46 CREATE FUNCTION site_aggregates_site ()
  47     RETURNS TRIGGER
  48     LANGUAGE plpgsql
  49     AS $$
  50 BEGIN
  51     IF (TG_OP = 'INSERT') THEN
  52         INSERT INTO site_aggregates (site_id)
  53             VALUES (NEW.id);
  54     ELSIF (TG_OP = 'DELETE') THEN
  55         DELETE FROM site_aggregates
  56         WHERE site_id = OLD.id;
  57     END IF;
  58     RETURN NULL;
  59 END
  60 $$;
  61
  62 CREATE TRIGGER site_aggregates_site
  63     AFTER INSERT OR DELETE ON site
  64     FOR EACH ROW
  65     EXECUTE PROCEDURE site_aggregates_site ();
  66
  67 -- Add site aggregate triggers
  68 -- user
  69 CREATE FUNCTION site_aggregates_user_insert ()
  70     RETURNS TRIGGER
  71     LANGUAGE plpgsql
  72     AS $$
  73 BEGIN
  74     UPDATE
  75         site_aggregates
  76     SET
  77         users = users + 1;
  78     RETURN NULL;
  79 END
  80 $$;
  81
  82 CREATE FUNCTION site_aggregates_user_delete ()
  83     RETURNS TRIGGER
  84     LANGUAGE plpgsql
  85     AS $$
  86 BEGIN
  87     -- Join to site since the creator might not be there anymore
  88     UPDATE
  89         site_aggregates sa
  90     SET
  91         users = users - 1
  92     FROM
  93         site s
  94     WHERE
  95         sa.site_id = s.id;
  96     RETURN NULL;
  97 END
  98 $$;
  99
 100 CREATE TRIGGER site_aggregates_user_insert
 101     AFTER INSERT ON user_
 102     FOR EACH ROW
 103     WHEN (NEW.local = TRUE)
 104     EXECUTE PROCEDURE site_aggregates_user_insert ();
 105
 106 CREATE TRIGGER site_aggregates_user_delete
 107     AFTER DELETE ON user_
 108     FOR EACH ROW
 109     WHEN (OLD.local = TRUE)
 110     EXECUTE PROCEDURE site_aggregates_user_delete ();
 111
 112 -- post
 113 CREATE FUNCTION site_aggregates_post_insert ()
 114     RETURNS TRIGGER
 115     LANGUAGE plpgsql
 116     AS $$
 117 BEGIN
 118     UPDATE
 119         site_aggregates
 120     SET
 121         posts = posts + 1;
 122     RETURN NULL;
 123 END
 124 $$;
 125
 126 CREATE FUNCTION site_aggregates_post_delete ()
 127     RETURNS TRIGGER
 128     LANGUAGE plpgsql
 129     AS $$
 130 BEGIN
 131     UPDATE
 132         site_aggregates sa
 133     SET
 134         posts = posts - 1
 135     FROM
 136         site s
 137     WHERE
 138         sa.site_id = s.id;
 139     RETURN NULL;
 140 END
 141 $$;
 142
 143 CREATE TRIGGER site_aggregates_post_insert
 144     AFTER INSERT ON post
 145     FOR EACH ROW
 146     WHEN (NEW.local = TRUE)
 147     EXECUTE PROCEDURE site_aggregates_post_insert ();
 148
 149 CREATE TRIGGER site_aggregates_post_delete
 150     AFTER DELETE ON post
 151     FOR EACH ROW
 152     WHEN (OLD.local = TRUE)
 153     EXECUTE PROCEDURE site_aggregates_post_delete ();
 154
 155 -- comment
 156 CREATE FUNCTION site_aggregates_comment_insert ()
 157     RETURNS TRIGGER
 158     LANGUAGE plpgsql
 159     AS $$
 160 BEGIN
 161     UPDATE
 162         site_aggregates
 163     SET
 164         comments = comments + 1;
 165     RETURN NULL;
 166 END
 167 $$;
 168
 169 CREATE FUNCTION site_aggregates_comment_delete ()
 170     RETURNS TRIGGER
 171     LANGUAGE plpgsql
 172     AS $$
 173 BEGIN
 174     UPDATE
 175         site_aggregates sa
 176     SET
 177         comments = comments - 1
 178     FROM
 179         site s
 180     WHERE
 181         sa.site_id = s.id;
 182     RETURN NULL;
 183 END
 184 $$;
 185
 186 CREATE TRIGGER site_aggregates_comment_insert
 187     AFTER INSERT ON comment
 188     FOR EACH ROW
 189     WHEN (NEW.local = TRUE)
 190     EXECUTE PROCEDURE site_aggregates_comment_insert ();
 191
 192 CREATE TRIGGER site_aggregates_comment_delete
 193     AFTER DELETE ON comment
 194     FOR EACH ROW
 195     WHEN (OLD.local = TRUE)
 196     EXECUTE PROCEDURE site_aggregates_comment_delete ();
 197
 198 -- community
 199 CREATE FUNCTION site_aggregates_community_insert ()
 200     RETURNS TRIGGER
 201     LANGUAGE plpgsql
 202     AS $$
 203 BEGIN
 204     UPDATE
 205         site_aggregates
 206     SET
 207         communities = communities + 1;
 208     RETURN NULL;
 209 END
 210 $$;
 211
 212 CREATE FUNCTION site_aggregates_community_delete ()
 213     RETURNS TRIGGER
 214     LANGUAGE plpgsql
 215     AS $$
 216 BEGIN
 217     UPDATE
 218         site_aggregates sa
 219     SET
 220         communities = communities - 1
 221     FROM
 222         site s
 223     WHERE
 224         sa.site_id = s.id;
 225     RETURN NULL;
 226 END
 227 $$;
 228
 229 CREATE TRIGGER site_aggregates_community_insert
 230     AFTER INSERT ON community
 231     FOR EACH ROW
 232     WHEN (NEW.local = TRUE)
 233     EXECUTE PROCEDURE site_aggregates_community_insert ();
 234
 235 CREATE TRIGGER site_aggregates_community_delete
 236     AFTER DELETE ON community
 237     FOR EACH ROW
 238     WHEN (OLD.local = TRUE)
 239     EXECUTE PROCEDURE site_aggregates_community_delete ();
 240