def select_report_instance_diskspace(self, owner_id, report_id): with cursor() as cur: cur.execute( """SELECT report_instance_diskspace FROM report WHERE owner_id=? AND report_id=?""", [owner_id, report_id]) return cur.fetchone()['report_instance_diskspace']
def select_extra_ri_data(self, report_id, report_instance_id): with cursor() as cur: cur.execute("""SELECT extra_ri_data FROM report_instance WHERE report_id=? AND tags=? AND report_instance_id=?""", [report_id, [], report_instance_id]) row = cur.fetchone() return row['extra_ri_data'] if row else None
def select(self, report_id, report_instance_id, tags): tags = tags or [] with cursor() as cur: cur.execute("""SELECT * FROM report_instance WHERE report_id=? AND tags=? AND report_instance_id=?""", [report_id, tags, report_instance_id]) return postprocess_tags(cur.fetchone())
def update_from_rid_to_rid(self, report_id, series_id, tags, from_rid=undefined, to_rid=undefined): q = """UPDATE series_def SET {from_rid_clause} {to_rid_clause} WHERE report_id=? AND tags=? AND series_id=?""" params = [] fmt = {} if from_rid is not undefined: fmt['from_rid_clause'] = 'from_rid=?' params.append(from_rid) else: fmt['from_rid_clause'] = '' if to_rid is not undefined: fmt['to_rid_clause'] = 'to_rid=?' params.append(to_rid) else: fmt['to_rid_clause'] = '' params += [report_id, tags, series_id] with cursor() as cur: cur.execute(q.format(**fmt), params)
def select_report_instance_days(self, report_id, tags): with cursor() as cur: cur.execute("""SELECT day FROM report_instance_day WHERE report_id=? AND tags=?""", [report_id, tags]) dates = [row['day'] for row in cur.fetchall()] return [util.datetime_from_date(d) for d in dates]
def select_layout_by_report_multi(self, owner_id, report_id, tags, label, limit): with cursor() as cur: cur.execute("""SELECT * FROM layout_by_report WHERE owner_id=? AND report_id=? AND tags=? AND label=? LIMIT ?""", [owner_id, report_id, tags, label, limit]) return cur.fetchall()
def clear_all_series_defs(self, report_id, tags_powerset): with cursor() as cur: cur.execute( """UPDATE series_def SET from_rid=NULL, to_rid=NULL WHERE report_id=? AND tags IN {in_p}""".format( in_p=in_params(tags_powerset)), [report_id] + tags_powerset)
def select_multi(self, series_id, min_report_instance_id, max_report_instance_id, limit): q = """SELECT report_instance_id, json_value, header FROM series_value WHERE series_id=? {min_clause} {max_clause} ORDER BY report_instance_id DESC LIMIT ?""" params = [series_id] fmt = {} if min_report_instance_id is not None: fmt['min_clause'] = 'AND report_instance_id > ?' params.append(min_report_instance_id) else: fmt['min_clause'] = '' if max_report_instance_id is not None: fmt['max_clause'] = 'AND report_instance_id < ?' params.append(max_report_instance_id) else: fmt['max_clause'] = '' params.append(limit) with cursor() as cur: cur.execute(q.format(**fmt), params) return cur.fetchall()
def select_report_instance_diskspace_for_owner(self, owner_id): with cursor() as cur: cur.execute( """SELECT report_instance_diskspace FROM report_data_for_owner WHERE owner_id=?""", [owner_id]) row = cur.fetchone() return row['report_instance_diskspace'] if row else 0
def select_id_or_insert_multi(self, report_id, tags_series_spec_list): res = [] with cursor() as cur: for (tags, series_spec) in tags_series_spec_list: cur.execute( """SELECT series_id FROM series_def WHERE series_spec=? AND report_id=? AND tags=?""", [serialize.mjson(series_spec), report_id, tags]) row = cur.fetchone() if row: res.append(row['series_id']) continue series_id = gen_timeuuid() cur.execute( """INSERT INTO series_def (report_id, tags, series_id, series_spec, from_rid, to_rid) SELECT ?, ?, ?, ?, ?, ? WHERE NOT EXISTS (SELECT 1 FROM series_def WHERE report_id=? AND tags=? AND series_id=?)""", [ report_id, tags, series_id, serialize.mjson(series_spec), None, None, report_id, tags, series_id ]) if cur.lastrowid: res.append(series_id) continue cur.execute( """SELECT series_id FROM series_def WHERE series_spec=? AND report_id=? AND tags=?""", [serialize.mjson(series_spec), report_id, tags]) res.append(cur.fetchone()['series_id']) return res
def select(self, owner_id, dashboard_id): with cursor() as cur: cur.execute( """SELECT * FROM dashboard WHERE owner_id=? AND dashboard_id=?""", [owner_id, dashboard_id]) return cur.fetchone()
def select_multi(self, owner_id, report_id_list): with cursor() as cur: cur.execute( """SELECT * FROM report WHERE report_id IN {in_p}""".format( in_p=in_params(report_id_list)), report_id_list) return cur.fetchall()
def set_all_dashboards_ordering(self, owner_id, dashboard_id_list): with cursor() as cur: cur.execute(*replace( 'all_dashboards_properties', dict(owner_id=owner_id, dashboard_id_ordering=serialize.mjson( dashboard_id_list))))
def select_report_instance_days(self, report_id, tags): with cursor() as cur: cur.execute( """SELECT day FROM report_instance_day WHERE report_id=? AND tags=?""", [report_id, tags]) return [row['day'] for row in cur.fetchall()]
def set(self, owner_id, dashboard_id, old_layout_id, new_layout_id, new_layout_def, new_layout_props): with cursor() as cur: if old_layout_id is None: try: cur.execute(*insert( 'dashboard_layout', dict( owner_id=owner_id, dashboard_id=dashboard_id, layout_def=new_layout_def, layout_props=new_layout_props, layout_id=new_layout_id, ))) except sqlite3.IntegrityError: return False else: return True cur.execute( """UPDATE dashboard_layout SET layout_id=?, layout_def=?, layout_props=? WHERE owner_id=? AND dashboard_id=? AND layout_id=?""", [ new_layout_id, new_layout_def, new_layout_props, owner_id, dashboard_id, old_layout_id ]) return cur.rowcount == 1
def select_tags_sample(self, report_id, tag_prefix, limit): with cursor() as cur: cur.execute( """SELECT tag FROM report_tag WHERE report_id=? AND tag LIKE ? LIMIT ?""", [report_id, '%s%%' % tag_prefix, limit]) return [r['tag'] for r in cur.fetchall()]
def select_multi(self, report_id, kind, key_list): with cursor() as cur: q = """SELECT options_key, options_value FROM options WHERE report_id=? AND kind=? AND options_key IN {in_p}""".\ format(in_p=in_params(key_list)) cur.execute(q, [report_id, kind] + key_list) return [(row['options_key'], row['options_value']) for row in cur.fetchall()]
def insert_multi(self, series_id, data_it): q = """INSERT OR IGNORE INTO series_value (series_id, report_instance_id, json_value, header) VALUES (?, ?, ?, ?)""" params_list = [] for d in data_it: params_list.append([series_id, d['report_instance_id'], d['json_value'], d.get('header')]) with cursor() as cur: cur.executemany(q, params_list)
def select_multi(self, dashboard_id, tile_id_list): with cursor() as cur: cur.execute( """SELECT * FROM tile WHERE dashboard_id=? AND tile_id IN {in_p}""".format( in_p=in_params(tile_id_list)), [dashboard_id] + tile_id_list) return cur.fetchall()
def select_user_id(self, api_key): with cursor() as cur: cur.execute( """SELECT user_id FROM user WHERE api_key=?""", [api_key]) row = cur.fetchone() if not row: return None return row['user_id']
def is_password_valid(self, user_id, password): with cursor() as cur: cur.execute("""SELECT password FROM user WHERE user_id=?""", [user_id]) row = cur.fetchone() if not row: return False return werkzeug.security.check_password_hash( row['password'], password)
def set_multi(self, report_id, kind, key_value_list): with cursor() as cur: for (key, value) in key_value_list: cur.execute(*replace( 'options', dict(report_id=report_id, kind=kind, options_key=key, options_value=value)))
def delete(self, owner_id, report_id): row = self.select(report_id) if not row: log.warn('No report row %s', report_id) return with cursor() as cur: cur.execute("""DELETE FROM report WHERE report_id=?""", [report_id]) cur.execute("""DELETE FROM report_tag WHERE report_id=?""", [report_id])
def insert_layout_by_report_multi(self, owner_id, report_id_list, tags, label, dashboard_id, layout_id): tags = tags or [] with cursor() as cur: qs = """REPLACE INTO layout_by_report (owner_id, report_id, tags, label, dashboard_id, layout_id) VALUES (?, ?, ?, ?, ?, ?)""" params_list = [] for report_id in report_id_list: params_list.append([owner_id, report_id, tags, label, dashboard_id, layout_id]) cur.executemany(qs, params_list)
def select_latest_id(self, report_id, tags): tags = tags or [] with cursor() as cur: cur.execute("""SELECT report_instance_id FROM report_instance WHERE report_id=? AND tags=? ORDER BY report_instance_id DESC LIMIT 1""", [report_id, tags]) row = cur.fetchone() return row['report_instance_id'] if row else None
def select_multi(self, report_id, tags_series_id_list): res = [] with cursor() as cur: for tags, series_id in tags_series_id_list: cur.execute("""SELECT * FROM series_def WHERE report_id=? AND tags=? AND series_id=?""", [report_id, tags, series_id]) res.append(postprocess_tags(cur.fetchone())) return res
def insert(self, owner_id, dashboard_name, dashboard_options): dashboard_id = gen_uuid() row = dict(owner_id=owner_id, dashboard_id=dashboard_id, dashboard_name=dashboard_name, dashboard_options=dashboard_options) with cursor() as cur: cur.execute(*insert('dashboard', row)) return row
def select(self, owner_id, dashboard_id, columns=('layout_id', 'layout_def', 'layout_props')): what = ', '.join(columns) with cursor() as cur: cur.execute("""SELECT {what} FROM dashboard_layout WHERE owner_id=? AND dashboard_id=?""".format(what=what), [owner_id, dashboard_id]) return cur.fetchone()
def select(self, user_id): with cursor() as cur: cur.execute( """SELECT api_key FROM user WHERE user_id=?""", [user_id]) row = cur.fetchone() if not row: return None return row['api_key']
def select_all_dashboards_ordering(self, owner_id): with cursor() as cur: cur.execute( """SELECT dashboard_id_ordering FROM all_dashboards_properties WHERE owner_id=?""", [owner_id]) row = cur.fetchone() if row: return serialize.json_loads(row['dashboard_id_ordering']) return None