/*
	Update from DB Schema 2.1 to 2.2

	Drops unused Router and Physlevel DB fields
	Replaces DeviceList,DeviceListDetail with CategoryDetail
	Remove Router_Type, add PhysLevel_SignalType
	Drops the obsolete-in-2.1 ErrMsgs tables (SysErrMsgs and UserErrMsgs).
	(we didn't drop them in 2.1, since the customer
	has no way to restore them if they wanted to revert
	to 2.0).
*/

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

/* ---------------------------------------------- 
	Drop our inconsistently-named stored procs
-------------------------------------------------
*/
if exists (select * from sysobjects where id = object_id(N'[dbo].[AllCategories]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AllCategories]

if exists (select * from sysobjects where id = object_id(N'[dbo].[AllDstCategories]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AllDstCategories]

if exists (select * from sysobjects where id = object_id(N'[dbo].[AllDstDevices]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AllDstDevices]

if exists (select * from sysobjects where id = object_id(N'[dbo].[AllDstLevels]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AllDstLevels]

if exists (select * from sysobjects where id = object_id(N'[dbo].[AllSalvos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AllSalvos]

if exists (select * from sysobjects where id = object_id(N'[dbo].[AllSrcCategories]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AllSrcCategories]

if exists (select * from sysobjects where id = object_id(N'[dbo].[AllSrcDevices]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AllSrcDevices]

if exists (select * from sysobjects where id = object_id(N'[dbo].[AllSrcLevels]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AllSrcLevels]

if exists (select * from sysobjects where id = object_id(N'[dbo].[CategoryNameFromID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CategoryNameFromID]

if exists (select * from sysobjects where id = object_id(N'[dbo].[DeviceNameFromID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeviceNameFromID]

if exists (select * from sysobjects where id = object_id(N'[dbo].[DevicesInCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DevicesInCategory]

if exists (select * from sysobjects where id = object_id(N'[dbo].[FK_Info]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[FK_Info]

if exists (select * from sysobjects where id = object_id(N'[dbo].[GetEnumStrings]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetEnumStrings]

if exists (select * from sysobjects where id = object_id(N'[dbo].[GetEnvyConfigHostName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetEnvyConfigHostName]

if exists (select * from sysobjects where id = object_id(N'[dbo].[LevelsForDstDevice]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[LevelsForDstDevice]

if exists (select * from sysobjects where id = object_id(N'[dbo].[LevelsForSrcDevice]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[LevelsForSrcDevice]

if exists (select * from sysobjects where id = object_id(N'[dbo].[SalvoNameFromID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SalvoNameFromID]

if exists (select * from sysobjects where id = object_id(N'[dbo].[ShiftVirtualXptPortNumbers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ShiftVirtualXptPortNumbers]

if exists (select * from sysobjects where id = object_id(N'[dbo].[PhysLevel_VirtXptChange]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[PhysLevel_VirtXptChange]

if exists (select * from sysobjects where id = object_id(N'[dbo].[ConfigIdentity_Just1Row]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[ConfigIdentity_Just1Row]

if exists (select * from sysobjects where id = object_id(N'[dbo].[RunReplication]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[RunReplication]

if exists (select * from sysobjects where id = object_id(N'[dbo].[AddControlDBDefaults]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AddControlDBDefaults]

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 the schema
-------------------------------------------------
*/
declare @sSql varchar(255)


/* add PhysLevel_SignalType field, and populate it from Router_Type 
*/
IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'Router' AND COLUMN_NAME= 'Router_Type' ))
BEGIN
	PRINT 'FYI, Router_Type already gone.'
END 
ELSE 
BEGIN
	PRINT 'Adding PhysLevel_SignalType...'
	EXEC('Alter table PhysLevel add PhysLevel_SignalType int NOT NULL DEFAULT(1)')
	PRINT 'Moving Type data...'
	set @sSQL = 'DECLARE plCursor CURSOR FOR SELECT DISTINCT \
		PhysLevel_ID, Router_Type FROM PhysLevel \
		INNER JOIN Router ON Router_ID = PhysLevel_Router'
	EXEC(@sSQL)
	open plCursor
	declare @plID int
	declare @plType int
	FETCH NEXT FROM plCursor into @plID, @plType
	WHILE @@FETCH_STATUS = 0
	BEGIN
		set @sSQL = 'UPDATE PhysLevel SET PhysLevel_SignalType=' + convert(varchar(8),@plType) 
			+ ' WHERE PhysLevel_ID=' + convert(varchar(8), @plID)
		EXEC(@sSQL)
		FETCH NEXT FROM plCursor into @plID, @plType
	END
	close plCursor
	deallocate plCursor
	EXEC('NV_ChgSchema_DropColumnAndItsConstraints ''Router'',''Router_Type''')
END



/* drop unused columns */
EXEC('NV_ChgSchema_DropColumnAndItsConstraints ''Router'',''Router_NumIns''')
EXEC('NV_ChgSchema_DropColumnAndItsConstraints ''Router'',''Router_NumOuts''')
EXEC('NV_ChgSchema_DropColumnAndItsConstraints ''Router'',''Router_Composite''')
EXEC('NV_ChgSchema_DropColumnAndItsConstraints ''PhysLevel'',''PhysLevel_NumVirtXPTs''')
EXEC('NV_ChgSchema_DropColumnAndItsConstraints ''PhysLevel'',''PhysLevel_InputRtrPortStart''')
EXEC('NV_ChgSchema_DropColumnAndItsConstraints ''PhysLevel'',''PhysLevel_OutputRtrPortStart''')

/* add category_description */
IF EXISTS (SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'Category' AND COLUMN_NAME= 'Category_Description' ))
	PRINT 'FYI, Category_Description already exists.'
ELSE BEGIN
	PRINT 'adding category_description..'
	EXEC('Alter table Category add Category_Description varchar(255) NULL')
END


declare @devlistID int
declare @catID int
/* -------------------------------------------
' make a cat. for any devlist that doesn't have one 
------------------------------------------- */
IF EXISTS (SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name='DeviceList')
BEGIN
	EXEC ('DECLARE dlCursor CURSOR FOR SELECT DISTINCT \
		DeviceList_ID, Category_ID FROM DeviceList \
		LEFT OUTER JOIN Category ON DeviceList_ID = Category_DeviceList \
		WHERE Category_ID IS NULL')
	open dlCursor
	declare @sDevlistID varchar(15)
	FETCH NEXT FROM dlCursor into @devlistID, @catID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		set @sDevlistID = convert(varchar(15),@devlistID)
		set @sSql = 'INSERT INTO Category(Category_Mnemonic,Category_DeviceList,Category_Description) VALUES('''
				+ 'Cat'+ @sDevlistID + ''',' + @sDevlistID 
				+ ', ''Autogenerated for devlistID=' + @sDevlistID + ''''
		EXEC (@sSql)
		FETCH NEXT FROM dlCursor into @devlistID, @catID
	END
	close dlCursor
	deallocate dlCursor
END


/* -------------------------------------------
' add and populate categorydetail table 
------------------------------------------- */
IF EXISTS (SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name='CategoryDetail')
	PRINT 'FYI, CategoryDetail table already exists, so will not be created'
ELSE BEGIN
	EXEC('CREATE TABLE CategoryDetail(
				CategoryDetail_Category int NOT NULL REFERENCES Category(Category_ID),
				CategoryDetail_Device int NOT NULL REFERENCES Device(Device_ID),
				CategoryDetail_Index int NOT NULL
				CONSTRAINT PK_CATEGORYDETAIL PRIMARY KEY CLUSTERED
				(
					CategoryDetail_Category,
					CategoryDetail_Device
				)
			)
		')
	EXEC('INSERT INTO CategoryDetail(CategoryDetail_Category,CategoryDetail_Device,CategoryDetail_Index)
		SELECT Category_ID,DeviceListDetail_Device,DeviceListDetail_Index
			FROM Category INNER JOIN DeviceList ON Category_DeviceList=DeviceList_ID
			INNER JOIN DeviceListDetail ON DeviceListDetail_DeviceList=DeviceList_ID
		')

END

/* -------------------------------------------
replace tielineclass preferredDstList(a devlist) with PreferredDsts(a category)
------------------------------------------- */

IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
(TABLE_NAME = 'TielineClass' AND COLUMN_NAME= 'TielineClass_PreferredDstList' ))
BEGIN
	PRINT 'FYI, TielineClass_PreferredDstList already gone.'
END ELSE BEGIN
	PRINT 'TielineClass_PreferredDstList is present.'
	EXEC('Alter table TielineClass add TielineClass_PreferredDsts int NULL REFERENCES Category(Category_ID)')
	EXEC ('DECLARE tlcCursor CURSOR FOR SELECT DISTINCT \
		TielineClass_PreferredDstList, Category_ID FROM TielineClass \
		INNER JOIN Category ON TielineClass_PreferredDstList = Category_DeviceList \
		WHERE TielineClass_PreferredDstList IS NOT NULL')
	open tlcCursor
	FETCH NEXT FROM tlcCursor into @devlistID, @catID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		set @sSql = 'UPDATE TielineClass SET TielineClass_PreferredDsts=' + convert(varchar(15),@catID) 
			+ ' WHERE TielineClass_PreferredDstList=' + convert(varchar(15), @devlistID)
		EXEC(@sSql)
		FETCH NEXT FROM tlcCursor into @devlistID, @catID
	END
	close tlcCursor
	deallocate tlcCursor
END
/* go */

EXEC('NV_ChgSchema_DropColumnAndItsConstraints ''TielineClass'',''TielineClass_PreferredDstList''')
EXEC('NV_ChgSchema_DropTable ''DeviceListDetail''')
EXEC('NV_ChgSchema_DropColumnAndItsConstraints ''Category'',''Category_DeviceList''')
EXEC('NV_ChgSchema_DropTable ''DeviceList''')

go

/* ---------------------------------------------- 
	remove leftover constraints that might have 
	slipped through from previous version updates
-------------------------------------------------*/
declare @ConstraintName varchar(80)
	
/* if by chance RouterControl_ControlMode constraint is still present, get rid of it. */
	set @ConstraintName=NULL
	SELECT @ConstraintName=so.name FROM syscomments scom INNER JOIN sysobjects so ON scom.id=so.id 
	WHERE scom.text LIKE '%RouterControl_ControlMode%' AND so.parent_obj=object_id('RouterControl')
	if (@ConstraintName IS NOT NULL)
	BEGIN
		PRINT 'Dropping leftover RouterControl_ControlMode checkconstraint ' + @ConstraintName
		EXEC('Alter table RouterControl drop constraint ' + @ConstraintName)
	END

/* if by chance App_Type constraint is still present, get rid of it. */
	set @ConstraintName=NULL
	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

/* if by chance PK_ROUTERCONTROL constraint is still present, get rid of it. */
	set @ConstraintName=NULL
	SELECT @ConstraintName=CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE (CONSTRAINT_NAME='PK_ROUTERCONTROL')
	IF (@ConstraintName IS NOT NULL)
	BEGIN
		PRINT 'Dropping RouterControl constraint ' + @ConstraintName
		EXEC('Alter table RouterControl drop constraint ' + @ConstraintName)
	END


go

/* ---------------------------------------------- 
	drop the  (temporary) stored procs
-------------------------------------------------*/

drop procedure NV_ChgSchema_DropColumnAndItsConstraints
drop procedure NV_ChgSchema_DropTable

/* /////////////////////////////////////////////////////////////
  control DB
////////////////////////////////////////////////////////////// */


PRINT 'Inserting version information.'
declare @verCmd varchar(255)	
set @verCmd = 'INSERT INTO Version(Version_SVNumber, Version_Description) VALUES('
	+ '''1213686'', ''Update of Envy release 2.1 to release 2.2 database schema.'')'
EXEC(@verCmd)
go

/* /////////////////////////////////////////////////////////////
  ErrorMsgs DB
////////////////////////////////////////////////////////////// */

use errorMsgs
go

/* ---------------------------------------------- 
	Create the 'Drop Table' stored proc in ErrorMsgs DB (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
	PRINT 'Dropping ' + @tableName + ' table'
	EXEC('Drop table ' + @tableName )
END
go
/* ---------------------------------------------- */

EXEC('NV_ChgSchema_DropTable ''UserErrMsgs''')
EXEC('NV_ChgSchema_DropTable ''SysErrMsgs''')

/* ---------------------------------------------- 
	drop the (temporary) stored proc in ErrorMsgs DB
-------------------------------------------------
*/
drop procedure NV_ChgSchema_DropTable
go

