/**
* Orthanc - A Lightweight, RESTful DICOM Store
* Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
* Department, University Hospital of Liege, Belgium
* Copyright (C) 2017-2020 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 "SQLiteDatabaseWrapper.h"
#include "../../../OrthancFramework/Sources/DicomFormat/DicomArray.h"
#include "../../../OrthancFramework/Sources/Logging.h"
#include "../../../OrthancFramework/Sources/SQLite/Transaction.h"
#include "../Search/ISqlLookupFormatter.h"
#include "../ServerToolbox.h"
#include
#include
#include
namespace Orthanc
{
namespace Internals
{
class SignalFileDeleted : public SQLite::IScalarFunction
{
private:
IDatabaseListener& listener_;
public:
SignalFileDeleted(IDatabaseListener& listener) :
listener_(listener)
{
}
virtual const char* GetName() const
{
return "SignalFileDeleted";
}
virtual unsigned int GetCardinality() const
{
return 7;
}
virtual void Compute(SQLite::FunctionContext& context)
{
std::string uncompressedMD5, compressedMD5;
if (!context.IsNullValue(5))
{
uncompressedMD5 = context.GetStringValue(5);
}
if (!context.IsNullValue(6))
{
compressedMD5 = context.GetStringValue(6);
}
FileInfo info(context.GetStringValue(0),
static_cast(context.GetIntValue(1)),
static_cast(context.GetInt64Value(2)),
uncompressedMD5,
static_cast(context.GetIntValue(3)),
static_cast(context.GetInt64Value(4)),
compressedMD5);
listener_.SignalFileDeleted(info);
}
};
class SignalResourceDeleted : public SQLite::IScalarFunction
{
private:
IDatabaseListener& listener_;
public:
SignalResourceDeleted(IDatabaseListener& listener) :
listener_(listener)
{
}
virtual const char* GetName() const
{
return "SignalResourceDeleted";
}
virtual unsigned int GetCardinality() const
{
return 2;
}
virtual void Compute(SQLite::FunctionContext& context)
{
ResourceType type = static_cast(context.GetIntValue(1));
ServerIndexChange change(ChangeType_Deleted, type, context.GetStringValue(0));
listener_.SignalChange(change);
}
};
class SignalRemainingAncestor : public SQLite::IScalarFunction
{
private:
bool hasRemainingAncestor_;
std::string remainingPublicId_;
ResourceType remainingType_;
public:
SignalRemainingAncestor() :
hasRemainingAncestor_(false)
{
}
void Reset()
{
hasRemainingAncestor_ = false;
}
virtual const char* GetName() const
{
return "SignalRemainingAncestor";
}
virtual unsigned int GetCardinality() const
{
return 2;
}
virtual void Compute(SQLite::FunctionContext& context)
{
CLOG(TRACE, SQLITE) << "There exists a remaining ancestor with public ID \""
<< context.GetStringValue(0) << "\" of type "
<< context.GetIntValue(1);
if (!hasRemainingAncestor_ ||
remainingType_ >= context.GetIntValue(1))
{
hasRemainingAncestor_ = true;
remainingPublicId_ = context.GetStringValue(0);
remainingType_ = static_cast(context.GetIntValue(1));
}
}
bool HasRemainingAncestor() const
{
return hasRemainingAncestor_;
}
const std::string& GetRemainingAncestorId() const
{
assert(hasRemainingAncestor_);
return remainingPublicId_;
}
ResourceType GetRemainingAncestorType() const
{
assert(hasRemainingAncestor_);
return remainingType_;
}
};
}
void SQLiteDatabaseWrapper::GetChangesInternal(std::list& target,
bool& done,
SQLite::Statement& s,
uint32_t maxResults)
{
target.clear();
while (target.size() < maxResults && s.Step())
{
int64_t seq = s.ColumnInt64(0);
ChangeType changeType = static_cast(s.ColumnInt(1));
ResourceType resourceType = static_cast(s.ColumnInt(3));
const std::string& date = s.ColumnString(4);
int64_t internalId = s.ColumnInt64(2);
std::string publicId = GetPublicId(internalId);
target.push_back(ServerIndexChange(seq, changeType, resourceType, publicId, date));
}
done = !(target.size() == maxResults && s.Step());
}
void SQLiteDatabaseWrapper::GetExportedResourcesInternal(std::list& target,
bool& done,
SQLite::Statement& s,
uint32_t maxResults)
{
target.clear();
while (target.size() < maxResults && s.Step())
{
int64_t seq = s.ColumnInt64(0);
ResourceType resourceType = static_cast(s.ColumnInt(1));
std::string publicId = s.ColumnString(2);
ExportedResource resource(seq,
resourceType,
publicId,
s.ColumnString(3), // modality
s.ColumnString(8), // date
s.ColumnString(4), // patient ID
s.ColumnString(5), // study instance UID
s.ColumnString(6), // series instance UID
s.ColumnString(7)); // sop instance UID
target.push_back(resource);
}
done = !(target.size() == maxResults && s.Step());
}
void SQLiteDatabaseWrapper::GetChildren(std::list& childrenPublicIds,
int64_t id)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId FROM Resources WHERE parentId=?");
s.BindInt64(0, id);
childrenPublicIds.clear();
while (s.Step())
{
childrenPublicIds.push_back(s.ColumnString(0));
}
}
void SQLiteDatabaseWrapper::DeleteResource(int64_t id)
{
signalRemainingAncestor_->Reset();
SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM Resources WHERE internalId=?");
s.BindInt64(0, id);
s.Run();
if (signalRemainingAncestor_->HasRemainingAncestor() &&
listener_ != NULL)
{
listener_->SignalRemainingAncestor(signalRemainingAncestor_->GetRemainingAncestorType(),
signalRemainingAncestor_->GetRemainingAncestorId());
}
}
bool SQLiteDatabaseWrapper::GetParentPublicId(std::string& target,
int64_t id)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT a.publicId FROM Resources AS a, Resources AS b "
"WHERE a.internalId = b.parentId AND b.internalId = ?");
s.BindInt64(0, id);
if (s.Step())
{
target = s.ColumnString(0);
return true;
}
else
{
return false;
}
}
int64_t SQLiteDatabaseWrapper::GetTableRecordCount(const std::string& table)
{
/**
* "Generally one cannot use SQL parameters/placeholders for
* database identifiers (tables, columns, views, schemas, etc.) or
* database functions (e.g., CURRENT_DATE), but instead only for
* binding literal values." => To avoid any SQL injection, we
* check that the "table" parameter has only alphabetic
* characters.
* https://stackoverflow.com/a/1274764/881731
**/
for (size_t i = 0; i < table.size(); i++)
{
if (!isalpha(table[i]))
{
throw OrthancException(ErrorCode_ParameterOutOfRange);
}
}
// Don't use "SQLITE_FROM_HERE", otherwise "table" would be cached
SQLite::Statement s(db_, "SELECT COUNT(*) FROM " + table);
if (s.Step())
{
int64_t c = s.ColumnInt(0);
assert(!s.Step());
return c;
}
else
{
throw OrthancException(ErrorCode_InternalError);
}
}
SQLiteDatabaseWrapper::SQLiteDatabaseWrapper(const std::string& path) :
listener_(NULL),
signalRemainingAncestor_(NULL),
version_(0)
{
db_.Open(path);
}
SQLiteDatabaseWrapper::SQLiteDatabaseWrapper() :
listener_(NULL),
signalRemainingAncestor_(NULL),
version_(0)
{
db_.OpenInMemory();
}
int SQLiteDatabaseWrapper::GetGlobalIntegerProperty(GlobalProperty property,
int defaultValue)
{
std::string tmp;
if (!LookupGlobalProperty(tmp, GlobalProperty_DatabasePatchLevel))
{
return defaultValue;
}
else
{
try
{
return boost::lexical_cast(tmp);
}
catch (boost::bad_lexical_cast&)
{
throw OrthancException(ErrorCode_ParameterOutOfRange,
"Global property " + boost::lexical_cast(property) +
" should be an integer, but found: " + tmp);
}
}
}
void SQLiteDatabaseWrapper::Open()
{
db_.Execute("PRAGMA ENCODING=\"UTF-8\";");
// Performance tuning of SQLite with PRAGMAs
// http://www.sqlite.org/pragma.html
db_.Execute("PRAGMA SYNCHRONOUS=NORMAL;");
db_.Execute("PRAGMA JOURNAL_MODE=WAL;");
db_.Execute("PRAGMA LOCKING_MODE=EXCLUSIVE;");
db_.Execute("PRAGMA WAL_AUTOCHECKPOINT=1000;");
//db_.Execute("PRAGMA TEMP_STORE=memory");
// Make "LIKE" case-sensitive in SQLite
db_.Execute("PRAGMA case_sensitive_like = true;");
{
SQLite::Transaction t(db_);
t.Begin();
if (!db_.DoesTableExist("GlobalProperties"))
{
LOG(INFO) << "Creating the database";
std::string query;
ServerResources::GetFileResource(query, ServerResources::PREPARE_DATABASE);
db_.Execute(query);
}
// Check the version of the database
std::string tmp;
if (!LookupGlobalProperty(tmp, GlobalProperty_DatabaseSchemaVersion))
{
tmp = "Unknown";
}
bool ok = false;
try
{
LOG(INFO) << "Version of the Orthanc database: " << tmp;
version_ = boost::lexical_cast(tmp);
ok = true;
}
catch (boost::bad_lexical_cast&)
{
}
if (!ok)
{
throw OrthancException(ErrorCode_IncompatibleDatabaseVersion,
"Incompatible version of the Orthanc database: " + tmp);
}
// New in Orthanc 1.5.1
if (version_ == 6)
{
if (!LookupGlobalProperty(tmp, GlobalProperty_GetTotalSizeIsFast) ||
tmp != "1")
{
LOG(INFO) << "Installing the SQLite triggers to track the size of the attachments";
std::string query;
ServerResources::GetFileResource(query, ServerResources::INSTALL_TRACK_ATTACHMENTS_SIZE);
db_.Execute(query);
}
}
t.Commit();
}
signalRemainingAncestor_ = new Internals::SignalRemainingAncestor;
db_.Register(signalRemainingAncestor_);
}
static void ExecuteUpgradeScript(SQLite::Connection& db,
ServerResources::FileResourceId script)
{
std::string upgrade;
ServerResources::GetFileResource(upgrade, script);
db.BeginTransaction();
db.Execute(upgrade);
db.CommitTransaction();
}
void SQLiteDatabaseWrapper::Upgrade(unsigned int targetVersion,
IStorageArea& storageArea)
{
if (targetVersion != 6)
{
throw OrthancException(ErrorCode_IncompatibleDatabaseVersion);
}
// This version of Orthanc is only compatible with versions 3, 4,
// 5 and 6 of the DB schema
if (version_ != 3 &&
version_ != 4 &&
version_ != 5 &&
version_ != 6)
{
throw OrthancException(ErrorCode_IncompatibleDatabaseVersion);
}
if (version_ == 3)
{
LOG(WARNING) << "Upgrading database version from 3 to 4";
ExecuteUpgradeScript(db_, ServerResources::UPGRADE_DATABASE_3_TO_4);
version_ = 4;
}
if (version_ == 4)
{
LOG(WARNING) << "Upgrading database version from 4 to 5";
ExecuteUpgradeScript(db_, ServerResources::UPGRADE_DATABASE_4_TO_5);
version_ = 5;
}
if (version_ == 5)
{
LOG(WARNING) << "Upgrading database version from 5 to 6";
// No change in the DB schema, the step from version 5 to 6 only
// consists in reconstructing the main DICOM tags information
// (as more tags got included).
db_.BeginTransaction();
ServerToolbox::ReconstructMainDicomTags(*this, storageArea, ResourceType_Patient);
ServerToolbox::ReconstructMainDicomTags(*this, storageArea, ResourceType_Study);
ServerToolbox::ReconstructMainDicomTags(*this, storageArea, ResourceType_Series);
ServerToolbox::ReconstructMainDicomTags(*this, storageArea, ResourceType_Instance);
db_.Execute("UPDATE GlobalProperties SET value=\"6\" WHERE property=" +
boost::lexical_cast(GlobalProperty_DatabaseSchemaVersion) + ";");
db_.CommitTransaction();
version_ = 6;
}
}
void SQLiteDatabaseWrapper::SetListener(IDatabaseListener& listener)
{
listener_ = &listener;
db_.Register(new Internals::SignalFileDeleted(listener));
db_.Register(new Internals::SignalResourceDeleted(listener));
}
void SQLiteDatabaseWrapper::ClearTable(const std::string& tableName)
{
db_.Execute("DELETE FROM " + tableName);
}
bool SQLiteDatabaseWrapper::LookupParent(int64_t& parentId,
int64_t resourceId)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"SELECT parentId FROM Resources WHERE internalId=?");
s.BindInt64(0, resourceId);
if (!s.Step())
{
throw OrthancException(ErrorCode_UnknownResource);
}
if (s.ColumnIsNull(0))
{
return false;
}
else
{
parentId = s.ColumnInt(0);
return true;
}
}
ResourceType SQLiteDatabaseWrapper::GetResourceType(int64_t resourceId)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"SELECT resourceType FROM Resources WHERE internalId=?");
s.BindInt64(0, resourceId);
if (s.Step())
{
return static_cast(s.ColumnInt(0));
}
else
{
throw OrthancException(ErrorCode_UnknownResource);
}
}
std::string SQLiteDatabaseWrapper::GetPublicId(int64_t resourceId)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"SELECT publicId FROM Resources WHERE internalId=?");
s.BindInt64(0, resourceId);
if (s.Step())
{
return s.ColumnString(0);
}
else
{
throw OrthancException(ErrorCode_UnknownResource);
}
}
void SQLiteDatabaseWrapper::GetChanges(std::list& target /*out*/,
bool& done /*out*/,
int64_t since,
uint32_t maxResults)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM Changes WHERE seq>? ORDER BY seq LIMIT ?");
s.BindInt64(0, since);
s.BindInt(1, maxResults + 1);
GetChangesInternal(target, done, s, maxResults);
}
void SQLiteDatabaseWrapper::GetLastChange(std::list& target /*out*/)
{
bool done; // Ignored
SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM Changes ORDER BY seq DESC LIMIT 1");
GetChangesInternal(target, done, s, 1);
}
class SQLiteDatabaseWrapper::Transaction : public IDatabaseWrapper::ITransaction
{
private:
SQLiteDatabaseWrapper& that_;
std::unique_ptr transaction_;
int64_t initialDiskSize_;
public:
Transaction(SQLiteDatabaseWrapper& that) :
that_(that),
transaction_(new SQLite::Transaction(that_.db_))
{
#if defined(NDEBUG)
// Release mode
initialDiskSize_ = 0;
#else
// Debug mode
initialDiskSize_ = static_cast(that_.GetTotalCompressedSize());
#endif
}
virtual void Begin()
{
transaction_->Begin();
}
virtual void Rollback()
{
transaction_->Rollback();
}
virtual void Commit(int64_t fileSizeDelta /* only used in debug */)
{
transaction_->Commit();
assert(initialDiskSize_ + fileSizeDelta >= 0 &&
initialDiskSize_ + fileSizeDelta == static_cast(that_.GetTotalCompressedSize()));
}
};
IDatabaseWrapper::ITransaction* SQLiteDatabaseWrapper::StartTransaction()
{
return new Transaction(*this);
}
void SQLiteDatabaseWrapper::GetAllMetadata(std::map& target,
int64_t id)
{
target.clear();
SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT type, value FROM Metadata WHERE id=?");
s.BindInt64(0, id);
while (s.Step())
{
MetadataType key = static_cast(s.ColumnInt(0));
target[key] = s.ColumnString(1);
}
}
void SQLiteDatabaseWrapper::SetGlobalProperty(GlobalProperty property,
const std::string& value)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT OR REPLACE INTO GlobalProperties VALUES(?, ?)");
s.BindInt(0, property);
s.BindString(1, value);
s.Run();
}
bool SQLiteDatabaseWrapper::LookupGlobalProperty(std::string& target,
GlobalProperty property)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"SELECT value FROM GlobalProperties WHERE property=?");
s.BindInt(0, property);
if (!s.Step())
{
return false;
}
else
{
target = s.ColumnString(0);
return true;
}
}
int64_t SQLiteDatabaseWrapper::CreateResource(const std::string& publicId,
ResourceType type)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO Resources VALUES(NULL, ?, ?, NULL)");
s.BindInt(0, type);
s.BindString(1, publicId);
s.Run();
return db_.GetLastInsertRowId();
}
bool SQLiteDatabaseWrapper::LookupResource(int64_t& id,
ResourceType& type,
const std::string& publicId)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"SELECT internalId, resourceType FROM Resources WHERE publicId=?");
s.BindString(0, publicId);
if (!s.Step())
{
return false;
}
else
{
id = s.ColumnInt(0);
type = static_cast(s.ColumnInt(1));
// Check whether there is a single resource with this public id
assert(!s.Step());
return true;
}
}
void SQLiteDatabaseWrapper::AttachChild(int64_t parent,
int64_t child)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "UPDATE Resources SET parentId = ? WHERE internalId = ?");
s.BindInt64(0, parent);
s.BindInt64(1, child);
s.Run();
}
void SQLiteDatabaseWrapper::SetMetadata(int64_t id,
MetadataType type,
const std::string& value)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT OR REPLACE INTO Metadata VALUES(?, ?, ?)");
s.BindInt64(0, id);
s.BindInt(1, type);
s.BindString(2, value);
s.Run();
}
void SQLiteDatabaseWrapper::DeleteMetadata(int64_t id,
MetadataType type)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM Metadata WHERE id=? and type=?");
s.BindInt64(0, id);
s.BindInt(1, type);
s.Run();
}
bool SQLiteDatabaseWrapper::LookupMetadata(std::string& target,
int64_t id,
MetadataType type)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"SELECT value FROM Metadata WHERE id=? AND type=?");
s.BindInt64(0, id);
s.BindInt(1, type);
if (!s.Step())
{
return false;
}
else
{
target = s.ColumnString(0);
return true;
}
}
void SQLiteDatabaseWrapper::AddAttachment(int64_t id,
const FileInfo& attachment)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO AttachedFiles VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
s.BindInt64(0, id);
s.BindInt(1, attachment.GetContentType());
s.BindString(2, attachment.GetUuid());
s.BindInt64(3, attachment.GetCompressedSize());
s.BindInt64(4, attachment.GetUncompressedSize());
s.BindInt(5, attachment.GetCompressionType());
s.BindString(6, attachment.GetUncompressedMD5());
s.BindString(7, attachment.GetCompressedMD5());
s.Run();
}
void SQLiteDatabaseWrapper::DeleteAttachment(int64_t id,
FileContentType attachment)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM AttachedFiles WHERE id=? AND fileType=?");
s.BindInt64(0, id);
s.BindInt(1, attachment);
s.Run();
}
void SQLiteDatabaseWrapper::ListAvailableAttachments(std::list& target,
int64_t id)
{
target.clear();
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"SELECT fileType FROM AttachedFiles WHERE id=?");
s.BindInt64(0, id);
while (s.Step())
{
target.push_back(static_cast(s.ColumnInt(0)));
}
}
bool SQLiteDatabaseWrapper::LookupAttachment(FileInfo& attachment,
int64_t id,
FileContentType contentType)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"SELECT uuid, uncompressedSize, compressionType, compressedSize, "
"uncompressedMD5, compressedMD5 FROM AttachedFiles WHERE id=? AND fileType=?");
s.BindInt64(0, id);
s.BindInt(1, contentType);
if (!s.Step())
{
return false;
}
else
{
attachment = FileInfo(s.ColumnString(0),
contentType,
s.ColumnInt64(1),
s.ColumnString(4),
static_cast(s.ColumnInt(2)),
s.ColumnInt64(3),
s.ColumnString(5));
return true;
}
}
void SQLiteDatabaseWrapper::ClearMainDicomTags(int64_t id)
{
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM DicomIdentifiers WHERE id=?");
s.BindInt64(0, id);
s.Run();
}
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM MainDicomTags WHERE id=?");
s.BindInt64(0, id);
s.Run();
}
}
void SQLiteDatabaseWrapper::SetMainDicomTag(int64_t id,
const DicomTag& tag,
const std::string& value)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO MainDicomTags VALUES(?, ?, ?, ?)");
s.BindInt64(0, id);
s.BindInt(1, tag.GetGroup());
s.BindInt(2, tag.GetElement());
s.BindString(3, value);
s.Run();
}
void SQLiteDatabaseWrapper::SetIdentifierTag(int64_t id,
const DicomTag& tag,
const std::string& value)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO DicomIdentifiers VALUES(?, ?, ?, ?)");
s.BindInt64(0, id);
s.BindInt(1, tag.GetGroup());
s.BindInt(2, tag.GetElement());
s.BindString(3, value);
s.Run();
}
void SQLiteDatabaseWrapper::GetMainDicomTags(DicomMap& map,
int64_t id)
{
map.Clear();
SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM MainDicomTags WHERE id=?");
s.BindInt64(0, id);
while (s.Step())
{
map.SetValue(s.ColumnInt(1),
s.ColumnInt(2),
s.ColumnString(3), false);
}
}
void SQLiteDatabaseWrapper::GetChildrenPublicId(std::list& target,
int64_t id)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT a.publicId FROM Resources AS a, Resources AS b "
"WHERE a.parentId = b.internalId AND b.internalId = ?");
s.BindInt64(0, id);
target.clear();
while (s.Step())
{
target.push_back(s.ColumnString(0));
}
}
void SQLiteDatabaseWrapper::GetChildrenInternalId(std::list& target,
int64_t id)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT a.internalId FROM Resources AS a, Resources AS b "
"WHERE a.parentId = b.internalId AND b.internalId = ?");
s.BindInt64(0, id);
target.clear();
while (s.Step())
{
target.push_back(s.ColumnInt64(0));
}
}
void SQLiteDatabaseWrapper::LogChange(int64_t internalId,
const ServerIndexChange& change)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO Changes VALUES(NULL, ?, ?, ?, ?)");
s.BindInt(0, change.GetChangeType());
s.BindInt64(1, internalId);
s.BindInt(2, change.GetResourceType());
s.BindString(3, change.GetDate());
s.Run();
}
void SQLiteDatabaseWrapper::LogExportedResource(const ExportedResource& resource)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"INSERT INTO ExportedResources VALUES(NULL, ?, ?, ?, ?, ?, ?, ?, ?)");
s.BindInt(0, resource.GetResourceType());
s.BindString(1, resource.GetPublicId());
s.BindString(2, resource.GetModality());
s.BindString(3, resource.GetPatientId());
s.BindString(4, resource.GetStudyInstanceUid());
s.BindString(5, resource.GetSeriesInstanceUid());
s.BindString(6, resource.GetSopInstanceUid());
s.BindString(7, resource.GetDate());
s.Run();
}
void SQLiteDatabaseWrapper::GetExportedResources(std::list& target,
bool& done,
int64_t since,
uint32_t maxResults)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"SELECT * FROM ExportedResources WHERE seq>? ORDER BY seq LIMIT ?");
s.BindInt64(0, since);
s.BindInt(1, maxResults + 1);
GetExportedResourcesInternal(target, done, s, maxResults);
}
void SQLiteDatabaseWrapper::GetLastExportedResource(std::list& target)
{
bool done; // Ignored
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"SELECT * FROM ExportedResources ORDER BY seq DESC LIMIT 1");
GetExportedResourcesInternal(target, done, s, 1);
}
uint64_t SQLiteDatabaseWrapper::GetTotalCompressedSize()
{
// Old SQL query that was used in Orthanc <= 1.5.0:
// SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT SUM(compressedSize) FROM AttachedFiles");
SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT value FROM GlobalIntegers WHERE key=0");
s.Run();
return static_cast(s.ColumnInt64(0));
}
uint64_t SQLiteDatabaseWrapper::GetTotalUncompressedSize()
{
// Old SQL query that was used in Orthanc <= 1.5.0:
// SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT SUM(uncompressedSize) FROM AttachedFiles");
SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT value FROM GlobalIntegers WHERE key=1");
s.Run();
return static_cast(s.ColumnInt64(0));
}
uint64_t SQLiteDatabaseWrapper::GetResourceCount(ResourceType resourceType)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"SELECT COUNT(*) FROM Resources WHERE resourceType=?");
s.BindInt(0, resourceType);
if (!s.Step())
{
return 0;
}
else
{
int64_t c = s.ColumnInt(0);
assert(!s.Step());
return c;
}
}
void SQLiteDatabaseWrapper::GetAllPublicIds(std::list& target,
ResourceType resourceType)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId FROM Resources WHERE resourceType=?");
s.BindInt(0, resourceType);
target.clear();
while (s.Step())
{
target.push_back(s.ColumnString(0));
}
}
void SQLiteDatabaseWrapper::GetAllPublicIds(std::list& target,
ResourceType resourceType,
size_t since,
size_t limit)
{
if (limit == 0)
{
target.clear();
return;
}
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"SELECT publicId FROM Resources WHERE "
"resourceType=? LIMIT ? OFFSET ?");
s.BindInt(0, resourceType);
s.BindInt64(1, limit);
s.BindInt64(2, since);
target.clear();
while (s.Step())
{
target.push_back(s.ColumnString(0));
}
}
bool SQLiteDatabaseWrapper::SelectPatientToRecycle(int64_t& internalId)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"SELECT patientId FROM PatientRecyclingOrder ORDER BY seq ASC LIMIT 1");
if (!s.Step())
{
// No patient remaining or all the patients are protected
return false;
}
else
{
internalId = s.ColumnInt(0);
return true;
}
}
bool SQLiteDatabaseWrapper::SelectPatientToRecycle(int64_t& internalId,
int64_t patientIdToAvoid)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"SELECT patientId FROM PatientRecyclingOrder "
"WHERE patientId != ? ORDER BY seq ASC LIMIT 1");
s.BindInt64(0, patientIdToAvoid);
if (!s.Step())
{
// No patient remaining or all the patients are protected
return false;
}
else
{
internalId = s.ColumnInt(0);
return true;
}
}
bool SQLiteDatabaseWrapper::IsProtectedPatient(int64_t internalId)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"SELECT * FROM PatientRecyclingOrder WHERE patientId = ?");
s.BindInt64(0, internalId);
return !s.Step();
}
void SQLiteDatabaseWrapper::SetProtectedPatient(int64_t internalId,
bool isProtected)
{
if (isProtected)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM PatientRecyclingOrder WHERE patientId=?");
s.BindInt64(0, internalId);
s.Run();
}
else if (IsProtectedPatient(internalId))
{
SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO PatientRecyclingOrder VALUES(NULL, ?)");
s.BindInt64(0, internalId);
s.Run();
}
else
{
// Nothing to do: The patient is already unprotected
}
}
bool SQLiteDatabaseWrapper::IsExistingResource(int64_t internalId)
{
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"SELECT * FROM Resources WHERE internalId=?");
s.BindInt64(0, internalId);
return s.Step();
}
bool SQLiteDatabaseWrapper::IsDiskSizeAbove(uint64_t threshold)
{
return GetTotalCompressedSize() > threshold;
}
class SQLiteDatabaseWrapper::LookupFormatter : public ISqlLookupFormatter
{
private:
std::list values_;
public:
virtual std::string GenerateParameter(const std::string& value)
{
values_.push_back(value);
return "?";
}
virtual std::string FormatResourceType(ResourceType level)
{
return boost::lexical_cast(level);
}
virtual std::string FormatWildcardEscape()
{
return "ESCAPE '\\'";
}
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);
}
}
};
static void AnswerLookup(std::list& resourcesId,
std::list& instancesId,
SQLite::Connection& db,
ResourceType level)
{
resourcesId.clear();
instancesId.clear();
std::unique_ptr 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));
}
}
void SQLiteDatabaseWrapper::ApplyLookupResources(std::list& resourcesId,
std::list* instancesId,
const std::vector& lookup,
ResourceType queryLevel,
size_t limit)
{
LookupFormatter formatter;
std::string sql;
LookupFormatter::Apply(sql, formatter, lookup, queryLevel, limit);
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();
}
if (instancesId != NULL)
{
AnswerLookup(resourcesId, *instancesId, db_, queryLevel);
}
else
{
resourcesId.clear();
SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId FROM Lookup");
while (s.Step())
{
resourcesId.push_back(s.ColumnString(0));
}
}
}
int64_t SQLiteDatabaseWrapper::GetLastChangeIndex()
{
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"SELECT seq FROM sqlite_sequence WHERE name='Changes'");
if (s.Step())
{
int64_t c = s.ColumnInt(0);
assert(!s.Step());
return c;
}
else
{
// No change has been recorded so far in the database
return 0;
}
}
void SQLiteDatabaseWrapper::TagMostRecentPatient(int64_t patient)
{
{
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"DELETE FROM PatientRecyclingOrder WHERE patientId=?");
s.BindInt64(0, patient);
s.Run();
assert(db_.GetLastChangeCount() == 0 ||
db_.GetLastChangeCount() == 1);
if (db_.GetLastChangeCount() == 0)
{
// The patient was protected, there was nothing to delete from the recycling order
return;
}
}
{
SQLite::Statement s(db_, SQLITE_FROM_HERE,
"INSERT INTO PatientRecyclingOrder VALUES(NULL, ?)");
s.BindInt64(0, patient);
s.Run();
}
}
}