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