gvSIG data provider for Oracle

package org.gvsig.oracle.dal.operations;

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

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

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;

    public static final String ORACLE_SRID_ATTR_TAG = "oracle_srid";

    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,

            int geometryType,

            int geometrySubtype

    ) {

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

    }

    @Override

    public void fetch(JDBCConnection conn) throws DataException {

        super.fetch(conn);

    }

    @Override

    public String getSQLToRetrievePrimaryKeysFromInformationSchema() throws SQLException {

        OracleSQLBuilder sqlbuilder = (OracleSQLBuilder) 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(sqlbuilder.getProviderTableName(table))

                )

        );

        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.NCLOB:

            case java.sql.Types.CLOB:

            case java.sql.Types.NVARCHAR:

            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;

            case java.sql.Types.DATE:

                return DataTypes.DATE;

            case java.sql.Types.TIME:

                return DataTypes.TIME;

            case java.sql.Types.TIMESTAMP:

                return DataTypes.TIMESTAMP;

            case java.sql.Types.BOOLEAN:

            case java.sql.Types.BIT:

                return DataTypes.BOOLEAN;

            case java.sql.Types.BLOB:

            case java.sql.Types.BINARY:

            case java.sql.Types.LONGVARBINARY:

                return DataTypes.BYTEARRAY;

            default:

                String typeName = rsMetadata.getColumnTypeName(colIndex);

                if ("MDSYS.SDO_GEOMETRY".equalsIgnoreCase(typeName)) {

                    return DataTypes.GEOMETRY;

                }

                return DataTypes.OBJECT;

        }

    }

    @Override

    protected void fetchGeometryTypeAndSRS(

            EditableFeatureAttributeDescriptor attr,

            ResultSetMetaData rsMetadata,

            int colIndex

    ) {

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

            return;

        }

        doFetchFromMetadataView(attr, rsMetadata, colIndex);

    }

    /**

     * Fetches the SRID and geom type from several Oracle metadata tables:

     * ALL_SDO_GEOM_METADATA, ALL_SDO_INDEX_METADATA, ALL_SDO_INDEX_INFO

     *

     * @param attr

     * @param rsMetadata

     * @param colIndex

     */

    protected void doFetchFromMetadataView(

            EditableFeatureAttributeDescriptor attr,

            ResultSetMetaData rsMetadata,

            int colIndex

    ) {

        int srid = -1;

        int dimensions = 2;

        try {

            String sql = "SELECT SRID, "

                    + "( SELECT count(*) FROM TABLE(t1.diminfo) ) dimensions "

                    + "FROM ALL_SDO_GEOM_METADATA t1 "

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

            ResultSet rs = null;

            PreparedStatement st = null;

            try {

                st = this.getConnection().prepareStatement(sql);

                st.setString(1, this.getTable().getSchema());

                st.setString(2, this.getTable().getTable().toUpperCase());

                st.setString(3, attr.getName().toUpperCase());

                rs = JDBCUtils.executeQuery(st, sql);

                if (rs.next()) {

                    srid = rs.getInt(1);

                    dimensions = rs.getInt(2);

                }

                if (srid != -1) {

                    IProjection proj = this.helper.getSRSSolver().getProjection(conn, srid);

                    attr.setSRS(proj);

                    if(this.helper.getSRSSolver().hasDatabaseCode(conn, srid)){

                        attr.getTags().set(ORACLE_SRID_ATTR_TAG, srid);

                    }

                }

            } catch (Exception ex) {

                LOGGER.debug("Can't get srs for column '" + attr.getName() + "'.", ex);

            } finally {

                JDBCUtils.closeQuietly(rs);

                JDBCUtils.closeQuietly(st);

            }

            if(srid == -1) {

                sql = "SELECT SRID, "

                        + "( SELECT count(*) FROM TABLE(t1.diminfo) ) dimensions "

                        + "FROM USER_SDO_GEOM_METADATA t1 "

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

                rs = null;

                st = null;

                try {

                    st = this.getConnection().prepareStatement(sql);

                    st.setString(1, this.getTable().getTable().toUpperCase());

                    st.setString(2, attr.getName().toUpperCase());

                    rs = JDBCUtils.executeQuery(st, sql);

                    if (rs.next()) {

                        srid = rs.getInt(1);

                        dimensions = rs.getInt(2);

                    }

                    if (srid != -1) {

                        IProjection proj = this.helper.getSRSSolver().getProjection(conn, srid);

                        attr.setSRS(proj);

                    }

                } catch (Exception ex) {

                    LOGGER.debug("Can't get srs for column '" + attr.getName() + "'.", ex);

                } finally {

                    JDBCUtils.closeQuietly(rs);

                    JDBCUtils.closeQuietly(st);

                }

            }

        } catch (Exception ex) {

            LOGGER.debug("Can't get geometry type and srs from column '" + attr.getName() + "'.", ex);

        }

        String oracleGeomType = doFetchFromSpatialIndex(attr.getName());

        GeometryType gt;

        if (oracleGeomType != null) {

            gt = GeometryTypeUtils.getGeometryTypeFromOracleName(oracleGeomType, dimensions);

            if(gt.getType() == Geometry.TYPES.GEOMETRY) {

                gt = doFetchFromOgisGeometryTable(attr);                

            }

        } else {

            gt = doFetchFromOgisGeometryTable(attr);

        }

        if (gt == null) {

            switch (dimensions) {

                case 4:

                    gt = GeometryTypeUtils.getGeometryTypeFromDatabaseTypeName("ZM");

                    break;

                case 3:

                    gt = GeometryTypeUtils.getGeometryTypeFromDatabaseTypeName(null);

                    break;

                case 2:

                    gt = GeometryTypeUtils.getGeometryTypeFromDatabaseTypeName("");

                    break;

                default:

                    gt = GeometryTypeUtils.getGeometryTypeFromDatabaseTypeName(null);

                    break;

            }

        }

        attr.setGeometryType(gt);

    }

    protected GeometryType doFetchFromOgisGeometryTable(EditableFeatureAttributeDescriptor attr) {

        String sql = "SELECT GEOMETRY_TYPE "

                + "FROM MDSYS.OGIS_GEOMETRY_COLUMNS "

                + "WHERE F_TABLE_SCHEMA = ? AND F_TABLE_NAME = ? AND F_GEOMETRY_COLUMN = ?";

        ResultSet rs = null;

        PreparedStatement st = null;

        try {

            st = this.getConnection().prepareStatement(sql);

            st.setString(1, this.getTable().getSchema());

            st.setString(2, this.getTable().getTable());

            st.setString(3, attr.getName());

            rs = JDBCUtils.executeQuery(st, sql);

            int geomType;

            if (rs.next()) {

                geomType = rs.getInt(1);

                return GeometryTypeUtils.getGeometryTypeFromSFSTypeCode(geomType);

            }

        } catch (Exception ex) {

            LOGGER.debug("Can't get geometry type from MDSYS.OGIS_GEOMETRY_COLUMNS '" + attr.getName() + "'.", ex);

        } finally {

            JDBCUtils.closeQuietly(rs);

            JDBCUtils.closeQuietly(st);

        }

        return null;

    }

    /**

     * Fetches the geometry type from the spatial index definition. Note that

     * this method does not allow to distinguish XYZ geoms from XYM geoms, so

     * XYM is reported as XYZ.

     *

     * @param geomColumnName the name of the column containing the geometry

     * @return An Oracle geometry type name, or "DEFAULT" if an spatial index is

     * not defined for the geom

     */

    protected String doFetchFromSpatialIndex(

            String geomColumnName

    ) {

        try {

            String sql = "SELECT SDO_LAYER_GTYPE "

                    + "FROM ALL_SDO_INDEX_METADATA tmd, ALL_SDO_INDEX_INFO tinfo "

                    + "WHERE tmd.SDO_INDEX_OWNER = tinfo.SDO_INDEX_OWNER "

                    + "AND tmd.SDO_INDEX_NAME = tinfo.INDEX_NAME "

                    + "AND TABLE_OWNER = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?";

            ResultSet rs = null;

            PreparedStatement st = null;

            try {

                st = this.getConnection().prepareStatement(sql);

                st.setString(1, this.getTable().getSchema());

                st.setString(2, this.getTable().getTable());

                st.setString(3, geomColumnName);

                rs = JDBCUtils.executeQuery(st, sql);

                String geomType;

                if (rs.next()) {

                    geomType = rs.getString(1);

                    return geomType;

                }

            } finally {

                JDBCUtils.closeQuietly(rs);

                JDBCUtils.closeQuietly(st);

            }

        } catch (Exception ex) {

            LOGGER.debug("Can't get geometry type from column '" + geomColumnName + "'.", ex);

        }

        return null;

    }

    protected Set<String> fetchTriggers(JDBCConnection connection, String schema, String table) {

        if (tableTriggers == null) {

            String getTriggersSql = "SELECT TRIGGER_NAME FROM ALL_TRIGGERS"

                    + " WHERE OWNER = ? AND TABLE_NAME = ? AND STATUS = 'ENABLED'";

            ResultSet rs = null;

            PreparedStatement st = null;

            Set<String> triggers = new HashSet<>();

            try {

                st = connection.prepareStatement(getTriggersSql);

                st.setString(1, schema);

                st.setString(2, table);

                rs = JDBCUtils.executeQuery(st, getTriggersSql);

                while (rs.next()) {

                    triggers.add(rs.getString(1));

                }

            } catch (Exception ex) {

            } finally {

                JDBCUtils.closeQuietly(rs);

                JDBCUtils.closeQuietly(st);

            }

            tableTriggers = triggers;

        }

        return tableTriggers;

    }

    @Override

    protected EditableFeatureAttributeDescriptor getAttributeFromMetadata(EditableFeatureType type, JDBCConnection conn,

            ResultSetMetaData rsMetadata, int colIndex) throws SQLException {

        OracleSQLBuilder sqlbuilder = (OracleSQLBuilder) this.helper.createSQLBuilder();

//        EditableFeatureAttributeDescriptor attr = super.getAttributeFromMetadata(type, conn, rsMetadata, colIndex);

        EditableFeatureAttributeDescriptor attr = type.add(

                rsMetadata.getColumnName(colIndex),

                this.getDataTypeFromMetadata(rsMetadata, colIndex)

        );

        attr.setAllowNull(

            rsMetadata.isNullable(colIndex) == ResultSetMetaData.columnNullable

        );

        attr.setIsAutomatic(rsMetadata.isAutoIncrement(colIndex));

        attr.setIsReadOnly(rsMetadata.isReadOnly(colIndex));

        switch(attr.getType()) {

            case DataTypes.STRING:

              attr.setSize(rsMetadata.getPrecision(colIndex));

              attr.setPrecision(DataType.PRECISION_NONE);

              attr.setScale(DataType.SCALE_NONE);

              break;

            case DataTypes.BYTE:

              attr.setDisplaySize(rsMetadata.getColumnDisplaySize(colIndex));

              attr.setPrecision(DataType.BYTE_DEFAULT_PRECISION);

              attr.setScale(DataType.SCALE_NONE);

              break;

            case DataTypes.INT:

            case DataTypes.LONG:

              attr.setPrecision(rsMetadata.getPrecision(colIndex));

              attr.setScale(DataType.SCALE_NONE);

              break;

            case DataTypes.FLOAT:

            case DataTypes.DOUBLE:

            case DataTypes.DECIMAL:

              attr.setDisplaySize(rsMetadata.getColumnDisplaySize(colIndex));

              attr.setScale(rsMetadata.getScale(colIndex));

              attr.setPrecision(rsMetadata.getPrecision(colIndex));

              break;

            case DataTypes.OBJECT:

                attr.setAdditionalInfo(

                        "SQLType",

                        rsMetadata.getColumnType(colIndex)

                );

                attr.setAdditionalInfo(

                        "SQLTypeName",

                        rsMetadata.getColumnTypeName(colIndex)

                );

                break;

            case DataTypes.GEOMETRY:

                this.fetchGeometryTypeAndSRS(attr, rsMetadata, colIndex);

                break;

        }

        String serialTriggerName = sqlbuilder.getSerialTriggerName(this.getTable().getTable(), attr.getName());

        if (fetchTriggers(conn, this.getTable().getSchema(), this.getTable().getTable()).contains(serialTriggerName)) {

            attr.setIsAutomatic(true);

        }

        return attr;

    }

}