--**************************************************************************************************
--*
--*	This script will update PS10 web tables to PS11 web tables.
--*
--*	Privileges needed: member of db_owner or db_ddladmin
--*
--*	Comments
--*	-----------------------------------------------------------------------------------------
--*     1 - In order to specify non-default filegroups, uncomment the "-- ON [PRIMARY]" line 
--*         within each CREATE statement and replace "Primary" with the desired filegroup
--*
--**************************************************************************************************

--****************************************************************************************************
--*		Drop obsolete P10 tables from P11 schema
--****************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_WEB_MESSAGES_DELEGATIONS')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_WEB_MESSAGES_DELEGATIONS
GO


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_WEB_VIEW_RESOURCES')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_WEB_VIEW_RESOURCES
GO


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_WEB_VIEW_PROJECTCATEGORIES')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_WEB_VIEW_PROJECTCATEGORIES
GO


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_WEB_VIEW_REPORTCATEGORIES')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_WEB_VIEW_REPORTCATEGORIES
GO


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_WEB_VIEW_CATEGORIES')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_WEB_VIEW_CATEGORIES
GO


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_WEB_RESOURCES_CATEGORIES')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_WEB_RESOURCES_CATEGORIES
GO


--**************************************************************************************************
--*		MSP_WEB_ADMIN
--**************************************************************************************************

ALTER TABLE dbo.MSP_WEB_ADMIN ADD CONSTRAINT 
   DF_MSP_WEB_ADMIN_WADMIN_NPE_SCHEDULED_TIME DEFAULT 0 for WADMIN_NPE_SCHEDULED_TIME
GO

ALTER TABLE dbo.MSP_WEB_ADMIN ALTER COLUMN
   WADMIN_NPE_SCHEDULED_TIME 		int 			NULL
GO

-- When upgrading, we should rename the old fields to preserve them instead of dropping them for STS migration purposes
EXEC sp_rename 'MSP_WEB_ADMIN.[WADMIN_PUBDOCS_STS_SERVER_ID]', 'WADMIN_PUBDOCS_STS_SERVER_ID_P2002', 'COLUMN'

-- When upgrading, we should rename the old fields to preserve them instead of dropping them for STS migration purposes
EXEC sp_rename 'MSP_WEB_ADMIN.[WADMIN_PUBDOCS_STS_SUBWEB_NAME]', 'WADMIN_PUBDOCS_STS_SUBWEB_NAME_P2002', 'COLUMN'

ALTER TABLE dbo.MSP_WEB_ADMIN ADD
   WADMIN_PROTECT_ACTUALS 		tinyint 		NOT NULL DEFAULT 0,
   WADMIN_STS_TEMPLATE_LCID		int			NULL,
   WADMIN_STS_TEMPLATE_ID		nvarchar(50)		NULL,
   WADMIN_STS_PRIMARY_OWNER_EMAIL	nvarchar(100)		NULL,
   WADMIN_STS_SECONDARY_OWNER_NAME	nvarchar(255)		NULL,
   WADMIN_STS_SECONDARY_OWNER_EMAIL	nvarchar(100)		NULL,
   WADMIN_STS_PORTAL_URL		nvarchar(1024)		NULL,
   WADMIN_STS_PORTAL_NAME		nvarchar(255)		NULL,
   WADMIN_BUILD_TEAM_BY_RBS     	tinyint   		NOT NULL Default 1,
   WADMIN_PUBDOCS_STS_SUBWEB_NAME 	nvarchar(128) 		NOT NULL DEFAULT (N'MS_ProjectServer_PublicDocuments'),
   WADMIN_VERSION_MAJOR          	tinyint 	        NULL,
   WADMIN_VERSION_MINOR          	tinyint         	NULL,
   WADMIN_VERSION_BUILD          	int             	NULL,
   WADMIN_VERSION_REVISION       	tinyint         	NULL,
   WADMIN_IS_HOSTED_ORG			tinyint 		NOT NULL DEFAULT 0
GO

--**************************************************************************************************
--*		MSP_WEB_TRANSACTIONS
--**************************************************************************************************

ALTER TABLE dbo.MSP_WEB_TRANSACTIONS ALTER COLUMN
   WTRANS_DESC 				nvarchar(255)		NULL
GO

--**************************************************************************************************
--*		MSP_WEB_VIEW_REPORTS
--**************************************************************************************************

ALTER TABLE dbo.MSP_WEB_VIEW_REPORTS ADD
   WVIEW_GROUPING_SORTING_PARAMS 	nvarchar(256) 		NULL,
   WVIEW_FILTER_BY_RBS 			tinyint 		NOT NULL DEFAULT 0 CHECK ((WVIEW_FILTER_BY_RBS >= 0) AND (WVIEW_FILTER_BY_RBS <= 1)),
   WVIEW_OUTLINE_LEVEL                  smallint                NOT NULL DEFAULT -1,
   WVIEW_SPLITTER_POS                   int                     NOT NULL DEFAULT 400
GO

--**************************************************************************************************
--*		MSP_WEB_VIEW_REPORTS_FIELDS
--**************************************************************************************************

ALTER TABLE dbo.MSP_WEB_VIEW_REPORTS_FIELDS ADD
   WVIEW_FIELD_WIDTH                    int                	NULL,
   WVIEW_FIELD_AUTOSIZE                 tinyint                 NOT NULL DEFAULT 1
GO


ALTER TABLE dbo.MSP_WEB_VIEW_REPORTS_FIELDS ADD CONSTRAINT
   DF_WEB_VIEW_REPORTS_FIELDS_FIELD_WIDTH DEFAULT 100 FOR WVIEW_FIELD_WIDTH WITH VALUES
GO


UPDATE MSP_WEB_VIEW_REPORTS_FIELDS SET
   WVIEW_FIELD_WIDTH = 100
WHERE
   WVIEW_FIELD_WIDTH IS NULL
GO


ALTER TABLE dbo.MSP_WEB_VIEW_REPORTS_FIELDS ALTER COLUMN
   WVIEW_FIELD_WIDTH 			int			NOT NULL
GO

--**************************************************************************************************
--*		MSP_WEB_SECURITY_GROUPS
--**************************************************************************************************

ALTER TABLE dbo.MSP_WEB_SECURITY_GROUPS ADD
   WSEC_GRP_AD_GROUP 			nvarchar(64)		NULL,
   WSEC_GRP_AD_LOG 			smallint 		NULL
GO

--**************************************************************************************************
--*		MSP_WEB_ASSIGNMENTS
--**************************************************************************************************

ALTER TABLE dbo.MSP_WEB_ASSIGNMENTS ADD
   ASSN_BOOKING_TYPE 			smallint 		NOT NULL DEFAULT 0,
   WASSN_ASSIGNED_TO_EXISTING           tinyint                 NOT NULL DEFAULT 0
GO

--**************************************************************************************************
--*		MSP_WEB_ASSIGNMENTS
--**************************************************************************************************

DECLARE @ConstraintName 	varchar(200)
DECLARE @DropText 		varchar(200)

SELECT @ConstraintName = CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CHECK_CLAUSE like '%WASSN_ACTUALS_PENDING%' 

SELECT @DropText = 'ALTER TABLE dbo.MSP_WEB_ASSIGNMENTS DROP CONSTRAINT ' + @ConstraintName
EXEC (@DropText)

ALTER TABLE dbo.MSP_WEB_ASSIGNMENTS ADD CONSTRAINT
   CK_WEB_ASSIGNMENTS_ACTUALS_PENDING CHECK ((WASSN_ACTUALS_PENDING >= 0) AND (WASSN_ACTUALS_PENDING <= 2))
GO

--**************************************************************************************************
--*		MSP_WEB_RESOURCES
--**************************************************************************************************

ALTER TABLE dbo.MSP_WEB_RESOURCES ADD
   WRES_AD_GUID 			uniqueidentifier 	NULL,
   WRES_RBS_UID           		int      		NULL,
   WRES_WSS_VALIDATOR_GUID		nvarchar(32)		NULL
GO

update  wr
set wr.WRES_RBS_UID = cf.CODE_UID
from MSP_WEB_RESOURCES wr inner join MSP_CODE_FIELDS cf on wr.RES_EUID = cf.CODE_REF_UID
where cf.PROJ_ID = 1 and CODE_CATEGORY = 1 and CODE_FIELD_ID = 205521540

--**************************************************************************************************
--*		MSP_WEB_ADMIN_AD
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_WEB_ADMIN_AD') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_WEB_ADMIN_AD 
GO

CREATE TABLE dbo.MSP_WEB_ADMIN_AD 
(
   WADMIN_AD_ID				int IDENTITY(1, 1) 	NOT NULL,
   WADMIN_AD_GRP_ENABLE_UPDATE 		tinyint 		NOT NULL DEFAULT 0 CHECK (WADMIN_AD_GRP_ENABLE_UPDATE IN (0, 1)),
   WADMIN_AD_GRP_AUTO_LAST_RUN 		datetime 		NULL,
   WADMIN_AD_GRP_LOG_TIMESTAMP 		datetime 		NULL,
   WADMIN_AD_GRP_LOG_STATUS 		int 			NULL CHECK (WADMIN_AD_GRP_LOG_STATUS IN (0, 1, 2)),
   WADMIN_AD_GRP_LOG_STATUS_TEXT 	nvarchar(255) 		NULL,
   WADMIN_AD_GRP_UPDATE_FREQ_EVERY 	int 			NOT NULL DEFAULT 0,
   WADMIN_AD_GRP_UPDATE_FREQ_UNIT 	int 			NOT NULL DEFAULT 1 CHECK (WADMIN_AD_GRP_UPDATE_FREQ_UNIT IN (0, 1, 2)),
   WADMIN_AD_GRP_START_DATE 		datetime 		NULL,
   WADMIN_AD_GRP_START_TIME 		int 			NULL,
   WADMIN_AD_GRP_UPDATE 		tinyint 		NULL, 
   WADMIN_AD_ERESPOOL_ENABLE_UPDATE 	tinyint 		NOT NULL DEFAULT 0 CHECK (WADMIN_AD_ERESPOOL_ENABLE_UPDATE IN (0, 1)),
   WADMIN_AD_ERESPOOL_GROUP 		nvarchar(64) 		NULL,
   WADMIN_AD_ERESPOOL_AUTO_LAST_RUN 	datetime 		NULL,
   WADMIN_AD_ERESPOOL_LOG_TIMESTAMP 	datetime 		NULL,
   WADMIN_AD_ERESPOOL_LOG_STATUS 	int 			NULL CHECK (WADMIN_AD_ERESPOOL_LOG_STATUS IN (0, 1, 2, 3)),
   WADMIN_AD_ERESPOOL_LOG_STATUS_TEXT 	nvarchar(255) 		NULL,
   WADMIN_AD_ERESPOOL_UPDATE_FREQ_EVERY int 			NOT NULL DEFAULT 0,
   WADMIN_AD_ERESPOOL_UPDATE_FREQ_UNIT 	int 			NOT NULL DEFAULT 1 CHECK (WADMIN_AD_ERESPOOL_UPDATE_FREQ_UNIT IN (0, 1, 2)),
   WADMIN_AD_ERESPOOL_START_DATE 	datetime 		NULL,
   WADMIN_AD_ERESPOOL_START_TIME 	int 			NULL,
   WADMIN_AD_ERESPOOL_UPDATE 		tinyint 		NOT NULL DEFAULT 0
)
-- ON [PRIMARY]
GO


--****************************************************************************************************
--*		MSP_WEB_OBJECTS
--****************************************************************************************************

DROP INDEX dbo.MSP_WEB_OBJECTS.I_OBJ_TYPEPROJLISTTP
GO

-- When upgrading, we should rename the old fields to preserve them instead of dropping them for STS migration purposes
EXEC sp_rename 'MSP_WEB_OBJECTS.[WOBJ_LIST_NAME]', 'WOBJ_LIST_NAME_P2002', 'COLUMN'

ALTER TABLE dbo.MSP_WEB_OBJECTS ADD
   WOBJ_RISK_REF_CNT 			int 			NOT NULL DEFAULT 0,
   WOBJ_LIST_NAME			uniqueidentifier	NULL
GO


CREATE INDEX I_OBJ_TYPEPROJLISTTP
ON dbo.MSP_WEB_OBJECTS (WOBJ_TYPE, WOBJ_PROJ_ID, WOBJ_LIST_NAME, WOBJ_TP_ID)
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_WEB_TIME_PERIODS
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_WEB_TIME_PERIODS') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_WEB_TIME_PERIODS 
GO


CREATE TABLE dbo.MSP_WEB_TIME_PERIODS 
(
   WPRD_ID 			int IDENTITY(100, 1) 		NOT NULL,
   WPRD_START_DATE 		datetime 			NOT NULL,
   WPRD_FINISH_DATE 		datetime 			NOT NULL,
   WPRD_STATE 			int 				NOT NULL DEFAULT 0,
   RESERVED_DATA1 		float 				NOT NULL DEFAULT 0
)
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_WEB_PROJECTS
--**************************************************************************************************

EXEC sp_rename 'MSP_WEB_PROJECTS.[WPROJ_STS_SUBWEB_NAME]', 'WPROJ_STS_SUBWEB_NAME_P2002', 'COLUMN'
EXEC sp_rename 'MSP_WEB_PROJECTS.[WSTS_SERVER_ID]', 'WSTS_SERVER_ID_P2002', 'COLUMN'


ALTER TABLE dbo.MSP_WEB_PROJECTS ADD
   WPROJ_ISSUE_LIST_NAME 	uniqueidentifier 	NULL,
   WPROJ_RISK_LIST_NAME 	uniqueidentifier 	NULL,
   WPROJ_ADMINPROJECT           tinyint      		NOT NULL DEFAULT 0 CHECK ((WPROJ_ADMINPROJECT >= 0) AND (WPROJ_ADMINPROJECT <= 1)),
   WPROJ_STS_SUBWEB_NAME 	nvarchar(128) 		NULL DEFAULT 'NONE',
   WSTS_SERVER_ID               int                     NOT NULL DEFAULT 0
GO


UPDATE dbo.MSP_WEB_PROJECTS
SET WPROJ_STS_SUBWEB_NAME = 'NONE'


UPDATE dbo.MSP_WEB_PROJECTS
SET WSTS_SERVER_ID = 0 


UPDATE dbo.MSP_WEB_PROJECTS 
SET WPROJ_LAST_PUB = getdate()
WHERE WPROJ_LAST_PUB IS NULL


ALTER TABLE dbo.MSP_WEB_PROJECTS ALTER COLUMN
   WPROJ_LAST_PUB datetime NOT NULL
GO


ALTER TABLE dbo.MSP_WEB_PROJECTS ADD CONSTRAINT
   DF_WEB_PROJECTS_LAST_PUB DEFAULT GetDate() FOR WPROJ_LAST_PUB WITH VALUES
GO


UPDATE dbo.MSP_WEB_PROJECTS
SET PROJ_TIMESTAMP = substring(PROJ_TIMESTAMP, 1, 28) + '0000'
WHERE PROJ_NAME <> 'Nonproject time'


IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_WEB_PROJECTS_AUI_TRG')
AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER dbo.MSP_WEB_PROJECTS_AUI_TRG
GO


CREATE TRIGGER dbo.MSP_WEB_PROJECTS_AUI_TRG
ON dbo.MSP_WEB_PROJECTS
FOR INSERT
AS 

  DECLARE @new_PROJ_ID int
  DECLARE @new_PROJ_TIMESTAMP nvarchar(32)
  DECLARE @new_WRES_ID int
  DECLARE @DupCount int

  SELECT @new_PROJ_ID = PROJ_ID FROM inserted
  SELECT @new_PROJ_TIMESTAMP = PROJ_TIMESTAMP FROM inserted
  SELECT @new_WRES_ID = WRES_ID FROM inserted

  -- if PROJ_ID is NULL then simply insert row, as we allow NULL PROJ_ID's
  IF @new_PROJ_ID IS NULL
  BEGIN
    RETURN -- Since we are inserting a NULL PROJ_ID this is ok.  Exit trigger code.
  END

  -- if PROJ_ID is NOT NULL then ensure same PROJ_ID does not already exist in the table
  IF @new_PROJ_ID IS NOT NULL
  BEGIN
    SELECT @DupCount = count(*) FROM MSP_WEB_PROJECTS WHERE PROJ_ID = @new_PROJ_ID
    IF @DupCount = 0
    BEGIN
      RETURN
    END
	  IF @DupCount > 1
    BEGIN
      
			DELETE 
			FROM MSP_WEB_PROJECTS 
			WHERE PROJ_ID = @new_PROJ_ID 
			AND PROJ_TIMESTAMP = @new_PROJ_TIMESTAMP
      AND WRES_ID = @new_WRES_ID

			--RAISERROR ('Attempted to add a duplicate PROJ_ID to MSP_WEB_PROJECTS!', 16, 1, @new_PROJ_ID, @new_PROJ_TIMESTAMP)
      RAISERROR ('Attempted to insert duplicate PROJ_ID (%d), PROJ_TIMESTAMP (%s), IN MSP_WEB_PROJECTS', 16, 1, @new_PROJ_ID, @new_PROJ_TIMESTAMP)

    END
  END

GO


--**************************************************************************************************
--*		MSP_WEB_STS_SERVERS
--**************************************************************************************************

-- Drop PK from MSP_WEB_STS_SERVERS prior to renaming
ALTER TABLE dbo.MSP_WEB_STS_SERVERS DROP CONSTRAINT PK_MSP_WEB_STS_SERVERS_WSTS_SERVER_ID

-- Rename table MSP_WEB_STS_SERVERS to MSP_WEB_STS_SERVERS_P2002
EXEC sp_rename 'MSP_WEB_STS_SERVERS', 'MSP_WEB_STS_SERVERS_P2002'

-- Create empty P11 version of MSP_WEB_STS_SERVERS
CREATE TABLE dbo.MSP_WEB_STS_SERVERS
(
	WSTS_SERVER_ID 					int IDENTITY(1,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_STS_SERVERS_WSTS_SERVER_ID PRIMARY KEY,
	WSTS_SERVER_NAME 				nvarchar(255) 		NULL DEFAULT 'NONE', 
	WSTS_SERVER_WEB_PORT 				int 			NOT NULL DEFAULT 80,
	WSTS_SERVER_ADMIN_PORT 				int 			NOT NULL DEFAULT 0,
	WSTS_WEB_PORT_IS_SSL 				tinyint 		NOT NULL DEFAULT 0,
	WSTS_ADMIN_PORT_IS_SSL 				tinyint 		NOT NULL DEFAULT 0,
	WSTS_MANAGED_PATH	  			nvarchar(60)		NULL DEFAULT 'projects',
	WSTS_EXTRANET_SERVER_NAME			nvarchar(255)		NULL,
        WSTS_ADMIN_SERVER_NAME				nvarchar(255)		NULL,
        WSTS_NETBIOS_SERVER_NAME			nvarchar(255)		NULL 
)
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_WEB_WORK_ADJUSTED
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_WEB_WORK_ADJUSTED') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_WEB_WORK_ADJUSTED 
GO


CREATE TABLE dbo.MSP_WEB_WORK_ADJUSTED 
(
   WADJUST_ID						int IDENTITY(100,1) 	NOT NULL,
   WASSN_ID 						int			NOT NULL,
   WWORK_DATE						datetime 		NOT NULL,
   WWORK_TYPE 						int 			NOT NULL,
   WWORK_VALUE						decimal(25,6) 		NOT NULL,
   WWORK_VALUE_ADJUSTED					decimal(25,6) 		NOT NULL,
   WWORK_ADJUST_DATE 					datetime		NOT NULL,
   WRES_ID 						int 			NOT NULL
)
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_WEB_TRANSACTIONS
--**************************************************************************************************

ALTER TABLE dbo.MSP_WEB_TRANSACTIONS ADD
   WTRANS_ADJUST					tinyint			NOT NULL DEFAULT 0
GO

--****************************************************************************************************
--*		MSP_WEB_OBJECT_TYPES
--****************************************************************************************************

DROP TABLE dbo.MSP_WEB_OBJECT_TYPES
GO

--****************************************************************************************************
--*		MSP_WEB_OBJECT_LINK_TYPES
--****************************************************************************************************

DROP TABLE dbo.MSP_WEB_OBJECT_LINK_TYPES
GO

--**************************************************************************************************
--*		MSP_WEB_VIEW_FIELDS
--**************************************************************************************************

CREATE INDEX I_WFIELD_NAME_OLEDB 
ON dbo.MSP_WEB_VIEW_FIELDS (WFIELD_NAME_OLEDB)
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_WEB_WORK
--**************************************************************************************************

ALTER TABLE dbo.MSP_WEB_WORK ADD
   WWORK_APPROVAL_STATUS			int			NOT NULL DEFAULT 0
GO

--**************************************************************************************************
--*		MSP_WEB_WORK_APPROVAL
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_WEB_WORK_APPROVAL') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_WEB_WORK_APPROVAL 
GO


CREATE TABLE dbo.MSP_WEB_WORK_APPROVAL 
(
   WAPPROVAL_ID						int IDENTITY(1,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_WORK_APPROVAL_WAPPROVAL_ID PRIMARY KEY NONCLUSTERED, 
   WASSN_ID 						int			NOT NULL,
   WRES_ID 						int 			NOT NULL,
   WPRD_START_DATE					datetime 		NOT NULL,
   WPRD_FINISH_DATE					datetime		NOT NULL,
   WRES_ID_APPROVER 					int 			NOT NULL DEFAULT -1,
   WAPPROVAL_STATUS					tinyint			NOT NULL DEFAULT 0,
   WAPPROVAL_NOTIFICATION_SENT     			tinyint			NOT NULL DEFAULT 0
)
-- ON [PRIMARY]
GO


CREATE CLUSTERED INDEX I_APPROVAL_WRESID_WASSNID 
ON dbo.MSP_WEB_WORK_APPROVAL (WRES_ID, WASSN_ID, WAPPROVAL_ID) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO


CREATE INDEX I_APPROVAL_WASSNID_START_FINISH 
ON dbo.MSP_WEB_WORK_APPROVAL (WASSN_ID, WPRD_START_DATE, WPRD_FINISH_DATE) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO


CREATE INDEX I_APPROVAL_WASSNID_WRESID_NOTIF_SENT_STATUS
ON dbo.MSP_WEB_WORK_APPROVAL (WASSN_ID, WRES_ID, WAPPROVAL_NOTIFICATION_SENT, WAPPROVAL_STATUS) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_WEB_SECURITY_USERDEFINED
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_WEB_SECURITY_USERDEFINED')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_WEB_SECURITY_USERDEFINED
GO


CREATE TABLE dbo.MSP_WEB_SECURITY_USERDEFINED
(
	WSEC_PERMISSION_NUMBER int NOT NULL,	
	WSEC_ITEM_TYPE int NOT NULL,
	WSEC_ITEM_ID int NOT NULL	
)
GO


CREATE CLUSTERED INDEX I_WEB_SECURITY_USERDEFINED_PERM_NUMBER
ON dbo.MSP_WEB_SECURITY_USERDEFINED (WSEC_PERMISSION_NUMBER)
WITH FILLFACTOR = 40
GO

--**************************************************************************************************
--*		MSP_WEB_CONVERSIONS
--**************************************************************************************************

ALTER TABLE dbo.MSP_WEB_CONVERSIONS DROP CONSTRAINT
  PK_MSP_WEB_CONVERSIONS_STRING_TYPE_ID_CONV_VALUE_LANG_ID
GO


ALTER TABLE dbo.MSP_WEB_CONVERSIONS ADD CONSTRAINT
  PK_MSP_WEB_CONVERSIONS PRIMARY KEY CLUSTERED (LANG_ID, CONV_VALUE, STRING_TYPE_ID)
GO

--****************************************************************************************************
--*		MSP_WEB_WORKGROUP_FIELDS_INFO
--****************************************************************************************************

DROP INDEX dbo.MSP_WEB_WORKGROUP_FIELDS_INFO.I_CFINFO_FIELDID
GO


CREATE NONCLUSTERED INDEX I_MSP_WEB_WORKGROUP_FIELDS_INFO_FIELD_ID 
ON dbo.MSP_WEB_WORKGROUP_FIELDS_INFO (FIELD_ID)
WITH FILLFACTOR = 40
GO


ALTER TABLE dbo.MSP_WEB_WORKGROUP_FIELDS_INFO DROP CONSTRAINT
  PK_MSP_WEB_WORKGROUP_FIELDS_INFO_CUSTFIELD_INFO_ID
GO


ALTER TABLE dbo.MSP_WEB_WORKGROUP_FIELDS_INFO ADD CONSTRAINT
  PK_MSP_WEB_WORKGROUP_FIELDS_INFO PRIMARY KEY CLUSTERED (CUSTFIELD_INFO_ID)
GO

--****************************************************************************************************
--*		MSP_WEB_ADMIN_CUBE
--****************************************************************************************************

ALTER TABLE dbo.MSP_WEB_ADMIN_CUBE ADD
  WADMIN_CUBE_EXTRANET_OLAP_SERVER            	nvarchar(255)          NULL,
  WADMIN_CUBE_BUILD_GUID         		uniqueidentifier       NULL
GO

--****************************************************************************************************
--*		MSP_WEB_WSS_LIST_SYNCHRONIZE_TIMES
--****************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_WEB_WSS_LIST_SYNCHRONIZE_TIMES')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_WEB_WSS_LIST_SYNCHRONIZE_TIMES
GO


CREATE TABLE dbo.MSP_WEB_WSS_LIST_SYNCHRONIZE_TIMES
(
  WPROJ_ID				int			NOT NULL,
  WOBJ_LIST_NAME			uniqueidentifier	NOT NULL,
  WPROJ_WSS_LAST_LIST_SYNC_TIME		nvarchar(50) 		NOT NULL DEFAULT (N'2000-01-01T00:00:00Z')
)
-- ON PRIMARY
GO


ALTER TABLE dbo.MSP_WEB_WSS_LIST_SYNCHRONIZE_TIMES ADD CONSTRAINT
  PK_MSP_WEB_WSS_LIST_SYNCHRONIZE_TIMES PRIMARY KEY CLUSTERED (WPROJ_ID, WOBJ_LIST_NAME)
GO




-- End of script

