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}