Mercurial > hg > orthanc-databases

annotate MySQL/Plugins/DeleteResources.sql @ 424:95c413106a66 improve-delete

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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;