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 } |