SHARE
TWEET

Untitled

a guest Mar 25th, 2019 56 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DELIMITER //
  2.  
  3. # DROP FUNCTION F_DEADLINE_UPDATE_IS_NEEDED;
  4. # DROP PROCEDURE P_UPDATE_CATEGORY_DEADLINE;
  5. # DROP TRIGGER T_ADD_TASK;
  6. # DROP TRIGGER T_DELETE_CATEGORY;
  7. # DROP TRIGGER T_UPDATE_TASK;
  8. # DROP TRIGGER T_DELETE_TASK;
  9. SET GLOBAL log_bin_trust_function_creators = 1;
  10.  
  11. CREATE PROCEDURE P_UPDATE_CATEGORY_DEADLINE(category_id INT)
  12. BEGIN
  13.   SET @category_has_parent = TRUE;
  14.   SET @update_performed = TRUE;
  15.  
  16.   WHILE @category_has_parent AND @update_performed DO
  17.   SET @category_has_parent = FALSE;
  18.   SET @update_performed = FALSE;
  19.   SET @category_parent_id = (SELECT c.parent_id FROM CORE_TASK_CATEGORY c WHERE  c.id = category_id);
  20.  
  21.   SET @deadline_by_category = (SELECT MAX(c.deadline) FROM CORE_TASK_CATEGORY c WHERE c.parent_id = category_id);
  22.   SET @deadline_by_task = (SELECT MAX(t.deadline) FROM CORE_TASK t WHERE t.category_id = category_id);
  23.   SET @current_deadline = (SELECT c.deadline FROM CORE_TASK_CATEGORY c WHERE c.id = category_id);
  24.   SET @deadline_to_set = @deadline_by_category;
  25.  
  26.   IF (@deadline_to_set IS NULL OR @deadline_to_set < @deadline_by_task) THEN
  27.     SET @deadline_to_set = @deadline_by_task;
  28.   END IF;
  29.  
  30.   IF (@deadline_to_set IS NULL OR @current_deadline IS NULL OR @deadline_to_set <> @current_deadline) THEN
  31.     UPDATE CORE_TASK_CATEGORY c
  32.     SET c.deadline = @deadline_to_set,
  33.         c.version  = c.version + 1
  34.     WHERE c.id = category_id;
  35.  
  36.     SET @update_performed = TRUE;
  37.   END IF;
  38.   IF (@category_parent_id IS NOT NULL) THEN
  39.     SET @category_has_parent = TRUE;
  40.     SET category_id = @category_parent_id;
  41.   END IF;
  42.   END WHILE;
  43.  
  44. END;
  45.  
  46. CREATE FUNCTION F_DEADLINE_UPDATE_IS_NEEDED(category_id INT, proposed_deadline DATETIME)
  47.   RETURNS BOOLEAN
  48. BEGIN
  49.   SET @current_deadline = (SELECT c.deadline FROM CORE_TASK_CATEGORY c WHERE c.id = category_id);
  50.   RETURN @current_deadline IS NULL AND proposed_deadline IS NOT NULL OR
  51.          proposed_deadline IS NULL AND @current_deadline IS NOT NULL OR
  52.          @current_deadline <> proposed_deadline;
  53. END;
  54.  
  55. CREATE TRIGGER T_ADD_TASK
  56.   AFTER
  57.     INSERT
  58.   ON CORE_TASK
  59.   FOR EACH ROW
  60. BEGIN
  61.   IF (F_DEADLINE_UPDATE_IS_NEEDED(NEW.category_id, NEW.deadline)) THEN
  62.     CALL P_UPDATE_CATEGORY_DEADLINE(NEW.category_id);
  63.   END IF;
  64. END;
  65.  
  66. CREATE TRIGGER T_UPDATE_TASK
  67.   AFTER UPDATE
  68.   ON CORE_TASK
  69.   FOR EACH ROW
  70. BEGIN
  71.  
  72.   IF (F_DEADLINE_UPDATE_IS_NEEDED(NEW.category_id, NEW.deadline)) THEN
  73.     CALL P_UPDATE_CATEGORY_DEADLINE(NEW.category_id);
  74.   END IF;
  75.  
  76. END;
  77.  
  78. CREATE TRIGGER T_DELETE_TASK
  79.   AFTER DELETE
  80.   ON CORE_TASK
  81.   FOR EACH ROW
  82. BEGIN
  83.   IF (F_DEADLINE_UPDATE_IS_NEEDED(OLD.category_id, NULL)) THEN
  84.     CALL P_UPDATE_CATEGORY_DEADLINE(OLD.category_id);
  85.   END IF;
  86. END;
  87.  
  88.  
  89. CREATE TRIGGER T_DELETE_CATEGORY
  90.   AFTER DELETE
  91.   ON CORE_TASK_CATEGORY
  92.   FOR EACH ROW
  93. BEGIN
  94.   IF (F_DEADLINE_UPDATE_IS_NEEDED(OLD.parent_id, NULL)) THEN
  95.     CALL P_UPDATE_CATEGORY_DEADLINE(OLD.parent_id);
  96.   END IF;
  97. END;
  98.  
  99. //
  100.  
  101. DELIMITER ;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top