/*
	ms-upg110.sql
	MS SQL Server upgrade script from 8.10.2 -> 8.11.0.

	Copyright 2019 Laserfiche
*/

set ansi_defaults on
set arithabort on
set implicit_transactions off
GO

-- Upgrade Step
if not exists (
  select * from dboptions
  where optionname = N'UpgradeStep8110')
begin
	insert into dboptions (optionname, optionvalue)
	values (N'UpgradeStep8110', N'1')
end
GO

if not exists (
	select * from INFORMATION_SCHEMA.COLUMNS
	where TABLE_NAME = 'vhist_ann'
	and COLUMN_NAME = 'protect')
begin
	alter table vhist_ann add 
	keephist		bit null,
	protect			bit default 0 not null
end
GO

if not exists (
  select * from dboptions
  where optionname = N'UpgradeStep8110')
begin
	insert into dboptions (optionname, optionvalue)
	values (N'UpgradeStep8110', N'2')
end
GO

if exists (
  select * from sysobjects
  where id = OBJECT_ID(N'insert_page')
  and OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
	drop procedure insert_page
end
GO

if not exists (
  select * from sysobjects
  where id = OBJECT_ID(N'insert_pages')
  and OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
	declare @sql varchar(8000)
	set @sql = '
	create procedure insert_pages
	as
		select * from toc where tocid = 1'

	exec (@sql)
end
GO

alter procedure insert_pages
	@tocid int,
	@pgnum int,
	@pgcount int,
	@curtime datetime,
	@etag bigint,
	@basesid int,
	@pgflags int,
	@who varbinary(85),
	@pgid bigint output
as
	set nocount on

	declare @pagecount int

	select @pagecount = pagecount
    from toc
    where tocid = @tocid

	update toc set modified = @curtime, pagecount = pagecount + @pgcount,
		toc_modifier = @who, etag = @etag
	where tocid = @tocid

	if @pgnum < @pagecount
	begin
		update doc set pagenum = pagenum + @pgcount
		where tocid = @tocid and pagenum >= @pgnum
	end

	;with pages as (
		select @pgnum as pgnum, @basesid as sid
		union all
		select pgnum+1, sid+1 from pages where pgnum+1<@pgnum + @pgcount
	)
	insert into doc (tocid, pagenum, storeid, page_flags)
    select @tocid, pgnum, sid, @pgflags from pages option (maxrecursion 0);

	set @pgid = @@identity - @pgcount + 1
GO

update dboptions set optionvalue = N'8.11.0'
where optionname = N'version'
GO