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

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

CREATE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerInsert
ON {databaseOwner}{objectQualifier}DMX_Entries
AFTER INSERT
AS

IF TRIGGER_NESTLEVEL() < 2
BEGIN

 -- select self (in root)
 UPDATE e
 SET e.[Path] = '0;' + CAST(i.EntryId AS NVARCHAR(20)) + ';',
  e.LastModified = ISNULL(e.LastModified, GETDATE()),
  e.LastVersionId = ISNULL(e.LastVersionId, e.EntryId)
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN INSERTED i ON i.EntryId=e.EntryId
  WHERE i.CollectionId=0;

 -- select self (not in root)
 UPDATE e
 SET e.[Path] = e1.[Path] + CAST(i.EntryId AS NVARCHAR(20)) + ';',
  e.LastModified = ISNULL(e.LastModified, GETDATE()),
  e.LastVersionId = ISNULL(e.LastVersionId, e.EntryId)
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.EntryId LIKE e.CollectionId
  INNER JOIN INSERTED i ON i.EntryId=e.EntryId;
 
 -- select parents where new entry
 UPDATE e
 SET e.[FileSize] = e.[FileSize] + i.FileSize,
  e.LastModified = GETDATE()
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN INSERTED i ON i.CollectionId=e1.EntryId
  WHERE i.LastVersionId IS NULL OR i.LastVersionId=i.EntryId;

END
GO

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

CREATE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerUpdate
ON {databaseOwner}{objectQualifier}DMX_Entries
AFTER UPDATE
AS

IF TRIGGER_NESTLEVEL() < 2
 AND (UPDATE(Author) OR UPDATE(CollectionId) OR UPDATE([Entry]) OR UPDATE(EntryType) 
  OR UPDATE(IsApproved) OR UPDATE(Keywords) OR UPDATE(MD5Hash) OR UPDATE(OriginalFilename)
  OR UPDATE([State]) OR UPDATE(PermissionsOnlyByAdmin) OR UPDATE(InheritAttributes))
BEGIN

 DECLARE @PortalId INT
 SELECT @PortalId = (SELECT TOP 1 PortalId FROM INSERTED);
 DECLARE @Now DATETIME
 SET @Now = GETDATE()

-- move: select old parents
 UPDATE e
 SET e.[FileSize] = e.[FileSize] - i.FileSize
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN DELETED d ON d.CollectionId=e1.EntryId
  INNER JOIN INSERTED i ON i.EntryId=d.EntryId AND i.CollectionId<>d.CollectionId;

-- move: select new parents
 UPDATE e
 SET e.[FileSize] = e.[FileSize] + i.FileSize,
  e.LastModified = @Now
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN INSERTED i ON i.CollectionId=e1.EntryId
  INNER JOIN DELETED d ON i.EntryId=d.EntryId AND i.CollectionId<>d.CollectionId;

-- move: select self and children
 UPDATE e 
 SET [Path]=NULL
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN DELETED d ON e.[Path] LIKE d.[Path]+'%'
  INNER JOIN INSERTED i ON i.EntryId=d.EntryId AND i.CollectionId<>d.CollectionId;
 EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, -1;

-- nomove/nonewversion: select parents
 UPDATE e
 SET e.LastModified = @Now
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN INSERTED i ON i.CollectionId=e1.EntryId
  INNER JOIN DELETED d ON i.EntryId=d.EntryId AND i.CollectionId=d.CollectionId AND i.FileSize=d.FileSize;

-- nomove/newversion: select parents
 UPDATE e
 SET e.LastModified = @Now,
  e.FileSize = e.FileSize - d.FileSize + i.FileSize
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN INSERTED i ON i.CollectionId=e1.EntryId
  INNER JOIN DELETED d ON i.EntryId=d.EntryId AND i.CollectionId=d.CollectionId AND i.FileSize<>d.FileSize;

-- move: select self
 UPDATE e
 SET e.LastModified = @Now
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN INSERTED i ON i.EntryId=e.EntryId
  INNER JOIN DELETED d ON i.EntryId=d.EntryId AND d.LastModified=i.LastModified;
 
END
GO

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

CREATE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerDelete
ON {databaseOwner}{objectQualifier}DMX_Entries
AFTER DELETE
AS

IF TRIGGER_NESTLEVEL() < 2
BEGIN

 DECLARE @PortalId INT
 SELECT @PortalId = (SELECT TOP 1 PortalId FROM INSERTED);

-- select parents
 UPDATE e
 SET e.[FileSize] = e.[FileSize] - d.FileSize
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN DELETED d ON d.CollectionId=e1.EntryId
  WHERE d.EntryId=d.LastVersionId;
 
END
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetLastModified
 @PortalId Int,
 @EntryId Int = -1,
 @LastModified DATETIME = NULL
AS

IF @LastModified IS NULL
 BEGIN
  IF @EntryId=-1
   BEGIN
    UPDATE {databaseOwner}{objectQualifier}DMX_Entries
    SET LastModified=GETDATE()
    WHERE PortalId=@PortalId
    AND LastModified IS NULL;
   END
  ELSE
   BEGIN
    DECLARE @entrypath VARCHAR(4000)
    SET @entrypath = (SELECT [Path] 
      FROM {databaseOwner}{objectQualifier}DMX_Entries
      WHERE EntryId = @EntryId);
    UPDATE {databaseOwner}{objectQualifier}DMX_Entries
     SET LastModified=GETDATE()
     WHERE PortalId=@PortalId
    AND @entrypath LIKE [Path]+'%';
   END
 END
ELSE
 BEGIN
  UPDATE {databaseOwner}{objectQualifier}DMX_Entries
   SET LastModified=@LastModified
   WHERE PortalId=@PortalId
    AND EntryId=@EntryId;
 END

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdatePaths
 @PortalID INT,
 @EntryId INT = NULL
AS

BEGIN

-- NULLIFY what is 'dirty' 
IF @EntryId is NULL 
 BEGIN
  UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET [Path]=NULL WHERE PortalID = @PortalID;
 END 
ELSE 
 BEGIN
  DECLARE @OldPath VARCHAR(4000)
  SELECT @OldPath = (SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId)
  UPDATE {databaseOwner}{objectQualifier}DMX_Entries 
   SET [Path]=NULL 
  WHERE [Path] LIKE @OldPath + '%' 
   AND PortalID = @PortalID 
 END

-- If we have any root items with NULL we set the [Path] for these 
UPDATE {databaseOwner}{objectQualifier}DMX_Entries 
 SET [Path] = '0;' + Cast(EntryId as NVARCHAR(32)) + ';' 
WHERE
 CollectionId=0
 And PortalId=@PortalId
 AND [Path] IS NULL

-- As long as we find items that are nullified but whose parents are not NULL, we set the path for them
WHILE EXISTS 
 (SELECT 'X' 
  FROM {databaseOwner}{objectQualifier}DMX_Entries 
  WHERE 
   PortalId=@PortalId
   AND [Path] IS NULL
   AND EXISTS (SELECT 'X' 
               FROM {databaseOwner}{objectQualifier}DMX_Entries e1 
               WHERE e1.[Path] IS NOT NULL 
                AND e1.EntryID={databaseOwner}{objectQualifier}DMX_Entries.CollectionID))
 BEGIN
  UPDATE {databaseOwner}{objectQualifier}DMX_Entries
   SET [Path] = (SELECT e2.[Path] + CAST({databaseOwner}{objectQualifier}DMX_Entries.EntryId AS NVARCHAR(32)) + ';' 
                 FROM {databaseOwner}{objectQualifier}DMX_Entries e2 
                 WHERE e2.EntryID={databaseOwner}{objectQualifier}DMX_Entries.CollectionID)
  WHERE 
   {databaseOwner}{objectQualifier}DMX_Entries.PortalId=@PortalId
   AND [Path] IS NULL
   AND EXISTS (SELECT 'X' 
               FROM {databaseOwner}{objectQualifier}DMX_Entries e3 
               WHERE e3.[Path] IS NOT NULL 
                AND e3.EntryID={databaseOwner}{objectQualifier}DMX_Entries.CollectionID)
 END;

END
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntry
	@EntryId Int,
	@Author NVARCHAR(500),
	@CollectionId Int,
	@Created DateTime,
	@Deleted Bit,
	@Entry NVARCHAR(2000),
	@EntryType NVARCHAR(150),
	@FileSize BIGINT,
	@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

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;

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_LockEntry
 @PortalId Int,
 @EntryId Int,
	@LockedUntil DateTime,
	@LockedBy Int,
	@LockKey UNIQUEIDENTIFIER
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET
	[LockedBy] = @LockedBy,
	[LockedUntil] = @LockedUntil,
	[LockKey] = @LockKey
WHERE
	([EntryId] = @EntryId OR LastVersionId=@EntryId)
 AND [PortalId] = @PortalId;

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UnLockEntry
 @PortalId Int,
 @EntryId Int
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET
	[LockedBy] = -1,
	[LockedUntil] = GETDATE(),
	[LockKey] = NULL
WHERE
	([EntryId] = @EntryId OR LastVersionId=@EntryId)
 AND [PortalId] = @PortalId;

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubscriptionsForEntry
	@EntryId INT
AS

BEGIN
 DECLARE @BasePath VARCHAR(2000)
 SELECT @BasePath = ';'+(SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryID);
 SELECT * FROM {databaseOwner}{objectQualifier}DMX_Subscriptions s
 WHERE @BasePath LIKE '%;'+CAST(s.EntryId AS VARCHAR(10))+';%'
 AND EXISTS (SELECT ep1.EntryId 
  FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep1
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions Permission ON Permission.PermissionId=ep1.PermissionId 
  LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur ON ur.RoleId=ep1.RoleId
  WHERE ep1.EntryId=@EntryId AND (ur.UserID=s.UserId OR ep1.UserId=s.UserId OR ep1.RoleId=-1)
   AND Permission.PermissionKey='VIEW')
END
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionsForEntrytype
 @PortalId INT,
 @EntryType NVARCHAR(100),
 @UserId INT
AS

BEGIN
IF @UserID=-4

SELECT p.*
FROM {databaseOwner}{objectQualifier}DMX_Permissions p 
 INNER JOIN {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes pe ON p.PermissionId=pe.PermissionId
WHERE (p.PortalId=@PortalID)
 AND @Entrytype LIKE pe.Entrytype+'%';

ELSE

SELECT p.*,
 CAST((SELECT COUNT(PermissionId) FROM {databaseOwner}{objectQualifier}DMX_PermissionPermissions pp 
   LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur ON ur.RoleId=pp.RoleId 
   WHERE pp.PermissionId=p.PermissionId 
    AND (ur.UserId=@UserId OR pp.RoleId=-1)) AS Bit) AS IsAllowedToEdit
FROM {databaseOwner}{objectQualifier}DMX_Permissions p 
 INNER JOIN {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes pe ON p.PermissionId=pe.PermissionId
WHERE (p.PortalId=@PortalID)
 AND @Entrytype LIKE pe.Entrytype+'%';

END

GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSpecificEntryPermission
 @PortalId INT,
 @EntryId INT,
 @PermissionKey NVARCHAR(20),
 @UserId INT
AS

SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON ep.PermissionId=p.PermissionId
 LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur ON ep.RoleId=ur.RoleId
WHERE (ep.UserID=@UserId
 OR ur.UserId=@UserId
 OR ep.RoleId=-1)
 AND ep.PortalId=@PortalId
 AND ep.EntryId=@EntryId
 AND p.PermissionKey=@PermissionKey

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionsByPortal
 @PortalId INT,
 @UserId INT = -1
AS

SELECT p.*, 
 CAST((SELECT COUNT(PermissionId) FROM {databaseOwner}{objectQualifier}DMX_PermissionPermissions pp 
   LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur ON ur.RoleId=pp.RoleId 
   WHERE pp.PermissionId=p.PermissionId 
    AND (ur.UserId=@UserId OR pp.RoleId=-1)) AS Bit) AS IsAllowedToEdit 
 FROM {databaseOwner}{objectQualifier}DMX_Permissions p 
 WHERE (p.PortalId=@PortalID)

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryPermissionsForUser
 @PortalId INT,
 @EntryId INT,
 @UserId INT
AS

SELECT p.* FROM {databaseOwner}{objectQualifier}DMX_Permissions p 
 INNER JOIN {databaseOwner}{objectQualifier}DMX_EntryPermissions ep ON p.PermissionId=ep.PermissionId 
 LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur ON ur.RoleId=ep.RoleId 
WHERE 
 (ep.RoleId=-1 OR ur.UserId=@UserId OR ep.UserId=@UserId) 
 AND (ep.EntryId=@EntryId) 
 AND (ep.PortalId=@PortalId)

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{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
 IF @EntryId > 0
 BEGIN
  DECLARE @entrypath VARCHAR(4000)
  SET @entrypath = (SELECT [Path] 
    FROM {databaseOwner}{objectQualifier}DMX_Entries
    WHERE EntryId = @EntryId);
  UPDATE {databaseOwner}{objectQualifier}DMX_Entries
  SET FileSize=NULL
  WHERE PortalId=@PortalId
  AND @entrypath LIKE [Path]+'%'
  AND EntryType LIKE 'Collection%';
 END
 IF @EntryId = -1
  UPDATE {databaseOwner}{objectQualifier}DMX_Entries
  SET FileSize=NULL
  WHERE PortalId=@PortalId
  AND EntryType LIKE 'Collection%';
 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 IS NULL
  AND (e.PortalId=@PortalId OR @PortalId=-1);
END

GO

