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)