Mysql procedure to emulate DELETE ON CASCADE-CodePudding

I need to write a mysql procedure to delete s_conf which is parent of other s_conf through s_conf_cpsc and each have child rows on s_conf_at. Each s_conf has an id_a which is a string. The procedure should check if s_conf is only child, then delete, else do nothing to it and proceed with the other. It should delete all related rows on s_conf, s_conf_cpsc and s_conf_at (unless condition previously stated is met).

So far I have this

CREATE PROCEDURE delete_s_conf_recursive(IN ida VARCHAR(255))
BEGIN

    DECLARE _id INT;
    DECLARE _id_a VARCHAR(255);
    DECLARE done INT DEFAULT FALSE;
   
    DECLARE cur CURSOR FOR 
     SELECT ss.id_a, ss.id FROM s_conf AS s 
     LEFT JOIN s_conf_cpsc AS scc ON scc.id_conf = s.id AND s.id_a =  ida 
     LEFT JOIN s_conf AS ss ON scc.id_conf_h = ss.id
     WHERE ss.id_a IS NOT NULL;
     
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO _id_a, _id;

        IF done THEN
            LEAVE read_loop;
        END IF;
        
        CALL delete_s_conf_recursive(_id_a);
        
     END LOOP;
    CLOSE cur;
    
 SET @q = (SELECT COUNT(*) FROM s_conf_cpsc LEFT JOIN s_conf ON s_conf_cpsc.id_conf_h = s_conf.id  AND s_conf.id_a = ida WHERE s_conf.id_a = ida);
    
   IF (@q < 2)  THEN 
     
     DELETE  s_conf_tipo_at FROM s_conf_tipo_at
    LEFT JOIN s_conf ON s_conf_tipo_at.id_conf = s_conf.id  AND s_conf.id_a = ida
    WHERE s_conf.id_a = ida;

    DELETE  s_conf_cpsc FROM s_conf_cpsc  
     LEFT JOIN s_conf ON s_conf_cpsc.id_conf = s_conf.id  AND s_conf.id_a = ida
    WHERE s_conf.id_a = ida;

    DELETE FROM s_conf
    WHERE s_conf.id_a = ida;
    
   END IF;
   
END $$
DELIMITER ;

But it fails to check that the s_conf is only child before atempting to delete. The tables are ON DELETE RESTRICT and should not be changed.

Example: I want to delete s_conf 1 which has childs 2 and 3. But 3 is also child of 4. So the procedure should delete s_conf_cpsc 1-2 and 1-3 but only delete s_conf_at of 2 because 3 is still a child of 4.

Mysql version is 5.6

CodePudding user response：

Could you try the below?

CREATE PROCEDURE delete_s_conf_recursive(IN ida VARCHAR(255))
BEGIN
    DECLARE _id INT;
    DECLARE _id_a VARCHAR(255);
    DECLARE done INT DEFAULT FALSE;

    DECLARE cur CURSOR FOR 
     SELECT ss.id_a, ss.id FROM s_conf AS s 
     LEFT JOIN s_conf_cpsc AS scc ON scc.id_conf = s.id AND s.id_a = ida 
     LEFT JOIN s_conf AS ss ON scc.id_conf_h = ss.id
     WHERE ss.id_a IS NOT NULL;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO _id_a, _id;

        IF done THEN
            LEAVE read_loop;
        END IF;

        CALL delete_s_conf_recursive(_id_a);
    END LOOP;
    CLOSE cur;

    SET @q = (SELECT COUNT(*) FROM s_conf_cpsc WHERE id_conf_h = (SELECT id FROM s_conf WHERE id_a = ida));

    IF (@q = 0) THEN 
        DELETE FROM s_conf_at WHERE id_a = ida;
        DELETE FROM s_conf_cpsc WHERE id_conf = (SELECT id FROM s_conf WHERE id_a = ida);
        DELETE FROM s_conf WHERE id_a = ida;
    END IF;
END $$
DELIMITER ;

The main difference is that it checks the number of child rows in the s_conf_cpsc table directly using a subquery. It does this by counting the number of rows in s_conf_cpsc where the id_conf_h column matches the id of the row in the s_conf table with the input ida. If the count is 0, indicating that the s_conf row has no children, it proceeds to delete the related rows in the s_conf_at, s_conf_cpsc, and s_conf tables.

CodePudding user response：

I found that a simpler approach was to separate the function into two parts, one that will take care recursevily of the children recieving id from parent and child to be deleted in order to check if the id pased as parent is the only one and if not to delete that relation without touching the s_conf_at, and other function that will called the first but will only take care of the first id that is passed.

Here is the result

DROP PROCEDURE IF EXISTS delete_s_conf_recursive_ch;
DELIMITER $$
CREATE PROCEDURE delete_s_conf_recursive(IN ida VARCHAR(255))
BEGIN

    DECLARE _id INT;
    DECLARE _id_a VARCHAR(255);
    DECLARE done INT DEFAULT FALSE;
   
    DECLARE cur CURSOR FOR 
     SELECT ss.id_a, ss.id FROM s_conf AS s 
     LEFT JOIN s_conf_cpsc AS scc ON scc.id_conf = s.id AND s.id_a =  ida 
     LEFT JOIN s_conf AS ss ON scc.id_conf_h = ss.id
     WHERE ss.id_a IS NOT NULL;
     
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO _id_a, _id;

        IF done THEN
            LEAVE read_loop;
        END IF;
       
        CALL delete_s_conf_recursive_ch(_id_a, ida);
        
     END LOOP;
    CLOSE cur;
     
     DELETE  s_conf_at FROM s_conf_at
    LEFT JOIN s_conf ON s_conf_at.id_conf = s_conf.id  AND s_conf.id_a = ida
    WHERE s_conf.id_a = ida;


    DELETE FROM s_conf
    WHERE s_conf.id_a = ida;

   
END $$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE eliminar_s_conf_hijo(IN ida VARCHAR(255), ida_p VARCHAR(255))
BEGIN

    DECLARE _id INT;
    DECLARE _id_a VARCHAR(255);
    DECLARE done INT DEFAULT FALSE;
    DECLARE q INT;
   
    DECLARE cur CURSOR FOR 
     SELECT ss.id_a, ss.id FROM s_conf AS s 
     LEFT JOIN s_conf_cpsc AS scc ON scc.id_conf = s.id AND s.id_a =  ida 
     LEFT JOIN s_conf AS ss ON scc.id_conf_h = ss.id
     WHERE ss.id_a IS NOT NULL;
     
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO _id_a, _id;

        IF done THEN
            LEAVE read_loop;
        END IF;
       
        CALL eliminar_s_conf_hijo(_id_a, ida);
        
     END LOOP;
    CLOSE cur;
    
   SELECT COUNT(*) INTO q FROM s_conf_cpsc WHERE s_conf_cpsc.id_conf_h IN (SELECT id FROM s_conf WHERE s_conf.id_a = ida);

    DELETE FROM s_conf_cpsc
    WHERE s_conf_cpsc.id_conf IN (SELECT id FROM s_conf WHERE s_conf.id_a = ida_p) 
     AND s_conf_cpsc.id_conf_h IN (SELECT id FROM s_conf WHERE s_conf.id_a = ida);
    
   IF q < 2  THEN 
     
     DELETE  s_conf_at FROM s_conf_at
    LEFT JOIN s_conf ON s_conf_at.id_conf = s_conf.id  AND s_conf.id_a = ida
    WHERE s_conf.id_a = ida;


    DELETE FROM s_conf
    WHERE s_conf.id_a = ida;
    
   END IF;
   
END $$
DELIMITER ;