Mercurial > hg > orthanc-databases

comparison Framework/Plugins/IndexBackend.cpp @ 558:d186007b0f1e find-refactoring

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
wip: PG find-refactoring
author Alain Mazy <am@orthanc.team>
date Fri, 13 Sep 2024 16:56:18 +0200
parents d8ee2f676a3c
children e18ec71019fa
comparison
equal deleted inserted replaced
557:d8ee2f676a3c 558:d186007b0f1e
3065 } 3065 }
3066 #endif 3066 #endif
3067 3067
3068 3068
3069 #if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) 3069 #if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
3070 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* GetResourceContent(
3071 Orthanc::DatabasePluginMessages::Find_Response* response,
3072 Orthanc::DatabasePluginMessages::ResourceType level)
3073 {
3074 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = NULL; // the protobuf response will be the owner
3075
3076 switch (level)
3077 {
3078 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT:
3079 content = response->mutable_patient_content();
3080 break;
3081 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
3082 content = response->mutable_study_content();
3083 break;
3084 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
3085 content =response->mutable_series_content();
3086 break;
3087 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE:
3088 content = response->mutable_instance_content();
3089 break;
3090 default:
3091 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
3092 }
3093 return content;
3094 }
3095
3096 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* GetChildrenContent(
3097 Orthanc::DatabasePluginMessages::Find_Response* response,
3098 Orthanc::DatabasePluginMessages::ResourceType childrenLevel)
3099 {
3100 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = NULL; // the protobuf response will be the owner
3101
3102 switch (childrenLevel)
3103 {
3104 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
3105 content = response->mutable_children_studies_content();
3106 break;
3107 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
3108 content =response->mutable_children_series_content();
3109 break;
3110 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE:
3111 content = response->mutable_children_instances_content();
3112 break;
3113 default:
3114 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
3115 }
3116 return content;
3117 }
3118
3070 3119
3071 #define C0_QUERY_ID 0 3120 #define C0_QUERY_ID 0
3072 #define C1_INTERNAL_ID 1 3121 #define C1_INTERNAL_ID 1
3073 #define C2_ROW_NUMBER 2 3122 #define C2_ROW_NUMBER 2
3074 #define C3_STRING_1 3 3123 #define C3_STRING_1 3
3080 #define C9_BIG_INT_2 9 3129 #define C9_BIG_INT_2 9
3081 3130
3082 #define QUERY_LOOKUP 1 3131 #define QUERY_LOOKUP 1
3083 #define QUERY_MAIN_DICOM_TAGS 2 3132 #define QUERY_MAIN_DICOM_TAGS 2
3084 #define QUERY_ATTACHMENTS 3 3133 #define QUERY_ATTACHMENTS 3
3134 #define QUERY_METADATA 4
3135 #define QUERY_PARENT_MAIN_DICOM_TAGS 5
3136 #define QUERY_PARENT_IDENTIFIER 6
3137 #define QUERY_CHILDREN_IDENTIFIERS 7
3138 #define QUERY_CHILDREN_MAIN_DICOM_TAGS 8
3139 #define QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS 9
3140
3085 #define STRINGIFY(x) #x 3141 #define STRINGIFY(x) #x
3086 #define TOSTRING(x) STRINGIFY(x) 3142 #define TOSTRING(x) STRINGIFY(x)
3087 3143
3088 void IndexBackend::ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response, 3144 void IndexBackend::ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response,
3089 DatabaseManager& manager, 3145 DatabaseManager& manager,
3138 " NULL::BIGINT AS c8_big_int1, " 3194 " NULL::BIGINT AS c8_big_int1, "
3139 " NULL::BIGINT AS c9_big_int2 " 3195 " NULL::BIGINT AS c9_big_int2 "
3140 "FROM MainDicomTags " 3196 "FROM MainDicomTags "
3141 "INNER JOIN Lookup ON MainDicomTags.id = Lookup.internalId "; 3197 "INNER JOIN Lookup ON MainDicomTags.id = Lookup.internalId ";
3142 } 3198 }
3199
3200 // need resource metadata ?
3201 if (request.retrieve_metadata())
3202 {
3203 sql +=
3204 "UNION SELECT "
3205 " " TOSTRING(QUERY_METADATA) " AS c0_queryId, "
3206 " Lookup.internalId AS c1_internalId, "
3207 " NULL::BIGINT AS c2_rowNumber, "
3208 " value AS c3_string1, "
3209 " NULL::TEXT AS c4_string2, "
3210 " NULL::TEXT AS c5_string3, "
3211 " type AS c6_int1, "
3212 " NULL::INT AS c7_int2, "
3213 " NULL::BIGINT AS c8_big_int1, "
3214 " NULL::BIGINT AS c9_big_int2 "
3215 "FROM Metadata "
3216 "INNER JOIN Lookup ON Metadata.id = Lookup.internalId ";
3217 }
3143 3218
3144 // need resource attachments ? 3219 // need resource attachments ?
3145 if (request.retrieve_attachments()) 3220 if (request.retrieve_attachments())
3146 { 3221 {
3147 sql += 3222 sql +=
3158 " uncompressedSize AS c9_big_int2 " 3233 " uncompressedSize AS c9_big_int2 "
3159 "FROM AttachedFiles " 3234 "FROM AttachedFiles "
3160 "INNER JOIN Lookup ON AttachedFiles.id = Lookup.internalId "; 3235 "INNER JOIN Lookup ON AttachedFiles.id = Lookup.internalId ";
3161 } 3236 }
3162 3237
3238 // need MainDicomTags from parent ?
3239 if (request.level() > Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT)
3240 {
3241 const Orthanc::DatabasePluginMessages::Find_Request_ParentSpecification* parentSpec = NULL;
3242 switch (request.level())
3243 {
3244 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
3245 parentSpec = &(request.parent_patient());
3246 break;
3247 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
3248 parentSpec = &(request.parent_study());
3249 break;
3250 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE:
3251 parentSpec = &(request.parent_series());
3252 break;
3253
3254 default:
3255 break;
3256 }
3257
3258 if (parentSpec->retrieve_main_dicom_tags())
3259 {
3260 sql +=
3261 "UNION SELECT "
3262 " " TOSTRING(QUERY_PARENT_MAIN_DICOM_TAGS) " AS c0_queryId, "
3263 " Lookup.internalId AS c1_internalId, "
3264 " NULL::BIGINT AS c2_rowNumber, "
3265 " value AS c3_string1, "
3266 " NULL::TEXT AS c4_string2, "
3267 " NULL::TEXT AS c5_string3, "
3268 " tagGroup AS c6_int1, "
3269 " tagElement AS c7_int2, "
3270 " NULL::BIGINT AS c8_big_int1, "
3271 " NULL::BIGINT AS c9_big_int2 "
3272 "FROM MainDicomTags "
3273 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
3274 "INNER JOIN Lookup ON MainDicomTags.id = currentLevel.parentId";
3275 }
3276
3277 // need MainDicomTags from grandparent ?
3278 if (request.level() > Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY)
3279 {
3280 const Orthanc::DatabasePluginMessages::Find_Request_ParentSpecification* grandparentSpec = NULL;
3281 switch (request.level())
3282 {
3283 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
3284 grandparentSpec = &(request.parent_patient());
3285 break;
3286 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE:
3287 grandparentSpec = &(request.parent_study());
3288 break;
3289
3290 default:
3291 break;
3292 }
3293
3294 if (grandparentSpec->retrieve_main_dicom_tags())
3295 {
3296 sql += "TODO";
3297 }
3298 }
3299 }
3300
3301 // need MainDicomTags from children ?
3302 if (request.level() <= Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES)
3303 {
3304 const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* childrenSpec = NULL;
3305 switch (request.level())
3306 {
3307 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT:
3308 childrenSpec = &(request.children_studies());
3309 break;
3310 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
3311 childrenSpec = &(request.children_series());
3312 break;
3313 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
3314 childrenSpec = &(request.children_instances());
3315 break;
3316
3317 default:
3318 break;
3319 }
3320
3321 if (childrenSpec->retrieve_main_dicom_tags_size() > 0) // TODO: retrieve only the requested tags ?
3322 {
3323 sql +=
3324 "UNION SELECT "
3325 " " TOSTRING(QUERY_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, "
3326 " Lookup.internalId AS c1_internalId, "
3327 " NULL::BIGINT AS c2_rowNumber, "
3328 " value AS c3_string1, "
3329 " NULL::TEXT AS c4_string2, "
3330 " NULL::TEXT AS c5_string3, "
3331 " tagGroup AS c6_int1, "
3332 " tagElement AS c7_int2, "
3333 " NULL::BIGINT AS c8_big_int1, "
3334 " NULL::BIGINT AS c9_big_int2 "
3335 "FROM MainDicomTags "
3336 " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
3337 " INNER JOIN Lookup ON MainDicomTags.id = childLevel.internalId ";
3338 }
3339
3340 // need children identifiers ?
3341 if (childrenSpec->retrieve_identifiers())
3342 {
3343 sql +=
3344 "UNION SELECT "
3345 " " TOSTRING(QUERY_CHILDREN_IDENTIFIERS) " AS c0_queryId, "
3346 " Lookup.internalId AS c1_internalId, "
3347 " NULL::BIGINT AS c2_rowNumber, "
3348 " childLevel.publicId AS c3_string1, "
3349 " NULL::TEXT AS c4_string2, "
3350 " NULL::TEXT AS c5_string3, "
3351 " NULL::INT AS c6_int1, "
3352 " NULL::INT AS c7_int2, "
3353 " NULL::BIGINT AS c8_big_int1, "
3354 " NULL::BIGINT AS c9_big_int2 "
3355 "FROM Resources AS currentLevel "
3356 " INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
3357 " INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId ";
3358 }
3359
3360 if (request.level() <= Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY)
3361 {
3362 const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* grandchildrenSpec = NULL;
3363 switch (request.level())
3364 {
3365 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT:
3366 grandchildrenSpec = &(request.children_series());
3367 break;
3368 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
3369 grandchildrenSpec = &(request.children_instances());
3370 break;
3371
3372 default:
3373 break;
3374 }
3375
3376 if (grandchildrenSpec->retrieve_main_dicom_tags_size() > 0) // TODO: retrieve only the requested tags ?
3377 {
3378 sql +=
3379 "UNION SELECT "
3380 " " TOSTRING(QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, "
3381 " Lookup.internalId AS c1_internalId, "
3382 " NULL::BIGINT AS c2_rowNumber, "
3383 " value AS c3_string1, "
3384 " NULL::TEXT AS c4_string2, "
3385 " NULL::TEXT AS c5_string3, "
3386 " tagGroup AS c6_int1, "
3387 " tagElement AS c7_int2, "
3388 " NULL::BIGINT AS c8_big_int1, "
3389 " NULL::BIGINT AS c9_big_int2 "
3390 "FROM MainDicomTags "
3391 " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
3392 " INNER JOIN Resources grandChildLevel ON childLevel.parentId = Lookup.internalId "
3393 " INNER JOIN Lookup ON MainDicomTags.id = grandChildLevel.internalId ";
3394 }
3395 }
3396 }
3397
3398 // need parent identifier ?
3399 if (request.retrieve_parent_identifier())
3400 {
3401 sql +=
3402 "UNION SELECT "
3403 " " TOSTRING(QUERY_PARENT_IDENTIFIER) " AS c0_queryId, "
3404 " Lookup.internalId AS c1_internalId, "
3405 " NULL::BIGINT AS c2_rowNumber, "
3406 " parentLevel.publicId AS c3_string1, "
3407 " NULL::TEXT AS c4_string2, "
3408 " NULL::TEXT AS c5_string3, "
3409 " NULL::INT AS c6_int1, "
3410 " NULL::INT AS c7_int2, "
3411 " NULL::BIGINT AS c8_big_int1, "
3412 " NULL::BIGINT AS c9_big_int2 "
3413 "FROM Resources AS currentLevel "
3414 " INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
3415 " INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId ";
3416 }
3417
3418
3419 // Find_Request_ParentSpecification Patient.parent_patient)
3420 //
3421
3422 // if (requestLevel > ResourceType_Patient && request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 1)).IsRetrieveMainDicomTags())
3423 // {
3424 // sql = "SELECT currentLevel.internalId, tagGroup, tagElement, value "
3425 // "FROM MainDicomTags "
3426 // "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
3427 // "INNER JOIN Lookup ON MainDicomTags.id = currentLevel.parentId";
3428
3429 // SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
3430 // while (s.Step())
3431 // {
3432 // FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
3433 // res.AddStringDicomTag(static_cast<ResourceType>(requestLevel - 1),
3434 // static_cast<uint16_t>(s.ColumnInt(1)),
3435 // static_cast<uint16_t>(s.ColumnInt(2)),
3436 // s.ColumnString(3));
3437 // }
3438 // }
3163 3439
3164 // TODO-FIND: other requests 3440 // TODO-FIND: other requests
3165 3441
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 ! 3442 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 3443
3168 DatabaseManager::StandaloneStatement statement(manager, sql); // TODO-FIND: cache dynamic statement ? Probably worth it since it can be very complex queries ! 3444 DatabaseManager::StandaloneStatement statement(manager, sql); // TODO-FIND: cache dynamic statement ? Probably worth it since it can be very complex queries !
3169 formatter.PrepareStatement(statement); 3445 formatter.PrepareStatement(statement);
3170 statement.Execute(); 3446 statement.Execute(formatter.GetDictionary());
3171 3447
3172 3448
3173 std::map<int64_t, Orthanc::DatabasePluginMessages::Find_Response*> responses; 3449 std::map<int64_t, Orthanc::DatabasePluginMessages::Find_Response*> responses;
3174 3450
3175 while (!statement.IsDone()) 3451 while (!statement.IsDone())
3187 responses[internalId]->set_internal_id(internalId); 3463 responses[internalId]->set_internal_id(internalId);
3188 break; 3464 break;
3189 3465
3190 case QUERY_MAIN_DICOM_TAGS: 3466 case QUERY_MAIN_DICOM_TAGS:
3191 { 3467 {
3192 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = NULL; // the protobuf response will be the owner 3468 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], request.level()); // 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(); 3469 Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags();
3470
3213 tag->set_value(statement.ReadString(C3_STRING_1)); 3471 tag->set_value(statement.ReadString(C3_STRING_1));
3214 tag->set_group(statement.ReadInteger32(C6_INT_1)); 3472 tag->set_group(statement.ReadInteger32(C6_INT_1));
3215 tag->set_element(statement.ReadInteger32(C7_INT_2)); 3473 tag->set_element(statement.ReadInteger32(C7_INT_2));
3216 }; break; 3474 }; break;
3475
3476 case QUERY_PARENT_MAIN_DICOM_TAGS:
3477 {
3478 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() - 1)); // the protobuf response will be the owner
3479 Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags();
3480
3481 tag->set_value(statement.ReadString(C3_STRING_1));
3482 tag->set_group(statement.ReadInteger32(C6_INT_1));
3483 tag->set_element(statement.ReadInteger32(C7_INT_2));
3484 }; break;
3485
3486 case QUERY_CHILDREN_IDENTIFIERS:
3487 {
3488 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 1)); // the protobuf response will be the owner
3489 content->add_identifiers(statement.ReadString(C3_STRING_1));
3490 }; break;
3491
3492 case QUERY_CHILDREN_MAIN_DICOM_TAGS:
3493 {
3494 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 1)); // the protobuf response will be the owner
3495 Orthanc::DatabasePluginMessages::Find_Response_MultipleTags* tag = content->add_main_dicom_tags();
3496
3497 tag->set_values(0, statement.ReadString(C3_STRING_1)); // TODO: handle sequences ??
3498 tag->set_group(statement.ReadInteger32(C6_INT_1));
3499 tag->set_element(statement.ReadInteger32(C7_INT_2));
3500 }; break;
3501
3502 case QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS:
3503 {
3504 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 2)); // the protobuf response will be the owner
3505 Orthanc::DatabasePluginMessages::Find_Response_MultipleTags* tag = content->add_main_dicom_tags();
3506
3507 tag->set_values(0, statement.ReadString(C3_STRING_1)); // TODO: handle sequences ??
3508 tag->set_group(statement.ReadInteger32(C6_INT_1));
3509 tag->set_element(statement.ReadInteger32(C7_INT_2));
3510 }; break;
3217 3511
3218 case QUERY_ATTACHMENTS: 3512 case QUERY_ATTACHMENTS:
3219 { 3513 {
3220 Orthanc::DatabasePluginMessages::FileInfo* attachment = responses[internalId]->add_attachments(); // the protobuf response is the owner 3514 Orthanc::DatabasePluginMessages::FileInfo* attachment = responses[internalId]->add_attachments(); // the protobuf response is the owner
3221 3515
3226 attachment->set_compression_type(statement.ReadInteger32(C7_INT_2)); 3520 attachment->set_compression_type(statement.ReadInteger32(C7_INT_2));
3227 attachment->set_compressed_size(statement.ReadInteger64(C8_BIG_INT_1)); 3521 attachment->set_compressed_size(statement.ReadInteger64(C8_BIG_INT_1));
3228 attachment->set_uncompressed_size(statement.ReadInteger64(C9_BIG_INT_2)); 3522 attachment->set_uncompressed_size(statement.ReadInteger64(C9_BIG_INT_2));
3229 }; break; 3523 }; break;
3230 3524
3525 case QUERY_METADATA:
3526 {
3527 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], request.level()); // the protobuf response will be the owner
3528 Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = content->add_metadata();
3529
3530 metadata->set_value(statement.ReadString(C3_STRING_1));
3531 metadata->set_key(statement.ReadInteger32(C6_INT_1));
3532 }; break;
3533
3534 case QUERY_PARENT_IDENTIFIER:
3535 {
3536 responses[internalId]->set_parent_public_id(statement.ReadString(C3_STRING_1));
3537 }; break;
3538
3231 default: 3539 default:
3232 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); 3540 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
3233 } 3541 }
3234 statement.Next(); 3542 statement.Next();
3235 } 3543 }