[PATCH hg.sr.ht] hgsrht: Use canonical user IDs
Export this patch
# HG changeset patch
# User Adnan Maolood <me@adnano.co>
# Date 1657824474 14400
# Thu Jul 14 14:47:54 2022 -0400
# Node ID a77343254a2cdaf13ca507928a9b2dc30ac96af3
# Parent fb35ffb8588103afe2051a1c57f5684deac25a27
hgsrht: Use canonical user IDs
Update user IDs across hg.sr.ht to match those of meta.sr.ht.
diff --git a/hgsrht/alembic/versions/5e11e143e2f3_add_user_remote_id.py b/hgsrht/alembic/versions/5e11e143e2f3_add_user_remote_id.py
new file mode 100644
--- /dev/null
+++ b/hgsrht/alembic/versions/5e11e143e2f3_add_user_remote_id.py
@@ -0,0 +1,54 @@
+ """Add user.remote_id+ + Revision ID: 5e11e143e2f3+ Revises: bb87b52896ac+ Create Date: 2022-07-14 14:36:38.664621+ + """+ + # revision identifiers, used by Alembic.+ revision = '5e11e143e2f3'+ down_revision = 'bb87b52896ac'+ + from alembic import op+ import sqlalchemy as sa+ from sqlalchemy.ext.declarative import declarative_base+ from sqlalchemy.orm import scoped_session, sessionmaker+ from srht.crypto import internal_anon+ from srht.database import db+ from srht.graphql import exec_gql+ + Base = declarative_base()+ + class User(Base):+ __tablename__ = "user"+ id = sa.Column(sa.Integer, primary_key=True)+ username = sa.Column(sa.Unicode(256), index=True, unique=True)+ remote_id = sa.Column(sa.Integer, unique=True)+ + def upgrade():+ engine = op.get_bind()+ session = scoped_session(sessionmaker(+ autocommit=False,+ autoflush=False,+ bind=engine))+ Base.query = session.query_property()+ + op.execute("""ALTER TABLE "user" ADD COLUMN remote_id integer UNIQUE""")+ + for user in User.query:+ user.remote_id = fetch_user_id(user.username)+ print(f"~{user.username} id: {user.id} -> {user.remote_id}")+ session.commit()+ + op.execute("""ALTER TABLE "user" ALTER COLUMN remote_id SET NOT NULL""")+ + def downgrade():+ op.drop_column("user", "remote_id")+ + def fetch_user_id(username):+ resp = exec_gql("meta.sr.ht",+ "query($username: String!) { user(username: $username) { id } }",+ user=internal_anon,+ username=username)+ return resp["user"]["id"]--- /dev/null
+++ b/hgsrht/alembic/versions/c9e27b24e019_use_canonical_user_id.py
@@ -0,0 +1,101 @@
+ """Use canonical user ID+ + Revision ID: c9e27b24e019+ Revises: 5e11e143e2f3+ Create Date: 2022-07-14 14:38:49.400479+ + """+ + # revision identifiers, used by Alembic.+ revision = 'c9e27b24e019'+ down_revision = '5e11e143e2f3'+ + from alembic import op+ import sqlalchemy as sa+ + + # These tables all have a column referencing "user"(id)+ tables = [+ ("access", "user_id"),+ ("celery_webhook_subscription", "user_id"),+ ("gql_user_wh_sub", "user_id"),+ ("oauthtoken", "user_id"),+ ("redirect", "owner_id"),+ ("repository", "owner_id"),+ ("sshkey", "user_id"),+ ("user_webhook_subscription", "user_id"),+ ("webhook_subscription", "user_id"),+ ]+ + def upgrade():+ # Drop unique constraints+ op.execute("""+ ALTER TABLE access DROP CONSTRAINT uq_access_user_id_repo_id;+ ALTER TABLE repository DROP CONSTRAINT uq_repo_owner_id_name;+ """)+ + # Drop foreign key constraints and update user IDs+ for (table, col) in tables:+ op.execute(f"""+ ALTER TABLE {table} DROP CONSTRAINT {table}_{col}_fkey;+ UPDATE {table} t SET {col} = u.remote_id FROM "user" u WHERE u.id = t.{col};+ """)+ + # Update primary key+ op.execute("""+ ALTER TABLE "user" DROP CONSTRAINT user_pkey;+ ALTER TABLE "user" DROP CONSTRAINT user_remote_id_key;+ ALTER TABLE "user" RENAME COLUMN id TO old_id;+ ALTER TABLE "user" RENAME COLUMN remote_id TO id;+ ALTER TABLE "user" ADD PRIMARY KEY (id);+ ALTER TABLE "user" ADD UNIQUE (old_id);+ """)+ + # Add foreign key constraints+ for (table, col) in tables:+ op.execute(f"""+ ALTER TABLE {table} ADD CONSTRAINT {table}_{col}_fkey FOREIGN KEY ({col}) REFERENCES "user"(id) ON DELETE CASCADE;+ """)+ + # Add unique constraints+ op.execute("""+ ALTER TABLE access ADD CONSTRAINT uq_access_user_id_repo_id UNIQUE (user_id, repo_id);+ ALTER TABLE repository ADD CONSTRAINT uq_repo_owner_id_name UNIQUE (owner_id, name);+ """)+ + + def downgrade():+ # Drop unique constraints+ op.execute("""+ ALTER TABLE access DROP CONSTRAINT uq_access_user_id_repo_id;+ ALTER TABLE repository DROP CONSTRAINT uq_repo_owner_id_name;+ """)+ + # Drop foreign key constraints and update user IDs+ for (table, col) in tables:+ op.execute(f"""+ ALTER TABLE {table} DROP CONSTRAINT {table}_{col}_fkey;+ UPDATE {table} t SET {col} = u.old_id FROM "user" u WHERE u.id = t.{col};+ """)+ + # Update primary key+ op.execute("""+ ALTER TABLE "user" DROP CONSTRAINT user_pkey;+ ALTER TABLE "user" DROP CONSTRAINT user_old_id_key;+ ALTER TABLE "user" RENAME COLUMN id TO remote_id;+ ALTER TABLE "user" RENAME COLUMN old_id TO id;+ ALTER TABLE "user" ADD PRIMARY KEY (id);+ ALTER TABLE "user" ADD UNIQUE (remote_id);+ """)+ + # Add foreign key constraints+ for (table, col) in tables:+ op.execute(f"""+ ALTER TABLE {table} ADD CONSTRAINT {table}_{col}_fkey FOREIGN KEY ({col}) REFERENCES "user"(id) ON DELETE CASCADE;+ """)+ + # Add unique constraints+ op.execute("""+ ALTER TABLE access ADD CONSTRAINT uq_access_user_id_repo_id UNIQUE (user_id, repo_id);+ ALTER TABLE repository ADD CONSTRAINT uq_repo_owner_id_name UNIQUE (owner_id, name);+ """)
Thanks. I see this is part of a broader change -- does it depend on the
other commits, or can they all be submitted independently?
I'll wait to see if Drew accepts those other changes anyway.