/*
	Update from DB Schema 2.0 to 2.1

	Adds tieline-related tables
	Drops RouterComposite table.
	Drops Omnibus tables.
	
	(doesn't drop the obsolete ErrMsgs tables 
	(SysErrMsgs and UserErrMsgs), since the customer
	has no way to restore them if they want to revert
	to 2.0.  These tables SHOULD be dropped for >2.1
	schema update.
*/

/* 
	beware--these variables are only good 
	until the next "go" command! 
*/

use control

DECLARE @CONTROLDB varchar(30)
set @CONTROLDB = db_name()
print 'Using ' + @CONTROLDB

if (left(@CONTROLDB,7)<>'control')
begin
	print 'ERROR, active DB 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

DECLARE @ERRORMSGDB varchar(30)
DECLARE @MNEMONIC_SIZE varchar(2)
DECLARE @NAME_SIZE varchar(3)
DECLARE @DESC_SIZE varchar(4)
DECLARE @PARAM_SIZE varchar(3)
DECLARE @CONTROLDB varchar(30)
set @ERRORMSGDB = 'ErrorMsgs'
set @MNEMONIC_SIZE = '8'
set @NAME_SIZE = '24'
set @DESC_SIZE = '255'
set @PARAM_SIZE = '50'

set @CONTROLDB = db_name()

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[InterLvlsetMap]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'Creating InterLvlsetMap table.'
EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.InterLvlsetMap (
	InterLvlsetMap_ID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	InterLvlsetMap_Name varchar (' + @NAME_SIZE + ') NOT NULL,
	InterLvlsetMap_UpstrLvlset int NOT NULL REFERENCES Levelset(Levelset_ID),
	InterLvlsetMap_DnstrLvlset int NOT NULL REFERENCES Levelset(Levelset_ID),
	CHECK((InterLvlsetMap_UpstrLvlset <> InterLvlsetMap_DnstrLvlset)),
	InterLvlsetMap_DefaultConns int NOT NULL DEFAULT 0	
		CHECK(InterLvlsetMap_DefaultConns in (0, 1))
	)'
)
END
ELSE
	PRINT 'Not creating InterLvlsetMap table, it already exists.'

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[InterLSMapDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'Creating InterLSMapDetail table.'
EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.InterLSMapDetail (
	InterLSMapDetail_ID int
		IDENTITY(1, 1)
		PRIMARY KEY CLUSTERED,		
	InterLSMapDetail_InterLvlsetMap int NOT NULL REFERENCES InterLvlsetMap(InterLvlsetMap_ID),
	InterLSMapDetail_UpstrVL int NOT NULL REFERENCES VirtualLevel(VirtualLevel_ID),
	InterLSMapDetail_DnstrVL int NOT NULL REFERENCES VirtualLevel(VirtualLevel_ID),
	InterLSMapDetail_TielineNeeded int NOT NULL DEFAULT 1
		CHECK(InterLSMapDetail_TielineNeeded in (0, 1))
	)'
)
END
ELSE
	PRINT 'Not creating InterLSMapDetail table, it already exists.'

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[TielineClass]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'Creating TielineClass table.'
EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.TielineClass (
	TielineClass_ID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	TielineClass_Name varchar (' + @NAME_SIZE + ') NOT NULL,
	TielineClass_InterLvlsetMap int NOT NULL REFERENCES InterLvlsetMap(InterLvlsetMap_ID),
	TielineClass_PreferredDstList int NULL REFERENCES DeviceList(DeviceList_ID),
	TielineClass_Description varchar(' + @DESC_SIZE + ') NULL
	)'
)
END
ELSE
	PRINT 'Not creating TielineClass table, it already exists.'

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[TLClassDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'Creating TLClassDetail table.'
EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.TLClassDetail (
	TLClassDetail_TielineClass int NOT NULL REFERENCES TielineClass(TielineClass_ID),
	TLClassDetail_DnstrVL int NOT NULL REFERENCES VirtualLevel(VirtualLevel_ID)
	)'
)
END
ELSE
	PRINT 'Not creating TLClassDetail table, it already exists.'

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[Tieline]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'Creating Tieline table.'
EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.Tieline (
	Tieline_ID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	Tieline_Name varchar (' + @NAME_SIZE + ') NOT NULL,
	Tieline_TielineClass int NOT NULL REFERENCES TielineClass(TielineClass_ID),
	Tieline_UpstrDevice int NOT NULL REFERENCES Device(Device_ID),
	Tieline_DnstrDevice int NOT NULL REFERENCES Device(Device_ID),
	Tieline_DefaultState int NOT NULL DEFAULT 1
		CHECK(Tieline_DefaultState in (0, 1))
	)'
)
END
ELSE
	PRINT 'Not creating Tieline table, it already exists.'

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[RouterComposite]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Dropping RouterComposite table in Control DB.'
	EXEC('drop table ' + @CONTROLDB + '.dbo.RouterComposite')
END
ELSE
	PRINT 'Skipping RouterComposite drop command, table does not exist.'

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[ETDevDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Dropping ETDevDetail table in Control DB.'
	EXEC('drop table ' + @CONTROLDB + '.dbo.ETDevDetail')
END
ELSE
	PRINT 'Skipping ETDevDetail drop command, table does not exist.'

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[ETLSDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Dropping ETLSDetail table in Control DB.'
	EXEC('drop table ' + @CONTROLDB + '.dbo.ETLSDetail')
END
ELSE
	PRINT 'Skipping ETLSDetail drop command, table does not exist.'

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[ETPLDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Dropping ETPLDetail table in Control DB.'
	EXEC('drop table ' + @CONTROLDB + '.dbo.ETPLDetail')
END
ELSE
	PRINT 'Skipping ETPLDetail drop command, table does not exist.'
	
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[ExportTo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'Dropping ExportTo table in Control DB.'
	EXEC('drop table ' + @CONTROLDB + '.dbo.ExportTo')
END
ELSE
	PRINT 'Skipping ExportTo drop command, table does not exist.'
	
go	
  
declare @ConstraintName sysname

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

PRINT 'Inserting version information.'
INSERT INTO Version(Version_SVNumber, Version_Description) VALUES( '1192168', 'Update of Envy release 2.0 to release 2.1 database schema.')
go
	
/* Not yet!  wait for 2.2 or higher release */

/* use ErrorMsgs */
/* go */

/* PRINT 'Dropping UserErrMsgs table in ErrorMsgs DB.'  */
/* drop table UserErrMsgs */
/* go */

/* PRINT 'Dropping SysErrMsgs table in ErrorMsgs DB.'  */
/* drop table SysErrMsgs */
/* go */

