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