Mercurial > hg > orthanc

comparison OrthancServer/Search/ISqlLookupFormatter.cpp @ 3058:6faf575ba9cc db-changes

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