05.05.00
/************************************************************/
/***** SqlDataProvider *****/
/***** *****/
/***** *****/
/***** Note: To manually execute this script you must *****/
/***** perform a search and replace operation *****/
/***** for {databaseOwner} and {objectQualifier} *****/
/***** *****/
/************************************************************/
/* Delete Orphaned Module*/
/*************************/
DELETE
FROM {databaseOwner}{objectQualifier}Modules
WHERE ModuleID IN (SELECT ModuleID FROM {databaseOwner}{objectQualifier}vw_modules WHERE TabModuleID IS NULL)
GO
/* Add UniqueId Column to Tabs Table */
/*************************************/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}Tabs' AND COLUMN_NAME='UniqueId')
BEGIN
-- Add new Column
ALTER TABLE {databaseOwner}{objectQualifier}Tabs
ADD UniqueId uniqueidentifier NOT NULL CONSTRAINT DF_{objectQualifier}Tabs_Guid DEFAULT newId()
ALTER TABLE {databaseOwner}{objectQualifier}Tabs
ADD CONSTRAINT IX_{objectQualifier}Tabs_UniqueId UNIQUE NONCLUSTERED
(
UniqueId
)
END
GO
/* Add VersionGuid Column to Tabs Table */
/****************************************/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}Tabs' AND COLUMN_NAME='VersionGuid')
BEGIN
-- Add new Column
ALTER TABLE {databaseOwner}{objectQualifier}Tabs
ADD VersionGuid uniqueidentifier NOT NULL CONSTRAINT DF_{objectQualifier}Tabs_VersionGuid DEFAULT newId()
END
GO
/* Add DefaultLanguageGuid Column to Tabs Table */
/************************************************/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}Tabs' AND COLUMN_NAME='DefaultLanguageGuid')
BEGIN
-- Add new Column
ALTER TABLE {databaseOwner}{objectQualifier}Tabs
ADD DefaultLanguageGuid uniqueidentifier NULL
END
GO
/* Add LocalizedVersionGuid Column to Tabs Table */
/*************************************************/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}Tabs' AND COLUMN_NAME='LocalizedVersionGuid')
BEGIN
-- Add new Column
ALTER TABLE {databaseOwner}{objectQualifier}Tabs
ADD LocalizedVersionGuid uniqueidentifier NOT NULL CONSTRAINT DF_{objectQualifier}Tabs_LocalizedVersionGuid DEFAULT newId()
END
GO
/* Update Tabs View to Include new Guids */
/*****************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}vw_Tabs]') AND OBJECTPROPERTY(id, N'IsVIEW') = 1)
DROP VIEW {databaseOwner}{objectQualifier}vw_Tabs
GO
CREATE VIEW {databaseOwner}{objectQualifier}vw_Tabs
AS
SELECT
T.TabID,
T.UniqueId,
T.VersionGuid,
T.DefaultLanguageGuid,
T.LocalizedVersionGuid,
T.TabOrder,
T.PortalID,
T.TabName,
T.IsVisible,
T.ParentId,
T.[Level],
CASE WHEN LEFT(LOWER(T.IconFile), 7) = 'fileid=' THEN
(SELECT Folder + FileName
FROM {databaseOwner}{objectQualifier}Files
WHERE fileid = CAST((RIGHT(LOWER(T.IconFile), Len(T.IconFile) - 7)) AS int)) ELSE T.IconFile END AS IconFile,
CASE WHEN LEFT(LOWER(T.IconFileLarge), 7) = 'fileid=' THEN
(SELECT Folder + FileName
FROM {databaseOwner}{objectQualifier}Files
WHERE fileid = CAST((RIGHT(LOWER(T.IconFileLarge), Len(T.IconFileLarge) - 7)) AS int)) ELSE T.IconFileLarge END AS IconFileLarge,
T.DisableLink,
T.Title,
T.Description,
T.KeyWords,
T.IsDeleted,
T.SkinSrc,
T.ContainerSrc,
T.TabPath,
T.StartDate,
T.EndDate,
T.Url,
CASE WHEN EXISTS (SELECT 1 FROM {databaseOwner}{objectQualifier}Tabs T2 WHERE T2.ParentId = T.TabId) THEN 'true' ELSE 'false' END AS HasChildren,
T.RefreshInterval,
T.PageHeadText,
T.IsSecure,
T.PermanentRedirect,
T.SiteMapPriority,
CI.ContentItemID,
CI.Content,
CI.ContentTypeID,
CI.ModuleID,
CI.ContentKey,
CI.Indexed,
T.CultureCode,
T.CreatedByUserID,
T.CreatedOnDate,
T.LastModifiedByUserID,
T.LastModifiedOnDate
FROM {databaseOwner}{objectQualifier}Tabs AS T
LEFT OUTER JOIN {databaseOwner}{objectQualifier}ContentItems AS CI ON T.ContentItemID = CI.ContentItemID
GO
/**********************************************************/
/* Adding UniqueId and VersionGuid Columns to Files Table */
/**********************************************************/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}Files' AND COLUMN_NAME='UniqueId')
BEGIN
-- Add a new UniqueId Column
ALTER TABLE {databaseOwner}{objectQualifier}Files
ADD UniqueId UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_{objectQualifier}Files_UniqueId DEFAULT NEWID()
ALTER TABLE {databaseOwner}{objectQualifier}Files
ADD CONSTRAINT IX_{objectQualifier}Files_UniqueId UNIQUE NONCLUSTERED
(
UniqueId
)
END
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}Files' AND COLUMN_NAME='VersionGuid')
BEGIN
-- Add a new VersionGuid Column
ALTER TABLE {databaseOwner}{objectQualifier}Files
ADD VersionGuid UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_{objectQualifier}Files_VersionGuid DEFAULT NEWID()
END
GO
/**********************************************************/
/* Adding SHA1Hash Column to Files Table */
/**********************************************************/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}Files' AND COLUMN_NAME='SHA1Hash')
BEGIN
-- Add a new SHA1Hash Column
ALTER TABLE {databaseOwner}{objectQualifier}Files
ADD SHA1Hash varchar(40)
END
GO
/**********************************************************/
/* Adding UniqueId and VersionGuid Columns to Folders Table */
/**********************************************************/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}Folders' AND COLUMN_NAME='UniqueId')
BEGIN
-- Add a new UniqueId Column
ALTER TABLE {databaseOwner}{objectQualifier}Folders
ADD UniqueId UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_{objectQualifier}Folders_UniqueId DEFAULT NEWID()
ALTER TABLE {databaseOwner}{objectQualifier}Folders
ADD CONSTRAINT IX_{objectQualifier}Folders_UniqueId UNIQUE NONCLUSTERED
(
UniqueId
)
END
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}Folders' AND COLUMN_NAME='VersionGuid')
BEGIN
-- Add a new VersionGuid Column
ALTER TABLE {databaseOwner}{objectQualifier}Folders
ADD VersionGuid UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_{objectQualifier}Folders_VersionGuid DEFAULT NEWID()
END
GO
/**********************************************************/
/* Modify AddFile SP */
/**********************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}AddFile]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}AddFile
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}AddFile
@PortalId int,
@UniqueId uniqueidentifier,
@VersionGuid uniqueidentifier,
@FileName nvarchar(100),
@Extension nvarchar(100),
@Size int,
@Width int,
@Height int,
@ContentType nvarchar(200),
@Folder nvarchar(200),
@FolderID int,
@CreatedByUserID int,
@Hash varchar(40)
AS
DECLARE @FileID int
SELECT @FileId = FileID FROM {databaseOwner}{objectQualifier}Files WHERE FolderID = @FolderID AND FileName = @FileName
IF @FileID IS Null
BEGIN
INSERT INTO {databaseOwner}{objectQualifier}Files (
PortalId,
UniqueId,
VersionGuid,
FileName,
Extension,
Size,
Width,
Height,
ContentType,
Folder,
FolderID,
CreatedByUserID,
CreatedOnDate,
LastModifiedByUserID,
LastModifiedOnDate,
SHA1Hash
)
VALUES (
@PortalId,
@UniqueId,
@VersionGuid,
@FileName,
@Extension,
@Size,
@Width,
@Height,
@ContentType,
@Folder,
@FolderID,
@CreatedByUserID,
getdate(),
@CreatedByUserID,
getdate(),
@Hash
)
SELECT @FileID = SCOPE_IDENTITY()
END
ELSE
BEGIN
UPDATE {databaseOwner}{objectQualifier}Files
SET FileName = @FileName,
VersionGuid = @VersionGuid,
Extension = @Extension,
Size = @Size,
Width = @Width,
Height = @Height,
ContentType = @ContentType,
Folder = @Folder,
FolderID = @FolderID,
LastModifiedByUserID = @CreatedByUserID,
LastModifiedOnDate = getdate(),
SHA1Hash = @Hash
WHERE FileId = @FileID
END
SELECT @FileID
GO
/**********************************************************/
/* Modify UpdateFile SP */
/**********************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UpdateFile]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}UpdateFile
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdateFile]
@FileId int,
@VersionGuid uniqueidentifier,
@FileName nvarchar(100),
@Extension nvarchar(100),
@Size int,
@Width int,
@Height int,
@ContentType nvarchar(200),
@Folder nvarchar(200),
@FolderID int,
@LastModifiedByUserID int,
@Hash varchar(40)
AS
UPDATE {databaseOwner}{objectQualifier}Files
SET FileName = @FileName,
VersionGuid = @VersionGuid,
Extension = @Extension,
Size = @Size,
Width = @Width,
Height = @Height,
ContentType = @ContentType,
Folder = @Folder,
FolderID = @FolderID,
LastModifiedByUserID = @LastModifiedByUserID,
LastModifiedOnDate = getdate(),
SHA1Hash = @Hash
WHERE FileId = @FileId
GO
/**********************************************************/
/* Modify AddFolder SP */
/**********************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}AddFolder]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}AddFolder
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}AddFolder]
@PortalID int,
@UniqueId uniqueidentifier,
@VersionGuid uniqueidentifier,
@FolderPath varchar(300),
@StorageLocation int,
@IsProtected bit,
@IsCached bit,
@LastUpdated datetime,
@CreatedByUserID int
AS
INSERT INTO {databaseOwner}{objectQualifier}Folders (
PortalID,
UniqueId,
VersionGuid,
FolderPath,
StorageLocation,
IsProtected,
IsCached,
LastUpdated,
CreatedByUserID,
CreatedOnDate,
LastModifiedByUserID,
LastModifiedOnDate
)
VALUES (
@PortalID,
@UniqueId,
@VersionGuid,
@FolderPath,
@StorageLocation,
@IsProtected,
@IsCached,
@LastUpdated,
@CreatedByUserID,
getdate(),
@CreatedByUserID,
getdate()
)
SELECT SCOPE_IDENTITY()
GO
/**********************************************************/
/* Modify UpdateFolder SP */
/**********************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UpdateFolder]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}UpdateFolder
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdateFolder]
@PortalID int,
@VersionGuid uniqueidentifier,
@FolderID int,
@FolderPath varchar(300),
@StorageLocation int,
@IsProtected bit,
@IsCached bit,
@LastUpdated datetime,
@LastModifiedByUserID int
AS
UPDATE {databaseOwner}{objectQualifier}Folders
SET FolderPath = @FolderPath,
VersionGuid = @VersionGuid,
StorageLocation = @StorageLocation,
IsProtected = @IsProtected,
IsCached = @IsCached,
LastUpdated = @LastUpdated,
LastModifiedByUserID = @LastModifiedByUserID,
LastModifiedOnDate = getdate()
WHERE ((PortalID = @PortalID) OR (PortalID IS Null AND @PortalID IS Null)) AND FolderID = @FolderID
GO
/* Modify AddTab */
/*****************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}AddTab]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}AddTab
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}AddTab]
@ContentItemID int,
@PortalID int,
@UniqueId uniqueidentifier,
@VersionGuid uniqueidentifier,
@DefaultLanguageGuid uniqueidentifier,
@LocalizedVersionGuid uniqueidentifier,
@TabName nvarchar(50),
@IsVisible bit,
@DisableLink bit,
@ParentId int,
@IconFile nvarchar(100),
@IconFileLarge nvarchar(100),
@Title nvarchar(200),
@Description nvarchar(500),
@KeyWords nvarchar(500),
@Url nvarchar(255),
@SkinSrc nvarchar(200),
@ContainerSrc nvarchar(200),
@TabPath nvarchar(255),
@StartDate datetime,
@EndDate datetime,
@RefreshInterval int,
@PageHeadText nvarchar(500),
@IsSecure bit,
@PermanentRedirect bit,
@SiteMapPriority float,
@CreatedByUserID int,
@CultureCode nvarchar(50)
AS
INSERT INTO {databaseOwner}{objectQualifier}Tabs (
ContentItemID,
PortalID,
UniqueId,
VersionGuid,
DefaultLanguageGuid,
LocalizedVersionGuid,
TabName,
IsVisible,
DisableLink,
ParentId,
IconFile,
IconFileLarge,
Title,
Description,
KeyWords,
IsDeleted,
Url,
SkinSrc,
ContainerSrc,
TabPath,
StartDate,
EndDate,
RefreshInterval,
PageHeadText,
IsSecure,
PermanentRedirect,
SiteMapPriority,
CreatedByUserID,
CreatedOnDate,
LastModifiedByUserID,
LastModifiedOnDate,
CultureCode
)
VALUES (
@ContentItemID,
@PortalID,
@UniqueId,
@VersionGuid,
@DefaultLanguageGuid,
@LocalizedVersionGuid,
@TabName,
@IsVisible,
@DisableLink,
@ParentId,
@IconFile,
@IconFileLarge,
@Title,
@Description,
@KeyWords,
0,
@Url,
@SkinSrc,
@ContainerSrc,
@TabPath,
@StartDate,
@EndDate,
@RefreshInterval,
@PageHeadText,
@IsSecure,
@PermanentRedirect,
@SiteMapPriority,
@CreatedByUserID,
getdate(),
@CreatedByUserID,
getdate(),
@CultureCode
)
SELECT SCOPE_IDENTITY()
GO
/* Modify UpdateTab */
/********************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UpdateTab]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}UpdateTab
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdateTab]
@TabId int,
@ContentItemID int,
@PortalId int,
@VersionGuid uniqueidentifier,
@DefaultLanguageGuid uniqueidentifier,
@LocalizedVersionGuid uniqueidentifier,
@TabName nvarchar(50),
@IsVisible bit,
@DisableLink bit,
@ParentId int,
@IconFile nvarchar(100),
@IconFileLarge nvarchar(100),
@Title nvarchar(200),
@Description nvarchar(500),
@KeyWords nvarchar(500),
@IsDeleted bit,
@Url nvarchar(255),
@SkinSrc nvarchar(200),
@ContainerSrc nvarchar(200),
@TabPath nvarchar(255),
@StartDate datetime,
@EndDate datetime,
@RefreshInterval int,
@PageHeadText nvarchar(500),
@IsSecure bit,
@PermanentRedirect bit,
@SiteMapPriority float,
@LastModifiedByUserID int,
@CultureCode nvarchar(50)
AS
UPDATE {databaseOwner}{objectQualifier}Tabs
SET
ContentItemID = @ContentItemID,
PortalId = @PortalId,
VersionGuid = @VersionGuid,
DefaultLanguageGuid = @DefaultLanguageGuid,
LocalizedVersionGuid = @LocalizedVersionGuid,
TabName = @TabName,
IsVisible = @IsVisible,
DisableLink = @DisableLink,
ParentId = @ParentId,
IconFile = @IconFile,
IconFileLarge = @IconFileLarge,
Title = @Title,
Description = @Description,
KeyWords = @KeyWords,
IsDeleted = @IsDeleted,
Url = @Url,
SkinSrc = @SkinSrc,
ContainerSrc = @ContainerSrc,
TabPath = @TabPath,
StartDate = @StartDate,
EndDate = @EndDate,
RefreshInterval = @RefreshInterval,
PageHeadText = @PageHeadText,
IsSecure = @IsSecure,
PermanentRedirect = @PermanentRedirect,
SiteMapPriority = @SiteMapPriority,
LastModifiedByUserID = @LastModifiedByUserID,
LastModifiedOnDate = getdate(),
CultureCode = @CultureCode
WHERE TabId = @TabId
GO
/* Add UpdateTabVersion */
/************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UpdateTabVersion]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}UpdateTabVersion
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdateTabVersion]
@TabID int,
@VersionGuid uniqueidentifier
AS
UPDATE {databaseOwner}{objectQualifier}Tabs
SET VersionGuid = @VersionGuid
WHERE TabID = @TabID
GO
/* Add IsPublished Column to PortalLanguages Table */
/***************************************************/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}PortalLanguages' AND COLUMN_NAME='IsPublished')
BEGIN
-- Add new Column
ALTER TABLE {databaseOwner}{objectQualifier}PortalLanguages
ADD IsPublished bit NOT NULL CONSTRAINT DF_{objectQualifier}PortalLanguages_IsPublished DEFAULT ((0))
END
GO
/* Modify AddPortalLanguage */
/****************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}AddPortalLanguage]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}AddPortalLanguage
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}AddPortalLanguage]
@PortalId int,
@LanguageId int,
@IsPublished bit,
@CreatedByUserID int
AS
INSERT INTO {databaseOwner}{objectQualifier}PortalLanguages (
PortalId,
LanguageId,
IsPublished,
[CreatedByUserID],
[CreatedOnDate],
[LastModifiedByUserID],
[LastModifiedOnDate]
)
VALUES (
@PortalId,
@LanguageId,
@IsPublished,
@CreatedByUserID,
getdate(),
@CreatedByUserID,
getdate()
)
SELECT SCOPE_IDENTITY()
GO
/* Modify GetLanguagesByPortal */
/*******************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetLanguagesByPortal]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetLanguagesByPortal
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetLanguagesByPortal]
@PortalId int
AS
SELECT
L.*,
PL.PortalId,
PL.IsPublished
FROM {databaseOwner}{objectQualifier}Languages L
INNER JOIN {databaseOwner}{objectQualifier}PortalLanguages PL On L.LanguageID = PL.LanguageID
WHERE PL.PortalID = @PortalID
GO
/* Create UpdatePortalLanguage */
/*******************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UpdatePortalLanguage]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}UpdatePortalLanguage
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdatePortalLanguage]
@PortalId int,
@LanguageId int,
@IsPublished bit,
@LastModifiedByUserID int
AS
UPDATE {databaseOwner}{objectQualifier}PortalLanguages
SET
IsPublished = @IsPublished,
LastModifiedByUserID = @LastModifiedByUserID,
LastModifiedOnDate = getdate()
WHERE PortalId = @PortalId
AND LanguageId = @LanguageId
GO
/* Copy ModuleTitle, Header, Footer to TabModule table */
/*******************************************************/
-- this was done in 5.2.0 script - but any modules added since would not be updated - so redo
UPDATE {databaseOwner}{objectQualifier}TabModules
SET ModuleTitle = M.ModuleTitle,
Header = M.Header,
Footer = M.Footer
FROM {databaseOwner}{objectQualifier}Modules M
INNER JOIN {databaseOwner}{objectQualifier}TabModules TM ON M.ModuleID = TM.ModuleID
GO
/* Add CultureCode Column to Tabmodules Table */
/**********************************************/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}TabModules' AND COLUMN_NAME='CultureCode')
BEGIN
-- Add new Column
ALTER TABLE {databaseOwner}{objectQualifier}TabModules
ADD CultureCode nvarchar(10) NULL
END
GO
/* Add UniqueId Column to Tabmodules Table */
/******************************************/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}TabModules' AND COLUMN_NAME='UniqueId')
BEGIN
-- Add new Column
ALTER TABLE {databaseOwner}{objectQualifier}TabModules
ADD UniqueId uniqueidentifier NOT NULL CONSTRAINT DF_{objectQualifier}TabModules_Guid DEFAULT newId()
ALTER TABLE {databaseOwner}{objectQualifier}TabModules
ADD CONSTRAINT IX_{objectQualifier}TabModules_UniqueId UNIQUE NONCLUSTERED
(
UniqueId
)
END
GO
/* Add VersionGuid Column to Tabmodules Table */
/*********************************************/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}TabModules' AND COLUMN_NAME='VersionGuid')
BEGIN
-- Add new Column
ALTER TABLE {databaseOwner}{objectQualifier}TabModules
ADD VersionGuid uniqueidentifier NOT NULL CONSTRAINT DF_{objectQualifier}TabModules_VersionGuid DEFAULT newId()
END
GO
/* Add DefaultLanguageGuid Column to Tabmodules Table */
/*****************************************************/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}TabModules' AND COLUMN_NAME='DefaultLanguageGuid')
BEGIN
-- Add new Column
ALTER TABLE {databaseOwner}{objectQualifier}TabModules
ADD DefaultLanguageGuid uniqueidentifier NULL
END
GO
/* Add LocalizedVersionGuid Column to Tabmodules Table */
/*******************************************************/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}TabModules' AND COLUMN_NAME='LocalizedVersionGuid')
BEGIN
-- Add new Column
ALTER TABLE {databaseOwner}{objectQualifier}TabModules
ADD LocalizedVersionGuid uniqueidentifier NOT NULL CONSTRAINT DF_{objectQualifier}TabModules_LocalizedVersionGuid DEFAULT newId()
END
GO
/* Update Modules View */
/***********************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}vw_Modules]') AND OBJECTPROPERTY(id, N'IsVIEW') = 1)
DROP VIEW {databaseOwner}{objectQualifier}vw_Modules
GO
CREATE VIEW {databaseOwner}{objectQualifier}vw_Modules
AS
SELECT
M.PortalID,
TM.TabID,
TM.TabModuleID,
M.ModuleID,
M.ModuleDefID,
TM.ModuleOrder,
TM.PaneName,
TM.ModuleTitle,
TM.CacheTime,
TM.CacheMethod,
TM.Alignment,
TM.Color,
TM.Border,
CASE WHEN LEFT(LOWER(TM.IconFile), 6) = 'fileid' THEN (SELECT Folder + FileName FROM {databaseOwner}{objectQualifier}Files WHERE 'fileid=' + CONVERT(varchar, {databaseOwner}{objectQualifier}Files.FileID) = TM.IconFile) ELSE TM.IconFile END AS IconFile,
M.AllTabs,
TM.Visibility,
TM.IsDeleted,
TM.Header,
TM.Footer,
M.StartDate,
M.EndDate,
TM.ContainerSrc,
TM.DisplayTitle,
TM.DisplayPrint,
TM.DisplaySyndicate,
TM.IsWebSlice,
TM.WebSliceTitle,
TM.WebSliceExpiryDate,
TM.WebSliceTTL,
M.InheritViewPermissions,
MD.DesktopModuleID,
MD.DefaultCacheTime,
MC.ModuleControlID,
DM.BusinessControllerClass,
DM.IsAdmin,
DM.SupportedFeatures,
CI.ContentItemID,
CI.Content,
CI.ContentTypeID,
CI.ContentKey,
CI.Indexed,
M.CreatedByUserID,
M.CreatedOnDate,
M.LastModifiedByUserID,
M.LastModifiedOnDate,
TM.UniqueId,
TM.VersionGuid,
TM.DefaultLanguageGuid,
TM.LocalizedVersionGuid,
TM.CultureCode
FROM {databaseOwner}{objectQualifier}ModuleDefinitions AS MD
INNER JOIN {databaseOwner}{objectQualifier}Modules AS M ON MD.ModuleDefID = M.ModuleDefID
INNER JOIN {databaseOwner}{objectQualifier}ModuleControls AS MC ON MD.ModuleDefID = MC.ModuleDefID
INNER JOIN {databaseOwner}{objectQualifier}DesktopModules AS DM ON MD.DesktopModuleID = DM.DesktopModuleID
LEFT OUTER JOIN {databaseOwner}{objectQualifier}ContentItems AS CI ON M.ContentItemID = CI.ContentItemID
LEFT OUTER JOIN {databaseOwner}{objectQualifier}TabModules AS TM ON M.ModuleID = TM.ModuleID
WHERE (MC.ControlKey IS NULL)
GO
/* Update AddTabModule */
/************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}AddTabModule]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}AddTabModule
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}AddTabModule]
@TabId int,
@ModuleId int,
@ModuleTitle nvarchar(256),
@Header ntext,
@Footer ntext,
@ModuleOrder int,
@PaneName nvarchar(50),
@CacheTime int,
@CacheMethod varchar(50),
@Alignment nvarchar(10),
@Color nvarchar(20),
@Border nvarchar(1),
@IconFile nvarchar(100),
@Visibility int,
@ContainerSrc nvarchar(200),
@DisplayTitle bit,
@DisplayPrint bit,
@DisplaySyndicate bit,
@IsWebSlice bit,
@WebSliceTitle nvarchar(256),
@WebSliceExpiryDate datetime,
@WebSliceTTL int,
@UniqueId uniqueidentifier,
@VersionGuid uniqueidentifier,
@DefaultLanguageGuid uniqueidentifier,
@LocalizedVersionGuid uniqueidentifier,
@CultureCode nvarchar(10),
@CreatedByUserID int
AS
INSERT INTO {databaseOwner}{objectQualifier}TabModules (
TabId,
ModuleId,
ModuleTitle,
Header,
Footer,
ModuleOrder,
PaneName,
CacheTime,
CacheMethod,
Alignment,
Color,
Border,
IconFile,
Visibility,
ContainerSrc,
DisplayTitle,
DisplayPrint,
DisplaySyndicate,
IsWebSlice,
WebSliceTitle,
WebSliceExpiryDate,
WebSliceTTL,
UniqueId,
VersionGuid,
DefaultLanguageGuid,
LocalizedVersionGuid,
CultureCode,
CreatedByUserID,
CreatedOnDate,
LastModifiedByUserID,
LastModifiedOnDate
)
VALUES (
@TabId,
@ModuleId,
@ModuleTitle,
@Header,
@Footer,
@ModuleOrder,
@PaneName,
@CacheTime,
@CacheMethod,
@Alignment,
@Color,
@Border,
@IconFile,
@Visibility,
@ContainerSrc,
@DisplayTitle,
@DisplayPrint,
@DisplaySyndicate,
@IsWebSlice,
@WebSliceTitle,
@WebSliceExpiryDate,
@WebSliceTTL,
@UniqueId,
@VersionGuid,
@DefaultLanguageGuid,
@LocalizedVersionGuid,
@CultureCode,
@CreatedByUserID,
getdate(),
@CreatedByUserID,
getdate()
)
GO
/* Update DeleteTabModule */
/***************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}DeleteTabModule]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DeleteTabModule
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}DeleteTabModule]
@TabId int,
@ModuleId int,
@SoftDelete bit
AS
IF @SoftDelete = 1
UPDATE {databaseOwner}{objectQualifier}TabModules
SET IsDeleted = 1,
VersionGuid = newId()
WHERE TabId = @TabId
AND ModuleId = @ModuleId
ELSE
DELETE
FROM {databaseOwner}{objectQualifier}TabModules
WHERE TabId = @TabId
AND ModuleId = @ModuleId
GO
/* Update RestoreTabModule */
/***************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}RestoreTabModule]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}RestoreTabModule
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}RestoreTabModule]
@TabId int,
@ModuleId int
AS
UPDATE {databaseOwner}{objectQualifier}TabModules
SET IsDeleted = 0,
VersionGuid = newId()
WHERE TabId = @TabId
AND ModuleId = @ModuleId
GO
/* Update UpdateModuleOrder */
/***************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UpdateModuleOrder]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}UpdateModuleOrder
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdateModuleOrder]
@TabId int,
@ModuleId int,
@ModuleOrder int,
@PaneName nvarchar(50)
AS
UPDATE {databaseOwner}{objectQualifier}TabModules
SET ModuleOrder = @ModuleOrder,
PaneName = @PaneName,
VersionGuid = newId()
WHERE TabId = @TabId
AND ModuleId = @ModuleId
GO
/* Update UpdateTabModule */
/**************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UpdateTabModule]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}UpdateTabModule
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdateTabModule]
@TabModuleId int,
@TabId int,
@ModuleId int,
@ModuleTitle nvarchar(256),
@Header ntext,
@Footer ntext,
@ModuleOrder int,
@PaneName nvarchar(50),
@CacheTime int,
@CacheMethod varchar(50),
@Alignment nvarchar(10),
@Color nvarchar(20),
@Border nvarchar(1),
@IconFile nvarchar(100),
@Visibility int,
@ContainerSrc nvarchar(200),
@DisplayTitle bit,
@DisplayPrint bit,
@DisplaySyndicate bit,
@IsWebSlice bit,
@WebSliceTitle nvarchar(256),
@WebSliceExpiryDate datetime,
@WebSliceTTL int,
@VersionGuid uniqueidentifier,
@DefaultLanguageGuid uniqueidentifier,
@LocalizedVersionGuid uniqueidentifier,
@CultureCode nvarchar(10),
@LastModifiedByUserID int
AS
UPDATE {databaseOwner}{objectQualifier}TabModules
SET
TabId = @TabId,
ModuleId = @ModuleId,
ModuleTitle = @ModuleTitle,
Header = @Header,
Footer = @Footer,
ModuleOrder = @ModuleOrder,
PaneName = @PaneName,
CacheTime = @CacheTime,
CacheMethod = @CacheMethod,
Alignment = @Alignment,
Color = @Color,
Border = @Border,
IconFile = @IconFile,
Visibility = @Visibility,
ContainerSrc = @ContainerSrc,
DisplayTitle = @DisplayTitle,
DisplayPrint = @DisplayPrint,
DisplaySyndicate = @DisplaySyndicate,
IsWebSlice = @IsWebSlice,
WebSliceTitle = @WebSliceTitle,
WebSliceExpiryDate = @WebSliceExpiryDate,
WebSliceTTL = @WebSliceTTL,
VersionGuid = @VersionGuid,
DefaultLanguageGuid = @DefaultLanguageGuid,
LocalizedVersionGuid = @LocalizedVersionGuid,
CultureCode= @CultureCode,
LastModifiedByUserID = @LastModifiedByUserID,
LastModifiedOnDate = getdate()
WHERE TabModuleId = @TabModuleId
GO
/* Add UpdateTabModuleVersion SP */
/**********************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UpdateTabModuleVersion]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}UpdateTabModuleVersion
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdateTabModuleVersion]
@TabModuleID int,
@VersionGuid uniqueidentifier
AS
UPDATE {databaseOwner}{objectQualifier}TabModules
SET VersionGuid = @VersionGuid
WHERE TabModuleID = @TabModuleID
GO
/* Add GetTabModule SP */
/**********************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetTabModule]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetTabModule
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetTabModule]
@TabModuleID int
AS
SELECT *
FROM {databaseOwner}{objectQualifier}vw_Modules
WHERE TabModuleID = @TabModuleID
GO
/* Add GetAllTabsModulesByModuleID SP */
/**********************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetAllTabsModulesByModuleID]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetAllTabsModulesByModuleID
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetAllTabsModulesByModuleID]
@ModuleID int
AS
SELECT *
FROM {databaseOwner}{objectQualifier}vw_Modules
WHERE ModuleID = @ModuleID
GO
/* Add EnsureLocalizationExists Procedure */
/******************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}EnsureLocalizationExists]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}EnsureLocalizationExists
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}EnsureLocalizationExists
@PortalId int,
@CultureCode nvarchar(10)
AS
IF NOT EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}PortalLocalization WHERE CultureCode=@CultureCode AND Portalid=@PortalId)
BEGIN
DECLARE @PortalName nvarchar(128)
DECLARE @LogoFile nvarchar(50)
DECLARE @FooterText nvarchar(100)
DECLARE @Description nvarchar(500)
DECLARE @KeyWords nvarchar(500)
DECLARE @BackgroundFile nvarchar(50)
DECLARE @HomeTabId int
DECLARE @LoginTabId int
DECLARE @UserTabId int
DECLARE @AdminTabId int
DECLARE @RegisterTabId int
DECLARE @defaultlanguage nvarchar(10)
SELECT @defaultlanguage= DefaultLanguage FROM {databaseOwner}{objectQualifier}Portals WHERE Portalid=@PortalId
IF EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}PortalLocalization WHERE CultureCode=@defaultlanguage AND Portalid=@PortalId)
BEGIN
--clone the default language
SELECT
@PortalName = PortalName,
@LogoFile = LogoFile,
@FooterText = FooterText,
@Description = Description,
@KeyWords = KeyWords,
@BackgroundFile = BackgroundFile,
@HomeTabId = HomeTabId,
@LoginTabId = LoginTabId,
@UserTabId = UserTabId,
@AdminTabId = AdminTabId,
@RegisterTabId = RegisterTabId
FROM {databaseOwner}{objectQualifier}PortalLocalization WHERE CultureCode=@defaultlanguage AND Portalid=@PortalId
INSERT INTO {databaseOwner}{objectQualifier}PortalLocalization (
PortalId,
CultureCode,
PortalName,
LogoFile,
FooterText,
Description,
KeyWords,
BackgroundFile,
HomeTabId,
LoginTabId,
UserTabId,
AdminTabId,
RegisterTabId,
CreatedByUserID,
CreatedOnDate,
LastModifiedByUserID,
LastModifiedOnDate
)
VALUES (
@PortalId,
@CultureCode,
@PortalName,
@LogoFile,
@FooterText,
@Description,
@KeyWords,
@BackgroundFile,
@HomeTabId,
@LoginTabId,
@UserTabId,
@AdminTabId,
@RegisterTabId,
-1,
getdate(),
-1,
getdate()
)
END
ELSE
BEGIN
IF EXISTS (select * from {databaseOwner}{objectQualifier}PortalLocalization where CultureCode='en-us' and Portalid=@PortalId)
BEGIN
--if defaultlanguage is missing clone en-us if available
SELECT
@PortalName = PortalName,
@LogoFile = LogoFile,
@FooterText = FooterText,
@Description = Description,
@KeyWords = KeyWords,
@BackgroundFile = BackgroundFile,
@HomeTabId = HomeTabId,
@LoginTabId=LoginTabId,
@UserTabId=UserTabId,
@AdminTabId=AdminTabId,
@RegisterTabId=RegisterTabId
FROM {databaseOwner}{objectQualifier}PortalLocalization where CultureCode='en-us' and Portalid=@PortalId
INSERT INTO {databaseOwner}{objectQualifier}PortalLocalization (
PortalId,
CultureCode,
PortalName,
LogoFile,
FooterText,
Description,
KeyWords,
BackgroundFile,
HomeTabId,
LoginTabId,
UserTabId,
AdminTabId,
RegisterTabId,
CreatedByUserID,
CreatedOnDate,
LastModifiedByUserID,
LastModifiedOnDate
)
VALUES (
@PortalId,
@CultureCode,
@PortalName,
@LogoFile,
@FooterText,
@Description,
@KeyWords,
@BackgroundFile,
@HomeTabId,
@LoginTabId,
@UserTabId,
@AdminTabId,
@RegisterTabId,
-1,
getdate(),
-1,
getdate()
)
END
ELSE
BEGIN
IF EXISTS (select top 1 * from {databaseOwner}{objectQualifier}PortalLocalization where Portalid=@PortalId)
BEGIN
--cannot select by particular culturecode as defaultlanguage has been deleted (possible in early versions of DotNetNuke)
SELECT TOP 1
@PortalName = PortalName,
@LogoFile = LogoFile,
@FooterText = FooterText,
@Description = Description,
@KeyWords = KeyWords,
@BackgroundFile = BackgroundFile,
@HomeTabId = HomeTabId,
@LoginTabId=LoginTabId,
@UserTabId=UserTabId,
@AdminTabId=AdminTabId,
@RegisterTabId=RegisterTabId
FROM {databaseOwner}{objectQualifier}PortalLocalization where portalid=@PortalId
INSERT INTO {databaseOwner}{objectQualifier}PortalLocalization (
PortalId,
CultureCode,
PortalName,
LogoFile,
FooterText,
Description,
KeyWords,
BackgroundFile,
HomeTabId,
LoginTabId,
UserTabId,
AdminTabId,
RegisterTabId,
CreatedByUserID,
CreatedOnDate,
LastModifiedByUserID,
LastModifiedOnDate
)
VALUES (
@PortalId,
@CultureCode,
@PortalName,
@LogoFile,
@FooterText,
@Description,
@KeyWords,
@BackgroundFile,
@HomeTabId,
@LoginTabId,
@UserTabId,
@AdminTabId,
@RegisterTabId,
-1,
getdate(),
-1,
getdate()
)
END
END
END
END
GO
/* Fix GetHostSettings SP */
/**********************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetHostSettings]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetHostSettings
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetHostSettings]
AS
IF NOT EXISTS ( select 1 from {databaseOwner}{objectQualifier}HostSettings where SettingName = 'GUID' )
INSERT INTO {databaseOwner}{objectQualifier}HostSettings ( SettingName, SettingValue, SettingIsSecure ) values ( 'GUID', newid(), 0 )
SELECT SettingName,
SettingValue,
SettingIsSecure,
CreatedByUserID,
CreatedOnDate,
LastModifiedByUserID,
LastModifiedOnDate
FROM {databaseOwner}{objectQualifier}HostSettings
GO
/* Add GetModuleByUniqueID SP */
/**********************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetModuleByUniqueID]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetModuleByUniqueID
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetModuleByUniqueID]
@UniqueID uniqueidentifier
AS
SELECT *
FROM {databaseOwner}{objectQualifier}vw_Modules
WHERE UniqueID = @UniqueID
GO
/* Add GetTabByUniqueID SP */
/**********************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetTabByUniqueID]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetTabByUniqueID
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetTabByUniqueID]
@UniqueID uniqueidentifier
AS
SELECT *
FROM {databaseOwner}{objectQualifier}vw_Tabs
WHERE UniqueID = @UniqueID
GO
/* Add UpdateFileVersion SP */
/**********************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UpdateFileVersion]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}UpdateFileVersion
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdateFileVersion]
@FileID int,
@VersionGuid uniqueidentifier
AS
UPDATE {databaseOwner}{objectQualifier}Files
SET VersionGuid = @VersionGuid
WHERE FileID = @FileID
GO
/* Add UpdateFolderVersion SP */
/**********************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UpdateFolderVersion]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}UpdateFolderVersion
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdateFolderVersion]
@FolderID int,
@VersionGuid uniqueidentifier
AS
UPDATE {databaseOwner}{objectQualifier}Folders
SET VersionGuid = @VersionGuid
WHERE FolderID = @FolderID
GO
/* Update GetTabPaths SP */
/*************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetTabPaths]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetTabPaths
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetTabPaths]
@PortalID int,
@CultureCode nvarchar(10)
AS
SELECT
TabID,
PortalID,
TabPath
FROM {databaseOwner}{objectQualifier}Tabs
WHERE (PortalID = @PortalID AND (CultureCode = @CultureCode OR CultureCode Is Null))
OR @PortalID Is NULL
GO
/* Add GetFileByUniqueID SP */
/**********************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetFileByUniqueID]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetFileByUniqueID
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetFileByUniqueID]
@UniqueID uniqueidentifier
AS
SELECT *
FROM {databaseOwner}{objectQualifier}Files
WHERE UniqueID = @UniqueID
GO
/* Add GetFolderByUniqueID SP */
/**********************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetFolderByUniqueID]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetFolderByUniqueID
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetFolderByUniqueID]
@UniqueID uniqueidentifier
AS
SELECT *
FROM {databaseOwner}{objectQualifier}Folders
WHERE UniqueID = @UniqueID
GO
/* Add UpdateTabTranslationStatus */
/**********************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UpdateTabTranslationStatus]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}UpdateTabTranslationStatus
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdateTabTranslationStatus]
@TabId int,
@LocalizedVersionGuid uniqueidentifier,
@LastModifiedByUserID int
AS
UPDATE {databaseOwner}{objectQualifier}Tabs
SET
LocalizedVersionGuid = @LocalizedVersionGuid,
LastModifiedByUserID = @LastModifiedByUserID,
LastModifiedOnDate = getdate()
WHERE TabId = @TabId
GO
/* Add UpdateTabModuleTranslationStatus */
/****************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UpdateTabModuleTranslationStatus]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}UpdateTabModuleTranslationStatus
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdateTabModuleTranslationStatus]
@TabModuleId int,
@LocalizedVersionGuid uniqueidentifier,
@LastModifiedByUserID int
AS
UPDATE {databaseOwner}{objectQualifier}TabModules
SET
LocalizedVersionGuid = @LocalizedVersionGuid,
LastModifiedByUserID = @LastModifiedByUserID,
LastModifiedOnDate = getdate()
WHERE TabModuleId = @TabModuleId
GO
/* Modify GetFile */
/**********************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetFile]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetFile
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetFile]
@FileName nvarchar(100),
@PortalID int,
@FolderID int
AS
SELECT FileId,
{databaseOwner}{objectQualifier}Folders.PortalId,
FileName,
Extension,
Size,
Width,
Height,
ContentType,
{databaseOwner}{objectQualifier}Files.FolderID,
'Folder' = FolderPath,
StorageLocation,
IsCached,
{databaseOwner}{objectQualifier}Files.UniqueId,
{databaseOwner}{objectQualifier}Files.VersionGuid,
{databaseOwner}{objectQualifier}Files.SHA1Hash
FROM {databaseOwner}{objectQualifier}Files
INNER JOIN {databaseOwner}{objectQualifier}Folders ON {databaseOwner}{objectQualifier}Files.FolderID = {databaseOwner}{objectQualifier}Folders.FolderID
WHERE FileName = @FileName
AND {databaseOwner}{objectQualifier}Files.FolderID = @FolderID
AND (({databaseOwner}{objectQualifier}Folders.PortalId = @PortalID) OR (@PortalID IS NULL AND {databaseOwner}{objectQualifier}Folders.PortalId IS NULL))
GO
/* Modify GetFileById */
/**********************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetFileById]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetFileById
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetFileById]
@FileId int,
@PortalID int
AS
SELECT FileId,
{databaseOwner}{objectQualifier}Folders.PortalId,
FileName,
Extension,
Size,
Width,
Height,
ContentType,
{databaseOwner}{objectQualifier}Files.FolderID,
'Folder' = FolderPath,
StorageLocation,
IsCached,
SHA1Hash
FROM {databaseOwner}{objectQualifier}Files
INNER JOIN {databaseOwner}{objectQualifier}Folders ON {databaseOwner}{objectQualifier}Files.FolderID = {databaseOwner}{objectQualifier}Folders.FolderID
WHERE FileId = @FileId
AND (({databaseOwner}{objectQualifier}Folders.PortalId = @PortalID) OR (@PortalID IS NULL AND {databaseOwner}{objectQualifier}Folders.PortalId IS NULL))
GO
/* Modify AddModule */
/********************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}AddModule]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}AddModule
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}AddModule]
@ContentItemID int,
@PortalID int,
@ModuleDefId int,
@AllTabs bit,
@StartDate datetime,
@EndDate datetime,
@InheritViewPermissions bit,
@IsDeleted bit,
@CreatedByUserID int
AS
INSERT INTO {databaseOwner}{objectQualifier}Modules (
ContentItemID,
PortalId,
ModuleDefId,
AllTabs,
StartDate,
EndDate,
InheritViewPermissions,
IsDeleted,
CreatedByUserID,
CreatedOnDate,
LastModifiedByUserID,
LastModifiedOnDate
)
VALUES (
@ContentItemID,
@PortalID,
@ModuleDefId,
@AllTabs,
@StartDate,
@EndDate,
@InheritViewPermissions,
@IsDeleted,
@CreatedByUserID,
getdate(),
@CreatedByUserID,
getdate()
)
SELECT SCOPE_IDENTITY()
GO
/* Modify UpdateModule */
/***********************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UpdateModule]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}UpdateModule
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdateModule]
@ModuleId int,
@ContentItemID int,
@AllTabs bit,
@StartDate datetime,
@EndDate datetime,
@InheritViewPermissions bit,
@IsDeleted bit,
@LastModifiedByUserID int
AS
UPDATE {databaseOwner}{objectQualifier}Modules
SET
ContentItemID = @ContentItemID,
AllTabs = @AllTabs,
StartDate = @StartDate,
EndDate = @EndDate,
InheritViewPermissions = @InheritViewPermissions,
IsDeleted = @IsDeleted,
LastModifiedByUserID = @LastModifiedByUserID,
LastModifiedOnDate = getdate()
WHERE ModuleId = @ModuleId
GO
/* Remove ModuleTitle, Header and Footer from Modules */
/******************************************************/
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}Modules' AND COLUMN_NAME='ModuleTitle')
BEGIN
-- Drop Column
ALTER TABLE {databaseOwner}{objectQualifier}Modules
DROP COLUMN ModuleTitle
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}Modules' AND COLUMN_NAME='Header')
BEGIN
-- Drop Column
ALTER TABLE {databaseOwner}{objectQualifier}Modules
DROP COLUMN Header
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}Modules' AND COLUMN_NAME='Footer')
BEGIN
-- Drop Column
ALTER TABLE {databaseOwner}{objectQualifier}Modules
DROP COLUMN Footer
END
GO
/* Add DefaultVisibility to ProfilePropertyDefinition */
/******************************************************/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}ProfilePropertyDefinition' AND COLUMN_NAME='DefaultVisibility')
BEGIN
ALTER TABLE {databaseOwner}{objectQualifier}ProfilePropertyDefinition
ADD DefaultVisibility int NULL
END
GO
/* Set default value of new column */
/********************************/
UPDATE {databaseOwner}{objectQualifier}ProfilePropertyDefinition
SET DefaultVisibility = 2
WHERE PropertyName Not In ('Prefix', 'FirstName', 'MiddleName', 'LastName', 'Suffix', 'Photo')
GO
UPDATE {databaseOwner}{objectQualifier}ProfilePropertyDefinition
SET DefaultVisibility = 0
WHERE PropertyName In ('Prefix', 'FirstName', 'MiddleName', 'LastName', 'Suffix', 'Photo')
GO
/* Update AddPropertyDefinition */
/********************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}AddPropertyDefinition]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}AddPropertyDefinition
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}AddPropertyDefinition
@PortalId int,
@ModuleDefId int,
@DataType int,
@DefaultValue ntext,
@PropertyCategory nvarchar(50),
@PropertyName nvarchar(50),
@Required bit,
@ValidationExpression nvarchar(2000),
@ViewOrder int,
@Visible bit,
@Length int,
@DefaultVisibility int,
@CreatedByUserID int
AS
DECLARE @PropertyDefinitionId int
SELECT @PropertyDefinitionId = PropertyDefinitionId
FROM {databaseOwner}{objectQualifier}ProfilePropertyDefinition
WHERE (PortalId = @PortalId OR (PortalId IS NULL AND @PortalId IS NULL))
AND PropertyName = @PropertyName
IF @vieworder=-1
BEGIN
SELECT @vieworder = MAX(ViewOrder) + 1
FROM {databaseOwner}{objectQualifier}ProfilePropertyDefinition
END
IF @PropertyDefinitionId is null
BEGIN
INSERT {databaseOwner}{objectQualifier}ProfilePropertyDefinition (
PortalId,
ModuleDefId,
Deleted,
DataType,
DefaultValue,
PropertyCategory,
PropertyName,
Required,
ValidationExpression,
ViewOrder,
Visible,
Length,
DefaultVisibility,
[CreatedByUserID],
[CreatedOnDate],
[LastModifiedByUserID],
[LastModifiedOnDate]
)
VALUES (
@PortalId,
@ModuleDefId,
0,
@DataType,
@DefaultValue,
@PropertyCategory,
@PropertyName,
@Required,
@ValidationExpression,
@ViewOrder,
@Visible,
@Length,
@DefaultVisibility,
@CreatedByUserID,
getdate(),
@CreatedByUserID,
getdate()
)
SELECT @PropertyDefinitionId = SCOPE_IDENTITY()
END
ELSE
BEGIN
UPDATE {databaseOwner}{objectQualifier}ProfilePropertyDefinition
SET DataType = @DataType,
ModuleDefId = @ModuleDefId,
DefaultValue = @DefaultValue,
PropertyCategory = @PropertyCategory,
Required = @Required,
ValidationExpression = @ValidationExpression,
ViewOrder = @ViewOrder,
Deleted = 0,
Visible = @Visible,
Length = @Length,
DefaultVisibility = @DefaultVisibility,
[LastModifiedByUserID] = @CreatedByUserID,
[LastModifiedOnDate] = getdate()
WHERE PropertyDefinitionId = @PropertyDefinitionId
END
SELECT @PropertyDefinitionId
GO
/* Update UpdatePropertyDefinition */
/***********************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UpdatePropertyDefinition]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}UpdatePropertyDefinition
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdatePropertyDefinition
@PropertyDefinitionId int,
@DataType int,
@DefaultValue nvarchar(50),
@PropertyCategory nvarchar(50),
@PropertyName nvarchar(50),
@Required bit,
@ValidationExpression nvarchar(100),
@ViewOrder int,
@Visible bit,
@Length int,
@DefaultVisibility int,
@LastModifiedByUserID int
AS
UPDATE {databaseOwner}{objectQualifier}ProfilePropertyDefinition
SET DataType = @DataType,
DefaultValue = @DefaultValue,
PropertyCategory = @PropertyCategory,
PropertyName = @PropertyName,
Required = @Required,
ValidationExpression = @ValidationExpression,
ViewOrder = @ViewOrder,
Visible = @Visible,
Length = @Length,
DefaultVisibility = @DefaultVisibility,
[LastModifiedByUserID] = @LastModifiedByUserID,
[LastModifiedOnDate] = getdate()
WHERE PropertyDefinitionId = @PropertyDefinitionId
GO
if not exists ( select 1 from {databaseOwner}{objectQualifier}Permission where PermissionCode = 'SYSTEM_FOLDER' and ModuleDefId = -1 and PermissionKey = 'BROWSE' )
begin
INSERT INTO {databaseOwner}{objectQualifier}Permission (
PermissionCode,
ModuleDefId,
PermissionKey,
PermissionName,
ViewOrder,
CreatedByUserID,
CreatedOnDate,
LastModifiedByUserID,
LastModifiedOnDate
)
values (
'SYSTEM_FOLDER',
-1,
'BROWSE',
'Browse Folder',
0,
NULL,
NULL,
NULL,
NULL
)
declare @READ int
select @READ = PermissionID
from {databaseOwner}{objectQualifier}Permission
where PermissionCode = 'SYSTEM_FOLDER'
and ModuleDefId = -1
and PermissionKey = 'READ'
declare @BROWSE int
select @BROWSE = PermissionID
from {databaseOwner}{objectQualifier}Permission
where PermissionCode = 'SYSTEM_FOLDER'
and ModuleDefId = -1
and PermissionKey = 'BROWSE'
INSERT INTO {databaseOwner}{objectQualifier}FolderPermission
SELECT FP.FolderID, @BROWSE, FP.AllowAccess, FP.RoleID, FP.UserID, FP.CreatedByUserID, FP.CreatedOnDate, FP.LastModifiedByUserID, FP.LastModifiedOnDate
FROM {databaseOwner}{objectQualifier}FolderPermission FP
INNER JOIN {databaseOwner}{objectQualifier}Folders ON FP.FolderID = {databaseOwner}{objectQualifier}Folders.FolderID
WHERE PermissionID = @READ
AND SUBSTRING(FolderPath,1,6) <> 'Users/'
end
GO
/* script to fix issue with errorneous administratorroleid values*/
DECLARE @PortalID int
DECLARE @AdministratorRoleId int
--Iterate through each portal
DECLARE Portals_Cursor CURSOR FOR
SELECT PortalID,AdministratorRoleId
FROM {databaseOwner}{objectQualifier}Portals
OPEN Portals_Cursor
--get first
FETCH NEXT FROM Portals_Cursor
into @PortalID,@AdministratorRoleId
WHILE @@FETCH_STATUS = 0
BEGIN
if @AdministratorRoleId=-1 or @AdministratorRoleId not in (select roleid from {databaseOwner}{objectQualifier}userroles as ur inner join {databaseOwner}{objectQualifier}userportals as up on ur.userid=up.userid where up.portalid=@portalid)
update {databaseOwner}{objectQualifier}portals set AdministratorRoleId =
(select roleid from {databaseOwner}{objectQualifier}Roles as r where
r.PortalID ={databaseOwner}{objectQualifier}Portals.PortalID and RoleName = 'Administrators' and {databaseOwner}{objectQualifier}Porta...) where portalid=@portalid
FETCH NEXT FROM Portals_Cursor
into @PortalID, @AdministratorRoleId
END
CLOSE Portals_Cursor
DEALLOCATE Portals_Cursor
GO
/* Cleanup code Add Foreign Key to PortalLocalization Table for previous failed 5.4.0 versions */
/***********************************************************************************************/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'FK_{objectQualifier}PortalLocalization_{objectQualifier}Portals') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
BEGIN
/* Delete unused entries in PortalLocalization */
DELETE FROM {databaseOwner}{objectQualifier}PortalLocalization WHERE portalID not In (SELECT PortalID from {databaseOwner}{objectQualifier}Portals)
/* add key and cascade delete rule */
ALTER TABLE {databaseOwner}{objectQualifier}PortalLocalization
ADD CONSTRAINT FK_{objectQualifier}PortalLocalization_{objectQualifier}Portals
FOREIGN KEY ( PortalID ) REFERENCES {databaseOwner}{objectQualifier}Portals ( PortalID ) ON DELETE CASCADE
END
GO
/**********************************************************/
/* Modify GetAllFiles SP */
/**********************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetAllFiles]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetAllFiles
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetAllFiles]
AS
SELECT
F.FileId,
FO.PortalId,
F.FileName,
F.Extension,
F.Size,
F.Width,
F.Height,
F.ContentType,
F.FolderID,
'Folder' = FolderPath,
FO.StorageLocation,
FO.IsCached,
F.UniqueId,
F.VersionGuid,
F.SHA1Hash
FROM
{databaseOwner}[{objectQualifier}Files] F
INNER JOIN
{databaseOwner}[{objectQualifier}Folders] FO on F.FolderID = FO.FolderID
GO
/* Fix incorrect culture codes assigned pre-5.5 */
/************************************************/
UPDATE {databaseOwner}{objectQualifier}Tabs
SET CultureCode = NULL
GO
/************************************************************/
/***** SqlDataProvider *****/
/************************************************************/
05.06.00
/************************************************************/
/***** SqlDataProvider *****/
/***** *****/
/***** *****/
/***** Note: To manually execute this script you must *****/
/***** perform a search and replace operation *****/
/***** for {databaseOwner} and {objectQualifier} *****/
/***** *****/
/************************************************************/
/* DNN-13277 - Clustered Primary Key for UserProfile */
/******************************************************/
if not exists (select * from dbo.sysobjects where id = object_id(N'PK_{objectQualifier}UserProfile') and OBJECTPROPERTY(id, N'CnstIsClustKey') = 1)
begin
alter table {databaseOwner}[{objectQualifier}UserProfile]
drop constraint PK_{objectQualifier}UserProfile
alter table {databaseOwner}[{objectQualifier}UserProfile]
add constraint PK_{objectQualifier}UserProfile
primary key clustered ( [ProfileID] asc ) on [PRIMARY]
end
go
/* DNN-13287 - Clustered Primary Key for RoleGroups */
/*****************************************************/
if not exists (select * from dbo.sysobjects where id = object_id(N'PK_{objectQualifier}RoleGroups') and OBJECTPROPERTY(id, N'CnstIsClustKey') = 1)
begin
alter table {databaseOwner}[{objectQualifier}Roles]
drop constraint [FK_{objectQualifier}Roles_{objectQualifier}RoleGroups]
alter table {databaseOwner}[{objectQualifier}RoleGroups]
drop constraint PK_{objectQualifier}RoleGroups
alter table {databaseOwner}[{objectQualifier}RoleGroups]
add constraint PK_{objectQualifier}RoleGroups
primary key clustered ( [RoleGroupID] asc ) on [PRIMARY]
alter table {databaseOwner}[{objectQualifier}Roles]
add constraint [FK_{objectQualifier}Roles_{objectQualifier}RoleGroups]
foreign key ([RoleGroupID]) references {databaseOwner}[{objectQualifier}RoleGroups] ([RoleGroupID])
end
go
/* DNN-13285 - Clustered Primary Key for ModuleDefinitions */
/*************************************************************/
if not exists (select * from dbo.sysobjects where id = object_id(N'PK_{objectQualifier}ModuleDefinitions') and OBJECTPROPERTY(id, N'CnstIsClustKey') = 1)
begin
alter table {databaseOwner}[{objectQualifier}Modules]
drop constraint FK_{objectQualifier}Modules_{objectQualifier}ModuleDefinitions
alter table {databaseOwner}[{objectQualifier}ModuleControls]
drop constraint FK_{objectQualifier}ModuleControls_{objectQualifier}ModuleDefinitions
alter table {databaseOwner}[{objectQualifier}ModuleDefinitions]
drop constraint PK_{objectQualifier}ModuleDefinitions
alter table {databaseOwner}[{objectQualifier}ModuleDefinitions]
add constraint PK_{objectQualifier}ModuleDefinitions
primary key clustered (ModuleDefID asc ) on [PRIMARY]
alter table {databaseOwner}[{objectQualifier}ModuleControls]
add constraint FK_{objectQualifier}ModuleControls_{objectQualifier}ModuleDefinitions
foreign key ( ModuleDefID ) references {databaseOwner}[{objectQualifier}ModuleDefinitions] ( ModuleDefID )
on update no action
on delete cascade
alter table {databaseOwner}[{objectQualifier}Modules]
add constraint FK_{objectQualifier}Modules_{objectQualifier}ModuleDefinitions
foreign key ( ModuleDefID ) references {databaseOwner}[{objectQualifier}ModuleDefinitions] ( ModuleDefID )
on update no action
on delete cascade
end
go
/* DNN-14444 - Add new Search Setting for Tag Info Filter*/
/*********************************************************/
IF NOT EXISTS (SELECT * FROM {databaseOwner}[{objectQualifier}HostSettings] WHERE SettingName = 'SearchIncludedTagInfoFilter')
BEGIN
INSERT INTO {databaseOwner}[{objectQualifier}HostSettings]
(SettingName, SettingValue,SettingIsSecure,CreatedByUserID, CreatedOnDate,LastModifiedByUserID, LastModifiedOnDate)
VALUES ('SearchIncludedTagInfoFilter', 'alt|href|src|title', 0,-1,GETDATE(), -1, GETDATE())
END
GO
/* DNN-13273 - ModuleControls needs index */
/*******************************************/
if not exists (select * from dbo.sysindexes where name='IX_{objectQualifier}ModuleControls_ControlKey_ViewOrder')
begin
create index IX_{objectQualifier}ModuleControls_ControlKey_ViewOrder
on {databaseOwner}[{objectQualifier}ModuleControls] (ControlKey, ViewOrder) on [PRIMARY]
end
go
/* DNN-13289 - ContentItems needs additional index */
/****************************************************/
if not exists (select * from dbo.sysindexes where name='IX_{objectQualifier}ContentItems_Tags_TermID')
begin
create index IX_{objectQualifier}ContentItems_Tags_TermID
on {databaseOwner}[{objectQualifier}ContentItems_Tags] ([TermID] asc) on [PRIMARY]
end
go
/* DNN-13291 - Index for TabSettings */
/**************************************/
if not exists (select * from dbo.sysindexes where name='IX_{objectQualifier}TabSettings_TabID_SettingName')
begin
create unique clustered index IX_{objectQualifier}TabSettings_TabID_SettingName
on {databaseOwner}[{objectQualifier}TabSettings] ([TabID] asc, [SettingName] asc)
with (FILLFACTOR = 90) on [PRIMARY]
end
go
/* DNN-13447 - Index on column Users.Email */
/********************************************/
if not exists (select * from dbo.sysindexes where name='IX_{objectQualifier}Users_Email')
begin
create index IX_{objectQualifier}Users_Email
on {databaseOwner}[{objectQualifier}Users] ([Email]) on [PRIMARY]
end
go
/* DNN-13452 - New index on EventLog_LogGUID */
/**********************************************/
if not exists (select * from dbo.sysindexes where name='IX_{objectQualifier}EventLog_LogGUID')
begin
create nonclustered index IX_{objectQualifier}EventLog_LogGUID
on {databaseOwner}[{objectQualifier}EventLog] ( [LogGUID] asc )
with (FILLFACTOR = 75) ON [PRIMARY]
end
go
/* DNN-13365 - Need additional index on table [Tabs] */
/******************************************************/
if not exists (select * from dbo.sysindexes where name='IX_{objectQualifier}Tabs_PortalID_ParentID')
begin
create index IX_{objectQualifier}Tabs_PortalID_ParentID
on {databaseOwner}[{objectQualifier}Tabs] (PortalID ASC, ParentID ASC) on [PRIMARY]
end
go
/* DNN-13396 - UserPortals need index removed */
/***********************************************/
if exists (select * from dbo.sysindexes where name='IX_{objectQualifier}UserPortals_1')
begin
drop index {databaseOwner}[{objectQualifier}UserPortals].[IX_{objectQualifier}UserPortals_1]
end
go
/* DNN-13544 - Remove index on ModulePermissions */
/**************************************************/
if exists (select * from dbo.sysindexes where name='IX_{objectQualifier}ModulePermission_Modules')
begin
drop index {databaseOwner}[{objectQualifier}ModulePermission].IX_{objectQualifier}ModulePermission_Modules
end
go
/* DNN-13290 - ScheduleHistory needs index adjustment */
/*******************************************************/
if exists (select * FROM sys.indexes where object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ScheduleHistory]') and name = N'IX_{objectQualifier}ScheduleHistory_NextStart')
drop index [IX_{objectQualifier}ScheduleHistory_NextStart] ON {databaseOwner}[{objectQualifier}ScheduleHistory] WITH ( ONLINE = OFF )
go
create nonclustered index [IX_{objectQualifier}ScheduleHistory_NextStart]
ON {databaseOwner}[{objectQualifier}ScheduleHistory] ( [ScheduleID] ASC, [NextStart] DESC ) ON [PRIMARY]
go
if exists (select * FROM sys.indexes where object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ScheduleHistory]') and name = N'IX_{objectQualifier}ScheduleHistory_StartDate')
drop index [IX_{objectQualifier}ScheduleHistory_StartDate] ON {databaseOwner}[{objectQualifier}ScheduleHistory] WITH ( ONLINE = OFF )
go
create nonclustered index [IX_{objectQualifier}ScheduleHistory_StartDate]
ON {databaseOwner}[{objectQualifier}ScheduleHistory] ( [ScheduleID] ASC, [StartDate] DESC ) ON [PRIMARY]
go
/* DNN-13328 - Nonclustered indexes needed on table [Portals] */
/***************************************************************/
if not exists (select * from dbo.sysindexes where name='IX_{objectQualifier}Portals_DefaultLanguage')
begin
create index IX_{objectQualifier}Portals_DefaultLanguage
on {databaseOwner}[{objectQualifier}Portals] (DefaultLanguage) on [PRIMARY]
end
go
if not exists (select * from dbo.sysindexes where name='IX_{objectQualifier}Portals_AdministratorId')
begin
create index IX_{objectQualifier}Portals_AdministratorId
on {databaseOwner}[{objectQualifier}Portals] (AdministratorId) on [PRIMARY]
end
go
/* DNN-13288 - Procedure GetContentItemsByTerm needs simplification */
/*********************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetContentItemsByTerm]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
drop procedure {databaseOwner}{objectQualifier}GetContentItemsByTerm
GO
create procedure {databaseOwner}{objectQualifier}GetContentItemsByTerm
@Term nvarchar(250)
AS
DECLARE @TermID int,
@TermLeft int,
@TermRight int,
@VocabularyID int
SELECT @TermID = TermID,
@TermLeft = TermLeft,
@TermRight = TermRight,
@VocabularyID = VocabularyID
FROM {databaseOwner}{objectQualifier}Taxonomy_Terms
WHERE Name = @Term
IF @TermLeft = 0 AND @TermRight = 0
-- Simple Term
BEGIN
SELECT c.*
FROM {databaseOwner}{objectQualifier}ContentItems As c
INNER JOIN {databaseOwner}{objectQualifier}ContentItems_Tags ct ON ct.ContentItemID = c.ContentItemID
INNER JOIN {databaseOwner}{objectQualifier}Taxonomy_Terms t ON t.TermID = ct.TermID
WHERE t.TermID = @TermID
END
ELSE
BEGIN
-- Hierarchical Term
SELECT c.*
FROM {databaseOwner}{objectQualifier}ContentItems As c
INNER JOIN {databaseOwner}{objectQualifier}ContentItems_Tags ct ON ct.ContentItemID = c.ContentItemID
INNER JOIN {databaseOwner}{objectQualifier}Taxonomy_Terms t ON t.TermID = ct.TermID
WHERE t.TermLeft >= @TermLeft
AND t.TermRight <= @TermRight
AND t.VocabularyID = @VocabularyID
END
GO
/* DNN-13276 - vw_TabPermissions.TabID should probabaly come from dbo.Tabs */
/***************************************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}vw_TabPermissions]') AND OBJECTPROPERTY(id, N'IsVIEW') = 1)
DROP VIEW {databaseOwner}{objectQualifier}vw_TabPermissions
GO
CREATE VIEW {databaseOwner}{objectQualifier}vw_TabPermissions
AS
SELECT
TP.TabPermissionID,
T.TabID,
P.PermissionID,
TP.RoleID,
CASE TP.RoleID
when -1 then 'All Users'
when -2 then 'Superuser'
when -3 then 'Unauthenticated Users'
else R.RoleName
END AS 'RoleName',
TP.AllowAccess,
TP.UserID,
U.Username,
U.DisplayName,
P.PermissionCode,
P.ModuleDefID,
P.PermissionKey,
P.PermissionName,
T.PortalId,
TP.CreatedByUserID,
TP.CreatedOnDate,
TP.LastModifiedByUserID,
TP.LastModifiedOnDate
FROM {databaseOwner}{objectQualifier}TabPermission AS TP
INNER JOIN {databaseOwner}{objectQualifier}Tabs AS T ON TP.TabID = T.TabID
LEFT OUTER JOIN {databaseOwner}{objectQualifier}Permission AS P ON TP.PermissionID = P.PermissionID
LEFT OUTER JOIN {databaseOwner}{objectQualifier}Roles AS R ON TP.RoleID = R.RoleID
LEFT OUTER JOIN {databaseOwner}{objectQualifier}Users AS U ON TP.UserID = U.UserID
GO
/* Create TabModules View */
/**************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}vw_TabModules]') AND OBJECTPROPERTY(id, N'IsVIEW') = 1)
DROP VIEW {databaseOwner}{objectQualifier}vw_TabModules
GO
CREATE VIEW {databaseOwner}{objectQualifier}vw_TabModules
AS
SELECT
M.PortalID,
TM.TabID,
TM.TabModuleID,
M.ModuleID,
M.ModuleDefID,
TM.ModuleOrder,
TM.PaneName,
TM.ModuleTitle,
TM.CacheTime,
TM.CacheMethod,
TM.Alignment,
TM.Color,
TM.Border,
CASE WHEN LEFT(LOWER(TM.IconFile), 6) = 'fileid' THEN (SELECT Folder + FileName FROM {databaseOwner}{objectQualifier}Files WHERE 'fileid=' + CONVERT(varchar, {databaseOwner}{objectQualifier}Files.FileID) = TM.IconFile) ELSE TM.IconFile END AS IconFile,
M.AllTabs,
TM.Visibility,
TM.IsDeleted,
TM.Header,
TM.Footer,
M.StartDate,
M.EndDate,
TM.ContainerSrc,
TM.DisplayTitle,
TM.DisplayPrint,
TM.DisplaySyndicate,
TM.IsWebSlice,
TM.WebSliceTitle,
TM.WebSliceExpiryDate,
TM.WebSliceTTL,
M.InheritViewPermissions,
MD.DesktopModuleID,
MD.DefaultCacheTime,
MC.ModuleControlID,
DM.BusinessControllerClass,
DM.IsAdmin,
DM.SupportedFeatures,
CI.ContentItemID,
CI.Content,
CI.ContentTypeID,
CI.ContentKey,
CI.Indexed,
TM.CreatedByUserID,
TM.CreatedOnDate,
TM.LastModifiedByUserID,
TM.LastModifiedOnDate,
TM.UniqueId,
TM.VersionGuid,
TM.DefaultLanguageGuid,
TM.LocalizedVersionGuid,
TM.CultureCode
FROM {databaseOwner}{objectQualifier}ModuleDefinitions AS MD
INNER JOIN {databaseOwner}{objectQualifier}Modules AS M ON MD.ModuleDefID = M.ModuleDefID
INNER JOIN {databaseOwner}{objectQualifier}ModuleControls AS MC ON MD.ModuleDefID = MC.ModuleDefID
INNER JOIN {databaseOwner}{objectQualifier}DesktopModules AS DM ON MD.DesktopModuleID = DM.DesktopModuleID
LEFT OUTER JOIN {databaseOwner}{objectQualifier}ContentItems AS CI ON M.ContentItemID = CI.ContentItemID
LEFT OUTER JOIN {databaseOwner}{objectQualifier}TabModules AS TM ON M.ModuleID = TM.ModuleID
WHERE (MC.ControlKey IS NULL)
GO
/* Modify GetTabModule */
/***********************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetTabModule]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetTabModule
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetTabModule]
@TabModuleID int
AS
SELECT *
FROM {databaseOwner}{objectQualifier}vw_TabModules
WHERE TabModuleID = @TabModuleID
GO
/* Modify GetTabModules */
/************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetTabModules]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetTabModules
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetTabModules]
@TabId int
AS
SELECT *
FROM {databaseOwner}{objectQualifier}vw_TabModules
WHERE TabId = @TabId
ORDER BY ModuleOrder
GO
/* DNN-13399 - Adding Procedure CalculatePagingInformation */
/***********************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}CalculatePagingInformation]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}CalculatePagingInformation
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}CalculatePagingInformation]
(
@pageIndex int,
@pageSize int,
@rowsToReturn int = null output,
@pageLowerBound int = null output,
@pageUpperBound int = null output
)
as
begin
declare
@minValue int,
@maxValue int,
@maxPageIndex int,
@pageSizeBig bigint,
@pageIndexBig bigint,
@rowsToReturnBig bigint,
@pageLowerBoundBig bigint,
@pageUpperBoundBig bigint
select
@minValue = 0,
@maxValue = cast(0x7fffffff as int),
@maxPageIndex = @maxValue / @pageSize,
@pageSizeBig = @pageSize,
@pageIndexBig = @pageIndex
if (@pageSize = 0 and @pageIndex = 0)
or @pageSize = @maxValue
or @pageIndex < 0
or @pageSize < 0
or @pageSize is null
or @pageIndex is null
begin
select
@rowsToReturn = @maxValue,
@pageLowerBound = 0,
@pageUpperBound = @maxValue
end
else
begin
select
@rowsToReturnBig = @pageSizeBig * (@pageIndexBig + 1),
@pageLowerBoundBig = @pageSizeBig * @pageIndexBig,
@pageUpperBoundBig = @pageLowerBoundBig + @pageSizeBig + 1
select
@rowsToReturn = case when @rowsToReturnBig <= @maxValue then cast(@rowsToReturnBig as int) else @maxValue end,
@pageLowerBound = case when @pageLowerBoundBig <= @maxValue then cast(@pageLowerBoundBig as int) else @maxValue end,
@pageUpperBound = case when @pageUpperBoundBig <= @maxValue then cast(@pageUpperBoundBig as int) else @maxValue end
end
end
GO
/* DNN-13448 - Adjust GetUsersByEmail for performance */
/******************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetUsersByEmail]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetUsersByEmail
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetUsersByEmail]
@PortalID int,
@EmailToMatch nvarchar(256),
@PageIndex int,
@PageSize int
AS
BEGIN
-- Set the page bounds
DECLARE
@PageLowerBound INT,
@PageUpperBound INT,
@RowsToReturn int,
@TotalRecords int
exec {databaseOwner}[{objectQualifier}CalculatePagingInformation] @PageIndex, @PageSize, @RowsToReturn output, @PageLowerBound output, @PageUpperBound output
declare @tblPageIndex table (
IndexId int IDENTITY (0, 1) NOT NULL primary key,
UserId int
)
if @PortalId is null and @EmailToMatch IS NULL
begin
with [UsersByEmail] as (
SELECT U.*, ROW_NUMBER() OVER (ORDER BY Email ASC) AS ROWID
FROM {databaseOwner}{objectQualifier}vw_Users U
WHERE U.PortalId IS NULL
AND U.IsDeleted = 0
)
select *, ROWID - 1 AS IndexID, UserID
from [UsersByEmail]
where ROWID > @PageLowerBound AND ROWID < @PageUpperBound
end
else if @PortalId is null and @EmailToMatch IS NOT NULL
begin
with [UsersByEmail] as (
SELECT U.*, ROW_NUMBER() OVER (ORDER BY Email ASC) AS ROWID
FROM {databaseOwner}{objectQualifier}vw_Users U
WHERE U.PortalId IS NULL
AND LOWER(U.Email) LIKE LOWER(@EmailToMatch)
AND U.IsDeleted = 0
)
select *, ROWID - 1 AS IndexID, UserID
from [UsersByEmail]
where ROWID > @PageLowerBound AND ROWID < @PageUpperBound
end
else if @EmailToMatch IS NULL
begin
with [UsersByEmail] as (
SELECT U.*, ROW_NUMBER() OVER (ORDER BY Email ASC) AS ROWID
FROM {databaseOwner}{objectQualifier}vw_Users U
WHERE U.PortalId = @PortalID
AND U.IsDeleted = 0
)
select *, ROWID - 1 AS IndexID, UserID
from [UsersByEmail]
where ROWID > @PageLowerBound AND ROWID < @PageUpperBound
end
else
begin
with [UsersByEmail] as (
SELECT U.*, ROW_NUMBER() OVER (ORDER BY Email ASC) AS ROWID
FROM {databaseOwner}{objectQualifier}vw_Users U
WHERE U.PortalId = @PortalID
AND LOWER(U.Email) LIKE LOWER(@EmailToMatch)
AND U.IsDeleted = 0
)
select *, ROWID - 1 AS IndexID, UserID
from [UsersByEmail]
where ROWID > @PageLowerBound AND ROWID < @PageUpperBound
end
if @PortalId is null and @EmailToMatch IS NULL
begin
SELECT count(*) as TotalRecords
FROM {databaseOwner}{objectQualifier}vw_Users U
WHERE U.PortalId IS NULL
AND U.IsDeleted = 0
end
else if @PortalId is null and @EmailToMatch IS NOT NULL
begin
SELECT count(*) as TotalRecords
FROM {databaseOwner}{objectQualifier}vw_Users U
WHERE U.PortalId IS NULL
AND LOWER(U.Email) LIKE LOWER(@EmailToMatch)
AND U.IsDeleted = 0
end
else if @EmailToMatch IS NULL
begin
SELECT count(*) as TotalRecords
FROM {databaseOwner}{objectQualifier}vw_Users U
WHERE U.PortalId = @PortalID
AND U.IsDeleted = 0
end
else
begin
SELECT count(*) as TotalRecords
FROM {databaseOwner}{objectQualifier}vw_Users U
WHERE U.PortalId = @PortalID
AND LOWER(U.Email) LIKE LOWER(@EmailToMatch)
AND U.IsDeleted = 0
end
END
GO
/* DNN-13430 - Adjust GetAllUsers for performance */
/**************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetAllUsers]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetAllUsers
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetAllUsers]
@PortalID int,
@PageIndex int,
@PageSize int
AS
BEGIN
-- Set the page bounds
DECLARE
@PageLowerBound INT,
@PageUpperBound INT,
@RowsToReturn int
exec {databaseOwner}[{objectQualifier}CalculatePagingInformation] @PageIndex, @PageSize, @RowsToReturn output, @PageLowerBound output, @PageUpperBound output
if @PortalID is null
begin
with [tmpUsers] as (
select U.*, row_number() over (order by U.UserName) AS rowid
FROM {databaseOwner}{objectQualifier}vw_Users u
where U.PortalID IS NULL AND U.IsDeleted = 0
)
select * from [tmpUsers]
where rowid > @PageLowerBound and rowid < @PageUpperBound
order by rowid
end
else
begin
with [tmpUsers] as (
select U.*, row_number() over (order by U.UserName) AS rowid
FROM {databaseOwner}{objectQualifier}vw_Users u
WHERE U.PortalID = @PortalID AND U.IsDeleted = 0
)
select * from [tmpUsers]
where rowid > @PageLowerBound and rowid < @PageUpperBound
order by rowid
end
set rowcount 0
if @PortalId is null
begin
SELECT COUNT(*) as TotalRecords
FROM {databaseOwner}{objectQualifier}Users as U
WHERE U.IsDeleted = 0
AND NOT EXISTS (
select 1 from {databaseOwner}{objectQualifier}UserPortals UP where U.UserId = UP.UserId
)
end
else
begin
SELECT COUNT(*) as TotalRecords
FROM {databaseOwner}{objectQualifier}Users U
INNER JOIN {databaseOwner}{objectQualifier}UserPortals UP ON U.UserId = UP.UserId
WHERE UP.PortalId = @PortalId
AND UP.IsDeleted = 0
end
END
GO
/* DNN-13803 - Adding index on table [Tabs] */
/*********************************************/
if not exists (select * from dbo.sysindexes where name='IX_{objectQualifier}Tabs_ContentID')
begin
create index IX_{objectQualifier}Tabs_ContentID
on {databaseOwner}[{objectQualifier}Tabs] (ContentItemID) on [PRIMARY]
end
go
/* DNN-13811 - Adjust GetOnlineUser for performance */
/**************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetOnlineUser]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetOnlineUser
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetOnlineUser]
@UserID int
AS
SELECT
U.UserID,
U.UserName
FROM {databaseOwner}{objectQualifier}Users U
WHERE U.UserID = @UserID
AND EXISTS (
select 1 from {databaseOwner}{objectQualifier}UsersOnline UO where UO.UserID = U.UserID
)
GO
/* DNN-13825 - More Indexes for table [Users] */
/***********************************************/
if not exists (select * from dbo.sysindexes where name='IX_{objectQualifier}Users_IsDeleted_DisplayName')
begin
create index IX_{objectQualifier}Users_IsDeleted_DisplayName
on {databaseOwner}[{objectQualifier}Users] (IsDeleted ASC, DisplayName ASC) on [PRIMARY]
end
go
if not exists (select * from dbo.sysindexes where name='IX_{objectQualifier}UserProfile_UserID_PropertyDefinitionID')
begin
create index IX_{objectQualifier}UserProfile_UserID_PropertyDefinitionID
on {databaseOwner}[{objectQualifier}UserProfile] (UserID ASC, PropertyDefinitionID ASC) on [PRIMARY]
end
go
/* DNN-13821 - Adjusting Messaging_GetInbox for performance and to bring paging inline with other procedures */
/*************************************************************************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}Messaging_GetInbox]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}Messaging_GetInbox
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}Messaging_GetInbox]
@PortalID int,
@UserID int,
@PageNumber int,
@PageSize int
AS
-- Set the page bounds
DECLARE
@PageLowerBound INT,
@PageUpperBound INT,
@RowsToReturn int,
@PageIndex int
/* this is 1-based rather than 0-based indexing. Accomodating so that we are consistent with paging */
SET @PageIndex = @PageNumber - 1
exec {databaseOwner}[{objectQualifier}CalculatePagingInformation] @PageIndex, @PageSize, @RowsToReturn output, @PageLowerBound output, @PageUpperBound output
begin
with UserInbox as (
select * , ROW_NUMBER() over (order by Date desc) as RowNumber
from {databaseOwner}{objectQualifier}Messaging_Messages
where (ToUserID = @UserID AND Status IN (1,2) AND SkipPortal = '0')
OR (FromUserID = @UserID AND Status = 0)
)
select * from UserInbox
where RowNumber > @PageLowerBound AND RowNumber < @PageUpperBound
order by RowNumber
end
GO
/* Add SearchTabId to PortalLocalization Table */
/*************************************************/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{objectQualifier}PortalLocalization' AND COLUMN_NAME='SearchTabId')
BEGIN
-- Add new SearchTabId Column
ALTER TABLE {databaseOwner}{objectQualifier}PortalLocalization
ADD SearchTabId int NULL
END
GO
/* Add vw_PortalsDefaultLanguage View */
/**************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}vw_PortalsDefaultLanguage]') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW {databaseOwner}[{objectQualifier}vw_PortalsDefaultLanguage]
GO
CREATE VIEW {databaseOwner}[{objectQualifier}vw_PortalsDefaultLanguage]
AS
SELECT DISTINCT
P.PortalID,
PL.PortalName as PortalName,
CASE WHEN LEFT(LOWER(PL.LogoFile), 7) = 'fileid=' THEN
(SELECT Folder + FileName
FROM {databaseOwner}{objectQualifier}Files
WHERE fileid = CAST((RIGHT(LOWER(PL.LogoFile), Len(PL.LogoFile) - 7)) AS int)) ELSE PL.LogoFile END AS LogoFile,
PL.FooterText as FooterText,
P.ExpiryDate,
P.UserRegistration,
P.BannerAdvertising,
P.AdministratorId,
P.Currency,
P.HostFee,
P.HostSpace,
P.PageQuota,
P.UserQuota,
P.AdministratorRoleId,
P.RegisteredRoleId,
PL.Description as Description,
PL.KeyWords as KeyWords,
CASE WHEN LEFT(LOWER(PL.BackgroundFile), 7) = 'fileid=' THEN
(SELECT Folder + FileName
FROM {databaseOwner}{objectQualifier}Files
WHERE fileid = CAST((RIGHT(LOWER(PL.BackgroundFile), Len(PL.BackgroundFile) - 7)) AS int)) ELSE PL.BackgroundFile END AS BackgroundFile,
P.GUID,
P.PaymentProcessor,
P.ProcessorUserId,
P.ProcessorPassword,
P.SiteLogHistory,
U.Email,
P.DefaultLanguage,
P.TimezoneOffset,
PL.AdminTabId as AdminTabId,
P.HomeDirectory,
PL.SplashTabId as SplashTabId,
PL.HomeTabId as HomeTabId,
PL.LoginTabId as LoginTabId,
PL.RegisterTabId as RegisterTabId,
PL.UserTabId as UserTabId,
PL.SearchTabId as SearchTabId,
(SELECT TOP 1 TabID FROM {databaseOwner}{objectQualifier}Tabs WHERE (PortalID IS NULL) AND (ParentId IS NULL)) AS SuperTabId,
P.CreatedByUserID,
P.CreatedOnDate,
P.LastModifiedByUserID,
P.LastModifiedOnDate,
PL.CultureCode
FROM {databaseOwner}{objectQualifier}Portals AS P
INNER JOIN {databaseOwner}{objectQualifier}PortalLocalization AS PL ON P.PortalID = PL.PortalID AND PL.CultureCode = P.DefaultLanguage
LEFT OUTER JOIN {databaseOwner}{objectQualifier}Users AS U ON P.AdministratorId = U.UserID
GO
/* Update vw_Portals View */
/**************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}vw_Portals]') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW {databaseOwner}[{objectQualifier}vw_Portals]
GO
CREATE VIEW {databaseOwner}[{objectQualifier}vw_Portals]
AS
SELECT DISTINCT
P.PortalID,
PL.PortalName as PortalName,
CASE WHEN LEFT(LOWER(PL.LogoFile), 7) = 'fileid=' THEN
(SELECT Folder + FileName
FROM {databaseOwner}{objectQualifier}Files
WHERE fileid = CAST((RIGHT(LOWER(PL.LogoFile), Len(PL.LogoFile) - 7)) AS int)) ELSE PL.LogoFile END AS LogoFile,
PL.FooterText as FooterText,
P.ExpiryDate,
P.UserRegistration,
P.BannerAdvertising,
P.AdministratorId,
P.Currency,
P.HostFee,
P.HostSpace,
P.PageQuota,
P.UserQuota,
P.AdministratorRoleId,
P.RegisteredRoleId,
PL.Description as Description,
PL.KeyWords as KeyWords,
CASE WHEN LEFT(LOWER(PL.BackgroundFile), 7) = 'fileid=' THEN
(SELECT Folder + FileName
FROM {databaseOwner}{objectQualifier}Files
WHERE fileid = CAST((RIGHT(LOWER(PL.BackgroundFile), Len(PL.BackgroundFile) - 7)) AS int)) ELSE PL.BackgroundFile END AS BackgroundFile,
P.GUID,
P.PaymentProcessor,
P.ProcessorUserId,
P.ProcessorPassword,
P.SiteLogHistory,
U.Email,
P.DefaultLanguage,
P.TimezoneOffset,
PL.AdminTabId as AdminTabId,
P.HomeDirectory,
PL.SplashTabId as SplashTabId,
PL.HomeTabId as HomeTabId,
PL.LoginTabId as LoginTabId,
PL.RegisterTabId as RegisterTabId,
PL.UserTabId as UserTabId,
PL.SearchTabId as SearchTabId,
(SELECT TOP 1 TabID FROM {databaseOwner}{objectQualifier}Tabs WHERE (PortalID IS NULL) AND (ParentId IS NULL)) AS SuperTabId,
P.CreatedByUserID,
P.CreatedOnDate,
P.LastModifiedByUserID,
P.LastModifiedOnDate,
PL.CultureCode
FROM {databaseOwner}{objectQualifier}Portals AS P
INNER JOIN {databaseOwner}{objectQualifier}PortalLocalization AS PL ON P.PortalID = PL.PortalID
LEFT OUTER JOIN {databaseOwner}{objectQualifier}Users AS U ON P.AdministratorId = U.UserID
GO
/* Update UpdatePortalInfo Stored Procedure */
/********************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UpdatePortalInfo]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}UpdatePortalInfo
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdatePortalInfo
@PortalID int,
@PortalName nvarchar(128),
@LogoFile nvarchar(50),
@FooterText nvarchar(100),
@ExpiryDate datetime,
@UserRegistration int,
@BannerAdvertising int,
@Currency char(3),
@AdministratorId int,
@HostFee money,
@HostSpace int,
@PageQuota int,
@UserQuota int,
@PaymentProcessor nvarchar(50),
@ProcessorUserId nvarchar(50),
@ProcessorPassword nvarchar(50),
@Description nvarchar(500),
@KeyWords nvarchar(500),
@BackgroundFile nvarchar(50),
@SiteLogHistory int,
@SplashTabId int,
@HomeTabId int,
@LoginTabId int,
@RegisterTabId int,
@UserTabId int,
@SearchTabId int,
@DefaultLanguage nvarchar(10),
@TimeZoneOffset int,
@HomeDirectory varchar(100),
@LastModifiedByUserID int,
@CultureCode nvarchar(50)
AS
UPDATE {databaseOwner}{objectQualifier}Portals
SET
ExpiryDate = @ExpiryDate,
UserRegistration = @UserRegistration,
BannerAdvertising = @BannerAdvertising,
Currency = @Currency,
AdministratorId = @AdministratorId,
HostFee = @HostFee,
HostSpace = @HostSpace,
PageQuota = @PageQuota,
UserQuota = @UserQuota,
PaymentProcessor = @PaymentProcessor,
ProcessorUserId = @ProcessorUserId,
ProcessorPassword = @ProcessorPassword,
SiteLogHistory = @SiteLogHistory,
DefaultLanguage = @DefaultLanguage,
TimeZoneOffset = @TimeZoneOffset,
HomeDirectory = @HomeDirectory,
LastModifiedByUserID = @LastModifiedByUserID,
LastModifiedOnDate = getdate()
WHERE PortalId = @PortalID
IF EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}PortalLocalization WHERE PortalId = @PortalID AND CultureCode = @CultureCode)
BEGIN
UPDATE {databaseOwner}{objectQualifier}PortalLocalization
SET
PortalName = @PortalName,
LogoFile = @LogoFile,
FooterText = @FooterText,
Description = @Description,
KeyWords = @KeyWords,
BackgroundFile = @BackgroundFile,
HomeTabId = @HomeTabId,
LoginTabId = @LoginTabId,
RegisterTabId = @RegisterTabId,
UserTabId = @UserTabId,
SplashTabId = @SplashTabId,
SearchTabId = @SearchTabId,
LastModifiedByUserID = @LastModifiedByUserID,
LastModifiedOnDate = getdate()
WHERE PortalId = @PortalID AND CultureCode = @CultureCode
END
ELSE
BEGIN
DECLARE @AdminTabId int
SET @AdminTabId = (SELECT AdminTabId
FROM {databaseOwner}{objectQualifier}PortalLocalization
WHERE PortalID = @PortalID and CultureCode='en-US')
INSERT INTO {databaseOwner}{objectQualifier}PortalLocalization (
[PortalID],
[CultureCode],
[PortalName],
[LogoFile],
[FooterText],
[Description],
[KeyWords],
[BackgroundFile],
[HomeTabId],
[LoginTabId],
[UserTabId],
[AdminTabId],
[SplashTabId],
[SearchTabId],
[CreatedByUserID],
[CreatedOnDate],
[LastModifiedByUserID],
[LastModifiedOnDate]
)
VALUES (
@PortalID,
@CultureCode,
@PortalName,
@LogoFile,
@FooterText,
@Description,
@KeyWords,
@BackgroundFile,
@HomeTabId ,
@LoginTabId ,
@UserTabId,
@AdminTabid,
@SplashTabId ,
@SearchTabId,
-1,
getdate(),
-1,
getdate()
)
END
GO
/* Update UpdatePortalSetup Stored Procedure */
/********************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UpdatePortalSetup]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}UpdatePortalSetup
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdatePortalSetup
@PortalId int,
@AdministratorId int,
@AdministratorRoleId int,
@RegisteredRoleId int,
@SplashTabId int,
@HomeTabId int,
@LoginTabId int,
@RegisterTabId int,
@UserTabId int,
@SearchTabId int,
@AdminTabId int,
@CultureCode nvarchar(50)
AS
UPDATE {databaseOwner}{objectQualifier}Portals
SET
AdministratorId = @AdministratorId,
AdministratorRoleId = @AdministratorRoleId,
RegisteredRoleId = @RegisteredRoleId
WHERE PortalId = @PortalId
UPDATE {databaseOwner}{objectQualifier}PortalLocalization
SET
HomeTabId = @HomeTabId,
LoginTabId = @LoginTabId,
UserTabId = @UserTabId,
RegisterTabId = @RegisterTabId,
AdminTabId = @AdminTabId,
SplashTabId = @SplashTabId,
SearchTabId = @SearchTabId
WHERE portalID = @PortalID
GO
/* DNN-13599 - Indexes on Lists needs work */
/********************************************/
if not exists (select * from dbo.sysobjects where id = object_id(N'PK_{databaseOwner}[{objectQualifier}Lists]') and OBJECTPROPERTY(id, N'CnstIsClustKey') = 1)
begin
alter table {databaseOwner}[{objectQualifier}Lists]
drop constraint PK_{objectQualifier}Lists
alter table {databaseOwner}[{objectQualifier}Lists]
add constraint PK_{objectQualifier}Lists
primary key clustered ( [EntryID] asc ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
end
go
IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'IX_{objectQualifier}Lists_ListName_Value_Text_ParentID')
ALTER TABLE {databaseOwner}[{objectQualifier}Lists]
DROP CONSTRAINT [IX_{objectQualifier}Lists_ListName_Value_Text_ParentID]
GO
ALTER TABLE {databaseOwner}[{objectQualifier}Lists]
ADD CONSTRAINT [IX_{objectQualifier}Lists_ListName_Value_Text_ParentID] UNIQUE NONCLUSTERED
( [ListName] ASC,
[Value] ASC,
[Text] ASC,
[ParentID] ASC
) WITH (FILLFACTOR = 90, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'IX_{objectQualifier}Lists_ParentID')
ALTER TABLE {databaseOwner}[{objectQualifier}Lists]
DROP CONSTRAINT [IX_{objectQualifier}Lists_ParentID]
GO
CREATE INDEX IX_{objectQualifier}Lists_ParentID on [{objectQualifier}Lists] (ParentID ASC) on [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'IX_{objectQualifier}Lists_ListName_ParentID')
ALTER TABLE {databaseOwner}[{objectQualifier}Lists]
DROP CONSTRAINT [IX_{objectQualifier}Lists_ListName_ParentID]
GO
CREATE INDEX IX_{objectQualifier}Lists_ListName_ParentID on [{objectQualifier}Lists] (ListName ASC, ParentID ASC) on [PRIMARY]
GO
/*DNN-13736 - Missing FKs in DB */
/*********************************/
/*SkinPackages<-->Package*/
IF NOT EXISTS (select * from sys.foreign_keys where name ='FK_{objectQualifier}SkinPackages_{objectQualifier}Packages')
BEGIN
ALTER TABLE {databaseOwner}{objectQualifier}SkinPackages WITH NOCHECK ADD CONSTRAINT
FK_{objectQualifier}SkinPackages_{objectQualifier}Packages FOREIGN KEY (PackageID) REFERENCES {databaseOwner}{objectQualifier}Packages(PackageID)
ON UPDATE CASCADE
ON DELETE CASCADE
END
GO
/*Dashboard_Controls<-->Package*/
IF NOT EXISTS (select * from sys.foreign_keys where name ='FK_{objectQualifier}Dashboard_Controls_{objectQualifier}Packages')
BEGIN
ALTER TABLE {databaseOwner}{objectQualifier}Dashboard_Controls WITH NOCHECK ADD CONSTRAINT
FK_{objectQualifier}Dashboard_Controls_{objectQualifier}Packages FOREIGN KEY(PackageID) REFERENCES {databaseOwner}{objectQualifier}Packages(PackageID)
ON UPDATE CASCADE
ON DELETE CASCADE
END
GO
/*Authentication<-->Package*/
IF NOT EXISTS (select * from sys.foreign_keys where name ='FK_{objectQualifier}Authentication_{objectQualifier}Packages')
BEGIN
ALTER TABLE {databaseOwner}{objectQualifier}Authentication WITH NOCHECK ADD CONSTRAINT
FK_{objectQualifier}Authentication_{objectQualifier}Packages FOREIGN KEY(PackageID) REFERENCES {databaseOwner}{objectQualifier}Packages(PackageID)
ON UPDATE CASCADE
ON DELETE CASCADE
END
GO
/*SkinControls<-->Package*/
IF NOT EXISTS (select * from sys.foreign_keys where name ='FK_{objectQualifier}SkinControls_{objectQualifier}Packages')
BEGIN
ALTER TABLE {databaseOwner}{objectQualifier}SkinControls WITH NOCHECK ADD CONSTRAINT
FK_{objectQualifier}SkinControls_{objectQualifier}Packages FOREIGN KEY(PackageID) REFERENCES {databaseOwner}{objectQualifier}Packages(PackageID)
ON UPDATE CASCADE
ON DELETE CASCADE
END
GO
/*Package<-->PackageTypes*/
IF NOT EXISTS (select * from sys.foreign_keys where name ='FK_{objectQualifier}Packages_{objectQualifier}PackageTypes')
BEGIN
ALTER TABLE {databaseOwner}{objectQualifier}Packages WITH NOCHECK ADD CONSTRAINT
FK_{objectQualifier}Packages_{objectQualifier}PackageTypes FOREIGN KEY(PackageType) REFERENCES {databaseOwner}{objectQualifier}PackageTypes(PackageType)
ON UPDATE CASCADE
ON DELETE CASCADE
END
GO
/* Fix title of Login control */
/******************************/
UPDATE {databaseOwner}[{objectQualifier}ModuleControls]
SET ControlTitle = 'Account Login'
WHERE ModuleDefID is NULL AND
ControlKey = 'Login' AND
ControlSrc = 'DesktopModules/Admin/Authentication/Login.ascx'
GO
/************************************************************/
/***** SqlDataProvider *****/
/************************************************************/