Application: gvSIG desktop » gvSIG data provider for PostgreSQL

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

    }

}