migrations/2019-12-11-181820_add_site_fields/down.sql

   1 -- Drop the columns
   2 DROP VIEW site_view;
   3
   4 ALTER TABLE site
   5     DROP COLUMN enable_downvotes;
   6
   7 ALTER TABLE site
   8     DROP COLUMN open_registration;
   9
  10 ALTER TABLE site
  11     DROP COLUMN enable_nsfw;
  12
  13 -- Rebuild the views
  14 CREATE VIEW site_view AS
  15 SELECT
  16     *,
  17     (
  18         SELECT
  19             name
  20         FROM
  21             user_ u
  22         WHERE
  23             s.creator_id = u.id) AS creator_name,
  24     (
  25         SELECT
  26             count(*)
  27         FROM
  28             user_) AS number_of_users,
  29     (
  30         SELECT
  31             count(*)
  32         FROM
  33             post) AS number_of_posts,
  34     (
  35         SELECT
  36             count(*)
  37         FROM
  38             comment) AS number_of_comments,
  39     (
  40         SELECT
  41             count(*)
  42         FROM
  43             community) AS number_of_communities
  44 FROM
  45     site s;
  46