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