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]