This is an automated email from the ASF dual-hosted git repository.
kirs pushed a commit to branch dev
in repository https://gitbox.apache.org/repos/asf/dolphinscheduler.git
The following commit(s) were added to refs/heads/dev by this push:
new 0e75bd8 [BUG][SQL] fix the process list page load slowly. (#7166)
0e75bd8 is described below
commit 0e75bd8008b62795976919409b79f9137e929a4c
Author: BaoLiang <[email protected]>
AuthorDate: Sat Dec 4 15:53:18 2021 +0800
[BUG][SQL] fix the process list page load slowly. (#7166)
* fix #7601 move ddl to 2.0.1
* fix #7601 move ddl to 2.0.1
---
.../main/resources/sql/dolphinscheduler_mysql.sql | 2 +-
.../2.0.0_schema/mysql/dolphinscheduler_ddl.sql | 2 --
.../postgresql/dolphinscheduler_ddl.sql | 2 --
.../2.0.1-schema/mysql/dolphinscheduler_ddl.sql | 21 ++++++++++++
.../2.0.1-schema/postgre/dolphinscheduler_ddl.sql | 40 ++++++++++++++++++++++
5 files changed, 62 insertions(+), 5 deletions(-)
diff --git
a/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql
b/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql
index aac2429..eae6e18 100644
--- a/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql
+++ b/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql
@@ -600,7 +600,7 @@ CREATE TABLE `t_ds_process_instance` (
`next_process_instance_id` int(11) DEFAULT '0' COMMENT 'serial queue next
processInstanceId',
PRIMARY KEY (`id`),
KEY `process_instance_index` (`process_definition_code`,`id`) USING BTREE,
- KEY `start_time_index` (`start_time`,`end_time`) USING BTREE,
+ KEY `start_time_index` (`start_time`,`end_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- ----------------------------
diff --git
a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.0_schema/mysql/dolphinscheduler_ddl.sql
b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.0_schema/mysql/dolphinscheduler_ddl.sql
index e57e13c..d53a9c1 100644
---
a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.0_schema/mysql/dolphinscheduler_ddl.sql
+++
b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.0_schema/mysql/dolphinscheduler_ddl.sql
@@ -387,8 +387,6 @@ alter table t_ds_process_instance add var_pool longtext
COMMENT 'var_pool' AFTER
alter table t_ds_process_instance add dry_run tinyint(4) DEFAULT '0' COMMENT
'dry run flagļ¼0 normal, 1 dry run' AFTER var_pool;
alter table t_ds_process_instance drop KEY `process_instance_index`;
alter table t_ds_process_instance add KEY `process_instance_index`
(`process_definition_code`,`id`) USING BTREE;
-alter table t_ds_process_instance drop KEY `start_time_index`;
-alter table t_ds_process_instance add KEY `start_time_index`
(`start_time`,`end_time`) USING BTREE;
alter table t_ds_process_instance drop process_instance_json;
alter table t_ds_process_instance drop locations;
alter table t_ds_process_instance drop connects;
diff --git
a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.0_schema/postgresql/dolphinscheduler_ddl.sql
b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.0_schema/postgresql/dolphinscheduler_ddl.sql
index b398684..651dfda 100644
---
a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.0_schema/postgresql/dolphinscheduler_ddl.sql
+++
b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.0_schema/postgresql/dolphinscheduler_ddl.sql
@@ -98,12 +98,10 @@ BEGIN
--- drop index
EXECUTE 'DROP INDEX IF EXISTS "process_instance_index"';
EXECUTE 'DROP INDEX IF EXISTS "task_instance_index"';
- EXECUTE 'DROP INDEX IF EXISTS "start_time_index"';
--- create index
EXECUTE 'CREATE INDEX IF NOT EXISTS priority_id_index ON ' ||
quote_ident(v_schema) ||'.t_ds_command USING
Btree("process_instance_priority","id")';
EXECUTE 'CREATE INDEX IF NOT EXISTS process_instance_index ON ' ||
quote_ident(v_schema) ||'.t_ds_process_instance USING
Btree("process_definition_code","id")';
- EXECUTE 'CREATE INDEX IF NOT EXISTS start_time_index ON ' ||
quote_ident(v_schema) ||'.t_ds_process_instance USING
Btree("start_time","end_time")';
---add comment
EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_user.state is ''state 0:disable 1:enable''';
diff --git
a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.1-schema/mysql/dolphinscheduler_ddl.sql
b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.1-schema/mysql/dolphinscheduler_ddl.sql
new file mode 100644
index 0000000..bbc3d45
--- /dev/null
+++
b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.1-schema/mysql/dolphinscheduler_ddl.sql
@@ -0,0 +1,21 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+*/
+
+SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
+
+alter table t_ds_process_instance drop KEY `start_time_index`;
+alter table t_ds_process_instance add KEY `start_time_index`
(`start_time`,`end_time`) USING BTREE;
diff --git
a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.1-schema/postgre/dolphinscheduler_ddl.sql
b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.1-schema/postgre/dolphinscheduler_ddl.sql
new file mode 100644
index 0000000..97acb02
--- /dev/null
+++
b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.0.1-schema/postgre/dolphinscheduler_ddl.sql
@@ -0,0 +1,40 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+*/
+delimiter d//
+CREATE OR REPLACE FUNCTION public.dolphin_update_metadata(
+ )
+ RETURNS character varying
+ LANGUAGE 'plpgsql'
+ COST 100
+ VOLATILE PARALLEL UNSAFE
+AS $BODY$
+DECLARE
+ v_schema varchar;
+BEGIN
+ ---get schema name
+ v_schema =current_schema();
+
+ EXECUTE 'DROP INDEX IF EXISTS "start_time_index"';
+ EXECUTE 'CREATE INDEX IF NOT EXISTS start_time_index ON ' ||
quote_ident(v_schema) ||'.t_ds_process_instance USING
Btree("start_time","end_time")';
+
+ return 'Success!';
+ exception when others then
+ ---Raise EXCEPTION '(%)',SQLERRM;
+ return SQLERRM;
+END;
+$BODY$;
+d//
\ No newline at end of file
