

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

--$Id: cf_SurveyMonthlyChargeback.upg,v 1.2.36.4.4.1 2021/06/28 21:05:14 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_SurveyMonthlyChargeback')	
  IF (@oldRev IS NULL)														
  BEGIN                 														
      SET @oldRev = '0.0'														
  END																			
  IF NOT EXISTS (SELECT * FROM sysobjects										
  WHERE type = 'U'															
  AND name = 'cf_SurveyMonthlyChargeback')														
  BEGIN																		
  	SET @oldRev = '0.0'														
  	DELETE FROM GXDBVersions WHERE name = 'cf_SurveyMonthlyChargeback'							
  END																			
  IF (@oldRev = '1.11')       												
  BEGIN																		
      PRINT 'RETVAL: 0   Table [' +'cf_SurveyMonthlyChargeback' +'] does NOT require upgrade.'	
  	
  END																			
  else																		
  BEGIN																		
      PRINT 'Upgrading Table [' +'cf_SurveyMonthlyChargeback' +'].'							
  END-- Get @oldRev, compare to '1.11'. Chk for work to do.


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



--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/CloudDB/Tbl/cf_SurveyMonthlyChargeback.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_SurveyMonthlyChargeback.tbl,v $ $Id: cf_SurveyMonthlyChargeback.tbl,v 1.4.40.4.4.1 2021/06/28 21:05:24 rpolimera Exp $";

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

--

--  BEGIN

--

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

print '... Creating Table: cf_SurveyMonthlyChargeback'

exec ('



DECLARE @tblRetCode integer

Set NOCOUNT ON





create table cf_SurveyMonthlyChargeback (

	CommservUniqueId	INT	NOT NULL,

	SurveyResultId		BIGINT	NOT NULL,

	StartDate			DATETIME,

	Type				TINYINT	NOT NULL,

	ClientId			INT	NOT NULL,

	AppId				INT	NOT NULL,

	CopyId				INT	NOT NULL,

	FrontEndSize		BIGINT	NOT NULL,

	DataProtected		BIGINT	NOT NULL,

	DataWritten			BIGINT	NOT NULL,

	TotalProtectedApp	BIGINT	NOT NULL,

	TotalDataOnMedia	BIGINT	NOT NULL,

	FrontEndSizeType	INT NULL, 	--0 for Backup 1 for Archive 2 for Snap 3 for Replication



) 

set @tblRetCode = @@error

if (@tblRetCode <> 0)

BEGIN

		set @tblRetCode = @@error;

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

END

--------





CREATE CLUSTERED INDEX cf_SurveyMonthlyChargeback_SurveyResultId_ClientId_AppId_CopyId_idx ON cf_SurveyMonthlyChargeback (SurveyResultId, ClientId, AppId, CopyId)

 

CREATE INDEX cf_SurveyMonthlyChargeback_idx2 ON cf_SurveyMonthlyChargeback (CommservUniqueId, StartDate, Type)

 

CREATE INDEX cf_SurveyMonthlyChargeback_Type_StartDate_CCUId_idx ON cf_SurveyMonthlyChargeback (Type, StartDate, CommservUniqueId) INCLUDE (SurveyResultId)

 

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

--

--  END

--

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



insert into GXDBVersions values(1, ''cf_SurveyMonthlyChargeback'',  ''1.4.40.4.4.1'', ''cf_SurveyMonthlyChargeback'', '''')

set @tblRetCode = @@error

if (@tblRetCode <> 0)

BEGIN

	set @tblRetCode = @@error;

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

END

ALTER TABLE cf_SurveyMonthlyChargeback  ADD CONSTRAINT cf_SurveyMonthlyChargeback_SurveyResultId_FK	FOREIGN KEY (SurveyResultId)references	 cf_CommservSurveyResults (CommservSurveyResults_ID) ON DELETE CASCADE



set @tblRetCode = @@error 

if (@tblRetCode <> 0)

BEGIN

	set @tblRetCode = @@error;

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

END

----------


')
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'cf_SurveyMonthlyChargeback' AND COLUMN_NAME='FrontEndSizeType')			
  	BEGIN																														
  		SET @sqlString = 'ALTER TABLE ' + 'cf_SurveyMonthlyChargeback'+ ' ADD ' +'FrontEndSizeType'+ ' ' +'integer'			
  		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_SurveyMonthlyChargeback' AND xType = 'U')				
      IF EXISTS (SELECT * FROM sysindexes WHERE id = @parentID AND name = 'cf_SurveyMonthlyChargeback_idx1')						
      BEGIN																								
  		SET @sqlString = 'DROP INDEX [' + 'cf_SurveyMonthlyChargeback' +'].[' + 'cf_SurveyMonthlyChargeback_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_SurveyMonthlyChargeback' AND xType = 'U')				
      IF EXISTS (SELECT * FROM sysindexes WHERE id = @parentID AND name = 'cf_SurveyMonthlyChargeback_SurveyResultId_idx')						
      BEGIN																								
  		SET @sqlString = 'DROP INDEX [' + 'cf_SurveyMonthlyChargeback' +'].[' + 'cf_SurveyMonthlyChargeback_SurveyResultId_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

IF COL_LENGTH('cf_SurveyMonthlyChargeback', 'SurveyResultId ') = 4

BEGIN

EXECUTE @retCode = cvsp_drop_fkeys @fromTable = 'cf_SurveyMonthlyChargeback', @fromColumn = 'CommservSurveyResults_ID', @toTable = 'cf_CommservSurveyResults'

SELECT @parentID = (SELECT id FROM sysobjects WHERE name = 'cf_SurveyMonthlyChargeback' AND xType = 'U')				
      IF EXISTS (SELECT * FROM sysindexes WHERE id = @parentID AND name = 'cf_SurveyMonthlyChargeback_SurveyResultId_ClientId_AppId_CopyId_idx')						
      BEGIN																								
  		SET @sqlString = 'DROP INDEX [' + 'cf_SurveyMonthlyChargeback' +'].[' + 'cf_SurveyMonthlyChargeback_SurveyResultId_ClientId_AppId_CopyId_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_SurveyMonthlyChargeback' AND xType = 'U')				
      IF EXISTS (SELECT * FROM sysindexes WHERE id = @parentID AND name = 'cf_SurveyMcf_SurveyMonthlyChargeback_SurveyResultId_ClientId_AppId_CopyId_idxonthlyChargeback_SurveyResultId_idx')						
      BEGIN																								
  		SET @sqlString = 'DROP INDEX [' + 'cf_SurveyMonthlyChargeback' +'].[' + 'cf_SurveyMcf_SurveyMonthlyChargeback_SurveyResultId_ClientId_AppId_CopyId_idxonthlyChargeback_SurveyResultId_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

SET @oldDataType='INT'	
  	IF(@oldDataType LIKE '%char%')				
  	BEGIN										
  		IF(@oldDataType LIKE '%(%)%')			
  		BEGIN									
  			SET @oldDataType = SUBSTRING(@oldDataType, 0, CHARINDEX('(', @oldDataType, 0))	
  		END										
  	END			
  	IF (@oldDataType!='BIGINT')																
  			AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'cf_SurveyMonthlyChargeback' AND COLUMN_NAME='SurveyResultId' AND DATA_TYPE = @oldDataType)			
  	BEGIN																														
  		SET @sqlString = 'ALTER TABLE ' + 'cf_SurveyMonthlyChargeback'+ ' ALTER COLUMN ' +'SurveyResultId'+ ' ' +'BIGINT'		
  		+ ' NOT NULL '																											
  		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

SELECT @parentID = (SELECT id FROM sysobjects WHERE name = 'cf_SurveyMonthlyChargeback' AND xType = 'U')				
  	IF NOT EXISTS (SELECT  name FROM sys.foreign_keys WHERE parent_object_id = @parentID AND name = 'cf_SurveyMonthlyChargeback_SurveyResultId_FK')	
  	BEGIN																								
  		SET @sqlString = 'ALTER TABLE [' + 'cf_SurveyMonthlyChargeback' +']' + 'ADD CONSTRAINT [' + 'cf_SurveyMonthlyChargeback_SurveyResultId_FK' +'] FOREIGN KEY (' + 'SurveyResultId' + ') REFERENCES ['+ 'cf_CommservSurveyResults' +'] (' + 'CommservSurveyResults_ID' + ') 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

SELECT @parentID = (SELECT id FROM sysobjects WHERE name = 'cf_SurveyMonthlyChargeback' AND xType = 'U')								
      IF NOT EXISTS (SELECT * FROM sysindexes WHERE id = @parentID AND name = 'cf_SurveyMonthlyChargeback_SurveyResultId_ClientId_AppId_CopyId_idx')					
      BEGIN																								
  		SET @sqlString = 'CREATE CLUSTERED INDEX [' + 'cf_SurveyMonthlyChargeback_SurveyResultId_ClientId_AppId_CopyId_idx' +']' + 'ON [' + 'cf_SurveyMonthlyChargeback' +']  (' + 'SurveyResultId, ClientId, AppId, CopyId' + ')'		
  		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_SurveyMonthlyChargeback' AND xType = 'U')								
      IF NOT EXISTS (SELECT * FROM sysindexes WHERE id = @parentID AND name = 'cf_SurveyMonthlyChargeback_Type_StartDate_CCUId_idx')					
      BEGIN																								
  		SET @sqlString = 'CREATE INDEX [' + 'cf_SurveyMonthlyChargeback_Type_StartDate_CCUId_idx' +']' + 'ON [' + 'cf_SurveyMonthlyChargeback' +']  (' + 'Type, StartDate, CommservUniqueId' + ') INCLUDE ('+ 'SurveyResultId' + ')'		
  		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_SurveyMonthlyChargeback')			
  			insert into GXDBVersions VALUES(1, 'cf_SurveyMonthlyChargeback', '0.0', 'cf_SurveyMonthlyChargeback', '')	
  			UPDATE GXDBVersions SET revision = '1.11' WHERE name = 'cf_SurveyMonthlyChargeback'		
   PRINT 'RETVAL: ' + CONVERT(CHAR(3), @retCode) + 'Table [' + 'cf_SurveyMonthlyChargeback' + '] upgraded successfully to '			+ ' revision [' + '1.11' + '].'										
  	END																	
  	else																
  		PRINT 'RETVAL: ' + CONVERT(CHAR(5), @retCode) + ' TABLE ' + 'cf_SurveyMonthlyChargeback' + ' Upgrade Completed with Errors.'  
  	RETURN			   -- No more upgrade for this TABLE!

END

GO
IF @@error <> 0 RETURN
GO
