RdbmsCentralRepoFactory.java

Go to the documentation of this file.

/*
 * Central Repository
 *
 * Copyright 2020 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.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.logging.Level;
import org.sleuthkit.autopsy.centralrepository.datamodel.Persona.Confidence;
import org.sleuthkit.autopsy.centralrepository.datamodel.Persona.PersonaStatus;
import static org.sleuthkit.autopsy.centralrepository.datamodel.RdbmsCentralRepo.SOFTWARE_CR_DB_SCHEMA_VERSION;
import org.sleuthkit.autopsy.coreutils.Logger;
import org.sleuthkit.datamodel.Account;

public class RdbmsCentralRepoFactory {

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

   
    private final CentralRepoPlatforms selectedPlatform;
    private final SqliteCentralRepoSettings sqliteCentralRepoSettings;
    private final PostgresCentralRepoSettings postgresCentralRepoSettings;
    

    // SQLite pragmas
    private final static String PRAGMA_SYNC_OFF = "PRAGMA synchronous = OFF";
    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";


    
    public RdbmsCentralRepoFactory(CentralRepoPlatforms selectedPlatform, SqliteCentralRepoSettings repoSettings) throws CentralRepoException {
        this.selectedPlatform = selectedPlatform;
        this.sqliteCentralRepoSettings = repoSettings;
        this.postgresCentralRepoSettings =  null;
        
    }

     public RdbmsCentralRepoFactory(CentralRepoPlatforms selectedPlatform, PostgresCentralRepoSettings repoSettings) throws CentralRepoException {
        this.selectedPlatform = selectedPlatform;
        this.postgresCentralRepoSettings = repoSettings;
        this.sqliteCentralRepoSettings =  null;
    }
     

    public boolean initializeDatabaseSchema() {

        String createArtifactInstancesTableTemplate = getCreateArtifactInstancesTableTemplate(selectedPlatform);
        String createAccountInstancesTableTemplate = getCreateAccountInstancesTableTemplate(selectedPlatform);

        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.
        try (Connection conn = this.getEphemeralConnection();) {

            if (null == conn) {
                LOGGER.log(Level.SEVERE, "Cannot initialize CR database, don't have a valid connection."); // NON-NLS
                return false;
            }

            try (Statement stmt = conn.createStatement();) {

                // these setting PRAGMAs are SQLIte spcific
                if (selectedPlatform == CentralRepoPlatforms.SQLITE) {
                    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);
                }

                // Create Organizations table
                stmt.execute(getCreateOrganizationsTableStatement(selectedPlatform));

                // Create Cases table and indexes
                stmt.execute(getCreateCasesTableStatement(selectedPlatform));
                stmt.execute(getCasesOrgIdIndexStatement());
                stmt.execute(getCasesCaseUidIndexStatement());

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

                stmt.execute(getCreateReferenceSetsTableStatement(selectedPlatform));
                stmt.execute(getReferenceSetsOrgIdIndexTemplate());

                stmt.execute(getCreateCorrelationTypesTableStatement(selectedPlatform));

                stmt.execute(getCreateDbInfoTableStatement(selectedPlatform));
                stmt.execute("INSERT INTO db_info (name, value) VALUES ('" + RdbmsCentralRepo.SCHEMA_MAJOR_VERSION_KEY + "', '" + SOFTWARE_CR_DB_SCHEMA_VERSION.getMajor() + "')");
                stmt.execute("INSERT INTO db_info (name, value) VALUES ('" + RdbmsCentralRepo.SCHEMA_MINOR_VERSION_KEY + "', '" + SOFTWARE_CR_DB_SCHEMA_VERSION.getMinor() + "')");
                stmt.execute("INSERT INTO db_info (name, value) VALUES ('" + RdbmsCentralRepo.CREATION_SCHEMA_MAJOR_VERSION_KEY + "', '" + SOFTWARE_CR_DB_SCHEMA_VERSION.getMajor() + "')");
                stmt.execute("INSERT INTO db_info (name, value) VALUES ('" + RdbmsCentralRepo.CREATION_SCHEMA_MINOR_VERSION_KEY + "', '" + SOFTWARE_CR_DB_SCHEMA_VERSION.getMinor() + "')");

                // Create account_types and accounts tables which are referred by X_instances tables
                stmt.execute(getCreateAccountTypesTableStatement(selectedPlatform));
                stmt.execute(getCreateAccountsTableStatement(selectedPlatform));

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

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

                    // use the correct create table template, based on whether the attribute type represents an account or not.
                    String createTableTemplate = (CentralRepoDbUtil.correlationAttribHasAnAccount(type)) 
                                        ? createAccountInstancesTableTemplate 
                                        : createArtifactInstancesTableTemplate;
                    
                    stmt.execute(String.format(createTableTemplate, 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(getReferenceTypesTableTemplate(selectedPlatform), reference_type_dbname, reference_type_dbname));
                        stmt.execute(String.format(getReferenceTypeValueIndexTemplate(), reference_type_dbname, reference_type_dbname));
                        stmt.execute(String.format(getReferenceTypeValueKnownstatusIndexTemplate(), reference_type_dbname, reference_type_dbname));
                    }
                }
                // create Persona tables.
                createPersonaTables(stmt, selectedPlatform);
            } catch (SQLException ex) {
                LOGGER.log(Level.SEVERE, "Error initializing db schema.", ex); // NON-NLS
                return false;
            } catch (CentralRepoException 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;
            }
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "Error connecting to database.", ex); // NON-NLS
            return false;
        }

        return true;
    }

    public boolean insertDefaultDatabaseContent() {

        boolean result;
        try (Connection conn = this.getEphemeralConnection();) {
            if (null == conn) {
                return false;
            }

            result = CentralRepoDbUtil.insertDefaultCorrelationTypes(conn)
                    && CentralRepoDbUtil.insertDefaultOrganization(conn) 
                    && RdbmsCentralRepoFactory.insertDefaultAccountsTablesContent(conn, selectedPlatform )
                    && insertDefaultPersonaTablesContent(conn, selectedPlatform);

        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, String.format("Failed to populate default data in CR tables."), ex);
            return false;
        }

        return result;
    }

    private static String getCreateDbInfoTableStatement(CentralRepoPlatforms selectedPlatform) {
         /*
             * 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.
             */
           
        return "CREATE TABLE db_info ("
                + getNumericPrimaryKeyClause("id", selectedPlatform)
                + "name TEXT UNIQUE NOT NULL,"
                + "value TEXT NOT NULL "
                + ")";
                    
    }
    private static String getCreateOrganizationsTableStatement(CentralRepoPlatforms selectedPlatform) {
        // 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.
        
        return "CREATE TABLE IF NOT EXISTS organizations ("
                + getNumericPrimaryKeyClause("id", selectedPlatform)
                + "org_name text NOT NULL,"
                + "poc_name text NOT NULL,"
                + "poc_email text NOT NULL,"
                + "poc_phone text NOT NULL,"
                + "CONSTRAINT org_name_unique UNIQUE (org_name)"
                + ")";
    }
    
    private static String getCreateCasesTableStatement(CentralRepoPlatforms selectedPlatform) {
        
        return ("CREATE TABLE IF NOT EXISTS cases (")
                + getNumericPrimaryKeyClause("id", selectedPlatform)
                + "case_uid text NOT NULL,"
                + "org_id integer,"
                + "case_name text NOT NULL,"
                + "creation_date text NOT NULL,"
                + "case_number text,"
                + "examiner_name text,"
                + "examiner_email text,"
                + "examiner_phone text,"
                + "notes text,"
                + "foreign key (org_id) references organizations(id) ON UPDATE SET NULL ON DELETE SET NULL,"
                + "CONSTRAINT case_uid_unique UNIQUE(case_uid)" + getOnConflictIgnoreClause(selectedPlatform)
                + ")";
    }
    
    private static String getCasesOrgIdIndexStatement() {
        return "CREATE INDEX IF NOT EXISTS cases_org_id ON cases (org_id)";
    }
    
    private static String getCasesCaseUidIndexStatement() {
        return "CREATE INDEX IF NOT EXISTS cases_case_uid ON cases (case_uid)";
    }
    
    private static String getCreateReferenceSetsTableStatement(CentralRepoPlatforms selectedPlatform) {
       
        return "CREATE TABLE IF NOT EXISTS reference_sets ("
            + getNumericPrimaryKeyClause("id", selectedPlatform)
            + "org_id integer NOT NULL,"
            + "set_name text NOT NULL,"
            + "version text NOT NULL,"
            + "known_status integer NOT NULL,"
            + "read_only boolean NOT NULL,"
            + "type integer NOT NULL,"
            + "import_date text NOT NULL,"
            + "foreign key (org_id) references organizations(id) ON UPDATE SET NULL ON DELETE SET NULL,"
            + "CONSTRAINT hash_set_unique UNIQUE (set_name, version)"
            + ")";
        
    }
    
    private static String getReferenceSetsOrgIdIndexTemplate() {
         return "CREATE INDEX IF NOT EXISTS reference_sets_org_id ON reference_sets (org_id)";
    }
    
    private static String getReferenceTypesTableTemplate(CentralRepoPlatforms selectedPlatform) {
        // Each "%s" will be replaced with the relevant reference_TYPE table name.

        return "CREATE TABLE IF NOT EXISTS %s ("
                + getNumericPrimaryKeyClause("id", selectedPlatform)
                + "reference_set_id integer,"
                + "value text NOT NULL,"
                + "known_status integer NOT NULL,"
                + "comment text,"
                + "CONSTRAINT %s_multi_unique UNIQUE(reference_set_id, value)" + getOnConflictIgnoreClause(selectedPlatform) + ","
                + "foreign key (reference_set_id) references reference_sets(id) ON UPDATE SET NULL ON DELETE SET NULL"
                + ")";
    }
        
    private static String getReferenceTypeValueIndexTemplate() {
        return "CREATE INDEX IF NOT EXISTS %s_value ON %s (value)";
    }
    
    private static String getReferenceTypeValueKnownstatusIndexTemplate() {
        return "CREATE INDEX IF NOT EXISTS %s_value_known_status ON %s (value, known_status)";
    }
    
    private static String getCreateCorrelationTypesTableStatement(CentralRepoPlatforms selectedPlatform) {

        return "CREATE TABLE IF NOT EXISTS correlation_types ("
                + getNumericPrimaryKeyClause("id", selectedPlatform)
                + "display_name text NOT NULL,"
                + "db_table_name text NOT NULL,"
                + "supported integer NOT NULL,"
                + "enabled integer NOT NULL,"
                + "CONSTRAINT correlation_types_names UNIQUE (display_name, db_table_name)"
                + ")";
    }
    static String getCreateArtifactInstancesTableTemplate(CentralRepoPlatforms selectedPlatform) {
        // Each "%s" will be replaced with the relevant TYPE_instances table name.
        
        return "CREATE TABLE IF NOT EXISTS %s ("
                + getNumericPrimaryKeyClause("id", selectedPlatform)
                + "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 " + getBigIntType(selectedPlatform) + " ," 
                + "CONSTRAINT %s_multi_unique UNIQUE(data_source_id, value, file_path)" + getOnConflictIgnoreClause(selectedPlatform) + ","
                + "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 getCreateAccountInstancesTableTemplate(CentralRepoPlatforms selectedPlatform) {
        // Each "%s" will be replaced with the relevant TYPE_instances table name.
        
        return "CREATE TABLE IF NOT EXISTS %s ("
                + getNumericPrimaryKeyClause("id", selectedPlatform)
                + "case_id integer NOT NULL,"
                + "data_source_id integer NOT NULL,"
                + "account_id " + getBigIntType(selectedPlatform) + " DEFAULT NULL,"
                + "value text NOT NULL,"
                + "file_path text NOT NULL,"
                + "known_status integer NOT NULL,"
                + "comment text,"
                + "file_obj_id " + getBigIntType(selectedPlatform) + " ," 
                + "CONSTRAINT %s_multi_unique UNIQUE(data_source_id, value, file_path)" + getOnConflictIgnoreClause(selectedPlatform) + ","
                + "foreign key (account_id) references accounts(id),"
                + "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(CentralRepoPlatforms selectedPlatform) {
        return "CREATE TABLE IF NOT EXISTS data_sources ("
                + getNumericPrimaryKeyClause("id", selectedPlatform)
                + "case_id integer NOT NULL,"
                + "device_id text NOT NULL,"
                + "name text NOT NULL,"
                + "datasource_obj_id " + getBigIntType(selectedPlatform) + " ," 
                + "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 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)";
    }

    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)";
    }

    private static String getNumericPrimaryKeyClause(String pkName, CentralRepoPlatforms selectedPlatform) {
        switch (selectedPlatform) {
            case POSTGRESQL:
                return String.format(" %s SERIAL PRIMARY KEY, ", pkName);
            case SQLITE:
                return String.format(" %s integer primary key autoincrement NOT NULL ,", pkName);
            default:
                return "";
        }
        
    }

    private static String getOnConflictIgnoreClause(CentralRepoPlatforms selectedPlatform) {
        switch (selectedPlatform) {
            case POSTGRESQL:
                return "";
            case SQLITE:
                return " ON CONFLICT IGNORE ";
            default:
                return "";
        }
    }
    
    static String getBigIntType(CentralRepoPlatforms selectedPlatform) {
        switch (selectedPlatform) {
            case POSTGRESQL:
                return " BIGINT ";
            case SQLITE:
                return " INTEGER ";
            default:
                return "";
        }
    }
    
     private static String getOnConflictDoNothingClause(CentralRepoPlatforms selectedPlatform) {
        switch (selectedPlatform) {
            case POSTGRESQL:
                return "ON CONFLICT DO NOTHING";
            case SQLITE:
                return "";
            default:
                return "";
        }
    }
    private Connection getEphemeralConnection() {
        switch (selectedPlatform) {
            case POSTGRESQL:
                return this.postgresCentralRepoSettings.getEphemeralConnection(false);
            case SQLITE:
                return this.sqliteCentralRepoSettings.getEphemeralConnection();
            default:
                return null;
        }
    }
    
    static boolean createPersonaTables(Statement stmt, CentralRepoPlatforms selectedPlatform) throws SQLException {
        
        stmt.execute(getCreateConfidenceTableStatement(selectedPlatform));
        stmt.execute(getCreateExaminersTableStatement(selectedPlatform));
        stmt.execute(getCreatePersonaStatusTableStatement(selectedPlatform));
        
        stmt.execute(getCreatePersonasTableStatement(selectedPlatform));
        stmt.execute(getCreatePersonaAliasTableStatement(selectedPlatform));
        stmt.execute(getCreatePersonaMetadataTableStatement(selectedPlatform));
        stmt.execute(getCreatePersonaAccountsTableStatement(selectedPlatform));
        
        return true;
    }
    
    
    static String getCreateAccountTypesTableStatement(CentralRepoPlatforms selectedPlatform) {
       
        return "CREATE TABLE IF NOT EXISTS account_types ("
                + getNumericPrimaryKeyClause("id", selectedPlatform)
                + "type_name TEXT NOT NULL,"
                + "display_name TEXT NOT NULL,"
                + "correlation_type_id " + getBigIntType(selectedPlatform) + " ,"
                + "CONSTRAINT type_name_unique UNIQUE (type_name),"
                + "FOREIGN KEY (correlation_type_id) REFERENCES correlation_types(id)"
                + ")";
    }
    
    static String getCreateConfidenceTableStatement(CentralRepoPlatforms selectedPlatform) {

        return "CREATE TABLE IF NOT EXISTS confidence ("
                + getNumericPrimaryKeyClause("id", selectedPlatform)
                + "confidence_id integer NOT NULL,"
                + "description TEXT,"
                + "CONSTRAINT level_unique UNIQUE (confidence_id)"
                + ")";
    }

    static String getCreateExaminersTableStatement(CentralRepoPlatforms selectedPlatform) {

        return "CREATE TABLE IF NOT EXISTS examiners ("
                + getNumericPrimaryKeyClause("id", selectedPlatform)
                + "login_name TEXT NOT NULL,"
                + "display_name TEXT,"
                + "CONSTRAINT login_name_unique UNIQUE(login_name)"
                + ")";
    }
    
    static String getCreatePersonaStatusTableStatement(CentralRepoPlatforms selectedPlatform) {

        return "CREATE TABLE IF NOT EXISTS persona_status ("
                + getNumericPrimaryKeyClause("id", selectedPlatform)
                + "status_id integer NOT NULL,"
                + "status TEXT NOT NULL,"
                + "CONSTRAINT status_unique UNIQUE(status_id)"
                + ")";
    }
    
    
    static String getCreateAccountsTableStatement(CentralRepoPlatforms selectedPlatform) {

        return "CREATE TABLE IF NOT EXISTS accounts ("
                + getNumericPrimaryKeyClause("id", selectedPlatform)
                + "account_type_id integer NOT NULL,"
                + "account_unique_identifier TEXT NOT NULL,"
                + "CONSTRAINT account_unique UNIQUE(account_type_id, account_unique_identifier),"
                + "FOREIGN KEY (account_type_id) REFERENCES account_types(id)"
                + ")";
    }
    
    static String getCreatePersonasTableStatement(CentralRepoPlatforms selectedPlatform) {

        return "CREATE TABLE IF NOT EXISTS personas ("
                + getNumericPrimaryKeyClause("id", selectedPlatform)
                + "uuid TEXT NOT NULL,"
                + "comment TEXT NOT NULL,"
                + "name TEXT NOT NULL,"
                + "created_date " + getBigIntType(selectedPlatform) + " ,"
                + "modified_date " + getBigIntType(selectedPlatform) + " ,"
                + "status_id integer NOT NULL,"
                 + "examiner_id integer NOT NULL,"
                + "CONSTRAINT uuid_unique UNIQUE(uuid),"
                + "FOREIGN KEY (status_id) REFERENCES persona_status(status_id), "
                + "FOREIGN KEY (examiner_id) REFERENCES examiners(id)"
                + ")";
    }
    
    static String getCreatePersonaAliasTableStatement(CentralRepoPlatforms selectedPlatform) {

        return "CREATE TABLE IF NOT EXISTS persona_alias ("
                + getNumericPrimaryKeyClause("id", selectedPlatform)
                + "persona_id " + getBigIntType(selectedPlatform) + " ,"
                + "alias TEXT NOT NULL, "
                + "justification TEXT NOT NULL,"
                + "confidence_id integer NOT NULL,"
                + "date_added " + getBigIntType(selectedPlatform) + " ,"
                + "examiner_id integer NOT NULL,"
                + "FOREIGN KEY (persona_id) REFERENCES personas(id),"
                + "FOREIGN KEY (confidence_id) REFERENCES confidence(confidence_id),"
                + "FOREIGN KEY (examiner_id) REFERENCES examiners(id)"
                + ")";
    }
    
    static String getCreatePersonaMetadataTableStatement(CentralRepoPlatforms selectedPlatform) {

        return "CREATE TABLE IF NOT EXISTS persona_metadata ("
                + getNumericPrimaryKeyClause("id", selectedPlatform)
                + "persona_id " + getBigIntType(selectedPlatform) + " ,"
                + "name TEXT NOT NULL,"
                + "value TEXT NOT NULL,"
                + "justification TEXT NOT NULL,"
                + "confidence_id integer NOT NULL,"
                + "date_added " + getBigIntType(selectedPlatform) + " ,"
                + "examiner_id integer NOT NULL,"
                + "CONSTRAINT unique_metadata UNIQUE(persona_id, name),"
                + "FOREIGN KEY (persona_id) REFERENCES personas(id),"
                + "FOREIGN KEY (confidence_id) REFERENCES confidence(confidence_id),"
                + "FOREIGN KEY (examiner_id) REFERENCES examiners(id)"
                + ")";
    }
     
    static String getCreatePersonaAccountsTableStatement(CentralRepoPlatforms selectedPlatform) {

        return "CREATE TABLE IF NOT EXISTS persona_accounts ("
                + getNumericPrimaryKeyClause("id", selectedPlatform)
                + "persona_id " + getBigIntType(selectedPlatform) + " ,"
                + "account_id " + getBigIntType(selectedPlatform) + " ,"
                + "justification TEXT NOT NULL,"
                + "confidence_id integer NOT NULL,"
                + "date_added " + getBigIntType(selectedPlatform) + " ,"
                + "examiner_id integer NOT NULL,"
                + "FOREIGN KEY (persona_id) REFERENCES personas(id),"
                + "FOREIGN KEY (account_id) REFERENCES accounts(id),"
                + "FOREIGN KEY (confidence_id) REFERENCES confidence(confidence_id),"
                + "FOREIGN KEY (examiner_id) REFERENCES examiners(id)"
                + ")";
    }

    
    static boolean insertDefaultPersonaTablesContent(Connection conn, CentralRepoPlatforms selectedPlatform) {

        try (Statement stmt = conn.createStatement()) {
            // populate the confidence table
            for (Confidence confidence : Persona.Confidence.values()) {
                String sqlString = "INSERT INTO confidence (confidence_id, description) VALUES ( " + confidence.getLevelId() + ", '" + confidence.toString() + "')" //NON-NLS
                        + getOnConflictDoNothingClause(selectedPlatform);
                stmt.execute(sqlString);
            }
            
            // populate the persona_status table
            for (PersonaStatus status : Persona.PersonaStatus.values()) {
                String sqlString = "INSERT INTO persona_status (status_id, status) VALUES ( " + status.getStatusId() + ", '" + status.toString() + "')" //NON-NLS
                        + getOnConflictDoNothingClause(selectedPlatform);
                stmt.execute(sqlString);
            }
            
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, String.format("Failed to populate default data in Persona tables."), ex);
            return false;
        } 
        
        return true;
    }
    
    static boolean insertDefaultAccountsTablesContent(Connection conn, CentralRepoPlatforms selectedPlatform) {

        try (Statement stmt = conn.createStatement();) {

            // Populate the account_types table
            for (Account.Type type : Account.Type.PREDEFINED_ACCOUNT_TYPES) {
                if (type != Account.Type.DEVICE) {
                    int correlationTypeId = getCorrelationTypeIdForAccountType(conn, type);
                    if (correlationTypeId > 0) {
                        String sqlString = String.format("INSERT INTO account_types (type_name, display_name, correlation_type_id) VALUES ('%s', '%s', %d)" + getOnConflictDoNothingClause(selectedPlatform),
                                type.getTypeName(), type.getDisplayName(), correlationTypeId);
                        stmt.execute(sqlString);
                    }
                }
            }

        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, String.format("Failed to populate default data in account_types table."), ex);
            return false;
        }

        return true;
    }
    
    static int getCorrelationTypeIdForAccountType(Connection conn, Account.Type accountType) {

        int typeId = -1;
        if (accountType == Account.Type.EMAIL) {
            typeId = CorrelationAttributeInstance.EMAIL_TYPE_ID;
        } else if (accountType == Account.Type.PHONE) {
            typeId = CorrelationAttributeInstance.PHONE_TYPE_ID;
        } else {
            String querySql = "SELECT * FROM correlation_types WHERE display_name=?";
            try ( PreparedStatement preparedStatementQuery = conn.prepareStatement(querySql)) {
                preparedStatementQuery.setString(1, accountType.getDisplayName());
                try (ResultSet resultSet = preparedStatementQuery.executeQuery();) {
                    if (resultSet.next()) {
                        typeId = resultSet.getInt("id");
                    }
                }
            } catch (SQLException ex) {
                LOGGER.log(Level.SEVERE, String.format("Failed to get correlation typeId for account type %s.", accountType.getTypeName()), ex);
            } 
        }

        return typeId;
    }
}