Mercurial > hg > orthanc
comparison OrthancServer/Search/ISqlLookupFormatter.cpp @ 3058:6faf575ba9cc db-changes
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 } |