Mercurial > hg > orthanc-databases

comparison Resources/Orthanc/Databases/ISqlLookupFormatter.cpp @ 152:063aa53b5917

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
sync
author Sebastien Jodogne <s.jodogne@gmail.com>
date Wed, 01 Jul 2020 08:54:32 +0200
parents
children e712ff3eede3
comparison
equal deleted inserted replaced
150:d9101318442d 152:063aa53b5917
1 /**
2 * Orthanc - A Lightweight, RESTful DICOM Store
3 * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
4 * Department, University Hospital of Liege, Belgium
5 * Copyright (C) 2017-2020 Osimis S.A., Belgium
6 *
7 * This program is free software: you can redistribute it and/or
8 * modify it under the terms of the GNU General Public License as
9 * published by the Free Software Foundation, either version 3 of the
10 * License, or (at your option) any later version.
11 *
12 * In addition, as a special exception, the copyright holders of this
13 * program give permission to link the code of its release with the
14 * OpenSSL project's "OpenSSL" library (or with modified versions of it
15 * that use the same license as the "OpenSSL" library), and distribute
16 * the linked executables. You must obey the GNU General Public License
17 * in all respects for all of the code used other than "OpenSSL". If you
18 * modify file(s) with this exception, you may extend this exception to
19 * your version of the file(s), but you are not obligated to do so. If
20 * you do not wish to do so, delete this exception statement from your
21 * version. If you delete this exception statement from all source files
22 * in the program, then also delete it here.
23 *
24 * This program is distributed in the hope that it will be useful, but
25 * WITHOUT ANY WARRANTY; without even the implied warranty of
26 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
27 * General Public License for more details.
28 *
29 * You should have received a copy of the GNU General Public License
30 * along with this program. If not, see <http://www.gnu.org/licenses/>.
31 **/
32
33
34 #if !defined(ORTHANC_BUILDING_SERVER_LIBRARY)
35 # error Macro ORTHANC_BUILDING_SERVER_LIBRARY must be defined
36 #endif
37
38 #if ORTHANC_BUILDING_SERVER_LIBRARY == 1
39 # include "../PrecompiledHeadersServer.h"
40 #endif
41
42 #include "ISqlLookupFormatter.h"
43
44 #if ORTHANC_BUILDING_SERVER_LIBRARY == 1
45 # include "../../../OrthancFramework/Sources/OrthancException.h"
46 #else
47 # include <OrthancException.h>
48 #endif
49
50 #include "DatabaseConstraint.h"
51
52 namespace Orthanc
53 {
54 static std::string FormatLevel(ResourceType level)
55 {
56 switch (level)
57 {
58 case ResourceType_Patient:
59 return "patients";
60
61 case ResourceType_Study:
62 return "studies";
63
64 case ResourceType_Series:
65 return "series";
66
67 case ResourceType_Instance:
68 return "instances";
69
70 default:
71 throw OrthancException(ErrorCode_InternalError);
72 }
73 }
74
75
76 static bool FormatComparison(std::string& target,
77 ISqlLookupFormatter& formatter,
78 const DatabaseConstraint& constraint,
79 size_t index)
80 {
81 std::string tag = "t" + boost::lexical_cast<std::string>(index);
82
83 std::string comparison;
84
85 switch (constraint.GetConstraintType())
86 {
87 case ConstraintType_Equal:
88 case ConstraintType_SmallerOrEqual:
89 case ConstraintType_GreaterOrEqual:
90 {
91 std::string op;
92 switch (constraint.GetConstraintType())
93 {
94 case ConstraintType_Equal:
95 op = "=";
96 break;
97
98 case ConstraintType_SmallerOrEqual:
99 op = "<=";
100 break;
101
102 case ConstraintType_GreaterOrEqual:
103 op = ">=";
104 break;
105
106 default:
107 throw OrthancException(ErrorCode_InternalError);
108 }
109
110 std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue());
111
112 if (constraint.IsCaseSensitive())
113 {
114 comparison = tag + ".value " + op + " " + parameter;
115 }
116 else
117 {
118 comparison = "lower(" + tag + ".value) " + op + " lower(" + parameter + ")";
119 }
120
121 break;
122 }
123
124 case ConstraintType_List:
125 {
126 for (size_t i = 0; i < constraint.GetValuesCount(); i++)
127 {
128 if (!comparison.empty())
129 {
130 comparison += ", ";
131 }
132
133 std::string parameter = formatter.GenerateParameter(constraint.GetValue(i));
134
135 if (constraint.IsCaseSensitive())
136 {
137 comparison += parameter;
138 }
139 else
140 {
141 comparison += "lower(" + parameter + ")";
142 }
143 }
144
145 if (constraint.IsCaseSensitive())
146 {
147 comparison = tag + ".value IN (" + comparison + ")";
148 }
149 else
150 {
151 comparison = "lower(" + tag + ".value) IN (" + comparison + ")";
152 }
153
154 break;
155 }
156
157 case ConstraintType_Wildcard:
158 {
159 const std::string value = constraint.GetSingleValue();
160
161 if (value == "*")
162 {
163 if (!constraint.IsMandatory())
164 {
165 // Universal constraint on an optional tag, ignore it
166 return false;
167 }
168 }
169 else
170 {
171 std::string escaped;
172 escaped.reserve(value.size());
173
174 for (size_t i = 0; i < value.size(); i++)
175 {
176 if (value[i] == '*')
177 {
178 escaped += "%";
179 }
180 else if (value[i] == '?')
181 {
182 escaped += "_";
183 }
184 else if (value[i] == '%')
185 {
186 escaped += "\\%";
187 }
188 else if (value[i] == '_')
189 {
190 escaped += "\\_";
191 }
192 else if (value[i] == '\\')
193 {
194 escaped += "\\\\";
195 }
196 else
197 {
198 escaped += value[i];
199 }
200 }
201
202 std::string parameter = formatter.GenerateParameter(escaped);
203
204 if (constraint.IsCaseSensitive())
205 {
206 comparison = (tag + ".value LIKE " + parameter + " " +
207 formatter.FormatWildcardEscape());
208 }
209 else
210 {
211 comparison = ("lower(" + tag + ".value) LIKE lower(" +
212 parameter + ") " + formatter.FormatWildcardEscape());
213 }
214 }
215
216 break;
217 }
218
219 default:
220 return false;
221 }
222
223 if (constraint.IsMandatory())
224 {
225 target = comparison;
226 }
227 else if (comparison.empty())
228 {
229 target = tag + ".value IS NULL";
230 }
231 else
232 {
233 target = tag + ".value IS NULL OR " + comparison;
234 }
235
236 return true;
237 }
238
239
240 static void FormatJoin(std::string& target,
241 const DatabaseConstraint& constraint,
242 size_t index)
243 {
244 std::string tag = "t" + boost::lexical_cast<std::string>(index);
245
246 if (constraint.IsMandatory())
247 {
248 target = " INNER JOIN ";
249 }
250 else
251 {
252 target = " LEFT JOIN ";
253 }
254
255 if (constraint.IsIdentifier())
256 {
257 target += "DicomIdentifiers ";
258 }
259 else
260 {
261 target += "MainDicomTags ";
262 }
263
264 target += (tag + " ON " + tag + ".id = " + FormatLevel(constraint.GetLevel()) +
265 ".internalId AND " + tag + ".tagGroup = " +
266 boost::lexical_cast<std::string>(constraint.GetTag().GetGroup()) +
267 " AND " + tag + ".tagElement = " +
268 boost::lexical_cast<std::string>(constraint.GetTag().GetElement()));
269 }
270
271
272 void ISqlLookupFormatter::Apply(std::string& sql,
273 ISqlLookupFormatter& formatter,
274 const std::vector<DatabaseConstraint>& lookup,
275 ResourceType queryLevel,
276 size_t limit)
277 {
278 assert(ResourceType_Patient < ResourceType_Study &&
279 ResourceType_Study < ResourceType_Series &&
280 ResourceType_Series < ResourceType_Instance);
281
282 ResourceType upperLevel = queryLevel;
283 ResourceType lowerLevel = queryLevel;
284
285 for (size_t i = 0; i < lookup.size(); i++)
286 {
287 ResourceType level = lookup[i].GetLevel();
288
289 if (level < upperLevel)
290 {
291 upperLevel = level;
292 }
293
294 if (level > lowerLevel)
295 {
296 lowerLevel = level;
297 }
298 }
299
300 assert(upperLevel <= queryLevel &&
301 queryLevel <= lowerLevel);
302
303 std::string joins, comparisons;
304
305 size_t count = 0;
306
307 for (size_t i = 0; i < lookup.size(); i++)
308 {
309 std::string comparison;
310
311 if (FormatComparison(comparison, formatter, lookup[i], count))
312 {
313 std::string join;
314 FormatJoin(join, lookup[i], count);
315 joins += join;
316
317 if (!comparison.empty())
318 {
319 comparisons += " AND " + comparison;
320 }
321
322 count ++;
323 }
324 }
325
326 sql = ("SELECT " +
327 FormatLevel(queryLevel) + ".publicId, " +
328 FormatLevel(queryLevel) + ".internalId" +
329 " FROM Resources AS " + FormatLevel(queryLevel));
330
331 for (int level = queryLevel - 1; level >= upperLevel; level--)
332 {
333 sql += (" INNER JOIN Resources " +
334 FormatLevel(static_cast<ResourceType>(level)) + " ON " +
335 FormatLevel(static_cast<ResourceType>(level)) + ".internalId=" +
336 FormatLevel(static_cast<ResourceType>(level + 1)) + ".parentId");
337 }
338
339 for (int level = queryLevel + 1; level <= lowerLevel; level++)
340 {
341 sql += (" INNER JOIN Resources " +
342 FormatLevel(static_cast<ResourceType>(level)) + " ON " +
343 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" +
344 FormatLevel(static_cast<ResourceType>(level)) + ".parentId");
345 }
346
347 sql += (joins + " WHERE " + FormatLevel(queryLevel) + ".resourceType = " +
348 formatter.FormatResourceType(queryLevel) + comparisons);
349
350 if (limit != 0)
351 {
352 sql += " LIMIT " + boost::lexical_cast<std::string>(limit);
353 }
354 }
355 }