/*
	Update from DB Schema 3.0 to 3.1

	In case this was missed in prev db,
		Changes recovery model from 'full' to 'simple' for full versions of SQL Server
	  (prevents the transaction logs from growing too large)

	Give PhysLevel a name

	Add TielineClass_Params
	
	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
////////////////////////////////////////////////////////////// */

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 recovery model on old installations
   ----------------------------------------------
*/
declare @CONTROLDB nvarchar(32)
SET @CONTROLDB=db_name()
DECLARE @cmd sysname
SET @cmd = N'master.dbo.xp_cmdshell ''osql -n -E -d ' + @CONTROLDB
		+ N' -i C:\nvision\envy\install\UpgradeRecoveryModel.sql'' '
PRINT N'Executing ' + @cmd
	EXEC( @cmd)
GO
/* ---------------------------------------------- 
	Update the schema
-------------------------------------------------
*/
/* ----------------------------------------- 
Add Default constraints in PhysLevel and RouterControl tables.

These only needed if config was created as a 3.0.0 config.
set Default values for PhysLevel_ChopInterval,InputStart,OutputStart fields
and RouterControl_Protocol.
(all but inStart and outStart were already set, just not named.)
*/

PRINT 'Setting Default value for PhysLevel_ChopInterval field to 6'
PRINT 'Setting Default value for PhysLevel_Input and Output Start fields to 1'

/* first drop the existing Default Constraint on these fields
*/
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'))
		AND (sc.name='PhysLevel_ChopInterval' OR
		(sc.name='PhysLevel_InputStart' OR sc.name='PhysLevel_OutputStart'))
	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
END
go
/* now add the new ones
*/
EXEC('Alter table PhysLevel ADD CONSTRAINT DF__PhysLevel_ChopInterval DEFAULT 6 FOR PhysLevel_ChopInterval')
EXEC('Alter table PhysLevel ADD CONSTRAINT DF__PhysLevel_InputStart DEFAULT 1 FOR PhysLevel_InputStart')
EXEC('Alter table PhysLevel ADD CONSTRAINT DF__PhysLevel_OutputStart DEFAULT 1 FOR PhysLevel_OutputStart')
go

/* 'Setting Default value for RouterControl_Protocol field to 7 (Ethernet)'
*/

/* first drop the existing Default Constraint
*/
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'))
		AND (sc.name='RouterControl_Protocol')
	open ConstraintCursor
	FETCH NEXT FROM ConstraintCursor into @ConstraintName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC('Alter table RouterControl drop constraint ' + @ConstraintName) 
		FETCH NEXT FROM ConstraintCursor into @ConstraintName
	END
	close ConstraintCursor
	deallocate ConstraintCursor
END
go

/* now add the new ones
*/
EXEC('Alter table RouterControl ADD CONSTRAINT DF__RouterControl_Protocol DEFAULT 7 FOR RouterControl_Protocol')
go



/* 
	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 = 'TielineClass' AND COLUMN_NAME= 'TielineClass_Params' ))
BEGIN
	PRINT 'Adding TielineClass_Params field to TielineClass table'
	EXEC('ALTER TABLE TielineClass ADD TielineClass_Params varchar(' + @PARAM_SIZE + ') NULL')
END
ELSE
	PRINT 'FYI, TielineClass_Params field already exists'



IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'PhysLevel' AND COLUMN_NAME= 'PhysLevel_Name' ))
BEGIN
	PRINT 'Adding PhysLevel_Name field to PhysLevel table'
	EXEC('ALTER TABLE PhysLevel ADD PhysLevel_Name varchar(' + @NAME_SIZE + ') NULL')
END
ELSE
	PRINT 'FYI, PhysLevel_Name field already exists'


go

BEGIN
	PRINT N'Setting PhysLevel_Name for most old PhysLevels that lack it'
	declare @PLID int
	declare @PLNum int
	declare @rtrName varchar(24)
	declare @PLName varchar(64)
	declare PhysLevelCursor cursor
	FOR
	SELECT PhysLevel_ID, PhysLevel_Number, Router_Name FROM PhysLevel INNER JOIN Router ON PhysLevel_Router=Router_ID WHERE PhysLevel_Name IS NULL
	open PhysLevelCursor
	FETCH NEXT FROM PhysLevelCursor into @PLID, @PLNum, @rtrName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @PLName=@rtrName + N'_' + CAST(@PLNum AS varchar)
		IF (LEN(@PLName)>24) 
			SET @PLName=LEFT(@rtrName,8) + N'_' + CAST(@PLNum AS varchar)
		IF (LEN(@PLName)<24)  
		BEGIN
			PRINT 'Setting PhysLevel_Name field to ' + @PLName + ' for PhysLevel ID ' + CAST(@PLID AS varchar) 
			EXEC('UPDATE PhysLevel SET PhysLevel_Name=''' + @PLName 
				+ ''' WHERE PhysLevel_ID=' + @PLID )

		END
		FETCH NEXT FROM PhysLevelCursor into @PLID, @PLNum, @rtrName
	END
	close PhysLevelCursor
	deallocate PhysLevelCursor
END
go




/* now add the new ones
*/

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

