IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_LimitSearch') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_LimitSearch
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_LimitSearch
 @PortalId Int,
 @SearchId nvarchar(50),
 @CollectionId Int,
 @OnlyLastVersion BIT,
 @HideDeleted BIT,
 @OnlyApproved BIT
AS

BEGIN
 IF @CollectionId > -1
 BEGIN
  DECLARE @entrypath VARCHAR(4000)
  SET @entrypath = (SELECT [Path] 
   FROM {databaseOwner}{objectQualifier}DMX_Entries
   WHERE EntryId = @CollectionId);
  DELETE FROM {databaseOwner}{objectQualifier}DMX_SearchResults
   FROM {databaseOwner}{objectQualifier}DMX_SearchResults sr
   INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON sr.EntryId=e.EntryId
   WHERE NOT e.[Path] LIKE @entrypath+'%';
 END
 IF @OnlyLastVersion = 1
 BEGIN
  DELETE FROM {databaseOwner}{objectQualifier}DMX_SearchResults
   FROM {databaseOwner}{objectQualifier}DMX_SearchResults sr
   INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON sr.EntryId=e.EntryId
   WHERE NOT e.LastVersionId = e.EntryId;
 END
 IF @HideDeleted = 1
 BEGIN
  DELETE FROM {databaseOwner}{objectQualifier}DMX_SearchResults
   FROM {databaseOwner}{objectQualifier}DMX_SearchResults sr
   INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON sr.EntryId=e.EntryId
   WHERE e.Deleted = 1;
 END
 IF @OnlyApproved = 1
 BEGIN
  DELETE FROM {databaseOwner}{objectQualifier}DMX_SearchResults
   FROM {databaseOwner}{objectQualifier}DMX_SearchResults sr
   INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON sr.EntryId=e.EntryId
   WHERE e.IsApproved = 0;
 END
END

GO

-- provider mechanism enhancements
if not exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_Providers') and OBJECTPROPERTY(id, N'IsTable') = 1)
CREATE TABLE {databaseOwner}{objectQualifier}DMX_Providers (
 [Provider] NVARCHAR (255) NOT NULL,
 [ProviderID] INT IDENTITY (1, 1),
 [ProviderName] NVARCHAR (255) NOT NULL,
 [ProviderType] INT NOT NULL
) ON [PRIMARY]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'PK_{objectQualifier}DMX_Providers') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Providers ADD CONSTRAINT
	PK_{objectQualifier}DMX_Providers PRIMARY KEY CLUSTERED 
	(
	[ProviderID]
	) ON [PRIMARY]
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetProvider') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetProvider
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetProvider
	@ProviderID Int
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Providers
WHERE
	[ProviderID] = @ProviderID
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetProvidersByType') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetProvidersByType
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetProvidersByType
	@ProviderType Int
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Providers
WHERE
	[ProviderType] = @ProviderType
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_ListProviders') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_ListProviders
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_ListProviders
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Providers
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddProvider') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddProvider
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddProvider
	@Provider NVarChar (255),
	@ProviderName NVarChar (255),
	@ProviderType Int
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Providers (
	[Provider],
	[ProviderName],
	[ProviderType]
) VALUES (
	@Provider,
	@ProviderName,
	@ProviderType
)

select SCOPE_IDENTITY()
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateProvider') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateProvider
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateProvider
	@ProviderID Int,
	@Provider NVarChar (255),
	@ProviderName NVarChar (255),
	@ProviderType Int
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Providers SET
	[Provider] = @Provider,
	[ProviderName] = @ProviderName,
	[ProviderType] = @ProviderType
WHERE
	[ProviderID] = @ProviderID
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_DeleteProvider') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteProvider
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteProvider
	@ProviderID Int
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Providers
WHERE
	[ProviderID] = @ProviderID
GO

-- load up providers for the first time
SET IDENTITY_INSERT {databaseOwner}{objectQualifier}DMX_Providers ON;
INSERT INTO {databaseOwner}{objectQualifier}DMX_Providers ([ProviderID], [Provider],	[ProviderName],	[ProviderType])
VALUES (1, 'Bring2mind.DNN.Modules.DMX.Services.Storage.FileStorageProvider.FileStorageProvider, BRING2MIND.DNN.MODULES.DMX', 'FileStorageProvider', 1);
INSERT INTO {databaseOwner}{objectQualifier}DMX_Providers ([ProviderID], [Provider],	[ProviderName],	[ProviderType])
VALUES (2, 'Bring2mind.DNN.Modules.DMX.Services.Storage.SqlStorageProvider.SqlStorageProvider, BRING2MIND.DNN.MODULES.DMX', 'SqlStorageProvider', 1);
INSERT INTO {databaseOwner}{objectQualifier}DMX_Providers ([ProviderID], [Provider],	[ProviderName],	[ProviderType])
VALUES (3, 'Bring2mind.DNN.Modules.DMX.Services.Storage.S3StorageProvider.S3StorageProvider, BRING2MIND.DNN.MODULES.DMX', 'S3StorageProvider', 1);
INSERT INTO {databaseOwner}{objectQualifier}DMX_Providers ([ProviderID], [Provider],	[ProviderName],	[ProviderType])
VALUES (4, 'Bring2mind.DNN.Modules.DMX.Services.Search.LuceneSearchProvider.LuceneSearchProvider, BRING2MIND.DNN.MODULES.DMX', 'LuceneSearchProvider', 2);
INSERT INTO {databaseOwner}{objectQualifier}DMX_Providers ([ProviderID], [Provider],	[ProviderName],	[ProviderType])
VALUES (5, 'Bring2mind.DNN.Modules.DMX.Services.Search.IndexingServiceSearchProvider.IndexingServiceSearchProvider, BRING2MIND.DNN.MODULES.DMX', 'IndexingServiceSearchProvider', 2);
INSERT INTO {databaseOwner}{objectQualifier}DMX_Providers ([ProviderID], [Provider],	[ProviderName],	[ProviderType])
VALUES (6, 'Bring2mind.DNN.Modules.DMX.Services.Search.ISRemoteSqlSearchProvider.ISRemoteSqlSearchProvider, BRING2MIND.DNN.MODULES.DMX', 'ISRemoteSqlSearchProvider', 2);
SET IDENTITY_INSERT {databaseOwner}{objectQualifier}DMX_Providers OFF;

-- storage provider adjustments
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Entries ADD
 StorageProviderID INT
GO

ALTER TABLE {databaseOwner}{objectQualifier}DMX_Entries
ALTER COLUMN Entry NVARCHAR(2000)
GO

DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
UPDATE {databaseOwner}{objectQualifier}DMX_Entries
SET StorageProviderID = 1;
ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntry
	@Author NVARCHAR(500),
	@CollectionId Int,
	@Created DateTime,
	@Deleted Bit,
	@Entry NVARCHAR(2000),
	@EntryType NVARCHAR(150),
	@FileSize Int,
	@InheritAttributes Bit,
	@IsApproved Bit,
	@Keywords NVARCHAR(255),
	@LastVersionId Int,
	@LockedBy Int,
	@LockedUntil DateTime,
	@LockKey uniqueidentifier,
	@MD5Hash NVARCHAR(50),
	@OriginalFileName NVARCHAR(255),
	@Owner Int,
	@Path NVARCHAR(4000),
	@PermissionsOnlyByAdmin Bit,
	@PortalId Int,
	@State NVARCHAR(50),
 @StorageProviderID INT,
	@Version Int,
	@VersionsToKeep Int,
	@WorkflowId NVARCHAR(50)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Entries (
	[Author],
	[CollectionId],
	[Created],
	[Deleted],
	[Entry],
	[EntryType],
	[FileSize],
	[InheritAttributes],
	[IsApproved],
	[Keywords],
	[LastVersionId],
	[LockedBy],
	[LockedUntil],
	[LockKey],
	[MD5Hash],
	[OriginalFileName],
	[Owner],
	[Path],
	[PermissionsOnlyByAdmin],
	[PortalId],
	[State],
 [StorageProviderID],
	[Version],
	[VersionsToKeep],
	[WorkflowId]
) VALUES (
	@Author,
	@CollectionId,
	@Created,
	@Deleted,
	@Entry,
	@EntryType,
	@FileSize,
	@InheritAttributes,
	@IsApproved,
	@Keywords,
	@LastVersionId,
	@LockedBy,
	@LockedUntil,
	@LockKey,
	@MD5Hash,
	@OriginalFileName,
	@Owner,
	@Path,
	@PermissionsOnlyByAdmin,
	@PortalId,
	@State,
 @StorageProviderID,
	@Version,
	@VersionsToKeep,
	@WorkflowId
)

select SCOPE_IDENTITY()
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntry
	@EntryId Int,
 @BypassTriggers BIT,
	@Author NVARCHAR(500),
	@CollectionId Int,
	@Created DateTime,
	@Deleted Bit,
	@Entry NVARCHAR(2000),
	@EntryType NVARCHAR(150),
	@FileSize Int,
	@InheritAttributes Bit,
	@IsApproved Bit,
	@Keywords NVARCHAR(255),
	@LastVersionId Int,
	@LockedBy Int,
	@LockedUntil DateTime,
	@LockKey uniqueidentifier,
	@MD5Hash NVARCHAR(50),
	@OriginalFileName NVARCHAR(255),
	@Owner Int,
	@Path NVARCHAR(4000),
	@PermissionsOnlyByAdmin Bit,
	@PortalId Int,
	@State NVARCHAR(50),
 @StorageProviderID INT,
	@Version Int,
	@VersionsToKeep Int,
	@WorkflowId NVARCHAR(50)
AS

IF @BypassTriggers=1
DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET
	[Author] = @Author,
	[CollectionId] = @CollectionId,
	[Created] = @Created,
	[Deleted] = @Deleted,
	[Entry] = @Entry,
	[EntryType] = @EntryType,
	[FileSize] = @FileSize,
	[InheritAttributes] = @InheritAttributes,
	[IsApproved] = @IsApproved,
	[Keywords] = @Keywords,
	[LastVersionId] = @LastVersionId,
	[LockedBy] = @LockedBy,
	[LockedUntil] = @LockedUntil,
	[LockKey] = @LockKey,
	[MD5Hash] = @MD5Hash,
	[OriginalFileName] = @OriginalFileName,
	[Owner] = @Owner,
	[Path] = @Path,
	[PermissionsOnlyByAdmin] = @PermissionsOnlyByAdmin,
	[PortalId] = @PortalId,
	[State] = @State,
 [StorageProviderID]=@StorageProviderID,
	[Version] = @Version,
	[VersionsToKeep] = @VersionsToKeep,
	[WorkflowId] = @WorkflowId
WHERE
	[EntryId] = @EntryId;
IF @BypassTriggers=1
ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;

GO


-- new storage method stuff
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetEntriesByPortalAndProvider') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByPortalAndProvider
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByPortalAndProvider
 @PortalId INT,
 @ProviderId INT
AS

SELECT
 *,
 '' AS Title,
 '' AS Remarks
FROM
 {databaseOwner}{objectQualifier}DMX_Entries
WHERE
	[PortalId] = @PortalId
 AND [StorageProviderID]=@ProviderID
GO

-- catch LastVersionId not being set
DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET LastVersionID = EntryID WHERE LastVersionId IS NULL;
UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET LastModified = GETDATE() WHERE LastModified IS NULL;
ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_SetLastVersionID') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetLastVersionID
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetLastVersionID
 @PortalId INT,
 @EntryId INT = -1
AS

 DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET LastVersionID = EntryID WHERE LastVersionId IS NULL AND (EntryId=@EntryID OR @EntryId=-1) AND PortalId=@PortalId;
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET LastModified = GETDATE() WHERE LastModified IS NULL AND (EntryId=@EntryID OR @EntryId=-1) AND PortalId=@PortalId;
 ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_ResetFolderSizes') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_ResetFolderSizes
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_ResetFolderSizes
 @PortalId Int,
 @EntryId Int = -1
AS

BEGIN
 DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
 IF @EntryId > -1
 BEGIN
  DECLARE @entrypath VARCHAR(4000)
  SET @entrypath = (SELECT [Path] 
    FROM {databaseOwner}{objectQualifier}DMX_Entries
    WHERE EntryId = @EntryId);
  UPDATE {databaseOwner}{objectQualifier}DMX_Entries
  SET FileSize=0
  WHERE PortalId=@PortalId
  AND @entrypath LIKE [Path]+'%'
  AND EntryType LIKE 'Collection%';
 END
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET FileSize=
  (SELECT ISNULL(SUM(e1.FileSize), 0) FROM {databaseOwner}{objectQualifier}DMX_Entries e1 
  WHERE e1.EntryType LIKE 'File%'
  AND e1.[Path] LIKE e.Path+'%'
  AND e1.PortalId=e.PortalId
  AND e1.LastVersionId=e1.EntryId)
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 WHERE e.EntryType LIKE 'Collection%'
  AND e.FileSize=0
  AND (e.PortalId=@PortalId OR @PortalId=-1);
 ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
END

GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_EntryUpdatePath') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath
GO

CREATE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath
ON {databaseOwner}{objectQualifier}DMX_Entries
AFTER INSERT, UPDATE
AS

 DECLARE @EntryId INT
 DECLARE @PortalId INT
 DECLARE @RowCount INT
 SELECT @RowCount=(SELECT COUNT(*) FROM INSERTED)

 IF @RowCount=1
  BEGIN
   SELECT @EntryId = (SELECT EntryId FROM INSERTED);
   SELECT @PortalId = (SELECT PortalId FROM INSERTED);
   DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
   EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, @EntryId;
   ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
   EXEC {databaseOwner}{objectQualifier}DMX_SetLastModified @PortalId, @EntryId;
   EXEC {databaseOwner}{objectQualifier}DMX_SetLastVersionID @PortalId, @EntryId;
   EXEC {databaseOwner}{objectQualifier}DMX_ResetFolderSizes @PortalId, @EntryId;
  END
 ELSE
  BEGIN
   DECLARE cursorX CURSOR FOR
   SELECT INSERTED.EntryId, INSERTED.PortalID FROM INSERTED INNER JOIN DELETED
    ON INSERTED.EntryId= DELETED.EntryId
    WHERE INSERTED.CollectionId <> DELETED.EntryId;
   BEGIN
    OPEN cursorX
    FETCH cursorX
    INTO @EntryId, @PortalId
    WHILE @@fetch_status=0
    BEGIN
     FETCH cursorX
     INTO @EntryId, @PortalId;
     DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
  	  EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, @EntryId;
     ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
  	  EXEC {databaseOwner}{objectQualifier}DMX_SetLastModified @PortalId, @EntryId;
     EXEC {databaseOwner}{objectQualifier}DMX_SetLastVersionID @PortalId, @EntryId;
     EXEC {databaseOwner}{objectQualifier}DMX_ResetFolderSizes @PortalId, @EntryId;
    END;
    CLOSE cursorX;
    DEALLOCATE cursorX;
   END;
  END
  
GO

EXEC {databaseOwner}{objectQualifier}DMX_ResetFolderSizes -1, -1;
GO

-- changed and added log methods
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetLogsByEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByEntry
	@EntId INT,
 @MaxRecords INT
AS

IF @MaxRecords > 0
 BEGIN
  DECLARE @SQL varchar(max);
  SET @SQL = 'SELECT TOP ' + Convert(varchar, @MaxRecords);
  SET @SQL = @SQL + ' l.*, u.DisplayName';
  SET @SQL = @SQL + ' FROM {databaseOwner}{objectQualifier}Users u INNER JOIN {databaseOwner}{objectQualifier}DMX_Log l ON u.UserID = l.UserId';
  SET @SQL = @SQL + ' WHERE l.EntId = ' + Convert(varchar, @EntId) + ' ORDER BY l.Datime DESC';
  EXEC(@SQL);
 END
ELSE
 SELECT
  l.*,
  u.DisplayName
 FROM
  {databaseOwner}{objectQualifier}Users u INNER JOIN {databaseOwner}{objectQualifier}DMX_Log l ON u.UserID = l.UserId
 WHERE
  l.EntId = @EntId
 ORDER BY l.Datime DESC;

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_MoveLog') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveLog
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveLog
	@OldEntryId INT,
 @NewEntryId INT
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Log
SET EntId=@NewEntryId WHERE EntId=@OldEntryId
GO

-- change length of short texts (titles, names etc)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_ShortTexts
ALTER COLUMN [Text] NVARCHAR(1000)
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddShortText') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddShortText
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddShortText
	@TypeKey NVarChar (3),	
	@SubTypeKey NVarChar (20),	
	@ObjectId Int,	
	@Locale NVarChar (6),
	@Text NVarChar (1000)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_ShortTexts (
	[TypeKey],
	[SubTypeKey],
	[ObjectId],
	[Locale],
	Text
) VALUES (
	@TypeKey,
	@SubTypeKey,
	@ObjectId,
	@Locale,
	@Text
)
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_UpdateShortText') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateShortText
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateShortText
	@TypeKey NVarChar (3),
	@SubTypeKey NVarChar (20),
	@ObjectId Int,
	@Locale NVarChar (6),
	@Text NVarChar (1000)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_ShortTexts SET
	[Text] = @Text
WHERE
	[TypeKey] = @TypeKey
	AND [SubTypeKey] = @SubTypeKey
	AND [ObjectId] = @ObjectId
	AND [Locale] = @Locale
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_RenameEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_RenameEntry
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_RenameEntry
 @PortalId Int,
 @EntryId Int,
 @Locale NVarChar(10),
 @NewName nvarchar(1000)
AS
 IF EXISTS(SELECT EntryId FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId AND PortalId=@PortalId)
  BEGIN
   DELETE FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE 
    ObjectId=@EntryId
    AND TypeKey='ENT'
    AND SubTypeKey='TITLE'
    AND Locale=@Locale
   INSERT INTO {databaseOwner}{objectQualifier}DMX_ShortTexts 
    (ObjectId, TypeKey, SubTypeKey, Locale, Text)
    VALUES
    (@EntryId, 'ENT', 'TITLE', @Locale, @NewName)
  END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddFolder') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddFolder
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddFolder
 @PortalId INT,
 @CollectionId INT=0,
 @FolderName NVARCHAR(1000)='New Folder',
 @ViewRoles VARCHAR(2000)='',
 @EditRoles VARCHAR(2000)='',
 @AddRoles VARCHAR(2000)='',
 @ApproveRoles VARCHAR(2000)=''
AS

BEGIN
 DECLARE @Owner INT
 SET @Owner = (SELECT AdministratorId FROM {databaseOwner}{objectQualifier}Portals WHERE PortalId=@PortalId)
 DECLARE @NewId INT
 INSERT INTO {databaseOwner}{objectQualifier}DMX_Entries
  (CollectionId, Created, Deleted, Entry, EntryType, IsApproved, Owner, PortalId, Version, PermissionsOnlyByAdmin)
  VALUES
  (@CollectionId, GETDATE(), 0, '', 'Collection', 1, @Owner, @PortalId, 1, 0);
 SET @NewId = SCOPE_IDENTITY()
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
  SET LastVersionId=EntryId
  WHERE EntryId=@NewId;
 INSERT INTO {databaseOwner}{objectQualifier}DMX_ShortTexts
  (Locale, ObjectId, SubTypeKey, [Text], TypeKey)
  VALUES ('', @NewId, 'TITLE', @FolderName, 'ENT');
 INSERT INTO {databaseOwner}{objectQualifier}DMX_Log
  ([Action],[Datime],[EntId],[UserId])
 VALUES ('Add', GETDATE(), @NewId, @Owner);
 DECLARE @AdminRoleId INT
 SET @AdminRoleId = (SELECT AdministratorRoleId FROM {databaseOwner}{objectQualifier}Portals WHERE PortalId=@PortalId)
 IF @ViewRoles='' AND @EditRoles='' AND @AddRoles='' AND @ApproveRoles=''
  BEGIN
   IF @CollectionId=0
    BEGIN
     -- set Admin roles for default folder under root
     INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
      ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
     SELECT 1, @NewId, PermissionId, @PortalId, @AdminRoleId, -10
      FROM {databaseOwner}{objectQualifier}DMX_Permissions
      WHERE PortalId=@PortalId AND (PermissionKey='VIEW' OR PermissionKey='EDIT' OR PermissionKey='ADD');
    END
   ELSE
    -- Inherit permissions from parent
    INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
     ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
    SELECT 1, @NewId, [PermissionId],[PortalId],[RoleId],[UserId]
     FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
     WHERE EntryId=@CollectionId;
  END
 ELSE
  BEGIN
   -- set View roles
   INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
    ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
   SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
    FROM {databaseOwner}{objectQualifier}DMX_Split(@ViewRoles, ';') spl1
     INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10))=spl1.s OR r1.Rolename=spl1.s)
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='VIEW')
    WHERE NOT @ViewRoles=''
      AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
        WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10);
   -- set Edit roles
   INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
    ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
   SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
    FROM {databaseOwner}{objectQualifier}DMX_Split(@EditRoles, ';') spl1
     INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10))=spl1.s OR r1.Rolename=spl1.s)
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='EDIT')
    WHERE NOT @EditRoles=''
      AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
        WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10);
   -- set Add roles
   INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
    ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
   SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
    FROM {databaseOwner}{objectQualifier}DMX_Split(@AddRoles, ';') spl1
     INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10))=spl1.s OR r1.Rolename=spl1.s)
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='ADD')
    WHERE NOT @AddRoles=''
      AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
        WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10);
   -- set Approve roles
   INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
    ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
   SELECT DISTINCT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
    FROM {databaseOwner}{objectQualifier}DMX_Split(@ApproveRoles, ';') spl1
     INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10))=spl1.s OR r1.Rolename=spl1.s)
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='APPROVE')
    WHERE NOT @ApproveRoles=''
      AND NOT EXISTS (SELECT 'X' FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
        WHERE [AllowAccess]=1 AND [EntryId]= @NewId AND [PermissionId]=p1.PermissionId AND [PortalId]= @PortalId AND [RoleId]=r1.RoleId AND [UserId]=-10);
   -- make sure Admin is part of this
   INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
    ([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
   SELECT DISTINCT 1, @NewId, p.PermissionId, @PortalId, @AdminRoleId, -10
    FROM {databaseOwner}{objectQualifier}DMX_Permissions p
    WHERE p.PortalId=@PortalId AND (p.PermissionKey='VIEW' OR p.PermissionKey='EDIT' OR p.PermissionKey='ADD')
    AND NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
        WHERE ep.PermissionId=p.PermissionId AND ep.EntryId=@NewId AND ep.RoleId=@AdminRoleId);
  END
END
GO

-- change to attribute keys and naming
UPDATE {databaseOwner}{objectQualifier}DMX_Attributes
SET [Key]='att'+CAST(AttributeId AS NVARCHAR(10))
WHERE ISNULL([Key], '')=''
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAttribute
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAttribute
	@Addon NVarChar (50),
	@CollectionId Int,
	@ControlToLoad NVarChar (255),
	@IsPrivate Bit,
	@Key NVarChar (50),
	@PortalId Int,
	@Required Bit,
	@ResourceFile NVarChar (255),
	@ShowInUI Bit,
	@Values NVarChar (255),
	@ValueType NVarChar (30),
	@ViewOrder Int
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Attributes (
	[Addon],
	[CollectionId],
	[ControlToLoad],
	[IsPrivate],
	[Key],
	[PortalId],
	[Required],
	[ResourceFile],
	[ShowInUI],
	[Values],
	[ValueType],
	[ViewOrder]
) VALUES (
	@Addon,
	@CollectionId,
	@ControlToLoad,
	@IsPrivate,
	@Key,
	@PortalId,
	@Required,
	@ResourceFile,
	@ShowInUI,
	@Values,
	@ValueType,
	@ViewOrder
);
UPDATE {databaseOwner}{objectQualifier}DMX_Attributes
SET [Key]='att'+CAST(AttributeId AS NVARCHAR(10))
WHERE ISNULL([Key], '')='';

select SCOPE_IDENTITY()
GO

-- fix to be able to pull out cats alphabetically
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetSubCategories') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubCategories
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubCategories
 @PortalId INT,
 @CategoryId INT,
 @Locale NVARCHAR(6)
AS

SELECT c.*, ISNULL(stLoc.[Text], stDef.[Text]) Name
FROM {databaseOwner}{objectQualifier}DMX_Categories c
 INNER JOIN {databaseOwner}{objectQualifier}DMX_ShortTexts stDef ON c.Id=stDef.ObjectId AND stDef.TypeKey='CAT' AND stDef.SubTypeKey='NAME' AND stDef.Locale=''
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_ShortTexts stLoc ON c.Id=stLoc.ObjectId AND stLoc.TypeKey='CAT' AND stLoc.SubTypeKey='NAME' AND stLoc.Locale=@Locale
WHERE c.PortalId=@PortalID
 AND c.ParentId=@CategoryId
ORDER BY ViewOrder, Name

GO

-- fix for DNN 5.2
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetPortal') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPortal
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPortal
 @PortalId INT,
 @Locale NVARCHAR(6)
AS

DECLARE @DNNVersion INT
SET @DNNVersion = (SELECT MAX(v) FROM
(SELECT Major * 10000 + Minor * 100 + Build AS v FROM {databaseOwner}{objectQualifier}Version) AS x)
IF @DNNVersion > 50200
 BEGIN
  IF EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}PortalLocalization WHERE PortalId=@PortalId AND CultureCode=@Locale)
  SELECT * FROM {databaseOwner}{objectQualifier}Portals p
   INNER JOIN {databaseOwner}{objectQualifier}PortalLocalization pl ON p.PortalId=pl.PortalId
   WHERE p.PortalId=@PortalId
   AND pl.CultureCode=@Locale
  ELSE
  SELECT * FROM {databaseOwner}{objectQualifier}Portals p
   INNER JOIN {databaseOwner}{objectQualifier}PortalLocalization pl ON p.PortalId=pl.PortalId
   WHERE p.PortalId=@PortalId
   AND pl.CultureCode=p.DefaultLanguage
 END
ELSE
 BEGIN
  SELECT * FROM {databaseOwner}{objectQualifier}Portals
   WHERE PortalId=@PortalId
 END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_GetAttributesByPortal') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByPortal
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByPortal
	@PortalId Int,
 @Locale NVarChar(10)
AS

SELECT
	a.*,
 ISNULL(ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale='')), a.[Key]) [Name],
	ad.Description AS AddonsDescription
FROM
	{databaseOwner}{objectQualifier}DMX_Addons ad 
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Attributes a ON ad.AddonKey = a.Addon
WHERE
	a.PortalId = @PortalId
GO

