def _update( self, cmd, conn=False ):
start = time.time()
ret = DB._update( self, cmd, conn )
if DEBUG:
print >> debugFile, time.time() - start, cmd.replace('\n','')
debugFile.flush()
return ret
def _update(self, cmd, conn=False):
start = time.time()
ret = DB._update(self, cmd, conn)
if DEBUG:
print >> debugFile, time.time() - start, cmd.replace('\n', '')
debugFile.flush()
return ret
def _update( self, cmd, conn=False ):
""" Update MPIJob Database
"""
print "DB4"
start = Time.time()
ret = DB._update( self, cmd, conn )
if DEBUG:
print >> debugFile, Time.time() - start, cmd.replace('\n','')
debugFile.flush()
print ret
return ret
def _update(self, cmd, conn=False):
""" Update MPIJob Database
"""
print "DB4"
start = Time.time()
ret = DB._update(self, cmd, conn)
if DEBUG:
print >> debugFile, Time.time() - start, cmd.replace('\n', '')
debugFile.flush()
print ret
return ret
class ResourceManagementDB:
"""
The ResourceManagementDB class is a front-end to the Resource Management Database.
The simplest way to instantiate an object of type :class:`ResourceManagementDB`
is simply by calling
>>> rpDB = ResourceManagementDB()
This way, it will use the standard :mod:`DIRAC.Core.Base.DB`.
But there's the possibility to use other DB classes.
For example, we could pass custom DB instantiations to it,
provided the interface is the same exposed by :mod:`DIRAC.Core.Base.DB`.
>>> AnotherDB = AnotherDBClass()
>>> rmDB = ResourceManagementDB(DBin = AnotherDB)
Alternatively, for testing purposes, you could do:
>>> from DIRAC.ResourceStatusSystem.Utilities.mock import Mock
>>> mockDB = Mock()
>>> rmDB = ResourceManagementDB(DBin = mockDB)
Or, if you want to work with a local DB, providing it's mySQL:
>>> rmDB = ResourceManagementDB(DBin = ['UserName', 'Password'])
"""
def __init__(self, *args, **kwargs):
if len(args) == 1:
if isinstance(args[0], str):
# systemInstance=args[0]
maxQueueSize=10
if isinstance(args[0], int):
maxQueueSize=args[0]
# systemInstance='Default'
elif len(args) == 2:
# systemInstance=args[0]
maxQueueSize=args[1]
elif len(args) == 0:
# systemInstance='Default'
maxQueueSize=10
if 'DBin' in kwargs.keys():
DBin = kwargs['DBin']
if isinstance(DBin, list):
from DIRAC.Core.Utilities.MySQL import MySQL
self.db = MySQL('localhost', DBin[0], DBin[1], 'ResourceManagementDB')
else:
self.db = DBin
else:
from DIRAC.Core.Base.DB import DB
self.db = DB('ResourceManagementDB','ResourceStatus/ResourceManagementDB',maxQueueSize)
# self.db = DB('ResourceStatusDB','ResourceStatus/ResourceStatusDB',maxQueueSize)
#############################################################################
#############################################################################
# Policy functions
#############################################################################
#############################################################################
def addOrModifyPolicyRes(self, granularity, name, policyName,
status, reason, dateEffective = None):
"""
Add or modify a Policy Result to the PolicyRes table.
:params:
:attr:`granularity`: string - a ValidRes
see :mod:`DIRAC.ResourceStatusSystem.Utilities.Utils`
:attr:`name`: string - name of the ValidRes
:attr:`policyName`: string - the policy name
:attr:`status`: string - a ValidStatus:
see :mod:`DIRAC.ResourceStatusSystem.Utilities.Utils`
:attr:`reason`: string - free
:attr:`dateEffective`: datetime.datetime -
date from which the result is effective
"""
now = datetime.datetime.utcnow().replace(microsecond = 0).isoformat(' ')
if dateEffective is None:
dateEffective = now
req = "SELECT Granularity, Name, PolicyName, Status, Reason FROM PolicyRes "
req = req + "WHERE Granularity = '%s' AND Name = '%s' AND " %(granularity, name)
req = req + "PolicyName = '%s'" %(policyName)
resQuery = self.db._query(req)
if not resQuery['OK']:
raise RSSManagementDBException, where(self, self.addOrModifyPolicyRes) + resQuery['Message']
if resQuery['Value']:
req = "UPDATE PolicyRes SET "
if resQuery['Value'][0][3] != status:
req = req + "Status = '%s', Reason = '%s', DateEffective = '%s', " %(status, reason, dateEffective)
elif resQuery['Value'][0][4] != reason:
req = req + "Reason = '%s', " %(reason)
req = req + "LastCheckTime = '%s' WHERE Granularity = '%s' " %(now, granularity)
req = req + "AND Name = '%s' AND PolicyName = '%s'" %(name, policyName)
resUpdate = self.db._update(req)
if not resUpdate['OK']:
raise RSSManagementDBException, where(self, self.addOrModifyPolicyRes) + resUpdate['Message']
else:
req = "INSERT INTO PolicyRes (Granularity, Name, PolicyName, Status, Reason, DateEffective, "
req = req + "LastCheckTime) VALUES ('%s', '%s', '%s', " %(granularity, name, policyName)
req = req + "'%s', '%s', '%s', '%s')" %(status, reason, dateEffective, now)
resUpdate = self.db._update(req)
if not resUpdate['OK']:
raise RSSManagementDBException, where(self, self.addOrModifyPolicyRes) + resUpdate['Message']
#############################################################################
def getPolicyRes(self, name, policyName, lastCheckTime = False):
"""
Get a Policy Result from the PolicyRes table.
:params:
:attr:`name`: string - name of the ValidRes
:attr:`policyName`: string - the policy name
:attr:`lastCheckTime`: optional - if TRUE, it will get also the
LastCheckTime
"""
req = "SELECT Status, Reason"
if lastCheckTime:
req = req + ", LastCheckTime"
req = req + " FROM PolicyRes WHERE"
req = req + " Name = '%s' AND PolicyName = '%s'" %(name, policyName)
resQuery = self.db._query(req)
if not resQuery['OK']:
raise RSSManagementDBException, where(self, self.getPolicyRes) + resQuery['Message']
if not resQuery['Value']:
return []
return resQuery['Value'][0]
#############################################################################
#############################################################################
# ClientsCache functions
#############################################################################
#############################################################################
def addOrModifyClientsCacheRes(self, name, commandName, value, result,
opt_ID = None, dateEffective = None):
"""
Add or modify a Client Result to the ClientCache table.
:params:
:attr:`name`: string - name of the ValidRes
:attr:`commandName`: string - the command name
:attr:`value`: string - the value
:attr:`result`: string - command result
:attr:`opt_ID`: string or integer - optional ID (e.g. used for downtimes)
:attr:`dateEffective`: datetime.datetime -
date from which the result is effective
"""
now = datetime.datetime.utcnow().replace(microsecond = 0).isoformat(' ')
if dateEffective is None:
dateEffective = now
if opt_ID is not None:
if isinstance(opt_ID, int):
opt_ID = str(opt_ID)
req = "SELECT Name, CommandName, "
if opt_ID is not None:
req = req + "Opt_ID, "
req = req + "Value, Result FROM ClientsCache WHERE "
req = req + "Name = '%s' AND CommandName = '%s' " %(name, commandName)
if opt_ID is not None:
req = req + "AND Opt_ID = '%s' " %opt_ID
req = req + "AND Value = '%s' " %value
resQuery = self.db._query(req)
if not resQuery['OK']:
raise RSSManagementDBException, where(self, self.addOrModifyClientsCacheRes) + resQuery['Message']
if resQuery['Value']:
req = "UPDATE ClientsCache SET "
if resQuery['Value'][0][3] != result:
req = req + "Result = '%s', DateEffective = '%s', " %(result, dateEffective)
req = req + "LastCheckTime = '%s' WHERE " %(now)
req = req + "Name = '%s' AND CommandName = '%s' AND Value = '%s'" %(name, commandName, value)
if opt_ID is not None:
req = req + "AND Opt_ID = '%s' " %opt_ID
resUpdate = self.db._update(req)
if not resUpdate['OK']:
raise RSSManagementDBException, where(self, self.addOrModifyClientsCacheRes) + resUpdate['Message']
else:
req = "INSERT INTO ClientsCache (Name, CommandName, "
if opt_ID is not None:
req = req + "Opt_ID, "
req = req + "Value, Result, DateEffective, "
req = req + "LastCheckTime) VALUES ('%s', '%s', " %(name, commandName)
if opt_ID is not None:
req = req + "'%s', " %opt_ID
req = req + "'%s', '%s', '%s', '%s')" %(value, result, dateEffective, now)
resUpdate = self.db._update(req)
if not resUpdate['OK']:
raise RSSManagementDBException, where(self, self.addOrModifyClientsCacheRes) + resUpdate['Message']
#############################################################################
def getClientsCacheStuff(self, paramsList = None, ccID = None, name = None, commandName = None,
opt_ID = None, value = None, result = None, dateEffective = None,
lastCheckTime = None):
"""
Generic function to get values from the ClientsCache table.
:params:
:attr:`paramsList` - string or list of strings
:attr:`ccID` - string or list of strings
:attr:`name` - string or list of strings
:attr:`commandName` - string or list of strings
:attr:`opt_ID` - string or list of strings
:attr:`value` - string or list of strings
:attr:`result` - string or list of strings
:attr:`dateEffective` - string or list of strings
:attr:`lastCheckTime` - string or list of strings
"""
if (paramsList == None or paramsList == []):
params = "ccID, Name, CommandName, Opt_ID, Value, Result, DateEffective "
else:
if type(paramsList) is not type([]):
paramsList = [paramsList]
params = ','.join([x.strip()+' ' for x in paramsList])
req = "SELECT " + params + "FROM ClientsCache "
if not (ccID == name == commandName == opt_ID == value ==
result == dateEffective == lastCheckTime == None):
req = req + "WHERE "
if ccID is not None:
if type(ccID) is not type([]):
ccID = [ccID]
req = req + "ccID IN (" + ','.join([str(x).strip() + ' ' for x in ccID]) + ")"
if name is not None:
if ccID is not None:
req = req + " AND "
if type(name) is not type([]):
name = [name]
req = req + "Name IN (" + ','.join(['"' + x.strip() + '"' + ' ' for x in name]) + ")"
if commandName is not None:
if ccID is not None or name is not None:
req = req + " AND "
if type(commandName) is not type([]):
commandName = [commandName]
req = req + "CommandName IN (" + ','.join(['"' + x.strip() + '"' + ' ' for x in commandName]) + ")"
if opt_ID is not None:
if ccID is not None or name is not None or commandName is not None:
req = req + " AND "
if type(opt_ID) is not type([]):
opt_ID = [opt_ID]
req = req + "Opt_ID IN (" + ','.join(['"' + str(x).strip() + '"' + ' ' for x in opt_ID]) + ")"
if value is not None:
if ccID is not None or name is not None or commandName is not None or opt_ID is not None:
req = req + " AND "
if type(value) is not type([]):
value = [value]
req = req + "Value IN (" + ','.join(['"' + x.strip() + '"' + ' ' for x in value]) + ")"
if result is not None:
if (ccID is not None or name is not None or commandName is not None or opt_ID is not None or value is not None):
req = req + " AND "
if type(result) is not type([]):
result = [result]
req = req + "result IN (" + ','.join(['"' + x.strip() + '"' + ' ' for x in result]) + ")"
if dateEffective is not None:
if (ccID is not None or name is not None or commandName is not None or opt_ID is not None or value is not None or result is not None):
req = req + " AND "
if type(dateEffective) is not type([]):
dateEffective = [dateEffective]
req = req + "dateEffective IN (" + ','.join(['"' + x.strip() + '"' + ' ' for x in dateEffective]) + ")"
if lastCheckTime is not None:
if (ccID is not None or name is not None or commandName is not None or opt_ID is not None or value is not None or result is not None or dateEffective is not None):
req = req + " AND "
if type(lastCheckTime) is not type([]):
lastCheckTime = [lastCheckTime]
req = req + "lastCheckTime IN (" + ','.join(['"' + x.strip() + '"' + ' ' for x in lastCheckTime]) + ")"
resQuery = self.db._query(req)
if not resQuery['OK']:
raise RSSManagementDBException, where(self, self.getClientsCacheStuff) + resQuery['Message']
if not resQuery['Value']:
return []
return resQuery['Value']
#############################################################################
#############################################################################
# AccountingCache functions
#############################################################################
#############################################################################
def addOrModifyAccountingCacheRes(self, name, plotType, plotName, result, dateEffective = None):
"""
Add or modify an Accounting Result to the AccountingCache table.
:params:
:attr:`name`: string - name of the ValidRes
:attr:`plotType`: string - the plotType name (e.g. 'Pilot')
:attr:`plotName`: string - the plot name
:attr:`result`: string - command result
:attr:`dateEffective`: datetime.datetime -
date from which the result is effective
"""
now = datetime.datetime.utcnow().replace(microsecond = 0).isoformat(' ')
if dateEffective is None:
dateEffective = now
req = "SELECT Name, PlotType, PlotName, Result FROM AccountingCache WHERE "
req = req + "Name = '%s' AND PlotType = '%s' AND PlotName = '%s' " %(name, plotType, plotName)
resQuery = self.db._query(req)
if not resQuery['OK']:
raise RSSManagementDBException, where(self, self.addOrModifyAccountingCacheRes) + resQuery['Message']
if resQuery['Value']:
req = "UPDATE AccountingCache SET "
if resQuery['Value'][0][3] != result:
req = req + "Result = \"%s\", DateEffective = '%s', " %(result, dateEffective)
req = req + "LastCheckTime = '%s' WHERE " %(now)
req = req + "Name = '%s' AND PlotType = '%s' AND PlotName = '%s'" %(name, plotType, plotName)
resUpdate = self.db._update(req)
if not resUpdate['OK']:
raise RSSManagementDBException, where(self, self.addOrModifyAccountingCacheRes) + resUpdate['Message']
else:
req = "INSERT INTO AccountingCache (Name, PlotType, PlotName, Result, DateEffective, "
req = req + "LastCheckTime) VALUES ('%s', '%s', '%s', " %(name, plotType, plotName)
req = req + "\"%s\", '%s', '%s')" %(result, dateEffective, now)
resUpdate = self.db._update(req)
if not resUpdate['OK']:
raise RSSManagementDBException, where(self, self.addOrModifyAccountingCacheRes) + resUpdate['Message']
#############################################################################
def getAccountingCacheStuff(self, paramsList = None, acID = None, name = None, plotType = None,
plotName = None, result = None, dateEffective = None,
lastCheckTime = None):
"""
Generic function to get values from the AccountingCache table.
:params:
:attr:`paramsList` - string or list of strings
:attr:`acID` - string or list of strings
:attr:`name` - string or list of strings
:attr:`plotName` - string or list of strings
:attr:`result` - string or list of strings
:attr:`dateEffective` - string or list of strings
:attr:`lastCheckTime` - string or list of strings
"""
if (paramsList == None or paramsList == []):
params = "acID, Name, PlotType, PlotName, Result, DateEffective "
else:
if type(paramsList) != type([]):
paramsList = [paramsList]
params = ','.join([x.strip()+' ' for x in paramsList])
req = "SELECT " + params + "FROM AccountingCache "
if not (acID == name == plotType == plotName == result == dateEffective == lastCheckTime == None):
req = req + "WHERE "
if acID is not None:
if type(acID) is not type([]):
acID = [acID]
req = req + "acID IN (" + ','.join([str(x).strip() + ' ' for x in acID]) + ")"
if name is not None:
if acID is not None:
req = req + " AND "
if type(name) is not type([]):
name = [name]
req = req + "Name IN (" + ','.join(['"' + x.strip() + '"' + ' ' for x in name]) + ")"
if plotName is not None:
if acID is not None or name is not None:
req = req + " AND "
if type(plotName) is not type([]):
plotName = [plotName]
req = req + "PlotName IN (" + ','.join(['"' + x.strip() + '"' + ' ' for x in plotName]) + ")"
if plotType is not None:
if acID is not None or name is not None or plotName is not None:
req = req + " AND "
if type(plotType) is not type([]):
plotType = [plotType]
req = req + "PlotType IN (" + ','.join(['"' + x.strip() + '"' + ' ' for x in plotType]) + ")"
if result is not None:
if (acID is not None or name is not None or plotName is not None or plotType is not None):
req = req + " AND "
if type(result) is not type([]):
result = [result]
req = req + "Result IN (" + ','.join(['"' + x.strip() + '"' + ' ' for x in result]) + ")"
if dateEffective is not None:
if (acID is not None or name is not None or plotName is not None or plotType is not None or result is not None):
req = req + " AND "
if type(dateEffective) is not type([]):
dateEffective = [dateEffective]
req = req + "DateEffective IN (" + ','.join(['"' + x.strip() + '"' + ' ' for x in dateEffective]) + ")"
if lastCheckTime is not None:
if (acID is not None or name is not None or plotName is not None or plotType is not None or result is not None or dateEffective is not None):
req = req + " AND "
if type(lastCheckTime) is not type([]):
lastCheckTime = [lastCheckTime]
req = req + "LastCheckTime IN (" + ','.join(['"' + x.strip() + '"' + ' ' for x in lastCheckTime]) + ")"
resQuery = self.db._query(req)
if not resQuery['OK']:
raise RSSManagementDBException, where(self, self.getAccountingCacheStuff) + resQuery['Message']
if not resQuery['Value']:
return []
return resQuery['Value']
#############################################################################
#############################################################################
# Status functions
#############################################################################
#############################################################################
#usata solo nell'handler
def addStatus(self, status, description=''):
"""
Add a status.
:params:
:attr:`status`: string - a new status
:attr:`description`: string - optional description
"""
req = "INSERT INTO Status (Status, Description)"
req = req + "VALUES ('%s', '%s');" % (status, description)
resUpdate = self.db._update(req)
if not resUpdate['OK']:
raise RSSManagementDBException, where(self, self.addStatus) + resUpdate['Message']
#############################################################################
#usata solo nell'handler
def removeStatus(self, status):
"""
Remove a status from the Status table.
:params:
:attr:`status`: string - status
"""
req = "DELETE from Status WHERE Status = '%s';" % (status)
resDel = self.db._update(req)
if not resDel['OK']:
raise RSSManagementDBException, where(self, self.removeStatus) + resDel['Message']
#############################################################################
def getStatusList(self):
"""
Get list of status with no descriptions.
"""
req = "SELECT Status from Status"
resQuery = self.db._query(req)
if not resQuery['OK']:
raise RSSManagementDBException, where(self, self.getStatusList) + resQuery['Message']
if not resQuery['Value']:
return []
l = [ x[0] for x in resQuery['Value']]
return l
#############################################################################
# Web functions
#############################################################################
#############################################################################
def getDownTimesWeb(self, selectDict, sortList = [], startItem = 0, maxItems = 1000):
"""
Get downtimes registered in the RSS DB (with a web layout)
:params:
:attr:`selectDict`: { 'Granularity':['Site', 'Resource'], 'Severity': ['OUTAGE', 'AT_RISK']}
:attr:`sortList`
:attr:`startItem`
:attr:`maxItems`
"""
granularity = selectDict['Granularity']
severity = selectDict['Severity']
if not isinstance(granularity, list):
granularity = [granularity]
if not isinstance(severity, list):
severity = [severity]
paramNames = ['Granularity', 'Name', 'Severity', 'When']
req = "SELECT Granularity, Name, Reason FROM PolicyRes WHERE "
req = req + "PolicyName LIKE 'DT_%' AND Reason LIKE \'%found%\' "
req = req + "AND Granularity in (%s)" %(','.join(['"'+x.strip()+'"' for x in granularity]))
resQuery = self.db._query(req)
if not resQuery['OK']:
raise RSSManagementDBException, resQuery['Message']
if not resQuery['Value']:
records = []
else:
resQuery = resQuery['Value']
records = []
for tuple in resQuery:
sev = tuple[2].split()[2]
if sev not in severity:
continue
when = tuple[2].split(sev)[1][1:]
if when == '':
when = 'Ongoing'
records.append([tuple[0], tuple[1], sev, when])
finalDict = {}
finalDict['TotalRecords'] = len(records)
finalDict['ParameterNames'] = paramNames
# Return all the records if maxItems == 0 or the specified number otherwise
if maxItems:
finalDict['Records'] = records[startItem:startItem+maxItems]
else:
finalDict['Records'] = records
finalDict['Extras'] = None
return finalDict
#############################################################################
class ResourceManagementDB:
"""
The ResourceManagementDB class is a front-end to the Resource Management Database.
The simplest way to instantiate an object of type :class:`ResourceManagementDB`
is simply by calling
>>> rpDB = ResourceManagementDB()
This way, it will use the standard :mod:`DIRAC.Core.Base.DB`.
But there's the possibility to use other DB classes.
For example, we could pass custom DB instantiations to it,
provided the interface is the same exposed by :mod:`DIRAC.Core.Base.DB`.
>>> AnotherDB = AnotherDBClass()
>>> rmDB = ResourceManagementDB(DBin = AnotherDB)
Alternatively, for testing purposes, you could do:
>>> from DIRAC.ResourceStatusSystem.Utilities.mock import Mock
>>> mockDB = Mock()
>>> rmDB = ResourceManagementDB(DBin = mockDB)
Or, if you want to work with a local DB, providing it's mySQL:
>>> rmDB = ResourceManagementDB(DBin = ['UserName', 'Password'])
"""
def __init__(self, *args, **kwargs):
if len(args) == 1:
if isinstance(args[0], str):
# systemInstance=args[0]
maxQueueSize = 10
if isinstance(args[0], int):
maxQueueSize = args[0]
# systemInstance='Default'
elif len(args) == 2:
# systemInstance=args[0]
maxQueueSize = args[1]
elif len(args) == 0:
# systemInstance='Default'
maxQueueSize = 10
if 'DBin' in kwargs.keys():
DBin = kwargs['DBin']
if isinstance(DBin, list):
from DIRAC.Core.Utilities.MySQL import MySQL
self.db = MySQL('localhost', DBin[0], DBin[1],
'ResourceManagementDB')
else:
self.db = DBin
else:
from DIRAC.Core.Base.DB import DB
self.db = DB('ResourceManagementDB',
'ResourceStatus/ResourceManagementDB', maxQueueSize)
# self.db = DB('ResourceStatusDB','ResourceStatus/ResourceStatusDB',maxQueueSize)
#############################################################################
#############################################################################
# Policy functions
#############################################################################
#############################################################################
def addOrModifyPolicyRes(self,
granularity,
name,
policyName,
status,
reason,
dateEffective=None):
"""
Add or modify a Policy Result to the PolicyRes table.
:params:
:attr:`granularity`: string - a ValidRes
see :mod:`DIRAC.ResourceStatusSystem.Utilities.Utils`
:attr:`name`: string - name of the ValidRes
:attr:`policyName`: string - the policy name
:attr:`status`: string - a ValidStatus:
see :mod:`DIRAC.ResourceStatusSystem.Utilities.Utils`
:attr:`reason`: string - free
:attr:`dateEffective`: datetime.datetime -
date from which the result is effective
"""
now = datetime.datetime.utcnow().replace(microsecond=0).isoformat(' ')
if dateEffective is None:
dateEffective = now
req = "SELECT Granularity, Name, PolicyName, Status, Reason FROM PolicyRes "
req = req + "WHERE Granularity = '%s' AND Name = '%s' AND " % (
granularity, name)
req = req + "PolicyName = '%s'" % (policyName)
resQuery = self.db._query(req)
if not resQuery['OK']:
raise RSSManagementDBException, where(
self, self.addOrModifyPolicyRes) + resQuery['Message']
if resQuery['Value']:
req = "UPDATE PolicyRes SET "
if resQuery['Value'][0][3] != status:
req = req + "Status = '%s', Reason = '%s', DateEffective = '%s', " % (
status, reason, dateEffective)
elif resQuery['Value'][0][4] != reason:
req = req + "Reason = '%s', " % (reason)
req = req + "LastCheckTime = '%s' WHERE Granularity = '%s' " % (
now, granularity)
req = req + "AND Name = '%s' AND PolicyName = '%s'" % (name,
policyName)
resUpdate = self.db._update(req)
if not resUpdate['OK']:
raise RSSManagementDBException, where(
self, self.addOrModifyPolicyRes) + resUpdate['Message']
else:
req = "INSERT INTO PolicyRes (Granularity, Name, PolicyName, Status, Reason, DateEffective, "
req = req + "LastCheckTime) VALUES ('%s', '%s', '%s', " % (
granularity, name, policyName)
req = req + "'%s', '%s', '%s', '%s')" % (status, reason,
dateEffective, now)
resUpdate = self.db._update(req)
if not resUpdate['OK']:
raise RSSManagementDBException, where(
self, self.addOrModifyPolicyRes) + resUpdate['Message']
#############################################################################
def getPolicyRes(self, name, policyName, lastCheckTime=False):
"""
Get a Policy Result from the PolicyRes table.
:params:
:attr:`name`: string - name of the ValidRes
:attr:`policyName`: string - the policy name
:attr:`lastCheckTime`: optional - if TRUE, it will get also the
LastCheckTime
"""
req = "SELECT Status, Reason"
if lastCheckTime:
req = req + ", LastCheckTime"
req = req + " FROM PolicyRes WHERE"
req = req + " Name = '%s' AND PolicyName = '%s'" % (name, policyName)
resQuery = self.db._query(req)
if not resQuery['OK']:
raise RSSManagementDBException, where(
self, self.getPolicyRes) + resQuery['Message']
if not resQuery['Value']:
return []
return resQuery['Value'][0]
#############################################################################
#############################################################################
# ClientsCache functions
#############################################################################
#############################################################################
def addOrModifyClientsCacheRes(self,
name,
commandName,
value,
result,
opt_ID=None,
dateEffective=None):
"""
Add or modify a Client Result to the ClientCache table.
:params:
:attr:`name`: string - name of the ValidRes
:attr:`commandName`: string - the command name
:attr:`value`: string - the value
:attr:`result`: string - command result
:attr:`opt_ID`: string or integer - optional ID (e.g. used for downtimes)
:attr:`dateEffective`: datetime.datetime -
date from which the result is effective
"""
now = datetime.datetime.utcnow().replace(microsecond=0).isoformat(' ')
if dateEffective is None:
dateEffective = now
if opt_ID is not None:
if isinstance(opt_ID, int):
opt_ID = str(opt_ID)
req = "SELECT Name, CommandName, "
if opt_ID is not None:
req = req + "Opt_ID, "
req = req + "Value, Result FROM ClientsCache WHERE "
req = req + "Name = '%s' AND CommandName = '%s' " % (name, commandName)
if opt_ID is not None:
req = req + "AND Opt_ID = '%s' " % opt_ID
req = req + "AND Value = '%s' " % value
resQuery = self.db._query(req)
if not resQuery['OK']:
raise RSSManagementDBException, where(
self, self.addOrModifyClientsCacheRes) + resQuery['Message']
if resQuery['Value']:
req = "UPDATE ClientsCache SET "
if resQuery['Value'][0][3] != result:
req = req + "Result = '%s', DateEffective = '%s', " % (
result, dateEffective)
req = req + "LastCheckTime = '%s' WHERE " % (now)
req = req + "Name = '%s' AND CommandName = '%s' AND Value = '%s'" % (
name, commandName, value)
if opt_ID is not None:
req = req + "AND Opt_ID = '%s' " % opt_ID
resUpdate = self.db._update(req)
if not resUpdate['OK']:
raise RSSManagementDBException, where(
self,
self.addOrModifyClientsCacheRes) + resUpdate['Message']
else:
req = "INSERT INTO ClientsCache (Name, CommandName, "
if opt_ID is not None:
req = req + "Opt_ID, "
req = req + "Value, Result, DateEffective, "
req = req + "LastCheckTime) VALUES ('%s', '%s', " % (name,
commandName)
if opt_ID is not None:
req = req + "'%s', " % opt_ID
req = req + "'%s', '%s', '%s', '%s')" % (value, result,
dateEffective, now)
resUpdate = self.db._update(req)
if not resUpdate['OK']:
raise RSSManagementDBException, where(
self,
self.addOrModifyClientsCacheRes) + resUpdate['Message']
#############################################################################
def getClientsCacheStuff(self,
paramsList=None,
ccID=None,
name=None,
commandName=None,
opt_ID=None,
value=None,
result=None,
dateEffective=None,
lastCheckTime=None):
"""
Generic function to get values from the ClientsCache table.
:params:
:attr:`paramsList` - string or list of strings
:attr:`ccID` - string or list of strings
:attr:`name` - string or list of strings
:attr:`commandName` - string or list of strings
:attr:`opt_ID` - string or list of strings
:attr:`value` - string or list of strings
:attr:`result` - string or list of strings
:attr:`dateEffective` - string or list of strings
:attr:`lastCheckTime` - string or list of strings
"""
if (paramsList == None or paramsList == []):
params = "ccID, Name, CommandName, Opt_ID, Value, Result, DateEffective "
else:
if type(paramsList) is not type([]):
paramsList = [paramsList]
params = ','.join([x.strip() + ' ' for x in paramsList])
req = "SELECT " + params + "FROM ClientsCache "
if not (ccID == name == commandName == opt_ID == value == result ==
dateEffective == lastCheckTime == None):
req = req + "WHERE "
if ccID is not None:
if type(ccID) is not type([]):
ccID = [ccID]
req = req + "ccID IN (" + ','.join(
[str(x).strip() + ' ' for x in ccID]) + ")"
if name is not None:
if ccID is not None:
req = req + " AND "
if type(name) is not type([]):
name = [name]
req = req + "Name IN (" + ','.join(
['"' + x.strip() + '"' + ' ' for x in name]) + ")"
if commandName is not None:
if ccID is not None or name is not None:
req = req + " AND "
if type(commandName) is not type([]):
commandName = [commandName]
req = req + "CommandName IN (" + ','.join(
['"' + x.strip() + '"' + ' ' for x in commandName]) + ")"
if opt_ID is not None:
if ccID is not None or name is not None or commandName is not None:
req = req + " AND "
if type(opt_ID) is not type([]):
opt_ID = [opt_ID]
req = req + "Opt_ID IN (" + ','.join(
['"' + str(x).strip() + '"' + ' ' for x in opt_ID]) + ")"
if value is not None:
if ccID is not None or name is not None or commandName is not None or opt_ID is not None:
req = req + " AND "
if type(value) is not type([]):
value = [value]
req = req + "Value IN (" + ','.join(
['"' + x.strip() + '"' + ' ' for x in value]) + ")"
if result is not None:
if (ccID is not None or name is not None or commandName is not None
or opt_ID is not None or value is not None):
req = req + " AND "
if type(result) is not type([]):
result = [result]
req = req + "result IN (" + ','.join(
['"' + x.strip() + '"' + ' ' for x in result]) + ")"
if dateEffective is not None:
if (ccID is not None or name is not None or commandName is not None
or opt_ID is not None or value is not None
or result is not None):
req = req + " AND "
if type(dateEffective) is not type([]):
dateEffective = [dateEffective]
req = req + "dateEffective IN (" + ','.join(
['"' + x.strip() + '"' + ' ' for x in dateEffective]) + ")"
if lastCheckTime is not None:
if (ccID is not None or name is not None or commandName is not None
or opt_ID is not None or value is not None
or result is not None or dateEffective is not None):
req = req + " AND "
if type(lastCheckTime) is not type([]):
lastCheckTime = [lastCheckTime]
req = req + "lastCheckTime IN (" + ','.join(
['"' + x.strip() + '"' + ' ' for x in lastCheckTime]) + ")"
resQuery = self.db._query(req)
if not resQuery['OK']:
raise RSSManagementDBException, where(
self, self.getClientsCacheStuff) + resQuery['Message']
if not resQuery['Value']:
return []
return resQuery['Value']
#############################################################################
#############################################################################
# AccountingCache functions
#############################################################################
#############################################################################
def addOrModifyAccountingCacheRes(self,
name,
plotType,
plotName,
result,
dateEffective=None):
"""
Add or modify an Accounting Result to the AccountingCache table.
:params:
:attr:`name`: string - name of the ValidRes
:attr:`plotType`: string - the plotType name (e.g. 'Pilot')
:attr:`plotName`: string - the plot name
:attr:`result`: string - command result
:attr:`dateEffective`: datetime.datetime -
date from which the result is effective
"""
now = datetime.datetime.utcnow().replace(microsecond=0).isoformat(' ')
if dateEffective is None:
dateEffective = now
req = "SELECT Name, PlotType, PlotName, Result FROM AccountingCache WHERE "
req = req + "Name = '%s' AND PlotType = '%s' AND PlotName = '%s' " % (
name, plotType, plotName)
resQuery = self.db._query(req)
if not resQuery['OK']:
raise RSSManagementDBException, where(
self, self.addOrModifyAccountingCacheRes) + resQuery['Message']
if resQuery['Value']:
req = "UPDATE AccountingCache SET "
if resQuery['Value'][0][3] != result:
req = req + "Result = \"%s\", DateEffective = '%s', " % (
result, dateEffective)
req = req + "LastCheckTime = '%s' WHERE " % (now)
req = req + "Name = '%s' AND PlotType = '%s' AND PlotName = '%s'" % (
name, plotType, plotName)
resUpdate = self.db._update(req)
if not resUpdate['OK']:
raise RSSManagementDBException, where(
self,
self.addOrModifyAccountingCacheRes) + resUpdate['Message']
else:
req = "INSERT INTO AccountingCache (Name, PlotType, PlotName, Result, DateEffective, "
req = req + "LastCheckTime) VALUES ('%s', '%s', '%s', " % (
name, plotType, plotName)
req = req + "\"%s\", '%s', '%s')" % (result, dateEffective, now)
resUpdate = self.db._update(req)
if not resUpdate['OK']:
raise RSSManagementDBException, where(
self,
self.addOrModifyAccountingCacheRes) + resUpdate['Message']
#############################################################################
def getAccountingCacheStuff(self,
paramsList=None,
acID=None,
name=None,
plotType=None,
plotName=None,
result=None,
dateEffective=None,
lastCheckTime=None):
"""
Generic function to get values from the AccountingCache table.
:params:
:attr:`paramsList` - string or list of strings
:attr:`acID` - string or list of strings
:attr:`name` - string or list of strings
:attr:`plotName` - string or list of strings
:attr:`result` - string or list of strings
:attr:`dateEffective` - string or list of strings
:attr:`lastCheckTime` - string or list of strings
"""
if (paramsList == None or paramsList == []):
params = "acID, Name, PlotType, PlotName, Result, DateEffective "
else:
if type(paramsList) != type([]):
paramsList = [paramsList]
params = ','.join([x.strip() + ' ' for x in paramsList])
req = "SELECT " + params + "FROM AccountingCache "
if not (acID == name == plotType == plotName == result == dateEffective
== lastCheckTime == None):
req = req + "WHERE "
if acID is not None:
if type(acID) is not type([]):
acID = [acID]
req = req + "acID IN (" + ','.join(
[str(x).strip() + ' ' for x in acID]) + ")"
if name is not None:
if acID is not None:
req = req + " AND "
if type(name) is not type([]):
name = [name]
req = req + "Name IN (" + ','.join(
['"' + x.strip() + '"' + ' ' for x in name]) + ")"
if plotName is not None:
if acID is not None or name is not None:
req = req + " AND "
if type(plotName) is not type([]):
plotName = [plotName]
req = req + "PlotName IN (" + ','.join(
['"' + x.strip() + '"' + ' ' for x in plotName]) + ")"
if plotType is not None:
if acID is not None or name is not None or plotName is not None:
req = req + " AND "
if type(plotType) is not type([]):
plotType = [plotType]
req = req + "PlotType IN (" + ','.join(
['"' + x.strip() + '"' + ' ' for x in plotType]) + ")"
if result is not None:
if (acID is not None or name is not None or plotName is not None
or plotType is not None):
req = req + " AND "
if type(result) is not type([]):
result = [result]
req = req + "Result IN (" + ','.join(
['"' + x.strip() + '"' + ' ' for x in result]) + ")"
if dateEffective is not None:
if (acID is not None or name is not None or plotName is not None
or plotType is not None or result is not None):
req = req + " AND "
if type(dateEffective) is not type([]):
dateEffective = [dateEffective]
req = req + "DateEffective IN (" + ','.join(
['"' + x.strip() + '"' + ' ' for x in dateEffective]) + ")"
if lastCheckTime is not None:
if (acID is not None or name is not None or plotName is not None
or plotType is not None or result is not None
or dateEffective is not None):
req = req + " AND "
if type(lastCheckTime) is not type([]):
lastCheckTime = [lastCheckTime]
req = req + "LastCheckTime IN (" + ','.join(
['"' + x.strip() + '"' + ' ' for x in lastCheckTime]) + ")"
resQuery = self.db._query(req)
if not resQuery['OK']:
raise RSSManagementDBException, where(
self, self.getAccountingCacheStuff) + resQuery['Message']
if not resQuery['Value']:
return []
return resQuery['Value']
#############################################################################
#############################################################################
# Status functions
#############################################################################
#############################################################################
#usata solo nell'handler
def addStatus(self, status, description=''):
"""
Add a status.
:params:
:attr:`status`: string - a new status
:attr:`description`: string - optional description
"""
req = "INSERT INTO Status (Status, Description)"
req = req + "VALUES ('%s', '%s');" % (status, description)
resUpdate = self.db._update(req)
if not resUpdate['OK']:
raise RSSManagementDBException, where(
self, self.addStatus) + resUpdate['Message']
#############################################################################
#usata solo nell'handler
def removeStatus(self, status):
"""
Remove a status from the Status table.
:params:
:attr:`status`: string - status
"""
req = "DELETE from Status WHERE Status = '%s';" % (status)
resDel = self.db._update(req)
if not resDel['OK']:
raise RSSManagementDBException, where(
self, self.removeStatus) + resDel['Message']
#############################################################################
def getStatusList(self):
"""
Get list of status with no descriptions.
"""
req = "SELECT Status from Status"
resQuery = self.db._query(req)
if not resQuery['OK']:
raise RSSManagementDBException, where(
self, self.getStatusList) + resQuery['Message']
if not resQuery['Value']:
return []
l = [x[0] for x in resQuery['Value']]
return l
#############################################################################
# Web functions
#############################################################################
#############################################################################
def getDownTimesWeb(self,
selectDict,
sortList=[],
startItem=0,
maxItems=1000):
"""
Get downtimes registered in the RSS DB (with a web layout)
:params:
:attr:`selectDict`: { 'Granularity':['Site', 'Resource'], 'Severity': ['OUTAGE', 'AT_RISK']}
:attr:`sortList`
:attr:`startItem`
:attr:`maxItems`
"""
granularity = selectDict['Granularity']
severity = selectDict['Severity']
if not isinstance(granularity, list):
granularity = [granularity]
if not isinstance(severity, list):
severity = [severity]
paramNames = ['Granularity', 'Name', 'Severity', 'When']
req = "SELECT Granularity, Name, Reason FROM PolicyRes WHERE "
req = req + "PolicyName LIKE 'DT_%' AND Reason LIKE \'%found%\' "
req = req + "AND Granularity in (%s)" % (','.join(
['"' + x.strip() + '"' for x in granularity]))
resQuery = self.db._query(req)
if not resQuery['OK']:
raise RSSManagementDBException, resQuery['Message']
if not resQuery['Value']:
records = []
else:
resQuery = resQuery['Value']
records = []
for tuple in resQuery:
sev = tuple[2].split()[2]
if sev not in severity:
continue
when = tuple[2].split(sev)[1][1:]
if when == '':
when = 'Ongoing'
records.append([tuple[0], tuple[1], sev, when])
finalDict = {}
finalDict['TotalRecords'] = len(records)
finalDict['ParameterNames'] = paramNames
# Return all the records if maxItems == 0 or the specified number otherwise
if maxItems:
finalDict['Records'] = records[startItem:startItem + maxItems]
else:
finalDict['Records'] = records
finalDict['Extras'] = None
return finalDict
#############################################################################
class RSSConfigurationDB(object):
def __init__(self, DBin = None, maxQueueSize = 10):
if DBin: self.db = DBin
else:
from DIRAC.Core.Base.DB import DB
self.db = DB('RSSConfigurationDB',
'ResourceStatus/RSSConfigurationDB', maxQueueSize)
def _delete(self, tableName, inFields, inValues):
# FIXME: Should go in DIRAC.Core.Utilities.MySQL
zipped = zip(inFields, inValues)
where_clause = ''
for i,(l,v) in enumerate(zipped):
if i == 0:
where_clause = where_clause + l + '="' + v + '" '
else:
where_clause = where_clause + 'AND ' + l + '="' + v + '" '
return self.db._update('DELETE FROM ' + tableName + ' WHERE ' + where_clause)
def _addValue(self, table, vdict):
keys, values = list_split(vdict.items())
return self.db._insert(table, keys, values)
def _delValue(self, table, vdict):
keys, values = list_split(vdict.items())
return self._delete(table, keys, values)
def _addValues(self, table, vdicts):
res = []
for d in vdicts:
res.append(self._addValue(table, d))
return res
def _delValues(self, table, vdicts):
res = []
for d in vdicts:
res.append(self._delValue(table, d))
return res
def _query(self, table):
"""
Returns values in a list instead of a tuple, i.e. retval[1] is a
list of tuples, one per line of result.
"""
retval = self.db._query("SELECT * from " + table)
return retval['OK'], list(retval['Value'])
# Helper functions.
def addUsers(self, unames):
"""Add users. The argument is a list of strings representing
usernames."""
unames = [{'login':u} for u in unames]
return self._addValues(t_users, unames)
def addUser(self, uname):
"""Add one user. The argument is a string representing the
username"""
return self.addUsers([uname])[0]
def delUsers(self, unames):
"""Delete users. The argument is a list of strings representing
usernames."""
unames = [{'login': u} for u in unames]
return self._delValues(t_users, unames)
def delUser(self, uname):
"""Delete a user. The argument is a string representing
username."""
return self.delUsers([uname])[0]
def getUsers(self):
"""Return users registered in database. Return S_OK() with Value:
a dict representing users with (for now) only a field 'login'.
TODO: This function is not supposed to fail in normal conditions,
and result incorrect if it fails. Fix this.
"""
ret, users = self._query(t_users)
users = [{'login': u} for u in users]
return {'OK':ret, 'Value':users}
def addStatuses(self, statuses):
"""Add statuses. Argument is a list of tuples (label, priority)"""
statuses = [{'label':l, 'priority':p} for (l,p) in statuses]
return self._addValues(t_statuses, statuses)
def addStatus(self, label, priority):
"""Add a status. The argument is respectively a string and an
integer, representing the name of the status and the priority"""
return self.addStatuses([(label, priority)])[0]
def delStatuses(self, statuses):
"""Delete statuses. Argument is a list of strings (labels)"""
statuses = [{'label':l} for l in statuses]
return self._delValues(t_statuses, statuses)
def delStatus(self, status):
return self.delStatuses([status])[0]
def getStatuses(self):
ret, statuses = self._query(t_statuses)
statuses = [{'label':l, 'priority':p} for (l,p) in statuses]
return {'OK':ret, 'Value':statuses}
def addCheckFreq(self, kwargs):
"""
Add a new check frequency. Arguments must be a dict with fields:
granularity, site_type, status, freq.
"""
return self._addValue(t_checkfreqs, kwargs)
def delCheckFreq(self, kwargs):
"""
Delete check frequencies. Arguments must be a dict with fields
part or all of: granularity, site_type, status, freq.
"""
return self._delValue(t_checkfreqs, kwargs)
def getCheckFreqs(self):
ret, freqs = self._query(t_checkfreqs)
freqs = [{'granularity':g, 'site_type':st,'status':sta, 'freq':f}
for (g,st,sta,f) in freqs]
return {'OK':ret, 'Value':freqs}
def addAssigneeGroup(self, kwargs):
"""
Add new assignee groups. Argument must be a dict with fields:
label, login, granularity, site_type, service_type, resource_type,
notification.
"""
return self._addValue(t_assigneegroups, kwargs)
def delAssigneeGroup(self, kwargs):
"""
Delete assignee groups. Argument must be a dict with fields all or
part of: label, login, granularity, site_type, service_type,
resource_type, notification.
"""
return self._delValue(t_assigneegroups, kwargs)
def getAssigneeGroups(self):
ret, groups = self._query(t_assigneegroups)
groups = [{'label':label, 'login':login, 'granularity':granularity,
'site_type':site_type, 'service_type':service_type,
'resource_type':resource_type, 'notification':notification}
for (label, login, granularity, site_type,
service_type, resource_type, notification) in groups
]
return {'OK':ret, 'Value':groups}
def addPolicy(self, kwargs):
"""
Add new policies. Argument must be a dict with fiels: label,
description, status, former_status, site_type, service_type,
resource_type.
"""
return self._addValue(t_policies, kwargs)
def delPolicy(self, kwargs):
"""
Delete policies. Argument must be a dict with fields all or part
of: label, description, status, former_status, site_type,
service_type, resource_type, or a list of labels.
"""
return self._delValue(t_policies, kwargs)
def getPolicies(self):
ret, policies = self._query(t_policies)
policies = [{'label':label, 'description':description, 'status':status,
'former_status':former_status, 'site_type':site_type,
'service_type':service_type, 'resource_type':resource_type}
for (label, description, status, former_status,
site_type, service_type, resource_type) in policies
]
return {'OK':ret, 'Value':policies}
