gvSIG data provider for Oracle

package org.gvsig.oracle.dal;

import java.nio.ByteBuffer;

import java.nio.ByteOrder;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.text.MessageFormat;

import java.util.ArrayList;

import java.util.List;

import org.apache.commons.dbcp.BasicDataSource;

import org.apache.commons.lang3.StringUtils;

import org.cresques.cts.IProjection;

import org.gvsig.fmap.crs.CRSFactory;

import org.gvsig.fmap.dal.DataTypes;

import org.gvsig.fmap.dal.ExpressionBuilder;

import org.gvsig.fmap.dal.SQLBuilder;

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

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

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

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

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

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

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

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.geom.Geometry;

import org.gvsig.fmap.geom.aggregate.MultiLine;

import org.gvsig.fmap.geom.aggregate.MultiPoint;

import org.gvsig.fmap.geom.aggregate.MultiPolygon;

import org.gvsig.fmap.geom.exception.CreateGeometryException;

import org.gvsig.fmap.geom.primitive.Primitive;

import org.gvsig.fmap.geom.type.GeometryType;

import org.gvsig.oracle.dal.operations.OracleOperationsFactory;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class OracleHelper extends JDBCHelperBase {

    static final Logger logger = LoggerFactory.getLogger(OracleHelper.class);

    public static final String ORACLE_JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";

    public static String getConnectionURL(OracleConnectionParameters 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.");

        }

        if( port==null ) {

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

        }

        if( StringUtils.isEmpty(db) ) {

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

        }

        //

        // http://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleDriver.html

        //

        String connectionURL = MessageFormat.format(

            "jdbc:oracle:thin:@{0}:{1,number,######}:{2}",

            host,

            port,

            db

        );

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

        return connectionURL;

    }

    private static class ConnectionProvider {

        private static boolean needRegisterDriver = true;

        private BasicDataSource dataSource = null;

        private final OracleConnectionParameters connectionParameters;

        public ConnectionProvider(OracleConnectionParameters connectionParameters) {

            this.connectionParameters = connectionParameters;

        }

        public Connection getConnection() throws SQLException {

            if (this.dataSource == null) {

                this.dataSource = this.createDataSource();               

            }

            Connection conn = this.dataSource.getConnection();

            return conn;

        }

        private BasicDataSource createDataSource() throws SQLException {

            if (!this.isRegistered()) {

                this.registerDriver();

            }

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

                }

            } catch (Exception e) {

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

            }

            needRegisterDriver = false;

        }

    }

    private ConnectionProvider connectionProvider = null;

    public OracleHelper(JDBCConnectionParameters connectionParameters) {

        super(connectionParameters);

        this.srssolver = new SRSSolverBase(this);

    }

    @Override

    public Connection getConnection() throws AccessResourceException {

        try {

            if (this.connectionProvider == null) {

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

            }

            return this.connectionProvider.getConnection();

        } catch (SQLException ex) {

            throw new AccessResourceException(OracleLibrary.NAME, ex);

        }

    }

    @Override

    public OracleConnectionParameters getConnectionParameters() {

        return (OracleConnectionParameters) super.getConnectionParameters();

    }

    @Override

    public String getConnectionURL() {

        return getConnectionURL(this.getConnectionParameters());

    }

    @Override

    protected String getResourceType() {

        return OracleLibrary.NAME;

    }

    @Override

    public String getProviderName() {

        return OracleLibrary.NAME;

    }

    @Override

    public JDBCSQLBuilderBase createSQLBuilder() {

        return new OracleSQLBuilder(this);

    }

    @Override

    public OperationsFactory getOperations() {

        if (this.operationsFactory == null) {

            this.operationsFactory = new OracleOperationsFactory(this);

        }

        return operationsFactory;

    }

    @Override

    public SQLBuilder.GeometrySupportType getGeometrySupportType() {

        return SQLBuilder.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) {

        return parameters.getDBName() + "." + 

               parameters.getSchema()+ "." + 

               parameters.getTable();

    }

    @Override

    public JDBCNewStoreParameters createNewStoreParameters() {

        return new OracleNewStoreParameters();

    }

    @Override

    public JDBCStoreParameters createOpenStoreParameters() {

        return new OracleStoreParameters();

    }

    @Override

    public JDBCServerExplorerParameters createServerExplorerParameters() {

        return new OracleExplorerParameters();

    }

    public void setPreparedStatementParameters(PreparedStatement st, JDBCSQLBuilderBase sqlbuilder, FeatureType type, FeatureProvider feature) {

        try {

            List<Object> values = new ArrayList<>();

            for (ExpressionBuilder.Parameter parameter : sqlbuilder.getParameters()) {

                Object value;

                if (parameter.is_constant()) {

                    value = parameter.getValue();

                } else {

                    String name = parameter.getName();

                    value = feature.get(name);

                    FeatureAttributeDescriptor attr = type.getAttributeDescriptor(name);

                    if( attr.getType()==DataTypes.GEOMETRY ) {

                        value = forceGeometry(attr.getGeomType(), (Geometry) value);

                    }

                }

                values.add(value);

            }

            JDBCUtils.setObjects(st, values, sqlbuilder.geometry_support_type());

        } catch (Exception ex) {

            String f = "unknow";

            try {

                f = feature.toString();

            } catch (Exception ex2) {

                // Do nothing

            }

            throw new RuntimeException("Can't set parameters to prepared statement from the feature (" + f + ")", ex);

        }

    }

    private Geometry forceGeometry(GeometryType geomtype, Geometry geom) throws CreateGeometryException {

        switch( geomtype.getType() ) {

        case Geometry.TYPES.MULTIPOLYGON:

            if( geom.getType()==Geometry.TYPES.POLYGON ) {

                MultiPolygon x = getGeometryManager().createMultiPolygon(geomtype.getSubType());

                x.addPrimitive((Primitive) geom);

                geom = x;

            }

            break;

        case Geometry.TYPES.MULTILINE:

            if( geom.getType()==Geometry.TYPES.LINE ) {

                MultiLine x = getGeometryManager().createMultiLine(geomtype.getSubType());

                x.addPrimitive((Primitive) geom);

                geom = x;

            }

            break;

        case Geometry.TYPES.MULTIPOINT:

            if( geom.getType()==Geometry.TYPES.POINT ) {

                MultiLine x = getGeometryManager().createMultiLine(geomtype.getSubType());

                x.addPrimitive((Primitive) geom);

                geom = x;

            }

            break;

        case Geometry.TYPES.POLYGON:

            if( geom.getType()==Geometry.TYPES.MULTIPOLYGON ) {

                MultiPolygon x = (MultiPolygon) geom;

                if( x.getPrimitivesNumber()==1 ) {

                    geom = x.getPrimitiveAt(0);

                }

            }

            break;

        case Geometry.TYPES.LINE:

            if( geom.getType()==Geometry.TYPES.MULTILINE ) {

                MultiLine x = (MultiLine) geom;

                if( x.getPrimitivesNumber()==1 ) {

                    geom = x.getPrimitiveAt(0);

                }

            }

            break;

        case Geometry.TYPES.POINT:

            if( geom.getType()==Geometry.TYPES.MULTIPOINT ) {

                MultiPoint x = (MultiPoint) geom;

                if( x.getPrimitivesNumber()==1 ) {

                    geom = x.getPrimitiveAt(0);

                }

            }

        }

        return geom;

    }

    @Override

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

        try {

                // Oracle uses non-standard WKB encoding for rectangles (used in bounding boxes)

                // Correct it when needed

                byte[] wkb = (byte[]) rs.getBytes(index);

                ByteBuffer data = ByteBuffer.wrap(wkb);

                byte endian = data.get();

                if (endian == 1) {

                    data.order(ByteOrder.LITTLE_ENDIAN);

                }

                int typeword = data.getInt();

                int realtype = typeword & 0x1FFFFFFF; // cut off high flag bits

                if (realtype==3) { // polygon

                        int rings = data.getInt();

                        if (rings==1) {

                                int count = data.getInt();

                                if (count==2) { // Declares 2 points but it has in fact 5

                                        data.putInt(9, 5);

                                }

                        }

                }

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

        } catch (Exception ex) {

            throw new JDBCCantFetchValueException(ex);

        }

    }

    @Override

    public IProjection getProjectionFromDatabaseCode(String databaseCode) {

            /**

             * FIXME: We can query the following table to search the proper definition:

             * SELECT * FROM SDO_COORD_REF_SYS  ;

             */

        if( StringUtils.trimToEmpty(databaseCode).equals("0") ) {

            return null;

        }

        String abbrev = null;

                try {

                        abbrev = this.srssolver.getApplicationAbbrev(this.getConnection(), databaseCode);

                } catch (AccessResourceException e) {

                        // TODO Auto-generated catch block

                        e.printStackTrace();

                }

        if( StringUtils.isEmpty(abbrev) ) {

            return null;

        }

        IProjection proj = CRSFactory.getCRS(abbrev);

        return proj;

    }

}