Application: gvSIG desktop

/* gvSIG. Geographic Information System of the Valencian Government

 *

 * Copyright (C) 2007-2020 Infrastructures and Transports Department

 * of the Valencian Government (CIT)

 *

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 3

 * of the License, or (at your option) any later version.

 *

 * 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.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,

 * MA  02110-1301, USA.

 *

 */

package org.gvsig.fmap.dal.store.h2;

import java.io.File;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import org.apache.commons.dbcp.BasicDataSource;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.GeometrySupportType;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.FIELD_CONFIGURATION_NAME;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.FIELD_CONFIGURATION_VALUE;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.FIELD_RESOURCES_NAME;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.FIELD_RESOURCES_RESOURCE;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.TABLE_CONFIGURATION_NAME;

import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.TABLE_RESOURCES_NAME;

import org.gvsig.fmap.dal.exception.InitializeException;

import org.gvsig.fmap.dal.resource.exception.AccessResourceException;

import org.gvsig.fmap.dal.spi.DataServerExplorerProviderServices;

import org.gvsig.fmap.dal.spi.DataTransactionServices;

import static org.gvsig.fmap.dal.store.h2.H2SpatialHelper.LOGGER;

import org.gvsig.fmap.dal.store.h2.functions.Json_value;

import org.gvsig.fmap.dal.store.h2.functions.Reverse;

import org.gvsig.fmap.dal.store.h2.functions.Reverseinstr;

import org.gvsig.fmap.dal.store.h2.operations.H2SpatialOperationsFactory;

import org.gvsig.fmap.dal.store.jdbc.JDBCConnectionParameters;

import org.gvsig.fmap.dal.store.jdbc.JDBCNewStoreParameters;

import org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorerParameters;

import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCDriverClassNotFoundException;

import org.gvsig.fmap.dal.store.jdbc2.JDBCConnection;

import org.gvsig.fmap.dal.store.jdbc2.JDBCServerExplorer;

import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils;

import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory;

import org.gvsig.fmap.dal.store.jdbc2.spi.AbstractConnectionProvider;

import org.gvsig.fmap.dal.store.jdbc2.spi.ConnectionProvider;

import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCConnectionBase;

import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCHelperBase;

import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase;

import org.gvsig.fmap.dal.store.jdbc2.spi.SRSSolverDumb;

import org.h2gis.functions.factory.H2GISFunctions;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

@SuppressWarnings("UseSpecificCatch")

public class H2SpatialHelper extends JDBCHelperBase {

    @SuppressWarnings("FieldNameHidesFieldInSuperclass")

    protected static final Logger LOGGER = LoggerFactory.getLogger(H2SpatialHelper.class);

    public static final String H2SPATIAL_JDBC_DRIVER = "org.h2.Driver";

    public static class ConnectionProviderImpl extends AbstractConnectionProvider implements ConnectionProvider {

        private static boolean needRegisterDriver = true;

        private BasicDataSource dataSource = null;

        private H2SpatialConnectionParameters connectionParameters;

        public ConnectionProviderImpl(H2SpatialConnectionParameters connectionParameters) {

            this.connectionParameters = connectionParameters;

        }

        @Override

        public String getStatus() {

            if(dataSource == null) {

                return "Not using pool.";

            }

            StringBuilder builder = new StringBuilder();

            builder.append("Pool: ");

            builder.append(JDBCUtils.getHexId(dataSource));

            builder.append(" Actives: ");

            builder.append(dataSource.getNumActive());

            builder.append("/");

            builder.append(dataSource.getMaxActive());

            builder.append(" idle: ");

            builder.append(dataSource.getNumIdle());

            builder.append("/");

            builder.append(dataSource.getMinIdle());

            builder.append(":");

            builder.append(dataSource.getMaxIdle());

            return builder.toString();

        }

        @SuppressWarnings("ConvertToTryWithResources")

        public void shutdown() {

            LOGGER.info("Shutdown H2 connection.");

            try {

                Connection conn = this.getConnection();

                conn.createStatement().execute("CHECKPOINT SYNC");

                conn.createStatement().execute("ANALYZE");

                conn.createStatement().execute("SHUTDOWN COMPACT");

                conn.close();

                LOGGER.info("Shutdown completed.");

            } catch (Throwable th) {

                LOGGER.warn("Problems shutdown the database.", th);

            }

            closeDataSource();

        }

        @Override

        public String toString() {

            StringBuilder builder = new StringBuilder();

            builder.append(" url=").append(connectionParameters.getUrl());

            builder.append(" driver name=").append(connectionParameters.getJDBCDriverClassName());

            builder.append(" user=").append(connectionParameters.getUser());

            return builder.toString();

        }

        @Override

        public synchronized Connection getConnection() throws SQLException {

            File f = H2SpatialUtils.getLocalFile(connectionParameters);

            boolean newdb = f!=null && !f.exists(); 

            Connection conn;

            try {

                conn = DriverManager.getConnection(

                    connectionParameters.getUrl(), 

                    connectionParameters.getUser(), 

                    connectionParameters.getPassword()

                );

            } catch(Throwable th) {

                throw th;

            }

            H2SpatialUtils.server_start(

                    this.connectionParameters.getServerPortAsString(), 

                    this.connectionParameters.getServerAllowOthers()

            );

//            if (this.dataSource == null) {

//                this.dataSource = this.createDataSource();               

//            }

//            

//            try {

//                conn = this.dataSource.getConnection();

//            } catch(Throwable th) {

//                LOGGER.warn("Can't create connection to '"+this.dataSource.getUrl()+"'. "+this.getStatus());

//                LOGGER.warn("Can't create connection to '"+this.dataSource.getUrl()+"'.",th);

//                throw th;

//            }

            try {

                conn.createStatement().execute("SELECT TOP 1 SRID FROM SPATIAL_REF_SYS");

            } catch(SQLException ex) {

                H2GISFunctions.load(conn);

            }

            String[] sqls = new String[] {

                "CREATE SCHEMA IF NOT EXISTS PUBLIC;SET SCHEMA PUBLIC",

                        Json_value.getSQL(),

                        Reverse.getSQL(),

                        Reverseinstr.getSQL()

            };

            for (String sql : sqls) {

                try {

                    conn.createStatement().execute(sql);

                } catch(SQLException ex) {

                    LOGGER.debug("Can't configure gvsig tables.",ex);

                    LOGGER.warn("Can't configure gvsig tables. "+sql);

                    // Ignore this error.

                }

            }

            if( newdb ) {

                    String[] sqls2 = new String[] {

                        "CREATE CACHED TABLE PUBLIC.\""+TABLE_RESOURCES_NAME+"\"(\""+FIELD_RESOURCES_NAME+"\" VARCHAR(150) NOT NULL, \""+FIELD_RESOURCES_RESOURCE+"\" BLOB DEFAULT NULL)",

                        "ALTER TABLE PUBLIC.\""+TABLE_RESOURCES_NAME+"\" ADD CONSTRAINT PUBLIC.CONSTRAINT_E PRIMARY KEY(\""+FIELD_RESOURCES_NAME+"\")",

                        "CREATE CACHED TABLE PUBLIC.\""+TABLE_CONFIGURATION_NAME+"\"(\""+FIELD_CONFIGURATION_NAME+"\" VARCHAR(200) NOT NULL, \""+FIELD_CONFIGURATION_VALUE+"\" CLOB DEFAULT NULL)",

                        "ALTER TABLE PUBLIC.\""+TABLE_CONFIGURATION_NAME+"\" ADD CONSTRAINT PUBLIC.CONSTRAINT_2 PRIMARY KEY(\""+FIELD_CONFIGURATION_NAME+"\")",

                    };

                    for (String sql : sqls2) {

                        try {

                            conn.createStatement().execute(sql);

                        } catch(SQLException ex) {

                            LOGGER.debug("Can't configure gvsig tables.",ex);

                            LOGGER.warn("Can't configure gvsig tables. "+sql);

                            // Ignore this error.

                        }

                    }

            }

            return conn;

        }

        private BasicDataSource createDataSource() throws SQLException {

            if (!this.isRegistered()) {

                this.registerDriver();

            }

            H2SpatialConnectionParameters params = connectionParameters;

            BasicDataSource ds = new BasicDataSource();

            ds.setDriverClassName(params.getJDBCDriverClassName());

            if( !StringUtils.isEmpty(params.getUser()) ) {

                ds.setUsername(params.getUser());

            }

            if( !StringUtils.isEmpty(params.getPassword()) ) {

                ds.setPassword(params.getPassword());

            }

            ds.setUrl(params.getUrl());

            ds.setMaxWait(60L * 1000);

            //

            // Ajustamos el pool para que las conexiones se cierren a los

            // 10 segundos, asi tratamos de que al salir de gvSIG no queden

            // conexiones abiertas con la BBDD y pueda quedar corrupta esta.

            // Hay que tener en cuenta que es una BBDD embebida, y mientras

            // hayan conexiones abiertas pueden quedar cosas por bajar a disco.

            //

            int sidle = this.connectionParameters.getMaxSecondsIdle();

            if( sidle < 0 ) {

                ds.setTimeBetweenEvictionRunsMillis(-1);

                ds.setMinEvictableIdleTimeMillis(30*1000);

            } else {

                // Revisamos las conexiones inactivas cada 10 segundos

                ds.setTimeBetweenEvictionRunsMillis(sidle*1000);

                // Eliminadmos las conexiones que lleven inactivas mas de 10 segundos.

                ds.setMinEvictableIdleTimeMillis(sidle*1000);

            }

            // Ajustamos el numero minimo de conexiones a 0 para permitir

            // que se lleguen a cerrar todas las conexiones del pool.

            ds.setMinIdle(0);

            // dejaremos el MaxIdle a 20, no parece importante. .

            ds.setMaxIdle(20);

            return ds;

        }

        private boolean isRegistered() {

            return needRegisterDriver;

        }

        @Override

        public void registerDriver() throws SQLException {

            String className = this.connectionParameters.getJDBCDriverClassName();

            if (className == null) {

                return;

            }

            try {

                Class theClass = Class.forName(className);

                if (theClass == null) {

                    throw new JDBCDriverClassNotFoundException(H2SpatialLibrary.NAME, className);

                }

            } catch (Exception e) {

                throw new SQLException("Can't register JDBC driver '" + className + "'.", e);

            }

            needRegisterDriver = false;

        }

        @Override

        public void dispose() {

            closeDataSource();

            this.connectionParameters = null;

        }

        private void closeDataSource() {

            try {

                if( dataSource!=null ) {

                    LOGGER.info("Clossing connection pool.");

                    LOGGER.info(this.getStatus());

                    dataSource.close();

                    dataSource = null;

                    LOGGER.info("Connection pool closed.");

                    LOGGER.info(this.getStatus());

                }

            } catch (Throwable th) {

                LOGGER.warn("Problems closing connections pool.", th);

            }

        }

        @Override

        public boolean isDisposed() {

            return this.connectionParameters == null;

        }

    }

    private ConnectionProvider connectionProvider = null;

    /**

     * Constructor for use only for testing purposes.

     * 

     * @param connectionParameters

     * @param connectionProvider

     */

    public H2SpatialHelper(JDBCConnectionParameters connectionParameters, ConnectionProvider connectionProvider) { 

        super(connectionParameters);

        this.srssolver = new SRSSolverDumb(this);

        this.connectionProvider = connectionProvider;

    }

    public H2SpatialHelper(JDBCConnectionParameters connectionParameters) {

        super(connectionParameters);

//        this.srssolver = new SRSSolverBase(this);

        this.srssolver = new SRSSolverDumb(this); //Tratando de reducir accesos a la BBDD ? Ponerlo en los parametros ?

    }

    public void  shutdown() {

        try {

            try {

                this.getConnection();

            } catch(Throwable th) {

                LOGGER.debug("Can't get connection-provider",th);

            }

            if( this.connectionProvider!=null ) {

                ((ConnectionProviderImpl) this.connectionProvider).shutdown();

                this.connectionProvider = null;

            }

            H2SpatialUtils.server_stop();

        } catch (Throwable ex) {

            LOGGER.warn("Problems shutdown H2", ex);

        }

    }

    private void logConnectionStatus(String msg, Connection conn) {

        ConnectionProvider cp = this.getConnectionProvider();

        StringBuilder builder = new StringBuilder();

        builder.append(msg);

        if( conn == null ) {

            builder.append(": connection null");

        } else {

            Boolean closed = null;

            try {

                closed = conn.isClosed();

            } catch(Throwable th) {

            }

            builder.append(": connection ");

            builder.append(JDBCUtils.getConnId(conn));

            if( closed ) {

                builder.append(" (c)");

            }

            builder.append(" ");

        }

        builder.append(cp.getStatus());

        LOGGER.info(builder.toString());

    }

    @Override

    public ConnectionProvider getConnectionProvider() {

        if (this.connectionProvider == null) {

          H2SpatialConnectionParameters connectionParameters = this.getConnectionParameters();

          if( connectionParameters==null ) {

            return null; // Testing mode?

          }

          this.connectionProvider = new ConnectionProviderImpl(connectionParameters);

        }

        return this.connectionProvider;

    }

    @Override

    public synchronized JDBCConnection  getConnection() throws AccessResourceException {

        try {

            H2SpatialConnectionParameters connectionParameters = this.getConnectionParameters();

            JDBCConnection conn = (JDBCConnection) DataTransactionServices.getConnection(

                    transaction, 

                    this.getConnectionProviderKey(connectionParameters)

            );

            if( conn != null ) {

                return conn;

            }

            if (this.connectionProvider == null) {

              if( connectionParameters==null ) {

                return null; // Testing mode?

              }

              this.connectionProvider = new ConnectionProviderImpl(connectionParameters);

            }

            JDBCConnection connection = new JDBCConnectionBase(

                    this.transaction, 

                    this.connectionProvider.getConnection(), 

                    this.getConnectionProviderKey(connectionParameters)

            );

            if( LOGGER.isDebugEnabled() ) {

                LOGGER.debug("["+JDBCUtils.getConnId(connection.get())+"] getConnection "+connectionProvider.getStatus()+" "+ connectionProvider.toString());

            }

            return connection;

        } catch (SQLException ex) {

            throw new AccessResourceException(H2SpatialLibrary.NAME, ex);

        }

    }

    @Override

    public H2SpatialConnectionParameters getConnectionParameters() {

        return (H2SpatialConnectionParameters) super.getConnectionParameters();

    }

    @Override

    public String getConnectionURL() {

        return H2SpatialUtils.getConnectionURL(this.getConnectionParameters());

    }

    @Override

    protected String getResourceType() {

        return H2SpatialLibrary.NAME;

    }

    @Override

    public String getProviderName() {

        return H2SpatialLibrary.NAME;

    }

    @Override

    public JDBCSQLBuilderBase createSQLBuilder() {

        return new H2SpatialSQLBuilder(this);

    }

    @Override

    public OperationsFactory getOperations() {

        if (this.operationsFactory == null) {

            this.operationsFactory = new H2SpatialOperationsFactory(this);

        }

        return operationsFactory;

    }

    @Override

    public GeometrySupportType getGeometrySupportType() {

        return GeometrySupportType.WKB;

    }

    @Override

    public boolean hasSpatialFunctions() {

        return true;

    }

    @Override

    public boolean canWriteGeometry(int geometryType, int geometrySubtype) {

        return true;

    }

    @Override

    public String getQuoteForIdentifiers() {

        return "\"";

    }

    @Override

    public boolean allowAutomaticValues() {

        return true;

    }

    @Override

    public boolean supportOffsetInSelect() {

        return true;

    }

    @Override

    public String getQuoteForStrings() {

        return "'";

    }

    @Override

    public String getSourceId(JDBCStoreParameters parameters) {

        H2SpatialStoreParameters params = (H2SpatialStoreParameters) parameters;

        StringBuilder builder = new StringBuilder();

        builder.append(params.getTable());

        builder.append("(");

        boolean needComma = false;

        if( StringUtils.isNotBlank(params.getHost()) ) {

            builder.append("host=");

            builder.append(params.getHost());

            needComma = true;

        }

        if( params.getPort()>0 ) {

            if (needComma ) {

                builder.append(", ");

            }

            builder.append("port=");

            builder.append(params.getPort());

            needComma = true;

        }

        if( StringUtils.isNotBlank(params.getDBName()) ) {

            if (needComma ) {

                builder.append(", ");

            }

            builder.append("db=");

            builder.append(params.getDBName());

            needComma = true;

        }

        if( StringUtils.isNotBlank(params.getSchema()) ) {

            if (needComma ) {

                builder.append(", ");

            }

            builder.append("schema=");

            builder.append(params.getSchema());

            needComma = true;

        }

        File f = params.getFile();       

        if( f != null) {

            if (needComma ) {

                builder.append(", ");

            }

            builder.append("file=");

            builder.append(params.getFile().getAbsolutePath());

        }

        builder.append(")");

        return builder.toString();

    }

    @Override

    public JDBCNewStoreParameters createNewStoreParameters() {

        return new H2SpatialNewStoreParameters();

    }

    @Override

    public JDBCStoreParameters createOpenStoreParameters() {

        return new H2SpatialStoreParameters();

    }

    @Override

    public JDBCServerExplorerParameters createServerExplorerParameters() {

        return new H2SpatialExplorerParameters();

    }

    @Override

    public JDBCServerExplorer createServerExplorer(

            JDBCServerExplorerParameters parameters, 

            DataServerExplorerProviderServices providerServices

        ) throws InitializeException {

        JDBCServerExplorer explorer = new H2SpatialExplorer(

                parameters, 

                providerServices, 

                this

        );

        this.initialize(explorer, parameters, null);

        return explorer;

    }

    @Override

    public String getConnectionProviderStatus(){

        return this.getConnectionProvider().getStatus();

    }

    @Override

    public JDBCStoreParameters createOpenStoreParameters(JDBCServerExplorerParameters parameters) {

        JDBCStoreParameters p = super.createOpenStoreParameters(parameters); 

        if(StringUtils.isBlank(p.getCatalog())){

            p.setCatalog(StringUtils.upperCase(p.getDBName()));

        }

        return p;

    }

}