Mercurial > hg > orthanc-databases

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

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
implemented lookup for labels in postgresql
author Sebastien Jodogne <s.jodogne@gmail.com>
date Thu, 06 Apr 2023 19:09:51 +0200
parents c4f0f8087564
children a8774581adfc
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
152
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
1 /**
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
2 * Orthanc - A Lightweight, RESTful DICOM Store
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
3 * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
4 * Department, University Hospital of Liege, Belgium
397
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 358
diff changeset
5 * Copyright (C) 2017-2023 Osimis S.A., Belgium
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 358
diff changeset
6 * Copyright (C) 2021-2023 Sebastien Jodogne, ICTEAM UCLouvain, Belgium
152
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
7 *
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
8 * This program is free software: you can redistribute it and/or
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
9 * modify it under the terms of the GNU General Public License as
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
10 * published by the Free Software Foundation, either version 3 of the
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
11 * License, or (at your option) any later version.
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
12 *
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
13 * This program is distributed in the hope that it will be useful, but
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
14 * WITHOUT ANY WARRANTY; without even the implied warranty of
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
16 * General Public License for more details.
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
17 *
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
18 * You should have received a copy of the GNU General Public License
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
19 * along with this program. If not, see <http://www.gnu.org/licenses/>.
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
20 **/
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
21
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
22
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
23 #if !defined(ORTHANC_BUILDING_SERVER_LIBRARY)
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
24 # error Macro ORTHANC_BUILDING_SERVER_LIBRARY must be defined
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
25 #endif
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
26
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
27 #if ORTHANC_BUILDING_SERVER_LIBRARY == 1
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
28 # include "../PrecompiledHeadersServer.h"
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
29 #endif
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
30
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
31 #include "ISqlLookupFormatter.h"
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
32
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
33 #if ORTHANC_BUILDING_SERVER_LIBRARY == 1
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
34 # include "../../../OrthancFramework/Sources/OrthancException.h"
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
35 #else
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
36 # include <OrthancException.h>
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
37 #endif
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
38
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
39 #include "DatabaseConstraint.h"
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
40
170
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 152
diff changeset
41 #include <boost/lexical_cast.hpp>
398
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
42 #include <list>
170
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 152
diff changeset
43
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 152
diff changeset
44
152
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
45 namespace Orthanc
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
46 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
47 static std::string FormatLevel(ResourceType level)
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
48 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
49 switch (level)
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
50 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
51 case ResourceType_Patient:
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
52 return "patients";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
53
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
54 case ResourceType_Study:
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
55 return "studies";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
56
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
57 case ResourceType_Series:
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
58 return "series";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
59
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
60 case ResourceType_Instance:
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
61 return "instances";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
62
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
63 default:
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
64 throw OrthancException(ErrorCode_InternalError);
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
65 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
66 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
67
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
68
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
69 static bool FormatComparison(std::string& target,
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
70 ISqlLookupFormatter& formatter,
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
71 const DatabaseConstraint& constraint,
354
2a3bbb4104fa fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 353
diff changeset
72 size_t index,
2a3bbb4104fa fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 353
diff changeset
73 bool escapeBrackets)
152
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
74 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
75 std::string tag = "t" + boost::lexical_cast<std::string>(index);
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
76
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
77 std::string comparison;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
78
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
79 switch (constraint.GetConstraintType())
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
80 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
81 case ConstraintType_Equal:
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
82 case ConstraintType_SmallerOrEqual:
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
83 case ConstraintType_GreaterOrEqual:
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
84 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
85 std::string op;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
86 switch (constraint.GetConstraintType())
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
87 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
88 case ConstraintType_Equal:
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
89 op = "=";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
90 break;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
91
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
92 case ConstraintType_SmallerOrEqual:
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
93 op = "<=";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
94 break;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
95
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
96 case ConstraintType_GreaterOrEqual:
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
97 op = ">=";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
98 break;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
99
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
100 default:
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
101 throw OrthancException(ErrorCode_InternalError);
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
102 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
103
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
104 std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue());
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
105
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
106 if (constraint.IsCaseSensitive())
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
107 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
108 comparison = tag + ".value " + op + " " + parameter;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
109 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
110 else
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
111 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
112 comparison = "lower(" + tag + ".value) " + op + " lower(" + parameter + ")";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
113 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
114
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
115 break;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
116 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
117
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
118 case ConstraintType_List:
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
119 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
120 for (size_t i = 0; i < constraint.GetValuesCount(); i++)
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
121 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
122 if (!comparison.empty())
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
123 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
124 comparison += ", ";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
125 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
126
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
127 std::string parameter = formatter.GenerateParameter(constraint.GetValue(i));
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
128
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
129 if (constraint.IsCaseSensitive())
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
130 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
131 comparison += parameter;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
132 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
133 else
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
134 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
135 comparison += "lower(" + parameter + ")";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
136 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
137 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
138
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
139 if (constraint.IsCaseSensitive())
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
140 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
141 comparison = tag + ".value IN (" + comparison + ")";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
142 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
143 else
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
144 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
145 comparison = "lower(" + tag + ".value) IN (" + comparison + ")";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
146 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
147
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
148 break;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
149 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
150
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
151 case ConstraintType_Wildcard:
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
152 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
153 const std::string value = constraint.GetSingleValue();
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
154
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
155 if (value == "*")
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
156 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
157 if (!constraint.IsMandatory())
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
158 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
159 // Universal constraint on an optional tag, ignore it
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
160 return false;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
161 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
162 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
163 else
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
164 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
165 std::string escaped;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
166 escaped.reserve(value.size());
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
167
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
168 for (size_t i = 0; i < value.size(); i++)
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
169 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
170 if (value[i] == '*')
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
171 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
172 escaped += "%";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
173 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
174 else if (value[i] == '?')
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
175 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
176 escaped += "_";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
177 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
178 else if (value[i] == '%')
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
179 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
180 escaped += "\\%";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
181 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
182 else if (value[i] == '_')
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
183 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
184 escaped += "\\_";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
185 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
186 else if (value[i] == '\\')
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
187 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
188 escaped += "\\\\";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
189 }
354
2a3bbb4104fa fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 353
diff changeset
190 else if (escapeBrackets && value[i] == '[')
2a3bbb4104fa fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 353
diff changeset
191 {
2a3bbb4104fa fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 353
diff changeset
192 escaped += "\\[";
2a3bbb4104fa fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 353
diff changeset
193 }
2a3bbb4104fa fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 353
diff changeset
194 else if (escapeBrackets && value[i] == ']')
2a3bbb4104fa fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 353
diff changeset
195 {
2a3bbb4104fa fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 353
diff changeset
196 escaped += "\\]";
2a3bbb4104fa fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 353
diff changeset
197 }
152
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
198 else
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
199 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
200 escaped += value[i];
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
201 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
202 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
203
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
204 std::string parameter = formatter.GenerateParameter(escaped);
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
205
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
206 if (constraint.IsCaseSensitive())
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
207 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
208 comparison = (tag + ".value LIKE " + parameter + " " +
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
209 formatter.FormatWildcardEscape());
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
210 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
211 else
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
212 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
213 comparison = ("lower(" + tag + ".value) LIKE lower(" +
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
214 parameter + ") " + formatter.FormatWildcardEscape());
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
215 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
216 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
217
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
218 break;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
219 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
220
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
221 default:
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
222 return false;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
223 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
224
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
225 if (constraint.IsMandatory())
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
226 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
227 target = comparison;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
228 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
229 else if (comparison.empty())
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
230 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
231 target = tag + ".value IS NULL";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
232 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
233 else
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
234 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
235 target = tag + ".value IS NULL OR " + comparison;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
236 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
237
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
238 return true;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
239 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
240
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
241
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
242 static void FormatJoin(std::string& target,
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
243 const DatabaseConstraint& constraint,
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
244 size_t index)
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
245 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
246 std::string tag = "t" + boost::lexical_cast<std::string>(index);
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
247
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
248 if (constraint.IsMandatory())
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
249 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
250 target = " INNER JOIN ";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
251 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
252 else
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
253 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
254 target = " LEFT JOIN ";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
255 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
256
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
257 if (constraint.IsIdentifier())
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
258 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
259 target += "DicomIdentifiers ";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
260 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
261 else
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
262 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
263 target += "MainDicomTags ";
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
264 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
265
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
266 target += (tag + " ON " + tag + ".id = " + FormatLevel(constraint.GetLevel()) +
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
267 ".internalId AND " + tag + ".tagGroup = " +
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
268 boost::lexical_cast<std::string>(constraint.GetTag().GetGroup()) +
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
269 " AND " + tag + ".tagElement = " +
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
270 boost::lexical_cast<std::string>(constraint.GetTag().GetElement()));
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
271 }
398
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
272
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
273
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
274 static std::string Join(const std::list<std::string>& values,
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
275 const std::string& prefix,
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
276 const std::string& separator)
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
277 {
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
278 if (values.empty())
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
279 {
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
280 return "";
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
281 }
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
282 else
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
283 {
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
284 std::string s = prefix;
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
285
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
286 bool first = true;
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
287 for (std::list<std::string>::const_iterator it = values.begin(); it != values.end(); ++it)
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
288 {
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
289 if (first)
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
290 {
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
291 first = false;
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
292 }
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
293 else
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
294 {
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
295 s += separator;
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
296 }
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
297
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
298 s += *it;
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
299 }
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
300
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
301 return s;
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
302 }
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
303 }
152
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
304
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
305
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
306 void ISqlLookupFormatter::Apply(std::string& sql,
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
307 ISqlLookupFormatter& formatter,
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
308 const std::vector<DatabaseConstraint>& lookup,
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
309 ResourceType queryLevel,
398
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
310 const std::set<std::string>& withLabels,
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
311 const std::set<std::string>& withoutLabels,
152
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
312 size_t limit)
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
313 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
314 assert(ResourceType_Patient < ResourceType_Study &&
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
315 ResourceType_Study < ResourceType_Series &&
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
316 ResourceType_Series < ResourceType_Instance);
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
317
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
318 ResourceType upperLevel = queryLevel;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
319 ResourceType lowerLevel = queryLevel;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
320
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
321 for (size_t i = 0; i < lookup.size(); i++)
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
322 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
323 ResourceType level = lookup[i].GetLevel();
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
324
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
325 if (level < upperLevel)
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
326 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
327 upperLevel = level;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
328 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
329
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
330 if (level > lowerLevel)
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
331 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
332 lowerLevel = level;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
333 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
334 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
335
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
336 assert(upperLevel <= queryLevel &&
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
337 queryLevel <= lowerLevel);
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
338
354
2a3bbb4104fa fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 353
diff changeset
339 const bool escapeBrackets = formatter.IsEscapeBrackets();
2a3bbb4104fa fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 353
diff changeset
340
152
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
341 std::string joins, comparisons;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
342
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
343 size_t count = 0;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
344
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
345 for (size_t i = 0; i < lookup.size(); i++)
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
346 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
347 std::string comparison;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
348
354
2a3bbb4104fa fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 353
diff changeset
349 if (FormatComparison(comparison, formatter, lookup[i], count, escapeBrackets))
152
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
350 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
351 std::string join;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
352 FormatJoin(join, lookup[i], count);
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
353 joins += join;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
354
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
355 if (!comparison.empty())
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
356 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
357 comparisons += " AND " + comparison;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
358 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
359
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
360 count ++;
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
361 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
362 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
363
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
364 sql = ("SELECT " +
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
365 FormatLevel(queryLevel) + ".publicId, " +
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
366 FormatLevel(queryLevel) + ".internalId" +
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
367 " FROM Resources AS " + FormatLevel(queryLevel));
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
368
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
369 for (int level = queryLevel - 1; level >= upperLevel; level--)
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
370 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
371 sql += (" INNER JOIN Resources " +
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
372 FormatLevel(static_cast<ResourceType>(level)) + " ON " +
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
373 FormatLevel(static_cast<ResourceType>(level)) + ".internalId=" +
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
374 FormatLevel(static_cast<ResourceType>(level + 1)) + ".parentId");
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
375 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
376
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
377 for (int level = queryLevel + 1; level <= lowerLevel; level++)
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
378 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
379 sql += (" INNER JOIN Resources " +
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
380 FormatLevel(static_cast<ResourceType>(level)) + " ON " +
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
381 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" +
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
382 FormatLevel(static_cast<ResourceType>(level)) + ".parentId");
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
383 }
398
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
384
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
385 std::list<std::string> where;
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
386
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
387 if (!withLabels.empty())
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
388 {
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
389 std::list<std::string> labels;
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
390 for (std::set<std::string>::const_iterator it = withLabels.begin(); it != withLabels.end(); ++it)
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
391 {
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
392 labels.push_back(formatter.GenerateParameter(*it));
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
393 }
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
394
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
395 where.push_back(boost::lexical_cast<std::string>(withLabels.size()) +
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
396 " = (SELECT COUNT(1) FROM Labels WHERE internalId = " + FormatLevel(queryLevel) +
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
397 ".internalId AND label IN (" + Join(labels, "", ", ") + "))");
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
398 }
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
399
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
400 if (!withoutLabels.empty())
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
401 {
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
402 /**
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
403 * "In SQL Server, NOT EXISTS and NOT IN predicates are the best
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
404 * way to search for missing values, as long as both columns in
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
405 * question are NOT NULL."
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
406 * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
407 **/
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
408 std::list<std::string> labels;
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
409 for (std::set<std::string>::const_iterator it = withoutLabels.begin(); it != withoutLabels.end(); ++it)
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
410 {
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
411 labels.push_back(formatter.GenerateParameter(*it));
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
412 }
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
413
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
414 where.push_back("NOT EXISTS (SELECT 1 FROM Labels WHERE internalId = " + FormatLevel(queryLevel) +
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
415 ".internalId AND label IN (" + Join(labels, "", ", ") + "))");
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
416 }
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
417
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
418 where.push_back(FormatLevel(queryLevel) + ".resourceType = " +
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
419 formatter.FormatResourceType(queryLevel) + comparisons);
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
420
8dedfd982b83 implemented lookup for labels in postgresql
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 397
diff changeset
421 sql += joins + Join(where, " WHERE ", " AND ");
152
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
422
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
423 if (limit != 0)
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
424 {
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
425 sql += " LIMIT " + boost::lexical_cast<std::string>(limit);
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
426 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
427 }
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
428 }