Sleuth Kit Java Bindings (JNI)  4.11.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 
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  createTskObjects(stmt);
149  createHostTables(stmt);
150  createAccountTables(stmt);
151  createFileTables(stmt);
152  createArtifactTables(stmt);
153  createAnalysisResultsTables(stmt);
154  createTagTables(stmt);
155  createIngestTables(stmt);
156  createEventTables(stmt);
157  createAttributeTables(stmt);
158  createAccountInstancesAndArtifacts(stmt);
159  } catch (SQLException ex) {
160  throw new TskCoreException("Error initializing tables", ex);
161  }
162  }
163 
164  // tsk_objects is referenced by many other tables and should be created first
165  private void createTskObjects(Statement stmt) throws SQLException {
166  // The UNIQUE here on the object ID is to create an index
167  stmt.execute("CREATE TABLE tsk_objects (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, par_obj_id " + dbQueryHelper.getBigIntType()
168  + ", type INTEGER NOT NULL, UNIQUE (obj_id), FOREIGN KEY (par_obj_id) REFERENCES tsk_objects (obj_id) ON DELETE CASCADE)");
169  }
170 
171  private void createFileTables(Statement stmt) throws SQLException {
172 
173  stmt.execute("CREATE TABLE tsk_image_info (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, type INTEGER, ssize INTEGER, "
174  + "tzone TEXT, size " + dbQueryHelper.getBigIntType() + ", md5 TEXT, sha1 TEXT, sha256 TEXT, display_name TEXT, "
175  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
176 
177  stmt.execute("CREATE TABLE tsk_image_names (obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, name TEXT NOT NULL, "
178  + "sequence INTEGER NOT NULL, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
179 
180  stmt.execute("CREATE TABLE tsk_vs_info (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, vs_type INTEGER NOT NULL, "
181  + "img_offset " + dbQueryHelper.getBigIntType() + " NOT NULL, block_size " + dbQueryHelper.getBigIntType() + " NOT NULL, "
182  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
183 
184  stmt.execute("CREATE TABLE tsk_vs_parts (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
185  + "addr " + dbQueryHelper.getBigIntType() + " NOT NULL, start " + dbQueryHelper.getBigIntType() + " NOT NULL, "
186  + "length " + dbQueryHelper.getBigIntType() + " NOT NULL, "
187  + dbQueryHelper.getVSDescColName() + " TEXT, "
188  + "flags INTEGER NOT NULL, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE);");
189 
190  stmt.execute("CREATE TABLE tsk_pool_info (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
191  + "pool_type INTEGER NOT NULL, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE);");
192 
193  stmt.execute("CREATE TABLE data_source_info (obj_id " + dbQueryHelper.getBigIntType() + " PRIMARY KEY, device_id TEXT NOT NULL, "
194  + "time_zone TEXT NOT NULL, acquisition_details TEXT, added_date_time "+ dbQueryHelper.getBigIntType() + ", "
195  + "acquisition_tool_settings TEXT, acquisition_tool_name TEXT, acquisition_tool_version TEXT, "
196  + "host_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
197  + "FOREIGN KEY(host_id) REFERENCES tsk_hosts(id), "
198  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
199 
200  stmt.execute("CREATE TABLE tsk_fs_info (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
201  + "data_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
202  + "img_offset " + dbQueryHelper.getBigIntType() + " NOT NULL, fs_type INTEGER NOT NULL, "
203  + "block_size " + dbQueryHelper.getBigIntType() + " NOT NULL, "
204  + "block_count " + dbQueryHelper.getBigIntType() + " NOT NULL, root_inum " + dbQueryHelper.getBigIntType() + " NOT NULL, "
205  + "first_inum " + dbQueryHelper.getBigIntType() + " NOT NULL, last_inum " + dbQueryHelper.getBigIntType() + " NOT NULL, "
206  + "display_name TEXT, "
207  + "FOREIGN KEY(data_source_obj_id) REFERENCES data_source_info(obj_id) ON DELETE CASCADE, "
208  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
209 
210  stmt.execute("CREATE TABLE tsk_files (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
211  + "fs_obj_id " + dbQueryHelper.getBigIntType() + ", data_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
212  + "attr_type INTEGER, attr_id INTEGER, "
213  + "name TEXT NOT NULL, meta_addr " + dbQueryHelper.getBigIntType() + ", meta_seq " + dbQueryHelper.getBigIntType() + ", "
214  + "type INTEGER, has_layout INTEGER, has_path INTEGER, "
215  + "dir_type INTEGER, meta_type INTEGER, dir_flags INTEGER, meta_flags INTEGER, size " + dbQueryHelper.getBigIntType() + ", "
216  + "ctime " + dbQueryHelper.getBigIntType() + ", "
217  + "crtime " + dbQueryHelper.getBigIntType() + ", atime " + dbQueryHelper.getBigIntType() + ", "
218  + "mtime " + dbQueryHelper.getBigIntType() + ", mode INTEGER, uid INTEGER, gid INTEGER, md5 TEXT, sha256 TEXT, sha1 TEXT,"
219  + "known INTEGER, "
220  + "parent_path TEXT, mime_type TEXT, extension TEXT, "
221  + "owner_uid TEXT DEFAULT NULL, "
222  + "os_account_obj_id " + dbQueryHelper.getBigIntType() + " DEFAULT NULL, "
223  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
224  + "FOREIGN KEY(fs_obj_id) REFERENCES tsk_fs_info(obj_id) ON DELETE CASCADE, "
225  + "FOREIGN KEY(data_source_obj_id) REFERENCES data_source_info(obj_id) ON DELETE CASCADE, "
226  + "FOREIGN KEY(os_account_obj_id) REFERENCES tsk_os_accounts(os_account_obj_id) ON DELETE SET NULL) " );
227 
228  stmt.execute("CREATE TABLE file_encoding_types (encoding_type INTEGER PRIMARY KEY, name TEXT NOT NULL)");
229 
230  stmt.execute("CREATE TABLE tsk_files_path (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, path TEXT NOT NULL, "
231  + "encoding_type INTEGER NOT NULL, FOREIGN KEY(encoding_type) references file_encoding_types(encoding_type), "
232  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
233 
234  stmt.execute("CREATE TABLE tsk_files_derived (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
235  + "derived_id " + dbQueryHelper.getBigIntType() + " NOT NULL, rederive TEXT, "
236  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
237 
238  stmt.execute("CREATE TABLE tsk_files_derived_method (derived_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
239  + "tool_name TEXT NOT NULL, tool_version TEXT NOT NULL, other TEXT)");
240 
241  stmt.execute("CREATE TABLE tsk_file_layout (obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
242  + "byte_start " + dbQueryHelper.getBigIntType() + " NOT NULL, byte_len " + dbQueryHelper.getBigIntType() + " NOT NULL, "
243  + "sequence INTEGER NOT NULL, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE);");
244 
245  stmt.execute("CREATE TABLE reports (obj_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, path TEXT NOT NULL, "
246  + "crtime INTEGER NOT NULL, src_module_name TEXT NOT NULL, report_name TEXT NOT NULL, "
247  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE);");
248  }
249 
250  private void createArtifactTables(Statement stmt) throws SQLException {
251  stmt.execute("CREATE TABLE blackboard_artifact_types (artifact_type_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
252  + "type_name TEXT NOT NULL, display_name TEXT,"
253  + "category_type INTEGER DEFAULT 0)");
254 
255  stmt.execute("CREATE TABLE blackboard_attribute_types (attribute_type_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
256  + "type_name TEXT NOT NULL, display_name TEXT, value_type INTEGER NOT NULL)");
257 
258  stmt.execute("CREATE TABLE review_statuses (review_status_id INTEGER PRIMARY KEY, "
259  + "review_status_name TEXT NOT NULL, "
260  + "display_name TEXT NOT NULL)");
261 
262  stmt.execute("CREATE TABLE blackboard_artifacts (artifact_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
263  + "obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
264  + "artifact_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
265  + "data_source_obj_id " + dbQueryHelper.getBigIntType() + ", "
266  + "artifact_type_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
267  + "review_status_id INTEGER NOT NULL, "
268  + "UNIQUE (artifact_obj_id),"
269  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
270  + "FOREIGN KEY(artifact_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
271  + "FOREIGN KEY(data_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
272  + "FOREIGN KEY(artifact_type_id) REFERENCES blackboard_artifact_types(artifact_type_id), "
273  + "FOREIGN KEY(review_status_id) REFERENCES review_statuses(review_status_id))");
274 
275  /* Binary representation of BYTEA is a bunch of bytes, which could
276  * include embedded nulls so we have to pay attention to field length.
277  * http://www.postgresql.org/docs/9.4/static/libpq-example.html
278  */
279  stmt.execute("CREATE TABLE blackboard_attributes (artifact_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
280  + "artifact_type_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
281  + "source TEXT, context TEXT, attribute_type_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
282  + "value_type INTEGER NOT NULL, value_byte " + dbQueryHelper.getBlobType() + ", "
283  + "value_text TEXT, value_int32 INTEGER, value_int64 " + dbQueryHelper.getBigIntType() + ", value_double NUMERIC(20, 10), "
284  + "FOREIGN KEY(artifact_id) REFERENCES blackboard_artifacts(artifact_id) ON DELETE CASCADE, "
285  + "FOREIGN KEY(artifact_type_id) REFERENCES blackboard_artifact_types(artifact_type_id), "
286  + "FOREIGN KEY(attribute_type_id) REFERENCES blackboard_attribute_types(attribute_type_id))");
287  }
288 
289  private void createAnalysisResultsTables(Statement stmt) throws SQLException {
290  stmt.execute("CREATE TABLE tsk_analysis_results (artifact_obj_id " + dbQueryHelper.getBigIntType() + " PRIMARY KEY, "
291  + "conclusion TEXT, "
292  + "significance INTEGER NOT NULL, "
293  + "priority INTEGER NOT NULL, "
294  + "configuration TEXT, justification TEXT, "
295  + "ignore_score INTEGER DEFAULT 0, " // boolean
296  + "FOREIGN KEY(artifact_obj_id) REFERENCES blackboard_artifacts(artifact_obj_id) ON DELETE CASCADE"
297  + ")");
298 
299  stmt.execute("CREATE TABLE tsk_aggregate_score( obj_id " + dbQueryHelper.getBigIntType() + " PRIMARY KEY, "
300  + "data_source_obj_id " + dbQueryHelper.getBigIntType() + ", "
301  + "significance INTEGER NOT NULL, "
302  + "priority INTEGER NOT NULL, "
303  + "UNIQUE (obj_id),"
304  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
305  + "FOREIGN KEY(data_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE "
306  + ")");
307 
308  }
309  private void createTagTables(Statement stmt) throws SQLException {
310  stmt.execute("CREATE TABLE tsk_tag_sets (tag_set_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, name TEXT UNIQUE)");
311  stmt.execute("CREATE TABLE tag_names (tag_name_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, display_name TEXT UNIQUE, "
312  + "description TEXT NOT NULL, color TEXT NOT NULL, knownStatus INTEGER NOT NULL,"
313  + " tag_set_id " + dbQueryHelper.getBigIntType() + ", rank INTEGER, FOREIGN KEY(tag_set_id) REFERENCES tsk_tag_sets(tag_set_id) ON DELETE SET NULL)");
314 
315  stmt.execute("CREATE TABLE tsk_examiners (examiner_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
316  + "login_name TEXT NOT NULL, display_name TEXT, UNIQUE(login_name))");
317 
318  stmt.execute("CREATE TABLE content_tags (tag_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
319  + "obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, tag_name_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
320  + "comment TEXT NOT NULL, begin_byte_offset " + dbQueryHelper.getBigIntType() + " NOT NULL, "
321  + "end_byte_offset " + dbQueryHelper.getBigIntType() + " NOT NULL, "
322  + "examiner_id " + dbQueryHelper.getBigIntType() + ", "
323  + "FOREIGN KEY(examiner_id) REFERENCES tsk_examiners(examiner_id) ON DELETE CASCADE, "
324  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
325  + "FOREIGN KEY(tag_name_id) REFERENCES tag_names(tag_name_id) ON DELETE CASCADE)");
326 
327  stmt.execute("CREATE TABLE blackboard_artifact_tags (tag_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
328  + "artifact_id " + dbQueryHelper.getBigIntType() + " NOT NULL, tag_name_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
329  + "comment TEXT NOT NULL, examiner_id " + dbQueryHelper.getBigIntType() + ", "
330  + "FOREIGN KEY(examiner_id) REFERENCES tsk_examiners(examiner_id) ON DELETE CASCADE, "
331  + "FOREIGN KEY(artifact_id) REFERENCES blackboard_artifacts(artifact_id) ON DELETE CASCADE, "
332  + "FOREIGN KEY(tag_name_id) REFERENCES tag_names(tag_name_id) ON DELETE CASCADE)");
333  }
334 
341  private void addIndexes(Connection conn) throws TskCoreException {
342  try (Statement stmt = conn.createStatement()) {
343  // tsk_objects index
344  stmt.execute("CREATE INDEX parObjId ON tsk_objects(par_obj_id)");
345 
346  // file layout index
347  stmt.execute("CREATE INDEX layout_objID ON tsk_file_layout(obj_id)");
348 
349  // blackboard indexes
350  stmt.execute("CREATE INDEX artifact_objID ON blackboard_artifacts(obj_id)");
351  stmt.execute("CREATE INDEX artifact_artifact_objID ON blackboard_artifacts(artifact_obj_id)");
352  stmt.execute("CREATE INDEX artifact_typeID ON blackboard_artifacts(artifact_type_id)");
353  stmt.execute("CREATE INDEX attrsArtifactID ON blackboard_attributes(artifact_id)");
354 
355  //file type indexes
356  stmt.execute("CREATE INDEX mime_type ON tsk_files(dir_type,mime_type,type)");
357  stmt.execute("CREATE INDEX file_extension ON tsk_files(extension)");
358 
359  // account indexes
360  stmt.execute("CREATE INDEX relationships_account1 ON account_relationships(account1_id)");
361  stmt.execute("CREATE INDEX relationships_account2 ON account_relationships(account2_id)");
362  stmt.execute("CREATE INDEX relationships_relationship_source_obj_id ON account_relationships(relationship_source_obj_id)");
363  stmt.execute("CREATE INDEX relationships_date_time ON account_relationships(date_time)");
364  stmt.execute("CREATE INDEX relationships_relationship_type ON account_relationships(relationship_type)");
365  stmt.execute("CREATE INDEX relationships_data_source_obj_id ON account_relationships(data_source_obj_id)");
366 
367  //tsk_events indices
368  stmt.execute("CREATE INDEX events_data_source_obj_id ON tsk_event_descriptions(data_source_obj_id)");
369  stmt.execute("CREATE INDEX events_content_obj_id ON tsk_event_descriptions(content_obj_id)");
370  stmt.execute("CREATE INDEX events_artifact_id ON tsk_event_descriptions(artifact_id)");
371  stmt.execute("CREATE INDEX events_sub_type_time ON tsk_events(event_type_id, time)");
372  stmt.execute("CREATE INDEX events_time ON tsk_events(time)");
373 
374  // analysis results and scores indices
375  stmt.execute("CREATE INDEX score_significance_priority ON tsk_aggregate_score(significance, priority)");
376  stmt.execute("CREATE INDEX score_datasource_obj_id ON tsk_aggregate_score(data_source_obj_id)");
377 
378  stmt.execute("CREATE INDEX tsk_file_attributes_obj_id ON tsk_file_attributes(obj_id)");
379 
380 
381  } catch (SQLException ex) {
382  throw new TskCoreException("Error initializing db_info tables", ex);
383  }
384  }
385 
386  private void createIngestTables(Statement stmt) throws SQLException {
387  stmt.execute("CREATE TABLE ingest_module_types (type_id INTEGER PRIMARY KEY, type_name TEXT NOT NULL)");
388 
389  stmt.execute("CREATE TABLE ingest_job_status_types (type_id INTEGER PRIMARY KEY, type_name TEXT NOT NULL)");
390 
391  stmt.execute("CREATE TABLE ingest_modules (ingest_module_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
392  + "display_name TEXT NOT NULL, unique_name TEXT UNIQUE NOT NULL, type_id INTEGER NOT NULL, "
393  + "version TEXT NOT NULL, FOREIGN KEY(type_id) REFERENCES ingest_module_types(type_id) ON DELETE CASCADE);");
394 
395  stmt.execute("CREATE TABLE ingest_jobs (ingest_job_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
396  + "obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, host_name TEXT NOT NULL, "
397  + "start_date_time " + dbQueryHelper.getBigIntType() + " NOT NULL, "
398  + "end_date_time " + dbQueryHelper.getBigIntType() + " NOT NULL, status_id INTEGER NOT NULL, "
399  + "settings_dir TEXT, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
400  + "FOREIGN KEY(status_id) REFERENCES ingest_job_status_types(type_id) ON DELETE CASCADE);");
401 
402  stmt.execute("CREATE TABLE ingest_job_modules (ingest_job_id INTEGER, ingest_module_id INTEGER, "
403  + "pipeline_position INTEGER, PRIMARY KEY(ingest_job_id, ingest_module_id), "
404  + "FOREIGN KEY(ingest_job_id) REFERENCES ingest_jobs(ingest_job_id) ON DELETE CASCADE, "
405  + "FOREIGN KEY(ingest_module_id) REFERENCES ingest_modules(ingest_module_id) ON DELETE CASCADE);");
406  }
407 
408  private void createHostTables(Statement stmt) throws SQLException {
409 
410  stmt.execute("CREATE TABLE tsk_persons (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
411  + "name TEXT NOT NULL, " // person name
412  + "UNIQUE(name)) ");
413 
414  // References tsk_persons
415  stmt.execute("CREATE TABLE tsk_hosts (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
416  + "name TEXT NOT NULL, " // host name
417  + "db_status INTEGER DEFAULT 0, " // active/merged/deleted
418  + "person_id INTEGER, "
419  + "merged_into " + dbQueryHelper.getBigIntType() + ", "
420  + "FOREIGN KEY(person_id) REFERENCES tsk_persons(id) ON DELETE SET NULL, "
421  + "FOREIGN KEY(merged_into) REFERENCES tsk_hosts(id) ON DELETE CASCADE, "
422  + "UNIQUE(name)) ");
423 
424  stmt.execute("CREATE TABLE tsk_host_addresses (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
425  + "address_type INTEGER NOT NULL, "
426  + "address TEXT NOT NULL, "
427  + "UNIQUE(address_type, address)) ");
428 
429  stmt.execute("CREATE TABLE tsk_host_address_map (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
430  + "host_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
431  + "addr_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
432  + "source_obj_id " + dbQueryHelper.getBigIntType() + ", " // object id of the source where this mapping was found.
433  + "time " + dbQueryHelper.getBigIntType() + ", " // time at which the mapping existed
434  + "UNIQUE(host_id, addr_obj_id, time), "
435  + "FOREIGN KEY(host_id) REFERENCES tsk_hosts(id) ON DELETE CASCADE, "
436  + "FOREIGN KEY(addr_obj_id) REFERENCES tsk_host_addresses(id), "
437  + "FOREIGN KEY(source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE SET NULL )");
438 
439  // stores associations between DNS name and IP address
440  stmt.execute("CREATE TABLE tsk_host_address_dns_ip_map (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
441  + "dns_address_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
442  + "ip_address_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
443  + "source_obj_id " + dbQueryHelper.getBigIntType() + ", "
444  + "time " + dbQueryHelper.getBigIntType() + ", " // time at which the mapping existed
445  + "UNIQUE(dns_address_id, ip_address_id, time), "
446  + "FOREIGN KEY(dns_address_id) REFERENCES tsk_host_addresses(id) ON DELETE CASCADE, "
447  + "FOREIGN KEY(ip_address_id) REFERENCES tsk_host_addresses(id) ON DELETE CASCADE,"
448  + "FOREIGN KEY(source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE SET NULL )");
449 
450  // maps an address to an content/item using it
451  stmt.execute("CREATE TABLE tsk_host_address_usage (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
452  + "addr_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
453  + "obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, " // obj id of the content/item using the address
454  + "data_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, " // data source where the usage was found
455  + "UNIQUE(addr_obj_id, obj_id), "
456  + "FOREIGN KEY(addr_obj_id) REFERENCES tsk_host_addresses(id) ON DELETE CASCADE, "
457  + "FOREIGN KEY(data_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
458  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE )");
459  }
460 
461  // Must be called after tsk_persons, tsk_hosts and tsk_objects have been created.
462  private void createAccountTables(Statement stmt) throws SQLException {
463  stmt.execute("CREATE TABLE account_types (account_type_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
464  + "type_name TEXT UNIQUE NOT NULL, display_name TEXT NOT NULL)");
465 
466  // References account_types
467  stmt.execute("CREATE TABLE accounts (account_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
468  + "account_type_id INTEGER NOT NULL, account_unique_identifier TEXT NOT NULL, "
469  + "UNIQUE(account_type_id, account_unique_identifier), "
470  + "FOREIGN KEY(account_type_id) REFERENCES account_types(account_type_id))");
471 
472  // References accounts, tsk_objects
473  stmt.execute("CREATE TABLE account_relationships (relationship_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
474  + "account1_id INTEGER NOT NULL, account2_id INTEGER NOT NULL, "
475  + "relationship_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
476  + "date_time " + dbQueryHelper.getBigIntType() + ", relationship_type INTEGER NOT NULL, "
477  + "data_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
478  + "UNIQUE(account1_id, account2_id, relationship_source_obj_id), "
479  + "FOREIGN KEY(account1_id) REFERENCES accounts(account_id), "
480  + "FOREIGN KEY(account2_id) REFERENCES accounts(account_id), "
481  + "FOREIGN KEY(relationship_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
482  + "FOREIGN KEY(data_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
483 
484  // References tsk_hosts
485  stmt.execute("CREATE TABLE tsk_os_account_realms (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
486  + "realm_name TEXT DEFAULT NULL, " // realm name - for a domain realm, may be null
487  + "realm_addr TEXT DEFAULT NULL, " // a sid/uid or some some other identifier, may be null
488  + "realm_signature TEXT NOT NULL, " // Signature exists only to prevent duplicates. It is made up of realm address/name and scope host
489  + "scope_host_id " + dbQueryHelper.getBigIntType() + " DEFAULT NULL, " // if the realm scope is a single host
490  + "scope_confidence INTEGER, " // indicates whether we know for sure the realm scope or if we are inferring it
491  + "db_status INTEGER DEFAULT 0, " // active/merged/deleted
492  + "merged_into " + dbQueryHelper.getBigIntType() + " DEFAULT NULL, "
493  + "UNIQUE(realm_signature), "
494  + "FOREIGN KEY(scope_host_id) REFERENCES tsk_hosts(id) ON DELETE CASCADE,"
495  + "FOREIGN KEY(merged_into) REFERENCES tsk_os_account_realms(id) ON DELETE CASCADE )");
496 
497  // References tsk_objects, tsk_os_account_realms, tsk_persons
498  stmt.execute("CREATE TABLE tsk_os_accounts (os_account_obj_id " + dbQueryHelper.getBigIntType() + " PRIMARY KEY, "
499  + "login_name TEXT DEFAULT NULL, " // login name, if available, may be null
500  + "full_name TEXT DEFAULT NULL, " // full name, if available, may be null
501  + "realm_id " + dbQueryHelper.getBigIntType() + " NOT NULL, " // realm for the account
502  + "addr TEXT DEFAULT NULL, " // SID/UID, if available
503  + "signature TEXT NOT NULL, " // This exists only to prevent duplicates. It is either the addr or the login_name whichever is not null.
504  + "status INTEGER, " // enabled/disabled/deleted
505  + "type INTEGER, " // service/interactive
506  + "created_date " + dbQueryHelper.getBigIntType() + " DEFAULT NULL, "
507  + "db_status INTEGER DEFAULT 0, " // active/merged/deleted
508  + "merged_into " + dbQueryHelper.getBigIntType() + " DEFAULT NULL, "
509  + "UNIQUE(signature, realm_id), "
510  + "FOREIGN KEY(os_account_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
511  + "FOREIGN KEY(realm_id) REFERENCES tsk_os_account_realms(id) ON DELETE CASCADE,"
512  + "FOREIGN KEY(merged_into) REFERENCES tsk_os_accounts(os_account_obj_id) ON DELETE CASCADE )");
513 
514  }
515  // Must be called after createAccountTables() and blackboard_attribute_types, blackboard_artifacts creation.
516  private void createAccountInstancesAndArtifacts(Statement stmt) throws SQLException {
517 
518  // References tsk_os_accounts, tsk_hosts, tsk_objects, blackboard_attribute_types
519  stmt.execute("CREATE TABLE tsk_os_account_attributes (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
520  + "os_account_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
521  + "host_id " + dbQueryHelper.getBigIntType() + ", "
522  + "source_obj_id " + dbQueryHelper.getBigIntType() + ", "
523  + "attribute_type_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
524  + "value_type INTEGER NOT NULL, "
525  + "value_byte " + dbQueryHelper.getBlobType() + ", "
526  + "value_text TEXT, "
527  + "value_int32 INTEGER, value_int64 " + dbQueryHelper.getBigIntType() + ", "
528  + "value_double NUMERIC(20, 10), "
529  + "FOREIGN KEY(os_account_obj_id) REFERENCES tsk_os_accounts(os_account_obj_id) ON DELETE CASCADE, "
530  + "FOREIGN KEY(host_id) REFERENCES tsk_hosts(id) ON DELETE CASCADE, "
531  + "FOREIGN KEY(source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE SET NULL, "
532  + "FOREIGN KEY(attribute_type_id) REFERENCES blackboard_attribute_types(attribute_type_id))");
533 
534  // References tsk_os_accounts, tsk_objects, tsk_hosts
535  stmt.execute("CREATE TABLE tsk_os_account_instances (id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
536  + "os_account_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
537  + "data_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
538  + "instance_type INTEGER NOT NULL, " // PerformedActionOn/ReferencedOn
539  + "UNIQUE(os_account_obj_id, data_source_obj_id, instance_type), "
540  + "FOREIGN KEY(os_account_obj_id) REFERENCES tsk_os_accounts(os_account_obj_id) ON DELETE CASCADE, "
541  + "FOREIGN KEY(data_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE ) ");
542 
543  // References blackboard_artifacts, tsk_os_accounts
544  stmt.execute("CREATE TABLE tsk_data_artifacts ( "
545  + "artifact_obj_id " + dbQueryHelper.getBigIntType() + " PRIMARY KEY, "
546  + "os_account_obj_id " + dbQueryHelper.getBigIntType() + ", "
547  + "FOREIGN KEY(artifact_obj_id) REFERENCES blackboard_artifacts(artifact_obj_id) ON DELETE CASCADE, "
548  + "FOREIGN KEY(os_account_obj_id) REFERENCES tsk_os_accounts(os_account_obj_id) ON DELETE SET NULL) ");
549  }
550 
551  private void createEventTables(Statement stmt) throws SQLException {
552  stmt.execute("CREATE TABLE tsk_event_types ("
553  + " event_type_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY,"
554  + " display_name TEXT UNIQUE NOT NULL , "
555  + " super_type_id INTEGER REFERENCES tsk_event_types(event_type_id) )");
556 
557  /*
558  * Regarding the timeline event tables schema, note that several columns
559  * in the tsk_event_descriptions table seem, at first glance, to be
560  * attributes of events rather than their descriptions and would appear
561  * to belong in tsk_events table instead. The rationale for putting the
562  * data source object ID, content object ID, artifact ID and the flags
563  * indicating whether or not the event source has a hash set hit or is
564  * tagged were motivated by the fact that these attributes are identical
565  * for each event in a set of file system file MAC time events. The
566  * decision was made to avoid duplication and save space by placing this
567  * data in the tsk_event-descriptions table.
568  */
569  stmt.execute(
570  "CREATE TABLE tsk_event_descriptions ( "
571  + " event_description_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
572  + " full_description TEXT NOT NULL, "
573  + " med_description TEXT, "
574  + " short_description TEXT,"
575  + " data_source_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
576  + " content_obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
577  + " artifact_id " + dbQueryHelper.getBigIntType() + ", "
578  + " hash_hit INTEGER NOT NULL, " //boolean
579  + " tagged INTEGER NOT NULL, " //boolean
580  + " FOREIGN KEY(data_source_obj_id) REFERENCES data_source_info(obj_id) ON DELETE CASCADE, "
581  + " FOREIGN KEY(content_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
582  + " FOREIGN KEY(artifact_id) REFERENCES blackboard_artifacts(artifact_id) ON DELETE CASCADE,"
583  + " UNIQUE (full_description, content_obj_id, artifact_id))");
584 
585  stmt.execute(
586  "CREATE TABLE tsk_events ("
587  + " event_id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
588  + " event_type_id " + dbQueryHelper.getBigIntType() + " NOT NULL REFERENCES tsk_event_types(event_type_id) ,"
589  + " event_description_id " + dbQueryHelper.getBigIntType() + " NOT NULL REFERENCES tsk_event_descriptions(event_description_id) ON DELETE CASCADE ,"
590  + " time " + dbQueryHelper.getBigIntType() + " NOT NULL , "
591  + " UNIQUE (event_type_id, event_description_id, time))");
592  }
593 
594  private void createAttributeTables(Statement stmt) throws SQLException {
595  /*
596  * Binary representation of BYTEA is a bunch of bytes, which could
597  * include embedded nulls so we have to pay attention to field length.
598  * http://www.postgresql.org/docs/9.4/static/libpq-example.html
599  */
600  stmt.execute("CREATE TABLE tsk_file_attributes ( id " + dbQueryHelper.getPrimaryKey() + " PRIMARY KEY, "
601  + "obj_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
602  + "attribute_type_id " + dbQueryHelper.getBigIntType() + " NOT NULL, "
603  + "value_type INTEGER NOT NULL, value_byte " + dbQueryHelper.getBlobType() + ", "
604  + "value_text TEXT, value_int32 INTEGER, value_int64 " + dbQueryHelper.getBigIntType() + ", value_double NUMERIC(20, 10), "
605  + "FOREIGN KEY(obj_id) REFERENCES tsk_files(obj_id) ON DELETE CASCADE, "
606  + "FOREIGN KEY(attribute_type_id) REFERENCES blackboard_attribute_types(attribute_type_id))");
607  }
608 
612  private abstract class DbCreationHelper {
613 
619  abstract void createDatabase() throws TskCoreException;
620 
626  abstract Connection getConnection() throws TskCoreException;
627 
636  abstract void performPreInitialization(Connection conn) throws TskCoreException;
637 
644  abstract void performPostTableInitialization(Connection conn) throws TskCoreException;
645  }
646 
650  private class PostgreSQLDbCreationHelper extends DbCreationHelper {
651 
652  private final static String JDBC_BASE_URI = "jdbc:postgresql://"; // NON-NLS
653  private final static String JDBC_DRIVER = "org.postgresql.Driver"; // NON-NLS
654 
655  final private String caseName;
656  final private CaseDbConnectionInfo info;
657 
658  PostgreSQLDbCreationHelper(String caseName, CaseDbConnectionInfo info) {
659  this.caseName = caseName;
660  this.info = info;
661  }
662 
663  @Override
664  void createDatabase() throws TskCoreException{
665  try(Connection conn = getPostgresConnection();
666  Statement stmt = conn.createStatement()) {
667  stmt.execute("CREATE DATABASE \"" + caseName + "\" WITH ENCODING='UTF8'");
668  } catch (SQLException ex) {
669  throw new TskCoreException("Error creating PostgreSQL case " + caseName, ex);
670  }
671  }
672 
673  @Override
674  Connection getConnection() throws TskCoreException {
675  return getConnection(caseName);
676  }
677 
683  Connection getPostgresConnection() throws TskCoreException {
684  return getConnection("postgres");
685  }
686 
694  Connection getConnection(String databaseName) throws TskCoreException {
695  String encodedDbName;
696  try {
697  encodedDbName = URLEncoder.encode(databaseName, "UTF-8");
698  } catch (UnsupportedEncodingException ex) {
699  // Print the warning and continue with the unencoded name
700  logger.log(Level.WARNING, "Error encoding database name " + databaseName, ex);
701  encodedDbName = databaseName;
702  }
703 
704  StringBuilder url = new StringBuilder();
705  url.append(JDBC_BASE_URI)
706  .append(info.getHost())
707  .append(":")
708  .append(info.getPort())
709  .append('/') // NON-NLS
710  .append(encodedDbName);
711 
712  Connection conn;
713  try {
714  Properties props = new Properties();
715  props.setProperty("user", info.getUserName()); // NON-NLS
716  props.setProperty("password", info.getPassword()); // NON-NLS
717 
718  Class.forName(JDBC_DRIVER);
719  conn = DriverManager.getConnection(url.toString(), props);
720  } catch (ClassNotFoundException | SQLException ex) {
721  throw new TskCoreException("Failed to acquire ephemeral connection to PostgreSQL database " + databaseName, ex); // NON-NLS
722  }
723  return conn;
724  }
725 
726  @Override
727  void performPreInitialization(Connection conn) throws TskCoreException {
728  // Nothing to do here for PostgreSQL
729  }
730 
731  @Override
732  void performPostTableInitialization(Connection conn) throws TskCoreException {
733  try (Statement stmt = conn.createStatement()) {
734  stmt.execute("ALTER SEQUENCE blackboard_artifacts_artifact_id_seq minvalue -9223372036854775808 restart with -9223372036854775808");
735  } catch (SQLException ex) {
736  throw new TskCoreException("Error altering artifact ID sequence", ex);
737  }
738  }
739  }
740 
744  private class SQLiteDbCreationHelper extends DbCreationHelper {
745 
746  private final static String PRAGMA_SYNC_OFF = "PRAGMA synchronous = OFF"; // NON-NLS
747  private final static String PRAGMA_READ_UNCOMMITTED_TRUE = "PRAGMA read_uncommitted = True"; // NON-NLS
748  private final static String PRAGMA_ENCODING_UTF8 = "PRAGMA encoding = 'UTF-8'"; // NON-NLS
749  private final static String PRAGMA_PAGE_SIZE_4096 = "PRAGMA page_size = 4096"; // NON-NLS
750  private final static String PRAGMA_FOREIGN_KEYS_ON = "PRAGMA foreign_keys = ON"; // NON-NLS
751 
752  private final static String JDBC_DRIVER = "org.sqlite.JDBC"; // NON-NLS
753  private final static String JDBC_BASE_URI = "jdbc:sqlite:"; // NON-NLS
754 
755  String dbPath;
756 
757  SQLiteDbCreationHelper(String dbPath) {
758  this.dbPath = dbPath;
759  }
760 
761  @Override
762  void createDatabase() throws TskCoreException {
763  // SQLite doesn't need to explicitly create the case database but we will
764  // check that the folder exists and the database does not
765  File dbFile = new File(dbPath);
766  if (dbFile.exists()) {
767  throw new TskCoreException("Case database already exists : " + dbPath);
768  }
769 
770  if (dbFile.getParentFile() != null && !dbFile.getParentFile().exists()) {
771  throw new TskCoreException("Case database folder does not exist : " + dbFile.getParent());
772  }
773  }
774 
775  @Override
776  Connection getConnection() throws TskCoreException {
777 
778  StringBuilder url = new StringBuilder();
779  url.append(JDBC_BASE_URI)
780  .append(dbPath);
781 
782  Connection conn;
783  try {
784  Class.forName(JDBC_DRIVER);
785  conn = DriverManager.getConnection(url.toString());
786  } catch (ClassNotFoundException | SQLException ex) {
787  throw new TskCoreException("Failed to acquire ephemeral connection SQLite database " + dbPath, ex); // NON-NLS
788  }
789  return conn;
790  }
791 
792  @Override
793  void performPreInitialization(Connection conn) throws TskCoreException {
794  try (Statement stmt = conn.createStatement()) {
795  stmt.execute(PRAGMA_SYNC_OFF);
796  stmt.execute(PRAGMA_READ_UNCOMMITTED_TRUE);
797  stmt.execute(PRAGMA_ENCODING_UTF8);
798  stmt.execute(PRAGMA_PAGE_SIZE_4096);
799  stmt.execute(PRAGMA_FOREIGN_KEYS_ON);
800  } catch (SQLException ex) {
801  throw new TskCoreException("Error setting pragmas", ex);
802  }
803  }
804 
805  @Override
806  void performPostTableInitialization(Connection conn) throws TskCoreException {
807  // Nothing to do here for SQLite
808  }
809  }
810 }

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