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.dao.data;
021
022import ca.uhn.fhir.jpa.model.entity.ResourceTable;
023import org.springframework.data.domain.Pageable;
024import org.springframework.data.domain.Slice;
025import org.springframework.data.jpa.repository.JpaRepository;
026import org.springframework.data.jpa.repository.Modifying;
027import org.springframework.data.jpa.repository.Query;
028import org.springframework.data.repository.query.Param;
029import org.springframework.transaction.annotation.Propagation;
030import org.springframework.transaction.annotation.Transactional;
031
032import java.util.Collection;
033import java.util.Date;
034import java.util.List;
035import java.util.Map;
036import java.util.Optional;
037
038@Transactional(propagation = Propagation.MANDATORY)
039public interface IResourceTableDao extends JpaRepository<ResourceTable, Long>, IHapiFhirJpaRepository {
040
041        @Query("SELECT t.myId FROM ResourceTable t WHERE t.myDeleted IS NOT NULL")
042        Slice<Long> findIdsOfDeletedResources(Pageable thePageable);
043
044        @Query("SELECT t.myId FROM ResourceTable t WHERE t.myResourceType = :restype AND t.myDeleted IS NOT NULL")
045        Slice<Long> findIdsOfDeletedResourcesOfType(Pageable thePageable, @Param("restype") String theResourceName);
046
047        @Query(
048                        "SELECT t.myId FROM ResourceTable t WHERE t.myId = :resid AND t.myResourceType = :restype AND t.myDeleted IS NOT NULL")
049        Slice<Long> findIdsOfDeletedResourcesOfType(
050                        Pageable thePageable, @Param("resid") Long theResourceId, @Param("restype") String theResourceName);
051
052        @Query(
053                        "SELECT t.myResourceType as type, COUNT(t.myResourceType) as count FROM ResourceTable t GROUP BY t.myResourceType")
054        List<Map<?, ?>> getResourceCounts();
055
056        @Query(
057                        "SELECT t.myId FROM ResourceTable t WHERE t.myUpdated >= :low AND t.myUpdated <= :high ORDER BY t.myUpdated DESC")
058        Slice<Long> findIdsOfResourcesWithinUpdatedRangeOrderedFromNewest(
059                        Pageable thePage, @Param("low") Date theLow, @Param("high") Date theHigh);
060
061        @Query(
062                        "SELECT t.myId FROM ResourceTable t WHERE t.myUpdated >= :low AND t.myUpdated <= :high ORDER BY t.myUpdated ASC")
063        Slice<Long> findIdsOfResourcesWithinUpdatedRangeOrderedFromOldest(
064                        Pageable thePage, @Param("low") Date theLow, @Param("high") Date theHigh);
065
066        /**
067         * @return List of arrays containing [PID, resourceType, lastUpdated]
068         */
069        @Query(
070                        "SELECT t.myId, t.myResourceType, t.myUpdated FROM ResourceTable t WHERE t.myUpdated >= :low AND t.myUpdated <= :high ORDER BY t.myUpdated ASC")
071        Slice<Object[]> findIdsTypesAndUpdateTimesOfResourcesWithinUpdatedRangeOrderedFromOldest(
072                        Pageable thePage, @Param("low") Date theLow, @Param("high") Date theHigh);
073
074        /**
075         * @return List of arrays containing [PID, resourceType, lastUpdated]
076         */
077        @Query(
078                        "SELECT t.myId, t.myResourceType, t.myUpdated FROM ResourceTable t WHERE t.myUpdated >= :low AND t.myUpdated <= :high AND t.myPartitionIdValue IN (:partition_ids) ORDER BY t.myUpdated ASC")
079        Slice<Object[]> findIdsTypesAndUpdateTimesOfResourcesWithinUpdatedRangeOrderedFromOldestForPartitionIds(
080                        Pageable thePage,
081                        @Param("low") Date theLow,
082                        @Param("high") Date theHigh,
083                        @Param("partition_ids") List<Integer> theRequestPartitionIds);
084
085        /**
086         * @return List of arrays containing [PID, resourceType, lastUpdated]
087         */
088        @Query(
089                        "SELECT t.myId, t.myResourceType, t.myUpdated FROM ResourceTable t WHERE t.myUpdated >= :low AND t.myUpdated <= :high ORDER BY t.myUpdated ASC")
090        Slice<Object[]> findIdsTypesAndUpdateTimesOfResourcesWithinUpdatedRangeOrderedFromOldestForDefaultPartition(
091                        Pageable thePage, @Param("low") Date theLow, @Param("high") Date theHigh);
092
093        // TODO in the future, consider sorting by pid as well so batch jobs process in the same order across restarts
094        @Query(
095                        "SELECT t.myId FROM ResourceTable t WHERE t.myUpdated >= :low AND t.myUpdated <= :high AND t.myPartitionIdValue = :partition_id ORDER BY t.myUpdated ASC")
096        Slice<Long> findIdsOfPartitionedResourcesWithinUpdatedRangeOrderedFromOldest(
097                        Pageable thePage,
098                        @Param("low") Date theLow,
099                        @Param("high") Date theHigh,
100                        @Param("partition_id") Integer theRequestPartitionId);
101
102        @Query(
103                        "SELECT t.myId FROM ResourceTable t WHERE t.myUpdated >= :low AND t.myUpdated <= :high AND t.myResourceType = :restype ORDER BY t.myUpdated ASC")
104        Slice<Long> findIdsOfResourcesWithinUpdatedRangeOrderedFromOldest(
105                        Pageable thePage,
106                        @Param("restype") String theResourceType,
107                        @Param("low") Date theLow,
108                        @Param("high") Date theHigh);
109
110        @Modifying
111        @Query("UPDATE ResourceTable t SET t.myIndexStatus = :status WHERE t.myId = :id")
112        void updateIndexStatus(@Param("id") Long theId, @Param("status") Long theIndexStatus);
113
114        @Modifying
115        @Query("UPDATE ResourceTable t SET t.myUpdated = :updated WHERE t.myId = :id")
116        void updateLastUpdated(@Param("id") Long theId, @Param("updated") Date theUpdated);
117
118        @Modifying
119        @Query("DELETE FROM ResourceTable t WHERE t.myId = :pid")
120        void deleteByPid(@Param("pid") Long theId);
121
122        /**
123         * This method returns a Collection where each row is an element in the collection. Each element in the collection
124         * is an object array, where the order matters (the array represents columns returned by the query). Be careful if you change this query in any way.
125         */
126        @Query("SELECT t.myResourceType, t.myId, t.myDeleted FROM ResourceTable t WHERE t.myId IN (:pid)")
127        Collection<Object[]> findLookupFieldsByResourcePid(@Param("pid") List<Long> thePids);
128
129        /**
130         * This method returns a Collection where each row is an element in the collection. Each element in the collection
131         * is an object array, where the order matters (the array represents columns returned by the query). Be careful if you change this query in any way.
132         */
133        @Query(
134                        "SELECT t.myResourceType, t.myId, t.myDeleted FROM ResourceTable t WHERE t.myId IN (:pid) AND t.myPartitionIdValue IN :partition_id")
135        Collection<Object[]> findLookupFieldsByResourcePidInPartitionIds(
136                        @Param("pid") List<Long> thePids, @Param("partition_id") Collection<Integer> thePartitionId);
137
138        /**
139         * This method returns a Collection where each row is an element in the collection. Each element in the collection
140         * is an object array, where the order matters (the array represents columns returned by the query). Be careful if you change this query in any way.
141         */
142        @Query(
143                        "SELECT t.myResourceType, t.myId, t.myDeleted FROM ResourceTable t WHERE t.myId IN (:pid) AND (t.myPartitionIdValue IS NULL OR t.myPartitionIdValue IN :partition_id)")
144        Collection<Object[]> findLookupFieldsByResourcePidInPartitionIdsOrNullPartition(
145                        @Param("pid") List<Long> thePids, @Param("partition_id") Collection<Integer> thePartitionId);
146
147        /**
148         * This method returns a Collection where each row is an element in the collection. Each element in the collection
149         * is an object array, where the order matters (the array represents columns returned by the query). Be careful if you change this query in any way.
150         */
151        @Query(
152                        "SELECT t.myResourceType, t.myId, t.myDeleted FROM ResourceTable t WHERE t.myId IN (:pid) AND t.myPartitionIdValue IS NULL")
153        Collection<Object[]> findLookupFieldsByResourcePidInPartitionNull(@Param("pid") List<Long> thePids);
154
155        @Query("SELECT t.myVersion FROM ResourceTable t WHERE t.myId = :pid")
156        Long findCurrentVersionByPid(@Param("pid") Long thePid);
157
158        /**
159         * This query will return rows with the following values:
160         * Id (resource pid - long), ResourceType (Patient, etc), version (long)
161         * Order matters!
162         * @param pid - list of pids to get versions for
163         * @return
164         */
165        @Query("SELECT t.myId, t.myResourceType, t.myVersion FROM ResourceTable t WHERE t.myId IN ( :pid )")
166        Collection<Object[]> getResourceVersionsForPid(@Param("pid") List<Long> pid);
167
168        @Query(
169                        "SELECT t FROM ResourceTable t LEFT JOIN FETCH t.myForcedId WHERE t.myPartitionId.myPartitionId IS NULL AND t.myId = :pid")
170        Optional<ResourceTable> readByPartitionIdNull(@Param("pid") Long theResourceId);
171
172        @Query(
173                        "SELECT t FROM ResourceTable t LEFT JOIN FETCH t.myForcedId WHERE t.myPartitionId.myPartitionId = :partitionId AND t.myId = :pid")
174        Optional<ResourceTable> readByPartitionId(
175                        @Param("partitionId") int thePartitionId, @Param("pid") Long theResourceId);
176
177        @Query(
178                        "SELECT t FROM ResourceTable t LEFT JOIN FETCH t.myForcedId WHERE (t.myPartitionId.myPartitionId IS NULL OR t.myPartitionId.myPartitionId IN (:partitionIds)) AND t.myId = :pid")
179        Optional<ResourceTable> readByPartitionIdsOrNull(
180                        @Param("partitionIds") Collection<Integer> thrValues, @Param("pid") Long theResourceId);
181
182        @Query(
183                        "SELECT t FROM ResourceTable t LEFT JOIN FETCH t.myForcedId WHERE t.myPartitionId.myPartitionId IN (:partitionIds) AND t.myId = :pid")
184        Optional<ResourceTable> readByPartitionIds(
185                        @Param("partitionIds") Collection<Integer> thrValues, @Param("pid") Long theResourceId);
186
187        @Query("SELECT t FROM ResourceTable t LEFT JOIN FETCH t.myForcedId WHERE t.myId IN :pids")
188        List<ResourceTable> findAllByIdAndLoadForcedIds(@Param("pids") List<Long> thePids);
189}