Mercurial > hg > orthanc-databases

comparison Framework/Plugins/IndexBackend.cpp @ 557:d8ee2f676a3c find-refactoring

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
wip: started implementing Find in PostgreSQL
author Alain Mazy <am@orthanc.team>
date Fri, 13 Sep 2024 11:56:25 +0200
parents 44e6b65f1630
children d186007b0f1e
comparison
equal deleted inserted replaced
556:7057d9db8d9a 557:d8ee2f676a3c
3054 { 3054 {
3055 std::unique_ptr<DatabaseManager> manager(new DatabaseManager(backend.CreateDatabaseFactory())); 3055 std::unique_ptr<DatabaseManager> manager(new DatabaseManager(backend.CreateDatabaseFactory()));
3056 backend.ConfigureDatabase(*manager, hasIdentifierTags, identifierTags); 3056 backend.ConfigureDatabase(*manager, hasIdentifierTags, identifierTags);
3057 return manager.release(); 3057 return manager.release();
3058 } 3058 }
3059
3060 #if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
3061 bool IndexBackend::HasFindSupport() const
3062 {
3063 // TODO-FIND move to child plugins ?
3064 return true;
3065 }
3066 #endif
3067
3068
3069 #if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
3070
3071 #define C0_QUERY_ID 0
3072 #define C1_INTERNAL_ID 1
3073 #define C2_ROW_NUMBER 2
3074 #define C3_STRING_1 3
3075 #define C4_STRING_2 4
3076 #define C5_STRING_3 5
3077 #define C6_INT_1 6
3078 #define C7_INT_2 7
3079 #define C8_BIG_INT_1 8
3080 #define C9_BIG_INT_2 9
3081
3082 #define QUERY_LOOKUP 1
3083 #define QUERY_MAIN_DICOM_TAGS 2
3084 #define QUERY_ATTACHMENTS 3
3085 #define STRINGIFY(x) #x
3086 #define TOSTRING(x) STRINGIFY(x)
3087
3088 void IndexBackend::ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response,
3089 DatabaseManager& manager,
3090 const Orthanc::DatabasePluginMessages::Find_Request& request)
3091 {
3092 // TODO-FIND move to child plugins ?
3093
3094
3095 // If we want the Find to use a read-only transaction, we can not create temporary tables with
3096 // the lookup results. So we must use a CTE (Common Table Expression).
3097 // However, a CTE can only be used in a single query -> we must unionize all the following
3098 // queries to retrieve values from various tables.
3099 // However, to use UNION, all tables must have the same columns (numbers and types). That's
3100 // why we have generic column names.
3101 // So, at the end we'll have only one very big query !
3102
3103 std::string sql;
3104
3105 // extract the resource id of interest by executing the lookup in a CTE
3106 LookupFormatter formatter(manager.GetDialect());
3107 std::string lookupSql;
3108 ISqlLookupFormatter::Apply(lookupSql, formatter, request);
3109
3110 // base query, retrieve the ordered internalId and publicId of the selected resources
3111 sql = "WITH Lookup AS (" + lookupSql + ") "
3112 "SELECT "
3113 " " TOSTRING(QUERY_LOOKUP) " AS c0_queryId, "
3114 " Lookup.internalId AS c1_internalId, "
3115 " Lookup.rowNumber AS c2_rowNumber, "
3116 " Lookup.publicId AS c3_string1, "
3117 " NULL::TEXT AS c4_string2, "
3118 " NULL::TEXT AS c5_string3, "
3119 " NULL::INT AS c6_int1, "
3120 " NULL::INT AS c7_int2, "
3121 " NULL::BIGINT AS c8_big_int1, "
3122 " NULL::BIGINT AS c9_big_int2 "
3123 " FROM Lookup ";
3124
3125 // need MainDicomTags from resource ?
3126 if (request.retrieve_main_dicom_tags())
3127 {
3128 sql +=
3129 "UNION SELECT "
3130 " " TOSTRING(QUERY_MAIN_DICOM_TAGS) " AS c0_queryId, "
3131 " Lookup.internalId AS c1_internalId, "
3132 " NULL::BIGINT AS c2_rowNumber, "
3133 " value AS c3_string1, "
3134 " NULL::TEXT AS c4_string2, "
3135 " NULL::TEXT AS c5_string3, "
3136 " tagGroup AS c6_int1, "
3137 " tagElement AS c7_int2, "
3138 " NULL::BIGINT AS c8_big_int1, "
3139 " NULL::BIGINT AS c9_big_int2 "
3140 "FROM MainDicomTags "
3141 "INNER JOIN Lookup ON MainDicomTags.id = Lookup.internalId ";
3142 }
3143
3144 // need resource attachments ?
3145 if (request.retrieve_attachments())
3146 {
3147 sql +=
3148 "UNION SELECT "
3149 " " TOSTRING(QUERY_ATTACHMENTS) " AS c0_queryId, "
3150 " Lookup.internalId AS c1_internalId, "
3151 " NULL::BIGINT AS c2_rowNumber, "
3152 " uuid AS c3_string1, "
3153 " uncompressedHash AS c4_string2, "
3154 " compressedHash AS c5_string3, "
3155 " fileType AS c6_int1, "
3156 " compressionType AS c7_int2, "
3157 " compressedSize AS c8_big_int1, "
3158 " uncompressedSize AS c9_big_int2 "
3159 "FROM AttachedFiles "
3160 "INNER JOIN Lookup ON AttachedFiles.id = Lookup.internalId ";
3161 }
3162
3163
3164 // TODO-FIND: other requests
3165
3166 sql += "ORDER BY c0_queryId, c2_rowNumber"; // this is really important to make sure that the Lookup query is the first one to provide results since we use it to create the responses element !
3167
3168 DatabaseManager::StandaloneStatement statement(manager, sql); // TODO-FIND: cache dynamic statement ? Probably worth it since it can be very complex queries !
3169 formatter.PrepareStatement(statement);
3170 statement.Execute();
3171
3172
3173 std::map<int64_t, Orthanc::DatabasePluginMessages::Find_Response*> responses;
3174
3175 while (!statement.IsDone())
3176 {
3177 int32_t queryId = statement.ReadInteger32(C0_QUERY_ID);
3178 int64_t internalId = statement.ReadInteger64(C1_INTERNAL_ID);
3179
3180 assert(queryId == QUERY_LOOKUP || responses.find(internalId) != responses.end()); // the QUERY_LOOKUP must be read first and must create the response before any other query tries to populate the fields
3181
3182 switch (queryId)
3183 {
3184 case QUERY_LOOKUP:
3185 responses[internalId] = response.add_find(); // the protobuf message is the owner of the response
3186 responses[internalId]->set_public_id(statement.ReadString(C3_STRING_1));
3187 responses[internalId]->set_internal_id(internalId);
3188 break;
3189
3190 case QUERY_MAIN_DICOM_TAGS:
3191 {
3192 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = NULL; // the protobuf response will be the owner
3193
3194 switch (request.level())
3195 {
3196 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT:
3197 content = responses[internalId]->mutable_patient_content();
3198 break;
3199 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
3200 content = responses[internalId]->mutable_study_content();
3201 break;
3202 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
3203 content = responses[internalId]->mutable_series_content();
3204 break;
3205 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE:
3206 content = responses[internalId]->mutable_instance_content();
3207 break;
3208 default:
3209 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
3210 }
3211
3212 Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags();
3213 tag->set_value(statement.ReadString(C3_STRING_1));
3214 tag->set_group(statement.ReadInteger32(C6_INT_1));
3215 tag->set_element(statement.ReadInteger32(C7_INT_2));
3216 }; break;
3217
3218 case QUERY_ATTACHMENTS:
3219 {
3220 Orthanc::DatabasePluginMessages::FileInfo* attachment = responses[internalId]->add_attachments(); // the protobuf response is the owner
3221
3222 attachment->set_uuid(statement.ReadString(C3_STRING_1));
3223 attachment->set_uncompressed_hash(statement.ReadString(C4_STRING_2));
3224 attachment->set_compressed_hash(statement.ReadString(C5_STRING_3));
3225 attachment->set_content_type(statement.ReadInteger32(C6_INT_1));
3226 attachment->set_compression_type(statement.ReadInteger32(C7_INT_2));
3227 attachment->set_compressed_size(statement.ReadInteger64(C8_BIG_INT_1));
3228 attachment->set_uncompressed_size(statement.ReadInteger64(C9_BIG_INT_2));
3229 }; break;
3230
3231 default:
3232 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
3233 }
3234 statement.Next();
3235 }
3236 }
3237 #endif
3238
3059 } 3239 }