Application: gvSIG desktop

package org.gvsig.fmap.dal.store.mdb.operations;

import java.sql.Connection;

import java.sql.DatabaseMetaData;

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.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.OperationsFactory.TableReference;

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

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

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;

import org.gvsig.tools.ToolsLocator;

import org.gvsig.tools.dataTypes.DataType;

@SuppressWarnings("UseSpecificCatch")

public class MDBFetchFeatureTypeOperation extends FetchFeatureTypeOperation {

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

    private Map<String,GeometryColumnInfo> geometry_column;

    private static class GeometryColumnInfo {

        public String columnName;

        public int geometryType;

        public String geometryTypeName;

        public int dimensions;

        public int srid;

        public GeometryColumnInfo(String columnName, int geometryType, String geometryTypeName, int dimensions, int srid) {

            this.columnName = columnName;

            this.geometryType = geometryType;

            this.geometryTypeName = geometryTypeName;

            this.dimensions = dimensions;

            this.srid = srid;

        }

    }

    public MDBFetchFeatureTypeOperation(

            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 MDBFetchFeatureTypeOperation(

            JDBCHelper helper,

            EditableFeatureType featureType,

            TableReference table,

            List<String> primaryKeys,

            String defaultGeometryColumn,

            IProjection crs

        ) {

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

    }            

    private GeometryColumnInfo getGeometryColumnInfo(String name) {

        if( geometry_column==null ) {

            geometry_column = new HashMap<>();

            try {

                //

                // https://github.com/orbisgis/h2gis/wiki/1.-Spatial-data#geometry-columns-view

                //

                StringBuilder where = null;

                if( table.hasDatabase() ) {

                    if( where == null ) {

                        where = new StringBuilder();

                    } else {

                        where.append(" AND ");

                    }

                    where.append("UPPER(F_TABLE_CATALOG) = '");

                    where.append(table.getDatabase().toUpperCase());

                    where.append("'");

                }

                if( table.hasSchema()) {

                    if( where == null ) {

                        where = new StringBuilder();

                    } else {

                        where.append(" AND ");

                    }

                    where.append("UPPER(F_TABLE_SCHEMA) = '");

                    where.append(table.getSchema().toUpperCase());

                    where.append("'");

                }

                if( table.hasTable()) {

                    if( where == null ) {

                        where = new StringBuilder();

                    } else {

                        where.append(" AND ");

                    }

                    where.append("UPPER(F_TABLE_NAME) = '");

                    where.append(table.getTable().toUpperCase());

                    where.append("'");

                }            

                String sql = "SELECT F_GEOMETRY_COLUMN, GEOMETRY_TYPE, COORD_DIMENSION, SRID, TYPE FROM GEOMETRY_COLUMNS WHERE " + where;

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

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

                while( rs.next() ) {

                    geometry_column.put(

                        rs.getString("F_GEOMETRY_COLUMN"), 

                        new GeometryColumnInfo(

                            rs.getString("F_GEOMETRY_COLUMN"), 

                            rs.getInt("GEOMETRY_TYPE"), 

                            rs.getString("TYPE"), 

                            rs.getInt("COORD_DIMENSION"), 

                            rs.getInt("SRID")

                        )

                    );

                }

            } catch (SQLException | AccessResourceException ex) {

                LOGGER.warn("Can't read metadata from table '"+table+"'.",ex);

            }

        }

        return this.geometry_column.get(name);

    }

    @Override

    public void fetch(Connection conn) throws DataException {

        super.fetch(conn);

    }

    @Override

    protected void fetchGeometryTypeAndSRS(

            EditableFeatureAttributeDescriptor attr,

            ResultSetMetaData rsMetadata,

            int colIndex

        ) {

        try {

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

                GeometryColumnInfo column_info = this.getGeometryColumnInfo(attr.getName());

                String type = "GEOMETRY";

                if( column_info!=null ) {

                    // H2GIS solo soporte 2D y 3D no soporta Ms

                    if( column_info.dimensions==3 ) {

                        type = column_info.geometryTypeName+"Z";

                    } else {

                        type = column_info.geometryTypeName;

                    }

                    SRSSolver solver = this.helper.getSRSSolver();

                    attr.setSRS(

                        solver.getProjection(getConnection(), column_info.srid)

                    );

                }

                GeometryType gt = getGeometryTypeFromMDBType(type);

                if( gt != null ) {

                    attr.setGeometryType(gt);

                }

            }

        } catch(Exception ex) {

            throw new RuntimeException("Can't fetch geometry type and SRS.", ex);

        }

    }

    private GeometryType getGeometryTypeFromMDBType(String typeName) {

        if( mdbGeometryTypes==null ) {

            //

            // https://github.com/orbisgis/h2gis/wiki/1.-Spatial-data#geometry-columns-view

            //

            GeometryManager manager = GeometryLocator.getGeometryManager();

            mdbGeometryTypes = new HashMap<>();

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

            mdbGeometryTypes.put("GEOMCOLLECTIONZ", getGT(manager, Geometry.TYPES.GEOMETRY,Geometry.SUBTYPES.GEOM3D));

        }

        return mdbGeometryTypes.get(typeName);

    }

    @Override

    protected List<String> getPrimaryKeysFromInformationSchema(Connection conn) throws SQLException {

//        String sql = getSQLToRetrievePrimaryKeysFromInformationSchema(conn.getMetaData());

        DatabaseMetaData metadata = conn.getMetaData();

        String tableName = table.getTable();

        ResultSet rs = metadata.getPrimaryKeys(null, null, tableName);

        List<String> pks = new ArrayList();

        try {

            while (rs.next()) {

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

            }

            if (pks.isEmpty()) {

                return null;

            }

            return pks;

        } catch (Exception ex) {

            return pks;

        } finally {

            JDBCUtils.closeQuietly(rs);

        }

    }

    @Override

    public String getSQLToRetrievePrimaryKeysFromInformationSchema() throws SQLException {

        JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

//        ExpressionBuilder expbuilder = sqlbuilder.expression();

//

//        sqlbuilder.select().column().name("COLUMN_LIST");

//        sqlbuilder.select().column().name("CONSTRAINT_TYPE");

//        sqlbuilder.select().from().table().schema("INFORMATION_SCHEMA").name("CONSTRAINTS");

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

//                expbuilder.like(

//                        expbuilder.column("TABLE_NAME"), 

//                        expbuilder.constant(table.getTable())

//                )

//        );

//        if( table.hasSchema() ) {

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

//                    expbuilder.like(

//                            expbuilder.column("TABLE_SCHEMA"),

//                            expbuilder.constant(table.getSchema())

//                    )

//            );

//        }

////        if (catalog != null) {

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

////                    expbuilder.like(

////                            expbuilder.column("CONSTRAINT_CATALOG"),

////                            expbuilder.constant(catalog)

////                    )

////            );

////        }

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

//                expbuilder.eq(

//                        expbuilder.column("CONSTRAINT_TYPE"),

//                        expbuilder.constant("PRIMARY KEY")

//                )

//        );

        return sqlbuilder.toString();

    }

    protected EditableFeatureAttributeDescriptor getAttributeFromMetadata(

            EditableFeatureType type,

            Connection conn,

            ResultSetMetaData rsMetadata,

            int colIndex

        ) throws SQLException {

        int dataType = this.getDataTypeFromMetadata(rsMetadata, colIndex);

        if (dataType == DataTypes.DECIMAL &&

                rsMetadata.getPrecision(colIndex)==19 &&

                rsMetadata.getScale(colIndex)== 0) {

            dataType = DataTypes.LONG;

        }

        EditableFeatureAttributeDescriptor attr = type.add(

                rsMetadata.getColumnName(colIndex),

                dataType

        );

        attr.setAllowNull(

            rsMetadata.isNullable(colIndex) == ResultSetMetaData.columnNullable

        );

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

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

        switch(attr.getType()) {

            case DataTypes.BOOLEAN:

              attr.setAllowNull(false);

              break;

            case DataTypes.STRING:

              DataType myDataType = null;

                try {

                    myDataType = ToolsLocator.getDataTypesManager().get(DataTypes.STRING).clone();

                } catch (CloneNotSupportedException ex) {

                    throw new RuntimeException(ex);

                }

              myDataType.addCoercion(new CoercionGeometryToString());

              attr.setDataType(myDataType);

              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:

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

              attr.setPrecision(DataType.INT_DEFAULT_PRECISION);

              attr.setScale(DataType.SCALE_NONE);

              break;

            case DataTypes.LONG:

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

              attr.setPrecision(DataType.LONG_DEFAULT_PRECISION);

              attr.setScale(DataType.SCALE_NONE);

              break;

            case DataTypes.FLOAT:

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

              attr.setPrecision(DataType.FLOAT_DEFAULT_PRECISION);

              attr.setScale(DataType.SCALE_NONE);

              break;

            case DataTypes.DOUBLE:

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

              attr.setPrecision(DataType.DOUBLE_DEFAULT_PRECISION);

              attr.setScale(DataType.SCALE_NONE);

              break;

            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;

        }

        return attr;

    }

    @Override

       protected int getDataTypeFromMetadata(

            ResultSetMetaData rsMetadata,

            int colIndex

        ) throws SQLException {

        switch (rsMetadata.getColumnType(colIndex)) {

            case java.sql.Types.CLOB:

                return DataTypes.STRING;

            case java.sql.Types.NUMERIC:

            case java.sql.Types.DECIMAL:

                if (rsMetadata.getPrecision(colIndex)==19 && rsMetadata.getScale(colIndex)== 0) {

                   return DataTypes.LONG;

                }

                return DataTypes.DECIMAL;

        }

        return super.getDataTypeFromMetadata(rsMetadata, colIndex);

    }

}