<filename>migrations/versions/0b6d2cef18e8_add_location_system.py<gh_stars>0
"""Add location system

Revision ID: 0b6d2cef18e8
Revises: 75b8081a1c9c
Create Date: 2021-02-02 15:41:59.052802

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision = "0b6d2cef18e8"
down_revision = "75b8081a1c9c"
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "address",
        sa.Column("creation_time", sa.DateTime(), nullable=False),
        sa.Column("geo_api_id", sa.String(length=255), nullable=False),
        sa.Column("type", sa.String(length=20), nullable=False),
        sa.Column("coords", sa.ARRAY(sa.Numeric()), nullable=False),
        sa.Column("postal_code", sa.String(length=20), nullable=False),
        sa.Column("city", sa.String(length=255), nullable=False),
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.Column(
            "geo_api_raw_data",
            postgresql.JSONB(none_as_null=True, astext_type=sa.Text()),
            nullable=False,
        ),
        sa.Column("id", sa.Integer(), nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_address_geo_api_id"), "address", ["geo_api_id"], unique=False
    )
    op.create_table(
        "company_known_address",
        sa.Column("creation_time", sa.DateTime(), nullable=False),
        sa.Column("dismissed_at", sa.DateTime(), nullable=True),
        sa.Column(
            "dismiss_context",
            postgresql.JSONB(none_as_null=True, astext_type=sa.Text()),
            nullable=True,
        ),
        sa.Column("address_id", sa.Integer(), nullable=False),
        sa.Column("company_id", sa.Integer(), nullable=False),
        sa.Column("alias", sa.String(length=255), nullable=True),
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("dismiss_author_id", sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(
            ["address_id"],
            ["address.id"],
        ),
        sa.ForeignKeyConstraint(
            ["company_id"],
            ["company.id"],
        ),
        sa.ForeignKeyConstraint(
            ["dismiss_author_id"],
            ["user.id"],
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "company_id",
            "address_id",
            name="only_one_entry_per_company_and_address",
        ),
    )
    op.create_index(
        op.f("ix_company_known_address_address_id"),
        "company_known_address",
        ["address_id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_company_known_address_company_id"),
        "company_known_address",
        ["company_id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_company_known_address_dismiss_author_id"),
        "company_known_address",
        ["dismiss_author_id"],
        unique=False,
    )
    op.create_table(
        "location_entry",
        sa.Column("creation_time", sa.DateTime(), nullable=False),
        sa.Column("reception_time", sa.DateTime(), nullable=False),
        sa.Column("mission_id", sa.Integer(), nullable=False),
        sa.Column("address_id", sa.Integer(), nullable=False),
        sa.Column("company_known_address_id", sa.Integer(), nullable=True),
        sa.Column(
            "type",
            sa.Enum(
                "mission_start_location",
                "mission_end_location",
                name="locationentrytype",
                native_enum=False,
            ),
            nullable=False,
        ),
        sa.Column("submitter_id", sa.Integer(), nullable=False),
        sa.Column("id", sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ["address_id"],
            ["address.id"],
        ),
        sa.ForeignKeyConstraint(
            ["company_known_address_id"],
            ["company_known_address.id"],
        ),
        sa.ForeignKeyConstraint(
            ["mission_id"],
            ["mission.id"],
        ),
        sa.ForeignKeyConstraint(
            ["submitter_id"],
            ["user.id"],
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "mission_id",
            "type",
            name="only_one_location_entry_per_mission_and_type",
        ),
    )
    op.create_index(
        op.f("ix_location_entry_address_id"),
        "location_entry",
        ["address_id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_location_entry_mission_id"),
        "location_entry",
        ["mission_id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_location_entry_submitter_id"),
        "location_entry",
        ["submitter_id"],
        unique=False,
    )
    # ### end Alembic commands ###


def downgrade():
    op.drop_index(
        op.f("ix_location_entry_submitter_id"), table_name="location_entry"
    )
    op.drop_index(
        op.f("ix_location_entry_mission_id"), table_name="location_entry"
    )
    op.drop_index(
        op.f("ix_location_entry_address_id"), table_name="location_entry"
    )
    op.drop_table("location_entry")
    op.drop_index(
        op.f("ix_company_known_address_dismiss_author_id"),
        table_name="company_known_address",
    )
    op.drop_index(
        op.f("ix_company_known_address_company_id"),
        table_name="company_known_address",
    )
    op.drop_index(
        op.f("ix_company_known_address_address_id"),
        table_name="company_known_address",
    )
    op.drop_table("company_known_address")
    op.drop_index(op.f("ix_address_geo_api_id"), table_name="address")
    op.drop_table("address")
    # ### end Alembic commands ###

<filename>migrations/versions/0b6d2cef18e8_add_location_system.py<gh_stars>0
"""Add location system

Revision ID: 0b6d2cef18e8
Revises: 75b8081a1c9c
Create Date: 2021-02-02 15:41:59.052802

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision = "0b6d2cef18e8"
down_revision = "75b8081a1c9c"
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "address",
        sa.Column("creation_time", sa.DateTime(), nullable=False),
        sa.Column("geo_api_id", sa.String(length=255), nullable=False),
        sa.Column("type", sa.String(length=20), nullable=False),
        sa.Column("coords", sa.ARRAY(sa.Numeric()), nullable=False),
        sa.Column("postal_code", sa.String(length=20), nullable=False),
        sa.Column("city", sa.String(length=255), nullable=False),
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.Column(
            "geo_api_raw_data",
            postgresql.JSONB(none_as_null=True, astext_type=sa.Text()),
            nullable=False,
        ),
        sa.Column("id", sa.Integer(), nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_address_geo_api_id"), "address", ["geo_api_id"], unique=False
    )
    op.create_table(
        "company_known_address",
        sa.Column("creation_time", sa.DateTime(), nullable=False),
        sa.Column("dismissed_at", sa.DateTime(), nullable=True),
        sa.Column(
            "dismiss_context",
            postgresql.JSONB(none_as_null=True, astext_type=sa.Text()),
            nullable=True,
        ),
        sa.Column("address_id", sa.Integer(), nullable=False),
        sa.Column("company_id", sa.Integer(), nullable=False),
        sa.Column("alias", sa.String(length=255), nullable=True),
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("dismiss_author_id", sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(
            ["address_id"],
            ["address.id"],
        ),
        sa.ForeignKeyConstraint(
            ["company_id"],
            ["company.id"],
        ),
        sa.ForeignKeyConstraint(
            ["dismiss_author_id"],
            ["user.id"],
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "company_id",
            "address_id",
            name="only_one_entry_per_company_and_address",
        ),
    )
    op.create_index(
        op.f("ix_company_known_address_address_id"),
        "company_known_address",
        ["address_id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_company_known_address_company_id"),
        "company_known_address",
        ["company_id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_company_known_address_dismiss_author_id"),
        "company_known_address",
        ["dismiss_author_id"],
        unique=False,
    )
    op.create_table(
        "location_entry",
        sa.Column("creation_time", sa.DateTime(), nullable=False),
        sa.Column("reception_time", sa.DateTime(), nullable=False),
        sa.Column("mission_id", sa.Integer(), nullable=False),
        sa.Column("address_id", sa.Integer(), nullable=False),
        sa.Column("company_known_address_id", sa.Integer(), nullable=True),
        sa.Column(
            "type",
            sa.Enum(
                "mission_start_location",
                "mission_end_location",
                name="locationentrytype",
                native_enum=False,
            ),
            nullable=False,
        ),
        sa.Column("submitter_id", sa.Integer(), nullable=False),
        sa.Column("id", sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ["address_id"],
            ["address.id"],
        ),
        sa.ForeignKeyConstraint(
            ["company_known_address_id"],
            ["company_known_address.id"],
        ),
        sa.ForeignKeyConstraint(
            ["mission_id"],
            ["mission.id"],
        ),
        sa.ForeignKeyConstraint(
            ["submitter_id"],
            ["user.id"],
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "mission_id",
            "type",
            name="only_one_location_entry_per_mission_and_type",
        ),
    )
    op.create_index(
        op.f("ix_location_entry_address_id"),
        "location_entry",
        ["address_id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_location_entry_mission_id"),
        "location_entry",
        ["mission_id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_location_entry_submitter_id"),
        "location_entry",
        ["submitter_id"],
        unique=False,
    )
    # ### end Alembic commands ###


def downgrade():
    op.drop_index(
        op.f("ix_location_entry_submitter_id"), table_name="location_entry"
    )
    op.drop_index(
        op.f("ix_location_entry_mission_id"), table_name="location_entry"
    )
    op.drop_index(
        op.f("ix_location_entry_address_id"), table_name="location_entry"
    )
    op.drop_table("location_entry")
    op.drop_index(
        op.f("ix_company_known_address_dismiss_author_id"),
        table_name="company_known_address",
    )
    op.drop_index(
        op.f("ix_company_known_address_company_id"),
        table_name="company_known_address",
    )
    op.drop_index(
        op.f("ix_company_known_address_address_id"),
        table_name="company_known_address",
    )
    op.drop_table("company_known_address")
    op.drop_index(op.f("ix_address_geo_api_id"), table_name="address")
    op.drop_table("address")
    # ### end Alembic commands ###

Add location system  Revision ID: 0b6d2cef18e8 Revises: 75b8081a1c9c Create Date: 2021-02-02 15:41:59.052802 # revision identifiers, used by Alembic. # ### commands auto generated by Alembic - please adjust! ### # ### end Alembic commands ### # ### end Alembic commands ###