-- UPGWTBLS.SQL
-- 
-- Revised: 2002-04-11
--
-- Directions:
-- 1. Update the default language in the insert statement for dbo.temp_MSP_WEB_ADMIN
--    with the appropriate locale ID.
-- 2. Execute upgdb.cmd.
--


--
-- dbo.MSP_WEB_ADMIN
--
-- bit to tinyInt conversions, new columns added
--
CREATE TABLE dbo.temp_MSP_WEB_ADMIN
(
	WADMIN_IDFAKE int IDENTITY NOT NULL CONSTRAINT PK_MSP_WEB_ADMIN_WADMIN_IDFAKE PRIMARY KEY,
	WADMIN_AUTHENTICATION_TYPE int NOT NULL DEFAULT 3,
	WADMIN_NEW_ACCOUNT_PRIVILEGE int NOT NULL DEFAULT 3,
	WADMIN_IS_DELEGATION_ALLOWED tinyint NOT NULL DEFAULT 1 CHECK ((WADMIN_IS_DELEGATION_ALLOWED >= 0) AND (WADMIN_IS_DELEGATION_ALLOWED <= 1)),
	WADMIN_AUTH_REQUIRED_FOR_PUBLISH tinyint NOT NULL DEFAULT 0 CHECK ((WADMIN_AUTH_REQUIRED_FOR_PUBLISH >= 0) AND (WADMIN_AUTH_REQUIRED_FOR_PUBLISH <= 1)),
	WADMIN_WEEK_STARTS_ON int NOT NULL DEFAULT 1,
	WADMIN_MIN_PASSWORD_LENGTH int NOT NULL DEFAULT 8,
	WADMIN_NTFY_FROM_EMAIL nvarchar(100) NOT NULL,
	WADMIN_NTFY_EMAIL_TRAILER nvarchar(255) NULL,
	WADMIN_ORG_EMAIL_ADDRESS nvarchar(100) NOT NULL,
	WADMIN_DEFAULT_LANGUAGE int NOT NULL,
	WADMIN_DEFAULT_TRACKING_METHOD int NOT NULL DEFAULT 2 CHECK ((WADMIN_DEFAULT_TRACKING_METHOD >= 0) AND (WADMIN_DEFAULT_TRACKING_METHOD <= 4)),
	WADMIN_CAN_PUBLISH_CONSOLIDATED_PROJECTS tinyint NOT NULL DEFAULT 0 CHECK ((WADMIN_CAN_PUBLISH_CONSOLIDATED_PROJECTS >= 0) AND (WADMIN_CAN_PUBLISH_CONSOLIDATED_PROJECTS <= 1)),
	WADMIN_IS_TRACKING_METHOD_LOCKED tinyint NOT NULL DEFAULT 0 CHECK ((WADMIN_IS_TRACKING_METHOD_LOCKED >= 0) AND (WADMIN_IS_TRACKING_METHOD_LOCKED <= 1)),
	WADMIN_TRANS_HISTORY_DAYS int NOT NULL DEFAULT 30,
	WADMIN_TIMESHEET_SPAN tinyint NOT NULL  DEFAULT 1,
	WADMIN_WEEKLY_TIMESHEET_NUM_WEEKS tinyint NOT NULL DEFAULT 2,
	WADMIN_MONTHLY_REPORTS_PER_MONTH tinyint NOT NULL DEFAULT 2,
	WADMIN_MONTHLY_1PRD_1ST_START tinyint NOT NULL DEFAULT 1,
	WADMIN_MONTHLY_2PRDS_1ST_START tinyint NOT NULL  DEFAULT 1,
	WADMIN_MONTHLY_2PRDS_1ST_END tinyint NOT NULL DEFAULT 15,
	WADMIN_MONTHLY_3PRDS_1ST_START tinyint NOT NULL DEFAULT 1,
	WADMIN_MONTHLY_3PRDS_1ST_END tinyint NOT NULL DEFAULT 10,
	WADMIN_MONTHLY_3PRDS_2ND_END tinyint NOT NULL DEFAULT 20,
	WADMIN_MAX_HOUR_PER_DAY decimal (25,6) NOT NULL  DEFAULT 8,
	WADMIN_LOOKAHEAD int NOT NULL DEFAULT 10,
	WADMIN_TIMEPERIOD_GRANULARITY tinyint NOT NULL DEFAULT 2,
	WADMIN_LICENSES int NOT NULL DEFAULT 10,
	WADMIN_AUTO_CREATE_SUBWEBS tinyint NOT NULL DEFAULT 1,
	WADMIN_AUTO_ADD_USER_TO_SUBWEB tinyint NOT NULL DEFAULT 1,
	WADMIN_AUTO_ADD_USER_AS_ADV_AUTHOR_TO_SUBWEB tinyint NOT NULL DEFAULT 1,
	WADMIN_CURRENT_STS_SERVER_ID int NOT NULL DEFAULT 0,
	WADMIN_PUBDOCS_STS_SERVER_ID int NOT NULL DEFAULT 0,
	WADMIN_ENABLE_ENTERPRISE tinyint NOT NULL DEFAULT 1,
	WADMIN_DISPLAY_MASTER_IN_ENTERPRISE tinyint NOT NULL DEFAULT 0,
	WADMIN_ALLOW_LOCAL_BASE_CALS_IN_ENTERPRISE tinyint NOT NULL DEFAULT 0,
	WADMIN_NPE_LAST_RUN datetime NULL,
	WADMIN_NPE_NEXT_RUN datetime NULL,
	WADMIN_NPE_SCHEDULED_TIME int NULL,
	WADMIN_PUBDOCS_STS_SUBWEB_NAME nvarchar(128) DEFAULT (N'MS_ProjectServer_PublicDocuments'),
	WADMIN_ENFORCE_CURRENCY tinyint NOT NULL DEFAULT 1 CHECK ((WADMIN_ENFORCE_CURRENCY >= 0) AND (WADMIN_ENFORCE_CURRENCY <= 1)),
	WADMIN_LAST_STS_ADMIN_SYNCH_TIME datetime NULL,
	WADMIN_SMTP_SERVER_NAME nvarchar(256) NULL,
	WADMIN_SMTP_SERVER_PORT int DEFAULT 25,
	WADMIN_INTRANET_SERVER_URL nvarchar(255) NOT NULL DEFAULT (N''),
	WADMIN_EXTRANET_SERVER_URL nvarchar(255) NOT NULL DEFAULT (N''),
	WADMIN_ONLY_PRO_PUBLISH tinyint NOT NULL DEFAULT 0
)
GO

Insert Into dbo.temp_MSP_WEB_ADMIN
	(WADMIN_AUTHENTICATION_TYPE,
	WADMIN_NEW_ACCOUNT_PRIVILEGE,
	WADMIN_IS_DELEGATION_ALLOWED,
	WADMIN_WEEK_STARTS_ON,
	WADMIN_MIN_PASSWORD_LENGTH,
	WADMIN_DEFAULT_LANGUAGE,
	WADMIN_NTFY_FROM_EMAIL,
	WADMIN_ORG_EMAIL_ADDRESS,
	WADMIN_ENABLE_ENTERPRISE,
	WADMIN_ENFORCE_CURRENCY)
select	WADMIN_AUTHENTICATION_TYPE,
	WADMIN_NEW_ACCOUNT_PRIVILEGE,
	cast(WADMIN_IS_DELEGATION_ALLOWED as tinyint),
	WADMIN_WEEK_STARTS_ON,
	WADMIN_MIN_PASSWORD_LENGTH,
	1033,						-- update this value with the appropriate locale ID
	'',
	'',
	0,
	0
from	dbo.MSP_WEB_ADMIN
GO

DROP TABLE dbo.MSP_WEB_ADMIN
GO

execute sp_rename temp_MSP_WEB_ADMIN, MSP_WEB_ADMIN
GO


--
-- dbo.MSP_WEB_ADMIN_CUBE
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_ADMIN_CUBE
(
	WADMIN_CUBE_ID int IDENTITY (1, 1) NOT NULL CONSTRAINT PK_MSP_WEB_ADMIN_CUBE_WADMIN_CUBE_ID PRIMARY KEY CLUSTERED,
	WADMIN_CUBE_BUILD tinyint NOT NULL CHECK ((WADMIN_CUBE_BUILD >= 0) AND (WADMIN_CUBE_BUILD <= 1)),
	WADMIN_CUBE_OLAP_SERVER nvarchar(100) NOT NULL,
	WADMIN_CUBE_NAME nvarchar(100) NOT NULL,
	WADMIN_CUBE_DESCRIPTION nvarchar(255) NULL,
	WADMIN_CUBE_DATE_RANGE int NOT NULL,
	WADMIN_CUBE_DATE_RANGE_NEXT_NUM int NOT NULL,
	WADMIN_CUBE_DATE_RANGE_NEXT_UNIT int NOT NULL,
	WADMIN_CUBE_DATE_RANGE_PAST_NUM int NOT NULL,
	WADMIN_CUBE_DATE_RANGE_PAST_UNIT int NOT NULL,
	WADMIN_CUBE_DATE_RANGE_FROM datetime NOT NULL,
	WADMIN_CUBE_DATE_RANGE_TO datetime NOT NULL,
	WADMIN_CUBE_LOG_STATUS_TIMESTAMP datetime NULL,
	WADMIN_CUBE_LOG_STATUS int NULL,
	WADMIN_CUBE_LOG_STATUS_TEXT nvarchar(255) NULL,
	WADMIN_RES_DATE_RANGE int NOT NULL,
	WADMIN_RES_DATE_RANGE_NEXT_NUM int NOT NULL,
	WADMIN_RES_DATE_RANGE_NEXT_UNIT int NOT NULL,
	WADMIN_RES_DATE_RANGE_PAST_NUM int NOT NULL,
	WADMIN_RES_DATE_RANGE_PAST_UNIT int NOT NULL,
	WADMIN_RES_DATE_RANGE_FROM datetime NOT NULL,
	WADMIN_RES_DATE_RANGE_TO datetime NOT NULL,
	WADMIN_AUTO_LAST_RUN datetime NOT NULL,
	WADMIN_UPDATE_RECUR tinyint NOT NULL CHECK ((WADMIN_UPDATE_RECUR >= 0) AND (WADMIN_UPDATE_RECUR <= 1)),
	WADMIN_UPDATE_FREQ_EVERY int NOT NULL,
	WADMIN_UPDATE_FREQ_UNIT int NOT NULL,
	WADMIN_UPDATE_FREQ_START_DATE datetime NOT NULL,
	WADMIN_UPDATE_FREQ_START_TIME int NOT NULL,
	WADMIN_UPDATE_RES tinyint NOT NULL CHECK ((WADMIN_UPDATE_RES >= 0) AND (WADMIN_UPDATE_RES <= 1)),
	WADMIN_UPDATE_CUBE tinyint NOT NULL CHECK ((WADMIN_UPDATE_CUBE >= 0) AND (WADMIN_UPDATE_CUBE <= 1))
)
GO



--
-- dbo.MSP_WEB_ADMIN_LINKS
--
-- column WLINKS_TITLE dropped
--
CREATE TABLE dbo.temp_MSP_WEB_ADMIN_LINKS
(
	WLINKS_ID int IDENTITY (100, 1) NOT NULL CONSTRAINT PK_MSP_WEB_ADMIN_LINKS_WLINKS_ID PRIMARY KEY NONCLUSTERED,
	WLINKS_HREF nvarchar(500) NOT NULL,
	WLINKS_NAME nvarchar(255) NULL,
	WLINKS_HEIGHT nvarchar(255) NULL,
	WLINKS_TYPE int NOT NULL DEFAULT 0
)
GO

CREATE UNIQUE INDEX PK_WEB_ADMIN_LINKS
ON dbo.temp_MSP_WEB_ADMIN_LINKS (WLINKS_ID)
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_ADMIN_LINKS ON 
GO

insert into dbo.temp_MSP_WEB_ADMIN_LINKS
	(WLINKS_ID,
	WLINKS_HREF,
	WLINKS_NAME,
	WLINKS_HEIGHT,
	WLINKS_TYPE)
select	WLINKS_ID,
	WLINKS_HREF,
	WLINKS_NAME,
	WLINKS_HEIGHT,
	WLINKS_TYPE
from    dbo.MSP_WEB_ADMIN_LINKS

SET IDENTITY_INSERT dbo.temp_MSP_WEB_ADMIN_LINKS OFF
GO

DROP TABLE dbo.MSP_WEB_ADMIN_LINKS
GO

execute sp_rename temp_MSP_WEB_ADMIN_LINKS, MSP_WEB_ADMIN_LINKS
GO



--
-- dbo.MSP_WEB_ASSIGNMENTS
--
-- bit to tinyInt conversions, and new columns added
--
CREATE TABLE dbo.temp_MSP_WEB_ASSIGNMENTS
(
	WASSN_ID int IDENTITY (100, 1) NOT NULL CONSTRAINT PK_MSP_WEB_ASSIGNMENTS_WASSN_ID PRIMARY KEY NONCLUSTERED,
	WRES_ID int NULL,
	WASSN_PARENT_ID int NULL,
	WPROJ_ID int NULL,
	WRES_ID_MGR int NULL,
	ASSN_UID int NULL,
	TASK_UID int NULL,
	TASK_ID int NULL,
	TASK_UID_SUMMARY int NULL,
	TASK_NAME nvarchar(255) NULL,
	ASSN_START_DATE datetime NULL,
	ASSN_FINISH_DATE datetime NULL,
	ASSN_WORK decimal (25,6) NULL DEFAULT 0,
	ASSN_ACT_WORK decimal (25, 6) NULL DEFAULT 0,
	ASSN_REM_WORK decimal (25,6) NULL DEFAULT 0,
	WASSN_LAST_WORK decimal (25,6) NULL DEFAULT 0,
	WASSN_COMMENTS ntext NULL,
	WASSN_HISTORY_NOTES ntext NULL,
	WASSN_NOTE_STATUS int NOT NULL DEFAULT 0,
	TASK_IS_MILESTONE tinyint NOT NULL DEFAULT 0 CHECK ((TASK_IS_MILESTONE >= 0) AND (TASK_IS_MILESTONE <= 1)),
	TASK_IS_SUMMARY tinyint NOT NULL DEFAULT 0 CHECK ((TASK_IS_SUMMARY >= 0) AND (TASK_IS_SUMMARY <= 1)),
	WASSN_IS_CONFIRMED tinyint NOT NULL DEFAULT 0 CHECK ((WASSN_IS_CONFIRMED >= 0) AND (WASSN_IS_CONFIRMED <= 1)),
	WASSN_MGR_UPDATED tinyint NOT NULL DEFAULT 0 CHECK ((WASSN_MGR_UPDATED >= 0) AND (WASSN_MGR_UPDATED <= 1)),
	WASSN_CREATED_BY_RES int NOT NULL DEFAULT 0,
	WASSN_REMOVED_BY_RESOURCE int NOT NULL DEFAULT 0,
	WASSN_CURRENT_TRACKING_MODE int NULL DEFAULT 0,
	WASSN_UPDATE_TRACKING_MODE int NULL DEFAULT 0,
	WASSN_SEND_UPDATE_NEEDED tinyint NOT NULL DEFAULT 0 CHECK ((WASSN_SEND_UPDATE_NEEDED >= 0) AND (WASSN_SEND_UPDATE_NEEDED <= 1)),
	WASSN_DELETED_IN_PROJ tinyint NOT NULL DEFAULT 0 CHECK ((WASSN_DELETED_IN_PROJ >= 0) AND (WASSN_DELETED_IN_PROJ <= 1)),
	WASSN_RESOURCE_UPDATE tinyint NOT NULL DEFAULT 0 CHECK ((WASSN_RESOURCE_UPDATE >= 0) AND (WASSN_RESOURCE_UPDATE <= 1)),
	WASSN_REQUEST_UPDATE tinyint NOT NULL DEFAULT 0 CHECK ((WASSN_REQUEST_UPDATE >= 0) AND (WASSN_REQUEST_UPDATE <= 1)),
	WASSN_UPDATES_ACCEPTED tinyint NOT NULL DEFAULT 0 CHECK ((WASSN_UPDATES_ACCEPTED >= 0) AND (WASSN_UPDATES_ACCEPTED <= 1)),
	WASSN_DELEG_ACCEPTED tinyint NOT NULL DEFAULT 0 CHECK ((WASSN_DELEG_ACCEPTED >= 0) AND (WASSN_DELEG_ACCEPTED <= 1)),
	WASSN_DELEG_APPROVED tinyint NOT NULL DEFAULT 0 CHECK ((WASSN_DELEG_APPROVED >= 0) AND (WASSN_DELEG_APPROVED <= 1)),
	WASSN_ACTUALS_PENDING tinyint NOT NULL DEFAULT 0 CHECK ((WASSN_ACTUALS_PENDING >= 0) AND (WASSN_ACTUALS_PENDING <= 1)),
	WASSN_DELEG_PENDING tinyint NOT NULL DEFAULT 0 CHECK ((WASSN_DELEG_PENDING >= 0) AND (WASSN_DELEG_PENDING <= 1)),
	WASSN_IS_DELEGATED tinyint NOT NULL DEFAULT 0 CHECK ((WASSN_IS_DELEGATED >= 0) AND (WASSN_IS_DELEGATED <= 1)),
	WASSN_IS_NEW_ASSN tinyint NOT NULL DEFAULT 0 CHECK ((WASSN_IS_NEW_ASSN >= 0) AND (WASSN_IS_NEW_ASSN <= 1)),
	WASSN_UPDATE_STATUS int NOT NULL DEFAULT 0,
	WASSN_LAST_DELEG_ID int NULL,
	WASSN_PCT_COMP int NULL,
	WASSN_SEND_UPDATE_DATE datetime NULL,
	WASSN_SUMMARY_PROGRESS datetime NULL,
	WRES_ID_TEAM_LEAD int NULL,
	WNWRK_ID int NULL,
	WNWRK_ENTRY_ID nvarchar(300) NULL,
	RESERVED_DATA1 int NULL,
	RESERVED_DATA2 int NULL,
	RESERVED_DATA3 int NULL
)
GO

CREATE INDEX I_ASSIGN_WRESID_TEAM_LEAD
ON dbo.temp_MSP_WEB_ASSIGNMENTS(WRES_ID_TEAM_LEAD)
GO

CREATE INDEX I_ASSIGN_PROJSRCID_ASSNPROJUID_WRESID
ON dbo.temp_MSP_WEB_ASSIGNMENTS (WPROJ_ID, ASSN_UID, WRES_ID)
WITH FILLFACTOR = 40
GO

CREATE INDEX I_ASSIGN_PARENTTASKPROJUID
ON dbo.temp_MSP_WEB_ASSIGNMENTS (TASK_UID_SUMMARY)
GO

CREATE INDEX I_ASSIGN_TASKPROJUID
ON dbo.temp_MSP_WEB_ASSIGNMENTS (TASK_UID)
GO

CREATE CLUSTERED INDEX CI_ASSIGN_RESID
ON dbo.temp_MSP_WEB_ASSIGNMENTS (WRES_ID)
GO

CREATE INDEX I_ASSIGN_MGRRESID
ON dbo.temp_MSP_WEB_ASSIGNMENTS (WRES_ID_MGR)
GO

CREATE INDEX I_ASSIGN_LASTDELEGID
ON dbo.temp_MSP_WEB_ASSIGNMENTS (WASSN_LAST_DELEG_ID)
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_ASSIGNMENTS ON 
GO

Insert Into dbo.temp_MSP_WEB_ASSIGNMENTS
	(WASSN_ID,
	WRES_ID,
	WASSN_PARENT_ID,
	WPROJ_ID,
	WRES_ID_MGR,
	ASSN_UID,
	TASK_UID,
	TASK_ID,
	TASK_UID_SUMMARY,
	TASK_NAME,
	ASSN_START_DATE,
	ASSN_FINISH_DATE,
	ASSN_WORK,
	ASSN_REM_WORK,
	WASSN_LAST_WORK,
	WASSN_COMMENTS,
	TASK_IS_MILESTONE,
	TASK_IS_SUMMARY,
	WASSN_IS_CONFIRMED,
	WASSN_MGR_UPDATED,
	WASSN_CREATED_BY_RES,
	WASSN_REMOVED_BY_RESOURCE,
	WASSN_CURRENT_TRACKING_MODE,
	WASSN_UPDATE_TRACKING_MODE,
	WASSN_SEND_UPDATE_NEEDED,
	WASSN_DELETED_IN_PROJ,
	WASSN_RESOURCE_UPDATE,
	WASSN_REQUEST_UPDATE,
	WASSN_UPDATES_ACCEPTED,
	WASSN_DELEG_ACCEPTED,
	WASSN_DELEG_APPROVED,
	WASSN_ACTUALS_PENDING,
	WASSN_DELEG_PENDING,
	WASSN_IS_DELEGATED,
	WASSN_IS_NEW_ASSN,
	WASSN_LAST_DELEG_ID,
	WASSN_PCT_COMP,
	WRES_ID_TEAM_LEAD,
	WNWRK_ID,
	WNWRK_ENTRY_ID,
	RESERVED_DATA1,
	RESERVED_DATA2,
	RESERVED_DATA3)
Select  WASSN_ID,
	WRES_ID,
	WASSN_PARENT_ID,
	WPROJ_ID,
	WRES_ID_MGR,
	ASSN_UID,
	TASK_UID,
	TASK_ID,
	TASK_UID_SUMMARY,
	TASK_NAME,
	ASSN_START_DATE,
	ASSN_FINISH_DATE,
	ASSN_WORK,
	ASSN_REM_WORK,
	WASSN_LAST_WORK,
	Cast(WASSN_COMMENTS As nText),
	Cast(TASK_IS_MILESTONE As tinyInt),
	Cast(TASK_IS_SUMMARY As tinyInt),
	Cast(WASSN_IS_CONFIRMED As tinyInt),
	Cast(WASSN_MGR_UPDATED As tinyInt),
	WASSN_CREATED_BY_RES,
	WASSN_REMOVED_BY_RESOURCE,
	WASSN_CURRENT_TRACKING_MODE,
	WASSN_UPDATE_TRACKING_MODE,
	Cast(WASSN_SEND_UPDATE_NEEDED As tinyInt),
	Cast(WASSN_DELETED_IN_PROJ As tinyInt),
	Cast(WASSN_RESOURCE_UPDATE As tinyInt),
	Cast(WASSN_REQUEST_UPDATE As tinyInt),
	Cast(WASSN_UPDATES_ACCEPTED As tinyInt),
	Cast(WASSN_DELEG_ACCEPTED As tinyInt),
	Cast(WASSN_DELEG_APPROVED As tinyInt),
	Cast(WASSN_ACTUALS_PENDING As tinyInt),
	Cast(WASSN_DELEG_PENDING As tinyInt),
	Cast(WASSN_IS_DELEGATED As tinyInt),
	Cast(WASSN_IS_NEW_ASSN As tinyInt),
	WASSN_LAST_DELEG_ID,
	WASSN_PCT_COMP,
	WRES_ID_TEAM_LEAD,
	WNWRK_ID,
	WNWRK_ENTRY_ID,
	RESERVED_DATA1,
	RESERVED_DATA2,
	RESERVED_DATA3
FROM    dbo.MSP_WEB_ASSIGNMENTS
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_ASSIGNMENTS OFF
GO

DROP TABLE dbo.MSP_WEB_ASSIGNMENTS
GO

execute sp_rename temp_MSP_WEB_ASSIGNMENTS, MSP_WEB_ASSIGNMENTS
GO


--
-- dbo.MSP_WEB_CALENDAR_TRANSACTIONS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_CALENDAR_TRANSACTIONS
(
	WCTRANS_ID int IDENTITY (1, 1) NOT NULL CONSTRAINT PK_MSP_WEB_CALENDAR_TRANSACTIONS_WCTRANS_ID PRIMARY KEY CLUSTERED,
	WNONWORK_ID int NOT NULL,
	WCTRANS_DATE datetime NULL,
	WCTRANS_ACTION int NOT NULL DEFAULT 0,
	WRES_ID_CTRANS_RECEIVER int NULL,
	WRES_ID_CTRANS_SENDER int NULL
)
GO

CREATE INDEX IX_MSP_WEB_CALENDAR_TRANSACTIONS_WNONWORK_ID 
ON dbo.MSP_WEB_CALENDAR_TRANSACTIONS(WNONWORK_ID)
GO

CREATE INDEX IX_MSP_WEB_CALENDAR_TRANSACTIONS_WRES_ID_CTRANS_RECEIVER 
ON dbo.MSP_WEB_CALENDAR_TRANSACTIONS(WRES_ID_CTRANS_RECEIVER)
GO



--
-- dbo.MSP_WEB_CONVERSIONS
--
-- nvarchar 200 to 500 conversion
--
CREATE TABLE dbo.temp_MSP_WEB_CONVERSIONS
(
	STRING_TYPE_ID int NOT NULL,
	CONV_VALUE int NOT NULL,
	LANG_ID int NOT NULL,
	CONV_STRING nvarchar(1000) NULL,
	CONSTRAINT PK_MSP_WEB_CONVERSIONS_STRING_TYPE_ID_CONV_VALUE_LANG_ID
	PRIMARY KEY NONCLUSTERED (STRING_TYPE_ID, CONV_VALUE, LANG_ID)
)
GO

Insert Into dbo.temp_MSP_WEB_CONVERSIONS
	(STRING_TYPE_ID,
	CONV_VALUE,
	LANG_ID,
	CONV_STRING)
Select  STRING_TYPE_ID,
	CONV_VALUE,
	LANG_ID,
	CONV_STRING
FROM	dbo.MSP_WEB_CONVERSIONS
GO

DROP TABLE dbo.MSP_WEB_CONVERSIONS
GO

execute sp_rename temp_MSP_WEB_CONVERSIONS, MSP_WEB_CONVERSIONS
GO



--
-- dbo.MSP_WEB_DELEGATION_ASSIGNMENTS
--
-- bit to tinyInt conversions
--
CREATE TABLE dbo.temp_MSP_WEB_DELEGATION_ASSIGNMENTS
(
	WDELEG_ID int NOT NULL,
	WASSN_ID int NOT NULL,
	WDELEG_APPROVE tinyint NOT NULL DEFAULT 1 CHECK ((WDELEG_APPROVE >= 0) AND (WDELEG_APPROVE <= 1)),
	WDELEG_ACCEPT tinyint NOT NULL DEFAULT 1 CHECK ((WDELEG_ACCEPT >= 0) AND (WDELEG_ACCEPT <= 1)),
	WDELEG_KEEP_COPY tinyint NOT NULL DEFAULT 0 CHECK ((WDELEG_KEEP_COPY >= 0) AND (WDELEG_KEEP_COPY <= 1)),
	CONSTRAINT PK_MSP_WEB_DELEGATION_ASSIGNMENTS_WDELEG_ID_WASSN_ID 
	PRIMARY KEY CLUSTERED (WDELEG_ID, WASSN_ID) 
)
GO

CREATE INDEX I_DELEGASSIGN_ASSNID
ON dbo.temp_MSP_WEB_DELEGATION_ASSIGNMENTS (WASSN_ID)
GO

Insert Into dbo.temp_MSP_WEB_DELEGATION_ASSIGNMENTS
	(WDELEG_ID,
	WASSN_ID,
	WDELEG_APPROVE,
	WDELEG_ACCEPT,
	WDELEG_KEEP_COPY)
Select  WDELEG_ID,
	WASSN_ID,
	CAST(WDELEG_APPROVE As tinyInt),
	CAST(WDELEG_ACCEPT As tinyInt),
	CAST(WDELEG_KEEP_COPY As tinyInt)
FROM	dbo.MSP_WEB_DELEGATION_ASSIGNMENTS

GO

DROP TABLE dbo.MSP_WEB_DELEGATION_ASSIGNMENTS

GO

execute sp_rename temp_MSP_WEB_DELEGATION_ASSIGNMENTS, MSP_WEB_DELEGATION_ASSIGNMENTS
GO



--
-- dbo.MSP_WEB_DELEGATIONS
--
-- bit to tinyInt conversions
--
CREATE TABLE dbo.temp_MSP_WEB_DELEGATIONS
(
	WDELEG_ID int IDENTITY (100, 1) NOT NULL CONSTRAINT PK_MSP_WEB_DELEGATIONS_WDELEG_ID PRIMARY KEY CLUSTERED,
	WRES_ID_DELEGATOR int NULL,
	WRES_ID_DELEGATEE int NULL,
	WDELEG_KEEP_COPY tinyint NOT NULL DEFAULT 0 CHECK ((WDELEG_KEEP_COPY >= 0) AND (WDELEG_KEEP_COPY <= 1)),
	WDELEG_DATE datetime NULL,
	WDELEG_REJECT tinyint NOT NULL DEFAULT 0 CHECK ((WDELEG_REJECT >= 0) AND (WDELEG_REJECT <= 1))	
)
GO

CREATE INDEX I_DELEG_WRESIDDELEGATOR
ON dbo.temp_MSP_WEB_DELEGATIONS (WRES_ID_DELEGATOR)
GO

CREATE INDEX I_DELEG_WRESIDDELEGATEE
ON dbo.temp_MSP_WEB_DELEGATIONS (WRES_ID_DELEGATEE)
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_DELEGATIONS ON
GO

Insert Into dbo.temp_MSP_WEB_DELEGATIONS
	(WDELEG_ID,
	WRES_ID_DELEGATOR,
	WRES_ID_DELEGATEE,
	WDELEG_KEEP_COPY,
	WDELEG_DATE,
	WDELEG_REJECT)
Select  WDELEG_ID,
	WRES_ID_DELEGATOR,
	WRES_ID_DELEGATEE,
	CAST(WDELEG_KEEP_COPY As tinyInt),
	WDELEG_DATE,
	CAST(WDELEG_REJECT As tinyInt)
FROM	dbo.MSP_WEB_DELEGATIONS
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_DELEGATIONS OFF
GO

DROP TABLE dbo.MSP_WEB_DELEGATIONS
GO

execute sp_rename temp_MSP_WEB_DELEGATIONS, MSP_WEB_DELEGATIONS
GO



--
-- dbo.MSP_WEB_GANTT_SCHEMES
--
-- no changes
--



--
-- dbo.MSP_WEB_GANTT_SETTINGS
--
-- bit to tinyInt conversion
--
CREATE TABLE dbo.temp_MSP_WEB_GANTT_SETTINGS
(
	WGANTT_SCHEME_ID int NOT NULL,
	WGANTT_STYLE_ID int NOT NULL,
	WGANTT_SHOW tinyint NOT NULL DEFAULT 0 CHECK ((WGANTT_SHOW >= 0) AND (WGANTT_SHOW <= 1)),
	WGANTT_BAR_TYPE int NULL,
	WGANTT_BAR_PATTERN int NULL,
	WGANTT_BAR_COLOR int NULL,
	WGANTT_START_SHAPE int NULL,
	WGANTT_START_COLOR int NULL,
	WGANTT_END_SHAPE int NULL,
	WGANTT_END_COLOR int NULL,
	CONSTRAINT PK_MSP_WEB_GANTT_SETTINGS_WGANTT_SCHEME_ID_WGANTT_STYLE_ID 
	PRIMARY KEY CLUSTERED (WGANTT_SCHEME_ID, WGANTT_STYLE_ID)
)
GO

Insert Into dbo.temp_MSP_WEB_GANTT_SETTINGS
	(WGANTT_SCHEME_ID,
	WGANTT_STYLE_ID,
	WGANTT_SHOW,
	WGANTT_BAR_TYPE,
	WGANTT_BAR_PATTERN,
	WGANTT_BAR_COLOR,
	WGANTT_START_SHAPE,
	WGANTT_START_COLOR,
	WGANTT_END_SHAPE,
	WGANTT_END_COLOR)
Select  WGANTT_SCHEME_ID,
	WGANTT_STYLE_ID,
	CAST(WGANTT_SHOW As tinyInt),
	WGANTT_BAR_TYPE,
	WGANTT_BAR_PATTERN,
	WGANTT_BAR_COLOR,
	WGANTT_START_SHAPE,
	WGANTT_START_COLOR,
	WGANTT_END_SHAPE,
	WGANTT_END_COLOR
FROM	dbo.MSP_WEB_GANTT_SETTINGS

GO

DROP TABLE dbo.MSP_WEB_GANTT_SETTINGS

GO

execute sp_rename temp_MSP_WEB_GANTT_SETTINGS, MSP_WEB_GANTT_SETTINGS
GO



--
-- dbo.MSP_WEB_GANTT_STYLES
--
-- no changes
--



--
-- dbo.MSP_WEB_GROUP_SCHEMES
--
-- new
--
CREATE TABLE dbo.MSP_WEB_GROUP_SCHEMES
(
	WGROUP_SCHEME_ID int NOT NULL CONSTRAINT PK_MSP_WEB_GROUP_SCHEMES_WGROUP_SCHEME_ID PRIMARY KEY CLUSTERED,
	WGROUP_SCHEME_NAME nvarchar(50) NULL
)
GO



--
-- dbo.MSP_WEB_GROUP_SETTINGS
--
-- new
--
CREATE TABLE dbo.MSP_WEB_GROUP_SETTINGS
(
	WGROUP_SETTING_ID int IDENTITY (1, 1) NOT NULL CONSTRAINT PK_MSP_WEB_GROUP_SETTINGS_WGROUP_SETTING_ID PRIMARY KEY NONCLUSTERED,
	WGROUP_SCHEME_ID int NOT NULL,
	WGROUP_STYLE_ID int NULL,
	WGROUP_ROW_COLOR int NULL,
	WGROUP_ROW_PATTERN int NULL,
	WGROUP_TEXT_COLOR int NULL,
	WGROUP_FONT_STYLE int NULL
)
GO

CREATE UNIQUE CLUSTERED INDEX UQ_MSP_WEB_GROUP_SETTINGS ON dbo.MSP_WEB_GROUP_SETTINGS(WGROUP_SCHEME_ID, WGROUP_STYLE_ID)
GO



--
-- dbo.MSP_WEB_GROUP_STYLES
--
-- new
--
CREATE TABLE dbo.MSP_WEB_GROUP_STYLES
(
	WGROUP_STYLE_ID int NOT NULL CONSTRAINT PK_MSP_WEB_GROUP_STYLES_WGROUP_STYLE_ID PRIMARY KEY CLUSTERED,
	WGROUP_STYLE_CONV_VALUE int NULL
)
GO



--
-- dbo.MSP_WEB_INSTALLED_LANGUAGES
--
-- new
--
CREATE TABLE dbo.MSP_WEB_INSTALLED_LANGUAGES
(
	WLANG_LCID int NOT NULL CONSTRAINT PK_MSP_WEB_INSTALLED_LANGUAGES_WLANG_LCID PRIMARY KEY CLUSTERED,
	CONV_VALUE int NOT NULL
)
GO



--
-- dbo.MSP_WEB_MESSAGES
--
-- bit to tinyInt conversions
--
CREATE TABLE dbo.temp_MSP_WEB_MESSAGES
(
	WMSG_ID int IDENTITY (100, 1) NOT NULL CONSTRAINT PK_MSP_WEB_MESSAGES_WMSG_ID PRIMARY KEY NONCLUSTERED,
	WMSG_PROJ_TYPE int NULL,
	WRES_ID_SENDER int NULL,
	WRES_ID_RECEIVER int NULL,
	WMSG_SUBJECT nvarchar(255) NULL,
	WMSG_BODY nvarchar(3000) NULL,
	WMSG_TIME datetime NULL,
	WPROJ_ID int NOT NULL,
	WMSG_ACTUAL_TYPE int NULL,
	WMSG_PERIOD_START datetime NULL,
	WMSG_PERIOD_FINISH datetime NULL,
	WMSG_PERIOD_BROKEN_BY int NULL DEFAULT -1,
	WMSG_PERIOD_WEEK_STARTS_ON int NULL DEFAULT -1,
	WMSG_WAS_READ tinyint NOT NULL DEFAULT 0 CHECK ((WMSG_WAS_READ >= 0) AND (WMSG_WAS_READ <= 1)),
	WMSG_PROCESS_ST int NOT NULL DEFAULT 0,
	WMSG_RES_CAN_DECLINE tinyint NOT NULL DEFAULT 1 CHECK ((WMSG_RES_CAN_DECLINE >= 0) AND (WMSG_RES_CAN_DECLINE <= 1))
)
GO

CREATE INDEX I_MESSAGES_PROJSRCID 
ON dbo.temp_MSP_WEB_MESSAGES (WPROJ_ID)
GO

CREATE INDEX I_MESSAGES_SENDERID
ON dbo.temp_MSP_WEB_MESSAGES (WRES_ID_SENDER)
GO

CREATE INDEX I_MESSAGES_WMSGRECIPIENTID 
ON dbo.temp_MSP_WEB_MESSAGES (WRES_ID_RECEIVER)
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_MESSAGES ON

Insert Into dbo.temp_MSP_WEB_MESSAGES
	(WMSG_ID,
	WMSG_PROJ_TYPE,
	WRES_ID_SENDER,
	WRES_ID_RECEIVER,
	WMSG_SUBJECT,
	WMSG_BODY,
	WMSG_TIME,
	WPROJ_ID,
	WMSG_ACTUAL_TYPE,
	WMSG_PERIOD_START,
	WMSG_PERIOD_FINISH,
	WMSG_PERIOD_BROKEN_BY,
	WMSG_PERIOD_WEEK_STARTS_ON,
	WMSG_WAS_READ,
	WMSG_PROCESS_ST,
	WMSG_RES_CAN_DECLINE)
Select  WMSG_ID,
	WMSG_PROJ_TYPE,
	WRES_ID_SENDER,
	WRES_ID_RECEIVER,
	WMSG_SUBJECT,
	WMSG_BODY,
	WMSG_TIME,
	WPROJ_ID,
	WMSG_ACTUAL_TYPE,
	WMSG_PERIOD_START,
	WMSG_PERIOD_FINISH,
	WMSG_PERIOD_BROKEN_BY,
	WMSG_PERIOD_WEEK_STARTS_ON,
	CAST(WMSG_WAS_READ As tinyInt),
	WMSG_PROCESS_ST,
	CAST(WMSG_RES_CAN_DECLINE As tinyInt)
FROM	dbo.MSP_WEB_MESSAGES
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_MESSAGES OFF
GO

DROP TABLE dbo.MSP_WEB_MESSAGES
GO

execute sp_rename temp_MSP_WEB_MESSAGES, MSP_WEB_MESSAGES
GO



--
-- dbo.MSP_WEB_MESSAGES_ASSIGNMENTS
--
-- bit to tinyInt conversion
--
CREATE TABLE dbo.temp_MSP_WEB_MESSAGES_ASSIGNMENTS
(
	WMSG_ID int NOT NULL,		
	WASSN_ID int NOT NULL,		
	WASSN_IS_PROCESSED tinyint NOT NULL DEFAULT 0 CHECK ((WASSN_IS_PROCESSED >= 0) AND (WASSN_IS_PROCESSED <= 1)),
	CONSTRAINT PK_MSP_WEB_MESSAGES_ASSIGNMENTS_WMSG_ID_WASSN_ID PRIMARY KEY CLUSTERED (WMSG_ID, WASSN_ID) 
)
GO

Insert Into dbo.temp_MSP_WEB_MESSAGES_ASSIGNMENTS
	(WMSG_ID,
	WASSN_ID,
	WASSN_IS_PROCESSED)
Select  WMSG_ID,
	WASSN_ID,
	CAST(WASSN_IS_PROCESSED As tinyInt)

FROM	dbo.MSP_WEB_MESSAGES_ASSIGNMENTS
GO

DROP TABLE dbo.MSP_WEB_MESSAGES_ASSIGNMENTS
GO

execute sp_rename temp_MSP_WEB_MESSAGES_ASSIGNMENTS, MSP_WEB_MESSAGES_ASSIGNMENTS
GO



--
-- dbo.MSP_WEB_MESSAGES_DELEGATIONS
--
-- no changes
--



--
-- dbo.MSP_WEB_MESSAGES_NONWORKING
--
--identity column and constraint added
--
CREATE TABLE dbo.temp_MSP_WEB_MESSAGES_NONWORKING
(
	WMSGNONWORK_ID int IDENTITY (1, 1) NOT NULL CONSTRAINT PK_MSP_WEB_MESSAGES_NONWORKING_WMSGNONWORK_ID PRIMARY KEY CLUSTERED,
	WMSG_ID int NOT NULL,
	WNONWORK_ID int NOT NULL,
	CONSTRAINT UQ_MSP_WEB_MESSAGES_NONWORKING_WMSG_ID_WNONWORK_ID UNIQUE (WMSG_ID, WNONWORK_ID)
)
GO

--SET IDENTITY_INSERT dbo.temp_MSP_WEB_MESSAGES_NONWORKING ON
--GO

Insert Into dbo.temp_MSP_WEB_MESSAGES_NONWORKING
	(WMSG_ID,
	WNONWORK_ID)
Select  WMSG_ID,
	WNONWORK_ID
FROM	dbo.MSP_WEB_MESSAGES_NONWORKING
GO

--SET IDENTITY_INSERT dbo.temp_MSP_WEB_MESSAGES_NONWORKING OFF
--GO

DROP TABLE dbo.MSP_WEB_MESSAGES_NONWORKING
GO

execute sp_rename temp_MSP_WEB_MESSAGES_NONWORKING, MSP_WEB_MESSAGES_NONWORKING
GO



--
-- dbo.MSP_WEB_MGR_RULES
--
-- bit to tinyInt conversions, column WRULE_DURATION_UNIT dropped
--
CREATE TABLE dbo.temp_MSP_WEB_MGR_RULES
(
	WRULE_ID int IDENTITY (100, 1) NOT NULL CONSTRAINT PK_MSP_WEB_MGR_RULES_WRULE_ID PRIMARY KEY NONCLUSTERED,
	WRULE_NAME nvarchar(255) NULL,
	WRES_ID_MGR int NULL,
	WRULE_IS_ENABLED tinyint NOT NULL DEFAULT 1 CHECK ((WRULE_IS_ENABLED >= 0) AND (WRULE_IS_ENABLED <= 1)),
	WRULE_TYPE int NULL DEFAULT (-1),
	WRULE_CONDITION_TYPE int NULL DEFAULT 0,
	WRULE_FIELD1_ID int NULL,
	WRULE_FIELD2_ID int NULL,
	WRULE_OPERATOR int NULL,
	WRULE_VALTYPE int NULL,
	WRULE_INT_VAL int NULL,
	WRULE_DATE_VAL datetime NULL,
	WRULE_DECIMAL_VAL decimal (25, 6) NULL,
	WRULE_VARCHAR_VAL nvarchar(255) NULL,
	WRULE_IS_EXCL_PROJID tinyint NULL DEFAULT 0 CHECK ((WRULE_IS_EXCL_PROJID >= 0) AND (WRULE_IS_EXCL_PROJID <= 1)),
	WRULE_IS_EXCL_RES1ID tinyint NULL DEFAULT 0 CHECK ((WRULE_IS_EXCL_RES1ID >= 0) AND (WRULE_IS_EXCL_RES1ID <= 1)),
	WRULE_IS_EXCL_RES2ID tinyint NULL DEFAULT 0 CHECK ((WRULE_IS_EXCL_RES2ID >= 0) AND (WRULE_IS_EXCL_RES2ID <= 1)),
	WRULE_DESCRIPTION nvarchar(255)	
)
GO


SET IDENTITY_INSERT dbo.temp_MSP_WEB_MGR_RULES ON
GO

Insert Into dbo.temp_MSP_WEB_MGR_RULES
	(WRULE_ID,
	WRULE_NAME,
	WRES_ID_MGR,
	WRULE_IS_ENABLED,
	WRULE_TYPE,
	WRULE_CONDITION_TYPE,
	WRULE_FIELD1_ID,
	WRULE_FIELD2_ID,
	WRULE_OPERATOR,
	WRULE_VALTYPE,
	WRULE_INT_VAL,
	WRULE_DATE_VAL,
	WRULE_DECIMAL_VAL,
	WRULE_VARCHAR_VAL,
	WRULE_IS_EXCL_PROJID,
	WRULE_IS_EXCL_RES1ID,
	WRULE_IS_EXCL_RES2ID,
	WRULE_DESCRIPTION)
Select  WRULE_ID,
	WRULE_NAME,
	WRES_ID_MGR,
	CAST(WRULE_IS_ENABLED As tinyInt),
	WRULE_TYPE,
	WRULE_CONDITION_TYPE,
	WRULE_FIELD1_ID,
	WRULE_FIELD2_ID,
	WRULE_OPERATOR,
	WRULE_VALTYPE,
	WRULE_INT_VAL,
	WRULE_DATE_VAL,
	WRULE_DECIMAL_VAL,
	WRULE_VARCHAR_VAL,
	CAST(WRULE_IS_EXCL_PROJID As tinyInt),
	CAST(WRULE_IS_EXCL_RES1ID As tinyInt),
	CAST(WRULE_IS_EXCL_RES2ID As tinyInt),
	WRULE_DESCRIPTION
FROM	dbo.MSP_WEB_MGR_RULES
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_MGR_RULES OFF
GO

DROP TABLE dbo.MSP_WEB_MGR_RULES
GO

execute sp_rename temp_MSP_WEB_MGR_RULES, MSP_WEB_MGR_RULES
GO



--
-- dbo.MSP_WEB_MGR_RULES_LISTS
--
-- no change
--



--
-- dbo.MSP_WEB_MODELS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_MODELS
(
	WMOD_MODEL_UID int IDENTITY (100, 1) NOT NULL CONSTRAINT PK_MSP_WEB_MODELS_WMOD_MODEL_UID PRIMARY KEY CLUSTERED,
	WMOD_MODEL_NAME nvarchar(255) NULL,
	WMOD_MODEL_DESC nvarchar(255) NULL,
	WMOD_CREATE_DATE datetime NOT NULL DEFAULT getdate(),
	WMOD_UPDATE_DATE datetime NOT NULL DEFAULT getdate(),
	WMOD_CREATED_BY nvarchar(255) NULL,
	WMOD_LAST_UPDATED_BY nvarchar(255) NULL,
	WMOD_LOCKED_BY nvarchar(255) NULL,
	WMOD_CREATED_BY_ID int NOT NULL DEFAULT 0,
	WMOD_LAST_UPDATED_BY_ID int NOT NULL DEFAULT 0,
	WMOD_LOCKED_BY_ID int NOT NULL DEFAULT 0,
	WMOD_RESERVED_BINARY_DATA image NULL,
	WMOD_MODEL image NULL
)
GO



--
-- dbo.MSP_WEB_NONWORKING
--
-- bit to tinyInt conversions
--
CREATE TABLE dbo.temp_MSP_WEB_NONWORKING
(
	WNONWORK_ID int IDENTITY (100,1) NOT NULL CONSTRAINT PK_MSP_WEB_NONWORKING_WNONWORK_ID PRIMARY KEY NONCLUSTERED,
	WNONWORK_SUBJECT nvarchar(255) NULL,
	WNONWORK_START datetime NULL,
	WNONWORK_END datetime NULL,
	WNONWORK_IS_ALL_DAY tinyint NOT NULL DEFAULT 1 CHECK ((WNONWORK_IS_ALL_DAY >= 0) AND (WNONWORK_IS_ALL_DAY <= 1)),
	WNONWORK_IS_WORKING tinyint NOT NULL DEFAULT 0 CHECK ((WNONWORK_IS_WORKING >= 0) AND (WNONWORK_IS_WORKING <= 1))
)
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_NONWORKING ON
GO

Insert Into dbo.temp_MSP_WEB_NONWORKING
	(WNONWORK_ID,
	WNONWORK_SUBJECT,
	WNONWORK_START,
	WNONWORK_END,
	WNONWORK_IS_ALL_DAY,
	WNONWORK_IS_WORKING)
Select  WNONWORK_ID,
	WNONWORK_SUBJECT,
	WNONWORK_START,
	WNONWORK_END,
	CAST(WNONWORK_IS_ALL_DAY As tinyInt),
	CAST(WNONWORK_IS_WORKING As tinyInt)
FROM	dbo.MSP_WEB_NONWORKING
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_NONWORKING OFF
GO

DROP TABLE dbo.MSP_WEB_NONWORKING
GO

execute sp_rename temp_MSP_WEB_NONWORKING, MSP_WEB_NONWORKING
GO



--
-- dbo.MSP_WEB_NONWORKING_CATEGORIES
--
-- no change
--



--
-- dbo.MSP_WEB_NOTIFICATION_EVENTS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_NOTIFICATION_EVENTS
(
	WNTFY_EVENT_ID int IDENTITY (2000, 1) NOT NULL CONSTRAINT PK_MSP_WEB_NOTIFICATION_EVENTS_EVENT_ID PRIMARY KEY CLUSTERED,
	WNTFY_EVENT_DEFAULT tinyint NOT NULL DEFAULT 1 CHECK ((WNTFY_EVENT_DEFAULT >= 0) AND (WNTFY_EVENT_DEFAULT <= 1)),
	WNTFY_EVENT_ACTION_ID int NOT NULL DEFAULT 0,
	WNTFY_EVENT_SHOW_UI tinyint NOT NULL DEFAULT 0 CHECK ((WNTFY_EVENT_SHOW_UI >= 0) AND (WNTFY_EVENT_SHOW_UI <= 1)),
	WNTFY_EVENT_QUERY nvarchar(1000) NULL,
	WNTFY_EMAIL_SUBJECT int NOT NULL DEFAULT 0,
	WNTFY_EMAIL_TITLE int NOT NULL DEFAULT 0,
	WNTFY_EMAIL_CONTENT int NOT NULL DEFAULT 0,
	WNTFY_EMAIL_TAIL1 int NOT NULL DEFAULT 0,
	WNTFY_EMAIL_TAIL2 int NOT NULL DEFAULT 0,
	WNTFY_EVENT_DESC nvarchar(100) NULL
)
GO



--
-- dbo.MSP_WEB_NOTIFICATIONS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_NOTIFICATIONS
(
	WNTFY_OWNER_ID int NOT NULL,
	WNTFY_EVENT_ID int NOT NULL,
	WNTFY_IS_ENABLED tinyint NOT NULL DEFAULT 1 CHECK ((WNTFY_IS_ENABLED >= 0) AND (WNTFY_IS_ENABLED <= 1))
)
GO

ALTER TABLE dbo.MSP_WEB_NOTIFICATIONS
ADD CONSTRAINT PK_MSP_WEB_NOTIFICATIONS_WNTFY_OWNER_ID_WNTFY_EVENT_ID PRIMARY KEY CLUSTERED
(
	WNTFY_OWNER_ID,
	WNTFY_EVENT_ID
)
GO



--
-- dbo.MSP_WEB_OBJECTS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_OBJECTS
(
	WOBJ_ID int IDENTITY (1, 1) NOT NULL CONSTRAINT PK_MSP_WEB_OBJECTS_WOBJ_ID PRIMARY KEY CLUSTERED,
	WOBJ_TYPE int NOT NULL,
	WOBJ_PROJ_ID int NOT NULL DEFAULT 0,
	WOBJ_TASK_ID int NULL,
	WOBJ_TP_ID int NULL,
	WOBJ_LIST_NAME nvarchar(255) NULL,
	WOBJ_DESC ntext NULL,
	WOBJ_DOC_REF_CNT int NOT NULL DEFAULT 0,
	WOBJ_ISSUE_REF_CNT int NOT NULL DEFAULT 0,
	WOBJ_OTHER_REF_CNT int NOT NULL DEFAULT 0
)
GO

CREATE INDEX I_OBJ_TYPEPROJTASK
ON dbo.MSP_WEB_OBJECTS (WOBJ_TYPE, WOBJ_PROJ_ID, WOBJ_TASK_ID)
GO

CREATE INDEX I_OBJ_TYPEPROJLISTTP
ON dbo.MSP_WEB_OBJECTS (WOBJ_TYPE, WOBJ_PROJ_ID, WOBJ_LIST_NAME, WOBJ_TP_ID)
GO



--
-- dbo.MSP_WEB_OBJECT_LINKS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_OBJECT_LINKS
(
	WOBJLINK_ID int IDENTITY (1, 1) NOT NULL CONSTRAINT MSP_WEB_OBJECT_LINKS_WOBJLINK_ID PRIMARY KEY CLUSTERED,
	WOBJLINK_OBJECT1 int NOT NULL,
	WOBJLINK_OBJECT2 int NOT NULL,
	WOBJLINK_TYPE int NOT NULL
)
GO

CREATE INDEX I_OBJLINKS_OBJ1OBJ2
ON dbo.MSP_WEB_OBJECT_LINKS (WOBJLINK_OBJECT1, WOBJLINK_OBJECT2)
GO



--
-- dbo.MSP_WEB_OBJECT_TYPES
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_OBJECT_TYPES
(
	WOBJTYPE_ID int IDENTITY (1, 1) NOT NULL CONSTRAINT MSP_WEB_OBJECT_TYPES_WOBJTYPE_ID PRIMARY KEY CLUSTERED,
	WOBJTYPE_NAME nvarchar(50) NOT NULL
)
GO



--
-- dbo.MSP_WEB_OBJECT_LINK_TYPES
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_OBJECT_LINK_TYPES
(
	WLINKTYPE_ID int IDENTITY (1, 1) NOT NULL CONSTRAINT MSP_WEB_OBJECT_LINK_TYPES_WLINKTYPE_ID PRIMARY KEY CLUSTERED,
	WLINKTYPE_DESC nvarchar(255) NOT NULL
)
GO



--
-- dbo.MSP_WEB_PROJECT_WORKGROUP_INFO
--
-- column added, ID reset
--
ALTER TABLE dbo.MSP_WEB_PROJECT_WORKGROUP_INFO
	ADD PICKLIST_INFO ntext NULL
GO

UPDATE	dbo.MSP_WEB_PROJECT_WORKGROUP_INFO
SET	CUSTFIELD_INFO_ID = CUSTFIELD_INFO_ID + 10000
GO

--
-- dbo.temp_MSP_WEB_PROJECTS
--
-- bit to tinyInt conversions, and many new columns added
--
CREATE TABLE dbo.temp_MSP_WEB_PROJECTS
(
	WPROJ_ID int IDENTITY (100, 1) NOT NULL CONSTRAINT PK_MSP_WEB_PROJECTS_WPROJ_ID PRIMARY KEY CLUSTERED,
	PROJ_NAME nvarchar(255) NULL,
	PROJ_TIMESTAMP nvarchar(32) NULL,
	WPROJ_DESCRIPTION ntext NULL,
	WLOCAL_PATH nvarchar(260) NULL,
	WPATH nvarchar(260) NULL,
	WRES_ID int NULL,
	WDSN_ID int NULL,
	WPROJ_DELEG_ALLOWED tinyint NOT NULL DEFAULT 0 CHECK ((WPROJ_DELEG_ALLOWED >= 0) AND (WPROJ_DELEG_ALLOWED <= 1)),
	WPROJ_IS_NONWORKING tinyint NOT NULL DEFAULT 0 CHECK ((WPROJ_IS_NONWORKING >= 0) AND (WPROJ_IS_NONWORKING <= 1)),
	WPROJ_SCOPE tinyint NOT NULL DEFAULT 0,
	WPROJ_IS_CONSOLIDATED_PROJECT tinyint NOT NULL DEFAULT 0 CHECK ((WPROJ_IS_CONSOLIDATED_PROJECT >= 0) AND (WPROJ_IS_CONSOLIDATED_PROJECT <= 1)),
	WPROJ_RES_CAN_DECLINE tinyint NOT NULL DEFAULT 0 CHECK ((WPROJ_RES_CAN_DECLINE >= 0) AND (WPROJ_RES_CAN_DECLINE <= 1)),
	WPROJ_DEFAULT_WPROJ_ID int NOT NULL DEFAULT 0,
	WPROJ_TRACKING_METHOD int NULL DEFAULT 0,
	WPROJ_LAST_PUB datetime NULL,
	PROJ_ID int NULL,
	WPROJ_TYPE int NOT NULL DEFAULT 0,
	OPT_DEF_START_TIME datetime,
	OPT_DEF_FINISH_TIME datetime,
	WPROJ_STS_SUBWEB_NAME nvarchar(128) NULL DEFAULT 'NONE',
	WSTS_SERVER_ID int NOT NULL DEFAULT 0, 	
	CONSTRAINT UQ_MSP_WEB_PROJECTS_PROJ_ID_PROJ_TIMESTAMP_PROJ_NAME_WRES_ID UNIQUE (PROJ_ID, PROJ_TIMESTAMP, PROJ_NAME, WRES_ID)
)
GO

CREATE INDEX I_FILES_OWNERRESID 
ON dbo.temp_MSP_WEB_PROJECTS (WRES_ID)
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_PROJECTS ON
GO

Insert Into dbo.temp_MSP_WEB_PROJECTS
	(WPROJ_ID,
	PROJ_NAME,
	PROJ_TIMESTAMP,
	WLOCAL_PATH,
	WPATH,
	WRES_ID,
	WDSN_ID,
	WPROJ_DELEG_ALLOWED,
	WPROJ_IS_NONWORKING,
	WPROJ_DEFAULT_WPROJ_ID,
	OPT_DEF_START_TIME,
	OPT_DEF_FINISH_TIME)
Select  WPROJ_ID,
	PROJ_NAME,
	PROJ_TIMESTAMP,
	WLOCAL_PATH,
	WPATH,
	WRES_ID,
	WDSN_ID,
 	CAST(WPROJ_DELEG_ALLOWED As tinyInt),
	CAST(WPROJ_IS_NONWORKING As tinyInt),
	WPROJ_ID,
	OPT_DEF_START_TIME,
	OPT_DEF_FINISH_TIME
FROM	dbo.MSP_WEB_PROJECTS
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_PROJECTS OFF
GO

DROP TABLE dbo.MSP_WEB_PROJECTS
GO

execute sp_rename temp_MSP_WEB_PROJECTS, MSP_WEB_PROJECTS
GO



--
-- dbo.MSP_WEB_REMINDER_TYPES
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_REMINDER_TYPES
(
	WRMND_TYPE_ID int NOT NULL CONSTRAINT PK_MSP_WEB_REMINDER_TYPES_WRMND_TYPE_ID PRIMARY KEY CLUSTERED,
	WRMND_TABLE_ID int NOT NULL,
	WRMND_IS_SELF tinyint NOT NULL CHECK ((WRMND_IS_SELF >= 0) AND (WRMND_IS_SELF <= 1)),
	WRMND_QUERY nvarchar(1000) NULL,
	WRMND_EMAIL_TITLE int NOT NULL DEFAULT 0,
	WRMND_EMAIL_TAIL1 int NOT NULL DEFAULT 0,
	WRMND_TYPE_DESC nvarchar(100) NULL
)
GO



--
-- dbo.MSP_WEB_REMINDERS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_REMINDERS
(
	WRMND_OWNER_ID int NOT NULL,
	WRMND_TYPE_ID int NOT NULL,
	WRMND_IS_ENABLED tinyint NOT NULL CHECK ((WRMND_IS_ENABLED >= 0) AND (WRMND_IS_ENABLED <= 1)),
	WRMND_SEND_TO smallint NOT NULL DEFAULT 0,
	WRMND_PARAMETERS nvarchar(20) NULL,
	WRMND_CRITERIA nvarchar(510) NULL,
	WRMND_START_DATE datetime NULL,
	WFREQ int NOT NULL,
	WFREQPAR1 int NULL,
	WFREQPAR2 int NULL,
	WFREQPAR3 int NULL,
	WFREQPAR4 int NULL,
	WFREQPAR5 int NULL,
	WFREQPAR6 int NULL,
	WFREQ_DATE datetime NULL,
	WRMND_NEXT_UPDATE datetime NULL,
	CONSTRAINT PK_MSP_WEB_REMINDERS_WRMND_OWNER_ID_WRMND_TYPE_ID 
	PRIMARY KEY CLUSTERED (WRMND_OWNER_ID, WRMND_TYPE_ID)
)
GO




--
-- dbo.MSP_WEB_REMINDERS_TEMP
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_REMINDERS_TEMP
(
	WRMND_ID_RECEIVER int NOT NULL,
	WRMND_TABLE_ID int NOT NULL,
	WRMND_MSG_SENT tinyint NOT NULL CHECK ((WRMND_MSG_SENT >= 0) AND (WRMND_MSG_SENT <= 1)),
	WRMND_MESSAGE ntext NULL,
	CONSTRAINT PK_MSP_WEB_REMINDERS_TEMP_WRMND_ID_RECEIVER_WRMND_TABLE_ID
	PRIMARY KEY CLUSTERED (WRMND_ID_RECEIVER, WRMND_TABLE_ID)
)
GO




--
-- dbo.MSP_WEB_RESERVED_DATA
--
-- add fake ID
--
CREATE TABLE dbo.temp_MSP_WEB_RESERVED_DATA
(
	RESERVED_IDFAKE int IDENTITY CONSTRAINT PK_MSP_WEB_RESERVED_DATA PRIMARY KEY,
	RESERVED_DATA1 int NULL,
	RESERVED_DATA2 int NULL,
	RESERVED_DATA3 int NOT NULL DEFAULT 2,
	RESERVED_DATA4 int NULL DEFAULT -1
)
GO

Insert Into dbo.temp_MSP_WEB_RESERVED_DATA
	(RESERVED_DATA1,
	RESERVED_DATA2,
	RESERVED_DATA3,
	RESERVED_DATA4)
Select  RESERVED_DATA1,
	RESERVED_DATA2,
	RESERVED_DATA3,
	RESERVED_DATA4
FROM	dbo.MSP_WEB_RESERVED_DATA
GO

DROP TABLE dbo.MSP_WEB_RESERVED_DATA
GO

execute sp_rename temp_MSP_WEB_RESERVED_DATA, MSP_WEB_RESERVED_DATA
GO



--
-- dbo.MSP_WEB_RESOURCE_SETTINGS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_RESOURCE_SETTINGS
(
	WSET_ID int IDENTITY (1, 1) NOT NULL CONSTRAINT PK_MSP_WEB_RESOURCE_SETTINGS_WSET_ID PRIMARY KEY NONCLUSTERED,
	WSEC_PAGE_ID int NOT NULL,
	WRES_GUID uniqueidentifier NOT NULL,
	WSET_VALUE ntext NOT NULL
)
GO

CREATE UNIQUE CLUSTERED INDEX IX_MSP_WEB_RESOURCE_SETTINGS_WRES_GUID_WSET_DATASPACE
ON dbo.MSP_WEB_RESOURCE_SETTINGS(WRES_GUID, WSEC_PAGE_ID)
GO



--
-- dbo.MSP_WEB_RESOURCES
--
-- bit to tinyInt conversions, and many new columns added
--
CREATE TABLE dbo.temp_MSP_WEB_RESOURCES
(
	WRES_ID int IDENTITY (100, 1) NOT NULL CONSTRAINT PK_MSP_WEB_RESOURCES_WRES_ID PRIMARY KEY CLUSTERED,
	WRES_GUID uniqueidentifier NOT NULL DEFAULT NEWID(),
	RES_NAME nvarchar(255) NOT NULL,
	RES_EUID int NULL,
	WRES_USE_NT_LOGON tinyint NOT NULL DEFAULT 0 CHECK ((WRES_USE_NT_LOGON >= 0) AND (WRES_USE_NT_LOGON <= 1)),
	WRES_NT_ACCOUNT nvarchar(255) NOT NULL DEFAULT (N''),
	WRES_EMAIL nvarchar(255) NULL,
	WRES_EMAIL_LANGUAGE int NOT NULL DEFAULT 0,
	WRES_LAST_UPDATE_TIME datetime NULL,
	WRES_LAST_CHECKED_TIME datetime NULL,
	WRES_IS_OFFLINE tinyint NOT NULL DEFAULT 0 CHECK ((WRES_IS_OFFLINE >= 0) AND (WRES_IS_OFFLINE <= 1)),
	WRES_LAST_CONNECT datetime NULL,
	WRES_IS_MANAGER tinyint NOT NULL DEFAULT 0 CHECK ((WRES_IS_MANAGER >= 0) AND (WRES_IS_MANAGER <= 1)),
	WRES_IS_ADMIN tinyint NOT NULL DEFAULT 0 CHECK ((WRES_IS_ADMIN >= 0) AND (WRES_IS_ADMIN <= 1)),
	WRES_IS_ENABLED tinyint NOT NULL DEFAULT 1 CHECK ((WRES_IS_ENABLED >= 0) AND (WRES_IS_ENABLED <= 1)),
	WRES_IS_PC2000 tinyint NOT NULL DEFAULT 0 CHECK ((WRES_IS_PC2000 >= 0) AND (WRES_IS_PC2000 <= 1)),
	WRES_PASSWORD nvarchar(255) NOT NULL DEFAULT(N''),
	WRES_DEL_TASKUPDATE_MSG tinyint NOT NULL DEFAULT 0 CHECK ((WRES_DEL_TASKUPDATE_MSG >= 0) AND (WRES_DEL_TASKUPDATE_MSG <= 1)),
	WRES_DEL_DELEG_MSG tinyint NOT NULL DEFAULT 0 CHECK ((WRES_DEL_DELEG_MSG >= 0) AND (WRES_DEL_DELEG_MSG <= 1)),
	WRES_DEL_NEWTASK_MSG tinyint NOT NULL DEFAULT 0 CHECK ((WRES_DEL_NEWTASK_MSG >= 0) AND (WRES_DEL_NEWTASK_MSG <= 1)),
	WRES_DEL_REASSN_MSG tinyint NOT NULL DEFAULT 0 CHECK ((WRES_DEL_REASSN_MSG >= 0) AND (WRES_DEL_REASSN_MSG <= 1)),
	WRES_INCL_TEAMLEAD_RESOURCES tinyint NOT NULL DEFAULT 0 CHECK ((WRES_INCL_TEAMLEAD_RESOURCES >= 0) AND (WRES_INCL_TEAMLEAD_RESOURCES<= 1)),
	WRES_INCL_TEAMASSIGN_RESOURCES tinyint NOT NULL DEFAULT 0 CHECK ((WRES_INCL_TEAMASSIGN_RESOURCES >= 0) AND (WRES_INCL_TEAMASSIGN_RESOURCES <= 1)),
	WRES_CAN_LOGIN tinyint NOT NULL DEFAULT 0 CHECK ((WRES_CAN_LOGIN >= 0) AND (WRES_CAN_LOGIN <= 1)),
	WRES_COUNT_LICENSE tinyint NOT NULL DEFAULT 0 CHECK ((WRES_COUNT_LICENSE >= 0) AND (WRES_COUNT_LICENSE <= 1)),
	RES_PHONETICS nvarchar(255) NULL,
	RESERVED_DATA1 int NOT NULL,
	RESERVED_DATA2 int NOT NULL,
	RESERVED_DATA3 int NOT NULL,
	RESERVED_DATA4 int NOT NULL
)
GO

CREATE UNIQUE INDEX I_RESOURCES_WRESNAME 
ON dbo.temp_MSP_WEB_RESOURCES (RES_NAME)
WITH FILLFACTOR = 40
GO

CREATE INDEX IX_MSP_WEB_RESOURCES_WRES_NT_ACCOUNT on
dbo.temp_MSP_WEB_RESOURCES (WRES_NT_ACCOUNT)
GO

CREATE INDEX IX_MSP_WEB_RESOURCES_WRES_GUID
ON dbo.temp_MSP_WEB_RESOURCES (WRES_GUID)
GO

CREATE INDEX IX_MSP_WEB_RESOURCES_RES_EUID
ON dbo.temp_MSP_WEB_RESOURCES (RES_EUID)
GO
SET IDENTITY_INSERT dbo.temp_MSP_WEB_RESOURCES ON
GO

Insert Into dbo.temp_MSP_WEB_RESOURCES
	(WRES_ID,
	RES_NAME,
	WRES_USE_NT_LOGON,
	WRES_NT_ACCOUNT,
	WRES_EMAIL,
	WRES_LAST_UPDATE_TIME,
	WRES_LAST_CHECKED_TIME,
	WRES_IS_OFFLINE,
	WRES_LAST_CONNECT,
	WRES_IS_MANAGER,
	WRES_IS_ADMIN,
	WRES_IS_ENABLED,
	WRES_IS_PC2000,
	WRES_DEL_TASKUPDATE_MSG,
	WRES_DEL_DELEG_MSG,
	WRES_DEL_NEWTASK_MSG,
	WRES_DEL_REASSN_MSG,
	WRES_INCL_TEAMLEAD_RESOURCES,
	WRES_INCL_TEAMASSIGN_RESOURCES,
	RES_PHONETICS,
	RESERVED_DATA1,
	RESERVED_DATA2,
	RESERVED_DATA3,
	RESERVED_DATA4)
Select  WRES_ID,
	RES_NAME,
	CAST(WRES_USE_NT_LOGON As tinyInt),
	Case WRES_USE_NT_LOGON
		When 1 Then RES_NAME
		Else N''
	End,
	WRES_EMAIL,
	WRES_LAST_UPDATE_TIME,
	WRES_LAST_CHECKED_TIME,
	CAST(WRES_IS_OFFLINE As tinyInt),
	WRES_LAST_CONNECT,
	CAST(WRES_IS_MANAGER As tinyInt),
	CAST(WRES_IS_ADMIN As tinyInt),
	CAST(WRES_IS_ENABLED As tinyInt),
	1,
	CAST(WRES_DEL_TASKUPDATE_MSG As tinyInt),
	CAST(WRES_DEL_DELEG_MSG As tinyInt),
	CAST(WRES_DEL_NEWTASK_MSG As tinyInt),
	CAST(WRES_DEL_REASSN_MSG As tinyInt),
	CAST(WRES_INCL_TEAMLEAD_RESOURCES As tinyInt),
	CAST(WRES_INCL_TEAMASSIGN_RESOURCES As tinyInt),
	RES_PHONETICS,
	RESERVED_DATA1,
	RESERVED_DATA2,
	RESERVED_DATA3,
	RESERVED_DATA4
FROM	dbo.MSP_WEB_RESOURCES
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_RESOURCES OFF
GO

DROP TABLE dbo.MSP_WEB_RESOURCES
GO

execute sp_rename temp_MSP_WEB_RESOURCES, MSP_WEB_RESOURCES
GO



--
-- dbo.MSP_WEB_RESOURCES_CATEGORIES
--
-- no change
--



--
-- dbo.MSP_WEB_SECURITY_CATEGORY_OBJECTS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_SECURITY_CATEGORY_OBJECTS
(
	WSEC_CAT_ID int NOT NULL,	
	WSEC_OBJ_TYPE tinyint NOT NULL,	
	WSEC_OBJ_ID int NOT NULL,
	CONSTRAINT PK_MSP_WEB_SECURITY_CATEGORY_OBJECTS_WSEC_CAT_ID_WSEC_OBJ_TYPE_WSEC_OBJ_ID PRIMARY KEY CLUSTERED (WSEC_CAT_ID, WSEC_OBJ_TYPE, WSEC_OBJ_ID)
)
GO



--
-- dbo.MSP_WEB_SECURITY_CATEGORY_RULES
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_SECURITY_CATEGORY_RULES
(
	WSEC_CAT_ID int NOT NULL,	
	WSEC_OBJ_TYPE tinyint NOT NULL,
	WSEC_OBJ_RULE_TYPE tinyint NOT NULL DEFAULT 0, 
	CONSTRAINT PK_MSP_WEB_SECURITY_CATEGORY_RULES_WSEC_CAT_ID_WSEC_OBJ_TYPE_WSEC_OBJ_RULE_TYPE PRIMARY KEY CLUSTERED (WSEC_CAT_ID, WSEC_OBJ_TYPE, WSEC_OBJ_RULE_TYPE)
)
GO



--
-- dbo.MSP_WEB_SECURITY_CATEGORIES
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_SECURITY_CATEGORIES
(
	WSEC_CAT_ID int IDENTITY (100, 1) NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_CATEGORIES_WSEC_CAT_ID PRIMARY KEY CLUSTERED,
	WSEC_CAT_NAME nvarchar(255) NULL, 
	WSEC_CAT_DESC nvarchar(255) NULL
)
GO



--
-- dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS
(
	WSEC_FEA_ACT_ID int IDENTITY (10000, 1) NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_FEATURES_ACTIONS_WSEC_FEA_ACT_ID PRIMARY KEY CLUSTERED,
	WSEC_FEA_ACT_NAME_ID int NOT NULL,
	WSEC_FEA_ACT_PARENT int NOT NULL DEFAULT 0, 
	WSEC_IS_ACTION tinyint NOT NULL DEFAULT 0 CHECK ((WSEC_IS_ACTION >= 0) AND (WSEC_IS_ACTION <= 1)),
	WSEC_ON_OBJECT tinyint NOT NULL DEFAULT 0 CHECK ((WSEC_ON_OBJECT >= 0) AND (WSEC_ON_OBJECT <= 1)),
	WSEC_OBJ_TYPE_ID int NOT NULL DEFAULT 0
)
GO



--
-- dbo.MSP_WEB_SECURITY_GROUP_MEMBERS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_SECURITY_GROUP_MEMBERS
(
	WSEC_GRP_GUID uniqueidentifier NOT NULL,	
	WRES_GUID uniqueidentifier NOT NULL, 		
	CONSTRAINT PK_MSP_WEB_SECURITY_GROUP_MEMBERS_WRES_GUID_WRES_GUID_WSEC_GPR_GUID
	PRIMARY KEY CLUSTERED(WRES_GUID,WSEC_GRP_GUID)
)
GO

CREATE INDEX IX_MSP_WEB_SECURITY_GROUP_MEMBERS_WRES_GUID
ON dbo.MSP_WEB_SECURITY_GROUP_MEMBERS(WSEC_GRP_GUID)
GO



--
-- dbo.MSP_WEB_SECURITY_GROUPS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_SECURITY_GROUPS
(
	WSEC_GRP_ID int IDENTITY (100, 1) NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_GROUPS_WSEC_GRP_ID PRIMARY KEY NONCLUSTERED,
	WSEC_GRP_GUID uniqueidentifier NOT NULL DEFAULT (newid()),
	WSEC_GRP_NAME nvarchar(100) NOT NULL,
	WSEC_GRP_DESC nvarchar(255) NULL,
	WSEC_GRP_DASHBOARD_URL nvarchar(255) NULL
)
GO

CREATE CLUSTERED INDEX IX_MSP_WEB_SECURITY_GROUPS
ON dbo.MSP_WEB_SECURITY_GROUPS(WSEC_GRP_GUID)
GO



--
-- dbo.MSP_WEB_SECURITY_MENUS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_SECURITY_MENUS
(
	WSEC_MENU_ID int IDENTITY (10000, 1) NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_MENUS_WSEC_MENU_ID PRIMARY KEY CLUSTERED,
	WSEC_MENU_NAME_ID int NULL,
	WSEC_MENU_CUSTOM_NAME nvarchar(100) NULL,
	WSEC_MENU_PARENT_ID int NULL DEFAULT 0,  
	WSEC_MENU_SEQ smallint NULL,
	WSEC_MENU_DESC_ID int NULL,
	WSEC_MENU_CUSTOM_DESC nvarchar(100) NULL,
	WSEC_MENU_PAGE_ID int NULL,
	WSEC_MENU_IS_CUSTOM tinyint NOT NULL CHECK ((WSEC_MENU_IS_CUSTOM >= 0) AND (WSEC_MENU_IS_CUSTOM <= 1)),
	WSEC_MENU_IS_TOP_LEVEL tinyint NOT NULL CHECK ((WSEC_MENU_IS_TOP_LEVEL >= 0) AND (WSEC_MENU_IS_TOP_LEVEL <= 1)),
	WSEC_MENU_LINKGROUP_NAME_ID int NULL
)
GO



--
-- dbo.MSP_WEB_SECURITY_OBJECT_RULES
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_SECURITY_OBJECT_RULES
(
	WSEC_OBJ_TYPE tinyint NOT NULL,		
	WSEC_OBJ_RULE_TYPE tinyint NOT NULL DEFAULT 0, 
	WSEC_OBJ_RULE_DESC nvarchar(255) NULL
	CONSTRAINT PK_MSP_WEB_SECURITY_OBJECT_RULES_WSEC_OBJ_TYPE_WSEC_OBJ_RULE_TYPE PRIMARY KEY CLUSTERED (WSEC_OBJ_TYPE, WSEC_OBJ_RULE_TYPE)
)
GO



--
-- dbo.MSP_WEB_SECURITY_OBJECT_TYPES
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_SECURITY_OBJECT_TYPES
(
	WSEC_OBJ_TYPE_ID int IDENTITY (1000, 1) NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_OBJECT_TYPES_WSEC_OBJ_TYPE_ID PRIMARY KEY CLUSTERED,
	WSEC_OBJ_TYPE_NAME nvarchar(100) NOT NULL
)
GO



--
-- dbo.MSP_WEB_SECURITY_ORG_PERMISSIONS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_SECURITY_ORG_PERMISSIONS
(
	WSEC_FEA_ACT_ID int NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_ORG_PERMISSIONS_WSEC_FEA_ACT_ID PRIMARY KEY CLUSTERED,
	WSEC_ALLOW tinyint NOT NULL DEFAULT 0 CHECK ((WSEC_ALLOW >= 0) AND (WSEC_ALLOW <= 1)),
	WSEC_DENY tinyint NOT NULL DEFAULT 0 CHECK ((WSEC_DENY >= 0) AND (WSEC_DENY <= 1)),
	WSEC_ACCESS tinyint NOT NULL DEFAULT 0 CHECK ((WSEC_ACCESS >= 0) AND (WSEC_ACCESS <= 1)),
	WSEC_PAID tinyint NOT NULL DEFAULT 0 CHECK ((WSEC_PAID >= 0) AND (WSEC_PAID <= 1))
)
GO



--
-- dbo.MSP_WEB_SECURITY_PAGES
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_SECURITY_PAGES
(
	WSEC_PAGE_ID int IDENTITY (10000, 1) NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_PAGES_WSEC_PAGE_ID PRIMARY KEY CLUSTERED,
	WSEC_PAGE_MENU_ID int NULL,				
	WSEC_PAGE_URL nvarchar(255) NULL,
	WSEC_PAGE_CUSTOM_URL nvarchar(255) NULL,
	WSEC_PAGE_HELP_URL nvarchar(255) NULL,
	WSEC_PAGE_ACT_ID int NULL,		
	WSEC_PAGE_MASTER_PAGE_ID int NULL,
	WSEC_PAGE_SESSION_SETTINGS ntext NULL,
	WSEC_PAGE_DATABASE_SETTINGS ntext NULL,
	WSEC_PAGE_CAN_OFFLINE tinyint NOT NULL DEFAULT 0 CHECK ((WSEC_PAGE_CAN_OFFLINE >= 0) AND (WSEC_PAGE_CAN_OFFLINE <= 1))
)
GO

CREATE INDEX IX_MSP_WEB_SECURITY_PAGES_WSEC_PAGE_MENU_ID
ON dbo.MSP_WEB_SECURITY_PAGES(WSEC_PAGE_MENU_ID)
GO

CREATE INDEX IX_MSP_WEB_SECURITY_PAGES_WSEC_PAGE_ACT_ID
ON dbo.MSP_WEB_SECURITY_PAGES(WSEC_PAGE_ACT_ID)
GO



--
-- dbo.MSP_WEB_SECURITY_SP_CAT_PERMISSIONS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_SECURITY_SP_CAT_PERMISSIONS
(
	WSEC_REL_ID int NOT NULL,					
	WSEC_FEA_ACT_ID int NOT NULL,				
	WSEC_ALLOW tinyint NOT NULL DEFAULT 0 CHECK ((WSEC_ALLOW >= 0) AND (WSEC_ALLOW <= 1)),
	WSEC_DENY tinyint NOT NULL DEFAULT 0 CHECK ((WSEC_DENY >= 0) AND (WSEC_DENY <= 1)),
	WSEC_ACCESS tinyint NOT NULL DEFAULT 0 CHECK ((WSEC_ACCESS >= 0) AND (WSEC_ACCESS <= 1)),
	CONSTRAINT PK_MSP_WEB_SECURITY_SP_CAT_PERMISSIONS_WSEC_REL_ID_WSEC_FEA_ACT_ID
	PRIMARY KEY CLUSTERED (WSEC_REL_ID, WSEC_FEA_ACT_ID) 
)
GO



--
-- dbo.MSP_WEB_SECURITY_SP_CAT_RELATIONS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_SECURITY_SP_CAT_RELATIONS
(
	WSEC_REL_ID int IDENTITY (100, 1) NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_SP_CAT_RELATIONS_WSEC_REL_ID PRIMARY KEY CLUSTERED,
	WSEC_SP_GUID uniqueidentifier NOT NULL,
	WSEC_CAT_ID int NOT NULL 
)
GO  

CREATE INDEX  IX_MSP_WEB_SECURITY_SP_CAT_RELATIONS
ON dbo.MSP_WEB_SECURITY_SP_CAT_RELATIONS(WSEC_SP_GUID, WSEC_CAT_ID)
GO



--
-- dbo.MSP_WEB_SECURITY_TEMPLATE_PERMISSIONS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_SECURITY_TEMPLATE_PERMISSIONS
(
	WSEC_TMPL_ID int NOT NULL,
	WSEC_ACT_ID int NOT NULL, 
	WSEC_ALLOW tinyint NOT NULL DEFAULT 0 CHECK ((WSEC_ALLOW >= 0) AND (WSEC_ALLOW <= 1)),
	WSEC_DENY tinyint NOT NULL DEFAULT 0 CHECK ((WSEC_DENY >= 0) AND (WSEC_DENY <= 1)),
	WSEC_ACCESS tinyint NOT NULL DEFAULT 0 CHECK ((WSEC_ACCESS >= 0) AND (WSEC_ACCESS <= 1)),
	CONSTRAINT PK_MSP_WEB_SECURITY_TEMPLATE_PERMISSIONS_WSEC_TMPL_ID_WSEC_ACT_ID PRIMARY KEY
	CLUSTERED (WSEC_TMPL_ID,WSEC_ACT_ID)	
)
GO



--
-- dbo.MSP_WEB_SECURITY_TEMPLATES
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_SECURITY_TEMPLATES
(
	WSEC_TMPL_ID int IDENTITY (1000, 1) NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_TEMPLATES_WSEC_TMPL_ID PRIMARY KEY CLUSTERED,
	WSEC_TMPL_NAME nvarchar(100) NOT NULL,
	WSEC_TMPL_DESC nvarchar(255) NULL
)
GO



--
-- dbo.MSP_WEB_STATUS_DISTRIBUTION
--
-- no change
--



--
-- dbo.MSP_WEB_STATUS_FREQUENCIES
--
-- no change
--



--
-- dbo.MSP_WEB_STATUS_REPORTS
--
-- bit to tinyInt conversions
--
CREATE TABLE dbo.temp_MSP_WEB_STATUS_REPORTS
(	
	WSR_ID int IDENTITY (100, 1) NOT NULL CONSTRAINT PK_MSP_WEB_STATUS_REPORTS_WSR_ID PRIMARY KEY NONCLUSTERED,
	WRES_ID_MGR int NOT NULL,
	WREPORT_NAME nvarchar(255) NULL,
	WREPORT_UNREQUESTED tinyint NOT NULL DEFAULT 0 CHECK ((WREPORT_UNREQUESTED >= 0) AND (WREPORT_UNREQUESTED <= 1)),
	WREPORT_IS_ENABLED tinyint NOT NULL DEFAULT 1 CHECK ((WREPORT_IS_ENABLED >= 0) AND (WREPORT_IS_ENABLED <= 1)),
	WREPORT_FORMAT ntext
)
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_STATUS_REPORTS ON
GO

Insert Into dbo.temp_MSP_WEB_STATUS_REPORTS
	(WSR_ID,
	WRES_ID_MGR,
	WREPORT_NAME,
	WREPORT_UNREQUESTED,
	WREPORT_IS_ENABLED,
	WREPORT_FORMAT)
Select  WSR_ID,
	WRES_ID_MGR,
	WREPORT_NAME,
	CAST(WREPORT_UNREQUESTED As tinyInt),
	CAST(WREPORT_IS_ENABLED As tinyInt),
	WREPORT_FORMAT
FROM	dbo.MSP_WEB_STATUS_REPORTS
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_STATUS_REPORTS OFF
GO

DROP TABLE dbo.MSP_WEB_STATUS_REPORTS
GO

execute sp_rename temp_MSP_WEB_STATUS_REPORTS, MSP_WEB_STATUS_REPORTS
GO



--
-- dbo.MSP_WEB_STATUS_REQUESTS
--
-- bit to tinyInt conversions
--
CREATE TABLE dbo.temp_MSP_WEB_STATUS_REQUESTS
(	
	WRES_ID_RECEIVER int NOT NULL,
	WSR_ID int NOT NULL,
	WDUE_ON datetime NULL,
	WREQ_IS_AUTOMERGE tinyint NOT NULL DEFAULT 0 CHECK ((WREQ_IS_AUTOMERGE >= 0) AND (WREQ_IS_AUTOMERGE <= 1)),
	WREQ_IS_NEW_REQUEST tinyint NOT NULL DEFAULT 0 CHECK ((WREQ_IS_NEW_REQUEST >= 0) AND (WREQ_IS_NEW_REQUEST <= 1)),
	WREQ_IS_SENT tinyint NOT NULL DEFAULT 0 CHECK ((WREQ_IS_SENT >= 0) AND (WREQ_IS_SENT <= 1)),
	WREQ_IS_ENABLED tinyint NOT NULL DEFAULT 0 CHECK ((WREQ_IS_ENABLED >= 0) AND (WREQ_IS_ENABLED <= 1)),
	CONSTRAINT PK_MSP_WEB_STATUS_REQUESTS_WRES_ID_RECEIVER_WSR_ID
	PRIMARY KEY CLUSTERED (WSR_ID, WRES_ID_RECEIVER)
)
GO

Insert Into dbo.temp_MSP_WEB_STATUS_REQUESTS
	(WRES_ID_RECEIVER,
	WSR_ID,
	WDUE_ON,
	WREQ_IS_AUTOMERGE,
	WREQ_IS_NEW_REQUEST,
	WREQ_IS_SENT,
	WREQ_IS_ENABLED)
Select  WRES_ID_RECEIVER,
	WSR_ID,
	WDUE_ON,
	CAST(WREQ_IS_AUTOMERGE As tinyInt),
	CAST(WREQ_IS_NEW_REQUEST As tinyInt),
	CAST(WREQ_IS_SENT As tinyInt),
	CAST(WREQ_IS_ENABLED As tinyInt)
FROM	dbo.MSP_WEB_STATUS_REQUESTS
GO

DROP TABLE dbo.MSP_WEB_STATUS_REQUESTS
GO

execute sp_rename temp_MSP_WEB_STATUS_REQUESTS, MSP_WEB_STATUS_REQUESTS
GO



--
-- dbo.MSP_WEB_STATUS_REQUESTS
--
-- bit to tinyInt conversions
--
CREATE TABLE dbo.temp_MSP_WEB_STATUS_RESPONSES
(	
	WRESP_ID int IDENTITY (100, 1) NOT NULL CONSTRAINT PK_MSP_WEB_STATUS_RESPONSES_WRESP_ID PRIMARY KEY NONCLUSTERED,
	WRES_ID int NULL,
	WSR_ID int NULL,
	WSRESP_PERIOD_START datetime NULL,
	WSRESP_PERIOD_FINISH datetime NULL,
	WSUBMIT_STATUS int NULL,
	WSUBMIT_DATE datetime NULL,
	WUPDATE_STATUS int NULL,
	WNUM_SECTIONS int NULL,
	WRESP_IS_MATCHING tinyint NOT NULL DEFAULT 0 CHECK ((WRESP_IS_MATCHING >= 0) AND (WRESP_IS_MATCHING <= 1)),
	WRESP_IS_NEW_RESPONSE tinyint NOT NULL DEFAULT 0 CHECK ((WRESP_IS_NEW_RESPONSE >= 0) AND (WRESP_IS_NEW_RESPONSE <= 1)),
	WRESP_IS_MERGED tinyint NOT NULL DEFAULT 0 CHECK ((WRESP_IS_MERGED >= 0) AND (WRESP_IS_MERGED <= 1)),
	WRESP_TEXT ntext NULL
)
GO

CREATE INDEX I_WSR_ID
ON dbo.temp_MSP_WEB_STATUS_RESPONSES (WSR_ID, WRES_ID)
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_STATUS_RESPONSES ON
GO

Insert Into dbo.temp_MSP_WEB_STATUS_RESPONSES
	(WRESP_ID,
	WRES_ID,
	WSR_ID,
	WSRESP_PERIOD_START,
	WSRESP_PERIOD_FINISH,
	WSUBMIT_STATUS,
	WSUBMIT_DATE,
	WUPDATE_STATUS,
	WNUM_SECTIONS,
	WRESP_IS_MATCHING,
	WRESP_IS_NEW_RESPONSE,
	WRESP_IS_MERGED,
	WRESP_TEXT)
Select  WRESP_ID,
	WRES_ID,
	WSR_ID,
	WSRESP_PERIOD_START,
	WSRESP_PERIOD_FINISH,
	WSUBMIT_STATUS,
	WSUBMIT_DATE,
	WUPDATE_STATUS,
	WNUM_SECTIONS,
	CAST(WRESP_IS_MATCHING As tinyInt),
	CAST(WRESP_IS_NEW_RESPONSE As tinyInt),
	CAST(WRESP_IS_MERGED As tinyInt),
	WRESP_TEXT
FROM	dbo.MSP_WEB_STATUS_RESPONSES
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_STATUS_RESPONSES OFF
GO

DROP TABLE dbo.MSP_WEB_STATUS_RESPONSES
GO

execute sp_rename temp_MSP_WEB_STATUS_RESPONSES, MSP_WEB_STATUS_RESPONSES
GO



--
-- dbo.MSP_WEB_STRING_TYPES
--
-- no change
--



--
-- dbo.MSP_WEB_STS_SERVERS
--
-- new table
--
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_SERVER_WEB_SSL_PORT int NOT NULL DEFAULT 443,
	WSTS_WEB_PORT_IS_SSL tinyint NOT NULL DEFAULT 0,
	WSTS_ADMIN_PORT_IS_SSL tinyint NOT NULL DEFAULT 0,
	WSTS_DB_SERVER_NAME nvarchar(255) NOT NULL,
	WSTS_DB_NAME nvarchar(255) NOT NULL
)
GO



--
-- dbo.MSP_WEB_TRANSACTIONS
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_TRANSACTIONS
(
	WTRANS_ID int IDENTITY (1, 1) NOT NULL CONSTRAINT PK_MSP_WEB_TRANSACTIONS_WTRANS_ID PRIMARY KEY,
	WASSN_ID int NOT NULL,
	WTRANS_DATE datetime NULL,
	WTRANS_PROJ_UPDATE_DATE	datetime NULL,
	WTRANS_STATE int NOT NULL DEFAULT 0,
	WTRANS_TYPE int NOT NULL DEFAULT 0,
	WTRANS_ACTION int NOT NULL DEFAULT 0,
	WRES_ID_TRANS_SENDER int NULL,
	WDELEG_ID int NULL,
	WTRANS_DESC nvarchar(100) NULL
)
GO

CREATE INDEX IX_MSP_WEB_TRANSACTIONS_WASSN_ID
ON dbo.MSP_WEB_TRANSACTIONS(WASSN_ID)
GO



--
-- dbo.MSP_WEB_VIEW_CATEGORIES
--
-- bit to tinyint conversions
--
CREATE TABLE dbo.temp_MSP_WEB_VIEW_CATEGORIES
(
	WCAT_ID int IDENTITY (100, 1) NOT NULL CONSTRAINT PK_MSP_WEB_VIEW_CATEGORIES_WCAT_ID PRIMARY KEY NONCLUSTERED,
	WCAT_NAME nvarchar(255) NULL,
	WCAT_DESCRIPTION nvarchar(255) NULL,
	WCAT_INCL_ALL_PROJECTS tinyint NOT NULL DEFAULT 0 CHECK ((WCAT_INCL_ALL_PROJECTS >= 0) AND (WCAT_INCL_ALL_PROJECTS <= 1)),
	WCAT_INCL_TEAMASSIGN_PROJECTS tinyint NOT NULL DEFAULT 0 CHECK ((WCAT_INCL_TEAMASSIGN_PROJECTS >= 0) AND (WCAT_INCL_TEAMASSIGN_PROJECTS <= 1))
)
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_VIEW_CATEGORIES ON
GO

Insert Into dbo.temp_MSP_WEB_VIEW_CATEGORIES
	(WCAT_ID,
	WCAT_NAME,
	WCAT_DESCRIPTION,
	WCAT_INCL_ALL_PROJECTS,
	WCAT_INCL_TEAMASSIGN_PROJECTS)
Select  WCAT_ID,
	WCAT_NAME,
	WCAT_DESCRIPTION,
	cast(WCAT_INCL_ALL_PROJECTS as tinyint),
	cast(WCAT_INCL_TEAMASSIGN_PROJECTS as tinyint)
FROM	dbo.MSP_WEB_VIEW_CATEGORIES
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_VIEW_CATEGORIES OFF
GO

DROP TABLE dbo.MSP_WEB_VIEW_CATEGORIES
GO

execute sp_rename temp_MSP_WEB_VIEW_CATEGORIES, MSP_WEB_VIEW_CATEGORIES
GO



--
-- dbo.MSP_WEB_VIEW_DSNS
--
-- no change
--



--
-- dbo.MSP_WEB_VIEW_FAVORITES
--
-- new table
--
CREATE TABLE dbo.MSP_WEB_VIEW_FAVORITES
(
	WVIEW_FAV_ID int IDENTITY (1, 1) NOT NULL CONSTRAINT PK_MSP_WEB_VIEW_FAVORITES_WVIEW_FAV_ID PRIMARY KEY CLUSTERED,
	WRES_ID int NOT NULL,		
	WVIEW_TYPE int NOT NULL,
	WPROJ_ID int  NOT NULL, 	
	WVIEW_OWC_PIVOT_XML ntext NULL,
	WVIEW_OWC_CHART_XML ntext NULL,
	WVIEW_FAV_NAME nvarchar(100) NULL,
	WVIEW_FAV_URL ntext NULL,
	WVIEW_FAV_COLUMN_ORDER ntext NULL,
	WVIEW_FAV_AUTOFILTER ntext NULL,
	WSEC_MENU_ID int NOT NULL DEFAULT -99999, 
	WVIEW_DEFAULT_SETTINGS ntext NULL,
	WVIEW_TIMESTAMP uniqueidentifier NOT NULL DEFAULT ('{00000000-0000-0000-0000-000000000000}')
)
GO



--
-- dbo.MSP_WEB_VIEW_FIELDS
--
-- bit to tinyint conversions
--
CREATE TABLE dbo.temp_MSP_WEB_VIEW_FIELDS
(
	WFIELD_ID int NOT NULL CONSTRAINT PK_MSP_WEB_VIEW_FIELDS_WFIELD_ID PRIMARY KEY NONCLUSTERED,
	WFIELD_NAME_OLEDB nvarchar(255) NULL,
	WFIELD_NAME_SQL nvarchar(255) NULL,
	WFIELD_NAME_CONV_VALUE int NULL,
	WFIELD_TEXTCONV_TYPE int NULL,
	WTABLE_ID int NULL,
	WFIELD_IN_PROJECT_VIEW tinyint NOT NULL DEFAULT 0,
	WFIELD_IN_PORTFOLIO_VIEW tinyint NOT NULL DEFAULT 0,
	WFIELD_IN_WEBCLIENT_VIEW tinyint NOT NULL DEFAULT 0,
	WFIELD_IS_CUSTOM_FIELD tinyint NOT NULL DEFAULT 0,
	WFIELD_IS_GANTT_DEFAULT tinyint NOT NULL DEFAULT 0,
	WFIELD_IS_ENTRES_FIELD tinyint NOT NULL DEFAULT 0
)
GO

Insert Into dbo.temp_MSP_WEB_VIEW_FIELDS
	(WFIELD_ID,
	WFIELD_NAME_OLEDB,
	WFIELD_NAME_SQL,
	WFIELD_NAME_CONV_VALUE,
	WFIELD_TEXTCONV_TYPE,
	WTABLE_ID,
	WFIELD_IN_PROJECT_VIEW,
	WFIELD_IN_PORTFOLIO_VIEW,
	WFIELD_IN_WEBCLIENT_VIEW,
	WFIELD_IS_CUSTOM_FIELD,
	WFIELD_IS_GANTT_DEFAULT)
Select  WFIELD_ID,
	WFIELD_NAME_OLEDB,
	WFIELD_NAME_SQL,
	WFIELD_NAME_CONV_VALUE,
	WFIELD_TEXTCONV_TYPE,
	WTABLE_ID,
	cast(WFIELD_IN_PROJECT_VIEW as tinyint),
	cast(WFIELD_IN_PORTFOLIO_VIEW as tinyint),
	cast(WFIELD_IN_WEBCLIENT_VIEW as tinyint),
	cast(WFIELD_IS_CUSTOM_FIELD as tinyint),
	cast(WFIELD_IS_GANTT_DEFAULT as tinyint)
FROM	dbo.MSP_WEB_VIEW_FIELDS
GO

DROP TABLE dbo.MSP_WEB_VIEW_FIELDS
GO

execute sp_rename temp_MSP_WEB_VIEW_FIELDS, MSP_WEB_VIEW_FIELDS
GO



--
-- dbo.MSP_WEB_VIEW_PROJECTCATEGORIES
--
-- no change
--



--
-- dbo.MSP_WEB_VIEW_REPORTCATEGORIES
--
-- no change
--



--
-- dbo.MSP_WEB_VIEW_REPORTS
--
-- added columns
--
CREATE TABLE dbo.temp_MSP_WEB_VIEW_REPORTS
(
	WVIEW_ID int IDENTITY (100, 1) NOT NULL CONSTRAINT PK_MSP_WEB_VIEW_REPORTS_WVIEW_ID PRIMARY KEY NONCLUSTERED,
	WVIEW_NAME nvarchar(255) NOT NULL,
	WVIEW_DESCRIPTION nvarchar(255) NULL,
	WVIEW_TYPE int NULL,
	WVIEW_DISPLAY_TYPE int NULL,
	WVIEW_WORK_TYPE int NULL,
	WGANTT_SCHEME_ID int NULL,
	WTABLE_ID int NULL,
	WVIEW_FILTER_PARAM1 nvarchar(255) NULL,
	WVIEW_FILTER_PARAM2 nvarchar(255) NULL,
	WVIEW_FILTER_PARAM3 nvarchar(255) NULL,
	WVIEW_REPORT_KIND int NOT NULL DEFAULT 0,
	WVIEW_PATH nvarchar(1024) NULL,
	WVIEW_OWC_PIVOT_XML ntext NULL,
	WVIEW_OWC_CHART_XML ntext NULL,
	WGROUP_SCHEME_ID int NULL,
	WVIEW_DEFAULT_SETTINGS ntext NULL,
	WVIEW_TIMESTAMP uniqueidentifier NOT NULL DEFAULT ('{00000000-0000-0000-0000-000000000000}')
)
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_VIEW_REPORTS ON
GO

Insert Into dbo.temp_MSP_WEB_VIEW_REPORTS
	(WVIEW_ID,
	WVIEW_NAME,
	WVIEW_DESCRIPTION,
	WVIEW_TYPE,
	WVIEW_DISPLAY_TYPE,
	WVIEW_WORK_TYPE,
	WGANTT_SCHEME_ID,
	WTABLE_ID,
	WVIEW_FILTER_PARAM1,
	WVIEW_FILTER_PARAM2,
	WVIEW_FILTER_PARAM3,
	WVIEW_REPORT_KIND,
	WVIEW_PATH)
Select	WVIEW_ID,
	WVIEW_NAME,
	WVIEW_DESCRIPTION,
	WVIEW_TYPE,
	WVIEW_DISPLAY_TYPE,
	WVIEW_WORK_TYPE,
	WGANTT_SCHEME_ID,
	WTABLE_ID,
	WVIEW_FILTER_PARAM1,
	WVIEW_FILTER_PARAM2,
	WVIEW_FILTER_PARAM3,
	WVIEW_REPORT_KIND,
	WVIEW_PATH nvarchar
from	dbo.MSP_WEB_VIEW_REPORTS
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_VIEW_REPORTS OFF
GO

DROP TABLE dbo.MSP_WEB_VIEW_REPORTS
GO

execute sp_rename temp_MSP_WEB_VIEW_REPORTS, MSP_WEB_VIEW_REPORTS
GO



--
-- dbo.MSP_WEB_VIEW_REPORTS_FIELDS
--
-- no change
--



--
-- dbo.MSP_WEB_VIEW_RESOURCES
--
-- no change
--



--
-- dbo.MSP_WEB_VIEW_TABLES
--
-- no change
--




--
-- dbo.MSP_WEB_WORK
--
--columns added and removed, remove constraints first
--

ALTER TABLE dbo.MSP_WEB_WORK ADD
	WWORK_UPDATE_STATUS int NOT NULL DEFAULT 0,
	WWORK_READONLY tinyint NOT NULL DEFAULT 0
GO

-- drop default constraints before removing columns
declare @owner as sysname
declare @table as sysname
declare @constraint as sysname
declare @sql as nvarchar(1000)

declare const_list cursor for
select usr.name, tab.name, def.name
from sysobjects tab, syscolumns col, sysconstraints const, sysobjects def, sysusers as usr
where tab.name = 'MSP_WEB_WORK'
and col.name in ('WWORK_IS_SENT','WWORK_IS_SAVED')
and tab.id = const.id
and const.constid = def.id
and const.colid = col.colid
and col.id = tab.id
and tab.uid = usr.uid
and def.type = 'D'

open const_list

fetch next from const_list into @owner, @table, @constraint

while( @@fetch_status=0 )
begin
	select @sql = 'alter table ' + @table + ' drop constraint ' + @constraint
	--print @sql
	exec (@sql)
	fetch next from const_list into @owner, @table, @constraint
end

close const_list
deallocate const_list
GO

ALTER TABLE dbo.MSP_WEB_WORK
	DROP COLUMN WWORK_IS_SENT
GO

ALTER TABLE dbo.MSP_WEB_WORK
	DROP COLUMN WWORK_IS_SAVED
GO



--
-- dbo.MSP_WEB_WORKGROUP_FIELDS
--
-- bit to tinyInt conversion, and new column added
--
CREATE TABLE dbo.temp_MSP_WEB_WORKGROUP_FIELDS
(
	WASSN_ID int NOT NULL,
	FIELD_ID int NOT NULL,
	CUSTFIELD_TYPE int NULL,
	INT_VAL int NULL,
	DATE_VAL datetime NULL,
	DECIMAL_VAL decimal (25, 6) NULL,
	VARCHAR_VAL nvarchar(255) NULL,
	INDICATOR_VAL int NULL,
	DURATION_UNIT int NULL,
	WWORKGRP_UPDATE_STATUS int NOT NULL DEFAULT 0,
	WWORKGRP_INFO_IS_READONLY tinyint NOT NULL DEFAULT 0 CHECK ((WWORKGRP_INFO_IS_READONLY >= 0) AND (WWORKGRP_INFO_IS_READONLY <= 1))
	CONSTRAINT PK_MSP_WEB_WORKGROUP_FIELDS_WASSN_ID_FIELD_ID
	PRIMARY KEY CLUSTERED (WASSN_ID, FIELD_ID)
)
GO

Insert Into dbo.temp_MSP_WEB_WORKGROUP_FIELDS
	(WASSN_ID,
	FIELD_ID,
	CUSTFIELD_TYPE,
	INT_VAL,
	DATE_VAL,
	DECIMAL_VAL,
	VARCHAR_VAL,
	INDICATOR_VAL,
	DURATION_UNIT,
	WWORKGRP_INFO_IS_READONLY)
Select  WASSN_ID,
	FIELD_ID,
	CUSTFIELD_TYPE,
	INT_VAL,
	DATE_VAL,
	DECIMAL_VAL,
	VARCHAR_VAL,
	INDICATOR_VAL,
	DURATION_UNIT,
	CAST(WWORKGRP_INFO_IS_READONLY As tinyInt)
FROM	dbo.MSP_WEB_WORKGROUP_FIELDS
GO

DROP TABLE dbo.MSP_WEB_WORKGROUP_FIELDS
GO

execute sp_rename temp_MSP_WEB_WORKGROUP_FIELDS, MSP_WEB_WORKGROUP_FIELDS
GO



--
-- dbo.MSP_WEB_WORKGROUP_FIELDS_INFO
--
-- identity seed changed, columns added and removed
--
CREATE TABLE dbo.temp_MSP_WEB_WORKGROUP_FIELDS_INFO
(
	CUSTFIELD_INFO_ID int IDENTITY (10000, 1) NOT NULL CONSTRAINT PK_MSP_WEB_WORKGROUP_FIELDS_INFO_CUSTFIELD_INFO_ID PRIMARY KEY NONCLUSTERED,
	FIELD_ID int NULL,
	CUSTFIELD_NAME nvarchar(255) NULL,
	CONV_VALUE int NULL
)
GO

CREATE CLUSTERED INDEX I_CFINFO_FIELDID 
ON dbo.temp_MSP_WEB_WORKGROUP_FIELDS_INFO (FIELD_ID)
WITH FILLFACTOR = 40
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_WORKGROUP_FIELDS_INFO ON
GO

Insert Into dbo.temp_MSP_WEB_WORKGROUP_FIELDS_INFO
	(CUSTFIELD_INFO_ID,
	FIELD_ID,
	CUSTFIELD_NAME,
	CONV_VALUE)
Select	CUSTFIELD_INFO_ID + 10000,
	FIELD_ID,
	CUSTFIELD_NAME,
	-1
from	dbo.MSP_WEB_WORKGROUP_FIELDS_INFO
GO

SET IDENTITY_INSERT dbo.temp_MSP_WEB_WORKGROUP_FIELDS_INFO OFF
GO

DROP TABLE dbo.MSP_WEB_WORKGROUP_FIELDS_INFO
GO

execute sp_rename temp_MSP_WEB_WORKGROUP_FIELDS_INFO, MSP_WEB_WORKGROUP_FIELDS_INFO
GO



--  end of script  --
