analytics/configuration/schemas/mobile/sqlserver/upgrade-01-023-to-02-000.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.

-- 023-024
DROP TABLE MOB_CODE;
DROP TABLE MOB_STRINGS;
DROP TABLE MOB_INBOXES;
DROP TABLE MOB_JOB;
DROP TABLE MOB_JOB_RECIPIENT;

DECLARE @name nvarchar(32),
	@sql nvarchar(1000)

SELECT @name = O.name
FROM sysobjects AS O
LEFT JOIN sysobjects AS T
ON O.parent_obj = T.id
WHERE isnull(objectproperty(O.id,'IsMSShipped'),1) = 0
AND O.name NOT LIKE '%dtproper%'
AND O.name NOT LIKE 'dt[_]%'
AND T.name = 'MOB_RENDERS'
AND O.name LIKE 'DF__MOB_RENDE__JOB_I%'

IF NOT @name IS NULL
BEGIN
	SELECT @sql = 'ALTER TABLE [MOB_RENDERS] DROP CONSTRAINT [' + @name + ']'
	EXECUTE sp_executesql @sql
END

-- MOB_RESOURCES
CREATE TABLE [MOB_RESOURCES] (
	[RESOURCE_ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[LABEL] [nvarchar] (100) NULL,
	[IDENTIFIER] [varchar] (200) NULL,
	[CM_PATH] [nvarchar] (1000) NULL,
	[SOURCE_PATH] [nvarchar] (1000) NULL,
	[DESCRIPTION] [nvarchar] (500) NULL,
	[RESOURCE_TYPE] [int] NOT NULL DEFAULT 0
)
CREATE INDEX [IDX_MOB_REP_ID] ON [MOB_RESOURCES]([IDENTIFIER])

-- MOB_RENDERS
DROP TABLE [MOB_RENDERS]
CREATE TABLE [MOB_RENDERS] (
	[RENDER_ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[RENDER_TIME] [datetime] NOT NULL,
	[RENDER_SIZE] [int] NOT NULL,
	[STATUS_CODE] [int] NULL,
	[SOURCE_CODE] [tinyint] NULL,
	[DRILL_PARAMS] [nvarchar] (1000) NULL,
	[RESOURCE_ID] [int] NOT NULL,
	CONSTRAINT [FK_MOB_R_RSID] FOREIGN KEY(RESOURCE_ID) REFERENCES MOB_RESOURCES(RESOURCE_ID)
)

-- MOB_HISTORY
CREATE TABLE [MOB_HISTORY] (
	[HISTORY_ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[EVENT_TIME] [datetime] NULL,
	[EVENT_CODE] [int] NOT NULL,
	[ARGUMENTS] [varchar] (1000) NULL
)
CREATE INDEX [IDX_MOB_HIST_CODE] ON [MOB_HISTORY]([EVENT_CODE])
CREATE INDEX [IDX_MOB_HIST_TIME] ON [MOB_HISTORY]([EVENT_TIME])

-- MOB_USERS
DROP TABLE [MOB_USERS]
CREATE TABLE [MOB_USERS] (
	[USER_ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[DEVICE_ID] [varchar] (100) NOT NULL,
	[DEVICE_PROFILE] [varchar] (200) NULL,
	[CREDENTIAL_PATH] [varchar] (200) NULL,
	[CAM_ID] [varchar] (100) NULL,
	[KEY_TYPE] [varchar] (20) NULL,
	[KEY_BYTES] [varbinary] (256) NULL,
	[SYNC_METHOD] [varchar] (20) NULL,
	[LAST_LOGIN] [datetime] NULL
)
CREATE INDEX [IDX_CAM_ID] ON [MOB_USERS]([CAM_ID])

-- MOB_USER_HISTORY
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
)
CREATE INDEX [IDX_MOB_UH_USER] ON [MOB_USER_HISTORY]([USER_ID])

-- 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
)
CREATE INDEX [IDX_MOB_RH_RENDER] ON [MOB_RENDER_HISTORY]([RENDER_ID])

-- MOB_USER_RENDER
CREATE TABLE [MOB_USER_RENDER] (
	[USER_ID] [int] NOT NULL,
	[RENDER_ID] [int] NOT NULL,
	[NAME] [varchar] (100) NULL,
	[LAST_VIEWED] [datetime] 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
)

-- 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])
)

-- MOB_USER_RESOURCE_TYPES
CREATE TABLE [MOB_USER_RESOURCE_TYPES] (
	[TYPE] [tinyint] NOT NULL,
	[DESCRIPTION] [varchar] (100) NULL 
)

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

-- MOB_BLOBS
DROP TABLE [MOB_BLOBS]
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] [varbinary] (1024) NOT NULL,
	[FORMAT] [tinyint] 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
)
CREATE INDEX [IDX_MOB_BLOBS] ON [MOB_BLOBS]([BLOB_ID], [SEQUENCE])

-- MOB_BLOB_FORMATS
CREATE TABLE [MOB_BLOB_FORMATS] (
	[FORMAT] [tinyint] NOT NULL,
	[DESCRIPTION] [varchar] (100) NULL 
)

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, '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_SOURCE_CODES
CREATE TABLE [MOB_SOURCE_CODES] (
	[SOURCE_CODE] [tinyint] NOT NULL,
	[DESCRIPTION] [varchar] (100) NULL 
)
CREATE INDEX [PK_MOB_SOURCE_CODES] ON [MOB_SOURCE_CODES]([SOURCE_CODE])

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
DROP TABLE [MOB_TEMPSTOREBLOBS]
DROP TABLE [MOB_TEMPSTORAGE]
CREATE TABLE [MOB_TEMPSTORAGE] (
	[TS_ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[PATH] [varchar] (500) NULL,
	[CREATED] [datetime] NOT NULL,
	[MODIFIED] [datetime] NOT NULL 
)
CREATE INDEX [IDX_MOB_MODIFIED] ON [MOB_TEMPSTORAGE]([MODIFIED])
CREATE INDEX [IDX_MOB_CREATED] ON [MOB_TEMPSTORAGE]([CREATED])
CREATE INDEX [IDX_MOB_TMPPATH] ON [MOB_TEMPSTORAGE]([PATH])

-- MOB_TEMPSTOREBLOBS
CREATE TABLE [MOB_TEMPSTOREBLOBS] (
	[TS_ID] [int] NOT NULL,
	[SEQUENCE] [int] NOT NULL,
	[BLOB_VALUE] [varbinary] (1024) NOT NULL,
	CONSTRAINT [FK_MOB_TSB_TSID] FOREIGN KEY(TS_ID) REFERENCES MOB_TEMPSTORAGE(TS_ID) ON DELETE CASCADE
)
CREATE INDEX [IDX_MOB_TSBLOBS2] ON [MOB_TEMPSTOREBLOBS]([TS_ID], [SEQUENCE])

EXEC sp_rename 'MOB_RENDER_STATUS_CODES', 'MOB_STATUS_CODES'
DROP TABLE MOB_RENDER_STATUS