# HG changeset patch # User Sebastien Jodogne # Date 1545408478 -3600 # Node ID 6faf575ba9cc709cf61ab5f00439eda0171633af # Parent 87f52703ebbc20efe038c133d3a0e1d0e7fc65a4 refactoring: class ISqlLookupFormatter to be used in orthanc-databases diff -r 87f52703ebbc -r 6faf575ba9cc CMakeLists.txt --- a/CMakeLists.txt Fri Dec 21 14:12:14 2018 +0100 +++ b/CMakeLists.txt Fri Dec 21 17:07:58 2018 +0100 @@ -78,6 +78,7 @@ OrthancServer/Search/DatabaseLookup.cpp OrthancServer/Search/DicomTagConstraint.cpp OrthancServer/Search/HierarchicalMatcher.cpp + OrthancServer/Search/ISqlLookupFormatter.cpp OrthancServer/Search/IFindConstraint.cpp OrthancServer/Search/ListConstraint.cpp OrthancServer/Search/RangeConstraint.cpp diff -r 87f52703ebbc -r 6faf575ba9cc OrthancServer/SQLiteDatabaseWrapper.cpp --- a/OrthancServer/SQLiteDatabaseWrapper.cpp Fri Dec 21 14:12:14 2018 +0100 +++ b/OrthancServer/SQLiteDatabaseWrapper.cpp Fri Dec 21 17:07:58 2018 +0100 @@ -38,6 +38,7 @@ #include "../Core/Logging.h" #include "../Core/SQLite/Transaction.h" #include "EmbeddedResources.h" +#include "Search/ISqlLookupFormatter.h" #include "ServerToolbox.h" #include @@ -1118,223 +1119,32 @@ } - static std::string FormatLevel(ResourceType level) - { - switch (level) - { - case ResourceType_Patient: - return "patients"; - - case ResourceType_Study: - return "studies"; - - case ResourceType_Series: - return "series"; - - case ResourceType_Instance: - return "instances"; - default: - throw OrthancException(ErrorCode_InternalError); - } - } - - - static bool FormatComparison(std::string& target, - const DatabaseConstraint& constraint, - size_t index, - std::vector& parameters) + class SQLiteDatabaseWrapper::LookupFormatter : public ISqlLookupFormatter { - std::string tag = "t" + boost::lexical_cast(index); - - std::string comparison; - - switch (constraint.GetConstraintType()) - { - case ConstraintType_Equal: - case ConstraintType_SmallerOrEqual: - case ConstraintType_GreaterOrEqual: - { - std::string op; - switch (constraint.GetConstraintType()) - { - case ConstraintType_Equal: - op = "="; - break; - - case ConstraintType_SmallerOrEqual: - op = "<="; - break; - - case ConstraintType_GreaterOrEqual: - op = ">="; - break; - - default: - throw OrthancException(ErrorCode_InternalError); - } - - parameters.push_back(constraint.GetSingleValue()); - - if (constraint.IsCaseSensitive()) - { - comparison = tag + ".value " + op + " ?"; - } - else - { - comparison = "lower(" + tag + ".value) " + op + " lower(?)"; - } - - break; - } - - case ConstraintType_List: - { - for (size_t i = 0; i < constraint.GetValuesCount(); i++) - { - parameters.push_back(constraint.GetValue(i)); - - if (!comparison.empty()) - { - comparison += ", "; - } - - if (constraint.IsCaseSensitive()) - { - comparison += "?"; - } - else - { - comparison += "lower(?)"; - } - } - - if (constraint.IsCaseSensitive()) - { - comparison = tag + ".value IN (" + comparison + ")"; - } - else - { - comparison = "lower(" + tag + ".value) IN (" + comparison + ")"; - } - - break; - } - - case ConstraintType_Wildcard: - { - const std::string value = constraint.GetSingleValue(); + private: + std::list values_; - if (value == "*") - { - if (!constraint.IsMandatory()) - { - // Universal constraint on an optional tag, ignore it - return false; - } - } - else - { - std::string escaped; - escaped.reserve(value.size()); - - for (size_t i = 0; i < value.size(); i++) - { - if (value[i] == '*') - { - escaped += "%"; - } - else if (value[i] == '?') - { - escaped += "_"; - } - else if (value[i] == '%') - { - escaped += "\\%"; - } - else if (value[i] == '_') - { - escaped += "\\_"; - } - else if (value[i] == '\\') - { - escaped += "\\\\"; - } - else - { - escaped += value[i]; - } - } - - parameters.push_back(escaped); - - if (constraint.IsCaseSensitive()) - { - comparison = tag + ".value LIKE ? ESCAPE '\\'"; - } - else - { - comparison = "lower(" + tag + ".value) LIKE lower(?) ESCAPE '\\'"; - } - } - - break; + public: + virtual std::string GenerateParameter(const std::string& value) + { + values_.push_back(value); + return "?"; + } + + void Bind(SQLite::Statement& statement) const + { + size_t pos = 0; + + for (std::list::const_iterator + it = values_.begin(); it != values_.end(); ++it, pos++) + { + statement.BindString(pos, *it); } - - default: - // Don't modify "parameters" in this case! - return false; } - - if (constraint.IsMandatory()) - { - target = comparison; - } - else if (comparison.empty()) - { - target = tag + ".value IS NULL"; - } - else - { - target = tag + ".value IS NULL OR " + comparison; - } - - return true; - } - + }; - static void FormatJoin(std::string& target, - const DatabaseConstraint& constraint, - size_t index) - { - std::string tag = "t" + boost::lexical_cast(index); - - if (constraint.IsMandatory()) - { - target = " INNER JOIN "; - } - else - { - target = " LEFT JOIN "; - } - - if (constraint.IsIdentifier()) - { - target += "DicomIdentifiers "; - } - else - { - target += "MainDicomTags "; - } - - target += (tag + " ON " + tag + ".id = " + FormatLevel(constraint.GetLevel()) + - ".internalId AND " + tag + ".tagGroup = " + - boost::lexical_cast(constraint.GetTag().GetGroup()) + - " AND " + tag + ".tagElement = " + - boost::lexical_cast(constraint.GetTag().GetElement())); - } - static void AnswerLookup(std::vector& resourcesId, std::vector& instancesId, SQLite::Connection& db, @@ -1415,107 +1225,24 @@ ResourceType queryLevel, size_t limit) { - for (size_t i = 0; i < lookup.size(); i++) - { - std::cout << i << ": " << lookup[i].GetTag() << " - " << EnumerationToString(lookup[i].GetLevel()); - std::cout << std::endl; - } - - assert(ResourceType_Patient < ResourceType_Study && - ResourceType_Study < ResourceType_Series && - ResourceType_Series < ResourceType_Instance); - - ResourceType upperLevel = queryLevel; - ResourceType lowerLevel = queryLevel; + LookupFormatter formatter; - for (size_t i = 0; i < lookup.size(); i++) - { - ResourceType level = lookup[i].GetLevel(); - - if (level < upperLevel) - { - upperLevel = level; - } + std::string sql; + LookupFormatter::Apply(sql, formatter, lookup, queryLevel, limit); - if (level > lowerLevel) - { - lowerLevel = level; - } - } + sql = "CREATE TEMPORARY TABLE Lookup AS " + sql; - assert(upperLevel <= queryLevel && - queryLevel <= lowerLevel); - { SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup"); s.Run(); } - - std::string joins, comparisons; - std::vector parameters; - - size_t count = 0; - - for (size_t i = 0; i < lookup.size(); i++) - { - std::string comparison; - - if (FormatComparison(comparison, lookup[i], count, parameters)) - { - std::string join; - FormatJoin(join, lookup[i], count); - joins += join; - - if (!comparison.empty()) - { - comparisons += " AND " + comparison; - } - - count ++; - } - } { - std::string sql = ("CREATE TEMPORARY TABLE Lookup AS SELECT " + - FormatLevel(queryLevel) + ".publicId, " + - FormatLevel(queryLevel) + ".internalId" + - " FROM Resources AS " + FormatLevel(queryLevel)); - - for (int level = queryLevel - 1; level >= upperLevel; level--) - { - sql += (" INNER JOIN Resources " + - FormatLevel(static_cast(level)) + " ON " + - FormatLevel(static_cast(level)) + ".internalId=" + - FormatLevel(static_cast(level + 1)) + ".parentId"); - } - - for (int level = queryLevel + 1; level <= lowerLevel; level++) - { - sql += (" INNER JOIN Resources " + - FormatLevel(static_cast(level)) + " ON " + - FormatLevel(static_cast(level - 1)) + ".internalId=" + - FormatLevel(static_cast(level)) + ".parentId"); - } - - sql += (joins + " WHERE " + FormatLevel(queryLevel) + ".resourceType = " + - boost::lexical_cast(queryLevel) + comparisons); - - if (limit != 0) - { - sql += " LIMIT " + boost::lexical_cast(limit); - } - printf("[%s]\n", sql.c_str()); - SQLite::Statement s(db_, sql); - - for (size_t i = 0; i < parameters.size(); i++) - { - printf(" %lu = '%s'\n", i, parameters[i].c_str()); - s.BindString(i, parameters[i]); - } - - s.Run(); + SQLite::Statement statement(db_, sql); + formatter.Bind(statement); + statement.Run(); } if (instancesId != NULL) diff -r 87f52703ebbc -r 6faf575ba9cc OrthancServer/SQLiteDatabaseWrapper.h --- a/OrthancServer/SQLiteDatabaseWrapper.h Fri Dec 21 14:12:14 2018 +0100 +++ b/OrthancServer/SQLiteDatabaseWrapper.h Fri Dec 21 17:07:58 2018 +0100 @@ -53,6 +53,7 @@ { private: class Transaction; + class LookupFormatter; IDatabaseListener* listener_; SQLite::Connection db_; diff -r 87f52703ebbc -r 6faf575ba9cc OrthancServer/Search/ISqlLookupFormatter.cpp --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/OrthancServer/Search/ISqlLookupFormatter.cpp Fri Dec 21 17:07:58 2018 +0100 @@ -0,0 +1,346 @@ +/** + * Orthanc - A Lightweight, RESTful DICOM Store + * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics + * Department, University Hospital of Liege, Belgium + * Copyright (C) 2017-2018 Osimis S.A., Belgium + * + * This program is free software: you can redistribute it and/or + * modify it under the terms of the GNU General Public License as + * published by the Free Software Foundation, either version 3 of the + * License, or (at your option) any later version. + * + * In addition, as a special exception, the copyright holders of this + * program give permission to link the code of its release with the + * OpenSSL project's "OpenSSL" library (or with modified versions of it + * that use the same license as the "OpenSSL" library), and distribute + * the linked executables. You must obey the GNU General Public License + * in all respects for all of the code used other than "OpenSSL". If you + * modify file(s) with this exception, you may extend this exception to + * your version of the file(s), but you are not obligated to do so. If + * you do not wish to do so, delete this exception statement from your + * version. If you delete this exception statement from all source files + * in the program, then also delete it here. + * + * This program is distributed in the hope that it will be useful, but + * WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with this program. If not, see . + **/ + + +#include "../PrecompiledHeadersServer.h" +#include "ISqlLookupFormatter.h" + +#include "../../Core/OrthancException.h" + +namespace Orthanc +{ + static std::string FormatLevel(ResourceType level) + { + switch (level) + { + case ResourceType_Patient: + return "patients"; + + case ResourceType_Study: + return "studies"; + + case ResourceType_Series: + return "series"; + + case ResourceType_Instance: + return "instances"; + + default: + throw OrthancException(ErrorCode_InternalError); + } + } + + + static bool FormatComparison(std::string& target, + ISqlLookupFormatter& formatter, + const DatabaseConstraint& constraint, + size_t index) + { + std::string tag = "t" + boost::lexical_cast(index); + + std::string comparison; + + switch (constraint.GetConstraintType()) + { + case ConstraintType_Equal: + case ConstraintType_SmallerOrEqual: + case ConstraintType_GreaterOrEqual: + { + std::string op; + switch (constraint.GetConstraintType()) + { + case ConstraintType_Equal: + op = "="; + break; + + case ConstraintType_SmallerOrEqual: + op = "<="; + break; + + case ConstraintType_GreaterOrEqual: + op = ">="; + break; + + default: + throw OrthancException(ErrorCode_InternalError); + } + + std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue()); + + if (constraint.IsCaseSensitive()) + { + comparison = tag + ".value " + op + " " + parameter; + } + else + { + comparison = "lower(" + tag + ".value) " + op + " lower(" + parameter + ")"; + } + + break; + } + + case ConstraintType_List: + { + for (size_t i = 0; i < constraint.GetValuesCount(); i++) + { + if (!comparison.empty()) + { + comparison += ", "; + } + + std::string parameter = formatter.GenerateParameter(constraint.GetValue(i)); + + if (constraint.IsCaseSensitive()) + { + comparison += parameter; + } + else + { + comparison += "lower(" + parameter + ")"; + } + } + + if (constraint.IsCaseSensitive()) + { + comparison = tag + ".value IN (" + comparison + ")"; + } + else + { + comparison = "lower(" + tag + ".value) IN (" + comparison + ")"; + } + + break; + } + + case ConstraintType_Wildcard: + { + const std::string value = constraint.GetSingleValue(); + + if (value == "*") + { + if (!constraint.IsMandatory()) + { + // Universal constraint on an optional tag, ignore it + return false; + } + } + else + { + std::string escaped; + escaped.reserve(value.size()); + + for (size_t i = 0; i < value.size(); i++) + { + if (value[i] == '*') + { + escaped += "%"; + } + else if (value[i] == '?') + { + escaped += "_"; + } + else if (value[i] == '%') + { + escaped += "\\%"; + } + else if (value[i] == '_') + { + escaped += "\\_"; + } + else if (value[i] == '\\') + { + escaped += "\\\\"; + } + else + { + escaped += value[i]; + } + } + + std::string parameter = formatter.GenerateParameter(escaped); + + if (constraint.IsCaseSensitive()) + { + comparison = tag + ".value LIKE " + parameter + " ESCAPE '\\'"; + } + else + { + comparison = "lower(" + tag + ".value) LIKE lower(" + parameter + ") ESCAPE '\\'"; + } + } + + break; + } + + default: + return false; + } + + if (constraint.IsMandatory()) + { + target = comparison; + } + else if (comparison.empty()) + { + target = tag + ".value IS NULL"; + } + else + { + target = tag + ".value IS NULL OR " + comparison; + } + + return true; + } + + + static void FormatJoin(std::string& target, + const DatabaseConstraint& constraint, + size_t index) + { + std::string tag = "t" + boost::lexical_cast(index); + + if (constraint.IsMandatory()) + { + target = " INNER JOIN "; + } + else + { + target = " LEFT JOIN "; + } + + if (constraint.IsIdentifier()) + { + target += "DicomIdentifiers "; + } + else + { + target += "MainDicomTags "; + } + + target += (tag + " ON " + tag + ".id = " + FormatLevel(constraint.GetLevel()) + + ".internalId AND " + tag + ".tagGroup = " + + boost::lexical_cast(constraint.GetTag().GetGroup()) + + " AND " + tag + ".tagElement = " + + boost::lexical_cast(constraint.GetTag().GetElement())); + } + + + void ISqlLookupFormatter::Apply(std::string& sql, + ISqlLookupFormatter& formatter, + const std::vector& lookup, + ResourceType queryLevel, + size_t limit) + { + for (size_t i = 0; i < lookup.size(); i++) + { + std::cout << i << ": " << lookup[i].GetTag() << " - " << EnumerationToString(lookup[i].GetLevel()); + std::cout << std::endl; + } + + assert(ResourceType_Patient < ResourceType_Study && + ResourceType_Study < ResourceType_Series && + ResourceType_Series < ResourceType_Instance); + + ResourceType upperLevel = queryLevel; + ResourceType lowerLevel = queryLevel; + + for (size_t i = 0; i < lookup.size(); i++) + { + ResourceType level = lookup[i].GetLevel(); + + if (level < upperLevel) + { + upperLevel = level; + } + + if (level > lowerLevel) + { + lowerLevel = level; + } + } + + assert(upperLevel <= queryLevel && + queryLevel <= lowerLevel); + + std::string joins, comparisons; + + size_t count = 0; + + for (size_t i = 0; i < lookup.size(); i++) + { + std::string comparison; + + if (FormatComparison(comparison, formatter, lookup[i], count)) + { + std::string join; + FormatJoin(join, lookup[i], count); + joins += join; + + if (!comparison.empty()) + { + comparisons += " AND " + comparison; + } + + count ++; + } + } + + sql = ("SELECT " + + FormatLevel(queryLevel) + ".publicId, " + + FormatLevel(queryLevel) + ".internalId" + + " FROM Resources AS " + FormatLevel(queryLevel)); + + for (int level = queryLevel - 1; level >= upperLevel; level--) + { + sql += (" INNER JOIN Resources " + + FormatLevel(static_cast(level)) + " ON " + + FormatLevel(static_cast(level)) + ".internalId=" + + FormatLevel(static_cast(level + 1)) + ".parentId"); + } + + for (int level = queryLevel + 1; level <= lowerLevel; level++) + { + sql += (" INNER JOIN Resources " + + FormatLevel(static_cast(level)) + " ON " + + FormatLevel(static_cast(level - 1)) + ".internalId=" + + FormatLevel(static_cast(level)) + ".parentId"); + } + + sql += (joins + " WHERE " + FormatLevel(queryLevel) + ".resourceType = " + + boost::lexical_cast(queryLevel) + comparisons); + + if (limit != 0) + { + sql += " LIMIT " + boost::lexical_cast(limit); + } + } +} diff -r 87f52703ebbc -r 6faf575ba9cc OrthancServer/Search/ISqlLookupFormatter.h --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/OrthancServer/Search/ISqlLookupFormatter.h Fri Dec 21 17:07:58 2018 +0100 @@ -0,0 +1,56 @@ +/** + * Orthanc - A Lightweight, RESTful DICOM Store + * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics + * Department, University Hospital of Liege, Belgium + * Copyright (C) 2017-2018 Osimis S.A., Belgium + * + * This program is free software: you can redistribute it and/or + * modify it under the terms of the GNU General Public License as + * published by the Free Software Foundation, either version 3 of the + * License, or (at your option) any later version. + * + * In addition, as a special exception, the copyright holders of this + * program give permission to link the code of its release with the + * OpenSSL project's "OpenSSL" library (or with modified versions of it + * that use the same license as the "OpenSSL" library), and distribute + * the linked executables. You must obey the GNU General Public License + * in all respects for all of the code used other than "OpenSSL". If you + * modify file(s) with this exception, you may extend this exception to + * your version of the file(s), but you are not obligated to do so. If + * you do not wish to do so, delete this exception statement from your + * version. If you delete this exception statement from all source files + * in the program, then also delete it here. + * + * This program is distributed in the hope that it will be useful, but + * WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with this program. If not, see . + **/ + + +#pragma once + +#include "DatabaseConstraint.h" + +namespace Orthanc +{ + // This class is also used by the "orthanc-databases" project + class ISqlLookupFormatter : public boost::noncopyable + { + public: + virtual ~ISqlLookupFormatter() + { + } + + virtual std::string GenerateParameter(const std::string& value) = 0; + + static void Apply(std::string& sql, + ISqlLookupFormatter& formatter, + const std::vector& lookup, + ResourceType queryLevel, + size_t limit); + }; +}