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.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.fmap.dal.SQLBuilder;

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

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException;

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

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

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

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

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 static org.gvsig.fmap.dal.store.jdbc2.spi.operations.ResultSetForSetProviderOperation.getAllExtraColumns;

import static org.gvsig.fmap.dal.store.jdbc2.spi.operations.ResultSetForSetProviderOperation.process2_ComputedFields;

import static org.gvsig.fmap.dal.store.jdbc2.spi.operations.ResultSetForSetProviderOperation.process3_Where;

import static org.gvsig.fmap.dal.store.jdbc2.spi.operations.ResultSetForSetProviderOperation.process4_Aggregates;

import static org.gvsig.fmap.dal.store.jdbc2.spi.operations.ResultSetForSetProviderOperation.process5_GroupBys;

public class CountOperation extends AbstractConnectionOperation {

    private final TableReference table;

    private final String baseFilter;

    private final FeatureQuery query;

    private final FeatureType featureType;

    public CountOperation(

            JDBCHelper helper

        ) {

        this(helper, null, null, null, null);

    }

    public CountOperation(

            JDBCHelper helper,

            FeatureType featureType,

            TableReference table,

            String baseFilter,

            FeatureQuery query

        ) {

        super(helper);

        this.featureType = featureType;

        this.table = table;

        this.baseFilter = baseFilter;

        this.query = query;

    }

    @Override

    public final Object perform(JDBCConnection conn) throws DataException {

        return this.count(conn);

    }

    public String getSQL() {

        JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        expbuilder.setProperty(PROP_FEATURE_TYPE, this.featureType);

        expbuilder.setProperty(PROP_TABLE, table);

        expbuilder.setProperty(PROP_TABLENAME, table.getTable());

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

        expbuilder.setProperty(PROP_JDBCHELPER, this.helper);

        expbuilder.setProperty(PROP_QUERY, this.query);

        SelectBuilder select = sqlbuilder.select();

        select.from().table()

                .database(this.table.getDatabase())

                .schema(this.table.getSchema())

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

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

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

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

        if (this.query != null && (query.hasAggregateFunctions() || query.hasGroupByColumns())) {

            JDBCSQLBuilderBase subsqlbuilder = this.createSQLBuilder();

            SelectBuilder subselect = subsqlbuilder.select();

            subselect.from().table()

                    .database(this.table.getDatabase())

                    .schema(this.table.getSchema())

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

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

            // ?sobra?

//            process2_ComputedFields(helper, featureType, query, sqlbuilder, subselect, extraColumnNames);

            process3_Where(helper, featureType, query, sqlbuilder, subselect);

            process4_Aggregates(this.table, this.featureType, this.query, getAllExtraColumns(this.featureType, this.query), sqlbuilder, subselect, extraColumnNames);

            process5_GroupBys(this.table, this.featureType, this.query, getAllExtraColumns(this.featureType, this.query), sqlbuilder, subselect, extraColumnNames);

            if (!StringUtils.isEmpty(baseFilter)) {

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

            }

            if(subselect.getColumns().isEmpty()){

                subselect.column().value(expbuilder.constant(1)).as("$ANY_VALUE$");

            }

            subsqlbuilder.setProperties(

                    ExpressionBuilder.Variable.class,

                    PROP_FEATURE_TYPE, this.featureType,

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

            this.helper.processSpecialFunctions(subsqlbuilder, featureType, extraColumnNames);

            String subsql = StringUtils.trim(subselect.toString());

            select.from().table()

                    .database(this.table.getDatabase())

                    .schema(this.table.getSchema())

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

            select.from().subquery(subsql);

        } else {

            process3_Where(helper, featureType, query, sqlbuilder, select);

            if (!StringUtils.isEmpty(baseFilter)) {

                sqlbuilder.select().where().and(expbuilder.custom(baseFilter));

            }

        }

        select.remove_all_columns();

        select.column().value(sqlbuilder.count().all());

        sqlbuilder.setProperties(

                null,

                PROP_FEATURE_TYPE, this.featureType,

                PROP_TABLE, table,

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

                PROP_JDBCHELPER, this.helper,

                PROP_QUERY, this.query

        );

        this.helper.expandCalculedColumns(sqlbuilder);

        this.helper.processSpecialFunctions(sqlbuilder, featureType, null);

        select.remove_all_columns();

        select.column().value(sqlbuilder.count().all());

        String sql = StringUtils.trim(select.toString());

        LOGGER.debug(sql);

        return sql;

    }

    public long count(JDBCConnection conn) throws DataException {

        String sql = this.getSQL();

        Statement st = null;

        ResultSet rs = null;

        try {

            st = conn.createStatement(sql);

            rs = JDBCUtils.executeQuery(st, sql);

            if (!rs.next()) {

                return 0;

            }

            return rs.getLong(1);

        } catch (SQLException ex) {

            throw new JDBCSQLException(ex,sql);

        } finally {

            JDBCUtils.closeQuietly(st);

            JDBCUtils.closeQuietly(rs);

        }

    }

}