SqliteEamDb.java

Go to the documentation of this file.

/*
 * Central Repository
 *
 * Copyright 2015-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.autopsy.centralrepository.datamodel;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;
import java.util.Set;
import java.util.concurrent.locks.ReentrantReadWriteLock;
import java.util.logging.Level;
import org.apache.commons.dbcp2.BasicDataSource;
import org.sleuthkit.autopsy.coreutils.Logger;
import org.sleuthkit.datamodel.TskData;
import org.sleuthkit.autopsy.casemodule.Case;
import org.sleuthkit.autopsy.coordinationservice.CoordinationService;

final class SqliteEamDb extends AbstractSqlEamDb {

    private final static Logger LOGGER = Logger.getLogger(SqliteEamDb.class.getName());

    private static SqliteEamDb instance;

    private BasicDataSource connectionPool = null;

    private final SqliteEamDbSettings dbSettings;

    // While the Sqlite database should only be used for single users, it is still
    // possible for multiple threads to attempt to write to the database simultaneously. 
    private final ReentrantReadWriteLock rwLock = new ReentrantReadWriteLock(true);

    public synchronized static SqliteEamDb getInstance() throws EamDbException {
        if (instance == null) {
            instance = new SqliteEamDb();
        }

        return instance;
    }

    private SqliteEamDb() throws EamDbException {
        dbSettings = new SqliteEamDbSettings();
        bulkArtifactsThreshold = dbSettings.getBulkThreshold();
    }

    @Override
    public void shutdownConnections() throws EamDbException {
        try {
            synchronized (this) {
                if (null != connectionPool) {
                    connectionPool.close();
                    connectionPool = null; // force it to be re-created on next connect()
                }
                clearCaches();
            }
        } catch (SQLException ex) {
            throw new EamDbException("Failed to close existing database connections.", ex); // NON-NLS
        }
    }

    @Override
    public void updateSettings() {
        synchronized (this) {
            dbSettings.loadSettings();
            bulkArtifactsThreshold = dbSettings.getBulkThreshold();
        }
    }

    @Override
    public void saveSettings() {
        synchronized (this) {
            dbSettings.saveSettings();
        }
    }

    @Override
    public void reset() throws EamDbException {
        try {
            acquireExclusiveLock();

            Connection conn = connect();

            try {

                Statement dropContent = conn.createStatement();
                dropContent.executeUpdate("DELETE FROM organizations");
                dropContent.executeUpdate("DELETE FROM cases");
                dropContent.executeUpdate("DELETE FROM data_sources");
                dropContent.executeUpdate("DELETE FROM reference_sets");
                dropContent.executeUpdate("DELETE FROM artifact_types");
                dropContent.executeUpdate("DELETE FROM db_info");

                String instancesTemplate = "DELETE FROM %s_instances";
                String referencesTemplate = "DELETE FROM global_files";
                for (CorrelationAttributeInstance.Type type : defaultCorrelationTypes) {
                    dropContent.executeUpdate(String.format(instancesTemplate, type.getDbTableName()));
                    // FUTURE: support other reference types
                    if (type.getId() == CorrelationAttributeInstance.FILES_TYPE_ID) {
                        dropContent.executeUpdate(String.format(referencesTemplate, type.getDbTableName()));
                    }
                }

                dropContent.executeUpdate("VACUUM");
            } catch (SQLException ex) {
                LOGGER.log(Level.WARNING, "Failed to reset database.", ex);
            } finally {
                EamDbUtil.closeConnection(conn);
            }

            dbSettings.insertDefaultDatabaseContent();
        } finally {
            releaseExclusiveLock();
        }
    }

    private void setupConnectionPool(boolean foreignKeysEnabled) throws EamDbException {

        if (dbSettings.dbFileExists() == false) {
            throw new EamDbException("Central repository database missing");
        }

        connectionPool = new BasicDataSource();
        connectionPool.setDriverClassName(dbSettings.getDriver());
        connectionPool.setUrl(dbSettings.getConnectionURL());

        // tweak pool configuration
        connectionPool.setInitialSize(50);
        connectionPool.setMaxTotal(-1);
        connectionPool.setMaxIdle(-1);
        connectionPool.setMaxWaitMillis(1000);
        connectionPool.setValidationQuery(dbSettings.getValidationQuery());
        if (foreignKeysEnabled) {
            connectionPool.setConnectionInitSqls(Arrays.asList("PRAGMA foreign_keys = ON"));
        } else {
            connectionPool.setConnectionInitSqls(Arrays.asList("PRAGMA foreign_keys = OFF"));
        }
    }

    @Override
    protected Connection connect(boolean foreignKeys) throws EamDbException {
        synchronized (this) {
            if (!EamDb.isEnabled()) {
                throw new EamDbException("Central Repository module is not enabled"); // NON-NLS
            }
            if (connectionPool == null) {
                setupConnectionPool(foreignKeys);
            }
            try {
                return connectionPool.getConnection();
            } catch (SQLException ex) {
                throw new EamDbException("Error getting connection from connection pool.", ex); // NON-NLS
            }
        }
    }

    @Override
    protected Connection connect() throws EamDbException {
        return connect(true);
    }
    
    @Override
    protected String getConflictClause() {
        // For sqlite, our conflict clause is part of the table schema
        return "";
    }

    @Override
    public void newDbInfo(String name, String value) throws EamDbException {
        try {
            acquireExclusiveLock();
            super.newDbInfo(name, value);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public String getDbInfo(String name) throws EamDbException {
        try {
            acquireSharedLock();
            return super.getDbInfo(name);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public void updateDbInfo(String name, String value) throws EamDbException {
        try {
            acquireExclusiveLock();
            super.updateDbInfo(name, value);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public CorrelationCase newCase(Case autopsyCase) throws EamDbException {
        try {
            acquireExclusiveLock();
            return super.newCase(autopsyCase);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public void addDataSourceObjectId(int rowId, long dataSourceObjectId) throws EamDbException {
        try {
            acquireExclusiveLock();
            super.addDataSourceObjectId(rowId, dataSourceObjectId);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public CorrelationCase newCase(CorrelationCase eamCase) throws EamDbException {
        try {
            acquireExclusiveLock();
            return super.newCase(eamCase);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public void updateCase(CorrelationCase eamCase) throws EamDbException {
        try {
            acquireExclusiveLock();
            super.updateCase(eamCase);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public CorrelationCase getCaseByUUID(String caseUUID) throws EamDbException {
        try {
            acquireSharedLock();
            return super.getCaseByUUID(caseUUID);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public CorrelationCase getCaseById(int caseId) throws EamDbException {
        try {
            acquireSharedLock();
            return super.getCaseById(caseId);
        } finally {
            releaseSharedLock();
        }

    }

    @Override
    public List<CorrelationCase> getCases() throws EamDbException {
        try {
            acquireSharedLock();
            return super.getCases();
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public CorrelationDataSource newDataSource(CorrelationDataSource eamDataSource) throws EamDbException {
        try {
            acquireExclusiveLock();
            return super.newDataSource(eamDataSource);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public CorrelationDataSource getDataSource(CorrelationCase correlationCase, Long caseDbDataSourceId) throws EamDbException {
        try {
            acquireSharedLock();
            return super.getDataSource(correlationCase, caseDbDataSourceId);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public CorrelationDataSource getDataSourceById(CorrelationCase correlationCase, int dataSourceId) throws EamDbException {
        try {
            acquireSharedLock();
            return super.getDataSourceById(correlationCase, dataSourceId);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public List<CorrelationDataSource> getDataSources() throws EamDbException {
        try {
            acquireSharedLock();
            return super.getDataSources();
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public void updateDataSourceName(CorrelationDataSource eamDataSource, String newName) throws EamDbException {
        try {
            acquireExclusiveLock();
            super.updateDataSourceName(eamDataSource, newName);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public void updateDataSourceMd5Hash(CorrelationDataSource eamDataSource) throws EamDbException {
        try {
            acquireExclusiveLock();
            super.updateDataSourceMd5Hash(eamDataSource);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public void updateDataSourceSha1Hash(CorrelationDataSource eamDataSource) throws EamDbException {
        try {
            acquireExclusiveLock();
            super.updateDataSourceSha1Hash(eamDataSource);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public void updateDataSourceSha256Hash(CorrelationDataSource eamDataSource) throws EamDbException {
        try {
            acquireExclusiveLock();
            super.updateDataSourceSha256Hash(eamDataSource);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public void addArtifactInstance(CorrelationAttributeInstance eamArtifact) throws EamDbException {
        try {
            acquireExclusiveLock();
            super.addArtifactInstance(eamArtifact);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public List<CorrelationAttributeInstance> getArtifactInstancesByTypeValue(CorrelationAttributeInstance.Type aType, String value) throws EamDbException, CorrelationAttributeNormalizationException {
        try {
            acquireSharedLock();
            return super.getArtifactInstancesByTypeValue(aType, value);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public List<CorrelationAttributeInstance> getArtifactInstancesByTypeValues(CorrelationAttributeInstance.Type aType, List<String> values) throws EamDbException, CorrelationAttributeNormalizationException {
        try {
            acquireSharedLock();
            return super.getArtifactInstancesByTypeValues(aType, values);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public List<CorrelationAttributeInstance> getArtifactInstancesByTypeValuesAndCases(CorrelationAttributeInstance.Type aType, List<String> values, List<Integer> caseIds) throws EamDbException, CorrelationAttributeNormalizationException {
        try {
            acquireSharedLock();
            return super.getArtifactInstancesByTypeValuesAndCases(aType, values, caseIds);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public List<CorrelationAttributeInstance> getArtifactInstancesByPath(CorrelationAttributeInstance.Type aType, String filePath) throws EamDbException {
        try {
            acquireSharedLock();
            return super.getArtifactInstancesByPath(aType, filePath);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public Long getCountArtifactInstancesByTypeValue(CorrelationAttributeInstance.Type aType, String value) throws EamDbException, CorrelationAttributeNormalizationException {
        try {
            acquireSharedLock();
            return super.getCountArtifactInstancesByTypeValue(aType, value);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public int getFrequencyPercentage(CorrelationAttributeInstance corAttr) throws EamDbException, CorrelationAttributeNormalizationException {
        try {
            acquireSharedLock();
            return super.getFrequencyPercentage(corAttr);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public Long getCountUniqueCaseDataSourceTuplesHavingTypeValue(CorrelationAttributeInstance.Type aType, String value) throws EamDbException, CorrelationAttributeNormalizationException {
        try {
            acquireSharedLock();
            return super.getCountUniqueCaseDataSourceTuplesHavingTypeValue(aType, value);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public Long getCountUniqueDataSources() throws EamDbException {
        try {
            acquireSharedLock();
            return super.getCountUniqueDataSources();
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public Long getCountArtifactInstancesByCaseDataSource(CorrelationDataSource correlationDataSource) throws EamDbException {
        try {
            acquireSharedLock();
            return super.getCountArtifactInstancesByCaseDataSource(correlationDataSource);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public void commitAttributeInstancesBulk() throws EamDbException {
        try {
            acquireExclusiveLock();
            super.commitAttributeInstancesBulk();
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public void bulkInsertCases(List<CorrelationCase> cases) throws EamDbException {
        try {
            acquireExclusiveLock();
            super.bulkInsertCases(cases);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public void setAttributeInstanceKnownStatus(CorrelationAttributeInstance eamArtifact, TskData.FileKnown knownStatus) throws EamDbException {
        try {
            acquireExclusiveLock();
            super.setAttributeInstanceKnownStatus(eamArtifact, knownStatus);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public List<CorrelationAttributeInstance> getArtifactInstancesKnownBad(CorrelationAttributeInstance.Type aType, String value) throws EamDbException, CorrelationAttributeNormalizationException {
        try {
            acquireSharedLock();
            return super.getArtifactInstancesKnownBad(aType, value);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public List<CorrelationAttributeInstance> getArtifactInstancesKnownBad(CorrelationAttributeInstance.Type aType) throws EamDbException {
        try {
            acquireSharedLock();
            return super.getArtifactInstancesKnownBad(aType);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public Long getCountArtifactInstancesKnownBad(CorrelationAttributeInstance.Type aType, String value) throws EamDbException, CorrelationAttributeNormalizationException {
        try {
            acquireSharedLock();
            return super.getCountArtifactInstancesKnownBad(aType, value);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public List<String> getListCasesHavingArtifactInstancesKnownBad(CorrelationAttributeInstance.Type aType, String value) throws EamDbException, CorrelationAttributeNormalizationException {
        try {
            acquireSharedLock();
            return super.getListCasesHavingArtifactInstancesKnownBad(aType, value);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public void deleteReferenceSet(int referenceSetID) throws EamDbException {
        try {
            acquireExclusiveLock();
            super.deleteReferenceSet(referenceSetID);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public boolean isValueInReferenceSet(String value, int referenceSetID, int correlationTypeID) throws EamDbException, CorrelationAttributeNormalizationException {
        try {
            acquireSharedLock();
            return super.isValueInReferenceSet(value, referenceSetID, correlationTypeID);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public void processInstanceTable(CorrelationAttributeInstance.Type type, InstanceTableCallback instanceTableCallback) throws EamDbException {
        try {
            acquireSharedLock();
            super.processInstanceTable(type, instanceTableCallback);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public void processInstanceTableWhere(CorrelationAttributeInstance.Type type, String whereClause, InstanceTableCallback instanceTableCallback) throws EamDbException {
        try {
            acquireSharedLock();
            super.processInstanceTableWhere(type, whereClause, instanceTableCallback);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public boolean referenceSetExists(String referenceSetName, String version) throws EamDbException {
        try {
            acquireSharedLock();
            return super.referenceSetExists(referenceSetName, version);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public boolean isArtifactKnownBadByReference(CorrelationAttributeInstance.Type aType, String value) throws EamDbException, CorrelationAttributeNormalizationException {
        try {
            acquireSharedLock();
            return super.isArtifactKnownBadByReference(aType, value);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public EamOrganization newOrganization(EamOrganization eamOrg) throws EamDbException {
        try {
            acquireExclusiveLock();
            return super.newOrganization(eamOrg);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public List<EamOrganization> getOrganizations() throws EamDbException {
        try {
            acquireSharedLock();
            return super.getOrganizations();
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public EamOrganization getOrganizationByID(int orgID) throws EamDbException {
        try {
            acquireSharedLock();
            return super.getOrganizationByID(orgID);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public void updateOrganization(EamOrganization updatedOrganization) throws EamDbException {
        try {
            acquireExclusiveLock();
            super.updateOrganization(updatedOrganization);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public void deleteOrganization(EamOrganization organizationToDelete) throws EamDbException {
        try {
            acquireExclusiveLock();
            super.deleteOrganization(organizationToDelete);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public int newReferenceSet(EamGlobalSet eamGlobalSet) throws EamDbException {
        try {
            acquireExclusiveLock();
            return super.newReferenceSet(eamGlobalSet);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public EamGlobalSet getReferenceSetByID(int referenceSetID) throws EamDbException {
        try {
            acquireSharedLock();
            return super.getReferenceSetByID(referenceSetID);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public List<EamGlobalSet> getAllReferenceSets(CorrelationAttributeInstance.Type correlationType) throws EamDbException {
        try {
            acquireSharedLock();
            return super.getAllReferenceSets(correlationType);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public void addReferenceInstance(EamGlobalFileInstance eamGlobalFileInstance, CorrelationAttributeInstance.Type correlationType) throws EamDbException {
        try {
            acquireExclusiveLock();
            super.addReferenceInstance(eamGlobalFileInstance, correlationType);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public void bulkInsertReferenceTypeEntries(Set<EamGlobalFileInstance> globalInstances, CorrelationAttributeInstance.Type contentType) throws EamDbException {
        try {
            acquireExclusiveLock();
            super.bulkInsertReferenceTypeEntries(globalInstances, contentType);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public List<EamGlobalFileInstance> getReferenceInstancesByTypeValue(CorrelationAttributeInstance.Type aType, String aValue) throws EamDbException, CorrelationAttributeNormalizationException {
        try {
            acquireSharedLock();
            return super.getReferenceInstancesByTypeValue(aType, aValue);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public int newCorrelationType(CorrelationAttributeInstance.Type newType) throws EamDbException {
        try {
            acquireExclusiveLock();
            return super.newCorrelationType(newType);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public List<CorrelationAttributeInstance.Type> getDefinedCorrelationTypes() throws EamDbException {
        try {
            acquireSharedLock();
            return super.getDefinedCorrelationTypes();
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public List<CorrelationAttributeInstance.Type> getEnabledCorrelationTypes() throws EamDbException {
        try {
            acquireSharedLock();
            return super.getEnabledCorrelationTypes();
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public List<CorrelationAttributeInstance.Type> getSupportedCorrelationTypes() throws EamDbException {
        try {
            acquireSharedLock();
            return super.getSupportedCorrelationTypes();
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public void updateCorrelationType(CorrelationAttributeInstance.Type aType) throws EamDbException {
        try {
            acquireExclusiveLock();
            super.updateCorrelationType(aType);
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public CorrelationAttributeInstance.Type getCorrelationTypeById(int typeId) throws EamDbException {
        try {
            acquireSharedLock();
            return super.getCorrelationTypeById(typeId);
        } finally {
            releaseSharedLock();
        }
    }

    @Override
    public void upgradeSchema() throws EamDbException, SQLException, IncompatibleCentralRepoException {
        try {
            acquireExclusiveLock();
            super.upgradeSchema();
        } finally {
            releaseExclusiveLock();
        }
    }

    @Override
    public CoordinationService.Lock getExclusiveMultiUserDbLock() throws EamDbException {
        // Multiple users are not supported for SQLite
        return null;
    }

    private void acquireExclusiveLock() {
        rwLock.writeLock().lock();
    }

    private void releaseExclusiveLock() {
        rwLock.writeLock().unlock();
    }

    private void acquireSharedLock() {
        rwLock.readLock().lock();
    }

    private void releaseSharedLock() {
        rwLock.readLock().unlock();
    }

    @Override
    boolean doesColumnExist(Connection conn, String tableName, String columnName) throws SQLException {
        final String tableInfoQueryTemplate = "PRAGMA table_info(%s)";  //NON-NLS
        ResultSet resultSet = null;
        Statement statement = null;
        boolean columnExists = false;
        try {
            statement = conn.createStatement();
            resultSet = statement.executeQuery(String.format(tableInfoQueryTemplate, tableName));
            while (resultSet.next()) {
                // the second value ( 2 ) is the column name
                if (resultSet.getString(2).equals(columnName)) {
                    columnExists = true;
                    break;
                }
            }
        } finally {
            EamDbUtil.closeResultSet(resultSet);
            EamDbUtil.closeStatement(statement);
        }
        return columnExists;
    }
}