001/* 002 * (C) Copyright 2006-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.core.storage.sql.jdbc; 020 021 022import static org.nuxeo.ecm.core.api.trash.TrashService.Feature.TRASHED_STATE_IN_MIGRATION; 023import static org.nuxeo.ecm.core.api.trash.TrashService.Feature.TRASHED_STATE_IS_DEDICATED_PROPERTY; 024import static org.nuxeo.ecm.core.api.trash.TrashService.Feature.TRASHED_STATE_IS_DEDUCED_FROM_LIFECYCLE; 025 026import java.io.Serializable; 027import java.sql.Types; 028import java.util.ArrayList; 029import java.util.Arrays; 030import java.util.Collections; 031import java.util.HashMap; 032import java.util.HashSet; 033import java.util.Iterator; 034import java.util.LinkedHashMap; 035import java.util.LinkedList; 036import java.util.List; 037import java.util.Map; 038import java.util.Map.Entry; 039import java.util.Set; 040import java.util.regex.Pattern; 041 042import org.apache.commons.lang3.StringUtils; 043import org.apache.commons.logging.Log; 044import org.apache.commons.logging.LogFactory; 045import org.nuxeo.common.utils.FullTextUtils; 046import org.nuxeo.ecm.core.api.LifeCycleConstants; 047import org.nuxeo.ecm.core.api.impl.FacetFilter; 048import org.nuxeo.ecm.core.api.trash.TrashService; 049import org.nuxeo.ecm.core.query.QueryFilter; 050import org.nuxeo.ecm.core.query.QueryParseException; 051import org.nuxeo.ecm.core.query.sql.NXQL; 052import org.nuxeo.ecm.core.query.sql.SQLQueryParser; 053import org.nuxeo.ecm.core.query.sql.model.BooleanLiteral; 054import org.nuxeo.ecm.core.query.sql.model.DateLiteral; 055import org.nuxeo.ecm.core.query.sql.model.DefaultQueryVisitor; 056import org.nuxeo.ecm.core.query.sql.model.DoubleLiteral; 057import org.nuxeo.ecm.core.query.sql.model.Expression; 058import org.nuxeo.ecm.core.query.sql.model.FromClause; 059import org.nuxeo.ecm.core.query.sql.model.FromList; 060import org.nuxeo.ecm.core.query.sql.model.Function; 061import org.nuxeo.ecm.core.query.sql.model.IntegerLiteral; 062import org.nuxeo.ecm.core.query.sql.model.Literal; 063import org.nuxeo.ecm.core.query.sql.model.LiteralList; 064import org.nuxeo.ecm.core.query.sql.model.MultiExpression; 065import org.nuxeo.ecm.core.query.sql.model.Operand; 066import org.nuxeo.ecm.core.query.sql.model.Operator; 067import org.nuxeo.ecm.core.query.sql.model.OrderByClause; 068import org.nuxeo.ecm.core.query.sql.model.OrderByExpr; 069import org.nuxeo.ecm.core.query.sql.model.OrderByList; 070import org.nuxeo.ecm.core.query.sql.model.Predicate; 071import org.nuxeo.ecm.core.query.sql.model.Reference; 072import org.nuxeo.ecm.core.query.sql.model.SQLQuery; 073import org.nuxeo.ecm.core.query.sql.model.SelectClause; 074import org.nuxeo.ecm.core.query.sql.model.StringLiteral; 075import org.nuxeo.ecm.core.query.sql.model.WhereClause; 076import org.nuxeo.ecm.core.storage.sql.ColumnType; 077import org.nuxeo.ecm.core.storage.sql.ColumnType.WrappedId; 078import org.nuxeo.ecm.core.storage.sql.Model; 079import org.nuxeo.ecm.core.storage.sql.ModelProperty; 080import org.nuxeo.ecm.core.storage.sql.Session.PathResolver; 081import org.nuxeo.ecm.core.storage.sql.jdbc.SQLInfo.ColumnMapMaker; 082import org.nuxeo.ecm.core.storage.sql.jdbc.SQLInfo.SQLInfoSelect; 083import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column; 084import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database; 085import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join; 086import org.nuxeo.ecm.core.storage.sql.jdbc.db.Select; 087import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table; 088import org.nuxeo.ecm.core.storage.sql.jdbc.db.TableAlias; 089import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.Dialect; 090import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.Dialect.ArraySubQuery; 091import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.Dialect.FulltextMatchInfo; 092import org.nuxeo.runtime.api.Framework; 093import org.nuxeo.runtime.migration.MigrationService; 094import org.nuxeo.runtime.migration.MigrationService.MigrationStatus; 095 096/** 097 * Transformer of NXQL queries into underlying SQL queries to the actual database. 098 * <p> 099 * The examples below are using the NXQL statement syntax: 100 * 101 * <pre> 102 * SELECT * FROM File 103 * WHERE 104 * dc:title = 'abc' 105 * AND uid:uid = '123' 106 * AND dc:contributors = 'bob' -- multi-valued 107 * </pre> 108 * 109 * If there are no proxies (ecm:isProxy = 0) we get: 110 * 111 * <pre> 112 * SELECT hierarchy.id 113 * FROM hierarchy 114 * LEFT JOIN dublincore ON hierarchy.id = dublincore.id 115 * LEFT JOIN uid ON hierarchy.id = uid.id 116 * WHERE 117 * hierarchy.primarytype IN ('File', 'SubFile') 118 * AND dublincore.title = 'abc' 119 * AND uid.uid = '123' 120 * AND EXISTS (SELECT 1 FROM dc_contributors WHERE hierarchy.id = dc_contributors.id 121 * AND dc_contributors.item = 'bob') 122 * AND NX_ACCESS_ALLOWED(hierarchy.id, 'user1|user2', 'perm1|perm2') 123 * </pre> 124 * 125 * The data tables (dublincore, uid) are joined using a LEFT JOIN, as the schema may not be present on all documents but 126 * this shouldn't prevent the WHERE clause from being evaluated. Complex properties are matched using an EXISTS and a 127 * subselect. When proxies are matched (ecm:isProxy = 1) there are two additional FULL JOINs. Security checks, id, name, 128 * parents and path use the base hierarchy (_H), but all other data use the joined hierarchy. 129 * 130 * <pre> 131 * SELECT _H.id 132 * FROM hierarchy _H 133 * JOIN proxies ON _H.id = proxies.id -- proxy full join 134 * JOIN hierarchy ON hierarchy.id = proxies.targetid -- proxy full join 135 * LEFT JOIN dublincore ON hierarchy.id = dublincore.id 136 * LEFT JOIN uid ON hierarchy.id = uid.id 137 * WHERE 138 * hierarchy.primarytype IN ('File', 'SubFile') 139 * AND dublincore.title = 'abc' 140 * AND uid.uid = '123' 141 * AND EXISTS (SELECT 1 FROM dc_contributors WHERE hierarchy.id = dc_contributors.id 142 * AND dc_contributors.item = 'bob') 143 * AND NX_ACCESS_ALLOWED(_H.id, 'user1|user2', 'perm1|perm2') -- uses _H 144 * </pre> 145 * 146 * When both normal documents and proxies are matched, we UNION ALL the two queries. If an ORDER BY is requested, then 147 * columns from the inner SELECTs have to be aliased so that an outer ORDER BY can user their names. 148 * 149 * @author Florent Guillaume 150 */ 151public class NXQLQueryMaker implements QueryMaker { 152 153 private static final Log log = LogFactory.getLog(NXQLQueryMaker.class); 154 155 public static final String TYPE_DOCUMENT = "Document"; 156 157 public static final String TYPE_RELATION = "Relation"; 158 159 public static final String TYPE_TAGGING = "Tagging"; 160 161 public static final String RELATION_TABLE = "relation"; 162 163 public static final String ECM_SIMPLE_ACP_PRINCIPAL = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_PRINCIPAL; 164 165 public static final String ECM_SIMPLE_ACP_PERMISSION = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_PERMISSION; 166 167 public static final String ECM_SIMPLE_ACP_GRANT = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_GRANT; 168 169 public static final String ECM_SIMPLE_ACP_NAME = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_NAME; 170 171 public static final String ECM_SIMPLE_ACP_POS = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_POS; 172 173 /** 174 * @since 7.4 175 */ 176 public static final String ECM_SIMPLE_ACP_CREATOR = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_CREATOR; 177 178 /** 179 * @since 7.4 180 */ 181 public static final String ECM_SIMPLE_ACP_BEGIN = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_BEGIN; 182 183 /** 184 * @since 7.4 185 */ 186 public static final String ECM_SIMPLE_ACP_END = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_END; 187 188 /** 189 * @since 7.4 190 */ 191 public static final String ECM_SIMPLE_ACP_STATUS = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_STATUS; 192 193 public static final String ECM_TAG_STAR = NXQL.ECM_TAG + "/*"; 194 195 /** 196 * @since 9.3 197 */ 198 public static final String FACETED_TAG = "nxtag:tags"; 199 200 /** 201 * @since 9.3 202 */ 203 public static final String FACETED_TAG_LABEL = "label"; 204 205 protected static final String TABLE_HIER_ALIAS = "_H"; 206 207 protected static final String TABLE_FRAG_ALIAS = "_F"; 208 209 protected static final String SUBQUERY_ARRAY_ALIAS = "_A"; 210 211 protected static final String COL_ALIAS_PREFIX = "_C"; 212 213 protected static final String UNION_ALIAS = "_T"; 214 215 protected static final String WITH_ALIAS_PREFIX = "_W"; 216 217 protected static final String READ_ACL_ALIAS = "_RACL"; 218 219 protected static final String READ_ACL_USER_MAP_ALIAS = "_ACLRUSERMAP"; 220 221 protected static final String DATE_CAST = "DATE"; 222 223 protected static final String COUNT_FUNCTION = "COUNT"; 224 225 protected static final String AVG_FUNCTION = "AVG"; 226 227 protected static final List<String> AGGREGATE_FUNCTIONS = Arrays.asList(COUNT_FUNCTION, AVG_FUNCTION, "SUM", "MIN", 228 "MAX"); 229 230 /* 231 * Fields used by the search service. 232 */ 233 234 protected SQLInfo sqlInfo; 235 236 protected Database database; 237 238 protected Dialect dialect; 239 240 protected Model model; 241 242 protected Set<String> neverPerInstanceMixins; 243 244 protected PathResolver pathResolver; 245 246 protected final Map<String, String> aliasesByName = new HashMap<>(); 247 248 protected final List<String> aliases = new LinkedList<>(); 249 250 /** The select columns which we don't want to be NULL. */ 251 protected Set<String> selectCollectionNotNull; 252 253 /** 254 * Whether the query must match only proxies (TRUE), no proxies (FALSE), or not specified (null). 255 */ 256 protected Boolean proxyClause; 257 258 /** The reason why proxyClause was set to non-null. */ 259 protected String proxyClauseReason; 260 261 // The hierarchy table for the hierarchy/name, may be an alias table 262 protected Table hierTable; 263 264 // The hierarchy table of the data 265 protected Table dataHierTable; 266 267 // The proxies table if querying for proxies 268 protected Table proxyTable; 269 270 protected List<Join> joins; 271 272 protected List<String> whereClauses; 273 274 protected List<Serializable> whereParams; 275 276 // fragmentName or prefix/fragmentName -> fragment table to join 277 protected Map<String, Table> propertyFragmentTables = new HashMap<>(); 278 279 protected int fragJoinCount = 0; 280 281 @Override 282 public String getName() { 283 return NXQL.NXQL; 284 } 285 286 @Override 287 public boolean accepts(String queryType) { 288 return queryType.equals(NXQL.NXQL); 289 } 290 291 public enum DocKind { 292 DIRECT, PROXY 293 } 294 295 @Override 296 public Query buildQuery(SQLInfo sqlInfo, Model model, PathResolver pathResolver, String query, 297 QueryFilter queryFilter, Object... params) { 298 this.sqlInfo = sqlInfo; 299 database = sqlInfo.database; 300 dialect = sqlInfo.dialect; 301 this.model = model; 302 this.pathResolver = pathResolver; 303 neverPerInstanceMixins = new HashSet<>(model.getNoPerDocumentQueryFacets()); 304 // compat 305 Set<String> npim = model.getRepositoryDescriptor().neverPerInstanceMixins; 306 if (npim != null) { 307 neverPerInstanceMixins.addAll(npim); 308 } 309 310 // transform the query according to the transformers defined by the 311 // security policies 312 SQLQuery sqlQuery = SQLQueryParser.parse(query); 313 for (SQLQuery.Transformer transformer : queryFilter.getQueryTransformers()) { 314 sqlQuery = transformer.transform(queryFilter.getPrincipal(), sqlQuery); 315 } 316 317 // SELECT * -> SELECT ecm:uuid 318 SelectClause selectClause = sqlQuery.select; 319 if (selectClause.isEmpty()) { 320 // turned into SELECT ecm:uuid 321 selectClause.add(new Reference(NXQL.ECM_UUID)); 322 } 323 boolean selectStar = selectClause.getSelectList().size() == 1 324 && (selectClause.get(0).equals(new Reference(NXQL.ECM_UUID))); 325 326 /* 327 * Analyze query to find all relevant types and keys for the criteria, and fulltext matches. 328 */ 329 330 QueryAnalyzer queryAnalyzer = newQueryAnalyzer(queryFilter.getFacetFilter()); 331 try { 332 queryAnalyzer.visitQuery(sqlQuery); 333 } catch (QueryCannotMatchException e) { 334 // query cannot match 335 return null; 336 } 337 338 boolean distinct = selectClause.isDistinct(); 339 if (selectStar && queryAnalyzer.hasWildcardIndex) { 340 distinct = true; 341 } 342 343 boolean reAnalyze = false; 344 // add a default ORDER BY ecm:fulltextScore DESC 345 if (queryAnalyzer.ftCount == 1 && !distinct && sqlQuery.orderBy == null) { 346 sqlQuery.orderBy = new OrderByClause( 347 new OrderByList(new OrderByExpr(new Reference(NXQL.ECM_FULLTEXT_SCORE), true)), false); 348 queryAnalyzer.orderByScore = true; 349 reAnalyze = true; 350 } 351 // if ORDER BY ecm:fulltextScore, make sure we SELECT on it too 352 if (queryAnalyzer.orderByScore && !queryAnalyzer.selectScore) { 353 selectClause.add(new Reference(NXQL.ECM_FULLTEXT_SCORE)); 354 reAnalyze = true; 355 } 356 if (reAnalyze) { 357 queryAnalyzer.visitQuery(sqlQuery); 358 } 359 360 if (queryAnalyzer.ftCount > 1 && (queryAnalyzer.orderByScore || queryAnalyzer.selectScore)) { 361 throw new QueryParseException( 362 "Cannot use " + NXQL.ECM_FULLTEXT_SCORE + " with more than one fulltext match expression"); 363 } 364 365 /* 366 * Find whether to check proxies, relations. 367 */ 368 369 if (!model.getRepositoryDescriptor().getProxiesEnabled() || queryAnalyzer.onlyRelations) { 370 if (proxyClause == Boolean.TRUE) { 371 // proxies disabled or searching only relations (which don't have proxies) 372 // query cannot match 373 return null; 374 } 375 proxyClause = Boolean.FALSE; 376 } 377 DocKind[] docKinds; 378 if (proxyClause == Boolean.TRUE) { 379 docKinds = new DocKind[] { DocKind.PROXY }; 380 } else if (proxyClause == Boolean.FALSE) { 381 docKinds = new DocKind[] { DocKind.DIRECT }; 382 } else { 383 docKinds = new DocKind[] { DocKind.DIRECT, DocKind.PROXY }; 384 } 385 boolean doUnion = docKinds.length > 1; 386 387 /* 388 * DISTINCT check and add additional selected columns for ORDER BY. 389 */ 390 391 boolean hasSelectCollection = queryAnalyzer.hasSelectCollection; 392 if (doUnion || distinct) { 393 // if UNION, we need all the ORDER BY columns in the SELECT list 394 // for aliasing 395 List<String> whatColumnNames = queryAnalyzer.whatColumnNames; 396 if (distinct && !whatColumnNames.contains(NXQL.ECM_UUID)) { 397 // when using DISTINCT, we can't also ORDER BY on an artificial ecm:uuid + pos 398 hasSelectCollection = false; 399 } 400 Set<String> onlyOrderByColumnNames = new HashSet<>(queryAnalyzer.orderByColumnNames); 401 if (hasSelectCollection) { 402 onlyOrderByColumnNames.add(NXQL.ECM_UUID); 403 } 404 onlyOrderByColumnNames.removeAll(whatColumnNames); 405 if (distinct && !onlyOrderByColumnNames.isEmpty()) { 406 // if DISTINCT, check that the ORDER BY columns are all in the 407 // SELECT list 408 if (!selectStar) { 409 throw new QueryParseException( 410 "For SELECT DISTINCT the ORDER BY columns must be in the SELECT list, missing: " 411 + onlyOrderByColumnNames); 412 } 413 // for a SELECT *, we can add the needed columns if they 414 // don't involve wildcard index array elements 415 if (queryAnalyzer.orderByHasWildcardIndex) { 416 throw new QueryParseException("For SELECT * the ORDER BY columns cannot use wildcard indexes"); 417 } 418 } 419 for (String name : onlyOrderByColumnNames) { 420 selectClause.add(new Reference(name)); 421 } 422 } 423 424 /* 425 * Build the FROM / JOIN criteria for each select. 426 */ 427 428 String from; 429 List<Column> whatColumns = null; 430 List<String> whatKeys = null; 431 Select select = null; 432 String orderBy = null; 433 List<String> statements = new ArrayList<>(2); 434 List<Serializable> selectParams = new LinkedList<>(); 435 List<String> withTables = new LinkedList<>(); 436 List<Select> withSelects = new LinkedList<>(); 437 List<String> withSelectsStatements = new LinkedList<>(); 438 List<Serializable> withParams = new LinkedList<>(); 439 Table hier = database.getTable(Model.HIER_TABLE_NAME); 440 441 for (DocKind docKind : docKinds) { 442 443 // Quoted id in the hierarchy. This is the id returned by the query. 444 String hierId; 445 446 joins = new LinkedList<>(); 447 whereClauses = new LinkedList<>(); 448 whereParams = new LinkedList<>(); 449 propertyFragmentTables = new HashMap<>(); 450 fragJoinCount = 0; 451 452 switch (docKind) { 453 case DIRECT: 454 hierTable = hier; 455 dataHierTable = hierTable; 456 hierId = hierTable.getColumn(Model.MAIN_KEY).getFullQuotedName(); 457 from = hierTable.getQuotedName(); 458 proxyTable = null; 459 break; 460 case PROXY: 461 hierTable = new TableAlias(hier, TABLE_HIER_ALIAS); 462 dataHierTable = hier; 463 // TODO use dialect 464 from = hier.getQuotedName() + " " + hierTable.getQuotedName(); 465 hierId = hierTable.getColumn(Model.MAIN_KEY).getFullQuotedName(); 466 // proxies 467 proxyTable = database.getTable(Model.PROXY_TABLE_NAME); 468 // join all that 469 addJoin(Join.INNER, null, proxyTable, Model.MAIN_KEY, hierTable, Model.MAIN_KEY, null, -1, null); 470 addJoin(Join.INNER, null, dataHierTable, Model.MAIN_KEY, proxyTable, Model.PROXY_TARGET_KEY, null, -1, 471 null); 472 break; 473 default: 474 throw new AssertionError(docKind); 475 } 476 fixInitialJoins(); 477 478 // init builder 479 480 WhereBuilder whereBuilder = newWhereBuilder(docKind == DocKind.PROXY); 481 selectClause.accept(whereBuilder); 482 whatColumns = whereBuilder.whatColumns; 483 whatKeys = whereBuilder.whatKeys; 484 485 /* 486 * Process WHERE. 487 */ 488 489 if (queryAnalyzer.wherePredicate != null) { 490 queryAnalyzer.wherePredicate.accept(whereBuilder); 491 // WHERE clause 492 String where = whereBuilder.buf.toString(); 493 if (where.length() != 0) { 494 whereClauses.add(where); 495 } 496 } 497 498 /* 499 * Process WHAT to select. 500 */ 501 502 // alias columns in all cases to simplify logic 503 List<String> whatNames = new ArrayList<>(1); 504 List<Serializable> whatNamesParams = new ArrayList<>(1); 505 String mainAlias = hierId; 506 aliasesByName.clear(); 507 aliases.clear(); 508 for (int i = 0; i < whatColumns.size(); i++) { 509 Column col = whatColumns.get(i); 510 String key = whatKeys.get(i); 511 String alias; 512 String whatName; 513 if (NXQL.ECM_FULLTEXT_SCORE.equals(key)) { 514 FulltextMatchInfo ftMatchInfo = whereBuilder.ftMatchInfo; 515 if (ftMatchInfo == null) { 516 throw new QueryParseException( 517 NXQL.ECM_FULLTEXT_SCORE + " cannot be used without " + NXQL.ECM_FULLTEXT); 518 } 519 alias = ftMatchInfo.scoreAlias; 520 whatName = ftMatchInfo.scoreExpr; 521 if (ftMatchInfo.scoreExprParam != null) { 522 whatNamesParams.add(ftMatchInfo.scoreExprParam); 523 } 524 } else { 525 alias = dialect.openQuote() + COL_ALIAS_PREFIX + (i + 1) + dialect.closeQuote(); 526 whatName = getSelectColName(col, key); 527 if (col.getTable().getRealTable() == hier && col.getKey().equals(Model.MAIN_KEY)) { 528 mainAlias = alias; 529 } 530 } 531 aliasesByName.put(key, alias); 532 aliases.add(alias); 533 whatNames.add(whatName + " AS " + alias); 534 } 535 536 fixWhatColumns(whatColumns); 537 538 /* 539 * Process ORDER BY. 540 */ 541 542 // ORDER BY computed just once; may use just aliases 543 if (orderBy == null) { 544 if (hasSelectCollection) { 545 if (sqlQuery.orderBy == null) { 546 sqlQuery.orderBy = new OrderByClause(new OrderByList()); 547 } 548 // always add ecm:uuid in ORDER BY 549 sqlQuery.orderBy.elements.add(new OrderByExpr(new Reference(NXQL.ECM_UUID), false)); 550 } 551 if (sqlQuery.orderBy != null) { 552 // needs aliasesByName 553 whereBuilder.aliasOrderByColumns = doUnion; 554 whereBuilder.buf.setLength(0); 555 sqlQuery.orderBy.accept(whereBuilder); 556 // ends up in WhereBuilder#visitOrderByExpr 557 if (hasSelectCollection) { 558 // also add all pos columns 559 whereBuilder.visitOrderByPosColumns(); 560 } 561 orderBy = whereBuilder.buf.toString(); 562 } 563 } 564 565 String selectWhat = StringUtils.join(whatNames, ", "); 566 if (!doUnion && distinct) { 567 selectWhat = "DISTINCT " + selectWhat; 568 } 569 570 /* 571 * Soft delete. 572 */ 573 574 if (model.getRepositoryDescriptor().getSoftDeleteEnabled()) { 575 whereClauses.add(hierTable.getColumn(Model.MAIN_IS_DELETED_KEY).getFullQuotedName() + " IS NULL"); 576 } 577 578 /* 579 * Security check. 580 */ 581 582 String securityClause = null; 583 List<Serializable> securityParams = new LinkedList<>(); 584 List<Join> securityJoins = new ArrayList<>(2); 585 if (queryFilter.getPrincipals() != null) { 586 Serializable principals = queryFilter.getPrincipals(); 587 Serializable permissions = queryFilter.getPermissions(); 588 if (!dialect.supportsArrays()) { 589 principals = StringUtils.join((String[]) principals, Dialect.ARRAY_SEP); 590 permissions = StringUtils.join((String[]) permissions, Dialect.ARRAY_SEP); 591 } 592 // when using WITH for the query, the main column is referenced 593 // through an alias because of the subselect 594 String id = dialect.supportsWith() ? mainAlias : hierId; 595 if (dialect.supportsReadAcl()) { 596 /* optimized read acl */ 597 // JOIN hierarchy_read_acl _RACL ON hierarchy.id = _RACL.id 598 // JOIN aclr_user_map _ACLRUSERMAP ON _RACL.acl_id = 599 // _ACLRUSERMAP.acl_id 600 // WHERE _ACLRUSERMAP.user_id = md5('bob,Everyone') 601 String racl = dialect.openQuote() + READ_ACL_ALIAS + dialect.closeQuote(); 602 String aclrum = dialect.openQuote() + READ_ACL_USER_MAP_ALIAS + dialect.closeQuote(); 603 securityJoins.add(new Join(Join.INNER, Model.HIER_READ_ACL_TABLE_NAME, READ_ACL_ALIAS, null, id, 604 racl + '.' + Model.HIER_READ_ACL_ID)); 605 securityJoins.add(new Join(Join.INNER, Model.ACLR_USER_MAP_TABLE_NAME, READ_ACL_USER_MAP_ALIAS, 606 null, racl + '.' + Model.HIER_READ_ACL_ACL_ID, aclrum + '.' + Model.ACLR_USER_MAP_ACL_ID)); 607 securityClause = dialect.getReadAclsCheckSql(aclrum + '.' + Model.ACLR_USER_MAP_USER_ID); 608 securityParams.add(principals); 609 } else { 610 securityClause = dialect.getSecurityCheckSql(id); 611 securityParams.add(principals); 612 securityParams.add(permissions); 613 } 614 } 615 616 /* 617 * Resulting select. 618 */ 619 620 if (securityClause != null) { 621 if (dialect.supportsWith()) { 622 // wrap security into a WITH 623 String withTable = dialect.openQuote() + WITH_ALIAS_PREFIX + (statements.size() + 1) 624 + dialect.closeQuote(); 625 withTables.add(withTable); 626 Select withSelect = new Select(null); 627 withSelect.setWhat("*"); 628 String withFrom = withTable; 629 for (Join j : securityJoins) { 630 withFrom += j.toSql(dialect); 631 } 632 withSelect.setFrom(withFrom); 633 withSelect.setWhere(securityClause); 634 withSelects.add(withSelect); 635 withSelectsStatements.add(withSelect.getStatement()); 636 withParams.addAll(securityParams); 637 } else { 638 // add directly to main select 639 joins.addAll(securityJoins); 640 whereClauses.add(securityClause); 641 whereParams.addAll(securityParams); 642 } 643 } 644 645 select = new Select(null); 646 select.setWhat(selectWhat); 647 selectParams.addAll(whatNamesParams); 648 649 StringBuilder fromb = new StringBuilder(from); 650 if (dialect.needsOracleJoins() && doUnion && !securityJoins.isEmpty() && queryAnalyzer.ftCount != 0) { 651 // NXP-5410 we must use Oracle joins 652 // when there's union all + fulltext + security 653 for (Join join : joins) { 654 if (!join.whereClauses.isEmpty()) { 655 // we cannot use Oracle join when there are join filters 656 throw new QueryParseException("Query too complex for Oracle (NXP-5410)"); 657 } 658 } 659 // implicit joins for Oracle 660 List<String> joinClauses = new LinkedList<>(); 661 for (Join join : joins) { 662 fromb.append(", "); 663 fromb.append(join.getTable(dialect)); 664 if (join.tableParam != null) { 665 selectParams.add(join.tableParam); 666 } 667 String joinClause = join.getClause(dialect); 668 if (join.kind == Join.LEFT) { 669 joinClause += "(+)"; // Oracle implicit LEFT JOIN syntax 670 } 671 if (!join.whereClauses.isEmpty()) { 672 joinClause += " AND " + StringUtils.join(join.whereClauses, " AND "); 673 selectParams.addAll(join.whereParams); 674 } 675 joinClauses.add(joinClause); 676 } 677 whereClauses.addAll(0, joinClauses); 678 } else { 679 // else ANSI join 680 Collections.sort(joins); // implicit JOINs last (PostgreSQL) 681 for (Join join : joins) { 682 if (join.tableParam != null) { 683 selectParams.add(join.tableParam); 684 } 685 String joinClause = join.toSql(dialect); 686 // add JOIN filter for complex properties 687 if (!join.whereClauses.isEmpty()) { 688 joinClause += " AND " + StringUtils.join(join.whereClauses, " AND "); 689 selectParams.addAll(join.whereParams); 690 } 691 fromb.append(joinClause); 692 } 693 } 694 695 select.setFrom(fromb.toString()); 696 select.setWhere(StringUtils.join(whereClauses, " AND ")); 697 selectParams.addAll(whereParams); 698 699 statements.add(select.getStatement()); 700 } 701 702 /* 703 * Create the whole select. 704 */ 705 706 if (doUnion) { 707 select = new Select(null); 708 // use aliases for column names 709 String selectWhat = StringUtils.join(aliases, ", "); 710 if (distinct) { 711 selectWhat = "DISTINCT " + selectWhat; 712 } 713 select.setWhat(selectWhat); 714 // note that Derby has bizarre restrictions on parentheses placement 715 // around UNION, see http://issues.apache.org/jira/browse/DERBY-2374 716 String subselect; 717 if (withSelects.isEmpty()) { 718 subselect = StringUtils.join(statements, " UNION ALL "); 719 } else { 720 StringBuilder with = new StringBuilder("WITH "); 721 for (int i = 0; i < statements.size(); i++) { 722 if (i > 0) { 723 with.append(", "); 724 } 725 with.append(withTables.get(i)); 726 with.append(" AS ("); 727 with.append(statements.get(i)); 728 with.append(')'); 729 } 730 with.append(' '); 731 subselect = with.toString() + StringUtils.join(withSelectsStatements, " UNION ALL "); 732 selectParams.addAll(withParams); 733 } 734 String selectFrom = '(' + subselect + ')'; 735 if (dialect.needsAliasForDerivedTable()) { 736 selectFrom += " AS " + dialect.openQuote() + UNION_ALIAS + dialect.closeQuote(); 737 } 738 select.setFrom(selectFrom); 739 } else { 740 // use last (and only) Select in above big loop 741 if (!withSelects.isEmpty()) { 742 select = new Select(null); 743 String with = withTables.get(0) + " AS (" + statements.get(0) + ')'; 744 select.setWith(with); 745 Select withSelect = withSelects.get(0); 746 select.setWhat(withSelect.getWhat()); 747 select.setFrom(withSelect.getFrom()); 748 select.setWhere(withSelect.getWhere()); 749 selectParams.addAll(withParams); 750 } 751 } 752 753 select.setOrderBy(orderBy); 754 fixSelect(select); 755 756 Query q = new Query(); 757 ColumnMapMaker mapMaker = new ColumnMapMaker(whatColumns, whatKeys); 758 q.selectInfo = new SQLInfoSelect(select.getStatement(), whatColumns, mapMaker, null, null); 759 q.selectParams = selectParams; 760 return q; 761 } 762 763 // overridden by specialized query makers that need to tweak some joins 764 protected void addJoin(int kind, String alias, Table table, String column, Table contextTable, String contextColumn, 765 String name, int index, String primaryType) { 766 Column column1 = contextTable.getColumn(contextColumn); 767 Column column2 = table.getColumn(column); 768 Join join = new Join(kind, table.getRealTable().getQuotedName(), alias, null, column1, column2); 769 if (name != null) { 770 String nameCol = table.getColumn(Model.HIER_CHILD_NAME_KEY).getFullQuotedName(); 771 join.addWhereClause(nameCol + " = ?", name); 772 } 773 if (index != -1) { 774 String posCol = table.getColumn(Model.HIER_CHILD_POS_KEY).getFullQuotedName(); 775 join.addWhereClause(posCol + " = ?", Long.valueOf(index)); 776 } 777 if (primaryType != null) { 778 String typeCol = table.getColumn(Model.MAIN_PRIMARY_TYPE_KEY).getFullQuotedName(); 779 join.addWhereClause(typeCol + " = ?", primaryType); 780 } 781 joins.add(join); 782 } 783 784 /** 785 * Gets the table for the given fragmentName, and maybe adds a join if one is not already done. 786 * <p> 787 * LEFT JOIN fragmentName _F123 ON contextHier.id = _F123.id 788 */ 789 protected Table getFragmentTable(Table contextHier, String fragmentName) { 790 return getFragmentTable(Join.LEFT, contextHier, fragmentName, fragmentName, Model.MAIN_KEY, -1, false, null); 791 } 792 793 /** 794 * Adds a more general JOIN: 795 * <p> 796 * (LEFT) JOIN fragmentName _F123 ON contextTable.id = _F123.fragmentColumn 797 */ 798 protected Table getFragmentTable(int joinKind, Table contextTable, String contextKey, String fragmentName, 799 String fragmentColumn, int index, boolean skipJoin, String primaryType) { 800 Table table = propertyFragmentTables.get(contextKey); 801 if (table == null) { 802 Table baseTable = database.getTable(fragmentName); 803 String alias = TABLE_FRAG_ALIAS + ++fragJoinCount; 804 table = new TableAlias(baseTable, alias); 805 propertyFragmentTables.put(contextKey, table); 806 if (!skipJoin) { 807 addJoin(joinKind, alias, table, fragmentColumn, contextTable, Model.MAIN_KEY, null, index, primaryType); 808 } 809 } 810 return table; 811 } 812 813 // overridden by specialized query makers that need to tweak some joins 814 protected void fixInitialJoins() { 815 // to be overridden 816 } 817 818 // overridden by specialized query makers that need to add COUNT 819 protected String getSelectColName(Column col) { 820 return col.getFullQuotedName(); 821 } 822 823 /** key used to extract array index if needed */ 824 protected String getSelectColName(Column col, String key) { 825 String colName = getSelectColName(col); 826 if (col.isArray()) { 827 String[] segments = canonicalXPath(key).split("/"); 828 if (segments.length > 1) { 829 // last segment 830 String segment = segments[segments.length - 1]; 831 if (INDEX.matcher(segment).matches() && !segment.startsWith("*")) { 832 int arrayElementIndex = Integer.parseInt(segment); 833 colName = dialect.getArrayElementString(colName, arrayElementIndex); 834 } 835 } 836 } 837 return colName; 838 } 839 840 // overridden by specialized query makers that need to add COUNT 841 protected void fixWhatColumns(List<Column> whatColumns) { 842 // to be overridden 843 } 844 845 // overridden by specialized query makers that need to add GROUP BY 846 protected void fixSelect(Select select) { 847 // to be overridden 848 } 849 850 protected static boolean findFulltextIndexOrField(Model model, String[] nameref) { 851 boolean useIndex; 852 String name = nameref[0]; 853 if (name.equals(NXQL.ECM_FULLTEXT)) { 854 name = Model.FULLTEXT_DEFAULT_INDEX; 855 useIndex = true; 856 } else { 857 // ecm:fulltext_indexname 858 // ecm:fulltext.field 859 char sep = name.charAt(NXQL.ECM_FULLTEXT.length()); 860 if (sep != '.' && sep != '_') { 861 throw new QueryParseException("Unknown field: " + name); 862 } 863 useIndex = sep == '_'; 864 name = name.substring(NXQL.ECM_FULLTEXT.length() + 1); 865 if (useIndex) { 866 if (!model.getFulltextConfiguration().indexNames.contains(name)) { 867 throw new QueryParseException("No such fulltext index: " + name); 868 } 869 } else { 870 // find if there's an index holding just that field 871 String index = model.getFulltextConfiguration().fieldToIndexName.get(name); 872 if (index != null) { 873 name = index; 874 useIndex = true; 875 } 876 } 877 } 878 nameref[0] = name; 879 return useIndex; 880 } 881 882 // digits or star or star followed by digits, for segments 883 protected final static Pattern INDEX = Pattern.compile("\\d+|\\*|\\*\\d+"); 884 885 // wildcard index in xpath 886 protected final static Pattern HAS_WILDCARD_INDEX = Pattern.compile(".*/(\\*|\\*\\d+)(/.*|$)"); 887 888 // wildcard index at the end in xpath 889 protected final static Pattern HAS_FINAL_WILDCARD_INDEX = Pattern.compile(".*/(\\*|\\*\\d+)"); 890 891 // digits or star or star followed by digits, then slash, for replaceAll 892 protected final static Pattern INDEX_SLASH = Pattern.compile("/(?:\\d+|\\*|\\*\\d+)(/|$)"); 893 894 // non-canonical index syntax, for replaceAll 895 protected final static Pattern NON_CANON_INDEX = Pattern.compile("[^/\\[\\]]+" // name 896 + "\\[(\\d+|\\*|\\*\\d+)\\]" // index in brackets 897 ); 898 899 /** 900 * Canonicalizes a Nuxeo-xpath. 901 * <p> 902 * Replaces {@code a/foo[123]/b} with {@code a/123/b} 903 * <p> 904 * A star or a star followed by digits can be used instead of just the digits as well. 905 * 906 * @param xpath the xpath 907 * @return the canonicalized xpath. 908 */ 909 public static String canonicalXPath(String xpath) { 910 while (xpath.length() > 0 && xpath.charAt(0) == '/') { 911 xpath = xpath.substring(1); 912 } 913 if (xpath.indexOf('[') == -1) { 914 return xpath; 915 } else { 916 return NON_CANON_INDEX.matcher(xpath).replaceAll("$1"); 917 } 918 } 919 920 /** 921 * Turns the xpath into one where all indices have been replaced by *. 922 * 923 * @param xpath the xpath 924 * @return the simple xpath 925 */ 926 public static String simpleXPath(String xpath) { 927 xpath = canonicalXPath(xpath); 928 return INDEX_SLASH.matcher(xpath).replaceAll("/*$1"); 929 } 930 931 public boolean hasWildcardIndex(String xpath) { 932 xpath = canonicalXPath(xpath); 933 return HAS_WILDCARD_INDEX.matcher(xpath).matches(); 934 } 935 936 public boolean hasFinalWildcardIndex(String xpath) { 937 xpath = canonicalXPath(xpath); 938 return HAS_FINAL_WILDCARD_INDEX.matcher(xpath).matches(); 939 } 940 941 /* Turns foo/*123 into foo#123 */ 942 protected static String keyForPos(String name) { 943 int i = name.lastIndexOf('/'); 944 if (i == -1) { 945 throw new RuntimeException("Unexpected name: " + name); 946 } 947 return name.substring(0, i) + "#" + name.substring(i + 2); 948 } 949 950 protected QueryAnalyzer newQueryAnalyzer(FacetFilter facetFilter) { 951 return new QueryAnalyzer(facetFilter); 952 } 953 954 protected static Set<String> getStringLiterals(LiteralList list) { 955 Set<String> set = new HashSet<>(); 956 for (Literal literal : list) { 957 if (!(literal instanceof StringLiteral)) { 958 throw new QueryParseException("requires string literals"); 959 } 960 set.add(((StringLiteral) literal).value); 961 } 962 return set; 963 } 964 965 protected static Serializable getSerializableLiteral(Literal literal) { 966 Serializable value; 967 if (literal instanceof BooleanLiteral) { 968 value = Boolean.valueOf(((BooleanLiteral) literal).value); 969 } else if (literal instanceof DateLiteral) { 970 DateLiteral dLit = (DateLiteral) literal; 971 value = dLit.onlyDate ? dLit.toSqlDate() : dLit.toCalendar(); 972 } else if (literal instanceof DoubleLiteral) { 973 value = Double.valueOf(((DoubleLiteral) literal).value); 974 } else if (literal instanceof IntegerLiteral) { 975 value = Long.valueOf(((IntegerLiteral) literal).value); 976 } else if (literal instanceof StringLiteral) { 977 value = ((StringLiteral) literal).value; 978 } else { 979 throw new QueryParseException("type of literal in list is not recognized: " + literal.getClass()); 980 } 981 return value; 982 } 983 984 protected static List<Serializable> getSerializableLiterals(LiteralList list) { 985 List<Serializable> serList = new ArrayList<>(list.size()); 986 for (Literal literal : list) { 987 serList.add(getSerializableLiteral(literal)); 988 } 989 return serList; 990 } 991 992 /** 993 * Collects various info about the query AST, and rewrites the toplevel AND {@link Predicate}s of the WHERE clause 994 * into a single {@link MultiExpression} for easier analysis. 995 */ 996 protected class QueryAnalyzer extends DefaultQueryVisitor { 997 998 protected FacetFilter facetFilter; 999 1000 protected boolean inSelect; 1001 1002 protected boolean inOrderBy; 1003 1004 protected LinkedList<Predicate> toplevelPredicates; 1005 1006 protected MultiExpression wherePredicate; 1007 1008 /** Do we match only relations (and therefore no proxies). */ 1009 protected boolean onlyRelations; 1010 1011 protected List<String> whatColumnNames; 1012 1013 protected List<String> orderByColumnNames; 1014 1015 /** Do we have a SELECT somelist/* FROM ... */ 1016 protected boolean hasSelectCollection; 1017 1018 protected boolean hasWildcardIndex; 1019 1020 protected boolean orderByHasWildcardIndex; 1021 1022 protected int ftCount; 1023 1024 protected boolean selectScore; 1025 1026 protected boolean orderByScore; 1027 1028 public QueryAnalyzer(FacetFilter facetFilter) { 1029 this.facetFilter = facetFilter; 1030 } 1031 1032 protected void init() { 1033 toplevelPredicates = new LinkedList<>(); 1034 whatColumnNames = new LinkedList<>(); 1035 orderByColumnNames = new LinkedList<>(); 1036 selectCollectionNotNull = new HashSet<>(); 1037 hasWildcardIndex = false; 1038 orderByHasWildcardIndex = false; 1039 ftCount = 0; 1040 selectScore = false; 1041 orderByScore = false; 1042 } 1043 1044 @Override 1045 public void visitQuery(SQLQuery node) { 1046 init(); 1047 if (facetFilter != null) { 1048 addFacetFilterClauses(facetFilter); 1049 } 1050 visitSelectClause(node.select); 1051 visitFromClause(node.from); 1052 visitWhereClause(node.where); // may be null 1053 if (node.orderBy != null) { 1054 visitOrderByClause(node.orderBy); 1055 } 1056 } 1057 1058 public void addFacetFilterClauses(FacetFilter facetFilter) { 1059 for (String mixin : facetFilter.required) { 1060 // every facet is required, not just any of them, 1061 // so do them one by one 1062 // expr = getMixinsMatchExpression(Collections.singleton(facet), 1063 // true); 1064 Predicate predicate = new Predicate(new Reference(NXQL.ECM_MIXINTYPE), Operator.EQ, 1065 new StringLiteral(mixin)); 1066 toplevelPredicates.add(predicate); 1067 } 1068 if (!facetFilter.excluded.isEmpty()) { 1069 // expr = getMixinsMatchExpression(facetFilter.excluded, false); 1070 LiteralList list = new LiteralList(); 1071 for (String mixin : facetFilter.excluded) { 1072 list.add(new StringLiteral(mixin)); 1073 } 1074 Predicate predicate = new Predicate(new Reference(NXQL.ECM_MIXINTYPE), Operator.NOTIN, list); 1075 toplevelPredicates.add(predicate); 1076 } 1077 } 1078 1079 @Override 1080 public void visitSelectClause(SelectClause node) { 1081 inSelect = true; 1082 super.visitSelectClause(node); 1083 inSelect = false; 1084 } 1085 1086 /** 1087 * Finds all the types to take into account (all concrete types being a subtype of the passed types) based on 1088 * the FROM list. 1089 * <p> 1090 * Adds them as a ecm:primaryType match in the toplevel operands. 1091 */ 1092 @Override 1093 public void visitFromClause(FromClause node) { 1094 onlyRelations = true; 1095 Set<String> fromTypes = new HashSet<>(); 1096 FromList elements = node.elements; 1097 for (String typeName : elements.values()) { 1098 if (TYPE_DOCUMENT.equalsIgnoreCase(typeName)) { 1099 typeName = TYPE_DOCUMENT; 1100 } 1101 Set<String> subTypes = model.getDocumentSubTypes(typeName); 1102 if (subTypes == null) { 1103 throw new QueryParseException("Unknown type: " + typeName); 1104 } 1105 fromTypes.addAll(subTypes); 1106 boolean isRelation = false; 1107 do { 1108 if (TYPE_RELATION.equals(typeName)) { 1109 isRelation = true; 1110 break; 1111 } 1112 typeName = model.getDocumentSuperType(typeName); 1113 } while (typeName != null); 1114 onlyRelations = onlyRelations && isRelation; 1115 } 1116 fromTypes.remove(Model.ROOT_TYPE); 1117 LiteralList list = new LiteralList(); 1118 for (String type : fromTypes) { 1119 list.add(new StringLiteral(type)); 1120 } 1121 toplevelPredicates.add(new Predicate(new Reference(NXQL.ECM_PRIMARYTYPE), Operator.IN, list)); 1122 } 1123 1124 @Override 1125 public void visitWhereClause(WhereClause node) { 1126 if (node != null) { 1127 analyzeToplevelPredicates(node.predicate); 1128 } 1129 simplifyToplevelPredicates(); 1130 wherePredicate = new MultiExpression(Operator.AND, toplevelPredicates); 1131 super.visitMultiExpression(wherePredicate); 1132 } 1133 1134 /** 1135 * Process special toplevel ANDed operands: ecm:isProxy 1136 */ 1137 protected void analyzeToplevelPredicates(Predicate predicate) { 1138 Operator op = predicate.operator; 1139 if (op == Operator.AND) { 1140 if (predicate.lvalue instanceof Predicate && predicate.rvalue instanceof Predicate) { 1141 analyzeToplevelPredicates((Predicate) predicate.lvalue); 1142 analyzeToplevelPredicates((Predicate) predicate.rvalue); 1143 return; 1144 } else if (predicate instanceof MultiExpression) { 1145 for (Predicate p : ((MultiExpression) predicate).predicates) { 1146 analyzeToplevelPredicates(p); 1147 } 1148 return; 1149 } 1150 } 1151 if (op == Operator.EQ || op == Operator.NOTEQ) { 1152 // put reference on the left side 1153 if (predicate.rvalue instanceof Reference) { 1154 predicate = new Predicate(predicate.rvalue, op, predicate.lvalue); 1155 } 1156 if (predicate.lvalue instanceof Reference) { 1157 String name = ((Reference) predicate.lvalue).name; 1158 if (NXQL.ECM_ISPROXY.equals(name)) { 1159 analyzeToplevelIsProxy(predicate); 1160 return; 1161 } else if (NXQL.ECM_PROXY_TARGETID.equals(name) || NXQL.ECM_PROXY_VERSIONABLEID.equals(name)) { 1162 analyzeToplevelProxyProperty(predicate); 1163 // no return, we want the node 1164 } 1165 } 1166 } 1167 toplevelPredicates.add(predicate); 1168 } 1169 1170 /** 1171 * Simplify ecm:primaryType positive references, and non-per-instance mixin types. 1172 */ 1173 protected void simplifyToplevelPredicates() { 1174 Set<String> primaryTypes = null; // if defined, required 1175 for (Iterator<Predicate> it = toplevelPredicates.iterator(); it.hasNext();) { 1176 // whenever we don't know how to optimize the expression, 1177 // we just continue the loop 1178 Expression expr = it.next(); 1179 if (!(expr.lvalue instanceof Reference)) { 1180 continue; 1181 } 1182 String name = ((Reference) expr.lvalue).name; 1183 Operator op = expr.operator; 1184 Operand rvalue = expr.rvalue; 1185 if (NXQL.ECM_PRIMARYTYPE.equals(name)) { 1186 if (op != Operator.EQ && op != Operator.IN) { 1187 continue; 1188 } 1189 Set<String> set; 1190 if (op == Operator.EQ) { 1191 if (!(rvalue instanceof StringLiteral)) { 1192 continue; 1193 } 1194 String primaryType = ((StringLiteral) rvalue).value; 1195 set = new HashSet<>(Collections.singleton(primaryType)); 1196 } else { // Operator.IN 1197 if (!(rvalue instanceof LiteralList)) { 1198 continue; 1199 } 1200 set = getStringLiterals((LiteralList) rvalue); 1201 } 1202 if (primaryTypes == null) { 1203 primaryTypes = set; 1204 } else { 1205 primaryTypes.retainAll(set); 1206 } 1207 it.remove(); // expression simplified into primaryTypes set 1208 } else if (NXQL.ECM_MIXINTYPE.equals(name)) { 1209 if (op != Operator.EQ && op != Operator.NOTEQ) { 1210 continue; 1211 } 1212 if (!(rvalue instanceof StringLiteral)) { 1213 continue; 1214 } 1215 String mixin = ((StringLiteral) rvalue).value; 1216 if (!neverPerInstanceMixins.contains(mixin)) { 1217 // mixin per instance -> primary type checks not enough 1218 continue; 1219 } 1220 Set<String> set = model.getMixinDocumentTypes(mixin); 1221 if (primaryTypes == null) { 1222 if (op == Operator.EQ) { 1223 primaryTypes = new HashSet<>(set); // copy 1224 } else { 1225 continue; // unknown positive, no optimization 1226 } 1227 } else { 1228 if (op == Operator.EQ) { 1229 primaryTypes.retainAll(set); 1230 } else { 1231 primaryTypes.removeAll(set); 1232 } 1233 } 1234 it.remove(); // expression simplified into primaryTypes set 1235 } 1236 } 1237 // readd the simplified primary types constraints 1238 if (primaryTypes != null) { 1239 if (primaryTypes.isEmpty()) { 1240 // TODO better removal 1241 primaryTypes.add("__NOSUCHTYPE__"); 1242 } 1243 Predicate predicate; 1244 if (primaryTypes.size() == 1) { 1245 String pt = primaryTypes.iterator().next(); 1246 predicate = new Predicate(new Reference(NXQL.ECM_PRIMARYTYPE), Operator.EQ, new StringLiteral(pt)); 1247 } else { // primaryTypes.size() > 1 1248 LiteralList list = new LiteralList(); 1249 for (String pt : primaryTypes) { 1250 list.add(new StringLiteral(pt)); 1251 } 1252 predicate = new Predicate(new Reference(NXQL.ECM_PRIMARYTYPE), Operator.IN, list); 1253 } 1254 toplevelPredicates.addFirst(predicate); 1255 } 1256 } 1257 1258 protected void analyzeToplevelIsProxy(Expression expr) { 1259 if (!(expr.rvalue instanceof IntegerLiteral)) { 1260 throw new QueryParseException(NXQL.ECM_ISPROXY + " requires literal 0 or 1 as right argument"); 1261 } 1262 long v = ((IntegerLiteral) expr.rvalue).value; 1263 if (v != 0 && v != 1) { 1264 throw new QueryParseException(NXQL.ECM_ISPROXY + " requires literal 0 or 1 as right argument"); 1265 } 1266 boolean isEq = expr.operator == Operator.EQ; 1267 updateProxyClause(Boolean.valueOf((v == 1) == isEq), expr); 1268 } 1269 1270 protected void analyzeToplevelProxyProperty(Expression expr) { 1271 // proxies required 1272 updateProxyClause(Boolean.TRUE, expr); 1273 } 1274 1275 private void updateProxyClause(Boolean value, Expression expr) { 1276 if (proxyClause != null && proxyClause != value) { 1277 throw new QueryCannotMatchException(); 1278 } 1279 proxyClause = value; 1280 proxyClauseReason = expr.toString(); 1281 } 1282 1283 @Override 1284 public void visitExpression(Expression node) { 1285 Reference ref = node.lvalue instanceof Reference ? (Reference) node.lvalue : null; 1286 String name = ref != null ? ref.name : null; 1287 if (name != null && name.startsWith(NXQL.ECM_FULLTEXT) && !NXQL.ECM_FULLTEXT_JOBID.equals(name)) { 1288 visitExpressionFulltext(node, name); 1289 } else { 1290 super.visitExpression(node); 1291 } 1292 } 1293 1294 protected void visitExpressionFulltext(Expression node, String name) { 1295 if (node.operator != Operator.EQ && node.operator != Operator.LIKE) { 1296 throw new QueryParseException(NXQL.ECM_FULLTEXT + " requires = or LIKE operator"); 1297 } 1298 if (!(node.rvalue instanceof StringLiteral)) { 1299 throw new QueryParseException(NXQL.ECM_FULLTEXT + " requires literal string as right argument"); 1300 } 1301 if (model.getRepositoryDescriptor().getFulltextDescriptor().getFulltextSearchDisabled()) { 1302 throw new QueryParseException("Fulltext search disabled by configuration"); 1303 } 1304 String[] nameref = new String[] { name }; 1305 boolean useIndex = findFulltextIndexOrField(model, nameref); 1306 if (useIndex) { 1307 ftCount++; 1308 } 1309 } 1310 1311 @Override 1312 public void visitReference(Reference node) { 1313 boolean hasTag = false; 1314 if (node.cast != null) { 1315 if (!DATE_CAST.equals(node.cast)) { 1316 throw new QueryParseException("Invalid cast: " + node); 1317 } 1318 } 1319 String name = node.name; 1320 if (NXQL.ECM_PATH.equals(name) || // 1321 NXQL.ECM_ANCESTORID.equals(name) || // 1322 NXQL.ECM_ISPROXY.equals(name) || // 1323 NXQL.ECM_MIXINTYPE.equals(name)) { 1324 if (inSelect) { 1325 throw new QueryParseException("Cannot select on column: " + name); 1326 } 1327 if (inOrderBy) { 1328 throw new QueryParseException("Cannot order by column: " + name); 1329 } 1330 } else if (NXQL.ECM_PRIMARYTYPE.equals(name) || // 1331 NXQL.ECM_UUID.equals(name) || // 1332 NXQL.ECM_NAME.equals(name) || // 1333 NXQL.ECM_POS.equals(name) || // 1334 NXQL.ECM_PARENTID.equals(name) || // 1335 NXQL.ECM_LIFECYCLESTATE.equals(name) || // 1336 NXQL.ECM_VERSIONLABEL.equals(name) || // 1337 NXQL.ECM_VERSIONDESCRIPTION.equals(name) || // 1338 NXQL.ECM_VERSIONCREATED.equals(name) || // 1339 NXQL.ECM_VERSION_VERSIONABLEID.equals(name) || // 1340 NXQL.ECM_ISLATESTVERSION.equals(name) || // 1341 NXQL.ECM_ISLATESTMAJORVERSION.equals(name) || // 1342 NXQL.ECM_ISVERSION_OLD.equals(name) || // 1343 NXQL.ECM_ISVERSION.equals(name) || // 1344 NXQL.ECM_ISCHECKEDIN.equals(name) || // 1345 NXQL.ECM_LOCK_OWNER.equals(name) || // 1346 NXQL.ECM_LOCK_CREATED.equals(name) || // 1347 NXQL.ECM_PROXY_TARGETID.equals(name) || // 1348 NXQL.ECM_PROXY_VERSIONABLEID.equals(name) || // 1349 NXQL.ECM_FULLTEXT_JOBID.equals(name)) { 1350 // ok 1351 } else if (NXQL.ECM_ISTRASHED.equals(name)) { 1352 TrashService trashService = Framework.getService(TrashService.class); 1353 if (trashService.hasFeature(TRASHED_STATE_IS_DEDICATED_PROPERTY) 1354 || trashService.hasFeature(TRASHED_STATE_IN_MIGRATION)) { 1355 // ok 1356 } else { 1357 name = NXQL.ECM_LIFECYCLESTATE; // column actually used 1358 } 1359 } else if (NXQL.ECM_TAG.equals(name) || name.startsWith(ECM_TAG_STAR)) { 1360 hasTag = true; 1361 } else if (NXQL.ECM_FULLTEXT_SCORE.equals(name)) { 1362 if (inOrderBy) { 1363 orderByScore = true; 1364 } else if (inSelect) { 1365 selectScore = true; 1366 } else { 1367 throw new QueryParseException("Can only use column in SELECT or ORDER BY: " + name); 1368 } 1369 } else if (name.startsWith(NXQL.ECM_FULLTEXT)) { 1370 if (inSelect) { 1371 throw new QueryParseException("Cannot select on column: " + name); 1372 } 1373 if (inOrderBy) { 1374 throw new QueryParseException("Cannot order by column: " + name); 1375 } 1376 String[] nameref = new String[] { name }; 1377 boolean useIndex = findFulltextIndexOrField(model, nameref); 1378 if (!useIndex) { 1379 // LIKE on a field, continue analysing with that field 1380 name = nameref[0]; 1381 checkProperty(name); // may throw 1382 } 1383 // else all is done in fulltext match info 1384 } else if (name.startsWith(NXQL.ECM_ACL)) { 1385 String simple = simpleXPath(name); 1386 if (simple.equals(ECM_SIMPLE_ACP_PRINCIPAL) || simple.equals(ECM_SIMPLE_ACP_PERMISSION) 1387 || simple.equals(ECM_SIMPLE_ACP_GRANT) || simple.equals(ECM_SIMPLE_ACP_NAME) 1388 || simple.equals(ECM_SIMPLE_ACP_POS) || simple.equals(ECM_SIMPLE_ACP_CREATOR) 1389 || simple.equals(ECM_SIMPLE_ACP_BEGIN) || simple.equals(ECM_SIMPLE_ACP_END) 1390 || simple.equals(ECM_SIMPLE_ACP_STATUS)) { 1391 // ok 1392 } else { 1393 throw new QueryParseException("Unknown field: " + name); 1394 } 1395 } else if (name.startsWith(NXQL.ECM_PREFIX)) { 1396 throw new QueryParseException("Unknown field: " + name); 1397 } else { 1398 checkProperty(name); // may throw 1399 } 1400 1401 if (inSelect) { 1402 whatColumnNames.add(name); 1403 } else if (inOrderBy) { 1404 orderByColumnNames.add(name); 1405 } 1406 if (hasWildcardIndex(name) || hasTag) { 1407 hasWildcardIndex = true; 1408 if (inOrderBy) { 1409 orderByHasWildcardIndex = true; 1410 } 1411 if (hasFinalWildcardIndex(name)) { 1412 if (inSelect) { 1413 hasSelectCollection = true; 1414 selectCollectionNotNull.add(name); 1415 } else if (!inOrderBy) { 1416 // if also explicit in the where clause, then we don't add the IS NOT NULL 1417 selectCollectionNotNull.remove(name); 1418 } 1419 } 1420 } 1421 } 1422 1423 /** 1424 * Checks that a property exists. 1425 * 1426 * @throws QueryParseException if the property doesn't exist 1427 */ 1428 protected void checkProperty(String xpath) { 1429 String simple = simpleXPath(xpath); 1430 ModelProperty prop = model.getPathPropertyInfo(simple); 1431 if (prop == null || prop.isIntermediateSegment()) { 1432 throw new QueryParseException("No such property: " + xpath); 1433 } 1434 } 1435 1436 @Override 1437 public void visitFunction(Function node) { 1438 if (!inSelect) { 1439 throw new QueryParseException("Function not supported in WHERE clause: " + node); 1440 } 1441 String func = node.name.toUpperCase(); 1442 Operand arg; 1443 if (!AGGREGATE_FUNCTIONS.contains(func) || node.args.size() != 1 1444 || !((arg = node.args.get(0)) instanceof Reference)) { 1445 throw new QueryParseException("Function not supported: " + node); 1446 } 1447 visitReference((Reference) arg); 1448 } 1449 1450 @Override 1451 public void visitOrderByClause(OrderByClause node) { 1452 inOrderBy = true; 1453 super.visitOrderByClause(node); 1454 inOrderBy = false; 1455 } 1456 1457 } 1458 1459 /** 1460 * Info about a column and its property type. 1461 */ 1462 protected static class ColumnInfo { 1463 1464 public final Column column; 1465 1466 public final Column posColumn; 1467 1468 public final int arrayElementIndex; 1469 1470 public final boolean isArrayElement; 1471 1472 public final boolean needsSubSelect; 1473 1474 public ColumnInfo(Column column, Column posColumn, int arrayElementIndex, boolean isArrayElement, 1475 boolean isArray) { 1476 this.column = column; 1477 this.posColumn = posColumn; 1478 this.arrayElementIndex = arrayElementIndex; 1479 this.isArrayElement = isArrayElement; 1480 this.needsSubSelect = !isArrayElement && isArray && !column.getType().isArray(); 1481 } 1482 } 1483 1484 protected WhereBuilder newWhereBuilder(boolean isProxies) { 1485 return new WhereBuilder(isProxies); 1486 } 1487 1488 /** 1489 * Builds the database-level WHERE query from the AST. 1490 */ 1491 protected class WhereBuilder extends DefaultQueryVisitor { 1492 1493 public static final String PATH_SEP = "/"; 1494 1495 public final LinkedList<Column> whatColumns = new LinkedList<>(); 1496 1497 public final LinkedList<String> whatKeys = new LinkedList<>(); 1498 1499 public final StringBuilder buf = new StringBuilder(); 1500 1501 // used to assign unique numbers to join aliases for complex property 1502 // wildcard indexes or tags 1503 protected int uniqueJoinIndex = 0; 1504 1505 protected int hierJoinCount = 0; 1506 1507 // path prefix -> hier table to join, 1508 protected Map<String, Table> propertyHierTables = new HashMap<>(); 1509 1510 protected final boolean isProxies; 1511 1512 protected boolean aliasOrderByColumns; 1513 1514 // internal fields 1515 1516 protected boolean allowSubSelect; 1517 1518 protected boolean inSelect; 1519 1520 protected boolean inOrderBy; 1521 1522 protected int ftJoinNumber; 1523 1524 protected FulltextMatchInfo ftMatchInfo; 1525 1526 // true when visiting the rvalue of an id expression 1527 protected boolean visitingId; 1528 1529 // arrayColumnName or prefix/arrayColumnName -> array column subquery to join 1530 protected Map<String, ArraySubQuery> propertyArraySubQueries = new HashMap<>(); 1531 1532 protected int arraySubQueryJoinCount = 0; 1533 1534 // additional collection pos columns on which to ORDER BY 1535 protected Map<String, Column> posColumns = new LinkedHashMap<>(0); 1536 1537 // collection pos columns for which we already did an ORDER BY 1538 protected List<Column> posColumnsInOrderBy = new ArrayList<>(); 1539 1540 public WhereBuilder(boolean isProxies) { 1541 this.isProxies = isProxies; 1542 } 1543 1544 protected int getUniqueJoinIndex() { 1545 return ++uniqueJoinIndex; 1546 } 1547 1548 /** 1549 * Gets the arraySubquery for the given arrayColumn in the given contextKey, and maybe adds a JOIN if one is not 1550 * already done. 1551 * <p> 1552 * LEFT JOIN (SELECT id, UNNEST(somecol) AS item, generate_subscripts(somecol, 1) AS pos FROM someschema) _A1 ON 1553 * _A1.id = hierarchy.id 1554 */ 1555 protected ArraySubQuery getArraySubQuery(Table contextHier, String contextKey, Column arrayColumn, 1556 boolean skipJoin) { 1557 ArraySubQuery arraySubQuery = propertyArraySubQueries.get(contextKey); 1558 if (arraySubQuery == null) { 1559 String alias = SUBQUERY_ARRAY_ALIAS + ++arraySubQueryJoinCount; 1560 arraySubQuery = dialect.getArraySubQuery(arrayColumn, alias); 1561 propertyArraySubQueries.put(contextKey, arraySubQuery); 1562 if (!skipJoin) { 1563 Join join = new Join(Join.LEFT, arraySubQuery.toSql(), alias, null, 1564 arraySubQuery.getSubQueryIdColumn().getFullQuotedName(), 1565 contextHier.getColumn(Model.MAIN_KEY).getFullQuotedName()); 1566 joins.add(join); 1567 } 1568 } 1569 return arraySubQuery; 1570 } 1571 1572 protected ColumnInfo getSpecialColumnInfo(String name) { 1573 String propertyName = null; 1574 Table table = null; 1575 String fragmentKey = null; 1576 if (NXQL.ECM_UUID.equals(name)) { 1577 table = hierTable; 1578 fragmentKey = Model.MAIN_KEY; 1579 } else if (NXQL.ECM_NAME.equals(name)) { 1580 table = hierTable; 1581 fragmentKey = Model.HIER_CHILD_NAME_KEY; 1582 } else if (NXQL.ECM_POS.equals(name)) { 1583 table = hierTable; 1584 fragmentKey = Model.HIER_CHILD_POS_KEY; 1585 } else if (NXQL.ECM_PARENTID.equals(name)) { 1586 table = hierTable; 1587 fragmentKey = Model.HIER_PARENT_KEY; 1588 } else if (NXQL.ECM_ISVERSION_OLD.equals(name) || NXQL.ECM_ISVERSION.equals(name)) { 1589 table = hierTable; 1590 fragmentKey = Model.MAIN_IS_VERSION_KEY; 1591 } else if (NXQL.ECM_ISCHECKEDIN.equals(name)) { 1592 table = hierTable; 1593 fragmentKey = Model.MAIN_CHECKED_IN_KEY; 1594 } else if (NXQL.ECM_PRIMARYTYPE.equals(name)) { 1595 table = dataHierTable; 1596 fragmentKey = Model.MAIN_PRIMARY_TYPE_KEY; 1597 } else if (NXQL.ECM_MIXINTYPE.equals(name)) { 1598 // toplevel ones have been extracted by the analyzer 1599 throw new QueryParseException("Cannot use non-toplevel " + name + " in query"); 1600 } else if (NXQL.ECM_ISTRASHED.equals(name)) { 1601 table = hierTable; 1602 fragmentKey = Model.MAIN_IS_TRASHED_KEY; 1603 } else if (NXQL.ECM_LIFECYCLESTATE.equals(name)) { 1604 propertyName = Model.MISC_LIFECYCLE_STATE_PROP; 1605 } else if (NXQL.ECM_VERSIONLABEL.equals(name)) { 1606 propertyName = Model.VERSION_LABEL_PROP; 1607 } else if (NXQL.ECM_VERSIONDESCRIPTION.equals(name)) { 1608 propertyName = Model.VERSION_DESCRIPTION_PROP; 1609 } else if (NXQL.ECM_VERSIONCREATED.equals(name)) { 1610 propertyName = Model.VERSION_CREATED_PROP; 1611 } else if (NXQL.ECM_VERSION_VERSIONABLEID.equals(name)) { 1612 propertyName = Model.VERSION_VERSIONABLE_PROP; 1613 } else if (NXQL.ECM_ISLATESTVERSION.equals(name)) { 1614 propertyName = Model.VERSION_IS_LATEST_PROP; 1615 } else if (NXQL.ECM_ISLATESTMAJORVERSION.equals(name)) { 1616 propertyName = Model.VERSION_IS_LATEST_MAJOR_PROP; 1617 } else if (NXQL.ECM_LOCK_OWNER.equals(name)) { 1618 propertyName = Model.LOCK_OWNER_PROP; 1619 } else if (NXQL.ECM_LOCK_CREATED.equals(name)) { 1620 propertyName = Model.LOCK_CREATED_PROP; 1621 } else if (NXQL.ECM_PROXY_TARGETID.equals(name)) { 1622 table = proxyTable; 1623 fragmentKey = Model.PROXY_TARGET_KEY; 1624 } else if (NXQL.ECM_PROXY_VERSIONABLEID.equals(name)) { 1625 table = proxyTable; 1626 fragmentKey = Model.PROXY_VERSIONABLE_KEY; 1627 } else if (NXQL.ECM_FULLTEXT_JOBID.equals(name)) { 1628 propertyName = Model.FULLTEXT_JOBID_PROP; 1629 } else if (NXQL.ECM_FULLTEXT_SCORE.equals(name)) { 1630 throw new QueryParseException(NXQL.ECM_FULLTEXT_SCORE + " cannot be used in WHERE clause"); 1631 } else if (name.startsWith(NXQL.ECM_FULLTEXT)) { 1632 throw new QueryParseException(NXQL.ECM_FULLTEXT + " must be used as left-hand operand"); 1633 } else if (NXQL.ECM_TAG.equals(name) || name.startsWith(ECM_TAG_STAR)) { 1634 // checking the migration service is a hack but we can't really do better here without refactoring 1635 MigrationStatus status = Framework.getService(MigrationService.class).getStatus("tag-storage"); 1636 boolean facetedTag = "facets".equals(status.getState()); 1637 if (facetedTag) { 1638 String newName = FACETED_TAG + "/*"; 1639 if (name.startsWith(ECM_TAG_STAR)) { 1640 newName += name.substring(ECM_TAG_STAR.length()) + "/" + FACETED_TAG_LABEL; 1641 } else { 1642 newName += "1/" + FACETED_TAG_LABEL; 1643 } 1644 return getRegularColumnInfo(newName); 1645 } else { 1646 /* 1647 * JOIN relation _F1 ON hierarchy.id = _F1.source JOIN hierarchy _F2 ON _F1.id = _F2.id AND 1648 * _F2.primarytype = 'Tagging' and returns _F2.name 1649 */ 1650 String suffix; 1651 if (name.startsWith(ECM_TAG_STAR)) { 1652 suffix = name.substring(ECM_TAG_STAR.length()); 1653 if (suffix.isEmpty()) { 1654 // any 1655 suffix = "/*-" + getUniqueJoinIndex(); 1656 } else { 1657 // named 1658 suffix = "/*" + suffix; 1659 } 1660 } else { 1661 suffix = ""; 1662 } 1663 String relContextKey = "_tag_relation" + suffix; 1664 Table rel = getFragmentTable(Join.INNER, dataHierTable, relContextKey, RELATION_TABLE, "source", -1, 1665 false, null); 1666 String fragmentName = Model.HIER_TABLE_NAME; 1667 fragmentKey = Model.HIER_CHILD_NAME_KEY; 1668 String hierContextKey = "_tag_hierarchy" + suffix; 1669 table = getFragmentTable(Join.INNER, rel, hierContextKey, fragmentName, Model.MAIN_KEY, -1, false, 1670 TYPE_TAGGING); 1671 } 1672 } else if (name.startsWith(NXQL.ECM_ACL)) { 1673 // get index and suffix; we already checked that there are two slashes 1674 int i = name.indexOf('/'); 1675 int j = name.lastIndexOf('/'); 1676 String index = name.substring(i + 1, j); // like "*1" 1677 String suffix = name.substring(j + 1); // like "principal" 1678 // re-create pseudo property name, which the Model mapped to a ModelProperty 1679 String newName = NXQL.ECM_ACL + '.' + suffix + '/' + index; 1680 return getRegularColumnInfo(newName); 1681 } else { 1682 throw new QueryParseException("No such property: " + name); 1683 } 1684 if (table == null) { 1685 ModelProperty propertyInfo = model.getPropertyInfo(propertyName); 1686 String fragmentName = propertyInfo.fragmentName; 1687 fragmentKey = propertyInfo.fragmentKey; 1688 if (fragmentName.equals(Model.HIER_TABLE_NAME)) { 1689 table = dataHierTable; 1690 } else { 1691 table = getFragmentTable(dataHierTable, fragmentName); 1692 } 1693 } 1694 Column column = table.getColumn(fragmentKey); // NOSONAR 1695 return new ColumnInfo(column, null, -1, false, false); 1696 } 1697 1698 /** 1699 * Finds info about column (special or not). 1700 */ 1701 public ColumnInfo getColumnInfo(String name) { 1702 if (name.startsWith(NXQL.ECM_PREFIX)) { 1703 return getSpecialColumnInfo(name); 1704 } else { 1705 return getRegularColumnInfo(name); 1706 } 1707 } 1708 1709 /** 1710 * Gets column information for a regular property. 1711 * <p> 1712 * Accumulates info about joins needed to get to this property. 1713 * <p> 1714 * IMPORTANT: THIS MUST NOT BE CALLED TWICE ON THE SAME PROPERTY as some structures are updated (joins, 1715 * counters). 1716 * 1717 * @throws QueryParseException if the property doesn't exist 1718 */ 1719 protected ColumnInfo getRegularColumnInfo(String xpath) { 1720 Table contextHier; 1721 if (model.isProxySchemaPath(xpath)) { 1722 // if xpath for proxy, then change contextHier to proxyTable 1723 if (proxyTable != null) { 1724 contextHier = hierTable; 1725 } else { 1726 contextHier = dataHierTable; 1727 } 1728 } else { 1729 contextHier = dataHierTable; 1730 } 1731 xpath = canonicalXPath(xpath); 1732 String[] segments = xpath.split("/"); 1733 String simple = null; // simplified prefix to match model 1734 String contextKey = null; // prefix used as key for table to join 1735 String segment; 1736 ModelProperty prop; 1737 for (int i = 0; i < segments.length; i++) { 1738 segment = segments[i]; 1739 simple = simple == null ? segment : simple + '/' + segment; 1740 String contextStart = contextKey == null ? "" : contextKey + '/'; 1741 String contextSuffix = ""; 1742 int index = -1; 1743 boolean star = false; 1744 boolean isArrayElement = false; 1745 if (i < segments.length - 1) { 1746 // check if we have a complex list index in the next 1747 // position 1748 String next = segments[i + 1]; 1749 if (INDEX.matcher(next).matches()) { 1750 isArrayElement = true; 1751 if (next.startsWith("*")) { 1752 star = true; 1753 next = next.substring(1); 1754 } 1755 if (!next.isEmpty()) { 1756 index = Integer.parseInt(next); 1757 } 1758 // swallow next segment 1759 i++; 1760 simple += "/*"; 1761 if (star) { 1762 if (index == -1) { 1763 // any 1764 contextSuffix = "/*-" + getUniqueJoinIndex(); 1765 } else { 1766 // named 1767 contextSuffix = "/*" + index; 1768 } 1769 index = -1; 1770 } else { 1771 contextSuffix = "/" + index; 1772 } 1773 } 1774 } 1775 1776 prop = model.getPathPropertyInfo(simple); 1777 if (prop == null) { 1778 throw new QueryParseException("No such property: " + xpath); 1779 } 1780 if (i < segments.length - 1) { 1781 // non-final segment 1782 if (!prop.isIntermediateSegment()) { 1783 throw new QueryParseException("No such property: " + xpath); 1784 } 1785 segment = prop.getIntermediateSegment(); // canonical 1786 contextKey = contextStart + segment + contextSuffix; 1787 Table table = propertyHierTables.get(contextKey); 1788 if (table == null) { 1789 // none existing 1790 // create new Join with hierarchy from previous 1791 String alias = TABLE_HIER_ALIAS + ++hierJoinCount; 1792 table = new TableAlias(dataHierTable, alias); 1793 propertyHierTables.put(contextKey, table); 1794 addJoin(Join.LEFT, alias, table, Model.HIER_PARENT_KEY, contextHier, Model.MAIN_KEY, segment, 1795 index, null); 1796 } 1797 contextHier = table; 1798 } else { 1799 // last segment 1800 if (prop.isIntermediateSegment()) { 1801 throw new QueryParseException("No such property: " + xpath); 1802 } 1803 Table table = database.getTable(prop.fragmentName); 1804 Column column = table.getColumn(prop.fragmentKey); 1805 boolean skipJoin = !isArrayElement && prop.propertyType.isArray() && !column.isArray(); 1806 Column posColumn = null; 1807 if (column.isArray() && star) { 1808 contextKey = contextStart + segment + contextSuffix; 1809 ArraySubQuery arraySubQuery = getArraySubQuery(contextHier, contextKey, column, skipJoin); 1810 column = arraySubQuery.getSubQueryValueColumn(); 1811 } else { 1812 // use fragment name, not segment, for table context key 1813 contextKey = contextStart + prop.fragmentName + contextSuffix; 1814 // maybe do an INNER JOIN instead of a LEFT JOIN if we require this column in the results 1815 // and there is no WHERE clause mentioning it 1816 int joinKind = selectCollectionNotNull.contains(xpath) ? Join.INNER : Join.LEFT; 1817 table = getFragmentTable(joinKind, contextHier, contextKey, prop.fragmentName, Model.MAIN_KEY, 1818 column.isArray() ? -1 : index, skipJoin, null); 1819 column = table.getColumn(prop.fragmentKey); 1820 if (star) { 1821 // we'll have to do an ORDER BY on the pos column as well 1822 posColumn = table.getColumn(Model.COLL_TABLE_POS_KEY); 1823 posColumns.put(keyForPos(xpath), posColumn); 1824 } 1825 } 1826 return new ColumnInfo(column, posColumn, column.isArray() ? index : -1, isArrayElement, 1827 prop.propertyType.isArray()); 1828 } 1829 } 1830 throw new AssertionError("not reached"); 1831 } 1832 1833 @Override 1834 public void visitQuery(SQLQuery node) { 1835 super.visitQuery(node); 1836 // intentionally does not set limit or offset in the query 1837 } 1838 1839 @Override 1840 public void visitSelectClause(SelectClause node) { 1841 inSelect = true; 1842 super.visitSelectClause(node); 1843 inSelect = false; 1844 } 1845 1846 @Override 1847 public void visitMultiExpression(MultiExpression node) { 1848 buf.append('('); 1849 for (Iterator<Predicate> it = node.predicates.iterator(); it.hasNext();) { 1850 it.next().accept(this); 1851 if (it.hasNext()) { 1852 node.operator.accept(this); 1853 } 1854 } 1855 buf.append(')'); 1856 } 1857 1858 @Override 1859 public void visitExpression(Expression node) { 1860 buf.append('('); 1861 Reference ref = node.lvalue instanceof Reference ? (Reference) node.lvalue : null; 1862 String name = ref != null ? ref.name : null; 1863 String cast = ref != null ? ref.cast : null; 1864 Operand rvalue = node.rvalue; 1865 if (DATE_CAST.equals(cast)) { 1866 checkDateLiteralForCast(rvalue, node); 1867 } 1868 Operator op = node.operator; 1869 if (op == Operator.STARTSWITH) { 1870 visitExpressionStartsWith(node); 1871 } else if (NXQL.ECM_PATH.equals(name)) { 1872 visitExpressionEcmPath(node); 1873 } else if (NXQL.ECM_ANCESTORID.equals(name)) { 1874 visitExpressionAncestorId(node); 1875 } else if (NXQL.ECM_ISPROXY.equals(name)) { 1876 visitExpressionIsProxy(node); 1877 } else if (NXQL.ECM_ISVERSION_OLD.equals(name) || NXQL.ECM_ISVERSION.equals(name)) { 1878 visitExpressionWhereFalseIsNull(node); 1879 } else if (NXQL.ECM_ISCHECKEDIN.equals(name) || NXQL.ECM_ISLATESTVERSION.equals(name) 1880 || NXQL.ECM_ISLATESTMAJORVERSION.equals(name)) { 1881 visitExpressionWhereFalseMayBeNull(node); 1882 } else if (NXQL.ECM_ISTRASHED.equals(name)) { 1883 visitExpressionIsTrashed(node); 1884 } else if (NXQL.ECM_MIXINTYPE.equals(name)) { 1885 visitExpressionMixinType(node); 1886 } else if (name != null && name.startsWith(NXQL.ECM_FULLTEXT) && !NXQL.ECM_FULLTEXT_JOBID.equals(name)) { 1887 visitExpressionFulltext(node, name); 1888 } else if ((op == Operator.EQ || op == Operator.NOTEQ || op == Operator.IN || op == Operator.NOTIN 1889 || op == Operator.LIKE || op == Operator.NOTLIKE || op == Operator.ILIKE 1890 || op == Operator.NOTILIKE)) { 1891 ColumnInfo info = name == null ? null : getColumnInfo(name); 1892 // node.lvalue must not be accepted from now on 1893 if (info != null && info.needsSubSelect) { 1894 // use EXISTS with subselect clause 1895 boolean direct = op == Operator.EQ || op == Operator.IN || op == Operator.LIKE 1896 || op == Operator.ILIKE; 1897 Operator directOp = direct ? op 1898 : (op == Operator.NOTEQ ? Operator.EQ 1899 : op == Operator.NOTIN ? Operator.IN 1900 : op == Operator.NOTLIKE ? Operator.LIKE : Operator.ILIKE); 1901 if (!direct) { 1902 buf.append("NOT "); 1903 } 1904 generateExistsStart(buf, info.column.getTable()); 1905 allowSubSelect = true; 1906 visitColumnExpression(info.column, directOp, rvalue, cast, name, info.arrayElementIndex); 1907 allowSubSelect = false; 1908 generateExistsEnd(buf); 1909 } else if (info != null) { 1910 // boolean literals have to be translated according the 1911 // database dialect 1912 if (info.column.getType() == ColumnType.BOOLEAN) { 1913 rvalue = getBooleanLiteral(rvalue); 1914 } 1915 visitColumnExpression(info.column, op, rvalue, cast, name, info.arrayElementIndex); 1916 } else { 1917 super.visitExpression(node); 1918 } 1919 } else if (op == Operator.BETWEEN || op == Operator.NOTBETWEEN) { 1920 LiteralList l = (LiteralList) rvalue; 1921 if (DATE_CAST.equals(cast)) { 1922 checkDateLiteralForCast(l.get(0), node); 1923 checkDateLiteralForCast(l.get(1), node); 1924 } 1925 node.lvalue.accept(this); 1926 buf.append(' '); 1927 op.accept(this); 1928 buf.append(' '); 1929 l.get(0).accept(this); 1930 buf.append(" AND "); 1931 l.get(1).accept(this); 1932 } else { 1933 super.visitExpression(node); 1934 } 1935 buf.append(')'); 1936 } 1937 1938 protected Operand getBooleanLiteral(Operand rvalue) { 1939 if (!(rvalue instanceof IntegerLiteral)) { 1940 throw new QueryParseException("Boolean expressions require literal 0 or 1 as right argument"); 1941 } 1942 long v = ((IntegerLiteral) rvalue).value; 1943 if (v != 0 && v != 1) { 1944 throw new QueryParseException("Boolean expressions require literal 0 or 1 as right argument"); 1945 } 1946 return new BooleanLiteral(v == 1); 1947 } 1948 1949 protected void visitColumnExpression(Column column, Operator op, Operand rvalue, String cast, String lvalueName, 1950 int arrayElementIndex) { 1951 if (op == Operator.EQ || op == Operator.NOTEQ || op == Operator.IN || op == Operator.NOTIN) { 1952 visitExpressionEqOrIn(column, op, rvalue, cast, arrayElementIndex); 1953 } else if (op == Operator.LIKE || op == Operator.NOTLIKE) { 1954 visitExpressionLike(column, op, rvalue, lvalueName, arrayElementIndex); 1955 } else if (op == Operator.ILIKE || op == Operator.NOTILIKE) { 1956 visitExpressionIlike(column, op, rvalue, lvalueName, arrayElementIndex); 1957 } else { 1958 visitSimpleExpression(column, op, rvalue, cast, -1); 1959 } 1960 } 1961 1962 protected void visitSimpleExpression(Column column, Operator op, Operand rvalue, String cast, 1963 int arrayElementIndex) { 1964 visitReference(column, cast, arrayElementIndex); 1965 op.accept(this); 1966 boolean oldVisitingId = visitingId; 1967 visitingId = column.getType().isId(); 1968 rvalue.accept(this); 1969 visitingId = oldVisitingId; 1970 } 1971 1972 /** 1973 * This operand is going to be used with a lvalue that has a DATE cast, so if it's a date literal make sure it's 1974 * not a TIMESTAMP. 1975 */ 1976 protected void checkDateLiteralForCast(Operand value, Expression node) { 1977 if (value instanceof DateLiteral && !((DateLiteral) value).onlyDate) { 1978 throw new QueryParseException("DATE() cast must be used with DATE literal, not TIMESTAMP: " + node); 1979 } 1980 } 1981 1982 protected void generateExistsStart(StringBuilder buf, Table table) { 1983 String tableName; 1984 if (table.isAlias()) { 1985 tableName = table.getRealTable().getQuotedName() + " " + table.getQuotedName(); 1986 } else { 1987 tableName = table.getQuotedName(); 1988 } 1989 buf.append(String.format("EXISTS (SELECT 1 FROM %s WHERE %s = %s AND ", tableName, 1990 dataHierTable.getColumn(Model.MAIN_KEY).getFullQuotedName(), 1991 table.getColumn(Model.MAIN_KEY).getFullQuotedName())); 1992 } 1993 1994 protected void generateExistsEnd(StringBuilder buf) { 1995 buf.append(")"); 1996 } 1997 1998 protected void visitExpressionStartsWith(Expression node) { 1999 if (!(node.lvalue instanceof Reference)) { 2000 throw new QueryParseException("Illegal left argument for " + Operator.STARTSWITH + ": " + node.lvalue); 2001 } 2002 if (!(node.rvalue instanceof StringLiteral)) { 2003 throw new QueryParseException(Operator.STARTSWITH + " requires literal path as right argument"); 2004 } 2005 String path = ((StringLiteral) node.rvalue).value; 2006 if (path.length() > 1 && path.endsWith(PATH_SEP)) { 2007 path = path.substring(0, path.length() - PATH_SEP.length()); 2008 } 2009 String name = ((Reference) node.lvalue).name; 2010 if (NXQL.ECM_PATH.equals(name)) { 2011 visitExpressionStartsWithPath(path); 2012 } else { 2013 visitExpressionStartsWithNonPath(node, path); 2014 } 2015 } 2016 2017 protected void visitExpressionStartsWithPath(String path) { 2018 // find the id from the path 2019 Serializable id = pathResolver.getIdForPath(path); 2020 if (id == null) { 2021 // no such path, always return a false 2022 // TODO remove the expression more intelligently from the parse 2023 // tree 2024 buf.append("0=1"); 2025 } else { 2026 // id is always valid, no need to pass it as argument to getInTreeSql 2027 buf.append(dialect.getInTreeSql(hierTable.getColumn(Model.MAIN_KEY).getFullQuotedName(), null)); 2028 whereParams.add(id); 2029 } 2030 } 2031 2032 protected void visitExpressionStartsWithNonPath(Expression node, String path) { 2033 String name = ((Reference) node.lvalue).name; 2034 ColumnInfo info = getColumnInfo(name); 2035 if (info.needsSubSelect) { 2036 // use EXISTS with subselect clause 2037 generateExistsStart(buf, info.column.getTable()); 2038 } 2039 buf.append('('); 2040 visitExpressionEqOrIn(info.column, Operator.EQ, new StringLiteral(path), null, -1); 2041 visitOperator(Operator.OR); 2042 // TODO escape % chars... 2043 visitExpressionLike(info.column, Operator.LIKE, new StringLiteral(path + PATH_SEP + '%'), name, -1); 2044 buf.append(')'); 2045 if (info.needsSubSelect) { 2046 generateExistsEnd(buf); 2047 } 2048 } 2049 2050 protected void visitExpressionEcmPath(Expression node) { 2051 if (node.operator != Operator.EQ && node.operator != Operator.NOTEQ) { 2052 throw new QueryParseException(NXQL.ECM_PATH + " requires = or <> operator"); 2053 } 2054 if (!(node.rvalue instanceof StringLiteral)) { 2055 throw new QueryParseException(NXQL.ECM_PATH + " requires literal path as right argument"); 2056 } 2057 String path = ((StringLiteral) node.rvalue).value; 2058 if (path.length() > 1 && path.endsWith(PATH_SEP)) { 2059 path = path.substring(0, path.length() - PATH_SEP.length()); 2060 } 2061 Serializable id = pathResolver.getIdForPath(path); 2062 if (id == null) { 2063 // no such path, always return a false 2064 // TODO remove the expression more intelligently from the parse 2065 // tree 2066 buf.append("0=1"); 2067 } else { 2068 visitReference(hierTable.getColumn(Model.MAIN_KEY)); 2069 visitOperator(node.operator); 2070 visitId(model.idToString(id)); 2071 } 2072 } 2073 2074 protected void visitExpressionAncestorId(Expression node) { 2075 if (node.operator != Operator.EQ && node.operator != Operator.NOTEQ) { 2076 throw new QueryParseException(NXQL.ECM_ANCESTORID + " requires = or <> operator"); 2077 } 2078 if (!(node.rvalue instanceof StringLiteral)) { 2079 throw new QueryParseException(NXQL.ECM_ANCESTORID + " requires literal id as right argument"); 2080 } 2081 boolean not = node.operator == Operator.NOTEQ; 2082 String id = ((StringLiteral) node.rvalue).value; 2083 if (not) { 2084 buf.append("(NOT ("); 2085 } 2086 String sql = dialect.getInTreeSql(hierTable.getColumn(Model.MAIN_KEY).getFullQuotedName(), id); 2087 if (sql == null) { 2088 buf.append("0=1"); 2089 } else { 2090 buf.append(sql); 2091 whereParams.add(id); 2092 } 2093 if (not) { 2094 buf.append("))"); 2095 } 2096 } 2097 2098 protected void visitExpressionIsProxy(Expression node) { 2099 boolean bool = getBooleanRValue(NXQL.ECM_ISPROXY, node); 2100 buf.append(isProxies == bool ? "1=1" : "0=1"); 2101 } 2102 2103 protected void visitExpressionWhereFalseIsNull(Expression node) { 2104 String name = ((Reference) node.lvalue).name; 2105 boolean bool = getBooleanRValue(name, node); 2106 node.lvalue.accept(this); 2107 if (bool) { 2108 buf.append(" = "); 2109 buf.append(dialect.toBooleanValueString(true)); 2110 } else { 2111 buf.append(" IS NULL"); 2112 } 2113 } 2114 2115 protected void visitExpressionWhereFalseMayBeNull(Expression node) { 2116 String name = ((Reference) node.lvalue).name; 2117 boolean bool = getBooleanRValue(name, node); 2118 if (bool) { 2119 node.lvalue.accept(this); 2120 buf.append(" = "); 2121 buf.append(dialect.toBooleanValueString(true)); 2122 } else { 2123 buf.append('('); 2124 node.lvalue.accept(this); 2125 buf.append(" = "); 2126 buf.append(dialect.toBooleanValueString(false)); 2127 buf.append(" OR "); 2128 node.lvalue.accept(this); 2129 buf.append(" IS NULL)"); 2130 } 2131 } 2132 2133 protected void visitExpressionIsTrashed(Expression node) { 2134 TrashService trashService = Framework.getService(TrashService.class); 2135 if (trashService.hasFeature(TRASHED_STATE_IS_DEDUCED_FROM_LIFECYCLE)) { 2136 visitExpressionIsTrashedOnLifeCycle(node); 2137 } else if (trashService.hasFeature(TRASHED_STATE_IN_MIGRATION)) { 2138 visitExpressionIsTrashedOnLifeCycle(node); 2139 buf.append(" OR "); 2140 visitExpressionWhereFalseMayBeNull(node); 2141 } else if (trashService.hasFeature(TRASHED_STATE_IS_DEDICATED_PROPERTY)) { 2142 visitExpressionWhereFalseMayBeNull(node); 2143 } else { 2144 throw new UnsupportedOperationException("TrashService is in an unknown state"); 2145 } 2146 } 2147 2148 protected void visitExpressionIsTrashedOnLifeCycle(Expression node) { 2149 String name = ((Reference) node.lvalue).name; 2150 boolean bool = getBooleanRValue(name, node); 2151 Operator op = bool ? Operator.EQ : Operator.NOTEQ; 2152 visitReference(new Reference(NXQL.ECM_LIFECYCLESTATE)); 2153 visitOperator(op); 2154 visitStringLiteral(LifeCycleConstants.DELETED_STATE); 2155 } 2156 2157 private boolean getBooleanRValue(String name, Expression node) { 2158 if (node.operator != Operator.EQ && node.operator != Operator.NOTEQ) { 2159 throw new QueryParseException(name + " requires = or <> operator"); 2160 } 2161 long v; 2162 if (!(node.rvalue instanceof IntegerLiteral) 2163 || ((v = ((IntegerLiteral) node.rvalue).value) != 0 && v != 1)) { 2164 throw new QueryParseException(name + " requires literal 0 or 1 as right argument"); 2165 } 2166 return node.operator == Operator.EQ ^ v == 0; 2167 } 2168 2169 /** 2170 * Include or exclude mixins. 2171 * <p> 2172 * include: primarytype IN (... types with Foo or Bar ...) OR mixintypes LIKE '%Foo%' OR mixintypes LIKE '%Bar%' 2173 * <p> 2174 * exclude: primarytype IN (... types without Foo or Bar ...) AND (mixintypes NOT LIKE '%Foo%' AND mixintypes 2175 * NOT LIKE '%Bar%' OR mixintypes IS NULL) 2176 */ 2177 protected void visitExpressionMixinType(Expression expr) { 2178 boolean include; 2179 Set<String> mixins; 2180 2181 Operator op = expr.operator; 2182 if (op == Operator.EQ || op == Operator.NOTEQ) { 2183 include = op == Operator.EQ; 2184 if (!(expr.rvalue instanceof StringLiteral)) { 2185 throw new QueryParseException(NXQL.ECM_MIXINTYPE + " = requires literal string as right argument"); 2186 } 2187 String value = ((StringLiteral) expr.rvalue).value; 2188 mixins = Collections.singleton(value); 2189 } else if (op == Operator.IN || op == Operator.NOTIN) { 2190 include = op == Operator.IN; 2191 if (!(expr.rvalue instanceof LiteralList)) { 2192 throw new QueryParseException(NXQL.ECM_MIXINTYPE + " = requires string list as right argument"); 2193 } 2194 mixins = getStringLiterals((LiteralList) expr.rvalue); 2195 } else { 2196 throw new QueryParseException(NXQL.ECM_MIXINTYPE + " unknown operator: " + op); 2197 } 2198 2199 /* 2200 * Primary types 2201 */ 2202 2203 Set<String> types; 2204 if (include) { 2205 types = new HashSet<>(); 2206 for (String mixin : mixins) { 2207 types.addAll(model.getMixinDocumentTypes(mixin)); 2208 } 2209 } else { 2210 types = new HashSet<>(model.getDocumentTypes()); 2211 for (String mixin : mixins) { 2212 types.removeAll(model.getMixinDocumentTypes(mixin)); 2213 } 2214 } 2215 2216 /* 2217 * Instance mixins 2218 */ 2219 2220 Set<String> instanceMixins = new HashSet<>(); 2221 for (String mixin : mixins) { 2222 if (!neverPerInstanceMixins.contains(mixin)) { 2223 instanceMixins.add(mixin); 2224 } 2225 } 2226 2227 /* 2228 * SQL generation 2229 */ 2230 2231 if (!types.isEmpty()) { 2232 Column col = dataHierTable.getColumn(Model.MAIN_PRIMARY_TYPE_KEY); 2233 visitReference(col); 2234 buf.append(" IN "); 2235 buf.append('('); 2236 for (Iterator<String> it = types.iterator(); it.hasNext();) { 2237 visitStringLiteral(it.next()); 2238 if (it.hasNext()) { 2239 buf.append(", "); 2240 } 2241 } 2242 buf.append(')'); 2243 2244 if (!instanceMixins.isEmpty()) { 2245 buf.append(include ? " OR " : " AND "); 2246 } 2247 } 2248 2249 if (!instanceMixins.isEmpty()) { 2250 buf.append('('); 2251 Column mixinsColumn = dataHierTable.getColumn(Model.MAIN_MIXIN_TYPES_KEY); 2252 String[] returnParam = new String[1]; 2253 for (Iterator<String> it = instanceMixins.iterator(); it.hasNext();) { 2254 String mixin = it.next(); 2255 String sql = dialect.getMatchMixinType(mixinsColumn, mixin, include, returnParam); 2256 buf.append(sql); 2257 if (returnParam[0] != null) { 2258 whereParams.add(returnParam[0]); 2259 } 2260 if (it.hasNext()) { 2261 buf.append(include ? " OR " : " AND "); 2262 } 2263 } 2264 if (!include) { 2265 buf.append(" OR "); 2266 visitReference(mixinsColumn); 2267 buf.append(" IS NULL"); 2268 } 2269 buf.append(')'); 2270 } 2271 2272 if (types.isEmpty() && instanceMixins.isEmpty()) { 2273 buf.append(include ? "0=1" : "0=0"); 2274 } 2275 } 2276 2277 protected void visitExpressionFulltext(Expression node, String name) { 2278 String[] nameref = new String[] { name }; 2279 boolean useIndex = findFulltextIndexOrField(model, nameref); 2280 name = nameref[0]; 2281 if (useIndex) { 2282 // use actual fulltext query using a dedicated index 2283 String fulltextQuery = ((StringLiteral) node.rvalue).value; 2284 fulltextQuery = dialect.getDialectFulltextQuery(fulltextQuery); 2285 ftJoinNumber++; 2286 Column mainColumn = dataHierTable.getColumn(Model.MAIN_KEY); 2287 FulltextMatchInfo info = dialect.getFulltextScoredMatchInfo(fulltextQuery, name, ftJoinNumber, 2288 mainColumn, model, database); 2289 ftMatchInfo = info; 2290 if (info.joins != null) { 2291 joins.addAll(info.joins); 2292 } 2293 buf.append(info.whereExpr); 2294 if (info.whereExprParam != null) { 2295 whereParams.add(info.whereExprParam); 2296 } 2297 } else { 2298 // single field matched with ILIKE 2299 log.warn("No fulltext index configured for field " + name + ", falling back on LIKE query"); 2300 String value = ((StringLiteral) node.rvalue).value; 2301 2302 // fulltext translation into pseudo-LIKE syntax 2303 Set<String> words = FullTextUtils.parseFullText(value, false); 2304 if (words.isEmpty()) { 2305 // only stop words or empty 2306 value = "DONTMATCHANYTHINGFOREMPTYQUERY"; 2307 } else { 2308 value = "%" + StringUtils.join(new ArrayList<>(words), "%") + "%"; 2309 } 2310 2311 Reference ref = new Reference(name); 2312 if (dialect.supportsIlike()) { 2313 visitReference(ref); 2314 buf.append(" ILIKE "); 2315 visitStringLiteral(value); 2316 } else { 2317 buf.append("LOWER("); 2318 visitReference(ref); 2319 buf.append(") LIKE "); 2320 visitStringLiteral(value); 2321 } 2322 } 2323 } 2324 2325 protected void visitExpressionEqOrIn(Column column, Operator op, Operand rvalue, String cast, 2326 int arrayElementIndex) { 2327 if (column.isArray() && arrayElementIndex == -1) { 2328 List<Serializable> params; 2329 if (rvalue instanceof Literal) { 2330 Serializable param = getSerializableLiteral((Literal) rvalue); 2331 params = Collections.singletonList(param); 2332 } else { 2333 params = getSerializableLiterals((LiteralList) rvalue); 2334 } 2335 boolean positive = op == Operator.EQ || op == Operator.IN; 2336 String sql = dialect.getArrayInSql(column, cast, positive, params); 2337 buf.append(sql); 2338 whereParams.addAll(params); 2339 } else { 2340 visitSimpleExpression(column, op, rvalue, cast, arrayElementIndex); 2341 } 2342 } 2343 2344 protected void visitExpressionLike(Column column, Operator op, Operand rvalue, String lvalueName, 2345 int arrayElementIndex) { 2346 if (column.isArray() && arrayElementIndex == -1) { 2347 if (lvalueName == null) { 2348 throw new AssertionError("Name is required when lvalue is an array"); 2349 } 2350 boolean positive = (op == Operator.LIKE); 2351 String sql = dialect.getArrayLikeSql(column, lvalueName, positive, dataHierTable); 2352 buf.append(sql); 2353 whereParams.add(getSerializableLiteral((Literal) rvalue)); 2354 } else { 2355 visitSimpleExpression(column, op, rvalue, null, arrayElementIndex); 2356 addLikeEscaping(); 2357 } 2358 } 2359 2360 protected void visitExpressionIlike(Column column, Operator op, Operand rvalue, String lvalueName, 2361 int arrayElementIndex) { 2362 if (column.isArray() && arrayElementIndex == -1) { 2363 if (lvalueName == null) { 2364 throw new AssertionError("Name is required when lvalue is an array"); 2365 } 2366 boolean positive = op == Operator.ILIKE; 2367 String sql = dialect.getArrayIlikeSql(column, lvalueName, positive, dataHierTable); 2368 buf.append(sql); 2369 whereParams.add(getSerializableLiteral((Literal) rvalue)); 2370 } else if (dialect.supportsIlike()) { 2371 visitSimpleExpression(column, op, rvalue, null, arrayElementIndex); 2372 } else { 2373 buf.append("LOWER("); 2374 visitReference(column, arrayElementIndex); 2375 buf.append(") "); 2376 if (op == Operator.NOTILIKE) { 2377 buf.append("NOT "); 2378 } 2379 buf.append("LIKE"); 2380 buf.append(" LOWER("); 2381 rvalue.accept(this); 2382 buf.append(")"); 2383 addLikeEscaping(); 2384 } 2385 } 2386 2387 protected void addLikeEscaping() { 2388 String escape = dialect.getLikeEscaping(); 2389 if (escape != null) { 2390 buf.append(escape); 2391 } 2392 } 2393 2394 @Override 2395 public void visitOperator(Operator node) { 2396 if (node != Operator.NOT) { 2397 buf.append(' '); 2398 } 2399 buf.append(node.toString()); 2400 buf.append(' '); 2401 } 2402 2403 @Override 2404 public void visitReference(Reference node) { 2405 String name = node.name; 2406 if (NXQL.ECM_FULLTEXT_SCORE.equals(name)) { 2407 visitScore(); 2408 return; 2409 } 2410 ColumnInfo info = getColumnInfo(name); 2411 if (info.needsSubSelect && !allowSubSelect) { 2412 String msg = inOrderBy ? "Cannot use collection %s in ORDER BY clause" 2413 : "Can only use collection %s with =, <>, IN or NOT IN clause"; 2414 throw new QueryParseException(String.format(msg, name)); 2415 } 2416 if (inSelect) { 2417 whatColumns.add(info.column); 2418 whatKeys.add(name); 2419 if (info.posColumn != null) { 2420 whatColumns.add(info.posColumn); 2421 whatKeys.add(keyForPos(name)); 2422 } 2423 } else { 2424 visitReference(info.column, node.cast); 2425 if (inOrderBy && info.posColumn != null) { 2426 buf.append(", "); 2427 visitReference(info.posColumn); 2428 posColumnsInOrderBy.add(info.posColumn); 2429 } 2430 } 2431 } 2432 2433 protected void visitReference(Column column) { 2434 visitReference(column, null, -1); 2435 } 2436 2437 protected void visitReference(Column column, String cast) { 2438 visitReference(column, cast, -1); 2439 } 2440 2441 protected void visitReference(Column column, int arrayElementIndex) { 2442 visitReference(column, null, arrayElementIndex); 2443 } 2444 2445 protected void visitReference(Column column, String cast, int arrayElementIndex) { 2446 if (DATE_CAST.equals(cast) && column.getType() != ColumnType.TIMESTAMP) { 2447 throw new QueryParseException("Cannot cast to " + cast + ": " + column); 2448 } 2449 String qname = column.getFullQuotedName(); 2450 if (arrayElementIndex != -1) { 2451 if (column.isArray()) { 2452 qname = dialect.getArrayElementString(qname, arrayElementIndex); 2453 } else { 2454 throw new QueryParseException( 2455 "Cannot use array index " + arrayElementIndex + " for non-array column " + column); 2456 } 2457 } 2458 // some databases (Derby) can't do comparisons on CLOB 2459 if (column.getJdbcType() == Types.CLOB) { 2460 String colFmt = dialect.getClobCast(inOrderBy); 2461 if (colFmt != null) { 2462 qname = String.format(colFmt, qname, Integer.valueOf(255)); 2463 } 2464 } 2465 if (cast != null) { 2466 // only DATE cast for now 2467 String fmt = dialect.getDateCast(); 2468 buf.append(String.format(fmt, qname)); 2469 } else { 2470 buf.append(qname); 2471 } 2472 } 2473 2474 @Override 2475 public void visitLiteralList(LiteralList node) { 2476 buf.append('('); 2477 for (Iterator<Literal> it = node.iterator(); it.hasNext();) { 2478 it.next().accept(this); 2479 if (it.hasNext()) { 2480 buf.append(", "); 2481 } 2482 } 2483 buf.append(')'); 2484 } 2485 2486 @Override 2487 public void visitDateLiteral(DateLiteral node) { 2488 buf.append('?'); 2489 if (node.onlyDate) { 2490 whereParams.add(node.toSqlDate()); 2491 } else { 2492 whereParams.add(node.toCalendar()); 2493 } 2494 } 2495 2496 @Override 2497 public void visitStringLiteral(StringLiteral node) { 2498 if (visitingId) { 2499 visitId(node.value); 2500 } else { 2501 visitStringLiteral(node.value); 2502 } 2503 } 2504 2505 // wrap the string so that the mapper can detect it 2506 // and map to an actual database id 2507 protected void visitId(String string) { 2508 buf.append('?'); 2509 whereParams.add(new WrappedId(string)); 2510 } 2511 2512 public void visitStringLiteral(String string) { 2513 buf.append('?'); 2514 whereParams.add(string); 2515 } 2516 2517 @Override 2518 public void visitDoubleLiteral(DoubleLiteral node) { 2519 buf.append(node.value); 2520 } 2521 2522 @Override 2523 public void visitIntegerLiteral(IntegerLiteral node) { 2524 buf.append(node.value); 2525 } 2526 2527 @Override 2528 public void visitBooleanLiteral(BooleanLiteral node) { 2529 buf.append('?'); 2530 whereParams.add(Boolean.valueOf(node.value)); 2531 } 2532 2533 @Override 2534 public void visitFunction(Function node) { 2535 String func = node.name.toUpperCase(); 2536 Reference ref = (Reference) node.args.get(0); 2537 ref.accept(this); // whatColumns / whatKeys for column 2538 2539 // replace column info with aggregate 2540 Column col = whatColumns.removeLast(); 2541 String key = whatKeys.removeLast(); 2542 final String aggFQN = func + "(" + col.getFullQuotedName() + ")"; 2543 final ColumnType aggType = getAggregateType(func, col.getType()); 2544 final int aggJdbcType = dialect.getJDBCTypeAndString(aggType).jdbcType; 2545 Column cc = new Column(col, col.getTable()) { 2546 private static final long serialVersionUID = 1L; 2547 2548 @Override 2549 public String getFullQuotedName() { 2550 return aggFQN; 2551 } 2552 2553 @Override 2554 public ColumnType getType() { 2555 return aggType; 2556 } 2557 2558 @Override 2559 public int getJdbcType() { 2560 return aggJdbcType; 2561 } 2562 }; 2563 whatColumns.add(cc); 2564 whatKeys.add(func + "(" + key + ")"); 2565 } 2566 2567 protected void visitScore() { 2568 if (inSelect) { 2569 Column col = new Column(hierTable, null, ColumnType.DOUBLE, null); 2570 whatColumns.add(col); 2571 whatKeys.add(NXQL.ECM_FULLTEXT_SCORE); 2572 } else { 2573 buf.append(aliasesByName.get(NXQL.ECM_FULLTEXT_SCORE)); 2574 } 2575 } 2576 2577 protected ColumnType getAggregateType(String func, ColumnType arg) { 2578 if (COUNT_FUNCTION.equals(func)) { 2579 return ColumnType.LONG; 2580 } 2581 if (AVG_FUNCTION.equals(func)) { 2582 return ColumnType.DOUBLE; 2583 } 2584 // SUM, MIN, MAX 2585 return arg; 2586 } 2587 2588 @Override 2589 public void visitOrderByList(OrderByList node) { 2590 inOrderBy = true; 2591 for (OrderByExpr obe : node) { 2592 if (buf.length() != 0) { 2593 // we can do this because we generate in an initially empty buffer 2594 buf.append(", "); 2595 } 2596 obe.accept(this); 2597 } 2598 inOrderBy = false; 2599 } 2600 2601 public void visitOrderByPosColumns() { 2602 inOrderBy = true; 2603 for (Entry<String, Column> es : posColumns.entrySet()) { 2604 Column col = es.getValue(); 2605 if (posColumnsInOrderBy.contains(col)) { 2606 continue; 2607 } 2608 if (buf.length() != 0) { 2609 buf.append(", "); 2610 } 2611 int length = buf.length(); 2612 visitReference(col); 2613 if (aliasOrderByColumns) { 2614 // but don't use generated values 2615 // make the ORDER BY clause uses the aliases instead 2616 buf.setLength(length); 2617 String alias = aliasesByName.get(es.getKey()); 2618 buf.append(alias); 2619 } 2620 } 2621 inOrderBy = false; 2622 } 2623 2624 @Override 2625 public void visitOrderByExpr(OrderByExpr node) { 2626 int length = buf.length(); 2627 // generates needed joins 2628 super.visitOrderByExpr(node); // visit reference 2629 if (aliasOrderByColumns) { 2630 // but don't use generated values 2631 // make the ORDER BY clause uses the aliases instead 2632 buf.setLength(length); 2633 buf.append(aliasesByName.get(node.reference.name)); 2634 } 2635 if (node.isDescending) { 2636 buf.append(dialect.getDescending()); 2637 } 2638 } 2639 2640 } 2641 2642}