def list_machines(provider_name, at=None, offset=0, limit=None, order=None):
if at:
query = """SELECT *
FROM machines_history
WHERE provider_code = %s
AND start_time <= %s
AND (end_time > %s OR end_time IS NULL)"""
else:
query = "SELECT * FROM machines WHERE provider_code = %s"
if order:
query += " " + order.to_sql({
"name": "machine_code",
"cores": "nbr_cores",
"ram": "ram_size",
"availability": "nbr_available"
})
if limit:
query += " LIMIT " + str(int(limit))
if offset:
query += " OFFSET " + str(int(offset))
g_db = core.api_util.DatabaseContext.get_conn()
if at:
result = g_db.execute(query, [provider_name, at, at]).fetchall()
else:
result = g_db.execute(query, [provider_name]).fetchall()
if not result:
return []
return pg_util.all_to_dict(result)
def list_machine_operations(provider_name,
machine_code,
user_rank=users.RANK_BRONZE,
at=None):
g_db = core.api_util.DatabaseContext.get_conn()
if not at:
query = """SELECT o.*
FROM operations AS o
LEFT JOIN operation_machine AS om
ON o.operation_name = om.operation_name
LEFT JOIN machines AS m
ON om.machine_uid = m.uid
AND o.provider_code = m.provider_code
WHERE o.provider_code = %s
AND o.user_rank = %s
AND m.machine_code = %s"""
result = g_db.execute(
query, [provider_name, user_rank, machine_code]).fetchall()
if not result:
return []
return pg_util.all_to_dict(result)
else:
query = """SELECT o.*
FROM operations_history AS o
LEFT JOIN operation_machine AS om
ON o.operation_name = om.operation_name
LEFT JOIN machines_history AS m
ON om.machine_uid = m.uid
AND o.provider_code = m.provider_code
WHERE o.provider_code = %s
AND o.user_rank = %s
AND m.machine_code = %s
AND o.start_time <= %s
AND (o.end_time > %s OR o.end_time IS NULL)
AND m.start_time <= %s
AND (m.end_time > %s OR m.end_time IS NULL)"""
g_db = core.api_util.DatabaseContext.get_conn()
if at:
result = g_db.execute(
query, [provider_name, user_rank, machine_code, at, at, at, at
]).fetchall()
else:
result = g_db.execute(
query, [provider_name, user_rank, machine_code]).fetchall()
if not result:
return []
return pg_util.all_to_dict(result)
def list_files(user_id, project_codename, include_deleted=False):
if include_deleted:
query = "SELECT * FROM project_files WHERE project_uid = %s"
else:
query = "SELECT * FROM project_files WHERE project_uid = %s AND delete_date IS NULL"
g_db = core.api_util.DatabaseContext.get_conn()
return pg_util.all_to_dict(
g_db.execute(query, [project_codename]).fetchall())
def list_tasks():
"""
List all pending tasks
:return: All the tasks in the queue
:rtype: list[dict[str, any]]
"""
g_db = core.api_util.DatabaseContext.get_conn()
return pg_util.all_to_dict(g_db.execute("SELECT * FROM task_queue").fetchall())
def search(term, include_deleted=False):
if include_deleted:
query = """SELECT DISTINCT ON ("uid") *
FROM projects_history
WHERE uid LIKE %s
ORDER BY uid, end_time DESC NULLS FIRST"""
else:
query = """SELECT DISTINCT ON ("uid") *
FROM projects
WHERE uid LIKE %s"""
g_db = core.api_util.DatabaseContext.get_conn()
return pg_util.all_to_dict(
g_db.execute(query, ['%' + term + '%']).fetchall())
def search(term, include_deleted=False):
query_args = ['%' + term + '%', '%' + term + '%']
query = """SELECT id, login, email, user_rank, create_date, delete_date
FROM users
WHERE (
login LIKE %s
OR email LIKE %s"""
if type_util.ll_int(term):
query += " OR id = %s "
query_args.append(int(term))
query += ")"
if include_deleted:
query += " AND delete_date IS NULL"
g_db = core.api_util.DatabaseContext.get_conn()
return pg_util.all_to_dict(g_db.execute(query, query_args).fetchall())
def get_benefits(from_date, to_date, currency=None, user_id=None):
conf = core.api_util.get_conf()
default_currency = conf.get('currency', 'main_currency')
if currency is None:
currency = default_currency
zephycoins_to_currency = 1.0 / currencies.get_currencies_to_zc(
)[currency] / core.api_util.PRICE_PRECISION
query_args = [
currency, core.api_util.OPENFOAM_DONATION_RATIO,
core.api_util.PRICE_PRECISION
]
currency_sql = ""
for specific_currency, rate in currencies.get_currencies_ratio(
currency).items():
currency_sql += " WHEN %s THEN %s "
query_args.extend([specific_currency, rate])
query_args.extend([zephycoins_to_currency, from_date, to_date] * 2)
query = """SELECT U.login,
U.id AS user_id,
U.email AS email,
COALESCE(T3.factured_computation, 0) AS factured_computation,
COALESCE(T3.computation_cost, 0) AS computation_cost,
COALESCE(T3.openfoam_commission, 0) AS openfoam_commission,
COALESCE(T2.factured_storage, 0) AS factured_storage,
(
COALESCE(T3.factured_computation, 0) - COALESCE(T3.computation_cost, 0) -
COALESCE(T3.openfoam_commission, 0) + COALESCE(T2.factured_storage, 0)
) AS benefits,
%s AS currency
FROM users AS U
LEFT JOIN (
SELECT T1.user_id,
SUM(T1.price) AS factured_computation,
SUM(T1.cost) AS computation_cost,
SUM(T1.price) * %s AS openfoam_commission
FROM (
SELECT J.user_id,
(J.nbr_machines
* GREATEST(
PCH.min_sec_granularity,
(CEIL(EXTRACT(EPOCH FROM (J.end_time - J.start_time))::numeric / PCH.sec_granularity) * PCH.sec_granularity)::integer
)
* PCH.cost_per_sec::numeric
/ %s * CASE PCH.currency """ + currency_sql + """ ELSE 0 END
) AS cost,
(SUM(UA.amount::numeric) * %s * -1 ) AS price
FROM user_accounts AS UA
LEFT JOIN jobs AS J ON J.id = UA.job_id
LEFT JOIN provider_costs_history AS PCH ON PCH.id = J.provider_cost_id
LEFT JOIN machine_prices_history AS MPH ON MPH.id = J.machine_price_id
WHERE UA.computing_start IS NOT NULL
AND UA.date > %s
AND UA.date <= %s
GROUP BY J.id, PCH.id, MPH.id, J.user_id
) AS T1
GROUP BY T1.user_id
) AS T3 ON T3.user_id = U.id
FULL JOIN (
SELECT UA.user_id, SUM(UA.amount::numeric * %s * -1 ) AS factured_storage
FROM user_accounts AS UA
WHERE UA.computing_start IS NULL
AND UA.job_id IS NOT NULL
AND UA.date > %s
AND UA.date <= %s
GROUP BY UA.user_id
) AS T2 ON T3.user_id = T2.user_id"""
if user_id is not None:
query += " WHERE U.id = %s"
query_args.append(user_id)
else:
query += """ WHERE T3.factured_computation IS NOT NULL
AND T3.computation_cost IS NOT NULL
AND T3.openfoam_commission IS NOT NULL
AND T2.factured_storage IS NOT NULL"""
g_db = core.api_util.DatabaseContext.get_conn()
with g_db.cursor() as cur:
return pg_util.all_to_dict(cur.execute(query, query_args).fetchall())
def list_unfinished_jobs():
unfinished_statuses = [JOB_STATUS_CANCELED, JOB_STATUS_KILLED, JOB_STATUS_FINISHED, JOB_STATUS_CANCELING]
query = "SELECT j.* FROM jobs AS j WHERE status NOT IN ("+", ".join(["%s"]*len(unfinished_statuses))+")"
g_db = core.api_util.DatabaseContext.get_conn()
result = g_db.execute(query, unfinished_statuses).fetchall()
return pg_util.all_to_dict(result)
def update_currency_exchange_rates(currency_api_url, currency_api_token):
# Load existing values
g_db = core.api_util.DatabaseContext.get_conn()
curr_tmp = pg_util.all_to_dict(
g_db.execute("""SELECT DISTINCT currency, is_fixed, to_zcoins
FROM currency_exchange_rates"""
).fetchall())
currencies = []
fixed_currencies_values = {}
ref_currency = None
for currency_info in curr_tmp:
currencies.append(currency_info["currency"])
if currency_info["is_fixed"] == True:
ref_currency = currency_info["currency"]
fixed_currencies_values[
currency_info["currency"]] = currency_info["to_zcoins"]
if ref_currency is None:
raise RuntimeError("No fixed currency in database !!!")
# Format params for api call
if "://" in currency_api_url:
currency_api_url = str(currency_api_url).split("://", 1)[1]
currency_api_url = "http://" + currency_api_url.rstrip("/") + "/api/live"
currency_param = ",".join([name_to_symbol(c) for c in currencies])
# Do the api call and check the result
result = requests.get(currency_api_url,
params={
"access_key": currency_api_token,
"currencies": currency_param
})
if result.status_code < 200 or result.status_code > 299:
raise RuntimeError(
"Unable to get the currency exchange rates: api call failed")
result = result.json()
if result['success'] != True:
raise RuntimeError(
"Unable to get the currency exchange rates: api call failed")
# map back the result
source_symbol = result['source']
source = symbol_to_name(source_symbol)
api_result = {}
for key, value in result['quotes'].items():
api_result[symbol_to_name(key[len(source_symbol):])] = value
# calculate the new values
currencies_to_zcoins = {}
for currency, to_source in api_result.items():
if currency in fixed_currencies_values.keys():
currencies_to_zcoins[currency] = fixed_currencies_values[currency]
if source not in fixed_currencies_values.keys():
currencies_to_zcoins[source] = fixed_currencies_values[
ref_currency] * api_result[ref_currency]
for currency, to_source in api_result.items():
if currency == source or currency in fixed_currencies_values.keys():
continue
currencies_to_zcoins[
currency] = currencies_to_zcoins[source] / api_result[currency]
# Save the results
for currency, to_zcoins in currencies_to_zcoins.items():
if util.float_equals(to_zcoins, 0) or to_zcoins < 0:
log.error("A currency should never be zero or less:")
log.error("DEBUG currencies: " +
json.dumps(currencies, indent=4, sort_keys=True))
log.error(
"DEBUG fixed_currencies_values: " +
json.dumps(fixed_currencies_values, indent=4, sort_keys=True))
log.error("DEBUG api_result: " +
json.dumps(api_result, indent=4, sort_keys=True))
log.error(
"DEBUG currencies_to_zcoins: " +
json.dumps(currencies_to_zcoins, indent=4, sort_keys=True))
log.error("DEBUG source: " +
json.dumps(source, indent=4, sort_keys=True))
log.error("DEBUG currency: " +
json.dumps(currency, indent=4, sort_keys=True))
log.error("DEBUG to_zcoins: " +
json.dumps(to_zcoins, indent=4, sort_keys=True))
raise RuntimeError("A currency should never be zero or less")
g_db.execute(
"UPDATE currency_exchange_rates SET to_zcoins = %s WHERE currency = %s",
[to_zcoins, currency])
def get_currencies_to_zc():
g_db = core.api_util.DatabaseContext.get_conn()
currencies = pg_util.all_to_dict(
g_db.execute("SELECT * FROM currency_exchange_rates").fetchall())
return dict({c["currency"]: c["to_zcoins"] for c in currencies})
def list_operations(provider_name,
user_rank=users.RANK_BRONZE,
include_machines=False,
at=None,
offset=0,
limit=None,
order=None):
if not include_machines:
if not at:
query = """SELECT * FROM operations
WHERE provider_code = %s
AND user_rank = %s"""
else:
query = """SELECT * FROM operations_history
WHERE provider_code = %s
AND user_rank = %s
AND start_time <= %s
AND (end_time > %s OR end_time IS NULL)"""
elif not at:
query = """SELECT o.*, ARRAY_REMOVE(ARRAY_AGG(m.machine_code), NULL) AS machines
FROM operations AS o
LEFT JOIN operation_machine AS om
ON o.operation_name = om.operation_name
LEFT JOIN machines AS m
ON om.machine_uid = m.uid
AND o.provider_code = m.provider_code
WHERE o.provider_code = %s
AND o.user_rank = %s
GROUP BY o.id, o.provider_code, o.operation_name, o.user_rank"""
else:
query = """SELECT o.*, ARRAY_REMOVE(ARRAY_AGG(m.machine_code), NULL) AS machines
FROM operations_history AS o
LEFT JOIN operation_machine AS om
ON o.operation_name = om.operation_name
LEFT JOIN machines_history AS m
ON om.machine_uid = m.uid
AND o.provider_code = m.provider_code
WHERE o.provider_code = %s
AND o.user_rank = %s
AND o.start_time <= %s
AND (o.end_time > %s OR o.end_time IS NULL)
AND m.start_time <= %s
AND (m.end_time > %s OR m.end_time IS NULL)
GROUP BY o.id, o.provider_code, o.operation_name, o.user_rank"""
if order:
if include_machines:
query += " " + order.to_sql({
"name": "operation_name",
"fixed_price": "fixed_cost",
"machine_limit": "cluster_limit"
})
else:
query += " " + order.to_sql({
"name": "o.operation_name",
"fixed_price": "o.fixed_cost",
"machine_limit": "o.cluster_limit"
})
if limit:
query += " LIMIT " + str(int(limit))
if offset:
query += " OFFSET " + str(int(offset))
g_db = core.api_util.DatabaseContext.get_conn()
if not include_machines:
if not at:
return g_db.execute(query, [provider_name, user_rank]).fetchall()
else:
return g_db.execute(query,
[provider_name, user_rank, at, at]).fetchall()
if not at:
req = g_db.execute(query, [provider_name, user_rank]).fetchall()
else:
req = g_db.execute(
query, [provider_name, user_rank, at, at, at, at]).fetchall()
return pg_util.all_to_dict(req)