analytics/configuration/schemas/mobile/sqlserver/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.

ALTER TABLE [MOB_RENDERS] DROP CONSTRAINT [FK_MOB_R_RSID]
ALTER TABLE [MOB_USER_RESOURCE] DROP CONSTRAINT [FK_MOB_URS_RSID]
ALTER TABLE [MOB_BLOBS] DROP CONSTRAINT [FK_MOB_BLOBS_RSID]
ALTER TABLE [MOB_BLOBS] DROP CONSTRAINT [FK_MOB_BLOBS_RID]
ALTER TABLE [MOB_USER_RENDER] DROP CONSTRAINT [FK_MOB_UR_UID]
ALTER TABLE [MOB_USER_RENDER] DROP CONSTRAINT [FK_MOB_UR_RID]
ALTER TABLE [MOB_RENDER_HISTORY] DROP CONSTRAINT [FK_MOB_RH_RID]
ALTER TABLE [MOB_RENDER_HISTORY] DROP CONSTRAINT [FK_MOB_RH_HID]

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_RESOURCES'
AND (O.name LIKE 'DF__MOB_RESOU__RESOU__%')

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

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_USER_RESOURCE'
AND (O.name LIKE 'DF__MOB_USER___USER___%')

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

DROP TRIGGER [TGR_MOB_URSRC_SYNC_IN]
DROP TRIGGER [TGR_MOB_URSRC_SYNC_UP]
DROP TRIGGER [TGR_MOB_URSRC_SYNC_DE]

DROP TABLE [MOB_RESOURCES]
DROP TABLE [MOB_USER_RESOURCE]
DROP TABLE [MOB_USER_RESOURCE_TYPES]

-- MOB_PORTALITEMS
CREATE TABLE [MOB_PORTALITEMS] (
	[PORTALITEM_ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[LABEL] [nvarchar] (100) NULL,
	[STORE_ID] [varchar] (200) NULL,
	[CM_PATH] [nvarchar] (1000) NULL,
	[SOURCE_PATH] [nvarchar] (1000) NULL,
	[DESCRIPTION] [nvarchar] (500) NULL,
	[PORTALITEM_TYPE] [int] NOT NULL DEFAULT 0
)
CREATE INDEX [IDX_MOB_REP_SI] ON [MOB_PORTALITEMS]([STORE_ID])

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

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

INSERT INTO [MOB_USER_PORTALITEM_TYPES] VALUES (0, 'HOMEREPORT')
INSERT INTO [MOB_USER_PORTALITEM_TYPES] VALUES (1, 'FAVORITE')

DROP TABLE [MOB_USER_RENDER]
DROP TABLE [MOB_RENDER_HISTORY]

-- 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,
	[PORTALITEM_ID] [int] NOT NULL,
	[BASE_DOC] nvarchar (200) NULL,
	[SMALL_THUMB] nvarchar (200) NULL,
	[MEDIUM_THUMB] nvarchar (200) NULL,
	[LARGE_THUMB] nvarchar (200) NULL,
	CONSTRAINT [FK_MOB_R_PIID] FOREIGN KEY(PORTALITEM_ID) REFERENCES MOB_PORTALITEMS(PORTALITEM_ID) ON DELETE CASCADE
)

-- MOB_BLOBS
DROP TABLE [MOB_BLOBS]
CREATE TABLE [MOB_BLOBS] (
	[HASH] [char] (32) NULL,
	[USER_ID] [int] NULL,
	[SEQUENCE] [int] NOT NULL,
	[BLOB_VALUE] [varbinary] (1024) NOT NULL,
	[FORMAT] [tinyint] NULL,
	[ADDED] [datetime] DEFAULT GETDATE() NOT NULL,
	CONSTRAINT [FK_MOB_BLOBS_UID] FOREIGN KEY(USER_ID) REFERENCES MOB_USERS(USER_ID) ON DELETE CASCADE
)
CREATE INDEX [IDX_MOB_BLOBS_HASH] ON [MOB_BLOBS]([HASH])

-- MOB_RESOURCES
CREATE TABLE [MOB_RESOURCES] (
	[RESOURCE_ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[RENDER_ID] [int] NULL,
	[PATH] nvarchar (200) NULL,
	[HASH] [char] (32) NULL,
	CONSTRAINT [FK_MOB_RES_RID] FOREIGN KEY(RENDER_ID) REFERENCES MOB_RENDERS(RENDER_ID) ON DELETE CASCADE,
)
CREATE INDEX [IDX_MOB_RESOURCES] ON [MOB_RESOURCES]([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,
	CONSTRAINT [PK_MOB_USERRNDR] PRIMARY KEY([USER_ID], [RENDER_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_RID] ON [MOB_RENDER_HISTORY]([RENDER_ID])