orthanc: OrthancServer/Sources/Search/ISqlLookupFormatter.cpp annotate

annotate OrthancServer/Sources/Search/ISqlLookupFormatter.cpp @ 5825:881cd0965146 find-refactoring

added OrderBy support in SQLite

author	Alain Mazy <am@orthanc.team>
date	Fri, 04 Oct 2024 19:03:14 +0200
parents	16ce3c920f71
children	d73dfb4548c6

rev	line source
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	1 /**
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	2 * Orthanc - A Lightweight, RESTful DICOM Store
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	3 * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	4 * Department, University Hospital of Liege, Belgium
5640 f7adfb22e20e updated copyright, as Orthanc Team now replaces Osimis Sebastien Jodogne <s.jodogne@gmail.com> parents: 5485 diff changeset	5 * Copyright (C) 2017-2023 Osimis S.A., Belgium
f7adfb22e20e updated copyright, as Orthanc Team now replaces Osimis Sebastien Jodogne <s.jodogne@gmail.com> parents: 5485 diff changeset	6 * Copyright (C) 2024-2024 Orthanc Team SRL, Belgium
5485 48b8dae6dc77 upgrade to year 2024 Sebastien Jodogne <s.jodogne@gmail.com> parents: 5477 diff changeset	7 * Copyright (C) 2021-2024 Sebastien Jodogne, ICTEAM UCLouvain, Belgium
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	8 *
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	9 * This program is free software: you can redistribute it and/or
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	10 * modify it under the terms of the GNU General Public License as
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	11 * published by the Free Software Foundation, either version 3 of the
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	12 * License, or (at your option) any later version.
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	13 *
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	14 * This program is distributed in the hope that it will be useful, but
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	15 * WITHOUT ANY WARRANTY; without even the implied warranty of
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	17 * General Public License for more details.
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	18 *
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	19 * You should have received a copy of the GNU General Public License
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	20 * along with this program. If not, see <http://www.gnu.org/licenses/>.
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	21 **/
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	22
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	23
5765 247fc5368693 un-sharing DatabaseConstraint and ISqlLookupFormatter with orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: 5762 diff changeset	24 #include "../PrecompiledHeadersServer.h"
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	25 #include "ISqlLookupFormatter.h"
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	26
5765 247fc5368693 un-sharing DatabaseConstraint and ISqlLookupFormatter with orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: 5762 diff changeset	27 #include "../../../OrthancFramework/Sources/OrthancException.h"
247fc5368693 un-sharing DatabaseConstraint and ISqlLookupFormatter with orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: 5762 diff changeset	28 #include "../../../OrthancFramework/Sources/Toolbox.h"
247fc5368693 un-sharing DatabaseConstraint and ISqlLookupFormatter with orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: 5762 diff changeset	29 #include "../Database/FindRequest.h"
3100 f6de9d25b20d removing build dependency on plugins Sebastien Jodogne <s.jodogne@gmail.com> parents: 3079 diff changeset	30 #include "DatabaseConstraint.h"
5825 881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	31 #include "../Database/MainDicomTagsRegistry.h"
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	32
5477 78f68ad10b69 missing includes for assert() Alain Mazy <am@osimis.io> parents: 5460 diff changeset	33 #include <cassert>
4304 50b0c69b653a continued abi Sebastien Jodogne <s.jodogne@gmail.com> parents: 4096 diff changeset	34 #include <boost/lexical_cast.hpp>
5227 988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	35 #include <list>
4304 50b0c69b653a continued abi Sebastien Jodogne <s.jodogne@gmail.com> parents: 4096 diff changeset	36
50b0c69b653a continued abi Sebastien Jodogne <s.jodogne@gmail.com> parents: 4096 diff changeset	37
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	38 namespace Orthanc
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	39 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	40 static std::string FormatLevel(ResourceType level)
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	41 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	42 switch (level)
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	43 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	44 case ResourceType_Patient:
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	45 return "patients";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	46
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	47 case ResourceType_Study:
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	48 return "studies";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	49
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	50 case ResourceType_Series:
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	51 return "series";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	52
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	53 case ResourceType_Instance:
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	54 return "instances";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	55
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	56 default:
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	57 throw OrthancException(ErrorCode_InternalError);
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	58 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	59 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	60
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	61
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	62 static bool FormatComparison(std::string& target,
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	63 ISqlLookupFormatter& formatter,
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	64 const DatabaseConstraint& constraint,
4817 b8fcd331b4b3 added ISqlLookupFormatter::IsEscapeBrackets() Sebastien Jodogne <s.jodogne@gmail.com> parents: 4437 diff changeset	65 size_t index,
b8fcd331b4b3 added ISqlLookupFormatter::IsEscapeBrackets() Sebastien Jodogne <s.jodogne@gmail.com> parents: 4437 diff changeset	66 bool escapeBrackets)
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	67 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	68 std::string tag = "t" + boost::lexical_cast<std::string>(index);
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	69
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	70 std::string comparison;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	71
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	72 switch (constraint.GetConstraintType())
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	73 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	74 case ConstraintType_Equal:
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	75 case ConstraintType_SmallerOrEqual:
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	76 case ConstraintType_GreaterOrEqual:
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	77 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	78 std::string op;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	79 switch (constraint.GetConstraintType())
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	80 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	81 case ConstraintType_Equal:
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	82 op = "=";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	83 break;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	84
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	85 case ConstraintType_SmallerOrEqual:
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	86 op = "<=";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	87 break;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	88
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	89 case ConstraintType_GreaterOrEqual:
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	90 op = ">=";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	91 break;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	92
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	93 default:
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	94 throw OrthancException(ErrorCode_InternalError);
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	95 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	96
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	97 std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue());
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	98
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	99 if (constraint.IsCaseSensitive())
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	100 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	101 comparison = tag + ".value " + op + " " + parameter;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	102 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	103 else
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	104 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	105 comparison = "lower(" + tag + ".value) " + op + " lower(" + parameter + ")";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	106 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	107
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	108 break;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	109 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	110
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	111 case ConstraintType_List:
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	112 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	113 for (size_t i = 0; i < constraint.GetValuesCount(); i++)
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	114 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	115 if (!comparison.empty())
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	116 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	117 comparison += ", ";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	118 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	119
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	120 std::string parameter = formatter.GenerateParameter(constraint.GetValue(i));
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	121
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	122 if (constraint.IsCaseSensitive())
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	123 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	124 comparison += parameter;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	125 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	126 else
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	127 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	128 comparison += "lower(" + parameter + ")";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	129 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	130 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	131
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	132 if (constraint.IsCaseSensitive())
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	133 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	134 comparison = tag + ".value IN (" + comparison + ")";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	135 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	136 else
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	137 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	138 comparison = "lower(" + tag + ".value) IN (" + comparison + ")";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	139 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	140
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	141 break;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	142 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	143
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	144 case ConstraintType_Wildcard:
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	145 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	146 const std::string value = constraint.GetSingleValue();
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	147
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	148 if (value == "*")
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	149 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	150 if (!constraint.IsMandatory())
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	151 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	152 // Universal constraint on an optional tag, ignore it
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	153 return false;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	154 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	155 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	156 else
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	157 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	158 std::string escaped;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	159 escaped.reserve(value.size());
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	160
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	161 for (size_t i = 0; i < value.size(); i++)
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	162 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	163 if (value[i] == '*')
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	164 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	165 escaped += "%";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	166 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	167 else if (value[i] == '?')
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	168 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	169 escaped += "_";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	170 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	171 else if (value[i] == '%')
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	172 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	173 escaped += "\\%";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	174 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	175 else if (value[i] == '_')
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	176 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	177 escaped += "\\_";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	178 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	179 else if (value[i] == '\\')
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	180 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	181 escaped += "\\\\";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	182 }
4817 b8fcd331b4b3 added ISqlLookupFormatter::IsEscapeBrackets() Sebastien Jodogne <s.jodogne@gmail.com> parents: 4437 diff changeset	183 else if (escapeBrackets && value[i] == '[')
b8fcd331b4b3 added ISqlLookupFormatter::IsEscapeBrackets() Sebastien Jodogne <s.jodogne@gmail.com> parents: 4437 diff changeset	184 {
b8fcd331b4b3 added ISqlLookupFormatter::IsEscapeBrackets() Sebastien Jodogne <s.jodogne@gmail.com> parents: 4437 diff changeset	185 escaped += "\\[";
b8fcd331b4b3 added ISqlLookupFormatter::IsEscapeBrackets() Sebastien Jodogne <s.jodogne@gmail.com> parents: 4437 diff changeset	186 }
b8fcd331b4b3 added ISqlLookupFormatter::IsEscapeBrackets() Sebastien Jodogne <s.jodogne@gmail.com> parents: 4437 diff changeset	187 else if (escapeBrackets && value[i] == ']')
b8fcd331b4b3 added ISqlLookupFormatter::IsEscapeBrackets() Sebastien Jodogne <s.jodogne@gmail.com> parents: 4437 diff changeset	188 {
b8fcd331b4b3 added ISqlLookupFormatter::IsEscapeBrackets() Sebastien Jodogne <s.jodogne@gmail.com> parents: 4437 diff changeset	189 escaped += "\\]";
b8fcd331b4b3 added ISqlLookupFormatter::IsEscapeBrackets() Sebastien Jodogne <s.jodogne@gmail.com> parents: 4437 diff changeset	190 }
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	191 else
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	192 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	193 escaped += value[i];
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	194 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	195 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	196
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	197 std::string parameter = formatter.GenerateParameter(escaped);
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	198
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	199 if (constraint.IsCaseSensitive())
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	200 {
3079 65e2bfa953ef ISqlLookupFormatter::FormatWildcardEscape() Sebastien Jodogne <s.jodogne@gmail.com> parents: 3078 diff changeset	201 comparison = (tag + ".value LIKE " + parameter + " " +
65e2bfa953ef ISqlLookupFormatter::FormatWildcardEscape() Sebastien Jodogne <s.jodogne@gmail.com> parents: 3078 diff changeset	202 formatter.FormatWildcardEscape());
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	203 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	204 else
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	205 {
3079 65e2bfa953ef ISqlLookupFormatter::FormatWildcardEscape() Sebastien Jodogne <s.jodogne@gmail.com> parents: 3078 diff changeset	206 comparison = ("lower(" + tag + ".value) LIKE lower(" +
65e2bfa953ef ISqlLookupFormatter::FormatWildcardEscape() Sebastien Jodogne <s.jodogne@gmail.com> parents: 3078 diff changeset	207 parameter + ") " + formatter.FormatWildcardEscape());
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	208 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	209 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	210
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	211 break;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	212 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	213
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	214 default:
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	215 return false;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	216 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	217
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	218 if (constraint.IsMandatory())
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	219 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	220 target = comparison;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	221 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	222 else if (comparison.empty())
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	223 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	224 target = tag + ".value IS NULL";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	225 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	226 else
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	227 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	228 target = tag + ".value IS NULL OR " + comparison;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	229 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	230
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	231 return true;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	232 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	233
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	234
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	235 static void FormatJoin(std::string& target,
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	236 const DatabaseConstraint& constraint,
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	237 size_t index)
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	238 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	239 std::string tag = "t" + boost::lexical_cast<std::string>(index);
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	240
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	241 if (constraint.IsMandatory())
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	242 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	243 target = " INNER JOIN ";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	244 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	245 else
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	246 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	247 target = " LEFT JOIN ";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	248 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	249
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	250 if (constraint.IsIdentifier())
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	251 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	252 target += "DicomIdentifiers ";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	253 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	254 else
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	255 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	256 target += "MainDicomTags ";
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	257 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	258
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	259 target += (tag + " ON " + tag + ".id = " + FormatLevel(constraint.GetLevel()) +
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	260 ".internalId AND " + tag + ".tagGroup = " +
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	261 boost::lexical_cast<std::string>(constraint.GetTag().GetGroup()) +
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	262 " AND " + tag + ".tagElement = " +
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	263 boost::lexical_cast<std::string>(constraint.GetTag().GetElement()));
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	264 }
5227 988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	265
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	266
5825 881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	267 static void FormatJoinForOrdering(std::string& target,
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	268 const DicomTag& tag,
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	269 size_t index,
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	270 ResourceType requestLevel)
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	271 {
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	272 std::string orderArg = "order" + boost::lexical_cast<std::string>(index);
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	273
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	274 target.clear();
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	275
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	276 ResourceType tagLevel;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	277 DicomTagType tagType;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	278 MainDicomTagsRegistry registry;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	279
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	280 registry.LookupTag(tagLevel, tagType, tag);
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	281
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	282 if (tagLevel == ResourceType_Patient && requestLevel == ResourceType_Study)
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	283 { // Patient tags are copied at study level
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	284 tagLevel = ResourceType_Study;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	285 }
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	286
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	287 std::string tagTable;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	288 if (tagType == DicomTagType_Identifier)
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	289 {
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	290 tagTable = "DicomIdentifiers ";
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	291 }
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	292 else
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	293 {
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	294 tagTable = "MainDicomTags ";
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	295 }
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	296
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	297 std::string tagFilter = orderArg + ".tagGroup = " + boost::lexical_cast<std::string>(tag.GetGroup()) + " AND " + orderArg + ".tagElement = " + boost::lexical_cast<std::string>(tag.GetElement());
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	298
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	299 if (tagLevel == requestLevel)
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	300 {
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	301 target = " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + FormatLevel(requestLevel) +
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	302 ".internalId AND " + tagFilter;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	303 }
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	304 else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 1)
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	305 {
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	306 target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId "
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	307 " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "parent.internalId AND " + tagFilter;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	308 }
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	309 else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 2)
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	310 {
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	311 target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId "
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	312 " INNER JOIN Resources " + orderArg + "grandparent ON " + orderArg + "grandparent.internalId = " + orderArg + "parent.parentId "
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	313 " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "grandparent.internalId AND " + tagFilter;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	314 }
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	315 else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 3)
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	316 {
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	317 target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId "
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	318 " INNER JOIN Resources " + orderArg + "grandparent ON " + orderArg + "grandparent.internalId = " + orderArg + "parent.parentId "
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	319 " INNER JOIN Resources " + orderArg + "grandgrandparent ON " + orderArg + "grandgrandparent.internalId = " + orderArg + "grandparent.parentId "
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	320 " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "grandgrandparent.internalId AND " + tagFilter;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	321 }
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	322 }
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	323
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	324 static void FormatJoinForOrdering(std::string& target,
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	325 const MetadataType& metadata,
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	326 size_t index,
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	327 ResourceType requestLevel)
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	328 {
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	329 std::string arg = "order" + boost::lexical_cast<std::string>(index);
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	330
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	331
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	332 target = " INNER JOIN Metadata " + arg + " ON " + arg + ".id = " + FormatLevel(requestLevel) +
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	333 ".internalId AND " + arg + ".type = " +
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	334 boost::lexical_cast<std::string>(metadata);
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	335 }
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	336
5227 988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	337 static std::string Join(const std::list<std::string>& values,
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	338 const std::string& prefix,
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	339 const std::string& separator)
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	340 {
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	341 if (values.empty())
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	342 {
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	343 return "";
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	344 }
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	345 else
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	346 {
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	347 std::string s = prefix;
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	348
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	349 bool first = true;
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	350 for (std::list<std::string>::const_iterator it = values.begin(); it != values.end(); ++it)
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	351 {
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	352 if (first)
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	353 {
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	354 first = false;
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	355 }
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	356 else
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	357 {
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	358 s += separator;
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	359 }
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	360
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	361 s += *it;
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	362 }
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	363
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	364 return s;
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	365 }
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	366 }
5422 7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	367
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	368 static bool FormatComparison2(std::string& target,
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	369 ISqlLookupFormatter& formatter,
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	370 const DatabaseConstraint& constraint,
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	371 bool escapeBrackets)
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	372 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	373 std::string comparison;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	374 std::string tagFilter = ("tagGroup = " + boost::lexical_cast<std::string>(constraint.GetTag().GetGroup())
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	375 + " AND tagElement = " + boost::lexical_cast<std::string>(constraint.GetTag().GetElement()));
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	376
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	377 switch (constraint.GetConstraintType())
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	378 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	379 case ConstraintType_Equal:
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	380 case ConstraintType_SmallerOrEqual:
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	381 case ConstraintType_GreaterOrEqual:
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	382 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	383 std::string op;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	384 switch (constraint.GetConstraintType())
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	385 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	386 case ConstraintType_Equal:
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	387 op = "=";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	388 break;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	389
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	390 case ConstraintType_SmallerOrEqual:
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	391 op = "<=";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	392 break;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	393
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	394 case ConstraintType_GreaterOrEqual:
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	395 op = ">=";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	396 break;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	397
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	398 default:
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	399 throw OrthancException(ErrorCode_InternalError);
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	400 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	401
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	402 std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue());
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	403
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	404 if (constraint.IsCaseSensitive())
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	405 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	406 comparison = " AND value " + op + " " + parameter;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	407 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	408 else
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	409 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	410 comparison = " AND lower(value) " + op + " lower(" + parameter + ")";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	411 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	412
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	413 break;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	414 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	415
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	416 case ConstraintType_List:
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	417 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	418 std::vector<std::string> comparisonValues;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	419 for (size_t i = 0; i < constraint.GetValuesCount(); i++)
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	420 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	421 std::string parameter = formatter.GenerateParameter(constraint.GetValue(i));
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	422
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	423 if (constraint.IsCaseSensitive())
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	424 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	425 comparisonValues.push_back(parameter);
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	426 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	427 else
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	428 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	429 comparisonValues.push_back("lower(" + parameter + ")");
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	430 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	431 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	432
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	433 std::string values;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	434 Toolbox::JoinStrings(values, comparisonValues, ", ");
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	435
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	436 if (constraint.IsCaseSensitive())
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	437 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	438 comparison = " AND value IN (" + values + ")";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	439 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	440 else
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	441 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	442 comparison = " AND lower(value) IN (" + values + ")";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	443 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	444
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	445 break;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	446 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	447
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	448 case ConstraintType_Wildcard:
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	449 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	450 const std::string value = constraint.GetSingleValue();
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	451
5422 7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	452 if (value == "*")
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	453 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	454 if (!constraint.IsMandatory())
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	455 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	456 // Universal constraint on an optional tag, ignore it
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	457 return false;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	458 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	459 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	460 else
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	461 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	462 std::string escaped;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	463 escaped.reserve(value.size());
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	464
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	465 for (size_t i = 0; i < value.size(); i++)
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	466 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	467 if (value[i] == '*')
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	468 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	469 escaped += "%";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	470 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	471 else if (value[i] == '?')
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	472 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	473 escaped += "_";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	474 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	475 else if (value[i] == '%')
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	476 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	477 escaped += "\\%";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	478 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	479 else if (value[i] == '_')
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	480 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	481 escaped += "\\_";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	482 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	483 else if (value[i] == '\\')
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	484 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	485 escaped += "\\\\";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	486 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	487 else if (escapeBrackets && value[i] == '[')
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	488 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	489 escaped += "\\[";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	490 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	491 else if (escapeBrackets && value[i] == ']')
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	492 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	493 escaped += "\\]";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	494 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	495 else
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	496 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	497 escaped += value[i];
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	498 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	499 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	500
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	501 std::string parameter = formatter.GenerateParameter(escaped);
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	502
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	503 if (constraint.IsCaseSensitive())
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	504 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	505 comparison = " AND value LIKE " + parameter + " " + formatter.FormatWildcardEscape();
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	506 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	507 else
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	508 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	509 comparison = " AND lower(value) LIKE lower(" + parameter + ") " + formatter.FormatWildcardEscape();
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	510 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	511 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	512
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	513 break;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	514 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	515
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	516 default:
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	517 return false;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	518 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	519
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	520 if (constraint.IsMandatory())
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	521 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	522 target = tagFilter + comparison;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	523 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	524 else if (comparison.empty())
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	525 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	526 target = tagFilter + " AND value IS NULL";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	527 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	528 else
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	529 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	530 target = tagFilter + " AND value IS NULL OR " + comparison;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	531 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	532
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	533 return true;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	534 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	535
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	536
5680 68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	537 void ISqlLookupFormatter::GetLookupLevels(ResourceType& lowerLevel,
68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	538 ResourceType& upperLevel,
68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	539 const ResourceType& queryLevel,
68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	540 const DatabaseConstraints& lookup)
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	541 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	542 assert(ResourceType_Patient < ResourceType_Study &&
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	543 ResourceType_Study < ResourceType_Series &&
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	544 ResourceType_Series < ResourceType_Instance);
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	545
5422 7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	546 lowerLevel = queryLevel;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	547 upperLevel = queryLevel;
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	548
5680 68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	549 for (size_t i = 0; i < lookup.GetSize(); i++)
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	550 {
5680 68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	551 ResourceType level = lookup.GetConstraint(i).GetLevel();
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	552
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	553 if (level < upperLevel)
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	554 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	555 upperLevel = level;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	556 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	557
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	558 if (level > lowerLevel)
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	559 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	560 lowerLevel = level;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	561 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	562 }
5422 7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	563 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	564
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	565
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	566 void ISqlLookupFormatter::Apply(std::string& sql,
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	567 ISqlLookupFormatter& formatter,
5680 68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	568 const DatabaseConstraints& lookup,
5422 7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	569 ResourceType queryLevel,
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	570 const std::set<std::string>& labels,
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	571 LabelsConstraint labelsConstraint,
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	572 size_t limit)
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	573 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	574 ResourceType lowerLevel, upperLevel;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	575 GetLookupLevels(lowerLevel, upperLevel, queryLevel, lookup);
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	576
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	577 assert(upperLevel <= queryLevel &&
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	578 queryLevel <= lowerLevel);
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	579
4817 b8fcd331b4b3 added ISqlLookupFormatter::IsEscapeBrackets() Sebastien Jodogne <s.jodogne@gmail.com> parents: 4437 diff changeset	580 const bool escapeBrackets = formatter.IsEscapeBrackets();
b8fcd331b4b3 added ISqlLookupFormatter::IsEscapeBrackets() Sebastien Jodogne <s.jodogne@gmail.com> parents: 4437 diff changeset	581
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	582 std::string joins, comparisons;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	583
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	584 size_t count = 0;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	585
5680 68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	586 for (size_t i = 0; i < lookup.GetSize(); i++)
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	587 {
5680 68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	588 const DatabaseConstraint& constraint = lookup.GetConstraint(i);
68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	589
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	590 std::string comparison;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	591
5680 68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	592 if (FormatComparison(comparison, formatter, constraint, count, escapeBrackets))
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	593 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	594 std::string join;
5680 68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	595 FormatJoin(join, constraint, count);
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	596 joins += join;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	597
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	598 if (!comparison.empty())
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	599 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	600 comparisons += " AND " + comparison;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	601 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	602
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	603 count ++;
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	604 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	605 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	606
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	607 sql = ("SELECT " +
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	608 FormatLevel(queryLevel) + ".publicId, " +
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	609 FormatLevel(queryLevel) + ".internalId" +
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	610 " FROM Resources AS " + FormatLevel(queryLevel));
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	611
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	612 for (int level = queryLevel - 1; level >= upperLevel; level--)
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	613 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	614 sql += (" INNER JOIN Resources " +
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	615 FormatLevel(static_cast<ResourceType>(level)) + " ON " +
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	616 FormatLevel(static_cast<ResourceType>(level)) + ".internalId=" +
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	617 FormatLevel(static_cast<ResourceType>(level + 1)) + ".parentId");
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	618 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	619
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	620 for (int level = queryLevel + 1; level <= lowerLevel; level++)
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	621 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	622 sql += (" INNER JOIN Resources " +
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	623 FormatLevel(static_cast<ResourceType>(level)) + " ON " +
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	624 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" +
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	625 FormatLevel(static_cast<ResourceType>(level)) + ".parentId");
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	626 }
5227 988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	627
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	628 std::list<std::string> where;
5256 261ce0ed85e6 fix Sebastien Jodogne <s.jodogne@gmail.com> parents: 5248 diff changeset	629 where.push_back(FormatLevel(queryLevel) + ".resourceType = " +
261ce0ed85e6 fix Sebastien Jodogne <s.jodogne@gmail.com> parents: 5248 diff changeset	630 formatter.FormatResourceType(queryLevel) + comparisons);
5227 988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	631
5248 a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	632 if (!labels.empty())
5227 988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	633 {
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	634 /**
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	635 * "In SQL Server, NOT EXISTS and NOT IN predicates are the best
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	636 * way to search for missing values, as long as both columns in
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	637 * question are NOT NULL."
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	638 * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	639 **/
5248 a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	640
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	641 std::list<std::string> formattedLabels;
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	642 for (std::set<std::string>::const_iterator it = labels.begin(); it != labels.end(); ++it)
5227 988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	643 {
5248 a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	644 formattedLabels.push_back(formatter.GenerateParameter(*it));
5227 988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	645 }
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	646
5248 a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	647 std::string condition;
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	648 switch (labelsConstraint)
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	649 {
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	650 case LabelsConstraint_Any:
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	651 condition = "> 0";
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	652 break;
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	653
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	654 case LabelsConstraint_All:
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	655 condition = "= " + boost::lexical_cast<std::string>(labels.size());
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	656 break;
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	657
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	658 case LabelsConstraint_None:
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	659 condition = "= 0";
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	660 break;
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	661
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	662 default:
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	663 throw OrthancException(ErrorCode_ParameterOutOfRange);
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	664 }
a7d95f951f8a replaced "WithLabels" and "WithoutLabels", by "Labels" and "LabelsConstraint" Sebastien Jodogne <s.jodogne@gmail.com> parents: 5227 diff changeset	665
5256 261ce0ed85e6 fix Sebastien Jodogne <s.jodogne@gmail.com> parents: 5248 diff changeset	666 where.push_back("(SELECT COUNT(1) FROM Labels AS selectedLabels WHERE selectedLabels.id = " + FormatLevel(queryLevel) +
261ce0ed85e6 fix Sebastien Jodogne <s.jodogne@gmail.com> parents: 5248 diff changeset	667 ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition);
5227 988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	668 }
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	669
988dab8deb1c "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments Sebastien Jodogne <s.jodogne@gmail.com> parents: 5185 diff changeset	670 sql += joins + Join(where, " WHERE ", " AND ");
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	671
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	672 if (limit != 0)
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	673 {
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	674 sql += " LIMIT " + boost::lexical_cast<std::string>(limit);
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	675 }
6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	676 }
5422 7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	677
5765 247fc5368693 un-sharing DatabaseConstraint and ISqlLookupFormatter with orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: 5762 diff changeset	678
5748 4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	679 void ISqlLookupFormatter::Apply(std::string& sql,
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	680 ISqlLookupFormatter& formatter,
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	681 const FindRequest& request)
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	682 {
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	683 const bool escapeBrackets = formatter.IsEscapeBrackets();
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	684 ResourceType queryLevel = request.GetLevel();
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	685 const std::string& strQueryLevel = FormatLevel(queryLevel);
5750 f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	686
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	687 ResourceType lowerLevel, upperLevel;
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	688 GetLookupLevels(lowerLevel, upperLevel, queryLevel, request.GetDicomTagConstraints());
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	689
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	690 assert(upperLevel <= queryLevel &&
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	691 queryLevel <= lowerLevel);
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	692
5825 881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	693 std::string ordering;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	694 std::string orderingJoins;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	695
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	696 if (request.GetOrdering().size() > 0)
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	697 {
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	698 int counter = 0;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	699 std::vector<std::string> orderByFields;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	700 for (std::deque<FindRequest::Ordering*>::const_iterator it = request.GetOrdering().begin(); it != request.GetOrdering().end(); ++it)
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	701 {
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	702 std::string orderingJoin;
5796 16ce3c920f71 rewrote SQLite find using CTEs instead of temporary tables Alain Mazy <am@orthanc.team> parents: 5765 diff changeset	703
5825 881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	704 switch ((*it)->GetKeyType())
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	705 {
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	706 case FindRequest::KeyType_DicomTag:
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	707 FormatJoinForOrdering(orderingJoin, (*it)->GetDicomTag(), counter, request.GetLevel());
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	708 break;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	709 case FindRequest::KeyType_Metadata:
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	710 FormatJoinForOrdering(orderingJoin, (*it)->GetMetadataType(), counter, request.GetLevel());
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	711 break;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	712 default:
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	713 throw OrthancException(ErrorCode_InternalError);
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	714 }
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	715 orderingJoins += orderingJoin;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	716
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	717 std::string orderByField = "order" + boost::lexical_cast<std::string>(counter) + ".value";
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	718 if ((*it)->GetDirection() == FindRequest::OrderingDirection_Ascending)
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	719 {
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	720 orderByField += " ASC";
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	721 }
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	722 else
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	723 {
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	724 orderByField += " DESC";
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	725 }
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	726 orderByFields.push_back(orderByField);
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	727 ++counter;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	728 }
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	729
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	730 std::string orderByFieldsString;
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	731 Toolbox::JoinStrings(orderByFieldsString, orderByFields, ", ");
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	732 ordering = "ROW_NUMBER() OVER (ORDER BY " + orderByFieldsString + ") AS rowNumber";
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	733 }
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	734 else
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	735 {
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	736 ordering = "ROW_NUMBER() OVER (ORDER BY " + strQueryLevel + ".publicId) AS rowNumber"; // we need a default ordering in order to make default queries repeatable when using since&limit
881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	737 }
5750 f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	738
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	739 sql = ("SELECT " +
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	740 strQueryLevel + ".publicId, " +
5796 16ce3c920f71 rewrote SQLite find using CTEs instead of temporary tables Alain Mazy <am@orthanc.team> parents: 5765 diff changeset	741 strQueryLevel + ".internalId, " +
16ce3c920f71 rewrote SQLite find using CTEs instead of temporary tables Alain Mazy <am@orthanc.team> parents: 5765 diff changeset	742 ordering +
5750 f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	743 " FROM Resources AS " + strQueryLevel);
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	744
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	745
5748 4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	746 std::string joins, comparisons;
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	747
5750 f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	748 if (request.GetOrthancIdentifiers().IsDefined() && request.GetOrthancIdentifiers().DetectLevel() <= queryLevel)
5748 4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	749 {
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	750 // single child resource matching, there should not be other constraints (at least for now)
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	751 assert(request.GetDicomTagConstraints().GetSize() == 0);
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	752 assert(request.GetLabels().size() == 0);
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	753 assert(request.HasLimits() == false);
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	754
5750 f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	755 ResourceType topParentLevel = request.GetOrthancIdentifiers().DetectLevel();
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	756 const std::string& strTopParentLevel = FormatLevel(topParentLevel);
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	757
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	758 comparisons = " AND " + strTopParentLevel + ".publicId = " + formatter.GenerateParameter(request.GetOrthancIdentifiers().GetLevel(topParentLevel));
5748 4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	759
5750 f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	760 for (int level = queryLevel; level > topParentLevel; level--)
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	761 {
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	762 sql += (" INNER JOIN Resources " +
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	763 FormatLevel(static_cast<ResourceType>(level - 1)) + " ON " +
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	764 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" +
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	765 FormatLevel(static_cast<ResourceType>(level)) + ".parentId");
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	766 }
5748 4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	767 }
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	768 else
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	769 {
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	770 size_t count = 0;
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	771
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	772 const DatabaseConstraints& dicomTagsConstraints = request.GetDicomTagConstraints();
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	773 for (size_t i = 0; i < dicomTagsConstraints.GetSize(); i++)
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	774 {
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	775 const DatabaseConstraint& constraint = dicomTagsConstraints.GetConstraint(i);
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	776
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	777 std::string comparison;
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	778
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	779 if (FormatComparison(comparison, formatter, constraint, count, escapeBrackets))
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	780 {
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	781 std::string join;
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	782 FormatJoin(join, constraint, count);
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	783 joins += join;
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	784
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	785 if (!comparison.empty())
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	786 {
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	787 comparisons += " AND " + comparison;
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	788 }
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	789
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	790 count ++;
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	791 }
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	792 }
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	793 }
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	794
5750 f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	795 for (int level = queryLevel - 1; level >= upperLevel; level--)
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	796 {
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	797 sql += (" INNER JOIN Resources " +
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	798 FormatLevel(static_cast<ResourceType>(level)) + " ON " +
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	799 FormatLevel(static_cast<ResourceType>(level)) + ".internalId=" +
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	800 FormatLevel(static_cast<ResourceType>(level + 1)) + ".parentId");
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	801 }
5748 4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	802
5750 f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	803 for (int level = queryLevel + 1; level <= lowerLevel; level++)
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	804 {
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	805 sql += (" INNER JOIN Resources " +
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	806 FormatLevel(static_cast<ResourceType>(level)) + " ON " +
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	807 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" +
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	808 FormatLevel(static_cast<ResourceType>(level)) + ".parentId");
f39406a9eda4 ExtendedFind in SQLite continued (6 tests still failing) Alain Mazy <am@orthanc.team> parents: 5748 diff changeset	809 }
5748 4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	810
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	811 std::list<std::string> where;
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	812 where.push_back(strQueryLevel + ".resourceType = " +
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	813 formatter.FormatResourceType(queryLevel) + comparisons);
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	814
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	815
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	816 if (!request.GetLabels().empty())
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	817 {
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	818 /**
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	819 * "In SQL Server, NOT EXISTS and NOT IN predicates are the best
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	820 * way to search for missing values, as long as both columns in
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	821 * question are NOT NULL."
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	822 * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	823 **/
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	824
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	825 const std::set<std::string>& labels = request.GetLabels();
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	826 std::list<std::string> formattedLabels;
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	827 for (std::set<std::string>::const_iterator it = labels.begin(); it != labels.end(); ++it)
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	828 {
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	829 formattedLabels.push_back(formatter.GenerateParameter(*it));
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	830 }
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	831
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	832 std::string condition;
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	833 switch (request.GetLabelsConstraint())
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	834 {
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	835 case LabelsConstraint_Any:
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	836 condition = "> 0";
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	837 break;
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	838
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	839 case LabelsConstraint_All:
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	840 condition = "= " + boost::lexical_cast<std::string>(labels.size());
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	841 break;
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	842
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	843 case LabelsConstraint_None:
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	844 condition = "= 0";
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	845 break;
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	846
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	847 default:
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	848 throw OrthancException(ErrorCode_ParameterOutOfRange);
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	849 }
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	850
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	851 where.push_back("(SELECT COUNT(1) FROM Labels AS selectedLabels WHERE selectedLabels.id = " + strQueryLevel +
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	852 ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition);
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	853 }
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	854
5825 881cd0965146 added OrderBy support in SQLite Alain Mazy <am@orthanc.team> parents: 5796 diff changeset	855 sql += joins + orderingJoins + Join(where, " WHERE ", " AND ");
5748 4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	856
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	857 if (request.HasLimits())
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	858 {
5762 d52b3f394f69 FormatLimits (SQLServer needs custom code) Alain Mazy <am@orthanc.team> parents: 5752 diff changeset	859 sql += formatter.FormatLimits(request.GetLimitsSince(), request.GetLimitsCount());
5748 4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	860 }
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	861 }
4bc650d88463 WIP: started to implement IntegratedFind in SQLite Alain Mazy <am@orthanc.team> parents: 5680 diff changeset	862
5422 7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	863
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	864 void ISqlLookupFormatter::ApplySingleLevel(std::string& sql,
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	865 ISqlLookupFormatter& formatter,
5680 68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	866 const DatabaseConstraints& lookup,
5422 7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	867 ResourceType queryLevel,
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	868 const std::set<std::string>& labels,
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	869 LabelsConstraint labelsConstraint,
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	870 size_t limit
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	871 )
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	872 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	873 ResourceType lowerLevel, upperLevel;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	874 GetLookupLevels(lowerLevel, upperLevel, queryLevel, lookup);
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	875
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	876 assert(upperLevel == queryLevel &&
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	877 queryLevel == lowerLevel);
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	878
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	879 const bool escapeBrackets = formatter.IsEscapeBrackets();
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	880
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	881 std::vector<std::string> mainDicomTagsComparisons, dicomIdentifiersComparisons;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	882
5680 68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	883 for (size_t i = 0; i < lookup.GetSize(); i++)
5422 7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	884 {
5680 68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	885 const DatabaseConstraint& constraint = lookup.GetConstraint(i);
68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	886
5422 7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	887 std::string comparison;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	888
5680 68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	889 if (FormatComparison2(comparison, formatter, constraint, escapeBrackets))
5422 7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	890 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	891 if (!comparison.empty())
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	892 {
5680 68fc5af30c03 added container class DatabaseConstraints Sebastien Jodogne <s.jodogne@gmail.com> parents: 5640 diff changeset	893 if (constraint.IsIdentifier())
5422 7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	894 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	895 dicomIdentifiersComparisons.push_back(comparison);
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	896 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	897 else
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	898 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	899 mainDicomTagsComparisons.push_back(comparison);
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	900 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	901 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	902 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	903 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	904
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	905 sql = ("SELECT publicId, internalId "
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	906 "FROM Resources "
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	907 "WHERE resourceType = " + formatter.FormatResourceType(queryLevel)
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	908 + " ");
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	909
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	910 if (dicomIdentifiersComparisons.size() > 0)
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	911 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	912 for (std::vector<std::string>::const_iterator it = dicomIdentifiersComparisons.begin(); it < dicomIdentifiersComparisons.end(); ++it)
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	913 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	914 sql += (" AND internalId IN (SELECT id FROM DicomIdentifiers WHERE " + *it + ") ");
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	915 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	916 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	917
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	918 if (mainDicomTagsComparisons.size() > 0)
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	919 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	920 for (std::vector<std::string>::const_iterator it = mainDicomTagsComparisons.begin(); it < mainDicomTagsComparisons.end(); ++it)
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	921 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	922 sql += (" AND internalId IN (SELECT id FROM MainDicomTags WHERE " + *it + ") ");
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	923 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	924 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	925
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	926 if (!labels.empty())
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	927 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	928 /**
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	929 * "In SQL Server, NOT EXISTS and NOT IN predicates are the best
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	930 * way to search for missing values, as long as both columns in
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	931 * question are NOT NULL."
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	932 * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	933 **/
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	934
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	935 std::list<std::string> formattedLabels;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	936 for (std::set<std::string>::const_iterator it = labels.begin(); it != labels.end(); ++it)
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	937 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	938 formattedLabels.push_back(formatter.GenerateParameter(*it));
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	939 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	940
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	941 std::string condition;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	942 std::string inOrNotIn;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	943 switch (labelsConstraint)
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	944 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	945 case LabelsConstraint_Any:
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	946 condition = "> 0";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	947 inOrNotIn = "IN";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	948 break;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	949
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	950 case LabelsConstraint_All:
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	951 condition = "= " + boost::lexical_cast<std::string>(labels.size());
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	952 inOrNotIn = "IN";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	953 break;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	954
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	955 case LabelsConstraint_None:
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	956 condition = "> 0";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	957 inOrNotIn = "NOT IN";
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	958 break;
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	959
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	960 default:
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	961 throw OrthancException(ErrorCode_ParameterOutOfRange);
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	962 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	963
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	964 sql += (" AND internalId " + inOrNotIn + " (SELECT id"
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	965 " FROM (SELECT id, COUNT(1) AS labelsCount "
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	966 "FROM Labels "
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	967 "WHERE label IN (" + Join(formattedLabels, "", ", ") + ") GROUP BY id"
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	968 ") AS temp "
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	969 " WHERE labelsCount " + condition + ")");
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	970 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	971
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	972 if (limit != 0)
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	973 {
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	974 sql += " LIMIT " + boost::lexical_cast<std::string>(limit);
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	975 }
7f37233359aa sync code from DB plugins Alain Mazy <am@osimis.io> parents: 5256 diff changeset	976 }
3058 6faf575ba9cc refactoring: class ISqlLookupFormatter to be used in orthanc-databases Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	977 }

Mercurial > hg > orthanc

annotate OrthancServer/Sources/Search/ISqlLookupFormatter.cpp @ 5825:881cd0965146 find-refactoring