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