def setTasksDone( self, taskIDs ): """ This will update the status for a list of taskIDs to Done. """ reqSelect = "SELECT * FROM Tasks WHERE TaskID IN (%s);" % intListToString( taskIDs ) resSelect = self._query( reqSelect ) if not resSelect['OK']: gLogger.error( "%s.%s_DB: problem retrieving record: %s. %s" % ( self._caller(), 'setTasksDone', reqSelect, resSelect['Message'] ) ) req = "UPDATE Tasks SET Status = 'Done', CompleteTime = UTC_TIMESTAMP() WHERE TaskID IN (%s);" % intListToString( taskIDs ) res = self._update( req ) if not res['OK']: gLogger.error( "StorageManagementDB.setTasksDone: Failed to set Tasks status to Done.", res['Message'] ) return res for record in resSelect['Value']: gLogger.info( "%s.%s_DB: to_update Tasks = %s" % ( self._caller(), 'setTasksDone', record ) ) #fix, no individual queries reqSelect1 = "SELECT * FROM Tasks WHERE TaskID IN (%s);" % intListToString( taskIDs ) resSelect1 = self._query( reqSelect1 ) if not resSelect1['OK']: gLogger.info( "%s.%s_DB: problem retrieving record: %s. %s" % ( self._caller(), 'setTasksDone', reqSelect1, resSelect1['Message'] ) ) for record in resSelect1['Value']: gLogger.info( "%s.%s_DB: updated Tasks = %s" % ( self._caller(), 'setTasksDone', record ) ) gLogger.debug( "StorageManagementDB.setTasksDone: Successfully updated %s Tasks with StageStatus=Done for taskIDs: %s." % ( res['Value'], taskIDs ) ) return res
def _getDirectoryReplicas( self, dirID, allStatus=False, connection=False ): """ Get replicas for files in a given directory """ replicaStatusIDs = [] if not allStatus: for status in self.db.visibleReplicaStatus: result = self._getStatusInt( status, connection=connection ) if result['OK']: replicaStatusIDs.append( result['Value'] ) fileStatusIDs = [] if not allStatus: for status in self.db.visibleFileStatus: result = self._getStatusInt( status, connection=connection ) if result['OK']: fileStatusIDs.append( result['Value'] ) if not self.db.lfnPfnConvention or self.db.lfnPfnConvention == "Weak": req = 'SELECT FF.FileName,FR.FileID,FR.SEID,FI.PFN FROM FC_Files as FF,' req += ' FC_Replicas as FR, FC_ReplicaInfo as FI' req += ' WHERE FF.FileID=FR.FileID AND FR.RepID=FI.RepID AND FF.DirID=%d ' % dirID if replicaStatusIDs: req += ' AND FR.Status in (%s)' % intListToString( replicaStatusIDs ) if fileStatusIDs: req += ' AND FF.Status in (%s)' % intListToString( fileStatusIDs ) else: req = "SELECT FF.FileName,FR.FileID,FR.SEID,'' FROM FC_Files as FF," req += ' FC_Replicas as FR' req += ' WHERE FF.FileID=FR.FileID AND FF.DirID=%d ' % dirID if replicaStatusIDs: req += ' AND FR.Status in (%s)' % intListToString( replicaStatusIDs ) if fileStatusIDs: req += ' AND FF.Status in (%s)' % intListToString( fileStatusIDs ) result = self.db._query( req, connection ) return result
def setStageComplete( self, replicaIDs ): # Daniela: FIX wrong PinExpiryTime (84000->86400 seconds = 1 day) reqSelect = "SELECT * FROM StageRequests WHERE ReplicaID IN (%s);" % intListToString( replicaIDs ) resSelect = self._query( reqSelect ) if not resSelect['OK']: gLogger.info( "%s.%s_DB: problem retrieving record: %s. %s" % ( self._caller(), 'setStageComplete', reqSelect, resSelect['Message'] ) ) return resSelect req = "UPDATE StageRequests SET StageStatus='Staged',StageRequestCompletedTime = UTC_TIMESTAMP(),PinExpiryTime = DATE_ADD(UTC_TIMESTAMP(),INTERVAL ( PinLength / %s ) SECOND) WHERE ReplicaID IN (%s);" % ( THROTTLING_STEPS, intListToString( replicaIDs ) ) res = self._update( req ) if not res['OK']: gLogger.error( "StorageManagementDB.setStageComplete: Failed to set StageRequest completed.", res['Message'] ) return res for record in resSelect['Value']: gLogger.info( "%s.%s_DB: to_update StageRequests = %s" % ( self._caller(), 'setStageComplete', record ) ) reqSelect1 = "SELECT * FROM StageRequests WHERE ReplicaID IN (%s);" % intListToString( replicaIDs ) resSelect1 = self._query( reqSelect1 ) if not resSelect1['OK']: gLogger.info( "%s.%s_DB: problem retrieving record: %s. %s" % ( self._caller(), 'setStageComplete', reqSelect1, resSelect1['Message'] ) ) for record in resSelect1['Value']: gLogger.info( "%s.%s_DB: updated StageRequests = %s" % ( self._caller(), 'setStageComplete', record ) ) gLogger.debug( "StorageManagementDB.setStageComplete: Successfully updated %s StageRequests table with StageStatus=Staged for ReplicaIDs: %s." % ( res['Value'], replicaIDs ) ) return res
def __findFilesForSE( self, se, dirList ): """ Find files in the given list of directories having replicas in the given se(s) """ seList = se if type( se ) in types.StringTypes: seList = [se] seIDs = [] for se in seList: result = self.db.seManager.getSEID( se ) if not result['OK']: return result seIDs.append( result['Value'] ) seString = intListToString( seIDs ) dirString = intListToString( dirList ) req = "SELECT F.FileID FROM FC_Files as F, FC_Replicas as R WHERE F.DirID IN (%s)" % dirString req += " AND R.SEID IN (%s) AND F.FileID=R.FileID" % seString result = self.db._query( req ) if not result['OK']: return result if not result['Value']: return S_OK( [] ) fileList = [] for row in result['Value']: fileID = row[0] fileList.append( fileID ) return S_OK( fileList )
def _getFileReplicas(self, fileIDs, fields=["PFN"], connection=False): connection = self._getConnection(connection) if not fileIDs: return S_ERROR("No such file or directory") req = "SELECT FileID,SEID,Status,%s FROM FC_Replicas WHERE FileID IN (%s);" % ( intListToString(fields), intListToString(fileIDs), ) res = self.db._query(req, connection) if not res["OK"]: return res replicas = {} for tuple in res["Value"]: fileID = tuple[0] if not replicas.has_key(fileID): replicas[fileID] = {} seID = tuple[1] res = self.db.seManager.getSEName(seID) if not res["OK"]: continue seName = res["Value"] statusID = tuple[2] res = self._getIntStatus(statusID, connection=connection) if not res["OK"]: continue status = res["Value"] replicas[fileID][seName] = {"Status": status} replicas[fileID][seName].update(dict(zip(fields, tuple[3:]))) for fileID in fileIDs: if not replicas.has_key(fileID): replicas[fileID] = {} return S_OK(replicas)
def _getFileReplicas(self, fileIDs, fields_input=["PFN"], allStatus=False, connection=False): """ Get replicas for the given list of files specified by their fileIDs """ fields = list(fields_input) connection = self._getConnection(connection) res = self.__getFileIDReplicas(fileIDs, allStatus=allStatus, connection=connection) if not res["OK"]: return res fileIDDict = res["Value"] if fileIDDict: if "Status" in fields: fields.remove("Status") repIDDict = {} if fields: req = "SELECT RepID,%s FROM FC_ReplicaInfo WHERE RepID IN (%s);" % ( intListToString(fields), intListToString(fileIDDict.keys()), ) res = self.db._query(req, connection) if not res["OK"]: return res for tuple_ in res["Value"]: repID = tuple_[0] repIDDict[repID] = dict(zip(fields, tuple_[1:])) statusID = fileIDDict[repID][2] res = self._getIntStatus(statusID, connection=connection) if not res["OK"]: continue repIDDict[repID]["Status"] = res["Value"] else: for repID in fileIDDict: statusID = fileIDDict[repID][2] res = self._getIntStatus(statusID, connection=connection) if not res["OK"]: continue repIDDict[repID] = {"Status": res["Value"]} seDict = {} replicas = {} for repID in fileIDDict.keys(): fileID, seID, statusID = fileIDDict[repID] replicas.setdefault(fileID, {}) if not seID in seDict: res = self.db.seManager.getSEName(seID) if not res["OK"]: continue seDict[seID] = res["Value"] seName = seDict[seID] replicas[fileID][seName] = repIDDict.get(repID, {}) if len(replicas) != len(fileIDs): for fileID in fileIDs: if not replicas.has_key(fileID): replicas[fileID] = {} return S_OK(replicas)
def _getFileDescendents( self, fileIDs, depths, connection = False ): connection = self._getConnection( connection ) req = "SELECT AncestorID, FileID, AncestorDepth FROM FC_FileAncestors WHERE AncestorID IN (%s)" \ % intListToString( fileIDs ) if depths: req = "%s AND AncestorDepth IN (%s);" % ( req, intListToString( depths ) ) res = self.db._query( req, connection ) if not res['OK']: return res fileIDAncestors = {} for ancestorID, fileID, depth in res['Value']: if not fileIDAncestors.has_key( ancestorID ): fileIDAncestors[ancestorID] = {} fileIDAncestors[ancestorID][fileID] = depth return S_OK( fileIDAncestors )
def _getFileAncestors(self, fileIDs, depths=[], connection=False): connection = self._getConnection(connection) req = "SELECT FileID, AncestorID, AncestorDepth FROM FC_FileAncestors WHERE FileID IN (%s)" \ % intListToString(fileIDs) if depths: req = "%s AND AncestorDepth IN (%s);" % (req, intListToString(depths)) res = self.db._query(req, connection) if not res['OK']: return res fileIDAncestors = {} for fileID, ancestorID, depth in res['Value']: if fileID not in fileIDAncestors: fileIDAncestors[fileID] = {} fileIDAncestors[fileID][ancestorID] = depth return S_OK(fileIDAncestors)
def _getFileMetadataByID( self, fileIDs, connection=False ): """ Get standard file metadata for a list of files specified by FileID :param fileIDS : list of file Ids :returns S_OK(files), where files is a dictionary indexed on fileID and the values dictionaries containing the following info: ["FileID", "Size", "UID", "GID", "s.Status", "GUID", "CreationDate"] """ # Format the filenames and status to be used in a IN clause in the sotred procedure formatedFileIds = intListToString( fileIDs ) result = self.db.executeStoredProcedureWithCursor( 'ps_get_all_info_for_file_ids', ( formatedFileIds, ) ) if not result['OK']: return result rows = result['Value'] fieldNames = ["FileID", "Size", "UID", "GID", "s.Status", "GUID", "CreationDate"] resultDict = {} for row in rows: rowDict = dict( zip( fieldNames, row ) ) rowDict["Size"] = int( rowDict["Size"] ) rowDict["UID"] = int( rowDict["UID"] ) rowDict["GID"] = int( rowDict["GID"] ) resultDict[rowDict["FileID"]] = rowDict return S_OK( resultDict )
def _getFileLFNs_old(self, fileIDs): """ Get the file LFNs for a given list of file IDs """ start = time.time() stringIDs = intListToString(fileIDs) req = "SELECT DirID, FileID, FileName from FC_Files WHERE FileID IN ( %s )" % stringIDs result = self.db._query(req) if not result["OK"]: return result dirPathDict = {} fileNameDict = {} for row in result["Value"]: if not row[0] in dirPathDict: dirPathDict[row[0]] = self.db.dtree.getDirectoryPath(row[0])["Value"] fileNameDict[row[1]] = "%s/%s" % (dirPathDict[row[0]], row[2]) failed = {} successful = fileNameDict for id in fileIDs: if not id in fileNameDict: failed[id] = "File ID not found" return S_OK({"Successful": successful, "Failed": failed})
def updateReplicaInformation( self, replicaTuples ): """ This method set the replica size information and pfn for the requested storage element. """ for replicaID, pfn, size in replicaTuples: reqSelect = "SELECT * FROM CacheReplicas WHERE ReplicaID = %s and Status != 'Cancelled';" % ( replicaID ) resSelect = self._query( reqSelect ) if not resSelect['OK']: gLogger.info( "%s.%s_DB: problem retrieving record: %s. %s" % ( self._caller(), 'updateReplicaInformation', reqSelect, resSelect['Message'] ) ) req = "UPDATE CacheReplicas SET PFN = '%s', Size = %s, Status = 'Waiting' WHERE ReplicaID = %s and Status != 'Cancelled';" % ( pfn, size, replicaID ) res = self._update( req ) if not res['OK']: gLogger.error( 'StagerDB.updateReplicaInformation: Failed to insert replica information.', res['Message'] ) replicaIDs = [] for record in resSelect['Value']: replicaIDs.append( record[0] ) gLogger.info( "%s.%s_DB: to_update CacheReplicas = %s" % ( self._caller(), 'updateReplicaInformation', record ) ) reqSelect1 = "SELECT * FROM CacheReplicas WHERE ReplicaID IN (%s);" % intListToString( replicaIDs ) resSelect1 = self._query( reqSelect1 ) if not resSelect1['OK']: gLogger.info( "%s.%s_DB: problem retrieving record: %s. %s" % ( self._caller(), 'updateReplicaInformation', reqSelect1, resSelect1['Message'] ) ) for record in resSelect1['Value']: gLogger.info( "%s.%s_DB: updated CacheReplicas = %s" % ( self._caller(), 'updateReplicaInformation', record ) ) gLogger.debug( 'StagerDB.updateReplicaInformation: Successfully updated CacheReplicas record With Status=Waiting, for ReplicaID= %s' % ( replicaID ) ) return S_OK()
def updateReplicaFailure( self, terminalReplicaIDs ): """ This method sets the status to Failure with the failure reason for the supplied Replicas. """ res = self.updateReplicaStatus( terminalReplicaIDs.keys(), 'Failed' ) if not res['OK']: return res updated = res['Value'] if not updated: return S_OK( updated ) for replicaID in updated: reqSelect = "Select * FROM CacheReplicas WHERE ReplicaID = %d" % ( replicaID ) resSelect = self._query( reqSelect ) if not resSelect['OK']: gLogger.info( "%s.%s_DB: problem retrieving record: %s. %s" % ( self._caller(), 'updateReplicaFailure', reqSelect, resSelect['Message'] ) ) req = "UPDATE CacheReplicas SET Reason = '%s' WHERE ReplicaID = %d" % ( terminalReplicaIDs[replicaID], replicaID ) res = self._update( req ) if not res['OK']: gLogger.error( 'StorageManagementDB.updateReplicaFailure: Failed to update replica fail reason.', res['Message'] ) return res replicaIDs = [] for record in resSelect['Value']: replicaIDs.append( record[0] ) gLogger.info( "%s.%s_DB: to_update CacheReplicas = %s" % ( self._caller(), 'updateReplicaFailure', record ) ) reqSelect1 = "SELECT * FROM CacheReplicas WHERE ReplicaID IN (%s);" % intListToString( replicaIDs ) resSelect1 = self._query( reqSelect1 ) if not resSelect1['OK']: gLogger.info( "%s.%s_DB: problem retrieving records: %s. %s" % ( self._caller(), 'updateReplicaFailure', reqSelect1, resSelect1['Message'] ) ) for record in resSelect1['Value']: gLogger.info( "%s.%s_DB: updated CacheReplicas = %s" % ( self._caller(), 'updateReplicaFailure', record ) ) return S_OK( updated )
def getCacheReplicas( self, condDict = {}, older = None, newer = None, timeStamp = 'LastUpdate', orderAttribute = None, limit = None, connection = False ): """ Get cache replicas for the supplied selection with support for the web standard structure """ connection = self.__getConnection( connection ) req = "SELECT %s FROM CacheReplicas" % ( intListToString( self.REPLICAPARAMS ) ) originalFileIDs = {} if condDict or older or newer: if condDict.has_key( 'TaskID' ): taskIDs = condDict.pop( 'TaskID' ) if type( taskIDs ) not in ( types.ListType, types.TupleType ): taskIDs = [taskIDs] res = self._getTaskReplicaIDs( taskIDs, connection = connection ) if not res['OK']: return res condDict['ReplicaID'] = res['Value'] req = "%s %s" % ( req, self.buildCondition( condDict, older, newer, timeStamp, orderAttribute, limit ) ) res = self._query( req, connection ) if not res['OK']: return res cacheReplicas = res['Value'] resultDict = {} for row in cacheReplicas: resultDict[row[0]] = dict( zip( self.REPLICAPARAMS[1:], row[1:] ) ) result = S_OK( resultDict ) result['Records'] = cacheReplicas result['ParameterNames'] = self.REPLICAPARAMS return result
def _getReplicaTasks( self, replicaIDs, connection = False ): connection = self.__getConnection( connection ) #Daniela: should select only tasks with complete replicaID sets #only if ALL Replicas belonging to a Task have a certain state, the task should be selected for state update req = "SELECT DISTINCT(TaskID) FROM TaskReplicas WHERE ReplicaID IN (%s);" % intListToString( replicaIDs ) res = self._query( req, connection ) if not res['OK']: return res taskIDs = [row[0] for row in res['Value']] # fix finalTaskIDs = [] for taskID in taskIDs: subreq = "SELECT ReplicaID FROM CacheReplicas WHERE Status = (SELECT Status FROM Tasks WHERE TaskID = %s);" % taskID subres = self._query( subreq, connection ) if not subres['OK']: return subres replicaIDsForTask = [row[0] for row in subres['Value']] setOriginalReplicaIDs = Set( replicaIDs ) setReplicaIDsForTask = Set( replicaIDsForTask ) if setReplicaIDsForTask <= setOriginalReplicaIDs: finalTaskIDs.append( taskID ) #end_fix return S_OK( finalTaskIDs )
def _getFileLFNs(self, fileIDs): """ Get the file LFNs for a given list of file IDs """ start = time.time() stringIDs = intListToString(fileIDs) treeTable = self.db.dtree.getTreeTable() req = ( "SELECT F.FileID, CONCAT(D.DirName,'/',F.FileName) from FC_Files as F, %s as D WHERE F.FileID IN ( %s ) AND F.DirID=D.DirID" % (treeTable, stringIDs) ) result = self.db._query(req) if not result["OK"]: return result fileNameDict = {} for row in result["Value"]: fileNameDict[row[0]] = row[1] failed = {} successful = fileNameDict if len(fileNameDict) != len(fileIDs): for id in fileIDs: if not id in fileNameDict: failed[id] = "File ID not found" return S_OK({"Successful": successful, "Failed": failed})
def __deleteFileReplicas( self, fileIDs, connection = False ): """ Delete all the replicas from the file ids :param fileIDs list of file ids :returns S_OK() or S_ERROR(msg) """ connection = self._getConnection(connection) if not fileIDs: return S_OK() formatedFileIds = intListToString( fileIDs ) result = self.db.executeStoredProcedureWithCursor( 'ps_delete_replicas_from_file_ids', ( formatedFileIds, ) ) if not result['OK']: return result errno, msg = result['Value'][0] if errno: return S_ERROR( msg ) return S_OK()
def _getFileLFNs(self, fileIDs): """ Get the file LFNs for a given list of file IDs """ stringIDs = intListToString(fileIDs) treeTable = self.db.dtree.getTreeTable() req = "SELECT F.FileID, CONCAT(D.DirName,'/',F.FileName) from FC_Files as F,\ %s as D WHERE F.FileID IN ( %s ) AND F.DirID=D.DirID" % ( treeTable, stringIDs) result = self.db._query(req) if not result['OK']: return result fileNameDict = {} for row in result['Value']: fileNameDict[row[0]] = row[1] failed = {} successful = fileNameDict if len(fileNameDict) != len(fileIDs): for id_ in fileIDs: if id_ not in fileNameDict: failed[id_] = "File ID not found" return S_OK({'Successful': successful, 'Failed': failed})
def getStageRequests( self, condDict = {}, older = None, newer = None, timeStamp = 'StageRequestSubmitTime', orderAttribute = None, limit = None, connection = False ): """ Get stage requests for the supplied selection with support for web standard structure """ connection = self.__getConnection( connection ) req = "SELECT %s FROM StageRequests" % ( intListToString( self.STAGEPARAMS ) ) if condDict or older or newer: if condDict.has_key( 'TaskID' ): taskIDs = condDict.pop( 'TaskID' ) if type( taskIDs ) not in ( types.ListType, types.TupleType ): taskIDs = [taskIDs] res = self._getTaskReplicaIDs( taskIDs, connection = connection ) if not res['OK']: return res if res['Value']: condDict['ReplicaID'] = res['Value'] else: condDict['ReplicaID'] = [-1] req = "%s %s" % ( req, self.buildCondition( condDict, older, newer, timeStamp, orderAttribute, limit ) ) res = self._query( req, connection ) if not res['OK']: return res stageRequests = res['Value'] resultDict = {} for row in stageRequests: resultDict[row[0]] = dict( zip( self.STAGEPARAMS[1:], row[1:] ) ) result = S_OK( resultDict ) result['Records'] = stageRequests result['ParameterNames'] = self.STAGEPARAMS return result
def removeUnlinkedReplicas( self, connection = False ): """ This will remove from the CacheReplicas tables where there are no associated links. """ connection = self.__getConnection( connection ) req = "SELECT ReplicaID from CacheReplicas WHERE Links = 0;" res = self._query( req, connection ) if not res['OK']: gLogger.error( "StorageManagementDB.removeUnlinkedReplicas. Problem selecting entries from CacheReplicas where Links = 0." ) return res replicaIDs = [] for tuple in res['Value']: replicaIDs.append( tuple[0] ) if not replicaIDs: return S_OK() reqSelect = "SELECT * FROM StageRequests WHERE ReplicaID IN (%s);" % intListToString( replicaIDs ) resSelect = self._query( reqSelect ) if not resSelect['OK']: gLogger.info( "%s.%s_DB: problem retrieving record: %s. %s" % ( self._caller(), 'removeUnlinkedReplicas', reqSelect, resSelect['Message'] ) ) for record in resSelect['Value']: gLogger.info( "%s.%s_DB: to_delete StageRequests = %s" % ( self._caller(), 'removeUnlinkedReplicas', record ) ) req = "DELETE FROM StageRequests WHERE ReplicaID IN (%s);" % intListToString( replicaIDs ) res = self._update( req, connection ) if not res['OK']: gLogger.error( "StorageManagementDB.removeUnlinkedReplicas. Problem deleting from StageRequests." ) return res gLogger.info( "%s.%s_DB: deleted StageRequests" % ( self._caller(), 'removeUnlinkedReplicas' ) ) gLogger.debug( "StorageManagementDB.removeUnlinkedReplicas: Successfully removed %s StageRequests entries for ReplicaIDs: %s." % ( res['Value'], replicaIDs ) ) reqSelect = "SELECT * FROM CacheReplicas WHERE ReplicaID IN (%s);" % intListToString( replicaIDs ) resSelect = self._query( reqSelect ) if not resSelect['OK']: gLogger.info( "%s.%s_DB: problem retrieving record: %s. %s" % ( self._caller(), 'removeUnlinkedReplicas', reqSelect, resSelect['Message'] ) ) for record in resSelect['Value']: gLogger.info( "%s.%s_DB: to_delete CacheReplicas = %s" % ( self._caller(), 'removeUnlinkedReplicas', record ) ) req = "DELETE FROM CacheReplicas WHERE ReplicaID IN (%s);" % intListToString( replicaIDs ) res = self._update( req, connection ) if res['OK']: gLogger.info( "%s.%s_DB: deleted CacheReplicas" % ( self._caller(), 'removeUnlinkedReplicas' ) ) gLogger.debug( "StorageManagementDB.removeUnlinkedReplicas: Successfully removed %s CacheReplicas entries for ReplicaIDs: %s." % ( res['Value'], replicaIDs ) ) else: gLogger.error( "StorageManagementDB.removeUnlinkedReplicas. Problem removing entries from CacheReplicas." ) return res
def __getFileIDReplicas(self,fileIDs,allStatus=False,connection=False): connection = self._getConnection(connection) if not fileIDs: return S_ERROR("No such file or directory") req = "SELECT FileID,SEID,RepID,Status FROM FC_Replicas WHERE FileID IN (%s)" % (intListToString(fileIDs)) if not allStatus: statusIDs = [] for status in self.db.visibleReplicaStatus: result = self._getStatusInt( status, connection=connection ) if result['OK']: statusIDs.append( result['Value'] ) req += " AND Status in (%s)" % (intListToString(statusIDs)) res = self.db._query(req,connection) if not res['OK']: return res fileIDDict = {} for fileID,seID,repID,statusID in res['Value']: fileIDDict[repID] = ( fileID, seID, statusID ) return S_OK(fileIDDict)
def cleanUpFTSFiles( self, requestID, fileIDs ): """ delete FTSFiles for given :requestID: and list of :fileIDs: :param int requestID: ReqDB.Request.RequestID :param list fileIDs: [ ReqDB.File.FileID, ... ] """ query = "DELETE FROM `FTSFile` WHERE `RequestID`= %s and `FileID` IN (%s)" % ( requestID, intListToString( fileIDs ) ) deleteFiles = self._transaction( [query] ) return deleteFiles
def _setFileParameter(self, fileID, paramName, paramValue, connection=False): connection = self._getConnection(connection) if type(fileID) not in [TupleType, ListType]: fileID = [fileID] req = "UPDATE FC_Files SET %s='%s', ModificationDate=UTC_TIMESTAMP() WHERE FileID IN (%s)" % ( paramName, paramValue, intListToString(fileID), ) return self.db._update(req, connection)
def _getReplicaIDTasks( self, replicaIDs, connection = False ): if not replicaIDs: return S_OK( [] ) req = "SELECT DISTINCT(TaskID) FROM TaskReplicas WHERE ReplicaID IN (%s);" % intListToString( replicaIDs ) res = self._query( req, connection ) if not res['OK']: return res taskIDs = [row[0] for row in res['Value']] return S_OK( taskIDs )
def _insertFileAncestors( self, fileID, ancestorDict, connection = False ): connection = self._getConnection( connection ) ancestorTuples = [] for ancestorID, depth in ancestorDict.items(): ancestorTuples.append( "(%d,%d,%d)" % ( fileID, ancestorID, depth ) ) if not ancestorTuples: return S_OK() req = "INSERT INTO FC_FileAncestors (FileID, AncestorID, AncestorDepth) VALUES %s" \ % intListToString( ancestorTuples ) return self.db._update( req, connection )
def _getReplicaIDTasks( self, replicaIDs, connection = False ): req = "SELECT TaskID FROM TaskReplicas WHERE ReplicaID IN (%s);" % intListToString( replicaIDs ) res = self._query( req, connection ) if not res['OK']: return res taskIDs = [] for tuple in res['Value']: taskID = tuple[0] if not taskID in taskIDs: taskIDs.append( taskID ) return S_OK( taskIDs )
def getRequestForJobs( self, jobIDs ): """ Get the request names associated to the jobsIDs """ req = "SELECT JobID,RequestName from Requests where JobID IN (%s);" % intListToString( jobIDs ) res = self._query( req ) if not res: return res jobIDs = {} for jobID, requestName in res['Value']: jobIDs[jobID] = requestName return S_OK( jobIDs )
def deleteFTSFiles( self, operationID, opFileIDList = None ): """ delete FTSFiles for reschedule :param int operationID: ReqDB.Operation.OperationID :param list opFileIDList: [ ReqDB.File.FileID, ... ] """ query = [ "DELETE FROM `FTSFile` WHERE OperationID = %s" % operationID ] if opFileIDList: query.append( " AND `FileID` IN (%s)" % intListToString( opFileIDList ) ) query.append( ";" ) return self._update( "".join( query ) )
def __findDirs(self,paths,metadata=['DirName']): dirs = {} req = "SELECT DirID,%s FROM DirectoryInfo WHERE DirName IN (%s)" % (intListToString(metadata),stringListToString(paths)) res = self.db._query(req) if not res['OK']: return res if not res['Value']: return S_OK(dirs) for tuple in res['Value']: dirID = tuple[0] dirs[dirID] = dict(zip(metadata,tuple[1:])) return S_OK(dirs)
def __deleteReplicas(self, replicaTuples, connection=False): connection = self._getConnection(connection) deleteTuples = [] for fileID, seID in replicaTuples: if type(seID) in StringTypes: res = self.db.seManager.findSE(seID) if not res["OK"]: return res seID = res["Value"] deleteTuples.append("(%d,%d)" % (fileID, seID)) req = "DELETE FROM FC_Replicas WHERE (FileID,SEID) IN (%s)" % intListToString(deleteTuples) return self.db._update(req, connection)
def _getDirectoryFiles(self, dirID, fileNames, metadata, allStatus=False, connection=False): connection = self._getConnection(connection) # metadata can be any of ['FileID','Size','UID','GID','Checksum','ChecksumType','Type','CreationDate','ModificationDate','Mode','Status'] req = "SELECT FileName,%s FROM FC_Files WHERE DirID=%d" % (intListToString(metadata), dirID) if not allStatus: statusIDs = [] res = self._getStatusInt("AprioriGood", connection=connection) if res["OK"]: statusIDs.append(res["Value"]) if statusIDs: req = "%s AND Status IN (%s)" % (req, intListToString(statusIDs)) if fileNames: req = "%s AND FileName IN (%s)" % (req, stringListToString(fileNames)) res = self.db._query(req, connection) if not res["OK"]: return res files = {} for tuple in res["Value"]: fileName = tuple[0] files[fileName] = dict(zip(metadata, tuple[1:])) return S_OK(files)
def getDirectoryPaths( self, dirIDList ): """ Get directory names by directory ID list :param dirIDList: list of dirIds :returns: S_OK( { dirID : dirName} ) """ dirs = dirIDList if not isinstance( dirIDList, list ): dirs = [dirIDList] dirDict = {} # Format the list dIds = intListToString( dirs ) result = self.db.executeStoredProcedureWithCursor( 'ps_get_dirNames_from_ids', ( dIds, ) ) if not result['OK']: return result for dirId, dirName in result['Value']: dirDict[dirId] = dirName return S_OK( dirDict )
def _getFileLFNs(self, fileIDs): """ Get the file LFNs for a given list of file IDs """ stringIDs = intListToString(fileIDs) req = "SELECT DirID, FileID, FileName from FC_Files WHERE FileID IN ( %s )" % stringIDs result = self.db._query(req) if not result['OK']: return result dirPathDict = {} fileNameDict = {} for row in result['Value']: if not row[0] in dirPathDict: dirPathDict[row[0]] = self.db.dtree.getDirectoryPath( row[0])['Value'] fileNameDict[row[1]] = '%s/%s' % (dirPathDict[row[0]], row[2]) failed = {} successful = fileNameDict for id in fileIDs: if not id in fileNameDict: failed[id] = "File ID not found" return S_OK({'Successful': successful, 'Failed': failed})
def __findFilesByMetadata(self, metaDict, dirList, credDict): """ Find a list of file IDs meeting the metaDict requirements and belonging to directories in dirList """ # 1.- classify Metadata keys storageElement = None standardMetaDict = {} userMetaDict = {} for meta, value in metaDict.items(): if meta == "SE": storageElement = value elif meta in FILE_STANDARD_METAKEYS: standardMetaDict[meta] = value else: userMetaDict[meta] = value tablesAndConditions = [] # 2.- standard search result = self.__buildStandardMetaQuery(standardMetaDict) if not result['OK']: return result tablesAndConditions.extend(result['Value']) # 3.- user search result = self.__buildUserMetaQuery(userMetaDict) if not result['OK']: return result tablesAndConditions.extend(result['Value']) # 4.- SE constrain result = self.__buildSEQuery(storageElement) if not result['OK']: return result tablesAndConditions.extend(result['Value']) query = 'SELECT F.FileID FROM ' conditions = [] tables = ['FC_Files as F'] if dirList: dirString = intListToString(dirList) conditions.append("F.DirID in (%s)" % dirString) counter = 0 for table, condition in tablesAndConditions: counter += 1 tables.append('%s as M%d' % (table, counter)) table = 'M%d' % counter condition = condition % table + ' AND F.FileID = %s.FileID' % table conditions.append('( %s )' % condition) query += ', '.join(tables) if conditions: query += ' WHERE %s' % ' AND '.join(conditions) result = self.db._query(query) if not result['OK']: return result if not result['Value']: return S_OK([]) # fileList = [ row[0] for row in result['Value' ] ] fileList = [] for row in result['Value']: fileID = row[0] fileList.append(fileID) return S_OK(fileList)
def __findFilesByMetadata(self, metaDict, dirList, credDict): """ Find a list of file IDs meeting the metaDict requirements and belonging to directories in dirList """ # 1.- classify Metadata keys storageElements = None standardMetaDict = {} userMetaDict = {} leftJoinTables = [] for meta, value in metaDict.items(): if meta == "SE": if isinstance(value, DictType): storageElements = value.get('in', []) else: storageElements = [value] elif meta in FILE_STANDARD_METAKEYS: standardMetaDict[meta] = value else: userMetaDict[meta] = value tablesAndConditions = [] leftJoinTables = [] # 2.- standard search if standardMetaDict: result = self.__buildStandardMetaQuery(standardMetaDict) if not result['OK']: return result tablesAndConditions.extend(result['Value']) # 3.- user search if userMetaDict: result = self.__buildUserMetaQuery(userMetaDict) if not result['OK']: return result tablesAndConditions.extend(result['Value']) leftJoinTables = result['LeftJoinTables'] # 4.- SE constraint if storageElements: result = self.__buildSEQuery(storageElements) if not result['OK']: return result tablesAndConditions.extend(result['Value']) query = 'SELECT F.FileID FROM FC_Files F ' conditions = [] tables = [] if dirList: dirString = intListToString(dirList) conditions.append("F.DirID in (%s)" % dirString) counter = 0 for table, condition in tablesAndConditions: if table == 'FC_FileInfo': query += 'INNER JOIN FC_FileInfo FI USING( FileID ) ' condition = condition.replace('%%', '%') elif table == 'FC_Files': condition = condition.replace('%%', '%') else: counter += 1 if table in leftJoinTables: tables.append('LEFT JOIN %s M%d USING( FileID )' % (table, counter)) else: tables.append('INNER JOIN %s M%d USING( FileID )' % (table, counter)) table = 'M%d' % counter condition = condition % table conditions.append(condition) query += ' '.join(tables) if conditions: query += ' WHERE %s' % ' AND '.join(conditions) result = self.db._query(query) if not result['OK']: return result if not result['Value']: return S_OK([]) # fileList = [ row[0] for row in result['Value' ] ] fileList = [] for row in result['Value']: fileID = row[0] fileList.append(fileID) return S_OK(fileList)
def __deleteFiles(self, fileIDs, connection=False): connection = self._getConnection(connection) if not fileIDs: return S_OK() req = "DELETE FROM FC_Files WHERE FileID in (%s)" % (intListToString(fileIDs)) return self.db._update(req, connection)
def __getFileIDReplicas(self,fileIDs,connection=False): connection = self._getConnection(connection) if not fileIDs: return S_ERROR("No such file or directory") req = "SELECT FileID,SEID,RepID,Status FROM FC_Replicas WHERE FileID IN (%s);" % (intListToString(fileIDs)) res = self.db._query(req,connection) if not res['OK']: return res fileIDDict = {} for fileID,seID,repID,status in res['Value']: fileIDDict[repID] = {'FileID':fileID,'SEID':seID,'Status':status} return S_OK(fileIDDict)
def _setFileParameter( self, fileID, paramName, paramValue, connection = False ): connection = self._getConnection(connection) if type(fileID) not in [TupleType,ListType]: fileID = [fileID] if paramName in ['UID','GID','Status','Size']: # Treat primary file attributes specially req = "UPDATE FC_Files SET %s='%s' WHERE FileID IN (%s)" % ( paramName, paramValue, intListToString( fileID ) ) result = self.db._update(req,connection) if not result['OK']: return result req = "UPDATE FC_FileInfo SET ModificationDate=UTC_TIMESTAMP() WHERE FileID IN (%s)" % intListToString( fileID ) else: req = "UPDATE FC_FileInfo SET %s='%s', ModificationDate=UTC_TIMESTAMP() WHERE FileID IN (%s)" % ( paramName, paramValue, intListToString( fileID ) ) return self.db._update(req,connection)
def __findFilesByMetadata(self, metaDict, dirList, credDict): """Find a list of file IDs meeting the metaDict requirements and belonging to directories in dirList :param dict metaDict: dictionary with the file metadata :param list dirList: list of directories to look into :return: S_OK/S_ERROR, Value - list of IDs of found files """ # 1.- classify Metadata keys storageElements = None standardMetaDict = {} userMetaDict = {} leftJoinTables = [] for meta, value in metaDict.items(): if meta == "SE": if isinstance(value, dict): storageElements = value.get("in", []) else: storageElements = [value] elif meta in FILE_STANDARD_METAKEYS: standardMetaDict[meta] = value else: userMetaDict[meta] = value tablesAndConditions = [] leftJoinTables = [] # 2.- standard search if standardMetaDict: result = self.__buildStandardMetaQuery(standardMetaDict) if not result["OK"]: return result tablesAndConditions.extend(result["Value"]) # 3.- user search if userMetaDict: result = self.__buildUserMetaQuery(userMetaDict) if not result["OK"]: return result tablesAndConditions.extend(result["Value"]) leftJoinTables = result["LeftJoinTables"] # 4.- SE constraint if storageElements: result = self.__buildSEQuery(storageElements) if not result["OK"]: return result tablesAndConditions.extend(result["Value"]) query = "SELECT F.FileID FROM FC_Files F " conditions = [] tables = [] if dirList: dirString = intListToString(dirList) conditions.append("F.DirID in (%s)" % dirString) counter = 0 for table, condition in tablesAndConditions: if table == "FC_FileInfo": query += "INNER JOIN FC_FileInfo FI USING( FileID ) " condition = condition.replace("%%", "%") elif table == "FC_Files": condition = condition.replace("%%", "%") else: counter += 1 if table in leftJoinTables: tables.append("LEFT JOIN %s M%d USING( FileID )" % (table, counter)) else: tables.append("INNER JOIN %s M%d USING( FileID )" % (table, counter)) table = "M%d" % counter condition = condition % table conditions.append(condition) query += " ".join(tables) if conditions: query += " WHERE %s" % " AND ".join(conditions) result = self.db._query(query) if not result["OK"]: return result if not result["Value"]: return S_OK([]) # fileList = [ row[0] for row in result['Value' ] ] fileList = [] for row in result["Value"]: fileID = row[0] fileList.append(fileID) return S_OK(fileList)
def _getRepIDsForReplica(self, replicaTuples, connection=False): connection = self._getConnection(connection) queryTuples = [] for fileID, seID in replicaTuples: queryTuples.append("(%d,%d)" % (fileID, seID)) req = "SELECT RepID,FileID,SEID FROM FC_Replicas WHERE (FileID,SEID) IN (%s)" % intListToString( queryTuples) res = self.db._query(req, connection) if not res['OK']: return res replicaDict = {} for repID, fileID, seID in res['Value']: replicaDict.setdefault(fileID, {}) replicaDict[fileID][seID] = repID return S_OK(replicaDict)
def _getDirectoryFiles(self, dirID, fileNames, metadata_input, allStatus=False, connection=False): """ Get the metadata for files in the same directory """ metadata = list(metadata_input) connection = self._getConnection(connection) # metadata can be any of ['FileID','Size','UID','GID','Status','Checksum','CheckSumType', # 'Type','CreationDate','ModificationDate','Mode'] req = "SELECT FileName,DirID,FileID,Size,UID,GID,Status FROM FC_Files WHERE DirID=%d" % ( dirID) if not allStatus: statusIDs = [] for status in self.db.visibleFileStatus: res = self._getStatusInt(status, connection=connection) if res['OK']: statusIDs.append(res['Value']) if statusIDs: req = "%s AND Status IN (%s)" % (req, intListToString(statusIDs)) if fileNames: req = "%s AND FileName IN (%s)" % (req, stringListToString(fileNames)) res = self.db._query(req, connection) if not res['OK']: return res fileNameIDs = res['Value'] if not fileNameIDs: return S_OK({}) filesDict = {} # If we only requested the FileIDs then there is no need to do anything else if metadata == ['FileID']: for fileName, dirID, fileID, size, uid, gid, status in fileNameIDs: filesDict[fileName] = {'FileID': fileID} return S_OK(filesDict) # Otherwise get the additionally requested metadata from the FC_FileInfo table files = {} userDict = {} groupDict = {} for fileName, dirID, fileID, size, uid, gid, status in fileNameIDs: filesDict[fileID] = fileName files[fileName] = {} if 'Size' in metadata: files[fileName]['Size'] = size if 'DirID' in metadata: files[fileName]['DirID'] = dirID if 'UID' in metadata: files[fileName]['UID'] = uid if uid in userDict: owner = userDict[uid] else: owner = 'unknown' result = self.db.ugManager.getUserName(uid) if result['OK']: owner = result['Value'] userDict[uid] = owner files[fileName]['Owner'] = owner if 'GID' in metadata: files[fileName]['GID'] = gid if gid in groupDict: group = groupDict[gid] else: group = 'unknown' result = self.db.ugManager.getGroupName(gid) if result['OK']: group = result['Value'] groupDict[gid] = group files[fileName]['OwnerGroup'] = group if 'Status' in metadata: files[fileName]['Status'] = status for element in ['FileID', 'Size', 'DirID', 'UID', 'GID', 'Status']: if element in metadata: metadata.remove(element) metadata.append('FileID') metadata.reverse() req = "SELECT %s FROM FC_FileInfo WHERE FileID IN (%s)" % ( intListToString(metadata), intListToString(filesDict.keys())) res = self.db._query(req, connection) if not res['OK']: return res for tuple_ in res['Value']: fileID = tuple_[0] rowDict = dict(zip(metadata, tuple_)) files[filesDict[fileID]].update(rowDict) return S_OK(files)
def setMigratingReplicaStatus(self,fileIDs,status): """ Update the status of the replica in the database """ gLogger.info("setMigratingReplicaStatus: Attempting to update status of %d replicas to '%s'." % (len(fileIDs),status)) req = "UPDATE MigratingReplicas SET Status='%s', LastUpdate=UTC_TIMESTAMP() WHERE ReplicaID IN (%s);" % (status,intListToString(fileIDs)) res = self._update(req) if not res['OK']: gLogger.error("setMigratingReplicaStatus: Failed update replica statuses.",res['Message']) else: gLogger.info("setMigratingReplicaStatus: Successfully updated replica statuses.") return res
def _setFileParameter(self,fileID,paramName,paramValue,connection=False): connection = self._getConnection(connection) if type(fileID) not in [TupleType,ListType]: fileID = [fileID] req = "UPDATE FC_Files SET %s='%s', ModificationDate=UTC_TIMESTAMP() WHERE FileID IN (%s)" % (paramName,paramValue,intListToString(fileID)) return self.db._update(req,connection)
def getRequestFileStatus( self, requestID, files ): req = "SELECT DISTINCT SubRequestID FROM SubRequests WHERE RequestID = %d;" % requestID res = self._query( req ) if not res['OK']: return res subRequests = [] for subRequestID in res['Value'][0]: subRequests.append( subRequestID ) req = "SELECT LFN,Status from Files WHERE SubRequestID IN (%s) AND LFN in (%s);" % ( intListToString( subRequests ), stringListToString( files ) ) res = self._query( req ) if not res['OK']: return res files = {} for lfn, status in res['Value']: files[lfn] = status return S_OK( files )