/*
 * Copyright (c) 2008-2010 The Regents of the University of California.
 * All rights reserved.
 *
 * '$Author: crawl $'
 * '$Date: 2018-01-26 23:35:42 +0000 (Fri, 26 Jan 2018) $' 
 * '$Revision: 34653 $'
 * 
 * Permission is hereby granted, without written agreement and without
 * license or royalty fees, to use, copy, modify, and distribute this
 * software and its documentation for any purpose, provided that the above
 * copyright notice and the following two paragraphs appear in all copies
 * of this software.
 *
 * IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY
 * FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES
 * ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
 * THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF
 * SUCH DAMAGE.
 *
 * THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
 * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE
 * PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF
 * CALIFORNIA HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES,
 * ENHANCEMENTS, OR MODIFICATIONS.
 *
 */

package org.kepler.util.sql;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.InetSocketAddress;
import java.net.Socket;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Properties;
import java.util.Random;
import java.util.Set;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.io.IOUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.tools.ant.types.FileSet;
import org.apache.tools.ant.types.Resource;
import org.apache.tools.ant.types.resources.FileResource;
import org.hsqldb.Server;
import org.hsqldb.server.ServerConstants;
import org.kepler.build.modules.Module;
import org.kepler.build.modules.ModuleTree;
import org.kepler.build.project.ProjectLocator;
import org.kepler.util.StatusNotifier;

import ptolemy.util.FileUtilities;
import ptolemy.util.MessageHandler;

/**
 *
 * An implementation of DatabaseType for HSQL.
 *
 * @author Daniel Crawl
 * @version $Id: HSQL.java 34653 2018-01-26 23:35:42Z crawl $
 *
 */
    
public class HSQL extends DatabaseType
{
   
    /** Only this package (DatabaseFactory) can instantiate. */
    protected HSQL()
    {
        super();
    }

    /** Close a JDBC connection. */
    @Override
    public void disconnect() throws SQLException
    {
        if(_connection != null)
        {   
            // NOTE: set the write delay to flush any pending updates to disk.
            // if this is not done, data could be lost. 
            // see:
            // http://bugzilla.ecoinformatics.org/show_bug.cgi?id=5410
            // http://bugzilla.ecoinformatics.org/show_bug.cgi?id=4325           
            //
            _executeSQL(_SQL_SET_WRITE_DELAY);

            synchronized(_urlMapLock)
            {
                String urlStr = _connectionToURLMap.remove(_connection);
                
                // see if the url was in the map. only connections that access
                // the db files directly are added to the map.
                if(urlStr != null)
                {
                    // it accesses the file directly
                    
                    // see what the ref count is
                    Integer count = _urlToCountMap.remove(urlStr);
                    
                    if(_isDebugging)
                    {
                        _log.debug("ref count is " + count + " for " + urlStr);
                    }
                    
                    if(count.intValue() == 1)
                    {
                        if(_isDebugging)
                        {
                            _log.debug("shutting down for " + urlStr);
                        }
                        _executeSQL("SHUTDOWN");
                        
                        // remove the port file
                        
                        // make sure the dbName is set
                        if(_dbName != null)
                        {
                            _deletePortFile(_dbName);
                        }                            
                        
                    }
                    else
                    {
                        // decrement ref count
                        count--;
                        _urlToCountMap.put(urlStr, count);
                    }
                }
            }

            if(_isDebugging)
            {
                _log.debug("closed connection " + _connection);
            }
        }
        
        super.disconnect();
    }
    
    /** Get the port for a database name. If dbPort is outside the range of
     *  ports previously used for Kepler (9001-9010), then dbPort is returned.
     *  If the port file exists in the database directory, then the port in
     *  this file is returned. Otherwise, this method chooses a random port,
     *  writes it to the port file, and returns the value. The port file is
     *  removed when the server is shut down either in shutdownServers(), or
     *  disconnect().
     */
    @Override
    protected synchronized String _getPort(String dbPort, String dbName, String hostName) throws IOException
    {
        
        boolean choseRandom = false;

        // make sure the host name is set
        if (hostName != null && !hostName.trim().isEmpty()) 
        {
            // check if db port is not null, and in 9001-9010
            if(dbPort == null || dbPort.trim().isEmpty())
            {
                choseRandom = true;
            }
            else
            {
                final int port = Integer.valueOf(dbPort).intValue();
                if(port >= 9001 && port <= 9010)
                {
                    choseRandom = true;
                }
            }
        }
        
        if(choseRandom) {
            
            dbPort = null;
            
            // see if port file exists for this database
            final File portFile = new File(dbName + _PORT_FILE_EXTENSION);
            if(portFile.exists())
            {
                final Properties properties = HSQLUtils.readPropertiesFile(portFile);
                dbPort = properties.getProperty(_PORT_FILE_PASSWORD_PROP_NAME);
            }
            
            // see if the port file existed and we're already using this port.
            // NOTE: by keeping a set of ports we've randomly chosen and checking
            // it here, we can handle the (unlikely) case where the same port is
            // in more than one port file.
            if(dbPort != null)
            {
                String existingDbName = _serverPortToName.get(dbPort);
                // see if the port is associated with a database
                if(existingDbName == null)
                {
                    // add the mapping
                    _serverPortToName.put(dbPort, dbName);
                }
                // see if the port is associated with a different database
                else if(!dbName.equals(existingDbName))
                {
                    // set to null so we chose a different port.
                    dbPort = null;
                }
            }
                    
            
            if(dbPort == null)
            {
                int port = -1;
                int tries = 10;
                
                while(tries > 0) {
                    // pick random port between 10,000 and 60,000
                    port = _random.nextInt(50000) + 10000;
                                                            
                    // see the port is in use
                    final Socket socket = new Socket();
                    final InetSocketAddress addr = new InetSocketAddress("localhost", port);
                    try
                    {
                        socket.connect(addr);
                        socket.close();
                        //System.out.println("port " + port + " already in use. tries = " + tries);
                        tries--;
                    }
                    catch(IOException e)
                    {
                        // connection failed
                        //System.out.println("port " + port + " not in use.");
                        break;
                    }
                }
                
                if(tries == 0)
                {
                    throw new IOException("Could not choose random port for HSQL server.");
                }

                dbPort = String.valueOf(port);

                _serverPortToName.put(dbPort, dbName);
                
                // write to port file
                final Properties properties = new Properties();
                properties.put(_PORT_FILE_PASSWORD_PROP_NAME, dbPort);
                _writePropertiesFile(portFile, properties);                
            }
        }
        return dbPort;
        
    }
                
    
    /** Get the string used in SQL statements to modify the
     *  data type of a column.
     */
    @Override
    public String getColumnAlterStr()
    {
        return "ALTER";
    }

    /** Adjust the name of a column. */
    @Override
    public String getColumnName(String columnName)
    {
        // user is reserved in HSQL 2.x.
        if(columnName.equalsIgnoreCase("user"))
        {
            return "\"USER\"";
        }
        return super.getColumnName(columnName);
    }

    /** Get a string representing the default time. */
    @Override
    public String getDefaultTimeStr()
    {
        return "'1900-01-01 01:00:00'";
    }

    /** Get a string representation of the false value for a boolean column. */
    @Override
    public String getFalseValue()
    {
        return "FALSE";
    }

    /** Get the name of the type. */
    @Override
    public String getName()
    {
        return "HSQL";
    }

    /** Get the primary file extension of the database. If the database
     *  is not file-based, this returns null.
     */
    @Override
    public String getPrimaryFileExtension()
    {
        return "data";
    }

    /** Get a string representation of the true value for a boolean column. */
    @Override
    public String getTrueValue()
    {
        return "TRUE";
    }

    /** Returns true if database name should be an absolute path in the
     *  file system.
     */
    @Override
    public boolean needAbsolutePathForName()
    {
        return true;
    }

    /** Returns true if need host name for connect. */
    @Override
    public boolean needHostForConnect()
    {
        return false;
    }

    /** Returns true if need password for connect. */
    @Override
    public boolean needPasswordForConnect()
    {
        return false;
    }

    /** Returns true if need user name for connect. */
    @Override
    public boolean needUserForConnect()
    {
        return false;
    }
    
    /** Rename a column. */
    @Override
    public void renameColumn(String oldName, Column newColumn, String tableName) throws SQLException
    {
        String newName = getColumnName(newColumn.getName());
        String sqlStr = "ALTER TABLE " + getTableName(tableName) +
            " ALTER COLUMN " + getColumnName(oldName) + " RENAME TO " + newName;
        _executeSQL(sqlStr);
    }
    
    /** Change a column to allow null values. */
    @Override
    public void setColumnNull(Column column, String tableName) throws SQLException
    {
        String sqlStr = "ALTER TABLE " + getTableName(tableName) +
                " ALTER COLUMN " + getColumnName(column.getName()) + " SET NULL";
            _executeSQL(sqlStr);
    }

    /** Set not null constraint to a column. */
    @Override
    public void setColumnNotNull(Column column, String tableName) throws SQLException
    {
        String sqlStr = "ALTER TABLE " + getTableName(tableName) +
            " ALTER COLUMN " + getColumnName(column.getName()) + " SET NOT NULL";
        _executeSQL(sqlStr);
    }

    /** Set whether HSQL servers are run in a separate process. */
    public static void setForkServers(boolean fork)
    {
        _forkServers = fork;
    }
    
    /** Stop any running servers. */
    public static void shutdownServers()
    {
        for(Server server : _servers)
        {
            // remove the port file
            _deletePortFile(server.getDatabasePath(0, true));
            
                final Connection connection = _getConnectionForServer(server);
                if(connection != null)
                {
                        Statement statement = null;
                    try
                    {
                        try
                        {
                            statement = connection.createStatement();
                            statement.execute("SHUTDOWN");
                        }
                        finally
                        {
                            if(statement != null)
                            {
                                statement.close();
                            }
                        }
                    }
                    catch(SQLException e)
                    {
                        MessageHandler.error("Error shutting down database.", e);
                    }
                    finally
                    {
                        try {
                                                connection.close();
                                        } catch (SQLException e) {
                                            MessageHandler.error("Error closing connection " +
                                                    "while shutting down databases.", e);
                                        }
                    }
                }               
        }
        _servers.clear();
    }
    
    ///////////////////////////////////////////////////////////////////
    // protected methods
    
    /** Returns true if foreign keys are automatically indexed. */
    @Override
    protected boolean _areForeignKeysIndexed()
    {
        return false;
    }

    /** Returns true if primary keys are automatically indexed. */
    @Override
    protected boolean _arePrimaryKeysIndexed()
    {
        return false;
    }

    /** Return the alias for a database given the path. */
    private static String _getDatabaseAlias(String path) throws SQLException
    {
        int pathIndex = path.lastIndexOf(File.separator);
        if(pathIndex == -1)
        {
            throw new SQLException("Database name must be an absolute " +
                "path: " + path);
        }
        else
        {
            return path.substring(pathIndex + 1);
        }        
    }
    
    /** Return the database path without the file name given the path. */
    private static String _getDatabasePath(String path) throws SQLException
    {
        int pathIndex = path.lastIndexOf(File.separator);
        if(pathIndex == -1)
        {
            throw new SQLException("Database name must be an absolute " +
                "path: " + path);
        }
        else
        {
            return path.substring(0, pathIndex);
        }
    }    

    /** Get the driver class name. */
    @Override
    protected String _getDriverName()
    {
        return "org.hsqldb.jdbcDriver";
    }

    /** Get a JDBC URL. */
    @Override
    protected String _getJDBCUrl(String hostName, String port,
        String databaseName) throws SQLException
    {
        // see if the host name is set
        if (hostName != null && hostName.length() > 0) 
        {
            String hostAndPort = _combineHostAndPort(hostName, port);
            
            // make sure the database name starts with a /
            if(!databaseName.startsWith("/"))
            {
                databaseName = "/" + databaseName;
            }
            
            String alias = _getDatabaseAlias(databaseName);
            return "jdbc:hsqldb:hsql://" + hostAndPort + "/" + alias +
                ";filepath=hsqldb:file:" + databaseName;
        }
        return "jdbc:hsqldb:" + databaseName;
    }

    /** Get the SQL string of a column type. */
    @Override
    protected String _getTypeString(Column column)
    {
        String retval = null;

        switch(column.getType())
        {
            case Boolean:
                retval = "BOOLEAN";
                break;
            case Blob:
                // NOTE: we use BINARY instead of OBJECT so that we can
                // query the size of the column.
                // see DatabaseType.getColumnSize()
                retval = "BINARY";
                break;
            case Integer:
                retval = "INTEGER";
                break;
            case Timestamp:
                retval = "TIMESTAMP";
                break;
            case TextBlob:
            case Varchar:
                retval = "VARCHAR";
                break;
        }

        if(retval != null && column.isAutoIncrement())
        {
            // set the starting value to be 1 since ResulSet.getInt()
            // returns 0 if the value is NULL.
            retval += " GENERATED BY DEFAULT AS IDENTITY(START WITH 1)";
        }

        return retval;
    }

    /** Returns true if database uses a catalog. */
    @Override
    protected boolean _hasCatalog()
    {
        return false;
    }

    /** Returns true if database supports auto-generated keys in its prepared
     *  statements.
     */
    @Override
    protected boolean _hasGeneratedKeys()
    {
        // HSQL 2.x supports generated keys
        return true;
    }
    
    /** Returns true if table is cached. */
    @Override
    protected boolean _isTableCached()
    {
        return true;
    }
    
    /** Returns true if column names should be capitalized. */
    @Override
    protected boolean _needCapitalColumnNames()
    {
        return true;
    }

    /** Returns true if table names should be capitalized. */
    @Override
    protected boolean _needCapitalTableNames()
    {
        return true;
    }
    
    /** Get a connection to the database. */
    @Override
    protected synchronized Connection _getConnection(String jdbcURL) throws SQLException
    {
        // attempt to find the path, alias, and port in the jdbc url
        Matcher matcher = _jdbcURLPattern.matcher(jdbcURL);
        if(!matcher.matches())
        {
            throw new SQLException("Could not parse JDBC URL " + 
                jdbcURL + "\n" +
                "JDBC URL must be in form of: " +
                "jdbc:hsqldb:hsql://hostname:port/alias;filepath=hsqldb:path");
        }
        
        final String pathStr = matcher.group(3);
        final String aliasStr = matcher.group(2);
        final String dbPort = matcher.group(1);
        
        String dbDir;
        if(pathStr.startsWith("file:")) {
            dbDir = pathStr.substring("file:".length());
        } else {
            dbDir = pathStr;
        }
        
        final String databaseName = dbDir + File.separator + aliasStr;
        
        final String userName = DEFAULT_USER_NAME;
        String passwd;
        try {
            passwd = HSQLUtils.getAuthFilePassword(databaseName);
        } catch (IOException e) {
            throw new SQLException("Error reading auth file for " + aliasStr + ": " + e.getMessage());
        }
        
        // check if database needs to be upgraded.
        try {
            _upgradeIfNecessary(databaseName, aliasStr, userName, passwd);
        } catch(Exception e) {
            throw new SQLException("Error upgrading database " + aliasStr, e);
        }

        try
        {
            Connection connection = DriverManager.getConnection(jdbcURL, userName, passwd);
            _updateConnectionReferenceCount(connection, jdbcURL);
            return connection;
        }
        catch(SQLException e)
        {
            String sqlState = e.getSQLState();

            if(_isDebugging)
            {
                _log.debug("failed = " + sqlState);
            }
            
            // see if the server is not running
            if(sqlState.equals(_SERVER_NOT_RUNNING_SQL_STATE) ||
                // HSQL 2.x error code
                sqlState.equals(_SERVER_NOT_RUNNING_SQL_STATE_2))
            {                
                
                StatusNotifier.log("Starting HSQL Server for " + aliasStr);

                // start a server
                int serverState = _launchDBServer(pathStr, aliasStr, dbPort);
                if(serverState != ServerConstants.SERVER_STATE_ONLINE)
                {
                    throw new SQLException("Unable to start HSQL server for " +
                        jdbcURL);
                }

                try
                {
                    Connection retval = DriverManager.getConnection(jdbcURL, userName, passwd);
                    
                    //System.out.println("new conn " + URL + " is " + conn);
                    _serverConnectionSet.add(retval);
                    _initializeNewURL(retval);
                    return retval;
                }
                catch(SQLException e2)
                {
                    throw new SQLException("Unable to start HSQL server for " +
                        jdbcURL + ": " + e2.getMessage());
                }
            }
            else
            {
                throw e;
            }
        }
    }
    
    /** Get a connection to the database. */
    @Override
    protected synchronized Connection _getConnection(String dbURL, String dbPort,
        String databaseName, String userName, String passwd)
        throws SQLException
    {
                
        // HSQL defaults to user "sa"
        userName = DEFAULT_USER_NAME;

        // read the password from the auth file unless it was specified
        // in the configuration file
        if(passwd == null || passwd.trim().isEmpty())
        {
            try
            {
                passwd = HSQLUtils.getAuthFilePassword(databaseName);
                //System.out.println("passwd " + passwd);
            }
            catch (IOException e)
            {
                throw new SQLException("Error reading auth file.", e);
            }
        }
        
        String pathStr = _getDatabasePath(databaseName);        
        String aliasStr = _getDatabaseAlias(databaseName);
        
        try {
            _upgradeIfNecessary(databaseName, aliasStr, userName, passwd);
        } catch(Exception e) {
            throw new SQLException("Error upgrading database " + aliasStr, e);
        }
       
        // record the database path so that in disconnect() we can remove
        // the port file when the database is shut down.
        _dbName = databaseName;
                        
        Connection conn = null;
        try
        {
            if(_isDebugging)
            {
                _log.debug("getting connection for " + dbURL);
            }
            
            //System.out.println("connecting to " + dbURL + " with passwd " + passwd);
            conn = DriverManager.getConnection(dbURL, userName, passwd);

            if(_isDebugging)
            {
                _log.debug("success");
            }

            //System.out.println("already running hsql url = " + URL);
        }
        catch(SQLException e)
        {
            String sqlState = e.getSQLState();
            String errorMessage = e.getMessage();

            if(_isDebugging)
            {
                _log.debug("failed = " + sqlState);
            }
            
            // see if the server is not running
            if(sqlState.equals(_SERVER_NOT_RUNNING_SQL_STATE) ||
                    // HSQL 2.x error code
                        sqlState.equals(_SERVER_NOT_RUNNING_SQL_STATE_2))
            {
                StatusNotifier.log("Starting HSQL Server for " + aliasStr);
                
                // start a server
                int serverState = _launchDBServer(pathStr, aliasStr, dbPort);
                if(serverState != ServerConstants.SERVER_STATE_ONLINE)
                {
                    throw new SQLException("Unable to start HSQL server for " +
                            dbURL, e);
                }

                try
                {
                    // create a connection using the existing password, if any
                    String existingPasswd = HSQLUtils.getAuthFilePassword(_dbName);
                    
                    //System.out.println("existingPasswd " + existingPasswd);
                    
                    conn = DriverManager.getConnection(dbURL, userName, existingPasswd);
                    
                    _log.info("started HSQL server at " + dbURL);
                    //new Exception().printStackTrace();
                    _serverConnectionSet.add(conn);
                    _initializeNewURL(conn);
                }
                catch(SQLException e2)
                {
                    throw new SQLException("Unable to start HSQL server for " +
                            dbURL, e2);
                }
                catch(IOException e3)
                {
                    throw new SQLException("Error reading auth file.", e3);
                }
            }
            else if(errorMessage != null && errorMessage.equals("Access is denied"))
            {
                // if the database does not exist, and the password is set,
                // getConnection() fails with Access is denied.
                // new databases must be created with no password.
                conn = DriverManager.getConnection(dbURL, userName, "");
            }
            else
            {                
                throw e;
            }
        }
        
        if (conn != null) 
        {
            if(_isDebugging)
            {
                _log.debug("opened connection " + conn + " url = " + dbURL);
            }

            boolean isNew = _updateConnectionReferenceCount(conn, dbURL);
            
            if(isNew) {
                try
                {
                    _changePassword(conn, databaseName, passwd);
                }
                catch (IOException e)
                {
                    throw new SQLException("Error writing auth file.", e);
                } 
            }
            
            return conn;
        }
        else
        {
            throw new SQLException(
                    "Failed to connect to url \""
                            + dbURL
                            + "\" as user \""
                            + userName
                            + ". Perhaps there was an error launching the db server. "
                            + "In addition, sometimes the database "
                            + "cache can become corrupt, so another "
                            + "solution is to remove the \"" + pathStr
                            + "\" directory.");
        }
    }    

    ///////////////////////////////////////////////////////////////////
    // private methods

    /** Change the password for the database. The new password is written to the
     *  auth file.
     *  @param connection the jdbc connection
     *  @param databaseName the database name
     *  @param passwd the new password for the database. if null or empty,
     *  one is randomly generated.
     */
    private static void _changePassword(Connection connection,
        String databaseName, String passwd) throws IOException, SQLException
    {

        String newPasswd;
        
        if(passwd == null || passwd.isEmpty())
        {
            // generate a random password
            newPasswd = UUID.randomUUID().toString();
        }
        else
        {
            newPasswd = passwd;
        }
        
        
        //System.out.println("changing password to " + newPasswd);
        
        
        final Properties properties = new Properties();
        properties.setProperty(HSQLUtils.AUTH_FILE_PASSWORD_PROP_NAME, newPasswd);
        
        // write the properties file
        final File authFile = new File(databaseName + HSQLUtils.AUTH_FILE_EXTENSION);
        _writePropertiesFile(authFile, properties);
        
        // remove read permissions for everyone but owner
        authFile.setReadable(false, false);
        authFile.setReadable(true, true);
                
        try(Statement statement = connection.createStatement();) {
            statement.execute("ALTER USER " + DEFAULT_USER_NAME + " SET PASSWORD \"" + newPasswd + "\"");
            // checkpoint the database so the password change is made persistent.
            statement.execute("CHECKPOINT");
        }
    }
        
    /** Write properties to a file. */
    private static void _writePropertiesFile(File file, Properties properties) throws IOException
    {
        // make sure the containing directory exists.
        File parentDir = file.getParentFile();
        if(!parentDir.exists() && !parentDir.isDirectory() &&
                        !parentDir.mkdirs()) {
                throw new IOException("Unable to create directories for " + parentDir);
        }
        
        OutputStream outputStream = null;
        try
        {
            outputStream = new FileOutputStream(file);
            properties.store(outputStream, null);
        }
        finally
        {
            if(outputStream != null)
            {
                outputStream.close();
            }
        }
    }
    
    /** Get a Connection object for a Server. Returns null on error. */
    private static Connection _getConnectionForServer(Server server)
    {
        String host = server.getAddress();
        //replace 0.0.0.0 to be localhost to avoid errors on some machines
        if (host.equals("0.0.0.0"))
                host = "localhost";
        final int port = server.getPort();
        final String alias = server.getDatabaseName(0, true);
        final String path = server.getDatabasePath(0, true);
                try {
                    // read the password from the auth file
                    String passwd = HSQLUtils.getAuthFilePassword(path);
                    
                    // XXX this assume user is sa and password is either in auth file or empty
                    // a better solution is to store the username and password when starting
                    // the server.
                        return DriverManager.getConnection("jdbc:hsqldb:hsql://" +
                                        host + ":" + port + "/" + alias, DEFAULT_USER_NAME, passwd);
                } catch (Exception e) {
                        MessageHandler.error("Could not get Connection object for HSQL server " + server, e);
                }
                return null;
    }
    
    /** Start the HSQL Server */
    private static int _launchDBServer(String dbNamePath, String dbAlias,
        String dbPort)
    {
        
        if(_forkServers)
        {
            System.out.println("spawning HSQL server for " + dbNamePath);
            
            // find the hsql jar
            String classpath = System.getProperty("java.class.path");
            String[] jars = classpath.split(File.pathSeparator);
            String hsqlJar = null;
            for(String jar : jars)
            {
                if(jar.matches(".*hsqldb-[\\d\\.]+\\.jar$"))
                {
                    hsqlJar = jar;
                    break;
                }
            }
            
            if(hsqlJar == null)
            {
                MessageHandler.error("Unable to find HSQL jar in class path.");
                return ServerConstants.SERVER_STATE_SHUTDOWN;
            }
            
            // NOTE: the database argument must include the file name of
            // the database. when using the Server API to start the server
            // (see below), the database argument does NOT include the file
            // name, but uses the alias as the file name.
            
            ProcessBuilder procBuilder = new ProcessBuilder("java",
                    "-cp",
                    hsqlJar,
                    "org.hsqldb.Server",
                    "-address",
                    "localhost",
                    "-port",
                    dbPort,
                    "-dbname.0",
                    dbAlias,
                    "-database.0",
                    dbNamePath + File.separator + dbAlias);
            procBuilder.redirectErrorStream(true);
            
            //for(String str : procBuilder.command())
              //System.out.print(str + " ");
            //System.out.println();
            
            try {
                /*Process proc =*/ procBuilder.start();
                
                // sleep a few seconds so that it has time to start before we
                // try to connect
                // XXX this may not be long enough
                Thread.sleep(3000);
                return ServerConstants.SERVER_STATE_ONLINE;
            } catch (Exception e) {
                MessageHandler.error("Error starting HSQL server.", e);
                return ServerConstants.SERVER_STATE_SHUTDOWN;
            }
        }
        else
        {
            Server server = new Server();
            
            if (!_isDebugging)
            {
                server.setLogWriter(null);
                server.setErrWriter(null);
            }
            else
            {
                _log.debug("starting server for " + dbNamePath);            
            }
      
            // the file name is full path and alias.
            String dbFileName = dbNamePath + File.separator + dbAlias;
      
            server.setDatabasePath(0, dbFileName);
            server.setDatabaseName(0, dbAlias);
            
            if(dbPort != null && dbPort.length() > 0)
            {
                try
                {
                    int port = Integer.parseInt(dbPort);
                    server.setPort(port);
                }
                catch(NumberFormatException e)
                {
                    System.out.print("ERROR: bad port " + dbPort + ": " +
                        e.getMessage());
                }
            }
            
            server.setSilent(true);
            server.setTrace(false);
            server.setNoSystemExit(true);
            server.start();        
                    
            _servers.add(server);
            
            return server.getState();
        }
    }
    
    /** Checkpoint any servers to compact their size. This rewrites
     *  the backing files for the databases.
     */
    public static void checkpointAllServers()
    {
        for(Connection connection : _serverConnectionSet)
        {
            Statement statement = null;
            try
            {
                try
                {
                    //long startTime = System.nanoTime();

                    //System.out.print("checkpointing....");
                    
                    statement = connection.createStatement();
                    statement.execute("CHECKPOINT DEFRAG");
                    
                    //long estimatedTime = System.nanoTime() - startTime;
                    
                    //System.out.println("done; took " +
                        //(double)(estimatedTime / 1000000000) + "s");

                }
                finally
                {
                    if(statement != null)
                    {
                        statement.close();
                    }
                }
            }
            catch(SQLException e)
            {
                //System.out.println("Error checkpointing database: " +
                    //e.getMessage());
            }
        }
    }
    
    /** Increment the reference count for a URL. */
    private boolean _updateConnectionReferenceCount(Connection connection, String urlStr) throws SQLException
    {
        // the reference count should be incremented for URLs with direct file access
        // or if the URL is for a server and servers are run in a separate process.
        if((!_forkServers && !urlStr.startsWith("jdbc:hsqldb:hsql") && !urlStr.startsWith("jdbc:hsqldb:http")) ||
            (_forkServers && urlStr.startsWith("jdbc:hsqldb:hsql")))
        {
            if(_isDebugging)
            {
                _log.debug(urlStr + " appears to access file directly.");
            }
            
            boolean isNew = false;
            
            // it does not, so we are accessing the database file
            // directly, and must issue shutdown in disconnect().

            synchronized(_urlMapLock)
            {
                Integer count = _urlToCountMap.get(urlStr);
                if(count == null)
                {
                    count = Integer.valueOf(1);
                    isNew = true;
                }
                else
                {
                    count++;
                }
                
                _urlToCountMap.put(urlStr, count);
                _connectionToURLMap.put(connection, urlStr);
            }
            
            if(isNew)
            {
                _initializeNewURL(connection);
            }
            
            return isNew;
        }
        
        return false;
    }
    
    /** Check if an HSQL database is 1.x and upgrade it to 2.x.
     * 
     * @param databaseName the full path and name (but not the extension) of the database file.
     * @param aliasStr the database alias
     * @param userName the user name to access the database
     * @param passwd the password to access the database
     * 
     */
    private void _upgradeIfNecessary(String databaseName, String aliasStr, String userName, String passwd)
            throws Exception
    {
        
        File propertiesFile = new File(databaseName + ".properties");
        
        // see if the database exists
        if(propertiesFile.exists()) {
            
            // check the version in the properties file
            Properties properties = HSQLUtils.readPropertiesFile(propertiesFile);
    
            final String version = properties.getProperty("version");
            if(version.startsWith("1")) {
                
                StatusNotifier.log("Upgrading " + aliasStr + " (this can take several minutes).");
                
                // see if a log file exists. if so, run CleanupHSQL1, which commits the
                // transactions in the log file.
                
                final File logFile = new File(databaseName + ".log");                
                if(logFile.exists()) {
                    
                    System.out.println("Log file found for " + aliasStr + ". Will run CleanupHSQL1.");
                    
                    // find the hsql 1 jar
                    ModuleTree tree = ModuleTree.instance();
                    Module module = tree.getModuleByStemName("core");
                    if(module == null) {
                        throw new Exception("Could not find core module in current suite.");
                    }
                                        
                    String hsql1Jar = null;
                    
                    FileSet files = new FileSet();
                    files.setProject(ProjectLocator.getAntProject());
                    files.setDir(module.getLibDir());
                    files.setIncludes("**/" + _HSQLDB_1_JAR);
                    Iterator<Resource> iterator = files.iterator();
                    if(iterator.hasNext()) {
                        Resource resource = iterator.next();
                        if(resource instanceof FileResource) {
                            File file = ((FileResource)resource).getFile();
                            hsql1Jar = file.getAbsolutePath();
                        }
                    }
                    
                    if(hsql1Jar == null) {
                        throw new Exception("Could not find " + _HSQLDB_1_JAR + ".");
                    }

                    // find CleanupHSQL1 jar
                    
                    String directory = new File(hsql1Jar).getParent();
                    File cleanupJar = new File(directory, _CLEANUP_HSQL_1_JAR);
                    
                    if(!cleanupJar.exists()) {
                        throw new Exception("Could not find " + _CLEANUP_HSQL_1_JAR);
                    }

                    ProcessBuilder builder = new ProcessBuilder("java", "-cp",
                            hsql1Jar + ":" + cleanupJar, "org.kepler.util.sql.CleanupHSQL1",
                            databaseName, userName);
                    builder.redirectErrorStream(true);
                    Process process = builder.start();
                    try(InputStream stdoutStream = process.getInputStream();) {
                        IOUtils.copy(stdoutStream, System.out);                        
                    }
                    
                    int rc = process.waitFor();
                    if(rc != 0) {
                        System.out.println("WARNING: CleanupHSQL1 returned " + rc);
                    }
                }
    
                // NOTE: need to make password upper-case for 1.x

                try(Connection connection = DriverManager.getConnection("jdbc:hsqldb:file:" + databaseName,
                        userName, passwd.toUpperCase());
                    Statement statement = connection.createStatement();) {
                    statement.execute("SHUTDOWN COMPACT");
                }

                // change the password since 2.x passwords are case-sensitive
                
                //System.out.println("changing password to " + passwd);
                
                try(Connection connection = DriverManager.getConnection("jdbc:hsqldb:file:" + databaseName,
                        userName, passwd.toUpperCase());
                    Statement statement = connection.createStatement();) {
                    statement.execute("ALTER USER " + userName + " SET PASSWORD \"" + passwd + "\"");
                    // checkpoint the database so the password change is made persistent.
                    statement.execute("CHECKPOINT");
                    statement.execute("SHUTDOWN");
                }
            }
            
        }
    }
        
    /** Perform initialization for a URL that has not yet been used. */
    private void _initializeNewURL(Connection connection) throws SQLException
    {
        //System.out.println("initializing for new URL. connection = " + connection);
        
        // increase number of rows cached in memory
        _executeSQL(_SET_CACHE_SCALE, connection);
        
        // HSQL 2.x: do not require VARCHAR columns to require a size. 
        // this is for backwards-compatibility with HSQL 1.x.
        _executeSQL("SET DATABASE SQL SIZE FALSE", connection);

        // HSQL 2.x: make shutdown faster by incrementally backing up the .data file.
        _executeSQL("SET FILES BACKUP INCREMENT TRUE", connection);
    }
    
    /** Execute an SQL statement for a Connection.
     *  TODO move to parent class
     */
    protected void _executeSQL(String sqlStr, Connection connection) throws SQLException 
    {
        try(Statement statement = connection.createStatement();) {
            statement.execute(sqlStr);
        }
    }

    /** Delete the port file for a database. */
    protected static void _deletePortFile(String dbName)
    {
        String portFileName = dbName + _PORT_FILE_EXTENSION;
        File portFile = new File(portFileName);
        if(portFile.exists() && !portFile.delete())
        {
            System.out.println("WARNING: Could not delete port file " + portFileName);
        }
    }
    
    ///////////////////////////////////////////////////////////////////
    // private variables
  
    /** A regex to find the port, alias, and file path in an hsql jdbc url. */
    private static final Pattern _jdbcURLPattern = 
        Pattern.compile("jdbc:hsqldb:hsql://[\\w/]+:(\\d+)/(\\w+)\\;filepath=hsqldb\\:(.*)");

    /** SQL state string returned in SQLException when trying to connect
     *  to a HSQL server that is not running.
     */
    private static final String _SERVER_NOT_RUNNING_SQL_STATE = "08000";
    private static final String _SERVER_NOT_RUNNING_SQL_STATE_2 = "08001";

    /** SQL statement to set write delay. */ 
    private static final String _SQL_SET_WRITE_DELAY = "SET WRITE_DELAY TRUE";
        
    /** A collection of HSQL server connections that we start. */
    private static final Set<Connection> _serverConnectionSet =
        new HashSet<Connection>();
    
    /** A collection of Server objects. */
    private static final Set<Server> _servers = Collections.synchronizedSet(new HashSet<Server>());
    
    /** A mapping of URLs to a count. (Only for URLs that directly access the file.) */
    private static final Map<String,Integer> _urlToCountMap = new HashMap<String,Integer>();
    
    /** A mapping of Connections to URL strings. (Only for URLs that directly access the file.) */
    private static final Map<Connection,String> _connectionToURLMap = new HashMap<Connection,String>();
        
    /** A lock to synchronize access for _urlToCountMap and _connectionToURLMap. */
    private static final Object _urlMapLock = new Object();
    
    /** SQL statement to set the maximum number of rows of cached tables that
     *  are held in memory. Setting to 18 improves performance.
     *  see: http://bugzilla.ecoinformatics.org/show_bug.cgi?id=5429#c2 
     */ 
    private static final String _SET_CACHE_SCALE = "SET PROPERTY \"hsqldb.cache_scale\" 18";

    /** If true, HSQL servers are run in a separate process. */
    private static boolean _forkServers = false;
    
    /** Logging */
    private static final Log _log = LogFactory.getLog(HSQL.class.getName());
    private static final boolean _isDebugging = _log.isDebugEnabled();
    
    /** Extension of the port file containing the server port. */
    private static final String _PORT_FILE_EXTENSION = ".port";

    /** Name of port property in port file. */
    private static final String _PORT_FILE_PASSWORD_PROP_NAME = "port";
   
    /** Random number generator used to generate random port numbers. */
    private static final Random _random = new Random();
    
    /** A mapping of ports randomly chosen for the server to database names. */
    private static final Map<String,String> _serverPortToName = new HashMap<String,String>();

    /** The database path and name. */
    private String _dbName;
    
    /** Default user name. */
    private static final String DEFAULT_USER_NAME = "sa";
    
    /** The name of the HSQL 1.x jar. */
    private static final String _HSQLDB_1_JAR = "hsqldb-1.8.1.3.jar";
    
    /** The name of the cleanup jar. */
    private static final String _CLEANUP_HSQL_1_JAR = "CleanupHSQL1.jar";

}