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