gvSIG data provider for Oracle

<?xml version="1.0"?>

<definitions>

  <version>1.0.0</version>

  <classes>

    <class name="Oracle" namespace="Metadata">

      <extends>

      	<class name="JDBC2" namespace="Metadata"/>

      </extends>

      <description>Metadata of a Oracle store</description>

      <fields>

      </fields>

    </class>

  </classes>

</definitions>  

<?xml version="1.0"?>

<definitions>

  <version>1.0.0</version>

  <classes>

    <class name="OracleResourceParameters">

      <extends>

        <class>JDBC2ResourceParameters</class>

      </extends>

      <fields>

        <field name="JDBCDriverClass" type="string" mandatory="true"

          defaultValue="oracle.jdbc.driver.OracleDriver" group="Advanced">

          <description>JDBC Driver class</description>

        </field>

		<field name="port" type="integer" mandatory="false"

          defaultValue="1521" group="Connection">

          <description></description>

        </field>

        <field name="host" type="string" mandatory="false" 

            defaultValue="127.0.0.1" group="Connection">

          <description></description>

        </field>

      </fields>

    </class>

    <class name="OracleStoreParameters">

      <extends>

        <class>JDBC2StoreParameters</class>

        <class>OracleResourceParameters</class>

      </extends>

      <fields/>

    </class>

    <class name="OracleNewStoreParameters">

      <extends>

        <class>JDBC2NewStoreParameters</class>

        <class>OracleResourceParameters</class>

      </extends>

      <fields/>

    </class>

    <class name="OracleServerExplorerParameters">

      <extends>

        <class>OracleResourceParameters</class>

        <class>JDBC2ServerExplorerParameters</class>

      </extends>

      <fields/>

    </class>

  </classes>

</definitions>  

org.gvsig.oracle.dal.OracleLibrary

/* gvSIG. Geographic Information System of the Valencian Government

 *

 * Copyright (C) 2007-2008 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 2

 * 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.oracle.dal;

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

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

public class OracleNewStoreParameters 

    extends 

        JDBCNewStoreParameters 

    implements 

        OracleConnectionParameters 

    {

    private final OracleConnectionParametersHelper helper;

    public OracleNewStoreParameters() {

        super(

            OracleLibrary.NAME + "NewStoreParameters", 

            OracleLibrary.NAME

        );

        this.helper = new OracleConnectionParametersHelper(this);

    }

    @Override

    public String getUrl() {

        return this.helper.getUrl();

    }

    @Override

    public void validate() throws ValidateDataParametersException {

        this.helper.validate();

    }

    @Override

    public void setTable(String table) {

        super.setTable(table.toUpperCase());

    }

}

package org.gvsig.oracle.dal.operations;

import java.util.Iterator;

import java.util.List;

import org.apache.commons.lang3.tuple.Pair;

import org.cresques.cts.IProjection;

import org.gvsig.fmap.dal.SQLBuilder;

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

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

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.AppendOperation;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.CalculateEnvelopeOfColumnOperation;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.CanModifyTableOperation;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.CreateTableOperation;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.DropTableOperation;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.FetchFeatureTypeOperation;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.ListTablesOperation;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.OperationsFactoryBase;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.PerformChangesOperation;

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

public class OracleOperationsFactory extends OperationsFactoryBase {

    public OracleOperationsFactory(JDBCHelper helper) {

        super(helper);

    }

    @Override

    public FetchFeatureTypeOperation createFetchFeatureType(EditableFeatureType type, TableReference table, List<String> primaryKeys, String defaultGeometryField, IProjection crs) {

        return new OracleFetchFeatureTypeOperation(

                helper, type, table, primaryKeys, 

                defaultGeometryField, crs

        );

    }

    @Override

    public CanModifyTableOperation createCanModifyTableOperation(

            TableReference table

        ) {

        return new OracleCanModifyTableOperation(helper, table);

    }

    @Override

    public ListTablesOperation createListTables(

            int mode,

            JDBCServerExplorerParameters baseParameters, 

            boolean informationTables

        ) {

        return new OracleListTablesOperation(

                helper, mode, baseParameters, informationTables );

    }

    @Override

    public PerformChangesOperation createPerformChanges(TableReference table, FeatureType type,

    		Iterator deleteds, Iterator inserteds, Iterator updateds, Iterator featureTypesChanged) {

    	return new OraclePerformChangesOperation(helper, table, type, 

                deleteds, inserteds, updateds, featureTypesChanged);

    }

    @Override

    public AppendOperation createAppend(TableReference table, FeatureType type) {

    	return new OracleAppendOperation(helper, table, type);

    }

    @Override

    public DropTableOperation createDropTable(TableReference table) {

    	return new OracleDropTableOperation(helper, table);

    }

    @Override

    public CreateTableOperation createTable(TableReference table, FeatureType type, List<Pair<String, SQLBuilder.Privilege>> userAndPrivileges, List<String> additionalSQLs) throws DataException {

        return new OracleCreateTableOperation(helper, table, type, userAndPrivileges, additionalSQLs);

    }

    @Override

    public CalculateEnvelopeOfColumnOperation createCalculateEnvelopeOfColumn(FeatureType featureType, TableReference table, String columnName, String baseFilter, Envelope workingArea, IProjection crs) {

        return new OracleCalculateEnvelopeOfColumnOperation(helper, featureType, table, columnName, baseFilter, workingArea, crs);

    }

}

/*

 * To change this license header, choose License Headers in Project Properties.

 * To change this template file, choose Tools | Templates

 * and open the template in the editor.

 */

package org.gvsig.oracle.dal.operations;

import java.sql.Connection;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.apache.commons.lang3.tuple.Pair;

import org.gvsig.fmap.dal.SQLBuilder;

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

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

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

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.CreateTableOperation;

/**

 *

 * @author fdiaz

 */

public class OracleCreateTableOperation extends CreateTableOperation{

    public OracleCreateTableOperation(JDBCHelper helper) {

        super(helper);

    }

    public OracleCreateTableOperation(JDBCHelper helper, OperationsFactory.TableReference table, FeatureType type, List<Pair<String, SQLBuilder.Privilege>> userAndPrivileges, List<String> additionalSQLs) {

        super(helper, table, type, userAndPrivileges, additionalSQLs);

        EditableFeatureType editableType = this.type.getEditable();

        boolean changed = false;

        for (FeatureAttributeDescriptor attr : editableType) {

            if( attr.getType()==org.gvsig.fmap.dal.DataTypes.GEOMETRY ) {

                if(!StringUtils.isAllUpperCase(attr.getName())) {

                    ((EditableFeatureAttributeDescriptor)attr).setName(attr.getName().toUpperCase());

                    changed = true;

                }

            }

        }

        if(changed){

            if(this.type instanceof EditableFeatureType){

                this.type = editableType.getCopy();

            } else {

                this.type = editableType.getNotEditableCopy();

            }

        }

    }

    @Override

    public boolean performCreateTable(Connection conn) throws DataException {

        boolean result = super.performCreateTable(conn);

        if(result) {

            updateSpatialIndexAndMetadata();

        }

        return result;

    }

    public void updateSpatialIndexAndMetadata() {

    	OracleUpdateSpatialIndexAndMetadata updateIdxOperation = 

        		new OracleUpdateSpatialIndexAndMetadata(this.helper,

        				this.table.getDatabase(),

        				this.table.getSchema(),

        				this.table.getTable().toUpperCase(),

        				this.type

        		);

        try {

            updateIdxOperation.execute();

        } catch (Exception e) {

            LOGGER.warn("Error creating/updating the spatial index", e);

        }

    }

}

package org.gvsig.oracle.dal.operations;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.text.MessageFormat;

import java.util.ArrayList;

import java.util.List;

import java.util.Locale;

import org.cresques.cts.IProjection;

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.resource.exception.AccessResourceException;

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.CalculateEnvelopeOfColumnOperation;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryLocator;

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

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

import org.gvsig.oracle.dal.GeometryTypeUtils;

import org.gvsig.oracle.dal.SpatialIndexUtils;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

/**

 * Updates the layer extent on table USER_SDO_GEOM_METADATA and rebuild the

 * SpatialIndex.

 *

 * The metadata record is inserted if not existing. The SpatialIndex is also

 * created if not existing.

 *

 * @author Cesar Martinez Izquierdo

 *

 */

@SuppressWarnings("UseSpecificCatch")

public class OracleUpdateSpatialIndexAndMetadata {

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

    protected static final double XTOLERANCE = 0.00001;

    protected static final double YTOLERANCE = 0.00001;

    protected final JDBCHelper helper;

    protected final String database;

    protected final String schema;

    protected final String table;

    protected final FeatureType featureType;

    private static final String[] DEFAULT_DIM_NAMES = new String[]{"x", "y", "z", "m"};

    private Connection connection = null;

    public OracleUpdateSpatialIndexAndMetadata(

            JDBCHelper helper,

            String database,

            String schema,

            String table,

            FeatureType featureType) {

        this.helper = helper;

        this.database = database;

        this.schema = schema;

        this.table = table;

        this.featureType = featureType;

    }

    public void begin() throws AccessResourceException {

        this.connection = this.helper.getConnectionWritable();

    }

    protected void clean() {

        this.helper.closeConnection(this.connection);

        this.connection = null;

    }

    /**

     * Executes the operation

     *

     * @throws DataException

     */

    public void execute() throws DataException {

        begin();

        FeatureAttributeDescriptor geomDescriptor = featureType.getDefaultGeometryAttribute();

        if(geomDescriptor != null){

            // Inserting/updating SRID, dimensions and extent on USER_SDO_GEOM_METADATA.

            // The layer metadata entry MUST exist before creating the spatial index.

            if (updateOrInsertMetadata(geomDescriptor)) {

                // We will only create/rebuild the index if metadata table has been changed (updated/inserted)

                createOrRebuildSpatialIndex(geomDescriptor);

            }

        }

        clean();

    }

    /**

     * Updates the USER_SDO_GEOM_METADATA table or inserts if an entry is not

     * found for the layer. The update is only performed if the new layer extent

     * is bigger than current one.

     *

     * @param geomDescriptor

     * @return true if the metadata table was changed, false otherwise

     */

    public boolean updateOrInsertMetadata(FeatureAttributeDescriptor geomDescriptor) {

        String geomColumnName = geomDescriptor.getName();

        IProjection crs = geomDescriptor.getSRS();

        CalculateEnvelopeOfColumnOperation envelopeOp = null;

        List<OracleDimensionMetadata> dimensions = fetchDimensionMetadata();

            envelopeOp = helper.getOperations().

                    createCalculateEnvelopeOfColumn(

                            featureType,

                            helper.getOperations().createTableReference(database, schema, table, null),

                            geomColumnName,

                            "",

                            null,

                            crs

                    );

        Envelope layerEnvelope = (Envelope) envelopeOp.perform();

        if(layerEnvelope != null){

            layerEnvelope.setProjectionIffNull(crs);

            SQLBuilder builder = helper.createSQLBuilder();

            int srid = (int) builder.srs_id(crs);

            if (schema.equalsIgnoreCase(helper.getConnectionParameters().getUser())) {

                if (dimensions.size() > 0) {

                    if (updateDimensions(dimensions, layerEnvelope)) {

                        return updateUserMetadata(dimensions, geomColumnName, srid);

                    }

                } else {

                    return insertUserMetadata(getDefaultDimensions(layerEnvelope), geomColumnName, srid);

                }

            } else {

                // FIXME: try inserting on global table MDSYS.SDO_GEOM_METADATA_TABLE

            }

        }

        return false;

    }

    /**

     * Updates layer metatada on USER_SDO_GEOM_METADATA table

     *

     * @param dimensions

     * @param geomColumnName

     * @param srid

     * @return Returns true if the layer record exists on the table and has been

     * correctly updated, false otherwise

     */

    public boolean updateUserMetadata(List<OracleDimensionMetadata> dimensions, String geomColumnName, int srid) {

        StringBuilder sdoMetadataUpdateQuery = new StringBuilder();

        sdoMetadataUpdateQuery.append("UPDATE USER_SDO_GEOM_METADATA SET DIMINFO = SDO_DIM_ARRAY(");

        for (int i = 0; i < dimensions.size(); i++) {

            if (i == 0) {

                sdoMetadataUpdateQuery.append("SDO_DIM_ELEMENT(?, ?, ?, ?)");

            } else {

                sdoMetadataUpdateQuery.append(",SDO_DIM_ELEMENT(?, ?, ?, ?)");

            }

        }

        sdoMetadataUpdateQuery.append("), SRID=? WHERE TABLE_NAME=? AND COLUMN_NAME=?");

        PreparedStatement st = null;

        try {

            String sql = sdoMetadataUpdateQuery.toString();

            st = connection.prepareStatement(sql);

            int paramCount = 1;

            for (OracleDimensionMetadata dim : dimensions) {

                st.setString(paramCount++, dim.getName());

                st.setDouble(paramCount++, dim.getLowerBound());

                st.setDouble(paramCount++, dim.getUpperBound());

                st.setDouble(paramCount++, dim.getTolerance());

            }

            st.setInt(paramCount++, srid);

            st.setString(paramCount++, table);

            st.setString(paramCount++, geomColumnName);

            int updatedRows = JDBCUtils.executeUpdate(st, sql);

            return (updatedRows > 0);

        } catch (SQLException ex) {

            throw new RuntimeException("Can't update layer metadata on USER_SDO_GEOM_METADATA", ex);

        } finally {

            JDBCUtils.closeQuietly(st);

        }

    }

    /**

     * Inserts layer metatada on USER_SDO_GEOM_METADATA table

     *

     * @param dimensions

     * @param geomColumnName

     * @param srid

     * @return Returns true if the layer record has been correctly inserted,

     * false otherwise

     */

    public boolean insertUserMetadata(List<OracleDimensionMetadata> dimensions, String geomColumnName, int srid) {

        StringBuilder sdoMetadataUpdateQuery = new StringBuilder();

        sdoMetadataUpdateQuery.append("INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, SRID, DIMINFO)");

        sdoMetadataUpdateQuery.append(" VALUES ('");

        sdoMetadataUpdateQuery.append(table);

        sdoMetadataUpdateQuery.append("', '");

        sdoMetadataUpdateQuery.append(geomColumnName);

        sdoMetadataUpdateQuery.append("', ");

        sdoMetadataUpdateQuery.append(srid);

        sdoMetadataUpdateQuery.append(", SDO_DIM_ARRAY(");

        for (int i = 0; i < dimensions.size(); i++) {

            if (i != 0) {

                sdoMetadataUpdateQuery.append(",");

            }

            sdoMetadataUpdateQuery.append("SDO_DIM_ELEMENT('");

            OracleDimensionMetadata dim = dimensions.get(i);

            sdoMetadataUpdateQuery.append(dim.getName());

            sdoMetadataUpdateQuery.append("', ");

            sdoMetadataUpdateQuery.append(String.format(Locale.ENGLISH, "%f", dim.getLowerBound()));

            sdoMetadataUpdateQuery.append(", ");

            sdoMetadataUpdateQuery.append(String.format(Locale.ENGLISH, "%f", dim.getUpperBound()));

            sdoMetadataUpdateQuery.append(", ");

            sdoMetadataUpdateQuery.append(String.format(Locale.ENGLISH, "%f", dim.getTolerance()));

            sdoMetadataUpdateQuery.append(")");

        }

        sdoMetadataUpdateQuery.append("))");

        Statement st = null;

        String sql = sdoMetadataUpdateQuery.toString();

        try {

            st = connection.createStatement();

            int insertedRows = JDBCUtils.executeUpdate(st, sql);

            return (insertedRows > 0);

        } catch (SQLException ex) {

            throw new RuntimeException("Can't insert layer metadata on USER_SDO_GEOM_METADATA", ex);

        } finally {

            JDBCUtils.closeQuietly(st);

        }

    }

    /**

     * Gets the default layer dimensions.

     *

     * @param layerEnvelope

     * @return

     */

    protected List<OracleDimensionMetadata> getDefaultDimensions(Envelope layerEnvelope) {

        List<OracleDimensionMetadata> dimensions = new ArrayList<>();

        if(layerEnvelope != null){

            for (int i = 0; i < DEFAULT_DIM_NAMES.length && i < layerEnvelope.getDimension(); i++) {

                dimensions.add(new OracleDimensionMetadata(DEFAULT_DIM_NAMES[i], layerEnvelope.getMinimum(i), layerEnvelope.getMaximum(i), XTOLERANCE));

            }

        }

        return dimensions;

    }

    protected Envelope getEnvelope(List<OracleDimensionMetadata> dimensions) {

        Envelope envelope;

        try {

            int subType = GeometryTypeUtils.getSubType(dimensions.size());

            Point lowerCorner = (Point) GeometryLocator.getGeometryManager().create(Geometry.TYPES.POINT, subType);

            Point upperCorner = (Point) GeometryLocator.getGeometryManager().create(Geometry.TYPES.POINT, subType);

            for (int i = 0; i < dimensions.size() && i < lowerCorner.getDimension(); i++) {

                lowerCorner.setCoordinateAt(i, dimensions.get(i).getLowerBound());

                upperCorner.setCoordinateAt(i, dimensions.get(i).getUpperBound());

            }

            envelope = GeometryLocator.getGeometryManager().createEnvelope(subType);

            envelope.setLowerCorner(lowerCorner);

            envelope.setUpperCorner(upperCorner);

            return envelope;

        } catch (Exception e) {

        }

        return null;

    }

    /**

     * Updates the dimension definition using the provided envelope. The update

     * is only performed if the new layer extent is bigger than the one defined

     * on dimension metadata.

     *

     * @param dimensions

     * @param newEnvelope

     * @return true if the definition was updated, false otherwise

     */

    protected boolean updateDimensions(

            List<OracleDimensionMetadata> dimensions,

            Envelope newEnvelope) {

        Envelope oldEnvelope = getEnvelope(dimensions);

        if (oldEnvelope == null || oldEnvelope.contains(newEnvelope)) {

            return false;

        }

        for (int i = 0; i < dimensions.size(); i++) {

            OracleDimensionMetadata dim = dimensions.get(i);

            dim.setLowerBound(newEnvelope.getMinimum(i));

            dim.setUpperBound(newEnvelope.getMaximum(i));

        }

        return true;

    }

    /**

     * Fetches dimensions from ALL_SDO_GEOM_METADATA table and updates the layer

     * envelope using the provided one

     *

     * @return

     */

    protected List<OracleDimensionMetadata> fetchDimensionMetadata() {

        String sdoMetadataGetTolerance = "SELECT dinfo.SDO_DIMNAME, dinfo.SDO_LB, dinfo.SDO_UB, dinfo.SDO_TOLERANCE "

                + "FROM MDSYS.ALL_SDO_GEOM_METADATA, TABLE(DIMINFO) dinfo "

                + "WHERE OWNER = ? AND TABLE_NAME = ?";

        ResultSet rs = null;

        List<OracleDimensionMetadata> dimensions = new ArrayList<>();

        PreparedStatement st = null;

        try {

            st = connection.prepareStatement(sdoMetadataGetTolerance);

            st.setString(1, schema);

            st.setString(2, table);

            rs = JDBCUtils.executeQuery(st, sdoMetadataGetTolerance);

            while (rs.next()) {

                OracleDimensionMetadata dim = new OracleDimensionMetadata(

                        rs.getString(1),

                        rs.getDouble(2),

                        rs.getDouble(3),

                        rs.getDouble(4));

                dimensions.add(dim);

            }

        } catch (Exception ex) {

            LOGGER.debug("Error getting dimensions (ALL_SDO_GEOM_METADATA).", ex);

        } finally {

            JDBCUtils.closeQuietly(rs);

            JDBCUtils.closeQuietly(st);

        }

        if(dimensions.isEmpty()){

            sdoMetadataGetTolerance = "SELECT dinfo.SDO_DIMNAME, dinfo.SDO_LB, dinfo.SDO_UB, dinfo.SDO_TOLERANCE "

                + "FROM MDSYS.USER_SDO_GEOM_METADATA, TABLE(DIMINFO) dinfo "

                + "WHERE TABLE_NAME = ?";

            rs = null;

            st = null;

            try {

                st = connection.prepareStatement(sdoMetadataGetTolerance);

                st.setString(1, table);

                rs = JDBCUtils.executeQuery(st, sdoMetadataGetTolerance);

                while (rs.next()) {

                    OracleDimensionMetadata dim = new OracleDimensionMetadata(

                            rs.getString(1),

                            rs.getDouble(2),

                            rs.getDouble(3),

                            rs.getDouble(4));

                    dimensions.add(dim);

                }

            } catch (Exception ex) {

                LOGGER.debug("Error getting dimensions (USER_SDO_GEOM_METADATA).", ex);

            } finally {

                JDBCUtils.closeQuietly(rs);

                JDBCUtils.closeQuietly(st);

            }

        }

        return dimensions;

    }

    public void createOrRebuildSpatialIndex(FeatureAttributeDescriptor geomDescriptor) {

        int geomType = geomDescriptor.getGeomType().getType();

        String oracleGeomTypeName = GeometryTypeUtils.toOracleGeometryTypeName(geomType);

        String geomColumn = geomDescriptor.getName();

        String indexName = SpatialIndexUtils.getSpatialIndex(connection, schema, table, geomColumn);

        Statement st = null;

        String sql;

        if (indexName == null) {

            indexName = SpatialIndexUtils.getSpatialIndexName(table, geomColumn);

            String createIndexSql = "CREATE INDEX \"{0}\" ON \"{1}\" (\"{2}\") INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS (''LAYER_GTYPE={3}'')"; // SDO_LEVEL=8

            sql = MessageFormat.format(

                    createIndexSql,

                    indexName,

                    table,

                    geomColumn,

                    oracleGeomTypeName

            );

            try {

                st = connection.createStatement();

                JDBCUtils.execute(st, sql);

            } catch (Exception ex) {

                LOGGER.debug("Error creating spatial index", ex);

            } finally {

                JDBCUtils.closeQuietly(st);

            }

        } else {

            String rebuildIndexSql = "ALTER INDEX \"{0}\" REBUILD PARAMETERS (''LAYER_GTYPE={1}'')";

            try {

                sql = MessageFormat.format(

                        rebuildIndexSql,

                        indexName,

                        oracleGeomTypeName

                );

                st = connection.createStatement();

                JDBCUtils.executeQuery(st, sql);

            } catch (Exception ex) {

                LOGGER.warn("Error rebuilding spatial index", ex);

            } finally {

                JDBCUtils.closeQuietly(st);

            }

        }

    }

    public class OracleDimensionMetadata {

        private String name;

        private double lb;

        private double ub;

        private double tolerance;

        public OracleDimensionMetadata(String name, double lb, double ub, double tolerance) {

            this.name = name;

            this.lb = lb;

            this.ub = ub;

            this.tolerance = tolerance;

        }

        public String getName() {

            return name;

        }

        public double getLowerBound() {

            return lb;

        }

        public double getUpperBound() {

            return ub;

        }

        public double getTolerance() {

            return tolerance;

        }

        public void setName(String name) {

            this.name = name;

        }

        public void setLowerBound(double lb) {

            this.lb = lb;

        }

        public void setUpperBound(double ub) {

            this.ub = ub;

        }

        public void setTolerance(double tolerance) {

            this.tolerance = tolerance;

        }

    }

}

package org.gvsig.oracle.dal.operations;

import java.util.List;

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.DropTableOperation;

public class OracleDropTableOperation extends DropTableOperation {

    public OracleDropTableOperation(JDBCHelper helper) {

            super(helper);

    }

    public OracleDropTableOperation(JDBCHelper helper, TableReference table) {

            super(helper, table);

    }

    public List<String> getSQLs() {

        JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

        sqlbuilder.drop_table().table()

                .database(this.table.getDatabase())

                .schema(this.table.getSchema())

                .name(this.table.getTable());

        List<String> sqls = sqlbuilder.drop_table().toStrings();

        StringBuilder builder = new StringBuilder();

        builder.append("DELETE FROM MDSYS.OGIS_GEOMETRY_COLUMNS WHERE F_TABLE_SCHEMA = '");

        builder.append(this.table.getSchema());

        builder.append("' AND F_TABLE_NAME = '");

        builder.append(this.table.getTable());

        builder.append("'");

        sqls.add(builder.toString());

        return sqls;

    }

}

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2020 gvSIG Association.

 *

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

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

package org.gvsig.oracle.dal.operations;

import org.apache.commons.lang3.StringUtils;

import org.cresques.cts.IProjection;

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.CalculateEnvelopeOfColumnOperation;

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

import org.gvsig.oracle.dal.SpatialIndexUtils;

public class OracleCalculateEnvelopeOfColumnOperation extends CalculateEnvelopeOfColumnOperation {

    public OracleCalculateEnvelopeOfColumnOperation(

            JDBCHelper helper,

            FeatureType featureType,

            TableReference table,

            String columnName,

            String baseFilter,

            Envelope limit,

            IProjection crs

    ) {

        super(helper, featureType, table, columnName, baseFilter, limit, crs);

    }

    @Override

    public String getSQLWithAggregate() {

        if (StringUtils.isEmpty(baseFilter) && limit == null) {

            boolean validIndex = false;

            String indexName = SpatialIndexUtils.getSpatialIndex(conn, table.getSchema(), table.getTable(), columnName);

            if(!StringUtils.isBlank(indexName)){

                validIndex = SpatialIndexUtils.isValidSpatialIndex(conn, table.getTable());

            }

            if (validIndex) {

                StringBuilder builder = new StringBuilder();

                builder.append("SELECT SDO_TUNE.EXTENT_OF('");

                builder.append(table.getTable());

                builder.append("', '");

                builder.append(columnName);

                builder.append("').GET_WKB() FROM DUAL");

                return builder.toString();

            }

        }

        return super.getSQLWithAggregate();

    }

}

package org.gvsig.oracle.dal.operations;

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.ResultSet;

import java.sql.SQLException;

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.CanModifyTableOperation;

public class OracleCanModifyTableOperation extends CanModifyTableOperation {

    public OracleCanModifyTableOperation(JDBCHelper helper) {

        super(helper);

    }

    public OracleCanModifyTableOperation(JDBCHelper helper, TableReference table) {

        super(helper, table);

    }

    @Override

    public boolean canModifyTable(Connection conn,

            TableReference table

    ) throws DataException {

        try {

            DatabaseMetaData metadata = conn.getMetaData();

            String user = helper.getConnectionParameters().getUser();

            if (table.getSchema()==null || table.getSchema().equalsIgnoreCase(user)) {

                return true;

            }

            ResultSet rs = metadata.getTablePrivileges(null, table.getSchema(), table.getTable());

            boolean canUpdate = false, canInsert = false;

            String privilege;

            while (rs.next() && !(canInsert && canUpdate)) {

                if (user.equalsIgnoreCase(rs.getString("GRANTEE"))) {

                    privilege = rs.getString("PRIVILEGE");

                    if ("INSERT".equals(privilege)) {

                        canInsert = true;

                    } else if ("UPDATE".equals(privilege)) {

                        canUpdate = true;

                    }

                }

            }

            return (canInsert && canUpdate);

        } catch (SQLException ex) {

            LOGGER.error("Can't fetch table privileges", ex);

        }

        return false;

    }

}

package org.gvsig.oracle.dal.operations;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.Iterator;

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

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

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

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.PerformChangesOperation;

@SuppressWarnings("UseSpecificCatch")

public class OraclePerformChangesOperation extends PerformChangesOperation {

    public OraclePerformChangesOperation(JDBCHelper helper) {

        super(helper);

    }

    public OraclePerformChangesOperation(JDBCHelper helper, TableReference table,

            FeatureType featureType, Iterator<FeatureReferenceProviderServices> deleteds,

            Iterator<FeatureProvider> inserteds, Iterator<FeatureProvider> updateds,

            Iterator<FeatureTypeChanged> featureTypesChanged) {

        super(helper, table, featureType, deleteds, inserteds, updateds, featureTypesChanged);

    }

    @Override

    public Object perform(Connection conn) throws DataException {

        Object result = super.perform(conn);

        try {

            // we need to commit before rebuilding the index

            conn.commit();

        } catch (SQLException e) {

            LOGGER.warn("Error commiting perform changes operation", e);

        }

        updateSpatialIndexAndMetadata();

        return result;

    }

    public void updateSpatialIndexAndMetadata() {

        OracleUpdateSpatialIndexAndMetadata updateIdxOperation

                = new OracleUpdateSpatialIndexAndMetadata(this.helper,

                        this.table.getDatabase(),

                        this.table.getSchema(),

                        this.table.getTable(),

                        this.featureType

                );

        try {

            updateIdxOperation.execute();

        } catch (Exception e) {

            LOGGER.warn("Error creating/updating the spatial index", e);

        }

    }

}

package org.gvsig.oracle.dal.operations;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

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

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

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

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

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

/**

 * Updates the layer extent on table USER_SDO_GEOM_METADATA and rebuild the

 * SpatialIndex.

 *

 * The metadata record is inserted if not existing. The SpatialIndex is also

 * created if not existing.

 *

 * @author Cesar Martinez Izquierdo

 *

 */

@SuppressWarnings("UseSpecificCatch")

public class OracleGetSRS {

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

    protected final JDBCHelper helper;

    protected final String database;

    protected final String schema;

    protected final String table;

    private Connection connection = null;

    public OracleGetSRS(

            JDBCHelper helper,

            String database,

            String schema,

            String table) {

        this.helper = helper;

        this.database = database;

        this.schema = schema;

        this.table = table;

    }

    public void begin() throws AccessResourceException {

        this.connection = this.helper.getConnection(); //.getConnectionWritable();

    }

    protected void clean() {

        this.helper.closeConnection(this.connection);

        this.connection = null;

    }

    /**

     * Executes the operation

     *

     * @return 

     * @throws DataException

     */

    public String execute() throws DataException {

        begin();

        StringBuilder srsQuery = new StringBuilder();

        srsQuery.append("select SRID from MDSYS.ALL_SDO_GEOM_METADATA where TABLE_NAME = UPPER('?');");

        PreparedStatement st = null;

        String srs = null;

        try {

            String sql = srsQuery.toString();

            st = connection.prepareStatement(sql);

            st.setString(1, table);

            ResultSet rs = JDBCUtils.executeQuery(st, sql);

            if(rs.first()) {

                srs = rs.getString(0);

            }

        } catch (SQLException ex) {

            throw new RuntimeException("Can't get srs from table "+table, ex);

        } finally {

            JDBCUtils.closeQuietly(st);

        }

        clean();

        return srs;

    }

}

package org.gvsig.oracle.dal.operations;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import org.cresques.cts.IProjection;

import org.gvsig.fmap.crs.CRSFactory;

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.ListTablesOperation;

@SuppressWarnings("UseSpecificCatch")

public class OracleListTablesOperation extends ListTablesOperation {

    private static final String METADATA_COLUMN_TABLE_OWNER = "OWNER";

    private static final String METADATA_COLUMN_TABLE_NAME = "TABLE_NAME";

    private static final String ALL_VIEWS_QUERY = "SELECT OWNER, VIEW_NAME TABLE_NAME FROM ALL_VIEWS";

    private static final String ALL_TABLES_QUERY = "SELECT OWNER, TABLE_NAME FROM ALL_TABLES";

    private static final String EXCLUDE_SYSTEM_VIEWS_FILTER = "OWNER != 'MDSYS' AND OWNER != 'CTXSYS' AND OWNER != 'EXFSYS' AND OWNER != 'OLAPSYS' AND OWNER != 'ORDDATA' AND OWNER != 'ORDSYS' AND OWNER != 'SYS' AND OWNER != 'SYSTEM' AND OWNER != 'WMSYS' AND OWNER != 'XDB' AND OWNER NOT LIKE 'APEX_%' AND VIEW_NAME NOT LIKE '%$%'";

    private static final String EXCLUDE_SYSTEM_TABLES_FILTER = "OWNER != 'MDSYS' AND OWNER != 'CTXSYS' AND OWNER != 'EXFSYS' AND OWNER != 'OLAPSYS' AND OWNER != 'ORDDATA' AND OWNER != 'ORDSYS' AND OWNER != 'SYS' AND OWNER != 'SYSTEM' AND OWNER != 'WMSYS' AND OWNER != 'XDB' AND OWNER NOT LIKE 'APEX_%' AND TABLE_NAME NOT LIKE '%$%'";

    public OracleListTablesOperation(JDBCHelper helper, int mode, JDBCServerExplorerParameters baseParameters,

            boolean informationTables) {

        super(helper, mode, baseParameters, informationTables);

    }

    @Override

    public List<JDBCStoreParameters> listTables(

            Connection conn,

            int mode,

            JDBCServerExplorerParameters serverParameters,

            boolean informationTables

    ) {

        try {

            String schema = serverParameters.getSchema();

            return this.getAllTablesAndViews(serverParameters, schema, informationTables);

        } catch (SQLException ex) {

            throw new RuntimeException("Can't fetch tables information", ex);

        }

    }

    protected List<JDBCStoreParameters> getAllTablesAndViews(

            JDBCServerExplorerParameters serverParameters,

            String schema,

            boolean includeSystemTbls) throws SQLException {

        List<JDBCStoreParameters> tables = new ArrayList<>();

        try {

            StringBuilder builder = new StringBuilder();

            builder.append(ALL_TABLES_QUERY);

            boolean where = false;

            if (!includeSystemTbls) {

                builder.append(" WHERE ");

                where = true;

                builder.append(EXCLUDE_SYSTEM_TABLES_FILTER);

            }

            if (schema != null) {

                if (where) {

                    builder.append(" AND ");

                } else {

                    builder.append(" WHERE ");

                }

                builder.append(METADATA_COLUMN_TABLE_OWNER);

                builder.append(" = '");

                builder.append(schema);

                builder.append("'");

            }

            builder.append(" UNION ");

            builder.append(ALL_VIEWS_QUERY);

            if (!includeSystemTbls) {

                builder.append(" WHERE ");

                where = true;

                builder.append(EXCLUDE_SYSTEM_VIEWS_FILTER);

            } else {

                where = false;

            }

            if (schema != null) {

                if (where) {

                    builder.append(" AND ");

                } else {

                    builder.append(" WHERE ");

                }

                builder.append(METADATA_COLUMN_TABLE_OWNER);

                builder.append(" = '");

                builder.append(schema);

                builder.append("'");

            }

            Statement st = null;

            ResultSet rs = null;

            try {

                st = this.getConnection().createStatement();

                rs = JDBCUtils.executeQuery(st, builder.toString());

                while (rs.next()) {

                    JDBCStoreParameters params = this.helper.createOpenStoreParameters(serverParameters);

                    params.setSchema(rs.getString(METADATA_COLUMN_TABLE_OWNER));

                    String table = rs.getString(METADATA_COLUMN_TABLE_NAME);

                    params.setTable(table);

                    IProjection projection = getProjection(table);

                    if (projection != null) {

                        params.setCRS(projection);

                    }

                    tables.add(params);

                }

            } finally {

                JDBCUtils.closeQuietly(rs);

                JDBCUtils.closeQuietly(st);

            }

        } catch (Exception ex) {

            LOGGER.debug("Can't get the list of tables accessible by the user.", ex);

            throw new SQLException(ex);

        }

        return tables;

    }

    private IProjection getProjection(String table) {

        StringBuilder srsQuery = new StringBuilder();

        srsQuery.append("select coordRef.DATA_SOURCE, meta.SRID from MDSYS.ALL_SDO_GEOM_METADATA meta left join SDO_COORD_REF_SYS coordRef ON coordRef.SRID = meta.SRID  where TABLE_NAME = UPPER(?)");

        PreparedStatement st = null;

        String authority;

        String srs;

        IProjection projection = null;

        try {

            String sql = srsQuery.toString();

            st = conn.prepareStatement(sql);

            st.setString(1, table);

            ResultSet rs = JDBCUtils.executeQuery(st, sql);

            if (rs.next()) {

                authority = rs.getString(1);

                srs = rs.getString(2);

                projection = CRSFactory.getCRS(authority + ":" + srs);

            }

        } catch (SQLException ex) {

            throw new RuntimeException("Can't get srs from table " + table, ex);

        } finally {

            JDBCUtils.closeQuietly(st);

        }

        return projection;

    }

}

package org.gvsig.oracle.dal.operations;

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.AppendOperation;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class OracleAppendOperation extends AppendOperation {

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

	public OracleAppendOperation(JDBCHelper helper, TableReference table, FeatureType type) {

		super(helper, table, type);

	}

	@Override

	public void end() {

		super.end();

		try {

			updateSpatialIndexAndMetadata();

		}

		catch (Exception ex) {

			LOGGER.error("Error updating spatial index and metadata", ex);

		}

	}

    public void updateSpatialIndexAndMetadata() {

        OracleUpdateSpatialIndexAndMetadata updateIdxOperation = 

        		new OracleUpdateSpatialIndexAndMetadata(this.helper,

        				this.table.getDatabase(),

        				this.table.getSchema(),

        				this.table.getTable(),

        				this.type

        		);

        try {

            updateIdxOperation.execute();

        } catch (DataException e) {

            LOGGER.warn("Error creating/updating the spatial index", e);

        }

    }

}

package org.gvsig.oracle.dal.operations;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.util.HashSet;

import java.util.List;

import java.util.Set;

import org.cresques.cts.IProjection;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.fmap.dal.DataTypes;

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

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

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

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.FetchFeatureTypeOperation;

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

import org.gvsig.oracle.dal.GeometryTypeUtils;

import org.gvsig.oracle.dal.OracleSQLBuilder;

import org.gvsig.tools.dataTypes.DataType;

@SuppressWarnings("UseSpecificCatch")

public class OracleFetchFeatureTypeOperation extends FetchFeatureTypeOperation {

    private static final int ORACLE_COLUMN_BINARY_FLOAT_TYPECODE = 100;

    private static final int ORACLE_COLUMN_BINARY_DOUBLE_TYPECODE = 101;

    protected Set<String> tableTriggers;

    public OracleFetchFeatureTypeOperation(JDBCHelper helper) {

        super(helper);

    }

    public OracleFetchFeatureTypeOperation(

            JDBCHelper helper,

            EditableFeatureType featureType,

            TableReference table,

            List<String> primaryKeys,

            String defaultGeometryColumn,

            IProjection crs

    ) {

        super(helper, featureType, table, primaryKeys, defaultGeometryColumn, crs);

    }

    @Override

    public void fetch(Connection conn) throws DataException {

        super.fetch(conn);

    }

    @Override

    public String getSQLToRetrievePrimaryKeysFromInformationSchema() throws SQLException {

        JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.select().column().name(

                sqlbuilder.quote_for_identifiers()

                        +"USER_TAB_COLS"

                        +sqlbuilder.quote_for_identifiers()

                        +"."

                        +sqlbuilder.quote_for_identifiers()

                        +"COLUMN_NAME"

                        +sqlbuilder.quote_for_identifiers()

        );

        sqlbuilder.select().column().name(

                sqlbuilder.quote_for_identifiers()

                        +"ALL_CONSTRAINTS"

                        +sqlbuilder.quote_for_identifiers()

                        +"."

                        +sqlbuilder.quote_for_identifiers()

                        +"CONSTRAINT_TYPE"

                        +sqlbuilder.quote_for_identifiers()

        );

        sqlbuilder.select().from().custom(

                "USER_TAB_COLS "

                + "JOIN ALL_CONS_COLUMNS on (ALL_CONS_COLUMNS.TABLE_NAME = USER_TAB_COLS.TABLE_NAME and ALL_CONS_COLUMNS.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME) "

                + "LEFT JOIN ALL_CONSTRAINTS on ALL_CONSTRAINTS.CONSTRAINT_NAME = ALL_CONS_COLUMNS.CONSTRAINT_NAME"

        );

        sqlbuilder.select().where().set(

                expbuilder.like(

                        expbuilder.custom(

                                sqlbuilder.quote_for_identifiers()

                                        +"USER_TAB_COLS"

                                        +sqlbuilder.quote_for_identifiers()

                                        +"."

                                        +sqlbuilder.quote_for_identifiers()

                                        +"TABLE_NAME"

                                        +sqlbuilder.quote_for_identifiers()),

                        expbuilder.constant(table.getTable())

                )

        );

        sqlbuilder.select().where().and(

                expbuilder.eq(

                        expbuilder.custom(

                                sqlbuilder.quote_for_identifiers()

                                        +"ALL_CONSTRAINTS"

                                        +sqlbuilder.quote_for_identifiers()

                                        +"."

                                        +sqlbuilder.quote_for_identifiers()

                                        +"CONSTRAINT_TYPE"

                                        +sqlbuilder.quote_for_identifiers()),

                        expbuilder.constant("'P'")

                )

        );

        return sqlbuilder.select().toString();

    }

    @Override

    protected int getDataTypeFromMetadata(

            ResultSetMetaData rsMetadata,

            int colIndex

    ) throws SQLException {

        int type = rsMetadata.getColumnType(colIndex);

        switch (type) {

            case java.sql.Types.INTEGER:

                return DataTypes.INT;

            case java.sql.Types.BIGINT:

                return DataTypes.LONG;

            case java.sql.Types.REAL:

                return DataTypes.DOUBLE;

            case java.sql.Types.DOUBLE:

            case ORACLE_COLUMN_BINARY_DOUBLE_TYPECODE:

                return DataTypes.DOUBLE;

            case java.sql.Types.CHAR:

                return DataTypes.STRING;

            case java.sql.Types.CLOB:

            case java.sql.Types.VARCHAR:

            case java.sql.Types.LONGVARCHAR:

                return DataTypes.STRING;

            case java.sql.Types.FLOAT:

            case ORACLE_COLUMN_BINARY_FLOAT_TYPECODE:

                return DataTypes.FLOAT;

            case java.sql.Types.NUMERIC:

            case java.sql.Types.DECIMAL:

                if (rsMetadata.getScale(colIndex) == 0) {

                    if (rsMetadata.getPrecision(colIndex) == DataType.LONG_MAX_PRECISION-1) {

                        return DataTypes.LONG;

                    }

                    if (rsMetadata.getPrecision(colIndex) == DataType.INT_MAX_PRECISION-1) {

                        return DataTypes.INTEGER;

                    }

                }

                return DataTypes.DECIMAL;