/*
_____________________________________________

	Update from DB Schema 5.0.0 to 6.0.0, to modify Tieline2 tables for Multisite and Grouping

*/

use control

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2Group]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating Tieline2Group table.'
	EXEC('CREATE TABLE dbo.Tieline2Group ( 
			Tieline2Group_ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
			Tieline2Group_Name nvarchar (24) NOT NULL,
			Tieline2Group_Description nvarchar (255)
	)')

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

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2GroupBinding]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating Tieline2GroupBinding table.'
	EXEC('CREATE TABLE dbo.Tieline2GroupBinding ( 
		Tieline2GroupBinding_Tieline2Group int NOT NULL REFERENCES dbo.Tieline2Group(Tieline2Group_ID),
		Tieline2GroupBinding_Tieline2 int NOT NULL REFERENCES dbo.Tieline2(Tieline2_ID),
		CONSTRAINT PK_TIELINEGROUPBINDING PRIMARY KEY
		(
			Tieline2GroupBinding_Tieline2Group,
			Tieline2GroupBinding_Tieline2
		)
	)')

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

/* Drop Tieline2PortBinding.Tieline2PortBindng_Remote  column */
IF EXISTS (SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'Tieline2PortBinding' AND COLUMN_NAME= 'Tieline2PortBinding_Remote' ))
BEGIN
	/* Drop the foreign key constraint first */
	declare @ConstraintName sysname

	SELECT @ConstraintName=CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE (COLUMN_NAME like 'Tieline2PortBinding_Remote%' and CONSTRAINT_NAME like 'FK__Tieline2P__Tieli_%')
	IF (@ConstraintName IS NOT NULL)
	BEGIN
		PRINT 'Dropping Tieline2PortBinding FK constraint ' + @ConstraintName
		EXEC('Alter table Tieline2PortBinding drop constraint ' + @ConstraintName)
	END
	ELSE
		PRINT'Skipping Tieline2PortBinding constraint drop, it does not exist'

	PRINT 'Dropping Tieline2PortBinding.Tieline2PortBinding_Remote...'
	EXEC('ALTER TABLE Tieline2PortBinding DROP COLUMN Tieline2PortBinding_Remote')
END
ELSE 	
	PRINT 'Tieline2PortBinding_Remote does not exist.'
GO


/* Table Remote has been renamed to ControlSystem. Easier to drop the table and create a new one */
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[Remote]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	DROP TABLE Remote
END
ELSE 	
	PRINT 'Remote does not exist.'
GO

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[ControlSystem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating ControlSystem table.'
	EXEC('CREATE TABLE dbo.ControlSystem ( 
			ControlSystem_ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
			ControlSystem_Name nvarchar (24) NOT NULL, 
			ControlSystem_IP nvarchar(24) NOT NULL, 
			ControlSystem_Description nvarchar (255)
	)')

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

declare @count int
SELECT @count=COUNT(*) FROM ControlSystem
if (@count=0)
BEGIN
    PRINT 'Initializing ControlSystem table'

    set IDENTITY_INSERT Suffix ON
    INSERT into ControlSystem (ControlSystem_Name, ControlSystem_IP, ControlSystem_Description) values('Local', '127.0.0.1', 'Local ControlSystem')
    set IDENTITY_INSERT Suffix OFF
END
ELSE
    PRINT 'Not initializing ControlSystem table, it already has records'
GO

/* Add Tieline2.Tieline2_ControlSystem */
IF NOT EXISTS (SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'Tieline2' AND COLUMN_NAME= 'Tieline2_ControlSystem' ))
BEGIN
	PRINT 'Add Tieline2.Tieline2_ControlSystem ...'
	EXEC('ALTER TABLE Tieline2 ADD Tieline2_ControlSystem int NOT NULL DEFAULT 1' )
END
ELSE 	
	PRINT 'Tieline2.Tieline2_ControlSystem  already exist.'
GO

/* Add Tieline2.Tieline2_ControlSystem_Tieline2 */
IF NOT EXISTS (SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'Tieline2' AND COLUMN_NAME= 'Tieline2_ControlSystem_Tieline2' ))
BEGIN
	PRINT 'Add Tieline2.Tieline2_ControlSystem_Tieline2 ...'
	EXEC('ALTER TABLE Tieline2 ADD Tieline2_ControlSystem_Tieline2 int NOT NULL DEFAULT 0' )
END
ELSE 	
	PRINT 'Tieline2.Tieline2_ControlSystem_Tieline2 already exist.'
GO


declare @count int
SELECT @count=COUNT(*) FROM Information_Schema.TABLE_CONSTRAINTS where TABLE_Name like 'Tieline2' and CONSTRAINT_NAME like 'UNIQUE_TIELINE2'
if (@count=0)
BEGIN
    PRINT 'Creating unique key on Tieline2'

    EXEC('ALTER TABLE Tieline2 ADD CONSTRAINT UNIQUE_TIELINE2 UNIQUE ( Tieline2_ControlSystem, Tieline2_ControlSystem_Tieline2)')
END
ELSE
    PRINT 'Tieline2 already has a unique key.'
GO

/* the tieline2reservation & reservation history tables need to be recreated */
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2Reservation]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	DROP TABLE Tieline2Reservation
END
ELSE 	
	PRINT 'Tieline2Reservation does not exist.'
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2ReservationHistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	DROP TABLE Tieline2ReservationHistory
END
ELSE 	
	PRINT 'Tieline2ReservationHistory does not exist.'
GO

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2Reservation]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating Tieline2Reservation table.'
	EXEC('CREATE TABLE dbo.Tieline2Reservation (
		Tieline2Reservation_Tieline2 int NOT NULL REFERENCES Tieline2(Tieline2_ID),
		Tieline2Reservation_ControlSystem_Name nvarchar (24) NOT NULL,
		Tieline2Reservation_User int NOT NULL,
		Tieline2Reservation_IPAddress nvarchar (20) NOT NULL,
		Tieline2Reservation_LTC nvarchar(20) NOT NULL,
		Tieline2Reservation_Timestamp datetime NOT NULL DEFAULT GETDATE(),
		Tieline2Reservation_SourcePhysLevel int NOT NULL,
		Tieline2Reservation_SourceSignalType int NOT NULL,
		Tieline2Reservation_SourcePort int NOT NULL,
		Tieline2Reservation_DestinationPhysLevel int NOT NULL,
		Tieline2Reservation_DestinationSignalType int NOT NULL,
		Tieline2Reservation_DestinationPort int NOT NULL,
		Tieline2Reservation_DownstreamPort int NOT NULL,
		Tieline2Reservation_UpstreamPort int NOT NULL
		CONSTRAINT PK_TIELINE2RESERVATION PRIMARY KEY
		(
			Tieline2Reservation_Tieline2,
			Tieline2Reservation_ControlSystem_Name,
			Tieline2Reservation_User,
			Tieline2Reservation_DestinationPhysLevel,
			Tieline2Reservation_DestinationPort
		)
	)')
END
ELSE
	PRINT 'Not creating Tieline2Reservation table, it already exists'
GO

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline2ReservationHistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Creating Tieline2ReservationHistory table.'
	EXEC('CREATE TABLE dbo.Tieline2ReservationHistory (
		Tieline2ReservationHistory_Tieline2 int NOT NULL REFERENCES Tieline2(Tieline2_ID),
		Tieline2ReservationHistory_ControlSystem_Name nvarchar (24) NOT NULL,
		Tieline2ReservationHistory_User int NOT NULL,
		Tieline2ReservationHistory_IPAddress nvarchar (20) NOT NULL,
		Tieline2ReservationHistory_LTC nvarchar(20) NOT NULL,
		Tieline2ReservationHistory_Timestamp datetime NOT NULL DEFAULT GETDATE(),
		Tieline2ReservationHistory_SourcePhysLevel int NOT NULL,
		Tieline2ReservationHistory_SourceSignalType int NOT NULL,
		Tieline2ReservationHistory_SourcePort int NOT NULL,
		Tieline2ReservationHistory_DestinationPhysLevel int NOT NULL,
		Tieline2ReservationHistory_DestinationSignalType int NOT NULL,
		Tieline2ReservationHistory_DestinationPort int NOT NULL,
		Tieline2ReservationHistory_DownstreamPort int NOT NULL,
		Tieline2ReservationHistory_UpstreamPort int NOT NULL,
		Tieline2ReservationHistory_ReleaseTimestamp datetime DEFAULT GETDATE()
	)')
END
ELSE
	PRINT 'Not creating Tieline2ReservationHistory table, it already exists'
GO

/* 
 * Drop foreign key constraints on Tieine2Reservation and Tieline2ReservationHistory 
 *
 * Create short term procedure
 */
CREATE PROCEDURE NV_DropTieline2ReservationConstraints
@vcharTableNameIn nvarchar(128)

AS

SET NOCOUNT ON

DECLARE @charConstraintName 	char(128)
DECLARE @vcharSQLStatement	char(2000)

DECLARE dropConstraintCursor CURSOR READ_ONLY
FOR
    SELECT CCu2.CONSTRAINT_NAME
    FROM (INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU ON
    RC.UNIQUE_CONSTRAINT_NAME = CCU.CONSTRAINT_NAME )
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU2 ON RC.CONSTRAINT_NAME = CCU2.CONSTRAINT_NAME
    WHERE ((CCU2.TABLE_NAME=@vcharTableNameIn and CCU.TABLE_NAME <> 'Tieline2') )

OPEN dropConstraintCursor

FETCH NEXT FROM dropConstraintCursor INTO @charConstraintName

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @vcharSQLStatement = 'ALTER TABLE ' + @vcharTableNameIn + ' DROP CONSTRAINT ' + @charConstraintName
    EXEC ( @vcharSQLStatement )

    FETCH NEXT FROM dropConstraintCursor INTO @charConstraintName
END

CLOSE dropConstraintCursor
DEALLOCATE dropConstraintCursor

RETURN 0
GO

PRINT 'Executing NV_DropTieline2ReservationConstraints for both tables ...'
EXEC NV_DropTieline2ReservationConstraints 'Tieline2Reservation'
EXEC NV_DropTieline2ReservationConstraints 'Tieline2ReservationHistory'
GO

PRINT 'Dropping NV_DropTieline2ReservationConstraints procedure'
DROP PROCEDURE NV_DropTieline2ReservationConstraints 
GO

PRINT 'Updating Alias table for UTF-8 support'
ALTER TABLE Alias
	ALTER COLUMN Alias_Mnemonic nvarchar(8)
GO

PRINT 'Updating App table for UTF-8 support'
ALTER TABLE App
	ALTER COLUMN App_Name nvarchar(32)
ALTER TABLE App
	ALTER COLUMN App_Params nvarchar(255)
GO

PRINT 'Updating Category table for UTF-8 support'
ALTER TABLE Category
	ALTER COLUMN Category_Mnemonic nvarchar(8)
ALTER TABLE Category
	ALTER COLUMN Category_Description nvarchar(255)
GO

PRINT 'Updating ControlPoint table for UTF-8 support'
ALTER TABLE ControlPoint
	ALTER COLUMN ControlPoint_SetupParams nvarchar(50)
ALTER TABLE ControlPoint
	ALTER COLUMN ControlPoint_Description nvarchar(255)
GO

PRINT 'Updating ControlSystem table for UTF-8 support'
ALTER TABLE ControlSystem
	ALTER COLUMN ControlSystem_Name nvarchar(24)
ALTER TABLE ControlSystem
	ALTER COLUMN ControlSystem_IP nvarchar(24)
ALTER TABLE ControlSystem
	ALTER COLUMN ControlSystem_Description nvarchar(255)
GO

PRINT 'Updating Device table for UTF-8 support'
ALTER TABLE Device
	ALTER COLUMN Device_Mnemonic nvarchar(8)
ALTER TABLE Device
	ALTER COLUMN Device_Description nvarchar(255)
GO

PRINT 'Updating GUI table for UTF-8 support'
ALTER TABLE GUI
	ALTER COLUMN GUI_Description nvarchar(255)
GO
PRINT 'Updating Host table for UTF-8 support'
ALTER TABLE Host
	ALTER COLUMN Host_Name nvarchar(24)
ALTER TABLE Host
	ALTER COLUMN Host_Params nvarchar(50)
ALTER TABLE Host
	ALTER COLUMN Host_NameOfBackup nvarchar(24)
GO
PRINT 'Updating LevelSet table for UTF-8 support'
ALTER TABLE LevelSet
	ALTER COLUMN LevelSet_Name nvarchar(24)
GO

PRINT 'Updating NvSysParam table for UTF-8 support'
ALTER TABLE NvSysParam
	ALTER COLUMN NvSysParam_Value nvarchar(255)
ALTER TABLE NvSysParam
	ALTER COLUMN NvSysParam_Desc nvarchar(255)
GO

PRINT 'Updating NvUser table for UTF-8 support'
ALTER TABLE NvUser
	ALTER COLUMN NvUser_Name nvarchar(32)
GO

PRINT 'Updating Panel table for UTF-8 support'
ALTER TABLE Panel
	ALTER COLUMN Panel_Name nvarchar(8)
ALTER TABLE Panel
	ALTER COLUMN Panel_Description nvarchar(255)
GO

PRINT 'Updating Path table for UTF-8 support'
ALTER TABLE Path
	ALTER COLUMN Path_FilePath nvarchar(255)
GO

PRINT 'Updating PhysConn table for UTF-8 support'
ALTER TABLE PhysConn
	ALTER COLUMN PhysConn_Description nvarchar(255)
GO

PRINT 'Updating PhysLevel table for UTF-8 support'
ALTER TABLE PhysLevel
	ALTER COLUMN PhysLevel_Name nvarchar(24)
GO

PRINT 'Updating Router table for UTF-8 support'
ALTER TABLE Router
	ALTER COLUMN Router_Name nvarchar(24)
GO

PRINT 'Updating RouterControl table for UTF-8 support'
ALTER TABLE RouterControl
	ALTER COLUMN RouterControl_ControlParams nvarchar(50)
GO

PRINT 'Updating Salvo table for UTF-8 support'
ALTER TABLE Salvo
	ALTER COLUMN Salvo_Mnemonic nvarchar(8)
ALTER TABLE Salvo
	ALTER COLUMN Salvo_Description nvarchar(255)
GO

PRINT 'Updating SalvoList table for UTF-8 support'
ALTER TABLE SalvoList
	ALTER COLUMN SalvoList_Mnemonic nvarchar(8)
GO

PRINT 'Updating SignalType table for UTF-8 support'
ALTER TABLE SignalType
	ALTER COLUMN SignalType_Name nvarchar(24)
ALTER TABLE SignalType
	ALTER COLUMN SignalType_Description nvarchar(255)
GO

PRINT 'Updating Suffix table for UTF-8 support'
ALTER TABLE Suffix
	ALTER COLUMN Suffix_Name nvarchar(4)
GO

PRINT 'Updating Tieline2 table for UTF-8 support'
ALTER TABLE Tieline2
	ALTER COLUMN Tieline2_Name nvarchar(24)
ALTER TABLE Tieline2
	ALTER COLUMN Tieline2_Description nvarchar(255)
GO

PRINT 'Updating Tieline2Group table for UTF-8 support'
ALTER TABLE Tieline2Group
	ALTER COLUMN Tieline2Group_Name nvarchar(24)
ALTER TABLE Tieline2Group
	ALTER COLUMN Tieline2Group_Description nvarchar(255)
GO

PRINT 'Updating Tieline2Reservation table for UTF-8 support'
ALTER TABLE Tieline2Reservation
	ALTER COLUMN Tieline2Reservation_IPAddress nvarchar(20)
ALTER TABLE Tieline2Reservation
	ALTER COLUMN Tieline2Reservation_LTC nvarchar(20)
GO

PRINT 'Updating Tieline2ReservationHistory table for UTF-8 support'
ALTER TABLE Tieline2ReservationHistory
	ALTER COLUMN Tieline2ReservationHistory_ControlSystem_Name nvarchar(24)
ALTER TABLE Tieline2ReservationHistory
	ALTER COLUMN Tieline2ReservationHistory_IPAddress nvarchar(20)
ALTER TABLE Tieline2ReservationHistory
	ALTER COLUMN Tieline2ReservationHistory_LTC nvarchar(20)
GO

PRINT 'Updating Version table for UTF-8 support'
ALTER TABLE Version
	ALTER COLUMN Version_SVNumber nvarchar(15)
ALTER TABLE Version
	ALTER COLUMN Version_Description nvarchar(255)
ALTER TABLE Version
	ALTER COLUMN Version_SWVersion nvarchar(24)
GO

PRINT 'Updating VirtualLevel table for UTF-8 support'
ALTER TABLE VirtualLevel
	ALTER COLUMN VirtualLevel_Mnemonic nvarchar(8)
GO

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

