Sleuth Kit Java Bindings (JNI)  4.11.0
Java bindings for using The Sleuth Kit
SleuthkitCase.java
Go to the documentation of this file.
1 /*
2  * Sleuth Kit Data Model
3  *
4  * Copyright 2011-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 com.google.common.cache.Cache;
22 import com.google.common.cache.CacheBuilder;
23 import com.google.common.collect.ImmutableSet;
24 import com.google.common.eventbus.EventBus;
25 import com.mchange.v2.c3p0.ComboPooledDataSource;
26 import com.mchange.v2.c3p0.DataSources;
27 import com.mchange.v2.c3p0.PooledDataSource;
28 import com.zaxxer.sparsebits.SparseBitSet;
29 import java.beans.PropertyVetoException;
30 import java.io.BufferedInputStream;
31 import java.io.BufferedOutputStream;
32 import java.io.File;
33 import java.io.FileInputStream;
34 import java.io.FileOutputStream;
35 import java.io.IOException;
36 import java.io.InputStream;
37 import java.io.OutputStream;
38 import java.io.UnsupportedEncodingException;
39 import java.net.InetAddress;
40 import java.net.URLEncoder;
41 import java.nio.charset.StandardCharsets;
42 import java.nio.file.Paths;
43 import java.sql.Connection;
44 import java.sql.DriverManager;
45 import java.sql.PreparedStatement;
46 import java.sql.ResultSet;
47 import java.sql.SQLException;
48 import java.sql.Statement;
49 import java.text.SimpleDateFormat;
50 import java.util.ArrayList;
51 import java.util.Arrays;
52 import java.util.Collection;
53 import java.util.Collections;
54 import java.util.Date;
55 import java.util.EnumMap;
56 import java.util.HashMap;
57 import java.util.HashSet;
58 import java.util.LinkedHashMap;
59 import java.util.List;
60 import java.util.Map;
61 import java.util.MissingResourceException;
62 import java.util.Objects;
63 import java.util.Properties;
64 import java.util.ResourceBundle;
65 import java.util.Set;
66 import java.util.UUID;
67 import java.util.concurrent.ConcurrentHashMap;
68 import java.util.concurrent.TimeUnit;
69 import java.util.concurrent.locks.ReentrantReadWriteLock;
70 import java.util.logging.Level;
71 import java.util.logging.Logger;
72 import java.util.stream.Collectors;
73 import org.apache.commons.lang3.StringUtils;
74 import org.postgresql.util.PSQLState;
92 import org.sqlite.SQLiteConfig;
93 import org.sqlite.SQLiteDataSource;
94 import org.sqlite.SQLiteJDBCLoader;
95 
100 public class SleuthkitCase {
101 
102  private static final int MAX_DB_NAME_LEN_BEFORE_TIMESTAMP = 47;
103 
108  static final CaseDbSchemaVersionNumber CURRENT_DB_SCHEMA_VERSION
109  = new CaseDbSchemaVersionNumber(9, 1);
110 
111  private static final long BASE_ARTIFACT_ID = Long.MIN_VALUE; // Artifact ids will start at the lowest negative value
112  private static final Logger logger = Logger.getLogger(SleuthkitCase.class.getName());
113  private static final ResourceBundle bundle = ResourceBundle.getBundle("org.sleuthkit.datamodel.Bundle");
114  private static final int IS_REACHABLE_TIMEOUT_MS = 1000;
115  private static final String SQL_ERROR_CONNECTION_GROUP = "08";
116  private static final String SQL_ERROR_AUTHENTICATION_GROUP = "28";
117  private static final String SQL_ERROR_PRIVILEGE_GROUP = "42";
118  private static final String SQL_ERROR_RESOURCE_GROUP = "53";
119  private static final String SQL_ERROR_LIMIT_GROUP = "54";
120  private static final String SQL_ERROR_INTERNAL_GROUP = "xx";
121  private static final int MIN_USER_DEFINED_TYPE_ID = 10000;
122 
123  private static final Set<String> CORE_TABLE_NAMES = ImmutableSet.of(
124  "tsk_events",
125  "tsk_event_descriptions",
126  "tsk_event_types",
127  "tsk_db_info",
128  "tsk_objects",
129  "tsk_image_info",
130  "tsk_image_names",
131  "tsk_vs_info",
132  "tsk_vs_parts",
133  "tsk_fs_info",
134  "tsk_file_layout",
135  "tsk_files",
136  "tsk_files_path",
137  "tsk_files_derived",
138  "tsk_files_derived_method",
139  "tag_names",
140  "content_tags",
141  "blackboard_artifact_tags",
142  "blackboard_artifacts",
143  "blackboard_attributes",
144  "blackboard_artifact_types",
145  "blackboard_attribute_types",
146  "data_source_info",
147  "file_encoding_types",
148  "ingest_module_types",
149  "ingest_job_status_types",
150  "ingest_modules",
151  "ingest_jobs",
152  "ingest_job_modules",
153  "account_types",
154  "accounts",
155  "account_relationships",
156  "review_statuses",
157  "reports,");
158 
159  private static final Set<String> CORE_INDEX_NAMES = ImmutableSet.of(
160  "parObjId",
161  "layout_objID",
162  "artifact_objID",
163  "artifact_artifact_objID",
164  "artifact_typeID",
165  "attrsArtifactID",
166  "mime_type",
167  "file_extension",
168  "relationships_account1",
169  "relationships_account2",
170  "relationships_relationship_source_obj_id",
171  "relationships_date_time",
172  "relationships_relationship_type",
173  "relationships_data_source_obj_id",
174  "events_time",
175  "events_type",
176  "events_data_source_obj_id",
177  "events_file_obj_id",
178  "events_artifact_id");
179 
180  private static final String TSK_VERSION_KEY = "TSK_VER";
181  private static final String SCHEMA_MAJOR_VERSION_KEY = "SCHEMA_MAJOR_VERSION";
182  private static final String SCHEMA_MINOR_VERSION_KEY = "SCHEMA_MINOR_VERSION";
183  private static final String CREATION_SCHEMA_MAJOR_VERSION_KEY = "CREATION_SCHEMA_MAJOR_VERSION";
184  private static final String CREATION_SCHEMA_MINOR_VERSION_KEY = "CREATION_SCHEMA_MINOR_VERSION";
185 
186  private final ConnectionPool connections;
187  private final Object carvedFileDirsLock = new Object();
188  private final Map<Long, VirtualDirectory> rootIdsToCarvedFileDirs = new HashMap<>();
189  private final Map<Long, FileSystem> fileSystemIdMap = new HashMap<>(); // Cache for file system files.
190  private final List<ErrorObserver> sleuthkitCaseErrorObservers = new ArrayList<>();
191  private final String databaseName;
192  private final String dbPath;
193  private final DbType dbType;
194  private final String caseDirPath;
195  private SleuthkitJNI.CaseDbHandle caseHandle;
196  private final String caseHandleIdentifier; // Used to identify this case in the JNI cache.
197  private String dbBackupPath;
198  private Map<Integer, BlackboardArtifact.Type> typeIdToArtifactTypeMap;
199  private Map<Integer, BlackboardAttribute.Type> typeIdToAttributeTypeMap;
200  private Map<String, BlackboardArtifact.Type> typeNameToArtifactTypeMap;
201  private Map<String, BlackboardAttribute.Type> typeNameToAttributeTypeMap;
202  private CaseDbSchemaVersionNumber caseDBSchemaCreationVersion;
203 
204  // Objects for caching the result of isRootDirectory(). Lock is for visibility only.
205  private final Object rootDirectoryMapLock = new Object();
206  private final Map<RootDirectoryKey, Long> rootDirectoryMap = new HashMap<>();
207  private final Cache<Long, Boolean> isRootDirectoryCache
208  = CacheBuilder.newBuilder().maximumSize(200000).expireAfterAccess(5, TimeUnit.MINUTES).build();
209 
210  /*
211  * First parameter is used to specify the SparseBitSet to use, as object IDs
212  * can be larger than the max size of a SparseBitSet
213  */
214  private final Map<Long, SparseBitSet> hasChildrenBitSetMap = new HashMap<>();
215 
216  private long nextArtifactId; // Used to ensure artifact ids come from the desired range.
217  // This read/write lock is used to implement a layer of locking on top of
218  // the locking protocol provided by the underlying SQLite database. The Java
219  // locking protocol improves performance for reasons that are not currently
220  // understood. Note that the lock is contructed to use a fairness policy.
221  private final ReentrantReadWriteLock rwLock = new ReentrantReadWriteLock(true);
222 
223  private CommunicationsManager communicationsMgr;
224  private TimelineManager timelineMgr;
225  private Blackboard blackboard;
226  private CaseDbAccessManager dbAccessManager;
227  private FileManager fileManager;
228  private TaggingManager taggingMgr;
229  private ScoringManager scoringManager;
230  private OsAccountRealmManager osAccountRealmManager;
231  private OsAccountManager osAccountManager;
232  private HostManager hostManager;
233  private PersonManager personManager;
234  private HostAddressManager hostAddressManager;
235 
236  private final Map<String, Set<Long>> deviceIdToDatasourceObjIdMap = new HashMap<>();
237 
238  private final EventBus eventBus = new EventBus("SleuthkitCase-EventBus");
239 
240  public void registerForEvents(Object listener) {
241  eventBus.register(listener);
242  }
243 
244  public void unregisterForEvents(Object listener) {
245  eventBus.unregister(listener);
246  }
247 
248  void fireTSKEvent(Object event) {
249  eventBus.post(event);
250  }
251 
252  // Cache of frequently used content objects (e.g. data source, file system).
253  private final Map<Long, Content> frequentlyUsedContentMap = new HashMap<>();
254 
255  private Examiner cachedCurrentExaminer = null;
256 
257  static {
258  Properties p = new Properties(System.getProperties());
259  p.put("com.mchange.v2.log.MLog", "com.mchange.v2.log.FallbackMLog");
260  p.put("com.mchange.v2.log.FallbackMLog.DEFAULT_CUTOFF_LEVEL", "SEVERE");
261  System.setProperties(p);
262  }
263 
278  public static void tryConnect(CaseDbConnectionInfo info) throws TskCoreException {
279  // Check if we can talk to the database.
280  if (info.getHost() == null || info.getHost().isEmpty()) {
281  throw new TskCoreException(bundle.getString("DatabaseConnectionCheck.MissingHostname")); //NON-NLS
282  } else if (info.getPort() == null || info.getPort().isEmpty()) {
283  throw new TskCoreException(bundle.getString("DatabaseConnectionCheck.MissingPort")); //NON-NLS
284  } else if (info.getUserName() == null || info.getUserName().isEmpty()) {
285  throw new TskCoreException(bundle.getString("DatabaseConnectionCheck.MissingUsername")); //NON-NLS
286  } else if (info.getPassword() == null || info.getPassword().isEmpty()) {
287  throw new TskCoreException(bundle.getString("DatabaseConnectionCheck.MissingPassword")); //NON-NLS
288  }
289 
290  try {
291  Class.forName("org.postgresql.Driver"); //NON-NLS
292  Connection conn = DriverManager.getConnection("jdbc:postgresql://" + info.getHost() + ":" + info.getPort() + "/postgres", info.getUserName(), info.getPassword()); //NON-NLS
293  if (conn != null) {
294  conn.close();
295  }
296  } catch (SQLException ex) {
297  String result;
298  String sqlState = ex.getSQLState().toLowerCase();
299  if (sqlState.startsWith(SQL_ERROR_CONNECTION_GROUP)) {
300  try {
301  if (InetAddress.getByName(info.getHost()).isReachable(IS_REACHABLE_TIMEOUT_MS)) {
302  // if we can reach the host, then it's probably port problem
303  result = bundle.getString("DatabaseConnectionCheck.Port"); //NON-NLS
304  } else {
305  result = bundle.getString("DatabaseConnectionCheck.HostnameOrPort"); //NON-NLS
306  }
307  } catch (IOException | MissingResourceException any) {
308  // it may be anything
309  result = bundle.getString("DatabaseConnectionCheck.Everything"); //NON-NLS
310  }
311  } else if (sqlState.startsWith(SQL_ERROR_AUTHENTICATION_GROUP)) {
312  result = bundle.getString("DatabaseConnectionCheck.Authentication"); //NON-NLS
313  } else if (sqlState.startsWith(SQL_ERROR_PRIVILEGE_GROUP)) {
314  result = bundle.getString("DatabaseConnectionCheck.Access"); //NON-NLS
315  } else if (sqlState.startsWith(SQL_ERROR_RESOURCE_GROUP)) {
316  result = bundle.getString("DatabaseConnectionCheck.ServerDiskSpace"); //NON-NLS
317  } else if (sqlState.startsWith(SQL_ERROR_LIMIT_GROUP)) {
318  result = bundle.getString("DatabaseConnectionCheck.ServerRestart"); //NON-NLS
319  } else if (sqlState.startsWith(SQL_ERROR_INTERNAL_GROUP)) {
320  result = bundle.getString("DatabaseConnectionCheck.InternalServerIssue"); //NON-NLS
321  } else {
322  result = bundle.getString("DatabaseConnectionCheck.Connection"); //NON-NLS
323  }
324  throw new TskCoreException(result);
325  } catch (ClassNotFoundException ex) {
326  throw new TskCoreException(bundle.getString("DatabaseConnectionCheck.Installation")); //NON-NLS
327  }
328  }
329 
341  private SleuthkitCase(String dbPath, SleuthkitJNI.CaseDbHandle caseHandle, DbType dbType) throws Exception {
342  Class.forName("org.sqlite.JDBC");
343  this.dbPath = dbPath;
344  this.dbType = dbType;
345  File dbFile = new File(dbPath);
346  this.caseDirPath = dbFile.getParentFile().getAbsolutePath();
347  this.databaseName = dbFile.getName();
348  this.connections = new SQLiteConnections(dbPath);
349  this.caseHandle = caseHandle;
350  this.caseHandleIdentifier = caseHandle.getCaseDbIdentifier();
351  init();
352  logSQLiteJDBCDriverInfo();
353  }
354 
372  private SleuthkitCase(String host, int port, String dbName, String userName, String password, SleuthkitJNI.CaseDbHandle caseHandle, String caseDirPath, DbType dbType) throws Exception {
373  this.dbPath = "";
374  this.databaseName = dbName;
375  this.dbType = dbType;
376  this.caseDirPath = caseDirPath;
377  this.connections = new PostgreSQLConnections(host, port, dbName, userName, password);
378  this.caseHandle = caseHandle;
379  this.caseHandleIdentifier = caseHandle.getCaseDbIdentifier();
380  init();
381  }
382 
383  private void init() throws Exception {
384  typeIdToArtifactTypeMap = new ConcurrentHashMap<>();
385  typeIdToAttributeTypeMap = new ConcurrentHashMap<>();
386  typeNameToArtifactTypeMap = new ConcurrentHashMap<>();
387  typeNameToAttributeTypeMap = new ConcurrentHashMap<>();
388 
389  /*
390  * The database schema must be updated before loading blackboard
391  * artifact/attribute types
392  */
393  updateDatabaseSchema(null);
394  initBlackboardArtifactTypes();
395  initBlackboardAttributeTypes();
396  initNextArtifactId();
397 
398  try (CaseDbConnection connection = connections.getConnection()) {
399  initIngestModuleTypes(connection);
400  initIngestStatusTypes(connection);
401  initReviewStatuses(connection);
402  initEncodingTypes(connection);
403  populateHasChildrenMap(connection);
404  updateExaminers(connection);
405  initDBSchemaCreationVersion(connection);
406  }
407 
408  blackboard = new Blackboard(this);
409  fileManager = new FileManager(this);
410  communicationsMgr = new CommunicationsManager(this);
411  timelineMgr = new TimelineManager(this);
412  dbAccessManager = new CaseDbAccessManager(this);
413  taggingMgr = new TaggingManager(this);
414  scoringManager = new ScoringManager(this);
415  osAccountRealmManager = new OsAccountRealmManager(this);
416  osAccountManager = new OsAccountManager(this);
417  hostManager = new HostManager(this);
418  personManager = new PersonManager(this);
419  hostAddressManager = new HostAddressManager(this);
420  }
421 
427  static Set<String> getCoreTableNames() {
428  return CORE_TABLE_NAMES;
429  }
430 
436  static Set<String> getCoreIndexNames() {
437  return CORE_INDEX_NAMES;
438  }
439 
448  boolean getHasChildren(Content content) {
449  long objId = content.getId();
450  long mapIndex = objId / Integer.MAX_VALUE;
451  int mapValue = (int) (objId % Integer.MAX_VALUE);
452 
453  synchronized (hasChildrenBitSetMap) {
454  if (hasChildrenBitSetMap.containsKey(mapIndex)) {
455  return hasChildrenBitSetMap.get(mapIndex).get(mapValue);
456  }
457  return false;
458  }
459  }
460 
466  private void setHasChildren(Long objId) {
467  long mapIndex = objId / Integer.MAX_VALUE;
468  int mapValue = (int) (objId % Integer.MAX_VALUE);
469 
470  synchronized (hasChildrenBitSetMap) {
471  if (hasChildrenBitSetMap.containsKey(mapIndex)) {
472  hasChildrenBitSetMap.get(mapIndex).set(mapValue);
473  } else {
474  SparseBitSet bitSet = new SparseBitSet();
475  bitSet.set(mapValue);
476  hasChildrenBitSetMap.put(mapIndex, bitSet);
477  }
478  }
479  }
480 
489  return communicationsMgr;
490  }
491 
498  return blackboard;
499  }
500 
507  return fileManager;
508  }
509 
518  return timelineMgr;
519  }
520 
521  /*
522  * Gets the case database access manager for this case.
523  *
524  * @return The per case CaseDbAccessManager object.
525  *
526  * @throws org.sleuthkit.datamodel.TskCoreException
527  */
529  return dbAccessManager;
530  }
531 
537  public synchronized TaggingManager getTaggingManager() {
538  return taggingMgr;
539  }
540 
549  return scoringManager;
550  }
551 
560  return osAccountRealmManager;
561  }
562 
571  return osAccountManager;
572  }
573 
582  return hostManager;
583  }
584 
593  return personManager;
594  }
595 
604  return hostAddressManager;
605  }
606 
613  private void initBlackboardArtifactTypes() throws SQLException, TskCoreException {
615  try (CaseDbConnection connection = connections.getConnection();
616  Statement statement = connection.createStatement();) {
617  for (ARTIFACT_TYPE type : ARTIFACT_TYPE.values()) {
618  try {
619  statement.execute("INSERT INTO blackboard_artifact_types (artifact_type_id, type_name, display_name, category_type) VALUES (" + type.getTypeID() + " , '" + type.getLabel() + "', '" + type.getDisplayName() + "' , " + type.getCategory().getID() + ")"); //NON-NLS
620  } catch (SQLException ex) {
621  try (ResultSet resultSet = connection.executeQuery(statement, "SELECT COUNT(*) AS count FROM blackboard_artifact_types WHERE artifact_type_id = '" + type.getTypeID() + "'")) { //NON-NLS
622  resultSet.next();
623  if (resultSet.getLong("count") == 0) {
624  throw ex;
625  }
626  }
627  }
628  this.typeIdToArtifactTypeMap.put(type.getTypeID(), new BlackboardArtifact.Type(type));
629  this.typeNameToArtifactTypeMap.put(type.getLabel(), new BlackboardArtifact.Type(type));
630  }
631  if (dbType == DbType.POSTGRESQL) {
632  int newPrimaryKeyIndex = Collections.max(Arrays.asList(ARTIFACT_TYPE.values())).getTypeID() + 1;
633  statement.execute("ALTER SEQUENCE blackboard_artifact_types_artifact_type_id_seq RESTART WITH " + newPrimaryKeyIndex); //NON-NLS
634  }
635  } finally {
637  }
638  }
639 
647  private void initBlackboardAttributeTypes() throws SQLException, TskCoreException {
649  try (CaseDbConnection connection = connections.getConnection();
650  Statement statement = connection.createStatement();) {
651  for (ATTRIBUTE_TYPE type : ATTRIBUTE_TYPE.values()) {
652  try {
653  statement.execute("INSERT INTO blackboard_attribute_types (attribute_type_id, type_name, display_name, value_type) VALUES (" + type.getTypeID() + ", '" + type.getLabel() + "', '" + type.getDisplayName() + "', '" + type.getValueType().getType() + "')"); //NON-NLS
654  } catch (SQLException ex) {
655  try (ResultSet resultSet = connection.executeQuery(statement, "SELECT COUNT(*) AS count FROM blackboard_attribute_types WHERE attribute_type_id = '" + type.getTypeID() + "'")) { //NON-NLS
656  resultSet.next();
657  if (resultSet.getLong("count") == 0) {
658  throw ex;
659  }
660  }
661  }
662  this.typeIdToAttributeTypeMap.put(type.getTypeID(), new BlackboardAttribute.Type(type));
663  this.typeNameToAttributeTypeMap.put(type.getLabel(), new BlackboardAttribute.Type(type));
664  }
665  if (this.dbType == DbType.POSTGRESQL) {
666  int newPrimaryKeyIndex = Collections.max(Arrays.asList(ATTRIBUTE_TYPE.values())).getTypeID() + 1;
667  statement.execute("ALTER SEQUENCE blackboard_attribute_types_attribute_type_id_seq RESTART WITH " + newPrimaryKeyIndex); //NON-NLS
668  }
669  } finally {
671  }
672  }
673 
683  private void initNextArtifactId() throws SQLException, TskCoreException {
684  CaseDbConnection connection = null;
685  Statement statement = null;
686  ResultSet resultSet = null;
688  try {
689  connection = connections.getConnection();
690  statement = connection.createStatement();
691  resultSet = connection.executeQuery(statement, "SELECT MAX(artifact_id) AS max_artifact_id FROM blackboard_artifacts"); //NON-NLS
692  resultSet.next();
693  this.nextArtifactId = resultSet.getLong("max_artifact_id") + 1;
694  if (this.nextArtifactId == 1) {
695  this.nextArtifactId = BASE_ARTIFACT_ID;
696  }
697  } finally {
698  closeResultSet(resultSet);
699  closeStatement(statement);
700  closeConnection(connection);
702  }
703  }
704 
712  private void initIngestModuleTypes(CaseDbConnection connection) throws SQLException, TskCoreException {
713  Statement statement = null;
714  ResultSet resultSet = null;
716  try {
717  statement = connection.createStatement();
718  for (IngestModuleType type : IngestModuleType.values()) {
719  try {
720  statement.execute("INSERT INTO ingest_module_types (type_id, type_name) VALUES (" + type.ordinal() + ", '" + type.toString() + "');"); //NON-NLS
721  } catch (SQLException ex) {
722  resultSet = connection.executeQuery(statement, "SELECT COUNT(*) as count FROM ingest_module_types WHERE type_id = " + type.ordinal() + ";"); //NON-NLS
723  resultSet.next();
724  if (resultSet.getLong("count") == 0) {
725  throw ex;
726  }
727  resultSet.close();
728  resultSet = null;
729  }
730  }
731  } finally {
732  closeResultSet(resultSet);
733  closeStatement(statement);
735  }
736  }
737 
745  private void initIngestStatusTypes(CaseDbConnection connection) throws SQLException, TskCoreException {
746  Statement statement = null;
747  ResultSet resultSet = null;
749  try {
750  statement = connection.createStatement();
751  for (IngestJobStatusType type : IngestJobStatusType.values()) {
752  try {
753  statement.execute("INSERT INTO ingest_job_status_types (type_id, type_name) VALUES (" + type.ordinal() + ", '" + type.toString() + "');"); //NON-NLS
754  } catch (SQLException ex) {
755  resultSet = connection.executeQuery(statement, "SELECT COUNT(*) as count FROM ingest_job_status_types WHERE type_id = " + type.ordinal() + ";"); //NON-NLS
756  resultSet.next();
757  if (resultSet.getLong("count") == 0) {
758  throw ex;
759  }
760  resultSet.close();
761  resultSet = null;
762  }
763  }
764  } finally {
765  closeResultSet(resultSet);
766  closeStatement(statement);
768  }
769  }
770 
777  private void initReviewStatuses(CaseDbConnection connection) throws SQLException, TskCoreException {
778  Statement statement = null;
779  ResultSet resultSet = null;
781  try {
782  statement = connection.createStatement();
783  for (BlackboardArtifact.ReviewStatus status : BlackboardArtifact.ReviewStatus.values()) {
784  try {
785  statement.execute("INSERT INTO review_statuses (review_status_id, review_status_name, display_name) " //NON-NLS
786  + "VALUES (" + status.getID() + ",'" + status.getName() + "','" + status.getDisplayName() + "')"); //NON-NLS
787  } catch (SQLException ex) {
788  resultSet = connection.executeQuery(statement, "SELECT COUNT(*) as count FROM review_statuses WHERE review_status_id = " + status.getID()); //NON-NLS
789  resultSet.next();
790  if (resultSet.getLong("count") == 0) {
791  throw ex;
792  }
793  resultSet.close();
794  resultSet = null;
795  }
796  }
797  } finally {
798  closeResultSet(resultSet);
799  closeStatement(statement);
801  }
802  }
803 
811  private void initEncodingTypes(CaseDbConnection connection) throws SQLException, TskCoreException {
812  Statement statement = null;
813  ResultSet resultSet = null;
815  try {
816  statement = connection.createStatement();
817  for (TskData.EncodingType type : TskData.EncodingType.values()) {
818  try {
819  statement.execute("INSERT INTO file_encoding_types (encoding_type, name) VALUES (" + type.getType() + " , '" + type.name() + "')"); //NON-NLS
820  } catch (SQLException ex) {
821  resultSet = connection.executeQuery(statement, "SELECT COUNT(*) as count FROM file_encoding_types WHERE encoding_type = " + type.getType()); //NON-NLS
822  resultSet.next();
823  if (resultSet.getLong("count") == 0) {
824  throw ex;
825  }
826  resultSet.close();
827  resultSet = null;
828  }
829  }
830  } finally {
831  closeResultSet(resultSet);
832  closeStatement(statement);
834  }
835  }
836 
845  private void updateExaminers(CaseDbConnection connection) throws SQLException, TskCoreException {
846 
847  String loginName = System.getProperty("user.name");
848  if (loginName.isEmpty()) {
849  logger.log(Level.SEVERE, "Cannot determine logged in user name");
850  return;
851  }
852 
854  try {
855  PreparedStatement statement;
856  switch (getDatabaseType()) {
857  case POSTGRESQL:
858  statement = connection.getPreparedStatement(PREPARED_STATEMENT.INSERT_EXAMINER_POSTGRESQL);
859  break;
860  case SQLITE:
861  statement = connection.getPreparedStatement(PREPARED_STATEMENT.INSERT_EXAMINER_SQLITE);
862  break;
863  default:
864  throw new TskCoreException("Unknown DB Type: " + getDatabaseType().name());
865  }
866  statement.clearParameters();
867  statement.setString(1, loginName);
868  connection.executeUpdate(statement);
869  } catch (SQLException ex) {
870  throw new TskCoreException("Error inserting row in tsk_examiners. login name: " + loginName, ex);
871  } finally {
873  }
874  }
875 
883  private void populateHasChildrenMap(CaseDbConnection connection) throws TskCoreException {
884  long timestamp = System.currentTimeMillis();
885 
886  Statement statement = null;
887  ResultSet resultSet = null;
889  try {
890  statement = connection.createStatement();
891  resultSet = statement.executeQuery("select distinct par_obj_id from tsk_objects"); //NON-NLS
892 
893  synchronized (hasChildrenBitSetMap) {
894  while (resultSet.next()) {
895  setHasChildren(resultSet.getLong("par_obj_id"));
896  }
897  }
898  long delay = System.currentTimeMillis() - timestamp;
899  logger.log(Level.INFO, "Time to initialize parent node cache: {0} ms", delay); //NON-NLS
900  } catch (SQLException ex) {
901  throw new TskCoreException("Error populating parent node cache", ex);
902  } finally {
903  closeResultSet(resultSet);
904  closeStatement(statement);
906  }
907  }
908 
915  void addDataSourceToHasChildrenMap() throws TskCoreException {
916 
917  CaseDbConnection connection = connections.getConnection();
918  try {
919  populateHasChildrenMap(connection);
920  } finally {
921  closeConnection(connection);
922  }
923  }
924 
934  private void updateDatabaseSchema(String dbPath) throws Exception {
935  CaseDbConnection connection = null;
936  ResultSet resultSet = null;
937  Statement statement = null;
939  try {
940  connection = connections.getConnection();
941  connection.beginTransaction();
942 
943  boolean hasMinorVersion = false;
944  ResultSet columns = connection.getConnection().getMetaData().getColumns(null, null, "tsk_db_info", "schema%");
945  while (columns.next()) {
946  if (columns.getString("COLUMN_NAME").equals("schema_minor_ver")) {
947  hasMinorVersion = true;
948  }
949  }
950 
951  // Get the schema version number of the case database from the tsk_db_info table.
952  int dbSchemaMajorVersion;
953  int dbSchemaMinorVersion = 0; //schemas before 7 have no minor version , default it to zero.
954 
955  statement = connection.createStatement();
956  resultSet = connection.executeQuery(statement, "SELECT schema_ver"
957  + (hasMinorVersion ? ", schema_minor_ver" : "")
958  + " FROM tsk_db_info"); //NON-NLS
959  if (resultSet.next()) {
960  dbSchemaMajorVersion = resultSet.getInt("schema_ver"); //NON-NLS
961  if (hasMinorVersion) {
962  //if there is a minor version column, use it, else default to zero.
963  dbSchemaMinorVersion = resultSet.getInt("schema_minor_ver"); //NON-NLS
964  }
965  } else {
966  throw new TskCoreException();
967  }
968  CaseDbSchemaVersionNumber dbSchemaVersion = new CaseDbSchemaVersionNumber(dbSchemaMajorVersion, dbSchemaMinorVersion);
969 
970  resultSet.close();
971  resultSet = null;
972  statement.close();
973  statement = null;
974  //check schema compatibility
975  if (false == CURRENT_DB_SCHEMA_VERSION.isCompatible(dbSchemaVersion)) {
976  //we cannot open a db with a major schema version higher than the current one.
977  throw new TskUnsupportedSchemaVersionException(
978  "Unsupported DB schema version " + dbSchemaVersion + ", the highest supported schema version is " + CURRENT_DB_SCHEMA_VERSION.getMajor() + ".X");
979  } else if (dbSchemaVersion.compareTo(CURRENT_DB_SCHEMA_VERSION) < 0) {
980  //The schema version is compatible,possibly after upgrades.
981 
982  if (null != dbPath) {
983  // Make a backup copy of the database. Client code can get the path of the backup
984  // using the getBackupDatabasePath() method.
985  String backupFilePath = dbPath + ".schemaVer" + dbSchemaVersion.toString() + ".backup"; //NON-NLS
986  copyCaseDB(backupFilePath);
987  dbBackupPath = backupFilePath;
988  }
989 
990  // ***CALL SCHEMA UPDATE METHODS HERE***
991  // Each method should examine the schema version passed to it and either:
992  // a. do nothing and return the schema version unchanged, or
993  // b. upgrade the database and return the schema version that the db was upgraded to.
994  dbSchemaVersion = updateFromSchema2toSchema3(dbSchemaVersion, connection);
995  dbSchemaVersion = updateFromSchema3toSchema4(dbSchemaVersion, connection);
996  dbSchemaVersion = updateFromSchema4toSchema5(dbSchemaVersion, connection);
997  dbSchemaVersion = updateFromSchema5toSchema6(dbSchemaVersion, connection);
998  dbSchemaVersion = updateFromSchema6toSchema7(dbSchemaVersion, connection);
999  dbSchemaVersion = updateFromSchema7toSchema7dot1(dbSchemaVersion, connection);
1000  dbSchemaVersion = updateFromSchema7dot1toSchema7dot2(dbSchemaVersion, connection);
1001  dbSchemaVersion = updateFromSchema7dot2toSchema8dot0(dbSchemaVersion, connection);
1002  dbSchemaVersion = updateFromSchema8dot0toSchema8dot1(dbSchemaVersion, connection);
1003  dbSchemaVersion = updateFromSchema8dot1toSchema8dot2(dbSchemaVersion, connection);
1004  dbSchemaVersion = updateFromSchema8dot2toSchema8dot3(dbSchemaVersion, connection);
1005  dbSchemaVersion = updateFromSchema8dot3toSchema8dot4(dbSchemaVersion, connection);
1006  dbSchemaVersion = updateFromSchema8dot4toSchema8dot5(dbSchemaVersion, connection);
1007  dbSchemaVersion = updateFromSchema8dot5toSchema8dot6(dbSchemaVersion, connection);
1008  dbSchemaVersion = updateFromSchema8dot6toSchema9dot0(dbSchemaVersion, connection);
1009  dbSchemaVersion = updateFromSchema9dot0toSchema9dot1(dbSchemaVersion, connection);
1010 
1011  statement = connection.createStatement();
1012  connection.executeUpdate(statement, "UPDATE tsk_db_info SET schema_ver = " + dbSchemaVersion.getMajor() + ", schema_minor_ver = " + dbSchemaVersion.getMinor()); //NON-NLS
1013  connection.executeUpdate(statement, "UPDATE tsk_db_info_extended SET value = " + dbSchemaVersion.getMajor() + " WHERE name = '" + SCHEMA_MAJOR_VERSION_KEY + "'"); //NON-NLS
1014  connection.executeUpdate(statement, "UPDATE tsk_db_info_extended SET value = " + dbSchemaVersion.getMinor() + " WHERE name = '" + SCHEMA_MINOR_VERSION_KEY + "'"); //NON-NLS
1015  statement.close();
1016  statement = null;
1017  }
1018 
1019  connection.commitTransaction();
1020  } catch (Exception ex) { // Cannot do exception multi-catch in Java 6, so use catch-all.
1021  rollbackTransaction(connection);
1022  throw ex;
1023  } finally {
1024  closeResultSet(resultSet);
1025  closeStatement(statement);
1026  closeConnection(connection);
1028  }
1029  }
1030 
1038  private void initDBSchemaCreationVersion(CaseDbConnection connection) throws SQLException {
1039 
1040  Statement statement = null;
1041  ResultSet resultSet = null;
1042  String createdSchemaMajorVersion = "0";
1043  String createdSchemaMinorVersion = "0";
1045  try {
1046  statement = connection.createStatement();
1047  resultSet = connection.executeQuery(statement, "SELECT name, value FROM tsk_db_info_extended");
1048  while (resultSet.next()) {
1049  String name = resultSet.getString("name");
1050  if (name.equals(CREATION_SCHEMA_MAJOR_VERSION_KEY) || name.equals("CREATED_SCHEMA_MAJOR_VERSION")) {
1051  createdSchemaMajorVersion = resultSet.getString("value");
1052  } else if (name.equals(CREATION_SCHEMA_MINOR_VERSION_KEY) || name.equals("CREATED_SCHEMA_MINOR_VERSION")) {
1053  createdSchemaMinorVersion = resultSet.getString("value");
1054  }
1055  }
1056 
1057  } finally {
1058  closeResultSet(resultSet);
1059  closeStatement(statement);
1061  }
1062 
1063  caseDBSchemaCreationVersion = new CaseDbSchemaVersionNumber(Integer.parseInt(createdSchemaMajorVersion), Integer.parseInt(createdSchemaMinorVersion));
1064  }
1065 
1075  public void copyCaseDB(String newDBPath) throws IOException {
1076  if (dbPath.isEmpty()) {
1077  throw new IOException("Copying case database files is not supported for this type of case database"); //NON-NLS
1078  }
1079  InputStream in = null;
1080  OutputStream out = null;
1082  try {
1083  InputStream inFile = new FileInputStream(dbPath);
1084  in = new BufferedInputStream(inFile);
1085  OutputStream outFile = new FileOutputStream(newDBPath);
1086  out = new BufferedOutputStream(outFile);
1087  int bytesRead = in.read();
1088  while (bytesRead != -1) {
1089  out.write(bytesRead);
1090  bytesRead = in.read();
1091  }
1092  } finally {
1093  try {
1094  if (in != null) {
1095  in.close();
1096  }
1097  if (out != null) {
1098  out.flush();
1099  out.close();
1100  }
1101  } catch (IOException e) {
1102  logger.log(Level.WARNING, "Could not close streams after db copy", e); //NON-NLS
1103  }
1105  }
1106  }
1107 
1111  private void logSQLiteJDBCDriverInfo() {
1112  try {
1113  SleuthkitCase.logger.info(String.format("sqlite-jdbc version %s loaded in %s mode", //NON-NLS
1114  SQLiteJDBCLoader.getVersion(), SQLiteJDBCLoader.isNativeMode()
1115  ? "native" : "pure-java")); //NON-NLS
1116  } catch (Exception ex) {
1117  SleuthkitCase.logger.log(Level.SEVERE, "Error querying case database mode", ex);
1118  }
1119  }
1120 
1134  @SuppressWarnings("deprecation")
1135  private CaseDbSchemaVersionNumber updateFromSchema2toSchema3(CaseDbSchemaVersionNumber schemaVersion, CaseDbConnection connection) throws SQLException, TskCoreException {
1136  if (schemaVersion.getMajor() != 2) {
1137  return schemaVersion;
1138  }
1139  Statement statement = null;
1140  Statement statement2 = null;
1141  Statement updateStatement = null;
1142  ResultSet resultSet = null;
1144  try {
1145  statement = connection.createStatement();
1146  statement2 = connection.createStatement();
1147 
1148  // Add new tables for tags.
1149  statement.execute("CREATE TABLE tag_names (tag_name_id INTEGER PRIMARY KEY, display_name TEXT UNIQUE, description TEXT NOT NULL, color TEXT NOT NULL)"); //NON-NLS
1150  statement.execute("CREATE TABLE content_tags (tag_id INTEGER PRIMARY KEY, obj_id INTEGER NOT NULL, tag_name_id INTEGER NOT NULL, comment TEXT NOT NULL, begin_byte_offset INTEGER NOT NULL, end_byte_offset INTEGER NOT NULL)"); //NON-NLS
1151  statement.execute("CREATE TABLE blackboard_artifact_tags (tag_id INTEGER PRIMARY KEY, artifact_id INTEGER NOT NULL, tag_name_id INTEGER NOT NULL, comment TEXT NOT NULL)"); //NON-NLS
1152 
1153  // Add a new table for reports.
1154  statement.execute("CREATE TABLE reports (report_id INTEGER PRIMARY KEY, path TEXT NOT NULL, crtime INTEGER NOT NULL, src_module_name TEXT NOT NULL, report_name TEXT NOT NULL)"); //NON-NLS
1155 
1156  // Add new columns to the image info table.
1157  statement.execute("ALTER TABLE tsk_image_info ADD COLUMN size INTEGER;"); //NON-NLS
1158  statement.execute("ALTER TABLE tsk_image_info ADD COLUMN md5 TEXT;"); //NON-NLS
1159  statement.execute("ALTER TABLE tsk_image_info ADD COLUMN display_name TEXT;"); //NON-NLS
1160 
1161  // Add a new column to the file system info table.
1162  statement.execute("ALTER TABLE tsk_fs_info ADD COLUMN display_name TEXT;"); //NON-NLS
1163 
1164  // Add a new column to the file table.
1165  statement.execute("ALTER TABLE tsk_files ADD COLUMN meta_seq INTEGER;"); //NON-NLS
1166 
1167  // Add new columns and indexes to the attributes table and populate the
1168  // new column. Note that addition of the new column is a denormalization
1169  // to optimize attribute queries.
1170  statement.execute("ALTER TABLE blackboard_attributes ADD COLUMN artifact_type_id INTEGER NULL NOT NULL DEFAULT -1;"); //NON-NLS
1171  statement.execute("CREATE INDEX attribute_artifactTypeId ON blackboard_attributes(artifact_type_id);"); //NON-NLS
1172  statement.execute("CREATE INDEX attribute_valueText ON blackboard_attributes(value_text);"); //NON-NLS
1173  statement.execute("CREATE INDEX attribute_valueInt32 ON blackboard_attributes(value_int32);"); //NON-NLS
1174  statement.execute("CREATE INDEX attribute_valueInt64 ON blackboard_attributes(value_int64);"); //NON-NLS
1175  statement.execute("CREATE INDEX attribute_valueDouble ON blackboard_attributes(value_double);"); //NON-NLS
1176  resultSet = statement.executeQuery("SELECT attrs.artifact_id AS artifact_id, " //NON-NLS
1177  + "arts.artifact_type_id AS artifact_type_id " //NON-NLS
1178  + "FROM blackboard_attributes AS attrs " //NON-NLS
1179  + "INNER JOIN blackboard_artifacts AS arts " //NON-NLS
1180  + "WHERE attrs.artifact_id = arts.artifact_id;"); //NON-NLS
1181  updateStatement = connection.createStatement();
1182  while (resultSet.next()) {
1183  long artifactId = resultSet.getLong("artifact_id");
1184  int artifactTypeId = resultSet.getInt("artifact_type_id");
1185  updateStatement.executeUpdate(
1186  "UPDATE blackboard_attributes " //NON-NLS
1187  + "SET artifact_type_id = " + artifactTypeId //NON-NLS
1188  + " WHERE blackboard_attributes.artifact_id = " + artifactId + ";"); //NON-NLS
1189  }
1190  resultSet.close();
1191 
1192  // Convert existing tag artifact and attribute rows to rows in the new tags tables.
1193  Map<String, Long> tagNames = new HashMap<>();
1194  long tagNameCounter = 1;
1195 
1196  // Convert file tags.
1197  // We need data from the TSK_TAG_NAME and TSK_COMMENT attributes, and need the file size from the tsk_files table.
1198  resultSet = statement.executeQuery("SELECT * FROM \n"
1199  + "(SELECT blackboard_artifacts.obj_id AS objId, blackboard_attributes.artifact_id AS artifactId, blackboard_attributes.value_text AS name\n"
1200  + "FROM blackboard_artifacts INNER JOIN blackboard_attributes \n"
1201  + "ON blackboard_artifacts.artifact_id = blackboard_attributes.artifact_id \n"
1202  + "WHERE blackboard_artifacts.artifact_type_id = "
1203  + BlackboardArtifact.ARTIFACT_TYPE.TSK_TAG_FILE.getTypeID()
1204  + " AND blackboard_attributes.attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_TAG_NAME.getTypeID()
1205  + ") AS tagNames \n"
1206  + "INNER JOIN \n"
1207  + "(SELECT tsk_files.obj_id as objId2, tsk_files.size AS fileSize \n"
1208  + "FROM blackboard_artifacts INNER JOIN tsk_files \n"
1209  + "ON blackboard_artifacts.obj_id = tsk_files.obj_id) AS fileData \n"
1210  + "ON tagNames.objId = fileData.objId2 \n"
1211  + "LEFT JOIN \n"
1212  + "(SELECT value_text AS comment, artifact_id AS tagArtifactId FROM blackboard_attributes WHERE attribute_type_id = "
1213  + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_COMMENT.getTypeID() + ") AS tagComments \n"
1214  + "ON tagNames.artifactId = tagComments.tagArtifactId");
1215 
1216  while (resultSet.next()) {
1217  long objId = resultSet.getLong("objId");
1218  long fileSize = resultSet.getLong("fileSize");
1219  String tagName = resultSet.getString("name");
1220  String tagComment = resultSet.getString("comment");
1221  if (tagComment == null) {
1222  tagComment = "";
1223  }
1224 
1225  if (tagName != null && !tagName.isEmpty()) {
1226  // Get the index for the tag name, adding it to the database if needed.
1227  long tagNameIndex;
1228  if (tagNames.containsKey(tagName)) {
1229  tagNameIndex = tagNames.get(tagName);
1230  } else {
1231  statement2.execute("INSERT INTO tag_names (display_name, description, color) "
1232  + "VALUES(\"" + tagName + "\", \"\", \"None\")");
1233  tagNames.put(tagName, tagNameCounter);
1234  tagNameIndex = tagNameCounter;
1235  tagNameCounter++;
1236  }
1237 
1238  statement2.execute("INSERT INTO content_tags (obj_id, tag_name_id, comment, begin_byte_offset, end_byte_offset) "
1239  + "VALUES(" + objId + ", " + tagNameIndex + ", \"" + tagComment + "\", 0, " + fileSize + ")");
1240  }
1241  }
1242  resultSet.close();
1243 
1244  // Convert artifact tags.
1245  // We need data from the TSK_TAG_NAME, TSK_TAGGED_ARTIFACT, and TSK_COMMENT attributes.
1246  resultSet = statement.executeQuery("SELECT * FROM \n"
1247  + "(SELECT blackboard_artifacts.obj_id AS objId, blackboard_attributes.artifact_id AS artifactId, "
1248  + "blackboard_attributes.value_text AS name\n"
1249  + "FROM blackboard_artifacts INNER JOIN blackboard_attributes \n"
1250  + "ON blackboard_artifacts.artifact_id = blackboard_attributes.artifact_id \n"
1251  + "WHERE blackboard_artifacts.artifact_type_id = "
1252  + BlackboardArtifact.ARTIFACT_TYPE.TSK_TAG_ARTIFACT.getTypeID()
1253  + " AND blackboard_attributes.attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_TAG_NAME.getTypeID()
1254  + ") AS tagNames \n"
1255  + "INNER JOIN \n"
1256  + "(SELECT value_int64 AS taggedArtifactId, artifact_id AS associatedArtifactId FROM blackboard_attributes WHERE attribute_type_id = "
1257  + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_TAGGED_ARTIFACT.getTypeID() + ") AS tagArtifacts \n"
1258  + "ON tagNames.artifactId = tagArtifacts.associatedArtifactId \n"
1259  + "LEFT JOIN \n"
1260  + "(SELECT value_text AS comment, artifact_id AS commentArtifactId FROM blackboard_attributes WHERE attribute_type_id = "
1261  + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_COMMENT.getTypeID() + ") AS tagComments \n"
1262  + "ON tagNames.artifactId = tagComments.commentArtifactId");
1263 
1264  while (resultSet.next()) {
1265  long artifactId = resultSet.getLong("taggedArtifactId");
1266  String tagName = resultSet.getString("name");
1267  String tagComment = resultSet.getString("comment");
1268  if (tagComment == null) {
1269  tagComment = "";
1270  }
1271  if (tagName != null && !tagName.isEmpty()) {
1272  // Get the index for the tag name, adding it to the database if needed.
1273  long tagNameIndex;
1274  if (tagNames.containsKey(tagName)) {
1275  tagNameIndex = tagNames.get(tagName);
1276  } else {
1277  statement2.execute("INSERT INTO tag_names (display_name, description, color) "
1278  + "VALUES(\"" + tagName + "\", \"\", \"None\")");
1279  tagNames.put(tagName, tagNameCounter);
1280  tagNameIndex = tagNameCounter;
1281  tagNameCounter++;
1282  }
1283 
1284  statement2.execute("INSERT INTO blackboard_artifact_tags (artifact_id, tag_name_id, comment) "
1285  + "VALUES(" + artifactId + ", " + tagNameIndex + ", \"" + tagComment + "\")");
1286  }
1287  }
1288  resultSet.close();
1289 
1290  statement.execute(
1291  "DELETE FROM blackboard_attributes WHERE artifact_id IN " //NON-NLS
1292  + "(SELECT artifact_id FROM blackboard_artifacts WHERE artifact_type_id = " //NON-NLS
1293  + ARTIFACT_TYPE.TSK_TAG_FILE.getTypeID()
1294  + " OR artifact_type_id = " + ARTIFACT_TYPE.TSK_TAG_ARTIFACT.getTypeID() + ");"); //NON-NLS
1295  statement.execute(
1296  "DELETE FROM blackboard_artifacts WHERE artifact_type_id = " //NON-NLS
1297  + ARTIFACT_TYPE.TSK_TAG_FILE.getTypeID()
1298  + " OR artifact_type_id = " + ARTIFACT_TYPE.TSK_TAG_ARTIFACT.getTypeID() + ";"); //NON-NLS
1299 
1300  return new CaseDbSchemaVersionNumber(3, 0);
1301  } finally {
1302  closeStatement(updateStatement);
1303  closeResultSet(resultSet);
1304  closeStatement(statement);
1305  closeStatement(statement2);
1307  }
1308  }
1309 
1323  private CaseDbSchemaVersionNumber updateFromSchema3toSchema4(CaseDbSchemaVersionNumber schemaVersion, CaseDbConnection connection) throws SQLException, TskCoreException {
1324  if (schemaVersion.getMajor() != 3) {
1325  return schemaVersion;
1326  }
1327 
1328  Statement statement = null;
1329  ResultSet resultSet = null;
1330  Statement queryStatement = null;
1331  ResultSet queryResultSet = null;
1332  Statement updateStatement = null;
1334  try {
1335  // Add mime_type column to tsk_files table. Populate with general
1336  // info artifact file signature data.
1337  statement = connection.createStatement();
1338  updateStatement = connection.createStatement();
1339  statement.execute("ALTER TABLE tsk_files ADD COLUMN mime_type TEXT;");
1340  resultSet = statement.executeQuery("SELECT files.obj_id AS obj_id, attrs.value_text AS value_text "
1341  + "FROM tsk_files AS files, blackboard_attributes AS attrs, blackboard_artifacts AS arts "
1342  + "WHERE files.obj_id = arts.obj_id AND "
1343  + "arts.artifact_id = attrs.artifact_id AND "
1344  + "arts.artifact_type_id = 1 AND "
1345  + "attrs.attribute_type_id = 62");
1346  while (resultSet.next()) {
1347  updateStatement.executeUpdate(
1348  "UPDATE tsk_files " //NON-NLS
1349  + "SET mime_type = '" + resultSet.getString("value_text") + "' " //NON-NLS
1350  + "WHERE tsk_files.obj_id = " + resultSet.getInt("obj_id") + ";"); //NON-NLS
1351  }
1352  resultSet.close();
1353 
1354  // Add value_type column to blackboard_attribute_types table.
1355  statement.execute("ALTER TABLE blackboard_attribute_types ADD COLUMN value_type INTEGER NOT NULL DEFAULT -1;");
1356  resultSet = statement.executeQuery("SELECT * FROM blackboard_attribute_types AS types"); //NON-NLS
1357  while (resultSet.next()) {
1358  int attributeTypeId = resultSet.getInt("attribute_type_id");
1359  String attributeLabel = resultSet.getString("type_name");
1360  if (attributeTypeId < MIN_USER_DEFINED_TYPE_ID) {
1361  updateStatement.executeUpdate(
1362  "UPDATE blackboard_attribute_types " //NON-NLS
1363  + "SET value_type = " + ATTRIBUTE_TYPE.fromLabel(attributeLabel).getValueType().getType() + " " //NON-NLS
1364  + "WHERE blackboard_attribute_types.attribute_type_id = " + attributeTypeId + ";"); //NON-NLS
1365  }
1366  }
1367  resultSet.close();
1368 
1369  // Add a data_sources_info table.
1370  queryStatement = connection.createStatement();
1371  statement.execute("CREATE TABLE data_source_info (obj_id INTEGER PRIMARY KEY, device_id TEXT NOT NULL, time_zone TEXT NOT NULL, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id));");
1372  resultSet = statement.executeQuery("SELECT * FROM tsk_objects WHERE par_obj_id IS NULL");
1373  while (resultSet.next()) {
1374  long objectId = resultSet.getLong("obj_id");
1375  String timeZone = "";
1376  queryResultSet = queryStatement.executeQuery("SELECT tzone FROM tsk_image_info WHERE obj_id = " + objectId);
1377  if (queryResultSet.next()) {
1378  timeZone = queryResultSet.getString("tzone");
1379  }
1380  queryResultSet.close();
1381  updateStatement.executeUpdate("INSERT INTO data_source_info (obj_id, device_id, time_zone) "
1382  + "VALUES(" + objectId + ", '" + UUID.randomUUID().toString() + "' , '" + timeZone + "');");
1383  }
1384  resultSet.close();
1385 
1386  // Add data_source_obj_id column to the tsk_files table.
1387  //
1388  // NOTE: A new case database will have the following FK constraint:
1389  //
1390  // REFERENCES data_source_info (obj_id)
1391  //
1392  // The constraint is sacrificed here to avoid having to create and
1393  // populate a new tsk_files table.
1394  //
1395  // TODO: Do this right.
1396  statement.execute("ALTER TABLE tsk_files ADD COLUMN data_source_obj_id BIGINT NOT NULL DEFAULT -1;");
1397  resultSet = statement.executeQuery("SELECT tsk_files.obj_id AS obj_id, par_obj_id FROM tsk_files, tsk_objects WHERE tsk_files.obj_id = tsk_objects.obj_id");
1398  while (resultSet.next()) {
1399  long fileId = resultSet.getLong("obj_id");
1400  long dataSourceId = getDataSourceObjectId(connection, fileId);
1401  updateStatement.executeUpdate("UPDATE tsk_files SET data_source_obj_id = " + dataSourceId + " WHERE obj_id = " + fileId + ";");
1402  }
1403  resultSet.close();
1404  statement.execute("CREATE TABLE ingest_module_types (type_id INTEGER PRIMARY KEY, type_name TEXT NOT NULL)"); //NON-NLS
1405  statement.execute("CREATE TABLE ingest_job_status_types (type_id INTEGER PRIMARY KEY, type_name TEXT NOT NULL)"); //NON-NLS
1406  if (this.dbType.equals(DbType.SQLITE)) {
1407  statement.execute("CREATE TABLE ingest_modules (ingest_module_id INTEGER PRIMARY KEY, display_name TEXT NOT NULL, unique_name TEXT UNIQUE NOT NULL, type_id INTEGER NOT NULL, version TEXT NOT NULL, FOREIGN KEY(type_id) REFERENCES ingest_module_types(type_id));"); //NON-NLS
1408  statement.execute("CREATE TABLE ingest_jobs (ingest_job_id INTEGER PRIMARY KEY, obj_id BIGINT NOT NULL, host_name TEXT NOT NULL, start_date_time BIGINT NOT NULL, end_date_time BIGINT NOT NULL, status_id INTEGER NOT NULL, settings_dir TEXT, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id), FOREIGN KEY(status_id) REFERENCES ingest_job_status_types(type_id));"); //NON-NLS
1409  } else {
1410  statement.execute("CREATE TABLE ingest_modules (ingest_module_id BIGSERIAL PRIMARY KEY, display_name TEXT NOT NULL, unique_name TEXT UNIQUE NOT NULL, type_id INTEGER NOT NULL, version TEXT NOT NULL, FOREIGN KEY(type_id) REFERENCES ingest_module_types(type_id));"); //NON-NLS
1411  statement.execute("CREATE TABLE ingest_jobs (ingest_job_id BIGSERIAL PRIMARY KEY, obj_id BIGINT NOT NULL, host_name TEXT NOT NULL, start_date_time BIGINT NOT NULL, end_date_time BIGINT NOT NULL, status_id INTEGER NOT NULL, settings_dir TEXT, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id), FOREIGN KEY(status_id) REFERENCES ingest_job_status_types(type_id));"); //NON-NLS
1412  }
1413 
1414  statement.execute("CREATE TABLE ingest_job_modules (ingest_job_id INTEGER, ingest_module_id INTEGER, pipeline_position INTEGER, PRIMARY KEY(ingest_job_id, ingest_module_id), FOREIGN KEY(ingest_job_id) REFERENCES ingest_jobs(ingest_job_id), FOREIGN KEY(ingest_module_id) REFERENCES ingest_modules(ingest_module_id));"); //NON-NLS
1415  initIngestModuleTypes(connection);
1416  initIngestStatusTypes(connection);
1417 
1418  return new CaseDbSchemaVersionNumber(4, 0);
1419 
1420  } finally {
1421  closeResultSet(queryResultSet);
1422  closeStatement(queryStatement);
1423  closeStatement(updateStatement);
1424  closeResultSet(resultSet);
1425  closeStatement(statement);
1427  }
1428  }
1429 
1443  private CaseDbSchemaVersionNumber updateFromSchema4toSchema5(CaseDbSchemaVersionNumber schemaVersion, CaseDbConnection connection) throws SQLException, TskCoreException {
1444  if (schemaVersion.getMajor() != 4) {
1445  return schemaVersion;
1446  }
1447 
1448  Statement statement = null;
1450  try {
1451  // Add the review_statuses lookup table.
1452  statement = connection.createStatement();
1453  statement.execute("CREATE TABLE review_statuses (review_status_id INTEGER PRIMARY KEY, review_status_name TEXT NOT NULL, display_name TEXT NOT NULL)");
1454 
1455  /*
1456  * Add review_status_id column to artifacts table.
1457  *
1458  * NOTE: For DBs created with schema 5 we define a foreign key
1459  * constraint on the review_status_column. We don't bother with this
1460  * for DBs updated to schema 5 because of limitations of the SQLite
1461  * ALTER TABLE command.
1462  */
1463  statement.execute("ALTER TABLE blackboard_artifacts ADD COLUMN review_status_id INTEGER NOT NULL DEFAULT " + BlackboardArtifact.ReviewStatus.UNDECIDED.getID());
1464 
1465  // Add the encoding table
1466  statement.execute("CREATE TABLE file_encoding_types (encoding_type INTEGER PRIMARY KEY, name TEXT NOT NULL);");
1467  initEncodingTypes(connection);
1468 
1469  /*
1470  * This needs to be done due to a Autopsy/TSK out of synch problem.
1471  * Without this, it is possible to upgrade from version 4 to 5 and
1472  * then 5 to 6, but not from 4 to 6.
1473  */
1474  initReviewStatuses(connection);
1475 
1476  // Add encoding type column to tsk_files_path
1477  // This should really have the FOREIGN KEY constraint but there are problems
1478  // getting that to work, so we don't add it on this upgrade path.
1479  statement.execute("ALTER TABLE tsk_files_path ADD COLUMN encoding_type INTEGER NOT NULL DEFAULT 0;");
1480 
1481  return new CaseDbSchemaVersionNumber(5, 0);
1482 
1483  } finally {
1484  closeStatement(statement);
1486  }
1487  }
1488 
1502  private CaseDbSchemaVersionNumber updateFromSchema5toSchema6(CaseDbSchemaVersionNumber schemaVersion, CaseDbConnection connection) throws SQLException, TskCoreException {
1503  if (schemaVersion.getMajor() != 5) {
1504  return schemaVersion;
1505  }
1506 
1507  /*
1508  * This upgrade fixes a bug where some releases had artifact review
1509  * status support in the case database and others did not.
1510  */
1511  Statement statement = null;
1512  ResultSet resultSet = null;
1514  try {
1515  /*
1516  * Add the review_statuses lookup table, if missing.
1517  */
1518  statement = connection.createStatement();
1519  statement.execute("CREATE TABLE IF NOT EXISTS review_statuses (review_status_id INTEGER PRIMARY KEY, review_status_name TEXT NOT NULL, display_name TEXT NOT NULL)");
1520 
1521  resultSet = connection.executeQuery(statement, "SELECT COUNT(*) AS count FROM review_statuses"); //NON-NLS
1522  resultSet.next();
1523  if (resultSet.getLong("count") == 0) {
1524  /*
1525  * Add review_status_id column to artifacts table.
1526  *
1527  * NOTE: For DBs created with schema 5 or 6 we define a foreign
1528  * key constraint on the review_status_column. We don't bother
1529  * with this for DBs updated to schema 5 or 6 because of
1530  * limitations of the SQLite ALTER TABLE command.
1531  */
1532  statement.execute("ALTER TABLE blackboard_artifacts ADD COLUMN review_status_id INTEGER NOT NULL DEFAULT " + BlackboardArtifact.ReviewStatus.UNDECIDED.getID());
1533  }
1534 
1535  return new CaseDbSchemaVersionNumber(6, 0);
1536 
1537  } finally {
1538  closeResultSet(resultSet);
1539  closeStatement(statement);
1541  }
1542  }
1543 
1557  private CaseDbSchemaVersionNumber updateFromSchema6toSchema7(CaseDbSchemaVersionNumber schemaVersion, CaseDbConnection connection) throws SQLException, TskCoreException {
1558  if (schemaVersion.getMajor() != 6) {
1559  return schemaVersion;
1560  }
1561 
1562  /*
1563  * This upgrade adds an indexed extension column to the tsk_files table.
1564  */
1565  Statement statement = null;
1566  Statement updstatement = null;
1567  ResultSet resultSet = null;
1569  try {
1570  statement = connection.createStatement();
1571  updstatement = connection.createStatement();
1572  statement.execute("ALTER TABLE tsk_files ADD COLUMN extension TEXT");
1573 
1574  resultSet = connection.executeQuery(statement, "SELECT obj_id,name FROM tsk_files"); //NON-NLS
1575  while (resultSet.next()) {
1576  long objID = resultSet.getLong("obj_id");
1577  String name = resultSet.getString("name");
1578  updstatement.executeUpdate("UPDATE tsk_files SET extension = '" + escapeSingleQuotes(extractExtension(name)) + "' "
1579  + "WHERE obj_id = " + objID);
1580  }
1581 
1582  statement.execute("CREATE INDEX file_extension ON tsk_files ( extension )");
1583 
1584  // Add artifact_obj_id column to blackboard_artifacts table, data conversion for old versions isn't necesarry.
1585  statement.execute("ALTER TABLE blackboard_artifacts ADD COLUMN artifact_obj_id INTEGER NOT NULL DEFAULT -1");
1586 
1587  return new CaseDbSchemaVersionNumber(7, 0);
1588 
1589  } finally {
1590  closeResultSet(resultSet);
1591  closeStatement(statement);
1592  closeStatement(updstatement);
1594  }
1595  }
1596 
1610  private CaseDbSchemaVersionNumber updateFromSchema7toSchema7dot1(CaseDbSchemaVersionNumber schemaVersion, CaseDbConnection connection) throws SQLException, TskCoreException {
1611  if (schemaVersion.getMajor() != 7) {
1612  return schemaVersion;
1613  }
1614 
1615  if (schemaVersion.getMinor() != 0) {
1616  return schemaVersion;
1617  }
1618 
1619  /*
1620  * This upgrade adds a minor version number column.
1621  */
1622  Statement statement = null;
1623  ResultSet resultSet = null;
1625  try {
1626  statement = connection.createStatement();
1627 
1628  //add the schema minor version number column.
1629  if (schemaVersion.getMinor() == 0) {
1630  //add the schema minor version number column.
1631  statement.execute("ALTER TABLE tsk_db_info ADD COLUMN schema_minor_ver INTEGER DEFAULT 1");
1632  }
1633  return new CaseDbSchemaVersionNumber(7, 1);
1634 
1635  } finally {
1636  closeResultSet(resultSet);
1637  closeStatement(statement);
1639  }
1640  }
1641 
1655  private CaseDbSchemaVersionNumber updateFromSchema7dot1toSchema7dot2(CaseDbSchemaVersionNumber schemaVersion, CaseDbConnection connection) throws SQLException, TskCoreException {
1656  if (schemaVersion.getMajor() != 7) {
1657  return schemaVersion;
1658  }
1659 
1660  if (schemaVersion.getMinor() != 1) {
1661  return schemaVersion;
1662  }
1663 
1664  Statement statement = null;
1665  Statement updstatement = null;
1666  ResultSet resultSet = null;
1668  try {
1669  //add the data_source_obj_id column to blackboard_artifacts.
1670  statement = connection.createStatement();
1671  statement.execute("ALTER TABLE blackboard_artifacts ADD COLUMN data_source_obj_id INTEGER NOT NULL DEFAULT -1");
1672 
1673  // populate data_source_obj_id for each artifact
1674  updstatement = connection.createStatement();
1675  resultSet = connection.executeQuery(statement, "SELECT artifact_id, obj_id FROM blackboard_artifacts"); //NON-NLS
1676  while (resultSet.next()) {
1677  long artifact_id = resultSet.getLong("artifact_id");
1678  long obj_id = resultSet.getLong("obj_id");
1679  long data_source_obj_id = getDataSourceObjectId(connection, obj_id);
1680  updstatement.executeUpdate("UPDATE blackboard_artifacts SET data_source_obj_id = " + data_source_obj_id + " "
1681  + "WHERE artifact_id = " + artifact_id);
1682  }
1683  closeResultSet(resultSet);
1684  closeStatement(statement);
1685  closeStatement(updstatement);
1686 
1687  /*
1688  * Add a knownStatus column to the tag_names table.
1689  */
1690  statement = connection.createStatement();
1691  statement.execute("ALTER TABLE tag_names ADD COLUMN knownStatus INTEGER NOT NULL DEFAULT " + TskData.FileKnown.UNKNOWN.getFileKnownValue());
1692 
1693  // Create account_types, accounts, and account_relationships table
1694  if (this.dbType.equals(DbType.SQLITE)) {
1695  statement.execute("CREATE TABLE account_types (account_type_id INTEGER PRIMARY KEY, type_name TEXT UNIQUE NOT NULL, display_name TEXT NOT NULL)");
1696  statement.execute("CREATE TABLE accounts (account_id INTEGER PRIMARY KEY, account_type_id INTEGER NOT NULL, account_unique_identifier TEXT NOT NULL, UNIQUE(account_type_id, account_unique_identifier) , FOREIGN KEY(account_type_id) REFERENCES account_types(account_type_id))");
1697  statement.execute("CREATE TABLE account_relationships (relationship_id INTEGER PRIMARY KEY, account1_id INTEGER NOT NULL, account2_id INTEGER NOT NULL, relationship_source_obj_id INTEGER NOT NULL, date_time INTEGER, relationship_type INTEGER NOT NULL, data_source_obj_id INTEGER NOT NULL, UNIQUE(account1_id, account2_id, relationship_source_obj_id), FOREIGN KEY(account1_id) REFERENCES accounts(account_id), FOREIGN KEY(account2_id) REFERENCES accounts(account_id), FOREIGN KEY(relationship_source_obj_id) REFERENCES tsk_objects(obj_id), FOREIGN KEY(data_source_obj_id) REFERENCES tsk_objects(obj_id))");
1698  } else {
1699  statement.execute("CREATE TABLE account_types (account_type_id BIGSERIAL PRIMARY KEY, type_name TEXT UNIQUE NOT NULL, display_name TEXT NOT NULL)");
1700  statement.execute("CREATE TABLE accounts (account_id BIGSERIAL PRIMARY KEY, account_type_id INTEGER NOT NULL, account_unique_identifier TEXT NOT NULL, UNIQUE(account_type_id, account_unique_identifier) , FOREIGN KEY(account_type_id) REFERENCES account_types(account_type_id))");
1701  statement.execute("CREATE TABLE account_relationships (relationship_id BIGSERIAL PRIMARY KEY, account1_id INTEGER NOT NULL, account2_id INTEGER NOT NULL, relationship_source_obj_id INTEGER NOT NULL, date_time BIGINT, relationship_type INTEGER NOT NULL, data_source_obj_id INTEGER NOT NULL, UNIQUE(account1_id, account2_id, relationship_source_obj_id), FOREIGN KEY(account1_id) REFERENCES accounts(account_id), FOREIGN KEY(account2_id) REFERENCES accounts(account_id), FOREIGN KEY(relationship_source_obj_id) REFERENCES tsk_objects(obj_id), FOREIGN KEY(data_source_obj_id) REFERENCES tsk_objects(obj_id))");
1702  }
1703 
1704  // Create indexes
1705  statement.execute("CREATE INDEX artifact_artifact_objID ON blackboard_artifacts(artifact_obj_id)");
1706  statement.execute("CREATE INDEX relationships_account1 ON account_relationships(account1_id)");
1707  statement.execute("CREATE INDEX relationships_account2 ON account_relationships(account2_id)");
1708  statement.execute("CREATE INDEX relationships_relationship_source_obj_id ON account_relationships(relationship_source_obj_id)");
1709  statement.execute("CREATE INDEX relationships_date_time ON account_relationships(date_time)");
1710  statement.execute("CREATE INDEX relationships_relationship_type ON account_relationships(relationship_type)");
1711  statement.execute("CREATE INDEX relationships_data_source_obj_id ON account_relationships(data_source_obj_id)");
1712 
1713  return new CaseDbSchemaVersionNumber(7, 2);
1714  } finally {
1715  closeResultSet(resultSet);
1716  closeStatement(statement);
1717  closeStatement(updstatement);
1719  }
1720  }
1721 
1735  private CaseDbSchemaVersionNumber updateFromSchema7dot2toSchema8dot0(CaseDbSchemaVersionNumber schemaVersion, CaseDbConnection connection) throws SQLException, TskCoreException {
1736  if (schemaVersion.getMajor() != 7) {
1737  return schemaVersion;
1738  }
1739 
1740  if (schemaVersion.getMinor() != 2) {
1741  return schemaVersion;
1742  }
1743 
1744  Statement updateSchemaStatement = connection.createStatement();
1745  Statement getExistingReportsStatement = connection.createStatement();
1746  ResultSet resultSet = null;
1747  ResultSet existingReports = null;
1748 
1750  try {
1751  // Update the schema to turn report_id into an object id.
1752 
1753  // Unfortunately, SQLite doesn't support adding a constraint
1754  // to an existing table so we have to rename the old...
1755  updateSchemaStatement.execute("ALTER TABLE reports RENAME TO old_reports");
1756 
1757  // ...create the new...
1758  updateSchemaStatement.execute("CREATE TABLE reports (obj_id BIGSERIAL PRIMARY KEY, path TEXT NOT NULL, crtime INTEGER NOT NULL, src_module_name TEXT NOT NULL, report_name TEXT NOT NULL, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id))");
1759 
1760  // ...add the existing report records back...
1761  existingReports = getExistingReportsStatement.executeQuery("SELECT * FROM old_reports");
1762  while (existingReports.next()) {
1763  String path = existingReports.getString(2);
1764  long crtime = existingReports.getInt(3);
1765  String sourceModule = existingReports.getString(4);
1766  String reportName = existingReports.getString(5);
1767 
1768  PreparedStatement insertObjectStatement = connection.getPreparedStatement(PREPARED_STATEMENT.INSERT_OBJECT, Statement.RETURN_GENERATED_KEYS);
1769  insertObjectStatement.clearParameters();
1770  insertObjectStatement.setNull(1, java.sql.Types.BIGINT);
1771  insertObjectStatement.setLong(2, TskData.ObjectType.REPORT.getObjectType());
1772  connection.executeUpdate(insertObjectStatement);
1773  resultSet = insertObjectStatement.getGeneratedKeys();
1774  if (!resultSet.next()) {
1775  throw new TskCoreException(String.format("Failed to INSERT report %s (%s) in tsk_objects table", reportName, path));
1776  }
1777  long objectId = resultSet.getLong(1); //last_insert_rowid()
1778 
1779  // INSERT INTO reports (obj_id, path, crtime, src_module_name, display_name) VALUES (?, ?, ?, ?, ?)
1780  PreparedStatement insertReportStatement = connection.getPreparedStatement(PREPARED_STATEMENT.INSERT_REPORT);
1781  insertReportStatement.clearParameters();
1782  insertReportStatement.setLong(1, objectId);
1783  insertReportStatement.setString(2, path);
1784  insertReportStatement.setLong(3, crtime);
1785  insertReportStatement.setString(4, sourceModule);
1786  insertReportStatement.setString(5, reportName);
1787  connection.executeUpdate(insertReportStatement);
1788  }
1789 
1790  // ...and drop the old table.
1791  updateSchemaStatement.execute("DROP TABLE old_reports");
1792 
1793  return new CaseDbSchemaVersionNumber(8, 0);
1794  } finally {
1795  closeResultSet(resultSet);
1796  closeResultSet(existingReports);
1797  closeStatement(updateSchemaStatement);
1798  closeStatement(getExistingReportsStatement);
1800  }
1801  }
1802 
1816  private CaseDbSchemaVersionNumber updateFromSchema8dot0toSchema8dot1(CaseDbSchemaVersionNumber schemaVersion, CaseDbConnection connection) throws SQLException, TskCoreException {
1817  if (schemaVersion.getMajor() != 8) {
1818  return schemaVersion;
1819  }
1820 
1821  if (schemaVersion.getMinor() != 0) {
1822  return schemaVersion;
1823  }
1824 
1826 
1827  try (Statement statement = connection.createStatement();) {
1828  // create examiners table
1829  if (this.dbType.equals(DbType.SQLITE)) {
1830  statement.execute("CREATE TABLE tsk_examiners (examiner_id INTEGER PRIMARY KEY, login_name TEXT NOT NULL, display_name TEXT, UNIQUE(login_name) )");
1831  statement.execute("ALTER TABLE content_tags ADD COLUMN examiner_id INTEGER REFERENCES tsk_examiners(examiner_id) DEFAULT NULL");
1832  statement.execute("ALTER TABLE blackboard_artifact_tags ADD COLUMN examiner_id INTEGER REFERENCES tsk_examiners(examiner_id) DEFAULT NULL");
1833  } else {
1834  statement.execute("CREATE TABLE tsk_examiners (examiner_id BIGSERIAL PRIMARY KEY, login_name TEXT NOT NULL, display_name TEXT, UNIQUE(login_name))");
1835  statement.execute("ALTER TABLE content_tags ADD COLUMN examiner_id BIGINT REFERENCES tsk_examiners(examiner_id) DEFAULT NULL");
1836  statement.execute("ALTER TABLE blackboard_artifact_tags ADD COLUMN examiner_id BIGINT REFERENCES tsk_examiners(examiner_id) DEFAULT NULL");
1837  }
1838 
1839  return new CaseDbSchemaVersionNumber(8, 1);
1840  } finally {
1842  }
1843  }
1844 
1858  private CaseDbSchemaVersionNumber updateFromSchema8dot1toSchema8dot2(CaseDbSchemaVersionNumber schemaVersion, CaseDbConnection connection) throws SQLException, TskCoreException {
1859  if (schemaVersion.getMajor() != 8) {
1860  return schemaVersion;
1861  }
1862 
1863  if (schemaVersion.getMinor() != 1) {
1864  return schemaVersion;
1865  }
1866 
1868 
1869  try (Statement statement = connection.createStatement();) {
1870  statement.execute("ALTER TABLE tsk_image_info ADD COLUMN sha1 TEXT DEFAULT NULL");
1871  statement.execute("ALTER TABLE tsk_image_info ADD COLUMN sha256 TEXT DEFAULT NULL");
1872 
1873  statement.execute("ALTER TABLE data_source_info ADD COLUMN acquisition_details TEXT");
1874 
1875  /*
1876  * Add new tsk_db_extended_info table with TSK version, creation
1877  * time schema and schema version numbers as the initial data. The
1878  * creation time schema version is set to 0, 0 to indicate that it
1879  * is not known.
1880  */
1881  statement.execute("CREATE TABLE tsk_db_info_extended (name TEXT PRIMARY KEY, value TEXT NOT NULL)");
1882  ResultSet result = statement.executeQuery("SELECT tsk_ver FROM tsk_db_info");
1883  result.next();
1884  statement.execute("INSERT INTO tsk_db_info_extended (name, value) VALUES ('" + TSK_VERSION_KEY + "', '" + result.getLong("tsk_ver") + "')");
1885  statement.execute("INSERT INTO tsk_db_info_extended (name, value) VALUES ('" + SCHEMA_MAJOR_VERSION_KEY + "', '8')");
1886  statement.execute("INSERT INTO tsk_db_info_extended (name, value) VALUES ('" + SCHEMA_MINOR_VERSION_KEY + "', '2')");
1887  statement.execute("INSERT INTO tsk_db_info_extended (name, value) VALUES ('" + CREATION_SCHEMA_MAJOR_VERSION_KEY + "', '0')");
1888  statement.execute("INSERT INTO tsk_db_info_extended (name, value) VALUES ('" + CREATION_SCHEMA_MINOR_VERSION_KEY + "', '0')");
1889 
1890  String primaryKeyType;
1891  switch (getDatabaseType()) {
1892  case POSTGRESQL:
1893  primaryKeyType = "BIGSERIAL";
1894  break;
1895  case SQLITE:
1896  primaryKeyType = "INTEGER";
1897  break;
1898  default:
1899  throw new TskCoreException("Unsupported data base type: " + getDatabaseType().toString());
1900  }
1901 
1902  //create and initialize tsk_event_types tables
1903  statement.execute("CREATE TABLE tsk_event_types ("
1904  + " event_type_id " + primaryKeyType + " PRIMARY KEY, "
1905  + " display_name TEXT UNIQUE NOT NULL, "
1906  + " super_type_id INTEGER REFERENCES tsk_event_types(event_type_id) )");
1907  statement.execute("insert into tsk_event_types(event_type_id, display_name, super_type_id)"
1908  + " values( 0, 'Event Types', null)");
1909  statement.execute("insert into tsk_event_types(event_type_id, display_name, super_type_id)"
1910  + " values(1, 'File System', 0)");
1911  statement.execute("insert into tsk_event_types(event_type_id, display_name, super_type_id)"
1912  + " values(2, 'Web Activity', 0)");
1913  statement.execute("insert into tsk_event_types(event_type_id, display_name, super_type_id)"
1914  + " values(3, 'Misc Types', 0)");
1915  statement.execute("insert into tsk_event_types(event_type_id, display_name, super_type_id)"
1916  + " values(4, 'Modified', 1)");
1917  statement.execute("insert into tsk_event_types(event_type_id, display_name, super_type_id)"
1918  + " values(5, 'Accessed', 1)");
1919  statement.execute("insert into tsk_event_types(event_type_id, display_name, super_type_id)"
1920  + " values(6, 'Created', 1)");
1921  statement.execute("insert into tsk_event_types(event_type_id, display_name, super_type_id)"
1922  + " values(7, 'Changed', 1)");
1923 
1924  //create tsk_events tables
1925  statement.execute("CREATE TABLE tsk_event_descriptions ("
1926  + " event_description_id " + primaryKeyType + " PRIMARY KEY, "
1927  + " full_description TEXT NOT NULL, "
1928  + " med_description TEXT, "
1929  + " short_description TEXT,"
1930  + " data_source_obj_id BIGINT NOT NULL, "
1931  + " file_obj_id BIGINT NOT NULL, "
1932  + " artifact_id BIGINT, "
1933  + " hash_hit INTEGER NOT NULL, " //boolean
1934  + " tagged INTEGER NOT NULL, " //boolean
1935  + " FOREIGN KEY(data_source_obj_id) REFERENCES data_source_info(obj_id), "
1936  + " FOREIGN KEY(file_obj_id) REFERENCES tsk_files(obj_id), "
1937  + " FOREIGN KEY(artifact_id) REFERENCES blackboard_artifacts(artifact_id))"
1938  );
1939 
1940  statement.execute("CREATE TABLE tsk_events ( "
1941  + " event_id " + primaryKeyType + " PRIMARY KEY, "
1942  + " event_type_id BIGINT NOT NULL REFERENCES tsk_event_types(event_type_id) ,"
1943  + " event_description_id BIGINT NOT NULL REFERENCES tsk_event_descriptions(event_description_id) ,"
1944  + " time INTEGER NOT NULL) "
1945  );
1946 
1947  //create tsk_events indices
1948  statement.execute("CREATE INDEX events_time ON tsk_events(time)");
1949  statement.execute("CREATE INDEX events_type ON tsk_events(event_type_id)");
1950  statement.execute("CREATE INDEX events_data_source_obj_id ON tsk_event_descriptions(data_source_obj_id) ");
1951  statement.execute("CREATE INDEX events_file_obj_id ON tsk_event_descriptions(file_obj_id) ");
1952  statement.execute("CREATE INDEX events_artifact_id ON tsk_event_descriptions(artifact_id) ");
1953  statement.execute("CREATE INDEX events_sub_type_time ON tsk_events(event_type_id, time) ");
1954  return new CaseDbSchemaVersionNumber(8, 2);
1955 
1956  } finally {
1958  }
1959  }
1960 
1974  private CaseDbSchemaVersionNumber updateFromSchema8dot2toSchema8dot3(CaseDbSchemaVersionNumber schemaVersion, CaseDbConnection connection) throws SQLException, TskCoreException {
1975  if (schemaVersion.getMajor() != 8) {
1976  return schemaVersion;
1977  }
1978 
1979  if (schemaVersion.getMinor() != 2) {
1980  return schemaVersion;
1981  }
1982 
1984 
1985  ResultSet resultSet = null;
1986 
1987  try (Statement statement = connection.createStatement();) {
1988 
1989  // Add the uniqueness constraint to the tsk_event and tsk_event_description tables.
1990  // Unfortunately, SQLite doesn't support adding a constraint
1991  // to an existing table so we have to rename the old...
1992  String primaryKeyType;
1993  switch (getDatabaseType()) {
1994  case POSTGRESQL:
1995  primaryKeyType = "BIGSERIAL";
1996  break;
1997  case SQLITE:
1998  primaryKeyType = "INTEGER";
1999  break;
2000  default:
2001  throw new TskCoreException("Unsupported data base type: " + getDatabaseType().toString());
2002  }
2003 
2004  //create and initialize tsk_event_types tables which may or may not exist
2005  statement.execute("CREATE TABLE IF NOT EXISTS tsk_event_types ("
2006  + " event_type_id " + primaryKeyType + " PRIMARY KEY, "
2007  + " display_name TEXT UNIQUE NOT NULL, "
2008  + " super_type_id INTEGER REFERENCES tsk_event_types(event_type_id) )");
2009 
2010  resultSet = statement.executeQuery("SELECT * from tsk_event_types");
2011 
2012  // If there is something in resultSet then the table must have previously
2013  // existing therefore there is not need to populate
2014  if (!resultSet.next()) {
2015 
2016  statement.execute("insert into tsk_event_types(event_type_id, display_name, super_type_id)"
2017  + " values( 0, 'Event Types', null)");
2018  statement.execute("insert into tsk_event_types(event_type_id, display_name, super_type_id)"
2019  + " values(1, 'File System', 0)");
2020  statement.execute("insert into tsk_event_types(event_type_id, display_name, super_type_id)"
2021  + " values(2, 'Web Activity', 0)");
2022  statement.execute("insert into tsk_event_types(event_type_id, display_name, super_type_id)"
2023  + " values(3, 'Misc Types', 0)");
2024  statement.execute("insert into tsk_event_types(event_type_id, display_name, super_type_id)"
2025  + " values(4, 'Modified', 1)");
2026  statement.execute("insert into tsk_event_types(event_type_id, display_name, super_type_id)"
2027  + " values(5, 'Accessed', 1)");
2028  statement.execute("insert into tsk_event_types(event_type_id, display_name, super_type_id)"
2029  + " values(6, 'Created', 1)");
2030  statement.execute("insert into tsk_event_types(event_type_id, display_name, super_type_id)"
2031  + " values(7, 'Changed', 1)");
2032  }
2033 
2034  // Delete the old table that may have been created with the upgrade
2035  // from 8.1 to 8.2.
2036  statement.execute("DROP TABLE IF EXISTS tsk_events");
2037 
2038  // Delete the old table that may have been created with the upgrade
2039  // from 8.1 to 8.2
2040  statement.execute("DROP TABLE IF EXISTS tsk_event_descriptions");
2041 
2042  //create new tsk_event_description table
2043  statement.execute("CREATE TABLE tsk_event_descriptions ("
2044  + " event_description_id " + primaryKeyType + " PRIMARY KEY, "
2045  + " full_description TEXT NOT NULL, "
2046  + " med_description TEXT, "
2047  + " short_description TEXT,"
2048  + " data_source_obj_id BIGINT NOT NULL, "
2049  + " file_obj_id BIGINT NOT NULL, "
2050  + " artifact_id BIGINT, "
2051  + " hash_hit INTEGER NOT NULL, " //boolean
2052  + " tagged INTEGER NOT NULL, " //boolean
2053  + " UNIQUE(full_description, file_obj_id, artifact_id), "
2054  + " FOREIGN KEY(data_source_obj_id) REFERENCES data_source_info(obj_id), "
2055  + " FOREIGN KEY(file_obj_id) REFERENCES tsk_files(obj_id), "
2056  + " FOREIGN KEY(artifact_id) REFERENCES blackboard_artifacts(artifact_id))"
2057  );
2058 
2059  // create a new table
2060  statement.execute("CREATE TABLE tsk_events ( "
2061  + " event_id " + primaryKeyType + " PRIMARY KEY, "
2062  + " event_type_id BIGINT NOT NULL REFERENCES tsk_event_types(event_type_id) ,"
2063  + " event_description_id BIGINT NOT NULL REFERENCES tsk_event_descriptions(event_description_id) ,"
2064  + " time INTEGER NOT NULL, "
2065  + " UNIQUE (event_type_id, event_description_id, time))"
2066  );
2067 
2068  // Fix mistakenly set names in tsk_db_info_extended
2069  statement.execute("UPDATE tsk_db_info_extended SET name = 'CREATION_SCHEMA_MAJOR_VERSION' WHERE name = 'CREATED_SCHEMA_MAJOR_VERSION'");
2070  statement.execute("UPDATE tsk_db_info_extended SET name = 'CREATION_SCHEMA_MINOR_VERSION' WHERE name = 'CREATED_SCHEMA_MINOR_VERSION'");
2071 
2072  return new CaseDbSchemaVersionNumber(8, 3);
2073  } finally {
2074  closeResultSet(resultSet);
2076  }
2077  }
2078 
2100  private CaseDbSchemaVersionNumber updateFromSchema8dot3toSchema8dot4(CaseDbSchemaVersionNumber schemaVersion, CaseDbConnection connection) throws SQLException, TskCoreException {
2101  if (schemaVersion.getMajor() != 8) {
2102  return schemaVersion;
2103  }
2104 
2105  if (schemaVersion.getMinor() != 3) {
2106  return schemaVersion;
2107  }
2108 
2109  Statement statement = connection.createStatement();
2110  ResultSet results = null;
2111 
2113  try {
2114  // This is a bug fix update for a misnamed column in tsk_event_descriptions in
2115  // the previous update code.
2116  if (null == getDatabaseType()) {
2117  throw new TskCoreException("Unsupported data base type: " + getDatabaseType().toString());
2118  }
2119 
2120  switch (getDatabaseType()) {
2121  case POSTGRESQL:
2122  // Check if the misnamed column is present
2123  results = statement.executeQuery("SELECT column_name FROM information_schema.columns "
2124  + "WHERE table_name='tsk_event_descriptions' and column_name='file_obj_id'");
2125  if (results.next()) {
2126  // In PostgreSQL we can rename the column if it exists
2127  statement.execute("ALTER TABLE tsk_event_descriptions "
2128  + "RENAME COLUMN file_obj_id TO content_obj_id");
2129 
2130  // In 8.2 to 8.3 upgrade, the event_id & time column in tsk_events table was erroneously created as type INTEGER, instead of BIGINT
2131  // Fix the schema, preserving any data if exists.
2132  statement.execute("CREATE TABLE temp_tsk_events ( "
2133  + " event_id BIGSERIAL PRIMARY KEY, "
2134  + " event_type_id BIGINT NOT NULL REFERENCES tsk_event_types(event_type_id) ,"
2135  + " event_description_id BIGINT NOT NULL REFERENCES tsk_event_descriptions(event_description_id),"
2136  + " time BIGINT NOT NULL, "
2137  + " UNIQUE (event_type_id, event_description_id, time))"
2138  );
2139 
2140  // Copy the data
2141  statement.execute("INSERT INTO temp_tsk_events(event_id, event_type_id, "
2142  + "event_description_id, time) SELECT * FROM tsk_events");
2143 
2144  // Drop the old table
2145  statement.execute("DROP TABLE tsk_events");
2146 
2147  // Rename the new table
2148  statement.execute("ALTER TABLE temp_tsk_events RENAME TO tsk_events");
2149 
2150  //create tsk_events indices that were skipped in the 8.2 to 8.3 update code
2151  statement.execute("CREATE INDEX events_data_source_obj_id ON tsk_event_descriptions(data_source_obj_id) ");
2152  statement.execute("CREATE INDEX events_content_obj_id ON tsk_event_descriptions(content_obj_id) ");
2153  statement.execute("CREATE INDEX events_artifact_id ON tsk_event_descriptions(artifact_id) ");
2154  statement.execute("CREATE INDEX events_sub_type_time ON tsk_events(event_type_id, time) ");
2155  statement.execute("CREATE INDEX events_time ON tsk_events(time) ");
2156  }
2157  break;
2158  case SQLITE:
2159  boolean hasMisnamedColumn = false;
2160  results = statement.executeQuery("pragma table_info('tsk_event_descriptions')");
2161  while (results.next()) {
2162  if (results.getString("name") != null && results.getString("name").equals("file_obj_id")) {
2163  hasMisnamedColumn = true;
2164  break;
2165  }
2166  }
2167 
2168  if (hasMisnamedColumn) {
2169  // Since we can't rename the column we'll need to make new tables and copy the data
2170  statement.execute("CREATE TABLE temp_tsk_event_descriptions ("
2171  + " event_description_id INTEGER PRIMARY KEY, "
2172  + " full_description TEXT NOT NULL, "
2173  + " med_description TEXT, "
2174  + " short_description TEXT,"
2175  + " data_source_obj_id BIGINT NOT NULL, "
2176  + " content_obj_id BIGINT NOT NULL, "
2177  + " artifact_id BIGINT, "
2178  + " hash_hit INTEGER NOT NULL, " //boolean
2179  + " tagged INTEGER NOT NULL, " //boolean
2180  + " UNIQUE(full_description, content_obj_id, artifact_id), "
2181  + " FOREIGN KEY(data_source_obj_id) REFERENCES data_source_info(obj_id), "
2182  + " FOREIGN KEY(content_obj_id) REFERENCES tsk_files(obj_id), "
2183  + " FOREIGN KEY(artifact_id) REFERENCES blackboard_artifacts(artifact_id))"
2184  );
2185 
2186  statement.execute("CREATE TABLE temp_tsk_events ( "
2187  + " event_id INTEGER PRIMARY KEY, "
2188  + " event_type_id BIGINT NOT NULL REFERENCES tsk_event_types(event_type_id) ,"
2189  + " event_description_id BIGINT NOT NULL REFERENCES temp_tsk_event_descriptions(event_description_id),"
2190  + " time INTEGER NOT NULL, "
2191  + " UNIQUE (event_type_id, event_description_id, time))"
2192  );
2193 
2194  // Copy the data
2195  statement.execute("INSERT INTO temp_tsk_event_descriptions(event_description_id, full_description, "
2196  + "med_description, short_description, data_source_obj_id, content_obj_id, artifact_id, "
2197  + "hash_hit, tagged) SELECT * FROM tsk_event_descriptions");
2198 
2199  statement.execute("INSERT INTO temp_tsk_events(event_id, event_type_id, "
2200  + "event_description_id, time) SELECT * FROM tsk_events");
2201 
2202  // Drop the old tables
2203  statement.execute("DROP TABLE tsk_events");
2204  statement.execute("DROP TABLE tsk_event_descriptions");
2205 
2206  // Rename the new tables
2207  statement.execute("ALTER TABLE temp_tsk_event_descriptions RENAME TO tsk_event_descriptions");
2208  statement.execute("ALTER TABLE temp_tsk_events RENAME TO tsk_events");
2209 
2210  //create tsk_events indices
2211  statement.execute("CREATE INDEX events_data_source_obj_id ON tsk_event_descriptions(data_source_obj_id) ");
2212  statement.execute("CREATE INDEX events_content_obj_id ON tsk_event_descriptions(content_obj_id) ");
2213  statement.execute("CREATE INDEX events_artifact_id ON tsk_event_descriptions(artifact_id) ");
2214  statement.execute("CREATE INDEX events_sub_type_time ON tsk_events(event_type_id, time) ");
2215  statement.execute("CREATE INDEX events_time ON tsk_events(time) ");
2216  }
2217  break;
2218  default:
2219  throw new TskCoreException("Unsupported data base type: " + getDatabaseType().toString());
2220  }
2221 
2222  // create pool info table
2223  if (this.dbType.equals(DbType.SQLITE)) {
2224  statement.execute("CREATE TABLE tsk_pool_info (obj_id INTEGER PRIMARY KEY, pool_type INTEGER NOT NULL, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
2225  } else {
2226  statement.execute("CREATE TABLE tsk_pool_info (obj_id BIGSERIAL PRIMARY KEY, pool_type INTEGER NOT NULL, FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE)");
2227  }
2228 
2229  // Add new account types for newly supported messaging applications, if they dont exists already.
2230  insertAccountTypeIfNotExists(statement, "IMO", "IMO");
2231  insertAccountTypeIfNotExists(statement, "LINE", "LINE");
2232  insertAccountTypeIfNotExists(statement, "SKYPE", "Skype");
2233  insertAccountTypeIfNotExists(statement, "TANGO", "Tango");
2234  insertAccountTypeIfNotExists(statement, "TEXTNOW", "TextNow");
2235  insertAccountTypeIfNotExists(statement, "THREEMA", "ThreeMa");
2236  insertAccountTypeIfNotExists(statement, "VIBER", "Viber");
2237  insertAccountTypeIfNotExists(statement, "XENDER", "Xender");
2238  insertAccountTypeIfNotExists(statement, "ZAPYA", "Zapya");
2239  insertAccountTypeIfNotExists(statement, "SHAREIT", "ShareIt");
2240 
2241  return new CaseDbSchemaVersionNumber(8, 4);
2242  } finally {
2243  closeResultSet(results);
2244  closeStatement(statement);
2246  }
2247  }
2248 
2249  private CaseDbSchemaVersionNumber updateFromSchema8dot4toSchema8dot5(CaseDbSchemaVersionNumber schemaVersion, CaseDbConnection connection) throws SQLException, TskCoreException {
2250  if (schemaVersion.getMajor() != 8) {
2251  return schemaVersion;
2252  }
2253 
2254  if (schemaVersion.getMinor() != 4) {
2255  return schemaVersion;
2256  }
2257 
2258  Statement statement = connection.createStatement();
2260  try {
2261  switch (getDatabaseType()) {
2262  case POSTGRESQL:
2263  statement.execute("CREATE TABLE tsk_tag_sets (tag_set_id BIGSERIAL PRIMARY KEY, name TEXT UNIQUE)");
2264  statement.execute("ALTER TABLE tag_names ADD COLUMN tag_set_id BIGINT REFERENCES tsk_tag_sets(tag_set_id)");
2265  break;
2266  case SQLITE:
2267  statement.execute("CREATE TABLE tsk_tag_sets (tag_set_id INTEGER PRIMARY KEY, name TEXT UNIQUE)");
2268  statement.execute("ALTER TABLE tag_names ADD COLUMN tag_set_id INTEGER REFERENCES tsk_tag_sets(tag_set_id)");
2269  break;
2270  }
2271 
2272  statement.execute("ALTER TABLE tag_names ADD COLUMN rank INTEGER");
2273 
2274  /*
2275  * Update existing Project Vic tag names (from Image Gallery in
2276  * Autopsy) to be part of a Tag Set. NOTE: These names are out of
2277  * date and will not work with the Project VIC Report module. New
2278  * cases will get the new names from Image Gallery.
2279  */
2280  String insertStmt = "INSERT INTO tsk_tag_sets (name) VALUES ('Project VIC')";
2281  if (getDatabaseType() == DbType.POSTGRESQL) {
2282  statement.execute(insertStmt, Statement.RETURN_GENERATED_KEYS);
2283  } else {
2284  statement.execute(insertStmt);
2285  }
2286  try (ResultSet resultSet = statement.getGeneratedKeys()) {
2287  if (resultSet != null && resultSet.next()) {
2288  int tagSetId = resultSet.getInt(1);
2289 
2290  String updateQuery = "UPDATE tag_names SET tag_set_id = %d, color = '%s', rank = %d, display_name = '%s' WHERE display_name = '%s'";
2291  statement.executeUpdate(String.format(updateQuery, tagSetId, "Red", 1, "Child Exploitation (Illegal)", "CAT-1: Child Exploitation (Illegal)"));
2292  statement.executeUpdate(String.format(updateQuery, tagSetId, "Lime", 2, "Child Exploitation (Non-Illegal/Age Difficult)", "CAT-2: Child Exploitation (Non-Illegal/Age Difficult)"));
2293  statement.executeUpdate(String.format(updateQuery, tagSetId, "Yellow", 3, "CGI/Animation (Child Exploitive)", "CAT-3: CGI/Animation (Child Exploitive)"));
2294  statement.executeUpdate(String.format(updateQuery, tagSetId, "Purple", 4, "Exemplar/Comparison (Internal Use Only)", "CAT-4: Exemplar/Comparison (Internal Use Only)"));
2295  statement.executeUpdate(String.format(updateQuery, tagSetId, "Fuchsia", 5, "Non-pertinent", "CAT-5: Non-pertinent"));
2296 
2297  String deleteContentTag = "DELETE FROM content_tags WHERE tag_name_id IN (SELECT tag_name_id from tag_names WHERE display_name LIKE 'CAT-0: Uncategorized')";
2298  String deleteArtifactTag = "DELETE FROM blackboard_artifact_tags WHERE tag_name_id IN (SELECT tag_name_id from tag_names WHERE display_name LIKE 'CAT-0: Uncategorized')";
2299  String deleteCat0 = "DELETE FROM tag_names WHERE display_name = 'CAT-0: Uncategorized'";
2300  statement.executeUpdate(deleteContentTag);
2301  statement.executeUpdate(deleteArtifactTag);
2302  statement.executeUpdate(deleteCat0);
2303 
2304  } else {
2305  throw new TskCoreException("Failed to retrieve the default tag_set_id from DB");
2306  }
2307  }
2308 
2309  // Add data_source_obj_id column to the tsk_files table. For newly created cases
2310  // this column will have a foreign key constraint on the data_source_info table.
2311  // There does not seem to be a reasonable way to do this in an upgrade,
2312  // so upgraded cases will be missing the foreign key.
2313  switch (getDatabaseType()) {
2314  case POSTGRESQL:
2315  statement.execute("ALTER TABLE tsk_fs_info ADD COLUMN data_source_obj_id BIGINT NOT NULL DEFAULT -1;");
2316  break;
2317  case SQLITE:
2318  statement.execute("ALTER TABLE tsk_fs_info ADD COLUMN data_source_obj_id INTEGER NOT NULL DEFAULT -1;");
2319  break;
2320  }
2321  Statement updateStatement = connection.createStatement();
2322  try (ResultSet resultSet = statement.executeQuery("SELECT obj_id FROM tsk_fs_info")) {
2323  while (resultSet.next()) {
2324  long fsId = resultSet.getLong("obj_id");
2325  long dataSourceId = getDataSourceObjectId(connection, fsId);
2326  updateStatement.executeUpdate("UPDATE tsk_fs_info SET data_source_obj_id = " + dataSourceId + " WHERE obj_id = " + fsId + ";");
2327  }
2328  } finally {
2329  closeStatement(updateStatement);
2330  }
2331 
2332  return new CaseDbSchemaVersionNumber(8, 5);
2333 
2334  } finally {
2335  closeStatement(statement);
2337  }
2338  }
2339 
2340  private CaseDbSchemaVersionNumber updateFromSchema8dot5toSchema8dot6(CaseDbSchemaVersionNumber schemaVersion, CaseDbConnection connection) throws SQLException, TskCoreException {
2341  if (schemaVersion.getMajor() != 8) {
2342  return schemaVersion;
2343  }
2344 
2345  if (schemaVersion.getMinor() != 5) {
2346  return schemaVersion;
2347  }
2348 
2349  Statement statement = connection.createStatement();
2351  try {
2352  statement.execute("ALTER TABLE tsk_files ADD COLUMN sha256 TEXT");
2353 
2354  return new CaseDbSchemaVersionNumber(8, 6);
2355 
2356  } finally {
2357  closeStatement(statement);
2359  }
2360  }
2361 
2362  @SuppressWarnings("deprecation")
2363  private CaseDbSchemaVersionNumber updateFromSchema8dot6toSchema9dot0(CaseDbSchemaVersionNumber schemaVersion, CaseDbConnection connection) throws SQLException, TskCoreException {
2364  if (schemaVersion.getMajor() != 8) {
2365  return schemaVersion;
2366  }
2367 
2368  if (schemaVersion.getMinor() != 6) {
2369  return schemaVersion;
2370  }
2371 
2372  Statement statement = connection.createStatement();
2374  try {
2375  String dateDataType = "BIGINT";
2376  String bigIntDataType = "BIGINT";
2377  String blobDataType = "BYTEA";
2378  String primaryKeyType = "BIGSERIAL";
2379 
2380  if (this.dbType.equals(DbType.SQLITE)) {
2381  dateDataType = "INTEGER";
2382  bigIntDataType = "INTEGER";
2383  blobDataType = "BLOB";
2384  primaryKeyType = "INTEGER";
2385  }
2386  statement.execute("ALTER TABLE data_source_info ADD COLUMN added_date_time " + dateDataType);
2387  statement.execute("ALTER TABLE data_source_info ADD COLUMN acquisition_tool_settings TEXT");
2388  statement.execute("ALTER TABLE data_source_info ADD COLUMN acquisition_tool_name TEXT");
2389  statement.execute("ALTER TABLE data_source_info ADD COLUMN acquisition_tool_version TEXT");
2390 
2391  // Add category type and initialize the types. We use the list of artifact types that
2392  // were categorized as analysis results as of the 8.7 update to ensure consistency in
2393  // case the built-in types change in a later release.
2394  statement.execute("ALTER TABLE blackboard_artifact_types ADD COLUMN category_type INTEGER DEFAULT 0");
2395  String analysisTypeObjIdList
2396  = BlackboardArtifact.ARTIFACT_TYPE.TSK_KEYWORD_HIT.getTypeID() + ", "
2397  + BlackboardArtifact.ARTIFACT_TYPE.TSK_HASHSET_HIT.getTypeID() + ", "
2398  + BlackboardArtifact.ARTIFACT_TYPE.TSK_INTERESTING_FILE_HIT.getTypeID() + ", "
2399  + BlackboardArtifact.ARTIFACT_TYPE.TSK_TAG_FILE.getTypeID() + ", "
2400  + BlackboardArtifact.ARTIFACT_TYPE.TSK_TAG_ARTIFACT.getTypeID() + ", "
2401  + BlackboardArtifact.ARTIFACT_TYPE.TSK_ENCRYPTION_DETECTED.getTypeID() + ", "
2402  + BlackboardArtifact.ARTIFACT_TYPE.TSK_EXT_MISMATCH_DETECTED.getTypeID() + ", "
2403  + BlackboardArtifact.ARTIFACT_TYPE.TSK_INTERESTING_ARTIFACT_HIT.getTypeID() + ", "
2404  + BlackboardArtifact.ARTIFACT_TYPE.TSK_FACE_DETECTED.getTypeID() + ", "
2405  + BlackboardArtifact.ARTIFACT_TYPE.TSK_ENCRYPTION_SUSPECTED.getTypeID() + ", "
2406  + BlackboardArtifact.ARTIFACT_TYPE.TSK_OBJECT_DETECTED.getTypeID() + ", "
2407  + BlackboardArtifact.ARTIFACT_TYPE.TSK_VERIFICATION_FAILED.getTypeID() + ", "
2408  + BlackboardArtifact.ARTIFACT_TYPE.TSK_DATA_SOURCE_USAGE.getTypeID() + ", "
2409  + BlackboardArtifact.ARTIFACT_TYPE.TSK_USER_CONTENT_SUSPECTED.getTypeID() + ", "
2410  + BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_ACCOUNT_TYPE.getTypeID() + ", "
2411  + BlackboardArtifact.ARTIFACT_TYPE.TSK_YARA_HIT.getTypeID() + ", "
2412  + BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_CATEGORIZATION.getTypeID();
2413  statement.execute("UPDATE blackboard_artifact_types SET category_type = " + BlackboardArtifact.Category.ANALYSIS_RESULT.getID()
2414  + " WHERE artifact_type_id IN (" + analysisTypeObjIdList + ")");
2415 
2416  // Create tsk file attributes table
2417  statement.execute("CREATE TABLE tsk_file_attributes (id " + primaryKeyType + " PRIMARY KEY, "
2418  + "obj_id " + bigIntDataType + " NOT NULL, "
2419  + "attribute_type_id " + bigIntDataType + " NOT NULL, "
2420  + "value_type INTEGER NOT NULL, value_byte " + blobDataType + ", "
2421  + "value_text TEXT, value_int32 INTEGER, value_int64 " + bigIntDataType + ", value_double NUMERIC(20, 10), "
2422  + "FOREIGN KEY(obj_id) REFERENCES tsk_files(obj_id) ON DELETE CASCADE, "
2423  + "FOREIGN KEY(attribute_type_id) REFERENCES blackboard_attribute_types(attribute_type_id))");
2424 
2425  // create analysis results tables
2426  statement.execute("CREATE TABLE tsk_analysis_results (artifact_obj_id " + bigIntDataType + " PRIMARY KEY, "
2427  + "conclusion TEXT, "
2428  + "significance INTEGER NOT NULL, "
2429  /*
2430  * method_category was a column in a little distributed
2431  * version of 9.0. It was renamed to priority before public
2432  * release. The 9.1 upgrade code will add the priority
2433  * column. This is commented out since it was never used.
2434  */
2435  // + "method_category INTEGER NOT NULL, "
2436  + "configuration TEXT, justification TEXT, "
2437  + "ignore_score INTEGER DEFAULT 0 " // boolean
2438  + ")");
2439 
2440  statement.execute("CREATE TABLE tsk_aggregate_score( obj_id " + bigIntDataType + " PRIMARY KEY, "
2441  + "data_source_obj_id " + bigIntDataType + ", "
2442  + "significance INTEGER NOT NULL, "
2443  // See comment above on why this is commented out
2444  // + "method_category INTEGER NOT NULL, "
2445  + "UNIQUE (obj_id),"
2446  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
2447  + "FOREIGN KEY(data_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE "
2448  + ")");
2449 
2450  // Create person table.
2451  statement.execute("CREATE TABLE tsk_persons (id " + primaryKeyType + " PRIMARY KEY, "
2452  + "name TEXT NOT NULL, " // person name
2453  + "UNIQUE(name)) ");
2454 
2455  // Create host table.
2456  statement.execute("CREATE TABLE tsk_hosts (id " + primaryKeyType + " PRIMARY KEY, "
2457  + "name TEXT NOT NULL, " // host name
2458  + "db_status INTEGER DEFAULT 0, " // active/merged/deleted
2459  + "person_id INTEGER, "
2460  + "merged_into " + bigIntDataType + ", "
2461  + "FOREIGN KEY(person_id) REFERENCES tsk_persons(id) ON DELETE SET NULL, "
2462  + "FOREIGN KEY(merged_into) REFERENCES tsk_hosts(id), "
2463  + "UNIQUE(name)) ");
2464 
2465  // Create OS Account and related tables
2466  statement.execute("CREATE TABLE tsk_os_account_realms (id " + primaryKeyType + " PRIMARY KEY, "
2467  + "realm_name TEXT DEFAULT NULL, " // realm name - for a domain realm, may be null
2468  + "realm_addr TEXT DEFAULT NULL, " // a sid/uid or some some other identifier, may be null
2469  + "realm_signature TEXT NOT NULL, " // Signature exists only to prevent duplicates. It is made up of realm address/name and scope host
2470  + "scope_host_id " + bigIntDataType + " DEFAULT NULL, " // if the realm scope is a single host
2471  + "scope_confidence INTEGER, " // indicates whether we know for sure the realm scope or if we are inferring it
2472  + "db_status INTEGER DEFAULT 0, " // active/merged/deleted
2473  + "merged_into " + bigIntDataType + " DEFAULT NULL, "
2474  + "UNIQUE(realm_signature), "
2475  + "FOREIGN KEY(scope_host_id) REFERENCES tsk_hosts(id),"
2476  + "FOREIGN KEY(merged_into) REFERENCES tsk_os_account_realms(id) )");
2477 
2478  // Add host column and create a host for each existing data source.
2479  // We will create a host for each device id so that related data sources will
2480  // be associated with the same host.
2481  statement.execute("ALTER TABLE data_source_info ADD COLUMN host_id INTEGER REFERENCES tsk_hosts(id)");
2482  Statement updateStatement = connection.createStatement();
2483  try (ResultSet resultSet = statement.executeQuery("SELECT obj_id, device_id FROM data_source_info")) {
2484  Map<String, Long> hostMap = new HashMap<>();
2485  long hostIndex = 1;
2486  while (resultSet.next()) {
2487  long objId = resultSet.getLong("obj_id");
2488  String deviceId = resultSet.getString("device_id");
2489 
2490  if (!hostMap.containsKey(deviceId)) {
2491  String hostName = "Host " + hostIndex;
2492  updateStatement.execute("INSERT INTO tsk_hosts (name, db_status) VALUES ('" + hostName + "', 0)");
2493  hostMap.put(deviceId, hostIndex);
2494  hostIndex++;
2495  }
2496  updateStatement.execute("UPDATE data_source_info SET host_id = " + hostMap.get(deviceId) + " WHERE obj_id = " + objId);
2497  }
2498  } finally {
2499  closeStatement(updateStatement);
2500  }
2501 
2502  statement.execute("CREATE TABLE tsk_os_accounts (os_account_obj_id " + bigIntDataType + " PRIMARY KEY, "
2503  + "login_name TEXT DEFAULT NULL, " // login name, if available, may be null
2504  + "full_name TEXT DEFAULT NULL, " // full name, if available, may be null
2505  + "realm_id " + bigIntDataType + " NOT NULL, " // realm for the account
2506  + "addr TEXT DEFAULT NULL, " // SID/UID, if available
2507  + "signature TEXT NOT NULL, " // This exists only to prevent duplicates. It is either the addr or the login_name whichever is not null.
2508  + "status INTEGER, " // enabled/disabled/deleted
2509  + "type INTEGER, " // service/interactive
2510  + "created_date " + bigIntDataType + " DEFAULT NULL, "
2511  + "db_status INTEGER DEFAULT 0, " // active/merged/deleted
2512  + "merged_into " + bigIntDataType + " DEFAULT NULL, "
2513  + "UNIQUE(signature, realm_id), "
2514  + "FOREIGN KEY(os_account_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
2515  + "FOREIGN KEY(realm_id) REFERENCES tsk_os_account_realms(id),"
2516  + "FOREIGN KEY(merged_into) REFERENCES tsk_os_accounts(os_account_obj_id) )");
2517 
2518  statement.execute("CREATE TABLE tsk_os_account_attributes (id " + primaryKeyType + " PRIMARY KEY, "
2519  + "os_account_obj_id " + bigIntDataType + " NOT NULL, "
2520  + "host_id " + bigIntDataType + ", "
2521  + "source_obj_id " + bigIntDataType + ", "
2522  + "attribute_type_id " + bigIntDataType + " NOT NULL, "
2523  + "value_type INTEGER NOT NULL, "
2524  + "value_byte " + bigIntDataType + ", "
2525  + "value_text TEXT, "
2526  + "value_int32 INTEGER, value_int64 " + bigIntDataType + ", "
2527  + "value_double NUMERIC(20, 10), "
2528  + "FOREIGN KEY(os_account_obj_id) REFERENCES tsk_os_accounts(os_account_obj_id), "
2529  + "FOREIGN KEY(host_id) REFERENCES tsk_hosts(id), "
2530  + "FOREIGN KEY(source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE SET NULL, "
2531  + "FOREIGN KEY(attribute_type_id) REFERENCES blackboard_attribute_types(attribute_type_id))");
2532 
2533  statement.execute("CREATE TABLE tsk_os_account_instances (id " + primaryKeyType + " PRIMARY KEY, "
2534  + "os_account_obj_id " + bigIntDataType + " NOT NULL, "
2535  + "data_source_obj_id " + bigIntDataType + " NOT NULL, "
2536  + "instance_type INTEGER NOT NULL, " // PerformedActionOn/ReferencedOn
2537  + "UNIQUE(os_account_obj_id, data_source_obj_id), "
2538  + "FOREIGN KEY(os_account_obj_id) REFERENCES tsk_os_accounts(os_account_obj_id), "
2539  + "FOREIGN KEY(data_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE )");
2540 
2541  statement.execute("CREATE TABLE tsk_data_artifacts ( "
2542  + "artifact_obj_id " + bigIntDataType + " PRIMARY KEY, "
2543  + "os_account_obj_id " + bigIntDataType + ", "
2544  + "FOREIGN KEY(os_account_obj_id) REFERENCES tsk_os_accounts(os_account_obj_id)) ");
2545 
2546  // add owner_uid & os_account_obj_id columns to tsk_files
2547  statement.execute("ALTER TABLE tsk_files ADD COLUMN owner_uid TEXT DEFAULT NULL");
2548  statement.execute("ALTER TABLE tsk_files ADD COLUMN os_account_obj_id " + bigIntDataType + " DEFAULT NULL REFERENCES tsk_os_accounts(os_account_obj_id) ");
2549 
2550  // create host address tables
2551  statement.execute("CREATE TABLE tsk_host_addresses (id " + primaryKeyType + " PRIMARY KEY, "
2552  + "address_type INTEGER NOT NULL, "
2553  + "address TEXT NOT NULL, "
2554  + "UNIQUE(address_type, address)) ");
2555 
2556  statement.execute("CREATE TABLE tsk_host_address_map (id " + primaryKeyType + " PRIMARY KEY, "
2557  + "host_id " + bigIntDataType + " NOT NULL, "
2558  + "addr_obj_id " + bigIntDataType + " NOT NULL, "
2559  + "source_obj_id " + bigIntDataType + ", " // object id of the source where this mapping was found.
2560  + "time " + bigIntDataType + ", " // time at which the mapping existed
2561  + "UNIQUE(host_id, addr_obj_id, time), "
2562  + "FOREIGN KEY(host_id) REFERENCES tsk_hosts(id) ON DELETE CASCADE, "
2563  + "FOREIGN KEY(addr_obj_id) REFERENCES tsk_host_addresses(id), "
2564  + "FOREIGN KEY(source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE SET NULL )");
2565 
2566  // stores associations between DNS name and IP address
2567  statement.execute("CREATE TABLE tsk_host_address_dns_ip_map (id " + primaryKeyType + " PRIMARY KEY, "
2568  + "dns_address_id " + bigIntDataType + " NOT NULL, "
2569  + "ip_address_id " + bigIntDataType + " NOT NULL, "
2570  + "source_obj_id " + bigIntDataType + ", "
2571  + "time " + bigIntDataType + ", " // time at which the mapping existed
2572  + "UNIQUE(dns_address_id, ip_address_id, time), "
2573  + "FOREIGN KEY(dns_address_id) REFERENCES tsk_host_addresses(id) ON DELETE CASCADE, "
2574  + "FOREIGN KEY(ip_address_id) REFERENCES tsk_host_addresses(id) ON DELETE CASCADE,"
2575  + "FOREIGN KEY(source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE SET NULL )");
2576 
2577  // maps an address to an artifact using it
2578  statement.execute("CREATE TABLE tsk_host_address_usage (id " + primaryKeyType + " PRIMARY KEY, "
2579  + "addr_obj_id " + bigIntDataType + " NOT NULL, "
2580  + "obj_id " + bigIntDataType + " NOT NULL, "
2581  + "data_source_obj_id " + bigIntDataType + " NOT NULL, " // data source where the usage was found
2582  + "UNIQUE(addr_obj_id, obj_id), "
2583  + "FOREIGN KEY(addr_obj_id) REFERENCES tsk_host_addresses(id) ON DELETE CASCADE, "
2584  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE )");
2585 
2586  return new CaseDbSchemaVersionNumber(9, 0);
2587 
2588  } finally {
2589  closeStatement(statement);
2591  }
2592  }
2593 
2594  private CaseDbSchemaVersionNumber updateFromSchema9dot0toSchema9dot1(CaseDbSchemaVersionNumber schemaVersion, CaseDbConnection connection) throws SQLException, TskCoreException {
2595  if (schemaVersion.getMajor() != 9) {
2596  return schemaVersion;
2597  }
2598 
2599  if (schemaVersion.getMinor() != 0) {
2600  return schemaVersion;
2601  }
2602 
2603  Statement statement = connection.createStatement();
2604  ResultSet results = null;
2606  try {
2607  // The 9.0 schema contained method_category columns that were renamed to priority.
2608  switch (getDatabaseType()) {
2609  case POSTGRESQL:
2610  // Check if the misnamed column is present. We'll assume here that the column will exist
2611  // in both tables if present in one.
2612  results = statement.executeQuery("SELECT column_name FROM information_schema.columns "
2613  + "WHERE table_name='tsk_analysis_results' and column_name='method_category'");
2614  if (results.next()) {
2615  // In PostgreSQL we can delete the column
2616  statement.execute("ALTER TABLE tsk_analysis_results "
2617  + "DROP COLUMN method_category");
2618  statement.execute("ALTER TABLE tsk_aggregate_score "
2619  + "DROP COLUMN method_category");
2620  }
2621  break;
2622  case SQLITE:
2623  // Check if the misnamed column is present. We'll assume here that the column will exist
2624  // in both tables if present in one.
2625  boolean hasMisnamedColumn = false;
2626  results = statement.executeQuery("pragma table_info('tsk_analysis_results')");
2627  while (results.next()) {
2628  if (results.getString("name") != null && results.getString("name").equals("method_category")) {
2629  hasMisnamedColumn = true;
2630  break;
2631  }
2632  }
2633 
2634  if (hasMisnamedColumn) {
2635  // Since we can't rename the column we'll need to make a new table and copy the data.
2636  // We'll add the priority column later.
2637  statement.execute("CREATE TABLE temp_tsk_analysis_results (artifact_obj_id INTEGER PRIMARY KEY, "
2638  + "conclusion TEXT, "
2639  + "significance INTEGER NOT NULL, "
2640  + "configuration TEXT, justification TEXT, "
2641  + "ignore_score INTEGER DEFAULT 0 " // boolean
2642  + ")");
2643  statement.execute("CREATE TABLE temp_tsk_aggregate_score( obj_id INTEGER PRIMARY KEY, "
2644  + "data_source_obj_id INTEGER, "
2645  + "significance INTEGER NOT NULL, "
2646  + "UNIQUE (obj_id),"
2647  + "FOREIGN KEY(obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE, "
2648  + "FOREIGN KEY(data_source_obj_id) REFERENCES tsk_objects(obj_id) ON DELETE CASCADE "
2649  + ")");
2650 
2651  // Copy the data
2652  statement.execute("INSERT INTO temp_tsk_analysis_results(artifact_obj_id, "
2653  + "conclusion, justification, significance, configuration, ignore_score) "
2654  + "SELECT artifact_obj_id, conclusion, justification, significance, configuration, ignore_score FROM tsk_analysis_results");
2655  statement.execute("INSERT INTO temp_tsk_aggregate_score(obj_id, "
2656  + "data_source_obj_id, significance) "
2657  + "SELECT obj_id, data_source_obj_id, significance FROM tsk_aggregate_score");
2658 
2659  // Drop the old tables
2660  statement.execute("DROP TABLE tsk_analysis_results");
2661  statement.execute("DROP TABLE tsk_aggregate_score");
2662 
2663  // Rename the new tables
2664  statement.execute("ALTER TABLE temp_tsk_analysis_results RENAME TO tsk_analysis_results");
2665  statement.execute("ALTER TABLE temp_tsk_aggregate_score RENAME TO tsk_aggregate_score");
2666 
2667  }
2668  break;
2669  default:
2670  throw new TskCoreException("Unsupported database type: " + getDatabaseType().toString());
2671  }
2672 
2673  // add an index on tsk_file_attributes table.
2674  statement.execute("CREATE INDEX tsk_file_attributes_obj_id ON tsk_file_attributes(obj_id)");
2675 
2676  statement.execute("ALTER TABLE tsk_analysis_results ADD COLUMN priority INTEGER NOT NULL DEFAULT " + Score.Priority.NORMAL.getId());
2677  statement.execute("ALTER TABLE tsk_aggregate_score ADD COLUMN priority INTEGER NOT NULL DEFAULT " + Score.Priority.NORMAL.getId());
2678 
2679  statement.execute("UPDATE blackboard_artifact_types SET category_type = 1 WHERE artifact_type_id = 16");
2680 
2681  return new CaseDbSchemaVersionNumber(9, 1);
2682  } finally {
2683  closeResultSet(results);
2684  closeStatement(statement);
2686  }
2687  }
2688 
2700  private void insertAccountTypeIfNotExists(Statement statement, String type_name, String display_name) throws TskCoreException, SQLException {
2701 
2702  String insertSQL = String.format("INTO account_types(type_name, display_name) VALUES ('%s', '%s')", type_name, display_name);
2703  switch (getDatabaseType()) {
2704  case POSTGRESQL:
2705  insertSQL = "INSERT " + insertSQL + " ON CONFLICT DO NOTHING"; //NON-NLS
2706  break;
2707  case SQLITE:
2708  insertSQL = "INSERT OR IGNORE " + insertSQL;
2709  break;
2710  default:
2711  throw new TskCoreException("Unknown DB Type: " + getDatabaseType().name());
2712  }
2713  statement.execute(insertSQL); //NON-NLS
2714  }
2715 
2723  static String extractExtension(final String fileName) {
2724  String ext;
2725  int i = fileName.lastIndexOf(".");
2726  // > 0 because we assume it's not an extension if period is the first character
2727  if ((i > 0) && ((i + 1) < fileName.length())) {
2728  ext = fileName.substring(i + 1);
2729  } else {
2730  return "";
2731  }
2732  // we added this at one point to deal with files that had crazy names based on URLs
2733  // it's too hard though to clean those up and not mess up basic extensions though.
2734  // We need to add '-' to the below if we use it again
2735  // String[] findNonAlphanumeric = ext.split("[^a-zA-Z0-9_]");
2736  // if (findNonAlphanumeric.length > 1) {
2737  // ext = findNonAlphanumeric[0];
2738  // }
2739  return ext.toLowerCase();
2740  }
2741 
2752  @Deprecated
2753  public int getSchemaVersion() {
2754  return getDBSchemaVersion().getMajor();
2755  }
2756 
2763  return CURRENT_DB_SCHEMA_VERSION;
2764  }
2765 
2773  return caseDBSchemaCreationVersion;
2774  }
2775 
2782  return this.dbType;
2783  }
2784 
2791  public String getBackupDatabasePath() {
2792  return dbBackupPath;
2793  }
2794 
2809  public CaseDbTransaction beginTransaction() throws TskCoreException {
2810  return new CaseDbTransaction(this);
2811  }
2812 
2818  public String getDatabaseName() {
2819  return databaseName;
2820  }
2821 
2828  public String getDbDirPath() {
2829  return caseDirPath;
2830  }
2831 
2838  if (dbType == DbType.SQLITE) {
2839  rwLock.writeLock().lock();
2840  }
2841  }
2842 
2849  if (dbType == DbType.SQLITE) {
2850  rwLock.writeLock().unlock();
2851  }
2852  }
2853 
2860  if (dbType == DbType.SQLITE) {
2861  rwLock.readLock().lock();
2862  }
2863  }
2864 
2871  if (dbType == DbType.SQLITE) {
2872  rwLock.readLock().unlock();
2873  }
2874  }
2875 
2885  public static SleuthkitCase openCase(String dbPath) throws TskCoreException {
2886  try {
2887  final SleuthkitJNI.CaseDbHandle caseHandle = SleuthkitJNI.openCaseDb(dbPath);
2888  return new SleuthkitCase(dbPath, caseHandle, DbType.SQLITE);
2890  //don't wrap in new TskCoreException
2891  throw ex;
2892  } catch (Exception ex) {
2893  throw new TskCoreException("Failed to open case database at " + dbPath, ex);
2894  }
2895  }
2896 
2908  public static SleuthkitCase openCase(String databaseName, CaseDbConnectionInfo info, String caseDir) throws TskCoreException {
2909  try {
2910  /*
2911  * The flow of this method involves trying to open case and if
2912  * successful, return that case. If unsuccessful, an exception is
2913  * thrown. We catch any exceptions, and use tryConnect() to attempt
2914  * to obtain further information about the error. If tryConnect() is
2915  * unable to successfully connect, tryConnect() will throw a
2916  * TskCoreException with a message containing user-level error
2917  * reporting. If tryConnect() is able to connect, flow continues and
2918  * we rethrow the original exception obtained from trying to create
2919  * the case. In this way, we obtain more detailed information if we
2920  * are able, but do not lose any information if unable.
2921  */
2922  final SleuthkitJNI.CaseDbHandle caseHandle = SleuthkitJNI.openCaseDb(databaseName, info);
2923  return new SleuthkitCase(info.getHost(), Integer.parseInt(info.getPort()), databaseName, info.getUserName(), info.getPassword(), caseHandle, caseDir, info.getDbType());
2924  } catch (PropertyVetoException exp) {
2925  // In this case, the JDBC driver doesn't support PostgreSQL. Use the generic message here.
2926  throw new TskCoreException(exp.getMessage(), exp);
2928  //don't wrap in new TskCoreException
2929  throw ex;
2930  } catch (Exception exp) {
2931  tryConnect(info); // attempt to connect, throw with user-friendly message if unable
2932  throw new TskCoreException(exp.getMessage(), exp); // throw with generic message if tryConnect() was successful
2933  }
2934  }
2935 
2945  public static SleuthkitCase newCase(String dbPath) throws TskCoreException {
2946  try {
2947  CaseDatabaseFactory factory = new CaseDatabaseFactory(dbPath);
2948  factory.createCaseDatabase();
2949 
2950  SleuthkitJNI.CaseDbHandle caseHandle = SleuthkitJNI.openCaseDb(dbPath);
2951  return new SleuthkitCase(dbPath, caseHandle, DbType.SQLITE);
2952  } catch (Exception ex) {
2953  throw new TskCoreException("Failed to create case database at " + dbPath, ex);
2954  }
2955  }
2956 
2972  public static SleuthkitCase newCase(String caseName, CaseDbConnectionInfo info, String caseDirPath) throws TskCoreException {
2973  String databaseName = createCaseDataBaseName(caseName);
2974  try {
2987  CaseDatabaseFactory factory = new CaseDatabaseFactory(databaseName, info);
2988  factory.createCaseDatabase();
2989 
2990  final SleuthkitJNI.CaseDbHandle caseHandle = SleuthkitJNI.openCaseDb(databaseName, info);
2991  return new SleuthkitCase(info.getHost(), Integer.parseInt(info.getPort()),
2992  databaseName, info.getUserName(), info.getPassword(), caseHandle, caseDirPath, info.getDbType());
2993  } catch (PropertyVetoException exp) {
2994  // In this case, the JDBC driver doesn't support PostgreSQL. Use the generic message here.
2995  throw new TskCoreException(exp.getMessage(), exp);
2996  } catch (Exception exp) {
2997  tryConnect(info); // attempt to connect, throw with user-friendly message if unable
2998  throw new TskCoreException(exp.getMessage(), exp); // throw with generic message if tryConnect() was successful
2999  }
3000  }
3001 
3011  private static String createCaseDataBaseName(String candidateDbName) {
3012  String dbName;
3013  if (!candidateDbName.isEmpty()) {
3014  /*
3015  * Replace all non-ASCII characters.
3016  */
3017  dbName = candidateDbName.replaceAll("[^\\p{ASCII}]", "_"); //NON-NLS
3018 
3019  /*
3020  * Replace all control characters.
3021  */
3022  dbName = dbName.replaceAll("[\\p{Cntrl}]", "_"); //NON-NLS
3023 
3024  /*
3025  * Replace /, \, :, ?, space, ' ".
3026  */
3027  dbName = dbName.replaceAll("[ /?:'\"\\\\]", "_"); //NON-NLS
3028 
3029  /*
3030  * Make it all lowercase.
3031  */
3032  dbName = dbName.toLowerCase();
3033 
3034  /*
3035  * Must start with letter or underscore. If not, prepend an
3036  * underscore.
3037  */
3038  if ((dbName.length() > 0 && !(Character.isLetter(dbName.codePointAt(0))) && !(dbName.codePointAt(0) == '_'))) {
3039  dbName = "_" + dbName;
3040  }
3041 
3042  /*
3043  * Truncate to 63 - 16 = 47 chars to accomodate a timestamp for
3044  * uniqueness.
3045  */
3046  if (dbName.length() > MAX_DB_NAME_LEN_BEFORE_TIMESTAMP) {
3047  dbName = dbName.substring(0, MAX_DB_NAME_LEN_BEFORE_TIMESTAMP);
3048  }
3049 
3050  } else {
3051  /*
3052  * Must start with letter or underscore.
3053  */
3054  dbName = "_";
3055  }
3056  /*
3057  * Add the time stmap.
3058  */
3059  SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss");
3060  Date date = new Date();
3061  dbName = dbName + "_" + dateFormat.format(date);
3062 
3063  return dbName;
3064  }
3065 
3073  public Examiner getCurrentExaminer() throws TskCoreException {
3074 
3075  // return cached value if there's one
3076  if (cachedCurrentExaminer != null) {
3077  return cachedCurrentExaminer;
3078  }
3079  String loginName = System.getProperty("user.name");
3080  if (loginName == null || loginName.isEmpty()) {
3081  throw new TskCoreException("Failed to determine logged in user name.");
3082  }
3083 
3084  ResultSet resultSet = null;
3085  CaseDbConnection connection = null;
3087  try {
3088  connection = connections.getConnection();
3089  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_EXAMINER_BY_LOGIN_NAME);
3090  statement.clearParameters();
3091  statement.setString(1, loginName);
3092  resultSet = connection.executeQuery(statement);
3093  if (resultSet.next()) {
3094  cachedCurrentExaminer = new Examiner(resultSet.getLong("examiner_id"), resultSet.getString("login_name"), resultSet.getString("display_name"));
3095  return cachedCurrentExaminer;
3096  } else {
3097  throw new TskCoreException("Error getting examaminer for name = " + loginName);
3098  }
3099 
3100  } catch (SQLException ex) {
3101  throw new TskCoreException("Error getting examaminer for name = " + loginName, ex);
3102  } finally {
3103  closeResultSet(resultSet);
3104  closeConnection(connection);
3106  }
3107 
3108  }
3109 
3119  Examiner getExaminerById(long id) throws TskCoreException {
3120 
3121  CaseDbConnection connection = null;
3122  ResultSet resultSet = null;
3124  try {
3125  connection = connections.getConnection();
3126  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_EXAMINER_BY_ID);
3127  statement.clearParameters();
3128  statement.setLong(1, id);
3129  resultSet = connection.executeQuery(statement);
3130  if (resultSet.next()) {
3131  return new Examiner(resultSet.getLong("examiner_id"), resultSet.getString("login_name"), resultSet.getString("full_name"));
3132  } else {
3133  throw new TskCoreException("Error getting examaminer for id = " + id);
3134  }
3135  } catch (SQLException ex) {
3136  throw new TskCoreException("Error getting examaminer for id = " + id, ex);
3137  } finally {
3138  closeResultSet(resultSet);
3139  closeConnection(connection);
3141  }
3142  }
3143 
3161  public AddImageProcess makeAddImageProcess(String timeZone, boolean addUnallocSpace, boolean noFatFsOrphans, String imageCopyPath) {
3162  return this.caseHandle.initAddImageProcess(timeZone, addUnallocSpace, noFatFsOrphans, imageCopyPath, this);
3163  }
3164 
3173  public List<Content> getRootObjects() throws TskCoreException {
3174  CaseDbConnection connection = null;
3175  Statement s = null;
3176  ResultSet rs = null;
3178  try {
3179  connection = connections.getConnection();
3180  s = connection.createStatement();
3181  rs = connection.executeQuery(s, "SELECT obj_id, type FROM tsk_objects " //NON-NLS
3182  + "WHERE par_obj_id IS NULL"); //NON-NLS
3183  Collection<ObjectInfo> infos = new ArrayList<ObjectInfo>();
3184  while (rs.next()) {
3185  infos.add(new ObjectInfo(rs.getLong("obj_id"), ObjectType.valueOf(rs.getShort("type")))); //NON-NLS
3186  }
3187 
3188  List<Content> rootObjs = new ArrayList<Content>();
3189  for (ObjectInfo i : infos) {
3190  if (null != i.type) {
3191  switch (i.type) {
3192  case IMG:
3193  rootObjs.add(getImageById(i.id));
3194  break;
3195  case ABSTRACTFILE:
3196  // Check if virtual dir for local files.
3197  AbstractFile af = getAbstractFileById(i.id);
3198  if (af instanceof VirtualDirectory) {
3199  rootObjs.add(af);
3200  } else {
3201  throw new TskCoreException("Parentless object has wrong type to be a root (ABSTRACTFILE, but not VIRTUAL_DIRECTORY: " + i.type);
3202  }
3203  break;
3204  case REPORT:
3205  break;
3206  case OS_ACCOUNT:
3207  break;
3208  case HOST_ADDRESS:
3209  break;
3210  case UNSUPPORTED:
3211  break;
3212  default:
3213  throw new TskCoreException("Parentless object has wrong type to be a root: " + i.type);
3214  }
3215  }
3216  }
3217  return rootObjs;
3218  } catch (SQLException ex) {
3219  throw new TskCoreException("Error getting root objects", ex);
3220  } finally {
3221  closeResultSet(rs);
3222  closeStatement(s);
3223  closeConnection(connection);
3225  }
3226  }
3227 
3239  List<Long> getDataSourceObjIds(String deviceId) throws TskCoreException {
3240 
3241  // check cached map first
3242  synchronized (deviceIdToDatasourceObjIdMap) {
3243  if (deviceIdToDatasourceObjIdMap.containsKey(deviceId)) {
3244  return new ArrayList<Long>(deviceIdToDatasourceObjIdMap.get(deviceId));
3245  }
3246 
3247  CaseDbConnection connection = null;
3248  Statement s = null;
3249  ResultSet rs = null;
3251  try {
3252  connection = connections.getConnection();
3253  s = connection.createStatement();
3254  rs = connection.executeQuery(s, "SELECT obj_id FROM data_source_info WHERE device_id = '" + deviceId + "'"); //NON-NLS
3255  List<Long> dataSourceObjIds = new ArrayList<Long>();
3256  while (rs.next()) {
3257  dataSourceObjIds.add(rs.getLong("obj_id"));
3258 
3259  // Add to map of deviceID to data_source_obj_id.
3260  long ds_obj_id = rs.getLong("obj_id");
3261  if (deviceIdToDatasourceObjIdMap.containsKey(deviceId)) {
3262  deviceIdToDatasourceObjIdMap.get(deviceId).add(ds_obj_id);
3263  } else {
3264  deviceIdToDatasourceObjIdMap.put(deviceId, new HashSet<Long>(Arrays.asList(ds_obj_id)));
3265  }
3266  }
3267  return dataSourceObjIds;
3268  } catch (SQLException ex) {
3269  throw new TskCoreException("Error getting data sources", ex);
3270  } finally {
3271  closeResultSet(rs);
3272  closeStatement(s);
3273  closeConnection(connection);
3275  }
3276  }
3277  }
3278 
3295  public List<DataSource> getDataSources() throws TskCoreException {
3296  CaseDbConnection connection = null;
3297  Statement statement = null;
3298  ResultSet resultSet = null;
3299  Statement statement2 = null;
3300  ResultSet resultSet2 = null;
3302  try {
3303  connection = connections.getConnection();
3304  statement = connection.createStatement();
3305  statement2 = connection.createStatement();
3306  resultSet = connection.executeQuery(statement,
3307  "SELECT ds.obj_id, ds.device_id, ds.time_zone, img.type, img.ssize, img.size, img.md5, img.sha1, img.sha256, img.display_name "
3308  + "FROM data_source_info AS ds "
3309  + "LEFT JOIN tsk_image_info AS img "
3310  + "ON ds.obj_id = img.obj_id"); //NON-NLS
3311 
3312  List<DataSource> dataSourceList = new ArrayList<DataSource>();
3313  Map<Long, List<String>> imagePathsMap = getImagePaths();
3314 
3315  while (resultSet.next()) {
3316  DataSource dataSource;
3317  Long objectId = resultSet.getLong("obj_id");
3318  String deviceId = resultSet.getString("device_id");
3319  String timezone = resultSet.getString("time_zone");
3320  String type = resultSet.getString("type");
3321 
3322  if (type == null) {
3323  /*
3324  * No data found in 'tsk_image_info', so we build a
3325  * LocalFilesDataSource.
3326  */
3327 
3328  resultSet2 = connection.executeQuery(statement2, "SELECT name FROM tsk_files WHERE tsk_files.obj_id = " + objectId); //NON-NLS
3329  String dsName = (resultSet2.next()) ? resultSet2.getString("name") : "";
3330  resultSet2.close();
3331 
3335  final short metaFlags = (short) (TSK_FS_META_FLAG_ENUM.ALLOC.getValue()
3336  | TSK_FS_META_FLAG_ENUM.USED.getValue());
3337  String parentPath = "/"; //NON-NLS
3338  dataSource = new LocalFilesDataSource(this, objectId, objectId, deviceId, dsName, dirType, metaType, dirFlag, metaFlags, timezone, null, null, FileKnown.UNKNOWN, parentPath);
3339  } else {
3340  /*
3341  * Data found in 'tsk_image_info', so we build an Image.
3342  */
3343  Long ssize = resultSet.getLong("ssize");
3344  Long size = resultSet.getLong("size");
3345  String md5 = resultSet.getString("md5");
3346  String sha1 = resultSet.getString("sha1");
3347  String sha256 = resultSet.getString("sha256");
3348  String name = resultSet.getString("display_name");
3349 
3350  List<String> imagePaths = imagePathsMap.get(objectId);
3351  if (name == null) {
3352  if (imagePaths.size() > 0) {
3353  String path = imagePaths.get(0);
3354  name = (new java.io.File(path)).getName();
3355  } else {
3356  name = "";
3357  }
3358  }
3359 
3360  dataSource = new Image(this, objectId, Long.valueOf(type), deviceId, ssize, name,
3361  imagePaths.toArray(new String[imagePaths.size()]), timezone, md5, sha1, sha256, size);
3362  }
3363 
3364  dataSourceList.add(dataSource);
3365  }
3366 
3367  return dataSourceList;
3368 
3369  } catch (SQLException ex) {
3370  throw new TskCoreException("Error getting data sources", ex);
3371  } finally {
3372  closeResultSet(resultSet);
3373  closeStatement(statement);
3374  closeResultSet(resultSet2);
3375  closeStatement(statement2);
3376  closeConnection(connection);
3378  }
3379  }
3380 
3400  public DataSource getDataSource(long objectId) throws TskDataException, TskCoreException {
3401  DataSource dataSource = null;
3402  CaseDbConnection connection = null;
3403  Statement statement = null;
3404  ResultSet resultSet = null;
3405  Statement statement2 = null;
3406  ResultSet resultSet2 = null;
3408  try {
3409  connection = connections.getConnection();
3410  statement = connection.createStatement();
3411  statement2 = connection.createStatement();
3412  resultSet = connection.executeQuery(statement,
3413  "SELECT ds.device_id, ds.time_zone, img.type, img.ssize, img.size, img.md5, img.sha1, img.sha256, img.display_name "
3414  + "FROM data_source_info AS ds "
3415  + "LEFT JOIN tsk_image_info AS img "
3416  + "ON ds.obj_id = img.obj_id "
3417  + "WHERE ds.obj_id = " + objectId); //NON-NLS
3418  if (resultSet.next()) {
3419  String deviceId = resultSet.getString("device_id");
3420  String timezone = resultSet.getString("time_zone");
3421  String type = resultSet.getString("type");
3422 
3423  if (type == null) {
3424  /*
3425  * No data found in 'tsk_image_info', so we build an
3426  * LocalFilesDataSource.
3427  */
3428 
3429  resultSet2 = connection.executeQuery(statement2, "SELECT name FROM tsk_files WHERE tsk_files.obj_id = " + objectId); //NON-NLS
3430  String dsName = (resultSet2.next()) ? resultSet2.getString("name") : "";
3431 
3435  final short metaFlags = (short) (TSK_FS_META_FLAG_ENUM.ALLOC.getValue()
3436  | TSK_FS_META_FLAG_ENUM.USED.getValue());
3437  String parentPath = "/"; //NON-NLS
3438  dataSource = new LocalFilesDataSource(this, objectId, objectId, deviceId, dsName, dirType, metaType, dirFlag, metaFlags, timezone, null, null, FileKnown.UNKNOWN, parentPath);
3439  } else {
3440  /*
3441  * Data found in 'tsk_image_info', so we build an Image.
3442  */
3443  Long ssize = resultSet.getLong("ssize");
3444  Long size = resultSet.getLong("size");
3445  String md5 = resultSet.getString("md5");
3446  String sha1 = resultSet.getString("sha1");
3447  String sha256 = resultSet.getString("sha256");
3448  String name = resultSet.getString("display_name");
3449 
3450  List<String> imagePaths = getImagePathsById(objectId, connection);
3451  if (name == null) {
3452  if (imagePaths.size() > 0) {
3453  String path = imagePaths.get(0);
3454  name = (new java.io.File(path)).getName();
3455  } else {
3456  name = "";
3457  }
3458  }
3459 
3460  dataSource = new Image(this, objectId, Long.valueOf(type), deviceId, ssize, name,
3461  imagePaths.toArray(new String[imagePaths.size()]), timezone, md5, sha1, sha256, size);
3462  }
3463  } else {
3464  throw new TskDataException(String.format("There is no data source with obj_id = %d", objectId));
3465  }
3466  } catch (SQLException ex) {
3467  throw new TskCoreException(String.format("Error getting data source with obj_id = %d", objectId), ex);
3468  } finally {
3469  closeResultSet(resultSet);
3470  closeStatement(statement);
3471  closeResultSet(resultSet2);
3472  closeStatement(statement2);
3473  closeConnection(connection);
3475  }
3476 
3477  return dataSource;
3478  }
3479 
3492  @Deprecated
3493  public ArrayList<BlackboardArtifact> getBlackboardArtifacts(int artifactTypeID) throws TskCoreException {
3494  ArrayList<BlackboardArtifact> artifacts = new ArrayList<>();
3495  artifacts.addAll(blackboard.getArtifactsByType(getArtifactType(artifactTypeID)));
3496  return artifacts;
3497  }
3498 
3509  public long getBlackboardArtifactsCount(long objId) throws TskCoreException {
3510  CaseDbConnection connection = null;
3511  ResultSet rs = null;
3513  try {
3514  connection = connections.getConnection();
3515 
3516  // SELECT COUNT(*) AS count FROM blackboard_artifacts WHERE obj_id = ?
3517  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.COUNT_ARTIFACTS_FROM_SOURCE);
3518  statement.clearParameters();
3519  statement.setLong(1, objId);
3520  rs = connection.executeQuery(statement);
3521  long count = 0;
3522  if (rs.next()) {
3523  count = rs.getLong("count");
3524  }
3525  return count;
3526  } catch (SQLException ex) {
3527  throw new TskCoreException("Error getting number of blackboard artifacts by content", ex);
3528  } finally {
3529  closeResultSet(rs);
3530  closeConnection(connection);
3532  }
3533  }
3534 
3545  public long getBlackboardArtifactsTypeCount(int artifactTypeID) throws TskCoreException {
3546  CaseDbConnection connection = null;
3547  ResultSet rs = null;
3549  try {
3550  connection = connections.getConnection();
3551 
3552  // SELECT COUNT(*) AS count FROM blackboard_artifacts WHERE artifact_type_id = ?
3553  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.COUNT_ARTIFACTS_OF_TYPE);
3554  statement.clearParameters();
3555  statement.setInt(1, artifactTypeID);
3556  rs = connection.executeQuery(statement);
3557  long count = 0;
3558  if (rs.next()) {
3559  count = rs.getLong("count");
3560  }
3561  return count;
3562  } catch (SQLException ex) {
3563  throw new TskCoreException("Error getting number of blackboard artifacts by type", ex);
3564  } finally {
3565  closeResultSet(rs);
3566  closeConnection(connection);
3568  }
3569  }
3570 
3582  public long getBlackboardArtifactsTypeCount(int artifactTypeID, long dataSourceID) throws TskCoreException {
3583  CaseDbConnection connection = null;
3584  ResultSet rs = null;
3586  try {
3587  connection = connections.getConnection();
3588 
3589  // SELECT COUNT(*) AS count FROM blackboard_artifacts WHERE artifact_type_id = ?
3590  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.COUNT_ARTIFACTS_OF_TYPE_BY_DATA_SOURCE);
3591  statement.clearParameters();
3592  statement.setInt(2, artifactTypeID);
3593  statement.setLong(1, dataSourceID);
3594  rs = connection.executeQuery(statement);
3595  long count = 0;
3596  if (rs.next()) {
3597  count = rs.getLong("count");
3598  }
3599  return count;
3600  } catch (SQLException ex) {
3601  throw new TskCoreException(String.format("Error getting number of blackboard artifacts by type (%d) and data source (%d)", artifactTypeID, dataSourceID), ex);
3602  } finally {
3603  closeResultSet(rs);
3604  closeConnection(connection);
3606  }
3607  }
3608 
3625  @Deprecated
3626  public List<BlackboardArtifact> getBlackboardArtifacts(BlackboardAttribute.ATTRIBUTE_TYPE attrType, String value) throws TskCoreException {
3628  try (CaseDbConnection connection = connections.getConnection(); Statement statement = connection.createStatement();
3629  ResultSet resultSet = connection.executeQuery(statement, "SELECT DISTINCT arts.artifact_id AS artifact_id, " //NON-NLS
3630  + "arts.obj_id AS obj_id, arts.artifact_obj_id AS artifact_obj_id, arts.data_source_obj_id AS data_source_obj_id, arts.artifact_type_id AS artifact_type_id, "
3631  + "types.type_name AS type_name, types.display_name AS display_name, "//NON-NLS
3632  + " arts.review_status_id AS review_status_id " //NON-NLS
3633  + "FROM blackboard_artifacts AS arts, blackboard_attributes AS attrs, blackboard_artifact_types AS types " //NON-NLS
3634  + "WHERE arts.artifact_id = attrs.artifact_id " //NON-NLS
3635  + " AND attrs.attribute_type_id = " + attrType.getTypeID() //NON-NLS
3636  + " AND attrs.value_text = '" + value + "'"
3637  + " AND types.artifact_type_id=arts.artifact_type_id"
3638  + " AND arts.review_status_id !=" + BlackboardArtifact.ReviewStatus.REJECTED.getID());) { //NON-NLS
3639 
3640  List<Long> analysisArtifactObjIds = new ArrayList<>();
3641  List<Long> dataArtifactObjIds = new ArrayList<>();
3642  while (resultSet.next()) {
3643  BlackboardArtifact.Type type = this.getArtifactType(resultSet.getInt("artifact_type_id"));
3644  if (type.getCategory() == BlackboardArtifact.Category.ANALYSIS_RESULT) {
3645  analysisArtifactObjIds.add(resultSet.getLong("artifact_obj_id"));
3646  } else {
3647  dataArtifactObjIds.add(resultSet.getLong("artifact_obj_id"));
3648  }
3649  }
3650 
3651  ArrayList<BlackboardArtifact> artifacts = new ArrayList<>();
3652  if (!analysisArtifactObjIds.isEmpty()) {
3653  artifacts.addAll(getArtifactsForValues(BlackboardArtifact.Category.ANALYSIS_RESULT, "artifacts.artifact_obj_id", analysisArtifactObjIds, connection));
3654  }
3655 
3656  if (!dataArtifactObjIds.isEmpty()) {
3657  artifacts.addAll(getArtifactsForValues(BlackboardArtifact.Category.DATA_ARTIFACT, "artifacts.artifact_obj_id", dataArtifactObjIds, connection));
3658  }
3659  return artifacts;
3660  } catch (SQLException ex) {
3661  throw new TskCoreException("Error getting blackboard artifacts by attribute", ex);
3662  } finally {
3664  }
3665  }
3666 
3685  @Deprecated
3686  public List<BlackboardArtifact> getBlackboardArtifacts(BlackboardAttribute.ATTRIBUTE_TYPE attrType, String subString, boolean startsWith) throws TskCoreException {
3687  String valSubStr = "%" + subString; //NON-NLS
3688  if (startsWith == false) {
3689  valSubStr += "%"; //NON-NLS
3690  }
3691 
3693  try (CaseDbConnection connection = connections.getConnection(); Statement statement = connection.createStatement();
3694  ResultSet resultSet = connection.executeQuery(statement, "SELECT DISTINCT arts.artifact_id AS artifact_id, " //NON-NLS
3695  + " arts.obj_id AS obj_id, arts.artifact_obj_id AS artifact_obj_id, arts.data_source_obj_id AS data_source_obj_id, arts.artifact_type_id AS artifact_type_id, " //NON-NLS
3696  + " types.type_name AS type_name, types.display_name AS display_name, " //NON-NLS
3697  + " arts.review_status_id AS review_status_id " //NON-NLS
3698  + " FROM blackboard_artifacts AS arts, blackboard_attributes AS attrs, blackboard_artifact_types AS types " //NON-NLS
3699  + " WHERE arts.artifact_id = attrs.artifact_id " //NON-NLS
3700  + " AND attrs.attribute_type_id = " + attrType.getTypeID() //NON-NLS
3701  + " AND LOWER(attrs.value_text) LIKE LOWER('" + valSubStr + "')"
3702  + " AND types.artifact_type_id=arts.artifact_type_id "
3703  + " AND arts.review_status_id !=" + BlackboardArtifact.ReviewStatus.REJECTED.getID());) {
3704  List<Long> analysisArtifactObjIds = new ArrayList<>();
3705  List<Long> dataArtifactObjIds = new ArrayList<>();
3706  while (resultSet.next()) {
3707  BlackboardArtifact.Type type = this.getArtifactType(resultSet.getInt("artifact_type_id"));
3708  if (type.getCategory() == BlackboardArtifact.Category.ANALYSIS_RESULT) {
3709  analysisArtifactObjIds.add(resultSet.getLong("artifact_obj_id"));
3710  } else {
3711  dataArtifactObjIds.add(resultSet.getLong("artifact_obj_id"));
3712  }
3713  }
3714 
3715  ArrayList<BlackboardArtifact> artifacts = new ArrayList<>();
3716  if (!analysisArtifactObjIds.isEmpty()) {
3717  artifacts.addAll(getArtifactsForValues(BlackboardArtifact.Category.ANALYSIS_RESULT, "artifacts.artifact_obj_id", analysisArtifactObjIds, connection));
3718  }
3719 
3720  if (!dataArtifactObjIds.isEmpty()) {
3721  artifacts.addAll(getArtifactsForValues(BlackboardArtifact.Category.DATA_ARTIFACT, "artifacts.artifact_obj_id", dataArtifactObjIds, connection));
3722  }
3723  return artifacts;
3724  } catch (SQLException ex) {
3725  throw new TskCoreException("Error getting blackboard artifacts by attribute. " + ex.getMessage(), ex);
3726  } finally {
3728  }
3729  }
3730 
3746  @Deprecated
3747  public List<BlackboardArtifact> getBlackboardArtifacts(BlackboardAttribute.ATTRIBUTE_TYPE attrType, int value) throws TskCoreException {
3749  try (CaseDbConnection connection = connections.getConnection(); Statement statement = connection.createStatement();
3750  ResultSet resultSet = connection.executeQuery(statement, "SELECT DISTINCT arts.artifact_id AS artifact_id, " //NON-NLS
3751  + " arts.obj_id AS obj_id, arts.artifact_obj_id AS artifact_obj_id, arts.data_source_obj_id AS data_source_obj_id, arts.artifact_type_id AS artifact_type_id, "
3752  + " types.type_name AS type_name, types.display_name AS display_name, "
3753  + " arts.review_status_id AS review_status_id "//NON-NLS
3754  + " FROM blackboard_artifacts AS arts, blackboard_attributes AS attrs, blackboard_artifact_types AS types " //NON-NLS
3755  + "WHERE arts.artifact_id = attrs.artifact_id " //NON-NLS
3756  + " AND attrs.attribute_type_id = " + attrType.getTypeID() //NON-NLS
3757  + " AND attrs.value_int32 = " + value //NON-NLS
3758  + " AND types.artifact_type_id=arts.artifact_type_id "
3759  + " AND arts.review_status_id !=" + BlackboardArtifact.ReviewStatus.REJECTED.getID());) {
3760  List<Long> analysisArtifactObjIds = new ArrayList<>();
3761  List<Long> dataArtifactObjIds = new ArrayList<>();
3762  while (resultSet.next()) {
3763  BlackboardArtifact.Type type = this.getArtifactType(resultSet.getInt("artifact_type_id"));
3764  if (type.getCategory() == BlackboardArtifact.Category.ANALYSIS_RESULT) {
3765  analysisArtifactObjIds.add(resultSet.getLong("artifact_obj_id"));
3766  } else {
3767  dataArtifactObjIds.add(resultSet.getLong("artifact_obj_id"));
3768  }
3769  }
3770 
3771  ArrayList<BlackboardArtifact> artifacts = new ArrayList<>();
3772  if (!analysisArtifactObjIds.isEmpty()) {
3773  artifacts.addAll(getArtifactsForValues(BlackboardArtifact.Category.ANALYSIS_RESULT, "artifacts.artifact_obj_id", analysisArtifactObjIds, connection));
3774  }
3775 
3776  if (!dataArtifactObjIds.isEmpty()) {
3777  artifacts.addAll(getArtifactsForValues(BlackboardArtifact.Category.DATA_ARTIFACT, "artifacts.artifact_obj_id", dataArtifactObjIds, connection));
3778  }
3779  return artifacts;
3780  } catch (SQLException ex) {
3781  throw new TskCoreException("Error getting blackboard artifacts by attribute", ex);
3782  } finally {
3784  }
3785  }
3786 
3803  @Deprecated
3804  public List<BlackboardArtifact> getBlackboardArtifacts(BlackboardAttribute.ATTRIBUTE_TYPE attrType, long value) throws TskCoreException {
3806  try (CaseDbConnection connection = connections.getConnection(); Statement statement = connection.createStatement();
3807  ResultSet resultSet = connection.executeQuery(statement, "SELECT DISTINCT arts.artifact_id AS artifact_id, " //NON-NLS
3808  + " arts.obj_id AS obj_id, arts.artifact_obj_id AS artifact_obj_id, arts.data_source_obj_id AS data_source_obj_id, arts.artifact_type_id AS artifact_type_id, "
3809  + " types.type_name AS type_name, types.display_name AS display_name, "
3810  + " arts.review_status_id AS review_status_id "//NON-NLS
3811  + " FROM blackboard_artifacts AS arts, blackboard_attributes AS attrs, blackboard_artifact_types AS types " //NON-NLS
3812  + " WHERE arts.artifact_id = attrs.artifact_id " //NON-NLS
3813  + " AND attrs.attribute_type_id = " + attrType.getTypeID() //NON-NLS
3814  + " AND attrs.value_int64 = " + value //NON-NLS
3815  + " AND types.artifact_type_id=arts.artifact_type_id "
3816  + " AND arts.review_status_id !=" + BlackboardArtifact.ReviewStatus.REJECTED.getID());) {
3817  List<Long> analysisArtifactObjIds = new ArrayList<>();
3818  List<Long> dataArtifactObjIds = new ArrayList<>();
3819  while (resultSet.next()) {
3820  BlackboardArtifact.Type type = this.getArtifactType(resultSet.getInt("artifact_type_id"));
3821  if (type.getCategory() == BlackboardArtifact.Category.ANALYSIS_RESULT) {
3822  analysisArtifactObjIds.add(resultSet.getLong("artifact_obj_id"));
3823  } else {
3824  dataArtifactObjIds.add(resultSet.getLong("artifact_obj_id"));
3825  }
3826  }
3827 
3828  ArrayList<BlackboardArtifact> artifacts = new ArrayList<>();
3829  if (!analysisArtifactObjIds.isEmpty()) {
3830  artifacts.addAll(getArtifactsForValues(BlackboardArtifact.Category.ANALYSIS_RESULT, "artifacts.artifact_obj_id", analysisArtifactObjIds, connection));
3831  }
3832 
3833  if (!dataArtifactObjIds.isEmpty()) {
3834  artifacts.addAll(getArtifactsForValues(BlackboardArtifact.Category.DATA_ARTIFACT, "artifacts.artifact_obj_id", dataArtifactObjIds, connection));
3835  }
3836  return artifacts;
3837  } catch (SQLException ex) {
3838  throw new TskCoreException("Error getting blackboard artifacts by attribute. " + ex.getMessage(), ex);
3839  } finally {
3841  }
3842  }
3843 
3860  @Deprecated
3861  public List<BlackboardArtifact> getBlackboardArtifacts(BlackboardAttribute.ATTRIBUTE_TYPE attrType, double value) throws TskCoreException {
3863  try (CaseDbConnection connection = connections.getConnection(); Statement statement = connection.createStatement();
3864  ResultSet resultSet = connection.executeQuery(statement, "SELECT DISTINCT arts.artifact_id AS artifact_id, " //NON-NLS
3865  + " arts.obj_id AS obj_id, arts.artifact_obj_id AS artifact_obj_id, arts.data_source_obj_id AS data_source_obj_id, arts.artifact_type_id AS artifact_type_id, "
3866  + " types.type_name AS type_name, types.display_name AS display_name, "
3867  + " arts.review_status_id AS review_status_id "//NON-NLS
3868  + " FROM blackboard_artifacts AS arts, blackboard_attributes AS attrs, blackboard_artifact_types AS types " //NON-NLS
3869  + " WHERE arts.artifact_id = attrs.artifact_id " //NON-NLS
3870  + " AND attrs.attribute_type_id = " + attrType.getTypeID() //NON-NLS
3871  + " AND attrs.value_double = " + value //NON-NLS
3872  + " AND types.artifact_type_id=arts.artifact_type_id "
3873  + " AND arts.review_status_id !=" + BlackboardArtifact.ReviewStatus.REJECTED.getID());) {
3874  List<Long> analysisArtifactObjIds = new ArrayList<>();
3875  List<Long> dataArtifactObjIds = new ArrayList<>();
3876  while (resultSet.next()) {
3877  BlackboardArtifact.Type type = this.getArtifactType(resultSet.getInt("artifact_type_id"));
3878  if (type.getCategory() == BlackboardArtifact.Category.ANALYSIS_RESULT) {
3879  analysisArtifactObjIds.add(resultSet.getLong("artifact_obj_id"));
3880  } else {
3881  dataArtifactObjIds.add(resultSet.getLong("artifact_obj_id"));
3882  }
3883  }
3884 
3885  ArrayList<BlackboardArtifact> artifacts = new ArrayList<>();
3886  if (!analysisArtifactObjIds.isEmpty()) {
3887  artifacts.addAll(getArtifactsForValues(BlackboardArtifact.Category.ANALYSIS_RESULT, "artifacts.artifact_obj_id", analysisArtifactObjIds, connection));
3888  }
3889 
3890  if (!dataArtifactObjIds.isEmpty()) {
3891  artifacts.addAll(getArtifactsForValues(BlackboardArtifact.Category.DATA_ARTIFACT, "artifacts.artifact_obj_id", dataArtifactObjIds, connection));
3892  }
3893  return artifacts;
3894  } catch (SQLException ex) {
3895  throw new TskCoreException("Error getting blackboard artifacts by attribute", ex);
3896  } finally {
3898  }
3899  }
3900 
3917  @Deprecated
3918  public List<BlackboardArtifact> getBlackboardArtifacts(BlackboardAttribute.ATTRIBUTE_TYPE attrType, byte value) throws TskCoreException {
3919 
3921  try (CaseDbConnection connection = connections.getConnection(); Statement statement = connection.createStatement();
3922  ResultSet resultSet = connection.executeQuery(statement, "SELECT DISTINCT arts.artifact_id AS artifact_id, " //NON-NLS
3923  + " arts.obj_id AS obj_id, arts.artifact_obj_id AS artifact_obj_id, arts.data_source_obj_id AS data_source_obj_id, arts.artifact_type_id AS artifact_type_id, "
3924  + " types.type_name AS type_name, types.display_name AS display_name, "
3925  + " arts.review_status_id AS review_status_id "//NON-NLS
3926  + " FROM blackboard_artifacts AS arts, blackboard_attributes AS attrs, blackboard_artifact_types AS types " //NON-NLS
3927  + " WHERE arts.artifact_id = attrs.artifact_id " //NON-NLS
3928  + " AND attrs.attribute_type_id = " + attrType.getTypeID() //NON-NLS
3929  + " AND attrs.value_byte = " + value //NON-NLS
3930  + " AND types.artifact_type_id=arts.artifact_type_id "
3931  + " AND arts.review_status_id !=" + BlackboardArtifact.ReviewStatus.REJECTED.getID());) {
3932  List<Long> analysisArtifactObjIds = new ArrayList<>();
3933  List<Long> dataArtifactObjIds = new ArrayList<>();
3934  while (resultSet.next()) {
3935  BlackboardArtifact.Type type = this.getArtifactType(resultSet.getInt("artifact_type_id"));
3936  if (type.getCategory() == BlackboardArtifact.Category.ANALYSIS_RESULT) {
3937  analysisArtifactObjIds.add(resultSet.getLong("artifact_obj_id"));
3938  } else {
3939  dataArtifactObjIds.add(resultSet.getLong("artifact_obj_id"));
3940  }
3941  }
3942 
3943  ArrayList<BlackboardArtifact> artifacts = new ArrayList<>();
3944  if (!analysisArtifactObjIds.isEmpty()) {
3945  artifacts.addAll(getArtifactsForValues(BlackboardArtifact.Category.ANALYSIS_RESULT, "artifacts.artifact_obj_id", analysisArtifactObjIds, connection));
3946  }
3947 
3948  if (!dataArtifactObjIds.isEmpty()) {
3949  artifacts.addAll(getArtifactsForValues(BlackboardArtifact.Category.DATA_ARTIFACT, "artifacts.artifact_obj_id", dataArtifactObjIds, connection));
3950  }
3951  return artifacts;
3952  } catch (SQLException ex) {
3953  throw new TskCoreException("Error getting blackboard artifacts by attribute", ex);
3954  } finally {
3956  }
3957  }
3958 
3966  public Iterable<BlackboardArtifact.Type> getArtifactTypes() throws TskCoreException {
3967  CaseDbConnection connection = null;
3968  Statement s = null;
3969  ResultSet rs = null;
3971  try {
3972  connection = connections.getConnection();
3973  s = connection.createStatement();
3974  rs = connection.executeQuery(s, "SELECT artifact_type_id, type_name, display_name, category_type FROM blackboard_artifact_types"); //NON-NLS
3975  ArrayList<BlackboardArtifact.Type> artifactTypes = new ArrayList<BlackboardArtifact.Type>();
3976  while (rs.next()) {
3977  artifactTypes.add(new BlackboardArtifact.Type(rs.getInt("artifact_type_id"),
3978  rs.getString("type_name"), rs.getString("display_name"),
3979  BlackboardArtifact.Category.fromID(rs.getInt("category_type"))));
3980  }
3981  return artifactTypes;
3982  } catch (SQLException ex) {
3983  throw new TskCoreException("Error getting artifact types", ex); //NON-NLS
3984  } finally {
3985  closeResultSet(rs);
3986  closeStatement(s);
3987  closeConnection(connection);
3989  }
3990  }
3991 
4000  public ArrayList<BlackboardArtifact.ARTIFACT_TYPE> getBlackboardArtifactTypesInUse() throws TskCoreException {
4001  String typeIdList = "";
4002  for (int i = 0; i < BlackboardArtifact.ARTIFACT_TYPE.values().length; ++i) {
4003  typeIdList += BlackboardArtifact.ARTIFACT_TYPE.values()[i].getTypeID();
4004  if (i < BlackboardArtifact.ARTIFACT_TYPE.values().length - 1) {
4005  typeIdList += ", ";
4006  }
4007  }
4008  String query = "SELECT DISTINCT artifact_type_id FROM blackboard_artifacts "
4009  + "WHERE artifact_type_id IN (" + typeIdList + ")";
4010  CaseDbConnection connection = null;
4011  Statement s = null;
4012  ResultSet rs = null;
4014  try {
4015  connection = connections.getConnection();
4016  s = connection.createStatement();
4017  rs = connection.executeQuery(s, query);
4018  ArrayList<BlackboardArtifact.ARTIFACT_TYPE> usedArts = new ArrayList<BlackboardArtifact.ARTIFACT_TYPE>();
4019  while (rs.next()) {
4020  usedArts.add(ARTIFACT_TYPE.fromID(rs.getInt("artifact_type_id")));
4021  }
4022  return usedArts;
4023  } catch (SQLException ex) {
4024  throw new TskCoreException("Error getting artifact types in use", ex);
4025  } finally {
4026  closeResultSet(rs);
4027  closeStatement(s);
4028  closeConnection(connection);
4030  }
4031  }
4032 
4043  public List<BlackboardArtifact.Type> getArtifactTypesInUse() throws TskCoreException {
4044  CaseDbConnection connection = null;
4045  Statement s = null;
4046  ResultSet rs = null;
4048  try {
4049  connection = connections.getConnection();
4050  s = connection.createStatement();
4051  rs = connection.executeQuery(s,
4052  "SELECT DISTINCT arts.artifact_type_id AS artifact_type_id, "
4053  + "types.type_name AS type_name, "
4054  + "types.display_name AS display_name, "
4055  + "types.category_type AS category_type "
4056  + "FROM blackboard_artifact_types AS types "
4057  + "INNER JOIN blackboard_artifacts AS arts "
4058  + "ON arts.artifact_type_id = types.artifact_type_id"); //NON-NLS
4059  List<BlackboardArtifact.Type> uniqueArtifactTypes = new ArrayList<BlackboardArtifact.Type>();
4060  while (rs.next()) {
4061  uniqueArtifactTypes.add(new BlackboardArtifact.Type(rs.getInt("artifact_type_id"),
4062  rs.getString("type_name"), rs.getString("display_name"),
4063  BlackboardArtifact.Category.fromID(rs.getInt("category_type"))));
4064  }
4065  return uniqueArtifactTypes;
4066  } catch (SQLException ex) {
4067  throw new TskCoreException("Error getting attribute types", ex);
4068  } finally {
4069  closeResultSet(rs);
4070  closeStatement(s);
4071  closeConnection(connection);
4073  }
4074  }
4075 
4083  public List<BlackboardAttribute.Type> getAttributeTypes() throws TskCoreException {
4084  CaseDbConnection connection = null;
4085  Statement s = null;
4086  ResultSet rs = null;
4088  try {
4089  connection = connections.getConnection();
4090  s = connection.createStatement();
4091  rs = connection.executeQuery(s, "SELECT attribute_type_id, type_name, display_name, value_type FROM blackboard_attribute_types"); //NON-NLS
4092  ArrayList<BlackboardAttribute.Type> attribute_types = new ArrayList<BlackboardAttribute.Type>();
4093  while (rs.next()) {
4094  attribute_types.add(new BlackboardAttribute.Type(rs.getInt("attribute_type_id"), rs.getString("type_name"),
4095  rs.getString("display_name"), TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.fromType(rs.getLong("value_type"))));
4096  }
4097  return attribute_types;
4098  } catch (SQLException ex) {
4099  throw new TskCoreException("Error getting attribute types", ex);
4100  } finally {
4101  closeResultSet(rs);
4102  closeStatement(s);
4103  closeConnection(connection);
4105  }
4106  }
4107 
4119  public int getBlackboardAttributeTypesCount() throws TskCoreException {
4120  CaseDbConnection connection = null;
4121  Statement s = null;
4122  ResultSet rs = null;
4124  try {
4125  connection = connections.getConnection();
4126  s = connection.createStatement();
4127  rs = connection.executeQuery(s, "SELECT COUNT(*) AS count FROM blackboard_attribute_types"); //NON-NLS
4128  int count = 0;
4129  if (rs.next()) {
4130  count = rs.getInt("count");
4131  }
4132  return count;
4133  } catch (SQLException ex) {
4134  throw new TskCoreException("Error getting number of blackboard artifacts by type", ex);
4135  } finally {
4136  closeResultSet(rs);
4137  closeStatement(s);
4138  closeConnection(connection);
4140  }
4141  }
4142 
4155  private long getArtifactsCountHelper(int artifactTypeID, long obj_id) throws TskCoreException {
4156  CaseDbConnection connection = null;
4157  ResultSet rs = null;
4159  try {
4160  connection = connections.getConnection();
4161 
4162  // SELECT COUNT(*) AS count FROM blackboard_artifacts WHERE obj_id = ? AND artifact_type_id = ?
4163  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.COUNT_ARTIFACTS_BY_SOURCE_AND_TYPE);
4164  statement.clearParameters();
4165  statement.setLong(1, obj_id);
4166  statement.setInt(2, artifactTypeID);
4167  rs = connection.executeQuery(statement);
4168  long count = 0;
4169  if (rs.next()) {
4170  count = rs.getLong("count");
4171  }
4172  return count;
4173  } catch (SQLException ex) {
4174  throw new TskCoreException("Error getting blackboard artifact count", ex);
4175  } finally {
4176  closeResultSet(rs);
4177  closeConnection(connection);
4179  }
4180  }
4181 
4194  public ArrayList<BlackboardArtifact> getBlackboardArtifacts(String artifactTypeName, long obj_id) throws TskCoreException {
4195  ArrayList<BlackboardArtifact> artifacts = new ArrayList<>();
4196  artifacts.addAll(blackboard.getArtifactsBySourceId(getArtifactType(artifactTypeName), obj_id));
4197  return artifacts;
4198  }
4199 
4212  public ArrayList<BlackboardArtifact> getBlackboardArtifacts(int artifactTypeID, long obj_id) throws TskCoreException {
4213  ArrayList<BlackboardArtifact> artifacts = new ArrayList<>();
4214  artifacts.addAll(blackboard.getArtifactsBySourceId(getArtifactType(artifactTypeID), obj_id));
4215  return artifacts;
4216  }
4217 
4230  public ArrayList<BlackboardArtifact> getBlackboardArtifacts(ARTIFACT_TYPE artifactType, long obj_id) throws TskCoreException {
4231  return getBlackboardArtifacts(artifactType.getTypeID(), obj_id);
4232  }
4233 
4246  public long getBlackboardArtifactsCount(String artifactTypeName, long obj_id) throws TskCoreException {
4247  int artifactTypeID = this.getArtifactType(artifactTypeName).getTypeID();
4248  if (artifactTypeID == -1) {
4249  return 0;
4250  }
4251  return getArtifactsCountHelper(artifactTypeID, obj_id);
4252  }
4253 
4266  public long getBlackboardArtifactsCount(int artifactTypeID, long obj_id) throws TskCoreException {
4267  return getArtifactsCountHelper(artifactTypeID, obj_id);
4268  }
4269 
4282  public long getBlackboardArtifactsCount(ARTIFACT_TYPE artifactType, long obj_id) throws TskCoreException {
4283  return getArtifactsCountHelper(artifactType.getTypeID(), obj_id);
4284  }
4285 
4297  public ArrayList<BlackboardArtifact> getBlackboardArtifacts(String artifactTypeName) throws TskCoreException {
4298  ArrayList<BlackboardArtifact> artifacts = new ArrayList<>();
4299  artifacts.addAll(blackboard.getArtifactsByType(getArtifactType(artifactTypeName)));
4300  return artifacts;
4301  }
4302 
4314  public ArrayList<BlackboardArtifact> getBlackboardArtifacts(ARTIFACT_TYPE artifactType) throws TskCoreException {
4315  ArrayList<BlackboardArtifact> artifacts = new ArrayList<>();
4316  artifacts.addAll(blackboard.getArtifactsByType(getArtifactType(artifactType.getTypeID())));
4317  return artifacts;
4318  }
4319 
4335  @Deprecated
4336  public List<BlackboardArtifact> getBlackboardArtifacts(ARTIFACT_TYPE artifactType, BlackboardAttribute.ATTRIBUTE_TYPE attrType, String value) throws TskCoreException {
4337 
4338  String dataArtifactJoin = "tsk_data_artifacts ON tsk_data_artifacts.artifact_obj_id = arts.artifact_obj_id";
4339  String analysisResultJoin = "tsk_analysis_result ON tsk_analysis_result.artifact_obj_id = arts.artifact_obj_id";
4340  String dataArtifactColumns = ", tsk_data_artifacts.os_account_obj_id AS os_account_obj_id";
4341  String analysResultColumns = ", tsk_analysis_result.conclusion AS conclusion, tsk_analysis_result.significance AS significance, tsk_analysis_result.priority AS priority, tsk_analysis_result.conclusion AS conclusion, tsk_analysis_result.significance AS significance, tsk_analysis_result.priority AS priority,";
4342 
4343  String formatQuery = "SELECT DISTINCT arts.artifact_id AS artifact_id, " //NON-NLS
4344  + "arts.obj_id AS obj_id, arts.artifact_obj_id as artifact_obj_id, arts.data_source_obj_id AS data_source_obj_id, arts.artifact_type_id AS artifact_type_id, "
4345  + "types.type_name AS type_name, types.display_name AS display_name,"
4346  + "arts.review_status_id AS review_status_id %s "//NON-NLS
4347  + "FROM blackboard_artifacts AS arts "
4348  + "JOIN blackboard_attributes AS attrs "
4349  + "JOIN blackboard_artifact_types AS types " //NON-NLS
4350  + "LEFT JOIN %s "
4351  + "WHERE arts.artifact_id = attrs.artifact_id " //NON-NLS
4352  + "AND attrs.attribute_type_id = %d "
4353  + " AND arts.artifact_type_id = %d "
4354  + " AND attrs.value_text = '%s' " //NON-NLS
4355  + " AND types.artifact_type_id=arts.artifact_type_id "
4356  + " AND arts.review_status_id != %d";
4357 
4358  String query = String.format(formatQuery,
4359  (artifactType.getCategory() == BlackboardArtifact.Category.ANALYSIS_RESULT ? analysResultColumns : dataArtifactColumns),
4360  (artifactType.getCategory() == BlackboardArtifact.Category.ANALYSIS_RESULT ? analysisResultJoin : dataArtifactJoin),
4361  attrType.getTypeID(),
4362  artifactType.getTypeID(),
4363  value,
4365 
4367  try (CaseDbConnection connection = connections.getConnection(); Statement s = connection.createStatement(); ResultSet rs = connection.executeQuery(s, query)) {
4368  ArrayList<BlackboardArtifact> artifacts = new ArrayList<>();
4369  while (rs.next()) {
4370  if (artifactType.getCategory() == BlackboardArtifact.Category.DATA_ARTIFACT) {
4371  Long osAccountObjId = rs.getLong("os_account_obj_id");
4372  if (rs.wasNull()) {
4373  osAccountObjId = null;
4374  }
4375 
4376  artifacts.add(new DataArtifact(this, rs.getLong("artifact_id"), rs.getLong("obj_id"),
4377  rs.getLong("artifact_obj_id"),
4378  rs.getObject("data_source_obj_id") != null ? rs.getLong("data_source_obj_id") : null,
4379  rs.getInt("artifact_type_id"), rs.getString("type_name"), rs.getString("display_name"),
4380  BlackboardArtifact.ReviewStatus.withID(rs.getInt("review_status_id")), osAccountObjId, false));
4381  } else {
4382  artifacts.add(new AnalysisResult(this, rs.getLong("artifact_id"), rs.getLong("obj_id"),
4383  rs.getLong("artifact_obj_id"),
4384  rs.getObject("data_source_obj_id") != null ? rs.getLong("data_source_obj_id") : null,
4385  rs.getInt("artifact_type_id"), rs.getString("type_name"), rs.getString("display_name"),
4386  BlackboardArtifact.ReviewStatus.withID(rs.getInt("review_status_id")),
4387  new Score(Score.Significance.fromID(rs.getInt("significance")), Score.Priority.fromID(rs.getInt("priority"))),
4388  rs.getString("conclusion"), rs.getString("configuration"), rs.getString("justification")));
4389  }
4390  }
4391  return artifacts;
4392  } catch (SQLException ex) {
4393  throw new TskCoreException("Error getting blackboard artifacts by artifact type and attribute. " + ex.getMessage(), ex);
4394  } finally {
4396  }
4397  }
4398 
4410  public BlackboardArtifact getBlackboardArtifact(long artifactID) throws TskCoreException {
4411  List<DataArtifact> dataArtifacts = blackboard.getDataArtifactsWhere("artifacts.artifact_id = " + artifactID);
4412  if (!dataArtifacts.isEmpty()) {
4413  return dataArtifacts.get(0);
4414  }
4415 
4416  List<AnalysisResult> analysisResults = blackboard.getAnalysisResultsWhere("artifacts.artifact_id = " + artifactID);
4417  if (!analysisResults.isEmpty()) {
4418  return analysisResults.get(0);
4419  }
4420 
4421  throw new TskCoreException("No blackboard artifact with id " + artifactID);
4422  }
4423 
4432  public void addBlackboardAttribute(BlackboardAttribute attr, int artifactTypeId) throws TskCoreException {
4434  try (CaseDbConnection connection = connections.getConnection();) {
4435  addBlackBoardAttribute(attr, artifactTypeId, connection);
4436  } catch (SQLException ex) {
4437  throw new TskCoreException("Error adding blackboard attribute " + attr.toString(), ex);
4438  } finally {
4440  }
4441  }
4442 
4452  public void addBlackboardAttributes(Collection<BlackboardAttribute> attributes, int artifactTypeId) throws TskCoreException {
4453  CaseDbConnection connection = null;
4455  try {
4456  connection = connections.getConnection();
4457  connection.beginTransaction();
4458  for (final BlackboardAttribute attr : attributes) {
4459  addBlackBoardAttribute(attr, artifactTypeId, connection);
4460  }
4461  connection.commitTransaction();
4462  } catch (SQLException ex) {
4463  rollbackTransaction(connection);
4464  throw new TskCoreException("Error adding blackboard attributes", ex);
4465  } finally {
4466  closeConnection(connection);
4468  }
4469  }
4470 
4471  void addBlackBoardAttribute(BlackboardAttribute attr, int artifactTypeId, CaseDbConnection connection) throws SQLException, TskCoreException {
4472  PreparedStatement statement;
4473  switch (attr.getAttributeType().getValueType()) {
4474  case STRING:
4475  case JSON:
4476  statement = connection.getPreparedStatement(PREPARED_STATEMENT.INSERT_STRING_ATTRIBUTE);
4477  statement.clearParameters();
4478  statement.setString(7, attr.getValueString());
4479  break;
4480  case BYTE:
4481  statement = connection.getPreparedStatement(PREPARED_STATEMENT.INSERT_BYTE_ATTRIBUTE);
4482  statement.clearParameters();
4483  statement.setBytes(7, attr.getValueBytes());
4484  break;
4485  case INTEGER:
4486  statement = connection.getPreparedStatement(PREPARED_STATEMENT.INSERT_INT_ATTRIBUTE);
4487  statement.clearParameters();
4488  statement.setInt(7, attr.getValueInt());
4489  break;
4490  case LONG:
4491  statement = connection.getPreparedStatement(PREPARED_STATEMENT.INSERT_LONG_ATTRIBUTE);
4492  statement.clearParameters();
4493  statement.setLong(7, attr.getValueLong());
4494  break;
4495  case DOUBLE:
4496  statement = connection.getPreparedStatement(PREPARED_STATEMENT.INSERT_DOUBLE_ATTRIBUTE);
4497  statement.clearParameters();
4498  statement.setDouble(7, attr.getValueDouble());
4499  break;
4500  case DATETIME:
4501  statement = connection.getPreparedStatement(PREPARED_STATEMENT.INSERT_LONG_ATTRIBUTE);
4502  statement.clearParameters();
4503  statement.setLong(7, attr.getValueLong());
4504  break;
4505  default:
4506  throw new TskCoreException("Unrecognized artifact attribute value type");
4507  }
4508  statement.setLong(1, attr.getArtifactID());
4509  statement.setInt(2, artifactTypeId);
4510  statement.setString(3, attr.getSourcesCSV());
4511  statement.setString(4, "");
4512  statement.setInt(5, attr.getAttributeType().getTypeID());
4513  statement.setLong(6, attr.getAttributeType().getValueType().getType());
4514  connection.executeUpdate(statement);
4515  }
4516 
4517  void addFileAttribute(Attribute attr, CaseDbConnection connection) throws SQLException, TskCoreException {
4518  PreparedStatement statement;
4519  statement = connection.getPreparedStatement(PREPARED_STATEMENT.INSERT_FILE_ATTRIBUTE, Statement.RETURN_GENERATED_KEYS);
4520  statement.clearParameters();
4521 
4522  statement.setLong(1, attr.getAttributeParentId());
4523  statement.setInt(2, attr.getAttributeType().getTypeID());
4524  statement.setLong(3, attr.getAttributeType().getValueType().getType());
4525 
4526  if (attr.getAttributeType().getValueType() == TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.BYTE) {
4527  statement.setBytes(4, attr.getValueBytes());
4528  } else {
4529  statement.setBytes(4, null);
4530  }
4531 
4532  if (attr.getAttributeType().getValueType() == TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.STRING
4533  || attr.getAttributeType().getValueType() == TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.JSON) {
4534  statement.setString(5, attr.getValueString());
4535  } else {
4536  statement.setString(5, null);
4537  }
4538  if (attr.getAttributeType().getValueType() == TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.INTEGER) {
4539  statement.setInt(6, attr.getValueInt());
4540  } else {
4541  statement.setNull(6, java.sql.Types.INTEGER);
4542  }
4543 
4544  if (attr.getAttributeType().getValueType() == TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.DATETIME
4545  || attr.getAttributeType().getValueType() == TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.LONG) {
4546  statement.setLong(7, attr.getValueLong());
4547  } else {
4548  statement.setNull(7, java.sql.Types.BIGINT);
4549  }
4550 
4551  if (attr.getAttributeType().getValueType() == TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.DOUBLE) {
4552  statement.setDouble(8, attr.getValueDouble());
4553  } else {
4554  statement.setNull(8, java.sql.Types.DOUBLE);
4555  }
4556 
4557  connection.executeUpdate(statement);
4558  try (ResultSet resultSet = statement.getGeneratedKeys()) {
4559  if (!resultSet.next()) {
4560  throw new TskCoreException(String.format("Failed to insert file attribute "
4561  + "with id=%d. The expected key was not generated", attr.getId()));
4562  }
4563 
4564  attr.setId(resultSet.getLong(1));
4565  }
4566  }
4567 
4578  String addSourceToArtifactAttribute(BlackboardAttribute attr, String source) throws TskCoreException {
4579  /*
4580  * WARNING: This is a temporary implementation that is not safe and
4581  * denormalizes the case datbase.
4582  *
4583  * TODO (JIRA-2294): Provide a safe and normalized solution to tracking
4584  * the sources of artifact attributes.
4585  */
4586  if (null == source || source.isEmpty()) {
4587  throw new TskCoreException("Attempt to add null or empty source module name to artifact attribute");
4588  }
4589  CaseDbConnection connection = null;
4591  Statement queryStmt = null;
4592  Statement updateStmt = null;
4593  ResultSet result = null;
4594  String newSources = "";
4595  try {
4596  connection = connections.getConnection();
4597  connection.beginTransaction();
4598  String valueClause = "";
4599  BlackboardAttribute.TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE valueType = attr.getAttributeType().getValueType();
4600  if (BlackboardAttribute.TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.BYTE != valueType) {
4601  switch (valueType) {
4602  case STRING:
4603  case JSON:
4604  valueClause = " value_text = '" + escapeSingleQuotes(attr.getValueString()) + "'";
4605  break;
4606  case INTEGER:
4607  valueClause = " value_int32 = " + attr.getValueInt();
4608  break;
4609  case LONG:
4610  case DATETIME:
4611  valueClause = " value_int64 = " + attr.getValueLong();
4612  break;
4613  case DOUBLE:
4614  valueClause = " value_double = " + attr.getValueDouble();
4615  break;
4616  default:
4617  throw new TskCoreException(String.format("Unrecognized value type for attribute %s", attr.getDisplayString()));
4618  }
4619  String query = "SELECT source FROM blackboard_attributes WHERE"
4620  + " artifact_id = " + attr.getArtifactID()
4621  + " AND attribute_type_id = " + attr.getAttributeType().getTypeID()
4622  + " AND value_type = " + attr.getAttributeType().getValueType().getType()
4623  + " AND " + valueClause + ";";
4624  queryStmt = connection.createStatement();
4625  updateStmt = connection.createStatement();
4626  result = connection.executeQuery(queryStmt, query);
4627  } else {
4628  /*
4629  * SELECT source FROM blackboard_attributes WHERE artifact_id =
4630  * ? AND attribute_type_id = ? AND value_type = 4 AND value_byte
4631  * = ?
4632  */
4633  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_ATTR_BY_VALUE_BYTE);
4634  statement.clearParameters();
4635  statement.setLong(1, attr.getArtifactID());
4636  statement.setLong(2, attr.getAttributeType().getTypeID());
4637  statement.setBytes(3, attr.getValueBytes());
4638  result = connection.executeQuery(statement);
4639  }
4640  while (result.next()) {
4641  String oldSources = result.getString("source");
4642  if (null != oldSources && !oldSources.isEmpty()) {
4643  Set<String> uniqueSources = new HashSet<String>(Arrays.asList(oldSources.split(",")));
4644  if (!uniqueSources.contains(source)) {
4645  newSources = oldSources + "," + source;
4646  } else {
4647  newSources = oldSources;
4648  }
4649  } else {
4650  newSources = source;
4651  }
4652  if (BlackboardAttribute.TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.BYTE != valueType) {
4653  String update = "UPDATE blackboard_attributes SET source = '" + newSources + "' WHERE"
4654  + " artifact_id = " + attr.getArtifactID()
4655  + " AND attribute_type_id = " + attr.getAttributeType().getTypeID()
4656  + " AND value_type = " + attr.getAttributeType().getValueType().getType()
4657  + " AND " + valueClause + ";";
4658  connection.executeUpdate(updateStmt, update);
4659  } else {
4660  /*
4661  * UPDATE blackboard_attributes SET source = ? WHERE
4662  * artifact_id = ? AND attribute_type_id = ? AND value_type
4663  * = 4 AND value_byte = ?
4664  */
4665  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.UPDATE_ATTR_BY_VALUE_BYTE);
4666  statement.clearParameters();
4667  statement.setString(1, newSources);
4668  statement.setLong(2, attr.getArtifactID());
4669  statement.setLong(3, attr.getAttributeType().getTypeID());
4670  statement.setBytes(4, attr.getValueBytes());
4671  connection.executeUpdate(statement);
4672  }
4673  }
4674  connection.commitTransaction();
4675  return newSources;
4676  } catch (SQLException ex) {
4677  rollbackTransaction(connection);
4678  throw new TskCoreException(String.format("Error adding source module to attribute %s", attr.getDisplayString()), ex);
4679  } finally {
4680  closeResultSet(result);
4681  closeStatement(updateStmt);
4682  closeStatement(queryStmt);
4683  closeConnection(connection);
4685  }
4686  }
4687 
4702  public BlackboardAttribute.Type addArtifactAttributeType(String attrTypeString, TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE valueType, String displayName) throws TskCoreException, TskDataException {
4703  CaseDbConnection connection = null;
4705  Statement s = null;
4706  ResultSet rs = null;
4707  try {
4708  connection = connections.getConnection();
4709  connection.beginTransaction();
4710  s = connection.createStatement();
4711  rs = connection.executeQuery(s, "SELECT attribute_type_id FROM blackboard_attribute_types WHERE type_name = '" + attrTypeString + "'"); //NON-NLS
4712  if (!rs.next()) {
4713  rs.close();
4714  rs = connection.executeQuery(s, "SELECT MAX(attribute_type_id) AS highest_id FROM blackboard_attribute_types");
4715  int maxID = 0;
4716  if (rs.next()) {
4717  maxID = rs.getInt("highest_id");
4718  if (maxID < MIN_USER_DEFINED_TYPE_ID) {
4719  maxID = MIN_USER_DEFINED_TYPE_ID;
4720  } else {
4721  maxID++;
4722  }
4723  }
4724  connection.executeUpdate(s, "INSERT INTO blackboard_attribute_types (attribute_type_id, type_name, display_name, value_type) VALUES ('" + maxID + "', '" + attrTypeString + "', '" + displayName + "', '" + valueType.getType() + "')"); //NON-NLS
4725  BlackboardAttribute.Type type = new BlackboardAttribute.Type(maxID, attrTypeString, displayName, valueType);
4726  this.typeIdToAttributeTypeMap.put(type.getTypeID(), type);
4727  this.typeNameToAttributeTypeMap.put(type.getTypeName(), type);
4728  connection.commitTransaction();
4729  return type;
4730  } else {
4731  throw new TskDataException("The attribute type that was added was already within the system.");
4732  }
4733 
4734  } catch (SQLException ex) {
4735  rollbackTransaction(connection);
4736  throw new TskCoreException("Error adding attribute type", ex);
4737  } finally {
4738  closeResultSet(rs);
4739  closeStatement(s);
4740  closeConnection(connection);
4742  }
4743  }
4744 
4755  public BlackboardAttribute.Type getAttributeType(String attrTypeName) throws TskCoreException {
4756  if (this.typeNameToAttributeTypeMap.containsKey(attrTypeName)) {
4757  return this.typeNameToAttributeTypeMap.get(attrTypeName);
4758  }
4759  CaseDbConnection connection = null;
4760  Statement s = null;
4761  ResultSet rs = null;
4763  try {
4764  connection = connections.getConnection();
4765  s = connection.createStatement();
4766  rs = connection.executeQuery(s, "SELECT attribute_type_id, type_name, display_name, value_type FROM blackboard_attribute_types WHERE type_name = '" + attrTypeName + "'"); //NON-NLS
4767  BlackboardAttribute.Type type = null;
4768  if (rs.next()) {
4769  type = new BlackboardAttribute.Type(rs.getInt("attribute_type_id"), rs.getString("type_name"),
4770  rs.getString("display_name"), TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.fromType(rs.getLong("value_type")));
4771  this.typeIdToAttributeTypeMap.put(type.getTypeID(), type);
4772  this.typeNameToAttributeTypeMap.put(attrTypeName, type);
4773  }
4774  return type;
4775  } catch (SQLException ex) {
4776  throw new TskCoreException("Error getting attribute type id", ex);
4777  } finally {
4778  closeResultSet(rs);
4779  closeStatement(s);
4780  closeConnection(connection);
4782  }
4783  }
4784 
4795  BlackboardAttribute.Type getAttributeType(int typeID) throws TskCoreException {
4796  if (this.typeIdToAttributeTypeMap.containsKey(typeID)) {
4797  return this.typeIdToAttributeTypeMap.get(typeID);
4798  }
4799  CaseDbConnection connection = null;
4800  Statement s = null;
4801  ResultSet rs = null;
4803  try {
4804  connection = connections.getConnection();
4805  s = connection.createStatement();
4806  rs = connection.executeQuery(s, "SELECT attribute_type_id, type_name, display_name, value_type FROM blackboard_attribute_types WHERE attribute_type_id = " + typeID + ""); //NON-NLS
4807  BlackboardAttribute.Type type = null;
4808  if (rs.next()) {
4809  type = new BlackboardAttribute.Type(rs.getInt("attribute_type_id"), rs.getString("type_name"),
4810  rs.getString("display_name"), TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.fromType(rs.getLong("value_type")));
4811  this.typeIdToAttributeTypeMap.put(typeID, type);
4812  this.typeNameToAttributeTypeMap.put(type.getTypeName(), type);
4813  }
4814  return type;
4815  } catch (SQLException ex) {
4816  throw new TskCoreException("Error getting attribute type id", ex);
4817  } finally {
4818  closeResultSet(rs);
4819  closeStatement(s);
4820  closeConnection(connection);
4822  }
4823  }
4824 
4835  public BlackboardArtifact.Type getArtifactType(String artTypeName) throws TskCoreException {
4836  if (this.typeNameToArtifactTypeMap.containsKey(artTypeName)) {
4837  return this.typeNameToArtifactTypeMap.get(artTypeName);
4838  }
4839  CaseDbConnection connection = null;
4840  Statement s = null;
4841  ResultSet rs = null;
4843  try {
4844  connection = connections.getConnection();
4845  s = connection.createStatement();
4846  rs = connection.executeQuery(s, "SELECT artifact_type_id, type_name, display_name, category_type FROM blackboard_artifact_types WHERE type_name = '" + artTypeName + "'"); //NON-NLS
4847  BlackboardArtifact.Type type = null;
4848  if (rs.next()) {
4849  type = new BlackboardArtifact.Type(rs.getInt("artifact_type_id"),
4850  rs.getString("type_name"), rs.getString("display_name"),
4851  BlackboardArtifact.Category.fromID(rs.getInt("category_type")));
4852  this.typeIdToArtifactTypeMap.put(type.getTypeID(), type);
4853  this.typeNameToArtifactTypeMap.put(artTypeName, type);
4854  }
4855  return type;
4856  } catch (SQLException ex) {
4857  throw new TskCoreException("Error getting artifact type from the database", ex);
4858  } finally {
4859  closeResultSet(rs);
4860  closeStatement(s);
4861  closeConnection(connection);
4863  }
4864  }
4865 
4877  BlackboardArtifact.Type getArtifactType(int artTypeId) throws TskCoreException {
4878  if (this.typeIdToArtifactTypeMap.containsKey(artTypeId)) {
4879  return typeIdToArtifactTypeMap.get(artTypeId);
4880  }
4881  CaseDbConnection connection = null;
4882  Statement s = null;
4883  ResultSet rs = null;
4885  try {
4886  connection = connections.getConnection();
4887  s = connection.createStatement();
4888  rs = connection.executeQuery(s, "SELECT artifact_type_id, type_name, display_name, category_type FROM blackboard_artifact_types WHERE artifact_type_id = " + artTypeId + ""); //NON-NLS
4889  BlackboardArtifact.Type type = null;
4890  if (rs.next()) {
4891  type = new BlackboardArtifact.Type(rs.getInt("artifact_type_id"),
4892  rs.getString("type_name"), rs.getString("display_name"),
4893  BlackboardArtifact.Category.fromID(rs.getInt("category_type")));
4894  this.typeIdToArtifactTypeMap.put(artTypeId, type);
4895  this.typeNameToArtifactTypeMap.put(type.getTypeName(), type);
4896  return type;
4897  } else {
4898  throw new TskCoreException("No artifact type found matching id: " + artTypeId);
4899  }
4900  } catch (SQLException ex) {
4901  throw new TskCoreException("Error getting artifact type from the database", ex);
4902  } finally {
4903  closeResultSet(rs);
4904  closeStatement(s);
4905  closeConnection(connection);
4907  }
4908  }
4909 
4925  public BlackboardArtifact.Type addBlackboardArtifactType(String artifactTypeName, String displayName) throws TskCoreException, TskDataException {
4926 
4927  return addBlackboardArtifactType(artifactTypeName, displayName, BlackboardArtifact.Category.DATA_ARTIFACT);
4928  }
4929 
4945  BlackboardArtifact.Type addBlackboardArtifactType(String artifactTypeName, String displayName, BlackboardArtifact.Category category) throws TskCoreException, TskDataException {
4946  CaseDbConnection connection = null;
4948  Statement s = null;
4949  ResultSet rs = null;
4950  try {
4951  connection = connections.getConnection();
4952  connection.beginTransaction();
4953  s = connection.createStatement();
4954  rs = connection.executeQuery(s, "SELECT artifact_type_id FROM blackboard_artifact_types WHERE type_name = '" + artifactTypeName + "'"); //NON-NLS
4955  if (!rs.next()) {
4956  rs.close();
4957  rs = connection.executeQuery(s, "SELECT MAX(artifact_type_id) AS highest_id FROM blackboard_artifact_types");
4958  int maxID = 0;
4959  if (rs.next()) {
4960  maxID = rs.getInt("highest_id");
4961  if (maxID < MIN_USER_DEFINED_TYPE_ID) {
4962  maxID = MIN_USER_DEFINED_TYPE_ID;
4963  } else {
4964  maxID++;
4965  }
4966  }
4967  connection.executeUpdate(s, "INSERT INTO blackboard_artifact_types (artifact_type_id, type_name, display_name, category_type) VALUES ('" + maxID + "', '" + artifactTypeName + "', '" + displayName + "', " + category.getID() + " )"); //NON-NLS
4968  BlackboardArtifact.Type type = new BlackboardArtifact.Type(maxID, artifactTypeName, displayName, category);
4969  this.typeIdToArtifactTypeMap.put(type.getTypeID(), type);
4970  this.typeNameToArtifactTypeMap.put(type.getTypeName(), type);
4971  connection.commitTransaction();
4972  return type;
4973  } else {
4974  throw new TskDataException("The attribute type that was added was already within the system.");
4975  }
4976  } catch (SQLException ex) {
4977  rollbackTransaction(connection);
4978  throw new TskCoreException("Error adding artifact type", ex);
4979  } finally {
4980  closeResultSet(rs);
4981  closeStatement(s);
4982  closeConnection(connection);
4984  }
4985  }
4986 
4987  public ArrayList<BlackboardAttribute> getBlackboardAttributes(final BlackboardArtifact artifact) throws TskCoreException {
4988  CaseDbConnection connection = null;
4989  Statement statement = null;
4990  ResultSet rs = null;
4992  try {
4993  connection = connections.getConnection();
4994  statement = connection.createStatement();
4995  rs = connection.executeQuery(statement, "SELECT attrs.artifact_id AS artifact_id, "
4996  + "attrs.source AS source, attrs.context AS context, attrs.attribute_type_id AS attribute_type_id, "
4997  + "attrs.value_type AS value_type, attrs.value_byte AS value_byte, "
4998  + "attrs.value_text AS value_text, attrs.value_int32 AS value_int32, "
4999  + "attrs.value_int64 AS value_int64, attrs.value_double AS value_double, "
5000  + "types.type_name AS type_name, types.display_name AS display_name "
5001  + "FROM blackboard_attributes AS attrs, blackboard_attribute_types AS types WHERE attrs.artifact_id = " + artifact.getArtifactID()
5002  + " AND attrs.attribute_type_id = types.attribute_type_id");
5003  ArrayList<BlackboardAttribute> attributes = new ArrayList<BlackboardAttribute>();
5004  while (rs.next()) {
5005  int attributeTypeId = rs.getInt("attribute_type_id");
5006  String attributeTypeName = rs.getString("type_name");
5007  BlackboardAttribute.Type attributeType;
5008  if (this.typeIdToAttributeTypeMap.containsKey(attributeTypeId)) {
5009  attributeType = this.typeIdToAttributeTypeMap.get(attributeTypeId);
5010  } else {
5011  attributeType = new BlackboardAttribute.Type(attributeTypeId, attributeTypeName,
5012  rs.getString("display_name"),
5014  this.typeIdToAttributeTypeMap.put(attributeTypeId, attributeType);
5015  this.typeNameToAttributeTypeMap.put(attributeTypeName, attributeType);
5016  }
5017 
5018  final BlackboardAttribute attr = new BlackboardAttribute(
5019  rs.getLong("artifact_id"),
5020  attributeType,
5021  rs.getString("source"),
5022  rs.getString("context"),
5023  rs.getInt("value_int32"),
5024  rs.getLong("value_int64"),
5025  rs.getDouble("value_double"),
5026  rs.getString("value_text"),
5027  rs.getBytes("value_byte"), this
5028  );
5029  attr.setParentDataSourceID(artifact.getDataSourceObjectID());
5030  attributes.add(attr);
5031  }
5032  return attributes;
5033  } catch (SQLException ex) {
5034  throw new TskCoreException("Error getting attributes for artifact, artifact id = " + artifact.getArtifactID(), ex);
5035  } finally {
5036  closeResultSet(rs);
5037  closeStatement(statement);
5038  closeConnection(connection);
5040  }
5041  }
5042 
5052  ArrayList<Attribute> getFileAttributes(final AbstractFile file) throws TskCoreException {
5053  CaseDbConnection connection = null;
5054  Statement statement = null;
5055  ResultSet rs = null;
5057  try {
5058  connection = connections.getConnection();
5059  statement = connection.createStatement();
5060  rs = connection.executeQuery(statement, "SELECT attrs.id as id, attrs.obj_id AS obj_id, "
5061  + "attrs.attribute_type_id AS attribute_type_id, "
5062  + "attrs.value_type AS value_type, attrs.value_byte AS value_byte, "
5063  + "attrs.value_text AS value_text, attrs.value_int32 AS value_int32, "
5064  + "attrs.value_int64 AS value_int64, attrs.value_double AS value_double, "
5065  + "types.type_name AS type_name, types.display_name AS display_name "
5066  + "FROM tsk_file_attributes AS attrs "
5067  + " INNER JOIN blackboard_attribute_types AS types "
5068  + " ON attrs.attribute_type_id = types.attribute_type_id "
5069  + " WHERE attrs.obj_id = " + file.getId());
5070 
5071  ArrayList<Attribute> attributes = new ArrayList<Attribute>();
5072  while (rs.next()) {
5073  int attributeTypeId = rs.getInt("attribute_type_id");
5074  String attributeTypeName = rs.getString("type_name");
5075  BlackboardAttribute.Type attributeType;
5076  if (this.typeIdToAttributeTypeMap.containsKey(attributeTypeId)) {
5077  attributeType = this.typeIdToAttributeTypeMap.get(attributeTypeId);
5078  } else {
5079  attributeType = new BlackboardAttribute.Type(attributeTypeId, attributeTypeName,
5080  rs.getString("display_name"),
5081  BlackboardAttribute.TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.fromType(rs.getInt("value_type")));
5082  this.typeIdToAttributeTypeMap.put(attributeTypeId, attributeType);
5083  this.typeNameToAttributeTypeMap.put(attributeTypeName, attributeType);
5084  }
5085 
5086  final Attribute attr = new Attribute(
5087  rs.getLong("id"),
5088  rs.getLong("obj_id"),
5089  attributeType,
5090  rs.getInt("value_int32"),
5091  rs.getLong("value_int64"),
5092  rs.getDouble("value_double"),
5093  rs.getString("value_text"),
5094  rs.getBytes("value_byte"), this
5095  );
5096  attributes.add(attr);
5097  }
5098  return attributes;
5099  } catch (SQLException ex) {
5100  throw new TskCoreException("Error getting attributes for file, file id = " + file.getId(), ex);
5101  } finally {
5102  closeResultSet(rs);
5103  closeStatement(statement);
5104  closeConnection(connection);
5106  }
5107  }
5108 
5121  public ArrayList<BlackboardAttribute> getMatchingAttributes(String whereClause) throws TskCoreException {
5122  CaseDbConnection connection = null;
5123  Statement s = null;
5124  ResultSet rs = null;
5126  try {
5127  connection = connections.getConnection();
5128  s = connection.createStatement();
5129  rs = connection.executeQuery(s, "SELECT blackboard_attributes.artifact_id AS artifact_id, "
5130  + "blackboard_attributes.source AS source, blackboard_attributes.context AS context, "
5131  + "blackboard_attributes.attribute_type_id AS attribute_type_id, "
5132  + "blackboard_attributes.value_type AS value_type, blackboard_attributes.value_byte AS value_byte, "
5133  + "blackboard_attributes.value_text AS value_text, blackboard_attributes.value_int32 AS value_int32, "
5134  + "blackboard_attributes.value_int64 AS value_int64, blackboard_attributes.value_double AS value_double "
5135  + "FROM blackboard_attributes " + whereClause); //NON-NLS
5136  ArrayList<BlackboardAttribute> matches = new ArrayList<BlackboardAttribute>();
5137  while (rs.next()) {
5139  // attribute type is cached, so this does not necessarily call to the db
5140  type = this.getAttributeType(rs.getInt("attribute_type_id"));
5142  rs.getLong("artifact_id"),
5143  type,
5144  rs.getString("source"),
5145  rs.getString("context"),
5146  rs.getInt("value_int32"),
5147  rs.getLong("value_int64"),
5148  rs.getDouble("value_double"),
5149  rs.getString("value_text"),
5150  rs.getBytes("value_byte"), this
5151  );
5152  matches.add(attr);
5153  }
5154  return matches;
5155  } catch (SQLException ex) {
5156  throw new TskCoreException("Error getting attributes using this where clause: " + whereClause, ex);
5157  } finally {
5158  closeResultSet(rs);
5159  closeStatement(s);
5160  closeConnection(connection);
5162  }
5163  }
5164 
5176  public ArrayList<BlackboardArtifact> getMatchingArtifacts(String whereClause) throws TskCoreException {
5177  String query = "SELECT blackboard_artifacts.artifact_id AS artifact_id, "
5178  + "blackboard_artifacts.obj_id AS obj_id, blackboard_artifacts.artifact_obj_id AS artifact_obj_id, blackboard_artifacts.data_source_obj_id AS data_source_obj_id, blackboard_artifacts.artifact_type_id AS artifact_type_id, "
5179  + "blackboard_artifacts.review_status_id AS review_status_id "
5180  + "FROM blackboard_artifacts " + whereClause;
5182  try (CaseDbConnection connection = connections.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(query)) {
5183 
5184  List<Long> analysisArtifactObjIds = new ArrayList<>();
5185  List<Long> dataArtifactObjIds = new ArrayList<>();
5186  while (resultSet.next()) {
5187  BlackboardArtifact.Type type = this.getArtifactType(resultSet.getInt("artifact_type_id"));
5188  if (type.getCategory() == BlackboardArtifact.Category.ANALYSIS_RESULT) {
5189  analysisArtifactObjIds.add(resultSet.getLong("artifact_obj_id"));
5190  } else {
5191  dataArtifactObjIds.add(resultSet.getLong("artifact_obj_id"));
5192  }
5193  }
5194 
5195  ArrayList<BlackboardArtifact> matches = new ArrayList<>();
5196  if (!analysisArtifactObjIds.isEmpty()) {
5197  matches.addAll(getArtifactsForValues(BlackboardArtifact.Category.ANALYSIS_RESULT, "artifacts.artifact_obj_id", analysisArtifactObjIds, connection));
5198  }
5199 
5200  if (!dataArtifactObjIds.isEmpty()) {
5201  matches.addAll(getArtifactsForValues(BlackboardArtifact.Category.DATA_ARTIFACT, "artifacts.artifact_obj_id", dataArtifactObjIds, connection));
5202  }
5203 
5204  return matches;
5205  } catch (SQLException ex) {
5206  throw new TskCoreException("Error getting attributes using this where clause: " + whereClause, ex);
5207  } finally {
5209  }
5210  }
5211 
5226  @Deprecated
5227  public BlackboardArtifact newBlackboardArtifact(int artifactTypeID, long obj_id) throws TskCoreException {
5228  BlackboardArtifact.Type type = getArtifactType(artifactTypeID);
5229  if (type == null) {
5230  throw new TskCoreException("Unknown artifact type for id: " + artifactTypeID);
5231  }
5232 
5233  Category category = type.getCategory();
5234  if (category == null) {
5235  throw new TskCoreException(String.format("No category for %s (id: %d)",
5236  type.getDisplayName() == null ? "<null>" : type.getDisplayName(),
5237  type.getTypeID()));
5238  }
5239 
5240  Content content = getContentById(obj_id);
5241  if (content == null) {
5242  throw new TskCoreException("No content found for object id: " + obj_id);
5243  }
5244 
5245  switch (category) {
5246  case ANALYSIS_RESULT:
5247  return content.newAnalysisResult(type, Score.SCORE_UNKNOWN, null, null, null, Collections.emptyList())
5248  .getAnalysisResult();
5249  case DATA_ARTIFACT:
5250  return content.newDataArtifact(type, Collections.emptyList());
5251  default:
5252  throw new TskCoreException("Unknown category type: " + category.getName());
5253  }
5254  }
5255 
5268  @Deprecated
5269  @SuppressWarnings("deprecation")
5270  public BlackboardArtifact newBlackboardArtifact(ARTIFACT_TYPE artifactType, long obj_id) throws TskCoreException {
5271  return newBlackboardArtifact(artifactType.getTypeID(), obj_id);
5272  }
5273 
5289  @Deprecated
5290  @SuppressWarnings("deprecation")
5291  BlackboardArtifact newBlackboardArtifact(int artifactTypeID, long obj_id, long data_source_obj_id) throws TskCoreException {
5292  BlackboardArtifact.Type type = getArtifactType(artifactTypeID);
5293  try (CaseDbConnection connection = connections.getConnection()) {
5294  return newBlackboardArtifact(artifactTypeID, obj_id, type.getTypeName(), type.getDisplayName(), data_source_obj_id, connection);
5295  }
5296  }
5297 
5298  @Deprecated
5299  private BlackboardArtifact newBlackboardArtifact(int artifact_type_id, long obj_id, String artifactTypeName, String artifactDisplayName) throws TskCoreException {
5300  try (CaseDbConnection connection = connections.getConnection()) {
5301  long data_source_obj_id = getDataSourceObjectId(connection, obj_id);
5302  return this.newBlackboardArtifact(artifact_type_id, obj_id, artifactTypeName, artifactDisplayName, data_source_obj_id, connection);
5303  }
5304  }
5305 
5306  PreparedStatement createInsertArtifactStatement(int artifact_type_id, long obj_id, long artifact_obj_id, long data_source_obj_id, CaseDbConnection connection) throws TskCoreException, SQLException {
5307 
5308  PreparedStatement statement;
5309  if (dbType == DbType.POSTGRESQL) {
5310  statement = connection.getPreparedStatement(PREPARED_STATEMENT.POSTGRESQL_INSERT_ARTIFACT, Statement.RETURN_GENERATED_KEYS);
5311  statement.clearParameters();
5312  statement.setLong(1, obj_id);
5313  statement.setLong(2, artifact_obj_id);
5314  statement.setLong(3, data_source_obj_id);
5315  statement.setInt(4, artifact_type_id);
5316  } else {
5317  statement = connection.getPreparedStatement(PREPARED_STATEMENT.INSERT_ARTIFACT, Statement.RETURN_GENERATED_KEYS);
5318  statement.clearParameters();
5319  this.nextArtifactId++;
5320  statement.setLong(1, this.nextArtifactId);
5321  statement.setLong(2, obj_id);
5322  statement.setLong(3, artifact_obj_id);
5323  statement.setLong(4, data_source_obj_id);
5324  statement.setInt(5, artifact_type_id);
5325  }
5326 
5327  return statement;
5328  }
5329 
5346  @Deprecated
5347  private BlackboardArtifact newBlackboardArtifact(int artifact_type_id, long obj_id, String artifactTypeName, String artifactDisplayName, long data_source_obj_id, CaseDbConnection connection) throws TskCoreException {
5348  BlackboardArtifact.Type type = getArtifactType(artifact_type_id);
5349  try {
5350  if (type.getCategory() == BlackboardArtifact.Category.ANALYSIS_RESULT) {
5351  return blackboard.newAnalysisResult(type, obj_id, data_source_obj_id, Score.SCORE_UNKNOWN, null, null, null, Collections.emptyList()).getAnalysisResult();
5352  } else {
5353  return blackboard.newDataArtifact(type, obj_id, data_source_obj_id, Collections.emptyList(), null);
5354  }
5355  } catch (BlackboardException ex) {
5356  throw new TskCoreException("Error creating a blackboard artifact", ex);
5357  }
5358  }
5359 
5378  AnalysisResult newAnalysisResult(BlackboardArtifact.Type artifactType, long objId, Long dataSourceObjId, Score score, String conclusion, String configuration, String justification, CaseDbConnection connection) throws TskCoreException {
5379 
5380  if (artifactType.getCategory() != BlackboardArtifact.Category.ANALYSIS_RESULT) {
5381  throw new TskCoreException(String.format("Artifact type (name = %s) is not of the AnalysisResult category. ", artifactType.getTypeName()));
5382  }
5383 
5384  long artifactID;
5386  try {
5387  // add a row in tsk_objects
5388  long artifactObjId = addObject(objId, TskData.ObjectType.ARTIFACT.getObjectType(), connection);
5389 
5390  // add a row in blackboard_artifacts table
5391  PreparedStatement insertArtifactstatement;
5392  ResultSet resultSet = null;
5393  try {
5394  insertArtifactstatement = createInsertArtifactStatement(artifactType.getTypeID(), objId, artifactObjId, dataSourceObjId, connection);
5395  connection.executeUpdate(insertArtifactstatement);
5396  resultSet = insertArtifactstatement.getGeneratedKeys();
5397  resultSet.next();
5398  artifactID = resultSet.getLong(1); //last_insert_rowid()
5399 
5400  // add a row in tsk_analysis_results if any data for it is set
5401  if (score.getSignificance() != Score.Significance.UNKNOWN
5402  || !StringUtils.isBlank(conclusion)
5403  || !StringUtils.isBlank(configuration)
5404  || !StringUtils.isBlank(justification)) {
5405 
5406  PreparedStatement analysisResultsStatement;
5407 
5408  analysisResultsStatement = connection.getPreparedStatement(PREPARED_STATEMENT.INSERT_ANALYSIS_RESULT);
5409  analysisResultsStatement.clearParameters();
5410 
5411  analysisResultsStatement.setLong(1, artifactObjId);
5412  analysisResultsStatement.setString(2, (conclusion != null) ? conclusion : "");
5413  analysisResultsStatement.setInt(3, score.getSignificance().getId());
5414  analysisResultsStatement.setInt(4, score.getPriority().getId());
5415  analysisResultsStatement.setString(5, (configuration != null) ? configuration : "");
5416  analysisResultsStatement.setString(6, (justification != null) ? justification : "");
5417 
5418  connection.executeUpdate(analysisResultsStatement);
5419  }
5420 
5421  return new AnalysisResult(this, artifactID, objId, artifactObjId, dataSourceObjId, artifactType.getTypeID(),
5422  artifactType.getTypeName(), artifactType.getDisplayName(),
5423  BlackboardArtifact.ReviewStatus.UNDECIDED, true,
5424  score, (conclusion != null) ? conclusion : "",
5425  (configuration != null) ? configuration : "", (justification != null) ? justification : "");
5426 
5427  } finally {
5428  closeResultSet(resultSet);
5429  }
5430 
5431  } catch (SQLException ex) {
5432  throw new TskCoreException("Error creating a analysis result", ex);
5433  } finally {
5435  }
5436  }
5437 
5450  boolean getContentHasChildren(Content content) throws TskCoreException {
5451  CaseDbConnection connection = null;
5452  ResultSet rs = null;
5454  try {
5455  connection = connections.getConnection();
5456 
5457  // SELECT COUNT(obj_id) AS count FROM tsk_objects WHERE par_obj_id = ?
5458  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.COUNT_CHILD_OBJECTS_BY_PARENT);
5459  statement.clearParameters();
5460  statement.setLong(1, content.getId());
5461  rs = connection.executeQuery(statement);
5462  boolean hasChildren = false;
5463  if (rs.next()) {
5464  hasChildren = rs.getInt("count") > 0;
5465  }
5466  return hasChildren;
5467  } catch (SQLException e) {
5468  throw new TskCoreException("Error checking for children of parent " + content, e);
5469  } finally {
5470  closeResultSet(rs);
5471  closeConnection(connection);
5473  }
5474  }
5475 
5488  int getContentChildrenCount(Content content) throws TskCoreException {
5489 
5490  if (!this.getHasChildren(content)) {
5491  return 0;
5492  }
5493 
5494  CaseDbConnection connection = null;
5495  ResultSet rs = null;
5497  try {
5498  connection = connections.getConnection();
5499 
5500  // SELECT COUNT(obj_id) AS count FROM tsk_objects WHERE par_obj_id = ?
5501  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.COUNT_CHILD_OBJECTS_BY_PARENT);
5502  statement.clearParameters();
5503  statement.setLong(1, content.getId());
5504  rs = connection.executeQuery(statement);
5505  int countChildren = -1;
5506  if (rs.next()) {
5507  countChildren = rs.getInt("count");
5508  }
5509  return countChildren;
5510  } catch (SQLException e) {
5511  throw new TskCoreException("Error checking for children of parent " + content, e);
5512  } finally {
5513  closeResultSet(rs);
5514  closeConnection(connection);
5516  }
5517  }
5518 
5530  List<Content> getAbstractFileChildren(Content parent, TSK_DB_FILES_TYPE_ENUM type) throws TskCoreException {
5531  CaseDbConnection connection = null;
5532  ResultSet rs = null;
5534  try {
5535  connection = connections.getConnection();
5536 
5537  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_FILES_BY_PARENT_AND_TYPE);
5538  statement.clearParameters();
5539  long parentId = parent.getId();
5540  statement.setLong(1, parentId);
5541  statement.setShort(2, type.getFileType());
5542  rs = connection.executeQuery(statement);
5543  return fileChildren(rs, connection, parentId);
5544  } catch (SQLException ex) {
5545  throw new TskCoreException("Error getting AbstractFile children for Content", ex);
5546  } finally {
5547  closeResultSet(rs);
5548  closeConnection(connection);
5550  }
5551  }
5552 
5562  List<Content> getAbstractFileChildren(Content parent) throws TskCoreException {
5563  CaseDbConnection connection = null;
5564  ResultSet rs = null;
5566  try {
5567  connection = connections.getConnection();
5568 
5569  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_FILES_BY_PARENT);
5570  statement.clearParameters();
5571  long parentId = parent.getId();
5572  statement.setLong(1, parentId);
5573  rs = connection.executeQuery(statement);
5574  return fileChildren(rs, connection, parentId);
5575  } catch (SQLException ex) {
5576  throw new TskCoreException("Error getting AbstractFile children for Content", ex);
5577  } finally {
5578  closeResultSet(rs);
5579  closeConnection(connection);
5581  }
5582  }
5583 
5595  List<Long> getAbstractFileChildrenIds(Content parent, TSK_DB_FILES_TYPE_ENUM type) throws TskCoreException {
5596  CaseDbConnection connection = null;
5597  ResultSet rs = null;
5599  try {
5600  connection = connections.getConnection();
5601 
5602  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_FILE_IDS_BY_PARENT_AND_TYPE);
5603  statement.clearParameters();
5604  statement.setLong(1, parent.getId());
5605  statement.setShort(2, type.getFileType());
5606  rs = connection.executeQuery(statement);
5607  List<Long> children = new ArrayList<Long>();
5608  while (rs.next()) {
5609  children.add(rs.getLong("obj_id"));
5610  }
5611  return children;
5612  } catch (SQLException ex) {
5613  throw new TskCoreException("Error getting AbstractFile children for Content", ex);
5614  } finally {
5615  closeResultSet(rs);
5616  closeConnection(connection);
5618  }
5619  }
5620 
5630  List<Long> getAbstractFileChildrenIds(Content parent) throws TskCoreException {
5631  CaseDbConnection connection = null;
5632  ResultSet rs = null;
5634  try {
5635  connection = connections.getConnection();
5636 
5637  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_FILE_IDS_BY_PARENT);
5638  statement.clearParameters();
5639  statement.setLong(1, parent.getId());
5640  rs = connection.executeQuery(statement);
5641  List<Long> children = new ArrayList<Long>();
5642  while (rs.next()) {
5643  children.add(rs.getLong("obj_id"));
5644  }
5645  return children;
5646  } catch (SQLException ex) {
5647  throw new TskCoreException("Error getting AbstractFile children for Content", ex);
5648  } finally {
5649  closeResultSet(rs);
5650  closeConnection(connection);
5652  }
5653  }
5654 
5665  List<Long> getBlackboardArtifactChildrenIds(Content parent) throws TskCoreException {
5666  CaseDbConnection connection = null;
5667  ResultSet rs = null;
5669  try {
5670  connection = connections.getConnection();
5671 
5672  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_ARTIFACT_OBJECTIDS_BY_PARENT);
5673  statement.clearParameters();
5674  statement.setLong(1, parent.getId());
5675  rs = connection.executeQuery(statement);
5676  List<Long> children = new ArrayList<Long>();
5677  while (rs.next()) {
5678  children.add(rs.getLong("obj_id"));
5679  }
5680  return children;
5681  } catch (SQLException ex) {
5682  throw new TskCoreException("Error getting children for BlackboardArtifact", ex);
5683  } finally {
5684  closeResultSet(rs);
5685  closeConnection(connection);
5687  }
5688  }
5689 
5699  List<Content> getBlackboardArtifactChildren(Content parent) throws TskCoreException {
5700  long parentId = parent.getId();
5701  List<Content> lc = new ArrayList<>();
5702  lc.addAll(blackboard.getAnalysisResults(parentId));
5703  lc.addAll(blackboard.getDataArtifactsBySource(parentId));
5704  return lc;
5705  }
5706 
5715  Collection<ObjectInfo> getChildrenInfo(Content c) throws TskCoreException {
5716  CaseDbConnection connection = null;
5717  Statement s = null;
5718  ResultSet rs = null;
5720  try {
5721  connection = connections.getConnection();
5722  s = connection.createStatement();
5723  rs = connection.executeQuery(s, "SELECT tsk_objects.obj_id AS obj_id, tsk_objects.type AS type " //NON-NLS
5724  + "FROM tsk_objects LEFT JOIN tsk_files " //NON-NLS
5725  + "ON tsk_objects.obj_id = tsk_files.obj_id " //NON-NLS
5726  + "WHERE tsk_objects.par_obj_id = " + c.getId()
5727  + " ORDER BY tsk_objects.obj_id"); //NON-NLS
5728  Collection<ObjectInfo> infos = new ArrayList<ObjectInfo>();
5729  while (rs.next()) {
5730  infos.add(new ObjectInfo(rs.getLong("obj_id"), ObjectType.valueOf(rs.getShort("type")))); //NON-NLS
5731  }
5732  return infos;
5733  } catch (SQLException ex) {
5734  throw new TskCoreException("Error getting Children Info for Content", ex);
5735  } finally {
5736  closeResultSet(rs);
5737  closeStatement(s);
5738  closeConnection(connection);
5740  }
5741  }
5742 
5753  ObjectInfo getParentInfo(Content c) throws TskCoreException {
5754  return getParentInfo(c.getId());
5755  }
5756 
5767  ObjectInfo getParentInfo(long contentId) throws TskCoreException {
5769  CaseDbConnection connection = null;
5770  Statement s = null;
5771  ResultSet rs = null;
5772  try {
5773  connection = connections.getConnection();
5774  s = connection.createStatement();
5775  rs = connection.executeQuery(s, "SELECT parent.obj_id AS obj_id, parent.type AS type " //NON-NLS
5776  + "FROM tsk_objects AS parent INNER JOIN tsk_objects AS child " //NON-NLS
5777  + "ON child.par_obj_id = parent.obj_id " //NON-NLS
5778  + "WHERE child.obj_id = " + contentId); //NON-NLS
5779  if (rs.next()) {
5780  return new ObjectInfo(rs.getLong("obj_id"), ObjectType.valueOf(rs.getShort("type")));
5781  } else {
5782  return null;
5783  }
5784  } catch (SQLException ex) {
5785  throw new TskCoreException("Error getting Parent Info for Content: " + contentId, ex);
5786  } finally {
5787  closeResultSet(rs);
5788  closeStatement(s);
5789  closeConnection(connection);
5791  }
5792  }
5793 
5804  Directory getParentDirectory(FsContent fsc) throws TskCoreException {
5805  if (fsc.isRoot()) {
5806  // Given FsContent is a root object and can't have parent directory
5807  return null;
5808  } else {
5809  ObjectInfo parentInfo = getParentInfo(fsc);
5810  if (parentInfo == null) {
5811  return null;
5812  }
5813  Directory parent = null;
5814  if (parentInfo.type == ObjectType.ABSTRACTFILE) {
5815  parent = getDirectoryById(parentInfo.id, fsc.getFileSystem());
5816  } else {
5817  throw new TskCoreException("Parent of FsContent (id: " + fsc.getId() + ") has wrong type to be directory: " + parentInfo.type);
5818  }
5819  return parent;
5820  }
5821  }
5822 
5834  public Content getContentById(long id) throws TskCoreException {
5835  // First check to see if this exists in our frequently used content cache.
5836  Content content = frequentlyUsedContentMap.get(id);
5837  if (null != content) {
5838  return content;
5839  }
5840 
5841  long parentId;
5842  TskData.ObjectType type;
5843 
5844  CaseDbConnection connection = null;
5845  Statement s = null;
5846  ResultSet rs = null;
5848  try {
5849  connection = connections.getConnection();
5850  s = connection.createStatement();
5851  rs = connection.executeQuery(s, "SELECT * FROM tsk_objects WHERE obj_id = " + id + " LIMIT 1"); //NON-NLS
5852  if (!rs.next()) {
5853  return null;
5854  }
5855  parentId = rs.getLong("par_obj_id"); //NON-NLS
5856  type = TskData.ObjectType.valueOf(rs.getShort("type")); //NON-NLS
5857  } catch (SQLException ex) {
5858  throw new TskCoreException("Error getting Content by ID.", ex);
5859  } finally {
5860  closeResultSet(rs);
5861  closeStatement(s);
5862  closeConnection(connection);
5864  }
5865 
5866  // Construct the object
5867  switch (type) {
5868  case IMG:
5869  content = getImageById(id);
5870  frequentlyUsedContentMap.put(id, content);
5871  break;
5872  case VS:
5873  content = getVolumeSystemById(id, parentId);
5874  break;
5875  case VOL:
5876  content = getVolumeById(id, parentId);
5877  frequentlyUsedContentMap.put(id, content);
5878  break;
5879  case POOL:
5880  content = getPoolById(id, parentId);
5881  break;
5882  case FS:
5883  content = getFileSystemById(id, parentId);
5884  frequentlyUsedContentMap.put(id, content);
5885  break;
5886  case ABSTRACTFILE:
5887  content = getAbstractFileById(id);
5888 
5889  // Add virtual and root directories to frequently used map.
5890  // Calling isRoot() on local directories goes up the entire directory structure
5891  // and they can only be the root of portable cases, so skip trying to add
5892  // them to the cache.
5893  if (((AbstractFile) content).isVirtual()
5894  || ((!(content instanceof LocalDirectory)) && ((AbstractFile) content).isRoot())) {
5895  frequentlyUsedContentMap.put(id, content);
5896  }
5897  break;
5898  case ARTIFACT:
5899  content = getArtifactById(id);
5900  break;
5901  case REPORT:
5902  content = getReportById(id);
5903  break;
5904  case OS_ACCOUNT:
5905  content = this.osAccountManager.getOsAccountByObjectId(id);
5906  break;
5907  case HOST_ADDRESS:
5908  content = hostAddressManager.getHostAddress(id);
5909  break;
5910  default:
5911  content = new UnsupportedContent(this, id);
5912  }
5913 
5914  return content;
5915  }
5916 
5924  String getFilePath(long id) {
5925 
5926  String filePath = null;
5927  CaseDbConnection connection = null;
5928  ResultSet rs = null;
5930  try {
5931  connection = connections.getConnection();
5932 
5933  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_LOCAL_PATH_FOR_FILE);
5934  statement.clearParameters();
5935  statement.setLong(1, id);
5936  rs = connection.executeQuery(statement);
5937  if (rs.next()) {
5938  filePath = rs.getString("path");
5939  }
5940  } catch (SQLException | TskCoreException ex) {
5941  logger.log(Level.SEVERE, "Error getting file path for file " + id, ex); //NON-NLS
5942  } finally {
5943  closeResultSet(rs);
5944  closeConnection(connection);
5946  }
5947  return filePath;
5948  }
5949 
5957  TskData.EncodingType getEncodingType(long id) {
5958 
5959  TskData.EncodingType type = TskData.EncodingType.NONE;
5960  CaseDbConnection connection = null;
5961  ResultSet rs = null;
5963  try {
5964  connection = connections.getConnection();
5965  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_ENCODING_FOR_FILE);
5966  statement.clearParameters();
5967  statement.setLong(1, id);
5968  rs = connection.executeQuery(statement);
5969  if (rs.next()) {
5970  type = TskData.EncodingType.valueOf(rs.getInt(1));
5971  }
5972  } catch (SQLException | TskCoreException ex) {
5973  logger.log(Level.SEVERE, "Error getting encoding type for file " + id, ex); //NON-NLS
5974  } finally {
5975  closeResultSet(rs);
5976  closeConnection(connection);
5978  }
5979  return type;
5980  }
5981 
5990  String getFileParentPath(long objectId, CaseDbConnection connection) {
5991  String parentPath = null;
5993  ResultSet rs = null;
5994  try {
5995  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_PATH_FOR_FILE);
5996  statement.clearParameters();
5997  statement.setLong(1, objectId);
5998  rs = connection.executeQuery(statement);
5999  if (rs.next()) {
6000  parentPath = rs.getString("parent_path");
6001  }
6002  } catch (SQLException ex) {
6003  logger.log(Level.SEVERE, "Error getting file parent_path for file " + objectId, ex); //NON-NLS
6004  } finally {
6005  closeResultSet(rs);
6007  }
6008  return parentPath;
6009  }
6010 
6019  String getFileName(long objectId, CaseDbConnection connection) {
6020  String fileName = null;
6022  ResultSet rs = null;
6023  try {
6024  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_FILE_NAME);
6025  statement.clearParameters();
6026  statement.setLong(1, objectId);
6027  rs = connection.executeQuery(statement);
6028  if (rs.next()) {
6029  fileName = rs.getString("name");
6030  }
6031  } catch (SQLException ex) {
6032  logger.log(Level.SEVERE, "Error getting file parent_path for file " + objectId, ex); //NON-NLS
6033  } finally {
6034  closeResultSet(rs);
6036  }
6037  return fileName;
6038  }
6039 
6050  DerivedFile.DerivedMethod getDerivedMethod(long id) throws TskCoreException {
6051 
6052  DerivedFile.DerivedMethod method = null;
6053  CaseDbConnection connection = null;
6054  ResultSet rs1 = null;
6055  ResultSet rs2 = null;
6057  try {
6058  connection = connections.getConnection();
6059 
6060  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_DERIVED_FILE);
6061  statement.clearParameters();
6062  statement.setLong(1, id);
6063  rs1 = connection.executeQuery(statement);
6064  if (rs1.next()) {
6065  int method_id = rs1.getInt("derived_id");
6066  String rederive = rs1.getString("rederive");
6067  method = new DerivedFile.DerivedMethod(method_id, rederive);
6068  statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_FILE_DERIVATION_METHOD);
6069  statement.clearParameters();
6070  statement.setInt(1, method_id);
6071  rs2 = connection.executeQuery(statement);
6072  if (rs2.next()) {
6073  method.setToolName(rs2.getString("tool_name"));
6074  method.setToolVersion(rs2.getString("tool_version"));
6075  method.setOther(rs2.getString("other"));
6076  }
6077  }
6078  } catch (SQLException e) {
6079  logger.log(Level.SEVERE, "Error getting derived method for file: " + id, e); //NON-NLS
6080  } finally {
6081  closeResultSet(rs2);
6082  closeResultSet(rs1);
6083  closeConnection(connection);
6085  }
6086  return method;
6087  }
6088 
6099  public AbstractFile getAbstractFileById(long id) throws TskCoreException {
6100  CaseDbConnection connection = connections.getConnection();
6101  try {
6102  return getAbstractFileById(id, connection);
6103  } finally {
6104  closeConnection(connection);
6105  }
6106  }
6107 
6120  AbstractFile getAbstractFileById(long objectId, CaseDbConnection connection) throws TskCoreException {
6122  ResultSet rs = null;
6123  try {
6124  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_FILE_BY_ID);
6125  statement.clearParameters();
6126  statement.setLong(1, objectId);
6127  rs = connection.executeQuery(statement);
6128  List<AbstractFile> files = resultSetToAbstractFiles(rs, connection);
6129  if (files.size() > 0) {
6130  return files.get(0);
6131  } else {
6132  return null;
6133  }
6134  } catch (SQLException ex) {
6135  throw new TskCoreException("Error getting file by id, id = " + objectId, ex);
6136  } finally {
6137  closeResultSet(rs);
6139  }
6140  }
6141 
6153  public BlackboardArtifact getArtifactById(long id) throws TskCoreException {
6154 
6155  CaseDbConnection connection = null;
6156  ResultSet rs = null;
6158  try {
6159  connection = connections.getConnection();
6160 
6161  // get the artifact type.
6162  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_ARTIFACT_TYPE_BY_ARTIFACT_OBJ_ID);
6163  statement.clearParameters();
6164  statement.setLong(1, id);
6165 
6166  rs = connection.executeQuery(statement);
6167  if (!rs.next()) {
6168  throw new TskCoreException("Error getting artifacttype for artifact with artifact_obj_id = " + id);
6169  }
6170 
6171  // based on the artifact type category, get the analysis result or the data artifact
6172  BlackboardArtifact.Type artifactType = getArtifactType(rs.getInt("artifact_type_id"));
6173  switch (artifactType.getCategory()) {
6174  case ANALYSIS_RESULT:
6175  return blackboard.getAnalysisResultById(id);
6176  case DATA_ARTIFACT:
6177  return blackboard.getDataArtifactById(id);
6178  default:
6179  throw new TskCoreException(String.format("Unknown artifact category for artifact with artifact_obj_id = %d, and artifact type = %s", id, artifactType.getTypeName()));
6180  }
6181 
6182  } catch (SQLException ex) {
6183  throw new TskCoreException("Error getting artifacts by artifact_obj_id, artifact_obj_id = " + id, ex);
6184  } finally {
6185  closeResultSet(rs);
6186  closeConnection(connection);
6188  }
6189  }
6190 
6204  @Deprecated
6205  public BlackboardArtifact getArtifactByArtifactId(long id) throws TskCoreException {
6206  String query = "SELECT artifact_type_id, artifact_obj_id WHERE artifact_id = " + id;
6208 
6209  try (CaseDbConnection connection = connections.getConnection();
6210  Statement statement = connection.createStatement();
6211  ResultSet resultSet = statement.executeQuery(query);) {
6212  if (resultSet != null && resultSet.next()) {
6213  BlackboardArtifact.Type artifactType = this.getArtifactType(resultSet.getInt("artifact_type_id"));
6214  long artifactObjId = resultSet.getLong("artifact_obj_id");
6215  switch (artifactType.getCategory()) {
6216  case ANALYSIS_RESULT:
6217  return blackboard.getAnalysisResultById(artifactObjId);
6218  case DATA_ARTIFACT:
6219  return blackboard.getDataArtifactById(artifactObjId);
6220  }
6221  }
6222  return null;
6223  } catch (SQLException ex) {
6224  throw new TskCoreException("Error getting artifacts by artifact id, artifact id = " + id, ex);
6225  } finally {
6227  }
6228  }
6229 
6242  private long getFileSystemId(long fileId, CaseDbConnection connection) {
6244  ResultSet rs = null;
6245  long ret = -1;
6246  try {
6247  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_FILE_SYSTEM_BY_OBJECT);
6248  statement.clearParameters();
6249  statement.setLong(1, fileId);
6250  rs = connection.executeQuery(statement);
6251  if (rs.next()) {
6252  ret = rs.getLong("fs_obj_id");
6253  if (ret == 0) {
6254  ret = -1;
6255  }
6256  }
6257  } catch (SQLException e) {
6258  logger.log(Level.SEVERE, "Error checking file system id of a file, id = " + fileId, e); //NON-NLS
6259  } finally {
6260  closeResultSet(rs);
6262  }
6263  return ret;
6264  }
6265 
6277  public boolean isFileFromSource(Content dataSource, long fileId) throws TskCoreException {
6278  String query = String.format("SELECT COUNT(*) AS count FROM tsk_files WHERE obj_id = %d AND data_source_obj_id = %d", fileId, dataSource.getId()); //NON-NLS
6279  CaseDbConnection connection = null;
6280  Statement statement = null;
6281  ResultSet resultSet = null;
6283  try {
6284  connection = connections.getConnection();
6285  statement = connection.createStatement();
6286  resultSet = connection.executeQuery(statement, query);
6287  resultSet.next();
6288  return (resultSet.getLong("count") > 0L);
6289  } catch (SQLException ex) {
6290  throw new TskCoreException(String.format("Error executing query %s", query), ex);
6291  } finally {
6292  closeResultSet(resultSet);
6293  closeStatement(statement);
6294  closeConnection(connection);
6296  }
6297  }
6298 
6308  private static boolean containsLikeWildcard(String str) {
6309  if (str == null) {
6310  return false;
6311  } else {
6312  return str.contains("%") || str.contains("_");
6313  }
6314  }
6315 
6327  public List<AbstractFile> findFiles(Content dataSource, String fileName) throws TskCoreException {
6328  String ext = "";
6329  if (!containsLikeWildcard(fileName)) {
6330  ext = SleuthkitCase.extractExtension(fileName);
6331  }
6332 
6333  List<AbstractFile> files = new ArrayList<>();
6334  CaseDbConnection connection = null;
6335  ResultSet resultSet = null;
6337  try {
6338  connection = connections.getConnection();
6339 
6340  PreparedStatement statement;
6341  if (ext.isEmpty()) {
6342  statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_FILES_BY_DATA_SOURCE_AND_NAME);
6343  statement.clearParameters();
6344  statement.setString(1, fileName.toLowerCase());
6345  statement.setLong(2, dataSource.getId());
6346  } else {
6347  statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_FILES_BY_EXTENSION_AND_DATA_SOURCE_AND_NAME);
6348  statement.clearParameters();
6349  statement.setString(1, ext);
6350  statement.setString(2, fileName.toLowerCase());
6351  statement.setLong(3, dataSource.getId());
6352  }
6353 
6354  resultSet = connection.executeQuery(statement);
6355  files.addAll(resultSetToAbstractFiles(resultSet, connection));
6356  } catch (SQLException e) {
6357  throw new TskCoreException(bundle.getString("SleuthkitCase.findFiles.exception.msg3.text"), e);
6358  } finally {
6359  closeResultSet(resultSet);
6360  closeConnection(connection);
6362  }
6363  return files;
6364  }
6365 
6379  public List<AbstractFile> findFiles(Content dataSource, String fileName, String dirSubString) throws TskCoreException {
6380  String ext = "";
6381  if (!containsLikeWildcard(fileName)) {
6382  ext = SleuthkitCase.extractExtension(fileName);
6383  }
6384 
6385  List<AbstractFile> files = new ArrayList<>();
6386  CaseDbConnection connection = null;
6387  ResultSet resultSet = null;
6389  try {
6390  connection = connections.getConnection();
6391  PreparedStatement statement;
6392  if (ext.isEmpty()) {
6393  statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_FILES_BY_DATA_SOURCE_AND_PARENT_PATH_AND_NAME);
6394  statement.clearParameters();
6395  statement.setString(1, fileName.toLowerCase());
6396  statement.setString(2, "%" + dirSubString.toLowerCase() + "%"); //NON-NLS
6397  statement.setLong(3, dataSource.getId());
6398  } else {
6399  statement = connection.getPreparedStatement(PREPARED_STATEMENT.SELECT_FILES_BY_EXTENSION_AND_DATA_SOURCE_AND_PARENT_PATH_AND_NAME);
6400  statement.clearParameters();
6401  statement.setString(1, ext);
6402  statement.setString(2, fileName.toLowerCase());
6403  statement.setString(3, "%" + dirSubString.toLowerCase() + "%"); //NON-NLS
6404  statement.setLong(4, dataSource.getId());
6405  }
6406 
6407  resultSet = connection.executeQuery(statement);
6408  files.addAll(resultSetToAbstractFiles(resultSet, connection));
6409  } catch (SQLException e) {
6410  throw new TskCoreException(bundle.getString("SleuthkitCase.findFiles3.exception.msg3.text"), e);
6411  } finally {
6412  closeResultSet(resultSet);
6413  closeConnection(connection);
6415  }
6416  return files;
6417  }
6418 
6430  public VirtualDirectory addVirtualDirectory(long parentId, String directoryName) throws TskCoreException {
6431  CaseDbTransaction localTrans = beginTransaction();
6432  try {
6433  VirtualDirectory newVD = addVirtualDirectory(parentId, directoryName, localTrans);
6434  localTrans.commit();
6435  localTrans = null;
6436  return newVD;
6437  } finally {
6438  if (null != localTrans) {
6439  try {
6440  localTrans.rollback();
6441  } catch (TskCoreException ex2) {
6442  logger.log(Level.SEVERE, "Failed to rollback transaction after exception", ex2);
6443  }
6444  }
6445  }
6446  }
6447 
6460  long addObject(long parentId, int objectType, CaseDbConnection connection) throws SQLException {
6461  ResultSet resultSet = null;
6463  try {
6464  // INSERT INTO tsk_objects (par_obj_id, type) VALUES (?, ?)
6465  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.INSERT_OBJECT, Statement.RETURN_GENERATED_KEYS);
6466  statement.clearParameters();
6467  if (parentId != 0) {
6468  statement.setLong(1, parentId);
6469  } else {
6470  statement.setNull(1, java.sql.Types.BIGINT);
6471  }
6472  statement.setInt(2, objectType);
6473  connection.executeUpdate(statement);
6474  resultSet = statement.getGeneratedKeys();
6475 
6476  if (resultSet.next()) {
6477  if (parentId != 0) {
6478  setHasChildren(parentId);
6479  }
6480  return resultSet.getLong(1); //last_insert_rowid()
6481  } else {
6482  throw new SQLException("Error inserting object with parent " + parentId + " into tsk_objects");
6483  }
6484  } finally {
6485  closeResultSet(resultSet);
6487  }
6488  }
6489 
6507  public VirtualDirectory addVirtualDirectory(long parentId, String directoryName, CaseDbTransaction transaction) throws TskCoreException {
6508  if (transaction == null) {
6509  throw new TskCoreException("Passed null CaseDbTransaction");
6510  }
6511 
6512  ResultSet resultSet = null;
6513  try {
6514  // Get the parent path.
6515  CaseDbConnection connection = transaction.getConnection();
6516 
6517  String parentPath;
6518  Content parent = this.getAbstractFileById(parentId, connection);
6519  if (parent instanceof AbstractFile) {
6520  if (isRootDirectory((AbstractFile) parent, transaction)) {
6521  parentPath = "/";
6522  } else {
6523  parentPath = ((AbstractFile) parent).getParentPath() + parent.getName() + "/"; //NON-NLS
6524  }
6525  } else {
6526  // The parent was either null or not an abstract file
6527  parentPath = "/";
6528  }
6529 
6530  // Insert a row for the virtual directory into the tsk_objects table.
6531  long newObjId = addObject(parentId, TskData.ObjectType.ABSTRACTFILE.getObjectType(), connection);
6532 
6533  // Insert a row for the virtual directory into the tsk_files table.
6534  // INSERT INTO tsk_files (obj_id, fs_obj_id, name, type, has_path, dir_type, meta_type,
6535  // dir_flags, meta_flags, size, ctime, crtime, atime, mtime, md5, known, mime_type, parent_path, data_source_obj_id,extension,owner_uid, os_account_obj_id)
6536  // VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?)
6537  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.INSERT_FILE);
6538  statement.clearParameters();
6539  statement.setLong(1, newObjId);
6540 
6541  // If the parent is part of a file system, grab its file system ID
6542  if (0 != parentId) {
6543  long parentFs = this.getFileSystemId(parentId, connection);
6544  if (parentFs != -1) {
6545  statement.setLong(2, parentFs);
6546  } else {
6547  statement.setNull(2, java.sql.Types.BIGINT);
6548  }
6549  } else {
6550  statement.setNull(2, java.sql.Types.BIGINT);
6551  }
6552 
6553  // name
6554  statement.setString(3, directoryName);
6555 
6556  //type
6557  statement.setShort(4, TskData.TSK_DB_FILES_TYPE_ENUM.VIRTUAL_DIR.getFileType());
6558  statement.setShort(5, (short) 1);
6559 
6560  //flags
6562  statement.setShort(6, dirType.getValue());
6564  statement.setShort(7, metaType.getValue());
6565 
6566  //allocated
6568  statement.setShort(8, dirFlag.getValue());
6569  final short metaFlags = (short) (TSK_FS_META_FLAG_ENUM.ALLOC.getValue()
6570  | TSK_FS_META_FLAG_ENUM.USED.getValue());
6571  statement.setShort(9, metaFlags);
6572 
6573  //size
6574  statement.setLong(10, 0);
6575 
6576  // nulls for params 11-14
6577  statement.setNull(11, java.sql.Types.BIGINT);
6578  statement.setNull(12, java.sql.Types.BIGINT);
6579  statement.setNull(13, java.sql.Types.BIGINT);
6580  statement.setNull(14, java.sql.Types.BIGINT);
6581 
6582  statement.setNull(15, java.sql.Types.VARCHAR); // MD5
6583  statement.setNull(16, java.sql.Types.VARCHAR); // SHA-256
6584  statement.setByte(17, FileKnown.UNKNOWN.getFileKnownValue()); // Known
6585  statement.setNull(18, java.sql.Types.VARCHAR); // MIME type
6586 
6587  // parent path
6588  statement.setString(19, parentPath);
6589 
6590  // data source object id (same as object id if this is a data source)
6591  long dataSourceObjectId;
6592  if (0 == parentId) {
6593  dataSourceObjectId = newObjId;
6594  } else {
6595  dataSourceObjectId = getDataSourceObjectId(connection, parentId);
6596  }
6597  statement.setLong(20, dataSourceObjectId);
6598 
6599  //extension, since this is not really file we just set it to null
6600  statement.setString(21, null);
6601 
6602  statement.setString(22, OsAccount.NO_OWNER_ID); // ownerUid
6603  statement.setNull(23, java.sql.Types.BIGINT); // osAccountObjId
6604 
6605  connection.executeUpdate(statement);
6606 
6607  return new VirtualDirectory(this, newObjId, dataSourceObjectId, directoryName, dirType,
6608  metaType, dirFlag, metaFlags, null, null, FileKnown.UNKNOWN,
6609  parentPath);
6610  } catch (SQLException e) {
6611  throw new TskCoreException("Error creating virtual directory '" + directoryName + "'", e);
6612  } finally {
6613  closeResultSet(resultSet);
6614  }
6615  }
6616 
6629  public LocalDirectory addLocalDirectory(long parentId, String directoryName) throws TskCoreException {
6630  CaseDbTransaction localTrans = beginTransaction();
6631  try {
6632  LocalDirectory newLD = addLocalDirectory(parentId, directoryName, localTrans);
6633  localTrans.commit();
6634  return newLD;
6635  } catch (TskCoreException ex) {
6636  try {
6637  localTrans.rollback();
6638  } catch (TskCoreException ex2) {
6639  logger.log(Level.SEVERE, String.format("Failed to rollback transaction after exception: %s", ex.getMessage()), ex2);
6640  }
6641  throw ex;
6642  }
6643  }
6644 
6662  public LocalDirectory addLocalDirectory(long parentId, String directoryName, CaseDbTransaction transaction) throws TskCoreException {
6663  if (transaction == null) {
6664  throw new TskCoreException("Passed null CaseDbTransaction");
6665  }
6666 
6667  ResultSet resultSet = null;
6668  try {
6669  // Get the parent path.
6670  CaseDbConnection connection = transaction.getConnection();
6671  AbstractFile parent = getAbstractFileById(parentId, connection);
6672  String parentPath;
6673  if ((parent == null) || isRootDirectory(parent, transaction)) {
6674  parentPath = "/";
6675  } else {
6676  parentPath = parent.getParentPath() + parent.getName() + "/"; //NON-NLS
6677  }
6678 
6679  // Insert a row for the local directory into the tsk_objects table.
6680  long newObjId = addObject(parentId, TskData.ObjectType.ABSTRACTFILE.getObjectType(), connection);
6681 
6682  // Insert a row for the local directory into the tsk_files table.
6683  // INSERT INTO tsk_files (obj_id, fs_obj_id, name, type, has_path, dir_type, meta_type,
6684  // dir_flags, meta_flags, size, ctime, crtime, atime, mtime, md5, sha256, known, mime_type, parent_path, data_source_obj_id, extension, owner_uid, os_account_obj_id)
6685  // VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
6686  PreparedStatement statement = connection.getPreparedStatement(PREPARED_STATEMENT.INSERT_FILE);
6687  statement.clearParameters();
6688  statement.setLong(1, newObjId);
6689 
6690  // The parent of a local directory will never be a file system
6691  statement.setNull(2, java.sql.Types.BIGINT);
6692 
6693  // name
6694  statement.setString(3, directoryName);
6695 
6696  //type
6697  statement.setShort(4, TskData.TSK_DB_FILES_TYPE_ENUM.LOCAL_DIR.getFileType());
6698  statement.setShort(5, (short) 1);
6699 
6700  //flags
6702  statement.setShort(6, dirType.getValue());
6704  statement.setShort(7, metaType.getValue());
6705 
6706  //allocated
6708  statement.setShort(8, dirFlag.getValue());
6709  final short metaFlags = (short) (TSK_FS_META_FLAG_ENUM.ALLOC.getValue()
6710  | TSK_FS_META_FLAG_ENUM.USED.getValue());
6711  statement.setShort(9, metaFlags);
6712 
6713  //size
6714  statement.setLong(10, 0);
6715 
6716  // nulls for params 11-14
6717  statement.setNull(11, java.sql.Types.BIGINT);
6718  statement.setNull(12, java.sql.Types.BIGINT);
6719  statement.setNull(13, java.sql.Types.BIGINT);
6720  statement.setNull(14, java.sql.Types.BIGINT);
6721 
6722  statement.setNull(15, java.sql.Types.VARCHAR); // MD5
6723  statement.setNull(16, java.sql.Types.VARCHAR); // SHA-256
6724  statement.setByte(17, FileKnown.UNKNOWN.getFileKnownValue()); // Known
6725  statement.setNull(18, java.sql.Types.VARCHAR); // MIME type
6726 
6727  // parent path
6728  statement.setString(19, parentPath);
6729 
6730  // data source object id
6731  long dataSourceObjectId = getDataSourceObjectId(connection, parentId);
6732  statement.setLong(20, dataSourceObjectId);
6733 
6734  //extension, since this is a directory we just set it to null
6735  statement.setString(21, null);
6736 
6737  statement.setString(22, OsAccount.NO_OWNER_ID); // ownerUid
6738  statement.setNull(23, java.sql.Types.BIGINT); // osAccountObjId
6739 
6740  connection.executeUpdate(statement);
6741 
6742  return new LocalDirectory(this, newObjId, dataSourceObjectId, directoryName, dirType,
6743  metaType, dirFlag, metaFlags, null, null, FileKnown.UNKNOWN,
6744  parentPath);
6745  } catch (SQLException e) {
6746  throw new TskCoreException("Error creating local directory '" + directoryName + "'", e);
6747  } finally {
6748  closeResultSet(resultSet);
6749  }
6750  }
6751 
6771  public LocalFilesDataSource addLocalFilesDataSource(String deviceId, String rootDirectoryName, String timeZone, CaseDbTransaction transaction) throws TskCoreException {
6772  return addLocalFilesDataSource(deviceId, rootDirectoryName, timeZone, null, transaction);
6773  }
6774 
6795  public LocalFilesDataSource addLocalFilesDataSource(String deviceId, String rootDirectoryName, String timeZone, Host host, CaseDbTransaction transaction) throws TskCoreException {
6796 
6797  Statement statement = null;
6798  try {
6799  CaseDbConnection connection = transaction.getConnection();
6800 
6801  // Insert a row for the root virtual directory of the data source
6802  // into the tsk_objects table.
6803  long newObjId = addObject(0, TskData.ObjectType.ABSTRACTFILE.getObjectType(), connection);
6804 
6805  // If no host was supplied, make one
6806  if (host == null) {
6807  host = getHostManager().newHost("LogicalFileSet_" + newObjId + " Host", transaction);
6808  }
6809 
6810  // Insert a row for the virtual directory of the data source into
6811  // the data_source_info table.
6812  statement = connection.createStatement();
6813  statement.executeUpdate("INSERT INTO data_source_info (obj_id, device_id, time_zone, host_id) "
6814  + "VALUES(" + newObjId + ", '" + deviceId + "', '" + timeZone + "', " + host.getHostId() + ");");
6815 
6816  // Insert a row for the root virtual directory of the data source
6817  // into the tsk_files table. Note that its data source object id is
6818  // its own object id.
6819  // INSERT INTO tsk_files (obj_id, fs_obj_id, name, type, has_path,
6820  // dir_type, meta_type, dir_flags, meta_flags, size, ctime, crtime,
6821  // atime, mtime, md5, known, mime_type, parent_path, data_source_obj_id, extension, owner_uid, os_account_obj_id)
6822  // VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?)
6823  PreparedStatement preparedStatement = connection.getPreparedStatement(PREPARED_STATEMENT.INSERT_FILE);
6824  preparedStatement.clearParameters();
6825  preparedStatement.setLong(1, newObjId);
6826  preparedStatement.setNull(2, java.sql.Types.BIGINT);
6827  preparedStatement.setString(3, rootDirectoryName);
6828  preparedStatement.setShort(4, TskData.TSK_DB_FILES_TYPE_ENUM.VIRTUAL_DIR.getFileType());
6829  preparedStatement.setShort(5, (short) 1);
6831  preparedStatement.setShort(6, TSK_FS_NAME_TYPE_ENUM.DIR.getValue());
6833  preparedStatement.setShort(7, metaType.getValue());
6835  preparedStatement.setShort(8, dirFlag.getValue());
6836  final short metaFlags = (short) (TSK_FS_META_FLAG_ENUM.ALLOC.getValue()
6837  | TSK_FS_META_FLAG_ENUM.USED.getValue());
6838  preparedStatement.setShort(9, metaFlags);
6839  preparedStatement.setLong(10, 0);
6840  preparedStatement.setNull(11, java.sql.Types.BIGINT);
6841  preparedStatement.setNull(12, java.sql.Types.BIGINT);
6842  preparedStatement.setNull(13, java.sql.Types.BIGINT);
6843  preparedStatement.setNull(14, java.sql.Types.BIGINT);
6844  preparedStatement.setNull(15, java.sql.Types.VARCHAR); // MD5
6845  preparedStatement.setNull(16, java.sql.Types.VARCHAR); // SHA-256
6846  preparedStatement.setByte(17, FileKnown.UNKNOWN.getFileKnownValue()); // Known
6847  preparedStatement.setNull(18, java.sql.Types.VARCHAR); // MIME type
6848  String parentPath = "/"; //NON-NLS
6849  preparedStatement.setString(19, parentPath);
6850  preparedStatement.setLong(20, newObjId);
6851  preparedStatement.setString(21, null); //extension, just set it to null
6852  preparedStatement.setString(22, OsAccount.NO_OWNER_ID); // ownerUid
6853  preparedStatement.setNull(23, java.sql.Types.BIGINT); // osAccountObjId
6854  connection.executeUpdate(preparedStatement);
6855 
6856  return new LocalFilesDataSource(this, newObjId, newObjId, deviceId, rootDirectoryName, dirType, metaType, dirFlag, metaFlags, timeZone, null, null, FileKnown.UNKNOWN, parentPath);
6857 
6858  } catch (SQLException ex) {
6859  throw new TskCoreException(String.format("Error creating local files data source with device id %s and directory name %s", deviceId, rootDirectoryName), ex);
6860  } finally {
6861  closeStatement(statement);
6862  }
6863  }
6864