procedures.sql

DELIMITER $$

-- ADD TASK
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_task`(
    IN p_title VARCHAR(45),
    IN p_description TEXT,
    IN p_limit_date DATETIME,
    IN p_priority ENUM('low','medium','high'),
    IN p_id_user INT,
    IN p_id_category INT
)
BEGIN
	insert into tasks(title, description, limit_date, priority, id_user, id_category) 
    VALUES (p_title, p_description, p_limit_date, p_priority, p_id_user, p_id_category);
END$$


-- DELETE TASK
CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_task`(
	IN p_id_task INT
)
BEGIN
	DELETE FROM tasks WHERE id_task = p_id_task;
END$$


-- EDIT TASK
CREATE DEFINER=`root`@`localhost` PROCEDURE `edit_task`(
    IN p_id_task INT,
    IN p_new_title VARCHAR(45),
    IN p_new_description TEXT,
    IN p_new_priority ENUM('low','medium','high')
)
BEGIN
	update tasks set title = IFNULL(p_new_title, title), description = IFNULL(p_new_description, description), 
    priority = IFNULL(p_new_priority, priority) where id_task = p_id_task;
END$$


-- UPDATE TASK STATE
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_task_state`(
    IN p_id_task INT,
    IN p_new_state ENUM('pending','complete')
)
BEGIN
	UPDATE tasks SET state = p_new_state WHERE id_task = p_id_task;
END$$

DELIMITER ;