ALTER TABLE {databaseOwner}[{objectQualifier}DMX_Attributes] DROP
CONSTRAINT [FK_{objectQualifier}DMX_Attributes_{objectQualifier}DMX_Addons]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'FK_{objectQualifier}DMX_Attributes_{objectQualifier}DMX_') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Attributes ADD CONSTRAINT
	FK_{objectQualifier}DMX_Attributes_{objectQualifier}DMX_Addons FOREIGN KEY
	(
    Addon
	) REFERENCES {databaseOwner}{objectQualifier}DMX_Addons
	(
    AddonKey
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

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

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


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

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

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

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

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

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

GO

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

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

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

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

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

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

GO

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

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

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

GO

