def get():
with dbconn() as conn:
cur = conn.cursor()
cur.execute('''SELECT pg_name, pg_slug, pg_department FROM zsm_data.product_group''')
rows = cur.fetchall()
res = [strip_column_prefix(r._asdict()) for r in rows]
return res
def add(product):
with dbconn() as conn:
cur = conn.cursor()
cur.execute(
'''SELECT * FROM zsm_data.product_group WHERE pg_slug = %s''',
(product['product_group'], ))
row = cur.fetchone()
if not row:
return connexion.problem(
status=404,
title='Product group not found',
detail='Can not find product group: {}'.format(
product['product_group']))
product_group = strip_column_prefix(row._asdict())
try:
cur.execute(
'''INSERT INTO zsm_data.product (p_name, p_slug, p_product_group_id) VALUES (%s, %s, %s)''',
(
product['name'],
slugger(product['name']),
product_group['id'],
))
conn.commit()
except IntegrityError:
return connexion.problem(
status=400,
title='Product Already Exists',
detail='Product with name: "{}" already exists!'.format(
product['name']))
return NoContent, 201
def get_service_level_objective_report(product, report_type):
with dbconn() as conn:
cur = conn.cursor()
cur.execute(
'''SELECT p.*, pg_name AS pg_product_group_name, pg_slug AS pg_product_group_slug, pg_department
FROM zsm_data.product p
JOIN zsm_data.product_group ON pg_id = p_product_group_id
WHERE p_slug = %s''', (product, ))
row = cur.fetchone()
if not row:
return 'Product not found', 404
product_data = strip_column_prefix(row._asdict())
service_level_objectives = get_service_level_objectives(product)
for slo in service_level_objectives:
days = collections.defaultdict(dict)
cur.execute(
'''
SELECT
date_trunc(\'day\', sli_timestamp) AS day,
sli_name AS name,
MIN(sli_value) AS min,
AVG(sli_value),
MAX(sli_value),
COUNT(sli_value),
(SELECT SUM(CASE b WHEN TRUE THEN 0 ELSE 1 END) FROM UNNEST(array_agg(sli_value BETWEEN
COALESCE(slit_from, \'-Infinity\') AND COALESCE(slit_to, \'Infinity\'))) AS dt(b)
) AS agg
FROM zsm_data.service_level_indicator
JOIN zsm_data.service_level_indicator_target ON slit_sli_name = sli_name
JOIN zsm_data.service_level_objective ON slo_id = slit_slo_id AND slo_id = %s
JOIN zsm_data.product ON p_id = slo_product_id AND p_slug = %s
WHERE
sli_timestamp >= date_trunc(\'day\', \'now\'::TIMESTAMP - INTERVAL \'7 days\') AND
sli_product_id = %s
GROUP BY date_trunc(\'day\', sli_timestamp), sli_name
''', (
slo['id'],
product,
product_data['id'],
))
rows = cur.fetchall()
for row in rows:
days[row.day.isoformat()][row.name] = {
'min': row.min,
'avg': row.avg,
'max': row.max,
'count': row.count,
'breaches': row.agg
}
slo['days'] = days
return {
'product': product_data,
'service_level_objectives': service_level_objectives
}
def get(product):
res = []
with dbconn() as conn:
cur = conn.cursor()
cur.execute('''SELECT slo_id, slo_title
FROM zsm_data.service_level_objective slo
JOIN zsm_data.product ON p_id = slo_product_id
WHERE p_slug = %s''', (product,))
rows = cur.fetchall()
for row in rows:
d = strip_column_prefix(row._asdict())
cur.execute(
'SELECT slit_from, slit_to, slit_sli_name, slit_unit FROM '
'zsm_data.service_level_indicator_target WHERE slit_slo_id = %s',
(row.slo_id,))
targets = cur.fetchall()
d['targets'] = [strip_column_prefix(r._asdict()) for r in targets]
res.append(d)
return res
def get(product):
with dbconn() as conn:
cur = conn.cursor()
cur.execute(
'''
SELECT p.p_name, p.p_slug, ds.ds_sli_name, ds.ds_definition FROM zsm_data.product AS p,
zsm_data.data_source AS ds WHERE p.p_id=ds.ds_product_id AND p.p_slug = %s
''', (product, ))
rows = cur.fetchall()
res = [strip_column_prefix(r._asdict()) for r in rows]
return res
def get_product(product):
with dbconn() as conn:
cur = conn.cursor()
cur.execute(
'''SELECT p_product_group_id, p_name, p_slug, p_delivery_team
FROM zsm_data.product WHERE p_slug = %s''', (product, ))
rows = cur.fetchall()
if len(rows) < 1:
return connexion.problem(
status=404,
title='Product not found',
detail='Product with id {} does not exist'.format(product))
else:
return strip_column_prefix(rows[0]._asdict())
def get(pg=None):
with dbconn() as conn:
cur = conn.cursor()
param_dict = {}
if pg:
param_dict['pg'] = pg
clause = ' AND pg_slug = %(pg)s '
else:
clause = ''
cur.execute(
'''SELECT p.*, pg_name AS pg_product_group_name, pg_slug AS pg_product_group_slug, pg_department
FROM zsm_data.product p, zsm_data.product_group pg WHERE pg_id = p_product_group_id'''
+ clause, param_dict)
rows = cur.fetchall()
res = [strip_column_prefix(r._asdict()) for r in rows]
return res
