115 lines
3.8 KiB
SQL
115 lines
3.8 KiB
SQL
-- +goose Up
|
|
-- Align schema for single-request takeover create flow.
|
|
-- Only apply additive changes when columns/constraints are missing.
|
|
|
|
SET @schema_name := DATABASE();
|
|
|
|
-- reserve_acs.base_id
|
|
SELECT COUNT(*) INTO @reserve_acs_has_base_id
|
|
FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @schema_name
|
|
AND TABLE_NAME = 'reserve_acs'
|
|
AND COLUMN_NAME = 'base_id';
|
|
|
|
SET @add_reserve_acs_base_id_sql := IF(
|
|
@reserve_acs_has_base_id = 0,
|
|
'ALTER TABLE reserve_acs ADD COLUMN base_id BINARY(16) NULL AFTER status',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt_add_reserve_acs_base_id FROM @add_reserve_acs_base_id_sql;
|
|
EXECUTE stmt_add_reserve_acs_base_id;
|
|
DEALLOCATE PREPARE stmt_add_reserve_acs_base_id;
|
|
|
|
-- Index reserve_acs.base_id
|
|
SELECT COUNT(*) INTO @reserve_acs_has_base_id_idx
|
|
FROM information_schema.STATISTICS
|
|
WHERE TABLE_SCHEMA = @schema_name
|
|
AND TABLE_NAME = 'reserve_acs'
|
|
AND INDEX_NAME = 'idx_reserve_acs_base_id';
|
|
|
|
SET @add_reserve_acs_base_id_idx_sql := IF(
|
|
@reserve_acs_has_base_id_idx = 0,
|
|
'CREATE INDEX idx_reserve_acs_base_id ON reserve_acs(base_id)',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt_add_reserve_acs_base_id_idx FROM @add_reserve_acs_base_id_idx_sql;
|
|
EXECUTE stmt_add_reserve_acs_base_id_idx;
|
|
DEALLOCATE PREPARE stmt_add_reserve_acs_base_id_idx;
|
|
|
|
-- FK reserve_acs.base_id -> bases.id
|
|
SELECT COUNT(*) INTO @reserve_acs_has_base_fk
|
|
FROM information_schema.TABLE_CONSTRAINTS
|
|
WHERE CONSTRAINT_SCHEMA = @schema_name
|
|
AND TABLE_NAME = 'reserve_acs'
|
|
AND CONSTRAINT_NAME = 'fk_reserve_acs_base'
|
|
AND CONSTRAINT_TYPE = 'FOREIGN KEY';
|
|
|
|
SET @add_reserve_acs_base_fk_sql := IF(
|
|
@reserve_acs_has_base_fk = 0,
|
|
'ALTER TABLE reserve_acs ADD CONSTRAINT fk_reserve_acs_base FOREIGN KEY (base_id) REFERENCES bases(id)',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt_add_reserve_acs_base_fk FROM @add_reserve_acs_base_fk_sql;
|
|
EXECUTE stmt_add_reserve_acs_base_fk;
|
|
DEALLOCATE PREPARE stmt_add_reserve_acs_base_fk;
|
|
|
|
-- Optional backfill reserve_acs.base_id from linked flight -> duty_roster.
|
|
-- Safe to run repeatedly.
|
|
UPDATE reserve_acs ra
|
|
JOIN flights f ON f.reserve_ac_id = ra.id AND f.deleted_at IS NULL
|
|
JOIN duty_rosters dr ON dr.id = f.duty_roster_id AND dr.deleted_at IS NULL
|
|
SET ra.base_id = dr.base_id
|
|
WHERE ra.base_id IS NULL
|
|
AND ra.deleted_at IS NULL;
|
|
|
|
-- +goose Down
|
|
-- Keep data; only drop FK/index/column if present.
|
|
|
|
SET @schema_name := DATABASE();
|
|
|
|
SELECT COUNT(*) INTO @reserve_acs_has_base_fk
|
|
FROM information_schema.TABLE_CONSTRAINTS
|
|
WHERE CONSTRAINT_SCHEMA = @schema_name
|
|
AND TABLE_NAME = 'reserve_acs'
|
|
AND CONSTRAINT_NAME = 'fk_reserve_acs_base'
|
|
AND CONSTRAINT_TYPE = 'FOREIGN KEY';
|
|
|
|
SET @drop_reserve_acs_base_fk_sql := IF(
|
|
@reserve_acs_has_base_fk = 1,
|
|
'ALTER TABLE reserve_acs DROP FOREIGN KEY fk_reserve_acs_base',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt_drop_reserve_acs_base_fk FROM @drop_reserve_acs_base_fk_sql;
|
|
EXECUTE stmt_drop_reserve_acs_base_fk;
|
|
DEALLOCATE PREPARE stmt_drop_reserve_acs_base_fk;
|
|
|
|
SELECT COUNT(*) INTO @reserve_acs_has_base_id_idx
|
|
FROM information_schema.STATISTICS
|
|
WHERE TABLE_SCHEMA = @schema_name
|
|
AND TABLE_NAME = 'reserve_acs'
|
|
AND INDEX_NAME = 'idx_reserve_acs_base_id';
|
|
|
|
SET @drop_reserve_acs_base_id_idx_sql := IF(
|
|
@reserve_acs_has_base_id_idx = 1,
|
|
'DROP INDEX idx_reserve_acs_base_id ON reserve_acs',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt_drop_reserve_acs_base_id_idx FROM @drop_reserve_acs_base_id_idx_sql;
|
|
EXECUTE stmt_drop_reserve_acs_base_id_idx;
|
|
DEALLOCATE PREPARE stmt_drop_reserve_acs_base_id_idx;
|
|
|
|
SELECT COUNT(*) INTO @reserve_acs_has_base_id
|
|
FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @schema_name
|
|
AND TABLE_NAME = 'reserve_acs'
|
|
AND COLUMN_NAME = 'base_id';
|
|
|
|
SET @drop_reserve_acs_base_id_sql := IF(
|
|
@reserve_acs_has_base_id = 1,
|
|
'ALTER TABLE reserve_acs DROP COLUMN base_id',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt_drop_reserve_acs_base_id FROM @drop_reserve_acs_base_id_sql;
|
|
EXECUTE stmt_drop_reserve_acs_base_id;
|
|
DEALLOCATE PREPARE stmt_drop_reserve_acs_base_id;
|