SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_EntryPermissions]'
GO
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
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_EntryPermissions] on {databaseOwner}[{objectQualifier}DMX_EntryPermissions]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_EntryPermissions] ADD CONSTRAINT [PK_{objectQualifier}DMX_EntryPermissions] PRIMARY KEY CLUSTERED  ([EntryId], [PermissionId], [RoleId], [UserId])
GO
PRINT N'Creating index [{objectQualifier}IX_DMX_EntryPermissions_PermissionId] on {databaseOwner}[{objectQualifier}DMX_EntryPermissions]'
GO
CREATE NONCLUSTERED INDEX [{objectQualifier}IX_DMX_EntryPermissions_PermissionId] ON {databaseOwner}[{objectQualifier}DMX_EntryPermissions] ([PermissionId])
GO
PRINT N'Creating index [{objectQualifier}IX_DMX_EntryPermissions_RoleId] on {databaseOwner}[{objectQualifier}DMX_EntryPermissions]'
GO
CREATE NONCLUSTERED INDEX [{objectQualifier}IX_DMX_EntryPermissions_RoleId] ON {databaseOwner}[{objectQualifier}DMX_EntryPermissions] ([RoleId])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEventLog]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEventLog
AS

SELECT *
FROM {databaseOwner}{objectQualifier}EventLog
WHERE LogTypeKey LIKE '%_EXCEPTION'
ORDER BY LogCreateDate

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_Log]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMX_Log]
(
[Action] [nvarchar] (20) NOT NULL,
[Datime] [datetime] NOT NULL,
[EntId] [int] NOT NULL,
[id] [int] NOT NULL IDENTITY(1, 1),
[UserId] [int] NOT NULL
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_Log] on {databaseOwner}[{objectQualifier}DMX_Log]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Log] ADD CONSTRAINT [PK_{objectQualifier}DMX_Log] PRIMARY KEY CLUSTERED  ([id])
GO
PRINT N'Creating index [{objectQualifier}IX_DMX_Log_EntId] on {databaseOwner}[{objectQualifier}DMX_Log]'
GO
CREATE NONCLUSTERED INDEX [{objectQualifier}IX_DMX_Log_EntId] ON {databaseOwner}[{objectQualifier}DMX_Log] ([EntId])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_MoveLog]'
GO


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

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

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_LongTexts]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMX_LongTexts]
(
[Locale] [nvarchar] (6) NOT NULL,
[ObjectId] [int] NOT NULL,
[SubTypeKey] [nvarchar] (20) NOT NULL,
[Text] [nvarchar] (max) NOT NULL,
[TypeKey] [nvarchar] (3) NOT NULL
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_LongTexts] on {databaseOwner}[{objectQualifier}DMX_LongTexts]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_LongTexts] ADD CONSTRAINT [PK_{objectQualifier}DMX_LongTexts] PRIMARY KEY CLUSTERED  ([Locale], [ObjectId], [SubTypeKey], [TypeKey])
GO
PRINT N'Creating index [{objectQualifier}IX_DMX_LongTexts_ObjectId] on {databaseOwner}[{objectQualifier}DMX_LongTexts]'
GO
CREATE NONCLUSTERED INDEX [{objectQualifier}IX_DMX_LongTexts_ObjectId] ON {databaseOwner}[{objectQualifier}DMX_LongTexts] ([ObjectId])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AttributeEntrytypes]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMX_AttributeEntrytypes]
(
[AttributeId] [int] NOT NULL,
[EntryType] [nvarchar] (50) NOT NULL
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_AttributeEntrytypes] on {databaseOwner}[{objectQualifier}DMX_AttributeEntrytypes]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_AttributeEntrytypes] ADD CONSTRAINT [PK_{objectQualifier}DMX_AttributeEntrytypes] PRIMARY KEY CLUSTERED  ([AttributeId], [EntryType])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_ShortTexts]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMX_ShortTexts]
(
[Locale] [nvarchar] (6) NOT NULL,
[ObjectId] [int] NOT NULL,
[SubTypeKey] [nvarchar] (20) NOT NULL,
[Text] [nvarchar] (1000) NULL,
[TypeKey] [nvarchar] (3) NOT NULL
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_ShortTexts] on {databaseOwner}[{objectQualifier}DMX_ShortTexts]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_ShortTexts] ADD CONSTRAINT [PK_{objectQualifier}DMX_ShortTexts] PRIMARY KEY CLUSTERED  ([Locale], [ObjectId], [SubTypeKey], [TypeKey])
GO
PRINT N'Creating index [{objectQualifier}IX_DMX_ShortTexts_ObjectId] on {databaseOwner}[{objectQualifier}DMX_ShortTexts]'
GO
CREATE NONCLUSTERED INDEX [{objectQualifier}IX_DMX_ShortTexts_ObjectId] ON {databaseOwner}[{objectQualifier}DMX_ShortTexts] ([ObjectId])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_Permissions]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMX_Permissions]
(
[Addon] [nvarchar] (50) NULL,
[PermissionId] [int] NOT NULL IDENTITY(1, 1),
[PermissionKey] [nvarchar] (20) NOT NULL,
[PortalId] [int] NULL,
[ResourceFile] [nvarchar] (255) NULL
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_Permissions] on {databaseOwner}[{objectQualifier}DMX_Permissions]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Permissions] ADD CONSTRAINT [PK_{objectQualifier}DMX_Permissions] PRIMARY KEY CLUSTERED  ([PermissionId])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_PermissionEntrytypes]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMX_PermissionEntrytypes]
(
[EntryType] [nvarchar] (100) NOT NULL,
[PermissionId] [int] NOT NULL
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_PermissionEntrytypes] on {databaseOwner}[{objectQualifier}DMX_PermissionEntrytypes]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_PermissionEntrytypes] ADD CONSTRAINT [PK_{objectQualifier}DMX_PermissionEntrytypes] PRIMARY KEY CLUSTERED  ([EntryType], [PermissionId])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_Providers]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMX_Providers]
(
[Provider] [nvarchar] (255) NOT NULL,
[ProviderID] [int] NOT NULL IDENTITY(1, 1),
[ProviderName] [nvarchar] (255) NOT NULL,
[ProviderType] [int] NOT NULL
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_Providers] on {databaseOwner}[{objectQualifier}DMX_Providers]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Providers] ADD CONSTRAINT [PK_{objectQualifier}DMX_Providers] PRIMARY KEY CLUSTERED  ([ProviderID])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteProvider]'
GO


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

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

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_LogTypes]'
GO
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
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_LogTypes] on {databaseOwner}[{objectQualifier}DMX_LogTypes]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_LogTypes] ADD CONSTRAINT [PK_{objectQualifier}DMX_LogTypes] PRIMARY KEY CLUSTERED  ([LogTypeKey], [PortalId])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddLogType]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddLogType
	@PortalId Int,	
	@LogTypeKey NVarChar (20),
	@AttachFile Bit,
	@CustomSend Bit,
	@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],
	[LogTypeKey],
	AttachFile,
	CustomSend,
	NotifyApprovers,
	NotifyAudit,
	NotifyEditors,
	NotifyOwner,
	NotifySelf,
	NotifySubscribers,
	ResourceFile
) VALUES (
	@PortalId,
	@LogTypeKey,
	@AttachFile,
	@CustomSend,
	@NotifyApprovers,
	@NotifyAudit,
	@NotifyEditors,
	@NotifyOwner,
	@NotifySelf,
	@NotifySubscribers,
	@ResourceFile
)



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_Subscriptions]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMX_Subscriptions]
(
[EntryId] [int] NOT NULL,
[LastAccess] [datetime] NULL,
[UserId] [int] NOT NULL
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_Subscriptions] on {databaseOwner}[{objectQualifier}DMX_Subscriptions]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Subscriptions] ADD CONSTRAINT [PK_{objectQualifier}DMX_Subscriptions] PRIMARY KEY CLUSTERED  ([EntryId], [UserId])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddSubscription]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddSubscription
	@UserId Int,	
	@EntryId Int,
	@LastAccess DateTime
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Subscriptions (
	[UserId],
	[EntryId],
	LastAccess
) VALUES (
	@UserId,
	@EntryId,
	@LastAccess
)



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_EntryCategories]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMX_EntryCategories]
(
[CategoryId] [int] NOT NULL,
[EntryId] [int] NOT NULL
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_EntryCategories] on {databaseOwner}[{objectQualifier}DMX_EntryCategories]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_EntryCategories] ADD CONSTRAINT [PK_{objectQualifier}DMX_EntryCategories] PRIMARY KEY CLUSTERED  ([CategoryId], [EntryId])
GO
PRINT N'Creating index [{objectQualifier}IX_DMX_EntryCategories_EntryId] on {databaseOwner}[{objectQualifier}DMX_EntryCategories]'
GO
CREATE NONCLUSTERED INDEX [{objectQualifier}IX_DMX_EntryCategories_EntryId] ON {databaseOwner}[{objectQualifier}DMX_EntryCategories] ([EntryId])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UserAccess]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_Addons]'
GO
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
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_Addons] on {databaseOwner}[{objectQualifier}DMX_Addons]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Addons] ADD CONSTRAINT [PK_{objectQualifier}DMX_Addons] PRIMARY KEY CLUSTERED  ([AddonKey])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_Extensions]'
GO
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] [nvarchar] (50) 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
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_Extensions] on {databaseOwner}[{objectQualifier}DMX_Extensions]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Extensions] ADD CONSTRAINT [PK_{objectQualifier}DMX_Extensions] PRIMARY KEY CLUSTERED  ([ExtensionKey], [PortalId])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetShortText]'
GO


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

SELECT
 *
FROM
	{databaseOwner}{objectQualifier}DMX_ShortTexts
WHERE
	[TypeKey] = @TypeKey
	AND [SubTypeKey] = @SubTypeKey
	AND [ObjectId] = @ObjectId
	AND [Locale] = @Locale


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteLog]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteLog
	@id Int
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Log
WHERE
	[id] = @id


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UpdatePermission]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdatePermission
	@PermissionId Int,
	@Addon NVarChar (50),
	@PermissionKey NVarChar (20),
	@PortalId Int,
	@ResourceFile NVarChar (255)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Permissions SET
	[Addon] = @Addon,
	[PermissionKey] = @PermissionKey,
	[PortalId] = @PortalId,
	[ResourceFile] = @ResourceFile
WHERE
	[PermissionId] = @PermissionId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetSubscriptionsByUser]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubscriptionsByUser
	@UserId Int
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Subscriptions
WHERE
 UserId = @UserId



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_Settings]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMX_Settings]
(
[PortalId] [int] NOT NULL,
[SettingName] [nvarchar] (50) NOT NULL,
[SettingValue] [nvarchar] (2000) NULL
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_Settings] on {databaseOwner}[{objectQualifier}DMX_Settings]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Settings] ADD CONSTRAINT [PK_{objectQualifier}DMX_Settings] PRIMARY KEY CLUSTERED  ([PortalId], [SettingName])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_SetSetting]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetSettings]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSettings
	@PortalId INT
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Settings
WHERE
	[PortalId] = @PortalId

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetPermission]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermission
	@PermissionId Int,
        @PortalId int
AS

SELECT
 *
FROM
	{databaseOwner}{objectQualifier}DMX_Permissions
WHERE
	[PermissionId] = @PermissionId
	AND [PortalId] = @PortalId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteAttributeEntrytype]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetLogTypes]'
GO


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

SELECT
 lt.*
FROM
 {databaseOwner}{objectQualifier}DMX_LogTypes lt
WHERE
 [PortalId] = @PortalId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetLongTexts]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLongTexts
 @TypeKey NVARCHAR(3),
 @SubTypeKey NVARCHAR(20),
 @ObjectId INT
AS

SELECT * FROM {databaseOwner}{objectQualifier}DMX_LongTexts
 WHERE (TypeKey=@TypeKey)
 AND (SubTypeKey=@SubTypeKey)
 AND (ObjectId=@ObjectId)


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetExtension]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetExtension
	@PortalId Int,
	@ExtensionKey NVarChar (50)
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Extensions
WHERE
	[PortalId] = @PortalId
	AND [ExtensionKey] = @ExtensionKey


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UpdateLogType]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateLogType
	@PortalId Int,
	@LogTypeKey NVarChar (20),
	@AttachFile Bit,
	@CustomSend Bit,
	@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
	[PortalId] = @PortalId
	AND [LogTypeKey] = @LogTypeKey


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UpdateLongText]'
GO


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

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


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_EntryAttributes]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMX_EntryAttributes]
(
[AttributeId] [int] NOT NULL,
[EntryId] [int] NOT NULL,
[Value] [nvarchar] (2000) NOT NULL
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_EntryAttributes] on {databaseOwner}[{objectQualifier}DMX_EntryAttributes]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_EntryAttributes] ADD CONSTRAINT [PK_{objectQualifier}DMX_EntryAttributes] PRIMARY KEY CLUSTERED  ([AttributeId], [EntryId])
GO
PRINT N'Creating index [{objectQualifier}IX_DMX_EntryAttributes_EntryId] on {databaseOwner}[{objectQualifier}DMX_EntryAttributes]'
GO
CREATE NONCLUSTERED INDEX [{objectQualifier}IX_DMX_EntryAttributes_EntryId] ON {databaseOwner}[{objectQualifier}DMX_EntryAttributes] ([EntryId])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteSubscription]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteSubscription
	@UserId Int,
	@EntryId Int
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Subscriptions
WHERE
	[UserId] = @UserId
	AND [EntryId] = @EntryId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_Repository]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMX_Repository]
(
[Blob] [varbinary] (max) NULL,
[BlobId] [int] NOT NULL IDENTITY(1, 1),
[PortalId] [int] NULL
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_Repository] on {databaseOwner}[{objectQualifier}DMX_Repository]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Repository] ADD CONSTRAINT [PK_{objectQualifier}DMX_Repository] PRIMARY KEY CLUSTERED  ([BlobId])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetFromRepository]'
GO


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

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

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UpdateLog]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateLog
	@id Int,
	@Action NVarChar (20),
	@Datime DateTime,
	@EntId Int,
	@UserId Int
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Log SET
	[Action] = @Action,
	[Datime] = @Datime,
	[EntId] = @EntId,
	[UserId] = @UserId
WHERE
	[id] = @id


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_PermissionPermissions]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMX_PermissionPermissions]
(
[PermissionId] [int] NOT NULL,
[RoleId] [int] NOT NULL
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_PermissionPermissions] on {databaseOwner}[{objectQualifier}DMX_PermissionPermissions]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_PermissionPermissions] ADD CONSTRAINT [PK_{objectQualifier}DMX_PermissionPermissions] PRIMARY KEY CLUSTERED  ([PermissionId], [RoleId])
GO
PRINT N'Creating index [{objectQualifier}IX_DMX_PermissionPermissions_RoleId] on {databaseOwner}[{objectQualifier}DMX_PermissionPermissions]'
GO
CREATE NONCLUSTERED INDEX [{objectQualifier}IX_DMX_PermissionPermissions_RoleId] ON {databaseOwner}[{objectQualifier}DMX_PermissionPermissions] ([RoleId])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddPermissionPermission]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddPermissionPermission
	@PermissionId Int,	
	@RoleId Int
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_PermissionPermissions (
	[PermissionId],
	[RoleId]
) VALUES (
	@PermissionId,
	@RoleId
)



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_Split]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UpdateProvider]'
GO


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

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

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_Categories]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMX_Categories]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[ParentId] [int] NOT NULL,
[PortalId] [int] NOT NULL,
[ViewOrder] [int] NULL
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_Categories] on {databaseOwner}[{objectQualifier}DMX_Categories]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Categories] ADD CONSTRAINT [PK_{objectQualifier}DMX_Categories] PRIMARY KEY CLUSTERED  ([id])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteCategory]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteCategory
 @CategoryId INT
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryCategories
WHERE CategoryId=@CategoryId;
DELETE FROM {databaseOwner}{objectQualifier}DMX_ShortTexts
WHERE ObjectId=@CategoryId AND TypeKey='CAT' AND SubTypeKey='NAME';
DELETE FROM {databaseOwner}{objectQualifier}DMX_Categories
WHERE id=@CategoryId;

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_Notifications]'
GO
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
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_Notifications] on {databaseOwner}[{objectQualifier}DMX_Notifications]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Notifications] ADD CONSTRAINT [PK_{objectQualifier}DMX_Notifications] PRIMARY KEY CLUSTERED  ([LogId], [UserId])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetLogType]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogType
	@PortalId Int,
	@LogTypeKey NVarChar (20)
AS

SELECT
 lt.*
FROM
 {databaseOwner}{objectQualifier}DMX_LogTypes lt
WHERE
 [PortalId] = @PortalId
 AND [LogTypeKey] = @LogTypeKey


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetProvidersByType]'
GO


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

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

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddToRepository]'
GO


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

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

select SCOPE_IDENTITY()

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetShortTexts]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetShortTexts
 @TypeKey NVARCHAR(3),
 @SubTypeKey NVARCHAR(20),
 @ObjectId INT
AS

SELECT * FROM {databaseOwner}{objectQualifier}DMX_ShortTexts
 WHERE (TypeKey=@TypeKey)
 AND (SubTypeKey=@SubTypeKey)
 AND (ObjectId=@ObjectId)


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetLastActionEvent]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLastActionEvent
	@EntryId INT,
 @Action VARCHAR(50)
AS

SELECT MAX(Datime) FROM {databaseOwner}{objectQualifier}DMX_Log
WHERE EntId=@EntryId
AND Action=@Action


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddAttributeEntrytype]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteShortText]'
GO


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

DELETE FROM {databaseOwner}{objectQualifier}DMX_ShortTexts
WHERE
	[TypeKey] = @TypeKey
	AND [SubTypeKey] = @SubTypeKey
	AND [ObjectId] = @ObjectId
	AND [Locale] = @Locale


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteLogType]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteLogType
	@PortalId Int,
	@LogTypeKey NVarChar (20)
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_LogTypes
WHERE
	[PortalId] = @PortalId
	AND [LogTypeKey] = @LogTypeKey


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddLog]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddLog
	@Action NVARCHAR (20),
	@Datime DateTime,
	@EntId Int,
	@UserId Int
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Log (
	[Action],
	[Datime],
	[EntId],
	[UserId]
) VALUES (
	@Action,
	@Datime,
	@EntId,
	@UserId
)

select SCOPE_IDENTITY()


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeletePermissionEntrytype]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeletePermissionEntrytype
	@PermissionId Int,
	@EntryType NVarChar (100)
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes
WHERE
	[PermissionId] = @PermissionId
	AND [EntryType] = @EntryType


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetSetting]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntryCategories]'
GO


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

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


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteSetting]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteEntryAttributesByEntry]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryAttributesByEntry
	@EntryId Int
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes
WHERE EntryID=@EntryID



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UpdateAddon]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddNotification]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddNotification
	@UserId Int,	
	@LogId Int,
	@PortalId Int,
	@Sent DateTime,
	@Template NVarChar (100)
AS

IF NOT EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}DMX_Notifications
 WHERE UserId=@UserId AND LogId=@LogId AND PortalId=@PortalId AND Template=@Template)
INSERT INTO {databaseOwner}{objectQualifier}DMX_Notifications (
	[UserId],
	[LogId],
	PortalId,
	Sent,
	Template
) VALUES (
	@UserId,
	@LogId,
	@PortalId,
	@Sent,
	@Template
)


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteSettings]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteSettings
	@PortalId INT
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Settings
WHERE
	[PortalId] = @PortalId

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddAddon]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteNotification]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteNotification
	@UserId Int,
	@LogId Int
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Notifications
WHERE
	[UserId] = @UserId
	AND [LogId] = @LogId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntryCategory]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMXDAV_GetExtension]'
GO


CREATE FUNCTION {databaseOwner}{objectQualifier}DMXDAV_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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UpdateEntryAttribute]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntryAttribute
	@EntryId Int,
	@AttributeId Int,
	@Value NVarChar (2000)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_EntryAttributes SET
	[Value] = @Value
WHERE
	[EntryId] = @EntryId
	AND [AttributeId] = @AttributeId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetSubCategories]'
GO


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

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


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_NumberOfCategories]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_NumberOfCategories
 @PortalId INT
AS

SELECT COUNT(*)
FROM {databaseOwner}{objectQualifier}DMX_Categories c
WHERE c.PortalId=@PortalId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UpdateExtension]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateExtension
	@PortalId INT,
	@ExtensionKey NVARCHAR (50),
	@AccessRights NVARCHAR (255),
	@Addon NVARCHAR (50),
	@ControlToLoad NVARCHAR (255),
	@Custom NVARCHAR (50),
	@DownloadUrl NVARCHAR (255),
	@EditControl NVARCHAR (255),
	@EntryTypes NVARCHAR (255),
	@Icon16 NVARCHAR (100),
	@Icon32 NVARCHAR (100),
	@IsPrivate BIT,
	@MimeType NVARCHAR (50),
	@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
	[PortalId] = @PortalId
	AND [ExtensionKey] = @ExtensionKey

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddPermission]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddPermission
	@Addon NVarChar (50),
	@PermissionKey NVarChar (20),
	@PortalId Int,
	@ResourceFile NVarChar (255)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Permissions (
	[Addon],
	[PermissionKey],
	[PortalId],
	[ResourceFile]
) VALUES (

	@Addon,
	@PermissionKey,
	@PortalId,
	@ResourceFile
)

select SCOPE_IDENTITY()


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_SearchResults]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMX_SearchResults]
(
[SearchId] [nvarchar] (50) NOT NULL,
[EntryId] [int] NOT NULL,
[Rank] [real] NULL
)

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UpdateShortText]'
GO


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

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

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetAttributeEntrytype]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteAddon]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAddon
	@AddonKey NVarChar (50)
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Addons
WHERE
	[AddonKey] = @AddonKey


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetAttributeEntrytypesByAttribute]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributeEntrytypesByAttribute
	@AttributeId Int
AS

SELECT
 *
FROM
	{databaseOwner}{objectQualifier}DMX_AttributeEntrytypes
WHERE
	AttributeId = @AttributeId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_FindAddonByName]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_FindAddonByName
	@Name NVarChar (50)
AS

SELECT
 *
FROM
	{databaseOwner}{objectQualifier}DMX_Addons
WHERE
	[Name] = @Name


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddEntryPermission]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntryPermission
	@EntryId Int,	
	@PermissionId Int,	
	@RoleId Int,	
	@UserId Int,
	@AllowAccess Bit,
	@Expires DateTime,
	@PortalId Int
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions (
	[EntryId],
	[PermissionId],
	[RoleId],
	[UserId],
	AllowAccess,
	Expires,
	PortalId
) VALUES (
	@EntryId,
	@PermissionId,
	@RoleId,
	@UserId,
	@AllowAccess,
	@Expires,
	@PortalId
)



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetAddon]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAddon
	@AddonKey NVarChar (50)
AS

SELECT
 *
FROM
	{databaseOwner}{objectQualifier}DMX_Addons
WHERE
	[AddonKey] = @AddonKey


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetPermissionEntrytype]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionEntrytype
	@PermissionId Int,
	@EntryType NVarChar (100)
AS

SELECT
 *
FROM
	{databaseOwner}{objectQualifier}DMX_PermissionEntrytypes
WHERE
	[PermissionId] = @PermissionId
	AND [EntryType] = @EntryType


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteEntryCategory]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddProvider]'
GO


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

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

select SCOPE_IDENTITY()

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetUnsentNotifications]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUnsentNotifications
 @PortalId int,
 @UserId Int
AS

SELECT
 n.*
FROM
 {databaseOwner}{objectQualifier}DMX_Notifications n
WHERE
 n.[UserId] = @UserId
 AND n.[PortalId] = @PortalId
 AND n.[Sent] IS NULL



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetPermissionPermissionsByRole]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionPermissionsByRole
	@RoleId Int
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_PermissionPermissions
WHERE
 RoleId = @RoleId



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntryCategoriesByEntry]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryCategoriesByEntry
	@EntryId Int
AS

SELECT
	*
FROM
	{databaseOwner}{objectQualifier}DMX_EntryCategories
WHERE
	EntryId = @EntryId



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetExtensionsByPortal]'
GO


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

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Extensions
WHERE
 PortalId = @PortalId



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetPermissionPermission]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionPermission
	@PermissionId Int,
	@RoleId Int
AS

SELECT
 *
FROM
	{databaseOwner}{objectQualifier}DMX_PermissionPermissions
WHERE
	[PermissionId] = @PermissionId
	AND [RoleId] = @RoleId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddEntryAttribute]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntryAttribute
	@EntryId Int,	
	@AttributeId Int,
	@Value NVarChar (2000)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryAttributes (
	[EntryId],
	[AttributeId],
	Value
) VALUES (
	@EntryId,
	@AttributeId,
	@Value
)



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UpdateNotification]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateNotification
	@UserId Int,
	@LogId Int,
	@PortalId Int,
	@Sent DateTime,
	@Template NVarChar (100)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Notifications SET
	[PortalId] = @PortalId,
	[Sent] = @Sent,
	[Template] = @Template
WHERE
	[UserId] = @UserId
	AND [LogId] = @LogId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetLongText]'
GO


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

SELECT
 *
FROM
	{databaseOwner}{objectQualifier}DMX_LongTexts
WHERE
	[TypeKey] = @TypeKey
	AND [SubTypeKey] = @SubTypeKey
	AND [ObjectId] = @ObjectId
	AND [Locale] = @Locale


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetProvider]'
GO


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

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

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeletePermission]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeletePermission
	@PermissionId Int
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Permissions
WHERE
	[PermissionId] = @PermissionId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetExtensionsByAddon]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetExtensionsByAddon
	@Addon NVarChar (50)
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Extensions
WHERE
 Addon = @Addon



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_FindEntry]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_FindEntry
 @PortalId Int,
 @SearchText nvarchar(255),
 @Locale nvarchar(10)
AS
SELECT
 e.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale='')) Title,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale='')) Remarks
FROM
 {databaseOwner}DMX_Entries e
WHERE
 PortalId=@PortalId AND
 (OriginalFilename LIKE '%'+@SearchText+'%' OR
 Keywords LIKE '%'+@SearchText+'%' OR
 EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale=@Locale AND Text LIKE '%'+@SearchText+'%'))


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddEntryCategory]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntryCategory
	@EntryId Int,	
	@CategoryId Int

AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryCategories (
	[EntryId],
	[CategoryId]
) VALUES (
	@EntryId,
	@CategoryId
)



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetCategory]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategory
	@id Int,
	@PortalId Int, 
    @Locale NVarChar(10)
AS

SELECT
	c.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='CAT' AND SubTypeKey='NAME' AND ObjectId=c.id AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='CAT' AND SubTypeKey='NAME' AND ObjectId=c.id AND Locale='')) Name
FROM
	{databaseOwner}{objectQualifier}DMX_Categories c
WHERE
	c.PortalId = @PortalId
	AND c.id = @id



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddCategory]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddCategory

	@ParentId Int,
	@PortalId Int,
	@ViewOrder Int
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Categories (
	[ParentId],
	[PortalId],
	[ViewOrder]
) VALUES (
	@ParentId,
	@PortalId,
	@ViewOrder
)

select SCOPE_IDENTITY()


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteAttributeEntrytypes]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAttributeEntrytypes
	@AttributeId Int
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes
WHERE
	[AttributeId] = @AttributeId

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteFromRepository]'
GO


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

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

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteEntryAttribute]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryAttribute
	@EntryId Int,
	@AttributeId Int
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_EntryAttributes
WHERE
	[EntryId] = @EntryId
	AND [AttributeId] = @AttributeId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteLongText]'
GO


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

DELETE FROM {databaseOwner}{objectQualifier}DMX_LongTexts
WHERE
	[TypeKey] = @TypeKey
	AND [SubTypeKey] = @SubTypeKey
	AND [ObjectId] = @ObjectId
	AND [Locale] = @Locale


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetAddonsWithFeature]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAddonsWithFeature
	@Feature INT
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Addons
WHERE
 ISNULL([Features], 0) & @Feature = @Feature


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMXDAV_Numbers]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMXDAV_Numbers]
(
[Number] [int] NOT NULL
)

GO
PRINT N'Creating primary key [Index_Numbers] on {databaseOwner}[{objectQualifier}DMXDAV_Numbers]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMXDAV_Numbers] ADD CONSTRAINT [Index_Numbers] PRIMARY KEY CLUSTERED  ([Number])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_ListProviders]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_ListProviders
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Providers

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddLongText]'
GO


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

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



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UpdateSubscription]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateSubscription
	@UserId Int,
	@EntryId Int,
	@LastAccess DateTime
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Subscriptions SET
	[LastAccess] = @LastAccess
WHERE
	[UserId] = @UserId
	AND [EntryId] = @EntryId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetSubscription]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSubscription
	@UserId Int,
	@EntryId Int
AS

SELECT
 *
FROM
	{databaseOwner}{objectQualifier}DMX_Subscriptions
WHERE
	[UserId] = @UserId
	AND [EntryId] = @EntryId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_ClearEntryPermissions]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddExtension]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddExtension
 @PortalId INT,	
 @ExtensionKey NVARCHAR (50),
 @AccessRights NVARCHAR (255),
 @Addon NVARCHAR (50),
 @ControlToLoad NVARCHAR (255),
 @Custom NVARCHAR (50),
 @DownloadUrl NVARCHAR (255),
 @EditControl NVARCHAR (255),
 @EntryTypes NVARCHAR (255),
 @Icon16 NVARCHAR (100),
 @Icon32 NVARCHAR (100),
 @IsPrivate BIT,
 @MimeType NVARCHAR (50),
 @NodePattern NVARCHAR (50),
 @ResourceFile NVARCHAR (255),
 @SettingsControl NVARCHAR (250),
 @ViewByDefault BIT,
 @ViewControl NVARCHAR (255)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Extensions (
 [PortalId],
 [ExtensionKey],
 AccessRights,
 Addon,
 ControlToLoad,
 Custom,
 DownloadUrl,
 EditControl,
 EntryTypes,
 Icon16,
 Icon32,
 IsPrivate,
 MimeType,
 NodePattern,
 ResourceFile,
 SettingsControl,
 ViewByDefault,
 ViewControl
) VALUES (
 @PortalId,
 @ExtensionKey,
 @AccessRights,
 @Addon,
 @ControlToLoad,
 @Custom,
 @DownloadUrl,
 @EditControl,
 @EntryTypes,
 @Icon16,
 @Icon32,
 @IsPrivate,
 @MimeType,
 @NodePattern,
 @ResourceFile,
 @SettingsControl,
 @ViewByDefault,
 @ViewControl
)


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeletePermissionPermission]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeletePermissionPermission
	@PermissionId Int,
	@RoleId Int
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_PermissionPermissions
WHERE
	[PermissionId] = @PermissionId
	AND [RoleId] = @RoleId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddPermissionEntrytype]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddPermissionEntrytype
 @PermissionId Int,	
 @EntryType NVarChar (100)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes (
	[PermissionId],
	[EntryType]
) VALUES (
	@PermissionId,
	@EntryType
)



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteEntryPermission]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UpdateCategory]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateCategory
	@id Int,
	@ParentId Int,
	@PortalId Int,
	@ViewOrder Int
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Categories SET
	[ParentId] = @ParentId,
	[PortalId] = @PortalId,
	[ViewOrder] = @ViewOrder
WHERE
	[id] = @id


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetPermissionEntrytypesByPermission]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionEntrytypesByPermission
	@PermissionId Int
AS

SELECT
 * 
FROM
 {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes
WHERE
 PermissionId = @PermissionId



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteExtension]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteExtension
	@PortalId Int,
	@ExtensionKey NVarChar (50)
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Extensions
WHERE
	[PortalId] = @PortalId
	AND [ExtensionKey] = @ExtensionKey


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntryCategoriesByCategory]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryCategoriesByCategory
	@CategoryId Int
AS

SELECT
	*
FROM
	{databaseOwner}{objectQualifier}DMX_EntryCategories
WHERE
	CategoryId = @CategoryId



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddShortText]'
GO


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

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

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetNotification]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetNotification
 @UserId Int,
 @LogId Int,
 @PortalId int
AS

SELECT
	[LogId],
	[PortalId],
	[Sent],
	[Template],
	[UserId]
FROM
	{databaseOwner}{objectQualifier}DMX_Notifications
WHERE
	[UserId] = @UserId
	AND [LogId] = @LogId
	AND [PortalId] = @PortalId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_Entries]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMX_Entries]
(
[Author] [nvarchar] (500) NULL,
[CollectionId] [int] NULL,
[Deleted] [bit] NULL,
[Entry] [nvarchar] (2000) NULL,
[EntryId] [int] NOT NULL IDENTITY(1, 1),
[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 CONSTRAINT [DF__{objectQualifier}D__Inher__0ADD8CFD] DEFAULT ((1)),
[StorageProviderID] [int] NULL
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_Entries] on {databaseOwner}[{objectQualifier}DMX_Entries]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Entries] ADD CONSTRAINT [PK_{objectQualifier}DMX_Entries] PRIMARY KEY CLUSTERED  ([EntryId])
GO
PRINT N'Creating index [IX_{objectQualifier}DMX_Entries_Data1] on {databaseOwner}[{objectQualifier}DMX_Entries]'
GO
CREATE NONCLUSTERED INDEX [IX_{objectQualifier}DMX_Entries_Data1] ON {databaseOwner}[{objectQualifier}DMX_Entries] ([LastVersionId], [EntryId], [CollectionId], [Deleted], [IsApproved], [Owner], [Version], [EntryType]) INCLUDE ([Path], [PermissionsOnlyByAdmin], [MD5Hash], [OriginalFileName], [PortalId], [State], [VersionsToKeep], [WorkflowId], [Created], [Keywords], [LastModified], [LockedBy], [LockedUntil], [LockKey], [Author], [Entry], [FileSize])
GO
PRINT N'Creating index [IX_{objectQualifier}DMX_Entries_CollectionID] on {databaseOwner}[{objectQualifier}DMX_Entries]'
GO
CREATE NONCLUSTERED INDEX [IX_{objectQualifier}DMX_Entries_CollectionID] ON {databaseOwner}[{objectQualifier}DMX_Entries] ([CollectionId])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_Attributes]'
GO
CREATE TABLE {databaseOwner}[{objectQualifier}DMX_Attributes]
(
[Addon] [nvarchar] (50) NULL,
[AttributeId] [int] NOT NULL IDENTITY(1, 1),
[ControlToLoad] [nvarchar] (255) NULL,
[EntryTypes] [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 CONSTRAINT [DF__{objectQualifier}D__Colle__155B1B70] DEFAULT ((-1)),
[ShowInUI] [bit] NULL
)

GO
PRINT N'Creating primary key [PK_{objectQualifier}DMX_Attributes] on {databaseOwner}[{objectQualifier}DMX_Attributes]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Attributes] ADD CONSTRAINT [PK_{objectQualifier}DMX_Attributes] PRIMARY KEY CLUSTERED  ([AttributeId])
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetPortalAttributeListWithEntryData]'
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.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale='')) Name,
 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
  WHERE a.PortalId=@PortalId
  AND (a.IsPrivate=0 OR @IncludePrivate=1)


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetBreadCrumbs]'
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.*, ISNULL(stLoc.[Text], stDef.[Text]) Title, ISNULL(ltLoc.[Text], ltDef.[Text]) Remarks
FROM {databaseOwner}{objectQualifier}DMX_Split(@BCPath, ';')
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=s
 INNER JOIN {databaseOwner}{objectQualifier}DMX_ShortTexts stDef ON e.EntryId=stDef.ObjectId AND stDef.TypeKey='ENT' AND stDef.SubTypeKey='TITLE' AND stDef.Locale=''
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_ShortTexts stLoc ON e.EntryId=stLoc.ObjectId AND stLoc.TypeKey='ENT' AND stLoc.SubTypeKey='TITLE' AND stLoc.Locale=@Locale
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_LongTexts ltDef ON e.EntryId=ltDef.ObjectId AND ltDef.TypeKey='ENT' AND ltDef.SubTypeKey='REMARKS' AND ltDef.Locale=''
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_LongTexts ltLoc ON e.EntryId=ltLoc.ObjectId AND ltLoc.TypeKey='ENT' AND ltLoc.SubTypeKey='REMARKS' AND ltLoc.Locale=@Locale
WHERE NOT s=''


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetAttributesByEntryType]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByEntryType
	@PortalId INT,
 @EntryType NVARCHAR(50),
 @Locale NVARCHAR(10)
AS

SELECT 
 a.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale='')) Name
FROM {databaseOwner}{objectQualifier}DMX_Attributes a
 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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetCategories]'
GO


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

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


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetPortalDetails]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetAttributeListForEntry]'
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.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale='')) Name,
 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 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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_ResetPermissions]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntryAttributesByEntry]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributesByEntry
	@EntryId Int,
 @Locale NVarChar(10)
AS

SELECT
	ea.[EntryId],
	ea.[Value],
	a.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale='')) Name
FROM
	{databaseOwner}{objectQualifier}DMX_EntryAttributes ea
	INNER JOIN {databaseOwner}{objectQualifier}DMX_Attributes a ON ea.AttributeId=a.AttributeId
WHERE
	ea.EntryId = @EntryId

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteEntry]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntry
	@EntryId Int
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_ShortTexts
WHERE	[TypeKey] = 'ENT'
	AND [ObjectId] = @EntryId;
DELETE FROM {databaseOwner}{objectQualifier}DMX_LongTexts
WHERE	[TypeKey] = 'ENT'
	AND [ObjectId] = @EntryId;
DELETE FROM {databaseOwner}{objectQualifier}DMX_Entries
WHERE	[EntryId] = @EntryId;

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetApprovedEntry]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetApprovedEntry
	@LastVersionId Int,
    @PortalId int,
    @Locale NVarChar(10)
AS

SELECT
	e.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale='')) Title,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale='')) Remarks
FROM
	{databaseOwner}{objectQualifier}DMX_Entries e
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetLogsByEntry]'
GO


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

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


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}vw_DMX_ActiveUserRoles]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetSubscriptionsForEntry]'
GO


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

BEGIN
 DECLARE @BasePath VARCHAR(2000)
 SELECT @BasePath = ';'+(SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryID);
 SELECT * FROM {databaseOwner}{objectQualifier}DMX_Subscriptions s
 WHERE @BasePath LIKE '%;'+CAST(s.EntryId AS VARCHAR(10))+';%'
 AND EXISTS (SELECT ep1.EntryId 
  FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep1
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions Permission ON Permission.PermissionId=ep1.PermissionId 
  LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur ON ur.RoleId=ep1.RoleId 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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntryPermissionsForUser]'
GO


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

SELECT p.* FROM {databaseOwner}{objectQualifier}DMX_Permissions p 
 INNER JOIN {databaseOwner}{objectQualifier}DMX_EntryPermissions ep ON p.PermissionId=ep.PermissionId 
 LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur ON ur.RoleId=ep.RoleId 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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetPermissionPermissionsByPermission]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPermissionPermissionsByPermission
	@PermissionId Int
AS

SELECT
 pp.*,
 r.Description AS RolesDescription
FROM
 {databaseOwner}{objectQualifier}Roles r INNER JOIN {databaseOwner}{objectQualifier}DMX_PermissionPermissions pp 
  ON r.RoleID = pp.RoleId
WHERE
 pp.PermissionId = @PermissionId



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetLogsByUser]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByUser
	@UserId Int
AS

SELECT
 l.*,
 lt.*,
 u.*
FROM
 {databaseOwner}{objectQualifier}DMX_Log l
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON l.EntId=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_LogTypes lt ON l.Action=lt.LogTypeKey AND lt.PortalId=e.PortalId
 INNER JOIN {databaseOwner}{objectQualifier}Users u ON u.UserId=l.UserId
WHERE
 l.UserId = @UserId



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntryByTitleInCollection]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryByTitleInCollection
 @PortalId INT,
	@CollectionId INT,
	@Title NVARCHAR(255),
 @Locale NVarChar(10)
AS

SELECT e.* 
FROM {databaseOwner}{objectQualifier}DMX_Entries e
INNER JOIN {databaseOwner}{objectQualifier}DMX_ShortTexts st ON st.TypeKey='ENT' AND st.SubTypeKey='TITLE' AND st.ObjectId=e.EntryId
WHERE e.CollectionId=@CollectionId
AND st.Text=@Title
AND PortalId=@PortalId
AND e.LastVersionId=e.EntryId
AND st.Locale=@Locale


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UpdatePaths]'
GO


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

BEGIN

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

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

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

END

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteAttribute]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAttribute
	@AttributeId Int
AS
BEGIN
 DELETE FROM {objectQualifier}DMX_EntryAttributes WHERE AttributeId=@AttributeId
 DELETE FROM {databaseOwner}{objectQualifier}DMX_Attributes WHERE [AttributeId] = @AttributeId
END


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMXDAV_GetPath]'
GO


CREATE FUNCTION {databaseOwner}{objectQualifier}DMXDAV_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
  SELECT @collection = CollectionId FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId AND PortalId=@PortalId
  DECLARE @name NVARCHAR(2000)
  SELECT @name = ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=@EntryId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=@EntryId AND Locale=''))
  DECLARE @orig NVARCHAR(300)
  SELECT @orig = OriginalFilename FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId AND PortalId=@PortalId
  SELECT @res = {databaseOwner}{objectQualifier}DMXDAV_GetPath(@collection, @PortalId, @Locale)+'/'+@name+{databaseOwner}{objectQualifier}DMXDAV_GetExtension(@orig)
 END
ELSE
 SELECT @res = ''
RETURN @res
END

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetAttributes]'
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.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale='')) Name
 FROM {databaseOwner}{objectQualifier}DMX_Attributes a
  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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_RenameEntry]'
GO


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

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetParentChain]'
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.*,
  ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale='')) Title
  FROM {databaseOwner}{objectQualifier}DMX_Entries e WHERE [EntryId] = @EntryId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntryBranch]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryBranch
	@PortalId INT,
	@EntryId INT,
	@AllVersions BIT,
 @IncludeSelf BIT,
	@Locale NVARCHAR(6)
AS

DECLARE @Path VARCHAR(4000)
SELECT @Path=[Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryID AND PortalId=@PortalID;

SELECT e.*, ISNULL(stLoc.[Text], stDef.[Text]) Title, ISNULL(ltLoc.[Text], ltDef.[Text]) Remarks
FROM {databaseOwner}{objectQualifier}DMX_Entries e
 INNER JOIN {databaseOwner}{objectQualifier}DMX_ShortTexts stDef ON e.EntryId=stDef.ObjectId AND stDef.TypeKey='ENT' AND stDef.SubTypeKey='TITLE' AND stDef.Locale=''
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_ShortTexts stLoc ON e.EntryId=stLoc.ObjectId AND stLoc.TypeKey='ENT' AND stLoc.SubTypeKey='TITLE' AND stLoc.Locale=@Locale
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_LongTexts ltDef ON e.EntryId=ltDef.ObjectId AND ltDef.TypeKey='ENT' AND ltDef.SubTypeKey='REMARKS' AND ltDef.Locale=''
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_LongTexts ltLoc ON e.EntryId=ltLoc.ObjectId AND ltLoc.TypeKey='ENT' AND ltLoc.SubTypeKey='REMARKS' AND ltLoc.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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UnifyRemovePermission]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UnifySetPermission]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_SoftDeleteEntry]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SoftDeleteEntry
 @PortalId Int,
 @EntryId Int,
 @Delete Bit
AS

BEGIN
 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;
END

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetCategoriesByEntry]'
GO


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

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

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetPermissionsByPortal]'
GO


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

SELECT p.*, 
 CAST((SELECT COUNT(PermissionId) FROM {databaseOwner}{objectQualifier}DMX_PermissionPermissions pp 
   LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur ON ur.RoleId=pp.RoleId AND ur.UserId=@UserId
   WHERE pp.PermissionId=p.PermissionId 
    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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetUserPermissions]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetLogByTime]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogByTime
 @PortalId Int,
 @StartTime DATETIME,
 @EndTime DATETIME
AS

SELECT
 l.*,
 lt.*,
 u.*
FROM
 {databaseOwner}{objectQualifier}DMX_Log l
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON l.EntId=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_LogTypes lt ON l.Action=lt.LogTypeKey AND lt.PortalId=e.PortalId
 INNER JOIN {databaseOwner}{objectQualifier}Users u ON u.UserId=l.UserId
WHERE
 e.PortalId = @PortalId
 AND l.Datime >= @StartTime
 AND l.Datime < @EndTime



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UnifyPermissions]'
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)
IF EXISTS (select [name] from tempdb..sysobjects where [name] LIKE '#ToChange%')
 DROP TABLE #ToChange;
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+'_%';
IF EXISTS (select [name] from tempdb..sysobjects where [name] LIKE '#NewPerms%')
 DROP TABLE #NewPerms;
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_ResetFolderSizes]'
GO


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

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


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntryAttributesByKeyAndValue]'
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.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale='')) Name
FROM {databaseOwner}{objectQualifier}DMX_Attributes a
 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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_MoveEntryAttributes]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetAttributesByPortal]'
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByPortal
	@PortalId Int,
 @Locale NVarChar(10)
AS

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

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntry]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntry
	@EntryId Int,
    @PortalId int,
    @Locale NVarChar(10)
AS

SELECT
	e.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale='')) Title,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale='')) Remarks
FROM
	{databaseOwner}{objectQualifier}DMX_Entries e
WHERE
	[EntryId] = @EntryId
	AND [PortalId] = @PortalId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetPortal]'
GO


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

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

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetCategoriesByPortal]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCategoriesByPortal
	@PortalId Int, 
    @Locale NVarChar(10)
AS

SELECT
	c.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='CAT' AND SubTypeKey='NAME' AND ObjectId=c.id AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='CAT' AND SubTypeKey='NAME' AND ObjectId=c.id AND Locale='')) Name
FROM
	{databaseOwner}{objectQualifier}DMX_Categories c
WHERE
	c.PortalId = @PortalId



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetAttributesByAddon]'
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByAddon
	@Addon NVarChar (50),
 @Locale NVarChar(10)
AS

SELECT
	a.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale='')) Name
FROM
 {databaseOwner}{objectQualifier}DMX_Attributes a
WHERE
 a.Addon = @Addon

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UpdateFileSize]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetAttribute]'
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttribute
 @AttributeId Int,
 @PortalId int,
 @Locale NVarChar(10)
AS

SELECT
	a.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale='')) Name
FROM
	{databaseOwner}{objectQualifier}DMX_Attributes a
WHERE
	a.[AttributeId] = @AttributeId
	AND (a.[PortalId] = @PortalId OR @PortalId=-1)

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMXDAV_URLEncode]'
GO


CREATE FUNCTION {databaseOwner}{objectQualifier}DMXDAV_URLEncode(@String VARCHAR(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}DMXDAV_Numbers WHERE Number <= LEN(@String)) CHARACTERARRAY
 RETURN @URLEncodedString
END


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddAttribute]'
GO


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

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

select SCOPE_IDENTITY()

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntryAttribute]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttribute
	@EntryId Int,
	@AttributeId Int,
 @Locale NVarChar(10)
AS

SELECT
	ea.[EntryId],
	ea.[Value],
	a.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale='')) Name
FROM
	{databaseOwner}{objectQualifier}DMX_EntryAttributes ea
	INNER JOIN {databaseOwner}{objectQualifier}DMX_Attributes a ON ea.AttributeId=a.AttributeId
WHERE
	ea.[EntryId] = @EntryId
	AND ea.[AttributeId] = @AttributeId

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntryAttributeListForEntry]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributeListForEntry
	@PortalId INT,
	@EntryId INT,
 @EntryType NVARCHAR(50),
 @Locale NVARCHAR(10)
AS

SELECT 
 ea.[EntryId], 
 ea.[Value], 
 a.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale='')) Name
FROM {databaseOwner}{objectQualifier}DMX_Attributes a
 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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DeleteEntryBranch]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntryBranch
	@PortalId INT,
	@EntryId INT,
 @IncludeSelf BIT
AS

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_ShortTexts
FROM {databaseOwner}{objectQualifier}DMX_ShortTexts st
INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=st.[ObjectId]
WHERE	st.[TypeKey] = 'ENT'	
 AND ((e.[Path] LIKE @Path+'%') OR (@IncludeSelf=1 AND e.LastVersionId=@EntryId))
 AND (@IncludeSelf=1 OR (NOT e.[Path]=@Path));
DELETE FROM {databaseOwner}{objectQualifier}DMX_LongTexts
FROM {databaseOwner}{objectQualifier}DMX_LongTexts lt
INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=lt.[ObjectId]
WHERE	lt.[TypeKey] = 'ENT'	
 AND ((e.[Path] LIKE @Path+'%') OR (@IncludeSelf=1 AND e.LastVersionId=@EntryId))
 AND (@IncludeSelf=1 OR (NOT e.[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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetSpecificEntryPermission]'
GO


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

SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON ep.PermissionId=p.PermissionId
 LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur ON ep.RoleId=ur.RoleId 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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_SetLastModified]'
GO


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

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


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_EntryIsIn]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UpdateHostSetting]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_RemoveDeadPermissions]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_SetLastVersionID]'
GO


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

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


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_MoveAttribute]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntryAttributesByAttribute]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributesByAttribute
	@AttributeId Int,
 @Locale NVarChar(10)
AS

SELECT
	ea.[EntryId],
	ea.[Value],
	a.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale='')) Name
FROM
	{databaseOwner}{objectQualifier}DMX_EntryAttributes ea
	INNER JOIN {databaseOwner}{objectQualifier}DMX_Attributes a ON ea.AttributeId=a.AttributeId
WHERE
	ea.AttributeId = @AttributeId

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_RemoveOldUserNotifications]'
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.Id=n.LogId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=l.EntId
 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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntriesByPortal]'
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 EntryId) AS rownum,
 e.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale='')) Title,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale='')) Remarks
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_LimitSearch]'
GO


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

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


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntriesByFilenameInCollection]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByFilenameInCollection
 @PortalId INT,
 @CollectionId INT,
 @Filename NVARCHAR(255)
AS

SELECT e.*, stDef.[Text] Title, ltDef.[Text] Remarks
FROM {databaseOwner}{objectQualifier}DMX_Entries e
 INNER JOIN {databaseOwner}{objectQualifier}DMX_ShortTexts stDef ON e.EntryId=stDef.ObjectId AND stDef.TypeKey='ENT' AND stDef.SubTypeKey='TITLE' AND stDef.Locale=''
 LEFT JOIN {databaseOwner}{objectQualifier}DMX_LongTexts ltDef ON e.EntryId=ltDef.ObjectId AND ltDef.TypeKey='ENT' AND ltDef.SubTypeKey='REMARKS' AND ltDef.Locale=''
WHERE (e.CollectionId=@CollectionId OR @CollectionId=-1)
AND e.OriginalFilename=@Filename
AND PortalId=@PortalId
AND e.LastVersionId=e.EntryId


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetSubscriptionsByEntry]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetUsersToNotify]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntriesByUser]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntryPermissionsByEntry]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryPermissionsByEntry
	@EntryId Int
AS

SELECT
	ep.*,
	p.[Addon],
	p.[PermissionKey],
	p.[ResourceFile],
	r.[RoleName],
	u.[UserName],
	u.[DisplayName]
FROM
	{databaseOwner}{objectQualifier}DMX_EntryPermissions ep
	INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON ep.PermissionId = p.PermissionId
	LEFT JOIN {databaseOwner}{objectQualifier}Roles r ON ep.RoleId = r.RoleId
	LEFT JOIN {databaseOwner}{objectQualifier}Users u ON ep.UserId = u.UserId
WHERE
	ep.EntryId = @EntryId



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetModuleUsageList]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_MoveEntry]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_MoveEntry
 @PortalId Int,
 @EntryId Int,
 @NewCollectionId INT
AS

 DECLARE @destpath VARCHAR(4000)
 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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_FindAttributeByKey]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_FindAttributeByKey
	@Key NVarChar (50),
 @PortalId INT,
 @Locale NVarChar(10)
AS

SELECT
	a.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale='')) Name
FROM
	{databaseOwner}{objectQualifier}DMX_Attributes a
WHERE
	a.[Key] = @Key
	AND [PortalId] = @PortalId

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntriesByPortalAndProvider]'
GO


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

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

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UpdateAttribute]'
GO


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

UPDATE {databaseOwner}{objectQualifier}DMX_Attributes SET
	[Addon] = @Addon,
	[CollectionId] = @CollectionId,
	[ControlToLoad] = @ControlToLoad,
	[IsPrivate] = @IsPrivate,
	[Key] = @Key,
	[PortalId] = @PortalId,
	[Required] = @Required,
	[ResourceFile] = @ResourceFile,
	[ShowInUI] = @ShowInUI,
	[Values] = @Values,
	[ValueType] = @ValueType,
	[ViewOrder] = @ViewOrder
WHERE
	[AttributeId] = @AttributeId

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntryAttributeByEntryAndKey]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributeByEntryAndKey
	@PortalId INT,
	@EntryId INT,
 @Key NVARCHAR(50),
 @Locale NVARCHAR(10)
AS

SELECT
 ea.[EntryId], 
 ea.[Value],
 a.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ATT' AND SubTypeKey='NAME' AND ObjectId=a.AttributeId AND Locale='')) Name
FROM {databaseOwner}{objectQualifier}DMX_Attributes a
 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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_LockEntry]'
GO


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

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


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_RemoveOldUserSubscriptions]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_RemoveOldUserSubscriptions
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Subscriptions
FROM {databaseOwner}{objectQualifier}DMX_Subscriptions s
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON e.EntryId=s.EntryId
 LEFT JOIN {databaseOwner}{objectQualifier}UserPortals up ON up.UserId=s.UserId AND up.PortalId=e.PortalId
WHERE up.UserPortalId IS NULL

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UnLockEntry]'
GO


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

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


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_IsEntryPermissionByAdmin]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_MoveAllEntries]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddHostSetting]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_UpdateEntry]'
GO


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

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


GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetHostSetting]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetHostSetting
	@SettingName nvarchar(50)
AS

SELECT *
FROM {databaseOwner}{objectQualifier}HostSettings
WHERE SettingName = @SettingName

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddEntry]'
GO


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

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

select SCOPE_IDENTITY()

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_EntryPermissionMaintenance]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_EntryPermissionMaintenance
 @PortalId INT
AS

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))
  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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetLogsByPortal]'
GO


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

SELECT
 l.*,
 lt.*,
 u.*
FROM
 {databaseOwner}{objectQualifier}DMX_Log l
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON l.EntId=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_LogTypes lt ON l.Action=lt.LogTypeKey AND lt.PortalId=e.PortalId
 INNER JOIN {databaseOwner}{objectQualifier}Users u ON u.UserId=l.UserId
WHERE
 e.PortalId = @PortalId



GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DMXInfo]'
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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetLog]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLog
 @id Int
AS

SELECT
 l.*,
 lt.*,
 u.*
FROM
 {databaseOwner}{objectQualifier}DMX_Log l
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON l.EntId=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_LogTypes lt ON l.Action=lt.LogTypeKey AND lt.PortalId=e.PortalId
 LEFT JOIN {databaseOwner}{objectQualifier}Users u ON u.UserId=l.UserId
WHERE
 l.[id] = @id

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_AddFolder]'
GO


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

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

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetPermissionsForEntrytype]'
GO


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

BEGIN
IF @UserID=-4

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

ELSE

SELECT p.*,
 CAST((SELECT COUNT(PermissionId) FROM {databaseOwner}{objectQualifier}DMX_PermissionPermissions pp 
   LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles ur ON ur.RoleId=pp.RoleId AND ur.UserId=@UserId
   WHERE pp.PermissionId=p.PermissionId 
    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
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_GetEntryListAsXml]'
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}DMXDAV_URLEncode({databaseOwner}{objectQualifier}DMXDAV_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

PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_DatabaseUsage]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DatabaseUsage
AS

-- from http://www.keyboardface.com/archives/2007/06/12/mssql-table-size-for-all-tables/
BEGIN
declare @RowCount int, @tablename varchar(100)
declare @Tables table (
PK int IDENTITY(1,1),
tablename varchar(100),
processed bit
)
INSERT into @Tables (tablename)
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME not like 'dt%' order by TABLE_NAME asc

declare @Space table (
name varchar(100), rows nvarchar(100), reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100)
)
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = 1
WHILE (@RowCount <> 0)
BEGIN
insert into @Space exec sp_spaceused @tablename
update @Tables set processed = 1 where tablename = @tablename
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = @@RowCount
END

update @Space set data = replace(data, ' KB', '')
update @Space set data = convert(int, data)/1000
update @Space set data = data + ' MB'
update @Space set reserved = replace(reserved, ' KB', '')
update @Space set reserved = convert(int, reserved)/1000
update @Space set reserved = reserved + ' MB'

select * from @Space order by convert(int, replace(data, ' MB', '')) desc
END

GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMX_SetDeleteRecursive]'
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetDeleteRecursive
 @PortalId Int,
 @EntryId Int,
 @Delete BIT
AS
 DECLARE @Path VARCHAR(2000)
 SET @Path = (SELECT [Path] FROM {databaseOwner}{objectQualifier}DMX_Entries WHERE EntryId=@EntryId AND PortalId=@PortalId)+'%';
 DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
  SET Deleted=@Delete
 WHERE
  PortalId=@PortalId
  AND (LastVersionId=@EntryId
  OR [Path] LIKE @Path);
 ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;


GO
PRINT N'Adding foreign keys to {databaseOwner}[{objectQualifier}DMX_Entries]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Entries] WITH NOCHECK ADD
CONSTRAINT [FK_{objectQualifier}DMX_Entries_{objectQualifier}Portals] FOREIGN KEY ([PortalId]) REFERENCES {databaseOwner}[{objectQualifier}Portals] ([PortalID]) ON DELETE CASCADE NOT FOR REPLICATION
GO
PRINT N'Adding foreign keys to {databaseOwner}[{objectQualifier}DMX_Repository]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Repository] WITH NOCHECK ADD
CONSTRAINT [FK_{objectQualifier}DMX_Repository_{objectQualifier}Portals] FOREIGN KEY ([PortalId]) REFERENCES {databaseOwner}[{objectQualifier}Portals] ([PortalID]) ON DELETE CASCADE NOT FOR REPLICATION
GO
PRINT N'Adding foreign keys to {databaseOwner}[{objectQualifier}DMX_Notifications]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Notifications] WITH NOCHECK ADD
CONSTRAINT [FK_{objectQualifier}DMX_Notifications_{objectQualifier}DMX_Log] FOREIGN KEY ([LogId]) REFERENCES {databaseOwner}[{objectQualifier}DMX_Log] ([id]) ON DELETE CASCADE NOT FOR REPLICATION
GO
PRINT N'Adding foreign keys to {databaseOwner}[{objectQualifier}DMX_EntryCategories]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_EntryCategories] WITH NOCHECK ADD
CONSTRAINT [FK_{objectQualifier}DMX_EntryCategories_{objectQualifier}DMX_Entries] FOREIGN KEY ([EntryId]) REFERENCES {databaseOwner}[{objectQualifier}DMX_Entries] ([EntryId]) ON DELETE CASCADE NOT FOR REPLICATION
GO
PRINT N'Adding foreign keys to {databaseOwner}[{objectQualifier}DMX_Extensions]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Extensions] WITH NOCHECK ADD
CONSTRAINT [FK_{objectQualifier}DMX_Extensions_{objectQualifier}DMX_Addons] FOREIGN KEY ([Addon]) REFERENCES {databaseOwner}[{objectQualifier}DMX_Addons] ([AddonKey]) ON DELETE CASCADE NOT FOR REPLICATION,
CONSTRAINT [FK_{objectQualifier}DMX_Extensions_{objectQualifier}Portals] FOREIGN KEY ([PortalId]) REFERENCES {databaseOwner}[{objectQualifier}Portals] ([PortalID]) ON DELETE CASCADE NOT FOR REPLICATION
GO
PRINT N'Adding foreign keys to {databaseOwner}[{objectQualifier}DMX_PermissionPermissions]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_PermissionPermissions] WITH NOCHECK ADD
CONSTRAINT [FK_{objectQualifier}DMX_PermissionPermissions_{objectQualifier}DMX_Permissions] FOREIGN KEY ([PermissionId]) REFERENCES {databaseOwner}[{objectQualifier}DMX_Permissions] ([PermissionId]) ON DELETE CASCADE NOT FOR REPLICATION
GO
PRINT N'Adding foreign keys to {databaseOwner}[{objectQualifier}DMX_Permissions]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Permissions] WITH NOCHECK ADD
CONSTRAINT [FK_{objectQualifier}DMX_Permissions_{objectQualifier}Portals] FOREIGN KEY ([PortalId]) REFERENCES {databaseOwner}[{objectQualifier}Portals] ([PortalID]) ON DELETE CASCADE NOT FOR REPLICATION
GO
PRINT N'Adding foreign keys to {databaseOwner}[{objectQualifier}DMX_Subscriptions]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Subscriptions] WITH NOCHECK ADD
CONSTRAINT [FK_{objectQualifier}DMX_Subscriptions_{objectQualifier}DMX_Entries] FOREIGN KEY ([EntryId]) REFERENCES {databaseOwner}[{objectQualifier}DMX_Entries] ([EntryId]) ON DELETE CASCADE NOT FOR REPLICATION
GO
PRINT N'Adding foreign keys to {databaseOwner}[{objectQualifier}DMX_PermissionEntrytypes]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_PermissionEntrytypes] WITH NOCHECK ADD
CONSTRAINT [FK_{objectQualifier}DMX_PermissionEntrytypes_{objectQualifier}DMX_Permissions] FOREIGN KEY ([PermissionId]) REFERENCES {databaseOwner}[{objectQualifier}DMX_Permissions] ([PermissionId]) ON DELETE CASCADE NOT FOR REPLICATION
GO
PRINT N'Adding foreign keys to {databaseOwner}[{objectQualifier}DMX_AttributeEntrytypes]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_AttributeEntrytypes] WITH NOCHECK ADD
CONSTRAINT [FK_{objectQualifier}DMX_AttributeEntrytypes_{objectQualifier}DMX_Attributes] FOREIGN KEY ([AttributeId]) REFERENCES {databaseOwner}[{objectQualifier}DMX_Attributes] ([AttributeId]) ON DELETE CASCADE NOT FOR REPLICATION
GO
PRINT N'Adding foreign keys to {databaseOwner}[{objectQualifier}DMX_Attributes]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Attributes] WITH NOCHECK ADD
CONSTRAINT [FK_{objectQualifier}DMX_Attributes_{objectQualifier}DMX_Addons] FOREIGN KEY ([Addon]) REFERENCES {databaseOwner}[{objectQualifier}DMX_Addons] ([AddonKey]) ON DELETE CASCADE NOT FOR REPLICATION,
CONSTRAINT [FK_{objectQualifier}DMX_Attributes_{objectQualifier}Portals] FOREIGN KEY ([PortalId]) REFERENCES {databaseOwner}[{objectQualifier}Portals] ([PortalID]) ON DELETE CASCADE NOT FOR REPLICATION
GO
PRINT N'Adding foreign keys to {databaseOwner}[{objectQualifier}DMX_Log]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Log] WITH NOCHECK ADD
CONSTRAINT [FK_{objectQualifier}DMX_Log_{objectQualifier}DMX_Entries] FOREIGN KEY ([EntId]) REFERENCES {databaseOwner}[{objectQualifier}DMX_Entries] ([EntryId]) ON DELETE CASCADE NOT FOR REPLICATION
GO
PRINT N'Adding foreign keys to {databaseOwner}[{objectQualifier}DMX_Categories]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Categories] WITH NOCHECK ADD
CONSTRAINT [FK_{objectQualifier}DMX_Categories_{objectQualifier}Portals] FOREIGN KEY ([PortalId]) REFERENCES {databaseOwner}[{objectQualifier}Portals] ([PortalID]) ON DELETE CASCADE NOT FOR REPLICATION
GO
PRINT N'Adding foreign keys to {databaseOwner}[{objectQualifier}DMX_EntryPermissions]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_EntryPermissions] WITH NOCHECK ADD
CONSTRAINT [FK_{objectQualifier}DMX_EntryPermissions_{objectQualifier}DMX_Entries] FOREIGN KEY ([EntryId]) REFERENCES {databaseOwner}[{objectQualifier}DMX_Entries] ([EntryId]) ON DELETE CASCADE NOT FOR REPLICATION
GO
PRINT N'Adding foreign keys to {databaseOwner}[{objectQualifier}DMX_EntryAttributes]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_EntryAttributes] WITH NOCHECK ADD
CONSTRAINT [FK_{objectQualifier}DMX_EntryAttributes_{objectQualifier}DMX_Attributes] FOREIGN KEY ([AttributeId]) REFERENCES {databaseOwner}[{objectQualifier}DMX_Attributes] ([AttributeId]) NOT FOR REPLICATION,
CONSTRAINT [FK_{objectQualifier}DMX_EntryAttributes_{objectQualifier}DMX_Entries] FOREIGN KEY ([EntryId]) REFERENCES {databaseOwner}[{objectQualifier}DMX_Entries] ([EntryId]) ON DELETE CASCADE NOT FOR REPLICATION
GO
PRINT N'Creating trigger {databaseOwner}[{objectQualifier}DMX_EntryTriggerInsert] on {databaseOwner}[{objectQualifier}DMX_Entries]'
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);

END

GO
PRINT N'Creating trigger {databaseOwner}[{objectQualifier}DMX_EntryTriggerUpdate] on {databaseOwner}[{objectQualifier}DMX_Entries]'
GO


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

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

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

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

-- 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
PRINT N'Creating trigger {databaseOwner}[{objectQualifier}DMX_EntryTriggerDelete] on {databaseOwner}[{objectQualifier}DMX_Entries]'
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

/*
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}DMXDAV_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]) VALUES ('Bring2mind.DNN.Modules.DMX.Services.Schedule.Maintenance, BRING2MIND.DNN.MODULES.DMX.CORE', 12, 'h', 2, 'h', 10, '', 0, 1, '', NULL)
GO
INSERT INTO {databaseOwner}[{objectQualifier}Schedule] ([TypeFullName], [TimeLapse], [TimeLapseMeasurement], [RetryTimeLapse], [RetryTimeLapseMeasurement], [RetainHistoryNum], [AttachToEvent], [CatchUpEnabled], [Enabled], [ObjectDependencies], [Servers]) VALUES ('Bring2mind.DNN.Modules.DMX.Services.Schedule.ScheduledTask, BRING2MIND.DNN.MODULES.DMX.CORE', 5, 'm', 2, 'm', 10, '', 0, 1, '', NULL)
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 (6, 'Bring2mind.DNN.Modules.DMX.Services.Search.ISRemoteSqlSearchProvider.ISRemoteSqlSearchProvider, BRING2MIND.DNN.MODULES.DMX', 'ISRemoteSqlSearchProvider', 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)
SET IDENTITY_INSERT {databaseOwner}{objectQualifier}DMX_Providers OFF;


