@@ 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"]
@@ 0,0 1,100 @@
+"""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"),
+ ("gql_user_wh_sub", "user_id"),
+ ("oauthtoken", "user_id"),
+ ("redirect", "owner_id"),
+ ("repo_webhook_subscription", "user_id"),
+ ("repository", "owner_id"),
+ ("sshkey", "user_id"),
+ ("user_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);
+ """)