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

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.dal.store.jdbc2.spi.expressionbuilder.formatters.ComputedAttribute;

import org.gvsig.fmap.geom.DataTypes;

import org.gvsig.tools.dynobject.DynField;

import org.gvsig.tools.evaluator.Evaluator;

import org.gvsig.tools.util.ContainerUtils;

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

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

        }

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

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

                columns.add(setType.getAttributeDescriptor(attrName));

            }

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

        }

        //Si hay especificado un offset (se esta paginando) siempre deberemos ordenar por un campo con valores unicos.

        //Anadiremos la clave primaria siempre en este caso para asegurarnos de que el orden de los registros es siempre el mismo.

//        if (!select.has_order_by()) {

            // Si no tenemos order by comprobamos si lo necesitamos y lo a?adimos.

            if (offset > 0 || (offset == 0 && limit > 0)) {

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

                if (select.has_group_by()) {

                    ExpressionBuilder.Value group = select.getGroups().get(0);

//                  if(!(group instanceof ExpressionBuilder.Function)) {

//                      expbuilder.getattr(this.table.getTable(), group.XXX);

//                  }

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

                } else 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 {

                    for (String attrName : primaryKeys) {

                        // Se precisa indicar un orden para usar OFFSET.

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

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

                        }

                    }

                }

            }

//        }

        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;

    }

    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;

    }

}