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.expressionevaluator.impl.function.dataaccess;

import org.apache.commons.lang3.Range;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.Code;

import org.gvsig.expressionevaluator.Codes;

import org.gvsig.expressionevaluator.ExpressionBuilder;

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

import org.gvsig.expressionevaluator.ExpressionEvaluator;

import org.gvsig.expressionevaluator.ExpressionRuntimeException;

import org.gvsig.expressionevaluator.Interpreter;

import org.gvsig.expressionevaluator.impl.DALFunctions;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataManager;

import static org.gvsig.fmap.dal.DataManager.FUNCTION_SELECT_COUNT;

import org.gvsig.fmap.dal.SQLBuilder;

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

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

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

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

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

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

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

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

import org.gvsig.fmap.dal.impl.expressionevaluator.DefaultFeatureExpressionEvaluator;

import org.gvsig.tools.dispose.DisposeUtils;

/**

 *

 * @author jjdelcerro

 */

@SuppressWarnings("UseSpecificCatch")

public class SelectAggregateFunction extends AbstractSelectFunction {

    public static String FUNCTION_SELECT_AGGREGATE = "SELECT_AGGREGATE";

    public SelectAggregateFunction() {

        super(DALFunctions.GROUP_DATA_ACCESS,

                FUNCTION_SELECT_AGGREGATE,

                Range.is(4),

                "Returns the aggregate function of features of the table by applying the filter indicated.\n"

                + "The syntax is:\n\n"

                + "SELECT aggregate_function(column1) FROM table WHERE boolean_expression;\n\n"

                + "Indicate a filter expression with WHERE is optional.\n"

                + "The SELECT statement must always end with a semicolon.",

                "SELECT sum({{column1}}) FROM table WHERE filter ;",

                new String[]{

                    "table - Name of the table",

                    "filter - boolean expression with the filter to apply",

                    "aggregate_function - aggregate function to use. Valid functions are SUM, COUNT, MAX, MIN, AVG",

                    "column1 - name of column to use in aggregate function",

                },

                "Object",

                true

        );

    }

    @Override

    public boolean isHidden() {

        return false;

    }

    @Override

    public boolean allowConstantFolding() {

        return false;

    }

    @Override

    public boolean useArgumentsInsteadObjects() {

        return true;

    }

    @Override

    public Object call(Interpreter interpreter, Object[] args) throws Exception {

        throw new UnsupportedOperationException();

    }

    private static final int TABLE = 0;

    private static final int WHERE = 1;

    private static final int AGGREGATE_FUNCTION = 2;

    private static final int AGGREGATE_COLUMN = 3;

    @Override

    public Object call(Interpreter interpreter, Codes args) throws Exception {

        String storeName = this.getIdentifier(args, TABLE);

        Code where = this.getWhereCode(args, WHERE);

        String aggregateFunction = this.getIdentifier(args, AGGREGATE_FUNCTION);

        String aggregateColumn = this.getIdentifier(args, AGGREGATE_COLUMN);

        FeatureStore featureStore = null;

        FeatureSet set = null;

        try {

            featureStore = this.getFeatureStore(storeName);

            if (featureStore == null) {

                throw new ExpressionRuntimeException("Cant locate the feature store '" + storeName + "' in function '" + this.name() + "'.");

            }

            FeatureQuery query = featureStore.createFeatureQuery();

            if (where != null) {

                Code where2 = removeOuterTablesReferences(interpreter, where);

                ExpressionEvaluator filter = new DefaultFeatureExpressionEvaluator(where2.toString());

                filter.toSymbolTable().addSymbolTable(interpreter.getSymbolTable());

                query.addFilter(filter);

            }

            query.retrievesAllAttributes();

            query.addAggregate(aggregateFunction, aggregateColumn);

            Feature f = featureStore.findFirst(query);

            if (f == null){

                return null;

            }

            return f.get(aggregateColumn);

        } catch (ExpressionRuntimeException ex) {

            throw ex;

        } catch (Exception ex) {

            throw new ExpressionRuntimeException("Problems calling '" + this.name() + "' function", ex);

        } finally {

            DisposeUtils.disposeQuietly(set);

            DisposeUtils.disposeQuietly(featureStore);

        }

    }

    @Override

    public ExpressionBuilder.Value toValue(ExpressionBuilder builder, Codes args) {

        try {

            SQLBuilder sqlBuilder = (SQLBuilder) builder.getProperty(PROP_SQLBUILDER);

            if(sqlBuilder == null){

                return super.toValue(builder, args);

            }

            FeatureType featureType = null;

            SQLBuilder.SelectBuilder select = sqlBuilder.createSelectBuilder();

            String builderTableName = (String) builder.getProperty(SQLBuilder.PROP_TABLENAME);

            String storeName = this.getIdentifier(args, TABLE);

            Code where = this.getWhereCode(args, WHERE);

            String aggregateFunction = this.getIdentifier(args, AGGREGATE_FUNCTION);

            String aggregateColumn = this.getIdentifier(args, AGGREGATE_COLUMN);

            if (storeName != null) {

                select.from().table().name(storeName);

            }

            SQLBuilder.TableNameBuilder table = select.from().table();

            String tableName = table.getName();

            select.column(aggregateColumn).value(builder.function(aggregateFunction, builder.column(aggregateColumn)));

            if (where != null) {

                ExpressionBuilder.Value value = where.toValue(builder);

                select.where().value(value);

                sqlBuilder.setProperties(value, null, SQLBuilder.PROP_ADD_TABLE_NAME_TO_COLUMNS, true);

            }

            if (featureType == null) {

                if (StringUtils.equalsIgnoreCase(builderTableName, tableName)) {

                    featureType = (FeatureType) builder.getProperty(SQLBuilder.PROP_FEATURE_TYPE);

                } else {

                    DataManager dataManager = DALLocator.getDataManager();

                    featureType = dataManager.getStoresRepository().getFeatureType(tableName);

                }

            }

            sqlBuilder.setProperties(

                    select,

                    null,

                    PROP_FEATURE_TYPE, featureType,

                    PROP_TABLE, table

            );

            return builder.group(select);

        } catch (Exception ex) {

            return super.toValue(builder, args);

        }

    }

}

        String storeName = this.getIdentifier(args, TABLE);

            String storeName = this.getIdentifier(args, TABLE);

    protected String getIdentifier(Codes args, int argn) {

        String storeName = this.getIdentifier(args, TABLE);

        String storeName = this.getIdentifier(args, TABLE);

            String storeName = this.getIdentifier(args, TABLE);

        String storeName = this.getIdentifier(args, TABLE);

import static org.gvsig.expressionevaluator.impl.function.dataaccess.SelectAggregateFunction.FUNCTION_SELECT_AGGREGATE;

    private static class SelectAggregateArgsBuilder implements ArgsBuilder {

        public SelectAggregateArgsBuilder() {

        }

        @Override

        public String toString() {

            return "select_aggregate_args()";

        }

        @Override

        public Codes build(StatementContext context) {

            context.trace(this.toString() + ".build");

            CodeBuilder codeBuilder = context.getCodeBuilder();

            BaseCodes args = (BaseCodes) codeBuilder.args();

            Code table = context.getCode("table");

            Code where = context.getCode("where");

            Code aggregateFunction = context.getCode("AGGREGATE_FUNCTION");

            Code aggregateColumn = context.getCode("AGGREGATE_COLUMN");

            args.add(table);

            if (where == null) {

                args.add(codeBuilder.constant(null));

            } else {

                args.add(where);

            }

            args.add(aggregateFunction);

            args.add(aggregateColumn);

            return args;

        }

    }

            theGrammar.addStatement(createSelectAggregateStatement(theGrammar));

    private Statement createSelectAggregateStatement(Grammar theGrammar) {

        Statement stmt;

//

//      SELECT COUNT(*) FROM table_name WHERE expression

//        

        theGrammar.addReservedWord("SELECT");

        theGrammar.addReservedWord("FROM");

        theGrammar.addReservedWord("WHERE");

        stmt = theGrammar.createStatement("SELECT_COUNT");

        stmt.addRule(stmt.require_any_token("SELECT"));

        stmt.addRule(stmt.require_any_token("COUNT","SUM", "MIN","MAX", "AVG").capture_as("AGGREGATE_FUNCTION"));

        stmt.addRule(stmt.require_any_token("("));

        stmt.addRule(stmt.require_identifier().capture_as("AGGREGATE_COLUMN"));

        stmt.addRule(stmt.require_any_token(")"));

        stmt.addRule(stmt.require_any_token("FROM"));

        stmt.addRule(stmt.require_expression().capture_as("TABLE"));

        stmt.addRule(stmt.optional_any_token("WHERE")

                .addRuleOnTrue(stmt.require_expression().capture_as("WHERE"))

        );

        stmt.addRule(stmt.optional_any_token(";"));

        stmt.code(

                FUNCTION_SELECT_AGGREGATE,

                new SelectAggregateArgsBuilder()

        );

        return stmt;

    }

import org.gvsig.expressionevaluator.impl.function.dataaccess.SelectAggregateFunction;

        this.addFunction(new SelectAggregateFunction());

    public void testSelectAggregate() {

        StringBuilder source = new StringBuilder();

        source.append("SELECT sum(\"TOTAL_MUERTOS\") FROM \"ARENA2_ACCIDENTES\";");

        Compiler compiler = createCompiler();

        Code code = compiler.compileExpression(source.toString());

        link(code);

        checkEquals("testSelect", "SELECT_AGGREGATE(\"ARENA2_ACCIDENTES\", NULL, 'sum', 'TOTAL_MUERTOS')", code.toString());

    }