sp_DeleteGroup

BEGIN

    DECLARE childGIDs TEXT;

    DECLARE parentGIDs TEXT;

    DECLARE selfAndChildGIDs TEXT;

    DECLARE tempGID VARCHAR(1000);

    DECLARE permissionID INT DEFAULT 0;

    DECLARE done INT DEFAULT FALSE;

    DECLARE cur CURSOR FOR SELECT `variable` FROM tmpSplit;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SELECT children FROM masterGroups WHERE groupID=GID INTO childGIDs;

    SELECT CONCAT(childGIDs, GID, ',') INTO selfAndChildGIDs;

    SELECT parents FROM masterGroups WHERE groupID=GID INTO parentGIDs;

​

    DELETE FROM subGroups WHERE FIND_IN_SET(groupID, selfAndChildGIDs);

    DELETE FROM masterGroups WHERE FIND_IN_SET(groupID, selfAndChildGIDs);

    DELETE FROM subGroupsContacts WHERE FIND_IN_SET(groupID, selfAndChildGIDs);

    DELETE FROM subGroupsNetworkDevices WHERE FIND_IN_SET(groupID, selfAndChildGIDs);

    DELETE FROM subGroupWChildren WHERE FIND_IN_SET(groupID, selfAndChildGIDs);

    DELETE FROM subGroupWChildrenContacts WHERE FIND_IN_SET(groupID, selfAndChildGIDs);

    DELETE FROM subGroupWChildrenNetworkDevices WHERE FIND_IN_SET(groupID, selfAndChildGIDs);

    DELETE FROM groupAgents WHERE FIND_IN_SET(groupID, selfAndChildGIDs);

    DELETE FROM groupDAgents WHERE FIND_IN_SET(groupID, selfAndChildGIDs);

    DELETE FROM hotfixgroups WHERE FIND_IN_SET(groupID, selfAndChildGIDs);

    DELETE FROM groupScripts WHERE FIND_IN_SET(groupID, selfAndChildGIDs);

​

    IF LENGTH(parentGIDs) > 1 THEN

        CALL sp_split(TRIM(',' FROM selfAndChildGIDs), 'tmpSplit');

        OPEN cur;

        updChildren: LOOP

            FETCH cur INTO tempGID;

            IF done THEN

                LEAVE updChildren;

            END IF;

            UPDATE mastergroups SET children=REPLACE(children, CONCAT(',', tempGID, ','), ',') WHERE FIND_IN_SET(groupID, TRIM(',' FROM parentGIDs));

        END LOOP;

        CLOSE cur;

        DROP TABLE tmpSplit;

    END IF;

    IF permissionID > 0 THEN

        DELETE FROM permissions WHERE PermID=permissionID;

    END IF;

END