/*
	Update from DB Schema 1.3 to 2.0
	Modify the Router table to include Router_PrimaryHost
	Drop primary key of RouterControl table.
	Modify the PhysLevel table, adding PhysLevel_ChopInterval 
	Modify the Host table, adding Host_LocalLogMode and Host_Params
	Add the AppPair table
	Modify the ControlPoint table, adding NOT NULL to ControlPoint_Host
	add the ErrMsgs table
*/

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

PRINT 'Altering Router table, adding Router_PrimaryHost' 
alter table Router
	add Router_PrimaryHost int NULL REFERENCES Host(Host_ID)
go

PRINT 'Altering RouterControl table, dropping key constraint.'
alter table RouterControl
	drop PK_ROUTERCONTROL
go

PRINT 'Altering PhysLevel table, adding PhysLevel_ChopInterval' 
alter table PhysLevel
	add PhysLevel_ChopInterval int NOT NULL DEFAULT(0)
go

PRINT 'Altering Host table, adding Host_LocalLogMode and Host_Params' 
alter table Host
	add Host_LocalLogMode int NOT NULL DEFAULT(3)	
go

alter table Host
	add Host_Params varchar(50) NULL
go

PRINT 'Altering ControlPoint table, adding NOT NULL to ControlPoint_Host'
alter table ControlPoint
	alter column ControlPoint_Host int NOT NULL
go

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[AppPair]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'Creating AppPair table.'
Create Table AppPair (
	AppPair_Primary int NOT NULL PRIMARY KEY REFERENCES APP(APP_ID),
	AppPair_Secondary int NOT NULL UNIQUE REFERENCES APP(APP_ID),
	AppPair_Params varchar(50) NULL,
	AppPair_Description varchar(255) NULL
)
END
ELSE
	PRINT 'Not creating AppPair table, it already exists'
	
PRINT 'Inserting version information.'
INSERT INTO Version(Version_SVNumber, Version_Description) VALUES( '1147476A0', 'Update of Envy release 1.3 to release 2.0 database schema.')
go	


use ErrorMsgs
go
IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[ErrMsgs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE ErrMsgs (
	ErrMsgs_ID int
		IDENTITY(1, 1)
		PRIMARY KEY CLUSTERED,
	ErrMsgs_English varchar (255) NULL ,
	ErrMsgs_Other varchar (255) NULL,
	ErrMsgs_ShortEnglish varchar(8) NULL,
	ErrMsgs_ShortOther varchar(8) NULL
)
END
ELSE
	PRINT 'Not creating ErrMsgs table, it already exists'
go



