/****** Repair for lost icons in Sprite support ******/
UPDATE {databaseOwner}{objectQualifier}DMX_Extensions 
SET Icon16 = 'DesktopModules/Bring2mind/DMX/images/' + REPLACE(Icon16, '-', '.')
WHERE 
 NOT Icon16 IN ('collection-gif','collection-syncfolder-gif','file-adobe-acrobat-gif','file-adobe-indesign-gif','file-adobe-photoshop-gif','file-bmp-gif','file-avi-gif','file-dwg-gif','file-dxf-gif','file-emf-gif','file-eps-gif','file-gif-gif','file-htm-gif','file-jpg-gif','file-microsoft-excel-gif','file-microsoft-excelx-gif','file-microsoft-infopath-gif','file-microsoft-powerpoint-gif','file-microsoft-powerpointx-gif','file-microsoft-word-gif','file-microsoft-wordx-gif','file-png-gif','file-mpeg-gif','file-quicktime-gif','file-rar-gif','file-rtf-gif','file-svg-gif','file-targa-gif','file-text-gif','file-tiff-gif','file-ttf-gif','file-unknown-gif','file-wav-gif','file-wma-gif','file-wmf-gif','file-wmv-gif','file-xml-gif','file-zip-gif','hyperlink-gif')
 AND CHARINDEX('/', Icon16)=0
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions 
SET Icon32 = 'DesktopModules/Bring2mind/DMX/images/' + REPLACE(Icon32, '-', '.')
WHERE 
 NOT Icon32 IN ('collection-big-gif','collection-syncfolder-big-gif','file-adobe-acrobat-big-gif','file-adobe-indesign-big-gif','file-adobe-photoshop-big-gif','file-bmp-big-gif','file-avi-big-gif','file-dwg-big-gif','file-dxf-big-gif','file-emf-big-gif','file-eps-big-gif','file-gif-big-gif','file-htm-big-gif','file-jpg-big-gif','file-microsoft-excel-big-gif','file-microsoft-excelx-big-gif','file-microsoft-infopath-big-gif','file-microsoft-powerpoint-big-gif','file-microsoft-powerpointx-big-gif','file-microsoft-word-big-gif','file-microsoft-wordx-big-gif','file-mpeg-big-gif','file-png-big-gif','file-quicktime-big-gif','file-rar-big-gif','file-rtf-big-gif','file-svg-big-gif','file-targa-big-gif','file-text-big-gif','file-tiff-big-gif','file-ttf-big-gif','file-unknown-big-gif','file-wav-big-gif','file-wma-big-gif','file-wmf-big-gif','file-wmv-big-gif','file-xml-big-gif','file-zip-big-gif','hyperlink-big-gif')
 AND CHARINDEX('/', Icon32)=0
GO

/****** Repair for nulls in extensions ******/
UPDATE {databaseOwner}{objectQualifier}DMX_Extensions
SET IsPrivate=0
WHERE IsPrivate IS NULL
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions
SET ViewByDefault=0
WHERE ViewByDefault IS NULL
GO

/****** Remove user subscriptions for users that have been deleted from a portal ******/
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_RemoveOldUserSubscriptions') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_RemoveOldUserSubscriptions
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_RemoveOldUserSubscriptions
AS

-- DNN 5+
BEGIN TRY
 EXEC('DELETE FROM {databaseOwner}{objectQualifier}DMX_Subscriptions
FROM {databaseOwner}{objectQualifier}DMX_Subscriptions s
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON s.EntryId=e.EntryId
WHERE
 NOT EXISTS (SELECT up.UserId FROM {databaseOwner}{objectQualifier}UserPortals up WHERE up.PortalId=e.PortalId AND up.UserId=s.UserId AND up.Authorised=1 AND up.IsDeleted=0)
 OR NOT EXISTS (SELECT u.UserId FROM {databaseOwner}{objectQualifier}Users u WHERE u.UserId=s.UserId)');
END TRY
BEGIN CATCH
END CATCH;
GO

-- DNN 4
DELETE FROM {databaseOwner}{objectQualifier}DMX_Subscriptions
FROM {databaseOwner}{objectQualifier}DMX_Subscriptions s
 INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e ON s.EntryId=e.EntryId
WHERE
 NOT EXISTS (SELECT up.UserId FROM {databaseOwner}{objectQualifier}UserPortals up WHERE up.PortalId=e.PortalId AND up.UserId=s.UserId)
 OR NOT EXISTS (SELECT u.UserId FROM {databaseOwner}{objectQualifier}Users u WHERE u.UserId=s.UserId)
GO

/****** Fix length of [Values] field ******/
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 (2000),
	@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
);
UPDATE {databaseOwner}{objectQualifier}DMX_Attributes
SET [Key]='att'+CAST(AttributeId AS NVARCHAR(10))
WHERE ISNULL([Key], '')='';

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 (2000),
	@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

