def retrieveinstall(self, name, ctypename=None, location=None):
'''Retrieve installed device name with table id upon giving device name.
return: tuple with format as ((id, field name, location, component type name, description, vendor), ...)
'''
sql = '''
select install_id, field_name, location, cmpnt_type_name, description, vendor_name
from install
left join cmpnt_type on install.cmpnt_type_id = cmpnt_type.cmpnt_type_id
left join cmpnttype__vendor on cmpnt_type.cmpnt_type_id = cmpnttype__vendor.cmpnt_type_id
left join vendor on cmpnttype__vendor.vendor_id = vendor.vendor_id
where
'''
vals = []
vals, sql = _assemblesql(sql, name, " field_name ", vals)
if ctypename:
vals, sql = _assemblesql(sql, ctypename, " cmpnt_type_name ", vals, connector="and")
if location:
vals, sql = _assemblesql(sql, location, " location ", vals, connector="and")
try:
cur = self.conn.cursor()
cur.execute(sql, vals)
res = cur.fetchall()
except MySQLdb.Error as e:
self.logger.info('Error when fetching device from install table:\n%s (%d)' %(e.args[1], e.args[0]))
raise Exception('Error when fetching device from install table:\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 retrievesystem(self, location=None):
'''
retrieve location information from install table
'''
sql = '''
select distinct location from install where
'''
val = None
if location == None:
sql += " location like %s "
val = "%"
else:
val = []
val, sql = _assemblesql(sql, location, " location ", val)
try:
cur = self.conn.cursor()
if isinstance(val, (list, tuple)):
cur.execute(sql, val)
else:
cur.execute(sql, (val, ))
rawres = cur.fetchall()
except MySQLdb.Error as e:
self.logger.info(
'Error when fetching system from install table:\n%s (%d)' %
(e.args[1], e.args[0]))
raise Exception(
'Error when fetching system from install table:\n%s (%d)' %
(e.args[1], e.args[0]))
system = []
for r in rawres:
system.append(r[0])
return system
def retrievesystem(self, location = None):
'''
retrieve location information from install table
'''
sql = '''
select distinct location from install where
'''
val = None
if location == None:
sql += " location like %s "
val = "%"
else:
val = []
val, sql = _assemblesql(sql, location, " location ", val)
try:
cur = self.conn.cursor()
if isinstance(val, (list, tuple)):
cur.execute(sql, val)
else:
cur.execute(sql, (val,))
rawres = cur.fetchall()
except MySQLdb.Error as e:
self.logger.info('Error when fetching system from install table:\n%s (%d)' %(e.args[1], e.args[0]))
raise Exception('Error when fetching system from install table:\n%s (%d)' %(e.args[1], e.args[0]))
system = []
for r in rawres:
system.append(r[0])
return system
def retrieveinstalledinventory(self, name, serial, ctypename=None, vendor=None, location=None):
'''
Retrieve devices from inventory what have been installed according given device name, serial number, component type and vendor.
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 with format like ((install id, inventory id, device name, location, serial number, component type name, description, vendor name), ...).
'''
sql = '''select install.install_id, inventory.inventory_id, install.field_name, install.location,
inventory.serial_no,
cmpnt_type.cmpnt_type_name, cmpnt_type.description,
vendor.vendor_name
from install
left join inventory__install on install.install_id = inventory__install.install_id
left join inventory on inventory__install.inventory_id = inventory.inventory_id
left join cmpnt_type on inventory.cmpnt_type_id = cmpnt_type.cmpnt_type_id
left join cmpnttype__vendor on cmpnt_type.cmpnt_type_id = cmpnttype__vendor.cmpnt_type_id
left join vendor on vendor.vendor_id = cmpnttype__vendor.vendor_id
where
'''
vals = []
vals, sql = _assemblesql(sql, name, " install.field_name ", vals)
vals, sql = _assemblesql(sql, serial, " inventory.serial_no ", vals, connector="and")
if ctypename:
vals, sql = _assemblesql(sql, ctypename, " cmpnt_type.cmpnt_type_name ", vals, connector="and")
if vendor:
vals, sql = _assemblesql(sql, vendor, " vendor.vendor_name ", vals, connector="and")
if location:
vals, sql = _assemblesql(sql, location, " install.location ", vals, connector="and")
try:
cur = self.conn.cursor()
cur.execute(sql, vals)
res = cur.fetchall()
except MySQLdb.Error as e:
self.logger.info('Error when selecting installed device (%s) with serial no (%s) from inventory with type (%s) from vendor (%s):\n%s (%b)'
%(name, serial, ctypename, vendor, e.args[1], e.args[0]))
raise Exception('Error when selecting installed device (%s) with serial no (%s) from inventory with type (%s) from vendor (%s):\n%s (%b)'
%(name, serial, ctypename, vendor, e.args[1], e.args[0]))
return res
def retrieveinstall(self, name, ctypename=None, location=None):
'''Retrieve installed device name with table id upon giving device name.
return: tuple with format as ((id, field name, location, component type name, description, vendor), ...)
'''
sql = '''
select install_id, field_name, location, cmpnt_type_name, description, vendor_name
from install
left join cmpnt_type on install.cmpnt_type_id = cmpnt_type.cmpnt_type_id
left join cmpnttype__vendor on cmpnt_type.cmpnt_type_id = cmpnttype__vendor.cmpnt_type_id
left join vendor on cmpnttype__vendor.vendor_id = vendor.vendor_id
where
'''
vals = []
vals, sql = _assemblesql(sql, name, " field_name ", vals)
if ctypename:
vals, sql = _assemblesql(sql,
ctypename,
" cmpnt_type_name ",
vals,
connector="and")
if location:
vals, sql = _assemblesql(sql,
location,
" location ",
vals,
connector="and")
try:
cur = self.conn.cursor()
cur.execute(sql, vals)
res = cur.fetchall()
except MySQLdb.Error as e:
self.logger.info(
'Error when fetching device from install table:\n%s (%d)' %
(e.args[1], e.args[0]))
raise Exception(
'Error when fetching device from install table:\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 retrieveinventoryid(self, name):
''''''
sql = '''
select field_name, ii.inventory_id
from install
left join inventory__install ii on ii.install_id = install.install_id
where
'''
vals=[]
vals, sql = _assemblesql(sql, name, " field_name ", vals)
try:
cur=self.conn.cursor()
cur.execute(sql, vals)
res = cur.fetchall()
except MySQLdb.Error as e:
self.logger.info('Error when fetching device name and inventory information:\n%s (%d)' %(e.args[1], e.args[0]))
raise Exception('Error when fetching device name and inventory information:\n%s (%d)' %(e.args[1], e.args[0]))
return res
def retrieveinventoryid(self, name):
''''''
sql = '''
select field_name, ii.inventory_id
from install
left join inventory__install ii on ii.install_id = install.install_id
where
'''
vals = []
vals, sql = _assemblesql(sql, name, " field_name ", vals)
try:
cur = self.conn.cursor()
cur.execute(sql, vals)
res = cur.fetchall()
except MySQLdb.Error as e:
self.logger.info(
'Error when fetching device name and inventory information:\n%s (%d)'
% (e.args[1], e.args[0]))
raise Exception(
'Error when fetching device name and inventory information:\n%s (%d)'
% (e.args[1], e.args[0]))
return res
def retrieveinstalledinventory(self,
name,
serial,
ctypename=None,
vendor=None,
location=None):
'''
Retrieve devices from inventory what have been installed according given device name, serial number, component type and vendor.
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 with format like ((install id, inventory id, device name, location, serial number, component type name, description, vendor name), ...).
'''
sql = '''select install.install_id, inventory.inventory_id, install.field_name, install.location,
inventory.serial_no,
cmpnt_type.cmpnt_type_name, cmpnt_type.description,
vendor.vendor_name
from install
left join inventory__install on install.install_id = inventory__install.install_id
left join inventory on inventory__install.inventory_id = inventory.inventory_id
left join cmpnt_type on inventory.cmpnt_type_id = cmpnt_type.cmpnt_type_id
left join cmpnttype__vendor on cmpnt_type.cmpnt_type_id = cmpnttype__vendor.cmpnt_type_id
left join vendor on vendor.vendor_id = cmpnttype__vendor.vendor_id
where
'''
vals = []
vals, sql = _assemblesql(sql, name, " install.field_name ", vals)
vals, sql = _assemblesql(sql,
serial,
" inventory.serial_no ",
vals,
connector="and")
if ctypename:
vals, sql = _assemblesql(sql,
ctypename,
" cmpnt_type.cmpnt_type_name ",
vals,
connector="and")
if vendor:
vals, sql = _assemblesql(sql,
vendor,
" vendor.vendor_name ",
vals,
connector="and")
if location:
vals, sql = _assemblesql(sql,
location,
" install.location ",
vals,
connector="and")
try:
cur = self.conn.cursor()
cur.execute(sql, vals)
res = cur.fetchall()
except MySQLdb.Error as e:
self.logger.info(
'Error when selecting installed device (%s) with serial no (%s) from inventory with type (%s) from vendor (%s):\n%s (%b)'
% (name, serial, ctypename, vendor, e.args[1], e.args[0]))
raise Exception(
'Error when selecting installed device (%s) with serial no (%s) from inventory with type (%s) from vendor (%s):\n%s (%b)'
% (name, serial, ctypename, vendor, e.args[1], e.args[0]))
return res