We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
Run flake8 against alembic migrations
*This is a good first issue for new contributors to take on, if you have any questions, please ask on the task or in our [Gitter room](https://gitter.im/freedomofpress/securedrop)!*

## Description

In `.flake8` we currently exclude `securedrop/alembic/versions`. We should be running flake8 against those files by removing the exclusion and fixing any new flake8 errors.

--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `securedrop/alembic/versions/c5a02eb52f2d_dropped_session_nonce_from_journalist_.py`
Content:
```
1 """dropped session_nonce from journalist table and revoked tokens table due to new session implementation
2 
3 Revision ID: c5a02eb52f2d
4 Revises: b7f98cfd6a70
5 Create Date: 2022-04-16 21:25:22.398189
6 
7 """
8 import sqlalchemy as sa
9 from alembic import op
10 
11 # revision identifiers, used by Alembic.
12 revision = "c5a02eb52f2d"
13 down_revision = "b7f98cfd6a70"
14 branch_labels = None
15 depends_on = None
16 
17 
18 def upgrade() -> None:
19     # ### commands auto generated by Alembic - please adjust! ###
20     op.drop_table("revoked_tokens")
21     with op.batch_alter_table("journalists", schema=None) as batch_op:
22         batch_op.drop_column("session_nonce")
23 
24     # ### end Alembic commands ###
25 
26 
27 def downgrade() -> None:
28     """This would have been the easy way, however previous does not have
29     default value and thus up/down assertion fails"""
30     # op.add_column('journalists', sa.Column('session_nonce', sa.Integer(), nullable=False, server_default='0'))
31 
32     conn = op.get_bind()
33     conn.execute("PRAGMA legacy_alter_table=ON")
34     # Save existing journalist table.
35     op.rename_table("journalists", "journalists_tmp")
36 
37     # Add nonce column.
38     op.add_column("journalists_tmp", sa.Column("session_nonce", sa.Integer()))
39 
40     # Populate nonce column.
41     journalists = conn.execute(sa.text("SELECT * FROM journalists_tmp")).fetchall()
42 
43     for journalist in journalists:
44         conn.execute(
45             sa.text(
46                 """UPDATE journalists_tmp SET session_nonce=0 WHERE
47                        id=:id"""
48             ).bindparams(id=journalist.id)
49         )
50 
51     # Now create new table with null constraint applied.
52     op.create_table(
53         "journalists",
54         sa.Column("id", sa.Integer(), nullable=False),
55         sa.Column("uuid", sa.String(length=36), nullable=False),
56         sa.Column("username", sa.String(length=255), nullable=False),
57         sa.Column("first_name", sa.String(length=255), nullable=True),
58         sa.Column("last_name", sa.String(length=255), nullable=True),
59         sa.Column("pw_salt", sa.Binary(), nullable=True),
60         sa.Column("pw_hash", sa.Binary(), nullable=True),
61         sa.Column("passphrase_hash", sa.String(length=256), nullable=True),
62         sa.Column("is_admin", sa.Boolean(), nullable=True),
63         sa.Column("session_nonce", sa.Integer(), nullable=False),
64         sa.Column("otp_secret", sa.String(length=32), nullable=True),
65         sa.Column("is_totp", sa.Boolean(), nullable=True),
66         sa.Column("hotp_counter", sa.Integer(), nullable=True),
67         sa.Column("last_token", sa.String(length=6), nullable=True),
68         sa.Column("created_on", sa.DateTime(), nullable=True),
69         sa.Column("last_access", sa.DateTime(), nullable=True),
70         sa.PrimaryKeyConstraint("id"),
71         sa.UniqueConstraint("username"),
72         sa.UniqueConstraint("uuid"),
73     )
74 
75     conn.execute(
76         """
77         INSERT INTO journalists
78         SELECT id, uuid, username, first_name, last_name, pw_salt, pw_hash,
79                passphrase_hash, is_admin, session_nonce, otp_secret, is_totp,
80                hotp_counter, last_token, created_on, last_access
81         FROM journalists_tmp
82     """
83     )
84 
85     # Now delete the old table.
86     op.drop_table("journalists_tmp")
87 
88     op.create_table(
89         "revoked_tokens",
90         sa.Column("id", sa.INTEGER(), nullable=False),
91         sa.Column("journalist_id", sa.INTEGER(), nullable=False),
92         sa.Column("token", sa.TEXT(), nullable=False),
93         sa.ForeignKeyConstraint(
94             ["journalist_id"],
95             ["journalists.id"],
96         ),
97         sa.PrimaryKeyConstraint("id"),
98         sa.UniqueConstraint("token"),
99     )
100
```

Path: `securedrop/alembic/versions/b7f98cfd6a70_make_filesystem_id_non_nullable.py`
Content:
```
1 """make_filesystem_id_non_nullable
2 
3 Revision ID: b7f98cfd6a70
4 Revises: d9d36b6f4d1e
5 Create Date: 2022-03-18 18:10:27.842201
6 
7 """
8 import sqlalchemy as sa
9 from alembic import op
10 
11 # revision identifiers, used by Alembic.
12 revision = "b7f98cfd6a70"
13 down_revision = "d9d36b6f4d1e"
14 branch_labels = None
15 depends_on = None
16 
17 
18 def upgrade() -> None:
19     # Not having a filesystem_id makes the source useless, so if any of those do exist, we'll
20     # delete them first, as part of this migration.
21     # Because we can't rely on SQLAlchemy's cascade deletion, we have to do it manually.
22     # First we delete out of replies/seen_files/seen_messages (things that refer to things that refer
23     # to sources)
24     op.execute(
25         "DELETE FROM seen_replies WHERE reply_id IN ("
26         "SELECT replies.id FROM replies "
27         "JOIN sources ON sources.id=replies.source_id "
28         "WHERE filesystem_id IS NULL)"
29     )
30     op.execute(
31         "DELETE FROM seen_files WHERE file_id IN ("
32         "SELECT submissions.id FROM submissions "
33         "JOIN sources ON sources.id=submissions.source_id "
34         "WHERE filesystem_id IS NULL)"
35     )
36     op.execute(
37         "DELETE FROM seen_messages WHERE message_id IN ("
38         "SELECT submissions.id FROM submissions "
39         "JOIN sources ON sources.id=submissions.source_id "
40         "WHERE filesystem_id IS NULL)"
41     )
42     # Now things that directly refer to sources
43     for table in ("source_stars", "submissions", "replies"):
44         op.execute(
45             f"DELETE FROM {table} WHERE source_id IN "  # nosec
46             f"(SELECT id FROM sources WHERE filesystem_id IS NULL)"
47         )  # nosec
48     # And now the sources
49     op.execute("DELETE FROM sources WHERE filesystem_id IS NULL")
50     with op.batch_alter_table("sources", schema=None) as batch_op:
51         batch_op.alter_column("filesystem_id", existing_type=sa.VARCHAR(length=96), nullable=False)
52 
53 
54 def downgrade() -> None:
55     with op.batch_alter_table("sources", schema=None) as batch_op:
56         batch_op.alter_column("filesystem_id", existing_type=sa.VARCHAR(length=96), nullable=True)
57
```

Path: `securedrop/alembic/versions/2e24fc7536e8_make_journalist_id_non_nullable.py`
Content:
```
1 """make journalist_id non-nullable
2 
3 Revision ID: 2e24fc7536e8
4 Revises: de00920916bf
5 Create Date: 2022-01-12 19:31:06.186285
6 
7 """
8 import os
9 import uuid
10 
11 import pyotp
12 import sqlalchemy as sa
13 from alembic import op
14 from passlib.hash import argon2
15 
16 # raise the errors if we're not in production
17 raise_errors = os.environ.get("SECUREDROP_ENV", "prod") != "prod"
18 
19 try:
20     from models import ARGON2_PARAMS
21     from passphrases import PassphraseGenerator
22 except:  # noqa
23     if raise_errors:
24         raise
25 
26 
27 # revision identifiers, used by Alembic.
28 revision = "2e24fc7536e8"
29 down_revision = "de00920916bf"
30 branch_labels = None
31 depends_on = None
32 
33 
34 def generate_passphrase_hash() -> str:
35     passphrase = PassphraseGenerator.get_default().generate_passphrase()
36     return argon2.using(**ARGON2_PARAMS).hash(passphrase)
37 
38 
39 def create_deleted() -> int:
40     """manually insert a "deleted" journalist user.
41 
42     We need to do it this way since the model will reflect the current state of
43     the schema, not what it is at the current migration step
44 
45     It should be basically identical to what Journalist.get_deleted() does
46     """
47     op.execute(
48         sa.text(
49             """\
50         INSERT INTO journalists (uuid, username, session_nonce, passphrase_hash, otp_secret)
51         VALUES (:uuid, "deleted", 0, :passphrase_hash, :otp_secret);
52         """
53         ).bindparams(
54             uuid=str(uuid.uuid4()),
55             passphrase_hash=generate_passphrase_hash(),
56             otp_secret=pyotp.random_base32(),
57         )
58     )
59     # Get the autoincrement ID back
60     conn = op.get_bind()
61     result = conn.execute('SELECT id FROM journalists WHERE username="deleted";').fetchall()
62     return result[0][0]
63 
64 
65 def migrate_nulls() -> None:
66     """migrate existing journalist_id=NULL over to deleted or delete them"""
67     op.execute("DELETE FROM journalist_login_attempt WHERE journalist_id IS NULL;")
68     op.execute("DELETE FROM revoked_tokens WHERE journalist_id IS NULL;")
69     # Look to see if we have data to migrate
70     tables = ("replies", "seen_files", "seen_messages", "seen_replies")
71     needs_migration = []
72     conn = op.get_bind()
73     for table in tables:
74         result = conn.execute(  # nosec
75             f"SELECT 1 FROM {table} WHERE journalist_id IS NULL;"
76         ).first()
77         if result is not None:
78             needs_migration.append(table)
79 
80     if not needs_migration:
81         return
82 
83     deleted_id = create_deleted()
84     for table in needs_migration:
85         # The seen_ tables have UNIQUE(fk_id, journalist_id), so the deleted journalist can only have
86         # seen each item once. It is possible multiple NULL journalist have seen the same thing so we
87         # do this update in two passes.
88         # First we update as many rows to point to the deleted journalist as possible, ignoring any
89         # unique key violations.
90         op.execute(
91             sa.text(
92                 f"UPDATE OR IGNORE {table} SET journalist_id=:journalist_id WHERE journalist_id IS NULL;"
93             ).bindparams(journalist_id=deleted_id)
94         )
95         # Then we delete any leftovers which had been ignored earlier.
96         op.execute(f"DELETE FROM {table} WHERE journalist_id IS NULL")  # nosec
97 
98 
99 def upgrade() -> None:
100     migrate_nulls()
101 
102     with op.batch_alter_table("journalist_login_attempt", schema=None) as batch_op:
103         batch_op.alter_column("journalist_id", existing_type=sa.INTEGER(), nullable=False)
104 
105     with op.batch_alter_table("replies", schema=None) as batch_op:
106         batch_op.alter_column("journalist_id", existing_type=sa.INTEGER(), nullable=False)
107 
108     with op.batch_alter_table("revoked_tokens", schema=None) as batch_op:
109         batch_op.alter_column("journalist_id", existing_type=sa.INTEGER(), nullable=False)
110 
111     with op.batch_alter_table("seen_files", schema=None) as batch_op:
112         batch_op.alter_column("journalist_id", existing_type=sa.INTEGER(), nullable=False)
113 
114     with op.batch_alter_table("seen_messages", schema=None) as batch_op:
115         batch_op.alter_column("journalist_id", existing_type=sa.INTEGER(), nullable=False)
116 
117     with op.batch_alter_table("seen_replies", schema=None) as batch_op:
118         batch_op.alter_column("journalist_id", existing_type=sa.INTEGER(), nullable=False)
119 
120 
121 def downgrade() -> None:
122     # We do not un-migrate the data back to journalist_id=NULL
123 
124     with op.batch_alter_table("seen_replies", schema=None) as batch_op:
125         batch_op.alter_column("journalist_id", existing_type=sa.INTEGER(), nullable=True)
126 
127     with op.batch_alter_table("seen_messages", schema=None) as batch_op:
128         batch_op.alter_column("journalist_id", existing_type=sa.INTEGER(), nullable=True)
129 
130     with op.batch_alter_table("seen_files", schema=None) as batch_op:
131         batch_op.alter_column("journalist_id", existing_type=sa.INTEGER(), nullable=True)
132 
133     with op.batch_alter_table("revoked_tokens", schema=None) as batch_op:
134         batch_op.alter_column("journalist_id", existing_type=sa.INTEGER(), nullable=True)
135 
136     with op.batch_alter_table("replies", schema=None) as batch_op:
137         batch_op.alter_column("journalist_id", existing_type=sa.INTEGER(), nullable=True)
138 
139     with op.batch_alter_table("journalist_login_attempt", schema=None) as batch_op:
140         batch_op.alter_column("journalist_id", existing_type=sa.INTEGER(), nullable=True)
141
```
--- 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/securedrop/alembic/versions/2e24fc7536e8_make_journalist_id_non_nullable.py b/securedrop/alembic/versions/2e24fc7536e8_make_journalist_id_non_nullable.py
--- a/securedrop/alembic/versions/2e24fc7536e8_make_journalist_id_non_nullable.py
+++ b/securedrop/alembic/versions/2e24fc7536e8_make_journalist_id_non_nullable.py
@@ -82,14 +82,15 @@
 
     deleted_id = create_deleted()
     for table in needs_migration:
-        # The seen_ tables have UNIQUE(fk_id, journalist_id), so the deleted journalist can only have
-        # seen each item once. It is possible multiple NULL journalist have seen the same thing so we
-        # do this update in two passes.
+        # The seen_ tables have UNIQUE(fk_id, journalist_id), so the deleted journalist can only
+        # have seen each item once. It is possible multiple NULL journalist have seen the same thing
+        # so we do this update in two passes.
         # First we update as many rows to point to the deleted journalist as possible, ignoring any
         # unique key violations.
         op.execute(
             sa.text(
-                f"UPDATE OR IGNORE {table} SET journalist_id=:journalist_id WHERE journalist_id IS NULL;"
+                f"UPDATE OR IGNORE {table} SET journalist_id=:journalist_id "
+                "WHERE journalist_id IS NULL;"
             ).bindparams(journalist_id=deleted_id)
         )
         # Then we delete any leftovers which had been ignored earlier.
diff --git a/securedrop/alembic/versions/b7f98cfd6a70_make_filesystem_id_non_nullable.py b/securedrop/alembic/versions/b7f98cfd6a70_make_filesystem_id_non_nullable.py
--- a/securedrop/alembic/versions/b7f98cfd6a70_make_filesystem_id_non_nullable.py
+++ b/securedrop/alembic/versions/b7f98cfd6a70_make_filesystem_id_non_nullable.py
@@ -19,8 +19,8 @@
     # Not having a filesystem_id makes the source useless, so if any of those do exist, we'll
     # delete them first, as part of this migration.
     # Because we can't rely on SQLAlchemy's cascade deletion, we have to do it manually.
-    # First we delete out of replies/seen_files/seen_messages (things that refer to things that refer
-    # to sources)
+    # First we delete out of replies/seen_files/seen_messages (things that refer to things
+    # (source_stars/submissions/replies) that refer to sources)
     op.execute(
         "DELETE FROM seen_replies WHERE reply_id IN ("
         "SELECT replies.id FROM replies "
diff --git a/securedrop/alembic/versions/c5a02eb52f2d_dropped_session_nonce_from_journalist_.py b/securedrop/alembic/versions/c5a02eb52f2d_dropped_session_nonce_from_journalist_.py
--- a/securedrop/alembic/versions/c5a02eb52f2d_dropped_session_nonce_from_journalist_.py
+++ b/securedrop/alembic/versions/c5a02eb52f2d_dropped_session_nonce_from_journalist_.py
@@ -1,4 +1,5 @@
-"""dropped session_nonce from journalist table and revoked tokens table due to new session implementation
+"""dropped session_nonce from journalist table and revoked tokens table
+   due to new session implementation
 
 Revision ID: c5a02eb52f2d
 Revises: b7f98cfd6a70
@@ -27,7 +28,8 @@
 def downgrade() -> None:
     """This would have been the easy way, however previous does not have
     default value and thus up/down assertion fails"""
-    # op.add_column('journalists', sa.Column('session_nonce', sa.Integer(), nullable=False, server_default='0'))
+    # op.add_column('journalists', sa.Column('session_nonce', sa.Integer(),
+    #               nullable=False, server_default='0'))
 
     conn = op.get_bind()
     conn.execute("PRAGMA legacy_alter_table=ON")

{"golden_diff": "diff --git a/securedrop/alembic/versions/2e24fc7536e8_make_journalist_id_non_nullable.py b/securedrop/alembic/versions/2e24fc7536e8_make_journalist_id_non_nullable.py\n--- a/securedrop/alembic/versions/2e24fc7536e8_make_journalist_id_non_nullable.py\n+++ b/securedrop/alembic/versions/2e24fc7536e8_make_journalist_id_non_nullable.py\n@@ -82,14 +82,15 @@\n \n     deleted_id = create_deleted()\n     for table in needs_migration:\n-        # The seen_ tables have UNIQUE(fk_id, journalist_id), so the deleted journalist can only have\n-        # seen each item once. It is possible multiple NULL journalist have seen the same thing so we\n-        # do this update in two passes.\n+        # The seen_ tables have UNIQUE(fk_id, journalist_id), so the deleted journalist can only\n+        # have seen each item once. It is possible multiple NULL journalist have seen the same thing\n+        # so we do this update in two passes.\n         # First we update as many rows to point to the deleted journalist as possible, ignoring any\n         # unique key violations.\n         op.execute(\n             sa.text(\n-                f\"UPDATE OR IGNORE {table} SET journalist_id=:journalist_id WHERE journalist_id IS NULL;\"\n+                f\"UPDATE OR IGNORE {table} SET journalist_id=:journalist_id \"\n+                \"WHERE journalist_id IS NULL;\"\n             ).bindparams(journalist_id=deleted_id)\n         )\n         # Then we delete any leftovers which had been ignored earlier.\ndiff --git a/securedrop/alembic/versions/b7f98cfd6a70_make_filesystem_id_non_nullable.py b/securedrop/alembic/versions/b7f98cfd6a70_make_filesystem_id_non_nullable.py\n--- a/securedrop/alembic/versions/b7f98cfd6a70_make_filesystem_id_non_nullable.py\n+++ b/securedrop/alembic/versions/b7f98cfd6a70_make_filesystem_id_non_nullable.py\n@@ -19,8 +19,8 @@\n     # Not having a filesystem_id makes the source useless, so if any of those do exist, we'll\n     # delete them first, as part of this migration.\n     # Because we can't rely on SQLAlchemy's cascade deletion, we have to do it manually.\n-    # First we delete out of replies/seen_files/seen_messages (things that refer to things that refer\n-    # to sources)\n+    # First we delete out of replies/seen_files/seen_messages (things that refer to things\n+    # (source_stars/submissions/replies) that refer to sources)\n     op.execute(\n         \"DELETE FROM seen_replies WHERE reply_id IN (\"\n         \"SELECT replies.id FROM replies \"\ndiff --git a/securedrop/alembic/versions/c5a02eb52f2d_dropped_session_nonce_from_journalist_.py b/securedrop/alembic/versions/c5a02eb52f2d_dropped_session_nonce_from_journalist_.py\n--- a/securedrop/alembic/versions/c5a02eb52f2d_dropped_session_nonce_from_journalist_.py\n+++ b/securedrop/alembic/versions/c5a02eb52f2d_dropped_session_nonce_from_journalist_.py\n@@ -1,4 +1,5 @@\n-\"\"\"dropped session_nonce from journalist table and revoked tokens table due to new session implementation\n+\"\"\"dropped session_nonce from journalist table and revoked tokens table\n+   due to new session implementation\n \n Revision ID: c5a02eb52f2d\n Revises: b7f98cfd6a70\n@@ -27,7 +28,8 @@\n def downgrade() -> None:\n     \"\"\"This would have been the easy way, however previous does not have\n     default value and thus up/down assertion fails\"\"\"\n-    # op.add_column('journalists', sa.Column('session_nonce', sa.Integer(), nullable=False, server_default='0'))\n+    # op.add_column('journalists', sa.Column('session_nonce', sa.Integer(),\n+    #               nullable=False, server_default='0'))\n \n     conn = op.get_bind()\n     conn.execute(\"PRAGMA legacy_alter_table=ON\")\n", "issue": "Run flake8 against alembic migrations\n*This is a good first issue for new contributors to take on, if you have any questions, please ask on the task or in our [Gitter room](https://gitter.im/freedomofpress/securedrop)!*\r\n\r\n## Description\r\n\r\nIn `.flake8` we currently exclude `securedrop/alembic/versions`. We should be running flake8 against those files by removing the exclusion and fixing any new flake8 errors.\n", "before_files": [{"content": "\"\"\"dropped session_nonce from journalist table and revoked tokens table due to new session implementation\n\nRevision ID: c5a02eb52f2d\nRevises: b7f98cfd6a70\nCreate Date: 2022-04-16 21:25:22.398189\n\n\"\"\"\nimport sqlalchemy as sa\nfrom alembic import op\n\n# revision identifiers, used by Alembic.\nrevision = \"c5a02eb52f2d\"\ndown_revision = \"b7f98cfd6a70\"\nbranch_labels = None\ndepends_on = None\n\n\ndef upgrade() -> None:\n    # ### commands auto generated by Alembic - please adjust! ###\n    op.drop_table(\"revoked_tokens\")\n    with op.batch_alter_table(\"journalists\", schema=None) as batch_op:\n        batch_op.drop_column(\"session_nonce\")\n\n    # ### end Alembic commands ###\n\n\ndef downgrade() -> None:\n    \"\"\"This would have been the easy way, however previous does not have\n    default value and thus up/down assertion fails\"\"\"\n    # op.add_column('journalists', sa.Column('session_nonce', sa.Integer(), nullable=False, server_default='0'))\n\n    conn = op.get_bind()\n    conn.execute(\"PRAGMA legacy_alter_table=ON\")\n    # Save existing journalist table.\n    op.rename_table(\"journalists\", \"journalists_tmp\")\n\n    # Add nonce column.\n    op.add_column(\"journalists_tmp\", sa.Column(\"session_nonce\", sa.Integer()))\n\n    # Populate nonce column.\n    journalists = conn.execute(sa.text(\"SELECT * FROM journalists_tmp\")).fetchall()\n\n    for journalist in journalists:\n        conn.execute(\n            sa.text(\n                \"\"\"UPDATE journalists_tmp SET session_nonce=0 WHERE\n                       id=:id\"\"\"\n            ).bindparams(id=journalist.id)\n        )\n\n    # Now create new table with null constraint applied.\n    op.create_table(\n        \"journalists\",\n        sa.Column(\"id\", sa.Integer(), nullable=False),\n        sa.Column(\"uuid\", sa.String(length=36), nullable=False),\n        sa.Column(\"username\", sa.String(length=255), nullable=False),\n        sa.Column(\"first_name\", sa.String(length=255), nullable=True),\n        sa.Column(\"last_name\", sa.String(length=255), nullable=True),\n        sa.Column(\"pw_salt\", sa.Binary(), nullable=True),\n        sa.Column(\"pw_hash\", sa.Binary(), nullable=True),\n        sa.Column(\"passphrase_hash\", sa.String(length=256), nullable=True),\n        sa.Column(\"is_admin\", sa.Boolean(), nullable=True),\n        sa.Column(\"session_nonce\", sa.Integer(), nullable=False),\n        sa.Column(\"otp_secret\", sa.String(length=32), nullable=True),\n        sa.Column(\"is_totp\", sa.Boolean(), nullable=True),\n        sa.Column(\"hotp_counter\", sa.Integer(), nullable=True),\n        sa.Column(\"last_token\", sa.String(length=6), nullable=True),\n        sa.Column(\"created_on\", sa.DateTime(), nullable=True),\n        sa.Column(\"last_access\", sa.DateTime(), nullable=True),\n        sa.PrimaryKeyConstraint(\"id\"),\n        sa.UniqueConstraint(\"username\"),\n        sa.UniqueConstraint(\"uuid\"),\n    )\n\n    conn.execute(\n        \"\"\"\n        INSERT INTO journalists\n        SELECT id, uuid, username, first_name, last_name, pw_salt, pw_hash,\n               passphrase_hash, is_admin, session_nonce, otp_secret, is_totp,\n               hotp_counter, last_token, created_on, last_access\n        FROM journalists_tmp\n    \"\"\"\n    )\n\n    # Now delete the old table.\n    op.drop_table(\"journalists_tmp\")\n\n    op.create_table(\n        \"revoked_tokens\",\n        sa.Column(\"id\", sa.INTEGER(), nullable=False),\n        sa.Column(\"journalist_id\", sa.INTEGER(), nullable=False),\n        sa.Column(\"token\", sa.TEXT(), nullable=False),\n        sa.ForeignKeyConstraint(\n            [\"journalist_id\"],\n            [\"journalists.id\"],\n        ),\n        sa.PrimaryKeyConstraint(\"id\"),\n        sa.UniqueConstraint(\"token\"),\n    )\n", "path": "securedrop/alembic/versions/c5a02eb52f2d_dropped_session_nonce_from_journalist_.py"}, {"content": "\"\"\"make_filesystem_id_non_nullable\n\nRevision ID: b7f98cfd6a70\nRevises: d9d36b6f4d1e\nCreate Date: 2022-03-18 18:10:27.842201\n\n\"\"\"\nimport sqlalchemy as sa\nfrom alembic import op\n\n# revision identifiers, used by Alembic.\nrevision = \"b7f98cfd6a70\"\ndown_revision = \"d9d36b6f4d1e\"\nbranch_labels = None\ndepends_on = None\n\n\ndef upgrade() -> None:\n    # Not having a filesystem_id makes the source useless, so if any of those do exist, we'll\n    # delete them first, as part of this migration.\n    # Because we can't rely on SQLAlchemy's cascade deletion, we have to do it manually.\n    # First we delete out of replies/seen_files/seen_messages (things that refer to things that refer\n    # to sources)\n    op.execute(\n        \"DELETE FROM seen_replies WHERE reply_id IN (\"\n        \"SELECT replies.id FROM replies \"\n        \"JOIN sources ON sources.id=replies.source_id \"\n        \"WHERE filesystem_id IS NULL)\"\n    )\n    op.execute(\n        \"DELETE FROM seen_files WHERE file_id IN (\"\n        \"SELECT submissions.id FROM submissions \"\n        \"JOIN sources ON sources.id=submissions.source_id \"\n        \"WHERE filesystem_id IS NULL)\"\n    )\n    op.execute(\n        \"DELETE FROM seen_messages WHERE message_id IN (\"\n        \"SELECT submissions.id FROM submissions \"\n        \"JOIN sources ON sources.id=submissions.source_id \"\n        \"WHERE filesystem_id IS NULL)\"\n    )\n    # Now things that directly refer to sources\n    for table in (\"source_stars\", \"submissions\", \"replies\"):\n        op.execute(\n            f\"DELETE FROM {table} WHERE source_id IN \"  # nosec\n            f\"(SELECT id FROM sources WHERE filesystem_id IS NULL)\"\n        )  # nosec\n    # And now the sources\n    op.execute(\"DELETE FROM sources WHERE filesystem_id IS NULL\")\n    with op.batch_alter_table(\"sources\", schema=None) as batch_op:\n        batch_op.alter_column(\"filesystem_id\", existing_type=sa.VARCHAR(length=96), nullable=False)\n\n\ndef downgrade() -> None:\n    with op.batch_alter_table(\"sources\", schema=None) as batch_op:\n        batch_op.alter_column(\"filesystem_id\", existing_type=sa.VARCHAR(length=96), nullable=True)\n", "path": "securedrop/alembic/versions/b7f98cfd6a70_make_filesystem_id_non_nullable.py"}, {"content": "\"\"\"make journalist_id non-nullable\n\nRevision ID: 2e24fc7536e8\nRevises: de00920916bf\nCreate Date: 2022-01-12 19:31:06.186285\n\n\"\"\"\nimport os\nimport uuid\n\nimport pyotp\nimport sqlalchemy as sa\nfrom alembic import op\nfrom passlib.hash import argon2\n\n# raise the errors if we're not in production\nraise_errors = os.environ.get(\"SECUREDROP_ENV\", \"prod\") != \"prod\"\n\ntry:\n    from models import ARGON2_PARAMS\n    from passphrases import PassphraseGenerator\nexcept:  # noqa\n    if raise_errors:\n        raise\n\n\n# revision identifiers, used by Alembic.\nrevision = \"2e24fc7536e8\"\ndown_revision = \"de00920916bf\"\nbranch_labels = None\ndepends_on = None\n\n\ndef generate_passphrase_hash() -> str:\n    passphrase = PassphraseGenerator.get_default().generate_passphrase()\n    return argon2.using(**ARGON2_PARAMS).hash(passphrase)\n\n\ndef create_deleted() -> int:\n    \"\"\"manually insert a \"deleted\" journalist user.\n\n    We need to do it this way since the model will reflect the current state of\n    the schema, not what it is at the current migration step\n\n    It should be basically identical to what Journalist.get_deleted() does\n    \"\"\"\n    op.execute(\n        sa.text(\n            \"\"\"\\\n        INSERT INTO journalists (uuid, username, session_nonce, passphrase_hash, otp_secret)\n        VALUES (:uuid, \"deleted\", 0, :passphrase_hash, :otp_secret);\n        \"\"\"\n        ).bindparams(\n            uuid=str(uuid.uuid4()),\n            passphrase_hash=generate_passphrase_hash(),\n            otp_secret=pyotp.random_base32(),\n        )\n    )\n    # Get the autoincrement ID back\n    conn = op.get_bind()\n    result = conn.execute('SELECT id FROM journalists WHERE username=\"deleted\";').fetchall()\n    return result[0][0]\n\n\ndef migrate_nulls() -> None:\n    \"\"\"migrate existing journalist_id=NULL over to deleted or delete them\"\"\"\n    op.execute(\"DELETE FROM journalist_login_attempt WHERE journalist_id IS NULL;\")\n    op.execute(\"DELETE FROM revoked_tokens WHERE journalist_id IS NULL;\")\n    # Look to see if we have data to migrate\n    tables = (\"replies\", \"seen_files\", \"seen_messages\", \"seen_replies\")\n    needs_migration = []\n    conn = op.get_bind()\n    for table in tables:\n        result = conn.execute(  # nosec\n            f\"SELECT 1 FROM {table} WHERE journalist_id IS NULL;\"\n        ).first()\n        if result is not None:\n            needs_migration.append(table)\n\n    if not needs_migration:\n        return\n\n    deleted_id = create_deleted()\n    for table in needs_migration:\n        # The seen_ tables have UNIQUE(fk_id, journalist_id), so the deleted journalist can only have\n        # seen each item once. It is possible multiple NULL journalist have seen the same thing so we\n        # do this update in two passes.\n        # First we update as many rows to point to the deleted journalist as possible, ignoring any\n        # unique key violations.\n        op.execute(\n            sa.text(\n                f\"UPDATE OR IGNORE {table} SET journalist_id=:journalist_id WHERE journalist_id IS NULL;\"\n            ).bindparams(journalist_id=deleted_id)\n        )\n        # Then we delete any leftovers which had been ignored earlier.\n        op.execute(f\"DELETE FROM {table} WHERE journalist_id IS NULL\")  # nosec\n\n\ndef upgrade() -> None:\n    migrate_nulls()\n\n    with op.batch_alter_table(\"journalist_login_attempt\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=False)\n\n    with op.batch_alter_table(\"replies\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=False)\n\n    with op.batch_alter_table(\"revoked_tokens\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=False)\n\n    with op.batch_alter_table(\"seen_files\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=False)\n\n    with op.batch_alter_table(\"seen_messages\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=False)\n\n    with op.batch_alter_table(\"seen_replies\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=False)\n\n\ndef downgrade() -> None:\n    # We do not un-migrate the data back to journalist_id=NULL\n\n    with op.batch_alter_table(\"seen_replies\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=True)\n\n    with op.batch_alter_table(\"seen_messages\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=True)\n\n    with op.batch_alter_table(\"seen_files\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=True)\n\n    with op.batch_alter_table(\"revoked_tokens\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=True)\n\n    with op.batch_alter_table(\"replies\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=True)\n\n    with op.batch_alter_table(\"journalist_login_attempt\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=True)\n", "path": "securedrop/alembic/versions/2e24fc7536e8_make_journalist_id_non_nullable.py"}], "after_files": [{"content": "\"\"\"dropped session_nonce from journalist table and revoked tokens table\n   due to new session implementation\n\nRevision ID: c5a02eb52f2d\nRevises: b7f98cfd6a70\nCreate Date: 2022-04-16 21:25:22.398189\n\n\"\"\"\nimport sqlalchemy as sa\nfrom alembic import op\n\n# revision identifiers, used by Alembic.\nrevision = \"c5a02eb52f2d\"\ndown_revision = \"b7f98cfd6a70\"\nbranch_labels = None\ndepends_on = None\n\n\ndef upgrade() -> None:\n    # ### commands auto generated by Alembic - please adjust! ###\n    op.drop_table(\"revoked_tokens\")\n    with op.batch_alter_table(\"journalists\", schema=None) as batch_op:\n        batch_op.drop_column(\"session_nonce\")\n\n    # ### end Alembic commands ###\n\n\ndef downgrade() -> None:\n    \"\"\"This would have been the easy way, however previous does not have\n    default value and thus up/down assertion fails\"\"\"\n    # op.add_column('journalists', sa.Column('session_nonce', sa.Integer(),\n    #               nullable=False, server_default='0'))\n\n    conn = op.get_bind()\n    conn.execute(\"PRAGMA legacy_alter_table=ON\")\n    # Save existing journalist table.\n    op.rename_table(\"journalists\", \"journalists_tmp\")\n\n    # Add nonce column.\n    op.add_column(\"journalists_tmp\", sa.Column(\"session_nonce\", sa.Integer()))\n\n    # Populate nonce column.\n    journalists = conn.execute(sa.text(\"SELECT * FROM journalists_tmp\")).fetchall()\n\n    for journalist in journalists:\n        conn.execute(\n            sa.text(\n                \"\"\"UPDATE journalists_tmp SET session_nonce=0 WHERE\n                       id=:id\"\"\"\n            ).bindparams(id=journalist.id)\n        )\n\n    # Now create new table with null constraint applied.\n    op.create_table(\n        \"journalists\",\n        sa.Column(\"id\", sa.Integer(), nullable=False),\n        sa.Column(\"uuid\", sa.String(length=36), nullable=False),\n        sa.Column(\"username\", sa.String(length=255), nullable=False),\n        sa.Column(\"first_name\", sa.String(length=255), nullable=True),\n        sa.Column(\"last_name\", sa.String(length=255), nullable=True),\n        sa.Column(\"pw_salt\", sa.Binary(), nullable=True),\n        sa.Column(\"pw_hash\", sa.Binary(), nullable=True),\n        sa.Column(\"passphrase_hash\", sa.String(length=256), nullable=True),\n        sa.Column(\"is_admin\", sa.Boolean(), nullable=True),\n        sa.Column(\"session_nonce\", sa.Integer(), nullable=False),\n        sa.Column(\"otp_secret\", sa.String(length=32), nullable=True),\n        sa.Column(\"is_totp\", sa.Boolean(), nullable=True),\n        sa.Column(\"hotp_counter\", sa.Integer(), nullable=True),\n        sa.Column(\"last_token\", sa.String(length=6), nullable=True),\n        sa.Column(\"created_on\", sa.DateTime(), nullable=True),\n        sa.Column(\"last_access\", sa.DateTime(), nullable=True),\n        sa.PrimaryKeyConstraint(\"id\"),\n        sa.UniqueConstraint(\"username\"),\n        sa.UniqueConstraint(\"uuid\"),\n    )\n\n    conn.execute(\n        \"\"\"\n        INSERT INTO journalists\n        SELECT id, uuid, username, first_name, last_name, pw_salt, pw_hash,\n               passphrase_hash, is_admin, session_nonce, otp_secret, is_totp,\n               hotp_counter, last_token, created_on, last_access\n        FROM journalists_tmp\n    \"\"\"\n    )\n\n    # Now delete the old table.\n    op.drop_table(\"journalists_tmp\")\n\n    op.create_table(\n        \"revoked_tokens\",\n        sa.Column(\"id\", sa.INTEGER(), nullable=False),\n        sa.Column(\"journalist_id\", sa.INTEGER(), nullable=False),\n        sa.Column(\"token\", sa.TEXT(), nullable=False),\n        sa.ForeignKeyConstraint(\n            [\"journalist_id\"],\n            [\"journalists.id\"],\n        ),\n        sa.PrimaryKeyConstraint(\"id\"),\n        sa.UniqueConstraint(\"token\"),\n    )\n", "path": "securedrop/alembic/versions/c5a02eb52f2d_dropped_session_nonce_from_journalist_.py"}, {"content": "\"\"\"make_filesystem_id_non_nullable\n\nRevision ID: b7f98cfd6a70\nRevises: d9d36b6f4d1e\nCreate Date: 2022-03-18 18:10:27.842201\n\n\"\"\"\nimport sqlalchemy as sa\nfrom alembic import op\n\n# revision identifiers, used by Alembic.\nrevision = \"b7f98cfd6a70\"\ndown_revision = \"d9d36b6f4d1e\"\nbranch_labels = None\ndepends_on = None\n\n\ndef upgrade() -> None:\n    # Not having a filesystem_id makes the source useless, so if any of those do exist, we'll\n    # delete them first, as part of this migration.\n    # Because we can't rely on SQLAlchemy's cascade deletion, we have to do it manually.\n    # First we delete out of replies/seen_files/seen_messages (things that refer to things\n    # (source_stars/submissions/replies) that refer to sources)\n    op.execute(\n        \"DELETE FROM seen_replies WHERE reply_id IN (\"\n        \"SELECT replies.id FROM replies \"\n        \"JOIN sources ON sources.id=replies.source_id \"\n        \"WHERE filesystem_id IS NULL)\"\n    )\n    op.execute(\n        \"DELETE FROM seen_files WHERE file_id IN (\"\n        \"SELECT submissions.id FROM submissions \"\n        \"JOIN sources ON sources.id=submissions.source_id \"\n        \"WHERE filesystem_id IS NULL)\"\n    )\n    op.execute(\n        \"DELETE FROM seen_messages WHERE message_id IN (\"\n        \"SELECT submissions.id FROM submissions \"\n        \"JOIN sources ON sources.id=submissions.source_id \"\n        \"WHERE filesystem_id IS NULL)\"\n    )\n    # Now things that directly refer to sources\n    for table in (\"source_stars\", \"submissions\", \"replies\"):\n        op.execute(\n            f\"DELETE FROM {table} WHERE source_id IN \"  # nosec\n            f\"(SELECT id FROM sources WHERE filesystem_id IS NULL)\"\n        )  # nosec\n    # And now the sources\n    op.execute(\"DELETE FROM sources WHERE filesystem_id IS NULL\")\n    with op.batch_alter_table(\"sources\", schema=None) as batch_op:\n        batch_op.alter_column(\"filesystem_id\", existing_type=sa.VARCHAR(length=96), nullable=False)\n\n\ndef downgrade() -> None:\n    with op.batch_alter_table(\"sources\", schema=None) as batch_op:\n        batch_op.alter_column(\"filesystem_id\", existing_type=sa.VARCHAR(length=96), nullable=True)\n", "path": "securedrop/alembic/versions/b7f98cfd6a70_make_filesystem_id_non_nullable.py"}, {"content": "\"\"\"make journalist_id non-nullable\n\nRevision ID: 2e24fc7536e8\nRevises: de00920916bf\nCreate Date: 2022-01-12 19:31:06.186285\n\n\"\"\"\nimport os\nimport uuid\n\nimport pyotp\nimport sqlalchemy as sa\nfrom alembic import op\nfrom passlib.hash import argon2\n\n# raise the errors if we're not in production\nraise_errors = os.environ.get(\"SECUREDROP_ENV\", \"prod\") != \"prod\"\n\ntry:\n    from models import ARGON2_PARAMS\n    from passphrases import PassphraseGenerator\nexcept:  # noqa\n    if raise_errors:\n        raise\n\n\n# revision identifiers, used by Alembic.\nrevision = \"2e24fc7536e8\"\ndown_revision = \"de00920916bf\"\nbranch_labels = None\ndepends_on = None\n\n\ndef generate_passphrase_hash() -> str:\n    passphrase = PassphraseGenerator.get_default().generate_passphrase()\n    return argon2.using(**ARGON2_PARAMS).hash(passphrase)\n\n\ndef create_deleted() -> int:\n    \"\"\"manually insert a \"deleted\" journalist user.\n\n    We need to do it this way since the model will reflect the current state of\n    the schema, not what it is at the current migration step\n\n    It should be basically identical to what Journalist.get_deleted() does\n    \"\"\"\n    op.execute(\n        sa.text(\n            \"\"\"\\\n        INSERT INTO journalists (uuid, username, session_nonce, passphrase_hash, otp_secret)\n        VALUES (:uuid, \"deleted\", 0, :passphrase_hash, :otp_secret);\n        \"\"\"\n        ).bindparams(\n            uuid=str(uuid.uuid4()),\n            passphrase_hash=generate_passphrase_hash(),\n            otp_secret=pyotp.random_base32(),\n        )\n    )\n    # Get the autoincrement ID back\n    conn = op.get_bind()\n    result = conn.execute('SELECT id FROM journalists WHERE username=\"deleted\";').fetchall()\n    return result[0][0]\n\n\ndef migrate_nulls() -> None:\n    \"\"\"migrate existing journalist_id=NULL over to deleted or delete them\"\"\"\n    op.execute(\"DELETE FROM journalist_login_attempt WHERE journalist_id IS NULL;\")\n    op.execute(\"DELETE FROM revoked_tokens WHERE journalist_id IS NULL;\")\n    # Look to see if we have data to migrate\n    tables = (\"replies\", \"seen_files\", \"seen_messages\", \"seen_replies\")\n    needs_migration = []\n    conn = op.get_bind()\n    for table in tables:\n        result = conn.execute(  # nosec\n            f\"SELECT 1 FROM {table} WHERE journalist_id IS NULL;\"\n        ).first()\n        if result is not None:\n            needs_migration.append(table)\n\n    if not needs_migration:\n        return\n\n    deleted_id = create_deleted()\n    for table in needs_migration:\n        # The seen_ tables have UNIQUE(fk_id, journalist_id), so the deleted journalist can only\n        # have seen each item once. It is possible multiple NULL journalist have seen the same thing\n        # so we do this update in two passes.\n        # First we update as many rows to point to the deleted journalist as possible, ignoring any\n        # unique key violations.\n        op.execute(\n            sa.text(\n                f\"UPDATE OR IGNORE {table} SET journalist_id=:journalist_id \"\n                \"WHERE journalist_id IS NULL;\"\n            ).bindparams(journalist_id=deleted_id)\n        )\n        # Then we delete any leftovers which had been ignored earlier.\n        op.execute(f\"DELETE FROM {table} WHERE journalist_id IS NULL\")  # nosec\n\n\ndef upgrade() -> None:\n    migrate_nulls()\n\n    with op.batch_alter_table(\"journalist_login_attempt\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=False)\n\n    with op.batch_alter_table(\"replies\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=False)\n\n    with op.batch_alter_table(\"revoked_tokens\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=False)\n\n    with op.batch_alter_table(\"seen_files\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=False)\n\n    with op.batch_alter_table(\"seen_messages\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=False)\n\n    with op.batch_alter_table(\"seen_replies\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=False)\n\n\ndef downgrade() -> None:\n    # We do not un-migrate the data back to journalist_id=NULL\n\n    with op.batch_alter_table(\"seen_replies\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=True)\n\n    with op.batch_alter_table(\"seen_messages\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=True)\n\n    with op.batch_alter_table(\"seen_files\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=True)\n\n    with op.batch_alter_table(\"revoked_tokens\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=True)\n\n    with op.batch_alter_table(\"replies\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=True)\n\n    with op.batch_alter_table(\"journalist_login_attempt\", schema=None) as batch_op:\n        batch_op.alter_column(\"journalist_id\", existing_type=sa.INTEGER(), nullable=True)\n", "path": "securedrop/alembic/versions/2e24fc7536e8_make_journalist_id_non_nullable.py"}]}