def tear_down_testing_orders(self): # delete scenes first scene_sql = "DELETE FROM ordering_scene where id > 0;" with db_instance() as db: db.execute(scene_sql) db.commit() # now you can delete orders ord_sql = "DELETE FROM ordering_order where id > 0;" with db_instance() as db: db.execute(ord_sql) db.commit() return True
def order_attr(self, col): """ Select the column value from the ordering_order table for this specific scene :param col: column to select on :return: value """ sql = ('SELECT %s ' 'FROM ordering_scene JOIN ordering_order ' 'ON ordering_order.id = ordering_scene.order_id ' 'WHERE ordering_scene.id = %s') log_sql = '' try: with db_instance() as db: log_sql = db.cursor.mogrify(sql, (db_extns.AsIs(col), self.id)) db.select(sql, (db_extns.AsIs(col), self.id)) ret = db[0][col] except DBConnectException as e: logger.critical('Error retrieving order_attr: {}\n' 'sql: {} \n'.format(e.message, log_sql)) raise SceneException(e) except KeyError as e: logger.critical('Error order_attr returned no results\n' 'sql: {}'.format(log_sql)) raise SceneException('Key Error: {}'.format(e.message)) return ret
def find(cls, ids): """ Retrieve scene objects by id :param ids: list of scene ids, or single scene id :return: list """ sql = '{} id IN %s;'.format(cls.base_sql) resp = list() if not isinstance(ids, list) and not isinstance(ids, int): raise SceneException("a list of integers, or a single integer, " "are the only valid arguments for Scene.find()") if isinstance(ids, list): _single = False for item in ids: if not isinstance(item, int): raise SceneException("list members must be of type int for " "Scene.find(): {0} is not an int".format(item)) else: _single = True ids = [ids] with db_instance() as db: db.select(sql, [tuple(ids)]) if db: for i in db: sd = dict(i) obj = Scene(**sd) resp.append(obj) if _single: return resp[0] else: return resp
def where(cls, params): """ Query for a particular row in the ordering_oder table :param params: dictionary of column: value parameter to select on :return: list of matching Order objects """ if not isinstance(params, dict): raise OrderException('Where arguments must be ' 'passed as a dictionary') sql, values = format_sql_params(cls.base_sql, params) ret = [] log_sql = '' try: with db_instance() as db: log_sql = db.cursor.mogrify(sql, values) logger.info('order.py where sql: {}'.format(log_sql)) db.select(sql, values) for i in db: od = dict(i) obj = Order(**od) ret.append(obj) except DBConnectException as e: logger.debug('Error order where: {}\n' 'sql: {}'.format(e.message, log_sql)) raise OrderException(e) return ret
def update_system_status(params): if params.keys().sort() is not [ 'system_message_title', 'system_message_body', 'display_system_message' ].sort(): return { 'msg': 'Only 3 params are valid, and they must be present:' 'system_message_title, system_message_body,' 'display_system_message' } sql_dict = { 'msg.system_message_title': params['system_message_title'], 'msg.system_message_body': params['system_message_body'], 'system.display_system_message': params['display_system_message'] } sql = "" for k, v in sql_dict.iteritems(): sql += "update ordering_configuration set value = '{0}' where key = '{1}';".format( v, k) try: with db_instance() as db: db.execute(sql) db.commit() except DBConnectException as e: logger.debug("error updating system status: {}".format(e)) return {'msg': "error updating database: {}".format(e.message)} return True
def setUp(self): os.environ['espa_api_testing'] = 'True' # create a user self.mock_user = MockUser() self.mock_order = MockOrder() self.user = User.find(self.mock_user.add_testing_user()) self.order_id = self.mock_order.generate_testing_order(self.user.id) self.app = http.app.test_client() self.app.testing = True self.sceneids = self.mock_order.scene_names_list(self.order_id)[0:2] token = ''.format(self.user.username, 'foo') auth_string = "Basic {}".format(base64.b64encode(token)) self.headers = {"Authorization": auth_string} with db_instance() as db: uidsql = "select user_id, orderid from ordering_order limit 1;" db.select(uidsql) self.userid = db[0]['user_id'] self.orderid = db[0]['orderid'] itemsql = "select name, order_id from ordering_scene limit 1;" db.select(itemsql) self.itemid = db[0][0] itemorderid = db[0][1] ordersql = "select orderid from ordering_order where id = {};".format(itemorderid) db.select(ordersql) self.itemorderid = db[0][0] self.base_order = lowercase_all(testorders.build_base_order())
def setUp(self): os.environ['espa_api_testing'] = 'True' # create a user self.mock_user = MockUser() self.mock_order = MockOrder() self.user = User.find(self.mock_user.add_testing_user()) self.order_id = self.mock_order.generate_testing_order(self.user.id) self.app = http.app.test_client() self.app.testing = True self.sceneids = self.mock_order.scene_names_list(self.order_id)[0:2] token = ''.format(self.user.username, 'foo') auth_string = "Basic {}".format(base64.b64encode(token)) self.headers = {"Authorization": auth_string} with db_instance() as db: uidsql = "select user_id, orderid from ordering_order limit 1;" db.select(uidsql) self.userid = db[0]['user_id'] self.orderid = db[0]['orderid'] itemsql = "select name, order_id from ordering_scene limit 1;" db.select(itemsql) self.itemid = db[0][0] itemorderid = db[0][1] ordersql = "select orderid from ordering_order where id = {};".format( itemorderid) db.select(ordersql) self.itemorderid = db[0][0] self.base_order = lowercase_all(testorders.build_base_order())
def order_attr(self, col): """ Select the column value from the ordering_order table for this specific scene :param col: column to select on :return: value """ sql = ('SELECT %s ' 'FROM ordering_scene JOIN ordering_order ' 'ON ordering_order.id = ordering_scene.order_id ' 'WHERE ordering_scene.id = %s') log_sql = '' try: with db_instance() as db: log_sql = db.cursor.mogrify(sql, (db_extns.AsIs(col), self.id)) db.select(sql, (db_extns.AsIs(col), self.id)) ret = db[0][col] except DBConnectException as e: logger.debug('Error retrieving order_attr: {}\n' 'sql: {} \n'.format(e.message, log_sql)) raise SceneException(e) except KeyError as e: logger.debug('Error order_attr returned no results\n' 'sql: {}'.format(log_sql)) raise SceneException('Key Error: {}' .format(e.message)) return ret
def update(self, att, val): """ Update a specified column value for this Order object :param att: column to update :param val: new value :return: updated value from self """ sql = 'update ordering_order set %s = %s where id = %s' log_sql = '' try: with db_instance() as db: log_sql = db.cursor.mogrify(sql, (db_extns.AsIs(att), val, self.id)) logger.info(log_sql) db.execute(sql, (db_extns.AsIs(att), val, self.id)) db.commit() except DBConnectException as e: logger.debug('Error updating order: {}\nSQL: {}'.format( e.message, log_sql)) self.__setattr__(att, val) return self.__getattribute__(att)
def fetch_order(self, ordernum): sql = "select * from ordering_order where orderid = %s;" out_dict = {} opts_dict = {} scrub_keys = ['initial_email_sent', 'completion_email_sent', 'id', 'user_id', 'ee_order_id', 'email'] with db_instance() as db: db.select(sql, (str(ordernum))) if db: for key, val in db[0].iteritems(): if isinstance(val, datetime.datetime): out_dict[key] = val.isoformat() else: out_dict[key] = val opts_str = db[0]['product_options'] opts_str = opts_str.replace("\n", "") opts_dict = yaml.load(opts_str) out_dict['product_options'] = opts_dict else: out_dict['msg'] = "sorry, no order matched that orderid" for k in scrub_keys: if k in out_dict.keys(): out_dict.pop(k) return out_dict
def where(cls, params): """ Query for particular users :param params: dictionary of column: value parameters :return: list of matching User objects """ if not isinstance(params, dict): raise UserException( 'Where arguments must be passed as a dictionary') sql, values = format_sql_params(cls.base_sql, params) ret = [] log_sql = '' try: with db_instance() as db: log_sql = db.cursor.mogrify(sql, values) logger.info('user.py where sql: {}'.format(log_sql)) db.select(sql, values) for i in db: obj = User(i["username"], i["email"], i["first_name"], i["last_name"], i["contactid"]) ret.append(obj) except DBConnectException as e: logger.debug('Error querying for users: {}\n' 'sql: {}'.format(e.message, log_sql)) raise UserException(e) return ret
def find_or_create_user(cls, username, email, first_name, last_name, contactid): user_id = None # username comes in not as a str from EE, which DBConnect takes issue with username = str(username) nownow = time.strftime('%Y-%m-%d %H:%M:%S') insert_stmt = "insert into auth_user (username, " \ "email, first_name, last_name, password, " \ "is_staff, is_active, is_superuser, " \ "last_login, date_joined, contactid) values " \ "(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) " \ "on conflict (username) " \ "do update set (email, contactid) = (%s, %s) " \ "where auth_user.username = %s" \ "returning id" arg_tup = (username, email, first_name, last_name, 'pass', 'f', 't', 'f', nownow, nownow, contactid, email, contactid, username) with db_instance() as db: try: db.execute(insert_stmt, arg_tup) db.commit() user_id = db.fetcharr[0]['id'] except: exc_type, exc_val, exc_trace = sys.exc_info() logger.debug("ERR user find_or_create args {0} {1} " \ "{2} {3}\n trace: {4}".format(username, email, first_name, last_name, traceback.format_exc())) raise exc_type, exc_val, exc_trace return user_id
def where(cls, params): """ Query for a particular row in the ordering_scene table :param params: dictionary of column: value parameter to select on :return: list of matching Scene objects """ if not isinstance(params, dict): raise SceneException('Where arguments must be ' 'passed as a dictionary') sql, values = format_sql_params(cls.base_sql, params) ret = [] log_sql = '' try: with db_instance() as db: log_sql = db.cursor.mogrify(sql, values) logger.info('scene.py where sql: {}'.format(log_sql)) db.select(sql, values) for i in db: sd = dict(i) obj = Scene(**sd) ret.append(obj) except DBConnectException as e: logger.critical('Error retrieving scenes: {}\n' 'sql: {}'.format(e.message, log_sql)) raise SceneException(e) return ret
def find(cls, ids): sql = '{} id IN %s;'.format(cls.base_sql) resp = list() if not isinstance(ids, list) and not isinstance(ids, int): raise UserException("a list of integers, or a single integer, " "are the only valid arguments for User.find()") if isinstance(ids, list): _single = False for item in ids: if not isinstance(item, int): raise UserException( "list members must be of type int for " "User.find(): {0} is not an int".format(item)) else: _single = True ids = [ids] with db_instance() as db: db.select(sql, [tuple(ids)]) if db: for i in db: obj = User(i["username"], i["email"], i["first_name"], i["last_name"], i["contactid"]) resp.append(obj) if _single: return resp[0] else: return resp
def update(self, att, val): """ Update a specifed column value for this Scene object with a new value :param att: column to update :param val: new value :return: updated value from self """ sql = 'update ordering_scene set %s = %s where id = %s' log_sql = '' try: with db_instance() as db: log_sql = db.cursor.mogrify(sql, (db_extns.AsIs(att), val, self.id)) logger.info("\n*** Updating scene: \n" + log_sql + '\n***\n"') db.execute(sql, (db_extns.AsIs(att), val, self.id)) db.commit() except DBConnectException as e: raise SceneException('Error updating scene: {}\nSQL: {}' .format(e.message, log_sql)) self.__setattr__(att, val) return self.__getattribute__(att)
def find_or_create_user(self): """ check if user exists in our DB, if not create them returns what should be assigned to self.id """ (username, email, first_name, last_name, contactid) = (self.username, self.email, self.first_name, self.last_name, self.contactid) user_id = None nownow = time.strftime('%Y-%m-%d %H:%M:%S') insert_stmt = "insert into auth_user (username, " \ "email, first_name, last_name, password, " \ "is_staff, is_active, is_superuser, " \ "last_login, date_joined, contactid) values " \ "(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) " \ "on conflict (username) " \ "do update set (email, contactid, last_login) = (%s, %s, %s) " \ "where auth_user.username = %s" \ "returning id" arg_tup = (username, email, first_name, last_name, 'pass', 'f', 't', 'f', nownow, nownow, contactid, email, contactid, nownow, username) with db_instance() as db: try: db.execute(insert_stmt, arg_tup) db.commit() user_id = db.fetcharr[0]['id'] except: exc_type, exc_val, exc_trace = sys.exc_info() logger.critical("ERR user find_or_create args {0} {1} " \ "{2} {3}\n trace: {4}".format(username, email, first_name, last_name, traceback.format_exc())) raise exc_type, exc_val, exc_trace return user_id
def update(self, att, val): """ Update a specifed column value for this Scene object with a new value :param att: column to update :param val: new value :return: updated value from self """ sql = 'update ordering_scene set %s = %s where id = %s' log_sql = '' try: with db_instance() as db: log_sql = db.cursor.mogrify(sql, (db_extns.AsIs(att), val, self.id)) logger.info("\n*** Updating scene: \n" + log_sql + '\n***\n"') db.execute(sql, (db_extns.AsIs(att), val, self.id)) db.commit() except DBConnectException as e: raise SceneException('Error updating scene: {}\nSQL: {}'.format( e.message, log_sql)) self.__setattr__(att, val) return self.__getattribute__(att)
def update_system_status(params): if set(params) != { 'system_message_title', 'system_message_body', 'display_system_message' }: return { 'msg': 'Only 3 params are valid, and they must be present:' 'system_message_title, system_message_body,' 'display_system_message' } sql = '''update ordering_configuration set value = %s where key = 'msg.system_message_title'; update ordering_configuration set value = %s where key = 'msg.system_message_body'; update ordering_configuration set value = %s where key = 'system.display_system_message'; ''' sql_vals = (params['system_message_title'], params['system_message_body'], params['display_system_message']) try: with db_instance() as db: db.execute(sql, sql_vals) db.commit() except DBConnectException as e: logger.critical("error updating system status: {}".format(e)) return {'msg': "error updating database: {}".format(e.message)} return True
def update(self, att, val): """ Update a specified column value for this Order object :param att: column to update :param val: new value :return: updated value from self """ sql = 'update ordering_order set %s = %s where id = %s' log_sql = '' try: with db_instance() as db: log_sql = db.cursor.mogrify(sql, (db_extns.AsIs(att), val, self.id)) logger.info(log_sql) db.execute(sql, (db_extns.AsIs(att), val, self.id)) db.commit() except DBConnectException as e: logger.debug('Error updating order: {}\nSQL: {}' .format(e.message, log_sql)) self.__setattr__(att, val) return self.__getattribute__(att)
def _retrieve_config(): config = {} with db_instance() as db: con_query = 'select key, value from ordering_configuration' db.select(con_query) for i in db: config[i['key']] = i['value'] return config
def update(self, att, val): self.__setattr__(att, val) if isinstance(val, str) or isinstance(val, datetime.datetime): val = "\'{0}\'".format(val) sql = "update auth_user set {0} = {1} where id = {2};".format( att, val, self.id) with db_instance() as db: db.execute(sql) db.commit() return True
def __init__(self, id=None, orderid=None, status=None, order_source=None, order_type=None, product_options=None, product_opts=None, initial_email_sent=None, completion_email_sent=None, note=None, completion_date=None, order_date=None, user_id=None, ee_order_id=None, email=None, priority=None): """ Initialize the Order object with all the information for it from the database All parameters are directly related to DB columns :param orderid: order ID long name, someone@someplace-123456 :param status: current status in the system :param order_source: origination of the Order, earthexplorer or ESPA :param order_type: typically 'level2_ondemand' :param product_options: legacy column :param product_opts: dict representation of the order :param initial_email_sent: date :param completion_email_sent: date :param note: user note on the order :param completion_date: date :param order_date: date :param user_id: earth explorer ID of the user :param ee_order_id: ID used by EE to track the order :param email: user email :param priority: legacy """ self.orderid = orderid self.status = status self.order_source = order_source self.order_type = order_type self.product_options = product_options self.product_opts = product_opts self.initial_email_sent = initial_email_sent self.completion_email_sent = completion_email_sent self.note = note self.completion_date = completion_date self.order_date = order_date self.user_id = user_id self.ee_order_id = ee_order_id self.email = email self.priority = priority if id: # no need to query the DB again self.id = id else: with db_instance() as db: sql = 'select id from ordering_order where orderid = %s' db.select(sql, orderid) if db: self.id = db[0]['id'] else: self.id = None
def delete(self, key): self.dump() if self.exists(key): query = 'delete from ordering_configuration where key = %s' with db_instance() as db: db.execute(query, (key,)) db.commit() return self.get(key)
def delete(self, key): self.dump() if self.exists(key): query = 'delete from ordering_configuration where key = %s' with db_instance() as db: db.execute(query, (key, )) db.commit() return self.get(key)
def put(self, key, value): self.dump() query = ('insert into ordering_configuration (key, value) values (%s, %s) ' 'on conflict (key) ' 'do update set value = %s') with db_instance() as db: db.execute(query, (key, value, value)) db.commit() return {key: self.get(key)}
def scene_status_count(self, status=None): sql = "select count(id) from ordering_scene where order_id = %s" arg_tup = (self.id,) if status in self.valid_statuses: sql += " AND status = %s " arg_tup = (self.id, status) with db_instance() as db: db.select(sql+";", arg_tup) return int(db[0][0])
def scene_status_count(self, status=None): sql = "select count(id) from ordering_scene where order_id = %s" arg_tup = (self.id, ) if status in self.valid_statuses: sql += " AND status = %s " arg_tup = (self.id, status) with db_instance() as db: db.select(sql + ";", arg_tup) return int(db[0][0])
def run(self, name): if name not in REPORTS: raise NotImplementedError query = REPORTS[name]['query'] if query is not None and len(query) > 0: with db_instance() as db: db.select(query) return db.dictfetchall else: logger.warn("Query was empty for {0}: {1}".format(name, query)) return {}
def order_status(self, orderid): sql = "select orderid, status from ordering_order where orderid = %s;" response = {} with db_instance() as db: db.select(sql, str(orderid)) if db: for i in ['orderid', 'status']: response[i] = db[0][i] else: response['msg'] = 'sorry, no orders matched orderid %s' % orderid return response
def get_system_status(self): sql = "select key, value from ordering_configuration where " \ "key in ('msg.system_message_body', 'msg.system_message_title', 'system.display_system_message');" with db_instance() as db: db.select(sql) if db: resp_dict = dict(db.fetcharr) return {'system_message_body': resp_dict['msg.system_message_body'], 'system_message_title': resp_dict['msg.system_message_title'], 'display_system_message': resp_dict['system.display_system_message']} else: return {'system_message_body': None, 'system_message_title': None}
def get_system_status(): sql = "select key, value from ordering_configuration where " \ "key in ('msg.system_message_body', 'msg.system_message_title', 'system.display_system_message');" with db_instance() as db: db.select(sql) if db: resp_dict = dict(db.fetcharr) return {'system_message_body': resp_dict['msg.system_message_body'], 'system_message_title': resp_dict['msg.system_message_title'], 'display_system_message': resp_dict['system.display_system_message']} else: return {'system_message_body': None, 'system_message_title': None}
def get(cls, col_name, scene_name, orderid): """ Retrieve a value for a particular column based on the long name of the order :param col_name: column value to retrieve :param scene_name: scene/collection id :param orderid: long name for the related order, [email protected] :return: column value """ sql = ('select %s ' 'from ordering_scene ' 'join ordering_order ' 'on ordering_order.id = ordering_scene.order_id ' 'where ordering_scene.name = %s ' 'and ordering_order.orderid = %s') if '.' in col_name: _, col = col_name.split('.') else: col = col_name log_sql = '' try: with db_instance() as db: log_sql = (db.cursor. mogrify(sql, (db_extns.AsIs(col_name), scene_name, orderid))) logger.info(log_sql) db.select(sql, (db_extns.AsIs(col_name), scene_name, orderid)) ret = db[0][col] except DBConnectException as e: logger.debug('Error scene get\n' 'msg: {0}\n' 'sql: {1}'.format(e.message, log_sql)) raise SceneException(e.message) except KeyError as e: logger.debug('Scene.get returned no results\n' 'sql: {}'.format(log_sql)) raise SceneException('Key Error: {}' .format(e.message)) return ret
def item_status(self, orderid, itemid='ALL', username=None): response = {} sql = "select oo.orderid, os.id scene_id, os.name, os.status, os.completion_date, os.note, " \ "os.product_dload_url, os.cksum_download_url, os.log_file_contents " \ "from ordering_order oo left join ordering_scene os on oo.id = " \ "os.order_id where oo.orderid = %s" user = User.by_username(username) if itemid is not "ALL": argtup = (orderid, itemid) sql += " AND os.name = %s;" else: argtup = (str(orderid)) sql += ";" with db_instance() as db: db.select(sql, argtup) items = [_ for _ in db.fetcharr] if items: id = items[0]['orderid'] response['orderid'] = {id: []} for item in items: try: ts = item['completion_date'].isoformat() except AttributeError: # completion_date not yet set ts = '' i = { 'scene_id': item['scene_id'], 'name': item['name'], 'status': item['status'], 'completion_date': ts, 'note': item['note'], 'product_dload_url': item['product_dload_url'], 'cksum_download_url': item['cksum_download_url'] } if user and user.is_staff(): i['log_file_contents'] = item['log_file_contents'] response['orderid'][id].append(i) else: response[ 'msg'] = 'sorry, no items matched orderid %s , itemid %s' % ( orderid, itemid) return response
def load(self, path): self.dump() # Debating the merits of such an operation # if clear: # with db_instance() as db: # db.execute('DELETE FROM ordering_configuration') # db.commit() with open(path, 'r') as f: sql = f.read() with db_instance() as db: db.execute(sql) db.commit()
def stat_query(self, name): if name not in STATS: raise NotImplementedError("value: {0}".format(name)) query = STATS[name]['query'] if query is not None and len(query) > 0: with db_instance() as db: db.select(query) result = db.dictfetchall stat = result[0]['statistic'] return stat else: logger.debug("Query was empty for {0}: {1}".format(name, query)) return None
def roles(self): result = None with db_instance() as db: db.select( "select is_staff, is_active, is_superuser from auth_user where id = %s;" % self.id) try: result = db[0] except: exc_type, exc_val, exc_trace = sys.exc_info() logger.debug( "ERR retrieving roles for user. msg{0} trace{1}".format( exc_val, traceback.format_exc())) raise exc_type, exc_val, exc_trace return result
def save(self): """ Upsert self to the database """ sql = ('INSERT INTO ordering_order %s VALUES %s ' 'ON CONFLICT (orderid) ' 'DO UPDATE ' 'SET %s = %s') attr_tup = ('orderid', 'status', 'order_source', 'product_options', 'product_opts', 'order_type', 'initial_email_sent', 'completion_email_sent', 'note', 'completion_date', 'order_date', 'user_id', 'ee_order_id', 'email', 'priority') vals = tuple(self.__getattribute__(v) if v != 'product_opts' else json.dumps(self.__getattribute__(v)) for v in attr_tup) cols = '({})'.format(','.join(attr_tup)) log_sql = '' try: with db_instance() as db: log_sql = db.cursor.mogrify(sql, (db_extns.AsIs(cols), vals, db_extns.AsIs(cols), vals)) db.execute(sql, (db_extns.AsIs(cols), vals, db_extns.AsIs(cols), vals)) db.commit() logger.info('Saved updates to order id: {}\n' 'order.id: {}\nsql: {}\nargs: {}' .format(self.orderid, self.id, log_sql, zip(attr_tup, vals))) except DBConnectException as e: logger.debug('Error saving order: {}\nsql: {}' .format(e.message, log_sql)) raise OrderException(e) new = Order.find(self.id) for att in attr_tup: self.__setattr__(att, new.__getattribute__(att))
def save(self): """ Upsert self to the database """ sql = ('INSERT INTO ordering_order %s VALUES %s ' 'ON CONFLICT (orderid) ' 'DO UPDATE ' 'SET %s = %s') attr_tup = ('orderid', 'status', 'order_source', 'product_options', 'product_opts', 'order_type', 'initial_email_sent', 'completion_email_sent', 'note', 'completion_date', 'order_date', 'user_id', 'ee_order_id', 'email', 'priority') vals = tuple( self.__getattribute__(v) if v != 'product_opts' else json. dumps(self.__getattribute__(v)) for v in attr_tup) cols = '({})'.format(','.join(attr_tup)) log_sql = '' try: with db_instance() as db: log_sql = db.cursor.mogrify( sql, (db_extns.AsIs(cols), vals, db_extns.AsIs(cols), vals)) db.execute( sql, (db_extns.AsIs(cols), vals, db_extns.AsIs(cols), vals)) db.commit() logger.info('Saved updates to order id: {}\n' 'order.id: {}\nsql: {}\nargs: {}'.format( self.orderid, self.id, log_sql, zip(attr_tup, vals))) except DBConnectException as e: logger.debug('Error saving order: {}\nsql: {}'.format( e.message, log_sql)) raise OrderException(e) new = Order.find(self.id) for att in attr_tup: self.__setattr__(att, new.__getattribute__(att))
def create(cls, params): """ Create a new scene entry in the ordering_scene table Also supports a bulk insert for large sets of scenes to insert dict{'name': , 'order_id': , 'status': , 'sensor_type': , 'ee_unit_id': } :param params: dictionary representation of a scene to insert into the system or a list of dictionary objects """ if isinstance(params, (list, tuple)): template = ','.join(['%s'] * len(params)) args = [(s['name'], s['order_id'], s['status'], s['sensor_type'], s['ee_unit_id'], '', '', '', '', '') for s in params] else: template = '%s' args = [(params['name'], params['order_id'], params['status'], params['sensor_type'], params['ee_unit_id'], '', '', '', '', '')] sql = ('INSERT INTO ordering_scene ' '(name, order_id, status, sensor_type, ee_unit_id, ' 'product_distro_location, product_dload_url, ' 'cksum_distro_location, cksum_download_url, ' 'processing_location) VALUES {}'.format(template)) log_sql = '' try: with db_instance() as db: log_sql = db.cursor.mogrify(sql, args) logger.info('scene creation sql: {}' .format(log_sql)) db.execute(sql, args) db.commit() except DBConnectException as e: logger.debug('error creating new scene(s): {}\n' 'sql: {}\n' .format(e.message, log_sql)) raise SceneException(e.message)
def item_status(self, orderid, itemid='ALL', username=None): response = {} sql = "select oo.orderid, os.id scene_id, os.name, os.status, os.completion_date, os.note, " \ "os.product_dload_url, os.cksum_download_url, os.log_file_contents " \ "from ordering_order oo left join ordering_scene os on oo.id = " \ "os.order_id where oo.orderid = %s" user = User.by_username(username) if itemid is not "ALL": argtup = (orderid, itemid) sql += " AND os.name = %s;" else: argtup = (str(orderid)) sql += ";" with db_instance() as db: db.select(sql, argtup) items = [_ for _ in db.fetcharr] if items: id = items[0]['orderid'] response['orderid'] = {id: []} for item in items: try: ts = item['completion_date'].isoformat() except AttributeError: # completion_date not yet set ts = '' i = {'scene_id': item['scene_id'], 'name': item['name'], 'status': item['status'], 'completion_date': ts, 'note': item['note'], 'product_dload_url': item['product_dload_url'], 'cksum_download_url': item['cksum_download_url']} if user and user.is_staff(): i['log_file_contents'] = item['log_file_contents'] response['orderid'][id].append(i) else: response['msg'] = 'sorry, no items matched orderid %s , itemid %s' % (orderid, itemid) return response
def setUp(self): os.environ['espa_api_testing'] = 'True' # create a user self.mock_user = MockUser() self.mock_order = MockOrder() self.user = User.find(self.mock_user.add_testing_user()) self.order_id = self.mock_order.generate_testing_order(self.user.id) self.app = http.app.test_client() self.app.testing = True self.sceneids = self.mock_order.scene_names_list(self.order_id)[0:2] token = ':'.join((self.user.username, 'foo')) auth_string = "Basic {}".format(base64.b64encode(token)) self.headers = {"Authorization": auth_string} with db_instance() as db: uidsql = "select user_id, orderid from ordering_order limit 1;" db.select(uidsql) self.userid = db[0]['user_id'] self.orderid = db[0]['orderid'] itemsql = "select name, order_id from ordering_scene limit 1;" db.select(itemsql) self.itemid = db[0][0] itemorderid = db[0][1] ordersql = "select orderid from ordering_order where id = {};".format( itemorderid) db.select(ordersql) self.itemorderid = db[0][0] self.base_order = lowercase_all(testorders.build_base_order()) self.sensors = [ k for k in self.base_order.keys() if isinstance(self.base_order[k], dict) and 'inputs' in self.base_order[k] ] self.inputs = {s: self.base_order[s]['inputs'] for s in self.sensors} self.input_names_all = set([ i for sublist in [s for k, s in self.inputs.items()] for i in sublist ])
def save(self): """ Save the current configuration of the scene object to the DB """ sql = 'UPDATE ordering_scene SET %s = %s WHERE id = %s' attr_tup = ('status', 'cksum_download_url', 'log_file_contents', 'processing_location', 'retry_after', 'job_name', 'note', 'retry_count', 'sensor_type', 'product_dload_url', 'tram_order_id', 'completion_date', 'ee_unit_id', 'retry_limit', 'cksum_distro_location', 'product_distro_location', 'reported_orphan', 'orphaned', 'failed_lta_status_update', 'download_size') vals = tuple(self.__getattribute__(v) for v in attr_tup) cols = '({})'.format(','.join(attr_tup)) log_sql = '' try: with db_instance() as db: log_sql = db.cursor.mogrify(sql, (db_extns.AsIs(cols), vals, self.id)) db.execute(sql, (db_extns.AsIs(cols), vals, self.id)) db.commit() logger.info('\n*** Saved updates to scene id: {}, name:{}\n' 'sql: {}\n args: {}\n***' .format(self.id, self.name, log_sql, zip(attr_tup, vals))) except DBConnectException as e: logger.debug("Error saving scene: {}\n" "sql: {}".format(e.message, log_sql)) raise SceneException(e) new = Scene.where({'id': self.id})[0] for att in attr_tup: self.__setattr__(att, new.__getattribute__(att))
def update_system_status(params): if params.keys().sort() is not ['system_message_title', 'system_message_body', 'display_system_message'].sort(): return {'msg': 'Only 3 params are valid, and they must be present:' 'system_message_title, system_message_body,' 'display_system_message'} sql_dict = {'msg.system_message_title': params['system_message_title'], 'msg.system_message_body': params['system_message_body'], 'system.display_system_message': params['display_system_message']} sql = "" for k, v in sql_dict.iteritems(): sql += "update ordering_configuration set value = '{0}' where key = '{1}';".format(v, k) try: with db_instance() as db: db.execute(sql) db.commit() except DBConnectException as e: logger.debug("error updating system status: {}".format(e)) return {'msg': "error updating database: {}".format(e.message)} return True
def bulk_update(cls, ids=None, updates=None): """ Update a list of scenes with :param ids: ids of scenes to update :param updates: attributes to update :return: True """ if not isinstance(ids, (list, tuple)): raise TypeError('Scene.bulk_update ids should be a list') if not isinstance(updates, dict): raise TypeError('Scene.bulk_update updates should be a dict') sql = 'UPDATE ordering_scene SET %s = %s WHERE id in %s' fields = '({})'.format(','.join(updates.keys())) vals = tuple(updates.values()) ids = tuple(ids) if ",)" in sql: sql = sql.replace(",)", ")") log_sql = '' try: with db_instance() as db: log_sql = db.cursor.mogrify(sql, (db_extns.AsIs(fields), vals, ids)) logger.info('\n*** Bulk Updating scenes: \n' + log_sql + "\n\***\n") db.execute(sql, (db_extns.AsIs(fields), vals, ids)) db.commit() except DBConnectException as e: logger.debug('Error scene bulk_update: {}\nSQL: {}' .format(e.message, log_sql)) raise SceneException(e) return True
def user_email(self): """ Retrieve the email address associated with this order :return: email address """ sql = 'select email from auth_user where id = %s' ret = None log_sql = '' try: with db_instance() as db: log_sql = db.cursor.mogrify(sql, (self.user_id,)) logger.info('order.py user_email: {}'.format(log_sql)) db.select(sql, self.user_id) ret = db[0]['email'] except DBConnectException as e: logger.debug('Error retrieving user_email: {}' .format(log_sql)) raise OrderException(e) return ret
def __init__(self, id=None, name=None, note=None, order_id=None, product_distro_location=None, product_dload_url=None, cksum_distro_location=None, cksum_download_url=None, status=None, processing_location=None, completion_date=None, log_file_contents=None, ee_unit_id=None, tram_order_id=None, sensor_type=None, job_name=None, retry_after=None, retry_limit=None, retry_count=None, reported_orphan=None, orphaned=None, download_size=None, failed_lta_status_update=None): """ Initialize the Scene object with all the information for it from the database All parameters are directly related to DB columns :param name: scene or collection id :param note: text :param order_id: ordering_order.id of associated Order :param product_distro_location: final location :param product_dload_url: access point for users :param cksum_distro_location: checksum location :param cksum_download_url: access point for users :param status: current processing status :param processing_location: Hadoop node doing the processing :param completion_date: date when processing completed :param log_file_contents: Hadoop log file :param ee_unit_id: EarthExplorer ID :param tram_order_id: LTA tram :param sensor_type: landsat/modis/plot :param job_name: Hadoop job name :param retry_after: when to retry :param retry_limit: max number of retry attempts :param retry_count: retry attempts :param reported_orphan: time reported missing hadoop :param orphaned: missing hadoop job :param download_size: size of final product download :param failed_lta_status_update: status update not yet delivered to LTA """ self.name = name self.note = note self.order_id = order_id self.product_distro_location = product_distro_location self.product_dload_url= product_dload_url self.cksum_distro_location = cksum_distro_location self.cksum_download_url = cksum_download_url self.status = status self.processing_location = processing_location self.completion_date = completion_date self.log_file_contents = log_file_contents self.ee_unit_id = ee_unit_id self.tram_order_id = tram_order_id self.sensor_type = sensor_type self.job_name = job_name self.retry_after = retry_after self.retry_limit = retry_limit self.retry_count = retry_count self.reported_orphan = reported_orphan self.orphaned = orphaned self.download_size = download_size self.failed_lta_status_update = failed_lta_status_update if id: # no need to query the DB again self.id = id else: with db_instance() as db: sql = ('select id ' 'from ordering_scene where ' 'name = %s ' 'and order_id = %s') db.select(sql, (self.name, self.order_id)) if db: self.id = db[0]['id'] else: self.id = None
def cleanup(self): sql = "DELETE from auth_user where id > 0;" with db_instance() as db: db.execute(sql) db.commit()
def create(cls, params): """ Place a new order into the system :param params: dict of required parameters to be used params = {'product_opts': {dictionary object of the order received} 'orderid': id generated from generate_order_id 'user_id': EE user id 'order_type': typically 'level2_ondemand' 'status': 'ordered' 'note': user notes 'ee_order_id': earth explorer order id, or '' 'order_source': 'espa' or 'ee' 'order_date': date time string 'priority': legacy item, should be 'normal' 'email': user's contact email 'product_options': legacy column} :return: order object """ opts = params['product_opts'] params['product_opts'] = json.dumps(params['product_opts']) sql = ('INSERT INTO ordering_order ' '(orderid, user_id, order_type, status, note, ' 'product_opts, ee_order_id, order_source, order_date, ' 'priority, email, product_options) ' 'VALUES (%(orderid)s, %(user_id)s, %(order_type)s, ' '%(status)s, %(note)s, %(product_opts)s, ' '%(ee_order_id)s, %(order_source)s, %(order_date)s, ' '%(priority)s, %(email)s, %(product_options)s)') logger.info('Order creation parameters: {}'.format(params)) log_sql = '' try: with db_instance() as db: log_sql = db.cursor.mogrify(sql, params) logger.info('New order complete SQL: {}' .format(log_sql)) db.execute(sql, params) db.commit() except DBConnectException as e: logger.debug('Error creating new order: {}\n' 'sql: {}'.format(e.message, log_sql)) raise OrderException(e) order = Order.find(params['orderid']) # Let the load_ee_order method handle the scene injection # as there is special logic for interacting with LTA if params['ee_order_id']: return order sensor_keys = sensor.SensorCONST.instances.keys() bulk_ls = [] for key in opts: if key in sensor_keys: sensor_type = '' item1 = opts[key]['inputs'][0] if isinstance(sensor.instance(item1), sensor.Landsat): sensor_type = 'landsat' elif isinstance(sensor.instance(item1), sensor.Modis): sensor_type = 'modis' for s in opts[key]['inputs']: scene_dict = {'name': s, 'sensor_type': sensor_type, 'order_id': order.id, 'status': 'submitted', 'ee_unit_id': None} bulk_ls.append(scene_dict) if 'plot_statistics' in opts and opts['plot_statistics']: scene_dict = {'name': 'plot', 'sensor_type': 'plot', 'order_id': order.id, 'status': 'submitted', 'ee_unit_id': None} bulk_ls.append(scene_dict) try: Scene.create(bulk_ls) except SceneException as e: logger.debug('Order creation failed on scene injection, ' 'order: {}\nexception: {}' .format(order.orderid, e.message)) with db_instance() as db: db.execute('delete ordering_order where id = %s', order.id) db.commit() raise OrderException(e) return order