001/*- 002 * #%L 003 * HAPI FHIR JPA Server 004 * %% 005 * Copyright (C) 2014 - 2024 Smile CDR, Inc. 006 * %% 007 * Licensed under the Apache License, Version 2.0 (the "License"); 008 * you may not use this file except in compliance with the License. 009 * You may obtain a copy of the License at 010 * 011 * http://www.apache.org/licenses/LICENSE-2.0 012 * 013 * Unless required by applicable law or agreed to in writing, software 014 * distributed under the License is distributed on an "AS IS" BASIS, 015 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 016 * See the License for the specific language governing permissions and 017 * limitations under the License. 018 * #L% 019 */ 020package ca.uhn.fhir.jpa.search.builder.sql; 021 022import ca.uhn.fhir.context.FhirContext; 023import ca.uhn.fhir.i18n.Msg; 024import ca.uhn.fhir.interceptor.model.RequestPartitionId; 025import ca.uhn.fhir.jpa.config.HibernatePropertiesProvider; 026import ca.uhn.fhir.jpa.model.config.PartitionSettings; 027import ca.uhn.fhir.jpa.model.dao.JpaPid; 028import ca.uhn.fhir.jpa.model.entity.StorageSettings; 029import ca.uhn.fhir.jpa.search.builder.QueryStack; 030import ca.uhn.fhir.jpa.search.builder.predicate.BaseJoiningPredicateBuilder; 031import ca.uhn.fhir.jpa.search.builder.predicate.ComboNonUniqueSearchParameterPredicateBuilder; 032import ca.uhn.fhir.jpa.search.builder.predicate.ComboUniqueSearchParameterPredicateBuilder; 033import ca.uhn.fhir.jpa.search.builder.predicate.CoordsPredicateBuilder; 034import ca.uhn.fhir.jpa.search.builder.predicate.DatePredicateBuilder; 035import ca.uhn.fhir.jpa.search.builder.predicate.NumberPredicateBuilder; 036import ca.uhn.fhir.jpa.search.builder.predicate.QuantityNormalizedPredicateBuilder; 037import ca.uhn.fhir.jpa.search.builder.predicate.QuantityPredicateBuilder; 038import ca.uhn.fhir.jpa.search.builder.predicate.ResourceIdPredicateBuilder; 039import ca.uhn.fhir.jpa.search.builder.predicate.ResourceLinkPredicateBuilder; 040import ca.uhn.fhir.jpa.search.builder.predicate.ResourceTablePredicateBuilder; 041import ca.uhn.fhir.jpa.search.builder.predicate.SearchParamPresentPredicateBuilder; 042import ca.uhn.fhir.jpa.search.builder.predicate.SourcePredicateBuilder; 043import ca.uhn.fhir.jpa.search.builder.predicate.StringPredicateBuilder; 044import ca.uhn.fhir.jpa.search.builder.predicate.TagPredicateBuilder; 045import ca.uhn.fhir.jpa.search.builder.predicate.TokenPredicateBuilder; 046import ca.uhn.fhir.jpa.search.builder.predicate.UriPredicateBuilder; 047import ca.uhn.fhir.rest.param.DateParam; 048import ca.uhn.fhir.rest.param.DateRangeParam; 049import ca.uhn.fhir.rest.param.ParamPrefixEnum; 050import com.healthmarketscience.sqlbuilder.BinaryCondition; 051import com.healthmarketscience.sqlbuilder.ComboCondition; 052import com.healthmarketscience.sqlbuilder.ComboExpression; 053import com.healthmarketscience.sqlbuilder.Condition; 054import com.healthmarketscience.sqlbuilder.FunctionCall; 055import com.healthmarketscience.sqlbuilder.InCondition; 056import com.healthmarketscience.sqlbuilder.OrderObject; 057import com.healthmarketscience.sqlbuilder.SelectQuery; 058import com.healthmarketscience.sqlbuilder.dbspec.Join; 059import com.healthmarketscience.sqlbuilder.dbspec.basic.DbColumn; 060import com.healthmarketscience.sqlbuilder.dbspec.basic.DbJoin; 061import com.healthmarketscience.sqlbuilder.dbspec.basic.DbSchema; 062import com.healthmarketscience.sqlbuilder.dbspec.basic.DbSpec; 063import com.healthmarketscience.sqlbuilder.dbspec.basic.DbTable; 064import jakarta.annotation.Nonnull; 065import jakarta.annotation.Nullable; 066import org.hibernate.dialect.Dialect; 067import org.hibernate.dialect.SQLServerDialect; 068import org.hibernate.dialect.pagination.AbstractLimitHandler; 069import org.hibernate.query.internal.QueryOptionsImpl; 070import org.hibernate.query.spi.Limit; 071import org.hibernate.query.spi.QueryOptions; 072import org.slf4j.Logger; 073import org.slf4j.LoggerFactory; 074 075import java.util.ArrayList; 076import java.util.Collection; 077import java.util.List; 078import java.util.Set; 079import java.util.UUID; 080import java.util.stream.Collectors; 081 082import static ca.uhn.fhir.rest.param.ParamPrefixEnum.GREATERTHAN; 083import static ca.uhn.fhir.rest.param.ParamPrefixEnum.GREATERTHAN_OR_EQUALS; 084import static ca.uhn.fhir.rest.param.ParamPrefixEnum.LESSTHAN; 085import static ca.uhn.fhir.rest.param.ParamPrefixEnum.LESSTHAN_OR_EQUALS; 086import static ca.uhn.fhir.rest.param.ParamPrefixEnum.NOT_EQUAL; 087import static org.apache.commons.lang3.ObjectUtils.defaultIfNull; 088 089public class SearchQueryBuilder { 090 091 private static final Logger ourLog = LoggerFactory.getLogger(SearchQueryBuilder.class); 092 private final String myBindVariableSubstitutionBase; 093 private final ArrayList<Object> myBindVariableValues; 094 private final DbSpec mySpec; 095 private final DbSchema mySchema; 096 private final SelectQuery mySelect; 097 private final PartitionSettings myPartitionSettings; 098 private final RequestPartitionId myRequestPartitionId; 099 private final String myResourceType; 100 private final StorageSettings myStorageSettings; 101 private final FhirContext myFhirContext; 102 private final SqlObjectFactory mySqlBuilderFactory; 103 private final boolean myCountQuery; 104 private final Dialect myDialect; 105 private boolean myMatchNothing; 106 private ResourceTablePredicateBuilder myResourceTableRoot; 107 private boolean myHaveAtLeastOnePredicate; 108 private BaseJoiningPredicateBuilder myFirstPredicateBuilder; 109 private boolean dialectIsMsSql; 110 private boolean dialectIsMySql; 111 private boolean myNeedResourceTableRoot; 112 private int myNextNearnessColumnId = 0; 113 private DbColumn mySelectedResourceIdColumn; 114 115 /** 116 * Constructor 117 */ 118 public SearchQueryBuilder( 119 FhirContext theFhirContext, 120 StorageSettings theStorageSettings, 121 PartitionSettings thePartitionSettings, 122 RequestPartitionId theRequestPartitionId, 123 String theResourceType, 124 SqlObjectFactory theSqlBuilderFactory, 125 HibernatePropertiesProvider theDialectProvider, 126 boolean theCountQuery) { 127 this( 128 theFhirContext, 129 theStorageSettings, 130 thePartitionSettings, 131 theRequestPartitionId, 132 theResourceType, 133 theSqlBuilderFactory, 134 UUID.randomUUID() + "-", 135 theDialectProvider.getDialect(), 136 theCountQuery, 137 new ArrayList<>()); 138 } 139 140 /** 141 * Constructor for child SQL Builders 142 */ 143 private SearchQueryBuilder( 144 FhirContext theFhirContext, 145 StorageSettings theStorageSettings, 146 PartitionSettings thePartitionSettings, 147 RequestPartitionId theRequestPartitionId, 148 String theResourceType, 149 SqlObjectFactory theSqlBuilderFactory, 150 String theBindVariableSubstitutionBase, 151 Dialect theDialect, 152 boolean theCountQuery, 153 ArrayList<Object> theBindVariableValues) { 154 myFhirContext = theFhirContext; 155 myStorageSettings = theStorageSettings; 156 myPartitionSettings = thePartitionSettings; 157 myRequestPartitionId = theRequestPartitionId; 158 myResourceType = theResourceType; 159 mySqlBuilderFactory = theSqlBuilderFactory; 160 myCountQuery = theCountQuery; 161 myDialect = theDialect; 162 if (myDialect instanceof org.hibernate.dialect.MySQLDialect) { 163 dialectIsMySql = true; 164 } 165 if (myDialect instanceof org.hibernate.dialect.SQLServerDialect) { 166 dialectIsMsSql = true; 167 } 168 169 mySpec = new DbSpec(); 170 mySchema = mySpec.addDefaultSchema(); 171 mySelect = new SelectQuery(); 172 173 myBindVariableSubstitutionBase = theBindVariableSubstitutionBase; 174 myBindVariableValues = theBindVariableValues; 175 } 176 177 public FhirContext getFhirContext() { 178 return myFhirContext; 179 } 180 181 /** 182 * Add and return a predicate builder (or a root query if no root query exists yet) for selecting on a Composite Unique search parameter 183 */ 184 public ComboUniqueSearchParameterPredicateBuilder addComboUniquePredicateBuilder() { 185 ComboUniqueSearchParameterPredicateBuilder retVal = 186 mySqlBuilderFactory.newComboUniqueSearchParameterPredicateBuilder(this); 187 addTable(retVal, null); 188 return retVal; 189 } 190 191 /** 192 * Add and return a predicate builder (or a root query if no root query exists yet) for selecting on a Composite Unique search parameter 193 */ 194 public ComboNonUniqueSearchParameterPredicateBuilder addComboNonUniquePredicateBuilder() { 195 ComboNonUniqueSearchParameterPredicateBuilder retVal = 196 mySqlBuilderFactory.newComboNonUniqueSearchParameterPredicateBuilder(this); 197 addTable(retVal, null); 198 return retVal; 199 } 200 201 /** 202 * Add and return a predicate builder (or a root query if no root query exists yet) for selecting on a COORDS search parameter 203 */ 204 public CoordsPredicateBuilder addCoordsPredicateBuilder(@Nullable DbColumn theSourceJoinColumn) { 205 CoordsPredicateBuilder retVal = mySqlBuilderFactory.coordsPredicateBuilder(this); 206 addTable(retVal, theSourceJoinColumn); 207 return retVal; 208 } 209 210 /** 211 * Create, add and return a predicate builder (or a root query if no root query exists yet) for selecting on a DATE search parameter 212 */ 213 public DatePredicateBuilder addDatePredicateBuilder(@Nullable DbColumn theSourceJoinColumn) { 214 DatePredicateBuilder retVal = mySqlBuilderFactory.dateIndexTable(this); 215 addTable(retVal, theSourceJoinColumn); 216 return retVal; 217 } 218 219 /** 220 * Create a predicate builder for selecting on a DATE search parameter 221 */ 222 public DatePredicateBuilder createDatePredicateBuilder() { 223 return mySqlBuilderFactory.dateIndexTable(this); 224 } 225 226 /** 227 * Create, add and return a predicate builder (or a root query if no root query exists yet) for selecting on a NUMBER search parameter 228 */ 229 public NumberPredicateBuilder addNumberPredicateBuilder(@Nullable DbColumn theSourceJoinColumn) { 230 NumberPredicateBuilder retVal = createNumberPredicateBuilder(); 231 addTable(retVal, theSourceJoinColumn); 232 return retVal; 233 } 234 235 /** 236 * Create a predicate builder for selecting on a NUMBER search parameter 237 */ 238 public NumberPredicateBuilder createNumberPredicateBuilder() { 239 return mySqlBuilderFactory.numberIndexTable(this); 240 } 241 242 /** 243 * Add and return a predicate builder (or a root query if no root query exists yet) for selecting on the Resource table 244 */ 245 public ResourceTablePredicateBuilder addResourceTablePredicateBuilder(@Nullable DbColumn theSourceJoinColumn) { 246 ResourceTablePredicateBuilder retVal = mySqlBuilderFactory.resourceTable(this); 247 addTable(retVal, theSourceJoinColumn); 248 return retVal; 249 } 250 251 /** 252 * Create, add and return a predicate builder (or a root query if no root query exists yet) for selecting on a QUANTITY search parameter 253 */ 254 public QuantityPredicateBuilder addQuantityPredicateBuilder(@Nullable DbColumn theSourceJoinColumn) { 255 QuantityPredicateBuilder retVal = createQuantityPredicateBuilder(); 256 addTable(retVal, theSourceJoinColumn); 257 258 return retVal; 259 } 260 261 /** 262 * Create a predicate builder for selecting on a QUANTITY search parameter 263 */ 264 public QuantityPredicateBuilder createQuantityPredicateBuilder() { 265 return mySqlBuilderFactory.quantityIndexTable(this); 266 } 267 268 public QuantityNormalizedPredicateBuilder addQuantityNormalizedPredicateBuilder( 269 @Nullable DbColumn theSourceJoinColumn) { 270 271 QuantityNormalizedPredicateBuilder retVal = mySqlBuilderFactory.quantityNormalizedIndexTable(this); 272 addTable(retVal, theSourceJoinColumn); 273 274 return retVal; 275 } 276 277 /** 278 * Add and return a predicate builder (or a root query if no root query exists yet) for selecting on a <code>_source</code> search parameter 279 */ 280 public SourcePredicateBuilder addSourcePredicateBuilder( 281 @Nullable DbColumn theSourceJoinColumn, SelectQuery.JoinType theJoinType) { 282 SourcePredicateBuilder retVal = mySqlBuilderFactory.newSourcePredicateBuilder(this); 283 addTable(retVal, theSourceJoinColumn, theJoinType); 284 return retVal; 285 } 286 287 /** 288 * Create, add and return a predicate builder (or a root query if no root query exists yet) for selecting on a REFERENCE search parameter 289 */ 290 public ResourceLinkPredicateBuilder addReferencePredicateBuilder( 291 QueryStack theQueryStack, @Nullable DbColumn theSourceJoinColumn) { 292 ResourceLinkPredicateBuilder retVal = createReferencePredicateBuilder(theQueryStack); 293 addTable(retVal, theSourceJoinColumn); 294 return retVal; 295 } 296 297 /** 298 * Create a predicate builder for selecting on a REFERENCE search parameter 299 */ 300 public ResourceLinkPredicateBuilder createReferencePredicateBuilder(QueryStack theQueryStack) { 301 return mySqlBuilderFactory.referenceIndexTable(theQueryStack, this, false); 302 } 303 304 /** 305 * Add and return a predicate builder (or a root query if no root query exists yet) for selecting on a resource link where the 306 * source and target are reversed. This is used for _has queries. 307 */ 308 public ResourceLinkPredicateBuilder addReferencePredicateBuilderReversed( 309 QueryStack theQueryStack, DbColumn theSourceJoinColumn) { 310 ResourceLinkPredicateBuilder retVal = mySqlBuilderFactory.referenceIndexTable(theQueryStack, this, true); 311 addTable(retVal, theSourceJoinColumn); 312 return retVal; 313 } 314 315 /** 316 * Create, add and return a predicate builder (or a root query if no root query exists yet) for selecting on a STRING search parameter 317 */ 318 public StringPredicateBuilder addStringPredicateBuilder(@Nullable DbColumn theSourceJoinColumn) { 319 StringPredicateBuilder retVal = createStringPredicateBuilder(); 320 addTable(retVal, theSourceJoinColumn); 321 return retVal; 322 } 323 324 /** 325 * Create a predicate builder for selecting on a STRING search parameter 326 */ 327 public StringPredicateBuilder createStringPredicateBuilder() { 328 return mySqlBuilderFactory.stringIndexTable(this); 329 } 330 331 /** 332 * Add and return a predicate builder (or a root query if no root query exists yet) for selecting on a <code>_tag</code> search parameter 333 */ 334 public TagPredicateBuilder addTagPredicateBuilder(@Nullable DbColumn theSourceJoinColumn) { 335 TagPredicateBuilder retVal = mySqlBuilderFactory.newTagPredicateBuilder(this); 336 addTable(retVal, theSourceJoinColumn); 337 return retVal; 338 } 339 340 /** 341 * Create, add and return a predicate builder (or a root query if no root query exists yet) for selecting on a TOKEN search parameter 342 */ 343 public TokenPredicateBuilder addTokenPredicateBuilder(@Nullable DbColumn theSourceJoinColumn) { 344 TokenPredicateBuilder retVal = createTokenPredicateBuilder(); 345 addTable(retVal, theSourceJoinColumn); 346 return retVal; 347 } 348 349 /** 350 * Create a predicate builder for selecting on a TOKEN search parameter 351 */ 352 public TokenPredicateBuilder createTokenPredicateBuilder() { 353 return mySqlBuilderFactory.tokenIndexTable(this); 354 } 355 356 public void addCustomJoin( 357 SelectQuery.JoinType theJoinType, DbTable theFromTable, DbTable theToTable, Condition theCondition) { 358 mySelect.addCustomJoin(theJoinType, theFromTable, theToTable, theCondition); 359 } 360 361 public ComboCondition createOnCondition(DbColumn theSourceColumn, DbColumn theTargetColumn) { 362 ComboCondition onCondition = ComboCondition.and(); 363 onCondition.addCondition(BinaryCondition.equalTo(theSourceColumn, theTargetColumn)); 364 365 return onCondition; 366 } 367 368 /** 369 * Add and return a predicate builder (or a root query if no root query exists yet) for selecting on a <code>:missing</code> search parameter 370 */ 371 public SearchParamPresentPredicateBuilder addSearchParamPresentPredicateBuilder( 372 @Nullable DbColumn theSourceJoinColumn) { 373 SearchParamPresentPredicateBuilder retVal = mySqlBuilderFactory.searchParamPresentPredicateBuilder(this); 374 addTable(retVal, theSourceJoinColumn); 375 return retVal; 376 } 377 378 /** 379 * Create, add and return a predicate builder (or a root query if no root query exists yet) for selecting on a URI search parameter 380 */ 381 public UriPredicateBuilder addUriPredicateBuilder(@Nullable DbColumn theSourceJoinColumn) { 382 UriPredicateBuilder retVal = createUriPredicateBuilder(); 383 addTable(retVal, theSourceJoinColumn); 384 return retVal; 385 } 386 387 /** 388 * Create a predicate builder for selecting on a URI search parameter 389 */ 390 public UriPredicateBuilder createUriPredicateBuilder() { 391 return mySqlBuilderFactory.uriIndexTable(this); 392 } 393 394 public SqlObjectFactory getSqlBuilderFactory() { 395 return mySqlBuilderFactory; 396 } 397 398 public ResourceIdPredicateBuilder newResourceIdBuilder() { 399 return mySqlBuilderFactory.resourceId(this); 400 } 401 402 /** 403 * Add and return a predicate builder (or a root query if no root query exists yet) for an arbitrary table 404 */ 405 private void addTable(BaseJoiningPredicateBuilder thePredicateBuilder, @Nullable DbColumn theSourceJoinColumn) { 406 addTable(thePredicateBuilder, theSourceJoinColumn, SelectQuery.JoinType.INNER); 407 } 408 409 private void addTable( 410 BaseJoiningPredicateBuilder thePredicateBuilder, 411 @Nullable DbColumn theSourceJoinColumn, 412 SelectQuery.JoinType theJoinType) { 413 if (theSourceJoinColumn != null) { 414 DbTable fromTable = theSourceJoinColumn.getTable(); 415 DbTable toTable = thePredicateBuilder.getTable(); 416 DbColumn toColumn = thePredicateBuilder.getResourceIdColumn(); 417 addJoin(fromTable, toTable, theSourceJoinColumn, toColumn, theJoinType); 418 } else { 419 if (myFirstPredicateBuilder == null) { 420 421 BaseJoiningPredicateBuilder root; 422 if (!myNeedResourceTableRoot) { 423 root = thePredicateBuilder; 424 } else { 425 if (thePredicateBuilder instanceof ResourceTablePredicateBuilder) { 426 root = thePredicateBuilder; 427 } else { 428 root = mySqlBuilderFactory.resourceTable(this); 429 } 430 } 431 432 if (myCountQuery) { 433 mySelect.addCustomColumns( 434 FunctionCall.count().setIsDistinct(true).addColumnParams(root.getResourceIdColumn())); 435 } else { 436 mySelectedResourceIdColumn = root.getResourceIdColumn(); 437 mySelect.addColumns(mySelectedResourceIdColumn); 438 } 439 mySelect.addFromTable(root.getTable()); 440 myFirstPredicateBuilder = root; 441 442 if (!myNeedResourceTableRoot || (thePredicateBuilder instanceof ResourceTablePredicateBuilder)) { 443 return; 444 } 445 } 446 447 DbTable fromTable = myFirstPredicateBuilder.getTable(); 448 DbTable toTable = thePredicateBuilder.getTable(); 449 DbColumn fromColumn = myFirstPredicateBuilder.getResourceIdColumn(); 450 DbColumn toColumn = thePredicateBuilder.getResourceIdColumn(); 451 addJoin(fromTable, toTable, fromColumn, toColumn, theJoinType); 452 } 453 } 454 455 public void addJoin( 456 DbTable theFromTable, 457 DbTable theToTable, 458 DbColumn theFromColumn, 459 DbColumn theToColumn, 460 SelectQuery.JoinType theJoinType) { 461 Join join = new DbJoin( 462 mySpec, theFromTable, theToTable, new DbColumn[] {theFromColumn}, new DbColumn[] {theToColumn}); 463 mySelect.addJoins(theJoinType, join); 464 } 465 466 public void addJoin(DbTable theFromTable, DbTable theToTable, DbColumn theFromColumn, DbColumn theToColumn) { 467 Join join = new DbJoin( 468 mySpec, theFromTable, theToTable, new DbColumn[] {theFromColumn}, new DbColumn[] {theToColumn}); 469 mySelect.addJoins(SelectQuery.JoinType.INNER, join); 470 } 471 472 /** 473 * Generate and return the SQL generated by this builder 474 */ 475 public GeneratedSql generate(@Nullable Integer theOffset, @Nullable Integer theMaxResultsToFetch) { 476 477 getOrCreateFirstPredicateBuilder(); 478 479 mySelect.validate(); 480 String sql = mySelect.toString(); 481 482 List<Object> bindVariables = new ArrayList<>(); 483 while (true) { 484 485 int idx = sql.indexOf(myBindVariableSubstitutionBase); 486 if (idx == -1) { 487 break; 488 } 489 490 int endIdx = sql.indexOf("'", idx + myBindVariableSubstitutionBase.length()); 491 String substitutionIndexString = sql.substring(idx + myBindVariableSubstitutionBase.length(), endIdx); 492 int substitutionIndex = Integer.parseInt(substitutionIndexString); 493 bindVariables.add(myBindVariableValues.get(substitutionIndex)); 494 495 sql = sql.substring(0, idx - 1) + "?" + sql.substring(endIdx + 1); 496 } 497 498 Integer maxResultsToFetch = theMaxResultsToFetch; 499 Integer offset = theOffset; 500 if (offset != null && offset == 0) { 501 offset = null; 502 } 503 if (maxResultsToFetch != null || offset != null) { 504 505 maxResultsToFetch = defaultIfNull(maxResultsToFetch, 10000); 506 507 AbstractLimitHandler limitHandler = (AbstractLimitHandler) myDialect.getLimitHandler(); 508 Limit selection = new Limit(); 509 selection.setFirstRow(offset); 510 selection.setMaxRows(maxResultsToFetch); 511 QueryOptions queryOptions = new QueryOptionsImpl(); 512 sql = limitHandler.processSql(sql, selection, queryOptions); 513 514 int startOfQueryParameterIndex = 0; 515 516 boolean isSqlServer = (myDialect instanceof SQLServerDialect); 517 if (isSqlServer) { 518 519 /* 520 * SQL server requires an ORDER BY clause to be present in the SQL if there is 521 * an OFFSET/FETCH FIRST clause, so if there isn't already an ORDER BY clause, 522 * the dialect will automatically add an order by with a pseudo-column name. This 523 * happens in SQLServer2012LimitHandler. 524 * 525 * But, SQL Server also pukes if you include an ORDER BY on a column that you 526 * aren't also SELECTing, if the select statement is DISTINCT. Who knows why SQL 527 * Server is so picky.. but anyhow, this causes an issue, so we manually replace 528 * the pseudo-column with an actual selected column. 529 */ 530 if (sql.startsWith("SELECT DISTINCT ")) { 531 if (sql.contains("order by @@version")) { 532 if (mySelectedResourceIdColumn != null) { 533 sql = sql.replace( 534 "order by @@version", "order by " + mySelectedResourceIdColumn.getColumnNameSQL()); 535 } 536 } 537 } 538 539 // The SQLServerDialect has a bunch of one-off processing to deal with rules on when 540 // a limit can be used, so we can't rely on the flags that the limithandler exposes since 541 // the exact structure of the query depends on the parameters 542 if (sql.contains("top(?)")) { 543 bindVariables.add(0, maxResultsToFetch); 544 } 545 if (sql.contains("offset 0 rows fetch first ? rows only")) { 546 bindVariables.add(maxResultsToFetch); 547 } 548 if (sql.contains("offset ? rows fetch next ? rows only")) { 549 bindVariables.add(theOffset); 550 bindVariables.add(maxResultsToFetch); 551 } 552 if (offset != null && sql.contains("rownumber_")) { 553 bindVariables.add(theOffset + 1); 554 bindVariables.add(theOffset + maxResultsToFetch + 1); 555 } 556 557 } else if (limitHandler.supportsVariableLimit()) { 558 559 boolean bindLimitParametersFirst = limitHandler.bindLimitParametersFirst(); 560 if (limitHandler.useMaxForLimit() && offset != null) { 561 maxResultsToFetch = maxResultsToFetch + offset; 562 } 563 564 if (limitHandler.bindLimitParametersInReverseOrder()) { 565 startOfQueryParameterIndex = bindCountParameter( 566 bindVariables, 567 maxResultsToFetch, 568 limitHandler, 569 startOfQueryParameterIndex, 570 bindLimitParametersFirst); 571 bindOffsetParameter( 572 bindVariables, offset, limitHandler, startOfQueryParameterIndex, bindLimitParametersFirst); 573 } else { 574 startOfQueryParameterIndex = bindOffsetParameter( 575 bindVariables, offset, limitHandler, startOfQueryParameterIndex, bindLimitParametersFirst); 576 bindCountParameter( 577 bindVariables, 578 maxResultsToFetch, 579 limitHandler, 580 startOfQueryParameterIndex, 581 bindLimitParametersFirst); 582 } 583 } 584 } 585 586 return new GeneratedSql(myMatchNothing, sql, bindVariables); 587 } 588 589 private int bindCountParameter( 590 List<Object> bindVariables, 591 Integer maxResultsToFetch, 592 AbstractLimitHandler limitHandler, 593 int startOfQueryParameterIndex, 594 boolean bindLimitParametersFirst) { 595 if (limitHandler.supportsLimit()) { 596 if (bindLimitParametersFirst) { 597 bindVariables.add(startOfQueryParameterIndex++, maxResultsToFetch); 598 } else { 599 bindVariables.add(maxResultsToFetch); 600 } 601 } 602 return startOfQueryParameterIndex; 603 } 604 605 public int bindOffsetParameter( 606 List<Object> theBindVariables, 607 @Nullable Integer theOffset, 608 AbstractLimitHandler theLimitHandler, 609 int theStartOfQueryParameterIndex, 610 boolean theBindLimitParametersFirst) { 611 if (theLimitHandler.supportsLimitOffset() && theOffset != null) { 612 if (theBindLimitParametersFirst) { 613 theBindVariables.add(theStartOfQueryParameterIndex++, theOffset); 614 } else { 615 theBindVariables.add(theOffset); 616 } 617 } 618 return theStartOfQueryParameterIndex; 619 } 620 621 /** 622 * If at least one predicate builder already exists, return the last one added to the chain. If none has been selected, create a builder on HFJ_RESOURCE, add it and return it. 623 */ 624 public BaseJoiningPredicateBuilder getOrCreateFirstPredicateBuilder() { 625 return getOrCreateFirstPredicateBuilder(true); 626 } 627 628 /** 629 * If at least one predicate builder already exists, return the last one added to the chain. If none has been selected, create a builder on HFJ_RESOURCE, add it and return it. 630 */ 631 public BaseJoiningPredicateBuilder getOrCreateFirstPredicateBuilder( 632 boolean theIncludeResourceTypeAndNonDeletedFlag) { 633 if (myFirstPredicateBuilder == null) { 634 getOrCreateResourceTablePredicateBuilder(theIncludeResourceTypeAndNonDeletedFlag); 635 } 636 return myFirstPredicateBuilder; 637 } 638 639 public ResourceTablePredicateBuilder getOrCreateResourceTablePredicateBuilder() { 640 return getOrCreateResourceTablePredicateBuilder(true); 641 } 642 643 public ResourceTablePredicateBuilder getOrCreateResourceTablePredicateBuilder( 644 boolean theIncludeResourceTypeAndNonDeletedFlag) { 645 if (myResourceTableRoot == null) { 646 ResourceTablePredicateBuilder resourceTable = mySqlBuilderFactory.resourceTable(this); 647 addTable(resourceTable, null); 648 if (theIncludeResourceTypeAndNonDeletedFlag) { 649 Condition typeAndDeletionPredicate = resourceTable.createResourceTypeAndNonDeletedPredicates(); 650 addPredicate(typeAndDeletionPredicate); 651 } 652 myResourceTableRoot = resourceTable; 653 } 654 return myResourceTableRoot; 655 } 656 657 /** 658 * The SQL Builder library has one annoying limitation, which is that it does not use/understand bind variables 659 * for its generated SQL. So we work around this by replacing our contents with a string in the SQL consisting 660 * of <code>[random UUID]-[value index]</code> and then 661 */ 662 public String generatePlaceholder(Object theValue) { 663 String placeholder = myBindVariableSubstitutionBase + myBindVariableValues.size(); 664 myBindVariableValues.add(theValue); 665 return placeholder; 666 } 667 668 public List<String> generatePlaceholders(Collection<?> theValues) { 669 return theValues.stream().map(this::generatePlaceholder).collect(Collectors.toList()); 670 } 671 672 public int countBindVariables() { 673 return myBindVariableValues.size(); 674 } 675 676 public void setMatchNothing() { 677 myMatchNothing = true; 678 } 679 680 public DbTable addTable(String theTableName) { 681 return mySchema.addTable(theTableName); 682 } 683 684 public PartitionSettings getPartitionSettings() { 685 return myPartitionSettings; 686 } 687 688 public RequestPartitionId getRequestPartitionId() { 689 return myRequestPartitionId; 690 } 691 692 public String getResourceType() { 693 return myResourceType; 694 } 695 696 public StorageSettings getStorageSettings() { 697 return myStorageSettings; 698 } 699 700 public void addPredicate(@Nonnull Condition theCondition) { 701 assert theCondition != null; 702 mySelect.addCondition(theCondition); 703 myHaveAtLeastOnePredicate = true; 704 } 705 706 public ComboCondition addPredicateLastUpdated(DateRangeParam theDateRange) { 707 ResourceTablePredicateBuilder resourceTableRoot = getOrCreateResourceTablePredicateBuilder(false); 708 return addPredicateLastUpdated(theDateRange, resourceTableRoot); 709 } 710 711 public ComboCondition addPredicateLastUpdated( 712 DateRangeParam theDateRange, ResourceTablePredicateBuilder theResourceTablePredicateBuilder) { 713 List<Condition> conditions = new ArrayList<>(2); 714 BinaryCondition condition; 715 716 if (isNotEqualsComparator(theDateRange)) { 717 condition = createConditionForValueWithComparator( 718 LESSTHAN, 719 theResourceTablePredicateBuilder.getLastUpdatedColumn(), 720 theDateRange.getLowerBoundAsInstant()); 721 conditions.add(condition); 722 condition = createConditionForValueWithComparator( 723 GREATERTHAN, 724 theResourceTablePredicateBuilder.getLastUpdatedColumn(), 725 theDateRange.getUpperBoundAsInstant()); 726 conditions.add(condition); 727 return ComboCondition.or(conditions.toArray(new Condition[0])); 728 } 729 730 if (theDateRange.getLowerBoundAsInstant() != null) { 731 condition = createConditionForValueWithComparator( 732 GREATERTHAN_OR_EQUALS, 733 theResourceTablePredicateBuilder.getLastUpdatedColumn(), 734 theDateRange.getLowerBoundAsInstant()); 735 conditions.add(condition); 736 } 737 738 if (theDateRange.getUpperBoundAsInstant() != null) { 739 condition = createConditionForValueWithComparator( 740 LESSTHAN_OR_EQUALS, 741 theResourceTablePredicateBuilder.getLastUpdatedColumn(), 742 theDateRange.getUpperBoundAsInstant()); 743 conditions.add(condition); 744 } 745 746 return ComboCondition.and(conditions.toArray(new Condition[0])); 747 } 748 749 private boolean isNotEqualsComparator(DateRangeParam theDateRange) { 750 if (theDateRange != null) { 751 DateParam lb = theDateRange.getLowerBound(); 752 DateParam ub = theDateRange.getUpperBound(); 753 754 return lb != null 755 && ub != null 756 && lb.getPrefix().equals(NOT_EQUAL) 757 && ub.getPrefix().equals(NOT_EQUAL); 758 } 759 return false; 760 } 761 762 public void addResourceIdsPredicate(List<Long> thePidList) { 763 DbColumn resourceIdColumn = getOrCreateFirstPredicateBuilder().getResourceIdColumn(); 764 InCondition predicate = new InCondition(resourceIdColumn, generatePlaceholders(thePidList)); 765 addPredicate(predicate); 766 } 767 768 public void excludeResourceIdsPredicate(Set<JpaPid> theExistingPidSetToExclude) { 769 770 // Do nothing if it's empty 771 if (theExistingPidSetToExclude == null || theExistingPidSetToExclude.isEmpty()) return; 772 773 List<Long> excludePids = JpaPid.toLongList(theExistingPidSetToExclude); 774 775 ourLog.trace("excludePids = {}", excludePids); 776 777 DbColumn resourceIdColumn = getOrCreateFirstPredicateBuilder().getResourceIdColumn(); 778 InCondition predicate = new InCondition(resourceIdColumn, generatePlaceholders(excludePids)); 779 predicate.setNegate(true); 780 addPredicate(predicate); 781 } 782 783 public BinaryCondition createConditionForValueWithComparator( 784 ParamPrefixEnum theComparator, DbColumn theColumn, Object theValue) { 785 switch (theComparator) { 786 case LESSTHAN: 787 return BinaryCondition.lessThan(theColumn, generatePlaceholder(theValue)); 788 case LESSTHAN_OR_EQUALS: 789 return BinaryCondition.lessThanOrEq(theColumn, generatePlaceholder(theValue)); 790 case GREATERTHAN: 791 return BinaryCondition.greaterThan(theColumn, generatePlaceholder(theValue)); 792 case GREATERTHAN_OR_EQUALS: 793 return BinaryCondition.greaterThanOrEq(theColumn, generatePlaceholder(theValue)); 794 case NOT_EQUAL: 795 return BinaryCondition.notEqualTo(theColumn, generatePlaceholder(theValue)); 796 case STARTS_AFTER: 797 case APPROXIMATE: 798 case ENDS_BEFORE: 799 case EQUAL: 800 default: 801 throw new IllegalArgumentException(Msg.code(1263)); 802 } 803 } 804 805 public SearchQueryBuilder newChildSqlBuilder() { 806 return new SearchQueryBuilder( 807 myFhirContext, 808 myStorageSettings, 809 myPartitionSettings, 810 myRequestPartitionId, 811 myResourceType, 812 mySqlBuilderFactory, 813 myBindVariableSubstitutionBase, 814 myDialect, 815 false, 816 myBindVariableValues); 817 } 818 819 public SelectQuery getSelect() { 820 return mySelect; 821 } 822 823 public boolean haveAtLeastOnePredicate() { 824 return myHaveAtLeastOnePredicate; 825 } 826 827 public void addSortCoordsNear( 828 CoordsPredicateBuilder theCoordsBuilder, 829 double theLatitudeValue, 830 double theLongitudeValue, 831 boolean theAscending) { 832 FunctionCall absLatitude = new FunctionCall("ABS"); 833 String latitudePlaceholder = generatePlaceholder(theLatitudeValue); 834 ComboExpression absLatitudeMiddle = new ComboExpression( 835 ComboExpression.Op.SUBTRACT, theCoordsBuilder.getColumnLatitude(), latitudePlaceholder); 836 absLatitude = absLatitude.addCustomParams(absLatitudeMiddle); 837 838 FunctionCall absLongitude = new FunctionCall("ABS"); 839 String longitudePlaceholder = generatePlaceholder(theLongitudeValue); 840 ComboExpression absLongitudeMiddle = new ComboExpression( 841 ComboExpression.Op.SUBTRACT, theCoordsBuilder.getColumnLongitude(), longitudePlaceholder); 842 absLongitude = absLongitude.addCustomParams(absLongitudeMiddle); 843 844 ComboExpression sum = new ComboExpression(ComboExpression.Op.ADD, absLatitude, absLongitude); 845 String ordering; 846 if (theAscending) { 847 ordering = ""; 848 } else { 849 ordering = " DESC"; 850 } 851 852 String columnName = "MHD" + (myNextNearnessColumnId++); 853 mySelect.addAliasedColumn(sum, columnName); 854 mySelect.addCustomOrderings(columnName + ordering); 855 } 856 857 public void addSortString(DbColumn theColumnValueNormalized, boolean theAscending) { 858 addSortString(theColumnValueNormalized, theAscending, false); 859 } 860 861 public void addSortString(DbColumn theColumnValueNormalized, boolean theAscending, boolean theUseAggregate) { 862 OrderObject.NullOrder nullOrder = OrderObject.NullOrder.LAST; 863 addSortString(theColumnValueNormalized, theAscending, nullOrder, theUseAggregate); 864 } 865 866 public void addSortNumeric(DbColumn theColumnValueNormalized, boolean theAscending) { 867 addSortNumeric(theColumnValueNormalized, theAscending, false); 868 } 869 870 public void addSortNumeric(DbColumn theColumnValueNormalized, boolean theAscending, boolean theUseAggregate) { 871 OrderObject.NullOrder nullOrder = OrderObject.NullOrder.LAST; 872 addSortNumeric(theColumnValueNormalized, theAscending, nullOrder, theUseAggregate); 873 } 874 875 public void addSortDate(DbColumn theColumnValueNormalized, boolean theAscending) { 876 addSortDate(theColumnValueNormalized, theAscending, false); 877 } 878 879 public void addSortDate(DbColumn theColumnValueNormalized, boolean theAscending, boolean theUseAggregate) { 880 OrderObject.NullOrder nullOrder = OrderObject.NullOrder.LAST; 881 addSortDate(theColumnValueNormalized, theAscending, nullOrder, theUseAggregate); 882 } 883 884 public void addSortString( 885 DbColumn theTheColumnValueNormalized, 886 boolean theTheAscending, 887 OrderObject.NullOrder theNullOrder, 888 boolean theUseAggregate) { 889 if ((dialectIsMySql || dialectIsMsSql)) { 890 // MariaDB, MySQL and MSSQL do not support "NULLS FIRST" and "NULLS LAST" syntax. 891 String direction = theTheAscending ? " ASC" : " DESC"; 892 String sortColumnName = 893 theTheColumnValueNormalized.getTable().getAlias() + "." + theTheColumnValueNormalized.getName(); 894 final StringBuilder sortColumnNameBuilder = new StringBuilder(); 895 // The following block has been commented out for performance. 896 // Uncomment if NullOrder is needed for MariaDB, MySQL or MSSQL 897 /* 898 // Null values are always treated as less than non-null values. 899 if ((theTheAscending && theNullOrder == OrderObject.NullOrder.LAST) 900 || (!theTheAscending && theNullOrder == OrderObject.NullOrder.FIRST)) { 901 // In this case, precede the "order by" column with a case statement that returns 902 // 1 for null and 0 non-null so that nulls will be sorted as greater than non-nulls. 903 sortColumnNameBuilder.append( "CASE WHEN " ).append( sortColumnName ).append( " IS NULL THEN 1 ELSE 0 END" ).append(direction).append(", "); 904 } 905 */ 906 sortColumnName = formatColumnNameForAggregate(theTheAscending, theUseAggregate, sortColumnName); 907 sortColumnNameBuilder.append(sortColumnName).append(direction); 908 mySelect.addCustomOrderings(sortColumnNameBuilder.toString()); 909 } else { 910 addSort(theTheColumnValueNormalized, theTheAscending, theNullOrder, theUseAggregate); 911 } 912 } 913 914 private static String formatColumnNameForAggregate( 915 boolean theTheAscending, boolean theUseAggregate, String sortColumnName) { 916 if (theUseAggregate) { 917 String aggregateFunction; 918 if (theTheAscending) { 919 aggregateFunction = "MIN"; 920 } else { 921 aggregateFunction = "MAX"; 922 } 923 sortColumnName = aggregateFunction + "(" + sortColumnName + ")"; 924 } 925 return sortColumnName; 926 } 927 928 public void addSortNumeric( 929 DbColumn theTheColumnValueNormalized, 930 boolean theAscending, 931 OrderObject.NullOrder theNullOrder, 932 boolean theUseAggregate) { 933 if ((dialectIsMySql || dialectIsMsSql)) { 934 // MariaDB, MySQL and MSSQL do not support "NULLS FIRST" and "NULLS LAST" syntax. 935 // Null values are always treated as less than non-null values. 936 // As such special handling is required here. 937 String direction; 938 String sortColumnName = 939 theTheColumnValueNormalized.getTable().getAlias() + "." + theTheColumnValueNormalized.getName(); 940 if ((theAscending && theNullOrder == OrderObject.NullOrder.LAST) 941 || (!theAscending && theNullOrder == OrderObject.NullOrder.FIRST)) { 942 // Negating the numeric column value and reversing the sort order will ensure that the rows appear 943 // in the correct order with nulls appearing first or last as needed. 944 direction = theAscending ? " DESC" : " ASC"; 945 sortColumnName = "-" + sortColumnName; 946 } else { 947 direction = theAscending ? " ASC" : " DESC"; 948 } 949 sortColumnName = formatColumnNameForAggregate(theAscending, theUseAggregate, sortColumnName); 950 mySelect.addCustomOrderings(sortColumnName + direction); 951 } else { 952 addSort(theTheColumnValueNormalized, theAscending, theNullOrder, theUseAggregate); 953 } 954 } 955 956 public void addSortDate( 957 DbColumn theTheColumnValueNormalized, 958 boolean theTheAscending, 959 OrderObject.NullOrder theNullOrder, 960 boolean theUseAggregate) { 961 if ((dialectIsMySql || dialectIsMsSql)) { 962 // MariaDB, MySQL and MSSQL do not support "NULLS FIRST" and "NULLS LAST" syntax. 963 String direction = theTheAscending ? " ASC" : " DESC"; 964 String sortColumnName = 965 theTheColumnValueNormalized.getTable().getAlias() + "." + theTheColumnValueNormalized.getName(); 966 final StringBuilder sortColumnNameBuilder = new StringBuilder(); 967 // The following block has been commented out for performance. 968 // Uncomment if NullOrder is needed for MariaDB, MySQL or MSSQL 969 /* 970 // Null values are always treated as less than non-null values. 971 if ((theTheAscending && theNullOrder == OrderObject.NullOrder.LAST) 972 || (!theTheAscending && theNullOrder == OrderObject.NullOrder.FIRST)) { 973 // In this case, precede the "order by" column with a case statement that returns 974 // 1 for null and 0 non-null so that nulls will be sorted as greater than non-nulls. 975 sortColumnNameBuilder.append( "CASE WHEN " ).append( sortColumnName ).append( " IS NULL THEN 1 ELSE 0 END" ).append(direction).append(", "); 976 } 977 */ 978 sortColumnName = formatColumnNameForAggregate(theTheAscending, theUseAggregate, sortColumnName); 979 sortColumnNameBuilder.append(sortColumnName).append(direction); 980 mySelect.addCustomOrderings(sortColumnNameBuilder.toString()); 981 } else { 982 addSort(theTheColumnValueNormalized, theTheAscending, theNullOrder, theUseAggregate); 983 } 984 } 985 986 private void addSort( 987 DbColumn theTheColumnValueNormalized, 988 boolean theTheAscending, 989 OrderObject.NullOrder theNullOrder, 990 boolean theUseAggregate) { 991 OrderObject.Dir direction = theTheAscending ? OrderObject.Dir.ASCENDING : OrderObject.Dir.DESCENDING; 992 Object columnToOrder = theTheColumnValueNormalized; 993 if (theUseAggregate) { 994 if (theTheAscending) { 995 columnToOrder = FunctionCall.min().addColumnParams(theTheColumnValueNormalized); 996 } else { 997 columnToOrder = FunctionCall.max().addColumnParams(theTheColumnValueNormalized); 998 } 999 } 1000 OrderObject orderObject = new OrderObject(direction, columnToOrder); 1001 orderObject.setNullOrder(theNullOrder); 1002 mySelect.addCustomOrderings(orderObject); 1003 } 1004 1005 /** 1006 * If set to true (default is false), force the generated SQL to start 1007 * with the {@link ca.uhn.fhir.jpa.model.entity.ResourceTable HFJ_RESOURCE} 1008 * table at the root of the query. 1009 * <p> 1010 * This seems to perform better if there are multiple joins on the 1011 * resource ID table. 1012 */ 1013 public void setNeedResourceTableRoot(boolean theNeedResourceTableRoot) { 1014 myNeedResourceTableRoot = theNeedResourceTableRoot; 1015 } 1016}