async def cdr(pool: aiopg.Pool, request: schema.CDRRequest) -> dict: async with pool.acquire() as conn: async with conn.cursor(cursor_factory=DictCursor) as cur: await cur.execute( "SELECT * FROM tenants WHERE uuid = %s;", [request.tenant_uuid] ) tenant = await cur.fetchone() if tenant is None: return {"success": False, "cdr": None} cdr = cdr_schema.CDRCreate( tenant_uuid=tenant['uuid'], source_ip=request.source_ip, source_port=request.source_port, from_uri=request.from_uri, to_uri=request.to_uri, call_id=request.call_id, call_start=request.call_start, duration=request.duration, ) await cur.execute( "INSERT INTO cdrs (tenant_uuid, source_ip, source_port, from_uri, to_uri, call_id, call_start, duration) " "VALUES (%s, %s, %s, %s, %s, %s, %s, %s);", [ cdr.tenant_uuid, cdr.source_ip, cdr.source_port, cdr.from_uri, cdr.to_uri, cdr.call_id, cdr.call_start, cdr.duration, ], ) return {"success": True, "cdr": cdr}
async def query(pool: aiopg.Pool, query: str, query_tuple: tuple = tuple()) -> None: """ Run a query without getting the result :param pool: :param query: :param query_tuple: :return: """ connection = await pool.acquire() try: cursor = await connection.cursor() await cursor.execute(query, query_tuple) finally: pool.release(connection)
async def dbtext_uacreg(pool: aiopg.Pool) -> schema.DBText: content = [] content.append( " ".join( [ "id(int)", "l_uuid(string)", "l_username(string)", "l_domain(string)", "r_username(string)", "r_domain(string)", "realm(string)", "auth_username(string)", "auth_password(string)", "auth_proxy(string)", "expires(int)", "flags(int)", "reg_delay(int)", "socket(string)", ] ) + "\n" ) async with pool.acquire() as conn: async with conn.cursor(cursor_factory=DictCursor) as cur: await cur.execute( "SELECT row_number() OVER () AS carrier_id, * FROM carrier_trunks WHERE registered = true ORDER BY id;" ) async for carrier_trunk in cur: content.append( ":".join( map( lambda x: x.replace(":", "\\:"), [ "%s" % carrier_trunk["carrier_id"], "%s" % carrier_trunk['id'], carrier_trunk['auth_username'], carrier_trunk['from_domain'], carrier_trunk['auth_username'], carrier_trunk['from_domain'], carrier_trunk['realm'], carrier_trunk['auth_username'], carrier_trunk['auth_password'], "sip:%s" % carrier_trunk['registrar_proxy'], str(carrier_trunk['expire_seconds']), "16", "0", "", ], ) ) + "\n" ) return schema.DBText(content="".join(content))
async def update_statistics(pool: aiopg.Pool, num_urls_checked: int) -> None: async with pool.acquire() as conn: async with conn.cursor() as cur: await cur.execute( """ UPDATE statistics SET num_urls_checked = num_urls_checked + %(num_urls_checked)s """, { 'num_urls_checked': num_urls_checked, } )
async def query_with_result( pool: aiopg.Pool, query: str, query_tuple: tuple = tuple()) -> AsyncIterable[Union[Tuple, None]]: """ Run a query and get the result piece-by-piece through an asynchronous generator :param pool: :param query: :param query_tuple: :return: """ connection = await pool.acquire() try: cursor = await connection.cursor() await cursor.execute(query, query_tuple) record = await cursor.fetchone() while record is not None: yield record record = await cursor.fetchone() finally: pool.release(connection)
async def update_url_status( pool: aiopg.Pool, url: str, check_time: datetime.datetime, next_check_time: datetime.datetime, priority_next_check_time: datetime.datetime, ipv4_status: Optional[UrlStatus], ipv6_status: Optional[UrlStatus] ) -> None: async with pool.acquire() as conn: async with conn.cursor() as cur: await cur.execute( """ UPDATE links SET next_check = CASE WHEN priority THEN %(priority_next_check_time)s ELSE %(next_check_time)s END, last_checked = %(check_time)s, ipv4_last_success = CASE WHEN %(ipv4_success)s THEN %(check_time)s ELSE ipv4_last_success END, ipv4_last_failure = CASE WHEN NOT %(ipv4_success)s THEN %(check_time)s ELSE ipv4_last_failure END, ipv4_success = %(ipv4_success)s, ipv4_status_code = %(ipv4_status_code)s, ipv4_permanent_redirect_target = %(ipv4_permanent_redirect_target)s, ipv6_last_success = CASE WHEN %(ipv6_success)s THEN %(check_time)s ELSE ipv6_last_success END, ipv6_last_failure = CASE WHEN NOT %(ipv6_success)s THEN %(check_time)s ELSE ipv6_last_failure END, ipv6_success = COALESCE(%(ipv6_success)s, ipv6_success), ipv6_status_code = COALESCE(%(ipv6_status_code)s, ipv6_status_code), ipv6_permanent_redirect_target = COALESCE(%(ipv6_permanent_redirect_target)s, ipv6_permanent_redirect_target) WHERE url = %(url)s """, { 'url': url, 'check_time': check_time, 'next_check_time': next_check_time, 'priority_next_check_time': priority_next_check_time, 'ipv4_success': ipv4_status.success if ipv4_status is not None else None, 'ipv4_status_code': ipv4_status.status_code if ipv4_status is not None else None, 'ipv4_permanent_redirect_target': ipv4_status.permanent_redirect_target if ipv4_status is not None else None, 'ipv6_success': ipv6_status.success if ipv6_status is not None else None, 'ipv6_status_code': ipv6_status.status_code if ipv6_status is not None else None, 'ipv6_permanent_redirect_target': ipv6_status.permanent_redirect_target if ipv6_status is not None else None, } )
async def iterate_urls_to_recheck(pool: aiopg.Pool) -> AsyncIterator[str]: async with pool.acquire() as conn: async with conn.cursor() as cur: await cur.execute( """ WITH all_urls AS ( SELECT url, row_number() OVER(PARTITION BY substring(url from '.*://([^/]*)')) AS num_for_host FROM links WHERE refcount > 0 AND next_check < now() ) SELECT url FROM all_urls WHERE num_for_host <= 100 LIMIT 10000 """ ) async for row in cur: yield row[0]