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 = 19;

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

-- create tables for post year/month/day queries
if object_id(N'dbo.cs_weblog_PostByYearMonth_tbl01') IS NULL
	exec sp_executesql N'create table dbo.cs_weblog_PostByYearMonth_tbl01 (
	[SectionID] int not null
	, [Year] smallint not null
	, [Month] tinyint not null
	, [Day] tinyint not null
	, [PostCount] int not null
	, constraint PK_cs_weblog_PostByYearMonth_tbl01 primary key clustered (
		[SectionID] asc
		, [Year] desc
		, [Month] desc
		, [Day] desc
	)
	)'

if object_id(N'dbo.cs_weblog_PostByYearMonth_tbl02') IS NULL
	exec sp_executesql N'create table dbo.cs_weblog_PostByYearMonth_tbl02 (
	[SectionID] int not null
	, [Year] smallint not null
	, [Month] tinyint not null
	, [Day] tinyint not null
	, [PostCount] int not null
	, constraint PK_cs_weblog_PostByYearMonth_tbl02 primary key clustered (
		[SectionID] asc
		, [Year] desc
		, [Month] desc
		, [Day] desc
	)
	)'

-- adjust the fill factor on the index for cs_Posts_InCategories
if exists (select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
	where tc.TABLE_NAME = N'cs_Posts_InCategories' and tc.CONSTRAINT_NAME = 'PK_cs_PostsInCategories')
	exec sp_executesql N'alter table cs_Posts_InCategories drop constraint PK_cs_PostsInCategories'

exec sp_executesql N'ALTER TABLE [dbo].[cs_Posts_InCategories]
	ADD CONSTRAINT [PK_cs_PostsInCategories] PRIMARY KEY CLUSTERED (
		[CategoryID]
		, [PostID]
	) with fillfactor = 90'

-- for cs_Referral
-- drop cs_Referral PK constraint
IF EXISTS (select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = N'cs_Referrals'
	and CONSTRAINT_NAME = N'PK_cs_Referrals' and CONSTRAINT_TYPE = N'PRIMARY KEY')
	exec sp_executesql N'ALTER TABLE [dbo].[cs_Referrals] DROP CONSTRAINT [PK_cs_Referrals]'

-- add cs_Referral PK constraint with new fill factor
exec sp_executesql N'
ALTER TABLE [dbo].[cs_Referrals] ADD CONSTRAINT [PK_cs_Referrals] PRIMARY KEY  CLUSTERED 
	(
		[SettingsID],
		[SectionID],
		[PostID],
		[UrlID]
	) with fillfactor = 90 ON [PRIMARY] 
'

-- for cs_Urls
-- drop cs_Urls.UrlID foreign constraint
IF EXISTS (select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = N'cs_Referrals'
	and CONSTRAINT_NAME = N'FK_cs_Referrals_cs_Urls' and CONSTRAINT_TYPE = N'FOREIGN KEY')
	exec sp_executesql N'ALTER TABLE [dbo].[cs_Referrals] DROP CONSTRAINT [FK_cs_Referrals_cs_Urls]'

-- drop cs_Referrals PK constraint
IF EXISTS (select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = N'cs_Urls'
	and CONSTRAINT_NAME = N'PK_cs_Urls' and CONSTRAINT_TYPE = N'PRIMARY KEY')
	exec sp_executesql N'ALTER TABLE [dbo].[cs_Urls] DROP CONSTRAINT [PK_cs_Urls]'

-- add cs_Urls PK constraint with new fill factor
exec sp_executesql N'
ALTER TABLE [dbo].[cs_Urls] ADD CONSTRAINT [PK_cs_Urls] PRIMARY KEY  CLUSTERED 
	(
		[UrlID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
'

-- add cs_Referrals FK constraint to cs_Urls.UrlID
exec sp_executesql N'
ALTER TABLE [dbo].[cs_Referrals] ADD CONSTRAINT [FK_cs_Referrals_cs_Urls] FOREIGN KEY 
	(
		[UrlID]
	) REFERENCES [cs_Urls] (
		[UrlID]
	)
'

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

