/*
_____________________________________________

	Update from DB Schema 6.0.1 to 6.0.8, add Tieline2Exclusion table

*/

use control

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2Exclusion]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating Tieline2Exclusion table.'
	EXEC('CREATE TABLE dbo.Tieline2Exclusion (
		Tieline2Exclusion_ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
		Tieline2Exclusion_Tieline2 int NOT NULL REFERENCES Tieline2(Tieline2_ID),
		Tieline2Exclusion_PhysLevel int NOT NULL REFERENCES PhysLevel(PhysLevel_ID),
		Tieline2Exclusion_Port int NOT NULL DEFAULT 0,
		Tieline2Exclusion_Direction int NOT NULL DEFAULT 1	
			CHECK(Tieline2Exclusion_Direction in (1, 2)),
		
		CONSTRAINT UNIQUE_TIELINE2EXCLUSION UNIQUE
			(
				Tieline2Exclusion_Tieline2,
				Tieline2Exclusion_PhysLevel,
				Tieline2Exclusion_Port,
				Tieline2Exclusion_Direction
			)
	)')

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

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2Exclusion]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	IF EXISTS ( select * from dbo.sysobjects where id = object_id(N'[PK_TIELINE2RESERVATION]') )
	BEGIN
		PRINT 'Removing constraint on Tieline2Reservation'
		EXEC( 'Alter table Tieline2Reservation drop constraint PK_TIELINE2RESERVATION' )
	END
END
ELSE
	PRINT 'Not altering TielineReservationTable'	
GO

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

