/* Control Schema Tables Version 3.6.0 */
/* 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)
*/

use control

DECLARE @CONTROLDB varchar(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 varchar(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 varchar(2)
DECLARE @NAME_SIZE varchar(3)
DECLARE @DESC_SIZE varchar(4)
DECLARE @PARAM_SIZE varchar(3)
set @MNEMONIC_SIZE = '8'
set @NAME_SIZE = '24'
set @DESC_SIZE = '255'
set @PARAM_SIZE = '50'

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


/* ---------------------------------------------
  IF YOU CHANGE THIS PARTNUMBER (in a file copied from this one),
  BE SURE TO ALSO CHANGE IT IN ConfigGUI\consts_i.asp!
   (as of Jun 2002, it should match part# of "NV9000 SYSTEM SETUP")
   AND in the ControlSchemaUpdate_[oldver]to[newver].sql script!
*/
SET @PARTNUMBER = 'SV0238-03H0'
SET @VERSION = '3.6.0' /* likewise for version#! (see above reminder) */
/* ---------------------------------------------*/


	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.Host (
			Host_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED ,
			Host_Name varchar (' + @NAME_SIZE + ') NOT NULL,
			Host_LocalLogMode int NOT NULL DEFAULT(3),	
			Host_Params varchar (' + @PARAM_SIZE + ') NULL,
			Host_NameOfBackup varchar (' + @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 varchar (' + @PARAM_SIZE + ') NULL,
			ControlPoint_Description varchar (' + @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 varchar (' + @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 varchar (' + @MNEMONIC_SIZE + ') NOT NULL ,
			Device_Description varchar (' + @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 varchar (' + @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.Router (
			Router_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED ,
			Router_Name varchar (' + @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 varchar (' + @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 varchar(32) NULL,
			App_ControlPoint int NULL REFERENCES ControlPoint(ControlPoint_ID),
			App_Type int NOT NULL,
			App_Params varchar (' + @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 varchar (' + @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 varchar (' + @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 varchar (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 varchar (' + @MNEMONIC_SIZE + ') NOT NULL,
			Category_Description varchar (' + @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 varchar (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 varchar (' + @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 varchar(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 varchar(' + @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 varchar(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 varchar(' + @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 varchar(' + @MNEMONIC_SIZE + ') NOT NULL,
			Salvo_Description varchar(' + @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 varchar(15) NOT NULL,
			Version_Description varchar(' + @DESC_SIZE + ') NOT NULL,
			Version_SWVersion varchar(' + @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 varchar(' + @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.ConfigIdentity (
			ConfigIdentity_ID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			ConfigIdentity_Name varchar(' + @NAME_SIZE + ') NOT NULL,
			ConfigIdentity_Description varchar(' + @DESC_SIZE + ') NULL
		)'
		/*
		ConfigIdentity:This table is used to describe a particular configuration database.
		ConfigIdentity_ID:Primary Key for the ConfigIdentity Table
		ConfigIdentity_Name:User supplied database name.
		ConfigIdentity_Description:User supplied database description.
		*/

	)

	EXEC( 'Create Table ' + @CONTROLDB + '.dbo.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(' + @PARAM_SIZE + ') NULL,
			AppPair_Description varchar(' + @DESC_SIZE + ') NULL
		)'
		/*
		AppPair:Table of application pairs for redundancy.
		AppPair_Primary:Primary application.
		AppPair_Secondary:Secondary application.
		AppPair_Params:Application specific parameters.
		AppPair_Description:Free text description.
		*/

	)
	
/* ---- Start of InterLevelsetMap and tieline tables -------*/	

	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))
		)'
		/*
		InterLvlsetMap:Specifies (with Details) how to connect an Upstream  & Downstream levelset.
		InterLvlsetMap_ID:InterLvlsetMap table primary key.
		InterLvlsetMap_Name:Name of this ordered pair of levelsets.
		InterLvlsetMap_UpstrLvlset:Upstream ("Source") Levelset.
		InterLvlsetMap_DnstrLvlset:Downstream ("Dest.") Levelset.  Must be different from Upstream one.
		InterLvlsetMap_DefaultConns:If true, "no tieline needed" (means that NV9000 feels free to do Takes between same-ID VirtualLevels of the up&dn levelsets,IF they're on the same PhysLevel.  Also means that no ILSM Details are needed.
		*/

	)


	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
		)'
		/*
		InterLSMapDetail:Specifies an ordered pair of VirtualLevels for connecting an Upstream  & Downstream levelset.
		InterLSMapDetail_ID:InterLSMapDetail table primary key.
		InterLSMapDetail_InterLvlsetMap:Which InterLvlsetMap the entry belongs to.
		InterLSMapDetail_UpstrVL:Upstream ("Source") Virtual Level.
		InterLSMapDetail_DnstrVL:Downstream ("Dest.") Virtual Level.
		InterLSMapDetail_TielineNeeded:Is a tieline needed to perform this connection? (0,1,2)
		*/

	)
	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_Description varchar(' + @DESC_SIZE + ') NULL,
			TielineClass_PreferredDsts int NULL REFERENCES Category(Category_ID),
			TielineClass_Params varchar (' + @PARAM_SIZE + ') NULL
		)'
		/*
		TielineClass:Specifies characteristics of a class such that all tielines that belong to it are interchangeable.
		TielineClass_ID:TielineClass table primary key.
		TielineClass_Name:Name of the TielineClass. Avoid using &lt; or &gt;
		TielineClass_InterLvlsetMap:Specifies the up&dn levelsets to be connected by tielines of this Class.
		TielineClass_Description:Whatever info you want...
		TielineClass_PreferredDsts:Category of dest devices to get the  Red Carpet treatment.
		TielineClass_Params:Free text parameter field.
		*/
	)
	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.TLClassDetail (
			TLClassDetail_TielineClass int NOT NULL REFERENCES TielineClass(TielineClass_ID),
			TLClassDetail_DnstrVL int NOT NULL REFERENCES VirtualLevel(VirtualLevel_ID)
		)'
		/*
		TLClassDetail:Specifies which between-level connections must be implementable using any tieline of the Class.
		TLClassDetail_TielineClass:Which tielineClass this Detail is for
		TLClassDetail_DnstrVL:Which of the TLC's InterLevelsetMapDetails the tielines of this class must implement. (i.e., need not necessarily implement all of them!)
		*/
	)
	/* on read-in of config, we'll need to generate the expected ls,vl:ls,vl of the tielineclass from the InterLSMap&Details & dnstrVLs
	*/


	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))
		)'
		/*
		Tieline:Specifies the relevant info about this pattern of physconns and what it can be used to do.
		Tieline_ID:Primary Key for this table.
		Tieline_Name:Name of this tieline.
		Tieline_TielineClass:Class to which this tieline must conform.
		Tieline_UpstrDevice:Configured "Device" which has the upstream Outputs of this Tieline.
		Tieline_DnstrDevice:Configured "Device" which has the downstream Inputs of this Tieline.
		Tieline_DefaultState:Dynamic state of this Tieline at startup.
		*/
	)
/* ---- End of InterLevelsetMap and tieline tables -------*/	
      
	EXEC( 'CREATE TABLE ' + @CONTROLDB + '.dbo.NvSysParam (
			NvSysParam_ID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			NvSysParam_Name varchar (' + @NAME_SIZE + ') NOT NULL UNIQUE,
			NvSysParam_Value varchar(255) NULL,
			NvSysParam_Desc varchar (' + @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 varchar (' + @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
		*/
	)

      /* 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