

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/CloudDB/Upgrade/cf_SurveyJobStats.upg] ---------- 

--$Id: cf_SurveyJobStats.upg,v 1.1.66.9.16.1 2021/06/28 21:05:12 rpolimera Exp $
BEGIN

DECLARE @oldRev CHAR(20)    		
  	DECLARE @oldVal CHAR(256)			
  	DECLARE @retCode   INTEGER			
  	DECLARE @sqlError  INTEGER			
  	SET     @retCode = 0              	
  	DECLARE @sqlString nvarchar(MAX)	
  	DECLARE @macroValue nvarchar(MAX)	
      DECLARE @parentID INTEGER				
      DECLARE @tName nvarchar(128)			
      DECLARE @colID INTEGER					
      DECLARE @constId INTEGER				
      DECLARE @cName   nvarchar(128)			
  	DECLARE @oldConstraintName   nvarchar(128)	
  	DECLARE @newConstraintName   nvarchar(128)	
  	DECLARE @oldDataType         nvarchar(64)	
  	DECLARE @keyname   nvarchar(128)		
      DECLARE @dbName   nvarchar(128)			
  	DECLARE @IsNullableAttr VARCHAR(100)	
  	DECLARE @DefaultValAttr NVARCHAR(MAX)	
  	DECLARE @DataType     NVARCHAR(	256)	
  	DECLARE @ct_msg			VARCHAR(1024) = ''	
  	DECLARE @ct_tcMsg		VARCHAR(1024) = ''	
  	DECLARE @nl				VARCHAR(3) = CHAR(13) + CHAR(10)	
  	SET ROWCOUNT 0							
  	SET NOCOUNT ON-- Declare/Initialize @oldRev, @oldVal, @retCode

SELECT @dbName = DB_NAME()													
  SELECT @oldRev = (SELECT revision FROM GXDBVersions WHERE name = 'cf_SurveyJobStats')	
  IF (@oldRev IS NULL)														
  BEGIN                 														
      SET @oldRev = '0.0'														
  END																			
  IF NOT EXISTS (SELECT * FROM sysobjects										
  WHERE type = 'U'															
  AND name = 'cf_SurveyJobStats')														
  BEGIN																		
  	SET @oldRev = '0.0'														
  	DELETE FROM GXDBVersions WHERE name = 'cf_SurveyJobStats'							
  END																			
  IF (@oldRev = '1.17')       												
  BEGIN																		
      PRINT 'RETVAL: 0   Table [' +'cf_SurveyJobStats' +'] does NOT require upgrade.'	
  	
  END																			
  else																		
  BEGIN																		
      PRINT 'Upgrading Table [' +'cf_SurveyJobStats' +'].'							
  END-- Get @oldRev, compare to '1.17'. Chk for work to do.


IF NOT EXISTS (select * from sysobjects where id = object_id(N'[dbo].[cf_SurveyJobStats]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN



--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/CloudDB/Tbl/cf_SurveyJobStats.tbl] ---------- 





-- ALLOW_COMMENTS_BELOW_HERE:

-- ----------------------------------------------------------------------

--

--           Copyright (c) 1998  CommVault Systems, Inc.

--                  All rights reserved.

--

--

--        This is unpublished proprietary source code of CommVault

--        Systems, Inc. The copyright notice above does not evidence

--        any actual or intended publication of such source code.

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

-- rcsid="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/CloudDB/Tbl/cf_SurveyJobStats.tbl,v $ $Id: cf_SurveyJobStats.tbl,v 1.5.46.9.16.1 2021/06/28 21:05:18 rpolimera Exp $";

--+-+-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-+

--

--  BEGIN

--

--+-+-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-+

print '... Creating Table: cf_SurveyJobStats'

exec ('



DECLARE @tblRetCode integer

Set NOCOUNT ON





create table cf_SurveyJobStats (

	CommservUniqueId INT	NOT NULL,

	JobId INT	NOT NULL,

	AppId INT	NOT NULL,

	OpType INT	NOT NULL,

	BkpLevel INT	NOT NULL,

	Status INT	NOT NULL,

	StartDate INT	NOT NULL,

	EndDate INT	NOT NULL,

	ScanType INT	NOT NULL,

	SizeChangePct INT	NOT NULL,

	TransferTime INT	NOT NULL,

	ProtectedObjects BIGINT	NOT NULL,

	FailedObjects BIGINT	NOT NULL,

	FailedFolders BIGINT	NOT NULL,

	DataProtected BIGINT	NOT NULL,

	DataCompressed BIGINT	NOT NULL,

	DataTransferred BIGINT	NOT NULL,

	DataWritten BIGINT	NOT NULL,

	ErrorCode VARCHAR(32)	NOT NULL,

	FailureReason NVARCHAR(512)	NOT NULL,

	StoragePolicy NVARCHAR(144)	NOT NULL,

	StoragePolicyID INT NULL,

	SchedulePolicy NVARCHAR(256) NULL,

	ScheduleName NVARCHAR(256) NULL,

	Attempts INT NULL,

	InitFrom INT NULL,

	Phases INT NULL,

	failureErrorCode	INT NULL ,

	DataStatus INT NULL,

	LogStoragePolicyID  INT NOT NULL DEFAULT 0,



) 

set @tblRetCode = @@error

if (@tblRetCode <> 0)

BEGIN

		set @tblRetCode = @@error;

		print ''RETVAL: 10 Table [cf_SurveyJobStats]''

END

--------





CREATE INDEX cf_SurveyJobStats_StartDate_idx ON cf_SurveyJobStats (StartDate, CommservUniqueId, OpType, Status, StoragePolicyID, failureErrorCode)

 

set @tblRetCode = @@error 

if (@tblRetCode <> 0)

BEGIN

	set @tblRetCode = @@error;

	print ''RETVAL: 02 Table [cf_SurveyJobStats]''

END

----------



CREATE INDEX cf_SurveyJobStats_EndDate_CommservUniqueId_AppId_idx ON cf_SurveyJobStats(EndDate, CommservUniqueId, AppId)

 

set @tblRetCode = @@error 

if (@tblRetCode <> 0)

BEGIN

	set @tblRetCode = @@error;

	print ''RETVAL: 02 Table [cf_SurveyJobStats]''

END

----------



CREATE INDEX cf_SurveyJobStats_CommservUniqueId_AppId_StartDate_idx ON cf_SurveyJobStats(CommservUniqueId, AppId, StartDate)

 

set @tblRetCode = @@error 

if (@tblRetCode <> 0)

BEGIN

	set @tblRetCode = @@error;

	print ''RETVAL: 02 Table [cf_SurveyJobStats]''

END

----------



-- Improve performance of INSERT INTO #tmpDailyJobs statement in ParseJobDetails stored procedure

CREATE INDEX cf_SurveyJobStats_CommservUniqueId_EndDate_idx ON cf_SurveyJobStats(CommservUniqueId, EndDate) INCLUDE (AppId, Status, DataProtected, DataWritten, FailureErrorCode)

 

set @tblRetCode = @@error 

if (@tblRetCode <> 0)

BEGIN

	set @tblRetCode = @@error;

	print ''RETVAL: 02 Table [cf_SurveyJobStats]''

END

----------



--+-+-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-+

--

--  END

--

--+-+-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-++-+



insert into GXDBVersions values(1, ''cf_SurveyJobStats'',  ''1.5.46.9.16.1'', ''cf_SurveyJobStats'', '''')

set @tblRetCode = @@error

if (@tblRetCode <> 0)

BEGIN

	set @tblRetCode = @@error;

	print ''RETVAL: 10 Table [GXDBVersions]''

END

ALTER TABLE cf_SurveyJobStats ADD CONSTRAINT cf_SurveyJobStats_CommservUniqueId_JobId_PK 		primary key (CommservUniqueId, JobId)



set @tblRetCode = @@error 

if (@tblRetCode <> 0)

BEGIN

	set @tblRetCode = @@error;

	print ''RETVAL: 02 Table [cf_SurveyJobStats]''

END

----------

ALTER TABLE cf_SurveyJobStats  ADD CONSTRAINT cf_SurveyJobStats_CommservUniqueId_AppId_FK	FOREIGN KEY (CommservUniqueId, AppId)references	 cf_SurveySubclients (CommservUniqueId, AppId) ON DELETE CASCADE



set @tblRetCode = @@error 

if (@tblRetCode <> 0)

BEGIN

	set @tblRetCode = @@error;

	print ''RETVAL: 02 Table [cf_SurveyJobStats]''

END

----------


')
END
SELECT @parentID = (SELECT id FROM sysobjects WHERE name = 'cf_SurveyJobStats' AND xType = 'U')				
  	IF NOT EXISTS (SELECT * FROM	sys.foreign_keys WHERE	name = 'cf_SurveyJobStats_CommservUniqueId_AppId_FK' AND delete_referential_action = 1 AND parent_object_id = @parentID)

	BEGIN

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE	name = 'cf_SurveyJobStats_CommservUniqueId_AppId_FK' AND parent_object_id = (SELECT id FROM sysobjects WHERE name = 'cf_SurveyJobStats' AND xType = 'U'))

		BEGIN

			ALTER TABLE cf_SurveyJobStats DROP cf_SurveyJobStats_CommservUniqueId_AppId_FK

		END

SELECT @parentID = (SELECT id FROM sysobjects WHERE name = 'cf_SurveyJobStats' AND xType = 'U')				
  	IF NOT EXISTS (SELECT  name FROM sys.foreign_keys WHERE parent_object_id = @parentID AND name = 'cf_SurveyJobStats_CommservUniqueId_AppId_FK')	
  	BEGIN																								
  		SET @sqlString = 'ALTER TABLE [' + 'cf_SurveyJobStats' +']' + 'ADD CONSTRAINT [' + 'cf_SurveyJobStats_CommservUniqueId_AppId_FK' +'] FOREIGN KEY (' + 'CommservUniqueId, AppId' + ') REFERENCES ['+ 'cf_SurveySubclients' +'] (' + 'CommservUniqueId, AppId' + ') ON DELETE CASCADE'		
  		IF ('' <> '')							
  		SET @sqlString = ''				
  	SET @retCode = 0						
  	EXEC sp_executesql @sqlString			
  	SET @sqlError = @@error					
  	IF (@sqlError <> 0)						
  	BEGIN									
  		IF (99 > @retCode)				
  	  		SET @retCode = 99			
   PRINT 'RETVAL: ' + CONVERT(CHAR(3), 99) + ' [' + @sqlString + ']' + ' SQL Error: ' + CONVERT(CHAR(8), @sqlError)		+ ' [' + @sqlString + ']'						
  		IF (@retCode > 10)					
  			RETURN							
  	END																					
  	END

	END

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'cf_SurveyJobStats' AND COLUMN_NAME='failureErrorCode')			
  	BEGIN																														
  		SET @sqlString = 'ALTER TABLE ' + 'cf_SurveyJobStats'+ ' ADD ' +'failureErrorCode'+ ' ' +'INT'			
  		IF ('' <> '')							
  		SET @sqlString = ''				
  	SET @retCode = 0						
  	EXEC sp_executesql @sqlString			
  	SET @sqlError = @@error					
  	IF (@sqlError <> 0)						
  	BEGIN									
  		IF (99 > @retCode)				
  	  		SET @retCode = 99			
   PRINT 'RETVAL: ' + CONVERT(CHAR(3), 99) + ' [' + @sqlString + ']' + ' SQL Error: ' + CONVERT(CHAR(8), @sqlError)		+ ' [' + @sqlString + ']'						
  		IF (@retCode > 10)					
  			RETURN							
  	END																										
  	END

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'cf_SurveyJobStats' AND COLUMN_NAME='DataStatus')			
  	BEGIN																														
  		SET @sqlString = 'ALTER TABLE ' + 'cf_SurveyJobStats'+ ' ADD ' +'DataStatus'+ ' ' +'INT'			
  		IF ('' <> '')							
  		SET @sqlString = ''				
  	SET @retCode = 0						
  	EXEC sp_executesql @sqlString			
  	SET @sqlError = @@error					
  	IF (@sqlError <> 0)						
  	BEGIN									
  		IF (99 > @retCode)				
  	  		SET @retCode = 99			
   PRINT 'RETVAL: ' + CONVERT(CHAR(3), 99) + ' [' + @sqlString + ']' + ' SQL Error: ' + CONVERT(CHAR(8), @sqlError)		+ ' [' + @sqlString + ']'						
  		IF (@retCode > 10)					
  			RETURN							
  	END																										
  	END

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'cf_SurveyJobStats' AND COLUMN_NAME='LogStoragePolicyID')			
  	BEGIN																														
  		SET @macroValue='0'			
  	IF(@macroValue <> '')			
  	BEGIN			
  		IF(ISNUMERIC(@macroValue) = 1)			
  		BEGIN			
  			SET @macroValue = @macroValue			
  		END			
  		ELSE IF(@macroValue = 'NULL')			
  		BEGIN			
  			SET @macroValue = @macroValue			
  		END			
  		ELSE IF(@macroValue LIKE '{%}')			
  		BEGIN			
  			SET @macroValue = SUBSTRING(@macroValue, 2, LEN(@macroValue) - 2)			
  		END			
  		ELSE IF(@macroValue LIKE '"%"')			
  		BEGIN			
  			SET @macroValue = '''' + REPLACE(SUBSTRING(@macroValue, 2, LEN(@macroValue) - 2), '''', '''''') + ''''			
  		END			
  		ELSE IF(@macroValue LIKE '0x%')			
  		BEGIN			
  			SET @macroValue = @macroValue			
  		END			
  		ELSE IF(@macroValue = '''')		
  		BEGIN			
  			SET @macroValue = ''''''			
  		END			
  		ELSE IF(@macroValue LIKE '%(%)%')			
  		BEGIN			
  			SET @macroValue = @macroValue			
  		END			
  		ELSE			
  		BEGIN			
  			SET @macroValue = '''' + REPLACE(@macroValue, '''', '''''') + ''''			
  		END			
  	END			
  	ELSE			
  	BEGIN			
  		SET @macroValue = ''''''			
  	END		
  		SET @sqlString = 'ALTER TABLE ' + 'cf_SurveyJobStats'+ ' ADD ' +'LogStoragePolicyID'+ ' ' +'INT'		
  			+ ' NOT NULL DEFAULT ' + @macroValue																	
  		IF ('' <> '')							
  		SET @sqlString = ''				
  	SET @retCode = 0						
  	EXEC sp_executesql @sqlString			
  	SET @sqlError = @@error					
  	IF (@sqlError <> 0)						
  	BEGIN									
  		IF (99 > @retCode)				
  	  		SET @retCode = 99			
   PRINT 'RETVAL: ' + CONVERT(CHAR(3), 99) + ' [' + @sqlString + ']' + ' SQL Error: ' + CONVERT(CHAR(8), @sqlError)		+ ' [' + @sqlString + ']'						
  		IF (@retCode > 10)					
  			RETURN							
  	END																											
  	END

SELECT @parentID = (SELECT id FROM sysobjects WHERE name = 'cf_SurveyJobStats' AND xType = 'U')				
      IF EXISTS (SELECT * FROM sysindexes WHERE id = @parentID AND name = 'cf_SurveyJobStats_idx1')						
      BEGIN																								
  		SET @sqlString = 'DROP INDEX [' + 'cf_SurveyJobStats' +'].[' + 'cf_SurveyJobStats_idx1' +']'							
  		IF ('' <> '')							
  		SET @sqlString = ''				
  	SET @retCode = 0						
  	EXEC sp_executesql @sqlString			
  	SET @sqlError = @@error					
  	IF (@sqlError <> 0)						
  	BEGIN									
  		IF (99 > @retCode)				
  	  		SET @retCode = 99			
   PRINT 'RETVAL: ' + CONVERT(CHAR(3), 99) + ' [' + @sqlString + ']' + ' SQL Error: ' + CONVERT(CHAR(8), @sqlError)		+ ' [' + @sqlString + ']'						
  		IF (@retCode > 10)					
  			RETURN							
  	END																					
  	END

SELECT @parentID = (SELECT id FROM sysobjects WHERE name = 'cf_SurveyJobStats' AND xType = 'U')								
      IF NOT EXISTS (SELECT * FROM sysindexes WHERE id = @parentID AND name = 'cf_SurveyJobStats_StartDate_idx')					
      BEGIN																								
  		SET @sqlString = 'CREATE INDEX [' + 'cf_SurveyJobStats_StartDate_idx' +']' + 'ON [' + 'cf_SurveyJobStats' +']  (' + 'StartDate, CommservUniqueId, OpType, Status, StoragePolicyID, failureErrorCode' + ')'		
  		IF ('' <> '')							
  		SET @sqlString = ''				
  	SET @retCode = 0						
  	EXEC sp_executesql @sqlString			
  	SET @sqlError = @@error					
  	IF (@sqlError <> 0)						
  	BEGIN									
  		IF (99 > @retCode)				
  	  		SET @retCode = 99			
   PRINT 'RETVAL: ' + CONVERT(CHAR(3), 99) + ' [' + @sqlString + ']' + ' SQL Error: ' + CONVERT(CHAR(8), @sqlError)		+ ' [' + @sqlString + ']'						
  		IF (@retCode > 10)					
  			RETURN							
  	END																					
  	END

SELECT @parentID = (SELECT id FROM sysobjects WHERE name = 'cf_SurveyJobStats' AND xType = 'U')								
      IF NOT EXISTS (SELECT * FROM sysindexes WHERE id = @parentID AND name = 'cf_SurveyJobStats_CommservUniqueId_AppId_StartDate_idx')					
      BEGIN																								
  		SET @sqlString = 'CREATE INDEX [' + 'cf_SurveyJobStats_CommservUniqueId_AppId_StartDate_idx' +']' + 'ON [' + 'cf_SurveyJobStats' +']  (' + 'CommservUniqueId, AppId, StartDate' + ')'		
  		IF ('' <> '')							
  		SET @sqlString = ''				
  	SET @retCode = 0						
  	EXEC sp_executesql @sqlString			
  	SET @sqlError = @@error					
  	IF (@sqlError <> 0)						
  	BEGIN									
  		IF (99 > @retCode)				
  	  		SET @retCode = 99			
   PRINT 'RETVAL: ' + CONVERT(CHAR(3), 99) + ' [' + @sqlString + ']' + ' SQL Error: ' + CONVERT(CHAR(8), @sqlError)		+ ' [' + @sqlString + ']'						
  		IF (@retCode > 10)					
  			RETURN							
  	END																					
  	END

SELECT @parentID = (SELECT id FROM sysobjects WHERE name = 'cf_SurveyJobStats' AND xType = 'U')				
      IF EXISTS (SELECT * FROM sysindexes WHERE id = @parentID AND name = 'cf_SurveyJobStats_EndDate_CommservUniqueId_idx')						
      BEGIN																								
  		SET @sqlString = 'DROP INDEX [' + 'cf_SurveyJobStats' +'].[' + 'cf_SurveyJobStats_EndDate_CommservUniqueId_idx' +']'							
  		IF ('' <> '')							
  		SET @sqlString = ''				
  	SET @retCode = 0						
  	EXEC sp_executesql @sqlString			
  	SET @sqlError = @@error					
  	IF (@sqlError <> 0)						
  	BEGIN									
  		IF (99 > @retCode)				
  	  		SET @retCode = 99			
   PRINT 'RETVAL: ' + CONVERT(CHAR(3), 99) + ' [' + @sqlString + ']' + ' SQL Error: ' + CONVERT(CHAR(8), @sqlError)		+ ' [' + @sqlString + ']'						
  		IF (@retCode > 10)					
  			RETURN							
  	END																					
  	END

SELECT @parentID = (SELECT id FROM sysobjects WHERE name = 'cf_SurveyJobStats' AND xType = 'U')								
      IF NOT EXISTS (SELECT * FROM sysindexes WHERE id = @parentID AND name = 'cf_SurveyJobStats_EndDate_CommservUniqueId_AppId_idx')					
      BEGIN																								
  		SET @sqlString = 'CREATE INDEX [' + 'cf_SurveyJobStats_EndDate_CommservUniqueId_AppId_idx' +']' + 'ON [' + 'cf_SurveyJobStats' +']  (' + 'EndDate, CommservUniqueId, AppId' + ')'		
  		IF ('' <> '')							
  		SET @sqlString = ''				
  	SET @retCode = 0						
  	EXEC sp_executesql @sqlString			
  	SET @sqlError = @@error					
  	IF (@sqlError <> 0)						
  	BEGIN									
  		IF (99 > @retCode)				
  	  		SET @retCode = 99			
   PRINT 'RETVAL: ' + CONVERT(CHAR(3), 99) + ' [' + @sqlString + ']' + ' SQL Error: ' + CONVERT(CHAR(8), @sqlError)		+ ' [' + @sqlString + ']'						
  		IF (@retCode > 10)					
  			RETURN							
  	END																					
  	END

SELECT @parentID = (SELECT id FROM sysobjects WHERE name = 'cf_SurveyJobStats' AND xType = 'U')								
      IF NOT EXISTS (SELECT * FROM sysindexes WHERE id = @parentID AND name = 'cf_SurveyJobStats_CommservUniqueId_EndDate_idx')					
      BEGIN																								
  		SET @sqlString = 'CREATE INDEX [' + 'cf_SurveyJobStats_CommservUniqueId_EndDate_idx' +']' + 'ON [' + 'cf_SurveyJobStats' +']  (' + 'CommservUniqueId, EndDate' + ') INCLUDE ('+ 'AppId, Status, DataProtected, DataWritten, FailureErrorCode' + ')'		
  		IF ('' <> '')							
  		SET @sqlString = ''				
  	SET @retCode = 0						
  	EXEC sp_executesql @sqlString			
  	SET @sqlError = @@error					
  	IF (@sqlError <> 0)						
  	BEGIN									
  		IF (99 > @retCode)				
  	  		SET @retCode = 99			
   PRINT 'RETVAL: ' + CONVERT(CHAR(3), 99) + ' [' + @sqlString + ']' + ' SQL Error: ' + CONVERT(CHAR(8), @sqlError)		+ ' [' + @sqlString + ']'						
  		IF (@retCode > 10)					
  			RETURN							
  	END																					
  	END

IF (@retCode < 10)													
  	BEGIN																
  		IF NOT EXISTS (SELECT * FROM GXDBVersions WHERE name = 'cf_SurveyJobStats')			
  			insert into GXDBVersions VALUES(1, 'cf_SurveyJobStats', '0.0', 'cf_SurveyJobStats', '')	
  			UPDATE GXDBVersions SET revision = '1.17' WHERE name = 'cf_SurveyJobStats'		
   PRINT 'RETVAL: ' + CONVERT(CHAR(3), @retCode) + 'Table [' + 'cf_SurveyJobStats' + '] upgraded successfully to '			+ ' revision [' + '1.17' + '].'										
  	END																	
  	else																
  		PRINT 'RETVAL: ' + CONVERT(CHAR(5), @retCode) + ' TABLE ' + 'cf_SurveyJobStats' + ' Upgrade Completed with Errors.'  
  	RETURN			   -- No more upgrade for this TABLE!

END

GO
IF @@error <> 0 RETURN
GO
