

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/IdxGetMAWithoutIndexSubClient.sp] ---------- 

-- ----------------------------------------------------------------------
--
--           Copyright (c) 2011  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.
-- ----------------------------------------------------------------------*/
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='IdxGetMAWithoutIndexSubClient')
BEGIN
	print '>>> Drop Stored Procedure: IdxGetMAWithoutIndexSubClient <<<'
	drop procedure IdxGetMAWithoutIndexSubClient
END
IF EXISTS (select * from GxQscripts where name='IdxGetMAWithoutIndexSubClient')
	delete from GxQscripts where name = 'IdxGetMAWithoutIndexSubClient'
GO

IF EXISTS (select * from GXDBVersions where aliasname='IdxGetMAWithoutIndexSubClient')
	delete from GXDBVersions where aliasname = 'IdxGetMAWithoutIndexSubClient'
GO
print '... Creating Procedure: IdxGetMAWithoutIndexSubClient'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure IdxGetMAWithoutIndexSubClient
  @i_CommCellId integer,
  @i_reserveParam integer
AS
-- Following are the "columns" returned, in the order in which they are returned
  DECLARE @o_MAClientId integer
  DECLARE @o_MAClientName nvarchar(256)
  DECLARE @o_subClientStatusFlag integer
  DECLARE @o_archGroupIdToAssign integer
DECLARE @curtime INT = DBO.GetUnixTime(getdate())
-- clean left overs
IF OBJECT_ID('tempdb.dbo.#l_IndexMAs') IS NOT NULL DROP TABLE #l_IndexMAs
IF OBJECT_ID('tempdb.dbo.#IdxClients') IS NOT NULL DROP TABLE #IdxClients
IF OBJECT_ID('tempdb.dbo.#OldIdxApps') IS NOT NULL DROP TABLE #OldIdxApps
-- Get existing list of Index server clients
CREATE TABLE #IdxClients (clientId INT, appId INT, archGroupPropVal INT, assignedArchGroupId INT, addProp INT, afilesFound INT, PRIMARY KEY (clientId))
INSERT INTO #IdxClients
SELECT DISTINCT APP.clientId clientId, 0, 0, 0, 0, 0
FROM APP_Application APP WITH (NOLOCK)
JOIN APP_InstanceProp IP WITH (NOLOCK) ON APP.instance = IP.componentNameId
WHERE IP.attrName =  'Distributed Cluster Type' AND IP.attrVal = 7 AND IP.modified = 0
-- VALIDATION for existing index server clients AND set missing stuff
UPDATE IDXC SET archGroupPropVal = ISNULL(CP.attrVal,0)
FROM #IdxClients IDXC
JOIN APP_ClientProp CP WITH (NOLOCK) ON IDXC.clientId = CP.componentNameId
WHERE CP.attrName = 'Index Server ArchGroup Id' AND CP.modified = 0
UPDATE IDXC SET appId = ISNULL(APP.id, 0), assignedArchGroupId = ISNULL(APP.dataArchGrpID, 0)
FROM #IdxClients IDXC
JOIN APP_Application APP WITH (NOLOCK) ON APP.clientId = IDXC.clientId
-- Get list of old Index backup subclients
CREATE TABLE #OldIdxApps (appId INT, PRIMARY KEY (appId))
INSERT INTO #OldIdxApps
SELECT DISTINCT APP.id appId
FROM APP_Application APP WITH (NOLOCK)
JOIN APP_SubClientProp APROP WITH (NOLOCK) ON APP.id = APROP.componentNameId AND APROP.attrName = 'Index SubClient' and APROP.attrVal = '1' AND APROP.modified = 0
LEFT JOIN #IdxClients IDXC ON IDXC.clientId = APP.clientId
WHERE IDXC.clientId IS NULL
-- VALIDATION for existing index server clients
UPDATE IDXC
SET addProp = ISNULL(CP.attrVal,1),
archGroupPropVal = (CASE WHEN CP.attrVal IS NOT NULL THEN CP.attrVal
						WHEN IDXC.assignedArchGroupId = AG.id AND C.name = AG.name + '_IndexServer' THEN AG.id
						WHEN C.name = AG.name + '_IndexServer' THEN AG.id END)
FROM #IdxClients IDXC
JOIN APP_Client C WITH (NOLOCK) ON IDXC.clientId = C.id
JOIN archGroup AG WITH (NOLOCK) ON AG.ID > 1 AND AG.flags & 262144 = 0 AND IDXC.assignedArchGroupId = AG.id OR C.name = AG.name + '_IndexServer'
LEFT JOIN APP_ClientProp CP WITH (NOLOCK) ON IDXC.clientId = CP.componentNameId AND CP.attrName = 'Index Server ArchGroup Id' AND CP.modified = 0
WHERE (IDXC.assignedArchGroupId = AG.id AND C.name = AG.name + '_IndexServer') OR C.name = AG.name + '_IndexServer'
-- Identify clients that has backups (this is to safe guard from deleting these clients)
UPDATE IDXC
SET afilesFound = 1
FROM #IdxClients IDXC
WHERE EXISTS (SELECT TOP 1 appId FROM archFile AF WITH(NOLOCK) WHERE AF.appId = IDXC.appId)
-- Add the missing property for existing clients
INSERT INTO APP_ClientProp (componentNameId, attrName, attrType, attrVal, created, modified, ccpId)
SELECT IDXC.clientId, 'Index Server ArchGroup Id', 7, IDXC.archGroupPropVal, CONVERT(NVARCHAR(32),@curtime), 0, 0
FROM #IdxClients IDXC
WHERE IDXC.addProp = 1
-- create index server clients for new storage policies
CREATE TABLE #l_IndexMAs (clientId INT, clientName NVARCHAR(510), flags INT, archGroupId INT)
INSERT INTO #l_IndexMAs
SELECT DISTINCT
ISNULL(IDXC.clientId, 0) clientId, AG.name + '_IndexServer' clientName, 0 flags, AG.ID archGroupId
FROM archGroup AG WITH (NOLOCK)
JOIN APP_Application APP WITH (NOLOCK) ON AG.id > 1 AND AG.flags & 262144 = 0 AND APP.dataArchGrpID = AG.id AND NOT EXISTS (SELECT 1 FROM #OldIdxApps WHERE APP.id = appId)
JOIN App_IndexDBInfo IDX WITH (NOLOCK) ON APP.backupSet = IDX.backupSetId
LEFT JOIN #IdxClients IDXC ON IDXC.archGroupPropVal = AG.id
WHERE IDXC.clientId IS NULL
-- Delete any index server clients, not created properly.. they will be recreated in next attempt
INSERT INTO #l_IndexMAs
SELECT DISTINCT IDXC.clientId clientid, C.name, 1 flags, 0
FROM #IdxClients IDXC
JOIN APP_Client C WITH (NOLOCK) ON IDXC.clientId = C.id
WHERE IDXC.appId = 0
-- Delete old index subclients that doesn't have valid data
-- Disable backup activity on old index backup subclients
INSERT INTO #l_IndexMAs
SELECT DISTINCT IDXAPP.appId, '',
(CASE WHEN AF.appId IS NULL THEN 2 WHEN JMA.appId IS NULL THEN 3 END) flags, 0
FROM #OldIdxApps IDXAPP
JOIN APP_Application APP WITH (NOLOCK) ON APP.id = IDXAPP.appId AND (APP.subclientStatus & 4) = 0
JOIN simInstalledPackages SP WITH (NOLOCK) ON APP.clientId = SP.ClientId AND SP.HighestSP >= 11
LEFT JOIN JMJobAction JMA WITH (NOLOCK) ON JMA.appId = APP.id and action = 1
LEFT JOIN ARCHFILE AF WITH (NOLOCK) ON AF.appId = APP.id
WHERE AF.appId IS NULL OR JMA.appId IS NULL
-- Rename Index server clients to match with storage policy name
INSERT INTO #l_IndexMAs
SELECT DISTINCT C.id clientid, AG.name + '_IndexServer' clientName, 4, AG.id
FROM #IdxClients IDXC
JOIN APP_Client C WITH (NOLOCK) ON C.id = IDXC.clientId
JOIN archGroup AG WITH (NOLOCK) ON AG.id > 1 AND AG.flags & 262144 = 0 AND AG.id = IDXC.archGroupPropVal
WHERE C.name <> AG.name + '_IndexServer'
-- Clean Index server clients that has their storage policies deleted
-- Same time rest storage policy for any index server if its assigned storage policy is deleted but original policy is still available
INSERT INTO #l_IndexMAs
SELECT DISTINCT (CASE WHEN AG.id IS NULL AND IDXC.afilesFound = 0 THEN IDXC.clientId ELSE IDXC.appId END), C.name,
(CASE WHEN AG.id IS NULL AND IDXC.afilesFound = 0 THEN 5 ELSE 6 END) flags,
(CASE WHEN AG.id IS NOT NULL THEN AG.id END) archGroupId
FROM #IdxClients IDXC
JOIN APP_Client C WITH (NOLOCK) ON C.id = IDXC.clientId AND (IDXC.assignedArchGroupId = 1 OR (IDXC.archGroupPropVal <>  IDXC.assignedArchGroupId AND NOT EXISTS(SELECT 1 FROM archGroup AG WITH(NOLOCK) WHERE AG.id = IDXC.archGroupPropVal) ) )
LEFT JOIN archGroup AG WITH (NOLOCK) ON AG.id > 1 AND AG.flags & 262144 = 0 AND IDXC.archGroupPropVal = AG.id
-- OUTPUT
SELECT * FROM #l_IndexMAs order by archGroupId
--CLEAN
IF OBJECT_ID('tempdb.dbo.#l_IndexMAs') IS NOT NULL DROP TABLE #l_IndexMAs
IF OBJECT_ID('tempdb.dbo.#IdxClients') IS NOT NULL DROP TABLE #IdxClients
IF OBJECT_ID('tempdb.dbo.#OldIdxApps') IS NOT NULL DROP TABLE #OldIdxApps
GO

IF EXISTS (select * from GxQscripts where name = 'IdxGetMAWithoutIndexSubClient')
	delete from GxQscripts where name = 'IdxGetMAWithoutIndexSubClient'
GO

IF EXISTS (select * from GXDBVersions where aliasname='IdxGetMAWithoutIndexSubClient')
	delete from GXDBVersions where aliasname = 'IdxGetMAWithoutIndexSubClient'
GO

insert into GXDBVersions values(2, 'IdxGetMAWithoutIndexSubClient',  '00000000000000000000', 'IdxGetMAWithoutIndexSubClient', '00000000000000000000')
GO

