103 lines
4.7 KiB
Python
103 lines
4.7 KiB
Python
"""Add database-stored scan configurations
|
|
|
|
Revision ID: 007
|
|
Revises: 006
|
|
Create Date: 2025-11-19
|
|
|
|
This migration introduces database-stored scan configurations to replace YAML
|
|
config files. Configs reference sites from the sites table, enabling visual
|
|
config builder and better data management.
|
|
"""
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
from sqlalchemy import text
|
|
|
|
|
|
# revision identifiers, used by Alembic
|
|
revision = '007'
|
|
down_revision = '006'
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade():
|
|
"""
|
|
Create scan_configs and scan_config_sites tables.
|
|
Add config_id foreign keys to scans and schedules tables.
|
|
"""
|
|
|
|
# Create scan_configs table
|
|
op.create_table('scan_configs',
|
|
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
|
|
sa.Column('title', sa.String(length=255), nullable=False, comment='Configuration title'),
|
|
sa.Column('description', sa.Text(), nullable=True, comment='Configuration description'),
|
|
sa.Column('created_at', sa.DateTime(), nullable=False, comment='Config creation time'),
|
|
sa.Column('updated_at', sa.DateTime(), nullable=False, comment='Last modification time'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
|
|
# Create scan_config_sites table (many-to-many between configs and sites)
|
|
op.create_table('scan_config_sites',
|
|
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
|
|
sa.Column('config_id', sa.Integer(), nullable=False, comment='FK to scan_configs'),
|
|
sa.Column('site_id', sa.Integer(), nullable=False, comment='FK to sites'),
|
|
sa.Column('created_at', sa.DateTime(), nullable=False, comment='Association creation time'),
|
|
sa.ForeignKeyConstraint(['config_id'], ['scan_configs.id'], ),
|
|
sa.ForeignKeyConstraint(['site_id'], ['sites.id'], ),
|
|
sa.PrimaryKeyConstraint('id'),
|
|
sa.UniqueConstraint('config_id', 'site_id', name='uix_config_site')
|
|
)
|
|
op.create_index(op.f('ix_scan_config_sites_config_id'), 'scan_config_sites', ['config_id'], unique=False)
|
|
op.create_index(op.f('ix_scan_config_sites_site_id'), 'scan_config_sites', ['site_id'], unique=False)
|
|
|
|
# Add config_id to scans table
|
|
with op.batch_alter_table('scans', schema=None) as batch_op:
|
|
batch_op.add_column(sa.Column('config_id', sa.Integer(), nullable=True, comment='FK to scan_configs table'))
|
|
batch_op.create_index('ix_scans_config_id', ['config_id'], unique=False)
|
|
batch_op.create_foreign_key('fk_scans_config_id', 'scan_configs', ['config_id'], ['id'])
|
|
# Mark config_file as deprecated in comment (already has nullable=True)
|
|
|
|
# Add config_id to schedules table and make config_file nullable
|
|
with op.batch_alter_table('schedules', schema=None) as batch_op:
|
|
batch_op.add_column(sa.Column('config_id', sa.Integer(), nullable=True, comment='FK to scan_configs table'))
|
|
batch_op.create_index('ix_schedules_config_id', ['config_id'], unique=False)
|
|
batch_op.create_foreign_key('fk_schedules_config_id', 'scan_configs', ['config_id'], ['id'])
|
|
# Make config_file nullable (it was required before)
|
|
batch_op.alter_column('config_file', existing_type=sa.Text(), nullable=True)
|
|
|
|
connection = op.get_bind()
|
|
|
|
print("✓ Migration complete: Scan configs tables created")
|
|
print(" - Created scan_configs table for database-stored configurations")
|
|
print(" - Created scan_config_sites association table")
|
|
print(" - Added config_id to scans table")
|
|
print(" - Added config_id to schedules table")
|
|
print(" - Existing YAML configs remain in config_file column for backward compatibility")
|
|
|
|
|
|
def downgrade():
|
|
"""Remove scan config tables and columns."""
|
|
|
|
# Remove foreign keys and columns from schedules
|
|
with op.batch_alter_table('schedules', schema=None) as batch_op:
|
|
batch_op.drop_constraint('fk_schedules_config_id', type_='foreignkey')
|
|
batch_op.drop_index('ix_schedules_config_id')
|
|
batch_op.drop_column('config_id')
|
|
# Restore config_file as required
|
|
batch_op.alter_column('config_file', existing_type=sa.Text(), nullable=False)
|
|
|
|
# Remove foreign keys and columns from scans
|
|
with op.batch_alter_table('scans', schema=None) as batch_op:
|
|
batch_op.drop_constraint('fk_scans_config_id', type_='foreignkey')
|
|
batch_op.drop_index('ix_scans_config_id')
|
|
batch_op.drop_column('config_id')
|
|
|
|
# Drop tables in reverse order
|
|
op.drop_index(op.f('ix_scan_config_sites_site_id'), table_name='scan_config_sites')
|
|
op.drop_index(op.f('ix_scan_config_sites_config_id'), table_name='scan_config_sites')
|
|
op.drop_table('scan_config_sites')
|
|
|
|
op.drop_table('scan_configs')
|
|
|
|
print("✓ Downgrade complete: Scan config tables and columns removed")
|