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.202</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.365</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.202</connection>

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

      <url>https://devel.gvsig.org/redmine/projects/gvsig-postgresql/repository/show/tags/org.gvsig.postgresql-2.0.202</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.202</version>

          </dependency>

          <dependency>

            <groupId>org.gvsig</groupId>

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

            <version>2.0.202</version>

          </dependency>

        <dependency>

            <groupId>org.postgresql</groupId>

            <artifactId>postgresql</artifactId>

            <version>42.3.1</version>

        </dependency>

        <dependency>

          <groupId>org.apache.commons</groupId>

          <artifactId>commons-dbcp2</artifactId>

          <version>2.7.0</version>

        </dependency>   

        <dependency>

            <groupId>org.apache.commons</groupId>

            <artifactId>commons-pool2</artifactId>

            <version>2.7.0</version>

        </dependency>

      </dependencies>

  </dependencyManagement>

  <modules>

    <module>org.gvsig.postgresql.app</module>

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

  </modules>

</project>

package org.gvsig.postgresql.dal;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import org.apache.commons.dbcp2.BasicDataSource;

import org.apache.commons.lang3.BooleanUtils;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.GeometrySupportType;

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

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

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

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

import org.gvsig.tools.dispose.Disposable;

import org.gvsig.tools.exception.BaseException;

import org.postgresql.util.DriverInfo;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

@SuppressWarnings("UseSpecificCatch")

public class PostgreSQLHelper extends JDBCHelperBase {

    /* friend */ static final Logger LOGGER = LoggerFactory.getLogger(PostgreSQLHelper.class);

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

    public static final boolean USE_CONNECTIONS_POOL = true;

    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 PostgreSQLConnectionProvider implements Disposable, ConnectionProvider {

        private static boolean needRegisterDriver = true;

        private BasicDataSource dataSource = null;

        private PostgreSQLConnectionParameters connectionParameters;

        public PostgreSQLConnectionProvider(PostgreSQLConnectionParameters connectionParameters) {

            this.connectionParameters = connectionParameters;

        }

        @Override

        public Connection getConnection() throws SQLException {

            Connection conn;

            if( USE_CONNECTIONS_POOL ) {

                if (this.dataSource == null) {

                    this.dataSource = this.createDataSource();               

                }

                if( LOGGER.isDebugEnabled() ) {

                    LOGGER.debug("getConnection:\n" + getStatusInformation());

                }

                try {

                    conn = this.dataSource.getConnection();

                } catch(Throwable ex) {

                    LOGGER.debug("Error getting connection from pool.",ex);

                    throw ex;

                }

                try {

                    conn.setNetworkTimeout(null, this.connectionParameters.getNetworkTimeout());

                } catch(Throwable ex) {

                    LOGGER.warn("Error setting the network timeout.",ex);

                }

                if( LOGGER.isDebugEnabled() ) {

                    LOGGER.debug("Created connection: {}\n  NumActive: {}\n  NumIdle: {}",

                        new Object[] {

                            conn.hashCode(), 

                            this.dataSource.getNumActive(),

                            this.dataSource.getNumIdle()

                        }

                    );

                }

            } else {

                try {

                    conn = DriverManager.getConnection(

                        connectionParameters.getUrl(), 

                        connectionParameters.getUser(), 

                        connectionParameters.getPassword()

                    );

                } catch(Throwable th) {

                    throw th;

                }

                if( LOGGER.isDebugEnabled() ) {

                    LOGGER.debug("Created not polled connection: {}",

                        new Object[] {

                            conn.hashCode()

                        }

                    );

                }

            }

            LOGGER.debug("PostgreSQL JDBC Driver: "+DriverInfo.DRIVER_VERSION);

            return conn;

        }

        public String getStatusInformation() {

            StringBuilder builder = new StringBuilder();

            if( this.dataSource==null ) {

                builder.append("Not poolled connection:\n");

                builder.append("  Connection URL: '").append(this.connectionParameters.getUrl()).append("'\n");

            } else {

                builder.append("BasicDataSource pool status:\n");

                builder.append("  Connection URL: '").append(this.dataSource.getUrl()).append("'\n");

                if( this.dataSource.getInitialSize()>0 ) {

                    builder.append("  InitialSize: ").append(this.dataSource.getInitialSize()).append(" (The initial number of connections that are created when the pool is started)\n");

                }

                if( this.dataSource.isPoolPreparedStatements() ) {

                    builder.append("  PoolPreparedStatements: ").append(this.dataSource.isPoolPreparedStatements()).append("\n");

                    builder.append("  MaxOpenPreparedStatements: ").append(this.dataSource.getMaxOpenPreparedStatements()).append(" (The maximum number of open statements that can be allocated from the statement pool at the same time, or non-positive for no limit)\n");

                }

                builder.append("  MaxTotal: ").append(this.dataSource.getMaxTotal()).append(" (The maximum number of active connections that can be allocated from this pool at the same time)\n");

                builder.append("  MaxIdle: ").append(this.dataSource.getMaxIdle()).append(" (The maximum number of connections that can remain idle in the pool)\n");

                builder.append("  NumActive:").append(this.dataSource.getNumActive()).append(" (the current number of active connections)\n");

                builder.append("  NumIdle:").append(this.dataSource.getNumIdle()).append(" (the current number of idle connections)\n");

            }

            return builder.toString();

        }

        private BasicDataSource createDataSource() throws SQLException {

            if (!this.isRegistered()) {

                this.registerDriver();

            }

            PostgreSQLConnectionParameters params = connectionParameters;

            BasicDataSource ds = new BasicDataSource();

            ds.setMaxIdle(params.getMaxIdle());

            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.setMinEvictableIdleTimeMillis(20*1000);

            ds.setTimeBetweenEvictionRunsMillis(20*1000);

            ds.setMinIdle(0);

            ds.setMaxWaitMillis(60L * 1000);

            return ds;

        }

        private boolean isRegistered() {

            return needRegisterDriver;

        }

        @Override

        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;

        }

        @Override

        public void dispose() {

            if( this.dataSource!=null ) {

                try {

                    this.dataSource.close();

                } catch (SQLException ex) {

                    LOGGER.warn("Can't close BasicDataSource", ex);

                }

                this.dataSource = null;

            }

            this.connectionParameters = null;

        }

        @Override

        public String getStatus() {

            if( dataSource==null ) {

                return "Not polled";

            }

            StringBuilder builder = new StringBuilder();

            builder.append("Pool: ");

            builder.append(JDBCUtils.getHexId(dataSource));

            builder.append(" Actives: ");

            builder.append(dataSource.getNumActive());

            builder.append("/");

            builder.append(dataSource.getMaxTotal()); //.getMaxActive());

            builder.append(" idle: ");

            builder.append(dataSource.getNumIdle());

            builder.append("/");

            builder.append(dataSource.getMinIdle());

            builder.append(":");

            builder.append(dataSource.getMaxIdle());

            return builder.toString();

        }

    }

    private ConnectionProvider connectionProvider = null;

    /**

     * Constructor for use only for testing purposes.

     * 

     * @param connectionParameters

     * @param connectionProvider

     */

    public PostgreSQLHelper(PostgreSQLConnectionParameters connectionParameters, ConnectionProvider connectionProvider) { 

        super(connectionParameters);

        this.srssolver = new SRSSolverDumb(this);

        this.connectionProvider = connectionProvider;

    }

    public PostgreSQLHelper(JDBCConnectionParameters connectionParameters) {

        super(connectionParameters);

        this.srssolver = new SRSSolverBase(this);

    }

    @Override

    protected void doDispose() throws BaseException {

        if( this.connectionProvider!=null ) {

            this.connectionProvider.dispose();

            this.connectionProvider = null;

        }

        super.doDispose(); 

    }

    @Override

    public Connection getConnection() throws AccessResourceException {

        try {

            if (this.connectionProvider == null) {

              if( this.getConnectionParameters()==null ) {

                return null;

              }

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

            }

            return this.connectionProvider.getConnection();

        } catch (SQLException ex) {

            throw new AccessResourceException(PostgreSQLLibrary.NAME, ex);

        }

    }

    @Override

    public void closeConnection(Connection connection) {

        if (connection != null) { // In test ???

            LOGGER.debug("[" + JDBCUtils.getConnId(connection) + "] closeConnection " + this.connectionProvider.getStatus());

        }

        super.closeConnection(connection);

    }

    @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 GeometrySupportType getGeometrySupportType() {

        return GeometrySupportType.EWKB;

    }

    @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 PostgreSQLServerExplorerParameters();

    }

}

package org.gvsig.postgresql.dal;

import org.apache.commons.lang3.StringUtils;

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.spi.DataStoreProviderServices;

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 PostgreSQLServerExplorerFactory extends JDBCServerExplorerFactory {

    private static final String NAME = PostgreSQLLibrary.NAME;

    public PostgreSQLServerExplorerFactory() {

        super(

            NAME,

            "PostgreSQL Server"

        );

    }

    public PostgreSQLServerExplorerFactory(String name) {

        // Cuando se instancia la factoria con un "name" que no es el de por

        // defecto, es para declarar "alias" para el ServerExplorer, normalmente

        // para mantener compatibilidad con versiones anteriores. 

        // Marcaremos la factoria como "hidden" para que no aparezca

        // en el interface de usuario.

        super(

            name,

            "PostgreSQL Server (for compatibility)",

            true

        );

    }

    @Override

    public JDBCServerExplorer create(

            DataServerExplorerParameters parameters, 

            DataServerExplorerProviderServices providerServices

        ) throws InitializeException {

        // Esto seria para convertir los parametros de gvSIG 2.3 a 2.4.

//        if( !StringUtils.equalsIgnoreCase(NAME, (CharSequence) parameters.getDynValue(DataStoreProviderServices.PROVIDER_PARAMTER_NAME)) ) {

//            parameters.setDynValue(DataStoreProviderServices.PROVIDER_PARAMTER_NAME, NAME);

//        }

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

        JDBCServerExplorer server = helper.createServerExplorer(

                (JDBCServerExplorerParameters) parameters, 

                providerServices

        );

        return server;

    }

    @Override

    public JDBCServerExplorerParameters createParameters() {

        JDBCServerExplorerParameters params = new PostgreSQLServerExplorerParameters();

        return params;    

    }

}

package org.gvsig.postgresql.dal;

import java.util.Arrays;

import java.util.Collections;

import java.util.HashSet;

import java.util.Set;

import java.util.function.Function;

import org.gvsig.fmap.dal.DataParameters;

import static org.gvsig.fmap.dal.DataStoreProviderFactory.NO;

import static org.gvsig.fmap.dal.DataStoreProviderFactory.UNKNOWN;

import static org.gvsig.fmap.dal.DataStoreProviderFactory.YES;

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 {

    private static final Set<String> ALLOWED_FUNCTIONS = new HashSet<>(

            Arrays.asList(new String[]{

        "COUNT",

        "SUM",

        "MIN",

        "MAX"

    }

            )

    );

    private static final Set<String> DENIED_FUNCTIONS = Collections.EMPTY_SET;

    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;

    }

    @Override

    public Function<String, Integer> allowFunction() {

        return (Function<String, Integer>) (String name) -> {

            if (ALLOWED_FUNCTIONS.contains(name)) {

                return YES;

            }

            if (DENIED_FUNCTIONS.contains(name)) {

                return NO;

            }

            return UNKNOWN;

        };

    }

    @Override

    public int allowGroupBy() {

        return YES;

    }

    @Override

    public int hasVectorialSupport() {

        return YES;

    }

    @Override

    public int hasRasterSupport() {

        return NO;

    }

    @Override

    public int hasTabularSupport() {

        return YES;

    }

    @Override

    public int hasSQLSupport() {

        return YES;

    }

}

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.expressionevaluator.ExpressionBuilder;

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.OperationsFactory.TableReference;

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

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

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;

import org.gvsig.postgresql.dal.PostgreSQLBuilder;

@SuppressWarnings("UseSpecificCatch")

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,

            TableReference table,

            List<String> primaryKeys,

            String defaultGeometryColumn,

            IProjection crs,

            int geometryType,

            int geometrySubtype

    ) {

        super(helper, featureType, table, primaryKeys, defaultGeometryColumn, crs, geometryType, geometrySubtype);

    }

    @Override

    public void fetch(Connection conn) throws DataException {

        super.fetch(conn);

    }

    @Override

    protected int getDataTypeFromMetadata(

            ResultSetMetaData rsMetadata,

            int colIndex

        ) throws SQLException {

        return super.getDataTypeFromMetadata(rsMetadata, colIndex);

    }

    @Override

    public String getSQLToRetrievePrimaryKeysFromInformationSchema() throws SQLException {

        PostgreSQLBuilder sqlbuilder = (PostgreSQLBuilder) this.createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        String column_COLUMN_NAME = "column_name";

        String column_CONSTRAINT_TYPE = "constraint_type";

        if( sqlbuilder.getDatabaseVersion().getMajor()<10 ) {

            column_COLUMN_NAME = "COLUMN_NAME";

            column_CONSTRAINT_TYPE = "CONSTRAINT_TYPE";

        }

        sqlbuilder.select().column().name(column_COLUMN_NAME);

        sqlbuilder.select().column().name(column_CONSTRAINT_TYPE);

        sqlbuilder.select().from().custom(

                "INFORMATION_SCHEMA.table_constraints t_cons "

                + "inner join INFORMATION_SCHEMA.key_column_usage c on "

                + "c.constraint_catalog = t_cons.constraint_catalog and "

                + "c.table_schema = t_cons.table_schema and "

                + "c.table_name = t_cons.table_name and "

                + "c.constraint_name = t_cons.constraint_name "

        );

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

                expbuilder.like(

                        expbuilder.custom("c.TABLE_NAME"), 

                        expbuilder.constant(table.getTable())

                )

        );

        if (table.hasSchema()) {

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

                    expbuilder.like(

                            expbuilder.custom("c.TABLE_SCHEMA"),

                            expbuilder.constant(table.getSchema())

                    )

            );

        }

//        if (catalog != null) {

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

//                    expbuilder.like(

//                            expbuilder.custom("c.CONSTRAINT_CATALOG"),

//                            expbuilder.constant(catalog)

//                    )

//            );

//        }

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

                expbuilder.eq(

                        expbuilder.column(column_CONSTRAINT_TYPE),

                        expbuilder.constant("PRIMARY KEY")

                )

        );

        return sqlbuilder.toString();

    }

    @Override

    protected void fetchGeometryTypeAndSRS(

            EditableFeatureAttributeDescriptor attr,

            ResultSetMetaData rsMetadata,

            int colIndex

        ) {

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

            return;

        }

        try {

            JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

            ExpressionBuilder expbuilder = sqlbuilder.expression();

            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(

                    expbuilder.eq(

                            expbuilder.column("f_table_name"),

                            expbuilder.constant(this.getTable().getTable())

                    )

            );                

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

                    expbuilder.eq(

                            expbuilder.column("f_geometry_column"),

                            expbuilder.constant(attr.getName())

                    )

            );         

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

            Statement st = null;

            ResultSet rs = null;

            Integer srsid = null;

            String geometryTypeName = null;

            try {

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

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

                if (rs.next()) {

                    srsid = rs.getInt("srid");

                    geometryTypeName = rs.getString("type");

                }

            } finally {

                JDBCUtils.closeQuietly(rs);

                JDBCUtils.closeQuietly(st);

            }

            if( !StringUtils.isEmpty(geometryTypeName) ) {

                GeometryType gt = getGeometryTypeFromDatabaseTypeName(geometryTypeName);

                attr.setGeometryType(gt);

            }

            if( srsid!=null ) {

                SRSSolver srssolver = this.helper.getSRSSolver();

                attr.setSRS(srssolver.getProjection(this.getConnection(),srsid));

            }

        } catch (Exception ex) {

            LOGGER.debug("Can't get geometry type and srs from column '"+attr.getName()+"'.",ex);

        }

    }

    private GeometryType getGeometryTypeFromDatabaseTypeName(String typeName) {

        if( databaseGeometryTypes==null ) {

            GeometryManager manager = GeometryLocator.getGeometryManager();

            databaseGeometryTypes = new HashMap<>();

            databaseGeometryTypes.put("POINT", getGT(manager, Geometry.TYPES.POINT,Geometry.SUBTYPES.GEOM2D));

            databaseGeometryTypes.put("POINTZ", getGT(manager, Geometry.TYPES.POINT,Geometry.SUBTYPES.GEOM3D));

            databaseGeometryTypes.put("POINTM", getGT(manager, Geometry.TYPES.POINT,Geometry.SUBTYPES.GEOM2DM));

            databaseGeometryTypes.put("POINTZM", getGT(manager, Geometry.TYPES.POINT,Geometry.SUBTYPES.GEOM3DM));

            databaseGeometryTypes.put("LINESTRING", getGT(manager, Geometry.TYPES.LINE,Geometry.SUBTYPES.GEOM2D));

            databaseGeometryTypes.put("LINESTRINGZ", getGT(manager, Geometry.TYPES.LINE,Geometry.SUBTYPES.GEOM3D));

            databaseGeometryTypes.put("LINESTRINGM", getGT(manager, Geometry.TYPES.LINE,Geometry.SUBTYPES.GEOM2DM));

            databaseGeometryTypes.put("LINESTRINGZM", getGT(manager, Geometry.TYPES.LINE,Geometry.SUBTYPES.GEOM3DM));

            databaseGeometryTypes.put("POLYGON", getGT(manager, Geometry.TYPES.POLYGON,Geometry.SUBTYPES.GEOM2D));

            databaseGeometryTypes.put("POLYGONZ", getGT(manager, Geometry.TYPES.POLYGON,Geometry.SUBTYPES.GEOM3D));

            databaseGeometryTypes.put("POLYGONM", getGT(manager, Geometry.TYPES.POLYGON,Geometry.SUBTYPES.GEOM2DM));

            databaseGeometryTypes.put("POLYGONZM", getGT(manager, Geometry.TYPES.POLYGON,Geometry.SUBTYPES.GEOM3DM));

            databaseGeometryTypes.put("MULTIPOINT", getGT(manager, Geometry.TYPES.MULTIPOINT,Geometry.SUBTYPES.GEOM2D));

            databaseGeometryTypes.put("MULTIPOINTZ", getGT(manager, Geometry.TYPES.MULTIPOINT,Geometry.SUBTYPES.GEOM3D));

            databaseGeometryTypes.put("MULTIPOINTM", getGT(manager, Geometry.TYPES.MULTIPOINT,Geometry.SUBTYPES.GEOM2DM));

            databaseGeometryTypes.put("MULTIPOINTZM", getGT(manager, Geometry.TYPES.MULTIPOINT,Geometry.SUBTYPES.GEOM3DM));

            databaseGeometryTypes.put("MULTILINESTRING", getGT(manager, Geometry.TYPES.MULTILINE,Geometry.SUBTYPES.GEOM2D));

            databaseGeometryTypes.put("MULTILINESTRINGZ", getGT(manager, Geometry.TYPES.MULTILINE,Geometry.SUBTYPES.GEOM3D));

            databaseGeometryTypes.put("MULTILINESTRINGM", getGT(manager, Geometry.TYPES.MULTILINE,Geometry.SUBTYPES.GEOM2DM));

            databaseGeometryTypes.put("MULTILINESTRINGZM", getGT(manager, Geometry.TYPES.MULTILINE,Geometry.SUBTYPES.GEOM3DM));

            databaseGeometryTypes.put("MULTIPOLYGON", getGT(manager, Geometry.TYPES.MULTIPOLYGON,Geometry.SUBTYPES.GEOM2D));

            databaseGeometryTypes.put("MULTIPOLYGONZ", getGT(manager, Geometry.TYPES.MULTIPOLYGON,Geometry.SUBTYPES.GEOM3D));

            databaseGeometryTypes.put("MULTIPOLYGONM", getGT(manager, Geometry.TYPES.MULTIPOLYGON,Geometry.SUBTYPES.GEOM2DM));

            databaseGeometryTypes.put("MULTIPOLYGONZM", getGT(manager, Geometry.TYPES.MULTIPOLYGON,Geometry.SUBTYPES.GEOM3DM));

            databaseGeometryTypes.put("GEOMETRY", getGT(manager, Geometry.TYPES.GEOMETRY,Geometry.SUBTYPES.GEOM2D));

            databaseGeometryTypes.put("GEOMETRYZ", getGT(manager, Geometry.TYPES.GEOMETRY,Geometry.SUBTYPES.GEOM3D));

            databaseGeometryTypes.put("GEOMETRYM", getGT(manager, Geometry.TYPES.GEOMETRY,Geometry.SUBTYPES.GEOM2DM));

            databaseGeometryTypes.put("GEOMETRYZM", getGT(manager, Geometry.TYPES.GEOMETRY,Geometry.SUBTYPES.GEOM3DM));

        }

        return databaseGeometryTypes.get(typeName);

    }

}

package org.gvsig.postgresql.dal.operations;

import java.util.List;

import org.cresques.cts.IProjection;

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

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

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

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

public class PostgreSQLOperationsFactory extends OperationsFactoryBase {

    public PostgreSQLOperationsFactory(JDBCHelper helper) {

        super(helper);

    }

    @Override

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

        return new PostgreSQLFetchFeatureTypeOperation(

                helper, type, table, primaryKeys, 

                defaultGeometryField, crs, -1, -1

        );

    }  

    @Override

    public FetchFeatureTypeOperation createFetchFeatureType(EditableFeatureType type, TableReference table, List<String> primaryKeys, String defaultGeometryField, IProjection crs, int geometryType, int geometrySubtype) {

        return new PostgreSQLFetchFeatureTypeOperation(

                helper, type, table, primaryKeys, 

                defaultGeometryField, crs, geometryType, geometrySubtype

        );

    }  

}

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

public class PostgreSQLStoreParameters extends JDBCStoreParameters implements PostgreSQLConnectionParameters {

    private final PostgreSQLConnectionParametersHelper helper;

    public PostgreSQLStoreParameters() {

        super(

                PostgreSQLLibrary.NAME + "StoreParameters",

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

    }

    @Override

    public int getMaxIdle() {

        return this.helper.getMaxIdle();

    }

    public void setUseSSL(boolean v) {

        this.helper.setUseSSL(v);

    }

    @Override

    public int getNetworkTimeout() {

        return this.helper.getNetworkTimeout();

    }

}

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 static final String MAXIDLE_PARAMTER_NAME = "maxIdle";

    public static final String NETWORK_TIMEOUT = "networkTimeout";

    public boolean getUseSSL();

    public int getMaxIdle();

    public int getNetworkTimeout();

}

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 int getMaxIdle() {

        return (int) this.getDynValue(PostgreSQLConnectionParameters.MAXIDLE_PARAMTER_NAME);

    }

    public boolean getUseSSL() {

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

    }

    public void setUseSSL(boolean v) {

        this.setDynValue(PostgreSQLConnectionParameters.USESSL_PARAMTER_NAME, v);

    }

    public int getNetworkTimeout() {

        return (int) this.getDynValue(PostgreSQLConnectionParameters.NETWORK_TIMEOUT);

    }

}

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.json.Json;

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",

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

        }

        // Por compatibilidad con gvSIG 2.3 registramos otra vez la factoria con

        // el nombre que tenia antes.

        if (!dataman.getServerExplorerRegister().exits("PostgreSQLExplorer")) {

            dataman.registerServerExplorerFactory(new PostgreSQLServerExplorerFactory("PostgreSQLExplorer"));

        }

        Json.registerSerializer(PostgreSQLServerExplorerParameters.class);

        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.sql.Connection;

import java.sql.DatabaseMetaData;

import java.text.MessageFormat;

import java.util.ArrayList;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.Formatter;

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

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

import org.gvsig.postgresql.dal.expressionbuilderformatter.PostgreSQLFormatter;

@SuppressWarnings("UseSpecificCatch")

public class PostgreSQLBuilder extends JDBCSQLBuilderBase {

    protected Formatter formatter = null;

    public static class Version {

        private final int major;

        private final int minor;

        public Version(int major, int minor) {

            this.major = major;

            this.minor = minor;

        }

        public int getMajor() {

            return major;

        }

        public int getMinor() {

            return minor;

        }

    }

    private Version databaseVersion = null;

    public Version getDatabaseVersion() {

        if( databaseVersion == null ) {

            Connection conn = null;

            try {

                conn = this.getHelper().getConnection();

                DatabaseMetaData metadata = conn.getMetaData();

                databaseVersion = new Version(

                    metadata.getDatabaseMajorVersion(),

                    metadata.getDatabaseMinorVersion()

                );

            } catch (Exception ex) {

                databaseVersion = new Version(0,0);

            } finally {

                this.getHelper().closeConnectionQuietly(conn);

            }

        }

        return databaseVersion;

    }

    public PostgreSQLBuilder(JDBCHelper helper) {

        super(helper);

        this.defaultSchema = "public";

        this.supportSchemas = true;

        this.allowAutomaticValues = true;

        this.geometrySupportType = this.helper.getGeometrySupportType();

        this.hasSpatialFunctions = this.helper.hasSpatialFunctions();

        this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table = null;

        this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_table = null;

        this.STMT_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.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( !StringUtils.isBlank(STMT_UPDATE_TABLE_STATISTICS_table) ) {

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

                // use the database name in the table name.

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

                if( table.has_schema()) {

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

                }

                String sql = MessageFormat.format(

                        STMT_UPDATE_TABLE_STATISTICS_table,

                        name

                    );

                if( !StringUtils.isEmpty(sql) ) {

                    sqls.add(sql);

                }

            }

            return sqls;

        }        

    }

    protected class PostgreSQLCreateIndexBuilder extends CreateIndexBuilderBase {

        @Override

        public List<String> toStrings(Formatter formatter) {

            StringBuilder builder = new StringBuilder();

            builder.append("CREATE ");

//            if( this.isUnique ) {

//                builder.append("UNIQUE ");

//            }

            builder.append("INDEX ");

            if( this.ifNotExist ) {

                Version version = getDatabaseVersion();

                if( version.getMajor()>=9 && version.getMinor()>=5 ) {

                    builder.append("IF NOT EXISTS ");

                }

            }

            builder.append(as_identifier(this.indexName));

            builder.append(" ON ");

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

            if( this.isSpatial ) {

                builder.append(" USING GIST ");

            }

            builder.append(" ( ");

            boolean is_first_column = true;

            for( String column : this.columns) {

                if( is_first_column ) {

                    is_first_column = false;

                } else {

                    builder.append(", ");

                }

                builder.append(as_identifier(column));

            }

            builder.append(" )");

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

            sqls.add(builder.toString());

            return sqls;

        }

    }

    protected class PostgreSQLCreateTableBuilder extends CreateTableBuilderBase {

        @Override

       public List<String> toStrings(Formatter formatter) {

           // 

           // 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(formatter));

            builder.append(" (");

            boolean first = true;

            for (ColumnDescriptor column : columns) {

                if( column.isGeometry() ) {

                    continue;

                }

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

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

                builder.append(" ");

                if( column.isAutomatic() ) {

                    builder.append(" SERIAL");

                } else {

                    builder.append(sqltype(

                            column.getType(), 

                            column.getSize(),

                            column.getPrecision(), 

                            column.getScale(), 

                            column.getGeometryType(),

                            column.getGeometrySubtype()

                    ));

                    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 (ColumnDescriptor column : columns) {

                if( column.isGeometry() ) {

                    String sql = MessageFormat.format(

                        AddGeometryColumn,

                        as_string(this.table.has_schema()?this.table.getSchema():"public"),

                        as_string(this.table.getName()),

                        as_string(column.getName()),

                        column.getGeometrySRSId(),

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

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

                        as_string(column.allowNulls())

                    );

                    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(Formatter formatter) {

            //

            // 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(", ");