Dépôt officiel du code source de l'ERP OpenConcerto

/*

 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.

 *

 * Copyright 2011-2019 OpenConcerto, by ILM Informatique. All rights reserved.

 *

 * The contents of this file are subject to the terms of the GNU General Public License Version 3

 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a

 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific

 * language governing permissions and limitations under the License.

 *

 * When distributing the software, include this License Header Notice in each file.

 */

 package org.openconcerto.sql.model;

import org.openconcerto.utils.CollectionUtils;

import org.openconcerto.utils.StringUtils;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Collection;

import java.util.Collections;

import java.util.List;

import java.util.Map;

import java.util.Map.Entry;

import java.util.Objects;

import java.util.stream.Collectors;

import net.jcip.annotations.Immutable;

/**

 * Une clause WHERE dans une requete SQL. Une clause peut être facilement combinée avec d'autre,

 * exemple : prenomPasVide.and(pasIndéfini).and(age_sup_3.or(assez_grand)).

 *

 * @author ILM Informatique 27 sept. 2004

 */

@Immutable

public class Where {

    static public final Where FALSE = Where.createRaw("1=0");

    static public final Where TRUE = Where.createRaw("1=1");

    static public final String NULL_IS_DATA_EQ = new String("===");

    static public final String NULL_IS_DATA_NEQ = new String("IS DISTINCT FROM");

    private static abstract class Combiner {

        public final Where combine(final Where w1, final Where w2) {

            if (w1 == null)

                return w2;

            else

                return this.combineNotNull(w1, w2);

        }

        protected abstract Where combineNotNull(Where w1, Where w2);

    }

    private static Combiner AndCombiner = new Combiner() {

        @Override

        protected Where combineNotNull(final Where where1, final Where where2) {

            return where1.and(where2);

        }

    };

    private static Combiner OrCombiner = new Combiner() {

        @Override

        protected Where combineNotNull(final Where where1, final Where where2) {

            return where1.or(where2);

        }

    };

    static private Where combine(final Collection<Where> wheres, final Combiner c) {

        Where res = null;

        for (final Where w : wheres) {

            res = c.combine(res, w);

        }

        return res;

    }

    static public Where and(final Collection<Where> wheres) {

        return combine(wheres, AndCombiner);

    }

    static public Where and(final SQLTable t, final Map<String, ?> fields) {

        final List<Where> res = new ArrayList<Where>(fields.size());

        for (final Entry<String, ?> e : fields.entrySet()) {

            res.add(new Where(t.getField(e.getKey()), "=", e.getValue()));

        }

        return and(res);

    }

    static public Where or(final Collection<Where> wheres) {

        return combine(wheres, OrCombiner);

    }

    /**

     * Permet de faire un ET entre 2 where.

     *

     * @param where1 le 1er, peut être <code>null</code>.

     * @param where2 le 2ème, peut être <code>null</code>.

     * @return le ET, peut être <code>null</code>.

     */

    static public Where and(final Where where1, final Where where2) {

        return AndCombiner.combine(where1, where2);

    }

    static public Where or(final Where where1, final Where where2) {

        return OrCombiner.combine(where1, where2);

    }

    static public Where isNull(final FieldRef ref) {

        return new Where(ref, "is", (Object) null);

    }

    static public Where isNotNull(final FieldRef ref) {

        return new Where(ref, "is not", (Object) null);

    }

    static public Where between(final FieldRef ref, final FieldRef lowerBound, final FieldRef upperBound) {

        return Where.createRaw(ref.getFieldRef() + " BETWEEN " + lowerBound.getFieldRef() + " AND " + upperBound.getFieldRef(), ref, lowerBound, upperBound);

    }

    static public Where inSubqueries(final FieldRef ref, final List<String> subQueries) {

        return subqueries(ref, true, subQueries);

    }

    static public Where notInSubqueries(final FieldRef ref, final List<String> subQueries) {

        return subqueries(ref, false, subQueries);

    }

    static public Where inValues(final FieldRef ref, final Collection<?> values) {

        return compareValues(ref, RowComparison.IN, values);

    }

    static public Where notInValues(final FieldRef ref, final Collection<?> values) {

        return compareValues(ref, RowComparison.NOT_IN, values);

    }

    static public Where compareValues(final FieldRef ref, final RowComparison cmp, final Collection<?> values) {

        return compareValues(ref, cmp, NullValue.IS_FORBIDDEN, values);

    }

    static public Where compareValues(final FieldRef ref, final RowComparison cmp, final NullValue nullMode, final Collection<?> values) {

        if (values.isEmpty()) {

            return cmp == RowComparison.IN ? FALSE : TRUE;

        }

        return createRaw(getCompareValuesClause(ref.getFieldRef(), cmp, nullMode, values, ref.getField().getType()), ref);

    }

    /**

     * Create a Where for a field value contained or not contained in sub-queries.

     *

     * @param ref the field.

     * @param in <code>true</code> if the field should be contained in the sub-queries.

     * @param subQueries the sub-queries to use.

     * @return a new Where.

     * @see Where#Where(FieldRef, boolean, SQLSelect)

     */

    static public Where subqueries(final FieldRef ref, final boolean in, final List<String> subQueries) {

        return createRaw(getInClause(ref, in, CollectionUtils.join(subQueries, "\nUNION\n")), ref);

    }

    static public Where createRaw(final String clause, final FieldRef... refs) {

        return createRaw(clause, Arrays.asList(refs));

    }

    static public Where createRaw(final String clause, final Collection<? extends FieldRef> refs) {

        if (clause == null)

            return null;

        return new Where(clause, refs);

    }

    /**

     * To create complex Where not possible with constructors.

     *

     * @param s the syntax to use.

     * @param pattern a pattern to be passed to {@link SQLBase#quoteStd(String, Object...)}, eg

     *        "EXTRACT(YEAR FROM %n) = 3007".

     * @param params the params to be passed to <code>quote()</code>, eg [|MISSION.DATE_DBT|].

     * @return a new Where with the result from <code>quote()</code> as its clause, and all

     *         <code>FieldRef</code> in params as its fields, eg {EXTRACT(YEAR FROM "DATE_DBT") =

     *         3007 , |MISSION.DATE_DBT|}.

     */

    static public Where quote(final SQLSyntax s, final String pattern, final Object... params) {

        final List<FieldRef> fields = new ArrayList<>();

        for (final Object param : params) {

            if (param instanceof FieldRef) {

                fields.add((FieldRef) param);

            }

        }

        return new Where(SQLBase.quote(s, pattern, params), fields);

    }

    static private final String normalizeOperator(final String op) {

        String res = op.trim();

        if (res.equals("!="))

            res = "<>";

        return res;

    }

    static public final String comparison(final FieldRef ref, final String op, final String y) {

        return comparison(null, ref.getField(), ref.getFieldRef(), op, y);

    }

    static public final String comparison(final SQLSyntax s, final String x, final String op, final String y) {

        return comparison(Objects.requireNonNull(s, "Missing syntax"), null, x, op, y);

    }

    static private final String comparison(SQLSyntax s, final DBStructureItem<?> syntaxSupplier, final String x, final String op, final String y) {

        Objects.requireNonNull(op, "Missing operator");

        if (op == NULL_IS_DATA_EQ || op == NULL_IS_DATA_NEQ) {

            if (s == null)

                s = syntaxSupplier.getDBSystemRoot().getSyntax();

            return s.getNullIsDataComparison(x, op == NULL_IS_DATA_EQ, y);

        } else {

            return x + ' ' + op + ' ' + y;

        }

    }

    static private final String getInClause(final FieldRef field1, final boolean in, final String inParens) {

        return getInClause(field1.getFieldRef(), in ? RowComparison.IN : RowComparison.NOT_IN, inParens);

    }

    static private final String getInClause(final String expr, final RowComparison cmp, final String inParens) {

        final String op = cmp == RowComparison.IN ? " in (" : " not in (";

        return expr + op + inParens + ')';

    }

    static public enum RowComparison {

        IN, NOT_IN

    }

    static public enum NullValue {

        IS_DATA, IS_FORBIDDEN, IS_UNKNOWN

    }

    static public final String getCompareValuesClause(final String expr, final RowComparison cmp, final Collection<?> values, final SQLType type) {

        return getCompareValuesClause(expr, cmp, NullValue.IS_FORBIDDEN, values, type);

    }

    static public final String getCompareValuesClause(final String expr, final RowComparison cmp, final NullValue nullMode, Collection<?> values, final SQLType type) {

        final boolean addNull;

        if (nullMode != NullValue.IS_UNKNOWN && values.contains(null)) {

            if (nullMode == NullValue.IS_FORBIDDEN)

                throw new IllegalArgumentException("Values contains a null value : " + values);

            assert nullMode == NullValue.IS_DATA;

            addNull = true;

        } else {

            addNull = false;

        }

        if (addNull) {

            values = values.stream().filter((i) -> i != null).collect(Collectors.toList());

        }

        String res = getInClause(expr, cmp, CollectionUtils.join(values, ",", (input) -> type.toString(input)));

        if (addNull) {

            if (cmp == RowComparison.IN) {

                res = expr + " is null or " + res;

            } else {

                res = expr + " is not null and " + res;

            }

        }

        return res;

    }

    private final List<FieldRef> fields;

    private final String clause;

    public Where(final FieldRef field1, final String op, final FieldRef field2) {

        this.fields = Arrays.asList(field1, field2);

        this.clause = comparison(field1, normalizeOperator(op), field2.getFieldRef());

    }

    public Where(final FieldRef field1, final String op, final int scalar) {

        this(field1, op, (Integer) scalar);

    }

    /**

     * Construct a clause like "field = 'hi'". Note: this method will try to rewrite "= null" and

     * "<> null" to "is null" and "is not null", treating null as a Java <code>null</code> (ie null

     * == null) and not as a SQL NULL (NULL != NULL), see PostgreSQL documentation section 9.2.

     * Comparison Operators. ATTN new Where(f, "=", null) will call

     * {@link #Where(FieldRef, String, FieldRef)}, you have to cast to Object.

     *

     * @param ref a field.

     * @param op an arbitrary operator.

     * @param o the object to compare <code>ref</code> to.

     */

    public Where(final FieldRef ref, String op, final Object o) {

        this.fields = Collections.singletonList(ref);

        op = normalizeOperator(op);

        if (o == null) {

            if (op.equals("="))

                op = "is";

            else if (op.equals("<>"))

                op = "is not";

        }

        this.clause = comparison(ref, op, ref.getField().getType().toString(o));

    }

    /**

     * Crée une clause "field1 in (values)". Some databases won't accept empty values (impossible

     * where clause), so we return false.

     *

     * @param field1 le champs à tester.

     * @param values les valeurs.

     * @deprecated use {@link #inValues(FieldRef, Collection)}

     */

    public Where(final FieldRef field1, final Collection<?> values) {

        this(field1, true, values);

    }

    /**

     * Construct a clause like "field1 not in (value, ...)".

     *

     * @param field1 le champs à tester.

     * @param in <code>true</code> for "in", <code>false</code> for "not in".

     * @param values les valeurs.

     * @deprecated use {@link #inValues(FieldRef, Collection)} or

     *             {@link #notInValues(FieldRef, Collection)}

     */

    public Where(final FieldRef field1, final boolean in, final Collection<?> values) {

        if (values.isEmpty()) {

            this.fields = Collections.emptyList();

            this.clause = in ? FALSE.getClause() : TRUE.getClause();

        } else {

            this.fields = Collections.singletonList(field1);

            this.clause = getCompareValuesClause(field1.getFieldRef(), in ? RowComparison.IN : RowComparison.NOT_IN, values, field1.getField().getType());

        }

    }

    public Where(final FieldRef field1, final boolean in, final SQLSelect subQuery) {

        this.fields = Collections.singletonList(field1);

        this.clause = getInClause(field1, in, subQuery.asString());

    }

    /**

     * Crée une clause "field BETWEEN borneInf AND borneSup".

     *

     * @param ref le champs à tester.

     * @param borneInf la valeur minimum.

     * @param borneSup la valeur maximum.

     */

    public Where(final FieldRef ref, final Object borneInf, final Object borneSup) {

        final SQLField field1 = ref.getField();

        this.fields = Collections.singletonList(ref);

        this.clause = ref.getFieldRef() + " BETWEEN " + field1.getType().toString(borneInf) + " AND " + field1.getType().toString(borneSup);

    }

    /**

     * Crée une clause pour que <code>ref</code> soit compris entre <code>bornInf</code> et

     * <code>bornSup</code>.

     *

     * @param ref a field, eg NAME.

     * @param borneInf the lower bound, eg "DOE".

     * @param infInclusive <code>true</code> if the lower bound should be included, eg

     *        <code>false</code> if "DOE" shouldn't match.

     * @param borneSup the upper bound, eg "SMITH".

     * @param supInclusive <code>true</code> if the upper bound should be included.

     */

    public Where(final FieldRef ref, final Object borneInf, final boolean infInclusive, final Object borneSup, final boolean supInclusive) {

        this.fields = Collections.singletonList(ref);

        final String infClause = new Where(ref, infInclusive ? ">=" : ">", borneInf).getClause();

        final String supClause = new Where(ref, supInclusive ? "<=" : "<", borneSup).getClause();

        this.clause = infClause + " AND " + supClause;

    }

    // raw ctor, see static methods

    private Where(final String clause, final Collection<? extends FieldRef> refs) {

        if (StringUtils.isEmpty(clause, true))

            throw new IllegalArgumentException("No clause");

        this.fields = Collections.unmodifiableList(new ArrayList<FieldRef>(refs));

        this.clause = clause;

    }

    public Where or(final Where w) {

        return this.combine(w, "OR");

    }

    public Where and(final Where w) {

        return this.combine(w, "AND");

    }

    public Where not() {

        return new Where("NOT (" + this.clause + ")", this.fields);

    }

    private Where combine(final Where w, final String op) {

        if (w == null)

            return this;

        final List<FieldRef> fields = new ArrayList<FieldRef>();

        fields.addAll(this.fields);

        fields.addAll(w.fields);

        final String clause = "(" + this.clause + ") " + op + " (" + w.clause + ")";

        return new Where(clause, fields);

    }

    /**

     * La clause.

     *

     * @return la clause.

     */

    public String getClause() {

        return this.clause;

    }

    /**

     * Les champs utilisés dans cette clause.

     *

     * @return a list of FieldRef.

     */

    public List<FieldRef> getFields() {

        return this.fields;

    }

    @Override

    public String toString() {

        return this.getClause();

    }

    @Override

    public boolean equals(Object obj) {

        if (this == obj)

            return true;

        if (obj == null)

            return false;

        if (getClass() != obj.getClass())

            return false;

        final Where o = (Where) obj;

        return this.getClause().equals(o.getClause()) && this.getFields().equals(o.getFields());

    }

    @Override

    public int hashCode() {

        final int prime = 31;

        int result = 1;

        result = prime * result + this.getClause().hashCode();

        result = prime * result + this.getFields().hashCode();

        return result;

    }

}