zhongjiajie commented on code in PR #11887:
URL: https://github.com/apache/dolphinscheduler/pull/11887#discussion_r969074721
##########
dolphinscheduler-dao/src/main/resources/sql/upgrade/3.1.1_schema/mysql/dolphinscheduler_ddl.sql:
##########
@@ -16,7 +16,34 @@
*/
-ALTER TABLE `t_ds_worker_group` ADD COLUMN `other_params_json` text DEFAULT
NULL COMMENT 'other params json';
-ALTER TABLE `t_ds_process_instance` ADD COLUMN `state_history` text DEFAULT
NULL COMMENT 'state history desc' AFTER `state`;
+drop procedure if exists add_column_if_not_exists;
+delimiter d//
+create procedure add_column_if_not_exists(target_table_name varchar(256),
target_column varchar(256),
+ add_statement varchar(256))
+begin
+ declare target_database varchar(256);
+ select database() into target_database;
+ IF EXISTS(SELECT *
+ FROM information_schema.COLUMNS
+ WHERE COLUMN_NAME = target_column
+ AND TABLE_NAME = target_table_name
+ ) THEN
+ set @statement = concat('alter table ',target_table_name,' drop column
',target_column);
+ PREPARE STMT FROM @statement;
+ EXECUTE STMT;
+ END IF;
+ set @statement = concat(add_statement);
+ PREPARE STMT FROM @statement;
+ EXECUTE STMT;
+end;
+d//
+delimiter ;
+-- ALTER TABLE `t_ds_worker_group` ADD COLUMN `other_params_json` text DEFAULT
NULL COMMENT 'other params json';
+-- ALTER TABLE `t_ds_process_instance` ADD COLUMN `state_history` text DEFAULT
NULL COMMENT 'state history desc' AFTER `state`;
+
+call add_column_if_not_exists('t_ds_worker_group','other_params_json',"ALTER
TABLE `t_ds_worker_group` ADD COLUMN `other_params_json` text DEFAULT NULL
COMMENT 'other params json'");
+call add_column_if_not_exists('t_ds_process_instance','state_history',"ALTER
TABLE `t_ds_process_instance` ADD COLUMN `state_history` text DEFAULT NULL
COMMENT 'state history desc' AFTER `state`");
Review Comment:
in add column procedure function, we using the whole alert statement `ALTER
TABLE `t_ds_worker_group` ADD COLUMN `other_params_json`, but the variable is
already in previous parameter named `t_ds_worker_group` and
`other_params_json`, I am not sure should we directly using the whole statement
or should we only use the suffix like `text DEFAULT NULL COMMENT 'other params
json'`
##########
dolphinscheduler-dao/src/main/resources/sql/upgrade/3.1.1_schema/mysql/dolphinscheduler_ddl.sql:
##########
@@ -16,7 +16,34 @@
*/
-ALTER TABLE `t_ds_worker_group` ADD COLUMN `other_params_json` text DEFAULT
NULL COMMENT 'other params json';
-ALTER TABLE `t_ds_process_instance` ADD COLUMN `state_history` text DEFAULT
NULL COMMENT 'state history desc' AFTER `state`;
+drop procedure if exists add_column_if_not_exists;
+delimiter d//
+create procedure add_column_if_not_exists(target_table_name varchar(256),
target_column varchar(256),
+ add_statement varchar(256))
+begin
+ declare target_database varchar(256);
+ select database() into target_database;
+ IF EXISTS(SELECT *
+ FROM information_schema.COLUMNS
+ WHERE COLUMN_NAME = target_column
+ AND TABLE_NAME = target_table_name
+ ) THEN
+ set @statement = concat('alter table ',target_table_name,' drop column
',target_column);
+ PREPARE STMT FROM @statement;
+ EXECUTE STMT;
+ END IF;
+ set @statement = concat(add_statement);
+ PREPARE STMT FROM @statement;
+ EXECUTE STMT;
+end;
+d//
+delimiter ;
+-- ALTER TABLE `t_ds_worker_group` ADD COLUMN `other_params_json` text DEFAULT
NULL COMMENT 'other params json';
+-- ALTER TABLE `t_ds_process_instance` ADD COLUMN `state_history` text DEFAULT
NULL COMMENT 'state history desc' AFTER `state`;
+
Review Comment:
also remove those lines
```suggestion
```
##########
dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.3_schema/mysql/dolphinscheduler_ddl.sql:
##########
@@ -15,7 +15,40 @@
* limitations under the License.
*/
+drop procedure if exists re_add_index;
+delimiter d//
+create procedure re_add_index(target_table_name varchar(256),
+ target_index_type varchar(8), target_index_name
varchar(256),
+ target_columns varchar(512), using_str
varchar(256))
+begin
+ declare target_database varchar(256);
+ select database() into target_database;
+ IF EXISTS(SELECT *
+ FROM information_schema.statistics
+ WHERE table_schema = target_database
+ AND table_name = target_table_name
+ AND index_name = target_index_name) THEN
+ set @statement = concat('drop index ', target_index_name, ' on ',
target_table_name);
+ PREPARE STMT FROM @statement;
+ EXECUTE STMT;
+ END IF;
+ set @statement =
+ concat('alter table ', target_table_name, ' add ',
target_index_type, ' ', target_index_name,
+ '(', target_columns,
+ ') ', using_str);
+ PREPARE STMT FROM @statement;
+ EXECUTE STMT;
+end;
+d//
+delimiter ;
+
ALTER TABLE `t_ds_task_instance` MODIFY COLUMN `task_params` longtext COMMENT
'job custom parameters' AFTER `app_link`;
-ALTER TABLE `t_ds_process_task_relation` ADD KEY `idx_code` (`project_code`,
`process_definition_code`) USING BTREE;
-ALTER TABLE `t_ds_process_task_relation_log` ADD KEY
`idx_process_code_version`
(`process_definition_code`,`process_definition_version`) USING BTREE;
-ALTER TABLE `t_ds_task_definition_log` ADD INDEX `idx_code_version`
(`code`,`version`) USING BTREE;
\ No newline at end of file
+# ALTER TABLE `t_ds_process_task_relation` ADD KEY `idx_code` (`project_code`,
`process_definition_code`) USING BTREE;
+# ALTER TABLE `t_ds_process_task_relation_log` ADD KEY
`idx_process_code_version`
(`process_definition_code`,`process_definition_version`) USING BTREE;
+# ALTER TABLE `t_ds_task_definition_log` ADD INDEX `idx_code_version`
(`code`,`version`) USING BTREE;
+
Review Comment:
can we remove those line if we do not need anymore?
```suggestion
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
