Mercurial > hg > orthanc-databases

comparison Resources/Orthanc/Databases/ISqlLookupFormatter.cpp @ 398:8dedfd982b83 db-protobuf

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
implemented lookup for labels in postgresql
author Sebastien Jodogne <s.jodogne@gmail.com>
date Thu, 06 Apr 2023 19:09:51 +0200
parents c4f0f8087564
children a8774581adfc
comparison
equal deleted inserted replaced
397:c4f0f8087564 398:8dedfd982b83
37 #endif 37 #endif
38 38
39 #include "DatabaseConstraint.h" 39 #include "DatabaseConstraint.h"
40 40
41 #include <boost/lexical_cast.hpp> 41 #include <boost/lexical_cast.hpp>
42 #include <list>
42 43
43 44
44 namespace Orthanc 45 namespace Orthanc
45 { 46 {
46 static std::string FormatLevel(ResourceType level) 47 static std::string FormatLevel(ResourceType level)
266 ".internalId AND " + tag + ".tagGroup = " + 267 ".internalId AND " + tag + ".tagGroup = " +
267 boost::lexical_cast<std::string>(constraint.GetTag().GetGroup()) + 268 boost::lexical_cast<std::string>(constraint.GetTag().GetGroup()) +
268 " AND " + tag + ".tagElement = " + 269 " AND " + tag + ".tagElement = " +
269 boost::lexical_cast<std::string>(constraint.GetTag().GetElement())); 270 boost::lexical_cast<std::string>(constraint.GetTag().GetElement()));
270 } 271 }
272
273
274 static std::string Join(const std::list<std::string>& values,
275 const std::string& prefix,
276 const std::string& separator)
277 {
278 if (values.empty())
279 {
280 return "";
281 }
282 else
283 {
284 std::string s = prefix;
285
286 bool first = true;
287 for (std::list<std::string>::const_iterator it = values.begin(); it != values.end(); ++it)
288 {
289 if (first)
290 {
291 first = false;
292 }
293 else
294 {
295 s += separator;
296 }
297
298 s += *it;
299 }
300
301 return s;
302 }
303 }
271 304
272 305
273 void ISqlLookupFormatter::Apply(std::string& sql, 306 void ISqlLookupFormatter::Apply(std::string& sql,
274 ISqlLookupFormatter& formatter, 307 ISqlLookupFormatter& formatter,
275 const std::vector<DatabaseConstraint>& lookup, 308 const std::vector<DatabaseConstraint>& lookup,
276 ResourceType queryLevel, 309 ResourceType queryLevel,
310 const std::set<std::string>& withLabels,
311 const std::set<std::string>& withoutLabels,
277 size_t limit) 312 size_t limit)
278 { 313 {
279 assert(ResourceType_Patient < ResourceType_Study && 314 assert(ResourceType_Patient < ResourceType_Study &&
280 ResourceType_Study < ResourceType_Series && 315 ResourceType_Study < ResourceType_Series &&
281 ResourceType_Series < ResourceType_Instance); 316 ResourceType_Series < ResourceType_Instance);
344 sql += (" INNER JOIN Resources " + 379 sql += (" INNER JOIN Resources " +
345 FormatLevel(static_cast<ResourceType>(level)) + " ON " + 380 FormatLevel(static_cast<ResourceType>(level)) + " ON " +
346 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" + 381 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" +
347 FormatLevel(static_cast<ResourceType>(level)) + ".parentId"); 382 FormatLevel(static_cast<ResourceType>(level)) + ".parentId");
348 } 383 }
349 384
350 sql += (joins + " WHERE " + FormatLevel(queryLevel) + ".resourceType = " + 385 std::list<std::string> where;
351 formatter.FormatResourceType(queryLevel) + comparisons); 386
387 if (!withLabels.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 {
402 /**
403 * "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
405 * question are NOT NULL."
406 * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/
407 **/
408 std::list<std::string> labels;
409 for (std::set<std::string>::const_iterator it = withoutLabels.begin(); it != withoutLabels.end(); ++it)
410 {
411 labels.push_back(formatter.GenerateParameter(*it));
412 }
413
414 where.push_back("NOT EXISTS (SELECT 1 FROM Labels WHERE internalId = " + FormatLevel(queryLevel) +
415 ".internalId AND label IN (" + Join(labels, "", ", ") + "))");
416 }
417
418 where.push_back(FormatLevel(queryLevel) + ".resourceType = " +
419 formatter.FormatResourceType(queryLevel) + comparisons);
420
421 sql += joins + Join(where, " WHERE ", " AND ");
352 422
353 if (limit != 0) 423 if (limit != 0)
354 { 424 {
355 sql += " LIMIT " + boost::lexical_cast<std::string>(limit); 425 sql += " LIMIT " + boost::lexical_cast<std::string>(limit);
356 } 426 }