Autopsy  4.8.0
Graphical digital forensics platform for The Sleuth Kit and other tools.
AbstractSqlEamDb.java
Go to the documentation of this file.
1 /*
2  * Central Repository
3  *
4  * Copyright 2015-2018 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.autopsy.centralrepository.datamodel;
20 
21 import com.google.common.cache.Cache;
22 import com.google.common.cache.CacheBuilder;
23 import com.google.common.cache.CacheLoader;
24 import java.net.UnknownHostException;
25 import java.util.ArrayList;
26 import java.util.List;
27 import java.util.Collection;
28 import java.util.LinkedHashSet;
29 import java.util.stream.Collectors;
30 import java.sql.Connection;
31 import java.sql.PreparedStatement;
32 import java.sql.ResultSet;
33 import java.sql.SQLException;
34 import java.sql.Statement;
35 import java.sql.Types;
36 import java.time.LocalDate;
37 import java.util.HashMap;
38 import java.util.Map;
39 import java.util.Set;
40 import java.util.concurrent.ExecutionException;
41 import java.util.concurrent.TimeUnit;
42 import java.util.logging.Level;
43 import org.sleuthkit.autopsy.casemodule.Case;
44 import static org.sleuthkit.autopsy.centralrepository.datamodel.EamDbUtil.updateSchemaVersion;
45 import org.sleuthkit.autopsy.coreutils.Logger;
46 import org.sleuthkit.autopsy.healthmonitor.HealthMonitor;
47 import org.sleuthkit.autopsy.healthmonitor.TimingMetric;
48 import org.sleuthkit.datamodel.CaseDbSchemaVersionNumber;
49 import org.sleuthkit.datamodel.TskData;
50 
56 abstract class AbstractSqlEamDb implements EamDb {
57 
58  private final static Logger logger = Logger.getLogger(AbstractSqlEamDb.class.getName());
59 
60  protected final List<CorrelationAttributeInstance.Type> defaultCorrelationTypes;
61 
62  private int bulkArtifactsCount;
63  protected int bulkArtifactsThreshold;
64  private final Map<String, Collection<CorrelationAttributeInstance>> bulkArtifacts;
65  private static final int CASE_CACHE_TIMEOUT = 5;
66  private static final int DATA_SOURCE_CACHE_TIMEOUT = 5;
67  private static final Cache<Integer, CorrelationAttributeInstance.Type> typeCache = CacheBuilder.newBuilder().build();
68  private static final Cache<String, CorrelationCase> caseCacheByUUID = CacheBuilder.newBuilder()
69  .expireAfterWrite(CASE_CACHE_TIMEOUT, TimeUnit.MINUTES).
70  build();
71  private static final Cache<Integer, CorrelationCase> caseCacheById = CacheBuilder.newBuilder()
72  .expireAfterWrite(CASE_CACHE_TIMEOUT, TimeUnit.MINUTES).
73  build();
74  private static final Cache<String, CorrelationDataSource> dataSourceCacheByDeviceId = CacheBuilder.newBuilder()
75  .expireAfterWrite(DATA_SOURCE_CACHE_TIMEOUT, TimeUnit.MINUTES).
76  build();
77  private static final Cache<String, CorrelationDataSource> dataSourceCacheById = CacheBuilder.newBuilder()
78  .expireAfterWrite(DATA_SOURCE_CACHE_TIMEOUT, TimeUnit.MINUTES).
79  build();
80  // Maximum length for the value column in the instance tables
81  static final int MAX_VALUE_LENGTH = 256;
82 
83  // number of instances to keep in bulk queue before doing an insert.
84  // Update Test code if this changes. It's hard coded there.
85  static final int DEFAULT_BULK_THRESHHOLD = 1000;
86 
92  protected AbstractSqlEamDb() throws EamDbException {
93  bulkArtifactsCount = 0;
94  bulkArtifacts = new HashMap<>();
95 
96  defaultCorrelationTypes = CorrelationAttributeInstance.getDefaultCorrelationTypes();
97  defaultCorrelationTypes.forEach((type) -> {
98  bulkArtifacts.put(type.getDbTableName(), new ArrayList<>());
99  });
100  }
101 
105  protected abstract Connection connect() throws EamDbException;
106 
115  @Override
116  public void newDbInfo(String name, String value) throws EamDbException {
117  Connection conn = connect();
118 
119  PreparedStatement preparedStatement = null;
120  String sql = "INSERT INTO db_info (name, value) VALUES (?, ?) "
121  + getConflictClause();
122  try {
123  preparedStatement = conn.prepareStatement(sql);
124  preparedStatement.setString(1, name);
125  preparedStatement.setString(2, value);
126  preparedStatement.executeUpdate();
127  } catch (SQLException ex) {
128  throw new EamDbException("Error adding new name/value pair to db_info.", ex);
129  } finally {
130  EamDbUtil.closeStatement(preparedStatement);
131  EamDbUtil.closeConnection(conn);
132  }
133 
134  }
135 
145  @Override
146  public String getDbInfo(String name) throws EamDbException {
147  Connection conn = connect();
148 
149  PreparedStatement preparedStatement = null;
150  ResultSet resultSet = null;
151  String value = null;
152  String sql = "SELECT value FROM db_info WHERE name=?";
153  try {
154  preparedStatement = conn.prepareStatement(sql);
155  preparedStatement.setString(1, name);
156  resultSet = preparedStatement.executeQuery();
157  if (resultSet.next()) {
158  value = resultSet.getString("value");
159  }
160  } catch (SQLException ex) {
161  throw new EamDbException("Error getting value for name.", ex);
162  } finally {
163  EamDbUtil.closeStatement(preparedStatement);
164  EamDbUtil.closeResultSet(resultSet);
165  EamDbUtil.closeConnection(conn);
166  }
167 
168  return value;
169  }
170 
174  protected final void clearCaches() {
175  typeCache.invalidateAll();
176  caseCacheByUUID.invalidateAll();
177  caseCacheById.invalidateAll();
178  dataSourceCacheByDeviceId.invalidateAll();
179  dataSourceCacheById.invalidateAll();
180  }
181 
190  @Override
191  public void updateDbInfo(String name, String value) throws EamDbException {
192  Connection conn = connect();
193 
194  PreparedStatement preparedStatement = null;
195  String sql = "UPDATE db_info SET value=? WHERE name=?";
196  try {
197  preparedStatement = conn.prepareStatement(sql);
198  preparedStatement.setString(1, value);
199  preparedStatement.setString(2, name);
200  preparedStatement.executeUpdate();
201  } catch (SQLException ex) {
202  throw new EamDbException("Error updating value for name.", ex);
203  } finally {
204  EamDbUtil.closeStatement(preparedStatement);
205  EamDbUtil.closeConnection(conn);
206  }
207  }
208 
218  @Override
219  public synchronized CorrelationCase newCase(CorrelationCase eamCase) throws EamDbException {
220 
221  // check if there is already an existing CorrelationCase for this Case
222  CorrelationCase cRCase = getCaseByUUID(eamCase.getCaseUUID());
223  if (cRCase != null) {
224  return cRCase;
225  }
226 
227  Connection conn = connect();
228  PreparedStatement preparedStatement = null;
229 
230  String sql = "INSERT INTO cases(case_uid, org_id, case_name, creation_date, case_number, "
231  + "examiner_name, examiner_email, examiner_phone, notes) "
232  + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) "
233  + getConflictClause();
234  ResultSet resultSet = null;
235  try {
236  preparedStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
237 
238  preparedStatement.setString(1, eamCase.getCaseUUID());
239  if (null == eamCase.getOrg()) {
240  preparedStatement.setNull(2, Types.INTEGER);
241  } else {
242  preparedStatement.setInt(2, eamCase.getOrg().getOrgID());
243  }
244  preparedStatement.setString(3, eamCase.getDisplayName());
245  preparedStatement.setString(4, eamCase.getCreationDate());
246  if ("".equals(eamCase.getCaseNumber())) {
247  preparedStatement.setNull(5, Types.INTEGER);
248  } else {
249  preparedStatement.setString(5, eamCase.getCaseNumber());
250  }
251  if ("".equals(eamCase.getExaminerName())) {
252  preparedStatement.setNull(6, Types.INTEGER);
253  } else {
254  preparedStatement.setString(6, eamCase.getExaminerName());
255  }
256  if ("".equals(eamCase.getExaminerEmail())) {
257  preparedStatement.setNull(7, Types.INTEGER);
258  } else {
259  preparedStatement.setString(7, eamCase.getExaminerEmail());
260  }
261  if ("".equals(eamCase.getExaminerPhone())) {
262  preparedStatement.setNull(8, Types.INTEGER);
263  } else {
264  preparedStatement.setString(8, eamCase.getExaminerPhone());
265  }
266  if ("".equals(eamCase.getNotes())) {
267  preparedStatement.setNull(9, Types.INTEGER);
268  } else {
269  preparedStatement.setString(9, eamCase.getNotes());
270  }
271 
272  preparedStatement.executeUpdate();
273  //update the case in the caches
274  resultSet = preparedStatement.getGeneratedKeys();
275  if (!resultSet.next()) {
276  throw new EamDbException(String.format("Failed to INSERT case %s in central repo", eamCase.getCaseUUID()));
277  }
278  int caseID = resultSet.getInt(1); //last_insert_rowid()
279  CorrelationCase correlationCase = new CorrelationCase(caseID, eamCase.getCaseUUID(), eamCase.getOrg(),
280  eamCase.getDisplayName(), eamCase.getCreationDate(), eamCase.getCaseNumber(), eamCase.getExaminerName(),
281  eamCase.getExaminerEmail(), eamCase.getExaminerPhone(), eamCase.getNotes());
282  caseCacheByUUID.put(eamCase.getCaseUUID(), correlationCase);
283  caseCacheById.put(caseID, correlationCase);
284  } catch (SQLException ex) {
285  throw new EamDbException("Error inserting new case.", ex); // NON-NLS
286  } finally {
287  EamDbUtil.closeResultSet(resultSet);
288  EamDbUtil.closeStatement(preparedStatement);
289  EamDbUtil.closeConnection(conn);
290  }
291 
292  // get a new version with the updated ID
293  return getCaseByUUID(eamCase.getCaseUUID());
294  }
295 
301  @Override
302  public CorrelationCase newCase(Case autopsyCase) throws EamDbException {
303  if (autopsyCase == null) {
304  throw new EamDbException("Case is null");
305  }
306 
307  CorrelationCase curCeCase = new CorrelationCase(
308  -1,
309  autopsyCase.getName(), // unique case ID
310  EamOrganization.getDefault(),
311  autopsyCase.getDisplayName(),
312  autopsyCase.getCreatedDate(),
313  autopsyCase.getNumber(),
314  autopsyCase.getExaminer(),
315  autopsyCase.getExaminerEmail(),
316  autopsyCase.getExaminerPhone(),
317  autopsyCase.getCaseNotes());
318  return newCase(curCeCase);
319  }
320 
321  @Override
322  public CorrelationCase getCase(Case autopsyCase) throws EamDbException {
323  return getCaseByUUID(autopsyCase.getName());
324  }
325 
331  @Override
332  public void updateCase(CorrelationCase eamCase) throws EamDbException {
333  if (eamCase == null) {
334  throw new EamDbException("Correlation case is null");
335  }
336 
337  Connection conn = connect();
338 
339  PreparedStatement preparedStatement = null;
340  String sql = "UPDATE cases "
341  + "SET org_id=?, case_name=?, creation_date=?, case_number=?, examiner_name=?, examiner_email=?, examiner_phone=?, notes=? "
342  + "WHERE case_uid=?";
343 
344  try {
345  preparedStatement = conn.prepareStatement(sql);
346 
347  if (null == eamCase.getOrg()) {
348  preparedStatement.setNull(1, Types.INTEGER);
349  } else {
350  preparedStatement.setInt(1, eamCase.getOrg().getOrgID());
351  }
352  preparedStatement.setString(2, eamCase.getDisplayName());
353  preparedStatement.setString(3, eamCase.getCreationDate());
354 
355  if ("".equals(eamCase.getCaseNumber())) {
356  preparedStatement.setNull(4, Types.INTEGER);
357  } else {
358  preparedStatement.setString(4, eamCase.getCaseNumber());
359  }
360  if ("".equals(eamCase.getExaminerName())) {
361  preparedStatement.setNull(5, Types.INTEGER);
362  } else {
363  preparedStatement.setString(5, eamCase.getExaminerName());
364  }
365  if ("".equals(eamCase.getExaminerEmail())) {
366  preparedStatement.setNull(6, Types.INTEGER);
367  } else {
368  preparedStatement.setString(6, eamCase.getExaminerEmail());
369  }
370  if ("".equals(eamCase.getExaminerPhone())) {
371  preparedStatement.setNull(7, Types.INTEGER);
372  } else {
373  preparedStatement.setString(7, eamCase.getExaminerPhone());
374  }
375  if ("".equals(eamCase.getNotes())) {
376  preparedStatement.setNull(8, Types.INTEGER);
377  } else {
378  preparedStatement.setString(8, eamCase.getNotes());
379  }
380 
381  preparedStatement.setString(9, eamCase.getCaseUUID());
382 
383  preparedStatement.executeUpdate();
384  //update the case in the cache
385  caseCacheById.put(eamCase.getID(), eamCase);
386  caseCacheByUUID.put(eamCase.getCaseUUID(), eamCase);
387  } catch (SQLException ex) {
388  throw new EamDbException("Error updating case.", ex); // NON-NLS
389  } finally {
390  EamDbUtil.closeStatement(preparedStatement);
391  EamDbUtil.closeConnection(conn);
392  }
393  }
394 
402  @Override
403  public CorrelationCase getCaseByUUID(String caseUUID) throws EamDbException {
404  try {
405  return caseCacheByUUID.get(caseUUID, () -> getCaseByUUIDFromCr(caseUUID));
406  } catch (CacheLoader.InvalidCacheLoadException ignored) {
407  //lambda valueloader returned a null value and cache can not store null values this is normal if the case does not exist in the central repo yet
408  return null;
409  } catch (ExecutionException ex) {
410  throw new EamDbException("Error getting autopsy case from Central repo", ex);
411  }
412  }
413 
421  private CorrelationCase getCaseByUUIDFromCr(String caseUUID) throws EamDbException {
422  Connection conn = connect();
423 
424  CorrelationCase eamCaseResult = null;
425  PreparedStatement preparedStatement = null;
426  ResultSet resultSet = null;
427 
428  String sql = "SELECT cases.id as case_id, case_uid, case_name, creation_date, case_number, examiner_name, "
429  + "examiner_email, examiner_phone, notes, organizations.id as org_id, org_name, poc_name, poc_email, poc_phone "
430  + "FROM cases "
431  + "LEFT JOIN organizations ON cases.org_id=organizations.id "
432  + "WHERE case_uid=?";
433 
434  try {
435  preparedStatement = conn.prepareStatement(sql);
436  preparedStatement.setString(1, caseUUID);
437  resultSet = preparedStatement.executeQuery();
438  if (resultSet.next()) {
439  eamCaseResult = getEamCaseFromResultSet(resultSet);
440  }
441  if (eamCaseResult != null) {
442  //Update the version in the other cache
443  caseCacheById.put(eamCaseResult.getID(), eamCaseResult);
444  }
445  } catch (SQLException ex) {
446  throw new EamDbException("Error getting case details.", ex); // NON-NLS
447  } finally {
448  EamDbUtil.closeStatement(preparedStatement);
449  EamDbUtil.closeResultSet(resultSet);
450  EamDbUtil.closeConnection(conn);
451  }
452 
453  return eamCaseResult;
454  }
455 
463  @Override
464  public CorrelationCase getCaseById(int caseId) throws EamDbException {
465  try {
466  return caseCacheById.get(caseId, () -> getCaseByIdFromCr(caseId));
467  } catch (CacheLoader.InvalidCacheLoadException ignored) {
468  //lambda valueloader returned a null value and cache can not store null values this is normal if the case does not exist in the central repo yet
469  return null;
470  } catch (ExecutionException ex) {
471  throw new EamDbException("Error getting autopsy case from Central repo", ex);
472  }
473  }
474 
482  private CorrelationCase getCaseByIdFromCr(int caseId) throws EamDbException {
483  Connection conn = connect();
484 
485  CorrelationCase eamCaseResult = null;
486  PreparedStatement preparedStatement = null;
487  ResultSet resultSet = null;
488 
489  String sql = "SELECT cases.id as case_id, case_uid, case_name, creation_date, case_number, examiner_name, "
490  + "examiner_email, examiner_phone, notes, organizations.id as org_id, org_name, poc_name, poc_email, poc_phone "
491  + "FROM cases "
492  + "LEFT JOIN organizations ON cases.org_id=organizations.id "
493  + "WHERE cases.id=?";
494  try {
495  preparedStatement = conn.prepareStatement(sql);
496  preparedStatement.setInt(1, caseId);
497  resultSet = preparedStatement.executeQuery();
498  if (resultSet.next()) {
499  eamCaseResult = getEamCaseFromResultSet(resultSet);
500  }
501  if (eamCaseResult != null) {
502  //Update the version in the other cache
503  caseCacheByUUID.put(eamCaseResult.getCaseUUID(), eamCaseResult);
504  }
505  } catch (SQLException ex) {
506  throw new EamDbException("Error getting case details.", ex); // NON-NLS
507  } finally {
508  EamDbUtil.closeStatement(preparedStatement);
509  EamDbUtil.closeResultSet(resultSet);
510  EamDbUtil.closeConnection(conn);
511  }
512 
513  return eamCaseResult;
514  }
515 
521  @Override
522  public List<CorrelationCase> getCases() throws EamDbException {
523  Connection conn = connect();
524 
525  List<CorrelationCase> cases = new ArrayList<>();
526  CorrelationCase eamCaseResult;
527  PreparedStatement preparedStatement = null;
528  ResultSet resultSet = null;
529 
530  String sql = "SELECT cases.id as case_id, case_uid, case_name, creation_date, case_number, examiner_name, "
531  + "examiner_email, examiner_phone, notes, organizations.id as org_id, org_name, poc_name, poc_email, poc_phone "
532  + "FROM cases "
533  + "LEFT JOIN organizations ON cases.org_id=organizations.id";
534 
535  try {
536  preparedStatement = conn.prepareStatement(sql);
537  resultSet = preparedStatement.executeQuery();
538  while (resultSet.next()) {
539  eamCaseResult = getEamCaseFromResultSet(resultSet);
540  cases.add(eamCaseResult);
541  }
542  } catch (SQLException ex) {
543  throw new EamDbException("Error getting all cases.", ex); // NON-NLS
544  } finally {
545  EamDbUtil.closeStatement(preparedStatement);
546  EamDbUtil.closeResultSet(resultSet);
547  EamDbUtil.closeConnection(conn);
548  }
549 
550  return cases;
551  }
552 
562  private static String getDataSourceByDeviceIdCacheKey(int caseId, String dataSourceDeviceId) {
563  return "Case" + caseId + "DeviceId" + dataSourceDeviceId; //NON-NLS
564  }
565 
575  private static String getDataSourceByIdCacheKey(int caseId, int dataSourceId) {
576  return "Case" + caseId + "Id" + dataSourceId; //NON-NLS
577  }
578 
584  @Override
585  public void newDataSource(CorrelationDataSource eamDataSource) throws EamDbException {
586  if (eamDataSource.getCaseID() == -1) {
587  throw new EamDbException("Case ID is -1");
588  } else if (eamDataSource.getID() != -1) {
589  // This data source is already in the central repo
590  return;
591  }
592  Connection conn = connect();
593 
594  PreparedStatement preparedStatement = null;
595 
596  String sql = "INSERT INTO data_sources(device_id, case_id, name) VALUES (?, ?, ?) "
597  + getConflictClause();
598  ResultSet resultSet = null;
599  try {
600  preparedStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
601 
602  preparedStatement.setString(1, eamDataSource.getDeviceID());
603  preparedStatement.setInt(2, eamDataSource.getCaseID());
604  preparedStatement.setString(3, eamDataSource.getName());
605 
606  preparedStatement.executeUpdate();
607  resultSet = preparedStatement.getGeneratedKeys();
608  if (!resultSet.next()) {
609  throw new EamDbException(String.format("Failed to INSERT data source %s in central repo", eamDataSource.getName()));
610  }
611  int dataSourceId = resultSet.getInt(1); //last_insert_rowid()
612  CorrelationDataSource dataSource = new CorrelationDataSource(eamDataSource.getCaseID(), dataSourceId, eamDataSource.getDeviceID(), eamDataSource.getName());
613  dataSourceCacheByDeviceId.put(getDataSourceByDeviceIdCacheKey(dataSource.getCaseID(), dataSource.getDeviceID()), dataSource);
614  dataSourceCacheById.put(getDataSourceByIdCacheKey(dataSource.getCaseID(), dataSource.getID()), dataSource);
615  } catch (SQLException ex) {
616  throw new EamDbException("Error inserting new data source.", ex); // NON-NLS
617  } finally {
618  EamDbUtil.closeResultSet(resultSet);
619  EamDbUtil.closeStatement(preparedStatement);
620  EamDbUtil.closeConnection(conn);
621  }
622  }
623 
635  @Override
636  public CorrelationDataSource getDataSource(CorrelationCase correlationCase, String dataSourceDeviceId) throws EamDbException {
637 
638  if (correlationCase == null) {
639  throw new EamDbException("Correlation case is null");
640  }
641  try {
642  return dataSourceCacheByDeviceId.get(getDataSourceByDeviceIdCacheKey(correlationCase.getID(), dataSourceDeviceId), () -> getDataSourceFromCr(correlationCase, dataSourceDeviceId));
643  } catch (CacheLoader.InvalidCacheLoadException ignored) {
644  //lambda valueloader returned a null value and cache can not store null values this is normal if the dataSource does not exist in the central repo yet
645  return null;
646  } catch (ExecutionException ex) {
647  throw new EamDbException("Error getting data source from central repository", ex);
648  }
649  }
650 
663  private CorrelationDataSource getDataSourceFromCr(CorrelationCase correlationCase, String dataSourceDeviceId) throws EamDbException {
664  Connection conn = connect();
665 
666  CorrelationDataSource eamDataSourceResult = null;
667  PreparedStatement preparedStatement = null;
668  ResultSet resultSet = null;
669 
670  String sql = "SELECT * FROM data_sources WHERE device_id=? AND case_id=?"; // NON-NLS
671 
672  try {
673  preparedStatement = conn.prepareStatement(sql);
674  preparedStatement.setString(1, dataSourceDeviceId);
675  preparedStatement.setInt(2, correlationCase.getID());
676  resultSet = preparedStatement.executeQuery();
677  if (resultSet.next()) {
678  eamDataSourceResult = getEamDataSourceFromResultSet(resultSet);
679  }
680  if (eamDataSourceResult != null) {
681  dataSourceCacheById.put(getDataSourceByIdCacheKey(correlationCase.getID(), eamDataSourceResult.getID()), eamDataSourceResult);
682  }
683  } catch (SQLException ex) {
684  throw new EamDbException("Error getting data source.", ex); // NON-NLS
685  } finally {
686  EamDbUtil.closeStatement(preparedStatement);
687  EamDbUtil.closeResultSet(resultSet);
688  EamDbUtil.closeConnection(conn);
689  }
690 
691  return eamDataSourceResult;
692  }
693 
703  @Override
704  public CorrelationDataSource getDataSourceById(CorrelationCase correlationCase, int dataSourceId) throws EamDbException {
705  if (correlationCase == null) {
706  throw new EamDbException("Correlation case is null");
707  }
708  try {
709  return dataSourceCacheById.get(getDataSourceByIdCacheKey(correlationCase.getID(), dataSourceId), () -> getDataSourceByIdFromCr(correlationCase, dataSourceId));
710  } catch (CacheLoader.InvalidCacheLoadException ignored) {
711  //lambda valueloader returned a null value and cache can not store null values this is normal if the dataSource does not exist in the central repo yet
712  return null;
713  } catch (ExecutionException ex) {
714  throw new EamDbException("Error getting data source from central repository", ex);
715  }
716  }
717 
727  private CorrelationDataSource getDataSourceByIdFromCr(CorrelationCase correlationCase, int dataSourceId) throws EamDbException {
728  Connection conn = connect();
729 
730  CorrelationDataSource eamDataSourceResult = null;
731  PreparedStatement preparedStatement = null;
732  ResultSet resultSet = null;
733 
734  String sql = "SELECT * FROM data_sources WHERE id=? AND case_id=?"; // NON-NLS
735 
736  try {
737  preparedStatement = conn.prepareStatement(sql);
738  preparedStatement.setInt(1, dataSourceId);
739  preparedStatement.setInt(2, correlationCase.getID());
740  resultSet = preparedStatement.executeQuery();
741  if (resultSet.next()) {
742  eamDataSourceResult = getEamDataSourceFromResultSet(resultSet);
743  }
744  if (eamDataSourceResult != null) {
745  dataSourceCacheByDeviceId.put(getDataSourceByDeviceIdCacheKey(correlationCase.getID(), eamDataSourceResult.getDeviceID()), eamDataSourceResult);
746  }
747  } catch (SQLException ex) {
748  throw new EamDbException("Error getting data source.", ex); // NON-NLS
749  } finally {
750  EamDbUtil.closeStatement(preparedStatement);
751  EamDbUtil.closeResultSet(resultSet);
752  EamDbUtil.closeConnection(conn);
753  }
754 
755  return eamDataSourceResult;
756  }
757 
763  @Override
764  public List<CorrelationDataSource> getDataSources() throws EamDbException {
765  Connection conn = connect();
766 
767  List<CorrelationDataSource> dataSources = new ArrayList<>();
768  CorrelationDataSource eamDataSourceResult;
769  PreparedStatement preparedStatement = null;
770  ResultSet resultSet = null;
771 
772  String sql = "SELECT * FROM data_sources";
773 
774  try {
775  preparedStatement = conn.prepareStatement(sql);
776  resultSet = preparedStatement.executeQuery();
777  while (resultSet.next()) {
778  eamDataSourceResult = getEamDataSourceFromResultSet(resultSet);
779  dataSources.add(eamDataSourceResult);
780  }
781  } catch (SQLException ex) {
782  throw new EamDbException("Error getting all data sources.", ex); // NON-NLS
783  } finally {
784  EamDbUtil.closeStatement(preparedStatement);
785  EamDbUtil.closeResultSet(resultSet);
786  EamDbUtil.closeConnection(conn);
787  }
788 
789  return dataSources;
790  }
791 
798  @Override
799  public void addArtifactInstance(CorrelationAttributeInstance eamArtifact) throws EamDbException {
800  checkAddArtifactInstanceNulls(eamArtifact);
801 
802  Connection conn = connect();
803 
804  PreparedStatement preparedStatement = null;
805 
806  // @@@ We should cache the case and data source IDs in memory
807  String tableName = EamDbUtil.correlationTypeToInstanceTableName(eamArtifact.getCorrelationType());
808  String sql
809  = "INSERT INTO "
810  + tableName
811  + "(case_id, data_source_id, value, file_path, known_status, comment) "
812  + "VALUES ((SELECT id FROM cases WHERE case_uid=? LIMIT 1), "
813  + "(SELECT id FROM data_sources WHERE device_id=? AND case_id=? LIMIT 1), ?, ?, ?, ?) "
814  + getConflictClause();
815 
816  try {
817  preparedStatement = conn.prepareStatement(sql);
818 
819  if (!eamArtifact.getCorrelationValue().isEmpty()) {
820 
821  preparedStatement.setString(1, eamArtifact.getCorrelationCase().getCaseUUID());
822  preparedStatement.setString(2, eamArtifact.getCorrelationDataSource().getDeviceID());
823  preparedStatement.setInt(3, eamArtifact.getCorrelationDataSource().getCaseID());
824  preparedStatement.setString(4, eamArtifact.getCorrelationValue());
825  preparedStatement.setString(5, eamArtifact.getFilePath().toLowerCase());
826  preparedStatement.setByte(6, eamArtifact.getKnownStatus().getFileKnownValue());
827  if ("".equals(eamArtifact.getComment())) {
828  preparedStatement.setNull(7, Types.INTEGER);
829  } else {
830  preparedStatement.setString(7, eamArtifact.getComment());
831  }
832 
833  preparedStatement.executeUpdate();
834  }
835 
836  } catch (SQLException ex) {
837  throw new EamDbException("Error inserting new artifact into artifacts table.", ex); // NON-NLS
838  } finally {
839  EamDbUtil.closeStatement(preparedStatement);
840  EamDbUtil.closeConnection(conn);
841  }
842  }
843 
844  private void checkAddArtifactInstanceNulls(CorrelationAttributeInstance eamArtifact) throws EamDbException {
845  if (eamArtifact == null) {
846  throw new EamDbException("CorrelationAttribute is null");
847  }
848  if (eamArtifact.getCorrelationType() == null) {
849  throw new EamDbException("Correlation type is null");
850  }
851  if (eamArtifact.getCorrelationValue() == null) {
852  throw new EamDbException("Correlation value is null");
853  }
854  if (eamArtifact.getCorrelationValue().length() >= MAX_VALUE_LENGTH) {
855  throw new EamDbException("Artifact value too long for central repository."
856  + "\nCorrelationArtifact ID: " + eamArtifact.getID()
857  + "\nCorrelationArtifact Type: " + eamArtifact.getCorrelationType().getDisplayName()
858  + "\nCorrelationArtifact Value: " + eamArtifact.getCorrelationValue());
859 
860  }
861  if (eamArtifact.getCorrelationCase() == null) {
862  throw new EamDbException("CorrelationAttributeInstance case is null");
863  }
864  if (eamArtifact.getCorrelationDataSource() == null) {
865  throw new EamDbException("CorrelationAttributeInstance data source is null");
866  }
867  if (eamArtifact.getKnownStatus() == null) {
868  throw new EamDbException("CorrelationAttributeInstance known status is null");
869  }
870  }
871 
883  @Override
884  public List<CorrelationAttributeInstance> getArtifactInstancesByTypeValue(CorrelationAttributeInstance.Type aType, String value) throws EamDbException, CorrelationAttributeNormalizationException {
885 
886  String normalizedValue = CorrelationAttributeNormalizer.normalize(aType, value);
887 
888  Connection conn = connect();
889 
890  List<CorrelationAttributeInstance> artifactInstances = new ArrayList<>();
891 
892  CorrelationAttributeInstance artifactInstance;
893  PreparedStatement preparedStatement = null;
894  ResultSet resultSet = null;
895 
896  String tableName = EamDbUtil.correlationTypeToInstanceTableName(aType);
897  String sql
898  = "SELECT "
899  + tableName
900  + ".id,"
901  + tableName
902  + ".value,"
903  + " cases.case_name, cases.case_uid, data_sources.id AS data_source_id, data_sources.name, device_id, file_path, known_status, comment, data_sources.case_id FROM "
904  + tableName
905  + " LEFT JOIN cases ON "
906  + tableName
907  + ".case_id=cases.id"
908  + " LEFT JOIN data_sources ON "
909  + tableName
910  + ".data_source_id=data_sources.id"
911  + " WHERE value=?";
912 
913  try {
914  preparedStatement = conn.prepareStatement(sql);
915  preparedStatement.setString(1, normalizedValue);
916  resultSet = preparedStatement.executeQuery();
917  while (resultSet.next()) {
918  artifactInstance = getEamArtifactInstanceFromResultSet(resultSet, aType);
919  artifactInstances.add(artifactInstance);
920  }
921  } catch (SQLException ex) {
922  throw new EamDbException("Error getting artifact instances by artifactType and artifactValue.", ex); // NON-NLS
923  } finally {
924  EamDbUtil.closeStatement(preparedStatement);
925  EamDbUtil.closeResultSet(resultSet);
926  EamDbUtil.closeConnection(conn);
927  }
928 
929  return artifactInstances;
930  }
931 
943  @Override
944  public List<CorrelationAttributeInstance> getArtifactInstancesByPath(CorrelationAttributeInstance.Type aType, String filePath) throws EamDbException {
945  if (aType == null) {
946  throw new EamDbException("Correlation type is null");
947  }
948  if (filePath == null) {
949  throw new EamDbException("Correlation value is null");
950  }
951  Connection conn = connect();
952 
953  List<CorrelationAttributeInstance> artifactInstances = new ArrayList<>();
954 
955  CorrelationAttributeInstance artifactInstance;
956  PreparedStatement preparedStatement = null;
957  ResultSet resultSet = null;
958 
959  String tableName = EamDbUtil.correlationTypeToInstanceTableName(aType);
960  String sql
961  = "SELECT "
962  + tableName
963  + ".id, "
964  + tableName
965  + ".value,"
966  + " cases.case_name, cases.case_uid, data_sources.id AS data_source_id, data_sources.name, device_id, file_path, known_status, comment, data_sources.case_id FROM "
967  + tableName
968  + " LEFT JOIN cases ON "
969  + tableName
970  + ".case_id=cases.id"
971  + " LEFT JOIN data_sources ON "
972  + tableName
973  + ".data_source_id=data_sources.id"
974  + " WHERE file_path=?";
975 
976  try {
977  preparedStatement = conn.prepareStatement(sql);
978  preparedStatement.setString(1, filePath.toLowerCase());
979  resultSet = preparedStatement.executeQuery();
980  while (resultSet.next()) {
981  try {
982  artifactInstance = getEamArtifactInstanceFromResultSet(resultSet, aType);
983  artifactInstances.add(artifactInstance);
984  } catch (CorrelationAttributeNormalizationException ex) {
985  logger.log(Level.INFO, "Unable to get artifact instance from resultset.", ex);
986  }
987  }
988  } catch (SQLException ex) {
989  throw new EamDbException("Error getting artifact instances by artifactType and artifactValue.", ex); // NON-NLS
990  } finally {
991  EamDbUtil.closeStatement(preparedStatement);
992  EamDbUtil.closeResultSet(resultSet);
993  EamDbUtil.closeConnection(conn);
994  }
995 
996  return artifactInstances;
997  }
998 
1009  @Override
1010  public Long getCountArtifactInstancesByTypeValue(CorrelationAttributeInstance.Type aType, String value) throws EamDbException, CorrelationAttributeNormalizationException {
1011  String normalizedValue = CorrelationAttributeNormalizer.normalize(aType, value);
1012 
1013  Connection conn = connect();
1014 
1015  Long instanceCount = 0L;
1016  PreparedStatement preparedStatement = null;
1017  ResultSet resultSet = null;
1018 
1019  String tableName = EamDbUtil.correlationTypeToInstanceTableName(aType);
1020  String sql
1021  = "SELECT count(*) FROM "
1022  + tableName
1023  + " WHERE value=?";
1024 
1025  try {
1026  preparedStatement = conn.prepareStatement(sql);
1027  preparedStatement.setString(1, normalizedValue);
1028  resultSet = preparedStatement.executeQuery();
1029  resultSet.next();
1030  instanceCount = resultSet.getLong(1);
1031  } catch (SQLException ex) {
1032  throw new EamDbException("Error getting count of artifact instances by artifactType and artifactValue.", ex); // NON-NLS
1033  } finally {
1034  EamDbUtil.closeStatement(preparedStatement);
1035  EamDbUtil.closeResultSet(resultSet);
1036  EamDbUtil.closeConnection(conn);
1037  }
1038 
1039  return instanceCount;
1040  }
1041 
1042  @Override
1043  public int getFrequencyPercentage(CorrelationAttributeInstance corAttr) throws EamDbException, CorrelationAttributeNormalizationException {
1044  if (corAttr == null) {
1045  throw new EamDbException("CorrelationAttribute is null");
1046  }
1047  Double uniqueTypeValueTuples = getCountUniqueCaseDataSourceTuplesHavingTypeValue(corAttr.getCorrelationType(), corAttr.getCorrelationValue()).doubleValue();
1048  Double uniqueCaseDataSourceTuples = getCountUniqueDataSources().doubleValue();
1049  Double commonalityPercentage = uniqueTypeValueTuples / uniqueCaseDataSourceTuples * 100;
1050  return commonalityPercentage.intValue();
1051  }
1052 
1063  @Override
1064  public Long getCountUniqueCaseDataSourceTuplesHavingTypeValue(CorrelationAttributeInstance.Type aType, String value) throws EamDbException, CorrelationAttributeNormalizationException {
1065  String normalizedValue = CorrelationAttributeNormalizer.normalize(aType, value);
1066 
1067  Connection conn = connect();
1068 
1069  Long instanceCount = 0L;
1070  PreparedStatement preparedStatement = null;
1071  ResultSet resultSet = null;
1072 
1073  String tableName = EamDbUtil.correlationTypeToInstanceTableName(aType);
1074  String sql
1075  = "SELECT count(*) FROM (SELECT DISTINCT case_id, data_source_id FROM "
1076  + tableName
1077  + " WHERE value=?) AS "
1078  + tableName
1079  + "_distinct_case_data_source_tuple";
1080 
1081  try {
1082  preparedStatement = conn.prepareStatement(sql);
1083  preparedStatement.setString(1, normalizedValue);
1084  resultSet = preparedStatement.executeQuery();
1085  resultSet.next();
1086  instanceCount = resultSet.getLong(1);
1087  } catch (SQLException ex) {
1088  throw new EamDbException("Error counting unique caseDisplayName/dataSource tuples having artifactType and artifactValue.", ex); // NON-NLS
1089  } finally {
1090  EamDbUtil.closeStatement(preparedStatement);
1091  EamDbUtil.closeResultSet(resultSet);
1092  EamDbUtil.closeConnection(conn);
1093  }
1094 
1095  return instanceCount;
1096  }
1097 
1098  @Override
1099  public Long getCountUniqueDataSources() throws EamDbException {
1100  Connection conn = connect();
1101 
1102  Long instanceCount = 0L;
1103  PreparedStatement preparedStatement = null;
1104  ResultSet resultSet = null;
1105 
1106  String stmt = "SELECT count(*) FROM data_sources";
1107 
1108  try {
1109  preparedStatement = conn.prepareStatement(stmt);
1110  resultSet = preparedStatement.executeQuery();
1111  resultSet.next();
1112  instanceCount = resultSet.getLong(1);
1113  } catch (SQLException ex) {
1114  throw new EamDbException("Error counting data sources.", ex); // NON-NLS
1115  } finally {
1116  EamDbUtil.closeStatement(preparedStatement);
1117  EamDbUtil.closeResultSet(resultSet);
1118  EamDbUtil.closeConnection(conn);
1119  }
1120 
1121  return instanceCount;
1122  }
1123 
1135  @Override
1136  public Long getCountArtifactInstancesByCaseDataSource(String caseUUID, String dataSourceID) throws EamDbException {
1137  Connection conn = connect();
1138 
1139  Long instanceCount = 0L;
1140  List<CorrelationAttributeInstance.Type> artifactTypes = getDefinedCorrelationTypes();
1141  PreparedStatement preparedStatement = null;
1142  ResultSet resultSet = null;
1143 
1144  // Figure out sql variables or subqueries
1145  String sql = "SELECT 0 ";
1146 
1147  for (CorrelationAttributeInstance.Type type : artifactTypes) {
1148  String table_name = EamDbUtil.correlationTypeToInstanceTableName(type);
1149 
1150  sql
1151  += "+ (SELECT count(*) FROM "
1152  + table_name
1153  + " WHERE data_source_id=(SELECT data_sources.id FROM cases INNER JOIN data_sources ON cases.id = data_sources.case_id WHERE case_uid=? and device_id=?))";
1154  }
1155 
1156  try {
1157  preparedStatement = conn.prepareStatement(sql);
1158 
1159  for (int i = 0; i < artifactTypes.size(); ++i) {
1160  preparedStatement.setString(2 * i + 1, caseUUID);
1161  preparedStatement.setString(2 * i + 2, dataSourceID);
1162  }
1163 
1164  resultSet = preparedStatement.executeQuery();
1165  resultSet.next();
1166  instanceCount = resultSet.getLong(1);
1167  } catch (SQLException ex) {
1168  throw new EamDbException("Error counting artifact instances by caseName/dataSource.", ex); // NON-NLS
1169  } finally {
1170  EamDbUtil.closeStatement(preparedStatement);
1171  EamDbUtil.closeResultSet(resultSet);
1172  EamDbUtil.closeConnection(conn);
1173  }
1174 
1175  return instanceCount;
1176  }
1177 
1185  @Override
1186  public void addAttributeInstanceBulk(CorrelationAttributeInstance eamArtifact) throws EamDbException {
1187 
1188  if (eamArtifact.getCorrelationType() == null) {
1189  throw new EamDbException("Correlation type is null");
1190  }
1191 
1192  synchronized (bulkArtifacts) {
1193  bulkArtifacts.get(eamArtifact.getCorrelationType().getDbTableName()).add(eamArtifact);
1194  bulkArtifactsCount++;
1195 
1196  if (bulkArtifactsCount >= bulkArtifactsThreshold) {
1197  commitAttributeInstancesBulk();
1198  }
1199  }
1200  }
1201 
1207  protected abstract String getConflictClause();
1208 
1213  @Override
1214  public void commitAttributeInstancesBulk() throws EamDbException {
1215  List<CorrelationAttributeInstance.Type> artifactTypes = getDefinedCorrelationTypes();
1216 
1217  Connection conn = connect();
1218  PreparedStatement bulkPs = null;
1219 
1220  try {
1221  synchronized (bulkArtifacts) {
1222  if (bulkArtifactsCount == 0) {
1223  return;
1224  }
1225 
1226  for (CorrelationAttributeInstance.Type type : artifactTypes) {
1227 
1228  String tableName = EamDbUtil.correlationTypeToInstanceTableName(type);
1229  String sql
1230  = "INSERT INTO "
1231  + tableName
1232  + " (case_id, data_source_id, value, file_path, known_status, comment) "
1233  + "VALUES ((SELECT id FROM cases WHERE case_uid=? LIMIT 1), "
1234  + "(SELECT id FROM data_sources WHERE device_id=? AND case_id=? LIMIT 1), ?, ?, ?, ?) "
1235  + getConflictClause();
1236 
1237  bulkPs = conn.prepareStatement(sql);
1238 
1239  Collection<CorrelationAttributeInstance> eamArtifacts = bulkArtifacts.get(type.getDbTableName());
1240  for (CorrelationAttributeInstance eamArtifact : eamArtifacts) {
1241 
1242  if (!eamArtifact.getCorrelationValue().isEmpty()) {
1243 
1244  if (eamArtifact.getCorrelationCase() == null) {
1245  throw new EamDbException("CorrelationAttributeInstance case is null for: "
1246  + "\n\tCorrelationArtifact ID: " + eamArtifact.getID()
1247  + "\n\tCorrelationArtifact Type: " + eamArtifact.getCorrelationType().getDisplayName()
1248  + "\n\tCorrelationArtifact Value: " + eamArtifact.getCorrelationValue());
1249  }
1250  if (eamArtifact.getCorrelationDataSource() == null) {
1251  throw new EamDbException("CorrelationAttributeInstance data source is null for: "
1252  + "\n\tCorrelationArtifact ID: " + eamArtifact.getID()
1253  + "\n\tCorrelationArtifact Type: " + eamArtifact.getCorrelationType().getDisplayName()
1254  + "\n\tCorrelationArtifact Value: " + eamArtifact.getCorrelationValue());
1255  }
1256  if (eamArtifact.getKnownStatus() == null) {
1257  throw new EamDbException("CorrelationAttributeInstance known status is null for: "
1258  + "\n\tCorrelationArtifact ID: " + eamArtifact.getID()
1259  + "\n\tCorrelationArtifact Type: " + eamArtifact.getCorrelationType().getDisplayName()
1260  + "\n\tCorrelationArtifact Value: " + eamArtifact.getCorrelationValue()
1261  + "\n\tEam Instance: "
1262  + "\n\t\tCaseId: " + eamArtifact.getCorrelationDataSource().getCaseID()
1263  + "\n\t\tDeviceID: " + eamArtifact.getCorrelationDataSource().getDeviceID());
1264  }
1265 
1266  if (eamArtifact.getCorrelationValue().length() < MAX_VALUE_LENGTH) {
1267  bulkPs.setString(1, eamArtifact.getCorrelationCase().getCaseUUID());
1268  bulkPs.setString(2, eamArtifact.getCorrelationDataSource().getDeviceID());
1269  bulkPs.setInt(3, eamArtifact.getCorrelationDataSource().getCaseID());
1270  bulkPs.setString(4, eamArtifact.getCorrelationValue());
1271  bulkPs.setString(5, eamArtifact.getFilePath());
1272  bulkPs.setByte(6, eamArtifact.getKnownStatus().getFileKnownValue());
1273  if ("".equals(eamArtifact.getComment())) {
1274  bulkPs.setNull(7, Types.INTEGER);
1275  } else {
1276  bulkPs.setString(7, eamArtifact.getComment());
1277  }
1278  bulkPs.addBatch();
1279  } else {
1280  logger.log(Level.WARNING, ("Artifact value too long for central repository."
1281  + "\n\tCorrelationArtifact ID: " + eamArtifact.getID()
1282  + "\n\tCorrelationArtifact Type: " + eamArtifact.getCorrelationType().getDisplayName()
1283  + "\n\tCorrelationArtifact Value: " + eamArtifact.getCorrelationValue())
1284  + "\n\tEam Instance: "
1285  + "\n\t\tCaseId: " + eamArtifact.getCorrelationDataSource().getCaseID()
1286  + "\n\t\tDeviceID: " + eamArtifact.getCorrelationDataSource().getDeviceID()
1287  + "\n\t\tFilePath: " + eamArtifact.getFilePath());
1288  }
1289  }
1290 
1291  }
1292 
1293  bulkPs.executeBatch();
1294  bulkArtifacts.get(type.getDbTableName()).clear();
1295  }
1296 
1297  TimingMetric timingMetric = HealthMonitor.getTimingMetric("Correlation Engine: Bulk insert");
1298  HealthMonitor.submitTimingMetric(timingMetric);
1299 
1300  // Reset state
1301  bulkArtifactsCount = 0;
1302  }
1303  } catch (SQLException ex) {
1304  throw new EamDbException("Error inserting bulk artifacts.", ex); // NON-NLS
1305  } finally {
1306  EamDbUtil.closeStatement(bulkPs);
1307  EamDbUtil.closeConnection(conn);
1308  }
1309  }
1310 
1314  @Override
1315  public void bulkInsertCases(List<CorrelationCase> cases) throws EamDbException {
1316  if (cases == null) {
1317  throw new EamDbException("cases argument is null");
1318  }
1319 
1320  if (cases.isEmpty()) {
1321  return;
1322  }
1323 
1324  Connection conn = connect();
1325 
1326  int counter = 0;
1327  PreparedStatement bulkPs = null;
1328  try {
1329  String sql = "INSERT INTO cases(case_uid, org_id, case_name, creation_date, case_number, "
1330  + "examiner_name, examiner_email, examiner_phone, notes) "
1331  + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) "
1332  + getConflictClause();
1333  bulkPs = conn.prepareStatement(sql);
1334 
1335  for (CorrelationCase eamCase : cases) {
1336  bulkPs.setString(1, eamCase.getCaseUUID());
1337  if (null == eamCase.getOrg()) {
1338  bulkPs.setNull(2, Types.INTEGER);
1339  } else {
1340  bulkPs.setInt(2, eamCase.getOrg().getOrgID());
1341  }
1342  bulkPs.setString(3, eamCase.getDisplayName());
1343  bulkPs.setString(4, eamCase.getCreationDate());
1344 
1345  if ("".equals(eamCase.getCaseNumber())) {
1346  bulkPs.setNull(5, Types.INTEGER);
1347  } else {
1348  bulkPs.setString(5, eamCase.getCaseNumber());
1349  }
1350  if ("".equals(eamCase.getExaminerName())) {
1351  bulkPs.setNull(6, Types.INTEGER);
1352  } else {
1353  bulkPs.setString(6, eamCase.getExaminerName());
1354  }
1355  if ("".equals(eamCase.getExaminerEmail())) {
1356  bulkPs.setNull(7, Types.INTEGER);
1357  } else {
1358  bulkPs.setString(7, eamCase.getExaminerEmail());
1359  }
1360  if ("".equals(eamCase.getExaminerPhone())) {
1361  bulkPs.setNull(8, Types.INTEGER);
1362  } else {
1363  bulkPs.setString(8, eamCase.getExaminerPhone());
1364  }
1365  if ("".equals(eamCase.getNotes())) {
1366  bulkPs.setNull(9, Types.INTEGER);
1367  } else {
1368  bulkPs.setString(9, eamCase.getNotes());
1369  }
1370 
1371  bulkPs.addBatch();
1372 
1373  counter++;
1374 
1375  // limit a batch's max size to bulkArtifactsThreshold
1376  if (counter >= bulkArtifactsThreshold) {
1377  bulkPs.executeBatch();
1378  counter = 0;
1379  }
1380  }
1381  // send the remaining batch records
1382  bulkPs.executeBatch();
1383  } catch (SQLException ex) {
1384  throw new EamDbException("Error inserting bulk cases.", ex); // NON-NLS
1385  } finally {
1386  EamDbUtil.closeStatement(bulkPs);
1387  EamDbUtil.closeConnection(conn);
1388  }
1389  }
1390 
1400  @Override
1401  public void updateAttributeInstanceComment(CorrelationAttributeInstance eamArtifact) throws EamDbException {
1402 
1403  if (eamArtifact == null) {
1404  throw new EamDbException("CorrelationAttributeInstance is null");
1405  }
1406  if (eamArtifact.getCorrelationCase() == null) {
1407  throw new EamDbException("Correlation case is null");
1408  }
1409  if (eamArtifact.getCorrelationDataSource() == null) {
1410  throw new EamDbException("Correlation data source is null");
1411  }
1412 
1413  Connection conn = connect();
1414  PreparedStatement preparedQuery = null;
1415  String tableName = EamDbUtil.correlationTypeToInstanceTableName(eamArtifact.getCorrelationType());
1416 
1417  String sqlUpdate
1418  = "UPDATE "
1419  + tableName
1420  + " SET comment=? "
1421  + "WHERE case_id=(SELECT id FROM cases WHERE case_uid=?) "
1422  + "AND data_source_id=(SELECT id FROM data_sources WHERE device_id=?) "
1423  + "AND value=? "
1424  + "AND file_path=?";
1425 
1426  try {
1427  preparedQuery = conn.prepareStatement(sqlUpdate);
1428  preparedQuery.setString(1, eamArtifact.getComment());
1429  preparedQuery.setString(2, eamArtifact.getCorrelationCase().getCaseUUID());
1430  preparedQuery.setString(3, eamArtifact.getCorrelationDataSource().getDeviceID());
1431  preparedQuery.setString(4, eamArtifact.getCorrelationValue());
1432  preparedQuery.setString(5, eamArtifact.getFilePath().toLowerCase());
1433  preparedQuery.executeUpdate();
1434  } catch (SQLException ex) {
1435  throw new EamDbException("Error getting/setting artifact instance comment=" + eamArtifact.getComment(), ex); // NON-NLS
1436  } finally {
1437  EamDbUtil.closeStatement(preparedQuery);
1438  EamDbUtil.closeConnection(conn);
1439  }
1440  }
1441 
1456  @Override
1457  public CorrelationAttributeInstance getCorrelationAttributeInstance(CorrelationAttributeInstance.Type type, CorrelationCase correlationCase,
1458  CorrelationDataSource correlationDataSource, String value, String filePath) throws EamDbException, CorrelationAttributeNormalizationException {
1459 
1460  if (correlationCase == null) {
1461  throw new EamDbException("Correlation case is null");
1462  }
1463  if (correlationDataSource == null) {
1464  throw new EamDbException("Correlation data source is null");
1465  }
1466  if (filePath == null) {
1467  throw new EamDbException("Correlation file path is null");
1468  }
1469 
1470  Connection conn = connect();
1471 
1472  PreparedStatement preparedStatement = null;
1473  ResultSet resultSet = null;
1474  CorrelationAttributeInstance correlationAttributeInstance = null;
1475 
1476  try {
1477  String normalizedValue = CorrelationAttributeNormalizer.normalize(type, value);
1478 
1479  String tableName = EamDbUtil.correlationTypeToInstanceTableName(type);
1480  String sql
1481  = "SELECT id, known_status, comment FROM "
1482  + tableName
1483  + " WHERE case_id=?"
1484  + " AND data_source_id=?"
1485  + " AND value=?"
1486  + " AND file_path=?";
1487 
1488  preparedStatement = conn.prepareStatement(sql);
1489  preparedStatement.setInt(1, correlationCase.getID());
1490  preparedStatement.setInt(2, correlationDataSource.getID());
1491  preparedStatement.setString(3, normalizedValue);
1492  preparedStatement.setString(4, filePath.toLowerCase());
1493  resultSet = preparedStatement.executeQuery();
1494  if (resultSet.next()) {
1495  int instanceId = resultSet.getInt(1);
1496  int knownStatus = resultSet.getInt(2);
1497  String comment = resultSet.getString(3);
1498 
1499  correlationAttributeInstance = new CorrelationAttributeInstance(type, value,
1500  instanceId, correlationCase, correlationDataSource, filePath, comment, TskData.FileKnown.valueOf((byte) knownStatus));
1501  }
1502  } catch (SQLException ex) {
1503  throw new EamDbException("Error getting notable artifact instances.", ex); // NON-NLS
1504  } finally {
1505  EamDbUtil.closeStatement(preparedStatement);
1506  EamDbUtil.closeResultSet(resultSet);
1507  EamDbUtil.closeConnection(conn);
1508  }
1509 
1510  return correlationAttributeInstance;
1511  }
1512 
1523  @Override
1524  public void setAttributeInstanceKnownStatus(CorrelationAttributeInstance eamArtifact, TskData.FileKnown knownStatus) throws EamDbException {
1525  if (eamArtifact == null) {
1526  throw new EamDbException("CorrelationAttribute is null");
1527  }
1528  if (knownStatus == null) {
1529  throw new EamDbException("Known status is null");
1530  }
1531 
1532  if (eamArtifact.getCorrelationCase() == null) {
1533  throw new EamDbException("Correlation case is null");
1534  }
1535  if (eamArtifact.getCorrelationDataSource() == null) {
1536  throw new EamDbException("Correlation data source is null");
1537  }
1538 
1539  Connection conn = connect();
1540 
1541  PreparedStatement preparedUpdate = null;
1542  PreparedStatement preparedQuery = null;
1543  ResultSet resultSet = null;
1544 
1545  String tableName = EamDbUtil.correlationTypeToInstanceTableName(eamArtifact.getCorrelationType());
1546 
1547  String sqlQuery
1548  = "SELECT id FROM "
1549  + tableName
1550  + " WHERE case_id=(SELECT id FROM cases WHERE case_uid=?) "
1551  + "AND data_source_id=(SELECT id FROM data_sources WHERE device_id=?) "
1552  + "AND value=? "
1553  + "AND file_path=?";
1554 
1555  String sqlUpdate
1556  = "UPDATE "
1557  + tableName
1558  + " SET known_status=?, comment=? "
1559  + "WHERE id=?";
1560 
1561  try {
1562  preparedQuery = conn.prepareStatement(sqlQuery);
1563  preparedQuery.setString(1, eamArtifact.getCorrelationCase().getCaseUUID());
1564  preparedQuery.setString(2, eamArtifact.getCorrelationDataSource().getDeviceID());
1565  preparedQuery.setString(3, eamArtifact.getCorrelationValue());
1566  preparedQuery.setString(4, eamArtifact.getFilePath());
1567  resultSet = preparedQuery.executeQuery();
1568  if (resultSet.next()) {
1569  int instance_id = resultSet.getInt("id");
1570  preparedUpdate = conn.prepareStatement(sqlUpdate);
1571 
1572  preparedUpdate.setByte(1, knownStatus.getFileKnownValue());
1573  // NOTE: if the user tags the same instance as BAD multiple times,
1574  // the comment from the most recent tagging is the one that will
1575  // prevail in the DB.
1576  if ("".equals(eamArtifact.getComment())) {
1577  preparedUpdate.setNull(2, Types.INTEGER);
1578  } else {
1579  preparedUpdate.setString(2, eamArtifact.getComment());
1580  }
1581  preparedUpdate.setInt(3, instance_id);
1582 
1583  preparedUpdate.executeUpdate();
1584  } else {
1585  // In this case, the user is tagging something that isn't in the database,
1586  // which means the case and/or datasource may also not be in the database.
1587  // We could improve effiency by keeping a list of all datasources and cases
1588  // in the database, but we don't expect the user to be tagging large numbers
1589  // of items (that didn't have the CE ingest module run on them) at once.
1590  CorrelationCase correlationCaseWithId = getCaseByUUID(eamArtifact.getCorrelationCase().getCaseUUID());
1591  if (null == correlationCaseWithId) {
1592  correlationCaseWithId = newCase(eamArtifact.getCorrelationCase());
1593  }
1594 
1595  if (null == getDataSource(correlationCaseWithId, eamArtifact.getCorrelationDataSource().getDeviceID())) {
1596  newDataSource(eamArtifact.getCorrelationDataSource());
1597  }
1598  eamArtifact.setKnownStatus(knownStatus);
1599  addArtifactInstance(eamArtifact);
1600  }
1601 
1602  } catch (SQLException ex) {
1603  throw new EamDbException("Error getting/setting artifact instance knownStatus=" + knownStatus.getName(), ex); // NON-NLS
1604  } finally {
1605  EamDbUtil.closeStatement(preparedUpdate);
1606  EamDbUtil.closeStatement(preparedQuery);
1607  EamDbUtil.closeResultSet(resultSet);
1608  EamDbUtil.closeConnection(conn);
1609  }
1610  }
1611 
1621  @Override
1622  public List<CorrelationAttributeInstance> getArtifactInstancesKnownBad(CorrelationAttributeInstance.Type aType, String value) throws EamDbException, CorrelationAttributeNormalizationException {
1623  String normalizedValue = CorrelationAttributeNormalizer.normalize(aType, value);
1624 
1625  Connection conn = connect();
1626 
1627  List<CorrelationAttributeInstance> artifactInstances = new ArrayList<>();
1628 
1629  CorrelationAttributeInstance artifactInstance;
1630  PreparedStatement preparedStatement = null;
1631  ResultSet resultSet = null;
1632 
1633  String tableName = EamDbUtil.correlationTypeToInstanceTableName(aType);
1634  String sql
1635  = "SELECT "
1636  + tableName
1637  + ".id, "
1638  + tableName
1639  + ".value, "
1640  + "cases.case_name, cases.case_uid, data_sources.id AS data_source_id, data_sources.name, device_id, file_path, known_status, comment, data_sources.case_id FROM "
1641  + tableName
1642  + " LEFT JOIN cases ON "
1643  + tableName
1644  + ".case_id=cases.id"
1645  + " LEFT JOIN data_sources ON "
1646  + tableName
1647  + ".data_source_id=data_sources.id"
1648  + " WHERE value=? AND known_status=?";
1649 
1650  try {
1651  preparedStatement = conn.prepareStatement(sql);
1652  preparedStatement.setString(1, normalizedValue);
1653  preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
1654  resultSet = preparedStatement.executeQuery();
1655  while (resultSet.next()) {
1656  artifactInstance = getEamArtifactInstanceFromResultSet(resultSet, aType);
1657  artifactInstances.add(artifactInstance);
1658  }
1659  } catch (SQLException ex) {
1660  throw new EamDbException("Error getting notable artifact instances.", ex); // NON-NLS
1661  } finally {
1662  EamDbUtil.closeStatement(preparedStatement);
1663  EamDbUtil.closeResultSet(resultSet);
1664  EamDbUtil.closeConnection(conn);
1665  }
1666 
1667  return artifactInstances;
1668  }
1669 
1681  @Override
1682  public List<CorrelationAttributeInstance> getArtifactInstancesKnownBad(CorrelationAttributeInstance.Type aType) throws EamDbException {
1683  if (aType == null) {
1684  throw new EamDbException("Correlation type is null");
1685  }
1686 
1687  Connection conn = connect();
1688 
1689  List<CorrelationAttributeInstance> artifactInstances = new ArrayList<>();
1690 
1691  CorrelationAttributeInstance artifactInstance;
1692  PreparedStatement preparedStatement = null;
1693  ResultSet resultSet = null;
1694 
1695  String tableName = EamDbUtil.correlationTypeToInstanceTableName(aType);
1696  String sql
1697  = "SELECT cases.case_name, cases.case_uid, data_sources.name, device_id, file_path, known_status, comment, data_sources.case_id, id, value FROM "
1698  + tableName
1699  + " LEFT JOIN cases ON "
1700  + tableName
1701  + ".case_id=cases.id"
1702  + " LEFT JOIN data_sources ON "
1703  + tableName
1704  + ".data_source_id=data_sources.id"
1705  + " WHERE known_status=?"
1706  + " GROUP BY "
1707  + tableName
1708  + ".value";
1709 
1710  try {
1711  preparedStatement = conn.prepareStatement(sql);
1712  preparedStatement.setByte(1, TskData.FileKnown.BAD.getFileKnownValue());
1713  resultSet = preparedStatement.executeQuery();
1714  while (resultSet.next()) {
1715  try {
1716  artifactInstance = getEamArtifactInstanceFromResultSet(resultSet, aType);
1717  artifactInstances.add(artifactInstance);
1718  } catch (CorrelationAttributeNormalizationException ex) {
1719  logger.log(Level.INFO, "Unable to get artifact instance from resultset.", ex);
1720  }
1721  }
1722  } catch (SQLException ex) {
1723  throw new EamDbException("Error getting notable artifact instances.", ex); // NON-NLS
1724  } finally {
1725  EamDbUtil.closeStatement(preparedStatement);
1726  EamDbUtil.closeResultSet(resultSet);
1727  EamDbUtil.closeConnection(conn);
1728  }
1729 
1730  return artifactInstances;
1731  }
1732 
1741  @Override
1742  public Long getCountArtifactInstancesKnownBad(CorrelationAttributeInstance.Type aType, String value) throws EamDbException, CorrelationAttributeNormalizationException {
1743 
1744  String normalizedValue = CorrelationAttributeNormalizer.normalize(aType, value);
1745 
1746  Connection conn = connect();
1747 
1748  Long badInstances = 0L;
1749  PreparedStatement preparedStatement = null;
1750  ResultSet resultSet = null;
1751 
1752  String tableName = EamDbUtil.correlationTypeToInstanceTableName(aType);
1753  String sql
1754  = "SELECT count(*) FROM "
1755  + tableName
1756  + " WHERE value=? AND known_status=?";
1757 
1758  try {
1759  preparedStatement = conn.prepareStatement(sql);
1760  preparedStatement.setString(1, normalizedValue);
1761  preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
1762  resultSet = preparedStatement.executeQuery();
1763  resultSet.next();
1764  badInstances = resultSet.getLong(1);
1765  } catch (SQLException ex) {
1766  throw new EamDbException("Error getting count of notable artifact instances.", ex); // NON-NLS
1767  } finally {
1768  EamDbUtil.closeStatement(preparedStatement);
1769  EamDbUtil.closeResultSet(resultSet);
1770  EamDbUtil.closeConnection(conn);
1771  }
1772 
1773  return badInstances;
1774  }
1775 
1788  @Override
1789  public List<String> getListCasesHavingArtifactInstancesKnownBad(CorrelationAttributeInstance.Type aType, String value) throws EamDbException, CorrelationAttributeNormalizationException {
1790 
1791  String normalizedValue = CorrelationAttributeNormalizer.normalize(aType, value);
1792 
1793  Connection conn = connect();
1794 
1795  Collection<String> caseNames = new LinkedHashSet<>();
1796 
1797  PreparedStatement preparedStatement = null;
1798  ResultSet resultSet = null;
1799 
1800  String tableName = EamDbUtil.correlationTypeToInstanceTableName(aType);
1801  String sql
1802  = "SELECT DISTINCT case_name FROM "
1803  + tableName
1804  + " INNER JOIN cases ON "
1805  + tableName
1806  + ".case_id=cases.id WHERE "
1807  + tableName
1808  + ".value=? AND "
1809  + tableName
1810  + ".known_status=?";
1811 
1812  try {
1813  preparedStatement = conn.prepareStatement(sql);
1814  preparedStatement.setString(1, normalizedValue);
1815  preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
1816  resultSet = preparedStatement.executeQuery();
1817  while (resultSet.next()) {
1818  caseNames.add(resultSet.getString("case_name"));
1819  }
1820  } catch (SQLException ex) {
1821  throw new EamDbException("Error getting notable artifact instances.", ex); // NON-NLS
1822  } finally {
1823  EamDbUtil.closeStatement(preparedStatement);
1824  EamDbUtil.closeResultSet(resultSet);
1825  EamDbUtil.closeConnection(conn);
1826  }
1827 
1828  return caseNames.stream().collect(Collectors.toList());
1829  }
1830 
1838  @Override
1839  public void deleteReferenceSet(int referenceSetID) throws EamDbException {
1840  deleteReferenceSetEntries(referenceSetID);
1841  deleteReferenceSetEntry(referenceSetID);
1842  }
1843 
1851  private void deleteReferenceSetEntry(int referenceSetID) throws EamDbException {
1852  Connection conn = connect();
1853 
1854  PreparedStatement preparedStatement = null;
1855  String sql = "DELETE FROM reference_sets WHERE id=?";
1856 
1857  try {
1858  preparedStatement = conn.prepareStatement(sql);
1859  preparedStatement.setInt(1, referenceSetID);
1860  preparedStatement.executeUpdate();
1861  } catch (SQLException ex) {
1862  throw new EamDbException("Error deleting reference set " + referenceSetID, ex); // NON-NLS
1863  } finally {
1864  EamDbUtil.closeStatement(preparedStatement);
1865  EamDbUtil.closeConnection(conn);
1866  }
1867  }
1868 
1877  private void deleteReferenceSetEntries(int referenceSetID) throws EamDbException {
1878  Connection conn = connect();
1879 
1880  PreparedStatement preparedStatement = null;
1881  String sql = "DELETE FROM %s WHERE reference_set_id=?";
1882 
1883  // When other reference types are added, this will need to loop over all the tables
1884  String fileTableName = EamDbUtil.correlationTypeToReferenceTableName(getCorrelationTypeById(CorrelationAttributeInstance.FILES_TYPE_ID));
1885 
1886  try {
1887  preparedStatement = conn.prepareStatement(String.format(sql, fileTableName));
1888  preparedStatement.setInt(1, referenceSetID);
1889  preparedStatement.executeUpdate();
1890  } catch (SQLException ex) {
1891  throw new EamDbException("Error deleting files from reference set " + referenceSetID, ex); // NON-NLS
1892  } finally {
1893  EamDbUtil.closeStatement(preparedStatement);
1894  EamDbUtil.closeConnection(conn);
1895  }
1896  }
1897 
1911  @Override
1912  public boolean referenceSetIsValid(int referenceSetID, String setName, String version) throws EamDbException {
1913  EamGlobalSet refSet = this.getReferenceSetByID(referenceSetID);
1914  if (refSet == null) {
1915  return false;
1916  }
1917 
1918  return (refSet.getSetName().equals(setName) && refSet.getVersion().equals(version));
1919  }
1920 
1932  @Override
1933  public boolean isFileHashInReferenceSet(String hash, int referenceSetID) throws EamDbException, CorrelationAttributeNormalizationException {
1934  return isValueInReferenceSet(hash, referenceSetID, CorrelationAttributeInstance.FILES_TYPE_ID);
1935  }
1936 
1946  @Override
1947  public boolean isValueInReferenceSet(String value, int referenceSetID, int correlationTypeID) throws EamDbException, CorrelationAttributeNormalizationException {
1948 
1949  String normalizeValued = CorrelationAttributeNormalizer.normalize(this.getCorrelationTypeById(correlationTypeID), value);
1950 
1951  Connection conn = connect();
1952 
1953  Long matchingInstances = 0L;
1954  PreparedStatement preparedStatement = null;
1955  ResultSet resultSet = null;
1956  String sql = "SELECT count(*) FROM %s WHERE value=? AND reference_set_id=?";
1957 
1958  String fileTableName = EamDbUtil.correlationTypeToReferenceTableName(getCorrelationTypeById(correlationTypeID));
1959 
1960  try {
1961  preparedStatement = conn.prepareStatement(String.format(sql, fileTableName));
1962  preparedStatement.setString(1, normalizeValued);
1963  preparedStatement.setInt(2, referenceSetID);
1964  resultSet = preparedStatement.executeQuery();
1965  resultSet.next();
1966  matchingInstances = resultSet.getLong(1);
1967  } catch (SQLException ex) {
1968  throw new EamDbException("Error determining if value (" + normalizeValued + ") is in reference set " + referenceSetID, ex); // NON-NLS
1969  } finally {
1970  EamDbUtil.closeStatement(preparedStatement);
1971  EamDbUtil.closeResultSet(resultSet);
1972  EamDbUtil.closeConnection(conn);
1973  }
1974 
1975  return 0 < matchingInstances;
1976  }
1977 
1986  @Override
1987  public boolean isArtifactKnownBadByReference(CorrelationAttributeInstance.Type aType, String value) throws EamDbException, CorrelationAttributeNormalizationException {
1988 
1989  //this should be done here so that we can be certain that aType and value are valid before we proceed
1990  String normalizeValued = CorrelationAttributeNormalizer.normalize(aType, value);
1991 
1992  // TEMP: Only support file correlation type
1993  if (aType.getId() != CorrelationAttributeInstance.FILES_TYPE_ID) {
1994  return false;
1995  }
1996 
1997  Connection conn = connect();
1998 
1999  Long badInstances = 0L;
2000  PreparedStatement preparedStatement = null;
2001  ResultSet resultSet = null;
2002  String sql = "SELECT count(*) FROM %s WHERE value=? AND known_status=?";
2003 
2004  try {
2005  preparedStatement = conn.prepareStatement(String.format(sql, EamDbUtil.correlationTypeToReferenceTableName(aType)));
2006  preparedStatement.setString(1, normalizeValued);
2007  preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
2008  resultSet = preparedStatement.executeQuery();
2009  resultSet.next();
2010  badInstances = resultSet.getLong(1);
2011  } catch (SQLException ex) {
2012  throw new EamDbException("Error determining if artifact is notable by reference.", ex); // NON-NLS
2013  } finally {
2014  EamDbUtil.closeStatement(preparedStatement);
2015  EamDbUtil.closeResultSet(resultSet);
2016  EamDbUtil.closeConnection(conn);
2017  }
2018 
2019  return 0 < badInstances;
2020  }
2021 
2030  @Override
2031  public void processInstanceTable(CorrelationAttributeInstance.Type type, InstanceTableCallback instanceTableCallback) throws EamDbException {
2032  if (type == null) {
2033  throw new EamDbException("Correlation type is null");
2034  }
2035 
2036  if (instanceTableCallback == null) {
2037  throw new EamDbException("Callback interface is null");
2038  }
2039 
2040  Connection conn = connect();
2041  PreparedStatement preparedStatement = null;
2042  ResultSet resultSet = null;
2043  String tableName = EamDbUtil.correlationTypeToInstanceTableName(type);
2044  StringBuilder sql = new StringBuilder();
2045  sql.append("select * from ");
2046  sql.append(tableName);
2047 
2048  try {
2049  preparedStatement = conn.prepareStatement(sql.toString());
2050  resultSet = preparedStatement.executeQuery();
2051  instanceTableCallback.process(resultSet);
2052  } catch (SQLException ex) {
2053  throw new EamDbException("Error getting all artifact instances from instances table", ex);
2054  } finally {
2055  EamDbUtil.closeStatement(preparedStatement);
2056  EamDbUtil.closeResultSet(resultSet);
2057  EamDbUtil.closeConnection(conn);
2058  }
2059  }
2060 
2070  @Override
2071  public void processInstanceTableWhere(CorrelationAttributeInstance.Type type, String whereClause, InstanceTableCallback instanceTableCallback) throws EamDbException {
2072  if (type == null) {
2073  throw new EamDbException("Correlation type is null");
2074  }
2075 
2076  if (instanceTableCallback == null) {
2077  throw new EamDbException("Callback interface is null");
2078  }
2079 
2080  if (whereClause == null) {
2081  throw new EamDbException("Where clause is null");
2082  }
2083 
2084  Connection conn = connect();
2085  PreparedStatement preparedStatement = null;
2086  ResultSet resultSet = null;
2087  String tableName = EamDbUtil.correlationTypeToInstanceTableName(type);
2088  StringBuilder sql = new StringBuilder(300);
2089  sql.append("select * from ")
2090  .append(tableName)
2091  .append(" WHERE ")
2092  .append(whereClause);
2093 
2094  try {
2095  preparedStatement = conn.prepareStatement(sql.toString());
2096  resultSet = preparedStatement.executeQuery();
2097  instanceTableCallback.process(resultSet);
2098  } catch (SQLException ex) {
2099  throw new EamDbException("Error getting all artifact instances from instances table", ex);
2100  } finally {
2101  EamDbUtil.closeStatement(preparedStatement);
2102  EamDbUtil.closeResultSet(resultSet);
2103  EamDbUtil.closeConnection(conn);
2104  }
2105  }
2106 
2107  @Override
2108  public EamOrganization newOrganization(EamOrganization eamOrg) throws EamDbException {
2109  if (eamOrg == null) {
2110  throw new EamDbException("EamOrganization is null");
2111  } else if (eamOrg.getOrgID() != -1) {
2112  throw new EamDbException("EamOrganization already has an ID");
2113  }
2114 
2115  Connection conn = connect();
2116  ResultSet generatedKeys = null;
2117  PreparedStatement preparedStatement = null;
2118  String sql = "INSERT INTO organizations(org_name, poc_name, poc_email, poc_phone) VALUES (?, ?, ?, ?) "
2119  + getConflictClause();
2120 
2121  try {
2122  preparedStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
2123  preparedStatement.setString(1, eamOrg.getName());
2124  preparedStatement.setString(2, eamOrg.getPocName());
2125  preparedStatement.setString(3, eamOrg.getPocEmail());
2126  preparedStatement.setString(4, eamOrg.getPocPhone());
2127 
2128  preparedStatement.executeUpdate();
2129  generatedKeys = preparedStatement.getGeneratedKeys();
2130  if (generatedKeys.next()) {
2131  eamOrg.setOrgID((int) generatedKeys.getLong(1));
2132  return eamOrg;
2133  } else {
2134  throw new SQLException("Creating user failed, no ID obtained.");
2135  }
2136  } catch (SQLException ex) {
2137  throw new EamDbException("Error inserting new organization.", ex); // NON-NLS
2138  } finally {
2139  EamDbUtil.closeStatement(preparedStatement);
2140  EamDbUtil.closeResultSet(generatedKeys);
2141  EamDbUtil.closeConnection(conn);
2142  }
2143  }
2144 
2152  @Override
2153  public List<EamOrganization> getOrganizations() throws EamDbException {
2154  Connection conn = connect();
2155 
2156  List<EamOrganization> orgs = new ArrayList<>();
2157  PreparedStatement preparedStatement = null;
2158  ResultSet resultSet = null;
2159  String sql = "SELECT * FROM organizations";
2160 
2161  try {
2162  preparedStatement = conn.prepareStatement(sql);
2163  resultSet = preparedStatement.executeQuery();
2164  while (resultSet.next()) {
2165  orgs.add(getEamOrganizationFromResultSet(resultSet));
2166  }
2167  return orgs;
2168 
2169  } catch (SQLException ex) {
2170  throw new EamDbException("Error getting all organizations.", ex); // NON-NLS
2171  } finally {
2172  EamDbUtil.closeStatement(preparedStatement);
2173  EamDbUtil.closeResultSet(resultSet);
2174  EamDbUtil.closeConnection(conn);
2175  }
2176  }
2177 
2187  @Override
2188  public EamOrganization getOrganizationByID(int orgID) throws EamDbException {
2189  Connection conn = connect();
2190 
2191  PreparedStatement preparedStatement = null;
2192  ResultSet resultSet = null;
2193  String sql = "SELECT * FROM organizations WHERE id=?";
2194 
2195  try {
2196  preparedStatement = conn.prepareStatement(sql);
2197  preparedStatement.setInt(1, orgID);
2198  resultSet = preparedStatement.executeQuery();
2199  resultSet.next();
2200  return getEamOrganizationFromResultSet(resultSet);
2201 
2202  } catch (SQLException ex) {
2203  throw new EamDbException("Error getting organization by id.", ex); // NON-NLS
2204  } finally {
2205  EamDbUtil.closeStatement(preparedStatement);
2206  EamDbUtil.closeResultSet(resultSet);
2207  EamDbUtil.closeConnection(conn);
2208  }
2209  }
2210 
2220  @Override
2221  public EamOrganization getReferenceSetOrganization(int referenceSetID) throws EamDbException {
2222 
2223  EamGlobalSet globalSet = getReferenceSetByID(referenceSetID);
2224  if (globalSet == null) {
2225  throw new EamDbException("Reference set with ID " + referenceSetID + " not found");
2226  }
2227  return (getOrganizationByID(globalSet.getOrgID()));
2228  }
2229 
2237  private void testArgument(EamOrganization org) throws EamDbException {
2238  if (org == null) {
2239  throw new EamDbException("EamOrganization is null");
2240  } else if (org.getOrgID() == -1) {
2241  throw new EamDbException("Organization has -1 row ID");
2242  }
2243  }
2244 
2253  @Override
2254  public void updateOrganization(EamOrganization updatedOrganization) throws EamDbException {
2255  testArgument(updatedOrganization);
2256 
2257  Connection conn = connect();
2258  PreparedStatement preparedStatement = null;
2259  String sql = "UPDATE organizations SET org_name = ?, poc_name = ?, poc_email = ?, poc_phone = ? WHERE id = ?";
2260  try {
2261  preparedStatement = conn.prepareStatement(sql);
2262  preparedStatement.setString(1, updatedOrganization.getName());
2263  preparedStatement.setString(2, updatedOrganization.getPocName());
2264  preparedStatement.setString(3, updatedOrganization.getPocEmail());
2265  preparedStatement.setString(4, updatedOrganization.getPocPhone());
2266  preparedStatement.setInt(5, updatedOrganization.getOrgID());
2267  preparedStatement.executeUpdate();
2268  } catch (SQLException ex) {
2269  throw new EamDbException("Error updating organization.", ex); // NON-NLS
2270  } finally {
2271  EamDbUtil.closeStatement(preparedStatement);
2272  EamDbUtil.closeConnection(conn);
2273  }
2274  }
2275 
2276  @Override
2277  public void deleteOrganization(EamOrganization organizationToDelete) throws EamDbException {
2278  testArgument(organizationToDelete);
2279 
2280  Connection conn = connect();
2281  PreparedStatement checkIfUsedStatement = null;
2282  ResultSet resultSet = null;
2283  String checkIfUsedSql = "SELECT (select count(*) FROM cases WHERE org_id=?) + (select count(*) FROM reference_sets WHERE org_id=?)";
2284  PreparedStatement deleteOrgStatement = null;
2285  String deleteOrgSql = "DELETE FROM organizations WHERE id=?";
2286  try {
2287  checkIfUsedStatement = conn.prepareStatement(checkIfUsedSql);
2288  checkIfUsedStatement.setInt(1, organizationToDelete.getOrgID());
2289  checkIfUsedStatement.setInt(2, organizationToDelete.getOrgID());
2290  resultSet = checkIfUsedStatement.executeQuery();
2291  resultSet.next();
2292  if (resultSet.getLong(1) > 0) {
2293  throw new EamDbException("Can not delete organization which is currently in use by a case or reference set in the central repository.");
2294  }
2295  deleteOrgStatement = conn.prepareStatement(deleteOrgSql);
2296  deleteOrgStatement.setInt(1, organizationToDelete.getOrgID());
2297  deleteOrgStatement.executeUpdate();
2298  } catch (SQLException ex) {
2299  throw new EamDbException("Error executing query when attempting to delete organization by id.", ex); // NON-NLS
2300  } finally {
2301  EamDbUtil.closeStatement(checkIfUsedStatement);
2302  EamDbUtil.closeStatement(deleteOrgStatement);
2303  EamDbUtil.closeResultSet(resultSet);
2304  EamDbUtil.closeConnection(conn);
2305  }
2306  }
2307 
2317  @Override
2318  public int newReferenceSet(EamGlobalSet eamGlobalSet) throws EamDbException {
2319  if (eamGlobalSet == null) {
2320  throw new EamDbException("EamGlobalSet is null");
2321  }
2322 
2323  if (eamGlobalSet.getFileKnownStatus() == null) {
2324  throw new EamDbException("File known status on the EamGlobalSet is null");
2325  }
2326 
2327  if (eamGlobalSet.getType() == null) {
2328  throw new EamDbException("Type on the EamGlobalSet is null");
2329  }
2330 
2331  Connection conn = connect();
2332 
2333  PreparedStatement preparedStatement1 = null;
2334  PreparedStatement preparedStatement2 = null;
2335  ResultSet resultSet = null;
2336  String sql1 = "INSERT INTO reference_sets(org_id, set_name, version, known_status, read_only, type, import_date) VALUES (?, ?, ?, ?, ?, ?, ?) "
2337  + getConflictClause();
2338  String sql2 = "SELECT id FROM reference_sets WHERE org_id=? AND set_name=? AND version=? AND import_date=? LIMIT 1";
2339 
2340  try {
2341  preparedStatement1 = conn.prepareStatement(sql1);
2342  preparedStatement1.setInt(1, eamGlobalSet.getOrgID());
2343  preparedStatement1.setString(2, eamGlobalSet.getSetName());
2344  preparedStatement1.setString(3, eamGlobalSet.getVersion());
2345  preparedStatement1.setInt(4, eamGlobalSet.getFileKnownStatus().getFileKnownValue());
2346  preparedStatement1.setBoolean(5, eamGlobalSet.isReadOnly());
2347  preparedStatement1.setInt(6, eamGlobalSet.getType().getId());
2348  preparedStatement1.setString(7, eamGlobalSet.getImportDate().toString());
2349 
2350  preparedStatement1.executeUpdate();
2351 
2352  preparedStatement2 = conn.prepareStatement(sql2);
2353  preparedStatement2.setInt(1, eamGlobalSet.getOrgID());
2354  preparedStatement2.setString(2, eamGlobalSet.getSetName());
2355  preparedStatement2.setString(3, eamGlobalSet.getVersion());
2356  preparedStatement2.setString(4, eamGlobalSet.getImportDate().toString());
2357 
2358  resultSet = preparedStatement2.executeQuery();
2359  resultSet.next();
2360  return resultSet.getInt("id");
2361 
2362  } catch (SQLException ex) {
2363  throw new EamDbException("Error inserting new global set.", ex); // NON-NLS
2364  } finally {
2365  EamDbUtil.closeStatement(preparedStatement1);
2366  EamDbUtil.closeStatement(preparedStatement2);
2367  EamDbUtil.closeResultSet(resultSet);
2368  EamDbUtil.closeConnection(conn);
2369  }
2370  }
2371 
2381  @Override
2382  public EamGlobalSet getReferenceSetByID(int referenceSetID) throws EamDbException {
2383  Connection conn = connect();
2384 
2385  PreparedStatement preparedStatement1 = null;
2386  ResultSet resultSet = null;
2387  String sql1 = "SELECT * FROM reference_sets WHERE id=?";
2388 
2389  try {
2390  preparedStatement1 = conn.prepareStatement(sql1);
2391  preparedStatement1.setInt(1, referenceSetID);
2392  resultSet = preparedStatement1.executeQuery();
2393  if (resultSet.next()) {
2394  return getEamGlobalSetFromResultSet(resultSet);
2395  } else {
2396  return null;
2397  }
2398 
2399  } catch (SQLException ex) {
2400  throw new EamDbException("Error getting reference set by id.", ex); // NON-NLS
2401  } finally {
2402  EamDbUtil.closeStatement(preparedStatement1);
2403  EamDbUtil.closeResultSet(resultSet);
2404  EamDbUtil.closeConnection(conn);
2405  }
2406  }
2407 
2417  @Override
2418  public List<EamGlobalSet> getAllReferenceSets(CorrelationAttributeInstance.Type correlationType) throws EamDbException {
2419 
2420  if (correlationType == null) {
2421  throw new EamDbException("Correlation type is null");
2422  }
2423 
2424  List<EamGlobalSet> results = new ArrayList<>();
2425  Connection conn = connect();
2426 
2427  PreparedStatement preparedStatement1 = null;
2428  ResultSet resultSet = null;
2429  String sql1 = "SELECT * FROM reference_sets WHERE type=" + correlationType.getId();
2430 
2431  try {
2432  preparedStatement1 = conn.prepareStatement(sql1);
2433  resultSet = preparedStatement1.executeQuery();
2434  while (resultSet.next()) {
2435  results.add(getEamGlobalSetFromResultSet(resultSet));
2436  }
2437 
2438  } catch (SQLException ex) {
2439  throw new EamDbException("Error getting reference sets.", ex); // NON-NLS
2440  } finally {
2441  EamDbUtil.closeStatement(preparedStatement1);
2442  EamDbUtil.closeResultSet(resultSet);
2443  EamDbUtil.closeConnection(conn);
2444  }
2445  return results;
2446  }
2447 
2457  @Override
2458  public void addReferenceInstance(EamGlobalFileInstance eamGlobalFileInstance, CorrelationAttributeInstance.Type correlationType) throws EamDbException {
2459  if (eamGlobalFileInstance.getKnownStatus() == null) {
2460  throw new EamDbException("Known status of EamGlobalFileInstance is null");
2461  }
2462  if (correlationType == null) {
2463  throw new EamDbException("Correlation type is null");
2464  }
2465 
2466  Connection conn = connect();
2467 
2468  PreparedStatement preparedStatement = null;
2469 
2470  String sql = "INSERT INTO %s(reference_set_id, value, known_status, comment) VALUES (?, ?, ?, ?) "
2471  + getConflictClause();
2472 
2473  try {
2474  preparedStatement = conn.prepareStatement(String.format(sql, EamDbUtil.correlationTypeToReferenceTableName(correlationType)));
2475  preparedStatement.setInt(1, eamGlobalFileInstance.getGlobalSetID());
2476  preparedStatement.setString(2, eamGlobalFileInstance.getMD5Hash());
2477  preparedStatement.setByte(3, eamGlobalFileInstance.getKnownStatus().getFileKnownValue());
2478  preparedStatement.setString(4, eamGlobalFileInstance.getComment());
2479  preparedStatement.executeUpdate();
2480  } catch (SQLException ex) {
2481  throw new EamDbException("Error inserting new reference instance into reference_ table.", ex); // NON-NLS
2482  } finally {
2483  EamDbUtil.closeStatement(preparedStatement);
2484  EamDbUtil.closeConnection(conn);
2485  }
2486  }
2487 
2500  @Override
2501  public boolean referenceSetExists(String referenceSetName, String version) throws EamDbException {
2502  Connection conn = connect();
2503 
2504  PreparedStatement preparedStatement1 = null;
2505  ResultSet resultSet = null;
2506  String sql1 = "SELECT * FROM reference_sets WHERE set_name=? AND version=?";
2507 
2508  try {
2509  preparedStatement1 = conn.prepareStatement(sql1);
2510  preparedStatement1.setString(1, referenceSetName);
2511  preparedStatement1.setString(2, version);
2512  resultSet = preparedStatement1.executeQuery();
2513  return (resultSet.next());
2514 
2515  } catch (SQLException ex) {
2516  throw new EamDbException("Error testing whether reference set exists (name: " + referenceSetName
2517  + " version: " + version, ex); // NON-NLS
2518  } finally {
2519  EamDbUtil.closeStatement(preparedStatement1);
2520  EamDbUtil.closeResultSet(resultSet);
2521  EamDbUtil.closeConnection(conn);
2522  }
2523  }
2524 
2530  @Override
2531  public void bulkInsertReferenceTypeEntries(Set<EamGlobalFileInstance> globalInstances, CorrelationAttributeInstance.Type contentType) throws EamDbException {
2532  if (contentType == null) {
2533  throw new EamDbException("Correlation type is null");
2534  }
2535  if (globalInstances == null) {
2536  throw new EamDbException("Null set of EamGlobalFileInstance");
2537  }
2538 
2539  Connection conn = connect();
2540 
2541  PreparedStatement bulkPs = null;
2542  try {
2543  conn.setAutoCommit(false);
2544 
2545  // FUTURE: have a separate global_files table for each Type.
2546  String sql = "INSERT INTO %s(reference_set_id, value, known_status, comment) VALUES (?, ?, ?, ?) "
2547  + getConflictClause();
2548 
2549  bulkPs = conn.prepareStatement(String.format(sql, EamDbUtil.correlationTypeToReferenceTableName(contentType)));
2550 
2551  for (EamGlobalFileInstance globalInstance : globalInstances) {
2552  if (globalInstance.getKnownStatus() == null) {
2553  throw new EamDbException("EamGlobalFileInstance with value " + globalInstance.getMD5Hash() + " has null known status");
2554  }
2555 
2556  bulkPs.setInt(1, globalInstance.getGlobalSetID());
2557  bulkPs.setString(2, globalInstance.getMD5Hash());
2558  bulkPs.setByte(3, globalInstance.getKnownStatus().getFileKnownValue());
2559  bulkPs.setString(4, globalInstance.getComment());
2560  bulkPs.addBatch();
2561  }
2562 
2563  bulkPs.executeBatch();
2564  conn.commit();
2565  } catch (SQLException | EamDbException ex) {
2566  try {
2567  conn.rollback();
2568  } catch (SQLException ex2) {
2569  // We're alredy in an error state
2570  }
2571  throw new EamDbException("Error inserting bulk artifacts.", ex); // NON-NLS
2572  } finally {
2573  EamDbUtil.closeStatement(bulkPs);
2574  EamDbUtil.closeConnection(conn);
2575  }
2576  }
2577 
2588  @Override
2589  public List<EamGlobalFileInstance> getReferenceInstancesByTypeValue(CorrelationAttributeInstance.Type aType, String aValue) throws EamDbException, CorrelationAttributeNormalizationException {
2590  String normalizeValued = CorrelationAttributeNormalizer.normalize(aType, aValue);
2591 
2592  Connection conn = connect();
2593 
2594  List<EamGlobalFileInstance> globalFileInstances = new ArrayList<>();
2595  PreparedStatement preparedStatement1 = null;
2596  ResultSet resultSet = null;
2597  String sql1 = "SELECT * FROM %s WHERE value=?";
2598 
2599  try {
2600  preparedStatement1 = conn.prepareStatement(String.format(sql1, EamDbUtil.correlationTypeToReferenceTableName(aType)));
2601  preparedStatement1.setString(1, normalizeValued);
2602  resultSet = preparedStatement1.executeQuery();
2603  while (resultSet.next()) {
2604  globalFileInstances.add(getEamGlobalFileInstanceFromResultSet(resultSet));
2605  }
2606 
2607  } catch (SQLException ex) {
2608  throw new EamDbException("Error getting reference instances by type and value.", ex); // NON-NLS
2609  } finally {
2610  EamDbUtil.closeStatement(preparedStatement1);
2611  EamDbUtil.closeResultSet(resultSet);
2612  EamDbUtil.closeConnection(conn);
2613  }
2614 
2615  return globalFileInstances;
2616  }
2617 
2627  @Override
2628  public int newCorrelationType(CorrelationAttributeInstance.Type newType) throws EamDbException {
2629  if (newType == null) {
2630  throw new EamDbException("Correlation type is null");
2631  }
2632  int typeId;
2633  if (-1 == newType.getId()) {
2634  typeId = newCorrelationTypeNotKnownId(newType);
2635  } else {
2636  typeId = newCorrelationTypeKnownId(newType);
2637  }
2638 
2639  return typeId;
2640  }
2641 
2652  public int newCorrelationTypeNotKnownId(CorrelationAttributeInstance.Type newType) throws EamDbException {
2653  Connection conn = connect();
2654 
2655  PreparedStatement preparedStatement = null;
2656  PreparedStatement preparedStatementQuery = null;
2657  ResultSet resultSet = null;
2658  int typeId = 0;
2659  String insertSql;
2660  String querySql;
2661  // if we have a known ID, use it, if not (is -1) let the db assign it.
2662  insertSql = "INSERT INTO correlation_types(display_name, db_table_name, supported, enabled) VALUES (?, ?, ?, ?) " + getConflictClause();
2663 
2664  querySql = "SELECT * FROM correlation_types WHERE display_name=? AND db_table_name=?";
2665 
2666  try {
2667  preparedStatement = conn.prepareStatement(insertSql);
2668 
2669  preparedStatement.setString(1, newType.getDisplayName());
2670  preparedStatement.setString(2, newType.getDbTableName());
2671  preparedStatement.setInt(3, newType.isSupported() ? 1 : 0);
2672  preparedStatement.setInt(4, newType.isEnabled() ? 1 : 0);
2673 
2674  preparedStatement.executeUpdate();
2675 
2676  preparedStatementQuery = conn.prepareStatement(querySql);
2677  preparedStatementQuery.setString(1, newType.getDisplayName());
2678  preparedStatementQuery.setString(2, newType.getDbTableName());
2679 
2680  resultSet = preparedStatementQuery.executeQuery();
2681  if (resultSet.next()) {
2682  CorrelationAttributeInstance.Type correlationType = getCorrelationTypeFromResultSet(resultSet);
2683  typeId = correlationType.getId();
2684  }
2685  } catch (SQLException ex) {
2686  throw new EamDbException("Error inserting new correlation type.", ex); // NON-NLS
2687  } finally {
2688  EamDbUtil.closeStatement(preparedStatement);
2689  EamDbUtil.closeStatement(preparedStatementQuery);
2690  EamDbUtil.closeResultSet(resultSet);
2691  EamDbUtil.closeConnection(conn);
2692  }
2693  return typeId;
2694  }
2695 
2705  private int newCorrelationTypeKnownId(CorrelationAttributeInstance.Type newType) throws EamDbException {
2706  Connection conn = connect();
2707 
2708  PreparedStatement preparedStatement = null;
2709  PreparedStatement preparedStatementQuery = null;
2710  ResultSet resultSet = null;
2711  int typeId = 0;
2712  String insertSql;
2713  String querySql;
2714  // if we have a known ID, use it, if not (is -1) let the db assign it.
2715  insertSql = "INSERT INTO correlation_types(id, display_name, db_table_name, supported, enabled) VALUES (?, ?, ?, ?, ?) " + getConflictClause();
2716 
2717  querySql = "SELECT * FROM correlation_types WHERE display_name=? AND db_table_name=?";
2718 
2719  try {
2720  preparedStatement = conn.prepareStatement(insertSql);
2721 
2722  preparedStatement.setInt(1, newType.getId());
2723  preparedStatement.setString(2, newType.getDisplayName());
2724  preparedStatement.setString(3, newType.getDbTableName());
2725  preparedStatement.setInt(4, newType.isSupported() ? 1 : 0);
2726  preparedStatement.setInt(5, newType.isEnabled() ? 1 : 0);
2727 
2728  preparedStatement.executeUpdate();
2729 
2730  preparedStatementQuery = conn.prepareStatement(querySql);
2731  preparedStatementQuery.setString(1, newType.getDisplayName());
2732  preparedStatementQuery.setString(2, newType.getDbTableName());
2733 
2734  resultSet = preparedStatementQuery.executeQuery();
2735  if (resultSet.next()) {
2736  CorrelationAttributeInstance.Type correlationType = getCorrelationTypeFromResultSet(resultSet);
2737  typeId = correlationType.getId();
2738  }
2739  } catch (SQLException ex) {
2740  throw new EamDbException("Error inserting new correlation type.", ex); // NON-NLS
2741  } finally {
2742  EamDbUtil.closeStatement(preparedStatement);
2743  EamDbUtil.closeStatement(preparedStatementQuery);
2744  EamDbUtil.closeResultSet(resultSet);
2745  EamDbUtil.closeConnection(conn);
2746  }
2747  return typeId;
2748  }
2749 
2750  @Override
2751  public List<CorrelationAttributeInstance.Type> getDefinedCorrelationTypes() throws EamDbException {
2752  Connection conn = connect();
2753 
2754  List<CorrelationAttributeInstance.Type> aTypes = new ArrayList<>();
2755  PreparedStatement preparedStatement = null;
2756  ResultSet resultSet = null;
2757  String sql = "SELECT * FROM correlation_types";
2758 
2759  try {
2760  preparedStatement = conn.prepareStatement(sql);
2761  resultSet = preparedStatement.executeQuery();
2762  while (resultSet.next()) {
2763  aTypes.add(getCorrelationTypeFromResultSet(resultSet));
2764  }
2765  return aTypes;
2766 
2767  } catch (SQLException ex) {
2768  throw new EamDbException("Error getting all correlation types.", ex); // NON-NLS
2769  } finally {
2770  EamDbUtil.closeStatement(preparedStatement);
2771  EamDbUtil.closeResultSet(resultSet);
2772  EamDbUtil.closeConnection(conn);
2773  }
2774  }
2775 
2785  @Override
2786  public List<CorrelationAttributeInstance.Type> getEnabledCorrelationTypes() throws EamDbException {
2787  Connection conn = connect();
2788 
2789  List<CorrelationAttributeInstance.Type> aTypes = new ArrayList<>();
2790  PreparedStatement preparedStatement = null;
2791  ResultSet resultSet = null;
2792  String sql = "SELECT * FROM correlation_types WHERE enabled=1";
2793 
2794  try {
2795  preparedStatement = conn.prepareStatement(sql);
2796  resultSet = preparedStatement.executeQuery();
2797  while (resultSet.next()) {
2798  aTypes.add(getCorrelationTypeFromResultSet(resultSet));
2799  }
2800  return aTypes;
2801 
2802  } catch (SQLException ex) {
2803  throw new EamDbException("Error getting enabled correlation types.", ex); // NON-NLS
2804  } finally {
2805  EamDbUtil.closeStatement(preparedStatement);
2806  EamDbUtil.closeResultSet(resultSet);
2807  EamDbUtil.closeConnection(conn);
2808  }
2809  }
2810 
2820  @Override
2821  public List<CorrelationAttributeInstance.Type> getSupportedCorrelationTypes() throws EamDbException {
2822  Connection conn = connect();
2823 
2824  List<CorrelationAttributeInstance.Type> aTypes = new ArrayList<>();
2825  PreparedStatement preparedStatement = null;
2826  ResultSet resultSet = null;
2827  String sql = "SELECT * FROM correlation_types WHERE supported=1";
2828 
2829  try {
2830  preparedStatement = conn.prepareStatement(sql);
2831  resultSet = preparedStatement.executeQuery();
2832  while (resultSet.next()) {
2833  aTypes.add(getCorrelationTypeFromResultSet(resultSet));
2834  }
2835  return aTypes;
2836 
2837  } catch (SQLException ex) {
2838  throw new EamDbException("Error getting supported correlation types.", ex); // NON-NLS
2839  } finally {
2840  EamDbUtil.closeStatement(preparedStatement);
2841  EamDbUtil.closeResultSet(resultSet);
2842  EamDbUtil.closeConnection(conn);
2843  }
2844  }
2845 
2853  @Override
2854  public void updateCorrelationType(CorrelationAttributeInstance.Type aType) throws EamDbException {
2855  Connection conn = connect();
2856 
2857  PreparedStatement preparedStatement = null;
2858  String sql = "UPDATE correlation_types SET display_name=?, db_table_name=?, supported=?, enabled=? WHERE id=?";
2859 
2860  try {
2861  preparedStatement = conn.prepareStatement(sql);
2862  preparedStatement.setString(1, aType.getDisplayName());
2863  preparedStatement.setString(2, aType.getDbTableName());
2864  preparedStatement.setInt(3, aType.isSupported() ? 1 : 0);
2865  preparedStatement.setInt(4, aType.isEnabled() ? 1 : 0);
2866  preparedStatement.setInt(5, aType.getId());
2867  preparedStatement.executeUpdate();
2868  typeCache.put(aType.getId(), aType);
2869  } catch (SQLException ex) {
2870  throw new EamDbException("Error updating correlation type.", ex); // NON-NLS
2871  } finally {
2872  EamDbUtil.closeStatement(preparedStatement);
2873  EamDbUtil.closeConnection(conn);
2874  }
2875 
2876  }
2877 
2887  @Override
2888  public CorrelationAttributeInstance.Type getCorrelationTypeById(int typeId) throws EamDbException {
2889  try {
2890  return typeCache.get(typeId, () -> getCorrelationTypeByIdFromCr(typeId));
2891  } catch (CacheLoader.InvalidCacheLoadException ignored) {
2892  //lambda valueloader returned a null value and cache can not store null values this is normal if the correlation type does not exist in the central repo yet
2893  return null;
2894  } catch (ExecutionException ex) {
2895  throw new EamDbException("Error getting correlation type", ex);
2896  }
2897  }
2898 
2908  private CorrelationAttributeInstance.Type getCorrelationTypeByIdFromCr(int typeId) throws EamDbException {
2909  Connection conn = connect();
2910 
2911  CorrelationAttributeInstance.Type aType;
2912  PreparedStatement preparedStatement = null;
2913  ResultSet resultSet = null;
2914  String sql = "SELECT * FROM correlation_types WHERE id=?";
2915 
2916  try {
2917  preparedStatement = conn.prepareStatement(sql);
2918  preparedStatement.setInt(1, typeId);
2919  resultSet = preparedStatement.executeQuery();
2920  if (resultSet.next()) {
2921  aType = getCorrelationTypeFromResultSet(resultSet);
2922  return aType;
2923  } else {
2924  throw new EamDbException("Failed to find entry for correlation type ID = " + typeId);
2925  }
2926 
2927  } catch (SQLException ex) {
2928  throw new EamDbException("Error getting correlation type by id.", ex); // NON-NLS
2929  } finally {
2930  EamDbUtil.closeStatement(preparedStatement);
2931  EamDbUtil.closeResultSet(resultSet);
2932  EamDbUtil.closeConnection(conn);
2933  }
2934  }
2935 
2946  private CorrelationCase getEamCaseFromResultSet(ResultSet resultSet) throws SQLException {
2947  if (null == resultSet) {
2948  return null;
2949  }
2950 
2951  EamOrganization eamOrg = null;
2952 
2953  resultSet.getInt("org_id");
2954  if (!resultSet.wasNull()) {
2955 
2956  eamOrg = new EamOrganization(resultSet.getInt("org_id"),
2957  resultSet.getString("org_name"),
2958  resultSet.getString("poc_name"),
2959  resultSet.getString("poc_email"),
2960  resultSet.getString("poc_phone"));
2961  }
2962 
2963  CorrelationCase eamCase = new CorrelationCase(resultSet.getInt("case_id"), resultSet.getString("case_uid"), resultSet.getString("case_name"));
2964  eamCase.setOrg(eamOrg);
2965  eamCase.setCreationDate(resultSet.getString("creation_date"));
2966  eamCase.setCaseNumber(resultSet.getString("case_number"));
2967  eamCase.setExaminerName(resultSet.getString("examiner_name"));
2968  eamCase.setExaminerEmail(resultSet.getString("examiner_email"));
2969  eamCase.setExaminerPhone(resultSet.getString("examiner_phone"));
2970  eamCase.setNotes(resultSet.getString("notes"));
2971 
2972  return eamCase;
2973  }
2974 
2975  private CorrelationDataSource getEamDataSourceFromResultSet(ResultSet resultSet) throws SQLException {
2976  if (null == resultSet) {
2977  return null;
2978  }
2979 
2980  CorrelationDataSource eamDataSource = new CorrelationDataSource(
2981  resultSet.getInt("case_id"),
2982  resultSet.getInt("id"),
2983  resultSet.getString("device_id"),
2984  resultSet.getString("name")
2985  );
2986 
2987  return eamDataSource;
2988  }
2989 
2990  private CorrelationAttributeInstance.Type getCorrelationTypeFromResultSet(ResultSet resultSet) throws EamDbException, SQLException {
2991  if (null == resultSet) {
2992  return null;
2993  }
2994 
2995  CorrelationAttributeInstance.Type eamArtifactType = new CorrelationAttributeInstance.Type(
2996  resultSet.getInt("id"),
2997  resultSet.getString("display_name"),
2998  resultSet.getString("db_table_name"),
2999  resultSet.getBoolean("supported"),
3000  resultSet.getBoolean("enabled")
3001  );
3002 
3003  return eamArtifactType;
3004  }
3005 
3016  private CorrelationAttributeInstance getEamArtifactInstanceFromResultSet(ResultSet resultSet, CorrelationAttributeInstance.Type aType) throws SQLException, EamDbException, CorrelationAttributeNormalizationException {
3017  if (null == resultSet) {
3018  return null;
3019  }
3020  // @@@ We should have data source ID in the previous query instead of passing -1 into the below constructor
3021  return new CorrelationAttributeInstance(
3022  aType,
3023  resultSet.getString("value"),
3024  resultSet.getInt("id"),
3025  new CorrelationCase(resultSet.getInt("case_id"), resultSet.getString("case_uid"), resultSet.getString("case_name")),
3026  new CorrelationDataSource(resultSet.getInt("case_id"), resultSet.getInt("data_source_id"), resultSet.getString("device_id"), resultSet.getString("name")),
3027  resultSet.getString("file_path"),
3028  resultSet.getString("comment"),
3029  TskData.FileKnown.valueOf(resultSet.getByte("known_status"))
3030  );
3031  }
3032 
3033  private EamOrganization getEamOrganizationFromResultSet(ResultSet resultSet) throws SQLException {
3034  if (null == resultSet) {
3035  return null;
3036  }
3037 
3038  return new EamOrganization(
3039  resultSet.getInt("id"),
3040  resultSet.getString("org_name"),
3041  resultSet.getString("poc_name"),
3042  resultSet.getString("poc_email"),
3043  resultSet.getString("poc_phone")
3044  );
3045  }
3046 
3047  private EamGlobalSet getEamGlobalSetFromResultSet(ResultSet resultSet) throws SQLException, EamDbException {
3048  if (null == resultSet) {
3049  return null;
3050  }
3051 
3052  return new EamGlobalSet(
3053  resultSet.getInt("id"),
3054  resultSet.getInt("org_id"),
3055  resultSet.getString("set_name"),
3056  resultSet.getString("version"),
3057  TskData.FileKnown.valueOf(resultSet.getByte("known_status")),
3058  resultSet.getBoolean("read_only"),
3059  EamDb.getInstance().getCorrelationTypeById(resultSet.getInt("type")),
3060  LocalDate.parse(resultSet.getString("import_date"))
3061  );
3062  }
3063 
3064  private EamGlobalFileInstance getEamGlobalFileInstanceFromResultSet(ResultSet resultSet) throws SQLException, EamDbException, CorrelationAttributeNormalizationException {
3065  if (null == resultSet) {
3066  return null;
3067  }
3068 
3069  return new EamGlobalFileInstance(
3070  resultSet.getInt("id"),
3071  resultSet.getInt("reference_set_id"),
3072  resultSet.getString("value"),
3073  TskData.FileKnown.valueOf(resultSet.getByte("known_status")),
3074  resultSet.getString("comment")
3075  );
3076  }
3077 
3083  @Override
3084  public void upgradeSchema() throws EamDbException, SQLException {
3085 
3086  ResultSet resultSet = null;
3087  Statement statement = null;
3088  Connection conn = null;
3089  try {
3090 
3091  conn = connect();
3092  conn.setAutoCommit(false);
3093  statement = conn.createStatement();
3094 
3095  int minorVersion = 0;
3096  resultSet = statement.executeQuery("SELECT value FROM db_info WHERE name='SCHEMA_MINOR_VERSION'");
3097  if (resultSet.next()) {
3098  String minorVersionStr = resultSet.getString("value");
3099  try {
3100  minorVersion = Integer.parseInt(minorVersionStr);
3101  } catch (NumberFormatException ex) {
3102  throw new EamDbException("Bad value for schema minor version (" + minorVersionStr + ") - database is corrupt", ex);
3103  }
3104  }
3105 
3106  int majorVersion = 0;
3107  resultSet = statement.executeQuery("SELECT value FROM db_info WHERE name='SCHEMA_VERSION'");
3108  if (resultSet.next()) {
3109  String majorVersionStr = resultSet.getString("value");
3110  try {
3111  majorVersion = Integer.parseInt(majorVersionStr);
3112  } catch (NumberFormatException ex) {
3113  throw new EamDbException("Bad value for schema version (" + majorVersionStr + ") - database is corrupt", ex);
3114  }
3115  }
3116 
3117  CaseDbSchemaVersionNumber dbSchemaVersion = new CaseDbSchemaVersionNumber(majorVersion, minorVersion);
3118  if (dbSchemaVersion.equals(CURRENT_DB_SCHEMA_VERSION)) {
3119  logger.log(Level.INFO, "Central Repository is up to date");
3120  return;
3121  }
3122 
3123  // Update from 1.0 to 1.1
3124  if (dbSchemaVersion.compareTo(new CaseDbSchemaVersionNumber(1, 1)) < 0) {
3125  statement.execute("ALTER TABLE reference_sets ADD COLUMN known_status INTEGER;"); //NON-NLS
3126  statement.execute("ALTER TABLE reference_sets ADD COLUMN read_only BOOLEAN;"); //NON-NLS
3127  statement.execute("ALTER TABLE reference_sets ADD COLUMN type INTEGER;"); //NON-NLS
3128 
3129  // There's an outide chance that the user has already made an organization with the default name,
3130  // and the default org being missing will not impact any database operations, so continue on
3131  // regardless of whether this succeeds.
3132  EamDbUtil.insertDefaultOrganization(conn);
3133  }
3134 
3135  if (!updateSchemaVersion(conn)) {
3136  throw new EamDbException("Error updating schema version");
3137  }
3138 
3139  conn.commit();
3140  logger.log(Level.INFO, "Central Repository upgraded to version " + CURRENT_DB_SCHEMA_VERSION);
3141  } catch (SQLException | EamDbException ex) {
3142  try {
3143  if (conn != null) {
3144  conn.rollback();
3145  }
3146  } catch (SQLException ex2) {
3147  logger.log(Level.SEVERE, "Database rollback failed", ex2);
3148  }
3149  throw ex;
3150  } finally {
3151  EamDbUtil.closeResultSet(resultSet);
3152  EamDbUtil.closeStatement(statement);
3153  EamDbUtil.closeConnection(conn);
3154  }
3155  }
3156 
3157 }
org.sleuthkit.autopsy.centralrepository.datamodel.EamDbUtil.correlationTypeToInstanceTableName
static String correlationTypeToInstanceTableName(CorrelationAttributeInstance.Type type)
Definition: EamDbUtil.java:349
org.sleuthkit.autopsy.healthmonitor.HealthMonitor.getTimingMetric
static TimingMetric getTimingMetric(String name)
Definition: HealthMonitor.java:324
org.sleuthkit.autopsy.centralrepository.datamodel.CorrelationAttributeNormalizer.normalize
static String normalize(CorrelationAttributeInstance.Type attributeType, String data)
Definition: CorrelationAttributeNormalizer.java:46
org.sleuthkit.autopsy.centralrepository.datamodel.EamDb.getCorrelationTypeById
CorrelationAttributeInstance.Type getCorrelationTypeById(int typeId)
org.sleuthkit.autopsy.healthmonitor.HealthMonitor.submitTimingMetric
static void submitTimingMetric(TimingMetric metric)
Definition: HealthMonitor.java:338
org.sleuthkit.autopsy.coreutils.Logger.getLogger
synchronized static Logger getLogger(String name)
Definition: Logger.java:124
org.sleuthkit.autopsy.centralrepository.datamodel.EamDbUtil.correlationTypeToReferenceTableName
static String correlationTypeToReferenceTableName(CorrelationAttributeInstance.Type type)
Definition: EamDbUtil.java:360

Copyright © 2012-2018 Basis Technology. Generated on: Thu Oct 4 2018
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.