AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.357.sql

-- Standard operations are tenant-owned master data. The legacy unique index
-- blocked two tenants from using the same operation/domain code.
SET @std_op_ix_columns := (
  SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA=DATABASE()
    AND TABLE_NAME='StdOpMaster'
    AND INDEX_NAME='IX_StdOpMaster'
);

SET @std_op_ix_sql := CASE
  WHEN @std_op_ix_columns='tenant_id,StdOp,domain' THEN 'SELECT 1'
  WHEN @std_op_ix_columns IS NULL THEN
    'ALTER TABLE `StdOpMaster` ADD UNIQUE INDEX `IX_StdOpMaster` (`tenant_id`,`StdOp`,`domain`)'
  ELSE
    'ALTER TABLE `StdOpMaster` DROP INDEX `IX_StdOpMaster`, ADD UNIQUE INDEX `IX_StdOpMaster` (`tenant_id`,`StdOp`,`domain`)'
END;

PREPARE std_op_ix_stmt FROM @std_op_ix_sql;
EXECUTE std_op_ix_stmt;
DEALLOCATE PREPARE std_op_ix_stmt;

-- S6 source business keys can repeat across tenants.
SET @s6_ix_columns := (
  SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA=DATABASE()
    AND TABLE_NAME='mdp_stg_s6_report'
    AND INDEX_NAME='uk_source_key'
);

SET @s6_ix_sql := CASE
  WHEN @s6_ix_columns='tenant_id,source_system,source_table,source_biz_key' THEN 'SELECT 1'
  WHEN @s6_ix_columns IS NULL THEN
    'ALTER TABLE `mdp_stg_s6_report` ADD UNIQUE INDEX `uk_source_key` (`tenant_id`,`source_system`,`source_table`,`source_biz_key`)'
  ELSE
    'ALTER TABLE `mdp_stg_s6_report` DROP INDEX `uk_source_key`, ADD UNIQUE INDEX `uk_source_key` (`tenant_id`,`source_system`,`source_table`,`source_biz_key`)'
END;

PREPARE s6_ix_stmt FROM @s6_ix_sql;
EXECUTE s6_ix_stmt;
DEALLOCATE PREPARE s6_ix_stmt;