/************************************************************************/
/* DME 3.5 Database upgrade script */
/************************************************************************/
/* 
    This script update the database structure 
	from DME 3.5 SP1/SP2 to DME 3.5 SP3 

    Please replace script variable @db (just below) to the name of the 
	DME database.
	
    Notice: ALLWAYS have a recent backup of the database
			before running this script.
 ************************************************************************/
 

/* 
	PLEASE replace the @db value with the name of the DME database 
*/
DECLARE @db varchar(30);
SET @db = 'dme';

DECLARE @stop int;
SET @stop = 0;

/* 
   Validate that the script it being executed on the correct DME db
   This check was added to avoid accident where the script is executed on the wrong DB
*/   
--DECLARE @checkdb int
--SELECT @checkdb = COUNT(*) FROM sys.databases WHERE name=@db; 
IF @db != db_name() 
BEGIN
  PRINT '==========================================================================='
  PRINT '% Invalid script state' 
  PRINT 'This script is NOT executed on the @db specified database : ' + @db
  PRINT 'Please run the script on the correct database or change the name of the DME'
  PRINT 'database in the @db variable in the top of the script and try again'
  PRINT '==========================================================================='
  SET @stop = 1;
END



/* 
	Validate that this is a "real" DME 3.x database 
*/
DECLARE @tablefound int;
SELECT @tablefound = count(*) FROM INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK) WHERE table_name = 'RrdEntry'
IF @stop = 0 AND @tablefound = '0'
BEGIN
  PRINT '==========================================================================='
  PRINT '% Fatale DB version'
  PRINT 'This script is only ment for running on a DME 3.5 or newer database	'
  PRINT '==========================================================================='
  SET @stop = 1;
END


/* 
   Validate the current database structure version - Is it up-to-date already?
*/
DECLARE @structureVersion varchar(3);
SET @structureVersion = '0'
SELECT @structureVersion = value FROM DME_System WITH (NOLOCK) WHERE name = 'DATABASE_STRUCTURE_VERSION'
IF @stop = 0 AND @structureVersion = '6' 
BEGIN
  PRINT '==========================================================================='
  PRINT '% Database is already up-to-date'
  PRINT 'The database [' + db_name() + '] selected for update is already updated'
  PRINT 'The DB version is : DME 3.5 SP3'
  PRINT '==========================================================================='
  SET @stop = 1;
END


/* 
   Validate the database structure version - If the database has not been patched to DME3.5 SP1/SP2 the 
   DATDATABASE_STRUCTURE_VERSION number is lower than 4 and the script will stop with a warning 
*/
SELECT @structureVersion = value FROM DME_System WITH (NOLOCK) WHERE name = 'DATABASE_STRUCTURE_VERSION'
IF @stop = 0 AND @structureVersion != '5' 
BEGIN
  PRINT '============================================================================'
  PRINT '% Wrong DME database version'
  PRINT 'The current database [' + db_name() + '] selected for update does NOT'
  PRINT 'have the structure of a DME 3.5 SP1/SP2 database'
  IF  @structureVersion = '0' 
    PRINT 'The current DME database version is : DME 3.0 GA'
  ELSE 
    PRINT 'The current DME database version is : DME 3.0 SP' + @structureVersion
  PRINT 'Please upgrade the database version to DME 3.5 SP1/SP2 before doing this upgrade'
  PRINT '============================================================================'
  SET @stop = 1;
END


/* 
	Makes certain that there are no active connections to the database before updating 
	Notice : The DME server "SHOULD NOT BE RUNNING" when updating the database  		  
*/
DECLARE @connectionsFound int
SELECT @connectionsFound = count(*) FROM master..sysprocesses WHERE dbid=db_id(db_name()) AND spid<>@@spid
IF @stop = 0 AND @connectionsFound > 1
BEGIN
  PRINT '==========================================================================='
  PRINT '% Open connections to the database'
  PRINT 'There are open connections to the database.'
  PRINT 'The DME server "SHOULD NOT BE RUNNING" when updating the database'
  PRINT 'Please close all connections and run this script again'
  PRINT '==========================================================================='
  SET @stop = 1;
END


/* 
    Validate that READ_COMMITTED_SNAPSHOT_ON is set 
*/
DECLARE @env_state int, @sql varchar(255)
SELECT @env_state = IS_READ_COMMITTED_SNAPSHOT_ON FROM sys.databases WHERE name=@db; 
IF @stop = 0 AND @env_state != '1' 
BEGIN
	PRINT '==========================================================================='
	PRINT 'Adjust the DB setting for "READ_COMMITTED_SNAPSHOT" to ON'
	PRINT '==========================================================================='
	Select @sql = 'ALTER DATABASE '+@db+' SET READ_COMMITTED_SNAPSHOT ON'
	exec(@sql)
END

IF @stop = 0
BEGIN

PRINT '==========================================================================='
PRINT '* Start updating the DME database structure'


/************************************************************************/
/* Updating tables  */ 
/************************************************************************/
PRINT '* Updating existing tables'

ALTER TABLE [dbo].[dm_node]
  ALTER COLUMN [target] [nvarchar](512) NULL;

ALTER TABLE [dbo].[terminalRessourceNotification]
  ALTER COLUMN [errorMessage] [nvarchar](1024) NULL;

ALTER TABLE [dbo].[logTable]
  ADD [exceptionId] [nvarchar] (32) NULL;
   
ALTER TABLE [dbo].[logTable]
  ADD [originatingId] [nvarchar] (128) NULL;

/* Add indices */

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[dm_nodeData]') AND name = N'IX_logTable_exceptionId')
CREATE NONCLUSTERED INDEX [IX_logTable_exceptionId] ON [dbo].[logTable] 
(
	[exceptionId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF);



IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[dm_nodeData]') AND name = N'IX_dm_nodeData_checksum')
CREATE NONCLUSTERED INDEX [IX_dm_nodeData_checksum] ON [dbo].[dm_nodeData] 
(
	[checksum] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF);


IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK69604F7BF84B14D5]') AND parent_object_id = OBJECT_ID(N'[dbo].[dm_task]'))
  ALTER TABLE [dbo].[dm_task]  DROP CONSTRAINT [FK69604F7BF84B14D5];

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK69604F7BF84B14D5]') AND parent_object_id = OBJECT_ID(N'[dbo].[dm_task]'))
  ALTER TABLE [dbo].[dm_task]  WITH CHECK ADD  CONSTRAINT [FK69604F7BF84B14D5] FOREIGN KEY([job])
    REFERENCES [dbo].[dm_job] ([jobID]) ON DELETE CASCADE;

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK69604F7BF84B14D5]') AND parent_object_id = OBJECT_ID(N'[dbo].[dm_task]'))
  ALTER TABLE [dbo].[dm_task] CHECK CONSTRAINT [FK69604F7BF84B14D5];



/************************************************************************/
/* Update DME database version  */ 
/************************************************************************/
PRINT '* Updating the database version '

/** Set Database version **/ 
UPDATE DME_System SET value = '6' WHERE name = 'DATABASE_STRUCTURE_VERSION';
IF @@ROWCOUNT = 0
  INSERT INTO DME_System (name,value) VALUES ('DATABASE_STRUCTURE_VERSION','6');

PRINT '* End updating database'
PRINT '==========================================================================='

END