Application: gvSIG desktop » gvSIG data provider for PostgreSQL

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.isBlank(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.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.JDBCConnection;

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 ) {

            JDBCConnection 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 {

                JDBCConnection.closeQuietly(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(", ");

                }

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

            }

            if ( this.has_from() ) {

                builder.append(" FROM ");

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

            }

            if ( this.has_where() ) {

                builder.append(" WHERE ");

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

            }

            if( this.has_group_by() ) {

                builder.append(" GROUP BY ");

                builder.append(this.groupColumn.get(0).toString(formatter));

                for (int i = 1; i < groupColumn.size(); i++) {

                    builder.append(", ");

                    builder.append(this.groupColumn.get(i).toString(formatter));

                }

            } 

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

                }   

            }

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

        if( this.formatter==null ) {

            this.formatter = new PostgreSQLFormatter(this);

        }

        return this.formatter;

    }

    @Override

    public PostgreSQLHelper getHelper() {

        return (PostgreSQLHelper) helper;

    }

    @Override

    protected CreateTableBuilder createCreateTableBuilder() {

        return new PostgreSQLCreateTableBuilder();

    }

    @Override

    protected CreateIndexBuilder createCreateIndexBuilder() {

        return new PostgreSQLCreateIndexBuilder();

    }

    @Override

    public SelectBuilder createSelectBuilder() {

        return new PostgreSQLSelectBuilderBase();

    }

    @Override

    protected UpdateTableStatisticsBuilder createUpdateTableStatisticsBuilder() {

        return new PostgreSQLUpdateTableStatisticsBuilderBase();

    }       

    @Override

    public int getMaxRecomendedSQLLength() {

        return 10240;

    }

    @Override

    public String as_clob(String s) {

        int chunkSize = 1024;

        StringBuilder builder = new StringBuilder();

        builder.append("('");

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

            String chunk = s.substring(i, Math.min(s.length(), i + chunkSize));

            if( i>0 ) {

                builder.append("'::text || '");

            }

            builder.append(StringUtils.replace(chunk, "'", "''"));

        }

        builder.append("'::text)");

        return builder.toString();

    }

}

package org.gvsig.postgresql.dal.expressionbuilderformatter;

import java.text.MessageFormat;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.ExpressionBuilder.Function;

import static org.gvsig.expressionevaluator.ExpressionBuilder.OPERATOR_ILIKE;

import org.gvsig.expressionevaluator.ExpressionBuilder.Value;

import org.gvsig.expressionevaluator.Formatter;

import org.gvsig.fmap.dal.SQLBuilder;

/**

 *

 * @author jjdelcerro

 */

class ILike implements Formatter<Value> {

    private final Formatter<Value> formatter;

    private final SQLBuilder builder;

    public ILike(SQLBuilder builder, Formatter<Value> formatter) {

        this.builder = builder;

        this.formatter = formatter;

    }

    @Override

    public boolean canApply(Value value) {

        if (value instanceof Function) {

            return StringUtils.equalsIgnoreCase(OPERATOR_ILIKE, ((Function) value).name());

        }

        return false;

    }

    @Override

    public String format(Value function) {

        List<Value> parameters = ((Function) function).parameters();

        String p1 = parameters.get(0).toString(formatter);

        String p2 = parameters.get(1).toString(formatter);

        String r = MessageFormat.format("({0}) ~~* ({1})", p1, p2);

        return r;

    }

}

package org.gvsig.postgresql.dal.expressionbuilderformatter;

import java.util.List;

import java.util.Objects;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import static org.gvsig.expressionevaluator.ExpressionBuilder.FUNCTION_DECODE;

import org.gvsig.expressionevaluator.ExpressionBuilder.Function;

import org.gvsig.expressionevaluator.ExpressionBuilder.Value;

import org.gvsig.expressionevaluator.Formatter;

import org.gvsig.fmap.dal.SQLBuilder;

/**

 *

 * @author jjdelcerro

 */

public class Decode implements Formatter<Value> {

    private final SQLBuilder sqlbuilder;

    private final Formatter<Value> formatter;

    public Decode(SQLBuilder sqlbuilder, Formatter<Value> formatter) {

        this.sqlbuilder = sqlbuilder;

        this.formatter = formatter;

    }

    @Override

    public boolean canApply(ExpressionBuilder.Value value) {

        if (value instanceof ExpressionBuilder.Function) {

            if( StringUtils.equalsIgnoreCase(FUNCTION_DECODE, ((Function) value).name()) ) {

                List<Value> parameters = ((Function) value).parameters();

                if( parameters.size()==2) {

                    Value p1 = parameters.get(0);

                    Value p2 = parameters.get(1);

                    if( p1 instanceof ExpressionBuilder.Constant && 

                        p2 instanceof ExpressionBuilder.Constant &&

                        ((ExpressionBuilder.Constant)p1).value() instanceof String &&

                        ((ExpressionBuilder.Constant)p2).value() instanceof String 

                        ) {

                        String p2value = ((ExpressionBuilder.Constant)p2).value().toString();

                        if( StringUtils.equalsIgnoreCase(p2value, "hex") ) {

                            return true;

                        }

                    }

                }

            }

        }

        return false;

    }

    @Override

    public String format(Value function) {

        ExpressionBuilder builder = this.sqlbuilder.expression();

        List<Value> parameters = ((Function) function).parameters();

        Value p1 = parameters.get(0);

        String p1value = Objects.toString(((ExpressionBuilder.Constant)p1).value(), "");

        String r = builder.string("\\x"+p1value)+"::bytea";

        return r;

    }

}

package org.gvsig.postgresql.dal.expressionbuilderformatter;

import java.text.MessageFormat;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import static org.gvsig.expressionevaluator.GeometryExpressionBuilder.FUNCTION_ST_EXTENTAGGREGATE;

import org.gvsig.expressionevaluator.ExpressionBuilder.Function;

import org.gvsig.expressionevaluator.ExpressionBuilder.Value;

import org.gvsig.expressionevaluator.Formatter;

import org.gvsig.fmap.dal.SQLBuilder;

/**

 *

 * @author jjdelcerro

 */

public class ST_ExtentAggregate implements Formatter<Value> {

    private final SQLBuilder sqlbuilder;

    private final Formatter<Value> formatter;

    public ST_ExtentAggregate(SQLBuilder sqlbuilder, Formatter<Value> formatter) {

        this.sqlbuilder = sqlbuilder;

        this.formatter = formatter;

    }

    @Override

    public boolean canApply(ExpressionBuilder.Value value) {

        if (value instanceof ExpressionBuilder.Function) {

            return StringUtils.equalsIgnoreCase(FUNCTION_ST_EXTENTAGGREGATE, ((Function) value).name());

        }

        return false;

    }

    @Override

    public String format(Value function) {

        List<Value> parameters = ((Function) function).parameters();

        String p1 = parameters.get(0).toString(formatter);

        String r = MessageFormat.format("ST_Extent({0})", p1);

        return r;

    }

}

package org.gvsig.postgresql.dal.expressionbuilderformatter;

import java.text.MessageFormat;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.expressionevaluator.ExpressionBuilder.Function;

import org.gvsig.expressionevaluator.ExpressionBuilder.Value;

import org.gvsig.expressionevaluator.Formatter;

import org.gvsig.fmap.dal.SQLBuilder;

import static org.gvsig.fmap.dal.SQLBuilder.PROP_FEATURE_TYPE;

import static org.gvsig.fmap.dal.SQLBuilder.PROP_QUERY;

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

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

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

import org.gvsig.timesupport.DataTypes;

/**

 *

 * @author jjdelcerro

 */

class Sum implements Formatter<Value> {

    private final Formatter<Value> formatter;

    private final SQLBuilder builder;

    public Sum(SQLBuilder builder, Formatter<Value> formatter) {

        this.builder = builder;

        this.formatter = formatter;

    }

    @Override

    public boolean canApply(Value value) {

        if (value instanceof Function) {

            return StringUtils.equalsIgnoreCase(ExpressionBuilder.FUNCTION_SUM, ((Function) value).name());

        }

        return false;

    }

    @Override

    public String format(Value function) {

        List<Value> parameters = ((Function) function).parameters();

        Value p1 = parameters.get(0);

        if(p1 instanceof ExpressionBuilder.Variable) {

            FeatureType featureType = (FeatureType) p1.getProperty(PROP_FEATURE_TYPE);

            if(featureType != null){

                ExpressionBuilder.Variable variable = (ExpressionBuilder.Variable) p1;

                FeatureAttributeDescriptor attr = featureType.getAttributeDescriptorFromAll(variable.name());

                if (attr == null) {

                    FeatureQuery query = (FeatureQuery) p1.getProperty(PROP_QUERY);

                    if(query != null){

                        attr = query.getExtraColumn().get(variable.name());

                    }

                }

                if(attr != null){

                    if(attr.getDataType().getType() == DataTypes.BOOLEAN){

                        String r = MessageFormat.format("SUM(({0})::INT)", p1.toString(formatter));

                        return r;

                    }

                    if(attr.getDataType().isNumeric()){

                        String r = MessageFormat.format("SUM(({0}))", p1.toString(formatter));

                        return r;

                    }

                }

            }

        }

        String r = MessageFormat.format("SUM(({0})::DECIMAL)", p1.toString(formatter));

        return r;

    }

}

package org.gvsig.postgresql.dal.expressionbuilderformatter;

import java.text.MessageFormat;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.expressionevaluator.ExpressionBuilder.Function;

import org.gvsig.expressionevaluator.ExpressionBuilder.Value;

import org.gvsig.expressionevaluator.Formatter;

import org.gvsig.expressionevaluator.GeometryExpressionBuilder;

import org.gvsig.fmap.dal.SQLBuilder;

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

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

/**

 *

 * @author jjdelcerro

 */

public class ST_GeomFromWKB implements Formatter<Value> {

    private final SQLBuilder sqlbuilder;

    private final Formatter<Value> formatter;

    public ST_GeomFromWKB(SQLBuilder sqlbuilder, Formatter<Value> formatter) {

        this.sqlbuilder = sqlbuilder;

        this.formatter = formatter;

    }

    @Override

    public boolean canApply(ExpressionBuilder.Value value) {

        if (value instanceof ExpressionBuilder.Function) {

            return StringUtils.equalsIgnoreCase(GeometryExpressionBuilder.FUNCTION_ST_GEOMFROMWKB, ((Function) value).name());

        }

        return false;

    }

    @Override

    public String format(Value function) {

        String r;

        String wkb;

        String srid;

        List<Value> parameters = ((Function) function).parameters();

        switch(parameters.size()) {

            case 1:

                wkb = parameters.get(0).toString(formatter);

                r = MessageFormat.format("ST_GeomFromWKB({0})", wkb);

                break;

            case 2:

                wkb = parameters.get(0).toString(formatter);

                srid = parameters.get(1).toString(formatter);

                if( StringUtils.equals("0", srid) ) {

                    r = MessageFormat.format("ST_GeomFromWKB({0})", wkb);

                } else {

                    FeatureType ftype = (FeatureType) function.getProperty(PROP_FEATURE_TYPE);

                    if( ftype == null || ftype.getDefaultSRS()!=null ) {

                        r = MessageFormat.format("ST_GeomFromWKB({0},{1})", wkb, srid);

                    } else {

                        r = MessageFormat.format("ST_GeomFromWKB({0})", wkb);

                    }

                }

                break;

            default:

                throw new IllegalArgumentException("Wrong number of arguments ("+parameters.size()+") in function ST_GeomFromWKB; requiered 1 or 2 arguments.");

        }

        return r;

    }

}

package org.gvsig.postgresql.dal.expressionbuilderformatter;

import java.text.MessageFormat;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.Code.Constant;

import org.gvsig.expressionevaluator.ExpressionBuilder.Function;

import static org.gvsig.expressionevaluator.ExpressionBuilder.OPERATOR_IS;

import org.gvsig.expressionevaluator.ExpressionBuilder.Value;

import org.gvsig.expressionevaluator.Formatter;

import org.gvsig.fmap.dal.SQLBuilder;

/**

 *

 * @author jjdelcerro

 */

class IsNull implements Formatter<Value> {

    private final Formatter<Value> formatter;

    private final SQLBuilder builder;

    public IsNull(SQLBuilder builder, Formatter<Value> formatter) {

        this.builder = builder;

        this.formatter = formatter;

    }

    @Override

    public boolean canApply(Value value) {

        if (value instanceof Function) {

            if( StringUtils.equalsIgnoreCase(OPERATOR_IS, ((Function) value).name()) ) {

                List<Value> parameters = ((Function) value).parameters();

                Value p2 = parameters.get(1);

                if( p2 instanceof Constant && ((Constant)p2).value()==null ) {

                    return true;

                }

            }

        }

        return false;

    }

    @Override

    public String format(Value function) {

        List<Value> parameters = ((Function) function).parameters();

        String p1 = parameters.get(0).toString(formatter);

        String r = MessageFormat.format("( ({0}) ISNULL )", p1);

        return r;

    }

}

package org.gvsig.postgresql.dal.expressionbuilderformatter;

import java.text.MessageFormat;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import static org.gvsig.expressionevaluator.ExpressionBuilder.FUNCTION_IFNULL;

import org.gvsig.expressionevaluator.ExpressionBuilder.Function;

import org.gvsig.expressionevaluator.ExpressionBuilder.Value;

import org.gvsig.expressionevaluator.Formatter;

import org.gvsig.fmap.dal.SQLBuilder;

import org.gvsig.tools.dataTypes.DataTypes;

/**

 *

 * @author jjdelcerro

 */

class JsonValue implements Formatter<Value> {

    private final Formatter<Value> formatter;

    private final SQLBuilder builder;

    public JsonValue(SQLBuilder builder, Formatter<Value> formatter) {

        this.builder = builder;

        this.formatter = formatter;

    }

    @Override

    public boolean canApply(Value value) {

        if (value instanceof Function) {

            if (value instanceof ExpressionBuilder.Function) {

                boolean canApply = StringUtils.equalsIgnoreCase(ExpressionBuilder.FUNCTION_JSON_VALUE, ((Function) value).name());

                return canApply;

            }

        }

        return false;

    }

    @Override

    public String format(Value function) {

        List<Value> parameters = ((Function) function).parameters();

        String p1 = parameters.get(0).toString(formatter);

        String p2 = parameters.get(1).toString(formatter);

        String r;

        r = MessageFormat.format(

                "(jsonb_path_query_first({0}::jsonb, {1}) ->> 0 )",

                p1,

                p2

        );

        return r;

    }

}

package org.gvsig.postgresql.dal.expressionbuilderformatter;

import java.text.MessageFormat;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.expressionevaluator.ExpressionBuilder.Function;

import org.gvsig.expressionevaluator.ExpressionBuilder.Value;

import org.gvsig.expressionevaluator.Formatter;

import org.gvsig.expressionevaluator.GeometryExpressionBuilder;

import org.gvsig.fmap.dal.SQLBuilder;

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

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

/**

 *

 * @author jjdelcerro

 */

public class ST_GeomFromText implements Formatter<Value> {

    private final SQLBuilder sqlbuilder;

    private final Formatter<Value> formatter;

    public ST_GeomFromText(SQLBuilder sqlbuilder, Formatter<Value> formatter) {

        this.sqlbuilder = sqlbuilder;

        this.formatter = formatter;

    }

    @Override

    public boolean canApply(ExpressionBuilder.Value value) {

        if (value instanceof ExpressionBuilder.Function) {

            return StringUtils.equalsIgnoreCase(GeometryExpressionBuilder.FUNCTION_ST_GEOMFROMTEXT, ((Function) value).name());

        }

        return false;

    }

    @Override

    public String format(Value function) {

        String r;

        String wkt;

        String srid;

        List<Value> parameters = ((Function) function).parameters();

        switch(parameters.size()) {

            case 1:

                wkt = parameters.get(0).toString(formatter);

                r = MessageFormat.format("ST_GeomFromText({0})", wkt);

                break;

            case 2:

                wkt = parameters.get(0).toString(formatter);

                srid = parameters.get(1).toString(formatter);

                if( StringUtils.equals("0", srid) ) {

                    r = MessageFormat.format("ST_GeomFromText({0})", wkt);

                } else {

                    FeatureType ftype = (FeatureType) function.getProperty(PROP_FEATURE_TYPE);

                    if( ftype == null || ftype.getDefaultSRS()!=null ) {

                        r = MessageFormat.format("ST_GeomFromText({0},{1})", wkt, srid);

                    } else {

                        r = MessageFormat.format("ST_GeomFromText({0})", wkt);

                    }

                }

                break;

            default:

                throw new IllegalArgumentException("Wrong number of arguments ("+parameters.size()+") in function ST_GeomFromText; requiered 1 or 2 arguments.");

        }

        return r;

    }

}

package org.gvsig.postgresql.dal.expressionbuilderformatter;

import java.text.MessageFormat;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.Code.Constant;

import org.gvsig.expressionevaluator.ExpressionBuilder.Function;

import static org.gvsig.expressionevaluator.ExpressionBuilder.OPERATOR_IS;

import static org.gvsig.expressionevaluator.ExpressionBuilder.OPERATOR_NOT;

import org.gvsig.expressionevaluator.ExpressionBuilder.Value;

import org.gvsig.expressionevaluator.Formatter;

import org.gvsig.fmap.dal.SQLBuilder;

/**

 *

 * @author jjdelcerro

 */

class IsNotNull implements Formatter<Value> {

    private final Formatter<Value> formatter;

    private final SQLBuilder builder;

    public IsNotNull(SQLBuilder builder, Formatter<Value> formatter) {

        this.builder = builder;

        this.formatter = formatter;

    }

    @Override

    public boolean canApply(Value value_not) {

        if (value_not instanceof Function) {

            // NOT( IS( p1, NULL) )

            if( StringUtils.equalsIgnoreCase(OPERATOR_NOT, ((Function) value_not).name()) ) {

                List<Value> parameters = ((Function) value_not).parameters();

                Value value_is = parameters.get(0);

                if( value_is instanceof Function && StringUtils.equalsIgnoreCase(OPERATOR_IS, ((Function)value_is).name()) ) {

                    List<Value> parameters_is = ((Function) value_is).parameters();

                    Value is_p2 = parameters_is.get(1);

                    if( is_p2 instanceof Constant && ((Constant)is_p2).value()==null ) {

                        return true;

                    }

                }

            }

        }

        return false;

    }

    @Override

    public String format(Value value_not) {

        // NOT( IS( p1, NULL) )

        List<Value> parameters = ((Function) value_not).parameters();

        Value value_is = parameters.get(0);

        List<Value> parameters_is = ((Function)value_is).parameters();

        Value is_p1 = parameters_is.get(0);

        String r = MessageFormat.format(

                "( ({0}) IS NOT NULL )", 

                is_p1.toString(formatter)

        );

        return r;

    }

}

package org.gvsig.postgresql.dal.expressionbuilderformatter;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.expressionevaluator.ExpressionBuilder.Value;

import org.gvsig.expressionevaluator.Formatter;

import org.gvsig.expressionevaluator.spi.formatter.value.Cast;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.SQLBuilder;

import org.gvsig.fmap.dal.store.jdbc2.spi.expressionbuilder.formatters.$Constant;

import org.gvsig.fmap.dal.store.jdbc2.spi.expressionbuilder.formatters.$Identifier;

import org.gvsig.fmap.dal.store.jdbc2.spi.expressionbuilder.formatters.ComputedAttribute;

import org.gvsig.fmap.dal.store.jdbc2.spi.expressionbuilder.formatters.Exists;

import org.gvsig.fmap.dal.store.jdbc2.spi.expressionbuilder.formatters.In;

import org.gvsig.fmap.dal.store.jdbc2.spi.expressionbuilder.formatters.ToDouble;

import org.gvsig.fmap.dal.store.jdbc2.spi.expressionbuilder.formatters.ToInteger;

import org.gvsig.fmap.dal.store.jdbc2.spi.expressionbuilder.formatters.ToLong;

import org.gvsig.fmap.dal.store.jdbc2.spi.expressionbuilder.formatters.ToString;

/**

 *

 * @author jjdelcerro

 */

public class PostgreSQLFormatter implements Formatter<Value> {

    private final SQLBuilder builder;

    private final Formatter<ExpressionBuilder.Value>[] formatters;

    @SuppressWarnings("LeakingThisInConstructor")

    public PostgreSQLFormatter(SQLBuilder builder) {

        this.builder = builder;

        this.formatters = new Formatter[]{

            new Decode(this.builder, this),

            new Constant(this.builder, this),

            new ILike(this.builder, this),

            new IfNull(this.builder, this),

            new IsNull(this.builder, this),

            new IsNotNull(this.builder, this),

            new ST_ExtentAggregate(this.builder, this),

            new ST_GeomFromWKB(this.builder, this),

            new ST_GeomFromText(this.builder, this),

            new Getattr(this.builder, this),

//            new Iff(this),

            new Exists(this),

            new $Constant(this.builder, this),

            new $Identifier(this.builder, this),

            new ComputedAttribute(this.builder, this),

            new ToString(this.builder, this),

            new ToInteger(this.builder, this),

            new ToDouble(this.builder, this),

            new ToLong(this.builder, this),

            new JsonValue(this.builder, this),

            new Sum(this.builder, this),

            new Cast(this),

            new In(this),

            DALLocator.getDataManager().createDALExpressionBuilder().formatter(this),

        };

    }

    @Override

    public boolean canApply(Value value) {

        for (Formatter<Value> formatter : formatters) {

            if (formatter.canApply(value)) {

                return true;

            }

        }

        return false;

    }

    @Override

    public String format(Value value) {

        for (Formatter<Value> formatter : formatters) {

            if (formatter.canApply(value)) {

                return formatter.format(value);

            }

        }

        return value.toString(this);

    }

}

package org.gvsig.postgresql.dal.expressionbuilderformatter;

import java.text.MessageFormat;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.expressionevaluator.ExpressionBuilder.Value;

import org.gvsig.expressionevaluator.Formatter;

import org.gvsig.expressionevaluator.GeometryExpressionBuilder;

import org.gvsig.fmap.dal.SQLBuilder;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryUtils;

/**

 *

 * @author jjdelcerro

 */

public class Constant implements Formatter<Value> {

    private static final String FORMAT_ST_GEOMFROMWKB = "ST_GeomFromWKB(({0}), ({1}))";

    private static final String FORMAT_ST_GEOMFROMEWKB = "ST_GeomFromEWKB(({0}), ({1}))";

    private static final String FORMAT_ST_GEOMFROMTEXT = "ST_GeomFromText(({0}), ({1}))";

    private final SQLBuilder sqlbuilder;

    private final Formatter<Value> formatter;

    public Constant(SQLBuilder sqlbuilder, Formatter<Value> formatter) {

        this.sqlbuilder = sqlbuilder;

        this.formatter = formatter;

    }

    @Override

    public boolean canApply(ExpressionBuilder.Value value) {

        if (value instanceof ExpressionBuilder.Constant) {

            Object x = ((ExpressionBuilder.Constant)value).value();

            if( x instanceof byte[] ) {

                return true;

            }

            if( x instanceof Geometry ) {

                return true;

            }

        }

        return false;

    }

    @Override

    public String format(Value constant) {

        Object x = ((ExpressionBuilder.Constant)constant).value();

        GeometryExpressionBuilder builder = this.sqlbuilder.expression();

        if( x instanceof byte[] ) {

            return builder.string("\\x"+builder.bytearray_hex((byte[]) x))+"::bytea";

        }

        if( x instanceof Geometry ) {

            Geometry geometry = (Geometry) x;

            switch (builder.geometry_support_type()) {

                case EWKB:

                    return MessageFormat.format(

                            FORMAT_ST_GEOMFROMEWKB,

                            builder.string("\\x"+builder.bytearray_hex(GeometryUtils.toEWKB(geometry)))+"::bytea",

                            String.valueOf(builder.srs_id(geometry.getProjection()))

                    );

                case WKB:

                    return MessageFormat.format(

                            FORMAT_ST_GEOMFROMWKB,

                            builder.string("\\x"+builder.bytearray_hex(GeometryUtils.toWKB(geometry)))+"::bytea",

                            String.valueOf(builder.srs_id(geometry.getProjection()))

                    );

                case WKT:

                default:

                    return MessageFormat.format(

                            FORMAT_ST_GEOMFROMTEXT,

                            builder.string(GeometryUtils.toWKT(geometry)),

                            String.valueOf(builder.srs_id(geometry.getProjection()))

                    );

            }

        }

        return null;

    }

}

package org.gvsig.postgresql.dal.expressionbuilderformatter;

import java.text.MessageFormat;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import static org.gvsig.expressionevaluator.ExpressionBuilder.FUNCTION_IFNULL;

import org.gvsig.expressionevaluator.ExpressionBuilder.Function;

import org.gvsig.expressionevaluator.ExpressionBuilder.Value;

import org.gvsig.expressionevaluator.Formatter;

import org.gvsig.fmap.dal.SQLBuilder;

/**

 *

 * @author jjdelcerro

 */

class IfNull implements Formatter<Value> {

    private final Formatter<Value> formatter;

    private final SQLBuilder builder;

    public IfNull(SQLBuilder builder, Formatter<Value> formatter) {

        this.builder = builder;

        this.formatter = formatter;

    }

    @Override

    public boolean canApply(Value value) {

        if (value instanceof Function) {

            if (value instanceof ExpressionBuilder.Function) {

                return StringUtils.equalsIgnoreCase(FUNCTION_IFNULL, ((Function) value).name());

            }

        }

        return false;

    }

    @Override

    public String format(Value function) {

            List<Value> parameters = ((Function) function).parameters();

            String p1 = parameters.get(0).toString(formatter);

            String p2 = parameters.get(1).toString(formatter);

            String p3 = parameters.get(2).toString(formatter);

            String r = MessageFormat.format(

                    "CASE WHEN ({0}) IS NULL THEN ({1}) ELSE ({2}) END",

                    p1,

                    p3,

                    p2

            );

            return r;

    }

}

package org.gvsig.postgresql.dal.expressionbuilderformatter;

import org.apache.commons.lang3.StringUtils;

import static org.gvsig.expressionevaluator.ExpressionBuilder.FUNCTION_GETATTR;

import org.gvsig.expressionevaluator.ExpressionBuilder.Function;

import org.gvsig.expressionevaluator.ExpressionBuilder.Value;

import org.gvsig.expressionevaluator.ExpressionBuilder.Variable;

import org.gvsig.expressionevaluator.ExpressionBuilder.Constant;

import org.gvsig.expressionevaluator.Formatter;

import org.gvsig.fmap.dal.SQLBuilder;

/**

 *

 * @author jjdelcerro

 */

public class Getattr implements Formatter<Value> {

    private final SQLBuilder sqlbuilder;

    private final Formatter<Value> formatter;

    public Getattr(SQLBuilder sqlbuilder, Formatter<Value> formatter) {

        this.sqlbuilder = sqlbuilder;

        this.formatter = formatter;

    }

    @Override

    public boolean canApply(Value value) {

        if (value instanceof Function) {

            if( StringUtils.equalsIgnoreCase(FUNCTION_GETATTR, ((Function) value).name()) ) {

              Value obj = ((Function) value).parameters().get(0);

              Value attrname = ((Function) value).parameters().get(1);

              return obj instanceof Variable && attrname instanceof Constant;

            }

        }

        return false;

    }

    @Override

    public String format(Value function0) {

        Function function = (Function) function0;

        Variable obj = (Variable) function.parameters().get(0);

        Constant attrname = (Constant) function.parameters().get(1);

        return this.sqlbuilder.as_identifier(obj.name())+"."+this.sqlbuilder.as_identifier((String) attrname.value());

    }

}

/*

 * To change this license header, choose License Headers in Project Properties.

 * To change this template file, choose Tools | Templates

 * and open the template in the editor.

 */

package org.gvsig.postgresql.dal;

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

/**

 *

 * @author fdiaz

 */

public class FakePostgreSQLConnectionParameters extends FakeConnectionParameters implements PostgreSQLConnectionParameters {

    @Override

    public boolean getUseSSL() {

        return false;

    }

    @Override

    public int getMaxIdle() {

        return 8;

    }

    @Override

    public int getNetworkTimeout() {

        return 30000;

    }

}

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