analytics/configuration/schemas/mobile/oracle/upgrade-02-008-to-02-009.sql

-- Licensed Materials - Property of IBM
-- BI and PM: Mobile
-- (C) Copyright IBM Corp. 2007, 2012
-- US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

DECLARE
	TABLE_DOES_NOT_EXIST_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST_EXCEPTION, -942);
	CONSTRAINT_DOES_NOT_EXIST_EX EXCEPTION;
	PRAGMA EXCEPTION_INIT (CONSTRAINT_DOES_NOT_EXIST_EX, -02443);
BEGIN
	EXECUTE IMMEDIATE 'ALTER TABLE MOB_RENDERS DROP CONSTRAINT FK_MOB_R_RSID';
EXCEPTION
	WHEN TABLE_DOES_NOT_EXIST_EXCEPTION THEN
		NULL;
	WHEN CONSTRAINT_DOES_NOT_EXIST_EX THEN
		NULL;
END;
/

DECLARE
	TABLE_DOES_NOT_EXIST_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST_EXCEPTION, -942);
	CONSTRAINT_DOES_NOT_EXIST_EX EXCEPTION;
	PRAGMA EXCEPTION_INIT (CONSTRAINT_DOES_NOT_EXIST_EX, -04080);
BEGIN
	EXECUTE IMMEDIATE 'ALTER TABLE MOB_USER_RESOURCE DROP CONSTRAINT FK_MOB_URS_RSID';
EXCEPTION
	WHEN TABLE_DOES_NOT_EXIST_EXCEPTION THEN
		NULL;
	WHEN CONSTRAINT_DOES_NOT_EXIST_EX THEN
		NULL;
END;
/

DECLARE
	TABLE_DOES_NOT_EXIST_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST_EXCEPTION, -942);
	CONSTRAINT_DOES_NOT_EXIST_EX EXCEPTION;
	PRAGMA EXCEPTION_INIT (CONSTRAINT_DOES_NOT_EXIST_EX, -02443);
BEGIN
	EXECUTE IMMEDIATE 'ALTER TABLE MOB_BLOBS DROP CONSTRAINT FK_MOB_BLOBS_RSID';
EXCEPTION
	WHEN TABLE_DOES_NOT_EXIST_EXCEPTION THEN
		NULL;
	WHEN CONSTRAINT_DOES_NOT_EXIST_EX THEN
		NULL;
END;
/

DECLARE
	TABLE_DOES_NOT_EXIST_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST_EXCEPTION, -942);
	CONSTRAINT_DOES_NOT_EXIST_EX EXCEPTION;
	PRAGMA EXCEPTION_INIT (CONSTRAINT_DOES_NOT_EXIST_EX, -02443);
BEGIN
	EXECUTE IMMEDIATE 'ALTER TABLE MOB_BLOBS DROP CONSTRAINT FK_MOB_BLOBS_RID';
EXCEPTION
	WHEN TABLE_DOES_NOT_EXIST_EXCEPTION THEN
		NULL;
	WHEN CONSTRAINT_DOES_NOT_EXIST_EX THEN
		NULL;
END;
/

DECLARE
	TABLE_DOES_NOT_EXIST_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST_EXCEPTION, -942);
	CONSTRAINT_DOES_NOT_EXIST_EX EXCEPTION;
	PRAGMA EXCEPTION_INIT (CONSTRAINT_DOES_NOT_EXIST_EX, -02443);
BEGIN
	EXECUTE IMMEDIATE 'ALTER TABLE MOB_USER_RENDER DROP CONSTRAINT FK_MOB_UR_UID';
EXCEPTION
	WHEN TABLE_DOES_NOT_EXIST_EXCEPTION THEN
		NULL;
	WHEN CONSTRAINT_DOES_NOT_EXIST_EX THEN
		NULL;
END;
/

DECLARE
	TABLE_DOES_NOT_EXIST_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST_EXCEPTION, -942);
	CONSTRAINT_DOES_NOT_EXIST_EX EXCEPTION;
	PRAGMA EXCEPTION_INIT (CONSTRAINT_DOES_NOT_EXIST_EX, -02443);
BEGIN
	EXECUTE IMMEDIATE 'ALTER TABLE MOB_USER_RENDER DROP CONSTRAINT FK_MOB_UR_RID';
EXCEPTION
	WHEN TABLE_DOES_NOT_EXIST_EXCEPTION THEN
		NULL;
	WHEN CONSTRAINT_DOES_NOT_EXIST_EX THEN
		NULL;
END;
/

DECLARE
	TABLE_DOES_NOT_EXIST_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST_EXCEPTION, -942);
	CONSTRAINT_DOES_NOT_EXIST_EX EXCEPTION;
	PRAGMA EXCEPTION_INIT (CONSTRAINT_DOES_NOT_EXIST_EX, -02443);
BEGIN
	EXECUTE IMMEDIATE 'ALTER TABLE MOB_RENDER_HISTORY DROP CONSTRAINT FK_MOB_RH_RID';
EXCEPTION
	WHEN TABLE_DOES_NOT_EXIST_EXCEPTION THEN
		NULL;
	WHEN CONSTRAINT_DOES_NOT_EXIST_EX THEN
		NULL;
END;
/

DECLARE
	TABLE_DOES_NOT_EXIST_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST_EXCEPTION, -942);
	CONSTRAINT_DOES_NOT_EXIST_EX EXCEPTION;
	PRAGMA EXCEPTION_INIT (CONSTRAINT_DOES_NOT_EXIST_EX, -02443);
BEGIN
	EXECUTE IMMEDIATE 'ALTER TABLE MOB_RENDER_HISTORY DROP CONSTRAINT FK_MOB_RH_HID';
EXCEPTION
	WHEN TABLE_DOES_NOT_EXIST_EXCEPTION THEN
		NULL;
	WHEN CONSTRAINT_DOES_NOT_EXIST_EX THEN
		NULL;
END;
/

DECLARE
	TRIGGER_DOES_NOT_EXIST_EXC EXCEPTION;
	PRAGMA EXCEPTION_INIT (TRIGGER_DOES_NOT_EXIST_EXC, -04080);
BEGIN
	EXECUTE IMMEDIATE 'DROP TRIGGER TGR_MOB_URSRC_SYNC_IN';
EXCEPTION
	WHEN TRIGGER_DOES_NOT_EXIST_EXC THEN
		NULL;
END;
/

DECLARE
	CONSTRAINT_DOES_NOT_EXIST_EX EXCEPTION;
	PRAGMA EXCEPTION_INIT (CONSTRAINT_DOES_NOT_EXIST_EX, -04080);
BEGIN
	EXECUTE IMMEDIATE 'DROP TRIGGER TGR_MOB_URSRC_SYNC_UP';
EXCEPTION
	WHEN CONSTRAINT_DOES_NOT_EXIST_EX THEN
		NULL;
END;
/

DECLARE
	CONSTRAINT_DOES_NOT_EXIST_EX EXCEPTION;
	PRAGMA EXCEPTION_INIT (CONSTRAINT_DOES_NOT_EXIST_EX, -04080);
BEGIN
	EXECUTE IMMEDIATE 'DROP TRIGGER TGR_MOB_URSRC_SYNC_DE';
EXCEPTION
	WHEN CONSTRAINT_DOES_NOT_EXIST_EX THEN
		NULL;
END;
/

DECLARE
	TABLE_DOES_NOT_EXIST_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST_EXCEPTION, -942);
BEGIN
	EXECUTE IMMEDIATE 'DROP TABLE MOB_RESOURCES';
EXCEPTION
	WHEN TABLE_DOES_NOT_EXIST_EXCEPTION THEN
		NULL;
END;
/

DECLARE
	TABLE_DOES_NOT_EXIST_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST_EXCEPTION, -942);
BEGIN
	EXECUTE IMMEDIATE 'DROP TABLE MOB_USER_RESOURCE';
EXCEPTION
	WHEN TABLE_DOES_NOT_EXIST_EXCEPTION THEN
		NULL;
END;
/

DECLARE
	TABLE_DOES_NOT_EXIST_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST_EXCEPTION, -942);
BEGIN
	EXECUTE IMMEDIATE 'DROP TABLE MOB_USER_RESOURCE_TYPES';
EXCEPTION
	WHEN TABLE_DOES_NOT_EXIST_EXCEPTION THEN
		NULL;
END;
/

-- MOB_PORTALITEMS
DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_PORTALITEMS (
		PORTALITEM_ID int PRIMARY KEY NOT NULL,
		LABEL varchar (100) NULL,
		STORE_ID varchar (200) NULL,
		CM_PATH varchar (1000) NULL,
		SOURCE_PATH varchar (1000) NULL,
		DESCRIPTION varchar (500) NULL,
		PORTALITEM_TYPE int DEFAULT 0 NOT NULL
	)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_MOB_REP_SI ON MOB_PORTALITEMS(STORE_ID)';
EXCEPTION
	WHEN TABLE_EXISTS_EXCEPTION THEN
		NULL;
END;
/

DECLARE
	SEQUENCE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (SEQUENCE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_MOB_PORTALITEMS START WITH 1 INCREMENT BY 1 NOMAXVALUE';
EXCEPTION
	WHEN SEQUENCE_EXISTS_EXCEPTION THEN
		NULL;
END;
/

CREATE OR REPLACE TRIGGER "TRIGGER_MOB_PORTALITEMS" BEFORE INSERT ON "MOB_PORTALITEMS" FOR EACH ROW
	BEGIN
		SELECT SEQ_MOB_PORTALITEMS.nextval INTO :new.PORTALITEM_ID FROM DUAL;
	END;
/

-- MOB_USER_PORTALITEM
DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_USER_PORTALITEM (
		USER_ID int NOT NULL,
		PORTALITEM_ID int NOT NULL,
		USER_PORTALITEM_TYPE int DEFAULT 0 NOT NULL,
		CONSTRAINT FK_MOB_UPI_UID FOREIGN KEY(USER_ID) REFERENCES MOB_USERS(USER_ID) ON DELETE CASCADE,
		CONSTRAINT FK_MOB_UPI_RSID FOREIGN KEY(PORTALITEM_ID) REFERENCES MOB_PORTALITEMS(PORTALITEM_ID) ON DELETE CASCADE,
		CONSTRAINT PK_MOB_USERPIID PRIMARY KEY(USER_ID, PORTALITEM_ID)
	)';
EXCEPTION
	WHEN TABLE_EXISTS_EXCEPTION THEN
		NULL;
END;
/

-- USER_PORTALITEM_TYPES
DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_USER_PORTALITEM_TYPES (
		TYPE smallint NOT NULL,
		DESCRIPTION varchar (100) NULL 
	)';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_USER_PORTALITEM_TYPES VALUES (0, ''HOMEREPORT'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_USER_PORTALITEM_TYPES VALUES (1, ''FAVORITE'')';
EXCEPTION
	WHEN TABLE_EXISTS_EXCEPTION THEN
		NULL;
END;
/

DECLARE
	TABLE_DOES_NOT_EXIST_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST_EXCEPTION, -942);
BEGIN
	EXECUTE IMMEDIATE 'DROP TABLE MOB_USER_RENDER';
EXCEPTION
	WHEN TABLE_DOES_NOT_EXIST_EXCEPTION THEN
		NULL;
END;
/

DECLARE
	TABLE_DOES_NOT_EXIST_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST_EXCEPTION, -942);
BEGIN
	EXECUTE IMMEDIATE 'DROP TABLE MOB_RENDER_HISTORY';
EXCEPTION
	WHEN TABLE_DOES_NOT_EXIST_EXCEPTION THEN
		NULL;
END;
/

-- MOB_RENDERS
DECLARE
	TABLE_DOES_NOT_EXIST_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST_EXCEPTION, -942);
BEGIN
	EXECUTE IMMEDIATE 'DROP TABLE MOB_RENDERS';
EXCEPTION
	WHEN TABLE_DOES_NOT_EXIST_EXCEPTION THEN
		NULL;
END;
/

DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_RENDERS (
		RENDER_ID int PRIMARY KEY NOT NULL,	-- identity
		RENDER_TIME timestamp NOT NULL,
		RENDER_SIZE int NOT NULL,
		STATUS_CODE int NOT NULL,
		SOURCE_CODE smallint NULL,
		DRILL_PARAMS varchar (1000) NULL,
		PORTALITEM_ID int NOT NULL,
		BASE_DOC varchar (200),
		SMALL_THUMB varchar (200),
		MEDIUM_THUMB varchar (200),
		LARGE_THUMB varchar (200),
		CONSTRAINT FK_MOB_R_PIID FOREIGN KEY(PORTALITEM_ID) REFERENCES MOB_PORTALITEMS(PORTALITEM_ID) ON DELETE CASCADE
	)';
EXCEPTION
	WHEN TABLE_EXISTS_EXCEPTION THEN
		NULL;
END;
/

DECLARE
	SEQUENCE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (SEQUENCE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_MOB_RENDERS START WITH 1 INCREMENT BY 1 NOMAXVALUE';
EXCEPTION
	WHEN SEQUENCE_EXISTS_EXCEPTION THEN
		NULL;
END;
/

CREATE OR REPLACE TRIGGER "TRIGGER_MOB_RENDERS" BEFORE INSERT ON "MOB_RENDERS" FOR EACH ROW
	BEGIN
		SELECT SEQ_MOB_RENDERS.nextval INTO :new.RENDER_ID FROM DUAL;
	END;
/

-- MOB_BLOBS
DECLARE
	TABLE_DOES_NOT_EXIST_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST_EXCEPTION, -942);
BEGIN
	EXECUTE IMMEDIATE 'DROP TABLE MOB_BLOBS';
EXCEPTION
	WHEN TABLE_DOES_NOT_EXIST_EXCEPTION THEN
		NULL;
END;
/

DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_BLOBS (
		HASH char (32) NULL,
		USER_ID int NULL,
		SEQUENCE int NOT NULL,
		BLOB_VALUE blob NOT NULL,
		FORMAT smallint,
		ADDED timestamp DEFAULT SYSDATE NOT NULL,
		CONSTRAINT FK_MOB_BLOBS_UID FOREIGN KEY(USER_ID) REFERENCES MOB_USERS(USER_ID) ON DELETE CASCADE
	)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_MOB_BLOBS_HASH ON MOB_BLOBS(HASH)';
EXCEPTION
	WHEN TABLE_EXISTS_EXCEPTION THEN
		NULL;
END;
/

-- MOB_RESOURCES
DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_RESOURCES (
		RESOURCE_ID int PRIMARY KEY NOT NULL,	-- identity
		RENDER_ID int,
		PATH varchar (200),
		HASH char (32) NULL,
		CONSTRAINT FK_MOB_RES_RID FOREIGN KEY(RENDER_ID) REFERENCES MOB_RENDERS(RENDER_ID) ON DELETE CASCADE
	)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_MOB_RESOURCES ON MOB_RESOURCES(RENDER_ID)';
EXCEPTION
	WHEN TABLE_EXISTS_EXCEPTION THEN
		NULL;
END;
/

DECLARE
	SEQUENCE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (SEQUENCE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_MOB_RESOURCES START WITH 1 INCREMENT BY 1 NOMAXVALUE';
EXCEPTION
	WHEN SEQUENCE_EXISTS_EXCEPTION THEN
		NULL;
END;
/

CREATE OR REPLACE TRIGGER "TRIGGER_MOB_RESOURCES" BEFORE INSERT ON "MOB_RESOURCES" FOR EACH ROW
	BEGIN
		SELECT SEQ_MOB_RESOURCES.nextval INTO :new.RESOURCE_ID FROM DUAL;
	END;
/

-- MOB_USER_RENDER
DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_USER_RENDER (
		USER_ID int NOT NULL,
		RENDER_ID int NOT NULL,
		NAME varchar (100) NULL,
		LAST_VIEWED timestamp NULL,
		CONSTRAINT FK_MOB_UR_UID FOREIGN KEY(USER_ID) REFERENCES MOB_USERS(USER_ID) ON DELETE CASCADE,
		CONSTRAINT FK_MOB_UR_RID FOREIGN KEY(RENDER_ID) REFERENCES MOB_RENDERS(RENDER_ID) ON DELETE CASCADE,
		CONSTRAINT PK_MOB_USERRNDR PRIMARY KEY(USER_ID, RENDER_ID)
	)';
EXCEPTION
	WHEN TABLE_EXISTS_EXCEPTION THEN
		NULL;
END;
/

-- MOB_RENDER_HISTORY
DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_RENDER_HISTORY (
		RENDER_ID int NOT NULL,
		HISTORY_ID int NOT NULL,
		CONSTRAINT FK_MOB_RH_RID FOREIGN KEY(RENDER_ID) REFERENCES MOB_RENDERS(RENDER_ID) ON DELETE CASCADE, 
		CONSTRAINT FK_MOB_RH_HID FOREIGN KEY(HISTORY_ID) REFERENCES MOB_HISTORY(HISTORY_ID) ON DELETE CASCADE
	)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_MOB_RH_RID ON MOB_RENDER_HISTORY(RENDER_ID)';
EXCEPTION
	WHEN TABLE_EXISTS_EXCEPTION THEN
		NULL;
END;
/