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}