Sleuth Kit Java Bindings (JNI) 4.14.0
Java bindings for using The Sleuth Kit
Loading...
Searching...
No Matches
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 */
19package org.sleuthkit.datamodel;
20
21import java.io.File;
22import java.io.UnsupportedEncodingException;
23import java.net.URLEncoder;
24import java.sql.Connection;
25import java.sql.DriverManager;
26import java.sql.SQLException;
27import java.sql.Statement;
28import java.util.Properties;
29import java.util.logging.Logger;
30import java.util.logging.Level;
31import org.sleuthkit.datamodel.SQLHelper.PostgreSQLHelper;
32import org.sleuthkit.datamodel.SQLHelper.SQLiteHelper;
33
37class 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.