# HG changeset patch # User Alain Mazy # Date 1725033817 -7200 # Node ID afd421225eb476ca6a19fe406c1ee538661b4fc8 # Parent bbeefd4567dcff1882267c4fe357be7e13139712 WIP: started to implement IntegratedFind in SQLite diff -r bbeefd4567dc -r afd421225eb4 OrthancServer/Sources/Database/FindResponse.cpp --- a/OrthancServer/Sources/Database/FindResponse.cpp Thu Aug 29 16:18:20 2024 +0200 +++ b/OrthancServer/Sources/Database/FindResponse.cpp Fri Aug 30 18:03:37 2024 +0200 @@ -688,15 +688,17 @@ else { const std::string& id = item->GetIdentifier(); + int64_t internalId = item->GetInternalId(); - if (index_.find(id) == index_.end()) + if (identifierIndex_.find(id) == identifierIndex_.end() && internalIdIndex_.find(internalId) == internalIdIndex_.end()) { items_.push_back(protection.release()); - index_[id] = item; + identifierIndex_[id] = item; + internalIdIndex_[internalId] = item; } else { - throw OrthancException(ErrorCode_BadSequenceOfCalls, "This resource has already been added: " + id); + throw OrthancException(ErrorCode_BadSequenceOfCalls, "This resource has already been added: " + id + "/" + boost::lexical_cast(internalId)); } } } @@ -718,9 +720,25 @@ FindResponse::Resource& FindResponse::GetResourceByIdentifier(const std::string& id) { - Index::const_iterator found = index_.find(id); + IdentifierIndex::const_iterator found = identifierIndex_.find(id); - if (found == index_.end()) + if (found == identifierIndex_.end()) + { + throw OrthancException(ErrorCode_InexistentItem); + } + else + { + assert(found->second != NULL); + return *found->second; + } + } + + + FindResponse::Resource& FindResponse::GetResourceByInternalId(int64_t internalId) + { + InternalIdIndex::const_iterator found = internalIdIndex_.find(internalId); + + if (found == internalIdIndex_.end()) { throw OrthancException(ErrorCode_InexistentItem); } diff -r bbeefd4567dc -r afd421225eb4 OrthancServer/Sources/Database/FindResponse.h --- a/OrthancServer/Sources/Database/FindResponse.h Thu Aug 29 16:18:20 2024 +0200 +++ b/OrthancServer/Sources/Database/FindResponse.h Fri Aug 30 18:03:37 2024 +0200 @@ -280,10 +280,12 @@ }; private: - typedef std::map Index; + typedef std::map IdentifierIndex; + typedef std::map InternalIdIndex; std::deque items_; - Index index_; + IdentifierIndex identifierIndex_; + InternalIdIndex internalIdIndex_; public: ~FindResponse(); @@ -299,14 +301,26 @@ Resource& GetResourceByIdentifier(const std::string& id); + Resource& GetResourceByInternalId(int64_t internalId); + const Resource& GetResourceByIdentifier(const std::string& id) const { return const_cast(*this).GetResourceByIdentifier(id); } + const Resource& GetResourceByInternalId(int64_t internalId) const + { + return const_cast(*this).GetResourceByInternalId(internalId); + } + bool HasResource(const std::string& id) const { - return (index_.find(id) != index_.end()); + return (identifierIndex_.find(id) != identifierIndex_.end()); + } + + bool HasResource(int64_t& internalId) const + { + return (internalIdIndex_.find(internalId) != internalIdIndex_.end()); } }; } diff -r bbeefd4567dc -r afd421225eb4 OrthancServer/Sources/Database/OrthancIdentifiers.cpp --- a/OrthancServer/Sources/Database/OrthancIdentifiers.cpp Thu Aug 29 16:18:20 2024 +0200 +++ b/OrthancServer/Sources/Database/OrthancIdentifiers.cpp Fri Aug 30 18:03:37 2024 +0200 @@ -165,23 +165,23 @@ { return ResourceType_Patient; } - else if (HasPatientId() && + else if (// HasPatientId() && HasStudyId() && !HasSeriesId() && !HasInstanceId()) { return ResourceType_Study; } - else if (HasPatientId() && - HasStudyId() && + else if (// HasPatientId() && + // HasStudyId() && HasSeriesId() && !HasInstanceId()) { return ResourceType_Series; } - else if (HasPatientId() && - HasStudyId() && - HasSeriesId() && + else if (// HasPatientId() && + // HasStudyId() && + // HasSeriesId() && HasInstanceId()) { return ResourceType_Instance; @@ -240,4 +240,9 @@ throw OrthancException(ErrorCode_ParameterOutOfRange); } } + + bool OrthancIdentifiers::IsDefined() const + { + return HasPatientId() || HasStudyId() || HasSeriesId() || HasInstanceId(); + } } diff -r bbeefd4567dc -r afd421225eb4 OrthancServer/Sources/Database/OrthancIdentifiers.h --- a/OrthancServer/Sources/Database/OrthancIdentifiers.h Thu Aug 29 16:18:20 2024 +0200 +++ b/OrthancServer/Sources/Database/OrthancIdentifiers.h Fri Aug 30 18:03:37 2024 +0200 @@ -89,5 +89,7 @@ const std::string& id); std::string GetLevel(ResourceType level) const; + + bool IsDefined() const; }; } diff -r bbeefd4567dc -r afd421225eb4 OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp --- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp Thu Aug 29 16:18:20 2024 +0200 +++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp Fri Aug 30 18:03:37 2024 +0200 @@ -382,6 +382,131 @@ } } + virtual void ExecuteFind(FindResponse& response, + const FindRequest& request, + const Capabilities& capabilities) ORTHANC_OVERRIDE + { + LookupFormatter formatter; + + std::string sql; + LookupFormatter::Apply(sql, formatter, request); + + sql = "CREATE TEMPORARY TABLE Lookup AS " + sql; + + { + SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup"); + s.Run(); + } + + { + SQLite::Statement statement(db_, sql); + formatter.Bind(statement); + statement.Run(); + } + + { + SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId, internalId FROM Lookup"); + while (s.Step()) + { + response.Add(new FindResponse::Resource(request.GetLevel(), s.ColumnInt64(1), s.ColumnString(0))); + } + } + + if (request.IsRetrieveMainDicomTags()) + { + sql = "SELECT id, tagGroup, tagElement, value " + "FROM MainDicomTags " + "INNER JOIN Lookup ON MainDicomTags.id = Lookup.internalId"; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddStringDicomTag(request.GetLevel(), + static_cast(s.ColumnInt(1)), + static_cast(s.ColumnInt(2)), + s.ColumnString(3)); + } + } + + if (request.IsRetrieveParentIdentifier()) + { + sql = "SELECT currentLevel.internalId, parentLevel.publicId " + "FROM Resources AS currentLevel " + "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " + "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId "; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.SetParentIdentifier(s.ColumnString(1)); + } + } + + if (request.IsRetrieveMetadata()) + { + sql = "SELECT id, type, value " + "FROM Metadata " + "INNER JOIN Lookup ON Metadata.id = Lookup.internalId"; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddMetadata(request.GetLevel(), + static_cast(s.ColumnInt(1)), + s.ColumnString(2)); + } + } + + if (request.IsRetrieveLabels()) + { + sql = "SELECT id, label " + "FROM Labels " + "INNER JOIN Lookup ON Labels.id = Lookup.internalId"; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddLabel(s.ColumnString(1)); + } + } + + if (request.GetLevel() <= ResourceType_Series && request.GetChildrenSpecification(static_cast(request.GetLevel() + 1)).IsRetrieveIdentifiers()) + { + sql = "SELECT currentLevel.internalId, childLevel.publicId " + "FROM Resources AS currentLevel " + "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " + "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId "; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddChildIdentifier(static_cast(request.GetLevel() + 1), s.ColumnString(1)); + } + } + + if (request.GetLevel() <= ResourceType_Study && request.GetChildrenSpecification(static_cast(request.GetLevel() + 2)).IsRetrieveIdentifiers()) + { + sql = "SELECT currentLevel.internalId, grandChildLevel.publicId " + "FROM Resources AS currentLevel " + "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " + "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId " + "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId "; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddChildIdentifier(static_cast(request.GetLevel() + 2), s.ColumnString(1)); + } + } + } + + // From the "ICreateInstance" interface virtual void AttachChild(int64_t parent, diff -r bbeefd4567dc -r afd421225eb4 OrthancServer/Sources/Database/SQLiteDatabaseWrapper.h --- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.h Thu Aug 29 16:18:20 2024 +0200 +++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.h Fri Aug 30 18:03:37 2024 +0200 @@ -99,6 +99,11 @@ throw OrthancException(ErrorCode_NotImplemented); } + virtual bool HasIntegratedFind() const ORTHANC_OVERRIDE + { + return true; + } + /** * The "StartTransaction()" method is guaranteed to return a class * derived from "UnitTestsTransaction". The methods of diff -r bbeefd4567dc -r afd421225eb4 OrthancServer/Sources/Search/ISqlLookupFormatter.cpp --- a/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp Thu Aug 29 16:18:20 2024 +0200 +++ b/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp Fri Aug 30 18:03:37 2024 +0200 @@ -34,6 +34,7 @@ #if ORTHANC_BUILDING_SERVER_LIBRARY == 1 # include "../../../OrthancFramework/Sources/OrthancException.h" # include "../../../OrthancFramework/Sources/Toolbox.h" +# include "../Database/FindRequest.h" #else # include # include @@ -616,6 +617,151 @@ } } +#if ORTHANC_BUILDING_SERVER_LIBRARY == 1 + void ISqlLookupFormatter::Apply(std::string& sql, + ISqlLookupFormatter& formatter, + const FindRequest& request) + { + const bool escapeBrackets = formatter.IsEscapeBrackets(); + ResourceType queryLevel = request.GetLevel(); + const std::string& strQueryLevel = FormatLevel(queryLevel); + + std::string joins, comparisons; + + if (request.GetOrthancIdentifiers().IsDefined() && request.GetOrthancIdentifiers().DetectLevel() == queryLevel) + { + // single resource matching, there should not be other constraints + assert(request.GetDicomTagConstraints().GetSize() == 0); + assert(request.GetLabels().size() == 0); + assert(request.HasLimits() == false); + + comparisons = " AND " + strQueryLevel + ".publicId = " + formatter.GenerateParameter(request.GetOrthancIdentifiers().GetLevel(queryLevel)); + } + else if (request.GetOrthancIdentifiers().IsDefined() && request.GetOrthancIdentifiers().DetectLevel() == queryLevel - 1) + { + // single child resource matching, there should not be other constraints (at least for now) + assert(request.GetDicomTagConstraints().GetSize() == 0); + assert(request.GetLabels().size() == 0); + assert(request.HasLimits() == false); + + ResourceType parentLevel = static_cast(queryLevel - 1); + const std::string& strParentLevel = FormatLevel(parentLevel); + + comparisons = " AND " + strParentLevel + ".publicId = " + formatter.GenerateParameter(request.GetOrthancIdentifiers().GetLevel(parentLevel)); + joins += (" INNER JOIN Resources " + + strParentLevel + " ON " + + strQueryLevel + ".parentId=" + + strParentLevel + ".internalId"); + } + // TODO-FIND other levels (there's probably a way to make it generic as it was done before !!!): + // /studies/../instances + // /patients/../instances + // /series/../study + // /instances/../study + // ... + else + { + size_t count = 0; + + const DatabaseConstraints& dicomTagsConstraints = request.GetDicomTagConstraints(); + for (size_t i = 0; i < dicomTagsConstraints.GetSize(); i++) + { + const DatabaseConstraint& constraint = dicomTagsConstraints.GetConstraint(i); + + std::string comparison; + + if (FormatComparison(comparison, formatter, constraint, count, escapeBrackets)) + { + std::string join; + FormatJoin(join, constraint, count); + joins += join; + + if (!comparison.empty()) + { + comparisons += " AND " + comparison; + } + + count ++; + } + } + } + + sql = ("SELECT " + + strQueryLevel + ".publicId, " + + strQueryLevel + ".internalId" + + " FROM Resources AS " + strQueryLevel); + + // 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"); + // } + + std::list where; + where.push_back(strQueryLevel + ".resourceType = " + + formatter.FormatResourceType(queryLevel) + comparisons); + + + if (!request.GetLabels().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/ + **/ + + const std::set& labels = request.GetLabels(); + std::list formattedLabels; + for (std::set::const_iterator it = labels.begin(); it != labels.end(); ++it) + { + formattedLabels.push_back(formatter.GenerateParameter(*it)); + } + + std::string condition; + switch (request.GetLabelsConstraint()) + { + case LabelsConstraint_Any: + condition = "> 0"; + break; + + case LabelsConstraint_All: + condition = "= " + boost::lexical_cast(labels.size()); + break; + + case LabelsConstraint_None: + condition = "= 0"; + break; + + default: + throw OrthancException(ErrorCode_ParameterOutOfRange); + } + + where.push_back("(SELECT COUNT(1) FROM Labels AS selectedLabels WHERE selectedLabels.id = " + strQueryLevel + + ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition); + } + + sql += joins + Join(where, " WHERE ", " AND "); + + if (request.HasLimits()) + { + sql += " LIMIT " + boost::lexical_cast(request.GetLimitsCount()); + sql += " OFFSET " + boost::lexical_cast(request.GetLimitsSince()); + } + + } +#endif + void ISqlLookupFormatter::ApplySingleLevel(std::string& sql, ISqlLookupFormatter& formatter, diff -r bbeefd4567dc -r afd421225eb4 OrthancServer/Sources/Search/ISqlLookupFormatter.h --- a/OrthancServer/Sources/Search/ISqlLookupFormatter.h Thu Aug 29 16:18:20 2024 +0200 +++ b/OrthancServer/Sources/Search/ISqlLookupFormatter.h Fri Aug 30 18:03:37 2024 +0200 @@ -35,7 +35,8 @@ namespace Orthanc { class DatabaseConstraints; - + class FindRequest; + enum LabelsConstraint { LabelsConstraint_All, @@ -84,5 +85,11 @@ const std::set& labels, // New in Orthanc 1.12.0 LabelsConstraint labelsConstraint, // New in Orthanc 1.12.0 size_t limit); + +#if ORTHANC_BUILDING_SERVER_LIBRARY == 1 + static void Apply(std::string& sql, + ISqlLookupFormatter& formatter, + const FindRequest& request); +#endif }; }