Application: gvSIG desktop » gvSIG data provider for PostgreSQL

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <artifactId>org.gvsig.postgresql</artifactId>

  <version>2.0.65</version>

  <packaging>pom</packaging>

  <name>${project.artifactId}</name>

  <description>PostgreSQL support fort DAL and gvSIG</description>

  <parent>

      <groupId>org.gvsig</groupId>

      <artifactId>org.gvsig.desktop</artifactId>

      <version>2.0.164</version>

  </parent>

  <url>https://devel.gvsig.org/redmine/projects/gvsig-postgresql</url>

  <scm>

      <connection>scm:svn:https://devel.gvsig.org/svn/gvsig-postgresql/tags/org.gvsig.postgresql-2.0.65</connection>

      <developerConnection>scm:svn:https://devel.gvsig.org/svn/gvsig-postgresql/tags/org.gvsig.postgresql-2.0.65</developerConnection>

      <url>https://devel.gvsig.org/redmine/projects/gvsig-postgresql/repository/show/tags/org.gvsig.postgresql-2.0.65</url>

  </scm>

    <repositories>

        <repository>

            <id>gvsig-public-http-repository</id>

            <name>gvSIG maven public HTTP repository</name>

            <url>http://devel.gvsig.org/m2repo/j2se</url>

            <releases>

                <enabled>true</enabled>

                <updatePolicy>daily</updatePolicy>

                <checksumPolicy>warn</checksumPolicy>

            </releases>

            <snapshots>

                <enabled>true</enabled>

                <updatePolicy>daily</updatePolicy>

                <checksumPolicy>warn</checksumPolicy>

            </snapshots>

        </repository>

    </repositories>

	<build>

		<plugins>

			<plugin>

				<groupId>org.apache.maven.plugins</groupId>

				<artifactId>maven-release-plugin</artifactId>

				<configuration>

					<tagBase>https://devel.gvsig.org/svn/gvsig-postgresql/tags/</tagBase>

					<goals>deploy</goals>

				</configuration>

			</plugin>

		</plugins>

	</build>

  <dependencyManagement>

      <dependencies>

          <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.postgresql.provider</artifactId>

            <version>2.0.65</version>

          </dependency>

          <dependency>

            <groupId>org.gvsig</groupId>

            <artifactId>org.gvsig.postgresql.app.mainplugin</artifactId>

            <version>2.0.65</version>

          </dependency>

      </dependencies>

  </dependencyManagement>

  <dependencies>

        <dependency>

            <groupId>org.slf4j</groupId>

            <artifactId>slf4j-api</artifactId>

            <scope>compile</scope>

        </dependency>

  </dependencies>

  <modules>

    <module>org.gvsig.postgresql.app</module>

    <module>org.gvsig.postgresql.provider</module>

  </modules>

</project>

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <artifactId>org.gvsig.postgresql.provider</artifactId>

  <packaging>jar</packaging>

  <name>${project.artifactId}</name>

  <description>DAL provider for PostgeSQL files</description>

  <parent>

      <groupId>org.gvsig</groupId>

      <artifactId>org.gvsig.postgresql</artifactId>

      <version>2.0.65</version>

  </parent>

  <dependencies>

    <dependency>

        <groupId>com.googlecode.log4jdbc</groupId>

        <artifactId>log4jdbc</artifactId>

        <scope>compile</scope>

    </dependency>

    <dependency>

        <groupId>org.gvsig</groupId>

        <artifactId>org.gvsig.tools.lib</artifactId>

        <scope>compile</scope>

    </dependency>

    <dependency>

        <groupId>org.gvsig</groupId>

        <artifactId>org.gvsig.projection.api</artifactId>

        <scope>compile</scope>

    </dependency>

    <dependency>

        <groupId>org.gvsig</groupId>

        <artifactId>org.gvsig.fmap.dal.api</artifactId>

        <scope>compile</scope>

    </dependency>

    <dependency>

        <groupId>org.gvsig</groupId>

        <artifactId>org.gvsig.fmap.dal.db.jdbc</artifactId>

        <scope>compile</scope>

    </dependency>

    <dependency>

        <groupId>org.gvsig</groupId>

        <artifactId>org.gvsig.fmap.dal.db.lib</artifactId>

        <scope>compile</scope>

    </dependency>

    <dependency>

        <groupId>org.gvsig</groupId>

        <artifactId>org.gvsig.fmap.dal.spi</artifactId>

        <scope>compile</scope>

    </dependency>

    <dependency>

        <groupId>org.gvsig</groupId>

        <artifactId>org.gvsig.metadata.lib.basic.api</artifactId>

        <scope>compile</scope>

    </dependency>

    <dependency>

        <groupId>org.gvsig</groupId>

        <artifactId>org.gvsig.fmap.geometry.api</artifactId>

        <scope>compile</scope>

    </dependency>

    <!--

    <dependency>

        <groupId>org.gvsig</groupId>

        <artifactId>org.gvsig.fmap.geometry.operation</artifactId>

        <scope>compile</scope>

    </dependency>

    -->

    <dependency>

        <groupId>org.gvsig</groupId>

        <artifactId>org.gvsig.timesupport.lib.api</artifactId>

        <scope>compile</scope>

    </dependency>  

    <dependency>

      <groupId>org.gvsig</groupId>

      <artifactId>org.gvsig.utils</artifactId>

      <scope>compile</scope>

    </dependency>

    <dependency>

      <groupId>postgresql</groupId>

      <artifactId>postgresql</artifactId>

      <scope>compile</scope>

    </dependency>

    <dependency>

      <groupId>commons-dbcp</groupId>

      <artifactId>commons-dbcp</artifactId>

      <scope>compile</scope>

    </dependency>

    <dependency>

      <groupId>commons-collections</groupId>

      <artifactId>commons-collections</artifactId>

      <scope>compile</scope>

    </dependency>

    <!-- TESTS -->

    <dependency>

      <groupId>org.gvsig</groupId>

      <artifactId>org.gvsig.tools.lib</artifactId>

      <type>test-jar</type>

      <scope>test</scope>

    </dependency>

    <dependency>

      <groupId>org.gvsig</groupId>

      <artifactId>org.gvsig.compat.se</artifactId>

      <scope>test</scope>

    </dependency>

    <dependency>

      <groupId>org.gvsig</groupId>

      <artifactId>org.gvsig.fmap.dal.impl</artifactId>

      <type>test-jar</type>

      <scope>test</scope>

    </dependency>

    <!--

    <dependency>

      <groupId>org.gvsig</groupId>

      <artifactId>org.gvsig.fmap.dal.index.spatial.jts</artifactId>

      <scope>test</scope>

    </dependency>

    <dependency>

      <groupId>org.gvsig</groupId>

      <artifactId>org.gvsig.fmap.geometry.generalpath</artifactId>

      <scope>test</scope>

    </dependency>

    -->

    <dependency>

      <groupId>org.gvsig</groupId>

      <artifactId>org.gvsig.projection.cresques.impl</artifactId>

      <scope>test</scope>

    </dependency>

    <dependency>

      <groupId>org.gvsig</groupId>

      <artifactId>org.gvsig.timesupport.lib.impl</artifactId>

      <scope>test</scope>

    </dependency>   

  </dependencies>

</project>

org.gvsig.fmap.dal.store.postgresql.PostgreSQLLibrary

<?xml version="1.0"?>

<definitions>

  <version>1.0.0</version>

  <classes>

    <class name="PostgreSQL" namespace="Metadata">

      <extends>

      	<class name="JDBC" namespace="Metadata"/>

      </extends>

      <description>Metadata of a PostgreSQL store</description>

      <fields>

      </fields>

    </class>

  </classes>

</definitions>  

<?xml version="1.0"?>

<definitions>

  <version>1.0.0</version>

  <classes>

    <class name="PostgreSQLResourceParameters">

      <extends>

        <class>JDBCResourceParameters</class>

      </extends>

      <fields>

        <field name="JDBCDriverClass" type="string" mandatory="true"

          defaultValue="org.postgresql.Driver" group="Advanced">

          <description>JDBC Driver class</description>

        </field>

		<field name="port" type="integer" mandatory="false"

          defaultValue="5432" group="Connection">

          <description></description>

        </field>

        <field name="host" type="string" mandatory="false" 

            defaultValue="127.0.0.1" group="Connection">

          <description></description>

        </field>

        <field name="UseSSL" type="boolean" mandatory="false"

          defaultValue="false" group="Basic">

          <description>Use SSL connetion</description>

        </field>

      </fields>

    </class>

    <class name="PostgreSQLStoreParameters">

      <extends>

        <class>JDBCStoreParameters</class>

        <class>PostgreSQLResourceParameters</class>

      </extends>

      <fields/>

    </class>

    <class name="PostgreSQLNewStoreParameters">

      <extends>

        <class>JDBCNewStoreParameters</class>

        <class>PostgreSQLResourceParameters</class>

      </extends>

      <fields/>

    </class>

    <class name="PostgreSQLServerExplorerParameters">

      <extends>

        <class>PostgreSQLResourceParameters</class>

        <class>JDBCServerExplorerParameters</class>

      </extends>

      <fields/>

    </class>

  </classes>

</definitions>  

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

 *

 */

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

import org.gvsig.fmap.dal.feature.EditableFeatureType;

import org.gvsig.fmap.dal.feature.FeatureType;

import org.gvsig.fmap.dal.feature.NewFeatureStoreParameters;

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

public class PostgreSQLNewStoreParameters extends JDBCNewStoreParameters

        implements NewFeatureStoreParameters, PostgreSQLConnectionParameters {

    public static final String PARAMETERS_DEFINITION_NAME = "PostgreSQLNewStoreParameters";

    public PostgreSQLNewStoreParameters() {

        super(PARAMETERS_DEFINITION_NAME, PostgreSQLStoreProvider.NAME);

    }

    public EditableFeatureType getDefaultFeatureType() {

        return (EditableFeatureType) this.getDynValue(FEATURETYPE_PARAMTER_NAME);

    }

    public void setDefaultFeatureType(FeatureType featureType) {

        this.setDynValue(FEATURETYPE_PARAMTER_NAME, featureType);

    }

    public Boolean getUseSSL() {

        return (Boolean) this.getDynValue(DYNFIELDNAME_USESSL);

    }

    public void setUseSSL(Boolean useSSL) {

        this.setDynValue(DYNFIELDNAME_USESSL, useSSL);

    }

}

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

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

public class PostgreSQLResourceParameters extends JDBCResourceParameters

		implements PostgreSQLConnectionParameters {

	public static final String PARAMETERS_DEFINITION_NAME = "PostgreSQLResourceParameters";

	public PostgreSQLResourceParameters() {

		super(PARAMETERS_DEFINITION_NAME,PostgreSQLResource.NAME);

	}

    public PostgreSQLResourceParameters(String url, String host, Integer port,

			String dbName, String user, String password,

			String jdbcDriverClassName, Boolean ssl) {

		super(PARAMETERS_DEFINITION_NAME,PostgreSQLResource.NAME,url, host, port, dbName, user, password, jdbcDriverClassName);

		if (ssl != null) {

			this.setUseSSL(ssl.booleanValue());

		}

	}

	public String getUrl() {

		return PostgreSQLLibrary.getJdbcUrl(getHost(),

				getPort(),

				getDBName());

	}

	public Boolean getUseSSL() {

		return (Boolean) this.getDynValue(DYNFIELDNAME_USESSL);

	}

	public void setUseSSL(Boolean useSSL) {

		this.setDynValue(DYNFIELDNAME_USESSL, useSSL);

	}

	public void setUseSSL(boolean useSSL) {

		this.setDynValue(DYNFIELDNAME_USESSL, new Boolean(useSSL));

	}

}

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

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

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

public class PostgreSQLServerExplorerParameters extends

		JDBCServerExplorerParameters implements PostgreSQLConnectionParameters {

	public static final String PARAMETERS_DEFINITION_NAME = "PostgreSQLServerExplorerParameters";

	public PostgreSQLServerExplorerParameters() {

		super(PARAMETERS_DEFINITION_NAME, PostgreSQLServerExplorer.NAME);

	}

	public Boolean getUseSSL() {

		return (Boolean) this.getDynValue(DYNFIELDNAME_USESSL);

	}

	public void setUseSSL(Boolean useSSL) {

		this.setDynValue(DYNFIELDNAME_USESSL, useSSL);

	}

	public void setUseSSL(boolean useSSL) {

		this.setDynValue(DYNFIELDNAME_USESSL, new Boolean(useSSL));

	}

	public void validate() throws ValidateDataParametersException {

		if (getJDBCDriverClassName() == null) {

			setJDBCDriverClassName(PostgreSQLLibrary.DEFAULT_JDCB_DRIVER_NAME);

		}

		if (getUrl() == null) {

			setUrl(PostgreSQLLibrary.getJdbcUrl(getHost(), getPort(),

					getDBName()));

		}

		if (getPort() == null) {

			setPort(new Integer(5432));

		}

		super.validate();

	}

}

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

import java.sql.Connection;

import java.sql.DatabaseMetaData;

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

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.apache.commons.lang3.StringUtils;

import org.cresques.cts.IProjection;

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;

import org.postgresql.PGResultSetMetaData;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

/**

 * @author jmvivo

 *

 */

public class PostgreSQLHelper extends JDBCHelper {

	private static Logger logger = LoggerFactory

			.getLogger(PostgreSQLHelper.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;

	PostgreSQLHelper(JDBCHelperUser consumer,

			PostgreSQLConnectionParameters params)

			throws InitializeException {

		super(consumer, params);

	}

	protected void initializeResource() throws InitializeException {

		ResourceManagerProviderServices manager = (ResourceManagerProviderServices) DALLocator

		.getResourceManager();

		PostgreSQLResource resource = (PostgreSQLResource) manager

		.createAddResource(

				PostgreSQLResource.NAME, new Object[] {

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

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

						params.getPassword(),

						params.getJDBCDriverClassName(),

						((PostgreSQLConnectionParameters) 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_AsEWKB") + "("

                + 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

    public Object dalValueToJDBC(FeatureAttributeDescriptor attributeDescriptor, Object value)

            throws WriteException {

        try {

            if (value == null) {

                return null;

            }

            Date jdate = null;

            switch (attributeDescriptor.getType()) {

                case DataTypes.DATE:

                    jdate = (Date)value;

                    java.sql.Date sqldate = new java.sql.Date(jdate.getTime());

                    return sqldate;

                case DataTypes.TIME:

                    jdate = (Date)value;

                    java.sql.Time sqltime = new java.sql.Time(jdate.getTime());

                    return sqltime;

                case DataTypes.GEOMETRY:

                    IProjection srs = null;

                    byte[] wkb = null;

                    Geometry geom = null;

                    try {

                        geom = (Geometry)value;

                        Geometry geom1 = coerce(attributeDescriptor.getGeomType(), geom);

                        srs = attributeDescriptor.getSRS();

                        if (srs != null) {

                            wkb = geom1.convertToEWKBForcingType(getProviderSRID(srs), attributeDescriptor.getGeomType().getType());

                        } else {

                            wkb = geom1.convertToEWKB();

                        }

                    } catch (Exception e) {

                        String problem = "";

                        if (geom != null) {

                            Geometry.ValidationStatus vs = geom.getValidationStatus();

                            problem = vs.getMessage();

                        }

                        throw new DalValueToJDBCException(attributeDescriptor, value, problem, e);

                    }

                    return wkb;

                default:

                    return value;

            }

        } catch(DalValueToJDBCException ex) {

            throw ex;

        } catch (Exception e) {

            throw new DalValueToJDBCException(attributeDescriptor, value, e);

        }

    }

	@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) {

					if (rsMetadata instanceof PGResultSetMetaData) {

						tableId.schema = ((PGResultSetMetaData) rsMetadata)

								.getBaseSchemaName(rsIndex);

						tableId.table = ((PGResultSetMetaData) rsMetadata)

								.getBaseTableName(rsIndex);

						tableId.field = ((PGResultSetMetaData) rsMetadata)

								.getBaseColumnName(rsIndex);

					} else {

						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 instanceof PGResultSetMetaData) {

							if (!((PGResultSetMetaData) rsMetadata)

									.getBaseTableName(rsIndex).equals(

											rs.getString("f_table_name"))) {

								continue;

							}

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

							String metaSchema = ((PGResultSetMetaData) rsMetadata)

									.getBaseSchemaName(rsIndex);

							if (!metaSchema.equals(curSchema)) {

								if (metaSchema.length() == 0

										&& metaSchema == getDefaultSchema(conn)) {

								} else {

									continue;

								}

							}

						} else {

							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_AsEWKB") + "("

			    + 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();

		PostgreSQLNewStoreParameters pgNdsp = (PostgreSQLNewStoreParameters) 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);

    }

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