efa5e584648_triggers_for_updating_tiledb_column_in_.py 文件源码

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column(
        'reference_set',
        sa.Column('next_tiledb_column_offset', sa.BigInteger(), nullable=False, default=0))
    op.add_column(
        'reference', 
        sa.Column('tiledb_column_offset', sa.BigInteger(), nullable=True))
    op.alter_column('reference', 'length', existing_type=sa.BIGINT(), nullable=False)
    op.alter_column('reference', 'name', existing_type=sa.TEXT(), nullable=False)
    op.create_unique_constraint('unique_name_per_reference_set_constraint', 'reference', 
        ['reference_set_id', 'name'])
    op.create_index('unique_reference_set_id_offset_idx', 'reference', 
        ['reference_set_id', 'tiledb_column_offset'], 
        unique=True)
    op.drop_column('reference', 'offset')
    # Trigger on reference insertion
    op.execute('''\
    CREATE OR REPLACE FUNCTION increment_next_column_in_reference_set_pgsql()
      RETURNS trigger AS $increment_next_column_in_reference_set_pgsql$
    BEGIN
      UPDATE reference SET tiledb_column_offset=(select next_tiledb_column_offset from reference_set where id=NEW.reference_set_id) where NEW.tiledb_column_offset IS NULL and id=NEW.id;
      UPDATE reference_set SET next_tiledb_column_offset=next_tiledb_column_offset+NEW.length WHERE id = NEW.reference_set_id;
      RETURN NEW;
    END;
    $increment_next_column_in_reference_set_pgsql$ LANGUAGE plpgsql;
    CREATE TRIGGER increment_next_column_in_reference_set AFTER INSERT ON reference
    FOR EACH ROW EXECUTE PROCEDURE increment_next_column_in_reference_set_pgsql();
    ''')
    ### end Alembic commands ###