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