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