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
