This is an automated email from the ASF dual-hosted git repository. jstastnycz pushed a commit to branch sync-20250907 in repository https://gitbox.apache.org/repos/asf/incubator-kie-kogito-apps.git
commit 42cc09d7f6e32287b352c3e31fd413669c43a68e Author: Tiago Bento <[email protected]> AuthorDate: Mon Jul 28 10:13:14 2025 -0400 [DBACLD-187241] Move KIE Flyway scripts from `ibm/bamoe @ 9.2.1 branch` to downstream forks of `kogito-runtimes` and `kogito-apps` @ main (#21) --- .../main/resources/META-INF/kie-flyway.properties | 2 + ...reate_data_audit_storage_sqlserver_bamoe920.sql | 377 +++++++++++++++++++++ ..._update_event_type_check_sqlserver_bamoe921.sql | 5 + ...formation_to_process_instance_logs_bamoe921.sql | 11 + ...__create_data_audit_storage_oracle_bamoe921.sql | 360 ++++++++++++++++++++ .../main/resources/META-INF/kie-flyway.properties | 4 +- ...reate_data_index_storage_sqlserver_bamoe920.sql | 253 ++++++++++++++ ...reate_data_index_storage_sqlserver_bamoe921.sql | 9 + ...__create_data_index_storage_oracle_bamoe921.sql | 265 +++++++++++++++ .../main/resources/META-INF/kie-flyway.properties | 4 +- ...3.0.3__create_jobservice_sqlserver_bamoe920.sql | 28 ++ .../V3.0.3__create_jobservice_oracle_bamoe921.sql | 35 ++ .../main/resources/META-INF/kie-flyway.properties | 4 +- ...create_kogito_data_cache_sqlserver_bamoe920.sql | 6 + ...1__create_kogito_data_cache_oracle_bamoe921.sql | 6 + 15 files changed, 1366 insertions(+), 3 deletions(-) diff --git a/data-audit/kogito-addons-data-audit-jpa/kogito-addons-data-audit-jpa-common/src/main/resources/META-INF/kie-flyway.properties b/data-audit/kogito-addons-data-audit-jpa/kogito-addons-data-audit-jpa-common/src/main/resources/META-INF/kie-flyway.properties index 846dfb422..f9c12b1ed 100644 --- a/data-audit/kogito-addons-data-audit-jpa/kogito-addons-data-audit-jpa-common/src/main/resources/META-INF/kie-flyway.properties +++ b/data-audit/kogito-addons-data-audit-jpa/kogito-addons-data-audit-jpa-common/src/main/resources/META-INF/kie-flyway.properties @@ -21,3 +21,5 @@ module.name=data-audit module.locations.h2=classpath:kie-flyway/db/data-audit/h2 module.locations.postgresql=classpath:kie-flyway/db/data-audit/postgresql +module.locations.microsoft-sql-server=classpath:kie-flyway/db/data-audit/microsoft-sql-server +module.locations.oracle=classpath:kie-flyway/db/data-audit/oracle \ No newline at end of file diff --git a/data-audit/kogito-addons-data-audit-jpa/kogito-addons-data-audit-jpa-common/src/main/resources/kie-flyway/db/data-audit/microsoft-sql-server/V1.4.0__create_data_audit_storage_sqlserver_bamoe920.sql b/data-audit/kogito-addons-data-audit-jpa/kogito-addons-data-audit-jpa-common/src/main/resources/kie-flyway/db/data-audit/microsoft-sql-server/V1.4.0__create_data_audit_storage_sqlserver_bamoe920.sql new file mode 100644 index 000000000..abf68f04d --- /dev/null +++ b/data-audit/kogito-addons-data-audit-jpa/kogito-addons-data-audit-jpa-common/src/main/resources/kie-flyway/db/data-audit/microsoft-sql-server/V1.4.0__create_data_audit_storage_sqlserver_bamoe920.sql @@ -0,0 +1,377 @@ +CREATE TABLE audit_query ( + identifier character varying(255) NOT NULL, + graph_ql_definition character varying(5000), + query character varying(5000), + CONSTRAINT audit_query_pkey PRIMARY KEY (identifier) +); + +CREATE SEQUENCE job_execution_history_id_seq + START WITH 1 + INCREMENT BY 50 + NO MINVALUE + NO MAXVALUE + CACHE 50; + +CREATE TABLE job_execution_log ( + id bigint NOT NULL, + event_date datetime2(6), + execution_counter integer, + expiration_time datetime2(6), + job_id character varying(255), + node_instance_id character varying(255), + priority integer, + process_instance_id character varying(255), + repeat_interval bigint, + repeat_limit integer, + retries integer, + scheduled_id character varying(255), + status character varying(255), + CONSTRAINT job_execution_log_pkey PRIMARY KEY (id) +); + +CREATE TABLE process_instance_error_log ( + id bigint NOT NULL, + business_key character varying(255), + event_date datetime2(6), + event_id character varying(255), + parent_process_instance_id character varying(255), + process_id character varying(255), + process_instance_id character varying(255), + process_type character varying(255), + process_version character varying(255), + root_process_id character varying(255), + root_process_instance_id character varying(255), + error_message character varying(255), + node_definition_id character varying(255), + node_instance_id character varying(255), + CONSTRAINT process_instance_error_log_pkey PRIMARY KEY (id) +); + +CREATE SEQUENCE process_instance_error_log_seq_id + START WITH 1 + INCREMENT BY 50 + NO MINVALUE + NO MAXVALUE + CACHE 50; + +CREATE TABLE process_instance_node_log ( + id bigint NOT NULL, + business_key character varying(255), + event_date datetime2(6), + event_id character varying(255), + parent_process_instance_id character varying(255), + process_id character varying(255), + process_instance_id character varying(255), + process_type character varying(255), + process_version character varying(255), + root_process_id character varying(255), + root_process_instance_id character varying(255), + connection character varying(255), + event_data character varying(255), + event_type character varying(255), + node_definition_id character varying(255), + node_instance_id character varying(255), + node_name character varying(255), + node_type character varying(255), + sla_due_date datetime2(6), + work_item_id character varying(255), + CONSTRAINT process_instance_node_log_pkey PRIMARY KEY (id), + CONSTRAINT process_instance_node_log_event_type_check CHECK (event_type IN ('ENTER', 'EXIT', 'ABORTED', 'ASYNC_ENTER', 'OBSOLETE', 'SKIPPED', 'ERROR', 'SLA_VIOLATION')) +); + +CREATE SEQUENCE process_instance_node_log_id_seq + START WITH 1 + INCREMENT BY 50 + NO MINVALUE + NO MAXVALUE + CACHE 50; + +CREATE TABLE process_instance_state_log ( + id bigint NOT NULL, + business_key character varying(255), + event_date datetime2(6), + event_id character varying(255), + parent_process_instance_id character varying(255), + process_id character varying(255), + process_instance_id character varying(255), + process_type character varying(255), + process_version character varying(255), + root_process_id character varying(255), + root_process_instance_id character varying(255), + event_type character varying(255) NOT NULL, + outcome character varying(255), + sla_due_date datetime2(6), + state character varying(255), + CONSTRAINT process_instance_state_log_pkey PRIMARY KEY (id), + CONSTRAINT process_instance_state_log_event_type_check CHECK (event_type IN ('ACTIVE', 'COMPLETED', 'SLA_VIOLATION', 'MIGRATED')) +); + +CREATE SEQUENCE process_instance_state_log_id_seq + START WITH 1 + INCREMENT BY 50 + NO MINVALUE + NO MAXVALUE + CACHE 50; + +CREATE TABLE process_instance_state_roles_log ( + process_instance_state_log_id bigint NOT NULL, + role character varying(255), + CONSTRAINT fk_process_instance_state_pid FOREIGN KEY (process_instance_state_log_id) REFERENCES process_instance_state_log(id) +); + +CREATE TABLE process_instance_variable_log ( + id bigint NOT NULL, + business_key character varying(255), + event_date datetime2(6), + event_id character varying(255), + parent_process_instance_id character varying(255), + process_id character varying(255), + process_instance_id character varying(255), + process_type character varying(255), + process_version character varying(255), + root_process_id character varying(255), + root_process_instance_id character varying(255), + variable_id character varying(255), + variable_name character varying(255), + variable_value varchar(max), + CONSTRAINT process_instance_variable_log_pkey PRIMARY KEY (id) +); + +CREATE SEQUENCE process_instance_variable_log_id_seq + START WITH 1 + INCREMENT BY 50 + NO MINVALUE + NO MAXVALUE + CACHE 50; + +CREATE TABLE task_instance_assignment_log ( + id bigint NOT NULL, + business_key character varying(255), + event_date datetime2(6), + event_id character varying(255), + event_user character varying(255), + process_instance_id character varying(255), + user_task_definition_id character varying(255), + user_task_instance_id character varying(255), + assignment_type character varying(255), + task_name character varying(255), + CONSTRAINT task_instance_assignment_log_pkey PRIMARY KEY (id) +); + +CREATE SEQUENCE task_instance_assignment_log_id_seq + START WITH 1 + INCREMENT BY 50 + NO MINVALUE + NO MAXVALUE + CACHE 50; + +CREATE TABLE task_instance_assignment_users_log ( + task_instance_assignment_log_id bigint NOT NULL, + user_id character varying(255), + CONSTRAINT fk_task_instance_assignment_log_tid FOREIGN KEY (task_instance_assignment_log_id) REFERENCES task_instance_assignment_log(id) +); + +CREATE TABLE task_instance_attachment_log ( + id bigint NOT NULL, + business_key character varying(255), + event_date datetime2(6), + event_id character varying(255), + event_user character varying(255), + process_instance_id character varying(255), + user_task_definition_id character varying(255), + user_task_instance_id character varying(255), + attachment_id character varying(255), + attachment_name character varying(255), + attachment_uri character varying(255), + event_type integer, + CONSTRAINT task_instance_attachment_log_pkey PRIMARY KEY (id) +); + +CREATE SEQUENCE task_instance_attachment_log_id_seq + START WITH 1 + INCREMENT BY 50 + NO MINVALUE + NO MAXVALUE + CACHE 50; + +CREATE TABLE task_instance_comment_log ( + id bigint NOT NULL, + business_key character varying(255), + event_date datetime2(6), + event_id character varying(255), + event_user character varying(255), + process_instance_id character varying(255), + user_task_definition_id character varying(255), + user_task_instance_id character varying(255), + comment_content character varying(1000), + comment_id character varying(255), + event_type integer, + CONSTRAINT task_instance_comment_log_pkey PRIMARY KEY (id) +); + +CREATE SEQUENCE task_instance_comment_log_id_seq + START WITH 1 + INCREMENT BY 50 + NO MINVALUE + NO MAXVALUE + CACHE 50; + +CREATE TABLE task_instance_deadline_log ( + id bigint NOT NULL, + business_key character varying(255), + event_date datetime2(6), + event_id character varying(255), + event_user character varying(255), + process_instance_id character varying(255), + user_task_definition_id character varying(255), + user_task_instance_id character varying(255), + event_type character varying(255), + CONSTRAINT task_instance_deadline_log_pkey PRIMARY KEY (id) +); + +CREATE SEQUENCE task_instance_deadline_log_id_seq + START WITH 1 + INCREMENT BY 50 + NO MINVALUE + NO MAXVALUE + CACHE 50; + +CREATE TABLE task_instance_deadline_notification_log ( + task_instance_deadline_log_id bigint NOT NULL, + property_value character varying(255), + property_name character varying(255) NOT NULL, + CONSTRAINT task_instance_deadline_notification_log_pkey PRIMARY KEY (task_instance_deadline_log_id, property_name), + CONSTRAINT fk_task_instance_deadline_tid FOREIGN KEY (task_instance_deadline_log_id) REFERENCES task_instance_deadline_log(id) +); + +CREATE TABLE task_instance_state_log ( + id bigint NOT NULL, + business_key character varying(255), + event_date datetime2(6), + event_id character varying(255), + event_user character varying(255), + process_instance_id character varying(255), + user_task_definition_id character varying(255), + user_task_instance_id character varying(255), + actual_user character varying(255), + description character varying(255), + event_type character varying(255), + name character varying(255), + state character varying(255), + CONSTRAINT task_instance_state_log_pkey PRIMARY KEY (id) +); + +CREATE SEQUENCE task_instance_state_log_id_seq + START WITH 1 + INCREMENT BY 50 + NO MINVALUE + NO MAXVALUE + CACHE 50; + +CREATE TABLE task_instance_variable_log ( + id bigint NOT NULL, + business_key character varying(255), + event_date datetime2(6), + event_id character varying(255), + event_user character varying(255), + process_instance_id character varying(255), + user_task_definition_id character varying(255), + user_task_instance_id character varying(255), + variable_id character varying(255), + variable_name character varying(255), + variable_type character varying(255), + variable_value varchar(max), + CONSTRAINT task_instance_variable_log_pkey PRIMARY KEY (id), + CONSTRAINT task_instance_variable_log_variable_type_check CHECK (variable_type IN ('INPUT', 'OUTPUT')) +); + +CREATE SEQUENCE task_instance_variable_log_id_seq + START WITH 1 + INCREMENT BY 50 + NO MINVALUE + NO MAXVALUE + CACHE 50; + +CREATE INDEX ix_jel_jid ON job_execution_log (job_id); + +CREATE INDEX ix_jel_pid ON job_execution_log (process_instance_id); + +CREATE INDEX ix_jel_status ON job_execution_log (status); + +CREATE INDEX ix_piel_event_date ON process_instance_error_log (event_date); + +CREATE INDEX ix_piel_key ON process_instance_error_log (business_key); + +CREATE INDEX ix_piel_pid ON process_instance_error_log (process_instance_id); + +CREATE INDEX ix_pinl_event_date ON process_instance_node_log (event_date); + +CREATE INDEX ix_pinl_key ON process_instance_node_log (business_key); + +CREATE INDEX ix_pinl_pid ON process_instance_node_log (process_instance_id); + +CREATE INDEX ix_pisl_event_date ON process_instance_state_log (event_date); + +CREATE INDEX ix_pisl_key ON process_instance_state_log (business_key); + +CREATE INDEX ix_pisl_pid ON process_instance_state_log (process_instance_id); + +CREATE INDEX ix_pisl_state ON process_instance_state_log (state); + +CREATE INDEX ix_pivl_event_date ON process_instance_variable_log (event_date); + +CREATE INDEX ix_pivl_key ON process_instance_variable_log (business_key); + +CREATE INDEX ix_pivl_pid ON process_instance_variable_log (process_instance_id); + +CREATE INDEX ix_pivl_var_id ON process_instance_variable_log (variable_id); + +CREATE INDEX ix_tavl_event_date ON task_instance_variable_log (event_date); + +CREATE INDEX ix_tavl_key ON task_instance_variable_log (business_key); + +CREATE INDEX ix_tavl_pid ON task_instance_variable_log (process_instance_id); + +CREATE INDEX ix_tavl_utid ON task_instance_variable_log (user_task_instance_id); + +CREATE INDEX ix_utasl_event_date ON task_instance_assignment_log (event_date); + +CREATE INDEX ix_utasl_key ON task_instance_assignment_log (business_key); + +CREATE INDEX ix_utasl_pid ON task_instance_assignment_log (process_instance_id); + +CREATE INDEX ix_utasl_utid ON task_instance_assignment_log (user_task_instance_id); + +CREATE INDEX ix_utatl_event_date ON task_instance_attachment_log (event_date); + +CREATE INDEX ix_utatl_key ON task_instance_attachment_log (business_key); + +CREATE INDEX ix_utatl_pid ON task_instance_attachment_log (process_instance_id); + +CREATE INDEX ix_utatl_utid ON task_instance_attachment_log (user_task_instance_id); + +CREATE INDEX ix_utcl_event_date ON task_instance_comment_log (event_date); + +CREATE INDEX ix_utcl_key ON task_instance_comment_log (business_key); + +CREATE INDEX ix_utcl_pid ON task_instance_comment_log (process_instance_id); + +CREATE INDEX ix_utcl_utid ON task_instance_comment_log (user_task_instance_id); + +CREATE INDEX ix_utdl_event_date ON task_instance_deadline_log (event_date); + +CREATE INDEX ix_utdl_key ON task_instance_deadline_log (business_key); + +CREATE INDEX ix_utdl_pid ON task_instance_deadline_log (process_instance_id); + +CREATE INDEX ix_utdl_utid ON task_instance_deadline_log (user_task_instance_id); + +CREATE INDEX ix_utsl_event_date ON task_instance_state_log (event_date); + +CREATE INDEX ix_utsl_key ON task_instance_state_log (business_key); + +CREATE INDEX ix_utsl_pid ON task_instance_state_log (process_instance_id); + +CREATE INDEX ix_utsl_state ON task_instance_state_log (state); + +CREATE INDEX ix_utsl_utid ON task_instance_state_log (user_task_instance_id); diff --git a/data-audit/kogito-addons-data-audit-jpa/kogito-addons-data-audit-jpa-common/src/main/resources/kie-flyway/db/data-audit/microsoft-sql-server/V1.4.1__Add_state_update_event_type_check_sqlserver_bamoe921.sql b/data-audit/kogito-addons-data-audit-jpa/kogito-addons-data-audit-jpa-common/src/main/resources/kie-flyway/db/data-audit/microsoft-sql-server/V1.4.1__Add_state_update_event_type_check_sqlserver_bamoe921.sql new file mode 100644 index 000000000..d0837ac57 --- /dev/null +++ b/data-audit/kogito-addons-data-audit-jpa/kogito-addons-data-audit-jpa-common/src/main/resources/kie-flyway/db/data-audit/microsoft-sql-server/V1.4.1__Add_state_update_event_type_check_sqlserver_bamoe921.sql @@ -0,0 +1,5 @@ +ALTER TABLE process_instance_node_log DROP CONSTRAINT process_instance_node_log_event_type_check; +ALTER TABLE process_instance_state_log DROP CONSTRAINT process_instance_state_log_event_type_check; + +ALTER TABLE process_instance_node_log ADD CONSTRAINT process_instance_node_log_event_type_check CHECK (event_type in ('ENTER','EXIT','ABORTED','ASYNC_ENTER','OBSOLETE','SKIPPED','ERROR','SLA_VIOLATION','STATE_UPDATED')); +ALTER TABLE process_instance_state_log ADD CONSTRAINT process_instance_state_log_event_type_check CHECK (event_type in ('ACTIVE','COMPLETED','SLA_VIOLATION','MIGRATED','STATE_UPDATED')); \ No newline at end of file diff --git a/data-audit/kogito-addons-data-audit-jpa/kogito-addons-data-audit-jpa-common/src/main/resources/kie-flyway/db/data-audit/microsoft-sql-server/V1.4.2__Add_user_information_to_process_instance_logs_bamoe921.sql b/data-audit/kogito-addons-data-audit-jpa/kogito-addons-data-audit-jpa-common/src/main/resources/kie-flyway/db/data-audit/microsoft-sql-server/V1.4.2__Add_user_information_to_process_instance_logs_bamoe921.sql new file mode 100644 index 000000000..e34240f54 --- /dev/null +++ b/data-audit/kogito-addons-data-audit-jpa/kogito-addons-data-audit-jpa-common/src/main/resources/kie-flyway/db/data-audit/microsoft-sql-server/V1.4.2__Add_user_information_to_process_instance_logs_bamoe921.sql @@ -0,0 +1,11 @@ +ALTER TABLE process_instance_error_log +ADD event_user NVARCHAR(255); + +ALTER TABLE process_instance_node_log +ADD event_user NVARCHAR(255); + +ALTER TABLE process_instance_state_log +ADD event_user NVARCHAR(255); + +ALTER TABLE process_instance_variable_log +ADD event_user NVARCHAR(255); diff --git a/data-audit/kogito-addons-data-audit-jpa/kogito-addons-data-audit-jpa-common/src/main/resources/kie-flyway/db/data-audit/oracle/V1.4.2__create_data_audit_storage_oracle_bamoe921.sql b/data-audit/kogito-addons-data-audit-jpa/kogito-addons-data-audit-jpa-common/src/main/resources/kie-flyway/db/data-audit/oracle/V1.4.2__create_data_audit_storage_oracle_bamoe921.sql new file mode 100644 index 000000000..56096ae2f --- /dev/null +++ b/data-audit/kogito-addons-data-audit-jpa/kogito-addons-data-audit-jpa-common/src/main/resources/kie-flyway/db/data-audit/oracle/V1.4.2__create_data_audit_storage_oracle_bamoe921.sql @@ -0,0 +1,360 @@ + +CREATE TABLE audit_query ( + identifier VARCHAR2(255) NOT NULL, + graph_ql_definition CLOB, + query CLOB, + CONSTRAINT audit_query_pkey PRIMARY KEY (identifier) +); + +CREATE SEQUENCE job_execution_history_id_seq + START WITH 1 + INCREMENT BY 50 + CACHE 50; + +CREATE TABLE job_execution_log ( + id NUMBER(19) NOT NULL, + event_date timestamp(6), + execution_counter integer, + expiration_time timestamp(6), + job_id VARCHAR2(255), + node_instance_id VARCHAR2(255), + priority integer, + process_instance_id VARCHAR2(255), + repeat_interval NUMBER(19), + repeat_limit integer, + retries integer, + scheduled_id VARCHAR2(255), + status VARCHAR2(255), + CONSTRAINT job_execution_log_pkey PRIMARY KEY (id) +); + +CREATE TABLE process_instance_error_log ( + id NUMBER(19) NOT NULL, + business_key VARCHAR2(255), + event_date timestamp(6), + event_id VARCHAR2(255), + event_user VARCHAR2(255), + parent_process_instance_id VARCHAR2(255), + process_id VARCHAR2(255), + process_instance_id VARCHAR2(255), + process_type VARCHAR2(255), + process_version VARCHAR2(255), + root_process_id VARCHAR2(255), + root_process_instance_id VARCHAR2(255), + error_message VARCHAR2(255), + node_definition_id VARCHAR2(255), + node_instance_id VARCHAR2(255), + CONSTRAINT process_instance_error_log_pkey PRIMARY KEY (id) +); + +CREATE SEQUENCE process_instance_error_log_seq_id + START WITH 1 + INCREMENT BY 50 + CACHE 50; + +CREATE TABLE process_instance_node_log ( + id NUMBER(19) NOT NULL, + business_key VARCHAR2(255), + event_date timestamp(6), + event_id VARCHAR2(255), + event_user VARCHAR2(255), + parent_process_instance_id VARCHAR2(255), + process_id VARCHAR2(255), + process_instance_id VARCHAR2(255), + process_type VARCHAR2(255), + process_version VARCHAR2(255), + root_process_id VARCHAR2(255), + root_process_instance_id VARCHAR2(255), + connection VARCHAR2(255), + event_data VARCHAR2(255), + event_type VARCHAR2(255), + node_definition_id VARCHAR2(255), + node_instance_id VARCHAR2(255), + node_name VARCHAR2(255), + node_type VARCHAR2(255), + sla_due_date timestamp(6), + work_item_id VARCHAR2(255), + CONSTRAINT process_instance_node_log_event_type_check CHECK (event_type IN ('ENTER','EXIT','ABORTED','ASYNC_ENTER','OBSOLETE','SKIPPED','ERROR','SLA_VIOLATION','STATE_UPDATED')), + CONSTRAINT process_instance_node_log_pkey PRIMARY KEY (id) +); + +CREATE SEQUENCE process_instance_node_log_id_seq + START WITH 1 + INCREMENT BY 50 + CACHE 50; + +CREATE TABLE process_instance_state_log ( + id NUMBER(19) NOT NULL, + business_key VARCHAR2(255), + event_date timestamp(6), + event_id VARCHAR2(255), + event_user VARCHAR2(255), + parent_process_instance_id VARCHAR2(255), + process_id VARCHAR2(255), + process_instance_id VARCHAR2(255), + process_type VARCHAR2(255), + process_version VARCHAR2(255), + root_process_id VARCHAR2(255), + root_process_instance_id VARCHAR2(255), + event_type VARCHAR2(255) NOT NULL, + outcome VARCHAR2(255), + sla_due_date timestamp(6), + state VARCHAR2(255), + CONSTRAINT process_instance_state_log_event_type_check CHECK (event_type IN ('ACTIVE','COMPLETED','SLA_VIOLATION','MIGRATED','STATE_UPDATED')), + CONSTRAINT process_instance_state_log_pkey PRIMARY KEY (id) +); + +CREATE SEQUENCE process_instance_state_log_id_seq + START WITH 1 + INCREMENT BY 50 + CACHE 50; + +CREATE TABLE process_instance_state_roles_log ( + process_instance_state_log_id NUMBER(19) NOT NULL, + role VARCHAR2(255), + CONSTRAINT fk_process_instance_state_pid FOREIGN KEY (process_instance_state_log_id) REFERENCES process_instance_state_log(id) +); + +CREATE TABLE process_instance_variable_log ( + id NUMBER(19) NOT NULL, + business_key VARCHAR2(255), + event_date timestamp(6), + event_id VARCHAR2(255), + event_user VARCHAR2(255), + parent_process_instance_id VARCHAR2(255), + process_id VARCHAR2(255), + process_instance_id VARCHAR2(255), + process_type VARCHAR2(255), + process_version VARCHAR2(255), + root_process_id VARCHAR2(255), + root_process_instance_id VARCHAR2(255), + variable_id VARCHAR2(255), + variable_name VARCHAR2(255), + variable_value CLOB, + CONSTRAINT process_instance_variable_log_pkey PRIMARY KEY (id) +); + +CREATE SEQUENCE process_instance_variable_log_id_seq + START WITH 1 + INCREMENT BY 50 + CACHE 50; + +CREATE TABLE task_instance_assignment_log ( + id NUMBER(19) NOT NULL, + business_key VARCHAR2(255), + event_date timestamp(6), + event_id VARCHAR2(255), + event_user VARCHAR2(255), + process_instance_id VARCHAR2(255), + user_task_definition_id VARCHAR2(255), + user_task_instance_id VARCHAR2(255), + assignment_type VARCHAR2(255), + task_name VARCHAR2(255), + CONSTRAINT task_instance_assignment_log_pkey PRIMARY KEY (id) +); + +CREATE SEQUENCE task_instance_assignment_log_id_seq + START WITH 1 + INCREMENT BY 50 + CACHE 50; + +CREATE TABLE task_instance_assignment_users_log ( + task_instance_assignment_log_id NUMBER(19) NOT NULL, + user_id VARCHAR2(255), + CONSTRAINT fk_task_instance_assignment_log_tid FOREIGN KEY (task_instance_assignment_log_id) REFERENCES task_instance_assignment_log(id) +); + +CREATE TABLE task_instance_attachment_log ( + id NUMBER(19) NOT NULL, + business_key VARCHAR2(255), + event_date timestamp(6), + event_id VARCHAR2(255), + event_user VARCHAR2(255), + process_instance_id VARCHAR2(255), + user_task_definition_id VARCHAR2(255), + user_task_instance_id VARCHAR2(255), + attachment_id VARCHAR2(255), + attachment_name VARCHAR2(255), + attachment_uri VARCHAR2(255), + event_type integer, + CONSTRAINT task_instance_attachment_log_pkey PRIMARY KEY (id) +); + +CREATE SEQUENCE task_instance_attachment_log_id_seq + START WITH 1 + INCREMENT BY 50 + CACHE 50; + +CREATE TABLE task_instance_comment_log ( + id NUMBER(19) NOT NULL, + business_key VARCHAR2(255), + event_date timestamp(6), + event_id VARCHAR2(255), + event_user VARCHAR2(255), + process_instance_id VARCHAR2(255), + user_task_definition_id VARCHAR2(255), + user_task_instance_id VARCHAR2(255), + comment_content VARCHAR2(1000), + comment_id VARCHAR2(255), + event_type integer, + CONSTRAINT task_instance_comment_log_pkey PRIMARY KEY (id) +); + +CREATE SEQUENCE task_instance_comment_log_id_seq + START WITH 1 + INCREMENT BY 50 + CACHE 50; + +CREATE TABLE task_instance_deadline_log ( + id NUMBER(19) NOT NULL, + business_key VARCHAR2(255), + event_date timestamp(6), + event_id VARCHAR2(255), + event_user VARCHAR2(255), + process_instance_id VARCHAR2(255), + user_task_definition_id VARCHAR2(255), + user_task_instance_id VARCHAR2(255), + event_type VARCHAR2(255), + CONSTRAINT task_instance_deadline_log_pkey PRIMARY KEY (id) +); + +CREATE SEQUENCE task_instance_deadline_log_id_seq + START WITH 1 + INCREMENT BY 50 + CACHE 50; + +CREATE TABLE task_instance_deadline_notification_log ( + task_instance_deadline_log_id NUMBER(19) NOT NULL, + property_value VARCHAR2(255), + property_name VARCHAR2(255) NOT NULL, + CONSTRAINT task_instance_deadline_notification_log_pkey PRIMARY KEY (task_instance_deadline_log_id, property_name), + CONSTRAINT fk_task_instance_deadline_tid FOREIGN KEY (task_instance_deadline_log_id) REFERENCES task_instance_deadline_log(id) +); + +CREATE TABLE task_instance_state_log ( + id NUMBER(19) NOT NULL, + business_key VARCHAR2(255), + event_date timestamp(6), + event_id VARCHAR2(255), + event_user VARCHAR2(255), + process_instance_id VARCHAR2(255), + user_task_definition_id VARCHAR2(255), + user_task_instance_id VARCHAR2(255), + actual_user VARCHAR2(255), + description VARCHAR2(255), + event_type VARCHAR2(255), + name VARCHAR2(255), + state VARCHAR2(255), + CONSTRAINT task_instance_state_log_pkey PRIMARY KEY (id) +); + +CREATE SEQUENCE task_instance_state_log_id_seq + START WITH 1 + INCREMENT BY 50 + CACHE 50; + +CREATE TABLE task_instance_variable_log ( + id NUMBER(19) NOT NULL, + business_key VARCHAR2(255), + event_date timestamp(6), + event_id VARCHAR2(255), + event_user VARCHAR2(255), + process_instance_id VARCHAR2(255), + user_task_definition_id VARCHAR2(255), + user_task_instance_id VARCHAR2(255), + variable_id VARCHAR2(255), + variable_name VARCHAR2(255), + variable_type VARCHAR2(255), + variable_value CLOB, + CONSTRAINT task_instance_variable_log_variable_type_check CHECK (variable_type IN ('INPUT', 'OUTPUT')), + CONSTRAINT task_instance_variable_log_pkey PRIMARY KEY (id) +); + +CREATE SEQUENCE task_instance_variable_log_id_seq + START WITH 1 + INCREMENT BY 50 + CACHE 50; + +CREATE INDEX ix_jel_jid ON job_execution_log (job_id); + +CREATE INDEX ix_jel_pid ON job_execution_log (process_instance_id); + +CREATE INDEX ix_jel_status ON job_execution_log (status); + +CREATE INDEX ix_piel_event_date ON process_instance_error_log (event_date); + +CREATE INDEX ix_piel_key ON process_instance_error_log (business_key); + +CREATE INDEX ix_piel_pid ON process_instance_error_log (process_instance_id); + +CREATE INDEX ix_pinl_event_date ON process_instance_node_log (event_date); + +CREATE INDEX ix_pinl_key ON process_instance_node_log (business_key); + +CREATE INDEX ix_pinl_pid ON process_instance_node_log (process_instance_id); + +CREATE INDEX ix_pisl_event_date ON process_instance_state_log (event_date); + +CREATE INDEX ix_pisl_key ON process_instance_state_log (business_key); + +CREATE INDEX ix_pisl_pid ON process_instance_state_log (process_instance_id); + +CREATE INDEX ix_pisl_state ON process_instance_state_log (state); + +CREATE INDEX ix_pivl_event_date ON process_instance_variable_log (event_date); + +CREATE INDEX ix_pivl_key ON process_instance_variable_log (business_key); + +CREATE INDEX ix_pivl_pid ON process_instance_variable_log (process_instance_id); + +CREATE INDEX ix_pivl_var_id ON process_instance_variable_log (variable_id); + +CREATE INDEX ix_tavl_event_date ON task_instance_variable_log (event_date); + +CREATE INDEX ix_tavl_key ON task_instance_variable_log (business_key); + +CREATE INDEX ix_tavl_pid ON task_instance_variable_log (process_instance_id); + +CREATE INDEX ix_tavl_utid ON task_instance_variable_log (user_task_instance_id); + +CREATE INDEX ix_utasl_event_date ON task_instance_assignment_log (event_date); + +CREATE INDEX ix_utasl_key ON task_instance_assignment_log (business_key); + +CREATE INDEX ix_utasl_pid ON task_instance_assignment_log (process_instance_id); + +CREATE INDEX ix_utasl_utid ON task_instance_assignment_log (user_task_instance_id); + +CREATE INDEX ix_utatl_event_date ON task_instance_attachment_log (event_date); + +CREATE INDEX ix_utatl_key ON task_instance_attachment_log (business_key); + +CREATE INDEX ix_utatl_pid ON task_instance_attachment_log (process_instance_id); + +CREATE INDEX ix_utatl_utid ON task_instance_attachment_log (user_task_instance_id); + +CREATE INDEX ix_utcl_event_date ON task_instance_comment_log (event_date); + +CREATE INDEX ix_utcl_key ON task_instance_comment_log (business_key); + +CREATE INDEX ix_utcl_pid ON task_instance_comment_log (process_instance_id); + +CREATE INDEX ix_utcl_utid ON task_instance_comment_log (user_task_instance_id); + +CREATE INDEX ix_utdl_event_date ON task_instance_deadline_log (event_date); + +CREATE INDEX ix_utdl_key ON task_instance_deadline_log (business_key); + +CREATE INDEX ix_utdl_pid ON task_instance_deadline_log (process_instance_id); + +CREATE INDEX ix_utdl_utid ON task_instance_deadline_log (user_task_instance_id); + +CREATE INDEX ix_utsl_event_date ON task_instance_state_log (event_date); + +CREATE INDEX ix_utsl_key ON task_instance_state_log (business_key); + +CREATE INDEX ix_utsl_pid ON task_instance_state_log (process_instance_id); + +CREATE INDEX ix_utsl_state ON task_instance_state_log (state); + +CREATE INDEX ix_utsl_utid ON task_instance_state_log (user_task_instance_id); diff --git a/data-index/data-index-storage/data-index-storage-jpa/src/main/resources/META-INF/kie-flyway.properties b/data-index/data-index-storage/data-index-storage-jpa/src/main/resources/META-INF/kie-flyway.properties index 35f70ad9f..3d3c5c367 100644 --- a/data-index/data-index-storage/data-index-storage-jpa/src/main/resources/META-INF/kie-flyway.properties +++ b/data-index/data-index-storage/data-index-storage-jpa/src/main/resources/META-INF/kie-flyway.properties @@ -20,4 +20,6 @@ module.name=data-index module.locations.postgresql=classpath:kie-flyway/db/data-index/postgresql -module.locations.default=classpath:kie-flyway/db/data-index/ansi \ No newline at end of file +module.locations.default=classpath:kie-flyway/db/data-index/ansi +module.locations.microsoft-sql-server=classpath:kie-flyway/db/data-index/microsoft-sql-server +module.locations.oracle=classpath:kie-flyway/db/data-index/oracle \ No newline at end of file diff --git a/data-index/data-index-storage/data-index-storage-jpa/src/main/resources/kie-flyway/db/data-index/microsoft-sql-server/V1.45.1.0__create_data_index_storage_sqlserver_bamoe920.sql b/data-index/data-index-storage/data-index-storage-jpa/src/main/resources/kie-flyway/db/data-index/microsoft-sql-server/V1.45.1.0__create_data_index_storage_sqlserver_bamoe920.sql new file mode 100644 index 000000000..565511b5d --- /dev/null +++ b/data-index/data-index-storage/data-index-storage-jpa/src/main/resources/kie-flyway/db/data-index/microsoft-sql-server/V1.45.1.0__create_data_index_storage_sqlserver_bamoe920.sql @@ -0,0 +1,253 @@ + +CREATE TABLE process_definitions ( + id character varying(255) NOT NULL, + version character varying(255) NOT NULL, + name character varying(255), + type character varying(255), + source varbinary(max), + endpoint character varying(255), + description character varying(255), + metadata varchar(max), + CONSTRAINT definitions_pkey PRIMARY KEY (id, version) +); + +CREATE TABLE definitions_addons ( + process_id character varying(255) NOT NULL, + process_version character varying(255) NOT NULL, + addon character varying(255) NOT NULL, + CONSTRAINT definitions_addons_pkey PRIMARY KEY (process_id, process_version, addon), + CONSTRAINT fk_definitions_addons_definitions FOREIGN KEY (process_id, process_version) REFERENCES process_definitions(id, version) ON DELETE CASCADE +); + +CREATE TABLE definitions_annotations ( + annotation character varying(255) NOT NULL, + process_id character varying(255) NOT NULL, + process_version character varying(255) NOT NULL, + CONSTRAINT definitions_annotations_pkey PRIMARY KEY (annotation, process_id, process_version), + CONSTRAINT fk_definitions_annotations FOREIGN KEY (process_id, process_version) REFERENCES process_definitions(id, version) ON DELETE CASCADE +); + +CREATE TABLE definitions_nodes ( + id character varying(255) NOT NULL, + name character varying(255), + unique_id character varying(255), + type character varying(255), + process_id character varying(255) NOT NULL, + process_version character varying(255) NOT NULL, + CONSTRAINT definitions_nodes_pkey PRIMARY KEY (id, process_id, process_version), + CONSTRAINT fk_definitions_nodes_definitions FOREIGN KEY (process_id, process_version) REFERENCES process_definitions(id, version) ON DELETE CASCADE +); + +CREATE TABLE definitions_nodes_metadata ( + node_id character varying(255) NOT NULL, + process_id character varying(255) NOT NULL, + process_version character varying(255) NOT NULL, + meta_value character varying(255), + name character varying(255) NOT NULL, + CONSTRAINT definitions_nodes_metadata_pkey PRIMARY KEY (node_id, process_id, process_version, name), + CONSTRAINT fk_definitions_nodes_metadata_definitions_nodes FOREIGN KEY (node_id, process_id, process_version) REFERENCES definitions_nodes(id, process_id, process_version) ON DELETE CASCADE +); + +CREATE TABLE definitions_roles ( + process_id character varying(255) NOT NULL, + process_version character varying(255) NOT NULL, + role character varying(255) NOT NULL, + CONSTRAINT definitions_roles_pkey PRIMARY KEY (process_id, process_version, role), + CONSTRAINT fk_definitions_roles_definitions FOREIGN KEY (process_id, process_version) REFERENCES process_definitions(id, version) ON DELETE CASCADE +); + +CREATE TABLE jobs ( + id character varying(255) NOT NULL, + callback_endpoint character varying(255), + endpoint character varying(255), + execution_counter integer, + expiration_time datetimeoffset(6), + last_update datetimeoffset(6), + node_instance_id character varying(255), + priority integer, + process_id character varying(255), + process_instance_id character varying(255), + repeat_interval bigint, + repeat_limit integer, + retries integer, + root_process_id character varying(255), + root_process_instance_id character varying(255), + scheduled_id character varying(255), + status character varying(255), + CONSTRAINT jobs_pkey PRIMARY KEY (id) +); + + +CREATE TABLE processes ( + id character varying(255) NOT NULL, + business_key character varying(255), + end_time datetimeoffset(6), + endpoint character varying(255), + message character varying(max), + node_definition_id character varying(255), + last_update_time datetimeoffset(6), + parent_process_instance_id character varying(255), + process_id character varying(255), + process_name character varying(255), + root_process_id character varying(255), + root_process_instance_id character varying(255), + start_time datetimeoffset(6), + state integer, + variables nvarchar(max), + version character varying(255), + created_by character varying(max), + updated_by character varying(max), + sla_due_date datetimeoffset(6), + CONSTRAINT processes_pkey PRIMARY KEY (id) +); + + +CREATE TABLE milestones ( + id character varying(255) NOT NULL, + process_instance_id character varying(255) NOT NULL, + name character varying(255), + status character varying(255), + CONSTRAINT milestones_pkey PRIMARY KEY (id, process_instance_id), + CONSTRAINT fk_milestones_process FOREIGN KEY (process_instance_id) REFERENCES processes(id) ON DELETE CASCADE +); + +CREATE TABLE nodes ( + id character varying(255) NOT NULL, + definition_id character varying(255), + enter_time datetimeoffset(6), + exit_time datetimeoffset(6), + name character varying(255), + node_id character varying(255), + type character varying(255), + process_instance_id character varying(255) NOT NULL, + sla_due_date datetimeoffset(6), + CONSTRAINT nodes_pkey PRIMARY KEY (id), + CONSTRAINT fk_nodes_process FOREIGN KEY (process_instance_id) REFERENCES processes(id) ON DELETE CASCADE +); + +CREATE TABLE processes_addons ( + process_id character varying(255) NOT NULL, + addon character varying(255) NOT NULL, + CONSTRAINT processes_addons_pkey PRIMARY KEY (process_id, addon), + CONSTRAINT fk_processes_addons_processes FOREIGN KEY (process_id) REFERENCES processes(id) ON DELETE CASCADE +); + +CREATE TABLE processes_roles ( + process_id character varying(255) NOT NULL, + role character varying(255) NOT NULL, + CONSTRAINT processes_roles_pkey PRIMARY KEY (process_id, role), + CONSTRAINT fk_processes_roles_processes FOREIGN KEY (process_id) REFERENCES processes(id) ON DELETE CASCADE +); + +CREATE TABLE tasks ( + id character varying(255) NOT NULL, + actual_owner character varying(255), + completed datetimeoffset(6), + description character varying(255), + endpoint character varying(255), + inputs nvarchar(max), + last_update datetimeoffset(6), + name character varying(255), + outputs nvarchar(max), + priority character varying(255), + process_id character varying(255), + process_instance_id character varying(255), + reference_name character varying(255), + root_process_id character varying(255), + root_process_instance_id character varying(255), + started datetimeoffset(6), + state character varying(255), + external_reference_id character varying(4000), + sla_due_date datetimeoffset(6), + CONSTRAINT tasks_pkey PRIMARY KEY (id) +); + + +CREATE TABLE attachments ( + id character varying(255) NOT NULL, + content character varying(255), + name character varying(255), + updated_at datetimeoffset(6), + updated_by character varying(255), + task_id character varying(255) NOT NULL, + CONSTRAINT attachments_pkey PRIMARY KEY (id), + CONSTRAINT fk_attachments_tasks FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE, +); + +CREATE TABLE comments ( + id character varying(255) NOT NULL, + content character varying(1000), + updated_at datetimeoffset(6), + updated_by character varying(255), + task_id character varying(255) NOT NULL, + CONSTRAINT comments_pkey PRIMARY KEY (id), + CONSTRAINT fk_comments_tasks FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE +); + +CREATE TABLE tasks_admin_groups ( + task_id character varying(255) NOT NULL, + group_id character varying(255) NOT NULL, + CONSTRAINT tasks_admin_groups_pkey PRIMARY KEY (task_id, group_id), + CONSTRAINT fk_tasks_admin_groups_tasks FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE +); + +CREATE TABLE tasks_admin_users ( + task_id character varying(255) NOT NULL, + user_id character varying(255) NOT NULL, + CONSTRAINT tasks_admin_users_pkey PRIMARY KEY (task_id, user_id), + CONSTRAINT fk_tasks_admin_users_tasks FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE +); + +CREATE TABLE tasks_excluded_users ( + task_id character varying(255) NOT NULL, + user_id character varying(255) NOT NULL, + CONSTRAINT tasks_excluded_users_pkey PRIMARY KEY (task_id, user_id), + CONSTRAINT fk_tasks_excluded_users_tasks FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE +); + +CREATE TABLE tasks_potential_groups ( + task_id character varying(255) NOT NULL, + group_id character varying(255) NOT NULL, + CONSTRAINT tasks_potential_groups_pkey PRIMARY KEY (task_id, group_id), + CONSTRAINT fk_tasks_potential_groups_tasks FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE +); + +CREATE TABLE tasks_potential_users ( + task_id character varying(255) NOT NULL, + user_id character varying(255) NOT NULL, + CONSTRAINT tasks_potential_users_pkey PRIMARY KEY (task_id, user_id), + CONSTRAINT fk_tasks_potential_users_tasks FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE +); + + +CREATE INDEX idx_comments_tid ON comments (task_id); + +CREATE INDEX idx_attachments_tid ON attachments (task_id) + +CREATE INDEX idx_definitions_addons_pid_pv ON definitions_addons (process_id, process_version); + +CREATE INDEX idx_definitions_annotations_pid_pv ON definitions_annotations (process_id, process_version); + +CREATE INDEX idx_definitions_nodes_metadata_pid_pv ON definitions_nodes_metadata (process_id, process_version); + +CREATE INDEX idx_definitions_nodes_pid_pv ON definitions_nodes (process_id, process_version); + +CREATE INDEX idx_definitions_roles_pid_pv ON definitions_roles (process_id, process_version); + +CREATE INDEX idx_milestones_piid ON milestones (process_instance_id); + +CREATE INDEX idx_nodes_piid ON nodes (process_instance_id); + +CREATE INDEX idx_processes_addons_pid ON processes_addons (process_id); + +CREATE INDEX idx_processes_roles_pid ON processes_roles (process_id); + +CREATE INDEX idx_tasks_admin_groups_tid ON tasks_admin_groups (task_id); + +CREATE INDEX idx_tasks_admin_users_tid ON tasks_admin_users (task_id); + +CREATE INDEX idx_tasks_excluded_users_tid ON tasks_excluded_users (task_id); + +CREATE INDEX idx_tasks_potential_groups_tid ON tasks_potential_groups (task_id); + +CREATE INDEX idx_tasks_potential_users_tid ON tasks_potential_users (task_id); diff --git a/data-index/data-index-storage/data-index-storage-jpa/src/main/resources/kie-flyway/db/data-index/microsoft-sql-server/V1.45.1.2__create_data_index_storage_sqlserver_bamoe921.sql b/data-index/data-index-storage/data-index-storage-jpa/src/main/resources/kie-flyway/db/data-index/microsoft-sql-server/V1.45.1.2__create_data_index_storage_sqlserver_bamoe921.sql new file mode 100644 index 000000000..15db0bdd5 --- /dev/null +++ b/data-index/data-index-storage/data-index-storage-jpa/src/main/resources/kie-flyway/db/data-index/microsoft-sql-server/V1.45.1.2__create_data_index_storage_sqlserver_bamoe921.sql @@ -0,0 +1,9 @@ +ALTER TABLE nodes ADD retrigger BIT DEFAULT 0; + +ALTER TABLE nodes ADD error_message character varying(max); + +ALTER TABLE processes ADD node_instance_id character varying(255); + +ALTER TABLE processes ADD cloud_event_id character varying(1000); + +ALTER TABLE processes ADD cloud_event_source character varying(1000); \ No newline at end of file diff --git a/data-index/data-index-storage/data-index-storage-jpa/src/main/resources/kie-flyway/db/data-index/oracle/V1.45.1.2__create_data_index_storage_oracle_bamoe921.sql b/data-index/data-index-storage/data-index-storage-jpa/src/main/resources/kie-flyway/db/data-index/oracle/V1.45.1.2__create_data_index_storage_oracle_bamoe921.sql new file mode 100644 index 000000000..e2a93005a --- /dev/null +++ b/data-index/data-index-storage/data-index-storage-jpa/src/main/resources/kie-flyway/db/data-index/oracle/V1.45.1.2__create_data_index_storage_oracle_bamoe921.sql @@ -0,0 +1,265 @@ +CREATE TABLE tasks ( + id VARCHAR2(255) NOT NULL, + actual_owner VARCHAR2(255), + completed timestamp, + description VARCHAR2(255), + endpoint VARCHAR2(255), + inputs CLOB, + last_update timestamp, + name VARCHAR2(255), + outputs CLOB, + priority VARCHAR2(255), + process_id VARCHAR2(255), + process_instance_id VARCHAR2(255), + reference_name VARCHAR2(255), + root_process_id VARCHAR2(255), + root_process_instance_id VARCHAR2(255), + started timestamp, + state VARCHAR2(255), + external_reference_id VARCHAR2(4000), + sla_due_date timestamp, + CONSTRAINT tasks_pkey PRIMARY KEY (id) +); + +CREATE TABLE attachments ( + id VARCHAR2(255) NOT NULL, + content VARCHAR2(255), + name VARCHAR2(255), + updated_at timestamp, + updated_by VARCHAR2(255), + task_id VARCHAR2(255) NOT NULL, + CONSTRAINT attachments_pkey PRIMARY KEY (id), + CONSTRAINT fk_attachments_tasks FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE +); + +CREATE TABLE comments ( + id VARCHAR2(255) NOT NULL, + content VARCHAR2(1000), + updated_at timestamp, + updated_by VARCHAR2(255), + task_id VARCHAR2(255) NOT NULL, + CONSTRAINT comments_pkey PRIMARY KEY (id), + CONSTRAINT fk_comments_tasks FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE +); + +CREATE TABLE definitions ( + id VARCHAR2(255) NOT NULL, + version VARCHAR2(255) NOT NULL, + name VARCHAR2(255), + type VARCHAR2(255), + source BLOB, + endpoint VARCHAR2(255), + description VARCHAR2(255), + metadata CLOB, + CONSTRAINT definitions_pkey PRIMARY KEY (id, version) +); + +CREATE TABLE definitions_addons ( + process_id VARCHAR2(255) NOT NULL, + process_version VARCHAR2(255) NOT NULL, + addon VARCHAR2(255) NOT NULL, + CONSTRAINT definitions_addons_pkey PRIMARY KEY (process_id, process_version, addon), + CONSTRAINT fk_definitions_addons_definitions FOREIGN KEY (process_id, process_version) REFERENCES definitions(id, version) ON DELETE CASCADE +); + +CREATE TABLE definitions_annotations ( + annotation VARCHAR2(255) NOT NULL, + process_id VARCHAR2(255) NOT NULL, + process_version VARCHAR2(255) NOT NULL, + CONSTRAINT definitions_annotations_pkey PRIMARY KEY (annotation, process_id, process_version), + CONSTRAINT fk_definitions_annotations FOREIGN KEY (process_id, process_version) REFERENCES definitions(id, version) ON DELETE CASCADE +); + +CREATE TABLE definitions_nodes ( + id VARCHAR2(255) NOT NULL, + name VARCHAR2(255), + unique_id VARCHAR2(255), + type VARCHAR2(255), + process_id VARCHAR2(255) NOT NULL, + process_version VARCHAR2(255) NOT NULL, + CONSTRAINT definitions_nodes_pkey PRIMARY KEY (id, process_id, process_version), + CONSTRAINT fk_definitions_nodes_definitions FOREIGN KEY (process_id, process_version) REFERENCES definitions(id, version) ON DELETE CASCADE +); + +CREATE TABLE definitions_nodes_metadata ( + node_id VARCHAR2(255) NOT NULL, + process_id VARCHAR2(255) NOT NULL, + process_version VARCHAR2(255) NOT NULL, + meta_value VARCHAR2(255), + name VARCHAR2(255) NOT NULL, + CONSTRAINT definitions_nodes_metadata_pkey PRIMARY KEY (node_id, process_id, process_version, name), + CONSTRAINT fk_definitions_nodes_metadata_definitions_nodes FOREIGN KEY (node_id, process_id, process_version) REFERENCES definitions_nodes(id, process_id, process_version) ON DELETE CASCADE +); + +CREATE TABLE definitions_roles ( + process_id VARCHAR2(255) NOT NULL, + process_version VARCHAR2(255) NOT NULL, + role VARCHAR2(255) NOT NULL, + CONSTRAINT definitions_roles_pkey PRIMARY KEY (process_id, process_version, role), + CONSTRAINT fk_definitions_roles_definitions FOREIGN KEY (process_id, process_version) REFERENCES definitions(id, version) ON DELETE CASCADE +); + +CREATE TABLE jobs ( + id VARCHAR2(255) NOT NULL, + callback_endpoint VARCHAR2(255), + endpoint VARCHAR2(255), + execution_counter integer, + expiration_time timestamp, + last_update timestamp, + node_instance_id VARCHAR2(255), + priority integer, + process_id VARCHAR2(255), + process_instance_id VARCHAR2(255), + repeat_interval NUMBER(19), + repeat_limit integer, + retries integer, + root_process_id VARCHAR2(255), + root_process_instance_id VARCHAR2(255), + scheduled_id VARCHAR2(255), + status VARCHAR2(255), + CONSTRAINT jobs_pkey PRIMARY KEY (id) +); + +CREATE TABLE processes ( + id VARCHAR2(255) NOT NULL, + business_key VARCHAR2(255), + end_time timestamp, + endpoint VARCHAR2(255), + message CLOB, + node_definition_id VARCHAR2(255), + last_update_time timestamp, + parent_process_instance_id VARCHAR2(255), + process_id VARCHAR2(255), + process_name VARCHAR2(255), + root_process_id VARCHAR2(255), + root_process_instance_id VARCHAR2(255), + start_time timestamp, + state NUMBER(10), + variables CLOB, + version VARCHAR2(255), + created_by CLOB, + updated_by CLOB, + sla_due_date timestamp, + node_instance_id VARCHAR2(255), + cloud_event_id VARCHAR2(1000), + cloud_event_source VARCHAR2(1000), + CONSTRAINT processes_pkey PRIMARY KEY (id) +); + +CREATE TABLE milestones ( + id VARCHAR2(255) NOT NULL, + process_instance_id VARCHAR2(255) NOT NULL, + name VARCHAR2(255), + status VARCHAR2(255), + CONSTRAINT milestones_pkey PRIMARY KEY (id, process_instance_id), + CONSTRAINT fk_milestones_process FOREIGN KEY (process_instance_id) REFERENCES processes(id) ON DELETE CASCADE +); + +CREATE TABLE nodes ( + id VARCHAR2(255) NOT NULL, + definition_id VARCHAR2(255), + enter timestamp, + exit timestamp, + name VARCHAR2(255), + node_id VARCHAR2(255), + type VARCHAR2(255), + process_instance_id VARCHAR2(255) NOT NULL, + sla_due_date timestamp, + retrigger NUMBER(1) DEFAULT 0, + error_message CLOB, + CONSTRAINT nodes_pkey PRIMARY KEY (id), + CONSTRAINT fk_nodes_process FOREIGN KEY (process_instance_id) REFERENCES processes(id) ON DELETE CASCADE +); + +CREATE TABLE processes_addons ( + process_id VARCHAR2(255) NOT NULL, + addon VARCHAR2(255) NOT NULL, + CONSTRAINT processes_addons_pkey PRIMARY KEY (process_id, addon), + CONSTRAINT fk_processes_addons_processes FOREIGN KEY (process_id) REFERENCES processes(id) ON DELETE CASCADE +); + +CREATE TABLE processes_roles ( + process_id VARCHAR2(255) NOT NULL, + role VARCHAR2(255) NOT NULL, + CONSTRAINT processes_roles_pkey PRIMARY KEY (process_id, role), + CONSTRAINT fk_processes_roles_processes FOREIGN KEY (process_id) REFERENCES processes(id) ON DELETE CASCADE +); + +CREATE TABLE tasks_admin_groups ( + task_id VARCHAR2(255) NOT NULL, + group_id VARCHAR2(255) NOT NULL, + CONSTRAINT tasks_admin_groups_pkey PRIMARY KEY (task_id, group_id), + CONSTRAINT fk_tasks_admin_groups_tasks FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE +); + +CREATE TABLE tasks_admin_users ( + task_id VARCHAR2(255) NOT NULL, + user_id VARCHAR2(255) NOT NULL, + CONSTRAINT tasks_admin_users_pkey PRIMARY KEY (task_id, user_id), + CONSTRAINT fk_tasks_admin_users_tasks FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE +); + +CREATE TABLE tasks_excluded_users ( + task_id VARCHAR2(255) NOT NULL, + user_id VARCHAR2(255) NOT NULL, + CONSTRAINT tasks_excluded_users_pkey PRIMARY KEY (task_id, user_id), + CONSTRAINT fk_tasks_excluded_users_tasks FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE +); + +CREATE TABLE tasks_potential_groups ( + task_id VARCHAR2(255) NOT NULL, + group_id VARCHAR2(255) NOT NULL, + CONSTRAINT tasks_potential_groups_pkey PRIMARY KEY (task_id, group_id), + CONSTRAINT fk_tasks_potential_groups_tasks FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE +); + +CREATE TABLE tasks_potential_users ( + task_id VARCHAR2(255) NOT NULL, + user_id VARCHAR2(255) NOT NULL, + CONSTRAINT tasks_potential_users_pkey PRIMARY KEY (task_id, user_id), + CONSTRAINT fk_tasks_potential_users_tasks FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE +); + +CREATE TABLE definitions_metadata ( + process_id VARCHAR2(255) NOT NULL, + process_version VARCHAR2(255) NOT NULL, + meta_value VARCHAR2(255), + name VARCHAR2(255) NOT NULL, + CONSTRAINT definitions_metadata_pkey PRIMARY KEY (process_id, process_version, name), + CONSTRAINT fk_definitions_metadata FOREIGN KEY (process_id, process_version) REFERENCES definitions(id, version) ON DELETE CASCADE +); + +CREATE INDEX idx_attachments_tid ON attachments btree (task_id); + +CREATE INDEX idx_comments_tid ON comments btree (task_id); + +CREATE INDEX idx_definitions_addons_pid_pv ON definitions_addons btree (process_id, process_version); + +CREATE INDEX idx_definitions_annotations_pid_pv ON definitions_annotations btree (process_id, process_version); + +CREATE INDEX idx_definitions_metadata_pid_pv ON definitions_metadata btree (process_id, process_version); + +CREATE INDEX idx_definitions_nodes_metadata_pid_pv ON definitions_nodes_metadata btree (process_id, process_version); + +CREATE INDEX idx_definitions_nodes_pid_pv ON definitions_nodes btree (process_id, process_version); + +CREATE INDEX idx_definitions_roles_pid_pv ON definitions_roles btree (process_id, process_version); + +CREATE INDEX idx_milestones_piid ON milestones btree (process_instance_id); + +CREATE INDEX idx_nodes_piid ON nodes btree (process_instance_id); + +CREATE INDEX idx_processes_addons_pid ON processes_addons btree (process_id); + +CREATE INDEX idx_processes_roles_pid ON processes_roles btree (process_id); + +CREATE INDEX idx_tasks_admin_groups_tid ON tasks_admin_groups btree (task_id); + +CREATE INDEX idx_tasks_admin_users_tid ON tasks_admin_users btree (task_id); + +CREATE INDEX idx_tasks_excluded_users_tid ON tasks_excluded_users btree (task_id); + +CREATE INDEX idx_tasks_potential_groups_tid ON tasks_potential_groups btree (task_id); + +CREATE INDEX idx_tasks_potential_users_tid ON tasks_potential_users btree (task_id); + diff --git a/jobs-service/jobs-service-storage-jpa/src/main/resources/META-INF/kie-flyway.properties b/jobs-service/jobs-service-storage-jpa/src/main/resources/META-INF/kie-flyway.properties index 985651de5..6a67bce44 100644 --- a/jobs-service/jobs-service-storage-jpa/src/main/resources/META-INF/kie-flyway.properties +++ b/jobs-service/jobs-service-storage-jpa/src/main/resources/META-INF/kie-flyway.properties @@ -20,4 +20,6 @@ module.name=jobs-service module.locations.postgresql=classpath:kie-flyway/db/jobs-service/postgresql -module.locations.default=classpath:kie-flyway/db/jobs-service/ansi \ No newline at end of file +module.locations.default=classpath:kie-flyway/db/jobs-service/ansi +module.locations.microsoft-sql-server=classpath:kie-flyway/db/jobs-service/microsoft-sql-server +module.locations.oracle=classpath:kie-flyway/db/jobs-service/oracle \ No newline at end of file diff --git a/jobs-service/jobs-service-storage-jpa/src/main/resources/kie-flyway/db/jobs-service/microsoft-sql-server/V3.0.3__create_jobservice_sqlserver_bamoe920.sql b/jobs-service/jobs-service-storage-jpa/src/main/resources/kie-flyway/db/jobs-service/microsoft-sql-server/V3.0.3__create_jobservice_sqlserver_bamoe920.sql new file mode 100644 index 000000000..5f1efe0f0 --- /dev/null +++ b/jobs-service/jobs-service-storage-jpa/src/main/resources/kie-flyway/db/jobs-service/microsoft-sql-server/V3.0.3__create_jobservice_sqlserver_bamoe920.sql @@ -0,0 +1,28 @@ +CREATE TABLE job_details ( + id character varying(50) NOT NULL, -- the unique id internally on the job service + correlation_id character varying(50), -- the job id on the runtimes, + status character varying(40), -- the job status: 'ERROR' or 'EXECUTED' or 'SCHEDULED' or 'RETRY' or 'CANCELED' + last_update datetimeoffset, + retries integer, + execution_counter integer, -- number of times the job was executed + scheduled_id character varying(40), -- the execution control on the scheduler (id on vertx.setTimer, quartzId...) + priority integer, + recipient nvarchar(MAX), -- http callback, event topic + job_trigger nvarchar(MAX),-- when/how it should be executed + fire_time datetimeoffset, + execution_timeout bigint, + execution_timeout_unit character varying(40), + created datetimeoffset, + CONSTRAINT job_details_pkey1 PRIMARY KEY (id), + INDEX job_details_created_idx (created) +); + +CREATE TABLE job_service_management ( + id character varying(40) NOT NULL, + last_heartbeat datetimeoffset, + token character varying(40), + CONSTRAINT job_service_management_pkey PRIMARY KEY (id), + CONSTRAINT job_service_management_token_key UNIQUE (token) +); + +CREATE INDEX job_details_fire_time_idx ON job_details (fire_time); \ No newline at end of file diff --git a/jobs-service/jobs-service-storage-jpa/src/main/resources/kie-flyway/db/jobs-service/oracle/V3.0.3__create_jobservice_oracle_bamoe921.sql b/jobs-service/jobs-service-storage-jpa/src/main/resources/kie-flyway/db/jobs-service/oracle/V3.0.3__create_jobservice_oracle_bamoe921.sql new file mode 100644 index 000000000..17dbc5b32 --- /dev/null +++ b/jobs-service/jobs-service-storage-jpa/src/main/resources/kie-flyway/db/jobs-service/oracle/V3.0.3__create_jobservice_oracle_bamoe921.sql @@ -0,0 +1,35 @@ +-- +-- It contains all the required Tables to correctly manage and persist Job Instances +-- + +-- TABLE job_details: Represents a Job Instance on the Job Service with its details +CREATE TABLE job_details ( + id VARCHAR2(50) NOT NULL, -- the unique id internally on the job service + correlation_id VARCHAR2(50), -- the job id on the runtimes, + status VARCHAR2(40), -- the job status: 'ERROR' or 'EXECUTED' or 'SCHEDULED' or 'RETRY' or 'CANCELED' + last_update timestamp with time zone, + retries integer, + execution_counter integer, -- number of times the job was executed + scheduled_id VARCHAR2(40), -- the execution control on the scheduler (id on vertx.setTimer, quartzId...) + priority integer, + recipient CLOB, -- http callback, event topic + job_trigger CLOB, -- when/how it should be executed + fire_time timestamp with time zone, + execution_timeout NUMBER(19), + execution_timeout_unit VARCHAR2(40), + created timestamp with time zone, + CONSTRAINT job_details_pkey1 PRIMARY KEY (id) +); + +-- TABLE job_service_management: used for clustering and to check lead instance +CREATE TABLE job_service_management ( + id VARCHAR2(40) NOT NULL, + last_heartbeat timestamp with time zone, + token VARCHAR2(40), + CONSTRAINT job_service_management_pkey PRIMARY KEY (id), + CONSTRAINT job_service_management_token_key UNIQUE (token) +); + +CREATE INDEX job_details_created_idx ON job_details (created); + +CREATE INDEX job_details_fire_time_idx ON job_details (fire_time); diff --git a/persistence-commons/persistence-commons-jpa/src/main/resources/META-INF/kie-flyway.properties b/persistence-commons/persistence-commons-jpa/src/main/resources/META-INF/kie-flyway.properties index cf9d0ea88..cded3568c 100644 --- a/persistence-commons/persistence-commons-jpa/src/main/resources/META-INF/kie-flyway.properties +++ b/persistence-commons/persistence-commons-jpa/src/main/resources/META-INF/kie-flyway.properties @@ -20,4 +20,6 @@ module.name=persistence-commons module.locations.postgresql=classpath:kie-flyway/db/persistence-commons/postgresql -module.locations.default=classpath:kie-flyway/db/persistence-commons/ansi \ No newline at end of file +module.locations.default=classpath:kie-flyway/db/persistence-commons/ansi +module.locations.microsoft-sql-server=classpath:kie-flyway/db/persistence-commons/microsoft-sql-server +module.locations.oracle=classpath:kie-flyway/db/persistence-commons/oracle \ No newline at end of file diff --git a/persistence-commons/persistence-commons-jpa/src/main/resources/kie-flyway/db/persistence-commons/microsoft-sql-server/V1.45.0.9.1__create_kogito_data_cache_sqlserver_bamoe920.sql b/persistence-commons/persistence-commons-jpa/src/main/resources/kie-flyway/db/persistence-commons/microsoft-sql-server/V1.45.0.9.1__create_kogito_data_cache_sqlserver_bamoe920.sql new file mode 100644 index 000000000..d3f2a5c61 --- /dev/null +++ b/persistence-commons/persistence-commons-jpa/src/main/resources/kie-flyway/db/persistence-commons/microsoft-sql-server/V1.45.0.9.1__create_kogito_data_cache_sqlserver_bamoe920.sql @@ -0,0 +1,6 @@ +CREATE TABLE kogito_data_cache ( + var_name character varying(255) NOT NULL, + cache_name character varying(255) NOT NULL, + json_value nvarchar(max), + CONSTRAINT kogito_data_cache_pkey PRIMARY KEY (var_name, cache_name) +); \ No newline at end of file diff --git a/persistence-commons/persistence-commons-jpa/src/main/resources/kie-flyway/db/persistence-commons/oracle/V1.45.0.9.1__create_kogito_data_cache_oracle_bamoe921.sql b/persistence-commons/persistence-commons-jpa/src/main/resources/kie-flyway/db/persistence-commons/oracle/V1.45.0.9.1__create_kogito_data_cache_oracle_bamoe921.sql new file mode 100644 index 000000000..cba903c0b --- /dev/null +++ b/persistence-commons/persistence-commons-jpa/src/main/resources/kie-flyway/db/persistence-commons/oracle/V1.45.0.9.1__create_kogito_data_cache_oracle_bamoe921.sql @@ -0,0 +1,6 @@ +CREATE TABLE kogito_data_cache ( + var_name VARCHAR2(255) NOT NULL, + cache_name VARCHAR2(255) NOT NULL, + json_value CLOB, + CONSTRAINT kogito_data_cache_pkey PRIMARY KEY (var_name, cache_name) +); --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
