analytics/configuration/schemas/mobile/db2zOS/upgrade-02-000d-to-02-001.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.

--
--  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_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,
	LAST_UPDATED timestamp NOT NULL WITH DEFAULT
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/
CREATE UNIQUE INDEX PK_MOB_USERS ON MOB_USERS(USER_ID) USING STOGROUP DB0AUSR
/

-- MOB_PORTALITEMS
CREATE TABLE MOB_PORTALITEMS (
	PORTALITEM_ID int NOT NULL PRIMARY KEY generated always as identity (start with 1, increment by 1),
	LABEL varchar (100),
	STORE_ID varchar (200),
	CM_PATH varchar (1000),
	SOURCE_PATH varchar (1000),
	DESCRIPTION varchar (500),
	PORTALITEM_TYPE int NOT NULL DEFAULT 0
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/
CREATE UNIQUE INDEX PK_MOB_PORTALITEMS ON MOB_PORTALITEMS(PORTALITEM_ID) USING STOGROUP DB0AUSR
/
CREATE INDEX IDX_MOB_REP_SI ON MOB_PORTALITEMS(STORE_ID) USING STOGROUP DB0AUSR
/

-- MOB_USER_PORTALITEM
CREATE TABLE MOB_USER_PORTALITEM (
	USER_ID int NOT NULL,
	PORTALITEM_ID int NOT NULL,
	USER_PORTALITEM_TYPE int NOT NULL DEFAULT 0,
	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, USER_PORTALITEM_TYPE)
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/
CREATE UNIQUE INDEX PK_MOB_USERPIID ON MOB_USER_PORTALITEM(USER_ID, PORTALITEM_ID, USER_PORTALITEM_TYPE) 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 NOT NULL,
	SOURCE_CODE smallint,
	DRILL_PARAMS varchar (1000),
	PORTALITEM_ID int NOT NULL,
	BASE_DOC varchar (200),
	SAVED_OUTPUT_TYPE smallint,
	CREATION_TIME timestamp NOT NULL WITH DEFAULT,
	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
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/
CREATE UNIQUE INDEX PK_MOB_RENDERS ON MOB_RENDERS(RENDER_ID) USING STOGROUP DB0AUSR
/

-- MOB_BLOBS
CREATE TABLE MOB_BLOBS (
	HASH char (32),
	USER_ID int,
	SEQUENCE int NOT NULL,
	BLOB_VALUE varchar (1024) FOR BIT DATA NOT NULL,
	ADDED timestamp NOT NULL WITH DEFAULT,
	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_HASH ON MOB_BLOBS(HASH) USING STOGROUP DB0AUSR
/

-- MOB_RESOURCES
CREATE TABLE MOB_RESOURCES (
	RESOURCE_ID int NOT NULL PRIMARY KEY generated always as identity (start with 1, increment by 1),
	RENDER_ID int,
	PATH varchar (200),
	HASH char (32),
	FORMAT smallint,
	HEIGHT int,
	WIDTH int,
	BLOB_ID int,
	OBJECT_INDEX int,
	PAGE_INDEX int,
	CONSTRAINT FK_MOB_RES_RID FOREIGN KEY(RENDER_ID) REFERENCES MOB_RENDERS(RENDER_ID) ON DELETE CASCADE
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/
CREATE UNIQUE INDEX PK_MOB_RESOURCES ON MOB_RESOURCES(RESOURCE_ID) USING STOGROUP DB0AUSR
/
CREATE INDEX IDX_MOB_RESOURCES ON MOB_RESOURCES(RENDER_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_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 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
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/
CREATE INDEX IDX_MOB_RH_RID ON MOB_RENDER_HISTORY(RENDER_ID) USING STOGROUP DB0AUSR
/

-- MOB_DRILLS
CREATE TABLE MOB_DRILLS (
	SOURCE_RENDER_ID int NOT NULL,
	TARGET_RENDER_ID int NOT NULL,
	DRILL_CONTEXT varchar (1000) NOT NULL,
	DRILL_TYPE smallint NOT NULL,
	CONSTRAINT FK_MOB_D_SRID FOREIGN KEY(SOURCE_RENDER_ID) REFERENCES MOB_RENDERS(RENDER_ID) ON DELETE CASCADE
) IN COGMOBDB.COGMOBTS CCSID UNICODE
/