102 lines
3.3 KiB
SQL
102 lines
3.3 KiB
SQL
-- +goose Up
|
|
SET @bases_exists := (
|
|
SELECT COUNT(*)
|
|
FROM information_schema.tables
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'bases'
|
|
);
|
|
|
|
SET @col_default_shift_start_exists := (
|
|
SELECT COUNT(*)
|
|
FROM information_schema.columns
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'bases'
|
|
AND column_name = 'default_shift_start'
|
|
);
|
|
|
|
SET @col_default_shift_end_exists := (
|
|
SELECT COUNT(*)
|
|
FROM information_schema.columns
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'bases'
|
|
AND column_name = 'default_shift_end'
|
|
);
|
|
|
|
SET @backfill_default_shift_start_sql := IF(
|
|
@bases_exists > 0 AND @col_default_shift_start_exists > 0,
|
|
"UPDATE bases SET default_shift_start = '06:00:00' WHERE default_shift_start IS NULL",
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt_backfill_default_shift_start FROM @backfill_default_shift_start_sql;
|
|
EXECUTE stmt_backfill_default_shift_start;
|
|
DEALLOCATE PREPARE stmt_backfill_default_shift_start;
|
|
|
|
SET @backfill_default_shift_end_sql := IF(
|
|
@bases_exists > 0 AND @col_default_shift_end_exists > 0,
|
|
"UPDATE bases SET default_shift_end = '21:00:00' WHERE default_shift_end IS NULL",
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt_backfill_default_shift_end FROM @backfill_default_shift_end_sql;
|
|
EXECUTE stmt_backfill_default_shift_end;
|
|
DEALLOCATE PREPARE stmt_backfill_default_shift_end;
|
|
|
|
SET @modify_default_shift_start_sql := IF(
|
|
@bases_exists > 0 AND @col_default_shift_start_exists > 0,
|
|
'ALTER TABLE bases MODIFY COLUMN default_shift_start TIME NOT NULL',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt_modify_default_shift_start FROM @modify_default_shift_start_sql;
|
|
EXECUTE stmt_modify_default_shift_start;
|
|
DEALLOCATE PREPARE stmt_modify_default_shift_start;
|
|
|
|
SET @modify_default_shift_end_sql := IF(
|
|
@bases_exists > 0 AND @col_default_shift_end_exists > 0,
|
|
'ALTER TABLE bases MODIFY COLUMN default_shift_end TIME NOT NULL',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt_modify_default_shift_end FROM @modify_default_shift_end_sql;
|
|
EXECUTE stmt_modify_default_shift_end;
|
|
DEALLOCATE PREPARE stmt_modify_default_shift_end;
|
|
|
|
-- +goose Down
|
|
SET @bases_exists := (
|
|
SELECT COUNT(*)
|
|
FROM information_schema.tables
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'bases'
|
|
);
|
|
|
|
SET @col_default_shift_start_exists := (
|
|
SELECT COUNT(*)
|
|
FROM information_schema.columns
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'bases'
|
|
AND column_name = 'default_shift_start'
|
|
);
|
|
|
|
SET @col_default_shift_end_exists := (
|
|
SELECT COUNT(*)
|
|
FROM information_schema.columns
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'bases'
|
|
AND column_name = 'default_shift_end'
|
|
);
|
|
|
|
SET @modify_default_shift_start_sql := IF(
|
|
@bases_exists > 0 AND @col_default_shift_start_exists > 0,
|
|
'ALTER TABLE bases MODIFY COLUMN default_shift_start TIME NULL',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt_modify_default_shift_start_down FROM @modify_default_shift_start_sql;
|
|
EXECUTE stmt_modify_default_shift_start_down;
|
|
DEALLOCATE PREPARE stmt_modify_default_shift_start_down;
|
|
|
|
SET @modify_default_shift_end_sql := IF(
|
|
@bases_exists > 0 AND @col_default_shift_end_exists > 0,
|
|
'ALTER TABLE bases MODIFY COLUMN default_shift_end TIME NULL',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt_modify_default_shift_end_down FROM @modify_default_shift_end_sql;
|
|
EXECUTE stmt_modify_default_shift_end_down;
|
|
DEALLOCATE PREPARE stmt_modify_default_shift_end_down;
|