Application: gvSIG desktop

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

 *

 */

/*

 * AUTHORS (In addition to CIT):

 * 2009 IVER T.I   {{Task}}

 */

/**

 *

 */

package org.gvsig.fmap.dal.store.h2;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.Comparator;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import java.util.Properties;

import java.util.TreeMap;

import java.util.TreeSet;

import org.cresques.cts.IProjection;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.gvsig.fmap.crs.CRSFactory;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataTypes;

import org.gvsig.fmap.dal.NewDataStoreParameters;

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

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

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

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

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.feature.exception.CreateGeometryException;

import org.gvsig.fmap.dal.feature.exception.UnsupportedDataTypeException;

import org.gvsig.fmap.dal.resource.ResourceAction;

import org.gvsig.fmap.dal.resource.spi.ResourceManagerProviderServices;

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

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCException;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecutePreparedSQLException;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecuteSQLException;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCPreparingSQLException;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException;

import org.gvsig.fmap.geom.Geometry;

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

import org.gvsig.tools.ToolsLocator;

import org.gvsig.tools.exception.BaseException;

/**

 * @author jmvivo

 *

 */

public class H2Helper extends JDBCHelper {

    private static Logger logger = LoggerFactory

            .getLogger(H2Helper.class);

    private Map pgSR2SRSID = new TreeMap();

    private Map srsID2pgSR = new TreeMap();

    private static Properties beforePostgis13 = null;

    private int[] postGISVersion = {0, 0, 0};

    private boolean versionSet = false;

    H2Helper(JDBCHelperUser consumer,

            H2ConnectionParameters params)

            throws InitializeException {

        super(consumer, params);

    }

    protected void initializeResource() throws InitializeException {

        ResourceManagerProviderServices manager = (ResourceManagerProviderServices) DALLocator

                .getResourceManager();

        H2Resource resource = (H2Resource) manager

                .createAddResource(

                        H2Resource.NAME, new Object[]{

                            params.getUrl(), params.getHost(),

                            params.getPort(), params.getDBName(), params.getUser(),

                            params.getPassword(),

                            params.getJDBCDriverClassName(),

                            ((H2ConnectionParameters) params).getUseSSL()});

        this.setResource(resource);

    }

    protected String getDefaultSchema(Connection conn)

            throws JDBCException {

        if ( defaultSchema == null ) {

            String sql = "Select current_schema()";

            ResultSet rs = null;

            Statement st = null;

            String schema = null;

            try {

                st = conn.createStatement();

                try {

                    rs = JDBCHelper.executeQuery(st, sql);

                } catch (java.sql.SQLException e) {

                    throw new JDBCExecuteSQLException(sql, e);

                }

                rs.next();

                schema = rs.getString(1);

            } catch (java.sql.SQLException e) {

                throw new JDBCSQLException(e);

            } finally {

                try {

                    rs.close();

                } catch (Exception e) {

                    logger.error("Exception clossing resulset", e);

                };

                try {

                    st.close();

                } catch (Exception e) {

                    logger.error("Exception clossing statement", e);

                };

                rs = null;

                st = null;

            }

            defaultSchema = schema;

        }

        return defaultSchema;

    }

    public Envelope getFullEnvelopeOfField(

            JDBCStoreParameters storeParams,

            String geometryAttrName, Envelope limit)

            throws DataException {

        StringBuilder strb = new StringBuilder();

        strb.append("Select " + getFunctionName("ST_AsBinary") + "("

                + getFunctionName("ST_Extent") + "(");

        strb.append(escapeFieldName(geometryAttrName));

        strb.append(")) from ");

        if ( storeParams.getSQL() != null

                && storeParams.getSQL().trim().length() > 0 ) {

            strb.append('(');

            strb.append(storeParams.getSQL());

            strb.append(") as _subquery_alias_ ");

        } else {

            strb.append(storeParams.tableID());

        }

        if ( limit != null || (storeParams.getBaseFilter() != null

                && storeParams.getBaseFilter().trim().length() > 0) ) {

            strb.append(" where  ");

            if ( limit != null ) {

                strb.append(" ( " + getFunctionName("ST_Intersects") + "("

                        + getFunctionName("ST_GeomFromText") + "('");

                String workAreaWkt = null;

                try {

                    workAreaWkt = limit.getGeometry().convertToWKT();

                } catch (Exception e) {

                    throw new CreateGeometryException(e);

                }

                strb.append(workAreaWkt);

                strb.append("', ");

                IProjection proj = storeParams.getCRS();

                int sridInt = this.getProviderSRID(proj);

                if ( sridInt == -1 ) {

                    throw new CreateGeometryException(

                            new Exception("CRS is null or unknown."));

                } else {

                    strb.append(Integer.toString(sridInt));

                }

                strb.append("), " + getFunctionName("ST_Envelope") + "(");

                strb.append(escapeFieldName(geometryAttrName));

                strb.append(")) ) ");

            }

            if ( storeParams.getBaseFilter() != null && storeParams.getBaseFilter().trim().length() > 0 ) {

                if ( limit != null ) {

                    strb.append(" and ");

                }

                strb.append(" ( ");

                strb.append(storeParams.getBaseFilter());

                strb.append(" ) ");

            }

        }

        final String sql = strb.toString();

        this.open();

        return (Envelope) getResource().execute(new ResourceAction() {

            public String toString() {

                return "getEnvelope";

            }

            public Object run() throws Exception {

                ResultSet rs = null;

                Statement st = null;

                Connection conn = null;

                Envelope fullEnvelope = null;

                Envelope emptyEnv

                        = geomManager.createEnvelope(Geometry.SUBTYPES.GEOM2D);

                try {

                    conn = getConnection();

                    st = conn.createStatement();

                    try {

                        rs = JDBCHelper.executeQuery(st, sql);

                    } catch (java.sql.SQLException e) {

                        throw new JDBCExecuteSQLException(sql, e);

                    }

                    if ( !rs.next() ) {

                        return emptyEnv;

                    }

                    byte[] data = rs.getBytes(1);

                    if ( data == null ) {

                        return emptyEnv;

                    }

                    Geometry geom = geomManager.createFrom(data);

                    fullEnvelope = geom.getEnvelope();

                    return fullEnvelope;

                } catch (java.sql.SQLException e) {

                    throw new JDBCSQLException(e);

                } catch (BaseException e) {

                    throw new ReadException(user.getProviderName(), e);

                } finally {

                    try {

                        rs.close();

                    } catch (Exception e) {

                    }

                    try {

                        st.close();

                    } catch (Exception e) {

                    }

                    try {

                        conn.close();

                    } catch (Exception e) {

                    }

                    rs = null;

                    st = null;

                    conn = null;

                }

            }

        });

    }

    @Override

    protected boolean supportsGeometry() {

        return true;

    }

    /**

     * Fill <code>featureType</code> geometry attributes with SRS and ShapeType

     * information stored in the table GEOMETRY_COLUMNS

     *

     * @param conn

     * @param rsMetadata

     * @param featureType

     * @throws ReadException

     */

    protected void loadSRS_and_shapeType(Connection conn,

            ResultSetMetaData rsMetadata, EditableFeatureType featureType,

            String baseSchema, String baseTable)

            throws JDBCException {

        Statement st = null;

        ResultSet rs = null;

        try {

            // Sacamos la lista de los attributos geometricos

            EditableFeatureAttributeDescriptor attr;

            List geoAttrs = new ArrayList();

            Iterator iter = featureType.iterator();

            while ( iter.hasNext() ) {

                attr = (EditableFeatureAttributeDescriptor) iter.next();

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

                    geoAttrs.add(attr);

                }

            }

            if ( geoAttrs.size() < 1 ) {

                return;

            }

                        // preparamos un set con las lista de tablas de origen

            // de los campos

            class TableId {

                public String schema = null;

                public String table = null;

                public String field = null;

                public void appendToSQL(StringBuilder strb) {

                    if ( schema == null || schema.length() == 0 ) {

                        strb

                                .append("( F_TABLE_SCHEMA = current_schema() AND F_TABLE_NAME = '");

                    } else {

                        strb.append("( F_TABLE_SCHEMA = '");

                        strb.append(schema);

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

                    }

                    strb.append(table);

                    strb.append("' AND F_GEOMETRY_COLUMN = '");

                    strb.append(field);

                    strb.append("' )");

                }

            }

            Comparator cmp = new Comparator() {

                public int compare(Object arg0, Object arg1) {

                    TableId a0 = (TableId) arg0;

                    TableId a1 = (TableId) arg1;

                    int aux = a0.field.compareTo(a1.field);

                    if ( aux != 0 ) {

                        return aux;

                    }

                    aux = a0.table.compareTo(a1.table);

                    if ( aux != 0 ) {

                        return aux;

                    }

                    if ( a0.schema == null ) {

                        if ( a1.schema == null ) {

                            aux = 0;

                        } else {

                            aux = -1;

                        }

                    } else {

                        if ( a1.schema == null ) {

                            aux = -1;

                        } else {

                            aux = a0.schema.compareTo(a1.schema);

                        }

                    }

                    return aux;

                }

            };

            TreeSet set = new TreeSet(cmp);

            TableId tableId;

            iter = geoAttrs.iterator();

            int rsIndex;

            while ( iter.hasNext() ) {

                attr = (EditableFeatureAttributeDescriptor) iter.next();

                tableId = new TableId();

                rsIndex = attr.getIndex() + 1;

                if ( baseSchema == null && baseTable == null ) {

                    tableId.schema = rsMetadata.getSchemaName(rsIndex);

                    tableId.table = rsMetadata.getTableName(rsIndex);

                    tableId.field = rsMetadata.getColumnName(rsIndex);

                } else {

                    tableId.schema = baseSchema;

                    tableId.table = baseTable;

                    tableId.field = rsMetadata.getColumnName(rsIndex);

                }

                if ( tableId.table == null || tableId.table.length() == 0 ) {

                                        // Si no tiene tabla origen (viene de algun calculo por ej.)

                    // lo saltamos ya que no estara en la tabla GEOMETRY_COLUMNS

                    continue;

                }

                set.add(tableId);

            }

            if ( set.size() == 0 ) {

                return;

            }

            // Preparamos una sql para que nos saque el resultado

            StringBuilder strb = new StringBuilder();

            strb.append("Select geometry_columns.*,auth_name || ':' || auth_srid as SRSID ");

            strb.append("from geometry_columns left join spatial_ref_sys on ");

            strb.append("geometry_columns.srid = spatial_ref_sys.srid WHERE ");

            iter = set.iterator();

            for ( int i = 0; i < set.size() - 1; i++ ) {

                tableId = (TableId) iter.next();

                tableId.appendToSQL(strb);

                strb.append(" OR ");

            }

            tableId = (TableId) iter.next();

            tableId.appendToSQL(strb);

            String sql = strb.toString();

            st = conn.createStatement();

            try {

                rs = JDBCHelper.executeQuery(st, sql);

            } catch (SQLException e) {

                throw new JDBCExecuteSQLException(sql, e);

            }

            String srsID;

            int pgSrid;

            int geometryType;

            int geometrySubtype;

            String geomTypeStr;

            int dimensions;

            IProjection srs;

            while ( rs.next() ) {

                srsID = rs.getString("SRSID");

                pgSrid = rs.getInt("SRID");

                geomTypeStr = rs.getString("TYPE").toUpperCase();

                geometryType = Geometry.TYPES.GEOMETRY;

                if ( geomTypeStr.startsWith("POINT") ) {

                    geometryType = Geometry.TYPES.POINT;

                } else if ( geomTypeStr.startsWith("LINESTRING") ) {

                    geometryType = Geometry.TYPES.CURVE;

                } else if ( geomTypeStr.startsWith("POLYGON") ) {

                    geometryType = Geometry.TYPES.SURFACE;

                } else if ( geomTypeStr.startsWith("MULTIPOINT") ) {

                    geometryType = Geometry.TYPES.MULTIPOINT;

                } else if ( geomTypeStr.startsWith("MULTILINESTRING") ) {

                    geometryType = Geometry.TYPES.MULTICURVE;

                } else if ( geomTypeStr.startsWith("MULTIPOLYGON") ) {

                    geometryType = Geometry.TYPES.MULTISURFACE;

                }

                dimensions = rs.getInt("coord_dimension");

                geometrySubtype = Geometry.SUBTYPES.GEOM2D;

                if ( dimensions > 2 ) {

                    if ( dimensions == 3 ) {

                        if ( geomTypeStr.endsWith("M") ) {

                            geometrySubtype = Geometry.SUBTYPES.GEOM2DM;

                        } else {

                            geometrySubtype = Geometry.SUBTYPES.GEOM3D;

                        }

                    } else {

                        geometrySubtype = Geometry.SUBTYPES.GEOM3DM;

                    }

                }

                addToPgSRToSRSID(pgSrid, srsID);

                iter = geoAttrs.iterator();

                while ( iter.hasNext() ) {

                    attr = (EditableFeatureAttributeDescriptor) iter.next();

                    rsIndex = attr.getIndex() + 1;

                    if ( !rsMetadata.getColumnName(rsIndex).equals(

                            rs.getString("f_geometry_column")) ) {

                        continue;

                    }

                    if ( baseSchema == null && baseTable == null ) {

                        if ( !rsMetadata.getTableName(rsIndex).equals(

                                rs.getString("f_table_name")) ) {

                            continue;

                        }

                        String curSchema = rs.getString("f_table_schema");

                        String metaSchema = rsMetadata

                                .getSchemaName(rsIndex);

                        if ( !metaSchema.equals(curSchema) ) {

                            if ( metaSchema.length() == 0

                                    && metaSchema == getDefaultSchema(conn) ) {

                            } else {

                                continue;

                            }

                        }

                    }

                    attr.setGeometryType(geometryType);

                    attr.setGeometrySubType(geometrySubtype);

                    if ( srsID != null && srsID.length() > 0 ) {

                        attr.setSRS(CRSFactory.getCRS(srsID));

                    }

                    iter.remove();

                }

                iter = geoAttrs.iterator();

                while ( iter.hasNext() ) {

                    attr = (EditableFeatureAttributeDescriptor) iter.next();

                    attr.setSRS(null);

                    attr.setGeometryType(Geometry.TYPES.GEOMETRY);

                }

            }

        } catch (java.sql.SQLException e) {

            throw new JDBCSQLException(e);

        } finally {

            try {

                rs.close();

            } catch (Exception e) {

            };

            try {

                st.close();

            } catch (Exception e) {

            };

        }

    }

    public String getSqlColumnTypeDescription(FeatureAttributeDescriptor attr) {

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

            return "geometry";

        }

        return super.getSqlColumnTypeDescription(attr);

    }

    public int getPostgisGeomDimensions(int geometrySubType) {

        switch (geometrySubType) {

        case Geometry.SUBTYPES.GEOM2D:

            return 2;

        case Geometry.SUBTYPES.GEOM2DM:

        case Geometry.SUBTYPES.GEOM3D:

            return 3;

        case Geometry.SUBTYPES.GEOM3DM:

            return 4;

        default:

            throw new UnsupportedDataTypeException(

                    ToolsLocator.getDataTypesManager().getTypeName(DataTypes.GEOMETRY),

                    DataTypes.GEOMETRY);

        }

    }

    public String getPostgisGeomType(int geometryType, int geometrySubType) {

        String pgGeomType;

        switch (geometryType) {

        case Geometry.TYPES.GEOMETRY:

            pgGeomType = "GEOMETRY";

            break;

        case Geometry.TYPES.POINT:

            pgGeomType = "POINT";

            break;

        case Geometry.TYPES.CURVE:

            pgGeomType = "LINESTRING";

            break;

        case Geometry.TYPES.SURFACE:

            pgGeomType = "POLYGON";

            break;

        case Geometry.TYPES.MULTIPOINT:

            pgGeomType = "MULTIPOINT";

            break;

        case Geometry.TYPES.MULTICURVE:

            pgGeomType = "MULTILINESTRING";

            break;

        case Geometry.TYPES.MULTISURFACE:

            pgGeomType = "MULTIPOLYGON";

            break;

        default:

            logger.warn("Can't determine PostGIS geometry type, use GEOMETRY.");

            pgGeomType = "GEOMETRY";

        }

        if ( geometrySubType == Geometry.SUBTYPES.GEOM2DM

                || geometrySubType == Geometry.SUBTYPES.GEOM3DM ) {

            pgGeomType = pgGeomType + "M";

        } /* else  if (geometrySubType == Geometry.SUBTYPES.GEOM3D) {

         throw new UnsupportedGeometryException(geometryType,

         geometrySubType);

         /

         } */

        return pgGeomType;

    }

    public int getProviderSRID(String srs) {

        if ( srs != null ) {

            Integer pgSRID = (Integer) srsID2pgSR.get(srs);

            if ( pgSRID != null ) {

                return pgSRID.intValue();

            }

            return searchpgSRID(srs);

        }

        return -1;

    }

    public int getProviderSRID(IProjection srs) {

        if ( srs != null ) {

            Integer pgSRID = (Integer) srsID2pgSR.get(srs.getAbrev());

            if ( pgSRID != null ) {

                return pgSRID.intValue();

            }

            return searchpgSRID(srs);

        }

        return -1;

    }

    private int searchpgSRID(final IProjection srs) {

        if ( srs == null ) {

            return -1;

        }

        return searchpgSRID(srs.getAbrev());

    }

    private int searchpgSRID(final String srsID) {

        if ( srsID == null ) {

            return -1;

        }

        ConnectionAction action = new ConnectionAction() {

            public Object action(Connection conn) throws DataException {

                String[] abrev = srsID.split(":");

                StringBuilder sqlb = new StringBuilder();

                sqlb.append("select srid from spatial_ref_sys where ");

                if ( abrev.length > 1 ) {

                    sqlb.append("auth_name = ? and ");

                }

                sqlb.append("auth_srid = ?");

                String sql = sqlb.toString();

                PreparedStatement st;

                try {

                    st = conn.prepareStatement(sql);

                } catch (SQLException e) {

                    throw new JDBCPreparingSQLException(sql, e);

                }

                ResultSet rs = null;

                try {

                    int i = 0;

                    if ( abrev.length > 1 ) {

                        st.setString(i + 1, abrev[i]);

                        i++;

                    }

                    st.setInt(i + 1, Integer.parseInt(abrev[i]));

                    try {

                        rs = JDBCHelper.executeQuery(st, sql);

                    } catch (SQLException e) {

                        throw new JDBCExecutePreparedSQLException(sql, abrev, e);

                    }

                    if ( !rs.next() ) {

                        return null;

                    }

                    return new Integer(rs.getInt(1));

                } catch (SQLException e) {

                    throw new JDBCSQLException(e);

                } finally {

                    try {

                        rs.close();

                    } catch (Exception e) {

                    };

                    try {

                        st.close();

                    } catch (Exception e) {

                    };

                }

            }

        };

        Integer pgSRSID = null;

        try {

            pgSRSID = (Integer) doConnectionAction(action);

        } catch (Exception e) {

            logger.error("Excetion searching pgSRS", e);

            return -1;

        }

        if ( pgSRSID != null ) {

            addToPgSRToSRSID(pgSRSID.intValue(), srsID);

            return pgSRSID.intValue();

        }

        return -1;

    }

    private void addToPgSRToSRSID(int pgSRID, String srsId) {

        if ( pgSRID < 0 || srsId == null || srsId.length() == 0 ) {

            return;

        }

        Integer pgSRIDInteger = new Integer(pgSRID);

        pgSR2SRSID.put(pgSRIDInteger, srsId);

        srsID2pgSR.put(srsId, pgSRIDInteger);

    }

    public List<String> getSqlGeometyFieldAdd(FeatureAttributeDescriptor attr,

            String table, String schema) {

                    // SELECT AddGeometryColumn({schema}, {table}, {field}, {srid}(int),

        // {geomType}(Str), {dimensions}(int))

                    // gemoType:

                    /*

         * POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING,

         * MULTIPOLYGON, GEOMETRYCOLLECTION POINTM, LINESTRINGM, POLYGONM,

         * MULTIPOINTM, MULTILINESTRINGM, MULTIPOLYGONM, GEOMETRYCOLLECTIONM

         */

        List<String> sqls = new ArrayList<String>();

        StringBuilder strb = new StringBuilder();

        strb.append("SELECT AddGeometryColumn('");

        if ( schema != null && schema.length() > 0 ) {

            strb.append(schema);

            strb.append("', '");

        }

        strb.append(table);

        strb.append("', '");

        strb.append(attr.getName().toLowerCase());

        strb.append("', ");

        strb.append(getProviderSRID(attr.getSRS()));

        strb.append(", '");

        strb.append(getPostgisGeomType(

                attr.getGeometryType(),

                attr.getGeometrySubType()

        )

        );

        strb.append("', ");

        strb.append(getPostgisGeomDimensions(attr.getGeometrySubType()));

        strb.append(")");

        sqls.add(strb.toString());

        return sqls;

    }

    public String getSqlFieldName(FeatureAttributeDescriptor attribute) {

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

            return getFunctionName("ST_AsBinary") + "("

                    + super.getSqlFieldName(attribute) + ")";

        }

        return super.getSqlFieldName(attribute);

    }

    protected EditableFeatureAttributeDescriptor createAttributeFromJDBC(

            EditableFeatureType type, Connection conn,

            ResultSetMetaData rsMetadata, int colIndex) throws SQLException {

        if ( rsMetadata.getColumnType(colIndex) == java.sql.Types.OTHER ) {

            if ( rsMetadata.getColumnTypeName(colIndex).equalsIgnoreCase(

                    "geometry") ) {

                EditableFeatureAttributeDescriptor attr = type.add(rsMetadata.getColumnName(colIndex),

                        DataTypes.GEOMETRY);

                // Set default values for geometry type

                attr.setGeometryType(Geometry.TYPES.GEOMETRY);

                attr.setGeometrySubType(Geometry.SUBTYPES.GEOM2D);

                return attr;

            }

        }

        return super.createAttributeFromJDBC(type, conn, rsMetadata, colIndex);

    }

    public List getAdditionalSqlToCreate(NewDataStoreParameters ndsp,

            FeatureType fType) {

        FeatureAttributeDescriptor attr;

        Iterator iter = fType.iterator();

        List result = new ArrayList();

        H2NewStoreParameters pgNdsp = (H2NewStoreParameters) ndsp;

        while ( iter.hasNext() ) {

            attr = (FeatureAttributeDescriptor) iter.next();

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

                result.addAll(getSqlGeometyFieldAdd(attr, pgNdsp.getTable(),

                        pgNdsp

                        .getSchema()));

            }

            if ( attr.isIndexed() ) {

                result.add(getCreateIndexStatement((JDBCNewStoreParameters) ndsp, attr));

            }

            if ( attr.isAutomatic() ) {

                // A?adimos los GRANT para la secuencia asociada a la tabla.

                String table = "\"" + pgNdsp.getSchema() + "\".\"" + pgNdsp.getTable() + "_" + attr.getName() + "_seq\" ";

                result.addAll(this.createGrantStatements(pgNdsp, table));

            }

        }

        return result;

    }

    protected String getCreateIndexStatement(JDBCNewStoreParameters params, FeatureAttributeDescriptor attr) {

        String indexName = "idx_" + params.getTable() + "_" + attr.getName();

        String statement = "CREATE ";

        if ( !attr.allowIndexDuplicateds() ) {

            statement += " UNIQUE ";

        }

        statement += "INDEX \"" + indexName + "\" ";

        statement += "ON " + params.tableID() + " ";

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

            statement += " USING GIST ";

            statement += "( \"" + attr.getName() + "\")";

        } else {

            statement += "( \"" + attr.getName() + "\"";

            if ( attr.isIndexAscending() ) {

                statement += " ASC )";

            } else {

                statement += " DESC )";

            }

        }

        return statement;

    }

    public String getSqlFieldDescription(FeatureAttributeDescriptor attr)

            throws DataException {

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

            return null;

        }

        return super.getSqlFieldDescription(attr);

    }

    public boolean allowAutomaticValues() {

        return Boolean.TRUE;

    }

    public boolean supportOffset() {

        return true;

    }

    public boolean supportsUnion() {

        return true;

    }

    public String escapeFieldName(String field) {

        /*

         if (!reservedWord(field) && 

         field.matches("[a-z][a-z0-9_]*")) {

         return field;

         }

         */

        String quote = getIdentifierQuoteString();

        return quote + field + quote;

    }

    protected EditableFeatureAttributeDescriptor createAttributeFromJDBCNativeType(

            EditableFeatureType fType, ResultSetMetaData rsMetadata, int colIndex)

            throws SQLException {

        EditableFeatureAttributeDescriptor column;

        String nativeType = rsMetadata.getColumnTypeName(colIndex);

        if ( nativeType.startsWith("int") ) {

            column = fType.add(rsMetadata.getColumnName(colIndex),

                    DataTypes.INT);

            column.setAdditionalInfo("SQLType", new Integer(rsMetadata

                    .getColumnType(colIndex)));

            column.setAdditionalInfo("SQLTypeName", rsMetadata

                    .getColumnTypeName(colIndex));

            return column;

        }

        return super.createAttributeFromJDBCNativeType(fType, rsMetadata, colIndex);

    }

    public Object dalValueToJDBC(

            FeatureAttributeDescriptor attributeDescriptor, Object object)

            throws WriteException {

        if ( "int2".equals(attributeDescriptor.getAdditionalInfo("SQLTypeName")) ) {

            return new Short(String.valueOf(object));

        }

        return super.dalValueToJDBC(attributeDescriptor, object);

    }

    // =======================================

    public String getFunctionName(String newFuncName) {

        if ( !versionSet ) {

            postGISVersion = getPostgisVersion();

            versionSet = true;

        }

        return getFunctionNameForVersion(newFuncName, postGISVersion);

    }

    private String getFunctionNameForVersion(String newFuncName, int[] pv) {

        if ( newFuncName == null || pv == null ) {

            return newFuncName;

        }

        if ( pv.length < 2 ) {

            // cannot compare

            return newFuncName;

        }

        if ( pv[0] > 1 ) {

            return newFuncName;

        }

        if ( pv[0] == 1 && pv[1] >= 3 ) {

            return newFuncName;

        }

        Properties pp = this.getBeforePostgis13Properties();

        String k = newFuncName.toLowerCase();

        String v = pp.getProperty(k);

        if ( v == null ) {

            return newFuncName;

        } else {

            return v;

        }

    }

    private int[] getPostgisVersion() {

        String sql = "SELECT PostGIS_Lib_Version()";

        ResultSet rs = null;

        Statement st = null;

        String v = null;

        Connection conn = null;

        try {

            conn = this.getConnection();

            st = conn.createStatement();

            rs = JDBCHelper.executeQuery(st, sql);

            rs.next();

            v = rs.getString(1);

            if ( v == null ) {

                throw new Exception("Returned version is NULL");

            }

        } catch (Exception exc) {

            logger.error("Unable to get Postgis version: " + exc.getMessage(), exc);

            return null;

        } finally {

            try {

                rs.close();

            } catch (Exception e) {

            };

            try {

                st.close();

            } catch (Exception e) {

            };

            try {

                conn.close();

            } catch (Exception e) {

            };

        }

        String[] vv = v.split("\\.");

        int[] resp = new int[3];

        try {

            for ( int i = 0; i < 3; i++ ) {

                resp[i] = Integer.parseInt(vv[i]);

            }

        } catch (Exception exc) {

            logger.error("Unable to parse version: " + v, exc);

            return null;

        }

        return resp;

    }

    protected Properties getBeforePostgis13Properties() {

        if ( beforePostgis13 == null ) {

            beforePostgis13 = new Properties();

            // Left side MUST be in lower case

            // Right side will be used if Postgis version < 1.3

            beforePostgis13.setProperty("st_intersects", "Intersects");

            beforePostgis13.setProperty("st_extent", "Extent");

            beforePostgis13.setProperty("st_envelope", "Envelope");

            beforePostgis13.setProperty("st_asbinary", "AsBinary");

            beforePostgis13.setProperty("st_geomfromtext", "GeomFromText");

            beforePostgis13.setProperty("st_geomfromwkb", "GeomFromWKB");

        }

        return beforePostgis13;

    }

}