TimelineManager.java

Go to the documentation of this file.

/*
 * Sleuth Kit Data Model
 *
 * Copyright 2018-2019 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.datamodel;

import com.google.common.annotations.Beta;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import static java.util.Objects.isNull;
import java.util.Optional;
import java.util.Set;
import java.util.stream.Collectors;
import org.joda.time.DateTimeZone;
import org.joda.time.Interval;
import static org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_TL_EVENT;
import static org.sleuthkit.datamodel.BlackboardAttribute.ATTRIBUTE_TYPE.TSK_TL_EVENT_TYPE;
import static org.sleuthkit.datamodel.CollectionUtils.isNotEmpty;
import org.sleuthkit.datamodel.SleuthkitCase.CaseDbConnection;
import static org.sleuthkit.datamodel.SleuthkitCase.escapeSingleQuotes;
import static org.sleuthkit.datamodel.StringUtils.buildCSVString;

public final class TimelineManager {

        private static final ImmutableList<TimelineEventType> ROOT_CATEGORY_AND_FILESYSTEM_TYPES
                        = ImmutableList.of(
                                        TimelineEventType.ROOT_EVENT_TYPE,
                                        TimelineEventType.WEB_ACTIVITY,
                                        TimelineEventType.MISC_TYPES,
                                        TimelineEventType.FILE_SYSTEM,
                                        TimelineEventType.FILE_ACCESSED,
                                        TimelineEventType.FILE_CHANGED,
                                        TimelineEventType.FILE_CREATED,
                                        TimelineEventType.FILE_MODIFIED);

        private static final ImmutableList<TimelineEventType> PREDEFINED_EVENT_TYPES
                        = new ImmutableList.Builder<TimelineEventType>()
                                        .add(TimelineEventType.CUSTOM_TYPES)
                                        .addAll(TimelineEventType.WEB_ACTIVITY.getChildren())
                                        .addAll(TimelineEventType.MISC_TYPES.getChildren())
                                        .addAll(TimelineEventType.CUSTOM_TYPES.getChildren())
                                        .build();

        private final SleuthkitCase caseDB;

        private final Map<Long, TimelineEventType> eventTypeIDMap = new HashMap<>();

        TimelineManager(SleuthkitCase caseDB) throws TskCoreException {
                this.caseDB = caseDB;

                //initialize root and base event types, these are added to the DB in c++ land
                ROOT_CATEGORY_AND_FILESYSTEM_TYPES.forEach(eventType -> eventTypeIDMap.put(eventType.getTypeID(), eventType));

                //initialize the other event types that aren't added in c++
                caseDB.acquireSingleUserCaseWriteLock();
                try (final CaseDbConnection con = caseDB.getConnection();
                                final Statement statement = con.createStatement()) {
                        for (TimelineEventType type : PREDEFINED_EVENT_TYPES) {
                                con.executeUpdate(statement,
                                                insertOrIgnore(" INTO tsk_event_types(event_type_id, display_name, super_type_id) "
                                                                + "VALUES( " + type.getTypeID() + ", '"
                                                                + escapeSingleQuotes(type.getDisplayName()) + "',"
                                                                + type.getParent().getTypeID()
                                                                + ")")); //NON-NLS
                                eventTypeIDMap.put(type.getTypeID(), type);
                        }
                } catch (SQLException ex) {
                        throw new TskCoreException("Failed to initialize timeline event types", ex); // NON-NLS
                } finally {
                        caseDB.releaseSingleUserCaseWriteLock();
                }
        }

        public Interval getSpanningInterval(Collection<Long> eventIDs) throws TskCoreException {
                if (eventIDs.isEmpty()) {
                        return null;
                }
                final String query = "SELECT Min(time) as minTime, Max(time) as maxTime FROM tsk_events WHERE event_id IN (" + buildCSVString(eventIDs) + ")"; //NON-NLS
                caseDB.acquireSingleUserCaseReadLock();
                try (CaseDbConnection con = caseDB.getConnection();
                                Statement stmt = con.createStatement();
                                ResultSet results = stmt.executeQuery(query);) {
                        if (results.next()) {
                                return new Interval(results.getLong("minTime") * 1000, (results.getLong("maxTime") + 1) * 1000, DateTimeZone.UTC); // NON-NLS
                        }
                } catch (SQLException ex) {
                        throw new TskCoreException("Error executing get spanning interval query: " + query, ex); // NON-NLS
                } finally {
                        caseDB.releaseSingleUserCaseReadLock();
                }
                return null;
        }

        public Interval getSpanningInterval(Interval timeRange, TimelineFilter.RootFilter filter, DateTimeZone timeZone) throws TskCoreException {
                long start = timeRange.getStartMillis() / 1000;
                long end = timeRange.getEndMillis() / 1000;
                String sqlWhere = getSQLWhere(filter);
                String augmentedEventsTablesSQL = getAugmentedEventsTablesSQL(filter);
                String queryString = " SELECT (SELECT Max(time) FROM " + augmentedEventsTablesSQL
                                + "                      WHERE time <=" + start + " AND " + sqlWhere + ") AS start,"
                                + "              (SELECT Min(time)  FROM " + augmentedEventsTablesSQL
                                + "                      WHERE time >= " + end + " AND " + sqlWhere + ") AS end";//NON-NLS
                caseDB.acquireSingleUserCaseReadLock();
                try (CaseDbConnection con = caseDB.getConnection();
                                Statement stmt = con.createStatement(); //can't use prepared statement because of complex where clause
                                ResultSet results = stmt.executeQuery(queryString);) {

                        if (results.next()) {
                                long start2 = results.getLong("start"); // NON-NLS
                                long end2 = results.getLong("end"); // NON-NLS

                                if (end2 == 0) {
                                        end2 = getMaxEventTime();
                                }
                                return new Interval(start2 * 1000, (end2 + 1) * 1000, timeZone);
                        }
                } catch (SQLException ex) {
                        throw new TskCoreException("Failed to get MIN time.", ex); // NON-NLS
                } finally {
                        caseDB.releaseSingleUserCaseReadLock();
                }
                return null;
        }

        public TimelineEvent getEventById(long eventID) throws TskCoreException {
                String sql = "SELECT * FROM  " + getAugmentedEventsTablesSQL(false) + " WHERE event_id = " + eventID;
                caseDB.acquireSingleUserCaseReadLock();
                try (CaseDbConnection con = caseDB.getConnection();
                                Statement stmt = con.createStatement();) {
                        try (ResultSet results = stmt.executeQuery(sql);) {
                                if (results.next()) {
                                        int typeID = results.getInt("event_type_id");
                                        TimelineEventType type = getEventType(typeID).orElseThrow(() -> newEventTypeMappingException(typeID)); //NON-NLS
                                        return new TimelineEvent(eventID,
                                                        results.getLong("data_source_obj_id"),
                                                        results.getLong("content_obj_id"),
                                                        results.getLong("artifact_id"),
                                                        results.getLong("time"),
                                                        type, results.getString("full_description"),
                                                        results.getString("med_description"),
                                                        results.getString("short_description"),
                                                        intToBoolean(results.getInt("hash_hit")),
                                                        intToBoolean(results.getInt("tagged")));
                                }
                        }
                } catch (SQLException sqlEx) {
                        throw new TskCoreException("Error while executing query " + sql, sqlEx); // NON-NLS
                } finally {
                        caseDB.releaseSingleUserCaseReadLock();
                }
                return null;
        }

        public List<Long> getEventIDs(Interval timeRange, TimelineFilter.RootFilter filter) throws TskCoreException {
                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<>();

                String query = "SELECT tsk_events.event_id AS event_id FROM " + getAugmentedEventsTablesSQL(filter)
                                + " WHERE time >=  " + startTime + " AND time <" + endTime + " AND " + getSQLWhere(filter) + " ORDER BY time ASC"; // NON-NLS
                caseDB.acquireSingleUserCaseReadLock();
                try (CaseDbConnection con = caseDB.getConnection();
                                Statement stmt = con.createStatement();
                                ResultSet results = stmt.executeQuery(query);) {
                        while (results.next()) {
                                resultIDs.add(results.getLong("event_id")); //NON-NLS
                        }

                } catch (SQLException sqlEx) {
                        throw new TskCoreException("Error while executing query " + query, sqlEx); // NON-NLS
                } finally {
                        caseDB.releaseSingleUserCaseReadLock();
                }

                return resultIDs;
        }

        public Long getMaxEventTime() throws TskCoreException {
                caseDB.acquireSingleUserCaseReadLock();
                try (CaseDbConnection con = caseDB.getConnection();
                                Statement stms = con.createStatement();
                                ResultSet results = stms.executeQuery(STATEMENTS.GET_MAX_TIME.getSQL());) {
                        if (results.next()) {
                                return results.getLong("max"); // NON-NLS
                        }
                } catch (SQLException ex) {
                        throw new TskCoreException("Error while executing query " + STATEMENTS.GET_MAX_TIME.getSQL(), ex); // NON-NLS
                } finally {
                        caseDB.releaseSingleUserCaseReadLock();
                }
                return -1l;
        }

        public Long getMinEventTime() throws TskCoreException {
                caseDB.acquireSingleUserCaseReadLock();
                try (CaseDbConnection con = caseDB.getConnection();
                                Statement stms = con.createStatement();
                                ResultSet results = stms.executeQuery(STATEMENTS.GET_MIN_TIME.getSQL());) {
                        if (results.next()) {
                                return results.getLong("min"); // NON-NLS
                        }
                } catch (SQLException ex) {
                        throw new TskCoreException("Error while executing query " + STATEMENTS.GET_MAX_TIME.getSQL(), ex); // NON-NLS
                } finally {
                        caseDB.releaseSingleUserCaseReadLock();
                }
                return -1l;
        }

        public Optional<TimelineEventType> getEventType(long eventTypeID) {
                return Optional.ofNullable(eventTypeIDMap.get(eventTypeID));
        }

        public ImmutableList<TimelineEventType> getEventTypes() {
                return ImmutableList.copyOf(eventTypeIDMap.values());
        }

        private String insertOrIgnore(String query) {
                switch (caseDB.getDatabaseType()) {
                        case POSTGRESQL:
                                return " INSERT " + query + " ON CONFLICT DO NOTHING "; //NON-NLS
                        case SQLITE:
                                return " INSERT OR IGNORE " + query; //NON-NLS
                        default:
                                throw new UnsupportedOperationException("Unsupported DB type: " + caseDB.getDatabaseType().name());
                }
        }

        private enum STATEMENTS {

                GET_MAX_TIME("SELECT Max(time) AS max FROM tsk_events"), // NON-NLS
                GET_MIN_TIME("SELECT Min(time) AS min FROM tsk_events"); // NON-NLS

                private final String sql;

                private STATEMENTS(String sql) {
                        this.sql = sql;
                }

                String getSQL() {
                        return sql;
                }
        }

        public List<Long> getEventIDsForArtifact(BlackboardArtifact artifact) throws TskCoreException {
                ArrayList<Long> eventIDs = new ArrayList<>();

                String query
                                = "SELECT event_id FROM tsk_events "
                                + " LEFT JOIN tsk_event_descriptions on ( tsk_events.event_description_id = tsk_event_descriptions.event_description_id ) "
                                + " WHERE artifact_id = " + artifact.getArtifactID();
                caseDB.acquireSingleUserCaseReadLock();
                try (CaseDbConnection con = caseDB.getConnection();
                                Statement stmt = con.createStatement();
                                ResultSet results = stmt.executeQuery(query);) {
                        while (results.next()) {
                                eventIDs.add(results.getLong("event_id"));//NON-NLS
                        }
                } catch (SQLException ex) {
                        throw new TskCoreException("Error executing getEventIDsForArtifact query.", ex); // NON-NLS
                } finally {
                        caseDB.releaseSingleUserCaseReadLock();
                }
                return eventIDs;
        }

        public Set<Long> getEventIDsForContent(Content content, boolean includeDerivedArtifacts) throws TskCoreException {
                caseDB.acquireSingleUserCaseWriteLock();
                try (CaseDbConnection conn = caseDB.getConnection()) {
                        return getEventAndDescriptionIDs(conn, content.getId(), includeDerivedArtifacts).keySet();
                } finally {
                        caseDB.releaseSingleUserCaseWriteLock();
                }
        }

        private long addEventDescription(long dataSourceObjId, long fileObjId, Long artifactID,
                        String fullDescription, String medDescription, String shortDescription,
                        boolean hasHashHits, boolean tagged, CaseDbConnection connection) throws TskCoreException {
                String insertDescriptionSql
                                = "INSERT INTO tsk_event_descriptions ( "
                                + "data_source_obj_id, content_obj_id, artifact_id,  "
                                + " full_description, med_description, short_description, "
                                + " hash_hit, tagged "
                                + " ) VALUES ("
                                + dataSourceObjId + ","
                                + fileObjId + ","
                                + Objects.toString(artifactID, "NULL") + ","
                                + quotePreservingNull(fullDescription) + ","
                                + quotePreservingNull(medDescription) + ","
                                + quotePreservingNull(shortDescription) + ", "
                                + booleanToInt(hasHashHits) + ","
                                + booleanToInt(tagged)
                                + " )";

                caseDB.acquireSingleUserCaseWriteLock();
                try (Statement insertDescriptionStmt = connection.createStatement()) {
                        connection.executeUpdate(insertDescriptionStmt, insertDescriptionSql, PreparedStatement.RETURN_GENERATED_KEYS);
                        try (ResultSet generatedKeys = insertDescriptionStmt.getGeneratedKeys()) {
                                generatedKeys.next();
                                return generatedKeys.getLong(1);
                        }
                } catch (SQLException ex) {
                        throw new TskCoreException("Failed to insert event description.", ex); // NON-NLS
                } finally {
                        caseDB.releaseSingleUserCaseWriteLock();
                }
        }

        Collection<TimelineEvent> addEventsForNewFile(AbstractFile file, CaseDbConnection connection) throws TskCoreException {
                //gather time stamps into map
                Map<TimelineEventType, Long> timeMap = ImmutableMap.of(TimelineEventType.FILE_CREATED, file.getCrtime(),
                                TimelineEventType.FILE_ACCESSED, file.getAtime(),
                                TimelineEventType.FILE_CHANGED, file.getCtime(),
                                TimelineEventType.FILE_MODIFIED, file.getMtime());

                /*
                 * If there are no legitimate ( greater than zero ) time stamps skip the
                 * rest of the event generation.
                 */
                if (Collections.max(timeMap.values()) <= 0) {
                        return Collections.emptySet();
                }

                String description = file.getParentPath() + file.getName();
                long fileObjId = file.getId();
                Set<TimelineEvent> events = new HashSet<>();
                caseDB.acquireSingleUserCaseWriteLock();
                try {
                        long descriptionID = addEventDescription(file.getDataSourceObjectId(), fileObjId, null,
                                        description, null, null, false, false, connection);

                        for (Map.Entry<TimelineEventType, Long> timeEntry : timeMap.entrySet()) {
                                Long time = timeEntry.getValue();
                                if (time > 0) {// if the time is legitimate ( greater than zero ) insert it
                                        TimelineEventType type = timeEntry.getKey();
                                        long eventID = addEventWithExistingDescription(time, type, descriptionID, connection);

                                        /*
                                         * Last two flags indicating hasTags and hasHashHits are
                                         * both set to false with the assumption that this is not
                                         * possible for a new file. See JIRA-5407
                                         */
                                        events.add(new TimelineEvent(eventID, descriptionID, fileObjId, null, time, type,
                                                        description, null, null, false, false));
                                }
                        }

                } finally {
                        caseDB.releaseSingleUserCaseWriteLock();
                }
                events.stream()
                                .map(TimelineEventAddedEvent::new)
                                .forEach(caseDB::fireTSKEvent);

                return events;
        }

        Set<TimelineEvent> addArtifactEvents(BlackboardArtifact artifact) throws TskCoreException {
                Set<TimelineEvent> newEvents = new HashSet<>();

                /*
                 * If the artifact is a TSK_TL_EVENT, use the TSK_TL_EVENT_TYPE
                 * attribute to determine its event type, but give it a generic
                 * description.
                 */
                if (artifact.getArtifactTypeID() == TSK_TL_EVENT.getTypeID()) {
                        TimelineEventType eventType;//the type of the event to add.
                        BlackboardAttribute attribute = artifact.getAttribute(new BlackboardAttribute.Type(TSK_TL_EVENT_TYPE));
                        if (attribute == null) {
                                eventType = TimelineEventType.OTHER;
                        } else {
                                long eventTypeID = attribute.getValueLong();
                                eventType = eventTypeIDMap.getOrDefault(eventTypeID, TimelineEventType.OTHER);
                        }

                        // @@@ This casting is risky if we change class hierarchy, but was expedient.  Should move parsing to another class
                        addArtifactEvent(((TimelineEventArtifactTypeImpl) TimelineEventType.OTHER)::makeEventDescription, eventType, artifact)
                                        .ifPresent(newEvents::add);
                } else {
                        /*
                         * If there are any event types configured to make descriptions
                         * automatically, use those.
                         */
                        Set<TimelineEventArtifactTypeImpl> eventTypesForArtifact = eventTypeIDMap.values().stream()
                                        .filter(TimelineEventArtifactTypeImpl.class::isInstance)
                                        .map(TimelineEventArtifactTypeImpl.class::cast)
                                        .filter(eventType -> eventType.getArtifactTypeID() == artifact.getArtifactTypeID())
                                        .collect(Collectors.toSet());

                        for (TimelineEventArtifactTypeImpl eventType : eventTypesForArtifact) {
                                addArtifactEvent(eventType::makeEventDescription, eventType, artifact)
                                                .ifPresent(newEvents::add);
                        }
                }
                newEvents.stream()
                                .map(TimelineEventAddedEvent::new)
                                .forEach(caseDB::fireTSKEvent);
                return newEvents;
        }

        private Optional<TimelineEvent> addArtifactEvent(TSKCoreCheckedFunction<BlackboardArtifact, TimelineEventDescriptionWithTime> payloadExtractor,
                        TimelineEventType eventType, BlackboardArtifact artifact) throws TskCoreException {
                TimelineEventDescriptionWithTime eventPayload = payloadExtractor.apply(artifact);
                if (eventPayload == null) {
                        return Optional.empty();
                }
                long time = eventPayload.getTime();
                // if the time is legitimate ( greater than zero ) insert it into the db
                if (time <= 0) {
                        return Optional.empty();
                }
                String fullDescription = eventPayload.getDescription(TimelineLevelOfDetail.HIGH);
                String medDescription = eventPayload.getDescription(TimelineLevelOfDetail.MEDIUM);
                String shortDescription = eventPayload.getDescription(TimelineLevelOfDetail.LOW);
                long artifactID = artifact.getArtifactID();
                long fileObjId = artifact.getObjectID();
                long dataSourceObjectID = artifact.getDataSourceObjectID();

                AbstractFile file = caseDB.getAbstractFileById(fileObjId);
                boolean hasHashHits = false;
                // file will be null if source was data source or some non-file
                if (file != null) {
                        hasHashHits = isNotEmpty(file.getHashSetNames());
                }
                boolean tagged = isNotEmpty(caseDB.getBlackboardArtifactTagsByArtifact(artifact));

                TimelineEvent event;
                caseDB.acquireSingleUserCaseWriteLock();
                try (CaseDbConnection connection = caseDB.getConnection();) {

                        long descriptionID = addEventDescription(dataSourceObjectID, fileObjId, artifactID,
                                        fullDescription, medDescription, shortDescription,
                                        hasHashHits, tagged, connection);

                        long eventID = addEventWithExistingDescription(time, eventType, descriptionID, connection);

                        event = new TimelineEvent(eventID, dataSourceObjectID, fileObjId, artifactID,
                                        time, eventType, fullDescription, medDescription, shortDescription,
                                        hasHashHits, tagged);

                } finally {
                        caseDB.releaseSingleUserCaseWriteLock();
                }
                return Optional.of(event);
        }

        private long addEventWithExistingDescription(Long time, TimelineEventType type, long descriptionID, CaseDbConnection connection) throws TskCoreException {
                String insertEventSql
                                = "INSERT INTO tsk_events ( event_type_id, event_description_id , time) "
                                + " VALUES (" + type.getTypeID() + ", " + descriptionID + ", " + time + ")";

                caseDB.acquireSingleUserCaseWriteLock();
                try (Statement insertRowStmt = connection.createStatement();) {
                        connection.executeUpdate(insertRowStmt, insertEventSql, PreparedStatement.RETURN_GENERATED_KEYS);

                        try (ResultSet generatedKeys = insertRowStmt.getGeneratedKeys();) {
                                generatedKeys.next();
                                return generatedKeys.getLong(1);
                        }
                } catch (SQLException ex) {
                        throw new TskCoreException("Failed to insert event for existing description.", ex); // NON-NLS
                } finally {
                        caseDB.releaseSingleUserCaseWriteLock();
                }
        }

        static private String quotePreservingNull(String value) {
                return isNull(value) ? " NULL " : "'" + escapeSingleQuotes(value) + "'";//NON-NLS
        }

        private Map<Long, Long> getEventAndDescriptionIDs(CaseDbConnection conn, long contentObjID, boolean includeArtifacts) throws TskCoreException {
                return getEventAndDescriptionIDsHelper(conn, contentObjID, (includeArtifacts ? "" : " AND artifact_id IS NULL"));
        }

        private Map<Long, Long> getEventAndDescriptionIDs(CaseDbConnection conn, long contentObjID, Long artifactID) throws TskCoreException {
                return getEventAndDescriptionIDsHelper(conn, contentObjID, " AND artifact_id = " + artifactID);
        }

        private Map<Long, Long> getEventAndDescriptionIDsHelper(CaseDbConnection con, long fileObjID, String artifactClause) throws TskCoreException {
                //map from event_id to the event_description_id for that event.
                Map<Long, Long> eventIDToDescriptionIDs = new HashMap<>();
                String sql = "SELECT event_id, tsk_events.event_description_id"
                                + " FROM tsk_events "
                                + " LEFT JOIN tsk_event_descriptions ON ( tsk_events.event_description_id = tsk_event_descriptions.event_description_id )"
                                + " WHERE content_obj_id = " + fileObjID
                                + artifactClause;
                try (Statement selectStmt = con.createStatement(); ResultSet executeQuery = selectStmt.executeQuery(sql);) {
                        while (executeQuery.next()) {
                                eventIDToDescriptionIDs.put(executeQuery.getLong("event_id"), executeQuery.getLong("event_description_id")); //NON-NLS
                        }
                } catch (SQLException ex) {
                        throw new TskCoreException("Error getting event description ids for object id = " + fileObjID, ex);
                }
                return eventIDToDescriptionIDs;
        }

        @Beta
        public Set<Long> updateEventsForContentTagAdded(Content content) throws TskCoreException {
                caseDB.acquireSingleUserCaseWriteLock();
                try (CaseDbConnection conn = caseDB.getConnection()) {
                        Map<Long, Long> eventIDs = getEventAndDescriptionIDs(conn, content.getId(), false);
                        updateEventSourceTaggedFlag(conn, eventIDs.values(), 1);
                        return eventIDs.keySet();
                } finally {
                        caseDB.releaseSingleUserCaseWriteLock();
                }
        }

        @Beta
        public Set<Long> updateEventsForContentTagDeleted(Content content) throws TskCoreException {
                caseDB.acquireSingleUserCaseWriteLock();
                try (CaseDbConnection conn = caseDB.getConnection()) {
                        if (caseDB.getContentTagsByContent(content).isEmpty()) {
                                Map<Long, Long> eventIDs = getEventAndDescriptionIDs(conn, content.getId(), false);
                                updateEventSourceTaggedFlag(conn, eventIDs.values(), 0);
                                return eventIDs.keySet();
                        } else {
                                return Collections.emptySet();
                        }
                } finally {
                        caseDB.releaseSingleUserCaseWriteLock();
                }
        }

        public Set<Long> updateEventsForArtifactTagAdded(BlackboardArtifact artifact) throws TskCoreException {
                caseDB.acquireSingleUserCaseWriteLock();
                try (CaseDbConnection conn = caseDB.getConnection()) {
                        Map<Long, Long> eventIDs = getEventAndDescriptionIDs(conn, artifact.getObjectID(), artifact.getArtifactID());
                        updateEventSourceTaggedFlag(conn, eventIDs.values(), 1);
                        return eventIDs.keySet();
                } finally {
                        caseDB.releaseSingleUserCaseWriteLock();
                }
        }

        public Set<Long> updateEventsForArtifactTagDeleted(BlackboardArtifact artifact) throws TskCoreException {
                caseDB.acquireSingleUserCaseWriteLock();
                try (CaseDbConnection conn = caseDB.getConnection()) {
                        if (caseDB.getBlackboardArtifactTagsByArtifact(artifact).isEmpty()) {
                                Map<Long, Long> eventIDs = getEventAndDescriptionIDs(conn, artifact.getObjectID(), artifact.getArtifactID());
                                updateEventSourceTaggedFlag(conn, eventIDs.values(), 0);
                                return eventIDs.keySet();
                        } else {
                                return Collections.emptySet();
                        }
                } finally {
                        caseDB.releaseSingleUserCaseWriteLock();
                }
        }

        private void updateEventSourceTaggedFlag(CaseDbConnection conn, Collection<Long> eventDescriptionIDs, int flagValue) throws TskCoreException {
                if (eventDescriptionIDs.isEmpty()) {
                        return;
                }
                
                String sql = "UPDATE tsk_event_descriptions SET tagged = " + flagValue + " WHERE event_description_id IN (" + buildCSVString(eventDescriptionIDs) + ")"; //NON-NLS
                try (Statement updateStatement = conn.createStatement()) {
                        updateStatement.executeUpdate(sql);
                } catch (SQLException ex) {
                        throw new TskCoreException("Error marking content events tagged: " + sql, ex);//NON-NLS
                }
        }

        public Set<Long> updateEventsForHashSetHit(Content content) throws TskCoreException {
                caseDB.acquireSingleUserCaseWriteLock();
                try (CaseDbConnection con = caseDB.getConnection(); Statement updateStatement = con.createStatement();) {
                        Map<Long, Long> eventIDs = getEventAndDescriptionIDs(con, content.getId(), true);
                        if (! eventIDs.isEmpty()) {
                                String sql = "UPDATE tsk_event_descriptions SET hash_hit = 1" + " WHERE event_description_id IN (" + buildCSVString(eventIDs.values()) + ")"; //NON-NLS
                                try {
                                        updateStatement.executeUpdate(sql); //NON-NLS
                                        return eventIDs.keySet();
                                } catch (SQLException ex) {
                                        throw new TskCoreException("Error setting hash_hit of events.", ex);//NON-NLS
                                }
                        } else {
                                return eventIDs.keySet();
                        }
                } catch (SQLException ex) {
                        throw new TskCoreException("Error setting hash_hit of events.", ex);//NON-NLS
                } finally {
                        caseDB.releaseSingleUserCaseWriteLock();
                }
        }

        void rollBackTransaction(SleuthkitCase.CaseDbTransaction trans) throws TskCoreException {
                trans.rollback();
        }

        public Map<TimelineEventType, Long> countEventsByType(Long startTime, Long endTime, TimelineFilter.RootFilter filter, TimelineEventType.HierarchyLevel typeHierachyLevel) throws TskCoreException {
                long adjustedEndTime = Objects.equals(startTime, endTime) ? endTime + 1 : endTime;
                //do we want the base or subtype column of the databse
                String typeColumn = typeColumnHelper(TimelineEventType.HierarchyLevel.EVENT.equals(typeHierachyLevel));

                String queryString = "SELECT count(DISTINCT tsk_events.event_id) AS count, " + typeColumn//NON-NLS
                                + " FROM " + getAugmentedEventsTablesSQL(filter)//NON-NLS
                                + " WHERE time >= " + startTime + " AND time < " + adjustedEndTime + " AND " + getSQLWhere(filter) // NON-NLS
                                + " GROUP BY " + typeColumn; // NON-NLS

                caseDB.acquireSingleUserCaseReadLock();
                try (CaseDbConnection con = caseDB.getConnection();
                                Statement stmt = con.createStatement();
                                ResultSet results = stmt.executeQuery(queryString);) {
                        Map<TimelineEventType, Long> typeMap = new HashMap<>();
                        while (results.next()) {
                                int eventTypeID = results.getInt(typeColumn);
                                TimelineEventType eventType = getEventType(eventTypeID)
                                                .orElseThrow(() -> newEventTypeMappingException(eventTypeID));//NON-NLS

                                typeMap.put(eventType, results.getLong("count")); // NON-NLS
                        }
                        return typeMap;
                } catch (SQLException ex) {
                        throw new TskCoreException("Error getting count of events from db: " + queryString, ex); // NON-NLS
                } finally {
                        caseDB.releaseSingleUserCaseReadLock();
                }
        }

        private static TskCoreException newEventTypeMappingException(int eventTypeID) {
                return new TskCoreException("Error mapping event type id " + eventTypeID + " to EventType.");//NON-NLS
        }

        static private String getAugmentedEventsTablesSQL(TimelineFilter.RootFilter filter) {
                TimelineFilter.FileTypesFilter fileTypesFitler = filter.getFileTypesFilter();
                boolean needsMimeTypes = fileTypesFitler != null && fileTypesFitler.hasSubFilters();

                return getAugmentedEventsTablesSQL(needsMimeTypes);
        }

        static private String getAugmentedEventsTablesSQL(boolean needMimeTypes) {
                /*
                 * Regarding the timeline event tables schema, note that several columns
                 * in the tsk_event_descriptions table seem, at first glance, to be
                 * attributes of events rather than their descriptions and would appear
                 * to belong in tsk_events table instead. The rationale for putting the
                 * data source object ID, content object ID, artifact ID and the flags
                 * indicating whether or not the event source has a hash set hit or is
                 * tagged were motivated by the fact that these attributes are identical
                 * for each event in a set of file system file MAC time events. The
                 * decision was made to avoid duplication and save space by placing this
                 * data in the tsk_event-descriptions table.
                 */
                return "( SELECT event_id, time, tsk_event_descriptions.data_source_obj_id, content_obj_id, artifact_id, "
                                + " full_description, med_description, short_description, tsk_events.event_type_id, super_type_id,"
                                + " hash_hit, tagged "
                                + (needMimeTypes ? ", mime_type" : "")
                                + " FROM tsk_events "
                                + " JOIN tsk_event_descriptions ON ( tsk_event_descriptions.event_description_id = tsk_events.event_description_id)"
                                + " JOIN tsk_event_types ON (tsk_events.event_type_id = tsk_event_types.event_type_id )  "
                                + (needMimeTypes ? " LEFT OUTER JOIN tsk_files "
                                                + "     ON (tsk_event_descriptions.content_obj_id = tsk_files.obj_id)"
                                                : "")
                                + ")  AS tsk_events";
        }

        private static int booleanToInt(boolean value) {
                return value ? 1 : 0;
        }

        private static boolean intToBoolean(int value) {
                return value != 0;
        }

        public List<TimelineEvent> getEvents(Interval timeRange, TimelineFilter.RootFilter filter) throws TskCoreException {
                List<TimelineEvent> events = new ArrayList<>();

                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
                }

                if (filter == null) {
                        return events;
                }

                if (endTime < startTime) {
                        return events;
                }

                //build dynamic parts of query
                String querySql = "SELECT time, content_obj_id, data_source_obj_id, artifact_id, " // NON-NLS
                                + "  event_id, " //NON-NLS
                                + " hash_hit, " //NON-NLS
                                + " tagged, " //NON-NLS
                                + " event_type_id, super_type_id, "
                                + " full_description, med_description, short_description " // NON-NLS
                                + " FROM " + getAugmentedEventsTablesSQL(filter) // NON-NLS
                                + " WHERE time >= " + startTime + " AND time < " + endTime + " AND " + getSQLWhere(filter) // NON-NLS
                                + " ORDER BY time"; // NON-NLS

                caseDB.acquireSingleUserCaseReadLock();
                try (CaseDbConnection con = caseDB.getConnection();
                                Statement stmt = con.createStatement();
                                ResultSet resultSet = stmt.executeQuery(querySql);) {

                        while (resultSet.next()) {
                                int eventTypeID = resultSet.getInt("event_type_id");
                                TimelineEventType eventType = getEventType(eventTypeID).orElseThrow(()
                                                -> new TskCoreException("Error mapping event type id " + eventTypeID + "to EventType."));//NON-NLS

                                TimelineEvent event = new TimelineEvent(
                                                resultSet.getLong("event_id"), // NON-NLS
                                                resultSet.getLong("data_source_obj_id"), // NON-NLS
                                                resultSet.getLong("content_obj_id"), // NON-NLS
                                                resultSet.getLong("artifact_id"), // NON-NLS
                                                resultSet.getLong("time"), // NON-NLS
                                                eventType,
                                                resultSet.getString("full_description"), // NON-NLS
                                                resultSet.getString("med_description"), // NON-NLS
                                                resultSet.getString("short_description"), // NON-NLS
                                                resultSet.getInt("hash_hit") != 0, //NON-NLS
                                                resultSet.getInt("tagged") != 0);

                                events.add(event);
                        }

                } catch (SQLException ex) {
                        throw new TskCoreException("Error getting events from db: " + querySql, ex); // NON-NLS
                } finally {
                        caseDB.releaseSingleUserCaseReadLock();
                }

                return events;
        }

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

        String getSQLWhere(TimelineFilter.RootFilter filter) {

                String result;
                if (filter == null) {
                        return getTrueLiteral();
                } else {
                        result = filter.getSQLWhere(this);
                }

                return result;
        }

        private String getTrueLiteral() {
                switch (caseDB.getDatabaseType()) {
                        case POSTGRESQL:
                                return "TRUE";//NON-NLS
                        case SQLITE:
                                return "1";//NON-NLS
                        default:
                                throw new UnsupportedOperationException("Unsupported DB type: " + caseDB.getDatabaseType().name());//NON-NLS

                }
        }

        final static public class TimelineEventAddedEvent {

                private final TimelineEvent addedEvent;

                public TimelineEvent getAddedEvent() {
                        return addedEvent;
                }

                TimelineEventAddedEvent(TimelineEvent event) {
                        this.addedEvent = event;
                }
        }

        @FunctionalInterface
        private interface TSKCoreCheckedFunction<I, O> {

                O apply(I input) throws TskCoreException;
        }
}