-- *********************************************************************
-- 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.3.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.3.0.xml::tm-1.3.0.01::gfouquet::(Checksum: 3:a485a541f9f651ccfaff9d8e6417d1c3)
-- Adds a TM database version number
UPDATE CORE_CONFIG SET STR_KEY = 'squashtest.tm.database.version', VALUE = '1.3.0' WHERE STR_KEY = 'squashtest.tm.database.version';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Adds a 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.3.0.xml', 'tm-1.3.0.01', '2.0.5', '3:a485a541f9f651ccfaff9d8e6417d1c3', 124);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0.2::gfouquet::(Checksum: 3:c640d8ac650fe8f6006086929242d891)
-- Add a default group to users who do not have one (should be users from alpha versions)
create temporary table GROUPLESS_USERS (USER_ID INTEGER);

insert
			into GROUPLESS_USERS (USER_ID)
			select cu.ID from CORE_USER cu
			where
			cu.ID not in (select cgm.USER_ID from CORE_GROUP_MEMBER cgm);

insert
			into CORE_GROUP_MEMBER (USER_ID, GROUP_ID)
			select cu.ID, cg.ID
			from
			CORE_USER cu, CORE_GROUP cg
			where cg.QUALIFIED_NAME =
			'squashtest.tm.group.User'
			and cu.ID in (select USER_ID from
			GROUPLESS_USERS);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Add a default group to users who do not have one (should be users from alpha versions)', 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.3.0.xml', 'tm-1.3.0.2', '2.0.5', '3:c640d8ac650fe8f6006086929242d891', 125);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1112.0::bsiri::(Checksum: 3:d41d8cd98f00b204e9800998ecf8427e)
-- The following serie of changeset labelled tm-1.3-feat-1112.x implements the closure table
-- 			modelization for tree-like data	at 
-- 			http://www.mysqlperformanceblog.com/2011/02/14/moving-subtrees-in-closure-table/, 
-- 			with a focus on the mysql-specifics shortcomings regarding modifying a table already
-- 			under modification. 
-- 			
-- 			Big kudos to you Bill Karwin !
INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'The following serie of changeset labelled tm-1.3-feat-1112.x implements the closure table
			modelization for tree-like data	at 
			http://www.mysqlperformanceblog.com/2011/02/14/moving-subtrees-in-closure-table/, 
			with a focus on the mysql-specific...', NOW(), 'Empty', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1112.0', '2.0.5', '3:d41d8cd98f00b204e9800998ecf8427e', 126);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1112.01::bsiri::(Checksum: 3:99fd3365d5a4e2e5ab22251b964eb0e4)
-- closure table for test case library nodes. Its content is set and modified by four triggers, see 
-- 			changeset 'tm-1.3-feat-1112.05-mysql' or 'tm-1.3-feat-1112.05-h2'.
CREATE TABLE TCLN_RELATIONSHIP_CLOSURE (ANCESTOR_ID BIGINT NOT NULL, DESCENDANT_ID BIGINT NOT NULL, DEPTH SMALLINT NOT NULL, CONSTRAINT fk_tcln_relationship_clos_desc FOREIGN KEY (DESCENDANT_ID) REFERENCES TEST_CASE_LIBRARY_NODE(TCLN_ID), CONSTRAINT fk_tcln_relationship_clos_anc FOREIGN KEY (ANCESTOR_ID) REFERENCES TEST_CASE_LIBRARY_NODE(TCLN_ID));

CREATE INDEX idx_tcln_relationship_clos_desc ON TCLN_RELATIONSHIP_CLOSURE(DESCENDANT_ID);

CREATE INDEX idx_tcln_relationship_clos_anc ON TCLN_RELATIONSHIP_CLOSURE(ANCESTOR_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'closure table for test case library nodes. Its content is set and modified by four triggers, see 
			changeset ''tm-1.3-feat-1112.05-mysql'' or ''tm-1.3-feat-1112.05-h2''.', NOW(), 'Create Table, Create Index (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1112.01', '2.0.5', '3:99fd3365d5a4e2e5ab22251b964eb0e4', 127);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1112.02::bsiri::(Checksum: 3:441617d39b17884a498947ee0356236a)
-- closure table for requirement library nodes. Its content is set and modified by four triggers, see 
-- 			changeset 'tm-1.3-feat-1112.06-mysql' or 'tm-1.3-feat-1112.06-h2'.
CREATE TABLE RLN_RELATIONSHIP_CLOSURE (ANCESTOR_ID BIGINT NOT NULL, DESCENDANT_ID BIGINT NOT NULL, DEPTH SMALLINT NOT NULL, CONSTRAINT fk_rln_relationship_clos_anc FOREIGN KEY (ANCESTOR_ID) REFERENCES REQUIREMENT_LIBRARY_NODE(RLN_ID), CONSTRAINT fk_rln_relationship_clos_desc FOREIGN KEY (DESCENDANT_ID) REFERENCES REQUIREMENT_LIBRARY_NODE(RLN_ID));

CREATE INDEX idx_rln_relationship_clos_desc ON RLN_RELATIONSHIP_CLOSURE(DESCENDANT_ID);

CREATE INDEX idx_rln_relationship_clos_anc ON RLN_RELATIONSHIP_CLOSURE(ANCESTOR_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'closure table for requirement library nodes. Its content is set and modified by four triggers, see 
			changeset ''tm-1.3-feat-1112.06-mysql'' or ''tm-1.3-feat-1112.06-h2''.', NOW(), 'Create Table, Create Index (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1112.02', '2.0.5', '3:441617d39b17884a498947ee0356236a', 128);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1112.03::bsiri::(Checksum: 3:adf216326aaf2837331d8a203b98f174)
-- closure table for campaign library nodes. Its content is set and modified by four triggers, see 
-- 			changeset 'tm-1.3-feat-1112.07-mysql' or 'tm-1.3-feat-1112.07-h2'.
CREATE TABLE CLN_RELATIONSHIP_CLOSURE (ANCESTOR_ID BIGINT NOT NULL, DESCENDANT_ID BIGINT NOT NULL, DEPTH SMALLINT NOT NULL, CONSTRAINT fk_cln_relationship_clos_desc FOREIGN KEY (DESCENDANT_ID) REFERENCES CAMPAIGN_LIBRARY_NODE(CLN_ID), CONSTRAINT fk_cln_relationship_clos_anc FOREIGN KEY (ANCESTOR_ID) REFERENCES CAMPAIGN_LIBRARY_NODE(CLN_ID));

CREATE INDEX idx_cln_relationship_clos_desc ON CLN_RELATIONSHIP_CLOSURE(DESCENDANT_ID);

CREATE INDEX idx_cln_relationship_clos_anc ON CLN_RELATIONSHIP_CLOSURE(ANCESTOR_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'closure table for campaign library nodes. Its content is set and modified by four triggers, see 
			changeset ''tm-1.3-feat-1112.07-mysql'' or ''tm-1.3-feat-1112.07-h2''.', NOW(), 'Create Table, Create Index (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1112.03', '2.0.5', '3:adf216326aaf2837331d8a203b98f174', 129);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1112.04::bsiri::(Checksum: 3:c8cc273c614bb28a8fcdb028c25dbc3f)
-- first step of the migration : creation of temporary tables in which we will dump 
-- 			the X_RELATIONSHIP tables, then truncate the later. We will reinsert the data once 
-- 			the triggers are all set.
CREATE TABLE TCLN_RELATIONSHIP_TMP (ANCESTOR_ID BIGINT, DESCENDANT_ID BIGINT);

CREATE TABLE RLN_RELATIONSHIP_TMP (ANCESTOR_ID BIGINT, DESCENDANT_ID BIGINT);

CREATE TABLE CLN_RELATIONSHIP_TMP (ANCESTOR_ID BIGINT, DESCENDANT_ID BIGINT);

insert into TCLN_RELATIONSHIP_TMP
			select * from TCLN_RELATIONSHIP;

insert into RLN_RELATIONSHIP_TMP
			select * from RLN_RELATIONSHIP;

insert into CLN_RELATIONSHIP_TMP
			select * from CLN_RELATIONSHIP;

delete from TCLN_RELATIONSHIP;

delete from RLN_RELATIONSHIP;

delete from CLN_RELATIONSHIP;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'first step of the migration : creation of temporary tables in which we will dump 
			the X_RELATIONSHIP tables, then truncate the later. We will reinsert the data once 
			the triggers are all set.', NOW(), 'Create Table (x3), Custom SQL (x6)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1112.04', '2.0.5', '3:c8cc273c614bb28a8fcdb028c25dbc3f', 130);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1112.05-h2::bsiri::(Checksum: 3:e2fcd15c0dd43cb2ee1ea666a7f13877)
-- h2-triggers for TCLN_RELATIONSHIP_CLOSURE table
create trigger ins_tcln_after after insert on
			TEST_CASE_LIBRARY_NODE
			for each row call
			"org.squashtest.csp.h2.triggers.TCLNAfterInsert";

create trigger del_tcln_before before delete on
			TEST_CASE_LIBRARY_NODE
			for each row call
			"org.squashtest.csp.h2.triggers.TCLNBeforeDelete";

create trigger attach_tcln_after after insert on
			TCLN_RELATIONSHIP
			for each row call
			"org.squashtest.csp.h2.triggers.TCLNAfterAttach";

create trigger detach_tcln_before before delete on
			TCLN_RELATIONSHIP
			for each row call
			"org.squashtest.csp.h2.triggers.TCLNBeforeDetach";

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'h2-triggers for TCLN_RELATIONSHIP_CLOSURE table', NOW(), 'Custom SQL (x4)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1112.05-h2', '2.0.5', '3:e2fcd15c0dd43cb2ee1ea666a7f13877', 131);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1112.06-h2::bsiri::(Checksum: 3:39a67134a8801c6f31020bb1bedcde57)
-- h2-triggers for RLN_RELATIONSHIP_CLOSURE table
create trigger ins_rln_after after insert on
			REQUIREMENT_LIBRARY_NODE
			for each row call
			"org.squashtest.csp.h2.triggers.RLNAfterInsert";

create trigger del_rln_before before delete on
			REQUIREMENT_LIBRARY_NODE
			for each row call
			"org.squashtest.csp.h2.triggers.RLNBeforeDelete";

create trigger attach_rln_after after insert on RLN_RELATIONSHIP
			for each row call "org.squashtest.csp.h2.triggers.RLNAfterAttach";

create trigger detach_rln_before before delete on
			RLN_RELATIONSHIP
			for each row call
			"org.squashtest.csp.h2.triggers.RLNBeforeDetach";

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'h2-triggers for RLN_RELATIONSHIP_CLOSURE table', NOW(), 'Custom SQL (x4)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1112.06-h2', '2.0.5', '3:39a67134a8801c6f31020bb1bedcde57', 132);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1112.07-h2::bsiri::(Checksum: 3:ae35491aff13e7c16e580312374b6ef5)
-- h2-triggers for CLN_RELATIONSHIP_CLOSURE table
create trigger ins_cln_after after insert on
			CAMPAIGN_LIBRARY_NODE
			for each row call
			"org.squashtest.csp.h2.triggers.CLNAfterInsert";

create trigger del_cln_before before delete on
			CAMPAIGN_LIBRARY_NODE
			for each row call
			"org.squashtest.csp.h2.triggers.CLNBeforeDelete";

create trigger attach_cln_after after insert on CLN_RELATIONSHIP
			for each row call "org.squashtest.csp.h2.triggers.CLNAfterAttach";

create trigger detach_cln_before before delete on
			CLN_RELATIONSHIP
			for each row call
			"org.squashtest.csp.h2.triggers.CLNBeforeDetach";

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'h2-triggers for CLN_RELATIONSHIP_CLOSURE table', NOW(), 'Custom SQL (x4)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1112.07-h2', '2.0.5', '3:ae35491aff13e7c16e580312374b6ef5', 133);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1112.08::bsiri::(Checksum: 3:8fe9ae051c17a059358fb13d14f2ed29)
-- this step is mandatory if we want the next one to work (triggers need the nodes to have self-reference in the closure tables)
insert into TCLN_RELATIONSHIP_CLOSURE
			select tcln_id, tcln_id, 0
			from TEST_CASE_LIBRARY_NODE
			where deleted_on is null;

insert into RLN_RELATIONSHIP_CLOSURE
			select rln_id, rln_id, 0
			from
			REQUIREMENT_LIBRARY_NODE
			where deleted_on is null;

insert into CLN_RELATIONSHIP_CLOSURE
			select cln_id, cln_id, 0
			from
			CAMPAIGN_LIBRARY_NODE
			where deleted_on is null;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'this step is mandatory if we want the next one to work (triggers need the nodes to have self-reference in the closure tables)', NOW(), 'Custom SQL (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1112.08', '2.0.5', '3:8fe9ae051c17a059358fb13d14f2ed29', 134);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1112.09::bsiri::(Checksum: 3:639e7a0abd3bc0a34b286a770910f8b4)
-- we can now reinsert our data from the tmp tables and let the triggers do the job
insert into TCLN_RELATIONSHIP
			select * from TCLN_RELATIONSHIP_TMP;

insert into RLN_RELATIONSHIP
			select * from RLN_RELATIONSHIP_TMP;

insert into CLN_RELATIONSHIP
			select * from CLN_RELATIONSHIP_TMP;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'we can now reinsert our data from the tmp tables and let the triggers do the job', NOW(), 'Custom SQL (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1112.09', '2.0.5', '3:639e7a0abd3bc0a34b286a770910f8b4', 135);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1112.10::bsiri::(Checksum: 3:d0a27d40e8e9b9efb5ebe6a91abb6aca)
-- If success, we can now drop our temp tables and complete the migration
DROP TABLE TCLN_RELATIONSHIP_TMP;

DROP TABLE RLN_RELATIONSHIP_TMP;

DROP TABLE CLN_RELATIONSHIP_TMP;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'If success, we can now drop our temp tables and complete the migration', NOW(), 'Drop Table (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1112.10', '2.0.5', '3:d0a27d40e8e9b9efb5ebe6a91abb6aca', 136);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1209.01::mpagnon::(Checksum: 3:defbf8305a7fce0c9d2f6523331d888c)
-- To connect Squash TM to a bugtracker, you must provide the name and the url of the bugtracker. The mantis plugin is shipped with the application
-- 	 IFRAME_FRIENDLY :tells if the bugtracker page can be accessed through an iframe : if the response header of the bugtracker page has the property [X-Frame-Options = DENY] the answer is false
-- 	 URL : must point to the base name of the end point. Eg : http://www.yourdomain.com/mantis . Tips : Prefer IPs over localhost and domain names over IPs. If you don't some minor issues may occur when reporting an issue.
CREATE TABLE BUGTRACKER (BUGTRACKER_ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, NAME VARCHAR(50) NOT NULL, KIND VARCHAR(50) DEFAULT 'mantis' NOT NULL, URL VARCHAR(255) NOT NULL, IFRAME_FRIENDLY BOOLEAN DEFAULT FALSE NOT NULL, CONSTRAINT PK_BUGTRACKER PRIMARY KEY (BUGTRACKER_ID));

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'To connect Squash TM to a bugtracker, you must provide the name and the url of the bugtracker. The mantis plugin is shipped with the application
	 IFRAME_FRIENDLY :tells if the bugtracker page can be accessed through an iframe : if the response header ...', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1209.01', '2.0.5', '3:defbf8305a7fce0c9d2f6523331d888c', 137);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1181.01::bsiri::(Checksum: 3:089301e7802af061a7135098a5805bdd)
-- creating table TEST_AUTOMATION_SERVER
CREATE TABLE TEST_AUTOMATION_SERVER (SERVER_ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, BASE_URL VARCHAR(255) NOT NULL, LOGIN VARCHAR(50) NOT NULL, PASSWORD VARCHAR(255) NOT NULL, KIND VARCHAR(30) DEFAULT 'jenkins' NOT NULL, CONSTRAINT PK_TEST_AUTOMATION_SERVER PRIMARY KEY (SERVER_ID));

CREATE INDEX idx_autotest_server ON TEST_AUTOMATION_SERVER(SERVER_ID);

ALTER TABLE TEST_AUTOMATION_SERVER ADD CONSTRAINT uni_autotest_server UNIQUE (BASE_URL, LOGIN);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'creating table TEST_AUTOMATION_SERVER', NOW(), 'Create Table, Create Index, 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.3.0.xml', 'tm-1.3.0-feat-1181.01', '2.0.5', '3:089301e7802af061a7135098a5805bdd', 138);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1181.02::bsiri::(Checksum: 3:62bfccf08ab461a9202a7288e1df89be)
-- creating table TEST_AUTOMATION_PROJECT
CREATE TABLE TEST_AUTOMATION_PROJECT (PROJECT_ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, NAME VARCHAR(50) NOT NULL, SERVER_ID BIGINT, CONSTRAINT PK_TEST_AUTOMATION_PROJECT PRIMARY KEY (PROJECT_ID), CONSTRAINT fk_auto_test_project FOREIGN KEY (SERVER_ID) REFERENCES TEST_AUTOMATION_SERVER(SERVER_ID));

CREATE INDEX idx_auto_test_project ON TEST_AUTOMATION_PROJECT(PROJECT_ID);

ALTER TABLE TEST_AUTOMATION_PROJECT ADD CONSTRAINT uni_autotest_project UNIQUE (NAME, SERVER_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'creating table TEST_AUTOMATION_PROJECT', NOW(), 'Create Table, Create Index, 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.3.0.xml', 'tm-1.3.0-feat-1181.02', '2.0.5', '3:62bfccf08ab461a9202a7288e1df89be', 139);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1181.03::bsiri::(Checksum: 3:0aa83558ede587eab6dba3c5615274b8)
-- now a PROJECT may or may not have test automation enabled
ALTER TABLE PROJECT ADD TEST_AUTOMATION_ENABLED BOOLEAN DEFAULT FALSE NOT NULL;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'now a PROJECT may or may not have test automation enabled', 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.3.0.xml', 'tm-1.3.0-feat-1181.03', '2.0.5', '3:0aa83558ede587eab6dba3c5615274b8', 140);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1181.04::bsiri::(Checksum: 3:2e342ec4d8845ca9310cb70c3992379e)
-- table where TM projects and TA projects are bound to each others
CREATE TABLE TM_TA_PROJECTS (TM_PROJECT_ID BIGINT NOT NULL, TA_PROJECT_ID BIGINT NOT NULL, CONSTRAINT fk_tm_ta_projects_tm FOREIGN KEY (TM_PROJECT_ID) REFERENCES PROJECT(PROJECT_ID), CONSTRAINT fk_tm_ta_projects_ta FOREIGN KEY (TA_PROJECT_ID) REFERENCES TEST_AUTOMATION_PROJECT(PROJECT_ID));

ALTER TABLE TM_TA_PROJECTS ADD CONSTRAINT pk_tm_ta_projects PRIMARY KEY (TM_PROJECT_ID, TA_PROJECT_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'table where TM projects and TA projects are bound to each others', NOW(), 'Create Table, Add Primary Key', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1181.04', '2.0.5', '3:2e342ec4d8845ca9310cb70c3992379e', 141);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1194.01::mpagnon::(Checksum: 3:a8249eba32af76a637ec9ea446e92e54)
-- Bugtracker project represent the project's name in a configured bugtracker
CREATE TABLE BUGTRACKER_BINDING (BUGTRACKER_BINDING_ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, BUGTRACKER_ID BIGINT NOT NULL, PROJECT_NAME VARCHAR(255), PROJECT_ID BIGINT NOT NULL, CONSTRAINT PK_BUGTRACKER_BINDING PRIMARY KEY (BUGTRACKER_BINDING_ID), CONSTRAINT FK_BUGTRACKER_BINDING_PROJECT FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID), CONSTRAINT FK_BUGTRACKER_BINDING_BUGTRACKER FOREIGN KEY (BUGTRACKER_ID) REFERENCES BUGTRACKER(BUGTRACKER_ID));

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Bugtracker project represent the project''s name in a configured bugtracker', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1194.01', '2.0.5', '3:a8249eba32af76a637ec9ea446e92e54', 142);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1194.04::mpagnon::(Checksum: 3:ccc10006653c4f2635a3768dc6a2f04e)
-- Data migration: add all bugtrackers that had an issue
insert into BUGTRACKER (NAME, KIND, URL, IFRAME_FRIENDLY)
			select distinct issues.BT_NAME , 'mantis', 'TO BE DEFINED', true
			from ISSUE issues;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Data migration: add all bugtrackers that had an issue', 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.3.0.xml', 'tm-1.3.0-feat-1194.04', '2.0.5', '3:ccc10006653c4f2635a3768dc6a2f04e', 143);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1194.05::mpagnon::(Checksum: 3:a9bd40911864a678b0c67e6fb8f4253f)
-- New foreign key in ISSUE table that points to a BUGTRACKER
ALTER TABLE ISSUE ADD BUGTRACKER_ID BIGINT;

ALTER TABLE ISSUE ADD CONSTRAINT fk_issue_bugtracker FOREIGN KEY (BUGTRACKER_ID) REFERENCES BUGTRACKER (BUGTRACKER_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'New foreign key in ISSUE table that points to a BUGTRACKER', 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.3.0.xml', 'tm-1.3.0-feat-1194.05', '2.0.5', '3:a9bd40911864a678b0c67e6fb8f4253f', 144);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1194.06::mpagnon::(Checksum: 3:410ac4d270b36d8c582a4ae001a307f4)
-- Data migration : update issue's bugtracker FK with migrated BTs
CREATE TABLE ISSUEBT
			(
			ISSUEID BIGINT,
			BTID BIGINT
			);

Insert into ISSUEBT (ISSUEID, BTID)
			select distinct iss.ISSUE_ID,
			bt.BUGTRACKER_ID
			from ISSUE as iss, BUGTRACKER as bt
			where iss.BT_NAME = bt.NAME;

update ISSUE
			set BUGTRACKER_ID = (
				select BTID from ISSUEBT 
				where ISSUEBT.ISSUEID = ISSUE.ISSUE_ID
			);

DROP table ISSUEBT;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Data migration : update issue''s bugtracker FK with migrated BTs', 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.3.0.xml', 'tm-1.3.0-feat-1194.06', '2.0.5', '3:410ac4d270b36d8c582a4ae001a307f4', 145);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1194.07::mpagnon::(Checksum: 3:8db7a0d029f7e427f43c482f6f49ebe7)
-- Drop of column "BT_NAME" on table issue because it is not used anymore => use of FK to table BUGTRACKER instead
ALTER TABLE ISSUE DROP COLUMN BT_NAME;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Drop of column "BT_NAME" on table issue because it is not used anymore => use of FK to table BUGTRACKER instead', NOW(), 'Drop Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1194.07', '2.0.5', '3:8db7a0d029f7e427f43c482f6f49ebe7', 146);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1194.08::mpagnon::(Checksum: 3:27f8b473a60fb5a4eeee9f6faa4c82d8)
-- After Data migration : issue's bugtracker must not be null
ALTER TABLE ISSUE ALTER COLUMN  BUGTRACKER_ID BIGINT NOT NULL;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'After Data migration : issue''s bugtracker must not be null', NOW(), 'Add Not-Null Constraint', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1194.08', '2.0.5', '3:27f8b473a60fb5a4eeee9f6faa4c82d8', 147);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1210.01::bsiri::(Checksum: 3:4c9b3806e8ad67f8b6ceb37a62616d18)
-- Creation of the table AUTOMATED_TEST
CREATE TABLE AUTOMATED_TEST (TEST_ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, NAME VARCHAR(255), PROJECT_ID BIGINT, CONSTRAINT PK_AUTOMATED_TEST PRIMARY KEY (TEST_ID), CONSTRAINT fk_automated_test_project FOREIGN KEY (PROJECT_ID) REFERENCES TEST_AUTOMATION_PROJECT(PROJECT_ID));

ALTER TABLE AUTOMATED_TEST ADD CONSTRAINT uni_auto_test UNIQUE (NAME, PROJECT_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Creation of the table AUTOMATED_TEST', NOW(), 'Create Table, 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.3.0.xml', 'tm-1.3.0-feat-1210.01', '2.0.5', '3:4c9b3806e8ad67f8b6ceb37a62616d18', 148);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1210.02::bsiri::(Checksum: 3:b3908ccdd7b72dc53d6d9645fe358b98)
-- a TEST_CASE can now refer to 0 or 1 AUTOMATED_TEST
ALTER TABLE TEST_CASE ADD TA_TEST BIGINT DEFAULT null;

ALTER TABLE TEST_CASE ADD CONSTRAINT fk_test_case_ta_test FOREIGN KEY (TA_TEST) REFERENCES AUTOMATED_TEST (TEST_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'a TEST_CASE can now refer to 0 or 1 AUTOMATED_TEST', 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.3.0.xml', 'tm-1.3.0-feat-1210.02', '2.0.5', '3:b3908ccdd7b72dc53d6d9645fe358b98', 149);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1210.03::gfouquet::(Checksum: 3:5c1676c12fff5e9fb3d0088a83a86006)
-- Added a role / group for test automation clients
INSERT INTO CORE_GROUP (QUALIFIED_NAME) VALUES ('squashtest.authz.group.tm.TestAutomationServer');

INSERT INTO CORE_GROUP_AUTHORITY (AUTHORITY, GROUP_ID) VALUES ('ROLE_TA_API_CLIENT', (select max(ID) from CORE_GROUP));

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Added a role / group for test automation clients', NOW(), 'Insert Row (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1210.03', '2.0.5', '3:5c1676c12fff5e9fb3d0088a83a86006', 150);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1211.01::bsiri::(Checksum: 3:8acdd23d131835dfe7e74448a92297d1)
-- Creation of the automated suites table. Note that althought its ID is
-- 			still the primary key, it won't be autogenerated : the system will supply it 
-- 			along the rest instead.
-- 			
-- 			A automated suite groups together a bunch of test automation executions, 
-- 			which themselves refer to test automation tests.
CREATE TABLE AUTOMATED_SUITE (SUITE_ID VARCHAR(50) NOT NULL, CONSTRAINT PK_AUTOMATED_SUITE PRIMARY KEY (SUITE_ID));

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Creation of the automated suites table. Note that althought its ID is
			still the primary key, it won''t be autogenerated : the system will supply it 
			along the rest instead.
			
			A automated suite groups together a bunch of test automation execut...', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1211.01', '2.0.5', '3:8acdd23d131835dfe7e74448a92297d1', 151);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml::tm-1.3.0-feat-1211.02::bsiri::(Checksum: 3:a101bd36f7d9c501c587cf5e6d5c78c0)
-- Table for the automated executions extender. An automated execution is 
-- 			an extension to Executions that handles things related to test automation.
-- 			
-- 			The correct business model would have been that an automated execution is a specialization of 
-- 			execution, thus inherited from it. Sadly Hibernates got in the way so we came
-- 			up with that solution instead.
CREATE TABLE AUTOMATED_EXECUTION_EXTENDER (EXTENDER_ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, MASTER_EXECUTION_ID BIGINT NOT NULL, TEST_ID BIGINT NOT NULL, RESULT_URL VARCHAR(300), SUITE_ID VARCHAR(50), RESULT_SUMMARY LONGVARCHAR DEFAULT null, CONSTRAINT PK_AUTOMATED_EXECUTION_EXTENDER PRIMARY KEY (EXTENDER_ID), CONSTRAINT fk_auto_exec_suite FOREIGN KEY (SUITE_ID) REFERENCES AUTOMATED_SUITE(SUITE_ID), CONSTRAINT fk_auto_exec_exec FOREIGN KEY (MASTER_EXECUTION_ID) REFERENCES EXECUTION(EXECUTION_ID), CONSTRAINT fk_auto_exec_auto_test FOREIGN KEY (TEST_ID) REFERENCES AUTOMATED_TEST(TEST_ID));

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Table for the automated executions extender. An automated execution is 
			an extension to Executions that handles things related to test automation.
			
			The correct business model would have been that an automated execution is a specialization of 
...', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.3.0.xml', 'tm-1.3.0-feat-1211.02', '2.0.5', '3:a101bd36f7d9c501c587cf5e6d5c78c0', 152);

-- Release Database Lock
-- Release Database Lock
