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 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 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(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
