<filename>data/migrations/versions/d17c695859ea_delete_old_appr_tables.py<gh_stars>1000+
"""
Delete old Appr tables.

Revision ID: d17c695859ea
Revises: 5<PASSWORD>
Create Date: 2018-07-16 15:21:11.593040
"""

# revision identifiers, used by Alembic.
revision = "d17c695859ea"
down_revision = "5d463ea1e8a8"

import sqlalchemy as sa
from sqlalchemy.sql import table, column
from util.migrate import UTF8LongText, UTF8CharField


def upgrade(op, tables, tester):
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table("tag")
    op.drop_table("manifestlistmanifest")
    op.drop_table("manifestlist")
    op.drop_table("manifestblob")
    op.drop_table("manifest")
    op.drop_table("blobplacement")
    op.drop_table("blob")
    op.drop_table("blobplacementlocation")
    op.drop_table("tagkind")
    # ### end Alembic commands ###


def downgrade(op, tables, tester):
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "tagkind",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_tagkind")),
    )
    op.create_index("tagkind_name", "tagkind", ["name"], unique=True)

    op.create_table(
        "blobplacementlocation",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_blobplacementlocation")),
    )
    op.create_index("blobplacementlocation_name", "blobplacementlocation", ["name"], unique=True)

    op.create_table(
        "blob",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("digest", sa.String(length=255), nullable=False),
        sa.Column("media_type_id", sa.Integer(), nullable=False),
        sa.Column("size", sa.BigInteger(), nullable=False),
        sa.Column("uncompressed_size", sa.BigInteger(), nullable=True),
        sa.ForeignKeyConstraint(
            ["media_type_id"], ["mediatype.id"], name=op.f("fk_blob_media_type_id_mediatype")
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_blob")),
    )
    op.create_index("blob_digest", "blob", ["digest"], unique=True)
    op.create_index("blob_media_type_id", "blob", ["media_type_id"], unique=False)

    op.create_table(
        "manifest",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("digest", sa.String(length=255), nullable=False),
        sa.Column("media_type_id", sa.Integer(), nullable=False),
        sa.Column("manifest_json", UTF8LongText, nullable=False),
        sa.ForeignKeyConstraint(
            ["media_type_id"], ["mediatype.id"], name=op.f("fk_manifest_media_type_id_mediatype")
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_manifest")),
    )
    op.create_index("manifest_digest", "manifest", ["digest"], unique=True)
    op.create_index("manifest_media_type_id", "manifest", ["media_type_id"], unique=False)

    op.create_table(
        "manifestlist",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("digest", sa.String(length=255), nullable=False),
        sa.Column("manifest_list_json", UTF8LongText, nullable=False),
        sa.Column("schema_version", UTF8CharField(length=255), nullable=False),
        sa.Column("media_type_id", sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ["media_type_id"],
            ["mediatype.id"],
            name=op.f("fk_manifestlist_media_type_id_mediatype"),
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_manifestlist")),
    )
    op.create_index("manifestlist_digest", "manifestlist", ["digest"], unique=True)
    op.create_index("manifestlist_media_type_id", "manifestlist", ["media_type_id"], unique=False)

    op.create_table(
        "blobplacement",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("blob_id", sa.Integer(), nullable=False),
        sa.Column("location_id", sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ["blob_id"], ["blob.id"], name=op.f("fk_blobplacement_blob_id_blob")
        ),
        sa.ForeignKeyConstraint(
            ["location_id"],
            ["blobplacementlocation.id"],
            name=op.f("fk_blobplacement_location_id_blobplacementlocation"),
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_blobplacement")),
    )
    op.create_index("blobplacement_blob_id", "blobplacement", ["blob_id"], unique=False)
    op.create_index(
        "blobplacement_blob_id_location_id",
        "blobplacement",
        ["blob_id", "location_id"],
        unique=True,
    )
    op.create_index("blobplacement_location_id", "blobplacement", ["location_id"], unique=False)

    op.create_table(
        "manifestblob",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("manifest_id", sa.Integer(), nullable=False),
        sa.Column("blob_id", sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ["blob_id"], ["blob.id"], name=op.f("fk_manifestblob_blob_id_blob")
        ),
        sa.ForeignKeyConstraint(
            ["manifest_id"], ["manifest.id"], name=op.f("fk_manifestblob_manifest_id_manifest")
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_manifestblob")),
    )
    op.create_index("manifestblob_blob_id", "manifestblob", ["blob_id"], unique=False)
    op.create_index("manifestblob_manifest_id", "manifestblob", ["manifest_id"], unique=False)
    op.create_index(
        "manifestblob_manifest_id_blob_id", "manifestblob", ["manifest_id", "blob_id"], unique=True
    )

    op.create_table(
        "manifestlistmanifest",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("manifest_list_id", sa.Integer(), nullable=False),
        sa.Column("manifest_id", sa.Integer(), nullable=False),
        sa.Column("operating_system", UTF8CharField(length=255), nullable=True),
        sa.Column("architecture", UTF8CharField(length=255), nullable=True),
        sa.Column("platform_json", UTF8LongText, nullable=True),
        sa.Column("media_type_id", sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ["manifest_id"],
            ["manifest.id"],
            name=op.f("fk_manifestlistmanifest_manifest_id_manifest"),
        ),
        sa.ForeignKeyConstraint(
            ["manifest_list_id"],
            ["manifestlist.id"],
            name=op.f("fk_manifestlistmanifest_manifest_list_id_manifestlist"),
        ),
        sa.ForeignKeyConstraint(
            ["media_type_id"],
            ["mediatype.id"],
            name=op.f("fk_manifestlistmanifest_media_type_id_mediatype"),
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_manifestlistmanifest")),
    )
    op.create_index(
        "manifestlistmanifest_manifest_id", "manifestlistmanifest", ["manifest_id"], unique=False
    )
    op.create_index(
        "manifestlistmanifest_manifest_list_id",
        "manifestlistmanifest",
        ["manifest_list_id"],
        unique=False,
    )
    op.create_index(
        "manifestlistmanifest_manifest_listid_os_arch_mtid",
        "manifestlistmanifest",
        ["manifest_list_id", "operating_system", "architecture", "media_type_id"],
        unique=False,
    )
    op.create_index(
        "manifestlistmanifest_manifest_listid_mtid",
        "manifestlistmanifest",
        ["manifest_list_id", "media_type_id"],
        unique=False,
    )
    op.create_index(
        "manifestlistmanifest_media_type_id",
        "manifestlistmanifest",
        ["media_type_id"],
        unique=False,
    )

    op.create_table(
        "tag",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", UTF8CharField(length=190), nullable=False),
        sa.Column("repository_id", sa.Integer(), nullable=False),
        sa.Column("manifest_list_id", sa.Integer(), nullable=True),
        sa.Column("lifetime_start", sa.BigInteger(), nullable=False),
        sa.Column("lifetime_end", sa.BigInteger(), nullable=True),
        sa.Column("hidden", sa.Boolean(), nullable=False),
        sa.Column("reverted", sa.Boolean(), nullable=False),
        sa.Column("protected", sa.Boolean(), nullable=False),
        sa.Column("tag_kind_id", sa.Integer(), nullable=False),
        sa.Column("linked_tag_id", sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(
            ["linked_tag_id"], ["tag.id"], name=op.f("fk_tag_linked_tag_id_tag")
        ),
        sa.ForeignKeyConstraint(
            ["manifest_list_id"],
            ["manifestlist.id"],
            name=op.f("fk_tag_manifest_list_id_manifestlist"),
        ),
        sa.ForeignKeyConstraint(
            ["repository_id"], ["repository.id"], name=op.f("fk_tag_repository_id_repository")
        ),
        sa.ForeignKeyConstraint(
            ["tag_kind_id"], ["tagkind.id"], name=op.f("fk_tag_tag_kind_id_tagkind")
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_tag")),
    )
    op.create_index("tag_lifetime_end", "tag", ["lifetime_end"], unique=False)
    op.create_index("tag_linked_tag_id", "tag", ["linked_tag_id"], unique=False)
    op.create_index("tag_manifest_list_id", "tag", ["manifest_list_id"], unique=False)
    op.create_index("tag_repository_id", "tag", ["repository_id"], unique=False)
    op.create_index(
        "tag_repository_id_name_hidden", "tag", ["repository_id", "name", "hidden"], unique=False
    )
    op.create_index(
        "tag_repository_id_name_lifetime_end",
        "tag",
        ["repository_id", "name", "lifetime_end"],
        unique=True,
    )
    op.create_index("tag_repository_id_name", "tag", ["repository_id", "name"], unique=False)
    op.create_index("tag_tag_kind_id", "tag", ["tag_kind_id"], unique=False)

    # ### end Alembic commands ###

    blobplacementlocation_table = table(
        "blobplacementlocation",
        column("id", sa.Integer()),
        column("name", sa.String()),
    )

    op.bulk_insert(
        blobplacementlocation_table,
        [
            {"name": "local_eu"},
            {"name": "local_us"},
        ],
    )

    tagkind_table = table(
        "tagkind",
        column("id", sa.Integer()),
        column("name", sa.String()),
    )

    op.bulk_insert(
        tagkind_table,
        [
            {"id": 1, "name": "tag"},
            {"id": 2, "name": "release"},
            {"id": 3, "name": "channel"},
        ],
    )

<filename>data/migrations/versions/d17c695859ea_delete_old_appr_tables.py<gh_stars>1000+
"""
Delete old Appr tables.

Revision ID: d17c695859ea
Revises: 5<PASSWORD>
Create Date: 2018-07-16 15:21:11.593040
"""

# revision identifiers, used by Alembic.
revision = "d17c695859ea"
down_revision = "5d463ea1e8a8"

import sqlalchemy as sa
from sqlalchemy.sql import table, column
from util.migrate import UTF8LongText, UTF8CharField


def upgrade(op, tables, tester):
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table("tag")
    op.drop_table("manifestlistmanifest")
    op.drop_table("manifestlist")
    op.drop_table("manifestblob")
    op.drop_table("manifest")
    op.drop_table("blobplacement")
    op.drop_table("blob")
    op.drop_table("blobplacementlocation")
    op.drop_table("tagkind")
    # ### end Alembic commands ###


def downgrade(op, tables, tester):
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "tagkind",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_tagkind")),
    )
    op.create_index("tagkind_name", "tagkind", ["name"], unique=True)

    op.create_table(
        "blobplacementlocation",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_blobplacementlocation")),
    )
    op.create_index("blobplacementlocation_name", "blobplacementlocation", ["name"], unique=True)

    op.create_table(
        "blob",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("digest", sa.String(length=255), nullable=False),
        sa.Column("media_type_id", sa.Integer(), nullable=False),
        sa.Column("size", sa.BigInteger(), nullable=False),
        sa.Column("uncompressed_size", sa.BigInteger(), nullable=True),
        sa.ForeignKeyConstraint(
            ["media_type_id"], ["mediatype.id"], name=op.f("fk_blob_media_type_id_mediatype")
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_blob")),
    )
    op.create_index("blob_digest", "blob", ["digest"], unique=True)
    op.create_index("blob_media_type_id", "blob", ["media_type_id"], unique=False)

    op.create_table(
        "manifest",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("digest", sa.String(length=255), nullable=False),
        sa.Column("media_type_id", sa.Integer(), nullable=False),
        sa.Column("manifest_json", UTF8LongText, nullable=False),
        sa.ForeignKeyConstraint(
            ["media_type_id"], ["mediatype.id"], name=op.f("fk_manifest_media_type_id_mediatype")
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_manifest")),
    )
    op.create_index("manifest_digest", "manifest", ["digest"], unique=True)
    op.create_index("manifest_media_type_id", "manifest", ["media_type_id"], unique=False)

    op.create_table(
        "manifestlist",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("digest", sa.String(length=255), nullable=False),
        sa.Column("manifest_list_json", UTF8LongText, nullable=False),
        sa.Column("schema_version", UTF8CharField(length=255), nullable=False),
        sa.Column("media_type_id", sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ["media_type_id"],
            ["mediatype.id"],
            name=op.f("fk_manifestlist_media_type_id_mediatype"),
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_manifestlist")),
    )
    op.create_index("manifestlist_digest", "manifestlist", ["digest"], unique=True)
    op.create_index("manifestlist_media_type_id", "manifestlist", ["media_type_id"], unique=False)

    op.create_table(
        "blobplacement",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("blob_id", sa.Integer(), nullable=False),
        sa.Column("location_id", sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ["blob_id"], ["blob.id"], name=op.f("fk_blobplacement_blob_id_blob")
        ),
        sa.ForeignKeyConstraint(
            ["location_id"],
            ["blobplacementlocation.id"],
            name=op.f("fk_blobplacement_location_id_blobplacementlocation"),
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_blobplacement")),
    )
    op.create_index("blobplacement_blob_id", "blobplacement", ["blob_id"], unique=False)
    op.create_index(
        "blobplacement_blob_id_location_id",
        "blobplacement",
        ["blob_id", "location_id"],
        unique=True,
    )
    op.create_index("blobplacement_location_id", "blobplacement", ["location_id"], unique=False)

    op.create_table(
        "manifestblob",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("manifest_id", sa.Integer(), nullable=False),
        sa.Column("blob_id", sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ["blob_id"], ["blob.id"], name=op.f("fk_manifestblob_blob_id_blob")
        ),
        sa.ForeignKeyConstraint(
            ["manifest_id"], ["manifest.id"], name=op.f("fk_manifestblob_manifest_id_manifest")
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_manifestblob")),
    )
    op.create_index("manifestblob_blob_id", "manifestblob", ["blob_id"], unique=False)
    op.create_index("manifestblob_manifest_id", "manifestblob", ["manifest_id"], unique=False)
    op.create_index(
        "manifestblob_manifest_id_blob_id", "manifestblob", ["manifest_id", "blob_id"], unique=True
    )

    op.create_table(
        "manifestlistmanifest",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("manifest_list_id", sa.Integer(), nullable=False),
        sa.Column("manifest_id", sa.Integer(), nullable=False),
        sa.Column("operating_system", UTF8CharField(length=255), nullable=True),
        sa.Column("architecture", UTF8CharField(length=255), nullable=True),
        sa.Column("platform_json", UTF8LongText, nullable=True),
        sa.Column("media_type_id", sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ["manifest_id"],
            ["manifest.id"],
            name=op.f("fk_manifestlistmanifest_manifest_id_manifest"),
        ),
        sa.ForeignKeyConstraint(
            ["manifest_list_id"],
            ["manifestlist.id"],
            name=op.f("fk_manifestlistmanifest_manifest_list_id_manifestlist"),
        ),
        sa.ForeignKeyConstraint(
            ["media_type_id"],
            ["mediatype.id"],
            name=op.f("fk_manifestlistmanifest_media_type_id_mediatype"),
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_manifestlistmanifest")),
    )
    op.create_index(
        "manifestlistmanifest_manifest_id", "manifestlistmanifest", ["manifest_id"], unique=False
    )
    op.create_index(
        "manifestlistmanifest_manifest_list_id",
        "manifestlistmanifest",
        ["manifest_list_id"],
        unique=False,
    )
    op.create_index(
        "manifestlistmanifest_manifest_listid_os_arch_mtid",
        "manifestlistmanifest",
        ["manifest_list_id", "operating_system", "architecture", "media_type_id"],
        unique=False,
    )
    op.create_index(
        "manifestlistmanifest_manifest_listid_mtid",
        "manifestlistmanifest",
        ["manifest_list_id", "media_type_id"],
        unique=False,
    )
    op.create_index(
        "manifestlistmanifest_media_type_id",
        "manifestlistmanifest",
        ["media_type_id"],
        unique=False,
    )

    op.create_table(
        "tag",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", UTF8CharField(length=190), nullable=False),
        sa.Column("repository_id", sa.Integer(), nullable=False),
        sa.Column("manifest_list_id", sa.Integer(), nullable=True),
        sa.Column("lifetime_start", sa.BigInteger(), nullable=False),
        sa.Column("lifetime_end", sa.BigInteger(), nullable=True),
        sa.Column("hidden", sa.Boolean(), nullable=False),
        sa.Column("reverted", sa.Boolean(), nullable=False),
        sa.Column("protected", sa.Boolean(), nullable=False),
        sa.Column("tag_kind_id", sa.Integer(), nullable=False),
        sa.Column("linked_tag_id", sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(
            ["linked_tag_id"], ["tag.id"], name=op.f("fk_tag_linked_tag_id_tag")
        ),
        sa.ForeignKeyConstraint(
            ["manifest_list_id"],
            ["manifestlist.id"],
            name=op.f("fk_tag_manifest_list_id_manifestlist"),
        ),
        sa.ForeignKeyConstraint(
            ["repository_id"], ["repository.id"], name=op.f("fk_tag_repository_id_repository")
        ),
        sa.ForeignKeyConstraint(
            ["tag_kind_id"], ["tagkind.id"], name=op.f("fk_tag_tag_kind_id_tagkind")
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_tag")),
    )
    op.create_index("tag_lifetime_end", "tag", ["lifetime_end"], unique=False)
    op.create_index("tag_linked_tag_id", "tag", ["linked_tag_id"], unique=False)
    op.create_index("tag_manifest_list_id", "tag", ["manifest_list_id"], unique=False)
    op.create_index("tag_repository_id", "tag", ["repository_id"], unique=False)
    op.create_index(
        "tag_repository_id_name_hidden", "tag", ["repository_id", "name", "hidden"], unique=False
    )
    op.create_index(
        "tag_repository_id_name_lifetime_end",
        "tag",
        ["repository_id", "name", "lifetime_end"],
        unique=True,
    )
    op.create_index("tag_repository_id_name", "tag", ["repository_id", "name"], unique=False)
    op.create_index("tag_tag_kind_id", "tag", ["tag_kind_id"], unique=False)

    # ### end Alembic commands ###

    blobplacementlocation_table = table(
        "blobplacementlocation",
        column("id", sa.Integer()),
        column("name", sa.String()),
    )

    op.bulk_insert(
        blobplacementlocation_table,
        [
            {"name": "local_eu"},
            {"name": "local_us"},
        ],
    )

    tagkind_table = table(
        "tagkind",
        column("id", sa.Integer()),
        column("name", sa.String()),
    )

    op.bulk_insert(
        tagkind_table,
        [
            {"id": 1, "name": "tag"},
            {"id": 2, "name": "release"},
            {"id": 3, "name": "channel"},
        ],
    )

Delete old Appr tables.  Revision ID: d17c695859ea Revises: 5<PASSWORD> Create Date: 2018-07-16 15:21:11.593040 # revision identifiers, used by Alembic. # ### commands auto generated by Alembic - please adjust! ### # ### end Alembic commands ### # ### commands auto generated by Alembic - please adjust! ### # ### end Alembic commands ###