platomix-gmetry-bi/sqls/应用复制1226/20241226.sql

-- 1、report_datasource(数据源信息)
    -- 1)、主键修改数据类型为bigint型
    ALTER TABLE `digital_operation_ac`.`report_datasource`
        MODIFY COLUMN id bigint COMMENT '主键ID';

-- 2、cube_data(数据集)
    -- 1)、取消id自增长,去掉原主键
    ALTER TABLE `digital_operation_ac`.`cube_data`
        MODIFY COLUMN id bigint COMMENT '业务ID',
        DROP PRIMARY KEY;
    -- 2)、添加app_id+kid值
    ALTER TABLE `digital_operation_ac`.`cube_data`
        ADD COLUMN `app_id` varchar(128) NULL DEFAULT "000000" COMMENT "应用id" AFTER `id`,
        ADD COLUMN kid bigint NOT NULL COMMENT "主键ID" AFTER `id`;
    -- 3)、更新kid值
    UPDATE `digital_operation_ac`.`cube_data` SET kid = id;
    -- 4)、设置kid为主键,设置业务ID+APPID唯一值;
    ALTER TABLE `digital_operation_ac`.`cube_data` ADD PRIMARY KEY (kid),
        ADD UNIQUE `unq_id_app_id`(`id`, `app_id`);
    -- 5)、修改字段类型
    ALTER TABLE `digital_operation_ac`.`cube_data` MODIFY COLUMN old_cube_data_id bigint COMMENT "被复制的数据集id";
    ALTER TABLE `digital_operation_ac`.`cube_data` MODIFY COLUMN datasource_id bigint COMMENT '数据源ID';

-- 3、cube_table_column(数据集表结构)
    -- 1)、取消id自增长,去掉原主键
    ALTER TABLE `digital_operation_ac`.`cube_table_column`
        MODIFY COLUMN id bigint COMMENT '业务ID',
        DROP PRIMARY KEY;
    -- 2)、添加app_id+kid值
    ALTER TABLE `digital_operation_ac`.`cube_table_column`
        ADD COLUMN `app_id` varchar(128) NULL DEFAULT "000000" COMMENT "应用id" AFTER `id`,
        ADD COLUMN kid bigint NOT NULL COMMENT "主键ID" AFTER `id`;
    -- 3)、更新kid值
    UPDATE `digital_operation_ac`.`cube_table_column` SET kid = id;
    -- 4)、设置kid为主键,设置业务ID+APPID唯一值;
    ALTER TABLE `digital_operation_ac`.`cube_table_column` ADD PRIMARY KEY (kid),
        ADD UNIQUE `unq_id_app_id`(`id`, `app_id`);
    -- 5)、修改字段类型
    ALTER TABLE `digital_operation_ac`.`cube_table_column` MODIFY COLUMN cube_data_id bigint COMMENT '数据集ID';


-- 4、cube_data_permission(数据集权限)
    -- 1)、取消id自增长,去掉原主键
    ALTER TABLE `digital_operation_ac`.`cube_data_permission`
        MODIFY COLUMN id bigint COMMENT '业务ID',
        DROP PRIMARY KEY;
    -- 2)、添加app_id+kid值
    ALTER TABLE `digital_operation_ac`.`cube_data_permission`
        ADD COLUMN `app_id` varchar(128) NULL DEFAULT "000000" COMMENT "应用id" AFTER `id`,
        ADD COLUMN `tenant_id` varchar(12) NULL DEFAULT "000000" COMMENT "租户ID" AFTER `id`,
        ADD COLUMN kid bigint NOT NULL COMMENT "主键ID" AFTER `id`;
    -- 3)、更新kid值
    UPDATE `digital_operation_ac`.`cube_data_permission` SET kid = id;
    -- 4)、设置kid为主键,设置业务ID+APPID唯一值;
    ALTER TABLE `digital_operation_ac`.`cube_data_permission` ADD PRIMARY KEY (kid),
        ADD UNIQUE `unq_id_app_id`(`id`, `app_id`);
    -- 5)、修改字段类型
    ALTER TABLE `digital_operation_ac`.`cube_data_permission` MODIFY COLUMN cube_data_id bigint COMMENT '数据集ID';
    ALTER TABLE `digital_operation_ac`.`cube_data_permission` MODIFY COLUMN data_perm_id bigint COMMENT '权限组ID';


-- 5、report_datasource_permission(数据源权限组)
    -- 1)、取消id自增长,去掉原主键
    ALTER TABLE `digital_operation_ac`.`report_datasource_permission`
        MODIFY COLUMN id bigint COMMENT '业务ID',
        DROP PRIMARY KEY;
    -- 2)、添加app_id+kid值
    ALTER TABLE `digital_operation_ac`.`report_datasource_permission`
        ADD COLUMN `app_id` varchar(128) NULL DEFAULT "000000" COMMENT "应用id" AFTER `id`,
        ADD COLUMN `tenant_id` varchar(12) NULL DEFAULT "000000" COMMENT "租户ID" AFTER `id`,
        ADD COLUMN kid bigint NOT NULL COMMENT "主键ID" AFTER `id`;
    -- 3)、更新kid值
    UPDATE `digital_operation_ac`.`report_datasource_permission` SET kid = id;
    -- 4)、设置kid为主键,设置业务ID+APPID唯一值;
    ALTER TABLE `digital_operation_ac`.`report_datasource_permission` ADD PRIMARY KEY (kid),
        ADD UNIQUE `unq_id_app_id`(`id`, `app_id`);
    -- 5)、修改字段类型
    ALTER TABLE `digital_operation_ac`.`report_datasource_permission` MODIFY COLUMN datasource_id bigint COMMENT '数据源ID';
    ALTER TABLE `digital_operation_ac`.`report_datasource_permission` MODIFY COLUMN data_perm_id bigint COMMENT '权限组ID';


-- 6、cube_table_static(数据集静态数据表)
    -- 1)、取消id自增长,去掉原主键
    ALTER TABLE `digital_operation_ac`.`cube_table_static`
        MODIFY COLUMN id bigint COMMENT '业务ID',
        DROP PRIMARY KEY;
    -- 2)、添加app_id+kid值
    ALTER TABLE `digital_operation_ac`.`cube_table_static`
        ADD COLUMN `app_id` varchar(128) NULL DEFAULT "000000" COMMENT "应用id" AFTER `id`,
        ADD COLUMN kid bigint NOT NULL COMMENT "主键ID" AFTER `id`;
    -- 3)、更新kid值
    UPDATE `digital_operation_ac`.`cube_table_static` SET kid = id;
    -- 4)、设置kid为主键,设置业务ID+APPID唯一值;
    ALTER TABLE `digital_operation_ac`.`cube_table_static` ADD PRIMARY KEY (kid),
        ADD UNIQUE `unq_id_app_id`(`id`, `app_id`);
    -- 5)、修改字段类型
    ALTER TABLE `digital_operation_ac`.`cube_table_static` MODIFY COLUMN cube_data_id bigint COMMENT '数据集ID';
    ALTER TABLE `digital_operation_ac`.`cube_table_static` MODIFY COLUMN datasource_id bigint COMMENT '数据源ID';


-- 7、data_api(API接口)
    -- 5)、修改字段类型
    ALTER TABLE `digital_operation_ac`.`data_api` MODIFY COLUMN datasource_id bigint COMMENT '数据源id';


-- 8、report_table_category(表分类实体类)
    -- 1)、取消id自增长,去掉原主键
    ALTER TABLE `digital_operation_ac`.`report_table_category`
        MODIFY COLUMN id bigint COMMENT '业务ID',
        DROP PRIMARY KEY;
    -- 2)、添加app_id+kid值
    ALTER TABLE `digital_operation_ac`.`report_table_category`
        ADD COLUMN `app_id` varchar(128) NULL DEFAULT "000000" COMMENT "应用id" AFTER `id`,
        ADD COLUMN kid bigint NOT NULL COMMENT "主键ID" AFTER `id`;
    -- 3)、更新kid值
    UPDATE `digital_operation_ac`.`report_table_category` SET kid = id;
    -- 4)、设置kid为主键,设置业务ID+APPID唯一值;
    ALTER TABLE `digital_operation_ac`.`report_table_category` ADD PRIMARY KEY (kid),
        ADD UNIQUE `unq_id_app_id`(`id`, `app_id`);
    -- 5)、修改字段类型
    ALTER TABLE `digital_operation_ac`.`report_table_category` MODIFY COLUMN datasource_id bigint COMMENT '数据源id';


-- 9、report_dimension(报表维度实体类)
    -- 1)、取消id自增长,去掉原主键
    ALTER TABLE `digital_operation_ac`.`report_dimension`
        MODIFY COLUMN id bigint COMMENT '业务ID',
        DROP PRIMARY KEY;
    -- 2)、添加app_id+kid值
    ALTER TABLE `digital_operation_ac`.`report_dimension`
        ADD COLUMN `app_id` varchar(128) NULL DEFAULT "000000" COMMENT "应用id" AFTER `id`,
        ADD COLUMN kid bigint NOT NULL COMMENT "主键ID" AFTER `id`;
    -- 3)、更新kid值
    UPDATE `digital_operation_ac`.`report_dimension` SET kid = id;
    -- 4)、设置kid为主键,设置业务ID+APPID唯一值;
    ALTER TABLE `digital_operation_ac`.`report_dimension` ADD PRIMARY KEY (kid),
        ADD UNIQUE `unq_id_app_id`(`id`, `app_id`);
    -- 5)、修改字段类型
    ALTER TABLE `digital_operation_ac`.`report_dimension` MODIFY COLUMN datasource_id bigint COMMENT '数据源id';


-- 10、cube_table_file(文件表)
    -- 1)、取消id自增长,去掉原主键
    ALTER TABLE `digital_operation_ac`.`cube_table_file`
        MODIFY COLUMN id bigint COMMENT '业务ID',
        DROP PRIMARY KEY;
    -- 2)、添加app_id+kid值
    ALTER TABLE `digital_operation_ac`.`cube_table_file`
        ADD COLUMN `app_id` varchar(128) NULL DEFAULT "000000" COMMENT "应用id" AFTER `id`,
        ADD COLUMN kid bigint NOT NULL COMMENT "主键ID" AFTER `id`;
    -- 3)、更新kid值
    UPDATE `digital_operation_ac`.`cube_table_file` SET kid = id;
    -- 4)、设置kid为主键,设置业务ID+APPID唯一值;
    ALTER TABLE `digital_operation_ac`.`cube_table_file` ADD PRIMARY KEY (kid),
        ADD UNIQUE `unq_id_app_id`(`id`, `app_id`);
    -- 5)、修改字段类型
    ALTER TABLE `digital_operation_ac`.`cube_table_file` MODIFY COLUMN datasource_id bigint COMMENT '数据源ID';


-- 11、cube_sql_view(数据集_SQL视图结构)
    -- 1)、取消id自增长,去掉原主键
    ALTER TABLE `digital_operation_ac`.`cube_sql_view`
        MODIFY COLUMN id bigint COMMENT '业务ID',
        DROP PRIMARY KEY;
    -- 2)、添加app_id+kid值
    ALTER TABLE `digital_operation_ac`.`cube_sql_view`
        ADD COLUMN `app_id` varchar(128) NULL DEFAULT "000000" COMMENT "应用id" AFTER `id`,
        ADD COLUMN kid bigint NOT NULL COMMENT "主键ID" AFTER `id`;
    -- 3)、更新kid值
    UPDATE `digital_operation_ac`.`cube_sql_view` SET kid = id;
    -- 4)、设置kid为主键,设置业务ID+APPID唯一值;
    ALTER TABLE `digital_operation_ac`.`cube_sql_view` ADD PRIMARY KEY (kid),
        ADD UNIQUE `unq_id_app_id`(`id`, `app_id`);
    -- 5)、修改字段类型
    ALTER TABLE `digital_operation_ac`.`cube_sql_view` MODIFY COLUMN datasource_id bigint COMMENT '数据源ID';


-- 12、data_api_config_detail
    -- 5)、修改字段类型
    ALTER TABLE `digital_operation_ac`.`data_api_config_detail` MODIFY COLUMN dimension_id bigint COMMENT '维度id';


-- 13、visual_component
    -- 5)、修改字段类型
    ALTER TABLE `digital_operation_ac`.`visual_component` MODIFY COLUMN datasource_id bigint COMMENT '数据源ID';
    ALTER TABLE `digital_operation_ac`.`visual_component` MODIFY COLUMN cube_data_id bigint COMMENT '数据集ID';

-- 14、blade_log_error添加应用字段
    ALTER TABLE `digital_operation_ac`.`blade_log_error`
        ADD COLUMN `app_id` varchar(128) NULL DEFAULT "000000" COMMENT "应用id" AFTER `id`;

--15、visual_warning
    -- 5)、修改字段类型
    ALTER TABLE `digital_operation_ac`.`visual_warning` MODIFY COLUMN datasource_id bigint COMMENT '数据源ID';
    ALTER TABLE `digital_operation_ac`.`visual_warning` MODIFY COLUMN cube_data_id bigint COMMENT '数据集ID';