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.concurrent.TimeUnit;
26 import java.util.logging.Level;
27 import org.apache.commons.dbcp2.BasicDataSource;
35 final class PostgresEamDb
extends AbstractSqlEamDb {
37 private final static Logger LOGGER = Logger.
getLogger(PostgresEamDb.class.getName());
39 private final static String CONFLICT_CLAUSE =
"ON CONFLICT DO NOTHING";
41 private static PostgresEamDb instance;
43 private static final int CONN_POOL_SIZE = 10;
44 private BasicDataSource connectionPool = null;
46 private final PostgresEamDbSettings dbSettings;
56 public synchronized static PostgresEamDb getInstance() throws EamDbException {
57 if (instance == null) {
58 instance =
new PostgresEamDb();
70 private PostgresEamDb() throws EamDbException {
71 dbSettings =
new PostgresEamDbSettings();
72 bulkArtifactsThreshold = dbSettings.getBulkThreshold();
76 public void shutdownConnections() throws EamDbException {
79 if (connectionPool != null) {
80 connectionPool.close();
81 connectionPool = null;
85 }
catch (SQLException ex) {
86 throw new EamDbException(
"Failed to close existing database connections.", ex);
91 public void updateSettings() {
93 dbSettings.loadSettings();
94 bulkArtifactsThreshold = dbSettings.getBulkThreshold();
99 public void saveSettings() {
100 synchronized (
this) {
101 dbSettings.saveSettings();
106 public void reset() throws EamDbException {
107 Connection conn = connect();
110 Statement dropContent = conn.createStatement();
111 dropContent.executeUpdate(
"TRUNCATE TABLE organizations RESTART IDENTITY CASCADE");
112 dropContent.executeUpdate(
"TRUNCATE TABLE cases RESTART IDENTITY CASCADE");
113 dropContent.executeUpdate(
"TRUNCATE TABLE data_sources RESTART IDENTITY CASCADE");
114 dropContent.executeUpdate(
"TRUNCATE TABLE reference_sets RESTART IDENTITY CASCADE");
115 dropContent.executeUpdate(
"TRUNCATE TABLE correlation_types RESTART IDENTITY CASCADE");
116 dropContent.executeUpdate(
"TRUNCATE TABLE db_info RESTART IDENTITY CASCADE");
118 String instancesTemplate =
"TRUNCATE TABLE %s_instances RESTART IDENTITY CASCADE";
119 String referencesTemplate =
"TRUNCATE TABLE reference_%s RESTART IDENTITY CASCADE";
120 for (CorrelationAttributeInstance.Type type : defaultCorrelationTypes) {
121 dropContent.executeUpdate(String.format(instancesTemplate, type.getDbTableName()));
123 if (type.getId() == CorrelationAttributeInstance.FILES_TYPE_ID) {
124 dropContent.executeUpdate(String.format(referencesTemplate, type.getDbTableName()));
127 }
catch (SQLException ex) {
128 LOGGER.log(Level.WARNING,
"Failed to reset database.", ex);
130 EamDbUtil.closeConnection(conn);
133 dbSettings.insertDefaultDatabaseContent();
140 private void setupConnectionPool() throws EamDbException {
141 connectionPool =
new BasicDataSource();
142 connectionPool.setUsername(dbSettings.getUserName());
143 connectionPool.setPassword(dbSettings.getPassword());
144 connectionPool.setDriverClassName(dbSettings.getDriver());
146 StringBuilder connectionURL =
new StringBuilder();
147 connectionURL.append(dbSettings.getJDBCBaseURI());
148 connectionURL.append(dbSettings.getHost());
149 connectionURL.append(
":");
150 connectionURL.append(dbSettings.getPort());
151 connectionURL.append(
"/");
152 connectionURL.append(dbSettings.getDbName());
154 connectionPool.setUrl(connectionURL.toString());
155 connectionPool.setUsername(dbSettings.getUserName());
156 connectionPool.setPassword(dbSettings.getPassword());
159 connectionPool.setInitialSize(5);
160 connectionPool.setMaxIdle(CONN_POOL_SIZE);
161 connectionPool.setValidationQuery(dbSettings.getValidationQuery());
174 protected Connection connect(
boolean foreignKeys)
throws EamDbException {
187 protected Connection connect() throws EamDbException {
188 synchronized (
this) {
189 if (!EamDb.isEnabled()) {
190 throw new EamDbException(
"Central Repository module is not enabled");
193 if (connectionPool == null) {
194 setupConnectionPool();
198 return connectionPool.getConnection();
199 }
catch (SQLException ex) {
200 throw new EamDbException(
"Error getting connection from connection pool.", ex);
205 protected String getConflictClause() {
206 return CONFLICT_CLAUSE;
221 public CoordinationService.Lock getExclusiveMultiUserDbLock() throws EamDbException {
224 if (!UserPreferences.getIsMultiUserModeEnabled()) {
228 String databaseNodeName = dbSettings.getHost() +
"_" + dbSettings.getDbName();
229 CoordinationService.Lock lock = CoordinationService.getInstance().tryGetExclusiveLock(CoordinationService.CategoryNode.CENTRAL_REPO, databaseNodeName, 5, TimeUnit.MINUTES);
234 throw new EamDbException(
"Error acquiring database lock");
235 }
catch (InterruptedException ex) {
236 throw new EamDbException(
"Error acquiring database lock");
237 }
catch (CoordinationService.CoordinationServiceException ex) {
244 boolean doesColumnExist(Connection conn, String tableName, String columnName)
throws SQLException {
245 final String objectIdColumnExistsTemplate =
"SELECT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='%s' AND column_name='%s')";
246 ResultSet resultSet = null;
247 Statement statement = null;
248 boolean columnExists =
false;
250 statement = conn.createStatement();
251 resultSet = statement.executeQuery(String.format(objectIdColumnExistsTemplate, tableName, columnName));
252 if (resultSet.next()) {
253 columnExists = resultSet.getBoolean(1);
256 EamDbUtil.closeResultSet(resultSet);
257 EamDbUtil.closeStatement(statement);
synchronized static Logger getLogger(String name)