/******* TABLES *******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Addons') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_Addons(
 [AddonKey] [nvarchar](50) NOT NULL,
 [Availability] [nvarchar](255) NULL,
 [Description] [nvarchar](255) NULL,
 [Installed] [datetime] NULL,
 [Name] [nvarchar](50) NULL,
 [Controller] [nvarchar](255) NULL,
 [Features] [int] NULL,
 CONSTRAINT PK_{objectQualifier}DMX_Addons PRIMARY KEY CLUSTERED 
(
 [AddonKey] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_ApplicationPermissions') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_ApplicationPermissions(
 [PortalId] [int] NOT NULL,
 [AllowAccess] [bit] NULL,
 [Expires] [datetime] NULL,
 [PermissionId] [int] NOT NULL,
 [RoleId] [int] NOT NULL,
 [UserId] [int] NOT NULL,
 CONSTRAINT PK_{objectQualifier}DMX_ApplicationPermissions PRIMARY KEY CLUSTERED 
(
 [PortalId] ASC,
 [PermissionId] ASC,
 [RoleId] ASC,
 [UserId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AttributeEntrytypes') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes(
 [AttributeId] [int] NOT NULL,
 [EntryType] [nvarchar](50) NOT NULL,
 CONSTRAINT PK_{objectQualifier}DMX_AttributeEntrytypes PRIMARY KEY CLUSTERED 
(
 [AttributeId] ASC,
 [EntryType] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Attributes') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_Attributes(
 [Addon] [nvarchar](50) NULL,
 [AttributeId] [int] IDENTITY(1,1) NOT NULL,
 [ControlToLoad] [nvarchar](255) NULL,
 [IsPrivate] [bit] NULL,
 [Key] [nvarchar](50) NULL,
 [PortalId] [int] NULL,
 [Required] [bit] NULL,
 [ResourceFile] [nvarchar](255) NULL,
 [Values] [nvarchar](2000) NULL,
 [ValueType] [nvarchar](30) NULL,
 [ViewOrder] [int] NULL,
 [CollectionId] [int] NOT NULL,
 [ShowInUI] [bit] NULL,
 [AttributeName] [nvarchar](200) NULL,
 CONSTRAINT PK_{objectQualifier}DMX_Attributes PRIMARY KEY CLUSTERED 
(
 [AttributeId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AttributesML') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_AttributesML(
 [AttributeId] [int] NOT NULL,
 [Locale] [varchar](10) NOT NULL,
 [AttributeName] [nvarchar](200) NOT NULL,
 CONSTRAINT PK_{objectQualifier}DMX_AttributesML PRIMARY KEY CLUSTERED 
(
 [AttributeId] ASC,
 [Locale] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Categories') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_Categories(
 [CategoryId] [int] IDENTITY(1,1) NOT NULL,
 [ParentId] [int] NOT NULL,
 [PortalId] [int] NOT NULL,
 [ViewOrder] [int] NULL,
 [CategoryName] [nvarchar](200) NULL,
 CONSTRAINT PK_{objectQualifier}DMX_Categories PRIMARY KEY CLUSTERED 
(
 [CategoryId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_CategoriesML') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_CategoriesML(
 [CategoryId] [int] NOT NULL,
 [Locale] [varchar](10) NOT NULL,
 [CategoryName] [nvarchar](200) NOT NULL,
 CONSTRAINT PK_{objectQualifier}DMX_CategoriesML PRIMARY KEY CLUSTERED 
(
 [CategoryId] ASC,
 [Locale] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DirectAccessKeys') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_DirectAccessKeys(
 [PortalId] [int] NOT NULL,
 [Downloads] [int] NULL,
 [Email] [nvarchar](100) NULL,
 [EntryId] [int] NULL,
 [Expires] [datetime] NULL,
 [Key] [nvarchar](100) NOT NULL,
 [UserId] [int] NULL,
 CONSTRAINT PK_{objectQualifier}DMX_DirectAccessKeys PRIMARY KEY CLUSTERED 
(
 [PortalId] ASC,
 [Key] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Entries') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_Entries(
 [Author] [nvarchar](500) NULL,
 [CollectionId] [int] NULL,
 [Deleted] [bit] NULL,
 [Entry] [nvarchar](2000) NULL,
 [EntryId] [int] IDENTITY(1,1) NOT NULL,
 [EntryType] [nvarchar](150) NOT NULL,
 [FileSize] [bigint] NULL,
 [Created] [datetime] NULL,
 [IsApproved] [bit] NULL,
 [Keywords] [nvarchar](255) NULL,
 [LastModified] [datetime] NULL,
 [LastVersionId] [int] NULL,
 [LockedBy] [int] NULL,
 [LockedUntil] [datetime] NULL,
 [LockKey] [uniqueidentifier] NULL,
 [MD5Hash] [nvarchar](50) NULL,
 [OriginalFileName] [nvarchar](255) NULL,
 [Owner] [int] NULL,
 [PortalId] [int] NULL,
 [State] [nvarchar](50) NULL,
 [Version] [int] NULL,
 [VersionsToKeep] [int] NULL,
 [WorkflowId] [nvarchar](50) NULL,
 [Path] [varchar](4000) NULL,
 [PermissionsOnlyByAdmin] [bit] NULL,
 [InheritAttributes] [bit] NOT NULL,
 [StorageProviderID] [int] NULL,
 [Thumbnail] [varchar](200) NULL,
 [Hidden] [bit] NULL,
 [Remarks] [nvarchar](max) NULL,
 [Title] [nvarchar](1000) NULL,
 CONSTRAINT PK_{objectQualifier}DMX_Entries PRIMARY KEY CLUSTERED 
(
 [EntryId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END;
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Entries') AND name = N'IX_{objectQualifier}DMX_Entries_CollectionID')
CREATE NONCLUSTERED INDEX IX_{objectQualifier}DMX_Entries_CollectionID ON {databaseOwner}{objectQualifier}DMX_Entries 
(
 [CollectionId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF);
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Entries') AND name = N'IX_{objectQualifier}DMX_Entries_Data1')
CREATE NONCLUSTERED INDEX IX_{objectQualifier}DMX_Entries_Data1 ON {databaseOwner}{objectQualifier}DMX_Entries 
(
 [LastVersionId] ASC,
 [EntryId] ASC,
 [CollectionId] ASC,
 [Deleted] ASC,
 [IsApproved] ASC,
 [Owner] ASC,
 [Version] ASC,
 [EntryType] ASC
)
INCLUDE ( [Author],
[Entry],
[Created],
[LastModified],
[LockedBy],
[LockedUntil],
[LockKey],
[MD5Hash],
[FileSize],
[Keywords],
[State],
[PortalId],
[PermissionsOnlyByAdmin],
[Path],
[OriginalFileName],
[WorkflowId],
[VersionsToKeep]) WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntriesML') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_EntriesML(
 [EntryId] [int] NOT NULL,
 [Locale] [nvarchar](10) NOT NULL,
 [Remarks] [nvarchar](max) NULL,
 [Title] [nvarchar](1000) NULL,
 CONSTRAINT PK_{objectQualifier}DMX_EntriesML PRIMARY KEY CLUSTERED 
(
 [EntryId] ASC,
 [Locale] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryAttributes') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_EntryAttributes(
 [AttributeId] [int] NOT NULL,
 [EntryId] [int] NOT NULL,
 [Value] [nvarchar](2000) NOT NULL,
 CONSTRAINT PK_{objectQualifier}DMX_EntryAttributes PRIMARY KEY CLUSTERED 
(
 [AttributeId] ASC,
 [EntryId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END;
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryAttributes') AND name = N'{objectQualifier}IX_DMX_EntryAttributes_EntryId')
CREATE NONCLUSTERED INDEX {objectQualifier}IX_DMX_EntryAttributes_EntryId ON {databaseOwner}{objectQualifier}DMX_EntryAttributes 
(
 [EntryId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryCategories') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_EntryCategories(
 [CategoryId] [int] NOT NULL,
 [EntryId] [int] NOT NULL,
 CONSTRAINT PK_{objectQualifier}DMX_EntryCategories PRIMARY KEY CLUSTERED 
(
 [CategoryId] ASC,
 [EntryId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END;
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryCategories') AND name = N'{objectQualifier}IX_DMX_EntryCategories_EntryId')
CREATE NONCLUSTERED INDEX {objectQualifier}IX_DMX_EntryCategories_EntryId ON {databaseOwner}{objectQualifier}DMX_EntryCategories 
(
 [EntryId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryPermissions') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_EntryPermissions(
 [AllowAccess] [bit] NULL,
 [EntryId] [int] NOT NULL,
 [Expires] [datetime] NULL,
 [PermissionId] [int] NOT NULL,
 [PortalId] [int] NOT NULL,
 [RoleId] [int] NOT NULL,
 [UserId] [int] NOT NULL,
 CONSTRAINT PK_{objectQualifier}DMX_EntryPermissions PRIMARY KEY CLUSTERED 
(
 [EntryId] ASC,
 [PermissionId] ASC,
 [RoleId] ASC,
 [UserId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END;
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryPermissions') AND name = N'{objectQualifier}IX_DMX_EntryPermissions_PermissionId')
CREATE NONCLUSTERED INDEX {objectQualifier}IX_DMX_EntryPermissions_PermissionId ON {databaseOwner}{objectQualifier}DMX_EntryPermissions 
(
 [PermissionId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF);
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryPermissions') AND name = N'{objectQualifier}IX_DMX_EntryPermissions_RoleId')
CREATE NONCLUSTERED INDEX {objectQualifier}IX_DMX_EntryPermissions_RoleId ON {databaseOwner}{objectQualifier}DMX_EntryPermissions 
(
 [RoleId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Extensions') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_Extensions(
 [AccessRights] [nvarchar](255) NULL,
 [Addon] [nvarchar](50) NOT NULL,
 [ControlToLoad] [nvarchar](255) NULL,
 [Custom] [nvarchar](400) NULL,
 [DownloadUrl] [nvarchar](255) NULL,
 [EntryTypes] [nvarchar](255) NULL,
 [ExtensionKey] [nvarchar](50) NOT NULL,
 [Icon16] [nvarchar](100) NULL,
 [Icon32] [nvarchar](100) NULL,
 [IsPrivate] [bit] NULL,
 [MimeType] [varchar](200) NULL,
 [NodePattern] [nvarchar](50) NULL,
 [PortalId] [int] NOT NULL,
 [ResourceFile] [nvarchar](255) NULL,
 [SettingsControl] [nvarchar](250) NULL,
 [ViewByDefault] [bit] NULL,
 [EditControl] [nvarchar](255) NULL,
 [ViewControl] [nvarchar](255) NULL,
 CONSTRAINT PK_{objectQualifier}DMX_Extensions PRIMARY KEY CLUSTERED 
(
 [ExtensionKey] ASC,
 [PortalId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Log') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_Log(
 [Action] [nvarchar](20) NOT NULL,
 [Datime] [datetime] NOT NULL,
 [EntryId] [int] NOT NULL,
 [LogId] [int] IDENTITY(1,1) NOT NULL,
 [UserId] [int] NOT NULL,
 [PortalId] [int] NULL,
 CONSTRAINT PK_{objectQualifier}DMX_Log PRIMARY KEY CLUSTERED 
(
 [LogId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END;
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Log') AND name = N'{objectQualifier}IX_DMX_Log_EntId')
CREATE NONCLUSTERED INDEX {objectQualifier}IX_DMX_Log_EntId ON {databaseOwner}{objectQualifier}DMX_Log 
(
 [EntryId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_LogTypes') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_LogTypes(
 [LogTypeKey] [nvarchar](20) NOT NULL,
 [ResourceFile] [nvarchar](150) NULL,
 [AttachFile] [bit] NULL,
 [CustomSend] [bit] NULL,
 [NotifyApprovers] [nvarchar](100) NULL,
 [NotifyAudit] [bit] NULL,
 [NotifyEditors] [nvarchar](100) NULL,
 [NotifyOwner] [nvarchar](100) NULL,
 [NotifySelf] [nvarchar](100) NULL,
 [NotifySubscribers] [nvarchar](100) NULL,
 [PortalId] [int] NOT NULL,
 CONSTRAINT PK_{objectQualifier}DMX_LogTypes PRIMARY KEY CLUSTERED 
(
 [LogTypeKey] ASC,
 [PortalId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Notifications') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_Notifications(
 [LogId] [int] NOT NULL,
 [PortalId] [int] NOT NULL,
 [Sent] [datetime] NULL,
 [Template] [nvarchar](100) NOT NULL,
 [UserId] [int] NOT NULL,
 CONSTRAINT PK_{objectQualifier}DMX_Notifications PRIMARY KEY CLUSTERED 
(
 [LogId] ASC,
 [UserId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Numbers') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_Numbers(
 [Number] [int] NOT NULL,
 CONSTRAINT PK_{objectQualifier}DMX_Numbers PRIMARY KEY CLUSTERED 
(
 [Number] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_PermissionEntrytypes') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes(
 [EntryType] [nvarchar](100) NOT NULL,
 [PermissionId] [int] NOT NULL,
 CONSTRAINT PK_{objectQualifier}DMX_PermissionEntrytypes PRIMARY KEY CLUSTERED 
(
 [EntryType] ASC,
 [PermissionId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_PermissionPermissions') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_PermissionPermissions(
 [PermissionId] [int] NOT NULL,
 [RoleId] [int] NOT NULL,
 [PortalId] [int] NOT NULL,
 [AllowAccess] [bit] NULL,
 [Expires] [datetime] NULL,
 [UserId] [int] NOT NULL,
 CONSTRAINT PK_{objectQualifier}DMX_PermissionPermissions PRIMARY KEY CLUSTERED 
(
 [PortalId] ASC,
 [PermissionId] ASC,
 [RoleId] ASC,
 [UserId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END;
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_PermissionPermissions') AND name = N'{objectQualifier}IX_DMX_PermissionPermissions_RoleId')
CREATE NONCLUSTERED INDEX {objectQualifier}IX_DMX_PermissionPermissions_RoleId ON {databaseOwner}{objectQualifier}DMX_PermissionPermissions 
(
 [RoleId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Permissions') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_Permissions(
 [Addon] [nvarchar](50) NULL,
 [PermissionId] [int] IDENTITY(1,1) NOT NULL,
 [PermissionKey] [nvarchar](20) NOT NULL,
 [PortalId] [int] NULL,
 [ResourceFile] [nvarchar](255) NULL,
 CONSTRAINT PK_{objectQualifier}DMX_Permissions PRIMARY KEY CLUSTERED 
(
 [PermissionId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Providers') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_Providers(
 [Provider] [nvarchar](255) NOT NULL,
 [ProviderID] [int] IDENTITY(1,1) NOT NULL,
 [ProviderName] [nvarchar](255) NOT NULL,
 [ProviderType] [int] NOT NULL,
 CONSTRAINT PK_{objectQualifier}DMX_Providers PRIMARY KEY CLUSTERED 
(
 [ProviderID] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Repository') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_Repository(
 [Blob] [varbinary](max) NULL,
 [BlobId] [int] IDENTITY(1,1) NOT NULL,
 [PortalId] [int] NULL,
 CONSTRAINT PK_{objectQualifier}DMX_Repository PRIMARY KEY CLUSTERED 
(
 [BlobId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_SearchResults') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_SearchResults(
 [SearchId] [nvarchar](50) NOT NULL,
 [EntryId] [int] NOT NULL,
 [Rank] [real] NULL,
 [Hits] [int] NOT NULL,
 [Extract] [nvarchar](2000) NULL
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Settings') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_Settings(
 [PortalId] [int] NOT NULL,
 [SettingName] [nvarchar](50) NOT NULL,
 [SettingValue] [nvarchar](2000) NULL,
 CONSTRAINT PK_{objectQualifier}DMX_Settings PRIMARY KEY CLUSTERED 
(
 [PortalId] ASC,
 [SettingName] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Subscriptions') AND type in (N'U'))
BEGIN
CREATE TABLE {databaseOwner}{objectQualifier}DMX_Subscriptions(
 [EntryId] [int] NOT NULL,
 [LastAccess] [datetime] NULL,
 [UserId] [int] NOT NULL,
 CONSTRAINT PK_{objectQualifier}DMX_Subscriptions PRIMARY KEY CLUSTERED 
(
 [EntryId] ASC,
 [UserId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO

/******* STRUCTURE *******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_ApplicationPermissions_Portals') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_ApplicationPermissions'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_ApplicationPermissions  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_ApplicationPermissions_Portals FOREIGN KEY([PortalId])
REFERENCES {objectQualifier}Portals ([PortalID])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_ApplicationPermissions_Portals') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_ApplicationPermissions'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_ApplicationPermissions CHECK CONSTRAINT FK_{objectQualifier}DMX_ApplicationPermissions_Portals
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_AttributeEntrytypes_Attributes') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AttributeEntrytypes'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_AttributeEntrytypes_Attributes FOREIGN KEY([AttributeId])
REFERENCES {objectQualifier}DMX_Attributes ([AttributeId])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_AttributeEntrytypes_Attributes') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AttributeEntrytypes'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes CHECK CONSTRAINT FK_{objectQualifier}DMX_AttributeEntrytypes_Attributes
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Attributes_Addons') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Attributes'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Attributes  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_Attributes_Addons FOREIGN KEY([Addon])
REFERENCES {objectQualifier}DMX_Addons ([AddonKey])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Attributes_Addons') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Attributes'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Attributes CHECK CONSTRAINT FK_{objectQualifier}DMX_Attributes_Addons
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Attributes_Portals') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Attributes'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Attributes  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_Attributes_Portals FOREIGN KEY([PortalId])
REFERENCES {objectQualifier}Portals ([PortalID])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Attributes_Portals') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Attributes'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Attributes CHECK CONSTRAINT FK_{objectQualifier}DMX_Attributes_Portals
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_AttributesML_Attributes') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AttributesML'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_AttributesML  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_AttributesML_Attributes FOREIGN KEY([AttributeId])
REFERENCES {objectQualifier}DMX_Attributes ([AttributeId])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_AttributesML_Attributes') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AttributesML'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_AttributesML CHECK CONSTRAINT FK_{objectQualifier}DMX_AttributesML_Attributes
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Categories_Portals') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Categories'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Categories  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_Categories_Portals FOREIGN KEY([PortalId])
REFERENCES {objectQualifier}Portals ([PortalID])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Categories_Portals') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Categories'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Categories CHECK CONSTRAINT FK_{objectQualifier}DMX_Categories_Portals
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_CategoriesML_Categories') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_CategoriesML'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_CategoriesML  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_CategoriesML_Categories FOREIGN KEY([CategoryId])
REFERENCES {objectQualifier}DMX_Categories ([CategoryId])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_CategoriesML_Categories') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_CategoriesML'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_CategoriesML CHECK CONSTRAINT FK_{objectQualifier}DMX_CategoriesML_Categories
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_DirectAccessKeys_Entries') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DirectAccessKeys'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_DirectAccessKeys  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_DirectAccessKeys_Entries FOREIGN KEY([EntryId])
REFERENCES {objectQualifier}DMX_Entries ([EntryId])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_DirectAccessKeys_Entries') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DirectAccessKeys'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_DirectAccessKeys CHECK CONSTRAINT FK_{objectQualifier}DMX_DirectAccessKeys_Entries
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Entries_Portals') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Entries'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Entries  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_Entries_Portals FOREIGN KEY([PortalId])
REFERENCES {objectQualifier}Portals ([PortalID])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Entries_Portals') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Entries'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Entries CHECK CONSTRAINT FK_{objectQualifier}DMX_Entries_Portals
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_EntriesML_Entries') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntriesML'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_EntriesML  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_EntriesML_Entries FOREIGN KEY([EntryId])
REFERENCES {objectQualifier}DMX_Entries ([EntryId])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_EntriesML_Entries') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntriesML'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_EntriesML CHECK CONSTRAINT FK_{objectQualifier}DMX_EntriesML_Entries
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_EntryAttributes_Attributes') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryAttributes'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_EntryAttributes  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_EntryAttributes_Attributes FOREIGN KEY([AttributeId])
REFERENCES {objectQualifier}DMX_Attributes ([AttributeId])
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_EntryAttributes_Attributes') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryAttributes'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_EntryAttributes CHECK CONSTRAINT FK_{objectQualifier}DMX_EntryAttributes_Attributes
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_EntryAttributes_Entries') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryAttributes'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_EntryAttributes  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_EntryAttributes_Entries FOREIGN KEY([EntryId])
REFERENCES {objectQualifier}DMX_Entries ([EntryId])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_EntryAttributes_Entries') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryAttributes'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_EntryAttributes CHECK CONSTRAINT FK_{objectQualifier}DMX_EntryAttributes_Entries
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_EntryCategories_Entries') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryCategories'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_EntryCategories  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_EntryCategories_Entries FOREIGN KEY([EntryId])
REFERENCES {objectQualifier}DMX_Entries ([EntryId])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_EntryCategories_Entries') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryCategories'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_EntryCategories CHECK CONSTRAINT FK_{objectQualifier}DMX_EntryCategories_Entries
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_EntryPermissions_Entries') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryPermissions'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_EntryPermissions  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_EntryPermissions_Entries FOREIGN KEY([EntryId])
REFERENCES {objectQualifier}DMX_Entries ([EntryId])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_EntryPermissions_Entries') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryPermissions'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_EntryPermissions CHECK CONSTRAINT FK_{objectQualifier}DMX_EntryPermissions_Entries
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Extensions_Addons') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Extensions'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Extensions  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_Extensions_Addons FOREIGN KEY([Addon])
REFERENCES {objectQualifier}DMX_Addons ([AddonKey])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Extensions_Addons') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Extensions'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Extensions CHECK CONSTRAINT FK_{objectQualifier}DMX_Extensions_Addons
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Log_Entries') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Log'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Log  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_Log_Entries FOREIGN KEY([EntryId])
REFERENCES {objectQualifier}DMX_Entries ([EntryId])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Log_Entries') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Log'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Log CHECK CONSTRAINT FK_{objectQualifier}DMX_Log_Entries
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Notifications_Log') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Notifications'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Notifications  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_Notifications_Log FOREIGN KEY([LogId])
REFERENCES {objectQualifier}DMX_Log ([LogId])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Notifications_Log') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Notifications'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Notifications CHECK CONSTRAINT FK_{objectQualifier}DMX_Notifications_Log
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_PermissionEntrytypes_Permissions') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_PermissionEntrytypes'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_PermissionEntrytypes_Permissions FOREIGN KEY([PermissionId])
REFERENCES {objectQualifier}DMX_Permissions ([PermissionId])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_PermissionEntrytypes_Permissions') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_PermissionEntrytypes'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes CHECK CONSTRAINT FK_{objectQualifier}DMX_PermissionEntrytypes_Permissions
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_PermissionPermissions_Permissions') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_PermissionPermissions'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_PermissionPermissions  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_PermissionPermissions_Permissions FOREIGN KEY([PermissionId])
REFERENCES {objectQualifier}DMX_Permissions ([PermissionId])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_PermissionPermissions_Permissions') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_PermissionPermissions'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_PermissionPermissions CHECK CONSTRAINT FK_{objectQualifier}DMX_PermissionPermissions_Permissions
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Repository_Portals') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Repository'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Repository  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_Repository_Portals FOREIGN KEY([PortalId])
REFERENCES {objectQualifier}Portals ([PortalID])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Repository_Portals') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Repository'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Repository CHECK CONSTRAINT FK_{objectQualifier}DMX_Repository_Portals
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Subscriptions_Entries') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Subscriptions'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Subscriptions  WITH NOCHECK ADD  CONSTRAINT FK_{objectQualifier}DMX_Subscriptions_Entries FOREIGN KEY([EntryId])
REFERENCES {objectQualifier}DMX_Entries ([EntryId])
ON DELETE CASCADE
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}FK_{objectQualifier}DMX_Subscriptions_Entries') AND parent_object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Subscriptions'))
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Subscriptions CHECK CONSTRAINT FK_{objectQualifier}DMX_Subscriptions_Entries
GO

/******* TRIGGERS *******/
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryTriggerDelete'))
DROP TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerDelete
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 AND e.EntryType LIKE 'Collection%';
 
END
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryTriggerInsert'))
DROP TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerInsert
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 e.EntryType LIKE 'Collection%' AND (i.LastVersionId IS NULL OR i.LastVersionId=i.EntryId);
 -- repair missing LastVersionId
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET LastVersionId=EntryId
 WHERE LastVersionId IS NULL;
END
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryTriggerUpdate'))
DROP TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerUpdate
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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)
  OR UPDATE(Title) OR UPDATE(Remarks))
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
 WHERE e.EntryType LIKE 'Collection%';
-- 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
 WHERE e.EntryType LIKE 'Collection%';
-- 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
 WHERE e.EntryType LIKE 'Collection%';
-- 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

/******* FUNCTIONS *******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_ExtractConcatenate') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION {databaseOwner}{objectQualifier}DMX_ExtractConcatenate
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION {databaseOwner}{objectQualifier}DMX_ExtractConcatenate
 (@EntryId INT, @SEPARATOR NVARCHAR(8))
RETURNS NVARCHAR(2000)
AS
BEGIN
 DECLARE @RES NVARCHAR(2000)
 SELECT @RES = (SELECT ISNULL(Extract,'') AS X
  FROM {databaseOwner}{objectQualifier}DMX_SearchResults
  WHERE EntryId=@EntryId
  FOR XML PATH(''));
 SET @RES = REPLACE(@RES, '</X><X>', @SEPARATOR);
 SET @RES = REPLACE(@RES, '<X>', '');
 SET @RES = REPLACE(@RES, '</X>', '');
 RETURN @RES
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_fn_GetExtension') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION {databaseOwner}{objectQualifier}DMX_fn_GetExtension
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION {databaseOwner}{objectQualifier}DMX_fn_GetExtension
 (@Filename NVARCHAR(200))
RETURNS NVARCHAR(10)
AS
BEGIN
 IF @Filename IS NOT NULL
  IF @Filename<>''
   DECLARE @POS INT
   SELECT @POS = LEN(@Filename)-1
   DECLARE @CHR NVARCHAR(1)
   SELECT @CHR = SUBSTRING(@Filename,@POS,1)
   WHILE @CHR<>'.' AND @POS>1
   BEGIN
    SELECT @POS = @POS-1
    SELECT @CHR = SUBSTRING(@Filename,@POS,1)
   END
   IF @POS > 1
    RETURN SUBSTRING(@Filename,@POS,LEN(@Filename)-@POS+1)
 RETURN ''
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_fn_GetPath') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION {databaseOwner}{objectQualifier}DMX_fn_GetPath
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION {databaseOwner}{objectQualifier}DMX_fn_GetPath
 (@EntryId INT, 
  @PortalId INT, 
  @Locale NVARCHAR(10))
RETURNS NVARCHAR(4000)
AS
BEGIN
 DECLARE @res NVARCHAR(4000)
 IF EXISTS (SELECT EntryId FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId AND PortalId=@PortalId)
 BEGIN
  DECLARE 
   @collection INT,
   @name NVARCHAR(2000),
   @orig NVARCHAR(300);
  WITH x AS (
   SELECT e.CollectionId, e.OriginalFileName, ISNULL(el.Title, e.Title) Title
   FROM {databaseOwner}{objectQualifier}DMX_Entries e
    LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
   WHERE e.EntryId=@EntryId AND e.PortalId=@PortalId)
  SELECT
   @collection = CollectionId,
   @name = Title,
   @orig = OriginalFilename
  FROM x;
  SELECT @res = {databaseOwner}{objectQualifier}DMX_fn_GetPath(@collection, @PortalId, @Locale)+'/'+@name+{databaseOwner}{objectQualifier}DMX_fn_GetExtension(@orig)
 END
ELSE
 SELECT @res = ''
RETURN @res
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_fn_URLEncode') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION {databaseOwner}{objectQualifier}DMX_fn_URLEncode
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION {databaseOwner}{objectQualifier}DMX_fn_URLEncode(@String NVARCHAR(4000))
RETURNS VARCHAR(8000)
AS
BEGIN
 DECLARE @URLEncodedString VARCHAR(8000)
 SELECT @URLEncodedString=''
 SELECT @URLEncodedString=@URLEncodedString + 
  CASE WHEN theChar LIKE '[A-Za-z0-9()''*-._!/]'
   THEN theChar
   ELSE '%' + substring ('0123456789ABCDEF',(ASCII(theChar) / 16)+1,1) +  SUBSTRING('0123456789ABCDEF',(ASCII(theChar) % 16)+1,1)
  END
  FROM (SELECT [theChar]=SUBSTRING(@string,number,1)
   FROM {databaseOwner}{objectQualifier}DMX_Numbers WHERE Number <= LEN(@String)) CHARACTERARRAY
 RETURN @URLEncodedString
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_Split') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION {databaseOwner}{objectQualifier}DMX_Split
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION {databaseOwner}{objectQualifier}DMX_Split (@s varchar(512), @sep char(1))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_fn_GetEntriesLocalized') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION {databaseOwner}{objectQualifier}DMX_fn_GetEntriesLocalized
GO

CREATE FUNCTION {databaseOwner}{objectQualifier}DMX_fn_GetEntriesLocalized
 (@Locale VARCHAR(10))
 RETURNS TABLE
AS
RETURN
(SELECT
  e.Author,
  e.CollectionId,
  e.Deleted,
  e.Entry,
  e.EntryId,
  e.EntryType,
  e.FileSize,
  e.Created,
  e.IsApproved,
  e.Keywords,
  e.LastModified,
  e.LastVersionId,
  e.LockedBy,
  e.LockedUntil,
  e.LockKey,
  e.MD5Hash,
  e.OriginalFileName,
  e.Owner,
  e.PortalId,
  e.State,
  e.Version,
  e.VersionsToKeep,
  e.WorkflowId,
  e.Path,
  e.PermissionsOnlyByAdmin,
  e.InheritAttributes,
  e.StorageProviderID,
  e.Thumbnail,
  e.Hidden,
  ISNULL(eml.Remarks, e.Remarks) AS Remarks, 
  ISNULL(eml.Title, e.Title) AS Title, 
  e.Remarks AS RemarksOriginal,
  e.Title AS TitleOriginal,
  eml.Remarks AS RemarksLocalized, 
  eml.Title AS TitleLocalized, 
  eml.Locale,
  ext.AccessRights,
  ext.Addon,
  ext.ControlToLoad, 
  ext.Custom,
  ext.DownloadUrl,
  ext.EntryTypes, 
  ext.Icon16,
  ext.Icon32,
  ext.IsPrivate,
  ext.MimeType, 
  ext.NodePattern,
  ext.ResourceFile,
  ext.SettingsControl, 
  ext.ViewByDefault,
  ext.EditControl,
  ext.ViewControl, 
  u.DisplayName AS OwnerDisplayName,
  CASE WHEN (PATINDEX('Collection%', EntryType) > 0) THEN 1 ELSE 0 END AS IsCollection, 
  CASE WHEN LockedUntil > GETDATE() THEN 1 ELSE 0 END AS IsLocked,
  ext.ExtensionKey
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e 
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Extensions ext ON e.EntryType = ext.ExtensionKey AND e.PortalId = ext.PortalId
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Users u ON e.Owner = u.UserID
 LEFT OUTER JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntriesML WHERE Locale=@Locale) eml ON e.EntryId = eml.EntryId)

GO

/******* VIEWS *******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles'))
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles
AS
SELECT ur.UserID, ur.RoleID
FROM {databaseOwner}{objectQualifier}UserRoles ur
WHERE 
 ISNULL(ur.EffectiveDate, DATEADD(day, -1, GETDATE())) < GETDATE()
 AND ISNULL(ur.ExpiryDate, DATEADD(day, 1, GETDATE())) > GETDATE()
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}vw_DMX_ApplicationPermissions'))
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_ApplicationPermissions
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_ApplicationPermissions
AS
SELECT 
 ap.AllowAccess, 
 ap.Expires, 
 ap.PermissionId, 
 ap.PortalId, 
 ap.RoleId, 
 ap.UserId, 
 p.Addon, 
 p.PermissionKey, 
 p.ResourceFile, 
 ISNULL(u.Username, N'') AS Username, 
 ISNULL(u.DisplayName, N'') AS DisplayName, 
 ISNULL(r.RoleName, N'') AS RoleName
FROM {databaseOwner}{objectQualifier}DMX_ApplicationPermissions ap
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON ap.PermissionId = p.PermissionId 
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Users u ON ap.UserId = u.UserID
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Roles r ON ap.RoleId = r.RoleID
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}vw_DMX_Entries'))
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_Entries
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_Entries
AS
SELECT 
 e.Author,
 e.CollectionId,
 e.Deleted,
 e.Entry, 
 e.EntryId,
 e.EntryType,
 e.FileSize,
 e.Created, 
 e.IsApproved,
 e.Keywords,
 e.LastModified,
 e.LastVersionId, 
 e.LockedBy,
 e.LockedUntil,
 e.LockKey,
 e.MD5Hash, 
 e.OriginalFileName,
 e.Owner,
 e.PortalId,
 e.State, 
 e.Version,
 e.VersionsToKeep,
 e.WorkflowId, 
 e.Path, 
 e.PermissionsOnlyByAdmin,
 e.InheritAttributes,
 e.StorageProviderID, 
 e.Remarks,
 e.Title,
 e.Thumbnail,
 e.Hidden, 
 ext.AccessRights,
 ext.Addon,
 ext.ControlToLoad, 
 ext.Custom,
 ext.DownloadUrl,
 ext.EntryTypes, 
 ext.Icon16,
 ext.Icon32,
 ext.IsPrivate,
 ext.MimeType, 
 ext.NodePattern,
 ext.ResourceFile,
 ext.SettingsControl, 
 ext.ViewByDefault,
 ext.EditControl,
 ext.ViewControl, 
 u.DisplayName AS OwnerDisplayName,
 CASE WHEN (PATINDEX('Collection%', EntryType) > 0) THEN 1 ELSE 0 END AS IsCollection, 
 CASE WHEN LockedUntil > GETDATE() THEN 1 ELSE 0 END AS IsLocked,
 ext.ExtensionKey
FROM {databaseOwner}{objectQualifier}DMX_Entries e
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Extensions ext ON e.EntryType = ext.ExtensionKey AND e.PortalId = ext.PortalId
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Users u ON e.Owner = u.UserID
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}vw_DMX_EntryPermissions'))
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_EntryPermissions
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_EntryPermissions
AS
SELECT
 ep.AllowAccess,
 ep.EntryId,
 ep.Expires, 
 ep.PermissionId,
 ep.PortalId,
 ep.RoleId, 
 ep.UserId,
 p.Addon,
 p.PermissionKey, 
 p.ResourceFile,
 ISNULL(u.Username, N'') AS Username,
 ISNULL(u.DisplayName, N'') AS DisplayName,
 ISNULL(r.RoleName, N'') AS RoleName
FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON ep.PermissionId = p.PermissionId
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Roles r ON ep.RoleId = r.RoleID
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Users u ON ep.UserId = u.UserID
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}vw_DMX_Log'))
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_Log
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_Log
AS
SELECT
 l.Action,
 l.Datime,
 l.EntryId,
 l.LogId,
 l.UserId, 
 l.PortalId,
 lt.LogTypeKey,
 lt.ResourceFile,
 lt.AttachFile,
 lt.CustomSend, 
 lt.NotifyApprovers,
 lt.NotifyAudit,
 lt.NotifyEditors, 
 lt.NotifyOwner,
 lt.NotifySelf,
 lt.NotifySubscribers, 
 ISNULL(u.Username, N'') AS Username,
 ISNULL(u.DisplayName, N'') AS DisplayName
FROM {databaseOwner}{objectQualifier}DMX_Log l
 INNER JOIN {databaseOwner}{objectQualifier}DMX_LogTypes lt ON l.PortalId = lt.PortalId AND l.Action = lt.LogTypeKey
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Users u ON l.UserId = u.UserID
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}vw_DMX_Notifications'))
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_Notifications
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_Notifications
AS
SELECT
 n.LogId,
 n.PortalId,
 n.Sent,
 n.Template, 
 n.UserId AS RecipientUserId,
 l.Action,
 l.Datime,
 l.EntryId,
 l.UserId, 
 lt.ResourceFile,
 lt.AttachFile,
 lt.CustomSend, 
 lt.NotifyApprovers,
 lt.NotifyAudit,
 lt.NotifyEditors, 
 lt.NotifyOwner,
 lt.NotifySelf,
 lt.NotifySubscribers, 
 lt.LogTypeKey
FROM {databaseOwner}{objectQualifier}DMX_Log l
 INNER JOIN {databaseOwner}{objectQualifier}DMX_LogTypes lt ON l.PortalId = lt.PortalId AND l.Action=lt.LogTypeKey
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Notifications n ON l.LogId = n.LogId
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}vw_DMX_PermissionPermissions'))
DROP VIEW {databaseOwner}{objectQualifier}vw_DMX_PermissionPermissions
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW {databaseOwner}{objectQualifier}vw_DMX_PermissionPermissions
AS
SELECT 
 pp.AllowAccess, 
 pp.Expires, 
 pp.PermissionId, 
 pp.PortalId, 
 pp.RoleId, 
 pp.UserId, 
 p.Addon, 
 p.PermissionKey, 
 p.ResourceFile, 
 ISNULL(u.Username, N'') AS Username, 
 ISNULL(u.DisplayName, N'') AS DisplayName, 
 ISNULL(r.RoleName, N'') AS RoleName
FROM {databaseOwner}{objectQualifier}DMX_PermissionPermissions pp
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON pp.PermissionId = p.PermissionId 
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Users u ON pp.UserId = u.UserID
 LEFT OUTER JOIN {databaseOwner}{objectQualifier}Roles r ON pp.RoleId = r.RoleID
GO

/******* SPROCS *******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddAddon') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAddon
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAddon
 @AddonKey NVARCHAR (50), 
 @Availability NVARCHAR (255), 
 @Controller NVARCHAR (255), 
 @Description NVARCHAR (255), 
 @Features INT, 
 @Installed DATETIME, 
 @Name NVARCHAR (50)
AS
INSERT INTO {databaseOwner}{objectQualifier}DMX_Addons (
 [AddonKey],
 [Availability],
 [Controller],
 [Description],
 [Features],
 [Installed],
 [Name]
)
 VALUES ( @AddonKey, @Availability, @Controller, @Description, @Features, @Installed, @Name)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddAttribute') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAttribute
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAttribute
 @PortalId INT, 
 @Addon NVARCHAR (50), 
 @AttributeName NVARCHAR (200), 
 @CollectionId INT, 
 @ControlToLoad NVARCHAR (255), 
 @IsPrivate BIT, 
 @Key NVARCHAR (50), 
 @Required BIT, 
 @ResourceFile NVARCHAR (255), 
 @ShowInUI BIT, 
 @Values NVARCHAR (2000), 
 @ValueType NVARCHAR (30), 
 @ViewOrder INT
AS
INSERT INTO {databaseOwner}{objectQualifier}DMX_Attributes (
 [PortalId],
 [Addon],
 [AttributeName],
 [CollectionId],
 [ControlToLoad],
 [IsPrivate],
 [Key],
 [Required],
 [ResourceFile],
 [ShowInUI],
 [Values],
 [ValueType],
 [ViewOrder]
)
 VALUES ( @PortalId, @Addon, @AttributeName, @CollectionId, @ControlToLoad, @IsPrivate, @Key, @Required, @ResourceFile, @ShowInUI, @Values, @ValueType, @ViewOrder)
select SCOPE_IDENTITY()
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddAttributeEntrytype') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAttributeEntrytype
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAttributeEntrytype
 @AttributeId INT, 
 @EntryType NVARCHAR (50)
AS
INSERT INTO {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes (
 [AttributeId],
 [EntryType])
VALUES (
 @AttributeId,
 @EntryType)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddCategory') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddCategory
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddCategory
 @PortalId INT, 
 @CategoryName NVARCHAR (200), 
 @ParentId INT, 
 @ViewOrder INT
AS
INSERT INTO {databaseOwner}{objectQualifier}DMX_Categories (
 [PortalId],
 [CategoryName],
 [ParentId],
 [ViewOrder]
)
 VALUES ( @PortalId, @CategoryName, @ParentId, @ViewOrder)
select SCOPE_IDENTITY()
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddDirectAccessKey') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddDirectAccessKey
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddDirectAccessKey
 @PortalId INT, 
 @Downloads INT, 
 @Email NVARCHAR (100), 
 @EntryId INT, 
 @Expires DATETIME, 
 @Key NVARCHAR (100), 
 @UserId INT
AS
IF EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}DMX_DirectAccessKeys
   WHERE PortalId=@PortalId AND [Key]=@Key)
 SELECT 0
ELSE
INSERT INTO {databaseOwner}{objectQualifier}DMX_DirectAccessKeys (
 [PortalId],
 [Downloads],
 [Email],
 [EntryId],
 [Expires],
 [Key],
 [UserId])
VALUES (
 @PortalId,
 @Downloads,
 @Email,
 @EntryId,
 @Expires,
 @Key,
 @UserId);
 SELECT 1;
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntry
 @PortalId INT, 
 @Author NVARCHAR (500), 
 @CollectionId INT, 
 @Created DATETIME, 
 @Deleted BIT, 
 @Entry NVARCHAR (2000), 
 @EntryType NVARCHAR (150), 
 @FileSize BIGINT, 
 @Hidden BIT, 
 @InheritAttributes BIT, 
 @IsApproved BIT, 
 @Keywords NVARCHAR (255), 
 @LastVersionId INT, 
 @LockedBy INT, 
 @LockedUntil DATETIME, 
 @LockKey UNIQUEIDENTIFIER, 
 @MD5Hash NVARCHAR (50), 
 @OriginalFileName NVARCHAR (255), 
 @Owner INT, 
 @PermissionsOnlyByAdmin BIT, 
 @Remarks NVARCHAR (MAX), 
 @State NVARCHAR (50), 
 @StorageProviderID INT, 
 @Thumbnail VARCHAR (200), 
 @Title NVARCHAR (1000), 
 @Version INT, 
 @VersionsToKeep INT, 
 @WorkflowId NVARCHAR (50)
AS
INSERT INTO {databaseOwner}{objectQualifier}DMX_Entries (
 [PortalId],
 [Author],
 [CollectionId],
 [Created],
 [Deleted],
 [Entry],
 [EntryType],
 [FileSize],
 [Hidden],
 [InheritAttributes],
 [IsApproved],
 [Keywords],
 [LastVersionId],
 [LockedBy],
 [LockedUntil],
 [LockKey],
 [MD5Hash],
 [OriginalFileName],
 [Owner],
 [PermissionsOnlyByAdmin],
 [Remarks],
 [State],
 [StorageProviderID],
 [Thumbnail],
 [Title],
 [Version],
 [VersionsToKeep],
 [WorkflowId]
)
 VALUES ( @PortalId, @Author, @CollectionId, @Created, @Deleted, @Entry, @EntryType, @FileSize, @Hidden, @InheritAttributes, @IsApproved, @Keywords, @LastVersionId, @LockedBy, @LockedUntil, @LockKey, @MD5Hash, @OriginalFileName, @Owner, @PermissionsOnlyByAdmin, @Remarks, @State, @StorageProviderID, @Thumbnail, @Title, @Version, @VersionsToKeep, @WorkflowId)
select SCOPE_IDENTITY()
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddEntryAttribute') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntryAttribute
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntryAttribute
 @AttributeId INT, 
 @EntryId INT, 
 @Value NVARCHAR (2000)
AS
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryAttributes (
 [AttributeId],
 [EntryId],
 [Value])
VALUES (
 @AttributeId,
 @EntryId,
 @Value)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddEntryCategory') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntryCategory
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntryCategory
 @EntryId Int, 
 @CategoryId Int
AS
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryCategories (
 [EntryId],
 [CategoryId]
) VALUES (
 @EntryId,
 @CategoryId
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddEntryPermission') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntryPermission
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntryPermission
 @PortalId INT, 
 @AllowAccess BIT, 
 @EntryId INT, 
 @Expires DATETIME, 
 @PermissionId INT, 
 @RoleId INT, 
 @UserId INT
AS

IF NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions WHERE PortalId=@PortalId AND EntryId=@EntryId AND PermissionId=@PermissionId AND RoleId=@RoleId AND UserId=@UserId)
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions (
 [PortalId],
 [AllowAccess],
 [EntryId],
 [Expires],
 [PermissionId],
 [RoleId],
 [UserId])
VALUES (
 @PortalId,
 @AllowAccess,
 @EntryId,
 @Expires,
 @PermissionId,
 @RoleId,
 @UserId)

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddExtension') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddExtension
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddExtension
 @PortalId INT, 
 @AccessRights NVARCHAR (255), 
 @Addon NVARCHAR (50), 
 @ControlToLoad NVARCHAR (255), 
 @Custom NVARCHAR (400), 
 @DownloadUrl NVARCHAR (255), 
 @EditControl NVARCHAR (255), 
 @EntryTypes NVARCHAR (255), 
 @ExtensionKey NVARCHAR (50), 
 @Icon16 NVARCHAR (100), 
 @Icon32 NVARCHAR (100), 
 @IsPrivate BIT, 
 @MimeType VARCHAR (200), 
 @NodePattern NVARCHAR (50), 
 @ResourceFile NVARCHAR (255), 
 @SettingsControl NVARCHAR (250), 
 @ViewByDefault BIT, 
 @ViewControl NVARCHAR (255)
AS
INSERT INTO {databaseOwner}{objectQualifier}DMX_Extensions (
 [PortalId],
 [AccessRights],
 [Addon],
 [ControlToLoad],
 [Custom],
 [DownloadUrl],
 [EditControl],
 [EntryTypes],
 [ExtensionKey],
 [Icon16],
 [Icon32],
 [IsPrivate],
 [MimeType],
 [NodePattern],
 [ResourceFile],
 [SettingsControl],
 [ViewByDefault],
 [ViewControl])
VALUES (
 @PortalId,
 @AccessRights,
 @Addon,
 @ControlToLoad,
 @Custom,
 @DownloadUrl,
 @EditControl,
 @EntryTypes,
 @ExtensionKey,
 @Icon16,
 @Icon32,
 @IsPrivate,
 @MimeType,
 @NodePattern,
 @ResourceFile,
 @SettingsControl,
 @ViewByDefault,
 @ViewControl)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddFolder') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddFolder
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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, Title)
  VALUES
  (@CollectionId, GETDATE(), 0, '', 'Collection', 1, @Owner, @PortalId, 1, 0, @FolderName);
 SET @NewId = SCOPE_IDENTITY();
 INSERT INTO {databaseOwner}{objectQualifier}DMX_Log
  ([Action],[Datime],[EntryId],[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

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddHostSetting') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddHostSetting
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddHostSetting
 @SettingName nvarchar(50),
 @SettingValue nvarchar(256)
AS
INSERT INTO {databaseOwner}{objectQualifier}HostSettings (
 SettingName,
 SettingValue,
 SettingIsSecure
) 
VALUES (
 @SettingName,
 @SettingValue,
 0
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddLog') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddLog
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddLog
 @PortalId INT, 
 @Action NVARCHAR (20), 
 @Datime DATETIME, 
 @EntryId INT, 
 @UserId INT
AS
INSERT INTO {databaseOwner}{objectQualifier}DMX_Log (
 [PortalId],
 [Action],
 [Datime],
 [EntryId],
 [UserId]
)
 VALUES ( @PortalId, @Action, @Datime, @EntryId, @UserId)
select SCOPE_IDENTITY()
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddLogType') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddLogType
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddLogType
 @PortalId INT, 
 @AttachFile BIT, 
 @CustomSend BIT, 
 @LogTypeKey NVARCHAR (20), 
 @NotifyApprovers NVARCHAR (100), 
 @NotifyAudit BIT, 
 @NotifyEditors NVARCHAR (100), 
 @NotifyOwner NVARCHAR (100), 
 @NotifySelf NVARCHAR (100), 
 @NotifySubscribers NVARCHAR (100), 
 @ResourceFile NVARCHAR (150)
AS
INSERT INTO {databaseOwner}{objectQualifier}DMX_LogTypes (
 [PortalId],
 [AttachFile],
 [CustomSend],
 [LogTypeKey],
 [NotifyApprovers],
 [NotifyAudit],
 [NotifyEditors],
 [NotifyOwner],
 [NotifySelf],
 [NotifySubscribers],
 [ResourceFile])
VALUES (
 @PortalId,
 @AttachFile,
 @CustomSend,
 @LogTypeKey,
 @NotifyApprovers,
 @NotifyAudit,
 @NotifyEditors,
 @NotifyOwner,
 @NotifySelf,
 @NotifySubscribers,
 @ResourceFile)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddNotification') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddNotification
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddNotification
 @PortalId INT, 
 @LogId INT, 
 @Sent DATETIME, 
 @Template NVARCHAR (100), 
 @UserId INT
AS
INSERT INTO {databaseOwner}{objectQualifier}DMX_Notifications (
 [PortalId],
 [LogId],
 [Sent],
 [Template],
 [UserId])
VALUES (
 @PortalId,
 @LogId,
 @Sent,
 @Template,
 @UserId)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddPermission') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddPermission
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddPermission
 @PortalId INT, 
 @Addon NVARCHAR (50), 
 @PermissionKey NVARCHAR (20), 
 @ResourceFile NVARCHAR (255)
AS
INSERT INTO {databaseOwner}{objectQualifier}DMX_Permissions (
 [PortalId],
 [Addon],
 [PermissionKey],
 [ResourceFile]
)
 VALUES ( @PortalId, @Addon, @PermissionKey, @ResourceFile)
select SCOPE_IDENTITY()
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddPermissionEntrytype') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddPermissionEntrytype
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddPermissionEntrytype
 @EntryType NVARCHAR (100), 
 @PermissionId INT
AS
INSERT INTO {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes (
 [EntryType],
 [PermissionId])
VALUES (
 @EntryType,
 @PermissionId)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddProvider') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddProvider
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddSubscription') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddSubscription
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddSubscription
 @EntryId INT, 
 @LastAccess DATETIME, 
 @UserId INT
AS
INSERT INTO {databaseOwner}{objectQualifier}DMX_Subscriptions (
 [EntryId],
 [LastAccess],
 [UserId])
VALUES (
 @EntryId,
 @LastAccess,
 @UserId)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_AddToRepository') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddToRepository
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_CleanUpDirectAccessKeys') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_CleanUpDirectAccessKeys
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_CleanUpDirectAccessKeys
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_DirectAccessKeys
WHERE Expires < GETDATE() OR Downloads < 1
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_ClearEntryPermissions') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_ClearEntryPermissions
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_ClearEntryPermissions
 @PortalId INT,
 @EntryId INT
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions 
 WHERE (PortalId=@PortalId) AND (EntryId=@EntryId)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DatabaseUsage') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DatabaseUsage
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DatabaseUsage
AS
SELECT
 o.name,
 SUM(s.reserved_page_count) * 8.0 / 1024 reserved,
 SUM(s.row_count) [rows]
FROM
 sys.dm_db_partition_stats s
 INNER JOIN sys.objects o ON s.object_id = o.object_id
GROUP BY o.name
ORDER BY reserved DESC
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DBCleanup') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DBCleanup
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DBCleanup
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_Permissions
FROM {databaseOwner}{objectQualifier}DMX_Permissions perm
 LEFT JOIN {databaseOwner}{objectQualifier}Portals p ON perm.PortalId=p.PortalId
WHERE perm.PortalId>-1
 AND p.PortalId IS NULL;
DELETE FROM {databaseOwner}{objectQualifier}DMX_Extensions
FROM {databaseOwner}{objectQualifier}DMX_Extensions ext
 LEFT JOIN {databaseOwner}{objectQualifier}Portals p ON ext.PortalId=p.PortalId
WHERE ext.PortalId>-1
 AND p.PortalId IS NULL;
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteAddon') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAddon
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAddon
 @AddonKey NVARCHAR (50)
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_Addons
WHERE
 [AddonKey] = @AddonKey
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteApplicationPermissions') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteApplicationPermissions
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteApplicationPermissions
 @PortalId INT
AS
DELETE FROM
 {databaseOwner}{objectQualifier}DMX_ApplicationPermissions
WHERE
 [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteAttribute') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAttribute
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAttribute
 @AttributeId INT
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_Attributes
WHERE
 [AttributeId] = @AttributeId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteAttributeEntrytype') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAttributeEntrytype
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAttributeEntrytype
 @AttributeId INT,
 @EntryType NVARCHAR (50)
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes
WHERE
 [AttributeId] = @AttributeId
 AND [EntryType] = @EntryType
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteAttributeEntrytypes') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAttributeEntrytypes
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAttributeEntrytypes
 @AttributeId Int
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes
WHERE
 [AttributeId] = @AttributeId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteCategory') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteCategory
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteCategory
 @CategoryId INT
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryCategories
WHERE CategoryId=@CategoryId;
DELETE FROM {databaseOwner}{objectQualifier}DMX_Categories
WHERE CategoryId=@CategoryId;
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteDirectAccessKey') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteDirectAccessKey
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteDirectAccessKey
 @PortalId INT,
 @Key NVARCHAR (100)
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_DirectAccessKeys
WHERE
 [PortalId] = @PortalId
 AND [Key] = @Key
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntry
 @EntryId Int
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_Entries
WHERE [EntryId] = @EntryId;
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteEntryAttribute') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryAttribute
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryAttribute
 @AttributeId INT,
 @EntryId INT
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes
WHERE
 [AttributeId] = @AttributeId
 AND [EntryId] = @EntryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteEntryAttributesByEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryAttributesByEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryAttributesByEntry
 @EntryId Int
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes
WHERE EntryID=@EntryID
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteEntryBranch') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryBranch
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryBranch
 @PortalId INT,
 @EntryId INT,
 @IncludeSelf BIT
AS
EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, @EntryId;
DECLARE @Path VARCHAR(4000)
SELECT @Path=[Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryID AND PortalId=@PortalID;
DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes
 FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes ea
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON ea.EntryId=e.EntryId
 WHERE ((e.[Path] LIKE @Path+'%') OR (@IncludeSelf=1 AND e.LastVersionId=@EntryId))
 AND (@IncludeSelf=1 OR (NOT [Path]=@Path));
DELETE FROM {databaseOwner}{objectQualifier}DMX_Attributes
 FROM {databaseOwner}{objectQualifier}DMX_Attributes a
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON a.CollectionId=e.EntryId
 WHERE ((e.[Path] LIKE @Path+'%') OR (@IncludeSelf=1 AND e.LastVersionId=@EntryId))
 AND (@IncludeSelf=1 OR (NOT [Path]=@Path));
DELETE FROM {databaseOwner}{objectQualifier}DMX_Entries
 WHERE (([Path] LIKE @Path+'%') OR (@IncludeSelf=1 AND LastVersionId=@EntryId))
 AND (@IncludeSelf=1 OR (NOT [Path]=@Path));
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteEntryCategory') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryCategory
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryCategory
 @EntryId Int,
 @CategoryId Int
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryCategories
WHERE
 [EntryId] = @EntryId
 AND [CategoryId] = @CategoryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteEntryPermission') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryPermission
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryPermission
 @EntryId INT,
 @PermissionId INT,
 @RoleId INT,
 @UserId INT
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
WHERE
 [EntryId] = @EntryId
 AND [PermissionId] = @PermissionId
 AND [RoleId] = @RoleId
 AND [UserId] = @UserId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteExtension') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteExtension
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteExtension
 @ExtensionKey NVARCHAR (50),
 @PortalId INT
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_Extensions
WHERE
 [ExtensionKey] = @ExtensionKey
 AND [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteFromRepository') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteFromRepository
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteFromRepository
 @BlobId Int
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_Repository
WHERE
 [BlobId] = @BlobId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteLog') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteLog
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteLog
 @LogId INT
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_Log
WHERE
 [LogId] = @LogId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteLogType') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteLogType
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteLogType
 @LogTypeKey NVARCHAR (20),
 @PortalId INT
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_LogTypes
WHERE
 [LogTypeKey] = @LogTypeKey
 AND [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteNotification') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteNotification
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteNotification
 @LogId INT,
 @UserId INT
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_Notifications
WHERE
 [LogId] = @LogId
 AND [UserId] = @UserId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeletePermission') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeletePermission
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeletePermission
 @PermissionId INT
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_Permissions
WHERE
 [PermissionId] = @PermissionId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeletePermissionEntrytype') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeletePermissionEntrytype
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeletePermissionEntrytype
 @EntryType NVARCHAR (100),
 @PermissionId INT
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes
WHERE
 [EntryType] = @EntryType
 AND [PermissionId] = @PermissionId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeletePermissionPermissions') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeletePermissionPermissions
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeletePermissionPermissions
 @PortalId INT
AS
DELETE FROM
 {databaseOwner}{objectQualifier}DMX_PermissionPermissions
WHERE
 [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteProvider') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteProvider
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteProvider
 @ProviderID INT
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_Providers
WHERE
 [ProviderID] = @ProviderID
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteSetting') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteSetting
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteSetting
 @PortalId INT,
 @SettingName NVARCHAR (50)
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_Settings
WHERE
 [PortalId] = @PortalId
 AND [SettingName] = @SettingName
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteSettings') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteSettings
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteSettings
 @PortalId INT
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_Settings
WHERE
 [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DeleteSubscription') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteSubscription
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteSubscription
 @EntryId INT,
 @UserId INT
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_Subscriptions
WHERE
 [EntryId] = @EntryId
 AND [UserId] = @UserId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DirectAccess') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DirectAccess
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DirectAccess
 @PortalId INT,
 @Key NVARCHAR (100)
AS
DECLARE @Result INT
SET @Result = -1
IF EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}DMX_DirectAccessKeys
 WHERE PortalId=@PortalId AND [Key]=@Key AND Downloads>0 AND Expires>GETDATE())
BEGIN
 SET @Result = (SELECT EntryId
 FROM {databaseOwner}{objectQualifier}DMX_DirectAccessKeys
 WHERE [PortalId] = @PortalId
  AND [Key] = @Key);
 UPDATE {databaseOwner}{objectQualifier}DMX_DirectAccessKeys
 SET Downloads=Downloads-1
 WHERE PortalId=@PortalId AND [Key]=@Key;
 EXEC {databaseOwner}{objectQualifier}DMX_CleanUpDirectAccessKeys;
END
SELECT @Result
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_DMXInfo') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DMXInfo
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DMXInfo
AS
SELECT 
 p.PortalId, 
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Extensions ext WHERE ext.PortalId=p.PortalId) NrExtensions,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Permissions pe WHERE pe.PortalId=p.PortalId) NrPermissions,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_PermissionPermissions pp 
      INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions ppe ON pp.PermissionId=ppe.PermissionId WHERE ppe.PortalId=p.PortalId) NrPermissionPermissions,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_LogTypes lt WHERE lt.PortalId=p.PortalId) NrLogTypes,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Entries e WHERE e.PortalId=p.PortalId) NrEntries,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Attributes a WHERE a.PortalId=p.PortalId) NrAttributes,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Categories c WHERE c.PortalId=p.PortalId) NrCategories,
 (SELECT COUNT(e.EntryId) FROM {databaseOwner}{objectQualifier}DMX_Entries e
   INNER JOIN {databaseOwner}{objectQualifier}DMX_EntryPermissions ep ON e.EntryID=ep.EntryId
   INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON p.PermissionId=ep.PermissionId
  WHERE p.PermissionKey='APPROVE' AND e.PortalId=p.PortalId) NrApprovalItems
FROM {databaseOwner}{objectQualifier}Portals p
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryIsIn') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_EntryIsIn
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_EntryIsIn
 @PortalId Int,
 @EntryId Int,
 @CollectionId INT
AS
DECLARE @entrypath VARCHAR(4000)
SET @entrypath = (SELECT [Path] 
 FROM {databaseOwner}{objectQualifier}DMX_Entries
 WHERE EntryId = @EntryId)
IF @entrypath LIKE '%;'+CAST(@CollectionId AS VARCHAR(10))+';%' OR @EntryId = @CollectionId
 SELECT 1
ELSE
 SELECT 0
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_EntryPermissionMaintenance') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_EntryPermissionMaintenance
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_EntryPermissionMaintenance
 @PortalId INT
AS
DECLARE @NotInPortalGroup INT
SET @NotInPortalGroup=1
BEGIN TRY
 DECLARE @sSQL nvarchar(500);
 DECLARE @ParmDefinition nvarchar(500);
 SELECT @sSQL = N'SELECT @retvalOUT = COUNT(p.PortalId) FROM {databaseOwner}{objectQualifier}Portals p WHERE p.PortalId=' + CAST(@PortalId AS NVARCHAR) + ' AND p.PortalGroupID=-1';
 SET @ParmDefinition = N'@retvalOUT int OUTPUT';
 EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@NotInPortalGroup OUTPUT;
 SELECT @NotInPortalGroup;
END TRY
BEGIN CATCH
END CATCH
DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
WHERE 
 EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_Entries e WHERE e.EntryId={databaseOwner}{objectQualifier}DMX_EntryPermissions.EntryId AND e.PortalId=@PortalId)
 AND ((UserId>0 
   AND NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}Users u 
    INNER JOIN {databaseOwner}{objectQualifier}UserPortals up ON u.UserId=up.UserId
    WHERE u.UserId = {databaseOwner}{objectQualifier}DMX_EntryPermissions.UserId
     AND up.PortalId=@PortalId)
   AND @NotInPortalGroup=1)
  OR (UserId=-10 
   AND RoleId>0 
   AND NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}Roles r 
    WHERE r.RoleId = {databaseOwner}{objectQualifier}DMX_EntryPermissions.RoleId
     AND r.PortalId=@PortalId)))
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_FindAddonByName') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_FindAddonByName
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_FindAddonByName
 @Name NVarChar (50)
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Addons
WHERE
 [Name] = @Name
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_FindAttributeByKey') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_FindAttributeByKey
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_FindAttributeByKey
 @Key NVARCHAR (50),
 @PortalId INT,
 @Locale NVARCHAR(10)
AS
SELECT
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM
 {databaseOwner}{objectQualifier}DMX_Attributes a
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
WHERE
 a.[Key] = @Key
 AND [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_FindEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_FindEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_FindEntry
 @PortalId INT,
 @SearchText NVARCHAR(255),
 @Locale NVARCHAR(10)
AS
SELECT
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE
 e.PortalId=@PortalId AND
 (e.OriginalFilename LIKE '%'+@SearchText+'%' OR
 e.Keywords LIKE '%'+@SearchText+'%' OR
 e.Title LIKE '%'+@SearchText+'%' OR
 el.Title LIKE '%'+@SearchText+'%')
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetAddon') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAddon
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAddon
 @AddonKey NVARCHAR (50)
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Addons
WHERE
 [AddonKey] = @AddonKey
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetAddonsWithFeature') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAddonsWithFeature
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAddonsWithFeature
 @Feature INT
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Addons
WHERE
 ISNULL([Features], 0) & @Feature = @Feature
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetAllPortalCollections') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAllPortalCollections
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAllPortalCollections
 @PortalId INT,
 @UserId INT,
 @Locale VARCHAR(10)
AS
IF @UserId=-10
 SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
  e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
  e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
  e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
  ISNULL(el.Title, e.Title) Title
  FROM {databaseOwner}{objectQualifier}DMX_Entries e
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
  WHERE e.PortalId=@PortalId
  AND e.EntryType LIKE 'Collection%'
  ORDER BY Title
ELSE
 SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
  e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
  e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
  e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
  ISNULL(el.Title, e.Title) Title
  FROM {databaseOwner}{objectQualifier}DMX_Entries e
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
  INNER JOIN
   (SELECT DISTINCT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId AND pt.PermissionKey='VIEW' 
     LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId AND r.UserId=@UserId 
     WHERE (ep.UserId=@UserId OR NOT r.UserId IS NULL OR ep.RoleId=-1 OR (@UserId=-1 AND ep.RoleId=-3)))
   perm ON perm.EntryId=e.EntryId
  WHERE e.PortalId=@PortalId
  AND e.EntryType LIKE 'Collection%'
  ORDER BY Title
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetApplicationPermissions') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetApplicationPermissions
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetApplicationPermissions
 @PortalId INT
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_ApplicationPermissions
WHERE
 [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetApplicationPermissionsForUser') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetApplicationPermissionsForUser
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetApplicationPermissionsForUser
 @PortalId INT,
 @UserId INT
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_ApplicationPermissions ap
 LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles aur ON ap.RoleId=aur.RoleId
WHERE
 ap.PortalId = @PortalId
 AND ISNULL(aur.UserId, ap.UserId) = @UserId
 AND ap.AllowAccess = 1
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetApprovedEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetApprovedEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetApprovedEntry
 @LastVersionId INT,
 @PortalId INT,
 @Locale NVARCHAR(10)
AS
SELECT
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE
 [LastVersionId] = @LastVersionId
 AND e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE LastVersionId=e.LastVersionId AND IsApproved=1)
 AND [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetAttribute') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttribute
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttribute
 @AttributeId INT,
 @Locale NVARCHAR(10)
AS
SELECT
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM
 {databaseOwner}{objectQualifier}DMX_Attributes a
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
WHERE
 a.[AttributeId] = @AttributeId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetAttributeEntrytype') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributeEntrytype
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributeEntrytype
 @AttributeId INT,
 @EntryType NVARCHAR (50)
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes
WHERE
 [AttributeId] = @AttributeId
 AND [EntryType] = @EntryType
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetAttributeEntrytypesByAttribute') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributeEntrytypesByAttribute
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributeEntrytypesByAttribute
 @AttributeId Int
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes
WHERE
 AttributeId = @AttributeId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetAttributeListForEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributeListForEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributeListForEntry
 @PortalId INT,
 @CollectionId INT,
 @EntryId INT,
 @EntryType NVARCHAR(50),
 @IncludePrivate BIT,
 @Locale NVARCHAR(10)
AS
WITH tmp (EntryId, CollectionId) AS
 (SELECT e1.EntryId, e1.CollectionId FROM {databaseOwner}{objectQualifier}DMX_Entries e1 WHERE e1.EntryId=@CollectionId AND e1.InheritAttributes=1
  UNION ALL
  SELECT e.EntryId, e.CollectionId FROM {databaseOwner}{objectQualifier}DMX_Entries e
   INNER JOIN tmp ON e.EntryId=tmp.CollectionId WHERE e.InheritAttributes=1)
SELECT
 ea.[EntryId], 
 ea.[Value],
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName,
 CAST ((CASE WHEN (NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeId=a.AttributeId)
   OR EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeId=a.AttributeId AND @EntryType LIKE EntryType+'%')) THEN 1 ELSE 0 END) AS BIT) AS Applies
 FROM {databaseOwner}{objectQualifier}DMX_Attributes a
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
  LEFT JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes WHERE EntryId = @EntryId) ea ON ea.AttributeId=a.AttributeId
  LEFT JOIN tmp ON tmp.CollectionId=a.CollectionId
WHERE a.PortalId=@PortalId
 AND (a.IsPrivate=0 OR @IncludePrivate=1)
 AND (a.CollectionId=-1 OR (NOT tmp.CollectionId IS NULL) OR a.CollectionId=@CollectionId)
ORDER BY CASE a.CollectionId WHEN -1 THEN 0 ELSE 1 END, a.ViewOrder
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetAttributeLocalizations') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributeLocalizations
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributeLocalizations
 @AttributeId INT
AS
SELECT *
FROM {databaseOwner}{objectQualifier}DMX_AttributesML
WHERE [AttributeId] = @AttributeId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetAttributes') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributes
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributes
 @PortalId INT,
 @CollectionId INT,
 @IncludePrivate BIT,
 @Locale NVARCHAR(10)
AS
WITH tmp(EntryId , CollectionId ) AS
 (SELECT e1.EntryId, e1.CollectionId FROM {databaseOwner}{objectQualifier}DMX_Entries e1 WHERE e1.EntryId=@CollectionId AND e1.InheritAttributes=1
  UNION ALL
  SELECT e.EntryId, e.CollectionId FROM {databaseOwner}{objectQualifier}DMX_Entries e
   INNER JOIN tmp ON e.EntryId=tmp.CollectionId WHERE e.InheritAttributes=1)
SELECT
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
 FROM {databaseOwner}{objectQualifier}DMX_Attributes a
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
  LEFT JOIN tmp ON tmp.CollectionId=a.CollectionId
WHERE a.PortalId=@PortalId
 AND (a.IsPrivate=0 OR @IncludePrivate=1)
 AND (a.CollectionId=-1 OR (NOT tmp.CollectionId IS NULL) OR a.CollectionId=@CollectionId)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetAttributesByAddon') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByAddon
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByAddon
 @Addon NVARCHAR (50),
 @Locale NVARCHAR(10)
AS
SELECT
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM
 {databaseOwner}{objectQualifier}DMX_Attributes a
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
WHERE
 a.Addon = @Addon
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetAttributesByEntryType') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByEntryType
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByEntryType
 @PortalId INT,
 @EntryType NVARCHAR(50),
 @Locale NVARCHAR(10)
AS
SELECT 
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM {databaseOwner}{objectQualifier}DMX_Attributes a
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
 WHERE a.PortalId=@PortalId
 AND a.IsPrivate=0
 AND (NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeId=a.AttributeId)
  OR EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeId=a.AttributeId AND @EntryType LIKE EntryType+'%'))
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetAttributesByPortal') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByPortal
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByPortal
 @PortalId INT,
 @Locale NVARCHAR(10)
AS
SELECT
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName,
 ad.Description AS AddonsDescription
FROM
 {databaseOwner}{objectQualifier}DMX_Addons ad 
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Attributes a ON ad.AddonKey = a.Addon
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
WHERE
 a.PortalId = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetBreadCrumbs') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetBreadCrumbs
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetBreadCrumbs
 @RootId INT,
 @EntryId INT,
 @Locale VARCHAR(10)
AS
DECLARE @BCPath VARCHAR(4000)
SELECT @BCPath = [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId
DECLARE @StartPath VARCHAR(4000)
SELECT @StartPath = [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@RootId
SET @StartPath = ISNULL(@StartPath, '0;') 
SET @BCPath=REPLACE(@BCPath, @StartPath, CAST(@RootId AS VARCHAR(10))+';')
SELECT
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM {databaseOwner}{objectQualifier}DMX_Split(@BCPath, ';')
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=s
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE NOT s=''
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetCategories') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategories
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategories
 @PortalId INT,
 @EntryId INT,
 @Locale NVARCHAR(6)
AS
SELECT
 c.CategoryId, c.ParentId, c.PortalId, c.ViewOrder,
 ISNULL(cl.[CategoryName], c.CategoryName) CategoryName,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_EntryCategories ec WHERE ec.EntryId=@EntryId  AND ec.CategoryId=c.CategoryId) Selected
FROM {databaseOwner}{objectQualifier}DMX_Categories c
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntryCategories ec ON c.CategoryId=ec.CategoryId AND ec.EntryId=@EntryId
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_CategoriesML cl ON c.CategoryId=cl.CategoryId AND cl.Locale=@Locale
WHERE c.PortalId=@PortalID
ORDER BY ParentId, ViewOrder
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetCategoriesByEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoriesByEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoriesByEntry
 @PortalId INT,
 @EntryId INT,
 @Locale NVARCHAR(6)
AS
SELECT
 c.CategoryId, c.ParentId, c.PortalId, c.ViewOrder,
 ISNULL(cl.[CategoryName], c.CategoryName) CategoryName
FROM {databaseOwner}{objectQualifier}DMX_Categories c
 INNER JOIN {databaseOwner}{objectQualifier}DMX_EntryCategories ec ON c.CategoryId=ec.CategoryId AND ec.EntryId=@EntryId
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_CategoriesML cl ON c.CategoryId=cl.CategoryId AND cl.Locale=@Locale
WHERE c.PortalId=@PortalID
ORDER BY ParentId, ViewOrder
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetCategoriesByPortal') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoriesByPortal
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoriesByPortal
 @PortalId INT,
 @Locale NVARCHAR(10)
AS
SELECT
 c.CategoryId, c.ParentId, c.PortalId, c.ViewOrder,
 ISNULL(cl.[CategoryName], c.CategoryName) CategoryName
FROM
 {databaseOwner}{objectQualifier}DMX_Categories c
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_CategoriesML cl ON c.CategoryId=cl.CategoryId AND cl.Locale=@Locale
WHERE
 c.PortalId = @PortalId
ORDER BY
 c.ParentId, c.ViewOrder
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetCategory') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategory
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategory
 @CategoryId INT,
 @Locale NVARCHAR(10)
AS
SELECT
 c.CategoryId, c.ParentId, c.PortalId, c.ViewOrder,
 ISNULL(cl.[CategoryName], c.CategoryName) CategoryName
FROM
 {databaseOwner}{objectQualifier}DMX_Categories c
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_CategoriesML cl ON c.CategoryId=cl.CategoryId AND cl.Locale=@Locale
WHERE
 c.CategoryId = @CategoryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetCategoryByName') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoryByName
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoryByName
 @PortalId INT,
 @Name NVARCHAR(200),
 @Locale VARCHAR(10)
AS
SELECT c.CategoryId, c.ParentId, c.PortalId, c.ViewOrder, ISNULL(cl.CategoryName, c.CategoryName) CategoryName
 FROM {databaseOwner}{objectQualifier}DMX_Categories c
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_CategoriesML cl ON c.CategoryId=cl.CategoryId AND cl.Locale=@Locale
 WHERE c.PortalId = @PortalID
 AND cl.CategoryName = @Name OR (cl.CategoryName IS NULL AND c.CategoryName=@Name)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetCategoryLocalizations') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoryLocalizations
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoryLocalizations
 @CategoryId INT
AS
SELECT *
FROM {databaseOwner}{objectQualifier}DMX_CategoriesML
WHERE [CategoryId] = @CategoryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetCollectionContents') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCollectionContents
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCollectionContents
 @PortalId INT,
 @CollectionId INT,
 @Locale VARCHAR(10)
AS
SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 WHERE e.PortalId=@PortalId
 AND e.CollectionId=@CollectionId
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND e2.IsApproved=1))
 ORDER BY Title
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetDirectAccessKeysByEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetDirectAccessKeysByEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetDirectAccessKeysByEntry
 @EntryId INT,
 @StartRowIndex INT = -1,
 @MaximumRows INT = 0,
 @OrderBy VARCHAR(100) = ''
AS
SELECT * FROM
(SELECT
 dak.*,
 ROW_NUMBER() OVER
 (ORDER BY
  CASE @OrderBy WHEN 'PORTALID DESC' THEN dak.[PortalId] END DESC,
  CASE @OrderBy WHEN 'PORTALID' THEN dak.[PortalId] END ASC,
  CASE @OrderBy WHEN 'PORTALID ASC' THEN dak.[PortalId] END ASC,
  CASE @OrderBy WHEN 'DOWNLOADS DESC' THEN dak.[Downloads] END DESC,
  CASE @OrderBy WHEN 'DOWNLOADS' THEN dak.[Downloads] END ASC,
  CASE @OrderBy WHEN 'DOWNLOADS ASC' THEN dak.[Downloads] END ASC,
  CASE @OrderBy WHEN 'EMAIL DESC' THEN dak.[Email] END DESC,
  CASE @OrderBy WHEN 'EMAIL' THEN dak.[Email] END ASC,
  CASE @OrderBy WHEN 'EMAIL ASC' THEN dak.[Email] END ASC,
  CASE @OrderBy WHEN 'ENTRYID DESC' THEN dak.[EntryId] END DESC,
  CASE @OrderBy WHEN 'ENTRYID' THEN dak.[EntryId] END ASC,
  CASE @OrderBy WHEN 'ENTRYID ASC' THEN dak.[EntryId] END ASC,
  CASE @OrderBy WHEN 'EXPIRES DESC' THEN dak.[Expires] END DESC,
  CASE @OrderBy WHEN 'EXPIRES' THEN dak.[Expires] END ASC,
  CASE @OrderBy WHEN 'EXPIRES ASC' THEN dak.[Expires] END ASC,
  CASE @OrderBy WHEN 'KEY DESC' THEN dak.[Key] END DESC,
  CASE @OrderBy WHEN 'KEY' THEN dak.[Key] END ASC,
  CASE @OrderBy WHEN 'KEY ASC' THEN dak.[Key] END ASC,
  CASE @OrderBy WHEN 'USERID DESC' THEN dak.[UserId] END DESC,
  CASE @OrderBy WHEN 'USERID' THEN dak.[UserId] END ASC,
  CASE @OrderBy WHEN 'USERID ASC' THEN dak.[UserId] END ASC
) AS RowNum
FROM
 {databaseOwner}{objectQualifier}DMX_DirectAccessKeys dak
WHERE
 dak.EntryId = @EntryId) AS Tbl
WHERE ((RowNum BETWEEN @StartRowIndex AND (@MaximumRows + @StartRowIndex - 1)) OR @StartRowIndex = 0)
 OR (@StartRowIndex = -1 AND (RowNum-1) % @MaximumRows = 0)
ORDER BY RowNum ASC
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntriesByFilenameInCollection') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByFilenameInCollection
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByFilenameInCollection
 @PortalId INT,
 @CollectionId INT,
 @Filename NVARCHAR(255)
AS
SELECT 
 e.*
FROM {databaseOwner}{objectQualifier}DMX_Entries e
WHERE (e.CollectionId=@CollectionId OR @CollectionId=-1)
AND e.OriginalFilename=@Filename
AND PortalId=@PortalId
AND e.LastVersionId=e.EntryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntriesByPortal') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByPortal
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByPortal
 @PortalId INT,
 @IncludeDeleted BIT,
 @Locale NVARCHAR(10),
 @PageSize INT = 0,
 @StartRecord INT = 1
AS
SELECT *
FROM
(SELECT
 ROW_NUMBER() OVER (ORDER BY e.EntryId) AS rownum,
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE
 e.PortalId = @PortalId
 AND ((NOT e.Deleted = 1) OR (@IncludeDeleted = 1))) AS tmp
WHERE (@PageSize=0 OR (rownum >= @StartRecord AND rownum < @StartRecord + @PageSize))
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntriesByPortalAndProvider') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByPortalAndProvider
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntriesByUser') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByUser
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByUser
 @Owner Int,
 @IncludeDeleted BIT
AS
SELECT
 e.*
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e
WHERE
 e.Owner = @Owner
 AND ((NOT e.Deleted = 1) OR (@IncludeDeleted = 1))
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntry
 @EntryId INT,
 @Locale NVARCHAR(10)
AS
SELECT
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE
 e.[EntryId] = @EntryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryAttribute') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttribute
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttribute
 @EntryId INT,
 @AttributeId INT,
 @Locale NVARCHAR(10)
AS
SELECT
 ea.[EntryId],
 ea.[Value],
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM
 {databaseOwner}{objectQualifier}DMX_EntryAttributes ea
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Attributes a ON ea.AttributeId=a.AttributeId
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
WHERE
 ea.[EntryId] = @EntryId
 AND ea.[AttributeId] = @AttributeId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryAttributeByEntryAndKey') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributeByEntryAndKey
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributeByEntryAndKey
 @PortalId INT,
 @EntryId INT,
 @Key NVARCHAR(50),
 @Locale NVARCHAR(10)
AS
SELECT
 ea.[EntryId], 
 ea.[Value],
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM {databaseOwner}{objectQualifier}DMX_Attributes a
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
 LEFT JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes WHERE EntryId=@EntryId) ea ON ea.AttributeId=a.AttributeId
 WHERE a.PortalId=@PortalId
  AND a.[Key]=@Key
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryAttributeListForEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributeListForEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributeListForEntry
 @PortalId INT,
 @EntryId INT,
 @EntryType NVARCHAR(50),
 @Locale NVARCHAR(10)
AS
SELECT 
 ea.[EntryId], 
 ea.[Value], 
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM {databaseOwner}{objectQualifier}DMX_Attributes a
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
 LEFT JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes WHERE EntryId=@EntryId) ea
 ON ea.AttributeId=a.AttributeId
 WHERE a.PortalId=@PortalId
  AND (NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeId=a.AttributeId)
   OR EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeId=a.AttributeId AND @EntryType LIKE EntryType+'%'))
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryAttributesByAttribute') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributesByAttribute
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributesByAttribute
 @AttributeId INT,
 @Locale NVARCHAR(10)
AS
SELECT
 ea.[EntryId],
 ea.[Value],
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM
 {databaseOwner}{objectQualifier}DMX_EntryAttributes ea
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Attributes a ON ea.AttributeId=a.AttributeId
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
WHERE
 ea.AttributeId = @AttributeId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryAttributesByEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributesByEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributesByEntry
 @EntryId INT,
 @Locale NVARCHAR(10)
AS
SELECT
 ea.[EntryId],
 ea.[Value],
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM
 {databaseOwner}{objectQualifier}DMX_EntryAttributes ea
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Attributes a ON ea.AttributeId=a.AttributeId
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
WHERE
 ea.EntryId = @EntryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryAttributesByKeyAndValue') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributesByKeyAndValue
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributesByKeyAndValue
 @PortalId INT,
 @Key NVARCHAR(50),
 @AttributeValue NVARCHAR(2000),
 @Locale NVARCHAR(10)
AS
SELECT
 ea.[EntryId], 
 ea.[Value],
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName
FROM {databaseOwner}{objectQualifier}DMX_Attributes a
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
 INNER JOIN {databaseOwner}{objectQualifier}DMX_EntryAttributes ea ON ea.AttributeId=a.AttributeId
 WHERE a.PortalId=@PortalID
  AND a.[Key]=@Key
  AND ea.Value=@AttributeValue
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryBranch') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryBranch
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryBranch
 @PortalId INT,
 @EntryId INT,
 @AllVersions BIT,
 @IncludeSelf BIT,
 @Locale NVARCHAR(6)
AS
EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, @EntryId;
DECLARE @Path VARCHAR(4000)
SELECT @Path=[Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryID AND PortalId=@PortalID;
SELECT 
 e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
WHERE ((e.[Path] LIKE @Path+'%') OR (e.LastVersionId=@EntryId AND @AllVersions=1 AND @IncludeSelf=1))
 AND (@IncludeSelf=1 OR (NOT e.[Path]=@Path))
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId) OR @AllVersions=1)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryByEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByEntry
 @PortalId INT,
 @Entry AS NVARCHAR(2000)
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Entries
WHERE
 PortalId=@PortalId
AND
 Entry=@Entry
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryByFilename') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByFilename
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByFilename
 @PortalId INT,
 @UserId INT,
 @CollectionId INT,
 @OriginalFileName NVARCHAR(255),
 @Locale VARCHAR(10)
AS
IF @UserId=-10
SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 WHERE e.PortalId=@PortalId
 AND e.OriginalFileName=@OriginalFileName
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND (e2.IsApproved=1 OR e2.Owner=@UserId)))
 AND (e.CollectionId=@CollectionId OR @CollectionId=-1)
ELSE
SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 INNER JOIN
  (SELECT DISTINCT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
    INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId AND pt.PermissionKey='VIEW' 
    LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId AND r.UserId=@UserId 
    WHERE (ep.UserId=@UserId OR NOT r.UserId IS NULL OR ep.RoleId=-1 OR (@UserId=-1 AND ep.RoleId=-3)))
  perm ON perm.EntryId=e.EntryId
 WHERE e.PortalId=@PortalId
 AND e.OriginalFileName=@OriginalFileName
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND (e2.IsApproved=1 OR e2.Owner=@UserId)))
 AND (e.CollectionId=@CollectionId OR @CollectionId=-1)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryByLockKey') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByLockKey
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByLockKey
 @PortalId INT,
 @LockKey UNIQUEIDENTIFIER,
 @Locale VARCHAR(10)
AS
SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 WHERE e.PortalId=@PortalId
  AND e.LockKey=@LockKey
  AND e.LockedUntil > GETDATE()
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryByTitle') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByTitle
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByTitle
 @PortalId INT,
 @UserId INT,
 @Title NVARCHAR(1000),
 @Locale VARCHAR(10)
AS
IF @UserId=-10
 SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
  e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
  e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
  e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
  ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
  FROM {databaseOwner}{objectQualifier}DMX_Entries e
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
  WHERE e.PortalId=@PortalId
  AND (el.Title=@Title OR (el.Title IS NULL AND e.Title=@Title))
  AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND (e2.IsApproved=1 OR e2.Owner=@UserId)))
ELSE
 SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
  e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
  e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
  e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
  ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
  FROM {databaseOwner}{objectQualifier}DMX_Entries e
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
  INNER JOIN
   (SELECT DISTINCT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
     INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId AND pt.PermissionKey='VIEW' 
     LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId AND r.UserId=@UserId 
     WHERE (ep.UserId=@UserId OR NOT r.UserId IS NULL OR ep.RoleId=-1 OR (@UserId=-1 AND ep.RoleId=-3)))
   perm ON perm.EntryId=e.EntryId
  WHERE e.PortalId=@PortalId
  AND (el.Title=@Title OR (el.Title IS NULL AND e.Title=@Title))
  AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND (e2.IsApproved=1 OR e2.Owner=@UserId)))
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryByTitleInCollection') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByTitleInCollection
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByTitleInCollection
 @PortalId INT,
 @UserId INT,
 @CollectionId INT,
 @Title NVARCHAR(1000),
 @Locale VARCHAR(10)
AS
IF @UserId > -10
SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 INNER JOIN
  (SELECT DISTINCT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
    INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId AND pt.PermissionKey='VIEW' 
    LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId AND r.UserId=@UserId 
    WHERE (ep.UserId=@UserId OR NOT r.UserId IS NULL OR ep.RoleId=-1 OR (@UserId=-1 AND ep.RoleId=-3)))
  perm ON perm.EntryId=e.EntryId
 WHERE e.PortalId=@PortalId
 AND e.CollectionId=@CollectionId
 AND (el.Title=@Title OR (el.Title IS NULL AND e.Title=@Title))
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND (e2.IsApproved=1 OR e2.Owner=@UserId)))
IF @UserId = -10
SELECT e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
 e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
 e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
 e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
 ISNULL(el.Title, e.Title) Title, ISNULL(el.Remarks, e.Remarks) Remarks
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 WHERE e.PortalId=@PortalId
 AND e.CollectionId=@CollectionId
 AND (el.Title=@Title OR (el.Title IS NULL AND e.Title=@Title))
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND (e2.IsApproved=1 OR e2.Owner=@UserId)))
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryCategoriesByCategory') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryCategoriesByCategory
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryCategoriesByCategory
 @CategoryId Int
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_EntryCategories
WHERE
 CategoryId = @CategoryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryCategoriesByEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryCategoriesByEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryCategoriesByEntry
 @EntryId Int
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_EntryCategories
WHERE
 EntryId = @EntryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryCategory') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryCategory
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryCategory
 @EntryId Int,
 @CategoryId Int
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_EntryCategories
WHERE
 [EntryId] = @EntryId
 AND [CategoryId] = @CategoryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryListAsXml') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryListAsXml
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryListAsXml
 @EntryList VARCHAR(300),
 @UserId INT
AS
DECLARE @Ents TABLE (s NVARCHAR(512))
INSERT INTO @Ents (s) SELECT s FROM {databaseOwner}{objectQualifier}DMX_Split(@EntryList,';')
SELECT
 1 AS Tag,
 NULL AS Parent,
 e.EntryId AS [Entry!1!EntryId!ID], 
 e.Author AS [Entry!1!Author],
 e.CollectionId AS [Entry!1!CollectionId],
 e.Created AS [Entry!1!Created],
 e.Deleted AS [Entry!1!Deleted],
 e.Entry AS [Entry!1!Entry],
 e.EntryType AS [Entry!1!EntryType],
 e.FileSize AS [Entry!1!FileSize],
 e.IsApproved AS [Entry!1!IsApproved],
 e.Keywords AS [Entry!1!Keywords],
 e.LastVersionId AS [Entry!1!LastVersionId],
 e.LockedBy AS [Entry!1!LockedBy],
 e.OriginalFileName AS [Entry!1!OriginalFileName],
 e.PortalId AS [Entry!1!PortalId],
 e.Owner AS [Entry!1!Owner],
 e.Version AS [Entry!1!Version],
 e.LastModified AS [Entry!1!LastModified],
 e.LockedUntil AS [Entry!1!LockedUntil],
 e.LockKey AS [Entry!1!LockKey],
 e.MD5Hash AS [Entry!1!MD5Hash],
 e.State AS [Entry!1!State],
 e.VersionsToKeep AS [Entry!1!VersionsToKeep],
 e.WorkflowId AS [Entry!1!WorkflowId],
 ext.EditControl AS [Entry!1!EditControl],
 ext.ViewControl AS [Entry!1!ViewControl],
 REPLACE({databaseOwner}{objectQualifier}DMX_fn_URLEncode({databaseOwner}{objectQualifier}DMX_fn_GetPath(e.EntryId, e.PortalId, '')), '''', '\''') AS [Entry!1!FullPath],
 CAST (CASE WHEN e.LockedUntil > GETDATE() THEN 1 ELSE 0 END AS BIT) AS [Entry!1!IsLocked],
 CAST (CASE WHEN subs.EntryID IS NULL THEN 0 ELSE 1 END AS BIT) AS [Entry!1!Subscription],
 NULL AS [Permission!2!PermissionKey]
FROM
 @Ents ents
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON ents.s=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Extensions ext ON ext.ExtensionKey=e.EntryType AND e.PortalId=ext.PortalId
 LEFT JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_Subscriptions WHERE UserID=@UserId) subs ON subs.EntryId=e.EntryId
UNION ALL
SELECT DISTINCT
 2,
 1,
 e.EntryId, 
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 p.PermissionKey AS [Permission!2!PermissionKey]
FROM
 @Ents ents
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON ents.s=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_EntryPermissions ep ON ep.EntryId=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON p.PermissionId=ep.PermissionId
 LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId AND r.UserId=@UserId
WHERE
 (ep.UserId=@UserId OR NOT r.UserId IS NULL OR ep.RoleId=-1)
FOR XML EXPLICIT
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryLocalizations') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryLocalizations
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryLocalizations
 @EntryId INT
AS
SELECT *
FROM {databaseOwner}{objectQualifier}DMX_EntriesML
WHERE [EntryId] = @EntryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryPermissionsByEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryPermissionsByEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryPermissionsByEntry
 @EntryId Int
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_EntryPermissions
WHERE
 EntryId = @EntryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryPermissionsForUser') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryPermissionsForUser
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 AND ur.UserId=@UserId
WHERE 
 (ep.RoleId=-1 OR NOT ur.UserId IS NULL OR ep.UserId=@UserId) 
 AND (ep.EntryId=@EntryId) 
 AND (ep.PortalId=@PortalId)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEntryTitle') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryTitle
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryTitle
 @PortalId INT,
 @EntryId INT,
 @Locale VARCHAR(10)
AS
SELECT ISNULL(el.Title, e.Title) Title
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
 WHERE e.EntryID=@EntryId
 AND e.PortalID=@PortalId
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId AND e2.IsApproved=1))
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetEventLog') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEventLog
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEventLog
AS
SELECT *
FROM {databaseOwner}{objectQualifier}EventLog
WHERE LogTypeKey LIKE '%_EXCEPTION'
ORDER BY LogCreateDate
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetExtension') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetExtension
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetExtension
 @ExtensionKey NVARCHAR (50),
 @PortalId INT
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Extensions
WHERE
 [ExtensionKey] = @ExtensionKey
 AND [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetExtensionsByAddon') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetExtensionsByAddon
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetExtensionsByAddon
 @Addon NVarChar (50)
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Extensions
WHERE
 Addon = @Addon
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetExtensionsByPortal') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetExtensionsByPortal
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetExtensionsByPortal
 @PortalId Int
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Extensions
WHERE
 PortalId = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetFromRepository') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetFromRepository
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetHostSetting') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetHostSetting
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetHostSetting
 @SettingName nvarchar(50)
AS
SELECT *
FROM {databaseOwner}{objectQualifier}HostSettings
WHERE SettingName = @SettingName
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetLastActionEvent') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLastActionEvent
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLastActionEvent
 @EntryId INT,
 @Action VARCHAR(50)
AS
SELECT MAX(Datime) FROM {databaseOwner}{objectQualifier}DMX_Log
WHERE EntryId=@EntryId
AND Action=@Action
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetLog') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLog
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLog
 @LogId INT
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_Log
WHERE
 [LogId] = @LogId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetLogByTime') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogByTime
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogByTime
 @PortalId Int,
 @StartTime DATETIME,
 @EndTime DATETIME
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_Log
WHERE
 PortalId = @PortalId
 AND Datime >= @StartTime
 AND Datime < @EndTime
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetLogsByEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByEntry
 @EntryId INT,
 @MaxRecords INT
AS
IF @MaxRecords > 0
 BEGIN
  DECLARE @SQL varchar(max);
  SET @SQL = 'SELECT TOP ' + Convert(varchar, @MaxRecords);
  SET @SQL = @SQL + ' *';
  SET @SQL = @SQL + ' FROM {databaseOwner}{objectQualifier}vw_DMX_Log';
  SET @SQL = @SQL + ' WHERE EntryId = ' + Convert(varchar, @EntryId) + ' ORDER BY Datime DESC';
  EXEC(@SQL);
 END
ELSE
 SELECT
  *
 FROM
  {databaseOwner}{objectQualifier}vw_DMX_Log
 WHERE
  EntryId = @EntryId
  ORDER BY Datime DESC;
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetLogsByPortal') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByPortal
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByPortal
 @PortalId Int
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_Log
WHERE
 PortalId = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetLogsByUser') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByUser
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByUser
 @UserId Int
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_Log
WHERE
 UserId = @UserId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetLogType') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogType
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogType
 @LogTypeKey NVARCHAR (20),
 @PortalId INT
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_LogTypes
WHERE
 [LogTypeKey] = @LogTypeKey
 AND [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetLogTypes') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogTypes
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogTypes
 @PortalId Int
AS
SELECT
 lt.*
FROM
 {databaseOwner}{objectQualifier}DMX_LogTypes lt
WHERE
 [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetModuleUsageList') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetModuleUsageList
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetModuleUsageList
AS
SELECT 
 dm.FolderName,
 dm.ModuleName,
 dm.FriendlyName,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}Modules m
   INNER JOIN {databaseOwner}{objectQualifier}ModuleDefinitions md ON m.ModuleDefID=md.ModuleDefID
   WHERE md.DesktopModuleID=dm.DesktopModuleID) AS NrInstances
FROM {databaseOwner}{objectQualifier}DesktopModules dm
 ORDER BY dm.FolderName
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetNotification') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetNotification
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetNotification
 @LogId INT,
 @UserId INT
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_Notifications
WHERE
 [LogId] = @LogId
 AND [RecipientUserId] = @UserId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetParentChain') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetParentChain
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetParentChain
 @PortalId INT,
 @EntryId INT,
 @Locale NVARCHAR(10)
AS
 DECLARE @CollectionId INT
 SELECT @CollectionId = (SELECT CollectionId FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId)
 IF @CollectionId>0
  EXEC {databaseOwner}{objectQualifier}DMX_GetParentChain @PortalId, @CollectionId, @Locale
 SELECT 
  e.Author, e.CollectionId, e.Deleted, e.Entry, e.EntryId, e.EntryType, e.FileSize, e.Created, e.IsApproved,
  e.Keywords, e.LastModified, e.LastVersionId, e.LockedBy, e.LockedUntil, e.LockKey, e.MD5Hash, e.OriginalFileName,
  e.Owner, e.PortalId, e.State, e.Version, e.VersionsToKeep, e.WorkflowId, e.Path, e.PermissionsOnlyByAdmin,
  e.InheritAttributes, e.StorageProviderID, e.Thumbnail, e.Hidden,
  ISNULL(el.Title, e.Title) Title
  FROM {databaseOwner}{objectQualifier}DMX_Entries e
   LEFT JOIN {databaseOwner}{objectQualifier}DMX_EntriesML el ON e.EntryId=el.EntryId AND el.Locale=@Locale
   WHERE e.[EntryId] = @EntryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetPermission') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermission
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermission
 @PermissionId INT
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Permissions
WHERE
 [PermissionId] = @PermissionId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetPermissionByKey') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionByKey
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionByKey
 @PermissionKey NVARCHAR(20),
 @PortalId INT
AS
SELECT p.* 
 FROM {databaseOwner}{objectQualifier}DMX_Permissions p 
 WHERE (p.PermissionKey=@PermissionKey) AND (p.PortalId=@PortalId)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetPermissionEntrytype') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionEntrytype
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionEntrytype
 @EntryType NVARCHAR (100),
 @PermissionId INT
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes
WHERE
 [EntryType] = @EntryType
 AND [PermissionId] = @PermissionId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetPermissionEntrytypesByPermission') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionEntrytypesByPermission
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionEntrytypesByPermission
 @PermissionId Int
AS
SELECT
 * 
FROM
 {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes
WHERE
 PermissionId = @PermissionId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetPermissionPermissions') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionPermissions
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionPermissions
 @PortalId INT
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_PermissionPermissions
WHERE
 [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetPermissionsByPortal') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionsByPortal
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 AND ur.UserId=@UserId
   WHERE pp.PermissionId=p.PermissionId AND pp.AllowAccess=1
    AND (NOT ur.UserId IS NULL OR pp.RoleId=-1)) AS Bit) AS IsAllowedToEdit 
 FROM {databaseOwner}{objectQualifier}DMX_Permissions p 
 WHERE (p.PortalId=@PortalID)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetPermissionsForEntrytype') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionsForEntrytype
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 AND ur.UserId=@UserId
   WHERE pp.PermissionId=p.PermissionId AND pp.AllowAccess=1
    AND (NOT ur.UserId IS NULL 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 sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetPortal') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPortal
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetPortalAttributeListWithEntryData') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPortalAttributeListWithEntryData
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPortalAttributeListWithEntryData
 @PortalId INT,
 @EntryId INT,
 @EntryType NVARCHAR(50),
 @IncludePrivate BIT,
 @Locale NVARCHAR(10)
AS
SELECT
 ea.[EntryId], 
 ea.[Value],
 a.Addon, a.AttributeId, a.ControlToLoad, a.IsPrivate, a.[Key], a.PortalId, a.Required,
 a.ResourceFile, a.[Values], a.ValueType, a.ViewOrder, a.CollectionId, a.ShowInUI,
 ISNULL(al.AttributeName, a.AttributeName) AttributeName,
 CAST ((CASE WHEN (NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeId=a.AttributeId)
   OR EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeId=a.AttributeId AND @EntryType LIKE EntryType+'%')) THEN 1 ELSE 0 END) AS BIT) AS Applies
 FROM {databaseOwner}{objectQualifier}DMX_Attributes a
  LEFT JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes WHERE EntryId = @EntryId) ea ON ea.AttributeId=a.AttributeId
  LEFT JOIN {databaseOwner}{objectQualifier}DMX_AttributesML al ON al.AttributeId=a.AttributeId AND al.Locale=@Locale
  WHERE a.PortalId=@PortalId
  AND (a.IsPrivate=0 OR @IncludePrivate=1)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetPortalDetails') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPortalDetails
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPortalDetails
AS
SELECT 
 p.*, 
 (SELECT COUNT(*) FROM {objectQualifier}Users u INNER JOIN {objectQualifier}UserPortals up ON u.UserId=up.UserId WHERE up.PortalId=p.PortalId) AS NrUsers, 
 (SELECT COUNT(*) FROM {objectQualifier}Tabs t WHERE t.PortalID=p.PortalID) AS NrTabs 
FROM {objectQualifier}Portals p
 ORDER BY p.PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetProvider') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetProvider
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetProvider
 @ProviderID INT
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Providers
WHERE
 [ProviderID] = @ProviderID
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetProvidersByType') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetProvidersByType
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetProvidersByType
 @ProviderType Int
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Providers
WHERE
 [ProviderType] = @ProviderType
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetSetting') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSetting
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSetting
 @PortalId INT,
 @SettingName NVARCHAR (50)
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Settings
WHERE
 [PortalId] = @PortalId
 AND [SettingName] = @SettingName
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetSettings') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSettings
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSettings
 @PortalId INT
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Settings
WHERE
 [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetSpecificEntryPermission') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSpecificEntryPermission
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 AND ur.UserId=@UserId
WHERE (ep.UserID=@UserId
 OR NOT ur.UserId IS NULL
 OR ep.RoleId=-1)
 AND ep.PortalId=@PortalId
 AND ep.EntryId=@EntryId
 AND p.PermissionKey=@PermissionKey
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetSubCategories') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubCategories
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubCategories
 @PortalId INT,
 @CategoryId INT,
 @Locale NVARCHAR(6)
AS
SELECT
 c.CategoryId, c.ParentId, c.PortalId, c.ViewOrder,
 ISNULL(cl.[CategoryName], c.CategoryName) CategoryName
FROM {databaseOwner}{objectQualifier}DMX_Categories c
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_CategoriesML cl ON c.CategoryId=cl.CategoryId AND cl.Locale=@Locale
WHERE c.PortalId=@PortalID
 AND c.ParentId=@CategoryId
ORDER BY ViewOrder, CategoryName
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetSubscription') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubscription
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubscription
 @EntryId INT,
 @UserId INT
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Subscriptions
WHERE
 [EntryId] = @EntryId
 AND [UserId] = @UserId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetSubscriptionsByEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubscriptionsByEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubscriptionsByEntry
 @EntryId Int
AS
SELECT
 s.*,
 u.DisplayName
FROM
 {databaseOwner}{objectQualifier}Users u INNER JOIN {databaseOwner}{objectQualifier}DMX_Subscriptions s 
  ON u.UserID = s.UserId
WHERE
 s.EntryId = @EntryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetSubscriptionsByUser') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubscriptionsByUser
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubscriptionsByUser
 @UserId Int
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Subscriptions
WHERE
 UserId = @UserId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetSubscriptionsForEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubscriptionsForEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 AND ur.UserID=s.UserId
  WHERE ep1.EntryId=@EntryId AND (NOT ur.UserId IS NULL OR ep1.UserId=s.UserId OR ep1.RoleId=-1)
   AND Permission.PermissionKey='VIEW')
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetUnsentNotifications') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUnsentNotifications
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUnsentNotifications
 @PortalId int,
 @UserId Int
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}vw_DMX_Notifications
WHERE
 [RecipientUserId] = @UserId
 AND [PortalId] = @PortalId
 AND [Sent] IS NULL
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetUserPermissions') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUserPermissions
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUserPermissions
 @EntryId Int,
 @PortalId Int,
 @UserId Int
AS
SELECT DISTINCT p.PermissionKey
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 AND ur.UserId=@UserId
LEFT JOIN {databaseOwner}{objectQualifier}Users u ON u.UserId=@UserId
WHERE ep.EntryId=@EntryId
AND ep.PortalId=@PortalId
AND p.PortalId=@PortalId
AND (ep.RoleID=-1 OR NOT ur.UserId IS NULL OR ep.UserId=@UserId OR u.IsSuperUser=1)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetUserRoles') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUserRoles
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUserRoles
 @PortalId INT,
 @UserId INT,
 @Roles VARCHAR(50)
AS
SELECT r.*
FROM {databaseOwner}{objectQualifier}Roles r
 INNER JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles aur ON r.RoleId=aur.RoleId
WHERE aur.UserId=@UserId
 AND @Roles LIKE '%;'+CAST(aur.RoleId AS VARCHAR)+';%'
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetUserRolesByRoleId') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUserRolesByRoleId
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUserRolesByRoleId
 @PortalId INT,
 @RoleId INT
AS
SELECT ur.*
 FROM {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur
 INNER JOIN {databaseOwner}{objectQualifier}Roles r ON r.RoleId=ur.RoleID
 WHERE r.PortalID=@PortalId
 AND NOT ur.RoleID IS NULL
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetUsersByRoleId') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUsersByRoleId
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUsersByRoleId
 @RoleId INT
AS
SELECT u.*
FROM {databaseOwner}{objectQualifier}Users u
 INNER JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles aur ON u.UserId=aur.UserId
WHERE aur.RoleId=@RoleId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_GetUsersToNotify') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUsersToNotify
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUsersToNotify
 @PortalId int
AS
SELECT 
 u.*
FROM
 {databaseOwner}{objectQualifier}Users u
WHERE
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Notifications n WHERE n.[UserId] = u.UserId AND n.[PortalId] = @PortalId AND n.[Sent] IS NULL)>0
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_IsEntryPermissionByAdmin') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_IsEntryPermissionByAdmin
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_IsEntryPermissionByAdmin
 @PortalId Int,
 @EntryId Int
AS
 IF EXISTS(SELECT PermissionsOnlyByAdmin FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId AND PermissionsOnlyByAdmin=1)
  SELECT 1
 ELSE
  DECLARE @Path VARCHAR(2000)
  SELECT @Path = ';'+(SELECT Path FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId)+';'
  IF EXISTS(SELECT PermissionsOnlyByAdmin FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE PermissionsOnlyByAdmin=1 AND @Path LIKE '%;'+CAST(EntryId AS VARCHAR(10))+';%')
   SELECT 1
  ELSE
   SELECT 0
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_IsSuperUser') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_IsSuperUser
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_IsSuperUser
 @UserId INT
AS
SELECT ISNULL(IsSuperUser, 0) IsSuperUser
 FROM {databaseOwner}{objectQualifier}Users
 WHERE UserID=@UserId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_LimitSearch') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_LimitSearch
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_LimitSearch
 @PortalId Int,
 @SearchId nvarchar(50),
 @CollectionId Int,
 @OnlyLastVersion BIT,
 @HideDeleted BIT,
 @OnlyApproved BIT,
 @NrHits INT
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
 IF @NrHits > 1
 BEGIN
  DELETE FROM {databaseOwner}{objectQualifier}DMX_SearchResults
  WHERE SearchId=@SearchId
   AND Hits < @NrHits;
 END
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_ListProviders') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_ListProviders
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_ListProviders
AS
SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Providers
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_LockEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_LockEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_MoveAllEntries') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveAllEntries
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveAllEntries
 @PortalId Int,
 @OldCollectionId Int,
 @NewCollectionId Int
AS
UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET
 [CollectionId] = @NewCollectionId
WHERE
 [CollectionId] = @OldCollectionId
 AND [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_MoveAttribute') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveAttribute
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveAttribute
 @PortalId INT,
 @AttributeId INT,
 @Displacement INT
AS
BEGIN
DECLARE @CollectionId INT
SET @CollectionId = (SELECT CollectionId FROM {databaseOwner}{objectQualifier}DMX_Attributes WHERE PortalId=@PortalId AND AttributeId=@AttributeId)
DECLARE @LastIndex INT
SET @LastIndex = (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Attributes WHERE PortalId=@PortalId AND CollectionId=@CollectionId)
DECLARE @OldIndex INT
SET @OldIndex = (SELECT ViewOrder FROM {databaseOwner}{objectQualifier}DMX_Attributes WHERE PortalId=@PortalId AND AttributeId=@AttributeId)
DECLARE @NewIndex INT
SET @NewIndex = @OldIndex + @Displacement
IF @NewIndex > -1 AND @NewIndex < @LastIndex
 BEGIN
  DECLARE @ReplacedAttribute INT
  SET @ReplacedAttribute = (SELECT AttributeId FROM {databaseOwner}{objectQualifier}DMX_Attributes WHERE PortalId=@PortalId AND CollectionId=@CollectionId AND ViewOrder=@NewIndex)
  UPDATE {databaseOwner}{objectQualifier}DMX_Attributes
   SET ViewOrder = @NewIndex
   WHERE PortalId=@PortalId AND AttributeId=@AttributeId;
  UPDATE {databaseOwner}{objectQualifier}DMX_Attributes
   SET ViewOrder = @OldIndex
   WHERE PortalId=@PortalId AND AttributeId=@ReplacedAttribute;
 END
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_MoveEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveEntry
 @PortalId Int,
 @EntryId Int,
 @NewCollectionId INT
AS
DECLARE @destpath VARCHAR(4000)
IF @NewCollectionId=0
 SET @destpath = '0;'
ELSE
 SET @destpath = (SELECT [Path] 
  FROM {databaseOwner}{objectQualifier}DMX_Entries
  WHERE EntryId = @NewCollectionId);
IF NOT @destpath LIKE '%;'+CAST(@EntryId AS VARCHAR(10))+';%' AND @EntryId <> @NewCollectionId
UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET CollectionId=@NewCollectionId
WHERE
 LastVersionId=@EntryId
 AND PortalId=@PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_MoveEntryAttributes') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveEntryAttributes
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveEntryAttributes
 @Path VARCHAR(4000),
 @OldAttributeId INT,
 @NewAttributeId INT
AS
UPDATE {databaseOwner}{objectQualifier}DMX_EntryAttributes
SET AttributeId=@NewAttributeId
WHERE AttributeId=@OldAttributeId
 AND EntryId IN 
 (SELECT EntryId FROM {databaseOwner}{objectQualifier}DMX_Entries 
 WHERE Path LIKE @Path+'%')
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_MoveLog') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveLog
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveLog
 @OldEntryId INT,
 @NewEntryId INT
AS
UPDATE {databaseOwner}{objectQualifier}DMX_Log
SET EntryId=@NewEntryId WHERE EntryId=@OldEntryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_NumberOfCategories') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_NumberOfCategories
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_NumberOfCategories
 @PortalId INT
AS
SELECT COUNT(*)
FROM {databaseOwner}{objectQualifier}DMX_Categories c
WHERE c.PortalId=@PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_RemoveDeadPermissions') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_RemoveDeadPermissions
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_RemoveDeadPermissions
AS
DELETE FROM
 {databaseOwner}{objectQualifier}DMX_EntryPermissions
WHERE
 (NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}Roles r WHERE r.RoleId={databaseOwner}{objectQualifier}DMX_EntryPermissions.RoleId) AND RoleId>-1 AND UserId=-10)
OR
 (NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}Users s WHERE s.UserId={databaseOwner}{objectQualifier}DMX_EntryPermissions.UserId) AND UserId>-1 AND RoleId=-1)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_RemoveOldUserNotifications') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_RemoveOldUserNotifications
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_RemoveOldUserNotifications
AS
DELETE FROM {databaseOwner}{objectQualifier}DMX_Notifications
FROM {databaseOwner}{objectQualifier}DMX_Notifications n
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Log l ON l.LogId=n.LogId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=l.EntryId
 LEFT JOIN {databaseOwner}{objectQualifier}UserPortals up ON up.UserId=n.UserId AND up.PortalId=e.PortalId
WHERE n.Sent IS NULL AND
 up.UserPortalId IS NULL
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_RemoveOldUserSubscriptions') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_RemoveOldUserSubscriptions
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_RemoveOldUserSubscriptions
AS
-- DNN 5+
BEGIN TRY
 EXEC('DELETE FROM {databaseOwner}{objectQualifier}DMX_Subscriptions
FROM {databaseOwner}{objectQualifier}DMX_Subscriptions s
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON s.EntryId=e.EntryId
WHERE
 NOT EXISTS (SELECT up.UserId FROM {databaseOwner}{objectQualifier}UserPortals up WHERE up.PortalId=e.PortalId AND up.UserId=s.UserId AND up.Authorised=1 AND up.IsDeleted=0)
 OR NOT EXISTS (SELECT u.UserId FROM {databaseOwner}{objectQualifier}Users u WHERE u.UserId=s.UserId)');
END TRY
BEGIN CATCH
END CATCH;
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_RenameEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_RenameEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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
   IF @Locale=''
    UPDATE {databaseOwner}{objectQualifier}DMX_Entries
     SET Title=@NewName WHERE EntryId=@EntryId AND PortalId=@PortalId
   ELSE BEGIN
    IF EXISTS (SELECT EntryId FROM {databaseOwner}{objectQualifier}DMX_EntriesML WHERE [EntryId] = @EntryId AND [Locale] = @Locale)
     UPDATE {databaseOwner}{objectQualifier}DMX_EntriesML
     SET [Title] = @NewName
     WHERE [EntryId] = @EntryId AND [Locale] = @Locale
    ELSE
     INSERT INTO {databaseOwner}{objectQualifier}DMX_EntriesML
      ([EntryId], [Locale], [Title])
      VALUES (@EntryId, @Locale, @NewName)
   END
  END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_ReparseDMXFileTypes') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_ReparseDMXFileTypes
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_ReparseDMXFileTypes
 @PortalId INT,
 @ExtensionKey NVARCHAR (50),
 @Extension VARCHAR (10)
AS
DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerUpdate ON {databaseOwner}{objectQualifier}DMX_Entries;
UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET EntryType=@ExtensionKey
 WHERE OriginalFilename LIKE '%.'+@Extension
  AND PortalId=@PortalId;
ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerUpdate ON {databaseOwner}{objectQualifier}DMX_Entries;
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_ResetFolderSizes') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_ResetFolderSizes
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_ResetPermissions') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_ResetPermissions
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_ResetPermissions
 @PortalId Int,
 @EntryId Int
AS
DECLARE @AdminRoleId INT
SELECT @AdminRoleId = (SELECT AdministratorRoleId FROM {databaseOwner}{objectQualifier}Portals WHERE PortalId=@PortalId);
DECLARE @BasePath VARCHAR(2000)
SELECT @BasePath = (SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId);
DELETE {databaseOwner}{objectQualifier}DMX_EntryPermissions
 FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=ep.EntryId
 WHERE e.PortalId=@PortalId AND e.[Path] LIKE @BasePath+'%';
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
 (AllowAccess, EntryId, Expires, PermissionId, PortalId, RoleId, UserId)
 SELECT 1, e.EntryId, NULL, p.PermissionId, 0, @AdminRoleId, -10
 FROM {databaseOwner}{objectQualifier}DMX_Entries e, {databaseOwner}{objectQualifier}DMX_Permissions p
 WHERE 
  e.PortalId=@PortalId AND e.[Path] LIKE @BasePath+'%' AND (NOT e.EntryType LIKE 'Collection%')
  AND (p.PermissionKey = 'EDIT' OR p.PermissionKey = 'VIEW');
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
 (AllowAccess, EntryId, Expires, PermissionId, PortalId, RoleId, UserId)
 SELECT 1, e.EntryId, NULL, p.PermissionId, 0, @AdminRoleId, -10
 FROM {databaseOwner}{objectQualifier}DMX_Entries e, {databaseOwner}{objectQualifier}DMX_Permissions p
 WHERE 
  e.PortalId=@PortalId AND e.[Path] LIKE @BasePath+'%' AND e.EntryType LIKE 'Collection%'
  AND (p.PermissionKey = 'EDIT' OR p.PermissionKey = 'VIEW'  OR p.PermissionKey = 'ADD');
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_ResortCategories') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_ResortCategories
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_ResortCategories
 @PortalId INT,
 @ParentId INT
AS
UPDATE {databaseOwner}{objectQualifier}DMX_Categories
 SET ViewOrder = subcats.vo
FROM {databaseOwner}{objectQualifier}DMX_Categories c
 INNER JOIN (SELECT
  CategoryId,
  ROW_NUMBER() OVER(ORDER BY ViewOrder ASC) - 1 vo
  FROM {databaseOwner}{objectQualifier}DMX_Categories
  WHERE PortalId=@PortalId AND ParentId=@ParentId) subcats ON subcats.CategoryId=c.CategoryId
  
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_SearchResultsClean') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SearchResultsClean
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SearchResultsClean
 @SearchId NVARCHAR(50)
AS
BEGIN
CREATE TABLE #RES (EntryId INT, [Rank] REAL, Hits INT, Extract NVARCHAR(2000));
INSERT INTO #RES (EntryId, [Rank], Hits, Extract)
SELECT EntryId, SUM([Rank]), COUNT(Hits), {databaseOwner}{objectQualifier}DMX_ExtractConcatenate(EntryId, ', ')
FROM {databaseOwner}{objectQualifier}DMX_SearchResults
GROUP BY SearchId, EntryId
HAVING SearchId=@SearchId OR SearchId LIKE @SearchId + '-%';
DECLARE @maxRank REAL;
SET @maxRank = (SELECT MAX([Rank]) FROM #RES);
IF @maxRank=0
 SET @maxRank=1;
UPDATE #RES
SET [Rank] = [Rank]/@maxRank*1000;
DELETE FROM {databaseOwner}{objectQualifier}DMX_SearchResults
WHERE SearchId=@SearchId;
INSERT INTO {databaseOwner}{objectQualifier}DMX_SearchResults
 (SearchId, EntryId, [Rank], Hits, Extract)
SELECT
 @SearchId, EntryId, [Rank], Hits, Extract
FROM #RES;
DROP TABLE #RES;
DELETE FROM {databaseOwner}{objectQualifier}DMX_SearchResults
WHERE SearchId LIKE @SearchId + '-%';
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_SetApplicationPermission') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetApplicationPermission
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetApplicationPermission
 @PortalId INT, 
 @AllowAccess BIT, 
 @Expires DATETIME, 
 @PermissionId INT, 
 @RoleId INT, 
 @UserId INT
AS
IF NOT EXISTS
 (SELECT * FROM {databaseOwner}{objectQualifier}DMX_ApplicationPermissions
  WHERE PortalId=@PortalId AND PermissionId=@PermissionId AND RoleId=@RoleId AND UserId=@UserId)
INSERT INTO {databaseOwner}{objectQualifier}DMX_ApplicationPermissions (
 [PortalId],
 [AllowAccess],
 [Expires],
 [PermissionId],
 [RoleId],
 [UserId])
VALUES (
 @PortalId,
 @AllowAccess,
 @Expires,
 @PermissionId,
 @RoleId,
 @UserId)
ELSE
UPDATE {databaseOwner}{objectQualifier}DMX_ApplicationPermissions SET
 [AllowAccess] = @AllowAccess,
 [Expires] = @Expires
WHERE
 [PermissionId] = @PermissionId
 AND [PortalId] = @PortalId
 AND [RoleId] = @RoleId
 AND [UserId] = @UserId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_SetAttributeLocalization') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetAttributeLocalization
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetAttributeLocalization
 @AttributeId INT,
 @Locale VARCHAR(10),
 @AttributeName NVARCHAR(200)
AS
IF @AttributeName=''
 DELETE FROM {databaseOwner}{objectQualifier}DMX_AttributesML
 WHERE [AttributeId] = @AttributeId AND [Locale] = @Locale
ELSE BEGIN
 IF EXISTS (SELECT AttributeName FROM {databaseOwner}{objectQualifier}DMX_AttributesML WHERE [AttributeId] = @AttributeId AND [Locale] = @Locale)
  UPDATE {databaseOwner}{objectQualifier}DMX_AttributesML
   SET [AttributeName] = @AttributeName
   WHERE [AttributeId] = @AttributeId AND [Locale] = @Locale
 ELSE
  INSERT INTO {databaseOwner}{objectQualifier}DMX_AttributesML
   ([AttributeId], [Locale], [AttributeName])
   VALUES (@AttributeId, @Locale, @AttributeName)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_SetCategoryLocalization') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetCategoryLocalization
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetCategoryLocalization
 @CategoryId INT,
 @Locale VARCHAR(10),
 @CategoryName NVARCHAR(200)
AS
IF @CategoryName=''
 DELETE FROM {databaseOwner}{objectQualifier}DMX_CategoriesML
 WHERE [CategoryId] = @CategoryId AND [Locale] = @Locale
ELSE BEGIN
 IF EXISTS (SELECT CategoryName FROM {databaseOwner}{objectQualifier}DMX_CategoriesML WHERE [CategoryId] = @CategoryId AND [Locale] = @Locale)
  UPDATE {databaseOwner}{objectQualifier}DMX_CategoriesML
   SET [CategoryName] = @CategoryName
   WHERE [CategoryId] = @CategoryId AND [Locale] = @Locale
 ELSE
  INSERT INTO {databaseOwner}{objectQualifier}DMX_CategoriesML
   ([CategoryId], [Locale], [CategoryName])
   VALUES (@CategoryId, @Locale, @CategoryName)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_SetEntryCategory') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetEntryCategory
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetEntryCategory
 @EntryId INT, 
 @CategoryId INT
AS
IF NOT EXISTS
 (SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryCategories
  WHERE EntryId=@EntryId AND CategoryId=@CategoryId)
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryCategories (
 [EntryId],
 [CategoryId])
VALUES (
 @EntryId,
 @CategoryId)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_SetEntryLocalization') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetEntryLocalization
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetEntryLocalization
 @EntryId INT,
 @Locale VARCHAR(10),
 @Title NVARCHAR(1000),
 @Remarks NVARCHAR(MAX)
AS
IF @Title='' AND (@Remarks IS NULL OR @REMARKS = '')
 DELETE FROM {databaseOwner}{objectQualifier}DMX_EntriesML
 WHERE [EntryId] = @EntryId AND [Locale] = @Locale
ELSE BEGIN
 IF EXISTS (SELECT EntryId FROM {databaseOwner}{objectQualifier}DMX_EntriesML WHERE [EntryId] = @EntryId AND [Locale] = @Locale)
  UPDATE {databaseOwner}{objectQualifier}DMX_EntriesML
   SET [Title] = @Title, [Remarks] = @Remarks
   WHERE [EntryId] = @EntryId AND [Locale] = @Locale
 ELSE
  INSERT INTO {databaseOwner}{objectQualifier}DMX_EntriesML
   ([EntryId], [Locale], [Title], [Remarks])
   VALUES (@EntryId, @Locale, @Title, @Remarks)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_SetEntryThumbnail') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetEntryThumbnail
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetEntryThumbnail
 @PortalId INT,
 @EntryId INT,
 @Thumbnail VARCHAR(200)
AS
UPDATE {databaseOwner}{objectQualifier}DMX_Entries
SET Thumbnail = @Thumbnail
WHERE PortalId = @PortalId
 AND EntryId = @EntryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_SetLastModified') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetLastModified
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_SetLastVersionID') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetLastVersionID
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_SetPermissionPermission') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetPermissionPermission
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetPermissionPermission
 @PortalId INT, 
 @AllowAccess BIT, 
 @Expires DATETIME, 
 @PermissionId INT, 
 @RoleId INT, 
 @UserId INT
AS
IF NOT EXISTS
 (SELECT * FROM {databaseOwner}{objectQualifier}DMX_PermissionPermissions
  WHERE PortalId=@PortalId AND PermissionId=@PermissionId AND RoleId=@RoleId AND UserId=@UserId)
INSERT INTO {databaseOwner}{objectQualifier}DMX_PermissionPermissions (
 [PortalId],
 [AllowAccess],
 [Expires],
 [PermissionId],
 [RoleId],
 [UserId])
VALUES (
 @PortalId,
 @AllowAccess,
 @Expires,
 @PermissionId,
 @RoleId,
 @UserId)
ELSE
UPDATE {databaseOwner}{objectQualifier}DMX_PermissionPermissions SET
 [AllowAccess] = @AllowAccess,
 [Expires] = @Expires
WHERE
 [PermissionId] = @PermissionId
 AND [PortalId] = @PortalId
 AND [RoleId] = @RoleId
 AND [UserId] = @UserId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_SetSetting') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SetSetting
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetSetting
 @PortalId INT,
 @SettingName NVARCHAR (50),
 @SettingValue NVARCHAR (2000)
AS
IF EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}DMX_Settings WHERE [PortalId] = @PortalId AND [SettingName] = @SettingName)
 UPDATE {databaseOwner}{objectQualifier}DMX_Settings SET
  [SettingValue] = @SettingValue
 WHERE
  [PortalId] = @PortalId
  AND [SettingName] = @SettingName
ELSE
 INSERT INTO {databaseOwner}{objectQualifier}DMX_Settings (
  [PortalId],
  [SettingName],
  SettingValue
 ) VALUES (
  @PortalId,
  @SettingName,
  @SettingValue
 )
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_SoftDeleteEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_SoftDeleteEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SoftDeleteEntry
 @PortalId Int,
 @EntryId Int,
 @Delete Bit
AS
EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, @EntryId;
DECLARE @LastVersionId INT
SELECT @LastVersionId=(SELECT LastVersionId FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId AND PortalId=@PortalId)
DECLARE @Path VARCHAR(4000)
SELECT @Path = (SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@LastVersionId AND PortalId=@PortalId)
UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET Deleted=@Delete
 WHERE LastVersionId=@LastVersionId AND PortalId=@PortalId;
IF @Delete=1
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
  SET Deleted=1
  WHERE Path LIKE @Path + '%' AND PortalId=@PortalId;
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_ToggleDelete') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_ToggleDelete
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_ToggleDelete
 @PortalId INT,
 @LastVersionId INT,
 @Delete BIT
AS
UPDATE {databaseOwner}{objectQualifier}DMX_Entries
 SET Deleted=@Delete
 WHERE LastVersionId=@LastVersionId
 AND PortalId=@PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UnifyPermissions') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UnifyPermissions
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UnifyPermissions
 @PortalId Int,
 @EntryId Int,
 @UserId INT
AS
DECLARE @BasePath VARCHAR(2000)
SELECT @BasePath = (SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId)
CREATE TABLE #ToChange 
 (EntryId INT, [Path] VARCHAR(2000), EntryType nvarchar(150));
INSERT INTO #ToChange
 (EntryId, [Path], EntryType)
SELECT e.EntryId, e.[Path], e.EntryType FROM {databaseOwner}{objectQualifier}DMX_Entries e INNER JOIN
 (SELECT DISTINCT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep 
   INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId
   LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId AND r.UserId=@UserId
   WHERE pt.PermissionKey='EDIT' AND (ep.UserId=@UserId OR NOT r.UserId IS NULL OR ep.RoleId=-1)) perm ON perm.EntryId=e.EntryId
 WHERE PortalId=@PortalId AND [Path] LIKE @BasePath+'_%';
CREATE TABLE #NewPerms (
 [AllowAccess] [bit] NULL,
 [EntryId] [int] NOT NULL,
 [Expires] [datetime] NULL,
 [PermissionId] [int] NOT NULL,
 [PortalId] [int] NOT NULL,
 [RoleId] [int] NOT NULL,
 [UserId] [int] NOT NULL,
 [EntryType] nvarchar(150))
INSERT INTO #NewPerms
 (AllowAccess, EntryId, Expires, PermissionId, PortalId, RoleId, UserId, EntryType)
SELECT ep.AllowAccess, ep.EntryId, ep.Expires, ep.PermissionId, ep.PortalId, ep.RoleId, ep.UserId, pet.EntryType
 FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep
 INNER JOIN {databaseOwner}{objectQualifier}DMX_PermissionEntryTypes pet ON pet.PermissionId=ep.PermissionId
 WHERE ep.EntryId=@EntryId
DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
 WHERE EntryId IN (SELECT EntryId FROM #ToChange);
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
 (AllowAccess, EntryId, Expires, PermissionId, PortalId, RoleId, UserId)
 SELECT ep.AllowAccess, tc.EntryId, ep.Expires, ep.PermissionId, ep.PortalId, ep.RoleId, ep.UserId
  FROM #NewPerms ep
  INNER JOIN #ToChange tc ON tc.EntryType LIKE ep.EntryType+'%';
DROP TABLE #NewPerms;
DROP TABLE #ToChange;
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UnifyRemovePermission') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UnifyRemovePermission
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UnifyRemovePermission
 @PortalId INT,
 @UnifyingUserId INT,
 @CollectionId INT,
 @PermissionKey NVARCHAR(20)
AS
BEGIN
 DECLARE @Id INT
 SELECT @Id = MIN(e.EntryId)
  FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN
   (SELECT DISTINCT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId
   LEFT JOIN {databaseOwner}{objectQualifier}UserRoles r ON r.RoleId=ep.RoleId WHERE pt.PermissionKey='EDIT' AND (ep.UserId=@UnifyingUserId OR r.UserId=@UnifyingUserId OR ep.RoleId=-1)) perm
  ON perm.EntryId=e.EntryId
  WHERE e.CollectionId=@CollectionId
 DECLARE @PermissionId INT
 SELECT @PermissionId = PermissionId FROM {databaseOwner}{objectQualifier}DMX_Permissions WHERE PermissionKey=@PermissionKey AND PortalId=@PortalId
 DECLARE @AdminRoleId INT
 SELECT @AdminRoleId=AdministratorRoleId FROM {databaseOwner}{objectQualifier}Portals WHERE PortalId=@PortalId
 WHILE @Id IS NOT NULL
 BEGIN
  DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
  FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep
   WHERE ep.EntryId=@Id 
    AND ep.PermissionId=@PermissionId
    AND NOT (ep.RoleId=@AdminRoleId AND (@PermissionKey='VIEW' OR @PermissionKey='EDIT' OR @PermissionKey='ADD'))
    AND (@PermissionKey<>'EDIT' OR (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}UserRoles r WHERE r.UserId=@UnifyingUserId AND r.RoleId=ep.RoleId)=0)
    AND (@PermissionKey<>'EDIT' OR ep.UserId <> @UnifyingUserId)
  EXEC {databaseOwner}{objectQualifier}DMX_UnifyRemovePermission @PortalId, @UnifyingUserId, @Id, @PermissionKey
  SELECT @Id = MIN(e.EntryId)
   FROM {databaseOwner}{objectQualifier}DMX_Entries e
   INNER JOIN
    (SELECT DISTINCT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId
    LEFT JOIN {databaseOwner}{objectQualifier}UserRoles r ON r.RoleId=ep.RoleId WHERE pt.PermissionKey='EDIT' AND (ep.UserId=@UnifyingUserId OR r.UserId=@UnifyingUserId OR ep.RoleId=-1)) perm 
   ON perm.EntryId=e.EntryId
   WHERE e.CollectionId=@CollectionId
   AND e.EntryId>@Id
 END
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UnifySetPermission') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UnifySetPermission
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UnifySetPermission
 @PortalId INT,
 @UnifyingUserId INT,
 @CollectionId INT,
 @PermissionKey NVARCHAR(20),
 @RoleId INT,
 @UserId INT
AS
BEGIN
 DECLARE @Id INT
 SELECT @Id = MIN(e.EntryId)
  FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN
   (SELECT DISTINCT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId
   LEFT JOIN {databaseOwner}{objectQualifier}UserRoles r ON r.RoleId=ep.RoleId WHERE pt.PermissionKey='EDIT' AND (ep.UserId=@UnifyingUserId OR r.UserId=@UnifyingUserId OR ep.RoleId=-1)) perm
  ON perm.EntryId=e.EntryId
  WHERE e.CollectionId=@CollectionId
 DECLARE @PermissionId INT
 SELECT @PermissionId = PermissionId FROM {databaseOwner}{objectQualifier}DMX_Permissions WHERE PermissionKey=@PermissionKey AND PortalId=@PortalId
 DECLARE @EntryType NVARCHAR(100)
 WHILE @Id IS NOT NULL
 BEGIN
  SELECT @EntryType = EntryType FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@Id
  IF (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes WHERE PermissionId=@PermissionId AND @EntryType LIKE EntryType+'%') = 1
  BEGIN
   IF NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions 
    WHERE [EntryId]=@Id AND [PermissionId]=@PermissionId AND [RoleId]=@RoleId AND [UserId]=@UserId AND [AllowAccess]=1 AND [PortalId]=@PortalId)
   INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions ([EntryId], [PermissionId], [RoleId], [UserId], [AllowAccess], [Expires], [PortalId])
    VALUES (@Id, @PermissionId, @RoleId, @UserId, 1, NULL, @PortalId)
  END
  EXEC {databaseOwner}{objectQualifier}DMX_UnifySetPermission @PortalId, @UnifyingUserId, @Id, @PermissionKey, @RoleId, @UserId
  SELECT @Id = MIN(e.EntryId)
   FROM {databaseOwner}{objectQualifier}DMX_Entries e
   INNER JOIN
    (SELECT DISTINCT ep.EntryId FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions pt ON pt.PermissionId=ep.PermissionId
    LEFT JOIN {databaseOwner}{objectQualifier}UserRoles r ON r.RoleId=ep.RoleId WHERE pt.PermissionKey='EDIT' AND (ep.UserId=@UnifyingUserId OR r.UserId=@UnifyingUserId OR ep.RoleId=-1)) perm 
   ON perm.EntryId=e.EntryId
   WHERE e.CollectionId=@CollectionId
   AND e.EntryId>@Id
 END
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UnLockEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UnLockEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UpdateAddon') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateAddon
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateAddon
 @AddonKey NVARCHAR (50), 
 @Availability NVARCHAR (255), 
 @Controller NVARCHAR (255), 
 @Description NVARCHAR (255), 
 @Features INT, 
 @Installed DATETIME, 
 @Name NVARCHAR (50)
AS
UPDATE {databaseOwner}{objectQualifier}DMX_Addons SET
 [Availability] = @Availability,
 [Controller] = @Controller,
 [Description] = @Description,
 [Features] = @Features,
 [Installed] = @Installed,
 [Name] = @Name
WHERE
 [AddonKey] = @AddonKey
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UpdateAttribute') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateAttribute
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateAttribute
 @PortalId INT, 
 @Addon NVARCHAR (50), 
 @AttributeId INT, 
 @AttributeName NVARCHAR (200), 
 @CollectionId INT, 
 @ControlToLoad NVARCHAR (255), 
 @IsPrivate BIT, 
 @Key NVARCHAR (50), 
 @Required BIT, 
 @ResourceFile NVARCHAR (255), 
 @ShowInUI BIT, 
 @Values NVARCHAR (2000), 
 @ValueType NVARCHAR (30), 
 @ViewOrder INT
AS
UPDATE {databaseOwner}{objectQualifier}DMX_Attributes SET
 [PortalId] = @PortalId,
 [Addon] = @Addon,
 [AttributeName] = @AttributeName,
 [CollectionId] = @CollectionId,
 [ControlToLoad] = @ControlToLoad,
 [IsPrivate] = @IsPrivate,
 [Key] = @Key,
 [Required] = @Required,
 [ResourceFile] = @ResourceFile,
 [ShowInUI] = @ShowInUI,
 [Values] = @Values,
 [ValueType] = @ValueType,
 [ViewOrder] = @ViewOrder
WHERE
 [AttributeId] = @AttributeId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UpdateCategory') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateCategory
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateCategory
 @PortalId INT, 
 @CategoryId INT, 
 @CategoryName NVARCHAR (200), 
 @ParentId INT, 
 @ViewOrder INT
AS
UPDATE {databaseOwner}{objectQualifier}DMX_Categories SET
 [PortalId] = @PortalId,
 [CategoryName] = @CategoryName,
 [ParentId] = @ParentId,
 [ViewOrder] = @ViewOrder
WHERE
 [CategoryId] = @CategoryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UpdateEntry') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntry
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntry
 @PortalId INT, 
 @Author NVARCHAR (500), 
 @CollectionId INT, 
 @Created DATETIME, 
 @Deleted BIT, 
 @Entry NVARCHAR (2000), 
 @EntryId INT, 
 @EntryType NVARCHAR (150), 
 @FileSize BIGINT, 
 @Hidden BIT, 
 @InheritAttributes BIT, 
 @IsApproved BIT, 
 @Keywords NVARCHAR (255), 
 @LastVersionId INT, 
 @LockedBy INT, 
 @LockedUntil DATETIME, 
 @LockKey UNIQUEIDENTIFIER, 
 @MD5Hash NVARCHAR (50), 
 @OriginalFileName NVARCHAR (255), 
 @Owner INT, 
 @PermissionsOnlyByAdmin BIT, 
 @Remarks NVARCHAR (MAX), 
 @State NVARCHAR (50), 
 @StorageProviderID INT, 
 @Thumbnail VARCHAR (200), 
 @Title NVARCHAR (1000), 
 @Version INT, 
 @VersionsToKeep INT, 
 @WorkflowId NVARCHAR (50)
AS
UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET
 [PortalId] = @PortalId,
 [Author] = @Author,
 [CollectionId] = @CollectionId,
 [Created] = @Created,
 [Deleted] = @Deleted,
 [Entry] = @Entry,
 [EntryType] = @EntryType,
 [FileSize] = @FileSize,
 [Hidden] = @Hidden,
 [InheritAttributes] = @InheritAttributes,
 [IsApproved] = @IsApproved,
 [Keywords] = @Keywords,
 [LastVersionId] = @LastVersionId,
 [LockedBy] = @LockedBy,
 [LockedUntil] = @LockedUntil,
 [LockKey] = @LockKey,
 [MD5Hash] = @MD5Hash,
 [OriginalFileName] = @OriginalFileName,
 [Owner] = @Owner,
 [PermissionsOnlyByAdmin] = @PermissionsOnlyByAdmin,
 [Remarks] = @Remarks,
 [State] = @State,
 [StorageProviderID] = @StorageProviderID,
 [Thumbnail] = @Thumbnail,
 [Title] = @Title,
 [Version] = @Version,
 [VersionsToKeep] = @VersionsToKeep,
 [WorkflowId] = @WorkflowId
WHERE
 [EntryId] = @EntryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UpdateEntryAttribute') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntryAttribute
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntryAttribute
 @AttributeId INT, 
 @EntryId INT, 
 @Value NVARCHAR (2000)
AS
UPDATE {databaseOwner}{objectQualifier}DMX_EntryAttributes SET
 [Value] = @Value
WHERE
 [AttributeId] = @AttributeId
 AND [EntryId] = @EntryId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UpdateExtension') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateExtension
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateExtension
 @PortalId INT, 
 @AccessRights NVARCHAR (255), 
 @Addon NVARCHAR (50), 
 @ControlToLoad NVARCHAR (255), 
 @Custom NVARCHAR (400), 
 @DownloadUrl NVARCHAR (255), 
 @EditControl NVARCHAR (255), 
 @EntryTypes NVARCHAR (255), 
 @ExtensionKey NVARCHAR (50), 
 @Icon16 NVARCHAR (100), 
 @Icon32 NVARCHAR (100), 
 @IsPrivate BIT, 
 @MimeType VARCHAR (200), 
 @NodePattern NVARCHAR (50), 
 @ResourceFile NVARCHAR (255), 
 @SettingsControl NVARCHAR (250), 
 @ViewByDefault BIT, 
 @ViewControl NVARCHAR (255)
AS
UPDATE {databaseOwner}{objectQualifier}DMX_Extensions SET
 [AccessRights] = @AccessRights,
 [Addon] = @Addon,
 [ControlToLoad] = @ControlToLoad,
 [Custom] = @Custom,
 [DownloadUrl] = @DownloadUrl,
 [EditControl] = @EditControl,
 [EntryTypes] = @EntryTypes,
 [Icon16] = @Icon16,
 [Icon32] = @Icon32,
 [IsPrivate] = @IsPrivate,
 [MimeType] = @MimeType,
 [NodePattern] = @NodePattern,
 [ResourceFile] = @ResourceFile,
 [SettingsControl] = @SettingsControl,
 [ViewByDefault] = @ViewByDefault,
 [ViewControl] = @ViewControl
WHERE
 [ExtensionKey] = @ExtensionKey
 AND [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UpdateFileSize') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateFileSize
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateFileSize
 @PortalId INT,
 @EntryId INT,
 @FileSize BIGINT
AS
UPDATE {databaseOwner}{objectQualifier}DMX_Entries
SET [FileSize]=@FileSize
WHERE
 [EntryId]=@EntryId
 AND [PortalId]=@PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UpdateHostSetting') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateHostSetting
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateHostSetting
 @SettingName nvarchar(50),
 @SettingValue nvarchar(256)
AS
UPDATE {databaseOwner}{objectQualifier}HostSettings
SET SettingValue = @SettingValue
WHERE SettingName = @SettingName
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UpdateLog') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateLog
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateLog
 @PortalId INT, 
 @Action NVARCHAR (20), 
 @Datime DATETIME, 
 @EntryId INT, 
 @LogId INT, 
 @UserId INT
AS
UPDATE {databaseOwner}{objectQualifier}DMX_Log SET
 [PortalId] = @PortalId,
 [Action] = @Action,
 [Datime] = @Datime,
 [EntryId] = @EntryId,
 [UserId] = @UserId
WHERE
 [LogId] = @LogId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UpdateLogType') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateLogType
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateLogType
 @PortalId INT, 
 @AttachFile BIT, 
 @CustomSend BIT, 
 @LogTypeKey NVARCHAR (20), 
 @NotifyApprovers NVARCHAR (100), 
 @NotifyAudit BIT, 
 @NotifyEditors NVARCHAR (100), 
 @NotifyOwner NVARCHAR (100), 
 @NotifySelf NVARCHAR (100), 
 @NotifySubscribers NVARCHAR (100), 
 @ResourceFile NVARCHAR (150)
AS
UPDATE {databaseOwner}{objectQualifier}DMX_LogTypes SET
 [AttachFile] = @AttachFile,
 [CustomSend] = @CustomSend,
 [NotifyApprovers] = @NotifyApprovers,
 [NotifyAudit] = @NotifyAudit,
 [NotifyEditors] = @NotifyEditors,
 [NotifyOwner] = @NotifyOwner,
 [NotifySelf] = @NotifySelf,
 [NotifySubscribers] = @NotifySubscribers,
 [ResourceFile] = @ResourceFile
WHERE
 [LogTypeKey] = @LogTypeKey
 AND [PortalId] = @PortalId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UpdateNotification') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateNotification
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateNotification
 @PortalId INT, 
 @LogId INT, 
 @Sent DATETIME, 
 @Template NVARCHAR (100), 
 @UserId INT
AS
UPDATE {databaseOwner}{objectQualifier}DMX_Notifications SET
 [PortalId] = @PortalId,
 [Sent] = @Sent,
 [Template] = @Template
WHERE
 [LogId] = @LogId
 AND [UserId] = @UserId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UpdatePaths') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdatePaths
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UpdatePermission') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdatePermission
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdatePermission
 @PortalId INT, 
 @Addon NVARCHAR (50), 
 @PermissionId INT, 
 @PermissionKey NVARCHAR (20), 
 @ResourceFile NVARCHAR (255)
AS
UPDATE {databaseOwner}{objectQualifier}DMX_Permissions SET
 [PortalId] = @PortalId,
 [Addon] = @Addon,
 [PermissionKey] = @PermissionKey,
 [ResourceFile] = @ResourceFile
WHERE
 [PermissionId] = @PermissionId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UpdateProvider') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateProvider
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateProvider
 @Provider NVARCHAR (255), 
 @ProviderID INT, 
 @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 sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UpdateSubscription') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateSubscription
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateSubscription
 @EntryId INT, 
 @LastAccess DATETIME, 
 @UserId INT
AS
UPDATE {databaseOwner}{objectQualifier}DMX_Subscriptions SET
 [LastAccess] = @LastAccess
WHERE
 [EntryId] = @EntryId
 AND [UserId] = @UserId
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}DMX_UserAccess') AND type in (N'P', N'PC'))
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UserAccess
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UserAccess
 @UserId INT,
 @EntryId INT
AS
UPDATE {databaseOwner}{objectQualifier}DMX_Subscriptions 
SET LastAccess=GETDATE() 
WHERE (EntryId=@EntryId)
 AND (UserID=@UserId)
GO

/*
DATA
*/
INSERT INTO {databaseOwner}[{objectQualifier}DMX_Addons] ([AddonKey], [Availability], [Description], [Installed], [Name])
VALUES ('Core', 'All', 'Document Exchange Core', GETDATE(), 'Core')
GO

BEGIN
 DECLARE @II INT
 SELECT @II=10000
 SET NOCOUNT ON
 WHILE (@II>0)
  BEGIN
   INSERT INTO {databaseOwner}{objectQualifier}DMX_Numbers (Number) SELECT @II
   SELECT @II=@II-1
  END
END
GO

INSERT INTO {databaseOwner}[{objectQualifier}Schedule] ([TypeFullName], [TimeLapse], [TimeLapseMeasurement], [RetryTimeLapse], [RetryTimeLapseMeasurement], [RetainHistoryNum], [AttachToEvent], [CatchUpEnabled], [Enabled], [ObjectDependencies], [Servers], [FriendlyName])
VALUES ('Bring2mind.DNN.Modules.DMX.Framework.Maintenance, BRING2MIND.DNN.MODULES.DMX.CORE', 12, 'h', 2, 'h', 10, '', 0, 1, '', NULL, 'DMX Maintenance Task')
GO

INSERT INTO {databaseOwner}[{objectQualifier}Schedule] ([TypeFullName], [TimeLapse], [TimeLapseMeasurement], [RetryTimeLapse], [RetryTimeLapseMeasurement], [RetainHistoryNum], [AttachToEvent], [CatchUpEnabled], [Enabled], [ObjectDependencies], [Servers], [FriendlyName])
VALUES ('Bring2mind.DNN.Modules.DMX.Services.Schedule.ScheduledTask, BRING2MIND.DNN.MODULES.DMX.CORE', 5, 'm', 2, 'm', 10, '', 0, 1, '', NULL, 'DMX Notification Task')
GO

INSERT INTO {databaseOwner}[{objectQualifier}Schedule] ([TypeFullName], [TimeLapse], [TimeLapseMeasurement], [RetryTimeLapse], [RetryTimeLapseMeasurement], [RetainHistoryNum], [AttachToEvent], [CatchUpEnabled], [Enabled], [ObjectDependencies], [Servers], [FriendlyName])
VALUES ('Bring2mind.DNN.Modules.DMX.Services.SyncFolders.SyncFolderTask, BRING2MIND.DNN.MODULES.DMX.CORE', 2, 'm', 10, 'm', 20, '', 0, 1, '', NULL, 'DMX SyncFolder Task')
GO

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 (7, 'Bring2mind.DNN.Modules.DMX.Services.Upload.AspnetUploadProvider.AspnetUploadProvider, BRING2MIND.DNN.MODULES.DMX','AspnetUploadProvider',3)
INSERT INTO {databaseOwner}{objectQualifier}DMX_Providers ([ProviderID], [Provider],	[ProviderName],	[ProviderType])
VALUES (8, 'Bring2mind.DNN.Modules.DMX.Services.Upload.CAUploadProvider.CAUploadProvider, BRING2MIND.DNN.MODULES.DMX','CAUploadProvider',3)
INSERT INTO {databaseOwner}{objectQualifier}DMX_Providers ([ProviderID], [Provider],	[ProviderName],	[ProviderType])
VALUES (9, 'Bring2mind.DNN.Modules.DMX.Services.Upload.RadUploadProvider.RadUploadProvider, BRING2MIND.DNN.MODULES.DMX','RadUploadProvider',3)
INSERT INTO {databaseOwner}{objectQualifier}DMX_Providers ([ProviderID], [Provider],	[ProviderName],	[ProviderType])
VALUES (10, 'Bring2mind.DNN.Modules.DMX.Services.Upload.SLUploadProvider.SLUploadProvider, BRING2MIND.DNN.MODULES.DMX','SilverlightUploadProvider',3)
SET IDENTITY_INSERT {databaseOwner}{objectQualifier}DMX_Providers OFF;
GO

-- add the global permissions and extensions
INSERT INTO {databaseOwner}{objectQualifier}DMX_Permissions (Addon,PermissionKey,PortalId,ResourceFile)
VALUES (N'Core', N'DOCADMIN', -1, N'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources')
GO
INSERT INTO {databaseOwner}{objectQualifier}DMX_Permissions (Addon,PermissionKey,PortalId,ResourceFile)
VALUES (N'Core', N'WEBDAV', -1, N'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources')
GO
INSERT INTO {databaseOwner}{objectQualifier}DMX_Permissions (Addon,PermissionKey,PortalId,ResourceFile)
VALUES (N'Core', N'MAILING', -1, N'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources')
GO

