def retrievemuniconv4install(self, name, ctypeproptypetmpltname, ctypeproptmpltdesc):
'''
Get magnet unit conversion information for given field name with component type property template name and its description.
This method retrieve a common information for given magnet type.
'''
sql = '''
select install.field_name, install.location, inventory.serial_no, cmpnt_type.cmpnt_type_name, cmpnt_type_prop.cmpnt_type_prop_value
from install
left join inventory__install on inventory__install.install_id = install.install_id
left join inventory on inventory__install.inventory_id = inventory.inventory_id
left join cmpnt_type on install.cmpnt_type_id = cmpnt_type.cmpnt_type_id
left join cmpnt_type_prop on cmpnt_type_prop.cmpnt_type_id = cmpnt_type.cmpnt_type_id
left join cmpnt_type_prop_type on cmpnt_type_prop.cmpnt_type_prop_type_id = cmpnt_type_prop_type.cmpnt_type_prop_type_id
where
install.field_name like %s
and cmpnt_type_prop_type.cmpnt_type_prop_type_name like %s
and cmpnt_type_prop_type.cmpnt_type_prop_type_desc like %s
'''
name = _wildcardformat(name)
ctypeproptypetmpltname = _wildcardformat(ctypeproptypetmpltname)
ctypeproptmpltdesc = _wildcardformat(ctypeproptmpltdesc)
try:
cur = self.conn.cursor()
cur.execute(sql, (name, ctypeproptypetmpltname, ctypeproptmpltdesc))
res = cur.fetchone()
except MySQLdb.Error as e:
self.logger.info('Error when fetching magnet unit conversion information for install:\n%s (%d)' %(e.args[1], e.args[0]))
raise Exception('Error when fetching magnet unit conversion information for install:\n%s (%d)' %(e.args[1], e.args[0]))
return res
def retrievemuniconv4inventory(self, invid, invproptmpltname,
invproptmpltdesc):
'''
Get magnet unit conversion information for given inventory id with inventory property template name and its description.
'''
sql = '''
select install.field_name, install.location, inventory.serial_no, cmpnt_type.cmpnt_type_name, inventory_prop.inventory_prop_value
from inventory
left join inventory__install on inventory__install.inventory_id = inventory.inventory_id
left join install on inventory__install.install_id = install.install_id
left join inventory_prop on inventory_prop.inventory_id = inventory.inventory_id
left join inventory_prop_tmplt on inventory_prop_tmplt.inventory_prop_tmplt_id = inventory_prop.inventory_prop_tmplt_id
left join cmpnt_type on inventory.cmpnt_type_id = cmpnt_type.cmpnt_type_id
where
inventory.inventory_id = %s
and inventory_prop_tmplt.inventory_prop_tmplt_name like %s
and inventory_prop_tmplt.inventory_prop_tmplt_desc like %s
'''
invproptmpltname = _wildcardformat(invproptmpltname)
invproptmpltdesc = _wildcardformat(invproptmpltdesc)
try:
cur = self.conn.cursor()
cur.execute(sql, (invid, invproptmpltname, invproptmpltdesc))
res = cur.fetchone()
except MySQLdb.Error as e:
self.logger.info(
'Error when fetching magnet unit conversion information for inventory:\n%s (%d)'
% (e.args[1], e.args[0]))
raise Exception(
'Error when fetching magnet unit conversion information for inventory:\n%s (%d)'
% (e.args[1], e.args[0]))
return res
def retrievemuniconv4inventory(self, invid, invproptmpltname, invproptmpltdesc):
'''
Get magnet unit conversion information for given inventory id with inventory property template name and its description.
'''
sql = '''
select install.field_name, install.location, inventory.serial_no, cmpnt_type.cmpnt_type_name, inventory_prop.inventory_prop_value
from inventory
left join inventory__install on inventory__install.inventory_id = inventory.inventory_id
left join install on inventory__install.install_id = install.install_id
left join inventory_prop on inventory_prop.inventory_id = inventory.inventory_id
left join inventory_prop_tmplt on inventory_prop_tmplt.inventory_prop_tmplt_id = inventory_prop.inventory_prop_tmplt_id
left join cmpnt_type on inventory.cmpnt_type_id = cmpnt_type.cmpnt_type_id
where
inventory.inventory_id = %s
and inventory_prop_tmplt.inventory_prop_tmplt_name like %s
and inventory_prop_tmplt.inventory_prop_tmplt_desc like %s
'''
invproptmpltname = _wildcardformat(invproptmpltname)
invproptmpltdesc = _wildcardformat(invproptmpltdesc)
try:
cur = self.conn.cursor()
cur.execute(sql, (invid, invproptmpltname, invproptmpltdesc))
res = cur.fetchone()
except MySQLdb.Error as e:
self.logger.info('Error when fetching magnet unit conversion information for inventory:\n%s (%d)' %(e.args[1], e.args[0]))
raise Exception('Error when fetching magnet unit conversion information for inventory:\n%s (%d)' %(e.args[1], e.args[0]))
return res
def retrievecmpnttype(self, name, desc=None, vendor=None):
'''Retrieve id of a given component type name, description [optional], and vendor [optional].
Wildcards are support in component type name search, which uses "*" for multiple match,
and "?" for single character match.
Return: tuple of component ((id, name, description, vendor, vendor id), ...) if vendor is provide,
otherwise ((id, name, description), ...).
'''
if vendor:
# retrieve also vendor information
sql = '''
select
ctype.cmpnt_type_id, ctype.cmpnt_type_name, ctype.description, vendor.vendor_name, vendor.vendor_id
from cmpnt_type ctype
left join cmpnttype__vendor ctvendor on ctvendor.cmpnt_type_id = ctype.cmpnt_type_id
left join vendor on ctvendor.vendor_id = vendor.vendor_id
where
ctype.cmpnt_type_name like %s
'''
else:
# ignore vendor information since vendor is not provided.
sql = '''
select
ctype.cmpnt_type_id, ctype.cmpnt_type_name, ctype.description
from cmpnt_type ctype
where
ctype.cmpnt_type_name like %s
'''
name = _wildcardformat(name)
try:
cur = self.conn.cursor()
if desc and vendor:
desc = _wildcardformat(desc)
vendor = _wildcardformat(vendor)
sql += " and ctype.description like %s and vendor.vendor_name like %s "
cur.execute(sql, (name, desc, vendor))
elif desc:
desc = _wildcardformat(desc)
sql += " and ctype.description like %s "
cur.execute(sql, (name, desc))
elif vendor:
vendor = _wildcardformat(vendor)
sql += " and vendor.vendor_name like %s "
cur.execute(sql, (name, vendor))
else:
cur.execute(sql, (name, ))
res = cur.fetchall()
except MySQLdb.Error, e:
self.logger.info('Error when fetching component types:\n%s (%d)' %
(e.args[1], e.args[0]))
raise Exception('Error when fetching component types:\n%s (%d)' %
(e.args[1], e.args[0]))
def retrievecmpnttype(self, name, desc=None, vendor=None):
'''Retrieve id of a given component type name, description [optional], and vendor [optional].
Wildcards are support in component type name search, which uses "*" for multiple match,
and "?" for single character match.
Return: tuple of component ((id, name, description, vendor, vendor id), ...) if vendor is provide,
otherwise ((id, name, description), ...).
'''
if vendor:
# retrieve also vendor information
sql = '''
select
ctype.cmpnt_type_id, ctype.cmpnt_type_name, ctype.description, vendor.vendor_name, vendor.vendor_id
from cmpnt_type ctype
left join cmpnttype__vendor ctvendor on ctvendor.cmpnt_type_id = ctype.cmpnt_type_id
left join vendor on ctvendor.vendor_id = vendor.vendor_id
where
ctype.cmpnt_type_name like %s
'''
else:
# ignore vendor information since vendor is not provided.
sql = '''
select
ctype.cmpnt_type_id, ctype.cmpnt_type_name, ctype.description
from cmpnt_type ctype
where
ctype.cmpnt_type_name like %s
'''
name = _wildcardformat(name)
try:
cur = self.conn.cursor()
if desc and vendor:
desc = _wildcardformat(desc)
vendor = _wildcardformat(vendor)
sql += " and ctype.description like %s and vendor.vendor_name like %s "
cur.execute(sql, (name, desc, vendor))
elif desc:
desc = _wildcardformat(desc)
sql += " and ctype.description like %s "
cur.execute(sql, (name, desc))
elif vendor:
vendor = _wildcardformat(vendor)
sql += " and vendor.vendor_name like %s "
cur.execute(sql, (name, vendor))
else:
cur.execute(sql, (name,))
res = cur.fetchall()
except MySQLdb.Error, e:
self.logger.info('Error when fetching component types:\n%s (%d)' % (e.args[1], e.args[0]))
raise Exception('Error when fetching component types:\n%s (%d)' % (e.args[1], e.args[0]))
def retrievecmpnttypeprop(self, ctypeid, cptypeid, value=None):
'''Retrieve value and id of given component type id and component type property type id.
Any property belonging to component type and shared by all inventory instances is save in the component type property table.
The type name of that property is defined in the component type property type table.
Return: tuple of component type property value and id ((component property id, property value)).
'''
sql = '''
select cmpnt_type_prop_id, cmpnt_type_prop_value
from cmpnt_type_prop
where
cmpnt_type_id = %s and cmpnt_type_prop_type_id = %s
'''
try:
cur = self.conn.cursor()
if value:
value = _wildcardformat(value)
if "%" in value or "_" in value:
sql += " and cmpnt_type_prop_value like %s "
else:
sql += " and cmpnt_type_prop_value = %s "
cur.execute(sql, (ctypeid, cptypeid, value))
else:
cur.execute(sql, (ctypeid, cptypeid))
res = cur.fetchall()
except MySQLdb.Error, e:
self.logger.info(
"Error when fetching component type property value:\n%s (%d)" %
(e.args[1], e.args[0]))
raise Exception(
"Error when fetching component type property value:\n%s (%d)" %
(e.args[1], e.args[0]))
def retrieveinventoryprop(self, inventoryid, iproptmpltid, value=None):
'''Retrieve id and value from inventory property table with given inventory id and inventory property template id.
An inventory property has to belong to a property template, which belongs to component type.
Use component type property table to retrieve a property, which is common for a component type.
Return: tuple of inventory property id, inventory property value, property template id and inventory_id,
((property id, inventory property value, property template id, inventory_id), ...).
'''
sql = '''
select
inventory_prop_id, inventory_prop_value, inventory_prop_tmplt_id, inventory_id
from inventory_prop
where
inventory_id = %s and inventory_prop_tmplt_id = %s
'''
values = [inventoryid, iproptmpltid]
if value:
value = _wildcardformat(value)
if "%" in value or "_" in value:
sql += " value like %s "
else:
sql += " value = %s "
values.append(value)
try:
cur = self.conn.cursor()
cur.execute(sql, values)
res = cur.fetchall()
except MySQLdb.Error as e:
self.logger.info('Error when retrieve id and vale from inventory property table:\n%s (%s)' %(e.args[1], e.args[0]))
raise Exception('Error when retrieve id and vale from inventory property table:\n%s (%s)' %(e.args[1], e.args[0]))
return res
def retrievecmpnttypeprop(self, ctypeid, cptypeid, value=None):
'''Retrieve value and id of given component type id and component type property type id.
Any property belonging to component type and shared by all inventory instances is save in the component type property table.
The type name of that property is defined in the component type property type table.
Return: tuple of component type property value and id ((component property id, property value)).
'''
sql = '''
select cmpnt_type_prop_id, cmpnt_type_prop_value
from cmpnt_type_prop
where
cmpnt_type_id = %s and cmpnt_type_prop_type_id = %s
'''
try:
cur = self.conn.cursor()
if value:
value = _wildcardformat(value)
if "%" in value or "_" in value:
sql += " and cmpnt_type_prop_value like %s "
else:
sql += " and cmpnt_type_prop_value = %s "
cur.execute(sql, (ctypeid, cptypeid, value))
else:
cur.execute(sql, (ctypeid, cptypeid))
res = cur.fetchall()
except MySQLdb.Error, e:
self.logger.info("Error when fetching component type property value:\n%s (%d)" %(e.args[1], e.args[0]))
raise Exception("Error when fetching component type property value:\n%s (%d)" %(e.args[1], e.args[0]))
def retrievecmpnttypeproptype(self, name, desc=None):
'''Retrieve id of a given component type property type name, and description [optional]
Wildcards are support in property type name search, which uses "*" for multiple match,
and "?" for single character match.
Return: tuple of property type of component type ((id, name, description), ...).
'''
if not isinstance(name, (str, unicode)):
raise Exception('Component type property type name has to be a string.')
sql = '''
select
cmpnt_type_prop_type_id, cmpnt_type_prop_type_name, cmpnt_type_prop_type_desc
from cmpnt_type_prop_type
where
cmpnt_type_prop_type_name like %s
'''
name = _wildcardformat(name)
try:
cur = self.conn.cursor()
if desc:
sql += " and cmpnt_type_prop_type_desc like %s "
cur.execute(sql, (name, desc))
else:
cur.execute(sql, (name,))
res = cur.fetchall()
except MySQLdb.Error, e:
self.logger.into('Error when fetching component type property types:\n%s (%d)' % (e.args[1], e.args[0]))
raise Exception('Error when fetching component type property types:\n%s (%d)' % (e.args[1], e.args[0]))
def retrievegoldenmodel(self, name, status=0, ignorestatus=False):
"""
Retrieve golden model with given name and other conditions
parameters:
name: model name
status: 0: current golden model [by default]
1: alternative golden model
2: previous golden models, but not any more
ignorestatus: get golden model no matter its status
"""
name = _wildcardformat(name)
sql = """
select gold_model_id, model_name,
gm.created_by, gm.create_date,
gm.updated_by, gm.update_date,
gm.gold_status_ind, gm.model_id
from gold_model gm
left join model on model.model_id = gm.model_id
where
model.model_name like %s
"""
try:
cur = self.conn.cursor()
if ignorestatus:
cur.execute(sql, (name,))
else:
cur.execute(sql + """ and gm.gold_status_ind like %s""", (name, status))
res = cur.fetchall()
except MySQLdb.Error as e:
self.logger.info("Error when retrieving golden model:\n%s (%d)" % (e.args[1], e.args[0]))
raise Exception("Error when retrieving golden model:\n%s (%d)" % (e.args[1], e.args[0]))
return res
def retrievemodelcodeinfo(self, codename, algorithm):
'''
retrieve model code information with given name and algorithm
Wildcasts are supported for search in code name and algorithm.
* for multiple characters matching
? for single character matching
return tuple of model code id, model code name, and algorithm
'''
sql = '''
select model_code_id, code_name, algorithm
from model_code
where
'''
if "*" in codename or "?" in codename:
sql += 'code_name like %s '
codename = _wildcardformat(codename)
else:
sql += 'code_name = %s '
if algorithm:
if algorithm == "*":
pass
elif "*" in algorithm or "?" in algorithm:
sql += 'and algorithm like %s'
algorithm = _wildcardformat(algorithm)
else:
sql += 'and algorithm = %s'
else:
sql += ' and algorithm is NULL'
try:
cur = self.conn.cursor()
if algorithm == None or algorithm == "*":
cur.execute(sql, (codename, ))
else:
cur.execute(sql, (codename, algorithm))
res = cur.fetchall()
except MySQLdb.Error as e:
self.logger.info(
'Error when retrieving model code info:\n%s (%d)' %
(e.args[1], e.args[0]))
raise Exception('Error when retrieving model code info:\n%s (%d)' %
(e.args[1], e.args[0]))
return res
def retrieveinventory(self, serial, ctypename=None, vendor=None):
'''Retrieve an inventory information according given serial number, vendor name, and component type name.
Wildcards are support in all parameters (device name, serial number, component type name, and vendor),
which uses "*" for multiple match, and "?" for single character match.
return: tuple of inventory id, serial number like:
with vendor name, component type name, and component type description if both component type and vendor are given
like ((inventory id, serial no, component type name, type description, vendor), ...)
'''
if not isinstance(serial, (str, unicode)):
raise Exception('Serial no has to be string.')
serial = _wildcardformat(serial)
sql = '''
select install.install_id, inv.inventory_id, install.field_name, install.location,
inv.serial_no,
ctype.cmpnt_type_name, ctype.description,
vendor.vendor_name
from inventory inv
left join vendor on vendor.vendor_id = inv.vendor_id
left join cmpnt_type ctype on ctype.cmpnt_type_id = inv.cmpnt_type_id
left join inventory__install on inventory__install.inventory_id = inv.inventory_id
left join install on inventory__install.install_id = install.install_id
where
'''
vals = []
try:
vals, sql = _assemblesql(sql, serial, " inv.serial_no ", vals)
if ctypename:
vals, sql = _assemblesql(sql,
ctypename,
" ctype.cmpnt_type_name ",
vals,
connector="and")
if vendor:
vals, sql = _assemblesql(sql,
vendor,
" vendor.vendor_name ",
vals,
connector="and")
cur = self.conn.cursor()
cur.execute(sql, vals)
res = cur.fetchall()
except MySQLdb.Error as e:
self.logger.info(
'Error when fetching inventory information for a given serial no (%s):\n%s (%b)'
% (serial, e.args[1], e.args[0]))
raise Exception(
'Error when fetching inventory information for a given serial no (%s):\n%s (%b)'
% (serial, e.args[1], e.args[0]))
return res
def retrievemuniconv4inventory(self, invid, invproptmpltname, invproptmpltdesc):
'''
Get magnet unit conversion information for given inventory id with inventory property template name and its description.
'''
sql = '''
select install.field_name, install.location, inventory.serial_no, cmpnt_type.cmpnt_type_name, inventory_prop.inventory_prop_value
from inventory
left join inventory__install on inventory__install.inventory_id = inventory.inventory_id
left join install on inventory__install.install_id = install.install_id
left join inventory_prop on inventory_prop.inventory_id = inventory.inventory_id
left join inventory_prop_tmplt on inventory_prop_tmplt.inventory_prop_tmplt_id = inventory_prop.inventory_prop_tmplt_id
left join cmpnt_type on inventory.cmpnt_type_id = cmpnt_type.cmpnt_type_id
where
inventory.inventory_id = %s
and inventory_prop_tmplt.inventory_prop_tmplt_name like %s
and inventory_prop_tmplt.inventory_prop_tmplt_desc like %s
'''
invproptmpltname = _wildcardformat(invproptmpltname)
invproptmpltdesc = _wildcardformat(invproptmpltdesc)
try:
cur = self.conn.cursor()
cur.execute(sql, (invid, invproptmpltname, invproptmpltdesc))
res = cur.fetchone()
# Retrieve MD
if len(res) >= 1:
md = self.retrieveMeasurementData(invid, res[3], True)
invValue = json.loads(res[4])
for munType in invValue:
if(md['type'] > 0):
invValue[munType]['md'] = md['data']
res = (res[0], res[1], res[2], res[3], json.dumps(invValue))
except Exception as e:
self.logger.info('Error when fetching magnet unit conversion information for inventory:\n%s (%d)' % (e.args[1], e.args[0]))
raise Exception('Error when fetching magnet unit conversion information for inventory:\n%s (%d)' % (e.args[1], e.args[0]))
return res
def retrievemodelcodeinfo(self, codename, algorithm):
"""
retrieve model code information with given name and algorithm
Wildcasts are supported for search in code name and algorithm.
* for multiple characters matching
? for single character matching
return tuple of model code id, model code name, and algorithm
"""
sql = """
select model_code_id, code_name, algorithm
from model_code
where
"""
if "*" in codename or "?" in codename:
sql += "code_name like %s "
codename = _wildcardformat(codename)
else:
sql += "code_name = %s "
if algorithm:
if algorithm == "*":
pass
elif "*" in algorithm or "?" in algorithm:
sql += "and algorithm like %s"
algorithm = _wildcardformat(algorithm)
else:
sql += "and algorithm = %s"
else:
sql += " and algorithm is NULL"
try:
cur = self.conn.cursor()
if algorithm == None or algorithm == "*":
cur.execute(sql, (codename,))
else:
cur.execute(sql, (codename, algorithm))
res = cur.fetchall()
except MySQLdb.Error as e:
self.logger.info("Error when retrieving model code info:\n%s (%d)" % (e.args[1], e.args[0]))
raise Exception("Error when retrieving model code info:\n%s (%d)" % (e.args[1], e.args[0]))
return res
def retrievemuniconvbycmpnttype4inventory(self, invid,
ctypeproptypetmpltname,
ctypeproptmpltdesc):
'''
Get magnet unit conversion information for given inventory id with component type property template name and its description.
This method retrieve a common information for given magnet type.
Use this method only when the measurement data for each individual magnet is not available.
'''
sql = '''
select install.field_name, install.location, inventory.serial_no, cmpnt_type.cmpnt_type_name, cmpnt_type_prop.cmpnt_type_prop_value
from inventory
left join inventory__install on inventory__install.inventory_id = inventory.inventory_id
left join install on inventory__install.install_id = install.install_id
left join cmpnt_type on inventory.cmpnt_type_id = cmpnt_type.cmpnt_type_id
left join cmpnt_type_prop on cmpnt_type_prop.cmpnt_type_id = cmpnt_type.cmpnt_type_id
left join cmpnt_type_prop_type on cmpnt_type_prop.cmpnt_type_prop_type_id = cmpnt_type_prop_type.cmpnt_type_prop_type_id
where
inventory.inventory_id = %s
and cmpnt_type_prop_type.cmpnt_type_prop_type_name like %s
and cmpnt_type_prop_type.cmpnt_type_prop_type_desc like %s
'''
ctypeproptypetmpltname = _wildcardformat(ctypeproptypetmpltname)
ctypeproptmpltdesc = _wildcardformat(ctypeproptmpltdesc)
try:
cur = self.conn.cursor()
cur.execute(sql,
(invid, ctypeproptypetmpltname, ctypeproptmpltdesc))
res = cur.fetchone()
except MySQLdb.Error as e:
self.logger.info(
'Error when fetching magnet unit conversion information for inventory with given component type:\n%s (%d)'
% (e.args[1], e.args[0]))
raise Exception(
'Error when fetching magnet unit conversion information for inventory with given component type:\n%s (%d)'
% (e.args[1], e.args[0]))
return res
def retrieveinventory(self, serial, ctypename=None, vendor=None):
'''
Retrieve an inventory information according given serial number, vendor name, and component type name.
Wildcards are support in all parameters (device name, serial number, component type name, and vendor),
which uses "*" for multiple match, and "?" for single character match.
return: tuple of inventory id, serial number like:
with vendor name, component type name, and component type description if both component type and vendor are given
like ((inventory id, serial no, component type name, type description, vendor), ...)
'''
if not isinstance(serial, (str, unicode)):
raise Exception('Serial no has to be string.')
serial = _wildcardformat(serial)
try:
res = self.physics.retrieveInventory(None, serial, ctypename, vendor)
result_block = ()
for r in res:
install_id = None
inventory_id = r[0]
install_name = None
location = None
serial_number = r[3]
component_type_name = r[4]
component_type_description = r[5]
vendor_name = r[6]
install = self.physics.retrieveInventoryToInstall(None, None, inventory_id)
if len(install) > 0:
installObj = install[0]
install_id = installObj[1]
install_name = installObj[3]
location = installObj[5]
result_line = (install_id, inventory_id, install_name, location, serial_number, component_type_name, component_type_description, vendor_name)
result_block += (result_line,)
return result_block
except MySQLError as e:
raise Exception(e)
def retrieveinventory(self, serial, ctypename=None, vendor=None):
'''Retrieve an inventory information according given serial number, vendor name, and component type name.
Wildcards are support in all parameters (device name, serial number, component type name, and vendor),
which uses "*" for multiple match, and "?" for single character match.
return: tuple of inventory id, serial number like:
with vendor name, component type name, and component type description if both component type and vendor are given
like ((inventory id, serial no, component type name, type description, vendor), ...)
'''
if not isinstance(serial, (str, unicode)):
raise Exception('Serial no has to be string.')
serial = _wildcardformat(serial)
sql = '''
select install.install_id, inv.inventory_id, install.field_name, install.location,
inv.serial_no,
ctype.cmpnt_type_name, ctype.description,
vendor.vendor_name
from inventory inv
left join vendor on vendor.vendor_id = inv.vendor_id
left join cmpnt_type ctype on ctype.cmpnt_type_id = inv.cmpnt_type_id
left join inventory__install on inventory__install.inventory_id = inv.inventory_id
left join install on inventory__install.install_id = install.install_id
where
'''
vals = []
try:
vals, sql = _assemblesql(sql, serial, " inv.serial_no ", vals)
if ctypename:
vals, sql = _assemblesql(sql, ctypename, " ctype.cmpnt_type_name ", vals, connector="and")
if vendor:
vals, sql = _assemblesql(sql, vendor, " vendor.vendor_name ", vals, connector="and")
cur=self.conn.cursor()
cur.execute(sql, vals)
res = cur.fetchall()
except MySQLdb.Error as e:
self.logger.info('Error when fetching inventory information for a given serial no (%s):\n%s (%b)' %(serial, e.args[1], e.args[0]))
raise Exception('Error when fetching inventory information for a given serial no (%s):\n%s (%b)' %(serial, e.args[1], e.args[0]))
return res
def retrieveinventoryproptmplt(self, name, ctypeid=None, desc=None):
'''Retrieve id of a given inventory property template name, with optional component type id or description
Wildcards are support in name search, which uses "*" for multiple match,
and "?" for single character match.
Return all inventory property template ids.
'''
if not isinstance(name, (str, unicode)):
raise Exception(
'Inventory property template name has to be a string.')
sql = '''
select
inventory_prop_tmplt_id, inventory_prop_tmplt_name, inventory_prop_tmplt_desc, inventory_prop_tmplt_default, inventory_prop_tmplt_units
from inventory_prop_tmplt
where
inventory_prop_tmplt_name like %s
'''
name = _wildcardformat(name)
value = [name]
if ctypeid:
sql += ' and cmpnt_type_id = %s '
value.append(ctypeid)
if desc:
sql += ' and inventory_prop_tmplt_desc like %s '
value.append(desc)
try:
cur = self.conn.cursor()
cur.execute(sql, value)
res = cur.fetchall()
except MySQLdb.Error, e:
self.logger.info(
'Error when fetching inventory property template:\n%s (%d)' %
(e.args[1], e.args[0]))
raise Exception(
'Error when fetching inventory property template:\n%s (%d)' %
(e.args[1], e.args[0]))
def retrieveinventoryprop(self, inventoryid, iproptmpltid, value=None):
'''Retrieve id and value from inventory property table with given inventory id and inventory property template id.
An inventory property has to belong to a property template, which belongs to component type.
Use component type property table to retrieve a property, which is common for a component type.
Return: tuple of inventory property id, inventory property value, property template id and inventory_id,
((property id, inventory property value, property template id, inventory_id), ...).
'''
sql = '''
select
inventory_prop_id, inventory_prop_value, inventory_prop_tmplt_id, inventory_id
from inventory_prop
where
inventory_id = %s and inventory_prop_tmplt_id = %s
'''
values = [inventoryid, iproptmpltid]
if value:
value = _wildcardformat(value)
if "%" in value or "_" in value:
sql += " value like %s "
else:
sql += " value = %s "
values.append(value)
try:
cur = self.conn.cursor()
cur.execute(sql, values)
res = cur.fetchall()
except MySQLdb.Error as e:
self.logger.info(
'Error when retrieve id and vale from inventory property table:\n%s (%s)'
% (e.args[1], e.args[0]))
raise Exception(
'Error when retrieve id and vale from inventory property table:\n%s (%s)'
% (e.args[1], e.args[0]))
return res
def retrieveinventoryproptmplt(self, name, ctypeid=None, desc=None):
'''Retrieve id of a given inventory property template name, with optional component type id or description
Wildcards are support in name search, which uses "*" for multiple match,
and "?" for single character match.
Return all inventory property template ids.
'''
if not isinstance(name, (str, unicode)):
raise Exception('Inventory property template name has to be a string.')
sql = '''
select
inventory_prop_tmplt_id, inventory_prop_tmplt_name, inventory_prop_tmplt_desc, inventory_prop_tmplt_default, inventory_prop_tmplt_units
from inventory_prop_tmplt
where
inventory_prop_tmplt_name like %s
'''
name = _wildcardformat(name)
value = [name]
if ctypeid:
sql += ' and cmpnt_type_id = %s '
value.append(ctypeid)
if desc:
sql += ' and inventory_prop_tmplt_desc like %s '
value.append(desc)
try:
cur = self.conn.cursor()
cur.execute(sql, value)
res = cur.fetchall()
except MySQLdb.Error, e:
self.logger.info('Error when fetching inventory property template:\n%s (%d)' % (e.args[1], e.args[0]))
raise Exception('Error when fetching inventory property template:\n%s (%d)' % (e.args[1], e.args[0]))
def retrievegoldenmodel(self, name, status=0, ignorestatus=False):
'''
Retrieve golden model with given name and other conditions
parameters:
name: model name
status: 0: current golden model [by default]
1: alternative golden model
2: previous golden models, but not any more
ignorestatus: get golden model no matter its status
'''
name = _wildcardformat(name)
sql = '''
select gold_model_id, model_name,
gm.created_by, gm.create_date,
gm.updated_by, gm.update_date,
gm.gold_status_ind, gm.model_id
from gold_model gm
left join model on model.model_id = gm.model_id
where
model.model_name like %s
'''
try:
cur = self.conn.cursor()
if ignorestatus:
cur.execute(sql, (name, ))
else:
cur.execute(sql + ''' and gm.gold_status_ind like %s''',
(name, status))
res = cur.fetchall()
except MySQLdb.Error as e:
self.logger.info('Error when retrieving golden model:\n%s (%d)' %
(e.args[1], e.args[0]))
raise Exception('Error when retrieving golden model:\n%s (%d)' %
(e.args[1], e.args[0]))
return res
def retrievecmpnttypeproptype(self, name, desc=None):
'''Retrieve id of a given component type property type name, and description [optional]
Wildcards are support in property type name search, which uses "*" for multiple match,
and "?" for single character match.
Return: tuple of property type of component type ((id, name, description), ...).
'''
if not isinstance(name, (str, unicode)):
raise Exception(
'Component type property type name has to be a string.')
sql = '''
select
cmpnt_type_prop_type_id, cmpnt_type_prop_type_name, cmpnt_type_prop_type_desc
from cmpnt_type_prop_type
where
cmpnt_type_prop_type_name like %s
'''
name = _wildcardformat(name)
try:
cur = self.conn.cursor()
if desc:
sql += " and cmpnt_type_prop_type_desc like %s "
cur.execute(sql, (name, desc))
else:
cur.execute(sql, (name, ))
res = cur.fetchall()
except MySQLdb.Error, e:
self.logger.into(
'Error when fetching component type property types:\n%s (%d)' %
(e.args[1], e.args[0]))
raise Exception(
'Error when fetching component type property types:\n%s (%d)' %
(e.args[1], e.args[0]))
def savegoldenmodel(self, name, status=0, creator=None):
'''
Save a model to a golden model
Parameters:
name: model name
creator: who craeted it, or changed the status last time
status: 0: current golden model [by default]
1: alternative golden model
2: previous golden models, but not any more
other number can be defined by user
return: True if saving gold model successfully, otherwise, raise an exception
'''
creator = _wildcardformat(creator)
sql = '''select model_id from model where model_name = %s'''
cur = self.conn.cursor()
try:
# get model id with given model name
cur.execute(sql, (_wildcardformat(name), ))
res = cur.fetchall()
if len(res) != 1:
raise ValueError(
'Error when retrieving model id for model (%s).' % name)
else:
modelid = res[0][0]
except MySQLdb.Error as e:
self.logger.info('Error when retrieving model:\n%s (%d)' %
(e.args[1], e.args[0]))
raise Exception('Error when retrieving model:\n%s (%d)' %
(e.args[1], e.args[0]))
res = self.retrievegoldenmodel(name, ignorestatus=True)
if len(res) == 0:
# if not found, flag model with given status.
# by default, flag it as current golden model
if creator == None:
sql = '''
insert into gold_model
(model_id, created_by, create_date, gold_status_ind)
values
(%s, NULL, now(), %s)
'''
vals = (modelid, status)
else:
sql = '''
insert into gold_model
(model_id, created_by, create_date, gold_status_ind)
values
(%s, %s, now(), %s)
'''
vals = (modelid, creator, status)
elif len(res) == 1:
if creator == None:
sql = '''
update gold_model
set gold_status_ind = %s, updated_by=NULL, update_date = now()
where gold_model_id = %s
'''
vals = (status, res[0][0])
else:
sql = '''
update gold_model
set gold_status_ind = %s, updated_by = %s, update_date = now()
where gold_model_id = %s
'''
vals = (status, creator, res[0][0])
else:
raise ValueError(
'More than one golden model found for given model (name: %s)' %
(name))
try:
cur.execute(sql, vals)
if self.transaction:
self.transaction.commit_unless_managed()
else:
self.conn.commit()
except MySQLdb.Error as e:
if self.transaction:
self.transaction.rollback_unless_managed()
else:
self.conn.rollback()
self.logger.info('Error when saving golden model:\n%s (%d)' %
(e.args[1], e.args[0]))
raise Exception('Error when saving golden model:\n%s (%d)' %
(e.args[1], e.args[0]))
return True
def retrievemodel(self, modelname=None, modelid=None):
"""
Retrieve a model list that satisfies given constrains.
parameters:
modelname: the name shows that which model this API will deal with
latticename: lattice name that this model belongs to
latticeversion: the version of lattice
latticebranch: the branch of lattice
return: a dictionary
{'model name': # model name
{'id': , # model id number
'latticeId': , # id of the lattice which given model belongs to
'description': , # description of this model
'creator': , # name who create this model first time
'originalDate': , # date when this model was created
'updated': , # name who modified last time
'lastModified': , # the date this model was modified last time
'tunex': , # horizontal tune
'tuney': , # vertical tune
'chromex0': , # linear horizontal chromaticity
'chromex1': , # non-linear horizontal chromaticity
'chromex2': , # high order non-linear horizontal chromaticity
'chromey0': , # linear vertical chromaticity
'chromey1': , # non-linear vertical chromaticity
'chromey2': , # high order non-linear vertical chromaticity
'finalEnergy': , # the final beam energy in GeV
'simulationCode': , # name of simulation code, Elegant and Tracy for example
'sumulationAlgorithm': , # algorithm used by simulation code, for example serial or parallel,
# and SI, or SI/PTC for Tracy code
'simulationControl': , # various control constrains such as initial condition, beam distribution,
# and output controls
'simulationControlFile': # file name that control the simulation conditions, like a .ele file for elegant
}
...
}
"""
if modelname == None and modelid == None:
raise ValueError("Cannot identify a model since neither name nor id is provided.")
sql = """
select model_id, lattice_id,
model_name, model_desc,
created_by, create_date,
updated_by, update_date,
tune_x, tune_y,
chrome_x_0, chrome_x_1, chrome_x_2,
chrome_y_0, chrome_y_1, chrome_y_2,
final_beam_energy,
code_name, algorithm,
model_control, model_control_url
from model
left join model_code on model_code.model_code_id = model.model_code_id
where
"""
vals = []
if modelname != None and modelid != None:
if "*" in modelname or "?" in modelname:
sql += " model_name like %s and model_id = %s"
vals.append(_wildcardformat(modelname))
else:
sql += " model_name = %s and model_id = %s"
vals.append(modelname)
vals.append(modelid)
elif modelname != None:
if "*" in modelname or "?" in modelname:
sql += " model_name like %s"
vals.append(_wildcardformat(modelname))
else:
sql += " model_name = %s"
vals.append(modelname)
else:
sql += " model_id = %s"
vals.append(modelid)
# modelname = _wildcardformat(modelname)
modelres = {}
try:
cur = self.conn.cursor()
cur.execute(sql, vals)
results = cur.fetchall()
for res in results:
tempdict = {"id": res[0], "latticeId": res[1]}
keys = [
"description",
"creator",
"originalDate",
"updated",
"lastModified",
"tunex",
"tuney",
"chromex0",
"chromex1",
"chromex2",
"chromey0",
"chromey1",
"chromey2",
"finalEnergy",
"simulationCode",
"sumulationAlgorithm",
"simulationControl",
"simulationControlFile",
]
for i in range(3, len(res)):
if res[i] != None:
tempdict[keys[i - 3]] = res[i]
modelres[res[2]] = tempdict
except MySQLdb.Error as e:
self.logger.info("Error when retrieving model information:\n%s (%d)" % (e.args[1], e.args[0]))
raise Exception("Error when retrieving model information:\n%s (%d)" % (e.args[1], e.args[0]))
return modelres
def retrievemodel(self, modelname=None, modelid=None):
'''
Retrieve a model list that satisfies given constrains.
parameters:
modelname: the name shows that which model this API will deal with
latticename: lattice name that this model belongs to
latticeversion: the version of lattice
latticebranch: the branch of lattice
return: a dictionary
{'model name': # model name
{'id': , # model id number
'latticeId': , # id of the lattice which given model belongs to
'description': , # description of this model
'creator': , # name who create this model first time
'originalDate': , # date when this model was created
'updated': , # name who modified last time
'lastModified': , # the date this model was modified last time
'tunex': , # horizontal tune
'tuney': , # vertical tune
'chromex0': , # linear horizontal chromaticity
'chromex1': , # non-linear horizontal chromaticity
'chromex2': , # high order non-linear horizontal chromaticity
'chromey0': , # linear vertical chromaticity
'chromey1': , # non-linear vertical chromaticity
'chromey2': , # high order non-linear vertical chromaticity
'finalEnergy': , # the final beam energy in GeV
'simulationCode': , # name of simulation code, Elegant and Tracy for example
'sumulationAlgorithm': , # algorithm used by simulation code, for example serial or parallel,
# and SI, or SI/PTC for Tracy code
'simulationControl': , # various control constrains such as initial condition, beam distribution,
# and output controls
'simulationControlFile': # file name that control the simulation conditions, like a .ele file for elegant
}
...
}
'''
if modelname == None and modelid == None:
raise ValueError(
"Cannot identify a model since neither name nor id is provided."
)
sql = '''
select model_id, lattice_id,
model_name, model_desc,
created_by, create_date,
updated_by, update_date,
tune_x, tune_y,
chrome_x_0, chrome_x_1, chrome_x_2,
chrome_y_0, chrome_y_1, chrome_y_2,
final_beam_energy,
code_name, algorithm,
model_control, model_control_url
from model
left join model_code on model_code.model_code_id = model.model_code_id
where
'''
vals = []
if modelname != None and modelid != None:
if "*" in modelname or "?" in modelname:
sql += ' model_name like %s and model_id = %s'
vals.append(_wildcardformat(modelname))
else:
sql += ' model_name = %s and model_id = %s'
vals.append(modelname)
vals.append(modelid)
elif modelname != None:
if "*" in modelname or "?" in modelname:
sql += ' model_name like %s'
vals.append(_wildcardformat(modelname))
else:
sql += ' model_name = %s'
vals.append(modelname)
else:
sql += ' model_id = %s'
vals.append(modelid)
# modelname = _wildcardformat(modelname)
modelres = {}
try:
cur = self.conn.cursor()
cur.execute(sql, vals)
results = cur.fetchall()
for res in results:
tempdict = {'id': res[0], 'latticeId': res[1]}
keys = [
'description', 'creator', 'originalDate', 'updated',
'lastModified', 'tunex', 'tuney', 'chromex0', 'chromex1',
'chromex2', 'chromey0', 'chromey1', 'chromey2',
'finalEnergy', 'simulationCode', 'sumulationAlgorithm',
'simulationControl', 'simulationControlFile'
]
for i in range(3, len(res)):
if res[i] != None:
tempdict[keys[i - 3]] = res[i]
modelres[res[2]] = tempdict
except MySQLdb.Error as e:
self.logger.info(
'Error when retrieving model information:\n%s (%d)' %
(e.args[1], e.args[0]))
raise Exception(
'Error when retrieving model information:\n%s (%d)' %
(e.args[1], e.args[0]))
return modelres
def savegoldenmodel(self, name, status=0, creator=None):
"""
Save a model to a golden model
Parameters:
name: model name
creator: who craeted it, or changed the status last time
status: 0: current golden model [by default]
1: alternative golden model
2: previous golden models, but not any more
other number can be defined by user
return: True if saving gold model successfully, otherwise, raise an exception
"""
creator = _wildcardformat(creator)
sql = """select model_id from model where model_name = %s"""
cur = self.conn.cursor()
try:
# get model id with given model name
cur.execute(sql, (_wildcardformat(name),))
res = cur.fetchall()
if len(res) != 1:
raise ValueError("Error when retrieving model id for model (%s)." % name)
else:
modelid = res[0][0]
except MySQLdb.Error as e:
self.logger.info("Error when retrieving model:\n%s (%d)" % (e.args[1], e.args[0]))
raise Exception("Error when retrieving model:\n%s (%d)" % (e.args[1], e.args[0]))
res = self.retrievegoldenmodel(name, ignorestatus=True)
if len(res) == 0:
# if not found, flag model with given status.
# by default, flag it as current golden model
if creator == None:
sql = """
insert into gold_model
(model_id, created_by, create_date, gold_status_ind)
values
(%s, NULL, now(), %s)
"""
vals = (modelid, status)
else:
sql = """
insert into gold_model
(model_id, created_by, create_date, gold_status_ind)
values
(%s, %s, now(), %s)
"""
vals = (modelid, creator, status)
elif len(res) == 1:
if creator == None:
sql = """
update gold_model
set gold_status_ind = %s, updated_by=NULL, update_date = now()
where gold_model_id = %s
"""
vals = (status, res[0][0])
else:
sql = """
update gold_model
set gold_status_ind = %s, updated_by = %s, update_date = now()
where gold_model_id = %s
"""
vals = (status, creator, res[0][0])
else:
raise ValueError("More than one golden model found for given model (name: %s)" % (name))
try:
cur.execute(sql, vals)
if self.transaction:
self.transaction.commit_unless_managed()
else:
self.conn.commit()
except MySQLdb.Error as e:
if self.transaction:
self.transaction.rollback_unless_managed()
else:
self.conn.rollback()
self.logger.info("Error when saving golden model:\n%s (%d)" % (e.args[1], e.args[0]))
raise Exception("Error when saving golden model:\n%s (%d)" % (e.args[1], e.args[0]))
return True
