Sleuth Kit Java Bindings (JNI)  4.10.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 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 
48  CaseDatabaseFactory(String dbPath) {
49  this.dbQueryHelper = new SQLiteHelper();
50  this.dbCreationHelper = new SQLiteDbCreationHelper(dbPath);
51  }
52 
63  CaseDatabaseFactory(String caseName, CaseDbConnectionInfo info) {
64  this.dbQueryHelper = new PostgreSQLHelper();
65  this.dbCreationHelper = new PostgreSQLDbCreationHelper(caseName, info);
66  }
67 
74  void createCaseDatabase() throws TskCoreException {
75  createDatabase();
76  initializeSchema();
77  }
78 
84  private void createDatabase() throws TskCoreException {
85  dbCreationHelper.createDatabase();
86  }
87 
93  private void initializeSchema() throws TskCoreException {
94  try (Connection conn = dbCreationHelper.getConnection()) {
95  // Perform any needed steps before creating the tables
96  dbCreationHelper.performPreInitialization(conn);
97 
98  // Add schema version
99  addDbInfo(conn);
100 
101  // Add tables
102  addTables(conn);
103  dbCreationHelper.performPostTableInitialization(conn);
104 
105  // Add indexes
106  addIndexes(conn);
107  } catch (SQLException ex) {
108  throw new TskCoreException("Error initializing case database", ex);
109  }
110  }
111 
119  private void addDbInfo(Connection conn) throws TskCoreException {
120  CaseDbSchemaVersionNumber version = SleuthkitCase.CURRENT_DB_SCHEMA_VERSION;
121  long tskVersionNum = SleuthkitJNI.getSleuthkitVersion(); // This is the current version of TSK
122 
123  try (Statement stmt = conn.createStatement()) {
124  stmt.execute("CREATE TABLE tsk_db_info (schema_ver INTEGER, tsk_ver INTEGER, schema_minor_ver INTEGER)");
125  stmt.execute("INSERT INTO tsk_db_info (schema_ver, tsk_ver, schema_minor_ver) VALUES (" +
126  version.getMajor() + ", " + tskVersionNum + ", " + version.getMinor() + ");");
127 
128  stmt.execute("CREATE TABLE tsk_db_info_extended (name TEXT PRIMARY KEY, value TEXT NOT NULL);");
129  stmt.execute("INSERT INTO tsk_db_info_extended (name, value) VALUES ('TSK_VERSION', '" + tskVersionNum + "');");
130  stmt.execute("INSERT INTO tsk_db_info_extended (name, value) VALUES ('SCHEMA_MAJOR_VERSION', '" + version.getMajor() + "');");
131  stmt.execute("INSERT INTO tsk_db_info_extended (name, value) VALUES ('SCHEMA_MINOR_VERSION', '" + version.getMinor() + "');");
132  stmt.execute("INSERT INTO tsk_db_info_extended (name, value) VALUES ('CREATION_SCHEMA_MAJOR_VERSION', '" + version.getMajor() + "');");
133  stmt.execute("INSERT INTO tsk_db_info_extended (name, value) VALUES ('CREATION_SCHEMA_MINOR_VERSION', '" + version.getMinor() + "');");
134  } catch (SQLException ex) {
135  throw new TskCoreException("Error initializing db_info tables", ex);
136  }
137  }
138 
146  private void addTables(Connection conn) throws TskCoreException {
147  try (Statement stmt = conn.createStatement()) {
148  createFileTables(stmt);
149  createArtifactTables(stmt);
150  createTagTables(stmt);
151  createIngestTables(stmt);
152  createAccountTables(stmt);
153  createEventTables(stmt);
154  } catch (SQLException ex) {
155  throw new TskCoreException("Error initializing tables", ex);
156  }
157  }
158 
159  private void createFileTables(Statement stmt) throws SQLException {
160  // The UNIQUE here on the object ID is to create an index
161  stmt.execute("CREATE TABLE tsk_objects (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, par_obj_id " + dbQueryHelper.getBigIntType()
162  + ", type INTEGER NOT NULL, UNIQUE (obj_id), FOREIGN KEY (par_obj_id) REFERENCES tsk_objects (obj_id) ON DELETE CASCADE)");
163 
164  stmt.execute("CREATE TABLE tsk_image_info (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, type INTEGER, ssize INTEGER, "
165  + "tzone TEXT, size " + dbQueryHelper.getBigIntType() + ", md5 TEXT, sha1 TEXT, sha256 TEXT, display_name TEXT, "
166  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
167 
168  stmt.execute("CREATE TABLE tsk_image_names (obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, name TEXT NOT NULL, "
169  + "sequence INTEGER NOT NULL, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
170 
171  stmt.execute("CREATE TABLE tsk_vs_info (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, vs_type INTEGER NOT NULL, "
172  + "img_offset " + dbQueryHelper.getBigIntType() + " NOT NULL, block_size " + dbQueryHelper.getBigIntType() + " NOT NULL, "
173  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
174 
175  stmt.execute("CREATE TABLE tsk_vs_parts (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
176  + "addr " + dbQueryHelper.getBigIntType() + " NOT NULL, start " + dbQueryHelper.getBigIntType() + " NOT NULL, "
177  + "length " + dbQueryHelper.getBigIntType() + " NOT NULL, "
178  + dbQueryHelper.getVSDescColName() + " TEXT, "
179  + "flags INTEGER NOT NULL, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE);");
180 
181  stmt.execute("CREATE TABLE tsk_pool_info (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
182  + "pool_type INTEGER NOT NULL, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE);");
183 
184  stmt.execute("CREATE TABLE data_source_info (obj_id " + dbQueryHelper.getBigIntType() + " PRIMARY KEY, device_id TEXT NOT NULL, "
185  + "time_zone TEXT NOT NULL, acquisition_details TEXT, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
186 
187  stmt.execute("CREATE TABLE tsk_fs_info (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
188  + "data_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
189  + "img_offset " + dbQueryHelper.getBigIntType() + " NOT NULL, fs_type INTEGER NOT NULL, "
190  + "block_size " + dbQueryHelper.getBigIntType() + " NOT NULL, "
191  + "block_count " + dbQueryHelper.getBigIntType() + " NOT NULL, root_inum " + dbQueryHelper.getBigIntType() + " NOT NULL, "
192  + "first_inum " + dbQueryHelper.getBigIntType() + " NOT NULL, last_inum " + dbQueryHelper.getBigIntType() + " NOT NULL, "
193  + "display_name TEXT, "
194  + "FOREIGN KEY(data_source_obj_id) REFERENCES data_source_info(obj_id) ON DELETE CASCADE, "
195  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
196 
197  stmt.execute("CREATE TABLE tsk_files (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
198  + "fs_obj_id " + dbQueryHelper.getBigIntType() + ", data_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
199  + "attr_type INTEGER, attr_id INTEGER, "
200  + "name TEXT NOT NULL, meta_addr " + dbQueryHelper.getBigIntType() + ", meta_seq " + dbQueryHelper.getBigIntType() + ", "
201  + "type INTEGER, has_layout INTEGER, has_path INTEGER, "
202  + "dir_type INTEGER, meta_type INTEGER, dir_flags INTEGER, meta_flags INTEGER, size " + dbQueryHelper.getBigIntType() + ", "
203  + "ctime " + dbQueryHelper.getBigIntType() + ", "
204  + "crtime " + dbQueryHelper.getBigIntType() + ", atime " + dbQueryHelper.getBigIntType() + ", "
205  + "mtime " + dbQueryHelper.getBigIntType() + ", mode INTEGER, uid INTEGER, gid INTEGER, md5 TEXT, sha256 TEXT, "
206  + "known INTEGER, "
207  + "parent_path TEXT, mime_type TEXT, extension TEXT, "
208  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
209  + "FOREIGN KEY(fs_obj_id) REFERENCES tsk_fs_info(obj_id) ON DELETE CASCADE, "
210  + "FOREIGN KEY(data_source_obj_id) REFERENCES data_source_info(obj_id) ON DELETE CASCADE)");
211 
212  stmt.execute("CREATE TABLE file_encoding_types (encoding_type INTEGER PRIMARY KEY, name TEXT NOT NULL)");
213 
214  stmt.execute("CREATE TABLE tsk_files_path (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, path TEXT NOT NULL, "
215  + "encoding_type INTEGER NOT NULL, FOREIGN KEY(encoding_type) references file_encoding_types(encoding_type), "
216  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
217 
218  stmt.execute("CREATE TABLE tsk_files_derived (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
219  + "derived_id " + dbQueryHelper.getBigIntType() + " NOT NULL, rederive TEXT, "
220  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
221 
222  stmt.execute("CREATE TABLE tsk_files_derived_method (derived_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
223  + "tool_name TEXT NOT NULL, tool_version TEXT NOT NULL, other TEXT)");
224 
225  stmt.execute("CREATE TABLE tsk_file_layout (obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
226  + "byte_start " + dbQueryHelper.getBigIntType() + " NOT NULL, byte_len " + dbQueryHelper.getBigIntType() + " NOT NULL, "
227  + "sequence INTEGER NOT NULL, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE);");
228 
229  stmt.execute("CREATE TABLE reports (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, path TEXT NOT NULL, "
230  + "crtime INTEGER NOT NULL, src_module_name TEXT NOT NULL, report_name TEXT NOT NULL, "
231  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE);");
232  }
233 
234  private void createArtifactTables(Statement stmt) throws SQLException {
235  stmt.execute("CREATE TABLE blackboard_artifact_types (artifact_type_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
236  + "type_name TEXT NOT NULL, display_name TEXT)");
237 
238  stmt.execute("CREATE TABLE blackboard_attribute_types (attribute_type_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
239  + "type_name TEXT NOT NULL, display_name TEXT, value_type INTEGER NOT NULL)");
240 
241  stmt.execute("CREATE TABLE review_statuses (review_status_id INTEGER PRIMARY KEY, "
242  + "review_status_name TEXT NOT NULL, "
243  + "display_name TEXT NOT NULL)");
244 
245  stmt.execute("CREATE TABLE blackboard_artifacts (artifact_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
246  + "obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
247  + "artifact_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
248  + "data_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
249  + "artifact_type_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
250  + "review_status_id INTEGER NOT NULL, "
251  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
252  + "FOREIGN KEY(artifact_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
253  + "FOREIGN KEY(data_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
254  + "FOREIGN KEY(artifact_type_id) REFERENCES blackboard_artifact_types(artifact_type_id), "
255  + "FOREIGN KEY(review_status_id) REFERENCES review_statuses(review_status_id))");
256 
257  /* Binary representation of BYTEA is a bunch of bytes, which could
258  * include embedded nulls so we have to pay attention to field length.
259  * http://www.postgresql.org/docs/9.4/static/libpq-example.html
260  */
261  stmt.execute("CREATE TABLE blackboard_attributes (artifact_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
262  + "artifact_type_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
263  + "source TEXT, context TEXT, attribute_type_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
264  + "value_type INTEGER NOT NULL, value_byte " + dbQueryHelper.getBlobType() + ", "
265  + "value_text TEXT, value_int32 INTEGER, value_int64 " + dbQueryHelper.getBigIntType() + ", value_double NUMERIC(20, 10), "
266  + "FOREIGN KEY(artifact_id) REFERENCES blackboard_artifacts(artifact_id) ON DELETE CASCADE, "
267  + "FOREIGN KEY(artifact_type_id) REFERENCES blackboard_artifact_types(artifact_type_id), "
268  + "FOREIGN KEY(attribute_type_id) REFERENCES blackboard_attribute_types(attribute_type_id))");
269  }
270 
271  private void createTagTables(Statement stmt) throws SQLException {
272  stmt.execute("CREATE TABLE tsk_tag_sets (tag_set_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, name TEXT UNIQUE)");
273  stmt.execute("CREATE TABLE tag_names (tag_name_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, display_name TEXT UNIQUE, "
274  + "description TEXT NOT NULL, color TEXT NOT NULL, knownStatus INTEGER NOT NULL,"
275  + " tag_set_id " + dbQueryHelper.getBigIntType() + ", rank INTEGER, FOREIGN KEY(tag_set_id) REFERENCES tsk_tag_sets(tag_set_id) ON DELETE SET NULL)");
276 
277  stmt.execute("CREATE TABLE tsk_examiners (examiner_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
278  + "login_name TEXT NOT NULL, display_name TEXT, UNIQUE(login_name))");
279 
280  stmt.execute("CREATE TABLE content_tags (tag_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
281  + "obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, tag_name_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
282  + "comment TEXT NOT NULL, begin_byte_offset " + dbQueryHelper.getBigIntType() + " NOT NULL, "
283  + "end_byte_offset " + dbQueryHelper.getBigIntType() + " NOT NULL, "
284  + "examiner_id " + dbQueryHelper.getBigIntType() + ", "
285  + "FOREIGN KEY(examiner_id) REFERENCES tsk_examiners(examiner_id) ON DELETE CASCADE, "
286  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
287  + "FOREIGN KEY(tag_name_id) REFERENCES tag_names(tag_name_id) ON DELETE CASCADE)");
288 
289  stmt.execute("CREATE TABLE blackboard_artifact_tags (tag_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
290  + "artifact_id " + dbQueryHelper.getBigIntType() + " NOT NULL, tag_name_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
291  + "comment TEXT NOT NULL, examiner_id " + dbQueryHelper.getBigIntType() + ", "
292  + "FOREIGN KEY(examiner_id) REFERENCES tsk_examiners(examiner_id) ON DELETE CASCADE, "
293  + "FOREIGN KEY(artifact_id) REFERENCES blackboard_artifacts(artifact_id) ON DELETE CASCADE, "
294  + "FOREIGN KEY(tag_name_id) REFERENCES tag_names(tag_name_id) ON DELETE CASCADE)");
295  }
296 
303  private void addIndexes(Connection conn) throws TskCoreException {
304  try (Statement stmt = conn.createStatement()) {
305  // tsk_objects index
306  stmt.execute("CREATE INDEX parObjId ON tsk_objects(par_obj_id)");
307 
308  // file layout index
309  stmt.execute("CREATE INDEX layout_objID ON tsk_file_layout(obj_id)");
310 
311  // blackboard indexes
312  stmt.execute("CREATE INDEX artifact_objID ON blackboard_artifacts(obj_id)");
313  stmt.execute("CREATE INDEX artifact_artifact_objID ON blackboard_artifacts(artifact_obj_id)");
314  stmt.execute("CREATE INDEX artifact_typeID ON blackboard_artifacts(artifact_type_id)");
315  stmt.execute("CREATE INDEX attrsArtifactID ON blackboard_attributes(artifact_id)");
316 
317  //file type indexes
318  stmt.execute("CREATE INDEX mime_type ON tsk_files(dir_type,mime_type,type)");
319  stmt.execute("CREATE INDEX file_extension ON tsk_files(extension)");
320 
321  // account indexes
322  stmt.execute("CREATE INDEX relationships_account1 ON account_relationships(account1_id)");
323  stmt.execute("CREATE INDEX relationships_account2 ON account_relationships(account2_id)");
324  stmt.execute("CREATE INDEX relationships_relationship_source_obj_id ON account_relationships(relationship_source_obj_id)");
325  stmt.execute("CREATE INDEX relationships_date_time ON account_relationships(date_time)");
326  stmt.execute("CREATE INDEX relationships_relationship_type ON account_relationships(relationship_type)");
327  stmt.execute("CREATE INDEX relationships_data_source_obj_id ON account_relationships(data_source_obj_id)");
328 
329  //tsk_events indices
330  stmt.execute("CREATE INDEX events_data_source_obj_id ON tsk_event_descriptions(data_source_obj_id)");
331  stmt.execute("CREATE INDEX events_content_obj_id ON tsk_event_descriptions(content_obj_id)");
332  stmt.execute("CREATE INDEX events_artifact_id ON tsk_event_descriptions(artifact_id)");
333  stmt.execute("CREATE INDEX events_sub_type_time ON tsk_events(event_type_id, time)");
334  stmt.execute("CREATE INDEX events_time ON tsk_events(time)");
335  } catch (SQLException ex) {
336  throw new TskCoreException("Error initializing db_info tables", ex);
337  }
338  }
339 
340  private void createIngestTables(Statement stmt) throws SQLException {
341  stmt.execute("CREATE TABLE ingest_module_types (type_id INTEGER PRIMARY KEY, type_name TEXT NOT NULL)");
342 
343  stmt.execute("CREATE TABLE ingest_job_status_types (type_id INTEGER PRIMARY KEY, type_name TEXT NOT NULL)");
344 
345  stmt.execute("CREATE TABLE ingest_modules (ingest_module_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
346  + "display_name TEXT NOT NULL, unique_name TEXT UNIQUE NOT NULL, type_id INTEGER NOT NULL, "
347  + "version TEXT NOT NULL, FOREIGN KEY(type_id) REFERENCES ingest_module_types(type_id) ON DELETE CASCADE);");
348 
349  stmt.execute("CREATE TABLE ingest_jobs (ingest_job_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
350  + "obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, host_name TEXT NOT NULL, "
351  + "start_date_time " + dbQueryHelper.getBigIntType() + " NOT NULL, "
352  + "end_date_time " + dbQueryHelper.getBigIntType() + " NOT NULL, status_id INTEGER NOT NULL, "
353  + "settings_dir TEXT, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
354  + "FOREIGN KEY(status_id) REFERENCES ingest_job_status_types(type_id) ON DELETE CASCADE);");
355 
356  stmt.execute("CREATE TABLE ingest_job_modules (ingest_job_id INTEGER, ingest_module_id INTEGER, "
357  + "pipeline_position INTEGER, PRIMARY KEY(ingest_job_id, ingest_module_id), "
358  + "FOREIGN KEY(ingest_job_id) REFERENCES ingest_jobs(ingest_job_id) ON DELETE CASCADE, "
359  + "FOREIGN KEY(ingest_module_id) REFERENCES ingest_modules(ingest_module_id) ON DELETE CASCADE);");
360  }
361 
362  private void createAccountTables(Statement stmt) throws SQLException {
363  stmt.execute("CREATE TABLE account_types (account_type_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
364  + "type_name TEXT UNIQUE NOT NULL, display_name TEXT NOT NULL)");
365 
366  stmt.execute("CREATE TABLE accounts (account_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
367  + "account_type_id INTEGER NOT NULL, account_unique_identifier TEXT NOT NULL, "
368  + "UNIQUE(account_type_id, account_unique_identifier), "
369  + "FOREIGN KEY(account_type_id) REFERENCES account_types(account_type_id))");
370 
371  stmt.execute("CREATE TABLE account_relationships (relationship_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
372  + "account1_id INTEGER NOT NULL, account2_id INTEGER NOT NULL, "
373  + "relationship_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
374  + "date_time " + dbQueryHelper.getBigIntType() + ", relationship_type INTEGER NOT NULL, "
375  + "data_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
376  + "UNIQUE(account1_id, account2_id, relationship_source_obj_id), "
377  + "FOREIGN KEY(account1_id) REFERENCES accounts(account_id), "
378  + "FOREIGN KEY(account2_id) REFERENCES accounts(account_id), "
379  + "FOREIGN KEY(relationship_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
380  + "FOREIGN KEY(data_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
381  }
382 
383  private void createEventTables(Statement stmt) throws SQLException {
384  stmt.execute("CREATE TABLE tsk_event_types ("
385  + " event_type_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY,"
386  + " display_name TEXT UNIQUE NOT NULL , "
387  + " super_type_id INTEGER REFERENCES tsk_event_types(event_type_id) )");
388 
389  stmt.execute("INSERT INTO tsk_event_types(event_type_id, display_name, super_type_id) VALUES(0, 'Event Types', null)");
390  stmt.execute("INSERT INTO tsk_event_types(event_type_id, display_name, super_type_id) VALUES(1, 'File System', 0)");
391  stmt.execute("INSERT INTO tsk_event_types(event_type_id, display_name, super_type_id) VALUES(2, 'Web Activity', 0)");
392  stmt.execute("INSERT INTO tsk_event_types(event_type_id, display_name, super_type_id) VALUES(3, 'Misc Types', 0)");
393  stmt.execute("INSERT INTO tsk_event_types(event_type_id, display_name, super_type_id) VALUES(4, 'Modified', 1)");
394  stmt.execute("INSERT INTO tsk_event_types(event_type_id, display_name, super_type_id) VALUES(5, 'Accessed', 1)");
395  stmt.execute("INSERT INTO tsk_event_types(event_type_id, display_name, super_type_id) VALUES(6, 'Created', 1)");
396  stmt.execute("INSERT INTO tsk_event_types(event_type_id, display_name, super_type_id) VALUES(7, 'Changed', 1)");
397  /*
398  * Regarding the timeline event tables schema, note that several columns
399  * in the tsk_event_descriptions table seem, at first glance, to be
400  * attributes of events rather than their descriptions and would appear
401  * to belong in tsk_events table instead. The rationale for putting the
402  * data source object ID, content object ID, artifact ID and the flags
403  * indicating whether or not the event source has a hash set hit or is
404  * tagged were motivated by the fact that these attributes are identical
405  * for each event in a set of file system file MAC time events. The
406  * decision was made to avoid duplication and save space by placing this
407  * data in the tsk_event-descriptions table.
408  */
409  stmt.execute(
410  "CREATE TABLE tsk_event_descriptions ( "
411  + " event_description_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
412  + " full_description TEXT NOT NULL, "
413  + " med_description TEXT, "
414  + " short_description TEXT,"
415  + " data_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
416  + " content_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
417  + " artifact_id " + dbQueryHelper.getBigIntType() + ", "
418  + " hash_hit INTEGER NOT NULL, " //boolean
419  + " tagged INTEGER NOT NULL, " //boolean
420  + " FOREIGN KEY(data_source_obj_id) REFERENCES data_source_info(obj_id) ON DELETE CASCADE, "
421  + " FOREIGN KEY(content_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
422  + " FOREIGN KEY(artifact_id) REFERENCES blackboard_artifacts(artifact_id) ON DELETE CASCADE,"
423  + " UNIQUE (full_description, content_obj_id, artifact_id))");
424 
425  stmt.execute(
426  "CREATE TABLE tsk_events ("
427  + " event_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
428  + " event_type_id " + dbQueryHelper.getBigIntType() + " NOT NULL REFERENCES tsk_event_types(event_type_id) ,"
429  + " event_description_id " + dbQueryHelper.getBigIntType() + " NOT NULL REFERENCES tsk_event_descriptions(event_description_id) ON DELETE CASCADE ,"
430  + " time " + dbQueryHelper.getBigIntType() + " NOT NULL , "
431  + " UNIQUE (event_type_id, event_description_id, time))");
432  }
433 
437  private abstract class DbCreationHelper {
438 
444  abstract void createDatabase() throws TskCoreException;
445 
451  abstract Connection getConnection() throws TskCoreException;
452 
461  abstract void performPreInitialization(Connection conn) throws TskCoreException;
462 
469  abstract void performPostTableInitialization(Connection conn) throws TskCoreException;
470  }
471 
475  private class PostgreSQLDbCreationHelper extends DbCreationHelper {
476 
477  private final static String JDBC_BASE_URI = "jdbc:postgresql://"; // NON-NLS
478  private final static String JDBC_DRIVER = "org.postgresql.Driver"; // NON-NLS
479 
480  final private String caseName;
481  final private CaseDbConnectionInfo info;
482 
483  PostgreSQLDbCreationHelper(String caseName, CaseDbConnectionInfo info) {
484  this.caseName = caseName;
485  this.info = info;
486  }
487 
488  @Override
489  void createDatabase() throws TskCoreException{
490  try(Connection conn = getPostgresConnection();
491  Statement stmt = conn.createStatement()) {
492  stmt.execute("CREATE DATABASE \"" + caseName + "\" WITH ENCODING='UTF8'");
493  } catch (SQLException ex) {
494  throw new TskCoreException("Error creating PostgreSQL case " + caseName, ex);
495  }
496  }
497 
498  @Override
499  Connection getConnection() throws TskCoreException {
500  return getConnection(caseName);
501  }
502 
508  Connection getPostgresConnection() throws TskCoreException {
509  return getConnection("postgres");
510  }
511 
519  Connection getConnection(String databaseName) throws TskCoreException {
520  String encodedDbName;
521  try {
522  encodedDbName = URLEncoder.encode(databaseName, "UTF-8");
523  } catch (UnsupportedEncodingException ex) {
524  // Print the warning and continue with the unencoded name
525  logger.log(Level.WARNING, "Error encoding database name " + databaseName, ex);
526  encodedDbName = databaseName;
527  }
528 
529  StringBuilder url = new StringBuilder();
530  url.append(JDBC_BASE_URI)
531  .append(info.getHost())
532  .append('/') // NON-NLS
533  .append(encodedDbName);
534 
535  Connection conn;
536  try {
537  Properties props = new Properties();
538  props.setProperty("user", info.getUserName()); // NON-NLS
539  props.setProperty("password", info.getPassword()); // NON-NLS
540 
541  Class.forName(JDBC_DRIVER);
542  conn = DriverManager.getConnection(url.toString(), props);
543  } catch (ClassNotFoundException | SQLException ex) {
544  throw new TskCoreException("Failed to acquire ephemeral connection to PostgreSQL database " + databaseName, ex); // NON-NLS
545  }
546  return conn;
547  }
548 
549  @Override
550  void performPreInitialization(Connection conn) throws TskCoreException {
551  // Nothing to do here for PostgreSQL
552  }
553 
554  @Override
555  void performPostTableInitialization(Connection conn) throws TskCoreException {
556  try (Statement stmt = conn.createStatement()) {
557  stmt.execute("ALTER SEQUENCE blackboard_artifacts_artifact_id_seq minvalue -9223372036854775808 restart with -9223372036854775808");
558  } catch (SQLException ex) {
559  throw new TskCoreException("Error altering artifact ID sequence", ex);
560  }
561  }
562  }
563 
567  private class SQLiteDbCreationHelper extends DbCreationHelper {
568 
569  private final static String PRAGMA_SYNC_OFF = "PRAGMA synchronous = OFF"; // NON-NLS
570  private final static String PRAGMA_READ_UNCOMMITTED_TRUE = "PRAGMA read_uncommitted = True"; // NON-NLS
571  private final static String PRAGMA_ENCODING_UTF8 = "PRAGMA encoding = 'UTF-8'"; // NON-NLS
572  private final static String PRAGMA_PAGE_SIZE_4096 = "PRAGMA page_size = 4096"; // NON-NLS
573  private final static String PRAGMA_FOREIGN_KEYS_ON = "PRAGMA foreign_keys = ON"; // NON-NLS
574 
575  private final static String JDBC_DRIVER = "org.sqlite.JDBC"; // NON-NLS
576  private final static String JDBC_BASE_URI = "jdbc:sqlite:"; // NON-NLS
577 
578  String dbPath;
579 
580  SQLiteDbCreationHelper(String dbPath) {
581  this.dbPath = dbPath;
582  }
583 
584  @Override
585  void createDatabase() throws TskCoreException {
586  // SQLite doesn't need to explicitly create the case database but we will
587  // check that the folder exists and the database does not
588  File dbFile = new File(dbPath);
589  if (dbFile.exists()) {
590  throw new TskCoreException("Case database already exists : " + dbPath);
591  }
592 
593  if (dbFile.getParentFile() != null && !dbFile.getParentFile().exists()) {
594  throw new TskCoreException("Case database folder does not exist : " + dbFile.getParent());
595  }
596  }
597 
598  @Override
599  Connection getConnection() throws TskCoreException {
600 
601  StringBuilder url = new StringBuilder();
602  url.append(JDBC_BASE_URI)
603  .append(dbPath);
604 
605  Connection conn;
606  try {
607  Class.forName(JDBC_DRIVER);
608  conn = DriverManager.getConnection(url.toString());
609  } catch (ClassNotFoundException | SQLException ex) {
610  throw new TskCoreException("Failed to acquire ephemeral connection SQLite database " + dbPath, ex); // NON-NLS
611  }
612  return conn;
613  }
614 
615  @Override
616  void performPreInitialization(Connection conn) throws TskCoreException {
617  try (Statement stmt = conn.createStatement()) {
618  stmt.execute(PRAGMA_SYNC_OFF);
619  stmt.execute(PRAGMA_READ_UNCOMMITTED_TRUE);
620  stmt.execute(PRAGMA_ENCODING_UTF8);
621  stmt.execute(PRAGMA_PAGE_SIZE_4096);
622  stmt.execute(PRAGMA_FOREIGN_KEYS_ON);
623  } catch (SQLException ex) {
624  throw new TskCoreException("Error setting pragmas", ex);
625  }
626  }
627 
628  @Override
629  void performPostTableInitialization(Connection conn) throws TskCoreException {
630  // Nothing to do here for SQLite
631  }
632  }
633 }

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