comparison OrthancServer/Sources/Search/ISqlLookupFormatter.cpp @ 4092:fb64d481940a

making the "framework" branch the new "default"
author Sebastien Jodogne <s.jodogne@gmail.com>
date Tue, 30 Jun 2020 15:53:17 +0200
parents 05b8fd21089c
children d633e5bb7ba3
comparison
equal deleted inserted replaced
4089:a2060a76ed6a 4092:fb64d481940a
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 #include "../PrecompiledHeadersServer.h"
35 #include "ISqlLookupFormatter.h"
36
37 #include "../../../OrthancFramework/Sources/OrthancException.h"
38 #include "DatabaseConstraint.h"
39
40 namespace Orthanc
41 {
42 static std::string FormatLevel(ResourceType level)
43 {
44 switch (level)
45 {
46 case ResourceType_Patient:
47 return "patients";
48
49 case ResourceType_Study:
50 return "studies";
51
52 case ResourceType_Series:
53 return "series";
54
55 case ResourceType_Instance:
56 return "instances";
57
58 default:
59 throw OrthancException(ErrorCode_InternalError);
60 }
61 }
62
63
64 static bool FormatComparison(std::string& target,
65 ISqlLookupFormatter& formatter,
66 const DatabaseConstraint& constraint,
67 size_t index)
68 {
69 std::string tag = "t" + boost::lexical_cast<std::string>(index);
70
71 std::string comparison;
72
73 switch (constraint.GetConstraintType())
74 {
75 case ConstraintType_Equal:
76 case ConstraintType_SmallerOrEqual:
77 case ConstraintType_GreaterOrEqual:
78 {
79 std::string op;
80 switch (constraint.GetConstraintType())
81 {
82 case ConstraintType_Equal:
83 op = "=";
84 break;
85
86 case ConstraintType_SmallerOrEqual:
87 op = "<=";
88 break;
89
90 case ConstraintType_GreaterOrEqual:
91 op = ">=";
92 break;
93
94 default:
95 throw OrthancException(ErrorCode_InternalError);
96 }
97
98 std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue());
99
100 if (constraint.IsCaseSensitive())
101 {
102 comparison = tag + ".value " + op + " " + parameter;
103 }
104 else
105 {
106 comparison = "lower(" + tag + ".value) " + op + " lower(" + parameter + ")";
107 }
108
109 break;
110 }
111
112 case ConstraintType_List:
113 {
114 for (size_t i = 0; i < constraint.GetValuesCount(); i++)
115 {
116 if (!comparison.empty())
117 {
118 comparison += ", ";
119 }
120
121 std::string parameter = formatter.GenerateParameter(constraint.GetValue(i));
122
123 if (constraint.IsCaseSensitive())
124 {
125 comparison += parameter;
126 }
127 else
128 {
129 comparison += "lower(" + parameter + ")";
130 }
131 }
132
133 if (constraint.IsCaseSensitive())
134 {
135 comparison = tag + ".value IN (" + comparison + ")";
136 }
137 else
138 {
139 comparison = "lower(" + tag + ".value) IN (" + comparison + ")";
140 }
141
142 break;
143 }
144
145 case ConstraintType_Wildcard:
146 {
147 const std::string value = constraint.GetSingleValue();
148
149 if (value == "*")
150 {
151 if (!constraint.IsMandatory())
152 {
153 // Universal constraint on an optional tag, ignore it
154 return false;
155 }
156 }
157 else
158 {
159 std::string escaped;
160 escaped.reserve(value.size());
161
162 for (size_t i = 0; i < value.size(); i++)
163 {
164 if (value[i] == '*')
165 {
166 escaped += "%";
167 }
168 else if (value[i] == '?')
169 {
170 escaped += "_";
171 }
172 else if (value[i] == '%')
173 {
174 escaped += "\\%";
175 }
176 else if (value[i] == '_')
177 {
178 escaped += "\\_";
179 }
180 else if (value[i] == '\\')
181 {
182 escaped += "\\\\";
183 }
184 else
185 {
186 escaped += value[i];
187 }
188 }
189
190 std::string parameter = formatter.GenerateParameter(escaped);
191
192 if (constraint.IsCaseSensitive())
193 {
194 comparison = (tag + ".value LIKE " + parameter + " " +
195 formatter.FormatWildcardEscape());
196 }
197 else
198 {
199 comparison = ("lower(" + tag + ".value) LIKE lower(" +
200 parameter + ") " + formatter.FormatWildcardEscape());
201 }
202 }
203
204 break;
205 }
206
207 default:
208 return false;
209 }
210
211 if (constraint.IsMandatory())
212 {
213 target = comparison;
214 }
215 else if (comparison.empty())
216 {
217 target = tag + ".value IS NULL";
218 }
219 else
220 {
221 target = tag + ".value IS NULL OR " + comparison;
222 }
223
224 return true;
225 }
226
227
228 static void FormatJoin(std::string& target,
229 const DatabaseConstraint& constraint,
230 size_t index)
231 {
232 std::string tag = "t" + boost::lexical_cast<std::string>(index);
233
234 if (constraint.IsMandatory())
235 {
236 target = " INNER JOIN ";
237 }
238 else
239 {
240 target = " LEFT JOIN ";
241 }
242
243 if (constraint.IsIdentifier())
244 {
245 target += "DicomIdentifiers ";
246 }
247 else
248 {
249 target += "MainDicomTags ";
250 }
251
252 target += (tag + " ON " + tag + ".id = " + FormatLevel(constraint.GetLevel()) +
253 ".internalId AND " + tag + ".tagGroup = " +
254 boost::lexical_cast<std::string>(constraint.GetTag().GetGroup()) +
255 " AND " + tag + ".tagElement = " +
256 boost::lexical_cast<std::string>(constraint.GetTag().GetElement()));
257 }
258
259
260 void ISqlLookupFormatter::Apply(std::string& sql,
261 ISqlLookupFormatter& formatter,
262 const std::vector<DatabaseConstraint>& lookup,
263 ResourceType queryLevel,
264 size_t limit)
265 {
266 assert(ResourceType_Patient < ResourceType_Study &&
267 ResourceType_Study < ResourceType_Series &&
268 ResourceType_Series < ResourceType_Instance);
269
270 ResourceType upperLevel = queryLevel;
271 ResourceType lowerLevel = queryLevel;
272
273 for (size_t i = 0; i < lookup.size(); i++)
274 {
275 ResourceType level = lookup[i].GetLevel();
276
277 if (level < upperLevel)
278 {
279 upperLevel = level;
280 }
281
282 if (level > lowerLevel)
283 {
284 lowerLevel = level;
285 }
286 }
287
288 assert(upperLevel <= queryLevel &&
289 queryLevel <= lowerLevel);
290
291 std::string joins, comparisons;
292
293 size_t count = 0;
294
295 for (size_t i = 0; i < lookup.size(); i++)
296 {
297 std::string comparison;
298
299 if (FormatComparison(comparison, formatter, lookup[i], count))
300 {
301 std::string join;
302 FormatJoin(join, lookup[i], count);
303 joins += join;
304
305 if (!comparison.empty())
306 {
307 comparisons += " AND " + comparison;
308 }
309
310 count ++;
311 }
312 }
313
314 sql = ("SELECT " +
315 FormatLevel(queryLevel) + ".publicId, " +
316 FormatLevel(queryLevel) + ".internalId" +
317 " FROM Resources AS " + FormatLevel(queryLevel));
318
319 for (int level = queryLevel - 1; level >= upperLevel; level--)
320 {
321 sql += (" INNER JOIN Resources " +
322 FormatLevel(static_cast<ResourceType>(level)) + " ON " +
323 FormatLevel(static_cast<ResourceType>(level)) + ".internalId=" +
324 FormatLevel(static_cast<ResourceType>(level + 1)) + ".parentId");
325 }
326
327 for (int level = queryLevel + 1; level <= lowerLevel; level++)
328 {
329 sql += (" INNER JOIN Resources " +
330 FormatLevel(static_cast<ResourceType>(level)) + " ON " +
331 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" +
332 FormatLevel(static_cast<ResourceType>(level)) + ".parentId");
333 }
334
335 sql += (joins + " WHERE " + FormatLevel(queryLevel) + ".resourceType = " +
336 formatter.FormatResourceType(queryLevel) + comparisons);
337
338 if (limit != 0)
339 {
340 sql += " LIMIT " + boost::lexical_cast<std::string>(limit);
341 }
342 }
343 }