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 substring(p.actor_id from '(?:.*://)?(?:www\.)?([^/?]*)') from (
  13   select actor_id from site
  14   union
  15   select actor_id from person
  16   union
  17   select actor_id from community
  18 ) as p;
  19
  20 -- Alter site, person, and community tables to reference the instance table.
  21 alter table site add column
  22 instance_id int references instance on update cascade on delete cascade;
  23
  24 alter table person add column
  25 instance_id int references instance on update cascade on delete cascade;
  26
  27 alter table community add column
  28 instance_id int references instance on update cascade on delete cascade;
  29
  30 -- Add those columns
  31 update site set instance_id = i.id
  32 from instance i
  33 where substring(actor_id from '(?:.*://)?(?:www\.)?([^/?]*)') = i.domain;
  34
  35 update person set instance_id = i.id
  36 from instance i
  37 where substring(actor_id from '(?:.*://)?(?:www\.)?([^/?]*)') = i.domain;
  38
  39 update community set instance_id = i.id
  40 from instance i
  41 where substring(actor_id from '(?:.*://)?(?:www\.)?([^/?]*)') = i.domain;
  42
  43 -- Make those columns unique not null now
  44 alter table site alter column instance_id set not null;
  45 alter table site add constraint idx_site_instance_unique unique (instance_id);
  46
  47 alter table person alter column instance_id set not null;
  48 alter table community alter column instance_id set not null;
  49
  50 -- Create allowlist and blocklist tables
  51 create table federation_allowlist (
  52   id serial primary key,
  53   instance_id int references instance on update cascade on delete cascade not null unique,
  54   published timestamp not null default now(),
  55   updated timestamp null
  56 );
  57
  58 create table federation_blocklist (
  59   id serial primary key,
  60   instance_id int references instance on update cascade on delete cascade not null unique,
  61   published timestamp not null default now(),
  62   updated timestamp null
  63 );
  64
  65 -- Move all the extra site settings-type columns to a local_site table
  66 -- Add a lot of other fields currently in the lemmy.hjson
  67 create table local_site (
  68   id serial primary key,
  69   site_id int references site on update cascade on delete cascade not null unique,
  70
  71   -- Site table fields
  72   site_setup boolean default false not null,
  73   enable_downvotes boolean default true not null,
  74   open_registration boolean default true not null,
  75   enable_nsfw boolean default true not null,
  76   community_creation_admin_only boolean default false not null,
  77   require_email_verification boolean default false not null,
  78   require_application boolean default true not null,
  79   application_question text default 'to verify that you are human, please explain why you want to create an account on this site'::text,
  80   private_instance boolean default false not null,
  81   default_theme text default 'browser'::text not null,
  82   default_post_listing_type text default 'Local'::text not null,
  83   legal_information text,
  84   hide_modlog_mod_names boolean default true not null,
  85   application_email_admins boolean default false not null,
  86
  87   -- Fields from lemmy.hjson
  88   slur_filter_regex text,
  89   actor_name_max_length int default 20 not null,
  90   federation_enabled boolean default true not null,
  91   federation_debug boolean default false not null,
  92   federation_strict_allowlist boolean default true not null,
  93   federation_http_fetch_retry_limit int default 25 not null,
  94   federation_worker_count int default 64 not null,
  95   captcha_enabled boolean default false not null,
  96   captcha_difficulty varchar(255) default 'medium' not null,
  97
  98   -- Time fields
  99   published timestamp without time zone default now() not null,
 100   updated timestamp without time zone
 101 );
 102
 103 -- 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
 104 create table local_site_rate_limit (
 105   id serial primary key,
 106   local_site_id int references local_site on update cascade on delete cascade not null unique,
 107   message int default 180 not null,
 108   message_per_second int default 60 not null,
 109   post int default 6 not null,
 110   post_per_second int default 600 not null,
 111   register int default 3 not null,
 112   register_per_second int default 3600 not null,
 113   image int default 6 not null,
 114   image_per_second int default 3600 not null,
 115   comment int default 6 not null,
 116   comment_per_second int default 600 not null,
 117   search int default 60 not null,
 118   search_per_second int default 600 not null,
 119   published timestamp without time zone default now() not null,
 120   updated timestamp without time zone
 121 );
 122
 123 -- Insert the data into local_site
 124 insert into local_site (
 125   site_id,
 126   site_setup,
 127   enable_downvotes,
 128   open_registration,
 129   enable_nsfw,
 130   community_creation_admin_only,
 131   require_email_verification,
 132   require_application,
 133   application_question,
 134   private_instance,
 135   default_theme,
 136   default_post_listing_type,
 137   legal_information,
 138   hide_modlog_mod_names,
 139   application_email_admins,
 140   published,
 141   updated
 142 )
 143 select
 144   id,
 145   true, -- Assume site if setup if there's already a site row
 146   enable_downvotes,
 147   open_registration,
 148   enable_nsfw,
 149   community_creation_admin_only,
 150   require_email_verification,
 151   require_application,
 152   application_question,
 153   private_instance,
 154   default_theme,
 155   default_post_listing_type,
 156   legal_information,
 157   hide_modlog_mod_names,
 158   application_email_admins,
 159   published,
 160   updated
 161 from site
 162 order by id limit 1;
 163
 164 -- Default here
 165 insert into local_site_rate_limit (
 166   local_site_id
 167 )
 168 select id from local_site
 169 order by id limit 1;
 170
 171 -- Drop all those columns from site
 172 alter table site
 173   drop column enable_downvotes,
 174   drop column open_registration,
 175   drop column enable_nsfw,
 176   drop column community_creation_admin_only,
 177   drop column require_email_verification,
 178   drop column require_application,
 179   drop column application_question,
 180   drop column private_instance,
 181   drop column default_theme,
 182   drop column default_post_listing_type,
 183   drop column legal_information,
 184   drop column hide_modlog_mod_names,
 185   drop column application_email_admins;
 186