Mercurial > hg > orthanc-databases

comparison Resources/Orthanc/Databases/ISqlLookupFormatter.cpp @ 405:1938ba8fba35

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
sync
author Sebastien Jodogne <s.jodogne@gmail.com>
date Sat, 15 Apr 2023 13:49:53 +0200
parents 1280b40d6696
children de6de66d70b2
comparison
equal deleted inserted replaced
394:2fd272ea8f00 405:1938ba8fba35
1 /** 1 /**
2 * Orthanc - A Lightweight, RESTful DICOM Store 2 * Orthanc - A Lightweight, RESTful DICOM Store
3 * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics 3 * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
4 * Department, University Hospital of Liege, Belgium 4 * Department, University Hospital of Liege, Belgium
5 * Copyright (C) 2017-2022 Osimis S.A., Belgium 5 * Copyright (C) 2017-2023 Osimis S.A., Belgium
6 * Copyright (C) 2021-2022 Sebastien Jodogne, ICTEAM UCLouvain, Belgium 6 * Copyright (C) 2021-2023 Sebastien Jodogne, ICTEAM UCLouvain, Belgium
7 * 7 *
8 * This program is free software: you can redistribute it and/or 8 * This program is free software: you can redistribute it and/or
9 * modify it under the terms of the GNU General Public License as 9 * modify it under the terms of the GNU General Public License as
10 * published by the Free Software Foundation, either version 3 of the 10 * published by the Free Software Foundation, either version 3 of the
11 * License, or (at your option) any later version. 11 * License, or (at your option) any later version.
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>& labels,
311 LabelsConstraint labelsConstraint,
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 }
384
385 std::list<std::string> where;
386 where.push_back(FormatLevel(queryLevel) + ".resourceType = " +
387 formatter.FormatResourceType(queryLevel) + comparisons);
388
389 if (!labels.empty())
390 {
391 /**
392 * "In SQL Server, NOT EXISTS and NOT IN predicates are the best
393 * way to search for missing values, as long as both columns in
394 * question are NOT NULL."
395 * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/
396 **/
397
398 std::list<std::string> formattedLabels;
399 for (std::set<std::string>::const_iterator it = labels.begin(); it != labels.end(); ++it)
400 {
401 formattedLabels.push_back(formatter.GenerateParameter(*it));
402 }
403
404 std::string condition;
405 switch (labelsConstraint)
406 {
407 case LabelsConstraint_Any:
408 condition = "> 0";
409 break;
410
411 case LabelsConstraint_All:
412 condition = "= " + boost::lexical_cast<std::string>(labels.size());
413 break;
414
415 case LabelsConstraint_None:
416 condition = "= 0";
417 break;
418
419 default:
420 throw OrthancException(ErrorCode_ParameterOutOfRange);
421 }
349 422
350 sql += (joins + " WHERE " + FormatLevel(queryLevel) + ".resourceType = " + 423 where.push_back("(SELECT COUNT(1) FROM Labels AS selectedLabels WHERE selectedLabels.id = " + FormatLevel(queryLevel) +
351 formatter.FormatResourceType(queryLevel) + comparisons); 424 ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition);
425 }
426
427 sql += joins + Join(where, " WHERE ", " AND ");
352 428
353 if (limit != 0) 429 if (limit != 0)
354 { 430 {
355 sql += " LIMIT " + boost::lexical_cast<std::string>(limit); 431 sql += " LIMIT " + boost::lexical_cast<std::string>(limit);
356 } 432 }