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;
66 if (host == null || host.isEmpty()) {
72 if (portString == null || portString.isEmpty()) {
75 port = Integer.parseInt(portString);
76 if (port < 0 || port > 65535) {
80 }
catch (NumberFormatException ex) {
85 if (dbName == null || dbName.isEmpty()) {
91 if (bulkThresholdString == null || bulkThresholdString.isEmpty()) {
94 this.bulkThreshold = Integer.parseInt(bulkThresholdString);
99 }
catch (NumberFormatException ex) {
104 if (userName == null || userName.isEmpty()) {
109 if (password == null || password.isEmpty()) {
115 LOGGER.log(Level.WARNING,
"Failed to convert password from hex text to text.", ex);
130 LOGGER.log(Level.SEVERE,
"Failed to convert password from text to hex text.", ex);
143 StringBuilder url =
new StringBuilder();
148 url.append(
"postgres");
153 return url.toString();
166 Properties props =
new Properties();
171 conn = DriverManager.getConnection(url, props);
172 }
catch (ClassNotFoundException | SQLException ex) {
175 LOGGER.log(Level.SEVERE,
"Failed to acquire ephemeral connection to postgresql.");
209 String sql =
"SELECT datname FROM pg_catalog.pg_database WHERE lower(datname) = lower(?) LIMIT 1";
210 PreparedStatement ps = null;
213 ps = conn.prepareStatement(sql);
215 rs = ps.executeQuery();
219 }
catch (SQLException ex) {
220 LOGGER.log(Level.SEVERE,
"Failed to execute database existance query.", ex);
254 String sql =
"CREATE DATABASE %s OWNER %s";
257 stmt = conn.createStatement();
259 }
catch (SQLException ex) {
260 LOGGER.log(Level.SEVERE,
"Failed to execute create database statement.", ex);
275 String sql =
"DROP DATABASE %s";
278 stmt = conn.createStatement();
279 stmt.execute(String.format(sql,
getDbName()));
280 }
catch (SQLException ex) {
281 LOGGER.log(Level.SEVERE,
"Failed to execute drop database statement.", ex);
307 StringBuilder createOrganizationsTable =
new StringBuilder();
308 createOrganizationsTable.append(
"CREATE TABLE IF NOT EXISTS organizations (");
309 createOrganizationsTable.append(
"id SERIAL PRIMARY KEY,");
310 createOrganizationsTable.append(
"org_name text NOT NULL,");
311 createOrganizationsTable.append(
"poc_name text NOT NULL,");
312 createOrganizationsTable.append(
"poc_email text NOT NULL,");
313 createOrganizationsTable.append(
"poc_phone text NOT NULL,");
314 createOrganizationsTable.append(
"CONSTRAINT org_name_unique UNIQUE (org_name)");
315 createOrganizationsTable.append(
")");
319 StringBuilder createCasesTable =
new StringBuilder();
320 createCasesTable.append(
"CREATE TABLE IF NOT EXISTS cases (");
321 createCasesTable.append(
"id SERIAL PRIMARY KEY,");
322 createCasesTable.append(
"case_uid text NOT NULL,");
323 createCasesTable.append(
"org_id integer,");
324 createCasesTable.append(
"case_name text NOT NULL,");
325 createCasesTable.append(
"creation_date text NOT NULL,");
326 createCasesTable.append(
"case_number text,");
327 createCasesTable.append(
"examiner_name text,");
328 createCasesTable.append(
"examiner_email text,");
329 createCasesTable.append(
"examiner_phone text,");
330 createCasesTable.append(
"notes text,");
331 createCasesTable.append(
"foreign key (org_id) references organizations(id) ON UPDATE SET NULL ON DELETE SET NULL,");
332 createCasesTable.append(
"CONSTRAINT case_uid_unique UNIQUE (case_uid)");
333 createCasesTable.append(
")");
336 String casesIdx1 =
"CREATE INDEX IF NOT EXISTS cases_org_id ON cases (org_id)";
337 String casesIdx2 =
"CREATE INDEX IF NOT EXISTS cases_case_uid ON cases (case_uid)";
339 StringBuilder createDataSourcesTable =
new StringBuilder();
340 createDataSourcesTable.append(
"CREATE TABLE IF NOT EXISTS data_sources (");
341 createDataSourcesTable.append(
"id SERIAL PRIMARY KEY,");
342 createDataSourcesTable.append(
"case_id integer NOT NULL,");
343 createDataSourcesTable.append(
"device_id text NOT NULL,");
344 createDataSourcesTable.append(
"name text NOT NULL,");
345 createDataSourcesTable.append(
"foreign key (case_id) references cases(id) ON UPDATE SET NULL ON DELETE SET NULL,");
346 createDataSourcesTable.append(
"CONSTRAINT datasource_unique UNIQUE (case_id, device_id, name)");
347 createDataSourcesTable.append(
")");
349 String dataSourceIdx1 =
"CREATE INDEX IF NOT EXISTS data_sources_name ON data_sources (name)";
351 StringBuilder createReferenceSetsTable =
new StringBuilder();
352 createReferenceSetsTable.append(
"CREATE TABLE IF NOT EXISTS reference_sets (");
353 createReferenceSetsTable.append(
"id SERIAL PRIMARY KEY,");
354 createReferenceSetsTable.append(
"org_id integer NOT NULL,");
355 createReferenceSetsTable.append(
"set_name text NOT NULL,");
356 createReferenceSetsTable.append(
"version text NOT NULL,");
357 createReferenceSetsTable.append(
"known_status integer NOT NULL,");
358 createReferenceSetsTable.append(
"read_only boolean NOT NULL,");
359 createReferenceSetsTable.append(
"type integer NOT NULL,");
360 createReferenceSetsTable.append(
"import_date text NOT NULL,");
361 createReferenceSetsTable.append(
"foreign key (org_id) references organizations(id) ON UPDATE SET NULL ON DELETE SET NULL,");
362 createReferenceSetsTable.append(
"CONSTRAINT hash_set_unique UNIQUE (set_name, version)");
363 createReferenceSetsTable.append(
")");
365 String referenceSetsIdx1 =
"CREATE INDEX IF NOT EXISTS reference_sets_org_id ON reference_sets (org_id)";
368 StringBuilder createReferenceTypesTableTemplate =
new StringBuilder();
369 createReferenceTypesTableTemplate.append(
"CREATE TABLE IF NOT EXISTS %s (");
370 createReferenceTypesTableTemplate.append(
"id SERIAL PRIMARY KEY,");
371 createReferenceTypesTableTemplate.append(
"reference_set_id integer,");
372 createReferenceTypesTableTemplate.append(
"value text NOT NULL,");
373 createReferenceTypesTableTemplate.append(
"known_status integer NOT NULL,");
374 createReferenceTypesTableTemplate.append(
"comment text,");
375 createReferenceTypesTableTemplate.append(
"CONSTRAINT %s_multi_unique UNIQUE (reference_set_id, value),");
376 createReferenceTypesTableTemplate.append(
"foreign key (reference_set_id) references reference_sets(id) ON UPDATE SET NULL ON DELETE SET NULL");
377 createReferenceTypesTableTemplate.append(
")");
380 String referenceTypesIdx1 =
"CREATE INDEX IF NOT EXISTS %s_value ON %s (value)";
381 String referenceTypesIdx2 =
"CREATE INDEX IF NOT EXISTS %s_value_known_status ON %s (value, known_status)";
383 StringBuilder createCorrelationTypesTable =
new StringBuilder();
384 createCorrelationTypesTable.append(
"CREATE TABLE IF NOT EXISTS correlation_types (");
385 createCorrelationTypesTable.append(
"id SERIAL PRIMARY KEY,");
386 createCorrelationTypesTable.append(
"display_name text NOT NULL,");
387 createCorrelationTypesTable.append(
"db_table_name text NOT NULL,");
388 createCorrelationTypesTable.append(
"supported integer NOT NULL,");
389 createCorrelationTypesTable.append(
"enabled integer NOT NULL,");
390 createCorrelationTypesTable.append(
"CONSTRAINT correlation_types_names UNIQUE (display_name, db_table_name)");
391 createCorrelationTypesTable.append(
")");
394 StringBuilder createArtifactInstancesTableTemplate =
new StringBuilder();
395 createArtifactInstancesTableTemplate.append(
"CREATE TABLE IF NOT EXISTS %s (");
396 createArtifactInstancesTableTemplate.append(
"id SERIAL PRIMARY KEY,");
397 createArtifactInstancesTableTemplate.append(
"case_id integer NOT NULL,");
398 createArtifactInstancesTableTemplate.append(
"data_source_id integer NOT NULL,");
399 createArtifactInstancesTableTemplate.append(
"value text NOT NULL,");
400 createArtifactInstancesTableTemplate.append(
"file_path text NOT NULL,");
401 createArtifactInstancesTableTemplate.append(
"known_status integer NOT NULL,");
402 createArtifactInstancesTableTemplate.append(
"comment text,");
403 createArtifactInstancesTableTemplate.append(
"CONSTRAINT %s_multi_unique_ UNIQUE (data_source_id, value, file_path),");
404 createArtifactInstancesTableTemplate.append(
"foreign key (case_id) references cases(id) ON UPDATE SET NULL ON DELETE SET NULL,");
405 createArtifactInstancesTableTemplate.append(
"foreign key (data_source_id) references data_sources(id) ON UPDATE SET NULL ON DELETE SET NULL");
406 createArtifactInstancesTableTemplate.append(
")");
409 String instancesIdx1 =
"CREATE INDEX IF NOT EXISTS %s_case_id ON %s (case_id)";
410 String instancesIdx2 =
"CREATE INDEX IF NOT EXISTS %s_data_source_id ON %s (data_source_id)";
411 String instancesIdx3 =
"CREATE INDEX IF NOT EXISTS %s_value ON %s (value)";
412 String instancesIdx4 =
"CREATE INDEX IF NOT EXISTS %s_value_known_status ON %s (value, known_status)";
414 StringBuilder createDbInfoTable =
new StringBuilder();
415 createDbInfoTable.append(
"CREATE TABLE IF NOT EXISTS db_info (");
416 createDbInfoTable.append(
"id SERIAL PRIMARY KEY NOT NULL,");
417 createDbInfoTable.append(
"name text NOT NULL,");
418 createDbInfoTable.append(
"value text NOT NULL");
419 createDbInfoTable.append(
")");
423 Connection conn = null;
429 Statement stmt = conn.createStatement();
431 stmt.execute(createOrganizationsTable.toString());
433 stmt.execute(createCasesTable.toString());
434 stmt.execute(casesIdx1);
435 stmt.execute(casesIdx2);
437 stmt.execute(createDataSourcesTable.toString());
438 stmt.execute(dataSourceIdx1);
440 stmt.execute(createReferenceSetsTable.toString());
441 stmt.execute(referenceSetsIdx1);
443 stmt.execute(createCorrelationTypesTable.toString());
445 stmt.execute(createDbInfoTable.toString());
450 String reference_type_dbname;
451 String instance_type_dbname;
456 stmt.execute(String.format(createArtifactInstancesTableTemplate.toString(), instance_type_dbname, instance_type_dbname));
457 stmt.execute(String.format(instancesIdx1, instance_type_dbname, instance_type_dbname));
458 stmt.execute(String.format(instancesIdx2, instance_type_dbname, instance_type_dbname));
459 stmt.execute(String.format(instancesIdx3, instance_type_dbname, instance_type_dbname));
460 stmt.execute(String.format(instancesIdx4, instance_type_dbname, instance_type_dbname));
464 stmt.execute(String.format(createReferenceTypesTableTemplate.toString(), reference_type_dbname, reference_type_dbname));
465 stmt.execute(String.format(referenceTypesIdx1, reference_type_dbname, reference_type_dbname));
466 stmt.execute(String.format(referenceTypesIdx2, reference_type_dbname, reference_type_dbname));
470 }
catch (SQLException ex) {
471 LOGGER.log(Level.SEVERE,
"Error initializing db schema.", ex);
474 LOGGER.log(Level.SEVERE,
"Error getting default correlation types. Likely due to one or more Type's with an invalid db table name.");
490 &&
EamDbUtil.insertDefaultOrganization(conn);
504 return !host.equals(hostString) || !Integer.toString(port).equals(portString)
505 || !dbName.equals(dbNameString) || !Integer.toString(bulkThreshold).equals(bulkThresholdString)
506 || !userName.equals(userNameString) || !password.equals(userPasswordString);
520 if (null != host && !host.isEmpty()) {
538 if (port > 0 && port < 65535) {
541 throw new EamDbException(
"Invalid port. Must be a number greater than 0.");
552 return dbName.toLowerCase();
559 if (dbName == null || dbName.isEmpty()) {
560 throw new EamDbException(
"Invalid database name. Cannot be empty.");
561 }
else if (!Pattern.matches(DB_NAMES_REGEX, dbName)) {
562 throw new EamDbException(
"Invalid database name. Name must start with a lowercase letter and can only contain lowercase letters, numbers, and '_'.");
565 this.dbName = dbName.toLowerCase();
579 if (bulkThreshold > 0) {
597 if (userName == null || userName.isEmpty()) {
599 }
else if (!Pattern.matches(DB_USER_NAMES_REGEX, userName)) {
600 throw new EamDbException(
"Invalid user name. Name must start with a letter and can only contain letters, numbers, and '_'.");
616 if (password == null || password.isEmpty()) {
617 throw new EamDbException(
"Invalid user password. Cannot be empty.");
static final Logger LOGGER
void setHost(String host)
Connection getEphemeralConnection(boolean usePostgresDb)
static boolean schemaVersionIsSet(Connection conn)
final String VALIDATION_QUERY
static boolean executeValidationQuery(Connection conn, String validationQuery)
final String DEFAULT_DBNAME
static final int FILES_TYPE_ID
boolean insertDefaultDatabaseContent()
boolean verifyDatabaseSchema()
final String JDBC_BASE_URI
final String DEFAULT_USERNAME
static void closeResultSet(ResultSet resultSet)
final String DEFAULT_HOST
final String DB_NAMES_REGEX
static String correlationTypeToReferenceTableName(CorrelationAttribute.Type type)
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()
final int DEFAULT_BULK_THRESHHOLD
String getValidationQuery()
static boolean insertDefaultCorrelationTypes(Connection conn)
String getConnectionURL(boolean usePostgresDb)
static String correlationTypeToInstanceTableName(CorrelationAttribute.Type type)
static String getConfigSetting(String moduleName, String settingName)
boolean verifyConnection()
void setUserName(String userName)
void setDbName(String dbName)
synchronized static Logger getLogger(String name)
static List< CorrelationAttribute.Type > getDefaultCorrelationTypes()
void setBulkThreshold(int bulkThreshold)
static String convertTextToHexText(String property)
static void closePreparedStatement(PreparedStatement preparedStatement)
final String DB_USER_NAMES_REGEX
static String convertHexTextToText(String property)