We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
Database is out of sync with models
Autogenerating a migration from 0abc1b27707bdf9a1ca94ba8392f0086b1eeb492 produces some differences that Alembic is attempting to rectify:

Running:
```
$ docker-compose run web python -m warehouse db revision --autogenerate --message "Sync"
```

Produces:
```python
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
"""
Sync

Revision ID: 71ccb4f929b0
Revises: 5ea52744d154
Create Date: 2019-07-26 17:00:45.297526
"""

from alembic import op
import sqlalchemy as sa


revision = '71ccb4f929b0'
down_revision = '5ea52744d154'

# Note: It is VERY important to ensure that a migration does not lock for a
#       long period of time and to ensure that each individual migration does
#       not break compatibility with the *previous* version of the code base.
#       This is because the migrations will be ran automatically as part of the
#       deployment process, but while the previous version of the code is still
#       up and running. Thus backwards incompatible changes must be broken up
#       over multiple migrations inside of multiple pull requests in order to
#       phase them in over multiple deploys.

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_unique_constraint('_user_macaroons_description_uc', 'macaroons', ['description', 'user_id'])
    op.drop_index('user_security_keys_label_key', table_name='user_security_keys')
    op.create_unique_constraint('user_security_keys_label_key', 'user_security_keys', ['label'])
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('user_security_keys_label_key', 'user_security_keys', type_='unique')
    op.create_index('user_security_keys_label_key', 'user_security_keys', ['user_id'], unique=False)
    op.drop_constraint('_user_macaroons_description_uc', 'macaroons', type_='unique')
    # ### end Alembic commands ### 
```

Ideally both `upgrade` and `downgrade` would be no-ops here.

--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `warehouse/migrations/versions/48def930fcfd_webauthn_and_macaroon_constraints.py`
Content:
```
1 # Licensed under the Apache License, Version 2.0 (the "License");
2 # you may not use this file except in compliance with the License.
3 # You may obtain a copy of the License at
4 #
5 # http://www.apache.org/licenses/LICENSE-2.0
6 #
7 # Unless required by applicable law or agreed to in writing, software
8 # distributed under the License is distributed on an "AS IS" BASIS,
9 # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
10 # See the License for the specific language governing permissions and
11 # limitations under the License.
12 """
13 WebAuthn and Macaroon constraints
14 
15 Revision ID: 48def930fcfd
16 Revises: 5ea52744d154
17 Create Date: 2019-07-26 17:55:41.802528
18 """
19 
20 from alembic import op
21 
22 revision = "48def930fcfd"
23 down_revision = "5ea52744d154"
24 
25 
26 def upgrade():
27     op.create_unique_constraint(
28         "_user_macaroons_description_uc", "macaroons", ["description", "user_id"]
29     )
30     op.drop_index("user_security_keys_label_key", table_name="user_security_keys")
31     op.create_unique_constraint(
32         "user_security_keys_label_key", "user_security_keys", ["label"]
33     )
34 
35 
36 def downgrade():
37     op.drop_constraint(
38         "user_security_keys_label_key", "user_security_keys", type_="unique"
39     )
40     op.create_index(
41         "user_security_keys_label_key", "user_security_keys", ["user_id"], unique=False
42     )
43     op.drop_constraint("_user_macaroons_description_uc", "macaroons", type_="unique")
44
```

Path: `warehouse/accounts/models.py`
Content:
```
1 # Licensed under the Apache License, Version 2.0 (the "License");
2 # you may not use this file except in compliance with the License.
3 # You may obtain a copy of the License at
4 #
5 # http://www.apache.org/licenses/LICENSE-2.0
6 #
7 # Unless required by applicable law or agreed to in writing, software
8 # distributed under the License is distributed on an "AS IS" BASIS,
9 # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
10 # See the License for the specific language governing permissions and
11 # limitations under the License.
12 
13 import enum
14 
15 from citext import CIText
16 from sqlalchemy import (
17     Binary,
18     Boolean,
19     CheckConstraint,
20     Column,
21     DateTime,
22     Enum,
23     ForeignKey,
24     Index,
25     Integer,
26     String,
27     UniqueConstraint,
28     orm,
29     select,
30     sql,
31 )
32 from sqlalchemy.dialects.postgresql import UUID
33 from sqlalchemy.ext.hybrid import hybrid_property
34 from sqlalchemy.orm.exc import NoResultFound
35 
36 from warehouse import db
37 from warehouse.sitemap.models import SitemapMixin
38 from warehouse.utils.attrs import make_repr
39 
40 
41 class UserFactory:
42     def __init__(self, request):
43         self.request = request
44 
45     def __getitem__(self, username):
46         try:
47             return self.request.db.query(User).filter(User.username == username).one()
48         except NoResultFound:
49             raise KeyError from None
50 
51 
52 class DisableReason(enum.Enum):
53 
54     CompromisedPassword = "password compromised"
55 
56 
57 class User(SitemapMixin, db.Model):
58 
59     __tablename__ = "users"
60     __table_args__ = (
61         CheckConstraint("length(username) <= 50", name="users_valid_username_length"),
62         CheckConstraint(
63             "username ~* '^([A-Z0-9]|[A-Z0-9][A-Z0-9._-]*[A-Z0-9])$'",
64             name="users_valid_username",
65         ),
66     )
67 
68     __repr__ = make_repr("username")
69 
70     username = Column(CIText, nullable=False, unique=True)
71     name = Column(String(length=100), nullable=False)
72     password = Column(String(length=128), nullable=False)
73     password_date = Column(DateTime, nullable=True, server_default=sql.func.now())
74     is_active = Column(Boolean, nullable=False, server_default=sql.false())
75     is_superuser = Column(Boolean, nullable=False, server_default=sql.false())
76     is_moderator = Column(Boolean, nullable=False, server_default=sql.false())
77     date_joined = Column(DateTime, server_default=sql.func.now())
78     last_login = Column(DateTime, nullable=False, server_default=sql.func.now())
79     disabled_for = Column(
80         Enum(DisableReason, values_callable=lambda x: [e.value for e in x]),
81         nullable=True,
82     )
83 
84     totp_secret = Column(Binary(length=20), nullable=True)
85 
86     webauthn = orm.relationship(
87         "WebAuthn", backref="user", cascade="all, delete-orphan", lazy=False
88     )
89 
90     emails = orm.relationship(
91         "Email", backref="user", cascade="all, delete-orphan", lazy=False
92     )
93 
94     macaroons = orm.relationship(
95         "Macaroon", backref="user", cascade="all, delete-orphan", lazy=False
96     )
97 
98     @property
99     def primary_email(self):
100         primaries = [x for x in self.emails if x.primary]
101         if primaries:
102             return primaries[0]
103 
104     @hybrid_property
105     def email(self):
106         primary_email = self.primary_email
107         return primary_email.email if primary_email else None
108 
109     @email.expression
110     def email(self):
111         return (
112             select([Email.email])
113             .where((Email.user_id == self.id) & (Email.primary.is_(True)))
114             .as_scalar()
115         )
116 
117     @property
118     def has_two_factor(self):
119         return self.totp_secret is not None or len(self.webauthn) > 0
120 
121     @property
122     def has_primary_verified_email(self):
123         return self.primary_email is not None and self.primary_email.verified
124 
125 
126 class WebAuthn(db.Model):
127     __tablename__ = "user_security_keys"
128     __table_args__ = (
129         UniqueConstraint("label", name="user_security_keys_label_key"),
130         Index("user_security_keys_label_key", "user_id"),
131     )
132 
133     user_id = Column(
134         UUID(as_uuid=True),
135         ForeignKey("users.id", deferrable=True, initially="DEFERRED"),
136         nullable=False,
137     )
138     label = Column(String, nullable=False)
139     credential_id = Column(String, unique=True, nullable=False)
140     public_key = Column(String, unique=True, nullable=True)
141     sign_count = Column(Integer, default=0)
142 
143 
144 class UnverifyReasons(enum.Enum):
145 
146     SpamComplaint = "spam complaint"
147     HardBounce = "hard bounce"
148     SoftBounce = "soft bounce"
149 
150 
151 class Email(db.ModelBase):
152 
153     __tablename__ = "user_emails"
154     __table_args__ = (
155         UniqueConstraint("email", name="user_emails_email_key"),
156         Index("user_emails_user_id", "user_id"),
157     )
158 
159     id = Column(Integer, primary_key=True, nullable=False)
160     user_id = Column(
161         UUID(as_uuid=True),
162         ForeignKey("users.id", deferrable=True, initially="DEFERRED"),
163         nullable=False,
164     )
165     email = Column(String(length=254), nullable=False)
166     primary = Column(Boolean, nullable=False)
167     verified = Column(Boolean, nullable=False)
168 
169     # Deliverability information
170     unverify_reason = Column(
171         Enum(UnverifyReasons, values_callable=lambda x: [e.value for e in x]),
172         nullable=True,
173     )
174     transient_bounces = Column(Integer, nullable=False, server_default=sql.text("0"))
175
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/warehouse/accounts/models.py b/warehouse/accounts/models.py
--- a/warehouse/accounts/models.py
+++ b/warehouse/accounts/models.py
@@ -126,8 +126,7 @@
 class WebAuthn(db.Model):
     __tablename__ = "user_security_keys"
     __table_args__ = (
-        UniqueConstraint("label", name="user_security_keys_label_key"),
-        Index("user_security_keys_label_key", "user_id"),
+        UniqueConstraint("label", "user_id", name="_user_security_keys_label_uc"),
     )
 
     user_id = Column(
diff --git a/warehouse/migrations/versions/48def930fcfd_webauthn_and_macaroon_constraints.py b/warehouse/migrations/versions/48def930fcfd_webauthn_and_macaroon_constraints.py
--- a/warehouse/migrations/versions/48def930fcfd_webauthn_and_macaroon_constraints.py
+++ b/warehouse/migrations/versions/48def930fcfd_webauthn_and_macaroon_constraints.py
@@ -27,17 +27,17 @@
     op.create_unique_constraint(
         "_user_macaroons_description_uc", "macaroons", ["description", "user_id"]
     )
-    op.drop_index("user_security_keys_label_key", table_name="user_security_keys")
     op.create_unique_constraint(
-        "user_security_keys_label_key", "user_security_keys", ["label"]
+        "_user_security_keys_label_uc", "user_security_keys", ["label", "user_id"]
     )
+    op.drop_index("user_security_keys_label_key", table_name="user_security_keys")
 
 
 def downgrade():
-    op.drop_constraint(
-        "user_security_keys_label_key", "user_security_keys", type_="unique"
-    )
     op.create_index(
         "user_security_keys_label_key", "user_security_keys", ["user_id"], unique=False
     )
+    op.drop_constraint(
+        "_user_security_keys_label_uc", "user_security_keys", type_="unique"
+    )
     op.drop_constraint("_user_macaroons_description_uc", "macaroons", type_="unique")

{"golden_diff": "diff --git a/warehouse/accounts/models.py b/warehouse/accounts/models.py\n--- a/warehouse/accounts/models.py\n+++ b/warehouse/accounts/models.py\n@@ -126,8 +126,7 @@\n class WebAuthn(db.Model):\n     __tablename__ = \"user_security_keys\"\n     __table_args__ = (\n-        UniqueConstraint(\"label\", name=\"user_security_keys_label_key\"),\n-        Index(\"user_security_keys_label_key\", \"user_id\"),\n+        UniqueConstraint(\"label\", \"user_id\", name=\"_user_security_keys_label_uc\"),\n     )\n \n     user_id = Column(\ndiff --git a/warehouse/migrations/versions/48def930fcfd_webauthn_and_macaroon_constraints.py b/warehouse/migrations/versions/48def930fcfd_webauthn_and_macaroon_constraints.py\n--- a/warehouse/migrations/versions/48def930fcfd_webauthn_and_macaroon_constraints.py\n+++ b/warehouse/migrations/versions/48def930fcfd_webauthn_and_macaroon_constraints.py\n@@ -27,17 +27,17 @@\n     op.create_unique_constraint(\n         \"_user_macaroons_description_uc\", \"macaroons\", [\"description\", \"user_id\"]\n     )\n-    op.drop_index(\"user_security_keys_label_key\", table_name=\"user_security_keys\")\n     op.create_unique_constraint(\n-        \"user_security_keys_label_key\", \"user_security_keys\", [\"label\"]\n+        \"_user_security_keys_label_uc\", \"user_security_keys\", [\"label\", \"user_id\"]\n     )\n+    op.drop_index(\"user_security_keys_label_key\", table_name=\"user_security_keys\")\n \n \n def downgrade():\n-    op.drop_constraint(\n-        \"user_security_keys_label_key\", \"user_security_keys\", type_=\"unique\"\n-    )\n     op.create_index(\n         \"user_security_keys_label_key\", \"user_security_keys\", [\"user_id\"], unique=False\n     )\n+    op.drop_constraint(\n+        \"_user_security_keys_label_uc\", \"user_security_keys\", type_=\"unique\"\n+    )\n     op.drop_constraint(\"_user_macaroons_description_uc\", \"macaroons\", type_=\"unique\")\n", "issue": "Database is out of sync with models\nAutogenerating a migration from 0abc1b27707bdf9a1ca94ba8392f0086b1eeb492 produces some differences that Alembic is attempting to rectify:\r\n\r\nRunning:\r\n```\r\n$ docker-compose run web python -m warehouse db revision --autogenerate --message \"Sync\"\r\n```\r\n\r\nProduces:\r\n```python\r\n# Licensed under the Apache License, Version 2.0 (the \"License\");\r\n# you may not use this file except in compliance with the License.\r\n# You may obtain a copy of the License at\r\n#\r\n# http://www.apache.org/licenses/LICENSE-2.0\r\n#\r\n# Unless required by applicable law or agreed to in writing, software\r\n# distributed under the License is distributed on an \"AS IS\" BASIS,\r\n# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\r\n# See the License for the specific language governing permissions and\r\n# limitations under the License.\r\n\"\"\"\r\nSync\r\n\r\nRevision ID: 71ccb4f929b0\r\nRevises: 5ea52744d154\r\nCreate Date: 2019-07-26 17:00:45.297526\r\n\"\"\"\r\n\r\nfrom alembic import op\r\nimport sqlalchemy as sa\r\n\r\n\r\nrevision = '71ccb4f929b0'\r\ndown_revision = '5ea52744d154'\r\n\r\n# Note: It is VERY important to ensure that a migration does not lock for a\r\n#       long period of time and to ensure that each individual migration does\r\n#       not break compatibility with the *previous* version of the code base.\r\n#       This is because the migrations will be ran automatically as part of the\r\n#       deployment process, but while the previous version of the code is still\r\n#       up and running. Thus backwards incompatible changes must be broken up\r\n#       over multiple migrations inside of multiple pull requests in order to\r\n#       phase them in over multiple deploys.\r\n\r\ndef upgrade():\r\n    # ### commands auto generated by Alembic - please adjust! ###\r\n    op.create_unique_constraint('_user_macaroons_description_uc', 'macaroons', ['description', 'user_id'])\r\n    op.drop_index('user_security_keys_label_key', table_name='user_security_keys')\r\n    op.create_unique_constraint('user_security_keys_label_key', 'user_security_keys', ['label'])\r\n    # ### end Alembic commands ###\r\n\r\n\r\ndef downgrade():\r\n    # ### commands auto generated by Alembic - please adjust! ###\r\n    op.drop_constraint('user_security_keys_label_key', 'user_security_keys', type_='unique')\r\n    op.create_index('user_security_keys_label_key', 'user_security_keys', ['user_id'], unique=False)\r\n    op.drop_constraint('_user_macaroons_description_uc', 'macaroons', type_='unique')\r\n    # ### end Alembic commands ### \r\n```\r\n\r\nIdeally both `upgrade` and `downgrade` would be no-ops here.\n", "before_files": [{"content": "# Licensed under the Apache License, Version 2.0 (the \"License\");\n# you may not use this file except in compliance with the License.\n# You may obtain a copy of the License at\n#\n# http://www.apache.org/licenses/LICENSE-2.0\n#\n# Unless required by applicable law or agreed to in writing, software\n# distributed under the License is distributed on an \"AS IS\" BASIS,\n# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n# See the License for the specific language governing permissions and\n# limitations under the License.\n\"\"\"\nWebAuthn and Macaroon constraints\n\nRevision ID: 48def930fcfd\nRevises: 5ea52744d154\nCreate Date: 2019-07-26 17:55:41.802528\n\"\"\"\n\nfrom alembic import op\n\nrevision = \"48def930fcfd\"\ndown_revision = \"5ea52744d154\"\n\n\ndef upgrade():\n    op.create_unique_constraint(\n        \"_user_macaroons_description_uc\", \"macaroons\", [\"description\", \"user_id\"]\n    )\n    op.drop_index(\"user_security_keys_label_key\", table_name=\"user_security_keys\")\n    op.create_unique_constraint(\n        \"user_security_keys_label_key\", \"user_security_keys\", [\"label\"]\n    )\n\n\ndef downgrade():\n    op.drop_constraint(\n        \"user_security_keys_label_key\", \"user_security_keys\", type_=\"unique\"\n    )\n    op.create_index(\n        \"user_security_keys_label_key\", \"user_security_keys\", [\"user_id\"], unique=False\n    )\n    op.drop_constraint(\"_user_macaroons_description_uc\", \"macaroons\", type_=\"unique\")\n", "path": "warehouse/migrations/versions/48def930fcfd_webauthn_and_macaroon_constraints.py"}, {"content": "# Licensed under the Apache License, Version 2.0 (the \"License\");\n# you may not use this file except in compliance with the License.\n# You may obtain a copy of the License at\n#\n# http://www.apache.org/licenses/LICENSE-2.0\n#\n# Unless required by applicable law or agreed to in writing, software\n# distributed under the License is distributed on an \"AS IS\" BASIS,\n# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n# See the License for the specific language governing permissions and\n# limitations under the License.\n\nimport enum\n\nfrom citext import CIText\nfrom sqlalchemy import (\n    Binary,\n    Boolean,\n    CheckConstraint,\n    Column,\n    DateTime,\n    Enum,\n    ForeignKey,\n    Index,\n    Integer,\n    String,\n    UniqueConstraint,\n    orm,\n    select,\n    sql,\n)\nfrom sqlalchemy.dialects.postgresql import UUID\nfrom sqlalchemy.ext.hybrid import hybrid_property\nfrom sqlalchemy.orm.exc import NoResultFound\n\nfrom warehouse import db\nfrom warehouse.sitemap.models import SitemapMixin\nfrom warehouse.utils.attrs import make_repr\n\n\nclass UserFactory:\n    def __init__(self, request):\n        self.request = request\n\n    def __getitem__(self, username):\n        try:\n            return self.request.db.query(User).filter(User.username == username).one()\n        except NoResultFound:\n            raise KeyError from None\n\n\nclass DisableReason(enum.Enum):\n\n    CompromisedPassword = \"password compromised\"\n\n\nclass User(SitemapMixin, db.Model):\n\n    __tablename__ = \"users\"\n    __table_args__ = (\n        CheckConstraint(\"length(username) <= 50\", name=\"users_valid_username_length\"),\n        CheckConstraint(\n            \"username ~* '^([A-Z0-9]|[A-Z0-9][A-Z0-9._-]*[A-Z0-9])$'\",\n            name=\"users_valid_username\",\n        ),\n    )\n\n    __repr__ = make_repr(\"username\")\n\n    username = Column(CIText, nullable=False, unique=True)\n    name = Column(String(length=100), nullable=False)\n    password = Column(String(length=128), nullable=False)\n    password_date = Column(DateTime, nullable=True, server_default=sql.func.now())\n    is_active = Column(Boolean, nullable=False, server_default=sql.false())\n    is_superuser = Column(Boolean, nullable=False, server_default=sql.false())\n    is_moderator = Column(Boolean, nullable=False, server_default=sql.false())\n    date_joined = Column(DateTime, server_default=sql.func.now())\n    last_login = Column(DateTime, nullable=False, server_default=sql.func.now())\n    disabled_for = Column(\n        Enum(DisableReason, values_callable=lambda x: [e.value for e in x]),\n        nullable=True,\n    )\n\n    totp_secret = Column(Binary(length=20), nullable=True)\n\n    webauthn = orm.relationship(\n        \"WebAuthn\", backref=\"user\", cascade=\"all, delete-orphan\", lazy=False\n    )\n\n    emails = orm.relationship(\n        \"Email\", backref=\"user\", cascade=\"all, delete-orphan\", lazy=False\n    )\n\n    macaroons = orm.relationship(\n        \"Macaroon\", backref=\"user\", cascade=\"all, delete-orphan\", lazy=False\n    )\n\n    @property\n    def primary_email(self):\n        primaries = [x for x in self.emails if x.primary]\n        if primaries:\n            return primaries[0]\n\n    @hybrid_property\n    def email(self):\n        primary_email = self.primary_email\n        return primary_email.email if primary_email else None\n\n    @email.expression\n    def email(self):\n        return (\n            select([Email.email])\n            .where((Email.user_id == self.id) & (Email.primary.is_(True)))\n            .as_scalar()\n        )\n\n    @property\n    def has_two_factor(self):\n        return self.totp_secret is not None or len(self.webauthn) > 0\n\n    @property\n    def has_primary_verified_email(self):\n        return self.primary_email is not None and self.primary_email.verified\n\n\nclass WebAuthn(db.Model):\n    __tablename__ = \"user_security_keys\"\n    __table_args__ = (\n        UniqueConstraint(\"label\", name=\"user_security_keys_label_key\"),\n        Index(\"user_security_keys_label_key\", \"user_id\"),\n    )\n\n    user_id = Column(\n        UUID(as_uuid=True),\n        ForeignKey(\"users.id\", deferrable=True, initially=\"DEFERRED\"),\n        nullable=False,\n    )\n    label = Column(String, nullable=False)\n    credential_id = Column(String, unique=True, nullable=False)\n    public_key = Column(String, unique=True, nullable=True)\n    sign_count = Column(Integer, default=0)\n\n\nclass UnverifyReasons(enum.Enum):\n\n    SpamComplaint = \"spam complaint\"\n    HardBounce = \"hard bounce\"\n    SoftBounce = \"soft bounce\"\n\n\nclass Email(db.ModelBase):\n\n    __tablename__ = \"user_emails\"\n    __table_args__ = (\n        UniqueConstraint(\"email\", name=\"user_emails_email_key\"),\n        Index(\"user_emails_user_id\", \"user_id\"),\n    )\n\n    id = Column(Integer, primary_key=True, nullable=False)\n    user_id = Column(\n        UUID(as_uuid=True),\n        ForeignKey(\"users.id\", deferrable=True, initially=\"DEFERRED\"),\n        nullable=False,\n    )\n    email = Column(String(length=254), nullable=False)\n    primary = Column(Boolean, nullable=False)\n    verified = Column(Boolean, nullable=False)\n\n    # Deliverability information\n    unverify_reason = Column(\n        Enum(UnverifyReasons, values_callable=lambda x: [e.value for e in x]),\n        nullable=True,\n    )\n    transient_bounces = Column(Integer, nullable=False, server_default=sql.text(\"0\"))\n", "path": "warehouse/accounts/models.py"}], "after_files": [{"content": "# Licensed under the Apache License, Version 2.0 (the \"License\");\n# you may not use this file except in compliance with the License.\n# You may obtain a copy of the License at\n#\n# http://www.apache.org/licenses/LICENSE-2.0\n#\n# Unless required by applicable law or agreed to in writing, software\n# distributed under the License is distributed on an \"AS IS\" BASIS,\n# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n# See the License for the specific language governing permissions and\n# limitations under the License.\n\"\"\"\nWebAuthn and Macaroon constraints\n\nRevision ID: 48def930fcfd\nRevises: 5ea52744d154\nCreate Date: 2019-07-26 17:55:41.802528\n\"\"\"\n\nfrom alembic import op\n\nrevision = \"48def930fcfd\"\ndown_revision = \"5ea52744d154\"\n\n\ndef upgrade():\n    op.create_unique_constraint(\n        \"_user_macaroons_description_uc\", \"macaroons\", [\"description\", \"user_id\"]\n    )\n    op.create_unique_constraint(\n        \"_user_security_keys_label_uc\", \"user_security_keys\", [\"label\", \"user_id\"]\n    )\n    op.drop_index(\"user_security_keys_label_key\", table_name=\"user_security_keys\")\n\n\ndef downgrade():\n    op.create_index(\n        \"user_security_keys_label_key\", \"user_security_keys\", [\"user_id\"], unique=False\n    )\n    op.drop_constraint(\n        \"_user_security_keys_label_uc\", \"user_security_keys\", type_=\"unique\"\n    )\n    op.drop_constraint(\"_user_macaroons_description_uc\", \"macaroons\", type_=\"unique\")\n", "path": "warehouse/migrations/versions/48def930fcfd_webauthn_and_macaroon_constraints.py"}, {"content": "# Licensed under the Apache License, Version 2.0 (the \"License\");\n# you may not use this file except in compliance with the License.\n# You may obtain a copy of the License at\n#\n# http://www.apache.org/licenses/LICENSE-2.0\n#\n# Unless required by applicable law or agreed to in writing, software\n# distributed under the License is distributed on an \"AS IS\" BASIS,\n# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n# See the License for the specific language governing permissions and\n# limitations under the License.\n\nimport enum\n\nfrom citext import CIText\nfrom sqlalchemy import (\n    Binary,\n    Boolean,\n    CheckConstraint,\n    Column,\n    DateTime,\n    Enum,\n    ForeignKey,\n    Index,\n    Integer,\n    String,\n    UniqueConstraint,\n    orm,\n    select,\n    sql,\n)\nfrom sqlalchemy.dialects.postgresql import UUID\nfrom sqlalchemy.ext.hybrid import hybrid_property\nfrom sqlalchemy.orm.exc import NoResultFound\n\nfrom warehouse import db\nfrom warehouse.sitemap.models import SitemapMixin\nfrom warehouse.utils.attrs import make_repr\n\n\nclass UserFactory:\n    def __init__(self, request):\n        self.request = request\n\n    def __getitem__(self, username):\n        try:\n            return self.request.db.query(User).filter(User.username == username).one()\n        except NoResultFound:\n            raise KeyError from None\n\n\nclass DisableReason(enum.Enum):\n\n    CompromisedPassword = \"password compromised\"\n\n\nclass User(SitemapMixin, db.Model):\n\n    __tablename__ = \"users\"\n    __table_args__ = (\n        CheckConstraint(\"length(username) <= 50\", name=\"users_valid_username_length\"),\n        CheckConstraint(\n            \"username ~* '^([A-Z0-9]|[A-Z0-9][A-Z0-9._-]*[A-Z0-9])$'\",\n            name=\"users_valid_username\",\n        ),\n    )\n\n    __repr__ = make_repr(\"username\")\n\n    username = Column(CIText, nullable=False, unique=True)\n    name = Column(String(length=100), nullable=False)\n    password = Column(String(length=128), nullable=False)\n    password_date = Column(DateTime, nullable=True, server_default=sql.func.now())\n    is_active = Column(Boolean, nullable=False, server_default=sql.false())\n    is_superuser = Column(Boolean, nullable=False, server_default=sql.false())\n    is_moderator = Column(Boolean, nullable=False, server_default=sql.false())\n    date_joined = Column(DateTime, server_default=sql.func.now())\n    last_login = Column(DateTime, nullable=False, server_default=sql.func.now())\n    disabled_for = Column(\n        Enum(DisableReason, values_callable=lambda x: [e.value for e in x]),\n        nullable=True,\n    )\n\n    totp_secret = Column(Binary(length=20), nullable=True)\n\n    webauthn = orm.relationship(\n        \"WebAuthn\", backref=\"user\", cascade=\"all, delete-orphan\", lazy=False\n    )\n\n    emails = orm.relationship(\n        \"Email\", backref=\"user\", cascade=\"all, delete-orphan\", lazy=False\n    )\n\n    macaroons = orm.relationship(\n        \"Macaroon\", backref=\"user\", cascade=\"all, delete-orphan\", lazy=False\n    )\n\n    @property\n    def primary_email(self):\n        primaries = [x for x in self.emails if x.primary]\n        if primaries:\n            return primaries[0]\n\n    @hybrid_property\n    def email(self):\n        primary_email = self.primary_email\n        return primary_email.email if primary_email else None\n\n    @email.expression\n    def email(self):\n        return (\n            select([Email.email])\n            .where((Email.user_id == self.id) & (Email.primary.is_(True)))\n            .as_scalar()\n        )\n\n    @property\n    def has_two_factor(self):\n        return self.totp_secret is not None or len(self.webauthn) > 0\n\n    @property\n    def has_primary_verified_email(self):\n        return self.primary_email is not None and self.primary_email.verified\n\n\nclass WebAuthn(db.Model):\n    __tablename__ = \"user_security_keys\"\n    __table_args__ = (\n        UniqueConstraint(\"label\", \"user_id\", name=\"_user_security_keys_label_uc\"),\n    )\n\n    user_id = Column(\n        UUID(as_uuid=True),\n        ForeignKey(\"users.id\", deferrable=True, initially=\"DEFERRED\"),\n        nullable=False,\n    )\n    label = Column(String, nullable=False)\n    credential_id = Column(String, unique=True, nullable=False)\n    public_key = Column(String, unique=True, nullable=True)\n    sign_count = Column(Integer, default=0)\n\n\nclass UnverifyReasons(enum.Enum):\n\n    SpamComplaint = \"spam complaint\"\n    HardBounce = \"hard bounce\"\n    SoftBounce = \"soft bounce\"\n\n\nclass Email(db.ModelBase):\n\n    __tablename__ = \"user_emails\"\n    __table_args__ = (\n        UniqueConstraint(\"email\", name=\"user_emails_email_key\"),\n        Index(\"user_emails_user_id\", \"user_id\"),\n    )\n\n    id = Column(Integer, primary_key=True, nullable=False)\n    user_id = Column(\n        UUID(as_uuid=True),\n        ForeignKey(\"users.id\", deferrable=True, initially=\"DEFERRED\"),\n        nullable=False,\n    )\n    email = Column(String(length=254), nullable=False)\n    primary = Column(Boolean, nullable=False)\n    verified = Column(Boolean, nullable=False)\n\n    # Deliverability information\n    unverify_reason = Column(\n        Enum(UnverifyReasons, values_callable=lambda x: [e.value for e in x]),\n        nullable=True,\n    )\n    transient_bounces = Column(Integer, nullable=False, server_default=sql.text(\"0\"))\n", "path": "warehouse/accounts/models.py"}]}