19 package org.sleuthkit.autopsy.centralrepository.datamodel;
21 import java.sql.Connection;
22 import java.sql.ResultSet;
23 import java.sql.SQLException;
24 import java.sql.Statement;
25 import java.util.Arrays;
26 import java.util.List;
28 import java.util.concurrent.locks.ReentrantReadWriteLock;
29 import java.util.logging.Level;
30 import org.apache.commons.dbcp2.BasicDataSource;
41 final class SqliteEamDb
extends AbstractSqlEamDb {
43 private final static Logger LOGGER = Logger.getLogger(SqliteEamDb.class.getName());
45 private static SqliteEamDb instance;
47 private BasicDataSource connectionPool = null;
49 private final SqliteEamDbSettings dbSettings;
53 private final ReentrantReadWriteLock rwLock =
new ReentrantReadWriteLock(
true);
63 public synchronized static SqliteEamDb getInstance() throws EamDbException {
64 if (instance == null) {
65 instance =
new SqliteEamDb();
77 private SqliteEamDb() throws EamDbException {
78 dbSettings =
new SqliteEamDbSettings();
79 bulkArtifactsThreshold = dbSettings.getBulkThreshold();
83 public void shutdownConnections() throws EamDbException {
86 if (null != connectionPool) {
87 connectionPool.close();
88 connectionPool = null;
92 }
catch (SQLException ex) {
93 throw new EamDbException(
"Failed to close existing database connections.", ex);
98 public void updateSettings() {
100 dbSettings.loadSettings();
101 bulkArtifactsThreshold = dbSettings.getBulkThreshold();
106 public void saveSettings() {
107 synchronized (
this) {
108 dbSettings.saveSettings();
113 public void reset() throws EamDbException {
115 acquireExclusiveLock();
117 Connection conn = connect();
121 Statement dropContent = conn.createStatement();
122 dropContent.executeUpdate(
"DELETE FROM organizations");
123 dropContent.executeUpdate(
"DELETE FROM cases");
124 dropContent.executeUpdate(
"DELETE FROM data_sources");
125 dropContent.executeUpdate(
"DELETE FROM reference_sets");
126 dropContent.executeUpdate(
"DELETE FROM artifact_types");
127 dropContent.executeUpdate(
"DELETE FROM db_info");
129 String instancesTemplate =
"DELETE FROM %s_instances";
130 String referencesTemplate =
"DELETE FROM global_files";
131 for (CorrelationAttributeInstance.Type type : defaultCorrelationTypes) {
132 dropContent.executeUpdate(String.format(instancesTemplate, type.getDbTableName()));
134 if (type.getId() == CorrelationAttributeInstance.FILES_TYPE_ID) {
135 dropContent.executeUpdate(String.format(referencesTemplate, type.getDbTableName()));
139 dropContent.executeUpdate(
"VACUUM");
140 }
catch (SQLException ex) {
141 LOGGER.log(Level.WARNING,
"Failed to reset database.", ex);
143 EamDbUtil.closeConnection(conn);
146 dbSettings.insertDefaultDatabaseContent();
148 releaseExclusiveLock();
156 private void setupConnectionPool(
boolean foreignKeysEnabled)
throws EamDbException {
158 if (dbSettings.dbFileExists() ==
false) {
159 throw new EamDbException(
"Central repository database missing");
162 connectionPool =
new BasicDataSource();
163 connectionPool.setDriverClassName(dbSettings.getDriver());
164 connectionPool.setUrl(dbSettings.getConnectionURL());
167 connectionPool.setInitialSize(50);
168 connectionPool.setMaxTotal(-1);
169 connectionPool.setMaxIdle(-1);
170 connectionPool.setMaxWaitMillis(1000);
171 connectionPool.setValidationQuery(dbSettings.getValidationQuery());
172 if (foreignKeysEnabled) {
173 connectionPool.setConnectionInitSqls(Arrays.asList(
"PRAGMA foreign_keys = ON"));
175 connectionPool.setConnectionInitSqls(Arrays.asList(
"PRAGMA foreign_keys = OFF"));
189 protected Connection connect(
boolean foreignKeys)
throws EamDbException {
190 synchronized (
this) {
191 if (!EamDb.isEnabled()) {
192 throw new EamDbException(
"Central Repository module is not enabled");
194 if (connectionPool == null) {
195 setupConnectionPool(foreignKeys);
198 return connectionPool.getConnection();
199 }
catch (SQLException ex) {
200 throw new EamDbException(
"Error getting connection from connection pool.", ex);
213 protected Connection connect() throws EamDbException {
214 return connect(
true);
218 protected String getConflictClause() {
232 public void newDbInfo(String name, String value)
throws EamDbException {
234 acquireExclusiveLock();
235 super.newDbInfo(name, value);
237 releaseExclusiveLock();
251 public String getDbInfo(String name)
throws EamDbException {
254 return super.getDbInfo(name);
269 public void updateDbInfo(String name, String value)
throws EamDbException {
271 acquireExclusiveLock();
272 super.updateDbInfo(name, value);
274 releaseExclusiveLock();
284 public CorrelationCase newCase(Case autopsyCase)
throws EamDbException {
286 acquireExclusiveLock();
287 return super.newCase(autopsyCase);
289 releaseExclusiveLock();
294 public void addDataSourceObjectId(
int rowId,
long dataSourceObjectId)
throws EamDbException {
296 acquireExclusiveLock();
297 super.addDataSourceObjectId(rowId, dataSourceObjectId);
299 releaseExclusiveLock();
311 public CorrelationCase newCase(CorrelationCase eamCase)
throws EamDbException {
313 acquireExclusiveLock();
314 return super.newCase(eamCase);
316 releaseExclusiveLock();
326 public void updateCase(CorrelationCase eamCase)
throws EamDbException {
328 acquireExclusiveLock();
329 super.updateCase(eamCase);
331 releaseExclusiveLock();
343 public CorrelationCase getCaseByUUID(String caseUUID)
throws EamDbException {
346 return super.getCaseByUUID(caseUUID);
360 public CorrelationCase getCaseById(
int caseId)
throws EamDbException {
363 return super.getCaseById(caseId);
376 public List<CorrelationCase> getCases() throws EamDbException {
379 return super.getCases();
391 public CorrelationDataSource newDataSource(CorrelationDataSource eamDataSource)
throws EamDbException {
393 acquireExclusiveLock();
394 return super.newDataSource(eamDataSource);
396 releaseExclusiveLock();
410 public CorrelationDataSource getDataSource(CorrelationCase correlationCase, Long caseDbDataSourceId)
throws EamDbException {
413 return super.getDataSource(correlationCase, caseDbDataSourceId);
429 public CorrelationDataSource getDataSourceById(CorrelationCase correlationCase,
int dataSourceId)
throws EamDbException {
432 return super.getDataSourceById(correlationCase, dataSourceId);
444 public List<CorrelationDataSource> getDataSources() throws EamDbException {
447 return super.getDataSources();
462 public void updateDataSourceName(CorrelationDataSource eamDataSource, String newName)
throws EamDbException {
464 acquireExclusiveLock();
465 super.updateDataSourceName(eamDataSource, newName);
467 releaseExclusiveLock();
477 public void updateDataSourceMd5Hash(CorrelationDataSource eamDataSource)
throws EamDbException {
479 acquireExclusiveLock();
480 super.updateDataSourceMd5Hash(eamDataSource);
482 releaseExclusiveLock();
492 public void updateDataSourceSha1Hash(CorrelationDataSource eamDataSource)
throws EamDbException {
494 acquireExclusiveLock();
495 super.updateDataSourceSha1Hash(eamDataSource);
497 releaseExclusiveLock();
508 public void updateDataSourceSha256Hash(CorrelationDataSource eamDataSource)
throws EamDbException {
510 acquireExclusiveLock();
511 super.updateDataSourceSha256Hash(eamDataSource);
513 releaseExclusiveLock();
524 public void addArtifactInstance(CorrelationAttributeInstance eamArtifact)
throws EamDbException {
526 acquireExclusiveLock();
527 super.addArtifactInstance(eamArtifact);
529 releaseExclusiveLock();
534 public List<CorrelationAttributeInstance> getArtifactInstancesByTypeValue(CorrelationAttributeInstance.Type aType, String value)
throws EamDbException, CorrelationAttributeNormalizationException {
537 return super.getArtifactInstancesByTypeValue(aType, value);
544 public List<CorrelationAttributeInstance> getArtifactInstancesByTypeValues(CorrelationAttributeInstance.Type aType, List<String> values)
throws EamDbException, CorrelationAttributeNormalizationException {
547 return super.getArtifactInstancesByTypeValues(aType, values);
554 public List<CorrelationAttributeInstance> getArtifactInstancesByTypeValuesAndCases(CorrelationAttributeInstance.Type aType, List<String> values, List<Integer> caseIds)
throws EamDbException, CorrelationAttributeNormalizationException {
557 return super.getArtifactInstancesByTypeValuesAndCases(aType, values, caseIds);
576 public Long getCountArtifactInstancesByTypeValue(CorrelationAttributeInstance.Type aType, String value)
throws EamDbException, CorrelationAttributeNormalizationException {
579 return super.getCountArtifactInstancesByTypeValue(aType, value);
586 public int getFrequencyPercentage(CorrelationAttributeInstance corAttr)
throws EamDbException, CorrelationAttributeNormalizationException {
589 return super.getFrequencyPercentage(corAttr);
608 public Long getCountUniqueCaseDataSourceTuplesHavingTypeValue(CorrelationAttributeInstance.Type aType, String value)
throws EamDbException, CorrelationAttributeNormalizationException {
611 return super.getCountUniqueCaseDataSourceTuplesHavingTypeValue(aType, value);
618 public Long getCountUniqueDataSources() throws EamDbException {
621 return super.getCountUniqueDataSources();
639 public Long getCountArtifactInstancesByCaseDataSource(CorrelationDataSource correlationDataSource)
throws EamDbException {
642 return super.getCountArtifactInstancesByCaseDataSource(correlationDataSource);
653 public void commitAttributeInstancesBulk() throws EamDbException {
655 acquireExclusiveLock();
656 super.commitAttributeInstancesBulk();
658 releaseExclusiveLock();
666 public void bulkInsertCases(List<CorrelationCase> cases)
throws EamDbException {
668 acquireExclusiveLock();
669 super.bulkInsertCases(cases);
671 releaseExclusiveLock();
686 public void setAttributeInstanceKnownStatus(CorrelationAttributeInstance eamArtifact, TskData.FileKnown knownStatus) throws EamDbException {
688 acquireExclusiveLock();
689 super.setAttributeInstanceKnownStatus(eamArtifact, knownStatus);
691 releaseExclusiveLock();
704 public Long getCountArtifactInstancesKnownBad(CorrelationAttributeInstance.Type aType, String value)
throws EamDbException, CorrelationAttributeNormalizationException {
707 return super.getCountArtifactInstancesKnownBad(aType, value);
726 public List<String> getListCasesHavingArtifactInstancesKnownBad(CorrelationAttributeInstance.Type aType, String value)
throws EamDbException, CorrelationAttributeNormalizationException {
729 return super.getListCasesHavingArtifactInstancesKnownBad(aType, value);
743 public void deleteReferenceSet(
int referenceSetID)
throws EamDbException {
745 acquireExclusiveLock();
746 super.deleteReferenceSet(referenceSetID);
748 releaseExclusiveLock();
762 public boolean isValueInReferenceSet(String value,
int referenceSetID,
int correlationTypeID)
throws EamDbException, CorrelationAttributeNormalizationException {
765 return super.isValueInReferenceSet(value, referenceSetID, correlationTypeID);
780 public void processInstanceTable(CorrelationAttributeInstance.Type type, InstanceTableCallback instanceTableCallback)
throws EamDbException {
783 super.processInstanceTable(type, instanceTableCallback);
798 public void processInstanceTableWhere(CorrelationAttributeInstance.Type type, String whereClause, InstanceTableCallback instanceTableCallback)
throws EamDbException {
801 super.processInstanceTableWhere(type, whereClause, instanceTableCallback);
820 public boolean referenceSetExists(String referenceSetName, String version)
throws EamDbException {
823 return super.referenceSetExists(referenceSetName, version);
838 public boolean isArtifactKnownBadByReference(CorrelationAttributeInstance.Type aType, String value)
throws EamDbException, CorrelationAttributeNormalizationException {
841 return super.isArtifactKnownBadByReference(aType, value);
857 public EamOrganization newOrganization(EamOrganization eamOrg)
throws EamDbException {
859 acquireExclusiveLock();
860 return super.newOrganization(eamOrg);
862 releaseExclusiveLock();
874 public List<EamOrganization> getOrganizations() throws EamDbException {
877 return super.getOrganizations();
893 public EamOrganization getOrganizationByID(
int orgID)
throws EamDbException {
896 return super.getOrganizationByID(orgID);
903 public void updateOrganization(EamOrganization updatedOrganization)
throws EamDbException {
905 acquireExclusiveLock();
906 super.updateOrganization(updatedOrganization);
908 releaseExclusiveLock();
913 public void deleteOrganization(EamOrganization organizationToDelete)
throws EamDbException {
915 acquireExclusiveLock();
916 super.deleteOrganization(organizationToDelete);
918 releaseExclusiveLock();
932 public int newReferenceSet(EamGlobalSet eamGlobalSet)
throws EamDbException {
934 acquireExclusiveLock();
935 return super.newReferenceSet(eamGlobalSet);
937 releaseExclusiveLock();
951 public EamGlobalSet getReferenceSetByID(
int referenceSetID)
throws EamDbException {
954 return super.getReferenceSetByID(referenceSetID);
970 public List<EamGlobalSet> getAllReferenceSets(CorrelationAttributeInstance.Type correlationType) throws EamDbException {
973 return super.getAllReferenceSets(correlationType);
989 public void addReferenceInstance(EamGlobalFileInstance eamGlobalFileInstance, CorrelationAttributeInstance.Type correlationType) throws EamDbException {
991 acquireExclusiveLock();
992 super.addReferenceInstance(eamGlobalFileInstance, correlationType);
994 releaseExclusiveLock();
1004 public void bulkInsertReferenceTypeEntries(Set<EamGlobalFileInstance> globalInstances, CorrelationAttributeInstance.Type contentType) throws EamDbException {
1006 acquireExclusiveLock();
1007 super.bulkInsertReferenceTypeEntries(globalInstances, contentType);
1009 releaseExclusiveLock();
1024 public List<EamGlobalFileInstance> getReferenceInstancesByTypeValue(CorrelationAttributeInstance.Type aType, String aValue)
throws EamDbException, CorrelationAttributeNormalizationException {
1026 acquireSharedLock();
1027 return super.getReferenceInstancesByTypeValue(aType, aValue);
1029 releaseSharedLock();
1043 public int newCorrelationType(CorrelationAttributeInstance.Type newType) throws EamDbException {
1045 acquireExclusiveLock();
1046 return super.newCorrelationType(newType);
1048 releaseExclusiveLock();
1062 public List<CorrelationAttributeInstance.Type> getDefinedCorrelationTypes() throws EamDbException {
1064 acquireSharedLock();
1065 return super.getDefinedCorrelationTypes();
1067 releaseSharedLock();
1081 public List<CorrelationAttributeInstance.Type> getEnabledCorrelationTypes() throws EamDbException {
1083 acquireSharedLock();
1084 return super.getEnabledCorrelationTypes();
1086 releaseSharedLock();
1100 public List<CorrelationAttributeInstance.Type> getSupportedCorrelationTypes() throws EamDbException {
1102 acquireSharedLock();
1103 return super.getSupportedCorrelationTypes();
1105 releaseSharedLock();
1117 public void updateCorrelationType(CorrelationAttributeInstance.Type aType) throws EamDbException {
1119 acquireExclusiveLock();
1120 super.updateCorrelationType(aType);
1122 releaseExclusiveLock();
1136 public CorrelationAttributeInstance.Type getCorrelationTypeById(
int typeId)
throws EamDbException {
1138 acquireSharedLock();
1139 return super.getCorrelationTypeById(typeId);
1141 releaseSharedLock();
1151 public void upgradeSchema() throws EamDbException, SQLException, IncompatibleCentralRepoException {
1153 acquireExclusiveLock();
1154 super.upgradeSchema();
1156 releaseExclusiveLock();
1172 public CoordinationService.Lock getExclusiveMultiUserDbLock() throws EamDbException {
1182 private void acquireExclusiveLock() {
1183 rwLock.writeLock().lock();
1191 private void releaseExclusiveLock() {
1192 rwLock.writeLock().unlock();
1200 private void acquireSharedLock() {
1201 rwLock.readLock().lock();
1209 private void releaseSharedLock() {
1210 rwLock.readLock().unlock();
1214 boolean doesColumnExist(Connection conn, String tableName, String columnName)
throws SQLException {
1215 final String tableInfoQueryTemplate =
"PRAGMA table_info(%s)";
1216 ResultSet resultSet = null;
1217 Statement statement = null;
1218 boolean columnExists =
false;
1220 statement = conn.createStatement();
1221 resultSet = statement.executeQuery(String.format(tableInfoQueryTemplate, tableName));
1222 while (resultSet.next()) {
1224 if (resultSet.getString(2).equals(columnName)) {
1225 columnExists =
true;
1230 EamDbUtil.closeResultSet(resultSet);
1231 EamDbUtil.closeStatement(statement);
1233 return columnExists;