Application: gvSIG desktop » gvSIG data provider for PostgreSQL

org.gvsig.postgresql.dal.PostgreSQLLibrary

<?xml version="1.0"?>

<definitions>

  <version>1.0.0</version>

  <classes>

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

      <extends>

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

      </extends>

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

      <fields>

      </fields>

    </class>

  </classes>

</definitions>  

<?xml version="1.0"?>

<definitions>

  <version>1.0.0</version>

  <classes>

    <class name="PostgreSQLResourceParameters">

      <extends>

        <class>JDBC2ResourceParameters</class>

      </extends>

      <fields>

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

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

          <description>JDBC Driver class</description>

        </field>

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

          defaultValue="5432" group="Connection">

          <description></description>

        </field>

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

            defaultValue="127.0.0.1" group="Connection">

          <description></description>

        </field>

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

          defaultValue="false" group="Basic">

          <description>Use SSL connetion</description>

        </field>

      </fields>

    </class>

    <class name="PostgreSQLStoreParameters">

      <extends>

        <class>JDBC2StoreParameters</class>

        <class>PostgreSQLResourceParameters</class>

      </extends>

      <fields/>

    </class>

    <class name="PostgreSQLNewStoreParameters">

      <extends>

        <class>JDBC2NewStoreParameters</class>

        <class>PostgreSQLResourceParameters</class>

      </extends>

      <fields/>

    </class>

    <class name="PostgreSQLServerExplorerParameters">

      <extends>

        <class>PostgreSQLResourceParameters</class>

        <class>JDBC2ServerExplorerParameters</class>

      </extends>

      <fields/>

    </class>

  </classes>

</definitions>  

package org.gvsig.postgresql.dal;

import org.gvsig.fmap.dal.resource.db.DBParameters;

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

public interface PostgreSQLConnectionParameters extends JDBCConnectionParameters, DBParameters {

	public static final String USESSL_PARAMTER_NAME = "UseSSL";

	public boolean getUseSSL();    

}

package org.gvsig.postgresql.dal;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.fmap.dal.DataParameters;

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

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

public class PostgreSQLConnectionParametersHelper {

    private final JDBCConnectionParameters parameters;

    public PostgreSQLConnectionParametersHelper(JDBCConnectionParameters parameters) {

        this.parameters = parameters;

    }

    public String getUrl() {

        String url = (String) this.getDynValue(JDBCConnectionParameters.URL_PARAMTER_NAME);

        if( StringUtils.isEmpty(url) ) {

            url = PostgreSQLHelper.getConnectionURL((PostgreSQLConnectionParameters) this.parameters);

            this.setDynValue(JDBCConnectionParameters.URL_PARAMTER_NAME,url);

        }

        return url;

    }

    public void validate() throws ValidateDataParametersException {

        if (this.getDynValue(JDBCConnectionParameters.JDBC_DRIVER_CLASS_PARAMTER_NAME) == null) {

            this.setDynValue(

                JDBCConnectionParameters.JDBC_DRIVER_CLASS_PARAMTER_NAME,

                PostgreSQLHelper.POSTGRESQL_JDBC_DRIVER

            );

        }

        if( this.getDynValue(JDBCConnectionParameters.PORT_PARAMTER_NAME)==null ) {

            this.setDynValue(JDBCConnectionParameters.PORT_PARAMTER_NAME, 5432);

        }

		if ( StringUtils.isEmpty((CharSequence) this.getDynValue(JDBCConnectionParameters.URL_PARAMTER_NAME))) {

            String url = PostgreSQLHelper.getConnectionURL(

                parameters.getHost(),

                parameters.getPort(),

                parameters.getDBName()

            );

            this.setDynValue(JDBCConnectionParameters.URL_PARAMTER_NAME, url);

		}

    }

    private Object getDynValue(String name) {

        return ((DataParameters)this.parameters).getDynValue(name);

    }

    private void setDynValue(String name, Object value) {

        ((DataParameters)this.parameters).setDynValue(name,value);

    }

    public boolean getUseSSL() {

        return (boolean) this.getDynValue(PostgreSQLConnectionParameters.USESSL_PARAMTER_NAME);

    }

    public void setUseSSL(boolean v) {

        this.setDynValue(PostgreSQLConnectionParameters.USESSL_PARAMTER_NAME, v);

    }

}

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

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

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

public class PostgreSQLExplorerParameters extends

        JDBCServerExplorerParameters 

    implements

        PostgreSQLConnectionParameters

    {

    private final PostgreSQLConnectionParametersHelper helper;

    public PostgreSQLExplorerParameters() {

        super(

                PostgreSQLLibrary.NAME + "ServerExplorerParameters",

                PostgreSQLLibrary.NAME

        );

        this.helper = new PostgreSQLConnectionParametersHelper(this);

    }

    @Override

    public String getUrl() {

        return this.helper.getUrl();

    }

    @Override

    public void validate() throws ValidateDataParametersException {

        this.helper.validate();

        super.validate();

    }

    @Override

    public boolean getUseSSL() {

        return this.helper.getUseSSL();

    }

    public void setUseSSL(boolean v) {

        this.helper.setUseSSL(v);

    }

}

package org.gvsig.postgresql.dal;

import org.gvsig.fmap.dal.DALLibrary;

import org.gvsig.fmap.dal.DALLocator;

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCStoreProviderBase;

import org.gvsig.metadata.exceptions.MetadataException;

import org.gvsig.tools.library.AbstractLibrary;

import org.gvsig.tools.library.LibraryException;

public class PostgreSQLLibrary extends AbstractLibrary {

    public static final String NAME = "PostgreSQL";

    @Override

    public void doRegistration() {

        registerAsServiceOf(DALLibrary.class);

        require(JDBCLibrary.class);

    }

    @Override

    protected void doInitialize() throws LibraryException {

    }

    @Override

    protected void doPostInitialize() throws LibraryException {

        LibraryException ex = null;

        DataManagerProviderServices dataman = 

                (DataManagerProviderServices) DALLocator.getDataManager();

        try {

            Class.forName(PostgreSQLHelper.POSTGRESQL_JDBC_DRIVER);

        } catch(Throwable th) {

            PostgreSQLHelper.logger.warn("Can't load PostgreSQL JDBC Driver.",th);

        }

        DBHelper.registerParametersDefinition(

                NAME + "StoreParameters",

                PostgreSQLStoreParameters.class,

                dataman.getResourceAsStream(this, NAME + "Parameters.xml")

        );

        DBHelper.registerParametersDefinition(

                NAME + "NewStoreParameters",

                PostgreSQLNewStoreParameters.class,

                dataman.getResourceAsStream(this, NAME + "Parameters.xml")

        );

        DBHelper.registerParametersDefinition(

                NAME + "ServerExplorerParameters",

                PostgreSQLExplorerParameters.class,

                dataman.getResourceAsStream(this, NAME + "Parameters.xml")

        );

//        DBHelper.registerParametersDefinition(

//                NAME + "ResourceParameters",

//                PostgreSQLResourceParameters.class,

//                dataman.getResourceAsStream(this, NAME + "Parameters.xml")

//        );

        try {

            DBHelper.registerMetadataDefinition(

                NAME,

                JDBCStoreProviderBase.class,

                dataman.getResourceAsStream(this, NAME + "Metadata.xml")

            );

        } catch (MetadataException e) {

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

        }

//        ResourceManagerProviderServices resman = (ResourceManagerProviderServices) DALLocator

//                .getResourceManager();

//

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

//            resman.register(NAME,

//                "Resource for " + NAME,

//                PostgreSQLResource.class,

//                PostgreSQLResourceParameters.class

//            );

//        }

        if (!dataman.getStoreProviderRegister().exits(NAME)) {

            dataman.registerStoreProviderFactory(new PostgreSQLStoreProviderFactory());

        }

        if (!dataman.getServerExplorerRegister().exits(NAME)) {

            dataman.registerServerExplorerFactory(new PostgreSQLExplorerFactory());

        }

        if (ex != null) {

            throw ex;

        }

    }

}

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2013 gvSIG Association.

 *

 * 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 3

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

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

package org.gvsig.postgresql.dal;

import java.text.MessageFormat;

import java.util.ArrayList;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

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

import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase;

public class PostgreSQLBuilder extends JDBCSQLBuilderBase {

    private final JDBCHelper helper;

    public PostgreSQLBuilder(JDBCHelper helper) {

        super();

        this.helper = helper;

        config.set(SQLConfig.default_schema, "public");

        config.set(SQLConfig.allowAutomaticValues, true);

        config.set(SQLConfig.geometry_type_support, this.helper.getGeometrySupportType());

        config.set(SQLConfig.has_spatial_functions, this.helper.hasSpatialFunctions());

        config.set(SQLConfig.constant_true, "true");

        config.set(SQLConfig.constant_false, "false");

        config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table);

        config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_table);

        config.set(SQLConfig.UPDATE_TABLE_STATISTICS_table,"ANALYZE {0}");

        config.set(SQLConfig.ST_GeomFromEWKB, "ST_GeomFromEWKB({0}, {1})");

        config.set(SQLConfig.ST_AsEWKB, "ST_AsEWKB(({0}))");        

        config.set(SQLConfig.ST_ExtentAggregate, "ST_Extent({0})");        

        config.set(SQLConfig.ST_UnionAggregate, "ST_Union({0})");

        config.set(SQLConfig.lcase, "lower({0})");

        config.set(SQLConfig.ucase, "upper({0})");

        config.set(SQLConfig.operator_ILIKE, "({0}) ILIKE ({1})");

        config.set(SQLConfig.isNull, "( ({0}) ISNULL )");

        config.set(SQLConfig.notIsNull, "( ({0}) NOT NULL )");

    }

    public class PostgreSQLUpdateTableStatisticsBuilderBase extends UpdateTableStatisticsBuilderBase {

        @Override

        public List<String> toStrings() {

            List<String> sqls = new ArrayList<>();

            if( config.has_functionality(SQLConfig.UPDATE_TABLE_STATISTICS_table) ) {

                // In postGIS, UpdateLayerStatistics function, don't allow to 

                // use the database name in the table name.

                String name = identifier(this.table.getName());

                if( table.has_schema()) {

                    name = identifier(this.table.getSchema()) + "." + name;

                }

                String sql = MessageFormat.format(

                        config.getString(SQLConfig.UPDATE_TABLE_STATISTICS_table),

                        name

                    );

                if( !StringUtils.isEmpty(sql) ) {

                    sqls.add(sql);

                }

            }

            return sqls;

        }        

    }

    protected class PostgreSQLCreateTableBuilder extends CreateTableBuilderBase {

        @Override

       public List<String> toStrings() {

           // 

           // https://www.postgresql.org/docs/9.1/static/sql-createtable.html

           //

            List<String> sqls = new ArrayList<>();

            StringBuilder builder = new StringBuilder();

            builder.append("CREATE TABLE ");

            builder.append(this.table.toString());

            builder.append(" (");

            boolean first = true;

            for (ColumnDescriptorBuilder column : columns) {

                if( column.isGeometry() ) {

                    continue;

                }

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

                builder.append(identifier(column.getName()));

                builder.append(" ");

                if( column.isAutomatic() ) {

                    builder.append(" SERIAL");

                } else {

                    builder.append(sqltype(column.getType(), column.getPrecision(), column.getSize()));

                    if (column.getDefaultValue() == null) {

                        if (column.allowNulls()) {

                            builder.append(" DEFAULT NULL");

                        }

                    } else {

                        builder.append(" DEFAULT '");

                        builder.append(column.getDefaultValue().toString());

                        builder.append("'");

                    }

                    if (column.allowNulls()) {

                        builder.append(" NULL");

                    } else {

                        builder.append(" NOT NULL");

                    }

                }

                if (column.isPrimaryKey()) {

                    builder.append(" PRIMARY KEY");

                }

            }

            builder.append(" )");

            sqls.add(builder.toString());

            String AddGeometryColumn = "SELECT AddGeometryColumn({0} , {1} , {2}, {3,number,#######} , {4} , {5}, {6})";

            for (ColumnDescriptorBuilderBase column : columns) {

                if( column.isGeometry() ) {

                    String sql = MessageFormat.format(

                        AddGeometryColumn,

                        constant(this.table.getSchema()),

                        constant(this.table.getName()),

                        constant(column.getName()),

                        column.getGeometrySRSId(),

                        constant(sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype())),

                        constant(sqlgeometrydimension(column.getGeometryType(), column.getGeometrySubtype())),

                        constant(column.allowNulls())

                    );

                    sqls.add(sql);

                }

            }

            for (ColumnDescriptorBuilderBase column : columns) {

                if( column.isIndexed() ) {

                    String sql;

                    String name = "idx_" + this.table().getName() + column.getName();

                    if( column.isGeometry() ) {

                        sql = MessageFormat.format(

                            config.getString(SQLConfig.CREATE_INDEX_name_ON_table_USING_GIST_column),

                            name,

                            this.table().toString(),

                            column.getName()

                        );

                    } else {

                        sql = MessageFormat.format(

                            config.getString(SQLConfig.CREATE_INDEX_name_ON_table_column),

                            name,

                            this.table().toString(),

                            column.getName()

                        );

                    }

                    sqls.add(sql);

                }

            }                        

            return sqls;

        }

    }

    public class PostgreSQLSelectBuilderBase extends SelectBuilderBase {

        @Override

        protected boolean isValid(StringBuilder message) {

            if( message == null ) {

                message = new StringBuilder();

            }

            if( this.has_offset() && !this.has_order_by() ) {

                // Algunos gestores de BBDD requieren que se especifique un

                // orden para poder usar OFFSET. Como eso parece buena idea para

                // asegurar que siempre tengamos los mismo resultados, lo exijimos

                // siempre.

                message.append("Can't use OFFSET without an ORDER BY.");

                return false;

            }

            return true;

        }        

        @Override

        public String toString() {

            //

            // https://www.postgresql.org/docs/9.1/static/sql-select.html

            //

            StringBuilder builder = new StringBuilder();

            if( !isValid(builder) ) {

                throw new IllegalStateException(builder.toString());

            }

            builder.append("SELECT ");

            if( this.distinct ) {

                builder.append("DISTINCT ");

            }

            boolean first = true;

            for (SelectColumnBuilder column : columns) {

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

                builder.append(column.toString());

            }

            if ( this.has_from() ) {

                builder.append(" FROM ");

                builder.append(this.from.toString());

            }

            if ( this.has_where() ) {

                builder.append(" WHERE ");

                builder.append(this.where.toString());

            }

            if( this.has_order_by() ) {

                builder.append(" ORDER BY ");

                first = true;

                for (OrderByBuilder item : this.order_by) {

                    if (first) {

                        first = false;

                    } else {

                        builder.append(", ");

                    }

                    builder.append(item.toString());                    

                }   

            }

            if ( this.has_limit() && this.has_offset() ) {

                builder.append(" OFFSET ");

                builder.append(this.offset);

                builder.append(" FETCH NEXT ");

                builder.append(this.limit);

                builder.append(" ROWS ONLY");

            } else if ( this.has_limit()) {

                builder.append(" LIMIT ");

                builder.append(this.limit);

            } else if ( this.has_offset() ) {

                builder.append(" LIMIT ALL OFFSET ");

                builder.append(this.offset);    

            }

            return builder.toString();

        }

    }

    @Override

    public String bytearray(byte[] data) {

        return "decode('"+bytearray_hex(data)+"','hex')";

    }

    @Override

    protected CreateTableBuilder createCreateTableBuilder() {

        return new PostgreSQLCreateTableBuilder();

    }

    @Override

    protected SelectBuilder createSelectBuilder() {

        return new PostgreSQLSelectBuilderBase();

    }

    @Override

    protected UpdateTableStatisticsBuilder createUpdateTableStatisticsBuilder() {

        return new PostgreSQLUpdateTableStatisticsBuilderBase();

    }    

}

package org.gvsig.postgresql.dal;

import org.gvsig.fmap.dal.DataServerExplorerParameters;

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

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

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.impl.JDBCServerExplorerFactory;

public class PostgreSQLExplorerFactory extends JDBCServerExplorerFactory {

    private static final String NAME = PostgreSQLLibrary.NAME;

    public PostgreSQLExplorerFactory() {

        super(

                NAME,

                "PostgreSQL Server"

        );

    }

    @Override

    public JDBCServerExplorer create(

            DataServerExplorerParameters parameters, 

            DataServerExplorerProviderServices providerServices

        ) throws InitializeException {

        JDBCHelper helper = new PostgreSQLHelper((JDBCConnectionParameters) parameters);

        JDBCServerExplorer server = helper.createServerExplorer(

                (JDBCServerExplorerParameters) parameters, 

                providerServices

        );

        return server;

    }

    @Override

    public JDBCServerExplorerParameters createParameters() {

        JDBCServerExplorerParameters params = new PostgreSQLExplorerParameters();

        return params;    

    }

}

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

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

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

public class PostgreSQLNewStoreParameters 

    extends 

        JDBCNewStoreParameters 

    implements 

        PostgreSQLConnectionParameters 

    {

    private final PostgreSQLConnectionParametersHelper helper;

    public PostgreSQLNewStoreParameters() {

        super(

            PostgreSQLLibrary.NAME + "NewStoreParameters", 

            PostgreSQLLibrary.NAME

        );

        this.helper = new PostgreSQLConnectionParametersHelper(this);

    }

    @Override

    public String getUrl() {

        return this.helper.getUrl();

    }

    @Override

    public void validate() throws ValidateDataParametersException {

        this.helper.validate();

    }

    @Override

    public boolean getUseSSL() {

        return this.helper.getUseSSL();

    }

    public void setUseSSL(boolean v) {

        this.helper.setUseSSL(v);

    }

}

package org.gvsig.postgresql.dal;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import org.apache.commons.dbcp.BasicDataSource;

import org.apache.commons.lang3.BooleanUtils;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.fmap.dal.DataTypes;

import org.gvsig.fmap.dal.ExpressionBuilder;

import org.gvsig.fmap.dal.SQLBuilder;

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

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

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

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

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

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils;

import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory;

import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCHelperBase;

import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase;

import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSRSsBase;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.aggregate.MultiLine;

import org.gvsig.fmap.geom.aggregate.MultiPoint;

import org.gvsig.fmap.geom.aggregate.MultiPolygon;

import org.gvsig.fmap.geom.exception.CreateGeometryException;

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

import org.gvsig.fmap.geom.type.GeometryType;

import org.gvsig.postgresql.dal.operations.PostgreSQLOperationsFactory;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class PostgreSQLHelper extends JDBCHelperBase {

    static final Logger logger = LoggerFactory.getLogger(PostgreSQLHelper.class);

    public static final String POSTGRESQL_JDBC_DRIVER = "org.postgresql.Driver";

    public static String getConnectionURL(PostgreSQLConnectionParameters params) {

        return getConnectionURL(

            params.getHost(),

            params.getPort(),

            params.getDBName()

        );

    }

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

        if( StringUtils.isEmpty(host) ) {

            throw new IllegalArgumentException("Parameter 'host' can't be null.");

        }

        String connectionURL = "jdbc:postgresql://" + host;

        if (port != null) {

            connectionURL = connectionURL + ":" + port;

        }

        connectionURL = connectionURL + "/" + db;

        logger.debug("connectionURL: {}", connectionURL);

        return connectionURL;

    }

    private static class ConnectionProvider {

        private static boolean needRegisterDriver = true;

        private BasicDataSource dataSource = null;

        private final PostgreSQLConnectionParameters connectionParameters;

        public ConnectionProvider(PostgreSQLConnectionParameters connectionParameters) {

            this.connectionParameters = connectionParameters;

        }

        public Connection getConnection() throws SQLException {

            if (this.dataSource == null) {

                this.dataSource = this.createDataSource();               

            }

            Connection conn = this.dataSource.getConnection();

            return conn;

        }

        private BasicDataSource createDataSource() throws SQLException {

            if (!this.isRegistered()) {

                this.registerDriver();

            }

            PostgreSQLConnectionParameters params = connectionParameters;

            BasicDataSource ds = new BasicDataSource();

            ds.setDriverClassName(params.getJDBCDriverClassName());

            if( params.getUseSSL() ) {

                String s = BooleanUtils.toStringTrueFalse(params.getUseSSL());

                ds.addConnectionProperty("ssl", s );

            }

            if( !StringUtils.isEmpty(params.getUser()) ) {

                ds.setUsername(params.getUser());

            }

            if( !StringUtils.isEmpty(params.getPassword()) ) {

                ds.setPassword(params.getPassword());

            }

            ds.setUrl(params.getUrl());

            ds.setMaxWait(60L * 1000);

            return ds;

        }

        private boolean isRegistered() {

            return needRegisterDriver;

        }

        public void registerDriver() throws SQLException {

            String className = this.connectionParameters.getJDBCDriverClassName();

            if (className == null) {

                return;

            }

            try {

                Class theClass = Class.forName(className);

                if (theClass == null) {

                    throw new JDBCDriverClassNotFoundException(PostgreSQLLibrary.NAME, className);

                }

            } catch (Exception e) {

                throw new SQLException("Can't register JDBC driver '" + className + "'.", e);

            }

            needRegisterDriver = false;

        }

    }

    private ConnectionProvider connectionProvider = null;

    public PostgreSQLHelper(JDBCConnectionParameters connectionParameters) {

        super(connectionParameters);

        this.srss = new JDBCSRSsBase(this);

    }

    @Override

    public Connection getConnection() throws AccessResourceException {

        try {

            if (this.connectionProvider == null) {

                this.connectionProvider = new ConnectionProvider(this.getConnectionParameters());

            }

            return this.connectionProvider.getConnection();

        } catch (SQLException ex) {

            throw new AccessResourceException(PostgreSQLLibrary.NAME, ex);

        }

    }

    @Override

    public PostgreSQLConnectionParameters getConnectionParameters() {

        return (PostgreSQLConnectionParameters) super.getConnectionParameters();

    }

    @Override

    public String getConnectionURL() {

        return getConnectionURL(this.getConnectionParameters());

    }

    @Override

    protected String getResourceType() {

        return PostgreSQLLibrary.NAME;

    }

    @Override

    public String getProviderName() {

        return PostgreSQLLibrary.NAME;

    }

    @Override

    public JDBCSQLBuilderBase createSQLBuilder() {

        return new PostgreSQLBuilder(this);

    }

    @Override

    public OperationsFactory getOperations() {

        if (this.operationsFactory == null) {

            this.operationsFactory = new PostgreSQLOperationsFactory(this);

        }

        return operationsFactory;

    }

    @Override

    public SQLBuilder.GeometrySupportType getGeometrySupportType() {

        return SQLBuilder.GeometrySupportType.WKB;

    }

    @Override

    public boolean hasSpatialFunctions() {

        return true;

    }

    @Override

    public boolean canWriteGeometry(int geometryType, int geometrySubtype) {

        return true;

    }

    @Override

    public String getQuoteForIdentifiers() {

        return "\"";

    }

    @Override

    public boolean allowAutomaticValues() {

        return true;

    }

    @Override

    public boolean supportOffsetInSelect() {

        return true;

    }

    @Override

    public String getQuoteForStrings() {

        return "'";

    }

    @Override

    public String getSourceId(JDBCStoreParameters parameters) {

        return parameters.getDBName() + "." + 

               parameters.getSchema()+ "." + 

               parameters.getTable();

    }

    @Override

    public JDBCNewStoreParameters createNewStoreParameters() {

        return new PostgreSQLNewStoreParameters();

    }

    @Override

    public JDBCStoreParameters createOpenStoreParameters() {

        return new PostgreSQLStoreParameters();

    }

    @Override

    public JDBCServerExplorerParameters createServerExplorerParameters() {

        return new PostgreSQLExplorerParameters();

    }

    public void setPreparedStatementParameters(PreparedStatement st, JDBCSQLBuilderBase sqlbuilder, FeatureType type, FeatureProvider feature) {

        try {

            List<Object> values = new ArrayList<>();

            for (ExpressionBuilder.Parameter parameter : sqlbuilder.getParameters()) {

                Object value;

                if (parameter.is_constant()) {

                    value = parameter.getValue();

                } else {

                    String name = parameter.getName();

                    value = feature.get(name);

                    FeatureAttributeDescriptor attr = type.getAttributeDescriptor(name);

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

                        value = forceGeometry(attr.getGeomType(), (Geometry) value);

                    }

                }

                values.add(value);

            }

            JDBCUtils.setObjects(st, values, sqlbuilder.geometry_support_type());

        } catch (Exception ex) {

            String f = "unknow";

            try {

                f = feature.toString();

            } catch (Exception ex2) {

                // Do nothing

            }

            throw new RuntimeException("Can't set parameters to prepared statement from the feature (" + f + ")", ex);

        }

    }

    private Geometry forceGeometry(GeometryType geomtype, Geometry geom) throws CreateGeometryException {

        switch( geomtype.getType() ) {

        case Geometry.TYPES.MULTIPOLYGON:

            if( geom.getType()==Geometry.TYPES.POLYGON ) {

                MultiPolygon x = getGeometryManager().createMultiPolygon(geomtype.getSubType());

                x.addPrimitive((Primitive) geom);

                geom = x;

            }

            break;

        case Geometry.TYPES.MULTILINE:

            if( geom.getType()==Geometry.TYPES.LINE ) {

                MultiLine x = getGeometryManager().createMultiLine(geomtype.getSubType());

                x.addPrimitive((Primitive) geom);

                geom = x;

            }

            break;

        case Geometry.TYPES.MULTIPOINT:

            if( geom.getType()==Geometry.TYPES.POINT ) {

                MultiLine x = getGeometryManager().createMultiLine(geomtype.getSubType());

                x.addPrimitive((Primitive) geom);

                geom = x;

            }

            break;

        case Geometry.TYPES.POLYGON:

            if( geom.getType()==Geometry.TYPES.MULTIPOLYGON ) {

                MultiPolygon x = (MultiPolygon) geom;

                if( x.getPrimitivesNumber()==1 ) {

                    geom = x.getPrimitiveAt(0);

                }

            }

            break;

        case Geometry.TYPES.LINE:

            if( geom.getType()==Geometry.TYPES.MULTILINE ) {

                MultiLine x = (MultiLine) geom;

                if( x.getPrimitivesNumber()==1 ) {

                    geom = x.getPrimitiveAt(0);

                }

            }

            break;

        case Geometry.TYPES.POINT:

            if( geom.getType()==Geometry.TYPES.MULTIPOINT ) {

                MultiPoint x = (MultiPoint) geom;

                if( x.getPrimitivesNumber()==1 ) {

                    geom = x.getPrimitiveAt(0);

                }

            }

        }

        return geom;

    }

}

package org.gvsig.postgresql.dal;

import org.gvsig.fmap.dal.DataParameters;

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

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

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.impl.JDBCStoreProviderFactory;

public class PostgreSQLStoreProviderFactory extends JDBCStoreProviderFactory {

    public PostgreSQLStoreProviderFactory() {

        super(

                PostgreSQLLibrary.NAME, 

                "PostgreSQL store"

        );

    }

    @Override

    public JDBCStoreProvider createProvider(

            DataParameters parameters,

            DataStoreProviderServices providerServices

    ) throws InitializeException {

        JDBCHelper helper = new PostgreSQLHelper((JDBCConnectionParameters) parameters);

        JDBCStoreProvider provider = helper.createProvider(

                (JDBCStoreParameters) parameters, 

                providerServices

        );

        return provider;

    }

    @Override

    public JDBCStoreParameters createParameters() {

        JDBCStoreParameters params = new PostgreSQLStoreParameters();

        return params;

    }

}

package org.gvsig.postgresql.dal.operations;

import java.util.Iterator;

import java.util.List;

import org.cresques.cts.IProjection;

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.AppendOperation;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.FetchFeatureTypeOperation;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.OperationsFactoryBase;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.PerformChangesOperation;

public class PostgreSQLOperationsFactory extends OperationsFactoryBase {

    public PostgreSQLOperationsFactory(JDBCHelper helper) {

        super(helper);

    }

    @Override

    public FetchFeatureTypeOperation createFetchFeatureType(EditableFeatureType type, String database, String schema, String table, List<String> primaryKeys, String defaultGeometryField, IProjection crs) {

        return new PostgreSQLFetchFeatureTypeOperation(

                helper, type, database, schema, table, primaryKeys, 

                defaultGeometryField, crs

        );

    }  

    @Override

    public PerformChangesOperation createPerformChanges(String database, String schema, String table, FeatureType type, Iterator deleteds, Iterator inserteds, Iterator updateds, Iterator featureTypesChanged) {

        return new PostgreSQLPerformChangesOperation(

            helper, 

            database, schema, table, type, 

            deleteds, inserteds, updateds, 

            featureTypesChanged

        ); 

    }

    @Override

    public AppendOperation createAppend(String database, String schema, String table, FeatureType type) {

        return new PostgreSQLAppendOperation(helper, database, schema, table, type); 

    }

}

package org.gvsig.postgresql.dal.operations;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Iterator;

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

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

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

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

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

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

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase;

import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.PerformChangesOperation;

import org.gvsig.postgresql.dal.PostgreSQLHelper;

public class PostgreSQLPerformChangesOperation extends PerformChangesOperation {

    public PostgreSQLPerformChangesOperation(JDBCHelper helper) {

        this(helper, null, null, null, null, null, null, null, null);

    }

    public PostgreSQLPerformChangesOperation(JDBCHelper helper,

            String dbName,

            String schemaName,

            String tableName,

            FeatureType featureType,

            Iterator<FeatureReferenceProviderServices> deleteds,

            Iterator<FeatureProvider> inserteds,

            Iterator<FeatureProvider> updateds,

            Iterator<FeatureStoreProvider.FeatureTypeChanged> featureTypesChanged) {

        super(helper);

        this.dbName = dbName;

        this.deleteds = deleteds;

        this.inserteds = inserteds;

        this.updateds = updateds;

        this.schemaName = schemaName;

        this.tableName = tableName;

        this.featureType = featureType;

        this.featureTypesChanged = featureTypesChanged;

    }

    private PostgreSQLHelper getHelper() {

        return (PostgreSQLHelper) this.helper;

    }

    @Override

    public void performInserts(Connection conn,

            String database,

            String schema,

            String table,

            FeatureType type,

            Iterator<FeatureProvider> inserteds) throws DataException {

        JDBCSQLBuilderBase sqlbuilder = buildInsertSQL(database, schema, table, type);

        PreparedStatement st;

        String sql = sqlbuilder.insert().toString();

        try {

            st = conn.prepareStatement(sql);

            while (inserteds.hasNext()) {

                FeatureProvider feature = inserteds.next();

                getHelper().setPreparedStatementParameters(st, sqlbuilder, type, feature);

                if (JDBCUtils.executeUpdate(st,sql) == 0) {

                    throw new JDBCExecuteSQLException(

                            sqlbuilder.insert().toString(),

                            null

                    );

                }

            }

        } catch (JDBCExecuteSQLException ex) {

            throw ex;

        } catch (Exception ex) {

            throw new JDBCExecuteSQLException(sql,ex);

        }

    }

    @Override

    public void performUpdates(Connection conn,

            String database,

            String schema,

            String table,

            FeatureType type,

            Iterator<FeatureProvider> updateds) throws DataException {

        JDBCSQLBuilderBase sqlbuilder = buildUpdateSQL(database, schema, table, type);

        PreparedStatement st = null;

        String sql = sqlbuilder.update().toString();

        try {

            st = conn.prepareStatement(sql);

            while (updateds.hasNext()) {

                FeatureProvider feature = (FeatureProvider) updateds.next();

                getHelper().setPreparedStatementParameters(st, sqlbuilder, type, feature);

                if (JDBCUtils.executeUpdate(st,sql) == 0) {

                    throw new JDBCUpdateWithoutChangesException(sql,null);

                }

            }

        } catch (SQLException e) {

            throw new JDBCSQLException(e);

        } finally {

            JDBCUtils.closeQuietly(st);

        }

    }

    @Override

    public void performUpdateTable(Connection conn,

            String database,

            String schema,

            String table,

            FeatureType original,

            FeatureType target) throws DataException {

        SQLBuilderBase sqlbuilder = buildUpdateTableSQL(database, schema, table, original, target);

        Statement st = null;

        try {

            st = conn.createStatement();

            for (String sql : sqlbuilder.alter_table().toStrings()) {

                JDBCUtils.execute(st, sql);

            }

        } catch (SQLException e) {

            throw new JDBCSQLException(e);

        } finally {

            JDBCUtils.closeQuietly(st);

        }

    }

}

package org.gvsig.postgresql.dal.operations;

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.AppendOperation;

import org.gvsig.postgresql.dal.PostgreSQLHelper;

public class PostgreSQLAppendOperation extends AppendOperation {

    public PostgreSQLAppendOperation(

            JDBCHelper helper, 

            String database,

            String schema, 

            String table, 

            FeatureType type

        ) {

        super(helper, database, schema, table, type);

    }

    private PostgreSQLHelper getHelper() {

        return (PostgreSQLHelper) this.helper;

    }

    @Override

    public void append(FeatureProvider feature) throws DataException {

        int n;

        try {

            getHelper().setPreparedStatementParameters(preparedStatement, sqlbuilder, type, feature);

            n = JDBCUtils.executeUpdate(this.preparedStatement,this.sql);

        } catch(Exception ex) {

            throw new RuntimeException("Can't insert feature.", ex);

        }

        if( n<1 ) {

            throw new RuntimeException("Can't insert feature (n="+n+").");

        }

    }

}

package org.gvsig.postgresql.dal.operations;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import org.cresques.cts.IProjection;

import org.gvsig.fmap.dal.DataTypes;

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils;

import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase;

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.FetchFeatureTypeOperation;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryLocator;

import org.gvsig.fmap.geom.GeometryManager;

import org.gvsig.fmap.geom.type.GeometryType;

public class PostgreSQLFetchFeatureTypeOperation extends FetchFeatureTypeOperation {

    private static Map<String,GeometryType>databaseGeometryTypes = null;

    public PostgreSQLFetchFeatureTypeOperation(

            JDBCHelper helper

        ) {

        super(helper);

    }

    private GeometryType getGT(

            GeometryManager manager, 

            int type, 

            int subtype

        ) {

        try {

            return manager.getGeometryType(type, subtype);

        } catch (Exception ex) {

            return null;

        }

    }

    public PostgreSQLFetchFeatureTypeOperation(

            JDBCHelper helper,

            EditableFeatureType featureType,

            String dbname,

            String schema,

            String table,

            List<String> primaryKeys,

            String defaultGeometryColumn,

            IProjection crs

        ) {

        super(helper, featureType, dbname, schema, table, primaryKeys, defaultGeometryColumn, crs);

    }            

    @Override

    public void fetch(EditableFeatureType featureType, Connection conn, String dbname, String schema, String table, List<String> pks, String defaultGeometryColumn, IProjection crs) throws DataException {

        super.fetch(featureType, conn, dbname, schema, table, pks, defaultGeometryColumn, crs);

    }

    @Override

    protected int getDataTypeFromMetadata(

            ResultSetMetaData rsMetadata,

            int colIndex

        ) throws SQLException {

        return super.getDataTypeFromMetadata(rsMetadata, colIndex);

    }

    @Override

    protected void fetchGeometryTypeAndSRS(

            EditableFeatureAttributeDescriptor attr,

            ResultSetMetaData rsMetadata,

            int colIndex

        ) {

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

            return;

        }

        try {

            JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

            sqlbuilder.select().column().name("f_table_catalog");

            sqlbuilder.select().column().name("f_table_schema");

            sqlbuilder.select().column().name("f_table_name");

            sqlbuilder.select().column().name("f_geometry_column");

            sqlbuilder.select().column().name("coord_dimension");

            sqlbuilder.select().column().name("srid");

            sqlbuilder.select().column().name("type");

            sqlbuilder.select().where().set(

                    sqlbuilder.eq(

                            sqlbuilder.column("f_table_name"),

                            sqlbuilder.constant(this.getTablename())

                    )

            );                

            sqlbuilder.select().where().and(

                    sqlbuilder.eq(

                            sqlbuilder.column("f_geometry_column"),

                            sqlbuilder.constant(attr.getName())

                    )

            );         

            sqlbuilder.select().from().table().name("geometry_columns");

            Statement st = null;

            ResultSet rs = null;

            String srsid = null;

            String geometryTypeName = null;

            try {

                st = this.getConnection().createStatement();

                rs = JDBCUtils.executeQuery(st, sqlbuilder.toString());

                if (rs.next()) {

                    srsid = rs.getString("srid");

                    geometryTypeName = rs.getString("type");

                }

            } finally {

                JDBCUtils.closeQuietly(rs);

                JDBCUtils.closeQuietly(st);

            }

            if( !StringUtils.isEmpty(geometryTypeName) ) {

                GeometryType gt = getGeometryTypeFromDatabaseTypeName(geometryTypeName);

                attr.setGeometryType(gt);

            }

            if( !StringUtils.isEmpty(srsid) ) {

                attr.setSRS(this.helper.getProjectionFromDatabaseCode(srsid));

            }