AcidMap/migrations/versions/92ec1d480a3c_add_soil_table.py

"""添加新数据表

Revision ID: 92ec1d480a3c
Revises: beeaf68d0ee1
Create Date: 2025-07-11 03:08:36.151648

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '92ec1d480a3c'
down_revision = 'beeaf68d0ee1'
branch_labels = None
depends_on = None


def upgrade():
    """升级数据库到当前版本"""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('Soil_data',
    sa.Column('ID', sa.Integer(), autoincrement=True, nullable=False, comment='自增主键'),
    sa.Column('Farmland_ID', sa.Integer(), nullable=False, comment='关联Farmland_data表的Farmland_ID'),
    sa.Column('Sample_ID', sa.Integer(), nullable=False, comment='关联Farmland_data表的Sample_ID'),
    sa.Column('0002IDW', sa.Float(), nullable=True, comment='粒径<0.002mm的黏粒组分占比(%)'),
    sa.Column('bd020_90', sa.Float(), nullable=True, comment='容重(g/cm³)，使用时需除1000'),
    sa.Column('POR_Layer', sa.Float(), nullable=True, comment='土壤孔隙度(cm³/cm³)'),
    sa.Column('ExAl_IDW', sa.Float(), nullable=True, comment='交换性Al³⁺[cmol(1/3Al³⁺)/kg]'),
    sa.Column('ExCa_IDW', sa.Float(), nullable=True, comment='交换性Ca²⁺[cmol(1/2Ca²⁺)/kg]'),
    sa.Column('ExK_IDW', sa.Float(), nullable=True, comment='交换性K⁺[cmol(K⁺)/kg]'),
    sa.Column('ExMg_IDW', sa.Float(), nullable=True, comment='交换性Mg²⁺[cmol(1/2Mg²⁺)/kg]'),
    sa.Column('ExNa_IDW', sa.Float(), nullable=True, comment='交换性Na⁺[cmol(Na⁺)/kg]'),
    sa.Column('Fed_IDW', sa.Float(), nullable=True, comment='游离铁(g/kg)'),
    sa.Column('SOM_IDW', sa.Float(), nullable=True, comment='有机质(g/kg)'),
    sa.Column('IDW_2013PC_Cd', sa.Float(), nullable=True, comment='2013年普查总镉(mg/kg)'),
    sa.Column('IDW_2018XC_Cd', sa.Float(), nullable=True, comment='2018年详查总镉(mg/kg)'),
    sa.Column('IDW_2023SP_Cd', sa.Float(), nullable=True, comment='2023年三普镉(mg/kg)'),
    sa.Column('IDW_2013PC_pH', sa.Float(), nullable=True, comment='2013年普查pH值'),
    sa.Column('IDW_2018XC_pH', sa.Float(), nullable=True, comment='2018年详查pH值'),
    sa.Column('IDW_2023SP_pH', sa.Float(), nullable=True, comment='2023年三普pH值'),
    sa.Column('002_0002IDW', sa.Float(), nullable=True, comment='粉粒组分(0.002~0.02mm)占比(%)'),
    sa.Column('02_002IDW', sa.Float(), nullable=True, comment='砂粒组分(0.02~0.2mm)占比(%)'),
    sa.Column('2_02IDW', sa.Float(), nullable=True, comment='石砾组分(>2mm)占比(%)'),
    sa.Column('AvaK_IDW', sa.Float(), nullable=True, comment='速效钾(mg/kg)'),
    sa.Column('AvaP_IDW', sa.Float(), nullable=True, comment='有效磷(mg/kg)'),
    sa.Column('CEC_IDW', sa.Float(), nullable=True, comment='阳离子交换量[cmol(+)/kg]'),
    sa.Column('EC_IDW', sa.Float(), nullable=True, comment='电导率(mS/cm)'),
    sa.Column('OC-Fe_0-30', sa.Float(), nullable=True, comment='土壤铁结合态有机碳(g/kg)'),
    sa.Column('SAvaK_IDW', sa.Float(), nullable=True, comment='缓效钾(mg/kg)'),
    sa.Column('TAl_IDW', sa.Float(), nullable=True, comment='全铝(%)'),
    sa.Column('TCa_IDW', sa.Float(), nullable=True, comment='全钙(%)'),
    sa.Column('TCd_IDW', sa.Float(), nullable=True, comment='总镉(mg/kg)'),
    sa.Column('TEB_IDW', sa.Float(), nullable=True, comment='水溶性盐总量(mg/L)'),
    sa.Column('TExH_IDW', sa.Float(), nullable=True, comment='交换性酸总量[cmol (H⁺ + 1/3Al³⁺)/kg]'),
    sa.Column('TFe_IDW', sa.Float(), nullable=True, comment='全铁(%)'),
    sa.Column('TK_IDW', sa.Float(), nullable=True, comment='全钾(g/kg)'),
    sa.Column('TMg_IDW', sa.Float(), nullable=True, comment='全镁(%)'),
    sa.Column('TMn_IDW', sa.Float(), nullable=True, comment='全锰(mg/kg)'),
    sa.Column('TN_IDW', sa.Float(), nullable=True, comment='全氮(g/kg)'),
    sa.Column('TP_IDW', sa.Float(), nullable=True, comment='全磷(g/kg)'),
    sa.Column('TS_IDW', sa.Float(), nullable=True, comment='全硫(g/kg)'),
    sa.Column('DQCJ_Cd', sa.Float(), nullable=True, comment='大气沉降输入Cd(g/ha/a)'),
    sa.Column('GGS_Cd', sa.Float(), nullable=True, comment='灌溉水输入Cd(g/ha/a)'),
    sa.Column('DX_Cd', sa.Float(), nullable=True, comment='地下渗漏Cd(g/ha/a)'),
    sa.Column('DB_Cd', sa.Float(), nullable=True, comment='地表径流Cd(g/ha/a)'),
    sa.ForeignKeyConstraint(['Farmland_ID', 'Sample_ID'], ['Farmland_data.Farmland_ID', 'Farmland_data.Sample_ID'], ),
    sa.PrimaryKeyConstraint('ID'),
    comment='土壤理化性质数据表，存储关联的耕地样点土壤指标'
    )
    op.drop_index('idx_farmland_data_geom', table_name='Farmland_data', postgresql_using='gist')
    op.drop_index('raster_table_st_convexhull_idx1', table_name='raster_table', postgresql_using='gist')
    op.drop_index('raster_table_st_convexhull_idx2', table_name='raster_table', postgresql_using='gist')
    op.drop_index('raster_table_st_convexhull_idx3', table_name='raster_table', postgresql_using='gist')
    op.drop_index('raster_table_st_convexhull_idx4', table_name='raster_table', postgresql_using='gist')
    op.drop_index('raster_table_st_convexhull_idx5', table_name='raster_table', postgresql_using='gist')
    op.drop_index('raster_table_st_convexhull_idx6', table_name='raster_table', postgresql_using='gist')
    op.drop_index('raster_table_st_convexhull_idx7', table_name='raster_table', postgresql_using='gist')
    op.create_index('idx_raster_table_rast', 'raster_table', [sa.text('ST_ConvexHull(rast)')], unique=False, postgresql_using='gist')
    # ### end Alembic commands ###


def downgrade():
    """将数据库降级到上一版本"""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('idx_raster_table_rast', table_name='raster_table', postgresql_using='gist')
    op.create_index('raster_table_st_convexhull_idx7', 'raster_table', [sa.text('st_convexhull(rast)')], unique=False, postgresql_using='gist')
    op.create_index('raster_table_st_convexhull_idx6', 'raster_table', [sa.text('st_convexhull(rast)')], unique=False, postgresql_using='gist')
    op.create_index('raster_table_st_convexhull_idx5', 'raster_table', [sa.text('st_convexhull(rast)')], unique=False, postgresql_using='gist')
    op.create_index('raster_table_st_convexhull_idx4', 'raster_table', [sa.text('st_convexhull(rast)')], unique=False, postgresql_using='gist')
    op.create_index('raster_table_st_convexhull_idx3', 'raster_table', [sa.text('st_convexhull(rast)')], unique=False, postgresql_using='gist')
    op.create_index('raster_table_st_convexhull_idx2', 'raster_table', [sa.text('st_convexhull(rast)')], unique=False, postgresql_using='gist')
    op.create_index('raster_table_st_convexhull_idx1', 'raster_table', [sa.text('st_convexhull(rast)')], unique=False, postgresql_using='gist')
    op.create_index('idx_farmland_data_geom', 'Farmland_data', ['geom'], unique=False, postgresql_using='gist')
    op.drop_table('Soil_data')
    # ### end Alembic commands ###