1 -- Create an instance table
2 -- Holds any connected or unconnected domain
3 create table instance (
5 domain varchar(255) not null unique,
6 published timestamp not null default now(),
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
15 select actor_id from person
17 select actor_id from community
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;
24 alter table person add column
25 instance_id int references instance on update cascade on delete cascade;
27 alter table community add column
28 instance_id int references instance on update cascade on delete cascade;
31 update site set instance_id = i.id
33 where substring(actor_id from '(?:.*://)?(?:www\.)?([^/?]*)') = i.domain;
35 update person set instance_id = i.id
37 where substring(actor_id from '(?:.*://)?(?:www\.)?([^/?]*)') = i.domain;
39 update community set instance_id = i.id
41 where substring(actor_id from '(?:.*://)?(?:www\.)?([^/?]*)') = i.domain;
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);
47 alter table person alter column instance_id set not null;
48 alter table community alter column instance_id set not null;
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
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
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,
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,
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,
99 published timestamp without time zone default now() not null,
100 updated timestamp without time zone
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
123 -- Insert the data into local_site
124 insert into local_site (
130 community_creation_admin_only,
131 require_email_verification,
133 application_question,
136 default_post_listing_type,
138 hide_modlog_mod_names,
139 application_email_admins,
145 true, -- Assume site if setup if there's already a site row
149 community_creation_admin_only,
150 require_email_verification,
152 application_question,
155 default_post_listing_type,
157 hide_modlog_mod_names,
158 application_email_admins,
165 insert into local_site_rate_limit (
168 select id from local_site
171 -- Drop all those columns from 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;