gvSIG data provider for Oracle

package org.gvsig.oracle.dal.operations;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.cresques.cts.IProjection;

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.feature.spi.ExpressionBuilderBase;

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

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

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

import org.gvsig.fmap.geom.GeometryManager;

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

public class OracleFetchFeatureTypeOperation extends FetchFeatureTypeOperation {

    private static Map<String,GeometryType>databaseGeometryTypes = null;

    private static Map<Integer,GeometryType>databaseGeometryTypeNumbers = null;

    private static final int UNKNOWN_GEOMETRY = 0;

    private static final int POINT = 1;

    private static final int LINE_OR_CURVE = 2;

    private static final int POLYGON = 3;

    private static final int COLLECTION = 4;

    private static final int MULTIPOINT = 5;

    private static final int MULTILINE_OR_MULTICURVE = 6;

    private static final int MULTIPOLYGON = 7;

    public OracleFetchFeatureTypeOperation(

            JDBCHelper helper

        ) {

        super(helper);

    }

    private GeometryType getGT(

            GeometryManager manager, 

            int type, 

            int subtype

        ) {

        try {

            return manager.getGeometryType(type, subtype);

        } catch (Exception ex) {

            return null;

        }

    }

    public OracleFetchFeatureTypeOperation(

            JDBCHelper helper,

            EditableFeatureType featureType,

            String dbname,

            String schema,

            String table,

            List<String> primaryKeys,

            String defaultGeometryColumn,

            IProjection crs

        ) {

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

    }            

    @Override

    public void fetch(EditableFeatureType featureType, Connection conn, String dbname, String schema, String table, List<String> pks, String defaultGeometryColumn, IProjection crs) throws DataException {

        super.fetch(featureType, conn, dbname, schema, table, pks, defaultGeometryColumn, crs);

    }

    @Override

    protected int getDataTypeFromMetadata(

            ResultSetMetaData rsMetadata,

            int colIndex

        ) throws SQLException {

            int type = rsMetadata.getColumnType(colIndex);

            if (type>=2000 && type<=4449) {

                    return DataTypes.GEOMETRY;

            }

            String typeName = rsMetadata.getColumnTypeName(colIndex);

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

            return DataTypes.GEOMETRY;

        }

        return super.getDataTypeFromMetadata(rsMetadata, colIndex);

    }

    @Override

    protected void fetchGeometryTypeAndSRS(

            EditableFeatureAttributeDescriptor attr,

            ResultSetMetaData rsMetadata,

            int colIndex

        ) {

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

            return;

        }

        try {

                /**

                 * Directly querying the table to get the geometry type and srid.

                 * This will perform a full scan on the table :-(

                 * 

                 * We can also get the SRID and number of dimensions from 

                 * MDSYS.USER_SDO_GEOM_METADATA or MDSYS.SDO_GEOM_METADATA_TABLE,

                 * but we can't get the geometry type (point, line, etc) from there.

                 */

            JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

            ;

            sqlbuilder.select().distinct();

            sqlbuilder.select().column().value(sqlbuilder.function("SDO_GTYPE", "({0}).SDO_GTYPE", sqlbuilder.variable(attr.getName())));

            sqlbuilder.select().column().value(sqlbuilder.ST_SRID(sqlbuilder.variable(attr.getName())));

            sqlbuilder.select().from().table().schema(this.getSchema());

            sqlbuilder.select().from().table().name(this.getTablename());

            Statement st = null;

            ResultSet rs = null;

            ArrayList<Integer> sridList = new ArrayList<Integer>();

            ArrayList<Integer> geometryCodeList = new ArrayList<Integer>();

            try {

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

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

                while (rs.next()) {

                        geometryCodeList.add(rs.getInt(1));

                        sridList.add(rs.getInt(2));

                }

            } finally {

                JDBCUtils.closeQuietly(rs);

                JDBCUtils.closeQuietly(st);

            }

            try {

                    if( sridList.size()==1 ) {

                        attr.setSRS(this.helper.getProjectionFromDatabaseCode(sridList.get(0).toString()));

                    }

                    else if (sridList.size()>1)  {

                            logger.error("More than one CRS detected on the layer: "+sridList.toString()+". Some geometries will be incorrectly handled.");

                    }

            } catch (Exception ex) {

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

            }

            if( geometryCodeList.size() == 1 ) {

                GeometryType gt = getGeometryTypeFromDatabaseTypeNumber(geometryCodeList.get(0));

                attr.setGeometryType(gt);

            }

            return;

        } catch (Exception ex) {

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

        }

        attr.setGeometryType(getGeometryTypeFromDatabaseTypeName("GEOMETRY"));

    }

    private GeometryType getGeometryTypeFromDatabaseTypeName(String typeName) {

        if( databaseGeometryTypes==null ) {

            GeometryManager manager = GeometryLocator.getGeometryManager();

            databaseGeometryTypes = new HashMap<>();

            databaseGeometryTypes.put("POINT", getGT(manager, Geometry.TYPES.POINT,Geometry.SUBTYPES.GEOM2D));

            databaseGeometryTypes.put("POINTZ", getGT(manager, Geometry.TYPES.POINT,Geometry.SUBTYPES.GEOM3D));

            databaseGeometryTypes.put("POINTM", getGT(manager, Geometry.TYPES.POINT,Geometry.SUBTYPES.GEOM2DM));

            databaseGeometryTypes.put("POINTZM", getGT(manager, Geometry.TYPES.POINT,Geometry.SUBTYPES.GEOM3DM));

            databaseGeometryTypes.put("LINESTRING", getGT(manager, Geometry.TYPES.LINE,Geometry.SUBTYPES.GEOM2D));

            databaseGeometryTypes.put("LINESTRINGZ", getGT(manager, Geometry.TYPES.LINE,Geometry.SUBTYPES.GEOM3D));

            databaseGeometryTypes.put("LINESTRINGM", getGT(manager, Geometry.TYPES.LINE,Geometry.SUBTYPES.GEOM2DM));

            databaseGeometryTypes.put("LINESTRINGZM", getGT(manager, Geometry.TYPES.LINE,Geometry.SUBTYPES.GEOM3DM));

            databaseGeometryTypes.put("POLYGON", getGT(manager, Geometry.TYPES.POLYGON,Geometry.SUBTYPES.GEOM2D));

            databaseGeometryTypes.put("POLYGONZ", getGT(manager, Geometry.TYPES.POLYGON,Geometry.SUBTYPES.GEOM3D));

            databaseGeometryTypes.put("POLYGONM", getGT(manager, Geometry.TYPES.POLYGON,Geometry.SUBTYPES.GEOM2DM));

            databaseGeometryTypes.put("POLYGONZM", getGT(manager, Geometry.TYPES.POLYGON,Geometry.SUBTYPES.GEOM3DM));

            databaseGeometryTypes.put("MULTIPOINT", getGT(manager, Geometry.TYPES.MULTIPOINT,Geometry.SUBTYPES.GEOM2D));

            databaseGeometryTypes.put("MULTIPOINTZ", getGT(manager, Geometry.TYPES.MULTIPOINT,Geometry.SUBTYPES.GEOM3D));

            databaseGeometryTypes.put("MULTIPOINTM", getGT(manager, Geometry.TYPES.MULTIPOINT,Geometry.SUBTYPES.GEOM2DM));

            databaseGeometryTypes.put("MULTIPOINTZM", getGT(manager, Geometry.TYPES.MULTIPOINT,Geometry.SUBTYPES.GEOM3DM));

            databaseGeometryTypes.put("MULTILINESTRING", getGT(manager, Geometry.TYPES.MULTILINE,Geometry.SUBTYPES.GEOM2D));

            databaseGeometryTypes.put("MULTILINESTRINGZ", getGT(manager, Geometry.TYPES.MULTILINE,Geometry.SUBTYPES.GEOM3D));

            databaseGeometryTypes.put("MULTILINESTRINGM", getGT(manager, Geometry.TYPES.MULTILINE,Geometry.SUBTYPES.GEOM2DM));

            databaseGeometryTypes.put("MULTILINESTRINGZM", getGT(manager, Geometry.TYPES.MULTILINE,Geometry.SUBTYPES.GEOM3DM));

            databaseGeometryTypes.put("MULTIPOLYGON", getGT(manager, Geometry.TYPES.MULTIPOLYGON,Geometry.SUBTYPES.GEOM2D));

            databaseGeometryTypes.put("MULTIPOLYGONZ", getGT(manager, Geometry.TYPES.MULTIPOLYGON,Geometry.SUBTYPES.GEOM3D));

            databaseGeometryTypes.put("MULTIPOLYGONM", getGT(manager, Geometry.TYPES.MULTIPOLYGON,Geometry.SUBTYPES.GEOM2DM));

            databaseGeometryTypes.put("MULTIPOLYGONZM", getGT(manager, Geometry.TYPES.MULTIPOLYGON,Geometry.SUBTYPES.GEOM3DM));

            databaseGeometryTypes.put("GEOMETRY", getGT(manager, Geometry.TYPES.GEOMETRY,Geometry.SUBTYPES.GEOM2D));

            databaseGeometryTypes.put("GEOMETRYZ", getGT(manager, Geometry.TYPES.GEOMETRY,Geometry.SUBTYPES.GEOM3D));

            databaseGeometryTypes.put("GEOMETRYM", getGT(manager, Geometry.TYPES.GEOMETRY,Geometry.SUBTYPES.GEOM2DM));

            databaseGeometryTypes.put("GEOMETRYZM", getGT(manager, Geometry.TYPES.GEOMETRY,Geometry.SUBTYPES.GEOM3DM));

        }

        return databaseGeometryTypes.get(typeName);

    }

    private GeometryType getGeometryTypeFromDatabaseTypeNumber(int typeCode) {

            int dimensions = typeCode/1000;

            int typeCodeRemainder = typeCode - 1000*dimensions;

            int mDimPos = typeCodeRemainder/100;

            int geomType = typeCodeRemainder - 100*mDimPos;

            String baseType;

            switch(geomType) {

            case POINT:

                    baseType = "POINT";

                    break;

            case LINE_OR_CURVE:

                    baseType = "LINESTRING";

                    break;

            case POLYGON:

                    baseType = "POLYGON";

                    break;

            case MULTIPOINT:

                    baseType = "MULTIPOINT";

                    break;

            case MULTILINE_OR_MULTICURVE:

                    baseType = "MULTILINESTRING";

                    break;

            case MULTIPOLYGON:

                    baseType = "MULTIPOLYGON";

                    break;

            default:

                    baseType = "GEOMETRY";

                    break;

            }

                if (dimensions==3) {

                        if (mDimPos>0) {

                                return getGeometryTypeFromDatabaseTypeName(baseType+"M");

                        }

                        return getGeometryTypeFromDatabaseTypeName(baseType+"Z");

                }

                if (dimensions==4) {

                        return getGeometryTypeFromDatabaseTypeName(baseType+"ZM");

                }

                return getGeometryTypeFromDatabaseTypeName(baseType);

    }

}