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

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

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

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml::tm-1.13.0-feature-5155-1::jsimon::(Checksum: 3:a56f65a96f482490bde5603a06fc5621)
CREATE TABLE BUGTRACKER_PROJECT (BUGTRACKER_PROJECT_ID bigserial NOT NULL, BUGTRACKER_BINDING_ID BIGINT NOT NULL, BUGTRACKER_PROJECT_NAME VARCHAR(255), BUGTRACKER_PROJECT_ORDER INT NOT NULL, CONSTRAINT PK_BUGTRACKER_PROJECT PRIMARY KEY (BUGTRACKER_PROJECT_ID), CONSTRAINT fk_bugtracker_project_bugtracker_binding FOREIGN KEY (BUGTRACKER_BINDING_ID) REFERENCES BUGTRACKER_BINDING(BUGTRACKER_BINDING_ID));

CREATE INDEX idx_bugtracker_project ON BUGTRACKER_PROJECT(BUGTRACKER_PROJECT_ID);

insert into BUGTRACKER_PROJECT(BUGTRACKER_BINDING_ID,
			BUGTRACKER_PROJECT_NAME, BUGTRACKER_PROJECT_ORDER) select
			BUGTRACKER_BINDING_ID, PROJECT_NAME, 0 from BUGTRACKER_BINDING;

ALTER TABLE BUGTRACKER_BINDING DROP COLUMN PROJECT_NAME;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jsimon', '', NOW(), 'Create Table, Create Index, Custom SQL, Drop Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml', 'tm-1.13.0-feature-5155-1', '2.0.5', '3:a56f65a96f482490bde5603a06fc5621', 364);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml::tm-1.13.0-feature-5162-1::bsiri::(Checksum: 3:0a9de91bdb533abb9e3e14b6a68dd6e8)
-- Now campaigns and iterations have a reference too
ALTER TABLE CAMPAIGN ADD REFERENCE VARCHAR(50) DEFAULT '';

COMMENT ON COLUMN CAMPAIGN.REFERENCE IS 'a reference lavel for a campaign';

ALTER TABLE ITERATION ADD REFERENCE VARCHAR(50) DEFAULT '';

COMMENT ON COLUMN ITERATION.REFERENCE IS 'a reference lavel for an iteration';

update ITERATION i
			set reference = (select (ci.iteration_order +
			1) from
			CAMPAIGN_ITERATION ci where ci.iteration_id = i.iteration_id
			);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Now campaigns and iterations have a reference too', NOW(), 'Add Column (x2), Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml', 'tm-1.13.0-feature-5162-1', '2.0.5', '3:0a9de91bdb533abb9e3e14b6a68dd6e8', 365);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml::tm-1.13.0-feature-4022-1::jsimon::(Checksum: 3:2976df3ca9dc93cdfbfcca42bec41c0e)
ALTER TABLE PROJECT ADD ALLOW_TC_MODIF_DURING_EXEC BOOLEAN NOT NULL DEFAULT FALSE;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jsimon', '', 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.13.0.xml', 'tm-1.13.0-feature-4022-1', '2.0.5', '3:2976df3ca9dc93cdfbfcca42bec41c0e', 366);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml::tm-1.13.0-feature-5292-issues::bsiri::(Checksum: 3:7cc7f139a4c92ab4725e8daa938c3af6)
-- creating a view that helps querying on all the issues reported in an execution because
-- 			querying EXECUTION and EXECUTION_STEP separately is just a pain in the ass.
CREATE VIEW EXECUTION_ISSUES_CLOSURE AS select exec.execution_id, issue.issue_id
			from EXECUTION exec
			inner join ISSUE issue on exec.issue_list_id = issue.issue_list_id
			UNION
			select eesteps.execution_id, issue.issue_id
			from EXECUTION_EXECUTION_STEPS eesteps
			inner join EXECUTION_STEP estep on eesteps.execution_step_id = estep.execution_step_id
			inner join ISSUE issue on estep.issue_list_id = issue.issue_list_id;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'creating a view that helps querying on all the issues reported in an execution because
			querying EXECUTION and EXECUTION_STEP separately is just a pain in the ass.', NOW(), 'Create View', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml', 'tm-1.13.0-feature-5292-issues', '2.0.5', '3:7cc7f139a4c92ab4725e8daa938c3af6', 367);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml::tm-1.13.0-feature-5375-1::jsimon, bsiri::(Checksum: 3:e5df9278737d833aec4f141a4842f68c)
-- The scope is a particular filter on the data : it restrict the perimeter from which the measured entities can be chosen,
-- 			for example from project X and folder Y
CREATE TABLE CHART_QUERY (CHART_QUERY_ID bigserial NOT NULL, NAME VARCHAR(100), STRATEGY VARCHAR(20), JOIN_STYLE VARCHAR(10), CONSTRAINT PK_CHART_QUERY PRIMARY KEY (CHART_QUERY_ID));

COMMENT ON COLUMN CHART_QUERY.CHART_QUERY_ID IS 'autogenerated ID';

COMMENT ON COLUMN CHART_QUERY.NAME IS 'internal usage, when this CHART_QUERY is a    CHART_COLUMN_PROTOTYPE subquery the name helps identify which one';

COMMENT ON COLUMN CHART_QUERY.STRATEGY IS 'says how the query builder should integrate this query    to a main query. Accepts values : MAIN, SUBQUERY or INLINED';

COMMENT ON COLUMN CHART_QUERY.JOIN_STYLE IS 'generally indicate whether should use inner join or left join when a    natural join is possible. Possible values : INNER_JOIN or LEFT_JOIN';

CREATE TABLE CHART_DEFINITION (CHART_ID bigserial NOT NULL, USER_ID BIGINT NOT NULL, QUERY_ID BIGINT NOT NULL, NAME VARCHAR(50), VISIBILITY VARCHAR(20), CHART_TYPE VARCHAR(20), DESCRIPTION TEXT, PROJECT_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_CHART_DEFINITION PRIMARY KEY (CHART_ID), CONSTRAINT fk_chart_owner FOREIGN KEY (USER_ID) REFERENCES CORE_USER(PARTY_ID), CONSTRAINT fk_project_chart_definition_project_id FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID), CONSTRAINT fk_chart_query FOREIGN KEY (QUERY_ID) REFERENCES CHART_QUERY(CHART_QUERY_ID));

COMMENT ON COLUMN CHART_DEFINITION.CHART_ID IS 'autogenerated ID';

COMMENT ON COLUMN CHART_DEFINITION.USER_ID IS 'foreign key to the owner of this chart definition';

COMMENT ON COLUMN CHART_DEFINITION.QUERY_ID IS 'foreign key to a CHART_QUERY';

COMMENT ON COLUMN CHART_DEFINITION.NAME IS 'The name of this chart';

COMMENT ON COLUMN CHART_DEFINITION.VISIBILITY IS 'whether this chart is ''PRIVATE'' or ''PUBLIC''';

COMMENT ON COLUMN CHART_DEFINITION.CHART_TYPE IS 'whether this chart is a ''PIE'', ''BAR'' etc';

COMMENT ON COLUMN CHART_DEFINITION.DESCRIPTION IS 'additional informations about this chart';

COMMENT ON COLUMN CHART_DEFINITION.PROJECT_ID IS 'cle etrangere vers le projet.';

CREATE INDEX idx_chart_definition ON CHART_DEFINITION(CHART_ID);

CREATE TABLE CHART_COLUMN_PROTOTYPE (CHART_COLUMN_ID bigserial NOT NULL, COLUMN_TYPE VARCHAR(15), SUBQUERY_ID BIGINT, LABEL VARCHAR(255), ENTITY_TYPE VARCHAR(30), ENTITY_ROLE VARCHAR(40), ATTRIBUTE_NAME VARCHAR(255), DATA_TYPE VARCHAR(50), BUSINESS BOOLEAN DEFAULT TRUE, ATTRIBUTE_TYPE VARCHAR(15), CONSTRAINT PK_CHART_COLUMN_PROTOTYPE PRIMARY KEY (CHART_COLUMN_ID), CONSTRAINT fk_column_query FOREIGN KEY (SUBQUERY_ID) REFERENCES CHART_QUERY(CHART_QUERY_ID));

COMMENT ON COLUMN CHART_COLUMN_PROTOTYPE.CHART_COLUMN_ID IS 'the column ID';

COMMENT ON COLUMN CHART_COLUMN_PROTOTYPE.COLUMN_TYPE IS 'complementary information about the nature of this column : native attribute (''ATTRIBUTE''),     computed field (''CALCULATED'') - often as a subquery -, or custom field (''CUF'')';

COMMENT ON COLUMN CHART_COLUMN_PROTOTYPE.SUBQUERY_ID IS 'optional foreign key to a (sub) CHART_QUERY';

COMMENT ON COLUMN CHART_COLUMN_PROTOTYPE.LABEL IS 'The default label for this column.';

COMMENT ON COLUMN CHART_COLUMN_PROTOTYPE.ENTITY_TYPE IS 'The kind of entities this column belongs to';

COMMENT ON COLUMN CHART_COLUMN_PROTOTYPE.ENTITY_ROLE IS 'a complementary qualifier for ENTITY_TYPE,           used when an entity has an auxialiary role regarding another entity.           For instance an InfoListItem may have a role of nature for a TestCase,           or category for a RequirementVersion. May be null if nothing special about it.';

COMMENT ON COLUMN CHART_COLUMN_PROTOTYPE.ATTRIBUTE_NAME IS 'The name of the attribute of the entity';

COMMENT ON COLUMN CHART_COLUMN_PROTOTYPE.DATA_TYPE IS 'Data type of the attribute';

COMMENT ON COLUMN CHART_COLUMN_PROTOTYPE.BUSINESS IS 'tells whether this column is public or internal.';

COMMENT ON COLUMN CHART_COLUMN_PROTOTYPE.ATTRIBUTE_TYPE IS 'complementary information about the nature of this column : native attribute (''ATTRIBUTE''),     computed field (''CALCULATED'') - often as a subquery -, or custom field (''CUF'')';

CREATE INDEX idx_column_prototype ON CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID);

CREATE TABLE CHART_COLUMN_ROLE (CHART_COLUMN_ID BIGINT NOT NULL, ROLE VARCHAR(255), CONSTRAINT fk_column_role_chart_column FOREIGN KEY (CHART_COLUMN_ID) REFERENCES CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID));

COMMENT ON COLUMN CHART_COLUMN_ROLE.CHART_COLUMN_ID IS 'foreign key to CHART_COLUMN_PROTOTYPE';

COMMENT ON COLUMN CHART_COLUMN_ROLE.ROLE IS 'name of a role for the referenced column';

CREATE TABLE CHART_MEASURE_COLUMN (CHART_COLUMN_ID BIGINT NOT NULL, QUERY_ID BIGINT NOT NULL, LABEL VARCHAR(30), MEASURE_OPERATION VARCHAR(20), MEASURE_RANK INT, CONSTRAINT fk_chart_measure_chart_column FOREIGN KEY (CHART_COLUMN_ID) REFERENCES CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID), CONSTRAINT fk_chart_measure_chart_query FOREIGN KEY (QUERY_ID) REFERENCES CHART_QUERY(CHART_QUERY_ID));

COMMENT ON COLUMN CHART_MEASURE_COLUMN.CHART_COLUMN_ID IS 'foreign key to the CHART_COLUMN_PROTOTYPE this measure applies to';

COMMENT ON COLUMN CHART_MEASURE_COLUMN.QUERY_ID IS 'foreign key to a CHART_QUERY';

COMMENT ON COLUMN CHART_MEASURE_COLUMN.LABEL IS 'a label for this measure. Will override the default label of the column prototype if non null.';

COMMENT ON COLUMN CHART_MEASURE_COLUMN.MEASURE_OPERATION IS 'the measure applied to the column';

COMMENT ON COLUMN CHART_MEASURE_COLUMN.MEASURE_RANK IS 'the order in which the measure will be displayed when the chart is rendered.';

CREATE TABLE CHART_FILTER (FILTER_ID bigserial NOT NULL, CHART_COLUMN_ID BIGINT NOT NULL, QUERY_ID BIGINT NOT NULL, FILTER_OPERATION VARCHAR(20), CONSTRAINT PK_CHART_FILTER PRIMARY KEY (FILTER_ID), CONSTRAINT fk_chart_filter_chart_column FOREIGN KEY (CHART_COLUMN_ID) REFERENCES CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID), CONSTRAINT "fk_chart_FILTER_chart_query" FOREIGN KEY (QUERY_ID) REFERENCES CHART_QUERY(CHART_QUERY_ID));

COMMENT ON COLUMN CHART_FILTER.FILTER_ID IS 'autogenerated ID';

COMMENT ON COLUMN CHART_FILTER.CHART_COLUMN_ID IS 'foreign key to a CHART_COLUMN_PROTOTYPE';

COMMENT ON COLUMN CHART_FILTER.QUERY_ID IS 'foreign key to a CHART_QUERY';

COMMENT ON COLUMN CHART_FILTER.FILTER_OPERATION IS 'a comparison operator';

CREATE INDEX idx_chart_filter ON CHART_FILTER(FILTER_ID);

CREATE TABLE CHART_FILTER_VALUES (FILTER_ID BIGINT NOT NULL, FILTER_VALUE VARCHAR(255), CONSTRAINT fk_chart_filter_value_chart_column FOREIGN KEY (FILTER_ID) REFERENCES CHART_FILTER(FILTER_ID));

COMMENT ON COLUMN CHART_FILTER_VALUES.FILTER_ID IS 'foreign key to CHART_FILTER';

COMMENT ON COLUMN CHART_FILTER_VALUES.FILTER_VALUE IS 'a value for this filter';

CREATE TABLE CHART_AXIS_COLUMN (CHART_COLUMN_ID BIGINT NOT NULL, QUERY_ID BIGINT NOT NULL, LABEL VARCHAR(100), AXIS_OPERATION VARCHAR(20), AXIS_RANK INT, CONSTRAINT fk_chart_axis_chart_query FOREIGN KEY (QUERY_ID) REFERENCES CHART_QUERY(CHART_QUERY_ID), CONSTRAINT fk_axis_column_chart_column FOREIGN KEY (CHART_COLUMN_ID) REFERENCES CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID));

COMMENT ON COLUMN CHART_AXIS_COLUMN.CHART_COLUMN_ID IS 'foreign key to CHART_COLUMN_PROTOTYPE';

COMMENT ON COLUMN CHART_AXIS_COLUMN.QUERY_ID IS 'foreign key to a CHART_QUERY';

COMMENT ON COLUMN CHART_AXIS_COLUMN.LABEL IS 'a label for this axis. If non null, will override the default label in the CHART_COLUMN_PROTOTYPE';

COMMENT ON COLUMN CHART_AXIS_COLUMN.AXIS_OPERATION IS 'may define an additional operation about how data are grouped on this axis, most of the time ''NONE'' is fine, however     for Timestamp columns an adequate aggregation (day, month etc) may be useful';

COMMENT ON COLUMN CHART_AXIS_COLUMN.AXIS_RANK IS 'in which order the axis are processed (if relevant)';

CREATE TABLE CHART_SCOPE (CHART_ID BIGINT NOT NULL, ENTITY_REFERENCE_TYPE VARCHAR(50), ENTITY_REFERENCE_ID INT, CONSTRAINT fk_chart_scope_chart_definition FOREIGN KEY (CHART_ID) REFERENCES CHART_DEFINITION(CHART_ID));

COMMENT ON COLUMN CHART_SCOPE.CHART_ID IS 'foreign key to a CHART_DEFINITION';

COMMENT ON COLUMN CHART_SCOPE.ENTITY_REFERENCE_TYPE IS 'the type of the referenced entity';

COMMENT ON COLUMN CHART_SCOPE.ENTITY_REFERENCE_ID IS 'the id of the referenced entity';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jsimon, bsiri', 'The scope is a particular filter on the data : it restrict the perimeter from which the measured entities can be chosen,
			for example from project X and folder Y', NOW(), 'Create Table (x2), Create Index, Create Table, Create Index, Create Table (x3), Create Index, Create Table (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml', 'tm-1.13.0-feature-5375-1', '2.0.5', '3:e5df9278737d833aec4f141a4842f68c', 368);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml::tm-1.13.0-feature-5375-2::bsiri::(Checksum: 3:440e9bd08f41c2961d3f5451c56f5dbe)
-- populate the column prototype referential data (generated from src/main/script/MakeChartColumns.groovy)
-- --------------------------------------------
		-- Chart Column Prototype Definition --
		--
		-- generated by src/main/script/MakeChartColumns.groovy
		-- --------------------------------------------
		
		-- -------------------------------------------
		-- section 1 :  basic attribute columns
		-- -------------------------------------------
		
		
		-- columns for entity : REQUIREMENT --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(1, 'ATTRIBUTE', TRUE, 'REQUIREMENT_ID', 'REQUIREMENT', null, 'NUMERIC', 'id', null),
			(3, 'ATTRIBUTE', FALSE, 'REQUIREMENT_PROJECT', 'REQUIREMENT', null, 'NUMERIC', 'project.id', null),
			(4, 'ATTRIBUTE', TRUE, 'REQUIREMENT_CRITICALITY', 'REQUIREMENT', null, 'LEVEL_ENUM', 'resource.criticality', null),
			(5, 'ATTRIBUTE', TRUE, 'REQUIREMENT_STATUS', 'REQUIREMENT', null, 'LEVEL_ENUM', 'resource.status', null),
			(6, 'ATTRIBUTE', TRUE, 'REQUIREMENT_CATEGORY', 'REQUIREMENT', null, 'INFO_LIST_ITEM', 'resource.category.code', null);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
		values
			 (1, 'MEASURE'), (1, 'AXIS'), (1, 'FILTER'),
			 (4, 'MEASURE'), (4, 'AXIS'), (4, 'FILTER'),
			 (5, 'MEASURE'), (5, 'AXIS'), (5, 'FILTER'),
			 (6, 'MEASURE'), (6, 'AXIS'), (6, 'FILTER');

-- columns for entity : REQUIREMENT_VERSION --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(7, 'ATTRIBUTE', TRUE, 'REQUIREMENT_VERSION_ID', 'REQUIREMENT_VERSION', null, 'NUMERIC', 'id', null),
			(8, 'ATTRIBUTE', TRUE, 'REQUIREMENT_VERSION_REFERENCE', 'REQUIREMENT_VERSION', null, 'STRING', 'reference', null),
			(9, 'ATTRIBUTE', TRUE, 'REQUIREMENT_VERSION_CATEGORY', 'REQUIREMENT_VERSION', null, 'INFO_LIST_ITEM', 'category.code', null),
			(10, 'ATTRIBUTE', TRUE, 'REQUIREMENT_VERSION_CRITICALITY', 'REQUIREMENT_VERSION', null, 'LEVEL_ENUM', 'criticality', null),
			(11, 'ATTRIBUTE', TRUE, 'REQUIREMENT_VERSION_STATUS', 'REQUIREMENT_VERSION', null, 'LEVEL_ENUM', 'status', null),
			(12, 'ATTRIBUTE', TRUE, 'REQUIREMENT_VERSION_CREATED_BY', 'REQUIREMENT_VERSION', null, 'STRING', 'audit.createdBy', null),
			(13, 'ATTRIBUTE', TRUE, 'REQUIREMENT_VERSION_CREATED_ON', 'REQUIREMENT_VERSION', null, 'DATE', 'audit.createdOn', null),
			(14, 'ATTRIBUTE', TRUE, 'REQUIREMENT_VERSION_MODIFIED_BY', 'REQUIREMENT_VERSION', null, 'STRING', 'audit.lastModifiedBy', null),
			(15, 'ATTRIBUTE', TRUE, 'REQUIREMENT_VERSION_MODIFIED_ON', 'REQUIREMENT_VERSION', null, 'DATE', 'audit.lastModifiedOn', null),
			(16, 'ATTRIBUTE', TRUE, 'REQUIREMENT_VERSION_VERS_NUM', 'REQUIREMENT_VERSION', null, 'NUMERIC', 'versionNumber', null);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
		values
			 (7, 'MEASURE'), (7, 'AXIS'), (7, 'FILTER'),
			 (8, 'FILTER'), (8, 'AXIS'),
			 (9, 'MEASURE'), (9, 'AXIS'), (9, 'FILTER'),
			 (10, 'MEASURE'), (10, 'AXIS'), (10, 'FILTER'),
			 (11, 'MEASURE'), (11, 'AXIS'), (11, 'FILTER'),
			 (12, 'AXIS'), (12, 'FILTER'),
			 (13, 'AXIS'), (13, 'FILTER'),
			 (14, 'AXIS'), (14, 'FILTER'),
			 (15, 'AXIS'), (15, 'FILTER'),
			 (16, 'FILTER'), (16, 'MEASURE');

-- columns for entity : TEST_CASE --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(19, 'ATTRIBUTE', TRUE, 'TEST_CASE_ID', 'TEST_CASE', null, 'NUMERIC', 'id', null),
			(20, 'ATTRIBUTE', TRUE, 'TEST_CASE_REFERENCE', 'TEST_CASE', null, 'STRING', 'reference', null),
			(21, 'ATTRIBUTE', TRUE, 'TEST_CASE_IMPORTANCE', 'TEST_CASE', null, 'LEVEL_ENUM', 'importance', null),
			(22, 'ATTRIBUTE', TRUE, 'TEST_CASE_NATURE', 'TEST_CASE', null, 'INFO_LIST_ITEM', 'nature.code', null),
			(23, 'ATTRIBUTE', TRUE, 'TEST_CASE_TYPE', 'TEST_CASE', null, 'INFO_LIST_ITEM', 'type.code', null),
			(24, 'ATTRIBUTE', TRUE, 'TEST_CASE_STATUS', 'TEST_CASE', null, 'LEVEL_ENUM', 'status', null),
			(25, 'ATTRIBUTE', TRUE, 'TEST_CASE_CREATED_BY', 'TEST_CASE', null, 'STRING', 'audit.createdBy', null),
			(26, 'ATTRIBUTE', TRUE, 'TEST_CASE_CREATED_ON', 'TEST_CASE', null, 'DATE', 'audit.createdOn', null),
			(27, 'ATTRIBUTE', TRUE, 'TEST_CASE_MODIFIED_BY', 'TEST_CASE', null, 'STRING', 'audit.lastModifiedBy', null),
			(28, 'ATTRIBUTE', TRUE, 'TEST_CASE_MODIFIED_ON', 'TEST_CASE', null, 'DATE', 'audit.lastModifiedOn', null),
			(29, 'ATTRIBUTE', FALSE, 'TEST_CASE_PROJECT', 'TEST_CASE', null, 'NUMERIC', 'project.id', null);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
		values
			 (19, 'MEASURE'), (19, 'AXIS'), (19, 'FILTER'),
			 (20, 'FILTER'), (20, 'AXIS'),
			 (21, 'MEASURE'), (21, 'AXIS'), (21, 'FILTER'),
			 (22, 'MEASURE'), (22, 'AXIS'), (22, 'FILTER'),
			 (23, 'MEASURE'), (23, 'AXIS'), (23, 'FILTER'),
			 (24, 'MEASURE'), (24, 'AXIS'), (24, 'FILTER'),
			 (25, 'AXIS'), (25, 'FILTER'),
			 (26, 'AXIS'), (26, 'FILTER'),
			 (27, 'AXIS'), (27, 'FILTER'),
			 (28, 'AXIS'), (28, 'FILTER');

-- columns for entity : CAMPAIGN --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(37, 'ATTRIBUTE', TRUE, 'CAMPAIGN_ID', 'CAMPAIGN', null, 'NUMERIC', 'id', null),
			(38, 'ATTRIBUTE', FALSE, 'CAMPAIGN_PROJECT', 'CAMPAIGN', null, 'NUMERIC', 'project.id', null),
			(39, 'ATTRIBUTE', TRUE, 'CAMPAIGN_REFERENCE', 'CAMPAIGN', null, 'STRING', 'reference', null),
			(40, 'ATTRIBUTE', TRUE, 'CAMPAIGN_SCHED_START', 'CAMPAIGN', null, 'DATE', 'scheduledPeriod.scheduledStartDate', null),
			(41, 'ATTRIBUTE', TRUE, 'CAMPAIGN_SCHED_END', 'CAMPAIGN', null, 'DATE', 'scheduledPeriod.scheduledEndDate', null),
			(42, 'ATTRIBUTE', TRUE, 'CAMPAIGN_ACTUAL_START', 'CAMPAIGN', null, 'DATE', 'actualPeriod.actualStartDate', null),
			(43, 'ATTRIBUTE', TRUE, 'CAMPAIGN_ACTUAL_END', 'CAMPAIGN', null, 'DATE', 'actualPeriod.actualEndDate', null);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
		values
			 (37, 'MEASURE'), (37, 'AXIS'), (37, 'FILTER'),
			 (39, 'FILTER'), (39, 'MEASURE'),
			 (40, 'AXIS'), (40, 'FILTER'),
			 (41, 'AXIS'), (41, 'FILTER'),
			 (42, 'AXIS'), (42, 'FILTER'),
			 (43, 'AXIS'), (43, 'FILTER');

-- columns for entity : ITERATION --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(46, 'ATTRIBUTE', TRUE, 'ITERATION_ID', 'ITERATION', null, 'NUMERIC', 'id', null),
			(47, 'ATTRIBUTE', TRUE, 'ITERATION_REFERENCE', 'ITERATION', null, 'STRING', 'reference', null),
			(48, 'ATTRIBUTE', TRUE, 'ITERATION_SCHED_START', 'ITERATION', null, 'DATE', 'scheduledPeriod.scheduledStartDate', null),
			(49, 'ATTRIBUTE', TRUE, 'ITERATION_SCHED_END', 'ITERATION', null, 'DATE', 'scheduledPeriod.scheduledEndDate', null),
			(50, 'ATTRIBUTE', TRUE, 'ITERATION_ACTUAL_START', 'ITERATION', null, 'DATE', 'actualPeriod.actualStartDate', null),
			(51, 'ATTRIBUTE', TRUE, 'ITERATION_ACTUAL_END', 'ITERATION', null, 'DATE', 'actualPeriod.actualEndDate', null);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
		values
			 (46, 'MEASURE'), (46, 'AXIS'), (46, 'FILTER'),
			 (47, 'FILTER'), (47, 'MEASURE'),
			 (48, 'AXIS'), (48, 'FILTER'),
			 (49, 'AXIS'), (49, 'FILTER'),
			 (50, 'AXIS'), (50, 'FILTER'),
			 (51, 'AXIS'), (51, 'FILTER');

-- columns for entity : ITEM_TEST_PLAN --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(54, 'ATTRIBUTE', TRUE, 'ITEM_TEST_PLAN_ID', 'ITEM_TEST_PLAN', null, 'NUMERIC', 'id', null),
			(55, 'ATTRIBUTE', TRUE, 'ITEM_TEST_PLAN_LABEL', 'ITEM_TEST_PLAN', null, 'STRING', 'label', null),
			(56, 'ATTRIBUTE', TRUE, 'ITEM_TEST_PLAN_STATUS', 'ITEM_TEST_PLAN', null, 'EXECUTION_STATUS', 'executionStatus', null),
			(57, 'ATTRIBUTE', TRUE, 'ITEM_TEST_PLAN_LASTEXECON', 'ITEM_TEST_PLAN', null, 'DATE', 'lastExecutedOn', null),
			(58, 'ATTRIBUTE', TRUE, 'ITEM_TEST_PLAN_DATASET_LABEL', 'ITEM_TEST_PLAN', null, 'STRING', 'referencedDataset.name', null),
			(59, 'ATTRIBUTE', TRUE, 'ITEM_TEST_PLAN_TESTER', 'ITEM_TEST_PLAN', null, 'STRING', 'user.login', null),
			(60, 'ATTRIBUTE', TRUE, 'ITEM_TEST_PLAN_TC_ID', 'ITEM_TEST_PLAN', null, 'NUMERIC', 'referencedTestCase.id', null);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
		values
			 (54, 'MEASURE'), (54, 'AXIS'), (54, 'FILTER'),
			 (55, 'MEASURE'), (55, 'AXIS'), (55, 'FILTER'),
			 (56, 'MEASURE'), (56, 'AXIS'), (56, 'FILTER'),
			 (57, 'MEASURE'), (57, 'AXIS'), (57, 'FILTER'),
			 (58, 'MEASURE'), (58, 'AXIS'), (58, 'FILTER'),
			 (59, 'MEASURE'), (59, 'AXIS'), (59, 'FILTER'),
			 (60, 'MEASURE'), (60, 'AXIS'), (60, 'FILTER');

-- columns for entity : EXECUTION --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(66, 'ATTRIBUTE', TRUE, 'EXECUTION_ID', 'EXECUTION', null, 'NUMERIC', 'id', null),
			(67, 'ATTRIBUTE', TRUE, 'EXECUTION_LABEL', 'EXECUTION', null, 'STRING', 'name', null),
			(68, 'ATTRIBUTE', TRUE, 'EXECUTION_DS_LABEL', 'EXECUTION', null, 'STRING', 'datasetLabel', null),
			(69, 'ATTRIBUTE', TRUE, 'EXECUTION_LASTEXEC', 'EXECUTION', null, 'DATE', 'lastExecutedOn', null),
			(70, 'ATTRIBUTE', TRUE, 'EXECUTION_TESTER_LOGIN', 'EXECUTION', null, 'STRING', 'lastExecutedBy', null),
			(71, 'ATTRIBUTE', TRUE, 'EXECUTION_STATUS', 'EXECUTION', null, 'EXECUTION_STATUS', 'executionStatus', null);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
		values
			 (66, 'MEASURE'), (66, 'AXIS'), (66, 'FILTER'),
			 (67, 'AXIS'), (67, 'FILTER'),
			 (68, 'AXIS'), (68, 'FILTER'),
			 (69, 'AXIS'), (69, 'FILTER'),
			 (70, 'AXIS'), (70, 'FILTER'),
			 (71, 'MEASURE'), (71, 'AXIS'), (71, 'FILTER');

-- columns for entity : ISSUE --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(74, 'ATTRIBUTE', FALSE, 'ISSUE_ID', 'ISSUE', null, 'NUMERIC', 'id', null),
			(75, 'ATTRIBUTE', FALSE, 'ISSUE_REMOTE_ID', 'ISSUE', null, 'STRING', 'remoteIssueId', null),
			(76, 'ATTRIBUTE', FALSE, 'ISSUE_STATUS', 'ISSUE', null, 'STRING', 'status', null),
			(77, 'ATTRIBUTE', FALSE, 'ISSUE_SEVERITY', 'ISSUE', null, 'STRING', 'severity', null),
			(78, 'ATTRIBUTE', FALSE, 'ISSUE_BUGTRACKER', 'ISSUE', null, 'STRING', 'bugtracker', null);

-- columns for entity : TEST_CASE_STEP --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(79, 'ATTRIBUTE', FALSE, 'TEST_CASE_STEP_ID', 'TEST_CASE_STEP', null, 'NUMERIC', 'id', null),
			(80, 'ATTRIBUTE', FALSE, 'TEST_CASE_STEP_CLASS', 'TEST_CASE_STEP', null, 'NUMERIC', 'class', null);

-- columns for entity : TEST_CASE_NATURE --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(81, 'ATTRIBUTE', FALSE, 'TEST_CASE_NATURE_ID', 'INFO_LIST_ITEM', 'TEST_CASE_NATURE', 'NUMERIC', 'id', null),
			(82, 'ATTRIBUTE', FALSE, 'TEST_CASE_NATURE_LABEL', 'INFO_LIST_ITEM', 'TEST_CASE_NATURE', 'STRING', 'label', null);

-- columns for entity : TEST_CASE_TYPE --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(83, 'ATTRIBUTE', FALSE, 'TEST_CASE_TYPE_ID', 'INFO_LIST_ITEM', 'TEST_CASE_TYPE', 'NUMERIC', 'id', null),
			(84, 'ATTRIBUTE', FALSE, 'TEST_CASE_TYPE_LABEL', 'INFO_LIST_ITEM', 'TEST_CASE_TYPE', 'STRING', 'label', null);

-- columns for entity : REQUIREMENT_VERSION_CATEGORY --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(85, 'ATTRIBUTE', FALSE, 'REQUIREMENT_VERSION_CATEGORY_ID', 'INFO_LIST_ITEM', 'REQUIREMENT_VERSION_CATEGORY', 'NUMERIC', 'id', null),
			(86, 'ATTRIBUTE', FALSE, 'REQUIREMENT_VERSION_CATEGORY_LABEL', 'INFO_LIST_ITEM', 'REQUIREMENT_VERSION_CATEGORY', 'STRING', 'label', null);

-- columns for entity : TEST_CASE_MILESTONE --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(87, 'ATTRIBUTE', FALSE, 'TEST_CASE_MILESTONE_ID', 'MILESTONE', 'TEST_CASE_MILESTONE', 'NUMERIC', 'id', null),
			(88, 'ATTRIBUTE', FALSE, 'TEST_CASE_MILESTONE_LABEL', 'MILESTONE', 'TEST_CASE_MILESTONE', 'STRING', 'label', null);

-- columns for entity : REQUIREMENT_VERSION_MILESTONE --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(89, 'ATTRIBUTE', FALSE, 'REQUIREMENT_VERSION_MILESTONE_ID', 'MILESTONE', 'REQUIREMENT_VERSION_MILESTONE', 'NUMERIC', 'id', null),
			(90, 'ATTRIBUTE', FALSE, 'REQUIREMENT_VERSION_MILESTONE_LABEL', 'MILESTONE', 'REQUIREMENT_VERSION_MILESTONE', 'STRING', 'label', null);

-- columns for entity : ITERATION_TEST_PLAN_ASSIGNED_USER --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(91, 'ATTRIBUTE', FALSE, 'ITERATION_TEST_PLAN_ASSIGNED_USER_ID', 'USER', 'ITERATION_TEST_PLAN_ASSIGNED_USER', 'NUMERIC', 'id', null),
			(92, 'ATTRIBUTE', FALSE, 'ITERATION_TEST_PLAN_ASSIGNED_USER_LOGIN', 'USER', 'ITERATION_TEST_PLAN_ASSIGNED_USER', 'STRING', 'login', null);

-- columns for entity : AUTOMATED_TEST --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(93, 'ATTRIBUTE', FALSE, 'AUTOMATED_TEST_ID', 'AUTOMATED_TEST', null, 'NUMERIC', 'id', null);

-- columns for entity : AUTOMATED_EXECUTION_EXTENDER --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(94, 'ATTRIBUTE', FALSE, 'AUTOMATED_EXECUTION_EXTENDER_ID', 'AUTOMATED_EXECUTION_EXTENDER', null, 'NUMERIC', 'id', null);

-- -------------------------------------------
		-- section 2 :  subqueries
		-- -------------------------------------------
		
		-- subqueries for entity REQUIREMENT --
		
		-- subquery reqVCountSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (1, 'REQUIREMENT_NB_VERSIONS_SUBQUERY', 'SUBQUERY', 'INNER_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (7, 1, 'COUNT', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (1, 1, 'NONE', 0);

-- subqueries for entity REQUIREMENT_VERSION --
		
		-- subquery rvVerifTCCountSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (2, 'REQUIREMENT_VERSION_TCCOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (19, 2, 'COUNT', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (7, 2, 'NONE', 0);

-- subquery rvMilesCountSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (3, 'REQUIREMENT_VERSION_MILCOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (89, 3, 'COUNT', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (7, 3, 'NONE', 0);

-- subqueries for entity TEST_CASE --
		
		-- subquery tcVerifVersionCountSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (4, 'TEST_CASE_VERSCOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (7, 4, 'COUNT', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (19, 4, 'NONE', 0);

-- subquery tcCallStepsCountSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (5, 'TEST_CASE_CALLSTEPCOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (79, 5, 'COUNT', 0);

insert into CHART_FILTER(FILTER_ID, CHART_COLUMN_ID, QUERY_ID, FILTER_OPERATION) values (1, 80, 5, 'EQUALS');

insert into CHART_FILTER_VALUES(FILTER_ID, FILTER_VALUE)
		values 
			(1,'2');

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (19, 5, 'NONE', 0);

-- subquery tcStepsCountSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (6, 'TEST_CASE_STEPCOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (79, 6, 'COUNT', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (19, 6, 'NONE', 0);

-- subquery tcMilesCountSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (7, 'TEST_CASE_VERSION_MILCOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (87, 7, 'COUNT', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (19, 7, 'NONE', 0);

-- subquery tcIterCountSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (8, 'TEST_CASE_ITERCOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (46, 8, 'COUNT', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (19, 8, 'NONE', 0);

-- subquery tcExeCountSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (9, 'TEST_CASE_EXECOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (66, 9, 'COUNT', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (19, 9, 'NONE', 0);

-- subquery tcHasAutoSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (10, 'TEST_CASE_HASAUTOSCRIPT_SUBQUERY', 'INLINED', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (93, 10, 'NOT_NULL', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (19, 10, 'NONE', 0);

-- subqueries for entity CAMPAIGN --
		
		-- subquery cIterCountSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (11, 'CAMPAIGN_ITERCOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (46, 11, 'COUNT', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (37, 11, 'NONE', 0);

-- subquery cIssueCountSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (12, 'CAMPAIGN_ISSUECOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (75, 12, 'COUNT', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (37, 12, 'NONE', 0);

-- subqueries for entity ITERATION --
		
		-- subquery itItemCountSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (13, 'ITERATION_ITEMCOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (54, 13, 'COUNT', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (46, 13, 'NONE', 0);

-- subquery itIssueCountSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (14, 'ITERATION_ISSUECOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (75, 14, 'COUNT', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (46, 14, 'NONE', 0);

-- subqueries for entity ITEM_TEST_PLAN --
		
		-- subquery itpTcDeletedSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (15, 'ITEM_TEST_PLAN_TCDELETED_SUBQUERY', 'INLINED', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (19, 15, 'IS_NULL', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (54, 15, 'NONE', 0);

-- subquery itpIsExecutedSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (16, 'ITEM_TEST_PLAN_ISEXECUTED_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (66, 16, 'NOT_NULL', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (54, 16, 'NONE', 0);

-- subquery itpManExCountSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (17, 'ITEM_TEST_PLAN_MANEXCOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (66, 17, 'COUNT', 0);

insert into CHART_FILTER(FILTER_ID, CHART_COLUMN_ID, QUERY_ID, FILTER_OPERATION) values (2, 94, 17, 'NOT_NULL');

insert into CHART_FILTER_VALUES(FILTER_ID, FILTER_VALUE)
		values 
			(2,'FALSE');

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (54, 17, 'NONE', 0);

-- subquery itpAutoExCountSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (18, 'ITEM_TEST_PLAN_AUTOEXCOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (66, 18, 'COUNT', 0);

insert into CHART_FILTER(FILTER_ID, CHART_COLUMN_ID, QUERY_ID, FILTER_OPERATION) values (3, 94, 18, 'NOT_NULL');

insert into CHART_FILTER_VALUES(FILTER_ID, FILTER_VALUE)
		values 
			(3,'TRUE');

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (54, 18, 'NONE', 0);

-- subquery itpIssueCountSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (19, 'ITEM_TEST_PLAN_ISSUECOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (75, 19, 'COUNT', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (54, 19, 'NONE', 0);

-- subqueries for entity EXECUTION --
		
		-- subquery exIsAutoSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (20, 'EXECUTION_ISAUTO_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (94, 20, 'NOT_NULL', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (66, 20, 'NONE', 0);

-- subquery exIssueCountSub --
		
		insert into CHART_QUERY(CHART_QUERY_ID, NAME, STRATEGY, JOIN_STYLE) values (21, 'EXECUTION_ISSUECOUNT_SUBQUERY', 'SUBQUERY', 'LEFT_JOIN');

insert into CHART_MEASURE_COLUMN(CHART_COLUMN_ID, QUERY_ID, MEASURE_OPERATION, MEASURE_RANK) values (75, 21, 'COUNT', 0);

insert into CHART_AXIS_COLUMN(CHART_COLUMN_ID, QUERY_ID, AXIS_OPERATION, AXIS_RANK)  values (66, 21, 'NONE', 0);

-- subqueries for entity ISSUE --
		
		-- subqueries for entity TEST_CASE_STEP --
		
		-- subqueries for entity TEST_CASE_NATURE --
		
		-- subqueries for entity TEST_CASE_TYPE --
		
		-- subqueries for entity REQUIREMENT_VERSION_CATEGORY --
		
		-- subqueries for entity TEST_CASE_MILESTONE --
		
		-- subqueries for entity REQUIREMENT_VERSION_MILESTONE --
		
		-- subqueries for entity ITERATION_TEST_PLAN_ASSIGNED_USER --
		
		-- subqueries for entity AUTOMATED_TEST --
		
		-- subqueries for entity AUTOMATED_EXECUTION_EXTENDER --
		
		
		-- -------------------------------------------
		-- section 3 :  calculated columns
		-- -------------------------------------------
		
		
		-- columns for entity : REQUIREMENT --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(2, 'CALCULATED', TRUE, 'REQUIREMENT_NB_VERSIONS', 'REQUIREMENT', null, 'NUMERIC', 'count(requirementVersionCoverages)', 1);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
		values
			 (2, 'MEASURE'), (2, 'AXIS'), (2, 'FILTER');

-- columns for entity : REQUIREMENT_VERSION --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(17, 'CALCULATED', TRUE, 'REQUIREMENT_VERSION_TCCOUNT', 'REQUIREMENT_VERSION', null, 'NUMERIC', 'count(requirementVersionCoverages)', 2),
			(18, 'CALCULATED', TRUE, 'REQUIREMENT_VERSION_MILCOUNT', 'REQUIREMENT_VERSION', null, 'NUMERIC', 'count(milestones)', 3);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
		values
			 (17, 'MEASURE'), (17, 'AXIS'), (17, 'FILTER'),
			 (18, 'MEASURE'), (18, 'AXIS'), (18, 'FILTER');

-- columns for entity : TEST_CASE --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(30, 'CALCULATED', TRUE, 'TEST_CASE_VERSCOUNT', 'TEST_CASE', null, 'NUMERIC', 'count(requirementVersionCoverages)', 4),
			(31, 'CALCULATED', TRUE, 'TEST_CASE_CALLSTEPCOUNT', 'TEST_CASE', null, 'NUMERIC', 'count(steps[class="CallTestStep"])', 5),
			(32, 'CALCULATED', TRUE, 'TEST_CASE_STEPCOUNT', 'TEST_CASE', null, 'NUMERIC', 'count(steps)', 6),
			(33, 'CALCULATED', TRUE, 'TEST_CASE_MILCOUNT', 'TEST_CASE', null, 'NUMERIC', 'count(milestones)', 7),
			(34, 'CALCULATED', TRUE, 'TEST_CASE_ITERCOUNT', 'TEST_CASE', null, 'NUMERIC', 'count(iterations)', 8),
			(35, 'CALCULATED', TRUE, 'TEST_CASE_EXECOUNT', 'TEST_CASE', null, 'NUMERIC', 'count(executions)', 9),
			(36, 'CALCULATED', TRUE, 'TEST_CASE_HASAUTOSCRIPT', 'TEST_CASE', null, 'BOOLEAN', 'notnull(automatedTest)', 10);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
		values
			 (30, 'MEASURE'), (30, 'AXIS'), (30, 'FILTER'),
			 (31, 'MEASURE'), (31, 'AXIS'), (31, 'FILTER'),
			 (32, 'MEASURE'), (32, 'AXIS'), (32, 'FILTER'),
			 (33, 'MEASURE'), (33, 'AXIS'), (33, 'FILTER'),
			 (34, 'MEASURE'), (34, 'AXIS'), (34, 'FILTER'),
			 (35, 'MEASURE'), (35, 'AXIS'), (35, 'FILTER'),
			 (36, 'MEASURE'), (36, 'AXIS'), (36, 'FILTER');

-- columns for entity : CAMPAIGN --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(44, 'CALCULATED', TRUE, 'CAMPAIGN_ITERCOUNT', 'CAMPAIGN', null, 'NUMERIC', 'count(iterations)', 11),
			(45, 'CALCULATED', TRUE, 'CAMPAIGN_ISSUECOUNT', 'CAMPAIGN', null, 'NUMERIC', 'count(issues)', 12);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
		values
			 (44, 'MEASURE'), (44, 'AXIS'), (44, 'FILTER'),
			 (45, 'MEASURE'), (45, 'AXIS'), (45, 'FILTER');

-- columns for entity : ITERATION --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(52, 'CALCULATED', TRUE, 'ITERATION_ITEMCOUNT', 'ITERATION', null, 'NUMERIC', 'count(testPlans)', 13),
			(53, 'CALCULATED', TRUE, 'ITERATION_ISSUECOUNT', 'ITERATION', null, 'NUMERIC', 'count(issues)', 14);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
		values
			 (52, 'MEASURE'), (52, 'AXIS'), (52, 'FILTER'),
			 (53, 'MEASURE'), (53, 'AXIS'), (53, 'FILTER');

-- columns for entity : ITEM_TEST_PLAN --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(61, 'CALCULATED', TRUE, 'ITEM_TEST_PLAN_TC_DELETED', 'ITEM_TEST_PLAN', null, 'BOOLEAN', 'isnull(referencedTestCase)', 15),
			(62, 'CALCULATED', TRUE, 'ITEM_TEST_PLAN_IS_EXECUTED', 'ITEM_TEST_PLAN', null, 'BOOLEAN', 'notnull(executions)', 16),
			(63, 'CALCULATED', TRUE, 'ITEM_TEST_PLAN_MANEXCOUNT', 'ITEM_TEST_PLAN', null, 'NUMERIC', 'count(executions[auto="false"])', 17),
			(64, 'CALCULATED', TRUE, 'ITEM_TEST_PLAN_AUTOEXCOUNT', 'ITEM_TEST_PLAN', null, 'NUMERIC', 'count(executions[auto="true"])', 18),
			(65, 'CALCULATED', TRUE, 'ITEM_TEST_PLAN_ISSUECOUNT', 'ITEM_TEST_PLAN', null, 'NUMERIC', 'count(issues)', 19);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
		values
			 (61, 'MEASURE'), (61, 'AXIS'), (61, 'FILTER'),
			 (62, 'MEASURE'), (62, 'AXIS'), (62, 'FILTER'),
			 (63, 'MEASURE'), (63, 'AXIS'), (63, 'FILTER'),
			 (64, 'MEASURE'), (64, 'AXIS'), (64, 'FILTER'),
			 (65, 'MEASURE'), (65, 'AXIS'), (65, 'FILTER');

-- columns for entity : EXECUTION --
		
		insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID, COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
		values
			(72, 'CALCULATED', TRUE, 'EXECUTION_ISAUTO', 'EXECUTION', null, 'BOOLEAN', 'notnull(automatedExecutionExtender)', 20),
			(73, 'CALCULATED', TRUE, 'EXECUTION_ISSUECOUNT', 'EXECUTION', null, 'NUMERIC', 'count(issues)', 21);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
		values
			 (72, 'FILTER'),
			 (73, 'MEASURE'), (73, 'AXIS'), (73, 'FILTER');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'populate the column prototype referential data (generated from src/main/script/MakeChartColumns.groovy)', 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.13.0.xml', 'tm-1.13.0-feature-5375-2', '2.0.5', '3:440e9bd08f41c2961d3f5451c56f5dbe', 369);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml::tm-1.13.0-feature-5292-1::jthebault::(Checksum: 3:149c727753bc94c5ad30c43a6ba52ee3)
-- closure table for custom report library nodes. Its content is set and modified by four triggers
CREATE TABLE CUSTOM_REPORT_LIBRARY (CRL_ID bigserial NOT NULL, ATTACHMENT_LIST_ID BIGINT, CONSTRAINT PK_CUSTOM_REPORT_LIBRARY PRIMARY KEY (CRL_ID));

COMMENT ON COLUMN CUSTOM_REPORT_LIBRARY.CRL_ID IS 'pk genere';

COMMENT ON COLUMN CUSTOM_REPORT_LIBRARY.ATTACHMENT_LIST_ID IS 'FK to ATTACHMENT_LIST';

CREATE INDEX IDX_CUSTOM_REPORT_LIBRARY ON CUSTOM_REPORT_LIBRARY(CRL_ID);

CREATE TABLE CUSTOM_REPORT_LIBRARY_NODE (CRLN_ID bigserial NOT NULL, NAME VARCHAR(255), ENTITY_TYPE VARCHAR(50), ENTITY_ID BIGINT, CRL_ID BIGINT, CONSTRAINT PK_CUSTOM_REPORT_LIBRARY_NODE PRIMARY KEY (CRLN_ID), CONSTRAINT fk_crln_custom_report_library_crl_id FOREIGN KEY (CRL_ID) REFERENCES CUSTOM_REPORT_LIBRARY(CRL_ID));

COMMENT ON COLUMN CUSTOM_REPORT_LIBRARY_NODE.CRLN_ID IS 'pk genere';

COMMENT ON COLUMN CUSTOM_REPORT_LIBRARY_NODE.CRL_ID IS 'fk to the containing CUSTOM_REPORT_LIBRARY';

CREATE TABLE CRLN_RELATIONSHIP (ANCESTOR_ID BIGINT, DESCENDANT_ID BIGINT NOT NULL, CONTENT_ORDER BIGINT, CONSTRAINT fk_crln_relationship_descendant FOREIGN KEY (DESCENDANT_ID) REFERENCES CUSTOM_REPORT_LIBRARY_NODE(CRLN_ID), CONSTRAINT fk_crln_relationship_ancestor FOREIGN KEY (ANCESTOR_ID) REFERENCES CUSTOM_REPORT_LIBRARY_NODE(CRLN_ID));

COMMENT ON COLUMN CRLN_RELATIONSHIP.ANCESTOR_ID IS 'cle etrangere vers le noeud parent';

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

COMMENT ON COLUMN CRLN_RELATIONSHIP.CONTENT_ORDER IS 'numéro d''ordre du noeud dans son parent';

CREATE INDEX idx_crln_relationship_descendant ON CRLN_RELATIONSHIP(DESCENDANT_ID);

CREATE INDEX idx_crln_relationship_ancestor ON CRLN_RELATIONSHIP(ANCESTOR_ID);

CREATE TABLE CUSTOM_REPORT_FOLDER (CRF_ID bigserial NOT NULL, NAME VARCHAR(255), DESCRIPTION TEXT, PROJECT_ID BIGINT NOT NULL, CONSTRAINT PK_CRF_ID PRIMARY KEY (CRF_ID), CONSTRAINT fk_project_custom_report_folder_project_id FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID));

COMMENT ON COLUMN CUSTOM_REPORT_FOLDER.CRF_ID IS 'pk genere';

COMMENT ON COLUMN CUSTOM_REPORT_FOLDER.PROJECT_ID IS 'cle etrangere vers le projet.';

CREATE TABLE CUSTOM_REPORT_DASHBOARD (CRD_ID bigserial NOT NULL, NAME VARCHAR(255), PROJECT_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_CRD_ID PRIMARY KEY (CRD_ID), CONSTRAINT fk_project_custom_report_dashboard_project_id FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID));

COMMENT ON COLUMN CUSTOM_REPORT_DASHBOARD.CRD_ID IS 'pk genere';

COMMENT ON COLUMN CUSTOM_REPORT_DASHBOARD.PROJECT_ID IS 'cle etrangere vers le projet.';

CREATE TABLE CUSTOM_REPORT_CHART_BINDING (CRCB_ID bigserial NOT NULL, CRD_ID BIGINT NOT NULL, CHART_ID BIGINT NOT NULL, ROW INT, COL INT, SIZE_X INT, SIZE_Y INT, CONSTRAINT PK_CRCB_ID PRIMARY KEY (CRCB_ID), CONSTRAINT fk_chart_bindind_custom_report_dashboard_crd_id FOREIGN KEY (CRD_ID) REFERENCES CUSTOM_REPORT_DASHBOARD(CRD_ID), CONSTRAINT fk_chart_bindind_chart_definition_chart_id FOREIGN KEY (CHART_ID) REFERENCES CHART_DEFINITION(CHART_ID));

COMMENT ON COLUMN CUSTOM_REPORT_CHART_BINDING.CRCB_ID IS 'pk genere';

COMMENT ON COLUMN CUSTOM_REPORT_CHART_BINDING.CRD_ID IS 'cle etrangere vers le dashboard.';

COMMENT ON COLUMN CUSTOM_REPORT_CHART_BINDING.CHART_ID IS 'cle etrangere vers le chart.';

ALTER TABLE PROJECT ADD CRL_ID BIGINT;

COMMENT ON COLUMN PROJECT.CRL_ID IS 'FK FOR CUSTOM_REPORT_LIBRARY.CRL_ID';

ALTER TABLE ATTACHMENT_LIST ADD TMP_CRL_ID BIGINT;

COMMENT ON COLUMN ATTACHMENT_LIST.TMP_CRL_ID IS 'TMP FK FOR CUSTOM_REPORT_LIBRARY.CRL_ID';

CREATE TABLE CRLN_RELATIONSHIP_CLOSURE (ANCESTOR_ID BIGINT NOT NULL, DESCENDANT_ID BIGINT NOT NULL, DEPTH SMALLINT NOT NULL, CONSTRAINT fk_crln_relationship_clos_anc FOREIGN KEY (ANCESTOR_ID) REFERENCES CUSTOM_REPORT_LIBRARY_NODE(CRLN_ID), CONSTRAINT fk_crln_relationship_clos_desc FOREIGN KEY (DESCENDANT_ID) REFERENCES CUSTOM_REPORT_LIBRARY_NODE(CRLN_ID));

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

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

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

CREATE INDEX idx_crln_relationship_clos_desc ON CRLN_RELATIONSHIP_CLOSURE(DESCENDANT_ID);

CREATE INDEX idx_crln_relationship_clos_anc ON CRLN_RELATIONSHIP_CLOSURE(ANCESTOR_ID);

ALTER TABLE CRLN_RELATIONSHIP_CLOSURE ADD UNIQUE (ANCESTOR_ID, DESCENDANT_ID);

INSERT INTO ACL_CLASS (CLASSNAME) VALUES ('org.squashtest.tm.domain.customreport.CustomReportLibrary');

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.tm.domain.customreport.CustomReportLibrary'), '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.tm.domain.customreport.CustomReportLibrary'), '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.tm.domain.customreport.CustomReportLibrary'), '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.tm.domain.customreport.CustomReportLibrary'), '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.tm.domain.customreport.CustomReportLibrary'), '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.tm.domain.customreport.CustomReportLibrary'), '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.tm.domain.customreport.CustomReportLibrary'), '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.tm.domain.customreport.CustomReportLibrary'), '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.tm.domain.customreport.CustomReportLibrary'), '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.tm.domain.customreport.CustomReportLibrary'), '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.ProjectViewer'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.tm.domain.customreport.CustomReportLibrary'), '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.tm.domain.customreport.CustomReportLibrary'), '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.tm.domain.customreport.CustomReportLibrary'), '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.tm.domain.customreport.CustomReportLibrary'), '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.tm.domain.customreport.CustomReportLibrary'), '1');

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jthebault', 'closure table for custom report library nodes. Its content is set and modified by four triggers', NOW(), 'Create Table, Create Index, Create Table (x2), Create Index (x2), Create Table (x3), Add Column (x2), Create Table, Create Index (x2), Add Unique Constraint, 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.13.0.xml', 'tm-1.13.0-feature-5292-1', '2.0.5', '3:149c727753bc94c5ad30c43a6ba52ee3', 370);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml::tm-1.13.0-feat-5292-2-postgresql::jthebault::(Checksum: 3:1e52ba24506285ab542ce0b4ff18ea21)
-- postgresql-triggers for CRLN_RELATIONSHIP_CLOSURE table
drop trigger if exists ins_crln_after on CUSTOM_REPORT_LIBRARY_NODE;
		drop function if exists ins_crln_after();
		create function ins_crln_after() returns trigger as $ins_crln_after$
		begin
			insert into CRLN_RELATIONSHIP_CLOSURE
			values (new.crln_id, new.crln_id, 0);
			return new;
		end;
		$ins_crln_after$ language plpgsql;

		create trigger ins_crln_after after insert on CUSTOM_REPORT_LIBRARY_NODE
		for each row execute procedure ins_crln_after();

drop trigger if exists del_crln_before on CUSTOM_REPORT_LIBRARY_NODE;
		drop function if exists del_crln_before();
		create function del_crln_before() returns trigger as $del_crln_before$
		begin
			delete from CRLN_RELATIONSHIP_CLOSURE
			where ancestor_id = old.crln_id
			and descendant_id = old.crln_id;
			return old;
		end;
		$del_crln_before$ language plpgsql;

		create trigger del_crln_before before delete on CUSTOM_REPORT_LIBRARY_NODE
		for each row execute procedure del_crln_before();

drop trigger if exists attach_crln_after on CRLN_RELATIONSHIP;
		drop function if exists attach_crln_after();
		create function attach_crln_after() returns trigger as $attach_crln_after$
		begin
			insert into CRLN_RELATIONSHIP_CLOSURE
			select c1.ancestor_id, c2.descendant_id, c1.depth + c2.depth + 1
			from CRLN_RELATIONSHIP_CLOSURE c1
			cross join CRLN_RELATIONSHIP_CLOSURE c2
			where c1.descendant_id = new.ancestor_id
			and c2.ancestor_id = new.descendant_id;
			return new;
		end;
		$attach_crln_after$ language plpgsql;

		create trigger attach_crln_after after insert on CRLN_RELATIONSHIP
		for each row execute procedure attach_crln_after();

drop trigger if exists detach_crln_before on CRLN_RELATIONSHIP;
		drop function if exists detach_crln_before();
		create function detach_crln_before() returns trigger as $detach_crln_before$
		begin
			delete from CRLN_RELATIONSHIP_CLOSURE
			where descendant_id in ( select descendant_id from CRLN_RELATIONSHIP_CLOSURE where ancestor_id = old.descendant_id )
			and ancestor_id not in ( select descendant_id from CRLN_RELATIONSHIP_CLOSURE where ancestor_id = old.descendant_id )
			;
			return old;
		end;
		$detach_crln_before$ language plpgsql;

		create trigger detach_crln_before before delete on CRLN_RELATIONSHIP
		for each row execute procedure detach_crln_before();

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jthebault', 'postgresql-triggers for CRLN_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.13.0.xml', 'tm-1.13.0-feat-5292-2-postgresql', '2.0.5', '3:1e52ba24506285ab542ce0b4ff18ea21', 371);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml::tm-1.13.0-feat-5292-3::jthebault::(Checksum: 3:57e627e737c45ca7b55a4b43a85e3321)
-- Migration for custom report workspace evolution. Mainly create the Library entities and their nodes, attachement list...
insert into CUSTOM_REPORT_LIBRARY (CRL_ID)
		select p.PROJECT_ID
		from PROJECT p;

update PROJECT p
		set CRL_ID = PROJECT_ID;

insert into ACL_OBJECT_IDENTITY (IDENTITY,CLASS_ID)
		  select CRL_ID, ID from CUSTOM_REPORT_LIBRARY,ACL_CLASS
		  where ACL_CLASS.CLASSNAME LIKE 'org.squashtest.tm.domain.customreport.CustomReportLibrary';

insert into ATTACHMENT_LIST (TMP_CRL_ID)
		select CRL_ID
		from CUSTOM_REPORT_LIBRARY crl;

update CUSTOM_REPORT_LIBRARY crl
		set ATTACHMENT_LIST_ID = (
			select ATTACHMENT_LIST_ID
			from ATTACHMENT_LIST al
			where crl.CRL_ID = al.TMP_CRL_ID
	);

insert into CUSTOM_REPORT_LIBRARY_NODE (ENTITY_ID,CRL_ID,ENTITY_TYPE)
		select CRL_ID,CRL_ID,'LIBRARY'
		from CUSTOM_REPORT_LIBRARY;

update CUSTOM_REPORT_LIBRARY_NODE
		set NAME = (
				select p.NAME
				from PROJECT p
				where ENTITY_ID = p.CRL_ID
			)
		where ENTITY_TYPE = 'LIBRARY';

ALTER TABLE ATTACHMENT_LIST DROP COLUMN TMP_CRL_ID;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jthebault', 'Migration for custom report workspace evolution. Mainly create the Library entities and their nodes, attachement list...', NOW(), 'Custom SQL (x4), Drop Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml', 'tm-1.13.0-feat-5292-3', '2.0.5', '3:57e627e737c45ca7b55a4b43a85e3321', 372);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml::tm-1.13.0-feature-5292-4::bsiri::(Checksum: 3:f1cdcaaaa75c07b16f3b864ed3a28527)
-- set the foreign key constraints from project to custom report library, and from custom report_library to attachment_list
ALTER TABLE PROJECT ADD CONSTRAINT fk_project_crl FOREIGN KEY (CRL_ID) REFERENCES CUSTOM_REPORT_LIBRARY (CRL_ID);

ALTER TABLE CUSTOM_REPORT_LIBRARY ADD CONSTRAINT fk_crl_attachment_list FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST (ATTACHMENT_LIST_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'set the foreign key constraints from project to custom report library, and from custom report_library to attachment_list', NOW(), 'Add Foreign Key Constraint (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml', 'tm-1.13.0-feature-5292-4', '2.0.5', '3:f1cdcaaaa75c07b16f3b864ed3a28527', 373);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml::tm-1.13.0-feature-5292-5::jthebault::(Checksum: 3:be5321cb8f4ae12a1789ba66eab429d6)
-- update the sequences afterward
SELECT setval('custom_report_library_crl_id_seq', (SELECT MAX(CRL_ID) from CUSTOM_REPORT_LIBRARY));

SELECT setval('custom_report_library_node_crln_id_seq', (SELECT MAX(CRLN_ID) from CUSTOM_REPORT_LIBRARY_NODE));

SELECT setval('acl_object_identity_id_seq', (SELECT MAX(ID) from ACL_OBJECT_IDENTITY));

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jthebault', 'update the sequences afterward', 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.13.0.xml', 'tm-1.13.0-feature-5292-5', '2.0.5', '3:be5321cb8f4ae12a1789ba66eab429d6', 374);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml::tm-1.13.0-feature-5375-3::jsimon, bsiri::(Checksum: 3:6c40497eb5af9c904d9ed7b59abd9994)
-- update the sequences afterward
SELECT setval('chart_column_prototype_chart_column_id_seq', (SELECT MAX(CHART_COLUMN_ID) from CHART_COLUMN_PROTOTYPE));

SELECT setval('chart_query_chart_query_id_seq', (SELECT MAX(CHART_QUERY_ID) from CHART_QUERY));

SELECT setval('chart_filter_filter_id_seq', (SELECT MAX(FILTER_ID) from CHART_FILTER));

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jsimon, bsiri', 'update the sequences afterward', 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.13.0.xml', 'tm-1.13.0-feature-5375-3', '2.0.5', '3:6c40497eb5af9c904d9ed7b59abd9994', 375);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml::tm-1.13.0-feature-5375-4::jsimon::(Checksum: 3:0f35f24cf83abba62a3a6934dd715fe2)
CREATE TABLE CHART_PROJECT_SCOPE (CHART_PROJECT_SCOPE_ID bigserial NOT NULL, CHART_ID BIGINT NOT NULL, PROJECT_SCOPE VARCHAR(15), CONSTRAINT PK_CHART_PROJECT_SCOPE PRIMARY KEY (CHART_PROJECT_SCOPE_ID), CONSTRAINT fk_chart_project_scope_chart_definition FOREIGN KEY (CHART_ID) REFERENCES CHART_DEFINITION(CHART_ID));

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jsimon', '', 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.13.0.xml', 'tm-1.13.0-feature-5375-4', '2.0.5', '3:0f35f24cf83abba62a3a6934dd715fe2', 376);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml::tm-1.13.0-feature-5265-1::bsiri::(Checksum: 3:00802e64f31ea118f27a9aa0478ce606)
-- This table holds the optional extension for requirement, that flags them as synchronized requirement
CREATE TABLE REQUIREMENT_SYNC_EXTENDER (REQ_SYNC_ID bigserial NOT NULL, REQUIREMENT_ID BIGINT NOT NULL, BUGTRACKER_ID BIGINT NOT NULL, REMOTE_REQ_ID VARCHAR(15), REMOTE_PROJECT_ID VARCHAR(15), REMOTE_REQ_URL VARCHAR(300), LAST_SYNCHRONIZED_BY VARCHAR(50) DEFAULT null, CONSTRAINT PK_REQUIREMENT_SYNC_EXTENDER PRIMARY KEY (REQ_SYNC_ID), CONSTRAINT fk_sync_extender_bugtracker FOREIGN KEY (BUGTRACKER_ID) REFERENCES BUGTRACKER(BUGTRACKER_ID), CONSTRAINT fk_sync_extender_requirement FOREIGN KEY (REQUIREMENT_ID) REFERENCES REQUIREMENT(RLN_ID));

COMMENT ON COLUMN REQUIREMENT_SYNC_EXTENDER.REQ_SYNC_ID IS 'autogenerated key';

COMMENT ON COLUMN REQUIREMENT_SYNC_EXTENDER.REQUIREMENT_ID IS 'foreign key to the (now synchronized) requirement';

COMMENT ON COLUMN REQUIREMENT_SYNC_EXTENDER.BUGTRACKER_ID IS 'foreign key to the source bugtracker';

COMMENT ON COLUMN REQUIREMENT_SYNC_EXTENDER.REMOTE_REQ_ID IS 'ID of the remote requirement';

COMMENT ON COLUMN REQUIREMENT_SYNC_EXTENDER.REMOTE_PROJECT_ID IS 'ID of the remote project that hosts the remote requirement';

COMMENT ON COLUMN REQUIREMENT_SYNC_EXTENDER.REMOTE_REQ_URL IS 'the url of the remote requirement';

COMMENT ON COLUMN REQUIREMENT_SYNC_EXTENDER.LAST_SYNCHRONIZED_BY IS 'the last used that synchronized that requirement with the remote manager';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'This table holds the optional extension for requirement, that flags them as synchronized requirement', 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.13.0.xml', 'tm-1.13.0-feature-5265-1', '2.0.5', '3:00802e64f31ea118f27a9aa0478ce606', 377);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.0.xml::tm-1.13.0-feature-5265-2::bsiri::(Checksum: 3:2665f041c2959aebc332280a2806f725)
-- adding a column recording how a requirement should be managed
ALTER TABLE REQUIREMENT ADD MODE VARCHAR(15) DEFAULT 'NATIVE';

COMMENT ON COLUMN REQUIREMENT.MODE IS 'Says whether a requirement is NATIVE or SYNCHRONIZED';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'adding a column recording how a requirement should be managed', 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.13.0.xml', 'tm-1.13.0-feature-5265-2', '2.0.5', '3:2665f041c2959aebc332280a2806f725', 378);

-- Release Database Lock
-- Release Database Lock
