-- 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 IBM DB2 on z/OS.
--
--  Modifications:
--
--  1) Change COGMOBDB  to the desired database name.
--  2) Change DB0AUSR   to the desired storage group name.
--  3) Note: CCSID is an estimated value, your requirements may differ.

-- MOB_RESOURCES
CREATE TABLE MOB_RESOURCES (
	RESOURCE_ID int NOT NULL PRIMARY KEY generated always as identity (start with 1, increment by 1),
	LABEL varchar (100) ,
	IDENTIFIER varchar (200) ,
	CM_PATH varchar (1000) ,
	SOURCE_PATH varchar (1000) ,
	DESCRIPTION varchar (500) ,
	RESOURCE_TYPE int NOT NULL DEFAULT 0
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/
CREATE UNIQUE INDEX PK_MOB_RESOURCES ON MOB_RESOURCES(RESOURCE_ID) USING STOGROUP DB0AUSR
/
CREATE INDEX IDX_MOB_REP_ID ON MOB_RESOURCES(IDENTIFIER) USING STOGROUP DB0AUSR
/

-- MOB_RENDERS
CREATE TABLE MOB_RENDERS (
	RENDER_ID int NOT NULL PRIMARY KEY generated always as identity (start with 1, increment by 1),
	RENDER_TIME timestamp NOT NULL,
	RENDER_SIZE int NOT NULL,
	STATUS_CODE int ,
	SOURCE_CODE smallint ,
	DRILL_PARAMS varchar (1000) ,
	RESOURCE_ID int NOT NULL,
	CONSTRAINT FK_MOB_R_RSID FOREIGN KEY(RESOURCE_ID) REFERENCES MOB_RESOURCES(RESOURCE_ID)
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/
CREATE UNIQUE INDEX PK_MOB_RENDERS ON MOB_RENDERS(RENDER_ID) USING STOGROUP DB0AUSR
/


-- MOB_USERS
CREATE TABLE MOB_USERS (
	USER_ID int NOT NULL PRIMARY KEY generated always as identity (start with 1, increment by 1),
	DEVICE_ID varchar (100) NOT NULL,
	DEVICE_PROFILE varchar (200) ,
	CREDENTIAL_PATH varchar (200) ,
	CAM_ID varchar (100) ,
	KEY_TYPE varchar (20) ,
	KEY_BYTES varchar (256) for bit data,
	SYNC_METHOD varchar (20) ,
	LAST_LOGIN timestamp 
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/

CREATE UNIQUE INDEX PK_MOB_USERS ON MOB_USERS(USER_ID) USING STOGROUP DB0AUSR
/
CREATE INDEX IDX_CAM_ID ON MOB_USERS(CAM_ID) USING STOGROUP DB0AUSR
/

-- MOB_USER_RENDER
CREATE TABLE MOB_USER_RENDER (
	USER_ID int NOT NULL,
	RENDER_ID int NOT NULL,
	NAME varchar (100) ,
	LAST_VIEWED timestamp ,
	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)
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/

CREATE UNIQUE INDEX PK_MOB_USERRNDR ON MOB_USER_RENDER(USER_ID, RENDER_ID) USING STOGROUP DB0AUSR
/

-- MOB_USER_RESOURCE
CREATE TABLE MOB_USER_RESOURCE (
	USER_ID int NOT NULL,
	RESOURCE_ID int NOT NULL,
	USER_RESOURCE_TYPE int NOT NULL DEFAULT 0,
	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)
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/

CREATE UNIQUE INDEX PK_MOB_USERRSRC ON MOB_USER_RESOURCE(USER_ID, RESOURCE_ID) USING STOGROUP DB0AUSR
/

-- MOB_USER_RESOURCE_TYPES
CREATE TABLE MOB_USER_RESOURCE_TYPES (
	TYPE smallint NOT NULL,
	DESCRIPTION varchar (100)  
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/

INSERT INTO MOB_USER_RESOURCE_TYPES VALUES (0, 'DASHBOARD')
/
INSERT INTO MOB_USER_RESOURCE_TYPES VALUES (1, 'FAVORITE')
/

-- MOB_BLOBS
CREATE TABLE MOB_BLOBS (
	BLOB_ID int NOT NULL,
	RENDER_ID int ,
	RESOURCE_ID int ,
	USER_ID int ,
	SEQUENCE int NOT NULL,
	BLOB_VALUE varchar (1024) FOR BIT DATA,
	FORMAT smallint ,
	OBJECT_INDEX int , 
	PAGE_INDEX int ,
	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
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/
CREATE INDEX IDX_MOB_BLOBS ON MOB_BLOBS(RENDER_ID, BLOB_ID, SEQUENCE) USING STOGROUP DB0AUSR
/

-- MOB_BLOB_FORMATS
CREATE TABLE MOB_BLOB_FORMATS (
	FORMAT smallint NOT NULL,
	DESCRIPTION varchar (100)  
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/

INSERT INTO MOB_BLOB_FORMATS VALUES (0, 'USER COOKIE')
/
INSERT INTO MOB_BLOB_FORMATS VALUES (1, 'REPORT INFO')
/
INSERT INTO MOB_BLOB_FORMATS VALUES (2, 'REPORT OUTPUT')
/
INSERT INTO MOB_BLOB_FORMATS VALUES (3, 'DB')
/
INSERT INTO MOB_BLOB_FORMATS VALUES (4, 'PAGE HTML')
/
INSERT INTO MOB_BLOB_FORMATS VALUES (5, 'IMAGE')
/
INSERT INTO MOB_BLOB_FORMATS VALUES (6, 'RENDER THUMBNAIL SMALL')
/
INSERT INTO MOB_BLOB_FORMATS VALUES (7, 'RENDER THUMBNAIL LARGE')
/
INSERT INTO MOB_BLOB_FORMATS VALUES (8, 'iOS THUMBNAIL SMALL')
/
INSERT INTO MOB_BLOB_FORMATS VALUES (9, 'iOS THUMBNAIL LARGE')
/
INSERT INTO MOB_BLOB_FORMATS VALUES (10, 'SVG')
/
INSERT INTO MOB_BLOB_FORMATS VALUES (11, 'TABLE HTML')
/
INSERT INTO MOB_BLOB_FORMATS VALUES (12, 'ATTCH IMAGE')
/
INSERT INTO MOB_BLOB_FORMATS VALUES (13, 'CONVERSATION STATE')
/
INSERT INTO MOB_BLOB_FORMATS VALUES (14, 'DASHBOARD')
/
INSERT INTO MOB_BLOB_FORMATS VALUES (15, 'BUX CONVERSATION STATE')
/

-- MOB_STATUS_CODES
CREATE TABLE MOB_STATUS_CODES (
	STATUS_CODE smallint NOT NULL,
	DESCRIPTION varchar (100)  
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/
CREATE INDEX PK_M_R_S_C ON MOB_STATUS_CODES(STATUS_CODE) USING STOGROUP DB0AUSR
/

INSERT INTO MOB_STATUS_CODES VALUES (1, 'IN PROGRESS')
/
INSERT INTO MOB_STATUS_CODES VALUES (2, 'COMPLETED')
/
INSERT INTO MOB_STATUS_CODES VALUES (3, 'PENDING')
/
INSERT INTO MOB_STATUS_CODES VALUES (9, 'CANCELLED')
/
INSERT INTO MOB_STATUS_CODES VALUES (99, 'ERROR')
/

-- MOB_SOURCE_CODES
CREATE TABLE MOB_SOURCE_CODES (
	SOURCE_CODE smallint NOT NULL,
	DESCRIPTION varchar (100)  
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/
CREATE INDEX PK_M_R_SO_C ON MOB_SOURCE_CODES(SOURCE_CODE) USING STOGROUP DB0AUSR
/

INSERT INTO MOB_SOURCE_CODES VALUES (0, 'AD-HOC')
/
INSERT INTO MOB_SOURCE_CODES VALUES (1, 'SCHEDULED/RUN-WITH-OPTIONS')
/
INSERT INTO MOB_SOURCE_CODES VALUES (2, 'DRILL-THROUGH')
/
INSERT INTO MOB_SOURCE_CODES VALUES (3, 'DRILL-UP/DOWN')
/

-- MOB_TEMPSTORAGE
CREATE TABLE MOB_TEMPSTORAGE (
	TS_ID int NOT NULL PRIMARY KEY generated always as identity (start with 1, increment by 1),
	PATH varchar (500) ,
	CREATED timestamp NOT NULL,
	MODIFIED timestamp NOT NULL 
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/
CREATE UNIQUE INDEX PK_MOB_TS ON MOB_TEMPSTORAGE(TS_ID) USING STOGROUP DB0AUSR
/
CREATE INDEX IDX_MOB_MODIFIED ON MOB_TEMPSTORAGE(MODIFIED) USING STOGROUP DB0AUSR
/
CREATE INDEX IDX_MOB_CREATED ON MOB_TEMPSTORAGE(CREATED) USING STOGROUP DB0AUSR
/
CREATE INDEX IDX_MOB_TMPPATH ON MOB_TEMPSTORAGE(PATH) USING STOGROUP DB0AUSR
/

-- MOB_TEMPSTOREBLOBS
CREATE TABLE MOB_TEMPSTOREBLOBS (
	TS_ID int NOT NULL,
	SEQUENCE int NOT NULL,
	BLOB_VALUE blob (1024) NOT NULL,
	CONSTRAINT FK_MOB_TSB_TSID FOREIGN KEY(TS_ID) REFERENCES MOB_TEMPSTORAGE(TS_ID) ON DELETE CASCADE
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/

CREATE UNIQUE INDEX IDX_MOB_TSBLOBS2 ON MOB_TEMPSTOREBLOBS(TS_ID, SEQUENCE) USING STOGROUP DB0AUSR
/
CREATE AUXILIARY TABLE MOB_ATB01
   IN COGMOBDB.MOBTSL01
   STORES MOB_TEMPSTOREBLOBS
   COLUMN BLOB_VALUE
/
CREATE  UNIQUE  INDEX IDXMOB_ATB01 ON MOB_ATB01 USING STOGROUP DB0AUSR 
/

-- MOB_HISTORY
CREATE TABLE MOB_HISTORY (
	HISTORY_ID int NOT NULL PRIMARY KEY generated always as identity (start with 1, increment by 1),
	EVENT_TIME timestamp,
	EVENT_CODE int,
	ARGUMENTS varchar (1000)
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/

CREATE UNIQUE INDEX PK_MOB_HISTORY ON MOB_HISTORY(HISTORY_ID) USING STOGROUP DB0AUSR
/
CREATE INDEX IDX_HISTORY_CODE ON MOB_HISTORY(EVENT_CODE) USING STOGROUP DB0AUSR
/
CREATE INDEX IDX_HISTORY_TIME ON MOB_HISTORY(EVENT_TIME) USING STOGROUP DB0AUSR
/

-- MOB_USER_HISTORY
CREATE TABLE MOB_USER_HISTORY (
	USER_ID int,
	HISTORY_ID int,
	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
	
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/
CREATE INDEX IDX_HIST_USER_ID ON MOB_USER_HISTORY(USER_ID) USING STOGROUP DB0AUSR
/

-- MOB_RENDER_HISTORY
CREATE TABLE MOB_RENDER_HISTORY (
	RENDER_ID int,
	HISTORY_ID int,
	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
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/
CREATE INDEX IDX_MOB_RH_RID ON MOB_RENDER_HISTORY(RENDER_ID) USING STOGROUP DB0AUSR
/