# HG changeset patch
# User Alain Mazy <am@orthanc.team>
# Date 1714746216 -7200
# Node ID a164d8aebe0e51d0ee31a32e058d3ea384c3c22c
# Parent  bf4b9c7cf3388997166e9b80cac76dd27aa756c0# Parent  d0dd95ff0662a9431d99d56de9c2eb2d34c923ca
merge improve-mysql-delete -> default

diff -r bf4b9c7cf338 -r a164d8aebe0e MySQL/CMakeLists.txt
--- a/MySQL/CMakeLists.txt	Fri May 03 16:22:02 2024 +0200
+++ b/MySQL/CMakeLists.txt	Fri May 03 16:23:36 2024 +0200
@@ -90,6 +90,7 @@
   MYSQL_PREPARE_INDEX          ${CMAKE_SOURCE_DIR}/Plugins/PrepareIndex.sql
   MYSQL_GET_LAST_CHANGE_INDEX  ${CMAKE_SOURCE_DIR}/Plugins/GetLastChangeIndex.sql
   MYSQL_CREATE_INSTANCE        ${CMAKE_SOURCE_DIR}/Plugins/CreateInstance.sql
+  MYSQL_DELETE_RESOURCES       ${CMAKE_SOURCE_DIR}/Plugins/DeleteResources.sql
   )
 
 if (EXISTS ${ORTHANC_SDK_ROOT}/orthanc/OrthancDatabasePlugin.proto)
diff -r bf4b9c7cf338 -r a164d8aebe0e MySQL/NEWS
--- a/MySQL/NEWS	Fri May 03 16:22:02 2024 +0200
+++ b/MySQL/NEWS	Fri May 03 16:23:36 2024 +0200
@@ -7,7 +7,8 @@
 Optimal Orthanc runtime: 1.12.0+
 
 * Fix check of Orthanc runtime version
-
+* Introduced a procedure to delete resources 
+  (to be tested: https://discourse.orthanc-server.org/t/image-insert-are-too-slow-databse-performance-too-poor-when-using-mysql-mariadb/3820/19)
 
 Release 5.1 (2023-06-27)
 ========================
diff -r bf4b9c7cf338 -r a164d8aebe0e MySQL/Plugins/DeleteResources.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/MySQL/Plugins/DeleteResources.sql	Fri May 03 16:23:36 2024 +0200
@@ -0,0 +1,23 @@
+DROP PROCEDURE IF EXISTS DeleteResources;
+
+CREATE PROCEDURE DeleteResources(
+    IN p_id BIGINT
+)
+BEGIN
+    DECLARE v_internalId BIGINT@
+    DECLARE done INT DEFAULT FALSE@
+	DECLARE cur1 CURSOR FOR
+		SELECT internalId FROM DeletedResources@
+	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE@
+	set done=FALSE@	
+
+	OPEN cur1@
+	REPEAT
+		FETCH cur1 INTO v_internalId@
+		IF NOT done THEN
+		DELETE FROM Resources WHERE internalId=v_internalId@
+		END IF@
+	UNTIL done END REPEAT@
+	CLOSE cur1@
+
+END;
\ No newline at end of file
diff -r bf4b9c7cf338 -r a164d8aebe0e MySQL/Plugins/MySQLIndex.cpp
--- a/MySQL/Plugins/MySQLIndex.cpp	Fri May 03 16:22:02 2024 +0200
+++ b/MySQL/Plugins/MySQLIndex.cpp	Fri May 03 16:23:36 2024 +0200
@@ -317,7 +317,27 @@
         t.Commit();
       }
 
-      if (revision != 7)
+      if (revision == 7)
+      {
+        DatabaseManager::Transaction t(manager, TransactionType_ReadWrite);
+        
+        // Install the "CreateInstance" extension
+        std::string query;
+        
+        Orthanc::EmbeddedResources::GetFileResource
+          (query, Orthanc::EmbeddedResources::MYSQL_DELETE_RESOURCES);
+
+        // Need to escape arobases: Don't use "t.GetDatabaseTransaction().ExecuteMultiLines()" here
+        db.ExecuteMultiLines(query, true);
+        
+        revision = 8;
+        SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision);
+
+        t.Commit();
+      }
+
+
+      if (revision != 8)
       {
         LOG(ERROR) << "MySQL plugin is incompatible with database schema revision: " << revision;
         throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);        
@@ -471,11 +491,25 @@
       lookupResourcesToDelete.Execute(args);
     }
 
+    // {
+    //   DatabaseManager::CachedStatement deleteHierarchy(
+    //     STATEMENT_FROM_HERE, manager,
+    //     "DELETE FROM Resources WHERE internalId IN (SELECT internalId FROM DeletedResources)");
+    //   deleteHierarchy.Execute();
+    // }
+
+
     {
-      DatabaseManager::CachedStatement deleteHierarchy(
+      DatabaseManager::CachedStatement deleteResources(
         STATEMENT_FROM_HERE, manager,
-        "DELETE FROM Resources WHERE internalId IN (SELECT internalId FROM DeletedResources)");
-      deleteHierarchy.Execute();
+        "CALL DeleteResources(${id})");
+
+      deleteResources.SetParameterType("id", ValueType_Integer64);
+
+      Dictionary args;
+      args.SetIntegerValue("id", id);
+    
+      deleteResources.Execute(args);
     }
 
     SignalDeletedResources(output, manager);
diff -r bf4b9c7cf338 -r a164d8aebe0e TODO
--- a/TODO	Fri May 03 16:22:02 2024 +0200
+++ b/TODO	Fri May 03 16:23:36 2024 +0200
@@ -10,6 +10,11 @@
 * Performance of joins in LookupResources: Create cached statement for
   LookupResources, that are grouped to search up to, say, 10 tags,
   instead of recompiling for each request
+* Do not log "DatabaseCannotSerialize" errors in the plugin but only
+  in Orthanc after all retries have been made.
+* Try to avoid the use of temporary tables:
+  https://discourse.orthanc-server.org/t/image-insert-are-too-slow-databse-performance-too-poor/3820
+
 
 * Implement "large queries" for:
   - updating all metadata of a resource at once