/*
	Update from DB Schema 3.1 to 3.5

	Add PhysLevel_NumVirtualXPTs and PhysLevel_VirtualXPTDefInput, if not already there

	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 
		previous Update scripts and
		may well be needed for the next update after this one. (akh 6/02)
*/

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

use control

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'

IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'PhysLevel' AND COLUMN_NAME= 'PhysLevel_NumVirtualXPTs' ))
BEGIN
	PRINT 'Adding PhysLevel_NumVirtualXPTs field to PhysLevel table'
	EXEC('ALTER TABLE PhysLevel ADD PhysLevel_NumVirtualXPTs int NOT NULL DEFAULT(0)')
END
ELSE
	PRINT 'PhysLevel_NumVirtualXPTs field already exists'
go

IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'PhysLevel' AND COLUMN_NAME= 'PhysLevel_VirtualXPTDefInput' ))
BEGIN
	PRINT 'Adding PhysLevel_VirtualXPTDefInput field to PhysLevel table'
	EXEC('ALTER TABLE PhysLevel ADD PhysLevel_VirtualXPTDefInput int NOT NULL DEFAULT(0)')
END
ELSE
	PRINT 'PhysLevel_VirtualXPTDefInput field already exists'
go

declare @ConstraintName varchar(80)
	
/* Drop the constraint on InterLSMapDetail_TielineNeeded. */
	set @ConstraintName=NULL
	SELECT @ConstraintName=so.name FROM syscomments scom INNER JOIN sysobjects so ON scom.id=so.id 
	WHERE scom.text LIKE '%InterLSMapDetail_TielineNeeded%' AND so.parent_obj=object_id('InterLSMapDetail')
	if (@ConstraintName IS NOT NULL)
	BEGIN
		PRINT 'Dropping InterLSMapDetail_TielineNeeded check constraint ' + @ConstraintName
		EXEC('Alter table InterLSMapDetail drop constraint ' + @ConstraintName)
	END



/* ---------------------------------------------- 
	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.5.0','SV0238-03', 'Update of NV Version 3.1 to Version 3.5 database schema.')
end
go

