def post_update(product, name, body):
kairosdb_url = os.getenv('KAIROSDB_URL')
with dbconn() as conn:
cur = conn.cursor()
cur.execute(
'SELECT ds_definition FROM zsm_data.data_source WHERE '
'ds_product_id = (SELECT p_id FROM zsm_data.product WHERE p_slug = %s) AND ds_sli_name = %s',
(product, name))
row = cur.fetchone()
if not row:
return 'Not found', 404
definition, = row
start = body.get('start', MAX_QUERY_TIME_SLICE)
end = body.get('end')
if end and end >= start:
raise ProblemException(
title='Invalid "end" field value',
detail='"end" field should be less than "start" field value')
count = process_sli(product, name, definition, kairosdb_url, start, end,
'minutes', database_uri)
return {'count': count}
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():
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 delete(slo_id):
with dbconn() as conn:
cur = conn.cursor()
cur.execute('''DELETE FROM zsm_data.service_level_indicator_target WHERE slit_slo_id = %s''', (slo_id,))
cur.execute('''DELETE FROM zsm_data.service_level_objective WHERE slo_id = %s''', (slo_id,))
conn.commit()
return (NoContent, 204,) if cur.rowcount else (NoContent, 404,)
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 add(product_group):
with dbconn() as conn:
cur = conn.cursor()
cur.execute('''INSERT INTO zsm_data.product_group (pg_name, pg_department, pg_slug) VALUES (%s, %s, %s)''',
(product_group['name'], product_group['department'], slugger(product_group['name'])))
conn.commit()
cur.close()
return NoContent, 201
def delete(pg_slug: str):
with dbconn() as conn:
cur = conn.cursor()
cur.execute(
'''DELETE FROM zsm_data.product_group WHERE pg_slug = %s''',
(pg_slug, ))
conn.commit()
return (NoContent, 404) if not cur.rowcount else (NoContent, 200)
def get_service_level_indicators(product, name, time_from=None, time_to=None):
# TODO: allow filtering by time_from/time_to
with dbconn() as conn:
cur = conn.cursor()
cur.execute('''SELECT sli_timestamp, sli_value FROM zsm_data.service_level_indicator
WHERE sli_product_id = (SELECT p_id FROM zsm_data.product WHERE p_slug = %s) AND sli_name = %s
AND sli_timestamp >= date_trunc(\'day\', \'now\'::TIMESTAMP - INTERVAL \'7 days\')
ORDER BY 1''', (product, name))
return cur.fetchall()
def delete(product, sli_name):
with dbconn() as conn:
cur = conn.cursor()
cur.execute(
'''
DELETE FROM zsm_data.data_source WHERE ds_product_id =
(SELECT p_id FROM zsm_data.product WHERE p_slug = %s) AND ds_sli_name = %s
''', (product, sli_name))
conn.commit()
return (NoContent, 200) if cur.rowcount != 0 else (NoContent, 404)
def add(product):
with dbconn() as conn:
cur = conn.cursor()
cur.execute(
'''INSERT INTO zsm_data.product (p_name, p_slug, p_product_group_id)
VALUES (%s, %s, (SELECT pg_id FROM zsm_data.product_group WHERE pg_slug = %s))''',
(product['name'], slugger(product['name']), product['product_group']))
conn.commit()
cur.close()
return NoContent, 201
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 add(product, slo):
with dbconn() as conn:
cur = conn.cursor()
cur.execute('INSERT INTO zsm_data.service_level_objective (slo_title, slo_product_id) '
'VALUES (%s, (SELECT p_id FROM zsm_data.product WHERE p_slug= %s)) RETURNING slo_id',
(slo['title'], product))
slo_id = cur.fetchone()[0]
for t in slo['targets']:
cur.execute('INSERT INTO zsm_data.service_level_indicator_target '
'(slit_slo_id,slit_sli_name,slit_unit,slit_from,slit_to)'
'VALUES (%s, %s, %s, %s, %s)', (slo_id, t['sli_name'], t['unit'], t.get('from'), t.get('to')))
conn.commit()
return NoContent, 201
def delete(product):
with dbconn() as conn:
cur = conn.cursor()
cur.execute('''DELETE FROM zsm_data.product WHERE p_slug = %s''',
(product, ))
conn.commit()
return (
NoContent,
200,
) if cur.rowcount else (
NoContent,
404,
)
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 add(product, data_source):
with dbconn() as conn:
cur = conn.cursor()
cur.execute(
'''
INSERT INTO zsm_data.data_source (ds_product_id, ds_sli_name, ds_definition)
VALUES ((SELECT p_id FROM zsm_data.product WHERE p_slug = %s), %s, %s)
''', (
product,
data_source['sli_name'],
json.dumps(data_source['definition']),
))
conn.commit()
return NoContent, 201
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
def update_service_level_objectives(product):
with dbconn() as conn:
cur = conn.cursor()
cur.execute(
'''SELECT f.sli_name AS sli_name, seconds_ago FROM zsm_data.service_level_indicators_full AS f
LEFT OUTER JOIN zsm_data.sli_latest_data AS l ON f.sli_name=l.sli_name AND f.p_id=l.p_id
WHERE f.p_slug = %s''', (product, ))
rows = cur.fetchall()
res = {}
for row in rows:
start_time = ((row.seconds_ago // 60) +
5) if row.seconds_ago else MAX_QUERY_TIME_SLICE
res[row.sli_name] = {'start': start_time}
response = post_update(product, row.sli_name, res[row.sli_name])
res[row.sli_name]['count'] = response['count']
return res
def add(product_group):
with dbconn() as conn:
cur = conn.cursor()
try:
cur.execute(
'''INSERT INTO zsm_data.product_group (pg_name, pg_department, pg_slug) VALUES (%s, %s, %s)''',
(product_group['name'], product_group['department'],
slugger(product_group['name'])))
conn.commit()
cur.close()
return NoContent, 201
except IntegrityError:
return connexion.problem(
status=400,
title='Product Group already exists',
detail='Product group with name: "{}" already exists!'.format(
product_group['name']))
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
