def _query( self, cmd, conn=False ):
start = time.time()
ret = DB._query( self, cmd, conn )
if DEBUG:
print >> debugFile, time.time() - start, cmd.replace('\n','')
debugFile.flush()
return ret
def _query(self, cmd, conn=False):
start = time.time()
ret = DB._query(self, cmd, conn)
if DEBUG:
print >> debugFile, time.time() - start, cmd.replace('\n', '')
debugFile.flush()
return ret
def _query( self, cmd, conn=False ):
""" Make queries to MPIJob DB
"""
print "DB3"
start = Time.time()
ret = DB._query( self, cmd, conn )
if DEBUG:
print >> debugFile, Time.time() - start, cmd.replace('\n','')
debugFile.flush()
print ret
return ret
def _query(self, cmd, conn=False):
""" Make queries to MPIJob DB
"""
print "DB3"
start = Time.time()
ret = DB._query(self, cmd, conn)
if DEBUG:
print >> debugFile, Time.time() - start, cmd.replace('\n', '')
debugFile.flush()
print ret
return ret
class ResourceManagementDB( object ):
'''
Class that defines the tables for the ResourceManagementDB on a python dictionary.
'''
# Written PrimaryKey as list on purpose !!
_tablesDB = {}
_tablesDB[ 'AccountingCache' ] = { 'Fields' :
{
#'AccountingCacheID' : 'INT UNSIGNED AUTO_INCREMENT NOT NULL',
'Name' : 'VARCHAR(64) NOT NULL',
'PlotType' : 'VARCHAR(16) NOT NULL',
'PlotName' : 'VARCHAR(64) NOT NULL',
'Result' : 'TEXT NOT NULL',
'DateEffective' : 'DATETIME NOT NULL',
'LastCheckTime' : 'DATETIME NOT NULL'
},
'PrimaryKey' : [ 'Name', 'PlotType', 'PlotName' ]
}
_tablesDB[ 'DowntimeCache' ] = { 'Fields' :
{
'DowntimeID' : 'VARCHAR(64) NOT NULL',
'Element' : 'VARCHAR(32) NOT NULL',
'Name' : 'VARCHAR(64) NOT NULL',
'StartDate' : 'DATETIME NOT NULL',
'EndDate' : 'DATETIME NOT NULL',
'Severity' : 'VARCHAR(32) NOT NULL',
'Description' : 'VARCHAR(512) NOT NULL',
'Link' : 'VARCHAR(255) NOT NULL',
'DateEffective' : 'DATETIME NOT NULL',
'LastCheckTime' : 'DATETIME NOT NULL'
},
'PrimaryKey' : [ 'DowntimeID' ]
}
_tablesDB[ 'GGUSTicketsCache' ] = { 'Fields' :
{
'GocSite' : 'VARCHAR(64) NOT NULL',
'Link' : 'VARCHAR(1024) NOT NULL',
'OpenTickets' : 'INTEGER NOT NULL DEFAULT 0',
'Tickets' : 'VARCHAR(1024) NOT NULL',
'LastCheckTime' : 'DATETIME NOT NULL'
},
'PrimaryKey' : [ 'GocSite' ]
}
_tablesDB[ 'JobCache' ] = { 'Fields' :
{
'Site' : 'VARCHAR(64) NOT NULL',
'MaskStatus' : 'VARCHAR(32) NOT NULL',
'Efficiency' : 'DOUBLE NOT NULL DEFAULT 0',
'Status' : 'VARCHAR(16) NOT NULL',
'LastCheckTime' : 'DATETIME NOT NULL'
},
'PrimaryKey' : [ 'Site' ]
}
_tablesDB[ 'PilotCache' ] = { 'Fields' :
{
'Site' : 'VARCHAR(64) NOT NULL',
'CE' : 'VARCHAR(64) NOT NULL',
'PilotsPerJob' : 'DOUBLE NOT NULL DEFAULT 0',
'PilotJobEff' : 'DOUBLE NOT NULL DEFAULT 0',
'Status' : 'VARCHAR(16) NOT NULL',
'LastCheckTime' : 'DATETIME NOT NULL'
},
'PrimaryKey' : [ 'Site', 'CE' ]
}
_tablesDB[ 'PolicyResult' ] = { 'Fields' :
{
'Element' : 'VARCHAR(32) NOT NULL',
'Name' : 'VARCHAR(64) NOT NULL',
'PolicyName' : 'VARCHAR(64) NOT NULL',
'StatusType' : 'VARCHAR(16) NOT NULL DEFAULT ""',
'Status' : 'VARCHAR(16) NOT NULL',
'Reason' : 'VARCHAR(512) NOT NULL DEFAULT "Unspecified"',
'DateEffective' : 'DATETIME NOT NULL',
'LastCheckTime' : 'DATETIME NOT NULL'
},
'PrimaryKey' : [ 'Element', 'Name', 'StatusType', 'PolicyName' ]
}
_tablesDB[ 'PolicyResultLog' ] = { 'Fields' :
{
'PolicyResultLogID' : 'INT UNSIGNED AUTO_INCREMENT NOT NULL',
'Element' : 'VARCHAR(32) NOT NULL',
'Name' : 'VARCHAR(64) NOT NULL',
'PolicyName' : 'VARCHAR(64) NOT NULL',
'StatusType' : 'VARCHAR(16) NOT NULL DEFAULT ""',
'Status' : 'VARCHAR(8) NOT NULL',
'Reason' : 'VARCHAR(512) NOT NULL DEFAULT "Unspecified"',
'DateEffective' : 'DATETIME NOT NULL',
'LastCheckTime' : 'DATETIME NOT NULL'
},
'PrimaryKey' : [ 'PolicyResultLogID' ]
}
_tablesDB[ 'SpaceTokenOccupancyCache' ] = { 'Fields' :
{
'Endpoint' : 'VARCHAR( 64 ) NOT NULL',
'Token' : 'VARCHAR( 64 ) NOT NULL',
'Total' : 'INTEGER NOT NULL DEFAULT 0',
'Guaranteed' : 'INTEGER NOT NULL DEFAULT 0',
'Free' : 'INTEGER NOT NULL DEFAULT 0',
'LastCheckTime' : 'DATETIME NOT NULL'
},
'PrimaryKey' : [ 'Endpoint', 'Token' ]
}
_tablesDB[ 'TransferCache' ] = { 'Fields' :
{
'SourceName' : 'VARCHAR( 64 ) NOT NULL',
'DestinationName' : 'VARCHAR( 64 ) NOT NULL',
'Metric' : 'VARCHAR( 16 ) NOT NULL',
'Value' : 'DOUBLE NOT NULL DEFAULT 0',
'LastCheckTime' : 'DATETIME NOT NULL'
},
'PrimaryKey' : [ 'SourceName', 'DestinationName', 'Metric' ]
}
_tablesDB[ 'UserRegistryCache' ] = { 'Fields' :
{
'Login' : 'VARCHAR(16)',
'Name' : 'VARCHAR(64) NOT NULL',
'Email' : 'VARCHAR(64) NOT NULL',
'LastCheckTime' : 'DATETIME NOT NULL'
},
'PrimaryKey' : [ 'Login' ]
}
_tablesDB[ 'VOBOXCache' ] = { 'Fields' :
{
'Site' : 'VARCHAR( 64 ) NOT NULL',
'System' : 'VARCHAR( 64 ) NOT NULL',
'ServiceUp' : 'INTEGER NOT NULL DEFAULT 0',
'MachineUp' : 'INTEGER NOT NULL DEFAULT 0',
'LastCheckTime' : 'DATETIME NOT NULL'
},
'PrimaryKey' : [ 'Site', 'System' ]
}
_tablesLike = {}
_likeToTable = {}
def __init__( self, maxQueueSize = 10, mySQL = None ):
'''
Constructor, accepts any DB or mySQL connection, mostly used for testing
purposes.
'''
self._tableDict = self.__generateTables()
if mySQL is not None:
self.database = mySQL
else:
self.database = DB( 'ResourceManagementDB',
'ResourceStatus/ResourceManagementDB', maxQueueSize )
## SQL Methods ###############################################################
def insert( self, params, meta ):
'''
Inserts args in the DB making use of kwargs where parameters such as
the 'table' are specified ( filled automatically by the Client). Typically you
will not pass kwargs to this function, unless you know what are you doing
and you have a very special use case.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
utcnow = datetime.utcnow().replace( microsecond = 0 )
# We force lastCheckTime to utcnow if it is not present on the params
#if not( 'lastCheckTime' in params and not( params[ 'lastCheckTime' ] is None ) ):
if 'lastCheckTime' in params and params[ 'lastCheckTime' ] is None:
params[ 'lastCheckTime' ] = utcnow
if 'dateEffective' in params and params[ 'dateEffective' ] is None:
params[ 'dateEffective' ] = utcnow
return MySQLWrapper.insert( self, params, meta )
def update( self, params, meta ):
'''
Updates row with values given on args. The row selection is done using the
default of MySQLMonkey ( column.primary or column.keyColumn ). It can be
modified using kwargs. The 'table' keyword argument is mandatory, and
filled automatically by the Client. Typically you will not pass kwargs to
this function, unless you know what are you doing and you have a very
special use case.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
# We force lastCheckTime to utcnow if it is not present on the params
#if not( 'lastCheckTime' in params and not( params[ 'lastCheckTime' ] is None ) ):
if 'lastCheckTime' in params and params[ 'lastCheckTime' ] is None:
params[ 'lastCheckTime' ] = datetime.utcnow().replace( microsecond = 0 )
return MySQLWrapper.update( self, params, meta )
def select( self, params, meta ):
'''
Uses arguments to build conditional SQL statement ( WHERE ... ). If the
sql statement desired is more complex, you can use kwargs to interact with
the MySQL buildCondition parser and generate a more sophisticated query.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
return MySQLWrapper.select( self, params, meta )
def delete( self, params, meta ):
'''
Uses arguments to build conditional SQL statement ( WHERE ... ). If the
sql statement desired is more complex, you can use kwargs to interact with
the MySQL buildCondition parser and generate a more sophisticated query.
There is only one forbidden query, with all parameters None ( this would
mean a query of the type `DELETE * from TableName` ). The usage of kwargs
is the same as in the get function.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
return MySQLWrapper.delete( self, params, meta )
## Extended SQL methods ######################################################
def addOrModify( self, params, meta ):
'''
Using the PrimaryKeys of the table, it looks for the record in the database.
If it is there, it is updated, if not, it is inserted as a new entry.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
selectQuery = self.select( params, meta )
if not selectQuery[ 'OK' ]:
return selectQuery
if selectQuery[ 'Value' ]:
# Pseudo - code
# for all column not being PrimaryKey and not a time column:
# if one or more column different than params if not None:
# we update dateTime as well
columns = selectQuery[ 'Columns' ]
values = selectQuery[ 'Value' ]
if len( values ) != 1:
return S_ERROR( 'More than one value returned on addOrModify, please report !!' )
selectDict = dict( zip( columns, values[ 0 ] ) )
newDateEffective = None
for key, value in params.items():
if key in ( 'lastCheckTime', 'dateEffective' ):
continue
if value is None:
continue
if value != selectDict[ key[0].upper() + key[1:] ]:
newDateEffective = datetime.utcnow().replace( microsecond = 0 )
break
if 'dateEffective' in params:
params[ 'dateEffective' ] = newDateEffective
userQuery = self.update( params, meta )
else:
userQuery = self.insert( params, meta )
# if self.recordLogs:
#
# if 'table' in meta and meta[ 'table' ].endswith( 'Status' ):
#
# if isUpdate:
# updateRes = self.select( params, meta )
# if not updateRes[ 'OK' ]:
# return updateRes
#
# # If we are updating more that one result at a time, this is most likely
# # going to be a mess. All queries must be one at a time, if need to do
# if len( updateRes[ 'Value' ] ) != 1:
# return S_ERROR( ' PLEASE REPORT to developers !!: %s, %s' % ( params, meta ) )
# if len( updateRes[ 'Value' ][ 0 ] ) != len( updateRes[ 'Columns' ] ):
# # Uyyy, something went seriously wrong !!
# return S_ERROR( ' PLEASE REPORT to developers !!: %s' % updateRes )
#
# params = dict( zip( updateRes['Columns'], updateRes[ 'Value' ][0] ))
#
# meta[ 'tableName' ] = meta[ 'tableName' ].replace( 'Status', 'Log' )
#
# logRes = self.insert( params, meta )
# if not logRes[ 'OK' ]:
# return logRes
#
return userQuery
def addIfNotThere( self, params, meta ):
'''
Using the PrimaryKeys of the table, it looks for the record in the database.
If it is not there, it is inserted as a new entry.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
selectQuery = self.select( params, meta )
if not selectQuery[ 'OK' ]:
return selectQuery
if selectQuery[ 'Value' ]:
return selectQuery
return self.insert( params, meta )
## Auxiliar methods ##########################################################
def getTable( self, tableName ):
'''
Returns a table dictionary description given its name
'''
if tableName in self._tableDict:
return S_OK( self._tableDict[ tableName ] )
return S_ERROR( '%s is not on the schema' % tableName )
def getTablesList( self ):
'''
Returns a list of the table names in the schema.
'''
return S_OK( self._tableDict.keys() )
## Protected methods #########################################################
def _checkTable( self ):
'''
Method used by database tools to write the schema
'''
return self.__createTables()
## Private methods ###########################################################
def __createTables( self, tableName = None ):
'''
Writes the schema in the database. If no tableName is given, all tables
are written in the database. If a table is already in the schema, it is
skipped to avoid problems trying to create a table that already exists.
'''
# Horrible SQL here !!
tablesCreatedRes = self.database._query( "show tables" )
if not tablesCreatedRes[ 'OK' ]:
return tablesCreatedRes
tablesCreated = [ tableCreated[0] for tableCreated in tablesCreatedRes[ 'Value' ] ]
tables = {}
if tableName is None:
tables.update( self._tableDict )
elif tableName in self._tableDict:
tables = { tableName : self._tableDict[ tableName ] }
else:
return S_ERROR( '"%s" is not a known table' % tableName )
for tableName in tablesCreated:
if tableName in tables:
del tables[ tableName ]
res = self.database._createTables( tables )
if not res[ 'OK' ]:
return res
# Human readable S_OK message
if res[ 'Value' ] == 0:
res[ 'Value' ] = 'No tables created'
else:
res[ 'Value' ] = 'Tables created: %s' % ( ','.join( tables.keys() ) )
return res
def __generateTables( self ):
'''
Method used to transform the class variables into instance variables,
for safety reasons.
'''
# Avoids copying object.
tables = {}
tables.update( self._tablesDB )
for tableName, tableLike in self._likeToTable.items():
tables[ tableName ] = self._tablesLike[ tableLike ]
return tables
################################################################################
#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF
class ResourceStatusDB( object ):
'''
Class that defines the tables for the ResourceStatusDB on a python dictionary.
'''
# Written PrimaryKey as list on purpose !!
_tablesDB = {}
_tablesLike = {}
_tablesLike[ 'ElementStatus' ] = { 'Fields' :{'Name' : 'VARCHAR(64) NOT NULL',
'StatusType' : 'VARCHAR(128) NOT NULL DEFAULT "all"',
'Status' : 'VARCHAR(8) NOT NULL DEFAULT ""',
'ElementType' : 'VARCHAR(32) NOT NULL DEFAULT ""',
'Reason' : 'VARCHAR(512) NOT NULL DEFAULT "Unspecified"',
'DateEffective' : 'DATETIME NOT NULL',
'LastCheckTime' : 'DATETIME NOT NULL DEFAULT "1000-01-01 00:00:00"',
'TokenOwner' : 'VARCHAR(16) NOT NULL DEFAULT "rs_svc"',
'TokenExpiration' : 'DATETIME NOT NULL DEFAULT "9999-12-31 23:59:59"'
},
'PrimaryKey' : [ 'Name', 'StatusType' ]#, 'ElementType' ] #FIXME: elementType is needed to be part of the key ??
}
_tablesLike[ 'ElementWithID' ] = { 'Fields' : {'ID' : 'BIGINT UNSIGNED AUTO_INCREMENT NOT NULL',
'Name' : 'VARCHAR(64) NOT NULL',
'StatusType' : 'VARCHAR(128) NOT NULL DEFAULT "all"',
'Status' : 'VARCHAR(8) NOT NULL DEFAULT ""',
'ElementType' : 'VARCHAR(32) NOT NULL DEFAULT ""',
'Reason' : 'VARCHAR(512) NOT NULL DEFAULT "Unspecified"',
'DateEffective' : 'DATETIME NOT NULL',
'LastCheckTime' : 'DATETIME NOT NULL DEFAULT "1000-01-01 00:00:00"',
'TokenOwner' : 'VARCHAR(16) NOT NULL DEFAULT "rs_svc"',
'TokenExpiration' : 'DATETIME NOT NULL DEFAULT "9999-12-31 23:59:59"'
},
'PrimaryKey' : [ 'ID' ]
}
_likeToTable = { 'SiteStatus' : 'ElementStatus',
'SiteLog' : 'ElementWithID',
'SiteHistory' : 'ElementWithID',
'ResourceStatus' : 'ElementStatus',
'ResourceLog' : 'ElementWithID',
'ResourceHistory' : 'ElementWithID',
'NodeStatus' : 'ElementStatus',
'NodeLog' : 'ElementWithID',
'NodeHistory' : 'ElementWithID',
'ComponentStatus' : 'ElementStatus',
'ComponentLog' : 'ElementWithID',
'ComponentHistory' : 'ElementWithID',
}
def __init__( self, mySQL = None ):
'''
Constructor, accepts any DB or mySQL connection, mostly used for testing
purposes.
'''
self._tableDict = self.__generateTables()
if mySQL is not None:
self.database = mySQL
else:
self.database = DB( 'ResourceStatusDB',
'ResourceStatus/ResourceStatusDB' )
## SQL Methods ###############################################################
def insert( self, params, meta ):
'''
Inserts args in the DB making use of kwargs where parameters such as
the 'table' are specified ( filled automatically by the Client). Typically you
will not pass kwargs to this function, unless you know what are you doing
and you have a very special use case.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
utcnow = datetime.utcnow().replace( microsecond = 0 )
# We force lastCheckTime to utcnow if it is not present on the params
#if not( 'lastCheckTime' in params and not( params[ 'lastCheckTime' ] is None ) ):
if 'lastCheckTime' in params and params[ 'lastCheckTime' ] is None:
params[ 'lastCheckTime' ] = utcnow
# If it is a XStatus table, we force dateEffective to now.
if 'table' in meta and meta[ 'table' ].endswith( 'Status' ):
if 'dateEffective' in params and params[ 'dateEffective' ] is None:
params[ 'dateEffective' ] = utcnow
return MySQLWrapper.insert( self, params, meta )
def update( self, params, meta ):
'''
Updates row with values given on args. The row selection is done using the
default of MySQLMonkey ( column.primary or column.keyColumn ). It can be
modified using kwargs. The 'table' keyword argument is mandatory, and
filled automatically by the Client. Typically you will not pass kwargs to
this function, unless you know what are you doing and you have a very
special use case.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
# We force lastCheckTime to utcnow if it is not present on the params
if not( 'lastCheckTime' in params and not params[ 'lastCheckTime' ] is None ):
params[ 'lastCheckTime' ] = datetime.utcnow().replace( microsecond = 0 )
return MySQLWrapper.update( self, params, meta )
def select( self, params, meta ):
'''
Uses arguments to build conditional SQL statement ( WHERE ... ). If the
sql statement desired is more complex, you can use kwargs to interact with
the MySQL buildCondition parser and generate a more sophisticated query.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
return MySQLWrapper.select( self, params, meta )
def delete( self, params, meta ):
'''
Uses arguments to build conditional SQL statement ( WHERE ... ). If the
sql statement desired is more complex, you can use kwargs to interact with
the MySQL buildCondition parser and generate a more sophisticated query.
There is only one forbidden query, with all parameters None ( this would
mean a query of the type DELETE * from TableName ). The usage of kwargs
is the same as in the get function.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
return MySQLWrapper.delete( self, params, meta )
## Extended SQL methods ######################################################
def addOrModify( self, params, meta ):
'''
Using the PrimaryKeys of the table, it looks for the record in the database.
If it is there, it is updated, if not, it is inserted as a new entry.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
selectQuery = self.select( params, meta )
if not selectQuery[ 'OK' ]:
return selectQuery
isUpdate = False
if selectQuery[ 'Value' ]:
columns = selectQuery[ 'Columns' ]
values = selectQuery[ 'Value' ]
if len( values ) != 1:
return S_ERROR( 'More than one value returned on addOrModify, please report !!' )
selectDict = dict( zip( columns, values[ 0 ] ) )
newDateEffective = None
for key, value in params.items():
if key in ( 'lastCheckTime', 'dateEffective' ):
continue
if value is None:
continue
if value != selectDict[ key[0].upper() + key[1:] ]:
newDateEffective = datetime.utcnow().replace( microsecond = 0 )
break
if 'dateEffective' in params:
params[ 'dateEffective' ] = newDateEffective
userQuery = self.update( params, meta )
isUpdate = True
else:
userQuery = self.insert( params, meta )
logResult = self._logRecord( params, meta, isUpdate )
if not logResult[ 'OK' ]:
return logResult
return userQuery
def modify( self, params, meta ):
'''
Using the PrimaryKeys of the table, it looks for the record in the database.
If it is there, it is updated, if not, it does nothing.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
selectQuery = self.select( params, meta )
if not selectQuery[ 'OK' ]:
return selectQuery
if not selectQuery[ 'Value' ]:
return S_ERROR( 'Nothing to update for %s' % str( params ) )
columns = selectQuery[ 'Columns' ]
values = selectQuery[ 'Value' ]
if len( values ) != 1:
return S_ERROR( 'More than one value returned on addOrModify, please report !!' )
selectDict = dict( zip( columns, values[ 0 ] ) )
newDateEffective = None
for key, value in params.items():
if key in ( 'lastCheckTime', 'dateEffective' ):
continue
if value is None:
continue
if value != selectDict[ key[0].upper() + key[1:] ]:
newDateEffective = datetime.utcnow().replace( microsecond = 0 )
break
if 'dateEffective' in params:
params[ 'dateEffective' ] = newDateEffective
userQuery = self.update( params, meta )
logResult = self._logRecord( params, meta, True )
if not logResult[ 'OK' ]:
return logResult
return userQuery
def addIfNotThere( self, params, meta ):
'''
Using the PrimaryKeys of the table, it looks for the record in the database.
If it is not there, it is inserted as a new entry.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
selectQuery = self.select( params, meta )
if not selectQuery[ 'OK' ]:
return selectQuery
if selectQuery[ 'Value' ]:
return selectQuery
insertQuery = self.insert( params, meta )
# Record logs
if 'table' in meta and meta[ 'table' ].endswith( 'Status' ):
meta[ 'table' ] = meta[ 'table' ].replace( 'Status', 'Log' )
logRes = self.insert( params, meta )
if not logRes[ 'OK' ]:
return logRes
return insertQuery
## Auxiliar methods ##########################################################
def getTable( self, tableName ):
'''
Returns a table dictionary description given its name
'''
if tableName in self._tableDict:
return S_OK( self._tableDict[ tableName ] )
return S_ERROR( '%s is not on the schema' % tableName )
def getTablesList( self ):
'''
Returns a list of the table names in the schema.
'''
return S_OK( self._tableDict.keys() )
## Protected methods #########################################################
def _checkTable( self ):
'''
Method used by database tools to write the schema
'''
return self.__createTables()
def _logRecord( self, params, meta, isUpdate ):
'''
Method that records every change on a LogTable, if activated on the CS.
'''
if not ( 'table' in meta and meta[ 'table' ].endswith( 'Status' ) ):
return S_OK()
if isUpdate:
updateRes = self.select( params, meta )
if not updateRes[ 'OK' ]:
return updateRes
# If we are updating more that one result at a time, this is most likely
# going to be a mess. All queries must be one at a time, if need to do
if len( updateRes[ 'Value' ] ) != 1:
return S_ERROR( ' PLEASE REPORT to developers !!: %s, %s' % ( params, meta ) )
if len( updateRes[ 'Value' ][ 0 ] ) != len( updateRes[ 'Columns' ] ):
# Uyyy, something went seriously wrong !!
return S_ERROR( ' PLEASE REPORT to developers !!: %s' % updateRes )
params = dict( zip( updateRes['Columns'], updateRes[ 'Value' ][0] ))
meta[ 'table' ] = meta[ 'table' ].replace( 'Status', 'Log' )
logRes = self.insert( params, meta )
return logRes
## Private methods ###########################################################
def __createTables( self, tableName = None ):
'''
Writes the schema in the database. If no tableName is given, all tables
are written in the database. If a table is already in the schema, it is
skipped to avoid problems trying to create a table that already exists.
'''
# Horrible SQL here !!
tablesCreatedRes = self.database._query( "show tables" )
if not tablesCreatedRes[ 'OK' ]:
return tablesCreatedRes
tablesCreated = [ tableCreated[0] for tableCreated in tablesCreatedRes[ 'Value' ] ]
tables = {}
if tableName is None:
tables.update( self._tableDict )
elif tableName in self._tableDict:
tables = { tableName : self._tableDict[ tableName ] }
else:
return S_ERROR( '"%s" is not a known table' % tableName )
for tableName in tablesCreated:
if tableName in tables:
del tables[ tableName ]
res = self.database._createTables( tables )
if not res[ 'OK' ]:
return res
# Human readable S_OK message
if res[ 'Value' ] == 0:
res[ 'Value' ] = 'No tables created'
else:
res[ 'Value' ] = 'Tables created: %s' % ( ','.join( tables.keys() ) )
return res
def __generateTables( self ):
'''
Method used to transform the class variables into instance variables,
for safety reasons.
'''
# Avoids copying object.
tables = {}
tables.update( self._tablesDB )
for tableName, tableLike in self._likeToTable.items():
tables[ tableName ] = self._tablesLike[ tableLike ]
return tables
class ResourceStatusDB(object):
'''
Class that defines the tables for the ResourceStatusDB on a python dictionary.
'''
# Written PrimaryKey as list on purpose !!
_tablesDB = {}
_tablesLike = {}
_tablesLike['ElementStatus'] = {
'Fields': {
'Name': 'VARCHAR(64) NOT NULL',
'StatusType': 'VARCHAR(128) NOT NULL DEFAULT "all"',
'Status': 'VARCHAR(8) NOT NULL DEFAULT ""',
'ElementType': 'VARCHAR(32) NOT NULL DEFAULT ""',
'Reason': 'VARCHAR(512) NOT NULL DEFAULT "Unspecified"',
'DateEffective': 'DATETIME NOT NULL',
'LastCheckTime': 'DATETIME NOT NULL DEFAULT "1000-01-01 00:00:00"',
'TokenOwner': 'VARCHAR(16) NOT NULL DEFAULT "rs_svc"',
'TokenExpiration':
'DATETIME NOT NULL DEFAULT "9999-12-31 23:59:59"'
},
#FIXME: elementType is needed to be part of the key ??
'PrimaryKey': ['Name', 'StatusType'] #, 'ElementType' ]
}
_tablesLike['ElementWithID'] = {
'Fields': {
'ID': 'BIGINT UNSIGNED AUTO_INCREMENT NOT NULL',
'Name': 'VARCHAR(64) NOT NULL',
'StatusType': 'VARCHAR(128) NOT NULL DEFAULT "all"',
'Status': 'VARCHAR(8) NOT NULL DEFAULT ""',
'ElementType': 'VARCHAR(32) NOT NULL DEFAULT ""',
'Reason': 'VARCHAR(512) NOT NULL DEFAULT "Unspecified"',
'DateEffective': 'DATETIME NOT NULL',
'LastCheckTime': 'DATETIME NOT NULL DEFAULT "1000-01-01 00:00:00"',
'TokenOwner': 'VARCHAR(16) NOT NULL DEFAULT "rs_svc"',
'TokenExpiration':
'DATETIME NOT NULL DEFAULT "9999-12-31 23:59:59"'
},
'PrimaryKey': ['ID']
}
_likeToTable = {
'SiteStatus': 'ElementStatus',
'SiteLog': 'ElementWithID',
'SiteHistory': 'ElementWithID',
'ResourceStatus': 'ElementStatus',
'ResourceLog': 'ElementWithID',
'ResourceHistory': 'ElementWithID',
'NodeStatus': 'ElementStatus',
'NodeLog': 'ElementWithID',
'NodeHistory': 'ElementWithID',
'ComponentStatus': 'ElementStatus',
'ComponentLog': 'ElementWithID',
'ComponentHistory': 'ElementWithID',
}
def __init__(self, mySQL=None):
'''
Constructor, accepts any DB or mySQL connection, mostly used for testing
purposes.
'''
self._tableDict = self.__generateTables()
if mySQL is not None:
self.database = mySQL
else:
self.database = DB('ResourceStatusDB',
'ResourceStatus/ResourceStatusDB')
## SQL Methods ###############################################################
def insert(self, params, meta):
'''
Inserts args in the DB making use of kwargs where parameters such as
the 'table' are specified ( filled automatically by the Client). Typically you
will not pass kwargs to this function, unless you know what are you doing
and you have a very special use case.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
utcnow = datetime.utcnow().replace(microsecond=0)
# We force lastCheckTime to utcnow if it is not present on the params
#if not( 'lastCheckTime' in params and not( params[ 'lastCheckTime' ] is None ) ):
if 'lastCheckTime' in params and params['lastCheckTime'] is None:
params['lastCheckTime'] = utcnow
# If it is a XStatus table, we force dateEffective to now.
if 'table' in meta and meta['table'].endswith('Status'):
if 'dateEffective' in params and params['dateEffective'] is None:
params['dateEffective'] = utcnow
return MySQLWrapper.insert(self, params, meta)
def update(self, params, meta):
'''
Updates row with values given on args. The row selection is done using the
default of MySQLMonkey ( column.primary or column.keyColumn ). It can be
modified using kwargs. The 'table' keyword argument is mandatory, and
filled automatically by the Client. Typically you will not pass kwargs to
this function, unless you know what are you doing and you have a very
special use case.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
# We force lastCheckTime to utcnow if it is not present on the params
if not ('lastCheckTime' in params
and not (params['lastCheckTime'] is None)):
params['lastCheckTime'] = datetime.utcnow().replace(microsecond=0)
return MySQLWrapper.update(self, params, meta)
def select(self, params, meta):
'''
Uses arguments to build conditional SQL statement ( WHERE ... ). If the
sql statement desired is more complex, you can use kwargs to interact with
the MySQL buildCondition parser and generate a more sophisticated query.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
return MySQLWrapper.select(self, params, meta)
def delete(self, params, meta):
'''
Uses arguments to build conditional SQL statement ( WHERE ... ). If the
sql statement desired is more complex, you can use kwargs to interact with
the MySQL buildCondition parser and generate a more sophisticated query.
There is only one forbidden query, with all parameters None ( this would
mean a query of the type `DELETE * from TableName` ). The usage of kwargs
is the same as in the get function.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
return MySQLWrapper.delete(self, params, meta)
## Extended SQL methods ######################################################
def addOrModify(self, params, meta):
'''
Using the PrimaryKeys of the table, it looks for the record in the database.
If it is there, it is updated, if not, it is inserted as a new entry.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
selectQuery = self.select(params, meta)
if not selectQuery['OK']:
return selectQuery
isUpdate = False
if selectQuery['Value']:
columns = selectQuery['Columns']
values = selectQuery['Value']
if len(values) != 1:
return S_ERROR(
'More than one value returned on addOrModify, please report !!'
)
selectDict = dict(zip(columns, values[0]))
newDateEffective = None
for key, value in params.items():
if key in ('lastCheckTime', 'dateEffective'):
continue
if value is None:
continue
if value != selectDict[key[0].upper() + key[1:]]:
newDateEffective = datetime.utcnow().replace(microsecond=0)
break
if 'dateEffective' in params:
params['dateEffective'] = newDateEffective
userQuery = self.update(params, meta)
isUpdate = True
else:
userQuery = self.insert(params, meta)
logResult = self._logRecord(params, meta, isUpdate)
if not logResult['OK']:
return logResult
return userQuery
def modify(self, params, meta):
'''
Using the PrimaryKeys of the table, it looks for the record in the database.
If it is there, it is updated, if not, it does nothing.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
selectQuery = self.select(params, meta)
if not selectQuery['OK']:
return selectQuery
if not selectQuery['Value']:
return S_ERROR('Nothing to update for %s' % str(params))
columns = selectQuery['Columns']
values = selectQuery['Value']
if len(values) != 1:
return S_ERROR(
'More than one value returned on addOrModify, please report !!'
)
selectDict = dict(zip(columns, values[0]))
newDateEffective = None
for key, value in params.items():
if key in ('lastCheckTime', 'dateEffective'):
continue
if value is None:
continue
if value != selectDict[key[0].upper() + key[1:]]:
newDateEffective = datetime.utcnow().replace(microsecond=0)
break
if 'dateEffective' in params:
params['dateEffective'] = newDateEffective
userQuery = self.update(params, meta)
logResult = self._logRecord(params, meta, True)
if not logResult['OK']:
return logResult
return userQuery
def addIfNotThere(self, params, meta):
'''
Using the PrimaryKeys of the table, it looks for the record in the database.
If it is not there, it is inserted as a new entry.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
selectQuery = self.select(params, meta)
if not selectQuery['OK']:
return selectQuery
if selectQuery['Value']:
return selectQuery
insertQuery = self.insert(params, meta)
# Record logs
if 'table' in meta and meta['table'].endswith('Status'):
meta['table'] = meta['table'].replace('Status', 'Log')
logRes = self.insert(params, meta)
if not logRes['OK']:
return logRes
return insertQuery
## Auxiliar methods ##########################################################
def getTable(self, tableName):
'''
Returns a table dictionary description given its name
'''
if tableName in self._tableDict:
return S_OK(self._tableDict[tableName])
return S_ERROR('%s is not on the schema' % tableName)
def getTablesList(self):
'''
Returns a list of the table names in the schema.
'''
return S_OK(self._tableDict.keys())
## Protected methods #########################################################
def _checkTable(self):
'''
Method used by database tools to write the schema
'''
return self.__createTables()
def _logRecord(self, params, meta, isUpdate):
'''
Method that records every change on a LogTable, if activated on the CS.
'''
if not ('table' in meta and meta['table'].endswith('Status')):
return S_OK()
if isUpdate:
updateRes = self.select(params, meta)
if not updateRes['OK']:
return updateRes
# If we are updating more that one result at a time, this is most likely
# going to be a mess. All queries must be one at a time, if need to do
if len(updateRes['Value']) != 1:
return S_ERROR(' PLEASE REPORT to developers !!: %s, %s' %
(params, meta))
if len(updateRes['Value'][0]) != len(updateRes['Columns']):
# Uyyy, something went seriously wrong !!
return S_ERROR(' PLEASE REPORT to developers !!: %s' %
updateRes)
params = dict(zip(updateRes['Columns'], updateRes['Value'][0]))
meta['table'] = meta['table'].replace('Status', 'Log')
logRes = self.insert(params, meta)
return logRes
## Private methods ###########################################################
def __createTables(self, tableName=None):
'''
Writes the schema in the database. If no tableName is given, all tables
are written in the database. If a table is already in the schema, it is
skipped to avoid problems trying to create a table that already exists.
'''
# Horrible SQL here !!
tablesCreatedRes = self.database._query("show tables")
if not tablesCreatedRes['OK']:
return tablesCreatedRes
tablesCreated = [
tableCreated[0] for tableCreated in tablesCreatedRes['Value']
]
tables = {}
if tableName is None:
tables.update(self._tableDict)
elif tableName in self._tableDict:
tables = {tableName: self._tableDict[tableName]}
else:
return S_ERROR('"%s" is not a known table' % tableName)
for tableName in tablesCreated:
if tableName in tables:
del tables[tableName]
res = self.database._createTables(tables)
if not res['OK']:
return res
# Human readable S_OK message
if res['Value'] == 0:
res['Value'] = 'No tables created'
else:
res['Value'] = 'Tables created: %s' % (','.join(tables.keys()))
return res
def __generateTables(self):
'''
Method used to transform the class variables into instance variables,
for safety reasons.
'''
# Avoids copying object.
tables = {}
tables.update(self._tablesDB)
for tableName, tableLike in self._likeToTable.items():
tables[tableName] = self._tablesLike[tableLike]
return tables
################################################################################
#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF
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(object):
'''
Class that defines the tables for the ResourceManagementDB on a python dictionary.
'''
# Written PrimaryKey as list on purpose !!
_tablesDB = {}
_tablesDB['AccountingCache'] = {
'Fields': {
#'AccountingCacheID' : 'INT UNSIGNED AUTO_INCREMENT NOT NULL',
'Name': 'VARCHAR(64) NOT NULL',
'PlotType': 'VARCHAR(16) NOT NULL',
'PlotName': 'VARCHAR(64) NOT NULL',
'Result': 'TEXT NOT NULL',
'DateEffective': 'DATETIME NOT NULL',
'LastCheckTime': 'DATETIME NOT NULL'
},
'PrimaryKey': ['Name', 'PlotType', 'PlotName']
}
_tablesDB['DowntimeCache'] = {
'Fields': {
'DowntimeID': 'VARCHAR(64) NOT NULL',
'Element': 'VARCHAR(32) NOT NULL',
'Name': 'VARCHAR(64) NOT NULL',
'StartDate': 'DATETIME NOT NULL',
'EndDate': 'DATETIME NOT NULL',
'Severity': 'VARCHAR(32) NOT NULL',
'Description': 'VARCHAR(512) NOT NULL',
'Link': 'VARCHAR(255) NOT NULL',
'DateEffective': 'DATETIME NOT NULL',
'LastCheckTime': 'DATETIME NOT NULL'
},
'PrimaryKey': ['DowntimeID']
}
_tablesDB['GGUSTicketsCache'] = {
'Fields': {
'GocSite': 'VARCHAR(64) NOT NULL',
'Link': 'VARCHAR(1024) NOT NULL',
'OpenTickets': 'INTEGER NOT NULL DEFAULT 0',
'Tickets': 'VARCHAR(1024) NOT NULL',
'LastCheckTime': 'DATETIME NOT NULL'
},
'PrimaryKey': ['GocSite']
}
_tablesDB['JobCache'] = {
'Fields': {
'Site': 'VARCHAR(64) NOT NULL',
'Timespan': 'INTEGER NOT NULL',
'Checking': 'INTEGER NOT NULL DEFAULT 0',
'Completed': 'INTEGER NOT NULL DEFAULT 0',
'Done': 'INTEGER NOT NULL DEFAULT 0',
'Failed': 'INTEGER NOT NULL DEFAULT 0',
'Killed': 'INTEGER NOT NULL DEFAULT 0',
'Matched': 'INTEGER NOT NULL DEFAULT 0',
'Received': 'INTEGER NOT NULL DEFAULT 0',
'Running': 'INTEGER NOT NULL DEFAULT 0',
'Staging': 'INTEGER NOT NULL DEFAULT 0',
'Stalled': 'INTEGER NOT NULL DEFAULT 0',
'Waiting': 'INTEGER NOT NULL DEFAULT 0',
'LastCheckTime': 'DATETIME NOT NULL'
},
'PrimaryKey': ['Site', 'Timespan']
}
_tablesDB['PilotCache'] = {
'Fields': {
'CE': 'VARCHAR(64) NOT NULL',
'Timespan': 'INTEGER NOT NULL',
'Scheduled': 'INTEGER NOT NULL DEFAULT 0',
'Waiting': 'INTEGER NOT NULL DEFAULT 0',
'Submitted': 'INTEGER NOT NULL DEFAULT 0',
'Running': 'INTEGER NOT NULL DEFAULT 0',
'Done': 'INTEGER NOT NULL DEFAULT 0',
'Aborted': 'INTEGER NOT NULL DEFAULT 0',
'Cancelled': 'INTEGER NOT NULL DEFAULT 0',
'Deleted': 'INTEGER NOT NULL DEFAULT 0',
'Failed': 'INTEGER NOT NULL DEFAULT 0',
'Held': 'INTEGER NOT NULL DEFAULT 0',
'Killed': 'INTEGER NOT NULL DEFAULT 0',
'Stalled': 'INTEGER NOT NULL DEFAULT 0',
'LastCheckTime': 'DATETIME NOT NULL'
},
'PrimaryKey': ['CE', 'Timespan']
}
_tablesDB['PolicyResult'] = {
'Fields': {
'Element': 'VARCHAR(32) NOT NULL',
'Name': 'VARCHAR(64) NOT NULL',
'PolicyName': 'VARCHAR(64) NOT NULL',
'StatusType': 'VARCHAR(16) NOT NULL DEFAULT ""',
'Status': 'VARCHAR(16) NOT NULL',
'Reason': 'VARCHAR(512) NOT NULL DEFAULT "Unspecified"',
'DateEffective': 'DATETIME NOT NULL',
'LastCheckTime': 'DATETIME NOT NULL'
},
'PrimaryKey': ['Element', 'Name', 'StatusType', 'PolicyName']
}
_tablesDB['SpaceTokenOccupancyCache'] = {
'Fields': {
'Endpoint': 'VARCHAR( 64 ) NOT NULL',
'Token': 'VARCHAR( 64 ) NOT NULL',
'Total': 'DOUBLE NOT NULL DEFAULT 0',
'Guaranteed': 'DOUBLE NOT NULL DEFAULT 0',
'Free': 'DOUBLE NOT NULL DEFAULT 0',
'LastCheckTime': 'DATETIME NOT NULL'
},
'PrimaryKey': ['Endpoint', 'Token']
}
_tablesDB['TransferCache'] = {
'Fields': {
'SourceName': 'VARCHAR( 64 ) NOT NULL',
'DestinationName': 'VARCHAR( 64 ) NOT NULL',
'Metric': 'VARCHAR( 16 ) NOT NULL',
'Value': 'DOUBLE NOT NULL DEFAULT 0',
'LastCheckTime': 'DATETIME NOT NULL'
},
'PrimaryKey': ['SourceName', 'DestinationName', 'Metric']
}
_tablesDB['UserRegistryCache'] = {
'Fields': {
'Login': '******',
'Name': 'VARCHAR(64) NOT NULL',
'Email': 'VARCHAR(64) NOT NULL',
'LastCheckTime': 'DATETIME NOT NULL'
},
'PrimaryKey': ['Login']
}
_tablesDB['VOBOXCache'] = {
'Fields': {
'Site': 'VARCHAR( 64 ) NOT NULL',
'System': 'VARCHAR( 64 ) NOT NULL',
'ServiceUp': 'INTEGER NOT NULL DEFAULT 0',
'MachineUp': 'INTEGER NOT NULL DEFAULT 0',
'LastCheckTime': 'DATETIME NOT NULL'
},
'PrimaryKey': ['Site', 'System']
}
_tablesDB['ErrorReportBuffer'] = {
'Fields': {
'ID': 'INT UNSIGNED AUTO_INCREMENT NOT NULL',
'Name': 'VARCHAR(64) NOT NULL',
'ElementType': 'VARCHAR(32) NOT NULL',
'Reporter': 'VARCHAR(64) NOT NULL',
'ErrorMessage': 'VARCHAR(512) NOT NULL',
'Operation': 'VARCHAR(64) NOT NULL',
'Arguments': 'VARCHAR(512) NOT NULL DEFAULT ""',
'DateEffective': 'DATETIME NOT NULL'
},
'PrimaryKey': ['ID']
}
_tablesLike = {}
_tablesLike['PolicyResultWithID'] = {
'Fields': {
'ID': 'INT UNSIGNED AUTO_INCREMENT NOT NULL',
'Element': 'VARCHAR(32) NOT NULL',
'Name': 'VARCHAR(64) NOT NULL',
'PolicyName': 'VARCHAR(64) NOT NULL',
'StatusType': 'VARCHAR(16) NOT NULL DEFAULT ""',
'Status': 'VARCHAR(8) NOT NULL',
'Reason': 'VARCHAR(512) NOT NULL DEFAULT "Unspecified"',
'DateEffective': 'DATETIME NOT NULL',
'LastCheckTime': 'DATETIME NOT NULL'
},
'PrimaryKey': ['ID']
}
_likeToTable = {
'PolicyResultLog': 'PolicyResultWithID',
'PolicyResultHistory': 'PolicyResultWithID',
}
def __init__(self, maxQueueSize=10, mySQL=None):
'''
Constructor, accepts any DB or mySQL connection, mostly used for testing
purposes.
'''
self._tableDict = self.__generateTables()
if mySQL is not None:
self.database = mySQL
else:
self.database = DB('ResourceManagementDB',
'ResourceStatus/ResourceManagementDB',
maxQueueSize)
## SQL Methods ###############################################################
def insert(self, params, meta):
'''
Inserts args in the DB making use of kwargs where parameters such as
the 'table' are specified ( filled automatically by the Client). Typically you
will not pass kwargs to this function, unless you know what are you doing
and you have a very special use case.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
utcnow = datetime.utcnow().replace(microsecond=0)
# We force lastCheckTime to utcnow if it is not present on the params
#if not( 'lastCheckTime' in params and not( params[ 'lastCheckTime' ] is None ) ):
if 'lastCheckTime' in params and params['lastCheckTime'] is None:
params['lastCheckTime'] = utcnow
if 'dateEffective' in params and params['dateEffective'] is None:
params['dateEffective'] = utcnow
return MySQLWrapper.insert(self, params, meta)
def update(self, params, meta):
'''
Updates row with values given on args. The row selection is done using the
default of MySQLMonkey ( column.primary or column.keyColumn ). It can be
modified using kwargs. The 'table' keyword argument is mandatory, and
filled automatically by the Client. Typically you will not pass kwargs to
this function, unless you know what are you doing and you have a very
special use case.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
# We force lastCheckTime to utcnow if it is not present on the params
#if not( 'lastCheckTime' in params and not( params[ 'lastCheckTime' ] is None ) ):
if 'lastCheckTime' in params and params['lastCheckTime'] is None:
params['lastCheckTime'] = datetime.utcnow().replace(microsecond=0)
return MySQLWrapper.update(self, params, meta)
def select(self, params, meta):
'''
Uses arguments to build conditional SQL statement ( WHERE ... ). If the
sql statement desired is more complex, you can use kwargs to interact with
the MySQL buildCondition parser and generate a more sophisticated query.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
return MySQLWrapper.select(self, params, meta)
def delete(self, params, meta):
'''
Uses arguments to build conditional SQL statement ( WHERE ... ). If the
sql statement desired is more complex, you can use kwargs to interact with
the MySQL buildCondition parser and generate a more sophisticated query.
There is only one forbidden query, with all parameters None ( this would
mean a query of the type `DELETE * from TableName` ). The usage of kwargs
is the same as in the get function.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
return MySQLWrapper.delete(self, params, meta)
## Extended SQL methods ######################################################
def addOrModify(self, params, meta):
'''
Using the PrimaryKeys of the table, it looks for the record in the database.
If it is there, it is updated, if not, it is inserted as a new entry.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
selectQuery = self.select(params, meta)
if not selectQuery['OK']:
return selectQuery
isUpdate = False
if selectQuery['Value']:
# Pseudo - code
# for all column not being PrimaryKey and not a time column:
# if one or more column different than params if not None:
# we update dateTime as well
columns = selectQuery['Columns']
values = selectQuery['Value']
if len(values) != 1:
return S_ERROR(
'More than one value returned on addOrModify, please report !!'
)
selectDict = dict(zip(columns, values[0]))
newDateEffective = None
for key, value in params.items():
if key in ('lastCheckTime', 'dateEffective'):
continue
if value is None:
continue
if value != selectDict[key[0].upper() + key[1:]]:
newDateEffective = datetime.utcnow().replace(microsecond=0)
break
if 'dateEffective' in params:
params['dateEffective'] = newDateEffective
userQuery = self.update(params, meta)
isUpdate = True
else:
userQuery = self.insert(params, meta)
# This part only applies to PolicyResult table
logResult = self._logRecord(params, meta, isUpdate)
if not logResult['OK']:
return logResult
return userQuery
# FIXME: this method looks unused. Maybe can be removed from the code.
def addIfNotThere(self, params, meta):
'''
Using the PrimaryKeys of the table, it looks for the record in the database.
If it is not there, it is inserted as a new entry.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
'''
selectQuery = self.select(params, meta)
if not selectQuery['OK']:
return selectQuery
if selectQuery['Value']:
return selectQuery
return self.insert(params, meta)
## Auxiliar methods ##########################################################
def getTable(self, tableName):
'''
Returns a table dictionary description given its name
'''
if tableName in self._tableDict:
return S_OK(self._tableDict[tableName])
return S_ERROR('%s is not on the schema' % tableName)
def getTablesList(self):
'''
Returns a list of the table names in the schema.
'''
return S_OK(self._tableDict.keys())
## Protected methods #########################################################
def _checkTable(self):
'''
Method used by database tools to write the schema
'''
return self.__createTables()
def _logRecord(self, params, meta, isUpdate):
'''
Method that records every change on a LogTable.
'''
if not ('table' in meta and meta['table'] == 'PolicyResult'):
return S_OK()
if isUpdate:
# This looks little bit like a non-sense. If we were updating, we may have
# not passed a complete set of parameters, so we have to get all them from the
# database :/. It costs us one more query.
updateRes = self.select(params, meta)
if not updateRes['OK']:
return updateRes
params = dict(zip(updateRes['Columns'], updateRes['Value'][0]))
# Writes to PolicyResult"Log"
meta['table'] += 'Log'
logRes = self.insert(params, meta)
return logRes
## Private methods ###########################################################
def __createTables(self, tableName=None):
'''
Writes the schema in the database. If no tableName is given, all tables
are written in the database. If a table is already in the schema, it is
skipped to avoid problems trying to create a table that already exists.
'''
# Horrible SQL here !!
tablesCreatedRes = self.database._query("show tables")
if not tablesCreatedRes['OK']:
return tablesCreatedRes
tablesCreated = [
tableCreated[0] for tableCreated in tablesCreatedRes['Value']
]
tables = {}
if tableName is None:
tables.update(self._tableDict)
elif tableName in self._tableDict:
tables = {tableName: self._tableDict[tableName]}
else:
return S_ERROR('"%s" is not a known table' % tableName)
for tableName in tablesCreated:
if tableName in tables:
del tables[tableName]
res = self.database._createTables(tables)
if not res['OK']:
return res
# Human readable S_OK message
if res['Value'] == 0:
res['Value'] = 'No tables created'
else:
res['Value'] = 'Tables created: %s' % (','.join(tables.keys()))
return res
def __generateTables(self):
'''
Method used to transform the class variables into instance variables,
for safety reasons.
'''
# Avoids copying object.
tables = {}
tables.update(self._tablesDB)
for tableName, tableLike in self._likeToTable.items():
tables[tableName] = self._tablesLike[tableLike]
return tables
################################################################################
#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF#EOF
class ResourceManagementDB(object):
"""
Class that defines the tables for the ResourceManagementDB on a python dictionary.
"""
# Written PrimaryKey as list on purpose !!
_tablesDB = {}
_tablesDB["AccountingCache"] = {
"Fields": {
#'AccountingCacheID' : 'INT UNSIGNED AUTO_INCREMENT NOT NULL',
"Name": "VARCHAR(64) NOT NULL",
"PlotType": "VARCHAR(16) NOT NULL",
"PlotName": "VARCHAR(64) NOT NULL",
"Result": "TEXT NOT NULL",
"DateEffective": "DATETIME NOT NULL",
"LastCheckTime": "DATETIME NOT NULL",
},
"PrimaryKey": ["Name", "PlotType", "PlotName"],
}
_tablesDB["DowntimeCache"] = {
"Fields": {
"DowntimeID": "VARCHAR(64) NOT NULL",
"Element": "VARCHAR(32) NOT NULL",
"Name": "VARCHAR(64) NOT NULL",
"StartDate": "DATETIME NOT NULL",
"EndDate": "DATETIME NOT NULL",
"Severity": "VARCHAR(32) NOT NULL",
"Description": "VARCHAR(512) NOT NULL",
"Link": "VARCHAR(255) NOT NULL",
"DateEffective": "DATETIME NOT NULL",
"LastCheckTime": "DATETIME NOT NULL",
},
"PrimaryKey": ["DowntimeID"],
}
_tablesDB["GGUSTicketsCache"] = {
"Fields": {
"GocSite": "VARCHAR(64) NOT NULL",
"Link": "VARCHAR(1024) NOT NULL",
"OpenTickets": "INTEGER NOT NULL DEFAULT 0",
"Tickets": "VARCHAR(1024) NOT NULL",
"LastCheckTime": "DATETIME NOT NULL",
},
"PrimaryKey": ["GocSite"],
}
_tablesDB["JobCache"] = {
"Fields": {
"Site": "VARCHAR(64) NOT NULL",
"Timespan": "INTEGER NOT NULL",
"Checking": "INTEGER NOT NULL DEFAULT 0",
"Completed": "INTEGER NOT NULL DEFAULT 0",
"Done": "INTEGER NOT NULL DEFAULT 0",
"Failed": "INTEGER NOT NULL DEFAULT 0",
"Killed": "INTEGER NOT NULL DEFAULT 0",
"Matched": "INTEGER NOT NULL DEFAULT 0",
"Received": "INTEGER NOT NULL DEFAULT 0",
"Running": "INTEGER NOT NULL DEFAULT 0",
"Staging": "INTEGER NOT NULL DEFAULT 0",
"Stalled": "INTEGER NOT NULL DEFAULT 0",
"Waiting": "INTEGER NOT NULL DEFAULT 0",
"LastCheckTime": "DATETIME NOT NULL",
},
"PrimaryKey": ["Site", "Timespan"],
}
_tablesDB["PilotCache"] = {
"Fields": {
"CE": "VARCHAR(64) NOT NULL",
"Timespan": "INTEGER NOT NULL",
"Scheduled": "INTEGER NOT NULL DEFAULT 0",
"Waiting": "INTEGER NOT NULL DEFAULT 0",
"Submitted": "INTEGER NOT NULL DEFAULT 0",
"Running": "INTEGER NOT NULL DEFAULT 0",
"Done": "INTEGER NOT NULL DEFAULT 0",
"Aborted": "INTEGER NOT NULL DEFAULT 0",
"Cancelled": "INTEGER NOT NULL DEFAULT 0",
"Deleted": "INTEGER NOT NULL DEFAULT 0",
"Failed": "INTEGER NOT NULL DEFAULT 0",
"Held": "INTEGER NOT NULL DEFAULT 0",
"Killed": "INTEGER NOT NULL DEFAULT 0",
"Stalled": "INTEGER NOT NULL DEFAULT 0",
"LastCheckTime": "DATETIME NOT NULL",
},
"PrimaryKey": ["CE", "Timespan"],
}
_tablesDB["PolicyResult"] = {
"Fields": {
"Element": "VARCHAR(32) NOT NULL",
"Name": "VARCHAR(64) NOT NULL",
"PolicyName": "VARCHAR(64) NOT NULL",
"StatusType": 'VARCHAR(16) NOT NULL DEFAULT ""',
"Status": "VARCHAR(16) NOT NULL",
"Reason": 'VARCHAR(512) NOT NULL DEFAULT "Unspecified"',
"DateEffective": "DATETIME NOT NULL",
"LastCheckTime": "DATETIME NOT NULL",
},
"PrimaryKey": ["Element", "Name", "StatusType", "PolicyName"],
}
_tablesDB["SpaceTokenOccupancyCache"] = {
"Fields": {
"Endpoint": "VARCHAR( 64 ) NOT NULL",
"Token": "VARCHAR( 64 ) NOT NULL",
"Total": "DOUBLE NOT NULL DEFAULT 0",
"Guaranteed": "DOUBLE NOT NULL DEFAULT 0",
"Free": "DOUBLE NOT NULL DEFAULT 0",
"LastCheckTime": "DATETIME NOT NULL",
},
"PrimaryKey": ["Endpoint", "Token"],
}
_tablesDB["TransferCache"] = {
"Fields": {
"SourceName": "VARCHAR( 64 ) NOT NULL",
"DestinationName": "VARCHAR( 64 ) NOT NULL",
"Metric": "VARCHAR( 16 ) NOT NULL",
"Value": "DOUBLE NOT NULL DEFAULT 0",
"LastCheckTime": "DATETIME NOT NULL",
},
"PrimaryKey": ["SourceName", "DestinationName", "Metric"],
}
_tablesDB["UserRegistryCache"] = {
"Fields": {
"Login": "******",
"Name": "VARCHAR(64) NOT NULL",
"Email": "VARCHAR(64) NOT NULL",
"LastCheckTime": "DATETIME NOT NULL",
},
"PrimaryKey": ["Login"],
}
_tablesDB["VOBOXCache"] = {
"Fields": {
"Site": "VARCHAR( 64 ) NOT NULL",
"System": "VARCHAR( 64 ) NOT NULL",
"ServiceUp": "INTEGER NOT NULL DEFAULT 0",
"MachineUp": "INTEGER NOT NULL DEFAULT 0",
"LastCheckTime": "DATETIME NOT NULL",
},
"PrimaryKey": ["Site", "System"],
}
_tablesDB["ErrorReportBuffer"] = {
"Fields": {
"ID": "INT UNSIGNED AUTO_INCREMENT NOT NULL",
"Name": "VARCHAR(64) NOT NULL",
"ElementType": "VARCHAR(32) NOT NULL",
"Reporter": "VARCHAR(64) NOT NULL",
"ErrorMessage": "VARCHAR(512) NOT NULL",
"Operation": "VARCHAR(64) NOT NULL",
"Arguments": 'VARCHAR(512) NOT NULL DEFAULT ""',
"DateEffective": "DATETIME NOT NULL",
},
"PrimaryKey": ["ID"],
}
_tablesLike = {}
_tablesLike["PolicyResultWithID"] = {
"Fields": {
"ID": "INT UNSIGNED AUTO_INCREMENT NOT NULL",
"Element": "VARCHAR(32) NOT NULL",
"Name": "VARCHAR(64) NOT NULL",
"PolicyName": "VARCHAR(64) NOT NULL",
"StatusType": 'VARCHAR(16) NOT NULL DEFAULT ""',
"Status": "VARCHAR(8) NOT NULL",
"Reason": 'VARCHAR(512) NOT NULL DEFAULT "Unspecified"',
"DateEffective": "DATETIME NOT NULL",
"LastCheckTime": "DATETIME NOT NULL",
},
"PrimaryKey": ["ID"],
}
_likeToTable = {"PolicyResultLog": "PolicyResultWithID", "PolicyResultHistory": "PolicyResultWithID"}
def __init__(self, maxQueueSize=10, mySQL=None):
"""
Constructor, accepts any DB or mySQL connection, mostly used for testing
purposes.
"""
self._tableDict = self.__generateTables()
if mySQL is not None:
self.database = mySQL
else:
self.database = DB("ResourceManagementDB", "ResourceStatus/ResourceManagementDB", maxQueueSize)
## SQL Methods ###############################################################
def insert(self, params, meta):
"""
Inserts args in the DB making use of kwargs where parameters such as
the 'table' are specified ( filled automatically by the Client). Typically you
will not pass kwargs to this function, unless you know what are you doing
and you have a very special use case.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
"""
utcnow = datetime.utcnow().replace(microsecond=0)
# We force lastCheckTime to utcnow if it is not present on the params
# if not( 'lastCheckTime' in params and not( params[ 'lastCheckTime' ] is None ) ):
if "lastCheckTime" in params and params["lastCheckTime"] is None:
params["lastCheckTime"] = utcnow
if "dateEffective" in params and params["dateEffective"] is None:
params["dateEffective"] = utcnow
return MySQLWrapper.insert(self, params, meta)
def update(self, params, meta):
"""
Updates row with values given on args. The row selection is done using the
default of MySQLMonkey ( column.primary or column.keyColumn ). It can be
modified using kwargs. The 'table' keyword argument is mandatory, and
filled automatically by the Client. Typically you will not pass kwargs to
this function, unless you know what are you doing and you have a very
special use case.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
"""
# We force lastCheckTime to utcnow if it is not present on the params
# if not( 'lastCheckTime' in params and not( params[ 'lastCheckTime' ] is None ) ):
if "lastCheckTime" in params and params["lastCheckTime"] is None:
params["lastCheckTime"] = datetime.utcnow().replace(microsecond=0)
return MySQLWrapper.update(self, params, meta)
def select(self, params, meta):
"""
Uses arguments to build conditional SQL statement ( WHERE ... ). If the
sql statement desired is more complex, you can use kwargs to interact with
the MySQL buildCondition parser and generate a more sophisticated query.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
"""
return MySQLWrapper.select(self, params, meta)
def delete(self, params, meta):
"""
Uses arguments to build conditional SQL statement ( WHERE ... ). If the
sql statement desired is more complex, you can use kwargs to interact with
the MySQL buildCondition parser and generate a more sophisticated query.
There is only one forbidden query, with all parameters None ( this would
mean a query of the type `DELETE * from TableName` ). The usage of kwargs
is the same as in the get function.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
"""
return MySQLWrapper.delete(self, params, meta)
## Extended SQL methods ######################################################
def addOrModify(self, params, meta):
"""
Using the PrimaryKeys of the table, it looks for the record in the database.
If it is there, it is updated, if not, it is inserted as a new entry.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
"""
selectQuery = self.select(params, meta)
if not selectQuery["OK"]:
return selectQuery
isUpdate = False
if selectQuery["Value"]:
# Pseudo - code
# for all column not being PrimaryKey and not a time column:
# if one or more column different than params if not None:
# we update dateTime as well
columns = selectQuery["Columns"]
values = selectQuery["Value"]
if len(values) != 1:
return S_ERROR("More than one value returned on addOrModify, please report !!")
selectDict = dict(zip(columns, values[0]))
newDateEffective = None
for key, value in params.items():
if key in ("lastCheckTime", "dateEffective"):
continue
if value is None:
continue
if value != selectDict[key[0].upper() + key[1:]]:
newDateEffective = datetime.utcnow().replace(microsecond=0)
break
if "dateEffective" in params:
params["dateEffective"] = newDateEffective
userQuery = self.update(params, meta)
isUpdate = True
else:
userQuery = self.insert(params, meta)
# This part only applies to PolicyResult table
logResult = self._logRecord(params, meta, isUpdate)
if not logResult["OK"]:
return logResult
return userQuery
# FIXME: this method looks unused. Maybe can be removed from the code.
def addIfNotThere(self, params, meta):
"""
Using the PrimaryKeys of the table, it looks for the record in the database.
If it is not there, it is inserted as a new entry.
:Parameters:
**params** - `dict`
arguments for the mysql query ( must match table columns ! ).
**meta** - `dict`
metadata for the mysql query. It must contain, at least, `table` key
with the proper table name.
:return: S_OK() || S_ERROR()
"""
selectQuery = self.select(params, meta)
if not selectQuery["OK"]:
return selectQuery
if selectQuery["Value"]:
return selectQuery
return self.insert(params, meta)
## Auxiliar methods ##########################################################
def getTable(self, tableName):
"""
Returns a table dictionary description given its name
"""
if tableName in self._tableDict:
return S_OK(self._tableDict[tableName])
return S_ERROR("%s is not on the schema" % tableName)
def getTablesList(self):
"""
Returns a list of the table names in the schema.
"""
return S_OK(self._tableDict.keys())
## Protected methods #########################################################
def _checkTable(self):
"""
Method used by database tools to write the schema
"""
return self.__createTables()
def _logRecord(self, params, meta, isUpdate):
"""
Method that records every change on a LogTable.
"""
if not ("table" in meta and meta["table"] == "PolicyResult"):
return S_OK()
if isUpdate:
# This looks little bit like a non-sense. If we were updating, we may have
# not passed a complete set of parameters, so we have to get all them from the
# database :/. It costs us one more query.
updateRes = self.select(params, meta)
if not updateRes["OK"]:
return updateRes
params = dict(zip(updateRes["Columns"], updateRes["Value"][0]))
# Writes to PolicyResult"Log"
meta["table"] += "Log"
logRes = self.insert(params, meta)
return logRes
## Private methods ###########################################################
def __createTables(self, tableName=None):
"""
Writes the schema in the database. If no tableName is given, all tables
are written in the database. If a table is already in the schema, it is
skipped to avoid problems trying to create a table that already exists.
"""
# Horrible SQL here !!
tablesCreatedRes = self.database._query("show tables")
if not tablesCreatedRes["OK"]:
return tablesCreatedRes
tablesCreated = [tableCreated[0] for tableCreated in tablesCreatedRes["Value"]]
tables = {}
if tableName is None:
tables.update(self._tableDict)
elif tableName in self._tableDict:
tables = {tableName: self._tableDict[tableName]}
else:
return S_ERROR('"%s" is not a known table' % tableName)
for tableName in tablesCreated:
if tableName in tables:
del tables[tableName]
res = self.database._createTables(tables)
if not res["OK"]:
return res
# Human readable S_OK message
if res["Value"] == 0:
res["Value"] = "No tables created"
else:
res["Value"] = "Tables created: %s" % (",".join(tables.keys()))
return res
def __generateTables(self):
"""
Method used to transform the class variables into instance variables,
for safety reasons.
"""
# Avoids copying object.
tables = {}
tables.update(self._tablesDB)
for tableName, tableLike in self._likeToTable.items():
tables[tableName] = self._tablesLike[tableLike]
return tables
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}
