

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

-- ----------------------------------------------------------------------
--
--           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.
-- ----------------------------------------------------------------------*/
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='ArchGetSubclientMountpathList')
	delete from GXDBVersions where aliasname = 'ArchGetSubclientMountpathList'
GO
print '... Creating Procedure: ArchGetSubclientMountpathList'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure ArchGetSubclientMountpathList
  @i_subClientId integer,
  @i_flag integer,
  @i_MediaAgentId integer,
  @i_CallerClientId integer
AS
  DECLARE @retVal integer;
  DECLARE @Path VARCHAR(2048);
  DECLARE @MountPathId integer;
  DECLARE @FullTimeStamp integer;
  DECLARE @MediaAgentId integer;
  DECLARE @MangledMediaAgentName VARCHAR(2048);
--This will turn off message: "xxx rows affected".
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
DECLARE @isActiveMPfull INT = 0
DECLARE @useLogArchGrp INT = 0;
DECLARE @now INT = dbo.Getunixtime(Getutcdate())
DECLARE @mpID INT = 0
DECLARE @newMPId INT = 0
DECLARE @currentClientId INT = 0
DECLARE @dataServerBits INT = (16|32|128)
SET @retVal = 0
IF @i_flag & 1 > 0
  SET @useLogArchGrp = 1
SET @i_MediaAgentId = ISNULL(@i_MediaAgentId,0)
SET @mpID = ISNULL((SELECT TOP 1 mountPathId FROM APP_SubclientToMountpathMapping WITH (READUNCOMMITTED) WHERE fullTimeStamp = 0
AND subClientId = @i_subClientId ) , 0)
IF Object_id('tempdb.dbo.#listMPs') IS NOT NULL   DROP TABLE #listMPs
CREATE TABLE #listMPs
  (
     LibraryId INT,
     DeviceId  INT,
     mpid      INT,
     path      VARCHAR(2048),
     mpName		NVARCHAR(2048),
     Cname    VARCHAR(2048),
     MAid		INT,
     LastUseTime INT,
     isFillSpill INT,
     DpFlag	INT,
     pathFormat VARCHAR(2)
  )
IF @i_flag & 2 > 0
BEGIN
  SET @currentClientId = (SELECT clientId FROM APP_Application WITH (READUNCOMMITTED) WHERE id = @i_subClientId)
  --list all local MPs
	DECLARE @DpConfig INT = 0
	DECLARE @copyId INT = 0
	DECLARE @archGroupId INT = 0
	SELECT @archGroupId = CASE WHEN @useLogArchGrp = 1 THEN logArchGrpId ELSE dataArchGrpId END FROM APP_Application WITH (NOLOCK) WHERE id = @i_subClientId
	-- check if the given client belongs to a different region group and if there is a different storage policy pointing to the new regional client group.
	IF @i_CallerClientId > 0
	AND	EXISTS (SELECT  1
				FROM    APP_AdvanceSettings WITH(READUNCOMMITTED)
				WHERE   keyname = 'EnableRegionBasedBackups'
				AND     entityId = @i_CallerClientId
AND     entityType = 3
				AND     relativePath = 'iDataAgent'
				AND     CONVERT(NVARCHAR(10), value) = N'true'
				AND     enabled = 1
				AND     deleted = 0)
	AND EXISTS (SELECT 1 FROM APP_ClientGroupAssoc a WITH (NOLOCK), ArchGroupClientGroupAssociation b WITH (NOLOCK)
				WHERE	a.clientId = @i_CallerClientId
				AND		a.clientGroupId = b.clientGroupId
				AND		b.archGroupId > 0
				AND		attributes & 1 = 1 /*Region based client group backup*/
				AND		enabled = 1)
	BEGIN
		SELECT @archGroupId = b.archGroupId
		FROM	APP_ClientGroupAssoc a WITH (NOLOCK), ArchGroupClientGroupAssociation b WITH (NOLOCK)
		WHERE	a.clientId = @i_CallerClientId
		AND		a.clientGroupId = b.clientGroupId
		AND		b.archGroupId > 0
		AND		attributes & 1 = 1 /*Region based client group backup*/
		AND		enabled = 1
		-- Check log storage policy setting for log data type
		IF @useLogArchGrp = 1
		BEGIN
			SELECT @archGroupId = logSp FROM ArchGroup WITH (NOLOCK) WHERE Id = @archGroupId AND logSP > 0
		END
	END
	SELECT @DpConfig = AGC.flags,@copyId = AGC.id
	FROM	archGroupCopy AGC WITH (READUNCOMMITTED), archGroup AG WITH (READUNCOMMITTED)
	WHERE	AG.id = @archGroupId
	AND		AG.defaultCopy = AGC.id
	INSERT INTO #listMPs
	SELECT DISTINCT MP.LibraryId,DC.DeviceId, MP.MountPathId, DC.folder, MP.MountPathName,CN.name,CN.id, D.LastUseTime, 0, DP.Flag,
	(CASE SO.Name  WHEN 'Linux' THEN '/' ELSE '\\' END)
	FROM   MMDatapath DP WITH (READUNCOMMITTED)
	INNER JOIN MMDrivePool DVP WITH (READUNCOMMITTED) ON DP.DrivePoolId = DVP.DrivePoolId
	INNER JOIN MMMountPath MP WITH (READUNCOMMITTED) ON DVP.MasterPoolId = MP.MasterPoolId
	INNER JOIN MMMountPathToStorageDevice MSD WITH (READUNCOMMITTED) ON  MSD.MountPathId = MP.MountPathId
	INNER JOIN MMMediaSide MS WITH (READUNCOMMITTED) ON MS.MediaSideId = MP.MediaSideId
	INNER JOIN MMDrive D WITH (READUNCOMMITTED) ON D.MediaId = MS.MediaId
	INNER JOIN MMDeviceController DC WITH (READUNCOMMITTED) ON DC.DeviceId = MSD.DeviceId
	INNER JOIN App_Client CN WITH (READUNCOMMITTED) ON CN.id = DC.clientId
	LEFT OUTER JOIN APP_VMToPMMap VC WITH(READUNCOMMITTED) ON CN.Id = VC.VMClientId
	INNER JOIN App_Client C WITH (READUNCOMMITTED) ON C.id = ISNULL(VC.PMClientId,CN.Id)
	INNER JOIN simOperatingSystem SO WITH (READUNCOMMITTED) ON C.simOperatingSystemId = SO.id
	INNER JOIN simInstalledPackages SI WITH (READUNCOMMITTED) ON C.id = SI.ClientId
WHERE  DP.Flag & 4 > 0
		   AND DP.SpareGroupId = 0
		   AND DP.CopyId = @copyId
		   AND DC.deviceAccessType & ( @dataServerBits ) = 0
		   AND DC.ClientId = DVP.ClientId
		   AND MP.MountPathTypeId <> 7 /*MOUNT_PATH_EXTERNAL_REMOTE_HOST*/
		   AND (SO.Name  LIKE 'Linux' OR SO.Name LIKE 'Windows%')
		   AND (C.ReleaseId > 16 OR (C.ReleaseId = 16 AND SI.HighestSP >= 20))
	--validation of MPs
	--only take MPs that are online
	DELETE MPs
	FROM   #listMPs MPs,
		   MMLibrary L WITH (READUNCOMMITTED),
		   MMDevice D WITH (READUNCOMMITTED),
		   MMMountPath MP WITH (READUNCOMMITTED),
		   MMDeviceController DVC WITH (READUNCOMMITTED)
	WHERE   L.LibraryId = MPs.LibraryId
		   AND MP.MountPathId = MPs.mpid
		   AND D.DeviceId = mps.DeviceId
		   AND DVC.DeviceId = D.DeviceId
		   AND DVC.ClientId = MPs.MAid
		   AND DVC.deviceAccessType & ( @dataServerBits ) = 0
		   AND ( D.deviceenabled = 0
		   OR D.devicebroken > 0
		   OR DVC.DeviceControllerEnabled = 0
		   OR DVC.DeviceAccessible = 0
OR DVC.DeviceAccessType & 2 = 0
		   OR L.librarybroken > 0
		   OR L.libraryenabled = 0
		   OR MP.isenabled = 0
		   OR MP.isoffline > 0
		   OR MP.MaxConcurrentWriters <= 0
OR MP.Attribute & 4096 > 0)
	-- only take the MP that has enough free space
	DELETE FROM M
	FROM   #listMPs M ,MMMountPath MP WITH (READUNCOMMITTED), MMMediaside MS WITH (READUNCOMMITTED)
	WHERE  MP.MountPathId = m.mpid
		   AND MP.MediaSideId = MS.MediaSideId
		   AND MS.FreeBytesMB < ( MP.MagneticSpaceRsrvInMB +
4096 )
	IF NOT EXISTS (SELECT 1 FROM #listMPs)
	BEGIN
		-- no MP available
		SET @retVal = 1
		GOTO PROC_EXIT
	END
	DELETE FROM #listMPs
	WHERE MAid = @currentClientId
		IF NOT EXISTS (SELECT 1 FROM #listMPs)
	BEGIN
		-- no MP available is from MA different from Client
		SET @retVal = 2
		GOTO PROC_EXIT
	END
	--If preferred
IF (@DpConfig & 512) = 0
AND (@DpConfig & (8 + 16 )) = 0
	AND EXISTS (SELECT 1 FROM #listMPs WHERE DpFlag & 1  > 0)
	BEGIN
	--if default path present, then Delete all non default DPs
		DELETE FROM #listMPs
WHERE DpFlag & 1 = 0
	END
	UPDATE L
	SET isFillSpill = (CASE
WHEN ExtendedAttributes & 1 = 1 THEN 0
			ELSE 1
		END)
		FROM  #listMPs L, MMLibrary L1 WITH (NOLOCK)
			WHERE  L.LibraryId = L1.LibraryId
	IF EXISTS (SELECT 1 FROM #listMPs WHERE mpId = @mpID)
		SET @newMPId = @mpID
	ELSE
		SET @newMPId = (
		SELECT TOP 1 L.mpid
		FROM   #listMPs L
ORDER BY (CASE WHEN (@DpConfig & (8 + 16 )) > 0 AND DpFlag & 1 > 0
                       THEN 0 ELSE 1 END),
				 (CASE WHEN isFillSpill = 1
					   THEN -LastUseTime
					   ELSE
					   (
							SELECT COUNT(1) FROM APP_SubclientToMountpathMapping SM  WITH (READUNCOMMITTED)
							WHERE L.mpId = SM.mountPathId AND SM.fullTimeStamp = 0
							GROUP BY SM.mountPathId
					   )
				  END)
		  )
	BEGIN TRANSACTION
		-- update entry and insert new
		UPDATE SM
		SET fullTimeStamp = @now
		FROM APP_SubclientToMountpathMapping SM
		WHERE SM.subClientId = @i_subClientId
		AND SM.fullTimeStamp <= 0
		AND NOT EXISTS(SELECT 1 FROM #listMPs M WHERE M.mpid = SM.mountPathId)
		SET @retVal = @@ERROR
		IF @retVal <> 0
		BEGIN
			ROLLBACK TRANSACTION
			GOTO PROC_EXIT
		END
		IF @newMPId <> @mpID
		BEGIN
			DECLARE @rowsUpdated INT = 0
			UPDATE APP_SubclientToMountpathMapping
			SET fullTimeStamp = 0
			WHERE mountPathId = @newMPId
			AND subClientId = @i_subClientId
			AND fullTimeStamp <> 0
			SELECT @retVal = @@ERROR, @rowsUpdated = @@ROWCOUNT
			IF @retVal <> 0
			BEGIN
				ROLLBACK TRANSACTION
				GOTO PROC_EXIT
			END
			IF @rowsUpdated = 0
				INSERT INTO APP_SubclientToMountpathMapping
				SELECT @i_subClientId, 0 , @newMPId
			SET @retVal = @@ERROR
			IF @retVal <> 0
			BEGIN
				ROLLBACK TRANSACTION
				GOTO PROC_EXIT
			END
		END
		-- Incase there already is an entry in the table for failover mp that was unusable before but now is eligible for failover again
		UPDATE APP_SubclientToMountpathMapping
		SET fullTimeStamp = -1
		FROM #listMPs
		INNER JOIN APP_SubclientToMountpathMapping
		ON mountPathId = mpid
		AND subClientId = @i_subClientId
		WHERE mpid <> @newMPId
		AND fullTimeStamp > 0
		SET @retVal = @@ERROR
		IF @retVal <> 0
		BEGIN
			ROLLBACK TRANSACTION
			GOTO PROC_EXIT
		END
		--incase of a new entry for a failover mountpath
		INSERT INTO APP_SubclientToMountpathMapping
		SELECT DISTINCT @i_subClientId, -1 , mpid
		FROM #listMPs
		LEFT JOIN APP_SubclientToMountpathMapping
		ON mountPathId = mpid
		AND subClientId = @i_subClientId
		WHERE mpid <> @newMPId
		AND mountPathId IS NULL
		SET @retVal = @@ERROR
		IF @retVal <> 0
		BEGIN
			ROLLBACK TRANSACTION
			GOTO PROC_EXIT
		END
	-- Make an entry in APP_ClientAccessControl table for the priviled client(for 3dfs security)
	EXEC App_UpdateClientAccessControl @i_CallerClientId, @currentClientId
	COMMIT TRANSACTION
	SET @mpID = @newMPId
END
ELSE IF (@i_flag & 4 > 0)
  -- for online backup , return all MPs
  BEGIN
	DECLARE @MP TABLE
	(
	   mpid          INT,
	   fulltimestamp INT,
	   dp            VARCHAR(2048),
	   mpName        NVARCHAR(2048),
	   Cname        VARCHAR(2048),
	   MAid			INT,
	   mangledMA	VARCHAR(MAX),
	   pathFormat	VARCHAR(2)
	)
	INSERT INTO @MP
			  (mpid,
			   fulltimestamp)
	SELECT mountPathId, fullTimeStamp FROM APP_SubclientToMountpathMapping WITH (READUNCOMMITTED)
	WHERE subClientId = @i_subClientId
	UPDATE M
	SET    dp = DC.folder,
	mpName =  MP.MountPathName,
	Cname = CN.NAME,
	MAid = CN.id,
	mangledMA = dbo.GetClientMangledHostName(C.id,NULL),
	pathFormat = (CASE SO.Name  WHEN 'Linux' THEN '/' ELSE '\\' END)
	FROM   @MP M
		INNER JOIN MMMountPathToStorageDevice MSD WITH (READUNCOMMITTED) ON MSD.MountPathId = M.mpid
		INNER JOIN MMMountPath MP WITH (READUNCOMMITTED) ON MP.MountPathId = M.mpId
		INNER JOIN MMDeviceController DC WITH (READUNCOMMITTED) ON DC.DeviceId = MSD.DeviceId
		INNER JOIN App_Client CN WITH (READUNCOMMITTED) ON CN.id = DC.clientId
		LEFT OUTER JOIN APP_VMToPMMap VC WITH(READUNCOMMITTED) ON CN.Id = VC.VMClientId
		INNER JOIN App_Client C WITH (READUNCOMMITTED) ON C.id = ISNULL(VC.PMClientId, CN.Id)
		INNER JOIN simOperatingSystem SO WITH (READUNCOMMITTED) ON C.simOperatingSystemId = SO.id
		INNER JOIN simInstalledPackages SI WITH (READUNCOMMITTED) ON C.id = SI.ClientId
		WHERE (SO.Name  LIKE 'Linux' OR SO.Name LIKE 'Windows%')
			AND (C.ReleaseId > 16 OR (C.ReleaseId = 16 AND SI.HighestSP >= 19))
AND DC.deviceAccessType & ( @dataServerBits|4 ) = 4
			AND (@i_MediaAgentId = 0 OR DC.ClientId = @i_MediaAgentId)
  END
PROC_EXIT:
IF @retVal = 0
BEGIN
IF @i_flag & 2 > 0
	SELECT * FROM (SELECT  @retVal as retVal, 'IP | ' + (CASE WHEN RIGHT(path,1) IN ('\', '/') THEN path ELSE  path + L.pathFormat END)  + L.mpName + L.pathFormat + 'CV_MAGNETIC' + ' | ' + Cname as Path,
	mpid as MountPathId, (CASE WHEN L.mpid = @mpID THEN  0  ELSE -1 END) as FullTimeStamp, MAid as MediaAgentId,dbo.GetClientMangledHostName(L.MAid,NULL) as MangledMediaAgentName
	FROM #listMPs L) TMP
	ORDER  BY CASE WHEN FullTimeStamp = 0 THEN 1 ELSE 2 END
ELSE IF @i_flag & 4 > 0
	SELECT @retVal as retVal, 'IP | ' +  (CASE WHEN RIGHT(dp,1) IN ('\', '/') THEN dp ELSE  dp + pathFormat END)  + mpName + pathFormat + 'CV_MAGNETIC'+ ' | ' + Cname as Path,
	mpid as MountPathId, fulltimestamp as FullTimeStamp, MAid as MediaAgentId, mangledMA as MangledMediaAgentName
	FROM   @MP
	WHERE MAid > 0
	ORDER  BY CASE WHEN fulltimestamp = 0 THEN 1 ELSE 2 END
END
ELSE
	SELECT @retVal as retVal, ' ' as Path, 0 as MountPathId, 0 as FullTimeStamp, 0 as MediaAgentId, '' as MangledMediaAgentName
RETURN;
GO

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

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

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

