Mercurial > hg > orthanc-databases

comparison Resources/Orthanc/Databases/ISqlLookupFormatter.cpp @ 401:a8774581adfc db-protobuf

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint"
author Sebastien Jodogne <s.jodogne@gmail.com>
date Fri, 07 Apr 2023 22:32:15 +0200
parents 8dedfd982b83
children 3cd39c8cade9
comparison
equal deleted inserted replaced
400:897253c21208 401:a8774581adfc
305 305
306 void ISqlLookupFormatter::Apply(std::string& sql, 306 void ISqlLookupFormatter::Apply(std::string& sql,
307 ISqlLookupFormatter& formatter, 307 ISqlLookupFormatter& formatter,
308 const std::vector<DatabaseConstraint>& lookup, 308 const std::vector<DatabaseConstraint>& lookup,
309 ResourceType queryLevel, 309 ResourceType queryLevel,
310 const std::set<std::string>& withLabels, 310 const std::set<std::string>& labels,
311 const std::set<std::string>& withoutLabels, 311 LabelsConstraint labelsConstraint,
312 size_t limit) 312 size_t limit)
313 { 313 {
314 assert(ResourceType_Patient < ResourceType_Study && 314 assert(ResourceType_Patient < ResourceType_Study &&
315 ResourceType_Study < ResourceType_Series && 315 ResourceType_Study < ResourceType_Series &&
316 ResourceType_Series < ResourceType_Instance); 316 ResourceType_Series < ResourceType_Instance);
382 FormatLevel(static_cast<ResourceType>(level)) + ".parentId"); 382 FormatLevel(static_cast<ResourceType>(level)) + ".parentId");
383 } 383 }
384 384
385 std::list<std::string> where; 385 std::list<std::string> where;
386 386
387 if (!withLabels.empty()) 387 if (!labels.empty())
388 {
389 std::list<std::string> labels;
390 for (std::set<std::string>::const_iterator it = withLabels.begin(); it != withLabels.end(); ++it)
391 {
392 labels.push_back(formatter.GenerateParameter(*it));
393 }
394
395 where.push_back(boost::lexical_cast<std::string>(withLabels.size()) +
396 " = (SELECT COUNT(1) FROM Labels WHERE internalId = " + FormatLevel(queryLevel) +
397 ".internalId AND label IN (" + Join(labels, "", ", ") + "))");
398 }
399
400 if (!withoutLabels.empty())
401 { 388 {
402 /** 389 /**
403 * "In SQL Server, NOT EXISTS and NOT IN predicates are the best 390 * "In SQL Server, NOT EXISTS and NOT IN predicates are the best
404 * way to search for missing values, as long as both columns in 391 * way to search for missing values, as long as both columns in
405 * question are NOT NULL." 392 * question are NOT NULL."
406 * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ 393 * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/
407 **/ 394 **/
408 std::list<std::string> labels; 395
409 for (std::set<std::string>::const_iterator it = withoutLabels.begin(); it != withoutLabels.end(); ++it) 396 std::list<std::string> formattedLabels;
410 { 397 for (std::set<std::string>::const_iterator it = labels.begin(); it != labels.end(); ++it)
411 labels.push_back(formatter.GenerateParameter(*it)); 398 {
412 } 399 formattedLabels.push_back(formatter.GenerateParameter(*it));
413 400 }
414 where.push_back("NOT EXISTS (SELECT 1 FROM Labels WHERE internalId = " + FormatLevel(queryLevel) + 401
415 ".internalId AND label IN (" + Join(labels, "", ", ") + "))"); 402 std::string condition;
403 switch (labelsConstraint)
404 {
405 case LabelsConstraint_Any:
406 condition = "> 0";
407 break;
408
409 case LabelsConstraint_All:
410 condition = "= " + boost::lexical_cast<std::string>(labels.size());
411 break;
412
413 case LabelsConstraint_None:
414 condition = "= 0";
415 break;
416
417 default:
418 throw OrthancException(ErrorCode_ParameterOutOfRange);
419 }
420
421 where.push_back("(SELECT COUNT(1) FROM Labels WHERE internalId = " + FormatLevel(queryLevel) +
422 ".internalId AND label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition);
416 } 423 }
417 424
418 where.push_back(FormatLevel(queryLevel) + ".resourceType = " + 425 where.push_back(FormatLevel(queryLevel) + ".resourceType = " +
419 formatter.FormatResourceType(queryLevel) + comparisons); 426 formatter.FormatResourceType(queryLevel) + comparisons);
420 427