Application: gvSIG desktop

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2020 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.fmap.dal.store.jdbc2.spi.operations;

import java.sql.Connection;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.apache.commons.lang3.ArrayUtils;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.Code;

import org.gvsig.expressionevaluator.Expression;

import org.gvsig.expressionevaluator.ExpressionBuilder;

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

import org.gvsig.expressionevaluator.ExpressionUtils;

import org.gvsig.fmap.dal.SQLBuilder.SelectBuilder;

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

import org.gvsig.fmap.dal.expressionevaluator.FeatureAttributeEmulatorExpression;

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

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

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

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

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

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

import org.gvsig.fmap.dal.feature.FeatureQueryOrder.FeatureQueryOrderMember;

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.ResulSetControler.ResultSetEntry;

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

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

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

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

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

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

import org.gvsig.fmap.geom.DataTypes;

import org.gvsig.tools.dynobject.DynField;

import org.gvsig.tools.evaluator.Evaluator;

import org.gvsig.tools.lang.CloneableUtils;

public class ResultSetForSetProviderOperation extends AbstractConnectionOperation {

    private final TableReference table;

    private final String baseFilter;

    private final String baseOrder;

    private final FeatureType storeType;

    private final FeatureType setType;

    private final FeatureQuery query;

    private final long limit;

    private final long offset;

    private final int fetchSize;

    public ResultSetForSetProviderOperation(

            JDBCHelper helper,

            TableReference table,

            String baseFilter,

            String baseOrder,

            FeatureQuery query,

            FeatureType storeType,

            FeatureType setType,

            long limit,

            long offset,

            int fetchSize

    ) {

        super(helper);

        this.table = table;

        this.baseFilter = baseFilter;

        this.baseOrder = baseOrder;

        this.storeType = storeType;

        this.setType = setType;

        this.query = query;

        this.limit = limit;

        this.offset = offset;

        this.fetchSize = fetchSize;

    }

    @Override

    protected Object perform_operation() throws Exception {

        ResultSetEntry rs = createResultSet();

        return rs;

    }

    @Override

    public Object perform(Connection conn) throws DataException {

        throw new UnsupportedOperationException("Not supported yet.");

    }

    public String getSQL() {

        List<FeatureAttributeDescriptor> columns = new ArrayList<>();

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        String sql = this.getSQL(sqlbuilder, columns, null);

        return sql;

    }

    public String getSQL(

            JDBCSQLBuilderBase sqlbuilder,

            List<FeatureAttributeDescriptor> columns,

            List<String> extraColumnNames

    ) {

        double tolerance = -1; //query.getScale(); 

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        SelectBuilder select = sqlbuilder.select();

        ArrayList<ExpressionBuilder.Value> valuesToRemoveFeatureType = new ArrayList<>();

        if( shouldUseACustomSelect() ) {

            String sql = table.getSubquery();

            if( StringUtils.containsAny(sql, "${where_filter}", "${and_filter}") ) {

                Evaluator filter = query.getFilter();

                if (filter != null) {

                    String sqlfilter = filter.getSQL();

                    if (!StringUtils.isEmpty(sqlfilter)) {

                        if (this.helper.supportFilter(this.storeType, filter)) {

                            select.where().set(expbuilder.toValue(sqlfilter));

                        }

                    }

                }

                if (!StringUtils.isEmpty(baseFilter)) {

                    select.where().and(expbuilder.toValue(baseFilter));

                }

            }

            if( StringUtils.containsAny(sql, "${order_by_orderspec}", "${comma_orderspec}") ) {

                FeatureQueryOrder order = query.getOrder();

                if (order != null) {

                    for (FeatureQueryOrderMember member : order.members()) {

                        String attrName = member.getAttributeName();

                        ExpressionBuilder.Variable col = expbuilder.column(attrName);

                        select.order_by().value(col).ascending(member.getAscending());

                    }

                }

            }

            if( select.has_where() || select.has_order_by() ) {

                sqlbuilder.setProperties(

                        null,

                        PROP_FEATURE_TYPE, this.storeType,

                        PROP_TABLE, table,

                        PROP_SYMBOLTABLE, this.query==null? null:this.query.getSymbolTable(),

                        PROP_JDBCHELPER, this.helper,

                        PROP_QUERY, this.query

                );

                for (ExpressionBuilder.Value value : valuesToRemoveFeatureType) {

                    value.setProperty(PROP_FEATURE_TYPE, null);

                }

                if( select.has_where()) {

                    String s = select.where().toString();

                    sql = StringUtils.replace(sql, "${where_filter}", "WHERE " + s);

                    sql = StringUtils.replace(sql, "${and_filter}", "AND (" + s + ")");

                }

                if( select.has_order_by() ) {

                    String s = select.order_by().toString();

                    sql = StringUtils.replace(sql, "${order_by_orderspec}", "ORDER BY " + s);

                    sql = StringUtils.replace(sql, "${comma_orderspec}", ", "+ s);

                }

            }

            for (FeatureAttributeDescriptor attr : storeType) {

                columns.add(attr);

            }

            return sql;

        }

        Map<String, EditableFeatureAttributeDescriptor> allExtraColumns = new HashMap<>();

        for (EditableFeatureAttributeDescriptor column : this.setType.getExtraColumns().getColumns()) {

            allExtraColumns.put(column.getName(), column);

        }

        if(query != null){

            for (EditableFeatureAttributeDescriptor column : this.query.getExtraColumn().getColumns()) {

                allExtraColumns.put(column.getName(), column);

            }

        }

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

        for (FeatureAttributeDescriptor attr : storeType.getPrimaryKey()) {

            primaryKeys.add(attr.getName());

        }

        List<String> forcedColumns = new ArrayList<>(primaryKeys);

        String[] constantsAttributeNames = null;

        if (query != null && query.hasConstantsAttributeNames()) {

            constantsAttributeNames = query.getConstantsAttributeNames();

        }

        for (FeatureAttributeDescriptor attr : setType) {

            if (attr.isComputed()) {

//              if(StringUtils.isNotBlank(System.getenv("ENABLE_COMPUTED_SQL_ATTR"))) { 

                if (attr.getRelationType() == DynField.RELATION_TYPE_NONE) {

                    FeatureAttributeEmulator attrEmulator = attr.getFeatureAttributeEmulator();

                    if (attrEmulator instanceof FeatureAttributeEmulatorExpression) {

                        FeatureAttributeEmulatorExpression x = (FeatureAttributeEmulatorExpression) attrEmulator;

                        Expression exp = x.getExpression();

                        if (query != null && query.hasGroupByColumns()) {

                            String aggregate = query.getAggregate(this.table.getTable(), attr.getName());

                            if (this.query.isAGroupByColumn(attr.getName())) {

                                if (!select.has_column(attr.getName())) {

                                    select.column().value(exp.getCode().toValue()).as(attr.getName());

                                }

                                if (extraColumnNames != null && !extraColumnNames.contains(attr.getName())) {

                                    extraColumnNames.add(attr.getName());

                                }

                            } else if (aggregate == null) {

                                select.column().value(expbuilder.constant(null)).as(attr.getName());

                            } else {

                                String fn = this.query.getAggregateFunctions().get(attr.getName());

                                ExpressionBuilder.Function aggregateExp = expbuilder.function(fn, exp.getCode().toValue());

                                if (!select.has_column(attr.getName())) {

                                    select.column().value(aggregateExp).as(attr.getName());

                                }

                                if (extraColumnNames != null && !extraColumnNames.contains(attr.getName())) {

                                    extraColumnNames.add(attr.getName());

                                }

                            }

                        } else {

                            if (exp != null && !exp.isEmpty() && this.helper.supportExpression(setType, exp.getPhrase())) {

                                Code code = exp.getCode();

                                select.column()

                                        .value(code.toValue(expbuilder))

                                        .as(attr.getName());

                                if (extraColumnNames != null && !extraColumnNames.contains(attr.getName())) {

                                    extraColumnNames.add(attr.getName());

                                }

                            }

                        }

                    }

                }

//              }

                continue;

            }

            if (ArrayUtils.contains(constantsAttributeNames, attr.getName())) {

                continue;

            }

            if (attr.isPrimaryKey()) {

                forcedColumns.remove(attr.getName());

            }

            if (query != null && query.hasGroupByColumns()) {

                String aggregate = query.getAggregate(this.table.getTable(), attr.getName());

                if (this.query.isAGroupByColumn(attr.getName())) {

                    select.column().name(attr.getName());

                } else if (aggregate == null) {

                    select.column().value(expbuilder.constant(null)).as(attr.getName());

                } else {

                    select.column()

                            .value(ExpressionUtils.compile(aggregate).toValue(expbuilder))

                            .as(attr.getName());

                }

            } else {

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

                    select.column().name(attr.getName()).as_geometry();

                    //                if( tolerance<=0 || !sqlbuilder.getConfig().has_functionality(Config.ST_Simplify)) {

                    //                    select.column().name(attr.getName()).as_geometry();

                    //                } else {

                    //                    select.column().value(

                    //                        sqlbuilder.ST_Simplify( 

                    //                            sqlbuilder.column(attr.getName()),

                    //                            sqlbuilder.constant(tolerance)

                    //                        )

                    //                    ).as_geometry();

                    //                }

                } else {

                    select.column().name(attr.getName());

                }

            }

            columns.add(attr);

        }

        if (query != null && query.hasGroupByColumns()) {

            for (Map.Entry<String, String> entry : query.getAggregateFunctions().entrySet()) {

                Expression exp;

                FeatureAttributeDescriptor attr = allExtraColumns.get(entry.getKey());

                if (attr == null) {

                    attr = this.setType.getAttributeDescriptorFromAll(entry.getKey());

                    if (attr == null) {

                        exp = ExpressionUtils.createExpression(entry.getKey());

                        Code code = exp.getCode();

                        if (!(code instanceof Code.Callable)) {

                            throw new RuntimeException("Not able to use aggregate function with this expression(1): " + entry.getKey());

                        }

                        Code.Callable callable = (Code.Callable) code;

                        if (!callable.name().equalsIgnoreCase(FUNCTION_LET)) {

                            throw new RuntimeException("Not able to use aggregate function with this expression(2): " + entry.getKey());

                        }

                        String name = ((Code.Identifier) callable.parameters().get(0)).name();

                        ExpressionBuilder.Value aggregate = callable.parameters().get(1).toValue();

                        ExpressionBuilder.Function aggregateExp = expbuilder.function(entry.getValue(), aggregate);

                        select.remove_column(name);

                        select.column().value(aggregateExp).as(name);

                    }

                } else {

                    exp = ((FeatureAttributeEmulatorExpression) attr.getFeatureAttributeEmulator()).getExpression();

                    ExpressionBuilder.Function aggregateExp = expbuilder.function(entry.getValue(), exp.getCode().toValue());

                    if (!select.has_column(attr.getName())) {

                        select.column().value(aggregateExp).as(attr.getName());

                    }

                    if (extraColumnNames != null && !extraColumnNames.contains(attr.getName())) {

                        extraColumnNames.add(attr.getName());

                    }

                }

            }

            for (String attrName : query.getGroupByColumns()) {

                if (allExtraColumns.get(attrName) != null) { //from setType and query

                    EditableFeatureAttributeDescriptor attr = allExtraColumns.get(attrName);

                    ExpressionBuilder.Variable col = expbuilder.column(attrName);

                    select.group_by(col);

                    Expression exp = ((FeatureAttributeEmulatorExpression) attr.getFeatureAttributeEmulator()).getExpression();

                    if (!select.has_column(attrName)) {

                        select.column().value(exp.getCode().toValue()).as(attrName);

                    }

                    if (extraColumnNames!=null && !extraColumnNames.contains(attr.getName())) {

                        extraColumnNames.add(attrName);

                    }

                } else if (setType.get(attrName) != null && setType.getAttributeDescriptor(attrName).isComputed()) {

                    FeatureAttributeDescriptor attr = setType.getAttributeDescriptor(attrName);

                    ExpressionBuilder.Variable col = expbuilder.column(attrName);

                    select.group_by(col);

                    Expression exp = ((FeatureAttributeEmulatorExpression) attr.getFeatureAttributeEmulator()).getExpression();

                    if (!select.has_column(attrName)) {

                        select.column().value(exp.getCode().toValue()).as(attrName);

                    }

                    if (extraColumnNames!=null && !extraColumnNames.contains(attr.getName())) {

                        extraColumnNames.add(attrName);

                    }

                } else if (setType.get(attrName) == null) {

                    try {

                        Code code = ExpressionUtils.compile(attrName);

                        select.group_by(code.toValue());

                    } catch (Exception ex) {

                        throw new RuntimeException("Not able to create column by expression in groupby query", ex);

                    }

                } else {

                    ExpressionBuilder.Function atrcolumn = expbuilder.getattr(this.table.getTable(), attrName);

                    select.group_by(atrcolumn);

                }

            }

        } else {

            for (String attrName : forcedColumns) {

                select.column().name(attrName);

                FeatureAttributeDescriptor attr = setType.getAttributeDescriptor(attrName);

                // Ojo, que cuando estamos pintando una vista NO TENEMOS LA PK y attr vale null!!!!

                // No se si deberiamos forzar a que cuando se crea un subfeaturetype se meta siempre

                // la pk, o simplemente nunca asumir que la vamos a tener.

                if( attr!=null ) {

                    columns.add(attr);

                }

            }

            if (this.query != null) {

                FeatureExtraColumns extraColumns = this.query.getExtraColumn();

                if (extraColumns != null && !extraColumns.isEmpty()) {

                    for (EditableFeatureAttributeDescriptor attr : extraColumns.getColumns()) {

                        if (!attr.isComputed()) {

                            continue;

                        }

                        FeatureAttributeEmulator attrEmulator = attr.getFeatureAttributeEmulator();

                        if (attrEmulator instanceof FeatureAttributeEmulatorExpression) {

                            FeatureAttributeEmulatorExpression x = (FeatureAttributeEmulatorExpression) attrEmulator;

                            Expression exp = x.getExpression();

                            if (exp != null && !exp.isEmpty() && this.helper.supportExpression(setType, exp.getPhrase())) {

                                Code code = exp.getCode();

                                select.column()

                                        .value(code.toValue(expbuilder))

                                        .as(attr.getName());

                                if (extraColumnNames!=null && !extraColumnNames.contains(attr.getName())) {

                                    extraColumnNames.add(attr.getName());

                                }

                            }

                        }

                    }

                }

            }

        }

        select.from().table()

                .database(this.table.getDatabase())

                .schema(this.table.getSchema())

                .name(this.table.getTable());

        select.from().subquery(this.table.getSubquery());

        Evaluator filter = query == null ? null : query.getFilter();

        if (filter != null) {

            String sqlfilter = filter.getSQL();

            if (!StringUtils.isEmpty(sqlfilter)) {

                if (this.helper.supportFilter(this.storeType, filter)) {

                    select.where().set(expbuilder.toValue(sqlfilter));

                }

            }

        }

        if (!StringUtils.isEmpty(baseFilter)) {

            select.where().and(expbuilder.toValue(baseFilter));

        }

        FeatureQueryOrder order = query == null ? null : query.getOrder();

        if (order != null) {

            for (FeatureQueryOrderMember member : order.members()) {

                String attrName = member.getAttributeName();

                if (member.hasEvaluator()) {

                    String sqlorder = member.getEvaluator().getSQL();

                    select.order_by()

                            .value(expbuilder.toValue(sqlorder))

                            .ascending(member.getAscending());

                } else {

                    if (allExtraColumns.get(attrName) != null) {

                        Expression exp = ((FeatureAttributeEmulatorExpression) allExtraColumns.get(attrName).getFeatureAttributeEmulator()).getExpression();

                        if (!select.has_column(attrName)) {

                            select.column().value(exp.getCode().toValue()).as(attrName);

                        }

                        if (extraColumnNames!=null && !extraColumnNames.contains(attrName)) {

                            extraColumnNames.add(attrName);

                        }

                    } else if (setType.get(attrName) != null && setType.getAttributeDescriptor(attrName).isComputed()) {

                        Expression exp = ((FeatureAttributeEmulatorExpression) setType.getAttributeDescriptor(attrName).getFeatureAttributeEmulator()).getExpression();

                        if (!select.has_column(attrName)) {

                            select.column().value(exp.getCode().toValue()).as(attrName);

                        }

                        if (extraColumnNames!=null && !extraColumnNames.contains(attrName)) {

                            extraColumnNames.add(attrName);

                        }

                    }

                    ExpressionBuilder.Variable col = expbuilder.column(attrName);

                    // En el groupBy no queremos que se sustituya el nombre del campo calculado

                    // por su expresion. Se encarga el formater y lo evitamos quitandole el ftype

                    // al value.

                    valuesToRemoveFeatureType.add(col);

                    select.order_by().value(col).ascending(member.getAscending());

//                    select.order_by()

//                            .column(member.getAttributeName())

//                            .ascending(member.getAscending());

                }

            }

        }

        if (!StringUtils.isEmpty(baseOrder)) {

            select.order_by().custom(baseOrder);

        }

        if (select.has_group_by()) { // && isPaginated()) {

            // Cuando paginamos debemos ordenar por las columnas del groupby.

            // Ordenamos siempre para obtener el mismo resultado cuando paginamos

            // y no paginamos.

            for (ExpressionBuilder.Value group : select.getGroups()) {

                if (select.getOrderBy(group) == null) {

                    ExpressionBuilder.Value v = (ExpressionBuilder.Value) CloneableUtils.cloneQuietly(group);

                    select.order_by().value(v).ascending();

                    valuesToRemoveFeatureType.add(v);

                }

            }

        }

        if (primaryKeys.isEmpty()) {

            // Muy probablemente si no tiene pk sea una vista, asi que 

            // pasaremos de ordenar y esperemos que la vista este ya ordenada.

            select.disable_check_order_and_offset();

        } else {

            // Siempre ordenamos por la clave primaria

            for (String attrName : primaryKeys) {

                if (select.getOrderBy(attrName) == null) {

                    select.order_by().column(attrName).ascending();

                }

            }

        }

        if (limit > 0) {

            select.limit(limit);

        } else {

            select.limit(query == null ? null : query.getLimit());

        }

        if (offset > 0) {

            select.offset(offset);

        }

        sqlbuilder.setProperties(

                null,

                PROP_FEATURE_TYPE, this.storeType,

                PROP_TABLE, table,

                PROP_SYMBOLTABLE, this.query==null? null:this.query.getSymbolTable(),

                PROP_JDBCHELPER, this.helper,

                PROP_QUERY, this.query

        );

        for (ExpressionBuilder.Value value : valuesToRemoveFeatureType) {

            value.setProperty(PROP_FEATURE_TYPE, null);

        }

        this.helper.expandCalculedColumns(sqlbuilder);

        this.helper.processSpecialFunctions(sqlbuilder, storeType, extraColumnNames);

        String sql = sqlbuilder.toString();

        return sql;

    }

    private boolean isPaginated() {

        // No tengo claro que (offset==0 && limit>0) sea lo mas correcto,

        // Pero cuando se va a paginar y se pide la primera pagina offset es

        // 0 y limit>0, y si no ordenamos ya esa primera pagina los resultados

        // que se obtienen no son correctos, ya que la primera pagina se saca

        // sin ordenar y el resto ordenadas.

        // Probablemente deberiamos tener alguna otra forma de detectar que

        // estamos paginanado ya que asi no distinguimo si solo queremos 

        // obtener los primeros elementos sin importarnos su orden.

        return (offset > 0 || (offset == 0 && limit > 0));

    }

    public ResultSetEntry createResultSet() throws DataException {

        List<FeatureAttributeDescriptor> columns = new ArrayList<>();

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

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        String sql = this.getSQL(sqlbuilder, columns, extraColumnNames);

        ResultSetEntry resultSetEntry = this.helper.getResulSetControler().create(

                sql, fetchSize,

                columns.toArray(new FeatureAttributeDescriptor[columns.size()]),

                extraColumnNames.toArray(new String[extraColumnNames.size()])

        );

        return resultSetEntry;

    }

    private boolean shouldUseACustomSelect() {

        if( !table.hasSubquery() ) {

            return false;

        }

        if( this.query == null ) {

            return false;

        }

        if( this.query.isUseSubquery() ) {

            return false;

        }

        if( this.query.hasGroupByColumns() ) {

            return false;

        }

        if( this.query.hasAggregateFunctions() ) {

            return false;

        }        

        if( !this.query.hasFilter() && !this.query.hasOrder() ) {

            return true;

        }

//        si el filtro es incompatible con la BBDD

//            return false

//        si el orden es incompatible con la BBDD

//            return false

//        si filtro u orden usan la funcion foreing_value 

//            return false;

        return true;

    }

}