~sircmpwn/sr.ht-dev

This thread contains a patchset. You're looking at the original emails, but you may wish to use the patch review UI. Review patch
4 3

[PATCH lists.sr.ht] schema: Rename SQL indexes to PostgreSQL-style

Details
Message ID
<20230314020044.30069-1-adnan@maolood.com>
DKIM signature
missing
Download raw message
Patch: +52 -12
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

[lists.sr.ht/patches] build failed

builds.sr.ht <builds@sr.ht>
Details
Message ID
<CR5QKF5JZD1X.3L0N7SBVKZT1T@cirno2>
In-Reply-To
<20230314020044.30069-1-adnan@maolood.com> (view parent)
DKIM signature
missing
Download raw message
lists.sr.ht/patches: FAILED in 3m39s

[schema: Rename SQL indexes to PostgreSQL-style][0] from [Adnan Maolood][1]

[0]: https://lists.sr.ht/~sircmpwn/sr.ht-dev/patches/39692
[1]: adnan@maolood.com

✓ #956805 SUCCESS lists.sr.ht/patches/debian.yml    https://builds.sr.ht/~sircmpwn/job/956805
✓ #956803 SUCCESS lists.sr.ht/patches/alpine.yml    https://builds.sr.ht/~sircmpwn/job/956803
✗ #956804 FAILED  lists.sr.ht/patches/archlinux.yml https://builds.sr.ht/~sircmpwn/job/956804
Details
Message ID
<CRTZ8GGSJFZW.1XDCMZT74Z60L@taiga>
In-Reply-To
<20230314020044.30069-1-adnan@maolood.com> (view parent)
DKIM signature
missing
Download raw message
More background?
Details
Message ID
<CRYKHG8KJN1E.GIP3543GONT8@framework>
In-Reply-To
<CRTZ8GGSJFZW.1XDCMZT74Z60L@taiga> (view parent)
DKIM signature
missing
Download raw message
On Tue Apr 11, 2023 at 9:56 AM EDT, Drew DeVault wrote:
> More background?

This is something that you had wanted to do as part of the move away
from alembic. Is this no longer wanted?
Details
Message ID
<CRYYU6O9NB0A.NB9ZX16MDSR@taiga>
In-Reply-To
<CRYKHG8KJN1E.GIP3543GONT8@framework> (view parent)
DKIM signature
missing
Download raw message
On Mon Apr 17, 2023 at 1:25 AM CEST, Adnan Maolood wrote:
> This is something that you had wanted to do as part of the move away
> from alembic. Is this no longer wanted?

Right, I remember now. Will bring in this patch shortly.
Reply to thread Export thread (mbox)