-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/global.changelog-master.xml
-- Ran at: 03/04/20 12:06
-- Against: liquibase@jdbc:postgresql://localhost:5432/squash_dist_cur
-- Liquibase version: 2.0.5
-- *********************************************************************

-- Create Database Lock Table
CREATE TABLE databasechangeloglock (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP WITH TIME ZONE, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

INSERT INTO databasechangeloglock (ID, LOCKED) VALUES (1, FALSE);

-- Lock Database
-- Create Database Change Log Table
CREATE TABLE databasechangelog (ID VARCHAR(63) NOT NULL, AUTHOR VARCHAR(63) NOT NULL, FILENAME VARCHAR(200) NOT NULL, DATEEXECUTED TIMESTAMP WITH TIME ZONE NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONSTRAINT PK_DATABASECHANGELOG PRIMARY KEY (ID, AUTHOR, FILENAME));

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/auth/auth.changelog-1.0.xml::1::gfouquet::(Checksum: 3:c3f1c8c5573dd761b4aa35d473b1db7d)
CREATE TABLE AUTH_USER (LOGIN VARCHAR(50) NOT NULL, PASSWORD CHAR(40) NOT NULL, ACTIVE BOOLEAN DEFAULT TRUE NOT NULL, CONSTRAINT PK_AUTH_USER PRIMARY KEY (LOGIN));

COMMENT ON TABLE AUTH_USER IS 'Stores users credentials';

COMMENT ON COLUMN AUTH_USER.LOGIN IS 'The user''s unique identifier';

COMMENT ON COLUMN AUTH_USER.PASSWORD IS 'SHA-1 hash of the user''s password';

CREATE UNIQUE INDEX IDX_AUTHENTICATION ON AUTH_USER(LOGIN);

INSERT INTO AUTH_USER (ACTIVE, LOGIN, PASSWORD) VALUES (TRUE, 'admin', 'd033e22ae348aeb5660fc2140aec35850c4da997');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', '', NOW(), 'Create Table, Create Index, Insert Row', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/auth/auth.changelog-1.0.xml', '1', '2.0.5', '3:c3f1c8c5573dd761b4aa35d473b1db7d', 1);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml::1::gfouquet::(Checksum: 3:0827c56c3da3a248891d04c6f0ddb00e)
CREATE TABLE CORE_USER (ID bigserial NOT NULL, LOGIN VARCHAR(50) NOT NULL, FIRST_NAME VARCHAR(50) DEFAULT '' NOT NULL, LAST_NAME VARCHAR(50) DEFAULT '' NOT NULL, EMAIL VARCHAR(50) DEFAULT '' NOT NULL, ACTIVE BOOLEAN DEFAULT FALSE NOT NULL, CREATED_BY VARCHAR(50) NOT NULL, CREATED_ON TIMESTAMP WITH TIME ZONE NOT NULL, LAST_MODIFIED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, LAST_MODIFIED_BY VARCHAR(50) DEFAULT null, CONSTRAINT PK_CORE_USER PRIMARY KEY (ID), CONSTRAINT CT_UNIQUE_USER_LOGIN UNIQUE (LOGIN));

COMMENT ON COLUMN CORE_USER.LOGIN IS 'The user''s login as provided by the authentication system';

COMMENT ON COLUMN CORE_USER.FIRST_NAME IS 'The user''s first name';

COMMENT ON COLUMN CORE_USER.LAST_NAME IS 'The user''s last name';

COMMENT ON COLUMN CORE_USER.EMAIL IS 'The user''s e-mail';

COMMENT ON COLUMN CORE_USER.ACTIVE IS 'User is active or not';

COMMENT ON COLUMN CORE_USER.CREATED_BY IS 'nom du createur de cet utilisateur.';

COMMENT ON COLUMN CORE_USER.CREATED_ON IS 'Date de creation de cet utilisateur. Il s''agit de la date de creation de l''entree dans       la base, non d''une date metier.';

COMMENT ON COLUMN CORE_USER.LAST_MODIFIED_ON IS 'Date de derniere mise a jour de cette instance.';

COMMENT ON COLUMN CORE_USER.LAST_MODIFIED_BY IS 'Nom du dernier utilisateur ayant mis a jour cet instance.';

CREATE UNIQUE INDEX DX_USER_LOGIN ON CORE_USER(LOGIN);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', '', NOW(), 'Create Table, Create Index', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml', '1', '2.0.5', '3:0827c56c3da3a248891d04c6f0ddb00e', 2);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml::2::gfouquet::(Checksum: 3:9cb7669efca04a3d97a7de885d0408fb)
CREATE TABLE CORE_GROUP (ID bigserial NOT NULL, QUALIFIED_NAME VARCHAR(255) NOT NULL, CONSTRAINT PK_CORE_GROUP PRIMARY KEY (ID), CONSTRAINT CT_UN_GRP_QUAL_NAME UNIQUE (QUALIFIED_NAME));

COMMENT ON COLUMN CORE_GROUP.QUALIFIED_NAME IS 'Qualified name of the group, both used as a functional key and a localization key. Should be something like ''squashtest.authz.group._app module_._GroupName_''';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', '', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml', '2', '2.0.5', '3:9cb7669efca04a3d97a7de885d0408fb', 3);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml::3::gfouquet::(Checksum: 3:9f898022aa169e6e6fe3643d9a8536e0)
CREATE TABLE CORE_GROUP_AUTHORITY (GROUP_ID BIGINT NOT NULL, AUTHORITY VARCHAR(50) NOT NULL, CONSTRAINT FK_GRP_AUTH_GRP FOREIGN KEY (GROUP_ID) REFERENCES CORE_GROUP(ID));

COMMENT ON COLUMN CORE_GROUP_AUTHORITY.GROUP_ID IS 'Shared key with CORE_GROUP table';

COMMENT ON COLUMN CORE_GROUP_AUTHORITY.AUTHORITY IS 'The authority';

CREATE INDEX DX_GRP_ID ON CORE_GROUP_AUTHORITY(GROUP_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', '', NOW(), 'Create Table, Create Index', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml', '3', '2.0.5', '3:9f898022aa169e6e6fe3643d9a8536e0', 4);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml::4::gfouquet::(Checksum: 3:12d5a7ac46a1fc80f78b4abf1e2cbbc8)
CREATE TABLE CORE_GROUP_MEMBER (USER_ID BIGINT NOT NULL, GROUP_ID BIGINT NOT NULL, CONSTRAINT FK_GRP_MEMBER_USR FOREIGN KEY (USER_ID) REFERENCES CORE_USER(ID), CONSTRAINT FK_GRP_MEMBER_GRP FOREIGN KEY (GROUP_ID) REFERENCES CORE_GROUP(ID));

CREATE UNIQUE INDEX DX_GRP_MEMBER ON CORE_GROUP_MEMBER(USER_ID, GROUP_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', '', NOW(), 'Create Table, Create Index', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml', '4', '2.0.5', '3:12d5a7ac46a1fc80f78b4abf1e2cbbc8', 5);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml::5::gfouquet::(Checksum: 3:003d6da0a4bdceba7282faefe2e73525)
INSERT INTO CORE_GROUP (QUALIFIED_NAME) VALUES ('squashtest.authz.group.core.Admin');

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

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

INSERT INTO CORE_USER (CREATED_BY, CREATED_ON, EMAIL, LAST_NAME, LOGIN) VALUES ('liquibase', CURRENT_TIMESTAMP, 'admin@squashtest.org', 'Squash Administrator', 'admin');

INSERT INTO CORE_GROUP_MEMBER (GROUP_ID, USER_ID) VALUES ((select max(ID) from CORE_GROUP), (select max(ID) from CORE_USER));

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', '', NOW(), 'Insert Row (x5)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml', '5', '2.0.5', '3:003d6da0a4bdceba7282faefe2e73525', 6);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml::7::gfouquet::(Checksum: 3:2872b424d0bf771cf612c1dc63533d84)
CREATE TABLE ACL_CLASS (ID bigserial NOT NULL, CLASSNAME VARCHAR(255) NOT NULL, CONSTRAINT PK_ACL_CLASS PRIMARY KEY (ID), CONSTRAINT CT_UN_CLASSNAME UNIQUE (CLASSNAME));

COMMENT ON COLUMN ACL_CLASS.CLASSNAME IS 'Fully qualified classname';

CREATE UNIQUE INDEX DX_CLASS_NAME ON ACL_CLASS(CLASSNAME);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', '', NOW(), 'Create Table, Create Index', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml', '7', '2.0.5', '3:2872b424d0bf771cf612c1dc63533d84', 7);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml::8::gfouquet::(Checksum: 3:2c5ed802f9f1885284157f65b772d4ae)
CREATE TABLE ACL_OBJECT_IDENTITY (ID bigserial NOT NULL, IDENTITY BIGINT NOT NULL, CLASS_ID BIGINT NOT NULL, CONSTRAINT PK_ACL_OBJECT_ID PRIMARY KEY (ID), CONSTRAINT FK_ACL_OBJ_ID_ACL_CLASS FOREIGN KEY (CLASS_ID) REFERENCES ACL_CLASS(ID));

COMMENT ON COLUMN ACL_OBJECT_IDENTITY.IDENTITY IS 'The id of the target entity';

ALTER TABLE ACL_OBJECT_IDENTITY ADD UNIQUE (IDENTITY, CLASS_ID);

CREATE INDEX DX_OBJ_ID_IDENTITY ON ACL_OBJECT_IDENTITY(IDENTITY);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', '', NOW(), 'Create Table, Add Unique Constraint, Create Index', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml', '8', '2.0.5', '3:2c5ed802f9f1885284157f65b772d4ae', 8);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml::9::gfouquet::(Checksum: 3:14139425ec3263f63ca78174b6cfe960)
CREATE TABLE ACL_GROUP (ID bigserial NOT NULL, QUALIFIED_NAME VARCHAR(255) NOT NULL, CONSTRAINT PK_ACL_GRP PRIMARY KEY (ID), CONSTRAINT CT_UN_ACL_GRP_QUAL_NAME UNIQUE (QUALIFIED_NAME));

COMMENT ON COLUMN ACL_GROUP.QUALIFIED_NAME IS 'Group''s qualified name, both used as a functional key and a localization key. Should be something like ''squashtest.acl.group._app module_._GroupName_''';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', '', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml', '9', '2.0.5', '3:14139425ec3263f63ca78174b6cfe960', 9);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml::10::gfouquet::(Checksum: 3:fcf338b8327512d052f48426bc2b2cd4)
CREATE TABLE ACL_GROUP_PERMISSION (ACL_GROUP_ID BIGINT NOT NULL, PERMISSION_MASK INT DEFAULT 0 NOT NULL, CLASS_ID BIGINT NOT NULL, PERMISSION_ORDER INT DEFAULT 0 NOT NULL, GRANTING BOOLEAN DEFAULT TRUE NOT NULL, CONSTRAINT FK_ACL_GRP_PERMISSION_ACL_GRP FOREIGN KEY (ACL_GROUP_ID) REFERENCES ACL_GROUP(ID), CONSTRAINT FK_SC_AUTH_ACL_CLASS FOREIGN KEY (CLASS_ID) REFERENCES ACL_CLASS(ID));

COMMENT ON COLUMN ACL_GROUP_PERMISSION.PERMISSION_MASK IS 'Mask of permissions as defined by spring security';

COMMENT ON COLUMN ACL_GROUP_PERMISSION.CLASS_ID IS 'The class to which the permissions apply';

ALTER TABLE ACL_GROUP_PERMISSION ADD CONSTRAINT PK_ACL_GRP_PERMISSION PRIMARY KEY (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', '', NOW(), 'Create Table, Add Primary Key', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml', '10', '2.0.5', '3:fcf338b8327512d052f48426bc2b2cd4', 10);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml::11::gfouquet::(Checksum: 3:9ae257f0e06c96c6b7d52b475eb4af62)
CREATE TABLE ACL_RESPONSIBILITY_SCOPE_ENTRY (ID bigserial NOT NULL, USER_ID BIGINT NOT NULL, ACL_GROUP_ID BIGINT NOT NULL, OBJECT_IDENTITY_ID BIGINT NOT NULL, CONSTRAINT PK_RESP_SCOPE_ENTRY PRIMARY KEY (ID), CONSTRAINT FK_RESP_SCOPE_OBJ_ID FOREIGN KEY (OBJECT_IDENTITY_ID) REFERENCES ACL_OBJECT_IDENTITY(ID), CONSTRAINT FK_RESP_SCOPE_ACL_GRP FOREIGN KEY (ACL_GROUP_ID) REFERENCES ACL_GROUP(ID), CONSTRAINT FK_RESP_SCOPE_USER FOREIGN KEY (USER_ID) REFERENCES CORE_USER(ID));

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', '', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.15.0.xml', '11', '2.0.5', '3:9ae257f0e06c96c6b7d52b475eb4af62', 11);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.20.0.xml::core-0.20.0.01::ramoussou::(Checksum: 3:80791e9849bdd487180343805c35b3e2)
-- Creation of a new table CORE_CONFIG containing Application configuration
CREATE TABLE CORE_CONFIG (STR_KEY VARCHAR(255) NOT NULL, VALUE TEXT, CONSTRAINT PK_CORE_CONFIG PRIMARY KEY (STR_KEY));

COMMENT ON COLUMN CORE_CONFIG.STR_KEY IS 'Configuration Key';

COMMENT ON COLUMN CORE_CONFIG.VALUE IS 'Configuration value';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('ramoussou', 'Creation of a new table CORE_CONFIG containing Application configuration', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.20.0.xml', 'core-0.20.0.01', '2.0.5', '3:80791e9849bdd487180343805c35b3e2', 12);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.20.0.xml::core-0.20.0.02::gfouquet::(Checksum: 3:b440864cd51c94293db4c375b14d73b4)
-- Default welcome message
insert into CORE_CONFIG (STR_KEY, VALUE) values ('WELCOME_MESSAGE', '<p><span style="color:#ff0000;">The administrator default login is <strong>admin/admin</strong> (you can change this password in the &quot;<em>My account (admin)</em>&quot; menu).</span></p><p>Go to the &quot;<em>Administration&quot;</em> menu (in the upper right corner) to create new users and projects, or change this default home message.</p><p>&nbsp;</p>');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Default welcome message', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-0.20.0.xml', 'core-0.20.0.02', '2.0.5', '3:b440864cd51c94293db4c375b14d73b4', 13);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-1.1.1.xml::core-1.1.1.RELEASE.01::mpagnon::(Checksum: 3:b32be3c4e5a9a4c1790c516c467fdcce)
-- Add new line in CORE_CONFIG for LOGIN_MESSAGE
insert into CORE_CONFIG (STR_KEY, VALUE) select 'LOGIN_MESSAGE', VALUE from CORE_CONFIG where STR_KEY = 'WELCOME_MESSAGE';

update CORE_CONFIG set VALUE = '1.1.1' where STR_KEY = 'squashtest.tm.database.version';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Add new line in CORE_CONFIG for LOGIN_MESSAGE', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-1.1.1.xml', 'core-1.1.1.RELEASE.01', '2.0.5', '3:b32be3c4e5a9a4c1790c516c467fdcce', 14);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-1.4.0.xml::core-feat-327::mpagnon::(Checksum: 3:34782a78840d9958a93867752ac303e8)
-- Because admin was not active , need to make sure that all user active in auth_user are active in core_user
update CORE_USER
					set ACTIVE = true
					where CORE_USER.LOGIN in (select AUTH_USER.LOGIN from AUTH_USER where AUTH_USER.ACTIVE = true);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Because admin was not active , need to make sure that all user active in auth_user are active in core_user', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/core/core.changelog-1.4.0.xml', 'core-feat-327', '2.0.5', '3:34782a78840d9958a93867752ac303e8', 15);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.01::bsiri::(Checksum: 3:4e6c3f9dea5db7ce08cde70ffee4b555)
-- This table maintains the mapping between an issue in Squash and an 
-- 			issue in the remote bugtracker
CREATE TABLE ISSUE (ISSUE_ID bigserial NOT NULL, REMOTE_ISSUE_ID VARCHAR(255) NOT NULL, CONSTRAINT PK_ISSUE PRIMARY KEY (ISSUE_ID));

COMMENT ON COLUMN ISSUE.REMOTE_ISSUE_ID IS 'considering the different types    a bugtracker may use for its own primary keys, we use here a varchar type as it is generic enough';

CREATE INDEX idx_issue ON ISSUE(ISSUE_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'This table maintains the mapping between an issue in Squash and an 
			issue in the remote bugtracker', 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-0.15.0.xml', 'tm-0.14.0.01', '2.0.5', '3:4e6c3f9dea5db7ce08cde70ffee4b555', 16);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.02::bsiri::(Checksum: 3:4923e5c1de211cad8c0f4b3c771c81af)
-- each Squash entity that may be attached to one or several bugs will be given an issue list. This table will maintain that list, an entry means that some entity refering to it have several.
CREATE TABLE ISSUE_LIST (ISSUE_LIST_ID bigserial NOT NULL, CONSTRAINT PK_ISSUE_LIST PRIMARY KEY (ISSUE_LIST_ID));

CREATE INDEX idx_issue_list ON ISSUE_LIST(ISSUE_LIST_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'each Squash entity that may be attached to one or several bugs will be given an issue list. This table will maintain that list, an entry means that some entity refering to it have several.', 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-0.15.0.xml', 'tm-0.14.0.02', '2.0.5', '3:4923e5c1de211cad8c0f4b3c771c81af', 17);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.03::bsiri::(Checksum: 3:cf1b8733816e62e4fd608d629a0db0a0)
-- This table will map one issue_list to (0,several) issues
CREATE TABLE ISSUE_LIST_CONTENT (ISSUE_LIST_ID BIGINT NOT NULL, ISSUE_ID BIGINT NOT NULL, CONSTRAINT fk_list_issue_list FOREIGN KEY (ISSUE_LIST_ID) REFERENCES ISSUE_LIST(ISSUE_LIST_ID), CONSTRAINT fk_list_issue_issue FOREIGN KEY (ISSUE_ID) REFERENCES ISSUE(ISSUE_ID));

COMMENT ON COLUMN ISSUE_LIST_CONTENT.ISSUE_LIST_ID IS 'fk to the issue list';

COMMENT ON COLUMN ISSUE_LIST_CONTENT.ISSUE_ID IS 'fk to an issue';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'This table will map one issue_list to (0,several) issues', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml', 'tm-0.14.0.03', '2.0.5', '3:cf1b8733816e62e4fd608d629a0db0a0', 18);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.04-pg::gfouquet::(Checksum: 3:f5c52b72f100c6f1147111174540e360)
-- Cette table contient le contenu d'une pièce jointe, dont les meta infos sont stockees dans la 
--       table ATTACHMENT. La relation entre les deux tables est 1-1
CREATE TABLE ATTACHMENT_CONTENT (ATTACHMENT_CONTENT_ID bigserial NOT NULL, STREAM_CONTENT OID DEFAULT NULL, CONSTRAINT PK_ATTACHMENT_CONTENT PRIMARY KEY (ATTACHMENT_CONTENT_ID));

COMMENT ON COLUMN ATTACHMENT_CONTENT.ATTACHMENT_CONTENT_ID IS 'clé primaire du contenu de la piece jointe. Devrait être autogénérée.';

COMMENT ON COLUMN ATTACHMENT_CONTENT.STREAM_CONTENT IS 'Le contenu de la piece jointe.';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Cette table contient le contenu d''une pièce jointe, dont les meta infos sont stockees dans la 
      table ATTACHMENT. La relation entre les deux tables est 1-1', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml', 'tm-0.14.0.04-pg', '2.0.5', '3:f5c52b72f100c6f1147111174540e360', 19);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.05::bsiri::(Checksum: 3:c3a74f53f543266e9664ced59749936d)
-- Cette table stockera les pieces jointes. La piece elle même est un blob stocke a part dans
-- 					la table ATTACHMENT_CONTENT. La relation entre les deux tables est 1-1
CREATE TABLE ATTACHMENT (ATTACHMENT_ID bigserial NOT NULL, NAME VARCHAR(255) NOT NULL, TYPE VARCHAR(10) NOT NULL, SIZE BIGINT NOT NULL, ADDED_ON TIMESTAMP WITH TIME ZONE NOT NULL, CONTENT_ID BIGINT, CONSTRAINT PK_ATTACHMENT PRIMARY KEY (ATTACHMENT_ID), CONSTRAINT fk_attachment_content FOREIGN KEY (CONTENT_ID) REFERENCES ATTACHMENT_CONTENT(ATTACHMENT_CONTENT_ID));

COMMENT ON COLUMN ATTACHMENT.ATTACHMENT_ID IS 'clé primaire de la piece jointe. Devrait être autogénérée.';

COMMENT ON COLUMN ATTACHMENT.NAME IS 'nom de la piece jointe';

COMMENT ON COLUMN ATTACHMENT.TYPE IS 'type de la piece jointe. Cette information pourrait etre trivialement deduite du nom de fichier';

COMMENT ON COLUMN ATTACHMENT.SIZE IS 'taille de la piece jointe en octet. Cette information est déterminée au moment de l''upload et doit etre fournie a la base     dès sa création';

COMMENT ON COLUMN ATTACHMENT.ADDED_ON IS 'Date de l''ajout de la piece jointe.';

COMMENT ON COLUMN ATTACHMENT.CONTENT_ID IS 'cle etrangere vers la table ATTACHMENT_CONTENT';

CREATE INDEX idx_attachment ON ATTACHMENT(ATTACHMENT_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table stockera les pieces jointes. La piece elle même est un blob stocke a part dans
					la table ATTACHMENT_CONTENT. La relation entre les deux tables est 1-1', 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-0.15.0.xml', 'tm-0.14.0.05', '2.0.5', '3:c3a74f53f543266e9664ced59749936d', 20);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.06::bsiri::(Checksum: 3:91e399d585df7a01de44b11c56ccc4b4)
-- Cette table matérialise une liste d'ATTACHMENT. Bien qu'elle ne contienne que sa clé primaire, 
-- 			sa raison d'exister est, au niveau de l'application, de fournir 'd'un point de vue technique) 
-- 			une entité indépendante des entités métier auquel on rajoute les pièces jointes (d'un point de vue métier).
CREATE TABLE ATTACHMENT_LIST (ATTACHMENT_LIST_ID bigserial NOT NULL, CONSTRAINT PK_ATTACHMENT_LIST PRIMARY KEY (ATTACHMENT_LIST_ID));

COMMENT ON COLUMN ATTACHMENT_LIST.ATTACHMENT_LIST_ID IS 'clé primaire, devrait être autogénérée.';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table matérialise une liste d''ATTACHMENT. Bien qu''elle ne contienne que sa clé primaire, 
			sa raison d''exister est, au niveau de l''application, de fournir ''d''un point de vue technique) 
			une entité indépendante des entités métier auquel on ra...', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml', 'tm-0.14.0.06', '2.0.5', '3:91e399d585df7a01de44b11c56ccc4b4', 21);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.07::bsiri::(Checksum: 3:5aafdf5b488ea626d85ef782959884d0)
-- Table d'association entre un cas de test et ses pieces jointes
CREATE TABLE ATTACHMENT_LIST_CONTENT (AL_ID BIGINT NOT NULL, ATTACHMENT_ID BIGINT NOT NULL, CONSTRAINT fk_list_attachment_attachment FOREIGN KEY (ATTACHMENT_ID) REFERENCES ATTACHMENT(ATTACHMENT_ID), CONSTRAINT fk_list_attachment_list FOREIGN KEY (AL_ID) REFERENCES ATTACHMENT_LIST(ATTACHMENT_LIST_ID));

COMMENT ON COLUMN ATTACHMENT_LIST_CONTENT.AL_ID IS 'clé étrangère vers ATTACHMENT_LIST.';

COMMENT ON COLUMN ATTACHMENT_LIST_CONTENT.ATTACHMENT_ID IS 'clé étrangère vers la table ATTACHMENT';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Table d''association entre un cas de test et ses pieces jointes', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml', 'tm-0.14.0.07', '2.0.5', '3:5aafdf5b488ea626d85ef782959884d0', 22);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.08::ramoussou::(Checksum: 3:10f6bb98e3ad8c7688581c2434dc5596)
-- Table representant les projets englobants les libraries d'entite que nous utilisons.
CREATE TABLE PROJECT (PROJECT_ID bigserial NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION TEXT, LABEL VARCHAR(255), ACTIVE BOOLEAN DEFAULT TRUE NOT NULL, CREATED_BY VARCHAR(50) NOT NULL, CREATED_ON TIMESTAMP WITH TIME ZONE NOT NULL, LAST_MODIFIED_BY VARCHAR(50) DEFAULT null, LAST_MODIFIED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, CL_ID BIGINT, TCL_ID BIGINT, RL_ID BIGINT, CONSTRAINT PK_PROJECT PRIMARY KEY (PROJECT_ID));

COMMENT ON COLUMN PROJECT.PROJECT_ID IS 'cle primaire de la table';

COMMENT ON COLUMN PROJECT.NAME IS 'nom du projet';

COMMENT ON COLUMN PROJECT.DESCRIPTION IS 'description du projet';

COMMENT ON COLUMN PROJECT.LABEL IS 'Label du projet';

COMMENT ON COLUMN PROJECT.ACTIVE IS 'si le projet est actif ou non';

COMMENT ON COLUMN PROJECT.CREATED_BY IS 'nom du createur du projet.';

COMMENT ON COLUMN PROJECT.CREATED_ON IS 'Date de creation de ce projet. Il s''agit de la date de creation de l''entree dans la base, non d''une date metier.';

COMMENT ON COLUMN PROJECT.LAST_MODIFIED_BY IS 'Nom du dernier utilisateur ayant mis a jour cet instance.';

COMMENT ON COLUMN PROJECT.LAST_MODIFIED_ON IS 'Date de derniere mise a jour de cette instance.';

COMMENT ON COLUMN PROJECT.CL_ID IS 'cle etrangere de la table campaign-Library';

COMMENT ON COLUMN PROJECT.TCL_ID IS 'cle etrangere de la table TESTCASE-Library';

COMMENT ON COLUMN PROJECT.RL_ID IS 'cle etrangere de la table REQUIREMENT-Library';

CREATE INDEX idx_project ON PROJECT(PROJECT_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('ramoussou', 'Table representant les projets englobants les libraries d''entite que nous utilisons.', 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-0.15.0.xml', 'tm-0.14.0.08', '2.0.5', '3:10f6bb98e3ad8c7688581c2434dc5596', 23);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.09::bsiri::(Checksum: 3:6ca7c6625c398a565905e6de822b7398)
-- Cette table gere les bibliotheques de cas de test. Une bibliotheque de cas de test peut contenir plusieurs 
-- 		repertoires et/ou cas de test, ces deux entites etant regroupees sous l'appellation generique d'element de bibliotheque 
-- 		de cas de test (test case library nodes).
CREATE TABLE TEST_CASE_LIBRARY (TCL_ID bigserial NOT NULL, PROJECT_ID BIGINT, CONSTRAINT PK_TEST_CASE_LIBRARY PRIMARY KEY (TCL_ID), CONSTRAINT fk_tcl_project FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID));

COMMENT ON COLUMN TEST_CASE_LIBRARY.TCL_ID IS 'cle primaire autogeneree';

COMMENT ON COLUMN TEST_CASE_LIBRARY.PROJECT_ID IS 'cle etrangere de la table Project';

CREATE INDEX idx_test_case_library ON TEST_CASE_LIBRARY(TCL_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table gere les bibliotheques de cas de test. Une bibliotheque de cas de test peut contenir plusieurs 
		repertoires et/ou cas de test, ces deux entites etant regroupees sous l''appellation generique d''element de bibliotheque 
		de cas de test (tes...', 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-0.15.0.xml', 'tm-0.14.0.09', '2.0.5', '3:6ca7c6625c398a565905e6de822b7398', 24);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.10::bsiri::(Checksum: 3:70c11ac27e2a6bb65143efee427cd28e)
-- Cette table gere les bibliotheques d'exigences. Une bibliotheque d'exigences peut contenir plusieurs repertoires 
-- 				et/ou exigences, ces deux entites etant regroupees sous l'appellation generique d'element de bibliotheque d'exigences (requirement 
-- 				library nodes).
CREATE TABLE REQUIREMENT_LIBRARY (RL_ID bigserial NOT NULL, PROJECT_ID BIGINT, CONSTRAINT PK_REQUIREMENT_LIBRARY PRIMARY KEY (RL_ID), CONSTRAINT fk_rl_project FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID));

COMMENT ON COLUMN REQUIREMENT_LIBRARY.RL_ID IS 'cle primaire autogeneree';

COMMENT ON COLUMN REQUIREMENT_LIBRARY.PROJECT_ID IS 'cle etrangere de la table Project';

CREATE INDEX idx_requirement_library ON REQUIREMENT_LIBRARY(RL_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table gere les bibliotheques d''exigences. Une bibliotheque d''exigences peut contenir plusieurs repertoires 
				et/ou exigences, ces deux entites etant regroupees sous l''appellation generique d''element de bibliotheque d''exigences (requirement 
		...', 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-0.15.0.xml', 'tm-0.14.0.10', '2.0.5', '3:70c11ac27e2a6bb65143efee427cd28e', 25);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.11::bsiri::(Checksum: 3:fd84d5da84499f830851aa8a825f8ff9)
-- Cette table gere les bibliotheques de campagnes. Une bibliotheque 
-- 		de campagnes peut contenir plusieurs repertoires et/ou campagnes, ces deux 
-- 		entites etant regroupees sous l'appellation generique d'element de bibliotheque 
-- 		de campagnes (campaign library nodes).
CREATE TABLE CAMPAIGN_LIBRARY (CL_ID bigserial NOT NULL, PROJECT_ID BIGINT, CONSTRAINT PK_CAMPAIGN_LIBRARY PRIMARY KEY (CL_ID), CONSTRAINT fk_cl_project FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID));

COMMENT ON COLUMN CAMPAIGN_LIBRARY.CL_ID IS 'cle primaire autogeneree';

COMMENT ON COLUMN CAMPAIGN_LIBRARY.PROJECT_ID IS 'cle etrangere de la table Project';

CREATE INDEX idx_campaign_library ON CAMPAIGN_LIBRARY(CL_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table gere les bibliotheques de campagnes. Une bibliotheque 
		de campagnes peut contenir plusieurs repertoires et/ou campagnes, ces deux 
		entites etant regroupees sous l''appellation generique d''element de bibliotheque 
		de campagnes (campaign...', 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-0.15.0.xml', 'tm-0.14.0.11', '2.0.5', '3:fd84d5da84499f830851aa8a825f8ff9', 26);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.12::ramoussou::(Checksum: 3:2e779973c9bd4020fafd9645e240fb4b)
ALTER TABLE PROJECT ADD CONSTRAINT fk_project_cl FOREIGN KEY (CL_ID) REFERENCES CAMPAIGN_LIBRARY (CL_ID);

ALTER TABLE PROJECT ADD CONSTRAINT fk_project_tcl FOREIGN KEY (TCL_ID) REFERENCES TEST_CASE_LIBRARY (TCL_ID);

ALTER TABLE PROJECT ADD CONSTRAINT fk_project_rl FOREIGN KEY (RL_ID) REFERENCES REQUIREMENT_LIBRARY (RL_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('ramoussou', '', NOW(), 'Add Foreign Key Constraint (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml', 'tm-0.14.0.12', '2.0.5', '3:2e779973c9bd4020fafd9645e240fb4b', 27);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.13::bsiri::(Checksum: 3:77ff261548687bed7e3050fc610081cc)
-- Cette table represente un element (generique) faisant partie d'une bibliotheque de cas de test. Cet element 
-- 		generique peut representer soit un repertoire, soit un cas de test. Pour cette raison, que l'utilisateur cree l'un ou 
-- 		l'autre cette table sera necessairement alimentee, et contiendra la cle primaire de l'element en question.
-- 		Toutefois le type reel de l'element cas de test ou repertoire) n'est pas renseigne, il est necessaire de rechercher 
-- 		si la cle est referencee dans la table TEST_CASE_FOLDER ou TEST_CASE pour determiner si cet element releve de l'un ou l'autre.
CREATE TABLE TEST_CASE_LIBRARY_NODE (TCLN_ID bigserial NOT NULL, DELETED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, DESCRIPTION TEXT DEFAULT null, NAME VARCHAR(255) NOT NULL, CREATED_BY VARCHAR(50) NOT NULL, CREATED_ON TIMESTAMP WITH TIME ZONE NOT NULL, LAST_MODIFIED_BY VARCHAR(50) DEFAULT null, LAST_MODIFIED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, PROJECT_ID BIGINT, CONSTRAINT PK_TEST_CASE_LIBRARY_NODE PRIMARY KEY (TCLN_ID), CONSTRAINT fk_tcln_project FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID));

COMMENT ON COLUMN TEST_CASE_LIBRARY_NODE.TCLN_ID IS 'cle primaire autogeneree.';

COMMENT ON COLUMN TEST_CASE_LIBRARY_NODE.DELETED_ON IS 'drapeau indiquant si l''element est actif (NULL) ou logiquement supprime (not NULL).';

COMMENT ON COLUMN TEST_CASE_LIBRARY_NODE.DESCRIPTION IS 'Une description associee a cet element. Devrait etre un champ texte long ou un clob.';

COMMENT ON COLUMN TEST_CASE_LIBRARY_NODE.NAME IS 'Le nom/label de l''element en question.';

COMMENT ON COLUMN TEST_CASE_LIBRARY_NODE.CREATED_BY IS 'nom du createur de ce cas de test.';

COMMENT ON COLUMN TEST_CASE_LIBRARY_NODE.CREATED_ON IS 'Date de creation de ce cas de test. Il s''agit de la date de creation de l''entree dans la base, non d''une date metier.';

COMMENT ON COLUMN TEST_CASE_LIBRARY_NODE.LAST_MODIFIED_BY IS 'Nom du dernier utilisateur ayant mis a jour cet instance.';

COMMENT ON COLUMN TEST_CASE_LIBRARY_NODE.LAST_MODIFIED_ON IS 'Date de derniere mise a jour de cette instance.';

COMMENT ON COLUMN TEST_CASE_LIBRARY_NODE.PROJECT_ID IS 'FK to the Project to which belongs this resource';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table represente un element (generique) faisant partie d''une bibliotheque de cas de test. Cet element 
		generique peut representer soit un repertoire, soit un cas de test. Pour cette raison, que l''utilisateur cree l''un ou 
		l''autre cette table ...', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml', 'tm-0.14.0.13', '2.0.5', '3:77ff261548687bed7e3050fc610081cc', 28);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.14::bsiri::(Checksum: 3:a9d2e473eafcd420919c707d59260567)
-- Cette table represente un element (generique) faisant partie d'une bibliotheque d'exigences. Cet element 
-- 				generique peut representer soit un repertoire, soit une exigence. Pour cette raison, que l'utilisateur cree l'un ou l'autre 
-- 				cette table sera necessairement alimentee, et contiendra la cle primaire de l'element en question. Toutefois le type reel 
-- 				de l'element (exigence ou repertoire) n'est pas renseigne, il est necessaire de rechercher si la cle est referencee dans 
-- 				la table REQUIREMENT_FOLDER ou REQUIREMENT pour determiner si cet element releve de l'un ou l'autre.
CREATE TABLE REQUIREMENT_LIBRARY_NODE (RLN_ID bigserial NOT NULL, DELETED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, DESCRIPTION TEXT DEFAULT null, NAME VARCHAR(255) NOT NULL, CREATED_BY VARCHAR(50) NOT NULL, CREATED_ON TIMESTAMP WITH TIME ZONE NOT NULL, LAST_MODIFIED_BY VARCHAR(50) DEFAULT null, LAST_MODIFIED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, PROJECT_ID BIGINT, CONSTRAINT PK_REQUIREMENT_LIBRARY_NODE PRIMARY KEY (RLN_ID), CONSTRAINT fk_rln_project FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID));

COMMENT ON COLUMN REQUIREMENT_LIBRARY_NODE.RLN_ID IS 'cle primaire autogeneree';

COMMENT ON COLUMN REQUIREMENT_LIBRARY_NODE.DELETED_ON IS 'drapeau indiquant si l''element est actif (NULL) ou logiquement supprime (not NULL).';

COMMENT ON COLUMN REQUIREMENT_LIBRARY_NODE.DESCRIPTION IS 'Une description associee a cet element. Devrait etre un champ texte long ou un clob.';

COMMENT ON COLUMN REQUIREMENT_LIBRARY_NODE.NAME IS 'Le nom/label de l''element en question.';

COMMENT ON COLUMN REQUIREMENT_LIBRARY_NODE.CREATED_BY IS 'nom du createur de ce cas de test.';

COMMENT ON COLUMN REQUIREMENT_LIBRARY_NODE.CREATED_ON IS 'Date de creation de cette exigence. Il s''agit de la date de creation de l''entree dans la base, non d''une date metier.';

COMMENT ON COLUMN REQUIREMENT_LIBRARY_NODE.LAST_MODIFIED_BY IS 'Nom du dernier utilisateur ayant mis a jour cet instance.';

COMMENT ON COLUMN REQUIREMENT_LIBRARY_NODE.LAST_MODIFIED_ON IS 'Date de derniere mise a jour de cette instance.';

COMMENT ON COLUMN REQUIREMENT_LIBRARY_NODE.PROJECT_ID IS 'FK to the Project to which belongs this resource';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table represente un element (generique) faisant partie d''une bibliotheque d''exigences. Cet element 
				generique peut representer soit un repertoire, soit une exigence. Pour cette raison, que l''utilisateur cree l''un ou l''autre 
				cette table s...', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml', 'tm-0.14.0.14', '2.0.5', '3:a9d2e473eafcd420919c707d59260567', 29);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.15::bsiri::(Checksum: 3:dabf7a16ee4eea69980a88a02299a585)
-- Cette table represente un element (generique) faisant partie 
-- 				d'une bibliotheque de campagne. Cet element generique peut representer soit 
-- 				un repertoire, soit une campagne. Pour cette raison, que l'utilisateur cree 
-- 				l'un ou l'autre cette table sera necessairement alimentee, et contiendra 
-- 				la cle primaire de l'element en question. Toutefois le type reel de l'element 
-- 				(campagne ou repertoire) n'est pas renseigne, il est necessaire de rechercher 
-- 				si la cle est referencee dans la table CAMPAIGN_FOLDER ou CAMPAIGN pour determiner 
-- 				si cet element releve de l'un ou l'autre.
CREATE TABLE CAMPAIGN_LIBRARY_NODE (CLN_ID bigserial NOT NULL, DELETED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, DESCRIPTION TEXT DEFAULT null, NAME VARCHAR(255) NOT NULL, CREATED_BY VARCHAR(50) NOT NULL, CREATED_ON TIMESTAMP WITH TIME ZONE NOT NULL, LAST_MODIFIED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, LAST_MODIFIED_BY VARCHAR(50) DEFAULT null, PROJECT_ID BIGINT DEFAULT NULL, CONSTRAINT PK_CAMPAIGN_LIBRARY_NODE PRIMARY KEY (CLN_ID), CONSTRAINT fk_cln_project FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID));

COMMENT ON COLUMN CAMPAIGN_LIBRARY_NODE.CLN_ID IS 'cle primaire autogeneree';

COMMENT ON COLUMN CAMPAIGN_LIBRARY_NODE.DELETED_ON IS 'drapeau indiquant si l''element est actif (NULL) ou logiquement supprime (not NULL).';

COMMENT ON COLUMN CAMPAIGN_LIBRARY_NODE.DESCRIPTION IS 'Une description associee a cet element. Devrait etre un champ texte long ou un clob.';

COMMENT ON COLUMN CAMPAIGN_LIBRARY_NODE.NAME IS 'Le nom/label de l''element en question.';

COMMENT ON COLUMN CAMPAIGN_LIBRARY_NODE.CREATED_BY IS 'nom du createur de cette campagne.';

COMMENT ON COLUMN CAMPAIGN_LIBRARY_NODE.CREATED_ON IS 'Date de creation de cette campagne. Il s''agit de la date de creation de l''entree dans     la base, non d''une date metier.';

COMMENT ON COLUMN CAMPAIGN_LIBRARY_NODE.LAST_MODIFIED_ON IS 'Date de derniere mise a jour de cette instance.';

COMMENT ON COLUMN CAMPAIGN_LIBRARY_NODE.LAST_MODIFIED_BY IS 'Nom du dernier utilisateur ayant mis a jour cet instance.';

COMMENT ON COLUMN CAMPAIGN_LIBRARY_NODE.PROJECT_ID IS 'FK to the Project to which belongs this resource';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table represente un element (generique) faisant partie 
				d''une bibliotheque de campagne. Cet element generique peut representer soit 
				un repertoire, soit une campagne. Pour cette raison, que l''utilisateur cree 
				l''un ou l''autre cette ta...', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml', 'tm-0.14.0.15', '2.0.5', '3:dabf7a16ee4eea69980a88a02299a585', 30);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.16::bsiri::(Checksum: 3:958c749a03fa027fdd348b7d4923fc52)
-- Cette table gere les cas de test proprement dits. Une entree dans cette table indique que l'element reference dans la table TEST_CASE_LIBRARY_NODE par la cle primaire (egalement cle etrangere) est de type cas de test.
CREATE TABLE TEST_CASE (TCLN_ID BIGINT NOT NULL, "VERSION" INT NOT NULL, EXECUTION_MODE VARCHAR(255) DEFAULT null, ATTACHMENT_LIST_ID BIGINT NOT NULL, CONSTRAINT PK_TEST_CASSE PRIMARY KEY (TCLN_ID), CONSTRAINT fk_test_case_test_case_node FOREIGN KEY (TCLN_ID) REFERENCES TEST_CASE_LIBRARY_NODE(TCLN_ID), CONSTRAINT fk_test_case_attachment_list FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST(ATTACHMENT_LIST_ID));

COMMENT ON COLUMN TEST_CASE.TCLN_ID IS 'cle primaire, qui est aussi une cle etrangere vers la table TEST_CASE_LIBRARY_NODE. Ne doit donc pas etre autogeneree.';

COMMENT ON COLUMN TEST_CASE."VERSION" IS 'Le numero de version de ce cas de test';

COMMENT ON COLUMN TEST_CASE.EXECUTION_MODE IS 'Mode d''execution du test case. Bien que ce champs soit caractere variable, les donnees reelles seront la representation texte d''un type enumere.';

COMMENT ON COLUMN TEST_CASE.ATTACHMENT_LIST_ID IS 'clé étrangère vers un ATTACHMENT_LIST, l''entité qui gère les pièces jointes.';

CREATE INDEX idx_test_case ON TEST_CASE(TCLN_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table gere les cas de test proprement dits. Une entree dans cette table indique que l''element reference dans la table TEST_CASE_LIBRARY_NODE par la cle primaire (egalement cle etrangere) est de type cas de test.', 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-0.15.0.xml', 'tm-0.14.0.16', '2.0.5', '3:958c749a03fa027fdd348b7d4923fc52', 31);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.17::bsiri::(Checksum: 3:7ab96cee4052f3f5d3d3202a15b0f734)
-- Cette table est rudimentaire. La cle primaire est simplement une cle etrangere vers la table TEST_CASE_LIBRARY_NODE (relation qualifiante). 
-- 		Une entree dans cette table indique que l'element indexe par cette cle est de type repertoire.
CREATE TABLE TEST_CASE_FOLDER (TCLN_ID BIGINT NOT NULL, CONSTRAINT PK_TEST_CASE_FOLDER PRIMARY KEY (TCLN_ID), CONSTRAINT fk_folder_test_case_node FOREIGN KEY (TCLN_ID) REFERENCES TEST_CASE_LIBRARY_NODE(TCLN_ID));

COMMENT ON COLUMN TEST_CASE_FOLDER.TCLN_ID IS 'cle primaire, qui est une cle etrangere vers la table CAMPAIGN_LIBRARY_NODE. Ne doit donc pas etre autogeneree.';

CREATE INDEX idx_tc_folder ON TEST_CASE_FOLDER(TCLN_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table est rudimentaire. La cle primaire est simplement une cle etrangere vers la table TEST_CASE_LIBRARY_NODE (relation qualifiante). 
		Une entree dans cette table indique que l''element indexe par cette cle est de type repertoire.', 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-0.15.0.xml', 'tm-0.14.0.17', '2.0.5', '3:7ab96cee4052f3f5d3d3202a15b0f734', 32);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.18::bsiri::(Checksum: 3:974cd4efa946c3c586e4cf657e3da0f5)
-- Cette table associe les bibliotheques de cas de test a leur contenu (elements de bibliotheques de cas de test). 
-- 		Le contenu d'une bibliotheque de cas de test est arborescent et est represente suivant un patron composite 
-- 		(voire TEST_CASE_LIBRARY_NODE, TEST_CASE_FOLDER, TEST_CASE). Par defaut la presente table peut contenir uniquement 
-- 		les references vers les elements fils directs; on peut aussi mettre en relation une bibliotheque de cas de test avec 
-- 		n'importe quel element de son contenu (quelque soit sa profondeur hierarchique) pour des raisons d'optimisation.
CREATE TABLE TEST_CASE_LIBRARY_CONTENT (LIBRARY_ID BIGINT NOT NULL, CONTENT_ID BIGINT NOT NULL, CONSTRAINT fk_test_case_lib_content_content FOREIGN KEY (CONTENT_ID) REFERENCES TEST_CASE_LIBRARY_NODE(TCLN_ID), CONSTRAINT fk_test_case_lib_content_lib FOREIGN KEY (LIBRARY_ID) REFERENCES TEST_CASE_LIBRARY(TCL_ID), UNIQUE (CONTENT_ID));

COMMENT ON COLUMN TEST_CASE_LIBRARY_CONTENT.LIBRARY_ID IS 'cle etrangere vers une bibliotheque de cas de test.';

COMMENT ON COLUMN TEST_CASE_LIBRARY_CONTENT.CONTENT_ID IS 'cle etrangere vers un noeud de la bibliotheque de cas de test.';

CREATE INDEX idx_test_case_lib_content_content ON TEST_CASE_LIBRARY_CONTENT(CONTENT_ID);

CREATE INDEX idx_test_case_lib_content_lib ON TEST_CASE_LIBRARY_CONTENT(LIBRARY_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table associe les bibliotheques de cas de test a leur contenu (elements de bibliotheques de cas de test). 
		Le contenu d''une bibliotheque de cas de test est arborescent et est represente suivant un patron composite 
		(voire TEST_CASE_LIBRARY_NO...', 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-0.15.0.xml', 'tm-0.14.0.18', '2.0.5', '3:974cd4efa946c3c586e4cf657e3da0f5', 33);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.19::bsiri::(Checksum: 3:afae4a16d26c6b777160d1c69eeb7980)
-- Cette table gere la hierarchie entre elements d'une bibliotheque de cas de test, associant un element fils a 
-- 		un repertoire parent (si non NULL) ou eventuellement aucun (NULL, dans le cas ou l'element est a la racine de la bibliotheque 
-- 		de cas de test).
CREATE TABLE TCLN_RELATIONSHIP (ANCESTOR_ID BIGINT NOT NULL, DESCENDANT_ID BIGINT NOT NULL, CONSTRAINT fk_tcln_relationship_ancestor FOREIGN KEY (ANCESTOR_ID) REFERENCES TEST_CASE_FOLDER(TCLN_ID), CONSTRAINT fk_tcln_relationship_descendant FOREIGN KEY (DESCENDANT_ID) REFERENCES TEST_CASE_LIBRARY_NODE(TCLN_ID), UNIQUE (DESCENDANT_ID));

COMMENT ON COLUMN TCLN_RELATIONSHIP.ANCESTOR_ID IS 'cle etrangere vers le repertoire parent (il s''agit necessairement d''un repertoire).';

COMMENT ON COLUMN TCLN_RELATIONSHIP.DESCENDANT_ID IS 'cle etrangere vers l''element fils.';

CREATE INDEX idx_tcln_relationship_descendant ON TCLN_RELATIONSHIP(DESCENDANT_ID);

CREATE INDEX idx_tcln_relationship_ancestor ON TCLN_RELATIONSHIP(ANCESTOR_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table gere la hierarchie entre elements d''une bibliotheque de cas de test, associant un element fils a 
		un repertoire parent (si non NULL) ou eventuellement aucun (NULL, dans le cas ou l''element est a la racine de la bibliotheque 
		de cas de t...', 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-0.15.0.xml', 'tm-0.14.0.19', '2.0.5', '3:afae4a16d26c6b777160d1c69eeb7980', 34);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.20::bsiri::(Checksum: 3:a338d63bb4e8bdc84a9be5748180a008)
-- Cette table gere les campagnes proprement dites. Une entree 
-- 				dans cette table indique que l'element reference dans la table CAMPAIGN_LIBRARY_NODE 
-- 				par la cle primaire (egalement cle etrangere) est de type campagne.
CREATE TABLE CAMPAIGN (CLN_ID BIGINT NOT NULL, ACTUAL_END_AUTO BOOLEAN NOT NULL, ACTUAL_END_DATE TIMESTAMP WITH TIME ZONE DEFAULT null, ACTUAL_START_AUTO BOOLEAN NOT NULL, ACTUAL_START_DATE TIMESTAMP WITH TIME ZONE DEFAULT null, SCHEDULED_END_DATE TIMESTAMP WITH TIME ZONE DEFAULT null, SCHEDULED_START_DATE TIMESTAMP WITH TIME ZONE DEFAULT null, ATTACHMENT_LIST_ID BIGINT, CONSTRAINT PK_CAMPAIGN PRIMARY KEY (CLN_ID), CONSTRAINT FK_CAMPAIGN_CL_NODE FOREIGN KEY (CLN_ID) REFERENCES CAMPAIGN_LIBRARY_NODE(CLN_ID), CONSTRAINT fk_campaign_attachment_list FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST(ATTACHMENT_LIST_ID));

COMMENT ON COLUMN CAMPAIGN.ACTUAL_END_AUTO IS 'Drapeau indiquant si l''utilisateur a choisit une determination automatique de la date de depart effective ou non.';

COMMENT ON COLUMN CAMPAIGN.ACTUAL_END_DATE IS 'Date de fin effective (reelle) de la campagne.';

COMMENT ON COLUMN CAMPAIGN.ACTUAL_START_AUTO IS 'Drapeau indiquant si l''utilisateur a choisit une determination automatique de la date de fin effective ou non.';

COMMENT ON COLUMN CAMPAIGN.ACTUAL_START_DATE IS 'Date de depar effective (reelle) de la campagne.';

COMMENT ON COLUMN CAMPAIGN.SCHEDULED_END_DATE IS 'Date planifiee de fin de la campagne.';

COMMENT ON COLUMN CAMPAIGN.SCHEDULED_START_DATE IS 'Date planifiee de debut de la campagne.';

COMMENT ON COLUMN CAMPAIGN.ATTACHMENT_LIST_ID IS 'clé étrangère vers un ATTACHMENT_LIST, l''entité qui gère les pièces jointes.';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table gere les campagnes proprement dites. Une entree 
				dans cette table indique que l''element reference dans la table CAMPAIGN_LIBRARY_NODE 
				par la cle primaire (egalement cle etrangere) est de type campagne.', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml', 'tm-0.14.0.20', '2.0.5', '3:a338d63bb4e8bdc84a9be5748180a008', 35);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.21::bsiri::(Checksum: 3:097d056da8cab56d6c7510dca188b264)
-- Cette table est rudimentaire. La cle primaire est simplement 
-- 				une cle etrangere vers la table CAMPAIGN_LIBRARY_NODE (relation qualifiante). 
-- 				Une entree dans cette table indique que l'element indexe par cette cle est 
-- 				de type repertoire.
CREATE TABLE CAMPAIGN_FOLDER (CLN_ID BIGINT NOT NULL, CONSTRAINT PK_CAMPAIGN_FOLDER PRIMARY KEY (CLN_ID), CONSTRAINT FK_CAMPAIGN_FOLDER_CL_NODE FOREIGN KEY (CLN_ID) REFERENCES CAMPAIGN_LIBRARY_NODE(CLN_ID));

COMMENT ON COLUMN CAMPAIGN_FOLDER.CLN_ID IS 'cle primaire, qui est une cle etrangere vers la table CAMPAIGN_LIBRARY_NODE. Ne doit donc pas etre autogeneree.';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table est rudimentaire. La cle primaire est simplement 
				une cle etrangere vers la table CAMPAIGN_LIBRARY_NODE (relation qualifiante). 
				Une entree dans cette table indique que l''element indexe par cette cle est 
				de type repertoire.', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml', 'tm-0.14.0.21', '2.0.5', '3:097d056da8cab56d6c7510dca188b264', 36);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.22::bsiri::(Checksum: 3:a45d3cfc2a298842fef802a55c8f7779)
-- Cette table gere les bibliotheques de campagnes. Une bibliotheque 
-- 				de campagnes peut contenir plusieurs repertoires et/ou campagnes, ces deux 
-- 				entites etant regroupees sous l'appellation generique d'element de bibliotheque 
-- 				de campagnes (campaign library nodes).
CREATE TABLE CAMPAIGN_LIBRARY_CONTENT (LIBRARY_ID BIGINT NOT NULL, CONTENT_ID BIGINT NOT NULL, CONSTRAINT fk_cl_content_content FOREIGN KEY (CONTENT_ID) REFERENCES CAMPAIGN_LIBRARY_NODE(CLN_ID), CONSTRAINT fk_cl_content_lib FOREIGN KEY (LIBRARY_ID) REFERENCES CAMPAIGN_LIBRARY(CL_ID), UNIQUE (CONTENT_ID));

COMMENT ON COLUMN CAMPAIGN_LIBRARY_CONTENT.LIBRARY_ID IS 'cle etrangere vers une bibliotheque de campagnes.';

COMMENT ON COLUMN CAMPAIGN_LIBRARY_CONTENT.CONTENT_ID IS 'cle etrangere vers un noeud de la bibliotheque de campagnes.';

CREATE UNIQUE INDEX dx_cl_content_cl_node ON CAMPAIGN_LIBRARY_CONTENT(CONTENT_ID);

CREATE INDEX dx_cl_content_campaign_lib ON CAMPAIGN_LIBRARY_CONTENT(LIBRARY_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table gere les bibliotheques de campagnes. Une bibliotheque 
				de campagnes peut contenir plusieurs repertoires et/ou campagnes, ces deux 
				entites etant regroupees sous l''appellation generique d''element de bibliotheque 
				de campagnes (ca...', 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-0.15.0.xml', 'tm-0.14.0.22', '2.0.5', '3:a45d3cfc2a298842fef802a55c8f7779', 37);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.23::bsiri::(Checksum: 3:d2df248f7905d7cd3031d82abae63bf4)
-- Cette table gere la hierarchie entre elements d'une bibliotheque 
-- 				de campagne, associant un element fils a un repertoire parent (si non NULL) 
-- 				ou eventuellement aucun (NULL, dans le cas ou l'element est a la racine de 
-- 				la bibliotheque de campagne).
CREATE TABLE CLN_RELATIONSHIP (ANCESTOR_ID BIGINT NOT NULL, DESCENDANT_ID BIGINT NOT NULL, CONSTRAINT idx_cln_relationship_descendant FOREIGN KEY (DESCENDANT_ID) REFERENCES CAMPAIGN_LIBRARY_NODE(CLN_ID), CONSTRAINT fk_cln_relationship_ancestor FOREIGN KEY (ANCESTOR_ID) REFERENCES CAMPAIGN_FOLDER(CLN_ID), UNIQUE (DESCENDANT_ID));

COMMENT ON COLUMN CLN_RELATIONSHIP.ANCESTOR_ID IS 'cle etrangere vers le repertoire parent (il s''agit necessairement d''un repertoire).';

COMMENT ON COLUMN CLN_RELATIONSHIP.DESCENDANT_ID IS 'cle etrangere vers l''element fils.';

CREATE INDEX dx_cln_relationship_descendant ON CLN_RELATIONSHIP(DESCENDANT_ID);

CREATE INDEX dx_cln_relationship_ancestor ON CLN_RELATIONSHIP(ANCESTOR_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table gere la hierarchie entre elements d''une bibliotheque 
				de campagne, associant un element fils a un repertoire parent (si non NULL) 
				ou eventuellement aucun (NULL, dans le cas ou l''element est a la racine de 
				la bibliotheque de ca...', 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-0.15.0.xml', 'tm-0.14.0.23', '2.0.5', '3:d2df248f7905d7cd3031d82abae63bf4', 38);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.24::bsiri::(Checksum: 3:73bf704391132aab5dba024c0ac13c38)
CREATE TABLE ITERATION (ITERATION_ID bigserial NOT NULL, DELETED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, DESCRIPTION TEXT DEFAULT null, NAME VARCHAR(255) NOT NULL, CREATED_BY VARCHAR(50) NOT NULL, CREATED_ON TIMESTAMP WITH TIME ZONE NOT NULL, LAST_MODIFIED_BY VARCHAR(50) DEFAULT null, LAST_MODIFIED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, ACTUAL_END_AUTO BOOLEAN NOT NULL, ACTUAL_END_DATE TIMESTAMP WITH TIME ZONE DEFAULT null, ACTUAL_START_AUTO BOOLEAN NOT NULL, ACTUAL_START_DATE TIMESTAMP WITH TIME ZONE DEFAULT null, SCHEDULED_END_DATE TIMESTAMP WITH TIME ZONE DEFAULT null, SCHEDULED_START_DATE TIMESTAMP WITH TIME ZONE DEFAULT null, ATTACHMENT_LIST_ID BIGINT, CONSTRAINT PK_ITERATION PRIMARY KEY (ITERATION_ID), CONSTRAINT fk_iteration__attachment_list FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST(ATTACHMENT_LIST_ID));

COMMENT ON COLUMN ITERATION.ATTACHMENT_LIST_ID IS 'clé étrangère vers un ATTACHMENT_LIST, l''entité qui gère les pièces jointes.';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', '', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml', 'tm-0.14.0.24', '2.0.5', '3:73bf704391132aab5dba024c0ac13c38', 39);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.25::bsiri::(Checksum: 3:51e8cc1f164086246ecead34823b1448)
CREATE TABLE CAMPAIGN_ITERATION (CAMPAIGN_ID BIGINT NOT NULL, ITERATION_ID BIGINT NOT NULL, ITERATION_ORDER INT NOT NULL, CONSTRAINT fk_camp_iter_iteration FOREIGN KEY (ITERATION_ID) REFERENCES ITERATION(ITERATION_ID), CONSTRAINT fk_camp_iter_campaign FOREIGN KEY (CAMPAIGN_ID) REFERENCES CAMPAIGN(CLN_ID));

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', '', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml', 'tm-0.14.0.25', '2.0.5', '3:51e8cc1f164086246ecead34823b1448', 40);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.26::bsiri::(Checksum: 3:5a513e8d87c090f9bc4b47b47bf05c8a)
-- Cette table gere les pas de test d'un cas de test.
CREATE TABLE TEST_STEP (TEST_STEP_ID bigserial NOT NULL, EXPECTED_RESULT TEXT DEFAULT null, ACTION TEXT NOT NULL, ATTACHMENT_LIST_ID BIGINT, CONSTRAINT PK_TEST_STEP PRIMARY KEY (TEST_STEP_ID), CONSTRAINT fk_test_step__attachment_list FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST(ATTACHMENT_LIST_ID));

COMMENT ON COLUMN TEST_STEP.TEST_STEP_ID IS 'cle primaire autogeneree.';

COMMENT ON COLUMN TEST_STEP.EXPECTED_RESULT IS 'le resultat attendu de l''action effectuee.';

COMMENT ON COLUMN TEST_STEP.ACTION IS 'action a realiser dans ce pas de test.';

COMMENT ON COLUMN TEST_STEP.ATTACHMENT_LIST_ID IS 'clé étrangère vers un ATTACHMENT_LIST, l''entité qui gère les pièces jointes.';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table gere les pas de test d''un cas de test.', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml', 'tm-0.14.0.26', '2.0.5', '3:5a513e8d87c090f9bc4b47b47bf05c8a', 41);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.27::bsiri::(Checksum: 3:f7aa9c081ea1f80aabaa9d9e35cdf43a)
-- Cette table associe un cas de test a ses pas de test. Meme si virtuellement la relation entre un cas de 
-- 		test et sa liste de pas de test est 1..*, cette table d'association existe pour ajouter un attribut d'ordre (la 
-- 		liste des pas de test est ordonnee). La cle primaire est ici composite, et ne doit pas etre autogeneree.
CREATE TABLE TEST_CASE_STEPS (TEST_CASE_ID BIGINT NOT NULL, STEP_ID BIGINT NOT NULL, STEP_ORDER INT NOT NULL, CONSTRAINT fk_tc_steps_tc FOREIGN KEY (TEST_CASE_ID) REFERENCES TEST_CASE(TCLN_ID), CONSTRAINT fk_tc_steps_step FOREIGN KEY (STEP_ID) REFERENCES TEST_STEP(TEST_STEP_ID));

COMMENT ON COLUMN TEST_CASE_STEPS.TEST_CASE_ID IS 'cle etrangere vers TEST_CASE.';

COMMENT ON COLUMN TEST_CASE_STEPS.STEP_ID IS 'cle etrangere vers la table TEST_STEP.';

COMMENT ON COLUMN TEST_CASE_STEPS.STEP_ORDER IS 'numero d''ordre d''un pas de test dans la liste des pas de test d''un cas de test.';

CREATE INDEX idx_tc_steps_step ON TEST_CASE_STEPS(STEP_ID);

CREATE INDEX idx_tc_steps_tc ON TEST_CASE_STEPS(TEST_CASE_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table associe un cas de test a ses pas de test. Meme si virtuellement la relation entre un cas de 
		test et sa liste de pas de test est 1..*, cette table d''association existe pour ajouter un attribut d''ordre (la 
		liste des pas de test est ordo...', 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-0.15.0.xml', 'tm-0.14.0.27', '2.0.5', '3:f7aa9c081ea1f80aabaa9d9e35cdf43a', 42);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.28::xpr::(Checksum: 3:f0fe8221736982edf61f00098266d93f)
CREATE TABLE PROJECT_FILTER (PROJECT_FILTER_ID bigserial NOT NULL, ACTIVATED BOOLEAN DEFAULT FALSE NOT NULL, USER_LOGIN VARCHAR(255) NOT NULL, CONSTRAINT PK_PROJECT_FILTER PRIMARY KEY (PROJECT_FILTER_ID));

COMMENT ON COLUMN PROJECT_FILTER.ACTIVATED IS 'filter activated yes or no';

COMMENT ON COLUMN PROJECT_FILTER.USER_LOGIN IS 'unique user login';

CREATE INDEX DX_UN_PROJECT_FILTER_USER ON PROJECT_FILTER(USER_LOGIN);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('xpr', '', 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-0.15.0.xml', 'tm-0.14.0.28', '2.0.5', '3:f0fe8221736982edf61f00098266d93f', 43);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.29::xpr::(Checksum: 3:84be3a27181bbaf2e53d715219078843)
CREATE TABLE PROJECT_FILTER_ENTRY (PROJECT_ID BIGINT NOT NULL, FILTER_ID BIGINT NOT NULL, CONSTRAINT FK_PROJ_FILTER_ENTRY_PROJ FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID), CONSTRAINT FK_PROJ_FILTER_ENTRY_PROJ_FILTER FOREIGN KEY (FILTER_ID) REFERENCES PROJECT_FILTER(PROJECT_FILTER_ID));

COMMENT ON COLUMN PROJECT_FILTER_ENTRY.PROJECT_ID IS 'foreign key to project';

COMMENT ON COLUMN PROJECT_FILTER_ENTRY.FILTER_ID IS 'foreign key to filter';

ALTER TABLE PROJECT_FILTER_ENTRY ADD CONSTRAINT PK_PROJ_FILTER_ENTRY PRIMARY KEY (PROJECT_ID, FILTER_ID);

CREATE INDEX dx_pfp_project ON PROJECT_FILTER_ENTRY(PROJECT_ID);

CREATE INDEX dx_pfp_filter ON PROJECT_FILTER_ENTRY(FILTER_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('xpr', '', NOW(), 'Create Table, Add Primary Key, Create Index (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml', 'tm-0.14.0.29', '2.0.5', '3:84be3a27181bbaf2e53d715219078843', 44);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.30::bsiri::(Checksum: 3:4e4246703f7bfc305b0e14b9ed587820)
-- Cette table gere les exigences proprement dites. Une entree dans cette table indique que l'element reference 
-- 				dans la table REQUIREMENT_LIBRARY_NODE par la cle primaire (egalement cle etrangere) est de type exigence.
CREATE TABLE REQUIREMENT (RLN_ID BIGINT NOT NULL, REFERENCE VARCHAR(20), CRITICALITY VARCHAR(255) DEFAULT 'UNDEFINED' NOT NULL, ATTACHMENT_LIST_ID BIGINT NOT NULL, CONSTRAINT PK_REQUIREMENT PRIMARY KEY (RLN_ID), CONSTRAINT fk_requirement_requirement_node FOREIGN KEY (RLN_ID) REFERENCES REQUIREMENT_LIBRARY_NODE(RLN_ID), CONSTRAINT fk_requirement_attachment_list FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST(ATTACHMENT_LIST_ID));

COMMENT ON COLUMN REQUIREMENT.RLN_ID IS 'cle primaire, qui est aussi une cle etrangere vers la table REQUIREMENT_LIBRARY_NODE. Ne doit donc pas etre autogeneree.';

COMMENT ON COLUMN REQUIREMENT.REFERENCE IS 'Identifiant de l''exigence';

COMMENT ON COLUMN REQUIREMENT.CRITICALITY IS 'Requirement criticality. Althought this field is a variable character, the data will be the text representation of an enumeration';

COMMENT ON COLUMN REQUIREMENT.ATTACHMENT_LIST_ID IS 'clé étrangère vers un ATTACHMENT_LIST, l''entité qui gère les pièces jointes.';

CREATE INDEX idx_requirement ON REQUIREMENT(RLN_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table gere les exigences proprement dites. Une entree dans cette table indique que l''element reference 
				dans la table REQUIREMENT_LIBRARY_NODE par la cle primaire (egalement cle etrangere) est de type exigence.', 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-0.15.0.xml', 'tm-0.14.0.30', '2.0.5', '3:4e4246703f7bfc305b0e14b9ed587820', 45);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.31::bsiri::(Checksum: 3:df70c5c8de2e94240a24f3eda7a972dc)
-- Cette table est rudimentaire. La cle primaire est simplement une cle etrangere vers la table REQUIREMENT_LIBRARY_NODE 
-- 				(relation qualifiante). Une entree dans cette table indique que l'element indexe par cette cle est de type repertoire.
CREATE TABLE REQUIREMENT_FOLDER (RLN_ID BIGINT NOT NULL, CONSTRAINT PK_REQUIREMENT_FOLDER PRIMARY KEY (RLN_ID), CONSTRAINT fk_folder_requirement_node FOREIGN KEY (RLN_ID) REFERENCES REQUIREMENT_LIBRARY_NODE(RLN_ID));

COMMENT ON COLUMN REQUIREMENT_FOLDER.RLN_ID IS 'cle primaire, qui est une cle etrangere vers la table REQUIREMENT_LIBRARY_NODE. Ne doit donc pas etre autogeneree.';

CREATE INDEX idx_req_folder ON REQUIREMENT_FOLDER(RLN_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table est rudimentaire. La cle primaire est simplement une cle etrangere vers la table REQUIREMENT_LIBRARY_NODE 
				(relation qualifiante). Une entree dans cette table indique que l''element indexe par cette cle est de type repertoire.', 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-0.15.0.xml', 'tm-0.14.0.31', '2.0.5', '3:df70c5c8de2e94240a24f3eda7a972dc', 46);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.32::bsiri::(Checksum: 3:f3c140be4ee750aaf78e1b6985ecfbac)
-- Cette table associe les bibliotheques d'exigences a leur contenu (elements de bibliotheques d'exigence). 
-- 		Le contenu d'une bibliotheque d'exigence est arborescent et est represente suivant un patron composite (voire REQUIREMENT_LIBRARY_NODE, 
-- 		REQUIREMENT_FOLDER, REQUIREMENT). Par defaut la presente table peut contenir uniquement les references vers les elements 
-- 		fils directs; on peut aussi mettre en relation une bibliotheque d'exigence avec n'importe quel element de son contenu (quelque 
-- 		soit sa profondeur hierarchique) pour des raisons d'optimisation.
CREATE TABLE REQUIREMENT_LIBRARY_CONTENT (LIBRARY_ID BIGINT NOT NULL, CONTENT_ID BIGINT NOT NULL, CONSTRAINT fk_requirement_lib_content_lib FOREIGN KEY (LIBRARY_ID) REFERENCES REQUIREMENT_LIBRARY(RL_ID), CONSTRAINT fk_requirement_lib_content_content FOREIGN KEY (CONTENT_ID) REFERENCES REQUIREMENT_LIBRARY_NODE(RLN_ID), UNIQUE (CONTENT_ID));

COMMENT ON COLUMN REQUIREMENT_LIBRARY_CONTENT.LIBRARY_ID IS 'cle etrangere vers une bibliotheque d''exigence.';

COMMENT ON COLUMN REQUIREMENT_LIBRARY_CONTENT.CONTENT_ID IS 'cle etrangere vers un noeud de la bibliotheque d''exigences.';

CREATE INDEX idx_requirement_lib_content_content ON REQUIREMENT_LIBRARY_CONTENT(CONTENT_ID);

CREATE INDEX idx_requirement_lib_content_lib ON REQUIREMENT_LIBRARY_CONTENT(LIBRARY_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table associe les bibliotheques d''exigences a leur contenu (elements de bibliotheques d''exigence). 
		Le contenu d''une bibliotheque d''exigence est arborescent et est represente suivant un patron composite (voire REQUIREMENT_LIBRARY_NODE, 
		REQUI...', 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-0.15.0.xml', 'tm-0.14.0.32', '2.0.5', '3:f3c140be4ee750aaf78e1b6985ecfbac', 47);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.33::bsiri::(Checksum: 3:2871df094de3dea7956c501df28c6a3c)
-- Cette table gere la hierarchie entre elements d'une bibliotheque d'exigences, associant un element fils 
-- 				a un repertoire parent.
CREATE TABLE RLN_RELATIONSHIP (ANCESTOR_ID BIGINT NOT NULL, DESCENDANT_ID BIGINT NOT NULL, CONSTRAINT fk_rln_relationship_descendant FOREIGN KEY (DESCENDANT_ID) REFERENCES REQUIREMENT_LIBRARY_NODE(RLN_ID), CONSTRAINT fk_rln_relationship_ancestor FOREIGN KEY (ANCESTOR_ID) REFERENCES REQUIREMENT_FOLDER(RLN_ID), UNIQUE (DESCENDANT_ID));

COMMENT ON COLUMN RLN_RELATIONSHIP.ANCESTOR_ID IS 'cle etrangere vers le repertoire parent (il s''agit necessairement d''un repertoire).';

COMMENT ON COLUMN RLN_RELATIONSHIP.DESCENDANT_ID IS 'cle etrangere vers l''element fils.';

CREATE INDEX idx_rln_relationship_descendant ON RLN_RELATIONSHIP(DESCENDANT_ID);

CREATE INDEX idx_rln_relationship_ancestor ON RLN_RELATIONSHIP(ANCESTOR_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table gere la hierarchie entre elements d''une bibliotheque d''exigences, associant un element fils 
				a un repertoire parent.', 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-0.15.0.xml', 'tm-0.14.0.33', '2.0.5', '3:2871df094de3dea7956c501df28c6a3c', 48);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.34::bsiri::(Checksum: 3:677016450819c882153db2d9093348b1)
-- Cette table associe un cas de test avec une liste d'execution. Elle conserve également
-- 		en mémoire le status d'execution, qui devra sera mis à jour au fur et à mesure qu'une execution 
-- 		est ajoutée ou exécutée.
CREATE TABLE ITEM_TEST_PLAN (ITEM_TEST_PLAN_ID bigserial NOT NULL, EXECUTION_STATUS VARCHAR(255) DEFAULT null, LAST_EXECUTED_BY VARCHAR(255) DEFAULT null, LAST_EXECUTED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, TCLN_ID BIGINT, LABEL VARCHAR(255), CREATED_BY VARCHAR(50) NOT NULL, CREATED_ON TIMESTAMP WITH TIME ZONE NOT NULL, LAST_MODIFIED_BY VARCHAR(50) DEFAULT null, LAST_MODIFIED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, CONSTRAINT PK_ITEM_TEST_PLAN PRIMARY KEY (ITEM_TEST_PLAN_ID), CONSTRAINT fk_itertestplan_test_case FOREIGN KEY (TCLN_ID) REFERENCES TEST_CASE(TCLN_ID));

COMMENT ON COLUMN ITEM_TEST_PLAN.ITEM_TEST_PLAN_ID IS 'clé primaire du test plan. Devrait être autogénérée.';

COMMENT ON COLUMN ITEM_TEST_PLAN.EXECUTION_STATUS IS 'Status d''execution de ce plan de test. Bien que ce champs soit caractère variable, les données réelles     seront la représentation texte d''un type énuméré. Sera recalculé en fonction du status de     sa derniere execution';

COMMENT ON COLUMN ITEM_TEST_PLAN.LAST_EXECUTED_BY IS 'Nom du dernier utilisateur ayant execute cet instance.';

COMMENT ON COLUMN ITEM_TEST_PLAN.LAST_EXECUTED_ON IS 'Date de derniere execution de cette instance.';

COMMENT ON COLUMN ITEM_TEST_PLAN.TCLN_ID IS 'Clé étrangère vers le cas de test référencé';

COMMENT ON COLUMN ITEM_TEST_PLAN.LABEL IS 'Le label (duplicata du nom du cas de test) du test plan';

COMMENT ON COLUMN ITEM_TEST_PLAN.CREATED_BY IS 'nom du créateur du testplan';

COMMENT ON COLUMN ITEM_TEST_PLAN.CREATED_ON IS 'Date de création du test plan. Il s''agit de la date de création de l''entrée dans la base, non d''une date métier.';

COMMENT ON COLUMN ITEM_TEST_PLAN.LAST_MODIFIED_BY IS 'Nom du dernier utilisateur ayant mis à jour cet instance.';

COMMENT ON COLUMN ITEM_TEST_PLAN.LAST_MODIFIED_ON IS 'Date de dernière mise à jour de cette instance.';

CREATE INDEX idx_itertestplan ON ITEM_TEST_PLAN(ITEM_TEST_PLAN_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table associe un cas de test avec une liste d''execution. Elle conserve également
		en mémoire le status d''execution, qui devra sera mis à jour au fur et à mesure qu''une execution 
		est ajoutée ou exécutée.', 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-0.15.0.xml', 'tm-0.14.0.34', '2.0.5', '3:677016450819c882153db2d9093348b1', 49);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.35::bsiri::(Checksum: 3:bbb6238456ee0b3789507be210559f4a)
-- Cette table associe une iteration a ses test plans. Même si virtuellement la relation entre une iteration
-- 		 et sa liste de test plans est 1..*, cette table d'association existe pour ajouter un attribut d'ordre (la 
-- 		liste des test plans est ordonnée). La clé primaire est ici composite, et ne doit pas être autogénérée.
CREATE TABLE ITEM_TEST_PLAN_LIST (ITERATION_ID BIGINT NOT NULL, ITEM_TEST_PLAN_ID BIGINT NOT NULL, ITEM_TEST_PLAN_ORDER INT NOT NULL, CONSTRAINT fk_item_test_plan_iteration FOREIGN KEY (ITERATION_ID) REFERENCES ITERATION(ITERATION_ID), CONSTRAINT "fk_ITEM_TESTplan_testplan" FOREIGN KEY (ITEM_TEST_PLAN_ID) REFERENCES ITEM_TEST_PLAN(ITEM_TEST_PLAN_ID));

COMMENT ON COLUMN ITEM_TEST_PLAN_LIST.ITERATION_ID IS 'clé étrangère vers ITERATION.';

COMMENT ON COLUMN ITEM_TEST_PLAN_LIST.ITEM_TEST_PLAN_ID IS 'clé étrangère vers la table ITEM_TEST_PLAN';

COMMENT ON COLUMN ITEM_TEST_PLAN_LIST.ITEM_TEST_PLAN_ORDER IS 'numéro d''ordre d''une execution dans la liste des test plans d''une itération.';

CREATE INDEX "idx_ITEM_TESTplan_iteration" ON ITEM_TEST_PLAN_LIST(ITERATION_ID);

CREATE INDEX "idx_ITEM_TESTplan_testplan" ON ITEM_TEST_PLAN_LIST(ITEM_TEST_PLAN_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table associe une iteration a ses test plans. Même si virtuellement la relation entre une iteration
		 et sa liste de test plans est 1..*, cette table d''association existe pour ajouter un attribut d''ordre (la 
		liste des test plans est ordonnée)...', 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-0.15.0.xml', 'tm-0.14.0.35', '2.0.5', '3:bbb6238456ee0b3789507be210559f4a', 50);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.36::bsiri::(Checksum: 3:6db9b5c08ba6fc50bfb43f5109fa5a43)
-- Table des executions. Une execution correspond à un cas de test, et ses pas d'execution (voir EXECUTION_STEP) correspondent
-- 		à des pas de test. Pour assurer un contrôle sur les modifications de l'entité source une référence est gardée vers le 
-- 		cas de test source (de même que EXECUTION_STEP garde une référence vers le TEST_STEP correspondant, même si les données sont 
-- 		dans les faits dupliqués).
CREATE TABLE EXECUTION (EXECUTION_ID bigserial NOT NULL, TCLN_ID BIGINT, CREATED_BY VARCHAR(50) NOT NULL, CREATED_ON TIMESTAMP WITH TIME ZONE NOT NULL, LAST_MODIFIED_BY VARCHAR(50) DEFAULT null, LAST_MODIFIED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, DESCRIPTION TEXT DEFAULT null, NAME VARCHAR(255) NOT NULL, EXECUTION_MODE VARCHAR(255) DEFAULT null, EXECUTION_STATUS VARCHAR(255) DEFAULT null, LAST_EXECUTED_BY VARCHAR(255) DEFAULT null, LAST_EXECUTED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, ATTACHMENT_LIST_ID BIGINT, ISSUE_LIST_ID BIGINT NOT NULL, CONSTRAINT PK_EXECUTION PRIMARY KEY (EXECUTION_ID), CONSTRAINT fk_execution_test_case FOREIGN KEY (TCLN_ID) REFERENCES TEST_CASE(TCLN_ID), CONSTRAINT fk_execution_issue_list FOREIGN KEY (ISSUE_LIST_ID) REFERENCES ISSUE_LIST(ISSUE_LIST_ID), CONSTRAINT fk_execution_attachment_list FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST(ATTACHMENT_LIST_ID));

COMMENT ON COLUMN EXECUTION.EXECUTION_ID IS 'clé primaire de l''execution. Devrait être autogénérée.';

COMMENT ON COLUMN EXECUTION.TCLN_ID IS 'Clé étrangère vers le cas de test référencé. Cette information est redondante puisque      il s''agit toujours du même que celui du test plan, cependant cette information ne devant     jamais évoluer il n''y a pas de risque à dénormaliser';

COMMENT ON COLUMN EXECUTION.CREATED_BY IS 'nom du créateur de l''execution.';

COMMENT ON COLUMN EXECUTION.CREATED_ON IS 'Date de création de cette execution. Il s''agit de la date de création de l''entrée dans la base, non d''une date métier.';

COMMENT ON COLUMN EXECUTION.LAST_MODIFIED_BY IS 'Nom du dernier utilisateur ayant mis à jour cet instance.';

COMMENT ON COLUMN EXECUTION.LAST_MODIFIED_ON IS 'Date de dernière mise à jour de cette instance.';

COMMENT ON COLUMN EXECUTION.DESCRIPTION IS 'Une description associée à cet élément. Devrait être un champ texte long ou un clob.';

COMMENT ON COLUMN EXECUTION.NAME IS 'Le nom/label de l''élément en question.';

COMMENT ON COLUMN EXECUTION.EXECUTION_MODE IS 'Mode d''execution de cette execution. Bien que ce champs soit caractère variable, les données réelles     seront la représentation texte d''un type énuméré. C''est une copie du mode d''execution du cas de test référencé.';

COMMENT ON COLUMN EXECUTION.EXECUTION_STATUS IS 'Status d''execution de ce plan de test. Bien que ce champs soit caractère variable, les données réelles     seront la représentation texte d''un type énuméré. Sera recalculé en fonction du status de     sa derniere execution';

COMMENT ON COLUMN EXECUTION.LAST_EXECUTED_BY IS 'Nom du dernier utilisateur ayant execute cet instance.';

COMMENT ON COLUMN EXECUTION.LAST_EXECUTED_ON IS 'Date de derniere execution de cette instance.';

COMMENT ON COLUMN EXECUTION.ATTACHMENT_LIST_ID IS 'clé étrangère vers un ATTACHMENT_LIST, l''entité qui gère les pièces jointes.';

COMMENT ON COLUMN EXECUTION.ISSUE_LIST_ID IS 'clé étrangère vers un ISSUE_LIST, l''entité qui gère les listes d''issues.';

CREATE INDEX idx_execution ON EXECUTION(EXECUTION_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Table des executions. Une execution correspond à un cas de test, et ses pas d''execution (voir EXECUTION_STEP) correspondent
		à des pas de test. Pour assurer un contrôle sur les modifications de l''entité source une référence est gardée vers le 
		cas d...', 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-0.15.0.xml', 'tm-0.14.0.36', '2.0.5', '3:6db9b5c08ba6fc50bfb43f5109fa5a43', 51);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.37::bsiri::(Checksum: 3:2cc260f36456b3930ceede7332059452)
-- Associe les executions aux test plans
CREATE TABLE ITEM_TEST_PLAN_EXECUTION (ITEM_TEST_PLAN_ID BIGINT NOT NULL, EXECUTION_ID BIGINT NOT NULL, EXECUTION_ORDER INT NOT NULL, CONSTRAINT fk_testplan_exec_testplan FOREIGN KEY (ITEM_TEST_PLAN_ID) REFERENCES ITEM_TEST_PLAN(ITEM_TEST_PLAN_ID), CONSTRAINT fk_testplan_exec_exec FOREIGN KEY (EXECUTION_ID) REFERENCES EXECUTION(EXECUTION_ID));

COMMENT ON COLUMN ITEM_TEST_PLAN_EXECUTION.ITEM_TEST_PLAN_ID IS 'clé étrangère vers ITEM_TEST_PLAN.';

COMMENT ON COLUMN ITEM_TEST_PLAN_EXECUTION.EXECUTION_ID IS 'clé étrangère vers la table EXECUTION';

COMMENT ON COLUMN ITEM_TEST_PLAN_EXECUTION.EXECUTION_ORDER IS 'numéro d''ordre d''une execution dans la liste des test plans d''une itération.';

CREATE INDEX idx_testplan_exec_testplan ON ITEM_TEST_PLAN_EXECUTION(ITEM_TEST_PLAN_ID);

CREATE INDEX idx_testplan_exec_exec ON ITEM_TEST_PLAN_EXECUTION(EXECUTION_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Associe les executions aux test plans', 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-0.15.0.xml', 'tm-0.14.0.37', '2.0.5', '3:2cc260f36456b3930ceede7332059452', 52);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.38::bsiri::(Checksum: 3:0b7eeee1a4e20b8418ec30b1d220cbf5)
-- Cette table gère les pas d'execution d'une execution. Voir la table EXECUTION pour plus de détails.
CREATE TABLE EXECUTION_STEP (EXECUTION_STEP_ID bigserial NOT NULL, EXPECTED_RESULT TEXT DEFAULT null, ACTION TEXT NOT NULL, EXECUTION_STATUS VARCHAR(255) DEFAULT null, LAST_EXECUTED_BY VARCHAR(255) DEFAULT null, LAST_EXECUTED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, COMMENT TEXT, TEST_STEP_ID BIGINT, CREATED_BY VARCHAR(50) NOT NULL, CREATED_ON TIMESTAMP WITH TIME ZONE NOT NULL, LAST_MODIFIED_BY VARCHAR(50) DEFAULT null, LAST_MODIFIED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, ATTACHMENT_LIST_ID BIGINT, ISSUE_LIST_ID BIGINT NOT NULL, CONSTRAINT PK_EXECUTION_STEP PRIMARY KEY (EXECUTION_STEP_ID), CONSTRAINT fk_execution_step__attachment_list FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST(ATTACHMENT_LIST_ID), CONSTRAINT fk_executionstep_teststep FOREIGN KEY (TEST_STEP_ID) REFERENCES TEST_STEP(TEST_STEP_ID), CONSTRAINT fk_execution_step_issue_list FOREIGN KEY (ISSUE_LIST_ID) REFERENCES ISSUE_LIST(ISSUE_LIST_ID));

COMMENT ON COLUMN EXECUTION_STEP.EXECUTION_STEP_ID IS 'clé primaire autogénérée.';

COMMENT ON COLUMN EXECUTION_STEP.EXPECTED_RESULT IS 'le résultat attendu de l''action effectuée.';

COMMENT ON COLUMN EXECUTION_STEP.ACTION IS 'action à réaliser dans ce pas d''execution.';

COMMENT ON COLUMN EXECUTION_STEP.EXECUTION_STATUS IS 'Status de l''execution de ce pas d''execution. Bien que ce champs soit caractère variable, les données réelles     seront la représentation texte d''un type énuméré.';

COMMENT ON COLUMN EXECUTION_STEP.LAST_EXECUTED_BY IS 'Nom du dernier utilisateur ayant execute cet instance.';

COMMENT ON COLUMN EXECUTION_STEP.LAST_EXECUTED_ON IS 'Date de derniere execution de cette instance.';

COMMENT ON COLUMN EXECUTION_STEP.COMMENT IS 'commentaire libre du recetteur.';

COMMENT ON COLUMN EXECUTION_STEP.TEST_STEP_ID IS 'Clé étrangère vers le pas de test référencé';

COMMENT ON COLUMN EXECUTION_STEP.CREATED_BY IS 'nom du créateur du pas d''execution.';

COMMENT ON COLUMN EXECUTION_STEP.CREATED_ON IS 'Date de création de ce pas d''execution. Il s''agit de la date de création de l''entrée dans la base, non d''une date métier.';

COMMENT ON COLUMN EXECUTION_STEP.LAST_MODIFIED_BY IS 'Nom du dernier utilisateur ayant mis à jour cet instance.';

COMMENT ON COLUMN EXECUTION_STEP.LAST_MODIFIED_ON IS 'Date de dernière mise à jour de cette instance.';

COMMENT ON COLUMN EXECUTION_STEP.ATTACHMENT_LIST_ID IS 'clé étrangère vers un ATTACHMENT_LIST, l''entité qui gère les pièces jointes.';

COMMENT ON COLUMN EXECUTION_STEP.ISSUE_LIST_ID IS 'clé étrangère vers un ISSUE_LIST, l''entité qui gère les listes d''issues.';

CREATE INDEX idx_executionstep ON EXECUTION_STEP(EXECUTION_STEP_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table gère les pas d''execution d''une execution. Voir la table EXECUTION pour plus de détails.', 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-0.15.0.xml', 'tm-0.14.0.38', '2.0.5', '3:0b7eeee1a4e20b8418ec30b1d220cbf5', 53);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.39::bsiri::(Checksum: 3:973dad84ab8afd2ed0428f2fcad18c33)
-- Cette table associe une executions à ses pas d'execution. Même si virtuellement la relation entre une execution
-- 		 et sa liste de pas d'execution est 1..*, cette table d'association existe pour ajouter un attribut d'ordre (la 
-- 		liste des pas executions est ordonnée). La clé primaire est ici composite, et ne doit pas être autogénérée.
CREATE TABLE EXECUTION_EXECUTION_STEPS (EXECUTION_ID BIGINT NOT NULL, EXECUTION_STEP_ID BIGINT NOT NULL, EXECUTION_STEP_ORDER INT NOT NULL, CONSTRAINT fk_exec_exec_step_exec_step FOREIGN KEY (EXECUTION_STEP_ID) REFERENCES EXECUTION_STEP(EXECUTION_STEP_ID), CONSTRAINT fk_exec_exec_step_exec FOREIGN KEY (EXECUTION_ID) REFERENCES EXECUTION(EXECUTION_ID));

COMMENT ON COLUMN EXECUTION_EXECUTION_STEPS.EXECUTION_ID IS 'clé étrangère vers la table EXECUTION.';

COMMENT ON COLUMN EXECUTION_EXECUTION_STEPS.EXECUTION_STEP_ID IS 'clé étrangère vers EXECUTION_STEP';

COMMENT ON COLUMN EXECUTION_EXECUTION_STEPS.EXECUTION_STEP_ORDER IS 'numéro d''ordre d''un pas d''execution dans la liste des pas d''executions d''une execution.';

CREATE INDEX idx_exec_exec_step_exec ON EXECUTION_EXECUTION_STEPS(EXECUTION_ID);

CREATE INDEX idx_exec_exec_step_exec_step ON EXECUTION_EXECUTION_STEPS(EXECUTION_STEP_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table associe une executions à ses pas d''execution. Même si virtuellement la relation entre une execution
		 et sa liste de pas d''execution est 1..*, cette table d''association existe pour ajouter un attribut d''ordre (la 
		liste des pas execution...', 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-0.15.0.xml', 'tm-0.14.0.39', '2.0.5', '3:973dad84ab8afd2ed0428f2fcad18c33', 54);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.40::bsiri::(Checksum: 3:022678424f32aff0ea457d87cfc81c39)
-- Cette table gere les associations entre cas de test et exigences. 
-- 				La cle primaire est composite et ne doit pas etre autogeneree.
CREATE TABLE TEST_CASE_REQUIREMENT_LINK (TEST_CASE_ID BIGINT NOT NULL, REQUIREMENT_ID BIGINT NOT NULL, CONSTRAINT fk_tc_req_tc FOREIGN KEY (TEST_CASE_ID) REFERENCES TEST_CASE(TCLN_ID), CONSTRAINT fk_tc_req_req FOREIGN KEY (REQUIREMENT_ID) REFERENCES REQUIREMENT(RLN_ID));

COMMENT ON COLUMN TEST_CASE_REQUIREMENT_LINK.TEST_CASE_ID IS 'cle etrangere vers TEST_CASE';

COMMENT ON COLUMN TEST_CASE_REQUIREMENT_LINK.REQUIREMENT_ID IS 'cle etrangere vers REQUIREMENT';

ALTER TABLE TEST_CASE_REQUIREMENT_LINK ADD CONSTRAINT pk_tc_req_link PRIMARY KEY (TEST_CASE_ID, REQUIREMENT_ID);

CREATE INDEX idx_tc_req_tc ON TEST_CASE_REQUIREMENT_LINK(TEST_CASE_ID);

CREATE INDEX idx_tc_req_req ON TEST_CASE_REQUIREMENT_LINK(REQUIREMENT_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Cette table gere les associations entre cas de test et exigences. 
				La cle primaire est composite et ne doit pas etre autogeneree.', NOW(), 'Create Table, Add Primary Key, Create Index (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml', 'tm-0.14.0.40', '2.0.5', '3:022678424f32aff0ea457d87cfc81c39', 55);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.41::adurand::(Checksum: 3:05303033f44dedf713634bc5d2a8f876)
-- Cette table gere les associations entre les campagnes et les cas de test (= les plans de test des campagnes).La cle primaire est composite et ne doit pas etre autogeneree.
CREATE TABLE CAMPAIGN_TEST_PLAN (CAMPAIGN_ID BIGINT NOT NULL, TEST_CASE_ID BIGINT NOT NULL, CONSTRAINT fk_camp_tp_camp FOREIGN KEY (CAMPAIGN_ID) REFERENCES CAMPAIGN(CLN_ID), CONSTRAINT fk_camp_tp_tc FOREIGN KEY (TEST_CASE_ID) REFERENCES TEST_CASE(TCLN_ID));

COMMENT ON COLUMN CAMPAIGN_TEST_PLAN.CAMPAIGN_ID IS 'cle etrangere vers CAMPAIGN';

COMMENT ON COLUMN CAMPAIGN_TEST_PLAN.TEST_CASE_ID IS 'cle etrangere vers TEST_CASE';

ALTER TABLE CAMPAIGN_TEST_PLAN ADD CONSTRAINT pk_camp_tp PRIMARY KEY (CAMPAIGN_ID, TEST_CASE_ID);

CREATE INDEX idx_camp_tp_camp ON CAMPAIGN_TEST_PLAN(CAMPAIGN_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('adurand', 'Cette table gere les associations entre les campagnes et les cas de test (= les plans de test des campagnes).La cle primaire est composite et ne doit pas etre autogeneree.', NOW(), 'Create Table, Add Primary Key, Create Index', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml', 'tm-0.14.0.41', '2.0.5', '3:05303033f44dedf713634bc5d2a8f876', 56);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.42::bsiri::(Checksum: 3:efcf1c28e93336d052ae489901878a3a)
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.43::xpr::(Checksum: 3:ae6e2167fb4b8136983297802a1c7d60)
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.44::gfouquet::(Checksum: 3:67ee96d82ee3e4cc07bf2c0e252cee36)
INSERT INTO CORE_GROUP_AUTHORITY (AUTHORITY, GROUP_ID) VALUES ('ROLE_TM_USER', (select ID from CORE_GROUP where QUALIFIED_NAME='squashtest.authz.group.core.Admin'));

INSERT INTO CORE_GROUP (QUALIFIED_NAME) VALUES ('squashtest.tm.group.User');

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

INSERT INTO CORE_GROUP (QUALIFIED_NAME) VALUES ('squashtest.authz.group.tm.ProjectManager');

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

INSERT INTO ACL_CLASS (CLASSNAME) VALUES ('org.squashtest.csp.tm.domain.project.Project');

INSERT INTO ACL_GROUP (QUALIFIED_NAME) VALUES ('squashtest.acl.group.tm.ProjectManager');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '15');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '2');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '4');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '8');

INSERT INTO ACL_GROUP (QUALIFIED_NAME) VALUES ('squashtest.acl.group.tm.TestEditor');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '15');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '2');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '4');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '8');

INSERT INTO ACL_GROUP (QUALIFIED_NAME) VALUES ('squashtest.acl.group.tm.TestRunner');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestRunner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '1');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', '', NOW(), 'Insert Row (x20)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml', 'tm-0.14.0.44', '2.0.5', '3:67ee96d82ee3e4cc07bf2c0e252cee36', 57);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.15.0.xml::tm-0.14.0.45::gfouquet::(Checksum: 3:17d0e76b39bf9550b5d66f84108de444)
INSERT INTO ACL_GROUP (QUALIFIED_NAME) VALUES ('squashtest.acl.group.tm.ProjectViewer');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectViewer'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '1');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', '', 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-0.15.0.xml', 'tm-0.14.0.45', '2.0.5', '3:17d0e76b39bf9550b5d66f84108de444', 58);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.17.0.xml::tm-0.17.0.01::gfouquet::(Checksum: 3:98ccb741049fc7ae4b5e4d02b64ae4e8)
-- This table stores "action" tests steps, i.e. steps with an action and expected result, which were formerly stored in TEST_STEP
CREATE TABLE ACTION_TEST_STEP (TEST_STEP_ID BIGINT NOT NULL, ACTION TEXT NOT NULL, EXPECTED_RESULT TEXT DEFAULT null, ATTACHMENT_LIST_ID BIGINT, CONSTRAINT pk_action_step PRIMARY KEY (TEST_STEP_ID), CONSTRAINT fk_action_step_test_step FOREIGN KEY (TEST_STEP_ID) REFERENCES TEST_STEP(TEST_STEP_ID), CONSTRAINT fk_action_test_step_attachment_list FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST(ATTACHMENT_LIST_ID));

COMMENT ON COLUMN ACTION_TEST_STEP.TEST_STEP_ID IS 'Shared key with TEST_STEP table';

COMMENT ON COLUMN ACTION_TEST_STEP.ACTION IS 'The step''s action';

COMMENT ON COLUMN ACTION_TEST_STEP.EXPECTED_RESULT IS 'The step''s expected result';

COMMENT ON COLUMN ACTION_TEST_STEP.ATTACHMENT_LIST_ID IS 'clé étrangère vers un ATTACHMENT_LIST, l''entité qui gère les pièces jointes.';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'This table stores "action" tests steps, i.e. steps with an action and expected result, which were formerly stored in TEST_STEP', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.17.0.xml', 'tm-0.17.0.01', '2.0.5', '3:98ccb741049fc7ae4b5e4d02b64ae4e8', 59);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.17.0.xml::tm-0.17.0.02::gfouquet::(Checksum: 3:a54eda81c8b073f49f3c48b969724007)
-- Data migration from TEST_STEP to ACTION_TEST_STEP
insert into ACTION_TEST_STEP (TEST_STEP_ID, ACTION, EXPECTED_RESULT, ATTACHMENT_LIST_ID) select TEST_STEP_ID, ACTION, EXPECTED_RESULT, ATTACHMENT_LIST_ID from TEST_STEP;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Data migration from TEST_STEP to ACTION_TEST_STEP', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.17.0.xml', 'tm-0.17.0.02', '2.0.5', '3:a54eda81c8b073f49f3c48b969724007', 60);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.17.0.xml::tm-0.17.0.03::gfouquet::(Checksum: 3:24b184e1522e87284c5bc4435010d822)
-- Removes useless comlumns from TEST_STEP
ALTER TABLE TEST_STEP DROP CONSTRAINT fk_test_step__attachment_list;

ALTER TABLE TEST_STEP DROP COLUMN ACTION;

ALTER TABLE TEST_STEP DROP COLUMN EXPECTED_RESULT;

ALTER TABLE TEST_STEP DROP COLUMN ATTACHMENT_LIST_ID;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Removes useless comlumns from TEST_STEP', NOW(), 'Drop Foreign Key Constraint, Drop Column (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.17.0.xml', 'tm-0.17.0.03', '2.0.5', '3:24b184e1522e87284c5bc4435010d822', 61);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.17.0.xml::tm-0.17.0.04::gfouquet::(Checksum: 3:f17d3f5ef7678112090526434ab02012)
-- This table stores "call" tests steps, i.e. steps which call an entire test case
CREATE TABLE CALL_TEST_STEP (TEST_STEP_ID BIGINT NOT NULL, CALLED_TEST_CASE_ID BIGINT NOT NULL, CONSTRAINT pk_call_step PRIMARY KEY (TEST_STEP_ID), CONSTRAINT fk_call_step_called_test_case FOREIGN KEY (CALLED_TEST_CASE_ID) REFERENCES TEST_CASE(TCLN_ID), CONSTRAINT fk_call_step_test_step FOREIGN KEY (TEST_STEP_ID) REFERENCES TEST_STEP(TEST_STEP_ID));

COMMENT ON COLUMN CALL_TEST_STEP.TEST_STEP_ID IS 'Shared key with TEST_STEP table';

COMMENT ON COLUMN CALL_TEST_STEP.CALLED_TEST_CASE_ID IS 'ID of called test case';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'This table stores "call" tests steps, i.e. steps which call an entire test case', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.17.0.xml', 'tm-0.17.0.04', '2.0.5', '3:f17d3f5ef7678112090526434ab02012', 62);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.19.0.xml::tm-0.19.0.01::ramoussou::(Checksum: 3:a2c617f3cd30d4df478396383ec1120b)
-- We have to set campaign_ID and Test_plan_order to "true" to let hibernate persist new items
CREATE TABLE CAMPAIGN_TEST_PLAN_ITEM (CTPI_ID bigserial NOT NULL, CAMPAIGN_ID BIGINT, TEST_CASE_ID BIGINT NOT NULL, USER_ID BIGINT, TEST_PLAN_ORDER INT, CONSTRAINT PK_CAMPAIGN_TEST_PLAN_ITEM PRIMARY KEY (CTPI_ID), CONSTRAINT fk_camp_tp_user FOREIGN KEY (USER_ID) REFERENCES CORE_USER(ID), CONSTRAINT fk_camp_tp_testcase FOREIGN KEY (TEST_CASE_ID) REFERENCES TEST_CASE(TCLN_ID), CONSTRAINT fk_camp_tp_campaign FOREIGN KEY (CAMPAIGN_ID) REFERENCES CAMPAIGN(CLN_ID));

COMMENT ON COLUMN CAMPAIGN_TEST_PLAN_ITEM.CTPI_ID IS 'clé primaire du test plan. Devrait être autogénérée.';

COMMENT ON COLUMN CAMPAIGN_TEST_PLAN_ITEM.CAMPAIGN_ID IS 'cle etrangere vers CAMPAIGN';

COMMENT ON COLUMN CAMPAIGN_TEST_PLAN_ITEM.TEST_CASE_ID IS 'cle etrangere vers TEST_CASE';

COMMENT ON COLUMN CAMPAIGN_TEST_PLAN_ITEM.USER_ID IS 'Id de l''utilisateur assigné a ce cas de test reférenncé';

COMMENT ON COLUMN CAMPAIGN_TEST_PLAN_ITEM.TEST_PLAN_ORDER IS 'numéro d''ordre d''une execution dans la liste des test plans d''une itération.';

CREATE INDEX idx_camptestplan ON CAMPAIGN_TEST_PLAN_ITEM(CTPI_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('ramoussou', 'We have to set campaign_ID and Test_plan_order to "true" to let hibernate persist new items', 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-0.19.0.xml', 'tm-0.19.0.01', '2.0.5', '3:a2c617f3cd30d4df478396383ec1120b', 63);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.19.0.xml::tm-0.19.0.02::ramoussou::(Checksum: 3:bab4e9ace40d3f38f0a909166ffa3543)
-- Migrates data from unordered campaign test plan table to ordered test plan table. 
-- 	 		Items which references (soft) deleted test cases and which should not be in the table are not migrated
insert into CAMPAIGN_TEST_PLAN_ITEM (CAMPAIGN_ID, TEST_CASE_ID) 
			select tp.CAMPAIGN_ID, tp.TEST_CASE_ID 
			from CAMPAIGN_TEST_PLAN tp 
				inner join TEST_CASE_LIBRARY_NODE tc on tc.TCLN_ID = tp.TEST_CASE_ID
			where tc.DELETED_ON is null;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('ramoussou', 'Migrates data from unordered campaign test plan table to ordered test plan table. 
	 		Items which references (soft) deleted test cases and which should not be in the table are not migrated', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.19.0.xml', 'tm-0.19.0.02', '2.0.5', '3:bab4e9ace40d3f38f0a909166ffa3543', 64);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.19.0.xml::tm-0.19.0.03::ramoussou::(Checksum: 3:7cf5b162cc14dd208a967e41dfb3ad4e)
-- Calculate and insert and add Not null constraint on order for Campaign Test Plan For H2
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.19.0.xml::tm-0.19.0.04::ramoussou::(Checksum: 3:905488109451a024de6e2560c5076e65)
-- Calculate and insert and add Not null constraint on order for Campaign Test Plan for myssql
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.19.0.xml::tm-0.19.0.04-postgres::mpagnon::(Checksum: 3:8447241e5630abc830696ab5b207c8b2)
-- Calculate and insert and add Not null constraint on order for Campaign Test Plan for postgresql
CREATE  TABLE MIGRATION_CAMPAIGN_TEST_PLAN (
      TEST_PLAN_ORDER BIGINT NULL DEFAULT NULL,
      CAMPAIGN_ID BIGINT NULL DEFAULT NULL,
      TEST_CASE_ID BIGINT NULL DEFAULT NULL
      );

CREATE OR REPLACE FUNCTION testfunction() RETURNS text AS 
$$
DECLARE rn integer := 0-1;
rn2 integer := 0;
prevcamp bigint := 0;
r record;
BEGIN
FOR r in (select ctp.CAMPAIGN_ID, ctp.TEST_CASE_ID  from CAMPAIGN_TEST_PLAN_ITEM ctp order by CAMPAIGN_ID)
LOOP
	IF prevcamp  = r.CAMPAIGN_ID 
        THEN 
             rn2 := rn ;
             rn := rn2 + 1;
        ELSE rn := 0 ; 
             prevcamp := r.CAMPAIGN_ID;
        END IF;
	  insert into MIGRATION_CAMPAIGN_TEST_PLAN (TEST_PLAN_ORDER, CAMPAIGN_ID, TEST_CASE_ID)
       select rn, r.CAMPAIGN_ID , r.TEST_CASE_ID  ;
      
       

END LOOP;
return 'ok';
END
$$ LANGUAGE plpgsql;

select testfunction();
DROP FUNCTION TestFunction();

update CAMPAIGN_TEST_PLAN_ITEM citp set TEST_PLAN_ORDER 
      = 
        (select mctp.TEST_PLAN_ORDER from MIGRATION_CAMPAIGN_TEST_PLAN mctp 
        where mctp.CAMPAIGN_ID = citp.CAMPAIGN_ID 
        and mctp.TEST_CASE_ID = citp.TEST_CASE_ID);

drop table MIGRATION_CAMPAIGN_TEST_PLAN;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Calculate and insert and add Not null constraint on order for Campaign Test Plan for postgresql', NOW(), 'Custom SQL, Create Procedure, Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.19.0.xml', 'tm-0.19.0.04-postgres', '2.0.5', '3:8447241e5630abc830696ab5b207c8b2', 65);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.19.0.xml::tm-0.19.0.06::ramoussou::(Checksum: 3:f9e834dd9a237218a636c461a1f14acb)
-- Drop table CAMPAIGN_TEST_PLAN
DROP TABLE CAMPAIGN_TEST_PLAN;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('ramoussou', 'Drop table CAMPAIGN_TEST_PLAN', NOW(), 'Drop Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.19.0.xml', 'tm-0.19.0.06', '2.0.5', '3:f9e834dd9a237218a636c461a1f14acb', 66);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.19.0.xml::tm-0.19.0.07::ramoussou::(Checksum: 3:3464e0eb5a1a382e6691a4f727ec00e0)
ALTER TABLE ITEM_TEST_PLAN ADD USER_ID BIGINT;

ALTER TABLE ITEM_TEST_PLAN ADD CONSTRAINT fk_itertestplan_user FOREIGN KEY (USER_ID) REFERENCES CORE_USER (ID);

COMMENT ON COLUMN ITEM_TEST_PLAN.USER_ID IS 'Id de l''utilisateur assigné a ce cas de test reférenncé';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('ramoussou', '', NOW(), 'Add Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.19.0.xml', 'tm-0.19.0.07', '2.0.5', '3:3464e0eb5a1a382e6691a4f727ec00e0', 67);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.20.0.xml::tm-0.20.0.01::gfouquet::(Checksum: 3:79d0a7f41b244e13231f6617599db287)
-- The PROJECT_ID foreign key in TEST_CASE_LIBRARY is useless, the useful one is in the PROJECT table
ALTER TABLE TEST_CASE_LIBRARY DROP CONSTRAINT fk_tcl_project;

ALTER TABLE TEST_CASE_LIBRARY DROP COLUMN PROJECT_ID;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'The PROJECT_ID foreign key in TEST_CASE_LIBRARY is useless, the useful one is in the PROJECT table', NOW(), 'Drop Foreign Key Constraint, Drop Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.20.0.xml', 'tm-0.20.0.01', '2.0.5', '3:79d0a7f41b244e13231f6617599db287', 68);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.20.0.xml::tm-0.20.0.02::gfouquet::(Checksum: 3:90ee0d65007fd13cf852fd74c93cc5ab)
-- The PROJECT_ID foreign key in REQUIREMENT_LIBRARY is useless, the useful one is in the PROJECT table
ALTER TABLE REQUIREMENT_LIBRARY DROP CONSTRAINT fk_rl_project;

ALTER TABLE REQUIREMENT_LIBRARY DROP COLUMN PROJECT_ID;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'The PROJECT_ID foreign key in REQUIREMENT_LIBRARY is useless, the useful one is in the PROJECT table', NOW(), 'Drop Foreign Key Constraint, Drop Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.20.0.xml', 'tm-0.20.0.02', '2.0.5', '3:90ee0d65007fd13cf852fd74c93cc5ab', 69);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.20.0.xml::tm-0.20.0.03::gfouquet::(Checksum: 3:2839276f429b636cc801c6e05258e229)
-- The PROJECT_ID foreign key in CAMPAIGN_LIBRARY is useless, the useful one is in the PROJECT table
ALTER TABLE CAMPAIGN_LIBRARY DROP CONSTRAINT fk_cl_project;

ALTER TABLE CAMPAIGN_LIBRARY DROP COLUMN PROJECT_ID;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'The PROJECT_ID foreign key in CAMPAIGN_LIBRARY is useless, the useful one is in the PROJECT table', NOW(), 'Drop Foreign Key Constraint, Drop Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.20.0.xml', 'tm-0.20.0.03', '2.0.5', '3:2839276f429b636cc801c6e05258e229', 70);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.20.0.xml::tm-0.20.0.05::bsiri::(Checksum: 3:33f92afafccb2a461ebeee6b06c0d503)
ALTER TABLE EXECUTION ALTER COLUMN  ISSUE_LIST_ID DROP NOT NULL;

ALTER TABLE EXECUTION_STEP ALTER COLUMN  ISSUE_LIST_ID DROP NOT NULL;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', '', NOW(), 'Drop Not-Null Constraint (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.20.0.xml', 'tm-0.20.0.05', '2.0.5', '3:33f92afafccb2a461ebeee6b06c0d503', 71);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.20.0.xml::tm-0.20.0.07::bsiri::(Checksum: 3:01c34eb5d2046c3d206c2658ef6efdb8)
-- add an ondelete set null to the fk campaign test plan item  -> core user
ALTER TABLE CAMPAIGN_TEST_PLAN_ITEM DROP CONSTRAINT fk_camp_tp_user;

ALTER TABLE CAMPAIGN_TEST_PLAN_ITEM ADD CONSTRAINT fk_camp_tp_user FOREIGN KEY (USER_ID) REFERENCES CORE_USER (ID) ON DELETE SET NULL;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'add an ondelete set null to the fk campaign test plan item  -> core user', NOW(), 'Drop Foreign Key Constraint, Add Foreign Key Constraint', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.20.0.xml', 'tm-0.20.0.07', '2.0.5', '3:01c34eb5d2046c3d206c2658ef6efdb8', 72);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.20.0.xml::tm-0.20.0.40::gfouquet::(Checksum: 3:ab522a0d38bc1df143c84b75c668820b)
-- Replace the ATTACHMENT_LIST_CONTENT table by a FK in ATTACHMENT
ALTER TABLE ATTACHMENT ADD ATTACHMENT_LIST_ID BIGINT;

update ATTACHMENT a set ATTACHMENT_LIST_ID = (select AL_ID from ATTACHMENT_LIST_CONTENT alc where
			alc.ATTACHMENT_ID = a.ATTACHMENT_ID);

DROP TABLE ATTACHMENT_LIST_CONTENT;

ALTER TABLE ATTACHMENT ADD CONSTRAINT fk_attachment_att_list FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST (ATTACHMENT_LIST_ID);

ALTER TABLE ATTACHMENT ALTER COLUMN  ATTACHMENT_LIST_ID SET NOT NULL;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Replace the ATTACHMENT_LIST_CONTENT table by a FK in ATTACHMENT', NOW(), 'Add Column, Custom SQL, Drop Table, Add Foreign Key Constraint, 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-0.20.0.xml', 'tm-0.20.0.40', '2.0.5', '3:ab522a0d38bc1df143c84b75c668820b', 73);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.20.0.xml::tm-0.20.0.41::gfouquet::(Checksum: 3:726804bd77783ba5146a81d59aaab4ac)
-- Replace the ISSUE_LIST_CONTENT table by a FK in ISSUE
ALTER TABLE ISSUE ADD ISSUE_LIST_ID BIGINT;

COMMENT ON COLUMN ISSUE.ISSUE_LIST_ID IS 'References the list of issue to which an issue belongs';

update ISSUE i set ISSUE_LIST_ID = (select ISSUE_LIST_ID from ISSUE_LIST_CONTENT ilc where ilc.ISSUE_ID =
			i.ISSUE_ID);

DROP TABLE ISSUE_LIST_CONTENT;

ALTER TABLE ISSUE ADD CONSTRAINT fk_issue_issue_list FOREIGN KEY (ISSUE_LIST_ID) REFERENCES ISSUE_LIST (ISSUE_LIST_ID);

ALTER TABLE ISSUE ALTER COLUMN  ISSUE_LIST_ID SET NOT NULL;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Replace the ISSUE_LIST_CONTENT table by a FK in ISSUE', NOW(), 'Add Column, Custom SQL, Drop Table, Add Foreign Key Constraint, 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-0.20.0.xml', 'tm-0.20.0.41', '2.0.5', '3:726804bd77783ba5146a81d59aaab4ac', 74);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.20.0.xml::tm-0.20.0.42::gfouquet::(Checksum: 3:0fe189d2c826b59230a2e5fea194ff17)
-- Rename ITEM_TEST_PLAN into ITERATION_TEST_PLAN_ITEM
ALTER TABLE ITEM_TEST_PLAN RENAME TO ITERATION_TEST_PLAN_ITEM;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Rename ITEM_TEST_PLAN into ITERATION_TEST_PLAN_ITEM', NOW(), 'Rename Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.20.0.xml', 'tm-0.20.0.42', '2.0.5', '3:0fe189d2c826b59230a2e5fea194ff17', 75);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.23.0.xml::tm-0.23.0.01::bsiri::(Checksum: 3:4c7a622b9ce59f63f8a353974546e243)
-- remove ProjectManager and TestRunner from the ACL permission table
delete from ACL_GROUP_PERMISSION where ACL_GROUP_ID in 
			(select id from ACL_GROUP where qualified_name='squashtest.acl.group.tm.ProjectManager' 
			or qualified_name='squashtest.acl.group.tm.TestRunner');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'remove ProjectManager and TestRunner from the ACL permission table', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.23.0.xml', 'tm-0.23.0.01', '2.0.5', '3:4c7a622b9ce59f63f8a353974546e243', 76);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.23.0.xml::tm-0.23.0.02::bsiri::(Checksum: 3:0d9176e53082f38f2505081b41416b75)
-- all users that were ProjectManager or TestRunner on anything are now TestEditor
update ACL_RESPONSIBILITY_SCOPE_ENTRY
			set ACL_GROUP_ID = (select id from ACL_GROUP where qualified_name='squashtest.acl.group.tm.TestEditor')
			where ACL_GROUP_ID in (select id from ACL_GROUP where qualified_name='squashtest.acl.group.tm.ProjectManager' 
									or qualified_name='squashtest.acl.group.tm.TestRunner');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'all users that were ProjectManager or TestRunner on anything are now TestEditor', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.23.0.xml', 'tm-0.23.0.02', '2.0.5', '3:0d9176e53082f38f2505081b41416b75', 77);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.23.0.xml::tm-0.23.0.03::bsiri::(Checksum: 3:8de0f418634703a9aa5c73e391db6471)
-- remove ProjectManager and TestRunner from the existing acl groups
delete from ACL_GROUP
			where qualified_name='squashtest.acl.group.tm.ProjectManager' 
			or qualified_name='squashtest.acl.group.tm.TestRunner';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'remove ProjectManager and TestRunner from the existing acl groups', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-0.23.0.xml', 'tm-0.23.0.03', '2.0.5', '3:8de0f418634703a9aa5c73e391db6471', 78);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.01::gfouquet::(Checksum: 3:7b812bb021cbb1145febc9bad5624b79)
-- Corrects status to BLOCKED in EXECUTION table
UPDATE EXECUTION SET EXECUTION_STATUS = 'BLOCKED' WHERE EXECUTION_STATUS='BLOQUED';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Corrects status to BLOCKED in EXECUTION table', 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.1.0.xml', 'tm-1.1.0.01', '2.0.5', '3:7b812bb021cbb1145febc9bad5624b79', 79);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.02::gfouquet::(Checksum: 3:e0a5f002e81988762e6b1b2d329f84b1)
-- Corrects status to BLOCKED in EXECUTION_STEP table
UPDATE EXECUTION_STEP SET EXECUTION_STATUS = 'BLOCKED' WHERE EXECUTION_STATUS='BLOQUED';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Corrects status to BLOCKED in EXECUTION_STEP table', 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.1.0.xml', 'tm-1.1.0.02', '2.0.5', '3:e0a5f002e81988762e6b1b2d329f84b1', 80);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.03::gfouquet::(Checksum: 3:8ed2c90c46fdd119a126dfce9311bf50)
-- Corrects status to BLOCKED in EXECUTION_STEP table
UPDATE ITERATION_TEST_PLAN_ITEM SET EXECUTION_STATUS = 'BLOCKED' WHERE EXECUTION_STATUS='BLOQUED';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Corrects status to BLOCKED in EXECUTION_STEP table', 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.1.0.xml', 'tm-1.1.0.03', '2.0.5', '3:8ed2c90c46fdd119a126dfce9311bf50', 81);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.04::gfouquet::(Checksum: 3:602af0823f8965c71e6f8ba05124a2cd)
-- Adds an importance to test cases
ALTER TABLE TEST_CASE ADD IMPORTANCE VARCHAR(20) NOT NULL DEFAULT 'LOW';

COMMENT ON COLUMN TEST_CASE.IMPORTANCE IS 'The importance of the test case';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Adds an importance to test cases', 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.1.0.xml', 'tm-1.1.0.04', '2.0.5', '3:602af0823f8965c71e6f8ba05124a2cd', 82);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.05::gfouquet::(Checksum: 3:d600b40e7b3fb8526cd8184109d2dfbe)
-- Adds a auto compute importance property to test cases
ALTER TABLE TEST_CASE ADD IMPORTANCE_AUTO BOOLEAN NOT NULL DEFAULT FALSE;

COMMENT ON COLUMN TEST_CASE.IMPORTANCE_AUTO IS 'Should the importance be automatically computed';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Adds a auto compute importance property to test cases', 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.1.0.xml', 'tm-1.1.0.05', '2.0.5', '3:d600b40e7b3fb8526cd8184109d2dfbe', 83);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.06::gfouquet::(Checksum: 3:2e544b51db99e2b730f9bac869c54861)
-- Adds a TM database version number
INSERT INTO CORE_CONFIG (STR_KEY, VALUE) VALUES ('squashtest.tm.database.version', '1.1.0');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Adds a TM database version number', NOW(), 'Insert Row', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.06', '2.0.5', '3:2e544b51db99e2b730f9bac869c54861', 84);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.07::gfouquet::(Checksum: 3:7aeec9343a5b8e86f987d291a5edb778)
-- A tree node organizes resources. These are stored in the RESOURCE table
CREATE TABLE RESOURCE (RES_ID bigserial NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION TEXT DEFAULT null, CREATED_BY VARCHAR(50) NOT NULL, CREATED_ON TIMESTAMP WITH TIME ZONE NOT NULL, LAST_MODIFIED_BY VARCHAR(50) DEFAULT null, LAST_MODIFIED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, CONSTRAINT PK_RESOURCE PRIMARY KEY (RES_ID));

COMMENT ON COLUMN RESOURCE.RES_ID IS 'generated surrogate key';

COMMENT ON COLUMN RESOURCE.NAME IS 'Resource''s name';

COMMENT ON COLUMN RESOURCE.DESCRIPTION IS 'Resource''s description';

COMMENT ON COLUMN RESOURCE.CREATED_BY IS 'Who created this requirement''s version';

COMMENT ON COLUMN RESOURCE.CREATED_ON IS 'When this requirement''s version was created';

COMMENT ON COLUMN RESOURCE.LAST_MODIFIED_BY IS 'Who modified this requirement''s version last';

COMMENT ON COLUMN RESOURCE.LAST_MODIFIED_ON IS 'When this requirement''s version was last modified';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'A tree node organizes resources. These are stored in the RESOURCE table', 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.1.0.xml', 'tm-1.1.0.07', '2.0.5', '3:7aeec9343a5b8e86f987d291a5edb778', 85);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.08::gfouquet::(Checksum: 3:7969f710831387cc73bc194aa34d1b8c)
-- SIMPLE_RESOURCE stores "concrete" resources with a name and a description
CREATE TABLE SIMPLE_RESOURCE (RES_ID BIGINT NOT NULL, CONSTRAINT pk_simple_resource PRIMARY KEY (RES_ID), CONSTRAINT fk_resource FOREIGN KEY (RES_ID) REFERENCES RESOURCE(RES_ID));

COMMENT ON COLUMN SIMPLE_RESOURCE.RES_ID IS 'shared key with RESOURCE table';

ALTER TABLE REQUIREMENT_FOLDER ADD RES_ID BIGINT;

ALTER TABLE REQUIREMENT_FOLDER ADD CONSTRAINT fk_req_folder_simple_resource FOREIGN KEY (RES_ID) REFERENCES SIMPLE_RESOURCE (RES_ID);

COMMENT ON COLUMN REQUIREMENT_FOLDER.RES_ID IS 'FK to SIMPLE_RESOURCE table';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'SIMPLE_RESOURCE stores "concrete" resources with a name and a description', NOW(), 'Create Table, Add Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.08', '2.0.5', '3:7969f710831387cc73bc194aa34d1b8c', 86);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.09::gfouquet::(Checksum: 3:8eef6e42740a9a0de705a27dcbfa9ba8)
-- Moves requirement folder data into SIMPLE_RESOURCE table
insert into RESOURCE (RES_ID, NAME, DESCRIPTION, CREATED_BY, CREATED_ON, LAST_MODIFIED_BY, LAST_MODIFIED_ON) 
			select f.RLN_ID, NAME, DESCRIPTION, CREATED_BY, CREATED_ON, LAST_MODIFIED_BY, LAST_MODIFIED_ON
			from REQUIREMENT_LIBRARY_NODE n
			inner join REQUIREMENT_FOLDER f on n.RLN_Id = f.RLN_ID
			where n.DELETED_ON is null;

insert into SIMPLE_RESOURCE (RES_ID) 
			select res.RES_ID
			from REQUIREMENT_LIBRARY_NODE n
			inner join REQUIREMENT_FOLDER f on n.RLN_Id = f.RLN_ID
			inner join RESOURCE res on res.RES_ID = f.RLN_ID
			where n.DELETED_ON is null;

update REQUIREMENT_FOLDER 
			set RES_ID = RLN_ID
			where exists (select 1 from REQUIREMENT_LIBRARY_NODE where REQUIREMENT_LIBRARY_NODE.RLN_ID = REQUIREMENT_FOLDER.RLN_ID and DELETED_ON is null);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Moves requirement folder data into SIMPLE_RESOURCE table', 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.1.0.xml', 'tm-1.1.0.09', '2.0.5', '3:8eef6e42740a9a0de705a27dcbfa9ba8', 87);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.10::gfouquet::(Checksum: 3:11e8ae33c5eb2ee02e6046721193047f)
-- Creates REQUIREMENT_VERSION table in which we store the versions of requirements
CREATE TABLE REQUIREMENT_VERSION (RES_ID BIGINT NOT NULL, REQUIREMENT_ID BIGINT, REFERENCE VARCHAR(20), VERSION_NUMBER INT DEFAULT 1, CRITICALITY VARCHAR(50) DEFAULT 'UNDEFINED' NOT NULL, REQUIREMENT_STATUS VARCHAR(50) DEFAULT 'WORK_IN_PROGRESS' NOT NULL, ATTACHMENT_LIST_ID BIGINT NOT NULL, CONSTRAINT pk_requirement_version PRIMARY KEY (RES_ID), CONSTRAINT fk_req_version_resource FOREIGN KEY (RES_ID) REFERENCES RESOURCE(RES_ID), CONSTRAINT fk_requirement_version_requirement FOREIGN KEY (REQUIREMENT_ID) REFERENCES REQUIREMENT(RLN_ID), CONSTRAINT fk_requirement_version_attachment_list FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST(ATTACHMENT_LIST_ID));

COMMENT ON COLUMN REQUIREMENT_VERSION.RES_ID IS 'shared key with RESOURCE table';

COMMENT ON COLUMN REQUIREMENT_VERSION.REQUIREMENT_ID IS 'FK to the requirement. Should not be null but is nullable because of cyclic association REQ - REQ_VER';

COMMENT ON COLUMN REQUIREMENT_VERSION.REFERENCE IS 'The requirement''s reference';

COMMENT ON COLUMN REQUIREMENT_VERSION.VERSION_NUMBER IS 'This requirement''s version number';

COMMENT ON COLUMN REQUIREMENT_VERSION.CRITICALITY IS 'Requirement criticality. Althought this field is a variable character, the data will be the text representation of an enumeration';

COMMENT ON COLUMN REQUIREMENT_VERSION.ATTACHMENT_LIST_ID IS 'FK to a requirement''s list of attachments';

ALTER TABLE REQUIREMENT_VERSION ADD CONSTRAINT cu_version_of_requirement UNIQUE (REQUIREMENT_ID, VERSION_NUMBER);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Creates REQUIREMENT_VERSION table in which we store the versions of requirements', 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.1.0.xml', 'tm-1.1.0.10', '2.0.5', '3:11e8ae33c5eb2ee02e6046721193047f', 88);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.11::gfouquet::(Checksum: 3:0baecf978673f11a9f8dd9999fed2cd1)
-- Migrates requirement data into REQUIREMENT_VERSION table
insert into RESOURCE 
				(RES_ID, NAME, DESCRIPTION, CREATED_BY, CREATED_ON, LAST_MODIFIED_BY, LAST_MODIFIED_ON) 
			select r.RLN_ID, NAME, DESCRIPTION, CREATED_BY, CREATED_ON, LAST_MODIFIED_BY, LAST_MODIFIED_ON
			from REQUIREMENT_LIBRARY_NODE n
			inner join REQUIREMENT r on n.RLN_Id = r.RLN_ID
			where n.DELETED_ON is null;

insert into REQUIREMENT_VERSION 
				(RES_ID, REQUIREMENT_ID, REFERENCE, CRITICALITY, ATTACHMENT_LIST_ID) 
			select res.RES_ID, r.RLN_ID, REFERENCE, CRITICALITY, ATTACHMENT_LIST_ID
			from REQUIREMENT_LIBRARY_NODE n
			inner join REQUIREMENT r on n.RLN_Id = r.RLN_ID
			inner join RESOURCE res on res.RES_ID = r.RLN_ID
			where n.DELETED_ON is null;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Migrates requirement data into REQUIREMENT_VERSION table', 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.1.0.xml', 'tm-1.1.0.11', '2.0.5', '3:0baecf978673f11a9f8dd9999fed2cd1', 89);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.12::gfouquet::(Checksum: 3:4a2bca6beb7751e2c4a5f50b1ecb0c2b)
-- Adds the 'latest version' relationship between REQUIREMENT and REQUIREMENT_VERSION tables
ALTER TABLE REQUIREMENT ADD CURRENT_VERSION_ID BIGINT;

ALTER TABLE REQUIREMENT ADD CONSTRAINT fk_requirement_current_version FOREIGN KEY (CURRENT_VERSION_ID) REFERENCES REQUIREMENT_VERSION (RES_ID);

COMMENT ON COLUMN REQUIREMENT.CURRENT_VERSION_ID IS 'FK to REQUIREMENT_VERSION linking to the requirement''s latest version. Should not be null but the column is nullable because of cyclic relationship between the tables.';

update REQUIREMENT r
			set CURRENT_VERSION_ID = (
				select RES_ID 
				from REQUIREMENT_VERSION rv 
				where rv.REQUIREMENT_ID = r.RLN_ID
			);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Adds the ''latest version'' relationship between REQUIREMENT and REQUIREMENT_VERSION tables', NOW(), 'Add Column, Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.12', '2.0.5', '3:4a2bca6beb7751e2c4a5f50b1ecb0c2b', 90);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.13::gfouquet::(Checksum: 3:f808950f1318acd85849691d30ef7418)
-- Test Cases now cover Requirements versions. We create a new association table and populate it from TEST_CASE_REQUIREMENT_LINK
CREATE TABLE TEST_CASE_VERIFIED_REQUIREMENT_VERSION (VERIFIED_REQ_VERSION_ID BIGINT, VERIFYING_TEST_CASE_ID BIGINT, CONSTRAINT fk_verified_req_version FOREIGN KEY (VERIFIED_REQ_VERSION_ID) REFERENCES REQUIREMENT_VERSION(RES_ID), CONSTRAINT fk_verifying_test_case FOREIGN KEY (VERIFYING_TEST_CASE_ID) REFERENCES TEST_CASE(TCLN_ID));

COMMENT ON COLUMN TEST_CASE_VERIFIED_REQUIREMENT_VERSION.VERIFIED_REQ_VERSION_ID IS 'FK to the verified requirement';

COMMENT ON COLUMN TEST_CASE_VERIFIED_REQUIREMENT_VERSION.VERIFYING_TEST_CASE_ID IS 'FK to the verifying test case';

insert into TEST_CASE_VERIFIED_REQUIREMENT_VERSION (VERIFYING_TEST_CASE_ID, VERIFIED_REQ_VERSION_ID)
			select TEST_CASE_ID, RES_ID
			from TEST_CASE_REQUIREMENT_LINK tcrl
			inner join REQUIREMENT_VERSION rv on rv.REQUIREMENT_ID = tcrl.REQUIREMENT_ID;

ALTER TABLE TEST_CASE_VERIFIED_REQUIREMENT_VERSION ALTER COLUMN  VERIFIED_REQ_VERSION_ID SET NOT NULL;

ALTER TABLE TEST_CASE_VERIFIED_REQUIREMENT_VERSION ALTER COLUMN  VERIFYING_TEST_CASE_ID SET NOT NULL;

ALTER TABLE TEST_CASE_VERIFIED_REQUIREMENT_VERSION ADD CONSTRAINT uc_tc_verifies_req_version UNIQUE (VERIFIED_REQ_VERSION_ID, VERIFYING_TEST_CASE_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Test Cases now cover Requirements versions. We create a new association table and populate it from TEST_CASE_REQUIREMENT_LINK', NOW(), 'Create Table, Custom SQL, Add Not-Null Constraint (x2), 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.1.0.xml', 'tm-1.1.0.13', '2.0.5', '3:f808950f1318acd85849691d30ef7418', 91);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.14::gfouquet::(Checksum: 3:434950a4c673124b5466920aa1d43907)
-- Trashes the now useless TEST_CASE_REQUIREMENT_LINK table
ALTER TABLE TEST_CASE_REQUIREMENT_LINK DROP CONSTRAINT fk_tc_req_tc;

ALTER TABLE TEST_CASE_REQUIREMENT_LINK DROP CONSTRAINT fk_tc_req_req;

DROP TABLE TEST_CASE_REQUIREMENT_LINK;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Trashes the now useless TEST_CASE_REQUIREMENT_LINK table', NOW(), 'Drop Foreign Key Constraint (x2), Drop Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.14', '2.0.5', '3:434950a4c673124b5466920aa1d43907', 92);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.15::bsiri::(Checksum: 3:df9ca9a019aead911d53118c13fe27b7)
-- This table is the main table of the event logging system, in other words the history of a business entity. As for Squash TM
-- 		version 1.1.0 and until further notice, we log here requirement modifications only. It may grows to log other entities in the future, ie test cases.
-- 		Note : the application will fill this table itself, since we don't want triggers in the database.
CREATE TABLE REQUIREMENT_AUDIT_EVENT (EVENT_ID bigserial NOT NULL, REQ_VERSION_ID BIGINT NOT NULL, EVENT_DATE TIMESTAMP WITH TIME ZONE NOT NULL, AUTHOR VARCHAR(255) NOT NULL, CONSTRAINT PK_REQUIREMENT_AUDIT_EVENT PRIMARY KEY (EVENT_ID), CONSTRAINT fk_audit_event_requirement_version FOREIGN KEY (REQ_VERSION_ID) REFERENCES REQUIREMENT_VERSION(RES_ID));

COMMENT ON COLUMN REQUIREMENT_AUDIT_EVENT.REQ_VERSION_ID IS 'the ID of the target entity. As for 1.1.0 and until further notice, the target is always a requirement version.';

COMMENT ON COLUMN REQUIREMENT_AUDIT_EVENT.EVENT_DATE IS 'the timestamp of the log entry';

COMMENT ON COLUMN REQUIREMENT_AUDIT_EVENT.AUTHOR IS 'the login of the user who generated the event';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'This table is the main table of the event logging system, in other words the history of a business entity. As for Squash TM
		version 1.1.0 and until further notice, we log here requirement modifications only. It may grows to log other entities in the ...', 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.1.0.xml', 'tm-1.1.0.15', '2.0.5', '3:df9ca9a019aead911d53118c13fe27b7', 93);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.16::bsiri::(Checksum: 3:f7781e8a8128679b692a576e954b946d)
-- It is a child table of REQUIREMENT_AUDIT_EVENT. This table exists for the sole purpose of qualifying a given event in a creation event, as such its content is empty (the sheer presence
-- 		of an entry in this table is sufficient)
CREATE TABLE REQUIREMENT_CREATION (EVENT_ID BIGINT NOT NULL, CONSTRAINT PK_CREATION_EVENT PRIMARY KEY (EVENT_ID), CONSTRAINT fk_create_audit FOREIGN KEY (EVENT_ID) REFERENCES REQUIREMENT_AUDIT_EVENT(EVENT_ID));

COMMENT ON COLUMN REQUIREMENT_CREATION.EVENT_ID IS 'primary key, which is also a foreign key to REQUIREMENT_AUDIT_EVENT (they share that key : do not make this table autoincrement it).';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'It is a child table of REQUIREMENT_AUDIT_EVENT. This table exists for the sole purpose of qualifying a given event in a creation event, as such its content is empty (the sheer presence
		of an entry in this table is sufficient)', 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.1.0.xml', 'tm-1.1.0.16', '2.0.5', '3:f7781e8a8128679b692a576e954b946d', 94);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.17::bsiri::(Checksum: 3:04d14fac6434ead391f617b4f49e303a)
-- This is a child table of REQUIREMENT_AUDIT_EVENT. This table will log updates on the target entity. Not every operations will be logged. 
-- 			Note 1 : As for version 1.1.0 and until further notice for the sake of simplicity we log both the former and the new value. It may change in the future.
-- 			Note 2 : For technical reasons this table exists in two flavors. The twin table is REQUIREMENT_LARGE_PROPERTY_CHANGE. This table will log standard attributes, 
-- 			while the other one will log blobs.
CREATE TABLE REQUIREMENT_PROPERTY_CHANGE (EVENT_ID BIGINT NOT NULL, OLD_VALUE VARCHAR(100), NEW_VALUE VARCHAR(100), PROPERTY_NAME VARCHAR(100), CONSTRAINT PK_PPT_CHANGE PRIMARY KEY (EVENT_ID), CONSTRAINT "fk_PPT_change_audit" FOREIGN KEY (EVENT_ID) REFERENCES REQUIREMENT_AUDIT_EVENT(EVENT_ID));

COMMENT ON COLUMN REQUIREMENT_PROPERTY_CHANGE.EVENT_ID IS 'primary key, which is also a foreign key to REQUIREMENT_AUDIT_EVENT (they share that key : do not make this table autoincrement it).';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'This is a child table of REQUIREMENT_AUDIT_EVENT. This table will log updates on the target entity. Not every operations will be logged. 
			Note 1 : As for version 1.1.0 and until further notice for the sake of simplicity we log both the former and th...', 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.1.0.xml', 'tm-1.1.0.17', '2.0.5', '3:04d14fac6434ead391f617b4f49e303a', 95);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.18::bsiri::(Checksum: 3:f7bcd79666e2e120c0a26260a1028380)
-- This is a child table of REQUIREMENT_AUDIT_EVENT. This table will log updates on the target entity. Not every operations will be logged. 
-- 			Note 1 : As for version 1.1.0 and until further notice for the sake of simplicity we log both the former and the new value. It may change in the future.
-- 			Note 2 : For technical reasons this table exists in two flavors. The twin table is REQUIREMENT_PROPERTY_CHANGE. This table will log blobs, 
-- 			while the other one will log standard attributes.
CREATE TABLE REQUIREMENT_LARGE_PROPERTY_CHANGE (EVENT_ID BIGINT NOT NULL, OLD_VALUE TEXT DEFAULT null, NEW_VALUE TEXT DEFAULT null, PROPERTY_NAME VARCHAR(100), CONSTRAINT PK_LARGE_PPT_CHANGE PRIMARY KEY (EVENT_ID), CONSTRAINT "fk_lPPT_change_audit" FOREIGN KEY (EVENT_ID) REFERENCES REQUIREMENT_AUDIT_EVENT(EVENT_ID));

COMMENT ON COLUMN REQUIREMENT_LARGE_PROPERTY_CHANGE.EVENT_ID IS 'primary key, which is also a foreign key to REQUIREMENT_AUDIT_EVENT (they share that key : do not make this table autoincrement it).';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'This is a child table of REQUIREMENT_AUDIT_EVENT. This table will log updates on the target entity. Not every operations will be logged. 
			Note 1 : As for version 1.1.0 and until further notice for the sake of simplicity we log both the former and th...', 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.1.0.xml', 'tm-1.1.0.18', '2.0.5', '3:f7bcd79666e2e120c0a26260a1028380', 96);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.19::bsiri::(Checksum: 3:9a53e8e6e08eb787dbab66e10b85cfe9)
-- Will immediately update the tables with the create_events of existing requirements.
insert into REQUIREMENT_AUDIT_EVENT (REQ_VERSION_ID, EVENT_DATE, AUTHOR) 
			select rv.RES_ID, CREATED_ON, CREATED_BY  
			from REQUIREMENT_VERSION rv 
			inner join RESOURCE r on r.RES_ID = rv.RES_ID;

insert into REQUIREMENT_CREATION (event_id) select event_id from REQUIREMENT_AUDIT_EVENT;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Will immediately update the tables with the create_events of existing requirements.', 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.1.0.xml', 'tm-1.1.0.19', '2.0.5', '3:9a53e8e6e08eb787dbab66e10b85cfe9', 97);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.20::gfouquet::(Checksum: 3:bee2aa78c17ce9f26bdc145f4d2faa69)
-- Remove obsolete columns from REQUIREMENT and REQUIREMENT_LIBRARY_NODE table. 
-- 			Data is now in REQUIREMENT_VERSION and REQUIREMENT_FOLDER tables.
ALTER TABLE REQUIREMENT DROP COLUMN REFERENCE;

ALTER TABLE REQUIREMENT DROP COLUMN CRITICALITY;

ALTER TABLE REQUIREMENT DROP CONSTRAINT fk_requirement_attachment_list;

ALTER TABLE REQUIREMENT DROP COLUMN ATTACHMENT_LIST_ID;

ALTER TABLE REQUIREMENT_LIBRARY_NODE DROP COLUMN NAME;

ALTER TABLE REQUIREMENT_LIBRARY_NODE DROP COLUMN DESCRIPTION;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Remove obsolete columns from REQUIREMENT and REQUIREMENT_LIBRARY_NODE table. 
			Data is now in REQUIREMENT_VERSION and REQUIREMENT_FOLDER tables.', NOW(), 'Drop Column (x2), Drop Foreign Key Constraint, Drop Column (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.20', '2.0.5', '3:bee2aa78c17ce9f26bdc145f4d2faa69', 98);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.21::fogaillard::(Checksum: 3:749fd7e10d0f1275176e6e5379316cb8)
-- Add Prerequisite collumn to the test_case table
ALTER TABLE TEST_CASE ADD PREREQUISITE TEXT DEFAULT null;

COMMENT ON COLUMN TEST_CASE.PREREQUISITE IS 'the prerequisite for a test_case';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('fogaillard', 'Add Prerequisite collumn to the test_case table', 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.1.0.xml', 'tm-1.1.0.21', '2.0.5', '3:749fd7e10d0f1275176e6e5379316cb8', 99);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.22::bsiri::(Checksum: 3:f21b8cdf96f5a52a837b8a843b3c0dfd)
-- Add the table TEST_SUITE. A TEST_SUITE is basically a view on a subset of ITERATION_TEST_PLAN_ITEMs.
CREATE TABLE TEST_SUITE (ID bigserial NOT NULL, NAME VARCHAR(100), DESCRIPTION TEXT DEFAULT null, ATTACHMENT_LIST_ID BIGINT NOT NULL, CREATED_BY VARCHAR(50) NOT NULL, CREATED_ON TIMESTAMP WITH TIME ZONE NOT NULL, LAST_MODIFIED_BY VARCHAR(50) DEFAULT null, LAST_MODIFIED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, CONSTRAINT PK_TEST_SUITE PRIMARY KEY (ID), CONSTRAINT fk_test_suite_attachment_list FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST(ATTACHMENT_LIST_ID));

COMMENT ON COLUMN TEST_SUITE.CREATED_BY IS 'Who created this test suite';

COMMENT ON COLUMN TEST_SUITE.CREATED_ON IS 'When this test suite was created';

COMMENT ON COLUMN TEST_SUITE.LAST_MODIFIED_BY IS 'Who modified this test suite last';

COMMENT ON COLUMN TEST_SUITE.LAST_MODIFIED_ON IS 'When this test suite was last modified';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Add the table TEST_SUITE. A TEST_SUITE is basically a view on a subset of ITERATION_TEST_PLAN_ITEMs.', 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.1.0.xml', 'tm-1.1.0.22', '2.0.5', '3:f21b8cdf96f5a52a837b8a843b3c0dfd', 100);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.23::bsiri::(Checksum: 3:cc2c3ce46094fc05b76f34154fea809c)
-- Association table between ITERATION and TEST_SUITE. Like in many other places here, the purpose of this association table is not to model a n-n cardinality between ITERATION and 
-- 			TEST_SUITE, but 1-n. We thus gain performance at the expense of the model quality.
CREATE TABLE ITERATION_TEST_SUITE (ITERATION_ID BIGINT NOT NULL, TEST_SUITE_ID BIGINT NOT NULL, CONSTRAINT fk_iteration_test_suite_iteration FOREIGN KEY (ITERATION_ID) REFERENCES ITERATION(ITERATION_ID), CONSTRAINT fk_iteration_test_suite_suite FOREIGN KEY (TEST_SUITE_ID) REFERENCES TEST_SUITE(ID), UNIQUE (TEST_SUITE_ID));

CREATE INDEX dx_iteration_test_suite_iteration ON ITERATION_TEST_SUITE(ITERATION_ID);

CREATE INDEX dx_iteration_test_suite_suite ON ITERATION_TEST_SUITE(TEST_SUITE_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Association table between ITERATION and TEST_SUITE. Like in many other places here, the purpose of this association table is not to model a n-n cardinality between ITERATION and 
			TEST_SUITE, but 1-n. We thus gain performance at the expense of the mo...', 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.1.0.xml', 'tm-1.1.0.23', '2.0.5', '3:cc2c3ce46094fc05b76f34154fea809c', 101);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.24::bsiri::(Checksum: 3:7b4ae4519e1c0fa1c18c75f27c75c8a8)
-- Hereby an ITERATION_TEST_PLAN_ITEM shalt reference 0 or 1 TEST_SUITE
ALTER TABLE ITERATION_TEST_PLAN_ITEM ADD TEST_SUITE BIGINT DEFAULT null;

ALTER TABLE ITERATION_TEST_PLAN_ITEM ADD CONSTRAINT fk_itpi_testsuite FOREIGN KEY (TEST_SUITE) REFERENCES TEST_SUITE (ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Hereby an ITERATION_TEST_PLAN_ITEM shalt reference 0 or 1 TEST_SUITE', 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.1.0.xml', 'tm-1.1.0.24', '2.0.5', '3:7b4ae4519e1c0fa1c18c75f27c75c8a8', 102);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.25::fogaillard::(Checksum: 3:507c15d0675f77b3a197c7d7f607f682)
-- Add Prerequisite collumn to the execution table
ALTER TABLE EXECUTION ADD PREREQUISITE TEXT DEFAULT null;

COMMENT ON COLUMN EXECUTION.PREREQUISITE IS 'the prerequisite for a test_case execution';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('fogaillard', 'Add Prerequisite collumn to the execution table', 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.1.0.xml', 'tm-1.1.0.25', '2.0.5', '3:507c15d0675f77b3a197c7d7f607f682', 103);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.32::mpagnon::(Checksum: 3:90da51d034265bddab458bf2cb1340e6)
INSERT INTO ACL_GROUP (QUALIFIED_NAME) VALUES ('squashtest.acl.group.tm.ProjectManager');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '32');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '2');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '4');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '8');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', '', NOW(), 'Insert Row (x6)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml', 'tm-1.2.0.32', '2.0.5', '3:90da51d034265bddab458bf2cb1340e6', 104);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.34-1::mpagnon::(Checksum: 3:7ec2104ebfaac35cfc9daac99d3a9326)
-- add acl-class and acl-groups
INSERT INTO ACL_CLASS (CLASSNAME) VALUES ('org.squashtest.csp.tm.domain.requirement.RequirementLibrary');

INSERT INTO ACL_CLASS (CLASSNAME) VALUES ('org.squashtest.csp.tm.domain.testcase.TestCaseLibrary');

INSERT INTO ACL_CLASS (CLASSNAME) VALUES ('org.squashtest.csp.tm.domain.campaign.CampaignLibrary');

INSERT INTO ACL_GROUP (QUALIFIED_NAME) VALUES ('squashtest.acl.group.tm.TestRunner');

INSERT INTO ACL_GROUP (QUALIFIED_NAME) VALUES ('squashtest.acl.group.tm.TestDesigner');

INSERT INTO ACL_GROUP (QUALIFIED_NAME) VALUES ('squashtest.acl.group.tm.AdvanceTester');

INSERT INTO ACL_GROUP (QUALIFIED_NAME) VALUES ('squashtest.acl.group.tm.Validator');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'add acl-class and acl-groups', NOW(), 'Insert Row (x7)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml', 'tm-1.2.0.34-1', '2.0.5', '3:7ec2104ebfaac35cfc9daac99d3a9326', 105);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.34-2::mpagnon::(Checksum: 3:e89ca0317424a04b6fd94396619224a6)
-- remove existing permissions and redefine them
TRUNCATE TABLE ACL_GROUP_PERMISSION;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'remove existing permissions and redefine them', 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.2.0.xml', 'tm-1.2.0.34-2', '2.0.5', '3:e89ca0317424a04b6fd94396619224a6', 106);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.34-3::mpagnon::(Checksum: 3:225504bd6a738b6d974968be183c87f2)
-- ProjectManager / CampaignLibrary
INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '32');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '2');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '4');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '8');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '64');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '256');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '512');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '1024');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '2');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '4');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '8');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '64');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '256');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '512');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '1024');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '2');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '4');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '8');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '64');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '128');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '256');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '1024');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'ProjectManager / CampaignLibrary', NOW(), 'Insert Row (x26)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml', 'tm-1.2.0.34-3', '2.0.5', '3:225504bd6a738b6d974968be183c87f2', 107);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.34-4::mpagnon::(Checksum: 3:0b1e26bb09cae69721b814e68ab3e85b)
-- TestEditor / CampaignLibrary
INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '2');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '4');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '8');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '64');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '256');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '1024');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '2');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '4');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '8');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '64');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '256');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '1024');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '2');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '4');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '8');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '64');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '128');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '256');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestEditor'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '1024');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'TestEditor / CampaignLibrary', NOW(), 'Insert Row (x23)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml', 'tm-1.2.0.34-4', '2.0.5', '3:0b1e26bb09cae69721b814e68ab3e85b', 108);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.34-5::mpagnon::(Checksum: 3:978f35b6b98e4c777664656ca5842573)
-- ProjectViewer / CampaignLibrary
INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectViewer'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectViewer'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectViewer'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectViewer'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '1');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'ProjectViewer / CampaignLibrary', NOW(), 'Insert Row (x4)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml', 'tm-1.2.0.34-5', '2.0.5', '3:978f35b6b98e4c777664656ca5842573', 109);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.34-6::mpagnon::(Checksum: 3:bee4f7ed123e4e6a36f03f51f9167c98)
-- TestRunner / CampaignLibrary
INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestRunner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestRunner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestRunner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestRunner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '128');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestRunner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '1024');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'TestRunner / CampaignLibrary', NOW(), 'Insert Row (x5)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml', 'tm-1.2.0.34-6', '2.0.5', '3:bee4f7ed123e4e6a36f03f51f9167c98', 110);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.34-7::mpagnon::(Checksum: 3:a80fff3186b159d8f7d260916f70bd9a)
-- TestDesigner / CampaignLibrary
INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestDesigner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestDesigner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestDesigner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '2');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestDesigner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '4');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestDesigner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '8');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestDesigner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '64');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestDesigner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '256');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestDesigner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '1024');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestDesigner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestDesigner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '2');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestDesigner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '4');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestDesigner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '8');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestDesigner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '64');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestDesigner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '256');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestDesigner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '1024');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestDesigner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '1');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'TestDesigner / CampaignLibrary', NOW(), 'Insert Row (x16)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml', 'tm-1.2.0.34-7', '2.0.5', '3:a80fff3186b159d8f7d260916f70bd9a', 111);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.34-8::mpagnon::(Checksum: 3:3c3a9108132480e399c803596ae9c4f2)
-- AdvanceTester / CampaignLibrary
INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '2');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '64');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '1024');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '2');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '4');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '8');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '64');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '128');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '256');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '1024');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'AdvanceTester / CampaignLibrary', NOW(), 'Insert Row (x14)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml', 'tm-1.2.0.34-8', '2.0.5', '3:3c3a9108132480e399c803596ae9c4f2', 112);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.34-9::mpagnon::(Checksum: 3:e3fcbf536483063022c4843f78d506c1)
-- Validator / CampaignLibrary
INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.Validator'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.Validator'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.Validator'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'), '64');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.Validator'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '1');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.Validator'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'), '64');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.Validator'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'), '1');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Validator / CampaignLibrary', NOW(), 'Insert Row (x6)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml', 'tm-1.2.0.34-9', '2.0.5', '3:e3fcbf536483063022c4843f78d506c1', 113);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.34-10::mpagnon::(Checksum: 3:bef756c0b744c1e235e17add47210541)
-- Existing Data Update: Delete acls for deleted projects in 1.1.1
DELETE FROM ACL_RESPONSIBILITY_SCOPE_ENTRY
			 WHERE OBJECT_IDENTITY_ID not in (
				select aoi.ID from  ACL_OBJECT_IDENTITY aoi, ACL_CLASS ac , PROJECT p
				where ac.CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project' 
				and ac.ID = aoi.CLASS_ID
				and aoi.IDENTITY = p.PROJECT_ID
			);

DELETE FROM ACL_OBJECT_IDENTITY
			WHERE IDENTITY not in (
				select  p.PROJECT_ID from PROJECT p
			)
			and CLASS_ID in (
				select ac.ID from ACL_CLASS ac where ac.CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project' 
			);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Existing Data Update: Delete acls for deleted projects in 1.1.1', 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.2.0.xml', 'tm-1.2.0.34-10', '2.0.5', '3:bef756c0b744c1e235e17add47210541', 114);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.34-11::mpagnon::(Checksum: 3:7e07f9d4d5ad2ab698697ce5fb344295)
-- Existing Data Update for ACL_OBJECT_IDENTITY / REQUIREMENT_LIBRARY
insert into ACL_OBJECT_IDENTITY (IDENTITY, CLASS_ID)
			select p.RL_ID, ac2.ID
			from  ACL_CLASS ac1, ACL_CLASS ac2, PROJECT p, ACL_OBJECT_IDENTITY aoi
			where ac2.CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary'
			and p.PROJECT_ID = aoi.IDENTITY
			and aoi.CLASS_ID = ac1.ID 
			and ac1.CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Existing Data Update for ACL_OBJECT_IDENTITY / REQUIREMENT_LIBRARY', 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.2.0.xml', 'tm-1.2.0.34-11', '2.0.5', '3:7e07f9d4d5ad2ab698697ce5fb344295', 115);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.34-12::mpagnon::(Checksum: 3:e341c11b9ecf86ac6c7680940391a38b)
-- Existing Data Update for ACL_OBJECT_IDENTITY / TEST_CASE_LIBRARY
insert into ACL_OBJECT_IDENTITY (IDENTITY, CLASS_ID)
			select p.TCL_ID, ac2.ID
			from  ACL_CLASS ac1, ACL_CLASS ac2, PROJECT p, ACL_OBJECT_IDENTITY aoi
			where ac2.CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary'
			and p.PROJECT_ID = aoi.IDENTITY
			and aoi.CLASS_ID = ac1.ID 
			and ac1.CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Existing Data Update for ACL_OBJECT_IDENTITY / TEST_CASE_LIBRARY', 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.2.0.xml', 'tm-1.2.0.34-12', '2.0.5', '3:e341c11b9ecf86ac6c7680940391a38b', 116);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.34-13::mpagnon::(Checksum: 3:33f76b9ba0f7f5f328b3c31f01b02a65)
-- Existing Data Update for ACL_OBJECT_IDENTITY / CAMPAIGN_LIBRARY
insert into ACL_OBJECT_IDENTITY (IDENTITY, CLASS_ID)
			select p.CL_ID, ac2.ID
			from  ACL_CLASS ac1, ACL_CLASS ac2, PROJECT p, ACL_OBJECT_IDENTITY aoi
			where ac2.CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary'
			and p.PROJECT_ID = aoi.IDENTITY
			and aoi.CLASS_ID = ac1.ID 
			and ac1.CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Existing Data Update for ACL_OBJECT_IDENTITY / CAMPAIGN_LIBRARY', 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.2.0.xml', 'tm-1.2.0.34-13', '2.0.5', '3:33f76b9ba0f7f5f328b3c31f01b02a65', 117);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.34-14::mpagnon::(Checksum: 3:6497fdb66ae815d8badaffaf328f8bce)
-- Existing Data Update for ACL_RESPONSIBILITY_SCOPE_ENTRY / REQUIREMENT_LIBRARY
insert into ACL_RESPONSIBILITY_SCOPE_ENTRY (USER_ID, ACL_GROUP_ID, OBJECT_IDENTITY_ID)
			select arse.USER_ID, arse.ACL_GROUP_ID, aoiRequirement.ID
			from ACL_RESPONSIBILITY_SCOPE_ENTRY arse, ACL_OBJECT_IDENTITY aoiProject, ACL_CLASS aclassProject, ACL_OBJECT_IDENTITY aoiRequirement, ACL_CLASS aclassRequirement, PROJECT p
			where arse.OBJECT_IDENTITY_ID = aoiProject.ID
			and aoiProject.CLASS_ID = aclassProject.ID
			and aclassProject.CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'
			and p.PROJECT_ID = aoiProject.IDENTITY
			and aoiRequirement.IDENTITY = p.RL_ID
			and aoiRequirement.CLASS_ID = aclassRequirement.ID
			and aclassRequirement.CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Existing Data Update for ACL_RESPONSIBILITY_SCOPE_ENTRY / REQUIREMENT_LIBRARY', 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.2.0.xml', 'tm-1.2.0.34-14', '2.0.5', '3:6497fdb66ae815d8badaffaf328f8bce', 118);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.34-15::mpagnon::(Checksum: 3:a5201760a6e03f033820401b3f488a7d)
-- Existing Data Update for ACL_RESPONSIBILITY_SCOPE_ENTRY / TEST_CASE_LIBRARY
insert into ACL_RESPONSIBILITY_SCOPE_ENTRY (USER_ID, ACL_GROUP_ID, OBJECT_IDENTITY_ID)
			select arse.USER_ID, arse.ACL_GROUP_ID, aoiTestCase.ID
			from ACL_RESPONSIBILITY_SCOPE_ENTRY arse, ACL_OBJECT_IDENTITY aoiProject, ACL_CLASS aclassProject, ACL_OBJECT_IDENTITY aoiTestCase, ACL_CLASS aclassTestCase, PROJECT p
			where arse.OBJECT_IDENTITY_ID = aoiProject.ID
			and aoiProject.CLASS_ID = aclassProject.ID
			and aclassProject.CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'
			and p.PROJECT_ID = aoiProject.IDENTITY
			and aoiTestCase.IDENTITY = p.TCL_ID
			and aoiTestCase.CLASS_ID = aclassTestCase.ID
			and aclassTestCase.CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Existing Data Update for ACL_RESPONSIBILITY_SCOPE_ENTRY / TEST_CASE_LIBRARY', 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.2.0.xml', 'tm-1.2.0.34-15', '2.0.5', '3:a5201760a6e03f033820401b3f488a7d', 119);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.34-16::mpagnon::(Checksum: 3:cba831d75d165c2cfa3feb789de74ef9)
-- Existing Data Update for ACL_RESPONSIBILITY_SCOPE_ENTRY / CAMPAIGN_LIBRARY
insert into ACL_RESPONSIBILITY_SCOPE_ENTRY (USER_ID, ACL_GROUP_ID, OBJECT_IDENTITY_ID)
			select arse.USER_ID, arse.ACL_GROUP_ID, aoiCampaign.ID
			from ACL_RESPONSIBILITY_SCOPE_ENTRY arse, ACL_OBJECT_IDENTITY aoiProject, ACL_CLASS aclassProject, ACL_OBJECT_IDENTITY aoiCampaign, ACL_CLASS aclassCampaign, PROJECT p
			where arse.OBJECT_IDENTITY_ID = aoiProject.ID
			and aoiProject.CLASS_ID = aclassProject.ID
			and aclassProject.CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project'
			and p.PROJECT_ID = aoiProject.IDENTITY
			and aoiCampaign.IDENTITY = p.CL_ID
			and aoiCampaign.CLASS_ID = aclassCampaign.ID
			and aclassCampaign.CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Existing Data Update for ACL_RESPONSIBILITY_SCOPE_ENTRY / CAMPAIGN_LIBRARY', 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.2.0.xml', 'tm-1.2.0.34-16', '2.0.5', '3:cba831d75d165c2cfa3feb789de74ef9', 120);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.35-01::bsiri::(Checksum: 3:82a94192242d975538bbc05704aec87d)
-- table ISSUE nows holds the bugtracker name where the remote issues are hosted
ALTER TABLE ISSUE ADD BT_NAME VARCHAR(60) NOT NULL;

UPDATE ISSUE SET BT_NAME = 'default';

CREATE INDEX idx_bt_name ON ISSUE(BT_NAME);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'table ISSUE nows holds the bugtracker name where the remote issues are hosted', NOW(), 'Add Column, Create Index', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml', 'tm-1.2.0.35-01', '2.0.5', '3:82a94192242d975538bbc05704aec87d', 121);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.36-01::mpagnon::(Checksum: 3:1b5fcef232cf2faab752bd448469dc0c)
-- table RequirementVersion nows holds a Category attribute
ALTER TABLE REQUIREMENT_VERSION ADD CATEGORY VARCHAR(50) NOT NULL DEFAULT 'UNDEFINED';

COMMENT ON COLUMN REQUIREMENT_VERSION.CATEGORY IS 'Requirement category. Althought this field is a variable character, the data will be the text representation of an enumeration';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'table RequirementVersion nows holds a Category attribute', 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.2.0.xml', 'tm-1.2.0.36-01', '2.0.5', '3:1b5fcef232cf2faab752bd448469dc0c', 122);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.2.0.xml::tm-1.2.0.36-02::mpagnon::(Checksum: 3:3360838ead20282ef502404a7fefc830)
-- table Test-Case nows holds a Reference attribute
ALTER TABLE TEST_CASE ADD REFERENCE VARCHAR(20) NOT NULL DEFAULT '';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'table Test-Case nows holds a Reference attribute', 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.2.0.xml', 'tm-1.2.0.36-02', '2.0.5', '3:3360838ead20282ef502404a7fefc830', 123);

-- 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));

COMMENT ON COLUMN TCLN_RELATIONSHIP_CLOSURE.ANCESTOR_ID IS 'cle etrangere vers le noeud parent. La relation est parfois d''un noeud vers lui meme.';

COMMENT ON COLUMN TCLN_RELATIONSHIP_CLOSURE.DESCENDANT_ID IS 'cle etrangere vers l''element fils. La relation est parfois d''un noeud vers lui meme.';

COMMENT ON COLUMN TCLN_RELATIONSHIP_CLOSURE.DEPTH IS 'stores how deep is the descendant element nested in the subtree of the ancestor element';

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));

COMMENT ON COLUMN RLN_RELATIONSHIP_CLOSURE.ANCESTOR_ID IS 'cle etrangere vers le noeud parent. La relation est parfois d''un noeud vers lui meme.';

COMMENT ON COLUMN RLN_RELATIONSHIP_CLOSURE.DESCENDANT_ID IS 'cle etrangere vers l''element fils. La relation est parfois d''un noeud vers lui meme.';

COMMENT ON COLUMN RLN_RELATIONSHIP_CLOSURE.DEPTH IS 'stores how deep is the descendant element nested in the subtree of the ancestor element';

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_anc FOREIGN KEY (ANCESTOR_ID) REFERENCES CAMPAIGN_LIBRARY_NODE(CLN_ID), CONSTRAINT fk_cln_relationship_clos_desc FOREIGN KEY (DESCENDANT_ID) REFERENCES CAMPAIGN_LIBRARY_NODE(CLN_ID));

COMMENT ON COLUMN CLN_RELATIONSHIP_CLOSURE.ANCESTOR_ID IS 'cle etrangere vers le noeud parent. La relation est parfois d''un noeud vers lui meme.';

COMMENT ON COLUMN CLN_RELATIONSHIP_CLOSURE.DESCENDANT_ID IS 'cle etrangere vers l''element fils. La relation est parfois d''un noeud vers lui meme.';

COMMENT ON COLUMN CLN_RELATIONSHIP_CLOSURE.DEPTH IS 'stores how deep is the descendant element nested in the subtree of the ancestor element';

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-postgresql::mpagnon::(Checksum: 3:d7cfa1ff5ebb8a2ce685d566dbcf7bb8)
-- postgresql-triggers for TCLN_RELATIONSHIP_CLOSURE table
drop trigger if exists ins_tcln_after on TEST_CASE_LIBRARY_NODE;
      drop function if exists ins_tcln_after();
      create function ins_tcln_after() returns trigger as $ins_tcln_after$
      begin
        insert into TCLN_RELATIONSHIP_CLOSURE
        values (new.tcln_id, new.tcln_id, 0); 
        return new;
      end;
      $ins_tcln_after$ language plpgsql;
      
      create trigger ins_tcln_after after insert on TEST_CASE_LIBRARY_NODE
      for each row execute procedure ins_tcln_after();

drop trigger if exists del_tcln_before on TEST_CASE_LIBRARY_NODE;
      drop function if exists del_tcln_before();
      create function del_tcln_before() returns trigger as $del_tcln_before$
      begin
        delete from TCLN_RELATIONSHIP_CLOSURE 
        where ancestor_id=old.tcln_id 
        and descendant_id=old.tcln_id;  
        return old;
      end;
      $del_tcln_before$ language plpgsql;
      
      create trigger del_tcln_before before delete on TEST_CASE_LIBRARY_NODE
      for each row execute procedure del_tcln_before();

drop trigger if exists attach_tcln_after on TCLN_RELATIONSHIP;
      drop function if exists attach_tcln_after();
      create function attach_tcln_after() returns trigger as $attach_tcln_after$
      begin
        insert into TCLN_RELATIONSHIP_CLOSURE
        select c1.ancestor_id, c2.descendant_id, c1.depth + c2.depth + 1
        from TCLN_RELATIONSHIP_CLOSURE c1
        cross join TCLN_RELATIONSHIP_CLOSURE c2
        where c1.descendant_id = new.ancestor_id
        and c2.ancestor_id = new.descendant_id;   
        return new;
      end;
      $attach_tcln_after$ language plpgsql;
      
      create trigger attach_tcln_after after insert on TCLN_RELATIONSHIP
      for each row execute procedure attach_tcln_after();

drop trigger if exists detach_tcln_before on TCLN_RELATIONSHIP;
      drop function if exists detach_tcln_before();
      create function detach_tcln_before() returns trigger as $detach_tcln_before$
      begin
        delete from TCLN_RELATIONSHIP_CLOSURE 
        where descendant_id in ( select descendant_id from TCLN_RELATIONSHIP_CLOSURE where ancestor_id = old.descendant_id )
        and ancestor_id not in ( select descendant_id from TCLN_RELATIONSHIP_CLOSURE where ancestor_id = old.descendant_id )
        ;
        return old;
      
      end;
      $detach_tcln_before$ language plpgsql;
      
      create trigger detach_tcln_before before delete on TCLN_RELATIONSHIP
      for each row execute procedure detach_tcln_before();

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'postgresql-triggers for TCLN_RELATIONSHIP_CLOSURE table', NOW(), 'Create Procedure (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-postgresql', '2.0.5', '3:d7cfa1ff5ebb8a2ce685d566dbcf7bb8', 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-postgresql::mpagnon::(Checksum: 3:da5c2a4af847f6c0dd3abd5c9db52da5)
-- postgresql-triggers for RLN_RELATIONSHIP_CLOSURE table
drop trigger if exists ins_rln_after on REQUIREMENT_LIBRARY_NODE;
      drop function if exists ins_rln_after();
      create function ins_rln_after() returns trigger as $ins_rln_after$
      begin
        insert into RLN_RELATIONSHIP_CLOSURE 
        values (new.rln_id, new.rln_id, 0);
        return new;
      end;
      $ins_rln_after$ language plpgsql;
      
      create trigger ins_rln_after after insert on REQUIREMENT_LIBRARY_NODE
      for each row execute procedure ins_rln_after();

drop trigger if exists del_rln_before on REQUIREMENT_LIBRARY_NODE;
      drop function if exists del_rln_before();
      create function del_rln_before() returns trigger as $del_rln_before$
      begin
        delete from RLN_RELATIONSHIP_CLOSURE 
        where ancestor_id = old.rln_id 
        and descendant_id = old.rln_id; 
        return old;
      end;
      $del_rln_before$ language plpgsql;
      
      create trigger del_rln_before before delete on REQUIREMENT_LIBRARY_NODE
      for each row execute procedure del_rln_before();

drop trigger if exists attach_rln_after on RLN_RELATIONSHIP;
      drop function if exists attach_rln_after();
      create function attach_rln_after() returns trigger as $attach_rln_after$
      begin
        insert into RLN_RELATIONSHIP_CLOSURE
        select c1.ancestor_id, c2.descendant_id, c1.depth + c2.depth + 1
        from RLN_RELATIONSHIP_CLOSURE c1
        cross join RLN_RELATIONSHIP_CLOSURE c2
        where c1.descendant_id = new.ancestor_id
        and c2.ancestor_id = new.descendant_id;
        return new;
      end;
      $attach_rln_after$ language plpgsql;
      
      create trigger attach_rln_after after insert on RLN_RELATIONSHIP
      for each row execute procedure attach_rln_after();

drop trigger if exists detach_rln_before on RLN_RELATIONSHIP;
      drop function if exists detach_rln_before();
      create function detach_rln_before() returns trigger as $detach_rln_before$
      begin
      delete from RLN_RELATIONSHIP_CLOSURE 
        where descendant_id in ( select descendant_id from RLN_RELATIONSHIP_CLOSURE where ancestor_id = old.descendant_id )
        and ancestor_id not in ( select descendant_id from RLN_RELATIONSHIP_CLOSURE where ancestor_id = old.descendant_id )
        ;
        return old;
      end;
      $detach_rln_before$ language plpgsql;
      
      create trigger detach_rln_before before delete on RLN_RELATIONSHIP
      for each row execute procedure detach_rln_before();

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'postgresql-triggers for RLN_RELATIONSHIP_CLOSURE table', NOW(), 'Create Procedure (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-postgresql', '2.0.5', '3:da5c2a4af847f6c0dd3abd5c9db52da5', 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-postgresql::mpagnon::(Checksum: 3:12027eeb905361558c5c7d9532191d3b)
-- postgresql-triggers for CLN_RELATIONSHIP_CLOSURE table
drop trigger if exists ins_cln_after on CAMPAIGN_LIBRARY_NODE;
      drop function if exists ins_cln_after();
      create function ins_cln_after() returns trigger as $ins_cln_after$
      begin
        insert into CLN_RELATIONSHIP_CLOSURE
        values (new.cln_id, new.cln_id, 0);
        return new;
      end;
      $ins_cln_after$ language plpgsql;
      
      create trigger ins_cln_after after insert on CAMPAIGN_LIBRARY_NODE
      for each row execute procedure ins_cln_after();

drop trigger if exists del_cln_before on CAMPAIGN_LIBRARY_NODE;
      drop function if exists del_cln_before();
      create function del_cln_before() returns trigger as $del_cln_before$
      begin
        delete from CLN_RELATIONSHIP_CLOSURE 
        where ancestor_id = old.cln_id 
        and descendant_id = old.cln_id;
        return old;
      end;
      $del_cln_before$ language plpgsql;
      
      create trigger del_cln_before before delete on CAMPAIGN_LIBRARY_NODE
      for each row execute procedure del_cln_before();

drop trigger if exists attach_cln_after on CLN_RELATIONSHIP;
       drop function if exists attach_cln_after();
      create function attach_cln_after() returns trigger as $attach_cln_after$
      begin
        insert into CLN_RELATIONSHIP_CLOSURE
        select c1.ancestor_id, c2.descendant_id, c1.depth + c2.depth + 1
        from CLN_RELATIONSHIP_CLOSURE c1
        cross join CLN_RELATIONSHIP_CLOSURE c2
        where c1.descendant_id = new.ancestor_id
        and c2.ancestor_id = new.descendant_id;
        return new;
      end;
      $attach_cln_after$ language plpgsql;
      
      create trigger attach_cln_after after insert on CLN_RELATIONSHIP
      for each row execute procedure attach_cln_after();

drop trigger if exists detach_cln_before on CLN_RELATIONSHIP;
      drop function if exists detach_cln_before();
      create function detach_cln_before() returns trigger as $detach_cln_before$
      begin
        delete from CLN_RELATIONSHIP_CLOSURE 
        where descendant_id in ( select descendant_id from CLN_RELATIONSHIP_CLOSURE where ancestor_id = old.descendant_id )
        and ancestor_id not in ( select descendant_id from CLN_RELATIONSHIP_CLOSURE where ancestor_id = old.descendant_id )
        ;
        return old;
      end;
      $detach_cln_before$ language plpgsql;
      
      create trigger detach_cln_before before delete on CLN_RELATIONSHIP
      for each row execute procedure detach_cln_before();

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'postgresql-triggers for CLN_RELATIONSHIP_CLOSURE table', NOW(), 'Create Procedure (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-postgresql', '2.0.5', '3:12027eeb905361558c5c7d9532191d3b', 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 bigserial 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));

COMMENT ON TABLE BUGTRACKER IS 'stores bugtrackers informations';

COMMENT ON COLUMN BUGTRACKER.BUGTRACKER_ID IS 'primary key, should be auto-generated.';

COMMENT ON COLUMN BUGTRACKER.NAME IS 'identifies the instance configured.';

COMMENT ON COLUMN BUGTRACKER.KIND IS 'tells what kind of connector should be used';

COMMENT ON COLUMN BUGTRACKER.URL IS 'the bugtracker url';

COMMENT ON COLUMN BUGTRACKER.IFRAME_FRIENDLY IS 'tells if the bugtracker page can be accessed through an iframe';

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 bigserial 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));

COMMENT ON COLUMN TEST_AUTOMATION_SERVER.BASE_URL IS 'the url of the server';

COMMENT ON COLUMN TEST_AUTOMATION_SERVER.LOGIN IS 'the login TM will use';

COMMENT ON COLUMN TEST_AUTOMATION_SERVER.PASSWORD IS 'yes, this is a plain clear password in the database. Until we have what it takes to make it better.';

COMMENT ON COLUMN TEST_AUTOMATION_SERVER.KIND IS 'the kind of automated test server TM will deal with.';

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 bigserial 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));

COMMENT ON COLUMN TEST_AUTOMATION_PROJECT.SERVER_ID IS 'foreign key to an automated test server';

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 NOT NULL DEFAULT FALSE;

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 bigserial 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_BUGTRACKER FOREIGN KEY (BUGTRACKER_ID) REFERENCES BUGTRACKER(BUGTRACKER_ID), CONSTRAINT FK_BUGTRACKER_BINDING_PROJECT FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID));

COMMENT ON COLUMN BUGTRACKER_BINDING.PROJECT_NAME IS 'project-name in the bugtracker';

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);

COMMENT ON COLUMN ISSUE.BUGTRACKER_ID IS 'Issue''s bugtracker';

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 SET 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 bigserial 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 bigserial NOT NULL, MASTER_EXECUTION_ID BIGINT NOT NULL, TEST_ID BIGINT NOT NULL, RESULT_URL VARCHAR(300), SUITE_ID VARCHAR(50), RESULT_SUMMARY TEXT 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_auto_test FOREIGN KEY (TEST_ID) REFERENCES AUTOMATED_TEST(TEST_ID), CONSTRAINT fk_auto_exec_exec FOREIGN KEY (MASTER_EXECUTION_ID) REFERENCES EXECUTION(EXECUTION_ID));

COMMENT ON COLUMN AUTOMATED_EXECUTION_EXTENDER.MASTER_EXECUTION_ID IS 'foreign key to the Execution it attaches to.';

COMMENT ON COLUMN AUTOMATED_EXECUTION_EXTENDER.TEST_ID IS 'we save that information as soon as the auto execution is created,              because it may change in the future (even if the test case that this execution             is linked to stays otherwhise the same)';

COMMENT ON COLUMN AUTOMATED_EXECUTION_EXTENDER.RESULT_URL IS 'urls longer than 300 chars should go to hell';

COMMENT ON COLUMN AUTOMATED_EXECUTION_EXTENDER.SUITE_ID IS 'foreignkey of the suite this execution belongs to';

COMMENT ON COLUMN AUTOMATED_EXECUTION_EXTENDER.RESULT_SUMMARY IS 'a short debriefing of a test once it has been        executed, for instance an error message';

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);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml::tm-1.4.0.00::gfouquet::(Checksum: 3:4ef18a63bbd3169cef35179fb8c91028)
-- Adds a TM database version number
UPDATE CORE_CONFIG SET STR_KEY = 'squashtest.tm.database.version', VALUE = '1.4.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.4.0.xml', 'tm-1.4.0.00', '2.0.5', '3:4ef18a63bbd3169cef35179fb8c91028', 153);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml::tm-1.4.0-feat-1109.01::gfouquet::(Checksum: 3:8f83c1af5962af42e68d6401ef310c9f)
-- Creates a table for Custom Fields
CREATE TABLE CUSTOM_FIELD (CF_ID bigserial NOT NULL, FIELD_TYPE CHAR(3) NOT NULL, NAME VARCHAR(255) NOT NULL, LABEL VARCHAR(255) DEFAULT '' NOT NULL, OPTIONAL BOOLEAN DEFAULT TRUE NOT NULL, DEFAULT_VALUE VARCHAR(255), INPUT_TYPE CHAR(30) NOT NULL, CONSTRAINT pk_custom_field PRIMARY KEY (CF_ID));

COMMENT ON COLUMN CUSTOM_FIELD.FIELD_TYPE IS 'CF : simple, SSF : multiple-choice';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Creates a table for Custom Fields', 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.4.0.xml', 'tm-1.4.0-feat-1109.01', '2.0.5', '3:8f83c1af5962af42e68d6401ef310c9f', 154);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml::tm-1.4.0-feat-1109.02::gfouquet::(Checksum: 3:ec08ab8b8e2359f529da4a311a6c84ab)
-- Creates a table for Custom Fields Options
CREATE TABLE CUSTOM_FIELD_OPTION (CF_ID BIGINT NOT NULL, LABEL VARCHAR(255) NOT NULL, POSITION INT NOT NULL, CONSTRAINT fk_cf_option_cf FOREIGN KEY (CF_ID) REFERENCES CUSTOM_FIELD(CF_ID));

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Creates a table for Custom Fields Options', 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.4.0.xml', 'tm-1.4.0-feat-1109.02', '2.0.5', '3:ec08ab8b8e2359f529da4a311a6c84ab', 155);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml::tm-1.4.0-feat-1390.01::gfouquet::(Checksum: 3:cadfa5b3c7e141db73867b4bc8e98a18)
-- Creates a table for Custom Fields Binding
CREATE TABLE CUSTOM_FIELD_BINDING (CFB_ID bigserial NOT NULL, CF_ID BIGINT NOT NULL, BOUND_ENTITY VARCHAR(30) NOT NULL, BOUND_PROJECT_ID BIGINT NOT NULL, POSITION INT, CONSTRAINT pk_cfb_option PRIMARY KEY (CFB_ID), CONSTRAINT fk_cfb_cf FOREIGN KEY (CF_ID) REFERENCES CUSTOM_FIELD(CF_ID), CONSTRAINT fk_cfb_bound_project FOREIGN KEY (BOUND_PROJECT_ID) REFERENCES PROJECT(PROJECT_ID));

ALTER TABLE CUSTOM_FIELD_BINDING ADD CONSTRAINT uc_cf_entity_proj_bnd UNIQUE (BOUND_PROJECT_ID, BOUND_ENTITY, CF_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Creates a table for Custom Fields Binding', 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.4.0.xml', 'tm-1.4.0-feat-1390.01', '2.0.5', '3:cadfa5b3c7e141db73867b4bc8e98a18', 156);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml::tm-1.4.0-feat-1390.02::gfouquet::(Checksum: 3:8fc5d161321c668c715d5e5a5be4bf90)
-- Creates a table for rendering locations of a bound custom field
CREATE TABLE CUSTOM_FIELD_RENDERING_LOCATION (CFB_ID BIGINT NOT NULL, RENDERING_LOCATION VARCHAR(30) NOT NULL, CONSTRAINT fk_cfb_render_loc_cfb FOREIGN KEY (CFB_ID) REFERENCES CUSTOM_FIELD_BINDING(CFB_ID));

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Creates a table for rendering locations of a bound custom field', 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.4.0.xml', 'tm-1.4.0-feat-1390.02', '2.0.5', '3:8fc5d161321c668c715d5e5a5be4bf90', 157);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml::tm-1.4.0.feat-1513.01::mpagnon::(Checksum: 3:2b44c9ff7d0cf014f99833e33912a423)
-- Add property attachment list in library nodes, projects and libraries
ALTER TABLE TEST_CASE_LIBRARY_NODE ADD ATTACHMENT_LIST_ID BIGINT;

ALTER TABLE TEST_CASE_LIBRARY_NODE ADD CONSTRAINT "fk_tcNode_attachment_list" FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST (ATTACHMENT_LIST_ID);

COMMENT ON COLUMN TEST_CASE_LIBRARY_NODE.ATTACHMENT_LIST_ID IS 'clé étrangère vers un ATTACHMENT_LIST, l''entité qui gère les pièces jointes.';

ALTER TABLE CAMPAIGN_LIBRARY_NODE ADD ATTACHMENT_LIST_ID BIGINT;

ALTER TABLE CAMPAIGN_LIBRARY_NODE ADD CONSTRAINT "fk_campNode_attachment_list" FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST (ATTACHMENT_LIST_ID);

COMMENT ON COLUMN CAMPAIGN_LIBRARY_NODE.ATTACHMENT_LIST_ID IS 'clé étrangère vers un ATTACHMENT_LIST, l''entité qui gère les pièces jointes.';

ALTER TABLE RESOURCE ADD ATTACHMENT_LIST_ID BIGINT;

ALTER TABLE RESOURCE ADD CONSTRAINT fk_resource_attachment_list FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST (ATTACHMENT_LIST_ID);

COMMENT ON COLUMN RESOURCE.ATTACHMENT_LIST_ID IS 'clé étrangère vers un ATTACHMENT_LIST, l''entité qui gère les pièces jointes.';

ALTER TABLE PROJECT ADD ATTACHMENT_LIST_ID BIGINT;

ALTER TABLE PROJECT ADD CONSTRAINT fk_project_attachment_list FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST (ATTACHMENT_LIST_ID);

COMMENT ON COLUMN PROJECT.ATTACHMENT_LIST_ID IS 'clé étrangère vers un ATTACHMENT_LIST, l''entité qui gère les pièces jointes.';

ALTER TABLE REQUIREMENT_LIBRARY ADD ATTACHMENT_LIST_ID BIGINT;

ALTER TABLE REQUIREMENT_LIBRARY ADD CONSTRAINT "fk_reqLib_attachment_list" FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST (ATTACHMENT_LIST_ID);

COMMENT ON COLUMN REQUIREMENT_LIBRARY.ATTACHMENT_LIST_ID IS 'clé étrangère vers un ATTACHMENT_LIST, l''entité qui gère les pièces jointes.';

ALTER TABLE TEST_CASE_LIBRARY ADD ATTACHMENT_LIST_ID BIGINT;

ALTER TABLE TEST_CASE_LIBRARY ADD CONSTRAINT "fk_tcLib_attachment_list" FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST (ATTACHMENT_LIST_ID);

COMMENT ON COLUMN TEST_CASE_LIBRARY.ATTACHMENT_LIST_ID IS 'clé étrangère vers un ATTACHMENT_LIST, l''entité qui gère les pièces jointes.';

ALTER TABLE CAMPAIGN_LIBRARY ADD ATTACHMENT_LIST_ID BIGINT;

ALTER TABLE CAMPAIGN_LIBRARY ADD CONSTRAINT "fk_campLib_attachment_list" FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST (ATTACHMENT_LIST_ID);

COMMENT ON COLUMN CAMPAIGN_LIBRARY.ATTACHMENT_LIST_ID IS 'clé étrangère vers un ATTACHMENT_LIST, l''entité qui gère les pièces jointes.';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Add property attachment list in library nodes, projects and libraries', NOW(), 'Add Column (x7)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml', 'tm-1.4.0.feat-1513.01', '2.0.5', '3:2b44c9ff7d0cf014f99833e33912a423', 158);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml::tm-1.4.0.feat-1513.02::mpagnon::(Checksum: 3:1699dc3bd6443b42d5b84be37244fcdf)
-- Migrate existing attachment lists
update TEST_CASE_LIBRARY_NODE
			set ATTACHMENT_LIST_ID = (select TEST_CASE.ATTACHMENT_LIST_ID from TEST_CASE where TEST_CASE.TCLN_ID = TEST_CASE_LIBRARY_NODE.TCLN_ID);

update CAMPAIGN_LIBRARY_NODE
			set ATTACHMENT_LIST_ID = (select CAMPAIGN.ATTACHMENT_LIST_ID from CAMPAIGN where CAMPAIGN.CLN_ID = CAMPAIGN_LIBRARY_NODE.CLN_ID);

update RESOURCE
			set ATTACHMENT_LIST_ID = (select REQUIREMENT_VERSION.ATTACHMENT_LIST_ID from REQUIREMENT_VERSION where REQUIREMENT_VERSION.RES_ID = RESOURCE.RES_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Migrate existing attachment lists', 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.4.0.xml', 'tm-1.4.0.feat-1513.02', '2.0.5', '3:1699dc3bd6443b42d5b84be37244fcdf', 159);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml::tm-1.4.0.feat-1513.03::mpagnon::(Checksum: 3:9489cb2edd3dd5c64179ed2221a3e581)
-- Add attachment lists to test_case_folders
ALTER TABLE ATTACHMENT_LIST ADD TCLN_ID BIGINT;

ALTER TABLE ATTACHMENT_LIST ADD CONSTRAINT fk_tcln FOREIGN KEY (TCLN_ID) REFERENCES TEST_CASE_LIBRARY_NODE (TCLN_ID);

COMMENT ON COLUMN ATTACHMENT_LIST.TCLN_ID IS 'colonne provisoire pour se rapeller de à qui était destinée la nouvelle attachment_list';

insert into ATTACHMENT_LIST (TCLN_ID)
			select TEST_CASE_FOLDER.TCLN_ID from TEST_CASE_FOLDER;

update TEST_CASE_LIBRARY_NODE 
			set ATTACHMENT_LIST_ID = (select ATTACHMENT_LIST.ATTACHMENT_LIST_ID from ATTACHMENT_LIST where ATTACHMENT_LIST.TCLN_ID = TEST_CASE_LIBRARY_NODE.TCLN_ID)
			where ATTACHMENT_LIST_ID is null;

ALTER TABLE ATTACHMENT_LIST DROP CONSTRAINT fk_tcln;

ALTER TABLE ATTACHMENT_LIST DROP COLUMN TCLN_ID;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Add attachment lists to test_case_folders', NOW(), 'Add Column, Custom SQL (x2), Drop Foreign Key Constraint, Drop Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml', 'tm-1.4.0.feat-1513.03', '2.0.5', '3:9489cb2edd3dd5c64179ed2221a3e581', 160);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml::tm-1.4.0.feat-1513.04::mpagnon::(Checksum: 3:9067e9a177ebc3d48e3ba6ee428dfa95)
-- Add attachment lists to campaign_folders
ALTER TABLE ATTACHMENT_LIST ADD CLN_ID BIGINT;

ALTER TABLE ATTACHMENT_LIST ADD CONSTRAINT fk_cln FOREIGN KEY (CLN_ID) REFERENCES CAMPAIGN_LIBRARY_NODE (CLN_ID);

COMMENT ON COLUMN ATTACHMENT_LIST.CLN_ID IS 'table provisoire pour se rapeller de à qui était destinée la nouvelle attachment_list';

insert into ATTACHMENT_LIST (CLN_ID)
			select CAMPAIGN_FOLDER.CLN_ID from CAMPAIGN_FOLDER;

update CAMPAIGN_LIBRARY_NODE 
			set ATTACHMENT_LIST_ID = (select ATTACHMENT_LIST.ATTACHMENT_LIST_ID from ATTACHMENT_LIST where ATTACHMENT_LIST.CLN_ID = CAMPAIGN_LIBRARY_NODE.CLN_ID)
			where ATTACHMENT_LIST_ID is null;

ALTER TABLE ATTACHMENT_LIST DROP CONSTRAINT fk_cln;

ALTER TABLE ATTACHMENT_LIST DROP COLUMN CLN_ID;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Add attachment lists to campaign_folders', NOW(), 'Add Column, Custom SQL (x2), Drop Foreign Key Constraint, Drop Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml', 'tm-1.4.0.feat-1513.04', '2.0.5', '3:9067e9a177ebc3d48e3ba6ee428dfa95', 161);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml::tm-1.4.0.feat-1513.05::mpagnon::(Checksum: 3:132d9664007ea9ef0be3785013bba662)
-- Add attachment lists to requirement folders
ALTER TABLE ATTACHMENT_LIST ADD RES_ID BIGINT;

ALTER TABLE ATTACHMENT_LIST ADD CONSTRAINT fk_sr FOREIGN KEY (RES_ID) REFERENCES RESOURCE (RES_ID);

COMMENT ON COLUMN ATTACHMENT_LIST.RES_ID IS 'table provisoire pour se rapeller de à qui était destinée la nouvelle attachment_list';

insert into ATTACHMENT_LIST (RES_ID)
			select SIMPLE_RESOURCE.RES_ID from SIMPLE_RESOURCE;

update RESOURCE 
			set ATTACHMENT_LIST_ID = (select ATTACHMENT_LIST.ATTACHMENT_LIST_ID from ATTACHMENT_LIST where ATTACHMENT_LIST.RES_ID = RESOURCE.RES_ID)
			where ATTACHMENT_LIST_ID is null;

ALTER TABLE ATTACHMENT_LIST DROP CONSTRAINT fk_sr;

ALTER TABLE ATTACHMENT_LIST DROP COLUMN RES_ID;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Add attachment lists to requirement folders', NOW(), 'Add Column, Custom SQL (x2), Drop Foreign Key Constraint, Drop Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml', 'tm-1.4.0.feat-1513.05', '2.0.5', '3:132d9664007ea9ef0be3785013bba662', 162);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml::tm-1.4.0.feat-1513.06::mpagnon::(Checksum: 3:8f9980911439ce0cbb91a1cf37288805)
-- Add attachment lists to projects
ALTER TABLE ATTACHMENT_LIST ADD PROJECT_ID BIGINT;

ALTER TABLE ATTACHMENT_LIST ADD CONSTRAINT fk_project FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT (PROJECT_ID);

COMMENT ON COLUMN ATTACHMENT_LIST.PROJECT_ID IS 'table provisoire pour se rapeller de à qui était destinée la nouvelle attachment_list';

insert into ATTACHMENT_LIST (PROJECT_ID)
			select PROJECT.PROJECT_ID from PROJECT;

update PROJECT 
			set ATTACHMENT_LIST_ID = (select ATTACHMENT_LIST.ATTACHMENT_LIST_ID from ATTACHMENT_LIST where ATTACHMENT_LIST.PROJECT_ID = PROJECT.PROJECT_ID)
			where ATTACHMENT_LIST_ID is null;

ALTER TABLE ATTACHMENT_LIST DROP CONSTRAINT fk_project;

ALTER TABLE ATTACHMENT_LIST DROP COLUMN PROJECT_ID;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Add attachment lists to projects', NOW(), 'Add Column, Custom SQL (x2), Drop Foreign Key Constraint, Drop Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml', 'tm-1.4.0.feat-1513.06', '2.0.5', '3:8f9980911439ce0cbb91a1cf37288805', 163);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml::tm-1.4.0.feat-1513.07::mpagnon::(Checksum: 3:8effa97f3aa3afdfb1165e54f3590dbb)
-- Add attachment lists to reqLib
ALTER TABLE ATTACHMENT_LIST ADD RL_ID BIGINT;

ALTER TABLE ATTACHMENT_LIST ADD CONSTRAINT "fk_rLib" FOREIGN KEY (RL_ID) REFERENCES REQUIREMENT_LIBRARY (RL_ID);

COMMENT ON COLUMN ATTACHMENT_LIST.RL_ID IS 'table provisoire pour se rapeller de à qui était destinée la nouvelle attachment_list';

insert into ATTACHMENT_LIST (RL_ID)
			select REQUIREMENT_LIBRARY.RL_ID from REQUIREMENT_LIBRARY;

update REQUIREMENT_LIBRARY 
			set ATTACHMENT_LIST_ID = (select ATTACHMENT_LIST.ATTACHMENT_LIST_ID from ATTACHMENT_LIST where ATTACHMENT_LIST.RL_ID = REQUIREMENT_LIBRARY.RL_ID)
			where ATTACHMENT_LIST_ID is null;

ALTER TABLE ATTACHMENT_LIST DROP CONSTRAINT "fk_rLib";

ALTER TABLE ATTACHMENT_LIST DROP COLUMN RL_ID;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Add attachment lists to reqLib', NOW(), 'Add Column, Custom SQL (x2), Drop Foreign Key Constraint, Drop Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml', 'tm-1.4.0.feat-1513.07', '2.0.5', '3:8effa97f3aa3afdfb1165e54f3590dbb', 164);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml::tm-1.4.0.feat-1513.08::mpagnon::(Checksum: 3:a0a88d53a3a4159ea8924339b4ec7371)
-- Add attachment lists to test_case_library
ALTER TABLE ATTACHMENT_LIST ADD TCL_ID BIGINT;

ALTER TABLE ATTACHMENT_LIST ADD CONSTRAINT "fk_tcLib" FOREIGN KEY (TCL_ID) REFERENCES TEST_CASE_LIBRARY (TCL_ID);

COMMENT ON COLUMN ATTACHMENT_LIST.TCL_ID IS 'table provisoire pour se rapeller de à qui était destinée la nouvelle attachment_list';

insert into ATTACHMENT_LIST (TCL_ID)
			select TEST_CASE_LIBRARY.TCL_ID from TEST_CASE_LIBRARY;

update TEST_CASE_LIBRARY 
			set ATTACHMENT_LIST_ID = (select ATTACHMENT_LIST.ATTACHMENT_LIST_ID from ATTACHMENT_LIST where ATTACHMENT_LIST.TCL_ID = TEST_CASE_LIBRARY.TCL_ID)
			where ATTACHMENT_LIST_ID is null;

ALTER TABLE ATTACHMENT_LIST DROP CONSTRAINT "fk_tcLib";

ALTER TABLE ATTACHMENT_LIST DROP COLUMN TCL_ID;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Add attachment lists to test_case_library', NOW(), 'Add Column, Custom SQL (x2), Drop Foreign Key Constraint, Drop Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml', 'tm-1.4.0.feat-1513.08', '2.0.5', '3:a0a88d53a3a4159ea8924339b4ec7371', 165);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml::tm-1.4.0.feat-1513.09::mpagnon::(Checksum: 3:eb49cfdde8f39905ce3134599987b174)
-- Add attachment lists to campaign libraries
ALTER TABLE ATTACHMENT_LIST ADD CL_ID BIGINT;

ALTER TABLE ATTACHMENT_LIST ADD CONSTRAINT "fk_cLib" FOREIGN KEY (CL_ID) REFERENCES CAMPAIGN_LIBRARY (CL_ID);

COMMENT ON COLUMN ATTACHMENT_LIST.CL_ID IS 'table provisoire pour se rapeller de à qui était destinée la nouvelle attachment_list';

insert into ATTACHMENT_LIST (CL_ID)
			select CAMPAIGN_LIBRARY.CL_ID from CAMPAIGN_LIBRARY;

update CAMPAIGN_LIBRARY 
			set ATTACHMENT_LIST_ID = (select ATTACHMENT_LIST.ATTACHMENT_LIST_ID from ATTACHMENT_LIST where ATTACHMENT_LIST.CL_ID = CAMPAIGN_LIBRARY.CL_ID)
			where ATTACHMENT_LIST_ID is null;

ALTER TABLE ATTACHMENT_LIST DROP CONSTRAINT "fk_cLib";

ALTER TABLE ATTACHMENT_LIST DROP COLUMN CL_ID;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Add attachment lists to campaign libraries', NOW(), 'Add Column, Custom SQL (x2), Drop Foreign Key Constraint, Drop Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml', 'tm-1.4.0.feat-1513.09', '2.0.5', '3:eb49cfdde8f39905ce3134599987b174', 166);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml::tm-1.4.0.feat-1513.010::mpagnon::(Checksum: 3:53271a83b14f6d877528c3f746ade8c8)
-- Make attachment_list_ids new fks not nullable
ALTER TABLE TEST_CASE_LIBRARY_NODE ALTER COLUMN  ATTACHMENT_LIST_ID SET NOT NULL;

ALTER TABLE CAMPAIGN_LIBRARY_NODE ALTER COLUMN  ATTACHMENT_LIST_ID SET NOT NULL;

ALTER TABLE RESOURCE ALTER COLUMN  ATTACHMENT_LIST_ID SET NOT NULL;

ALTER TABLE PROJECT ALTER COLUMN  ATTACHMENT_LIST_ID SET NOT NULL;

ALTER TABLE REQUIREMENT_LIBRARY ALTER COLUMN  ATTACHMENT_LIST_ID SET NOT NULL;

ALTER TABLE CAMPAIGN_LIBRARY ALTER COLUMN  ATTACHMENT_LIST_ID SET NOT NULL;

ALTER TABLE TEST_CASE_LIBRARY ALTER COLUMN  ATTACHMENT_LIST_ID SET NOT NULL;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Make attachment_list_ids new fks not nullable', NOW(), 'Add Not-Null Constraint (x7)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml', 'tm-1.4.0.feat-1513.010', '2.0.5', '3:53271a83b14f6d877528c3f746ade8c8', 167);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml::tm-1.4.0.feat-1513.11::mpagnon::(Checksum: 3:d9940e95526714a749acbc2df372c2cf)
-- Drop previous attachment list ids
ALTER TABLE TEST_CASE DROP CONSTRAINT fk_test_case_attachment_list;

ALTER TABLE CAMPAIGN DROP CONSTRAINT fk_campaign_attachment_list;

ALTER TABLE REQUIREMENT_VERSION DROP CONSTRAINT fk_requirement_version_attachment_list;

ALTER TABLE TEST_CASE DROP COLUMN ATTACHMENT_LIST_ID;

ALTER TABLE CAMPAIGN DROP COLUMN ATTACHMENT_LIST_ID;

ALTER TABLE REQUIREMENT_VERSION DROP COLUMN ATTACHMENT_LIST_ID;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Drop previous attachment list ids', NOW(), 'Drop Foreign Key Constraint (x3), Drop Column (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml', 'tm-1.4.0.feat-1513.11', '2.0.5', '3:d9940e95526714a749acbc2df372c2cf', 168);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.4.0.xml::tm-1.4.0.feat-1392.01::bsiri::(Checksum: 3:3ff2bd1da5ba3ca536fefc185d4f5c77)
-- The table CUSTOM_FIELD_VALUE holds instances of a CUSTOM_FIELD that are bound to instances of : 
-- 			test cases, requirement version, campagnes, iterations and test suites. A custom field value is created 
-- 			when a custom field binding is created or an entity subject to an existing custom field binding is created. 
-- 			A custom field value is destroyed when the bound entity instance is destroyed or the custom field binding 
-- 			is destroyed.			
-- 						
-- 			Note that in this table the coupling between the custom field value and the bound entity is week : it consists of a pair
-- 			of Type/ID (BINDABLE_ENTITY_TYPE and BINDABLE_ENTITY_ID) rather than a foreign key to the said entity, nor is true 
-- 			the opposite ( ie no FK from a test case to a custom field value).
