142 lines
4.4 KiB
SQL
142 lines
4.4 KiB
SQL
SET NAMES utf8mb4;
|
|
|
|
SET @current_schema = DATABASE();
|
|
|
|
SET @ddl = IF(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = @current_schema
|
|
AND TABLE_NAME = 'country_dashboard_period_metric'
|
|
AND COLUMN_NAME = 'stat_timezone'
|
|
),
|
|
'SELECT 1',
|
|
'ALTER TABLE `country_dashboard_period_metric` ADD COLUMN `stat_timezone` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''Asia/Riyadh'' COMMENT ''统计时区'' AFTER `period_key`'
|
|
);
|
|
PREPARE stmt FROM @ddl;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @ddl = IF(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = @current_schema
|
|
AND TABLE_NAME = 'country_dashboard_period_user_metric'
|
|
AND COLUMN_NAME = 'stat_timezone'
|
|
),
|
|
'SELECT 1',
|
|
'ALTER TABLE `country_dashboard_period_user_metric` ADD COLUMN `stat_timezone` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''Asia/Riyadh'' COMMENT ''统计时区'' AFTER `period_key`'
|
|
);
|
|
PREPARE stmt FROM @ddl;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @ddl = IF(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = @current_schema
|
|
AND TABLE_NAME = 'country_dashboard_period_metric'
|
|
AND INDEX_NAME = 'uk_period_origin_country'
|
|
),
|
|
'ALTER TABLE `country_dashboard_period_metric` DROP INDEX `uk_period_origin_country`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt FROM @ddl;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
ALTER TABLE `country_dashboard_period_metric`
|
|
ADD UNIQUE KEY `uk_period_origin_country` (`period_type`, `period_key`, `stat_timezone`, `sys_origin`, `country_code`);
|
|
|
|
SET @ddl = IF(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = @current_schema
|
|
AND TABLE_NAME = 'country_dashboard_period_metric'
|
|
AND INDEX_NAME = 'idx_origin_period'
|
|
),
|
|
'ALTER TABLE `country_dashboard_period_metric` DROP INDEX `idx_origin_period`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt FROM @ddl;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
ALTER TABLE `country_dashboard_period_metric`
|
|
ADD KEY `idx_origin_period` (`sys_origin`, `stat_timezone`, `period_type`, `period_key`);
|
|
|
|
SET @ddl = IF(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = @current_schema
|
|
AND TABLE_NAME = 'country_dashboard_period_metric'
|
|
AND INDEX_NAME = 'idx_period_date'
|
|
),
|
|
'ALTER TABLE `country_dashboard_period_metric` DROP INDEX `idx_period_date`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt FROM @ddl;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
ALTER TABLE `country_dashboard_period_metric`
|
|
ADD KEY `idx_period_date` (`stat_timezone`, `period_type`, `period_start_date`, `period_end_date`);
|
|
|
|
SET @ddl = IF(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = @current_schema
|
|
AND TABLE_NAME = 'country_dashboard_period_user_metric'
|
|
AND INDEX_NAME = 'uk_period_user_metric'
|
|
),
|
|
'ALTER TABLE `country_dashboard_period_user_metric` DROP INDEX `uk_period_user_metric`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt FROM @ddl;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
ALTER TABLE `country_dashboard_period_user_metric`
|
|
ADD UNIQUE KEY `uk_period_user_metric` (`period_type`, `period_key`, `stat_timezone`, `sys_origin`, `country_code`, `user_id`, `metric_type`);
|
|
|
|
SET @ddl = IF(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = @current_schema
|
|
AND TABLE_NAME = 'country_dashboard_period_user_metric'
|
|
AND INDEX_NAME = 'idx_period_metric_country'
|
|
),
|
|
'ALTER TABLE `country_dashboard_period_user_metric` DROP INDEX `idx_period_metric_country`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt FROM @ddl;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
ALTER TABLE `country_dashboard_period_user_metric`
|
|
ADD KEY `idx_period_metric_country` (`period_type`, `period_key`, `stat_timezone`, `sys_origin`, `metric_type`, `country_code`);
|
|
|
|
SET @ddl = IF(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = @current_schema
|
|
AND TABLE_NAME = 'country_dashboard_period_user_metric'
|
|
AND INDEX_NAME = 'idx_origin_user_metric'
|
|
),
|
|
'ALTER TABLE `country_dashboard_period_user_metric` DROP INDEX `idx_origin_user_metric`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt FROM @ddl;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
ALTER TABLE `country_dashboard_period_user_metric`
|
|
ADD KEY `idx_origin_user_metric` (`sys_origin`, `stat_timezone`, `user_id`, `metric_type`);
|