/*
_____________________________________________

	Update from DB Schema 8.0.0 to 8.1.0, add new field to PhysConn table and new table, Network. All changes are for GvNode suppot
*/

use control

DECLARE @UpdateNeeded INT;
SET @UpdateNeeded = 0;

DECLARE @DESC_SIZE nvarchar(4)
set @DESC_SIZE = '255'

PRINT 'Executing ControlSchemaUpdate_800to810.sql.'

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[GvNodePhysConn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Dropping GvNodePhysConn table'
		DROP TABLE GvNodePhysConn;
		SET @UpdateNeeded = 1;	
END
ELSE
	PRINT 'GvNodePhysConn table does not exist'

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[GvNodeFrame]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN	

	IF EXISTS (select column_name from INFORMATION_SCHEMA.columns where
                     Table_name = 'GvNodeFrame'
                     and Column_name = 'GvNodeFrame_ID')	
	BEGIN	
		PRINT 'Dropping GvNodeFrame table, then recreating'
		Drop table GvNodeFrame;
	
		PRINT 'Creating GvNodeFrame table'
		EXEC( 'CREATE TABLE control.dbo.GvNodeFrame (
				GvNodeFrame_Router int NOT NULL REFERENCES Router(Router_ID) PRIMARY KEY,
				GvNodeFrame_Description nvarchar (' + @DESC_SIZE + ') NULL,
				GvNodeFrame_Group int NOT NULL,
				GvNodeFrame_Number int NOT NULL,
			
				CONSTRAINT ucGVNodeFrame UNIQUE (GvNodeFrame_Group, GvNodeFrame_Number)
			)' )
		SET @UpdateNeeded = 1;
	END
	ELSE
	BEGIN
		PRINT 'Not dropping and recreating GvNodeFrame table, primary key already updated.'
	END
END
	
IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Network]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN	
	PRINT 'Creating Network table'
	EXEC( 'CREATE TABLE Network (
		Network_ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,		
		Network_Name varchar(20) NOT NULL,
		Network_Lane int NOT NULL,
		Network_Frame int NOT NULL REFERENCES GvNodeFrame(GvNodeFrame_Router),
		Network_Redundant int REFERENCES Network(Network_ID) NULL,
		Network_Format int NOT NULL
		)'
	)
	SET @UpdateNeeded = 1;
END
ELSE
	PRINT 'Network table already exists.'

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[PhysConn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN	
	IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where
                     Table_name = 'PhysConn'
                     and Column_name = 'PhysConn_Network')	
	BEGIN
		PRINT 'Adding PhysConn.PhysConn_Network column'
		ALTER TABLE PhysConn ADD PhysConn_Network int REFERENCES Network(Network_ID) NULL;
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT' PhysConn.PhysConn_Network column already exists'
		
	DECLARE @ConstraintName AS NVARCHAR(255);
    	DECLARE @DropConstraintSQL AS NVARCHAR(255);


	--Get the name of the constraint that will be dropped
	SET @ConstraintName = 
    		 (SELECT TOP 1 o.name FROM sysobjects o 
     			JOIN syscolumns c ON o.id = c.cdefault 
     			JOIN sysobjects t ON c.id = t.id 
     				WHERE o.xtype = 'd'  AND c.name = 'PhysConn_IPPort' AND t.name = 'PhysConn');


	IF (@constraintName IS NOT NULL) OR (LEN(@constraintName ) > 0)
	BEGIN
	PRINT 'Dropping default constraint on PhysConn.PhysConn_IPPort'
		DECLARE @SQLCmd varchar(200);
		SET @SQLCmd = 'ALTER TABLE PhysConn DROP CONSTRAINT ' + @constraintName ;
		EXEC (@SQLCmd);
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT 'PhysConn.IPPort default constraint does not exist'
		
	PRINT 'Dropping NOT NULL constraint on PhysConn.PhysConn_PhysLevelPort'
	ALTER TABLE PhysConn ALTER COLUMN PhysConn_PhysLevelPort int NULL;
	
END
ELSE
	PRINT 'Error: PhysConn table does not exist'	
	
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[Device]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN	
	IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where
                     Table_name = 'Device'
                     and Column_name = 'Device_Type')	
	BEGIN
		PRINT 'Adding Device.Device_Type column'
		ALTER TABLE Device ADD Device_Type int NOT NULL DEFAULT(1);
		SET @UpdateNeeded = 1;
	END
	ELSE
		PRINT' Device.Device_Type column already exists'
END

SET @UpdateNeeded = 1;

IF ( @UpdateNeeded = 1 )
BEGIN
	PRINT 'Inserting version information.'
	INSERT INTO Version(Version_SWVersion,Version_SVNumber, Version_Description) VALUES( 
		'8.1.0','SV0238-08D0', 'Update of NV Version 8.0.0 to Version 8.1.0 database schema.')
END

GO

