/*
	Update from DB Schema 2.2 to 2.3

	Adds NvSysParam table
	note that there's code below to create & then delete (but never use) stored
		procs for "drop column of table" and "drop table".  I've left the code
		in here for consistency's sake since the procs were used in the
		previous Update script and
		may well be needed for the next update after this one. (akh 6/02)
*/

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

DECLARE @CONTROLDB varchar(30)
set @CONTROLDB = db_name()
print 'Using ' + @CONTROLDB

if (left(@CONTROLDB,7)<>'control')
begin
	print 'ERROR, active DB 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
/* ---------------------------------------------- 
	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
-------------------------------------------------
*/
declare @sSql varchar(255)


/* 
	beware--these variables are only good 
	until the next "go" command! 
*/

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'

/* add NvSysParam table 
*/

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME = 'NvSysParam')
BEGIN
	PRINT 'FYI, NvSysParam table already present.'
END 
ELSE 
BEGIN
	PRINT 'Adding NvSysParam table ...'
	DECLARE @CONTROLDB varchar(30)
	SET @CONTROLDB = db_Name()
	EXEC( 'CREATE TABLE ' + @CONTROLDB  + '.dbo.NvSysParam (
			NvSysParam_ID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			NvSysParam_Name varchar (' + @NAME_SIZE + ') NOT NULL UNIQUE,
			NvSysParam_Value varchar(255) NULL,
			NvSysParam_Desc varchar (' + @DESC_SIZE + ') NULL
		)'
	)

END

/* set Default value for PhysLevel_InputLock&Protect fields to Disabled
*/

/* first drop the existing Default Constraints on these fields
*/
	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'))
		AND (sc.name='PhysLevel_InputLock' OR sc.name='PhysLevel_InputProtect')
	open ConstraintCursor
	FETCH NEXT FROM ConstraintCursor into @ConstraintName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC('Alter table PhysLevel drop constraint ' + @ConstraintName) 
		FETCH NEXT FROM ConstraintCursor into @ConstraintName
	END
	close ConstraintCursor
	deallocate ConstraintCursor

/* now add the new ones
*/
EXEC('Alter table PhysLevel ADD CONSTRAINT DF__PhysLevel_InputLock DEFAULT 3 FOR PhysLevel_InputLock')
EXEC('Alter table PhysLevel ADD CONSTRAINT DF__PhysLevel_InputProtect DEFAULT 3 FOR PhysLevel_InputProtect')

go

/* -------------------------------------------
' In PhysLvl table, convert any 'In Router' locks/prots to InServer
------------------------------------------- */
IF EXISTS (SELECT * FROM PhysLevel WHERE PhysLevel_InputProtect=2 OR PhysLevel_InputLock=2)
BEGIN		
	PRINT 'You had Input Lock or Protect locations in PhysLevel table set to "In Router".  Changing to "In Server"...'
	EXEC('UPDATE PhysLevel SET PhysLevel_InputProtect=1 WHERE PhysLevel_InputProtect=2')
	EXEC('UPDATE PhysLevel SET PhysLevel_InputLock=1 WHERE PhysLevel_InputLock=2')
END
IF EXISTS (SELECT PhysLevel_ID FROM PhysLevel WHERE PhysLevel_OutputProtect=2 OR PhysLevel_OutputLock=2)
BEGIN		
	PRINT 'You had Output Lock or Protect locations in PhysLevel table set to "In Router".  Changing to "In Server"...'
	EXEC('UPDATE PhysLevel SET PhysLevel_OutputProtect=1 WHERE PhysLevel_OutputProtect=2')
	EXEC('UPDATE PhysLevel SET PhysLevel_OutputLock=1 WHERE PhysLevel_OutputLock=2')
END



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

drop procedure NV_ChgSchema_DropColumnAndItsConstraints
drop procedure NV_ChgSchema_DropTable



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

if (@@error<>0)
	print 'bad.'
else
begin
PRINT 'Inserting version information.'
INSERT INTO Version(Version_SVNumber, Version_Description) VALUES( 
	'SV0238-02', 'Update of NV9000 Version 2.2 to Version 2.3 database schema.')
end
go

