19 package org.sleuthkit.autopsy.centralrepository.datamodel;
21 import java.sql.Connection;
22 import java.sql.DriverManager;
23 import java.sql.PreparedStatement;
24 import java.sql.ResultSet;
25 import java.sql.SQLException;
26 import java.sql.Statement;
27 import java.util.List;
28 import java.util.Properties;
29 import java.util.logging.Level;
30 import java.util.regex.Pattern;
67 if (host == null || host.isEmpty()) {
73 if (portString == null || portString.isEmpty()) {
76 port = Integer.parseInt(portString);
77 if (port < 0 || port > 65535) {
81 }
catch (NumberFormatException ex) {
86 if (dbName == null || dbName.isEmpty()) {
92 if (bulkThresholdString == null || bulkThresholdString.isEmpty()) {
93 this.bulkThreshold = AbstractSqlEamDb.DEFAULT_BULK_THRESHHOLD;
95 this.bulkThreshold = Integer.parseInt(bulkThresholdString);
96 if (getBulkThreshold() <= 0) {
97 this.bulkThreshold = AbstractSqlEamDb.DEFAULT_BULK_THRESHHOLD;
100 }
catch (NumberFormatException ex) {
101 this.bulkThreshold = AbstractSqlEamDb.DEFAULT_BULK_THRESHHOLD;
105 if (userName == null || userName.isEmpty()) {
110 if (password == null || password.isEmpty()) {
116 LOGGER.log(Level.WARNING,
"Failed to convert password from hex text to text.", ex);
131 LOGGER.log(Level.SEVERE,
"Failed to convert password from text to hex text.", ex);
143 String getConnectionURL(
boolean usePostgresDb) {
144 StringBuilder url =
new StringBuilder();
145 url.append(getJDBCBaseURI());
149 url.append(
"postgres");
154 return url.toString();
166 String url = getConnectionURL(usePostgresDb);
167 Properties props =
new Properties();
171 Class.forName(getDriver());
172 conn = DriverManager.getConnection(url, props);
173 }
catch (ClassNotFoundException | SQLException ex) {
176 LOGGER.log(Level.SEVERE,
"Failed to acquire ephemeral connection to postgresql.");
210 String sql =
"SELECT datname FROM pg_catalog.pg_database WHERE lower(datname) = lower(?) LIMIT 1";
211 PreparedStatement ps = null;
214 ps = conn.prepareStatement(sql);
216 rs = ps.executeQuery();
220 }
catch (SQLException ex) {
221 LOGGER.log(Level.SEVERE,
"Failed to execute database existance query.", ex);
255 String sql =
"CREATE DATABASE %s OWNER %s";
258 stmt = conn.createStatement();
260 }
catch (SQLException ex) {
261 LOGGER.log(Level.SEVERE,
"Failed to execute create database statement.", ex);
276 String sql =
"DROP DATABASE %s";
279 stmt = conn.createStatement();
280 stmt.execute(String.format(sql,
getDbName()));
281 }
catch (SQLException ex) {
282 LOGGER.log(Level.SEVERE,
"Failed to execute drop database statement.", ex);
308 StringBuilder createOrganizationsTable =
new StringBuilder();
309 createOrganizationsTable.append(
"CREATE TABLE IF NOT EXISTS organizations (");
310 createOrganizationsTable.append(
"id SERIAL PRIMARY KEY,");
311 createOrganizationsTable.append(
"org_name text NOT NULL,");
312 createOrganizationsTable.append(
"poc_name text NOT NULL,");
313 createOrganizationsTable.append(
"poc_email text NOT NULL,");
314 createOrganizationsTable.append(
"poc_phone text NOT NULL,");
315 createOrganizationsTable.append(
"CONSTRAINT org_name_unique UNIQUE (org_name)");
316 createOrganizationsTable.append(
")");
320 StringBuilder createCasesTable =
new StringBuilder();
321 createCasesTable.append(
"CREATE TABLE IF NOT EXISTS cases (");
322 createCasesTable.append(
"id SERIAL PRIMARY KEY,");
323 createCasesTable.append(
"case_uid text NOT NULL,");
324 createCasesTable.append(
"org_id integer,");
325 createCasesTable.append(
"case_name text NOT NULL,");
326 createCasesTable.append(
"creation_date text NOT NULL,");
327 createCasesTable.append(
"case_number text,");
328 createCasesTable.append(
"examiner_name text,");
329 createCasesTable.append(
"examiner_email text,");
330 createCasesTable.append(
"examiner_phone text,");
331 createCasesTable.append(
"notes text,");
332 createCasesTable.append(
"foreign key (org_id) references organizations(id) ON UPDATE SET NULL ON DELETE SET NULL,");
333 createCasesTable.append(
"CONSTRAINT case_uid_unique UNIQUE (case_uid)");
334 createCasesTable.append(
")");
337 String casesIdx1 =
"CREATE INDEX IF NOT EXISTS cases_org_id ON cases (org_id)";
338 String casesIdx2 =
"CREATE INDEX IF NOT EXISTS cases_case_uid ON cases (case_uid)";
340 StringBuilder createDataSourcesTable =
new StringBuilder();
341 createDataSourcesTable.append(
"CREATE TABLE IF NOT EXISTS data_sources (");
342 createDataSourcesTable.append(
"id SERIAL PRIMARY KEY,");
343 createDataSourcesTable.append(
"case_id integer NOT NULL,");
344 createDataSourcesTable.append(
"device_id text NOT NULL,");
345 createDataSourcesTable.append(
"name text NOT NULL,");
346 createDataSourcesTable.append(
"foreign key (case_id) references cases(id) ON UPDATE SET NULL ON DELETE SET NULL,");
347 createDataSourcesTable.append(
"CONSTRAINT datasource_unique UNIQUE (case_id, device_id, name)");
348 createDataSourcesTable.append(
")");
350 String dataSourceIdx1 =
"CREATE INDEX IF NOT EXISTS data_sources_name ON data_sources (name)";
352 StringBuilder createReferenceSetsTable =
new StringBuilder();
353 createReferenceSetsTable.append(
"CREATE TABLE IF NOT EXISTS reference_sets (");
354 createReferenceSetsTable.append(
"id SERIAL PRIMARY KEY,");
355 createReferenceSetsTable.append(
"org_id integer NOT NULL,");
356 createReferenceSetsTable.append(
"set_name text NOT NULL,");
357 createReferenceSetsTable.append(
"version text NOT NULL,");
358 createReferenceSetsTable.append(
"known_status integer NOT NULL,");
359 createReferenceSetsTable.append(
"read_only boolean NOT NULL,");
360 createReferenceSetsTable.append(
"type integer NOT NULL,");
361 createReferenceSetsTable.append(
"import_date text NOT NULL,");
362 createReferenceSetsTable.append(
"foreign key (org_id) references organizations(id) ON UPDATE SET NULL ON DELETE SET NULL,");
363 createReferenceSetsTable.append(
"CONSTRAINT hash_set_unique UNIQUE (set_name, version)");
364 createReferenceSetsTable.append(
")");
366 String referenceSetsIdx1 =
"CREATE INDEX IF NOT EXISTS reference_sets_org_id ON reference_sets (org_id)";
369 StringBuilder createReferenceTypesTableTemplate =
new StringBuilder();
370 createReferenceTypesTableTemplate.append(
"CREATE TABLE IF NOT EXISTS %s (");
371 createReferenceTypesTableTemplate.append(
"id SERIAL PRIMARY KEY,");
372 createReferenceTypesTableTemplate.append(
"reference_set_id integer,");
373 createReferenceTypesTableTemplate.append(
"value text NOT NULL,");
374 createReferenceTypesTableTemplate.append(
"known_status integer NOT NULL,");
375 createReferenceTypesTableTemplate.append(
"comment text,");
376 createReferenceTypesTableTemplate.append(
"CONSTRAINT %s_multi_unique UNIQUE (reference_set_id, value),");
377 createReferenceTypesTableTemplate.append(
"foreign key (reference_set_id) references reference_sets(id) ON UPDATE SET NULL ON DELETE SET NULL");
378 createReferenceTypesTableTemplate.append(
")");
381 String referenceTypesIdx1 =
"CREATE INDEX IF NOT EXISTS %s_value ON %s (value)";
382 String referenceTypesIdx2 =
"CREATE INDEX IF NOT EXISTS %s_value_known_status ON %s (value, known_status)";
384 StringBuilder createCorrelationTypesTable =
new StringBuilder();
385 createCorrelationTypesTable.append(
"CREATE TABLE IF NOT EXISTS correlation_types (");
386 createCorrelationTypesTable.append(
"id SERIAL PRIMARY KEY,");
387 createCorrelationTypesTable.append(
"display_name text NOT NULL,");
388 createCorrelationTypesTable.append(
"db_table_name text NOT NULL,");
389 createCorrelationTypesTable.append(
"supported integer NOT NULL,");
390 createCorrelationTypesTable.append(
"enabled integer NOT NULL,");
391 createCorrelationTypesTable.append(
"CONSTRAINT correlation_types_names UNIQUE (display_name, db_table_name)");
392 createCorrelationTypesTable.append(
")");
395 StringBuilder createArtifactInstancesTableTemplate =
new StringBuilder();
396 createArtifactInstancesTableTemplate.append(
"CREATE TABLE IF NOT EXISTS %s (");
397 createArtifactInstancesTableTemplate.append(
"id SERIAL PRIMARY KEY,");
398 createArtifactInstancesTableTemplate.append(
"case_id integer NOT NULL,");
399 createArtifactInstancesTableTemplate.append(
"data_source_id integer NOT NULL,");
400 createArtifactInstancesTableTemplate.append(
"value text NOT NULL,");
401 createArtifactInstancesTableTemplate.append(
"file_path text NOT NULL,");
402 createArtifactInstancesTableTemplate.append(
"known_status integer NOT NULL,");
403 createArtifactInstancesTableTemplate.append(
"comment text,");
404 createArtifactInstancesTableTemplate.append(
"CONSTRAINT %s_multi_unique_ UNIQUE (data_source_id, value, file_path),");
405 createArtifactInstancesTableTemplate.append(
"foreign key (case_id) references cases(id) ON UPDATE SET NULL ON DELETE SET NULL,");
406 createArtifactInstancesTableTemplate.append(
"foreign key (data_source_id) references data_sources(id) ON UPDATE SET NULL ON DELETE SET NULL");
407 createArtifactInstancesTableTemplate.append(
")");
410 String instancesIdx1 =
"CREATE INDEX IF NOT EXISTS %s_case_id ON %s (case_id)";
411 String instancesIdx2 =
"CREATE INDEX IF NOT EXISTS %s_data_source_id ON %s (data_source_id)";
412 String instancesIdx3 =
"CREATE INDEX IF NOT EXISTS %s_value ON %s (value)";
413 String instancesIdx4 =
"CREATE INDEX IF NOT EXISTS %s_value_known_status ON %s (value, known_status)";
415 StringBuilder createDbInfoTable =
new StringBuilder();
416 createDbInfoTable.append(
"CREATE TABLE IF NOT EXISTS db_info (");
417 createDbInfoTable.append(
"id SERIAL PRIMARY KEY NOT NULL,");
418 createDbInfoTable.append(
"name text NOT NULL,");
419 createDbInfoTable.append(
"value text NOT NULL");
420 createDbInfoTable.append(
")");
424 Connection conn = null;
430 Statement stmt = conn.createStatement();
432 stmt.execute(createOrganizationsTable.toString());
434 stmt.execute(createCasesTable.toString());
435 stmt.execute(casesIdx1);
436 stmt.execute(casesIdx2);
438 stmt.execute(createDataSourcesTable.toString());
439 stmt.execute(dataSourceIdx1);
441 stmt.execute(createReferenceSetsTable.toString());
442 stmt.execute(referenceSetsIdx1);
444 stmt.execute(createCorrelationTypesTable.toString());
446 stmt.execute(createDbInfoTable.toString());
451 String reference_type_dbname;
452 String instance_type_dbname;
457 stmt.execute(String.format(createArtifactInstancesTableTemplate.toString(), instance_type_dbname, instance_type_dbname));
458 stmt.execute(String.format(instancesIdx1, instance_type_dbname, instance_type_dbname));
459 stmt.execute(String.format(instancesIdx2, instance_type_dbname, instance_type_dbname));
460 stmt.execute(String.format(instancesIdx3, instance_type_dbname, instance_type_dbname));
461 stmt.execute(String.format(instancesIdx4, instance_type_dbname, instance_type_dbname));
465 stmt.execute(String.format(createReferenceTypesTableTemplate.toString(), reference_type_dbname, reference_type_dbname));
466 stmt.execute(String.format(referenceTypesIdx1, reference_type_dbname, reference_type_dbname));
467 stmt.execute(String.format(referenceTypesIdx2, reference_type_dbname, reference_type_dbname));
471 }
catch (SQLException ex) {
472 LOGGER.log(Level.SEVERE,
"Error initializing db schema.", ex);
475 LOGGER.log(Level.SEVERE,
"Error getting default correlation types. Likely due to one or more Type's with an invalid db table name.");
491 &&
EamDbUtil.insertDefaultOrganization(conn);
497 boolean isChanged() {
505 return !host.equals(hostString) || !Integer.toString(port).equals(portString)
506 || !dbName.equals(dbNameString) || !Integer.toString(bulkThreshold).equals(bulkThresholdString)
507 || !userName.equals(userNameString) || !password.equals(userPasswordString);
521 if (null != host && !host.isEmpty()) {
539 if (port > 0 && port < 65535) {
542 throw new EamDbException(
"Invalid port. Must be a number greater than 0.");
553 return dbName.toLowerCase();
560 if (dbName == null || dbName.isEmpty()) {
561 throw new EamDbException(
"Invalid database name. Cannot be empty.");
562 }
else if (!Pattern.matches(DB_NAMES_REGEX, dbName)) {
563 throw new EamDbException(
"Invalid database name. Name must start with a lowercase letter and can only contain lowercase letters, numbers, and '_'.");
566 this.dbName = dbName.toLowerCase();
572 int getBulkThreshold() {
580 if (bulkThreshold > 0) {
598 if (userName == null || userName.isEmpty()) {
600 }
else if (!Pattern.matches(DB_USER_NAMES_REGEX, userName)) {
601 throw new EamDbException(
"Invalid user name. Name must start with a letter and can only contain letters, numbers, and '_'.");
617 if (password == null || password.isEmpty()) {
618 throw new EamDbException(
"Invalid user password. Cannot be empty.");
626 String getValidationQuery() {
640 String getJDBCBaseURI() {
static final Logger LOGGER
void setHost(String host)
static String correlationTypeToInstanceTableName(CorrelationAttributeInstance.Type type)
Connection getEphemeralConnection(boolean usePostgresDb)
static boolean schemaVersionIsSet(Connection conn)
final String VALIDATION_QUERY
static boolean executeValidationQuery(Connection conn, String validationQuery)
final String DEFAULT_DBNAME
boolean insertDefaultDatabaseContent()
boolean verifyDatabaseSchema()
final String JDBC_BASE_URI
final String DEFAULT_USERNAME
static void closeResultSet(ResultSet resultSet)
static void closeStatement(Statement statement)
static List< CorrelationAttributeInstance.Type > getDefaultCorrelationTypes()
final String DEFAULT_HOST
final String DB_NAMES_REGEX
void setPassword(String password)
final String DEFAULT_PASSWORD
static void closeConnection(Connection conn)
static synchronized void setConfigSetting(String moduleName, String settingName, String settingVal)
boolean initializeDatabaseSchema()
boolean verifyDatabaseExists()
static boolean insertDefaultCorrelationTypes(Connection conn)
static String getConfigSetting(String moduleName, String settingName)
boolean verifyConnection()
void setUserName(String userName)
void setDbName(String dbName)
synchronized static Logger getLogger(String name)
void setBulkThreshold(int bulkThreshold)
static String correlationTypeToReferenceTableName(CorrelationAttributeInstance.Type type)
static String convertTextToHexText(String property)
final String DB_USER_NAMES_REGEX
static final int FILES_TYPE_ID
static String convertHexTextToText(String property)