Application: gvSIG desktop

    public void testSelectCountWhere2() {

        StringBuilder source = new StringBuilder();

        source.append("BEGIN");

        source.append("  tableName := 'countries';");

        source.append("  SELECT COUNT(*) FROM :tableName  ");

        source.append("    WHERE countries.LASTCENSUS > 0 ; ");

        source.append("END");

        Compiler compiler = createCompiler();

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

        link(code);

        checkEquals("testSelectCountWhere", "BLOCK(LET('tableName', 'countries'), SELECT_COUNT($HOSTEXPRESSION(\"tableName\", 'IN'), (\"countries\".\"LASTCENSUS\" > 0)))", code.toString());

    }

    public void testSelectWhere3() {

        StringBuilder source = new StringBuilder();

        source.append("BEGIN");

        source.append("  tableName := 'countries';");

        source.append("  SELECT * FROM :tableName  ");

        source.append("    WHERE countries.LASTCENSUS > 0 ; ");

        source.append("END");

        Compiler compiler = createCompiler();

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

        link(code);

        checkEquals("testSelectWhere3", "BLOCK(LET('tableName', 'countries'), SELECT(TUPLE(), $HOSTEXPRESSION(\"tableName\", 'IN'), (\"countries\".\"LASTCENSUS\" > 0), TUPLE(), TUPLE(), NULL))", code.toString());

    }

public class SelectCountFunction extends AbstractSelectFunction {

    public SelectCountFunction() {

        super(DALFunctions.GROUP_DATA_ACCESS,

                FUNCTION_SELECT_COUNT,

                Range.between(1, 2),

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

                + "The syntax is:\n\n"

                + "SELECT COUNT(*) 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 COUNT(*) FROM {{table}} WHERE filter ;",

                new String[]{

                    "table - Name of the table",

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

                "Long",

                false

        );

    }

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

    @Override

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

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

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

        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() + "'.");

            }

            if (where == null) {

                set = featureStore.getFeatureSet();

            } else {

                FeatureQuery query = featureStore.createFeatureQuery();

                Code where2 = removeOuterTablesReferences(interpreter, where);

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

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

                query.addFilter(filter);

                query.retrievesAllAttributes();

                set = featureStore.getFeatureSet(query);

            }

            return set.getSize();

        } catch (ExpressionRuntimeException ex) {

            throw ex;

        } catch (Exception ex) {

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

        } finally {

            DisposeUtils.disposeQuietly(set);

            DisposeUtils.disposeQuietly(featureStore);

package org.gvsig.expressionevaluator.impl.function.dataaccess;

import java.util.ArrayList;

import java.util.HashSet;

import java.util.List;

import java.util.Objects;

import java.util.Set;

import org.apache.commons.lang3.Range;

import org.apache.commons.lang3.StringUtils;

import org.apache.commons.lang3.tuple.ImmutablePair;

import org.apache.commons.lang3.tuple.Pair;

import org.gvsig.expressionevaluator.Code;

import org.gvsig.expressionevaluator.Code.Callable;

import org.gvsig.expressionevaluator.CodeBuilder;

import org.gvsig.expressionevaluator.Codes;

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

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

import org.gvsig.expressionevaluator.ExpressionRuntimeException;

import org.gvsig.expressionevaluator.ExpressionUtils;

import org.gvsig.expressionevaluator.Interpreter;

import org.gvsig.expressionevaluator.SymbolTable;

import org.gvsig.expressionevaluator.spi.AbstractFunction;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataManager;

import org.gvsig.fmap.dal.DataStore;

import static org.gvsig.fmap.dal.expressionevaluator.FeatureSymbolTable.SYMBOL_CURRENT_TABLE;

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

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

/**

 *

 * @author gvSIG Team

 */

@SuppressWarnings("UseSpecificCatch")

public abstract class AbstractSelectFunction extends AbstractFunction {

    protected AbstractSelectFunction(String group, String name, Range argc, String description, String template, String[] descriptionArgs, String returnType, boolean sqlCompatible) {

        super(group, name, argc, description, template, descriptionArgs, returnType, sqlCompatible);

    }

    protected AbstractSelectFunction(String group, String name, Range argc, String description, String template, String[] descriptionArgs, String returnType) {

        super(group, name, argc, description, template, descriptionArgs, returnType);

    }

    protected String getTableName(Codes args, int argn) {

        Code storeName_code = (Code) args.get(argn);

        if (storeName_code instanceof Code.Identifier) {

            return ((Code.Identifier) storeName_code).name();

        } else if (storeName_code instanceof Code.Constant) {

            Code.Constant storeName_const = (Code.Constant) storeName_code;

            if (storeName_const.value() instanceof CharSequence) {

                return storeName_const.value().toString();

            }

        }

        throw new ExpressionRuntimeException("Invalid table name (" + Objects.toString(storeName_code) + ") in function '" + this.name() + "'.");

    }

    protected Code getWhereCode(Codes args, int argn) {

        Code where = args.get(argn);

        if (where.code() == Code.CONSTANT) {

            if (((Code.Constant) where).value() == null) {

                where = null;

            }

        }

        return where;

    }

    protected Callable getTupleOrNull(Codes args, int argn) {

        Code code = args.get(argn);

        if (code.code() == Code.CONSTANT) {

            if (((Code.Constant) code).value() != null) {

                throw new ExpressionRuntimeException("Tupple or null expected in argument " + argn + " of function '" + this.name() + "'.");

            }

            return null;

        }

        if (code.code() != Code.CALLABLE) {

            throw new ExpressionRuntimeException("Tupple or null expected in argument " + argn + " of function '" + this.name() + "'.");

        }

        Callable caller = (Callable) code;

        if (!StringUtils.equalsIgnoreCase(FUNCTION_TUPLE, caller.name())) {

            throw new ExpressionRuntimeException("Tupple or null expected in argument " + argn + " of function '" + this.name() + "'.");

        }

        return caller;

    }

    protected Code removeOuterTablesReferences(Interpreter interpreter, Code where) {

        try {

            SymbolTable symbolTable = interpreter.getSymbolTable();

            TableAttributeHandler table = (TableAttributeHandler) symbolTable.value(SYMBOL_CURRENT_TABLE);

            List<Pair<Code, Code>> replaces = new ArrayList<>();

            CodeBuilder codeBuilder = ExpressionUtils.createCodeBuilder();

            final Set<Code> replacesToASkip = new HashSet<>();

            Code where2 = where.clone();

            where2.accept((Object o) -> {

                if (o == null) {

                    return;

                }

                Code code = (Code) o;

                switch (code.code()) {

                    case Code.CALLABLE:

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

                        if (StringUtils.equalsIgnoreCase(caller.name(), FUNCTION_GETATTR)) {

                            Codes args = caller.parameters();

                            Code arg0 = args.get(0);

                            Code arg1 = args.get(1);

                            replacesToASkip.add(arg1);

                            if (arg0 instanceof Code.Identifier && arg1 instanceof Code.Constant) {

                                Object tt = symbolTable.value(((Code.Identifier) arg0).name());

                                if (tt instanceof TableAttributeHandler

                                        && StringUtils.equalsIgnoreCase(((TableAttributeHandler) tt).getName(), table.getName())) {

                                    String columnName = Objects.toString(((Code.Constant) arg1).value(), null);

                                    if (columnName != null) {

                                        Object value = table.get(columnName);

                                        replaces.add(

                                                new ImmutablePair<>(

                                                        caller,

                                                        codeBuilder.constant(value)

                                                )

                                        );

                                    }

                                }

                            }

                        }

                        break;

                }

            });

            where2.accept((Object o) -> {

                if (o == null) {

                    return;

                }

                Code code = (Code) o;

                if (replacesToASkip.contains(code)) {

                    return;

                }

                switch (code.code()) {

                    case Code.IDENTIFIER:

                        Code.Identifier id = (Code.Identifier) code;

                        if (symbolTable.exists(id.name())) {

                            Object value = symbolTable.value(id.name());

                            replaces.add(

                                    new ImmutablePair<>(

                                            id,

                                            codeBuilder.constant(value)

                                    )

                            );

                        }

                        break;

                }

            });

            if (replaces.isEmpty()) {

                return where;

            }

            for (Pair<Code, Code> replace : replaces) {

                if (replace != null) {

                    where2.replace(replace.getLeft(), replace.getRight());

                }

            }

            return where2;

        } catch (Exception ex) {

            throw new ExpressionRuntimeException("Can't remove references to outer tables.", ex);

        }

    }

    protected DataStore getStore(String storeName) {

        DataManager dataManager = DALLocator.getDataManager();

        DataStore store = dataManager.getStoresRepository().getStore(storeName);

        return store;

    }

    protected FeatureStore getFeatureStore(String storeName) {

        DataManager dataManager = DALLocator.getDataManager();

        DataStore store = dataManager.getStoresRepository().getStore(storeName);

        if (store instanceof FeatureStore) {

            return (FeatureStore) store;

        }

        return null;

    }

}

public class SelectFunction

        extends AbstractSelectFunction

        implements Optimizer.FunctionOptimizer {

    public SelectFunction() {

        super(DALFunctions.GROUP_DATA_ACCESS,

                FUNCTION_SELECT,

                Range.is(6),

                "Returns a list of features of the table by applying the filter, order and limit indicated.\n"

                + "The syntax is:\n\n"

                + "SELECT * FROM table WHERE boolean_expression ORDER BY order_column LIMIT limit;\n\n"

                + "Indicate a filter expression with WHERE, an order or LIMIT is optional.\n"

                + "You can use an asterisk or enter the column names you want to retrieve separated by commas.\n"

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

                "SELECT * FROM table WHERE boolean_expression ORDER BY order_column LIMIT limit;",

                new String[]{

                    "column_names/asterisk - Names of the columns table to retrieve.",

                    "table_name - Name of the table",

                    "filter - boolean expression to apply as filter",

                    "order_column - the order used to retrieve the features. It is a list of column names separated by a comma. The column name can optionally be followed by ASC or DESC to indicate whether the order should be ascending or descending.",

                    "limit - Maximum number of features to return"

                },

                "List",

                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 COLUMNS = 0;

    private static final int TABLE = 1;

    private static final int WHERE = 2;

    private static final int ORDER = 3;

    private static final int ORDER_MODE = 4;

    private static final int LIMIT = 5;

    @Override

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

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

        Code columns = getTupleOrNull(args, COLUMNS);

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

        Number limit = (Number) getObject(interpreter, args, LIMIT);

        Callable order = getTupleOrNull(args, ORDER);

        Callable order_mode = getTupleOrNull(args, ORDER_MODE);

        FeatureQueryOrder queryOrder = null;

        if (order != null || order_mode != null) {

            for (int n = 0; n < order.parameters().size(); n++) {

                String member = (String) interpreter.run(order.parameters().get(n));

                Boolean mode = (Boolean) interpreter.run(order_mode.parameters().get(n));

                if (queryOrder == null) {

                    queryOrder = new DefaultFeatureQueryOrder();

                queryOrder.add(member, mode);

            }

        FeatureStore featureStore = null;

        try {

            featureStore = this.getFeatureStore(storeName);

            if (featureStore == null) {

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

            }

            List<Feature> features;

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

            }

            if (queryOrder != null) {

                query.getOrder().copyFrom(queryOrder);

            }

            if (limit != null) {

                query.setLimit(limit.longValue());

            }

            // FIXME: add columns to query.addAttributeName() 

            query.retrievesAllAttributes();

            features = featureStore.getFeatures(query);

            return features;

        } catch (ExpressionRuntimeException ex) {

            throw ex;

        } catch (Exception ex) {

            throw new ExpressionRuntimeException("Problems calling '" + FUNCTION_SELECT + "' function", ex);

    @Override

    public Code optimize(Optimizer optimizer, Callable caller) {

        return caller; // Don't optimize SELECT

    }

            args.add(table);

            args.add(table);

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

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