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)
13 substring(p.actor_id FROM '(?:.*://)?(?:www\.)?([^/?]*)')
30 -- Alter site, person, and community tables to reference the instance table.
32 ADD COLUMN instance_id int REFERENCES instance ON UPDATE CASCADE ON DELETE CASCADE;
35 ADD COLUMN instance_id int REFERENCES instance ON UPDATE CASCADE ON DELETE CASCADE;
38 ADD COLUMN instance_id int REFERENCES instance ON UPDATE CASCADE ON DELETE CASCADE;
48 substring(actor_id FROM '(?:.*://)?(?:www\.)?([^/?]*)') = i.domain;
57 substring(actor_id FROM '(?:.*://)?(?:www\.)?([^/?]*)') = i.domain;
66 substring(actor_id FROM '(?:.*://)?(?:www\.)?([^/?]*)') = i.domain;
68 -- Make those columns unique not null now
70 ALTER COLUMN instance_id SET NOT NULL;
73 ADD CONSTRAINT idx_site_instance_unique UNIQUE (instance_id);
76 ALTER COLUMN instance_id SET NOT NULL;
79 ALTER COLUMN instance_id SET NOT NULL;
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
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
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,
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,
127 published timestamp without time zone DEFAULT now() NOT NULL,
128 updated timestamp without time zone
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
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)
155 TRUE, -- Assume site if setup if there's already a site row
159 community_creation_admin_only,
160 require_email_verification,
162 application_question,
165 default_post_listing_type,
167 hide_modlog_mod_names,
168 application_email_admins,
178 INSERT INTO local_site_rate_limit (local_site_id)
187 -- Drop all those columns from 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;