Mercurial > hg > orthanc-databases

comparison Framework/Plugins/IndexBackend.cpp @ 559:e18ec71019fa find-refactoring

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Find in PG continued: all basic integration tests ok
author Alain Mazy <am@orthanc.team>
date Sat, 14 Sep 2024 11:23:40 +0200
parents d186007b0f1e
children b0ce1ebc9b90
comparison
equal deleted inserted replaced
558:d186007b0f1e 559:e18ec71019fa
3130 3130
3131 #define QUERY_LOOKUP 1 3131 #define QUERY_LOOKUP 1
3132 #define QUERY_MAIN_DICOM_TAGS 2 3132 #define QUERY_MAIN_DICOM_TAGS 2
3133 #define QUERY_ATTACHMENTS 3 3133 #define QUERY_ATTACHMENTS 3
3134 #define QUERY_METADATA 4 3134 #define QUERY_METADATA 4
3135 #define QUERY_PARENT_MAIN_DICOM_TAGS 5 3135 #define QUERY_LABELS 5
3136 #define QUERY_PARENT_IDENTIFIER 6 3136 #define QUERY_PARENT_MAIN_DICOM_TAGS 10
3137 #define QUERY_CHILDREN_IDENTIFIERS 7 3137 #define QUERY_PARENT_IDENTIFIER 11
3138 #define QUERY_CHILDREN_MAIN_DICOM_TAGS 8 3138 #define QUERY_GRAND_PARENT_MAIN_DICOM_TAGS 15
3139 #define QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS 9 3139 #define QUERY_CHILDREN_IDENTIFIERS 20
3140 #define QUERY_CHILDREN_MAIN_DICOM_TAGS 21
3141 #define QUERY_CHILDREN_METADATA 22
3142 #define QUERY_GRAND_CHILDREN_IDENTIFIERS 30
3143 #define QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS 31
3144 #define QUERY_GRAND_CHILDREN_METADATA 32
3145 #define QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS 40
3146 #define QUERY_ONE_INSTANCE_IDENTIFIER 50
3147 #define QUERY_ONE_INSTANCE_METADATA 51
3148 #define QUERY_ONE_INSTANCE_ATTACHMENTS 52
3140 3149
3141 #define STRINGIFY(x) #x 3150 #define STRINGIFY(x) #x
3142 #define TOSTRING(x) STRINGIFY(x) 3151 #define TOSTRING(x) STRINGIFY(x)
3143 3152
3144 void IndexBackend::ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response, 3153 void IndexBackend::ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response,
3179 " FROM Lookup "; 3188 " FROM Lookup ";
3180 3189
3181 // need MainDicomTags from resource ? 3190 // need MainDicomTags from resource ?
3182 if (request.retrieve_main_dicom_tags()) 3191 if (request.retrieve_main_dicom_tags())
3183 { 3192 {
3184 sql += 3193 sql += "UNION SELECT "
3185 "UNION SELECT " 3194 " " TOSTRING(QUERY_MAIN_DICOM_TAGS) " AS c0_queryId, "
3186 " " TOSTRING(QUERY_MAIN_DICOM_TAGS) " AS c0_queryId, " 3195 " Lookup.internalId AS c1_internalId, "
3187 " Lookup.internalId AS c1_internalId, " 3196 " NULL::BIGINT AS c2_rowNumber, "
3188 " NULL::BIGINT AS c2_rowNumber, " 3197 " value AS c3_string1, "
3189 " value AS c3_string1, " 3198 " NULL::TEXT AS c4_string2, "
3190 " NULL::TEXT AS c4_string2, " 3199 " NULL::TEXT AS c5_string3, "
3191 " NULL::TEXT AS c5_string3, " 3200 " tagGroup AS c6_int1, "
3192 " tagGroup AS c6_int1, " 3201 " tagElement AS c7_int2, "
3193 " tagElement AS c7_int2, " 3202 " NULL::BIGINT AS c8_big_int1, "
3194 " NULL::BIGINT AS c8_big_int1, " 3203 " NULL::BIGINT AS c9_big_int2 "
3195 " NULL::BIGINT AS c9_big_int2 " 3204 "FROM MainDicomTags "
3196 "FROM MainDicomTags " 3205 "INNER JOIN Lookup ON MainDicomTags.id = Lookup.internalId ";
3197 "INNER JOIN Lookup ON MainDicomTags.id = Lookup.internalId ";
3198 } 3206 }
3199 3207
3200 // need resource metadata ? 3208 // need resource metadata ?
3201 if (request.retrieve_metadata()) 3209 if (request.retrieve_metadata())
3202 { 3210 {
3203 sql += 3211 sql += "UNION SELECT "
3204 "UNION SELECT " 3212 " " TOSTRING(QUERY_METADATA) " AS c0_queryId, "
3205 " " TOSTRING(QUERY_METADATA) " AS c0_queryId, " 3213 " Lookup.internalId AS c1_internalId, "
3206 " Lookup.internalId AS c1_internalId, " 3214 " NULL::BIGINT AS c2_rowNumber, "
3207 " NULL::BIGINT AS c2_rowNumber, " 3215 " value AS c3_string1, "
3208 " value AS c3_string1, " 3216 " NULL::TEXT AS c4_string2, "
3209 " NULL::TEXT AS c4_string2, " 3217 " NULL::TEXT AS c5_string3, "
3210 " NULL::TEXT AS c5_string3, " 3218 " type AS c6_int1, "
3211 " type AS c6_int1, " 3219 " NULL::INT AS c7_int2, "
3212 " NULL::INT AS c7_int2, " 3220 " NULL::BIGINT AS c8_big_int1, "
3213 " NULL::BIGINT AS c8_big_int1, " 3221 " NULL::BIGINT AS c9_big_int2 "
3214 " NULL::BIGINT AS c9_big_int2 " 3222 "FROM Metadata "
3215 "FROM Metadata " 3223 "INNER JOIN Lookup ON Metadata.id = Lookup.internalId ";
3216 "INNER JOIN Lookup ON Metadata.id = Lookup.internalId ";
3217 } 3224 }
3218 3225
3219 // need resource attachments ? 3226 // need resource attachments ?
3220 if (request.retrieve_attachments()) 3227 if (request.retrieve_attachments())
3221 { 3228 {
3222 sql += 3229 sql += "UNION SELECT "
3223 "UNION SELECT " 3230 " " TOSTRING(QUERY_ATTACHMENTS) " AS c0_queryId, "
3224 " " TOSTRING(QUERY_ATTACHMENTS) " AS c0_queryId, " 3231 " Lookup.internalId AS c1_internalId, "
3225 " Lookup.internalId AS c1_internalId, " 3232 " NULL::BIGINT AS c2_rowNumber, "
3226 " NULL::BIGINT AS c2_rowNumber, " 3233 " uuid AS c3_string1, "
3227 " uuid AS c3_string1, " 3234 " uncompressedHash AS c4_string2, "
3228 " uncompressedHash AS c4_string2, " 3235 " compressedHash AS c5_string3, "
3229 " compressedHash AS c5_string3, " 3236 " fileType AS c6_int1, "
3230 " fileType AS c6_int1, " 3237 " compressionType AS c7_int2, "
3231 " compressionType AS c7_int2, " 3238 " compressedSize AS c8_big_int1, "
3232 " compressedSize AS c8_big_int1, " 3239 " uncompressedSize AS c9_big_int2 "
3233 " uncompressedSize AS c9_big_int2 " 3240 "FROM AttachedFiles "
3234 "FROM AttachedFiles " 3241 "INNER JOIN Lookup ON AttachedFiles.id = Lookup.internalId ";
3235 "INNER JOIN Lookup ON AttachedFiles.id = Lookup.internalId "; 3242 }
3243
3244 // need resource labels ?
3245 if (request.retrieve_labels())
3246 {
3247 sql += "UNION SELECT "
3248 " " TOSTRING(QUERY_LABELS) " AS c0_queryId, "
3249 " Lookup.internalId AS c1_internalId, "
3250 " NULL::BIGINT AS c2_rowNumber, "
3251 " label AS c3_string1, "
3252 " NULL::TEXT AS c4_string2, "
3253 " NULL::TEXT AS c5_string3, "
3254 " NULL::INT AS c6_int1, "
3255 " NULL::INT AS c7_int2, "
3256 " NULL::BIGINT AS c8_big_int1, "
3257 " NULL::BIGINT AS c9_big_int2 "
3258 "FROM Labels "
3259 "INNER JOIN Lookup ON Labels.id = Lookup.internalId ";
3236 } 3260 }
3237 3261
3238 // need MainDicomTags from parent ? 3262 // need MainDicomTags from parent ?
3239 if (request.level() > Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT) 3263 if (request.level() > Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT)
3240 { 3264 {
3255 break; 3279 break;
3256 } 3280 }
3257 3281
3258 if (parentSpec->retrieve_main_dicom_tags()) 3282 if (parentSpec->retrieve_main_dicom_tags())
3259 { 3283 {
3260 sql += 3284 sql += "UNION SELECT "
3261 "UNION SELECT " 3285 " " TOSTRING(QUERY_PARENT_MAIN_DICOM_TAGS) " AS c0_queryId, "
3262 " " TOSTRING(QUERY_PARENT_MAIN_DICOM_TAGS) " AS c0_queryId, " 3286 " Lookup.internalId AS c1_internalId, "
3263 " Lookup.internalId AS c1_internalId, " 3287 " NULL::BIGINT AS c2_rowNumber, "
3264 " NULL::BIGINT AS c2_rowNumber, " 3288 " value AS c3_string1, "
3265 " value AS c3_string1, " 3289 " NULL::TEXT AS c4_string2, "
3266 " NULL::TEXT AS c4_string2, " 3290 " NULL::TEXT AS c5_string3, "
3267 " NULL::TEXT AS c5_string3, " 3291 " tagGroup AS c6_int1, "
3268 " tagGroup AS c6_int1, " 3292 " tagElement AS c7_int2, "
3269 " tagElement AS c7_int2, " 3293 " NULL::BIGINT AS c8_big_int1, "
3270 " NULL::BIGINT AS c8_big_int1, " 3294 " NULL::BIGINT AS c9_big_int2 "
3271 " NULL::BIGINT AS c9_big_int2 " 3295 "FROM Lookup "
3272 "FROM MainDicomTags " 3296 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
3273 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " 3297 "INNER JOIN MainDicomTags ON MainDicomTags.id = currentLevel.parentId ";
3274 "INNER JOIN Lookup ON MainDicomTags.id = currentLevel.parentId";
3275 } 3298 }
3276 3299
3277 // need MainDicomTags from grandparent ? 3300 // need MainDicomTags from grandparent ?
3278 if (request.level() > Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY) 3301 if (request.level() > Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY)
3279 { 3302 {
3291 break; 3314 break;
3292 } 3315 }
3293 3316
3294 if (grandparentSpec->retrieve_main_dicom_tags()) 3317 if (grandparentSpec->retrieve_main_dicom_tags())
3295 { 3318 {
3296 sql += "TODO"; 3319 sql += "UNION SELECT "
3320 " " TOSTRING(QUERY_GRAND_PARENT_MAIN_DICOM_TAGS) " AS c0_queryId, "
3321 " Lookup.internalId AS c1_internalId, "
3322 " NULL::BIGINT AS c2_rowNumber, "
3323 " value AS c3_string1, "
3324 " NULL::TEXT AS c4_string2, "
3325 " NULL::TEXT AS c5_string3, "
3326 " tagGroup AS c6_int1, "
3327 " tagElement AS c7_int2, "
3328 " NULL::BIGINT AS c8_big_int1, "
3329 " NULL::BIGINT AS c9_big_int2 "
3330 "FROM Lookup "
3331 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
3332 "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId "
3333 "INNER JOIN MainDicomTags ON MainDicomTags.id = parentLevel.parentId ";
3297 } 3334 }
3298 } 3335 }
3299 } 3336 }
3300 3337
3301 // need MainDicomTags from children ? 3338 // need MainDicomTags from children ?
3318 break; 3355 break;
3319 } 3356 }
3320 3357
3321 if (childrenSpec->retrieve_main_dicom_tags_size() > 0) // TODO: retrieve only the requested tags ? 3358 if (childrenSpec->retrieve_main_dicom_tags_size() > 0) // TODO: retrieve only the requested tags ?
3322 { 3359 {
3323 sql += 3360 sql += "UNION SELECT "
3324 "UNION SELECT " 3361 " " TOSTRING(QUERY_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, "
3325 " " TOSTRING(QUERY_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, " 3362 " Lookup.internalId AS c1_internalId, "
3326 " Lookup.internalId AS c1_internalId, " 3363 " NULL::BIGINT AS c2_rowNumber, "
3327 " NULL::BIGINT AS c2_rowNumber, " 3364 " value AS c3_string1, "
3328 " value AS c3_string1, " 3365 " NULL::TEXT AS c4_string2, "
3329 " NULL::TEXT AS c4_string2, " 3366 " NULL::TEXT AS c5_string3, "
3330 " NULL::TEXT AS c5_string3, " 3367 " tagGroup AS c6_int1, "
3331 " tagGroup AS c6_int1, " 3368 " tagElement AS c7_int2, "
3332 " tagElement AS c7_int2, " 3369 " NULL::BIGINT AS c8_big_int1, "
3333 " NULL::BIGINT AS c8_big_int1, " 3370 " NULL::BIGINT AS c9_big_int2 "
3334 " NULL::BIGINT AS c9_big_int2 " 3371 "FROM Lookup "
3335 "FROM MainDicomTags " 3372 " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
3336 " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId " 3373 " INNER JOIN MainDicomTags ON MainDicomTags.id = childLevel.internalId ";
3337 " INNER JOIN Lookup ON MainDicomTags.id = childLevel.internalId ";
3338 } 3374 }
3339 3375
3340 // need children identifiers ? 3376 // need children identifiers ?
3341 if (childrenSpec->retrieve_identifiers()) 3377 if (childrenSpec->retrieve_identifiers())
3342 { 3378 {
3343 sql += 3379 sql += "UNION SELECT "
3344 "UNION SELECT " 3380 " " TOSTRING(QUERY_CHILDREN_IDENTIFIERS) " AS c0_queryId, "
3345 " " TOSTRING(QUERY_CHILDREN_IDENTIFIERS) " AS c0_queryId, " 3381 " Lookup.internalId AS c1_internalId, "
3346 " Lookup.internalId AS c1_internalId, " 3382 " NULL::BIGINT AS c2_rowNumber, "
3347 " NULL::BIGINT AS c2_rowNumber, " 3383 " childLevel.publicId AS c3_string1, "
3348 " childLevel.publicId AS c3_string1, " 3384 " NULL::TEXT AS c4_string2, "
3349 " NULL::TEXT AS c4_string2, " 3385 " NULL::TEXT AS c5_string3, "
3350 " NULL::TEXT AS c5_string3, " 3386 " NULL::INT AS c6_int1, "
3351 " NULL::INT AS c6_int1, " 3387 " NULL::INT AS c7_int2, "
3352 " NULL::INT AS c7_int2, " 3388 " NULL::BIGINT AS c8_big_int1, "
3353 " NULL::BIGINT AS c8_big_int1, " 3389 " NULL::BIGINT AS c9_big_int2 "
3354 " NULL::BIGINT AS c9_big_int2 " 3390 "FROM Resources AS currentLevel "
3355 "FROM Resources AS currentLevel " 3391 " INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
3356 " INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " 3392 " INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId ";
3357 " INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId "; 3393 }
3394
3395 if (childrenSpec->retrieve_metadata_size() > 0) // TODO-FIND: retrieve only the requested metadata ?
3396 {
3397 sql += "UNION SELECT "
3398 " " TOSTRING(QUERY_CHILDREN_METADATA) " AS c0_queryId, "
3399 " Lookup.internalId AS c1_internalId, "
3400 " NULL::BIGINT AS c2_rowNumber, "
3401 " value AS c3_string1, "
3402 " NULL::TEXT AS c4_string2, "
3403 " NULL::TEXT AS c5_string3, "
3404 " type AS c6_int1, "
3405 " NULL::INT AS c7_int2, "
3406 " NULL::BIGINT AS c8_big_int1, "
3407 " NULL::BIGINT AS c9_big_int2 "
3408 "FROM Lookup "
3409 " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
3410 " INNER JOIN Metadata ON Metadata.id = childLevel.internalId ";
3358 } 3411 }
3359 3412
3360 if (request.level() <= Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY) 3413 if (request.level() <= Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY)
3361 { 3414 {
3362 const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* grandchildrenSpec = NULL; 3415 const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* grandchildrenSpec = NULL;
3371 3424
3372 default: 3425 default:
3373 break; 3426 break;
3374 } 3427 }
3375 3428
3376 if (grandchildrenSpec->retrieve_main_dicom_tags_size() > 0) // TODO: retrieve only the requested tags ? 3429 // need grand children identifiers ?
3430 if (grandchildrenSpec->retrieve_identifiers())
3377 { 3431 {
3378 sql += 3432 sql += "UNION SELECT "
3379 "UNION SELECT " 3433 " " TOSTRING(QUERY_GRAND_CHILDREN_IDENTIFIERS) " AS c0_queryId, "
3380 " " TOSTRING(QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, " 3434 " Lookup.internalId AS c1_internalId, "
3381 " Lookup.internalId AS c1_internalId, " 3435 " NULL::BIGINT AS c2_rowNumber, "
3382 " NULL::BIGINT AS c2_rowNumber, " 3436 " grandChildLevel.publicId AS c3_string1, "
3383 " value AS c3_string1, " 3437 " NULL::TEXT AS c4_string2, "
3384 " NULL::TEXT AS c4_string2, " 3438 " NULL::TEXT AS c5_string3, "
3385 " NULL::TEXT AS c5_string3, " 3439 " NULL::INT AS c6_int1, "
3386 " tagGroup AS c6_int1, " 3440 " NULL::INT AS c7_int2, "
3387 " tagElement AS c7_int2, " 3441 " NULL::BIGINT AS c8_big_int1, "
3388 " NULL::BIGINT AS c8_big_int1, " 3442 " NULL::BIGINT AS c9_big_int2 "
3389 " NULL::BIGINT AS c9_big_int2 " 3443 "FROM Resources AS currentLevel "
3390 "FROM MainDicomTags " 3444 "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
3391 " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId " 3445 "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId "
3392 " INNER JOIN Resources grandChildLevel ON childLevel.parentId = Lookup.internalId " 3446 "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId ";
3393 " INNER JOIN Lookup ON MainDicomTags.id = grandChildLevel.internalId "; 3447 }
3448
3449 if (grandchildrenSpec->retrieve_main_dicom_tags_size() > 0) // TODO-FIND: retrieve only the requested tags ?
3450 {
3451 sql += "UNION SELECT "
3452 " " TOSTRING(QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, "
3453 " Lookup.internalId AS c1_internalId, "
3454 " NULL::BIGINT AS c2_rowNumber, "
3455 " value AS c3_string1, "
3456 " NULL::TEXT AS c4_string2, "
3457 " NULL::TEXT AS c5_string3, "
3458 " tagGroup AS c6_int1, "
3459 " tagElement AS c7_int2, "
3460 " NULL::BIGINT AS c8_big_int1, "
3461 " NULL::BIGINT AS c9_big_int2 "
3462 "FROM Lookup "
3463 " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
3464 " INNER JOIN Resources grandChildLevel ON childLevel.parentId = Lookup.internalId "
3465 " INNER JOIN MainDicomTags ON MainDicomTags.id = grandChildLevel.internalId ";
3466 }
3467
3468 if (grandchildrenSpec->retrieve_metadata_size() > 0) // TODO-FIND: retrieve only the requested metadata ?
3469 {
3470 sql += "UNION SELECT "
3471 " " TOSTRING(QUERY_GRAND_CHILDREN_METADATA) " AS c0_queryId, "
3472 " Lookup.internalId AS c1_internalId, "
3473 " NULL::BIGINT AS c2_rowNumber, "
3474 " value AS c3_string1, "
3475 " NULL::TEXT AS c4_string2, "
3476 " NULL::TEXT AS c5_string3, "
3477 " type AS c6_int1, "
3478 " NULL::INT AS c7_int2, "
3479 " NULL::BIGINT AS c8_big_int1, "
3480 " NULL::BIGINT AS c9_big_int2 "
3481 "FROM Lookup "
3482 " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
3483 " INNER JOIN Resources grandChildLevel ON childLevel.parentId = Lookup.internalId "
3484 " INNER JOIN Metadata ON Metadata.id = grandChildLevel.internalId ";
3485 }
3486
3487 if (request.level() == Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT)
3488 {
3489 const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* grandgrandchildrenSpec = &(request.children_instances());
3490
3491 // need grand children identifiers ?
3492 if (grandgrandchildrenSpec->retrieve_identifiers())
3493 {
3494 sql += "UNION SELECT "
3495 " " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS) " AS c0_queryId, "
3496 " Lookup.internalId AS c1_internalId, "
3497 " NULL::BIGINT AS c2_rowNumber, "
3498 " grandGrandChildLevel.publicId AS c3_string1, "
3499 " NULL::TEXT AS c4_string2, "
3500 " NULL::TEXT AS c5_string3, "
3501 " NULL::INT AS c6_int1, "
3502 " NULL::INT AS c7_int2, "
3503 " NULL::BIGINT AS c8_big_int1, "
3504 " NULL::BIGINT AS c9_big_int2 "
3505 "FROM Resources AS currentLevel "
3506 "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
3507 "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId "
3508 "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId "
3509 "INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId ";
3510 }
3394 } 3511 }
3395 } 3512 }
3396 } 3513 }
3397 3514
3398 // need parent identifier ? 3515 // need parent identifier ?
3399 if (request.retrieve_parent_identifier()) 3516 if (request.retrieve_parent_identifier())
3400 { 3517 {
3401 sql += 3518 sql += "UNION SELECT "
3402 "UNION SELECT " 3519 " " TOSTRING(QUERY_PARENT_IDENTIFIER) " AS c0_queryId, "
3403 " " TOSTRING(QUERY_PARENT_IDENTIFIER) " AS c0_queryId, " 3520 " Lookup.internalId AS c1_internalId, "
3404 " Lookup.internalId AS c1_internalId, " 3521 " NULL::BIGINT AS c2_rowNumber, "
3405 " NULL::BIGINT AS c2_rowNumber, " 3522 " parentLevel.publicId AS c3_string1, "
3406 " parentLevel.publicId AS c3_string1, " 3523 " NULL::TEXT AS c4_string2, "
3407 " NULL::TEXT AS c4_string2, " 3524 " NULL::TEXT AS c5_string3, "
3408 " NULL::TEXT AS c5_string3, " 3525 " NULL::INT AS c6_int1, "
3409 " NULL::INT AS c6_int1, " 3526 " NULL::INT AS c7_int2, "
3410 " NULL::INT AS c7_int2, " 3527 " NULL::BIGINT AS c8_big_int1, "
3411 " NULL::BIGINT AS c8_big_int1, " 3528 " NULL::BIGINT AS c9_big_int2 "
3412 " NULL::BIGINT AS c9_big_int2 " 3529 "FROM Resources AS currentLevel "
3413 "FROM Resources AS currentLevel " 3530 " INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
3414 " INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " 3531 " INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId ";
3415 " INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId "; 3532 }
3416 } 3533
3417 3534 // need one instance info ?
3418 3535 if (request.level() != Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE &&
3419 // Find_Request_ParentSpecification Patient.parent_patient) 3536 request.retrieve_one_instance_metadata_and_attachments())
3420 // 3537 {
3421 3538 // Here, we create a nested CTE 'OneInstance' with one instance ID to join with metadata and main
3422 // if (requestLevel > ResourceType_Patient && request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 1)).IsRetrieveMainDicomTags()) 3539 sql += "UNION"
3423 // { 3540 " (WITH OneInstance AS";
3424 // sql = "SELECT currentLevel.internalId, tagGroup, tagElement, value " 3541
3425 // "FROM MainDicomTags " 3542 switch (request.level())
3426 // "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " 3543 {
3427 // "INNER JOIN Lookup ON MainDicomTags.id = currentLevel.parentId"; 3544 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
3428 3545 {
3429 // SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); 3546 sql+= " (SELECT DISTINCT ON (Lookup.internalId) Lookup.internalId AS parentInternalId, childLevel.publicId AS instancePublicId, childLevel.internalId AS instanceInternalId"
3430 // while (s.Step()) 3547 " FROM Resources AS childLevel "
3431 // { 3548 " INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId) ";
3432 // FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 3549 }; break;
3433 // res.AddStringDicomTag(static_cast<ResourceType>(requestLevel - 1), 3550 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
3434 // static_cast<uint16_t>(s.ColumnInt(1)), 3551 {
3435 // static_cast<uint16_t>(s.ColumnInt(2)), 3552 sql+= " (SELECT DISTINCT ON (Lookup.internalId) Lookup.internalId AS parentInternalId, grandChildLevel.publicId AS instancePublicId, grandChildLevel.internalId AS instanceInternalId"
3436 // s.ColumnString(3)); 3553 " FROM Resources AS grandChildLevel "
3437 // } 3554 " INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId "
3438 // } 3555 " INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId) ";
3439 3556 }; break;
3440 // TODO-FIND: other requests 3557 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT:
3441 3558 {
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 ! 3559 sql+= " (SELECT DISTINCT ON (Lookup.internalId) Lookup.internalId AS parentInternalId, grandGrandChildLevel.publicId AS instancePublicId, grandGrandChildLevel.internalId AS instanceInternalId"
3560 " FROM Resources AS grandGrandChildLevel "
3561 " INNER JOIN Resources grandChildLevel ON grandGrandChildLevel.parentId = grandChildLevel.internalId "
3562 " INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId "
3563 " INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId) ";
3564 }; break;
3565 default:
3566 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
3567 }
3568
3569 sql += " SELECT"
3570 " " TOSTRING(QUERY_ONE_INSTANCE_IDENTIFIER) " AS c0_queryId, "
3571 " parentInternalId AS c1_internalId, "
3572 " NULL::BIGINT AS c2_rowNumber, "
3573 " instancePublicId AS c3_string1, "
3574 " NULL::TEXT AS c4_string2, "
3575 " NULL::TEXT AS c5_string3, "
3576 " NULL::INT AS c6_int1, "
3577 " NULL::INT AS c7_int2, "
3578 " instanceInternalId AS c8_big_int1, "
3579 " NULL::BIGINT AS c9_big_int2 "
3580 " FROM OneInstance ";
3581
3582 sql += " UNION SELECT"
3583 " " TOSTRING(QUERY_ONE_INSTANCE_METADATA) " AS c0_queryId, "
3584 " parentInternalId AS c1_internalId, "
3585 " NULL::BIGINT AS c2_rowNumber, "
3586 " Metadata.value AS c3_string1, "
3587 " NULL::TEXT AS c4_string2, "
3588 " NULL::TEXT AS c5_string3, "
3589 " Metadata.type AS c6_int1, "
3590 " NULL::INT AS c7_int2, "
3591 " NULL::BIGINT AS c8_big_int1, "
3592 " NULL::BIGINT AS c9_big_int2 "
3593 " FROM Metadata "
3594 " INNER JOIN OneInstance ON Metadata.id = OneInstance.instanceInternalId";
3595
3596 sql += " UNION SELECT"
3597 " " TOSTRING(QUERY_ONE_INSTANCE_ATTACHMENTS) " AS c0_queryId, "
3598 " parentInternalId AS c1_internalId, "
3599 " NULL::BIGINT AS c2_rowNumber, "
3600 " uuid AS c3_string1, "
3601 " uncompressedHash AS c4_string2, "
3602 " compressedHash AS c5_string3, "
3603 " fileType AS c6_int1, "
3604 " compressionType AS c7_int2, "
3605 " compressedSize AS c8_big_int1, "
3606 " uncompressedSize AS c9_big_int2 "
3607 " FROM AttachedFiles "
3608 " INNER JOIN OneInstance ON AttachedFiles.id = OneInstance.instanceInternalId";
3609
3610 sql += " ) ";
3611
3612 }
3613
3614 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 !
3443 3615
3444 DatabaseManager::StandaloneStatement statement(manager, sql); // TODO-FIND: cache dynamic statement ? Probably worth it since it can be very complex queries ! 3616 DatabaseManager::StandaloneStatement statement(manager, sql); // TODO-FIND: cache dynamic statement ? Probably worth it since it can be very complex queries !
3445 formatter.PrepareStatement(statement); 3617 formatter.PrepareStatement(statement);
3446 statement.Execute(formatter.GetDictionary()); 3618 statement.Execute(formatter.GetDictionary());
3447 3619
3456 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 3628 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
3457 3629
3458 switch (queryId) 3630 switch (queryId)
3459 { 3631 {
3460 case QUERY_LOOKUP: 3632 case QUERY_LOOKUP:
3461 responses[internalId] = response.add_find(); // the protobuf message is the owner of the response 3633 responses[internalId] = response.add_find();
3462 responses[internalId]->set_public_id(statement.ReadString(C3_STRING_1)); 3634 responses[internalId]->set_public_id(statement.ReadString(C3_STRING_1));
3463 responses[internalId]->set_internal_id(internalId); 3635 responses[internalId]->set_internal_id(internalId);
3464 break; 3636 break;
3465 3637
3638 case QUERY_LABELS:
3639 responses[internalId]->add_labels(statement.ReadString(C3_STRING_1));
3640 break;
3641
3466 case QUERY_MAIN_DICOM_TAGS: 3642 case QUERY_MAIN_DICOM_TAGS:
3467 { 3643 {
3468 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], request.level()); // the protobuf response will be the owner 3644 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], request.level());
3469 Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags(); 3645 Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags();
3470 3646
3471 tag->set_value(statement.ReadString(C3_STRING_1)); 3647 tag->set_value(statement.ReadString(C3_STRING_1));
3472 tag->set_group(statement.ReadInteger32(C6_INT_1)); 3648 tag->set_group(statement.ReadInteger32(C6_INT_1));
3473 tag->set_element(statement.ReadInteger32(C7_INT_2)); 3649 tag->set_element(statement.ReadInteger32(C7_INT_2));
3474 }; break; 3650 }; break;
3475 3651
3476 case QUERY_PARENT_MAIN_DICOM_TAGS: 3652 case QUERY_PARENT_MAIN_DICOM_TAGS:
3477 { 3653 {
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 3654 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() - 1));
3479 Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags(); 3655 Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags();
3480 3656
3481 tag->set_value(statement.ReadString(C3_STRING_1)); 3657 tag->set_value(statement.ReadString(C3_STRING_1));
3482 tag->set_group(statement.ReadInteger32(C6_INT_1)); 3658 tag->set_group(statement.ReadInteger32(C6_INT_1));
3483 tag->set_element(statement.ReadInteger32(C7_INT_2)); 3659 tag->set_element(statement.ReadInteger32(C7_INT_2));
3484 }; break; 3660 }; break;
3485 3661
3486 case QUERY_CHILDREN_IDENTIFIERS: 3662 case QUERY_GRAND_PARENT_MAIN_DICOM_TAGS:
3487 { 3663 {
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 3664 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() - 2));
3489 content->add_identifiers(statement.ReadString(C3_STRING_1)); 3665 Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags();
3490 }; break; 3666
3491 3667 tag->set_value(statement.ReadString(C3_STRING_1));
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)); 3668 tag->set_group(statement.ReadInteger32(C6_INT_1));
3499 tag->set_element(statement.ReadInteger32(C7_INT_2)); 3669 tag->set_element(statement.ReadInteger32(C7_INT_2));
3500 }; break; 3670 }; break;
3501 3671
3502 case QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS: 3672 case QUERY_CHILDREN_IDENTIFIERS:
3503 { 3673 {
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 3674 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 1));
3675 content->add_identifiers(statement.ReadString(C3_STRING_1));
3676 }; break;
3677
3678 case QUERY_CHILDREN_MAIN_DICOM_TAGS:
3679 {
3680 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 1));
3505 Orthanc::DatabasePluginMessages::Find_Response_MultipleTags* tag = content->add_main_dicom_tags(); 3681 Orthanc::DatabasePluginMessages::Find_Response_MultipleTags* tag = content->add_main_dicom_tags();
3506 3682 tag->add_values(statement.ReadString(C3_STRING_1)); // TODO: handle sequences ??
3507 tag->set_values(0, statement.ReadString(C3_STRING_1)); // TODO: handle sequences ??
3508 tag->set_group(statement.ReadInteger32(C6_INT_1)); 3683 tag->set_group(statement.ReadInteger32(C6_INT_1));
3509 tag->set_element(statement.ReadInteger32(C7_INT_2)); 3684 tag->set_element(statement.ReadInteger32(C7_INT_2));
3510 }; break; 3685 }; break;
3511 3686
3687 case QUERY_CHILDREN_METADATA:
3688 {
3689 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 1));
3690 Orthanc::DatabasePluginMessages::Find_Response_MultipleMetadata* metadata = content->add_metadata();
3691
3692 metadata->add_values(statement.ReadString(C3_STRING_1));
3693 metadata->set_key(statement.ReadInteger32(C6_INT_1));
3694 }; break;
3695
3696 case QUERY_GRAND_CHILDREN_IDENTIFIERS:
3697 {
3698 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 2));
3699 content->add_identifiers(statement.ReadString(C3_STRING_1));
3700 }; break;
3701
3702 case QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS:
3703 {
3704 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 2));
3705 Orthanc::DatabasePluginMessages::Find_Response_MultipleTags* tag = content->add_main_dicom_tags();
3706
3707 tag->add_values(statement.ReadString(C3_STRING_1)); // TODO: handle sequences ??
3708 tag->set_group(statement.ReadInteger32(C6_INT_1));
3709 tag->set_element(statement.ReadInteger32(C7_INT_2));
3710 }; break;
3711
3712 case QUERY_GRAND_CHILDREN_METADATA:
3713 {
3714 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 2));
3715 Orthanc::DatabasePluginMessages::Find_Response_MultipleMetadata* metadata = content->add_metadata();
3716
3717 metadata->add_values(statement.ReadString(C3_STRING_1));
3718 metadata->set_key(statement.ReadInteger32(C6_INT_1));
3719 }; break;
3720
3721 case QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS:
3722 {
3723 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 3));
3724 content->add_identifiers(statement.ReadString(C3_STRING_1));
3725 }; break;
3726
3512 case QUERY_ATTACHMENTS: 3727 case QUERY_ATTACHMENTS:
3513 { 3728 {
3514 Orthanc::DatabasePluginMessages::FileInfo* attachment = responses[internalId]->add_attachments(); // the protobuf response is the owner 3729 Orthanc::DatabasePluginMessages::FileInfo* attachment = responses[internalId]->add_attachments();
3515 3730
3516 attachment->set_uuid(statement.ReadString(C3_STRING_1)); 3731 attachment->set_uuid(statement.ReadString(C3_STRING_1));
3517 attachment->set_uncompressed_hash(statement.ReadString(C4_STRING_2)); 3732 attachment->set_uncompressed_hash(statement.ReadString(C4_STRING_2));
3518 attachment->set_compressed_hash(statement.ReadString(C5_STRING_3)); 3733 attachment->set_compressed_hash(statement.ReadString(C5_STRING_3));
3519 attachment->set_content_type(statement.ReadInteger32(C6_INT_1)); 3734 attachment->set_content_type(statement.ReadInteger32(C6_INT_1));
3522 attachment->set_uncompressed_size(statement.ReadInteger64(C9_BIG_INT_2)); 3737 attachment->set_uncompressed_size(statement.ReadInteger64(C9_BIG_INT_2));
3523 }; break; 3738 }; break;
3524 3739
3525 case QUERY_METADATA: 3740 case QUERY_METADATA:
3526 { 3741 {
3527 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], request.level()); // the protobuf response will be the owner 3742 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], request.level());
3528 Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = content->add_metadata(); 3743 Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = content->add_metadata();
3529 3744
3530 metadata->set_value(statement.ReadString(C3_STRING_1)); 3745 metadata->set_value(statement.ReadString(C3_STRING_1));
3531 metadata->set_key(statement.ReadInteger32(C6_INT_1)); 3746 metadata->set_key(statement.ReadInteger32(C6_INT_1));
3532 }; break; 3747 }; break;
3534 case QUERY_PARENT_IDENTIFIER: 3749 case QUERY_PARENT_IDENTIFIER:
3535 { 3750 {
3536 responses[internalId]->set_parent_public_id(statement.ReadString(C3_STRING_1)); 3751 responses[internalId]->set_parent_public_id(statement.ReadString(C3_STRING_1));
3537 }; break; 3752 }; break;
3538 3753
3754 case QUERY_ONE_INSTANCE_IDENTIFIER:
3755 {
3756 responses[internalId]->set_one_instance_public_id(statement.ReadString(C3_STRING_1));
3757 }; break;
3758 case QUERY_ONE_INSTANCE_METADATA:
3759 {
3760 Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = responses[internalId]->add_one_instance_metadata();
3761
3762 metadata->set_value(statement.ReadString(C3_STRING_1));
3763 metadata->set_key(statement.ReadInteger32(C6_INT_1));
3764 }; break;
3765 case QUERY_ONE_INSTANCE_ATTACHMENTS:
3766 {
3767 Orthanc::DatabasePluginMessages::FileInfo* attachment = responses[internalId]->add_one_instance_attachments();
3768
3769 attachment->set_uuid(statement.ReadString(C3_STRING_1));
3770 attachment->set_uncompressed_hash(statement.ReadString(C4_STRING_2));
3771 attachment->set_compressed_hash(statement.ReadString(C5_STRING_3));
3772 attachment->set_content_type(statement.ReadInteger32(C6_INT_1));
3773 attachment->set_compression_type(statement.ReadInteger32(C7_INT_2));
3774 attachment->set_compressed_size(statement.ReadInteger64(C8_BIG_INT_1));
3775 attachment->set_uncompressed_size(statement.ReadInteger64(C9_BIG_INT_2));
3776 }; break;
3777
3539 default: 3778 default:
3540 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); 3779 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
3541 } 3780 }
3542 statement.Next(); 3781 statement.Next();
3543 } 3782 }