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;
31 import org.openide.util.NbBundle.Messages;
42 final class SqliteCentralRepo
extends RdbmsCentralRepo {
44 private final static Logger LOGGER = Logger.getLogger(SqliteCentralRepo.class.getName());
46 private static SqliteCentralRepo instance;
48 private BasicDataSource connectionPool = null;
50 private final SqliteCentralRepoSettings dbSettings;
54 private final ReentrantReadWriteLock rwLock =
new ReentrantReadWriteLock(
true);
64 public synchronized static SqliteCentralRepo getInstance() throws CentralRepoException {
65 if (instance == null) {
66 instance =
new SqliteCentralRepo();
78 private SqliteCentralRepo() throws CentralRepoException {
79 dbSettings =
new SqliteCentralRepoSettings();
80 bulkArtifactsThreshold = dbSettings.getBulkThreshold();
84 public void shutdownConnections() throws CentralRepoException {
87 if (null != connectionPool) {
88 connectionPool.close();
89 connectionPool = null;
93 }
catch (SQLException ex) {
94 throw new CentralRepoException(
"Failed to close existing database connections.", ex);
99 public void updateSettings() {
100 synchronized (
this) {
101 dbSettings.loadSettings();
102 bulkArtifactsThreshold = dbSettings.getBulkThreshold();
107 public void saveSettings() {
108 synchronized (
this) {
109 dbSettings.saveSettings();
114 public void reset() throws CentralRepoException {
116 acquireExclusiveLock();
118 Connection conn = connect();
122 Statement dropContent = conn.createStatement();
123 dropContent.executeUpdate(
"DELETE FROM organizations");
124 dropContent.executeUpdate(
"DELETE FROM cases");
125 dropContent.executeUpdate(
"DELETE FROM data_sources");
126 dropContent.executeUpdate(
"DELETE FROM reference_sets");
127 dropContent.executeUpdate(
"DELETE FROM artifact_types");
128 dropContent.executeUpdate(
"DELETE FROM db_info");
130 String instancesTemplate =
"DELETE FROM %s_instances";
131 String referencesTemplate =
"DELETE FROM global_files";
132 for (CorrelationAttributeInstance.Type type : defaultCorrelationTypes) {
133 dropContent.executeUpdate(String.format(instancesTemplate, type.getDbTableName()));
135 if (type.getId() == CorrelationAttributeInstance.FILES_TYPE_ID) {
136 dropContent.executeUpdate(String.format(referencesTemplate, type.getDbTableName()));
140 dropContent.executeUpdate(
"VACUUM");
141 }
catch (SQLException ex) {
142 LOGGER.log(Level.WARNING,
"Failed to reset database.", ex);
144 CentralRepoDbUtil.closeConnection(conn);
147 RdbmsCentralRepoFactory centralRepoSchemaFactory =
new RdbmsCentralRepoFactory(CentralRepoPlatforms.SQLITE, dbSettings);
148 centralRepoSchemaFactory.insertDefaultDatabaseContent();
150 releaseExclusiveLock();
158 @Messages({
"SqliteEamDb.databaseMissing.message=Central repository database missing"})
159 private void setupConnectionPool(
boolean foreignKeysEnabled)
throws CentralRepoException {
161 if (dbSettings.dbFileExists() ==
false) {
162 throw new CentralRepoException(
"Central repository database missing", Bundle.SqliteEamDb_databaseMissing_message());
165 connectionPool =
new BasicDataSource();
166 connectionPool.setDriverClassName(dbSettings.getDriver());
167 connectionPool.setUrl(dbSettings.getConnectionURL());
170 connectionPool.setInitialSize(50);
171 connectionPool.setMaxTotal(-1);
172 connectionPool.setMaxIdle(-1);
173 connectionPool.setMaxWaitMillis(1000);
174 connectionPool.setValidationQuery(dbSettings.getValidationQuery());
175 if (foreignKeysEnabled) {
176 connectionPool.setConnectionInitSqls(Arrays.asList(
"PRAGMA foreign_keys = ON"));
178 connectionPool.setConnectionInitSqls(Arrays.asList(
"PRAGMA foreign_keys = OFF"));
192 @Messages({
"SqliteEamDb.connectionFailedMessage.message=Error getting connection to database.",
193 "SqliteEamDb.centralRepositoryDisabled.message=Central Repository module is not enabled."})
195 protected Connection connect(
boolean foreignKeys)
throws CentralRepoException {
196 synchronized (
this) {
197 if (!CentralRepository.isEnabled()) {
198 throw new CentralRepoException(
"Central repository database missing", Bundle.SqliteEamDb_centralRepositoryDisabled_message());
200 if (connectionPool == null) {
201 setupConnectionPool(foreignKeys);
204 return connectionPool.getConnection();
205 }
catch (SQLException ex) {
206 throw new CentralRepoException(
"Error getting connection from connection pool.", Bundle.SqliteEamDb_connectionFailedMessage_message(), ex);
220 protected Connection connect() throws CentralRepoException {
221 return connect(
true);
225 protected String getConflictClause() {
231 protected Connection getEphemeralConnection() {
232 return this.dbSettings.getEphemeralConnection();
244 public void newDbInfo(String name, String value)
throws CentralRepoException {
246 acquireExclusiveLock();
247 super.newDbInfo(name, value);
249 releaseExclusiveLock();
263 public String getDbInfo(String name)
throws CentralRepoException {
266 return super.getDbInfo(name);
281 public void updateDbInfo(String name, String value)
throws CentralRepoException {
283 acquireExclusiveLock();
284 super.updateDbInfo(name, value);
286 releaseExclusiveLock();
296 public CorrelationCase newCase(Case autopsyCase)
throws CentralRepoException {
298 acquireExclusiveLock();
299 return super.newCase(autopsyCase);
301 releaseExclusiveLock();
306 public void addDataSourceObjectId(
int rowId,
long dataSourceObjectId)
throws CentralRepoException {
308 acquireExclusiveLock();
309 super.addDataSourceObjectId(rowId, dataSourceObjectId);
311 releaseExclusiveLock();
323 public CorrelationCase newCase(CorrelationCase eamCase)
throws CentralRepoException {
325 acquireExclusiveLock();
326 return super.newCase(eamCase);
328 releaseExclusiveLock();
338 public void updateCase(CorrelationCase eamCase)
throws CentralRepoException {
340 acquireExclusiveLock();
341 super.updateCase(eamCase);
343 releaseExclusiveLock();
355 public CorrelationCase getCaseByUUID(String caseUUID)
throws CentralRepoException {
358 return super.getCaseByUUID(caseUUID);
372 public CorrelationCase getCaseById(
int caseId)
throws CentralRepoException {
375 return super.getCaseById(caseId);
388 public List<CorrelationCase> getCases() throws CentralRepoException {
391 return super.getCases();
403 public CorrelationDataSource newDataSource(CorrelationDataSource eamDataSource)
throws CentralRepoException {
405 acquireExclusiveLock();
406 return super.newDataSource(eamDataSource);
408 releaseExclusiveLock();
422 public CorrelationDataSource getDataSource(CorrelationCase correlationCase, Long caseDbDataSourceId)
throws CentralRepoException {
425 return super.getDataSource(correlationCase, caseDbDataSourceId);
441 public CorrelationDataSource getDataSourceById(CorrelationCase correlationCase,
int dataSourceId)
throws CentralRepoException {
444 return super.getDataSourceById(correlationCase, dataSourceId);
456 public List<CorrelationDataSource> getDataSources() throws CentralRepoException {
459 return super.getDataSources();
474 public void updateDataSourceName(CorrelationDataSource eamDataSource, String newName)
throws CentralRepoException {
476 acquireExclusiveLock();
477 super.updateDataSourceName(eamDataSource, newName);
479 releaseExclusiveLock();
489 public void updateDataSourceMd5Hash(CorrelationDataSource eamDataSource)
throws CentralRepoException {
491 acquireExclusiveLock();
492 super.updateDataSourceMd5Hash(eamDataSource);
494 releaseExclusiveLock();
504 public void updateDataSourceSha1Hash(CorrelationDataSource eamDataSource)
throws CentralRepoException {
506 acquireExclusiveLock();
507 super.updateDataSourceSha1Hash(eamDataSource);
509 releaseExclusiveLock();
520 public void updateDataSourceSha256Hash(CorrelationDataSource eamDataSource)
throws CentralRepoException {
522 acquireExclusiveLock();
523 super.updateDataSourceSha256Hash(eamDataSource);
525 releaseExclusiveLock();
536 public void addArtifactInstance(CorrelationAttributeInstance eamArtifact)
throws CentralRepoException {
538 acquireExclusiveLock();
539 super.addArtifactInstance(eamArtifact);
541 releaseExclusiveLock();
546 public List<CorrelationAttributeInstance> getArtifactInstancesByTypeValue(CorrelationAttributeInstance.Type aType, String value)
throws CentralRepoException, CorrelationAttributeNormalizationException {
549 return super.getArtifactInstancesByTypeValue(aType, value);
556 public List<CorrelationAttributeInstance> getArtifactInstancesByTypeValues(CorrelationAttributeInstance.Type aType, List<String> values)
throws CentralRepoException, CorrelationAttributeNormalizationException {
559 return super.getArtifactInstancesByTypeValues(aType, values);
566 public List<CorrelationAttributeInstance> getArtifactInstancesByTypeValuesAndCases(CorrelationAttributeInstance.Type aType, List<String> values, List<Integer> caseIds)
throws CentralRepoException, CorrelationAttributeNormalizationException {
569 return super.getArtifactInstancesByTypeValuesAndCases(aType, values, caseIds);
588 public Long getCountArtifactInstancesByTypeValue(CorrelationAttributeInstance.Type aType, String value)
throws CentralRepoException, CorrelationAttributeNormalizationException {
591 return super.getCountArtifactInstancesByTypeValue(aType, value);
598 public int getFrequencyPercentage(CorrelationAttributeInstance corAttr)
throws CentralRepoException, CorrelationAttributeNormalizationException {
601 return super.getFrequencyPercentage(corAttr);
620 public Long getCountUniqueCaseDataSourceTuplesHavingTypeValue(CorrelationAttributeInstance.Type aType, String value)
throws CentralRepoException, CorrelationAttributeNormalizationException {
623 return super.getCountUniqueCaseDataSourceTuplesHavingTypeValue(aType, value);
630 public Long getCountCasesWithOtherInstances(CorrelationAttributeInstance instance)
throws CentralRepoException, CorrelationAttributeNormalizationException {
633 return super.getCountCasesWithOtherInstances(instance);
640 public Long getCountUniqueDataSources() throws CentralRepoException {
643 return super.getCountUniqueDataSources();
661 public Long getCountArtifactInstancesByCaseDataSource(CorrelationDataSource correlationDataSource)
throws CentralRepoException {
664 return super.getCountArtifactInstancesByCaseDataSource(correlationDataSource);
675 public void commitAttributeInstancesBulk() throws CentralRepoException {
677 acquireExclusiveLock();
678 super.commitAttributeInstancesBulk();
680 releaseExclusiveLock();
688 public void bulkInsertCases(List<CorrelationCase> cases)
throws CentralRepoException {
690 acquireExclusiveLock();
691 super.bulkInsertCases(cases);
693 releaseExclusiveLock();
708 public void setAttributeInstanceKnownStatus(CorrelationAttributeInstance eamArtifact, TskData.FileKnown knownStatus) throws CentralRepoException {
710 acquireExclusiveLock();
711 super.setAttributeInstanceKnownStatus(eamArtifact, knownStatus);
713 releaseExclusiveLock();
726 public Long getCountArtifactInstancesKnownBad(CorrelationAttributeInstance.Type aType, String value)
throws CentralRepoException, CorrelationAttributeNormalizationException {
729 return super.getCountArtifactInstancesKnownBad(aType, value);
748 public List<String> getListCasesHavingArtifactInstancesKnownBad(CorrelationAttributeInstance.Type aType, String value)
throws CentralRepoException, CorrelationAttributeNormalizationException {
751 return super.getListCasesHavingArtifactInstancesKnownBad(aType, value);
765 public void deleteReferenceSet(
int referenceSetID)
throws CentralRepoException {
767 acquireExclusiveLock();
768 super.deleteReferenceSet(referenceSetID);
770 releaseExclusiveLock();
784 public boolean isValueInReferenceSet(String value,
int referenceSetID,
int correlationTypeID)
throws CentralRepoException, CorrelationAttributeNormalizationException {
787 return super.isValueInReferenceSet(value, referenceSetID, correlationTypeID);
802 public void processInstanceTable(CorrelationAttributeInstance.Type type, InstanceTableCallback instanceTableCallback)
throws CentralRepoException {
805 super.processInstanceTable(type, instanceTableCallback);
820 public void processInstanceTableWhere(CorrelationAttributeInstance.Type type, String whereClause, InstanceTableCallback instanceTableCallback)
throws CentralRepoException {
823 super.processInstanceTableWhere(type, whereClause, instanceTableCallback);
838 public void processSelectClause(String selectClause, InstanceTableCallback instanceTableCallback)
throws CentralRepoException {
841 super.processSelectClause(selectClause, instanceTableCallback);
848 public void executeCommand(String sql, List<Object> params)
throws CentralRepoException {
850 acquireExclusiveLock();
851 super.executeCommand(sql, params);
853 releaseExclusiveLock();
858 public void executeQuery(String sql, List<Object> params, CentralRepositoryDbQueryCallback queryCallback)
throws CentralRepoException {
861 super.executeQuery(sql, params, queryCallback);
880 public boolean referenceSetExists(String referenceSetName, String version)
throws CentralRepoException {
883 return super.referenceSetExists(referenceSetName, version);
898 public boolean isArtifactKnownBadByReference(CorrelationAttributeInstance.Type aType, String value)
throws CentralRepoException, CorrelationAttributeNormalizationException {
901 return super.isArtifactKnownBadByReference(aType, value);
917 public CentralRepoOrganization newOrganization(CentralRepoOrganization eamOrg)
throws CentralRepoException {
919 acquireExclusiveLock();
920 return super.newOrganization(eamOrg);
922 releaseExclusiveLock();
934 public List<CentralRepoOrganization> getOrganizations() throws CentralRepoException {
937 return super.getOrganizations();
953 public CentralRepoOrganization getOrganizationByID(
int orgID)
throws CentralRepoException {
956 return super.getOrganizationByID(orgID);
963 public void updateOrganization(CentralRepoOrganization updatedOrganization)
throws CentralRepoException {
965 acquireExclusiveLock();
966 super.updateOrganization(updatedOrganization);
968 releaseExclusiveLock();
973 public void deleteOrganization(CentralRepoOrganization organizationToDelete)
throws CentralRepoException {
975 acquireExclusiveLock();
976 super.deleteOrganization(organizationToDelete);
978 releaseExclusiveLock();
992 public int newReferenceSet(CentralRepoFileSet eamGlobalSet)
throws CentralRepoException {
994 acquireExclusiveLock();
995 return super.newReferenceSet(eamGlobalSet);
997 releaseExclusiveLock();
1011 public CentralRepoFileSet getReferenceSetByID(
int referenceSetID)
throws CentralRepoException {
1013 acquireSharedLock();
1014 return super.getReferenceSetByID(referenceSetID);
1016 releaseSharedLock();
1030 public List<CentralRepoFileSet> getAllReferenceSets(CorrelationAttributeInstance.Type correlationType) throws CentralRepoException {
1032 acquireSharedLock();
1033 return super.getAllReferenceSets(correlationType);
1035 releaseSharedLock();
1049 public void addReferenceInstance(CentralRepoFileInstance eamGlobalFileInstance, CorrelationAttributeInstance.Type correlationType) throws CentralRepoException {
1051 acquireExclusiveLock();
1052 super.addReferenceInstance(eamGlobalFileInstance, correlationType);
1054 releaseExclusiveLock();
1064 public void bulkInsertReferenceTypeEntries(Set<CentralRepoFileInstance> globalInstances, CorrelationAttributeInstance.Type contentType) throws CentralRepoException {
1066 acquireExclusiveLock();
1067 super.bulkInsertReferenceTypeEntries(globalInstances, contentType);
1069 releaseExclusiveLock();
1084 public List<CentralRepoFileInstance> getReferenceInstancesByTypeValue(CorrelationAttributeInstance.Type aType, String aValue)
throws CentralRepoException, CorrelationAttributeNormalizationException {
1086 acquireSharedLock();
1087 return super.getReferenceInstancesByTypeValue(aType, aValue);
1089 releaseSharedLock();
1103 public int newCorrelationType(CorrelationAttributeInstance.Type newType) throws CentralRepoException {
1105 acquireExclusiveLock();
1106 return super.newCorrelationType(newType);
1108 releaseExclusiveLock();
1122 public List<CorrelationAttributeInstance.Type> getDefinedCorrelationTypes() throws CentralRepoException {
1124 acquireSharedLock();
1125 return super.getDefinedCorrelationTypes();
1127 releaseSharedLock();
1141 public List<CorrelationAttributeInstance.Type> getEnabledCorrelationTypes() throws CentralRepoException {
1143 acquireSharedLock();
1144 return super.getEnabledCorrelationTypes();
1146 releaseSharedLock();
1160 public List<CorrelationAttributeInstance.Type> getSupportedCorrelationTypes() throws CentralRepoException {
1162 acquireSharedLock();
1163 return super.getSupportedCorrelationTypes();
1165 releaseSharedLock();
1177 public void updateCorrelationType(CorrelationAttributeInstance.Type aType) throws CentralRepoException {
1179 acquireExclusiveLock();
1180 super.updateCorrelationType(aType);
1182 releaseExclusiveLock();
1196 public CorrelationAttributeInstance.Type getCorrelationTypeById(
int typeId)
throws CentralRepoException {
1198 acquireSharedLock();
1199 return super.getCorrelationTypeById(typeId);
1201 releaseSharedLock();
1211 public void upgradeSchema() throws CentralRepoException, SQLException, IncompatibleCentralRepoException {
1213 acquireExclusiveLock();
1214 super.upgradeSchema();
1216 releaseExclusiveLock();
1232 public CoordinationService.Lock getExclusiveMultiUserDbLock() throws CentralRepoException {
1242 private void acquireExclusiveLock() {
1243 rwLock.writeLock().lock();
1251 private void releaseExclusiveLock() {
1252 rwLock.writeLock().unlock();
1260 private void acquireSharedLock() {
1261 rwLock.readLock().lock();
1269 private void releaseSharedLock() {
1270 rwLock.readLock().unlock();
1274 boolean doesColumnExist(Connection conn, String tableName, String columnName)
throws SQLException {
1275 final String tableInfoQueryTemplate =
"PRAGMA table_info(%s)";
1276 ResultSet resultSet = null;
1277 Statement statement = null;
1278 boolean columnExists =
false;
1280 statement = conn.createStatement();
1281 resultSet = statement.executeQuery(String.format(tableInfoQueryTemplate, tableName));
1282 while (resultSet.next()) {
1284 if (resultSet.getString(2).equals(columnName)) {
1285 columnExists =
true;
1290 CentralRepoDbUtil.closeResultSet(resultSet);
1291 CentralRepoDbUtil.closeStatement(statement);
1293 return columnExists;