SqliteEamDbSettings.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.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.InvalidPathException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.logging.Level;
import java.util.regex.Pattern;
import org.sleuthkit.autopsy.coreutils.Logger;
import org.sleuthkit.autopsy.coreutils.ModuleSettings;
import org.sleuthkit.autopsy.coreutils.PlatformUtil;
import static org.sleuthkit.autopsy.centralrepository.datamodel.AbstractSqlEamDb.SOFTWARE_CR_DB_SCHEMA_VERSION;

public final class SqliteEamDbSettings {

    private final static Logger LOGGER = Logger.getLogger(SqliteEamDbSettings.class.getName());
    private final static String DEFAULT_DBNAME = "central_repository.db"; // NON-NLS
    private final static String DEFAULT_DBDIRECTORY = PlatformUtil.getUserDirectory() + File.separator + "central_repository"; // NON-NLS
    private final static String JDBC_DRIVER = "org.sqlite.JDBC"; // NON-NLS
    private final static String JDBC_BASE_URI = "jdbc:sqlite:"; // NON-NLS
    private final static String VALIDATION_QUERY = "SELECT count(*) from sqlite_master"; // NON-NLS
    private final static String PRAGMA_SYNC_OFF = "PRAGMA synchronous = OFF";
    private final static String PRAGMA_SYNC_NORMAL = "PRAGMA synchronous = NORMAL";
    private final static String PRAGMA_JOURNAL_WAL = "PRAGMA journal_mode = WAL";
    private final static String PRAGMA_READ_UNCOMMITTED_TRUE = "PRAGMA read_uncommitted = True";
    private final static String PRAGMA_ENCODING_UTF8 = "PRAGMA encoding = 'UTF-8'";
    private final static String PRAGMA_PAGE_SIZE_4096 = "PRAGMA page_size = 4096";
    private final static String PRAGMA_FOREIGN_KEYS_ON = "PRAGMA foreign_keys = ON";
    private final static String DB_NAMES_REGEX = "[a-z][a-z0-9_]*(\\.db)?";
    private String dbName;
    private String dbDirectory;
    private int bulkThreshold;

    public SqliteEamDbSettings() {
        loadSettings();
    }

    public void loadSettings() {
        dbName = ModuleSettings.getConfigSetting("CentralRepository", "db.sqlite.dbName"); // NON-NLS
        if (dbName == null || dbName.isEmpty()) {
            dbName = DEFAULT_DBNAME;
        }

        dbDirectory = ModuleSettings.getConfigSetting("CentralRepository", "db.sqlite.dbDirectory"); // NON-NLS
        if (dbDirectory == null || dbDirectory.isEmpty()) {
            dbDirectory = DEFAULT_DBDIRECTORY;
        }

        try {
            String bulkThresholdString = ModuleSettings.getConfigSetting("CentralRepository", "db.sqlite.bulkThreshold"); // NON-NLS
            if (bulkThresholdString == null || bulkThresholdString.isEmpty()) {
                this.bulkThreshold = AbstractSqlEamDb.DEFAULT_BULK_THRESHHOLD;
            } else {
                this.bulkThreshold = Integer.parseInt(bulkThresholdString);
                if (getBulkThreshold() <= 0) {
                    this.bulkThreshold = AbstractSqlEamDb.DEFAULT_BULK_THRESHHOLD;
                }
            }
        } catch (NumberFormatException ex) {
            this.bulkThreshold = AbstractSqlEamDb.DEFAULT_BULK_THRESHHOLD;
        }
    }

    public void saveSettings() {
        createDbDirectory();

        ModuleSettings.setConfigSetting("CentralRepository", "db.sqlite.dbName", getDbName()); // NON-NLS
        ModuleSettings.setConfigSetting("CentralRepository", "db.sqlite.dbDirectory", getDbDirectory()); // NON-NLS
        ModuleSettings.setConfigSetting("CentralRepository", "db.sqlite.bulkThreshold", Integer.toString(getBulkThreshold())); // NON-NLS
    }

    public boolean dbFileExists() {
        File dbFile = new File(getFileNameWithPath());
        if (!dbFile.exists()) {
            return false;
        }
        // It's unlikely, but make sure the file isn't actually a directory
        return (!dbFile.isDirectory());
    }

    public boolean dbDirectoryExists() {
        // Ensure dbDirectory is a valid directory
        File dbDir = new File(getDbDirectory());

        if (!dbDir.exists()) {
            return false;
        } else if (!dbDir.isDirectory()) {
            return false;
        }

        return true;

    }

    public boolean createDbDirectory() {
        if (!dbDirectoryExists()) {
            try {
                File dbDir = new File(getDbDirectory());
                Files.createDirectories(dbDir.toPath());
                LOGGER.log(Level.INFO, "sqlite directory did not exist, created it at {0}.", getDbDirectory()); // NON-NLS
            } catch (IOException | InvalidPathException | SecurityException ex) {
                LOGGER.log(Level.SEVERE, "Failed to create sqlite database directory.", ex); // NON-NLS
                return false;
            }
        }

        return true;
    }

    public boolean deleteDatabase() {
        File dbFile = new File(this.getFileNameWithPath());
        return dbFile.delete();
    }

    String getConnectionURL() {
        StringBuilder url = new StringBuilder();
        url.append(getJDBCBaseURI());
        url.append(getFileNameWithPath());

        return url.toString();
    }

    private Connection getEphemeralConnection() {
        if (!dbDirectoryExists()) {
            return null;
        }

        Connection conn;
        try {
            String url = getConnectionURL();
            Class.forName(getDriver());
            conn = DriverManager.getConnection(url);
        } catch (ClassNotFoundException | SQLException ex) {
            LOGGER.log(Level.SEVERE, "Failed to acquire ephemeral connection to sqlite.", ex); // NON-NLS
            conn = null;
        }
        return conn;
    }

    public boolean verifyConnection() {
        Connection conn = getEphemeralConnection();
        if (null == conn) {
            return false;
        }

        boolean result = EamDbUtil.executeValidationQuery(conn, VALIDATION_QUERY);
        EamDbUtil.closeConnection(conn);
        return result;
    }

    public boolean verifyDatabaseSchema() {
        Connection conn = getEphemeralConnection();
        if (null == conn) {
            return false;
        }

        boolean result = EamDbUtil.schemaVersionIsSet(conn);
        EamDbUtil.closeConnection(conn);
        return result;
    }

    public boolean initializeDatabaseSchema() {
        // The "id" column is an alias for the built-in 64-bit int "rowid" column.
        // It is autoincrementing by default and must be of type "integer primary key".
        // We've omitted the autoincrement argument because we are not currently
        // using the id value to search for specific rows, so we do not care
        // if a rowid is re-used after an existing rows was previously deleted.
        StringBuilder createOrganizationsTable = new StringBuilder();
        createOrganizationsTable.append("CREATE TABLE IF NOT EXISTS organizations (");
        createOrganizationsTable.append("id integer primary key autoincrement NOT NULL,");
        createOrganizationsTable.append("org_name text NOT NULL,");
        createOrganizationsTable.append("poc_name text NOT NULL,");
        createOrganizationsTable.append("poc_email text NOT NULL,");
        createOrganizationsTable.append("poc_phone text NOT NULL,");
        createOrganizationsTable.append("CONSTRAINT org_name_unique UNIQUE (org_name)");
        createOrganizationsTable.append(")");

        // NOTE: The organizations will only have a small number of rows, so
        // an index is probably not worthwhile.
        StringBuilder createCasesTable = new StringBuilder();
        createCasesTable.append("CREATE TABLE IF NOT EXISTS cases (");
        createCasesTable.append("id integer primary key autoincrement NOT NULL,");
        createCasesTable.append("case_uid text NOT NULL,");
        createCasesTable.append("org_id integer,");
        createCasesTable.append("case_name text NOT NULL,");
        createCasesTable.append("creation_date text NOT NULL,");
        createCasesTable.append("case_number text,");
        createCasesTable.append("examiner_name text,");
        createCasesTable.append("examiner_email text,");
        createCasesTable.append("examiner_phone text,");
        createCasesTable.append("notes text,");
        createCasesTable.append("CONSTRAINT case_uid_unique UNIQUE(case_uid) ON CONFLICT IGNORE,");
        createCasesTable.append("foreign key (org_id) references organizations(id) ON UPDATE SET NULL ON DELETE SET NULL");
        createCasesTable.append(")");

        // NOTE: when there are few cases in the cases table, these indices may not be worthwhile
        String casesIdx1 = "CREATE INDEX IF NOT EXISTS cases_org_id ON cases (org_id)";
        String casesIdx2 = "CREATE INDEX IF NOT EXISTS cases_case_uid ON cases (case_uid)";

        StringBuilder createReferenceSetsTable = new StringBuilder();
        createReferenceSetsTable.append("CREATE TABLE IF NOT EXISTS reference_sets (");
        createReferenceSetsTable.append("id integer primary key autoincrement NOT NULL,");
        createReferenceSetsTable.append("org_id integer NOT NULL,");
        createReferenceSetsTable.append("set_name text NOT NULL,");
        createReferenceSetsTable.append("version text NOT NULL,");
        createReferenceSetsTable.append("known_status integer NOT NULL,");
        createReferenceSetsTable.append("read_only boolean NOT NULL,");
        createReferenceSetsTable.append("type integer NOT NULL,");
        createReferenceSetsTable.append("import_date text NOT NULL,");
        createReferenceSetsTable.append("foreign key (org_id) references organizations(id) ON UPDATE SET NULL ON DELETE SET NULL,");
        createReferenceSetsTable.append("CONSTRAINT hash_set_unique UNIQUE (set_name, version)");
        createReferenceSetsTable.append(")");

        String referenceSetsIdx1 = "CREATE INDEX IF NOT EXISTS reference_sets_org_id ON reference_sets (org_id)";

        // Each "%s" will be replaced with the relevant reference_TYPE table name.
        StringBuilder createReferenceTypesTableTemplate = new StringBuilder();
        createReferenceTypesTableTemplate.append("CREATE TABLE IF NOT EXISTS %s (");
        createReferenceTypesTableTemplate.append("id integer primary key autoincrement NOT NULL,");
        createReferenceTypesTableTemplate.append("reference_set_id integer,");
        createReferenceTypesTableTemplate.append("value text NOT NULL,");
        createReferenceTypesTableTemplate.append("known_status integer NOT NULL,");
        createReferenceTypesTableTemplate.append("comment text,");
        createReferenceTypesTableTemplate.append("CONSTRAINT %s_multi_unique UNIQUE(reference_set_id, value) ON CONFLICT IGNORE,");
        createReferenceTypesTableTemplate.append("foreign key (reference_set_id) references reference_sets(id) ON UPDATE SET NULL ON DELETE SET NULL");
        createReferenceTypesTableTemplate.append(")");

        // Each "%s" will be replaced with the relevant reference_TYPE table name.
        String referenceTypesIdx1 = "CREATE INDEX IF NOT EXISTS %s_value ON %s (value)";
        String referenceTypesIdx2 = "CREATE INDEX IF NOT EXISTS %s_value_known_status ON %s (value, known_status)";

        StringBuilder createCorrelationTypesTable = new StringBuilder();
        createCorrelationTypesTable.append("CREATE TABLE IF NOT EXISTS correlation_types (");
        createCorrelationTypesTable.append("id integer primary key autoincrement NOT NULL,");
        createCorrelationTypesTable.append("display_name text NOT NULL,");
        createCorrelationTypesTable.append("db_table_name text NOT NULL,");
        createCorrelationTypesTable.append("supported integer NOT NULL,");
        createCorrelationTypesTable.append("enabled integer NOT NULL,");
        createCorrelationTypesTable.append("CONSTRAINT correlation_types_names UNIQUE (display_name, db_table_name)");
        createCorrelationTypesTable.append(")");

        String createArtifactInstancesTableTemplate = getCreateArtifactInstancesTableTemplate();

        String instancesCaseIdIdx = getAddCaseIdIndexTemplate();
        String instancesDatasourceIdIdx = getAddDataSourceIdIndexTemplate();
        String instancesValueIdx = getAddValueIndexTemplate();
        String instancesKnownStatusIdx = getAddKnownStatusIndexTemplate();
        String instancesObjectIdIdx = getAddObjectIdIndexTemplate();

        // NOTE: the db_info table currenly only has 1 row, so having an index
        // provides no benefit.
        Connection conn = null;
        try {
            conn = getEphemeralConnection();
            if (null == conn) {
                return false;
            }
            Statement stmt = conn.createStatement();
            stmt.execute(PRAGMA_JOURNAL_WAL);
            stmt.execute(PRAGMA_SYNC_OFF);
            stmt.execute(PRAGMA_READ_UNCOMMITTED_TRUE);
            stmt.execute(PRAGMA_ENCODING_UTF8);
            stmt.execute(PRAGMA_PAGE_SIZE_4096);
            stmt.execute(PRAGMA_FOREIGN_KEYS_ON);

            stmt.execute(createOrganizationsTable.toString());

            stmt.execute(createCasesTable.toString());
            stmt.execute(casesIdx1);
            stmt.execute(casesIdx2);

            stmt.execute(getCreateDataSourcesTableStatement());
            stmt.execute(getAddDataSourcesNameIndexStatement());
            stmt.execute(getAddDataSourcesObjectIdIndexStatement());

            stmt.execute(createReferenceSetsTable.toString());
            stmt.execute(referenceSetsIdx1);

            stmt.execute(createCorrelationTypesTable.toString());

            /*
             * Note that the essentially useless id column in the following
             * table is required for backwards compatibility. Otherwise, the
             * name column could be the primary key.
             */
            stmt.execute("CREATE TABLE db_info (id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL, value TEXT NOT NULL)");
            stmt.execute("INSERT INTO db_info (name, value) VALUES ('" + AbstractSqlEamDb.SCHEMA_MAJOR_VERSION_KEY + "', '" + SOFTWARE_CR_DB_SCHEMA_VERSION.getMajor() + "')");
            stmt.execute("INSERT INTO db_info (name, value) VALUES ('" + AbstractSqlEamDb.SCHEMA_MINOR_VERSION_KEY + "', '" + SOFTWARE_CR_DB_SCHEMA_VERSION.getMinor() + "')");
            stmt.execute("INSERT INTO db_info (name, value) VALUES ('" + AbstractSqlEamDb.CREATION_SCHEMA_MAJOR_VERSION_KEY + "', '" + SOFTWARE_CR_DB_SCHEMA_VERSION.getMajor() + "')");
            stmt.execute("INSERT INTO db_info (name, value) VALUES ('" + AbstractSqlEamDb.CREATION_SCHEMA_MINOR_VERSION_KEY + "', '" + SOFTWARE_CR_DB_SCHEMA_VERSION.getMinor() + "')");

            // Create a separate instance and reference table for each artifact type
            List<CorrelationAttributeInstance.Type> DEFAULT_CORRELATION_TYPES = CorrelationAttributeInstance.getDefaultCorrelationTypes();

            String reference_type_dbname;
            String instance_type_dbname;
            for (CorrelationAttributeInstance.Type type : DEFAULT_CORRELATION_TYPES) {
                reference_type_dbname = EamDbUtil.correlationTypeToReferenceTableName(type);
                instance_type_dbname = EamDbUtil.correlationTypeToInstanceTableName(type);

                stmt.execute(String.format(createArtifactInstancesTableTemplate, instance_type_dbname, instance_type_dbname));
                stmt.execute(String.format(instancesCaseIdIdx, instance_type_dbname, instance_type_dbname));
                stmt.execute(String.format(instancesDatasourceIdIdx, instance_type_dbname, instance_type_dbname));
                stmt.execute(String.format(instancesValueIdx, instance_type_dbname, instance_type_dbname));
                stmt.execute(String.format(instancesKnownStatusIdx, instance_type_dbname, instance_type_dbname));
                stmt.execute(String.format(instancesObjectIdIdx, instance_type_dbname, instance_type_dbname));

                // FUTURE: allow more than the FILES type
                if (type.getId() == CorrelationAttributeInstance.FILES_TYPE_ID) {
                    stmt.execute(String.format(createReferenceTypesTableTemplate.toString(), reference_type_dbname, reference_type_dbname));
                    stmt.execute(String.format(referenceTypesIdx1, reference_type_dbname, reference_type_dbname));
                    stmt.execute(String.format(referenceTypesIdx2, reference_type_dbname, reference_type_dbname));
                }
            }
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "Error initializing db schema.", ex); // NON-NLS
            return false;
        } catch (EamDbException ex) {
            LOGGER.log(Level.SEVERE, "Error getting default correlation types. Likely due to one or more Type's with an invalid db table name."); // NON-NLS
            return false;
        } finally {
            EamDbUtil.closeConnection(conn);
        }
        return true;
    }

    static String getCreateArtifactInstancesTableTemplate() {
        // Each "%s" will be replaced with the relevant TYPE_instances table name.
        return "CREATE TABLE IF NOT EXISTS %s (id integer primary key autoincrement NOT NULL,"
                + "case_id integer NOT NULL,data_source_id integer NOT NULL,value text NOT NULL,"
                + "file_path text NOT NULL,known_status integer NOT NULL,comment text,file_obj_id integer,"
                + "CONSTRAINT %s_multi_unique UNIQUE(data_source_id, value, file_path) ON CONFLICT IGNORE,"
                + "foreign key (case_id) references cases(id) ON UPDATE SET NULL ON DELETE SET NULL,"
                + "foreign key (data_source_id) references data_sources(id) ON UPDATE SET NULL ON DELETE SET NULL)";
    }

    static String getCreateDataSourcesTableStatement() {
        return "CREATE TABLE IF NOT EXISTS data_sources (id integer primary key autoincrement NOT NULL,"
                + "case_id integer NOT NULL,device_id text NOT NULL,name text NOT NULL,datasource_obj_id integer,"
                + "md5 text DEFAULT NULL,sha1 text DEFAULT NULL,sha256 text DEFAULT NULL,"
                + "foreign key (case_id) references cases(id) ON UPDATE SET NULL ON DELETE SET NULL,"
                + "CONSTRAINT datasource_unique UNIQUE (case_id, datasource_obj_id))";
    }

    static String getAddDataSourcesNameIndexStatement() {
        return "CREATE INDEX IF NOT EXISTS data_sources_name ON data_sources (name)";
    }

    static String getAddDataSourcesObjectIdIndexStatement() {
        return "CREATE INDEX IF NOT EXISTS data_sources_object_id ON data_sources (datasource_obj_id)";
    }

    static String getAddCaseIdIndexTemplate() {
        // Each "%s" will be replaced with the relevant TYPE_instances table name.
        return "CREATE INDEX IF NOT EXISTS %s_case_id ON %s (case_id)";
    }

    static String getAddDataSourceIdIndexTemplate() {
        // Each "%s" will be replaced with the relevant TYPE_instances table name.
        return "CREATE INDEX IF NOT EXISTS %s_data_source_id ON %s (data_source_id)";
    }

    static String getAddValueIndexTemplate() {
        // Each "%s" will be replaced with the relevant TYPE_instances table name.
        return "CREATE INDEX IF NOT EXISTS %s_value ON %s (value)";
    }

    static String getAddKnownStatusIndexTemplate() {
        // Each "%s" will be replaced with the relevant TYPE_instances table name.
        return "CREATE INDEX IF NOT EXISTS %s_value_known_status ON %s (value, known_status)";
    }

    static String getAddObjectIdIndexTemplate() {
        // Each "%s" will be replaced with the relevant TYPE_instances table name.
        return "CREATE INDEX IF NOT EXISTS %s_file_obj_id ON %s (file_obj_id)";
    }

    public boolean insertDefaultDatabaseContent() {
        Connection conn = getEphemeralConnection();
        if (null == conn) {
            return false;
        }

        boolean result = EamDbUtil.insertDefaultCorrelationTypes(conn) && EamDbUtil.insertDefaultOrganization(conn);
        EamDbUtil.closeConnection(conn);
        return result;
    }

    boolean isChanged() {
        String dbNameString = ModuleSettings.getConfigSetting("CentralRepository", "db.sqlite.dbName"); // NON-NLS
        String dbDirectoryString = ModuleSettings.getConfigSetting("CentralRepository", "db.sqlite.dbDirectory"); // NON-NLS
        String bulkThresholdString = ModuleSettings.getConfigSetting("CentralRepository", "db.sqlite.bulkThreshold"); // NON-NLS

        return !dbName.equals(dbNameString)
                || !dbDirectory.equals(dbDirectoryString)
                || !Integer.toString(bulkThreshold).equals(bulkThresholdString);
    }

    public String getDbName() {
        return dbName;
    }

    public void setDbName(String dbName) throws EamDbException {
        if (dbName == null || dbName.isEmpty()) {
            throw new EamDbException("Invalid database file name. Cannot be null or empty."); // NON-NLS
        } else if (!Pattern.matches(DB_NAMES_REGEX, dbName)) {
            throw new EamDbException("Invalid database file name. Name must start with a lowercase letter and can only contain lowercase letters, numbers, and '_'."); // NON-NLS
        }

        this.dbName = dbName;
    }

    int getBulkThreshold() {
        return bulkThreshold;
    }

    void setBulkThreshold(int bulkThreshold) throws EamDbException {
        if (bulkThreshold > 0) {
            this.bulkThreshold = bulkThreshold;
        } else {
            throw new EamDbException("Invalid bulk threshold."); // NON-NLS
        }
    }

    public String getDbDirectory() {
        return dbDirectory;
    }

    public void setDbDirectory(String dbDirectory) throws EamDbException {
        if (dbDirectory != null && !dbDirectory.isEmpty()) {
            this.dbDirectory = dbDirectory;
        } else {
            throw new EamDbException("Invalid directory for sqlite database. Cannot empty"); // NON-NLS
        }
    }

    public String getFileNameWithPath() {
        return getDbDirectory() + File.separator + getDbName();
    }

    String getDriver() {
        return JDBC_DRIVER;
    }

    String getValidationQuery() {
        return VALIDATION_QUERY;
    }

    String getJDBCBaseURI() {
        return JDBC_BASE_URI;
    }

}