-- 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.

--
-- Mobile database table creation scripts for MS SQL 2000.

-- 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,
		LABEL varchar (100) NULL,
		IDENTIFIER varchar (200) NULL,
		CM_PATH varchar (1000) NULL,
		SOURCE_PATH varchar (1000) NULL,
		DESCRIPTION varchar (500) NULL,
		RESOURCE_TYPE int DEFAULT 0 NOT NULL
	)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_MOB_REP_ID ON MOB_RESOURCES(IDENTIFIER)';
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_RENDERS
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,
		RESOURCE_ID int NOT NULL,
		CONSTRAINT FK_MOB_R_RSID FOREIGN KEY(RESOURCE_ID) REFERENCES MOB_RESOURCES(RESOURCE_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_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_USERS
DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_USERS (
		USER_ID int PRIMARY KEY NOT NULL,
		DEVICE_ID varchar (100) NULL,
		DEVICE_PROFILE varchar (200) NULL,
		CREDENTIAL_PATH varchar (200) NULL,
		CAM_ID varchar (100) NULL,
		KEY_TYPE varchar (20) NULL,
		KEY_BYTES blob NULL,
		SYNC_METHOD varchar (20) NULL,
		LAST_LOGIN timestamp NULL
		)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_CAM_ID ON MOB_USERS(CAM_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_USERS START WITH 1 INCREMENT BY 1 NOMAXVALUE';
EXCEPTION
	WHEN SEQUENCE_EXISTS_EXCEPTION THEN
		NULL;
END;
/
CREATE OR REPLACE TRIGGER "TRIGGER_MOB_USERS" BEFORE INSERT ON "MOB_USERS" FOR EACH ROW
	BEGIN
		SELECT SEQ_MOB_USERS.nextval INTO :new.USER_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_USER_RESOURCE
DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_USER_RESOURCE (
		USER_ID int NOT NULL,
		RESOURCE_ID int NOT NULL,
		USER_RESOURCE_TYPE int DEFAULT 0 NOT NULL,
		CONSTRAINT FK_MOB_URS_UID FOREIGN KEY(USER_ID) REFERENCES MOB_USERS(USER_ID) ON DELETE CASCADE,
		CONSTRAINT FK_MOB_URS_RSID FOREIGN KEY(RESOURCE_ID) REFERENCES MOB_RESOURCES(RESOURCE_ID) ON DELETE CASCADE,
		CONSTRAINT PK_MOB_USERRSRC PRIMARY KEY(USER_ID, RESOURCE_ID)
	)';
EXCEPTION
	WHEN TABLE_EXISTS_EXCEPTION THEN
		NULL;
END;
/

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

-- MOB_BLOBS
DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_BLOBS (
		BLOB_ID int NOT NULL,
		RENDER_ID int NULL,
		RESOURCE_ID int NULL,
		USER_ID int NULL,
		SEQUENCE int NOT NULL,
		BLOB_VALUE blob NOT NULL,
		FORMAT smallint NULL,
		OBJECT_INDEX int NULL,
		PAGE_INDEX int NULL,
		CONSTRAINT FK_MOB_BLOBS_RID FOREIGN KEY(RENDER_ID) REFERENCES MOB_RENDERS(RENDER_ID) ON DELETE CASCADE,
		CONSTRAINT FK_MOB_BLOBS_RSID FOREIGN KEY(RESOURCE_ID) REFERENCES MOB_RESOURCES(RESOURCE_ID) ON DELETE CASCADE,
		CONSTRAINT FK_MOB_BLOBS_UID FOREIGN KEY(USER_ID) REFERENCES MOB_USERS(USER_ID) ON DELETE CASCADE
	)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_MOB_BLOBS ON MOB_BLOBS(BLOB_ID, SEQUENCE)';
EXCEPTION
	WHEN TABLE_EXISTS_EXCEPTION THEN
		NULL;
END;
/

-- MOB_BLOB_FORMATS
DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_BLOB_FORMATS (
		FORMAT smallint NOT NULL,
		DESCRIPTION varchar (100) NULL 
	)';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_BLOB_FORMATS VALUES (0, ''USER COOKIE'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_BLOB_FORMATS VALUES (1, ''REPORT INFO'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_BLOB_FORMATS VALUES (2, ''REPORT OUTPUT'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_BLOB_FORMATS VALUES (3, ''DB'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_BLOB_FORMATS VALUES (4, ''PAGE HTML'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_BLOB_FORMATS VALUES (5, ''IMAGE'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_BLOB_FORMATS VALUES (6, ''RENDER THUMBNAIL SMALL'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_BLOB_FORMATS VALUES (7, ''RENDER THUMBNAIL LARGE'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_BLOB_FORMATS VALUES (8, ''iOS THUMBNAIL SMALL'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_BLOB_FORMATS VALUES (9, ''iOS THUMBNAIL LARGE'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_BLOB_FORMATS VALUES (10, ''SVG'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_BLOB_FORMATS VALUES (11, ''TABLE HTML'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_BLOB_FORMATS VALUES (12, ''ATTCH IMAGE'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_BLOB_FORMATS VALUES (13, ''CONVERSATION STATE'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_BLOB_FORMATS VALUES (14, ''DASHBOARD'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_BLOB_FORMATS VALUES (15, ''BUX CONVERSATION STATE'')';
EXCEPTION
	WHEN TABLE_EXISTS_EXCEPTION THEN
		NULL;
END;
/

-- MOB_STATUS_CODES
DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_STATUS_CODES (
		STATUS_CODE smallint NOT NULL,
		DESCRIPTION varchar (100) NULL 
	)';
	EXECUTE IMMEDIATE 'CREATE INDEX PK_MOB_STATUS_CODES ON MOB_STATUS_CODES(STATUS_CODE)';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_STATUS_CODES VALUES (1, ''IN PROGRESS'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_STATUS_CODES VALUES (2, ''COMPLETED'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_STATUS_CODES VALUES (3, ''PENDING'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_STATUS_CODES VALUES (9, ''CANCELLED'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_STATUS_CODES VALUES (99, ''ERROR'')';
EXCEPTION
	WHEN TABLE_EXISTS_EXCEPTION THEN
		NULL;
END;
/

-- MOB_SOURCE_CODES
DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_SOURCE_CODES (
		SOURCE_CODE smallint NOT NULL,
		DESCRIPTION varchar (100) NULL 
	)';
	EXECUTE IMMEDIATE 'CREATE INDEX PK_MOB_SOURCE_CODES ON MOB_SOURCE_CODES(SOURCE_CODE)';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_SOURCE_CODES VALUES (0, ''AD-HOC'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_SOURCE_CODES VALUES (1, ''SCHEDULED/RUN-WITH-OPTIONS'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_SOURCE_CODES VALUES (2, ''DRILL-THROUGH'')';
	EXECUTE IMMEDIATE 'INSERT INTO MOB_SOURCE_CODES VALUES (3, ''DRILL-UP/DOWN'')';
EXCEPTION
	WHEN TABLE_EXISTS_EXCEPTION THEN
		NULL;
END;
/

-- MOB_TEMPSTORAGE
DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_TEMPSTORAGE (
		TS_ID int PRIMARY KEY NOT NULL,
		PATH varchar (500) NULL,
		CREATED timestamp NOT NULL,
		MODIFIED timestamp NOT NULL 
	)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_MOB_MODIFIED ON MOB_TEMPSTORAGE(MODIFIED)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_MOB_CREATED ON MOB_TEMPSTORAGE(CREATED)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_MOB_TMPPATH ON MOB_TEMPSTORAGE(PATH)';
	EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_MOB_TEMPSTORAGE START WITH 1 INCREMENT BY 1 NOMAXVALUE';
EXCEPTION
	WHEN TABLE_EXISTS_EXCEPTION THEN
		NULL;
END;
/

CREATE OR REPLACE TRIGGER "TRIGGER_MOB_TEMPSTORAGE" 
    BEFORE INSERT 
    ON "MOB_TEMPSTORAGE" 
    FOR EACH ROW
BEGIN
    SELECT SEQ_MOB_TEMPSTORAGE.NEXTVAL 
        INTO :NEW.TS_ID 
        FROM DUAL;
END;
/

-- MOB_TEMPSTOREBLOBS
DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_TEMPSTOREBLOBS (
		TS_ID int NOT NULL,
		SEQUENCE int NOT NULL,
		BLOB_VALUE blob NOT NULL,
		CONSTRAINT FK_MOB_TSB_TSID FOREIGN KEY(TS_ID) REFERENCES MOB_TEMPSTORAGE(TS_ID) ON DELETE CASCADE
	)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_MOB_TSBLOBS2 ON MOB_TEMPSTOREBLOBS(TS_ID, SEQUENCE)';
EXCEPTION
	WHEN TABLE_EXISTS_EXCEPTION THEN
		NULL;
END;
/

-- MOB_HISTORY
DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_HISTORY (
		HISTORY_ID int PRIMARY KEY NOT NULL, -- identity
		EVENT_TIME timestamp NULL,
		EVENT_CODE int NOT NULL,
		ARGUMENTS varchar (1000) NULL
	)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_HISTORY_CODE ON  MOB_HISTORY(EVENT_CODE)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_HISTORY_TIME ON MOB_HISTORY(EVENT_TIME)';
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_HISTORY START WITH 1 INCREMENT BY 1 NOMAXVALUE';
EXCEPTION
	WHEN OTHERS THEN
		NULL;
END;
/
CREATE OR REPLACE TRIGGER "TRIGGER_MOB_HISTORY" BEFORE INSERT ON "MOB_HISTORY" FOR EACH ROW
	BEGIN
		SELECT SEQ_MOB_HISTORY.nextval INTO :new.HISTORY_ID FROM DUAL;
	END;
/

-- MOB_USER_HISTORY
DECLARE
	TABLE_EXISTS_EXCEPTION EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS_EXCEPTION, -955);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE MOB_USER_HISTORY (
		USER_ID int NOT NULL,
		HISTORY_ID int NOT NULL,
		CONSTRAINT FK_MOB_UH_UID FOREIGN KEY(USER_ID) REFERENCES MOB_USERS(USER_ID) ON DELETE CASCADE, 
		CONSTRAINT FK_MOB_UH_HID FOREIGN KEY(HISTORY_ID) REFERENCES MOB_HISTORY(HISTORY_ID) ON DELETE CASCADE,
		CONSTRAINT PK_MOB_UH_UID PRIMARY KEY(USER_ID, HISTORY_ID)
	)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_HIST_USER_ID ON  MOB_USER_HISTORY(USER_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;
/