/* Create ErrorMsgs db if not already present; 
	back up (or create and call script to add tables/version) Control System (live or edit) database schema */

/* ------ change (only) these when the version changes --------------*/

DECLARE @VER varchar(8)
SET @VER='8.2.0'

/* --------------------------------------------*/
DECLARE @CONTROLDB varchar(30)
SET @CONTROLDB = 'control' 

DECLARE @FILENAMEFORBACKUP   varchar(30)
set @FILENAMEFORBACKUP = 'DB_preVer'+ Replace(@VER,'.','_') +'.Bak' /* LEAVE THE "PRE"! since its used for backup of pre-existing DB if any */

IF EXISTS (SELECT * from master..sysdatabases where name = @CONTROLDB) 
	BEGIN /* back it up */ 
	
		EXEC('BACKUP DATABASE ' + @CONTROLDB 
			+ ' TO DISK='+'''C:\nvision\envy\data\bak\' + @FILENAMEFORBACKUP
			+ ''' WITH DESCRIPTION='+'''Backup of existing ' + @CONTROLDB 
			+ ' database prior to version '+@VER+' install''')
		PRINT 'Backing up existing ' + @CONTROLDB + ' database to file'			
	END
ELSE /* create it */
	BEGIN 
		PRINT 'Creating ' + @CONTROLDB

		EXEC ( 'CREATE DATABASE ' + @CONTROLDB +
			' ON
			( NAME = ' + @CONTROLDB + '_dat,
			  FILENAME = ''c:\nvision\envy\data\' + @CONTROLDB + '.mdf'',
			  SIZE = 50MB,
			  MAXSIZE = 1000MB,
			  FILEGROWTH = 10MB )'
		)
		
	END
	/* execute the controlschema[ver].sql script 
	(only adds tables if none already exist)
	*/
DECLARE @cmd sysname
SET @cmd = 'master.dbo.xp_cmdshell ''osql -n -E -d ' + @CONTROLDB
		+ ' -i C:\nvision\envy\install\ControlSchemaTables_' + @VER + '.sql'' '
PRINT 'Executing ' + @cmd
	EXEC( @cmd)

/* --------------------------------------------*/

DECLARE @MNEMONIC_SIZE varchar(2)
DECLARE @NAME_SIZE varchar(3)
DECLARE @DESC_SIZE varchar(4)
DECLARE @PARAM_SIZE varchar(3)
DECLARE @ERRORMSGDB varchar(30)

/* Modify this line if needed,to set the name of the error message database */
set @ERRORMSGDB = 'ErrorMsgs'

set @MNEMONIC_SIZE = '8'
set @NAME_SIZE = '24'
set @DESC_SIZE = '255'
set @PARAM_SIZE = '50'


IF exists (select * from master..sysdatabases where name = @ERRORMSGDB)
	PRINT 'FYI, ' + @ERRORMSGDB + ' database already exists.'
ELSE
BEGIN /* create it */
	EXEC( 'CREATE DATABASE ' + @ERRORMSGDB +
		' ON
		( NAME = ' + @ERRORMSGDB + '_dat,
		  FILENAME = ''c:\nvision\envy\data\errmsgs.mdf'',
		  SIZE = 1,
		  MAXSIZE = 5,
		  FILEGROWTH = 1 )'
	)

	EXEC( 'CREATE TABLE ' + @ERRORMSGDB + '.dbo.ErrMsgs (
			ErrMsgs_ID int
				IDENTITY(1, 1)
				PRIMARY KEY CLUSTERED,
			ErrMsgs_English varchar (' + @DESC_SIZE + ') NULL ,
			ErrMsgs_Other varchar (' + @DESC_SIZE + ') NULL ,
			ErrMsgs_ShortEnglish varchar (' + @MNEMONIC_SIZE + ') NULL,
			ErrMsgs_ShortOther varchar (' + @MNEMONIC_SIZE + ') NULL
		)'
		/*
		ErrMsgs:The ErrMsgs table is used to store error messages.
		ErrMsgs_ID:Primary key for the ErrMsgs table.
		ErrMsgs_English:English error description.
		ErrMsgs_Other:Alternate language error description.
		ErrMsgs_ShortEnglish:Abbreviated English error description.
		ErrMsgs_ShortOther:Abbreviated alternate language error message.
		*/

	)
END /* creating ErrMsgs db */
GO

PRINT  CHAR(13) + CHAR(10)+ N'Finished processing Control Schema script.'
go
