orthanc: OrthancServer/SQLiteDatabaseWrapper.cpp comparison

comparison OrthancServer/SQLiteDatabaseWrapper.cpp @ 3030:25afa7b8cb51 db-changes

SQLiteDatabaseWrapper::ApplyLookupResources()

author	Sebastien Jodogne <s.jodogne@gmail.com>
date	Wed, 19 Dec 2018 11:18:39 +0100
parents	ea653ec47f31
children	18a2d196414b

comparison

equal deleted inserted replaced

-:ea653ec47f31
+:25afa7b8cb51
 {
 return GetTotalCompressedSize() > threshold;
 }
+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 void FormatJoin(std::string& target,
 const DatabaseConstraint& constraint,
 size_t index)
 {
 std::string tag = "t" + boost::lexical_cast<std::string>(index);
 else
 {
 target += "MainDicomTags ";
 }
-target += tag + " ON " + tag + ".id = ";
+target += (tag + " ON " + tag + ".id = " + FormatLevel(constraint.GetLevel()) +
+".internalId AND " + tag + ".tagGroup = " +
-switch (constraint.GetLevel())
-{
-case ResourceType_Patient:
-target += "patient";
-break;
-case ResourceType_Study:
-target += "study";
-break;
-case ResourceType_Series:
-target += "series";
-break;
-case ResourceType_Instance:
-target += "instance";
-break;
-default:
-throw OrthancException(ErrorCode_InternalError);
-}
-target += (".internalId AND " + tag + ".tagGroup = " +
 boost::lexical_cast<std::string>(constraint.GetTag().GetGroup()) +
 " AND " + tag + ".tagElement = " +
 boost::lexical_cast<std::string>(constraint.GetTag().GetElement()));
 }
 break;
 }
 default:
-// Don't modify "parameters"!
+// Don't modify "parameters" in this case!
 return false;
 }
 if (constraint.IsMandatory())
 {
 target += tag + ".value IS NULL OR " + comparison;
 }
 return true;
 }
+static void PrepareLookup(SQLite::Connection& db)
+{
+SQLite::Statement s(db, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup");
+s.Run();
+}
+static void AnswerLookup(std::vector<std::string>& resourcesId,
+std::vector<std::string>& instancesId,
+SQLite::Connection& db,
+ResourceType level)
+{
+resourcesId.clear();
+instancesId.clear();
+std::auto_ptr<SQLite::Statement> statement;
+switch (level)
+{
+case ResourceType_Patient:
+{
+statement.reset(
+new SQLite::Statement(
+db, SQLITE_FROM_HERE,
+"SELECT patients.publicId, instances.publicID FROM Lookup AS patients "
+"INNER JOIN Resources studies ON patients.internalId=studies.parentId "
+"INNER JOIN Resources series ON studies.internalId=series.parentId "
+"INNER JOIN Resources instances ON series.internalId=instances.parentId "
+"GROUP BY patients.publicId"));
+break;
+}
+case ResourceType_Study:
+{
+statement.reset(
+new SQLite::Statement(
+db, SQLITE_FROM_HERE,
+"SELECT studies.publicId, instances.publicID FROM Lookup AS studies "
+"INNER JOIN Resources series ON studies.internalId=series.parentId "
+"INNER JOIN Resources instances ON series.internalId=instances.parentId "
+"GROUP BY studies.publicId"));
+break;
+}
+case ResourceType_Series:
+{
+statement.reset(
+new SQLite::Statement(
+db, SQLITE_FROM_HERE,
+"SELECT series.publicId, instances.publicID FROM Lookup AS series "
+"INNER JOIN Resources instances ON series.internalId=instances.parentId "
+"GROUP BY series.publicId"));
+break;
+}
+case ResourceType_Instance:
+{
+statement.reset(
+new SQLite::Statement(
+db, SQLITE_FROM_HERE, "SELECT publicId, publicId FROM Lookup"));
+break;
+}
+default:
+throw OrthancException(ErrorCode_InternalError);
+}
+assert(statement.get() != NULL);
+while (statement->Step())
+{
+resourcesId.push_back(statement->ColumnString(0));
+instancesId.push_back(statement->ColumnString(1));
+}
+}
+static void FormatConstraints(std::string& joins,
+std::string& comparisons,
+std::vector<std::string>& parameters,
+const std::vector<DatabaseConstraint>& lookup)
+{
+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;
+comparisons += " AND (" + comparison + ")";
+count ++;
+}
+}
+}
 void SQLiteDatabaseWrapper::ApplyLookupPatients(std::vector<std::string>& patientsId,
 std::vector<std::string>& instancesId,
 const std::vector<DatabaseConstraint>& lookup,
 size_t limit)
 {
-printf("ICI 1\n");
+PrepareLookup(db_);
-{
-SQLite::Statement s(db_, "DROP TABLE IF EXISTS Lookup");
-s.Run();
-}
 std::string joins, comparisons;
 std::vector<std::string> parameters;
+FormatConstraints(joins, comparisons, parameters, lookup);
-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;
-comparisons += " AND (" + comparison + ")";
-count ++;
-}
-}
 {
 std::string sql = ("CREATE TEMPORARY TABLE Lookup AS "
-"SELECT patient.publicId, patient.internalId FROM Resources AS patient" +
+"SELECT patients.publicId, patients.internalId FROM Resources AS patients" +
-joins + " WHERE patient.resourceType = " +
+joins + " WHERE patients.resourceType = " +
 boost::lexical_cast<std::string>(ResourceType_Patient) + comparisons);
 if (limit != 0)
 {
 sql += " LIMIT " + boost::lexical_cast<std::string>(limit);
 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();
 }
-{
+AnswerLookup(patientsId, instancesId, db_, ResourceType_Patient);
-SQLite::Statement s
+}
-(db_, "SELECT patient.publicId, instances.publicID FROM Lookup AS patient "
-"INNER JOIN Resources studies ON patient.internalId=studies.parentId "
-"INNER JOIN Resources series ON studies.internalId=series.parentId "
-"INNER JOIN Resources instances ON series.internalId=instances.parentId "
-"GROUP BY patient.publicId");
-patientsId.clear();
-while (s.Step())
-{
-const std::string patient = s.ColumnString(0);
-const std::string instance = s.ColumnString(1);
-patientsId.push_back(patient);
-instancesId.push_back(instance);
-printf("** [%s] [%s]\n", patient.c_str(), instance.c_str());
-}
-}
-}
 void SQLiteDatabaseWrapper::ApplyLookupResources(std::vector<std::string>& resourcesId,
 std::vector<std::string>& instancesId,
 const std::vector<DatabaseConstraint>& lookup,
 ResourceType queryLevel,
 size_t limit)
 {
+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++)
 {
-if (!IsResourceLevelAboveOrEqual(upperLevel, lookup[i].GetLevel()))
+ResourceType level = lookup[i].GetLevel();
-{
-upperLevel = lookup[i].GetLevel();
+if (level < upperLevel)
-}
+{
+upperLevel = level;
-if (!IsResourceLevelAboveOrEqual(lookup[i].GetLevel(), lowerLevel))
+}
-{
-lowerLevel = lookup[i].GetLevel();
+if (level > lowerLevel)
+{
+lowerLevel = level;
 }
 }
 printf("ICI 2: [%s] -> [%s]\n", EnumerationToString(upperLevel), EnumerationToString(lowerLevel));
-throw OrthancException(ErrorCode_NotImplemented);
+PrepareLookup(db_);
+std::string joins, comparisons;
+std::vector<std::string> parameters;
+FormatConstraints(joins, comparisons, parameters, lookup);
+{
+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<ResourceType>(level)) + " ON " +
+FormatLevel(static_cast<ResourceType>(level)) + ".internalId=" +
+FormatLevel(static_cast<ResourceType>(level + 1)) + ".parentId");
+}
+for (int level = queryLevel + 1; level <= lowerLevel; level++)
+{
+sql += (" INNER JOIN Resources " +
+FormatLevel(static_cast<ResourceType>(level - 1)) + " ON " +
+FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" +
+FormatLevel(static_cast<ResourceType>(level)) + ".parentId");
+}
+sql += (joins + " WHERE " + FormatLevel(queryLevel) + ".resourceType = " +
+boost::lexical_cast<std::string>(queryLevel) + comparisons);
+if (limit != 0)
+{
+sql += " LIMIT " + boost::lexical_cast<std::string>(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();
+}
+AnswerLookup(resourcesId, instancesId, db_, queryLevel);
 }
 }

Mercurial > hg > orthanc

comparison OrthancServer/SQLiteDatabaseWrapper.cpp @ 3030:25afa7b8cb51 db-changes