19 package org.sleuthkit.autopsy.centralrepository.datamodel;
22 import java.io.IOException;
23 import java.nio.file.Files;
24 import java.nio.file.InvalidPathException;
25 import java.sql.Connection;
26 import java.sql.DriverManager;
27 import java.sql.SQLException;
28 import java.sql.Statement;
29 import java.util.List;
30 import java.util.logging.Level;
31 import java.util.regex.Pattern;
66 if (dbName == null || dbName.isEmpty()) {
71 if (dbDirectory == null || dbDirectory.isEmpty()) {
77 if (bulkThresholdString == null || bulkThresholdString.isEmpty()) {
80 this.bulkThreshold = Integer.parseInt(bulkThresholdString);
85 }
catch (NumberFormatException ex) {
105 if(! dbFile.exists()){
109 return ( ! dbFile.isDirectory());
121 if (!dbDir.exists()) {
123 }
else if (!dbDir.isDirectory()) {
140 Files.createDirectories(dbDir.toPath());
141 LOGGER.log(Level.INFO,
"sqlite directory did not exist, created it at {0}.",
getDbDirectory());
142 }
catch (IOException | InvalidPathException | SecurityException ex) {
143 LOGGER.log(Level.SEVERE,
"Failed to create sqlite database directory.", ex);
157 return dbFile.delete();
166 StringBuilder url =
new StringBuilder();
170 return url.toString();
190 conn = DriverManager.getConnection(url);
191 }
catch (ClassNotFoundException | SQLException ex) {
192 LOGGER.log(Level.SEVERE,
"Failed to acquire ephemeral connection to sqlite.", ex);
249 StringBuilder createOrganizationsTable =
new StringBuilder();
250 createOrganizationsTable.append(
"CREATE TABLE IF NOT EXISTS organizations (");
251 createOrganizationsTable.append(
"id integer primary key autoincrement NOT NULL,");
252 createOrganizationsTable.append(
"org_name text NOT NULL,");
253 createOrganizationsTable.append(
"poc_name text NOT NULL,");
254 createOrganizationsTable.append(
"poc_email text NOT NULL,");
255 createOrganizationsTable.append(
"poc_phone text NOT NULL,");
256 createOrganizationsTable.append(
"CONSTRAINT org_name_unique UNIQUE (org_name)");
257 createOrganizationsTable.append(
")");
261 StringBuilder createCasesTable =
new StringBuilder();
262 createCasesTable.append(
"CREATE TABLE IF NOT EXISTS cases (");
263 createCasesTable.append(
"id integer primary key autoincrement NOT NULL,");
264 createCasesTable.append(
"case_uid text NOT NULL,");
265 createCasesTable.append(
"org_id integer,");
266 createCasesTable.append(
"case_name text NOT NULL,");
267 createCasesTable.append(
"creation_date text NOT NULL,");
268 createCasesTable.append(
"case_number text,");
269 createCasesTable.append(
"examiner_name text,");
270 createCasesTable.append(
"examiner_email text,");
271 createCasesTable.append(
"examiner_phone text,");
272 createCasesTable.append(
"notes text,");
273 createCasesTable.append(
"CONSTRAINT case_uid_unique UNIQUE(case_uid) ON CONFLICT IGNORE,");
274 createCasesTable.append(
"foreign key (org_id) references organizations(id) ON UPDATE SET NULL ON DELETE SET NULL");
275 createCasesTable.append(
")");
278 String casesIdx1 =
"CREATE INDEX IF NOT EXISTS cases_org_id ON cases (org_id)";
279 String casesIdx2 =
"CREATE INDEX IF NOT EXISTS cases_case_uid ON cases (case_uid)";
281 StringBuilder createDataSourcesTable =
new StringBuilder();
282 createDataSourcesTable.append(
"CREATE TABLE IF NOT EXISTS data_sources (");
283 createDataSourcesTable.append(
"id integer primary key autoincrement NOT NULL,");
284 createDataSourcesTable.append(
"case_id integer NOT NULL,");
285 createDataSourcesTable.append(
"device_id text NOT NULL,");
286 createDataSourcesTable.append(
"name text NOT NULL,");
287 createDataSourcesTable.append(
"foreign key (case_id) references cases(id) ON UPDATE SET NULL ON DELETE SET NULL,");
288 createDataSourcesTable.append(
"CONSTRAINT datasource_unique UNIQUE (case_id, device_id, name)");
289 createDataSourcesTable.append(
")");
291 String dataSourceIdx1 =
"CREATE INDEX IF NOT EXISTS data_sources_name ON data_sources (name)";
293 StringBuilder createReferenceSetsTable =
new StringBuilder();
294 createReferenceSetsTable.append(
"CREATE TABLE IF NOT EXISTS reference_sets (");
295 createReferenceSetsTable.append(
"id integer primary key autoincrement NOT NULL,");
296 createReferenceSetsTable.append(
"org_id integer NOT NULL,");
297 createReferenceSetsTable.append(
"set_name text NOT NULL,");
298 createReferenceSetsTable.append(
"version text NOT NULL,");
299 createReferenceSetsTable.append(
"known_status integer NOT NULL,");
300 createReferenceSetsTable.append(
"read_only boolean NOT NULL,");
301 createReferenceSetsTable.append(
"type integer NOT NULL,");
302 createReferenceSetsTable.append(
"import_date text NOT NULL,");
303 createReferenceSetsTable.append(
"foreign key (org_id) references organizations(id) ON UPDATE SET NULL ON DELETE SET NULL,");
304 createReferenceSetsTable.append(
"CONSTRAINT hash_set_unique UNIQUE (set_name, version)");
305 createReferenceSetsTable.append(
")");
307 String referenceSetsIdx1 =
"CREATE INDEX IF NOT EXISTS reference_sets_org_id ON reference_sets (org_id)";
310 StringBuilder createReferenceTypesTableTemplate =
new StringBuilder();
311 createReferenceTypesTableTemplate.append(
"CREATE TABLE IF NOT EXISTS %s (");
312 createReferenceTypesTableTemplate.append(
"id integer primary key autoincrement NOT NULL,");
313 createReferenceTypesTableTemplate.append(
"reference_set_id integer,");
314 createReferenceTypesTableTemplate.append(
"value text NOT NULL,");
315 createReferenceTypesTableTemplate.append(
"known_status integer NOT NULL,");
316 createReferenceTypesTableTemplate.append(
"comment text,");
317 createReferenceTypesTableTemplate.append(
"CONSTRAINT %s_multi_unique UNIQUE(reference_set_id, value) ON CONFLICT IGNORE,");
318 createReferenceTypesTableTemplate.append(
"foreign key (reference_set_id) references reference_sets(id) ON UPDATE SET NULL ON DELETE SET NULL");
319 createReferenceTypesTableTemplate.append(
")");
322 String referenceTypesIdx1 =
"CREATE INDEX IF NOT EXISTS %s_value ON %s (value)";
323 String referenceTypesIdx2 =
"CREATE INDEX IF NOT EXISTS %s_value_known_status ON %s (value, known_status)";
325 StringBuilder createCorrelationTypesTable =
new StringBuilder();
326 createCorrelationTypesTable.append(
"CREATE TABLE IF NOT EXISTS correlation_types (");
327 createCorrelationTypesTable.append(
"id integer primary key autoincrement NOT NULL,");
328 createCorrelationTypesTable.append(
"display_name text NOT NULL,");
329 createCorrelationTypesTable.append(
"db_table_name text NOT NULL,");
330 createCorrelationTypesTable.append(
"supported integer NOT NULL,");
331 createCorrelationTypesTable.append(
"enabled integer NOT NULL,");
332 createCorrelationTypesTable.append(
"CONSTRAINT correlation_types_names UNIQUE (display_name, db_table_name)");
333 createCorrelationTypesTable.append(
")");
336 StringBuilder createArtifactInstancesTableTemplate =
new StringBuilder();
337 createArtifactInstancesTableTemplate.append(
"CREATE TABLE IF NOT EXISTS %s (");
338 createArtifactInstancesTableTemplate.append(
"id integer primary key autoincrement NOT NULL,");
339 createArtifactInstancesTableTemplate.append(
"case_id integer NOT NULL,");
340 createArtifactInstancesTableTemplate.append(
"data_source_id integer NOT NULL,");
341 createArtifactInstancesTableTemplate.append(
"value text NOT NULL,");
342 createArtifactInstancesTableTemplate.append(
"file_path text NOT NULL,");
343 createArtifactInstancesTableTemplate.append(
"known_status integer NOT NULL,");
344 createArtifactInstancesTableTemplate.append(
"comment text,");
345 createArtifactInstancesTableTemplate.append(
"CONSTRAINT %s_multi_unique UNIQUE(data_source_id, value, file_path) ON CONFLICT IGNORE,");
346 createArtifactInstancesTableTemplate.append(
"foreign key (case_id) references cases(id) ON UPDATE SET NULL ON DELETE SET NULL,");
347 createArtifactInstancesTableTemplate.append(
"foreign key (data_source_id) references data_sources(id) ON UPDATE SET NULL ON DELETE SET NULL");
348 createArtifactInstancesTableTemplate.append(
")");
351 String instancesIdx1 =
"CREATE INDEX IF NOT EXISTS %s_case_id ON %s (case_id)";
352 String instancesIdx2 =
"CREATE INDEX IF NOT EXISTS %s_data_source_id ON %s (data_source_id)";
353 String instancesIdx3 =
"CREATE INDEX IF NOT EXISTS %s_value ON %s (value)";
354 String instancesIdx4 =
"CREATE INDEX IF NOT EXISTS %s_value_known_status ON %s (value, known_status)";
356 StringBuilder createDbInfoTable =
new StringBuilder();
357 createDbInfoTable.append(
"CREATE TABLE IF NOT EXISTS db_info (");
358 createDbInfoTable.append(
"id integer primary key NOT NULL,");
359 createDbInfoTable.append(
"name text NOT NULL,");
360 createDbInfoTable.append(
"value text NOT NULL");
361 createDbInfoTable.append(
")");
365 Connection conn = null;
371 Statement stmt = conn.createStatement();
372 stmt.execute(PRAGMA_JOURNAL_WAL);
373 stmt.execute(PRAGMA_SYNC_OFF);
374 stmt.execute(PRAGMA_READ_UNCOMMITTED_TRUE);
375 stmt.execute(PRAGMA_ENCODING_UTF8);
376 stmt.execute(PRAGMA_PAGE_SIZE_4096);
377 stmt.execute(PRAGMA_FOREIGN_KEYS_ON);
379 stmt.execute(createOrganizationsTable.toString());
381 stmt.execute(createCasesTable.toString());
382 stmt.execute(casesIdx1);
383 stmt.execute(casesIdx2);
385 stmt.execute(createDataSourcesTable.toString());
386 stmt.execute(dataSourceIdx1);
388 stmt.execute(createReferenceSetsTable.toString());
389 stmt.execute(referenceSetsIdx1);
391 stmt.execute(createCorrelationTypesTable.toString());
393 stmt.execute(createDbInfoTable.toString());
398 String reference_type_dbname;
399 String instance_type_dbname;
404 stmt.execute(String.format(createArtifactInstancesTableTemplate.toString(), instance_type_dbname, instance_type_dbname));
405 stmt.execute(String.format(instancesIdx1, instance_type_dbname, instance_type_dbname));
406 stmt.execute(String.format(instancesIdx2, instance_type_dbname, instance_type_dbname));
407 stmt.execute(String.format(instancesIdx3, instance_type_dbname, instance_type_dbname));
408 stmt.execute(String.format(instancesIdx4, instance_type_dbname, instance_type_dbname));
412 stmt.execute(String.format(createReferenceTypesTableTemplate.toString(), reference_type_dbname, reference_type_dbname));
413 stmt.execute(String.format(referenceTypesIdx1, reference_type_dbname, reference_type_dbname));
414 stmt.execute(String.format(referenceTypesIdx2, reference_type_dbname, reference_type_dbname));
417 }
catch (SQLException ex) {
418 LOGGER.log(Level.SEVERE,
"Error initializing db schema.", ex);
421 LOGGER.log(Level.SEVERE,
"Error getting default correlation types. Likely due to one or more Type's with an invalid db table name.");
437 &&
EamDbUtil.insertDefaultOrganization(conn);
447 return !dbName.equals(dbNameString)
448 || !dbDirectory.equals(dbDirectoryString)
449 || !Integer.toString(bulkThreshold).equals(bulkThresholdString);
465 if (dbName == null || dbName.isEmpty()) {
466 throw new EamDbException(
"Invalid database file name. Cannot be null or empty.");
467 }
else if (!Pattern.matches(DB_NAMES_REGEX, dbName)) {
468 throw new EamDbException(
"Invalid database file name. Name must start with a lowercase letter and can only contain lowercase letters, numbers, and '_'.");
485 if (bulkThreshold > 0) {
509 if (dbDirectory != null && !dbDirectory.isEmpty()) {
512 throw new EamDbException(
"Invalid directory for sqlite database. Cannot empty");
boolean createDbDirectory()
boolean insertDefaultDatabaseContent()
static boolean schemaVersionIsSet(Connection conn)
static final String PRAGMA_JOURNAL_WAL
static final String PRAGMA_SYNC_OFF
static boolean executeValidationQuery(Connection conn, String validationQuery)
static final int FILES_TYPE_ID
String getFileNameWithPath()
boolean verifyConnection()
boolean initializeDatabaseSchema()
String getConnectionURL()
String getValidationQuery()
final int DEFAULT_BULK_THRESHHOLD
Connection getEphemeralConnection()
final String VALIDATION_QUERY
static String correlationTypeToReferenceTableName(CorrelationAttribute.Type type)
final String DEFAULT_DBDIRECTORY
static final String PRAGMA_READ_UNCOMMITTED_TRUE
final String JDBC_BASE_URI
static void closeConnection(Connection conn)
static synchronized void setConfigSetting(String moduleName, String settingName, String settingVal)
boolean verifyDatabaseSchema()
static final String PRAGMA_PAGE_SIZE_4096
static boolean insertDefaultCorrelationTypes(Connection conn)
static final Logger LOGGER
void setBulkThreshold(int bulkThreshold)
static String correlationTypeToInstanceTableName(CorrelationAttribute.Type type)
static String getConfigSetting(String moduleName, String settingName)
static final String PRAGMA_SYNC_NORMAL
final String DB_NAMES_REGEX
final String DEFAULT_DBNAME
void setDbName(String dbName)
synchronized static Logger getLogger(String name)
static List< CorrelationAttribute.Type > getDefaultCorrelationTypes()
static final String PRAGMA_FOREIGN_KEYS_ON
static final String PRAGMA_ENCODING_UTF8
void setDbDirectory(String dbDirectory)
boolean dbDirectoryExists()