hystory_mysql.txt

Insert in this file the history of DB Schema, only for MySQL
Every items must have:
- The Developer who has made the change
- The SQL script that updates the schema ( schema, index and FK )
- The Date

###################################################################################################
- DBMS changes After 2.0
- Angelo
- 27/02/2009

ALTER TABLE `spagobi`.`sbi_audit` MODIFY COLUMN `DOC_PARAMETERS` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL;

ALTER TABLE `spagobi`.`sbi_viewpoints` MODIFY COLUMN `VP_VALUE_PARAMS` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL;

ALTER TABLE `spagobi`.`sbi_remember_me` MODIFY COLUMN `PARAMETERS` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL;

ALTER TABLE `spagobi`.`sbi_data_set` MODIFY COLUMN `NUM_ROWS` BOOLEAN DEFAULT FALSE;

ALTER TABLE `spagobi`.`sbi_menu` MODIFY COLUMN `BIOBJ_PARAMETERS` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

ALTER TABLE `spagobi`.`sbi_kpi` ADD COLUMN `kpi_type` INTEGER AFTER `flg_is_father`,
 ADD COLUMN `metric_scale_type` INTEGER AFTER `kpi_type`,
 ADD COLUMN `measure_type` INTEGER AFTER `metric_scale_type`,
 ADD COLUMN `interpretation` VARCHAR(1000) AFTER `measure_type`,
 ADD COLUMN `input_attributes` VARCHAR(1000) AFTER `interpretation`,
 ADD COLUMN `model_reference` VARCHAR(255) AFTER `input_attributes`,
 ADD COLUMN `target_audience` VARCHAR(1000) AFTER `model_reference`;


ALTER TABLE `spagobi`.`sbi_kpi_inst_period` CHANGE COLUMN `DEFAULT` `DEFAULT_VALUE` BOOLEAN DEFAULT NULL;

ALTER TABLE `spagobi`.`sbi_kpi_model_inst` ADD COLUMN `label` VARCHAR(100) BINARY NOT NULL AFTER `description`,
 ADD COLUMN `start_date` DATETIME AFTER `label`,
 ADD COLUMN `end_date` DATETIME AFTER `start_date`;
 
 
ALTER TABLE `spagobi`.`sbi_alarm_event` ADD COLUMN `KPI_DESCRIPTION` VARCHAR(100) AFTER `RESOURCES`,
 ADD COLUMN `RESOURCE_ID` INTEGER UNSIGNED AFTER `KPI_DESCRIPTION`,
 ADD COLUMN `KPI_INSTANCE_ID` INTEGER UNSIGNED AFTER `RESOURCE_ID`;
 
 
 CREATE UNIQUE INDEX XAK2SBI_KPI_MODEL_INST ON SBI_KPI_MODEL_INST
(
       LABEL						ASC
);

Alter table `SBI_KPI_INSTANCE` add Foreign Key (`KPI_ID`) references `SBI_KPI` (`KPI_ID`) on delete  restrict on update  restrict;

Alter table `SBI_KPI` add Foreign Key (`kpi_type`) references `SBI_DOMAINS` (`VALUE_ID`) on delete  restrict on update  restrict;
Alter table `SBI_KPI` add Foreign Key (`metric_scale_type`) references `SBI_DOMAINS` (`VALUE_ID`) on delete  restrict on update  restrict;
Alter table `SBI_KPI` add Foreign Key (`measure_type`) references `SBI_DOMAINS` (`VALUE_ID`) on delete  restrict on update  restrict;
 
ALTER TABLE `spagobi`.`sbi_data_set` ADD COLUMN `LANGUAGE_SCRIPT` VARCHAR(50) AFTER `num_rows`; 

- gigavard
- 12/03/2009
ALTER TABLE `spagobi`.`sbi_domains` MODIFY COLUMN `VALUE_CD` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;


- gigavard
- 13/03/2009
ALTER TABLE `spagobi`.`sbi_functions` MODIFY COLUMN `CODE` VARCHAR(40) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL; 

###################################################################################################
- DBMS changes After 2.1
- gigavard
- 20/04/2009
ALTER TABLE SBI_DATA_SOURCE MODIFY COLUMN `URL_CONNECTION` VARCHAR(500) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL;
 
 
 - gigavard
 - 29/04/2009
 
 ALTER TABLE `spagobi`.`SBI_ALARM` ADD UNIQUE INDEX `label_unique`(`LABEL`);

ALTER TABLE `spagobi`.`SBI_ALARM_CONTACT` ADD UNIQUE INDEX `name_unique`(`NAME`);

ALTER TABLE `spagobi`.`SBI_KPI` ADD UNIQUE INDEX `code_unique`(`code`);

ALTER TABLE `spagobi`.`SBI_KPI_MODEL` ADD UNIQUE INDEX `unique_kpi_model_cd`(`KPI_MODEL_CD`);

ALTER TABLE `spagobi`.`SBI_KPI_PERIODICITY` ADD UNIQUE INDEX `name_unique`(`name`);

ALTER TABLE `spagobi`.`SBI_THRESHOLD` ADD UNIQUE INDEX `code_unique`(`code`);

ALTER TABLE `spagobi`.`SBI_THRESHOLD_VALUE` ADD UNIQUE INDEX `label_thId_unique`(`label`, `THRESHOLD_ID`);

ALTER TABLE `spagobi`.`SBI_RESOURCES` ADD UNIQUE INDEX `unique_resource_name`(`RESOURCE_NAME`);



ALTER TABLE `spagobi`.`SBI_KPI_MODEL` MODIFY COLUMN `KPI_MODEL_CD` VARCHAR(40) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

ALTER TABLE `spagobi`.`SBI_ALARM` MODIFY COLUMN `LABEL` VARCHAR(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

ALTER TABLE `spagobi`.`SBI_KPI_PERIODICITY` MODIFY COLUMN `name` VARCHAR(400) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

ALTER TABLE `spagobi`.`SBI_RESOURCES` MODIFY COLUMN `RESOURCE_NAME` VARCHAR(40) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

ALTER TABLE `spagobi`.`SBI_THRESHOLD` MODIFY COLUMN `code` VARCHAR(45) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

ALTER TABLE `spagobi`.`SBI_THRESHOLD_VALUE` MODIFY COLUMN `label` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
 
 
 - gigavard
 - 15/06/2009
 
 CREATE TABLE SBI_EXPORTERS (
  ENGINE_ID INTEGER NOT NULL,
  DOMAIN_ID INTEGER NOT NULL,
  DEFAULT_VALUE BOOLEAN,
  PRIMARY KEY (ENGINE_ID, DOMAIN_ID)) type = InnoDB;

CREATE UNIQUE INDEX XAK1SBI_EXPORTERS ON SBI_EXPORTERS
(
       ENGINE_ID                         ASC,
       DOMAIN_ID                          ASC
);

ALTER TABLE SBI_EXPORTERS ADD CONSTRAINT FK_sbi_exporters_domain FOREIGN KEY FK_sbi_exporters_domain ( DOMAIN_ID ) REFERENCES SBI_DOMAINS ( VALUE_ID ) ON DELETE RESTRICT;
ALTER TABLE SBI_EXPORTERS ADD CONSTRAINT FK_sbi_exporters_engine FOREIGN KEY FK_sbi_exporters_engine ( ENGINE_ID ) REFERENCES SBI_ENGINES ( ENGINE_ID ) ON DELETE RESTRICT;

###################################################################################################
- DBMS changes After 2.2

- angelo
-31/07/09
ALTER TABLE SBI_DATA_SOURCE ADD COLUMN MULTI_SCHEMA TINYINT(1) NOT NULL DEFAULT '0';
ALTER TABLE SBI_DATA_SOURCE ADD COLUMN ATTR_SCHEMA VARCHAR(45) DEFAULT NULL;

- davide
-07/09/09
ALTER TABLE SBI_EXT_ROLES ADD COLUMN BUILD_QBE_QUERY BOOLEAN DEFAULT TRUE;

-chiara
-09/09/2009
ALTER TABLE SBI_KPI_VALUE ADD COLUMN XML_DATA TEXT;

###################################################################################################
- DBMS changes After 2.3

-giulio
-05/11/2009
ALTER TABLE SBI_DATA_SET ADD COLUMN DS_METADATA VARCHAR(2000) DEFAULT NULL;

-antonella 
-12/11/2009
ALTER TABLE SBI_OBJECT_NOTES ADD COLUMN OWNER VARCHAR(50);
ALTER TABLE SBI_OBJECT_NOTES ADD COLUMN ISPUBLIC BOOLEAN;
ALTER TABLE SBI_OBJECT_NOTES ADD COLUMN CREATION_DATE TIMESTAMP NOT NULL;
ALTER TABLE SBI_OBJECT_NOTES ADD COLUMN LAST_CHANGE_DATE TIMESTAMP NOT NULL; 

/* force a valid value for date fields in existing records: */
UPDATE SBI_OBJECT_NOTES SET LAST_CHANGE_DATE = NOW(),CREATION_DATE = NOW();

/* force a valid value for owner field in existing records: 
***************************** ATTENTION **********************************
* The OWNER value depends from your context... 
we suggest 'biadmin' because is the classic admin user in SpaogoBI demo: 
you should change this value with a valid user in your platfrom, in this way 
he may change or delete the EXISTING notes!!*/
UPDATE SBI_OBJECT_NOTES SET OWNER = 'biadmin';
/*************************************************************************/
COMMIT;

-19/11/2009
DELETE FROM SBI_REMEMBER_ME WHERE SUBOBJ_ID IN (SELECT SUBOBJ_ID FROM SBI_SUBOBJECTS WHERE NAME IS NULL OR NAME ='');
DELETE FROM SBI_AUDIT WHERE SUBOBJ_ID IN (SELECT SUBOBJ_ID FROM SBI_SUBOBJECTS WHERE NAME IS NULL OR NAME ='');
DELETE FROM SBI_MENU WHERE SUBOBJ_NAME IN (SELECT NAME FROM SBI_SUBOBJECTS WHERE NAME IS NULL OR NAME ='');
DELETE FROM SBI_BINARY_CONTENTS WHERE BIN_ID IN (SELECT BIN_ID FROM SBI_SUBOBJECTS WHERE NAME IS NULL OR NAME ='');
DELETE FROM SBI_SUBOBJECTS WHERE NAME IS NULL OR NAME =''; 
COMMIT;
ALTER TABLE SBI_SUBOBJECTS MODIFY COLUMN NAME VARCHAR(50) NOT NULL;

-24/11/2009 
ALTER TABLE SBI_OBJECTS DROP COLUMN DESCR_EXT;
ALTER TABLE SBI_OBJECTS DROP COLUMN OBJECTIVE;
ALTER TABLE SBI_OBJECTS DROP COLUMN LANGUAGE;
ALTER TABLE SBI_OBJECTS DROP COLUMN KEYWORDS;

ALTER TABLE SBI_EXT_ROLES ADD COLUMN SAVE_METADATA BOOLEAN DEFAULT TRUE;

CREATE TABLE SBI_OBJ_METADATA (
	OBJ_META_ID 		INTEGER NOT NULL AUTO_INCREMENT,
    LABEL	 	        VARCHAR(20) NOT NULL,
    NAME 	            VARCHAR(40) NOT NULL,
    DESCRIPTION	        VARCHAR(100),  
    DATA_TYPE_ID			INTEGER NOT NULL,
    CREATION_DATE 	    TIMESTAMP NOT NULL,    
    PRIMARY KEY (OBJ_META_ID)
)TYPE=INNODB;

CREATE UNIQUE INDEX XAK1SBI_OBJ_METADATA ON SBI_OBJ_METADATA
(
       OBJ_META_ID                          ASC
);
ALTER TABLE SBI_OBJ_METADATA ADD CONSTRAINT FK_SBI_OBJ_METADATA_1 FOREIGN KEY FK_SBI_OBJ_METADATA_1 ( DATA_TYPE_ID ) REFERENCES SBI_DOMAINS(VALUE_ID);

CREATE TABLE SBI_OBJ_METACONTENTS (
  OBJ_METACONTENT_ID INTEGER  NOT NULL AUTO_INCREMENT,
  OBJMETA_ID 		 INTEGER  NOT NULL ,
  BIOBJ_ID 			 INTEGER  NOT NULL,
  SUBOBJ_ID 		 INTEGER,
  BIN_ID 			 INTEGER,
  CREATION_DATE 	 TIMESTAMP NOT NULL,   
  LAST_CHANGE_DATE   TIMESTAMP NOT NULL,   
    PRIMARY KEY (OBJ_METACONTENT_ID)
)ENGINE=INNODB;


CREATE UNIQUE INDEX XAK1SBI_OBJ_METACONTENTS ON SBI_OBJ_METACONTENTS
(
        OBJMETA_ID                          ASC,
        BIOBJ_ID                            ASC,
        SUBOBJ_ID                           ASC
);

ALTER TABLE SBI_OBJ_METACONTENTS ADD CONSTRAINT FK_SBI_OBJ_METACONTENTS_1 FOREIGN KEY FK_SBI_OBJ_METACONTENTS_1 ( OBJMETA_ID ) REFERENCES SBI_OBJ_METADATA (  OBJ_META_ID );
ALTER TABLE SBI_OBJ_METACONTENTS ADD CONSTRAINT FK_SBI_OBJ_METACONTENTS_2 FOREIGN KEY FK_SBI_OBJ_METACONTENTS_2 ( BIOBJ_ID )   REFERENCES SBI_OBJECTS (  BIOBJ_ID );
ALTER TABLE SBI_OBJ_METACONTENTS ADD CONSTRAINT FK_SBI_OBJ_METACONTENTS_3 FOREIGN KEY FK_SBI_OBJ_METACONTENTS_3 ( SUBOBJ_ID )  REFERENCES SBI_SUBOBJECTS (  SUBOBJ_ID ) ;
ALTER TABLE SBI_OBJ_METACONTENTS ADD CONSTRAINT FK_SBI_OBJ_METACONTENTS_4 FOREIGN KEY FK_SBI_OBJ_METACONTENTS_4 ( BIN_ID )     REFERENCES SBI_BINARY_CONTENTS(BIN_ID);

--adds new funcionality for metadata management
INSERT INTO SBI_USER_FUNC (NAME, DESCRIPTION) VALUES ('ObjMetadataManagement', 'ObjMetadataManagement');
INSERT INTO  SBI_ROLE_TYPE_USER_FUNC values((SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'ROLE_TYPE' AND VALUE_CD = 'ADMIN'), (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME='ObjMetadataManagement'))
COMMIT;

-chiara
ALTER TABLE SBI_SUBOBJECTS MODIFY COLUMN DESCRIPTION VARCHAR(1000) DEFAULT NULL;

- DBMS changes After 2.4
-- Nicola 21/12/2009

/* Modifies to add Model Label */
ALTER TABLE SBI_KPI_MODEL ADD COLUMN KPI_MODEL_LBL VARCHAR(100) NOT NULL AFTER KPI_MODEL_DESC;
UPDATE SBI_KPI_MODEL SET KPI_MODEL_LBL = KPI_MODEL_CD;
ALTER TABLE SBI_KPI_MODEL DROP INDEX KPI_MODEL_CD;
ALTER TABLE SBI_KPI_MODEL ADD UNIQUE INDEX UNIQUE_LABEL(KPI_MODEL_LBL);
ALTER TABLE SBI_KPI_MODEL ADD UNIQUE INDEX UNIQUE_PAR_ID_CD(KPI_PARENT_MODEL_ID, KPI_MODEL_CD);

/* Modifies SBI_THRESHOLD_VALUE TABLE to add the selection to specify if the range is open or closed */
ALTER TABLE SBI_THRESHOLD_VALUE ADD COLUMN min_closed BOOLEAN ,
 ADD COLUMN max_closed BOOLEAN ,
 ADD COLUMN th_value DOUBLE ;

/* Modifies SBI_KPI_MODEL_INST to specify a KPI INSTANCE or a MODEL INSTANCE LABEL */
ALTER TABLE SBI_KPI_MODEL_INST ADD COLUMN modelUUID VARCHAR(400);  


-- Giulio 4/1/2010
ALTER TABLE sbi_lov MODIFY COLUMN LOV_PROVIDER VARCHAR TEXT DEFAULT NULL;

--Monica 11/01/2010
CREATE TABLE SBI_USER (
	USER_ID CHAR(100) NOT NULL,
	PASSWORD VARCHAR(150),
	FULL_NAME VARCHAR(255),
	DT_PWD_BEGIN DATETIME,
	DT_PWD_END DATETIME, 
	FLG_PWD_BLOCKED BOOLEAN,
	DT_LAST_ACCESS DATETIME,
	ID INT NOT NULL,
 PRIMARY KEY (ID));

CREATE TABLE SBI_ATTRIBUTE (
	ATTRIBUTE_NAME VARCHAR(255) NOT NULL,
	DESCRIPTION VARCHAR(500) NOT NULL,
	ATTRIBUTE_ID INT NOT NULL,
 PRIMARY KEY (ATTRIBUTE_ID));

CREATE TABLE SBI_USER_ATTRIBUTES (
	ID INT NOT NULL,
	ATTRIBUTE_ID INT NOT NULL,
	ATTRIBUTE_VALUE VARCHAR(500),
 PRIMARY KEY (ID,ATTRIBUTE_ID));


CREATE TABLE SBI_EXT_USER_ROLES (
	ID INT NOT NULL,
	EXT_ROLE_ID INT NOT NULL,
 PRIMARY KEY (ID,EXT_ROLE_ID));


ALTER TABLE SBI_USER_ATTRIBUTES ADD FOREIGN KEY (ID) REFERENCES SBI_USER (ID) ON DELETE  RESTRICT ON UPDATE  RESTRICT;
ALTER TABLE SBI_EXT_USER_ROLES ADD FOREIGN KEY (ID) REFERENCES SBI_USER (ID) ON DELETE  RESTRICT ON UPDATE  RESTRICT;
ALTER TABLE SBI_USER_ATTRIBUTES ADD FOREIGN KEY (ATTRIBUTE_ID) REFERENCES SBI_ATTRIBUTE (ATTRIBUTE_ID) ON DELETE  RESTRICT ON UPDATE  RESTRICT;
ALTER TABLE SBI_EXT_USER_ROLES ADD FOREIGN KEY (EXT_ROLE_ID) REFERENCES SBI_EXT_ROLES (EXT_ROLE_ID) ON DELETE  RESTRICT ON UPDATE  RESTRICT;

--Antonella 20/01/2010
/** configuration table*/
CREATE TABLE SBI_CONFIG (
	ID 				INTEGER  NOT NULL AUTO_INCREMENT,
	LABEL			VARCHAR(100) NOT NULL,
	NAME			VARCHAR(100) NULL,
	DESCRIPTION 	VARCHAR(500) NULL,
	IS_ACTIVE 		BOOLEAN DEFAULT TRUE,
	VALUE_CHECK 	VARCHAR(1000) NULL,
	VALUE_TYPE_ID 	INTEGER NULL,    
 PRIMARY KEY (ID));
 
 
CREATE UNIQUE INDEX XAK1SBI_CONFIG ON SBI_CONFIG
(
       LABEL                          ASC
);


ALTER TABLE SBI_CONFIG ADD CONSTRAINT FK_sbi_config_1 FOREIGN KEY FK_sbi_config_1 ( VALUE_TYPE_ID ) REFERENCES SBI_DOMAINS ( VALUE_ID ) ON DELETE RESTRICT;


--Antonella 27/01/2010
/** change creation date datatype (date --> timestamp) */
ALTER TABLE SBI_VIEWPOINTS MODIFY COLUMN VP_CREATION_DATE TIMESTAMP NOT NULL;


--Chiara 03/02/2010
CREATE TABLE SBI_ACTIVITY_MONITORING (
  ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  ACTION_TIME TIMESTAMP,
  USERNAME 	 	VARCHAR(40) NOT NULL,
  USERGROUP		VARCHAR(400),
  LOG_LEVEL 	VARCHAR(10) ,
  ACTION_CODE 	VARCHAR(45) NOT NULL,
  INFO 			VARCHAR(400),
  PRIMARY KEY (ID)
)ENGINE=INNODB;

CREATE UNIQUE INDEX XAK1SBI_USER ON SBI_USER
(
       USER_ID                          ASC
);

ALTER TABLE SBI_ALARM MODIFY COLUMN ID_KPI_INSTANCE INTEGER,
 MODIFY COLUMN ID_THRESHOLD_VALUE INTEGER;
 
-- ##################################################################################################
-- 	From 2.5
-- ##################################################################################################

 --Chiara 27/05/2010
ALTER TABLE SBI_RESOURCES ADD COLUMN RESOURCE_CODE VARCHAR(45);
UPDATE SBI_RESOURCES SET RESOURCE_CODE = RESOURCE_NAME;
ALTER TABLE SBI_RESOURCES ADD UNIQUE INDEX UNIQUE_RES_CODE (RESOURCE_CODE);
ALTER TABLE SBI_RESOURCES MODIFY COLUMN RESOURCE_CODE VARCHAR(45) NOT NULL;

--Chiara 28/08/2010
Create table `SBI_KPI_DOCUMENTS` (
	`ID_KPI_DOC` Int NOT NULL AUTO_INCREMENT,
	`BIOBJ_ID` Int NOT NULL,
	`KPI_ID` Int NOT NULL,
 Primary Key (`ID_KPI_DOC`)) ENGINE = InnoDB;
 
Alter table `SBI_KPI_DOCUMENTS` add Foreign Key (`BIOBJ_ID`) references `SBI_OBJECTS` (`BIOBJ_ID`) on delete  restrict on update  restrict;
Alter table `SBI_KPI_DOCUMENTS` add Foreign Key (`KPI_ID`) references `SBI_KPI` (`KPI_ID`) on delete  restrict on update  restrict;

INSERT INTO SBI_KPI_DOCUMENTS(KPI_ID,BIOBJ_ID)
SELECT k.KPI_ID, o.BIOBJ_ID
FROM sbi_kpi k,sbi_Objects o
WHERE
k.DOCUMENT_LABEL = o.LABEL
and k.DOCUMENT_LABEL IS NOT NULL;

ALTER TABLE SBI_KPI DROP COLUMN document_label;

--Antonella 08/09/2010: generic user defined property management
CREATE TABLE SBI_UDP (
	UDP_ID	        INTEGER  NOT NULL AUTO_INCREMENT,
	TYPE_ID			INTEGER NOT NULL,
	FAMILY_ID		INTEGER NOT NULL,
	LABEL           VARCHAR(20) NOT NULL,
	NAME            VARCHAR(40) NOT NULL,
	DESCRIPTION     VARCHAR(1000) NULL,
	IS_MULTIVALUE   BOOLEAN DEFAULT FALSE,    
 PRIMARY KEY (UDP_ID));
 
 
CREATE UNIQUE INDEX XAK1SBI_UDP ON SBI_UDP
(
       LABEL                          ASC
);

CREATE INDEX XIF3_SBI_SBI_UDP ON SBI_UDP
(
       TYPE_ID                  ASC
);

CREATE INDEX XIF2SBI_SBI_UDP ON SBI_UDP
(
       FAMILY_ID                  ASC
);

ALTER TABLE SBI_UDP ADD CONSTRAINT FK_SBI_SBI_UDP_1 FOREIGN KEY FK_SBI_UDP_1 ( TYPE_ID ) REFERENCES SBI_DOMAINS ( VALUE_ID ) ON DELETE RESTRICT;
ALTER TABLE SBI_UDP ADD CONSTRAINT FK_SBI_SBI_UDP_2 FOREIGN KEY FK_SBI_UDP_2 ( FAMILY_ID ) REFERENCES SBI_DOMAINS ( VALUE_ID ) ON DELETE RESTRICT;


CREATE TABLE SBI_UDP_VALUE (
	UDP_VALUE_ID       INTEGER  NOT NULL AUTO_INCREMENT,
	UDP_ID			   INTEGER NOT NULL,
	VALUE              VARCHAR(1000) NOT NULL,
	PROG               INTEGER NULL,
	LABEL              VARCHAR(20) NOT NULL,
	NAME               VARCHAR(40) NULL,
	FAMILY			   VARCHAR(40) NULL,
    BEGIN_TS           TIMESTAMP NOT NULL,
    END_TS             TIMESTAMP NULL,
    REFERENCE_ID	   INTEGER NULL,	
 PRIMARY KEY (UDP_VALUE_ID));
 
CREATE INDEX XIF2SBI_SBI_UDP_VALUE ON SBI_UDP_VALUE
(
       UDP_ID            ASC
);

ALTER TABLE SBI_UDP_VALUE ADD CONSTRAINT FK_SBI_UDP_VALUE_2 FOREIGN KEY FK_SBI_UDP_VALUE_2 ( UDP_ID ) REFERENCES SBI_UDP ( UDP_ID ) ON DELETE RESTRICT;

--adds new funcionality for udp management
INSERT INTO SBI_USER_FUNC (NAME, DESCRIPTION) VALUES ('UserDefinedPropertyManagement', 'UserDefinedPropertyManagement');
COMMIT;
INSERT INTO  SBI_ROLE_TYPE_USER_FUNC values((SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'ROLE_TYPE' AND VALUE_CD = 'ADMIN'), (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME='UserDefinedPropertyManagement'))
COMMIT;
--Monica : KPI RELATIONS 
CREATE TABLE SBI_KPI_REL (
  KPI_REL_ID INT(11) NOT NULL AUTO_INCREMENT,
  KPI_FATHER_ID INT(11)  NOT NULL,
  KPI_CHILD_ID INT(11)  NOT NULL,
  PARAMETER VARCHAR(100),
  PRIMARY KEY (KPI_REL_ID)
)ENGINE=INNODB;

ALTER TABLE SBI_KPI_REL ADD CONSTRAINT FK_SBI_KPI_REL_3 FOREIGN KEY FK_SBI_KPI_REL_3 ( KPI_FATHER_ID ) REFERENCES SBI_KPI ( KPI_ID ) ON DELETE RESTRICT;
ALTER TABLE SBI_KPI_REL ADD CONSTRAINT FK_SBI_KPI_REL_4 FOREIGN KEY FK_SBI_KPI_REL_4 ( KPI_CHILD_ID ) REFERENCES SBI_KPI ( KPI_ID ) ON DELETE RESTRICT;


-- ggavardi 28/09/2010
 CREATE TABLE SBI_KPI_ERROR (
  KPI_ERROR_ID INTEGER NOT NULL AUTO_INCREMENT,
  KPI_MODEL_INST_ID INTEGER NOT NULL,
  USER_MSG VARCHAR(1000),
  FULL_MSG TEXT,
  TS_DATE TIMESTAMP ,
  LABEL_MOD_INST VARCHAR(100) ,
  PARAMETERS VARCHAR(1000),
  PRIMARY KEY (KPI_ERROR_ID)
);
ALTER TABLE SBI_KPI_ERROR ADD CONSTRAINT FK_SBI_KPI_ERROR_MODEL_1 FOREIGN KEY FK_SBI_KPI_ERROR_MODEL_1 ( KPI_MODEL_INST_ID ) REFERENCES SBI_KPI_MODEL_INST ( KPI_MODEL_INST ) ON DELETE RESTRICT;

--Chiara 28/09/2010
DROP TABLE SBI_KPI_MODEL_ATTR_VAL;
DROP TABLE SBI_KPI_MODEL_ATTR;

--new column on SBI_KPI
ALTER TABLE SBI_KPI ADD COLUMN IS_ADDITIVE CHAR(1);

--Davide 29/09/2010 (Organizationl Unit)
CREATE TABLE SBI_ORG_UNIT (
  ID            INTEGER NOT NULL,
  LABEL            VARCHAR(200) NOT NULL,
  NAME             VARCHAR(400) NOT NULL,
  DESCRIPTION      VARCHAR(1000),
  UNIQUE (LABEL),
  PRIMARY KEY(ID)
)ENGINE=INNODB;

CREATE TABLE SBI_ORG_UNIT_HIERARCHIES (
  ID            INTEGER NOT NULL,
  LABEL            VARCHAR(100) NOT NULL,
  NAME             VARCHAR(200) NOT NULL,
  DESCRIPTION      VARCHAR(1000),
  TARGET     VARCHAR(1000),
  COMPANY    VARCHAR(100) NOT NULL,
  UNIQUE (LABEL, COMPANY),
  PRIMARY KEY(ID)
)ENGINE=INNODB;

CREATE TABLE SBI_ORG_UNIT_NODES (
  NODE_ID            INTEGER NOT NULL,
  OU_ID           INTEGER NOT NULL,
  HIERARCHY_ID  INTEGER NOT NULL,
  PARENT_NODE_ID INTEGER NULL,
  PATH VARCHAR(4000) NOT NULL,
  PRIMARY KEY(NODE_ID)
)ENGINE=INNODB;

CREATE TABLE SBI_ORG_UNIT_GRANT (
  ID INTEGER NOT NULL,
  HIERARCHY_ID  INTEGER NOT NULL,
  KPI_MODEL_INST_NODE_ID INTEGER NOT NULL,
  START_DATE  DATE,
  END_DATE  DATE,
  LABEL            VARCHAR(200) NOT NULL,
  NAME             VARCHAR(400) NOT NULL,
  DESCRIPTION      VARCHAR(1000),
  UNIQUE (LABEL),
  PRIMARY KEY(ID)
)ENGINE=INNODB;

CREATE TABLE SBI_ORG_UNIT_GRANT_NODES (
  NODE_ID INTEGER NOT NULL,
  KPI_MODEL_INST_NODE_ID INTEGER NOT NULL,
  GRANT_ID INTEGER NOT NULL,
  PRIMARY KEY(NODE_ID, KPI_MODEL_INST_NODE_ID, GRANT_ID)
)ENGINE=INNODB;


ALTER TABLE SBI_ORG_UNIT_NODES ADD CONSTRAINT FK_SBI_ORG_UNIT_NODES_1 FOREIGN KEY FK_SBI_ORG_UNIT_NODES_1 ( OU_ID ) REFERENCES SBI_ORG_UNIT ( ID ) ON DELETE CASCADE;
ALTER TABLE SBI_ORG_UNIT_NODES ADD CONSTRAINT FK_SBI_ORG_UNIT_NODES_2 FOREIGN KEY FK_SBI_ORG_UNIT_NODES_2 ( HIERARCHY_ID ) REFERENCES SBI_ORG_UNIT_HIERARCHIES ( ID ) ON DELETE CASCADE;
ALTER TABLE SBI_ORG_UNIT_NODES ADD CONSTRAINT FK_SBI_ORG_UNIT_NODES_3 FOREIGN KEY FK_SBI_ORG_UNIT_NODES_3 ( PARENT_NODE_ID ) REFERENCES SBI_ORG_UNIT_NODES ( NODE_ID ) ON DELETE CASCADE;

ALTER TABLE SBI_ORG_UNIT_GRANT ADD CONSTRAINT FK_SBI_ORG_UNIT_GRANT_2 FOREIGN KEY FK_SBI_ORG_UNIT_GRANT_2 ( HIERARCHY_ID ) REFERENCES SBI_ORG_UNIT_HIERARCHIES ( ID ) ON DELETE CASCADE;
ALTER TABLE SBI_ORG_UNIT_GRANT ADD CONSTRAINT FK_SBI_ORG_UNIT_GRANT_3 FOREIGN KEY FK_SBI_ORG_UNIT_GRANT_3 ( KPI_MODEL_INST_NODE_ID ) REFERENCES SBI_KPI_MODEL_INST ( KPI_MODEL_INST ) ON DELETE CASCADE;

ALTER TABLE SBI_ORG_UNIT_GRANT_NODES ADD CONSTRAINT FK_SBI_ORG_UNIT_GRANT_NODES_1 FOREIGN KEY FK_SBI_ORG_UNIT_GRANT_NODES_1 ( NODE_ID ) REFERENCES SBI_ORG_UNIT_NODES ( NODE_ID ) ON DELETE CASCADE;
ALTER TABLE SBI_ORG_UNIT_GRANT_NODES ADD CONSTRAINT FK_SBI_ORG_UNIT_GRANT_NODES_2 FOREIGN KEY FK_SBI_ORG_UNIT_GRANT_NODES_2 ( KPI_MODEL_INST_NODE_ID ) REFERENCES SBI_KPI_MODEL_INST ( KPI_MODEL_INST ) ON DELETE CASCADE;
ALTER TABLE SBI_ORG_UNIT_GRANT_NODES ADD CONSTRAINT FK_SBI_ORG_UNIT_GRANT_NODES_3 FOREIGN KEY FK_SBI_ORG_UNIT_GRANT_NODES_3 ( GRANT_ID ) REFERENCES SBI_ORG_UNIT_GRANT ( ID ) ON DELETE CASCADE;
 
 --MONICA 18/10/10: KPI OU
ALTER TABLE SBI_KPI_VALUE ADD COLUMN ORG_UNIT_ID INTEGER;
--Davide 12/10/2010: analytical drivers are visible by default
UPDATE SBI_OBJ_PAR SET VIEW_FL = 1;

--Davide 05/11/2010: added COMPANY column to SBI_ORG_UNIT_HIERARCHIES
ALTER TABLE SBI_ORG_UNIT_HIERARCHIES ADD COLUMN COMPANY VARCHAR(200);

--MONICA  05/11/2010:BUG ON DUPLICATE ROLE NAMES
ALTER TABLE SBI_EXT_ROLES MODIFY COLUMN NAME VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
 ADD UNIQUE INDEX NAME(NAME);

--MONICA  05/11/2010: KPI ENGINE : added hierarchy as parameter to filter grants and added company 
 ALTER TABLE SBI_KPI_VALUE ADD COLUMN HIERARCHY_ID INT(11) AFTER `ORG_UNIT_ID`,
 ADD COLUMN `COMPANY` VARCHAR(200) AFTER `HIERARCHY_ID`;

--Davide 11/11/2010 (Organizationl Unit: colums LABEL and NAME resized to 100; unique key is (LABEL,NAME))
ALTER TABLE SBI_ORG_UNIT MODIFY COLUMN LABEL VARCHAR(100) NOT NULL;
ALTER TABLE SBI_ORG_UNIT MODIFY COLUMN NAME VARCHAR(100) NOT NULL;
ALTER TABLE SBI_ORG_UNIT DROP INDEX LABEL;
ALTER TABLE SBI_ORG_UNIT ADD INDEX XAK1SBI_ORG_UNIT(LABEL, NAME);

--Davide 16/11/2010: added url to external application in menu configuration
ALTER TABLE SBI_MENU ADD COLUMN EXT_APP_URL VARCHAR(1000);

--Andrea 20/12/2010: added to solve bug SPAGOBI-433
ALTER TABLE SBI_EVENTS_LOG MODIFY COLUMN DESCR TEXT;

COMMIT;    
--Monica 10/01/2011: error in OU name filed too short
alter table sbi_org_unit modify column NAME varchar(200);

--Alberto 11/01/2011: goal scripts


CREATE TABLE SBI_GOAL (
  GOAL_ID       INTEGER NOT NULL AUTO_INCREMENT,
  GRANT_ID      INTEGER NOT NULL,
  START_DATE    DATE NOT NULL,
  END_DATE      DATE NOT NULL,
  NAME          VARCHAR(20) NOT NULL,
  LABEL          VARCHAR(20),
  DESCRIPTION		VARCHAR(1000),
  PRIMARY KEY (GOAL_ID)
);


CREATE TABLE SBI_GOAL_HIERARCHY (
  GOAL_HIERARCHY_ID INTEGER NOT NULL AUTO_INCREMENT,
  ORG_UNIT_ID       INTEGER NOT NULL,
  GOAL_ID           INTEGER NOT NULL,
  PARENT_ID         INTEGER,
  NAME              VARCHAR(50) NOT NULL,
  LABEL             VARCHAR(50),
  GOAL              VARCHAR(1000) ,
  PRIMARY KEY (GOAL_HIERARCHY_ID)
);


CREATE TABLE SBI_GOAL_KPI (
  GOAL_KPI_ID         INTEGER NOT NULL AUTO_INCREMENT,
  KPI_INSTANCE_ID     INTEGER NOT NULL,
  GOAL_HIERARCHY_ID   INTEGER NOT NULL,
  WEIGHT1             DOUBLE,
  WEIGHT2             DOUBLE,
  THRESHOLD1          DOUBLE,
  THRESHOLD2          DOUBLE,
  THRESHOLD1SIGN      INTEGER,
  THRESHOLD2SIGN      INTEGER,
  PRIMARY KEY (GOAL_KPI_ID)
);

ALTER TABLE SBI_GOAL_KPI ENGINE = InnoDB;
ALTER TABLE SBI_GOAL_HIERARCHY ENGINE = InnoDB;
ALTER TABLE SBI_GOAL ENGINE = InnoDB;

ALTER TABLE SBI_GOAL ADD CONSTRAINT FK_GRANT_ID_GRANT  FOREIGN KEY FK_GRANT_ID_GRANT (GRANT_ID) REFERENCES SBI_ORG_UNIT_GRANT (ID) ON DELETE CASCADE ON UPDATE RESTRICT;
                 
ALTER TABLE SBI_GOAL_HIERARCHY ADD CONSTRAINT FK_SBI_GOAL_HIERARCHY_GOAL  FOREIGN KEY FK_SBI_GOAL_HIERARCHY_GOAL (GOAL_ID) REFERENCES SBI_GOAL (GOAL_ID) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE SBI_GOAL_HIERARCHY ADD CONSTRAINT FK_SBI_GOAL_HIERARCHY_PARENT  FOREIGN KEY FK_SBI_GOAL_HIERARCHY_PARENT (PARENT_ID) REFERENCES SBI_GOAL_HIERARCHY (GOAL_HIERARCHY_ID) ON DELETE CASCADE ON UPDATE RESTRICT;
 
ALTER TABLE SBI_GOAL_KPI ADD CONSTRAINT FK_SBI_GOAL_KPI_GOAL  FOREIGN KEY FK_SBI_GOAL_KPI_GOAL (GOAL_HIERARCHY_ID) REFERENCES SBI_GOAL_HIERARCHY (GOAL_HIERARCHY_ID)  ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE SBI_GOAL_KPI ADD CONSTRAINT FK_SBI_GOAL_KPI_KPI  FOREIGN KEY FK_SBI_GOAL_KPI_KPI (KPI_INSTANCE_ID) REFERENCES SBI_KPI_MODEL_INST (KPI_MODEL_INST) ON DELETE CASCADE ON UPDATE RESTRICT;
      
--Chiara 22/03/2011: dataset scripts
ALTER TABLE SBI_DATA_SET ADD COLUMN CATEGORY_ID INTEGER;

ALTER TABLE SBI_DATA_SET ADD CONSTRAINT FK_SBI_DATA_SET_CAT  FOREIGN KEY FK_SBI_DATA_SET_CAT (CATEGORY_ID) REFERENCES SBI_DOMAINS (VALUE_ID) ON DELETE CASCADE ON UPDATE RESTRICT;
--Chiara 30/03/2011
ALTER TABLE SBI_DATA_SET DROP COLUMN EXECUTOR_CLASS;

INSERT into SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) values ('groovy','Groovy','SCRIPT_TYPE','Script Type','Script Type');
INSERT into SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) values ('rhino-nonjdk','Javascript','SCRIPT_TYPE','Script Type','Script Type');
INSERT into SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) values ('ejs','Embedded Javascript','SCRIPT_TYPE','Script Type','Script Type');
INSERT into SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) values ('File','SbiFileDataSet','DATA_SET_TYPE','Data Set Type','SbiFileDataSet');
INSERT into SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) values ('Query','SbiQueryDataSet','DATA_SET_TYPE','Data Set Type','SbiQueryDataSet');
INSERT into SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) values ('Java Class','SbiJClassDataSet','DATA_SET_TYPE','Data Set Type','SbiJClassDataSet');
INSERT into SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) values ('Web Service','SbiWSDataSet','DATA_SET_TYPE','Data Set Type','SbiWSDataSet');
INSERT into SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) values ('Script','SbiScriptDataSet','DATA_SET_TYPE','Data Set Type','SbiScriptDataSet');
INSERT into SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) values ('Qbe','SbiQbeDataSet','DATA_SET_TYPE','Data Set Type','SbiQbeDataSet');

CREATE TABLE SBI_DATA_SET_HISTORY (
	DS_H_ID 	   INTEGER NOT NULL AUTO_INCREMENT,
	DS_ID 		   INTEGER NOT NULL,
	ACTIVE		   BOOLEAN NOT NULL,
	VERSION_NUM	   INTEGER NOT NULL,
	OBJECT_TYPE    VARCHAR(50),
	DS_METADATA    VARCHAR(2000),
	PARAMS         VARCHAR(1000),
	CATEGORY_ID    INTEGER,
    FILE_NAME	   VARCHAR(300),
    QUERY   	   TEXT,
    DATA_SOURCE_ID INTEGER,
    ADRESS   	   VARCHAR(250),
    OPERATION      VARCHAR(50),
    JCLASS_NAME    VARCHAR(100),
    LANGUAGE_SCRIPT VARCHAR(50),
	SCRIPT   	   TEXT,
	JSON_QUERY     TEXT,
    TRANSFORMER_ID INTEGER,
    PIVOT_COLUMN   VARCHAR(50),
	PIVOT_ROW      VARCHAR(50),
	PIVOT_VALUE    VARCHAR(50),
	NUM_ROWS	   BOOLEAN DEFAULT FALSE,	
	USER_IN              VARCHAR(100) NOT NULL,
	TIME_IN              TIMESTAMP NOT NULL,
	META_VERSION         VARCHAR(100),
	SBI_VERSION_IN       VARCHAR(10),
    PRIMARY KEY (DS_H_ID)
)TYPE=INNODB;

ALTER TABLE SBI_DATA_SET_HISTORY ADD CONSTRAINT FK_SBI_DATA_SET_T  FOREIGN KEY FK_SBI_DATA_SET_T ( TRANSFORMER_ID ) REFERENCES SBI_DOMAINS ( VALUE_ID ) ON DELETE CASCADE;
ALTER TABLE SBI_DATA_SET_HISTORY ADD CONSTRAINT FK_SBI_DATA_SET_CAT  FOREIGN KEY FK_SBI_DATA_SET_CAT (CATEGORY_ID) REFERENCES SBI_DOMAINS (VALUE_ID) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE SBI_DATA_SET_HISTORY ADD CONSTRAINT FK_SBI_DATA_SET_DS  FOREIGN KEY FK_SBI_DATA_SET_DS ( DATA_SOURCE_ID ) REFERENCES SBI_DATA_SOURCE ( DS_ID ) ON DELETE CASCADE;
ALTER TABLE SBI_DATA_SET_HISTORY ADD CONSTRAINT FK_SBI_DATA_SET_DS2  FOREIGN KEY FK_SBI_DATA_SET_DS2 (DS_ID) REFERENCES SBI_DATA_SET (DS_ID) ON DELETE CASCADE ON UPDATE RESTRICT;

INSERT INTO SBI_DATA_SET_HISTORY
(DS_ID,ACTIVE,VERSION_NUM,FILE_NAME,QUERY,JCLASS_NAME,SCRIPT,PARAMS,DS_METADATA,DATA_SOURCE_ID
,OBJECT_TYPE,OPERATION,TRANSFORMER_ID,PIVOT_COLUMN,PIVOT_ROW,PIVOT_VALUE,NUM_ROWS,LANGUAGE_SCRIPT
,USER_IN, TIME_IN)
SELECT DS_ID,true ACTIVE,1 VERSION_NUM,FILE_NAME,QUERY,JCLASS_NAME,SCRIPT,PARAMS,DS_METADATA,DATA_SOURCE_ID
,OBJECT_TYPE,OPERATION,TRANSFORMER_ID,PIVOT_COLUMN,PIVOT_ROW,PIVOT_VALUE,NUM_ROWS,LANGUAGE_SCRIPT
,'biadmin' USER_IN, now() TIME_IN
FROM SBI_DATA_SET;

commit;

--Drop all SBI_DATA_SET foreign keys
ALTER TABLE SBI_DATA_SET DROP FOREIGN KEY FK_SBI_DATA_SET_1;
ALTER TABLE SBI_DATA_SET DROP FOREIGN KEY FK_SBI_DATA_SET_CAT;

ALTER TABLE SBI_DATA_SET DROP COLUMN CATEGORY_ID;
ALTER TABLE SBI_DATA_SET DROP COLUMN FILE_NAME;
ALTER TABLE SBI_DATA_SET DROP COLUMN QUERY;
ALTER TABLE SBI_DATA_SET DROP COLUMN JCLASS_NAME;
ALTER TABLE SBI_DATA_SET DROP COLUMN SCRIPT;
ALTER TABLE SBI_DATA_SET DROP COLUMN PARAMS;
ALTER TABLE SBI_DATA_SET DROP COLUMN DS_METADATA;
ALTER TABLE SBI_DATA_SET DROP COLUMN DATA_SOURCE_ID;
ALTER TABLE SBI_DATA_SET DROP COLUMN OBJECT_TYPE;
ALTER TABLE SBI_DATA_SET DROP COLUMN OPERATION;
ALTER TABLE SBI_DATA_SET DROP COLUMN TRANSFORMER_ID;
ALTER TABLE SBI_DATA_SET DROP COLUMN PIVOT_COLUMN;
ALTER TABLE SBI_DATA_SET DROP COLUMN PIVOT_ROW;
ALTER TABLE SBI_DATA_SET DROP COLUMN PIVOT_VALUE;
ALTER TABLE SBI_DATA_SET DROP COLUMN NUM_ROWS;
ALTER TABLE SBI_DATA_SET DROP COLUMN LANGUAGE_SCRIPT;
ALTER TABLE SBI_DATA_SET DROP COLUMN ADRESS;

ALTER TABLE SBI_DATA_SET ADD COLUMN USER_IN VARCHAR(100) NOT NULL;
ALTER TABLE SBI_DATA_SET ADD COLUMN USER_UP VARCHAR(100); 
ALTER TABLE SBI_DATA_SET ADD COLUMN USER_DE VARCHAR(100);
ALTER TABLE SBI_DATA_SET ADD COLUMN TIME_IN TIMESTAMP NOT NULL;
ALTER TABLE SBI_DATA_SET ADD COLUMN TIME_UP TIMESTAMP;
ALTER TABLE SBI_DATA_SET ADD COLUMN TIME_DE TIMESTAMP;
ALTER TABLE SBI_DATA_SET ADD COLUMN SBI_VERSION_IN VARCHAR(10); 
ALTER TABLE SBI_DATA_SET ADD COLUMN SBI_VERSION_UP VARCHAR(10); 
ALTER TABLE SBI_DATA_SET ADD COLUMN SBI_VERSION_DE VARCHAR(10); 
ALTER TABLE SBI_DATA_SET ADD COLUMN META_VERSION VARCHAR(100); 
ALTER TABLE SBI_DATA_SET ADD COLUMN ORGANIZATION VARCHAR(20); 

UPDATE SBI_DATA_SET SET USER_IN = 'biadmin';

--Chiara 4/4/2011
ALTER TABLE SBI_DATA_SET DROP COLUMN TIME_UP;
ALTER TABLE SBI_DATA_SET DROP COLUMN TIME_DE;
ALTER TABLE SBI_DATA_SET ADD COLUMN TIME_UP TIMESTAMP NULL DEFAULT NULL;
ALTER TABLE SBI_DATA_SET ADD COLUMN TIME_DE TIMESTAMP NULL DEFAULT NULL;

--Davide 12/04/2011
delete from SBI_DOMAINS WHERE VALUE_CD = 'Qbe' AND DOMAIN_CD = 'DATA_SET_TYPE';
INSERT into SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) values ('Qbe','SbiQbeDataSet','DATA_SET_TYPE','Data Set Type','SbiQbeDataSet');

ALTER TABLE SBI_DATA_SET_HISTORY ADD COLUMN DATAMARTS VARCHAR(400); 

Angelo 13/04/2011

CREATE TABLE `hibernate_sequences` (
  `sequence_name` varchar(200) NOT NULL,
  `next_val` int(11) NOT NULL,
  PRIMARY KEY (`sequence_name`)
);

Chiara 15/04/2011
INSERT INTO SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) VALUES ('groovy','Groovy','SCRIPT_TYPE','Script Type','Script Type');
INSERT INTO SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) VALUES ('rhino-nonjdk','Javascript','SCRIPT_TYPE','Script Type','Script Type');
INSERT INTO SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) VALUES ('ejs','Embedded Javascript','SCRIPT_TYPE','Script Type','Script Type');
INSERT INTO SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) VALUES ('File','SbiFileDataSet','DATA_SET_TYPE','Data Set Type','SbiFileDataSet');
INSERT INTO SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) VALUES ('Query','SbiQueryDataSet','DATA_SET_TYPE','Data Set Type','SbiQueryDataSet');
INSERT INTO SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) VALUES ('Java Class','SbiJClassDataSet','DATA_SET_TYPE','Data Set Type','SbiJClassDataSet');
INSERT INTO SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) VALUES ('Web Service','SbiWSDataSet','DATA_SET_TYPE','Data Set Type','SbiWSDataSet');
INSERT INTO SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) VALUES ('Script','SbiScriptDataSet','DATA_SET_TYPE','Data Set Type','SbiScriptDataSet');
INSERT INTO SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) VALUES ('Qbe','SbiQbeDataSet','DATA_SET_TYPE','Data Set Type','SbiQbeDataSet');
INSERT INTO SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) VALUES ('WORKSHEET','sbidomains.nm.worksheet','BIOBJ_TYPE','BI Object types','sbidomains.ds.worksheet');
INSERT INTO SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) VALUES ('DEVELOPMENT', 'sbidomains.permissiononfolder.nm.dev','PERMISSION_ON_FOLDER','Permission on folder','sbidomains.permissiononfolder.ds.dev');
INSERT INTO SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) VALUES ('TEST', 'sbidomains.permissiononfolder.nm.test','PERMISSION_ON_FOLDER','Permission on folder','sbidomains.permissiononfolder.ds.test');
INSERT INTO SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) VALUES ('EXECUTION', 'sbidomains.permissiononfolder.nm.execute','PERMISSION_ON_FOLDER','Permission on folder','sbidomains.permissiononfolder.ds.execute');
INSERT INTO SBI_DOMAINS (VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS) VALUES ('CREATION', 'sbidomains.permissiononfolder.nm.create','PERMISSION_ON_FOLDER','Permission on folder','sbidomains.permissiononfolder.ds.create');
UPDATE SBI_FUNC_ROLE SET STATE_ID = (SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'DEVELOPMENT' AND DOMAIN_CD = 'PERMISSION_ON_FOLDER'), STATE_CD = 'DEVELOPMENT' WHERE STATE_ID = (SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'DEV' AND DOMAIN_CD = 'STATE');
UPDATE SBI_FUNC_ROLE SET STATE_ID = (SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'TEST' AND DOMAIN_CD = 'PERMISSION_ON_FOLDER'), STATE_CD = 'TEST' WHERE STATE_ID = (SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'TEST' AND DOMAIN_CD = 'STATE');
UPDATE SBI_FUNC_ROLE SET STATE_ID = (SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'EXECUTION' AND DOMAIN_CD = 'PERMISSION_ON_FOLDER'), STATE_CD = 'EXECUTION' WHERE STATE_ID = (SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'REL' AND DOMAIN_CD = 'STATE');

INSERT INTO SBI_USER_FUNC (NAME, DESCRIPTION) VALUES ('DomainManagement', 'DomainManagement');
INSERT INTO SBI_ROLE_TYPE_USER_FUNC values((SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'ROLE_TYPE' AND VALUE_CD = 'ADMIN'), (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME='DomainManagement'));
INSERT INTO SBI_USER_FUNC (NAME, DESCRIPTION) VALUES ('ConfigManagement', 'ConfigManagement');
INSERT INTO SBI_ROLE_TYPE_USER_FUNC values((SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'ROLE_TYPE' AND VALUE_CD = 'ADMIN'), (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME='ConfigManagement'));
INSERT INTO SBI_USER_FUNC (NAME, DESCRIPTION) VALUES ('UserSaveDocumentFunctionality', 'UserSaveDocumentFunctionality');
INSERT INTO SBI_ROLE_TYPE_USER_FUNC values((SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'ROLE_TYPE' AND VALUE_CD = 'ADMIN'), (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME='UserSaveDocumentFunctionality'));
INSERT INTO SBI_ROLE_TYPE_USER_FUNC values((SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'ROLE_TYPE' AND VALUE_CD = 'USER'), (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME='UserSaveDocumentFunctionality'));
INSERT INTO SBI_ROLE_TYPE_USER_FUNC values((SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'ROLE_TYPE' AND VALUE_CD = 'DEV_ROLE'), (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME='UserSaveDocumentFunctionality'));
INSERT INTO SBI_ROLE_TYPE_USER_FUNC values((SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'ROLE_TYPE' AND VALUE_CD = 'TEST_ROLE'), (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME='UserSaveDocumentFunctionality'));
INSERT INTO SBI_ENGINES (NAME,ENCRYPT, LABEL,MAIN_URL, DRIVER_NM, ENGINE_TYPE,BIOBJ_TYPE,USE_DATASOURCE,USE_DATASET) VALUES 
 ('Worksheet Engine', 0,'WorksheetEngine','/SpagoBIQbeEngine/servlet/AdapterHTTP','it.eng.spagobi.engines.drivers.worksheet.WorksheetDriver',(SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'ENGINE_TYPE' AND VALUE_CD = 'EXT'),(SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'BIOBJ_TYPE' AND VALUE_CD = 'WORKSHEET'),true,false);

CREATE TABLE SBI_DATA_SET_HISTORY (
	DS_H_ID 	   INTEGER NOT NULL AUTO_INCREMENT,
	DS_ID 		   INTEGER NOT NULL,
	ACTIVE		   BOOLEAN NOT NULL,
	VERSION_NUM	   INTEGER NOT NULL,
	OBJECT_TYPE    VARCHAR(50),
	DS_METADATA    VARCHAR(2000),
	PARAMS         VARCHAR(1000),
	CATEGORY_ID    INTEGER,
    FILE_NAME	   VARCHAR(300),
    QUERY   	   TEXT,
    DATA_SOURCE_ID INTEGER,
    ADRESS   	   VARCHAR(250),
    OPERATION      VARCHAR(50),
    JCLASS_NAME    VARCHAR(100),
    LANGUAGE_SCRIPT VARCHAR(50),
	SCRIPT   	   TEXT,
	JSON_QUERY     TEXT,
	DATAMARTS	   VARCHAR(400),
    TRANSFORMER_ID INTEGER,
    PIVOT_COLUMN   VARCHAR(50),
	PIVOT_ROW      VARCHAR(50),
	PIVOT_VALUE    VARCHAR(50),
	NUM_ROWS	   BOOLEAN DEFAULT FALSE,	
	USER_IN              VARCHAR(100) NOT NULL,
	TIME_IN              TIMESTAMP NOT NULL,
	META_VERSION         VARCHAR(100),
	SBI_VERSION_IN       VARCHAR(10),
    PRIMARY KEY (DS_H_ID)
)TYPE=INNODB;

ALTER TABLE SBI_DATA_SET_HISTORY ADD CONSTRAINT FK_SBI_DATA_SET_T  FOREIGN KEY ( TRANSFORMER_ID ) REFERENCES SBI_DOMAINS ( VALUE_ID ) ON DELETE CASCADE;
ALTER TABLE SBI_DATA_SET_HISTORY ADD CONSTRAINT FK_SBI_DATA_SET_CAT  FOREIGN KEY (CATEGORY_ID) REFERENCES SBI_DOMAINS (VALUE_ID) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE SBI_DATA_SET_HISTORY ADD CONSTRAINT FK_SBI_DATA_SET_DS  FOREIGN KEY ( DATA_SOURCE_ID ) REFERENCES SBI_DATA_SOURCE ( DS_ID ) ON DELETE CASCADE;
ALTER TABLE SBI_DATA_SET_HISTORY ADD CONSTRAINT FK_SBI_DATA_SET_DS2  FOREIGN KEY (DS_ID) REFERENCES SBI_DATA_SET (DS_ID) ON DELETE CASCADE ON UPDATE RESTRICT;

INSERT INTO SBI_DATA_SET_HISTORY
(DS_H_ID,DS_ID,ACTIVE,VERSION_NUM,FILE_NAME,QUERY,JCLASS_NAME,SCRIPT,PARAMS,DS_METADATA
	,DATA_SOURCE_ID,OBJECT_TYPE,OPERATION,TRANSFORMER_ID,PIVOT_COLUMN,PIVOT_ROW
	,PIVOT_VALUE,NUM_ROWS,LANGUAGE_SCRIPT,USER_IN, TIME_IN)
SELECT DS_ID, DS_ID,true ACTIVE,1 VERSION_NUM,FILE_NAME,QUERY,JCLASS_NAME,SCRIPT,PARAMS,DS_METADATA,DATA_SOURCE_ID
,OBJECT_TYPE,OPERATION,TRANSFORMER_ID,PIVOT_COLUMN,PIVOT_ROW,PIVOT_VALUE,NUM_ROWS,LANGUAGE_SCRIPT
,'biadmin' USER_IN, NOW() TIME_IN
FROM SBI_DATA_SET;

commit;

Angelo 19/04/2011

AGGIUNTI I CAMPI TECNICI , ESEGUIRE  UpdateTecnologyFields.sql


--Davide 23/05/2011: permissions on folders
INSERT into SBI_DOMAINS (VALUE_ID, VALUE_CD, VALUE_NM, DOMAIN_CD, DOMAIN_NM,VALUE_DS, USER_IN) values ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'), 'DEVELOPMENT', 'sbidomains.permissiononfolder.nm.dev','PERMISSION_ON_FOLDER','Permission on folder','sbidomains.permissiononfolder.ds.dev', '');
UPDATE hibernate_sequences SET next_val = (SELECT MAX(VALUE_ID) + 1 FROM SBI_DOMAINS) WHERE sequence_name = 'SBI_DOMAINS';  
INSERT into SBI_DOMAINS (VALUE_ID, VALUE_CD, VALUE_NM, DOMAIN_CD, DOMAIN_NM,VALUE_DS, USER_IN) values ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'), 'TEST', 'sbidomains.permissiononfolder.nm.test','PERMISSION_ON_FOLDER','Permission on folder','sbidomains.permissiononfolder.ds.test', '');
UPDATE hibernate_sequences SET next_val = (SELECT MAX(VALUE_ID) + 1 FROM SBI_DOMAINS) WHERE sequence_name = 'SBI_DOMAINS';  
INSERT into SBI_DOMAINS (VALUE_ID, VALUE_CD, VALUE_NM, DOMAIN_CD, DOMAIN_NM,VALUE_DS, USER_IN) values ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'), 'EXECUTION', 'sbidomains.permissiononfolder.nm.execute','PERMISSION_ON_FOLDER','Permission on folder','sbidomains.permissiononfolder.ds.execute', '');
UPDATE hibernate_sequences SET next_val = (SELECT MAX(VALUE_ID) + 1 FROM SBI_DOMAINS) WHERE sequence_name = 'SBI_DOMAINS';  
INSERT into SBI_DOMAINS (VALUE_ID, VALUE_CD, VALUE_NM, DOMAIN_CD, DOMAIN_NM,VALUE_DS, USER_IN) values ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'), 'CREATION', 'sbidomains.permissiononfolder.nm.create','PERMISSION_ON_FOLDER','Permission on folder','sbidomains.permissiononfolder.ds.create', '');
UPDATE hibernate_sequences SET next_val = (SELECT MAX(VALUE_ID) + 1 FROM SBI_DOMAINS) WHERE sequence_name = 'SBI_DOMAINS';
UPDATE SBI_FUNC_ROLE set STATE_ID = (SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'DEVELOPMENT' AND DOMAIN_CD = 'PERMISSION_ON_FOLDER'), STATE_CD = 'DEVELOPMENT' WHERE STATE_ID = (SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'DEV' AND DOMAIN_CD = 'STATE');
UPDATE SBI_FUNC_ROLE set STATE_ID = (SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'TEST' AND DOMAIN_CD = 'PERMISSION_ON_FOLDER'), STATE_CD = 'TEST' WHERE STATE_ID = (SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'TEST' AND DOMAIN_CD = 'STATE');
UPDATE SBI_FUNC_ROLE set STATE_ID = (SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'EXECUTION' AND DOMAIN_CD = 'PERMISSION_ON_FOLDER'), STATE_CD = 'EXECUTION' WHERE STATE_ID = (SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'REL' AND DOMAIN_CD = 'STATE');

--Chiara 30/05/2011
INSERT INTO  SBI_EXPORTERS (ENGINE_ID,DOMAIN_ID,DEFAULT_VALUE)values((SELECT ENGINE_ID FROM SBI_ENGINES WHERE LABEL='WorksheetEngine'),(SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'EXPORT_TYPE' AND VALUE_CD = 'XLS'), false);

--Monia 30/05/2011
ALTER TABLE SBI_CONFIG ADD COLUMN CATEGORY VARCHAR( 50 ) NULL 

--Chiara 15/06/2011
--If this is not the correct name of your foreign key delete it manually
ALTER TABLE SBI_KPI_DOCUMENTS DROP FOREIGN KEY sbi_kpi_documents_ibfk_1;
ALTER TABLE SBI_KPI_DOCUMENTS  ADD CONSTRAINT FK_SBI_KPI_DOCUMENTS_1 FOREIGN KEY ( BIOBJ_ID ) REFERENCES  SBI_OBJECTS  ( BIOBJ_ID ) ON DELETE CASCADE ON UPDATE CASCADE;
--Monica 11/07/2011
ALTER TABLE SBI_ORG_UNIT_GRANT ADD COLUMN IS_AVAILABLE INTEGER DEFAULT 1;

-- GIULIO 20/07/2011
CREATE TABLE SBI_OBJ_PARVIEW (
  OBJ_PAR_ID INTEGER NOT NULL,
   OBJ_PAR_FATHER_ID  INTEGER NOT NULL,
   OPERATION  VARCHAR(20) NOT NULL,
   COMPARE_VALUE  VARCHAR(200) NOT NULL,
   VIEW_LABEL  VARCHAR(50),
   PROG INTEGER,
          USER_IN              VARCHAR(100) NOT NULL,
       USER_UP              VARCHAR(100),
       USER_DE              VARCHAR(100),
       TIME_IN              TIMESTAMP NOT NULL,
       TIME_UP              TIMESTAMP NULL DEFAULT NULL,
       TIME_DE              TIMESTAMP NULL DEFAULT NULL,
       SBI_VERSION_IN       VARCHAR(10),
       SBI_VERSION_UP       VARCHAR(10),
       SBI_VERSION_DE       VARCHAR(10),
       META_VERSION         VARCHAR(100),
       ORGANIZATION         VARCHAR(20),    
  PRIMARY KEY ( OBJ_PAR_ID ,  OBJ_PAR_FATHER_ID ,  OPERATION, COMPARE_VALUE )
);

CREATE INDEX XAK1SBI_PARVIEW ON SBI_OBJ_PARVIEW
(
       OBJ_PAR_ID                         ASC,
       OBJ_PAR_FATHER_ID           		  ASC
);

ALTER TABLE SBI_OBJ_PARVIEW ADD CONSTRAINT FK_SBI_OBJ_PARVIEW_1 FOREIGN KEY ( OBJ_PAR_ID ) REFERENCES SBI_OBJ_PAR ( OBJ_PAR_ID ) ON DELETE RESTRICT;
ALTER TABLE SBI_OBJ_PARVIEW ADD CONSTRAINT FK_SBI_OBJ_PARVIEW_2 FOREIGN KEY ( OBJ_PAR_FATHER_ID ) REFERENCES SBI_OBJ_PAR ( OBJ_PAR_ID ) ON DELETE RESTRICT;


-- Change after 3.1  14/9/2011 Giulio

ALTER TABLE sbi_data_set_history ADD COLUMN CUSTOM_DATA TEXT AFTER DATAMARTS;

INSERT INTO SBI_DOMAINS (VALUE_ID, VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN)
VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),
	'Custom','SbiCustomDataSet','DATA_SET_TYPE','Data Set Type','SbiCustomDataSet', 'biadmin');


-- 6/10/2011 Giulio
ALTER TABLE sbi_data_set_history MODIFY COLUMN DS_METADATA TEXT;

-- 20/10/2011 Davide : il motore worksheet può lavorare con i dataset
UPDATE SBI_ENGINES SET USE_DATASET = TRUE WHERE DRIVER_NM = 'it.eng.spagobi.engines.drivers.worksheet.WorksheetDriver';

-- 06/12/2011 Davide : exporter XLSX per Qbe e Worksheet
INSERT INTO SBI_DOMAINS (VALUE_ID,VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS,USER_IN)
	VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),	
	'XLSX','XLSX','EXPORT_TYPE','Exporters type','Export type', 'biadmin');
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_DOMAINS';
commit;
INSERT INTO SBI_EXPORTERS (ENGINE_ID,DOMAIN_ID,DEFAULT_VALUE) 
	VALUES ((SELECT ENGINE_ID FROM SBI_ENGINES WHERE DRIVER_NM = 'it.eng.spagobi.engines.drivers.worksheet.WorksheetDriver'),
	(SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'EXPORT_TYPE' AND VALUE_CD = 'XLSX'), 
	false);
commit;
INSERT INTO SBI_EXPORTERS (ENGINE_ID,DOMAIN_ID,DEFAULT_VALUE) 
	VALUES ((SELECT ENGINE_ID FROM SBI_ENGINES WHERE DRIVER_NM = 'it.eng.spagobi.engines.drivers.qbe.QbeDriver'),
	(SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'EXPORT_TYPE' AND VALUE_CD = 'XLSX'), 
	false);
commit;

Change after 3.1  14/9/2011 Giulio

-- 6/12/2011 Giulio
CREATE TABLE SBI_I18N_MESSAGES (
  LANGUAGE_CD INTEGER NOT NULL,
  LABEL VARCHAR(200) NOT NULL,
  MESSAGE TEXT,
  PRIMARY KEY (LANGUAGE_CD, LABEL)
);

ALTER TABLE SBI_I18N_MESSAGES ADD CONSTRAINT FK_SBI_I18N_MESSAGES FOREIGN KEY (LANGUAGE_CD) REFERENCES SBI_DOMAINS(VALUE_ID);


INSERT INTO SBI_DOMAINS (VALUE_ID, VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN)
VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),
	'ITA','Italian','LANG','language ISO Code','Italian', 'biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

INSERT INTO SBI_DOMAINS (VALUE_ID, VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN)
VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),
	'ENG','English','LANG','language ISO Code','English', 'biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

INSERT INTO SBI_DOMAINS (VALUE_ID, VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN)
VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),
	'FRA','French','LANG','language ISO Code','French', 'biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

INSERT INTO SBI_DOMAINS (VALUE_ID, VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN)
VALUES ((SELECT next_val FROM hibRernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),
	'SPA','Spanish','LANG','language ISO Code','Spanish', 'biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;
--Monica 20/12/11
CREATE TABLE  SBI_KPI_COMMENTS(
  KPI_COMMENT_ID int(11) NOT NULL AUTO_INCREMENT,
  KPI_INST_ID int(11) DEFAULT NULL,
  BIN_ID int(11) DEFAULT NULL,
  EXEC_REQ varchar(500) DEFAULT NULL,
  OWNER varchar(50) DEFAULT NULL,
  ISPUBLIC tinyint(1) DEFAULT NULL,
  CREATION_DATE timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  LAST_CHANGE_DATE timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  USER_IN varchar(100) NOT NULL,
  USER_UP varchar(100) DEFAULT NULL,
  USER_DE varchar(100) DEFAULT NULL,
  TIME_IN timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  TIME_UP timestamp NULL DEFAULT NULL,
  TIME_DE timestamp NULL DEFAULT NULL,
  SBI_VERSION_IN varchar(10) DEFAULT NULL,
  SBI_VERSION_UP varchar(10) DEFAULT NULL,
  SBI_VERSION_DE varchar(10) DEFAULT NULL,
  META_VERSION varchar(100) DEFAULT NULL,
  ORGANIZATION varchar(20) DEFAULT NULL,
  PRIMARY KEY (KPI_COMMENT_ID),
  UNIQUE KEY XAK1SBI_KPI_COMMENT (KPI_COMMENT_ID),
  KEY FK_SBI_KPI_COMMENT_1 (BIN_ID),
  KEY FK_SBI_KPI_COMMENT_2 (KPI_INST_ID),
  CONSTRAINT FK_SBI_KPI_COMMENT_1 FOREIGN KEY (BIN_ID) REFERENCES SBI_BINARY_CONTENTS (BIN_ID),
  CONSTRAINT FK_SBI_KPI_COMMENT_2 FOREIGN KEY (KPI_INST_ID) REFERENCES SBI_KPI_INSTANCE (ID_KPI_INSTANCE)
);
// 23/01/2012   Giulio

CREATE TABLE SBI_PROGRESS_THREAD (
       PROGRESS_THREAD_ID   INTEGER NOT NULL,
       USER_ID              VARCHAR(100) NOT NULL,
       PARTIAL              INTEGER,
       TOTAL        	      INTEGER,
       FUNCTION_CD         VARCHAR(200),
       STATUS              VARCHAR(4000),
       RANDOM_KEY			VARCHAR(4000),	
       TYPE 				VARCHAR(200),
       PRIMARY KEY (PROGRESS_THREAD_ID)
)
 
insert into hibernate_sequences(next_val,sequence_name) values (ifnull((select max(PROGRESS_THREAD_ID)+1 from SBI_PROGRESS_THREAD) ,1),'SBI_PROGRESS_THREAD');


ALTER TABLE SBI_EXT_ROLES ADD COLUMN DO_MASSIVE_EXPORT BOOLEAN DEFAULT TRUE;

-- monica 03/02/2012
ALTER TABLE SBI_UDP_VALUE MODIFY COLUMN VALUE VARCHAR(1000) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL;


-- 6 febbraio
INSERT INTO SBI_CONFIG ( ID, LABEL, NAME, DESCRIPTION, IS_ACTIVE, VALUE_CHECK, VALUE_TYPE_ID, USER_IN) VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_CONFIG'),'JNDI_THREAD_MANAGER', 'JNDI_THREAD_MANAGER', 'Jndi to build work manager', true, 'java:/comp/env/wm/SpagoWorkManager',(select VALUE_ID from SBI_DOMAINS where VALUE_CD = 'STRING' AND DOMAIN_CD = 'PAR_TYPE'), 'biadmin');=======

-- 22/02/2012 Monica
INSERT INTO SBI_DOMAINS (VALUE_ID, VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN)
	VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),
	'MOBILE_TABLE','sbidomains.nm.mobile.table','BIOBJ_TYPE','BI Object types','sbidomains.ds.mobile.table', 'biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

INSERT INTO SBI_DOMAINS (VALUE_ID, VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN)
	VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),
	'MOBILE_CHART','sbidomains.nm.mobile.chart','BIOBJ_TYPE','BI Object types','sbidomains.ds.mobile.chart', 'biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

INSERT INTO SBI_DOMAINS (VALUE_ID, VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN)
	VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),
	'MOBILE_COMPOSED','sbidomains.nm.mobile.composed','BIOBJ_TYPE','BI Object types','sbidomains.ds.mobile.composed', 'biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

INSERT INTO SBI_ENGINES (ENGINE_ID,NAME, ENCRYPT, LABEL,MAIN_URL, DRIVER_NM, ENGINE_TYPE,BIOBJ_TYPE,USE_DATASOURCE,USE_DATASET, USER_IN, TIME_IN) VALUES
((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_ENGINES'),'Table Mobile Engine', 0, 'TableMobileEngine','/SpagoBIMobileEngine/servlet/AdapterHTTP?ACTION_NAME=MOBILE_ENGINE_START_ACTION','it.eng.spagobi.engines.drivers.generic.GenericDriver',(SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'ENGINE_TYPE' AND VALUE_CD = 'EXT'),(SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'BIOBJ_TYPE' AND VALUE_CD = 'MOBILE_TABLE'),false, true, 'biadmin', current_timestamp);
INSERT INTO SBI_ENGINES (ENGINE_ID, NAME, ENCRYPT, LABEL,MAIN_URL, DRIVER_NM, ENGINE_TYPE,BIOBJ_TYPE,USE_DATASOURCE,USE_DATASET, USER_IN, TIME_IN) VALUES
((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_ENGINES'),'Chart Mobile Engine', 0, 'ChartMobileEngine','/SpagoBIMobileEngine/servlet/AdapterHTTP?ACTION_NAME=MOBILE_ENGINE_START_ACTION','it.eng.spagobi.engines.drivers.generic.GenericDriver',(SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'ENGINE_TYPE' AND VALUE_CD = 'EXT'),(SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'BIOBJ_TYPE' AND VALUE_CD = 'MOBILE_CHART'),false, true, 'biadmin', current_timestamp);
INSERT INTO SBI_ENGINES (ENGINE_ID, NAME, ENCRYPT, LABEL,MAIN_URL, DRIVER_NM, ENGINE_TYPE,BIOBJ_TYPE,USE_DATASOURCE,USE_DATASET, USER_IN, TIME_IN) VALUES
((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_ENGINES'),'Composed Mobile Engine', 0, 'ComposedMobileEngine','/SpagoBIMobileEngine/servlet/AdapterHTTP?ACTION_NAME=MOBILE_ENGINE_START_ACTION','it.eng.spagobi.engines.drivers.generic.GenericDriver',(SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'ENGINE_TYPE' AND VALUE_CD = 'EXT'),(SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'BIOBJ_TYPE' AND VALUE_CD = 'MOBILE_COMPOSED'),false, true, 'biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+3 where sequence_name = 'SBI_ENGINES';
commit;

-- 12/03/2012 Antonella
INSERT INTO SBI_DOMAINS (VALUE_ID, VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN)
    VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),
    'CHART','Chart','BIOBJ_TYPE','BI Object types','sbidomains.ds.chart', 'biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

INSERT INTO SBI_ENGINES (ENGINE_ID, NAME, ENCRYPT, LABEL,MAIN_URL, DRIVER_NM, ENGINE_TYPE,BIOBJ_TYPE,USE_DATASOURCE,USE_DATASET, USER_IN, TIME_IN) VALUES
((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_ENGINES'),'ChartExternalEngine', 0, 'ChartExternalEngine','/SpagoBIChartEngine/servlet/AdapterHTTP','it.eng.spagobi.engines.drivers.chart.ChartDriver',(SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'ENGINE_TYPE' AND VALUE_CD = 'EXT'),(SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'BIOBJ_TYPE' AND VALUE_CD = 'CHART'),false, true, 'biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_ENGINES';
commit;

INSERT INTO SBI_EXPORTERS (ENGINE_ID,DOMAIN_ID,DEFAULT_VALUE) 
	VALUES ((SELECT ENGINE_ID FROM SBI_ENGINES WHERE LABEL = 'ChartExternalEngine'),
	(SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'EXPORT_TYPE' AND VALUE_CD = 'PDF'), 
	false);
commit;
INSERT INTO SBI_EXPORTERS (ENGINE_ID,DOMAIN_ID,DEFAULT_VALUE) 
	VALUES ((SELECT ENGINE_ID FROM SBI_ENGINES WHERE LABEL = 'ChartExternalEngine'),
	(SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'EXPORT_TYPE' AND VALUE_CD = 'JPG'), 
	true);
commit;

update sbi_engines set main_url = '/SpagoBIConsoleEngine/servlet/AdapterHTTP', driver_nm = 'it.eng.spagobi.engines.drivers.console.ConsoleDriver' where label = 'ConsoleEngine';
commit;

-- 02/05/2012 Marco
ALTER TABLE SBI_LOV ADD COLUMN DATASET_ID INT(11) AFTER ORGANIZATION,
 ADD CONSTRAINT FK_SBI_LOV_2 FOREIGN KEY FK_SBI_LOV_2 (DATASET_ID)
    REFERENCES SBI_DATA_SET (DS_ID)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;
	
INSERT INTO SBI_DOMAINS (VALUE_ID, VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN)
    VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),
    'DATASET','sbidomains.nm.dataset','INPUT_TYPE','Input mode and values','sbidomains.ds.dataset', 'server', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

-- 07/05/2012 Alberto Ghedin
ALTER TABLE SBI_EXT_ROLES ADD COLUMN EDIT_WORKSHEET BOOLEAN DEFAULT TRUE;

-- 10/05/2012 Andrea Gioia
DELETE FROM SBI_DOMAINS WHERE domain_cd = 'SCRIPT_TYPE' AND value_cd='ejs';
UPDATE SBI_DOMAINS SET value_cd='ECMAScript' WHERE domain_cd = 'SCRIPT_TYPE' AND value_cd='rhino-nonjdk';
ALTER TABLE SBI_DATA_SET_HISTORY ADD COLUMN QUERY_SCRIPT TEXT DEFAULT NULL;
ALTER TABLE SBI_DATA_SET_HISTORY ADD COLUMN QUERY_SCRIPT_LANGUAGE VARCHAR(100) DEFAULT NULL;

-- 01/06/2012 Antonella
ALTER TABLE SBI_DATA_SET_HISTORY MODIFY COLUMN PARAMS VARCHAR(4000);

-- 14/06/2012 Davide (supporto per il multi-tenancy)
ALTER TABLE SBI_DATA_SET_HISTORY ADD COLUMN ORGANIZATION VARCHAR(20);
UPDATE SBI_DATA_SET_HISTORY set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_CHECKS set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_CHECKS  DROP INDEX XAK1SBI_CHECKS , ADD UNIQUE XAK1SBI_CHECKS (LABEL, ORGANIZATION);

UPDATE SBI_DOMAINS set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_ENGINES set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_ENGINES DROP INDEX XAK1SBI_ENGINES , ADD UNIQUE XAK1SBI_ENGINES (LABEL, ORGANIZATION);

UPDATE SBI_EXT_ROLES set ORGANIZATION = 'SPAGOBI';
commit;


DELIMITER $$

CREATE PROCEDURE UPGRADE_SBI_EXT_ROLES()
 BEGIN
 IF  ((
         select count(*)
         from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
         WHERE CONSTRAINT_NAME = 'XAK1SBI_EXT_ROLES'
         AND TABLE_NAME = 'SBI_EXT_ROLES'
         and TABLE_SCHEMA = SCHEMA()
      ) > 0 )
       THEN
          ALTER TABLE SBI_EXT_ROLES DROP INDEX XAK1SBI_EXT_ROLES;
          ALTER TABLE SBI_EXT_ROLES ADD UNIQUE XAK1SBI_EXT_ROLES (NAME, ORGANIZATION);
       ELSE
          ALTER TABLE SBI_EXT_ROLES ADD UNIQUE XAK1SBI_EXT_ROLES (NAME, ORGANIZATION);
END IF;
END $$

DELIMITER ;
CALL UPGRADE_SBI_EXT_ROLES();

DROP PROCEDURE IF EXISTS UPGRADE_SBI_EXT_ROLES;

UPDATE SBI_FUNC_ROLE set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_FUNCTIONS set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_FUNCTIONS  DROP INDEX XAK1SBI_FUNCTIONS , ADD UNIQUE XAK1SBI_FUNCTIONS (CODE, ORGANIZATION);

UPDATE SBI_LOV set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_LOV DROP INDEX XAK1SBI_LOV  , ADD UNIQUE XAK1SBI_LOV (LABEL, ORGANIZATION);

UPDATE SBI_OBJ_FUNC set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_OBJ_PAR set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_OBJ_STATE set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_OBJECTS set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_OBJECTS DROP INDEX XAK1SBI_OBJECTS , ADD UNIQUE XAK1SBI_OBJECTS (LABEL, ORGANIZATION); 

UPDATE SBI_OBJECTS_RATING set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_PARAMETERS set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_PARAMETERS DROP INDEX XAK1SBI_PARAMETERS , ADD UNIQUE XAK1SBI_PARAMETERS (LABEL, ORGANIZATION); 

UPDATE SBI_PARUSE set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_PARUSE_CK set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_PARUSE_DET set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_SUBREPORTS set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_OBJ_PARUSE set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_EVENTS set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_EVENTS_LOG set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_AUDIT set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_GEO_MAPS set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_GEO_MAPS DROP INDEX XAK1SBI_GEO_MAPS , ADD UNIQUE XAK1SBI_GEO_MAPS (NAME, ORGANIZATION);

UPDATE SBI_GEO_FEATURES set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_GEO_FEATURES DROP INDEX XAK1SBI_GEO_FEATURES , ADD UNIQUE XAK1SBI_GEO_FEATURES (NAME, ORGANIZATION);

UPDATE SBI_GEO_MAP_FEATURES set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_VIEWPOINTS set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_DATA_SOURCE set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_DATA_SOURCE DROP INDEX XAK1SBI_DATA_SOURCE , ADD UNIQUE XAK1SBI_DATA_SOURCE (LABEL, ORGANIZATION);

UPDATE SBI_BINARY_CONTENTS set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_BINARY_CONTENTS DROP INDEX XAK1SBI_BINARY_CONTENTS;

UPDATE SBI_OBJECT_TEMPLATES set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_OBJECT_TEMPLATES DROP INDEX XAK1SBI_OBJECT_TEMPLATES;

UPDATE SBI_OBJECT_NOTES set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_OBJECT_NOTES DROP INDEX XAK1SBI_OBJECT_NOTES;

UPDATE SBI_SUBOBJECTS set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_SUBOBJECTS DROP INDEX XAK1SBI_SUBOBJECTS;

UPDATE SBI_SNAPSHOTS set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_SNAPSHOTS DROP INDEX XAK1SBI_SNAPSHOTS;

UPDATE SBI_USER_FUNC set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_USER_FUNC DROP INDEX XAK1SBI_USER_FUNC;

ALTER TABLE SBI_ROLE_TYPE_USER_FUNC DROP INDEX XAK1SBI_ROLE_TYPE_USER_FUNC;

UPDATE SBI_DOSSIER_PRES set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_DOSSIER_PRES DROP INDEX XAK1SBI_DOSSIER_PRES;

UPDATE SBI_DOSSIER_TEMP set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_DOSSIER_TEMP DROP INDEX XAK1SBI_DOSSIER_TEMP;

UPDATE SBI_DOSSIER_BIN_TEMP set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_DOSSIER_BIN_TEMP DROP INDEX XAK1SBI_DOSSIER_BIN_TEMP;

UPDATE SBI_DIST_LIST set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_DIST_LIST DROP INDEX XAK1SBI_SBI_DIST_LIST, ADD UNIQUE XAK1SBI_SBI_DIST_LIST (NAME, ORGANIZATION);

UPDATE SBI_DIST_LIST_USER set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_DIST_LIST_USER DROP INDEX XAK1SBI_DIST_LIST_USER;

UPDATE SBI_DIST_LIST_OBJECTS set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_DIST_LIST_OBJECTS DROP INDEX XAK1SBI_DIST_LIST_OBJECTS;

UPDATE SBI_REMEMBER_ME set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_REMEMBER_ME DROP INDEX XAK1SBI_REMEMBER_ME;

UPDATE SBI_DATA_SET set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_DATA_SET DROP INDEX XAK1SBI_DATA_SET , ADD UNIQUE XAK1SBI_DATA_SET (LABEL, ORGANIZATION);

UPDATE SBI_MENU set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_MENU_ROLE set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_KPI_ROLE set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_KPI_ROLE DROP INDEX XAK1SBI_KPI_ROLE;

UPDATE SBI_KPI set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_KPI DROP INDEX XAK1SBI_KPI , ADD UNIQUE XAK1SBI_KPI (CODE, ORGANIZATION);

UPDATE SBI_KPI_DOCUMENTS set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_MEASURE_UNIT set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_THRESHOLD set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_THRESHOLD DROP INDEX XIF1SBI_THRESHOLD , ADD UNIQUE XIF1SBI_THRESHOLD (CODE, ORGANIZATION);

UPDATE SBI_THRESHOLD_VALUE set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_KPI_MODEL set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_KPI_MODEL DROP INDEX XIF1SBI_KPI_MODEL ,  ADD UNIQUE XIF1SBI_KPI_MODEL (KPI_MODEL_LBL, ORGANIZATION);

UPDATE SBI_KPI_PERIODICITY set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_KPI_PERIODICITY DROP INDEX XIF1SBI_KPI_PERIODICITY , ADD UNIQUE XIF1SBI_KPI_PERIODICITY (NAME, ORGANIZATION);

UPDATE SBI_KPI_INSTANCE set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_KPI_INST_PERIOD set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_KPI_INSTANCE_HISTORY set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_KPI_INSTANCE_HISTORY DROP INDEX XIF1SBI_KPI_INST_HISTORY;

UPDATE SBI_KPI_VALUE set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_KPI_VALUE DROP INDEX XIF1SBI_KPI_VALUE;

UPDATE SBI_KPI_MODEL_INST set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_KPI_MODEL_INST DROP INDEX XAK2SBI_KPI_MODEL_INST , ADD UNIQUE XAK2SBI_KPI_MODEL_INST (LABEL, ORGANIZATION);

UPDATE SBI_RESOURCES set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_RESOURCES DROP INDEX XIF1SBI_RESOURCES , ADD UNIQUE XIF1SBI_RESOURCES (RESOURCE_CODE, ORGANIZATION);

UPDATE SBI_KPI_MODEL_RESOURCES set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_ALARM set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_ALARM DROP INDEX XIF1SBI_ALARM , ADD UNIQUE XIF1SBI_ALARM (LABEL, ORGANIZATION);

UPDATE SBI_ALARM_EVENT set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_ALARM_CONTACT set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_ALARM_CONTACT DROP INDEX XIF1SBI_ALARM_CONTACT , ADD UNIQUE XIF1SBI_ALARM_CONTACT (NAME, ORGANIZATION);

UPDATE SBI_OBJ_METADATA set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_OBJ_METADATA DROP INDEX XAK1SBI_OBJ_METADATA , ADD UNIQUE XAK1SBI_OBJ_METADATA (LABEL, ORGANIZATION);

UPDATE SBI_OBJ_METACONTENTS set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_CONFIG set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_USER set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_ATTRIBUTE set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_ATTRIBUTE ADD UNIQUE XAK1SBI_USER ( ATTRIBUTE_NAME, ORGANIZATION );

UPDATE SBI_USER_ATTRIBUTES set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_EXT_USER_ROLES set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_UDP set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_UDP DROP INDEX XAK1SBI_UDP , ADD UNIQUE XAK1SBI_UDP (LABEL, ORGANIZATION);

UPDATE SBI_UDP_VALUE set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_KPI_REL set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_KPI_ERROR set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_ORG_UNIT set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_ORG_UNIT DROP INDEX XIF1SBI_ORG_UNIT , ADD UNIQUE XIF1SBI_ORG_UNIT (LABEL, NAME, ORGANIZATION);

UPDATE SBI_ORG_UNIT_HIERARCHIES set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_ORG_UNIT_HIERARCHIES DROP INDEX XIF1SBI_ORG_UNIT_HIERARCHIES , ADD UNIQUE XIF1SBI_ORG_UNIT_HIERARCHIES (LABEL, COMPANY, ORGANIZATION);

commit;

UPDATE SBI_ORG_UNIT_NODES set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_ORG_UNIT_GRANT set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_ORG_UNIT_GRANT DROP INDEX XIF1SBI_ORG_UNIT_GRANT , ADD UNIQUE XIF1SBI_ORG_UNIT_GRANT (LABEL, ORGANIZATION);

UPDATE SBI_ORG_UNIT_GRANT_NODES set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_GOAL set ORGANIZATION = 'SPAGOBI';
commit;
ALTER TABLE SBI_GOAL ADD UNIQUE XIF1SBI_SBI_GOAL (NAME, ORGANIZATION);

UPDATE SBI_GOAL_HIERARCHY set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_GOAL_KPI set ORGANIZATION = 'SPAGOBI';
commit;

UPDATE SBI_OBJ_PARVIEW set ORGANIZATION = 'SPAGOBI';
commit;

ALTER TABLE SBI_I18N_MESSAGES ADD COLUMN USER_IN VARCHAR( 100 ) NOT NULL;
ALTER TABLE SBI_I18N_MESSAGES ADD COLUMN USER_UP VARCHAR( 100 );
ALTER TABLE SBI_I18N_MESSAGES ADD COLUMN USER_DE VARCHAR( 100 );
ALTER TABLE SBI_I18N_MESSAGES ADD COLUMN TIME_IN TIMESTAMP NOT NULL;
ALTER TABLE SBI_I18N_MESSAGES ADD COLUMN TIME_UP TIMESTAMP NULL DEFAULT NULL;
ALTER TABLE SBI_I18N_MESSAGES ADD COLUMN TIME_DE TIMESTAMP NULL DEFAULT NULL;
ALTER TABLE SBI_I18N_MESSAGES ADD COLUMN SBI_VERSION_IN VARCHAR( 10 );
ALTER TABLE SBI_I18N_MESSAGES ADD COLUMN SBI_VERSION_UP VARCHAR( 10 );
ALTER TABLE SBI_I18N_MESSAGES ADD COLUMN SBI_VERSION_DE VARCHAR( 10 );
ALTER TABLE SBI_I18N_MESSAGES ADD COLUMN META_VERSION VARCHAR( 100 );
ALTER TABLE SBI_I18N_MESSAGES ADD COLUMN ORGANIZATION VARCHAR( 20 );

UPDATE SBI_I18N_MESSAGES set ORGANIZATION = 'SPAGOBI';
commit;

ALTER TABLE SBI_I18N_MESSAGES DROP PRIMARY KEY , ADD PRIMARY KEY (LANGUAGE_CD, LABEL, ORGANIZATION);

UPDATE SBI_KPI_COMMENTS set ORGANIZATION = 'SPAGOBI';
commit;

CREATE TABLE SBI_ORGANIZATIONS (
       ID     INTEGER NOT NULL,
       NAME   VARCHAR(20),
       UNIQUE XAK1SBI_ORGANIZATIONS (NAME),
       PRIMARY KEY (ID)
)ENGINE=InnoDB;

INSERT INTO SBI_ORGANIZATIONS(ID,NAME) VALUES (1,'SPAGOBI');
INSERT INTO hibernate_sequences(NEXT_VAL,SEQUENCE_NAME) VALUES (2,'SBI_ORGANIZATIONS');
commit;

SET FOREIGN_KEY_CHECKS = 0;

update QRTZ_JOB_DETAILS set JOB_GROUP = CONCAT('SPAGOBI/' , JOB_GROUP);
update QRTZ_TRIGGERS set JOB_GROUP = CONCAT('SPAGOBI/' , JOB_GROUP);
update QRTZ_JOB_LISTENERS set JOB_GROUP = CONCAT('SPAGOBI/' , JOB_GROUP);
update QRTZ_FIRED_TRIGGERS set JOB_GROUP = CONCAT('SPAGOBI/' , JOB_GROUP);

commit;

SET FOREIGN_KEY_CHECKS = 1;

-- 19/06/2012 Davide: MAXIMIZER_ENABLED column for manual input on analytical drivers' modality 
ALTER TABLE SBI_PARUSE ADD COLUMN MAXIMIZER_ENABLED BOOLEAN DEFAULT FALSE;
UPDATE SBI_PARUSE SET MAXIMIZER_ENABLED = FALSE;
commit;

-- 17/07/2012 Davide: new FinalUsersManagement user functionality 
INSERT INTO SBI_USER_FUNC (USER_FUNCT_ID, NAME, DESCRIPTION, USER_IN, TIME_IN)
    VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_USER_FUNC'), 
    'FinalUsersManagement','FinalUsersManagement', 'server', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_USER_FUNC';
commit;

INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'USER' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'FinalUsersManagement'));
commit;

ALTER TABLE SBI_EXT_ROLES ADD COLUMN MANAGE_USERS BOOLEAN DEFAULT FALSE;

-- 30/07/2012 Antonella: Deleted 'DATE_DEFAULT' as parameter type. The only parameters types usable are: date, num and string!
update SBI_PARAMETERS set PAR_TYPE_CD = 'DATE', PAR_TYPE_ID = 
	(select VALUE_ID from SBI_DOMAINS where value_cd = 'DATE' and domain_cd = 'PAR_TYPE') WHERE PAR_TYPE_CD = 'DATE_DEFAULT';
delete from SBI_DOMAINS  where VALUE_NM = 'sbidomains.nm.date.default';
commit;


-- 11/01/2014 Andrea: added some new selection modalities for analytical driver to domain table and moved multiplicity selction to document detail
-- see: SPAGOBI-1043
DELETE FROM sbi_domains WHERE domain_cd = 'SELECTION_TYPE';

INSERT INTO sbi_domains (VALUE_ID, VALUE_CD, VALUE_NM, DOMAIN_CD, DOMAIN_NM, VALUE_DS, USER_IN, ORGANIZATION, TIME_IN)
VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'), 'LIST', 'sbidomains.nm.list', 'SELECTION_TYPE', 'Selection modality of parameter values', 'sbidomains.ds.list', 'SPAGOBI', 'SPAGOBI', current_timestamp);
UPDATE hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
COMMIT;

INSERT INTO sbi_domains (VALUE_ID, VALUE_CD, VALUE_NM, DOMAIN_CD, DOMAIN_NM, VALUE_DS, USER_IN, ORGANIZATION, TIME_IN)
VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'), 'LOOKUP', 'sbidomains.nm.lookup', 'SELECTION_TYPE', 'Selection modality of parameter values', 'sbidomains.ds.lookup', 'SPAGOBI', 'SPAGOBI', current_timestamp);
UPDATE hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
COMMIT;

INSERT INTO sbi_domains (VALUE_ID, VALUE_CD, VALUE_NM, DOMAIN_CD, DOMAIN_NM, VALUE_DS, USER_IN, ORGANIZATION, TIME_IN)
VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'), 'SLIDER', 'sbidomains.nm.slider', 'SELECTION_TYPE', 'Selection modality of parameter values', 'sbidomains.ds.slider', 'SPAGOBI', 'SPAGOBI', current_timestamp);
UPDATE hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
COMMIT;

INSERT INTO sbi_domains (VALUE_ID, VALUE_CD, VALUE_NM, DOMAIN_CD, DOMAIN_NM, VALUE_DS, USER_IN, ORGANIZATION, TIME_IN)
VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'), 'TREE', 'sbidomains.nm.tree', 'SELECTION_TYPE', 'Selection modality of parameter values', 'sbidomains.ds.tree', 'SPAGOBI', 'SPAGOBI', current_timestamp);
UPDATE hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
COMMIT;

INSERT INTO sbi_domains (VALUE_ID, VALUE_CD, VALUE_NM, DOMAIN_CD, DOMAIN_NM, VALUE_DS, USER_IN, ORGANIZATION, TIME_IN)
VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'), 'COMBOBOX', 'sbidomains.nm.combobox', 'SELECTION_TYPE', 'Selection modality of parameter values', 'sbidomains.ds.combobox', 'SPAGOBI', 'SPAGOBI', current_timestamp);
UPDATE hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
COMMIT;

UPDATE sbi_obj_par SET MULT_FL=0;
UPDATE sbi_obj_par SET MULT_FL=1 WHERE par_id IN (SELECT a.par_id FROM   sbi_parameters a, sbi_paruse m
WHERE a.par_id = m.par_id and selection_type = 'CHECK_LIST');
UPDATE sbi_paruse SET selection_type='LOOKUP' WHERE selection_type = 'CHECK_LIST'OR selection_type = 'LIST'

COMMIT;

-- 11/01/2014 Andrea: moved mandatory selction to document detail
-- see: SPAGOBI-1043
UPDATE sbi_obj_par SET REQ_FL=0;
UPDATE sbi_obj_par SET REQ_FL=1 WHERE par_id IN (SELECT a.par_id FROM   sbi_parameters a, sbi_paruse m, sbi_paruse_ck r, sbi_checks c
WHERE a.par_id = m.par_id and m.use_id = r.use_id and r.check_id = c.check_id and c.value_type_cd = 'MANDATORY')

-- 29/01/2013 Davide: Catalogues for meta-models and artifacts
INSERT INTO SBI_USER_FUNC (USER_FUNCT_ID, NAME, DESCRIPTION, USER_IN, TIME_IN)
    VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_USER_FUNC'), 
    'ArtifactCatalogueManagement','ArtifactCatalogueManagement', 'server', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_USER_FUNC';
commit;

INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'ADMIN' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'ArtifactCatalogueManagement'));
commit;

INSERT INTO SBI_USER_FUNC (USER_FUNCT_ID, NAME, DESCRIPTION, USER_IN, TIME_IN)
    VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_USER_FUNC'), 
    'MetaModelsCatalogueManagement','MetaModelsCatalogueManagement', 'server', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_USER_FUNC';
commit;

INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'ADMIN' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'MetaModelsCatalogueManagement'));
commit;

CREATE TABLE SBI_META_MODELS (
       ID                   INTEGER NOT NULL,
       NAME                 VARCHAR(100) NOT NULL,
       DESCR                VARCHAR(500) NULL,
       USER_IN              VARCHAR(100) NOT NULL,
       USER_UP              VARCHAR(100),
       USER_DE              VARCHAR(100),
       TIME_IN              TIMESTAMP NOT NULL,
       TIME_UP              TIMESTAMP NULL DEFAULT NULL,
       TIME_DE              TIMESTAMP NULL DEFAULT NULL,
       SBI_VERSION_IN       VARCHAR(10),
       SBI_VERSION_UP       VARCHAR(10),
       SBI_VERSION_DE       VARCHAR(10),
       META_VERSION         VARCHAR(100),
       ORGANIZATION         VARCHAR(20),
       CONSTRAINT XAK1SBI_META_MODELS UNIQUE (NAME, ORGANIZATION),
       PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE SBI_META_MODELS_VERSIONS (
        ID                   INTEGER NOT NULL,
        MODEL_ID             INTEGER NOT NULL,
        CONTENT              MEDIUMBLOB NOT NULL,
        NAME                 VARCHAR(100),  
        PROG                 INTEGER,
        CREATION_DATE        TIMESTAMP NULL DEFAULT NULL,
        CREATION_USER        VARCHAR(50) NOT NULL, 
        ACTIVE               BOOLEAN,  
        USER_IN              VARCHAR(100) NOT NULL,
        USER_UP              VARCHAR(100),
        USER_DE              VARCHAR(100),
        TIME_IN              TIMESTAMP NOT NULL,
        TIME_UP              TIMESTAMP NULL DEFAULT NULL,
        TIME_DE              TIMESTAMP NULL DEFAULT NULL,
        SBI_VERSION_IN       VARCHAR(10),
        SBI_VERSION_UP       VARCHAR(10),
        SBI_VERSION_DE       VARCHAR(10),
        META_VERSION         VARCHAR(100),
        ORGANIZATION         VARCHAR(20),
        PRIMARY KEY (ID)
) ENGINE=InnoDB;

ALTER TABLE SBI_META_MODELS_VERSIONS ADD CONSTRAINT FK_SBI_META_MODELS_VERSIONS_1 FOREIGN KEY ( MODEL_ID ) REFERENCES SBI_META_MODELS( ID ) ON DELETE CASCADE;

CREATE TABLE SBI_ARTIFACTS (
       ID                   INTEGER NOT NULL,
       NAME                 VARCHAR(100) NOT NULL,
       DESCR                VARCHAR(500) NULL,
       TYPE                 VARCHAR(50) NULL,
       USER_IN              VARCHAR(100) NOT NULL,
       USER_UP              VARCHAR(100),
       USER_DE              VARCHAR(100),
       TIME_IN              TIMESTAMP NOT NULL,
       TIME_UP              TIMESTAMP NULL DEFAULT NULL,
       TIME_DE              TIMESTAMP NULL DEFAULT NULL,
       SBI_VERSION_IN       VARCHAR(10),
       SBI_VERSION_UP       VARCHAR(10),
       SBI_VERSION_DE       VARCHAR(10),
       META_VERSION         VARCHAR(100),
       ORGANIZATION         VARCHAR(20),
       CONSTRAINT XAK1SBI_ARTIFACTS UNIQUE (NAME, TYPE, ORGANIZATION),
       PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE SBI_ARTIFACTS_VERSIONS (
        ID                   INTEGER NOT NULL,
        ARTIFACT_ID          INTEGER NOT NULL,
        CONTENT              MEDIUMBLOB NOT NULL,
        NAME                 VARCHAR(100),  
        PROG                 INTEGER,
        CREATION_DATE        TIMESTAMP NULL DEFAULT NULL, 
        CREATION_USER        VARCHAR(50) NOT NULL, 
        ACTIVE               BOOLEAN,  
        USER_IN              VARCHAR(100) NOT NULL,
        USER_UP              VARCHAR(100),
        USER_DE              VARCHAR(100),
        TIME_IN              TIMESTAMP NOT NULL,
        TIME_UP              TIMESTAMP NULL DEFAULT NULL,
        TIME_DE              TIMESTAMP NULL DEFAULT NULL,
        SBI_VERSION_IN       VARCHAR(10),
        SBI_VERSION_UP       VARCHAR(10),
        SBI_VERSION_DE       VARCHAR(10),
        META_VERSION         VARCHAR(100),
        ORGANIZATION         VARCHAR(20),
        PRIMARY KEY (ID)
) ENGINE=InnoDB;

ALTER TABLE SBI_ARTIFACTS_VERSIONS ADD CONSTRAINT FK_SBI_ARTIFACTS_VERSIONS_1 FOREIGN KEY ( ARTIFACT_ID ) REFERENCES SBI_ARTIFACTS( ID ) ON DELETE CASCADE;

-- 30/01/2013 Davide: analytical drivers' default values
ALTER TABLE SBI_PARUSE ADD COLUMN DEFAULT_LOV_ID INTEGER NULL;
ALTER TABLE SBI_PARUSE ADD COLUMN DEFAULT_FORMULA VARCHAR(4000) NULL;
ALTER TABLE SBI_PARUSE ADD CONSTRAINT FK_SBI_PARUSE_3 FOREIGN KEY ( DEFAULT_LOV_ID ) REFERENCES SBI_LOV ( LOV_ID ) ON DELETE RESTRICT;

-- 08/04/2013 Davide: definition of "create Worksheet from dataset user functionality"
INSERT INTO SBI_USER_FUNC (USER_FUNCT_ID, NAME, DESCRIPTION, USER_IN, TIME_IN)
    VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_USER_FUNC'), 
    'CreateWorksheetFromDatasetUserFunctionality','CreateWorksheetFromDatasetUserFunctionality', 'server', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_USER_FUNC';
commit;
INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'USER' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'CreateWorksheetFromDatasetUserFunctionality'));
commit;

--- 23/04/2013 Marco: added column Category_Id on SBI_META_MODELS
ALTER TABLE SBI_META_MODELS ADD COLUMN CATEGORY_ID INTEGER NULL;

--- 23/04/2013 Anto: added column for persistence dataset management
ALTER TABLE SBI_DATA_SET_HISTORY ADD COLUMN IS_PERSISTED BOOLEAN DEFAULT FALSE;
ALTER TABLE SBI_DATA_SET_HISTORY ADD COLUMN DATA_SOURCE_PERSIST_ID INTEGER NULL;
ALTER TABLE SBI_DATA_SET_HISTORY ADD COLUMN IS_FLAT_DATASET BOOLEAN DEFAULT FALSE;
ALTER TABLE SBI_DATA_SET_HISTORY ADD COLUMN FLAT_TABLE_NAME VARCHAR(50) NULL;
ALTER TABLE SBI_DATA_SET_HISTORY ADD COLUMN DATA_SOURCE_FLAT_ID INTEGER NULL;

ALTER TABLE SBI_DATA_SET_HISTORY ADD CONSTRAINT FK_SBI_DATA_SET_DS3 FOREIGN KEY ( DATA_SOURCE_PERSIST_ID ) REFERENCES SBI_DATA_SOURCE( DS_ID ) ON DELETE CASCADE;
ALTER TABLE SBI_DATA_SET_HISTORY ADD CONSTRAINT FK_SBI_DATA_SET_DS4 FOREIGN KEY ( DATA_SOURCE_FLAT_ID ) REFERENCES SBI_DATA_SOURCE( DS_ID ) ON DELETE CASCADE;

--- 06/05/2013 Marco: added table SBI_EXT_ROLES_CATEGORY
CREATE TABLE  SBI_EXT_ROLES_CATEGORY (
  EXT_ROLE_ID INTEGER NOT NULL,
  CATEGORY_ID INTEGER NOT NULL,
  PRIMARY KEY (EXT_ROLE_ID,CATEGORY_ID),
  KEY FK_SB_EXT_ROLES_META_MODEL_CATEGORY_2 (CATEGORY_ID),
  CONSTRAINT FK_SB_EXT_ROLES_META_MODEL_CATEGORY_1 FOREIGN KEY (EXT_ROLE_ID) REFERENCES SBI_EXT_ROLES (EXT_ROLE_ID),
  CONSTRAINT FK_SB_EXT_ROLES_META_MODEL_CATEGORY_2 FOREIGN KEY (CATEGORY_ID) REFERENCES SBI_DOMAINS (VALUE_ID)
) ENGINE=InnoDB ;

-- 27/05/2013 Antonella: modifies for refactoring dataset:
CREATE TABLE SBI_DATA_SET_TEMP (
	   DS_ID 		   		        INTEGER NOT NULL ,
	   VERSION_NUM	   		 	  INTEGER NOT NULL,
	   ACTIVE		   		 	      BOOLEAN NOT NULL,
	   DESCR 		   		        VARCHAR(160), 
	   LABEL	 	   		        VARCHAR(50) NOT NULL,
	   NAME	   	   			      VARCHAR(50) NOT NULL,  
 	   
	   OBJECT_TYPE   		 	    VARCHAR(50),
	   DS_METADATA    		 	  TEXT,
	   PARAMS         		 	  VARCHAR(4000),
	   CATEGORY_ID    		 	  INTEGER,
	   TRANSFORMER_ID 		 	  INTEGER,
     PIVOT_COLUMN   		 	  VARCHAR(50),
	   PIVOT_ROW      		 	  VARCHAR(50),
	   PIVOT_VALUE   		 	    VARCHAR(50),
	   NUM_ROWS	   		 	      BOOLEAN DEFAULT FALSE,	
	   IS_PERSISTED  		 	    BOOLEAN DEFAULT FALSE,
	   DATA_SOURCE_PERSIST_ID INTEGER NULL,
	   IS_FLAT_DATASET 		    BOOLEAN DEFAULT FALSE,
	   FLAT_TABLE_NAME 		    VARCHAR(50) NULL,
	   DATA_SOURCE_FLAT_ID 	  INTEGER NULL,
	   
	   CONFIGURATION          TEXT NULL,    
	   	   
	   USER_IN                VARCHAR(100) NOT NULL,
	   USER_UP                VARCHAR(100),
	   USER_DE                VARCHAR(100),
	   TIME_IN                TIMESTAMP NOT NULL,
	   TIME_UP                TIMESTAMP NULL DEFAULT NULL,
	   TIME_DE                TIMESTAMP NULL DEFAULT NULL,
	   SBI_VERSION_IN         VARCHAR(10),
	   SBI_VERSION_UP         VARCHAR(10),
	   SBI_VERSION_DE         VARCHAR(10),
	   META_VERSION           VARCHAR(100),
	   ORGANIZATION           VARCHAR(20), 
	   
     UNIQUE XAK1SBI_DATA_SET (LABEL, VERSION_NUM, ORGANIZATION),
     PRIMARY KEY (DS_ID, VERSION_NUM)
) ENGINE=InnoDB;

INSERT SBI_DATA_SET_TEMP (DS_ID, VERSION_NUM, ACTIVE,  LABEL, DESCR, NAME, OBJECT_TYPE, DS_METADATA, PARAMS, CATEGORY_ID, TRANSFORMER_ID, PIVOT_COLUMN, PIVOT_ROW, PIVOT_VALUE, NUM_ROWS, IS_PERSISTED, 
DATA_SOURCE_PERSIST_ID, IS_FLAT_DATASET, FLAT_TABLE_NAME, DATA_SOURCE_FLAT_ID, USER_IN, USER_UP, USER_DE, TIME_IN, TIME_UP, TIME_DE, SBI_VERSION_IN, SBI_VERSION_UP, SBI_VERSION_DE,
META_VERSION, ORGANIZATION, CONFIGURATION) 
SELECT DS.DS_ID, ds_h.VERSION_NUM, ds_h.ACTIVE, ds.LABEL, ds.DESCR, ds.name,
ds_h.OBJECT_TYPE, ds_h.DS_METADATA,
ds_h.PARAMS, ds_h.CATEGORY_ID, ds_h.TRANSFORMER_ID, ds_h.PIVOT_COLUMN, ds_h.PIVOT_ROW,
ds_h.PIVOT_VALUE, ds_h.NUM_ROWS, ds_h.IS_PERSISTED, ds_h.DATA_SOURCE_PERSIST_ID, 
ds_h.IS_FLAT_DATASET, ds_h.FLAT_TABLE_NAME, ds_h.DATA_SOURCE_FLAT_ID, ds_h.USER_IN, 
null as USER_UP,null as USER_DE, ds_h.TIME_IN, null as TIME_UP, null as TIME_DE,
ds_h.SBI_VERSION_IN, null as SBI_VERSION_UP,  null as SBI_VERSION_DE, ds_h.META_VERSION,
ds_h.ORGANIZATION,
case when ds_h.OBJECT_TYPE = 'SbiQueryDataSet' then 
concat('{"Query":"',REPLACE(ds_h.QUERY,'"','\\"'),'","queryScript":"',REPLACE(COALESCE(DS_H.QUERY_SCRIPT,''),'"','\\"'),'","queryScriptLanguage":"',COALESCE(QUERY_SCRIPT_LANGUAGE,''),'","dataSource":"',COALESCE(CAST((SELECT LABEL FROM SBI_DATA_SOURCE WHERE DS_ID = DATA_SOURCE_ID) AS CHAR),''),'"}')  
WHEN ds_h.OBJECT_TYPE = 'SbiFileDataSet' then 
CONCAT('{"fileName":"',COALESCE(DS_H.FILE_NAME,''),'"}')
WHEN ds_h.OBJECT_TYPE = 'SbiFileDataSet' then 
CONCAT('{"SbiJClassDataSet":"',COALESCE(DS_H.JCLASS_NAME,''),'"}')
WHEN ds_h.OBJECT_TYPE = 'SbiWSDataSet' then 
CONCAT('{"wsAddress":"',COALESCE(DS_H.ADRESS,''),'","wsOperation":"',COALESCE(DS_H.OPERATION,''),'"}')
WHEN ds_h.OBJECT_TYPE = 'SbiScriptDataSet' then 
CONCAT('{"Script":"',REPLACE(COALESCE(DS_H.SCRIPT,''),'"','\\"'),'","scriptLanguage":"',COALESCE(DS_H.LANGUAGE_SCRIPT,''),'"}')
WHEN ds_h.OBJECT_TYPE = 'SbiCustomDataSet' then 
CONCAT('{"customData":"',REPLACE(COALESCE(DS_H.CUSTOM_DATA,'"{}"'),'"','\\"'),'","jClassName":"',COALESCE(DS_H.JCLASS_NAME,''),'"}')
WHEN ds_h.OBJECT_TYPE = 'SbiQbeDataSet' then 
CONCAT('{"qbeDatamarts":"',COALESCE(DS_H.DATAMARTS,''),'","qbeDataSource":"',COALESCE(CAST((SELECT LABEL FROM SBI_DATA_SOURCE WHERE DS_ID = DATA_SOURCE_ID) AS CHAR),''),'","qbeJSONQuery":"',REPLACE(COALESCE(DS_H.JSON_QUERY,''),'"','\\"'),'"}')
end AS CONFIGURATION
FROM 
SBI_DATA_SET DS INNER JOIN SBI_DATA_SET_HISTORY DS_H ON (DS.DS_ID = DS_H.DS_ID)
order by ds_id, version_num;

commit;

--DROP TABLE SBI_DATA_SET_HISTORY CASCADE;   -- to do at the end, when all it's ended correctly!
--DROP TABLE SBI_DATA_SET CASCADE;           -- to do at the end, when all it's ended correctly!
RENAME TABLE SBI_DATA_SET TO SBI_DATA_SET_OLD;
RENAME TABLE SBI_DATA_SET_HISTORY TO SBI_DATA_SET_HISTORY_OLD;
RENAME TABLE SBI_DATA_SET_TEMP TO SBI_DATA_SET;
-- to do only after drop stmt
--ALTER TABLE SBI_DATA_SET ADD CONSTRAINT FK_SBI_DATA_SET_T  FOREIGN KEY ( TRANSFORMER_ID ) REFERENCES SBI_DOMAINS ( VALUE_ID ) ON DELETE CASCADE;
--ALTER TABLE SBI_DATA_SET ADD CONSTRAINT FK_SBI_DATA_SET_CAT  FOREIGN KEY (CATEGORY_ID) REFERENCES SBI_DOMAINS (VALUE_ID) ON DELETE CASCADE ON UPDATE RESTRICT;
--ALTER TABLE SBI_DATA_SET ADD CONSTRAINT FK_SBI_DATA_SET_DS3 FOREIGN KEY ( DATA_SOURCE_PERSIST_ID ) REFERENCES SBI_DATA_SOURCE( DS_ID ) ON DELETE CASCADE;
--ALTER TABLE SBI_DATA_SET ADD CONSTRAINT FK_SBI_DATA_SET_DS4 FOREIGN KEY ( DATA_SOURCE_FLAT_ID ) REFERENCES SBI_DATA_SOURCE( DS_ID ) ON DELETE CASCADE;

--DROP OLDER FK TO SBI_DATA_SET
ALTER TABLE SBI_LOV DROP FOREIGN KEY FK_SBI_LOV_2;
ALTER TABLE SBI_OBJECTS DROP FOREIGN KEY FK_SBI_OBJECTS_7;

--per la tabella sbi_kpi la fk non ha nome esplicito: bisogna rilevarlo tramite query e definire così lo stmt di drop (per ora manualemnte!)
select constraint_name from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE constraint_schema = '<SCHEMA_DB_NAME>' and
referenced_table_name = 'SBI_DATA_SET'  and table_name = 'SBI_KPI';
ALTER TABLE sbi_kpi DROP FOREIGN KEY <FK_NAME_GETTED>;

-- 28/05/2013 Antonella: insert records for selfservice dataset management 
INSERT INTO SBI_USER_FUNC (USER_FUNCT_ID, NAME, DESCRIPTION, USER_IN, TIME_IN)
    VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_USER_FUNC'), 
    'SelfServiceDatasetManagement','SelfServiceDatasetManagement', 'server', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_USER_FUNC';
commit;
INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'USER' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'SelfServiceDatasetManagement'));
INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'ADMIN' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'SelfServiceDatasetManagement'));
INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'DEV_ROLE' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'SelfServiceDatasetManagement'));
INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'TEST_ROLE' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'SelfServiceDatasetManagement'));
INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'MODEL_ADMIN' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'SelfServiceDatasetManagement'));
commit;

-- 29/05/2013 Davide: Worksheet engine is able to use datasets
UPDATE SBI_ENGINES SET USE_DATASET = TRUE WHERE DRIVER_NM = 'it.eng.spagobi.engines.drivers.worksheet.WorksheetDriver';
commit;


-- 07/06/2013 Alberto: insert records for selfservice metamodels management 

INSERT INTO SBI_USER_FUNC (USER_FUNCT_ID, NAME, DESCRIPTION, USER_IN, TIME_IN)
    VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_USER_FUNC'), 
    'SelfServiceMetaModelManagement','SelfServiceMetaModelManagement', 'server', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_USER_FUNC';
commit;
INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'USER' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'SelfServiceMetaModelManagement'));
INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'ADMIN' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'SelfServiceMetaModelManagement'));
INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'DEV_ROLE' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'SelfServiceMetaModelManagement'));
INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'TEST_ROLE' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'SelfServiceMetaModelManagement'));
INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'MODEL_ADMIN' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'SelfServiceMetaModelManagement'));
commit;

-- 11/06/2013 Alberto: add the data source column for the metamodels 

ALTER TABLE SBI_META_MODELS add DATA_SOURCE_ID INTEGER;
ALTER TABLE SBI_META_MODELS ADD CONSTRAINT FK_SBIDATA_SOURCE FOREIGN KEY ( DATA_SOURCE_ID ) REFERENCES SBI_DATA_SOURCE( DS_ID );

-- 10/06/2013 Antonella: add columns for dataset PUBLIC / PRIVATE 
ALTER TABLE SBI_DATA_SET ADD COLUMN OWNER VARCHAR(50);
ALTER TABLE SBI_DATA_SET ADD COLUMN IS_PUBLIC BOOLEAN DEFAULT FALSE;

UPDATE SBI_DATA_SET SET IS_PUBLIC = TRUE, OWNER = USER_IN;
COMMIT;

-- 20/06/2013 Davide: added DATASET_FILE_MAX_SIZE config parameter
INSERT INTO SBI_CONFIG ( ID, LABEL, NAME, DESCRIPTION, IS_ACTIVE, VALUE_CHECK, VALUE_TYPE_ID, CATEGORY, USER_IN, TIME_IN) VALUES 
((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_CONFIG'), 
'SPAGOBI.DATASET_FILE_MAX_SIZE', 'DATASET FILE MAX SIZE', 'Max size for a file used as a dataset', true, '10485760',
(select VALUE_ID from SBI_DOMAINS where VALUE_CD = 'STRING' AND DOMAIN_CD = 'PAR_TYPE'), 'GENERIC_CONFIGURATION', 'biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_CONFIG';
commit;

-- 21/06/2013 Davide: fixed SPAGOBI.TIMESTAMP-FORMAT.format (was dd/MM/yyyy hh:mm:ss)
UPDATE SBI_CONFIG SET VALUE_CHECK = 'dd/MM/yyyy HH:mm:ss' WHERE LABEL = 'SPAGOBI.TIMESTAMP-FORMAT.format';
commit;

--21/06/2013 Anto: Added authorizations for final user management
ALTER TABLE SBI_EXT_ROLES ADD COLUMN SEE_DOCUMENT_BROWSER BOOLEAN DEFAULT TRUE;
ALTER TABLE SBI_EXT_ROLES ADD COLUMN SEE_FAVOURITES 	BOOLEAN DEFAULT TRUE;
ALTER TABLE SBI_EXT_ROLES ADD COLUMN SEE_SUBSCRIPTIONS 	BOOLEAN DEFAULT TRUE;
ALTER TABLE SBI_EXT_ROLES ADD COLUMN SEE_MY_DATA 		BOOLEAN DEFAULT TRUE;
ALTER TABLE SBI_EXT_ROLES ADD COLUMN SEE_TODO_LIST 		BOOLEAN DEFAULT TRUE;
ALTER TABLE SBI_EXT_ROLES ADD COLUMN CREATE_DOCUMENTS 	BOOLEAN DEFAULT TRUE;

-- 24/06/2013 Marco: Added default mandatory Dataset Metadata Properties
INSERT into SBI_DOMAINS (VALUE_ID,VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN) values ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),'fieldType','fieldType','DS_META_PROPERTY','Data Set Metadata Property','Data Set Metadata Property','biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

INSERT into SBI_DOMAINS (VALUE_ID,VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN) values ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),'type','type','DS_META_PROPERTY','Data Set Metadata Property','Data Set Metadata Property','biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

-- 25/06/2013 Marco: Added default mandatory Dataset Metadata Properties' Values
INSERT into SBI_DOMAINS (VALUE_ID,VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN) values ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),'MEASURE','MEASURE','DS_META_VALUE','Data Set Metadata Value','Data Set Metadata Value','biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

INSERT into SBI_DOMAINS (VALUE_ID,VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN) values ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),'ATTRIBUTE','ATTRIBUTE','DS_META_VALUE','Data Set Metadata Value','Data Set Metadata Value','biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

INSERT into SBI_DOMAINS (VALUE_ID,VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN) values ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),'String','String','DS_META_VALUE','Data Set Metadata Value','Data Set Metadata Value','biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

INSERT into SBI_DOMAINS (VALUE_ID,VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN) values ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),'Integer','Integer','DS_META_VALUE','Data Set Metadata Value','Data Set Metadata Value','biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

INSERT into SBI_DOMAINS (VALUE_ID,VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN) values ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),'Double','Double','DS_META_VALUE','Data Set Metadata Value','Data Set Metadata Value','biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

-- 11/07/2013 Antonella: Added PREVIEW_FILE on SBI_OBJECTS to manage img under resources/preview/images
ALTER TABLE SBI_OBJECTS ADD COLUMN PREVIEW_FILE VARCHAR(100);

-- 12/07/2013 Monica: Addedtables for community management
CREATE TABLE  SBI_COMMUNITY(
  COMMUNITY_ID int(11) NOT NULL AUTO_INCREMENT,
  NAME varchar(200) NOT NULL,
  DESCRIPTION varchar(350) DEFAULT NULL,
  OWNER char(100) NOT NULL,
  FUNCT_CODE varchar(40) DEFAULT NULL,
  CREATION_DATE timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  LAST_CHANGE_DATE timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  USER_IN varchar(100) NOT NULL,
  USER_UP varchar(100) DEFAULT NULL,
  USER_DE varchar(100) DEFAULT NULL,
  TIME_IN timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  TIME_UP timestamp NULL DEFAULT NULL,
  TIME_DE timestamp NULL DEFAULT NULL,
  SBI_VERSION_IN varchar(10) DEFAULT NULL,
  SBI_VERSION_UP varchar(10) DEFAULT NULL,
  SBI_VERSION_DE varchar(10) DEFAULT NULL,
  META_VERSION varchar(100) DEFAULT NULL,
  ORGANIZATION varchar(20) DEFAULT NULL,
  PRIMARY KEY (COMMUNITY_ID)
);

CREATE TABLE SBI_COMMUNITY_USERS (
  COMMUNITY_ID int(11) NOT NULL,
  USER_ID char(100) NOT NULL,
  CREATION_DATE timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  LAST_CHANGE_DATE timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  USER_IN varchar(100) NOT NULL,
  USER_UP varchar(100) DEFAULT NULL,
  USER_DE varchar(100) DEFAULT NULL,
  TIME_IN timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  TIME_UP timestamp NULL DEFAULT NULL,
  TIME_DE timestamp NULL DEFAULT NULL,
  SBI_VERSION_IN varchar(10) DEFAULT NULL,
  SBI_VERSION_UP varchar(10) DEFAULT NULL,
  SBI_VERSION_DE varchar(10) DEFAULT NULL,
  META_VERSION varchar(100) DEFAULT NULL,
  ORGANIZATION varchar(20) DEFAULT NULL,
  PRIMARY KEY (`COMMUNITY_ID`,`USER_ID`),
  CONSTRAINT `FK_COMMUNITY` FOREIGN KEY (`COMMUNITY_ID`) REFERENCES `SBI_COMMUNITY` (`COMMUNITY_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ;


--26/07/2013 Alberto: functionality to manage the measure catalogue
INSERT INTO SBI_USER_FUNC (USER_FUNCT_ID, NAME, DESCRIPTION, USER_IN, TIME_IN)
    VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_USER_FUNC'), 
    'MeasuresCatalogueManagement','MeasuresCatalogueManagement', 'server', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_USER_FUNC';

INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'ADMIN' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'MeasuresCatalogueManagement'));
commit;

--01/08/2013 Alberto: layers catalogue
CREATE TABLE SBI_GEO_LAYERS (
  LAYER_ID int(11) NOT NULL,
  LABEL varchar(100) NOT NULL,
  NAME varchar(100),
  DESCR varchar(100),
  LAYER_DEFINITION BLOB NOT NULL,
  TYPE varchar(40),
  USER_IN varchar(100) NOT NULL,
  USER_UP varchar(100) DEFAULT NULL,
  USER_DE varchar(100) DEFAULT NULL,
  TIME_IN timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  TIME_UP timestamp NULL DEFAULT NULL,
  TIME_DE timestamp NULL DEFAULT NULL,
  SBI_VERSION_IN varchar(10) DEFAULT NULL,
  SBI_VERSION_UP varchar(10) DEFAULT NULL,
  SBI_VERSION_DE varchar(10) DEFAULT NULL,
  META_VERSION varchar(100) DEFAULT NULL,
  ORGANIZATION varchar(20) DEFAULT NULL,
  PRIMARY KEY (`LAYER_ID`)
) ;


-- Davide 01/08/2013: solved bug https://spagobi.eng.it/jira/browse/SPAGOBI-1289
INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'ADMIN' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'CreateWorksheetFromDatasetUserFunctionality'));
INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'DEV_ROLE' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'CreateWorksheetFromDatasetUserFunctionality'));
INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'TEST_ROLE' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'CreateWorksheetFromDatasetUserFunctionality'));
INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'MODEL_ADMIN' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'CreateWorksheetFromDatasetUserFunctionality'));
commit;

-- Alberto 02/08/2013: layer catalogue

ALTER TABLE SBI_GEO_LAYERS ADD UNIQUE INDEX LABEL_UNIQUE (ORGANIZATION, LABEL ASC) ;


INSERT into SBI_DOMAINS (VALUE_ID, VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN) 
values ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),'FILE','FILE','LAYER_TYPE','Layer Type','Layer Type','');
UPDATE hibernate_sequences SET next_val = (SELECT MAX(VALUE_ID) + 1 FROM SBI_DOMAINS) WHERE sequence_name = 'SBI_DOMAINS';  

INSERT into SBI_DOMAINS (VALUE_ID, VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN) 
values ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),'WFS','WFS','LAYER_TYPE','Layer Type','Layer Type','');
UPDATE hibernate_sequences SET next_val = (SELECT MAX(VALUE_ID) + 1 FROM SBI_DOMAINS) WHERE sequence_name = 'SBI_DOMAINS';  

INSERT INTO SBI_USER_FUNC (USER_FUNCT_ID, NAME, DESCRIPTION, USER_IN, TIME_IN)
    VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_USER_FUNC'), 
    'GeoLayersManagement','GeoLayersManagement', 'server', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_USER_FUNC';

INSERT INTO SBI_ROLE_TYPE_USER_FUNC (ROLE_TYPE_ID, USER_FUNCT_ID)
    VALUES ((SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD = 'ADMIN' AND DOMAIN_CD = 'ROLE_TYPE'), 
    (SELECT USER_FUNCT_ID FROM SBI_USER_FUNC WHERE NAME = 'GeoLayersManagement'));
commit;


-- Monica 21/08/2013
ALTER TABLE SBI_COMMUNITY ADD UNIQUE INDEX NAME_UNIQUE (ORGANIZATION, NAME ASC) ; 

-- Antonella 20/08/2013: managed public/private scope of documents
ALTER TABLE SBI_OBJECTS ADD COLUMN IS_PUBLIC BOOLEAN DEFAULT FALSE;
UPDATE SBI_OBJECTS SET IS_PUBLIC = TRUE;

-- Davide 26/08/2013: added PERSIST_TABLE_NAME column
ALTER TABLE SBI_DATA_SET ADD COLUMN PERSIST_TABLE_NAME VARCHAR(50);

-- Davide 03/09/2013: REFACTORING: flat dataset is a type of dataset
ALTER TABLE SBI_DATA_SET DROP COLUMN IS_FLAT_DATASET;
ALTER TABLE SBI_DATA_SET DROP COLUMN FLAT_TABLE_NAME;
ALTER TABLE SBI_DATA_SET DROP COLUMN DATA_SOURCE_FLAT_ID;

-- Marco 25/09/2013: added IS_BASE_LAYER column to SBI_GEO_LAYERS
ALTER TABLE SBI_GEO_LAYERS ADD COLUMN IS_BASE_LAYER BOOLEAN DEFAULT FALSE;

-- Fabrizio Pajalunga 02/10/2013: added user attributes
INSERT INTO SBI_ATTRIBUTE (attribute_name,description,attribute_id,user_in,time_in,sbi_version_in,organization) values ('gender','gender',(SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'sbi_attribute'),'server_init',sysdate(),'4.0','SPAGOBI');
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_ATTRIBUTE';
commit;

INSERT INTO SBI_ATTRIBUTE (attribute_name,description,attribute_id,user_in,time_in,sbi_version_in,organization) values ('location','location',(SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'sbi_attribute'),'server_init',sysdate(),'4.0','SPAGOBI');
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_ATTRIBUTE';
commit;

INSERT INTO SBI_ATTRIBUTE (attribute_name,description,attribute_id,user_in,time_in,sbi_version_in,organization) values ('community','community',(SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'sbi_attribute'),'server_init',sysdate(),'4.0','SPAGOBI');
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_ATTRIBUTE';
commit;

INSERT INTO SBI_ATTRIBUTE (attribute_name,description,attribute_id,user_in,time_in,sbi_version_in,organization) values ('short_bio','short_bio',(SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'sbi_attribute'),'server_init',sysdate(),'4.0','SPAGOBI');
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_ATTRIBUTE';
commit;

INSERT INTO SBI_ATTRIBUTE (attribute_name,description,attribute_id,user_in,time_in,sbi_version_in,organization) values ('language','language',(SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'sbi_attribute'),'server_init',sysdate(),'4.0','SPAGOBI');
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_ATTRIBUTE';
commit;

-- Marco 11/10/2013: changed sbi_data_set primary key
ALTER TABLE SBI_DATA_SET MODIFY COLUMN ORGANIZATION VARCHAR(20) NOT NULL,
 DROP PRIMARY KEY,
 ADD PRIMARY KEY  (DS_ID, VERSION_NUM, ORGANIZATION);
 
 
-- Giulio 25/09/2013
ALTER TABLE SBI_ENGINES DROP FOREIGN KEY FK_SBI_ENGINE_3;
ALTER TABLE SBI_ENGINES  DROP COLUMN DEFAULT_DS_ID;
ALTER TABLE SBI_DATA_SET DROP COLUMN DATA_SOURCE_PERSIST_ID;

ALTER TABLE SBI_DATA_SOURCE ADD COLUMN READ_ONLY BOOLEAN DEFAULT FALSE;
ALTER TABLE SBI_DATA_SOURCE ADD COLUMN WRITE_DEFAULT BOOLEAN DEFAULT FALSE;
commit;

-- Davide 21/10/2013: removed spagobi@eng.it from SpagoBI config
UPDATE SBI_CONFIG SET VALUE_CHECK = '' WHERE VALUE_CHECK = 'spagobi@eng.it';
commit;

-- Monica 23/11/2013: added content_type mng for snapshots
ALTER TABLE SBI_SNAPSHOTS ADD COLUMN CONTENT_TYPE VARCHAR(300) NULL DEFAULT NULL  AFTER ORGANIZATION ;

-- Giulio 30/10/2013
ALTER TABLE SBI_DATA_SET ADD CONSTRAINT FK_DATA_SET_CATEGORY FOREIGN KEY (CATEGORY_ID) REFERENCES SBI_DOMAINS (VALUE_ID);
ALTER TABLE SBI_META_MODELS ADD CONSTRAINT FK_META_MODELS_CATEGORY FOREIGN KEY (CATEGORY_ID) REFERENCES SBI_DOMAINS (VALUE_ID);

-- MONICA 30/10/2013: ALTERED COLUMN LENGTH FOR RESOURCE_NAME
ALTER TABLE SBI_RESOURCES MODIFY COLUMN RESOURCE_NAME VARCHAR(200);

-- MONICA 4/11/2013: bug on final user functionality
DELETE FROM SBI_ROLE_TYPE_USER_FUNC WHERE ROLE_TYPE_ID= (SELECT value_id FROM SBI_DOMAINS where domain_cd = 'ROLE_TYPE' AND VALUE_CD ='USER')
AND USER_FUNCT_ID =(SELECT user_funct_id FROM SBI_USER_FUNC where NAME = 'FinalUsersManagement');

-- Marco 4/11/2013 Altered column length for LABEL
ALTER TABLE SBI_OBJECTS MODIFY COLUMN LABEL VARCHAR(100);

-- Monica 6/11/2013 Dataset refactoring USER, ENTERPRISE, TECHNICAL
INSERT INTO SBI_DOMAINS (VALUE_ID, VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN)
    VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),
    'USER','User','DS_SCOPE','Dataset scope','Dataset scope', 'biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

INSERT INTO SBI_DOMAINS (VALUE_ID, VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN)
    VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),
    'TECHNICAL','Technical','DS_SCOPE','Dataset scope','Dataset scope', 'biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

INSERT INTO SBI_DOMAINS (VALUE_ID, VALUE_CD,VALUE_NM,DOMAIN_CD,DOMAIN_NM,VALUE_DS, USER_IN, TIME_IN)
    VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_DOMAINS'),
    'ENTERPRISE','Enterprise','DS_SCOPE','Dataset scope','Dataset scope', 'biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where  sequence_name = 'SBI_DOMAINS';
commit;

ALTER TABLE SBI_DATA_SET ADD COLUMN SCOPE_ID INT(11) NULL DEFAULT NULL;
ALTER TABLE SBI_DATA_SET ADD CONSTRAINT FK_SBI_DOMAINS_2 FOREIGN KEY ( SCOPE_ID ) REFERENCES SBI_DOMAINS( VALUE_ID ) ON DELETE CASCADE;

UPDATE SBI_DATA_SET
       SET SCOPE_ID =
                 CASE
                   WHEN OWNER IN (SELECT 
						U.USER_ID
						FROM 
						SBI_USER U,
						SBI_EXT_USER_ROLES R,
						SBI_EXT_ROLES RO
						WHERE OWNER = U.USER_ID
						AND R.ID = U.ID
						AND RO.EXT_ROLE_ID = R.EXT_ROLE_ID
						AND RO.ROLE_TYPE_CD IN ('ADMIN', 'DEV_ROLE')
						AND IS_PUBLIC = 0) THEN (SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD='TECHNICAL' AND DOMAIN_CD='DS_SCOPE')
                   WHEN OWNER IN (SELECT 
						U.USER_ID
						FROM 
						SBI_USER U,
						SBI_EXT_USER_ROLES R,
						SBI_EXT_ROLES RO
						WHERE OWNER = U.USER_ID
						AND R.ID = U.ID
						AND RO.EXT_ROLE_ID = R.EXT_ROLE_ID
						AND RO.ROLE_TYPE_CD IN ('ADMIN', 'DEV_ROLE')
						AND IS_PUBLIC = 1) THEN (SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD='ENTERPRISE' AND DOMAIN_CD='DS_SCOPE')
                   WHEN OWNER IN (SELECT 
						U.USER_ID
						FROM 
						SBI_USER U,
						SBI_EXT_USER_ROLES R,
						SBI_EXT_ROLES RO
						WHERE OWNER = U.USER_ID
						AND R.ID = U.ID
						AND RO.EXT_ROLE_ID = R.EXT_ROLE_ID
						AND RO.ROLE_TYPE_CD ='USER') THEN (SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD='USER' AND DOMAIN_CD='DS_SCOPE')
                   ELSE (SELECT VALUE_ID FROM SBI_DOMAINS WHERE VALUE_CD='TECHNICAL' AND DOMAIN_CD='DS_SCOPE')
                 END;
                 
commit;

--12/11/2013 Antonella: delete MANDATORY check
DELETE FROM SBI_PARUSE_CK WHERE CHECK_ID = (SELECT CHECK_ID FROM SBI_CHECKS WHERE value_Type_Cd = 'MANDATORY');
DELETE FROM SBI_CHECKS WHERE CHECK_ID = (SELECT CHECK_ID FROM SBI_CHECKS WHERE value_Type_Cd = 'MANDATORY');
COMMIT;

--13/11/2013 fix bug in functionalities initializers
DELETE FROM SBI_USER_FUNC WHERE NAME = 'FinalUsersManagement';
COMMIT;

----------------------------------- 4.1 -----------------------------------------------------------------------------

-- Monica 9/12/2013 Multi-Tenancy functionalities


ALTER TABLE SBI_ORGANIZATIONS ADD COLUMN THEME VARCHAR(100) NULL DEFAULT 'SPAGOBI.THEMES.THEME.default';
ALTER TABLE SBI_USER ADD COLUMN IS_SUPERADMIN TINYINT(1) DEFAULT 0;

UPDATE SBI_USER us SET us.IS_SUPERADMIN = 1 WHERE us.ID IN(
	SELECT ur.ID FROM SBI_EXT_USER_ROLES ur WHERE ur.EXT_ROLE_ID IN( 
		SELECT role.EXT_ROLE_ID FROM SBI_EXT_ROLES role WHERE role.ROLE_TYPE_CD = 'ADMIN'
	)
);

CREATE TABLE SBI_ORGANIZATION_ENGINE (
  ENGINE_ID int(11) NOT NULL,
  ORGANIZATION_ID int(11) NOT NULL,
  CREATION_DATE timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  LAST_CHANGE_DATE timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  USER_IN varchar(100) NOT NULL,
  USER_UP varchar(100) DEFAULT NULL,
  USER_DE varchar(100) DEFAULT NULL,
  TIME_IN timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  TIME_UP timestamp NULL DEFAULT NULL,
  TIME_DE timestamp NULL DEFAULT NULL,
  SBI_VERSION_IN varchar(10) DEFAULT NULL,
  SBI_VERSION_UP varchar(10) DEFAULT NULL,
  SBI_VERSION_DE varchar(10) DEFAULT NULL,
  META_VERSION varchar(100) DEFAULT NULL,
  ORGANIZATION varchar(20) DEFAULT NULL,
  PRIMARY KEY (ENGINE_ID,ORGANIZATION_ID ),
  CONSTRAINT FK_ENGINE_1 FOREIGN KEY (ENGINE_ID) REFERENCES SBI_ENGINES (ENGINE_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,

  CONSTRAINT FK_ORGANIZATION_1 FOREIGN KEY (ORGANIZATION_ID) REFERENCES SBI_ORGANIZATIONS (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
) ;

CREATE TABLE SBI_ORGANIZATION_DATASOURCE (
  DATASOURCE_ID int(11) NOT NULL,
  ORGANIZATION_ID int(11) NOT NULL,
  CREATION_DATE timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  LAST_CHANGE_DATE timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  USER_IN varchar(100) NOT NULL,
  USER_UP varchar(100) DEFAULT NULL,
  USER_DE varchar(100) DEFAULT NULL,
  TIME_IN timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  TIME_UP timestamp NULL DEFAULT NULL,
  TIME_DE timestamp NULL DEFAULT NULL,
  SBI_VERSION_IN varchar(10) DEFAULT NULL,
  SBI_VERSION_UP varchar(10) DEFAULT NULL,
  SBI_VERSION_DE varchar(10) DEFAULT NULL,
  META_VERSION varchar(100) DEFAULT NULL,
  ORGANIZATION varchar(20) DEFAULT NULL,
  PRIMARY KEY (DATASOURCE_ID,ORGANIZATION_ID ),
  CONSTRAINT FK_DATASOURCE_2 FOREIGN KEY (DATASOURCE_ID) REFERENCES SBI_DATA_SOURCE (DS_ID) ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT FK_ORGANIZATION_2 FOREIGN KEY (ORGANIZATION_ID) REFERENCES SBI_ORGANIZATIONS (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
) ;

INSERT INTO SBI_ORGANIZATION_DATASOURCE (DATASOURCE_ID, ORGANIZATION_ID, CREATION_DATE, LAST_CHANGE_DATE, USER_IN, TIME_IN, SBI_VERSION_IN)
  SELECT ds.ds_id, org.id, SYSDATE(), SYSDATE(), "server", SYSDATE(), "4.1"
  FROM SBI_DATA_SOURCE ds, SBI_ORGANIZATIONS org WHERE ds.organization = org.name;
  
 INSERT INTO SBI_ORGANIZATION_ENGINE (ENGINE_ID, ORGANIZATION_ID, CREATION_DATE, LAST_CHANGE_DATE, USER_IN, TIME_IN, SBI_VERSION_IN)
  SELECT eng.engine_id, org.id, SYSDATE(), SYSDATE(), "server", SYSDATE(), "4.1"
  FROM SBI_OENGINES eng, SBI_ORGANIZATIONS org WHERE eng.organization = org.name;
  
UPDATE SBI_OBJECTS r JOIN (
SELECT B.ENGINE_ID AS OK, A.ENGINE_ID AS KO
FROM
(SELECT ENGINE_ID, LABEL, ORGANIZATION FROM SBI_ENGINES WHERE ORGANIZATION !='SPAGOBI') A,
(SELECT ENGINE_ID, LABEL, ORGANIZATION FROM SBI_ENGINES WHERE ORGANIZATION ='SPAGOBI') B
WHERE A.LABEL=B.LABEL
) t ON (r.ENGINE_ID = t.KO)
SET r.ENGINE_ID = t.OK
WHERE r.ENGINE_ID = t.KO;
(SELECT ENGINE_ID, LABEL, ORGANIZATION FROM SBI_ENGINES WHERE ORGANIZATION !='SPAGOBI');

DELETE FROM SBI_EXPORTERS where engine_id IN (SELECT ENGINE_ID FROM SBI_ENGINES WHERE ORGANIZATION !='SPAGOBI');
COMMIT;

delete from SBI_ENGINES where organization !='SPAGOBI';
COMMIT;

-- 9/12/13 Monica: functionalities refactoring
 
  
CREATE TABLE SBI_AUTHORIZATIONS (
  ID int(11) NOT NULL,
  NAME varchar(200) DEFAULT NULL,
  CREATION_DATE timestamp NOT NULL,
  LAST_CHANGE_DATE timestamp NOT NULL,
  USER_IN varchar(100) NOT NULL,
  USER_UP varchar(100) DEFAULT NULL,
  USER_DE varchar(100) DEFAULT NULL,
  TIME_IN timestamp NOT NULL,
  TIME_UP timestamp NULL DEFAULT NULL,
  TIME_DE timestamp NULL DEFAULT NULL,
  SBI_VERSION_IN varchar(10) DEFAULT NULL,
  SBI_VERSION_UP varchar(10) DEFAULT NULL,
  SBI_VERSION_DE varchar(10) DEFAULT NULL,
  META_VERSION varchar(100) DEFAULT NULL,
  ORGANIZATION varchar(20) DEFAULT NULL,
  PRIMARY KEY (ID)
) ;
insert into hibernate_sequences (SEQUENCE_NAME, NEXT_VAL) 
values('SBI_AUTHORIZATIONS', 1);
COMMIT;

INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE) 
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'SAVE_SUBOBJECTS', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
commit;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;

INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'SEE_SUBOBJECTS', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
commit;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;

INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'SEE_VIEWPOINTS', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
commit;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;


INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'SEE_SNAPSHOTS', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
commit;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;

INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'SEE_NOTES', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;

INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'SEND_MAIL', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;

INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'SAVE_INTO_FOLDER', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;

INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'SAVE_REMEMBER_ME', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;

INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'SEE_METADATA', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;

INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'SAVE_METADATA', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;

INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'BUILD_QBE_QUERY', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;

INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'DO_MASSIVE_EXPORT', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;

INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'EDIT_WORKSHEET', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;


INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'MANAGE_USERS', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;

INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'SEE_DOCUMENT_BROWSER', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;

INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'SEE_FAVOURITES', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;

INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'SEE_SUBSCRIPTIONS', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;


INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'SEE_MY_DATA', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;


INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'SEE_TODO_LIST', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;



INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN, LAST_CHANGE_DATE)
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'CREATE_DOCUMENTS', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP, current_timestamp) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;


CREATE TABLE SBI_AUTHORIZATIONS_ROLES (
  AUTHORIZATION_ID int(11) NOT NULL,
  ROLE_ID int(11) NOT NULL,
  USER_IN varchar(100) NOT NULL,
  USER_UP varchar(100) DEFAULT NULL,
  USER_DE varchar(100) DEFAULT NULL,
  TIME_IN timestamp NOT NULL,
  TIME_UP timestamp NULL DEFAULT NULL,
  TIME_DE timestamp NULL DEFAULT NULL,
  SBI_VERSION_IN varchar(10) DEFAULT NULL,
  SBI_VERSION_UP varchar(10) DEFAULT NULL,
  SBI_VERSION_DE varchar(10) DEFAULT NULL,
  META_VERSION varchar(100) DEFAULT NULL,
  ORGANIZATION varchar(20) DEFAULT NULL,
  PRIMARY KEY (AUTHORIZATION_ID,ROLE_ID ),
  CONSTRAINT FK_ROLE1 FOREIGN KEY (ROLE_ID) REFERENCES SBI_EXT_ROLES (EXT_ROLE_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_AUTHORIZATION_1 FOREIGN KEY (AUTHORIZATION_ID) REFERENCES SBI_AUTHORIZATIONS (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
) ;

INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'SAVE_SUBOBJECTS') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.SAVE_SUBOBJECTS =1;

INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'CREATE_DOCUMENTS') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.CREATE_DOCUMENTS =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'SEE_SUBOBJECTS') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.SEE_SUBOBJECTS =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'SEE_VIEWPOINTS') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.SEE_VIEWPOINTS =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'SEE_SNAPSHOTS') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.SEE_SNAPSHOTS =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'SEE_NOTES') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.SEE_NOTES =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'SEND_MAIL') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.SEND_MAIL =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'SAVE_INTO_FOLDER') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.SAVE_INTO_FOLDER =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'SAVE_REMEMBER_ME') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.SAVE_REMEMBER_ME =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'SEE_METADATA') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.SEE_METADATA =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'SAVE_METADATA') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.SAVE_METADATA =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'BUILD_QBE_QUERY') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.BUILD_QBE_QUERY =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'DO_MASSIVE_EXPORT') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.DO_MASSIVE_EXPORT =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'EDIT_WORKSHEET') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.EDIT_WORKSHEET =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'MANAGE_USERS') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.MANAGE_USERS =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'SEE_DOCUMENT_BROWSER') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.SEE_DOCUMENT_BROWSER =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'SEE_FAVOURITES') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.SEE_FAVOURITES =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'SEE_SUBSCRIPTIONS') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.SEE_SUBSCRIPTIONS =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'SEE_MY_DATA') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.SEE_MY_DATA =1;
INSERT INTO SBI_AUTHORIZATIONS_ROLES 
(AUTHORIZATION_ID, ROLE_ID, ORGANIZATION, TIME_IN, USER_IN) 
SELECT (SELECT ID FROM SBI_AUTHORIZATIONS WHERE NAME= 'SEE_TODO_LIST') F ,
D.EXT_ROLE_ID, 
D.ORGANIZATION, 
CURRENT_TIMESTAMP, 'server' FROM SBI_EXT_ROLES D WHERE D.SEE_TODO_LIST =1;

ALTER TABLE SBI_EXT_ROLES DROP COLUMN SAVE_SUBOBJECTS;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN SEE_SUBOBJECTS;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN  SEE_VIEWPOINTS;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN  SEE_SNAPSHOTS;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN  SEE_NOTES;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN  SEND_MAIL;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN  SAVE_INTO_FOLDER;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN  SAVE_REMEMBER_ME;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN  SEE_METADATA; 
ALTER TABLE SBI_EXT_ROLES DROP COLUMN SAVE_METADATA;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN  BUILD_QBE_QUERY;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN  DO_MASSIVE_EXPORT;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN  EDIT_WORKSHEET;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN  MANAGE_USERS;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN  SEE_DOCUMENT_BROWSER;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN  SEE_FAVOURITES;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN  SEE_SUBSCRIPTIONS;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN  SEE_MY_DATA;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN  SEE_TODO_LIST;
ALTER TABLE SBI_EXT_ROLES DROP COLUMN  CREATE_DOCUMENTS;

INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN) 
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'KPI_COMMENT_EDIT_ALL', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;
INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN) 
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'KPI_COMMENT_EDIT_MY', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;
INSERT INTO SBI_AUTHORIZATIONS
(ID, NAME, CREATION_DATE, USER_IN, TIME_IN) 
values ((SELECT NEXT_VAL FROM hibernate_sequences WHERE SEQUENCE_NAME='SBI_AUTHORIZATIONS'), 
'KPI_COMMENT_DELETE', 
CURRENT_TIMESTAMP, 'server', CURRENT_TIMESTAMP) ;
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_AUTHORIZATIONS';
commit;

UPDATE SBI_ENGINES SET DRIVER_NM = 'it.eng.spagobi.engines.drivers.gis.GisDriver' 
		WHERE DRIVER_NM = 'it.eng.spagobi.engines.drivers.generic.GenericDriver' 
		AND BIOBJ_TYPE IN (SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'BIOBJ_TYPE' AND VALUE_CD = 'MAP'); 
COMMIT;

--27/01/2014 Antonella: Added SpagoBICockpitEngine configuration
INSERT INTO sbi_engines
(ENGINE_ID,ENCRYPT,NAME,DESCR,MAIN_URL,SECN_URL,OBJ_UPL_DIR,OBJ_USE_DIR,DRIVER_NM,LABEL,ENGINE_TYPE,CLASS_NM,BIOBJ_TYPE,USE_DATASET,USE_DATASOURCE,USER_IN,USER_UP,USER_DE,TIME_IN,
TIME_UP,TIME_DE,SBI_VERSION_IN,SBI_VERSION_UP,SBI_VERSION_DE,META_VERSION,ORGANIZATION)
VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_ENGINES'), 0, 'Cockpit Engine', 'Cockpit Engine', '/SpagoBICockpitEngine/CockpitEngineStartAction', NULL, NULL, NULL, 'it.eng.spagobi.engines.drivers.cockpit.CockpitDriver', 'SpagoBICockpitEngine', (SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'ENGINE_TYPE' AND VALUE_CD = 'EXT'), '',(SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'BIOBJ_TYPE' AND VALUE_CD = 'DOCUMENT_COMPOSITE'), false, false, 'database', 'biadmin', NULL, '2014-01-09 00:00:00', '2014-01-09 00:00:00', NULL, '4.1', '4.1', NULL, NULL, 'SPAGOBI');
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_ENGINES';
commit;
INSERT INTO SBI_ORGANIZATION_ENGINE (ENGINE_ID, ORGANIZATION_ID, CREATION_DATE, LAST_CHANGE_DATE, USER_IN, TIME_IN, SBI_VERSION_IN)
values((SELECT engine_id from SBI_ENGINES where label='SpagoBICockpitEngine'), (select id from SBI_ORGANIZATIONS where name = 'SPAGOBI'),
SYSDATE(), SYSDATE(), 'server', SYSDATE(), '4.1');
COMMIT;

--14/02/2014: Added XMLADriver:
update SBI_ENGINES SET DRIVER_NM = 'it.eng.spagobi.engines.drivers.xmla.XMLADriver' where label = 'XMLAEngine';
commit;

-- 10/03/2014  Angelo
ALTER TABLE SBI_AUDIT MODIFY DOC_LABEL varchar(200);
ALTER TABLE SBI_AUDIT MODIFY DOC_NAME varchar(200);

13/03/2014 aNGELO
ALTER TABLE SBI_OBJECTS DROP FOREIGN KEY FK_SBI_OBJECTS_5;
ALTER TABLE SBI_OBJECTS DROP FOREIGN KEY FK_SBI_OBJECTS_6;
ALTER TABLE SBI_OBJECTS DROP FOREIGN KEY FK_SBIDATA_SOURCE;

-- 17/03/2014 Marco
CREATE TABLE SBI_TRIGGER_PAUSED (
	   ID 					INTEGER  NOT NULL ,
       TRIGGER_NAME	 	    VARCHAR(80) NOT NULL,
       TRIGGER_GROUP 	    VARCHAR(80) NOT NULL,
       JOB_NAME 	        VARCHAR(80) NOT NULL,
       JOB_GROUP 	        VARCHAR(80) NOT NULL,	   	   
       USER_IN              VARCHAR(100) NOT NULL,
       USER_UP              VARCHAR(100),
       USER_DE              VARCHAR(100),
       TIME_IN              TIMESTAMP NOT NULL,
       TIME_UP              TIMESTAMP NULL DEFAULT NULL,
       TIME_DE              TIMESTAMP NULL DEFAULT NULL,
       SBI_VERSION_IN       VARCHAR(10),
       SBI_VERSION_UP       VARCHAR(10),
       SBI_VERSION_DE       VARCHAR(10),
       META_VERSION         VARCHAR(100),
       ORGANIZATION         VARCHAR(20),  
       UNIQUE XAK1SBI_TRIGGER_PAUSED (TRIGGER_NAME, TRIGGER_GROUP, JOB_NAME, JOB_GROUP),
       PRIMARY KEY (ID)
) ENGINE=InnoDB;

-- 27/03/2014 Davide: config for documents' preview image security settings
INSERT INTO SBI_CONFIG ( ID, LABEL, NAME, DESCRIPTION, IS_ACTIVE, VALUE_CHECK, VALUE_TYPE_ID, CATEGORY, USER_IN, TIME_IN) VALUES 
((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_CONFIG'), 
'SPAGOBI.DOCUMENTS.MAX_PREVIEW_IMAGE_SIZE', 'Max preview image size', 'Max dimension for a document''s preview image', true, '1048576',
(select VALUE_ID from SBI_DOMAINS where VALUE_CD = 'NUM' AND DOMAIN_CD = 'PAR_TYPE'), 'GENERIC_CONFIGURATION', 'biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_CONFIG';
commit;
INSERT INTO SBI_CONFIG ( ID, LABEL, NAME, DESCRIPTION, IS_ACTIVE, VALUE_CHECK, VALUE_TYPE_ID, CATEGORY, USER_IN, TIME_IN) VALUES 
((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_CONFIG'), 
'SPAGOBI.DOCUMENTS.MAX_PREVIEW_IMAGES_NUM', 'Max preview images', 'Max number for documents'' preview images', true, '200',
(select VALUE_ID from SBI_DOMAINS where VALUE_CD = 'NUM' AND DOMAIN_CD = 'PAR_TYPE'), 'GENERIC_CONFIGURATION', 'biadmin', current_timestamp);
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_CONFIG';
commit;

-- 28/03/2014 Davide: removed SBI_OBJECTS.IS_PUBLIC column
ALTER TABLE SBI_OBJECTS DROP COLUMN IS_PUBLIC;

-- 30/04/2014 Davide: added SpagoBIWhatIfEngine engine
INSERT INTO SBI_ENGINES
(ENGINE_ID,ENCRYPT,NAME,DESCR,MAIN_URL,SECN_URL,OBJ_UPL_DIR,OBJ_USE_DIR,DRIVER_NM,LABEL,ENGINE_TYPE,CLASS_NM,BIOBJ_TYPE,USE_DATASET,USE_DATASOURCE,USER_IN,USER_UP,USER_DE,TIME_IN,
TIME_UP,TIME_DE,SBI_VERSION_IN,SBI_VERSION_UP,SBI_VERSION_DE,META_VERSION,ORGANIZATION)
VALUES ((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_ENGINES'), 0, 'What-If Engine', 'What-If Engine', '/SpagoBIWhatIfEngine/restful-services/start', NULL, NULL, NULL, 'it.eng.spagobi.engines.drivers.whatif.WhatIfDriver', 'SpagoBIWhatIfEngine', (SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'ENGINE_TYPE' AND VALUE_CD = 'EXT'), NULL, (SELECT VALUE_ID FROM SBI_DOMAINS WHERE DOMAIN_CD = 'BIOBJ_TYPE' AND VALUE_CD = 'OLAP'), false, true, 'system', NULL, NULL, SYSDATE(), NULL, NULL, '5.0', NULL, NULL, NULL, 'SPAGOBI');
update hibernate_sequences set next_val = next_val+1 where sequence_name = 'SBI_ENGINES';
commit;
INSERT INTO SBI_ORGANIZATION_ENGINE (USER_IN, TIME_IN, SBI_VERSION_IN, ENGINE_ID, ORGANIZATION_ID, ORGANIZATION)
SELECT 
'system' AS USER_IN,
SYSDATE() AS TIME_IN, 
'5.0' AS SBI_VERSION_IN,
(SELECT engine_id from SBI_ENGINES where label = 'SpagoBIWhatIfEngine') AS ENGINE_ID, 
o.id AS ORGANIZATION_ID, o.name AS ORGANIZATION from SBI_ORGANIZATIONS o;
COMMIT;

-- 20/05/2014
ALTER TABLE SBI_ARTIFACTS
ADD COLUMN MODEL_LOCKED BOOLEAN NULL,
ADD COLUMN MODEL_LOCKER VARCHAR(100) NULL;
COMMIT;

UPDATE SBI_ARTIFACTS SET MODEL_LOCKED = false WHERE MODEL_LOCKED IS NULL;

-- 25/06/2014

ALTER TABLE SBI_OBJECTS ADD COLUMN PARAMETERS_REGION VARCHAR(20) NULL DEFAULT 'east';

insert into SBI_CONFIG(ID,LABEL, NAME, DESCRIPTION, IS_ACTIVE, VALUE_CHECK, VALUE_TYPE_ID, USER_IN, TIME_IN, SBI_VERSION_IN, ORGANIZATION, CATEGORY) values
((SELECT next_val FROM hibernate_sequences WHERE sequence_name = 'SBI_CONFIG'),'SPAGOBI.DOCUMENTS.PARAMETERS_REGION_DEFAULT', 'Parameters Region Default', 'Where parameters form is shown (north or east) by default', '1', 'right', '30',
 'biadmin', '2014-06-24 15:06:24', '4.2', 'SPAGOBI', 'GENERIC_CONFIGURATION')