/* Control Schema Tables Version 6.0.1 */
/* Table generation (and version insert) statements 
	for the Control System database schema.
	ASSUMES all tables need to be created! 
	*/
/* 
                ****************
				**** DO NOT ****
                ****************
...GRATUITOUSLY MESS WITH THE FORMAT OF THE FIELDNAMES&DESCRIPTIONS 
WITHIN COMMENTS JUST AFTER THE "EXEC" FOR EACH TABLE BELOW!!! 

They're used for autogenerating the HTML page on ENVY team
website describing the DB.

But DO feel free to add ones in similar format, for new tables
or for tables/fields currently lacking descriptive text.
 
thanks
akh
*/

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


/* Set the name of the control database to the sql-active db
(it probably came in via /d flag)
*/


DECLARE @CONTROLDB nvarchar(30)
SET @CONTROLDB = '' /* uninitialized, or you could set it to something as an Override */

IF (@CONTROLDB = '')
BEGIN
		SET @CONTROLDB = db_name()
		IF (LEFT(@CONTROLDB,7)<>'control')
		BEGIN
			DECLARE @errmsg0 nvarchar(128)
			set @errmsg0='Aborting, not sure what DB to put tables in (active is ' + @CONTROLDB + ')'
			raiserror(@errmsg0, 20, 1) with log
		END
END
	
IF EXISTS (SELECT name FROM sysobjects WHERE OBJECTPROPERTY(id, N'IsTable') = 1 AND (name NOT LIKE 'sys%' AND name NOT LIKE 'dt%'))
	PRINT 'DB ' + @CONTROLDB + ' already contains tables, wont create them anew'
ELSE
BEGIN
PRINT 'Creating Tables for DB ' + @CONTROLDB
DECLARE @MNEMONIC_SIZE nvarchar(2)
DECLARE @NAME_SIZE nvarchar(3)
DECLARE @DESC_SIZE nvarchar(4)
DECLARE @PARAM_SIZE nvarchar(3)
set @MNEMONIC_SIZE = '8'
set @NAME_SIZE = '24'
set @DESC_SIZE = '255'
set @PARAM_SIZE = '50'

DECLARE @FILENAMEFORBACKUP   nvarchar(30)
DECLARE @VERSION    nvarchar(30)
DECLARE @PARTNUMBER nvarchar(15)


/* ---------------------------------------------
  IF YOU CHANGE THIS PARTNUMBER (in a file copied from this one),
   AND in the ControlSchemaUpdate_[oldver]to[newver].sql script!
*/
SET @PARTNUMBER = 'SV0238-06D0'
SET @VERSION = '6.0.9' /* likewise for version#! (see above reminder) */
/* ---------------------------------------------*/


	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.Host (
			Host_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED ,
			Host_Name nvarchar (' + @NAME_SIZE + ') NOT NULL,
			Host_LocalLogMode int NOT NULL DEFAULT(3),	
			Host_Params nvarchar (' + @PARAM_SIZE + ') NULL,
			Host_NameOfBackup nvarchar (' + @NAME_SIZE + ') NULL
		)'
		/*
		Host:The host table lists the computer names of system hosts.
		Host_ID:Host table primary key.
		Host_Name:Network name of this host.
		Host_LocalLogMode:Default logging level for this host.  See the level definitions from the App table.
		Host_Params:Free text parameter field.
		Host_NameOfBackup: Name of Backup (Secondary) Host, if any.
		*/

	)
	
	
	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.ControlPoint (
			ControlPoint_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED ,
			ControlPoint_Host int REFERENCES Host(Host_ID) NULL,
			ControlPoint_SetupParams nvarchar (' + @PARAM_SIZE + ') NULL,
			ControlPoint_Description nvarchar (' + @DESC_SIZE + ') NULL
		)'
		/*
		ControlPoint:The controlpoint table lists the individual points of control in the system..
		ControlPoint_ID:ControlPoint table primary key.
		ControlPoint_Host:Host computer on which this control point exists (NOT REALLY NULLOK--a trigger sets its Default to the first Host_ID).
		ControlPoint_SetupParams:Specific startup parameters for this control point.
		ControlPoint_Description:Free text description of this control point.
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.LevelSet (
			LevelSet_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED ,
			LevelSet_Name nvarchar (' + @NAME_SIZE + ') NOT NULL
		)'
		/*
		LevelSet:The level set table describes commonly grouped levels within a facility.
		LevelSet_ID:LevelSet table primary key.
		LevelSet_Name:Free text name of a level set.
		*/

	)

	/* #DEVICE */
	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.Device (
			Device_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED ,
			Device_Mnemonic nvarchar (' + @MNEMONIC_SIZE + ') NOT NULL ,
			Device_Description nvarchar (' + @DESC_SIZE + ') NULL ,
			Device_LevelSet int NOT NULL REFERENCES LevelSet(LevelSet_ID)
		)'
		/*
		Device:This table enumerates every non-router device in the NV9000 database.
		Device_ID:Device table primary key.
		Device_Mnemonic:Free text display mnemonic of this device.
		Device_Description:Optional free text description of this device.
		Device_LevelSet:Level set to which this device belongs.
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.VirtualLevel (
			VirtualLevel_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED ,
			VirtualLevel_Mnemonic nvarchar (' + @MNEMONIC_SIZE + ') NOT NULL
		)'
		/*
		VirtualLevel:This table allows level mnemonics to be mapped to levelsets.
		VirtualLevel_ID:VirtualLevel primary key.
		VirtualLevel_Mnemonic:Free text display mnemonic of this virtual level.
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.VirtualLevelGroup (
			VirtualLevelGroup_ParentVirtualLevel int NOT NULL REFERENCES VirtualLevel(VirtualLevel_ID),
			VirtualLevelGroup_ChildVirtualLevel int NOT NULL REFERENCES VirtualLevel(VirtualLevel_ID),
			CONSTRAINT PK_VIRTUALLEVELGROUP PRIMARY KEY
			(
				VirtualLevelGroup_ParentVirtualLevel,
				VirtualLevelGroup_ChildVirtualLevel
			)
		)'

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.Router (
			Router_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED ,
			Router_Name nvarchar (' + @NAME_SIZE + ') NOT NULL ,
			Router_PrimaryHost int NULL REFERENCES Host(Host_ID)
		)'
		/*
		Router:This table lists all routers in the system.
		Router_ID:Primary Key
		Router_Name:Free text description of router.
		Router_PrimaryHost:Primary host from which to control this router (NOT REALLY NULLOK--a trigger sets its Default to the first Host_ID)..
		*/

	)
	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.RouterControl (
			RouterControl_Router int NOT NULL REFERENCES Router(Router_ID),
			RouterControl_ControlPoint int NOT NULL REFERENCES ControlPoint(ControlPoint_ID),
			RouterControl_Protocol int NOT NULL CONSTRAINT DF__RouterControl_Protocol DEFAULT(7),
			RouterControl_ControlParams nvarchar (' + @PARAM_SIZE + ') NULL,
			RouterControl_ControlMode int NOT NULL DEFAULT(1)
		)'
		/*
		RouterControl:This table describes control point usage by routers.
		RouterControl_Router:Router that is being controlled.
		RouterControl_ControlPoint:Control point that is used to control this router.
		RouterControl_Protocol:Protocol that is used to communicate with this router 
		RouterControl_ControlParams:Protocol specific parameters.
		RouterControl_ControlMode:Usage mode of this control point and protocol for this router 
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.App (
			App_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED,
			App_Name nvarchar(32) NULL,
			App_ControlPoint int NULL REFERENCES ControlPoint(ControlPoint_ID),
			App_Type int NOT NULL,
			App_Params nvarchar (' + @PARAM_SIZE + ') NULL,
			App_RunCondition int NOT NULL DEFAULT(0)
				CHECK(App_RunCondition IN (0, 1)),
			App_RunPriority int NOT NULL DEFAULT(0)
				CHECK(App_RunPriority IN (0, 1)),
			App_LocalLogMode int NOT NULL DEFAULT(1)
				CHECK(App_LocalLogMode IN (0, 1, 2, 3, 4, 5, 6, 7, 255))
		)'
		/*
		App:The app table lists applications that run on specific hosts.
		App_ID:App table primary key.
		App_Name:Optional application name.
		App_ControlPoint:Control point used by this application.
		App_Type:Application type 
		App_Params:Application specific launch parameters.
		App_RunCondition:Application launch rules 
		App_RunPriority:Application process priority 
		App_LocalLogMode:Application default logging mode 
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.PhysLevel (
			PhysLevel_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED ,
			PhysLevel_Router int NOT NULL REFERENCES Router(Router_ID),
			PhysLevel_Number int NOT NULL ,
			PhysLevel_InputStart int NOT NULL CONSTRAINT DF__PhysLevel_InputStart DEFAULT(1),
			PhysLevel_InputEnd int NOT NULL,
			PhysLevel_OutputStart int NOT NULL CONSTRAINT DF__PhysLevel_OutputStart DEFAULT(1),
			PhysLevel_OutputEnd int NOT NULL,
			PhysLevel_InputProtect int NOT NULL CONSTRAINT DF__PhysLevel_InputProtect DEFAULT(3)
				CHECK(PhysLevel_InputProtect in (1, 2, 3)),
			PhysLevel_InputLock int NOT NULL CONSTRAINT DF__PhysLevel_InputLock DEFAULT(3)
				CHECK(PhysLevel_InputLock in (1, 2, 3)),
			PhysLevel_OutputProtect int NOT NULL DEFAULT(1)
				CHECK(PhysLevel_OutputProtect in (1, 2, 3)),
			PhysLevel_OutputLock int NOT NULL DEFAULT(1)
				CHECK(PhysLevel_OutputLock in (1, 2, 3)),
			PhysLevel_SharedControl int NOT NULL DEFAULT(0)
				CHECK(PhysLevel_SharedControl in (0, 1)),
			PhysLevel_ChopInterval int NOT NULL CONSTRAINT DF__PhysLevel_ChopInterval DEFAULT(6),
			PhysLevel_SignalType int NOT NULL DEFAULT(1),
			PhysLevel_Name nvarchar (' + @NAME_SIZE + ') NULL,
			PhysLevel_NumVirtualXPTs int NOT NULL DEFAULT(0),
			PhysLevel_VirtualXPTDefInput int NOT NULL DEFAULT(0)
		)'
		/*
		PhysLevel:Table of physical levels within the system.
		PhysLevel_ID:Physical level primary key.
		PhysLevel_Router:Router that encompasses this level.
		PhysLevel_Number:Level number configured within the router.
		PhysLevel_InputStart:Starting logical input port.
		PhysLevel_InputEnd:Ending logical input port.
		PhysLevel_OutputStart:Starting logical output port.
		PhysLevel_OutputEnd:Ending logical output port.
		PhysLevel_InputProtect:Type of source protect used on this level. 
		PhysLevel_InputLock:Type of source lock used on this level. 
		PhysLevel_OutputProtect:Type of destination protect used on this level. 
		PhysLevel_OutputLock:Type of destination lock used on this router. 
		PhysLevel_SharedControl:Flag indicating shared control on this level 
		PhysLevel_ChopInterval:Chop interval for this level, in fields.
		PhysLevel_Name:Optional PhysLevel name.
		PhysLevel_NumVirtualXPTs:Defines the number of virtual XPTs on this level
		PhysLevel_VirtualXPTDefInput:Specifies the startup input for virtual XPTs
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.LevelSetDetail (
			LevelSetDetail_LevelSet int NOT NULL REFERENCES LevelSet(LevelSet_ID),
			LevelSetDetail_VirtualLevel int NOT NULL REFERENCES VirtualLevel(VirtualLevel_ID),
			LevelSetDetail_PhysLevel int NOT NULL REFERENCES PhysLevel(PhysLevel_ID),
			LevelSetDetail_DisplayIndex int NOT NULL,
			LevelSetDetail_FreeSource int NULL REFERENCES Device(Device_ID)
			CONSTRAINT PK_LEVELSETDETAIL PRIMARY KEY CLUSTERED
			(
				LevelSetDetail_LevelSet,
				LevelSetDetail_VirtualLevel
			)
		)'
		/*
		LevelSetDetail:The level set detail table resolves the relationship between virtual and physical levels within a levelset.
		LevelSetDetail_LevelSet:Level set with which this virtual level/physical level pair is associated.
		LevelSetDetail_VirtualLevel:Virtual level within this levelset.
		LevelSetDetail_PhysLevel:Physical level within this level set on which the virtual level is implemented.
		LevelSetDetail_DisplayIndex:Index at which this virtual level is displayed on control surfaces.
		LevelSetDetail_FreeSource:Device ID of the free source for this virtual level in this level set. DO NOT configure a Free Source for a Data Router Control level.
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.PhysConn (
			PhysConn_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED,
			PhysConn_Description nvarchar (' + @DESC_SIZE + ') NULL,
			PhysConn_Device int NOT NULL REFERENCES Device(Device_ID),
			PhysConn_VirtualLevel int NOT NULL REFERENCES VirtualLevel(VirtualLevel_ID),
			PhysConn_PhysLevelPort int NOT NULL,
			PhysConn_PhysLevelPortDir int NOT NULL
				CHECK (PhysConn_PhysLevelPortDir IN (1, 2, 3))
		)'
		/*
		PhysConn:This table lists the connections on a virtual level.
		PhysConn_ID:Physical connection primary key.
		PhysConn_Description:Free text label of this connection.
		PhysConn_Device:Device involved in this connection.
		PhysConn_VirtualLevel:Virtual Level of this port on this device.
		PhysConn_PhysLevelPort:Physical level port number.
		PhysConn_PhysLevelPortDir:Direction of this port.
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.Suffix (
			Suffix_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED ,
			Suffix_Name nvarchar (4) NOT NULL,
		)'
		/*
		Suffix:Table of user defined device descriptor strings.
		Suffix_ID:Suffix table primary key.
		Suffix_Name:Free text device descriptor string.
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.Category (
			Category_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED ,
			Category_Mnemonic nvarchar (' + @MNEMONIC_SIZE + ') NOT NULL,
			Category_Description nvarchar (' + @DESC_SIZE + ') NULL
		)'
		/*
		Category:Table of user defined categories.
		Category_ID:Category table primary key.
		Category_Mnemonic:Free text display mnemonic of this category.
		Category_Description:Description of this category.
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.CategoryDetail (
			CategoryDetail_Category int NOT NULL REFERENCES Category(Category_ID),
			CategoryDetail_Device int NOT NULL REFERENCES Device(Device_ID),
			CategoryDetail_Index int NULL,
			CategoryDetail_Suffix1 int NULL REFERENCES Suffix(Suffix_ID),
			CategoryDetail_Suffix2 int NULL REFERENCES Suffix(Suffix_ID),
			CategoryDetail_Suffix3 int NULL REFERENCES Suffix(Suffix_ID),
			CategoryDetail_Suffix4 int NULL REFERENCES Suffix(Suffix_ID),
			CategoryDetail_Suffix5 int NULL REFERENCES Suffix(Suffix_ID),
			CategoryDetail_Suffix6 int NULL REFERENCES Suffix(Suffix_ID),
			CategoryDetail_Suffix7 int NULL REFERENCES Suffix(Suffix_ID),
			CategoryDetail_Suffix8 int NULL REFERENCES Suffix(Suffix_ID),
			CONSTRAINT PK_CATEGORYDETAIL PRIMARY KEY CLUSTERED
			(
				CategoryDetail_Category,
				CategoryDetail_Device
			)
		)'
		/*
		CategoryDetail:This table enumerates the devices in the category.
		CategoryDetail_Category:Category this entry is for.
		CategoryDetail_Device:Device this entry represents.
		CategoryDetail_Index:Selection index for this device in this Category (obsolete; for bkwd compatibility).
		CategoryDetail_Suffix1:Descriptor text for this device in this Category.
		CategoryDetail_Suffix2:Descriptor text for this device in this Category.
		CategoryDetail_Suffix3:Descriptor text for this device in this Category.
		CategoryDetail_Suffix4:Descriptor text for this device in this Category.
		CategoryDetail_Suffix5:Descriptor text for this device in this Category.
		CategoryDetail_Suffix6:Descriptor text for this device in this Category.
		CategoryDetail_Suffix7:Descriptor text for this device in this Category.
		CategoryDetail_Suffix8:Descriptor text for this device in this Category.
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.NvUser (
			NvUser_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED
				CHECK(NvUser_ID < 65535),
			NvUser_Name nvarchar (32) NULL
		)'
		/*
		NvUser:This table is used to assign ID's to individual users of the system.
		NvUser_ID:NvUser table primary key.
		NvUser_Name:User name for a single user.
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.Path (
			Path_ID int
				IDENTITY(1,1)
				PRIMARY KEY CLUSTERED,
			Path_FilePath nvarchar (' + @DESC_SIZE + ') NOT NULL,
			Path_FileType int NOT NULL
		)'
		/*
		Path:This table identifies paths used in the system.
		Path_ID:Path table primary key.
		Path_FilePath:File path
		Path_FileType:Type of file 
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.Panel (
			Panel_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED,
			Panel_PanelID int NOT NULL,
			Panel_Name nvarchar(8) NULL,
			Panel_Path int NOT NULL REFERENCES Path(Path_ID),
			Panel_User int NOT NULL REFERENCES NvUser(NvUser_ID),
			Panel_Language int NOT NULL DEFAULT(1),
			Panel_Description nvarchar(' + @DESC_SIZE + ') NULL
		)'
		/*
		Panel:This table describes the control panels in the system.
		Panel_ID:Panel table primary key.
		Panel_PanelID:Unique ID for a single hardware panel.
		Panel_Name:*8* char display name for this panel.
		Panel_Path:System configuration file for this panel.
		Panel_User:User id for this panel.
		Panel_Language:Language used for this panel.
		Panel_Description:Panel description.
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.GUI (
			GUI_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED,
			GUI_CompName nvarchar(32) NOT NULL DEFAULT(''DEFAULT''),
			GUI_User int NOT NULL REFERENCES NvUser(NvUser_ID),
			GUI_Path int NOT NULL REFERENCES Path(Path_ID),
			GUI_Language int NOT NULL DEFAULT(1),
			GUI_Description nvarchar(' + @DESC_SIZE + ') NULL
		)'
		/*
		GUI:This table is used to assign an ID and a configuration path to an individual control GUI.
		GUI_ID:GUI table primary key.
		GUI_CompName:Computer name where the GUI is run from.
		GUI_User:User id for this GUI..
		GUI_Path:System configuration file for this GUI.
		GUI_Language:Language used for this GUI, 
		GUI_Description:Free text GUI description.
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.Salvo (
			Salvo_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED,
			Salvo_Mnemonic nvarchar(' + @MNEMONIC_SIZE + ') NOT NULL,
			Salvo_Description nvarchar(' + @DESC_SIZE + ') NULL,
			Salvo_Path int NOT NULL REFERENCES Path(Path_ID)
		)'
		/*
		Salvo:The salvo table stores the locations of system salvo files.
		Salvo_ID:Salvo table primary key.
		Salvo_Mnemonic:Salvo display name.
		Salvo_Description:Free text description of the salvo.
		Salvo_Path:Salvo file path.
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.Version (
			Version_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED,
			Version_Timestamp DATETIME NOT NULL DEFAULT GETDATE(),
			Version_SVNumber nvarchar(15) NOT NULL,
			Version_Description nvarchar(' + @DESC_SIZE + ') NOT NULL,
			Version_SWVersion nvarchar(' + @NAME_SIZE + ') NULL
		)'
		/*
		Version:This table is used to track DB versions and schema updates
		Version_ID:Version table primary key.
		Version_Timestamp:Timestamp of when a change was recorded - filled automatically.
		Version_SVNumber:SV number of the database scripts.
		Version_Description:Description of schema changes implemented in this SV.
		Version_SWVersion:This Version (Rev) #.
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.SalvoList (
			SalvoList_ID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			SalvoList_Mnemonic nvarchar(' + @MNEMONIC_SIZE + ') NOT NULL
		)'
		/*
		SalvoList:The SalvoList table identifies salvo lists.
		SalvoList_ID:Primary key for the SalvoList table.
		SalvoList_Mnemonic:Mnemonic for this salvo list.
		*/

	)

	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.SalvoListDetail (
			SalvoListDetail_SalvoList int NOT NULL REFERENCES SalvoList(SalvoList_ID),
			SalvoListDetail_Salvo int NOT NULL REFERENCES Salvo(Salvo_ID),
			SalvoListDetail_Index int NOT NULL
			CONSTRAINT PK_SALVOLISTDETAIL PRIMARY KEY CLUSTERED
			(
				SalvoListDetail_SalvoList,
				SalvoListDetail_Index
			)
		)'
		/*
		SalvoListDetail:The SalvoListDetail table enumerates the salvos in a salvo list.
		SalvoListDetail_SalvoList:Salvo list that this entry belongs to.
		SalvoListDetail_Salvo:Salvo id for this list entry.
		SalvoListDetail_Index:Index of a salvo within a list.
		*/

	)

      
	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.NvSysParam (
			NvSysParam_ID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			NvSysParam_Name nvarchar (' + @NAME_SIZE + ') NOT NULL UNIQUE,
			NvSysParam_Value nvarchar(255) NULL,
			NvSysParam_Desc nvarchar (' + @DESC_SIZE + ') NULL
		)'
		/*
		NvSysParam:Specifies name/value pairs for global settings
		NvSysParam_ID:Primary Key for this table.
		NvSysParam_Name:Name of a particular setting, must be unique.
		NvSysParam_Value:Value of this param, meaning is specific to each setting.
		NvSysParam_Desc:Description of what this setting does.
		*/
	)
	
	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.XptExclusion (
			XptExclusion_PhysLevel int NOT NULL REFERENCES PhysLevel(PhysLevel_ID),
			XptExclusion_Output int NOT NULL,
			XptExclusion_Input int NOT NULL
			CONSTRAINT PK_XPTEXCLUSION PRIMARY KEY CLUSTERED
			(
				XptExclusion_PhysLevel,
				XptExclusion_Output,
				XptExclusion_Input
			)
		)'
		/*
		XptExclusion:The exclusion table specifies the prohibited port-to-port connections for the PhysLevels.
		XptExclusion_PhysLevel:PhysLevel with the prohibited (excluded) port-to-port connection.
		XptExclusion_Output:Output port of the prohibited (excluded) port-to-port connection.
		XptExclusion_Input:Input port of the prohibited (excluded) port-to-port connection.
		*/
	)

	EXEC('CREATE TABLE [dbo].[Alias] (
			Alias_ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
			Alias_PhysLevel int NOT NULL REFERENCES PhysLevel(PhysLevel_ID),
			Alias_PhysLevelPort int NOT NULL, 
			Alias_Mnemonic nvarchar (' + @MNEMONIC_SIZE + ') NOT NULL 
		)'
		/*
		Alias:The Alias table specifies mnemonic alternatives for devices on specific levels
		Alias_PhysLevel:Physical level for this alias
		Alias_PhysLevelPort:Port on which to use this alias
		Alias_Mnemonic:Mnemonic to display
		*/
	)

	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)
	)')

	EXEC('CREATE TABLE dbo.Tieline2 (
		Tieline2_ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
		Tieline2_ControlSystem int NOT NULL REFERENCES dbo.ControlSystem(ControlSystem_ID) DEFAULT 1,
		Tieline2_ControlSystem_Tieline2 int NOT NULL DEFAULT 0,
		Tieline2_Cost INT NOT NULL DEFAULT 1,
		Tieline2_Name nvarchar (24) NOT NULL,
		Tieline2_Description nvarchar (255),
		CONSTRAINT UNIQUE_TIELINE2 UNIQUE
		(
			Tieline2_ControlSystem,
			Tieline2_ControlSystem_Tieline2
		),
		CONSTRAINT CHECK_TIELINE2 CHECK 
		( 
              (Tieline2_ControlSystem = 1) or 
	  	  ( Tieline2_ControlSystem != 1 and Tieline2_ControlSystem_Tieline2 != 0 )
		)
	)')

	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)
	)')
	
	EXEC('CREATE TABLE dbo.Tieline2GroupBinding ( 
		Tieline2GroupBinding_Tieline2Group int NOT NULL REFERENCES Tieline2Group(Tieline2Group_ID),
		Tieline2GroupBinding_Tieline2 int NOT NULL REFERENCES Tieline2(Tieline2_ID),
		CONSTRAINT PK_TIELINEGROUPBINDING PRIMARY KEY
		(
			Tieline2GroupBinding_Tieline2Group,
			Tieline2GroupBinding_Tieline2
		)
	)')

	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
			)
	)')
	
	EXEC('CREATE TABLE dbo.SignalType (
		SignalType_ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
		SignalType_Name nvarchar (24) NOT NULL,
		SignalType_Description nvarchar (255)
	)')

	EXEC('CREATE TABLE dbo.Tieline2PortBinding (
		Tieline2PortBinding_ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
		Tieline2PortBinding_Tieline2 int NOT NULL REFERENCES Tieline2(Tieline2_ID),
		Tieline2PortBinding_PhysLevel int NOT NULL REFERENCES PhysLevel(PhysLevel_ID),
		Tieline2PortBinding_Direction int NOT NULL DEFAULT 1	
			CHECK(Tieline2PortBinding_Direction in (1, 2)),
		Tieline2PortBinding_Port int NOT NULL DEFAULT 0,
		CONSTRAINT UNIQUE_TIELINE2PORTBINDING UNIQUE NONCLUSTERED
		(
			Tieline2PortBinding_PhysLevel,
			Tieline2PortBinding_Direction,
			Tieline2PortBinding_Port
		)
	)')

	EXEC('CREATE TABLE dbo.Tieline2SignalBinding (
		Tieline2SignalBinding_PortBinding int NOT NULL REFERENCES Tieline2PortBinding(Tieline2PortBinding_ID),
		Tieline2SignalBinding_SignalType int NOT NULL REFERENCES SignalType(SignalType_ID)
		CONSTRAINT PK_TIELINE2SIGNALBINDING PRIMARY KEY
		(
			Tieline2SignalBinding_PortBinding,
			Tieline2SignalBinding_SignalType
		)
	)')

	EXEC('CREATE TABLE dbo.VirtualLevelSignalBinding (
		VirtualLevelSignalBinding_VirtualLevel int NOT NULL REFERENCES VirtualLevel(VirtualLevel_ID),
		VirtualLevelSignalBinding_SignalType int NOT NULL REFERENCES SignalType(SignalType_ID),
		CONSTRAINT PK_VIRTUALLEVELSIGNALBINDING PRIMARY KEY
		(
			VirtualLevelSignalBinding_VirtualLevel,
			VirtualLevelSignalBinding_SignalType
		)
	)')

	EXEC('CREATE TABLE dbo.Tieline2Path (
		Tieline2Path_ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
		Tieline2Path_SourcePhysLevel int NOT NULL REFERENCES PhysLevel(PhysLevel_ID),
		Tieline2Path_SourceSignalType int NOT NULL REFERENCES SignalType(SignalType_ID),
		Tieline2Path_DestinationPhysLevel int NOT NULL REFERENCES PhysLevel(PhysLevel_ID),
		Tieline2Path_DestinationSignalType int NOT NULL REFERENCES SignalType(SignalType_ID),
		Tieline2Path_HopCount int NOT NULL DEFAULT 1,
		Tieline2Path_Cost int NOT NULL DEFAULT 1
		)')

	EXEC('create index IDX_T2PATH 
		on Tieline2Path ( Tieline2Path_SourcePhysLevel, 
		Tieline2Path_SourceSignalType, 
		Tieline2Path_DestinationPhysLevel, 
		Tieline2Path_DestinationSignalType)')

	EXEC('CREATE TABLE dbo.Tieline2PathDetail (
		Tieline2PathDetail_Tieline2Path int NOT NULL REFERENCES Tieline2Path(Tieline2Path_ID),
		Tieline2PathDetail_SignalType int NOT NULL REFERENCES SignalType(SignalType_ID),
		Tieline2PathDetail_InputTieline int REFERENCES Tieline2(Tieline2_ID),
		Tieline2PathDetail_OutputTieline int REFERENCES Tieline2(Tieline2_ID),
		Tieline2PathDetail_PhysLevel int NOT NULL REFERENCES PhysLevel(PhysLevel_ID),
		Tieline2PathDetail_InputPort int NOT NULL,
		Tieline2PathDetail_OutputPort int NOT NULL,
		Tieline2PathDetail_SegmentIndex int NOT NULL,
		CONSTRAINT PK_TIELINEPATHDETAILS PRIMARY KEY
		(
			Tieline2PathDetail_Tieline2Path,
			Tieline2PathDetail_PhysLevel,
			Tieline2PathDetail_InputPort,
			Tieline2PathDetail_OutputPort
		)
	)')

	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
	)')

	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()
	)')
	
      /* add this version info into the version table */
    PRINT 'Inserting version information.'
    
 	EXEC( 'INSERT INTO ' + @CONTROLDB + '.dbo.Version(
		Version_SWVersion,Version_SVNumber, Version_Description) VALUES('''
		+ @VERSION + ''',''' + @PARTNUMBER +
		''',''Initial database schema, release version '+@VERSION+''')'
	)     

PRINT  CHAR(13) + CHAR(10)+ N'Finished adding tables to v' + @VERSION + ' ' + @CONTROLDB + ' Control Schema.'
END /* creating tables */
go