001/*
002 * (C) Copyright 2018 Nuxeo (http://nuxeo.com/) and others.
003 *
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 *     http://www.apache.org/licenses/LICENSE-2.0
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 *
016 * Contributors:
017 *     Florent Guillaume
018 */
019package org.nuxeo.ecm.directory.sql;
020
021import java.io.Serializable;
022import java.util.ArrayList;
023import java.util.Iterator;
024import java.util.List;
025
026import org.nuxeo.ecm.core.query.QueryParseException;
027import org.nuxeo.ecm.core.query.sql.model.BooleanLiteral;
028import org.nuxeo.ecm.core.query.sql.model.DateLiteral;
029import org.nuxeo.ecm.core.query.sql.model.DefaultQueryVisitor;
030import org.nuxeo.ecm.core.query.sql.model.DoubleLiteral;
031import org.nuxeo.ecm.core.query.sql.model.Expression;
032import org.nuxeo.ecm.core.query.sql.model.Function;
033import org.nuxeo.ecm.core.query.sql.model.IntegerLiteral;
034import org.nuxeo.ecm.core.query.sql.model.Literal;
035import org.nuxeo.ecm.core.query.sql.model.LiteralList;
036import org.nuxeo.ecm.core.query.sql.model.MultiExpression;
037import org.nuxeo.ecm.core.query.sql.model.Operand;
038import org.nuxeo.ecm.core.query.sql.model.Operator;
039import org.nuxeo.ecm.core.query.sql.model.Predicate;
040import org.nuxeo.ecm.core.query.sql.model.Reference;
041import org.nuxeo.ecm.core.query.sql.model.StringLiteral;
042import org.nuxeo.ecm.core.storage.sql.ColumnSpec;
043import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column;
044
045/**
046 * Builds the database-level WHERE query from the AST, and collects parameters associated to free variables along with
047 * the database column to which they correspond.
048 *
049 * @since 10.3
050 */
051public class SQLQueryBuilder extends DefaultQueryVisitor {
052
053    /** @since 10.3 */
054    public static class ColumnAndValue {
055
056        public final Column column;
057
058        public final Serializable value;
059
060        public ColumnAndValue(Column column, Serializable value) {
061            this.column = column;
062            this.value = value;
063        }
064
065        public Column getColumn() {
066            return column;
067        }
068
069        public Serializable getValue() {
070            return value;
071        }
072    }
073
074    protected final SQLDirectory directory;
075
076    public final StringBuilder clause = new StringBuilder();
077
078    public final List<ColumnAndValue> params = new ArrayList<>();
079
080    protected Column visitedColumn;
081
082    public SQLQueryBuilder(SQLDirectory directory) {
083        this.directory = directory;
084    }
085
086    @Override
087    public void visitMultiExpression(MultiExpression node) {
088        if (node.predicates.isEmpty()) {
089            // if this happens we won't have a valid SQL expression
090            // but the caller should check for empty clauses and prune them
091            return;
092        }
093        clause.append('(');
094        for (Iterator<Predicate> it = node.predicates.iterator(); it.hasNext();) {
095            it.next().accept(this);
096            if (it.hasNext()) {
097                node.operator.accept(this);
098            }
099        }
100        clause.append(')');
101    }
102
103    @Override
104    public void visitExpression(Expression node) {
105        clause.append('(');
106        Operand lvalue = node.lvalue;
107        Operand rvalue = node.rvalue;
108        Column column = null;
109        if (lvalue instanceof Reference) {
110            Reference ref = (Reference) lvalue;
111            if (ref.cast != null) {
112                throw new QueryParseException("Cannot use cast: " + node);
113            }
114            column = getColumn(ref.name);
115            Operator op = node.operator;
116            if (op == Operator.BETWEEN || op == Operator.NOTBETWEEN) {
117                visitExpressionBetween(column, op, (LiteralList) rvalue);
118            } else if (op == Operator.LIKE || op == Operator.NOTLIKE) {
119                visitExpressionLike(column, op, rvalue);
120            } else if (op == Operator.ILIKE || op == Operator.NOTILIKE) {
121                visitExpressionILike(column, op, rvalue);
122            } else {
123                visitExpression(column, op, rvalue);
124            }
125        } else {
126            super.visitExpression(node);
127        }
128        clause.append(')');
129    }
130
131    protected void visitExpressionBetween(Column column, Operator op, LiteralList list) {
132        visitColumn(column);
133        op.accept(this);
134        list.get(0).accept(this);
135        clause.append(" AND ");
136        list.get(1).accept(this);
137    }
138
139    protected void visitExpressionLike(Column column, Operator op, Operand rvalue) {
140        visitExpression(column, op, rvalue);
141        addLikeEscaping();
142    }
143
144    protected void visitExpressionILike(Column column, Operator op, Operand rvalue) {
145        if (directory.getDialect().supportsIlike()) {
146            visitExpression(column, op, rvalue);
147        } else {
148            clause.append("LOWER(");
149            visitColumn(column);
150            clause.append(") ");
151            if (op == Operator.NOTILIKE) {
152                clause.append("NOT ");
153            }
154            clause.append("LIKE");
155            clause.append(" LOWER(");
156            rvalue.accept(this);
157            clause.append(")");
158            addLikeEscaping();
159        }
160    }
161
162    protected void addLikeEscaping() {
163        String escape = directory.getDialect().getLikeEscaping();
164        if (escape != null) {
165            clause.append(escape);
166        }
167    }
168
169    protected void visitExpression(Column column, Operator op, Operand rvalue) {
170        visitColumn(column);
171        if (op == Operator.EQ || op == Operator.NOTEQ) {
172            if (column.getType().spec == ColumnSpec.BOOLEAN) {
173                rvalue = getBooleanLiteral(rvalue);
174            }
175            if (directory.getDialect().hasNullEmptyString() && rvalue instanceof StringLiteral
176                    && ((StringLiteral) rvalue).value.isEmpty()) {
177                // see NXP-6172, empty values are Null in Oracle
178                op = op == Operator.EQ ? Operator.ISNULL : Operator.ISNOTNULL;
179                rvalue = null;
180            }
181
182        }
183        op.accept(this);
184        if (rvalue != null) {
185            rvalue.accept(this);
186        }
187    }
188
189    @Override
190    public void visitOperator(Operator node) {
191        if (node != Operator.NOT) {
192            clause.append(' ');
193        }
194        clause.append(node.toString());
195        clause.append(' ');
196    }
197
198    @Override
199    public void visitReference(Reference node) {
200        visitColumn(getColumn(node.name));
201    }
202
203    protected void visitColumn(Column column) {
204        visitedColumn = column;
205        clause.append(column.getQuotedName());
206    }
207
208    @Override
209    public void visitLiteralList(LiteralList node) {
210        clause.append('(');
211        for (Iterator<Literal> it = node.iterator(); it.hasNext();) {
212            it.next().accept(this);
213            if (it.hasNext()) {
214                clause.append(", ");
215            }
216        }
217        clause.append(')');
218    }
219
220    @Override
221    public void visitDateLiteral(DateLiteral node) {
222        clause.append('?');
223        if (node.onlyDate) {
224            params.add(new ColumnAndValue(visitedColumn, node.toSqlDate()));
225        } else {
226            params.add(new ColumnAndValue(visitedColumn, node.toCalendar()));
227        }
228    }
229
230    @Override
231    public void visitStringLiteral(StringLiteral node) {
232        clause.append('?');
233        params.add(new ColumnAndValue(visitedColumn, node.value));
234    }
235
236    @Override
237    public void visitDoubleLiteral(DoubleLiteral node) {
238        clause.append('?');
239        params.add(new ColumnAndValue(visitedColumn, Double.valueOf(node.value)));
240    }
241
242    @Override
243    public void visitIntegerLiteral(IntegerLiteral node) {
244        clause.append('?');
245        params.add(new ColumnAndValue(visitedColumn, Long.valueOf(node.value)));
246    }
247
248    @Override
249    public void visitBooleanLiteral(BooleanLiteral node) {
250        clause.append('?');
251        params.add(new ColumnAndValue(visitedColumn, Boolean.valueOf(node.value)));
252    }
253
254    @Override
255    public void visitFunction(Function node) {
256        throw new QueryParseException("Function not supported" + node);
257    }
258
259    protected Column getColumn(String name) {
260        Column column = directory.getTable().getColumn(name);
261        if (column == null) {
262            throw new QueryParseException("No column: " + name + " for directory: " + directory.getName());
263        }
264        return column;
265    }
266
267    protected Operand getBooleanLiteral(Operand rvalue) {
268        if (rvalue instanceof BooleanLiteral) {
269            return rvalue;
270        }
271        long v;
272        if (!(rvalue instanceof IntegerLiteral) || ((v = ((IntegerLiteral) rvalue).value) != 0 && v != 1)) {
273            throw new QueryParseException("Boolean expressions require boolean or literal 0 or 1 as right argument");
274        }
275        return new BooleanLiteral(v == 1);
276    }
277}