Sleuth Kit Java Bindings (JNI)  4.12.1
Java bindings for using The Sleuth Kit
CaseDatabaseFactory.java
Go to the documentation of this file.
1 /*
2  * Sleuth Kit Data Model
3  *
4  * Copyright 2020-2021 Basis Technology Corp.
5  * Contact: carrier <at> sleuthkit <dot> org
6  *
7  * Licensed under the Apache License, Version 2.0 (the "License");
8  * you may not use this file except in compliance with the License.
9  * You may obtain a copy of the License at
10  *
11  * http://www.apache.org/licenses/LICENSE-2.0
12  *
13  * Unless required by applicable law or agreed to in writing, software
14  * distributed under the License is distributed on an "AS IS" BASIS,
15  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
16  * See the License for the specific language governing permissions and
17  * limitations under the License.
18  */
19 package org.sleuthkit.datamodel;
20 
21 import java.io.File;
22 import java.io.UnsupportedEncodingException;
23 import java.net.URLEncoder;
24 import java.sql.Connection;
25 import java.sql.DriverManager;
26 import java.sql.SQLException;
27 import java.sql.Statement;
28 import java.util.Properties;
29 import java.util.logging.Logger;
30 import java.util.logging.Level;
31 import org.sleuthkit.datamodel.SQLHelper.PostgreSQLHelper;
32 import org.sleuthkit.datamodel.SQLHelper.SQLiteHelper;
33 
37 class CaseDatabaseFactory {
38 
39  private static final Logger logger = Logger.getLogger(CaseDatabaseFactory.class.getName());
40  private final SQLHelper dbQueryHelper;
41  private final DbCreationHelper dbCreationHelper;
42 
43  // ssl=true: enables SSL encryption.
44  // NonValidatingFactory avoids hostname verification.
45  // sslmode=require: This mode makes the encryption mandatory and also requires the connection to fail if it can't be encrypted.
46  // In this mode, the JDBC driver accepts all server certificates, including self-signed ones.
47  final static String SSL_NONVERIFY_URL = "?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory&sslmode=require";
48 
49  // ssl=true: enables SSL encryption.
50  // DefaultJavaSSLFactory: uses application's default JRE keystore to validate server certificate.
51  // sslmode=verify-ca: verifies that the server we are connecting to is trusted by CA.
52  final static String SSL_VERIFY_DEFAULT_URL = "?ssl=true&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory&sslmode=verify-ca";
53 
70  static String getCustomPostrgesSslVerificationUrl(String customSslValidationClassName) {
71  return "?ssl=true&sslfactory=" + customSslValidationClassName + "&sslmode=verify-ca";
72  }
73 
79  CaseDatabaseFactory(String dbPath) {
80  this.dbQueryHelper = new SQLiteHelper();
81  this.dbCreationHelper = new SQLiteDbCreationHelper(dbPath);
82  }
83 
94  CaseDatabaseFactory(String caseName, CaseDbConnectionInfo info) {
95  this.dbQueryHelper = new PostgreSQLHelper();
96  this.dbCreationHelper = new PostgreSQLDbCreationHelper(caseName, info);
97  }
98 
105  void createCaseDatabase() throws TskCoreException {
106  createDatabase();
107  initializeSchema();
108  }
109 
115  private void createDatabase() throws TskCoreException {
116  dbCreationHelper.createDatabase();
117  }
118 
124  private void initializeSchema() throws TskCoreException {
125  try (Connection conn = dbCreationHelper.getConnection()) {
126  // Perform any needed steps before creating the tables
127  dbCreationHelper.performPreInitialization(conn);
128 
129  // Add schema version
130  addDbInfo(conn);
131 
132  // Add tables
133  addTables(conn);
134  dbCreationHelper.performPostTableInitialization(conn);
135 
136  // Add indexes
137  addIndexes(conn);
138  } catch (SQLException ex) {
139  throw new TskCoreException("Error initializing case database", ex);
140  }
141  }
142 
150  private void addDbInfo(Connection conn) throws TskCoreException {
151  CaseDbSchemaVersionNumber version = SleuthkitCase.CURRENT_DB_SCHEMA_VERSION;
152  long tskVersionNum = SleuthkitJNI.getSleuthkitVersion(); // This is the current version of TSK
153 
154  try (Statement stmt = conn.createStatement()) {
155  stmt.execute("CREATE TABLE tsk_db_info (schema_ver INTEGER, tsk_ver INTEGER, schema_minor_ver INTEGER)");
156  stmt.execute("INSERT INTO tsk_db_info (schema_ver, tsk_ver, schema_minor_ver) VALUES (" +
157  version.getMajor() + ", " + tskVersionNum + ", " + version.getMinor() + ");");
158 
159  stmt.execute("CREATE TABLE tsk_db_info_extended (name TEXT PRIMARY KEY, value TEXT NOT NULL);");
160  stmt.execute("INSERT INTO tsk_db_info_extended (name, value) VALUES ('TSK_VERSION', '" + tskVersionNum + "');");
161  stmt.execute("INSERT INTO tsk_db_info_extended (name, value) VALUES ('SCHEMA_MAJOR_VERSION', '" + version.getMajor() + "');");
162  stmt.execute("INSERT INTO tsk_db_info_extended (name, value) VALUES ('SCHEMA_MINOR_VERSION', '" + version.getMinor() + "');");
163  stmt.execute("INSERT INTO tsk_db_info_extended (name, value) VALUES ('CREATION_SCHEMA_MAJOR_VERSION', '" + version.getMajor() + "');");
164  stmt.execute("INSERT INTO tsk_db_info_extended (name, value) VALUES ('CREATION_SCHEMA_MINOR_VERSION', '" + version.getMinor() + "');");
165  } catch (SQLException ex) {
166  throw new TskCoreException("Error initializing db_info tables", ex);
167  }
168  }
169 
177  private void addTables(Connection conn) throws TskCoreException {
178  try (Statement stmt = conn.createStatement()) {
179  createTskObjects(stmt);
180  createHostTables(stmt);
181  createAccountTables(stmt);
182  createFileTables(stmt);
183  createArtifactTables(stmt);
184  createAnalysisResultsTables(stmt);
185  createTagTables(stmt);
186  createIngestTables(stmt);
187  createEventTables(stmt);
188  createAttributeTables(stmt);
189  createAccountInstancesAndArtifacts(stmt);
190  } catch (SQLException ex) {
191  throw new TskCoreException("Error initializing tables", ex);
192  }
193  }
194 
195  // tsk_objects is referenced by many other tables and should be created first
196  private void createTskObjects(Statement stmt) throws SQLException {
197  // The UNIQUE here on the object ID is to create an index
198  stmt.execute("CREATE TABLE tsk_objects (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, par_obj_id " + dbQueryHelper.getBigIntType()
199  + ", type INTEGER NOT NULL, UNIQUE (obj_id), FOREIGN KEY (par_obj_id) REFERENCES tsk_objects (obj_id) ON DELETE CASCADE)");
200  }
201 
202  private void createFileTables(Statement stmt) throws SQLException {
203 
204  stmt.execute("CREATE TABLE tsk_image_info (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, type INTEGER, ssize INTEGER, "
205  + "tzone TEXT, size " + dbQueryHelper.getBigIntType() + ", md5 TEXT, sha1 TEXT, sha256 TEXT, display_name TEXT, "
206  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
207 
208  stmt.execute("CREATE TABLE tsk_image_names (obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, name TEXT NOT NULL, "
209  + "sequence INTEGER NOT NULL, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
210 
211  stmt.execute("CREATE TABLE tsk_vs_info (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, vs_type INTEGER NOT NULL, "
212  + "img_offset " + dbQueryHelper.getBigIntType() + " NOT NULL, block_size " + dbQueryHelper.getBigIntType() + " NOT NULL, "
213  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
214 
215  stmt.execute("CREATE TABLE tsk_vs_parts (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
216  + "addr " + dbQueryHelper.getBigIntType() + " NOT NULL, start " + dbQueryHelper.getBigIntType() + " NOT NULL, "
217  + "length " + dbQueryHelper.getBigIntType() + " NOT NULL, "
218  + dbQueryHelper.getVSDescColName() + " TEXT, "
219  + "flags INTEGER NOT NULL, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE);");
220 
221  stmt.execute("CREATE TABLE tsk_pool_info (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
222  + "pool_type INTEGER NOT NULL, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE);");
223 
224  stmt.execute("CREATE TABLE data_source_info (obj_id " + dbQueryHelper.getBigIntType() + " PRIMARY KEY, device_id TEXT NOT NULL, "
225  + "time_zone TEXT NOT NULL, acquisition_details TEXT, added_date_time "+ dbQueryHelper.getBigIntType() + ", "
226  + "acquisition_tool_settings TEXT, acquisition_tool_name TEXT, acquisition_tool_version TEXT, "
227  + "host_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
228  + "FOREIGN KEY(host_id) REFERENCES tsk_hosts(id), "
229  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
230 
231  stmt.execute("CREATE TABLE tsk_fs_info (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
232  + "data_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
233  + "img_offset " + dbQueryHelper.getBigIntType() + " NOT NULL, fs_type INTEGER NOT NULL, "
234  + "block_size " + dbQueryHelper.getBigIntType() + " NOT NULL, "
235  + "block_count " + dbQueryHelper.getBigIntType() + " NOT NULL, root_inum " + dbQueryHelper.getBigIntType() + " NOT NULL, "
236  + "first_inum " + dbQueryHelper.getBigIntType() + " NOT NULL, last_inum " + dbQueryHelper.getBigIntType() + " NOT NULL, "
237  + "display_name TEXT, "
238  + "FOREIGN KEY(data_source_obj_id) REFERENCES data_source_info(obj_id) ON DELETE CASCADE, "
239  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
240 
241  stmt.execute("CREATE TABLE file_collection_status_types (collection_status_type INTEGER PRIMARY KEY, name TEXT NOT NULL)");
242 
243  stmt.execute("CREATE TABLE tsk_files (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
244  + "fs_obj_id " + dbQueryHelper.getBigIntType() + ", data_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
245  + "attr_type INTEGER, attr_id INTEGER, "
246  + "name TEXT NOT NULL, meta_addr " + dbQueryHelper.getBigIntType() + ", meta_seq " + dbQueryHelper.getBigIntType() + ", "
247  + "type INTEGER, has_layout INTEGER, has_path INTEGER, "
248  + "dir_type INTEGER, meta_type INTEGER, dir_flags INTEGER, meta_flags INTEGER, size " + dbQueryHelper.getBigIntType() + ", "
249  + "ctime " + dbQueryHelper.getBigIntType() + ", "
250  + "crtime " + dbQueryHelper.getBigIntType() + ", atime " + dbQueryHelper.getBigIntType() + ", "
251  + "mtime " + dbQueryHelper.getBigIntType() + ", mode INTEGER, uid INTEGER, gid INTEGER, md5 TEXT, sha256 TEXT, sha1 TEXT,"
252  + "known INTEGER, "
253  + "parent_path TEXT, mime_type TEXT, extension TEXT, "
254  + "owner_uid TEXT DEFAULT NULL, "
255  + "os_account_obj_id " + dbQueryHelper.getBigIntType() + " DEFAULT NULL, "
256  + "collected INTEGER NOT NULL, "
257  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
258  + "FOREIGN KEY(fs_obj_id) REFERENCES tsk_fs_info(obj_id) ON DELETE CASCADE, "
259  + "FOREIGN KEY(data_source_obj_id) REFERENCES data_source_info(obj_id) ON DELETE CASCADE, "
260  + "FOREIGN KEY(os_account_obj_id) REFERENCES tsk_os_accounts(os_account_obj_id) ON DELETE SET NULL, "
261  + "FOREIGN KEY(collected) REFERENCES file_collection_status_types (collection_status_type))" );
262 
263  stmt.execute("CREATE TABLE file_encoding_types (encoding_type INTEGER PRIMARY KEY, name TEXT NOT NULL)");
264 
265  stmt.execute("CREATE TABLE tsk_files_path (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, path TEXT NOT NULL, "
266  + "encoding_type INTEGER NOT NULL, FOREIGN KEY(encoding_type) references file_encoding_types(encoding_type), "
267  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
268 
269  stmt.execute("CREATE TABLE tsk_files_derived (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
270  + "derived_id " + dbQueryHelper.getBigIntType() + " NOT NULL, rederive TEXT, "
271  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
272 
273  stmt.execute("CREATE TABLE tsk_files_derived_method (derived_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
274  + "tool_name TEXT NOT NULL, tool_version TEXT NOT NULL, other TEXT)");
275 
276  stmt.execute("CREATE TABLE tsk_file_layout (obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
277  + "byte_start " + dbQueryHelper.getBigIntType() + " NOT NULL, byte_len " + dbQueryHelper.getBigIntType() + " NOT NULL, "
278  + "sequence INTEGER NOT NULL, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE);");
279 
280  stmt.execute("CREATE TABLE reports (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, path TEXT NOT NULL, "
281  + "crtime INTEGER NOT NULL, src_module_name TEXT NOT NULL, report_name TEXT NOT NULL, "
282  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE);");
283  }
284 
285  private void createArtifactTables(Statement stmt) throws SQLException {
286  stmt.execute("CREATE TABLE blackboard_artifact_types (artifact_type_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
287  + "type_name TEXT NOT NULL, display_name TEXT,"
288  + "category_type INTEGER DEFAULT 0)");
289 
290  stmt.execute("CREATE TABLE blackboard_attribute_types (attribute_type_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
291  + "type_name TEXT NOT NULL, display_name TEXT, value_type INTEGER NOT NULL)");
292 
293  stmt.execute("CREATE TABLE review_statuses (review_status_id INTEGER PRIMARY KEY, "
294  + "review_status_name TEXT NOT NULL, "
295  + "display_name TEXT NOT NULL)");
296 
297  stmt.execute("CREATE TABLE blackboard_artifacts (artifact_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
298  + "obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
299  + "artifact_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
300  + "data_source_obj_id " + dbQueryHelper.getBigIntType() + ", "
301  + "artifact_type_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
302  + "review_status_id INTEGER NOT NULL, "
303  + "UNIQUE (artifact_obj_id),"
304  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
305  + "FOREIGN KEY(artifact_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
306  + "FOREIGN KEY(data_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
307  + "FOREIGN KEY(artifact_type_id) REFERENCES blackboard_artifact_types(artifact_type_id), "
308  + "FOREIGN KEY(review_status_id) REFERENCES review_statuses(review_status_id))");
309 
310  /* Binary representation of BYTEA is a bunch of bytes, which could
311  * include embedded nulls so we have to pay attention to field length.
312  * http://www.postgresql.org/docs/9.4/static/libpq-example.html
313  */
314  stmt.execute("CREATE TABLE blackboard_attributes (artifact_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
315  + "artifact_type_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
316  + "source TEXT, context TEXT, attribute_type_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
317  + "value_type INTEGER NOT NULL, value_byte " + dbQueryHelper.getBlobType() + ", "
318  + "value_text TEXT, value_int32 INTEGER, value_int64 " + dbQueryHelper.getBigIntType() + ", value_double NUMERIC(20, 10), "
319  + "FOREIGN KEY(artifact_id) REFERENCES blackboard_artifacts(artifact_id) ON DELETE CASCADE, "
320  + "FOREIGN KEY(artifact_type_id) REFERENCES blackboard_artifact_types(artifact_type_id), "
321  + "FOREIGN KEY(attribute_type_id) REFERENCES blackboard_attribute_types(attribute_type_id))");
322  }
323 
324  private void createAnalysisResultsTables(Statement stmt) throws SQLException {
325  stmt.execute("CREATE TABLE tsk_analysis_results (artifact_obj_id " + dbQueryHelper.getBigIntType() + " PRIMARY KEY, "
326  + "conclusion TEXT, "
327  + "significance INTEGER NOT NULL, "
328  + "priority INTEGER NOT NULL, "
329  + "configuration TEXT, justification TEXT, "
330  + "ignore_score INTEGER DEFAULT 0, " // boolean
331  + "FOREIGN KEY(artifact_obj_id) REFERENCES blackboard_artifacts(artifact_obj_id) ON DELETE CASCADE"
332  + ")");
333 
334  stmt.execute("CREATE TABLE tsk_aggregate_score( obj_id " + dbQueryHelper.getBigIntType() + " PRIMARY KEY, "
335  + "data_source_obj_id " + dbQueryHelper.getBigIntType() + ", "
336  + "significance INTEGER NOT NULL, "
337  + "priority INTEGER NOT NULL, "
338  + "UNIQUE (obj_id),"
339  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
340  + "FOREIGN KEY(data_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE "
341  + ")");
342 
343  }
344  private void createTagTables(Statement stmt) throws SQLException {
345  stmt.execute("CREATE TABLE tsk_tag_sets (tag_set_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, name TEXT UNIQUE)");
346  stmt.execute("CREATE TABLE tag_names (tag_name_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, display_name TEXT UNIQUE, "
347  + "description TEXT NOT NULL, color TEXT NOT NULL, knownStatus INTEGER NOT NULL,"
348  + " tag_set_id " + dbQueryHelper.getBigIntType() + ", rank INTEGER, FOREIGN KEY(tag_set_id) REFERENCES tsk_tag_sets(tag_set_id) ON DELETE SET NULL)");
349 
350  stmt.execute("CREATE TABLE tsk_examiners (examiner_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
351  + "login_name TEXT NOT NULL, display_name TEXT, UNIQUE(login_name))");
352 
353  stmt.execute("CREATE TABLE content_tags (tag_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
354  + "obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, tag_name_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
355  + "comment TEXT NOT NULL, begin_byte_offset " + dbQueryHelper.getBigIntType() + " NOT NULL, "
356  + "end_byte_offset " + dbQueryHelper.getBigIntType() + " NOT NULL, "
357  + "examiner_id " + dbQueryHelper.getBigIntType() + ", "
358  + "FOREIGN KEY(examiner_id) REFERENCES tsk_examiners(examiner_id) ON DELETE CASCADE, "
359  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
360  + "FOREIGN KEY(tag_name_id) REFERENCES tag_names(tag_name_id) ON DELETE CASCADE)");
361 
362  stmt.execute("CREATE TABLE blackboard_artifact_tags (tag_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
363  + "artifact_id " + dbQueryHelper.getBigIntType() + " NOT NULL, tag_name_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
364  + "comment TEXT NOT NULL, examiner_id " + dbQueryHelper.getBigIntType() + ", "
365  + "FOREIGN KEY(examiner_id) REFERENCES tsk_examiners(examiner_id) ON DELETE CASCADE, "
366  + "FOREIGN KEY(artifact_id) REFERENCES blackboard_artifacts(artifact_id) ON DELETE CASCADE, "
367  + "FOREIGN KEY(tag_name_id) REFERENCES tag_names(tag_name_id) ON DELETE CASCADE)");
368  }
369 
376  private void addIndexes(Connection conn) throws TskCoreException {
377  try (Statement stmt = conn.createStatement()) {
378  // tsk_objects index
379  stmt.execute("CREATE INDEX parObjId ON tsk_objects(par_obj_id)");
380 
381  // file layout index
382  stmt.execute("CREATE INDEX layout_objID ON tsk_file_layout(obj_id)");
383 
384  // blackboard indexes
385  stmt.execute("CREATE INDEX artifact_objID ON blackboard_artifacts(obj_id)");
386  stmt.execute("CREATE INDEX artifact_artifact_objID ON blackboard_artifacts(artifact_obj_id)");
387  stmt.execute("CREATE INDEX artifact_typeID ON blackboard_artifacts(artifact_type_id)");
388  stmt.execute("CREATE INDEX attrsArtifactID ON blackboard_attributes(artifact_id)");
389 
390  //file type indexes
391  stmt.execute("CREATE INDEX mime_type ON tsk_files(dir_type,mime_type,type)");
392  stmt.execute("CREATE INDEX file_extension ON tsk_files(extension)");
393 
394  // account indexes
395  stmt.execute("CREATE INDEX relationships_account1 ON account_relationships(account1_id)");
396  stmt.execute("CREATE INDEX relationships_account2 ON account_relationships(account2_id)");
397  stmt.execute("CREATE INDEX relationships_relationship_source_obj_id ON account_relationships(relationship_source_obj_id)");
398  stmt.execute("CREATE INDEX relationships_date_time ON account_relationships(date_time)");
399  stmt.execute("CREATE INDEX relationships_relationship_type ON account_relationships(relationship_type)");
400  stmt.execute("CREATE INDEX relationships_data_source_obj_id ON account_relationships(data_source_obj_id)");
401 
402  //tsk_events indices
403  stmt.execute("CREATE INDEX events_data_source_obj_id ON tsk_event_descriptions(data_source_obj_id)");
404  stmt.execute("CREATE INDEX events_content_obj_id ON tsk_event_descriptions(content_obj_id)");
405  stmt.execute("CREATE INDEX events_artifact_id ON tsk_event_descriptions(artifact_id)");
406  stmt.execute("CREATE INDEX events_sub_type_time ON tsk_events(event_type_id, time)");
407  stmt.execute("CREATE INDEX events_time ON tsk_events(time)");
408 
409  // analysis results and scores indices
410  stmt.execute("CREATE INDEX score_significance_priority ON tsk_aggregate_score(significance, priority)");
411  stmt.execute("CREATE INDEX score_datasource_obj_id ON tsk_aggregate_score(data_source_obj_id)");
412 
413  stmt.execute("CREATE INDEX tsk_file_attributes_obj_id ON tsk_file_attributes(obj_id)");
414 
415  // For DC support
416  stmt.execute("CREATE INDEX tsk_os_accounts_login_name_idx ON tsk_os_accounts(login_name, db_status, realm_id)");
417  stmt.execute("CREATE INDEX tsk_os_accounts_addr_idx ON tsk_os_accounts(addr, db_status, realm_id)");
418 
419  stmt.execute("CREATE INDEX tsk_os_account_realms_realm_name_idx ON tsk_os_account_realms(realm_name)");
420  stmt.execute("CREATE INDEX tsk_os_account_realms_realm_addr_idx ON tsk_os_account_realms(realm_addr)");
421 
422  } catch (SQLException ex) {
423  throw new TskCoreException("Error initializing db_info tables", ex);
424  }
425  }
426 
427  private void createIngestTables(Statement stmt) throws SQLException {
428  stmt.execute("CREATE TABLE ingest_module_types (type_id INTEGER PRIMARY KEY, type_name TEXT NOT NULL)");
429 
430  stmt.execute("CREATE TABLE ingest_job_status_types (type_id INTEGER PRIMARY KEY, type_name TEXT NOT NULL)");
431 
432  stmt.execute("CREATE TABLE ingest_modules (ingest_module_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
433  + "display_name TEXT NOT NULL, unique_name TEXT UNIQUE NOT NULL, type_id INTEGER NOT NULL, "
434  + "version TEXT NOT NULL, FOREIGN KEY(type_id) REFERENCES ingest_module_types(type_id) ON DELETE CASCADE);");
435 
436  stmt.execute("CREATE TABLE ingest_jobs (ingest_job_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
437  + "obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, host_name TEXT NOT NULL, "
438  + "start_date_time " + dbQueryHelper.getBigIntType() + " NOT NULL, "
439  + "end_date_time " + dbQueryHelper.getBigIntType() + " NOT NULL, status_id INTEGER NOT NULL, "
440  + "settings_dir TEXT, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
441  + "FOREIGN KEY(status_id) REFERENCES ingest_job_status_types(type_id) ON DELETE CASCADE);");
442 
443  stmt.execute("CREATE TABLE ingest_job_modules (ingest_job_id INTEGER, ingest_module_id INTEGER, "
444  + "pipeline_position INTEGER, PRIMARY KEY(ingest_job_id, ingest_module_id), "
445  + "FOREIGN KEY(ingest_job_id) REFERENCES ingest_jobs(ingest_job_id) ON DELETE CASCADE, "
446  + "FOREIGN KEY(ingest_module_id) REFERENCES ingest_modules(ingest_module_id) ON DELETE CASCADE);");
447  }
448 
449  private void createHostTables(Statement stmt) throws SQLException {
450 
451  stmt.execute("CREATE TABLE tsk_persons (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
452  + "name TEXT NOT NULL, " // person name
453  + "UNIQUE(name)) ");
454 
455  // References tsk_persons
456  stmt.execute("CREATE TABLE tsk_hosts (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
457  + "name TEXT NOT NULL, " // host name
458  + "db_status INTEGER DEFAULT 0, " // active/merged/deleted
459  + "person_id INTEGER, "
460  + "merged_into " + dbQueryHelper.getBigIntType() + ", "
461  + "FOREIGN KEY(person_id) REFERENCES tsk_persons(id) ON DELETE SET NULL, "
462  + "FOREIGN KEY(merged_into) REFERENCES tsk_hosts(id) ON DELETE CASCADE, "
463  + "UNIQUE(name)) ");
464 
465  stmt.execute("CREATE TABLE tsk_host_addresses (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
466  + "address_type INTEGER NOT NULL, "
467  + "address TEXT NOT NULL, "
468  + "UNIQUE(address_type, address)) ");
469 
470  stmt.execute("CREATE TABLE tsk_host_address_map (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
471  + "host_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
472  + "addr_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
473  + "source_obj_id " + dbQueryHelper.getBigIntType() + ", " // object id of the source where this mapping was found.
474  + "time " + dbQueryHelper.getBigIntType() + ", " // time at which the mapping existed
475  + "UNIQUE(host_id, addr_obj_id, time), "
476  + "FOREIGN KEY(host_id) REFERENCES tsk_hosts(id) ON DELETE CASCADE, "
477  + "FOREIGN KEY(addr_obj_id) REFERENCES tsk_host_addresses(id), "
478  + "FOREIGN KEY(source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE SET NULL )");
479 
480  // stores associations between DNS name and IP address
481  stmt.execute("CREATE TABLE tsk_host_address_dns_ip_map (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
482  + "dns_address_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
483  + "ip_address_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
484  + "source_obj_id " + dbQueryHelper.getBigIntType() + ", "
485  + "time " + dbQueryHelper.getBigIntType() + ", " // time at which the mapping existed
486  + "UNIQUE(dns_address_id, ip_address_id, time), "
487  + "FOREIGN KEY(dns_address_id) REFERENCES tsk_host_addresses(id) ON DELETE CASCADE, "
488  + "FOREIGN KEY(ip_address_id) REFERENCES tsk_host_addresses(id) ON DELETE CASCADE,"
489  + "FOREIGN KEY(source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE SET NULL )");
490 
491  // maps an address to an content/item using it
492  stmt.execute("CREATE TABLE tsk_host_address_usage (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
493  + "addr_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
494  + "obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, " // obj id of the content/item using the address
495  + "data_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, " // data source where the usage was found
496  + "UNIQUE(addr_obj_id, obj_id), "
497  + "FOREIGN KEY(addr_obj_id) REFERENCES tsk_host_addresses(id) ON DELETE CASCADE, "
498  + "FOREIGN KEY(data_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
499  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE )");
500  }
501 
502  // Must be called after tsk_persons, tsk_hosts and tsk_objects have been created.
503  private void createAccountTables(Statement stmt) throws SQLException {
504  stmt.execute("CREATE TABLE account_types (account_type_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
505  + "type_name TEXT UNIQUE NOT NULL, display_name TEXT NOT NULL)");
506 
507  // References account_types
508  stmt.execute("CREATE TABLE accounts (account_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
509  + "account_type_id INTEGER NOT NULL, account_unique_identifier TEXT NOT NULL, "
510  + "UNIQUE(account_type_id, account_unique_identifier), "
511  + "FOREIGN KEY(account_type_id) REFERENCES account_types(account_type_id))");
512 
513  // References accounts, tsk_objects
514  stmt.execute("CREATE TABLE account_relationships (relationship_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
515  + "account1_id INTEGER NOT NULL, account2_id INTEGER NOT NULL, "
516  + "relationship_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
517  + "date_time " + dbQueryHelper.getBigIntType() + ", relationship_type INTEGER NOT NULL, "
518  + "data_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
519  + "UNIQUE(account1_id, account2_id, relationship_source_obj_id), "
520  + "FOREIGN KEY(account1_id) REFERENCES accounts(account_id), "
521  + "FOREIGN KEY(account2_id) REFERENCES accounts(account_id), "
522  + "FOREIGN KEY(relationship_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
523  + "FOREIGN KEY(data_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
524 
525  // References tsk_hosts
526  stmt.execute("CREATE TABLE tsk_os_account_realms (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
527  + "realm_name TEXT DEFAULT NULL, " // realm name - for a domain realm, may be null
528  + "realm_addr TEXT DEFAULT NULL, " // a sid/uid or some some other identifier, may be null
529  + "realm_signature TEXT NOT NULL, " // Signature exists only to prevent duplicates. It is made up of realm address/name and scope host
530  + "scope_host_id " + dbQueryHelper.getBigIntType() + " DEFAULT NULL, " // if the realm scope is a single host
531  + "scope_confidence INTEGER, " // indicates whether we know for sure the realm scope or if we are inferring it
532  + "db_status INTEGER DEFAULT 0, " // active/merged/deleted
533  + "merged_into " + dbQueryHelper.getBigIntType() + " DEFAULT NULL, "
534  + "UNIQUE(realm_signature), "
535  + "FOREIGN KEY(scope_host_id) REFERENCES tsk_hosts(id) ON DELETE CASCADE,"
536  + "FOREIGN KEY(merged_into) REFERENCES tsk_os_account_realms(id) ON DELETE CASCADE )");
537 
538  // References tsk_objects, tsk_os_account_realms, tsk_persons
539  stmt.execute("CREATE TABLE tsk_os_accounts (os_account_obj_id " + dbQueryHelper.getBigIntType() + " PRIMARY KEY, "
540  + "login_name TEXT DEFAULT NULL, " // login name, if available, may be null
541  + "full_name TEXT DEFAULT NULL, " // full name, if available, may be null
542  + "realm_id " + dbQueryHelper.getBigIntType() + " NOT NULL, " // realm for the account
543  + "addr TEXT DEFAULT NULL, " // SID/UID, if available
544  + "signature TEXT NOT NULL, " // This exists only to prevent duplicates. It is either the addr or the login_name whichever is not null.
545  + "status INTEGER, " // enabled/disabled/deleted
546  + "type INTEGER, " // service/interactive
547  + "created_date " + dbQueryHelper.getBigIntType() + " DEFAULT NULL, "
548  + "db_status INTEGER DEFAULT 0, " // active/merged/deleted
549  + "merged_into " + dbQueryHelper.getBigIntType() + " DEFAULT NULL, "
550  + "UNIQUE(signature, realm_id), "
551  + "FOREIGN KEY(os_account_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
552  + "FOREIGN KEY(realm_id) REFERENCES tsk_os_account_realms(id) ON DELETE CASCADE,"
553  + "FOREIGN KEY(merged_into) REFERENCES tsk_os_accounts(os_account_obj_id) ON DELETE CASCADE )");
554 
555  }
556  // Must be called after createAccountTables() and blackboard_attribute_types, blackboard_artifacts creation.
557  private void createAccountInstancesAndArtifacts(Statement stmt) throws SQLException {
558 
559  // References tsk_os_accounts, tsk_hosts, tsk_objects, blackboard_attribute_types
560  stmt.execute("CREATE TABLE tsk_os_account_attributes (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
561  + "os_account_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
562  + "host_id " + dbQueryHelper.getBigIntType() + ", "
563  + "source_obj_id " + dbQueryHelper.getBigIntType() + ", "
564  + "attribute_type_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
565  + "value_type INTEGER NOT NULL, "
566  + "value_byte " + dbQueryHelper.getBlobType() + ", "
567  + "value_text TEXT, "
568  + "value_int32 INTEGER, value_int64 " + dbQueryHelper.getBigIntType() + ", "
569  + "value_double NUMERIC(20, 10), "
570  + "FOREIGN KEY(os_account_obj_id) REFERENCES tsk_os_accounts(os_account_obj_id) ON DELETE CASCADE, "
571  + "FOREIGN KEY(host_id) REFERENCES tsk_hosts(id) ON DELETE CASCADE, "
572  + "FOREIGN KEY(source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE SET NULL, "
573  + "FOREIGN KEY(attribute_type_id) REFERENCES blackboard_attribute_types(attribute_type_id))");
574 
575  // References tsk_os_accounts, tsk_objects, tsk_hosts
576  stmt.execute("CREATE TABLE tsk_os_account_instances (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
577  + "os_account_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
578  + "data_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
579  + "instance_type INTEGER NOT NULL, " // PerformedActionOn/ReferencedOn
580  + "UNIQUE(os_account_obj_id, data_source_obj_id, instance_type), "
581  + "FOREIGN KEY(os_account_obj_id) REFERENCES tsk_os_accounts(os_account_obj_id) ON DELETE CASCADE, "
582  + "FOREIGN KEY(data_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE ) ");
583 
584  // References blackboard_artifacts, tsk_os_accounts
585  stmt.execute("CREATE TABLE tsk_data_artifacts ( "
586  + "artifact_obj_id " + dbQueryHelper.getBigIntType() + " PRIMARY KEY, "
587  + "os_account_obj_id " + dbQueryHelper.getBigIntType() + ", "
588  + "FOREIGN KEY(artifact_obj_id) REFERENCES blackboard_artifacts(artifact_obj_id) ON DELETE CASCADE, "
589  + "FOREIGN KEY(os_account_obj_id) REFERENCES tsk_os_accounts(os_account_obj_id) ON DELETE SET NULL) ");
590  }
591 
592  private void createEventTables(Statement stmt) throws SQLException {
593  stmt.execute("CREATE TABLE tsk_event_types ("
594  + " event_type_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY,"
595  + " display_name TEXT UNIQUE NOT NULL , "
596  + " super_type_id INTEGER REFERENCES tsk_event_types(event_type_id) )");
597 
598  /*
599  * Regarding the timeline event tables schema, note that several columns
600  * in the tsk_event_descriptions table seem, at first glance, to be
601  * attributes of events rather than their descriptions and would appear
602  * to belong in tsk_events table instead. The rationale for putting the
603  * data source object ID, content object ID, artifact ID and the flags
604  * indicating whether or not the event source has a hash set hit or is
605  * tagged were motivated by the fact that these attributes are identical
606  * for each event in a set of file system file MAC time events. The
607  * decision was made to avoid duplication and save space by placing this
608  * data in the tsk_event-descriptions table.
609  */
610  stmt.execute(
611  "CREATE TABLE tsk_event_descriptions ( "
612  + " event_description_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
613  + " full_description TEXT NOT NULL, "
614  + " med_description TEXT, "
615  + " short_description TEXT,"
616  + " data_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
617  + " content_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
618  + " artifact_id " + dbQueryHelper.getBigIntType() + ", "
619  + " hash_hit INTEGER NOT NULL, " //boolean
620  + " tagged INTEGER NOT NULL, " //boolean
621  + " FOREIGN KEY(data_source_obj_id) REFERENCES data_source_info(obj_id) ON DELETE CASCADE, "
622  + " FOREIGN KEY(content_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
623  + " FOREIGN KEY(artifact_id) REFERENCES blackboard_artifacts(artifact_id) ON DELETE CASCADE,"
624  + " UNIQUE (full_description, content_obj_id, artifact_id))");
625 
626  stmt.execute(
627  "CREATE TABLE tsk_events ("
628  + " event_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
629  + " event_type_id " + dbQueryHelper.getBigIntType() + " NOT NULL REFERENCES tsk_event_types(event_type_id) ,"
630  + " event_description_id " + dbQueryHelper.getBigIntType() + " NOT NULL REFERENCES tsk_event_descriptions(event_description_id) ON DELETE CASCADE ,"
631  + " time " + dbQueryHelper.getBigIntType() + " NOT NULL , "
632  + " UNIQUE (event_type_id, event_description_id, time))");
633  }
634 
635  private void createAttributeTables(Statement stmt) throws SQLException {
636  /*
637  * Binary representation of BYTEA is a bunch of bytes, which could
638  * include embedded nulls so we have to pay attention to field length.
639  * http://www.postgresql.org/docs/9.4/static/libpq-example.html
640  */
641  stmt.execute("CREATE TABLE tsk_file_attributes ( id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
642  + "obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
643  + "attribute_type_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
644  + "value_type INTEGER NOT NULL, value_byte " + dbQueryHelper.getBlobType() + ", "
645  + "value_text TEXT, value_int32 INTEGER, value_int64 " + dbQueryHelper.getBigIntType() + ", value_double NUMERIC(20, 10), "
646  + "FOREIGN KEY(obj_id) REFERENCES tsk_files(obj_id) ON DELETE CASCADE, "
647  + "FOREIGN KEY(attribute_type_id) REFERENCES blackboard_attribute_types(attribute_type_id))");
648  }
649 
653  private abstract class DbCreationHelper {
654 
660  abstract void createDatabase() throws TskCoreException;
661 
667  abstract Connection getConnection() throws TskCoreException;
668 
677  abstract void performPreInitialization(Connection conn) throws TskCoreException;
678 
685  abstract void performPostTableInitialization(Connection conn) throws TskCoreException;
686  }
687 
691  private class PostgreSQLDbCreationHelper extends DbCreationHelper {
692 
693  private final static String JDBC_BASE_URI = "jdbc:postgresql://"; // NON-NLS
694  private final static String JDBC_DRIVER = "org.postgresql.Driver"; // NON-NLS
695 
696  final private String caseName;
697  final private CaseDbConnectionInfo info;
698 
699  PostgreSQLDbCreationHelper(String caseName, CaseDbConnectionInfo info) {
700  this.caseName = caseName;
701  this.info = info;
702  }
703 
704  @Override
705  void createDatabase() throws TskCoreException{
706  try(Connection conn = getPostgresConnection();
707  Statement stmt = conn.createStatement()) {
708  stmt.execute("CREATE DATABASE \"" + caseName + "\" WITH ENCODING='UTF8'");
709  } catch (SQLException ex) {
710  throw new TskCoreException("Error creating PostgreSQL case " + caseName, ex);
711  }
712  }
713 
714  @Override
715  Connection getConnection() throws TskCoreException {
716  return getConnection(caseName);
717  }
718 
724  Connection getPostgresConnection() throws TskCoreException {
725  return getConnection("postgres");
726  }
727 
735  Connection getConnection(String databaseName) throws TskCoreException {
736  String encodedDbName;
737  try {
738  encodedDbName = URLEncoder.encode(databaseName, "UTF-8");
739  } catch (UnsupportedEncodingException ex) {
740  // Print the warning and continue with the unencoded name
741  logger.log(Level.WARNING, "Error encoding database name " + databaseName, ex);
742  encodedDbName = databaseName;
743  }
744 
745  StringBuilder url = new StringBuilder();
746  url.append(JDBC_BASE_URI)
747  .append(info.getHost())
748  .append(":")
749  .append(info.getPort())
750  .append('/') // NON-NLS
751  .append(encodedDbName);
752 
753  if (info.isSslEnabled()) {
754  if (info.isSslVerify()) {
755  if (info.getCustomSslValidationClassName().isBlank()) {
756  url.append(SSL_VERIFY_DEFAULT_URL);
757  } else {
758  // use custom SSL certificate validation class
759  url.append(getCustomPostrgesSslVerificationUrl(info.getCustomSslValidationClassName()));
760  }
761  } else {
762  url.append(SSL_NONVERIFY_URL);
763  }
764  }
765 
766  Connection conn;
767  try {
768  Properties props = new Properties();
769  props.setProperty("user", info.getUserName()); // NON-NLS
770  props.setProperty("password", info.getPassword()); // NON-NLS
771 
772  Class.forName(JDBC_DRIVER);
773  conn = DriverManager.getConnection(url.toString(), props);
774  } catch (ClassNotFoundException | SQLException ex) {
775  throw new TskCoreException("Failed to acquire ephemeral connection to PostgreSQL database " + databaseName, ex); // NON-NLS
776  }
777  return conn;
778  }
779 
780  @Override
781  void performPreInitialization(Connection conn) throws TskCoreException {
782  // Nothing to do here for PostgreSQL
783  }
784 
785  @Override
786  void performPostTableInitialization(Connection conn) throws TskCoreException {
787  try (Statement stmt = conn.createStatement()) {
788  stmt.execute("ALTER SEQUENCE blackboard_artifacts_artifact_id_seq minvalue -9223372036854775808 restart with -9223372036854775808");
789 
790  // CT-9000: Postgres supports composite and partial indexes which results in smaller indexes and faster inserts.
791  // So in Postgres we can have an index which indexes only tsk_files with non-null MD5 and non-zero size:
792  stmt.execute("CREATE INDEX tsk_files_datasrc_md5_size_partial_index ON tsk_files(data_source_obj_id, md5, size) WHERE md5 IS NOT NULL AND size > 0");
793  } catch (SQLException ex) {
794  throw new TskCoreException("Error performing PostgreSQL post table initialization", ex);
795  }
796  }
797  }
798 
802  private class SQLiteDbCreationHelper extends DbCreationHelper {
803 
804  private final static String PRAGMA_SYNC_OFF = "PRAGMA synchronous = OFF"; // NON-NLS
805  private final static String PRAGMA_READ_UNCOMMITTED_TRUE = "PRAGMA read_uncommitted = True"; // NON-NLS
806  private final static String PRAGMA_ENCODING_UTF8 = "PRAGMA encoding = 'UTF-8'"; // NON-NLS
807  private final static String PRAGMA_PAGE_SIZE_4096 = "PRAGMA page_size = 4096"; // NON-NLS
808  private final static String PRAGMA_FOREIGN_KEYS_ON = "PRAGMA foreign_keys = ON"; // NON-NLS
809 
810  private final static String JDBC_DRIVER = "org.sqlite.JDBC"; // NON-NLS
811  private final static String JDBC_BASE_URI = "jdbc:sqlite:"; // NON-NLS
812 
813  String dbPath;
814 
815  SQLiteDbCreationHelper(String dbPath) {
816  this.dbPath = dbPath;
817  }
818 
819  @Override
820  void createDatabase() throws TskCoreException {
821  // SQLite doesn't need to explicitly create the case database but we will
822  // check that the folder exists and the database does not
823  File dbFile = new File(dbPath);
824  if (dbFile.exists()) {
825  throw new TskCoreException("Case database already exists : " + dbPath);
826  }
827 
828  if (dbFile.getParentFile() != null && !dbFile.getParentFile().exists()) {
829  throw new TskCoreException("Case database folder does not exist : " + dbFile.getParent());
830  }
831  }
832 
833  @Override
834  Connection getConnection() throws TskCoreException {
835 
836  StringBuilder url = new StringBuilder();
837  url.append(JDBC_BASE_URI)
838  .append(dbPath);
839 
840  Connection conn;
841  try {
842  Class.forName(JDBC_DRIVER);
843  conn = DriverManager.getConnection(url.toString());
844  } catch (ClassNotFoundException | SQLException ex) {
845  throw new TskCoreException("Failed to acquire ephemeral connection SQLite database " + dbPath, ex); // NON-NLS
846  }
847  return conn;
848  }
849 
850  @Override
851  void performPreInitialization(Connection conn) throws TskCoreException {
852  try (Statement stmt = conn.createStatement()) {
853  stmt.execute(PRAGMA_SYNC_OFF);
854  stmt.execute(PRAGMA_READ_UNCOMMITTED_TRUE);
855  stmt.execute(PRAGMA_ENCODING_UTF8);
856  stmt.execute(PRAGMA_PAGE_SIZE_4096);
857  stmt.execute(PRAGMA_FOREIGN_KEYS_ON);
858  } catch (SQLException ex) {
859  throw new TskCoreException("Error setting pragmas", ex);
860  }
861  }
862 
863  @Override
864  void performPostTableInitialization(Connection conn) throws TskCoreException {
865  try (Statement stmt = conn.createStatement()) {
866  // CT-9000: SQLite supports composite indexes but has only limited support for partial indexes
867  // (partial indexes in SQLite do not support IS NOT NULL as a condition):
868  stmt.execute("CREATE INDEX tsk_files_datasrc_md5_size_index ON tsk_files(data_source_obj_id, md5, size)");
869  } catch (SQLException ex) {
870  throw new TskCoreException("Error performing SQLite post table initialization", ex);
871  }
872  }
873  }
874 }

Copyright © 2011-2024 Brian Carrier. (carrier -at- sleuthkit -dot- org)
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.