/****** CHANGES TO MAIN TABLES: Attributes ******/

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_Attributes') and OBJECTPROPERTY(id, N'IsTable') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Attributes
ADD [ControlToLoad] NVARCHAR (255)
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttribute
	@id Int,
        @PortalId int
AS

SELECT
	[Addon],
	[ControlToLoad],
	[EntryTypes],
	[id],
	[IsPrivate],
	[Name],
	[PortalId],
	[Required],
	[ResourceFile],
	[Values],
	[ValueType],
	[ViewOrder]
FROM
	{databaseOwner}{objectQualifier}DMX_Attributes
WHERE
	[id] = @id
	AND [PortalId] = @PortalId
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{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),
	@ControlToLoad NVarChar (255),
	@EntryTypes NVarChar (255),
	@IsPrivate Bit,
	@Name NVarChar (50),
	@PortalId Int,
	@Required Bit,
	@ResourceFile NVarChar (255),
	@Values NVarChar (255),
	@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


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateAttribute
	@id Int,
	@Addon NVarChar (50),
	@ControlToLoad NVarChar (255),
	@EntryTypes NVarChar (255),
	@IsPrivate Bit,
	@Name NVarChar (50),
	@PortalId Int,
	@Required Bit,
	@ResourceFile NVarChar (255),
	@Values NVarChar (255),
	@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
	[id] = @id
GO

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

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

SELECT
	[Addon],
	[ControlToLoad],
	[EntryTypes],
	[id],
	[IsPrivate],
	[Name],
	[PortalId],
	[Required],
	[ResourceFile],
	[Values],
	[ValueType],
	[ViewOrder]
FROM
	{databaseOwner}{objectQualifier}DMX_Attributes
WHERE
	[Name] = @Name
	AND [PortalId] = @PortalId
GO

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

SELECT
	{databaseOwner}{objectQualifier}DMX_Attributes.[Addon],
	{databaseOwner}{objectQualifier}DMX_Attributes.[ControlToLoad],
	{databaseOwner}{objectQualifier}DMX_Attributes.[EntryTypes],
	{databaseOwner}{objectQualifier}DMX_Attributes.[id],
	{databaseOwner}{objectQualifier}DMX_Attributes.[IsPrivate],
	{databaseOwner}{objectQualifier}DMX_Attributes.[Name],
	{databaseOwner}{objectQualifier}DMX_Attributes.[PortalId],
	{databaseOwner}{objectQualifier}DMX_Attributes.[Required],
	{databaseOwner}{objectQualifier}DMX_Attributes.[ResourceFile],
	{databaseOwner}{objectQualifier}DMX_Attributes.[Values],
	{databaseOwner}{objectQualifier}DMX_Attributes.[ValueType],
	{databaseOwner}{objectQualifier}DMX_Attributes.[ViewOrder], 
	{databaseOwner}{objectQualifier}DMX_Addons.Description AS AddonsDescription, 
	{databaseOwner}{objectQualifier}Portals.Description AS PortalsDescription
FROM
	{databaseOwner}{objectQualifier}Portals INNER JOIN {databaseOwner}{objectQualifier}DMX_Addons INNER JOIN {databaseOwner}{objectQualifier}DMX_Attributes ON {databaseOwner}{objectQualifier}DMX_Addons.AddonKey = {databaseOwner}{objectQualifier}DMX_Attributes.Addon ON {databaseOwner}{objectQualifier}Portals.PortalID = {databaseOwner}{objectQualifier}DMX_Attributes.PortalId
WHERE
	{databaseOwner}{objectQualifier}DMX_Attributes.Addon = @Addon

GO

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

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

SELECT
	{databaseOwner}{objectQualifier}DMX_Attributes.[Addon],
	{databaseOwner}{objectQualifier}DMX_Attributes.[ControlToLoad],
	{databaseOwner}{objectQualifier}DMX_Attributes.[EntryTypes],
	{databaseOwner}{objectQualifier}DMX_Attributes.[id],
	{databaseOwner}{objectQualifier}DMX_Attributes.[IsPrivate],
	{databaseOwner}{objectQualifier}DMX_Attributes.[Name],
	{databaseOwner}{objectQualifier}DMX_Attributes.[PortalId],
	{databaseOwner}{objectQualifier}DMX_Attributes.[Required],
	{databaseOwner}{objectQualifier}DMX_Attributes.[ResourceFile],
	{databaseOwner}{objectQualifier}DMX_Attributes.[Values],
	{databaseOwner}{objectQualifier}DMX_Attributes.[ValueType],
	{databaseOwner}{objectQualifier}DMX_Attributes.[ViewOrder], 
	{databaseOwner}{objectQualifier}DMX_Addons.Description AS AddonsDescription, 
	{databaseOwner}{objectQualifier}Portals.Description AS PortalsDescription
FROM
	{databaseOwner}{objectQualifier}Portals INNER JOIN {databaseOwner}{objectQualifier}DMX_Addons INNER JOIN {databaseOwner}{objectQualifier}DMX_Attributes ON {databaseOwner}{objectQualifier}DMX_Addons.AddonKey = {databaseOwner}{objectQualifier}DMX_Attributes.Addon ON {databaseOwner}{objectQualifier}Portals.PortalID = {databaseOwner}{objectQualifier}DMX_Attributes.PortalId
WHERE
	{databaseOwner}{objectQualifier}DMX_Attributes.PortalId = @PortalId

GO

/****** CHANGES TO MAIN TABLES: Extensions ******/

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_Extensions') and OBJECTPROPERTY(id, N'IsTable') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Extensions
ADD  [DownloadUrl] NVARCHAR (255)
GO

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

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

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

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

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

SELECT
	{databaseOwner}{objectQualifier}DMX_Extensions.[AccessRights],
	{databaseOwner}{objectQualifier}DMX_Extensions.[Addon],
	{databaseOwner}{objectQualifier}DMX_Extensions.[ControlToLoad],
	{databaseOwner}{objectQualifier}DMX_Extensions.[Custom],
	{databaseOwner}{objectQualifier}DMX_Extensions.[DownloadUrl],
	{databaseOwner}{objectQualifier}DMX_Extensions.[EntryTypes],
	{databaseOwner}{objectQualifier}DMX_Extensions.[ExtensionKey],
	{databaseOwner}{objectQualifier}DMX_Extensions.[Icon16],
	{databaseOwner}{objectQualifier}DMX_Extensions.[Icon32],
	{databaseOwner}{objectQualifier}DMX_Extensions.[IsPrivate],
	{databaseOwner}{objectQualifier}DMX_Extensions.[MimeType],
	{databaseOwner}{objectQualifier}DMX_Extensions.[PortalId],
	{databaseOwner}{objectQualifier}DMX_Extensions.[ResourceFile],
	{databaseOwner}{objectQualifier}DMX_Extensions.[SettingsControl],
	{databaseOwner}{objectQualifier}DMX_Extensions.[ViewByDefault], 
	{databaseOwner}{objectQualifier}DMX_Addons.Description AS AddonsDescription, 
	{databaseOwner}{objectQualifier}Portals.Description AS PortalsDescription
FROM
	{databaseOwner}{objectQualifier}Portals INNER JOIN {databaseOwner}{objectQualifier}DMX_Addons INNER JOIN {databaseOwner}{objectQualifier}DMX_Extensions ON {databaseOwner}{objectQualifier}DMX_Addons.AddonKey = {databaseOwner}{objectQualifier}DMX_Extensions.Addon ON {databaseOwner}{objectQualifier}Portals.PortalID = {databaseOwner}{objectQualifier}DMX_Extensions.PortalId
WHERE
	{databaseOwner}{objectQualifier}DMX_Extensions.Addon = @Addon

GO

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

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

SELECT
	{databaseOwner}{objectQualifier}DMX_Extensions.[AccessRights],
	{databaseOwner}{objectQualifier}DMX_Extensions.[Addon],
	{databaseOwner}{objectQualifier}DMX_Extensions.[ControlToLoad],
	{databaseOwner}{objectQualifier}DMX_Extensions.[Custom],
	{databaseOwner}{objectQualifier}DMX_Extensions.[DownloadUrl],
	{databaseOwner}{objectQualifier}DMX_Extensions.[EntryTypes],
	{databaseOwner}{objectQualifier}DMX_Extensions.[ExtensionKey],
	{databaseOwner}{objectQualifier}DMX_Extensions.[Icon16],
	{databaseOwner}{objectQualifier}DMX_Extensions.[Icon32],
	{databaseOwner}{objectQualifier}DMX_Extensions.[IsPrivate],
	{databaseOwner}{objectQualifier}DMX_Extensions.[MimeType],
	{databaseOwner}{objectQualifier}DMX_Extensions.[PortalId],
	{databaseOwner}{objectQualifier}DMX_Extensions.[ResourceFile],
	{databaseOwner}{objectQualifier}DMX_Extensions.[SettingsControl],
	{databaseOwner}{objectQualifier}DMX_Extensions.[ViewByDefault], 
	{databaseOwner}{objectQualifier}DMX_Addons.Description AS AddonsDescription, 
	{databaseOwner}{objectQualifier}Portals.Description AS PortalsDescription
FROM
	{databaseOwner}{objectQualifier}Portals INNER JOIN {databaseOwner}{objectQualifier}DMX_Addons INNER JOIN {databaseOwner}{objectQualifier}DMX_Extensions ON {databaseOwner}{objectQualifier}DMX_Addons.AddonKey = {databaseOwner}{objectQualifier}DMX_Extensions.Addon ON {databaseOwner}{objectQualifier}Portals.PortalID = {databaseOwner}{objectQualifier}DMX_Extensions.PortalId
WHERE
	{databaseOwner}{objectQualifier}DMX_Extensions.PortalId = @PortalId

GO

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

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


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

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

SELECT {objectQualifier}DMX_Attributes.*, {objectQualifier}DMX_EntryAttributes.id AS EntryAttributeId, {objectQualifier}DMX_EntryAttributes.Value
FROM {objectQualifier}DMX_Attributes LEFT OUTER JOIN {objectQualifier}DMX_EntryAttributes ON {objectQualifier}DMX_Attributes.id={objectQualifier}DMX_EntryAttributes.Attribute
 AND ({objectQualifier}DMX_EntryAttributes.EntryId=@EntryId)
 WHERE ({objectQualifier}DMX_Attributes.PortalId=@PortalId)
ORDER BY {objectQualifier}DMX_Attributes.ViewOrder

GO


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

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

SELECT {objectQualifier}DMX_Entries.Description+' ('+ISNULL(p.Description, 'Root')+')' AS Description, {objectQualifier}DMX_Entries.EntryId, {objectQualifier}DMX_Entries.CollectionId, {objectQualifier}DMX_Entries.EditRoles, {objectQualifier}DMX_Entries.ViewRoles, {objectQualifier}DMX_Extensions.ResourceFile, {objectQualifier}DMX_Extensions.Icon16
FROM ({objectQualifier}DMX_Entries LEFT JOIN {objectQualifier}DMX_Extensions ON {objectQualifier}DMX_Entries.EntryType={objectQualifier}DMX_Extensions.ExtensionKey) LEFT JOIN {objectQualifier}DMX_Entries AS p ON {objectQualifier}DMX_Entries.CollectionId=p.EntryId
WHERE (((LEFT({objectQualifier}DMX_Entries.EntryType,10)='Collection') Or (LEFT({objectQualifier}DMX_Entries.EntryType,17)='Custom.Collection')) And (({objectQualifier}DMX_Entries.Deleted)=0) And (({objectQualifier}DMX_Entries.PortalId)=@PortalId)
And (({objectQualifier}DMX_Extensions.PortalId)=@PortalId))
ORDER BY {objectQualifier}DMX_Entries.Description

GO

/****** INCREASE LENGTH OF [Custom] IN EXTENSIONS ******/

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_Extensions') and OBJECTPROPERTY(id, N'IsTable') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Extensions
ALTER COLUMN Custom NVARCHAR(400)

GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddExtension
	@PortalId Int,
	@Addon NVarChar (50),
	@ExtensionKey NVarChar (50),
	@AccessRights NVarChar (255),
	@ControlToLoad NVarChar (255),
	@Custom NVarChar (400),
	@DownloadUrl NVarChar (255),
	@EntryTypes NVarChar (255),
	@Icon16 NVarChar (100),
	@Icon32 NVarChar (100),
	@IsPrivate Bit,
	@MimeType NVarChar (50),
	@ResourceFile NVarChar (255),
	@SettingsControl NVarChar (250),
	@ViewByDefault Bit
AS

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

GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateExtension
	@PortalId Int,
	@Addon NVarChar (50),
	@ExtensionKey NVarChar (50),
	@AccessRights NVarChar (255),
	@ControlToLoad NVarChar (255),
	@Custom NVarChar (400),
	@DownloadUrl NVarChar (255),
	@EntryTypes NVarChar (255),
	@Icon16 NVarChar (100),
	@Icon32 NVarChar (100),
	@IsPrivate Bit,
	@MimeType NVarChar (50),
	@ResourceFile NVarChar (255),
	@SettingsControl NVarChar (250),
	@ViewByDefault Bit
AS

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

