ÿþ/*

Run this script on the database containing the Beta 1 version (v2.0.2768) of Gallery Server Pro 2.0.

*/

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'Dropping foreign keys from [dbo].[gs_Album]'

GO

ALTER TABLE [dbo].[gs_Album] DROP

CONSTRAINT [FK_gs_Album_gs_Album],

CONSTRAINT [FK_gs_Album_gs_Gallery]

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'Dropping foreign keys from [dbo].[gs_Role_Album]'

GO

ALTER TABLE [dbo].[gs_Role_Album] DROP

CONSTRAINT [FK_gs_Role_Album_gs_Album]

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'Dropping foreign keys from [dbo].[gs_MediaObject]'

GO

-- Need an 'if' because the beta 1 SQL 2005 script did not add foreign key

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_gs_MediaObject_gs_Album]') AND type = 'F')

	ALTER TABLE [dbo].[gs_MediaObject] DROP

	CONSTRAINT [FK_gs_MediaObject_gs_Album]

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'Dropping foreign keys from [dbo].[gs_MediaObjectMetadata]'

GO

ALTER TABLE [dbo].[gs_MediaObjectMetadata] DROP

CONSTRAINT [FK_gs_MediaObjectMetadata_gs_MediaObject]

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'Dropping constraints from [dbo].[gs_Album]'

GO

ALTER TABLE [dbo].[gs_Album] DROP CONSTRAINT [PK_gs_Album]

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'Dropping constraints from [dbo].[gs_Album]'

GO

ALTER TABLE [dbo].[gs_Album] DROP CONSTRAINT [DF_gs_Album_Title]

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'Dropping constraints from [dbo].[gs_Album]'

GO

ALTER TABLE [dbo].[gs_Album] DROP CONSTRAINT [DF_gs_Album_DirectoryName]

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'Dropping constraints from [dbo].[gs_Album]'

GO

ALTER TABLE [dbo].[gs_Album] DROP CONSTRAINT [DF_gs_Album_Summary]

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'Dropping constraints from [dbo].[gs_Album]'

GO

ALTER TABLE [dbo].[gs_Album] DROP CONSTRAINT [DF_gs_Album_ThumbnailMediaObjectID]

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'Dropping constraints from [dbo].[gs_MediaObject]'

GO

ALTER TABLE [dbo].[gs_MediaObject] DROP CONSTRAINT [PK_gs_MediaObject]

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'Dropping constraints from [dbo].[gs_MediaObject]'

GO

ALTER TABLE [dbo].[gs_MediaObject] DROP CONSTRAINT [DF_gs_MediaObject_Title]

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'Dropping constraints from [dbo].[gs_MediaObject]'

GO

ALTER TABLE [dbo].[gs_MediaObject] DROP CONSTRAINT [DF_gs_MediaObject_HashKey]

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'Dropping constraints from [dbo].[gs_MediaObject]'

GO

ALTER TABLE [dbo].[gs_MediaObject] DROP CONSTRAINT [DF_gs_MediaObject_ThumbnailFilename]

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'Dropping constraints from [dbo].[gs_MediaObject]'

GO

ALTER TABLE [dbo].[gs_MediaObject] DROP CONSTRAINT [DF_gs_MediaObject_ThumbnailWidth]

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'Dropping constraints from [dbo].[gs_MediaObject]'

GO

ALTER TABLE [dbo].[gs_MediaObject] DROP CONSTRAINT [DF_gs_MediaObject_ThumbnailHeight]

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'Dropping constraints from [dbo].[gs_MediaObject]'

GO

ALTER TABLE [dbo].[gs_MediaObject] DROP CONSTRAINT [DF_gs_MediaObject_OptimizedFilename]

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'Dropping constraints from [dbo].[gs_MediaObject]'

GO

ALTER TABLE [dbo].[gs_MediaObject] DROP CONSTRAINT [DF_gs_MediaObject_OptimizedWidth]

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'Dropping constraints from [dbo].[gs_MediaObject]'

GO

ALTER TABLE [dbo].[gs_MediaObject] DROP CONSTRAINT [DF_gs_MediaObject_OptimizedHeight]

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'Dropping constraints from [dbo].[gs_MediaObject]'

GO

ALTER TABLE [dbo].[gs_MediaObject] DROP CONSTRAINT [DF_gs_MediaObject_OriginalFilename]

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'Dropping constraints from [dbo].[gs_MediaObject]'

GO

ALTER TABLE [dbo].[gs_MediaObject] DROP CONSTRAINT [DF_gs_MediaObject_OriginalWidth]

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'Dropping constraints from [dbo].[gs_MediaObject]'

GO

ALTER TABLE [dbo].[gs_MediaObject] DROP CONSTRAINT [DF_gs_MediaObject_OriginalHeight]

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'Dropping constraints from [dbo].[gs_MediaObject]'

GO

ALTER TABLE [dbo].[gs_MediaObject] DROP CONSTRAINT [DF_gs_MediaObject_FilenameHasChanged]

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'Dropping index [ForeignKeyIndex] from [dbo].[gs_MediaObject]'

GO

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'ForeignKeyIndex')

	DROP INDEX [dbo].[gs_MediaObject].[ForeignKeyIndex]

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'Rebuilding [dbo].[gs_Album]'

GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SET XACT_ABORT ON

GO

BEGIN TRANSACTION

CREATE TABLE [dbo].[tmp_ms_xx_gs_Album]

(

[AlbumID] [int] NOT NULL IDENTITY(1, 1),

[FKGalleryID] [int] NOT NULL,

[AlbumParentID] [int] NOT NULL,

[Title] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_gs_Album_Title] DEFAULT (''),

[DirectoryName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_gs_Album_DirectoryName] DEFAULT (''),

[Summary] [nvarchar] (1500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_gs_Album_Summary] DEFAULT (''),

[ThumbnailMediaObjectID] [int] NOT NULL CONSTRAINT [DF_gs_Album_ThumbnailMediaObjectID] DEFAULT ((0)),

[Seq] [int] NOT NULL,

[DateStart] [datetime] NULL,

[DateEnd] [datetime] NULL,

[DateAdded] [datetime] NOT NULL,

[CreatedBy] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[LastModifiedBy] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[DateLastModified] [datetime] NOT NULL,

[OwnedBy] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[IsPrivate] [bit] NOT NULL CONSTRAINT [DF_gs_Album_IsPrivate] DEFAULT ((0))

) ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[tmp_ms_xx_gs_Album] ON



INSERT INTO [dbo].[tmp_ms_xx_gs_Album]([AlbumID], FKGalleryID, AlbumParentID, Title, DirectoryName, 

 Summary, ThumbnailMediaObjectID, Seq, DateStart, DateEnd, 

 CreatedBy, DateAdded, LastModifiedBy, DateLastModified, OwnedBy, 

 IsPrivate)

SELECT [AlbumID], [FKGalleryID], [AlbumParentID], [Title], [DirectoryName],

 [Summary], [ThumbnailMediaObjectID], [Seq], [DateStart], [DateEnd], 

 'System', [DateAdded], 'System', GETDATE(), 'System', 0

FROM [dbo].[gs_Album] 



SET IDENTITY_INSERT [dbo].[tmp_ms_xx_gs_Album] OFF

DROP TABLE [dbo].[gs_Album]

EXEC sp_rename N'[dbo].[tmp_ms_xx_gs_Album]', N'gs_Album'

COMMIT TRANSACTION

GO

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

GO

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

GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

GO

PRINT N'Creating primary key [PK_gs_Album] on [dbo].[gs_Album]'

GO

ALTER TABLE [dbo].[gs_Album] ADD CONSTRAINT [PK_gs_Album] PRIMARY KEY CLUSTERED  ([AlbumID]) ON [PRIMARY]

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 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

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

IF NOT EXISTS (SELECT * FROM [gs_Gallery] WHERE GalleryID = @GalleryID)

BEGIN

	INSERT gs_Gallery (GalleryId, Description)

	VALUES (@GalleryId, 'My Gallery')

END



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

--          Check the Album table

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

IF NOT EXISTS (SELECT * FROM [gs_Album] 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 



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

--          Check the Synchronize table

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

IF NOT EXISTS (SELECT * FROM [gs_Synchronize] WHERE FKGalleryID = @GalleryID)

BEGIN

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

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

END

ELSE

BEGIN -- Reset record to clear out any previous synchronization

	UPDATE gs_Synchronize

	SET SynchID = '',

	SynchState = 0,

	TotalFiles = 0,

	CurrentFileIndex = 0

	WHERE FKGalleryID = @GalleryId

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'Rebuilding [dbo].[gs_MediaObject]'

GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SET XACT_ABORT ON

GO

BEGIN TRANSACTION

CREATE TABLE [dbo].[tmp_ms_xx_gs_MediaObject]

(

[MediaObjectID] [int] NOT NULL IDENTITY(1, 1),

[FKAlbumID] [int] NOT NULL,

[Title] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_gs_MediaObject_Title] DEFAULT (''),

[HashKey] [nchar] (47) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_gs_MediaObject_HashKey] DEFAULT (''),

[ThumbnailFilename] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_gs_MediaObject_ThumbnailFilename] DEFAULT (''),

[ThumbnailWidth] [int] NOT NULL CONSTRAINT [DF_gs_MediaObject_ThumbnailWidth] DEFAULT ((0)),

[ThumbnailHeight] [int] NOT NULL CONSTRAINT [DF_gs_MediaObject_ThumbnailHeight] DEFAULT ((0)),

[ThumbnailSizeKB] [int] NOT NULL,

[OptimizedFilename] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_gs_MediaObject_OptimizedFilename] DEFAULT (''),

[OptimizedWidth] [int] NOT NULL CONSTRAINT [DF_gs_MediaObject_OptimizedWidth] DEFAULT ((0)),

[OptimizedHeight] [int] NOT NULL CONSTRAINT [DF_gs_MediaObject_OptimizedHeight] DEFAULT ((0)),

[OptimizedSizeKB] [int] NOT NULL,

[OriginalFilename] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_gs_MediaObject_OriginalFilename] DEFAULT (''),

[OriginalWidth] [int] NOT NULL CONSTRAINT [DF_gs_MediaObject_OriginalWidth] DEFAULT ((0)),

[OriginalHeight] [int] NOT NULL CONSTRAINT [DF_gs_MediaObject_OriginalHeight] DEFAULT ((0)),

[OriginalSizeKB] [int] NOT NULL,

[Seq] [int] NOT NULL,

[CreatedBy] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[DateAdded] [datetime] NOT NULL,

[LastModifiedBy] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[DateLastModified] [datetime] NOT NULL,

[IsPrivate] [bit] NOT NULL CONSTRAINT [DF_gs_MediaObject_IsPrivate] DEFAULT ((0))

) ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[tmp_ms_xx_gs_MediaObject] ON



INSERT INTO [dbo].[tmp_ms_xx_gs_MediaObject] ([MediaObjectID], FKAlbumID, Title, HashKey, ThumbnailFilename, ThumbnailWidth, ThumbnailHeight,

 ThumbnailSizeKB, OptimizedFilename, OptimizedWidth, OptimizedHeight, OptimizedSizeKB,

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

 DateAdded, LastModifiedBy, DateLastModified, IsPrivate)

SELECT [MediaObjectID], [FKAlbumID], [Title], [HashKey], [ThumbnailFilename], [ThumbnailWidth], [ThumbnailHeight],

 [ThumbnailSizeKB], [OptimizedFilename], [OptimizedWidth], [OptimizedHeight], [OptimizedSizeKB], 

 [OriginalFilename], [OriginalWidth], [OriginalHeight], [OriginalSizeKB], [Seq], 'System', 

 [DateAdded], 'System', GETDATE(), 0

FROM [dbo].[gs_MediaObject]



SET IDENTITY_INSERT [dbo].[tmp_ms_xx_gs_MediaObject] OFF

DROP TABLE [dbo].[gs_MediaObject]

EXEC sp_rename N'[dbo].[tmp_ms_xx_gs_MediaObject]', N'gs_MediaObject'

COMMIT TRANSACTION

GO

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

GO

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

GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

GO

PRINT N'Creating primary key [PK_gs_MediaObject] on [dbo].[gs_MediaObject]'

GO

ALTER TABLE [dbo].[gs_MediaObject] ADD CONSTRAINT [PK_gs_MediaObject] PRIMARY KEY CLUSTERED  ([MediaObjectID]) ON [PRIMARY]

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 index [IDX_gs_MediaObject_FKAlbumID] on [dbo].[gs_MediaObject]'

GO

CREATE NONCLUSTERED INDEX [IDX_gs_MediaObject_FKAlbumID] ON [dbo].[gs_MediaObject] ([FKAlbumID] ASC) ON [PRIMARY]

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 index [IDX_gs_MediaObjectMetadata_FKMediaObjectId] on [dbo].[gs_MediaObjectMetadata]'

GO

CREATE NONCLUSTERED INDEX [IDX_gs_MediaObjectMetadata_FKMediaObjectId] ON [dbo].[gs_MediaObjectMetadata] ([FKMediaObjectId] ASC ) ON [PRIMARY]

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_SelectChildMediaObjects]'

GO

ALTER PROCEDURE [dbo].[gs_SelectChildMediaObjects]

(

	@AlbumId int

)

AS

SET NOCOUNT ON



SELECT 

	MediaObjectID, FKAlbumID, Title, HashKey, ThumbnailFilename, ThumbnailWidth, ThumbnailHeight, 

	ThumbnailSizeKB, OptimizedFilename, OptimizedWidth, OptimizedHeight, OptimizedSizeKB, 

	OriginalFilename, OriginalWidth, OriginalHeight, OriginalSizeKB, Seq, 

	CreatedBy, DateAdded, LastModifiedBy, DateLastModified, IsPrivate

FROM [gs_MediaObject]

WHERE FKAlbumID = @AlbumId



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_SearchGallery]'

GO

ALTER PROCEDURE [dbo].[gs_SearchGallery]

(     @SearchTerms nvarchar(4000), @GalleryId int)

AS

SET NOCOUNT ON



/* Search for albums and media objects that match the specified search

terms. The album or media object must match

ALL search terms to be considered a match. There is no 'OR' capability. For

albums, the Title and Summary columns 

are searched. For media objects, the Title and OriginalFilename columns and

all metadata in the MediaObjectMetadata 

tables is searched.



Inputs:

@SearchTerms - A comma-delimited set of search terms. May include spaces.

Ex: "cat,dog", "cat videos, dog videos"

  Multiple words in a single search term (such as "cat videos" in the

previous example) are treated as a phrase

  that must be matched, exactly like how Google treats phrases contained in

quotation marks. That is, "cat videos"

  requires the phrase "cat videos" to appear somewhere in the data, and it

will not match "cat and dog videos"

  (to match "cat and dog videos", you can use "cat,videos").

@GalleryId - The ID of the gallery to search.



Returns:

Returns a set of records with two columns:

gotype - A single character containing either 'a' for album or 'm' for media

object. Indicates whether the id

         stored in the second column is an album or media object.

id - The ID of a matching album or media object.



Algorithm:

The search follows these steps:

1. Create a temporary table #searchTerms and insert the search terms into

it, prepending and appending the wildcard

   character (%). Ex: If @SearchTerms = "cat videos,dog,fish", #searchTerms

will get 3 records: %cat videos%,

   %dog%, %fish%.

2. Create a second temporary table #searchResults to hold intermediate

search results.

3. Insert into #searchResults all albums where the title matches one of more

search terms. There will be one record

   inserted for each matching search term. Ex: If @SearchTerms = "cat

videos,dog,fish" and the album title =

   "My dog and cat videos", there will be two records inserted into

#searchResults, one with matchingSearchTerm =

   "%cat videos%" and the other "%dog%" (gotype='a', id=album ID,

fieldname='Album.Title' for both).

4. Repeat the above step for other fields: Album.Summary, MediaObject.Title,

MediaObject.OriginalFilename, and

   all media object metadata for each media object

5. Select those records from #searchResults where we made a successful match

for EVERY search term for each album or

   media object.

   

Note: The fieldname column in #searchResults is not used except for manual

debugging purposes. This column can

be removed if desired. 

*/



CREATE TABLE #searchTerms (searchTerm nvarchar(4000) COLLATE

SQL_Latin1_General_CP1_CI_AS)

CREATE TABLE #searchResults (

      gotype char(1), 

      id int, 

      fieldname nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS, 

      matchingSearchTerm nvarchar(3000) COLLATE

SQL_Latin1_General_CP1_CI_AS)



INSERT #searchTerms

SELECT '%' + nstr + '%' FROM

dbo.gs_func_convert_string_array_to_table(@SearchTerms, ',')



-- Search album title

INSERT #searchResults

SELECT 'a', gs_Album.AlbumId, 'Album.Title', '%' + SUBSTRING(gs_Album.Title,

PATINDEX(#searchTerms.searchTerm, gs_Album.Title),

LEN(#searchTerms.searchTerm) - 2) + '%'

 FROM gs_Album CROSS JOIN #searchTerms

 WHERE gs_Album.FKGalleryId = @GalleryId AND gs_Album.Title LIKE

#searchTerms.searchTerm



-- Search album summary

INSERT #searchResults

SELECT 'a', a.AlbumId, 'Album.Summary', '%' + SUBSTRING(a.Summary,

PATINDEX(#searchTerms.searchTerm, a.Summary), LEN(#searchTerms.searchTerm) -

2) + '%'

 FROM gs_Album a CROSS JOIN #searchTerms

 WHERE a.FKGalleryId = @GalleryId AND a.Summary LIKE #searchTerms.searchTerm



-- Search media object title

INSERT #searchResults

SELECT 'm', gs_MediaObject.MediaObjectId, 'MediaObject.Title', '%' +

SUBSTRING(gs_MediaObject.Title, PATINDEX(#searchTerms.searchTerm,

gs_MediaObject.Title), LEN(#searchTerms.searchTerm) - 2) + '%'

 FROM gs_MediaObject JOIN gs_Album

 ON gs_Album.AlbumId = gs_MediaObject.FKAlbumId CROSS JOIN #searchTerms

 WHERE gs_Album.FKGalleryId = @GalleryId AND gs_MediaObject.Title LIKE

#searchTerms.searchTerm --AND 0=1



-- Search media object original filename

INSERT #searchResults

SELECT 'm', gs_MediaObject.MediaObjectId, 'MediaObject.OriginalFilename',

'%' + SUBSTRING(gs_MediaObject.OriginalFilename,

PATINDEX(#searchTerms.searchTerm, gs_MediaObject.OriginalFilename),

LEN(#searchTerms.searchTerm) - 2) + '%'

 FROM gs_MediaObject JOIN gs_Album ON gs_Album.AlbumId =

gs_MediaObject.FKAlbumId CROSS JOIN #searchTerms

 WHERE gs_Album.FKGalleryId = @GalleryId AND gs_MediaObject.OriginalFilename

LIKE #searchTerms.searchTerm --AND 0=1



-- Search media object metadata

INSERT #searchResults

SELECT DISTINCT 'm', gs_MediaObject.MediaObjectId, 'MediaObjectMetadata',

'%' + SUBSTRING(gs_MediaObjectMetadata.Value,

PATINDEX(#searchTerms.searchTerm, gs_MediaObjectMetadata.Value),

LEN(#searchTerms.searchTerm) - 2) + '%'

 FROM gs_MediaObjectMetadata JOIN gs_MediaObject

 ON gs_MediaObjectMetadata.FKMediaObjectId = gs_MediaObject.MediaObjectId

JOIN gs_Album

 ON gs_Album.AlbumId = gs_MediaObject.FKAlbumId CROSS JOIN #searchTerms

 WHERE gs_Album.FKGalleryId = @GalleryId AND gs_MediaObjectMetadata.Value

LIKE #searchTerms.searchTerm



-- Uncomment for debug purposes:

--SELECT * from #searchTerms

--SELECT * FROM #searchResults



SELECT sr.gotype, sr.id

FROM #searchTerms AS st INNER JOIN (SELECT DISTINCT gotype, id,

matchingSearchTerm FROM #searchResults) AS sr ON st.searchTerm =

sr.matchingSearchTerm

GROUP BY sr.gotype, sr.id

HAVING (COUNT(*) >= (SELECT COUNT(*) FROM #searchTerms))



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_MediaObjectSelect]'

GO

ALTER PROCEDURE [dbo].[gs_MediaObjectSelect]

(

	@MediaObjectId int, @GalleryId int

)

AS

SET NOCOUNT ON



SELECT

	mo.MediaObjectID, mo.FKAlbumID, mo.Title, mo.HashKey, mo.ThumbnailFilename, mo.ThumbnailWidth, mo.ThumbnailHeight, 

	mo.ThumbnailSizeKB, mo.OptimizedFilename, mo.OptimizedWidth, mo.OptimizedHeight, mo.OptimizedSizeKB, 

	mo.OriginalFilename, mo.OriginalWidth, mo.OriginalHeight, mo.OriginalSizeKB, mo.Seq, 

	mo.CreatedBy, mo.DateAdded, mo.LastModifiedBy, mo.DateLastModified, mo.IsPrivate

FROM [gs_MediaObject] mo JOIN [gs_Album] a ON mo.FKAlbumID = a.AlbumID

WHERE mo.MediaObjectID = @MediaObjectId AND a.FKGalleryID = @GalleryID



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_MediaObjectUpdate]'

GO

ALTER PROCEDURE [dbo].[gs_MediaObjectUpdate]

(

 @MediaObjectId int, @HashKey char(47), @FKAlbumID int, 

 @ThumbnailFilename nvarchar(255), @ThumbnailWidth int, @ThumbnailHeight int, @ThumbnailSizeKB int,

 @OriginalFilename nvarchar(255),	@OriginalWidth int, @OriginalHeight int, @OriginalSizeKB int, 

 @OptimizedFilename nvarchar(255),	@OptimizedWidth int, @OptimizedHeight int, @OptimizedSizeKB int, 

 @Title nvarchar(1000), @Seq int, @LastModifiedBy nvarchar(256), @DateLastModified datetime, @IsPrivate bit

)

AS

SET NOCOUNT ON



/* Update the media object. */

UPDATE [gs_MediaObject]

SET HashKey = @HashKey, FKAlbumID = @FKAlbumID,

 ThumbnailFilename = @ThumbnailFilename, ThumbnailWidth = @ThumbnailWidth, 

 ThumbnailHeight = @ThumbnailHeight, ThumbnailSizeKB = @ThumbnailSizeKB,

 OptimizedFilename = @OptimizedFilename, OptimizedWidth = @OptimizedWidth,

 OptimizedHeight = @OptimizedHeight, OptimizedSizeKB = @OptimizedSizeKB, 

 OriginalFilename = @OriginalFilename, OriginalWidth = @OriginalWidth,

 OriginalHeight = @OriginalHeight, OriginalSizeKB = @OriginalSizeKB, 

 Title = @Title, Seq = @Seq, LastModifiedBy = @LastModifiedBy, 

 DateLastModified = @DateLastModified, IsPrivate = @IsPrivate

WHERE MediaObjectID = @MediaObjectId

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_MediaObjectInsert]'

GO

ALTER PROCEDURE [dbo].[gs_MediaObjectInsert]

(@HashKey char(47), @FKAlbumID int, @ThumbnailFilename nvarchar(255), 

 @ThumbnailWidth int, @ThumbnailHeight int,

 @ThumbnailSizeKB int, @OptimizedFilename nvarchar(255), 

 @OptimizedWidth int, @OptimizedHeight int,

 @OptimizedSizeKB int, @OriginalFilename nvarchar(255),	 

 @OriginalWidth int, @OriginalHeight int, @OriginalSizeKB int, 

 @Title nvarchar(1000), @Seq int, @CreatedBy nvarchar(256), @DateAdded datetime, 

 @LastModifiedBy nvarchar(256), @DateLastModified datetime, @IsPrivate bit,

 @Identity int OUT)

AS



/* Insert media object information. */

 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 (@HashKey, @FKAlbumID, @ThumbnailFilename, @ThumbnailWidth, @ThumbnailHeight,

 @ThumbnailSizeKB, @OptimizedFilename, @OptimizedWidth, @OptimizedHeight, @OptimizedSizeKB,

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

 @DateAdded, @LastModifiedBy, @DateLastModified, @IsPrivate)

 

SET @Identity = SCOPE_IDENTITY()

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_AlbumSelect]'

GO

ALTER PROCEDURE [dbo].[gs_AlbumSelect]

(

	@AlbumId int, @GalleryId int

)

AS

SET NOCOUNT ON



SELECT

	AlbumID, FKGalleryID as GalleryID, AlbumParentID, Title, DirectoryName, Summary, ThumbnailMediaObjectID, 

	Seq, DateStart, DateEnd, CreatedBy, DateAdded, LastModifiedBy, DateLastModified, OwnedBy, IsPrivate

FROM [gs_Album]

WHERE AlbumId = @AlbumId AND FKGalleryId = @GalleryId



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_AlbumInsert]'

GO

ALTER PROCEDURE [dbo].[gs_AlbumInsert]

(@AlbumParentID int, @GalleryID int, @Title nvarchar(200), @DirectoryName nvarchar(255),

 @Summary nvarchar(1500), @ThumbnailMediaObjectID int, @Seq int, 

 @DateStart datetime, @DateEnd datetime, @CreatedBy nvarchar(256), @DateAdded datetime, 

 @LastModifiedBy nvarchar(256), @DateLastModified datetime, @OwnedBy nvarchar(256),

 @IsPrivate bit, @Identity int OUT)

AS

/* Insert a new album. */

INSERT [gs_Album] (AlbumParentID, FKGalleryID, Title, DirectoryName, 

 Summary, ThumbnailMediaObjectID, Seq, DateStart, DateEnd, 

 CreatedBy, DateAdded, LastModifiedBy, DateLastModified, OwnedBy, 

 IsPrivate)

VALUES (@AlbumParentID, @GalleryID, @Title, @DirectoryName, 

 @Summary, @ThumbnailMediaObjectID, @Seq, @DateStart, @DateEnd, 

 @CreatedBy, @DateAdded, @LastModifiedBy, @DateLastModified, @OwnedBy, 

 @IsPrivate)



SET @Identity = SCOPE_IDENTITY()

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_AlbumUpdate]'

GO

ALTER PROCEDURE [dbo].[gs_AlbumUpdate]

(@AlbumID int, @AlbumParentID int, @Title nvarchar(200), @DirectoryName nvarchar(255),

 @Summary nvarchar(1500), @ThumbnailMediaObjectID int,  @Seq int, 

 @DateStart datetime, @DateEnd datetime, @LastModifiedBy nvarchar(256), 

 @DateLastModified datetime, @OwnedBy nvarchar(256), @IsPrivate bit)

 

AS

SET NOCOUNT ON



UPDATE [gs_Album]

SET

	AlbumParentID = @AlbumParentID,

	Title = @Title,

	DirectoryName = @DirectoryName,

	Summary = @Summary,

	ThumbnailMediaObjectID = @ThumbnailMediaObjectID,

	Seq = @Seq,

	DateStart = @DateStart,

	DateEnd = @DateEnd,

	LastModifiedBy = @LastModifiedBy,

	DateLastModified = @DateLastModified,

	OwnedBy = @OwnedBy,

	IsPrivate = @IsPrivate

WHERE (AlbumID = @AlbumID)



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_SelectRootAlbum]'

GO

ALTER PROCEDURE [dbo].[gs_SelectRootAlbum]

(

	@GalleryId int

)

AS

SET NOCOUNT ON



/* Return the root album. First, check to make sure it exists.

If not, call the stored proc that will insert a default record. */



IF NOT EXISTS (SELECT * FROM [gs_Album] WHERE AlbumParentID = 0 AND FKGalleryID = @GalleryId)

	EXEC dbo.gs_VerifyMinimumRecords @GalleryId



SELECT

	AlbumID, AlbumParentID, Title, DirectoryName, Summary, ThumbnailMediaObjectID, 

	Seq, DateStart, DateEnd, CreatedBy, DateAdded, LastModifiedBy, DateLastModified, OwnedBy, IsPrivate

FROM [gs_Album]

WHERE AlbumParentID = 0 AND FKGalleryID = @GalleryId

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),

	@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 @SysAdminRoleName nvarchar(256)

DECLARE @RootAlbumId int

DECLARE @AdminUserId uniqueidentifier



SET @SysAdminRoleName = N'System Administrator'



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, N'system administrator', N'Global Administration role.')	



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

	--                     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, N'system administrator', N'Global Administration role.')	

		 

	-- 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'Adding foreign keys to [dbo].[gs_Album]'

GO

ALTER TABLE [dbo].[gs_Album] ADD

CONSTRAINT [FK_gs_Album_gs_Album] FOREIGN KEY ([AlbumID]) REFERENCES [dbo].[gs_Album] ([AlbumID]),

CONSTRAINT [FK_gs_Album_gs_Gallery] FOREIGN KEY ([FKGalleryID]) REFERENCES [dbo].[gs_Gallery] ([GalleryID]) ON DELETE CASCADE

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'Adding foreign keys to [dbo].[gs_MediaObject]'

GO

ALTER TABLE [dbo].[gs_MediaObject] ADD

CONSTRAINT [FK_gs_MediaObject_gs_Album] FOREIGN KEY ([FKAlbumID]) REFERENCES [dbo].[gs_Album] ([AlbumID]) ON DELETE CASCADE

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'Adding foreign keys to [dbo].[gs_Role_Album]'

GO

ALTER TABLE [dbo].[gs_Role_Album] ADD

CONSTRAINT [FK_gs_Role_Album_gs_Album] FOREIGN KEY ([FKAlbumId]) REFERENCES [dbo].[gs_Album] ([AlbumID])

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'Adding foreign keys to [dbo].[gs_MediaObjectMetadata]'

GO

ALTER TABLE [dbo].[gs_MediaObjectMetadata] ADD

CONSTRAINT [FK_gs_MediaObjectMetadata_gs_MediaObject] FOREIGN KEY ([FKMediaObjectId]) REFERENCES [dbo].[gs_MediaObject] ([MediaObjectID]) ON DELETE CASCADE

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