-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/upgrade/upgrade.changelog-up-to-1.20.0.xml
-- Ran at: 03/04/20 12:06
-- Against: SA@jdbc:h2:/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/incremental-upgrade-database/squash-tm
-- Liquibase version: 2.0.5
-- *********************************************************************

-- Lock Database
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0::jprioux::(Checksum: 3:b937644b7cb0f46c1e1f7e40a1eda3f0)
-- Update TM database version number
UPDATE CORE_CONFIG SET VALUE = '1.20.0' WHERE STR_KEY = 'squashtest.tm.database.version';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jprioux', 'Update TM database version number', NOW(), 'Update Data', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0', '2.0.5', '3:b937644b7cb0f46c1e1f7e40a1eda3f0', 445);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-176-custom-report-custom-export::jlor::(Checksum: 3:e3569bc7ec6c776e5ace367c27859806)
-- Create new object CustomExport in Custom Report Workspace
CREATE TABLE CUSTOM_REPORT_CUSTOM_EXPORT (CRCE_ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, NAME VARCHAR(255), PROJECT_ID BIGINT NOT NULL, CREATED_BY VARCHAR(50) NOT NULL, CREATED_ON TIMESTAMP NOT NULL, LAST_MODIFIED_BY VARCHAR(50) DEFAULT null, LAST_MODIFIED_ON TIMESTAMP DEFAULT null, CONSTRAINT PK_CUSTOM_REPORT_CUSTOM_EXPORT PRIMARY KEY (CRCE_ID), CONSTRAINT fk_custom_export_project_id FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT (PROJECT_ID));

CREATE INDEX idx_fk_custom_export_project_id ON CUSTOM_REPORT_CUSTOM_EXPORT(PROJECT_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jlor', 'Create new object CustomExport in Custom Report Workspace', NOW(), 'Create Table, Create Index', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-176-custom-report-custom-export', '2.0.5', '3:e3569bc7ec6c776e5ace367c27859806', 446);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-125-use-tm-tree-structure-in-scm-repository::jlor::(Checksum: 3:3e5f82716b6e4832798a9be1ba640f6b)
-- Add an option in Project automation settings to use the tm tree structure when transmitting test cases to a scm repository
ALTER TABLE PROJECT ADD USE_TREE_STRUCTURE_IN_SCM_REPO BOOLEAN DEFAULT TRUE NOT NULL;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jlor', 'Add an option in Project automation settings to use the tm tree structure when transmitting test cases to a scm repository', NOW(), 'Add Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-125-use-tm-tree-structure-in-scm-repository', '2.0.5', '3:3e5f82716b6e4832798a9be1ba640f6b', 447);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-220-add-missing-role-automation::agu::(Checksum: 3:9793655635dd0f28284ac5f78b231108)
-- Add missing automation role following changeset tm-1.19.0-SQTM-510-add-roles-automation
INSERT INTO CORE_PARTY_AUTHORITY(PARTY_ID, AUTHORITY)
      SELECT cpa.PARTY_ID, 'ROLE_TF_FUNCTIONAL_TESTER' from CORE_PARTY_AUTHORITY cpa
      INNER JOIN CORE_PARTY_AUTHORITY cpa2 on cpa.PARTY_ID = cpa2.PARTY_ID
      WHERE cpa.AUTHORITY = 'ROLE_TM_USER'
      GROUP BY cpa.PARTY_ID
      HAVING COUNT(cpa.PARTY_ID) = 1;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('agu', 'Add missing automation role following changeset tm-1.19.0-SQTM-510-add-roles-automation', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-220-add-missing-role-automation', '2.0.5', '3:9793655635dd0f28284ac5f78b231108', 448);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-91-custom-export-column::jlor::(Checksum: 3:55b397d1565faa0dc6162f5a64f2e124)
-- Create new tables to store the scope and the columns of a custom export
CREATE TABLE CUSTOM_EXPORT_SCOPE (CUSTOM_EXPORT_ID BIGINT NOT NULL, ENTITY_REFERENCE_TYPE VARCHAR(50), ENTITY_REFERENCE_ID BIGINT, CONSTRAINT fk_custom_export_scope_custom_export_id FOREIGN KEY (CUSTOM_EXPORT_ID) REFERENCES CUSTOM_REPORT_CUSTOM_EXPORT (CRCE_ID));

CREATE TABLE CUSTOM_EXPORT_COLUMN (CUSTOM_EXPORT_ID BIGINT NOT NULL, LABEL VARCHAR(255), CUF_ID BIGINT, CONSTRAINT fk_custom_export_column_custom_export_id FOREIGN KEY (CUSTOM_EXPORT_ID) REFERENCES CUSTOM_REPORT_CUSTOM_EXPORT (CRCE_ID));

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jlor', 'Create new tables to store the scope and the columns of a custom export', NOW(), 'Create Table (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-91-custom-export-column', '2.0.5', '3:55b397d1565faa0dc6162f5a64f2e124', 449);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-13-add-conflict-association-ismanual::amk::(Checksum: 3:b006570f2144465d7445aaeb856f3509)
ALTER TABLE AUTOMATION_REQUEST ADD CONFLICT_ASSOCIATION LONGVARCHAR DEFAULT null;

ALTER TABLE AUTOMATION_REQUEST ADD IS_MANUAL BOOLEAN DEFAULT FALSE NOT NULL;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('amk', '', NOW(), 'Add Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-13-add-conflict-association-ismanual', '2.0.5', '3:b006570f2144465d7445aaeb856f3509', 450);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-13-update-is-manual::amk::(Checksum: 3:fc915e0d25c55ed07a819c85fefab933)
UPDATE AUTOMATION_REQUEST ar SET IS_MANUAL = true
      WHERE TEST_CASE_ID in (SELECT tc.TCLN_ID FROM TEST_CASE tc WHERE ar.TEST_CASE_ID = tc.TCLN_ID AND tc.TA_TEST IS NOT null);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('amk', '', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-13-update-is-manual', '2.0.5', '3:fc915e0d25c55ed07a819c85fefab933', 451);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-13-association-auto-cdt-script-auto::amk::(Checksum: 3:e8da5b14d399690b2a11539f85ca27a1)
ALTER TABLE TEST_CASE ADD UUID VARCHAR(36);

ALTER TABLE TEST_CASE ADD CONSTRAINT uc_test_case_uuid UNIQUE (UUID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('amk', '', NOW(), 'Add Column, Add Unique Constraint', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-13-association-auto-cdt-script-auto', '2.0.5', '3:e8da5b14d399690b2a11539f85ca27a1', 452);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-13-update-uuid-h2::amk::(Checksum: 3:a0b08f74009b32a551aec24a6cb6f74b)
-- generate uuid if it's null
UPDATE TEST_CASE SET UUID =  random_uuid() WHERE UUID is NULL;

ALTER TABLE TEST_CASE MODIFY UUID VARCHAR2(36) NOT NULL;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('amk', 'generate uuid if it''s null', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-13-update-uuid-h2', '2.0.5', '3:a0b08f74009b32a551aec24a6cb6f74b', 453);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-503-clean-campaign-test-plan-item-table::aguilhem::(Checksum: 3:0788589d8120dbbe8145f9e1a85f3a48)
-- Clean CAMPAIGN_TEST_PLAN_ITEM table from line with null value in CAMPAIGN_ID
DELETE FROM CAMPAIGN_TEST_PLAN_ITEM WHERE CAMPAIGN_ID IS NULL;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('aguilhem', 'Clean CAMPAIGN_TEST_PLAN_ITEM table from line with null value in CAMPAIGN_ID', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-503-clean-campaign-test-plan-item-table', '2.0.5', '3:0788589d8120dbbe8145f9e1a85f3a48', 454);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-180::abo::(Checksum: 3:c36f737b1eb8ae1cf6aaf22a4b7b2c35)
-- Change dataType for RequirementStatus
UPDATE CHART_COLUMN_PROTOTYPE SET DATA_TYPE = 'REQUIREMENT_STATUS'
      WHERE LABEL = 'REQUIREMENT_STATUS' OR LABEL = 'REQUIREMENT_VERSION_STATUS';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('abo', 'Change dataType for RequirementStatus', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-180', '2.0.5', '3:c36f737b1eb8ae1cf6aaf22a4b7b2c35', 455);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-282-00::bsiri::(Checksum: 3:613679307e297f935cf5d93eca3cd011)
-- Preparatory step : dropping foreign keys pointing to CHART_QUERY and CHART_COLUMN_PROTOTYPE.
ALTER TABLE CHART_DEFINITION DROP CONSTRAINT fk_chart_query;

ALTER TABLE CHART_COLUMN_PROTOTYPE DROP CONSTRAINT fk_column_query;

ALTER TABLE CHART_AXIS_COLUMN DROP CONSTRAINT fk_chart_axis_chart_query;

ALTER TABLE CHART_FILTER DROP CONSTRAINT fk_chart_FILTER_chart_query;

ALTER TABLE CHART_MEASURE_COLUMN DROP CONSTRAINT fk_chart_measure_chart_query;

ALTER TABLE CHART_COLUMN_ROLE DROP CONSTRAINT fk_column_role_chart_column;

ALTER TABLE CHART_AXIS_COLUMN DROP CONSTRAINT fk_axis_column_chart_column;

ALTER TABLE CHART_FILTER DROP CONSTRAINT fk_chart_filter_chart_column;

ALTER TABLE CHART_MEASURE_COLUMN DROP CONSTRAINT fk_chart_measure_chart_column;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Preparatory step : dropping foreign keys pointing to CHART_QUERY and CHART_COLUMN_PROTOTYPE.', NOW(), 'Drop Foreign Key Constraint (x9)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-282-00', '2.0.5', '3:613679307e297f935cf5d93eca3cd011', 456);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-282-01b-h2-pg::bsiri::(Checksum: 3:39c4a51a7aeab50ab97e4598ddd78df5)
-- Rename CHART_QUERY and CHART_COLUMN_PROTOTYPE and their primary keys
ALTER TABLE CHART_QUERY DROP PRIMARY KEY;

ALTER TABLE CHART_COLUMN_PROTOTYPE DROP PRIMARY KEY;

ALTER TABLE CHART_QUERY RENAME TO QUERY_MODEL;

ALTER TABLE CHART_COLUMN_PROTOTYPE RENAME TO QUERY_COLUMN_PROTOTYPE;

ALTER TABLE QUERY_MODEL ALTER COLUMN CHART_QUERY_ID RENAME TO QUERY_MODEL_ID;

ALTER TABLE QUERY_COLUMN_PROTOTYPE ALTER COLUMN CHART_COLUMN_ID RENAME TO QUERY_COLUMN_ID;

ALTER TABLE QUERY_MODEL ADD CONSTRAINT pk_query_model PRIMARY KEY (QUERY_MODEL_ID);

ALTER TABLE QUERY_COLUMN_PROTOTYPE ADD CONSTRAINT pk_query_column_prototype PRIMARY KEY (QUERY_COLUMN_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Rename CHART_QUERY and CHART_COLUMN_PROTOTYPE and their primary keys', NOW(), 'Drop Primary Key (x2), Rename Table (x2), Rename Column (x2), Add Primary Key (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-282-01b-h2-pg', '2.0.5', '3:39c4a51a7aeab50ab97e4598ddd78df5', 457);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-282-01c::bsiri::(Checksum: 3:71c736728cbdf3a00f21545b808ccb51)
-- Adding unique constraint on QUERY_COLUMN_PROTOTYPE.LABEL since its likely to act as
--   		a surrogate primary key.
ALTER TABLE QUERY_COLUMN_PROTOTYPE ALTER COLUMN  LABEL VARCHAR(255) NOT NULL;

ALTER TABLE QUERY_COLUMN_PROTOTYPE ADD UNIQUE (LABEL);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Adding unique constraint on QUERY_COLUMN_PROTOTYPE.LABEL since its likely to act as
  		a surrogate primary key.', NOW(), 'Add Not-Null Constraint, Add Unique Constraint', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-282-01c', '2.0.5', '3:71c736728cbdf3a00f21545b808ccb51', 458);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-282-02::bsiri::(Checksum: 3:3c67da2863694ee856e17df852d8424a)
-- This table indicates how the projected columns are ordered.
CREATE TABLE QUERY_PROJECTION_COLUMN (QUERY_COLUMN_ID BIGINT NOT NULL, QUERY_MODEL_ID BIGINT NOT NULL, LABEL VARCHAR(30), PROJECTION_OPERATION VARCHAR(20), PROJECTION_RANK INT, CUF_ID BIGINT, CONSTRAINT fk_query_projection_query_column FOREIGN KEY (QUERY_COLUMN_ID) REFERENCES QUERY_COLUMN_PROTOTYPE(QUERY_COLUMN_ID), CONSTRAINT fk_query_projection_query_model FOREIGN KEY (QUERY_MODEL_ID) REFERENCES QUERY_MODEL(QUERY_MODEL_ID));

CREATE TABLE QUERY_AGGREGATION_COLUMN (QUERY_MODEL_ID BIGINT NOT NULL, QUERY_COLUMN_ID BIGINT NOT NULL, LABEL VARCHAR(30), AGGREGATION_OPERATION VARCHAR(20), AGGREGATION_RANK INT, CUF_ID BIGINT, CONSTRAINT fk_query_aggr_query_column FOREIGN KEY (QUERY_COLUMN_ID) REFERENCES QUERY_COLUMN_PROTOTYPE(QUERY_COLUMN_ID), CONSTRAINT fk_query_aggr_query_model FOREIGN KEY (QUERY_MODEL_ID) REFERENCES QUERY_MODEL(QUERY_MODEL_ID));

CREATE TABLE QUERY_FILTER_COLUMN (QUERY_FILTER_ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, QUERY_COLUMN_ID BIGINT NOT NULL, QUERY_MODEL_ID BIGINT NOT NULL, FILTER_OPERATION VARCHAR(20), CUF_ID BIGINT, CONSTRAINT PK_QUERY_FILTER_COLUMN PRIMARY KEY (QUERY_FILTER_ID), CONSTRAINT fk_query_filter_query_column FOREIGN KEY (QUERY_COLUMN_ID) REFERENCES QUERY_COLUMN_PROTOTYPE(QUERY_COLUMN_ID), CONSTRAINT fk_query_filter_query_model FOREIGN KEY (QUERY_MODEL_ID) REFERENCES QUERY_MODEL(QUERY_MODEL_ID));

CREATE INDEX idx_query_filter ON QUERY_FILTER_COLUMN(QUERY_FILTER_ID);

CREATE TABLE QUERY_FILTER_VALUES (QUERY_FILTER_ID BIGINT NOT NULL, FILTER_VALUE VARCHAR(255), CONSTRAINT fk_query_filter_value_query_filter FOREIGN KEY (QUERY_FILTER_ID) REFERENCES QUERY_FILTER_COLUMN(QUERY_FILTER_ID));

CREATE TABLE QUERY_ORDERING_COLUMN (QUERY_MODEL_ID BIGINT NOT NULL, QUERY_COLUMN_ID BIGINT NOT NULL, ORDER_OPERATION VARCHAR(20), ORDER_RANK INT, ORDER_DIR CHAR(3) DEFAULT 'ASC', CUF_ID BIGINT, CONSTRAINT fk_query_order_query_model FOREIGN KEY (QUERY_MODEL_ID) REFERENCES QUERY_MODEL(QUERY_MODEL_ID), CONSTRAINT fk_query_order_query_column FOREIGN KEY (QUERY_COLUMN_ID) REFERENCES QUERY_COLUMN_PROTOTYPE(QUERY_COLUMN_ID));

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'This table indicates how the projected columns are ordered.', NOW(), 'Create Table (x3), Create Index, Create Table (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-282-02', '2.0.5', '3:3c67da2863694ee856e17df852d8424a', 459);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-282-03::bsiri::(Checksum: 3:28c51705fab9a500fe5cb63fe01a4c1e)
-- Now the fun part : migrate the former content into the new content
-- --------------------
      -- migration of axis --
      -- --------------------

      insert into QUERY_AGGREGATION_COLUMN (query_model_id, label, aggregation_operation, aggregation_rank, cuf_id, query_column_id)
      select cac.query_id, cac.label, cac.axis_operation, cac.axis_rank, cac.cuf_id, cac.chart_column_id
      from CHART_AXIS_COLUMN cac
      inner join QUERY_MODEL qm on cac.query_id = qm.query_model_id
      where qm.strategy != 'MAIN';

insert into QUERY_ORDERING_COLUMN (query_model_id, query_column_id, order_rank, order_dir, cuf_id, order_operation)
      select qac.query_model_id, qac.query_column_id, qac.aggregation_rank, 'ASC', qac.cuf_id, qac.aggregation_operation
      from QUERY_AGGREGATION_COLUMN qac;

-- ------------------------
      -- migration of measures --
      -- ------------------------

      insert into QUERY_PROJECTION_COLUMN (query_column_id, query_model_id, label, projection_operation, cuf_id, projection_rank)
      select cmc.chart_column_id, cmc.query_id, cmc.label, cmc.measure_operation, cmc.cuf_id, cmc.measure_rank
      from CHART_MEASURE_COLUMN cmc
      inner join QUERY_MODEL qm on cmc.query_id = qm.query_model_id
      where qm.strategy != 'MAIN';

insert into QUERY_ORDERING_COLUMN(query_model_id, query_column_id, order_dir, cuf_id, order_operation, order_rank)
      select cmc.query_id, cmc.chart_column_id, 'ASC', cmc.cuf_id, cmc.measure_operation, cmc.measure_rank + (
        select count(*) from CHART_AXIS_COLUMN cac where cac.query_id = cmc.query_id
      ) as adjust_rank
      from CHART_MEASURE_COLUMN cmc
      inner join QUERY_MODEL qm on cmc.query_id = qm.query_model_id
      where qm.strategy != 'MAIN';

-- -----------------------
      -- migration of filters --
      -- -----------------------

      insert into QUERY_FILTER_COLUMN (query_column_id, query_model_id, filter_operation, cuf_id)
      select cfc.chart_column_id, cfc.query_id, cfc.filter_operation, cfc.cuf_id
      from CHART_FILTER cfc
      inner join QUERY_MODEL qm on cfc.query_id = qm.query_model_id
      where qm.strategy != 'MAIN';

insert into QUERY_FILTER_VALUES (filter_value, query_filter_id)
      select cfv.filter_value, qfc.query_filter_id
      from CHART_FILTER_VALUES cfv
      inner join CHART_FILTER cfc on cfv.filter_id = cfc.filter_id
      inner join QUERY_MODEL qm on cfc.query_id = qm.query_model_id
      inner join QUERY_FILTER_COLUMN qfc on (
        cfc.chart_column_id = qfc.query_column_id and
        cfc.query_id = qfc.query_model_id and
        cfc.filter_operation = qfc.filter_operation
      )
      where qm.strategy != 'MAIN';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Now the fun part : migrate the former content into the new content', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-282-03', '2.0.5', '3:28c51705fab9a500fe5cb63fe01a4c1e', 460);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-282-04a::bsiri::(Checksum: 3:b80a3dbdf7226d13a33f7b4d58347c2d)
-- Creating the new foreign keys from CHART_X_COLUMNS tables, to CHART_DEFINITION. Initially nullable.
ALTER TABLE CHART_AXIS_COLUMN ADD CHART_DEFINITION_ID BIGINT;

ALTER TABLE CHART_AXIS_COLUMN ADD CONSTRAINT fk_chart_axis_chart_def FOREIGN KEY (CHART_DEFINITION_ID) REFERENCES CHART_DEFINITION (CHART_ID);

ALTER TABLE CHART_FILTER ADD CHART_DEFINITION_ID BIGINT;

ALTER TABLE CHART_FILTER ADD CONSTRAINT fk_chart_filter_chart_def FOREIGN KEY (CHART_DEFINITION_ID) REFERENCES CHART_DEFINITION (CHART_ID);

ALTER TABLE CHART_MEASURE_COLUMN ADD CHART_DEFINITION_ID BIGINT;

ALTER TABLE CHART_MEASURE_COLUMN ADD CONSTRAINT fk_chart_meas_chart_def FOREIGN KEY (CHART_DEFINITION_ID) REFERENCES CHART_DEFINITION (CHART_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Creating the new foreign keys from CHART_X_COLUMNS tables, to CHART_DEFINITION. Initially nullable.', NOW(), 'Add Column (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-282-04a', '2.0.5', '3:b80a3dbdf7226d13a33f7b4d58347c2d', 461);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-282-04b::bsiri::(Checksum: 3:f4ebdc99735f257c55ed7b00bae3eacf)
-- Populating the new foreign keys from the tables CHART_X_COLUMN  to CHART_DEFINITION
update CHART_AXIS_COLUMN cac
      set cac.chart_definition_id = (
      select cd.chart_id
      from QUERY_MODEL qm
      inner join CHART_DEFINITION cd on qm.query_model_id = cd.query_id
      where qm.strategy = 'MAIN'
      and cac.query_id = qm.query_model_id
      );

update CHART_FILTER cf
      set cf.chart_definition_id = (
      select cd.chart_id
      from QUERY_MODEL qm
      inner join CHART_DEFINITION cd on qm.query_model_id = cd.query_id
      where qm.strategy = 'MAIN'
      and cf.query_id = qm.query_model_id
      );

update CHART_MEASURE_COLUMN cmc
      set cmc.chart_definition_id = (
      select cd.chart_id
      from QUERY_MODEL qm
      inner join CHART_DEFINITION cd on qm.query_model_id = cd.query_id
      where qm.strategy = 'MAIN'
      and cmc.query_id = qm.query_model_id
      );

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Populating the new foreign keys from the tables CHART_X_COLUMN  to CHART_DEFINITION', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-282-04b', '2.0.5', '3:f4ebdc99735f257c55ed7b00bae3eacf', 462);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-282-04c::bsiri::(Checksum: 3:51fb1d72057ab9b0f7fad9e7161ac8db)
-- also delete the former QUERY_MODELs that previously had user data,
--       because now the CHART_X_COLUMNS now solely rely on their CHART_DEFINITION
delete from CHART_FILTER_VALUES
      where filter_id in (
      select filter_id from CHART_FILTER where chart_definition_id is null
      );

delete from CHART_FILTER where chart_definition_id is null;

delete from CHART_AXIS_COLUMN where chart_definition_id is null;

delete from CHART_MEASURE_COLUMN where chart_definition_id is null;

delete from QUERY_MODEL where strategy = 'MAIN';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'also delete the former QUERY_MODELs that previously had user data,
      because now the CHART_X_COLUMNS now solely rely on their CHART_DEFINITION', NOW(), 'Custom SQL (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-282-04c', '2.0.5', '3:51fb1d72057ab9b0f7fad9e7161ac8db', 463);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-282-04d::bsiri::(Checksum: 3:ab13e31e7ef0823a5e1ecd90dddf317d)
-- Now dropping the now useless columns that pointed to QUERY_MODEL
ALTER TABLE CHART_AXIS_COLUMN DROP COLUMN QUERY_ID;

ALTER TABLE CHART_FILTER DROP COLUMN QUERY_ID;

ALTER TABLE CHART_MEASURE_COLUMN DROP COLUMN QUERY_ID;

ALTER TABLE CHART_DEFINITION DROP COLUMN QUERY_ID;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Now dropping the now useless columns that pointed to QUERY_MODEL', NOW(), 'Drop Column (x4)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-282-04d', '2.0.5', '3:ab13e31e7ef0823a5e1ecd90dddf317d', 464);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-282-05::bsiri::(Checksum: 3:4d9860d29f71395a9be0d4e77a8fc2d9)
-- Rewiring the remaining foreign keys.
ALTER TABLE QUERY_COLUMN_PROTOTYPE ADD CONSTRAINT fk_query_col_query_model FOREIGN KEY (SUBQUERY_ID) REFERENCES QUERY_MODEL (QUERY_MODEL_ID);

ALTER TABLE CHART_COLUMN_ROLE ADD CONSTRAINT fk_column_role_chart_column FOREIGN KEY (CHART_COLUMN_ID) REFERENCES QUERY_COLUMN_PROTOTYPE (QUERY_COLUMN_ID);

ALTER TABLE CHART_AXIS_COLUMN ADD CONSTRAINT fk_axis_column_chart_column FOREIGN KEY (CHART_COLUMN_ID) REFERENCES QUERY_COLUMN_PROTOTYPE (QUERY_COLUMN_ID);

ALTER TABLE CHART_FILTER ADD CONSTRAINT fk_chart_filter_chart_column FOREIGN KEY (CHART_COLUMN_ID) REFERENCES QUERY_COLUMN_PROTOTYPE (QUERY_COLUMN_ID);

ALTER TABLE CHART_MEASURE_COLUMN ADD CONSTRAINT fk_chart_measure_chart_column FOREIGN KEY (CHART_COLUMN_ID) REFERENCES QUERY_COLUMN_PROTOTYPE (QUERY_COLUMN_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Rewiring the remaining foreign keys.', NOW(), 'Add Foreign Key Constraint (x5)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-282-05', '2.0.5', '3:4d9860d29f71395a9be0d4e77a8fc2d9', 465);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-282-06::bsiri::(Checksum: 3:eba5dafc64d9c19d5fcc261c72208226)
-- For the subqueries we have to use (w)hacky SQL. This is because we need to select-insert from no table,
--     which is supported with specific syntax by each table but aren't compatible together (we would need to
--       'select from dual' in postgresql for example).
-- ----------------------------------------------- --
      -- Inserting first the attribute columns. Some of  --
      -- them will be used in new query models, declared --
      -- just after them.                                --
      -- 												 --
      -- Also the first new columns declared are Entity  --
      -- columns : they represent the entity itself and  --
      -- have a COLUMN_TYPE = 'ENTITY'. Only the most 	 --
      -- common entities are represented, entities like  --
      -- RequirementVersionCoverage (for example) aren't --
      -- declared.
      -- ----------------------------------------------- --

      INSERT INTO QUERY_COLUMN_PROTOTYPE (COLUMN_TYPE, ENTITY_TYPE, ENTITY_ROLE, LABEL, ATTRIBUTE_NAME, DATA_TYPE, BUSINESS) VALUES

      ('ENTITY', 'REQUIREMENT', NULL, 'REQUIREMENT_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'REQUIREMENT_VERSION', NULL, 'REQUIREMENT_VERSION_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'TEST_CASE', NULL, 'TEST_CASE_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'CAMPAIGN', NULL, 'CAMPAIGN_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'ITERATION', NULL, 'ITERATION_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'ITEM_TEST_PLAN', NULL, 'ITEM_TEST_PLAN_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'EXECUTION', NULL, 'EXECUTION_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'ISSUE', NULL, 'ISSUE_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'TEST_CASE_STEP', NULL, 'TEST_CASE_STEP_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'INFO_LIST_ITEM', 'TEST_CASE_NATURE', 'TEST_CASE_NATURE_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'INFO_LIST_ITEM', 'TEST_CASE_TYPE', 'TEST_CASE_TYPE_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'INFO_LIST_ITEM', 'REQUIREMENT_VERSION_CATEGORY', 'REQUIREMENT_VERSION_CATEGORY_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'USER', 'ITERATION_TEST_PLAN_ASSIGNED_USER', 'ITERATION_TEST_PLAN_ASSIGNED_USER_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'MILESTONE', 'TEST_CASE_MILESTONE', 'TEST_CASE_MILESTONE_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'MILESTONE', 'REQUIREMENT_VERSION_MILESTONE', 'REQUIREMENT_VERSION_MILESTONE_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'MILESTONE', 'CAMPAIGN_MILESTONE', 'CAMPAIGN_MILESTONE_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'AUTOMATED_TEST', NULL, 'AUTOMATED_TEST_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'AUTOMATED_EXECUTION_EXTENDER', NULL, 'AUTOMATED_EXECUTION_EXTENDER_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'ATTACHMENT_LIST', 'TEST_CASE_ATTLIST', 'TEST_CASE_ATTLIST_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'ATTACHMENT_LIST', 'REQUIREMENT_VERSION_ATTLIST', 'REQUIREMENT_VERSION_ATTLIST_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'ATTACHMENT_LIST', 'CAMPAIGN_ATTLIST', 'CAMPAIGN_ATTLIST_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'ATTACHMENT', 'TEST_CASE_ATTACHMENT', 'TEST_CASE_ATTACHMENT_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'ATTACHMENT', 'CAMPAIGN_ATTACHMENT', 'CAMPAIGN_ATTACHMENT_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'ATTACHMENT', 'REQUIREMENT_VERSION_ATTACHMENT', 'REQUIREMENT_VERSION_ATTACHMENT_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'DATASET', NULL, 'DATASET_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'PARAMETER', NULL, 'PARAMETER_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'PROJECT', 'TEST_CASE_PROJECT', 'TEST_CASE_PROJECT_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'PROJECT', 'REQUIREMENT_PROJECT', 'REQUIREMENT_PROJECT_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'PROJECT', 'CAMPAIGN_PROJECT', 'CAMPAIGN_PROJECT_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'TEST_SUITE', 'ITEM_SUITE', 'ITEM_SUITE_ENTITY', NULL, 'ENTITY', FALSE),
      ('ENTITY', 'AUTOMATION_REQUEST', NULL, 'AUTOMATION_REQUEST_ENTITY', NULL, 'ENTITY', FALSE),


      ('ATTRIBUTE', 'MILESTONE', 'CAMPAIGN_MILESTONE', 'CAMPAIGN_MILESTONE_LABEL', 'label', 'STRING', FALSE),


      ('ATTRIBUTE', 'PROJECT', 'TEST_CASE_PROJECT', 'TEST_CASE_PROJECT_ID', 'id', 'NUMERIC', FALSE),
      ('ATTRIBUTE', 'PROJECT', 'TEST_CASE_PROJECT', 'TEST_CASE_PROJECT_NAME', 'name', 'STRING', FALSE),
      ('ATTRIBUTE', 'PROJECT', 'REQUIREMENT_PROJECT', 'REQUIREMENT_PROJECT_ID', 'id', 'NUMERIC', FALSE),
      ('ATTRIBUTE', 'PROJECT', 'REQUIREMENT_PROJECT', 'REQUIREMENT_PROJECT_NAME', 'name', 'STRING', FALSE),
      ('ATTRIBUTE', 'PROJECT', 'CAMPAIGN_PROJECT', 'CAMPAIGN_PROJECT_ID', 'id', 'NUMERIC', FALSE),
      ('ATTRIBUTE', 'PROJECT', 'CAMPAIGN_PROJECT', 'CAMPAIGN_PROJECT_NAME', 'name', 'STRING', FALSE),


      ('ATTRIBUTE', 'ATTACHMENT_LIST', 'TEST_CASE_ATTLIST', 'TEST_CASE_ATTLIST_ID', 'id', 'NUMERIC', FALSE),
      ('ATTRIBUTE', 'ATTACHMENT_LIST', 'REQUIREMENT_VERSION_ATTLIST', 'REQUIREMENT_VERSION_ATTLIST_ID', 'id', 'NUMERIC', FALSE),
      ('ATTRIBUTE', 'ATTACHMENT_LIST', 'CAMPAIGN_ATTLIST', 'CAMPAIGN_ATTLIST_ID', 'id', 'NUMERIC', FALSE),

      ('ATTRIBUTE', 'ATTACHMENT', 'TEST_CASE_ATTACHMENT', 'TEST_CASE_ATTACHMENT_ID', 'id', 'NUMERIC', FALSE),
      ('ATTRIBUTE', 'ATTACHMENT', 'REQUIREMENT_VERSION_ATTACHMENT', 'REQUIREMENT_VERSION_ATTACHMENT_ID', 'id', 'NUMERIC', FALSE),
      ('ATTRIBUTE', 'ATTACHMENT', 'CAMPAIGN_ATTACHMENT', 'CAMPAIGN_ATTACHMENT_ID', 'id', 'NUMERIC', FALSE),

      ('ATTRIBUTE', 'DATASET', NULL, 'DATASET_ID', 'id', 'NUMERIC', FALSE),
      ('ATTRIBUTE', 'PARAMETER', NULL, 'PARAMETER_ID', 'id', 'NUMERIC', FALSE),

      ('ATTRIBUTE', 'MILESTONE', 'TEST_CASE_MILESTONE',   'TEST_CASE_MILESTONE_STATUS', 'status', 'LEVEL_ENUM', FALSE),
      ('ATTRIBUTE', 'MILESTONE', 'TEST_CASE_MILESTONE',   'TEST_CASE_MILESTONE_END_DATE', 'endDate', 'DATE', FALSE),
      ('ATTRIBUTE', 'MILESTONE', 'REQUIREMENT_VERSION_MILESTONE', 'REQUIREMENT_VERSION_MILESTONE_STATUS', 'status', 'LEVEL_ENUM', FALSE),
      ('ATTRIBUTE', 'MILESTONE', 'REQUIREMENT_VERSION_MILESTONE', 'REQUIREMENT_VERSION_MILESTONE_END_DATE', 'endDate', 'DATE', FALSE),
      ('ATTRIBUTE', 'MILESTONE', 'CAMPAIGN_MILESTONE',    'CAMPAIGN_MILESTONE_STATUS', 'status', 'LEVEL_ENUM', FALSE),
      ('ATTRIBUTE', 'MILESTONE', 'CAMPAIGN_MILESTONE',    'CAMPAIGN_MILESTONE_END_DATE', 'endDate', 'DATE', FALSE),

      ('ATTRIBUTE', 'AUTOMATION_REQUEST', NULL, 'AUTOMATION_REQUEST_ID', 'id', 'NUMERIC', FALSE),
      ('ATTRIBUTE', 'AUTOMATION_REQUEST', NULL, 'AUTOMATION_REQUEST_STATUS', 'requestStatus', 'LEVEL_ENUM', FALSE),

      ('ATTRIBUTE', 'REQUIREMENT_VERSION', NULL, 'REQUIREMENT_VERSION_NAME', 'name', 'STRING', FALSE),
      ('ATTRIBUTE', 'REQUIREMENT_VERSION', NULL, 'REQUIREMENT_VERSION_DESCRIPTION', 'description', 'STRING', FALSE),

      ('ATTRIBUTE', 'TEST_CASE', NULL, 'TEST_CASE_NAME', 'name', 'STRING', FALSE),
      ('ATTRIBUTE', 'TEST_CASE', NULL, 'TEST_CASE_DESCRIPTION', 'description', 'STRING', FALSE),
      ('ATTRIBUTE', 'TEST_CASE', NULL, 'TEST_CASE_KIND', 'kind', 'LEVEL_ENUM', FALSE),
      ('ATTRIBUTE', 'TEST_CASE', NULL, 'TEST_CASE_PREQUISITE', 'prerequisite', 'STRING', FALSE),
      ('ATTRIBUTE', 'TEST_CASE', NULL, 'TEST_CASE_AUTOMATABLE', 'automatable', 'LEVEL_ENUM', FALSE),

      ('ATTRIBUTE', 'CAMPAIGN', NULL, 'CAMPAIGN_NAME', 'name', 'STRING', FALSE),

      ('ATTRIBUTE', 'ITERATION', NULL, 'ITERATION_NAME', 'name', 'STRING', FALSE),

      ('ATTRIBUTE', 'TEST_SUITE', 'ITEM_SUITE', 'ITEM_SUITE_ID', 'id', 'NUMERIC', FALSE),

      ('ATTRIBUTE', 'EXECUTION', NULL, 'EXECUTION_EXECUTION_MODE', 'executionMode', 'LEVEL_ENUM', FALSE),
      ('ATTRIBUTE', 'EXECUTION', NULL, 'EXECUTION_LAST_EXECUTED_ON', 'lastExecutedOn', 'DATE', FALSE),

      ('ATTRIBUTE', 'EXECUTION', NULL, 'ITEM_TEST_PLAN_LASTEXECBY', 'lastExecutedBy', 'STRING', FALSE);

-- ----------------------------------------------- --
      -- Inserting subqueries that implement the         --
      -- 'CALCULATED' columns defined right after them   --
      -- ----------------------------------------------- --


      -- requirement version attachment count subquery
      -- the inserts follow the order : query_model, aggregation, projection and finally the query_column_prototype

      INSERT INTO QUERY_MODEL (NAME, STRATEGY, JOIN_STYLE) values ('REQUIREMENT_VERSION_ATTCOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

INSERT INTO QUERY_AGGREGATION_COLUMN(QUERY_MODEL_ID, QUERY_COLUMN_ID, AGGREGATION_OPERATION, AGGREGATION_RANK)
        select
          (select max(QUERY_MODEL_ID) from QUERY_MODEL),
          (select QUERY_COLUMN_ID from QUERY_COLUMN_PROTOTYPE where label = 'REQUIREMENT_VERSION_ID'),
          'NONE', 0
        from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

INSERT INTO QUERY_PROJECTION_COLUMN(QUERY_MODEL_ID, QUERY_COLUMN_ID, PROJECTION_OPERATION, PROJECTION_RANK)
        select
          (select max(QUERY_MODEL_ID) from QUERY_MODEL),
          (select QUERY_COLUMN_ID from QUERY_COLUMN_PROTOTYPE where label = 'REQUIREMENT_VERSION_ATTACHMENT_ID'),
          'COUNT', 0
        from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

INSERT INTO QUERY_COLUMN_PROTOTYPE (COLUMN_TYPE, ENTITY_TYPE, ENTITY_ROLE, LABEL, ATTRIBUTE_NAME, DATA_TYPE, BUSINESS, SUBQUERY_ID)
        select
          'CALCULATED', 'REQUIREMENT_VERSION', NULL, 'REQUIREMENT_VERSION_ATTCOUNT', 'count(attachment)', 'NUMERIC', FALSE,
          (select max(QUERY_MODEL_ID) from QUERY_MODEL)
        from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

-- test case attachment count subquery
      -- the inserts follow the order : query_model, aggregation, projection and finally the query_column_prototype

      INSERT INTO QUERY_MODEL (NAME, STRATEGY, JOIN_STYLE) values ('TEST_CASE_ATTCOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

INSERT INTO QUERY_AGGREGATION_COLUMN(QUERY_MODEL_ID, QUERY_COLUMN_ID, AGGREGATION_OPERATION, AGGREGATION_RANK)
        select
          (select max(QUERY_MODEL_ID) from QUERY_MODEL),
          (select QUERY_COLUMN_ID from QUERY_COLUMN_PROTOTYPE where label = 'TEST_CASE_ID'),
          'NONE', 0
        from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

INSERT INTO QUERY_PROJECTION_COLUMN(QUERY_MODEL_ID, QUERY_COLUMN_ID, PROJECTION_OPERATION, PROJECTION_RANK)
        select
          (select max(QUERY_MODEL_ID) from QUERY_MODEL),
          (select QUERY_COLUMN_ID from QUERY_COLUMN_PROTOTYPE where label = 'TEST_CASE_ATTACHMENT_ID'),
          'COUNT', 0
        from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

INSERT INTO QUERY_COLUMN_PROTOTYPE (COLUMN_TYPE, ENTITY_TYPE, ENTITY_ROLE, LABEL, ATTRIBUTE_NAME, DATA_TYPE, BUSINESS, SUBQUERY_ID)
      select
      'CALCULATED', 'TEST_CASE', NULL, 'TEST_CASE_ATTCOUNT', 'count(attachment)', 'NUMERIC', FALSE,
      (select max(QUERY_MODEL_ID) from QUERY_MODEL)
      from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

-- test case parameter count subquery
      -- the inserts follow the order : query_model, aggregation, projection and finally the query_column_prototype

      INSERT INTO QUERY_MODEL (NAME, STRATEGY, JOIN_STYLE) values ('TEST_CASE_PARAMCOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

INSERT INTO QUERY_AGGREGATION_COLUMN(QUERY_MODEL_ID, QUERY_COLUMN_ID, AGGREGATION_OPERATION, AGGREGATION_RANK)
      select
      (select max(QUERY_MODEL_ID) from QUERY_MODEL),
      (select QUERY_COLUMN_ID from QUERY_COLUMN_PROTOTYPE where label = 'TEST_CASE_ID'),
      'NONE', 0
      from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

INSERT INTO QUERY_PROJECTION_COLUMN(QUERY_MODEL_ID, QUERY_COLUMN_ID, PROJECTION_OPERATION, PROJECTION_RANK)
      select
      (select max(QUERY_MODEL_ID) from QUERY_MODEL),
      (select QUERY_COLUMN_ID from QUERY_COLUMN_PROTOTYPE where label = 'PARAMETER_ID'),
      'COUNT', 0
      from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

INSERT INTO QUERY_COLUMN_PROTOTYPE (COLUMN_TYPE, ENTITY_TYPE, ENTITY_ROLE, LABEL, ATTRIBUTE_NAME, DATA_TYPE, BUSINESS, SUBQUERY_ID)
      select
      'CALCULATED', 'TEST_CASE', NULL, 'TEST_CASE_PARAMCOUNT', 'count(parameter)', 'NUMERIC', FALSE,
      (select max(QUERY_MODEL_ID) from QUERY_MODEL)
      from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

-- test case dataset count subquery
      -- the inserts follow the order : query_model, aggregation, projection and finally the query_column_prototype

      INSERT INTO QUERY_MODEL (NAME, STRATEGY, JOIN_STYLE) values ('TEST_CASE_DATASETCOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

INSERT INTO QUERY_AGGREGATION_COLUMN(QUERY_MODEL_ID, QUERY_COLUMN_ID, AGGREGATION_OPERATION, AGGREGATION_RANK)
      select
      (select max(QUERY_MODEL_ID) from QUERY_MODEL),
      (select QUERY_COLUMN_ID from QUERY_COLUMN_PROTOTYPE where label = 'TEST_CASE_ID'),
      'NONE', 0
      from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

INSERT INTO QUERY_PROJECTION_COLUMN(QUERY_MODEL_ID, QUERY_COLUMN_ID, PROJECTION_OPERATION, PROJECTION_RANK)
      select
      (select max(QUERY_MODEL_ID) from QUERY_MODEL),
      (select QUERY_COLUMN_ID from QUERY_COLUMN_PROTOTYPE where label = 'DATASET_ID'),
      'COUNT', 0
      from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

INSERT INTO QUERY_COLUMN_PROTOTYPE (COLUMN_TYPE, ENTITY_TYPE, ENTITY_ROLE, LABEL, ATTRIBUTE_NAME, DATA_TYPE, BUSINESS, SUBQUERY_ID)
      select
      'CALCULATED', 'TEST_CASE', NULL, 'TEST_CASE_DATASETCOUNT', 'count(dataset)', 'NUMERIC', FALSE,
      (select max(QUERY_MODEL_ID) from QUERY_MODEL)
      from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

-- item suite count subquery
      -- the inserts follow the order : query_model, aggregation, projection and finally the query_column_prototype

      INSERT INTO QUERY_MODEL (NAME, STRATEGY, JOIN_STYLE) values ('ITEM_TEST_PLAN_SUITECOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

INSERT INTO QUERY_AGGREGATION_COLUMN(QUERY_MODEL_ID, QUERY_COLUMN_ID, AGGREGATION_OPERATION, AGGREGATION_RANK)
      select
      (select max(QUERY_MODEL_ID) from QUERY_MODEL),
      (select QUERY_COLUMN_ID from QUERY_COLUMN_PROTOTYPE where label = 'ITEM_TEST_PLAN_ID'),
      'NONE', 0
      from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

INSERT INTO QUERY_PROJECTION_COLUMN(QUERY_MODEL_ID, QUERY_COLUMN_ID, PROJECTION_OPERATION, PROJECTION_RANK)
      select
      (select max(QUERY_MODEL_ID) from QUERY_MODEL),
      (select QUERY_COLUMN_ID from QUERY_COLUMN_PROTOTYPE where label = 'ITEM_SUITE_ID'),
      'COUNT', 0
      from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

INSERT INTO QUERY_COLUMN_PROTOTYPE (COLUMN_TYPE, ENTITY_TYPE, ENTITY_ROLE, LABEL, ATTRIBUTE_NAME, DATA_TYPE, BUSINESS, SUBQUERY_ID)
      select
      'CALCULATED', 'ITEM_TEST_PLAN', NULL, 'ITEM_TEST_PLAN_SUITECOUNT', 'count(testSuites)', 'NUMERIC', FALSE,
      (select max(QUERY_MODEL_ID) from QUERY_MODEL)
      from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

-- item dataset count subquery
      -- the inserts follow the order : query_model, aggregation, projection and finally the query_column_prototype

      INSERT INTO QUERY_MODEL (NAME, STRATEGY, JOIN_STYLE) values ('ITEM_TEST_PLAN_DSCOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

INSERT INTO QUERY_AGGREGATION_COLUMN(QUERY_MODEL_ID, QUERY_COLUMN_ID, AGGREGATION_OPERATION, AGGREGATION_RANK)
      select
      (select max(QUERY_MODEL_ID) from QUERY_MODEL),
      (select QUERY_COLUMN_ID from QUERY_COLUMN_PROTOTYPE where label = 'ITEM_TEST_PLAN_ID'),
      'NONE', 0
      from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

INSERT INTO QUERY_PROJECTION_COLUMN(QUERY_MODEL_ID, QUERY_COLUMN_ID, PROJECTION_OPERATION, PROJECTION_RANK)
      select
      (select max(QUERY_MODEL_ID) from QUERY_MODEL),
      (select QUERY_COLUMN_ID from QUERY_COLUMN_PROTOTYPE where label = 'DATASET_ID'),
      'COUNT', 0
      from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

INSERT INTO QUERY_COLUMN_PROTOTYPE (COLUMN_TYPE, ENTITY_TYPE, ENTITY_ROLE, LABEL, ATTRIBUTE_NAME, DATA_TYPE, BUSINESS, SUBQUERY_ID)
      select
      'CALCULATED', 'ITEM_TEST_PLAN', NULL, 'ITEM_TEST_PLAN_DSCOUNT', 'count(datasets)', 'NUMERIC', FALSE,
      (select max(QUERY_MODEL_ID) from QUERY_MODEL)
      from QUERY_COLUMN_PROTOTYPE where query_column_id = 1;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'For the subqueries we have to use (w)hacky SQL. This is because we need to select-insert from no table,
    which is supported with specific syntax by each table but aren''t compatible together (we would need to
      ''select from dual'' in postgresql fo...', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-282-06', '2.0.5', '3:eba5dafc64d9c19d5fcc261c72208226', 466);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml::tm-1.20.0-TM-282-delete-lastindexing-properties::jprioux::(Checksum: 3:32d0bfb9bce4e8ddd926e1917d15933b)
-- Delete all lastindexing properties from core config
DELETE FROM CORE_CONFIG WHERE STR_KEY LIKE 'lastindexing.%';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jprioux', 'Delete all lastindexing properties from core config', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.20.0.xml', 'tm-1.20.0-TM-282-delete-lastindexing-properties', '2.0.5', '3:32d0bfb9bce4e8ddd926e1917d15933b', 467);

-- Release Database Lock
-- Release Database Lock
