Mercurial > hg > orthanc-databases
comparison MySQL/Plugins/DeleteResources.sql @ 424:95c413106a66 improve-delete
Introduced a procedure to delete resources (to be tested)
author | Alain Mazy <am@osimis.io> |
---|---|
date | Wed, 13 Sep 2023 13:32:54 +0200 |
parents | |
children | d0dd95ff0662 |
comparison
equal
deleted
inserted
replaced
423:7d2ba3ece4ee | 424:95c413106a66 |
---|---|
1 DROP PROCEDURE IF EXISTS DeleteResources; | |
2 | |
3 CREATE PROCEDURE DeleteResources( | |
4 IN p_id BIGINT | |
5 ) | |
6 BEGIN | |
7 DECLARE v_internalId BIGINT@ | |
8 DECLARE done INT DEFAULT FALSE@ | |
9 DECLARE cur1 CURSOR FOR | |
10 SELECT internalId FROM DeletedResources@ | |
11 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE@ | |
12 set done=FALSE@ | |
13 -- Create a CTE to hold the temporary data ??? | |
14 -- WITH DeletedResources AS ( | |
15 -- SELECT internalId, resourceType, publicId | |
16 -- FROM Resources | |
17 -- ) | |
18 | |
19 CREATE TEMPORARY TABLE DeletedResources SELECT * FROM ( | |
20 SELECT internalId, resourceType, publicId FROM Resources WHERE internalId=p_id OR parentId=p_id | |
21 OR parentId IN (SELECT internalId FROM Resources WHERE parentId=p_id) | |
22 OR parentId IN (SELECT internalId FROM Resources WHERE parentId IN (SELECT internalId FROM Resources WHERE parentId=p_id))) AS t@ | |
23 | |
24 OPEN cur1@ | |
25 REPEAT | |
26 FETCH cur1 INTO v_internalId@ | |
27 IF NOT done THEN | |
28 DELETE FROM Resources WHERE internalId=v_internalId@ | |
29 END IF@ | |
30 UNTIL done END REPEAT@ | |
31 CLOSE cur1@ | |
32 | |
33 END; |