def select_events(self): if self.get_all(): self.cursor.execute("""SELECT * FROM event""") return result_as_dict_array(self.cursor) log.debug('Events selected before filtering: %d' % self.count_selected_events()) # For each filter which is defined remove the set of records not matching the filter if self.file_filter is not None: self.cursor.execute(""" DELETE FROM tmp_event_rids WHERE NOT EXISTS ( SELECT 1 FROM event e, input_file i WHERE rid = e.rowid AND i.id = e.file_id AND i.path = ? ) """, (self.file_filter,)) log.debug('Events selected after file_filter(%s): %d' % ( self.file_filter, self.count_selected_events())) if self.date_filter is not None: self.cursor.execute(""" DELETE FROM tmp_event_rids WHERE NOT EXISTS ( SELECT 1 FROM event e WHERE rid = e.rowid AND event_end LIKE ? ) """, (self.date_filter.strftime('%Y-%m-%d%%'),)) log.debug('Events selected after date_filter(%s): %d' % ( str(self.date_filter), self.count_selected_events())) if self.from_filter is not None and self.to_filter is not None: self.cursor.execute(""" DELETE FROM tmp_event_rids WHERE NOT EXISTS ( SELECT 1 FROM event e WHERE rid = e.rowid AND event_end >= ? AND event_end <= ? ) """, (self.from_filter.strftime(sqlite_datefmt), self.to_filter.strftime(sqlite_datefmt))) elif self.from_filter is not None and self.to_filter is None: self.cursor.execute(""" DELETE FROM tmp_event_rids WHERE NOT EXISTS ( SELECT 1 FROM event e WHERE rid = e.rowid AND event_end >= ? ) """, (self.from_filter.strftime(sqlite_datefmt),)) elif self.from_filter is None and self.to_filter is not None: self.cursor.execute(""" DELETE FROM tmp_event_rids WHERE NOT EXISTS ( SELECT 1 FROM event e WHERE rid = e.rowid AND event_end <= ? ) """, (self.to_filter.strftime(sqlite_datefmt),)) log.debug('Events selected after from_filter(%s) to_filter(%s): %d' % ( str(self.from_filter), str(self.to_filter), self.count_selected_events())) self.cursor.execute(""" SELECT * FROM event e WHERE EXISTS ( SELECT 1 FROM tmp_event_rids t WHERE e.rowid = t.rid ) """) return result_as_dict_array(self.cursor)
def select_raw_data(self): if self.get_all(): self.cursor.execute("""SELECT * FROM raw_data""") return result_as_dict_array(self.cursor) log.debug('Raw Data selected before filtering: %d' % self.count_selected_raw_data()) # For each filter which is defined remove the set of records not matching the filter if self.file_filter is not None: self.cursor.execute(""" DELETE FROM tmp_raw_data_rids WHERE NOT EXISTS ( SELECT 1 FROM raw_data r, input_file i WHERE rid = r.rowid AND i.id = r.file_id AND i.path = ? ) """, (self.file_filter,)) log.debug('Raw Data selected after file_filter(%s): %d' % ( self.file_filter, self.count_selected_raw_data())) if self.date_filter is not None: self.cursor.execute(""" DELETE FROM tmp_raw_data_rids WHERE NOT EXISTS ( SELECT 1 FROM raw_data r WHERE rid = r.rowid AND dt = ? ) """, (self.date_filter.strftime('%d-%m-%Y'),)) log.debug('Raw Data selected after date_filter(%s): %d' % ( str(self.date_filter), self.count_selected_raw_data())) if self.from_filter is not None and self.to_filter is not None: self.cursor.execute(""" DELETE FROM tmp_raw_data_rids WHERE NOT EXISTS ( SELECT 1 FROM raw_data r WHERE rid = r.rowid AND ts >= ? AND ts <= ? ) """, (self.from_filter.strftime(sqlite_datefmt), self.to_filter.strftime(sqlite_datefmt))) elif self.from_filter is not None and self.to_filter is None: self.cursor.execute(""" DELETE FROM tmp_raw_data_rids WHERE NOT EXISTS ( SELECT 1 FROM raw_data r WHERE rid = r.rowid AND ts >= ? ) """, (self.from_filter.strftime(sqlite_datefmt),)) elif self.from_filter is None and self.to_filter is not None: self.cursor.execute(""" DELETE FROM tmp_raw_data_rids WHERE NOT EXISTS ( SELECT 1 FROM raw_data r WHERE rid = r.rowid AND ts <= ? ) """, (self.to_filter.strftime(sqlite_datefmt),)) log.debug('Raw Data selected after from_filter(%s) to_filter(%s): %d' % ( str(self.from_filter), str(self.to_filter), self.count_selected_raw_data())) self.cursor.execute(""" SELECT * FROM raw_data r WHERE EXISTS ( SELECT 1 FROM tmp_raw_data_rids t WHERE r.rowid = t.rid ) """) return result_as_dict_array(self.cursor)