Application: gvSIG desktop » gvSIG data provider for PostgreSQL

<?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="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>  

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

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

import java.text.MessageFormat;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

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

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

import org.gvsig.fmap.dal.resource.exception.AccessResourceException;

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

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

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

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

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

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class PostgreSQLResource extends JDBCResource {

	final static private Logger logger = LoggerFactory

			.getLogger(PostgreSQLResource.class);

	public final static String NAME = "PostgreSQLResource";

	public static final String DESCRIPTION = "PostgreSQL Connection";

	public PostgreSQLResource(PostgreSQLResourceParameters parameters)

			throws InitializeException {

		super(parameters);

	}

	public String getName() throws AccessResourceException {

		PostgreSQLResourceParameters params = (PostgreSQLResourceParameters) this.getParameters();

		return MessageFormat.format("PostgreSQLResource({0},{1})",

				new Object[] { params.getUrl(),params.getUser() });

	}

        public String toString() {

            try {

                return this.getName();

            } catch(Exception ex) {

                return super.toString();

            }

        }

	protected void connectToDB() throws DataException {

		if (this.dataSource != null) {

			return;

		}

		JDBCResourceParameters jdbcParams = (JDBCResourceParameters) this

				.getParameters();

		BasicDataSource dataSource = new BasicDataSource();

		dataSource.setDriverClassName(jdbcParams.getJDBCDriverClassName());

		dataSource.setUsername(jdbcParams.getUser());

		dataSource.setPassword(jdbcParams.getPassword());

		dataSource.setUrl(jdbcParams.getUrl());

		dataSource.setMaxWait(60L * 1000); // FIXME

		// FIXME Set Pool parameters:

		/*

		dataSource.setMaxActive(maxActive);

		dataSource.setMaxIdle(maxActive);

		dataSource.setMaxOpenPreparedStatements(maxActive);

		dataSource.setMaxWait(maxActive);

		dataSource.setInitialSize(initialSize);

		dataSource.setDefaultReadOnly(defaultReadOnly);

		dataSource.setDefaultTransactionIsolation(defaultTransactionIsolation);

		dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);

		dataSource.setMinIdle(minIdle);

		dataSource.setTestOnBorrow(testOnBorrow);

		dataSource.setTestOnReturn(testOnReturn);

		dataSource.setTestWhileIdle(testOnReturn);

		dataSource

				.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);

		dataSource.setAccessToUnderlyingConnectionAllowed(allow);

		dataSource.setLoginTimeout(seconds);

		dataSource.setLogWriter(out);

		*/

		this.dataSource = dataSource;

	}	

	protected void registerJDBCDriver() throws InitializeException {

		String className = ((JDBCResourceParameters) getParameters())

				.getJDBCDriverClassName();

		if (className == null) {

			return;

		}

		Class theClass = null;

		try {

			theClass = Class.forName(className);

		} catch (Exception e){

			throw new InitializeException(e);

		}

		if (theClass == null) {

			try {

				throw new JDBCDriverClassNotFoundException(this.getName(),

						className);

			} catch (AccessResourceException e) {

				throw new InitializeException(e);

			}

		}

	}

	protected DataSource createDataSource() {

		PostgreSQLResourceParameters jdbcParams = (PostgreSQLResourceParameters) this

				.getParameters();

		BasicDataSource dataSource = new BasicDataSource();

		dataSource.setDriverClassName(jdbcParams.getJDBCDriverClassName());

		dataSource.setUsername(jdbcParams.getUser());

		dataSource.setPassword(jdbcParams.getPassword());

		dataSource.setUrl(jdbcParams.getUrl());

		dataSource.setMaxWait(60L * 1000); // FIXME

		// FIXME Set Pool parameters:

		/*

		dataSource.setMaxActive(maxActive);

		dataSource.setMaxIdle(maxActive);

		dataSource.setMaxOpenPreparedStatements(maxActive);

		dataSource.setMaxWait(maxActive);

		dataSource.setInitialSize(initialSize);

		dataSource.setDefaultReadOnly(defaultReadOnly);

		dataSource.setDefaultTransactionIsolation(defaultTransactionIsolation);

		dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);

		dataSource.setMinIdle(minIdle);

		dataSource.setTestOnBorrow(testOnBorrow);

		dataSource.setTestOnReturn(testOnReturn);

		dataSource.setTestWhileIdle(testOnReturn);

		dataSource

			.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);

		dataSource.setAccessToUnderlyingConnectionAllowed(allow);

		dataSource.setLoginTimeout(seconds);

		dataSource.setLogWriter(out);

		 */

		return dataSource;

	}

	public boolean isConnected() {

		if (dataSource == null) {

			return false;

		}

		if (dataSource instanceof BasicDataSource) {

			return ((BasicDataSource) dataSource).getNumActive() > 0

					|| ((BasicDataSource) dataSource).getNumIdle() > 0;

		}

		return true;

	}	

	private void logPoolStatus(String src) {

		if (logger.isDebugEnabled() ) {

			logger.debug(src + "  " + this.getStatusInformation());

		}

	}

        public String getStatusInformation() {

		if ( !(dataSource instanceof BasicDataSource) ) {

                    return "Connected: " + this.isConnected();

                }

        	BasicDataSource ds = (BasicDataSource) dataSource;

                String s = "Connected: " + this.isConnected() + ", " +

                        "actives: "+ ds.getNumActive() + "/"+ ds.getMaxActive() + ", " + 

                        "idle: " + ds.getNumIdle() + "/"+ ds.getMaxIdle() ;

                return s;

        }

        private static class CanGetConnectionException extends JDBCSQLException {

            public CanGetConnectionException(String datasource, SQLException cause) {

                super("Can't get a connection to the data source (%(datasource))", cause, "_CanGetConnectionException", 0);

                setValue("datasource", datasource);

            }

        }

	protected synchronized Object getTheConnection() throws DataException {

		try {

			Object conn = this.dataSource.getConnection();

			logPoolStatus("getTheConnection");

			return conn;

		} catch (SQLException e) {

			throw new CanGetConnectionException(this.toString(),e);

		}

	}	

}

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

import java.sql.Statement;

import java.util.ArrayList;

import java.util.Iterator;

import java.util.List;

import java.util.logging.Level;

import org.gvsig.fmap.dal.DataStoreParameters;

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

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

import org.gvsig.fmap.dal.resource.exception.AccessResourceException;

import org.gvsig.fmap.dal.spi.DataServerExplorerProviderServices;

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

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

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

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

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

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

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

/**

 * @author jmvivo

 *

 */

public class PostgreSQLServerExplorer extends JDBCServerExplorer {

    final static private Logger logger = LoggerFactory

            .getLogger(PostgreSQLServerExplorer.class);

    public static final String NAME = "PostgreSQLExplorer";

    public PostgreSQLServerExplorer(

            PostgreSQLServerExplorerParameters parameters,

            DataServerExplorerProviderServices services)

            throws InitializeException {

        super(parameters, services);

    }

    private PostgreSQLServerExplorerParameters getPostgreSQLParameters() {

        return (PostgreSQLServerExplorerParameters) getParameters();

    }

    protected JDBCHelper createHelper() throws InitializeException {

        return new PostgreSQLHelper(this, getPostgreSQLParameters());

    }

    public String getStoreName() {

        return PostgreSQLStoreProvider.NAME;

    }

    public String getProviderName() {

        return NAME;

    }

    protected JDBCStoreParameters createStoreParams()

            throws InitializeException, ProviderNotRegisteredException {

        PostgreSQLStoreParameters orgParams = (PostgreSQLStoreParameters) super

                .createStoreParams();

        orgParams.setUseSSL(getPostgreSQLParameters().getUseSSL());

        return orgParams;

    }

	// ****************************

    public boolean canAdd() {

        return true;

    }

    public DataStoreParameters getOpenParameters() throws DataException {

        PostgreSQLServerExplorerParameters parameters = getPostgreSQLParameters();

        PostgreSQLStoreParameters params = new PostgreSQLStoreParameters();

        params.setHost(parameters.getHost());

        params.setPort(parameters.getPort());

        params.setDBName(parameters.getDBName());

        params.setUser(parameters.getUser());

        params.setPassword(parameters.getPassword());

        params.setCatalog(parameters.getCatalog());

        params.setSchema(parameters.getSchema());

        params.setJDBCDriverClassName(parameters.getJDBCDriverClassName());

        params.setUrl(parameters.getUrl());

        return params;

    }

    protected void checkIsMine(DataStoreParameters dsp) {

        if (!(dsp instanceof PostgreSQLConnectionParameters)) {

            // FIXME Excpetion ???

            throw new IllegalArgumentException(

                    "not instance of PostgreSQLStoreParameters");

        }

        super.checkIsMine(dsp);

        PostgreSQLConnectionParameters pgp = (PostgreSQLConnectionParameters) dsp;

        if (pgp.getUseSSL().booleanValue() != getPostgreSQLParameters()

                .getUseSSL()) {

            throw new IllegalArgumentException("worng explorer: Host");

        }

    }

    public void remove(DataStoreParameters dsp) throws RemoveException {

        final PostgreSQLStoreParameters pgParams = (PostgreSQLStoreParameters) dsp;

        TransactionalAction action = new TransactionalAction() {

            public boolean continueTransactionAllowed() {

                return false;

            }

            public Object action(Connection conn) throws DataException {

                Statement st;

                try {

                    st = conn.createStatement();

                } catch (SQLException e) {

                    throw new JDBCSQLException(e);

                }

                String sqlDrop = "Drop table "

                        + pgParams.tableID();

                StringBuilder strb = new StringBuilder();

                strb.append("Delete from GEOMETRY_COLUMNS where f_table_schema = ");

                if (pgParams.getSchema() == null || pgParams.getSchema().length() == 0) {

                    strb.append("current_schema() ");

                } else {

                    strb.append('\'');

                    strb.append(pgParams.getSchema());

                    strb.append("' ");

                }

                strb.append("and f_table_name = '");

                strb.append(pgParams.getTable());

                strb.append('\'');

                String sqlDeleteFromGeometry_column = strb.toString();

                try {

                    try {

                        JDBCHelper.execute(st, sqlDrop);

                    } catch (SQLException e) {

                        throw new JDBCExecuteSQLException(sqlDrop, e);

                    }

                    try {

                        JDBCHelper.execute(st, sqlDeleteFromGeometry_column);

                    } catch (SQLException e) {

                        throw new JDBCExecuteSQLException(

                                sqlDeleteFromGeometry_column, e);

                    }

                } finally {

                    try {

                        st.close();

                    } catch (SQLException e) {

                    };

                }

                return null;

            }

        };

        try {

            this.helper.doConnectionAction(action);

        } catch (Exception e) {

            throw new RemoveException(this.getProviderName(), e);

        }

    }

    public NewDataStoreParameters getAddParameters() throws DataException {

        PostgreSQLServerExplorerParameters parameters = getPostgreSQLParameters();

        PostgreSQLNewStoreParameters params = new PostgreSQLNewStoreParameters();

        params.setHost(parameters.getHost());

        params.setPort(parameters.getPort());

        params.setDBName(parameters.getDBName());

        params.setUser(parameters.getUser());

        params.setPassword(parameters.getPassword());

        params.setCatalog(parameters.getCatalog());

        params.setSchema(parameters.getSchema());

        params.setJDBCDriverClassName(parameters.getJDBCDriverClassName());

        params.setUrl(parameters.getUrl());

        params.setUseSSL(parameters.getUseSSL());

        params.setDefaultFeatureType(this.getServerExplorerProviderServices()

                .createNewFeatureType());

        return params;

    }

    public boolean hasGeometrySupport() {

        return true;

    }

    protected PostgreSQLHelper getPgHelper() {

        return (PostgreSQLHelper) getHelper();

    }

    @Override

    public List getDataStoreProviderNames() {

        List x = new ArrayList(1);

        x.add(PostgreSQLStoreProvider.NAME);

        return x;

    }

    public void updateTableStatistics(String tableName) throws JDBCExecuteSQLException {

        String sql="";

        if( tableName.startsWith("\"") ) {

            sql = "VACUUM ANALYZE " + tableName ;

        } else {

            sql = "VACUUM ANALYZE \"" + tableName + "\"";

        }

        this.getHelper().execute(sql);

    }

}

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

import java.util.List;

import java.util.Properties;

import java.util.regex.Matcher;

import java.util.regex.Pattern;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataManager;

import org.gvsig.fmap.dal.DataServerExplorer;

import org.gvsig.fmap.dal.DataTypes;

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

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

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

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

import org.gvsig.fmap.dal.feature.spi.FeatureProvider;

import org.gvsig.fmap.dal.feature.spi.FeatureSetProvider;

import org.gvsig.fmap.dal.spi.DataStoreProviderServices;

import org.gvsig.fmap.dal.store.db.DBHelper;

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

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

import org.gvsig.fmap.geom.Geometry;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class PostgreSQLStoreProvider extends JDBCStoreProviderWriter {

	public final static Logger logger = LoggerFactory

			.getLogger(PostgreSQLStoreProvider.class);

	public static final String NAME = "PostgreSQL";

	public static final String DESCRIPTION = "PostgreSQL source";

	public static final String METADATA_DEFINITION_NAME = NAME;

	public PostgreSQLStoreProvider(PostgreSQLStoreParameters params,

			DataStoreProviderServices storeServices)

			throws InitializeException {

		super(params, storeServices, DBHelper.newMetadataContainer(METADATA_DEFINITION_NAME));

	}

	private PostgreSQLStoreParameters getPGParameters() {

		return (PostgreSQLStoreParameters) this.getParameters();

	}

	protected JDBCHelper createHelper() throws InitializeException {

	    JDBCHelper resp = new PostgreSQLHelper(this, getPGParameters());

	    return resp;

	}

	protected String fixFilter(String _filter) {

		if (_filter == null) {

			return null;

		}

		String filter = fixFunctionNames(_filter);

		// Transform SRS to code

		// GeomFromText\s*\(\s*'[^']*'\s*,\s*('[^']*')\s*\)

		String geom_from_text = this.getFunctionName("ST_GeomFromText");

		Pattern pattern = Pattern

				.compile(geom_from_text + "\\s*\\(\\s*'[^']*'\\s*,\\s*'([^']*)'\\s*\\)");

		Matcher matcher = pattern.matcher(filter);

		StringBuilder strb = new StringBuilder();

		int pos = 0;

		String srsCode;

		while (matcher.find(pos)) {

			strb.append(filter.substring(pos, matcher.start(1)));

			srsCode = matcher.group(1).trim();

			if (srsCode.startsWith("'")) {

				srsCode = srsCode.substring(1);

			}

			if (srsCode.endsWith("'")) {

				srsCode = srsCode.substring(0, srsCode.length() - 1);

			}

			strb.append(helper.getProviderSRID(srsCode));

			strb.append(filter.substring(matcher.end(1), matcher.end()));

			pos = matcher.end();

		}

		strb.append(filter.substring(pos));

		return strb.toString();

	}

    public String getName() {

		return NAME;

	}

	public FeatureSetProvider createSet(FeatureQuery query,

			FeatureType featureType) throws DataException {

		return new PostgreSQLSetProvider(this, query, featureType);

	}

	public DataServerExplorer getExplorer() throws ReadException {

		DataManager manager = DALLocator.getDataManager();

		PostgreSQLServerExplorerParameters exParams;

		PostgreSQLStoreParameters params = getPGParameters();

		try {

			exParams = (PostgreSQLServerExplorerParameters) manager

					.createServerExplorerParameters(PostgreSQLServerExplorer.NAME);

			exParams.setUrl(params.getUrl());

			exParams.setHost(params.getHost());

			exParams.setPort(params.getPort());

			exParams.setDBName(params.getDBName());

			exParams.setUser(params.getUser());

			exParams.setPassword(params.getPassword());

			exParams.setCatalog(params.getCatalog());

			exParams.setSchema(params.getSchema());

			exParams.setJDBCDriverClassName(params.getJDBCDriverClassName());

			exParams.setUseSSL(params.getUseSSL());

			return manager.openServerExplorer(PostgreSQLServerExplorer.NAME, exParams);

		} catch (DataException e) {

			throw new ReadException(this.getName(), e);

		} catch (ValidateDataParametersException e) {

			throw new ReadException(this.getName(), e);

		}

	}

	public boolean allowAutomaticValues() {

		return true;

	}

	public boolean hasGeometrySupport() {

		return true;

	}

	protected PostgreSQLHelper getPgHelper() {

		return (PostgreSQLHelper) getHelper();

	}

	public boolean canWriteGeometry(int geometryType, int geometrySubtype)

			throws DataException {

		FeatureType type = getFeatureStore().getDefaultFeatureType();

		FeatureAttributeDescriptor geomAttr = type.getAttributeDescriptor(type

								.getDefaultGeometryAttributeName());

		if (geomAttr == null) {

			return false;

		}

		if (geometrySubtype != geomAttr.getGeometrySubType()) {

			return false;

		}

		switch (geomAttr.getGeometryType()) {

		case Geometry.TYPES.GEOMETRY:

			return true;

		case Geometry.TYPES.MULTISURFACE:

			return geometryType == Geometry.TYPES.MULTISURFACE

					|| geometryType == Geometry.TYPES.SURFACE;

		case Geometry.TYPES.MULTIPOINT:

			return geometryType == Geometry.TYPES.MULTIPOINT

					|| geometryType == Geometry.TYPES.POINT;

		case Geometry.TYPES.MULTICURVE:

			return geometryType == Geometry.TYPES.MULTICURVE

					|| geometryType == Geometry.TYPES.CURVE;

		case Geometry.TYPES.MULTISOLID:

			return geometryType == Geometry.TYPES.MULTISOLID

					|| geometryType == Geometry.TYPES.SOLID;

		default:

			return geometryType == geomAttr.getGeometryType();

		}

	}

	protected void addToListFeatureValues(FeatureProvider featureProvider,

			FeatureAttributeDescriptor attrOfList,

			FeatureAttributeDescriptor attr,

			List<Object> values) throws DataException {

		super.addToListFeatureValues(featureProvider, attrOfList, attr, values);

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

			values.add(helper.getProviderSRID(attr.getSRS()));

		}

	}

	protected void prepareAttributeForInsert(

			FeatureAttributeDescriptor attr, List<String> fields, List<String> values) {

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

			fields.add(helper.escapeFieldName(attr.getName()));

			values.add(getFunctionName("ST_GeomFromWKB") + "(?,?)");

		} else {

			super.prepareAttributeForInsert(attr, fields, values);

		}

	}

	protected void prepareAttributeForUpdate(FeatureAttributeDescriptor attr,

			List<String> values) {

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

			values.add(helper.escapeFieldName(attr.getName())

					+ " = " + getFunctionName("ST_GeomFromWKB") + "(?,?)");

		} else {

			super.prepareAttributeForUpdate(attr, values);

		}

	}

	protected String getSqlStatementAddField(FeatureAttributeDescriptor attr,

			List<String> additionalStatement) throws DataException {

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

			PostgreSQLStoreParameters params = getPGParameters();

			additionalStatement.addAll(	((PostgreSQLHelper) helper)

					.getSqlGeometyFieldAdd(attr, params.getTable(), params

							.getSchema()));

		}

		return super.getSqlStatementAddField(attr, additionalStatement);

	}

	private String getSqlGeometyFieldDrop(FeatureAttributeDescriptor attr) {

		StringBuilder strb = new StringBuilder();

		PostgreSQLStoreParameters params = getPGParameters();

		strb.append("Delete from geometry_columns where f_geometry_column = '");

		strb.append(attr.getName());

		strb.append("' and f_table_nam = '");

		strb.append(params.getTable());

		strb.append("' and f_table_schema = ");

		if (params.getSchema() == null || params.getSchema().length() == 0) {

			strb.append("current_schema()");

		} else {

			strb.append("'");

			strb.append(params.getSchema());

			strb.append("'");

		}

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

			strb.append(" and f_table_catalog = '");

			strb.append(params.getCatalog());

			strb.append("'");

		}

		return strb.toString();

	}

	protected String getSqlStatementDropField(FeatureAttributeDescriptor attr,

			List<String> additionalStatement) {

		String result = super.getSqlStatementDropField(attr,

				additionalStatement);

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

			additionalStatement.add(getSqlGeometyFieldDrop(attr));

		}

		return result;

	}

	protected List<String> getSqlStatementAlterField(

			FeatureAttributeDescriptor attrOrg,

			FeatureAttributeDescriptor attrTrg, List<String> additionalStatement)

			throws DataException {

		//

		List<String> actions = super.getSqlStatementAlterField(attrOrg, attrTrg,

				additionalStatement);

		PostgreSQLStoreParameters params = getPGParameters();

		if (attrOrg.getDataType() != attrTrg.getDataType()) {

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

				additionalStatement.add(getSqlGeometyFieldDrop(attrOrg));

			}

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

				additionalStatement.addAll(((PostgreSQLHelper) helper)

						.getSqlGeometyFieldAdd(attrTrg, params.getTable(),

								params.getSchema()));

			}

		}

		if (attrOrg.getDataType() == attrTrg.getDataType()

				&& attrTrg.getType() == DataTypes.GEOMETRY) {

			// TODO Checks SRS and GeomType/Subtype

		}

		return actions;

	}

	private String getFunctionName(String newFunctionName) {

        PostgreSQLHelper hpr = getPgHelper();

        if (hpr == null) {

            logger.info("Unable to get PG helper.", new Exception("Helper is null"));

            return newFunctionName;

        } else {

            return hpr.getFunctionName(newFunctionName);

        }

    }

    private String fixFunctionNames(String _filter) {

        Properties props = this.getPgHelper().getBeforePostgis13Properties();

        Iterator iter = props.keySet().iterator();

        String kstr = null;

        String vstr = null;

        String resp = _filter;

        while (iter.hasNext()) {

            kstr = (String) iter.next();

            vstr = getPgHelper().getFunctionName(kstr);

            resp = replace(resp, kstr, vstr);

        }

        return resp;

    }

    private String replace(String str, String oldstr, String newstr) {

        if (oldstr == null || newstr == null ||

            oldstr.length() == 0 || oldstr.equals(newstr)) {

            return str;

        }

        String lowerstr = str.toLowerCase();

        String lowerold = oldstr.toLowerCase();

        if (lowerstr.indexOf(lowerold) == -1) {

            // nothing to do

            return str;

        }

        Pattern p = Pattern.compile(lowerold, Pattern.LITERAL);

        String[] parts = p.split(lowerstr); 

        StringBuffer resp = new StringBuffer();

        int auxind = 0;

        resp.append(str.subSequence(0, parts[0].length()));

        for (int i=1; i<parts.length; i++) {

            resp.append(newstr);

            auxind = getIndex(parts, i-1, oldstr.length());

            resp.append(str.subSequence(auxind, auxind + parts[i].length()));

        }

        return resp.toString();

    }

    /**

     * This method gets the index where the n-th part (0-based)

     * starts in the original string

     * 

     * @param parts

     * @param n

     * @param length

     * @return

     */

    private int getIndex(String[] parts, int till_n, int length) {

        int resp = 0;

        for (int i=0; i<(till_n+1); i++) {

            resp = resp + parts[i].length();

            resp = resp + length;

        }

        return resp;

    }

}

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

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 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_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 {