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)
rows = cur.execute("""SELECT series_id FROM series_def
WHERE report_id=? AND tags IN {in_p}""".format(
in_p=in_params(tags_powerset)),
[report_id] + tags_powerset)
for row in rows:
cur.execute("""DELETE FROM series_value WHERE series_id=?""", [row['series_id']])
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, 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 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 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_multi(self, owner_id, dashboard_id_list,
columns=('layout_id', 'layout_def', 'layout_props')):
if 'dashboard_id' not in columns:
columns += 'dashboard_id',
what = ', '.join(columns)
with cursor() as cur:
cur.execute("""SELECT {what}
FROM dashboard_layout
WHERE owner_id=? AND dashboard_id IN {in_p}"""\
.format(what=what, in_p=in_params(dashboard_id_list)),
[owner_id] + dashboard_id_list)
return cur.fetchall()
def delete(self, owner_id, report_id, report_instance_id):
ri = self.select(report_id, report_instance_id, None)
if not ri:
return False
diskspace = self._compute_ri_diskspace(ri)
tags_powerset = util.powerset(ri['all_tags'])
with cursor() as cur:
cur.execute(
"""DELETE FROM report_instance WHERE report_id=?
AND tags IN {in_p} AND report_instance_id=?""".
format(in_p=in_params(tags_powerset)),
[report_id] + tags_powerset + [report_instance_id])
# report counts
cur.execute(
"""UPDATE report SET
report_instance_count = report_instance_count - 1
WHERE report_id=?""", [report_id])
cur.execute(
"""UPDATE report SET
report_instance_diskspace = report_instance_diskspace - ?
WHERE report_id=?""", [diskspace, report_id])
# owner counts
cur.execute(
"""UPDATE report_data_for_owner
SET report_instance_count=report_instance_count-1
WHERE owner_id=?""", [owner_id])
cur.execute(
"""UPDATE report_data_for_owner
SET report_instance_diskspace=report_instance_diskspace-?
WHERE owner_id=?""", [diskspace, owner_id])
return True
def _delete_ris(self, owner_id, report_id, tags, ris, update_counters):
qs = []
tags_days = set()
all_tags_subsets = set()
with cursor() as cur:
for ri in ris:
tags_powerset = util.powerset(ri['all_tags'])
cur.execute("""DELETE FROM report_instance WHERE report_id=?
AND tags IN {in_p} AND report_instance_id=?""".format(in_p=in_params(tags_powerset)),
[report_id] + tags_powerset + [ri['report_instance_id']])
day = util.datetime_from_uuid1(ri['report_instance_id']).date()
for tags_subset in tags_powerset:
tags_days.add((tuple(tags_subset), day))
all_tags_subsets.add(tuple(tags_subset))
if update_counters:
total_diskspace = sum(self._compute_ri_diskspace(ri) for ri in ris)
cur.execute("""UPDATE report
SET report_instance_count = report_instance_count - ?
WHERE report_id=?""",
[len(ris), report_id])
cur.execute("""UPDATE report
SET report_instance_diskspace = report_instance_diskspace - ?
WHERE report_id=?""",
[total_diskspace, report_id])
cur.execute("""UPDATE report_data_for_owner
SET report_instance_count=report_instance_count - ?
WHERE owner_id=?""",
[len(ris), owner_id])
cur.execute("""UPDATE report_data_for_owner
SET report_instance_diskspace=report_instance_diskspace - ?
WHERE owner_id=?""",
[total_diskspace, owner_id])
### Delete days for which report instances no longer exist
for day_tags, day in tags_days:
cur.execute("""SELECT report_instance_id FROM report_instance
WHERE report_id=? AND tags=? AND
report_instance_id > ? AND report_instance_id < ?
LIMIT 1""",
[report_id, list(day_tags),
util.min_uuid_with_dt(datetime.datetime.combine(day,
datetime.datetime.min.time())),
util.max_uuid_with_dt(datetime.datetime.combine(day,
datetime.datetime.max.time()))])
if not cur.fetchall():
cur.execute("""DELETE FROM report_instance_day
WHERE report_id=? AND tags=? AND day=?""",
[report_id, list(day_tags), day])
### Delete tags for which report instances no longer exist
tags_present = set()
for tags, _ in tags_days:
for tag in tags:
tags_present.add(tag)
for tag in tags_present:
cur.execute("""SELECT report_id FROM report_instance_day
WHERE report_id=? AND tags=?
LIMIT 1""",
[report_id, [tag]])
if cur.fetchall():
continue
cur.execute("""DELETE FROM report_tag
WHERE report_id=? AND tag=?""",
[report_id, tag])
return len(ris), [list(ts) for ts in all_tags_subsets]