/****** Various Tuning Enhancements ******/
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetSpecificEntryPermission') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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}UserRoles ur ON ep.RoleId=ur.RoleId
WHERE (ep.UserID=@UserId
 OR ur.UserId=@UserId
 OR ep.RoleId=-1)
 AND ep.PortalId=@PortalId
 AND ep.EntryId=@EntryId
 AND p.PermissionKey=@PermissionKey
 AND (ISNULL(ur.EffectiveDate, GETDATE()) <= GETDATE())
 AND (ISNULL(ur.ExpiryDate,GETDATE()) >= GETDATE())

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_ClearEntryPermissions') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetLongTexts') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetShortTexts') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryPermissionsForUser') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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}UserRoles ur ON ur.RoleId=ep.RoleId 
WHERE 
 (ep.RoleId=-1 OR ur.UserId=@UserId OR ep.UserId=@UserId) 
 AND (ep.EntryId=@EntryId) 
 AND (ep.PortalId=@PortalId)
 AND (ISNULL(ur.EffectiveDate, GETDATE()) <= GETDATE())
 AND (ISNULL(ur.ExpiryDate,GETDATE()) >= GETDATE())

GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_UserAccess') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetPermissionsByPortal') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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}UserRoles ur ON ur.RoleId=pp.RoleId 
   WHERE pp.PermissionId=p.PermissionId 
    AND (ur.UserId=@UserId OR pp.RoleId=-1
    AND (ISNULL(ur.EffectiveDate, GETDATE()) <= GETDATE()) 
    AND (ISNULL(ur.ExpiryDate,GETDATE()) >= GETDATE()))) AS Bit) AS IsAllowedToEdit 
 FROM {databaseOwner}{objectQualifier}DMX_Permissions p 
 WHERE (p.PortalId=@PortalID)

GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetPermissionsForEntrytype') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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}UserRoles ur ON ur.RoleId=pp.RoleId 
   WHERE pp.PermissionId=p.PermissionId 
    AND (ur.UserId=@UserId OR pp.RoleId=-1) 
    AND (ISNULL(ur.EffectiveDate, GETDATE()) <= GETDATE())
    AND (ISNULL(ur.ExpiryDate,GETDATE()) >= GETDATE())) AS Bit) AS IsAllowedToEdit
FROM {databaseOwner}{objectQualifier}DMX_Permissions p 
 INNER JOIN {databaseOwner}{objectQualifier}DMX_PermissionEntrytypes pe ON p.PermissionId=pe.PermissionId
WHERE (p.PortalId=@PortalID)
 AND @Entrytype LIKE pe.Entrytype+'%';

END

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryCategories') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetCategories') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetSubCategories') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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

GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_NumberOfCategories') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryListAsXml
 @EntryList VARCHAR(300),
 @UserId INT
AS

SELECT DISTINCT Entry.Author, Entry.CollectionId, Entry.Created, Entry.Deleted, Entry.Entry, 
Entry.EntryId, Entry.EntryType, Entry.FileSize, Entry.IsApproved, Entry.Keywords, 
Entry.LastVersionId, Entry.LockedBy, Entry.OriginalFileName, Entry.PortalId, Entry.Owner, 
Entry.Version, Entry.LastModified, Entry.LockedUntil, Entry.LockKey, Entry.MD5Hash, Entry.State, 
Entry.VersionsToKeep, Entry.WorkflowId, 
REPLACE({databaseOwner}{objectQualifier}DMXDAV_URLEncode({databaseOwner}{objectQualifier}DMXDAV_GetPath(Entry.EntryId, Entry.PortalId, '')), '''', '\''') AS FullPath, 
CAST (CASE WHEN Entry.LockedUntil > GETDATE() THEN 1 ELSE 0 END AS BIT) AS IsLocked,
CAST (CASE WHEN subs.EntryID IS NULL THEN 0 ELSE 1 END AS BIT) AS Subscription,
Permission.PermissionKey 
FROM (SELECT DMXEntry.*, 
ext.ControlToLoad, ext.Custom, ext.DownloadUrl, ext.EntryTypes, ext.Icon16, 
ext.Icon32, ext.IsPrivate, ext.MimeType, ext.ResourceFile, ext.SettingsControl, 
ext.ViewByDefault
FROM (SELECT s FROM {databaseOwner}{objectQualifier}DMX_Split(@EntryList,';')) recs
INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries DMXEntry ON recs.s=DMXEntry.EntryId
INNER JOIN {databaseOwner}{objectQualifier}DMX_Extensions ext ON ext.ExtensionKey=DMXEntry.EntryType AND DMXEntry.PortalId=ext.PortalId) AS Entry 
INNER JOIN (SELECT ep1.EntryId, Permission.PermissionKey 
FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep1
INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions Permission ON Permission.PermissionId=ep1.PermissionId 
LEFT JOIN {databaseOwner}{objectQualifier}UserRoles ur ON ur.RoleId=ep1.RoleId
WHERE ur.UserID=@UserId OR ep1.UserId=@UserId OR ep1.RoleId=-1
 AND (ISNULL(ur.EffectiveDate, GETDATE()) <= GETDATE())
 AND (ISNULL(ur.ExpiryDate,GETDATE()) >= GETDATE())) Permission
ON Permission.EntryId=Entry.EntryId 
LEFT JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_Subscriptions WHERE UserID=@UserId) subs ON subs.EntryId=Entry.EntryId
FOR XML AUTO

GO

CREATE NONCLUSTERED INDEX IX_{objectQualifier}DMX_Entries_CollectionID ON {databaseOwner}{objectQualifier}DMX_Entries
	(
	CollectionId
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX IX_{objectQualifier}UserRoles_DMX ON {databaseOwner}{objectQualifier}UserRoles
(
	[RoleID] ASC
)
INCLUDE ([UserID],
[ExpiryDate],
[EffectiveDate]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX IX_{objectQualifier}DMX_Entries_Data1 ON {databaseOwner}{objectQualifier}DMX_Entries
(
	[LastVersionId] ASC,
	[EntryId] ASC,
	[CollectionId] ASC,
	[Deleted] ASC,
	[IsApproved] ASC,
	[Owner] ASC,
	[Version] ASC,
	[EntryType] ASC
)
INCLUDE ([Author],
[Entry],
[FileSize],
[Created],
[Keywords],
[LastModified],
[LockedBy],
[LockedUntil],
[LockKey],
[MD5Hash],
[OriginalFileName],
[PortalId],
[State],
[VersionsToKeep],
[WorkflowId],
[Path],
[PermissionsOnlyByAdmin]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

/****** SyncFolder Additions ******/
INSERT INTO {databaseOwner}{objectQualifier}DMX_Extensions ([AccessRights], [Addon], [ControlToLoad], [Custom], [ExtensionKey], [Icon16], [Icon32], [IsPrivate], [MimeType], [ResourceFile], [PortalId], [ViewByDefault], [EntryTypes])
SELECT CAST(p.AdministratorRoleID AS VARCHAR(10))+';', 
 'Core',
 'Bring2mind/DMX/ViewCollection.ascx',
 Null, 
 'Collection.SyncFolder',
 'DesktopModules/Bring2mind/DMX/images/Collection.SyncFolder.gif',
 'DesktopModules/Bring2mind/DMX/images/Collection.SyncFolder.Big.gif',
 0,
 Null,
 'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources',
 p.PortalId,
 1,
 Null
FROM {databaseOwner}{objectQualifier}Portals p
WHERE
 NOT EXISTS (SELECT * FROM 
 {databaseOwner}{objectQualifier}Portals q INNER JOIN {databaseOwner}{objectQualifier}DMX_Extensions ON q.PortalID = {databaseOwner}{objectQualifier}DMX_Extensions.PortalId
 WHERE
 ({objectQualifier}DMX_Extensions.Addon = 'Core')
 AND ({objectQualifier}DMX_Extensions.ExtensionKey = 'Collection.SyncFolder')
 AND (p.PortalId = q.PortalID))
GO

IF NOT EXISTS (select * from {databaseOwner}{objectQualifier}Schedule WHERE [TypeFullName]='Bring2mind.DNN.Modules.DMX.Framework.SyncFolderTask, BRING2MIND.DNN.MODULES.DMX.CORE')
INSERT INTO {databaseOwner}{objectQualifier}Schedule ([TypeFullName], [TimeLapse], [TimeLapseMeasurement], [RetryTimeLapse], [RetryTimeLapseMeasurement], [RetainHistoryNum], [AttachToEvent], [CatchUpEnabled], [Enabled], [ObjectDependencies])
VALUES ('Bring2mind.DNN.Modules.DMX.Framework.SyncFolderTask, BRING2MIND.DNN.MODULES.DMX.CORE', 2, 'm', 10, 'm', 20, '', 0, 1, '')

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryByTitleInCollection') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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


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

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

SELECT e.* 
FROM {databaseOwner}{objectQualifier}DMX_Entries e
WHERE e.CollectionId=@CollectionId
AND e.OriginalFilename=@Filename
AND PortalId=@PortalId
AND e.LastVersionId=e.EntryId

GO

/****** Changes to Entries Table for new attributes mechanism ******/

ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Entries] ADD
[InheritAttributes] [bit] NOT NULL DEFAULT 1
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntry
	@Author NVARCHAR(500),
	@CollectionId Int,
	@Created DateTime,
	@Deleted Bit,
	@Entry NVARCHAR(255),
	@EntryType NVARCHAR(150),
	@FileSize Int,
	@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),
	@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],
	[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,
	@Version,
	@VersionsToKeep,
	@WorkflowId
)

select SCOPE_IDENTITY()
GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntry
	@EntryId Int,
	@Author NVARCHAR(500),
	@CollectionId Int,
	@Created DateTime,
	@Deleted Bit,
	@Entry NVARCHAR(255),
	@EntryType NVARCHAR(150),
	@FileSize Int,
	@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),
	@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,
	[Version] = @Version,
	[VersionsToKeep] = @VersionsToKeep,
	[WorkflowId] = @WorkflowId
WHERE
	[EntryId] = @EntryId
GO

/****** New AttributeEntrytypes Table ******/

if not exists (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_AttributeEntrytypes') and OBJECTPROPERTY(id, N'IsTable') = 1)
CREATE TABLE {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes (
 [AttributeId] INT NOT NULL,
 [EntryType] NVARCHAR (50) NOT NULL
) ON [PRIMARY]
GO

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

if not exists (select * from dbo.sysobjects where id = object_id(N'FK_{objectQualifier}DMX_AttributeEntrytypes_{objectQualifier}DMX_') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes 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

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAttributeEntrytype') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_AddAttributeEntrytype') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_DeleteAttributeEntrytype') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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

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

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

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


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

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

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

GO

/****** Changes to Attributes Table ******/

ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Attributes] ADD
[CollectionId] INT NOT NULL DEFAULT -1,
[ShowInUI] BIT NULL
GO

EXEC sp_rename '{databaseOwner}{objectQualifier}DMX_Attributes.[Name]', 'Key', 'COLUMN';
GO

ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Attributes]
ALTER COLUMN [Key] NVARCHAR(50) NULL
GO

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

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

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

select SCOPE_IDENTITY()
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_UpdateAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAttributesByAddon') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAttributesByPortal') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByPortal
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByPortal
	@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,
	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

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_MoveAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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

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

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_FindAttributeByKey') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryAttributesByAttribute') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryAttributesByEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryAttributeByEntryAndKey') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryAttributeListForEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryAttributesByKeyAndValue') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetPortalAttributeListWithEntryData') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAttributesByEntryType') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAttributeListForEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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)

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetAttributes') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_MoveEntryAttributes') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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

/****** Migrate Data ******/

INSERT INTO {databaseOwner}{objectQualifier}DMX_ShortTexts 
 ([Locale],[ObjectId],[SubTypeKey],[Text],[TypeKey])
SELECT
 '', AttributeId, 'NAME', [Key], 'ATT'
FROM {databaseOwner}{objectQualifier}DMX_Attributes
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Attributes
SET [Key]=REPLACE([Key], ' ', '')
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Attributes
SET [ValueType]='Custom'
WHERE NOT (ControlToLoad IS NULL OR ControlToLoad = '')
GO

BEGIN
 DECLARE @AttributeId INT
 DECLARE @EntryTypes NVARCHAR(255)
 DECLARE cursorX CURSOR FOR
 SELECT AttributeId, EntryTypes FROM {databaseOwner}{objectQualifier}DMX_Attributes;
 BEGIN
  OPEN cursorX
  FETCH cursorX
  INTO @AttributeId, @EntryTypes
  WHILE @@fetch_status=0
  BEGIN
   IF NOT (@EntryTypes IS NULL OR @EntryTypes = '')
    INSERT INTO {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes (AttributeId, EntryType)
     SELECT @AttributeId, s FROM {databaseOwner}{objectQualifier}DMX_Split(@EntryTypes, ';')
     WHERE NOT EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeId=@AttributeId AND EntryType=s)
   FETCH cursorX
   INTO @AttributeId, @EntryTypes;
  END;
  CLOSE cursorX;
  DEALLOCATE cursorX;
 END;
END
GO

/****** Path length adjustment ******/
ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Entries]
ALTER COLUMN [Path] VARCHAR(4000) NULL
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_MoveEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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))+';'
 UPDATE {databaseOwner}{objectQualifier}DMX_Entries
  SET CollectionId=@NewCollectionId
 WHERE
  LastVersionId=@EntryId
  AND PortalId=@PortalId

GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_SoftDeleteEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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=@EntryId AND PortalId=@PortalId)
 IF @LastVersionId=@EntryId
  IF @Delete=1
   UPDATE {databaseOwner}{objectQualifier}DMX_Entries
    SET Deleted=@Delete
    WHERE Path LIKE @Path + '%' AND PortalId=@PortalId;
  ELSE
   UPDATE {databaseOwner}{objectQualifier}DMX_Entries
    SET Deleted=@Delete
    WHERE LastVersionId=@LastVersionId AND PortalId=@PortalId;
 ELSE
  UPDATE {databaseOwner}{objectQualifier}DMX_Entries
   SET Deleted=@Delete
   WHERE EntryId=@EntryId AND PortalId=@PortalId;
END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_UpdatePaths') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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


/****** Trigger stuff ******/
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_EntryUpdatePath') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath
GO

CREATE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath
ON {databaseOwner}{objectQualifier}DMX_Entries
AFTER INSERT, UPDATE
AS

 DECLARE @EntryId INT
 DECLARE @PortalId INT
 DECLARE @RowCount INT
 SELECT @RowCount=(SELECT COUNT(*) FROM INSERTED)

 IF @RowCount=1
  BEGIN
   SELECT @EntryId = (SELECT EntryId FROM INSERTED);
   SELECT @PortalId = (SELECT PortalId FROM INSERTED);
   DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
   EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, @EntryId;
   ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
   EXEC {databaseOwner}{objectQualifier}DMX_SetLastModified @PortalId, @EntryId;
  END
 ELSE
  BEGIN
   DECLARE cursorX CURSOR FOR
   SELECT INSERTED.EntryId, INSERTED.PortalID FROM INSERTED INNER JOIN DELETED
    ON INSERTED.EntryId= DELETED.EntryId
    WHERE INSERTED.CollectionId <> DELETED.EntryId;
   BEGIN
    OPEN cursorX
    FETCH cursorX
    INTO @EntryId, @PortalId
    WHILE @@fetch_status=0
    BEGIN
     FETCH cursorX
     INTO @EntryId, @PortalId;
     DISABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
  	  EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, @EntryId;
     ENABLE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryUpdatePath ON {databaseOwner}{objectQualifier}DMX_Entries;
  	  EXEC {databaseOwner}{objectQualifier}DMX_SetLastModified @PortalId, @EntryId;
    END;
    CLOSE cursorX;
    DEALLOCATE cursorX;
   END;
  END
  
GO

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

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

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

GO

/****** New mechanism to hook in default view and edit screens ******/
UPDATE {databaseOwner}{objectQualifier}DMX_Extensions
SET ControlToLoad='{defaultview}'
WHERE ControlToLoad='Bring2mind/DMX/ViewCollection.ascx'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions
SET ControlToLoad='{defaultedit}'
WHERE ControlToLoad='Bring2mind/DMX/EditEntry.ascx'
GO

UPDATE {databaseOwner}{objectQualifier}ModuleSettings 
 SET SettingName='DefaultViewPane'
FROM {databaseOwner}{objectQualifier}ModuleSettings ms
 INNER JOIN {databaseOwner}{objectQualifier}Modules m ON m.ModuleId=ms.ModuleId
 INNER JOIN {databaseOwner}{objectQualifier}ModuleDefinitions md ON m.ModuleDefID=md.ModuleDefID
 INNER JOIN {databaseOwner}{objectQualifier}DesktopModules dm ON dm.DesktopModuleId=md.DesktopModuleID
WHERE dm.FolderName='Bring2mind\DMX'
 AND ms.SettingName='DefaultPane'
GO


/****** Hard delete revision ******/
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_DeleteEntry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntryBranch') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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+'%'
 AND (@IncludeSelf=1 OR (NOT e.[Path]=@Path))
 AND (e.Version=(SELECT MAX(Version) FROM {databaseOwner}{objectQualifier}DMX_Entries e2 WHERE e2.LastVersionId=e.LastVersionId) OR @AllVersions=1)
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_DeleteEntryBranch') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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_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+'%'
 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+'%'
 AND (@IncludeSelf=1 OR (NOT e.[Path]=@Path));
DELETE FROM {databaseOwner}{objectQualifier}DMX_Entries
WHERE [Path] LIKE @Path+'%'
 AND (@IncludeSelf=1 OR (NOT [Path]=@Path));
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetBreadCrumbs') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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

/****** Syncfolder Mgt ******/

INSERT INTO {databaseOwner}{objectQualifier}DMX_LogTypes 
 ([LogTypeKey], [PortalId], [ResourceFile], [AttachFile], [CustomSend], [NotifyApprovers], [NotifyEditors], [NotifyOwner], [NotifySelf], [NotifySubscribers], [NotifyAudit]) 
SELECT N'Synchronize', p.PortalId, NULL, 0, 0, NULL, NULL, NULL, NULL, NULL, 1
 FROM {databaseOwner}{objectQualifier}Portals p
WHERE NOT EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}DMX_LogTypes WHERE PortalID=p.PortalID AND LogTypeKey='Synchronize')
GO

INSERT INTO {databaseOwner}{objectQualifier}DMX_LogTypes 
 ([LogTypeKey], [PortalId], [ResourceFile], [AttachFile], [CustomSend], [NotifyApprovers], [NotifyEditors], [NotifyOwner], [NotifySelf], [NotifySubscribers], [NotifyAudit]) 
SELECT N'SyncIn', p.PortalId, NULL, 0, 0, N'CORE_APPROVE', NULL, NULL, NULL, N'CORE_SUBSCRIBER', 1
 FROM {databaseOwner}{objectQualifier}Portals p
WHERE NOT EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}DMX_LogTypes WHERE PortalID=p.PortalID AND LogTypeKey='SyncIn')
GO

INSERT INTO {databaseOwner}{objectQualifier}DMX_LogTypes 
 ([LogTypeKey], [PortalId], [ResourceFile], [AttachFile], [CustomSend], [NotifyApprovers], [NotifyEditors], [NotifyOwner], [NotifySelf], [NotifySubscribers], [NotifyAudit]) 
SELECT N'SyncOut', p.PortalId, NULL, 0, 0, NULL, NULL, NULL, NULL, NULL, 1
 FROM {databaseOwner}{objectQualifier}Portals p
WHERE NOT EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}DMX_LogTypes WHERE PortalID=p.PortalID AND LogTypeKey='SyncOut')
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetLastActionEvent') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {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

-- SyncFolder Attribute
INSERT INTO {databaseOwner}{objectQualifier}DMX_Attributes (Addon, CollectionId, ControlToLoad, IsPrivate, [Key], PortalId, Required, ResourceFile, ShowInUI, [Values], ValueType, ViewOrder)
SELECT 'Core', -1, 'Bring2mind/DMX/Controls/SyncFolderAtts.ascx', 0, 'Att_SyncFolder', p.PortalId, 0, 'DesktopModules/Bring2mind/DMX/App_LocalResources/SharedResources', 0, NULL, 'Custom', 0
FROM {databaseOwner}{objectQualifier}Portals p
WHERE
 NOT EXISTS (SELECT * FROM 
 {databaseOwner}{objectQualifier}Portals q INNER JOIN {databaseOwner}{objectQualifier}DMX_Attributes ON q.PortalID = {databaseOwner}{objectQualifier}DMX_Attributes.PortalId
 WHERE
 ({objectQualifier}DMX_Attributes.Addon = 'Core')
 AND ({objectQualifier}DMX_Attributes.[Key] = 'Att_SyncFolder')
 AND (p.PortalId = q.PortalID))
GO

INSERT INTO {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes (AttributeID, EntryType)
SELECT a.AttributeId, 'Collection.SyncFolder'
FROM {databaseOwner}{objectQualifier}DMX_Attributes a
 WHERE a.[Key]='Att_SyncFolder'
 AND NOT EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}DMX_AttributeEntrytypes WHERE AttributeID=a.AttributeId AND EntryType='Collection.SyncFolder')
GO

INSERT INTO {databaseOwner}{objectQualifier}DMX_ShortTexts (ObjectID, TypeKey, SubTypeKey, Locale, Text)
SELECT a.AttributeId, 'ATT', 'NAME', '', 'Synchronized Folder'
FROM {databaseOwner}{objectQualifier}DMX_Attributes a
 WHERE a.[Key]='Att_SyncFolder'
 AND NOT EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE ObjectId=a.AttributeId AND TypeKey='ATT' AND SubTypeKey='NAME' AND Locale='')
GO

-- delete duplicates from scheduler

DECLARE @cnt int, @TypeFullName as varchar(200)

DECLARE dupCursor CURSOR FAST_FORWARD
FOR SELECT TypeFullName, COUNT(TypeFullName) AS Cnt
 FROM {databaseOwner}{objectQualifier}Schedule
 GROUP BY TypeFullName
 HAVING COUNT(TypeFullName) > 1
  AND TypeFullName LIKE 'Bring2mind%'

OPEN dupCursor

FETCH NEXT FROM dupCursor
INTO @TypeFullName, @cnt

WHILE @@FETCH_STATUS = 0
BEGIN
 DELETE TOP (@cnt-1)
 FROM {databaseOwner}{objectQualifier}Schedule
 WHERE TypeFullName = @TypeFullName

 FETCH NEXT FROM dupCursor
 INTO @TypeFullName, @cnt
END

CLOSE dupCursor
DEALLOCATE dupCursor
GO

-- hide WebDAV added custom attributes
UPDATE {databaseOwner}{objectQualifier}DMX_Attributes
SET IsPrivate=1
WHERE [Key] IN ('Win32CreationTime','Win32LastAccessTime','Win32LastModifiedTime')
GO

