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