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 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 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 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 _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 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 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 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 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 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
