AcidMap/migrations/versions/d89bb52ae481_add_provinces_and_cities_tables.py

"""add_provinces_and_cities_tables

Revision ID: d89bb52ae481
Revises: e52e802fe61a
Create Date: 2025-08-09 19:40:59.929438

"""
from alembic import op
import sqlalchemy as sa
import geoalchemy2

# revision identifiers, used by Alembic.
revision = 'd89bb52ae481'
down_revision = 'e52e802fe61a'
branch_labels = None
depends_on = None


def upgrade():
    """升级数据库到当前版本"""
    # 创建provinces表
    op.create_table('provinces',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=100), nullable=False, comment='省名'),
    sa.Column('code', sa.Integer(), nullable=False, comment='省代码'),
    sa.Column('geometry', geoalchemy2.types.Geometry(geometry_type='MULTIPOLYGON', srid=4326, from_text='ST_GeomFromEWKT', name='geometry', nullable=False), nullable=False, comment='省级行政区划的几何数据'),
    sa.Column('geojson', sa.JSON(), nullable=False, comment='完整的GeoJSON数据'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('code')
    )
    op.create_index('idx_provinces_code', 'provinces', ['code'], unique=False)
    op.create_index('idx_provinces_name', 'provinces', ['name'], unique=False)
    op.create_index(op.f('ix_provinces_id'), 'provinces', ['id'], unique=False)
    
    # 创建cities表
    op.create_table('cities',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=100), nullable=False, comment='市名'),
    sa.Column('code', sa.Integer(), nullable=False, comment='市代码'),
    sa.Column('province_name', sa.String(length=100), nullable=False, comment='省名'),
    sa.Column('province_code', sa.Integer(), nullable=False, comment='省代码'),
    sa.Column('geometry', geoalchemy2.types.Geometry(geometry_type='MULTIPOLYGON', srid=4326, from_text='ST_GeomFromEWKT', name='geometry', nullable=False), nullable=False, comment='市级行政区划的几何数据'),
    sa.Column('geojson', sa.JSON(), nullable=False, comment='完整的GeoJSON数据'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('code')
    )
    op.create_index('idx_cities_code', 'cities', ['code'], unique=False)
    op.create_index('idx_cities_name', 'cities', ['name'], unique=False)
    op.create_index('idx_cities_province_code', 'cities', ['province_code'], unique=False)
    op.create_index(op.f('ix_cities_id'), 'cities', ['id'], unique=False)


def downgrade():
    """降级数据库到上一个版本"""
    # 删除cities表
    op.drop_index(op.f('ix_cities_id'), table_name='cities')
    op.drop_index('idx_cities_province_code', table_name='cities')
    op.drop_index('idx_cities_name', table_name='cities')
    op.drop_index('idx_cities_code', table_name='cities')
    op.drop_table('cities')

    # 删除provinces表
    op.drop_index(op.f('ix_provinces_id'), table_name='provinces')
    op.drop_index('idx_provinces_name', table_name='provinces')
    op.drop_index('idx_provinces_code', table_name='provinces')
    op.drop_table('provinces')