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.46</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.122</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.46</connection>

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

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

  </scm>

    <repositories>

        <repository>

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

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

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

            <releases>

                <enabled>true</enabled>

                <updatePolicy>daily</updatePolicy>

                <checksumPolicy>warn</checksumPolicy>

            </releases>

            <snapshots>

                <enabled>true</enabled>

                <updatePolicy>daily</updatePolicy>

                <checksumPolicy>warn</checksumPolicy>

            </snapshots>

        </repository>

    </repositories>

	<build>

		<plugins>

			<plugin>

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

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

				<configuration>

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

					<goals>deploy</goals>

				</configuration>

			</plugin>

		</plugins>

	</build>

  <dependencyManagement>

      <dependencies>

          <dependency>

            <groupId>org.gvsig</groupId>

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

            <version>2.0.46</version>

          </dependency>

          <dependency>

            <groupId>org.gvsig</groupId>

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

            <version>2.0.46</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 org.gvsig.fmap.dal.store.jdbc.JDBCConnectionParameters;

/**

 * @author jmvivo

 *

 */

public interface PostgreSQLConnectionParameters extends

		JDBCConnectionParameters {

	public static final String DYNFIELDNAME_USESSL = "UseSSL";

	public Boolean getUseSSL();

}

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

import org.gvsig.fmap.dal.DALLocator;

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

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

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

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

import org.gvsig.metadata.exceptions.MetadataException;

import org.gvsig.tools.library.AbstractLibrary;

import org.gvsig.tools.library.LibraryException;

public class PostgreSQLLibrary extends AbstractLibrary {

	static String DEFAULT_JDCB_DRIVER_NAME = "org.postgresql.Driver";

    @Override

    public void doRegistration() {

        registerAsServiceOf(DALLibrary.class);

        require(JDBCLibrary.class);

    }

	public static String getJdbcUrl(String host, Integer port, String db) {

		String url = null;

		if(host != null){

		    String sport = "";

		    if (port != null) {

		        sport = ":" + port;

		    }

		    url = "jdbc:postgresql://" + host + sport + "/" + db;

		}

		return url;

	}

	@Override

	protected void doInitialize() throws LibraryException {

	}

	@Override

	protected void doPostInitialize() throws LibraryException {

		LibraryException ex=null;

		new JDBCLibrary().postInitialize();

		 DBHelper.registerParametersDefinition(

				 PostgreSQLStoreParameters.PARAMETERS_DEFINITION_NAME,

				 PostgreSQLStoreParameters.class,

				 "PostgreSQLParameters.xml"

		);

		DBHelper.registerParametersDefinition(

				PostgreSQLNewStoreParameters.PARAMETERS_DEFINITION_NAME,

				PostgreSQLNewStoreParameters.class,

				"PostgreSQLParameters.xml"

		);

		DBHelper.registerParametersDefinition(

				PostgreSQLServerExplorerParameters.PARAMETERS_DEFINITION_NAME, 

				PostgreSQLServerExplorerParameters.class, 

				"PostgreSQLParameters.xml"

		);

		DBHelper.registerParametersDefinition(

				PostgreSQLResourceParameters.PARAMETERS_DEFINITION_NAME, 

				PostgreSQLResourceParameters.class, 

				"PostgreSQLParameters.xml"

		);

		try {

			DBHelper.registerMetadataDefinition(

					PostgreSQLStoreProvider.METADATA_DEFINITION_NAME, 

					PostgreSQLStoreProvider.class, 

					"PostgreSQLMetadata.xml"

			);

		} catch (MetadataException e) {

			ex = new LibraryException(this.getClass(),e);

		}

		ResourceManagerProviderServices resman = (ResourceManagerProviderServices) DALLocator

		.getResourceManager();

		if (!resman.getResourceProviders().contains(PostgreSQLResource.NAME)) {

			resman.register(PostgreSQLResource.NAME,

					PostgreSQLResource.DESCRIPTION, PostgreSQLResource.class,

					PostgreSQLResourceParameters.class);

		}

		DataManagerProviderServices dataman = (DataManagerProviderServices) DALLocator

				.getDataManager();

		if (!dataman.getStoreProviders().contains(PostgreSQLStoreProvider.NAME)) {

			dataman.registerStoreProvider(PostgreSQLStoreProvider.NAME,

					PostgreSQLStoreProvider.class,

					PostgreSQLStoreParameters.class);

		}

		if (!dataman.getExplorerProviders().contains(

				PostgreSQLStoreProvider.NAME)) {

			dataman.registerExplorerProvider(PostgreSQLServerExplorer.NAME,

					PostgreSQLServerExplorer.class,

					PostgreSQLServerExplorerParameters.class);

		}

		if( ex!=null ) {

			throw ex;

		}

	}

}

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