content: /**
   * ﻿Copyright (C) 2007 - 2019 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.InputStream;
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Timestamp;
  import java.util.Calendar;
  
  import javax.xml.ws.Response;
  
  import org.n52.wps.DatabaseDocument.Database;
  import org.n52.wps.PropertyDocument.Property;
  import org.n52.wps.commons.WPSConfig;
  import org.slf4j.Logger;
  import org.slf4j.LoggerFactory;
  
  /**
  * An anstract-layer to the databases. 
  * 
  * @author Janne Kovanen
  * 
  */
  public abstract class AbstractDatabase implements IDatabase{
  	/** Property of the path to the location of the database */
  	public static final String PROPERTY_NAME_DATABASE_PATH = "databasePath";
  	
  	/** Property of the path to the location of the database - Actual name of the database. */
  	public static final String PROPERTY_NAME_DATABASE_NAME = "databaseName";
  	
  	/** Property of the path to the location of the database - name of database type: DERBY, HSQL, ...*/
  	public static final String PROPERTY_NAME_DATABASE = "database";
  	
  	/** SQL to create a response in the DB **/
  	public static final String 	creationString = "CREATE TABLE RESULTS (" +
  	"REQUEST_ID VARCHAR(100) NOT NULL PRIMARY KEY, " +
  	"REQUEST_DATE TIMESTAMP, " +
  	"RESPONSE_TYPE VARCHAR(100), " +
  	"RESPONSE CLOB, " +
  	"RESPONSE_MIMETYPE VARCHAR(100))";
  	
  	/** SQL to insert a response into the database */
  	public static final String insertionString = "INSERT INTO RESULTS VALUES (?, ?, ?, ?, ?)";
  
  	/** SQL to update a response, that was already stored in the database */
  	public static final String updateString = "UPDATE RESULTS SET RESPONSE = (?) WHERE REQUEST_ID = (?)";
  
  	/** SQL to retrieve a response from the database */
  	public static final String selectionString = "SELECT RESPONSE, RESPONSE_MIMETYPE FROM RESULTS WHERE REQUEST_ID = (?)";
  
  	/** The column of "response" in the select statement. */
  	protected static final int SELECT_COLUMN_RESPONSE = 1;
  
  	/** The column of "request_id" in the insert statement. */
  	protected static final int INSERT_COLUMN_REQUEST_ID = 1;
  
  	/** The column of "request_data" in the insert statement. */
  	protected static final int INSERT_COLUMN_REQUEST_DATE = 2;
  
  	/** The column of "response_type" in the insert statement. */
  	protected static final int INSERT_COLUMN_RESPONSE_TYPE = 3;
  
  	/** The column of "response" in the insert statement. */
  	protected static final int INSERT_COLUMN_RESPONSE = 4;
  
  	/** The column of "response" in the update statement. */
  	protected static final int UPDATE_COLUMN_RESPONSE = 1;
  
  	/** The column of "request_id" in the update statement. */
  	protected static final int UPDATE_COLUMN_REQUEST_ID = 2;
  
  	protected static final int INSERT_COLUMN_MIME_TYPE = 5;
  	
  	/** get access to the global logger. */
  	private static Logger LOGGER = LoggerFactory.getLogger(AbstractDatabase.class);
  	
  	protected static PreparedStatement insertSQL = null;
  	protected static PreparedStatement updateSQL = null;
  	protected static PreparedStatement selectSQL = null;
  	
  	/**
  	 * Get an instance of the Database object. Only one instance is required. If
  	 * it not already exists, it will be created. The first call of this method
  	 * can take some time. Preferable call this method once on application
  	 * startup, to serve additional calls in less time. When the
  	 * Database-application is started, it first tries to connect to an existing
  	 * database. If it is not found, this method tries to create a new one.
  	 * Implementations can have additional properties, such as username/password.
  	 * 
  	 * @return A static instance of Database.
  	 */
  	public static IDatabase getInstance() {
  		throw new SubclassNotImplementingException(
  		    "Subclasses of AbstractDatabase must implement the method \"static String getInstance()\"");
  	}
  	
      
      @Override
  	public synchronized void insertRequest(String id, InputStream inputStream, boolean xml) {			
          insertResultEntity(inputStream, "REQ_" + id, "ExecuteRequest", xml ? "text/xml" : "text/plain");
  	}
      
  	/**
  	 * Insert a new Response into the Database.
  	 * 
  	 * @param response  The Response to insert.
  	 * @see #storeResponse(Response)
  	 * 
  	 */
      @Override
  	public synchronized String insertResponse(String id, InputStream inputStream) {			
          return insertResultEntity(inputStream, id, "ExecuteResponse", "text/xml");
  	}
  	
  	/**
  	 * Inserts any result, which has to be stored in the DB.
  	 * @param baos
  	 * @param id
  	 * @param type
  	 */
  	protected synchronized String insertResultEntity(InputStream stream, String id, String type, String mimeType) {
  		// Use Calendar to get the current timestamp.
  		// Uses java.sql.Date !
  		Timestamp timestamp = new Timestamp(Calendar.getInstance().getTimeInMillis());
  
  		// try to insert a row of data into the database.
  		try {
  			AbstractDatabase.insertSQL.setString(INSERT_COLUMN_REQUEST_ID, id);
  			AbstractDatabase.insertSQL.setTimestamp(INSERT_COLUMN_REQUEST_DATE, timestamp);
  			AbstractDatabase.insertSQL.setString(INSERT_COLUMN_RESPONSE_TYPE, type);
  			AbstractDatabase.insertSQL.setAsciiStream(INSERT_COLUMN_RESPONSE, stream);
  			AbstractDatabase.insertSQL.setString(INSERT_COLUMN_MIME_TYPE, mimeType);
  			// AbstractDatabase.insertSQL.setAsciiStream(INSERT_COLUMN_RESPONSE, bais, b.length);
  		
  			AbstractDatabase.insertSQL.executeUpdate();
  			getConnection().commit();
  		} catch (SQLException e) {
  			LOGGER.error("Could not insert Response into database: "
  					+ e.getMessage());
  		} 
  		return generateRetrieveResultURL(id);
  	}
  
  	/**
  	 * Update the Response in the Database, based on the Identifier.
  	 * 
  	 * @param response
  	 *            The Response to update
  	 * @see #storeResponse(Response)
  	 */
      @Override
  	public synchronized void updateResponse(String id, InputStream inputStream) {
  
  		// Try to update the row of data into the database.
  		try {
  			AbstractDatabase.updateSQL.setString(
  					UPDATE_COLUMN_REQUEST_ID, id);
  			AbstractDatabase.updateSQL.setAsciiStream(
  					UPDATE_COLUMN_RESPONSE, inputStream);
  			AbstractDatabase.updateSQL.executeUpdate();
  			getConnection().commit();
  		} catch (SQLException e) {
  			LOGGER.error("Could not insert Response into database: "
  					+ e.getMessage());
  		}
  	}
  
  	/**
  	 * Store the Response of a deferred Request. It either gets inserted into
  	 * the database, or it updates a previous Response, based on the identifier.
  	 * 
  	 * @param response
  	 *            The Response to store.
  	 */ 
      @Override
  	public synchronized String storeResponse(String id, InputStream inputStream) {
  		if (lookupResponse(id) == null) {
  			return insertResponse(id, inputStream);
  		} else {
  			updateResponse(id, inputStream);
  			return null;
  		}
  	}
  
      @Override
  	public synchronized InputStream lookupRequest(String request_id) {
          request_id = "REQ_" + request_id;
  		try {
  			AbstractDatabase.selectSQL.setString(SELECT_COLUMN_RESPONSE, request_id);
  			ResultSet res = AbstractDatabase.selectSQL.executeQuery();
  			if (res == null || !res.next()) {
  				LOGGER.warn("Query did not return a valid result.");
  				return null;
  			} else {
  				LOGGER.info("Successfully retrieved the Request: "
  						+ request_id);
  				return res.getAsciiStream(1);
  			}
  		} catch (SQLException e) {
  			LOGGER.error("SQLException with request_id: " + request_id
  					+ "and message: " + e.getMessage());
  			return null;
  		}
  	}
      
  	/**
  	 * Retrieve the Response on a previous Request, based on an unique
  	 * identifier, which was already given to the client for reference.
  	 * 
  	 * @param request_id
  	 *            The identifier of the Request
  	 * @return null, if an SQLException occurred, else an InputStream with the
  	 *         Response
  	 */
      @Override
  	public synchronized InputStream lookupResponse(String request_id) {
  		try {
  			AbstractDatabase.selectSQL.setString(SELECT_COLUMN_RESPONSE, request_id);
  			ResultSet res = AbstractDatabase.selectSQL.executeQuery();
  			if (res == null || !res.next()) {
  				LOGGER.warn("Query did not return a valid result.");
  				return null;
  			} else {
  				LOGGER.info("Successfully retrieved the Response of Request: "
  						+ request_id);
  				return res.getAsciiStream(1);
  			}
  		} catch (SQLException e) {
  			LOGGER.error("SQLException with request_id: " + request_id
  					+ "and message: " + e.getMessage());
  			return null;
  		}
  	}
  	
      @Override
  	public synchronized String storeComplexValue(String id, InputStream stream, String type, String mimeType) {
  		return insertResultEntity(stream, id, type, mimeType);
  	}
  	
  	/**
  	 * The URL referencing the location from which the ExecuteResponse can be retrieved. 
  	 * If "status" is "true" in the Execute request, the ExecuteResponse should also be 
  	 * found here as soon as the process returns the initial response to the client. 
  	 * It should persist at this location as long as the outputs are accessible from the server.
  	 * The outputs may be stored for as long as the implementer of the server decides. 
  	 * If the process takes a long time, this URL can be repopulated on an ongoing basis 
  	 * in order to keep the client updated on progress. Before the process has succeeded, 
  	 * the ExecuteResponse contains information about the status of the process, including 
  	 * whether or not processing has started, and the percentage completed. It may also 
  	 * optionally contain the inputs and any ProcessStartedType interim results. When the 
  	 * process has succeeded, the ExecuteResponse found at this URL shall contain the output 
  	 * values or references to them.
  	 * @return
  	 */	
      @Override
  	public String generateRetrieveResultURL(String id) {
  		return getBaseResultURL() + id;   // TODO:  Parameterize this... Execution Context..?
  	}
  	
  	public abstract Connection getConnection();
  	public abstract String getConnectionURL();
  	
  	/**
  	 * Returns the name of the database.
  	 */
      @Override
  	public String getDatabaseName() {
  		
  		String dbName = getDatabaseProperties(PROPERTY_NAME_DATABASE_NAME);
  		return (dbName == null || dbName.equals("")) ? "wps" : dbName;
  	}
  	
  	static String getDatabaseProperties(String propertyName) {
  		Database database = WPSConfig.getInstance().getWPSConfig().getServer().getDatabase();
  		Property[] dbProperties = database.getPropertyArray();
  		for(Property property : dbProperties){
  			if(property.getName().equalsIgnoreCase(propertyName)){
  				return property.getStringValue();
  			}
  		}
  		return null;
  	}
  
  	/** 
  	 * Returns the path to the database.
  	 * 
  	 * @note The path has no file separator in the end of the file.
  	 */
  	protected static String getDatabasePath() {
  		String dbPath = getDatabaseProperties(PROPERTY_NAME_DATABASE_PATH);
  		String dbName = getDatabaseProperties(PROPERTY_NAME_DATABASE_NAME);
  		String dbTypeName = getDatabaseProperties(PROPERTY_NAME_DATABASE);
  		
  		if (dbPath == null || dbPath.compareTo("") == 0) {
              // TODO:  parameterize base path
  			dbPath = System.getProperty("java.io.tmpdir", ".") + File.separator + "Databases";
  			if(dbTypeName!=null && !dbTypeName.equals("")) {
  				dbPath += File.separator + dbTypeName.toUpperCase();
  			} else {
  				dbPath += File.separator + "DERBY";
  			}
  			if(dbName!=null && !dbName.equals("")) {
  				dbPath += File.separator + dbName.toUpperCase();
  			} else {
  				dbPath += File.separator + "wps";
  			}
  		}
  		return dbPath;
  	}
  	
      @Override
  	public void shutdown() {
  		try {
  			getConnection().close();
  		} catch (SQLException e) {
  			
  			LOGGER.error("Problem encountered when closing the SQL connection", e);
  		}
  	}
  	
      @Override
  	public String getMimeTypeForStoreResponse(String id) {
  		try {
  			AbstractDatabase.selectSQL.setString(SELECT_COLUMN_RESPONSE, id);
  		
  			ResultSet res = AbstractDatabase.selectSQL.executeQuery();
  			if (res == null || !res.next()) {
  				LOGGER.warn("Query did not return a valid result.");
  				return null;
  			} else {
  				LOGGER.info("Successfully retrieved the Mimetyoe of the response: "
  						+ id);
  				return res.getString(2);
  			}
  		} catch (SQLException e) {
  			LOGGER.error("SQLException with request_id: " + id
  					+ "and message: " + e.getMessage());
  			return null;
  		}
  	}
  	
  	@Override
  	public long getContentLengthForStoreResponse(String id) {
  		return -1;
  	}
  	
      @Override
  	public boolean deleteStoredResponse(String id) {
  		return false;
  	}
  
      @Override
  	public File lookupRequestAsFile(String id) {
  		return null;
  	}
  
      @Override
  	public File lookupResponseAsFile(String id) {
  		return null;
  	}
      
      public String getBaseResultURL() {
  
          return WPSConfig.getServerBaseURL() + "/RetrieveResultServlet?id=";
      }
  	
  }
  