/** * Copyright (C) 2007 - 2016 52°North Initiative for Geospatial Open Source * Software GmbH * * This program is free software; you can redistribute it and/or modify it * under the terms of the GNU General Public License version 2 as published * by the Free Software Foundation. * * If the program is linked with libraries which are licensed under one of * the following licenses, the combination of the program with the linked * library is not considered a "derivative work" of the program: * * • Apache License, version 2.0 * • Apache Software License, version 1.0 * • GNU Lesser General Public License, version 3 * • Mozilla Public License, versions 1.0, 1.1 and 2.0 * • Common Development and Distribution License (CDDL), version 1.0 * * Therefore the distribution of the program linked with libraries licensed * under the aforementioned licenses, is permitted by the copyright holders * if the distribution is compliant with both the GNU General Public * License version 2 and the aforementioned licenses. * * This program is distributed in the hope that it will be useful, but * WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General * Public License for more details. */ package org.n52.wps.server.database; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.net.URI; import java.net.URISyntaxException; import java.nio.file.Files; import java.nio.file.Path; import java.nio.file.Paths; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.text.MessageFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.List; import java.util.Locale; import java.util.Properties; import java.util.Timer; import java.util.TimerTask; import java.util.zip.GZIPInputStream; import java.util.zip.GZIPOutputStream; import javax.naming.NamingException; import org.apache.commons.io.IOUtils; import org.n52.wps.ServerDocument; import org.n52.wps.commons.PropertyUtil; import org.n52.wps.commons.WPSConfig; import org.n52.wps.server.database.connection.ConnectionHandler; import org.n52.wps.server.database.connection.DefaultConnectionHandler; import org.n52.wps.server.database.connection.JNDIConnectionHandler; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.google.common.base.Joiner; /** *Uses the Postgres database to store and retrieve data. * @author isuftin (Ivan Suftin, USGS) */ public class PostgresDatabase extends AbstractDatabase { private static final Logger LOGGER = LoggerFactory.getLogger(PostgresDatabase.class); private static final String DEFAULT_ENCODING = "UTF-8"; private static final String KEY_DATABASE_ROOT = "org.n52.wps.server.database"; private static final String KEY_DATABASE_PATH = "path"; private static final String KEY_DATABASE_WIPE_ENABLED = "wipe.enabled"; private static final String KEY_DATABASE_WIPE_PERIOD = "wipe.period"; private static final String KEY_DATABASE_WIPE_THRESHOLD = "wipe.threshold"; private static final boolean DEFAULT_DATABASE_WIPE_ENABLED = true; private static final long DEFAULT_DATABASE_WIPE_PERIOD = 1000 * 60 * 60; // default to running once an hour private static final long DEFAULT_DATABASE_WIPE_THRESHOLD = 1000 * 60 * 60 * 24 * 7; // default to wipe things over a week old private static final String FILE_URI_PREFIX = "file://"; private static final String SUFFIX_GZIP = "gz"; private static final String DEFAULT_BASE_DIRECTORY = Joiner.on(File.separator).join(System.getProperty("java.io.tmpdir", "."), "Database", "Results"); private static final ServerDocument.Server server = WPSConfig.getInstance().getWPSConfig().getServer(); private static final int SELECTION_STRING_REQUEST_ID_PARAM_INDEX = 1; private static final int SELECTION_STRING_RESPONSE_COLUMN_INDEX = 1; private static final int SELECTION_STRING_RESPONSE_MIMETYPE_COLUMN_INDEX = 2; private static String connectionURL; private static Path BASE_DIRECTORY; private static PostgresDatabase instance; private static ConnectionHandler connectionHandler; private final static boolean SAVE_RESULTS_TO_DB = Boolean.parseBoolean(getDatabaseProperties("saveResultsToDB")); private static Timer wipeTimer; private static final String CREATE_RESULTS_TABLE_PSQL = "CREATE TABLE RESULTS (" + "REQUEST_ID VARCHAR(100) NOT NULL PRIMARY KEY, " + "REQUEST_DATE TIMESTAMP, " + "RESPONSE_TYPE VARCHAR(100), " + "RESPONSE TEXT, " + "RESPONSE_MIMETYPE VARCHAR(100))"; public static synchronized PostgresDatabase getInstance() { if (instance == null) { instance = new PostgresDatabase(); } return instance; } protected final Object storeResponseSerialNumberLock = new Object(); private final String DATABASE_NAME; private PostgresDatabase() { PropertyUtil propertyUtil = new PropertyUtil(server.getDatabase().getPropertyArray(), KEY_DATABASE_ROOT); String baseDirectoryPath = propertyUtil.extractString(KEY_DATABASE_PATH, DEFAULT_BASE_DIRECTORY); String dbName = getDatabaseProperties(PROPERTY_NAME_DATABASE_NAME); DATABASE_NAME = (null == dbName || "".equals(dbName.trim())) ? "wps" : dbName; try { Class.forName("org.postgresql.Driver"); initializeBaseDirectory(baseDirectoryPath); initializeConnectionHandler(); initializeResultsTable(); initializeDatabaseWiper(propertyUtil); } catch (IOException | SQLException | NamingException ex) { LOGGER.error("Error creating PostgresDatabase", ex); throw new RuntimeException("Error creating PostgresDatabase", ex); } catch (ClassNotFoundException ex) { LOGGER.error("The database class could not be loaded.", ex); throw new UnsupportedDatabaseException("The database class could not be loaded.", ex); } } private void initializeBaseDirectory(final String baseDirectoryPath) throws IOException { BASE_DIRECTORY = Paths.get(baseDirectoryPath); LOGGER.info("Using \"{}\" as base directory for results database", baseDirectoryPath); Files.createDirectories(BASE_DIRECTORY); } private void initializeDatabaseWiper(PropertyUtil propertyUtil) { if (propertyUtil.extractBoolean(KEY_DATABASE_WIPE_ENABLED, DEFAULT_DATABASE_WIPE_ENABLED)) { long periodMillis = propertyUtil.extractPeriodAsMillis(KEY_DATABASE_WIPE_PERIOD, DEFAULT_DATABASE_WIPE_PERIOD); long thresholdMillis = propertyUtil.extractPeriodAsMillis(KEY_DATABASE_WIPE_THRESHOLD, DEFAULT_DATABASE_WIPE_THRESHOLD); wipeTimer = new Timer(PostgresDatabase.class.getSimpleName() + " Postgres Wiper", true); wipeTimer.scheduleAtFixedRate(new PostgresDatabase.WipeTimerTask(thresholdMillis), 15000, periodMillis); LOGGER.info("Started {} Postgres wiper timer; period {} ms, threshold {} ms", new Object[]{DATABASE_NAME, periodMillis, thresholdMillis}); } else { wipeTimer = null; } } private void initializeConnectionHandler() throws SQLException, NamingException { String jndiName = getDatabaseProperties("jndiName"); if (null != jndiName) { connectionHandler = new JNDIConnectionHandler(jndiName); } else { connectionURL = "jdbc:postgresql:" + getDatabasePath() + "/" + DATABASE_NAME; LOGGER.debug("Database connection URL is: " + connectionURL); String username = getDatabaseProperties("username"); String password = getDatabaseProperties("password"); Properties props = new Properties(); props.setProperty("create", "true"); props.setProperty("user", username); props.setProperty("password", password); connectionHandler = new DefaultConnectionHandler(connectionURL, props); } } private void initializeResultsTable() throws SQLException { try (Connection connection = connectionHandler.getConnection(); ResultSet rs = getTables(connection)) { if (!rs.next()) { LOGGER.debug("Table RESULTS does not yet exist, creating it."); try (Statement st = connection.createStatement()) { st.executeUpdate(CREATE_RESULTS_TABLE_PSQL); } } } } @Override public String getConnectionURL() { return connectionURL; } @Override public Connection getConnection() { try { return connectionHandler.getConnection(); } catch (SQLException ex) { throw new RuntimeException("Unable to obtain connection to database!", ex); } } private ResultSet getTables(Connection connection) throws SQLException { return connection.getMetaData().getTables(null, null, "results", new String[]{"TABLE"}); } @Override public void insertRequest(String id, InputStream inputStream, boolean xml) { insertResultEntity(inputStream, "REQ_" + id, "ExecuteRequest", xml ? "text/xml" : "text/plain"); } @Override public String insertResponse(String id, InputStream inputStream) { return insertResultEntity(inputStream, id, "ExecuteResponse", "text/xml"); } @Override protected String insertResultEntity(InputStream stream, String id, String type, String mimeType) { boolean compressData = !SAVE_RESULTS_TO_DB; boolean proceed = true; String data = ""; synchronized (storeResponseSerialNumberLock) { if (!SAVE_RESULTS_TO_DB) { try { // The result contents won't be saved to the database, // only a pointer to the file system. I am therefore // going to GZip the data to save space data = writeInputStreamToDisk(id, stream, compressData); } catch (IOException ex) { LOGGER.error("Failed to write output data to disk", ex); proceed = false; } } if (proceed) { try (Connection connection = getConnection(); PreparedStatement insertStatement = connection.prepareStatement(insertionString)) { insertStatement.setString(INSERT_COLUMN_REQUEST_ID, id); insertStatement.setTimestamp(INSERT_COLUMN_REQUEST_DATE, new Timestamp(Calendar.getInstance().getTimeInMillis())); insertStatement.setString(INSERT_COLUMN_RESPONSE_TYPE, type); insertStatement.setString(INSERT_COLUMN_MIME_TYPE, mimeType); if (SAVE_RESULTS_TO_DB) { // This is implemented because we need to handle the case of SAVE_RESULTS_TO_DB = true. However, // this should not be used if you expect results to be large. // TODO- Remove and reimplement when setAsciiStream() has been properly implemented // @ https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc4/AbstractJdbc4Statement.java insertStatement.setString(INSERT_COLUMN_RESPONSE, IOUtils.toString(stream, DEFAULT_ENCODING)); } else { insertStatement.setString(INSERT_COLUMN_RESPONSE, data); } insertStatement.executeUpdate(); LOGGER.debug(MessageFormat.format("Inserted data into database with id of:{0}, type of: {1}, mimetype of: {2}", id, type, mimeType)); } catch (SQLException | IOException ex) { LOGGER.error(MessageFormat.format("Failed to insert data into database with id of:{0}, type of: {1}, mimetype of: {2}", id, type, mimeType), ex); } } } return generateRetrieveResultURL(id); } /** * Writes an input stream to disk * * @param filename base filename * @param data String of data to write to disk, compressed using gzip * @param compress true to GZip results * @return String of the file URI pointing where the data was written * @throws Exception */ private String writeInputStreamToDisk(String filename, InputStream data, boolean compress) throws IOException { Path filePath = BASE_DIRECTORY.resolve(Joiner.on(".").join(filename, SUFFIX_GZIP)); Files.deleteIfExists(filePath); Path createdFilePath = Files.createFile(filePath); OutputStream os = new FileOutputStream(createdFilePath.toFile()); if (compress) { os = new GZIPOutputStream(os); } IOUtils.copyLarge(data, os); IOUtils.closeQuietly(os); return createdFilePath.toUri().toString().replaceFirst(FILE_URI_PREFIX, ""); } @Override public void updateResponse(String id, InputStream stream) { boolean compressData = !SAVE_RESULTS_TO_DB; boolean proceed = true; String data = ""; synchronized (storeResponseSerialNumberLock) { if (!SAVE_RESULTS_TO_DB) { try { // The result contents won't be saved to the database, only a pointer to the file system. I am therefore // going to GZip the data to save space data = writeInputStreamToDisk(id, stream, compressData); } catch (IOException ex) { LOGGER.error("Failed to write output data to disk", ex); proceed = false; } } if (proceed) { try (Connection connection = getConnection(); PreparedStatement updateStatement = connection.prepareStatement(updateString)) { updateStatement.setString(INSERT_COLUMN_REQUEST_ID, id); updateStatement.setTimestamp(INSERT_COLUMN_REQUEST_DATE, new Timestamp(Calendar.getInstance().getTimeInMillis())); if (SAVE_RESULTS_TO_DB) { // This is implemented because we need to handle the case of SAVE_RESULTS_TO_DB = true. However, // this should not be used if you expect results to be large. // TODO- Remove and reimplement when setAsciiStream() has been properly implemented // @ https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc4/AbstractJdbc4Statement.java updateStatement.setString(INSERT_COLUMN_RESPONSE, IOUtils.toString(stream, DEFAULT_ENCODING)); } else { updateStatement.setString(INSERT_COLUMN_RESPONSE, data); } updateStatement.executeUpdate(); LOGGER.debug("Updated data into database with id of:" + id); } catch (SQLException | IOException ex) { LOGGER.error(MessageFormat.format("Failed to update data in database with id of:{0}", id), ex); } } } } @Override public InputStream lookupResponse(String id) { InputStream result = null; synchronized (storeResponseSerialNumberLock) { if (id != null && !"".equals(id.trim())) { try (Connection connection = getConnection(); PreparedStatement selectStatement = connection.prepareStatement(selectionString)) { selectStatement.setString(SELECTION_STRING_REQUEST_ID_PARAM_INDEX, id); try (ResultSet rs = selectStatement.executeQuery()) { if (null == rs || !rs.next()) { LOGGER.warn("No response found for request id " + id); } else { result = rs.getAsciiStream(SELECTION_STRING_RESPONSE_COLUMN_INDEX); // Copy the file to disk and create an inputstream from that because once I leave // this function, result will not be accessible since the connection to the database // will be broken. I eat a bit of overhead this way, but afaik, it's the best solution File tempFile = Files.createTempFile("SAFE-TO-DELETE-" + id, null).toFile(); // Best effort, even though SelfCleaningFileInputStream should delete it tempFile.deleteOnExit(); // Copy the ASCII stream to file IOUtils.copyLarge(result, new FileOutputStream(tempFile)); IOUtils.closeQuietly(result); // Create an InputStream (of the self-cleaning type) from this File and pass that on result = new SelfCleaningFileInputStream(tempFile); } } catch (IOException ex) { LOGGER.error("Could not look up response in database", ex); } } catch (SQLException ex) { LOGGER.error("Could not look up response in database", ex); } if (null != result) { if (!SAVE_RESULTS_TO_DB) { try { String outputFileLocation = IOUtils.toString(result); LOGGER.debug("ID {} is output and saved to disk instead of database. Path = " + outputFileLocation); if (Files.exists(Paths.get(outputFileLocation))) { result = new GZIPInputStream(new FileInputStream(outputFileLocation)); } else { LOGGER.warn("Response not found on disk for id " + id + " at " + outputFileLocation); } } catch (FileNotFoundException ex) { LOGGER.warn("Response not found on disk for id " + id, ex); } catch (IOException ex) { LOGGER.warn("Error processing response for id " + id, ex); } } } else { LOGGER.warn("response found but returned null"); } } else { LOGGER.warn("tried to look up response for null id, returned null"); } } return result; } @Override public String getMimeTypeForStoreResponse(String id) { String mimeType = null; try (Connection connection = getConnection(); PreparedStatement selectStatement = connection.prepareStatement(selectionString)) { selectStatement.setString(SELECTION_STRING_REQUEST_ID_PARAM_INDEX, id); try (ResultSet rs = selectStatement.executeQuery()) { if (null == rs || !rs.next()) { LOGGER.warn("No response found for request id " + id); } else { mimeType = rs.getString(SELECTION_STRING_RESPONSE_MIMETYPE_COLUMN_INDEX); } } } catch (SQLException ex) { LOGGER.error("Could not look up response in database", ex); } return mimeType; } @Override public File lookupResponseAsFile(String id) { if (!SAVE_RESULTS_TO_DB) { synchronized (storeResponseSerialNumberLock) { try { String outputFileLocation = IOUtils.toString(lookupResponse(id)); return new File(new URI(outputFileLocation)); } catch (URISyntaxException | IOException ex) { LOGGER.warn("Could not get file location for response file for id " + id, ex); } } } LOGGER.warn("requested response as file for a response stored in the database, returning null"); return null; } private class WipeTimerTask extends TimerTask { private static final String DELETE_STATEMENT = "DELETE FROM RESULTS WHERE RESULTS.REQUEST_ID = ANY ( ? );"; private static final int DELETE_STATEMENT_LIST_PARAM_INDEX = 1; private static final String LOOKUP_STATEMENT = "SELECT * FROM " + "(SELECT REQUEST_ID, EXTRACT(EPOCH FROM REQUEST_DATE) * 1000 AS TIMESTAMP FROM RESULTS) items WHERE TIMESTAMP < ?"; private static final int LOOKUP_STATEMENT_TIMESTAMP_PARAM_INDEX = 1; private static final int LOOKUP_STATEMENT_REQUEST_ID_COLUMN_INDEX = 1; private final long thresholdMillis; private final String databaseName = getDatabaseName(); WipeTimerTask(long thresholdMillis) { this.thresholdMillis = thresholdMillis; } @Override public void run() { LOGGER.info(databaseName + " Postgres wiper, checking for records older than {} ms", thresholdMillis); try { int deletedRecordsCount = wipe(); if (deletedRecordsCount > 0) { LOGGER.info(databaseName + " Postgres wiper, cleaned {} records from database", deletedRecordsCount); } else { LOGGER.debug(databaseName + " Postgres wiper, cleaned {} records from database", deletedRecordsCount); } } catch (SQLException | IOException ex) { LOGGER.warn(databaseName + " Postgres wiper, failed to deleted old records", ex); } } private int wipe() throws SQLException, IOException { LOGGER.debug(databaseName + " Postgres wiper, checking for records older than {} ms", thresholdMillis); int deletedRecordsCount = 0; List<String> oldRecords = findOldRecords(); if (!SAVE_RESULTS_TO_DB) { for (String recordId : oldRecords) { if (recordId.toLowerCase(Locale.US).contains("output")) { Files.deleteIfExists(Paths.get(BASE_DIRECTORY.toString(), recordId)); } } } if (!oldRecords.isEmpty()) { deletedRecordsCount = deleteRecords(oldRecords); } return deletedRecordsCount; } private int deleteRecords(List<String> recordIds) throws SQLException { int deletedRecordsCount; try (Connection connection = connectionHandler.getConnection(); PreparedStatement deleteStatement = connection.prepareStatement(DELETE_STATEMENT)) { deleteStatement.setArray(DELETE_STATEMENT_LIST_PARAM_INDEX, connection.createArrayOf("varchar", recordIds.toArray())); deletedRecordsCount = deleteStatement.executeUpdate(); } return deletedRecordsCount; } private List<String> findOldRecords() throws SQLException { List<String> matchingRecords = new ArrayList<>(); try (Connection connection = connectionHandler.getConnection(); PreparedStatement lookupStatement = connection.prepareStatement(LOOKUP_STATEMENT)) { long ageMillis = System.currentTimeMillis() - thresholdMillis; lookupStatement.setLong(LOOKUP_STATEMENT_TIMESTAMP_PARAM_INDEX, ageMillis); try (ResultSet rs = lookupStatement.executeQuery()) { while (rs.next()) { matchingRecords.add(rs.getString(LOOKUP_STATEMENT_REQUEST_ID_COLUMN_INDEX)); } } } return matchingRecords; } } }