81 lines
2.4 KiB
SQL
81 lines
2.4 KiB
SQL
-- +goose Up
|
|
SET @table_hospitals_exists := (
|
|
SELECT COUNT(*)
|
|
FROM information_schema.tables
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'hospitals'
|
|
);
|
|
|
|
SET @col_hospitals_is_active_exists := (
|
|
SELECT COUNT(*)
|
|
FROM information_schema.columns
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'hospitals'
|
|
AND column_name = 'is_active'
|
|
);
|
|
|
|
SET @add_hospitals_is_active_sql := IF(
|
|
@table_hospitals_exists > 0 AND @col_hospitals_is_active_exists = 0,
|
|
'ALTER TABLE hospitals ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1 AFTER note',
|
|
'SELECT 1'
|
|
);
|
|
|
|
PREPARE stmt_add_hospitals_is_active FROM @add_hospitals_is_active_sql;
|
|
EXECUTE stmt_add_hospitals_is_active;
|
|
DEALLOCATE PREPARE stmt_add_hospitals_is_active;
|
|
|
|
SET @idx_hospitals_is_active_exists := (
|
|
SELECT COUNT(*)
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'hospitals'
|
|
AND index_name = 'idx_hospitals_is_active'
|
|
);
|
|
|
|
SET @add_idx_hospitals_is_active_sql := IF(
|
|
@table_hospitals_exists > 0 AND @idx_hospitals_is_active_exists = 0,
|
|
'ALTER TABLE hospitals ADD INDEX idx_hospitals_is_active (is_active)',
|
|
'SELECT 1'
|
|
);
|
|
|
|
PREPARE stmt_add_idx_hospitals_is_active FROM @add_idx_hospitals_is_active_sql;
|
|
EXECUTE stmt_add_idx_hospitals_is_active;
|
|
DEALLOCATE PREPARE stmt_add_idx_hospitals_is_active;
|
|
|
|
-- +goose Down
|
|
SET @idx_hospitals_is_active_exists := (
|
|
SELECT COUNT(*)
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'hospitals'
|
|
AND index_name = 'idx_hospitals_is_active'
|
|
);
|
|
|
|
SET @drop_idx_hospitals_is_active_sql := IF(
|
|
@idx_hospitals_is_active_exists > 0,
|
|
'ALTER TABLE hospitals DROP INDEX idx_hospitals_is_active',
|
|
'SELECT 1'
|
|
);
|
|
|
|
PREPARE stmt_drop_idx_hospitals_is_active FROM @drop_idx_hospitals_is_active_sql;
|
|
EXECUTE stmt_drop_idx_hospitals_is_active;
|
|
DEALLOCATE PREPARE stmt_drop_idx_hospitals_is_active;
|
|
|
|
SET @col_hospitals_is_active_exists := (
|
|
SELECT COUNT(*)
|
|
FROM information_schema.columns
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'hospitals'
|
|
AND column_name = 'is_active'
|
|
);
|
|
|
|
SET @drop_hospitals_is_active_sql := IF(
|
|
@col_hospitals_is_active_exists > 0,
|
|
'ALTER TABLE hospitals DROP COLUMN is_active',
|
|
'SELECT 1'
|
|
);
|
|
|
|
PREPARE stmt_drop_hospitals_is_active FROM @drop_hospitals_is_active_sql;
|
|
EXECUTE stmt_drop_hospitals_is_active;
|
|
DEALLOCATE PREPARE stmt_drop_hospitals_is_active;
|