# HG changeset patch # User Sebastien Jodogne # Date 1748878824 -7200 # Node ID d6d8c8c2dcc45d110f302df9ca154b4b1b534d7b # Parent 18fc493db0d2b45f0508759303a9de53e5063048 refactoring InstallRevisionAndCustomData.sql diff -r 18fc493db0d2 -r d6d8c8c2dcc4 OrthancServer/CMakeLists.txt --- a/OrthancServer/CMakeLists.txt Mon Jun 02 17:10:37 2025 +0200 +++ b/OrthancServer/CMakeLists.txt Mon Jun 02 17:40:24 2025 +0200 @@ -253,6 +253,7 @@ INSTALL_TRACK_ATTACHMENTS_SIZE ${CMAKE_SOURCE_DIR}/Sources/Database/InstallTrackAttachmentsSize.sql INSTALL_LABELS_TABLE ${CMAKE_SOURCE_DIR}/Sources/Database/InstallLabelsTable.sql INSTALL_REVISION_AND_CUSTOM_DATA ${CMAKE_SOURCE_DIR}/Sources/Database/InstallRevisionAndCustomData.sql + INSTALL_DELETED_FILES ${CMAKE_SOURCE_DIR}/Sources/Database/InstallDeletedFiles.sql INSTALL_KEY_VALUE_STORES_AND_QUEUES ${CMAKE_SOURCE_DIR}/Sources/Database/InstallKeyValueStoresAndQueues.sql ) diff -r 18fc493db0d2 -r d6d8c8c2dcc4 OrthancServer/Sources/Database/InstallDeletedFiles.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/OrthancServer/Sources/Database/InstallDeletedFiles.sql Mon Jun 02 17:40:24 2025 +0200 @@ -0,0 +1,55 @@ +-- 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 . + + +CREATE TABLE DeletedFiles( + uuid TEXT NOT NULL, -- 0 + customData TEXT -- 1 +); + +-- We need to use another AttachedFileDeleted trigger than the legacy one in "Upgrade4To5.sql". +-- +-- 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 preserve 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. + +DROP TRIGGER IF EXISTS 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 18fc493db0d2 -r d6d8c8c2dcc4 OrthancServer/Sources/Database/InstallRevisionAndCustomData.sql --- a/OrthancServer/Sources/Database/InstallRevisionAndCustomData.sql Mon Jun 02 17:10:37 2025 +0200 +++ b/OrthancServer/Sources/Database/InstallRevisionAndCustomData.sql Mon Jun 02 17:40:24 2025 +0200 @@ -28,39 +28,3 @@ -- 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 18fc493db0d2 -r d6d8c8c2dcc4 OrthancServer/Sources/Database/PrepareDatabase.sql --- a/OrthancServer/Sources/Database/PrepareDatabase.sql Mon Jun 02 17:10:37 2025 +0200 +++ b/OrthancServer/Sources/Database/PrepareDatabase.sql Mon Jun 02 17:40:24 2025 +0200 @@ -145,34 +145,13 @@ ${INSTALL_LABELS_TABLE} --- new in Orthanc 1.12.8 ------------------------- equivalent to InstallRevisionAndCustomData.sql - -CREATE TABLE DeletedFiles( - uuid TEXT NOT NULL, -- 0 - customData TEXT -- 1 -); - -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 - ---------------------------------------------------- +-- new in Orthanc 1.12.8 ------------------------- equivalent to InstallDeletedFiles.sql +${INSTALL_DELETED_FILES} -- new in Orthanc 1.12.8 ------------------------- equivalent to InstallKeyValueStoresAndQueues.sql ${INSTALL_KEY_VALUE_STORES_AND_QUEUES} ---------------------------------------------------- -- Set the version of the database schema -- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration diff -r 18fc493db0d2 -r d6d8c8c2dcc4 OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp --- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp Mon Jun 02 17:10:37 2025 +0200 +++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp Mon Jun 02 17:40:24 2025 +0200 @@ -2637,6 +2637,7 @@ InjectEmbeddedScript(query, "${INSTALL_TRACK_ATTACHMENTS_SIZE}", ServerResources::INSTALL_TRACK_ATTACHMENTS_SIZE); InjectEmbeddedScript(query, "${INSTALL_LABELS_TABLE}", ServerResources::INSTALL_LABELS_TABLE); + InjectEmbeddedScript(query, "${INSTALL_DELETED_FILES}", ServerResources::INSTALL_DELETED_FILES); InjectEmbeddedScript(query, "${INSTALL_KEY_VALUE_STORES_AND_QUEUES}", ServerResources::INSTALL_KEY_VALUE_STORES_AND_QUEUES); db_.Execute(query); @@ -2689,6 +2690,7 @@ { LOG(INFO) << "Upgrading SQLite schema to support revision and customData"; ExecuteEmbeddedScript(db_, ServerResources::INSTALL_REVISION_AND_CUSTOM_DATA); + ExecuteEmbeddedScript(db_, ServerResources::INSTALL_DELETED_FILES); } // New in Orthanc 1.12.8