SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DECLARE @Major int, @Minor int, @Patch int, @Installed DateTime, @Prereqs int

Set @Major = 3;
Set @Minor = 0;
Set @Patch = 20;

Select @Prereqs = isnull(Count(InstallDate),0)  from cs_SchemaVersion where Major=@Major and Minor=@Minor and Patch<@Patch

Select @Installed = InstallDate  from cs_SchemaVersion where Major=@Major and Minor=@Minor and Patch=@Patch

If(@Installed is null AND @Prereqs = @Patch)
	BEGIN
--## Schema Patch ##

-- updating UsernameRegex to updated pattern
exec('
declare
	@UsernameRegexOriginalEscaped nvarchar(128), @UsernameRegexNew nvarchar(128), @UsernameRegexNodeName nvarchar(128)
	, @id int, @ptrSettingsXML binary(16), @insert_offset int, @delete_length int

declare @t table (
	id int identity(1, 1) not null, ptrSettingsXML binary(16) not null
	, UsernameRegexStart int not null, UsernameRegexEnd int not null, UsernameRegexNodeLength int not null
	, insert_offset AS UsernameRegexStart + UsernameRegexNodeLength - 1
	, delete_length AS UsernameRegexEnd - (UsernameRegexStart + UsernameRegexNodeLength)
)

set nocount on

select
	-- actual pattern: N''[a-zA-Z]+[^\<\>]*''
	@UsernameRegexOriginalEscaped = N''[[]a-zA-Z]+[[][^]\&lt;\&gt;]*''
	, @UsernameRegexNew = N''^[a-zA-Z0-9_\- @\.]+$''
	, @UsernameRegexNodeName = N''UsernameRegex''

select
	db_name() DatabaseName, s.SettingsID, s.SettingsXML
	, case when patindex(
		N''%<'' + @UsernameRegexNodeName + N''>''
		+ @UsernameRegexOriginalEscaped
		+ N''</'' + @UsernameRegexNodeName + N''>%''
		, s.SettingsXML )<>0 then ''Updating'' else ''No Update'' end
from
	cs_SiteSettings s

insert into @t (ptrSettingsXML, UsernameRegexStart, UsernameRegexEnd, UsernameRegexNodeLength)
select
	TEXTPTR( s.SettingsXML )
	, patindex( N''%<'' + @UsernameRegexNodeName + N''>%'', s.SettingsXML )
	, patindex( N''%</'' + @UsernameRegexNodeName + N''>%'', s.SettingsXML )
	, 2 + LEN( @UsernameRegexNodeName )
from
	cs_SiteSettings s
where
	s.SettingsXML IS NOT NULL
	and patindex(
		N''%<'' + @UsernameRegexNodeName + N''>''
		+ @UsernameRegexOriginalEscaped
		+ N''</'' + @UsernameRegexNodeName + N''>%''
		, s.SettingsXML ) <> 0

while 1=1
begin
	select @id = (select min(id) from @t)
	if @id is null break
	
	select @ptrSettingsXML = t.ptrSettingsXML, @insert_offset = t.insert_offset, @delete_length = t.delete_length
	from @t t where id = @id
	
	UPDATETEXT cs_SiteSettings.SettingsXML @ptrSettingsXML @insert_offset @delete_length @UsernameRegexNew

	delete from @t where id = @id
end
')

--## END Schema Patch ##
Insert into cs_SchemaVersion(Major, Minor, Patch, InstallDate) values (@Major, @Minor, @Patch, GetDate())

Print 'Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ' was applied successfully '

	END
ELSE IF(@Installed is not null)
	BEGIN
Print 'Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ' was already applied on ' + Convert(varchar(50), @Installed)  
	END 
ELSE
	BEGIN
Print 'The patch could not be applied because your current schema is missing previous updates (Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ')' 
	END 

