IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{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
  DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
  UPDATE {databaseOwner}{objectQualifier}DMX_Entries
   SET LastModified=@LastModified
   WHERE PortalId=@PortalId
    AND EntryId=@EntryId;
  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_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

UPDATE {databaseOwner}{objectQualifier}DMX_Entries 
 SET LastVersionID = EntryID 
 WHERE LastVersionId IS NULL 
  AND (EntryId=@EntryID OR @EntryId=-1) 
  AND PortalId=@PortalId;

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
 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);
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

IF TRIGGER_NESTLEVEL() < 2
BEGIN
 DECLARE @PortalId INT
 SELECT @PortalId = (SELECT TOP 1 PortalId FROM INSERTED);
 -- reset folder sizes on old paths
 UPDATE e SET [FileSize]=0
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN INSERTED i ON i.[Path] LIKE e.[Path]+'%'
  WHERE e.EntryType LIKE 'Collection%';
 -- reset new paths
 UPDATE e SET [Path]=NULL
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN INSERTED i ON e.[Path] LIKE i.[Path]+'%';
 EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, -1;
 -- reset last modif for new path parents
 UPDATE e SET [LastModified]=NULL
 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.EntryId = e1.EntryId;
 -- reset folder sizes on new paths
 UPDATE e SET [FileSize]=0
 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.EntryId = e1.EntryId
  WHERE e.EntryType LIKE 'Collection%';
 -- update last mod, lastversionid, folder size
 EXEC {databaseOwner}{objectQualifier}DMX_SetLastModified @PortalId;
 EXEC {databaseOwner}{objectQualifier}DMX_SetLastVersionID @PortalId;
 EXEC {databaseOwner}{objectQualifier}DMX_ResetFolderSizes @PortalId;
END


