Mercurial > hg > orthanc
changeset 5227:988dab8deb1c db-protobuf
"/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments
| author | Sebastien Jodogne <s.jodogne@gmail.com> |
|---|---|
| date | Tue, 04 Apr 2023 09:16:12 +0200 |
| parents | 49e906a8fea2 |
| children | 365eb5ea5492 |
| files | NEWS OrthancServer/Sources/Database/InstallLabelsTable.sql OrthancServer/Sources/Database/PrepareDatabase.sql OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp OrthancServer/Sources/OrthancRestApi/OrthancRestResources.cpp OrthancServer/Sources/Search/ISqlLookupFormatter.cpp OrthancServer/Sources/Search/ISqlLookupFormatter.h |
| diffstat | 7 files changed, 144 insertions(+), 20 deletions(-) [+] |
line wrap: on
line diff
--- a/NEWS Tue Apr 04 07:09:22 2023 +0200 +++ b/NEWS Tue Apr 04 09:16:12 2023 +0200 @@ -11,6 +11,7 @@ * API version upgraded to 20 * New URIs "/.../{id}/labels/{label}" to test/set/remove labels +* "/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments * The "/patients/{id}", "/studies/{id}", "/series/{id}" and "/instances/{id}" contain the "Labels" field * "/system": added "UserMetadata"
--- a/OrthancServer/Sources/Database/InstallLabelsTable.sql Tue Apr 04 07:09:22 2023 +0200 +++ b/OrthancServer/Sources/Database/InstallLabelsTable.sql Tue Apr 04 09:16:12 2023 +0200 @@ -20,5 +20,9 @@ CREATE TABLE Labels( internalId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, - label TEXT + label TEXT NOT NULL, + PRIMARY KEY(internalId, label) -- Prevents duplicates ); + +CREATE INDEX LabelsIndex1 ON Labels(internalId); +CREATE INDEX LabelsIndex2 ON Labels(label); -- This index allows efficient lookups
--- a/OrthancServer/Sources/Database/PrepareDatabase.sql Tue Apr 04 07:09:22 2023 +0200 +++ b/OrthancServer/Sources/Database/PrepareDatabase.sql Tue Apr 04 09:16:12 2023 +0200 @@ -94,7 +94,8 @@ -- New in Orthanc 1.12.0 CREATE TABLE Labels( internalId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, - label TEXT + label TEXT NOT NULL, + PRIMARY KEY(internalId, label) -- Prevents duplicates ); CREATE INDEX ChildrenIndex ON Resources(parentId); @@ -114,6 +115,10 @@ CREATE INDEX ChangesIndex ON Changes(internalId); +-- New in Orthanc 1.12.0 +CREATE INDEX LabelsIndex1 ON Labels(internalId); +CREATE INDEX LabelsIndex2 ON Labels(label); -- This index allows efficient lookups + CREATE TRIGGER AttachedFileDeleted AFTER DELETE ON AttachedFiles BEGIN
--- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp Tue Apr 04 07:09:22 2023 +0200 +++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp Tue Apr 04 09:16:12 2023 +0200 @@ -345,16 +345,10 @@ const std::set<std::string>& withoutLabels, uint32_t limit) ORTHANC_OVERRIDE { - if (!withLabels.empty() || - !withoutLabels.empty()) - { - throw OrthancException(ErrorCode_NotImplemented); - } - LookupFormatter formatter; std::string sql; - LookupFormatter::Apply(sql, formatter, lookup, queryLevel, limit); + LookupFormatter::Apply(sql, formatter, lookup, queryLevel, withLabels, withoutLabels, limit); sql = "CREATE TEMPORARY TABLE Lookup AS " + sql; @@ -1090,7 +1084,7 @@ } else { - SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO Labels (internalId, label) VALUES(?, ?)"); + SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT OR IGNORE INTO Labels (internalId, label) VALUES(?, ?)"); s.BindInt64(0, resource); s.BindString(1, label); s.Run();
--- a/OrthancServer/Sources/OrthancRestApi/OrthancRestResources.cpp Tue Apr 04 07:09:22 2023 +0200 +++ b/OrthancServer/Sources/OrthancRestApi/OrthancRestResources.cpp Tue Apr 04 09:16:12 2023 +0200 @@ -3072,6 +3072,8 @@ static const char* const KEY_QUERY = "Query"; static const char* const KEY_REQUESTED_TAGS = "RequestedTags"; static const char* const KEY_SINCE = "Since"; + static const char* const KEY_WITH_LABELS = "WithLabels"; // New in Orthanc 1.12.0 + static const char* const KEY_WITHOUT_LABELS = "WithoutLabels"; // New in Orthanc 1.12.0 if (call.IsDocumentation()) { @@ -3101,6 +3103,10 @@ "all Main Dicom Tags to keep backward compatibility with Orthanc prior to 1.11.0.", false) .SetRequestField(KEY_QUERY, RestApiCallDocumentation::Type_JsonObject, "Associative array containing the filter on the values of the DICOM tags", true) + .SetRequestField(KEY_WITH_LABELS, RestApiCallDocumentation::Type_JsonListOfStrings, + "List of strings specifying which labels must be present in the resources (new in Orthanc 1.12.0)", true) + .SetRequestField(KEY_WITHOUT_LABELS, RestApiCallDocumentation::Type_JsonListOfStrings, + "List of strings specifying which labels must not be present in the resources (new in Orthanc 1.12.0)", true) .AddAnswerType(MimeType_Json, "JSON array containing either the Orthanc identifiers, or detailed information " "about the reported resources (if `Expand` argument is `true`)"); return; @@ -3131,25 +3137,37 @@ request[KEY_CASE_SENSITIVE].type() != Json::booleanValue) { throw OrthancException(ErrorCode_BadRequest, - "Field \"" + std::string(KEY_CASE_SENSITIVE) + "\" should be a Boolean"); + "Field \"" + std::string(KEY_CASE_SENSITIVE) + "\" must be a Boolean"); } else if (request.isMember(KEY_LIMIT) && request[KEY_LIMIT].type() != Json::intValue) { throw OrthancException(ErrorCode_BadRequest, - "Field \"" + std::string(KEY_LIMIT) + "\" should be an integer"); + "Field \"" + std::string(KEY_LIMIT) + "\" must be an integer"); } else if (request.isMember(KEY_SINCE) && request[KEY_SINCE].type() != Json::intValue) { throw OrthancException(ErrorCode_BadRequest, - "Field \"" + std::string(KEY_SINCE) + "\" should be an integer"); + "Field \"" + std::string(KEY_SINCE) + "\" must be an integer"); } else if (request.isMember(KEY_REQUESTED_TAGS) && request[KEY_REQUESTED_TAGS].type() != Json::arrayValue) { throw OrthancException(ErrorCode_BadRequest, - "Field \"" + std::string(KEY_REQUESTED_TAGS) + "\" should be an array"); + "Field \"" + std::string(KEY_REQUESTED_TAGS) + "\" must be an array"); + } + else if (request.isMember(KEY_WITH_LABELS) && + request[KEY_WITH_LABELS].type() != Json::arrayValue) + { + throw OrthancException(ErrorCode_BadRequest, + "Field \"" + std::string(KEY_WITH_LABELS) + "\" must be an array of strings"); + } + else if (request.isMember(KEY_WITHOUT_LABELS) && + request[KEY_WITHOUT_LABELS].type() != Json::arrayValue) + { + throw OrthancException(ErrorCode_BadRequest, + "Field \"" + std::string(KEY_WITHOUT_LABELS) + "\" must be an array of strings"); } else { @@ -3172,7 +3190,7 @@ if (tmp < 0) { throw OrthancException(ErrorCode_ParameterOutOfRange, - "Field \"" + std::string(KEY_LIMIT) + "\" should be a positive integer"); + "Field \"" + std::string(KEY_LIMIT) + "\" must be a positive integer"); } limit = static_cast<size_t>(tmp); @@ -3185,7 +3203,7 @@ if (tmp < 0) { throw OrthancException(ErrorCode_ParameterOutOfRange, - "Field \"" + std::string(KEY_SINCE) + "\" should be a positive integer"); + "Field \"" + std::string(KEY_SINCE) + "\" must be a positive integer"); } since = static_cast<size_t>(tmp); @@ -3208,7 +3226,7 @@ if (request[KEY_QUERY][members[i]].type() != Json::stringValue) { throw OrthancException(ErrorCode_BadRequest, - "Tag \"" + members[i] + "\" should be associated with a string"); + "Tag \"" + members[i] + "\" must be associated with a string"); } const std::string value = request[KEY_QUERY][members[i]].asString(); @@ -3223,6 +3241,36 @@ } } + if (request.isMember(KEY_WITH_LABELS)) // New in Orthanc 1.12.0 + { + for (Json::Value::ArrayIndex i = 0; i < request[KEY_WITH_LABELS].size(); i++) + { + if (request[KEY_WITH_LABELS][i].type() != Json::stringValue) + { + throw OrthancException(ErrorCode_BadRequest, "Field \""+ std::string(KEY_WITH_LABELS) + "\" must contain strings"); + } + else + { + query.AddWithLabel(request[KEY_WITH_LABELS][i].asString()); + } + } + } + + if (request.isMember(KEY_WITHOUT_LABELS)) // New in Orthanc 1.12.0 + { + for (Json::Value::ArrayIndex i = 0; i < request[KEY_WITHOUT_LABELS].size(); i++) + { + if (request[KEY_WITHOUT_LABELS][i].type() != Json::stringValue) + { + throw OrthancException(ErrorCode_BadRequest, "Field \""+ std::string(KEY_WITHOUT_LABELS) + "\" must contain strings"); + } + else + { + query.AddWithoutLabel(request[KEY_WITHOUT_LABELS][i].asString()); + } + } + } + FindVisitor visitor(OrthancRestApi::GetDicomFormat(request, DicomToJsonFormat_Human), context.GetFindStorageAccessMode()); context.Apply(visitor, query, level, since, limit); visitor.Answer(call.GetOutput(), context, level, expand, requestedTags);
--- a/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp Tue Apr 04 07:09:22 2023 +0200 +++ b/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp Tue Apr 04 09:16:12 2023 +0200 @@ -39,6 +39,7 @@ #include "DatabaseConstraint.h" #include <boost/lexical_cast.hpp> +#include <list> namespace Orthanc @@ -268,12 +269,46 @@ " AND " + tag + ".tagElement = " + boost::lexical_cast<std::string>(constraint.GetTag().GetElement())); } + + + static std::string Join(const std::list<std::string>& values, + const std::string& prefix, + const std::string& separator) + { + if (values.empty()) + { + return ""; + } + else + { + std::string s = prefix; + + bool first = true; + for (std::list<std::string>::const_iterator it = values.begin(); it != values.end(); ++it) + { + if (first) + { + first = false; + } + else + { + s += separator; + } + + s += *it; + } + + return s; + } + } void ISqlLookupFormatter::Apply(std::string& sql, ISqlLookupFormatter& formatter, const std::vector<DatabaseConstraint>& lookup, ResourceType queryLevel, + const std::set<std::string>& withLabels, + const std::set<std::string>& withoutLabels, size_t limit) { assert(ResourceType_Patient < ResourceType_Study && @@ -346,9 +381,44 @@ FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" + FormatLevel(static_cast<ResourceType>(level)) + ".parentId"); } - - sql += (joins + " WHERE " + FormatLevel(queryLevel) + ".resourceType = " + - formatter.FormatResourceType(queryLevel) + comparisons); + + std::list<std::string> where; + + if (!withLabels.empty()) + { + std::list<std::string> labels; + for (std::set<std::string>::const_iterator it = withLabels.begin(); it != withLabels.end(); ++it) + { + labels.push_back(formatter.GenerateParameter(*it)); + } + + where.push_back(boost::lexical_cast<std::string>(withLabels.size()) + + " = (SELECT COUNT(1) FROM Labels WHERE internalId = " + FormatLevel(queryLevel) + + ".internalId AND label IN (" + Join(labels, "", ", ") + "))"); + } + + if (!withoutLabels.empty()) + { + /** + * "In SQL Server, NOT EXISTS and NOT IN predicates are the best + * way to search for missing values, as long as both columns in + * question are NOT NULL." + * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ + **/ + std::list<std::string> labels; + for (std::set<std::string>::const_iterator it = withoutLabels.begin(); it != withoutLabels.end(); ++it) + { + labels.push_back(formatter.GenerateParameter(*it)); + } + + where.push_back("NOT EXISTS (SELECT 1 FROM Labels WHERE internalId = " + FormatLevel(queryLevel) + + ".internalId AND label IN (" + Join(labels, "", ", ") + "))"); + } + + where.push_back(FormatLevel(queryLevel) + ".resourceType = " + + formatter.FormatResourceType(queryLevel) + comparisons); + + sql += joins + Join(where, " WHERE ", " AND "); if (limit != 0) {
--- a/OrthancServer/Sources/Search/ISqlLookupFormatter.h Tue Apr 04 07:09:22 2023 +0200 +++ b/OrthancServer/Sources/Search/ISqlLookupFormatter.h Tue Apr 04 09:16:12 2023 +0200 @@ -60,6 +60,8 @@ ISqlLookupFormatter& formatter, const std::vector<DatabaseConstraint>& lookup, ResourceType queryLevel, + const std::set<std::string>& withLabels, // New in Orthanc 1.12.0 + const std::set<std::string>& withoutLabels, // New in Orthanc 1.12.0 size_t limit); }; }