/************************************************************************/
/* DME 3.6 Database upgrade script */
/************************************************************************/
/* 
    This script update the database structure 
	from all version of DME 3.5 to DME 3.6 SP1

    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 = 'DB';

DECLARE @stop int;
DECLARE @structureVersion int;
SET @stop = 0;
SET @structureVersion = 0;

PRINT '===========================================================================';
PRINT '* Validate the database naming';

/* 
   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
*/   
IF @db != db_name() 
BEGIN
  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 '===========================================================================';
  RETURN;
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  		  
*/
PRINT '* Check for open connections to the DB';
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 '% 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 '===========================================================================';
  RETURN;
END;


/* 
   Validate if the database has already been updated to this level - Is it up-to-date already?
*/
SELECT @structureVersion = value FROM DME_System WITH (NOLOCK) WHERE name = 'DATABASE_STRUCTURE_VERSION';
IF @structureVersion = 21 
BEGIN
  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.6 SP1';
  PRINT '===========================================================================';
  RETURN;
END;


/* 
   Get the current DME structure version.
   If the version is lower than DME3.5SP3-SP6 then request the user to update to this level
   before running this script again.
*/
PRINT '* Get database version';
IF @structureVersion < 5 
BEGIN
  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 SP3-SP6 database';
  IF  @structureVersion = 0 
    PRINT '  The current DME database version is : DME 3.0 GA';
  IF  @structureVersion > 0 AND @structureVersion < 5
    PRINT '  The current DME database version is : DME 3.0 SP' + CAST(@structureVersion as varchar);
  PRINT '  Please upgrade the database version to DME 3.5 before running this upgrade';
  PRINT '============================================================================';
  RETURN;
END



/*
   If this is DME 3.5 GA/SP1/SP2 then start updating 
*/
IF @structureVersion = 5
BEGIN
	PRINT '';
	PRINT '===========================================================================';	
	PRINT '* Updating database from DME 3.5 SP1/SP2 -> DME 3.5 SP3/SP4/SP5/SP6';

	-- Updating 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];

	SET @structureVersion = 6;
END;
  
/*
   If this is DME 3.5 GA/SP1/SP2 then start updating 
*/
  
IF @structureVersion = 6
BEGIN
	PRINT '';
	PRINT '===========================================================================';
	PRINT '* Updating database from DME 3.5 SP6 -> DME 3.5 SP7';
	
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pre_cache_connector]') AND type in (N'U'))
	BEGIN
	CREATE TABLE [dbo].[pre_cache_connector] ( 
	  [terminalID] [nvarchar](50) NOT NULL,
	  [function] [nvarchar] (128) NOT NULL, 
	  [preCacheState] [nvarchar] (50) NOT NULL, 
	  [connectorId] [nvarchar] (50) NOT NULL, 
	CONSTRAINT [PK__pre_cache_connector] PRIMARY KEY CLUSTERED (
		[terminalID] ASC,
		[function] ASC
	) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF)
	)
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prov_iphone_cacertificate]') AND type in (N'U'))
	BEGIN
	CREATE TABLE [dbo].[prov_iphone_cacertificate](
		[certificateID] [int] IDENTITY(1,1) NOT NULL,
		[certificate] [image] NULL,
		[defaultCA] [tinyint] NULL,
		[encryptedkeyPair] [image] NULL,
		[subjectDN] [nvarchar] (450) NULL,
	CONSTRAINT [PK__prov_iphone_cacertificate] PRIMARY KEY CLUSTERED (
		[certificateID] ASC
	) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prov_iphone_enrollment]') AND type in (N'U'))
	BEGIN
	CREATE TABLE [dbo].[prov_iphone_enrollment](
		[enrollmentID] [nvarchar](40) NOT NULL,
		[apnInfo] [image] NULL,
		[apnToken] [nvarchar](255) NULL,
		[certificate] [image] NULL,
		[challenge] [nvarchar](100) NULL,
		[created] [datetime] NULL,
		[deviceID] [nvarchar](50) NULL,
		[lastState] [nvarchar](60) NULL,
		[lastStatusDate] [datetime] NULL,
		[phoneNumber] [nvarchar](50) NULL,
		[status] [image] NULL,
		[subject] [nvarchar](255) NULL,
		[caCertificate] [int] NULL,
	CONSTRAINT [PK__prov_iphone_enrollment] PRIMARY KEY CLUSTERED (
		[enrollmentID] ASC
	) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));

	CREATE NONCLUSTERED INDEX [IX__pie__subject] ON [dbo].[prov_iphone_enrollment] (
		[subject] 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);

	CREATE NONCLUSTERED INDEX [IX__pie__apnToken] ON [dbo].[prov_iphone_enrollment] (
		[apnToken] 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);

	CREATE NONCLUSTERED INDEX [IX__pie__deviceID] ON [dbo].[prov_iphone_enrollment] (
		[deviceID] 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);

	CREATE NONCLUSTERED INDEX [IX__pie__lastStatusDate] ON [dbo].[prov_iphone_enrollment] (
		[lastStatusDate] 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);
	END;

	
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prov_iphone_operations]') AND type in (N'U'))
	BEGIN
	CREATE TABLE [dbo].[prov_iphone_operations](
		[operationID] [int]  IDENTITY(1,1) NOT NULL,
		[createdDate] [datetime] NULL,
		[lastStatus] [nvarchar] (50) NULL,
		[lastStatusDate] [datetime] NULL,
		[mdmRequest] [image] NULL,
		[meta] [nvarchar] (100) NULL,
		[responseObject] [image] NULL,
		[requestType] [nvarchar] (50) NULL,
		[requestUUID] [nvarchar] (60) NULL,
		[status] [image] NULL,
		[enrollment] [nvarchar] (40) NULL, 
	CONSTRAINT [PK__prov_iphone_operations] PRIMARY KEY CLUSTERED (
		[operationID] ASC
	) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));

	CREATE NONCLUSTERED INDEX [IX__pio__lastStatusDate] ON [dbo].[prov_iphone_operations] (
		[lastStatusDate] 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);
	END;

	IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK1C594B47D0112]') AND parent_object_id = OBJECT_ID(N'[dbo].[prov_iphone_enrollment]'))
	BEGIN
		ALTER TABLE [dbo].[prov_iphone_enrollment]  WITH CHECK ADD  CONSTRAINT [FK1C594B47D0112] FOREIGN KEY([caCertificate]) 
			REFERENCES [dbo].[prov_iphone_cacertificate] ([certificateID]);
		ALTER TABLE [dbo].[prov_iphone_enrollment] CHECK CONSTRAINT [FK1C594B47D0112];
	END;
	
	IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK1C594B47D0115]') AND parent_object_id = OBJECT_ID(N'[dbo].[prov_iphone_operations]'))
	BEGIN
		ALTER TABLE [dbo].[prov_iphone_operations]  WITH CHECK ADD  CONSTRAINT [FK1C594B47D0115] FOREIGN KEY([enrollment]) 
			REFERENCES [dbo].[prov_iphone_enrollment] ([enrollmentID]);
		ALTER TABLE [dbo].[prov_iphone_operations] CHECK CONSTRAINT [FK1C594B47D0115];
	END;

	
	SET @structureVersion = 7;
	
END;


/*
   If this is DME 3.5 SP7 then start updating 
*/
IF @structureVersion = 7
BEGIN
	PRINT '* Updating database from DME 3.5 SP7 -> DME 3.6 SP8/SP9/SP10';

	ALTER TABLE [dbo].[dme_traffic] ALTER COLUMN [imsi] [nvarchar] (40) NULL;

	SET @structureVersion = 9;
END;


/*************************************************/
/* Now start updating to database structure 3.6  */
/*************************************************/

/*
   If this is DME 3.5 SP8/SP9 then start updating to DME 3.6
*/
IF (@structureVersion = 8 OR @structureVersion = 9)
BEGIN
	PRINT '';
	PRINT '===========================================================================';
	PRINT '* Updating database from DME 3.5 SP8/SP9 -> DME 3.6';
	
	-- Renaming 3.5 tables
	
	DROP INDEX [IX_settings_localGroupID] ON [dbo].[settings];
	-- Remove FK Constraint to Terminal before renaming
	ALTER TABLE [dbo].[settings] DROP CONSTRAINT [FK5582BC23490769CC];
	EXEC sp_rename N'settings', N'settings_35';
	
	EXEC sp_rename N'defaultSettings', N'defaultSettings_35';
	
	ALTER TABLE [dbo].[localGroup] DROP CONSTRAINT [UQ__localGroup__76969D2E];
	EXEC sp_rename N'localGroup', N'localGroup_35';
	
	-- Create new tables
	
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AppBoxApplication]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[AppBoxApplication](
			[applicationId] [nvarchar](250) NOT NULL,
			[created] [datetime] NOT NULL,
			[locked] [tinyint] NOT NULL,
			[name] [nvarchar](100) NOT NULL,
			[accessLdapGroup] [nvarchar](250) NULL,
		CONSTRAINT [PK__AppBoxApplication] PRIMARY KEY CLUSTERED (
			[applicationId] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF),
		CONSTRAINT [UQ__AppBoxApplication__name] UNIQUE NONCLUSTERED  (
			[name] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AppBoxApplicationInfo]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[AppBoxApplicationInfo](
			[DTYPE] [nvarchar](31) NOT NULL,
			[applicationId] [nvarchar](250) NOT NULL,
			[infoKey] [nvarchar](45) NOT NULL,
			[modified] [datetime] NULL,
			[value] [nvarchar](255) NULL,
		CONSTRAINT [PK__AppBoxApplicationInfo] PRIMARY KEY CLUSTERED (
			[applicationId] ASC,
			[infoKey] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AppBoxDeploymentInfo]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[AppBoxDeploymentInfo](
			[lineNumber] [int] IDENTITY(1,1) NOT NULL,
			[created] [datetime] NOT NULL,
			[message] [nvarchar](255) NOT NULL,
			[applicationId] [nvarchar](250) NOT NULL,
		CONSTRAINT [PK__AppBoxDeploymentInfo] PRIMARY KEY CLUSTERED (
			[lineNumber] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;
	
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AppBoxDatastore]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[AppBoxDatastore](
			[applicationId] nvarchar(250) NOT NULL, 
			[deviceId] nvarchar(50) NOT NULL, 
			[resourceId] nvarchar(45) NOT NULL, 
			[created] datetime NOT NULL, 
			[data] ntext NULL, 
			[modified] datetime NOT NULL, 
		CONSTRAINT [PK__AppBoxDatastore] PRIMARY KEY CLUSTERED (
			[applicationId] ASC,
			[deviceId] ASC,
			[resourceId] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;
	
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AppBoxFilestore]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[AppBoxFilestore](
			[applicationId] [nvarchar](250) NOT NULL,
			[fileName] [nvarchar](150) NOT NULL,
			[created] [datetime] NOT NULL,
			[data] [image] NULL,
			[fingerprint] [nvarchar](32) NULL,
			[modified] [datetime] NOT NULL,
			[size] [numeric](19, 0) NULL,
		CONSTRAINT [PK__AppBoxFilestore] PRIMARY KEY CLUSTERED (
			[applicationId] ASC,
			[fileName] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AppBoxRequestFunction]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[AppBoxRequestFunction](
			[applicationId] nvarchar(250) NOT NULL,
			[functionName] nvarchar(45) NOT NULL, 
			[requestHandler] nvarchar(45) NOT NULL, 
			[displayName] nvarchar(150) NULL, 
			[referencedByForm] tinyint NULL, 
		CONSTRAINT [PK__AppBoxRequestFunction] PRIMARY KEY CLUSTERED (
			[applicationId] ASC,
			[functionName] ASC,
			[requestHandler] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AppBoxInstalledOn]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[AppBoxInstalledOn](
			[applicationId] [nvarchar](250) NOT NULL,
			[deviceId] [nvarchar](50) NOT NULL,
			[created] [datetime] NOT NULL,
		CONSTRAINT [PK__AppBoxInstalledOn] PRIMARY KEY CLUSTERED (
			[applicationId] ASC,
			[deviceId] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AppBoxRequestHandler]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[AppBoxRequestHandler](
			[applicationId] [nvarchar](250) NOT NULL,
			[requestHandler] [nvarchar](45) NOT NULL,
			[displayName] [nvarchar](150) NULL,
		CONSTRAINT [PK__AppBoxRequestHandler] PRIMARY KEY CLUSTERED (
			[applicationId] ASC,
			[requestHandler] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[rssfeeds]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[rssfeeds](
			[feedID] [int] IDENTITY(1,1) NOT NULL,
			[authentificationType] [tinyint] NULL,
			[eTag] [nvarchar](255) NULL,
			[lastModified] [datetime] NULL,
			[link] [nvarchar](2048) NULL,
			[linkHash] [nvarchar](32) NULL,
			[localGroupID] [nvarchar](5) NULL,
			[userID] [nvarchar](50) NULL,
		CONSTRAINT [PK__rssfeeds] PRIMARY KEY CLUSTERED (
			[feedID] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF),
		CONSTRAINT [UQ__rssfeeds__userID__localGroupID__linkHash] UNIQUE NONCLUSTERED (
			[userID] ASC,
			[localGroupID] ASC,
			[linkHash] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[subscriptions]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[subscriptions](
			[subscriptionId] [int] IDENTITY(1,1) NOT NULL,
			[currency] [nvarchar](255) NOT NULL,
			[edited] [datetime] NOT NULL,
			[lastIntervalReset] [datetime] NULL,
			[name] [nvarchar](255) NOT NULL,
			[resetInterval] [nvarchar](255) NULL,
			[storedToSettings] [tinyint] NOT NULL,
			[homeRates] [int] NULL,
			[mcc] [int] NOT NULL,
			[mnc] [int] NOT NULL,
			[roamingRates] [int] NULL,
		CONSTRAINT [PK__subscriptions] PRIMARY KEY CLUSTERED (
			[subscriptionId] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF),
		CONSTRAINT [UQ__subscriptions__name] UNIQUE NONCLUSTERED(
			[name] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tinyCDR]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[tinyCDR](
			[tinyCDRID] [int] IDENTITY(1,1) NOT NULL,
			[dispatchStatus] [nvarchar](255) NULL,
			[traffic_callID] [nvarchar](32) NULL,
			[traffic_terminalID] [nvarchar](50) NULL,
		CONSTRAINT [PK__tinyCDR] PRIMARY KEY CLUSTERED (
			[tinyCDRID] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[domain]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[domain](
			[id] [int] IDENTITY(1,1) NOT NULL,
			[name] [nvarchar](128) NOT NULL,
			[url] [nvarchar](128) NOT NULL,
			[modBy] [nvarchar](45) NULL,
			[modDate] [datetime] NULL,
		CONSTRAINT [PK__domain] PRIMARY KEY CLUSTERED (
			[id] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF),
		CONSTRAINT [UQ__domain__name] UNIQUE NONCLUSTERED (
			[name] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF),
		CONSTRAINT [UQ__domain__url] UNIQUE NONCLUSTERED (
			[url] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[settings]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[settings](
			[id] [int] IDENTITY(1,1) NOT NULL,
			[name] [nvarchar](250) NULL,
			[value] [nvarchar](2048) NOT NULL,
			[localGroupId] [int] NOT NULL,	
			[lockOnTerminal] [int] NULL,	
			[intervalSetting] [nvarchar](250) NULL,
			[lockInterval] [int] NULL,
			[lockOnSuperUser] [int] NULL,
			[changedDate] [datetime] NULL,
			[changedBy] [nvarchar](50) NULL,
			[noLicense] [int] NULL,
		CONSTRAINT [PK__settings] PRIMARY KEY CLUSTERED (
			[id] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF),
		CONSTRAINT [UQ__settings__name__localGroupID] UNIQUE NONCLUSTERED (
			[localGroupID] ASC,	[name] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));

		CREATE NONCLUSTERED INDEX [IX_settings_localGroupID] ON [dbo].[settings] (
			[localGroupID] 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)
	END;

	
	
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[groupsupportedsettings]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[groupsupportedsettings](
			[localGroupId] [int] NOT NULL,
			[settingsName] [nvarchar](250) NOT NULL,
			[settingsId] [int] NOT NULL,
		CONSTRAINT [PK__groupsupportedsettings] PRIMARY KEY CLUSTERED (
			[localGroupId] ASC,
			[settingsName] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[localGroup]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[localGroup](
			[groupID] [int] IDENTITY(1,1) NOT NULL,
			[name] [nvarchar](250) NOT NULL,
			[description] [nvarchar](250) NULL,
			[hierarchyLocation] [float] NOT NULL,
			[priority] [int] NULL,
			[ldapGroup] [nvarchar](250) NULL,
			[changedDate] [datetime] NULL,
			[licenseID] [nvarchar](100) NULL,
			[ldapServer] [nvarchar](250) NULL,
			[type] [nvarchar](128) NOT NULL,
			[domainId] [int] NOT NULL,
		CONSTRAINT [PK__localGroup] PRIMARY KEY CLUSTERED (
			[groupID] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF),
		CONSTRAINT [UQ__localGroup__domainId__name__type] UNIQUE NONCLUSTERED (
			[domainId] ASC,	[name] ASC,	[type] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[homerates]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[HomeRates](
			[homeRatesID] [int] IDENTITY(1,1) NOT NULL,
			[domesticMms] [decimal](10, 4) NULL,
			[domesticRate] [decimal](10, 4) NULL,
			[domesticSms] [decimal](10, 4) NULL,
			[includedMms] [int] NOT NULL,
			[includedSms] [int] NOT NULL,
			[includedTrafficMB] [int] NOT NULL,
			[includedVoice] [int] NOT NULL,
			[internationalMms] [decimal](10, 4) NULL,
			[internationalRate] [decimal](10, 4) NULL,
			[internationalSms] [decimal](10, 4) NULL,
			[receiving] [decimal](10, 4) NULL,
			[trafficMB] [decimal](10, 4) NULL,	
		CONSTRAINT [PK__homerates] PRIMARY KEY CLUSTERED (
			[homeRatesId] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IphoneProfile]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[IphoneProfile](
			[profileID] [nvarchar](255) NOT NULL,
			[category] [nvarchar](255) NULL,
			[changeDate] [datetime] NULL,
			[content] [image] NULL,
			[description] [nvarchar](255) NULL,
			[fileName] [nvarchar](255) NULL,
			[hash] [nvarchar](255) NULL,
			[identifier] [nvarchar](255) NULL,
			[name] [nvarchar](255) NULL,
			[smsCode] [nvarchar](255) NULL,
			[uuid] [nvarchar](255) NULL,
		CONSTRAINT [PK__IphoneProfile] PRIMARY KEY CLUSTERED (
			[profileID] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dm_bootstrapMessages]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[dm_bootstrapMessages](
			[bootstrapMessageID] [int] IDENTITY(1,1) NOT NULL,
			[bootstrapMessage] [nvarchar](2000) NULL,
			[initiateMessageID] [int] NULL,
		CONSTRAINT [PK__bootstrapMessages] PRIMARY KEY CLUSTERED (
			[bootstrapMessageID] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dm_node_result]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[dm_node_result](
			[id] [int] IDENTITY(1,1) NOT NULL,
			[format] [nvarchar](255) NULL,
			[target] [nvarchar](255) NULL,
			[type] [nvarchar](255) NULL,
			[nodeData] [int] NULL,
		CONSTRAINT [PK__dm_node_result] PRIMARY KEY CLUSTERED (
			[id] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dm_initiateMessages]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[dm_initiateMessages](
			[initiateMessageID] [int] IDENTITY(1,1) NOT NULL,
			[created] [datetime] NOT NULL,
			[status] [nvarchar](255) NOT NULL,
			[devicePK] [int] NOT NULL,
			[jobID] [nvarchar](255) NOT NULL,
		CONSTRAINT [PK__dm_initiateMessages] PRIMARY KEY CLUSTERED (
			[initiateMessageID] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF),
		CONSTRAINT [UQ__dm_initiateMessages_jobID] UNIQUE NONCLUSTERED (
			[jobID] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[countryrates]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[CountryRates](
			[CountryRatesID] [int] IDENTITY(1,1) NOT NULL,
			[InternationalRate] [decimal](10, 4) NOT NULL,
			[MMSRate] [decimal](10, 4) NOT NULL,
			[SMSRate] [decimal](10, 4) NOT NULL,
			[TrafficMB] [decimal](10, 4) NOT NULL,
			[type] [nvarchar](255) NULL,
			[mcc] [int] NULL,
			[subscription] [int] NOT NULL,
		CONSTRAINT [PK_CountryRates] PRIMARY KEY CLUSTERED (
			[CountryRatesID] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prov_iphone_configuration_profile]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[prov_iphone_configuration_profile](
			[profileID] [nvarchar](255) NOT NULL,
			[category] [nvarchar](255) NULL,
			[changeDate] [datetime] NULL,
			[content] [image] NULL,
			[description] [nvarchar](255) NULL,
			[fileName] [nvarchar](255) NULL,
			[hash] [nvarchar](255) NULL,
			[identifier] [nvarchar](255) NULL,
			[name] [nvarchar](255) NULL,
			[smsCode] [nvarchar](255) NULL,
			[uuid] [nvarchar](255) NULL,
			[encrypted] [tinyint] NULL,
			[organization] [nvarchar](255) NULL,
			[signed] [tinyint] NULL,
		CONSTRAINT [PK__prov_iphone_configuration_profile] PRIMARY KEY CLUSTERED (
			[profileID] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prov_iphone_installed_certificates]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[prov_iphone_installed_certificates](
			[installedCertificateID] [int] IDENTITY(1,1) NOT NULL,
			[certificate] [image] NULL,
			[name] [nvarchar](255) NULL,
			[updateDate] [datetime] NULL,
			[enrollment] [nvarchar](40) NULL,
		CONSTRAINT [PK__prov_iphone_installed_certificates] PRIMARY KEY CLUSTERED (
			[installedCertificateID] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prov_iphone_installed_profiles]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[prov_iphone_installed_profiles](
			[installedProfileID] [int] IDENTITY(1,1) NOT NULL,
			[created] [datetime] NULL,
			[groupInstall] [int] NULL,
			[hash] [nvarchar](255) NULL,
			[identifier] [nvarchar](255) NULL,
			[markForRemove] [tinyint] NULL,
			[profile] [image] NULL,
			[profileType] [nvarchar](255) NULL,
			[uuid] [nvarchar](255) NULL,
			[enrollment] [nvarchar](40) NULL, /* ?? */
			[iphoneProfile] [nvarchar](255) NULL,
		CONSTRAINT [PK__prov_iphone_installed_profiles] PRIMARY KEY CLUSTERED (
			[installedProfileID] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prov_iphone_installed_restrictions]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[prov_iphone_installed_restrictions](
			[installedRestrictionID] [int] IDENTITY(1,1) NOT NULL,
			[name] [nvarchar](255) NULL,
			[restrictions] [image] NULL,
			[updateDate] [datetime] NULL,
			[enrollment] [nvarchar](40) NULL,
		CONSTRAINT [PK__prov_iphone_installed_restrictions] PRIMARY KEY CLUSTERED (
			[installedRestrictionID] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF)) ;
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prov_iphone_profile_group]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[prov_iphone_profile_group](
			[groupId] [int] NOT NULL,
			[profileID] [nvarchar](100) NOT NULL,
		CONSTRAINT [PK__prov_iphone_profile_group] PRIMARY KEY CLUSTERED (
			[groupId] ASC,
			[profileID] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prov_iphone_provisioning_profile]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[prov_iphone_provisioning_profile](
			[profileID] [nvarchar](255) NOT NULL,
			[category] [nvarchar](255) NULL,
			[changeDate] [datetime] NULL,
			[content] [image] NULL,
			[description] [nvarchar](255) NULL,
			[fileName] [nvarchar](255) NULL,
			[hash] [nvarchar](255) NULL,
			[identifier] [nvarchar](255) NULL,
			[name] [nvarchar](255) NULL,
			[smsCode] [nvarchar](255) NULL,
			[uuid] [nvarchar](255) NULL,
			[expirationDate] [datetime] NULL,
		CONSTRAINT [PK__prov_iphone_provisioning_profile] PRIMARY KEY CLUSTERED (
			[profileID] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prov_job_group]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[prov_job_group](
			[groupID] [nvarchar](255) NOT NULL,
			[createdDate] [datetime] NULL,
			[description] [nvarchar](255) NULL,
			[groupType] [nvarchar](255) NULL,
			[operation] [nvarchar](255) NULL,
			[scheduled] [image] NULL,
		CONSTRAINT [PK__prov_job_group] PRIMARY KEY CLUSTERED (
			[groupID] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[roamingrates]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[roamingrates](
			[roamingRatesID] [int] IDENTITY(1,1) NOT NULL,
			[internationalRate] [decimal](10, 4) NULL,
			[mmsRate] [decimal](10, 4) NULL,
			[smsRate] [decimal](10, 4) NULL,
			[trafficMB] [decimal](10, 4) NULL,
		CONSTRAINT [PK__roamingrates] PRIMARY KEY CLUSTERED (
			[roamingRatesID] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DME_UI_Preference]') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[DME_UI_Preference](
			[ClassName] [nvarchar](30) NOT NULL,
			[Preference] [varbinary](255) NULL,
			[PreferenceName] [nvarchar](255) NOT NULL,
			[UserId] [nvarchar](50) NOT NULL,
		CONSTRAINT [PK__DME_UI_Preference] PRIMARY KEY CLUSTERED (
			[ClassName] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF),
		CONSTRAINT [UQ__DME_UI_Preference__UserId__ClassName__PreferenceName]  UNIQUE NONCLUSTERED ( 
			[UserId] ASC,
			[ClassName] ASC,
			[PreferenceName] ASC
		) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF));
	END;

	-- Updating tables 

	ALTER TABLE [dbo].[DME_UpdateFile] ALTER COLUMN	[listed] [tinyint] NULL;
	ALTER TABLE [dbo].[Connector] ALTER COLUMN	[locked] [tinyint] NULL;
	ALTER TABLE [dbo].[settings] ALTER COLUMN [name] [nvarchar] (250) NULL;
	ALTER TABLE [dbo].[dm_ddf] ALTER COLUMN [name] [nvarchar] (100) NULL;
	ALTER TABLE [dbo].[dm_ddf] ALTER COLUMN [changeDate] [datetime] NULL;
	ALTER TABLE [dbo].[dm_ddf_conf] ALTER COLUMN [name] [nvarchar] (100) NULL;
	ALTER TABLE [dbo].[dm_ddf_conf] ALTER COLUMN [changeDate] [datetime] NULL;
	ALTER TABLE [dbo].[dm_ddf_values] ALTER COLUMN [priority] [int] NULL;
	ALTER TABLE [dbo].[dm_ddf_values] ALTER COLUMN [path] [nvarchar] (1024) NULL;
	ALTER TABLE [dbo].[CountryRates] ALTER COLUMN [InternationalRate] [decimal](10, 4) NULL;
	ALTER TABLE [dbo].[CountryRates] ALTER COLUMN [MMSRate] [decimal](10, 4) NULL;
	ALTER TABLE [dbo].[CountryRates] ALTER COLUMN [SMSRate] [decimal](10, 4) NULL;
	ALTER TABLE [dbo].[CountryRates] ALTER COLUMN [TrafficMB] [decimal](10, 4);
	
	ALTER TABLE [dbo].[dme_traffic] ALTER COLUMN [intMcc] [int] NULL;
	ALTER TABLE [dbo].[dme_traffic] ALTER COLUMN [intMnc] [int] NULL;
	ALTER TABLE [dbo].[dme_traffic] ALTER COLUMN [extMcc] [int] NULL;
	
	-- Temporary fix to avoid FK constraint problem doing the update
	UPDATE dme_voice_locator_name SET mcc = 289 WHERE mcc = 288;
	
	
	ALTER TABLE [dbo].[dme_voice_locator_name] DROP CONSTRAINT [PK__dme_voice_locato__6C190EBB];
	ALTER TABLE [dbo].[dme_voice_locator_name] ALTER COLUMN	[mcc] [int] NOT NULL;
	ALTER TABLE [dbo].[dme_voice_locator_name] ALTER COLUMN	[mnc] [int] NOT NULL;
	
	ALTER TABLE [dbo].[dme_voice_locator_name] ADD CONSTRAINT [PK__dme_voice_locator_name] 
		PRIMARY KEY CLUSTERED ([mcc] ASC,[mnc] ASC) 
		WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF);
	-- MSSQL08 DMEDB-2 issue
	-- ALTER TABLE [dbo].[dme_voice_locator_name] CHECK CONSTRAINT [PK__dme_voice_locator_name];

	ALTER TABLE [dbo].[dme_voice_locator] ALTER COLUMN [countryname] [nvarchar] (80) NULL;
	EXEC sp_rename 'dme_voice_locator.countryname','countryCode','COLUMN'; 

	DROP INDEX [IX_terminalGroupMembership_localGroupID] ON [dbo].[terminalGroupMembership];
	ALTER TABLE [dbo].[terminalGroupMembership] DROP CONSTRAINT [PK__terminalGroupMem__25518C17];
	ALTER TABLE [dbo].[terminalGroupMembership]	ALTER COLUMN [localGroupID] [int] NOT NULL;
	ALTER TABLE [dbo].[terminalGroupMembership] ADD CONSTRAINT [PK__terminalGroupMembership] PRIMARY KEY CLUSTERED (
		[localGroupID] ASC, [terminalID] ASC
	) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF);
	-- MSSQL08 DMEDB-2 issue
	-- ALTER TABLE [dbo].[terminalGroupMembership] CHECK CONSTRAINT [PK__terminalGroupMembership];
		
	DROP INDEX [IX_terminalGroupMembershipTMP_localGroupID] ON [dbo].[terminalGroupMembership_tmp];
	ALTER TABLE [dbo].[terminalGroupMembership_tmp] DROP CONSTRAINT [PK__terminalGroupMem__2739D489];
	ALTER TABLE [dbo].[terminalGroupMembership_tmp]	ALTER COLUMN [localGroupID] [int] NOT NULL;
	ALTER TABLE [dbo].[terminalGroupMembership_tmp] ADD CONSTRAINT [PK__terminalGroupMembership_tmp] PRIMARY KEY CLUSTERED (
		[localGroupID] ASC, [terminalID] ASC
	) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF);
	-- MSSQL08 DMEDB-2 issue 
	-- ALTER TABLE [dbo].[terminalGroupMembership_tmp] CHECK CONSTRAINT [PK__terminalGroupMembership_tmp];
	
		
	ALTER TABLE [dbo].[terminalProperty] ADD [owner] [nvarchar] (100) NULL;
	ALTER TABLE [dbo].[applicationBlock] ADD [owner] [nvarchar] (100) NULL;

	ALTER TABLE [dbo].[terminalRessourceNotification] DROP COLUMN [lastNotificationTimes];

		
	ALTER TABLE [dbo].[dm_dme_jobs] ADD [state] [nvarchar] (255) NULL;
        ALTER TABLE [dbo].[dm_dme_jobs]	ADD [meta] [nvarchar] (255) NULL;
	ALTER TABLE [dbo].[dm_dme_jobs]	ADD [nodeStatus] [image] NULL;
	ALTER TABLE [dbo].[dm_dme_jobs]	ADD [waitPath] [nvarchar] (255) NULL;
	ALTER TABLE [dbo].[dm_dme_jobs]	ADD [jobGroup] [nvarchar] (255) NULL;
	
	ALTER TABLE [dbo].[dm_dme_jobs]	DROP COLUMN [groupID];
	ALTER TABLE [dbo].[dm_dme_jobs]	DROP COLUMN [notifications];
	
	ALTER TABLE [dbo].[dm_job] ADD [deleted] [tinyint] NULL;
	ALTER TABLE [dbo].[dm_job] ADD [runAfter] [datetime] NULL;
	
	ALTER TABLE [dbo].[dm_ddf_values] ADD [valueName] [nvarchar] (255) NULL;
	ALTER TABLE [dbo].[dm_operation] DROP CONSTRAINT [FK71208531D328DF2F];
	ALTER TABLE [dbo].[dm_operation] DROP COLUMN [parent_operationID];

	ALTER TABLE [dbo].[downloadLinks] ADD [distributionType] [nvarchar](255) NULL;
	ALTER TABLE [dbo].[downloadLinks] ADD [downloadType] [nvarchar](255) NULL;
	ALTER TABLE [dbo].[downloadLinks] ADD [provisioningType] [nvarchar](255) NULL;
	ALTER TABLE [dbo].[downloadLinks] ADD [queued] [datetime] NULL;
	ALTER TABLE [dbo].[downloadLinks] ADD [status] [image] NULL;
	ALTER TABLE [dbo].[downloadLinks] ADD [jobGroup] [nvarchar](255) NULL;
	ALTER TABLE [dbo].[downloadLinks] DROP COLUMN [expires];

	ALTER TABLE [dbo].[message] ADD [queued] [datetime] NULL;

	ALTER TABLE [dbo].[prov_iphone_operations] ADD [queueDate] [datetime] NULL;
	ALTER TABLE [dbo].[prov_iphone_operations] ADD [waitForOperationID] [int] NULL;
	ALTER TABLE [dbo].[prov_iphone_operations] ADD [waitPath] [nvarchar](255) NULL;
	ALTER TABLE [dbo].[prov_iphone_operations] ADD [waitSendIf] [nvarchar](255) NULL;
	ALTER TABLE [dbo].[prov_iphone_operations] ADD [jobGroup] [nvarchar](255) NULL;
	ALTER TABLE [dbo].[prov_iphone_operations] DROP COLUMN [responseObject];
   
	ALTER TABLE [dbo].[prov_iphone_enrollment] ADD [userID] [nvarchar](255) NULL;
	ALTER TABLE [dbo].[dme_report] ALTER COLUMN [reportFile] [nvarchar] (250) NULL;
	ALTER TABLE [dbo].[dme_report] ADD [isHidden] [tinyint] NULL;

	ALTER TABLE [dbo].[settingsType] ALTER COLUMN [cdata] [int] NULL;
	ALTER TABLE [dbo].[settingsType] ALTER COLUMN [useInterval] [int] NULL;
	
	
	-- Table ConnectorUser
	ALTER TABLE [dbo].[ConnectorUser] DROP CONSTRAINT [PK__ConnectorUser__0425A276];
	EXEC sp_rename 'ConnectorUser.[function]','functionName','COLUMN'; 
	ALTER TABLE [dbo].[ConnectorUser] ADD CONSTRAINT [PK__ConnectorUser] PRIMARY KEY CLUSTERED (
		[connectorId] ASC,
 		[functionName] ASC,
		[userID] ASC
	 ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF);
	-- MSSQL08 DMEDB-2 issue
	-- ALTER TABLE [dbo].[ConnectorUser] CHECK CONSTRAINT [PK__ConnectorUser];
 

	-- Table ConnectorFunction
	ALTER TABLE [dbo].[ConnectorFunction] ADD [applicationId] [nvarchar](250) NULL;
	ALTER TABLE [dbo].[ConnectorFunction] DROP CONSTRAINT [PK__ConnectorFunctio__00551192];
	EXEC sp_rename 'ConnectorFunction.[function]','functionName','COLUMN';  
	ALTER TABLE [dbo].[ConnectorFunction] ADD CONSTRAINT [PK__ConnectorFunction] PRIMARY KEY CLUSTERED (
		[connectorId] ASC,
		[functionName] ASC
	) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF);
	-- MSSQL08 DMEDB-2 issue
	-- ALTER TABLE [dbo].[ConnectorFunction] CHECK CONSTRAINT [PK__ConnectorFunction];
	
	
        -- Dropping obsolete UNIQUE key constraints
	-- MSSQL08 DMEDB-2 issue (next 3 lines)
        -- ALTER TABLE [dbo].[DMEDomain] DROP CONSTRAINT [UQ__DMEDomain__07020F21];
	-- ALTER TABLE [dbo].[Connector] DROP CONSTRAINT [UQ__Connector__7E6CC920];
	-- ALTER TABLE [dbo].[role] DROP CONSTRAINT [UQ__role__09A971A2];
	
	-- Dropping obsolete FK constraints
	ALTER TABLE [dbo].[dm_operation] DROP CONSTRAINT [FK71208531F592B5C2];
	ALTER TABLE [dbo].[ConnectorUser] DROP CONSTRAINT [FK1B6495985D6CA4BC];
	
	-- Add missing FK constraints
	ALTER TABLE [dbo].[AppBoxDataStore]  WITH CHECK ADD CONSTRAINT [FK6B57968D60EC6AC0] FOREIGN KEY([applicationId],[deviceId]) REFERENCES 
		[dbo].[AppBoxInstalledOn] ([applicationId],[deviceId]) ON DELETE CASCADE;
	ALTER TABLE [dbo].[AppBoxDataStore] CHECK CONSTRAINT [FK6B57968D60EC6AC0];
	
	ALTER TABLE [dbo].[AppBoxRequestFunction]  WITH CHECK ADD CONSTRAINT [FK9905F61DBE6492B6] FOREIGN KEY([applicationId],[requestHandler]) REFERENCES 
		[dbo].[AppBoxRequestHandler] ([applicationId],[requestHandler]);
	ALTER TABLE [dbo].[AppBoxRequestFunction] CHECK CONSTRAINT [FK9905F61DBE6492B6];
	
	ALTER TABLE [dbo].[AppBoxApplicationInfo] WITH CHECK ADD CONSTRAINT [FK6208E0B4FBA83726] FOREIGN KEY([applicationId])
		REFERENCES [dbo].[AppBoxApplication] ([applicationId]) ON DELETE CASCADE;
	ALTER TABLE [dbo].[AppBoxApplicationInfo] CHECK CONSTRAINT [FK6208E0B4FBA83726];

	ALTER TABLE [dbo].[AppBoxDeploymentInfo] WITH CHECK ADD CONSTRAINT [FKA648D3BDFBA83726] FOREIGN KEY([applicationId])
		REFERENCES [dbo].[AppBoxApplication] ([applicationId]) ON DELETE CASCADE;
	ALTER TABLE [dbo].[AppBoxDeploymentInfo] CHECK CONSTRAINT [FKA648D3BDFBA83726];

	ALTER TABLE [dbo].[AppBoxFilestore] WITH CHECK ADD CONSTRAINT [FK328B63FBFBA83726] FOREIGN KEY([applicationId])
		REFERENCES [dbo].[AppBoxApplication] ([applicationId]) ON DELETE CASCADE;
	ALTER TABLE [dbo].[AppBoxFilestore] CHECK CONSTRAINT [FK328B63FBFBA83726];

	ALTER TABLE [dbo].[AppBoxInstalledOn] WITH CHECK ADD CONSTRAINT [FK9F69308FD9276726] FOREIGN KEY([deviceId])
		REFERENCES [dbo].[terminal] ([terminalID]) ON DELETE CASCADE;
	ALTER TABLE [dbo].[AppBoxInstalledOn] CHECK CONSTRAINT [FK9F69308FD9276726];

	ALTER TABLE [dbo].[AppBoxInstalledOn] WITH CHECK ADD CONSTRAINT [FK9F69308FFBA83726] FOREIGN KEY([applicationId])
		REFERENCES [dbo].[AppBoxApplication] ([applicationId]) ON DELETE CASCADE;
	ALTER TABLE [dbo].[AppBoxInstalledOn] CHECK CONSTRAINT [FK9F69308FFBA83726];

	ALTER TABLE [dbo].[AppBoxRequestHandler] WITH CHECK ADD CONSTRAINT [FK248F865FBA83726] FOREIGN KEY([applicationId])
		REFERENCES [dbo].[AppBoxApplication] ([applicationId]) ON DELETE CASCADE;
	ALTER TABLE [dbo].[AppBoxRequestHandler] CHECK CONSTRAINT [FK248F865FBA83726];

	ALTER TABLE [dbo].[ConnectorFunction] WITH CHECK ADD  CONSTRAINT [FK67C96F25FBA83726] FOREIGN KEY([applicationId])
		REFERENCES [dbo].[AppBoxApplication] ([applicationId]) ON DELETE CASCADE;
	ALTER TABLE [dbo].[ConnectorFunction] CHECK CONSTRAINT [FK67C96F25FBA83726];

	ALTER TABLE [dbo].[ConnectorUser] WITH CHECK ADD CONSTRAINT [FK1B649598A0F9EB17] FOREIGN KEY([connectorId], [functionName])
		REFERENCES [dbo].[ConnectorFunction] ([connectorId], [functionName]) ON DELETE CASCADE;
	ALTER TABLE [dbo].[ConnectorUser] CHECK CONSTRAINT [FK1B649598A0F9EB17];
	
	ALTER TABLE [dbo].[CountryRates] WITH CHECK ADD CONSTRAINT [FK578F559D8E5C98B4] FOREIGN KEY([mcc])
		REFERENCES [dbo].[dme_voice_locator] ([mcc]); 
	ALTER TABLE [dbo].[CountryRates] CHECK CONSTRAINT [FK578F559D8E5C98B4];

	ALTER TABLE [dbo].[CountryRates] WITH CHECK ADD CONSTRAINT [FK578F559DE20A5EA5] FOREIGN KEY([subscription])
		REFERENCES [dbo].[subscriptions] ([subscriptionId]);
	ALTER TABLE [dbo].[CountryRates] CHECK CONSTRAINT [FK578F559DE20A5EA5];

	ALTER TABLE [dbo].[dm_node_result] WITH CHECK ADD CONSTRAINT [FKD9C596445C4450FD] FOREIGN KEY([nodeData]) 
		REFERENCES [dbo].[dm_nodeData] ([pk]);
	ALTER TABLE [dbo].[dm_node_result] CHECK CONSTRAINT [FKD9C596445C4450FD];

	ALTER TABLE [dbo].[dm_bootstrapMessages] WITH CHECK ADD CONSTRAINT [FK42152BA45D3EB49F] FOREIGN KEY([initiateMessageID])
		REFERENCES [dbo].[dm_initiateMessages] ([initiateMessageID]);
	ALTER TABLE [dbo].[dm_bootstrapMessages] CHECK CONSTRAINT [FK42152BA45D3EB49F];

	ALTER TABLE [dbo].[dm_dme_jobs] WITH CHECK ADD CONSTRAINT [FKFDC2FCAF166BE886] FOREIGN KEY([jobGroup])
		REFERENCES [dbo].[prov_job_group] ([groupID]);
	ALTER TABLE [dbo].[dm_dme_jobs] CHECK CONSTRAINT [FKFDC2FCAF166BE886];

	ALTER TABLE [dbo].[dm_initiateMessages] WITH CHECK ADD CONSTRAINT [FK7B4227FBA5806DEC] FOREIGN KEY([devicePK])
		REFERENCES [dbo].[dm_devices] ([devicePK]);
	ALTER TABLE [dbo].[dm_initiateMessages] CHECK CONSTRAINT [FK7B4227FBA5806DEC];

	ALTER TABLE [dbo].[dm_initiateMessages] WITH CHECK ADD CONSTRAINT [FK7B4227FBFE5326B0] FOREIGN KEY([jobID])
		REFERENCES [dbo].[dm_job] ([jobID]);
	ALTER TABLE [dbo].[dm_initiateMessages] CHECK CONSTRAINT [FK7B4227FBFE5326B0];

	ALTER TABLE [dbo].[DME_UI_Preference] WITH CHECK ADD CONSTRAINT [FK134669638159F4BB] FOREIGN KEY([UserId])
		REFERENCES [dbo].[usr] ([userID]);
	ALTER TABLE [dbo].[DME_UI_Preference] CHECK CONSTRAINT [FK134669638159F4BB];

	ALTER TABLE [dbo].[downloadLinks] WITH CHECK ADD CONSTRAINT [FKD5AC11166BE886] FOREIGN KEY([jobGroup])
		REFERENCES [dbo].[prov_job_group] ([groupID]);
	ALTER TABLE [dbo].[downloadLinks] CHECK CONSTRAINT [FKD5AC11166BE886];

	ALTER TABLE [dbo].[groupsupportedsettings] WITH CHECK ADD CONSTRAINT [FK8F8CFA3212E5A3D9] FOREIGN KEY([settingsId])
		REFERENCES [dbo].[settings] ([id]);
	ALTER TABLE [dbo].[groupsupportedsettings] CHECK CONSTRAINT [FK8F8CFA3212E5A3D9];

	ALTER TABLE [dbo].[groupsupportedsettings] WITH CHECK ADD CONSTRAINT [FK8F8CFA32B0BBAE7C] FOREIGN KEY([localGroupId])
		REFERENCES [dbo].[localGroup] ([groupID]);
	ALTER TABLE [dbo].[groupsupportedsettings] CHECK CONSTRAINT [FK8F8CFA32B0BBAE7C];

	ALTER TABLE [dbo].[localGroup] WITH CHECK ADD CONSTRAINT [FK4C6211D4302204BC] FOREIGN KEY([domainId])
		REFERENCES [dbo].[domain] ([id]);
	ALTER TABLE [dbo].[localGroup] CHECK CONSTRAINT [FK4C6211D4302204BC];

	ALTER TABLE [dbo].[prov_iphone_installed_restrictions] WITH CHECK ADD  CONSTRAINT [FK4BC163501D155C49] FOREIGN KEY([enrollment])
		REFERENCES [dbo].[prov_iphone_enrollment] ([enrollmentID]);
	ALTER TABLE [dbo].[prov_iphone_installed_restrictions] CHECK CONSTRAINT [FK4BC163501D155C49];

	ALTER TABLE [dbo].[prov_iphone_operations] WITH CHECK ADD CONSTRAINT [FKA0C2D270166BE886] FOREIGN KEY([jobGroup])
		REFERENCES [dbo].[prov_job_group] ([groupID]);
	ALTER TABLE [dbo].[prov_iphone_operations] CHECK CONSTRAINT [FKA0C2D270166BE886];

	ALTER TABLE [dbo].[prov_iphone_profile_group] WITH CHECK ADD CONSTRAINT [FKBEDD0765694CAC47] FOREIGN KEY([groupId])
		REFERENCES [dbo].[localGroup] ([groupID]);
	ALTER TABLE [dbo].[prov_iphone_profile_group] CHECK CONSTRAINT [FKBEDD0765694CAC47];
	
	ALTER TABLE [dbo].[prov_iphone_installed_certificates]  WITH CHECK ADD  CONSTRAINT [FKA7BBEEE51D155C49] FOREIGN KEY([enrollment]) 
		REFERENCES [dbo].[prov_iphone_enrollment] ([enrollmentID]);
	ALTER TABLE [dbo].[prov_iphone_installed_certificates] CHECK CONSTRAINT [FKA7BBEEE51D155C49];
	
	ALTER TABLE [dbo].[prov_iphone_installed_profiles]  WITH CHECK ADD  CONSTRAINT [FK2CCA5931D155C49] FOREIGN KEY([enrollment]) 
		REFERENCES [dbo].[prov_iphone_enrollment] ([enrollmentID]);
	ALTER TABLE [dbo].[prov_iphone_installed_profiles] CHECK CONSTRAINT [FK2CCA5931D155C49];
	
	ALTER TABLE [dbo].[settings] WITH CHECK ADD CONSTRAINT [FK5582BC23B0BBAE7C] FOREIGN KEY([localGroupId])
		REFERENCES [dbo].[localGroup] ([groupID]);
	ALTER TABLE [dbo].[settings] CHECK CONSTRAINT [FK5582BC23B0BBAE7C];

	ALTER TABLE [dbo].[subscriptions] WITH CHECK ADD CONSTRAINT [FK7674CAF62B13F27F] FOREIGN KEY([roamingRates])
		REFERENCES [dbo].[roamingrates] ([roamingRatesID]);
	ALTER TABLE [dbo].[subscriptions] CHECK CONSTRAINT [FK7674CAF62B13F27F];

	ALTER TABLE [dbo].[subscriptions] WITH CHECK ADD CONSTRAINT [FK7674CAF667B6CF7D] FOREIGN KEY([homeRates])
		REFERENCES [dbo].[HomeRates] ([homeRatesID]);
	ALTER TABLE [dbo].[subscriptions] CHECK CONSTRAINT [FK7674CAF667B6CF7D];

	ALTER TABLE [dbo].[subscriptions] WITH CHECK ADD CONSTRAINT [FK7674CAF6895906C8] FOREIGN KEY([mcc], [mnc])
		REFERENCES [dbo].[dme_voice_locator_name] ([mcc], [mnc]);
	ALTER TABLE [dbo].[subscriptions] CHECK CONSTRAINT [FK7674CAF6895906C8];


	ALTER TABLE [dbo].[terminalGroupMembership] WITH CHECK ADD CONSTRAINT [FK3355E599490769CC] FOREIGN KEY([terminalID])
		REFERENCES [dbo].[terminal] ([terminalID]);
	ALTER TABLE [dbo].[terminalGroupMembership] CHECK CONSTRAINT [FK3355E599490769CC];

	-- Disabled due to DMEDB-2 issue 
        -- ALTER TABLE [dbo].[terminalGroupMembership] WITH CHECK ADD CONSTRAINT [FK3355E599B0BBAE7C] FOREIGN KEY([localGroupID])
	--	REFERENCES [dbo].[localGroup] ([groupID]);
	-- ALTER TABLE [dbo].[terminalGroupMembership] CHECK CONSTRAINT [FK3355E599B0BBAE7C];

	ALTER TABLE [dbo].[tinyCDR]  WITH CHECK ADD  CONSTRAINT [FKB1C5D1112061D4A7] FOREIGN KEY([traffic_terminalID], [traffic_callID])
		REFERENCES [dbo].[dme_traffic] ([terminalID], [callID]);
	ALTER TABLE [dbo].[tinyCDR] CHECK CONSTRAINT [FKB1C5D1112061D4A7];
	
	-- Update FK constraints
	ALTER TABLE [dbo].[ConnectorFunction] DROP CONSTRAINT [FK67C96F255D6CA4BC];
	ALTER TABLE [dbo].[ConnectorFunction] WITH CHECK ADD CONSTRAINT [FK67C96F255D6CA4BC] FOREIGN KEY([connectorId])
		REFERENCES [dbo].[Connector] ([connectorId]) ON DELETE CASCADE;
	ALTER TABLE [dbo].[ConnectorFunction] CHECK CONSTRAINT [FK67C96F255D6CA4BC];

	ALTER TABLE [dbo].[ConnectorProperty] DROP CONSTRAINT [FKDA47D9225D6CA4BC];
	ALTER TABLE [dbo].[ConnectorProperty] WITH CHECK ADD CONSTRAINT [FKDA47D9225D6CA4BC] FOREIGN KEY([connectorId]) 
		REFERENCES [dbo].[Connector]([connectorId]) ON DELETE CASCADE;
	ALTER TABLE [dbo].[ConnectorProperty] CHECK CONSTRAINT [FKDA47D9225D6CA4BC];

	ALTER TABLE [dbo].[dm_connection_history] DROP CONSTRAINT [FKB170BFA913CE9D71];
	ALTER TABLE [dbo].[dm_connection_history] WITH CHECK ADD CONSTRAINT [FKB170BFA913CE9D71] FOREIGN KEY([device]) 
		REFERENCES [dbo].[dm_devices] ([devicePK]) ON DELETE CASCADE;
	ALTER TABLE [dbo].[dm_connection_history] CHECK CONSTRAINT [FKB170BFA913CE9D71];
	
	ALTER TABLE [dbo].[terminalProperty] DROP CONSTRAINT [FKD3B39871490769CC];
	ALTER TABLE [dbo].[terminalProperty]  WITH CHECK ADD CONSTRAINT [FKD3B39871490769CC] FOREIGN KEY([terminalID])
		REFERENCES [dbo].[terminal] ([terminalID]) ON DELETE CASCADE;
	ALTER TABLE [dbo].[terminalProperty] CHECK CONSTRAINT [FKD3B39871490769CC];

	ALTER TABLE [dbo].[terminalAssetMgmt] DROP CONSTRAINT [FK1C341CB5490769CC];
	ALTER TABLE [dbo].[terminalAssetMgmt] WITH CHECK ADD CONSTRAINT [FK1C341CB5490769CC] FOREIGN KEY([terminalId])
		REFERENCES [dbo].[terminal] ([terminalID]) ON DELETE CASCADE;
	ALTER TABLE [dbo].[terminalAssetMgmt] CHECK CONSTRAINT [FK1C341CB5490769CC];
	
	ALTER TABLE [dbo].[userTerminal] DROP CONSTRAINT [FK8BD1E7678159F4BB];
	ALTER TABLE [dbo].[userTerminal] WITH CHECK ADD CONSTRAINT [FK8BD1E7678159F4BB] FOREIGN KEY([userID])
		REFERENCES [dbo].[usr] ([userID]);
	ALTER TABLE [dbo].[userTerminal] CHECK CONSTRAINT [FK8BD1E7678159F4BB];
	
	ALTER TABLE [dbo].[userTerminal] DROP CONSTRAINT [FK8BD1E767490769CC];
	ALTER TABLE [dbo].[userTerminal] WITH CHECK ADD  CONSTRAINT [FK8BD1E767490769CC] FOREIGN KEY([terminalID]) 
		REFERENCES [dbo].[terminal] ([terminalID]) ON DELETE CASCADE;
	ALTER TABLE [dbo].[userTerminal] CHECK CONSTRAINT [FK8BD1E767490769CC];
	
	-- Dropping obsolete tables
	DROP TABLE [dbo].[dm_error_devices];
	DROP TABLE [dbo].[dm_software_install];
	DROP TABLE [dbo].[dm_action_dm_action];
	
	DROP TABLE [dbo].[ThirdPartyEvent];
	DROP TABLE [dbo].[ThirdPartyResponse];
	DROP TABLE [dbo].[ThirdPartyStatus];
	DROP TABLE [dbo].[ThirdPartyUserIdentifier];
	DROP TABLE [dbo].[SuspectedConnectors];
		
	-- Renaming UQ
	   
	EXEC sp_rename N'applicationBlock.UQ__applicationBlock__20C1E124', N'UQ__applicationBlock',N'INDEX';
	EXEC sp_rename N'dm_action_plan.UQ__dm_action_plan__398D8EEE',N'UQ__dm_action_plan__type__model',N'INDEX';
	EXEC sp_rename N'pendingNotifications.UQ__pendingNotificat__04E4BC85',N'UQ__pendingNotifications__SMSmessageID',N'INDEX';
	EXEC sp_rename N'dm_devices.UQ__dm_devices__403A8C7D',N'UQ__dm_devices__bootID',N'INDEX';
	EXEC sp_rename N'dmFileStore.UQ__dmFileStore__2F10007B',N'UQ__dmFileStore__path__name',N'INDEX';
	
	-- Renaming PK

	EXEC sp_rename N'applicationBlock.PK__applicationBlock__1FCDBCEB', N'PK__applicationBlock',N'INDEX';
	EXEC sp_rename N'Connector.PK__Connector__7D78A4E7', N'PK__Connector', N'INDEX';
	EXEC sp_rename N'ConnectorProperty.PK__ConnectorPropert__023D5A04', N'PK__ConnectorProperty', N'INDEX';
	EXEC sp_rename N'dayScheme.PK__dayScheme__22AA2996', N'PK__dayScheme', N'INDEX';
	EXEC sp_rename N'defaultApplicationBlock.PK__defaultApplicati__24927208', N'PK__defaultApplicationBlock', N'INDEX';
	EXEC sp_rename N'deviceInfo.PK__deviceInfo__286302EC', N'PK__deviceInfo',N'INDEX';
	EXEC sp_rename N'deviceSignature.PK__deviceSignature__2A4B4B5E', N'PK__deviceSignature',N'INDEX';
	EXEC sp_rename N'dm_ddf.PK__df_dff__ddfID', N'PK__dm_dff',N'INDEX';
	EXEC sp_rename N'dm_ddf_conf.PK__df_dff_conf__ddfConfID', N'PK__dm_ddf_conf',N'INDEX';
	EXEC sp_rename N'dm_ddf_values.PK__df_dff_values__ddfValueID', N'PK__dm_ddf_values',N'INDEX';
	EXEC sp_rename N'distinctValues.PK__distinctValues__2C3393D0', N'PK__distinctValues',N'INDEX';
	EXEC sp_rename N'dm_action.PK__dm_action__34C8D9D1', N'PK__dm_action',N'INDEX';
	EXEC sp_rename N'dm_action_plan.PK__dm_action_plan__38996AB5', N'PK__dm_action_plan',N'INDEX';
	EXEC sp_rename N'dm_config_server.PK__dm_config_server__3B75D760', N'PK__dm_config_server',N'INDEX';
	EXEC sp_rename N'dm_connection_history.PK__dm_connection_hi__3D5E1FD2', N'PK__dm_connection_history',N'INDEX';
	EXEC sp_rename N'dm_devices.PK__dm_devices__3F466844', N'PK__dm_devices',N'INDEX';
	EXEC sp_rename N'dm_dme_jobs.PK__dm_dme_jobs__4222D4EF', N'PK__dm_dme_jobs',N'INDEX';
	EXEC sp_rename N'dm_job.PK__dm_job__45F365D3', N'PK__dm_job',N'INDEX';
	EXEC sp_rename N'dm_node.PK__dm_node__47DBAE45', N'PK__dm_node',N'INDEX';
	EXEC sp_rename N'dm_nodeData.PK__dm_nodeData__49C3F6B7', N'PK__dm_nodeData',N'INDEX';
	EXEC sp_rename N'dm_operation.PK__dm_operation__4BAC3F29', N'PK__dm_operation',N'INDEX';
	EXEC sp_rename N'dm_support.PK__dm_support__5165187F', N'PK__dm_support',N'INDEX';
	EXEC sp_rename N'dm_support_param.PK__dm_support_param__534D60F1', N'PK__dm_support_param',N'INDEX';
	EXEC sp_rename N'dm_task.PK__dm_task__5535A963', N'PK__dm_task',N'INDEX';
	EXEC sp_rename N'dm_users.PK__dm_users__571DF1D5', N'PK__dm_users',N'INDEX';
	EXEC sp_rename N'DME_Attachments.PK__DME_Attachments__08EA5793', N'PK__DME_Attachments',N'INDEX';
	EXEC sp_rename N'dme_keystore_certs.PK__dme_keystore_cer__59063A47', N'PK__dme_keystore_certs',N'INDEX';
	EXEC sp_rename N'dme_keystore_crl_ap.PK__dme_keystore_crl__5AEE82B9', N'PK__dme_keystore_crl_ap',N'INDEX';
	EXEC sp_rename N'dme_keystore_crls.PK__dme_keystore_crl__5CD6CB2B', N'PK__dme_keystore_crls',N'INDEX';
	EXEC sp_rename N'dme_keystore_emails.PK__dme_keystore_ema__5EBF139D', N'PK__dme_keystore_emails',N'INDEX';
	EXEC sp_rename N'dme_keystore_keys.PK__dme_keystore_key__60A75C0F', N'PK__dme_keystore_keys',N'INDEX';
	EXEC sp_rename N'DME_License.PK__DME_License__0AD2A005', N'PK__DME_License',N'INDEX';
	EXEC sp_rename N'dme_report.PK__dme_report__628FA481', N'PK__dme_report',N'INDEX';
	EXEC sp_rename N'DME_ServerConfiguration.PK__DME_ServerConfig__0CBAE877', N'PK__DME_ServerConfiguration',N'INDEX';
	EXEC sp_rename N'dme_servicemonitor_failures.PK__dme_servicemonit__6477ECF3', N'PK__dme_servicemonitor_failures',N'INDEX';
	EXEC sp_rename N'DME_System.PK__DME_System__0EA330E9', N'PK__DME_System',N'INDEX';
	EXEC sp_rename N'dme_traffic.PK__dme_traffic__66603565', N'PK__dme_traffic',N'INDEX';
	EXEC sp_rename N'DME_UpdateFile.PK__DME_UpdateFile__108B795B', N'PK__DME_UpdateFile',N'INDEX';
	EXEC sp_rename N'dme_voice_locator.PK__dme_voice_locato__68487DD7', N'PK__dme_voice_locator',N'INDEX';
	EXEC sp_rename N'dme_voice_locator_area.PK__dme_voice_locato__6A30C649', N'PK__dme_voice_locator_area',N'INDEX';
	EXEC sp_rename N'DMEDomain.PK__DMEDomain__060DEAE8', N'PK__DMEDomain',N'INDEX';
	EXEC sp_rename N'dmFileStore.PK__dmFileStore__2E1BDC42', N'PK__dmFileStore',N'INDEX';
	EXEC sp_rename N'dmRules.PK__dmRules__30F848ED', N'PK__dmRules',N'INDEX';
	EXEC sp_rename N'dmWorkQueue.PK__dmWorkQueue__32E0915F', N'PK__dmWorkQueue',N'INDEX';
	EXEC sp_rename N'downloadLinks.PK__downloadLinks__6E01572D', N'PK__downloadLinks',N'INDEX';
	EXEC sp_rename N'folderSettings.PK__folderSettings__6FE99F9F', N'PK__folderSettings',N'INDEX';
	EXEC sp_rename N'groupGraph.PK__groupGraph__71D1E811', N'PK__groupGraph',N'INDEX';
	EXEC sp_rename N'homeMobileNetworks.PK__homeMobileNetwor__73BA3083', N'PK__homeMobileNetworks',N'INDEX';
	EXEC sp_rename N'localGroupRessourceNotification.PK__localGroupRessou__787EE5A0', N'PK__localGroupRessourceNotification',N'INDEX';
	EXEC sp_rename N'logTable.PK__logTable__7A672E12', N'PK__logTable',N'INDEX';
	EXEC sp_rename N'message.PK__message__7C4F7684', N'PK__message',N'INDEX';
	EXEC sp_rename N'NetworkStatistic.PK__NetworkStatistic__1273C1CD', N'PK__NetworkStatistic',N'INDEX';
	EXEC sp_rename N'notificationRessource.PK__notificationRess__7E37BEF6', N'PK__notificationRessource',N'INDEX';
	EXEC sp_rename N'notificationScheme.PK__notificationSche__00200768', N'PK__notificationScheme',N'INDEX';
	EXEC sp_rename N'onlineDevices.PK__onlineDevices__02084FDA', N'PK__onlineDevices',N'INDEX';
	EXEC sp_rename N'OTA.PK__OTA__145C0A3F', N'PK__OTA',N'INDEX';
	EXEC sp_rename N'pendingNotifications.PK__pendingNotificat__03F0984C', N'PK__pendingNotifications',N'INDEX';
	EXEC sp_rename N'preferredMobileNetworks.PK__preferredMobileN__06CD04F7', N'PK__preferredMobileNetworks',N'INDEX';
	EXEC sp_rename N'role.PK__role__08B54D69', N'PK__role',N'INDEX';
	EXEC sp_rename N'roleFunction.PK__roleFunction__0B91BA14', N'PK__roleFunction',N'INDEX';
	EXEC sp_rename N'scanTable.PK__scanTable__0D7A0286', N'PK__scanTable',N'INDEX';
	EXEC sp_rename N'sequence.PK__sequence__0F624AF8', N'PK__sequence',N'INDEX';
	EXEC sp_rename N'ServerSMS.PK__ServerSMS__164452B1', N'PK__ServerSMS',N'INDEX';
	EXEC sp_rename N'settingsHistory.PK__settingsHistory__14270015', N'PK__settingsHistory',N'INDEX';
	EXEC sp_rename N'settingsType.PK__settingsType__160F4887', N'PK__settingsType',N'INDEX';
	EXEC sp_rename N'sim.PK__sim__17F790F9', N'PK__sim',N'INDEX';
	EXEC sp_rename N'supportedFields.PK__supportedFields__19DFD96B', N'PK__supportedFields',N'INDEX';
	EXEC sp_rename N'syncAttachmentTable.PK__syncAttachmentTa__1BC821DD', N'PK__syncAttachmentTable',N'INDEX';
	EXEC sp_rename N'syncTable.PK__syncTable__1DB06A4F', N'PK__syncTable',N'INDEX';
	EXEC sp_rename N'tempData.PK__tempData__1F98B2C1', N'PK__tempData',N'INDEX';
	EXEC sp_rename N'terminal.PK__terminal__2180FB33', N'PK__terminal',N'INDEX';
	EXEC sp_rename N'terminalAssetMgmt.PK__terminalAssetMgm__236943A5', N'PK__terminalAssetMgmt',N'INDEX';
	EXEC sp_rename N'terminalOwner.PK__terminalOwner__29221CFB', N'PK__terminalOwner',N'INDEX';
	EXEC sp_rename N'terminalOwnerHistory.PK__terminalOwnerHis__2B0A656D', N'PK__terminalOwnerHistory',N'INDEX';
	EXEC sp_rename N'terminalProperty.PK__terminalProperty__2CF2ADDF', N'PK__terminalProperty',N'INDEX';
	EXEC sp_rename N'terminalRessourceNotification.PK__terminalRessourc__2EDAF651', N'PK__terminalRessourceNotification',N'INDEX';
	EXEC sp_rename N'tx_syncAttachmentTable.PK__tx_syncAttachmen__30C33EC3', N'PK__tx_syncAttachmentTable',N'INDEX';
	EXEC sp_rename N'tx_syncTable.PK__tx_syncTable__32AB8735', N'PK__tx_syncTable',N'INDEX';
	EXEC sp_rename N'uniqueKey.PK__uniqueKey__3493CFA7', N'PK__uniqueKey',N'INDEX';
	EXEC sp_rename N'updateProperty.PK__updateProperty__367C1819', N'PK__updateProperty',N'INDEX';
	EXEC sp_rename N'userFilterGroup.PK__userFilterGroup__3864608B', N'PK__userFilterGroup',N'INDEX';
	EXEC sp_rename N'userGroup.PK__userGroup__3A4CA8FD', N'PK__userGroup',N'INDEX';
	EXEC sp_rename N'userLdapGroup.PK__userLdapGroup__3C34F16F', N'PK__userLdapGroup',N'INDEX';
	EXEC sp_rename N'userProperty.PK__userProperty__3E1D39E1', N'PK__userProperty',N'INDEX';
	EXEC sp_rename N'userTerminal.PK__userTerminal__40058253', N'PK__userTerminal',N'INDEX';
	EXEC sp_rename N'usr.PK__usr__41EDCAC5', N'PK__usr',N'INDEX';

	/************************************************************************/
	/* Update DME database version  */ 
	/************************************************************************/

	SET @structureVersion = 20;
	
	/** Set Database version **/ 
	UPDATE DME_System SET value = '20' WHERE name = 'DATABASE_STRUCTURE_VERSION'
	IF @@ROWCOUNT = 0
	  INSERT INTO DME_System (name,value) VALUES ('DATABASE_STRUCTURE_VERSION','20')
END;
/***************************************************/
/* Now start updating to database structure 3.6SP1 */
/***************************************************/

/*
   If this is DME 3.6GA then start updating to DME 3.6SP1
*/
IF (@structureVersion = 20)
BEGIN
	PRINT '';
	PRINT '===========================================================================';
	PRINT '* Updating database from DME 3.6 GA -> DME 3.6 SP1';

	ALTER TABLE [dbo].[logTable] DROP COLUMN [serverIdentity];

    ALTER TABLE [dbo].[terminal] ADD [licenseChanged] [datetime] NULL;
    ALTER TABLE [dbo].[terminal] ADD [lockedChanged] [datetime] NULL;

	ALTER TABLE [dbo].[AppBoxApplicationInfo] ADD [valueClass] [nvarchar](255) NULL;
	ALTER TABLE [dbo].[AppBoxApplicationInfo] ADD [valueConstraints] [nvarchar](255) NULL;

	/************************************************************************/
	/* Update DME database version  */ 
	/************************************************************************/

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

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