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