# HG changeset patch
# User Sebastien Jodogne <s.jodogne@gmail.com>
# Date 1748877037 -7200
# Node ID 18fc493db0d2b45f0508759303a9de53e5063048
# Parent  2ee32b4433a51f39770a22e7829eaf90d138a627
removed redundancies between SQL scripts

diff -r 2ee32b4433a5 -r 18fc493db0d2 OrthancServer/CMakeLists.txt
--- a/OrthancServer/CMakeLists.txt	Mon Jun 02 16:34:30 2025 +0200
+++ b/OrthancServer/CMakeLists.txt	Mon Jun 02 17:10:37 2025 +0200
@@ -253,7 +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_KEY_VALUE_STORE_AND_QUEUES ${CMAKE_SOURCE_DIR}/Sources/Database/InstallKeyValueStoresAndQueues.sql
+  INSTALL_KEY_VALUE_STORES_AND_QUEUES ${CMAKE_SOURCE_DIR}/Sources/Database/InstallKeyValueStoresAndQueues.sql
   )
 
 if (STANDALONE_BUILD)
diff -r 2ee32b4433a5 -r 18fc493db0d2 OrthancServer/Sources/Database/PrepareDatabase.sql
--- a/OrthancServer/Sources/Database/PrepareDatabase.sql	Mon Jun 02 16:34:30 2025 +0200
+++ b/OrthancServer/Sources/Database/PrepareDatabase.sql	Mon Jun 02 17:10:37 2025 +0200
@@ -138,45 +138,11 @@
 
 
 -- new in Orthanc 1.5.1 -------------------------- equivalent to InstallTrackAttachmentsSize.sql
-
-CREATE TABLE GlobalIntegers(
-       key INTEGER PRIMARY KEY,
-       value INTEGER);
-
-INSERT INTO GlobalProperties VALUES (6, 1);  -- GlobalProperty_GetTotalSizeIsFast
-
-INSERT INTO GlobalIntegers SELECT 0, IFNULL(SUM(compressedSize), 0) FROM AttachedFiles;
-INSERT INTO GlobalIntegers SELECT 1, IFNULL(SUM(uncompressedSize), 0) FROM AttachedFiles;
-
-CREATE TRIGGER AttachedFileIncrementSize
-AFTER INSERT ON AttachedFiles
-BEGIN
-  UPDATE GlobalIntegers SET value = value + new.compressedSize WHERE key = 0;
-  UPDATE GlobalIntegers SET value = value + new.uncompressedSize WHERE key = 1;
-END;
-
-CREATE TRIGGER AttachedFileDecrementSize
-AFTER DELETE ON AttachedFiles
-BEGIN
-  UPDATE GlobalIntegers SET value = value - old.compressedSize WHERE key = 0;
-  UPDATE GlobalIntegers SET value = value - old.uncompressedSize WHERE key = 1;
-END;
-
---------------------------------------------------
+${INSTALL_TRACK_ATTACHMENTS_SIZE}
 
 
 -- new in Orthanc 1.12.0 ------------------------- equivalent to InstallLabelsTable.sql
-
-CREATE TABLE Labels(
-       id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
-       label TEXT NOT NULL,
-       PRIMARY KEY(id, label)  -- Prevents duplicates
-       );
-
-CREATE INDEX LabelsIndex1 ON Labels(id);
-CREATE INDEX LabelsIndex2 ON Labels(label);  -- This index allows efficient lookups
-
---------------------------------------------------
+${INSTALL_LABELS_TABLE}
 
 
 -- new in Orthanc 1.12.8 ------------------------- equivalent to InstallRevisionAndCustomData.sql
@@ -204,20 +170,7 @@
 
 
 -- new in Orthanc 1.12.8 ------------------------- equivalent to InstallKeyValueStoresAndQueues.sql
-CREATE TABLE KeyValueStores(
-       storeId TEXT NOT NULL,
-       key TEXT NOT NULL,
-       value BLOB NOT NULL,
-       PRIMARY KEY(storeId, key)  -- Prevents duplicates
-       );
-
-CREATE TABLE Queues (
-       id INTEGER PRIMARY KEY AUTOINCREMENT,
-       queueId TEXT NOT NULL,
-       value BLOB
-);
-
-CREATE INDEX QueuesIndex ON Queues (queueId, id);
+${INSTALL_KEY_VALUE_STORES_AND_QUEUES}
 
 ---------------------------------------------------
 
diff -r 2ee32b4433a5 -r 18fc493db0d2 OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp
--- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp	Mon Jun 02 16:34:30 2025 +0200
+++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp	Mon Jun 02 17:10:37 2025 +0200
@@ -39,6 +39,7 @@
 #include <OrthancServerResources.h>
 
 #include <stdio.h>
+#include <boost/algorithm/string/replace.hpp>
 #include <boost/lexical_cast.hpp>
 
 
@@ -2576,6 +2577,25 @@
   }
 
 
+  static void ExecuteEmbeddedScript(SQLite::Connection& db,
+                                    ServerResources::FileResourceId resourceId)
+  {
+    std::string script;
+    ServerResources::GetFileResource(script, resourceId);
+    db.Execute(script);
+  }
+
+
+  static void InjectEmbeddedScript(std::string& sql,
+                                   const std::string& name,
+                                   ServerResources::FileResourceId resourceId)
+  {
+    std::string script;
+    ServerResources::GetFileResource(script, resourceId);
+    boost::replace_all(sql, name, script);
+  }
+
+
   void SQLiteDatabaseWrapper::Open()
   {
     {
@@ -2614,6 +2634,11 @@
         LOG(INFO) << "Creating the database";
         std::string query;
         ServerResources::GetFileResource(query, ServerResources::PREPARE_DATABASE);
+
+        InjectEmbeddedScript(query, "${INSTALL_TRACK_ATTACHMENTS_SIZE}", ServerResources::INSTALL_TRACK_ATTACHMENTS_SIZE);
+        InjectEmbeddedScript(query, "${INSTALL_LABELS_TABLE}", ServerResources::INSTALL_LABELS_TABLE);
+        InjectEmbeddedScript(query, "${INSTALL_KEY_VALUE_STORES_AND_QUEUES}", ServerResources::INSTALL_KEY_VALUE_STORES_AND_QUEUES);
+
         db_.Execute(query);
       }
 
@@ -2648,18 +2673,14 @@
             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);
+          ExecuteEmbeddedScript(db_, ServerResources::INSTALL_TRACK_ATTACHMENTS_SIZE);
         }
 
         // New in Orthanc 1.12.0
         if (!db_.DoesTableExist("Labels"))
         {
           LOG(INFO) << "Installing the \"Labels\" table";
-          std::string query;
-          ServerResources::GetFileResource(query, ServerResources::INSTALL_LABELS_TABLE);
-          db_.Execute(query);
+          ExecuteEmbeddedScript(db_, ServerResources::INSTALL_LABELS_TABLE);
         }
 
         // New in Orthanc 1.12.8
@@ -2667,18 +2688,14 @@
             || 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);
+          ExecuteEmbeddedScript(db_, ServerResources::INSTALL_REVISION_AND_CUSTOM_DATA);
         }
 
         // New in Orthanc 1.12.8
         if (!db_.DoesTableExist("KeyValueStores"))
         {
           LOG(INFO) << "Installing the \"KeyValueStores\" and \"Queues\" tables";
-          std::string query;
-          ServerResources::GetFileResource(query, ServerResources::INSTALL_KEY_VALUE_STORE_AND_QUEUES);
-          db_.Execute(query);
+          ExecuteEmbeddedScript(db_, ServerResources::INSTALL_KEY_VALUE_STORES_AND_QUEUES);
         }
       }