To be exact:
If (SELECT {databaseOwner}{objectQualifier}fn_GetVersion(3,2,3)) = 0
BEGIN
ALTER TABLE {databaseOwner}{objectQualifier}Folders ADD
StorageLocation int NOT NULL CONSTRAINT DF_{objectQualifier}Folders_StorageLocation DEFAULT 0,
IsProtected bit NOT NULL CONSTRAINT DF_{objectQualifier}Folders_IsProtected DEFAULT 0,
IsCached bit NOT NULL CONSTRAINT DF_{objectQualifier}Folders_IsCached DEFAULT 0
END
GO
/* This sets all portal root folders to protected */
update {objectQualifier}Folders
Set IsProtected = 1
WHERE
FolderPath = ''
GO
/* This sets all containers root folders to protected */
update {objectQualifier}Folders
Set IsProtected = 1
WHERE
FolderPath = 'Containers/'
GO
/* This sets all skins root folders to protected */
update {objectQualifier}Folders
Set IsProtected = 1
WHERE
FolderPath = 'Skins/'
GO
/* This sets all cache root folders to protected */
UPDATE {objectQualifier}Folders
SET IsProtected = 1
WHERE
FolderPath = 'Cache/'
GO
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,
@FileName nvarchar(100),
@Extension nvarchar(100),
@Size int,
@WIdth int,
@Height int,
@ContentType nvarchar(200),
@Folder nvarchar(200),
@FolderID int
as
insert into {objectQualifier}Files (
PortalId,
FileName,
Extension,
Size,
WIdth,
Height,
ContentType,
Folder,
FolderID
)
values (
@PortalId,
@FileName,
@Extension,
@Size,
@WIdth,
@Height,
@ContentType,
@Folder,
@FolderID
)
select SCOPE_IDENTITY()
GO
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
FileId,
FO.PortalId,
FileName,
Extension,
Size,
Width,
Height,
ContentType,
F.FolderID,
'Folder' = FolderPath,
StorageLocation,
IsCached
FROM
{objectQualifier}Files F
INNER JOIN
{objectQualifier}Folders FO on F.FolderID = FO.FolderID
GO
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,
{objectQualifier}Folders.PortalId,
FileName,
Extension,
Size,
Width,
Height,
ContentType,
{objectQualifier}Files.FolderID,
'Folder' = FolderPath,
StorageLocation,
IsCached
from {objectQualifier}Files
inner join {objectQualifier}Folders on {objectQualifier}Files.FolderID = {objectQualifier}Folders.FolderID
where FileName = @FileName
and {objectQualifier}Files.FolderID = @FolderID
and (({objectQualifier}Folders.PortalId = @PortalId) or (@PortalId is null and {objectQualifier}Folders.PortalId is null))
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetFiles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}[{objectQualifier}GetFiles]
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetFiles]
@PortalId int,
@FolderID int
AS
SELECT
FileId,
FO.PortalId,
FileName,
Extension,
Size,
Width,
Height,
ContentType,
F.FolderID,
'Folder' = FolderPath,
StorageLocation,
IsCached
FROM
{objectQualifier}Files F
INNER JOIN
{objectQualifier}Folders FO on F.FolderID = FO.FolderID
WHERE
F.FolderID = @FolderID
AND
((FO.PortalId = @PortalId) or (@PortalId is NULL AND FO.PortalId is NULL))
ORDER BY FileName
GO
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,
{objectQualifier}Folders.PortalId,
FileName,
Extension,
Size,
Width,
Height,
ContentType,
{objectQualifier}Files.FolderID,
'Folder' = FolderPath,
StorageLocation,
IsCached
from {objectQualifier}Files
inner join {objectQualifier}Folders on {objectQualifier}Files.FolderID = {objectQualifier}Folders.FolderID
where FileId = @FileId
and (({objectQualifier}Folders.PortalId = @PortalId) or (@PortalId is null and {objectQualifier}Folders.PortalId is null))
GO
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,
@FileName nvarchar(100),
@Extension nvarchar(100),
@Size int,
@WIdth int,
@Height int,
@ContentType nvarchar(200),
@Folder nvarchar(200),
@FolderID int
as
update {objectQualifier}Files
set FileName = @FileName,
Extension = @Extension,
Size = @Size,
WIdth = @WIdth,
Height = @Height,
ContentType = @ContentType,
Folder = @Folder,
FolderID = @FolderID
where FileId = @FileId
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}DeleteFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}[{objectQualifier}DeleteFile]
GO
create procedure {databaseOwner}{objectQualifier}DeleteFile
@PortalId int,
@FileName nvarchar(100),
@FolderID int
AS
delete
from {objectQualifier}Files
where FileName = @FileName
and FolderID = @FolderID
and ((PortalId = @PortalId) or (@PortalId is null and PortalId is null))
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}UpdateFileContent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}[{objectQualifier}UpdateFileContent]
GO
CREATE procedure {databaseOwner}{objectQualifier}UpdateFileContent
@FileId int,
@Content image
as
update {objectQualifier}Files
set Content = @Content
where FileId = @FileId
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetFileContent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}[{objectQualifier}GetFileContent]
GO
CREATE procedure {databaseOwner}{objectQualifier}GetFileContent
@FileId int,
@PortalId int
as
select Content
from {objectQualifier}Files
where FileId = @FileId
and (({objectQualifier}Files.PortalId = @PortalId) or (@PortalId is null and {objectQualifier}Files.PortalId is null))
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetFolders]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}[{objectQualifier}GetFolders]
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetFolders]
@PortalID int,
@FolderID int,
@FolderPath nvarchar(300)
AS
SELECT *
FROM {objectQualifier}Folders
WHERE ((PortalID = @PortalID) or (PortalID is null and @PortalID is null))
AND (FolderID = @FolderID or @FolderID = -1)
AND (FolderPath = @FolderPath or @FolderPath = '')
ORDER BY FolderPath
GO
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,
@FolderPath varchar(300),
@StorageLocation int,
@IsProtected bit,
@IsCached bit
AS
IF NOT EXISTS (SELECT 1 FROM {objectQualifier}Folders WHERE PortalID = @PortalID and FolderPath = @FolderPath)
BEGIN
INSERT INTO {objectQualifier}Folders
(PortalID, FolderPath, StorageLocation, IsProtected, IsCached)
VALUES
(@PortalID, @FolderPath, @StorageLocation, @IsProtected, @IsCached)
SELECT SCOPE_IDENTITY()
END
GO
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,
@FolderID int,
@FolderPath varchar(300),
@StorageLocation int,
@IsProtected bit,
@IsCached bit
AS
UPDATE {objectQualifier}Folders
SET FolderPath = @FolderPath,
StorageLocation = @StorageLocation,
IsProtected = @IsProtected,
IsCached = @IsCached
WHERE ((PortalID = @PortalID) or (PortalID is null and @PortalID is null))
AND FolderID = @FolderID
GO
/* Add Transfer Profile Support Procedure */
/******************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}TransferUsersFromFlatProfile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}TransferUsersFromFlatProfile
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}TransferUsersFromFlatProfile]
@PropertyName nvarchar(100)
AS
DECLARE @sql varchar(8000)
SELECT @sql = 'INSERT INTO {objectQualifier}UserProfile (UserID, PropertyDefinitionID, PropertyValue, LastUpdatedDate) '
SELECT @sql = @sql +
'SELECT fp.UserID,
pd.PropertyDefinitionID,
Convert(varchar(50),' + column_name + ') AS PropertyValue,
fp.LastUpdatedDate
FROM {objectQualifier}FlatProfile fp
INNER JOIN {objectQualifier}ProfilePropertyDefinition pd
ON fp.PortalID = pd.PortalID
AND pd.PropertyName = ''' + column_name + '''
WHERE fp.' + column_name + ' IS NOT NULL
UNION '
FROM information_schema.columns
WHERE table_name='{objectQualifier}FlatProfile' AND column_name = @PropertyName
SELECT @sql = Left(@sql,Len(@sql)-5)
EXEC (@sql)
GO
Grant EXECUTE on {databaseOwner}[{objectQualifier}TransferUsersFromFlatProfile] to public
GO
/* Update ModuleControl definition for Register */
/************************************************/
UPDATE {objectQualifier}ModuleControls
SET ControlSrc = 'Admin/Users/ManageUsers.ascx'
WHERE ControlKey = 'Register'
GO
/* Update DisplayName value in Users */
/*************************************/
If (SELECT {databaseOwner}{objectQualifier}fn_GetVersion(3,2,3)) = 0
BEGIN
UPDATE {objectQualifier}Users
SET DisplayName = FirstName + ' ' + LastName
END
GO
/* Add New Data Types to Lists */
/*******************************/
If (SELECT {databaseOwner}{objectQualifier}fn_GetVersion(3,2,3)) = 0
BEGIN
DECLARE @RC int
--Add Data Types
EXECUTE @RC = {databaseOwner}[{objectQualifier}AddListEntry] 'DataType', 'Unknown', 'UseSystemType', '', 0, 0, NULL
EXECUTE @RC = {databaseOwner}[{objectQualifier}AddListEntry] 'DataType', 'Text', 'DotNetNuke.UI.WebControls.TextEditControl, DotNetNuke', '', 0, 0, NULL
EXECUTE @RC = {databaseOwner}[{objectQualifier}AddListEntry] 'DataType', 'Integer', 'DotNetNuke.UI.WebControls.IntegerEditControl, DotNetNuke', '', 0, 0, NULL
EXECUTE @RC = {databaseOwner}[{objectQualifier}AddListEntry] 'DataType', 'TrueFalse', 'DotNetNuke.UI.WebControls.CheckEditControl, DotNetNuke', '', 0, 0, NULL
EXECUTE @RC = {databaseOwner}[{objectQualifier}AddListEntry] 'DataType', 'TimeZone', 'DotNetNuke.UI.WebControls.DNNTimeZoneEditControl, DotNetNuke', '', 0, 0, NULL
EXECUTE @RC = {databaseOwner}[{objectQualifier}AddListEntry] 'DataType', 'Locale', 'DotNetNuke.UI.WebControls.DNNLocaleEditControl, DotNetNuke', '', 0, 0, NULL
EXECUTE @RC = {databaseOwner}[{objectQualifier}AddListEntry] 'DataType', 'Page', 'DotNetNuke.UI.WebControls.DNNPageEditControl, DotNetNuke', '', 0, 0, NULL
EXECUTE @RC = {databaseOwner}[{objectQualifier}AddListEntry] 'DataType', 'RichText', 'DotNetNuke.UI.WebControls.DNNRichTextEditControl, DotNetNuke', '', 0, 0, NULL
EXECUTE @RC = {databaseOwner}[{objectQualifier}AddListEntry] 'DataType', 'Country', 'DotNetNuke.UI.WebControls.DNNCountryEditControl, DotNetNuke', '', 0, 0, NULL
EXECUTE @RC = {databaseOwner}[{objectQualifier}AddListEntry] 'DataType', 'Region', 'DotNetNuke.UI.WebControls.DNNRegionEditControl, DotNetNuke', '', 0, 0, NULL
EXECUTE @RC = {databaseOwner}[{objectQualifier}AddListEntry] 'DataType', 'List', 'DotNetNuke.UI.WebControls.DNNListEditControl, DotNetNuke', '', 0, 0, NULL
END
GO
/* Add Default Profile Properties */
/**********************************/
If (SELECT {databaseOwner}{objectQualifier}fn_GetVersion(3,2,3)) = 0
BEGIN
DECLARE @RC int
DECLARE @PortalID int
--Add Host Properties
EXECUTE @RC = {databaseOwner}[{objectQualifier}AddDefaultPropertyDefinitions] -1
--Iterate through each portal
SELECT @PortalID = min(PortalID)
FROM {databaseOwner}{objectQualifier}Portals
WHILE @PortalID is not null
BEGIN
-- Add Portal Properties
EXECUTE @RC = {databaseOwner}[{objectQualifier}AddDefaultPropertyDefinitions] @PortalId
SELECT @PortalID = min(PortalID)
FROM {databaseOwner}{objectQualifier}Portals
WHERE PortalID > @PortalID
END
END
GO
/* Transfer Profile */
/*********************/
If (SELECT {databaseOwner}{objectQualifier}fn_GetVersion(3,2,3)) = 0
BEGIN
--Create Temp Table
CREATE TABLE {databaseOwner}{objectQualifier}FlatProfile (
[UserID] [int] ,
[PortalID] [int],
[Username] [nvarchar] (256) NULL ,
[FirstName] [nvarchar] (256) NULL ,
[LastName] [nvarchar] (256) NULL ,
[Street] [nvarchar] (256) NULL ,
[Unit] [nvarchar] (128) NULL ,
[City] [nvarchar] (256) NULL ,
[Country] [nvarchar] (256) NULL ,
[Region] [nvarchar] (256) NULL ,
[PostalCode] [nvarchar] (128) NULL ,
[Telephone] [nvarchar] (128) NULL ,
[Cell] [nvarchar] (128) NULL ,
[Fax] [nvarchar] (128) NULL ,
[Website] [nvarchar] (256) NULL ,
[IM] [nvarchar] (256) NULL,
[PreferredLocale] [nvarchar] (128) NULL ,
[TimeZone] [nvarchar] (256) NULL,
[LastUpdatedDate] [datetime]
)
--Extract from Profile Blob to Temp Table
INSERT INTO {objectQualifier}FlatProfile
SELECT
{objectQualifier}users.userid,
{objectQualifier}userportals.portalid,
{objectQualifier}users.username,
{objectQualifier}users.firstname,
{objectQualifier}users.lastname,
{databaseOwner}{objectQualifier}GetProfileElement('Street',PropertyNames,PropertyValuesString) Street,
{databaseOwner}{objectQualifier}GetProfileElement('Unit',PropertyNames,PropertyValuesString) Unit,
{databaseOwner}{objectQualifier}GetProfileElement('City',PropertyNames,PropertyValuesString) City,
{databaseOwner}{objectQualifier}GetProfileElement('Country',PropertyNames,PropertyValuesString) Country,
{databaseOwner}{objectQualifier}GetProfileElement('Region',PropertyNames,PropertyValuesString) Region,
{databaseOwner}{objectQualifier}GetProfileElement('PostalCode',PropertyNames,PropertyValuesString) PostalCode,
{databaseOwner}{objectQualifier}GetProfileElement('Telephone',PropertyNames,PropertyValuesString) Telephone,
{databaseOwner}{objectQualifier}GetProfileElement('Cell',PropertyNames,PropertyValuesString) Cell,
{databaseOwner}{objectQualifier}GetProfileElement('Fax',PropertyNames,PropertyValuesString) Fax,
{databaseOwner}{objectQualifier}GetProfileElement('Website',PropertyNames,PropertyValuesString) Website,
{databaseOwner}{objectQualifier}GetProfileElement('IM',PropertyNames,PropertyValuesString) IM,
{databaseOwner}{objectQualifier}GetProfileElement('PreferredLocale',PropertyNames,PropertyValuesString) PreferredLocale,
{databaseOwner}{objectQualifier}GetProfileElement('TimeZone',PropertyNames,PropertyValuesString) TimeZone,
aspnet_profile.LastUpdatedDate
FROM {objectQualifier}Users
inner join {objectQualifier}userportals on {objectQualifier}users.userid = {objectQualifier}userportals.userid
inner join aspnet_users on {objectQualifier}users.username = aspnet_users.username
inner join aspnet_profile on aspnet_profile.userid = aspnet_users.userid
where {objectQualifier}userportals.portalid = PortalID
--Move to UserProfile Table
EXECUTE {objectQualifier}TransferUsersFromFlatProfile N'FirstName'
EXECUTE {objectQualifier}TransferUsersFromFlatProfile N'LastName'
EXECUTE {objectQualifier}TransferUsersFromFlatProfile N'Unit'
EXECUTE {objectQualifier}TransferUsersFromFlatProfile N'Street'
EXECUTE {objectQualifier}TransferUsersFromFlatProfile N'City'
EXECUTE {objectQualifier}TransferUsersFromFlatProfile N'Region'
EXECUTE {objectQualifier}TransferUsersFromFlatProfile N'PostalCode'
EXECUTE {objectQualifier}TransferUsersFromFlatProfile N'Country'
EXECUTE {objectQualifier}TransferUsersFromFlatProfile N'Telephone'
EXECUTE {objectQualifier}TransferUsersFromFlatProfile N'Fax'
EXECUTE {objectQualifier}TransferUsersFromFlatProfile N'Cell'
EXECUTE {objectQualifier}TransferUsersFromFlatProfile N'Website'
EXECUTE {objectQualifier}TransferUsersFromFlatProfile N'IM'
EXECUTE {objectQualifier}TransferUsersFromFlatProfile N'TimeZone'
EXECUTE {objectQualifier}TransferUsersFromFlatProfile N'PreferredLocale'
--Drop temp objects
DROP TABLE {databaseOwner}{objectQualifier}FlatProfile
DROP PROCEDURE {databaseOwner}{objectQualifier}TransferUsersFromFlatProfile
END
GO
/* Add Transfer Users Support Procedure */
/****************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}TransferUsersToSameContext]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}[{objectQualifier}TransferUsersToSameContext]
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}TransferUsersToSameContext
@ApplicationName nvarchar(256),
@PortalID int
AS
-- Declare Variables
DECLARE @ApplicationId nvarchar(256)
-- First Check that the Application exists and if not create it
IF (SELECT ApplicationName FROM aspnet_Applications WHERE ApplicationName = @ApplicationName) IS NULL
BEGIN
INSERT INTO aspnet_Applications (
ApplicationName,
LoweredApplicationName)
VALUES (
@ApplicationName,
LOWER(@ApplicationName))
END
-- Next Get the ApplicationId that corresponds to the ApplicationName
SET @ApplicationId = (SELECT ApplicationId FROM aspnet_Applications WHERE ApplicationName = @ApplicationName)
IF @PortalID IS NOT NULL
BEGIN
-- Update Authorised Flag before Transferring Users)
UPDATE {objectQualifier}UserPortals
SET {objectQualifier}UserPortals.Authorised = AM.IsApproved
FROM aspnet_Applications A
INNER JOIN aspnet_Users AU ON A.ApplicationId = AU.ApplicationId
INNER JOIN aspnet_Membership AM ON A.ApplicationId = AM.ApplicationId
AND AU.UserId = AM.UserId
LEFT OUTER JOIN {objectQualifier}Users U
INNER JOIN {objectQualifier}UserPortals UP ON U.UserID = UP.UserId
ON AU.UserName = U.Username
AND A.ApplicationName = N'dnn_' + CAST(UP.PortalId AS nvarchar)
-- Update Membership
UPDATE aspnet_Membership
SET ApplicationId = @ApplicationId
FROM aspnet_Applications A
INNER JOIN aspnet_Users AU ON A.ApplicationId = AU.ApplicationId
INNER JOIN aspnet_Membership AM ON A.ApplicationId = AM.ApplicationId
AND AU.UserId = AM.UserId
LEFT OUTER JOIN {objectQualifier}TransferredUsers TU ON AU.UserName = TU.Username
LEFT OUTER JOIN {objectQualifier}Users U
INNER JOIN {objectQualifier}UserPortals UP ON U.UserID = UP.UserId
ON AU.UserName = U.Username
AND A.ApplicationName = N'{objectQualifier}' + CAST(UP.PortalId AS nvarchar)
WHERE (TU.TransferredAppId IS NULL) AND (UP.PortalId = @PortalID)
-- Update Users
UPDATE aspnet_Users
SET ApplicationId = @ApplicationId
FROM aspnet_Applications A
INNER JOIN aspnet_Users AU ON A.ApplicationId = AU.ApplicationId
LEFT OUTER JOIN {objectQualifier}TransferredUsers TU ON AU.UserName = TU.Username
LEFT OUTER JOIN {objectQualifier}Users U
INNER JOIN {objectQualifier}UserPortals UP ON U.UserID = UP.UserId
ON AU.UserName = U.Username
AND A.ApplicationName = N'{objectQualifier}' + CAST(UP.PortalId AS nvarchar)
WHERE (TU.TransferredAppId IS NULL) AND (UP.PortalId = @PortalID)
--Add Users transferred to Transferred Users Table
INSERT INTO {objectQualifier}TransferredUsers
SELECT
A.ApplicationId,
AU.UserName
FROM aspnet_Applications A
INNER JOIN aspnet_Users AU ON A.ApplicationId = AU.ApplicationId
LEFT OUTER JOIN {objectQualifier}TransferredUsers TU ON AU.UserName = TU.Username
LEFT OUTER JOIN {objectQualifier}Users U
INNER JOIN {objectQualifier}UserPortals UP ON U.UserID = UP.UserId
ON AU.UserName = U.Username
WHERE (TU.TransferredAppId IS NULL) AND (A.ApplicationName = @ApplicationName) AND (UP.PortalId = @PortalID)
END
ELSE
BEGIN
-- Update Membership
UPDATE aspnet_Membership
SET ApplicationId = @ApplicationId
FROM aspnet_Applications A
INNER JOIN aspnet_Membership M ON A.ApplicationId = M.ApplicationId
WHERE (A.ApplicationName = N'{objectQualifier}-1')
-- Update Users
UPDATE aspnet_Users
SET ApplicationId = @ApplicationId
FROM aspnet_Users AU
INNER JOIN aspnet_Applications A ON AU.ApplicationId = A.ApplicationId
WHERE (A.ApplicationName = N'{objectQualifier}-1')
--Add Users transferred to Transferred Users Table
INSERT INTO {objectQualifier}TransferredUsers
SELECT
A.ApplicationId,
AU.UserName
FROM aspnet_Applications A
INNER JOIN aspnet_Users AU ON A.ApplicationId = AU.ApplicationId
LEFT OUTER JOIN {objectQualifier}TransferredUsers TU ON AU.UserName = TU.Username
LEFT OUTER JOIN {objectQualifier}Users U ON AU.UserName = U.Username
WHERE (TU.TransferredAppId IS NULL) AND (A.ApplicationName = @ApplicationName) AND (U.IsSuperUser = 1)
END
GO
Grant EXECUTE on {databaseOwner}{objectQualifier}TransferUsersToSameContext to public
GO
/* Transfer Users */
/*******************/
If (SELECT {databaseOwner}{objectQualifier}fn_GetVersion(3,2,3)) = 0
BEGIN
--Create Temp Table to hold users
CREATE TABLE {databaseOwner}{objectQualifier}TransferredUsers (
[TransferredAppId] [uniqueidentifier] ,
[Username] [nvarchar] (256) NULL
)
--Declare Variables
DECLARE @PortalID int
-- Transfer Super Users
EXECUTE {objectQualifier}TransferUsersToSameContext N'DotNetNuke', NULL
--Iterate through Portals to transfer users
SELECT @PortalID = min(PortalID)
FROM {databaseOwner}{objectQualifier}Portals
WHILE @PortalID IS NOT NULL
BEGIN
-- Transfer Portal Users
EXECUTE {objectQualifier}TransferUsersToSameContext N'DotNetNuke', @PortalID
SELECT @PortalID = min(PortalID)
FROM {databaseOwner}{objectQualifier}Portals
WHERE PortalID > @PortalID
END
--Drop temp objects
DROP TABLE {databaseOwner}{objectQualifier}TransferredUsers
DROP PROCEDURE {databaseOwner}{objectQualifier}TransferUsersToSameContext
END
GO
/* Add New LogTypes */
/*********************/
If (SELECT {databaseOwner}{objectQualifier}fn_GetVersion(3,2,3)) = 0
BEGIN
INSERT INTO {databaseOwner}{objectQualifier}EventLogTypes (
LogTypeKey,
LogTypeFriendlyName,
LogTypeDescription,
LogTypeOwner,
LogTypeCSSClass)
VALUES (
'LOGIN_USERLOCKEDOUT',
'User Locked Out',
'',
'DotNetNuke.Logging.EventLogType',
'OperationFailure'
)
INSERT INTO {databaseOwner}{objectQualifier}EventLogTypes (
LogTypeKey,
LogTypeFriendlyName,
LogTypeDescription,
LogTypeOwner,
LogTypeCSSClass)
VALUES (
'LOGIN_USERNOTAPPROVED',
'User Not Approved',
'',
'DotNetNuke.Logging.EventLogType',
'OperationFailure'
)
END
GO
If (SELECT {databaseOwner}{objectQualifier}fn_GetVersion(3,2,3)) = 0
BEGIN
/* make FolderPath values consistent with Files table Folder column */
update {objectQualifier}Folders
set FolderPath = FolderPath + '/'
where FolderPath <> ''
/* Handle Updating Files table with new FolderID column based on Folders table */
update {objectQualifier}Files
set FolderID = FO.FolderID
from {objectQualifier}Files F, {objectQualifier}Folders FO
where F.PortalID = FO.PortalID
and F.Folder = FO.FolderPath
/* This sets all portal root folders to protected */
update {objectQualifier}Folders
Set IsProtected = 1
WHERE
FolderPath = ''
/* This sets all containers root folders to protected */
update {objectQualifier}Folders
Set IsProtected = 1
WHERE
FolderPath = 'Containers/'
/* This sets all skins root folders to protected */
update {objectQualifier}Folders
Set IsProtected = 1
WHERE
FolderPath = 'Skins/'
END
GO
/* New setting for banners */
/***************************/
If (SELECT {databaseOwner}{objectQualifier}fn_GetVersion(3,2,3)) = 0
BEGIN
INSERT INTO {databaseOwner}{objectQualifier}ModuleSettings
(moduleid,settingname,settingvalue)
SELECT m.ModuleID , 'padding','4'
FROM {databaseOwner}{objectQualifier}Modules m
INNER JOIN {objectQualifier}ModuleDefinitions md ON m.ModuleDefID = md.ModuleDefID
WHERE FriendlyName='Banners'
END
GO
/* Add NAV skinobject */
/**********************/
If (SELECT {databaseOwner}{objectQualifier}fn_GetVersion(3,2,4)) = 0
BEGIN
INSERT INTO {databaseOwner}[{objectQualifier}ModuleControls] ([ControlKey], [ControlSrc], [ControlType]) VALUES (N'NAV', N'Admin/Skins/Nav.ascx', -2)
END
GO
/* Update Folder Permissions */
/*****************************/
If (SELECT {databaseOwner}{objectQualifier}fn_GetVersion(3,2,4)) = 0
BEGIN
DECLARE @ReadPermissionID int
DECLARE @WritePermissionID int
DECLARE @AdminRoleID int
DECLARE @PortalID int
/* get the PermissionID for READ permissions on the SYSTEM_FOLDER */
SELECT @ReadPermissionID = PermissionID
FROM {databaseOwner}{objectQualifier}Permission
WHERE PermissionCode = 'SYSTEM_FOLDER' AND PermissionKey = 'READ'
/* get the PermissionID for WRITE permissions on the SYSTEM_FOLDER */
SELECT @WritePermissionID = PermissionID
FROM {databaseOwner}{objectQualifier}Permission
WHERE PermissionCode = 'SYSTEM_FOLDER' AND PermissionKey = 'WRITE'
--Iterate through each portal
SELECT @PortalID = min(PortalID)
FROM {databaseOwner}{objectQualifier}Portals
WHILE @PortalID is not null
BEGIN
/* get the Administrator Role ID */
SELECT @AdminRoleID = AdministratorRoleID
FROM {databaseOwner}{objectQualifier}Portals
WHERE PortalID = @PortalID
/* remove any records which already exist to avoid a duplicate key error on the insert */
DELETE
FROM {databaseOwner}{objectQualifier}FolderPermission
WHERE (PermissionID = @ReadPermissionID AND RoleID = @AdminRoleID)
OR (PermissionID = @WritePermissionID AND RoleID = @AdminRoleID)
/* give Administrators READ access to unsecure folders */
INSERT INTO {databaseOwner}{objectQualifier}FolderPermission
SELECT FolderID, @ReadPermissionID, @AdminRoleID, 1
FROM {databaseOwner}{objectQualifier}Folders
WHERE PortalID = @PortalID
/* give Administrators WRITE access to unsecure folders */
INSERT INTO {databaseOwner}{objectQualifier}FolderPermission
SELECT FolderID, @WritePermissionID, @AdminRoleID, 1
FROM {databaseOwner}{objectQualifier}Folders
WHERE PortalID = @PortalID
SELECT @PortalID = min(PortalID)
FROM {databaseOwner}{objectQualifier}Portals
WHERE PortalID > @PortalID
END
END
GO
/* Add Host Root Folder to Database */
/************************************/
If (SELECT {databaseOwner}{objectQualifier}fn_GetVersion(3,2,4)) = 0
BEGIN
DECLARE @FolderID int
SET @FolderID = (SELECT FolderID
FROM {objectQualifier}Folders
WHERE PortalID IS NULL AND FolderPath = '')
If @FolderID IS NULL
BEGIN
INSERT INTO {objectQualifier}Folders
( PortalID, FolderPath, StorageLocation, IsProtected, IsCached )
VALUES ( NULL, '', 0, 1, 0)
END
END
GO
/* Add Get User Count Procedure */
/********************************/
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetUserCountByPortal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}[{objectQualifier}GetUserCountByPortal]
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetUserCountByPortal]
@PortalId int
AS
SELECT COUNT(*) FROM {objectQualifier}vw_Users
WHERE PortalID = @PortalID
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}GetList]
GO
CREATE procedure {databaseOwner}{objectQualifier}GetList
@ListName nvarchar(50),
@ParentKey nvarchar(150),
@DefinitionID int
AS
If @ParentKey = ''
Begin
Select DISTINCT
E.[ListName],
E.[Level],
E.[DefinitionID],
E.[ParentID],
(SELECT MAX([SortOrder]) FROM {objectQualifier}Lists WHERE [ListName] = E.[ListName]) As [MaxSortOrder],
(SELECT COUNT(EntryID) FROM {objectQualifier}Lists WHERE [ListName] = E.[ListName] AND ParentID = E.[ParentID]) As EntryCount,
IsNull((SELECT [ListName] + '.' + [Value] + ':' FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') + E.[ListName] As [Key],
IsNull((SELECT [ListName] + '.' + [Text] + ':' FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') + E.[ListName] As [DisplayName],
IsNull((SELECT [ListName] + '.' + [Value] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') As [ParentKey],
IsNull((SELECT [ListName] + '.' + [Text] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') As [Parent],
IsNull((SELECT [ListName] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]),'') As [ParentList]
From {objectQualifier}Lists E (nolock)
where ([ListName] = @ListName or @ListName='')
and (DefinitionID = @DefinitionID or @DefinitionID = -1)
Order By E.[Level],[DisplayName]
End
Else
Begin
DECLARE @ParentListName nvarchar(50)
DECLARE @ParentValue nvarchar(100)
SET @ParentListName = LEFT(@ParentKey, CHARINDEX( '.', @ParentKey) - 1)
SET @ParentValue = RIGHT(@ParentKey, LEN(@ParentKey) - CHARINDEX( '.', @ParentKey))
Select DISTINCT
E.[ListName],
E.[Level],
E.[DefinitionID],
E.[ParentID],
(SELECT MAX([SortOrder]) FROM {objectQualifier}Lists WHERE [ListName] = E.[ListName]) As [MaxSortOrder],
(SELECT COUNT(EntryID) FROM {objectQualifier}Lists WHERE [ListName] = E.[ListName] AND ParentID = E.[ParentID]) As EntryCount,
IsNull((SELECT [ListName] + '.' + [Value] + ':' FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') + E.[ListName] As [Key],
IsNull((SELECT [ListName] + '.' + [Text] + ':' FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') + E.[ListName] As [DisplayName],
IsNull((SELECT [ListName] + '.' + [Value] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') As [ParentKey],
IsNull((SELECT [ListName] + '.' + [Text] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') As [Parent],
IsNull((SELECT [ListName] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]),'') As [ParentList]
From {objectQualifier}Lists E (nolock)
where [ListName] = @ListName And
[ParentID] = (SELECT [EntryID] From {objectQualifier}Lists Where [ListName] = @ParentListName And [Value] = @ParentValue)
Order By E.[Level], [DisplayName]
End
GO
/************************************************************/
/***** SqlDataProvider *****/
/************************************************************/
|