Mercurial > hg > orthanc-databases
annotate Resources/Orthanc/Databases/ISqlLookupFormatter.cpp @ 354:2a3bbb4104fa
fix changeset 389c037387ea
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Thu, 25 Nov 2021 13:17:51 +0100 |
parents | 389c037387ea |
children | 1280b40d6696 |
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 | |
193
3236894320d6
upgrade to year 2021
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
170
diff
changeset
|
5 * Copyright (C) 2017-2021 Osimis S.A., Belgium |
152 | 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 * This program is distributed in the hope that it will be useful, but | |
13 * WITHOUT ANY WARRANTY; without even the implied warranty of | |
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU | |
15 * General Public License for more details. | |
16 * | |
17 * You should have received a copy of the GNU General Public License | |
18 * along with this program. If not, see <http://www.gnu.org/licenses/>. | |
19 **/ | |
20 | |
21 | |
22 #if !defined(ORTHANC_BUILDING_SERVER_LIBRARY) | |
23 # error Macro ORTHANC_BUILDING_SERVER_LIBRARY must be defined | |
24 #endif | |
25 | |
26 #if ORTHANC_BUILDING_SERVER_LIBRARY == 1 | |
27 # include "../PrecompiledHeadersServer.h" | |
28 #endif | |
29 | |
30 #include "ISqlLookupFormatter.h" | |
31 | |
32 #if ORTHANC_BUILDING_SERVER_LIBRARY == 1 | |
33 # include "../../../OrthancFramework/Sources/OrthancException.h" | |
34 #else | |
35 # include <OrthancException.h> | |
36 #endif | |
37 | |
38 #include "DatabaseConstraint.h" | |
39 | |
170 | 40 #include <boost/lexical_cast.hpp> |
41 | |
42 | |
152 | 43 namespace Orthanc |
44 { | |
45 static std::string FormatLevel(ResourceType level) | |
46 { | |
47 switch (level) | |
48 { | |
49 case ResourceType_Patient: | |
50 return "patients"; | |
51 | |
52 case ResourceType_Study: | |
53 return "studies"; | |
54 | |
55 case ResourceType_Series: | |
56 return "series"; | |
57 | |
58 case ResourceType_Instance: | |
59 return "instances"; | |
60 | |
61 default: | |
62 throw OrthancException(ErrorCode_InternalError); | |
63 } | |
64 } | |
65 | |
66 | |
67 static bool FormatComparison(std::string& target, | |
68 ISqlLookupFormatter& formatter, | |
69 const DatabaseConstraint& constraint, | |
354
2a3bbb4104fa
fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
353
diff
changeset
|
70 size_t index, |
2a3bbb4104fa
fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
353
diff
changeset
|
71 bool escapeBrackets) |
152 | 72 { |
73 std::string tag = "t" + boost::lexical_cast<std::string>(index); | |
74 | |
75 std::string comparison; | |
76 | |
77 switch (constraint.GetConstraintType()) | |
78 { | |
79 case ConstraintType_Equal: | |
80 case ConstraintType_SmallerOrEqual: | |
81 case ConstraintType_GreaterOrEqual: | |
82 { | |
83 std::string op; | |
84 switch (constraint.GetConstraintType()) | |
85 { | |
86 case ConstraintType_Equal: | |
87 op = "="; | |
88 break; | |
89 | |
90 case ConstraintType_SmallerOrEqual: | |
91 op = "<="; | |
92 break; | |
93 | |
94 case ConstraintType_GreaterOrEqual: | |
95 op = ">="; | |
96 break; | |
97 | |
98 default: | |
99 throw OrthancException(ErrorCode_InternalError); | |
100 } | |
101 | |
102 std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue()); | |
103 | |
104 if (constraint.IsCaseSensitive()) | |
105 { | |
106 comparison = tag + ".value " + op + " " + parameter; | |
107 } | |
108 else | |
109 { | |
110 comparison = "lower(" + tag + ".value) " + op + " lower(" + parameter + ")"; | |
111 } | |
112 | |
113 break; | |
114 } | |
115 | |
116 case ConstraintType_List: | |
117 { | |
118 for (size_t i = 0; i < constraint.GetValuesCount(); i++) | |
119 { | |
120 if (!comparison.empty()) | |
121 { | |
122 comparison += ", "; | |
123 } | |
124 | |
125 std::string parameter = formatter.GenerateParameter(constraint.GetValue(i)); | |
126 | |
127 if (constraint.IsCaseSensitive()) | |
128 { | |
129 comparison += parameter; | |
130 } | |
131 else | |
132 { | |
133 comparison += "lower(" + parameter + ")"; | |
134 } | |
135 } | |
136 | |
137 if (constraint.IsCaseSensitive()) | |
138 { | |
139 comparison = tag + ".value IN (" + comparison + ")"; | |
140 } | |
141 else | |
142 { | |
143 comparison = "lower(" + tag + ".value) IN (" + comparison + ")"; | |
144 } | |
145 | |
146 break; | |
147 } | |
148 | |
149 case ConstraintType_Wildcard: | |
150 { | |
151 const std::string value = constraint.GetSingleValue(); | |
152 | |
153 if (value == "*") | |
154 { | |
155 if (!constraint.IsMandatory()) | |
156 { | |
157 // Universal constraint on an optional tag, ignore it | |
158 return false; | |
159 } | |
160 } | |
161 else | |
162 { | |
163 std::string escaped; | |
164 escaped.reserve(value.size()); | |
165 | |
166 for (size_t i = 0; i < value.size(); i++) | |
167 { | |
168 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 if (value[i] == '\\') | |
185 { | |
186 escaped += "\\\\"; | |
187 } | |
354
2a3bbb4104fa
fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
353
diff
changeset
|
188 else if (escapeBrackets && value[i] == '[') |
2a3bbb4104fa
fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
353
diff
changeset
|
189 { |
2a3bbb4104fa
fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
353
diff
changeset
|
190 escaped += "\\["; |
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 else if (escapeBrackets && value[i] == ']') |
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 escaped += "\\]"; |
2a3bbb4104fa
fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
353
diff
changeset
|
195 } |
152 | 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 | |
354
2a3bbb4104fa
fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
353
diff
changeset
|
303 const bool escapeBrackets = formatter.IsEscapeBrackets(); |
2a3bbb4104fa
fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
353
diff
changeset
|
304 |
152 | 305 std::string joins, comparisons; |
306 | |
307 size_t count = 0; | |
308 | |
309 for (size_t i = 0; i < lookup.size(); i++) | |
310 { | |
311 std::string comparison; | |
312 | |
354
2a3bbb4104fa
fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
353
diff
changeset
|
313 if (FormatComparison(comparison, formatter, lookup[i], count, escapeBrackets)) |
152 | 314 { |
315 std::string join; | |
316 FormatJoin(join, lookup[i], count); | |
317 joins += join; | |
318 | |
319 if (!comparison.empty()) | |
320 { | |
321 comparisons += " AND " + comparison; | |
322 } | |
323 | |
324 count ++; | |
325 } | |
326 } | |
327 | |
328 sql = ("SELECT " + | |
329 FormatLevel(queryLevel) + ".publicId, " + | |
330 FormatLevel(queryLevel) + ".internalId" + | |
331 " FROM Resources AS " + FormatLevel(queryLevel)); | |
332 | |
333 for (int level = queryLevel - 1; level >= upperLevel; level--) | |
334 { | |
335 sql += (" INNER JOIN Resources " + | |
336 FormatLevel(static_cast<ResourceType>(level)) + " ON " + | |
337 FormatLevel(static_cast<ResourceType>(level)) + ".internalId=" + | |
338 FormatLevel(static_cast<ResourceType>(level + 1)) + ".parentId"); | |
339 } | |
340 | |
341 for (int level = queryLevel + 1; level <= lowerLevel; level++) | |
342 { | |
343 sql += (" INNER JOIN Resources " + | |
344 FormatLevel(static_cast<ResourceType>(level)) + " ON " + | |
345 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" + | |
346 FormatLevel(static_cast<ResourceType>(level)) + ".parentId"); | |
347 } | |
348 | |
349 sql += (joins + " WHERE " + FormatLevel(queryLevel) + ".resourceType = " + | |
350 formatter.FormatResourceType(queryLevel) + comparisons); | |
351 | |
352 if (limit != 0) | |
353 { | |
354 sql += " LIMIT " + boost::lexical_cast<std::string>(limit); | |
355 } | |
356 } | |
357 } |