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]