001/* 002 * (C) Copyright 2006-2017 Nuxeo SA (http://nuxeo.com/) and others. 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * http://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 * 016 * Contributors: 017 * Florent Guillaume 018 */ 019package org.nuxeo.ecm.core.opencmis.impl.server; 020 021import java.io.Serializable; 022import java.math.BigDecimal; 023import java.math.BigInteger; 024import java.security.Principal; 025import java.sql.ResultSet; 026import java.sql.SQLException; 027import java.util.ArrayList; 028import java.util.Arrays; 029import java.util.Collections; 030import java.util.HashMap; 031import java.util.HashSet; 032import java.util.Iterator; 033import java.util.LinkedList; 034import java.util.List; 035import java.util.Map; 036import java.util.Map.Entry; 037import java.util.Set; 038import java.util.TreeSet; 039import java.util.concurrent.atomic.AtomicInteger; 040 041import org.antlr.runtime.RecognitionException; 042import org.antlr.runtime.tree.Tree; 043import org.apache.chemistry.opencmis.commons.PropertyIds; 044import org.apache.chemistry.opencmis.commons.definitions.PropertyDefinition; 045import org.apache.chemistry.opencmis.commons.definitions.TypeDefinition; 046import org.apache.chemistry.opencmis.commons.definitions.TypeDefinitionContainer; 047import org.apache.chemistry.opencmis.commons.enums.BaseTypeId; 048import org.apache.chemistry.opencmis.commons.enums.Cardinality; 049import org.apache.chemistry.opencmis.commons.exceptions.CmisRuntimeException; 050import org.apache.chemistry.opencmis.commons.impl.dataobjects.PropertyDecimalDefinitionImpl; 051import org.apache.chemistry.opencmis.server.support.query.AbstractPredicateWalker; 052import org.apache.chemistry.opencmis.server.support.query.CmisQlStrictLexer; 053import org.apache.chemistry.opencmis.server.support.query.CmisSelector; 054import org.apache.chemistry.opencmis.server.support.query.ColumnReference; 055import org.apache.chemistry.opencmis.server.support.query.FunctionReference; 056import org.apache.chemistry.opencmis.server.support.query.FunctionReference.CmisQlFunction; 057import org.apache.chemistry.opencmis.server.support.query.QueryObject; 058import org.apache.chemistry.opencmis.server.support.query.QueryUtilStrict; 059import org.apache.chemistry.opencmis.server.support.query.QueryObject.JoinSpec; 060import org.apache.chemistry.opencmis.server.support.query.QueryObject.SortSpec; 061import org.apache.commons.lang3.StringUtils; 062import org.apache.commons.logging.Log; 063import org.apache.commons.logging.LogFactory; 064import org.nuxeo.ecm.core.api.LifeCycleConstants; 065import org.nuxeo.ecm.core.api.security.SecurityConstants; 066import org.nuxeo.ecm.core.opencmis.impl.util.TypeManagerImpl; 067import org.nuxeo.ecm.core.query.QueryFilter; 068import org.nuxeo.ecm.core.query.QueryParseException; 069import org.nuxeo.ecm.core.schema.FacetNames; 070import org.nuxeo.ecm.core.security.SecurityPolicy; 071import org.nuxeo.ecm.core.security.SecurityPolicy.QueryTransformer; 072import org.nuxeo.ecm.core.security.SecurityPolicyService; 073import org.nuxeo.ecm.core.storage.sql.Model; 074import org.nuxeo.ecm.core.storage.sql.ModelProperty; 075import org.nuxeo.ecm.core.storage.sql.Session.PathResolver; 076import org.nuxeo.ecm.core.storage.sql.jdbc.QueryMaker; 077import org.nuxeo.ecm.core.storage.sql.jdbc.SQLInfo; 078import org.nuxeo.ecm.core.storage.sql.jdbc.SQLInfo.MapMaker; 079import org.nuxeo.ecm.core.storage.sql.jdbc.SQLInfo.SQLInfoSelect; 080import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column; 081import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database; 082import org.nuxeo.ecm.core.storage.sql.jdbc.db.Select; 083import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table; 084import org.nuxeo.ecm.core.storage.sql.jdbc.db.TableAlias; 085import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.Dialect; 086import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.Dialect.FulltextMatchInfo; 087import org.nuxeo.runtime.api.Framework; 088import org.nuxeo.runtime.services.config.ConfigurationService; 089 090/** 091 * Transformer of CMISQL queries into real SQL queries for the actual database. 092 */ 093public class CMISQLQueryMaker implements QueryMaker { 094 095 private static final Log log = LogFactory.getLog(CMISQLQueryMaker.class); 096 097 public static final String TYPE = "CMISQL"; 098 099 public static final String CMIS_PREFIX = "cmis:"; 100 101 public static final String NX_PREFIX = "nuxeo:"; 102 103 public static final String DC_FRAGMENT_NAME = "dublincore"; 104 105 public static final String DC_TITLE_KEY = "title"; 106 107 public static final String DC_DESCRIPTION_KEY = "description"; 108 109 public static final String DC_CREATOR_KEY = "creator"; 110 111 public static final String DC_CREATED_KEY = "created"; 112 113 public static final String DC_MODIFIED_KEY = "modified"; 114 115 public static final String DC_LAST_CONTRIBUTOR_KEY = "lastContributor"; 116 117 public static final String REL_FRAGMENT_NAME = "relation"; 118 119 public static final String REL_SOURCE_KEY = "source"; 120 121 public static final String REL_TARGET_KEY = "target"; 122 123 // list of SQL column where NULL (missing value) should be treated as 124 // Boolean.FALSE 125 public static final Set<String> NULL_IS_FALSE_COLUMNS = new HashSet<>( 126 Arrays.asList(Model.HIER_TABLE_NAME + " " + Model.MAIN_IS_VERSION_KEY, 127 Model.VERSION_TABLE_NAME + " " + Model.VERSION_IS_LATEST_KEY, 128 Model.VERSION_TABLE_NAME + " " + Model.VERSION_IS_LATEST_MAJOR_KEY, 129 Model.HIER_TABLE_NAME + " " + Model.MAIN_CHECKED_IN_KEY)); 130 131 /** 132 * These mixins never match an instance mixin when used in a clause nuxeo:secondaryObjectTypeIds = 'foo' 133 */ 134 protected static final Set<String> MIXINS_NOT_PER_INSTANCE = new HashSet<>( 135 Arrays.asList(FacetNames.FOLDERISH, FacetNames.HIDDEN_IN_NAVIGATION)); 136 137 protected Database database; 138 139 protected Dialect dialect; 140 141 protected Model model; 142 143 protected Table hierTable; 144 145 public boolean skipDeleted = true; 146 147 // ----- filled during walks of the clauses ----- 148 149 protected QueryUtilStrict queryUtil; 150 151 protected QueryObject query; 152 153 protected FulltextMatchInfo fulltextMatchInfo; 154 155 protected Set<String> lifecycleWhereClauseQualifiers = new HashSet<>(); 156 157 protected Set<String> mixinTypeWhereClauseQualifiers = new HashSet<>(); 158 159 /** Qualifier to type. */ 160 protected Map<String, String> qualifierToType = new HashMap<>(); 161 162 /** Qualifier to canonical qualifier (correlation name). */ 163 protected Map<String, String> canonicalQualifier = new HashMap<>(); 164 165 /** Map of qualifier -> fragment -> table */ 166 protected Map<String, Map<String, Table>> allTables = new HashMap<>(); 167 168 /** All qualifiers used (includes virtual columns) */ 169 protected Set<String> allQualifiers = new HashSet<>(); 170 171 /** The qualifiers which correspond to versionable types. */ 172 protected Set<String> versionableQualifiers = new HashSet<>(); 173 174 /** The columns we'll actually request from the database. */ 175 protected List<SqlColumn> realColumns = new LinkedList<>(); 176 177 /** Parameters for above (for SCORE expressions on some databases) */ 178 protected List<String> realColumnsParams = new LinkedList<>(); 179 180 /** The non-real-columns we'll return as well. */ 181 protected Map<String, ColumnReference> virtualColumns = new HashMap<>(); 182 183 /** Type info returned to caller. */ 184 protected Map<String, PropertyDefinition<?>> typeInfo = null; 185 186 /** Search only latest version = !searchAllVersions. */ 187 protected boolean searchLatestVersion = false; 188 189 /** used for diagnostic when using DISTINCT */ 190 protected List<String> virtualColumnNames = new LinkedList<>(); 191 192 /** 193 * Column corresponding to a returned value computed from an actual SQL expression. 194 */ 195 public static class SqlColumn { 196 197 /** Column name or expression passed to SQL statement. */ 198 public final String sql; 199 200 /** Column used to get the value from the result set. */ 201 public final Column column; 202 203 /** Key for the value returned to the caller. */ 204 public final String key; 205 206 public SqlColumn(String sql, Column column, String key) { 207 this.sql = sql; 208 this.column = column; 209 this.key = key; 210 } 211 } 212 213 @Override 214 public String getName() { 215 return TYPE; 216 } 217 218 @Override 219 public boolean accepts(String queryType) { 220 return queryType.equals(TYPE); 221 } 222 223 /** 224 * {@inheritDoc} 225 * <p> 226 * The optional parameters must be passed: {@code params[0]} is the {@link NuxeoCmisService}, optional 227 * {@code params[1]} is a type info map, optional {@code params[2]} is searchAllVersions (default 228 * {@code Boolean.TRUE} for this method). 229 */ 230 @Override 231 public Query buildQuery(SQLInfo sqlInfo, Model model, PathResolver pathResolver, String statement, 232 QueryFilter queryFilter, Object... params) { 233 database = sqlInfo.database; 234 dialect = sqlInfo.dialect; 235 this.model = model; 236 NuxeoCmisService service = (NuxeoCmisService) params[0]; 237 if (params.length > 1) { 238 typeInfo = (Map<String, PropertyDefinition<?>>) params[1]; 239 } 240 if (params.length > 2) { 241 Boolean searchAllVersions = (Boolean) params[2]; 242 searchLatestVersion = Boolean.FALSE.equals(searchAllVersions); 243 } 244 TypeManagerImpl typeManager = service.getTypeManager(); 245 246 boolean addSystemColumns = true; // TODO 247 248 hierTable = database.getTable(Model.HIER_TABLE_NAME); 249 250 statement = applySecurityPolicyQueryTransformers(service, queryFilter.getPrincipal(), statement); 251 try { 252 queryUtil = new QueryUtilStrict(statement, typeManager, new AnalyzingWalker(), false); 253 queryUtil.processStatement(); 254 query = queryUtil.getQueryObject(); 255 } catch (RecognitionException e) { 256 throw new QueryParseException(queryUtil.getErrorMessage(e), e); 257 } 258 259 resolveQualifiers(); 260 261 // now resolve column selectors to actual database columns 262 for (CmisSelector sel : query.getSelectReferences()) { 263 recordSelectSelector(sel); 264 } 265 for (CmisSelector sel : query.getJoinReferences()) { 266 recordSelector(sel, ClauseType.JOIN); 267 } 268 for (CmisSelector sel : query.getWhereReferences()) { 269 recordSelector(sel, ClauseType.WHERE); 270 } 271 for (SortSpec spec : query.getOrderBys()) { 272 recordSelector(spec.getSelector(), ClauseType.ORDER_BY); 273 } 274 275 findVersionableQualifiers(); 276 277 boolean distinct = false; // TODO extension 278 addSystemColumns(addSystemColumns, distinct); 279 280 /* 281 * Find info about fragments needed. 282 */ 283 284 List<String> whereClauses = new LinkedList<String>(); 285 List<Serializable> whereParams = new LinkedList<Serializable>(); 286 287 /* 288 * Walk joins. 289 */ 290 291 List<JoinSpec> joins = query.getJoins(); 292 StringBuilder from = new StringBuilder(); 293 List<Serializable> fromParams = new LinkedList<Serializable>(); 294 for (int njoin = -1; njoin < joins.size(); njoin++) { 295 JoinSpec join; 296 boolean outerJoin; 297 String alias; 298 if (njoin == -1) { 299 join = null; 300 outerJoin = false; 301 alias = query.getMainTypeAlias(); 302 } else { 303 join = joins.get(njoin); 304 outerJoin = join.kind.equals("LEFT") || join.kind.equals("RIGHT"); 305 alias = join.alias; 306 } 307 308 String typeQueryName = qualifierToType.get(alias); 309 String qual = canonicalQualifier.get(alias); 310 Table qualHierTable = getTable(hierTable, qual); 311 312 // determine relevant primary types 313 314 List<String> types = new ArrayList<String>(); 315 TypeDefinition td = query.getTypeDefinitionFromQueryName(typeQueryName); 316 if (td.getParentTypeId() != null) { 317 // don't add abstract root types 318 types.add(td.getId()); 319 } 320 LinkedList<TypeDefinitionContainer> typesTodo = new LinkedList<TypeDefinitionContainer>(); 321 typesTodo.addAll(typeManager.getTypeDescendants(td.getId(), -1, Boolean.TRUE)); 322 // recurse to get all subtypes 323 TypeDefinitionContainer tc; 324 while ((tc = typesTodo.poll()) != null) { 325 types.add(tc.getTypeDefinition().getId()); 326 typesTodo.addAll(tc.getChildren()); 327 } 328 if (types.isEmpty()) { 329 // shoudn't happen 330 types = Collections.singletonList("__NOSUCHTYPE__"); 331 } 332 // build clause 333 StringBuilder qms = new StringBuilder(); 334 for (int i = 0; i < types.size(); i++) { 335 if (i != 0) { 336 qms.append(", "); 337 } 338 qms.append("?"); 339 } 340 String primaryTypeClause = String.format("%s IN (%s)", 341 qualHierTable.getColumn(Model.MAIN_PRIMARY_TYPE_KEY).getFullQuotedName(), qms); 342 343 // table this join is about 344 345 Table table; 346 if (join == null) { 347 table = qualHierTable; 348 } else { 349 // find which table in onLeft/onRight refers to current 350 // qualifier 351 table = null; 352 for (ColumnReference col : Arrays.asList(join.onLeft, join.onRight)) { 353 if (alias.equals(col.getQualifier())) { 354 // TODO match with canonical qualifier instead? 355 table = ((Column) col.getInfo()).getTable(); 356 break; 357 } 358 } 359 if (table == null) { 360 throw new QueryParseException("Bad query, qualifier not found: " + qual); 361 } 362 } 363 String tableName; 364 if (table.isAlias()) { 365 tableName = table.getRealTable().getQuotedName() + " " + table.getQuotedName(); 366 } else { 367 tableName = table.getQuotedName(); 368 } 369 boolean isRelation = table.getKey().equals(REL_FRAGMENT_NAME); 370 371 // join clause on requested columns 372 373 boolean primaryTypeClauseDone = false; 374 375 if (join == null) { 376 from.append(tableName); 377 } else { 378 if (outerJoin) { 379 from.append(" "); 380 from.append(join.kind); 381 } 382 from.append(" JOIN "); 383 from.append(tableName); 384 from.append(" ON ("); 385 from.append(((Column) join.onLeft.getInfo()).getFullQuotedName()); 386 from.append(" = "); 387 from.append(((Column) join.onRight.getInfo()).getFullQuotedName()); 388 if (outerJoin && table.getKey().equals(Model.HIER_TABLE_NAME)) { 389 // outer join, type check must be part of JOIN 390 from.append(" AND "); 391 from.append(primaryTypeClause); 392 fromParams.addAll(types); 393 primaryTypeClauseDone = true; 394 } 395 from.append(")"); 396 } 397 398 // join other fragments for qualifier 399 400 String tableMainId = table.getColumn(Model.MAIN_KEY).getFullQuotedName(); 401 402 for (Table t : allTables.get(qual).values()) { 403 if (t.getKey().equals(table.getKey())) { 404 // already done above 405 continue; 406 } 407 String n; 408 if (t.isAlias()) { 409 n = t.getRealTable().getQuotedName() + " " + t.getQuotedName(); 410 } else { 411 n = t.getQuotedName(); 412 } 413 from.append(" LEFT JOIN "); 414 from.append(n); 415 from.append(" ON ("); 416 from.append(t.getColumn(Model.MAIN_KEY).getFullQuotedName()); 417 from.append(" = "); 418 from.append(tableMainId); 419 if (outerJoin && t.getKey().equals(Model.HIER_TABLE_NAME)) { 420 // outer join, type check must be part of JOIN 421 from.append(" AND "); 422 from.append(primaryTypeClause); 423 fromParams.addAll(types); 424 primaryTypeClauseDone = true; 425 } 426 from.append(")"); 427 } 428 429 // primary type clause, if not included in a JOIN 430 431 if (!primaryTypeClauseDone) { 432 whereClauses.add(primaryTypeClause); 433 whereParams.addAll(types); 434 } 435 436 // lifecycle not deleted filter 437 438 if (skipDeleted) { 439 ModelProperty propertyInfo = model.getPropertyInfo(Model.MISC_LIFECYCLE_STATE_PROP); 440 Column lscol = getTable(database.getTable(propertyInfo.fragmentName), qual).getColumn( 441 propertyInfo.fragmentKey); 442 String lscolName = lscol.getFullQuotedName(); 443 whereClauses.add(String.format("(%s <> ? OR %s IS NULL)", lscolName, lscolName)); 444 whereParams.add(LifeCycleConstants.DELETED_STATE); 445 } 446 447 // searchAllVersions filter 448 449 boolean versionable = versionableQualifiers.contains(qual); 450 if (searchLatestVersion && versionable) { 451 // add islatestversion = true 452 Table ver = getTable(database.getTable(Model.VERSION_TABLE_NAME), qual); 453 Column latestvercol = ver.getColumn(Model.VERSION_IS_LATEST_KEY); 454 String latestvercolName = latestvercol.getFullQuotedName(); 455 whereClauses.add(String.format("(%s = ?)", latestvercolName)); 456 whereParams.add(Boolean.TRUE); 457 } 458 459 // security check 460 461 boolean checkSecurity = !isRelation && queryFilter.getPrincipals() != null; 462 if (checkSecurity) { 463 Serializable principals; 464 Serializable permissions; 465 if (dialect.supportsArrays()) { 466 principals = queryFilter.getPrincipals(); 467 permissions = queryFilter.getPermissions(); 468 } else { 469 principals = StringUtils.join(queryFilter.getPrincipals(), '|'); 470 permissions = StringUtils.join(queryFilter.getPermissions(), '|'); 471 } 472 if (dialect.supportsReadAcl()) { 473 /* optimized read acl */ 474 String readAclTable; 475 String readAclTableAlias; 476 String aclrumTable; 477 String aclrumTableAlias; 478 if (joins.size() == 0) { 479 readAclTable = Model.HIER_READ_ACL_TABLE_NAME; 480 readAclTableAlias = readAclTable; 481 aclrumTable = Model.ACLR_USER_MAP_TABLE_NAME; 482 aclrumTableAlias = aclrumTable; 483 } else { 484 readAclTableAlias = "nxr" + (njoin + 1); 485 readAclTable = Model.HIER_READ_ACL_TABLE_NAME + ' ' + readAclTableAlias; // TODO dialect 486 aclrumTableAlias = "aclrum" + (njoin + 1); 487 aclrumTable = Model.ACLR_USER_MAP_TABLE_NAME + ' ' + aclrumTableAlias; // TODO dialect 488 } 489 String readAclIdCol = readAclTableAlias + '.' + Model.HIER_READ_ACL_ID; 490 String readAclAclIdCol = readAclTableAlias + '.' + Model.HIER_READ_ACL_ACL_ID; 491 String aclrumAclIdCol = aclrumTableAlias + '.' + Model.ACLR_USER_MAP_ACL_ID; 492 String aclrumUserIdCol = aclrumTableAlias + '.' + Model.ACLR_USER_MAP_USER_ID; 493 // first join with hierarchy_read_acl 494 if (outerJoin) { 495 from.append(" "); 496 from.append(join.kind); 497 } 498 from.append(String.format(" JOIN %s ON (%s = %s)", readAclTable, tableMainId, readAclIdCol)); 499 // second join with aclr_user_map 500 String securityCheck = dialect.getReadAclsCheckSql(aclrumUserIdCol); 501 String joinOn = String.format("%s = %s", readAclAclIdCol, aclrumAclIdCol); 502 if (outerJoin) { 503 from.append(" "); 504 from.append(join.kind); 505 // outer join, security check must be part of JOIN 506 joinOn = String.format("%s AND %s", joinOn, securityCheck); 507 fromParams.add(principals); 508 } else { 509 // inner join, security check can go in WHERE clause 510 whereClauses.add(securityCheck); 511 whereParams.add(principals); 512 } 513 from.append(String.format(" JOIN %s ON (%s)", aclrumTable, joinOn)); 514 } else { 515 String securityCheck = dialect.getSecurityCheckSql(tableMainId); 516 if (outerJoin) { 517 securityCheck = String.format("(%s OR %s IS NULL)", securityCheck, tableMainId); 518 } 519 whereClauses.add(securityCheck); 520 whereParams.add(principals); 521 whereParams.add(permissions); 522 } 523 } 524 } 525 526 /* 527 * WHERE clause. 528 */ 529 530 Tree whereNode = queryUtil.getWalker().getWherePredicateTree(); 531 if (whereNode != null) { 532 GeneratingWalker generator = new GeneratingWalker(); 533 generator.walkPredicate(whereNode); 534 whereClauses.add(generator.whereBuf.toString()); 535 whereParams.addAll(generator.whereBufParams); 536 537 // add JOINs for the external fulltext matches 538 Collections.sort(generator.ftJoins); // implicit JOINs last 539 // (PostgreSQL) 540 for (org.nuxeo.ecm.core.storage.sql.jdbc.db.Join join : generator.ftJoins) { 541 from.append(join.toSql(dialect)); 542 if (join.tableParam != null) { 543 fromParams.add(join.tableParam); 544 } 545 } 546 } 547 548 /* 549 * SELECT clause. 550 */ 551 552 List<String> selectWhat = new ArrayList<String>(); 553 List<Serializable> selectParams = new ArrayList<Serializable>(1); 554 for (SqlColumn rc : realColumns) { 555 selectWhat.add(rc.sql); 556 } 557 selectParams.addAll(realColumnsParams); 558 559 CMISQLMapMaker mapMaker = new CMISQLMapMaker(realColumns, virtualColumns, service); 560 String what = StringUtils.join(selectWhat, ", "); 561 if (distinct) { 562 what = "DISTINCT " + what; 563 } 564 565 /* 566 * ORDER BY clause. 567 */ 568 569 List<String> orderbys = new LinkedList<String>(); 570 for (SortSpec spec : query.getOrderBys()) { 571 String orderby; 572 CmisSelector sel = spec.getSelector(); 573 if (sel instanceof ColumnReference) { 574 Column column = (Column) sel.getInfo(); 575 orderby = column.getFullQuotedName(); 576 } else { 577 orderby = fulltextMatchInfo.scoreAlias; 578 } 579 if (!spec.ascending) { 580 orderby += " DESC"; 581 } 582 orderbys.add(orderby); 583 } 584 585 /* 586 * Create the whole select. 587 */ 588 589 Select select = new Select(null); 590 select.setWhat(what); 591 select.setFrom(from.toString()); 592 // TODO(fromParams); // TODO add before whereParams 593 select.setWhere(StringUtils.join(whereClauses, " AND ")); 594 select.setOrderBy(StringUtils.join(orderbys, ", ")); 595 596 Query q = new Query(); 597 q.selectInfo = new SQLInfoSelect(select.getStatement(), mapMaker); 598 q.selectParams = selectParams; 599 q.selectParams.addAll(fromParams); 600 q.selectParams.addAll(whereParams); 601 return q; 602 } 603 604 /** 605 * Applies security policies query transformers to the statement, if possible. Otherwise raises an exception. 606 * 607 * @since 5.7.2 608 * @throws CmisRuntimeException If a security policy prevents doing CMIS queries. 609 */ 610 protected String applySecurityPolicyQueryTransformers(NuxeoCmisService service, Principal principal, 611 String statement) { 612 SecurityPolicyService securityPolicyService = Framework.getService(SecurityPolicyService.class); 613 if (securityPolicyService == null) { 614 return statement; 615 } 616 String repositoryId = service.getNuxeoRepository().getId(); 617 for (SecurityPolicy policy : securityPolicyService.getPolicies()) { 618 if (!policy.isRestrictingPermission(SecurityConstants.BROWSE)) { 619 continue; 620 } 621 // check CMISQL transformer (new @since 5.7.2) 622 if (!policy.isExpressibleInQuery(repositoryId, TYPE)) { 623 throw new CmisRuntimeException( 624 "Security policy " + policy.getClass().getName() + " prevents CMISQL execution"); 625 } 626 QueryTransformer transformer = policy.getQueryTransformer(repositoryId, TYPE); 627 statement = transformer.transform(principal, statement); 628 } 629 return statement; 630 } 631 632 protected void findVersionableQualifiers() { 633 List<JoinSpec> joins = query.getJoins(); 634 for (int njoin = -1; njoin < joins.size(); njoin++) { 635 boolean firstTable = njoin == -1; 636 String alias; 637 if (firstTable) { 638 alias = query.getMainTypeAlias(); 639 } else { 640 alias = joins.get(njoin).alias; 641 } 642 String typeQueryName = qualifierToType.get(alias); 643 TypeDefinition td = query.getTypeDefinitionFromQueryName(typeQueryName); 644 boolean versionable = td.getBaseTypeId() == BaseTypeId.CMIS_DOCUMENT; 645 if (versionable) { 646 String qual = canonicalQualifier.get(alias); 647 versionableQualifiers.add(qual); 648 } 649 } 650 } 651 652 protected boolean isFacetsColumn(String name) { 653 return PropertyIds.SECONDARY_OBJECT_TYPE_IDS.equals(name) || NuxeoTypeHelper.NX_FACETS.equals(name); 654 } 655 656 // add main id to all qualifiers if 657 // - we have no DISTINCT (in which case more columns don't matter), or 658 // - we have virtual columns, or 659 // - system columns are requested 660 // check no added columns would bias the DISTINCT 661 // after this method, allTables also contain hier table for virtual columns 662 protected void addSystemColumns(boolean addSystemColumns, boolean distinct) { 663 664 List<CmisSelector> addedSystemColumns = new ArrayList<CmisSelector>(2); 665 666 for (String qual : allQualifiers) { 667 TypeDefinition type = getTypeForQualifier(qual); 668 669 // additional references to cmis:objectId and cmis:objectTypeId 670 for (String propertyId : Arrays.asList(PropertyIds.OBJECT_ID, PropertyIds.OBJECT_TYPE_ID)) { 671 ColumnReference col = new ColumnReference(qual, propertyId); 672 col.setTypeDefinition(propertyId, type); 673 String key = getColumnKey(col); 674 boolean add = true; 675 for (SqlColumn rc : realColumns) { 676 if (rc.key.equals(key)) { 677 add = false; 678 break; 679 } 680 } 681 if (add) { 682 addedSystemColumns.add(col); 683 } 684 } 685 if (skipDeleted || lifecycleWhereClauseQualifiers.contains(qual)) { 686 // add lifecycle state column 687 ModelProperty propertyInfo = model.getPropertyInfo(Model.MISC_LIFECYCLE_STATE_PROP); 688 Table table = getTable(database.getTable(propertyInfo.fragmentName), qual); 689 recordFragment(qual, table); 690 } 691 if (mixinTypeWhereClauseQualifiers.contains(qual)) { 692 recordFragment(qual, getTable(hierTable, qual)); 693 } 694 } 695 696 // additional system columns to select on 697 if (!distinct) { 698 for (CmisSelector col : addedSystemColumns) { 699 recordSelectSelector(col); 700 } 701 } else { 702 if (!addedSystemColumns.isEmpty()) { 703 if (!virtualColumnNames.isEmpty()) { 704 throw new QueryParseException( 705 "Cannot use DISTINCT with virtual columns: " + StringUtils.join(virtualColumnNames, ", ")); 706 } 707 if (addSystemColumns) { 708 throw new QueryParseException("Cannot use DISTINCT without explicit " + PropertyIds.OBJECT_ID); 709 } 710 // don't add system columns as it would prevent DISTINCT from 711 // working 712 } 713 } 714 715 // for all qualifiers 716 for (String qual : allQualifiers) { 717 // include hier in fragments 718 recordFragment(qual, getTable(hierTable, qual)); 719 // if only latest version include the version table 720 boolean versionable = versionableQualifiers.contains(qual); 721 if (searchLatestVersion && versionable) { 722 Table ver = database.getTable(Model.VERSION_TABLE_NAME); 723 recordFragment(qual, getTable(ver, qual)); 724 } 725 } 726 727 } 728 729 /** 730 * Records a SELECT selector, and associates it to a database column. 731 */ 732 protected void recordSelectSelector(CmisSelector sel) { 733 if (sel instanceof FunctionReference) { 734 FunctionReference fr = (FunctionReference) sel; 735 if (fr.getFunction() != CmisQlFunction.SCORE) { 736 throw new CmisRuntimeException("Unknown function: " + fr.getFunction()); 737 } 738 String key = fr.getAliasName(); 739 if (key == null) { 740 key = "SEARCH_SCORE"; // default, from spec 741 } 742 String scoreExprSql = fulltextMatchInfo.scoreExpr + " AS " + fulltextMatchInfo.scoreAlias; 743 SqlColumn c = new SqlColumn(scoreExprSql, fulltextMatchInfo.scoreCol, key); 744 realColumns.add(c); 745 if (fulltextMatchInfo.scoreExprParam != null) { 746 realColumnsParams.add(fulltextMatchInfo.scoreExprParam); 747 } 748 if (typeInfo != null) { 749 PropertyDecimalDefinitionImpl pd = new PropertyDecimalDefinitionImpl(); 750 pd.setId(key); 751 pd.setQueryName(key); 752 pd.setCardinality(Cardinality.SINGLE); 753 pd.setDisplayName("Score"); 754 pd.setLocalName("score"); 755 typeInfo.put(key, pd); 756 } 757 } else { // sel instanceof ColumnReference 758 ColumnReference col = (ColumnReference) sel; 759 String qual = canonicalQualifier.get(col.getQualifier()); 760 761 if (col.getPropertyQueryName().equals("*")) { 762 TypeDefinition type = getTypeForQualifier(qual); 763 for (PropertyDefinition<?> pd : type.getPropertyDefinitions().values()) { 764 String id = pd.getId(); 765 if ((pd.getCardinality() == Cardinality.SINGLE // 766 && Boolean.TRUE.equals(pd.isQueryable())) || id.equals(PropertyIds.BASE_TYPE_ID)) { 767 ColumnReference c = new ColumnReference(qual, id); 768 c.setTypeDefinition(id, type); 769 recordSelectSelector(c); 770 } 771 } 772 return; 773 } 774 775 String key = getColumnKey(col); 776 PropertyDefinition<?> pd = col.getPropertyDefinition(); 777 Column column = getColumn(col); 778 if (column != null && pd.getCardinality() == Cardinality.SINGLE) { 779 col.setInfo(column); 780 recordColumnFragment(qual, column); 781 String sql = column.getFullQuotedName(); 782 SqlColumn c = new SqlColumn(sql, column, key); 783 realColumns.add(c); 784 } else { 785 virtualColumns.put(key, col); 786 virtualColumnNames.add(key); 787 allQualifiers.add(qual); 788 } 789 if (typeInfo != null) { 790 typeInfo.put(key, pd); 791 } 792 } 793 } 794 795 public enum ClauseType { 796 JOIN, 797 WHERE, 798 ORDER_BY; 799 } 800 801 /** 802 * Records a JOIN / WHERE / ORDER BY selector, and associates it to a database column. 803 */ 804 protected void recordSelector(CmisSelector sel, ClauseType clauseType) { 805 if (sel instanceof FunctionReference) { 806 FunctionReference fr = (FunctionReference) sel; 807 if (clauseType != ClauseType.ORDER_BY) { 808 throw new QueryParseException("Cannot use function in " + clauseType + " clause: " + fr.getFunction()); 809 } 810 // ORDER BY SCORE, nothing further to record 811 if (fulltextMatchInfo == null) { 812 throw new QueryParseException("Cannot use ORDER BY SCORE without CONTAINS"); 813 } 814 return; 815 } 816 ColumnReference col = (ColumnReference) sel; 817 PropertyDefinition<?> pd = col.getPropertyDefinition(); 818 boolean multi = pd.getCardinality() == Cardinality.MULTI; 819 820 // fetch column and associate it to the selector 821 Column column = getColumn(col); 822 if (!isFacetsColumn(col.getPropertyId()) && column == null) { 823 throw new QueryParseException( 824 "Cannot use column in " + clauseType + " clause: " + col.getPropertyQueryName()); 825 } 826 col.setInfo(column); 827 String qual = canonicalQualifier.get(col.getQualifier()); 828 829 if (clauseType == ClauseType.WHERE && NuxeoTypeHelper.NX_LIFECYCLE_STATE.equals(col.getPropertyId())) { 830 // explicit lifecycle query: do not include the 'deleted' lifecycle 831 // filter 832 skipDeleted = false; 833 lifecycleWhereClauseQualifiers.add(qual); 834 } 835 if (clauseType == ClauseType.WHERE && isFacetsColumn(col.getPropertyId())) { 836 mixinTypeWhereClauseQualifiers.add(qual); 837 } 838 // record as a needed fragment 839 if (!multi) { 840 recordColumnFragment(qual, column); 841 } 842 } 843 844 /** 845 * Records a database column's fragment (to know what to join). 846 */ 847 protected void recordColumnFragment(String qual, Column column) { 848 recordFragment(qual, column.getTable()); 849 } 850 851 /** 852 * Records a database table and qualifier (to know what to join). 853 */ 854 protected void recordFragment(String qual, Table table) { 855 String fragment = table.getKey(); 856 Map<String, Table> tablesByFragment = allTables.get(qual); 857 if (tablesByFragment == null) { 858 allTables.put(qual, tablesByFragment = new HashMap<>()); 859 } 860 tablesByFragment.put(fragment, table); 861 allQualifiers.add(qual); 862 } 863 864 /** 865 * Finds what qualifiers are allowed and to what correlation name they are mapped. 866 */ 867 protected void resolveQualifiers() { 868 Map<String, String> types = query.getTypes(); 869 Map<String, AtomicInteger> typeCount = new HashMap<>(); 870 for (Entry<String, String> en : types.entrySet()) { 871 String qual = en.getKey(); 872 String typeQueryName = en.getValue(); 873 qualifierToType.put(qual, typeQueryName); 874 // if an alias, use as its own correlation name 875 canonicalQualifier.put(qual, qual); 876 // also use alias as correlation name for this type 877 // (ambiguous types removed later) 878 canonicalQualifier.put(typeQueryName, qual); 879 // count type use 880 if (!typeCount.containsKey(typeQueryName)) { 881 typeCount.put(typeQueryName, new AtomicInteger(0)); 882 } 883 typeCount.get(typeQueryName).incrementAndGet(); 884 } 885 for (Entry<String, AtomicInteger> en : typeCount.entrySet()) { 886 String typeQueryName = en.getKey(); 887 if (en.getValue().get() == 1) { 888 // for types used once, allow direct type reference 889 qualifierToType.put(typeQueryName, typeQueryName); 890 } else { 891 // ambiguous type, not legal as qualifier 892 canonicalQualifier.remove(typeQueryName); 893 } 894 } 895 // if only one type, allow omitted qualifier (null) 896 if (types.size() == 1) { 897 String typeQueryName = types.values().iterator().next(); 898 qualifierToType.put(null, typeQueryName); 899 // correlation name is actually null for all qualifiers 900 for (String qual : qualifierToType.keySet()) { 901 canonicalQualifier.put(qual, null); 902 } 903 } 904 } 905 906 /** 907 * Finds a database column from a CMIS reference. 908 */ 909 protected Column getColumn(ColumnReference col) { 910 String qual = canonicalQualifier.get(col.getQualifier()); 911 String id = col.getPropertyId(); 912 Column column; 913 if (id.startsWith(CMIS_PREFIX) || id.startsWith(NX_PREFIX)) { 914 column = getSystemColumn(qual, id); 915 } else { 916 ModelProperty propertyInfo = model.getPropertyInfo(id); 917 boolean multi = propertyInfo.propertyType.isArray(); 918 Table table = database.getTable(propertyInfo.fragmentName); 919 String key = multi ? Model.COLL_TABLE_VALUE_KEY : propertyInfo.fragmentKey; 920 column = getTable(table, qual).getColumn(key); 921 } 922 return column; 923 } 924 925 protected Column getSystemColumn(String qual, String id) { 926 Column column = getSystemColumn(id); 927 if (column != null && qual != null) { 928 // alias table according to qualifier 929 Table table = column.getTable(); 930 column = getTable(table, qual).getColumn(column.getKey()); 931 // TODO ensure key == name, or add getName() 932 } 933 return column; 934 } 935 936 protected Column getSystemColumn(String id) { 937 if (id.equals(PropertyIds.OBJECT_ID)) { 938 return hierTable.getColumn(Model.MAIN_KEY); 939 } 940 if (id.equals(PropertyIds.PARENT_ID)) { 941 return hierTable.getColumn(Model.HIER_PARENT_KEY); 942 } 943 if (id.equals(NuxeoTypeHelper.NX_PARENT_ID)) { 944 return hierTable.getColumn(Model.HIER_PARENT_KEY); 945 } 946 if (id.equals(NuxeoTypeHelper.NX_PATH_SEGMENT)) { 947 return hierTable.getColumn(Model.HIER_CHILD_NAME_KEY); 948 } 949 if (id.equals(NuxeoTypeHelper.NX_POS)) { 950 return hierTable.getColumn(Model.HIER_CHILD_POS_KEY); 951 } 952 if (id.equals(PropertyIds.OBJECT_TYPE_ID)) { 953 // joinedHierTable 954 return hierTable.getColumn(Model.MAIN_PRIMARY_TYPE_KEY); 955 } 956 if (id.equals(PropertyIds.VERSION_LABEL)) { 957 return database.getTable(Model.VERSION_TABLE_NAME).getColumn(Model.VERSION_LABEL_KEY); 958 } 959 if (id.equals(PropertyIds.IS_LATEST_MAJOR_VERSION)) { 960 return database.getTable(Model.VERSION_TABLE_NAME).getColumn(Model.VERSION_IS_LATEST_MAJOR_KEY); 961 } 962 if (id.equals(PropertyIds.IS_LATEST_VERSION)) { 963 return database.getTable(Model.VERSION_TABLE_NAME).getColumn(Model.VERSION_IS_LATEST_KEY); 964 } 965 if (id.equals(NuxeoTypeHelper.NX_ISVERSION)) { 966 return database.getTable(Model.HIER_TABLE_NAME).getColumn(Model.MAIN_IS_VERSION_KEY); 967 } 968 if (id.equals(NuxeoTypeHelper.NX_ISCHECKEDIN)) { 969 return database.getTable(Model.HIER_TABLE_NAME).getColumn(Model.MAIN_CHECKED_IN_KEY); 970 } 971 if (id.equals(NuxeoTypeHelper.NX_LIFECYCLE_STATE)) { 972 ModelProperty propertyInfo = model.getPropertyInfo(Model.MISC_LIFECYCLE_STATE_PROP); 973 return database.getTable(propertyInfo.fragmentName).getColumn(propertyInfo.fragmentKey); 974 } 975 if (id.equals(PropertyIds.NAME)) { 976 return database.getTable(DC_FRAGMENT_NAME).getColumn(DC_TITLE_KEY); 977 } 978 if (id.equals(PropertyIds.DESCRIPTION)) { 979 return database.getTable(DC_FRAGMENT_NAME).getColumn(DC_DESCRIPTION_KEY); 980 } 981 if (id.equals(PropertyIds.CREATED_BY)) { 982 return database.getTable(DC_FRAGMENT_NAME).getColumn(DC_CREATOR_KEY); 983 } 984 if (id.equals(PropertyIds.CREATION_DATE)) { 985 return database.getTable(DC_FRAGMENT_NAME).getColumn(DC_CREATED_KEY); 986 } 987 if (id.equals(PropertyIds.LAST_MODIFICATION_DATE)) { 988 return database.getTable(DC_FRAGMENT_NAME).getColumn(DC_MODIFIED_KEY); 989 } 990 if (id.equals(PropertyIds.LAST_MODIFIED_BY)) { 991 return database.getTable(DC_FRAGMENT_NAME).getColumn(DC_LAST_CONTRIBUTOR_KEY); 992 } 993 if (id.equals(PropertyIds.SOURCE_ID)) { 994 return database.getTable(REL_FRAGMENT_NAME).getColumn(REL_SOURCE_KEY); 995 } 996 if (id.equals(PropertyIds.TARGET_ID)) { 997 return database.getTable(REL_FRAGMENT_NAME).getColumn(REL_TARGET_KEY); 998 } 999 return null; 1000 } 1001 1002 /** Get key to use in data returned to high-level caller. */ 1003 protected static String getColumnKey(ColumnReference col) { 1004 String alias = col.getAliasName(); 1005 if (alias != null) { 1006 return alias; 1007 } 1008 return getPropertyKey(col.getQualifier(), col.getPropertyQueryName()); 1009 } 1010 1011 protected static String getPropertyKey(String qual, String id) { 1012 if (qual == null) { 1013 return id; 1014 } 1015 return qual + '.' + id; 1016 } 1017 1018 protected TypeDefinition getTypeForQualifier(String qual) { 1019 String typeQueryName = qualifierToType.get(qual); 1020 return query.getTypeDefinitionFromQueryName(typeQueryName); 1021 } 1022 1023 protected Table getTable(Table table, String qual) { 1024 if (qual == null) { 1025 return table; 1026 } else { 1027 return new TableAlias(table, getTableAlias(table, qual)); 1028 } 1029 } 1030 1031 protected String getTableAlias(Table table, String qual) { 1032 return "_" + qual + "_" + table.getPhysicalName(); 1033 } 1034 1035 /** 1036 * Map maker that can deal with aliased column names and computed values. 1037 */ 1038 // static to avoid keeping the whole QueryMaker in the returned object 1039 public static class CMISQLMapMaker implements MapMaker { 1040 1041 protected List<SqlColumn> realColumns; 1042 1043 protected Map<String, ColumnReference> virtualColumns; 1044 1045 protected NuxeoCmisService service; 1046 1047 public CMISQLMapMaker(List<SqlColumn> realColumns, Map<String, ColumnReference> virtualColumns, 1048 NuxeoCmisService service) { 1049 this.realColumns = realColumns; 1050 this.virtualColumns = virtualColumns; 1051 this.service = service; 1052 } 1053 1054 @Override 1055 public Map<String, Serializable> makeMap(ResultSet rs) throws SQLException { 1056 Map<String, Serializable> map = new HashMap<>(); 1057 1058 // get values from result set 1059 int i = 1; 1060 for (SqlColumn rc : realColumns) { 1061 Serializable value = rc.column.getFromResultSet(rs, i++); 1062 String key = rc.column.getKey(); 1063 // type conversion to CMIS values 1064 if (value instanceof Long) { 1065 value = BigInteger.valueOf(((Long) value).longValue()); 1066 } else if (value instanceof Integer) { 1067 value = BigInteger.valueOf(((Integer) value).intValue()); 1068 } else if (value instanceof Double) { 1069 value = BigDecimal.valueOf(((Double) value).doubleValue()); 1070 } else if (value == null) { 1071 // special handling of some columns where NULL means FALSE 1072 String column = rc.column.getTable().getRealTable().getKey() + " " + key; 1073 if (NULL_IS_FALSE_COLUMNS.contains(column)) { 1074 value = Boolean.FALSE; 1075 } 1076 } 1077 if (Model.MAIN_KEY.equals(key) || Model.HIER_PARENT_KEY.equals(key)) { 1078 value = String.valueOf(value); // idToString 1079 } 1080 map.put(rc.key, value); 1081 } 1082 1083 // virtual values 1084 // map to store actual data for each qualifier 1085 TypeManagerImpl typeManager = service.getTypeManager(); 1086 Map<String, NuxeoObjectData> datas = null; 1087 for (Entry<String, ColumnReference> vc : virtualColumns.entrySet()) { 1088 String key = vc.getKey(); 1089 ColumnReference col = vc.getValue(); 1090 String qual = col.getQualifier(); 1091 if (col.getPropertyId().equals(PropertyIds.BASE_TYPE_ID)) { 1092 // special case, no need to get full Nuxeo Document 1093 String typeId = (String) map.get(getPropertyKey(qual, PropertyIds.OBJECT_TYPE_ID)); 1094 TypeDefinitionContainer type = typeManager.getTypeById(typeId); 1095 String baseTypeId = type.getTypeDefinition().getBaseTypeId().value(); 1096 map.put(key, baseTypeId); 1097 continue; 1098 } 1099 if (datas == null) { 1100 datas = new HashMap<>(2); 1101 } 1102 NuxeoObjectData data = datas.get(qual); 1103 if (data == null) { 1104 // find main id for this qualifier in the result set 1105 // (main id always included in joins) 1106 // TODO check what happens if cmis:objectId is aliased 1107 String id = (String) map.get(getPropertyKey(qual, PropertyIds.OBJECT_ID)); 1108 try { 1109 // reentrant call to the same session, but the MapMaker 1110 // is only called from the IterableQueryResult in 1111 // queryAndFetch which manipulates no session state 1112 // TODO constructing the DocumentModel (in 1113 // NuxeoObjectData) is expensive, try to get value 1114 // directly 1115 data = (NuxeoObjectData) service.getObject(service.getNuxeoRepository().getId(), id, null, null, 1116 null, null, null, null, null); 1117 } catch (CmisRuntimeException e) { 1118 log.error("Cannot get document: " + id, e); 1119 } 1120 datas.put(qual, data); 1121 } 1122 Serializable v; 1123 if (data == null) { 1124 // could not fetch 1125 v = null; 1126 } else { 1127 NuxeoPropertyDataBase<?> pd = (NuxeoPropertyDataBase<?>) data.getProperty(col.getPropertyId()); 1128 if (pd == null) { 1129 v = null; 1130 } else { 1131 if (pd.getPropertyDefinition().getCardinality() == Cardinality.SINGLE) { 1132 v = (Serializable) pd.getFirstValue(); 1133 } else { 1134 v = (Serializable) pd.getValues(); 1135 } 1136 } 1137 } 1138 map.put(key, v); 1139 } 1140 1141 return map; 1142 } 1143 } 1144 1145 /** 1146 * Walker of the WHERE clause to gather fulltext info. 1147 */ 1148 public class AnalyzingWalker extends AbstractPredicateWalker { 1149 1150 public static final String NX_FULLTEXT_INDEX_PREFIX = "nx:"; 1151 1152 public boolean hasContains; 1153 1154 @Override 1155 public Boolean walkContains(Tree opNode, Tree qualNode, Tree queryNode) { 1156 if (hasContains && Framework.getService(ConfigurationService.class) 1157 .isBooleanPropertyFalse(NuxeoRepository.RELAX_CMIS_SPEC)) { 1158 throw new QueryParseException("At most one CONTAINS() is allowed"); 1159 } 1160 hasContains = true; 1161 1162 String qual = qualNode == null ? null : qualNode.getText(); 1163 qual = canonicalQualifier.get(qual); 1164 Column column = getSystemColumn(qual, PropertyIds.OBJECT_ID); 1165 String statement = (String) super.walkString(queryNode); 1166 String indexName = Model.FULLTEXT_DEFAULT_INDEX; 1167 1168 // micro parsing of the fulltext statement to perform fulltext 1169 // search on a non default index 1170 if (statement.startsWith(NX_FULLTEXT_INDEX_PREFIX)) { 1171 statement = statement.substring(NX_FULLTEXT_INDEX_PREFIX.length()); 1172 int firstColumnIdx = statement.indexOf(':'); 1173 if (firstColumnIdx > 0 && firstColumnIdx < statement.length() - 1) { 1174 String requestedIndexName = statement.substring(0, firstColumnIdx); 1175 statement = statement.substring(firstColumnIdx + 1); 1176 if (model.getFulltextConfiguration().indexNames.contains(requestedIndexName)) { 1177 indexName = requestedIndexName; 1178 } else { 1179 throw new QueryParseException("No such fulltext index: " + requestedIndexName); 1180 } 1181 } else { 1182 log.warn(String.format("fail to microparse custom fulltext index:" + " fallback to '%s'", 1183 indexName)); 1184 } 1185 } 1186 // CMIS syntax to our internal google-like internal syntax 1187 statement = cmisToFulltextQuery(statement); 1188 // internal syntax to backend syntax 1189 statement = dialect.getDialectFulltextQuery(statement); 1190 fulltextMatchInfo = dialect.getFulltextScoredMatchInfo(statement, indexName, 1, column, model, database); 1191 return null; 1192 } 1193 } 1194 1195 protected static String cmisToFulltextQuery(String statement) { 1196 // internal syntax has implicit AND 1197 statement = statement.replace(" and ", " "); 1198 statement = statement.replace(" AND ", " "); 1199 return statement; 1200 } 1201 1202 /** 1203 * Walker of the WHERE clause that generates final SQL. 1204 */ 1205 public class GeneratingWalker extends AbstractPredicateWalker { 1206 1207 public StringBuilder whereBuf = new StringBuilder(); 1208 1209 public LinkedList<Serializable> whereBufParams = new LinkedList<Serializable>(); 1210 1211 /** joins added by fulltext match */ 1212 public final List<org.nuxeo.ecm.core.storage.sql.jdbc.db.Join> ftJoins = new LinkedList<org.nuxeo.ecm.core.storage.sql.jdbc.db.Join>(); 1213 1214 @Override 1215 public Boolean walkNot(Tree opNode, Tree node) { 1216 whereBuf.append("NOT "); 1217 walkPredicate(node); 1218 return null; 1219 } 1220 1221 @Override 1222 public Boolean walkAnd(Tree opNode, Tree leftNode, Tree rightNode) { 1223 whereBuf.append("("); 1224 walkPredicate(leftNode); 1225 whereBuf.append(" AND "); 1226 walkPredicate(rightNode); 1227 whereBuf.append(")"); 1228 return null; 1229 } 1230 1231 @Override 1232 public Boolean walkOr(Tree opNode, Tree leftNode, Tree rightNode) { 1233 whereBuf.append("("); 1234 walkPredicate(leftNode); 1235 whereBuf.append(" OR "); 1236 walkPredicate(rightNode); 1237 whereBuf.append(")"); 1238 return null; 1239 } 1240 1241 @Override 1242 public Boolean walkEquals(Tree opNode, Tree leftNode, Tree rightNode) { 1243 if (isFacetsColumn(leftNode.getText())) { 1244 walkFacets(opNode, leftNode, rightNode); 1245 return null; 1246 } 1247 if (leftNode.getType() == CmisQlStrictLexer.COL && rightNode.getType() == CmisQlStrictLexer.BOOL_LIT 1248 && !Boolean.parseBoolean(rightNode.getText())) { 1249 // special handling of the " = false" case for column where 1250 // NULL means false 1251 walkIsNullOrFalse(leftNode); 1252 return null; 1253 } 1254 // normal case 1255 walkExpr(leftNode); 1256 whereBuf.append(" = "); 1257 walkExpr(rightNode); 1258 return null; 1259 } 1260 1261 @Override 1262 public Boolean walkNotEquals(Tree opNode, Tree leftNode, Tree rightNode) { 1263 if (leftNode.getType() == CmisQlStrictLexer.COL && rightNode.getType() == CmisQlStrictLexer.BOOL_LIT 1264 && Boolean.parseBoolean(rightNode.getText())) { 1265 // special handling of the " <> true" case for column where 1266 // NULL means false 1267 walkIsNullOrFalse(leftNode); 1268 return null; 1269 } 1270 walkExpr(leftNode); 1271 whereBuf.append(" <> "); 1272 walkExpr(rightNode); 1273 return null; 1274 } 1275 1276 protected void walkIsNullOrFalse(Tree leftNode) { 1277 Column c = resolveColumn(leftNode); 1278 String columnSpec = c.getTable().getRealTable().getKey() + " " + c.getKey(); 1279 if (NULL_IS_FALSE_COLUMNS.contains(columnSpec)) { 1280 // treat NULL and FALSE as equivalent 1281 whereBuf.append("("); 1282 walkExpr(leftNode); 1283 whereBuf.append(" IS NULL OR "); 1284 walkExpr(leftNode); 1285 whereBuf.append(" = ?)"); 1286 whereBufParams.add(Boolean.FALSE); 1287 } else { 1288 // explicit false equality test 1289 walkExpr(leftNode); 1290 whereBuf.append(" = ?"); 1291 whereBufParams.add(Boolean.FALSE); 1292 } 1293 } 1294 1295 @Override 1296 public Boolean walkGreaterThan(Tree opNode, Tree leftNode, Tree rightNode) { 1297 walkExpr(leftNode); 1298 whereBuf.append(" > "); 1299 walkExpr(rightNode); 1300 return null; 1301 } 1302 1303 @Override 1304 public Boolean walkGreaterOrEquals(Tree opNode, Tree leftNode, Tree rightNode) { 1305 walkExpr(leftNode); 1306 whereBuf.append(" >= "); 1307 walkExpr(rightNode); 1308 return null; 1309 } 1310 1311 @Override 1312 public Boolean walkLessThan(Tree opNode, Tree leftNode, Tree rightNode) { 1313 walkExpr(leftNode); 1314 whereBuf.append(" < "); 1315 walkExpr(rightNode); 1316 return null; 1317 } 1318 1319 @Override 1320 public Boolean walkLessOrEquals(Tree opNode, Tree leftNode, Tree rightNode) { 1321 walkExpr(leftNode); 1322 whereBuf.append(" <= "); 1323 walkExpr(rightNode); 1324 return null; 1325 } 1326 1327 @Override 1328 public Boolean walkIn(Tree opNode, Tree colNode, Tree listNode) { 1329 walkExpr(colNode); 1330 whereBuf.append(" IN "); 1331 walkExpr(listNode); 1332 return null; 1333 } 1334 1335 @Override 1336 public Boolean walkNotIn(Tree opNode, Tree colNode, Tree listNode) { 1337 walkExpr(colNode); 1338 whereBuf.append(" NOT IN "); 1339 walkExpr(listNode); 1340 return null; 1341 } 1342 1343 @Override 1344 public Boolean walkInAny(Tree opNode, Tree colNode, Tree listNode) { 1345 if (isFacetsColumn(resolveColumnReference(colNode).getName())) { 1346 walkFacets(opNode, colNode, listNode); 1347 return null; 1348 } 1349 walkAny(colNode, "IN", listNode); 1350 return null; 1351 } 1352 1353 @Override 1354 public Boolean walkNotInAny(Tree opNode, Tree colNode, Tree listNode) { 1355 if (isFacetsColumn(resolveColumnReference(colNode).getName())) { 1356 walkFacets(opNode, colNode, listNode); 1357 return null; 1358 } 1359 walkAny(colNode, "NOT IN", listNode); 1360 return null; 1361 } 1362 1363 @Override 1364 public Boolean walkEqAny(Tree opNode, Tree literalNode, Tree colNode) { 1365 if (isFacetsColumn(resolveColumnReference(colNode).getName())) { 1366 walkFacets(opNode, colNode, literalNode); 1367 return null; 1368 } 1369 // note that argument order is reversed 1370 walkAny(colNode, "=", literalNode); 1371 return null; 1372 } 1373 1374 protected void walkAny(Tree colNode, String op, Tree exprNode) { 1375 int token = ((Tree) colNode).getTokenStartIndex(); 1376 ColumnReference col = (ColumnReference) query.getColumnReference(Integer.valueOf(token)); 1377 PropertyDefinition<?> pd = col.getPropertyDefinition(); 1378 if (pd.getCardinality() != Cardinality.MULTI) { 1379 throw new QueryParseException( 1380 "Cannot use " + op + " ANY with single-valued property: " + col.getPropertyQueryName()); 1381 } 1382 Column column = (Column) col.getInfo(); 1383 String qual = canonicalQualifier.get(col.getQualifier()); 1384 // we need the real table and column in the subquery 1385 Table realTable = column.getTable().getRealTable(); 1386 Column realColumn = realTable.getColumn(column.getKey()); 1387 Column hierMainColumn = getTable(hierTable, qual).getColumn(Model.MAIN_KEY); 1388 Column multiMainColumn = realTable.getColumn(Model.MAIN_KEY); 1389 1390 whereBuf.append("EXISTS (SELECT 1 FROM "); 1391 whereBuf.append(realTable.getQuotedName()); 1392 whereBuf.append(" WHERE "); 1393 whereBuf.append(hierMainColumn.getFullQuotedName()); 1394 whereBuf.append(" = "); 1395 whereBuf.append(multiMainColumn.getFullQuotedName()); 1396 whereBuf.append(" AND "); 1397 whereBuf.append(realColumn.getFullQuotedName()); 1398 whereBuf.append(" "); 1399 whereBuf.append(op); 1400 whereBuf.append(" "); 1401 walkExpr(exprNode); 1402 whereBuf.append(")"); 1403 } 1404 1405 @Override 1406 public Boolean walkIsNull(Tree opNode, Tree colNode) { 1407 return walkIsNullOrIsNotNull(colNode, true); 1408 } 1409 1410 @Override 1411 public Boolean walkIsNotNull(Tree opNode, Tree colNode) { 1412 return walkIsNullOrIsNotNull(colNode, false); 1413 } 1414 1415 protected Boolean walkIsNullOrIsNotNull(Tree colNode, boolean isNull) { 1416 int token = ((Tree) colNode).getTokenStartIndex(); 1417 ColumnReference col = (ColumnReference) query.getColumnReference(Integer.valueOf(token)); 1418 PropertyDefinition<?> pd = col.getPropertyDefinition(); 1419 boolean multi = pd.getCardinality() == Cardinality.MULTI; 1420 if (multi) { 1421 // we need the real table and column in the subquery 1422 Column column = (Column) col.getInfo(); 1423 String qual = canonicalQualifier.get(col.getQualifier()); 1424 Table realTable = column.getTable().getRealTable(); 1425 Column hierMainColumn = getTable(hierTable, qual).getColumn(Model.MAIN_KEY); 1426 Column multiMainColumn = realTable.getColumn(Model.MAIN_KEY); 1427 if (isNull) { 1428 whereBuf.append("NOT "); 1429 } 1430 whereBuf.append("EXISTS (SELECT 1 FROM "); 1431 whereBuf.append(realTable.getQuotedName()); 1432 whereBuf.append(" WHERE "); 1433 whereBuf.append(hierMainColumn.getFullQuotedName()); 1434 whereBuf.append(" = "); 1435 whereBuf.append(multiMainColumn.getFullQuotedName()); 1436 whereBuf.append(')'); 1437 } else { 1438 walkExpr(colNode); 1439 whereBuf.append(isNull ? " IS NULL" : " IS NOT NULL"); 1440 } 1441 return null; 1442 } 1443 1444 @Override 1445 public Boolean walkLike(Tree opNode, Tree colNode, Tree stringNode) { 1446 walkExpr(colNode); 1447 whereBuf.append(" LIKE "); 1448 walkExpr(stringNode); 1449 return null; 1450 } 1451 1452 @Override 1453 public Boolean walkNotLike(Tree opNode, Tree colNode, Tree stringNode) { 1454 walkExpr(colNode); 1455 whereBuf.append(" NOT LIKE "); 1456 walkExpr(stringNode); 1457 return null; 1458 } 1459 1460 @Override 1461 public Boolean walkContains(Tree opNode, Tree qualNode, Tree queryNode) { 1462 if (fulltextMatchInfo.joins != null) { 1463 ftJoins.addAll(fulltextMatchInfo.joins); 1464 } 1465 whereBuf.append(fulltextMatchInfo.whereExpr); 1466 if (fulltextMatchInfo.whereExprParam != null) { 1467 whereBufParams.add(fulltextMatchInfo.whereExprParam); 1468 } 1469 return null; 1470 } 1471 1472 @Override 1473 public Boolean walkInFolder(Tree opNode, Tree qualNode, Tree paramNode) { 1474 String qual = qualNode == null ? null : qualNode.getText(); 1475 qual = canonicalQualifier.get(qual); 1476 // this is from the hierarchy table which is always present 1477 Column column = getSystemColumn(qual, PropertyIds.PARENT_ID); 1478 whereBuf.append(column.getFullQuotedName()); 1479 whereBuf.append(" = ?"); 1480 String id = (String) super.walkString(paramNode); 1481 whereBufParams.add(model.idFromString(id)); 1482 return null; 1483 } 1484 1485 @Override 1486 public Boolean walkInTree(Tree opNode, Tree qualNode, Tree paramNode) { 1487 String qual = qualNode == null ? null : qualNode.getText(); 1488 qual = canonicalQualifier.get(qual); 1489 // this is from the hierarchy table which is always present 1490 Column column = getSystemColumn(qual, PropertyIds.OBJECT_ID); 1491 String id = (String) super.walkString(paramNode); 1492 String sql = dialect.getInTreeSql(column.getFullQuotedName(), id); 1493 if (sql == null) { 1494 whereBuf.append("0=1"); 1495 } else { 1496 whereBuf.append(sql); 1497 whereBufParams.add(model.idFromString(id)); 1498 } 1499 return null; 1500 } 1501 1502 @Override 1503 public Object walkList(Tree node) { 1504 whereBuf.append("("); 1505 for (int i = 0; i < node.getChildCount(); i++) { 1506 if (i != 0) { 1507 whereBuf.append(", "); 1508 } 1509 Tree child = node.getChild(i); 1510 walkExpr(child); 1511 } 1512 whereBuf.append(")"); 1513 return null; 1514 } 1515 1516 @Override 1517 public Object walkBoolean(Tree node) { 1518 Serializable value = (Serializable) super.walkBoolean(node); 1519 whereBuf.append("?"); 1520 whereBufParams.add(value); 1521 return null; 1522 } 1523 1524 @Override 1525 public Object walkNumber(Tree node) { 1526 Serializable value = (Serializable) super.walkNumber(node); 1527 whereBuf.append("?"); 1528 whereBufParams.add(value); 1529 return null; 1530 } 1531 1532 @Override 1533 public Object walkString(Tree node) { 1534 Serializable value = (Serializable) super.walkString(node); 1535 whereBuf.append("?"); 1536 whereBufParams.add(value); 1537 return null; 1538 } 1539 1540 @Override 1541 public Object walkTimestamp(Tree node) { 1542 Serializable value = (Serializable) super.walkTimestamp(node); 1543 whereBuf.append("?"); 1544 whereBufParams.add(value); 1545 return null; 1546 } 1547 1548 @Override 1549 public Object walkCol(Tree node) { 1550 whereBuf.append(resolveColumn(node).getFullQuotedName()); 1551 return null; 1552 } 1553 1554 public ColumnReference resolveColumnReference(Tree node) { 1555 int token = node.getTokenStartIndex(); 1556 CmisSelector sel = query.getColumnReference(Integer.valueOf(token)); 1557 if (sel instanceof ColumnReference) { 1558 return (ColumnReference) sel; 1559 } else { 1560 throw new QueryParseException("Cannot use column in WHERE clause: " + sel.getName()); 1561 } 1562 } 1563 1564 public Column resolveColumn(Tree node) { 1565 return (Column) resolveColumnReference(node).getInfo(); 1566 } 1567 1568 protected void walkFacets(Tree opNode, Tree colNodel, Tree literalNode) { 1569 boolean include; 1570 Set<String> mixins; 1571 1572 int opType = opNode.getType(); 1573 if (opType == CmisQlStrictLexer.EQ_ANY) { 1574 include = true; 1575 if (literalNode.getType() != CmisQlStrictLexer.STRING_LIT) { 1576 throw new QueryParseException(colNodel.getText() + " = requires literal string as right argument"); 1577 } 1578 String value = super.walkString(literalNode).toString(); 1579 mixins = Collections.singleton(value); 1580 } else if (opType == CmisQlStrictLexer.IN_ANY || opType == CmisQlStrictLexer.NOT_IN_ANY) { 1581 include = opType == CmisQlStrictLexer.IN_ANY; 1582 mixins = new TreeSet<>(); 1583 for (int i = 0; i < literalNode.getChildCount(); i++) { 1584 mixins.add(super.walkString(literalNode.getChild(i)).toString()); 1585 } 1586 } else { 1587 throw new QueryParseException(colNodel.getText() + " unsupported operator: " + opNode.getText()); 1588 } 1589 1590 /* 1591 * Primary types - static mixins 1592 */ 1593 Set<String> types; 1594 if (include) { 1595 types = new HashSet<>(); 1596 for (String mixin : mixins) { 1597 types.addAll(model.getMixinDocumentTypes(mixin)); 1598 } 1599 } else { 1600 types = new HashSet<>(model.getDocumentTypes()); 1601 for (String mixin : mixins) { 1602 types.removeAll(model.getMixinDocumentTypes(mixin)); 1603 } 1604 } 1605 1606 /* 1607 * Instance mixins 1608 */ 1609 Set<String> instanceMixins = new HashSet<>(); 1610 for (String mixin : mixins) { 1611 if (!MIXINS_NOT_PER_INSTANCE.contains(mixin)) { 1612 instanceMixins.add(mixin); 1613 } 1614 } 1615 1616 /* 1617 * SQL generation 1618 */ 1619 1620 ColumnReference facetsCol = resolveColumnReference(colNodel); 1621 String qual = canonicalQualifier.get(facetsCol.getQualifier()); 1622 Table table = getTable(hierTable, qual); 1623 1624 if (!types.isEmpty()) { 1625 Column col = table.getColumn(Model.MAIN_PRIMARY_TYPE_KEY); 1626 whereBuf.append(col.getFullQuotedName()); 1627 whereBuf.append(" IN "); 1628 whereBuf.append('('); 1629 for (Iterator<String> it = types.iterator(); it.hasNext();) { 1630 whereBuf.append('?'); 1631 whereBufParams.add(it.next()); 1632 if (it.hasNext()) { 1633 whereBuf.append(", "); 1634 } 1635 } 1636 whereBuf.append(')'); 1637 1638 if (!instanceMixins.isEmpty()) { 1639 whereBuf.append(include ? " OR " : " AND "); 1640 } 1641 } 1642 1643 if (!instanceMixins.isEmpty()) { 1644 whereBuf.append('('); 1645 Column mixinsColumn = table.getColumn(Model.MAIN_MIXIN_TYPES_KEY); 1646 String[] returnParam = new String[1]; 1647 for (Iterator<String> it = instanceMixins.iterator(); it.hasNext();) { 1648 String mixin = it.next(); 1649 String sql = dialect.getMatchMixinType(mixinsColumn, mixin, include, returnParam); 1650 whereBuf.append(sql); 1651 if (returnParam[0] != null) { 1652 whereBufParams.add(returnParam[0]); 1653 } 1654 if (it.hasNext()) { 1655 whereBuf.append(include ? " OR " : " AND "); 1656 } 1657 } 1658 if (!include) { 1659 whereBuf.append(" OR "); 1660 whereBuf.append(mixinsColumn.getFullQuotedName()); 1661 whereBuf.append(" IS NULL"); 1662 } 1663 whereBuf.append(')'); 1664 } 1665 1666 if (types.isEmpty() && instanceMixins.isEmpty()) { 1667 whereBuf.append(include ? "0=1" : "0=0"); 1668 } 1669 } 1670 } 1671}