migrations/2022-10-06-183632_move_blocklist_to_db/up.sql

   1 -- Create an instance table
   2 -- Holds any connected or unconnected domain
   3 CREATE TABLE instance (
   4     id serial PRIMARY KEY,
   5     domain varchar(255) NOT NULL UNIQUE,
   6     published timestamp NOT NULL DEFAULT now(),
   7     updated timestamp NULL
   8 );
   9
  10 -- Insert all the domains to the instance table
  11 INSERT INTO instance (DOMAIN)
  12 SELECT DISTINCT
  13     substring(p.actor_id FROM '(?:.*://)?(?:www\.)?([^/?]*)')
  14 FROM (
  15     SELECT
  16         actor_id
  17     FROM
  18         site
  19     UNION
  20     SELECT
  21         actor_id
  22     FROM
  23         person
  24     UNION
  25     SELECT
  26         actor_id
  27     FROM
  28         community) AS p;
  29
  30 -- Alter site, person, and community tables to reference the instance table.
  31 ALTER TABLE site
  32     ADD COLUMN instance_id int REFERENCES instance ON UPDATE CASCADE ON DELETE CASCADE;
  33
  34 ALTER TABLE person
  35     ADD COLUMN instance_id int REFERENCES instance ON UPDATE CASCADE ON DELETE CASCADE;
  36
  37 ALTER TABLE community
  38     ADD COLUMN instance_id int REFERENCES instance ON UPDATE CASCADE ON DELETE CASCADE;
  39
  40 -- Add those columns
  41 UPDATE
  42     site
  43 SET
  44     instance_id = i.id
  45 FROM
  46     instance i
  47 WHERE
  48     substring(actor_id FROM '(?:.*://)?(?:www\.)?([^/?]*)') = i.domain;
  49
  50 UPDATE
  51     person
  52 SET
  53     instance_id = i.id
  54 FROM
  55     instance i
  56 WHERE
  57     substring(actor_id FROM '(?:.*://)?(?:www\.)?([^/?]*)') = i.domain;
  58
  59 UPDATE
  60     community
  61 SET
  62     instance_id = i.id
  63 FROM
  64     instance i
  65 WHERE
  66     substring(actor_id FROM '(?:.*://)?(?:www\.)?([^/?]*)') = i.domain;
  67
  68 -- Make those columns unique not null now
  69 ALTER TABLE site
  70     ALTER COLUMN instance_id SET NOT NULL;
  71
  72 ALTER TABLE site
  73     ADD CONSTRAINT idx_site_instance_unique UNIQUE (instance_id);
  74
  75 ALTER TABLE person
  76     ALTER COLUMN instance_id SET NOT NULL;
  77
  78 ALTER TABLE community
  79     ALTER COLUMN instance_id SET NOT NULL;
  80
  81 -- Create allowlist and blocklist tables
  82 CREATE TABLE federation_allowlist (
  83     id serial PRIMARY KEY,
  84     instance_id int REFERENCES instance ON UPDATE CASCADE ON DELETE CASCADE NOT NULL UNIQUE,
  85     published timestamp NOT NULL DEFAULT now(),
  86     updated timestamp NULL
  87 );
  88
  89 CREATE TABLE federation_blocklist (
  90     id serial PRIMARY KEY,
  91     instance_id int REFERENCES instance ON UPDATE CASCADE ON DELETE CASCADE NOT NULL UNIQUE,
  92     published timestamp NOT NULL DEFAULT now(),
  93     updated timestamp NULL
  94 );
  95
  96 -- Move all the extra site settings-type columns to a local_site table
  97 -- Add a lot of other fields currently in the lemmy.hjson
  98 CREATE TABLE local_site (
  99     id serial PRIMARY KEY,
 100     site_id int REFERENCES site ON UPDATE CASCADE ON DELETE CASCADE NOT NULL UNIQUE,
 101     -- Site table fields
 102     site_setup boolean DEFAULT FALSE NOT NULL,
 103     enable_downvotes boolean DEFAULT TRUE NOT NULL,
 104     open_registration boolean DEFAULT TRUE NOT NULL,
 105     enable_nsfw boolean DEFAULT TRUE NOT NULL,
 106     community_creation_admin_only boolean DEFAULT FALSE NOT NULL,
 107     require_email_verification boolean DEFAULT FALSE NOT NULL,
 108     require_application boolean DEFAULT TRUE NOT NULL,
 109     application_question text DEFAULT 'to verify that you are human, please explain why you want to create an account on this site' ::text,
 110     private_instance boolean DEFAULT FALSE NOT NULL,
 111     default_theme text DEFAULT 'browser' ::text NOT NULL,
 112     default_post_listing_type text DEFAULT 'Local' ::text NOT NULL,
 113     legal_information text,
 114     hide_modlog_mod_names boolean DEFAULT TRUE NOT NULL,
 115     application_email_admins boolean DEFAULT FALSE NOT NULL,
 116     -- Fields from lemmy.hjson
 117     slur_filter_regex text,
 118     actor_name_max_length int DEFAULT 20 NOT NULL,
 119     federation_enabled boolean DEFAULT TRUE NOT NULL,
 120     federation_debug boolean DEFAULT FALSE NOT NULL,
 121     federation_strict_allowlist boolean DEFAULT TRUE NOT NULL,
 122     federation_http_fetch_retry_limit int DEFAULT 25 NOT NULL,
 123     federation_worker_count int DEFAULT 64 NOT NULL,
 124     captcha_enabled boolean DEFAULT FALSE NOT NULL,
 125     captcha_difficulty varchar(255) DEFAULT 'medium' NOT NULL,
 126     -- Time fields
 127     published timestamp without time zone DEFAULT now() NOT NULL,
 128     updated timestamp without time zone
 129 );
 130
 131 -- local_site_rate_limit is its own table, so as to not go over 32 columns, and force diesel to use the 64-column-tables feature
 132 CREATE TABLE local_site_rate_limit (
 133     id serial PRIMARY KEY,
 134     local_site_id int REFERENCES local_site ON UPDATE CASCADE ON DELETE CASCADE NOT NULL UNIQUE,
 135     message int DEFAULT 180 NOT NULL,
 136     message_per_second int DEFAULT 60 NOT NULL,
 137     post int DEFAULT 6 NOT NULL,
 138     post_per_second int DEFAULT 600 NOT NULL,
 139     register int DEFAULT 3 NOT NULL,
 140     register_per_second int DEFAULT 3600 NOT NULL,
 141     image int DEFAULT 6 NOT NULL,
 142     image_per_second int DEFAULT 3600 NOT NULL,
 143     comment int DEFAULT 6 NOT NULL,
 144     comment_per_second int DEFAULT 600 NOT NULL,
 145     search int DEFAULT 60 NOT NULL,
 146     search_per_second int DEFAULT 600 NOT NULL,
 147     published timestamp without time zone DEFAULT now() NOT NULL,
 148     updated timestamp without time zone
 149 );
 150
 151 -- Insert the data into local_site
 152 INSERT INTO local_site (site_id, site_setup, enable_downvotes, open_registration, enable_nsfw, community_creation_admin_only, require_email_verification, require_application, application_question, private_instance, default_theme, default_post_listing_type, legal_information, hide_modlog_mod_names, application_email_admins, published, updated)
 153 SELECT
 154     id,
 155     TRUE, -- Assume site if setup if there's already a site row
 156     enable_downvotes,
 157     open_registration,
 158     enable_nsfw,
 159     community_creation_admin_only,
 160     require_email_verification,
 161     require_application,
 162     application_question,
 163     private_instance,
 164     default_theme,
 165     default_post_listing_type,
 166     legal_information,
 167     hide_modlog_mod_names,
 168     application_email_admins,
 169     published,
 170     updated
 171 FROM
 172     site
 173 ORDER BY
 174     id
 175 LIMIT 1;
 176
 177 -- Default here
 178 INSERT INTO local_site_rate_limit (local_site_id)
 179 SELECT
 180     id
 181 FROM
 182     local_site
 183 ORDER BY
 184     id
 185 LIMIT 1;
 186
 187 -- Drop all those columns from site
 188 ALTER TABLE site
 189     DROP COLUMN enable_downvotes,
 190     DROP COLUMN open_registration,
 191     DROP COLUMN enable_nsfw,
 192     DROP COLUMN community_creation_admin_only,
 193     DROP COLUMN require_email_verification,
 194     DROP COLUMN require_application,
 195     DROP COLUMN application_question,
 196     DROP COLUMN private_instance,
 197     DROP COLUMN default_theme,
 198     DROP COLUMN default_post_listing_type,
 199     DROP COLUMN legal_information,
 200     DROP COLUMN hide_modlog_mod_names,
 201     DROP COLUMN application_email_admins;
 202