Mercurial > hg > orthanc-databases

changeset 455:d0dd95ff0662 improve-delete

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
move temp table creation out of the procedure
author Alain Mazy <am@osimis.io>
date Mon, 22 Jan 2024 12:27:42 +0100
parents f4244c7e2725
children a164d8aebe0e
files MySQL/Plugins/DeleteResources.sql MySQL/Plugins/MySQLIndex.cpp
diffstat 2 files changed, 15 insertions(+), 25 deletions(-) [+]
line wrap: on
line diff
--- a/MySQL/Plugins/DeleteResources.sql	Mon Jan 22 10:00:28 2024 +0100
+++ b/MySQL/Plugins/DeleteResources.sql	Mon Jan 22 12:27:42 2024 +0100
@@ -10,16 +10,6 @@
 		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
--- a/MySQL/Plugins/MySQLIndex.cpp	Mon Jan 22 10:00:28 2024 +0100
+++ b/MySQL/Plugins/MySQLIndex.cpp	Mon Jan 22 12:27:42 2024 +0100
@@ -473,23 +473,23 @@
       }
     }
 
-    // {
-    //   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);
+    {
+      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);
-    // }
+      Dictionary args;
+      args.SetIntegerValue("id", id);
+      lookupResourcesToDelete.Execute(args);
+    }
 
     // {
     //   DatabaseManager::CachedStatement deleteHierarchy(