This is an automated email from the ASF dual-hosted git repository.
kirs pushed a commit to branch 2.0.0-release-prepare
in repository https://gitbox.apache.org/repos/asf/dolphinscheduler.git
The following commit(s) were added to refs/heads/2.0.0-release-prepare by this
push:
new 4baa211 cherry-pick 6716 supplement Postgre procedure sql file
(#6749)
4baa211 is described below
commit 4baa211e155db114960d1788540f27132381a5e5
Author: OS <[email protected]>
AuthorDate: Tue Nov 9 10:57:31 2021 +0800
cherry-pick 6716 supplement Postgre procedure sql file (#6749)
Co-authored-by: GaoTianDuo <[email protected]>
---
.../postgresql/dolphinscheduler_ddl.sql | 685 +++++++++------------
.../postgresql/dolphinscheduler_ddl_post.sql | 10 +
2 files changed, 297 insertions(+), 398 deletions(-)
diff --git a/sql/upgrade/2.0.0_schema/postgresql/dolphinscheduler_ddl.sql
b/sql/upgrade/2.0.0_schema/postgresql/dolphinscheduler_ddl.sql
index 663148a..e68781b 100644
--- a/sql/upgrade/2.0.0_schema/postgresql/dolphinscheduler_ddl.sql
+++ b/sql/upgrade/2.0.0_schema/postgresql/dolphinscheduler_ddl.sql
@@ -15,404 +15,293 @@
* limitations under the License.
*/
--- uc_dolphin_T_t_ds_user_A_state
-delimiter ;
-DROP FUNCTION IF EXISTS uc_dolphin_T_t_ds_user_A_state();
-delimiter d//
-CREATE FUNCTION uc_dolphin_T_t_ds_user_A_state() RETURNS void AS $$
+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
- IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS
- WHERE TABLE_CATALOG=current_database()
- AND TABLE_SCHEMA=current_schema()
- AND TABLE_NAME='t_ds_user'
- AND COLUMN_NAME ='state')
- THEN
- ALTER TABLE t_ds_user ADD COLUMN state int DEFAULT 1;
- comment on column t_ds_user.state is 'state 0:disable 1:enable';
- END IF;
+ ---get schema name
+ v_schema =current_schema();
+
+ --- rename columns
+ EXECUTE 'ALTER TABLE IF EXISTS ' || quote_ident(v_schema)
||'.t_ds_command RENAME COLUMN process_definition_id to
process_definition_code';
+ EXECUTE 'ALTER TABLE IF EXISTS ' || quote_ident(v_schema)
||'.t_ds_error_command RENAME COLUMN process_definition_id to
process_definition_code';
+ EXECUTE 'ALTER TABLE IF EXISTS ' || quote_ident(v_schema)
||'.t_ds_process_instance RENAME COLUMN process_definition_id to
process_definition_code';
+ EXECUTE 'ALTER TABLE IF EXISTS ' || quote_ident(v_schema)
||'.t_ds_task_instance RENAME COLUMN process_definition_id to task_code';
+ EXECUTE 'ALTER TABLE IF EXISTS ' || quote_ident(v_schema)
||'.t_ds_schedules RENAME COLUMN process_definition_id to
process_definition_code';
+ EXECUTE 'ALTER TABLE IF EXISTS ' || quote_ident(v_schema)
||'.t_ds_process_definition RENAME COLUMN project_id to project_code';
+
+ --- alter column type
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_command ALTER
COLUMN process_definition_code TYPE bigint';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_error_command
ALTER COLUMN process_definition_code TYPE bigint';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'.t_ds_process_instance ALTER COLUMN process_definition_code TYPE bigint';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_task_instance
ALTER COLUMN task_code TYPE bigint';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_schedules
ALTER COLUMN process_definition_code TYPE bigint';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'.t_ds_process_definition ALTER COLUMN project_code TYPE bigint';
+
+ --- add columns
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_user ADD COLUMN
IF NOT EXISTS "state" int DEFAULT 1';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_alertgroup ADD
COLUMN IF NOT EXISTS "alert_instance_ids" varchar(255) DEFAULT NULL';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_alertgroup ADD
COLUMN IF NOT EXISTS "create_user_id" int4 DEFAULT NULL';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_project ADD
COLUMN IF NOT EXISTS "code" bigint NOT NULL';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_command ADD
COLUMN IF NOT EXISTS "environment_code" bigint DEFAULT -1';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_command ADD
COLUMN IF NOT EXISTS "dry_run" int DEFAULT 0';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_command ADD
COLUMN IF NOT EXISTS "process_definition_version" int DEFAULT 0';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_command ADD
COLUMN IF NOT EXISTS "process_instance_id" int DEFAULT 0';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_error_command
ADD COLUMN IF NOT EXISTS "environment_code" bigint DEFAULT -1';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_error_command
ADD COLUMN IF NOT EXISTS "dry_run" int DEFAULT 0';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_error_command
ADD COLUMN IF NOT EXISTS "process_definition_version" int DEFAULT 0';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_error_command
ADD COLUMN IF NOT EXISTS "process_instance_id" int DEFAULT 0';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'.t_ds_process_instance ADD COLUMN IF NOT EXISTS "process_definition_version"
int DEFAULT 0';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'.t_ds_process_instance ADD COLUMN IF NOT EXISTS "environment_code" bigint
DEFAULT -1';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'.t_ds_process_instance ADD COLUMN IF NOT EXISTS "var_pool" text';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'.t_ds_process_instance ADD COLUMN IF NOT EXISTS "dry_run" int DEFAULT 0';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_task_instance
ADD COLUMN IF NOT EXISTS "task_definition_version" int DEFAULT 0';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_task_instance
ADD COLUMN IF NOT EXISTS "task_params" text';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_task_instance
ADD COLUMN IF NOT EXISTS "environment_code" bigint DEFAULT -1';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_task_instance
ADD COLUMN IF NOT EXISTS "environment_config" text';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_task_instance
ADD COLUMN IF NOT EXISTS "first_submit_time" timestamp DEFAULT NULL';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_task_instance
ADD COLUMN IF NOT EXISTS "delay_time" int DEFAULT 0';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_task_instance
ADD COLUMN IF NOT EXISTS "var_pool" text';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_task_instance
ADD COLUMN IF NOT EXISTS "dry_run" int DEFAULT 0';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_schedules ADD
COLUMN IF NOT EXISTS "timezone_id" varchar(40) DEFAULT NULL';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_schedules ADD
COLUMN IF NOT EXISTS "environment_code" int DEFAULT -1';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'.t_ds_process_definition ADD COLUMN IF NOT EXISTS "code" bigint';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'.t_ds_process_definition ADD COLUMN IF NOT EXISTS "warning_group_id" int';
+
+ ---drop columns
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_tenant DROP
COLUMN IF EXISTS "tenant_name"';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'.t_ds_process_instance DROP COLUMN IF EXISTS "process_instance_json"';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'.t_ds_process_instance DROP COLUMN IF EXISTS "locations"';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'.t_ds_process_instance DROP COLUMN IF EXISTS "connects"';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'.t_ds_process_instance DROP COLUMN IF EXISTS "dependence_schedule_times"';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_task_instance
DROP COLUMN IF EXISTS "task_json"';
+
+ -- add CONSTRAINT
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'."t_ds_alertgroup"
ADD CONSTRAINT "t_ds_alertgroup_name_un" UNIQUE ("group_name")';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'."t_ds_datasource"
ADD CONSTRAINT "t_ds_datasource_name_un" UNIQUE ("name","type")';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'."t_ds_command"
ALTER COLUMN "process_definition_code" SET NOT NULL';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'."t_ds_process_instance" ALTER COLUMN "process_definition_code" SET NOT
NULL';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'."t_ds_task_instance" ALTER COLUMN "task_code" SET NOT NULL';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'."t_ds_schedules"
ALTER COLUMN "process_definition_code" SET NOT NULL';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'."t_ds_process_definition" ALTER COLUMN "code" SET NOT NULL';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'."t_ds_process_definition" ALTER COLUMN "project_code" SET NOT NULL';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'."t_ds_process_definition" ADD CONSTRAINT "process_unique" UNIQUE
("name","project_code")';
+ EXECUTE 'ALTER TABLE ' || quote_ident(v_schema)
||'."t_ds_process_definition" ALTER COLUMN "description" SET NOT NULL';
+
+ --- drop index
+ EXECUTE 'DROP INDEX IF EXISTS "process_instance_index"';
+ EXECUTE 'DROP INDEX IF EXISTS "task_instance_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")';
+
+
+ ---add comment
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_user.state is ''state 0:disable 1:enable''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_alertgroup.alert_instance_ids is ''alert instance ids''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_alertgroup.create_user_id is ''create user id''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_project.code is ''coding''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_command.process_definition_code is ''process definition code''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_command.environment_code is ''environment code''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_command.dry_run is ''dry run flag：0 normal, 1 dry run''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_command.process_definition_version is ''process definition version''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_command.process_instance_id is ''process instance id''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_error_command.process_definition_code is ''process definition code''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_error_command.environment_code is ''environment code''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_error_command.dry_run is ''dry run flag：0 normal, 1 dry run''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_error_command.process_definition_version is ''process definition
version''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_error_command.process_instance_id is ''process instance id''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_process_instance.process_definition_code is ''process instance code''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_process_instance.process_definition_version is ''process instance
version''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_process_instance.environment_code is ''environment code''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_process_instance.var_pool is ''var pool''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_process_instance.dry_run is ''dry run flag：0 normal, 1 dry run''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_task_instance.task_code is ''task definition code''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_task_instance.task_definition_version is ''task definition version''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_task_instance.task_params is ''task params''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_task_instance.environment_code is ''environment code''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_task_instance.environment_config is ''this config contains many
environment variables config''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_task_instance.first_submit_time is ''task first submit time''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_task_instance.delay_time is ''task delay execution time''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_task_instance.var_pool is ''var pool''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_task_instance.dry_run is ''dry run flag：0 normal, 1 dry run''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_schedules.process_definition_code is ''process definition code''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_schedules.timezone_id is ''timezone id''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_schedules.environment_code is ''environment code''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_process_definition.code is ''encoding''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_process_definition.project_code is ''project code''';
+ EXECUTE 'comment on column ' || quote_ident(v_schema)
||'.t_ds_process_definition.warning_group_id is ''alert group id''';
+
+ --create table
+ EXECUTE 'CREATE TABLE IF NOT EXISTS '|| quote_ident(v_schema)
||'."t_ds_plugin_define" (
+ id serial NOT NULL,
+ plugin_name varchar(100) NOT NULL,
+ plugin_type varchar(100) NOT NULL,
+ plugin_params text NULL,
+ create_time timestamp NULL,
+ update_time timestamp NULL,
+ CONSTRAINT t_ds_plugin_define_pk PRIMARY KEY (id),
+ CONSTRAINT t_ds_plugin_define_un UNIQUE (plugin_name, plugin_type)
+ )';
+
+ EXECUTE 'CREATE TABLE IF NOT EXISTS '|| quote_ident(v_schema)
||'."t_ds_alert_plugin_instance" (
+ id serial NOT NULL,
+ plugin_define_id int4 NOT NULL,
+ plugin_instance_params text NULL,
+ create_time timestamp NULL,
+ update_time timestamp NULL,
+ instance_name varchar(200) NULL,
+ CONSTRAINT t_ds_alert_plugin_instance_pk PRIMARY KEY (id)
+ )';
+
+ EXECUTE 'CREATE TABLE IF NOT EXISTS '|| quote_ident(v_schema)
||'."t_ds_environment" (
+ id serial NOT NULL,
+ code bigint NOT NULL,
+ name varchar(100) DEFAULT NULL,
+ config text DEFAULT NULL,
+ description text,
+ operator int DEFAULT NULL,
+ create_time timestamp DEFAULT NULL,
+ update_time timestamp DEFAULT NULL,
+ PRIMARY KEY (id),
+ CONSTRAINT environment_name_unique UNIQUE (name),
+ CONSTRAINT environment_code_unique UNIQUE (code)
+ )';
+
+ EXECUTE 'CREATE TABLE IF NOT EXISTS '|| quote_ident(v_schema)
||'."t_ds_environment_worker_group_relation" (
+ id serial NOT NULL,
+ environment_code bigint NOT NULL,
+ worker_group varchar(255) NOT NULL,
+ operator int DEFAULT NULL,
+ create_time timestamp DEFAULT NULL,
+ update_time timestamp DEFAULT NULL,
+ PRIMARY KEY (id) ,
+ CONSTRAINT environment_worker_group_unique UNIQUE
(environment_code,worker_group)
+ )';
+
+ EXECUTE 'CREATE TABLE IF NOT EXISTS '|| quote_ident(v_schema)
||'."t_ds_process_definition_log" (
+ id int NOT NULL ,
+ code bigint NOT NULL,
+ name varchar(255) DEFAULT NULL ,
+ version int NOT NULL ,
+ description text ,
+ project_code bigint DEFAULT NULL ,
+ release_state int DEFAULT NULL ,
+ user_id int DEFAULT NULL ,
+ global_params text ,
+ locations text ,
+ warning_group_id int DEFAULT NULL ,
+ flag int DEFAULT NULL ,
+ timeout int DEFAULT 0 ,
+ tenant_id int DEFAULT -1 ,
+ execution_type int DEFAULT 0,
+ operator int DEFAULT NULL ,
+ operate_time timestamp DEFAULT NULL ,
+ create_time timestamp DEFAULT NULL ,
+ update_time timestamp DEFAULT NULL ,
+ PRIMARY KEY (id)
+ )';
+
+ EXECUTE 'CREATE TABLE IF NOT EXISTS '|| quote_ident(v_schema)
||'."t_ds_task_definition" (
+ id int NOT NULL ,
+ code bigint NOT NULL,
+ name varchar(255) DEFAULT NULL ,
+ version int NOT NULL ,
+ description text ,
+ project_code bigint DEFAULT NULL ,
+ user_id int DEFAULT NULL ,
+ task_type varchar(50) DEFAULT NULL ,
+ task_params text ,
+ flag int DEFAULT NULL ,
+ task_priority int DEFAULT NULL ,
+ worker_group varchar(255) DEFAULT NULL ,
+ environment_code bigint DEFAULT -1,
+ fail_retry_times int DEFAULT NULL ,
+ fail_retry_interval int DEFAULT NULL ,
+ timeout_flag int DEFAULT NULL ,
+ timeout_notify_strategy int DEFAULT NULL ,
+ timeout int DEFAULT 0 ,
+ delay_time int DEFAULT 0 ,
+ resource_ids text ,
+ create_time timestamp DEFAULT NULL ,
+ update_time timestamp DEFAULT NULL ,
+ PRIMARY KEY (id)
+ )';
+
+ EXECUTE 'CREATE TABLE IF NOT EXISTS '|| quote_ident(v_schema)
||'."t_ds_task_definition_log" (
+ id int NOT NULL ,
+ code bigint NOT NULL,
+ name varchar(255) DEFAULT NULL ,
+ version int NOT NULL ,
+ description text ,
+ project_code bigint DEFAULT NULL ,
+ user_id int DEFAULT NULL ,
+ task_type varchar(50) DEFAULT NULL ,
+ task_params text ,
+ flag int DEFAULT NULL ,
+ task_priority int DEFAULT NULL ,
+ worker_group varchar(255) DEFAULT NULL ,
+ environment_code bigint DEFAULT -1,
+ fail_retry_times int DEFAULT NULL ,
+ fail_retry_interval int DEFAULT NULL ,
+ timeout_flag int DEFAULT NULL ,
+ timeout_notify_strategy int DEFAULT NULL ,
+ timeout int DEFAULT 0 ,
+ delay_time int DEFAULT 0 ,
+ resource_ids text ,
+ operator int DEFAULT NULL ,
+ operate_time timestamp DEFAULT NULL ,
+ create_time timestamp DEFAULT NULL ,
+ update_time timestamp DEFAULT NULL ,
+ PRIMARY KEY (id)
+ )';
+
+ EXECUTE 'CREATE TABLE IF NOT EXISTS '|| quote_ident(v_schema)
||'."t_ds_process_task_relation" (
+ id int NOT NULL ,
+ name varchar(255) DEFAULT NULL ,
+ project_code bigint DEFAULT NULL ,
+ process_definition_code bigint DEFAULT NULL ,
+ process_definition_version int DEFAULT NULL ,
+ pre_task_code bigint DEFAULT NULL ,
+ pre_task_version int DEFAULT 0 ,
+ post_task_code bigint DEFAULT NULL ,
+ post_task_version int DEFAULT 0 ,
+ condition_type int DEFAULT NULL ,
+ condition_params text ,
+ create_time timestamp DEFAULT NULL ,
+ update_time timestamp DEFAULT NULL ,
+ PRIMARY KEY (id)
+ )';
+
+ EXECUTE 'CREATE TABLE IF NOT EXISTS '|| quote_ident(v_schema)
||'."t_ds_process_task_relation_log" (
+ id int NOT NULL ,
+ name varchar(255) DEFAULT NULL ,
+ project_code bigint DEFAULT NULL ,
+ process_definition_code bigint DEFAULT NULL ,
+ process_definition_version int DEFAULT NULL ,
+ pre_task_code bigint DEFAULT NULL ,
+ pre_task_version int DEFAULT 0 ,
+ post_task_code bigint DEFAULT NULL ,
+ post_task_version int DEFAULT 0 ,
+ condition_type int DEFAULT NULL ,
+ condition_params text ,
+ operator int DEFAULT NULL ,
+ operate_time timestamp DEFAULT NULL ,
+ create_time timestamp DEFAULT NULL ,
+ update_time timestamp DEFAULT NULL ,
+ PRIMARY KEY (id)
+ )';
+ return 'Success!';
+ exception when others then
+ ---Raise EXCEPTION '(%)',SQLERRM;
+ return SQLERRM;
END;
-$$ LANGUAGE plpgsql;
-d//
-delimiter ;
-select uc_dolphin_T_t_ds_user_A_state();
-DROP FUNCTION uc_dolphin_T_t_ds_user_A_state();
+$BODY$;
--- uc_dolphin_T_t_ds_tenant_A_tenant_name
-delimiter ;
-DROP FUNCTION IF EXISTS uc_dolphin_T_t_ds_tenant_A_tenant_name();
-delimiter d//
-CREATE FUNCTION uc_dolphin_T_t_ds_tenant_A_tenant_name() RETURNS void AS $$
-BEGIN
- IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
- WHERE TABLE_CATALOG=current_database()
- AND TABLE_SCHEMA=current_schema()
- AND TABLE_NAME='t_ds_tenant'
- AND COLUMN_NAME ='tenant_name')
- THEN
- ALTER TABLE t_ds_tenant DROP COLUMN "tenant_name";
- END IF;
-END;
-$$ LANGUAGE plpgsql;
-d//
-delimiter ;
-select uc_dolphin_T_t_ds_tenant_A_tenant_name();
-DROP FUNCTION uc_dolphin_T_t_ds_tenant_A_tenant_name();
-
--- uc_dolphin_T_t_ds_task_instance_A_first_submit_time
-delimiter d//
-CREATE OR REPLACE FUNCTION
uc_dolphin_T_t_ds_task_instance_A_first_submit_time() RETURNS void AS $$
-BEGIN
- IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS
- WHERE TABLE_NAME='t_ds_task_instance'
- AND COLUMN_NAME ='first_submit_time')
- THEN
- ALTER TABLE t_ds_task_instance ADD COLUMN first_submit_time timestamp
DEFAULT NULL;
- END IF;
-END;
-$$ LANGUAGE plpgsql;
-d//
-
-delimiter ;
-SELECT uc_dolphin_T_t_ds_task_instance_A_first_submit_time();
-DROP FUNCTION IF EXISTS uc_dolphin_T_t_ds_task_instance_A_first_submit_time();
-
--- uc_dolphin_T_t_ds_task_instance_A_delay_time
-delimiter d//
-CREATE OR REPLACE FUNCTION uc_dolphin_T_t_ds_task_instance_A_delay_time()
RETURNS void AS $$
-BEGIN
- IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS
- WHERE TABLE_NAME='t_ds_task_instance'
- AND COLUMN_NAME ='delay_time')
- THEN
- ALTER TABLE t_ds_task_instance ADD COLUMN delay_time int DEFAULT '0';
- END IF;
-END;
-$$ LANGUAGE plpgsql;
-d//
-
-delimiter ;
-SELECT uc_dolphin_T_t_ds_task_instance_A_delay_time();
-DROP FUNCTION IF EXISTS uc_dolphin_T_t_ds_task_instance_A_delay_time();
-
--- uc_dolphin_T_t_ds_task_instance_A_var_pool
-delimiter d//
-CREATE OR REPLACE FUNCTION uc_dolphin_T_t_ds_task_instance_A_var_pool()
RETURNS void AS $$
-BEGIN
- IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS
- WHERE TABLE_NAME='t_ds_task_instance'
- AND COLUMN_NAME ='var_pool')
- THEN
- ALTER TABLE t_ds_task_instance ADD COLUMN var_pool text;
- END IF;
-END;
-$$ LANGUAGE plpgsql;
-d//
-
-delimiter ;
-SELECT uc_dolphin_T_t_ds_task_instance_A_var_pool();
-DROP FUNCTION IF EXISTS uc_dolphin_T_t_ds_task_instance_A_var_pool();
-
--- uc_dolphin_T_t_ds_process_instance_A_var_pool
-delimiter d//
-CREATE OR REPLACE FUNCTION uc_dolphin_T_t_ds_process_instance_A_var_pool()
RETURNS void AS $$
-BEGIN
- IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS
- WHERE TABLE_NAME='t_ds_process_instance'
- AND COLUMN_NAME ='var_pool')
- THEN
- ALTER TABLE t_ds_process_instance ADD COLUMN var_pool text;
- END IF;
-END;
-$$ LANGUAGE plpgsql;
-d//
-
-delimiter ;
-SELECT uc_dolphin_T_t_ds_process_instance_A_var_pool();
-DROP FUNCTION IF EXISTS uc_dolphin_T_t_ds_process_instance_A_var_pool();
-
--- uc_dolphin_T_t_ds_process_definition_A_modify_by
-delimiter d//
-CREATE OR REPLACE FUNCTION ct_dolphin_T_t_ds_process_definition_version()
RETURNS void AS $$
-BEGIN
- CREATE TABLE IF NOT EXISTS t_ds_process_definition_version (
- id int NOT NULL ,
- process_definition_id int NOT NULL ,
- version int DEFAULT NULL ,
- process_definition_json text ,
- description text ,
- global_params text ,
- locations text ,
- connects text ,
- receivers text ,
- receivers_cc text ,
- create_time timestamp DEFAULT NULL ,
- timeout int DEFAULT '0' ,
- resource_ids varchar(64),
- PRIMARY KEY (id)
- ) ;
- create index process_definition_id_and_version on
t_ds_process_definition_version (process_definition_id,version);
-
- DROP SEQUENCE IF EXISTS t_ds_process_definition_version_id_sequence;
- CREATE SEQUENCE t_ds_process_definition_version_id_sequence;
- ALTER TABLE t_ds_process_definition_version ALTER COLUMN id SET DEFAULT
NEXTVAL('t_ds_process_definition_version_id_sequence');
-END;
-$$ LANGUAGE plpgsql;
-d//
-
-delimiter ;
-SELECT ct_dolphin_T_t_ds_process_definition_version();
-DROP FUNCTION IF EXISTS ct_dolphin_T_t_ds_process_definition_version();
-
--- ----------------------------
--- Table structure for t_ds_plugin_define
--- ----------------------------
-DROP TABLE IF EXISTS t_ds_plugin_define;
-CREATE TABLE t_ds_plugin_define (
- id serial NOT NULL,
- plugin_name varchar(100) NOT NULL,
- plugin_type varchar(100) NOT NULL,
- plugin_params text NULL,
- create_time timestamp NULL,
- update_time timestamp NULL,
- CONSTRAINT t_ds_plugin_define_pk PRIMARY KEY (id),
- CONSTRAINT t_ds_plugin_define_un UNIQUE (plugin_name, plugin_type)
-);
-
--- ----------------------------
--- Table structure for t_ds_alert_plugin_instance
--- ----------------------------
-DROP TABLE IF EXISTS t_ds_alert_plugin_instance;
-CREATE TABLE t_ds_alert_plugin_instance (
- id serial NOT NULL,
- plugin_define_id int4 NOT NULL,
- plugin_instance_params text NULL,
- create_time timestamp NULL,
- update_time timestamp NULL,
- instance_name varchar(200) NULL,
- CONSTRAINT t_ds_alert_plugin_instance_pk PRIMARY KEY (id)
-);
-
--- uc_dolphin_T_t_ds_process_definition_A_warning_group_id
-delimiter d//
-CREATE OR REPLACE FUNCTION
uc_dolphin_T_t_ds_process_definition_A_warning_group_id() RETURNS void AS $$
-BEGIN
- IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS
- WHERE TABLE_NAME='t_ds_process_definition'
- AND COLUMN_NAME ='warning_group_id')
- THEN
- ALTER TABLE t_ds_process_definition ADD COLUMN warning_group_id int4
DEFAULT NULL;
- COMMENT ON COLUMN t_ds_process_definition.warning_group_id IS 'alert
group id';
- END IF;
-END;
-$$ LANGUAGE plpgsql;
-d//
-
-delimiter ;
-SELECT uc_dolphin_T_t_ds_process_definition_A_warning_group_id();
-DROP FUNCTION IF EXISTS
uc_dolphin_T_t_ds_process_definition_A_warning_group_id();
-
--- uc_dolphin_T_t_ds_process_definition_version_A_warning_group_id
-delimiter d//
-CREATE OR REPLACE FUNCTION
uc_dolphin_T_t_ds_process_definition_version_A_warning_group_id() RETURNS void
AS $$
-BEGIN
- IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS
- WHERE TABLE_NAME='t_ds_process_definition_version'
- AND COLUMN_NAME ='warning_group_id')
- THEN
- ALTER TABLE t_ds_process_definition_version ADD COLUMN
warning_group_id int4 DEFAULT NULL;
- COMMENT ON COLUMN t_ds_process_definition_version.warning_group_id IS
'alert group id';
- END IF;
-END;
-$$ LANGUAGE plpgsql;
-d//
-
-delimiter ;
-SELECT uc_dolphin_T_t_ds_process_definition_version_A_warning_group_id();
-DROP FUNCTION IF EXISTS
uc_dolphin_T_t_ds_process_definition_version_A_warning_group_id();
-
--- uc_dolphin_T_t_ds_alertgroup_A_alert_instance_ids
-delimiter d//
-CREATE OR REPLACE FUNCTION uc_dolphin_T_t_ds_alertgroup_A_alert_instance_ids()
RETURNS void AS $$
-BEGIN
- IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS
- WHERE TABLE_NAME='t_ds_alertgroup'
- AND COLUMN_NAME ='alert_instance_ids')
- THEN
- ALTER TABLE t_ds_alertgroup ADD COLUMN alert_instance_ids varchar
(255) DEFAULT NULL;
- COMMENT ON COLUMN t_ds_alertgroup.alert_instance_ids IS 'alert
instance ids';
- END IF;
-END;
-$$ LANGUAGE plpgsql;
-d//
-
-delimiter ;
-SELECT uc_dolphin_T_t_ds_alertgroup_A_alert_instance_ids();
-DROP FUNCTION IF EXISTS uc_dolphin_T_t_ds_alertgroup_A_alert_instance_ids();
-
--- uc_dolphin_T_t_ds_alertgroup_A_create_user_id
-delimiter d//
-CREATE OR REPLACE FUNCTION uc_dolphin_T_t_ds_alertgroup_A_create_user_id()
RETURNS void AS $$
-BEGIN
- IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS
- WHERE TABLE_NAME='t_ds_alertgroup'
- AND COLUMN_NAME ='create_user_id')
- THEN
- ALTER TABLE t_ds_alertgroup ADD COLUMN create_user_id int4 DEFAULT
NULL;
- COMMENT ON COLUMN t_ds_alertgroup.create_user_id IS 'create user id';
- END IF;
-END;
-$$ LANGUAGE plpgsql;
-d//
-
-delimiter ;
-SELECT uc_dolphin_T_t_ds_alertgroup_A_create_user_id();
-DROP FUNCTION IF EXISTS uc_dolphin_T_t_ds_alertgroup_A_create_user_id();
-
--- uc_dolphin_T_t_ds_alertgroup_A_add_UN_groupName
-delimiter d//
-CREATE OR REPLACE FUNCTION uc_dolphin_T_t_ds_alertgroup_A_add_UN_groupName()
RETURNS void AS $$
-BEGIN
- IF NOT EXISTS (SELECT 1 FROM pg_stat_all_indexes
- WHERE relname='t_ds_alertgroup'
- AND indexrelname ='t_ds_alertgroup_name_un')
- THEN
- ALTER TABLE t_ds_alertgroup ADD CONSTRAINT t_ds_alertgroup_name_un
UNIQUE (group_name);
- END IF;
-END;
-$$ LANGUAGE plpgsql;
-d//
-
-delimiter ;
-SELECT uc_dolphin_T_t_ds_alertgroup_A_add_UN_groupName();
-DROP FUNCTION IF EXISTS uc_dolphin_T_t_ds_alertgroup_A_add_UN_groupName();
-
--- uc_dolphin_T_t_ds_datasource_A_add_UN_datasourceName
-delimiter d//
-CREATE OR REPLACE FUNCTION
uc_dolphin_T_t_ds_datasource_A_add_UN_datasourceName() RETURNS void AS $$
-BEGIN
- IF NOT EXISTS (SELECT 1 FROM pg_stat_all_indexes
- WHERE relname='t_ds_datasource'
- AND indexrelname ='t_ds_datasource_name_un')
- THEN
- ALTER TABLE t_ds_datasource ADD CONSTRAINT t_ds_datasource_name_un
UNIQUE (name, type);
- END IF;
-END;
-$$ LANGUAGE plpgsql;
-d//
-
-delimiter ;
-SELECT uc_dolphin_T_t_ds_datasource_A_add_UN_datasourceName();
-DROP FUNCTION IF EXISTS uc_dolphin_T_t_ds_datasource_A_add_UN_datasourceName();
-
--- uc_dolphin_T_t_ds_schedules_A_add_timezone
-delimiter d//
-CREATE OR REPLACE FUNCTION uc_dolphin_T_t_ds_schedules_A_add_timezone()
RETURNS void AS $$
-BEGIN
- IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS
- WHERE TABLE_NAME='t_ds_schedules'
- AND COLUMN_NAME ='timezone_id')
- THEN
-ALTER TABLE t_ds_schedules ADD COLUMN timezone_id varchar(40) DEFAULT NULL;
-END IF;
-END;
-$$ LANGUAGE plpgsql;
-d//
-
-delimiter ;
-SELECT uc_dolphin_T_t_ds_schedules_A_add_timezone();
-DROP FUNCTION IF EXISTS uc_dolphin_T_t_ds_schedules_A_add_timezone();
-
---
--- Table structure for table t_ds_environment
---
-DROP TABLE IF EXISTS t_ds_environment;
-CREATE TABLE t_ds_environment (
- id serial NOT NULL ,
- code bigint NOT NULL,
- name varchar(100) DEFAULT NULL ,
- config text DEFAULT NULL ,
- description text ,
- operator int DEFAULT NULL ,
- create_time timestamp DEFAULT NULL ,
- update_time timestamp DEFAULT NULL ,
- PRIMARY KEY (id) ,
- CONSTRAINT environment_name_unique UNIQUE (name),
- CONSTRAINT environment_code_unique UNIQUE (code)
-);
-
-ALTER TABLE t_ds_task_definition ADD COLUMN environment_code bigint DEFAULT
'-1';
-comment on column t_ds_task_definition.environment_code is 'environment code';
-
-ALTER TABLE t_ds_task_definition_log ADD COLUMN environment_code bigint
DEFAULT '-1';
-comment on column t_ds_task_definition_log.environment_code is 'environment
code';
-
-ALTER TABLE t_ds_command ADD COLUMN environment_code bigint DEFAULT '-1';
-comment on column t_ds_command.environment_code is 'environment code';
-
-ALTER TABLE t_ds_error_command ADD COLUMN environment_code bigint DEFAULT '-1';
-comment on column t_ds_error_command.environment_code is 'environment code';
-
-ALTER TABLE t_ds_schedules ADD COLUMN environment_code bigint DEFAULT '-1';
-comment on column t_ds_schedules.environment_code is 'environment code';
-
-ALTER TABLE t_ds_process_instance ADD COLUMN environment_code bigint DEFAULT
'-1';
-comment on column t_ds_process_instance.environment_code is 'environment code';
-
-ALTER TABLE t_ds_task_instance ADD COLUMN environment_code bigint DEFAULT '-1';
-comment on column t_ds_task_instance.environment_code is 'environment code';
-
-ALTER TABLE t_ds_task_instance ADD COLUMN environment_config text;
-comment on column t_ds_task_instance.environment_config is 'environment
config';
-
---
--- Table structure for table t_ds_environment_worker_group_relation
---
-DROP TABLE IF EXISTS t_ds_environment_worker_group_relation;
-CREATE TABLE t_ds_environment_worker_group_relation (
- id serial NOT NULL,
- environment_code bigint NOT NULL,
- worker_group varchar(255) NOT NULL,
- operator int DEFAULT NULL,
- create_time timestamp DEFAULT NULL,
- update_time timestamp DEFAULT NULL,
- PRIMARY KEY (id) ,
- CONSTRAINT environment_worker_group_unique UNIQUE
(environment_code,worker_group)
-);
-
-ALTER TABLE t_ds_task_definition ALTER COLUMN resource_ids TYPE text;
-ALTER TABLE t_ds_task_definition_log ALTER COLUMN resource_ids TYPE text;
-
--- uc_dolphin_T_t_ds_task_definition_A_drop_UN_taskName
-delimiter d//
-CREATE OR REPLACE FUNCTION
uc_dolphin_T_t_ds_task_definition_A_drop_UN_taskName() RETURNS void AS $$
-BEGIN
- IF EXISTS (SELECT 1 FROM pg_stat_all_indexes
- WHERE relname='t_ds_task_definition'
- AND indexrelname ='task_definition_unique')
- ALTER TABLE t_ds_task_definition drop CONSTRAINT
task_definition_unique;
- END IF;
-END;
-$$ LANGUAGE plpgsql;
-d//
-
-delimiter ;
-SELECT uc_dolphin_T_t_ds_task_definition_A_drop_UN_taskName();
-DROP FUNCTION IF EXISTS uc_dolphin_T_t_ds_task_definition_A_drop_UN_taskName();
-
--- ----------------------------
--- These columns will not be used in the new version,if you determine that the
historical data is useless, you can delete it using the sql below
--- ----------------------------
-
--- ALTER TABLE t_ds_alert DROP COLUMN "show_type", DROP COLUMN "alert_type",
DROP COLUMN "receivers", DROP COLUMN "receivers_cc";
-
--- ALTER TABLE t_ds_alertgroup DROP COLUMN "group_type";
-
--- ALTER TABLE t_ds_process_definition DROP COLUMN "receivers", DROP COLUMN
"receivers_cc";
-
--- ALTER TABLE t_ds_process_definition_version DROP COLUMN "receivers", DROP
COLUMN "receivers_cc";
-
--- DROP TABLE IF EXISTS t_ds_relation_user_alertgroup;
-
--- ALTER TABLE t_ds_command DROP COLUMN "dependence";
-
--- ALTER TABLE t_ds_error_command DROP COLUMN "dependence";
\ No newline at end of file
diff --git a/sql/upgrade/2.0.0_schema/postgresql/dolphinscheduler_ddl_post.sql
b/sql/upgrade/2.0.0_schema/postgresql/dolphinscheduler_ddl_post.sql
index 5f26e35..728b6bd 100644
--- a/sql/upgrade/2.0.0_schema/postgresql/dolphinscheduler_ddl_post.sql
+++ b/sql/upgrade/2.0.0_schema/postgresql/dolphinscheduler_ddl_post.sql
@@ -15,3 +15,13 @@
* limitations under the License.
*/
+ALTER TABLE "t_ds_process_definition" DROP CONSTRAINT
"t_ds_process_definition_pkey";
+ALTER TABLE "t_ds_process_definition" ADD CONSTRAINT
"t_ds_process_definition_pkey" PRIMARY KEY ("id","code");
+ALTER TABLE "t_ds_process_definition" DROP CONSTRAINT
"process_definition_unique";
+DROP INDEX "process_definition_index";
+ALTER TABLE "t_ds_process_definition" DROP "process_definition_json";
+ALTER TABLE "t_ds_process_definition" DROP "connects";
+ALTER TABLE "t_ds_process_definition" DROP "receivers";
+ALTER TABLE "t_ds_process_definition" DROP "receivers_cc";
+ALTER TABLE "t_ds_process_definition" DROP "modify_by";
+ALTER TABLE "t_ds_process_definition" DROP "resource_ids";
\ No newline at end of file
