def delete(self, template):
"""
_delete_
"""
# get template information
tableName = template.__class__.tableName
# get matching information from template
fields = self.getFields(template)
listOfFields = ' and '.join(['%s=%s' % (key, value)
for key, value in fields
])
# check for general query for all objects
if listOfFields != "":
listOfFields = " where " + listOfFields
# prepare query
query = 'delete from ' + tableName + \
' ' + listOfFields
# execute query
try:
rows = self.session.modify(query)
except Exception, msg:
raise DbError(msg)
def insert(self, obj):
"""
_insert_
Uses default values for non specified parameters. Note that all
parameters can be default, a useful method to book an ID.
"""
# check for valid type insertion
#if type(obj) not in self.__class__.validObjects:
# raise DbError("insertJob: cannot insert an object of type %s." % \
# str(type(obj)))
# get field information
fields = self.getFields(obj)
fieldList = ','.join([x[0] for x in fields])
valueList = ','.join([x[1] for x in fields])
# prepare query
query = 'insert into ' + obj.tableName + '(' + fieldList + ') ' + \
'values(' + valueList + ')'
# execute query
try:
rows = self.session.modify(query)
except Exception, msg:
raise DbError(msg)
def update(self, template, skipAttributes = None):
"""
_update_
"""
# get template information
tableName = template.__class__.tableName
tableIndex = template.__class__.tableIndex
tableIndexRes = [ template.mapping[key]
for key in template.__class__.tableIndex ]
if skipAttributes is None :
skipAttributes = {}
# get specification for keys (if any)
keys = [(template.mapping[key], template.data[key])
for key in tableIndex
if template.data[key] is not None]
keysSpec = " and ".join(['%s="%s"' % (key, value)
for key, value in keys
])
unlikeSpec = " and ".join(['%s!="%s"' % (key, value)
for key, value in skipAttributes.iteritems()
])
if keysSpec != "" and unlikeSpec != "" :
keysSpec += ' and ' + unlikeSpec
elif unlikeSpec != "" :
keysSpec = unlikeSpec
if keysSpec != "" :
keysSpec = ' where ' + keysSpec
# define update list (does not include keys)
fields = self.getFields(template)
listOfFields = ','.join(['%s=%s' % (key, value)
for key, value in fields
if key not in tableIndexRes
])
# return if there are no fields to update
if listOfFields == "":
return 0
# prepare query
query = 'update ' + tableName + ' set ' + listOfFields + \
keysSpec
# execute query
try:
rows = self.session.modify(query)
except Exception, msg:
raise DbError(msg)
def installMySQL(self, schemaLocation):
"""
install MySQL database
"""
import getpass
from ProdCommon.Database.MysqlInstance import MysqlInstance
# ask for password (optional)
print
userName = raw_input("""
Please provide the mysql user name (typically "root") for updating the
database server (leave empty if not needed): ')
""")
if userName == '':
userName = '******'
print
passwd = getpass.getpass(
"""Please provide mysql passwd associated to this user name for
updating the database server:
""")
# define connection type
from copy import deepcopy
rootConfig = deepcopy(self.dbConfig)
rootConfig.update({
'dbName': 'mysql',
'user': userName,
'passwd': passwd
})
dbInstance = MysqlInstance(rootConfig)
session = SafeSession(dbInstance=dbInstance)
# check if db exists
create = True
query = "show databases like '" + self.dbConfig['dbName'] + "'"
try:
session.execute(query)
session.commit()
results = session.fetchall()
if results[0][0] == self.dbConfig['dbName']:
print "DB ", self.dbConfig['dbName'], "already exists."
print "Installing just BossLite tables.\n"
create = False
except IndexError:
pass
except Exception, msg:
session.close()
raise DbError(str(msg))
def getTaskFromJob(self, job):
"""
retrieve Task object from Job object and perform association
"""
# db connect
if self.db is None:
self.connect()
# creating task
task = self.loadTask(job['taskId'], None)
# perform association
task.appendJob(job)
# operation validity checks
if len(task.jobs) != 1:
raise DbError( "ERROR: too many jobs loaded %s" % \
len( task.jobs ))
if id(task.jobs[0]) != id(job):
raise DbError("Fatal ERROR: mismatching job")
# return task
return task
def distinctAttr(self, template, value_1 , value_2, alist , strict = True):
"""
_distinctAttr_
"""
# get template information
mapping = template.__class__.fields.items()
tableName = template.__class__.tableName
# get field mapping in order
fieldMapping = [(key, value) for key, value in mapping]
# objectFields = [key[0] for key in fieldMapping]
# dbFields = [key[1] for key in fieldMapping]
#DanieleS
for key, val in fieldMapping:
if key == value_1:
dbFields = [val]
objectFields = [key]
if key == value_2:
field = val
# break
# get matching information from template
# fields = self.getFields(template)
# determine if comparison is strict or not
if strict:
operator = '='
else:
operator = ' like '
listOfFields = ' or '.join([('%s'+ operator +'%s') % (field, value)
for value in alist
])
# check for general query for all objects
if listOfFields != "":
listOfFields = " where " + listOfFields
# DanieleS.
# prepare query
query = 'select distinct (' + ', '.join(dbFields) + ') from ' + tableName + \
' ' + listOfFields
# execute query
results = None
theList = []
try:
results = self.session.select(query)
except Exception, msg:
raise DbError(msg)
def selectDistinct(self, template, distinctAttr, strict = True):
"""
_select_
"""
# get template information
mapping = template.__class__.fields.items()
tableName = template.__class__.tableName
# get field mapping in order
fieldMapping = [(key, value) for key, value in mapping]
objectFields = [key[0] for key in fieldMapping]
distFields = [key[1] for key in fieldMapping if key[0] in distinctAttr]
# get matching information from template
fields = self.getFields(template)
# determine if comparison is strict or not
if strict:
operator = '='
else:
operator = ' like '
listOfFields = ' and '.join([('%s'+ operator +'%s') % (key, value)
for key, value in fields
])
# check for general query for all objects
if listOfFields != "":
listOfFields = " where " + listOfFields
# prepare query
query = 'select distinct (' + ', '.join(distFields) + ')' + \
' from ' + tableName + \
' ' + listOfFields
# execute query
results = None
theList = []
try:
results = self.session.select(query)
except Exception, msg:
raise DbError(msg)
def installSQlite(self, schemaLocation):
"""
install SQLite database
"""
# create a session and db access
session = SafeSession(dbInstance=self.dbInstance)
# execute check query
query = "select tbl_name from sqlite_master where tbl_name='bl_task'"
try:
# if bl_task exists, no further operations are needed
session.execute(query)
results = session.fetchall()
if results[0][0] == self.dbConfig['dbName']:
print "DB ", self.dbConfig['dbName'], "already exists.\n"
return
session.close()
return
except IndexError:
pass
except StandardError:
pass
try:
# if bl_task exists, no further operations are needed
session.execute("select count(*) from bl_task")
session.close()
return
except StandardError:
pass
# execute query
queries = open(schemaLocation).read()
try:
for query in queries.split(';'):
session.execute(query)
except Exception, msg:
raise DbError(str(msg))
class BossLiteDB(object):
"""
High level API class for DB queries.
It allows load/operate/update DB using free format queries
"""
dbConfig = {
'dbName': 'BossLiteDB',
'user': '******',
'passwd': 'BossLitePass',
'socketFileLocation': '',
'host': '',
'portNr': '',
'refreshPeriod': 4 * 3600,
'maxConnectionAttempts': 5,
'dbWaitingTime': 10
}
def __init__(self, database, dbConfig):
"""
initialize the API instance
- database can be both MySQl or SQLite
- dbConfig can be a dictionary with the format
{'dbName':'BossLiteDB',
'host':'localhost',
'user':'******',
'passwd':'BossLitePass',
'socketFileLocation':'/var/run/mysql/mysql.sock',
'portNr':'',
'refreshPeriod' : 4*3600 ,
'maxConnectionAttempts' : 5,
'dbWaitingTime' : 10
}
"""
# database
self.database = database # "MySQL" or "SQLite"
# MySQL: get DB configuration from config file
if self.database == "MySQL":
# update db config
self.dbConfig['socketFileLocation'] = expandvars(
self.dbConfig['socketFileLocation'])
self.dbConfig.update(dbConfig)
# create DB instance
from ProdCommon.Database.MysqlInstance import MysqlInstance
self.dbInstance = MysqlInstance(self.dbConfig)
else:
# update db config
self.dbConfig = {'dbName': 'BossLiteDB'}
dbConfig['dbName'] = expandvars(dbConfig['dbName'])
self.dbConfig.update(dbConfig)
# create DB instance
from ProdCommon.Database.SqliteInstance import SqliteInstance
self.dbInstance = SqliteInstance(self.dbConfig)
# create a session and db access
self.session = None
##########################################################################
def connect(self):
"""
recreate a session and db access
"""
# create a session and db access
if self.session is None:
self.session = SafeSession(dbInstance=self.dbInstance)
##########################################################################
def close(self):
"""
close session and db access
"""
self.session.close()
self.session = None
##########################################################################
def reset(self):
"""
reset session and db access
"""
self.close()
self.connect()
##########################################################################
def commit(self):
"""
commit
"""
self.session.commit()
##########################################################################
def select(self, query):
"""
execute a query.
"""
# db connect
self.connect()
self.session.execute(query)
# return query results
return self.session.fetchall()
##########################################################################
def selectOne(self, query):
"""
execute a query.with only one result expected
"""
# db connect
self.connect()
self.session.execute(query)
# return query results
return self.session.fetchone()[0]
##########################################################################
def modify(self, query):
"""
execute a query which does not return such as insert/update/delete
"""
# db connect
self.connect()
# return query results
rows = self.session.execute(query)
if self.database == "MySQL":
self.session.commit()
return rows
##########################################################################
def updateDB(self, obj):
"""
update any object table in the DB
works for tasks, jobs, runningJobs
"""
# db connect
self.connect()
# update
obj.update(self.session)
if self.database == "MySQL":
self.session.commit()
##########################################################################
def installDB(self, schemaLocation):
"""
install database
"""
schemaLocation = expandvars(schemaLocation)
if self.database == "MySQL":
self.installMySQL(schemaLocation)
elif self.database == "SQLite":
self.installSQlite(schemaLocation)
else:
raise NotImplementedError
##########################################################################
def installMySQL(self, schemaLocation):
"""
install MySQL database
"""
import getpass
from ProdCommon.Database.MysqlInstance import MysqlInstance
# ask for password (optional)
print
userName = raw_input("""
Please provide the mysql user name (typically "root") for updating the
database server (leave empty if not needed): ')
""")
if userName == '':
userName = '******'
print
passwd = getpass.getpass(
"""Please provide mysql passwd associated to this user name for
updating the database server:
""")
# define connection type
from copy import deepcopy
rootConfig = deepcopy(self.dbConfig)
rootConfig.update({
'dbName': 'mysql',
'user': userName,
'passwd': passwd
})
dbInstance = MysqlInstance(rootConfig)
session = SafeSession(dbInstance=dbInstance)
# check if db exists
create = True
query = "show databases like '" + self.dbConfig['dbName'] + "'"
try:
session.execute(query)
session.commit()
results = session.fetchall()
if results[0][0] == self.dbConfig['dbName']:
print "DB ", self.dbConfig['dbName'], "already exists."
print "Installing just BossLite tables.\n"
create = False
except IndexError:
pass
except Exception, msg:
session.close()
raise DbError(str(msg))
# create db
if create:
query = 'create database ' + self.dbConfig['dbName']
try:
session.execute(query)
session.commit()
except Exception, msg:
session.close()
raise DbError(str(msg))
session.close()
raise DbError(str(msg))
# create tables
queries = open(schemaLocation).read()
try:
session.execute('use ' + self.dbConfig['dbName'])
session.commit()
for query in queries.split(';'):
if query.strip() == '':
continue
session.execute(query)
session.commit()
except Exception, msg:
session.close()
raise DbError(str(msg))
# grant user
query = 'GRANT UPDATE,SELECT,DELETE,INSERT ON ' + \
self.dbConfig['dbName'] + '.* TO \'' + \
self.dbConfig['user'] + '\'@\'' + self.dbConfig['host'] \
+ '\' IDENTIFIED BY \'' + self.dbConfig['passwd'] + '\';'
try:
session.execute(query)
session.commit()
except Exception, msg:
session.close()
raise DbError(str(msg))
# close session
session.close()
def selectJoin(self, template, jTemplate, jMap=None, less=None, more=None, options=None):
"""
_selectJoin_
select from template and jTemplate, using join condition from jMap
"""
# evaluate options
opt = { 'strict' : True,
'jType' : '',
'limit' : None,
'offset' : None,
'inList' : None }
if options is not None :
opt.update( options )
if more is None :
more = {}
if less is None :
less = {}
# get template information
dbMap = template.__class__.fields
mapping = template.__class__.fields.items()
tableName = template.__class__.tableName
# get template information
jDbMap = jTemplate.__class__.fields
jMapping = jTemplate.__class__.fields.items()
jTableName = jTemplate.__class__.tableName
# get field mapping in order
fieldMapping = [(key, value) for key, value in mapping]
objectFields = [key[0] for key in fieldMapping]
dbFields = [key[1] for key in fieldMapping]
# get field mapping in order for join table
jFieldMapping = [(key, value) for key, value in jMapping]
jObjectFields = [key[0] for key in jFieldMapping]
jDbFields = [key[1] for key in jFieldMapping]
# get matching information from template
fields = self.getFields(template)
# get matching information from join template
jFields = self.getFields(jTemplate)
# evaluate eventual lists
if opt['inList'] is None :
listOfFields = ''
else :
for key in opt['inList'].keys() :
k = template.__class__.fields[key]
listOfFields = 't1.' + k + ' in (' + \
','.join( str(val ) for val in opt['inList'][key]) + ') '
if listOfFields != "" :
listOfFields += ' and '
# determine if comparison is strict or not
if opt['strict']:
operator = '='
else:
operator = ' like '
# is there a set of field for more/less comparison?
listOfFields += ' and '.join([('t1.%s'+ operator +'%s') % (key, value)
for key, value in fields
if key not in more and key not in less
])
jListOfFields = ' and '.join([('t2.%s'+ operator +'%s') \
% (key, value)
for key, value in jFields
if key not in more and key not in less
])
# check for general query for all objects
if listOfFields != "" and jListOfFields != "":
listOfFields = " where " + listOfFields + " and " + jListOfFields
elif listOfFields != "":
listOfFields = " where " + listOfFields
elif jListOfFields != "":
listOfFields = " where " + jListOfFields
# evaluate more
for key, val in more.iteritems():
print key, jDbMap[key], val
if key in objectFields :
listOfFields += ' and t1.%s>%s ' % ( dbMap[key], val )
elif key in jObjectFields :
listOfFields += ' and t2.%s>%s ' % ( jDbMap[key], val )
# evaluate less
for key, val in less.iteritems():
if key in objectFields :
listOfFields += ' and t1.%s<%s ' % ( dbMap[key], val )
elif key in jObjectFields :
listOfFields += ' and t2.%s<%s ' % ( jDbMap[key], val )
# evaluate join conditions
jLFields = ''
if jMap is not None :
jLFields = ' and '.join([('t1.%s=t2.%s') % ( \
template.__class__.fields[key], \
jTemplate.__class__.fields[value])
for key, value in jMap.iteritems()
])
if jLFields != '':
jLFields = ' on (' + jLFields + ') '
# what kind of join?
if opt['jType'] == '' :
qJoin = ' inner join '
elif opt['jType'] == 'left' :
qJoin = ' left join '
elif opt['jType'] == 'right' :
qJoin = ' right join '
# prepare query
query = 'select ' + ', '.join( ['t1.'+ key for key in dbFields] ) + \
', ' + ', '.join( ['t2.'+ key for key in jDbFields] ) + \
' from ' + tableName + ' t1 ' + qJoin + \
jTableName + ' t2 ' + jLFields + listOfFields
# limit?
if opt['limit'] is not None :
if opt['offset'] is None or int(opt['offset']) == 0 :
query += ' limit %s' % opt['limit']
else :
query += ' limit %s,%s' % (opt['offset'], opt['limit'])
# execute query
results = None
theList = []
try:
results = self.session.select(query)
except Exception, msg:
raise DbError(msg)