Application: gvSIG desktop

/* gvSIG. Geographic Information System of the Valencian Government

 *

 * Copyright (C) 2007-2016 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.mdb;

import java.io.File;

import java.sql.Clob;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.text.MessageFormat;

import org.apache.commons.dbcp.BasicDataSource;

import org.apache.commons.io.IOUtils;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.GeometrySupportType;

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

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.store.mdb.operations.MDBOperationsFactory;

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.JDBCCantFetchValueException;

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

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.ConnectionProvider;

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.JDBCServerExplorerBase;

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

import org.gvsig.fmap.geom.Geometry;

//import org.h2.tools.Server;

//import org.h2gis.ext.H2GISExtension;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

@SuppressWarnings("UseSpecificCatch")

public class MDBHelper extends JDBCHelperBase {

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

    public static final String MDB_JDBC_DRIVER = "net.ucanaccess.jdbc.UcanaccessDriver";

    public static String getConnectionURL(MDBConnectionParameters params) {

        String connectionURL;

        String dbfilename = params.getFile().getAbsolutePath().replace("\\","/");

//        if( dbfilename!=null && dbfilename.endsWith(".mdb") ) {

//            dbfilename = dbfilename.substring(0, dbfilename.length()-3);

//        }

        StringBuilder commonParameters = new StringBuilder();

//        commonParameters.append(";MODE=PostgreSQL");

//        commonParameters.append(";SCHEMA=PUBLIC");

//        Integer LOCK_TIMEOUT = (Integer) params.getDynValue("LOCK_TIMEOUT");

//        if( LOCK_TIMEOUT!=null ) {

//            commonParameters.append(";LOCK_TIMEOUT=").append(LOCK_TIMEOUT);

//        }

//        Integer MULTI_THREADED = (Integer) params.getDynValue("MULTI_THREADED");

//        if( MULTI_THREADED!=null ) {

//            commonParameters.append(";MULTI_THREADED=").append(MULTI_THREADED);

//        }

//        Integer CHACHE_SIZE = (Integer) params.getDynValue("CHACHE_SIZE");

//        if( LOCK_TIMEOUT!=null ) {

//            commonParameters.append(";CHACHE_SIZE=").append(CHACHE_SIZE);

//        }

//        Integer LOG = (Integer) params.getDynValue("LOG");

//        if( LOCK_TIMEOUT!=null ) {

//            commonParameters.append(";LOG=").append(LOG);

//        }

//        Integer LOCK_MODE = (Integer) params.getDynValue("LOCK_MODE");

//        if( LOCK_TIMEOUT!=null ) {

//            commonParameters.append(";LOCK_MODE=").append(LOCK_MODE);

//        }

        if (!params.getFile().exists()) {

            commonParameters.append("newdatabaseversion=V2010;");

        }

        commonParameters.append("showSchema=true;");

        // http://ucanaccess.sourceforge.net/site.html#examples

        connectionURL =  MessageFormat.format(                

                "jdbc:ucanaccess://{0};"+commonParameters.toString(),

                dbfilename

        );

        LOGGER.debug("connectionURL: {}", connectionURL);

        return connectionURL;

    }

    public static class ConnectionProviderImpl implements ConnectionProvider {

        private static boolean needRegisterDriver = true;

        private BasicDataSource dataSource = null;

        private final MDBConnectionParameters connectionParameters;

//        private static Server server = null;

        private static String server = null;

        private static boolean startServer = true;

        public ConnectionProviderImpl(MDBConnectionParameters connectionParameters) {

            this.connectionParameters = connectionParameters;

        }

        @Override

        public String getStatus() {

            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();

        }

        public static void stopServer() {

          try {

//            server.shutdown();

          } catch(Throwable th) {

          }

          try {

//            server.stop();

          } catch(Throwable th) {

          }

//          server = null;

          startServer = true;

          LOGGER.info("MDB Server stopped" );

        }

        private void startServer() {

            if( startServer && server == null ) {

                String port = "9123";

//                Server theServer;

                String s = System.getProperty("MDBPort");

                if( s!=null ) {

                    try {

                        int n = Integer.parseInt(s);

                        port = String.valueOf(n);

                    } catch(Throwable th) {

                        // Ignore port number, use default.

                    }

                }

                if( System.getProperty("MDBAllowOthers")!=null ) {

//                    theServer = Server.createTcpServer("-tcpPort", port, "-tcpAllowOthers", "-ifExists");

                } else {

//                    theServer = Server.createTcpServer("-tcpPort", port, "-ifExists");

                }

//                theServer.start();

//                server = theServer;

                LOGGER.info("MDB Server started" );

//                LOGGER.info("  port  :"+ server.getPort());

//                LOGGER.info("  URL  :"+ server.getURL());

//                LOGGER.info("  status:"+ server.getStatus());

                // Tanto si consigue lanzar el server como si no, no lo vuelve a intentar

                startServer = false;

            }

        }

        @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();

        }

        public Connection getConnection() throws SQLException {

            File f = this.connectionParameters.getFile();

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

            if (this.dataSource == null) {

                this.dataSource = this.createDataSource();               

            }

            Connection conn = this.dataSource.getConnection();

////            try {

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

////            } catch(SQLException ex) {

//////                H2GISExtension.load(conn);

////            }

//            try {

////                conn.createStatement().execute("CREATE SCHEMA IF NOT EXISTS PUBLIC;SET SCHEMA PUBLIC");

//            } catch(SQLException ex) {

//                // Ignore this error.

//            }

            if (newdb) {

                //crea tablas

                //

            }

            return conn;

        }

        private BasicDataSource createDataSource() throws SQLException {

            if (!this.isRegistered()) {

                this.registerDriver();

            }

            startServer();

            MDBConnectionParameters 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);

            return ds;

        }

        private boolean isRegistered() {

            return needRegisterDriver;

        }

        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(MDBLibrary.NAME, className);

                }

            } catch (Exception e) {

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

            }

            needRegisterDriver = false;

        }

    }

    private ConnectionProvider connectionProvider = null;

        /**

     * Constructor for use only for testing purposes.

     * 

     * @param connectionParameters

     * @param connectionProvider

     */

    public MDBHelper(JDBCConnectionParameters connectionParameters, ConnectionProvider connectionProvider) { 

        super(connectionParameters);

        this.srssolver = new SRSSolverDumb(this);

        this.connectionProvider = connectionProvider;

    }

    public MDBHelper(JDBCConnectionParameters connectionParameters) {

        super(connectionParameters);

        this.srssolver = new SRSSolverDumb(this);

    }

    @Override

    public synchronized Connection  getConnection() throws AccessResourceException {

        try {

            if (this.connectionProvider == null) {

              MDBConnectionParameters connectionParameters = this.getConnectionParameters();

              if( connectionParameters==null ) {

                return null; // Testing mode?

              }

              this.connectionProvider = new ConnectionProviderImpl(connectionParameters);

            }

            Connection connection = this.connectionProvider.getConnection();

            if( LOGGER.isDebugEnabled() ) {

                LOGGER.debug("getConnection: connection = "+connection.hashCode()+ connectionProvider.toString());

            }

            return connection;

        } catch (SQLException ex) {

            throw new AccessResourceException(MDBLibrary.NAME, ex);

        }

    }

    @Override

    public void closeConnection(Connection connection) {

      if( connection!=null ) { // In test ???

        LOGGER.debug("closeConnection: connection = "+connection.hashCode());

      }

      super.closeConnection(connection);

    }

    @Override

    public MDBConnectionParameters getConnectionParameters() {

        return (MDBConnectionParameters) super.getConnectionParameters();

    }

    @Override

    public String getConnectionURL() {

        return getConnectionURL(this.getConnectionParameters());

    }

    @Override

    protected String getResourceType() {

        return MDBLibrary.NAME;

    }

    @Override

    public String getProviderName() {

        return MDBLibrary.NAME;

    }

    @Override

    public JDBCSQLBuilderBase createSQLBuilder() {

        return new MDBSQLBuilder(this);

    }

    @Override

    public OperationsFactory getOperations() {

        if (this.operationsFactory == null) {

            this.operationsFactory = new MDBOperationsFactory(this);

        }

        return operationsFactory;

    }

    @Override

    public GeometrySupportType getGeometrySupportType() {

        return GeometrySupportType.WKB;

    }

    @Override

    public boolean hasSpatialFunctions() {

        return false;

    }

    @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) {

        return parameters.getDBName() + "." + 

               parameters.getSchema()+ "." + 

               parameters.getTable();

    }

    @Override

    public JDBCNewStoreParameters createNewStoreParameters() {

        return new MDBNewStoreParameters();

    }

    @Override

    public JDBCStoreParameters createOpenStoreParameters() {

        return new MDBStoreParameters();

    }

    @Override

    public JDBCServerExplorerParameters createServerExplorerParameters() {

        return new MDBExplorerParameters();

    }

    @Override

    public JDBCServerExplorer createServerExplorer(

            JDBCServerExplorerParameters parameters, 

            DataServerExplorerProviderServices providerServices

        ) throws InitializeException {

        JDBCServerExplorer explorer = new JDBCServerExplorerBase(

                parameters, 

                providerServices, 

                this

        );

        this.initialize(explorer, parameters, null);

        return explorer;

    }

    @Override

  public Geometry getGeometryFromColumn(ResultSet rs, int index) throws DataException {

    try {

      Object value;

      Clob clob;

      String strGeom;

      switch (this.getGeometrySupportType()) {

        case NATIVE:

        case WKB:

            clob = rs.getClob(index);

            if (clob ==null) {

                return null;

            }

            strGeom = new String(IOUtils.toCharArray(clob.getCharacterStream()));

            clob.free();

            return this.getGeometryManager().createFrom(strGeom);

        case EWKB:

            clob = rs.getClob(index);

            if (clob ==null) {

                return null;

            }

            strGeom = new String(IOUtils.toCharArray(clob.getCharacterStream()));

            clob.free();

            return this.getGeometryManager().createFrom(strGeom);

        case WKT:

        default:

          value = rs.getString(index);

          if (value == null) {

            return null;

          }

          return this.getGeometryManager().createFrom((String) value);

      }

    } catch (Exception ex) {

      throw new JDBCCantFetchValueException(ex);

    }

  }

}