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)
