ÿþ/*

Run this script on the database containing version 2.0.2830 of Gallery Server Pro.

This script provides bug fixes to four stored procedures and adds one user defined function.

*/

GO

SET NUMERIC_ROUNDABORT OFF

GO

SET ANSI_PADDING ON

GO

SET ANSI_WARNINGS ON

GO

SET CONCAT_NULL_YIELDS_NULL ON

GO

SET ARITHABORT ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors

GO

CREATE TABLE #tmpErrors (Error int)

GO

SET XACT_ABORT ON

GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

GO

BEGIN TRANSACTION

GO

PRINT N'Altering [dbo].[gs_SynchronizeSave]'

GO

ALTER PROCEDURE [dbo].[gs_SynchronizeSave]

	(@SynchID nchar(50), @GalleryId int, @SynchState int, @TotalFiles int, @CurrentFileIndex int)

AS

SET NOCOUNT, XACT_ABORT ON

/* UPDATE the synchronize table with the specified data. */



/* Check if another synchronization is in progress. Return with error code if it is. */

IF EXISTS(SELECT * FROM [gs_Synchronize] WHERE FKGalleryID = @GalleryId AND SynchID <> @SynchID AND (SynchState = 1 OR SynchState = 2))

BEGIN

	RETURN 250000

END



BEGIN TRAN

IF EXISTS (SELECT * FROM [gs_Synchronize] WITH (UPDLOCK, HOLDLOCK) WHERE FKGalleryID = @GalleryId)

	UPDATE [gs_Synchronize]

	SET SynchID = @SynchID,

		FKGalleryId = @GalleryId,

		SynchState = @SynchState,

		TotalFiles = @TotalFiles,

		CurrentFileIndex = @CurrentFileIndex

	WHERE FKGalleryID = @GalleryId

ELSE

	INSERT [gs_Synchronize] (SynchID, FKGalleryID, SynchState, TotalFiles, CurrentFileIndex)

	VALUES (@SynchID, @GalleryId, @SynchState, @TotalFiles, @CurrentFileIndex)

COMMIT



RETURN

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Altering [dbo].[gs_VerifyMinimumRecords]'

GO

ALTER PROCEDURE [dbo].[gs_VerifyMinimumRecords]

(

	@GalleryID int

)

AS

SET NOCOUNT, XACT_ABORT ON

/* Verify that certain tables have the required records, inserting them if necessary. This proc is designed 

to be run each time the application starts. This proc is especially important the first time Gallery Server 

is run as Gallery Server depends on this proc to install the default set of data.



Gallery: This table requires at least one record to represent the current gallery. If no records are

			found, one is inserted with a ID equal to the @GalleryID parameter.

Album: This table requires at least one record to represent the root album. If no records are found, 

       a new record representing the root album is added.

Synchronize: This table requires one record for each gallery. It stores the current state of a synchronization,

			if one is in progress. When a synchronization is not in progress, the SynchState field should be zero

			for this gallery.

*/



/* ******************************************************* */

--          Check the Gallery table

/* ******************************************************* */

BEGIN TRAN

IF NOT EXISTS (SELECT * FROM [gs_Gallery] WITH (UPDLOCK, HOLDLOCK) WHERE GalleryID = @GalleryID)

BEGIN

	INSERT gs_Gallery (GalleryId, Description)

	VALUES (@GalleryId, 'My Gallery')

END

COMMIT



/* ******************************************************* */

--          Check the Album table

/* ******************************************************* */

BEGIN TRAN

IF NOT EXISTS (SELECT * FROM [gs_Album] WITH (UPDLOCK, HOLDLOCK) WHERE AlbumParentID = 0 AND FKGalleryID = @GalleryID)

BEGIN

  /* The album table does not have a root album for the specified gallery. Insert one. */

  INSERT [gs_Album] (AlbumParentID, FKGalleryId, Title, DirectoryName, Summary, ThumbnailMediaObjectID, Seq, CreatedBy, DateAdded, LastModifiedBy, DateLastModified, OwnedBy, IsPrivate)

  VALUES (0, @GalleryID, 'All albums', '','Welcome to Gallery Server Pro!', 0, 0, 'gs_VerifyMinimumRecords', GETDATE(), 'gs_VerifyMinimumRecords', GETDATE(), 'gs_VerifyMinimumRecords', 0)

END 

COMMIT



/* ******************************************************* */

--          Check the Synchronize table

/* ******************************************************* */

BEGIN TRAN

IF EXISTS (SELECT * FROM [gs_Synchronize] WITH (UPDLOCK, HOLDLOCK) WHERE FKGalleryID = @GalleryID)

BEGIN -- Reset record to clear out any previous synchronization

	UPDATE gs_Synchronize

	SET SynchID = '',

	SynchState = 0,

	TotalFiles = 0,

	CurrentFileIndex = 0

	WHERE FKGalleryID = @GalleryId

END

ELSE

BEGIN

	INSERT INTO gs_Synchronize (SynchID, FKGalleryID, SynchState, TotalFiles, CurrentFileIndex)

	VALUES ('',@GalleryId, 0, 0, 0)

END

COMMIT



RETURN

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Altering [dbo].[gs_RoleUpdate]'

GO

ALTER PROCEDURE [dbo].[gs_RoleUpdate]

(

	@GalleryId int, @RoleName nvarchar(256), @AllowViewAlbumsAndObjects bit, @AllowViewOriginalImage bit,

	@AllowAddChildAlbum bit, @AllowAddMediaObject bit, @AllowEditAlbum bit, @AllowEditMediaObject bit,

	@AllowDeleteChildAlbum bit, @AllowDeleteMediaObject bit, @AllowSynchronize bit, @HideWatermark bit,

	@AllowAdministerSite bit

)

AS

SET NOCOUNT, XACT_ABORT ON



/* Update the specified role. If the role does not exist, assume it is new and call the insert proc. */

BEGIN TRAN

IF EXISTS (SELECT * FROM [gs_Role] WITH (UPDLOCK, HOLDLOCK) WHERE FKGalleryId = @GalleryId AND RoleName = @RoleName)

BEGIN

	UPDATE [gs_Role]

	SET AllowViewAlbumsAndObjects = @AllowViewAlbumsAndObjects,

		AllowViewOriginalImage = @AllowViewOriginalImage,

		AllowAddChildAlbum = @AllowAddChildAlbum,

		AllowAddMediaObject = @AllowAddMediaObject,

		AllowEditAlbum = @AllowEditAlbum,

		AllowEditMediaObject = @AllowEditMediaObject,

		AllowDeleteChildAlbum = @AllowDeleteChildAlbum,

		AllowDeleteMediaObject = @AllowDeleteMediaObject,

		AllowSynchronize = @AllowSynchronize,

		HideWatermark = @HideWatermark, 

		AllowAdministerSite = @AllowAdministerSite

	WHERE FKGalleryId = @GalleryId AND RoleName = @RoleName

END

ELSE

BEGIN

	EXECUTE [dbo].[gs_RoleInsert] 

		@GalleryId,

		@RoleName,

		@AllowViewAlbumsAndObjects,

		@AllowViewOriginalImage,

		@AllowAddChildAlbum,

		@AllowAddMediaObject,

		@AllowEditAlbum,

		@AllowEditMediaObject,

		@AllowDeleteChildAlbum,

		@AllowDeleteMediaObject,

		@AllowSynchronize,

		@HideWatermark,

		@AllowAdministerSite

END

COMMIT



RETURN

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Altering [dbo].[gs_InitializeGalleryServerPro]'

GO

ALTER PROCEDURE [dbo].[gs_InitializeGalleryServerPro]

(

	@GalleryID int,

	@ApplicationName nvarchar(512),

	@SysAdminRoleName nvarchar(256),

	@SysAdminRoleNameDescription nvarchar(256),

	@AdminEmail nvarchar(256),

	@AdminUserName nvarchar(256),

	@AdminPassword nvarchar(256),

	@PasswordFormat int = 0,	-- 0 = clear text, 1 = encrypted

	@CreateSamples bit = 1

)

AS

SET NOCOUNT ON



/* This procedure is called from the web installer. It sets up the administrator user and ensures 

certain tables have default records. */



/* ******************************************************* */

--     Verify certain tables have default records

/* ******************************************************* */

EXEC dbo.gs_VerifyMinimumRecords @GalleryID



/* ******************************************************* */

--                  Membership Routines

/* ******************************************************* */

DECLARE @ApplicationID uniqueidentifier

DECLARE @ApplicationAlreadyExists bit

DECLARE @RootAlbumId int

DECLARE @AdminUserId uniqueidentifier



IF (NOT EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Applications') AND (type = 'U')))

	RETURN -- No Membership tables in this database. User presumably wants to connect to a Membership infrastructure in 

				 -- a remote database. This must be done manually so let's just exit.

	

-- Get root album ID

SELECT @RootAlbumId = AlbumID FROM gs_Album WHERE (AlbumParentID = 0)



--Get or Create the Application ID.

--An ApplicationName/ID can exist in more than one site, so it does not need to be unique

Set @ApplicationID = null



Select @ApplicationID = ApplicationId FROM aspnet_Applications where  LoweredApplicationName = Lower(@ApplicationName)





IF(@ApplicationID is  null)

BEGIN /* Application doesn't exist so let's create it */

	EXEC aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId = @ApplicationID OUTPUT

	Set @ApplicationAlreadyExists = 0

END

ELSE

	Set @ApplicationAlreadyExists = 1



/* ******************************************************* */

/* If Gallery Server Pro app doesn't exist, create sys admin 

	 role and admin user; otherwise create the role and user

	 only if they don't already exist. */

/* ******************************************************* */

IF(@ApplicationAlreadyExists = 0)

BEGIN

	/* ******************************************************* */

	--			Gallery Server Pro app exists

	--     Create system administrator role

	/* ******************************************************* */

	INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description])

	VALUES (newID(), @ApplicationID, @SysAdminRoleName, Lower(@SysAdminRoleName), @SysAdminRoleNameDescription)	



	/* ******************************************************* */

	--                     Create admin user

	/* ******************************************************* */

	Set @AdminUserId = newid()



	INSERT INTO [aspnet_Users] ([UserId], [ApplicationId], [UserName], [LoweredUserName], [MobileAlias], [IsAnonymous], [LastActivityDate]) 

		VALUES (@AdminUserId, @ApplicationID, @AdminUserName, Lower(@AdminUserName), NULL, 0, getdate())



	INSERT INTO [aspnet_Membership] ([ApplicationId], [UserId], [Password], [PasswordFormat], [PasswordSalt], [MobilePIN], [Email], [LoweredEmail], [PasswordQuestion], [PasswordAnswer], [IsApproved], [CreateDate], [LastLoginDate], [LastPasswordChangedDate], [Comment], FailedPasswordAnswerAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAttemptCount, LastLockoutDate, IsLockedOut) 

		VALUES (@ApplicationID, @AdminUserId,  @AdminPassword, @PasswordFormat,  N'DVZTktxeMzDtXR7eik7Cdw==', NULL, @AdminEmail,    NULL,            NULL,               NULL,            1,            getdate(),    getdate(),       getdate(),                 NULL,                 '1753-01-01',                              0,                               '1753-01-01',                       0,                          '1753-01-01',      0)



	/* ******************************************************* */

	-- Add new admin user to all roles for this application, which 

	-- will probably just be the system admin role we created.

	/* ******************************************************* */

	INSERT INTO aspnet_UsersInRoles (UserId, RoleId) 

	SELECT @AdminUserId, RoleId FROM aspnet_Roles WHERE ApplicationId = @ApplicationID



	/* ******************************************************* */

	-- Create gallery server role that applies to root album.

	/* ******************************************************* */

	INSERT INTO [gs_Role] (FKGalleryId, RoleName, AllowViewAlbumsAndObjects, AllowViewOriginalImage, AllowAddChildAlbum,

		AllowAddMediaObject, AllowEditAlbum, AllowEditMediaObject, AllowDeleteChildAlbum, AllowDeleteMediaObject, 

		AllowSynchronize, HideWatermark, AllowAdministerSite)

	VALUES (@GalleryID, @SysAdminRoleName, 1, 1, 1,	1, 1, 1, 1, 1, 1, 0, 1)

	

	INSERT INTO [gs_Role_Album] (FKRoleName, FKAlbumId)

	VALUES (@SysAdminRoleName, @RootAlbumId)

END

ELSE -- Gallery Server Pro app already exists (@ApplicationAlreadyExists = 1)

BEGIN

	-- Does admin user exist for this application? Create it if not. 

	Set @AdminUserId = NULL

	SELECT @AdminUserId = UserId FROM aspnet_Users WHERE (UserName = @AdminUserName) AND (ApplicationId = @ApplicationID)

	

	IF @AdminUserId = NULL

	BEGIN -- Admin user does not exist. Create it.

		Set @AdminUserId = newid()



		INSERT INTO [aspnet_Users] ([UserId], [ApplicationId], [UserName], [LoweredUserName], [MobileAlias], [IsAnonymous], [LastActivityDate]) 

			VALUES (@AdminUserId, @ApplicationID, @AdminUserName, Lower(@AdminUserName), NULL, 0, getdate())



		INSERT INTO [aspnet_Membership] ([ApplicationId], [UserId], [Password],     [PasswordFormat], [PasswordSalt],              [MobilePIN], [Email], [LoweredEmail], [PasswordQuestion], [PasswordAnswer], [IsApproved], [CreateDate], [LastLoginDate], [LastPasswordChangedDate], [Comment], FailedPasswordAnswerAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAttemptCount, LastLockoutDate, IsLockedOut) 

			VALUES (@ApplicationID, @AdminUserId,  @AdminPassword, @PasswordFormat,   N'DVZTktxeMzDtXR7eik7Cdw==', NULL,       @AdminEmail,    NULL,            NULL,               NULL,            1,            getdate(),    getdate(),       getdate(),                 NULL,                 '1753-01-01',                              0,                               '1753-01-01',                       0,                          '1753-01-01',      0)

	END

	ELSE

	BEGIN -- Admin user exists. Make sure password matches the one passed to this procedure

		UPDATE aspnet_Membership

		SET Password = @AdminPassword, PasswordFormat = @PasswordFormat

		WHERE UserId = @AdminUserId

	END



	-- Does sys admin role exist for this application? Create it if not.

	IF NOT EXISTS(SELECT * FROM [aspnet_Roles] WHERE RoleName = @SysAdminRoleName AND ApplicationId = @ApplicationID)

		INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description])

			VALUES (newID(), @ApplicationID, @SysAdminRoleName, Lower(@SysAdminRoleName), @SysAdminRoleNameDescription)	

		 

	-- Make sure admin user is part of sys admin role.

	IF NOT EXISTS(SELECT aspnet_UsersInRoles.*

								FROM aspnet_UsersInRoles INNER JOIN aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId

								WHERE aspnet_UsersInRoles.UserId = @AdminUserId AND aspnet_Roles.RoleName = @SysAdminRoleName AND aspnet_Roles.ApplicationId = @ApplicationID)

		INSERT INTO aspnet_UsersInRoles (UserId, RoleId) 

		SELECT @AdminUserId, RoleId FROM aspnet_Roles WHERE RoleName = @SysAdminRoleName AND ApplicationId = @ApplicationID



	-- Make sure sys admin role is a gallery server role assigned to the root album.

	IF NOT EXISTS(SELECT * FROM [gs_Role] WHERE RoleName = @SysAdminRoleName AND FKGalleryId = @GalleryID)

		INSERT INTO [gs_Role] (FKGalleryId, RoleName, AllowViewAlbumsAndObjects, AllowViewOriginalImage, AllowAddChildAlbum,

			AllowAddMediaObject, AllowEditAlbum, AllowEditMediaObject, AllowDeleteChildAlbum, AllowDeleteMediaObject, 

			AllowSynchronize, HideWatermark, AllowAdministerSite)

		VALUES (@GalleryID, @SysAdminRoleName, 1, 1, 1,	1, 1, 1, 1, 1, 1, 0, 1)

	

	IF NOT EXISTS(SELECT * FROM [gs_Role_Album] WHERE FKRoleName = @SysAdminRoleName)

		INSERT INTO [gs_Role_Album] (FKRoleName, FKAlbumId)

		VALUES (@SysAdminRoleName, @RootAlbumId)

END	



IF @CreateSamples = 1

BEGIN

	DECLARE @newAlbumId int

	DECLARE @newMediaObjectId int

	

	/* Create an album called Samples. */

	INSERT [gs_Album] (AlbumParentID, FKGalleryID, Title, DirectoryName, Summary, ThumbnailMediaObjectID, Seq, DateStart, DateEnd, CreatedBy, DateAdded, LastModifiedBy, DateLastModified, OwnedBy, IsPrivate)

	VALUES (@RootAlbumId, @GalleryID, 'Samples', 'Samples', 'Death Valley', 0, 1, GETDATE(), DATEADD(week, 2, GETDATE()), @AdminUserName, GETDATE(), @AdminUserName, GETDATE(), @AdminUserName, 0)



	SET @newAlbumId = SCOPE_IDENTITY()

	

	INSERT [gs_MediaObject] (HashKey, FKAlbumID, ThumbnailFilename, ThumbnailWidth, ThumbnailHeight,

	 ThumbnailSizeKB, OptimizedFilename, OptimizedWidth, OptimizedHeight, OptimizedSizeKB,

	 OriginalFilename, OriginalWidth, OriginalHeight, OriginalSizeKB, Title, Seq, CreatedBy, 

	 DateAdded, LastModifiedBy, DateLastModified, IsPrivate)

	VALUES ('4C-A8-D6-E6-88-29-28-BE-3A-79-EF-0B-F5-9B-2D-EE', @newAlbumId, 'zThumb_DeathValley.jpeg', 115, 86,

	 2, 'DeathValley.jpeg', 640, 480, 48, 'DeathValley.jpeg', 640, 480, 48, 'Death Valley, California', 1, @AdminUserName,

	 GETDATE(), @AdminUserName, GETDATE(), 0)

 

	SET @newMediaObjectId = SCOPE_IDENTITY()

	

	/* Set this new image as the thumbnail for the album */

	UPDATE [gs_Album] SET ThumbnailMediaObjectID = @newMediaObjectId WHERE AlbumID = @newAlbumId

END

RETURN

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Creating [dbo].[gs_GetVersion]'

/****** Object:  UserDefinedFunction [dbo].[gs_GetVersion]    Script Date: 11/24/2007 ******/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[gs_GetVersion]') AND xtype in (N'FN', N'IF', N'TF'))

DROP FUNCTION [dbo].[gs_GetVersion]

GO

CREATE FUNCTION [dbo].[gs_GetVersion]()

RETURNS

   VARCHAR(255)

AS

BEGIN

   RETURN '2.0.2886'

END

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION

GO

PRINT N'Configuring permission for user defined function [dbo].[gs_GetVersion]'

GRANT EXECUTE ON [dbo].[gs_GetVersion] TO [gs_GalleryServerProRole]

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT>0 BEGIN

PRINT 'The database update succeeded.'

COMMIT TRANSACTION

END

ELSE PRINT 'The database update failed.'

GO

DROP TABLE #tmpErrors

GO