# HG changeset patch
# User Alain Mazy <am@osimis.io>
# Date 1694604774 -7200
# Node ID 95c413106a667256cf75f65c2841b0ba98c84e77
# Parent  7d2ba3ece4eeefc4b45307aa36879904adb8eb5c
Introduced a procedure to delete resources (to be tested)

diff -r 7d2ba3ece4ee -r 95c413106a66 MySQL/CMakeLists.txt
--- a/MySQL/CMakeLists.txt	Mon Aug 14 10:16:53 2023 +0200
+++ b/MySQL/CMakeLists.txt	Wed Sep 13 13:32:54 2023 +0200
@@ -79,6 +79,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 7d2ba3ece4ee -r 95c413106a66 MySQL/NEWS
--- a/MySQL/NEWS	Mon Aug 14 10:16:53 2023 +0200
+++ b/MySQL/NEWS	Wed Sep 13 13:32:54 2023 +0200
@@ -1,3 +1,5 @@
+* Introduced a procedure to delete resources (to be tested)
+
 Release 5.1 (2023-06-27)
 ========================
 
diff -r 7d2ba3ece4ee -r 95c413106a66 MySQL/Plugins/DeleteResources.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/MySQL/Plugins/DeleteResources.sql	Wed Sep 13 13:32:54 2023 +0200
@@ -0,0 +1,33 @@
+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@	
+    -- Create a CTE to hold the temporary data ???
+    -- WITH DeletedResources AS (
+    --    SELECT internalId, resourceType, publicId
+    --    FROM Resources
+    -- )
+
+	CREATE TEMPORARY TABLE DeletedResources SELECT * FROM (
+		SELECT internalId, resourceType, publicId FROM Resources WHERE internalId=p_id OR parentId=p_id 
+			OR parentId IN (SELECT internalId FROM Resources WHERE parentId=p_id) 
+			OR parentId IN (SELECT internalId FROM Resources WHERE parentId IN (SELECT internalId FROM Resources WHERE parentId=p_id))) AS t@
+
+	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 7d2ba3ece4ee -r 95c413106a66 MySQL/Plugins/MySQLIndex.cpp
--- a/MySQL/Plugins/MySQLIndex.cpp	Mon Aug 14 10:16:53 2023 +0200
+++ b/MySQL/Plugins/MySQLIndex.cpp	Wed Sep 13 13:32:54 2023 +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);        
@@ -453,29 +473,43 @@
       }
     }
 
-    {
-      DatabaseManager::CachedStatement dropTemporaryTable(
-        STATEMENT_FROM_HERE, manager,
-        "DROP TEMPORARY TABLE IF EXISTS DeletedResources");
-      dropTemporaryTable.Execute();
-    }
+    // {
+    //   DatabaseManager::CachedStatement dropTemporaryTable(
+    //     STATEMENT_FROM_HERE, manager,
+    //     "DROP TEMPORARY TABLE IF EXISTS DeletedResources");
+    //   dropTemporaryTable.Execute();
+    // }
+
+    // {
+    //   DatabaseManager::CachedStatement lookupResourcesToDelete(
+    //     STATEMENT_FROM_HERE, manager,
+    //     "CREATE TEMPORARY TABLE DeletedResources SELECT * FROM (SELECT internalId, resourceType, publicId FROM Resources WHERE internalId=${id} OR parentId=${id} OR parentId IN (SELECT internalId FROM Resources WHERE parentId=${id}) OR parentId IN (SELECT internalId FROM Resources WHERE parentId IN (SELECT internalId FROM Resources WHERE parentId=${id}))) AS t");
+    //   lookupResourcesToDelete.SetParameterType("id", ValueType_Integer64);
+
+    //   Dictionary args;
+    //   args.SetIntegerValue("id", id);
+    //   lookupResourcesToDelete.Execute(args);
+    // }
+
+    // {
+    //   DatabaseManager::CachedStatement deleteHierarchy(
+    //     STATEMENT_FROM_HERE, manager,
+    //     "DELETE FROM Resources WHERE internalId IN (SELECT internalId FROM DeletedResources)");
+    //   deleteHierarchy.Execute();
+    // }
+
 
     {
-      DatabaseManager::CachedStatement lookupResourcesToDelete(
+      DatabaseManager::CachedStatement deleteResources(
         STATEMENT_FROM_HERE, manager,
-        "CREATE TEMPORARY TABLE DeletedResources SELECT * FROM (SELECT internalId, resourceType, publicId FROM Resources WHERE internalId=${id} OR parentId=${id} OR parentId IN (SELECT internalId FROM Resources WHERE parentId=${id}) OR parentId IN (SELECT internalId FROM Resources WHERE parentId IN (SELECT internalId FROM Resources WHERE parentId=${id}))) AS t");
-      lookupResourcesToDelete.SetParameterType("id", ValueType_Integer64);
+        "CALL DeleteResources(${id})");
+
+      deleteResources.SetParameterType("id", ValueType_Integer64);
 
       Dictionary args;
       args.SetIntegerValue("id", id);
-      lookupResourcesToDelete.Execute(args);
-    }
-
-    {
-      DatabaseManager::CachedStatement deleteHierarchy(
-        STATEMENT_FROM_HERE, manager,
-        "DELETE FROM Resources WHERE internalId IN (SELECT internalId FROM DeletedResources)");
-      deleteHierarchy.Execute();
+    
+      deleteResources.Execute(args);
     }
 
     SignalDeletedResources(output, manager);
diff -r 7d2ba3ece4ee -r 95c413106a66 TODO
--- a/TODO	Mon Aug 14 10:16:53 2023 +0200
+++ b/TODO	Wed Sep 13 13:32:54 2023 +0200
@@ -12,6 +12,9 @@
   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
+
 
 ---------------------
 Common - Storage area