IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{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

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

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


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{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

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'{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

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{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

/****** ADD SQL Storage Stuff ******/

if not exists (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_Repository') and OBJECTPROPERTY(id, N'IsTable') = 1)
CREATE TABLE {databaseOwner}{objectQualifier}DMX_Repository (
 [Blob] VARBINARY(MAX),
 [BlobId] INT IDENTITY (1, 1),
 [PortalId] INT
) ON [PRIMARY]
GO

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

/****** PROCEDURES ******/

if not exists (select * from dbo.sysobjects where id = object_id(N'FK_{objectQualifier}DMX_Repository_{objectQualifier}Portals') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Repository ADD CONSTRAINT
	FK_{objectQualifier}DMX_Repository_{objectQualifier}Portals FOREIGN KEY
	(
    PortalId
	) REFERENCES {databaseOwner}{objectQualifier}Portals
	(
    PortalID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetFromRepository
	@BlobId Int,
 @PortalId int
AS

SELECT
	[Blob],
	[BlobId],
	[PortalId]
FROM
	{databaseOwner}{objectQualifier}DMX_Repository
WHERE
	[BlobId] = @BlobId
	AND [PortalId] = @PortalId
GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddToRepository
	@PortalId Int
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Repository (
	[Blob],
	[PortalId]
) VALUES (
	0x0,
	@PortalId
)

select SCOPE_IDENTITY()
GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteFromRepository
	@BlobId Int
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Repository
WHERE
	[BlobId] = @BlobId
GO


