/*
_____________________________________________

	Update from DB Schema 3.5 to 3.6, or fix the transitory wrong DB Schema 3.6

	Add Alias table, if it's not already present
	
	If categorydetail_index field is NOT present:
		remove categorydetail_suffix fields
		add categorydetail_index
	if it IS already present:
		remove its constraint
	if Suffix table is not present:
		add it
	if Suffix table is empty
		add default suffixes
	and categorydetail_suffix fields aren't present:
		add them.
		if categorydetail_index isn't null, populate the suffix fields from it
	(and no, we dont blindly set categorydetail_index field to NULL, since this would make MasterCtl panels unhappy.)
_____________________________________________

FYIs for those changing this software in future:	
	(if you add a table, also add it to ConfigGUI's CtrlSysTblsForType_i.asp, 
	if ConfigGUI is still being used.)

	Note that this script creates temporary stored
		procs for "drop column of table" and "drop table".  
		Please don't delete it, even if this particular schema update isn't using it,
		since they've been used in previous Update scripts and
		will likely be needed for future ones. (akh 6/02)
_____________________________________________

*/

/* /////////////////////////////////////////////////////////////
  control DB
////////////////////////////////////////////////////////////// */

print 'Using ' + db_name()
declare @CONTROLDB sysname
SET @CONTROLDB=db_name()
if (LEFT(@CONTROLDB,7)<>'control')
begin
	print 'ERROR, active DB right now is ' + @CONTROLDB + ' (should be Control or equivalent)'
	DECLARE @errmsg0 varchar(128)
	set @errmsg0='Aborting, active DB is ' + @CONTROLDB
	raiserror(@errmsg0, 20, 1) with log
end
go

PRINT 'Adding Temporary stored procs'
go
/* ---------------------------------------------- 
	Create the 'Drop Column of Table' stored proc (temporary)
-------------------------------------------------
*/
CREATE PROCEDURE NV_ChgSchema_DropColumnAndItsConstraints
@tableName varchar(40),
@colName varchar(40)
AS
IF NOT EXISTS (SELECT Table_Name, Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = @tableName AND COLUMN_NAME= @colName ))
	PRINT 'FYI, ' + @colName + ' did not exist in ' + @tableName + ' table, so cannot be dropped'
ELSE BEGIN
	declare @ConstraintName sysname
	declare ConstraintCursor cursor
	
	/* rm 'check' constraint(s) if any */
	FOR
	SELECT so.name FROM syscomments scom INNER JOIN sysobjects so ON scom.id=so.id 
	WHERE scom.text LIKE '%' + @colName + '%' AND so.parent_obj=object_id(@tableName)
	open ConstraintCursor
	FETCH NEXT FROM ConstraintCursor into @ConstraintName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT 'Dropping ' + @tableName + ' checkconstraint ' + @ConstraintName
		EXEC('Alter table ' + @tableName + ' drop constraint ' + @ConstraintName)
		FETCH NEXT FROM ConstraintCursor into @ConstraintName
	END
	close ConstraintCursor
	deallocate ConstraintCursor
	
	declare @ConstraintName2 sysname
	declare ConstraintCursor2 cursor
	/* rm Default or ForeignKey constraints, if any */
	FOR
	SELECT so.name FROM sysobjects so INNER JOIN syscolumns sc 
		ON so.parent_obj=sc.id WHERE (so.info=sc.colID AND (so.xtype = 'D' or so.xtype= 'F'))
		AND (sc.id=object_id(@tableName) AND sc.name=@colName)
	open ConstraintCursor2
	FETCH NEXT FROM ConstraintCursor2 into @ConstraintName2
	WHILE @@FETCH_STATUS = 0
	BEGIN
		Print 'Dropping ' + @tableName + ' constraint ' +@ConstraintName2
		EXEC('Alter table ' + @tableName + ' drop constraint ' + @ConstraintName2) 
		FETCH NEXT FROM ConstraintCursor2 into @ConstraintName2
	END
	close ConstraintCursor2
	deallocate ConstraintCursor2

	/* rm column */
	Print 'Altering ' + @tableName + ' table, dropping column '+@colName
	EXEC('Alter table ' + @tableName + ' drop column ' + @colName)
	
END
go
/* ---------------------------------------------- 
	Create the 'Drop Table' stored proc (temporary)
-------------------------------------------------
*/
CREATE PROCEDURE NV_ChgSchema_DropTable
@tableName varchar(40)
AS
IF NOT EXISTS (SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name=@tableName)
	PRINT 'FYI, ' + @tableName + ' table did not exist, so cannot be dropped'
ELSE BEGIN
	declare @ConstraintName sysname
	declare ConstraintCursor cursor

	FOR
	SELECT so.name FROM sysobjects so INNER JOIN syscolumns sc 
		ON so.parent_obj=sc.id 
		WHERE (so.info=sc.colID AND (so.xtype = 'D' or so.xtype= 'F'))
		AND (sc.id=object_id(@tableName))
	open ConstraintCursor
	FETCH NEXT FROM ConstraintCursor into @ConstraintName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		Print 'Dropping ' + @tableName + ' constraint ' +@ConstraintName
		EXEC('Alter table ' + @tableName + ' drop constraint ' + @ConstraintName) 
		FETCH NEXT FROM ConstraintCursor into @ConstraintName
	END
	close ConstraintCursor
	deallocate ConstraintCursor
	

	PRINT 'Dropping ' + @tableName + ' table'
	EXEC('Drop table ' + @tableName )
END
go

/* ---------------------------------------------- 
	Update the schema
-------------------------------------------------
*/
/* ----------------------------------------- 
*/


/* 
	beware--these variables are only good 
	until the next "go" command! 
*/
declare @sSql varchar(255)
DECLARE @ERRORMSGDB varchar(30)
DECLARE @MNEMONIC_SIZE varchar(2)
DECLARE @NAME_SIZE varchar(3)
DECLARE @DESC_SIZE varchar(4)
DECLARE @PARAM_SIZE varchar(3)
set @ERRORMSGDB = 'ErrorMsgs'
set @MNEMONIC_SIZE = '8'
set @NAME_SIZE = '24'
set @DESC_SIZE = '255'
set @PARAM_SIZE = '50'

/*	Create the alias table, if it doesn't already exist	*/

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Alias]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating Alias table.'
	EXEC('CREATE TABLE dbo.Alias (
		Alias_ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
		Alias_PhysLevel int NOT NULL REFERENCES PhysLevel(PhysLevel_ID),
		Alias_PhysLevelPort int NOT NULL, 
		Alias_Mnemonic varchar (' + @MNEMONIC_SIZE + ') NOT NULL 
	)')

END
ELSE
	PRINT 'Not creating Alias table, it already exists'
GO

/*	If categorydetail_index field is NOT present
	(signifies that we're in the "oops, undesirable intermediate dbschema" state)
*/
IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'CategoryDetail' AND COLUMN_NAME= 'CategoryDetail_Index' ))
BEGIN
	PRINT 'Dropping CategoryDetail_Suffix fields, if they exist' 
	EXEC('NV_ChgSchema_DropColumnAndItsConstraints ''CategoryDetail'',''CategoryDetail_Suffix1''')
	EXEC('NV_ChgSchema_DropColumnAndItsConstraints ''CategoryDetail'',''CategoryDetail_Suffix2''')
	EXEC('NV_ChgSchema_DropColumnAndItsConstraints ''CategoryDetail'',''CategoryDetail_Suffix3''')
	EXEC('NV_ChgSchema_DropColumnAndItsConstraints ''CategoryDetail'',''CategoryDetail_Suffix4''')
	EXEC('NV_ChgSchema_DropColumnAndItsConstraints ''CategoryDetail'',''CategoryDetail_Suffix5''')
	EXEC('NV_ChgSchema_DropColumnAndItsConstraints ''CategoryDetail'',''CategoryDetail_Suffix6''')
	EXEC('NV_ChgSchema_DropColumnAndItsConstraints ''CategoryDetail'',''CategoryDetail_Suffix7''')
	EXEC('NV_ChgSchema_DropColumnAndItsConstraints ''CategoryDetail'',''CategoryDetail_Suffix8''')

	PRINT 'Adding CategoryDetail_Index field to CategoryDetail table since we deleted it'
	EXEC('ALTER TABLE CategoryDetail ADD CategoryDetail_Index int NULL')
END
ELSE BEGIN
	PRINT 'CategoryDetail_Index field already exists - removing nonnull constraint if any'
	EXEC('Alter table CategoryDetail ALTER COLUMN CategoryDetail_Index int NULL')
END
GO
	
/* 	if Suffix table is not present:
		add it
*/
IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Suffix]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating Suffix table.'
	EXEC('CREATE TABLE [dbo].[Suffix] (
		[Suffix_ID] [int] IDENTITY (1, 1) NOT NULL ,
		[Suffix_Name] [varchar] (4) NOT NULL 
	) ON [PRIMARY]
	')

	EXEC('ALTER TABLE [dbo].[Suffix] WITH NOCHECK ADD 
		CONSTRAINT [PK_Suffix] PRIMARY KEY  CLUSTERED 
		(
			[Suffix_ID]
		)  ON [PRIMARY] 
	')
END
ELSE
	PRINT 'Not creating Suffix table, it already exists'
GO
	
declare @count int
SELECT @count=COUNT(*) FROM Suffix 
if (@count=0)
BEGIN
	PRINT 'Initializing Suffix table'

	/* these also get initialized in stored proc NV_AddControlDBDefaults 
	(called by the ConfigGUI)
	(but the Upgrade doesn't call (in baselinedata.sql) the stored proc unless the DB is empty,
	hence we also do it here.)
	*/
	
	set IDENTITY_INSERT Suffix ON
	insert into Suffix(Suffix_ID, Suffix_Name) values (1, '1')
	insert into Suffix(Suffix_ID, Suffix_Name) values (2, '2')
	insert into Suffix(Suffix_ID, Suffix_Name) values (3, '3')
	insert into Suffix(Suffix_ID, Suffix_Name) values (4, '4')
	insert into Suffix(Suffix_ID, Suffix_Name) values (5, '5')
	insert into Suffix(Suffix_ID, Suffix_Name) values (6, '6')
	insert into Suffix(Suffix_ID, Suffix_Name) values (7, '7')
	insert into Suffix(Suffix_ID, Suffix_Name) values (8, '8')
	insert into Suffix(Suffix_ID, Suffix_Name) values (9, '9')
	insert into Suffix(Suffix_ID, Suffix_Name) values (10, '0')
	set IDENTITY_INSERT Suffix OFF
END
ELSE
	PRINT 'Not initializing Suffix table, it already has records'
GO


/* Add 'suffix' fields to CategoryDetail table, if they're not present. 
(in real life, either all will be present or none will)
And if Suffix8 isn't present, after adding it we also populate the Suffix fields 1-4
from the CategoryDetail_Index value.
*/

IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'CategoryDetail' AND COLUMN_NAME= 'CategoryDetail_Suffix1' ))
BEGIN
	PRINT 'Adding CategoryDetail_Suffix1 field to CategoryDetail table'
	EXEC('ALTER TABLE CategoryDetail ADD CategoryDetail_Suffix1 int NULL
	ALTER TABLE [dbo].[CategoryDetail] ADD 
	CONSTRAINT [FK_CategoryDetail_Suffix1] FOREIGN KEY 
	(
		[CategoryDetail_Suffix1]
	) REFERENCES [dbo].[Suffix] (
		[Suffix_ID]
	)')
END
ELSE
	PRINT 'CategoryDetail_Suffix1 field already exists'
GO

IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'CategoryDetail' AND COLUMN_NAME= 'CategoryDetail_Suffix2' ))
BEGIN
	PRINT 'Adding CategoryDetail_Suffix2 field to CategoryDetail table'
	EXEC('ALTER TABLE CategoryDetail ADD CategoryDetail_Suffix2 int NULL
	ALTER TABLE [dbo].[CategoryDetail] ADD 
	CONSTRAINT [FK_CategoryDetail_Suffix2] FOREIGN KEY 
	(
		[CategoryDetail_Suffix2]
	) REFERENCES [dbo].[Suffix] (
		[Suffix_ID]
	)')
END
ELSE
	PRINT 'CategoryDetail_Suffix2 field already exists'
GO

IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'CategoryDetail' AND COLUMN_NAME= 'CategoryDetail_Suffix3' ))
BEGIN
	PRINT 'Adding CategoryDetail_Suffix3 field to CategoryDetail table'
	EXEC('ALTER TABLE CategoryDetail ADD CategoryDetail_Suffix3 int  NULL
	ALTER TABLE [dbo].[CategoryDetail] ADD 
	CONSTRAINT [FK_CategoryDetail_Suffix3] FOREIGN KEY 
	(
		[CategoryDetail_Suffix3]
	) REFERENCES [dbo].[Suffix] (
		[Suffix_ID]
	)')
END
ELSE
	PRINT 'CategoryDetail_Suffix3 field already exists'
GO

IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'CategoryDetail' AND COLUMN_NAME= 'CategoryDetail_Suffix4' ))
BEGIN
	PRINT 'Adding CategoryDetail_Suffix4 field to CategoryDetail table'
	EXEC('ALTER TABLE CategoryDetail ADD CategoryDetail_Suffix4 int NULL
	ALTER TABLE [dbo].[CategoryDetail] ADD 
	CONSTRAINT [FK_CategoryDetail_Suffix4] FOREIGN KEY 
	(
		[CategoryDetail_Suffix4]
	) REFERENCES [dbo].[Suffix] (
		[Suffix_ID]
	)')
END
ELSE
	PRINT 'CategoryDetail_Suffix4 field already exists'
GO

IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'CategoryDetail' AND COLUMN_NAME= 'CategoryDetail_Suffix5' ))
BEGIN
	PRINT 'Adding CategoryDetail_Suffix5 field to CategoryDetail table'
	EXEC('ALTER TABLE CategoryDetail ADD CategoryDetail_Suffix5 int NULL
	ALTER TABLE [dbo].[CategoryDetail] ADD 
	CONSTRAINT [FK_CategoryDetail_Suffix5] FOREIGN KEY 
	(
		[CategoryDetail_Suffix5]
	) REFERENCES [dbo].[Suffix] (
		[Suffix_ID]
	)')
END
ELSE
	PRINT 'CategoryDetail_Suffix5 field already exists'
GO

IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'CategoryDetail' AND COLUMN_NAME= 'CategoryDetail_Suffix6' ))
BEGIN
	PRINT 'Adding CategoryDetail_Suffix6 field to CategoryDetail table'
	EXEC('ALTER TABLE CategoryDetail ADD CategoryDetail_Suffix6 int NULL
	ALTER TABLE [dbo].[CategoryDetail] ADD 
	CONSTRAINT [FK_CategoryDetail_Suffix6] FOREIGN KEY 
	(
		[CategoryDetail_Suffix6]
	) REFERENCES [dbo].[Suffix] (
		[Suffix_ID]
	)')
END
ELSE
	PRINT 'CategoryDetail_Suffix6 field already exists'
GO

IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'CategoryDetail' AND COLUMN_NAME= 'CategoryDetail_Suffix7' ))
BEGIN
	PRINT 'Adding CategoryDetail_Suffix7 field to CategoryDetail table'
	EXEC('ALTER TABLE CategoryDetail ADD CategoryDetail_Suffix7 int NULL
	ALTER TABLE [dbo].[CategoryDetail] ADD 
	CONSTRAINT [FK_CategoryDetail_Suffix7] FOREIGN KEY 
	(
		[CategoryDetail_Suffix7]
	) REFERENCES [dbo].[Suffix] (
		[Suffix_ID]
	)')
END
ELSE
	PRINT 'CategoryDetail_Suffix7 field already exists'
GO

IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'CategoryDetail' AND COLUMN_NAME= 'CategoryDetail_Suffix8' ))
BEGIN
	PRINT 'Adding CategoryDetail_Suffix8 field to CategoryDetail table'
	EXEC('ALTER TABLE CategoryDetail ADD CategoryDetail_Suffix8 int NULL
	ALTER TABLE [dbo].[CategoryDetail] ADD 
	CONSTRAINT [FK_CategoryDetail_Suffix8] FOREIGN KEY 
	(
		[CategoryDetail_Suffix8]
	) REFERENCES [dbo].[Suffix] (
		[Suffix_ID]
	)')

	/* fill 'suffix' fields from categorydetail_index
	*/
	IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
	(TABLE_NAME = 'CategoryDetail' AND COLUMN_NAME= 'CategoryDetail_Index'))
	BEGIN
		PRINT 'Translating Category Indexes into suffix references'
		EXEC('update categorydetail
			set categorydetail.categorydetail_suffix1 = suffix.suffix_ID 
			from categorydetail, suffix where 
			categorydetail_Index IS NOT NULL AND
			SUBSTRING(CAST(categorydetail_Index as VARCHAR(10)), 1, 1) = suffix.Suffix_Name
		')

		EXEC('update categorydetail
			set categorydetail.categorydetail_suffix2 = suffix.suffix_ID 
			from categorydetail, suffix where
			categorydetail_Index IS NOT NULL AND
			SUBSTRING(CAST(categorydetail_Index as VARCHAR(10)), 2, 1) = suffix.Suffix_Name
		')

		EXEC('update categorydetail
			set categorydetail.categorydetail_suffix3 = suffix.suffix_ID 
			from categorydetail, suffix where
			categorydetail_Index IS NOT NULL AND
			SUBSTRING(CAST(categorydetail_Index as VARCHAR(10)), 3, 1) = suffix.Suffix_Name
		')

		EXEC('update categorydetail
			set categorydetail.categorydetail_suffix4 = suffix.suffix_ID 
			from categorydetail, suffix where
			categorydetail_Index IS NOT NULL AND
			SUBSTRING(CAST(categorydetail_Index as VARCHAR(10)), 4, 1) = suffix.Suffix_Name
		')
	END
END /* CategoryDetail_Suffix8 field didnt exist */
ELSE
	PRINT 'CategoryDetail_Suffix8 field already exists'
GO


/*	No, dont drop it.  Backward compatibility is needed.
// PRINT ' *****NOT*****   Dropping field CategoryDetail_Index' 
//  EXEC('NV_ChgSchema_DropColumnAndItsConstraints
//     ''CategoryDetail'',''CategoryDetail_Index''')
// Don't set it to NULL either, since the MasterCtl panels will want its info.
// EXEC('update categorydetail
// 	set categorydetail.categorydetail_index = NULL 
// ')
*/

/* ---------------------------------------------- 
	drop the  (temporary) stored procs
-------------------------------------------------*/

PRINT 'Dropping Temporary stored procs'
drop procedure NV_ChgSchema_DropColumnAndItsConstraints
drop procedure NV_ChgSchema_DropTable
go


if (@@error<>0)
	print 'bad.'
else
begin
PRINT 'Inserting version information.'
INSERT INTO Version(Version_SWVersion,Version_SVNumber, Version_Description) VALUES( 
	'3.6.0','SV0238-03H0', 'Update of NV Version 3.5 to Version 3.6 database schema.')
end
go

