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