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