creq_proc.sql

--
-- Dumping routines for database 'creq'
--
/*!50003 DROP PROCEDURE IF EXISTS `creqGroupMemberships_Delete_Procedure` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`creq`@`%` PROCEDURE `creqGroupMemberships_Delete_Procedure`(IN parentId INT(4) UNSIGNED,
                                           IN childId INT(4) UNSIGNED)
BEGIN
DECLARE x INT(4) UNSIGNED;
CREATE TEMPORARY TABLE IF NOT EXISTS 
  suspect(`parentGroup` INT(4) UNSIGNED,
          `childGroup` INT(4) UNSIGNED);
CREATE TEMPORARY TABLE IF NOT EXISTS
  trusty1(`parentGroup` INT(4) UNSIGNED,
          `childGroup` INT(4) UNSIGNED);
INSERT INTO suspect
SELECT * FROM (
    SELECT X.`parentGroup`,
           Y.`childGroup`
    FROM `creqGroupImpliedMemberships` AS X,
         `creqGroupImpliedMemberships` AS Y
    WHERE X.`childGroup` = parentId 
      AND Y.`parentGroup` = childId
  UNION
    SELECT X.`parentGroup`,
           childId AS `childGroup`
    FROM `creqGroupImpliedMemberships` AS X
    WHERE X.`childGroup` = parentId
  UNION
    SELECT parentId AS `parentGroup`,
           X.`childGroup`
    FROM `creqGroupImpliedMemberships` AS X
    WHERE X.`parentGroup` = childId
  UNION
    SELECT parentId AS `parentGroup`, 
           childId as `childGroup`
    FROM `creqGroupImpliedMemberships` AS X
    WHERE X.`parentGroup` = parentId 
      AND X.`childGroup` = childId
) AS TMP3;
INSERT INTO trusty1
SELECT * FROM ( 
    SELECT `parentGroup`, `childGroup`
      FROM `creqGroupImpliedMemberships` AS TC6
    WHERE NOT EXISTS(
      SELECT * FROM suspect
      WHERE suspect.`parentGroup` = TC6.`parentGroup`
        AND suspect.`childGroup` = TC6.`childGroup`
    )
  UNION
    SELECT `parentGroup`, `childGroup`
      FROM `creqGroupMemberships` AS G1
    WHERE G1.`parentGroup` != parentId 
      AND G1.`childGroup` != childId
) AS TMP4;
CREATE TEMPORARY TABLE IF NOT EXISTS trusty2 SELECT * FROM trusty1;
CREATE TEMPORARY TABLE IF NOT EXISTS trusty3 SELECT * FROM trusty1;
CREATE TEMPORARY TABLE IF NOT EXISTS trusty4 SELECT * FROM trusty1;
CREATE TEMPORARY TABLE IF NOT EXISTS trusty5 SELECT * FROM trusty1;
CREATE TEMPORARY TABLE IF NOT EXISTS trusty6 SELECT * FROM trusty1;
DELETE FROM `creqGroupImpliedMemberships`
WHERE NOT EXISTS (
  SELECT * FROM (
    SELECT * FROM(
        SELECT * FROM trusty1
      UNION
        SELECT trusty2.`parentGroup`,
               trusty3.`childGroup`
        FROM trusty2,
             trusty3
        WHERE trusty2.`childGroup` = trusty3.`parentGroup`
      UNION
        SELECT trusty4.`parentGroup`,
               trusty6.`childGroup`
        FROM trusty4,
             trusty5,
             trusty6
        WHERE trusty4.`childGroup` = trusty5.`parentGroup`
          AND trusty5.`childGroup` = trusty6.`parentGroup`
    ) AS TMP5
  ) AS TMP6
  WHERE TMP6.`parentGroup` = `creqGroupImpliedMemberships`.`parentGroup`
    AND TMP6.`childGroup` = `creqGroupImpliedMemberships`.`childGroup`
);
UPDATE `creqGroupImpliedMemberships` 
   SET explicit='no' 
 WHERE `parentGroup` = parentId
   AND `childGroup` = childId;
DELETE FROM suspect;
DELETE FROM trusty1;
DELETE FROM trusty2;
DELETE FROM trusty3;
DELETE FROM trusty4;
DELETE FROM trusty5;
DELETE FROM trusty6;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `creqGroupMemberships_Insert_Procedure` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`creq`@`%` PROCEDURE `creqGroupMemberships_Insert_Procedure`(IN parentId INT(4) UNSIGNED,
                                           IN childId INT(4) UNSIGNED,
                                           IN typeId INT(4) UNSIGNED)
BEGIN
DECLARE x INT(4) UNSIGNED;
CREATE TEMPORARY TABLE IF NOT EXISTS 
  tcNew(`parentGroup` INT(4) UNSIGNED,
         `childGroup` INT(4) UNSIGNED);
INSERT INTO tcNew
SELECT * FROM (
	SELECT * FROM (
		SELECT TC1.`parentGroup`,
		       childId AS `childGroup` 
		FROM `creqGroupImpliedMemberships` AS TC1
		WHERE TC1.`childGroup` = parentId
	  UNION
		SELECT parentId AS `parentGroup`,
		       TC2.`childGroup`
		FROM `creqGroupImpliedMemberships` AS TC2
		WHERE TC2.`parentGroup` = childId
	  UNION
		SELECT TC3.`parentGroup`,
		       TC4.`childGroup`
		FROM `creqGroupImpliedMemberships` AS TC3,
		     `creqGroupImpliedMemberships` AS TC4
		WHERE TC3.`childGroup` = parentId 
		  AND TC4.`parentGroup` = childId
	  UNION
	    SELECT parentId,
	           childId
	) AS TMP1
) AS TMP2 
WHERE NOT EXISTS(
  SELECT * FROM 
    `creqGroupImpliedMemberships` AS TC5
  WHERE TC5.`parentGroup`=TMP2.`parentGroup` 
    AND TC5.`childGroup`=TMP2.`childGroup`
);
IF(
  (
    SELECT COUNT(*) AS dupes 
      FROM tcNew 
      WHERE `parentGroup`=`childGroup`
  ) != 0
) THEN
  DELETE FROM tcNew;
  SELECT raiseError('Adding this edge would create a cycle') INTO x;
END IF;
INSERT INTO `creqGroupImpliedMemberships`(`parentGroup`, `childGroup`, `type`)
  SELECT *, typeId FROM tcNew;
UPDATE `creqGroupImpliedMemberships` 
   SET explicit='yes' 
 WHERE `parentGroup` = parentId
   AND `childGroup` = childId;
DELETE FROM tcNew;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;