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