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