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