001package ca.uhn.fhir.jpa.dao.data;
002
003import ca.uhn.fhir.jpa.model.entity.ResourceTable;
004import org.springframework.data.domain.Pageable;
005import org.springframework.data.domain.Slice;
006import org.springframework.data.jpa.repository.JpaRepository;
007import org.springframework.data.jpa.repository.Modifying;
008import org.springframework.data.jpa.repository.Query;
009import org.springframework.data.repository.query.Param;
010import org.springframework.transaction.annotation.Propagation;
011import org.springframework.transaction.annotation.Transactional;
012
013import java.util.Collection;
014import java.util.Date;
015import java.util.List;
016import java.util.Map;
017import java.util.Optional;
018
019/*
020 * #%L
021 * HAPI FHIR JPA Server
022 * %%
023 * Copyright (C) 2014 - 2022 Smile CDR, Inc.
024 * %%
025 * Licensed under the Apache License, Version 2.0 (the "License");
026 * you may not use this file except in compliance with the License.
027 * You may obtain a copy of the License at
028 *
029 * http://www.apache.org/licenses/LICENSE-2.0
030 *
031 * Unless required by applicable law or agreed to in writing, software
032 * distributed under the License is distributed on an "AS IS" BASIS,
033 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
034 * See the License for the specific language governing permissions and
035 * limitations under the License.
036 * #L%
037 */
038
039@Transactional(propagation = Propagation.MANDATORY)
040public interface IResourceTableDao extends JpaRepository<ResourceTable, Long>, IHapiFhirJpaRepository {
041
042        @Query("SELECT t.myId FROM ResourceTable t WHERE t.myDeleted IS NOT NULL")
043        Slice<Long> findIdsOfDeletedResources(Pageable thePageable);
044
045        @Query("SELECT t.myId FROM ResourceTable t WHERE t.myResourceType = :restype AND t.myDeleted IS NOT NULL")
046        Slice<Long> findIdsOfDeletedResourcesOfType(Pageable thePageable, @Param("restype") String theResourceName);
047
048        @Query("SELECT t.myId FROM ResourceTable t WHERE t.myId = :resid AND t.myResourceType = :restype AND t.myDeleted IS NOT NULL")
049        Slice<Long> findIdsOfDeletedResourcesOfType(Pageable thePageable, @Param("resid") Long theResourceId, @Param("restype") String theResourceName);
050
051        @Query("SELECT t.myResourceType as type, COUNT(t.myResourceType) as count FROM ResourceTable t GROUP BY t.myResourceType")
052        List<Map<?, ?>> getResourceCounts();
053
054        @Query("SELECT t.myId FROM ResourceTable t WHERE t.myUpdated >= :low AND t.myUpdated <= :high ORDER BY t.myUpdated DESC")
055        Slice<Long> findIdsOfResourcesWithinUpdatedRangeOrderedFromNewest(Pageable thePage, @Param("low") Date theLow, @Param("high") Date theHigh);
056
057        @Query("SELECT t.myId FROM ResourceTable t WHERE t.myUpdated >= :low AND t.myUpdated <= :high ORDER BY t.myUpdated ASC")
058        Slice<Long> findIdsOfResourcesWithinUpdatedRangeOrderedFromOldest(Pageable thePage, @Param("low") Date theLow, @Param("high") Date theHigh);
059
060        /**
061         * @return List of arrays containing [PID, resourceType, lastUpdated]
062         */
063        @Query("SELECT t.myId, t.myResourceType, t.myUpdated FROM ResourceTable t WHERE t.myUpdated >= :low AND t.myUpdated <= :high ORDER BY t.myUpdated ASC")
064        Slice<Object[]> findIdsTypesAndUpdateTimesOfResourcesWithinUpdatedRangeOrderedFromOldest(Pageable thePage, @Param("low") Date theLow, @Param("high") Date theHigh);
065
066        /**
067         * @return List of arrays containing [PID, resourceType, lastUpdated]
068         */
069        @Query("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")
070        Slice<Object[]> findIdsTypesAndUpdateTimesOfResourcesWithinUpdatedRangeOrderedFromOldestForPartitionIds(Pageable thePage, @Param("low") Date theLow, @Param("high") Date theHigh, @Param("partition_ids") List<Integer> theRequestPartitionIds);
071
072        /**
073         * @return List of arrays containing [PID, resourceType, lastUpdated]
074         */
075        @Query("SELECT t.myId, t.myResourceType, t.myUpdated FROM ResourceTable t WHERE t.myUpdated >= :low AND t.myUpdated <= :high ORDER BY t.myUpdated ASC")
076        Slice<Object[]> findIdsTypesAndUpdateTimesOfResourcesWithinUpdatedRangeOrderedFromOldestForDefaultPartition(Pageable thePage, @Param("low") Date theLow, @Param("high") Date theHigh);
077
078        // TODO in the future, consider sorting by pid as well so batch jobs process in the same order across restarts
079        @Query("SELECT t.myId FROM ResourceTable t WHERE t.myUpdated >= :low AND t.myUpdated <= :high AND t.myPartitionIdValue = :partition_id ORDER BY t.myUpdated ASC")
080        Slice<Long> findIdsOfPartitionedResourcesWithinUpdatedRangeOrderedFromOldest(Pageable thePage, @Param("low") Date theLow, @Param("high") Date theHigh, @Param("partition_id") Integer theRequestPartitionId);
081
082        @Query("SELECT t.myId FROM ResourceTable t WHERE t.myUpdated >= :low AND t.myUpdated <= :high AND t.myResourceType = :restype ORDER BY t.myUpdated ASC")
083        Slice<Long> findIdsOfResourcesWithinUpdatedRangeOrderedFromOldest(Pageable thePage, @Param("restype") String theResourceType, @Param("low") Date theLow, @Param("high") Date theHigh);
084
085        @Modifying
086        @Query("UPDATE ResourceTable t SET t.myIndexStatus = :status WHERE t.myId = :id")
087        void updateIndexStatus(@Param("id") Long theId, @Param("status") Long theIndexStatus);
088
089        @Modifying
090        @Query("DELETE FROM ResourceTable t WHERE t.myId = :pid")
091        void deleteByPid(@Param("pid") Long theId);
092
093        /**
094         * This method returns a Collection where each row is an element in the collection. Each element in the collection
095         * 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.
096         */
097        @Query("SELECT t.myResourceType, t.myId, t.myDeleted FROM ResourceTable t WHERE t.myId IN (:pid)")
098        Collection<Object[]> findLookupFieldsByResourcePid(@Param("pid") List<Long> thePids);
099
100        /**
101         * This method returns a Collection where each row is an element in the collection. Each element in the collection
102         * 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.
103         */
104        @Query("SELECT t.myResourceType, t.myId, t.myDeleted FROM ResourceTable t WHERE t.myId IN (:pid) AND t.myPartitionIdValue IN :partition_id")
105        Collection<Object[]> findLookupFieldsByResourcePidInPartitionIds(@Param("pid") List<Long> thePids, @Param("partition_id") Collection<Integer> thePartitionId);
106
107        /**
108         * This method returns a Collection where each row is an element in the collection. Each element in the collection
109         * 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.
110         */
111        @Query("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)")
112        Collection<Object[]> findLookupFieldsByResourcePidInPartitionIdsOrNullPartition(@Param("pid") List<Long> thePids, @Param("partition_id") Collection<Integer> thePartitionId);
113
114        /**
115         * This method returns a Collection where each row is an element in the collection. Each element in the collection
116         * 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.
117         */
118        @Query("SELECT t.myResourceType, t.myId, t.myDeleted FROM ResourceTable t WHERE t.myId IN (:pid) AND t.myPartitionIdValue IS NULL")
119        Collection<Object[]> findLookupFieldsByResourcePidInPartitionNull(@Param("pid") List<Long> thePids);
120
121        @Query("SELECT t.myVersion FROM ResourceTable t WHERE t.myId = :pid")
122        Long findCurrentVersionByPid(@Param("pid") Long thePid);
123
124        /**
125         * This query will return rows with the following values:
126         * Id (resource pid - long), ResourceType (Patient, etc), version (long)
127         * Order matters!
128         * @param pid - list of pids to get versions for
129         * @return
130         */
131        @Query("SELECT t.myId, t.myResourceType, t.myVersion FROM ResourceTable t WHERE t.myId IN ( :pid )")
132        Collection<Object[]> getResourceVersionsForPid(@Param("pid") List<Long> pid);
133
134        @Query("SELECT t FROM ResourceTable t LEFT JOIN FETCH t.myForcedId WHERE t.myPartitionId.myPartitionId IS NULL AND t.myId = :pid")
135        Optional<ResourceTable> readByPartitionIdNull(@Param("pid") Long theResourceId);
136
137        @Query("SELECT t FROM ResourceTable t LEFT JOIN FETCH t.myForcedId WHERE t.myPartitionId.myPartitionId = :partitionId AND t.myId = :pid")
138        Optional<ResourceTable> readByPartitionId(@Param("partitionId") int thePartitionId, @Param("pid") Long theResourceId);
139
140        @Query("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")
141        Optional<ResourceTable> readByPartitionIdsOrNull(@Param("partitionIds") Collection<Integer> thrValues, @Param("pid") Long theResourceId);
142
143        @Query("SELECT t FROM ResourceTable t LEFT JOIN FETCH t.myForcedId WHERE t.myPartitionId.myPartitionId IN (:partitionIds) AND t.myId = :pid")
144        Optional<ResourceTable> readByPartitionIds(@Param("partitionIds") Collection<Integer> thrValues, @Param("pid") Long theResourceId);
145}