/****** TABLES AND PRIMARY KEYS ******/

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

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

/****** DATA ******/
INSERT INTO {databaseOwner}{objectQualifier}DMX_Providers
 ([Provider],[ProviderName],[ProviderType])
VALUES
 ('Bring2mind.DNN.Modules.DMX.Services.Upload.AspnetUploadProvider.AspnetUploadProvider, BRING2MIND.DNN.MODULES.DMX','AspnetUploadProvider',3)
GO

INSERT INTO {databaseOwner}{objectQualifier}DMX_Providers
 ([Provider],[ProviderName],[ProviderType])
VALUES
 ('Bring2mind.DNN.Modules.DMX.Services.Upload.CAUploadProvider.CAUploadProvider, BRING2MIND.DNN.MODULES.DMX','CAUploadProvider',3)
GO

INSERT INTO {databaseOwner}{objectQualifier}DMX_Providers
 ([Provider],[ProviderName],[ProviderType])
VALUES
 ('Bring2mind.DNN.Modules.DMX.Services.Upload.RadUploadProvider.RadUploadProvider, BRING2MIND.DNN.MODULES.DMX','RadUploadProvider',3)
GO

/****** PROCEDURES ******/

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetSetting
	@PortalId INT,
	@SettingName NVARCHAR (50)
AS

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

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

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

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

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_SetSetting
	@PortalId INT,
	@SettingName NVARCHAR (50),
	@SettingValue NVARCHAR (2000)
AS

IF EXISTS (SELECT * FROM {databaseOwner}{objectQualifier}DMX_Settings WHERE [PortalId] = @PortalId	AND [SettingName] = @SettingName)
 UPDATE {databaseOwner}{objectQualifier}DMX_Settings SET
	 [SettingValue] = @SettingValue
 WHERE
	 [PortalId] = @PortalId
	 AND [SettingName] = @SettingName
ELSE
 INSERT INTO {databaseOwner}{objectQualifier}DMX_Settings (
	 [PortalId],
	 [SettingName],
	 SettingValue
 ) VALUES (
	 @PortalId,
	 @SettingName,
	 @SettingValue
 )

GO


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteSetting
	@PortalId INT,
	@SettingName NVARCHAR (50)
AS

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

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

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

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

/****** SysInfo Stuff ******/

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetPortalDetails
AS

SELECT 
 p.*, 
 (SELECT COUNT(*) FROM {objectQualifier}Users u INNER JOIN {objectQualifier}UserPortals up ON u.UserId=up.UserId WHERE up.PortalId=p.PortalId) AS NrUsers, 
 (SELECT COUNT(*) FROM {objectQualifier}Tabs t WHERE t.PortalID=p.PortalID) AS NrTabs 
FROM {objectQualifier}Portals p
 ORDER BY p.PortalId
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetModuleUsageList
AS

SELECT 
 dm.FolderName,
 dm.ModuleName,
 dm.FriendlyName,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}Modules m
   INNER JOIN {databaseOwner}{objectQualifier}ModuleDefinitions md ON m.ModuleDefID=md.ModuleDefID
   WHERE md.DesktopModuleID=dm.DesktopModuleID) AS NrInstances
FROM {databaseOwner}{objectQualifier}DesktopModules dm
 ORDER BY dm.FolderName
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DatabaseUsage
AS

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

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

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

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

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEventLog
AS

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


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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DMXInfo
AS

SELECT 
 p.PortalId, 
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Extensions ext WHERE ext.PortalId=p.PortalId) NrExtensions,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Permissions pe WHERE pe.PortalId=p.PortalId) NrPermissions,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_PermissionPermissions pp 
      INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions ppe ON pp.PermissionId=ppe.PermissionId WHERE ppe.PortalId=p.PortalId) NrPermissionPermissions,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_LogTypes lt WHERE lt.PortalId=p.PortalId) NrLogTypes,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Entries e WHERE e.PortalId=p.PortalId) NrEntries,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Attributes a WHERE a.PortalId=p.PortalId) NrAttributes,
 (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}DMX_Categories c WHERE c.PortalId=p.PortalId) NrCategories,
 (SELECT COUNT(e.EntryId) FROM {databaseOwner}{objectQualifier}DMX_Entries e
   INNER JOIN {databaseOwner}{objectQualifier}DMX_EntryPermissions ep ON e.EntryID=ep.EntryId
   INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON p.PermissionId=ep.PermissionId
  WHERE p.PermissionKey='APPROVE' AND e.PortalId=p.PortalId) NrApprovalItems
FROM {databaseOwner}{objectQualifier}Portals p
GO

/****** Other Procedures ******/

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

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

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

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

GO

/****** Inline editor changes ******/
ALTER TABLE {databaseOwner}{objectQualifier}DMX_Extensions ADD
 [EditControl] NVARCHAR (255) NULL,
 [ViewControl] NVARCHAR (255) NULL
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,	
 @ExtensionKey NVARCHAR (50),
 @AccessRights NVARCHAR (255),
 @Addon NVARCHAR (50),
 @ControlToLoad NVARCHAR (255),
 @Custom NVARCHAR (50),
 @DownloadUrl NVARCHAR (255),
 @EditControl NVARCHAR (255),
 @EntryTypes NVARCHAR (255),
 @Icon16 NVARCHAR (100),
 @Icon32 NVARCHAR (100),
 @IsPrivate BIT,
 @MimeType NVARCHAR (50),
 @NodePattern NVARCHAR (50),
 @ResourceFile NVARCHAR (255),
 @SettingsControl NVARCHAR (250),
 @ViewByDefault BIT,
 @ViewControl NVARCHAR (255)
AS

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

GO

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,
	@ExtensionKey NVARCHAR (50),
	@AccessRights NVARCHAR (255),
	@Addon NVARCHAR (50),
	@ControlToLoad NVARCHAR (255),
	@Custom NVARCHAR (50),
	@DownloadUrl NVARCHAR (255),
	@EditControl NVARCHAR (255),
	@EntryTypes NVARCHAR (255),
	@Icon16 NVARCHAR (100),
	@Icon32 NVARCHAR (100),
	@IsPrivate BIT,
	@MimeType NVARCHAR (50),
	@NodePattern NVARCHAR (50),
	@ResourceFile NVARCHAR (255),
	@SettingsControl NVARCHAR (250),
	@ViewByDefault BIT,
	@ViewControl NVARCHAR (255)
AS

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

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions 
SET [EditControl] = 'Bring2mind/DMX/Controls/Edit/File.Htm.ascx',
[ViewControl] = 'Bring2mind/DMX/Controls/View/File.Htm.ascx'
WHERE [ExtensionKey] = 'File.Htm'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions 
SET [EditControl] = 'Bring2mind/DMX/Controls/Edit/File.Text.ascx',
[ViewControl] = 'Bring2mind/DMX/Controls/View/File.Text.ascx'
WHERE [ExtensionKey] = 'File.Text'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions 
SET [ViewControl] = 'Bring2mind/DMX/Controls/View/File.Adobe.Acrobat.ascx'
WHERE [ExtensionKey] = 'File.Adobe.Acrobat'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions 
SET [ViewControl] = 'Bring2mind/DMX/Controls/View/File.Image.ascx'
WHERE [ExtensionKey] IN ('File.Jpg', 'File.Bmp', 'File.Gif', 'File.Png', 'File.Tiff')
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

DECLARE @Ents TABLE (s NVARCHAR(512))
INSERT INTO @Ents (s) SELECT s FROM {databaseOwner}{objectQualifier}DMX_Split(@EntryList,';')

SELECT
 1 AS Tag,
 NULL AS Parent,
 e.EntryId AS [Entry!1!EntryId!ID], 
 e.Author AS [Entry!1!Author],
 e.CollectionId AS [Entry!1!CollectionId],
 e.Created AS [Entry!1!Created],
 e.Deleted AS [Entry!1!Deleted],
 e.Entry AS [Entry!1!Entry],
 e.EntryType AS [Entry!1!EntryType],
 e.FileSize AS [Entry!1!FileSize],
 e.IsApproved AS [Entry!1!IsApproved],
 e.Keywords AS [Entry!1!Keywords],
 e.LastVersionId AS [Entry!1!LastVersionId],
 e.LockedBy AS [Entry!1!LockedBy],
 e.OriginalFileName AS [Entry!1!OriginalFileName],
 e.PortalId AS [Entry!1!PortalId],
 e.Owner AS [Entry!1!Owner],
 e.Version AS [Entry!1!Version],
 e.LastModified AS [Entry!1!LastModified],
 e.LockedUntil AS [Entry!1!LockedUntil],
 e.LockKey AS [Entry!1!LockKey],
 e.MD5Hash AS [Entry!1!MD5Hash],
 e.State AS [Entry!1!State],
 e.VersionsToKeep AS [Entry!1!VersionsToKeep],
 e.WorkflowId AS [Entry!1!WorkflowId],
 ext.EditControl AS [Entry!1!EditControl],
 ext.ViewControl AS [Entry!1!ViewControl],
 REPLACE({databaseOwner}{objectQualifier}DMXDAV_URLEncode({databaseOwner}{objectQualifier}DMXDAV_GetPath(e.EntryId, e.PortalId, '')), '''', '\''') AS [Entry!1!FullPath],
 CAST (CASE WHEN e.LockedUntil > GETDATE() THEN 1 ELSE 0 END AS BIT) AS [Entry!1!IsLocked],
 CAST (CASE WHEN subs.EntryID IS NULL THEN 0 ELSE 1 END AS BIT) AS [Entry!1!Subscription],
 NULL AS [Permission!2!PermissionKey]
FROM
 @Ents ents
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON ents.s=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Extensions ext ON ext.ExtensionKey=e.EntryType AND e.PortalId=ext.PortalId
 LEFT JOIN (SELECT * FROM {databaseOwner}{objectQualifier}DMX_Subscriptions WHERE UserID=@UserId) subs ON subs.EntryId=e.EntryId

UNION ALL

SELECT DISTINCT
 2,
 1,
 e.EntryId, 
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 p.PermissionKey AS [Permission!2!PermissionKey]
FROM
 @Ents ents
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON ents.s=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_EntryPermissions ep ON ep.EntryId=e.EntryId
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p ON p.PermissionId=ep.PermissionId
 LEFT JOIN {databaseOwner}{objectQualifier}vw_DMX_ActiveUserRoles r ON r.RoleId=ep.RoleId AND r.UserId=@UserId
WHERE
 (ep.UserId=@UserId OR NOT r.UserId IS NULL OR ep.RoleId=-1)

FOR XML EXPLICIT

GO

/****** Sprite support ******/
UPDATE {databaseOwner}{objectQualifier}DMX_Extensions 
SET Icon16 = REPLACE(REPLACE(Icon16, 'DesktopModules/Bring2mind/DMX/images/', ''), '.', '-')
WHERE Icon16 LIKE 'DesktopModules/Bring2mind/DMX/images/%'
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions 
SET Icon32 = REPLACE(REPLACE(Icon32, 'DesktopModules/Bring2mind/DMX/images/', ''), '.', '-')
WHERE Icon32 LIKE 'DesktopModules/Bring2mind/DMX/images/%'
GO

UPDATE {databaseOwner}{objectQualifier}ModuleControls 
SET IconFile = REPLACE(IconFile, '/icon32/', '/32/')
WHERE IconFile LIKE '~/DesktopModules/Bring2mind/DMX/images/icon32/%'
GO

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

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

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

/****** Paging addition for GetEntriesByPortal ******/
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),
 @PageSize INT = 0,
 @StartRecord INT = 1
AS

SELECT *
FROM
(SELECT
 ROW_NUMBER() OVER (ORDER BY EntryId) AS rownum,
 e.*,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_ShortTexts WHERE TypeKey='ENT' AND SubTypeKey='TITLE' AND ObjectId=e.EntryId AND Locale='')) Title,
 ISNULL((SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale=@Locale),
  (SELECT [Text] FROM {databaseOwner}{objectQualifier}DMX_LongTexts WHERE TypeKey='ENT' AND SubTypeKey='REMARKS' AND ObjectId=e.EntryId AND Locale='')) Remarks
FROM
 {databaseOwner}{objectQualifier}DMX_Entries e
WHERE
 e.PortalId = @PortalId
 AND ((NOT e.Deleted = 1) OR (@IncludeDeleted = 1))) AS tmp
WHERE (@PageSize=0 OR (rownum >= @StartRecord AND rownum < @StartRecord + @PageSize))

GO

