Autopsy  3.1
Graphical digital forensics platform for The Sleuth Kit and other tools.
EventDB.java
Go to the documentation of this file.
1 /*
2  * Autopsy Forensic Browser
3  *
4  * Copyright 2013 Basis Technology Corp.
5  * Contact: carrier <at> sleuthkit <dot> org
6  *
7  * Licensed under the Apache License, Version 2.0 (the "License");
8  * you may not use this file except in compliance with the License.
9  * You may obtain a copy of the License at
10  *
11  * http://www.apache.org/licenses/LICENSE-2.0
12  *
13  * Unless required by applicable law or agreed to in writing, software
14  * distributed under the License is distributed on an "AS IS" BASIS,
15  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
16  * See the License for the specific language governing permissions and
17  * limitations under the License.
18  */
19 package org.sleuthkit.autopsy.timeline.events.db;
20 
21 import com.google.common.base.Stopwatch;
22 import com.google.common.collect.HashMultimap;
23 import com.google.common.collect.SetMultimap;
24 import java.io.File;
25 import java.sql.Connection;
26 import java.sql.DriverManager;
27 import java.sql.PreparedStatement;
28 import java.sql.ResultSet;
29 import java.sql.SQLException;
30 import java.sql.Statement;
31 import java.sql.Types;
32 import java.util.ArrayList;
33 import java.util.Arrays;
34 import java.util.Collection;
35 import java.util.Collections;
36 import java.util.HashMap;
37 import java.util.HashSet;
38 import java.util.Iterator;
39 import java.util.List;
40 import java.util.Map;
41 import java.util.Objects;
42 import java.util.Set;
43 import java.util.TimeZone;
44 import java.util.concurrent.locks.Lock;
45 import java.util.concurrent.locks.ReentrantReadWriteLock;
46 import java.util.logging.Level;
47 import java.util.stream.Collectors;
48 import org.apache.commons.lang3.StringUtils;
49 import org.joda.time.DateTimeZone;
50 import org.joda.time.Interval;
51 import org.joda.time.Period;
52 import org.openide.util.Exceptions;
72 import org.sqlite.SQLiteJDBCLoader;
73 
80 public class EventDB {
81 
82  private static final String ARTIFACT_ID_COLUMN = "artifact_id"; // NON-NLS
83 
84  private static final String BASE_TYPE_COLUMN = "base_type"; // NON-NLS
85 
86  private static final String EVENT_ID_COLUMN = "event_id"; // NON-NLS
87 
88  //column name constants//////////////////////
89  private static final String FILE_ID_COLUMN = "file_id"; // NON-NLS
90 
91  private static final String FULL_DESCRIPTION_COLUMN = "full_description"; // NON-NLS
92 
93  private static final String KNOWN_COLUMN = "known_state"; // NON-NLS
94 
95  private static final String LAST_ARTIFACT_ID_KEY = "last_artifact_id"; // NON-NLS
96 
97  private static final String LAST_OBJECT_ID_KEY = "last_object_id"; // NON-NLS
98 
99  private static final java.util.logging.Logger LOGGER = Logger.getLogger(EventDB.class.getName());
100 
101  private static final String MED_DESCRIPTION_COLUMN = "med_description"; // NON-NLS
102 
103  private static final String SHORT_DESCRIPTION_COLUMN = "short_description"; // NON-NLS
104 
105  private static final String SUB_TYPE_COLUMN = "sub_type"; // NON-NLS
106 
107  private static final String TIME_COLUMN = "time"; // NON-NLS
108 
109  private static final String WAS_INGEST_RUNNING_KEY = "was_ingest_running"; // NON-NLS
110 
111  static {
112  //make sure sqlite driver is loaded // possibly redundant
113  try {
114  Class.forName("org.sqlite.JDBC"); // NON-NLS
115  } catch (ClassNotFoundException ex) {
116  LOGGER.log(Level.SEVERE, "Failed to load sqlite JDBC driver", ex); // NON-NLS
117  }
118  }
119 
129  public static EventDB getEventDB(String dbPath) {
130  try {
131  EventDB eventDB = new EventDB(dbPath + File.separator + "events.db"); // NON-NLS
132 
133  return eventDB;
134  } catch (SQLException ex) {
135  LOGGER.log(Level.SEVERE, "sql error creating database connection", ex); // NON-NLS
136  return null;
137  } catch (Exception ex) {
138  LOGGER.log(Level.SEVERE, "error creating database connection", ex); // NON-NLS
139  return null;
140  }
141  }
142 
143  static List<Integer> getActiveSubTypes(TypeFilter filter) {
144  if (filter.isActive()) {
145  if (filter.getSubFilters().isEmpty()) {
146  return Collections.singletonList(RootEventType.allTypes.indexOf(filter.getEventType()));
147  } else {
148  return filter.getSubFilters().stream().flatMap((Filter t) -> getActiveSubTypes((TypeFilter) t).stream()).collect(Collectors.toList());
149  }
150  } else {
151  return Collections.emptyList();
152  }
153  }
154 
155  static String getSQLWhere(IntersectionFilter filter) {
156  return filter.getSubFilters().stream()
157  .filter(Filter::isActive)
158  .map(EventDB::getSQLWhere)
159  .collect(Collectors.joining(" and ", "( ", ")")); // NON-NLS
160  }
161 
162  static String getSQLWhere(UnionFilter filter) {
163  return filter.getSubFilters().stream()
164  .filter(Filter::isActive)
165  .map(EventDB::getSQLWhere)
166  .collect(Collectors.joining(" or ", "( ", ")")); // NON-NLS
167  }
168 
169  private static String getSQLWhere(Filter filter) {
170  //TODO: this is here so that the filters don't depend, even implicitly, on the db, but it leads to some nasty code
171  //it would all be much easier if all the getSQLWhere methods where moved to their respective filter classes
172  String result = "";
173  if (filter == null) {
174  return "1";
175  } else if (filter instanceof HideKnownFilter) {
176  result = getSQLWhere((HideKnownFilter) filter);
177  } else if (filter instanceof TextFilter) {
178  result = getSQLWhere((TextFilter) filter);
179  } else if (filter instanceof TypeFilter) {
180  result = getSQLWhere((TypeFilter) filter);
181  } else if (filter instanceof IntersectionFilter) {
182  result = getSQLWhere((IntersectionFilter) filter);
183  } else if (filter instanceof UnionFilter) {
184  result = getSQLWhere((UnionFilter) filter);
185  } else {
186  return "1";
187  }
188  result = StringUtils.deleteWhitespace(result).equals("(1and1and1)") ? "1" : result; // NON-NLS
189  //System.out.println(result);
190  return result;
191  }
192 
193  private static String getSQLWhere(HideKnownFilter filter) {
194  return (filter.isActive())
195  ? "(known_state is not '" + TskData.FileKnown.KNOWN.getFileKnownValue() + "')" // NON-NLS
196  : "1";
197  }
198 
199  private static String getSQLWhere(TextFilter filter) {
200  if (filter.isActive()) {
201  if (StringUtils.isBlank(filter.getText())) {
202  return "1";
203  }
204  String strip = StringUtils.strip(filter.getText());
205  return "((" + MED_DESCRIPTION_COLUMN + " like '%" + strip + "%') or (" // NON-NLS
206  + FULL_DESCRIPTION_COLUMN + " like '%" + strip + "%') or (" // NON-NLS
207  + SHORT_DESCRIPTION_COLUMN + " like '%" + strip + "%'))"; // NON-NLS
208  } else {
209  return "1";
210  }
211  }
212 
221  private static String getSQLWhere(TypeFilter filter) {
222  if (filter.isActive() == false) {
223  return "0";
224  } else if (filter.getEventType() instanceof RootEventType) {
225  //if the filter is a root filter and all base type filtes and subtype filters are active,
226  if (filter.getSubFilters().stream().allMatch(f
227  -> f.isActive() && ((TypeFilter) f).getSubFilters().stream().allMatch(Filter::isActive))) {
228  return "1"; //then collapse clause to true
229  }
230  }
231  return "(" + SUB_TYPE_COLUMN + " in (" + StringUtils.join(getActiveSubTypes(filter), ",") + "))"; // NON-NLS
232  }
233 
234  private volatile Connection con;
235 
236  private final String dbPath;
237 
238  private PreparedStatement getDBInfoStmt;
239 
240  private PreparedStatement getEventByIDStmt;
241 
242  private PreparedStatement getMaxTimeStmt;
243 
244  private PreparedStatement getMinTimeStmt;
245 
246  private PreparedStatement insertRowStmt;
247 
248  private final Set<PreparedStatement> preparedStatements = new HashSet<>();
249 
250  private PreparedStatement recordDBInfoStmt;
251 
252  private final ReentrantReadWriteLock rwLock = new ReentrantReadWriteLock(true); //use fairness policy
253 
254  private final Lock DBLock = rwLock.writeLock(); //using exclusing lock for all db ops for now
255 
256  private EventDB(String dbPath) throws SQLException, Exception {
257  this.dbPath = dbPath;
258  initializeDB();
259  }
260 
261  @Override
262  public void finalize() throws Throwable {
263  try {
264  closeDBCon();
265  } finally {
266  super.finalize();
267  }
268  }
269 
270  public Interval getSpanningInterval(Collection<Long> eventIDs) {
271 
272  Interval span = null;
273  dbReadLock();
274  try (Statement stmt = con.createStatement();
275  //You can't inject multiple values into one ? paramater in prepared statement,
276  //so we make new statement each time...
277  ResultSet rs = stmt.executeQuery("select Min(time), Max(time) from events where event_id in (" + StringUtils.join(eventIDs, ", ") + ")");) { // NON-NLS
278  while (rs.next()) {
279  span = new Interval(rs.getLong("Min(time)"), rs.getLong("Max(time)") + 1, DateTimeZone.UTC); // NON-NLS
280 
281  }
282  } catch (SQLException ex) {
283  LOGGER.log(Level.SEVERE, "Error executing get spanning interval query.", ex); // NON-NLS
284  } finally {
285  dbReadUnlock();
286  }
287  return span;
288  }
289 
290  EventTransaction beginTransaction() {
291  return new EventTransaction();
292  }
293 
294  void closeDBCon() {
295  if (con != null) {
296  try {
297  closeStatements();
298  con.close();
299  } catch (SQLException ex) {
300  LOGGER.log(Level.WARNING, "Failed to close connection to evetns.db", ex); // NON-NLS
301  }
302  }
303  con = null;
304  }
305 
306  void commitTransaction(EventTransaction tr, Boolean notify) {
307  if (tr.isClosed()) {
308  throw new IllegalArgumentException("can't close already closed transaction"); // NON-NLS
309  }
310  tr.commit(notify);
311  }
312 
313  int countAllEvents() {
314  int result = -1;
315  dbReadLock();
316  //TODO convert this to prepared statement -jm
317  try (ResultSet rs = con.createStatement().executeQuery("select count(*) as count from events")) { // NON-NLS
318  while (rs.next()) {
319  result = rs.getInt("count"); // NON-NLS
320  break;
321  }
322  } catch (SQLException ex) {
323  Exceptions.printStackTrace(ex);
324  } finally {
325  dbReadUnlock();
326  }
327  return result;
328  }
329 
330  Map<EventType, Long> countEvents(ZoomParams params) {
331  if (params.getTimeRange() != null) {
332  return countEvents(params.getTimeRange().getStartMillis() / 1000, params.getTimeRange().getEndMillis() / 1000, params.getFilter(), params.getTypeZoomLevel());
333  } else {
334  return Collections.emptyMap();
335  }
336  }
337 
344  void dbReadLock() {
345  DBLock.lock();
346  }
347 
353  void dbReadUnlock() {
354  DBLock.unlock();
355  }
356 
358  void dbWriteLock() {
359  //Logger.getLogger("LOCK").log(Level.INFO, "Locking " + rwLock.toString());
360  DBLock.lock();
361  }
362 
368  void dbWriteUnlock() {
369  //Logger.getLogger("LOCK").log(Level.INFO, "UNLocking " + rwLock.toString());
370  DBLock.unlock();
371  }
372 
373  void dropTable() {
374  //TODO: use prepared statement - jm
375  dbWriteLock();
376  try (Statement createStatement = con.createStatement()) {
377  createStatement.execute("drop table if exists events"); // NON-NLS
378  } catch (SQLException ex) {
379  LOGGER.log(Level.SEVERE, "could not drop old events table", ex); // NON-NLS
380  } finally {
381  dbWriteUnlock();
382  }
383  }
384 
385  List<AggregateEvent> getAggregatedEvents(ZoomParams params) {
386  return getAggregatedEvents(params.getTimeRange(), params.getFilter(), params.getTypeZoomLevel(), params.getDescrLOD());
387  }
388 
389  Interval getBoundingEventsInterval(Interval timeRange, Filter filter) {
390  long start = timeRange.getStartMillis() / 1000;
391  long end = timeRange.getEndMillis() / 1000;
392  final String sqlWhere = getSQLWhere(filter);
393 
394  dbReadLock();
395  try (Statement stmt = con.createStatement(); //can't use prepared statement because of complex where clause
396  ResultSet rs = stmt.executeQuery(" select (select Max(time) from events where time <=" + start + " and " + sqlWhere + ") as start,(select Min(time) from events where time >= " + end + " and " + sqlWhere + ") as end")) { // NON-NLS
397  while (rs.next()) {
398 
399  long start2 = rs.getLong("start"); // NON-NLS
400  long end2 = rs.getLong("end"); // NON-NLS
401 
402  if (end2 == 0) {
403  end2 = getMaxTime();
404  }
405  //System.out.println(start2 + " " + start + " " + end + " " + end2);
406  return new Interval(start2 * 1000, (end2 + 1) * 1000, TimeLineController.getJodaTimeZone());
407  }
408  } catch (SQLException ex) {
409  LOGGER.log(Level.SEVERE, "Failed to get MIN time.", ex); // NON-NLS
410  } finally {
411  dbReadUnlock();
412  }
413  return null;
414  }
415 
416  TimeLineEvent getEventById(Long eventID) {
417  TimeLineEvent result = null;
418  dbReadLock();
419  try {
420  getEventByIDStmt.clearParameters();
421  getEventByIDStmt.setLong(1, eventID);
422  try (ResultSet rs = getEventByIDStmt.executeQuery()) {
423  while (rs.next()) {
424  result = constructTimeLineEvent(rs);
425  break;
426  }
427  }
428  } catch (SQLException sqlEx) {
429  LOGGER.log(Level.SEVERE, "exception while querying for event with id = " + eventID, sqlEx); // NON-NLS
430  } finally {
431  dbReadUnlock();
432  }
433  return result;
434  }
435 
436  Set<Long> getEventIDs(Interval timeRange, Filter filter) {
437  return getEventIDs(timeRange.getStartMillis() / 1000, timeRange.getEndMillis() / 1000, filter);
438  }
439 
440  Set<Long> getEventIDs(Long startTime, Long endTime, Filter filter) {
441  if (Objects.equals(startTime, endTime)) {
442  endTime++;
443  }
444  Set<Long> resultIDs = new HashSet<>();
445 
446  dbReadLock();
447  final String query = "select event_id from events where time >= " + startTime + " and time <" + endTime + " and " + getSQLWhere(filter); // NON-NLS
448  //System.out.println(query);
449  try (Statement stmt = con.createStatement();
450  ResultSet rs = stmt.executeQuery(query)) {
451 
452  while (rs.next()) {
453  resultIDs.add(rs.getLong(EVENT_ID_COLUMN));
454  }
455 
456  } catch (SQLException sqlEx) {
457  LOGGER.log(Level.SEVERE, "failed to execute query for event ids in range", sqlEx); // NON-NLS
458  } finally {
459  dbReadUnlock();
460  }
461 
462  return resultIDs;
463  }
464 
465  long getLastArtfactID() {
466  return getDBInfo(LAST_ARTIFACT_ID_KEY, -1);
467  }
468 
469  long getLastObjID() {
470  return getDBInfo(LAST_OBJECT_ID_KEY, -1);
471  }
472 
474  Long getMaxTime() {
475  dbReadLock();
476  try (ResultSet rs = getMaxTimeStmt.executeQuery()) {
477  while (rs.next()) {
478  return rs.getLong("max"); // NON-NLS
479  }
480  } catch (SQLException ex) {
481  LOGGER.log(Level.SEVERE, "Failed to get MAX time.", ex); // NON-NLS
482  } finally {
483  dbReadUnlock();
484  }
485  return -1l;
486  }
487 
489  Long getMinTime() {
490  dbReadLock();
491  try (ResultSet rs = getMinTimeStmt.executeQuery()) {
492  while (rs.next()) {
493  return rs.getLong("min"); // NON-NLS
494  }
495  } catch (SQLException ex) {
496  LOGGER.log(Level.SEVERE, "Failed to get MIN time.", ex); // NON-NLS
497  } finally {
498  dbReadUnlock();
499  }
500  return -1l;
501  }
502 
503  boolean getWasIngestRunning() {
504  return getDBInfo(WAS_INGEST_RUNNING_KEY, 0) != 0;
505  }
506 
514  final synchronized void initializeDB() {
515  try {
516  if (isClosed()) {
517  openDBCon();
518  }
519  configureDB();
520 
521  } catch (SQLException ex) {
522  LOGGER.log(Level.SEVERE, "problem accessing database", ex); // NON-NLS
523  }
524 
525  dbWriteLock();
526  try {
527  try (Statement stmt = con.createStatement()) {
528  String sql = "CREATE TABLE if not exists db_info " // NON-NLS
529  + " ( key TEXT, " // NON-NLS
530  + " value INTEGER, " // NON-NLS
531  + "PRIMARY KEY (key))"; // NON-NLS
532  stmt.execute(sql);
533  } catch (SQLException ex) {
534  LOGGER.log(Level.SEVERE, "problem creating db_info table", ex); // NON-NLS
535  }
536 
537  try (Statement stmt = con.createStatement()) {
538  String sql = "CREATE TABLE if not exists events " // NON-NLS
539  + " (event_id INTEGER PRIMARY KEY, " // NON-NLS
540  + " file_id INTEGER, " // NON-NLS
541  + " artifact_id INTEGER, " // NON-NLS
542  + " time INTEGER, " // NON-NLS
543  + " sub_type INTEGER, " // NON-NLS
544  + " base_type INTEGER, " // NON-NLS
545  + " full_description TEXT, " // NON-NLS
546  + " med_description TEXT, " // NON-NLS
547  + " short_description TEXT, " // NON-NLS
548  + " known_state INTEGER)"; // NON-NLS
549  stmt.execute(sql);
550  } catch (SQLException ex) {
551  LOGGER.log(Level.SEVERE, "problem creating database table", ex); // NON-NLS
552  }
553 
554  try (Statement stmt = con.createStatement()) {
555  String sql = "CREATE INDEX if not exists file_idx ON events(file_id)"; // NON-NLS
556  stmt.execute(sql);
557  } catch (SQLException ex) {
558  LOGGER.log(Level.SEVERE, "problem creating file_idx", ex); // NON-NLS
559  }
560  try (Statement stmt = con.createStatement()) {
561  String sql = "CREATE INDEX if not exists artifact_idx ON events(artifact_id)"; // NON-NLS
562  stmt.execute(sql);
563  } catch (SQLException ex) {
564  LOGGER.log(Level.SEVERE, "problem creating artifact_idx", ex); // NON-NLS
565  }
566 
567  //for common queries the covering indexes below were better, but having the time index 'blocke' them
568 // try (Statement stmt = con.createStatement()) {
569 // String sql = "CREATE INDEX if not exists time_idx ON events(time)";
570 // stmt.execute(sql);
571 // } catch (SQLException ex) {
572 // LOGGER.log(Level.SEVERE, "problem creating time_idx", ex);
573 // }
574  try (Statement stmt = con.createStatement()) {
575  String sql = "CREATE INDEX if not exists sub_type_idx ON events(sub_type, time)"; // NON-NLS
576  stmt.execute(sql);
577  } catch (SQLException ex) {
578  LOGGER.log(Level.SEVERE, "problem creating sub_type_idx", ex); // NON-NLS
579  }
580 
581  try (Statement stmt = con.createStatement()) {
582  String sql = "CREATE INDEX if not exists base_type_idx ON events(base_type, time)"; // NON-NLS
583  stmt.execute(sql);
584  } catch (SQLException ex) {
585  LOGGER.log(Level.SEVERE, "problem creating base_type_idx", ex); // NON-NLS
586  }
587 
588  try (Statement stmt = con.createStatement()) {
589  String sql = "CREATE INDEX if not exists known_idx ON events(known_state)"; // NON-NLS
590  stmt.execute(sql);
591  } catch (SQLException ex) {
592  LOGGER.log(Level.SEVERE, "problem creating known_idx", ex); // NON-NLS
593  }
594 
595  try {
596  insertRowStmt = prepareStatement(
597  "INSERT INTO events (file_id ,artifact_id, time, sub_type, base_type, full_description, med_description, short_description, known_state) " // NON-NLS
598  + "VALUES (?,?,?,?,?,?,?,?,?)"); // NON-NLS
599 
600  getMaxTimeStmt = prepareStatement("select Max(time) as max from events"); // NON-NLS
601  getMinTimeStmt = prepareStatement("select Min(time) as min from events"); // NON-NLS
602  getEventByIDStmt = prepareStatement("select * from events where event_id = ?"); // NON-NLS
603  recordDBInfoStmt = prepareStatement("insert or replace into db_info (key, value) values (?, ?)"); // NON-NLS
604  getDBInfoStmt = prepareStatement("select value from db_info where key = ?"); // NON-NLS
605  } catch (SQLException sQLException) {
606  LOGGER.log(Level.SEVERE, "failed to prepareStatment", sQLException); // NON-NLS
607  }
608 
609  } finally {
610  dbWriteUnlock();
611  }
612 
613  }
614 
615  void insertEvent(long time, EventType type, Long objID, Long artifactID, String fullDescription, String medDescription, String shortDescription, TskData.FileKnown known) {
616  EventTransaction trans = beginTransaction();
617  insertEvent(time, type, objID, artifactID, fullDescription, medDescription, shortDescription, known, trans);
618  commitTransaction(trans, true);
619  }
620 
627  void insertEvent(long time, EventType type, Long objID, Long artifactID, String fullDescription, String medDescription, String shortDescription, TskData.FileKnown known, EventTransaction tr) {
628  if (tr.isClosed()) {
629  throw new IllegalArgumentException("can't update database with closed transaction"); // NON-NLS
630  }
631  int typeNum;
632  int superTypeNum;
633 
634  typeNum = RootEventType.allTypes.indexOf(type);
635  superTypeNum = type.getSuperType().ordinal();
636 
637  dbWriteLock();
638  try {
639 
640  //"INSERT INTO events (file_id ,artifact_id, time, sub_type, base_type, full_description, med_description, short_description) "
641  insertRowStmt.clearParameters();
642  if (objID != null) {
643  insertRowStmt.setLong(1, objID);
644  } else {
645  insertRowStmt.setNull(1, Types.INTEGER);
646  }
647  if (artifactID != null) {
648  insertRowStmt.setLong(2, artifactID);
649  } else {
650  insertRowStmt.setNull(2, Types.INTEGER);
651  }
652  insertRowStmt.setLong(3, time);
653 
654  if (typeNum != -1) {
655  insertRowStmt.setInt(4, typeNum);
656  } else {
657  insertRowStmt.setNull(4, Types.INTEGER);
658  }
659 
660  insertRowStmt.setInt(5, superTypeNum);
661  insertRowStmt.setString(6, fullDescription);
662  insertRowStmt.setString(7, medDescription);
663  insertRowStmt.setString(8, shortDescription);
664 
665  insertRowStmt.setByte(9, known == null ? TskData.FileKnown.UNKNOWN.getFileKnownValue() : known.getFileKnownValue());
666 
667  insertRowStmt.executeUpdate();
668 
669  } catch (SQLException ex) {
670  LOGGER.log(Level.SEVERE, "failed to insert event", ex); // NON-NLS
671  } finally {
672  dbWriteUnlock();
673  }
674  }
675 
676  boolean isClosed() throws SQLException {
677  if (con == null) {
678  return true;
679  }
680  return con.isClosed();
681  }
682 
683  void openDBCon() {
684  try {
685  if (con == null || con.isClosed()) {
686  con = DriverManager.getConnection("jdbc:sqlite:" + dbPath); // NON-NLS
687  }
688  } catch (SQLException ex) {
689  LOGGER.log(Level.WARNING, "Failed to open connection to events.db", ex); // NON-NLS
690  }
691  }
692 
693  void recordLastArtifactID(long lastArtfID) {
694  recordDBInfo(LAST_ARTIFACT_ID_KEY, lastArtfID);
695  }
696 
697  void recordLastObjID(Long lastObjID) {
698  recordDBInfo(LAST_OBJECT_ID_KEY, lastObjID);
699  }
700 
701  void recordWasIngestRunning(boolean wasIngestRunning) {
702  recordDBInfo(WAS_INGEST_RUNNING_KEY, (wasIngestRunning ? 1 : 0));
703  }
704 
705  void rollBackTransaction(EventTransaction trans) {
706  trans.rollback();
707  }
708 
709  boolean tableExists() {
710  //TODO: use prepared statement - jm
711  try (Statement createStatement = con.createStatement();
712  ResultSet executeQuery = createStatement.executeQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='events'")) { // NON-NLS
713  if (executeQuery.getString("name").equals("events") == false) { // NON-NLS
714  return false;
715  }
716  } catch (SQLException ex) {
717  Exceptions.printStackTrace(ex);
718  }
719  return true;
720  }
721 
722  private void closeStatements() throws SQLException {
723  for (PreparedStatement pStmt : preparedStatements) {
724  pStmt.close();
725  }
726  }
727 
728  private void configureDB() throws SQLException {
729  dbWriteLock();
730  //this should match Sleuthkit db setupt
731  try (Statement statement = con.createStatement()) {
732  //reduce i/o operations, we have no OS crash recovery anyway
733  statement.execute("PRAGMA synchronous = OFF;"); // NON-NLS
734  //we don't use this feature, so turn it off for minimal speed up on queries
735  //this is deprecated and not recomended
736  statement.execute("PRAGMA count_changes = OFF;"); // NON-NLS
737  //this made a big difference to query speed
738  statement.execute("PRAGMA temp_store = MEMORY"); // NON-NLS
739  //this made a modest improvement in query speeds
740  statement.execute("PRAGMA cache_size = 50000"); // NON-NLS
741  //we never delete anything so...
742  statement.execute("PRAGMA auto_vacuum = 0"); // NON-NLS
743  //allow to query while in transaction - no need read locks
744  statement.execute("PRAGMA read_uncommitted = True;"); // NON-NLS
745  } finally {
746  dbWriteUnlock();
747  }
748 
749  try {
750  LOGGER.log(Level.INFO, String.format("sqlite-jdbc version %s loaded in %s mode", // NON-NLS
751  SQLiteJDBCLoader.getVersion(), SQLiteJDBCLoader.isNativeMode()
752  ? "native" : "pure-java")); // NON-NLS
753  } catch (Exception exception) {
754  }
755  }
756 
757  private TimeLineEvent constructTimeLineEvent(ResultSet rs) throws SQLException {
758  EventType type = RootEventType.allTypes.get(rs.getInt(SUB_TYPE_COLUMN));
759  return new TimeLineEvent(rs.getLong(EVENT_ID_COLUMN),
760  rs.getLong(FILE_ID_COLUMN),
761  rs.getLong(ARTIFACT_ID_COLUMN),
762  rs.getLong(TIME_COLUMN),
763  type,
764  rs.getString(FULL_DESCRIPTION_COLUMN),
765  rs.getString(MED_DESCRIPTION_COLUMN),
766  rs.getString(SHORT_DESCRIPTION_COLUMN),
767  TskData.FileKnown.valueOf(rs.getByte(KNOWN_COLUMN)));
768  }
769 
787  private Map<EventType, Long> countEvents(Long startTime, Long endTime, Filter filter, EventTypeZoomLevel zoomLevel) {
788  if (Objects.equals(startTime, endTime)) {
789  endTime++;
790  }
791 
792  Map<EventType, Long> typeMap = new HashMap<>();
793 
794  //do we want the root or subtype column of the databse
795  final boolean useSubTypes = (zoomLevel == EventTypeZoomLevel.SUB_TYPE);
796 
797  //get some info about the range of dates requested
798  final String queryString = "select count(*), " + (useSubTypes ? SUB_TYPE_COLUMN : BASE_TYPE_COLUMN) // NON-NLS
799  + " from events where time >= " + startTime + " and time < " + endTime + " and " + getSQLWhere(filter) // NON-NLS
800  + " GROUP BY " + (useSubTypes ? SUB_TYPE_COLUMN : BASE_TYPE_COLUMN); // NON-NLS
801 
802  ResultSet rs = null;
803  dbReadLock();
804  //System.out.println(queryString);
805  try (Statement stmt = con.createStatement();) {
806  Stopwatch stopwatch = new Stopwatch();
807  stopwatch.start();
808  rs = stmt.executeQuery(queryString);
809  stopwatch.stop();
810  // System.out.println(stopwatch.elapsedMillis() / 1000.0 + " seconds");
811  while (rs.next()) {
812 
813  EventType type = useSubTypes
814  ? RootEventType.allTypes.get(rs.getInt(SUB_TYPE_COLUMN))
815  : BaseTypes.values()[rs.getInt(BASE_TYPE_COLUMN)];
816 
817  typeMap.put(type, rs.getLong("count(*)")); // NON-NLS
818  }
819 
820  } catch (Exception ex) {
821  LOGGER.log(Level.SEVERE, "error getting count of events from db.", ex); // NON-NLS
822  } finally {
823  try {
824  rs.close();
825  } catch (SQLException ex) {
826  Exceptions.printStackTrace(ex);
827  }
828  dbReadUnlock();
829  }
830  return typeMap;
831  }
832 
858  private List<AggregateEvent> getAggregatedEvents(Interval timeRange, Filter filter, EventTypeZoomLevel zoomLevel, DescriptionLOD lod) {
859  String descriptionColumn = getDescriptionColumn(lod);
860  final boolean useSubTypes = (zoomLevel.equals(EventTypeZoomLevel.SUB_TYPE));
861 
862  //get some info about the time range requested
864  //use 'rounded out' range
865  long start = timeRange.getStartMillis() / 1000;//.getLowerBound();
866  long end = timeRange.getEndMillis() / 1000;//Millis();//rangeInfo.getUpperBound();
867  if (Objects.equals(start, end)) {
868  end++;
869  }
870 
871  //get a sqlite srtftime format string
872  String strfTimeFormat = getStrfTimeFormat(rangeInfo.getPeriodSize());
873 
874  //effectively map from type to (map from description to events)
875  Map<EventType, SetMultimap< String, AggregateEvent>> typeMap = new HashMap<>();
876 
877  //get all agregate events in this time unit
878  dbReadLock();
879  String query = "select strftime('" + strfTimeFormat + "',time , 'unixepoch'" + (TimeLineController.getTimeZone().get().equals(TimeZone.getDefault()) ? ", 'localtime'" : "") + ") as interval, group_concat(event_id) as event_ids, Min(time), Max(time), " + descriptionColumn + ", " + (useSubTypes ? SUB_TYPE_COLUMN : BASE_TYPE_COLUMN) // NON-NLS
880  + " from events where time >= " + start + " and time < " + end + " and " + getSQLWhere(filter) // NON-NLS
881  + " group by interval, " + (useSubTypes ? SUB_TYPE_COLUMN : BASE_TYPE_COLUMN) + " , " + descriptionColumn // NON-NLS
882  + " order by Min(time)"; // NON-NLS
883  //System.out.println(query);
884  ResultSet rs = null;
885  try (Statement stmt = con.createStatement(); // scoop up requested events in groups organized by interval, type, and desription
886  ) {
887 
888  Stopwatch stopwatch = new Stopwatch();
889  stopwatch.start();
890 
891  rs = stmt.executeQuery(query);
892  stopwatch.stop();
893  //System.out.println(stopwatch.elapsedMillis() / 1000.0 + " seconds");
894  while (rs.next()) {
895  EventType type = useSubTypes ? RootEventType.allTypes.get(rs.getInt(SUB_TYPE_COLUMN)) : BaseTypes.values()[rs.getInt(BASE_TYPE_COLUMN)];
896 
897  AggregateEvent aggregateEvent = new AggregateEvent(
898  new Interval(rs.getLong("Min(time)") * 1000, rs.getLong("Max(time)") * 1000, TimeLineController.getJodaTimeZone()), // NON-NLS
899  type,
900  Arrays.asList(rs.getString("event_ids").split(",")), // NON-NLS
901  rs.getString(descriptionColumn), lod);
902 
903  //put events in map from type/descrition -> event
904  SetMultimap<String, AggregateEvent> descrMap = typeMap.get(type);
905  if (descrMap == null) {
906  descrMap = HashMultimap.<String, AggregateEvent>create();
907  typeMap.put(type, descrMap);
908  }
909  descrMap.put(aggregateEvent.getDescription(), aggregateEvent);
910  }
911 
912  } catch (SQLException ex) {
913  Exceptions.printStackTrace(ex);
914  } finally {
915  try {
916  rs.close();
917  } catch (SQLException ex) {
918  Exceptions.printStackTrace(ex);
919  }
920  dbReadUnlock();
921  }
922 
923  //result list to return
924  ArrayList<AggregateEvent> aggEvents = new ArrayList<>();
925 
926  //save this for use when comparing gap size
927  Period timeUnitLength = rangeInfo.getPeriodSize().getPeriod();
928 
929  //For each (type, description) key, merge agg events
930  for (SetMultimap<String, AggregateEvent> descrMap : typeMap.values()) {
931  for (String descr : descrMap.keySet()) {
932  //run through the sorted events, merging together adjacent events
933  Iterator<AggregateEvent> iterator = descrMap.get(descr).stream()
934  .sorted((AggregateEvent o1, AggregateEvent o2)
935  -> Long.compare(o1.getSpan().getStartMillis(), o2.getSpan().getStartMillis()))
936  .iterator();
937  AggregateEvent current = iterator.next();
938  while (iterator.hasNext()) {
939  AggregateEvent next = iterator.next();
940  Interval gap = current.getSpan().gap(next.getSpan());
941 
942  //if they overlap or gap is less one quarter timeUnitLength
943  //TODO: 1/4 factor is arbitrary. review! -jm
944  if (gap == null || gap.toDuration().getMillis() <= timeUnitLength.toDurationFrom(gap.getStart()).getMillis() / 4) {
945  //merge them
946  current = AggregateEvent.merge(current, next);
947  } else {
948  //done merging into current, set next as new current
949  aggEvents.add(current);
950  current = next;
951  }
952  }
953  aggEvents.add(current);
954  }
955  }
956 
957  //at this point we should have a list of aggregate events.
958  //one per type/description spanning consecutive time units as determined in rangeInfo
959  return aggEvents;
960  }
961 
962  private long getDBInfo(String key, long defaultValue) {
963  dbReadLock();
964  try {
965  getDBInfoStmt.setString(1, key);
966 
967  try (ResultSet rs = getDBInfoStmt.executeQuery()) {
968  long result = defaultValue;
969  while (rs.next()) {
970  result = rs.getLong("value"); // NON-NLS
971  }
972  return result;
973  } catch (SQLException ex) {
974  LOGGER.log(Level.SEVERE, "failed to read key: " + key + " from db_info", ex); // NON-NLS
975  } finally {
976  dbReadUnlock();
977  }
978  } catch (SQLException ex) {
979  LOGGER.log(Level.SEVERE, "failed to set key: " + key + " on getDBInfoStmt ", ex); // NON-NLS
980  }
981 
982  return defaultValue;
983  }
984 
985  private String getDescriptionColumn(DescriptionLOD lod) {
986  switch (lod) {
987  case FULL:
989  case MEDIUM:
990  return MED_DESCRIPTION_COLUMN;
991  case SHORT:
992  default:
994  }
995  }
996 
997  private String getStrfTimeFormat(TimeUnits info) {
998  switch (info) {
999  case DAYS:
1000  return "%Y-%m-%dT00:00:00"; // NON-NLS
1001  case HOURS:
1002  return "%Y-%m-%dT%H:00:00"; // NON-NLS
1003  case MINUTES:
1004  return "%Y-%m-%dT%H:%M:00"; // NON-NLS
1005  case MONTHS:
1006  return "%Y-%m-01T00:00:00"; // NON-NLS
1007  case SECONDS:
1008  return "%Y-%m-%dT%H:%M:%S"; // NON-NLS
1009  case YEARS:
1010  return "%Y-01-01T00:00:00"; // NON-NLS
1011  default:
1012  return "%Y-%m-%dT%H:%M:%S"; // NON-NLS
1013  }
1014  }
1015 
1016  private PreparedStatement prepareStatement(String queryString) throws SQLException {
1017  PreparedStatement prepareStatement = con.prepareStatement(queryString);
1018  preparedStatements.add(prepareStatement);
1019  return prepareStatement;
1020  }
1021 
1022  private void recordDBInfo(String key, long value) {
1023  dbWriteLock();
1024  try {
1025  recordDBInfoStmt.setString(1, key);
1026  recordDBInfoStmt.setLong(2, value);
1027  recordDBInfoStmt.executeUpdate();
1028  } catch (SQLException ex) {
1029  LOGGER.log(Level.SEVERE, "failed to set dbinfo key: " + key + " value: " + value, ex); // NON-NLS
1030  } finally {
1031  dbWriteUnlock();
1032  }
1033  }
1034 
1038  public class EventTransaction {
1039 
1040  private boolean closed = false;
1041 
1051  private EventTransaction() {
1052 
1053  //get the write lock, released in close()
1054  dbWriteLock();
1055  try {
1056  con.setAutoCommit(false);
1057 
1058  } catch (SQLException ex) {
1059  LOGGER.log(Level.SEVERE, "failed to set auto-commit to to false", ex); // NON-NLS
1060  }
1061 
1062  }
1063 
1064  private void rollback() {
1065  if (!closed) {
1066  try {
1067  con.rollback();
1068 
1069  } catch (SQLException ex1) {
1070  LOGGER.log(Level.SEVERE, "Exception while attempting to rollback!!", ex1); // NON-NLS
1071  } finally {
1072  close();
1073  }
1074  }
1075  }
1076 
1077  private void commit(Boolean notify) {
1078  if (!closed) {
1079  try {
1080  con.commit();
1081  // make sure we close before we update, bc they'll need locks
1082  close();
1083 
1084  if (notify) {
1085 // fireNewEvents(newEvents);
1086  }
1087  } catch (SQLException ex) {
1088  LOGGER.log(Level.SEVERE, "Error commiting events.db.", ex); // NON-NLS
1089  rollback();
1090  }
1091  }
1092  }
1093 
1094  private void close() {
1095  if (!closed) {
1096  try {
1097  con.setAutoCommit(true);
1098  } catch (SQLException ex) {
1099  LOGGER.log(Level.SEVERE, "Error setting auto-commit to true.", ex); // NON-NLS
1100  } finally {
1101  closed = true;
1102 
1103  dbWriteUnlock();
1104  }
1105  }
1106  }
1107 
1108  public Boolean isClosed() {
1109  return closed;
1110  }
1111  }
1112 
1113  public class MultipleTransactionException extends IllegalStateException {
1114 
1115  private static final String CANNOT_HAVE_MORE_THAN_ONE_OPEN_TRANSACTION = "cannot have more than one open transaction"; // NON-NLS
1116 
1118  super(CANNOT_HAVE_MORE_THAN_ONE_OPEN_TRANSACTION);
1119  }
1120 
1121  }
1122 }
static FileKnown valueOf(byte known)
static EventDB getEventDB(String dbPath)
Definition: EventDB.java:129
static AggregateEvent merge(AggregateEvent ag1, AggregateEvent ag2)
static final java.util.logging.Logger LOGGER
Definition: EventDB.java:99
static ReadOnlyObjectProperty< TimeZone > getTimeZone()
PreparedStatement prepareStatement(String queryString)
Definition: EventDB.java:1016
Interval getSpanningInterval(Collection< Long > eventIDs)
Definition: EventDB.java:270
void recordDBInfo(String key, long value)
Definition: EventDB.java:1022
List< AggregateEvent > getAggregatedEvents(Interval timeRange, Filter filter, EventTypeZoomLevel zoomLevel, DescriptionLOD lod)
Definition: EventDB.java:858
static String getSQLWhere(TextFilter filter)
Definition: EventDB.java:199
static RangeDivisionInfo getRangeDivisionInfo(Interval timeRange)
TimeLineEvent constructTimeLineEvent(ResultSet rs)
Definition: EventDB.java:757
static String getSQLWhere(Filter filter)
Definition: EventDB.java:169
final Set< PreparedStatement > preparedStatements
Definition: EventDB.java:248
long getDBInfo(String key, long defaultValue)
Definition: EventDB.java:962
static String getSQLWhere(TypeFilter filter)
Definition: EventDB.java:221
String getDescriptionColumn(DescriptionLOD lod)
Definition: EventDB.java:985
static String getSQLWhere(HideKnownFilter filter)
Definition: EventDB.java:193
Map< EventType, Long > countEvents(Long startTime, Long endTime, Filter filter, EventTypeZoomLevel zoomLevel)
Definition: EventDB.java:787
static Logger getLogger(String name)
Definition: Logger.java:131
static final List<?extends EventType > allTypes
Definition: EventType.java:35

Copyright © 2012-2015 Basis Technology. Generated on: Mon Oct 19 2015
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.