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