References: https://todo.sr.ht/~sircmpwn/sr.ht/306
---
..._rename_sql_indexes_to_postgresql_style.py | 43 +++++++++++++++++++
schema.sql | 21 ++++-----
2 files changed, 52 insertions(+), 12 deletions(-)
create mode 100644 listssrht/alembic/versions/2a4319aa8d00_rename_sql_indexes_to_postgresql_style.py
diff --git a/listssrht/alembic/versions/2a4319aa8d00_rename_sql_indexes_to_postgresql_style.py b/listssrht/alembic/versions/2a4319aa8d00_rename_sql_indexes_to_postgresql_style.py
new file mode 100644
index 0000000..02d6992
--- /dev/null
+++ b/listssrht/alembic/versions/2a4319aa8d00_rename_sql_indexes_to_postgresql_style.py
@@ -0,0 +1,43 @@
+"""Rename SQL indexes to PostgreSQL-style
+
+Revision ID: 2a4319aa8d00
+Revises: 7faa55b46247
+Create Date: 2023-03-13 21:55:24.211402
+
+"""
+
+# revision identifiers, used by Alembic.
+revision = '2a4319aa8d00'
+down_revision = '7faa55b46247'
+
+from alembic import op
+import sqlalchemy as sa
+
+
+def upgrade():
+ op.execute("""
+ ALTER TABLE "user" RENAME CONSTRAINT user_username_unique TO user_username_key;
+ ALTER TABLE list RENAME CONSTRAINT uq_list_owner_id_name TO list_owner_id_name_key;
+ ALTER TABLE access RENAME CONSTRAINT uq_access_list_id_email TO access_list_id_email_key;
+ ALTER TABLE access RENAME CONSTRAINT uq_access_list_id_user_id TO access_list_id_user_id_key;
+ ALTER TABLE email RENAME CONSTRAINT uq_email_list_message_id TO email_list_id_message_id_key;
+ ALTER TABLE subscription RENAME CONSTRAINT subscription_list_id_email_unique TO subscription_list_id_email_key;
+ ALTER TABLE subscription RENAME CONSTRAINT subscription_list_id_user_id_unique TO subscription_list_id_user_id_key;
+
+ DROP INDEX ix_user_username;
+ ALTER INDEX ix_patchset_tool_key RENAME TO patchset_tool_key_idx;
+ """)
+
+def downgrade():
+ op.execute("""
+ ALTER TABLE "user" RENAME CONSTRAINT user_username_key TO user_username_unique;
+ ALTER TABLE list RENAME CONSTRAINT list_owner_id_name_key TO uq_list_owner_id_name;
+ ALTER TABLE access RENAME CONSTRAINT access_list_id_email_key TO uq_access_list_id_email;
+ ALTER TABLE access RENAME CONSTRAINT access_list_id_user_id_key TO uq_access_list_id_user_id;
+ ALTER TABLE email RENAME CONSTRAINT email_list_id_message_id_key TO uq_email_list_message_id;
+ ALTER TABLE subscription RENAME CONSTRAINT subscription_list_id_email_key TO subscription_list_id_email_unique;
+ ALTER TABLE subscription RENAME CONSTRAINT subscription_list_id_user_id_key TO subscription_list_id_user_id_unique;
+
+ CREATE INDEX ix_user_username ON "user" USING btree (username);
+ ALTER INDEX patchset_tool_key_idx RENAME TO ix_patchset_tool_key;
+ """)
diff --git a/schema.sql b/schema.sql
index ed1a717..f69e080 100644
--- a/schema.sql
+++ b/schema.sql
@@ -28,7 +28,7 @@ CREATE TYPE webhook_event AS ENUM (
CREATE TABLE "user" (
id serial PRIMARY KEY,
- username character varying(256),
+ username character varying(256) UNIQUE,
created timestamp without time zone NOT NULL,
updated timestamp without time zone NOT NULL,
session character varying(128),
@@ -41,12 +41,9 @@ CREATE TABLE "user" (
location character varying(256),
bio character varying(4096),
oauth_revocation_token character varying(256),
- suspension_notice character varying(4096),
- CONSTRAINT user_username_unique UNIQUE (username)
+ suspension_notice character varying(4096)
);
-CREATE INDEX ix_user_username ON "user" USING btree (username);
-
CREATE TABLE list (
id serial PRIMARY KEY,
created timestamp without time zone NOT NULL,
@@ -60,7 +57,7 @@ CREATE TABLE list (
reject_mimetypes character varying DEFAULT 'text/html'::character varying NOT NULL,
import_in_progress boolean DEFAULT false NOT NULL,
visibility visibility NOT NULL,
- CONSTRAINT uq_list_owner_id_name UNIQUE (owner_id, name)
+ UNIQUE (owner_id, name)
);
CREATE TABLE access (
@@ -71,8 +68,8 @@ CREATE TABLE access (
user_id integer REFERENCES "user"(id) ON DELETE CASCADE,
list_id integer NOT NULL REFERENCES list(id) ON DELETE CASCADE,
permissions integer DEFAULT 7 NOT NULL,
- CONSTRAINT uq_access_list_id_email UNIQUE (list_id, email),
- CONSTRAINT uq_access_list_id_user_id UNIQUE (list_id, user_id)
+ UNIQUE (list_id, email),
+ UNIQUE (list_id, user_id)
);
CREATE TABLE email (
@@ -106,7 +103,7 @@ CREATE TABLE email (
patch_subject character varying,
superseded_by_id integer REFERENCES email(id) ON DELETE SET NULL,
- CONSTRAINT uq_email_list_message_id UNIQUE (list_id, message_id)
+ UNIQUE (list_id, message_id)
);
-- TODO: Remove me
@@ -153,7 +150,7 @@ CREATE TABLE patchset_tool (
key character varying(128) NOT NULL
);
-CREATE INDEX ix_patchset_tool_key ON patchset_tool USING btree (key);
+CREATE INDEX patchset_tool_key_idx ON patchset_tool USING btree (key);
CREATE TABLE subscription (
id serial PRIMARY KEY,
@@ -164,8 +161,8 @@ CREATE TABLE subscription (
user_id integer REFERENCES "user"(id) ON DELETE CASCADE,
CONSTRAINT subscription_email_xor_user_id
CHECK ((((email IS NULL) OR (user_id IS NULL)) AND ((email IS NOT NULL) OR (user_id IS NOT NULL)))),
- CONSTRAINT subscription_list_id_email_unique UNIQUE (list_id, email),
- CONSTRAINT subscription_list_id_user_id_unique UNIQUE (list_id, user_id)
+ UNIQUE (list_id, email),
+ UNIQUE (list_id, user_id)
);
-- GraphQL webhooks
base-commit: fdc1c4c7c546ed32e42c8f014873e94c2e7d91f1
--
2.39.2