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'Dropping foreign keys from {databaseOwner}[{objectQualifier}DMX_Attributes]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Attributes] DROP
CONSTRAINT [FK_{objectQualifier}DMX_Attributes_{objectQualifier}DMX_Addons],
CONSTRAINT [FK_{objectQualifier}DMX_Attributes_{objectQualifier}Portals]
GO
PRINT N'Dropping foreign keys from {databaseOwner}[{objectQualifier}DMX_EntryAttributes]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_EntryAttributes] DROP
CONSTRAINT [FK_{objectQualifier}DMX_EntryAttributes_{objectQualifier}DMX_Attributes],
CONSTRAINT [FK_{objectQualifier}DMX_EntryAttributes_{objectQualifier}DMX_Entries]
GO
PRINT N'Dropping foreign keys from {databaseOwner}[{objectQualifier}DMX_Log]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Log] DROP
CONSTRAINT [FK_{objectQualifier}DMX_Log_{objectQualifier}DMX_Entries],
CONSTRAINT [FK_{objectQualifier}DMX_Log_{objectQualifier}DMX_LogTypes]
GO
PRINT N'Dropping foreign keys from {databaseOwner}[{objectQualifier}DMX_Subscriptions]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Subscriptions] DROP
CONSTRAINT [FK_{objectQualifier}DMX_Subscriptions_{objectQualifier}DMX_Entries],
CONSTRAINT [FK_{objectQualifier}DMX_Subscriptions_{objectQualifier}Users]
GO
PRINT N'Dropping foreign keys from {databaseOwner}[{objectQualifier}DMX_Extensions]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Extensions] DROP
CONSTRAINT [FK_{objectQualifier}DMX_Extensions_{objectQualifier}Portals],
CONSTRAINT [FK_{objectQualifier}DMX_Extensions_{objectQualifier}DMX_Addons]
GO
PRINT N'Dropping constraints from {databaseOwner}[{objectQualifier}DMX_Attributes]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Attributes] DROP CONSTRAINT [PK_{objectQualifier}DMX_Attributes]
GO
PRINT N'Dropping constraints from {databaseOwner}[{objectQualifier}DMX_EntryAttributes]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_EntryAttributes] DROP CONSTRAINT [PK_{objectQualifier}DMX_EntryAttributes]
GO
PRINT N'Dropping constraints from {databaseOwner}[{objectQualifier}DMX_Extensions]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Extensions] DROP CONSTRAINT [PK_{objectQualifier}DMX_Extensions]
GO
PRINT N'Dropping constraints from {databaseOwner}[{objectQualifier}DMX_Log]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Log] DROP CONSTRAINT [PK_{objectQualifier}DMX_Log]
GO
PRINT N'Dropping constraints from {databaseOwner}[{objectQualifier}DMX_Subscriptions]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Subscriptions] DROP CONSTRAINT [PK_{objectQualifier}DMX_Subscriptions]
GO
PRINT N'Dropping trigger {databaseOwner}[{objectQualifier}DMX_Init] from {databaseOwner}[{objectQualifier}Portals]'
GO
DROP TRIGGER {databaseOwner}[{objectQualifier}DMX_Init]

GO
PRINT N'Dropping trigger {databaseOwner}[{objectQualifier}DMX_FollowUp] from {databaseOwner}[{objectQualifier}Portals]'
GO
DROP TRIGGER {databaseOwner}[{objectQualifier}DMX_FollowUp]

GO
PRINT N'Altering {databaseOwner}[{objectQualifier}DMX_Subscriptions]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Subscriptions] DROP
COLUMN [id]
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Subscriptions] ALTER COLUMN [EntryId] [int] NOT NULL
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Subscriptions] ALTER COLUMN [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_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'Rebuilding {databaseOwner}[{objectQualifier}DMX_EntryAttributes]'
GO
EXEC sp_rename '{databaseOwner}[{objectQualifier}DMX_EntryAttributes].[Attribute]', 'AttributeId', 'COLUMN';
GO
ALTER TABLE {databaseOwner}{objectQualifier}DMX_EntryAttributes 
ALTER COLUMN [EntryId] INT NOT NULL
GO
ALTER TABLE {databaseOwner}{objectQualifier}DMX_EntryAttributes 
ALTER COLUMN [AttributeId] INT NOT NULL
GO
ALTER TABLE {databaseOwner}{objectQualifier}DMX_EntryAttributes 
DROP COLUMN [id]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'PK_{objectQualifier}DMX_EntryAttributes') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_EntryAttributes ADD CONSTRAINT
	PK_{objectQualifier}DMX_EntryAttributes PRIMARY KEY CLUSTERED 
	(
	[AttributeId], [EntryId]
	) ON [PRIMARY]
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 {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 {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 {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] (255) NOT 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'Altering {databaseOwner}[{objectQualifier}DMX_Entries]'
GO
EXEC sp_rename '{databaseOwner}[{objectQualifier}DMX_Entries].[DateSubmitted]', 'Created', 'COLUMN';
GO
EXEC sp_rename '{databaseOwner}[{objectQualifier}DMX_Entries].[UserId]', 'Owner', 'COLUMN';
GO
EXEC sp_rename '{databaseOwner}[{objectQualifier}DMX_Entries].[IsPrivate]', 'IsPrivate_old', 'COLUMN';
GO
EXEC sp_rename '{databaseOwner}[{objectQualifier}DMX_Entries].[Remarks]', 'Remarks_old', 'COLUMN';
GO

ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Entries] ADD
[LockedUntil] [datetime] NULL,
[LockKey] [uniqueidentifier] NULL,
[MD5Hash] [nvarchar] (50) NULL,
[State] [nvarchar] (50) NULL,
[VersionsToKeep] [int] NULL,
[WorkflowId] [nvarchar] (50) NULL
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Entries] DROP
COLUMN [IsLocked]
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'Rebuilding {databaseOwner}[{objectQualifier}DMX_Log]'
GO
EXEC sp_rename '{databaseOwner}[{objectQualifier}DMX_Log].[Operation]', 'Action', 'COLUMN';
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'PK_{objectQualifier}DMX_Log') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Log ADD CONSTRAINT
	PK_{objectQualifier}DMX_Log PRIMARY KEY CLUSTERED 
	(
	[id]
	) ON [PRIMARY]
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'Altering {databaseOwner}[{objectQualifier}DMX_Extensions]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Extensions] ADD
[NodePattern] [nvarchar] (50) 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'Rebuilding {databaseOwner}[{objectQualifier}DMX_Attributes]'
GO
EXEC sp_rename '{databaseOwner}[{objectQualifier}DMX_Attributes].[id]', 'AttributeId', 'COLUMN';
GO
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Attributes ADD CONSTRAINT
	PK_{objectQualifier}DMX_Attributes PRIMARY KEY CLUSTERED 
	(
	[AttributeId]
	) ON [PRIMARY]
GO
PRINT N'Altering {databaseOwner}[{objectQualifier}DMX_Categories]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Categories] ADD
[ViewOrder] [int] NULL
GO
EXEC sp_rename '{databaseOwner}[{objectQualifier}DMX_Categories].[name]', 'Name_old', 'COLUMN';
GO
PRINT N'Creating {databaseOwner}[{objectQualifier}DMXDAV_Numbers]'
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{objectQualifier}DMXDAV_Numbers') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
 CREATE TABLE {databaseOwner}{objectQualifier}DMXDAV_Numbers(
  [Number] INT,
  CONSTRAINT [Index_Numbers] PRIMARY KEY CLUSTERED ([Number] ASC) ON [PRIMARY]) ON [PRIMARY]
 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

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'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_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]) 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_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_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_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_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'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_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_Extensions]'
GO
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Extensions] WITH NOCHECK ADD
CONSTRAINT [FK_{objectQualifier}DMX_Extensions_{objectQualifier}Portals] FOREIGN KEY ([PortalId]) REFERENCES {databaseOwner}[{objectQualifier}Portals] ([PortalID]) ON DELETE CASCADE NOT FOR REPLICATION,
CONSTRAINT [FK_{objectQualifier}DMX_Extensions_{objectQualifier}DMX_Addons] FOREIGN KEY ([Addon]) REFERENCES {databaseOwner}[{objectQualifier}DMX_Addons] ([AddonKey]) 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

/*
PROCEDURES AND FUNCTIONS
*/

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'Dropping {databaseOwner}[{objectQualifier}DMX_GetCategoryContents]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetCategoryContents]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetVersionList]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetVersionList]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetCollectionContents]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetCollectionContents]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetLogForEntry]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetLogForEntry]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetSubscriptionsByUsers]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetSubscriptionsByUsers]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetEntriesByUsers]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetEntriesByUsers]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetUserSubscriptions]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetUserSubscriptions]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetLogForItem]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetLogForItem]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetEntryAttributesByUsers]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetEntryAttributesByUsers]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetEntriesByPortals]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetEntriesByPortals]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetEntriesByCollectionId]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetEntriesByCollectionId]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetEntriesByLastVersion]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetEntriesByLastVersion]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_QuickSearch]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_QuickSearch]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetLatestApprovedEntry]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetLatestApprovedEntry]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_AddLogTypes]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_AddLogTypes]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetRecycleBinFileList]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetRecycleBinFileList]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_AddEntryAttributes]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_AddEntryAttributes]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetAttributesByAddonAndPortal]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetAttributesByAddonAndPortal]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_FindAttributesByName]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_FindAttributesByName]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_AddSubscriptions]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_AddSubscriptions]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_FindAddonsByName]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_FindAddonsByName]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetEntryAttributesByAttributes]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetEntryAttributesByAttributes]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_DeleteAnAttribute]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_DeleteAnAttribute]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_UpdateEntries]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_UpdateEntries]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetCategoryBreadCrumbs]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetCategoryBreadCrumbs]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetCompletePortalEntryList]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetCompletePortalEntryList]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetSubscriptionsByEntries]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetSubscriptionsByEntries]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetExtensionsByKey]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetExtensionsByKey]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_DeleteEntryAttributes]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_DeleteEntryAttributes]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetEntryAttributesByEntries]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetEntryAttributesByEntries]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetEntryAttributeList]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetEntryAttributeList]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_AddAddons]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_AddAddons]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_DeleteEntries]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_DeleteEntries]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetLogByUsers]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetLogByUsers]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetSubscriptions]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetSubscriptions]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetCategories]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetCategories]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetLogByEntries]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetLogByEntries]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_CleanupLog]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_CleanupLog]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetSubscriptionsByPortals]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetSubscriptionsByPortals]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetEntries]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetEntries]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_RelinkSubscriptions]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_RelinkSubscriptions]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_NumberOfCopies]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_NumberOfCopies]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetLatestAdditions]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetLatestAdditions]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_UpdateAddons]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_UpdateAddons]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetCategoriesByPortals]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetCategoriesByPortals]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetEntryAttributesByPortals]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetEntryAttributesByPortals]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetBreadCrumbs]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetBreadCrumbs]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_SearchAttributes]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_SearchAttributes]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_UpdateSubscriptions]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_UpdateSubscriptions]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetLogByLogTypes]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetLogByLogTypes]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetAllPortalCollections]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetAllPortalCollections]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_DeleteSubscriptions]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_DeleteSubscriptions]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_AddCategories]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_AddCategories]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetAddons]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetAddons]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_UpdateLogTypes]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_UpdateLogTypes]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetNrOfAttributes]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetNrOfAttributes]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetDeletedItems]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetDeletedItems]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetEntryAttributesByAddons]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetEntryAttributesByAddons]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_AddEntries]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_AddEntries]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_UpdateCategories]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_UpdateCategories]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetEntryAttributes]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetEntryAttributes]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetEntryAttributeByEntryAndAttributeName]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetEntryAttributeByEntryAndAttributeName]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetLogByPortals]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetLogByPortals]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_UpdateEntryAttributes]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_UpdateEntryAttributes]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetAttributeValue]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetAttributeValue]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_DeleteAddons]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_DeleteAddons]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetEntriesByEntryType]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetEntriesByEntryType]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_DeleteLogTypes]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_DeleteLogTypes]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetEntryAttributeByEntryAndAttribute]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetEntryAttributeByEntryAndAttribute]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_DeleteCategories]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_DeleteCategories]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_EmptyRecycleBin]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_EmptyRecycleBin]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_FindCategoriesByName]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_FindCategoriesByName]
GO
PRINT N'Dropping {databaseOwner}[{objectQualifier}DMX_GetExtensionsByAddonAndPortal]'
GO
DROP PROCEDURE {databaseOwner}[{objectQualifier}DMX_GetExtensionsByAddonAndPortal]
GO
PRINT N'Altering {databaseOwner}[{objectQualifier}DMX_AddLog]'
GO

ALTER 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_AddLogType]'
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddLogType
	@LogTypeKey NVarChar (20),
	@ResourceFile NVarChar (150)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_LogTypes (
	[LogTypeKey],
	[ResourceFile]
) VALUES (
	@LogTypeKey,
	@ResourceFile
)


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'Altering {databaseOwner}[{objectQualifier}DMX_GetExtension]'
GO

ALTER 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_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_AddShortText]'
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddShortText
	@TypeKey NVarChar (3),	
	@SubTypeKey NVarChar (20),	
	@ObjectId Int,	
	@Locale NVarChar (6),
	@Text NVarChar (255)
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_DeleteCategory]'
GO

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

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

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_GetPermissionsByPortal]'
GO

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

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


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

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

SELECT
	ea.[EntryId],
	ea.[Value],
	a.*
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_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'Altering {databaseOwner}[{objectQualifier}DMX_GetExtensionsByPortal]'
GO

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

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


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

ALTER PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByAddon
	@Addon NVarChar (50)
AS

SELECT
 *
FROM
 {databaseOwner}{objectQualifier}DMX_Attributes a
WHERE
 a.Addon = @Addon


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_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_GetApprovedEntry]'
GO

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

SELECT
	e.*,
 CASE
  WHEN (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale=@Locale) >0 THEN
   (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale=@Locale)
  ELSE
   (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale='')
 END Title,
 CASE
  WHEN (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale=@Locale) >0 THEN
   (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale=@Locale)
  ELSE
   (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale='')
 END 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'Altering {databaseOwner}[{objectQualifier}DMX_UpdateLog]'
GO

ALTER 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'Altering {databaseOwner}[{objectQualifier}DMX_GetAttributesByPortal]'
GO

ALTER PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByPortal
	@PortalId Int
AS

SELECT
	a.*, 
	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_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_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_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_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_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_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_DeleteLogType]'
GO

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

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

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 (255)
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_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_AddAddon]'
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAddon
	@AddonKey NVarChar (50),
	@Availability NVarChar (255),
	@Description NVarChar (255),
	@Installed DateTime,
	@Name NVarChar (50)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Addons (
	[AddonKey],
	[Availability],
	[Description],
	[Installed],
	[Name]
) VALUES (
	@AddonKey,
	@Availability,
	@Description,
	@Installed,
	@Name
)


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.*,
 CASE
  WHEN (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale=@Locale) >0 THEN
   (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale=@Locale)
  ELSE
   (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale='')
 END Title,
 CASE
  WHEN (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale=@Locale) >0 THEN
   (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale=@Locale)
  ELSE
   (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale='')
 END Remarks
FROM
	{databaseOwner}{objectQualifier}DMX_Entries e
WHERE
	[EntryId] = @EntryId
	AND [PortalId] = @PortalId

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

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

SELECT
	e.*,
 CASE
  WHEN (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale=@Locale) >0 THEN
   (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale=@Locale)
  ELSE
   (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale='')
 END Title,
 CASE
  WHEN (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale=@Locale) >0 THEN
   (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale=@Locale)
  ELSE
   (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale='')
 END Remarks
FROM
	{databaseOwner}{objectQualifier}DMX_Entries e
WHERE
	e.PortalId = @PortalId
	AND	((NOT e.Deleted = 1) OR (@IncludeDeleted = 1))


GO
PRINT N'Altering {databaseOwner}[{objectQualifier}DMX_AddExtension]'
GO

ALTER PROCEDURE {databaseOwner}{objectQualifier}DMX_AddExtension
	@PortalId Int,	
	@ExtensionKey NVarChar (50),
	@AccessRights NVarChar (255),
	@Addon NVarChar (50),
	@ControlToLoad NVarChar (255),
	@Custom NVarChar (400),
	@DownloadUrl 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
AS

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


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateAddon
	@AddonKey NVarChar (50),
	@Availability NVarChar (255),
	@Description NVarChar (255),
	@Installed DateTime,
	@Name NVarChar (50)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Addons SET
	[Availability] = @Availability,
	[Description] = @Description,
	[Installed] = @Installed,
	[Name] = @Name
WHERE
	[AddonKey] = @AddonKey

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

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

SELECT
	ea.[EntryId],
	ea.[Value],
	a.*
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_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_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_GetCategory]'
GO

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

SELECT
	c.*,
 CASE
  WHEN (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='CAT' AND SubTypeKey='NAME' AND ObjectId=c.id AND Locale=@Locale) >0 THEN
   (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='CAT' AND SubTypeKey='NAME' AND ObjectId=c.id AND Locale=@Locale)
  ELSE
   (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='CAT' AND SubTypeKey='NAME' AND ObjectId=c.id AND Locale='')
 END Name
FROM
	{databaseOwner}{objectQualifier}DMX_Categories c
WHERE
	c.PortalId = @PortalId
	AND c.id = @id


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_GetEntryPermission]'
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryPermission
	@EntryId Int,
	@PermissionId Int,
	@RoleId Int,
	@UserId Int,
        @PortalId int
AS

SELECT
 *
FROM
	{databaseOwner}{objectQualifier}DMX_EntryPermissions
WHERE
	[EntryId] = @EntryId
	AND [PermissionId] = @PermissionId
	AND [RoleId] = @RoleId
	AND [UserId] = @UserId
	AND [PortalId] = @PortalId

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_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_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_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'Altering {databaseOwner}[{objectQualifier}DMX_DeleteExtension]'
GO

ALTER 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_DeleteAddon]'
GO

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

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

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

ALTER 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_UpdateEntry]'
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntry
	@EntryId Int,
	@Author NVarChar (50),
	@CollectionId Int,
	@Deleted Bit,
	@Entry NVarChar (255),
	@EntryType NVarChar (150),
	@FileSize Int,
	@Created DateTime,
	@IsApproved Bit,
	@Keywords NVarChar (255),
	@LastModified DateTime,
	@LastVersionId Int,
	@LockedBy Int,
	@LockedUntil DateTime,
	@LockKey UNIQUEIDENTIFIER,
	@MD5Hash NVarChar (50),
	@OriginalFileName NVarChar (255),
	@Owner Int,
	@PortalId Int,
	@State NVarChar (50),
	@Version Int,
	@VersionsToKeep Int,
	@WorkflowId NVarChar (50)
AS

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

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'Altering {databaseOwner}[{objectQualifier}DMX_GetLogTypes]'
GO

ALTER PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogTypes
AS

SELECT
	[LogTypeKey],
	[ResourceFile]
FROM
	{databaseOwner}{objectQualifier}DMX_LogTypes


GO
PRINT N'Altering {databaseOwner}[{objectQualifier}DMX_FindAttributeByName]'
GO

ALTER PROCEDURE {databaseOwner}{objectQualifier}DMX_FindAttributeByName
	@Name NVarChar (50),
    @PortalId int
AS

SELECT
	*
FROM
	{databaseOwner}{objectQualifier}DMX_Attributes
WHERE
	[Name] = @Name
	AND [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}UserRoles ur ON ep.RoleId=ur.RoleID
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 ur.UserId=@UserId OR ep.UserId=@UserId OR u.IsSuperUser=1)


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateLogType
	@LogTypeKey NVarChar (20),
	@ResourceFile NVarChar (150)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_LogTypes SET
	[ResourceFile] = @ResourceFile
WHERE
	[LogTypeKey] = @LogTypeKey

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

ALTER PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAttribute

	@Addon NVarChar (50),
	@ControlToLoad NVarChar (255),
	@EntryTypes NVarChar (255),
	@IsPrivate Bit,
	@Name NVarChar (50),
	@PortalId Int,
	@Required Bit,
	@ResourceFile NVarChar (255),
	@Values NVarChar (2000),
	@ValueType NVarChar (30),
	@ViewOrder Int
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Attributes (
	[Addon],
	[ControlToLoad],
	[EntryTypes],
	[IsPrivate],
	[Name],
	[PortalId],
	[Required],
	[ResourceFile],
	[Values],
	[ValueType],
	[ViewOrder]
) VALUES (
	@Addon,
	@ControlToLoad,
	@EntryTypes,
	@IsPrivate,
	@Name,
	@PortalId,
	@Required,
	@ResourceFile,
	@Values,
	@ValueType,
	@ViewOrder
)

select SCOPE_IDENTITY()

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_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_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_GetLogsByUser]'
GO

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

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


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

ALTER PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateExtension
	@PortalId Int,
	@ExtensionKey NVarChar (50),
	@AccessRights NVarChar (255),
	@Addon NVarChar (50),
	@ControlToLoad NVarChar (255),
	@Custom NVarChar (400),
	@DownloadUrl 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
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions SET
	[AccessRights] = @AccessRights,
	[Addon] = @Addon,
	[ControlToLoad] = @ControlToLoad,
	[Custom] = @Custom,
	[DownloadUrl] = @DownloadUrl,
	[EntryTypes] = @EntryTypes,
	[Icon16] = @Icon16,
	[Icon32] = @Icon32,
	[IsPrivate] = @IsPrivate,
	[MimeType] = @MimeType,
	[NodePattern] = @NodePattern,
	[ResourceFile] = @ResourceFile,
	[SettingsControl] = @SettingsControl,
	[ViewByDefault] = @ViewByDefault
WHERE
	[PortalId] = @PortalId
	AND [ExtensionKey] = @ExtensionKey

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_GetEntryAttributesByEntry]'
GO

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

SELECT
	ea.[EntryId],
	ea.[Value],
	a.*
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_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'Altering {databaseOwner}[{objectQualifier}DMX_UpdateAttribute]'
GO

ALTER PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateAttribute
	@AttributeId Int,
	@Addon NVarChar (50),
	@ControlToLoad NVarChar (255),
	@EntryTypes NVarChar (255),
	@IsPrivate Bit,
	@Name NVarChar (50),
	@PortalId Int,
	@Required Bit,
	@ResourceFile NVarChar (255),
	@Values NVarChar (2000),
	@ValueType NVarChar (30),
	@ViewOrder Int
AS

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

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_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_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_DeleteEntry]'
GO

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

DELETE FROM {databaseOwner}{objectQualifier}DMX_Entries
WHERE
	[EntryId] = @EntryId

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'Altering {databaseOwner}[{objectQualifier}DMX_GetSubscription]'
GO

ALTER 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_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_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}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_GetShortTexts]'
GO

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

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

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_AddEntry]'
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntry

	@Author NVarChar (50),
	@CollectionId Int,
	@Deleted Bit,
	@Entry NVarChar (255),
	@EntryType NVarChar (150),
	@FileSize Int,
	@Created DateTime,
	@IsApproved Bit,
	@Keywords NVarChar (255),
	@LastModified DateTime,
	@LastVersionId Int,
	@LockedBy Int,
	@LockedUntil DateTime,
	@LockKey UNIQUEIDENTIFIER,
	@MD5Hash NVarChar (50),
	@OriginalFileName NVarChar (255),
	@Owner Int,
	@PortalId Int,
	@State NVarChar (50),
	@Version Int,
	@VersionsToKeep Int,
	@WorkflowId NVarChar (50)
AS

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

select SCOPE_IDENTITY()

GO
PRINT N'Altering {databaseOwner}[{objectQualifier}DMX_GetLog]'
GO

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

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

GO
PRINT N'Altering {databaseOwner}[{objectQualifier}DMX_GetAttribute]'
GO

ALTER PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttribute
	@AttributeId Int,
    @PortalId int
AS

SELECT
	*
FROM
	{databaseOwner}{objectQualifier}DMX_Attributes
WHERE
	[AttributeId] = @AttributeId
	AND [PortalId] = @PortalId

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

ALTER PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAttribute
	@AttributeId Int
AS

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

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_GetLogType]'
GO

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

SELECT
 *
FROM
	{databaseOwner}{objectQualifier}DMX_LogTypes
WHERE
	[LogTypeKey] = @LogTypeKey

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_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_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_GetCategoriesByPortal]'
GO

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

SELECT
	c.*,
 CASE
  WHEN (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='CAT' AND SubTypeKey='NAME' AND ObjectId=c.id AND Locale=@Locale) >0 THEN
   (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='CAT' AND SubTypeKey='NAME' AND ObjectId=c.id AND Locale=@Locale)
  ELSE
   (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='CAT' AND SubTypeKey='NAME' AND ObjectId=c.id AND Locale='')
 END Name
FROM
	{databaseOwner}{objectQualifier}DMX_Categories c
WHERE
	c.PortalId = @PortalId


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_GetEntryPermissionsByEntry]'
GO

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

SELECT
	ep.*,
	p.[Addon],
	p.[PermissionKey],
	p.[PermissionKey] AS DisplayName,
	p.[ResourceFile],
	r.[RoleName],
	u.[UserName]
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_GetLogsByPortal]'
GO

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

SELECT
 l.*,
 u.DisplayName
FROM
 {databaseOwner}{objectQualifier}Users u INNER JOIN 
 (({databaseOwner}{objectQualifier}DMX_Entries e INNER JOIN {databaseOwner}{objectQualifier}DMX_Log l ON e.EntryId = l.EntId)) 
  ON u.UserID = l.UserId
WHERE
 e.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_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_GetLogsByEntry]'
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetLogsByEntry
	@EntId Int
AS

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


GO

/*
FUNCTIONS
*/

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}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}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}DMXDAV_GetPath]'
GO

CREATE FUNCTION {databaseOwner}{objectQualifier}DMXDAV_GetPath
 (@EntryId INT, @PortalId INT)
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 = [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)+'/'+@name+{databaseOwner}{objectQualifier}DMXDAV_GetExtension(@orig)
 END
ELSE
 SELECT @res = ''
RETURN @res
END

GO


/*
DATA INITIALIZATION
*/

ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Categories] 
ALTER COLUMN Name_old nvarchar(100) NULL
GO

ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Entries] 
ALTER COLUMN Description nvarchar(150) NULL
GO

UPDATE {databaseOwner}[{objectQualifier}DMX_Extensions] SET [ControlToLoad]=N'Bring2mind/DMX/ViewCollection.ascx' WHERE [ControlToLoad]=N'Bring2mind/DMX/ViewDetails.ascx'
GO

DELETE FROM {databaseOwner}[{objectQualifier}DMX_Extensions] WHERE [ExtensionKey]=N'Command.Details' AND [Addon]=N'Core'
GO
DELETE FROM {databaseOwner}[{objectQualifier}DMX_Extensions] WHERE [ExtensionKey]=N'Command.Search' AND [Addon]=N'Core'
GO
DELETE FROM {databaseOwner}[{objectQualifier}DMX_Extensions] WHERE [ExtensionKey]=N'Command.ViewCategory' AND [Addon]=N'Core'
GO
DELETE FROM {databaseOwner}[{objectQualifier}DMX_Extensions] WHERE [ExtensionKey]=N'Command.ViewSubscriptions' AND [Addon]=N'Core'
GO
DELETE FROM {databaseOwner}[{objectQualifier}DMX_Extensions] WHERE [ExtensionKey]=N'Navbutton.Categories' AND [Addon]=N'Core'
GO
DELETE FROM {databaseOwner}[{objectQualifier}DMX_Extensions] WHERE [ExtensionKey]=N'Navbutton.Collections' AND [Addon]=N'Core'
GO
DELETE FROM {databaseOwner}[{objectQualifier}DMX_Extensions] WHERE [ExtensionKey]=N'Navbutton.MySubscriptions' AND [Addon]=N'Core'
GO
DELETE FROM {databaseOwner}[{objectQualifier}DMX_Extensions] WHERE [ExtensionKey]=N'Navbutton.Search' AND [Addon]=N'Core'
GO


INSERT INTO {databaseOwner}[{objectQualifier}DMX_Extensions] ([ExtensionKey], [PortalId], [AccessRights], [Addon], [ControlToLoad], [Custom], [DownloadUrl], [EntryTypes], [Icon16], [Icon32], [IsPrivate], [MimeType], [NodePattern], [ResourceFile], [SettingsControl], [ViewByDefault]) SELECT N'Contents.Categories', p.PortalId, N'-1;', N'Core', N'Bring2mind.DNN.Modules.DMX.Business.CategoriesController, Bring2mind.DNN.Modules.DMX.Core', N'1', NULL, NULL, NULL, NULL, 0, NULL, N'cat(\d+)', N'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources', NULL, NULL FROM {databaseOwner}[{objectQualifier}Portals] p
GO
INSERT INTO {databaseOwner}[{objectQualifier}DMX_Extensions] ([ExtensionKey], [PortalId], [AccessRights], [Addon], [ControlToLoad], [Custom], [DownloadUrl], [EntryTypes], [Icon16], [Icon32], [IsPrivate], [MimeType], [NodePattern], [ResourceFile], [SettingsControl], [ViewByDefault]) SELECT N'Contents.Collections', p.PortalId, N'-1;', N'Core', N'Bring2mind.DNN.Modules.DMX.Business.EntriesController, Bring2mind.DNN.Modules.DMX.Core', N'0', NULL, NULL, NULL, NULL, 0, NULL, N'ent(\d+)', N'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources', NULL, NULL FROM {databaseOwner}[{objectQualifier}Portals] p
GO
INSERT INTO {databaseOwner}[{objectQualifier}DMX_Extensions] ([ExtensionKey], [PortalId], [AccessRights], [Addon], [ControlToLoad], [Custom], [DownloadUrl], [EntryTypes], [Icon16], [Icon32], [IsPrivate], [MimeType], [NodePattern], [ResourceFile], [SettingsControl], [ViewByDefault]) SELECT N'Contents.NonApprovedItems', p.PortalId, N'-1;', N'Core', N'Bring2mind.DNN.Modules.DMX.Framework.ApprovalController, Bring2mind.DNN.Modules.DMX.Core', N'3', NULL, NULL, NULL, NULL, 0, NULL, N'nonapproveditems|nonapprovedowneditems', N'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources', NULL, NULL FROM {databaseOwner}[{objectQualifier}Portals] p
GO
INSERT INTO {databaseOwner}[{objectQualifier}DMX_Extensions] ([ExtensionKey], [PortalId], [AccessRights], [Addon], [ControlToLoad], [Custom], [DownloadUrl], [EntryTypes], [Icon16], [Icon32], [IsPrivate], [MimeType], [NodePattern], [ResourceFile], [SettingsControl], [ViewByDefault]) SELECT N'Contents.SearchResults', p.PortalId, N'-1;', N'Core', N'Bring2mind.DNN.Modules.DMX.Services.Search.SearchController, Bring2mind.DNN.Modules.DMX.Core', N'2', NULL, NULL, NULL, NULL, 0, NULL, N'searchresults', N'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources', NULL, NULL FROM {databaseOwner}[{objectQualifier}Portals] p
GO
INSERT INTO {databaseOwner}[{objectQualifier}DMX_Extensions] ([ExtensionKey], [PortalId], [AccessRights], [Addon], [ControlToLoad], [Custom], [DownloadUrl], [EntryTypes], [Icon16], [Icon32], [IsPrivate], [MimeType], [NodePattern], [ResourceFile], [SettingsControl], [ViewByDefault]) SELECT N'Contents.Subscriptions', p.PortalId, N'-1;', N'Core', N'Bring2mind.DNN.Modules.DMX.Business.SubscriptionsController, Bring2mind.DNN.Modules.DMX.Core', N'4', NULL, NULL, NULL, NULL, 0, NULL, N'subscriptions', N'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources', NULL, NULL FROM {databaseOwner}[{objectQualifier}Portals] p
GO
INSERT INTO {databaseOwner}[{objectQualifier}DMX_Extensions] ([ExtensionKey], [PortalId], [AccessRights], [Addon], [ControlToLoad], [Custom], [DownloadUrl], [EntryTypes], [Icon16], [Icon32], [IsPrivate], [MimeType], [NodePattern], [ResourceFile], [SettingsControl], [ViewByDefault]) SELECT N'DetailsPage.Details', p.PortalId, N'-1;', N'Core', N'Bring2mind/DMX/Controls/Details.ascx', N'0', NULL, N'File;Collection;Hyperlink;', NULL, NULL, 0, NULL, N'ent(\d+)', N'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources', NULL, NULL FROM {databaseOwner}[{objectQualifier}Portals] p
GO

INSERT INTO {databaseOwner}[{objectQualifier}DMX_Permissions] ([Addon], [PermissionKey], [PortalId], [ResourceFile]) SELECT N'Core', N'VIEW', p.PortalId, N'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources' FROM {databaseOwner}[{objectQualifier}Portals] p
GO
INSERT INTO {databaseOwner}[{objectQualifier}DMX_Permissions] ([Addon], [PermissionKey], [PortalId], [ResourceFile]) SELECT N'Core', N'EDIT', p.PortalId, N'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources' FROM {databaseOwner}[{objectQualifier}Portals] p
GO
INSERT INTO {databaseOwner}[{objectQualifier}DMX_Permissions] ([Addon], [PermissionKey], [PortalId], [ResourceFile]) SELECT N'Core', N'ADD', p.PortalId, N'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources' FROM {databaseOwner}[{objectQualifier}Portals] p
GO
INSERT INTO {databaseOwner}[{objectQualifier}DMX_Permissions] ([Addon], [PermissionKey], [PortalId], [ResourceFile]) SELECT N'Core', N'APPROVE', p.PortalId, N'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources' FROM {databaseOwner}[{objectQualifier}Portals] p
GO

INSERT INTO {databaseOwner}[{objectQualifier}DMX_PermissionEntrytypes] ([PermissionId], [EntryType]) SELECT [PermissionId], N'File' FROM {databaseOwner}[{objectQualifier}DMX_Permissions] WHERE [PermissionKey]='VIEW'
GO
INSERT INTO {databaseOwner}[{objectQualifier}DMX_PermissionEntrytypes] ([PermissionId], [EntryType]) SELECT [PermissionId], N'Collection' FROM {databaseOwner}[{objectQualifier}DMX_Permissions] WHERE [PermissionKey]='VIEW'
GO
INSERT INTO {databaseOwner}[{objectQualifier}DMX_PermissionEntrytypes] ([PermissionId], [EntryType]) SELECT [PermissionId], N'Hyperlink' FROM {databaseOwner}[{objectQualifier}DMX_Permissions] WHERE [PermissionKey]='VIEW'
GO
INSERT INTO {databaseOwner}[{objectQualifier}DMX_PermissionEntrytypes] ([PermissionId], [EntryType]) SELECT [PermissionId], N'File' FROM {databaseOwner}[{objectQualifier}DMX_Permissions] WHERE [PermissionKey]='EDIT'
GO
INSERT INTO {databaseOwner}[{objectQualifier}DMX_PermissionEntrytypes] ([PermissionId], [EntryType]) SELECT [PermissionId], N'Collection' FROM {databaseOwner}[{objectQualifier}DMX_Permissions] WHERE [PermissionKey]='EDIT'
GO
INSERT INTO {databaseOwner}[{objectQualifier}DMX_PermissionEntrytypes] ([PermissionId], [EntryType]) SELECT [PermissionId], N'Hyperlink' FROM {databaseOwner}[{objectQualifier}DMX_Permissions] WHERE [PermissionKey]='EDIT'
GO
INSERT INTO {databaseOwner}[{objectQualifier}DMX_PermissionEntrytypes] ([PermissionId], [EntryType]) SELECT [PermissionId], N'Collection' FROM {databaseOwner}[{objectQualifier}DMX_Permissions] WHERE [PermissionKey]='ADD'
GO
INSERT INTO {databaseOwner}[{objectQualifier}DMX_PermissionEntrytypes] ([PermissionId], [EntryType]) SELECT [PermissionId], N'File' FROM {databaseOwner}[{objectQualifier}DMX_Permissions] WHERE [PermissionKey]='APPROVE'
GO
INSERT INTO {databaseOwner}[{objectQualifier}DMX_PermissionEntrytypes] ([PermissionId], [EntryType]) SELECT [PermissionId], N'Collection' FROM {databaseOwner}[{objectQualifier}DMX_Permissions] WHERE [PermissionKey]='APPROVE'
GO
INSERT INTO {databaseOwner}[{objectQualifier}DMX_PermissionEntrytypes] ([PermissionId], [EntryType]) SELECT [PermissionId], N'Hyperlink' FROM {databaseOwner}[{objectQualifier}DMX_Permissions] WHERE [PermissionKey]='APPROVE'
GO

INSERT INTO {databaseOwner}[{objectQualifier}DMX_PermissionPermissions] ([PermissionId], [RoleId]) 
SELECT dmxp.PermissionId, p.RegisteredRoleId FROM {databaseOwner}{objectQualifier}Portals p INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions dmxp ON dmxp.PortalId=p.PortalId
WHERE dmxp.PermissionKey='VIEW'
GO

INSERT INTO {databaseOwner}[{objectQualifier}DMX_PermissionPermissions] ([PermissionId], [RoleId]) 
SELECT dmxp.PermissionId, p.RegisteredRoleId FROM {databaseOwner}{objectQualifier}Portals p INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions dmxp ON dmxp.PortalId=p.PortalId
WHERE dmxp.PermissionKey='EDIT'
GO

INSERT INTO {databaseOwner}[{objectQualifier}DMX_PermissionPermissions] ([PermissionId], [RoleId]) 
SELECT dmxp.PermissionId, p.AdministratorRoleId FROM {databaseOwner}{objectQualifier}Portals p INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions dmxp ON dmxp.PortalId=p.PortalId
WHERE dmxp.PermissionKey='ADD'
GO

INSERT INTO {databaseOwner}[{objectQualifier}DMX_PermissionPermissions] ([PermissionId], [RoleId]) 
SELECT dmxp.PermissionId, p.AdministratorRoleId FROM {databaseOwner}{objectQualifier}Portals p INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions dmxp ON dmxp.PortalId=p.PortalId
WHERE dmxp.PermissionKey='APPROVE'
GO


