Application: gvSIG desktop

/**

 * 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.fmap.dal.store.jdbc2.spi.operations;

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.apache.commons.collections.CollectionUtils;

import org.apache.commons.lang3.StringUtils;

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.jdbc.exception.SQLRuntimeException;

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

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

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

import org.gvsig.fmap.geom.GeometryLocator;

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

import static org.gvsig.fmap.dal.store.jdbc2.spi.operations.AbstractConnectionOperation.LOGGER;

import org.gvsig.tools.dataTypes.DataType;

@SuppressWarnings("UseSpecificCatch")

public class FetchFeatureTypeOperation extends AbstractConnectionOperation {

    protected static class IndexInformation {

        String column_name;

        boolean ascending;

        boolean unique;

    }

    protected final EditableFeatureType featureType;

    protected final TableReference table;

    protected final List<String> primaryKeys;

    protected final String defaultGeometryColumn;

    protected final IProjection crs;

    protected final int geometryType;

    protected final int geometrySubtype;

    protected Map<String,IndexInformation> indexesInformation;

    public FetchFeatureTypeOperation(

            JDBCHelper helper

        ) {

        this(helper, null, null, null, null, null, Geometry.TYPES.UNKNOWN, Geometry.SUBTYPES.UNKNOWN);

    }

    public FetchFeatureTypeOperation(

            JDBCHelper helper,

            EditableFeatureType featureType,

            String defaultGeometryColumn,

            IProjection crs

        ) {

        this(helper, featureType, null, null, defaultGeometryColumn, crs, Geometry.TYPES.UNKNOWN, Geometry.SUBTYPES.UNKNOWN);

    }

    public FetchFeatureTypeOperation(

            JDBCHelper helper,

            EditableFeatureType featureType,

            TableReference table,

            List<String> primaryKeys,

            String defaultGeometryColumn,

            IProjection crs,

            int geometryType,

            int geometrySubtype

        ) {

        super(helper);

        this.featureType = featureType;

        this.table = table;

        this.primaryKeys = primaryKeys;

        this.defaultGeometryColumn = defaultGeometryColumn;

        this.crs = crs;

        this.geometryType = geometryType;

        this.geometrySubtype = geometrySubtype;

    }

    @Override

    public final Object perform(JDBCConnection conn) throws DataException {

        this.fetch(conn);

        return true;

    }

    protected TableReference getTable() {

        return this.table;

    }

    public void fetch(JDBCConnection conn) throws DataException {

        List<String> pks = this.primaryKeys;

        Statement st = null;

        ResultSet rs = null;

        try {

            if (CollectionUtils.isEmpty(pks)) {

                if (table.hasSubquery()) {

                    LOGGER.debug("Searching pk in a table with a subquery ("+table.toString()+".");

                }

                pks = this.getPrimaryKeysFromMetadata(conn, null, table.getSchema(), table.getTable());

                if (CollectionUtils.isEmpty(pks)) {

                    pks = getPrimaryKeysFromInformationSchema(conn);

                }

            }

            st = conn.createStatement();

            st.setFetchSize(1);

            rs = JDBCUtils.executeQuery(st, this.getSQLToRetrieveMetadataOfTable());

            ResultSetMetaData rsMetadata = rs.getMetaData();

            fetchFeatureTypeFromMetadata(conn, rsMetadata, pks);

        } catch (SQLException ex) {

            throw new SQLRuntimeException("Can't fecth feature type.",ex);

        } finally {

            JDBCUtils.closeQuietly(rs);

            JDBCUtils.closeQuietly(st);

        }

    }

    public String getSQLToRetrieveMetadataOfTable() {

      JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

      sqlbuilder.select().column().all();

      sqlbuilder.select().from().table()

          .database(this.table.getDatabase())

          .schema(this.table.getSchema())

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

//      sqlbuilder.select().from().subquery(this.table.getSubquery());

      sqlbuilder.select().limit(0);

      String sql = sqlbuilder.toString();

      return sql;

    }

    public void fetchFeatureTypeFromMetadata(JDBCConnection conn, ResultSetMetaData rsMetadata) throws SQLException {

        this.fetchFeatureTypeFromMetadata(conn, rsMetadata, new ArrayList<>());

    }

    protected void fetchFeatureTypeFromMetadata(JDBCConnection conn, ResultSetMetaData rsMetadata, List<String> pks) throws SQLException {

        int i;

        int geometriesColumns = 0;

        String lastGeometry = null;

        EditableFeatureAttributeDescriptor attr;

        boolean firstGeometryAttrFound = false;

        for (i = 1; i <= rsMetadata.getColumnCount(); i++) {

            attr = getAttributeFromMetadata(featureType, conn, rsMetadata, i);

            if ( isInPrimaryKeys(pks,attr) ) {

                attr.setIsPrimaryKey(true);

            }

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

                geometriesColumns++;

                lastGeometry = attr.getName();

                // Set the default geometry attribute if it is the one

                // given as parameter or it is the first one, just in case.

                if (!firstGeometryAttrFound || StringUtils.equalsIgnoreCase(lastGeometry, defaultGeometryColumn)) {

                    firstGeometryAttrFound = true;

                    featureType.setDefaultGeometryAttributeName(lastGeometry);

                }

            }

        }

        if (StringUtils.isBlank(defaultGeometryColumn)) {

            if (geometriesColumns == 1) {

                featureType.setDefaultGeometryAttributeName(lastGeometry);

            }

        } else if (!StringUtils.equalsIgnoreCase(defaultGeometryColumn, featureType.getDefaultGeometryAttributeName())) {

            EditableFeatureAttributeDescriptor geomattr = featureType.getEditableAttributeDescriptor(defaultGeometryColumn);

            if (geomattr.getDataType().getType() != DataTypes.GEOMETRY) {

                geomattr.setDataType(DataTypes.GEOMETRY);

                geomattr.setGeometryType(Geometry.TYPES.GEOMETRY, Geometry.SUBTYPES.GEOM2D);

            }

        }

        if (featureType.getDefaultGeometryAttribute() != null) {

            EditableFeatureAttributeDescriptor attrGeom = (EditableFeatureAttributeDescriptor) featureType.getDefaultGeometryAttribute();

            if (crs != null) {

                attrGeom.setSRS(crs);

            }

            if (geometryType != Geometry.TYPES.UNKNOWN && geometrySubtype != Geometry.SUBTYPES.UNKNOWN) {

                attrGeom.setGeometryType(geometryType, geometrySubtype);

            } else if (geometryType != Geometry.TYPES.UNKNOWN) {

                attrGeom.setGeometryType(geometryType, attrGeom.getGeomType().getSubType());

            } else if (geometrySubtype != Geometry.SUBTYPES.UNKNOWN) {

                attrGeom.setGeometryType(attrGeom.getGeomType().getType(), geometrySubtype);

            }

        }

    }

    protected boolean isInPrimaryKeys(List<String> pks, EditableFeatureAttributeDescriptor attr) {

        if( pks == null || attr == null ) {

            return false;

        }

        // En algunos gestores de BBDD, los nombres obtenidos de las pks de los 

        // metadados no coinciden con los nombres de los campos ya que unos estan

        // en mayusculas y otros en minusculas, asi que en lugar de usar un "contains"

        // nos los recorremos y comparamos con IgnoreCase.

        for (String pk : pks) {

            if( StringUtils.equalsIgnoreCase(pk, attr.getName()) ) {

                return true;

            }

        }

        return false;        

    }

    protected List<String> getPrimaryKeysFromMetadata(

            JDBCConnection conn,

            String catalog,

            String schema,

            String table) throws SQLException {

        ResultSet rsPrimaryKeys = null;

        ResultSet rs = null;

        try {

            DatabaseMetaData metadata = conn.getMetaData();

            rs = metadata.getTables(catalog, schema, table, null);

            if (!rs.next()) {

                // No tables found with default values, ignoring catalog

                rs.close();

                catalog = null;

                schema = null;

                rs = metadata.getTables(catalog, schema, table, null);

                if (!rs.next()) {

                    // table not found

                    return null;

                } else if (rs.next()) {

                    // More that one, cant identify

                    return null;

                }

            } else if (rs.next()) {

                // More that one, cant identify

                return null;

            }

            rsPrimaryKeys = metadata.getPrimaryKeys(catalog, schema, table);

            List pks = new ArrayList();

            while (rsPrimaryKeys.next()) {

                pks.add(rsPrimaryKeys.getString("COLUMN_NAME"));

            }

            return pks;

        } catch (SQLException e) {

            return null;

        } finally {

            JDBCUtils.closeQuietly(rs);

            JDBCUtils.closeQuietly(rsPrimaryKeys);

        }

    }

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

        String sql = getSQLToRetrievePrimaryKeysFromInformationSchema();

        Statement st = null;

        ResultSet rs = null;

        List<String> pks = new ArrayList();

        try {

            st = conn.createStatement();

            rs = JDBCUtils.executeQuery(st, sql);

            while (rs.next()) {

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

            }

            if (pks.isEmpty()) {

                return null;

            }

            return pks;

        } catch (Exception ex) {

            return pks;

        } finally {

            JDBCUtils.closeQuietly(rs);

            JDBCUtils.closeQuietly(st);

        }

    }

    public String getSQLToRetrievePrimaryKeysFromInformationSchema() throws SQLException {

        JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.select().column().name("COLUMN_NAME");

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

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

                "INFORMATION_SCHEMA.table_constraints t_cons "

                + "inner join INFORMATION_SCHEMA.key_column_usage c on "

                + "c.constraint_catalog = t_cons.constraint_catalog and "

                + "c.table_schema = t_cons.table_schema and "

                + "c.table_name = t_cons.table_name and "

                + "c.constraint_name = t_cons.constraint_name "

        );

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

                expbuilder.like(

                        expbuilder.custom("c.TABLE_NAME"), 

                        expbuilder.constant(table.getTable())

                )

        );

        if( table.hasSchema() ) {

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

                    expbuilder.like(

                            expbuilder.custom("c.TABLE_SCHEMA"),

                            expbuilder.constant(table.getSchema())

                    )

            );

        }

//        if (catalog != null) {

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

//                    expbuilder.like(

//                            expbuilder.custom("c.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,

            JDBCConnection conn,

            ResultSetMetaData rsMetadata,

            int colIndex

        ) throws SQLException {

        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:

              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",

                        String.valueOf(rsMetadata.getColumnType(colIndex))

                );

                attr.setAdditionalInfo(

                        "SQLTypeName",

                        rsMetadata.getColumnTypeName(colIndex)

                );

                break;

            case DataTypes.GEOMETRY:

                this.fetchGeometryTypeAndSRS(attr, rsMetadata, colIndex);

                break;

        }

        IndexInformation indexInformation = this.getIndexesInformation(conn).get(attr.getName());

        if( indexInformation!=null ) {

            attr.setIsIndexed(true);

            attr.setIsIndexAscending(indexInformation.ascending);

//            attr.setIsIndexUnique(indexInformation.unique);

        }

        return attr;

    }

    protected Map<String,IndexInformation> getIndexesInformation(

            JDBCConnection conn

        ) throws SQLException {

        if( this.indexesInformation==null ) {

            this.indexesInformation = new HashMap<>();

            DatabaseMetaData metaData = conn.getMetaData();

            if( metaData!=null ) {

                ResultSet rsIndexes = metaData.getIndexInfo(null, this.table.getSchema(), this.table.getTable(), false, false);

                if( rsIndexes!=null ) {

                    while( rsIndexes.next() ) {

                        IndexInformation x = new IndexInformation();

                        x.column_name = rsIndexes.getString("COLUMN_NAME");

                        String asc_or_desc = rsIndexes.getString("ASC_OR_DESC");

                        // ASC_OR_DESC String => column sort sequence, 

                        // "A" => ascending, 

                        // "D" => descending, 

                        // may be null if sort sequence is not supported; 

                        // null when TYPE is tableIndexStatistic

                        if( StringUtils.isNotBlank(asc_or_desc) ) {

                            if( asc_or_desc.equalsIgnoreCase("A") ) {

                                x.ascending = true;

                            } else {

                                x.ascending = false;

                            }

                        }

                        x.unique = !rsIndexes.getBoolean("NON_UNIQUE");

                        // NON_UNIQUE boolean => Can index values be non-unique. 

                        // false when TYPE is tableIndexStatistic 

                        this.indexesInformation.put(x.column_name, x);

                    }

                }

            }

        }

        return this.indexesInformation;

    }    

    protected int getDataTypeFromMetadata(

            ResultSetMetaData rsMetadata,

            int colIndex

        ) throws SQLException {

        switch (rsMetadata.getColumnType(colIndex)) {

            case java.sql.Types.TINYINT:

                return DataTypes.BYTE;

            case java.sql.Types.SMALLINT:

            case java.sql.Types.INTEGER:

                return DataTypes.INT;

            case java.sql.Types.BIGINT:

                return DataTypes.LONG;

            case java.sql.Types.REAL:

            case java.sql.Types.FLOAT:

                return DataTypes.FLOAT;

            case java.sql.Types.DOUBLE:

                return DataTypes.DOUBLE;

            case java.sql.Types.NUMERIC:

            case java.sql.Types.DECIMAL:

                return DataTypes.DECIMAL;

            case java.sql.Types.CHAR:

            case java.sql.Types.VARCHAR:

            case java.sql.Types.LONGVARCHAR:

            case java.sql.Types.CLOB:

                return DataTypes.STRING;

            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( "geometry".equalsIgnoreCase(typeName) ) {

                    return DataTypes.GEOMETRY;

                }

                return DataTypes.OBJECT;

        }

    }

    /**

     * Inicializa el tipo, subtipo y SRS del attributo de tipo geometria.

     * 

     * @param attr

     * @param rsMetadata

     * @param colIndex 

     */

    protected void fetchGeometryTypeAndSRS(

            EditableFeatureAttributeDescriptor attr,

            ResultSetMetaData rsMetadata,

            int colIndex

        ) {

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

            return;

        }

        try {

            GeometryType geomType = GeometryLocator.getGeometryManager().getGeometryType(

                    Geometry.TYPES.GEOMETRY,

                    Geometry.SUBTYPES.GEOM2D

            );

            attr.setGeometryType(geomType);

            attr.setSRS((IProjection)null);

        } catch (Exception ex) {

            LOGGER.warn("Can't get default geometry type.",ex);

        }

    }

}