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.19</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.24</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.19</connection>

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

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

  </scm>

	<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.19</version>

          </dependency>

          <dependency>

            <groupId>org.gvsig</groupId>

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

            <version>2.0.19</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>

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

import java.util.List;

import org.cresques.cts.IProjection;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

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

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

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

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

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

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

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

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

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

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

import org.gvsig.tools.evaluator.Evaluator;

import org.gvsig.tools.evaluator.EvaluatorFieldValue;

import org.gvsig.tools.evaluator.EvaluatorFieldsInfo;

/**

 * @author jmvivo

 *

 */

public class PostgreSQLSetProvider extends JDBCSetProvider {

    private static Logger logger = LoggerFactory.getLogger(PostgreSQLSetProvider.class);

	public PostgreSQLSetProvider(JDBCStoreProvider store, FeatureQuery query,

			FeatureType featureType) throws DataException {

		super(store, query, featureType);

	}

	/*

	 * (non-Javadoc)

	 *

	 * @see org.gvsig.fmap.dal.feature.spi.FeatureSetProvider#canFilter()

	 */

	public boolean canFilter() {

		// TODO more checks

		if (!super.canFilter()) {

			return false;

		}

		return true;

	}

    protected String getSqlForEvaluator(Evaluator filter) {

        String resp = null;

        if (filter != null && filter.getSQL() != null) {

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

            EvaluatorFieldsInfo info = filter.getFieldsInfo();

            String filterString = filter.getSQL();

            String[] filterNames = null;

            String[] finalNames = null;

            if (info == null) {

                filterNames = getFieldNames(getFeatureType());

            } else {

                filterNames = info.getFieldNames();

            }

            finalNames = new String[filterNames.length];

            FeatureAttributeDescriptor attr;

            for (int i = 0; i < filterNames.length; i++) {

                attr = getFeatureType().getAttributeDescriptor(filterNames[i]);

                if (attr == null) {

                    finalNames[i] = filterNames[i];

                    continue;

                }

                finalNames[i] = getEscapedFieldName(attr.getName());

            }

            for (int i = 0; i < filterNames.length; i++) {

                if (!filterNames[i].equals(finalNames[i])) {

                    filterString = filterString.replaceAll(

                            "\\b" + filterNames[i] + "\\b",

                            finalNames[i]);

                }

            }

            resp = filterString;        

        }

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

        // In any case, append working area filter

        try {

            resp = appendWorkingAreaCondition(resp);

        } catch (Exception e) {

            logger.error("While appending working area condition.", e);

        }

        return resp;

    }	

    private String[] getFieldNames(FeatureType ft) {

        if (ft == null) {

            return new String[0];

        }

        FeatureAttributeDescriptor[] atts = ft.getAttributeDescriptors();

        String[] resp = new String[atts.length];

        for (int i=0; i<atts.length; i++) {

            resp[i] = atts[i].getName();

        }

        return resp;

    }

    private String getFunctionName(String newFunctionName) {

        PostgreSQLStoreProvider pg_sto_prov = (PostgreSQLStoreProvider) this.getStore();

        PostgreSQLHelper hpr = pg_sto_prov.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 appendWorkingAreaCondition(String sql) throws Exception {

        DBStoreParameters dbParams = 

        (DBStoreParameters) getStore().getParameters();

        Envelope wa = dbParams.getWorkingArea(); 

        if (wa == null) {

            return sql;

        } else {

            FeatureStore fstore = this.getStore().getFeatureStore();

            String geoname =

                fstore.getDefaultFeatureType().getDefaultGeometryAttributeName();

            StringBuffer strbuf = new StringBuffer();

            if (sql == null)  {

                strbuf.append(

                    getFunctionName("ST_Intersects") + "("

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

            } else {

                strbuf.append("(");

                strbuf.append(sql);

                strbuf.append(") AND "

                    + getFunctionName("ST_Intersects") + "("

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

            }

            String workAreaWkt = null;

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

            strbuf.append(workAreaWkt);

            strbuf.append("', ");

            PostgreSQLStoreProvider sprov = (PostgreSQLStoreProvider) getStore();

            PostgreSQLHelper helper = sprov.getPgHelper();

            IProjection proj = dbParams.getCRS();

            int sridInt = helper.getProviderSRID(proj); 

            if (sridInt == -1) {

                throw new CreateGeometryException(

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

            } else {

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

            }

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

            strbuf.append(helper.escapeFieldName(geoname));

            strbuf.append("))");

            return strbuf.toString();

        }

    }

}

/* 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.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() });

	}

	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() && dataSource instanceof BasicDataSource) {

			BasicDataSource ds = (BasicDataSource) dataSource;

			logger.debug(src + "  actives:" + ds.getNumActive() + "("

					+ ds.getMaxActive() + ") idle:" + ds.getNumIdle() + "("

					+ ds.getMaxIdle() + ")");

		}

	}

	protected synchronized Object getTheConnection() throws DataException {

		try {

			Object conn = this.dataSource.getConnection();

			logPoolStatus("getTheConnection");

			return conn;

		} catch (SQLException e) {

			throw new JDBCSQLException(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.List;

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

	}

	protected 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;

	}

	protected void checkIsMine(DataStoreParameters dsp) {

		if (!(dsp instanceof PostgreSQLStoreParameters)) {

			// FIXME Excpetion ???

			throw new IllegalArgumentException(

					"not instance of PostgreSQLStoreParameters");

		}

		super.checkIsMine(dsp);

		PostgreSQLStoreParameters pgp = (PostgreSQLStoreParameters) 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{

						st.execute(sqlDrop);

					} catch (SQLException e) {

						throw new JDBCExecuteSQLException(sqlDrop, e);

					}

					try {

						st.execute(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;

	}

}

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

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

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.resource.exception.AccessResourceException;

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.dal.store.jdbc.exception.JDBCExecuteSQLException;

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

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.

 *

 */

/*

* AUTHORS (In addition to CIT):

* 2008 IVER T.I. S.A.   {{Task}}

*/

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;

public class PostgreSQLNewStoreParameters extends PostgreSQLStoreParameters

		implements NewFeatureStoreParameters {

    public static final String PARAMETERS_DEFINITION_NAME = "PostgreSQLNewStoreParameters";

    public PostgreSQLNewStoreParameters() {

    	super(PARAMETERS_DEFINITION_NAME);

    }

	public EditableFeatureType getDefaultFeatureType() {

		return (EditableFeatureType) this.getDynValue(FEATURETYPE_PARAMTER_NAME);

	}

	public void setDefaultFeatureType(FeatureType featureType) {

		this.setDynValue(FEATURETYPE_PARAMTER_NAME, featureType);

	}

}

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

import java.io.InputStream;

import java.net.URL;

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

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

import org.gvsig.fmap.geom.GeometryManager;

import org.gvsig.fmap.geom.operation.GeometryOperationException;

import org.gvsig.fmap.geom.operation.GeometryOperationNotSupportedException;

import org.gvsig.fmap.geom.operation.fromwkb.FromWKB;

import org.gvsig.fmap.geom.operation.fromwkb.FromWKBGeometryOperationContext;

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 = st.executeQuery(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(