Application: gvSIG desktop » gvSIG data provider for PostgreSQL

package org.gvsig.postgresql.dal;

import java.sql.Blob;

import java.sql.Clob;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.HashMap;

import java.util.Map;

import org.apache.commons.dbcp2.BasicDataSource;

import org.apache.commons.io.IOUtils;

import org.apache.commons.lang3.ArrayUtils;

import org.apache.commons.lang3.BooleanUtils;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.GeometrySupportType;

import org.gvsig.fmap.dal.DataTypes;

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

import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.spi.FeatureProvider;

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

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

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

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

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

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.postgresql.dal.operations.PostgreSQLOperationsFactory;

import org.gvsig.tools.dispose.Disposable;

import org.gvsig.tools.dispose.DisposeUtils;

import org.gvsig.tools.exception.BaseException;

import org.postgresql.util.DriverInfo;

@SuppressWarnings("UseSpecificCatch")

public class PostgreSQLHelper extends JDBCHelperBase {

//    /* friend */ static final Logger LOGGER = LoggerFactory.getLogger(PostgreSQLHelper.class);

    public static final String POSTGRESQL_JDBC_DRIVER = "org.postgresql.Driver";

    public static final boolean USE_CONNECTIONS_POOL = true;

    public static String getConnectionURL(PostgreSQLConnectionParameters params) {

        return getConnectionURL(

            params.getHost(),

            params.getPort(),

            params.getDBName()

        );

    }

    public static String getConnectionURL(String host, Integer port, String db) {

        if( StringUtils.isEmpty(host) ) {

            throw new IllegalArgumentException("Parameter 'host' can't be null.");

        }

        String connectionURL = "jdbc:postgresql://" + host;

        if (port != null) {

            connectionURL = connectionURL + ":" + port;

        }

        connectionURL = connectionURL + "/" + db;

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

        return connectionURL;

    }

    private static class PostgreSQLConnectionProvider extends AbstractConnectionProvider implements Disposable, ConnectionProvider {

        private static boolean needRegisterDriver = true;

        private BasicDataSource dataSource = null;

        private PostgreSQLConnectionParameters connectionParameters;

        public PostgreSQLConnectionProvider(PostgreSQLConnectionParameters connectionParameters) {

            this.connectionParameters = connectionParameters;

            DisposeUtils.bind(this);

        }

        @Override

        public Connection getConnection() throws SQLException {

            Connection conn;

            if( USE_CONNECTIONS_POOL ) {

                if (this.dataSource == null) {

                    this.dataSource = this.createDataSource();               

                }

                if( LOGGER.isDebugEnabled() ) {

                    LOGGER.debug("getConnection:\n" + getStatusInformation());

                }

                try {

                    conn = this.dataSource.getConnection();

                } catch(Throwable ex) {

                    LOGGER.debug("Error getting connection from pool.",ex);

                    throw ex;

                }

                try {

                    conn.setNetworkTimeout(null, this.connectionParameters.getNetworkTimeout());

                } catch(Throwable ex) {

                    LOGGER.warn("Error setting the network timeout.",ex);

                }

                if( LOGGER.isDebugEnabled() ) {

                    LOGGER.debug("Created connection: {}\n  NumActive: {}\n  NumIdle: {}",

                        new Object[] {

                            conn.hashCode(), 

                            this.dataSource.getNumActive(),

                            this.dataSource.getNumIdle()

                        }

                    );

                }

            } else {

                try {

                    conn = DriverManager.getConnection(

                        connectionParameters.getUrl(), 

                        connectionParameters.getUser(), 

                        connectionParameters.getPassword()

                    );

                } catch(Throwable th) {

                    throw th;

                }

                if( LOGGER.isDebugEnabled() ) {

                    LOGGER.debug("Created not polled connection: {}",

                        new Object[] {

                            conn.hashCode()

                        }

                    );

                }

            }

            LOGGER.debug("PostgreSQL JDBC Driver: "+DriverInfo.DRIVER_VERSION);

            return conn;

        }

        public String getStatusInformation() {

            StringBuilder builder = new StringBuilder();

            if( this.dataSource==null ) {

                builder.append("Not poolled connection:\n");

                builder.append("  Connection URL: '").append(this.connectionParameters.getUrl()).append("'\n");

            } else {

                builder.append("BasicDataSource pool status:\n");

                builder.append("  Connection URL: '").append(this.dataSource.getUrl()).append("'\n");

                if( this.dataSource.getInitialSize()>0 ) {

                    builder.append("  InitialSize: ").append(this.dataSource.getInitialSize()).append(" (The initial number of connections that are created when the pool is started)\n");

                }

                if( this.dataSource.isPoolPreparedStatements() ) {

                    builder.append("  PoolPreparedStatements: ").append(this.dataSource.isPoolPreparedStatements()).append("\n");

                    builder.append("  MaxOpenPreparedStatements: ").append(this.dataSource.getMaxOpenPreparedStatements()).append(" (The maximum number of open statements that can be allocated from the statement pool at the same time, or non-positive for no limit)\n");

                }

                builder.append("  MaxTotal: ").append(this.dataSource.getMaxTotal()).append(" (The maximum number of active connections that can be allocated from this pool at the same time)\n");

                builder.append("  MaxIdle: ").append(this.dataSource.getMaxIdle()).append(" (The maximum number of connections that can remain idle in the pool)\n");

                builder.append("  NumActive:").append(this.dataSource.getNumActive()).append(" (the current number of active connections)\n");

                builder.append("  NumIdle:").append(this.dataSource.getNumIdle()).append(" (the current number of idle connections)\n");

            }

            return builder.toString();

        }

        private BasicDataSource createDataSource() throws SQLException {

            if (!this.isRegistered()) {

                this.registerDriver();

            }

            PostgreSQLConnectionParameters params = connectionParameters;

            BasicDataSource ds = new BasicDataSource();

            ds.setMaxIdle(params.getMaxIdle());

            ds.setDriverClassName(params.getJDBCDriverClassName());

            if( params.getUseSSL() ) {

                String s = BooleanUtils.toStringTrueFalse(params.getUseSSL());

                ds.addConnectionProperty("ssl", s );

            }

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

                ds.setUsername(params.getUser());

            }

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

                ds.setPassword(params.getPassword());

            }

            ds.setUrl(params.getUrl());

            ds.setMinEvictableIdleTimeMillis(20*1000);

            ds.setTimeBetweenEvictionRunsMillis(20*1000);

            ds.setMinIdle(0);

            ds.setMaxWaitMillis(60L * 1000);

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

                }

            } catch (Exception e) {

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

            }

            needRegisterDriver = false;

        }

        @Override

        public void dispose() {

            if(!DisposeUtils.release(this)){

                return;

            }

            if( this.dataSource!=null ) {

                try {

                    this.dataSource.close();

                } catch (SQLException ex) {

                    LOGGER.warn("Can't close BasicDataSource", ex);

                }

                this.dataSource = null;

            }

            this.connectionParameters = null;

        }

        @Override

        public boolean isDisposed() {

            return this.dataSource == null;

        }

        @Override

        public String getStatus() {

            if( dataSource==null ) {

                return "Not polled";

            }

            StringBuilder builder = new StringBuilder();

            builder.append("Pool: ");

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

            builder.append(" Actives: ");

            builder.append(dataSource.getNumActive());

            builder.append("/");

            builder.append(dataSource.getMaxTotal()); //.getMaxActive());

            builder.append(" idle: ");

            builder.append(dataSource.getNumIdle());

            builder.append("/");

            builder.append(dataSource.getMinIdle());

            builder.append(":");

            builder.append(dataSource.getMaxIdle());

            return builder.toString();

        }

    }

    static private Map<String,ConnectionProvider> connectionProviders = new HashMap();

    private ConnectionProvider connectionProvider = null;

    /**

     * Constructor for use only for testing purposes.

     * 

     * @param connectionParameters

     * @param connectionProvider

     */

    public PostgreSQLHelper(PostgreSQLConnectionParameters connectionParameters, ConnectionProvider connectionProvider) { 

        super(connectionParameters);

        this.srssolver = new SRSSolverDumb(this);

        this.connectionProvider = connectionProvider;

    }

    public PostgreSQLHelper(JDBCConnectionParameters connectionParameters) {

        super(connectionParameters);

        this.srssolver = new SRSSolverBase(this);

    }

    @Override

    protected void doDispose() throws BaseException {

        if( this.connectionProvider!=null ) {

            this.connectionProvider.dispose();

            this.connectionProvider = null;

        }

        super.doDispose(); 

    }

    private ConnectionProvider getConnectionProvider(JDBCConnectionParameters connectionParameters) {

        if (this.connectionProvider == null) {

            if (this.getConnectionParameters() == null) {

                return null;

            }

            String key = this.getConnectionProviderKey(connectionParameters);

            this.connectionProvider = connectionProviders.get(key);

            if (this.connectionProvider == null || ((PostgreSQLConnectionProvider) this.connectionProvider).isDisposed()) {

                this.connectionProvider = new PostgreSQLConnectionProvider(this.getConnectionParameters());

                connectionProviders.put(key, this.connectionProvider);

            } else {

                DisposeUtils.bind(this.connectionProvider);

            }

        }

        return this.connectionProvider;

    }

    @Override

    public synchronized JDBCConnection  getConnection() throws AccessResourceException {

        try {

            PostgreSQLConnectionParameters connectionParameters = this.getConnectionParameters();

            JDBCConnection conn = (JDBCConnection) DataTransactionServices.getConnection(

                    this.getTransaction(), 

                    this.getConnectionProviderKey(connectionParameters)

            );

            if( conn != null ) {

                return conn;

            }

            JDBCConnection connection = new JDBCConnectionBase(

                    this.getTransaction(), 

                    this.getConnectionProvider(connectionParameters).getConnection(), 

                    this.getConnectionProviderKey(connectionParameters)

            );

            return connection;

        } catch (SQLException ex) {

            throw new AccessResourceException(PostgreSQLLibrary.NAME, ex);

        }

    }

    @Override

    public PostgreSQLConnectionParameters getConnectionParameters() {

        return (PostgreSQLConnectionParameters) super.getConnectionParameters();

    }

    @Override

    public String getConnectionURL() {

        return getConnectionURL(this.getConnectionParameters());

    }

    @Override

    protected String getResourceType() {

        return PostgreSQLLibrary.NAME;

    }

    @Override

    public String getProviderName() {

        return PostgreSQLLibrary.NAME;

    }

    @Override

    public JDBCSQLBuilderBase createSQLBuilder() {

        return new PostgreSQLBuilder(this);

    }

    @Override

    public OperationsFactory getOperations() {

        if (this.operationsFactory == null) {

            this.operationsFactory = new PostgreSQLOperationsFactory(this);

        }

        return operationsFactory;

    }

    @Override

    public GeometrySupportType getGeometrySupportType() {

        return GeometrySupportType.EWKB;

    }

    @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 JDBCNewStoreParameters createNewStoreParameters() {

        return new PostgreSQLNewStoreParameters();

    }

    @Override

    public JDBCStoreParameters createOpenStoreParameters() {

        return new PostgreSQLStoreParameters();

    }

    @Override

    public JDBCServerExplorerParameters createServerExplorerParameters() {

        return new PostgreSQLServerExplorerParameters();

    }

    @Override

    public void fetchFeature(FeatureProvider feature, ResultSet rs, FeatureAttributeDescriptor[] columns, String[] extraValueNames) throws DataException {

        Object value;

        try {

            int rsIndex = 1;

            for (FeatureAttributeDescriptor column : columns) {

                switch (column.getType()) {

                    case DataTypes.GEOMETRY:

                        value = this.getGeometryFromColumn(rs, rsIndex++);

                        if (value != null){

                            ((Geometry)value).setProjection(column.getSRS());

                        }

                        break;

                    case DataTypes.BYTEARRAY:

                        value = rs.getBytes(rsIndex++);

                        break;

                    default:

                        value = rs.getObject(rsIndex++);

                        if (value instanceof Blob) {

                            Blob blob = (Blob) value;

                            value = blob.getBytes(1, (int) blob.length());

                            blob.free();

                        } else if (value instanceof Clob) {

                            Clob clob = (Clob) value;

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

                            clob.free();

                        }

                }

                feature.set(column.getIndex(), value);

            }

            if (ArrayUtils.isNotEmpty(extraValueNames)) {

                feature.setExtraValueNames(extraValueNames);

                for (int index = 0; index < extraValueNames.length; index++) {

                    value = rs.getObject(rsIndex++);

                    if (value instanceof Blob) {

                        Blob blob = (Blob) value;

                        value = blob.getBytes(0, (int) blob.length());

                        blob.free();

                    }

                    feature.setExtraValue(index, value);

                }

            }

        } catch (Exception ex) {

            throw new JDBCCantFetchValueException(ex);

        }

    }

}