Блог

Перенос логики (хранимая процедура)

23 Июня 2008, 00:00

Решил перенести часть логики (в будущем) блога с PHP на MySQL, переписав функцию добавления комментария с PHP на SQL и сделав хранимую процедуру. И вот что вышло :) Пока проверял только руками.

DELIMITER //
CREATE PROCEDURE blog_comment_add (IN new_p_id MEDIUMINT, IN new_u_id MEDIUMINT, IN new_parent INT, IN new_ip VARCHAR(36), IN new_title VARCHAR(50), IN new_text VARCHAR(4095))
BEGIN
DECLARE new_num TINYINT DEFAULT 0;
DECLARE max_num TINYINT DEFAULT 0;
DECLARE min_num TINYINT DEFAULT 0;
DECLARE new_level TINYINT DEFAULT 0;
DECLARE curr_parent MEDIUMINT;
DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;
IF new_parent > 0 THEN
SELECT `c_num`, `c_level` + 1, `c_parent` INTO new_num, new_level, curr_parent
FROM `blog_comment`
WHERE `c_id` = new_parent;
IF new_num > 1 AND new_num IS NOT NULL THEN
IF curr_parent IS NULL THEN
SELECT MIN(`c_num`) INTO min_num
FROM `blog_comment`
WHERE `c_p_id` = new_p_id AND `c_num` > new_num AND `c_parent` IS NULL;
ELSE
SELECT MIN(`c_num`) INTO min_num
FROM `blog_comment`
WHERE `c_p_id` = new_p_id AND `c_num` > new_num AND `c_parent` = curr_parent;
END IF;
IF min_num > 0 THEN
SET new_num = min_num;
ELSE
SELECT MAX(`c_num`) INTO max_num FROM `blog_comment` WHERE `c_parent` = new_parent;
IF max_num < 1 OR max_num IS NULL THEN
SET new_num = new_num + 1;
ELSE
SET new_num = max_num + 1;
END IF;
END IF;
UPDATE `blog_comment` SET `c_num` = `c_num` + 1
WHERE `c_p_id` = new_p_id AND `c_num` >= new_num;
END IF;
ELSE
SELECT MAX(`c_num`) INTO max_num FROM `blog_comment` WHERE `c_p_id` = new_p_id;
IF max_num < 1 OR max_num IS NULL THEN
SET new_num = 1;
ELSE
SET new_num = max_num + 1;
END IF;
END IF;
IF new_num > 0 THEN
INSERT INTO `blog_comment` (`c_p_id`, `c_u_id`, `c_parent`, `c_num`, `c_level`, `c_time`, `c_ip`, `c_title`, `c_text`)
VALUES (new_p_id, new_u_id, new_parent, new_num, new_level, NOW(), new_ip, new_title, new_text);
END IF;
END;//
DELIMITER ;

Добавить комментарий