def get_api_test_item_record(itemid):
select = """
select item.*,
tags.names as tags
from item
join lateral (
select array_agg(tag.name) as names
from tag
join tagitem on tagitem.item_id=item.id and tagitem.tag_id=tag.id
) tags on true
where id=%(item)s"""
results = api.Results()
with app.dbconn() as conn:
results.tables["items", True] = api.sql_tab2(conn, select,
{"item": itemid})
rawdata = results.tables["items"]
columns = api.tab2_columns_transform(rawdata[0],
insert=[("price", "revenue_level")
])
def xform_add_rev_level(oldrow, row):
level = int(math.log10(row.price))
if level > 4:
row.revenue_level = "\u221e"
else:
row.revenue_level = "EDCBA"[level]
rows = api.tab2_rows_transform(rawdata, columns, xform_add_rev_level)
results.tables["items", True] = columns, rows
return results.json_out()
def get_api_test_sql_exception01():
select = """
select *
from nonexistent
"""
results = api.Results()
with app.dbconn() as conn:
results.tables["data", True] = api.sql_tab2(conn, select)
def get_api_test_item_tag_list():
select = """
select *
from tag
"""
results = api.Results()
with app.dbconn() as conn:
results.tables["tags", True] = api.sql_tab2(conn, select)
return results.json_out()
def api_request_sleep(request):
duration = api.parse_float(request.query.get("duration"))
select = """
select pg_sleep(%s)
"""
results = api.Results()
with app.dbconn() as conn:
results.tables["sleep"] = api.sql_tab2(conn, select, (duration, ))
return results.json_out()
def get_api_test_items_list():
results = api.Results()
with app.dbconn() as conn:
cm = api.ColumnMap(
id=api.cgen.item.surrogate(),
name=api.cgen.item.name(),
price=api.cgen.currency_usd(),
revenue_level=api.cgen.auto(label="rev lev"),
)
results.tables["items", True] = api.sql_tab2(conn,
"select * from item",
column_map=cm)
return results.json_out()
def get_api_database_relation_sizes():
select = """
select nspname || '.' || relname as relation,
pg_size_pretty(pg_relation_size(c.oid)) as size,
pg_relation_size(c.oid) as size_bytes
from pg_class c
left join pg_namespace n on (n.oid = c.relnamespace)
where nspname not in ('pg_catalog', 'information_schema')
order by pg_relation_size(c.oid) desc
"""
results = api.Results(default_title=True)
with app.dbconn() as conn:
results.tables["sizes", True] = api.sql_tab2(conn, select)
return results.json_out()
def get_api_database_connections():
select = """
SELECT a.datname, a.pid, a.usename, a.application_name,
a.client_addr, a.client_hostname, a.client_port,
a.backend_start at time zone 'utc' as backend_start,
a.xact_start at time zone 'utc' as xact_start,
a.query_start at time zone 'utc' as query_start,
a.state,
a.query
FROM pg_stat_activity a;
"""
results = api.Results(default_title=True)
with app.dbconn() as conn:
results.tables["connections", True] = api.sql_tab2(conn, select)
return results.json_out()
def get_api_test_item_new():
select = """
select *
from item
where false"""
results = api.Results()
with app.dbconn() as conn:
columns, rows = api.sql_tab2(conn, select)
def default_rows(key, row):
row.price = 12
rows = api.tab2_rows_default(columns, [None], default_rows)
results.tables["items", True] = columns, rows
return results.json_out()
def get_api_database_locks():
select = """
SELECT
a.datname,
c.relname,
l.transactionid,
l.mode,
l.GRANTED,
a.usename,
a.query,
a.query_start at time zone 'utc' as start_time,
--age(now(), a.query_start) AS "age",
a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
JOIN pg_class c ON c.oid = l.relation
ORDER BY a.query_start;
"""
results = api.Results(default_title=True)
with app.dbconn() as conn:
results.tables["locks", True] = api.sql_tab2(conn, select)
return results.json_out()