# HG changeset patch # User Alain Mazy # Date 1663225327 -7200 # Node ID 9770d537880ded5e8302b39f035716736f4f5e57 # Parent 75e949689c0857a6f29da634627bb5bb4381dbcd added support for revision in SQLite + avoid upgrading DB version diff -r 75e949689c08 -r 9770d537880d NEWS --- a/NEWS Wed Sep 14 17:11:45 2022 +0200 +++ b/NEWS Thu Sep 15 09:02:07 2022 +0200 @@ -1,3 +1,22 @@ +Pending changes in the mainline +=============================== + +General +------- + +* SQLite default DB engine now supports metadata and attachment revisions +* Upgraded the DB to allow plugins to store customData for each attachment. +* New sample Advanced Storage plugin that allows: + - using multiple disk for image storage + - use more human friendly storage structure (experimental feature) + +Plugins +------- + +* New database plugin SDK (v4) to handle customData for attachments. +* New storage plugin SDK (v3) to handle customData for attachments, + + version 1.11.2 (2022-08-30) =========================== diff -r 75e949689c08 -r 9770d537880d OrthancFramework/Resources/CMake/OrthancFrameworkParameters.cmake --- a/OrthancFramework/Resources/CMake/OrthancFrameworkParameters.cmake Wed Sep 14 17:11:45 2022 +0200 +++ b/OrthancFramework/Resources/CMake/OrthancFrameworkParameters.cmake Thu Sep 15 09:02:07 2022 +0200 @@ -33,8 +33,7 @@ # * Orthanc 0.7.3 -> Orthanc 0.8.4 = version 4 # * Orthanc 0.8.5 -> Orthanc 0.9.4 = version 5 # * Orthanc 0.9.5 -> Orthanc 1.11.X = version 6 -# * Orthanc 1.12.0 -> mainline = version 7 -set(ORTHANC_DATABASE_VERSION 7) +set(ORTHANC_DATABASE_VERSION 6) # Version of the Orthanc API, can be retrieved from "/system" URI in # order to check whether new URI endpoints are available even if using diff -r 75e949689c08 -r 9770d537880d OrthancServer/CMakeLists.txt --- a/OrthancServer/CMakeLists.txt Wed Sep 14 17:11:45 2022 +0200 +++ b/OrthancServer/CMakeLists.txt Thu Sep 15 09:02:07 2022 +0200 @@ -230,7 +230,8 @@ PREPARE_DATABASE ${CMAKE_SOURCE_DIR}/Sources/Database/PrepareDatabase.sql UPGRADE_DATABASE_3_TO_4 ${CMAKE_SOURCE_DIR}/Sources/Database/Upgrade3To4.sql UPGRADE_DATABASE_4_TO_5 ${CMAKE_SOURCE_DIR}/Sources/Database/Upgrade4To5.sql - UPGRADE_DATABASE_6_TO_7 ${CMAKE_SOURCE_DIR}/Sources/Database/Upgrade6To7.sql + INSTALL_REVISION_AND_CUSTOM_DATA + ${CMAKE_SOURCE_DIR}/Sources/Database/InstallRevisionAndCustomData.sql INSTALL_TRACK_ATTACHMENTS_SIZE ${CMAKE_SOURCE_DIR}/Sources/Database/InstallTrackAttachmentsSize.sql diff -r 75e949689c08 -r 9770d537880d OrthancServer/Sources/Database/InstallRevisionAndCustomData.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/OrthancServer/Sources/Database/InstallRevisionAndCustomData.sql Thu Sep 15 09:02:07 2022 +0200 @@ -0,0 +1,66 @@ +-- Orthanc - A Lightweight, RESTful DICOM Store +-- Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics +-- Department, University Hospital of Liege, Belgium +-- Copyright (C) 2017-2022 Osimis S.A., Belgium +-- Copyright (C) 2021-2022 Sebastien Jodogne, ICTEAM UCLouvain, 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. +-- +-- 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 . + + +-- +-- This SQLite script updates the version of the Orthanc database from 6 to 7. +-- + +-- Add new columns for revision +ALTER TABLE Metadata ADD COLUMN revision INTEGER; +ALTER TABLE AttachedFiles ADD COLUMN revision INTEGER; + +-- Add new column for customData +ALTER TABLE AttachedFiles ADD COLUMN customData TEXT; + + +-- add another AttachedFileDeleted trigger +-- We want to keep backward compatibility and avoid changing the database version number (which would force +-- users to upgrade the DB). By keeping backward compatibility, we mean "allow a user to run a previous Orthanc +-- version after it has run this update script". +-- We must keep the signature of the initial trigger (it is impossible to have 2 triggers on the same event). +-- We tried adding a trigger on "BEFORE DELETE" but then it is being called when running the previous Orthanc +-- which makes it fail. +-- But, we need the customData in the C++ function that is called when a AttachedFiles is deleted. +-- The trick is then to save the customData in a DeletedFiles table. +-- The SignalFileDeleted C++ function will then get the customData from this table and delete the entry. +-- Drawback: if you downgrade Orthanc, the DeletedFiles table will remain and will be populated by the trigger +-- but not consumed by the C++ function -> we consider this is an acceptable drawback for a few people compared +-- to the burden of upgrading the DB. + +CREATE TABLE DeletedFiles( + uuid TEXT NOT NULL, -- 0 + customData TEXT -- 1 +); + +DROP TRIGGER AttachedFileDeleted; + +CREATE TRIGGER AttachedFileDeleted +AFTER DELETE ON AttachedFiles +BEGIN + INSERT INTO DeletedFiles VALUES(old.uuid, old.customData); + SELECT SignalFileDeleted(old.uuid, old.fileType, old.uncompressedSize, + old.compressionType, old.compressedSize, + old.uncompressedMD5, old.compressedMD5 + ); +END; + +-- Record that this upgrade has been performed + +INSERT INTO GlobalProperties VALUES (7, 1); -- GlobalProperty_SQLiteHasCustomDataAndRevision diff -r 75e949689c08 -r 9770d537880d OrthancServer/Sources/Database/PrepareDatabase.sql --- a/OrthancServer/Sources/Database/PrepareDatabase.sql Wed Sep 14 17:11:45 2022 +0200 +++ b/OrthancServer/Sources/Database/PrepareDatabase.sql Thu Sep 15 09:02:07 2022 +0200 @@ -51,6 +51,7 @@ id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, type INTEGER, value TEXT, + -- revision INTEGER, -- New in Orthanc 1.12.0 (added in InstallRevisionAndCustomData.sql) PRIMARY KEY(id, type) ); @@ -63,7 +64,8 @@ compressionType INTEGER, uncompressedMD5 TEXT, -- New in Orthanc 0.7.3 (database v4) compressedMD5 TEXT, -- New in Orthanc 0.7.3 (database v4) - customData TEXT, -- New in Orthanc 1.12.0 (database v7) + -- revision INTEGER, -- New in Orthanc 1.12.0 (added in InstallRevisionAndCustomData.sql) + -- customData TEXT, -- New in Orthanc 1.12.0 (added in InstallRevisionAndCustomData.sql) PRIMARY KEY(id, fileType) ); @@ -115,9 +117,8 @@ SELECT SignalFileDeleted(old.uuid, old.fileType, old.uncompressedSize, old.compressionType, old.compressedSize, -- These 2 arguments are new in Orthanc 0.7.3 (database v4) - old.uncompressedMD5, old.compressedMD5, - -- customData is new in Orthanc 1.12.0 (database v7) - old.customData); + old.uncompressedMD5, old.compressedMD5 + ); END; CREATE TRIGGER ResourceDeleted @@ -146,4 +147,4 @@ -- Set the version of the database schema -- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration -INSERT INTO GlobalProperties VALUES (1, "7"); +INSERT INTO GlobalProperties VALUES (1, "6"); diff -r 75e949689c08 -r 9770d537880d OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp --- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp Wed Sep 14 17:11:45 2022 +0200 +++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp Thu Sep 15 09:02:07 2022 +0200 @@ -39,6 +39,8 @@ #include #include +static std::map filesToDeleteCustomData; + namespace Orthanc { class SQLiteDatabaseWrapper::LookupFormatter : public ISqlLookupFormatter @@ -323,10 +325,9 @@ const FileInfo& attachment, int64_t revision) ORTHANC_OVERRIDE { - // TODO - REVISIONS SQLite::Statement s(db_, SQLITE_FROM_HERE, - "INSERT INTO AttachedFiles (id, fileType, uuid, compressedSize, uncompressedSize, compressionType, uncompressedMD5, compressedMD5, customData) " - "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)"); + "INSERT INTO AttachedFiles (id, fileType, uuid, compressedSize, uncompressedSize, compressionType, uncompressedMD5, compressedMD5, revision, customData) " + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); s.BindInt64(0, id); s.BindInt(1, attachment.GetContentType()); s.BindString(2, attachment.GetUuid()); @@ -335,7 +336,8 @@ s.BindInt(5, attachment.GetCompressionType()); s.BindString(6, attachment.GetUncompressedMD5()); s.BindString(7, attachment.GetCompressedMD5()); - s.BindString(8, attachment.GetCustomData()); + s.BindInt(8, revision); + s.BindString(9, attachment.GetCustomData()); s.Run(); } @@ -478,6 +480,28 @@ } } + void DeleteDeletedFile(const std::string& uuid) + { + SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM DeletedFiles WHERE uuid=?"); + s.BindString(0, uuid); + s.Run(); + } + + void GetDeletedFileCustomData(std::string& customData, const std::string& uuid) + { + SQLite::Statement s(db_, SQLITE_FROM_HERE, + "SELECT customData FROM DeletedFiles WHERE uuid=?"); + s.BindString(0, uuid); + + if (s.Step()) + { + customData = s.ColumnString(0); + } + else + { + throw OrthancException(ErrorCode_UnknownResource); + } + } virtual void GetAllMetadata(std::map& target, int64_t id) ORTHANC_OVERRIDE @@ -803,7 +827,7 @@ { SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT uuid, uncompressedSize, compressionType, compressedSize, " - "uncompressedMD5, compressedMD5, customData FROM AttachedFiles WHERE id=? AND fileType=?"); + "uncompressedMD5, compressedMD5, revision, customData FROM AttachedFiles WHERE id=? AND fileType=?"); s.BindInt64(0, id); s.BindInt(1, contentType); @@ -820,8 +844,8 @@ static_cast(s.ColumnInt(2)), s.ColumnInt64(3), s.ColumnString(5), - s.ColumnString(6)); - revision = 0; // TODO - REVISIONS + s.ColumnString(7)); + revision = s.ColumnInt(6); return true; } } @@ -856,7 +880,7 @@ MetadataType type) ORTHANC_OVERRIDE { SQLite::Statement s(db_, SQLITE_FROM_HERE, - "SELECT value FROM Metadata WHERE id=? AND type=?"); + "SELECT value, revision FROM Metadata WHERE id=? AND type=?"); s.BindInt64(0, id); s.BindInt(1, type); @@ -867,7 +891,7 @@ else { target = s.ColumnString(0); - revision = 0; // TODO - REVISIONS + revision = s.ColumnInt(1); return true; } } @@ -1039,11 +1063,11 @@ const std::string& value, int64_t revision) ORTHANC_OVERRIDE { - // TODO - REVISIONS - SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT OR REPLACE INTO Metadata VALUES(?, ?, ?)"); + SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT OR REPLACE INTO Metadata (id, type, value, revision) VALUES(?, ?, ?, ?)"); s.BindInt64(0, id); s.BindInt(1, type); s.BindString(2, value); + s.BindInt(3, revision); s.Run(); } @@ -1101,14 +1125,19 @@ virtual unsigned int GetCardinality() const ORTHANC_OVERRIDE { - return 8; + return 7; } virtual void Compute(SQLite::FunctionContext& context) ORTHANC_OVERRIDE { if (sqlite_.activeTransaction_ != NULL) { - std::string uncompressedMD5, compressedMD5, customData; + std::string id = context.GetStringValue(0); + + std::string customData; + sqlite_.activeTransaction_->GetDeletedFileCustomData(customData, id); + + std::string uncompressedMD5, compressedMD5; if (!context.IsNullValue(5)) { @@ -1120,11 +1149,6 @@ compressedMD5 = context.GetStringValue(6); } - if (!context.IsNullValue(7)) - { - customData = context.GetStringValue(7); - } - FileInfo info(context.GetStringValue(0), static_cast(context.GetIntValue(1)), static_cast(context.GetInt64Value(2)), @@ -1135,6 +1159,7 @@ customData); sqlite_.activeTransaction_->GetListener().SignalAttachmentDeleted(info); + sqlite_.activeTransaction_->DeleteDeletedFile(id); } } }; @@ -1362,8 +1387,8 @@ // New in Orthanc 1.5.1 if (version_ >= 6) { - if (!transaction->LookupGlobalProperty(tmp, GlobalProperty_GetTotalSizeIsFast, true /* unused in SQLite */) || - tmp != "1") + if (!transaction->LookupGlobalProperty(tmp, GlobalProperty_GetTotalSizeIsFast, true /* unused in SQLite */) + || tmp != "1") { LOG(INFO) << "Installing the SQLite triggers to track the size of the attachments"; std::string query; @@ -1372,6 +1397,19 @@ } } + // New in Orthanc 1.12.0 + if (version_ >= 6) + { + if (!transaction->LookupGlobalProperty(tmp, GlobalProperty_SQLiteHasCustomDataAndRevision, true /* unused in SQLite */) + || tmp != "1") + { + LOG(INFO) << "Upgrading SQLite schema to support revision and customData"; + std::string query; + ServerResources::GetFileResource(query, ServerResources::INSTALL_REVISION_AND_CUSTOM_DATA); + db_.Execute(query); + } + } + transaction->Commit(0); } } @@ -1455,13 +1493,6 @@ version_ = 6; } - if (version_ == 6) - { - LOG(WARNING) << "Upgrading database version from 6 to 7"; - ExecuteUpgradeScript(db_, ServerResources::UPGRADE_DATABASE_6_TO_7); - version_ = 7; - } - } diff -r 75e949689c08 -r 9770d537880d OrthancServer/Sources/Database/SQLiteDatabaseWrapper.h --- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.h Wed Sep 14 17:11:45 2022 +0200 +++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.h Thu Sep 15 09:02:07 2022 +0200 @@ -94,7 +94,7 @@ virtual bool HasRevisionsSupport() const ORTHANC_OVERRIDE { - return false; // TODO - REVISIONS + return true; } virtual bool HasAttachmentCustomDataSupport() const ORTHANC_OVERRIDE diff -r 75e949689c08 -r 9770d537880d OrthancServer/Sources/Database/Upgrade6To7.sql --- a/OrthancServer/Sources/Database/Upgrade6To7.sql Wed Sep 14 17:11:45 2022 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,46 +0,0 @@ --- Orthanc - A Lightweight, RESTful DICOM Store --- Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics --- Department, University Hospital of Liege, Belgium --- Copyright (C) 2017-2022 Osimis S.A., Belgium --- Copyright (C) 2021-2022 Sebastien Jodogne, ICTEAM UCLouvain, 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. --- --- 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 . - - --- --- This SQLite script updates the version of the Orthanc database from 6 to 7. --- - --- Add a new column to AttachedFiles - -ALTER TABLE AttachedFiles ADD COLUMN customData TEXT; - --- update the triggers -DROP TRIGGER AttachedFileDeleted; - -CREATE TRIGGER AttachedFileDeleted -AFTER DELETE ON AttachedFiles -BEGIN - SELECT SignalFileDeleted(old.uuid, old.fileType, old.uncompressedSize, - old.compressionType, old.compressedSize, - -- These 2 arguments are new in Orthanc 0.7.3 (database v4) - old.uncompressedMD5, old.compressedMD5, - -- Next argument new in Orthanc 1.12.0 (database v7) - old.customData); -END; - --- Change the database version --- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration - -UPDATE GlobalProperties SET value="7" WHERE property=1; diff -r 75e949689c08 -r 9770d537880d OrthancServer/Sources/ServerEnumerations.h --- a/OrthancServer/Sources/ServerEnumerations.h Wed Sep 14 17:11:45 2022 +0200 +++ b/OrthancServer/Sources/ServerEnumerations.h Thu Sep 15 09:02:07 2022 +0200 @@ -125,6 +125,7 @@ GlobalProperty_AnonymizationSequence = 3, GlobalProperty_JobsRegistry = 5, GlobalProperty_GetTotalSizeIsFast = 6, // New in Orthanc 1.5.2 + GlobalProperty_SQLiteHasCustomDataAndRevision = 7, // New in Orthanc 1.12.0 GlobalProperty_Modalities = 20, // New in Orthanc 1.5.0 GlobalProperty_Peers = 21, // New in Orthanc 1.5.0 diff -r 75e949689c08 -r 9770d537880d OrthancServer/UnitTestsSources/ServerIndexTests.cpp --- a/OrthancServer/UnitTestsSources/ServerIndexTests.cpp Wed Sep 14 17:11:45 2022 +0200 +++ b/OrthancServer/UnitTestsSources/ServerIndexTests.cpp Thu Sep 15 09:02:07 2022 +0200 @@ -295,7 +295,6 @@ transaction_->AddAttachment(a[4], FileInfo("my dicom file", FileContentType_Dicom, 42, "md5", "customData"), 43); transaction_->AddAttachment(a[6], FileInfo("world", FileContentType_Dicom, 44, "md5", "customData"), 44); - // TODO - REVISIONS - "42" is revision number, that is not currently stored (*) transaction_->SetMetadata(a[4], MetadataType_RemoteAet, "PINNACLE", 42); transaction_->GetAllMetadata(md, a[4]); @@ -334,17 +333,17 @@ int64_t revision; ASSERT_TRUE(transaction_->LookupMetadata(s, revision, a[4], MetadataType_RemoteAet)); - ASSERT_EQ(0, revision); // "0" instead of "42" because of (*) + ASSERT_EQ(42, revision); ASSERT_FALSE(transaction_->LookupMetadata(s, revision, a[4], MetadataType_Instance_IndexInSeries)); - ASSERT_EQ(0, revision); + ASSERT_EQ(42, revision); ASSERT_EQ("PINNACLE", s); std::string u; ASSERT_TRUE(transaction_->LookupMetadata(u, revision, a[4], MetadataType_RemoteAet)); - ASSERT_EQ(0, revision); + ASSERT_EQ(42, revision); ASSERT_EQ("PINNACLE", u); ASSERT_FALSE(transaction_->LookupMetadata(u, revision, a[4], MetadataType_Instance_IndexInSeries)); - ASSERT_EQ(0, revision); + ASSERT_EQ(42, revision); ASSERT_TRUE(transaction_->LookupGlobalProperty(s, GlobalProperty_FlushSleep, true)); ASSERT_FALSE(transaction_->LookupGlobalProperty(s, static_cast(42), true)); @@ -352,7 +351,7 @@ FileInfo att; ASSERT_TRUE(transaction_->LookupAttachment(att, revision, a[4], FileContentType_DicomAsJson)); - ASSERT_EQ(0, revision); // "0" instead of "42" because of (*) + ASSERT_EQ(42, revision); ASSERT_EQ("my json file", att.GetUuid()); ASSERT_EQ(21u, att.GetCompressedSize()); ASSERT_EQ("md5", att.GetUncompressedMD5()); @@ -361,7 +360,7 @@ ASSERT_EQ(CompressionType_ZlibWithSize, att.GetCompressionType()); ASSERT_TRUE(transaction_->LookupAttachment(att, revision, a[6], FileContentType_Dicom)); - ASSERT_EQ(0, revision); // "0" instead of "42" because of (*) + ASSERT_EQ(44, revision); ASSERT_EQ("world", att.GetUuid()); ASSERT_EQ(44u, att.GetCompressedSize()); ASSERT_EQ("md5", att.GetUncompressedMD5()); @@ -397,11 +396,11 @@ CheckTableRecordCount(0, "Resources"); CheckTableRecordCount(0, "AttachedFiles"); - CheckTableRecordCount(3, "GlobalProperties"); + CheckTableRecordCount(4, "GlobalProperties"); std::string tmp; ASSERT_TRUE(transaction_->LookupGlobalProperty(tmp, GlobalProperty_DatabaseSchemaVersion, true)); - ASSERT_EQ("7", tmp); + ASSERT_EQ("6", tmp); ASSERT_TRUE(transaction_->LookupGlobalProperty(tmp, GlobalProperty_FlushSleep, true)); ASSERT_EQ("World", tmp); ASSERT_TRUE(transaction_->LookupGlobalProperty(tmp, GlobalProperty_GetTotalSizeIsFast, true)); diff -r 75e949689c08 -r 9770d537880d TODO --- a/TODO Wed Sep 14 17:11:45 2022 +0200 +++ b/TODO Thu Sep 15 09:02:07 2022 +0200 @@ -1,16 +1,34 @@ TODO_CUSTOM_DATA branch -- add REVISIONS in SQLite since we change the DB schema +- add REVISIONS in AttachedFiles + Metadata in SQLite since we change the DB schema - add revisions and customData in all Database plugins - check if we can play with GlobalProperty_DatabasePatchLevel instead of upgrading the DB version ! - upgrade DB automatically such that it does not need a specific launch with --update , add --downgrade + --no-auto-upgrade command lines -- expand the DB plugin SDK to handle customDATA -- implement OrthancPluginDataBaseV4 and refuse to instantiate a PluginStorage3 if a DBv4 is not instantiated ! +- refuse to instantiate a PluginStorage3 if a DBv4 is not instantiated ! - handle all TODO_CUSTOM_DATA - check /attachments/... routes for path returned - AdvancedStoragePlugin - - show warning if a tag is missing when generating the path from tags (option to disable this warning) + - show warning if a tag is missing when generating the path from tags (+ option to disable this warning) - generate path from tags from resource (CreateAttachment) + - add an instanceId or parentSeriesId arg in CreateInstance ? - implement a 'legacy' root path to group all files with missing tags or path too long + - document that, once you have used the AdvancedStoragePlugin and stored DICOM files, you can not downgrade Orthanc to a previous Orthanc + without loosing access to the DICOM files + - write integration test for advanced-storage: + - launch 1.11.2 + - upload 1 file + - launch 1.12.0 with advanced-storage plugin with a non default namingScheme + - upload 1 file + - access + delete initial file + +- write integration test for transitions from one DB to the other (for each DB plugin): + - launch 1.11.2, + - upload 2 files, + - launch 1.12.0, + - access + delete one file, + - upload one file, + - launch 1.11.2 again, + - access + delete last 2 files + ======================= === Orthanc Roadmap ===