EventDB.java

Go to the documentation of this file.

/*
 * Autopsy Forensic Browser
 *
 * Copyright 2013-15 Basis Technology Corp.
 * Contact: carrier <at> sleuthkit <dot> org
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.sleuthkit.autopsy.timeline.db;

import com.google.common.collect.HashMultimap;
import com.google.common.collect.SetMultimap;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.TimeZone;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantReadWriteLock;
import java.util.logging.Level;
import java.util.stream.Collectors;
import javax.annotation.Nonnull;
import javax.annotation.Nullable;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.ImmutablePair;
import org.joda.time.DateTimeZone;
import org.joda.time.Interval;
import org.joda.time.Period;
import org.sleuthkit.autopsy.casemodule.Case;
import org.sleuthkit.autopsy.coreutils.Logger;
import org.sleuthkit.autopsy.coreutils.Version;
import org.sleuthkit.autopsy.timeline.TimeLineController;
import org.sleuthkit.autopsy.timeline.datamodel.CombinedEvent;
import org.sleuthkit.autopsy.timeline.datamodel.EventCluster;
import org.sleuthkit.autopsy.timeline.datamodel.EventStripe;
import org.sleuthkit.autopsy.timeline.datamodel.SingleEvent;
import org.sleuthkit.autopsy.timeline.datamodel.eventtype.BaseTypes;
import org.sleuthkit.autopsy.timeline.datamodel.eventtype.EventType;
import org.sleuthkit.autopsy.timeline.datamodel.eventtype.RootEventType;
import static org.sleuthkit.autopsy.timeline.db.SQLHelper.useHashHitTablesHelper;
import static org.sleuthkit.autopsy.timeline.db.SQLHelper.useTagTablesHelper;
import org.sleuthkit.autopsy.timeline.filters.RootFilter;
import org.sleuthkit.autopsy.timeline.filters.TagsFilter;
import org.sleuthkit.autopsy.timeline.utils.RangeDivisionInfo;
import org.sleuthkit.autopsy.timeline.zooming.DescriptionLoD;
import org.sleuthkit.autopsy.timeline.zooming.EventTypeZoomLevel;
import org.sleuthkit.autopsy.timeline.zooming.ZoomParams;
import org.sleuthkit.datamodel.AbstractFile;
import org.sleuthkit.datamodel.BlackboardArtifact;
import org.sleuthkit.datamodel.SleuthkitCase;
import org.sleuthkit.datamodel.Tag;
import org.sleuthkit.datamodel.TskData;
import org.sqlite.SQLiteJDBCLoader;

public class EventDB {

    private static final org.sleuthkit.autopsy.coreutils.Logger LOGGER = Logger.getLogger(EventDB.class.getName());

    static {
        //make sure sqlite driver is loaded, possibly redundant
        try {
            Class.forName("org.sqlite.JDBC"); // NON-NLS
        } catch (ClassNotFoundException ex) {
            LOGGER.log(Level.SEVERE, "Failed to load sqlite JDBC driver", ex); // NON-NLS
        }
    }

    public static EventDB getEventDB(Case autoCase) {
        try {
            return new EventDB(autoCase);
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "sql error creating database connection", ex); // NON-NLS
            return null;
        } catch (Exception ex) {
            LOGGER.log(Level.SEVERE, "error creating database connection", ex); // NON-NLS
            return null;
        }
    }

    private volatile Connection con;

    private final String dbPath;

    private PreparedStatement getEventByIDStmt;
    private PreparedStatement getMaxTimeStmt;
    private PreparedStatement getMinTimeStmt;
    private PreparedStatement getDataSourceIDsStmt;
    private PreparedStatement getHashSetNamesStmt;
    private PreparedStatement insertRowStmt;
    private PreparedStatement insertHashSetStmt;
    private PreparedStatement insertHashHitStmt;
    private PreparedStatement insertTagStmt;
    private PreparedStatement deleteTagStmt;
    private PreparedStatement selectHashSetStmt;
    private PreparedStatement countAllEventsStmt;
    private PreparedStatement dropEventsTableStmt;
    private PreparedStatement dropHashSetHitsTableStmt;
    private PreparedStatement dropHashSetsTableStmt;
    private PreparedStatement dropTagsTableStmt;
    private PreparedStatement dropDBInfoTableStmt;
    private PreparedStatement selectNonArtifactEventIDsByObjectIDStmt;
    private PreparedStatement selectEventIDsBYObjectAndArtifactIDStmt;

    private final Set<PreparedStatement> preparedStatements = new HashSet<>();

    private final Lock DBLock = new ReentrantReadWriteLock(true).writeLock(); //using exclusive lock for all db ops for now

    private EventDB(Case autoCase) throws SQLException, Exception {
        //should this go into module output (or even cache, we should be able to rebuild it)?
        this.dbPath = Paths.get(autoCase.getCaseDirectory(), "events.db").toString(); //NON-NLS
        initializeDB();
    }

    @Override
    public void finalize() throws Throwable {
        try {
            closeDBCon();
        } finally {
            super.finalize();
        }
    }

    void closeDBCon() {
        if (con != null) {
            try {
                closeStatements();
                con.close();
            } catch (SQLException ex) {
                LOGGER.log(Level.WARNING, "Failed to close connection to evetns.db", ex); // NON-NLS
            }
        }
        con = null;
    }

    public Interval getSpanningInterval(Collection<Long> eventIDs) {
        DBLock.lock();
        try (Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT Min(time), Max(time) FROM events WHERE event_id IN (" + StringUtils.join(eventIDs, ", ") + ")");) { // NON-NLS
            while (rs.next()) {
                return new Interval(rs.getLong("Min(time)") * 1000, (rs.getLong("Max(time)") + 1) * 1000, DateTimeZone.UTC); // NON-NLS
            }
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "Error executing get spanning interval query.", ex); // NON-NLS
        } finally {
            DBLock.unlock();
        }
        return null;
    }

    EventTransaction beginTransaction() {
        return new EventTransaction();
    }

    void commitTransaction(EventTransaction tr) {
        if (tr.isClosed()) {
            throw new IllegalArgumentException("can't close already closed transaction"); // NON-NLS
        }
        tr.commit();
    }

    int countAllEvents() {
        DBLock.lock();
        try (ResultSet rs = countAllEventsStmt.executeQuery()) { // NON-NLS
            while (rs.next()) {
                return rs.getInt("count"); // NON-NLS
            }
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "Error counting all events", ex); //NON-NLS
        } finally {
            DBLock.unlock();
        }
        return -1;
    }

    Map<EventType, Long> countEventsByType(ZoomParams params) {
        if (params.getTimeRange() != null) {
            return countEventsByType(params.getTimeRange().getStartMillis() / 1000,
                    params.getTimeRange().getEndMillis() / 1000,
                    params.getFilter(), params.getTypeZoomLevel());
        } else {
            return Collections.emptyMap();
        }
    }

    Map<String, Long> getTagCountsByTagName(Set<Long> eventIDsWithTags) {
        HashMap<String, Long> counts = new HashMap<>();
        DBLock.lock();
        try (Statement createStatement = con.createStatement();
                ResultSet rs = createStatement.executeQuery("SELECT tag_name_display_name, COUNT(DISTINCT tag_id) AS count FROM tags" //NON-NLS
                        + " WHERE event_id IN (" + StringUtils.join(eventIDsWithTags, ", ") + ")" //NON-NLS
                        + " GROUP BY tag_name_id" //NON-NLS
                        + " ORDER BY tag_name_display_name");) { //NON-NLS
            while (rs.next()) {
                counts.put(rs.getString("tag_name_display_name"), rs.getLong("count")); //NON-NLS
            }
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "Failed to get tag counts by tag name.", ex); //NON-NLS
        } finally {
            DBLock.unlock();
        }
        return counts;
    }

    void reInitializeDB() {
        DBLock.lock();
        try {
            dropEventsTableStmt.executeUpdate();
            dropHashSetHitsTableStmt.executeUpdate();
            dropHashSetsTableStmt.executeUpdate();
            dropTagsTableStmt.executeUpdate();
            dropDBInfoTableStmt.executeUpdate();
            initializeDB();
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "could not drop old tables", ex); // NON-NLS
        } finally {
            DBLock.unlock();
        }
    }

    void reInitializeTags() {
        DBLock.lock();
        try {
            dropTagsTableStmt.executeUpdate();
            initializeTagsTable();
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "could not drop old tags table", ex); // NON-NLS
        } finally {
            DBLock.unlock();
        }
    }

    Interval getBoundingEventsInterval(Interval timeRange, RootFilter filter) {
        long start = timeRange.getStartMillis() / 1000;
        long end = timeRange.getEndMillis() / 1000;
        final String sqlWhere = SQLHelper.getSQLWhere(filter);
        DBLock.lock();
        try (Statement stmt = con.createStatement(); //can't use prepared statement because of complex where clause
                ResultSet rs = stmt.executeQuery(" SELECT (SELECT Max(time) FROM events " + useHashHitTablesHelper(filter) + useTagTablesHelper(filter) + " WHERE time <=" + start + " AND " + sqlWhere + ") AS start," //NON-NLS
                        + "(SELECT Min(time)  FROM events" + useHashHitTablesHelper(filter) + useTagTablesHelper(filter) + " WHERE time >= " + end + " AND " + sqlWhere + ") AS end")) { // NON-NLS
            while (rs.next()) {

                long start2 = rs.getLong("start"); // NON-NLS
                long end2 = rs.getLong("end"); // NON-NLS

                if (end2 == 0) {
                    end2 = getMaxTime();
                }
                return new Interval(start2 * 1000, (end2 + 1) * 1000, TimeLineController.getJodaTimeZone());
            }
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "Failed to get MIN time.", ex); // NON-NLS
        } finally {
            DBLock.unlock();
        }
        return null;
    }

    SingleEvent getEventById(Long eventID) {
        SingleEvent result = null;
        DBLock.lock();
        try {
            getEventByIDStmt.clearParameters();
            getEventByIDStmt.setLong(1, eventID);
            try (ResultSet rs = getEventByIDStmt.executeQuery()) {
                while (rs.next()) {
                    result = constructTimeLineEvent(rs);
                    break;
                }
            }
        } catch (SQLException sqlEx) {
            LOGGER.log(Level.SEVERE, "exception while querying for event with id = " + eventID, sqlEx); // NON-NLS
        } finally {
            DBLock.unlock();
        }
        return result;
    }

    List<Long> getEventIDs(Interval timeRange, RootFilter filter) {
        Long startTime = timeRange.getStartMillis() / 1000;
        Long endTime = timeRange.getEndMillis() / 1000;

        if (Objects.equals(startTime, endTime)) {
            endTime++; //make sure end is at least 1 millisecond after start
        }

        ArrayList<Long> resultIDs = new ArrayList<>();

        DBLock.lock();
        final String query = "SELECT events.event_id AS event_id FROM events" + useHashHitTablesHelper(filter) + useTagTablesHelper(filter)
                + " WHERE time >=  " + startTime + " AND time <" + endTime + " AND " + SQLHelper.getSQLWhere(filter) + " ORDER BY time ASC"; // NON-NLS
        try (Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(query)) {
            while (rs.next()) {
                resultIDs.add(rs.getLong("event_id")); //NON-NLS
            }

        } catch (SQLException sqlEx) {
            LOGGER.log(Level.SEVERE, "failed to execute query for event ids in range", sqlEx); // NON-NLS
        } finally {
            DBLock.unlock();
        }

        return resultIDs;
    }

    List<CombinedEvent> getCombinedEvents(Interval timeRange, RootFilter filter) {
        Long startTime = timeRange.getStartMillis() / 1000;
        Long endTime = timeRange.getEndMillis() / 1000;

        if (Objects.equals(startTime, endTime)) {
            endTime++; //make sure end is at least 1 millisecond after start
        }

        ArrayList<CombinedEvent> results = new ArrayList<>();

        DBLock.lock();
        final String query = "SELECT full_description, time, file_id, GROUP_CONCAT(events.event_id), GROUP_CONCAT(sub_type)"
                + " FROM events " + useHashHitTablesHelper(filter) + useTagTablesHelper(filter)
                + " WHERE time >= " + startTime + " AND time <" + endTime + " AND " + SQLHelper.getSQLWhere(filter)
                + " GROUP BY time,full_description, file_id ORDER BY time ASC, full_description";
        try (Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(query)) {
            while (rs.next()) {

                //make a map from event type to event ID
                List<Long> eventIDs = SQLHelper.unGroupConcat(rs.getString("GROUP_CONCAT(events.event_id)"), Long::valueOf);
                List<EventType> eventTypes = SQLHelper.unGroupConcat(rs.getString("GROUP_CONCAT(sub_type)"), s -> RootEventType.allTypes.get(Integer.valueOf(s)));
                Map<EventType, Long> eventMap = new HashMap<>();
                for (int i = 0; i < eventIDs.size(); i++) {
                    eventMap.put(eventTypes.get(i), eventIDs.get(i));
                }
                results.add(new CombinedEvent(rs.getLong("time") * 1000, rs.getString("full_description"), rs.getLong("file_id"), eventMap));
            }

        } catch (SQLException sqlEx) {
            LOGGER.log(Level.SEVERE, "failed to execute query for combined events", sqlEx); // NON-NLS
        } finally {
            DBLock.unlock();
        }

        return results;
    }

    boolean hasNewColumns() {
        return hasHashHitColumn() && hasDataSourceIDColumn() && hasTaggedColumn()
                && (getDataSourceIDs().isEmpty() == false);
    }

    Set<Long> getDataSourceIDs() {
        HashSet<Long> hashSet = new HashSet<>();
        DBLock.lock();
        try (ResultSet rs = getDataSourceIDsStmt.executeQuery()) {
            while (rs.next()) {
                long datasourceID = rs.getLong("datasource_id"); //NON-NLS
                hashSet.add(datasourceID);
            }
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "Failed to get MAX time.", ex); // NON-NLS
        } finally {
            DBLock.unlock();
        }
        return hashSet;
    }

    Map<Long, String> getHashSetNames() {
        Map<Long, String> hashSets = new HashMap<>();
        DBLock.lock();
        try (ResultSet rs = getHashSetNamesStmt.executeQuery();) {
            while (rs.next()) {
                long hashSetID = rs.getLong("hash_set_id"); //NON-NLS
                String hashSetName = rs.getString("hash_set_name"); //NON-NLS
                hashSets.put(hashSetID, hashSetName);
            }
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "Failed to get hash sets.", ex); // NON-NLS
        } finally {
            DBLock.unlock();
        }
        return Collections.unmodifiableMap(hashSets);
    }

    void analyze() {
        DBLock.lock();
        try (Statement createStatement = con.createStatement()) {
            boolean b = createStatement.execute("analyze; analyze sqlite_master;"); //NON-NLS
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "Failed to analyze events db.", ex); // NON-NLS
        } finally {
            DBLock.unlock();
        }
    }

    Long getMaxTime() {
        DBLock.lock();
        try (ResultSet rs = getMaxTimeStmt.executeQuery()) {
            while (rs.next()) {
                return rs.getLong("max"); // NON-NLS
            }
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "Failed to get MAX time.", ex); // NON-NLS
        } finally {
            DBLock.unlock();
        }
        return -1l;
    }

    Long getMinTime() {
        DBLock.lock();
        try (ResultSet rs = getMinTimeStmt.executeQuery()) {
            while (rs.next()) {
                return rs.getLong("min"); // NON-NLS
            }
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "Failed to get MIN time.", ex); // NON-NLS
        } finally {
            DBLock.unlock();
        }
        return -1l;
    }

    final synchronized void initializeDB() {

        try {
            if (con == null || con.isClosed()) {
                con = DriverManager.getConnection("jdbc:sqlite:" + dbPath); // NON-NLS
            }
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "Failed to open connection to events.db", ex); // NON-NLS
            return;
        }
        try {
            configureDB();
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "problem accessing  database", ex); // NON-NLS
            return;
        }

        DBLock.lock();
        try {
            try (Statement stmt = con.createStatement()) {
                String sql = "CREATE TABLE if not exists db_info " // NON-NLS
                        + " ( key TEXT, " // NON-NLS
                        + " value INTEGER, " // NON-NLS
                        + "PRIMARY KEY (key))"; // NON-NLS
                stmt.execute(sql);
            } catch (SQLException ex) {
                LOGGER.log(Level.SEVERE, "problem creating db_info table", ex); // NON-NLS
            }

            try (Statement stmt = con.createStatement()) {
                String sql = "CREATE TABLE if not exists events " // NON-NLS
                        + " (event_id INTEGER PRIMARY KEY, " // NON-NLS
                        + " datasource_id INTEGER, " // NON-NLS
                        + " file_id INTEGER, " // NON-NLS
                        + " artifact_id INTEGER, " // NON-NLS
                        + " time INTEGER, " // NON-NLS
                        + " sub_type INTEGER, " // NON-NLS
                        + " base_type INTEGER, " // NON-NLS
                        + " full_description TEXT, " // NON-NLS
                        + " med_description TEXT, " // NON-NLS
                        + " short_description TEXT, " // NON-NLS
                        + " known_state INTEGER," //boolean // NON-NLS
                        + " hash_hit INTEGER," //boolean // NON-NLS
                        + " tagged INTEGER)"; //boolean // NON-NLS
                stmt.execute(sql);
            } catch (SQLException ex) {
                LOGGER.log(Level.SEVERE, "problem creating  database table", ex); // NON-NLS
            }

            if (hasDataSourceIDColumn() == false) {
                try (Statement stmt = con.createStatement()) {
                    String sql = "ALTER TABLE events ADD COLUMN datasource_id INTEGER"; // NON-NLS
                    stmt.execute(sql);
                } catch (SQLException ex) {
                    LOGGER.log(Level.SEVERE, "problem upgrading events table", ex); // NON-NLS
                }
            }
            if (hasTaggedColumn() == false) {
                try (Statement stmt = con.createStatement()) {
                    String sql = "ALTER TABLE events ADD COLUMN tagged INTEGER"; // NON-NLS
                    stmt.execute(sql);
                } catch (SQLException ex) {
                    LOGGER.log(Level.SEVERE, "problem upgrading events table", ex); // NON-NLS
                }
            }

            if (hasHashHitColumn() == false) {
                try (Statement stmt = con.createStatement()) {
                    String sql = "ALTER TABLE events ADD COLUMN hash_hit INTEGER"; // NON-NLS
                    stmt.execute(sql);
                } catch (SQLException ex) {
                    LOGGER.log(Level.SEVERE, "problem upgrading events table", ex); // NON-NLS
                }
            }

            try (Statement stmt = con.createStatement()) {
                String sql = "CREATE TABLE  if not exists hash_sets " //NON-NLS
                        + "( hash_set_id INTEGER primary key," //NON-NLS
                        + " hash_set_name VARCHAR(255) UNIQUE NOT NULL)"; //NON-NLS
                stmt.execute(sql);
            } catch (SQLException ex) {
                LOGGER.log(Level.SEVERE, "problem creating hash_sets table", ex); //NON-NLS
            }

            try (Statement stmt = con.createStatement()) {
                String sql = "CREATE TABLE  if not exists hash_set_hits " //NON-NLS
                        + "(hash_set_id INTEGER REFERENCES hash_sets(hash_set_id) not null, " //NON-NLS
                        + " event_id INTEGER REFERENCES events(event_id) not null, " //NON-NLS
                        + " PRIMARY KEY (hash_set_id, event_id))"; //NON-NLS
                stmt.execute(sql);
            } catch (SQLException ex) {
                LOGGER.log(Level.SEVERE, "problem creating hash_set_hits table", ex); //NON-NLS
            }

            initializeTagsTable();

            createIndex("events", Arrays.asList("datasource_id")); //NON-NLS
            createIndex("events", Arrays.asList("event_id", "hash_hit")); //NON-NLS
            createIndex("events", Arrays.asList("event_id", "tagged")); //NON-NLS
            createIndex("events", Arrays.asList("file_id")); //NON-NLS
            createIndex("events", Arrays.asList("artifact_id")); //NON-NLS
            createIndex("events", Arrays.asList("sub_type", "short_description", "time")); //NON-NLS
            createIndex("events", Arrays.asList("base_type", "short_description", "time")); //NON-NLS
            createIndex("events", Arrays.asList("time")); //NON-NLS
            createIndex("events", Arrays.asList("known_state")); //NON-NLS

            try {
                insertRowStmt = prepareStatement(
                        "INSERT INTO events (datasource_id,file_id ,artifact_id, time, sub_type, base_type, full_description, med_description, short_description, known_state, hash_hit, tagged) " // NON-NLS
                        + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"); // NON-NLS
                getHashSetNamesStmt = prepareStatement("SELECT hash_set_id, hash_set_name FROM hash_sets"); // NON-NLS
                getDataSourceIDsStmt = prepareStatement("SELECT DISTINCT datasource_id FROM events WHERE datasource_id != 0"); // NON-NLS
                getMaxTimeStmt = prepareStatement("SELECT Max(time) AS max FROM events"); // NON-NLS
                getMinTimeStmt = prepareStatement("SELECT Min(time) AS min FROM events"); // NON-NLS
                getEventByIDStmt = prepareStatement("SELECT * FROM events WHERE event_id =  ?"); // NON-NLS
                insertHashSetStmt = prepareStatement("INSERT OR IGNORE INTO hash_sets (hash_set_name)  values (?)"); //NON-NLS
                selectHashSetStmt = prepareStatement("SELECT hash_set_id FROM hash_sets WHERE hash_set_name = ?"); //NON-NLS
                insertHashHitStmt = prepareStatement("INSERT OR IGNORE INTO hash_set_hits (hash_set_id, event_id) values (?,?)"); //NON-NLS
                insertTagStmt = prepareStatement("INSERT OR IGNORE INTO tags (tag_id, tag_name_id,tag_name_display_name, event_id) values (?,?,?,?)"); //NON-NLS
                deleteTagStmt = prepareStatement("DELETE FROM tags WHERE tag_id = ?"); //NON-NLS

                /*
                 * This SQL query is really just a select count(*), but that has
                 * performance problems on very large tables unless you include
                 * a where clause see http://stackoverflow.com/a/9338276/4004683
                 * for more.
                 */
                countAllEventsStmt = prepareStatement("SELECT count(event_id) AS count FROM events WHERE event_id IS NOT null"); //NON-NLS
                dropEventsTableStmt = prepareStatement("DROP TABLE IF EXISTS events"); //NON-NLS
                dropHashSetHitsTableStmt = prepareStatement("DROP TABLE IF EXISTS hash_set_hits"); //NON-NLS
                dropHashSetsTableStmt = prepareStatement("DROP TABLE IF EXISTS hash_sets"); //NON-NLS
                dropTagsTableStmt = prepareStatement("DROP TABLE IF EXISTS tags"); //NON-NLS
                dropDBInfoTableStmt = prepareStatement("DROP TABLE IF EXISTS db_ino"); //NON-NLS
                selectNonArtifactEventIDsByObjectIDStmt = prepareStatement("SELECT event_id FROM events WHERE file_id == ? AND artifact_id IS NULL"); //NON-NLS
                selectEventIDsBYObjectAndArtifactIDStmt = prepareStatement("SELECT event_id FROM events WHERE file_id == ? AND artifact_id = ?"); //NON-NLS
            } catch (SQLException sQLException) {
                LOGGER.log(Level.SEVERE, "failed to prepareStatment", sQLException); // NON-NLS
            }
        } finally {
            DBLock.unlock();
        }
    }

    List<Long> getEventIDsForArtifact(BlackboardArtifact artifact) {
        DBLock.lock();

        String query = "SELECT event_id FROM events WHERE artifact_id == " + artifact.getArtifactID();

        ArrayList<Long> results = new ArrayList<>();
        try (Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(query);) {
            while (rs.next()) {
                results.add(rs.getLong("event_id"));
            }
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "Error executing getEventIDsForArtifact query.", ex); // NON-NLS
        } finally {
            DBLock.unlock();
        }
        return results;
    }

    List<Long> getEventIDsForFile(AbstractFile file, boolean includeDerivedArtifacts) {
        DBLock.lock();

        String query = "SELECT event_id FROM events WHERE file_id == " + file.getId()
                + (includeDerivedArtifacts ? "" : " AND artifact_id IS NULL");

        ArrayList<Long> results = new ArrayList<>();
        try (Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(query);) {
            while (rs.next()) {
                results.add(rs.getLong("event_id"));
            }
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "Error executing getEventIDsForFile query.", ex); // NON-NLS
        } finally {
            DBLock.unlock();
        }
        return results;
    }

    private void initializeTagsTable() {
        try (Statement stmt = con.createStatement()) {
            String sql = "CREATE TABLE IF NOT EXISTS tags " //NON-NLS
                    + "(tag_id INTEGER NOT NULL," //NON-NLS
                    + " tag_name_id INTEGER NOT NULL, " //NON-NLS
                    + " tag_name_display_name TEXT NOT NULL, " //NON-NLS
                    + " event_id INTEGER REFERENCES events(event_id) NOT NULL, " //NON-NLS
                    + " PRIMARY KEY (event_id, tag_name_id))"; //NON-NLS
            stmt.execute(sql);
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "problem creating tags table", ex); //NON-NLS
        }
    }

    private void createIndex(final String tableName, final List<String> columnList) {
        String indexColumns = columnList.stream().collect(Collectors.joining(",", "(", ")"));
        String indexName = tableName + "_" + StringUtils.join(columnList, "_") + "_idx"; //NON-NLS
        try (Statement stmt = con.createStatement()) {

            String sql = "CREATE INDEX IF NOT EXISTS " + indexName + " ON " + tableName + indexColumns; // NON-NLS
            stmt.execute(sql);
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "problem creating index " + indexName, ex); // NON-NLS
        }
    }

    private boolean hasDBColumn(@Nonnull final String dbColumn) {
        try (Statement stmt = con.createStatement()) {

            ResultSet executeQuery = stmt.executeQuery("PRAGMA table_info(events)"); //NON-NLS
            while (executeQuery.next()) {
                if (dbColumn.equals(executeQuery.getString("name"))) {
                    return true;
                }
            }
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "problem executing pragma", ex); // NON-NLS
        }
        return false;
    }

    private boolean hasDataSourceIDColumn() {
        return hasDBColumn("datasource_id"); //NON-NLS
    }

    private boolean hasTaggedColumn() {
        return hasDBColumn("tagged"); //NON-NLS
    }

    private boolean hasHashHitColumn() {
        return hasDBColumn("hash_hit"); //NON-NLS
    }

    void insertEvent(long time, EventType type, long datasourceID, long objID,
            Long artifactID, String fullDescription, String medDescription,
            String shortDescription, TskData.FileKnown known, Set<String> hashSets, List<? extends Tag> tags) {

        EventTransaction transaction = beginTransaction();
        insertEvent(time, type, datasourceID, objID, artifactID, fullDescription, medDescription, shortDescription, known, hashSets, tags, transaction);
        commitTransaction(transaction);
    }

    void insertEvent(long time, EventType type, long datasourceID, long objID,
            Long artifactID, String fullDescription, String medDescription,
            String shortDescription, TskData.FileKnown known, Set<String> hashSetNames,
            List<? extends Tag> tags, EventTransaction transaction) {

        if (transaction.isClosed()) {
            throw new IllegalArgumentException("can't update database with closed transaction"); // NON-NLS
        }
        int typeNum = RootEventType.allTypes.indexOf(type);
        int superTypeNum = type.getSuperType().ordinal();

        DBLock.lock();
        try {

            //"INSERT INTO events (datasource_id,file_id ,artifact_id, time, sub_type, base_type, full_description, med_description, short_description, known_state, hashHit, tagged) " 
            insertRowStmt.clearParameters();
            insertRowStmt.setLong(1, datasourceID);
            insertRowStmt.setLong(2, objID);
            if (artifactID != null) {
                insertRowStmt.setLong(3, artifactID);
            } else {
                insertRowStmt.setNull(3, Types.NULL);
            }
            insertRowStmt.setLong(4, time);

            if (typeNum != -1) {
                insertRowStmt.setInt(5, typeNum);
            } else {
                insertRowStmt.setNull(5, Types.INTEGER);
            }

            insertRowStmt.setInt(6, superTypeNum);
            insertRowStmt.setString(7, fullDescription);
            insertRowStmt.setString(8, medDescription);
            insertRowStmt.setString(9, shortDescription);

            insertRowStmt.setByte(10, known == null ? TskData.FileKnown.UNKNOWN.getFileKnownValue() : known.getFileKnownValue());

            insertRowStmt.setInt(11, hashSetNames.isEmpty() ? 0 : 1);
            insertRowStmt.setInt(12, tags.isEmpty() ? 0 : 1);

            insertRowStmt.executeUpdate();

            try (ResultSet generatedKeys = insertRowStmt.getGeneratedKeys()) {
                while (generatedKeys.next()) {
                    long eventID = generatedKeys.getLong("last_insert_rowid()"); //NON-NLS
                    for (String name : hashSetNames) {

                        // "insert or ignore into hash_sets (hash_set_name)  values (?)"
                        insertHashSetStmt.setString(1, name);
                        insertHashSetStmt.executeUpdate();

                        //TODO: use nested select to get hash_set_id rather than seperate statement/query ?
                        //"select hash_set_id from hash_sets where hash_set_name = ?"
                        selectHashSetStmt.setString(1, name);
                        try (ResultSet rs = selectHashSetStmt.executeQuery()) {
                            while (rs.next()) {
                                int hashsetID = rs.getInt("hash_set_id"); //NON-NLS
                                //"insert or ignore into hash_set_hits (hash_set_id, obj_id) values (?,?)";
                                insertHashHitStmt.setInt(1, hashsetID);
                                insertHashHitStmt.setLong(2, eventID);
                                insertHashHitStmt.executeUpdate();
                                break;
                            }
                        }
                    }
                    for (Tag tag : tags) {
                        //could this be one insert?  is there a performance win?
                        insertTag(tag, eventID);
                    }
                    break;
                }
            }

        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "failed to insert event", ex); // NON-NLS
        } finally {
            DBLock.unlock();
        }
    }

    Set<Long> addTag(long objectID, @Nullable Long artifactID, Tag tag, EventTransaction transaction) {
        if (transaction != null && transaction.isClosed()) {
            throw new IllegalArgumentException("can't update database with closed transaction"); // NON-NLS
        }
        DBLock.lock();
        try {
            Set<Long> eventIDs = markEventsTagged(objectID, artifactID, true);
            for (Long eventID : eventIDs) {
                insertTag(tag, eventID);
            }
            return eventIDs;
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "failed to add tag to event", ex); // NON-NLS
        } finally {
            DBLock.unlock();
        }
        return Collections.emptySet();
    }

    private void insertTag(Tag tag, long eventID) throws SQLException {

        //"INSERT OR IGNORE INTO tags (tag_id, tag_name_id,tag_name_display_name, event_id) values (?,?,?,?)"
        insertTagStmt.clearParameters();
        insertTagStmt.setLong(1, tag.getId());
        insertTagStmt.setLong(2, tag.getName().getId());
        insertTagStmt.setString(3, tag.getName().getDisplayName());
        insertTagStmt.setLong(4, eventID);
        insertTagStmt.executeUpdate();
    }

    Set<Long> deleteTag(long objectID, @Nullable Long artifactID, long tagID, boolean stillTagged) {
        DBLock.lock();
        try {
            //"DELETE FROM tags WHERE tag_id = ?
            deleteTagStmt.clearParameters();
            deleteTagStmt.setLong(1, tagID);
            deleteTagStmt.executeUpdate();

            return markEventsTagged(objectID, artifactID, stillTagged);
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "failed to add tag to event", ex); // NON-NLS
        } finally {
            DBLock.unlock();
        }
        return Collections.emptySet();
    }

    private Set<Long> markEventsTagged(long objectID, @Nullable Long artifactID, boolean tagged) throws SQLException {

        PreparedStatement selectStmt;
        if (Objects.isNull(artifactID)) {
            //"SELECT event_id FROM events WHERE file_id == ? AND artifact_id IS NULL"
            selectNonArtifactEventIDsByObjectIDStmt.clearParameters();
            selectNonArtifactEventIDsByObjectIDStmt.setLong(1, objectID);
            selectStmt = selectNonArtifactEventIDsByObjectIDStmt;
        } else {
            //"SELECT event_id FROM events WHERE file_id == ? AND artifact_id = ?"
            selectEventIDsBYObjectAndArtifactIDStmt.clearParameters();
            selectEventIDsBYObjectAndArtifactIDStmt.setLong(1, objectID);
            selectEventIDsBYObjectAndArtifactIDStmt.setLong(2, artifactID);
            selectStmt = selectEventIDsBYObjectAndArtifactIDStmt;
        }

        HashSet<Long> eventIDs = new HashSet<>();
        try (ResultSet executeQuery = selectStmt.executeQuery();) {
            while (executeQuery.next()) {
                eventIDs.add(executeQuery.getLong("event_id")); //NON-NLS
            }
        }

        //update tagged state for all event with selected ids
        try (Statement updateStatement = con.createStatement();) {
            updateStatement.executeUpdate("UPDATE events SET tagged = " + (tagged ? 1 : 0) //NON-NLS
                    + " WHERE event_id IN (" + StringUtils.join(eventIDs, ",") + ")"); //NON-NLS
        }

        return eventIDs;
    }

    void rollBackTransaction(EventTransaction trans) {
        trans.rollback();
    }

    private void closeStatements() throws SQLException {
        for (PreparedStatement pStmt : preparedStatements) {
            pStmt.close();
        }
    }

    private void configureDB() throws SQLException {
        DBLock.lock();
        //this should match Sleuthkit db setup
        try (Statement statement = con.createStatement()) {
            //reduce i/o operations, we have no OS crash recovery anyway
            statement.execute("PRAGMA synchronous = OFF;"); // NON-NLS
            //we don't use this feature, so turn it off for minimal speed up on queries
            //this is deprecated and not recomended
            statement.execute("PRAGMA count_changes = OFF;"); // NON-NLS
            //this made a big difference to query speed
            statement.execute("PRAGMA temp_store = MEMORY"); // NON-NLS
            //this made a modest improvement in query speeds
            statement.execute("PRAGMA cache_size = 50000"); // NON-NLS
            //we never delete anything so...
            statement.execute("PRAGMA auto_vacuum = 0"); // NON-NLS
            //allow to query while in transaction - no need read locks
            statement.execute("PRAGMA read_uncommitted = True;"); // NON-NLS
        } finally {
            DBLock.unlock();
        }

        try {
            LOGGER.log(Level.INFO, String.format("sqlite-jdbc version %s loaded in %s mode", // NON-NLS
                    SQLiteJDBCLoader.getVersion(), SQLiteJDBCLoader.isNativeMode() ? "native" : "pure-java")); // NON-NLS
        } catch (Exception exception) {
            LOGGER.log(Level.SEVERE, "Failed to determine if sqlite-jdbc is loaded in native or pure-java mode.", exception); //NON-NLS
        }
    }

    private SingleEvent constructTimeLineEvent(ResultSet rs) throws SQLException {
        return new SingleEvent(rs.getLong("event_id"), //NON-NLS
                rs.getLong("datasource_id"), //NON-NLS
                rs.getLong("file_id"), //NON-NLS
                rs.getLong("artifact_id"), //NON-NLS
                rs.getLong("time"), RootEventType.allTypes.get(rs.getInt("sub_type")), //NON-NLS
                rs.getString("full_description"), //NON-NLS
                rs.getString("med_description"), //NON-NLS
                rs.getString("short_description"), //NON-NLS
                TskData.FileKnown.valueOf(rs.getByte("known_state")), //NON-NLS
                rs.getInt("hash_hit") != 0, //NON-NLS
                rs.getInt("tagged") != 0); //NON-NLS
    }

    private Map<EventType, Long> countEventsByType(Long startTime, Long endTime, RootFilter filter, EventTypeZoomLevel zoomLevel) {
        if (Objects.equals(startTime, endTime)) {
            endTime++;
        }

        Map<EventType, Long> typeMap = new HashMap<>();

        //do we want the root or subtype column of the databse
        final boolean useSubTypes = (zoomLevel == EventTypeZoomLevel.SUB_TYPE);

        //get some info about the range of dates requested
        final String queryString = "SELECT count(DISTINCT events.event_id) AS count, " + typeColumnHelper(useSubTypes) //NON-NLS
                + " FROM events" + useHashHitTablesHelper(filter) + useTagTablesHelper(filter) + " WHERE time >= " + startTime + " AND time < " + endTime + " AND " + SQLHelper.getSQLWhere(filter) // NON-NLS
                + " GROUP BY " + typeColumnHelper(useSubTypes); // NON-NLS

        DBLock.lock();
        try (Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(queryString);) {
            while (rs.next()) {
                EventType type = useSubTypes
                        ? RootEventType.allTypes.get(rs.getInt("sub_type")) //NON-NLS
                        : BaseTypes.values()[rs.getInt("base_type")]; //NON-NLS

                typeMap.put(type, rs.getLong("count")); // NON-NLS
            }

        } catch (Exception ex) {
            LOGGER.log(Level.SEVERE, "Error getting count of events from db.", ex); // NON-NLS
        } finally {
            DBLock.unlock();
        }
        return typeMap;
    }

    List<EventStripe> getEventStripes(ZoomParams params) {
        //unpack params
        Interval timeRange = params.getTimeRange();
        RootFilter filter = params.getFilter();
        DescriptionLoD descriptionLOD = params.getDescriptionLOD();
        EventTypeZoomLevel typeZoomLevel = params.getTypeZoomLevel();

        long start = timeRange.getStartMillis() / 1000;
        long end = timeRange.getEndMillis() / 1000;

        //ensure length of querried interval is not 0
        end = Math.max(end, start + 1);

        //get some info about the time range requested
        RangeDivisionInfo rangeInfo = RangeDivisionInfo.getRangeDivisionInfo(timeRange);

        //build dynamic parts of query
        String strfTimeFormat = SQLHelper.getStrfTimeFormat(rangeInfo.getPeriodSize());
        String descriptionColumn = SQLHelper.getDescriptionColumn(descriptionLOD);
        final boolean useSubTypes = typeZoomLevel.equals(EventTypeZoomLevel.SUB_TYPE);
        String timeZone = TimeLineController.getTimeZone().get().equals(TimeZone.getDefault()) ? ", 'localtime'" : "";  // NON-NLS
        String typeColumn = typeColumnHelper(useSubTypes);

        //compose query string, the new-lines are only for nicer formatting if printing the entire query
        String query = "SELECT strftime('" + strfTimeFormat + "',time , 'unixepoch'" + timeZone + ") AS interval," // NON-NLS
                + "\n group_concat(events.event_id) as event_ids," //NON-NLS
                + "\n group_concat(CASE WHEN hash_hit = 1 THEN events.event_id ELSE NULL END) as hash_hits," //NON-NLS
                + "\n group_concat(CASE WHEN tagged = 1 THEN events.event_id ELSE NULL END) as taggeds," //NON-NLS
                + "\n min(time), max(time),  " + typeColumn + ", " + descriptionColumn // NON-NLS
                + "\n FROM events" + useHashHitTablesHelper(filter) + useTagTablesHelper(filter) // NON-NLS
                + "\n WHERE time >= " + start + " AND time < " + end + " AND " + SQLHelper.getSQLWhere(filter) // NON-NLS
                + "\n GROUP BY interval, " + typeColumn + " , " + descriptionColumn // NON-NLS
                + "\n ORDER BY min(time)"; // NON-NLS

        switch (Version.getBuildType()) {
            case DEVELOPMENT:
//                LOGGER.log(Level.INFO, "executing timeline query: {0}", query); //NON-NLS
                break;
            case RELEASE:
            default:
        }

        // perform query and map results to AggregateEvent objects
        List<EventCluster> events = new ArrayList<>();

        DBLock.lock();
        try (Statement createStatement = con.createStatement();
                ResultSet rs = createStatement.executeQuery(query)) {
            while (rs.next()) {
                events.add(eventClusterHelper(rs, useSubTypes, descriptionLOD, filter.getTagsFilter()));
            }
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "Failed to get events with query: " + query, ex); // NON-NLS
        } finally {
            DBLock.unlock();
        }

        return mergeClustersToStripes(rangeInfo.getPeriodSize().getPeriod(), events);
    }

    private EventCluster eventClusterHelper(ResultSet rs, boolean useSubTypes, DescriptionLoD descriptionLOD, TagsFilter filter) throws SQLException {
        Interval interval = new Interval(rs.getLong("min(time)") * 1000, rs.getLong("max(time)") * 1000, TimeLineController.getJodaTimeZone());// NON-NLS
        String eventIDsString = rs.getString("event_ids");// NON-NLS
        List<Long> eventIDs = SQLHelper.unGroupConcat(eventIDsString, Long::valueOf);
        String description = rs.getString(SQLHelper.getDescriptionColumn(descriptionLOD));
        EventType type = useSubTypes ? RootEventType.allTypes.get(rs.getInt("sub_type")) : BaseTypes.values()[rs.getInt("base_type")];// NON-NLS

        List<Long> hashHits = SQLHelper.unGroupConcat(rs.getString("hash_hits"), Long::valueOf); //NON-NLS
        List<Long> tagged = SQLHelper.unGroupConcat(rs.getString("taggeds"), Long::valueOf); //NON-NLS

        return new EventCluster(interval, type, eventIDs, hashHits, tagged, description, descriptionLOD);
    }

    static private List<EventStripe> mergeClustersToStripes(Period timeUnitLength, List<EventCluster> preMergedEvents) {

        //effectively map from type to (map from description to events)
        Map<EventType, SetMultimap< String, EventCluster>> typeMap = new HashMap<>();

        for (EventCluster aggregateEvent : preMergedEvents) {
            typeMap.computeIfAbsent(aggregateEvent.getEventType(), eventType -> HashMultimap.create())
                    .put(aggregateEvent.getDescription(), aggregateEvent);
        }
        //result list to return
        ArrayList<EventCluster> aggEvents = new ArrayList<>();

        //For each (type, description) key, merge agg events
        for (SetMultimap<String, EventCluster> descrMap : typeMap.values()) {
            //for each description ...
            for (String descr : descrMap.keySet()) {
                //run through the sorted events, merging together adjacent events
                Iterator<EventCluster> iterator = descrMap.get(descr).stream()
                        .sorted(Comparator.comparing(event -> event.getSpan().getStartMillis()))
                        .iterator();
                EventCluster current = iterator.next();
                while (iterator.hasNext()) {
                    EventCluster next = iterator.next();
                    Interval gap = current.getSpan().gap(next.getSpan());

                    //if they overlap or gap is less one quarter timeUnitLength
                    //TODO: 1/4 factor is arbitrary. review! -jm
                    if (gap == null || gap.toDuration().getMillis() <= timeUnitLength.toDurationFrom(gap.getStart()).getMillis() / 4) {
                        //merge them
                        current = EventCluster.merge(current, next);
                    } else {
                        //done merging into current, set next as new current
                        aggEvents.add(current);
                        current = next;
                    }
                }
                aggEvents.add(current);
            }
        }

        //merge clusters to stripes
        Map<ImmutablePair<EventType, String>, EventStripe> stripeDescMap = new HashMap<>();

        for (EventCluster eventCluster : aggEvents) {
            stripeDescMap.merge(ImmutablePair.of(eventCluster.getEventType(), eventCluster.getDescription()),
                    new EventStripe(eventCluster), EventStripe::merge);
        }

        return stripeDescMap.values().stream().sorted(Comparator.comparing(EventStripe::getStartMillis)).collect(Collectors.toList());
    }

    private static String typeColumnHelper(final boolean useSubTypes) {
        return useSubTypes ? "sub_type" : "base_type"; //NON-NLS
    }

    private PreparedStatement prepareStatement(String queryString) throws SQLException {
        PreparedStatement prepareStatement = con.prepareStatement(queryString);
        preparedStatements.add(prepareStatement);
        return prepareStatement;
    }

    public class EventTransaction {

        private boolean closed = false;

        private EventTransaction() {

            //get the write lock, released in close()
            DBLock.lock();
            try {
                con.setAutoCommit(false);
            } catch (SQLException ex) {
                LOGGER.log(Level.SEVERE, "failed to set auto-commit to to false", ex); // NON-NLS
            }

        }

        private void rollback() {
            if (!closed) {
                try {
                    con.rollback();

                } catch (SQLException ex1) {
                    LOGGER.log(Level.SEVERE, "Exception while attempting to rollback!!", ex1); // NON-NLS
                } finally {
                    close();
                }
            }
        }

        private void commit() {
            if (!closed) {
                try {
                    con.commit();
                    // make sure we close before we update, bc they'll need locks
                    close();

                } catch (SQLException ex) {
                    LOGGER.log(Level.SEVERE, "Error commiting events.db.", ex); // NON-NLS
                    rollback();
                }
            }
        }

        private void close() {
            if (!closed) {
                try {
                    con.setAutoCommit(true);
                } catch (SQLException ex) {
                    LOGGER.log(Level.SEVERE, "Error setting auto-commit to true.", ex); // NON-NLS
                } finally {
                    closed = true;

                    DBLock.unlock();
                }
            }
        }

        public Boolean isClosed() {
            return closed;
        }
    }
}