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