yumi-golang/migrations/009_baishun_profile_switch.sql

SET @current_schema := DATABASE();

SET @add_provider_profile_sql := IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = @current_schema
      AND table_name = 'baishun_provider_config'
      AND column_name = 'profile'
  ),
  'ALTER TABLE `baishun_provider_config` ADD COLUMN `profile` varchar(32) NOT NULL DEFAULT ''PROD'' AFTER `sys_origin`',
  'SELECT 1'
);
PREPARE add_provider_profile_stmt FROM @add_provider_profile_sql;
EXECUTE add_provider_profile_stmt;
DEALLOCATE PREPARE add_provider_profile_stmt;

SET @add_provider_active_sql := IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = @current_schema
      AND table_name = 'baishun_provider_config'
      AND column_name = 'active'
  ),
  'ALTER TABLE `baishun_provider_config` ADD COLUMN `active` tinyint(1) NOT NULL DEFAULT 1 AFTER `profile`',
  'SELECT 1'
);
PREPARE add_provider_active_stmt FROM @add_provider_active_sql;
EXECUTE add_provider_active_stmt;
DEALLOCATE PREPARE add_provider_active_stmt;

SET @drop_provider_old_unique_sql := IF(
  EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema = @current_schema
      AND table_name = 'baishun_provider_config'
      AND index_name = 'uk_baishun_provider_sys_origin'
  ),
  'ALTER TABLE `baishun_provider_config` DROP INDEX `uk_baishun_provider_sys_origin`',
  'SELECT 1'
);
PREPARE drop_provider_old_unique_stmt FROM @drop_provider_old_unique_sql;
EXECUTE drop_provider_old_unique_stmt;
DEALLOCATE PREPARE drop_provider_old_unique_stmt;

SET @add_provider_profile_unique_sql := IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema = @current_schema
      AND table_name = 'baishun_provider_config'
      AND index_name = 'uk_baishun_provider_sys_origin_profile'
  ),
  'ALTER TABLE `baishun_provider_config` ADD UNIQUE KEY `uk_baishun_provider_sys_origin_profile` (`sys_origin`, `profile`)',
  'SELECT 1'
);
PREPARE add_provider_profile_unique_stmt FROM @add_provider_profile_unique_sql;
EXECUTE add_provider_profile_unique_stmt;
DEALLOCATE PREPARE add_provider_profile_unique_stmt;

SET @add_provider_active_index_sql := IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema = @current_schema
      AND table_name = 'baishun_provider_config'
      AND index_name = 'idx_baishun_provider_active'
  ),
  'ALTER TABLE `baishun_provider_config` ADD KEY `idx_baishun_provider_active` (`sys_origin`, `active`, `update_time`)',
  'SELECT 1'
);
PREPARE add_provider_active_index_stmt FROM @add_provider_active_index_sql;
EXECUTE add_provider_active_index_stmt;
DEALLOCATE PREPARE add_provider_active_index_stmt;

SET @add_catalog_profile_sql := IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = @current_schema
      AND table_name = 'baishun_game_catalog'
      AND column_name = 'profile'
  ),
  'ALTER TABLE `baishun_game_catalog` ADD COLUMN `profile` varchar(32) NOT NULL DEFAULT ''PROD'' AFTER `sys_origin`',
  'SELECT 1'
);
PREPARE add_catalog_profile_stmt FROM @add_catalog_profile_sql;
EXECUTE add_catalog_profile_stmt;
DEALLOCATE PREPARE add_catalog_profile_stmt;

SET @drop_catalog_old_unique_sql := IF(
  EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema = @current_schema
      AND table_name = 'baishun_game_catalog'
      AND index_name = 'uk_baishun_catalog_sys_vendor_game'
  ),
  'ALTER TABLE `baishun_game_catalog` DROP INDEX `uk_baishun_catalog_sys_vendor_game`',
  'SELECT 1'
);
PREPARE drop_catalog_old_unique_stmt FROM @drop_catalog_old_unique_sql;
EXECUTE drop_catalog_old_unique_stmt;
DEALLOCATE PREPARE drop_catalog_old_unique_stmt;

SET @add_catalog_profile_unique_sql := IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema = @current_schema
      AND table_name = 'baishun_game_catalog'
      AND index_name = 'uk_baishun_catalog_sys_profile_vendor_game'
  ),
  'ALTER TABLE `baishun_game_catalog` ADD UNIQUE KEY `uk_baishun_catalog_sys_profile_vendor_game` (`sys_origin`, `profile`, `vendor_game_id`)',
  'SELECT 1'
);
PREPARE add_catalog_profile_unique_stmt FROM @add_catalog_profile_unique_sql;
EXECUTE add_catalog_profile_unique_stmt;
DEALLOCATE PREPARE add_catalog_profile_unique_stmt;

SET @add_catalog_profile_index_sql := IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema = @current_schema
      AND table_name = 'baishun_game_catalog'
      AND index_name = 'idx_baishun_catalog_profile_internal_game'
  ),
  'ALTER TABLE `baishun_game_catalog` ADD KEY `idx_baishun_catalog_profile_internal_game` (`sys_origin`, `profile`, `internal_game_id`, `status`)',
  'SELECT 1'
);
PREPARE add_catalog_profile_index_stmt FROM @add_catalog_profile_index_sql;
EXECUTE add_catalog_profile_index_stmt;
DEALLOCATE PREPARE add_catalog_profile_index_stmt;

SET @add_vendor_ext_profile_sql := IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = @current_schema
      AND table_name = 'sys_game_list_vendor_ext'
      AND column_name = 'profile'
  ),
  'ALTER TABLE `sys_game_list_vendor_ext` ADD COLUMN `profile` varchar(32) NOT NULL DEFAULT ''PROD'' AFTER `sys_origin`',
  'SELECT 1'
);
PREPARE add_vendor_ext_profile_stmt FROM @add_vendor_ext_profile_sql;
EXECUTE add_vendor_ext_profile_stmt;
DEALLOCATE PREPARE add_vendor_ext_profile_stmt;

SET @add_vendor_ext_profile_index_sql := IF(
  NOT EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema = @current_schema
      AND table_name = 'sys_game_list_vendor_ext'
      AND index_name = 'idx_game_vendor_ext_sys_profile_vendor'
  ),
  'ALTER TABLE `sys_game_list_vendor_ext` ADD KEY `idx_game_vendor_ext_sys_profile_vendor` (`sys_origin`, `profile`, `vendor_type`, `vendor_game_id`, `enabled`)',
  'SELECT 1'
);
PREPARE add_vendor_ext_profile_index_stmt FROM @add_vendor_ext_profile_index_sql;
EXECUTE add_vendor_ext_profile_index_stmt;
DEALLOCATE PREPARE add_vendor_ext_profile_index_stmt;