Реализация бизнес логики в MySQL
Реализация бизнес логики в MySQL
При обработке SQL-команды требуется прервать её выполнение с ошибкой.
Например, если сумма документа превышает лимит, то прервать операцию INSERT/UPDATE и сообщить об ошибке
Поискав в интернете и слегка подправив решение, появился такой код
И чтобы в php пользовательские SQL ошибки были с кодом -20000 и человеческим текстом ошибки:
Итоговый код триггера будет выглядеть так:
Или более красивый вариант с использованием функции
Например, для позиций документа нам необходимо:
Вот как это могло быть написано:
В итоге имеем много кода, код однотипный, дублирующийся и размазанный в 6х местах. Такой код невозможно поддерживать.
Как я решил эту проблему?
Я создал триггеры, которые:
Т.к. динамический SQL в триггерах запрещён, то я написал генератор триггеров.
Вот какой код нам выдаст генератор:
И вот удобный запрос, который проверяет, что версия триггера самая актуальная и после добавления столбца мы не забыли перегенерить триггеры, его можно вставить в unit тесты или вообще при сборке приложения автоматом перегенеривать все триггеры
Что в итоге получаем? Единую точку входа для всех изменений, которые делают триггеры —_trg_proc
Теперь перепишем наш код под новую систему
Кода стало меньше, он весь в одном месте и он не дублируется! Такой код поддерживать очень легко.
Привет Хабр! Хочу рассказать в статье мой опыт реализации бизнес логики (БЛ) в MySQL.
Есть разные мнения насчёт вопроса стоит ли хранить БЛ в базе.
Я много лет работаю с Oracle и философия Oracle подразумевает, что БЛ в БД это Best Practices.
Приведу пару цитат Тома Кайта:
Tom Kyte. Effective Oracle by Design
If the database does something, odds are that it does it better, faster and cheaper, that you could do it yourself
Том Кайт. Oracle для профессионалов.В то же время в среде web-разработчиков приходится слышать мнения, что БЛ в БД это чуть ли не антипаттерн. Но я не буду останавливаться на вопросе стоит ли реализовывать БЛ в БД. Пусть каждый решает сам. Тем, кто хочет посмотреть, что у меня получилось в свете не столь обширного (по сравнению с Oracle) инструментария MySQL, добро пожаловать под кат.
Прежде чем начать, хотелось бы объяснить вам мой подход к разработке. Я предпочитаю решать большинство проблем на уровне СУБД. Если что-то можно сделать в СУБД, я так и сделаю. […]
Мой подход состоит в том, чтобы делать в СУБД все, что возможно. […]
При разработке приложений баз данных я использую очень простую мантру:
- если можно, сделай это с помощью одного оператора SQL;
- если это нельзя сделать с помощью одного оператора SQL, сделай это в PL/SQL;
- если это нельзя сделать в PL/SQL, попытайся использовать хранимую процедуру на языке Java;
- если это нельзя сделать в Java, сделай это в виде внешней процедуры на языке C;
- если это нельзя реализовать в виде внешней процедуры на языке C, надо серьезно подумать, зачем это вообще делать…
Реализация предполагает нативный вызов SQL-команд (INSERT/UPDATE/DELETE) на клиенте с описанием логики в триггерах. Всё дальнейшее описание будет справедливо для MySQL 5.1.73. Вот основные моменты, с которыми я столкнулся при разработке:
- Безопасность на уровне строк (Row Level Security), см. мою предыдущую статью
- Генерация ошибок в триггерах: увы, нативным методом в MySQL 5.1 ошибку не сгенеришь.
- Удобное написание логики в триггерах: В MySQL нельзя создавать 1 триггер на разные SQL-команды, в итоге логика будет размазана по 6 подпрограммам
- Запрет динамического SQL в триггерах
- Отсутствие AFTER STATEMENT TRIGGER: в триггерах уровня строки запрещено менять таблицу в которую вносятся изменения, в Oracle эта проблема решается AFTER триггером уровня выражения
Генерация ошибок в триггерах
При обработке SQL-команды требуется прервать её выполнение с ошибкой.
Например, если сумма документа превышает лимит, то прервать операцию INSERT/UPDATE и сообщить об ошибке
CREATE TRIGGER docs_bef_ins_trg BEFORE INSERT ON docs FOR EACH ROW
BEGIN
DECLARE max_limit decimal(10,2);
SELECT o.max_limit INTO max_limit FROM org o WHERE o.id = NEW.org_id_client;
IF NEW.sum > max_limit THEN
-- ???
-- Тут мы хотим прервать выполнение триггера
-- и выйти с ошибкой, но в MySQL нет нативных
-- способов сделать это
-- ???
END IF;
END
$Поискав в интернете и слегка подправив решение, появился такой код
DELIMITER $
DROP PROCEDURE IF EXISTS raise_error$
CREATE PROCEDURE raise_error(msg TEXT)
BEGIN
SET @raise_error_msg := IFNULL(msg, '');
DROP TEMPORARY TABLE IF EXISTS mysql_error_generator;
CREATE TEMPORARY TABLE mysql_error_generator(raise_error VARCHAR(255) unique) engine=MEMORY;
INSERT INTO mysql_error_generator VALUES (IFNULL(msg, '')), (IFNULL(msg, ''));
END
$
DROP FUNCTION IF EXISTS raise_error$
CREATE FUNCTION raise_error(msg TEXT) RETURNS TEXT
BEGIN
CALL raise_error(msg);
RETURN msg;
END
$И чтобы в php пользовательские SQL ошибки были с кодом -20000 и человеческим текстом ошибки:
class ExPDOException extends PDOException {
public function __construct(PDOException $e, PDO $connection) {
parent::__construct($e->getMessage(), 0, $e->getPrevious());
$this->code = $e->getCode();
$this->errorInfo = $e->errorInfo;
// Пользовательская ошибка
if ($e->getCode() == 23000 && strstr($e->getMessage(), "for key 'raise_error'")) {
$this->code = -20000;
$this->errorInfo[0] = -20000;
$this->errorInfo[1] = -20000;
$sql = 'SELECT @raise_error_msg msg';
$q = $connection->query($sql);
$msg = $q->fetchColumn();
$this->message = $msg;
$this->errorInfo[2] = $msg;
}
}
}Итоговый код триггера будет выглядеть так:
CREATE TRIGGER docs_bef_ins_trg BEFORE INSERT ON docs FOR EACH ROW
BEGIN
DECLARE max_limit decimal(10,2);
DECLARE name VARCHAR(255);
SELECT o.max_limit, o.name INTO max_limit, client_name FROM org o WHERE o.id = NEW.org_id_client;
IF NEW.sum > max_limit THEN
CALL raise_error(CONCAT('Сумма (', NEW.sum
, ') по клиенту ', client_name
, ' не может превышать лимит ', max_limit
, ' в документе с ID = ', NEW.id));
END IF;
ENDИли более красивый вариант с использованием функции
CREATE TRIGGER docs_bef_ins_trg BEFORE INSERT ON docs FOR EACH ROW
BEGIN
DECLARE msg TEXT;
SET msg := (SELECT raise_error(CONCAT('Сумма (', NEW.sum
, ') по клиенту ', o.name
, ' не может превышать лимит ', max_limit
, ' в документе с id = ', NEW.id))
FROM org o
WHERE o.id = NEW.org_id_client
AND NEW.sum > o.max_limit
);
ENDУдобное написание логики и запрет динамического SQL в триггерах
Например, для позиций документа нам необходимо:
- проверять, закрыт ли документ
- при вставке позиции, если цена NULL, то определить цену по клиенту с помощью функции get_price
- денормализовывать сумму документа в мастер таблице
Вот как это могло быть написано:
CREATE TRIGGER doc_pos_bef_ins_trg BEFORE INSERT ON doc_pos FOR EACH ROW
BEGIN
DECLARE msg TEXT;
DECLARE org_id_client INT;
SET msg := (SELECT raise_error(CONCAT('Документ закрыт (id = '
, d.id, '). Изменения запрещены.'))
FROM docs d
WHERE d.id = NEW.doc_id
AND d.closed = 1
);
IF NEW.price IS NULL THEN
SELECT d.org_id_client
INTO org_id_client
FROM docs d
WHERE d.id = NEW.doc_id;
SET NEW.price = get_price(NEW.material_id, org_id_client);
END IF;
END
$
CREATE TRIGGER doc_pos_bef_upd_trg BEFORE UPDATE ON doc_pos FOR EACH ROW
BEGIN
DECLARE msg TEXT;
SET msg := (SELECT raise_error(CONCAT('Документ закрыт (id = '
, d.id, '). Изменения запрещены.'))
FROM docs d
WHERE d.closed = 1 AND d.id IN (OLD.doc_id, NEW.doc_id)
);
END
$
CREATE TRIGGER doc_pos_aft_del_trg BEFORE DELETE ON doc_pos FOR EACH ROW
BEGIN
DECLARE msg TEXT;
SET msg := (SELECT raise_error(CONCAT('Документ закрыт (id = '
, d.id, '). Изменения запрещены.'))
FROM docs d
WHERE d.id = OLD.doc_id
AND d.closed = 1
);
END
$
CREATE TRIGGER doc_pos_aft_ins_trg AFTER INSERT ON doc_pos FOR EACH ROW
BEGIN
UPDATE docs
SET sum = IFNULL(sum, 0) + IFNULL(NEW.kol * NEW.price, 0)
WHERE id = NEW.doc_id;
END
$
CREATE TRIGGER doc_pos_aft_upd_trg AFTER UPDATE ON doc_pos FOR EACH ROW
BEGIN
UPDATE docs
SET sum = IFNULL(sum, 0)
- CASE WHEN OLD.doc_id = id THEN IFNULL(OLD.kol * OLD.price, 0) ELSE 0 END
+ CASE WHEN NEW.doc_id = id THEN IFNULL(NEW.kol * NEW.price, 0) ELSE 0 END
WHERE id IN (OLD.doc_id, NEW.doc_id);
END
$
CREATE TRIGGER doc_pos_aft_del_trg AFTER DELETE ON doc_pos FOR EACH ROW
BEGIN
UPDATE docs
SET sum = IFNULL(sum, 0) + IFNULL(OLD.kol * OLD.price, 0)
WHERE id = OLD.doc_id;
END
$В итоге имеем много кода, код однотипный, дублирующийся и размазанный в 6х местах. Такой код невозможно поддерживать.
Как я решил эту проблему?
Я создал триггеры, которые:
- в каждом BEFORE триггере создают MEMORY TEMPORARY TABLE с предопределённым именем
_tmp_trg с одноимёнными столбцами и префиксами new_, old_ и полями time и type - поле time — время выполнения триггера B — BEFORE, A — AFTER
- поле type — DML операция, I — INSERT, U — UPDATE, D — DELETE
- вставляем текущие значения в триггере NEW. и OLD. в соответствующие поля
- вызывается процедура
_trg_proc - для BEFORE INSERT/UPDATE триггеров считываем обратно в переменные NEW. значения из соответствующих полей
- удаляем данные из временной таблицы, в AFTER триггере DROP TEMPORARY TABLE
Т.к. динамический SQL в триггерах запрещён, то я написал генератор триггеров.
Мой генератор триггеров
DELIMITER $
DROP FUNCTION IF EXISTS generate_trigger$
CREATE FUNCTION generate_trigger(table_name VARCHAR(200), trigger_time VARCHAR(200), trigger_type VARCHAR(200)) RETURNS TEXT BEGIN
DECLARE text TEXT;
DECLARE trigger_time_short VARCHAR(3);
DECLARE trigger_type_short VARCHAR(3);
SET group_concat_max_len = 9000000;
SET trigger_time_short := LOWER(SUBSTR(trigger_time, 1, 3));
SET trigger_type_short := LOWER(SUBSTR(trigger_type, 1, 3));
SET text := '';
SET text := CONCAT(text, 'DROP TRIGGER IF EXISTS ', table_name, '_', trigger_time_short, '_', trigger_type_short, '_trg$\n');
SET text := CONCAT(text, 'CREATE TRIGGER ', table_name, '_', trigger_time_short, '_', trigger_type_short, '_trg ', trigger_time, ' ', trigger_type, ' ON ', table_name,' FOR EACH ROW\n');
SET text := CONCAT(text, 'this_proc:BEGIN\n');
SET text := CONCAT(text, 'IF @disable_', table_name, '_trg = 1 THEN\n');
SET text := CONCAT(text, ' LEAVE this_proc;\n');
SET text := CONCAT(text, 'END IF;\n');
IF trigger_time = 'BEFORE' THEN
-- Создаём временную таблицу
SET text := CONCAT(text, 'CREATE TEMPORARY TABLE ');
-- Временная таблица уже может быть создана конструкцией INSERT INTO ... ON DUPLICATE KEY UPDATE поэтому добавляем IF NOT EXISTS
-- для INSERT IGNORE не сработает AFTER TRIGGER, поэтому тоже добавляем
IF trigger_type IN ('INSERT', 'UPDATE') THEN
SET text := CONCAT(text, 'IF NOT EXISTS ');
END IF;
SET text := CONCAT(text, table_name, '_tmp_trg (\n');
SET text := CONCAT(text, 'time VARCHAR(1)\n');
SET text := CONCAT(text, ', type VARCHAR(1)\n');
SET text := CONCAT(text, ', col_changed VARCHAR(1000)\n, ');
SET text := CONCAT(text, (SELECT GROUP_CONCAT(CONCAT('new_', COLUMN_NAME, ' ', COLUMN_TYPE, '\n, ', 'old_', COLUMN_NAME, ' ', COLUMN_TYPE) SEPARATOR '\n, ') text
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = table_name
AND C.TABLE_SCHEMA = DATABASE()
AND C.COLUMN_TYPE != 'text'
));
SET text := CONCAT(text, ') ENGINE=MEMORY;\n');
-- Создаём переменные
SET text := CONCAT(text, (SELECT GROUP_CONCAT(CONCAT('SET @new_', COLUMN_NAME, ' := ', IF(trigger_type = 'DELETE', 'NULL', CONCAT('NEW.', COLUMN_NAME)), ';\n'
, 'SET @old_', COLUMN_NAME, ' := ', IF(trigger_type = 'INSERT', 'NULL', CONCAT('OLD.', COLUMN_NAME)), ';') SEPARATOR '\n') text
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = table_name
AND C.TABLE_SCHEMA = DATABASE()
AND C.COLUMN_TYPE != 'text'
));
SET text := CONCAT(text, '\n');
END IF;
SET text := CONCAT(text, 'INSERT INTO ', table_name, '_tmp_trg VALUES ("', SUBSTR(trigger_time, 1, 1), '", "', SUBSTR(trigger_type, 1, 1), '", ');
-- заполним col_changed для UPDATE
IF trigger_type = 'UPDATE' THEN
SET text := CONCAT(text, 'CONCAT('
, (SELECT GROUP_CONCAT(CONCAT('IF(IFNULL(NEW.'
, COLUMN_NAME, ', "-ЪъЪ") != IFNULL(OLD.', COLUMN_NAME, ', "-ЪъЪ"), CONCAT("|', COLUMN_NAME, '|"), "")'
) SEPARATOR ', ') text
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = table_name
AND C.TABLE_SCHEMA = DATABASE()
AND C.COLUMN_TYPE != 'text'
), '), ');
ELSE
SET text := CONCAT(text, 'NULL, ');
END IF;
SET text := CONCAT(text, (SELECT GROUP_CONCAT(CONCAT(
CASE
WHEN trigger_time = 'BEFORE' THEN CONCAT('@new_', COLUMN_NAME)
WHEN trigger_type = 'DELETE' THEN 'NULL'
ELSE CONCAT('NEW.', COLUMN_NAME)
END
, ', '
, CASE
WHEN trigger_time = 'BEFORE' THEN CONCAT('@old_', COLUMN_NAME)
WHEN trigger_type = 'INSERT' THEN 'NULL'
ELSE CONCAT('OLD.', COLUMN_NAME)
END
) SEPARATOR ', ') text
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = table_name
AND C.TABLE_SCHEMA = DATABASE()
AND C.COLUMN_TYPE != 'text'
));
SET text := CONCAT(text, ');\n');
SET text := CONCAT(text, 'CALL ', table_name, '_trg_proc;\n');
IF trigger_time = 'BEFORE' THEN
SET text := CONCAT(text, IF(trigger_type = 'DELETE', '', (SELECT CONCAT('SELECT '
, GROUP_CONCAT(CONCAT('new_', COLUMN_NAME) SEPARATOR ', ')
, '\nINTO ', GROUP_CONCAT(CONCAT('@new_', COLUMN_NAME) SEPARATOR ', ')
, '\nFROM ', table_name, '_tmp_trg;\n'
, CONCAT(GROUP_CONCAT(CONCAT('SET NEW.', COLUMN_NAME, ' := @new_', COLUMN_NAME) SEPARATOR ';\n'), ';\n')
) text FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = table_name
AND C.TABLE_SCHEMA = DATABASE()
AND C.COLUMN_TYPE != 'text'
)));
SET text := CONCAT(text, 'DELETE FROM ', table_name, '_tmp_trg;\nEND$\n');
ELSE
SET text := CONCAT(text, 'DROP TEMPORARY TABLE ', table_name, '_tmp_trg;\nEND$\n');
END IF;
RETURN text;
END$
DROP FUNCTION IF EXISTS generate_triggers$
CREATE FUNCTION generate_triggers(p_table_name VARCHAR(200)) RETURNS TEXT BEGIN
DECLARE table_name VARCHAR(200);
DECLARE text TEXT;
SET group_concat_max_len = 9000000;
SET table_name := p_table_name;
SET text := '';
SET text := (SELECT GROUP_CONCAT(generate_trigger(table_name, trigger_time, trigger_type) SEPARATOR '\n')
FROM (SELECT 'BEFORE' trigger_time
UNION ALL SELECT 'AFTER' trigger_time) trigger_time
, (SELECT 'INSERT' trigger_type
UNION ALL SELECT 'UPDATE' trigger_type
UNION ALL SELECT 'DELETE' trigger_type
) trigger_type);
RETURN text;
END$Вот какой код нам выдаст генератор:
SHOW CREATE TABLE doc_pos;
SELECT generate_triggers('doc_pos');
Результат генератора триггеров
CREATE TABLE `doc_pos` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`doc_id` int(11) NOT NULL,
`mat_id` int(11) NOT NULL,
`kol_orig` decimal(10,3) DEFAULT NULL,
`kol` decimal(10,3) DEFAULT NULL,
`price` decimal(17,7) DEFAULT NULL,
`delivery_date` date DEFAULT NULL,
`comment` varchar(255) DEFAULT NULL,
`old_mat_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `doc_id` (`doc_id`,`mat_id`),
KEY `mat_id` (`mat_id`),
CONSTRAINT `doc_pos_ibfk_3` FOREIGN KEY (`mat_id`) REFERENCES `materials` (`id`),
CONSTRAINT `doc_pos_ibfk_1` FOREIGN KEY (`doc_id`) REFERENCES `docs` (`id`),
CONSTRAINT `doc_pos_ibfk_2` FOREIGN KEY (`mat_id`) REFERENCES `materials` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3137919 DEFAULT CHARSET=utf8 COMMENT='Позиции документов'
$
DROP TRIGGER IF EXISTS doc_pos_bef_ins_trg$
CREATE TRIGGER doc_pos_bef_ins_trg BEFORE INSERT ON doc_pos FOR EACH ROW
this_proc:BEGIN
IF @disable_doc_pos_trg = 1 THEN
LEAVE this_proc;
END IF;
CREATE TEMPORARY TABLE IF NOT EXISTS doc_pos_tmp_trg (
time VARCHAR(1)
, type VARCHAR(1)
, col_changed VARCHAR(1000)
, new_id int(11)
, old_id int(11)
, new_doc_id int(11)
, old_doc_id int(11)
, new_mat_id int(11)
, old_mat_id int(11)
, new_kol_orig decimal(10,3)
, old_kol_orig decimal(10,3)
, new_kol decimal(10,3)
, old_kol decimal(10,3)
, new_price decimal(17,7)
, old_price decimal(17,7)
, new_delivery_date date
, old_delivery_date date
, new_comment varchar(255)
, old_comment varchar(255)
, new_old_mat_id int(11)
, old_old_mat_id int(11)) ENGINE=MEMORY;
SET @new_id := NEW.id;
SET @old_id := NULL;
SET @new_doc_id := NEW.doc_id;
SET @old_doc_id := NULL;
SET @new_mat_id := NEW.mat_id;
SET @old_mat_id := NULL;
SET @new_kol_orig := NEW.kol_orig;
SET @old_kol_orig := NULL;
SET @new_kol := NEW.kol;
SET @old_kol := NULL;
SET @new_price := NEW.price;
SET @old_price := NULL;
SET @new_delivery_date := NEW.delivery_date;
SET @old_delivery_date := NULL;
SET @new_comment := NEW.comment;
SET @old_comment := NULL;
SET @new_old_mat_id := NEW.old_mat_id;
SET @old_old_mat_id := NULL;
INSERT INTO doc_pos_tmp_trg VALUES ("B", "I", NULL, @new_id, @old_id, @new_doc_id, @old_doc_id, @new_mat_id, @old_mat_id, @new_kol_orig, @old_kol_orig, @new_kol, @old_kol, @new_price, @old_price, @new_delivery_date, @old_delivery_date, @new_comment, @old_comment, @new_old_mat_id, @old_old_mat_id);
CALL doc_pos_trg_proc;
SELECT new_id, new_doc_id, new_mat_id, new_kol_orig, new_kol, new_price, new_delivery_date, new_comment, new_old_mat_id
INTO @new_id, @new_doc_id, @new_mat_id, @new_kol_orig, @new_kol, @new_price, @new_delivery_date, @new_comment, @new_old_mat_id
FROM doc_pos_tmp_trg;
SET NEW.id := @new_id;
SET NEW.doc_id := @new_doc_id;
SET NEW.mat_id := @new_mat_id;
SET NEW.kol_orig := @new_kol_orig;
SET NEW.kol := @new_kol;
SET NEW.price := @new_price;
SET NEW.delivery_date := @new_delivery_date;
SET NEW.comment := @new_comment;
SET NEW.old_mat_id := @new_old_mat_id;
DELETE FROM doc_pos_tmp_trg;
END$
DROP TRIGGER IF EXISTS doc_pos_aft_ins_trg$
CREATE TRIGGER doc_pos_aft_ins_trg AFTER INSERT ON doc_pos FOR EACH ROW
this_proc:BEGIN
IF @disable_doc_pos_trg = 1 THEN
LEAVE this_proc;
END IF;
INSERT INTO doc_pos_tmp_trg VALUES ("A", "I", NULL, NEW.id, NULL, NEW.doc_id, NULL, NEW.mat_id, NULL, NEW.kol_orig, NULL, NEW.kol, NULL, NEW.price, NULL, NEW.delivery_date, NULL, NEW.comment, NULL, NEW.old_mat_id, NULL);
CALL doc_pos_trg_proc;
DROP TEMPORARY TABLE doc_pos_tmp_trg;
END$
DROP TRIGGER IF EXISTS doc_pos_bef_upd_trg$
CREATE TRIGGER doc_pos_bef_upd_trg BEFORE UPDATE ON doc_pos FOR EACH ROW
this_proc:BEGIN
IF @disable_doc_pos_trg = 1 THEN
LEAVE this_proc;
END IF;
CREATE TEMPORARY TABLE IF NOT EXISTS doc_pos_tmp_trg (
time VARCHAR(1)
, type VARCHAR(1)
, col_changed VARCHAR(1000)
, new_id int(11)
, old_id int(11)
, new_doc_id int(11)
, old_doc_id int(11)
, new_mat_id int(11)
, old_mat_id int(11)
, new_kol_orig decimal(10,3)
, old_kol_orig decimal(10,3)
, new_kol decimal(10,3)
, old_kol decimal(10,3)
, new_price decimal(17,7)
, old_price decimal(17,7)
, new_delivery_date date
, old_delivery_date date
, new_comment varchar(255)
, old_comment varchar(255)
, new_old_mat_id int(11)
, old_old_mat_id int(11)) ENGINE=MEMORY;
SET @new_id := NEW.id;
SET @old_id := OLD.id;
SET @new_doc_id := NEW.doc_id;
SET @old_doc_id := OLD.doc_id;
SET @new_mat_id := NEW.mat_id;
SET @old_mat_id := OLD.mat_id;
SET @new_kol_orig := NEW.kol_orig;
SET @old_kol_orig := OLD.kol_orig;
SET @new_kol := NEW.kol;
SET @old_kol := OLD.kol;
SET @new_price := NEW.price;
SET @old_price := OLD.price;
SET @new_delivery_date := NEW.delivery_date;
SET @old_delivery_date := OLD.delivery_date;
SET @new_comment := NEW.comment;
SET @old_comment := OLD.comment;
SET @new_old_mat_id := NEW.old_mat_id;
SET @old_old_mat_id := OLD.old_mat_id;
INSERT INTO doc_pos_tmp_trg VALUES ("B", "U", CONCAT(IF(IFNULL(NEW.id, "-ЪъЪ") != IFNULL(OLD.id, "-ЪъЪ"), CONCAT("|id|"), ""), IF(IFNULL(NEW.doc_id, "-ЪъЪ") != IFNULL(OLD.doc_id, "-ЪъЪ"), CONCAT("|doc_id|"), ""), IF(IFNULL(NEW.mat_id, "-ЪъЪ") != IFNULL(OLD.mat_id, "-ЪъЪ"), CONCAT("|mat_id|"), ""), IF(IFNULL(NEW.kol_orig, "-ЪъЪ") != IFNULL(OLD.kol_orig, "-ЪъЪ"), CONCAT("|kol_orig|"), ""), IF(IFNULL(NEW.kol, "-ЪъЪ") != IFNULL(OLD.kol, "-ЪъЪ"), CONCAT("|kol|"), ""), IF(IFNULL(NEW.price, "-ЪъЪ") != IFNULL(OLD.price, "-ЪъЪ"), CONCAT("|price|"), ""), IF(IFNULL(NEW.delivery_date, "-ЪъЪ") != IFNULL(OLD.delivery_date, "-ЪъЪ"), CONCAT("|delivery_date|"), ""), IF(IFNULL(NEW.comment, "-ЪъЪ") != IFNULL(OLD.comment, "-ЪъЪ"), CONCAT("|comment|"), ""), IF(IFNULL(NEW.old_mat_id, "-ЪъЪ") != IFNULL(OLD.old_mat_id, "-ЪъЪ"), CONCAT("|old_mat_id|"), "")), @new_id, @old_id, @new_doc_id, @old_doc_id, @new_mat_id, @old_mat_id, @new_kol_orig, @old_kol_orig, @new_kol, @old_kol, @new_price, @old_price, @new_delivery_date, @old_delivery_date, @new_comment, @old_comment, @new_old_mat_id, @old_old_mat_id);
CALL doc_pos_trg_proc;
SELECT new_id, new_doc_id, new_mat_id, new_kol_orig, new_kol, new_price, new_delivery_date, new_comment, new_old_mat_id
INTO @new_id, @new_doc_id, @new_mat_id, @new_kol_orig, @new_kol, @new_price, @new_delivery_date, @new_comment, @new_old_mat_id
FROM doc_pos_tmp_trg;
SET NEW.id := @new_id;
SET NEW.doc_id := @new_doc_id;
SET NEW.mat_id := @new_mat_id;
SET NEW.kol_orig := @new_kol_orig;
SET NEW.kol := @new_kol;
SET NEW.price := @new_price;
SET NEW.delivery_date := @new_delivery_date;
SET NEW.comment := @new_comment;
SET NEW.old_mat_id := @new_old_mat_id;
DELETE FROM doc_pos_tmp_trg;
END$
DROP TRIGGER IF EXISTS doc_pos_aft_upd_trg$
CREATE TRIGGER doc_pos_aft_upd_trg AFTER UPDATE ON doc_pos FOR EACH ROW
this_proc:BEGIN
IF @disable_doc_pos_trg = 1 THEN
LEAVE this_proc;
END IF;
INSERT INTO doc_pos_tmp_trg VALUES ("A", "U", CONCAT(IF(IFNULL(NEW.id, "-ЪъЪ") != IFNULL(OLD.id, "-ЪъЪ"), CONCAT("|id|"), ""), IF(IFNULL(NEW.doc_id, "-ЪъЪ") != IFNULL(OLD.doc_id, "-ЪъЪ"), CONCAT("|doc_id|"), ""), IF(IFNULL(NEW.mat_id, "-ЪъЪ") != IFNULL(OLD.mat_id, "-ЪъЪ"), CONCAT("|mat_id|"), ""), IF(IFNULL(NEW.kol_orig, "-ЪъЪ") != IFNULL(OLD.kol_orig, "-ЪъЪ"), CONCAT("|kol_orig|"), ""), IF(IFNULL(NEW.kol, "-ЪъЪ") != IFNULL(OLD.kol, "-ЪъЪ"), CONCAT("|kol|"), ""), IF(IFNULL(NEW.price, "-ЪъЪ") != IFNULL(OLD.price, "-ЪъЪ"), CONCAT("|price|"), ""), IF(IFNULL(NEW.delivery_date, "-ЪъЪ") != IFNULL(OLD.delivery_date, "-ЪъЪ"), CONCAT("|delivery_date|"), ""), IF(IFNULL(NEW.comment, "-ЪъЪ") != IFNULL(OLD.comment, "-ЪъЪ"), CONCAT("|comment|"), ""), IF(IFNULL(NEW.old_mat_id, "-ЪъЪ") != IFNULL(OLD.old_mat_id, "-ЪъЪ"), CONCAT("|old_mat_id|"), "")), NEW.id, OLD.id, NEW.doc_id, OLD.doc_id, NEW.mat_id, OLD.mat_id, NEW.kol_orig, OLD.kol_orig, NEW.kol, OLD.kol, NEW.price, OLD.price, NEW.delivery_date, OLD.delivery_date, NEW.comment, OLD.comment, NEW.old_mat_id, OLD.old_mat_id);
CALL doc_pos_trg_proc;
DROP TEMPORARY TABLE doc_pos_tmp_trg;
END$
DROP TRIGGER IF EXISTS doc_pos_bef_del_trg$
CREATE TRIGGER doc_pos_bef_del_trg BEFORE DELETE ON doc_pos FOR EACH ROW
this_proc:BEGIN
IF @disable_doc_pos_trg = 1 THEN
LEAVE this_proc;
END IF;
CREATE TEMPORARY TABLE doc_pos_tmp_trg (
time VARCHAR(1)
, type VARCHAR(1)
, col_changed VARCHAR(1000)
, new_id int(11)
, old_id int(11)
, new_doc_id int(11)
, old_doc_id int(11)
, new_mat_id int(11)
, old_mat_id int(11)
, new_kol_orig decimal(10,3)
, old_kol_orig decimal(10,3)
, new_kol decimal(10,3)
, old_kol decimal(10,3)
, new_price decimal(17,7)
, old_price decimal(17,7)
, new_delivery_date date
, old_delivery_date date
, new_comment varchar(255)
, old_comment varchar(255)
, new_old_mat_id int(11)
, old_old_mat_id int(11)) ENGINE=MEMORY;
SET @new_id := NULL;
SET @old_id := OLD.id;
SET @new_doc_id := NULL;
SET @old_doc_id := OLD.doc_id;
SET @new_mat_id := NULL;
SET @old_mat_id := OLD.mat_id;
SET @new_kol_orig := NULL;
SET @old_kol_orig := OLD.kol_orig;
SET @new_kol := NULL;
SET @old_kol := OLD.kol;
SET @new_price := NULL;
SET @old_price := OLD.price;
SET @new_delivery_date := NULL;
SET @old_delivery_date := OLD.delivery_date;
SET @new_comment := NULL;
SET @old_comment := OLD.comment;
SET @new_old_mat_id := NULL;
SET @old_old_mat_id := OLD.old_mat_id;
INSERT INTO doc_pos_tmp_trg VALUES ("B", "D", NULL, @new_id, @old_id, @new_doc_id, @old_doc_id, @new_mat_id, @old_mat_id, @new_kol_orig, @old_kol_orig, @new_kol, @old_kol, @new_price, @old_price, @new_delivery_date, @old_delivery_date, @new_comment, @old_comment, @new_old_mat_id, @old_old_mat_id);
CALL doc_pos_trg_proc;
DELETE FROM doc_pos_tmp_trg;
END$
DROP TRIGGER IF EXISTS doc_pos_aft_del_trg$
CREATE TRIGGER doc_pos_aft_del_trg AFTER DELETE ON doc_pos FOR EACH ROW
this_proc:BEGIN
IF @disable_doc_pos_trg = 1 THEN
LEAVE this_proc;
END IF;
INSERT INTO doc_pos_tmp_trg VALUES ("A", "D", NULL, NULL, OLD.id, NULL, OLD.doc_id, NULL, OLD.mat_id, NULL, OLD.kol_orig, NULL, OLD.kol, NULL, OLD.price, NULL, OLD.delivery_date, NULL, OLD.comment, NULL, OLD.old_mat_id);
CALL doc_pos_trg_proc;
DROP TEMPORARY TABLE doc_pos_tmp_trg;
END$И вот удобный запрос, который проверяет, что версия триггера самая актуальная и после добавления столбца мы не забыли перегенерить триггеры, его можно вставить в unit тесты или вообще при сборке приложения автоматом перегенеривать все триггеры
Запрос на проверку триггеров
SELECT DISTINCT CONCAT(EVENT_OBJECT_TABLE, '') msg
FROM (
SELECT EVENT_OBJECT_TABLE
, CONCAT(SUBSTRING_INDEX(gen_trg, '\n', 2), '\n', T.ACTION_STATEMENT, '$', '\n') ACTION_STATEMENT
, gen_trg gen_trg
FROM (
SELECT T.ACTION_STATEMENT ACTION_STATEMENT
, generate_trigger(T.EVENT_OBJECT_TABLE, T.ACTION_TIMING, T.EVENT_MANIPULATION) gen_trg
, T.EVENT_OBJECT_TABLE
FROM INFORMATION_SCHEMA.TRIGGERS T
WHERE T.TRIGGER_SCHEMA = DATABASE()
) T
) T
WHERE T.ACTION_STATEMENT != T.gen_trg
Что в итоге получаем? Единую точку входа для всех изменений, которые делают триггеры —
Теперь перепишем наш код под новую систему
-- Триггер для doc_pos
DROP PROCEDURE IF EXISTS doc_pos_trg_proc$
CREATE PROCEDURE doc_pos_trg_proc()
BEGIN
DECLARE msg TEXT;
-- Документ закрыт. Изменения запрещены.
SET msg := (SELECT raise_error(CONCAT('Документ закрыт (id = '
, d.id, '). Изменения запрещены.'))
FROM doc_pos_tmp_trg dp
INNER JOIN docs d ON d.id IN (dp.new_doc_id, dp.old_doc_id)
WHERE d.closed = 1 AND dp.time = 'B'
);
-- Подставляем цену
UPDATE doc_pos_tmp_trg
INNER JOIN docs ON doc_pos_tmp_trg.new_doc_id = docs.id
SET dp.new_price = get_price(dp.new_material_id, d.org_id_client)
WHERE dp.time = 'B' AND dp.type = 'I';
-- Денормализация суммы
UPDATE docs
INNER JOIN doc_pos_tmp_trg ON docs.id IN (doc_pos_tmp_trg.new_doc_id, doc_pos_tmp_trg.old_doc_id)
SET sum = IFNULL(docs.sum, 0)
- CASE
WHEN doc_pos_tmp_trg.old_doc_id = id
THEN IFNULL(doc_pos_tmp_trg.old_kol * doc_pos_tmp_trg.old_price, 0)
ELSE 0
END
+ CASE
WHEN doc_pos_tmp_trg.new_doc_id = id
THEN IFNULL(doc_pos_tmp_trg.new_kol * doc_pos_tmp_trg.new_price, 0)
ELSE 0
END
WHERE doc_pos_tmp_trg.time = 'A';
END$Кода стало меньше, он весь в одном месте и он не дублируется! Такой код поддерживать очень легко.
Хочу пояснить несколько моментов по реализации:
- такой подход вместо нативных триггеров, как в первом варианте даёт некоторый оверхед.
На тестовых данных, практически без «полезной» нагрузки 5000 строк вставляется ~1.8с,
в моём случае 5000 строк ~5.9с. Если вынести создание TEMPORARY TABLE и создать
перманетную таблицу и слегка оптимизировать триггер удалось достичь результата 5000 за 3.6c
Но повторюсь, это вхолостую. В реальном коде доля затрат на создание и вставку данных в TEMPORARY TABLE не будет превышать 20%Много тестовых запросовDELIMITER $ DROP TABLE IF EXISTS test_doc_pos$ CREATE TABLE test_doc_pos ( `id` int(11) NOT NULL AUTO_INCREMENT, `doc_id` int(11) NOT NULL, `mat_id` int(11) NOT NULL, `kol_orig` decimal(10,3) DEFAULT NULL, `kol` decimal(10,3) DEFAULT NULL, `price` decimal(17,7) DEFAULT NULL, `delivery_date` date DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `old_mat_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `doc_id` (`doc_id`,`mat_id`), KEY `mat_id` (`mat_id`) ) $ DROP PROCEDURE IF EXISTS speed_test_doc_pos$ CREATE PROCEDURE speed_test_doc_pos(n INT) BEGIN DECLARE i INT DEFAULT 0; WHILE i < n DO INSERT INTO test_doc_pos (doc_id, mat_id, kol, comment) VALUES (i, i, 1, CONCAT('This is comment #', i)); SET i := i + 1; END WHILE; END$ -- Запуск без триггеров 5000 - 0.28c CALL speed_test_doc_pos(5000)$ -- Query OK, 1 row affected (0.28 sec) -- Вариант 1 с нативными триггерами 5000 - 1.8с: DROP TRIGGER IF EXISTS test_doc_pos_bef_ins_trg$ CREATE TRIGGER `test_doc_pos_bef_ins_trg` BEFORE INSERT ON `test_doc_pos` FOR EACH ROW this_proc:BEGIN IF @disable_test_doc_pos_trg = 1 THEN LEAVE this_proc; END IF; SET @db_mode = 'edit'; SET NEW.price := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE()); END $ DROP TRIGGER IF EXISTS test_doc_pos_aft_ins_trg$ CREATE TRIGGER `test_doc_pos_aft_ins_trg` AFTER INSERT ON `test_doc_pos` FOR EACH ROW this_proc:BEGIN IF @disable_test_doc_pos_trg = 1 THEN LEAVE this_proc; END IF; SET @db_mode = 'show'; END $ CALL speed_test_doc_pos(5000)$ -- Query OK, 1 row affected (1.88 sec) -- Вариант 2 - текущая моя версия - 5000 - 5.9с: DROP PROCEDURE IF EXISTS test_doc_pos_trg_proc$ CREATE PROCEDURE test_doc_pos_trg_proc() BEGIN SET @db_mode = (SELECT IF(time = 'B', 'edit', 'show') FROM test_doc_pos_tmp_trg); UPDATE test_doc_pos_tmp_trg SET new_price = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE()); END$ -- SELECT generate_triggers('test_doc_pos')$ DROP TABLE IF EXISTS test_doc_pos_tmp_trg$ DROP TRIGGER IF EXISTS test_doc_pos_bef_ins_trg$ CREATE TRIGGER test_doc_pos_bef_ins_trg BEFORE INSERT ON test_doc_pos FOR EACH ROW this_proc:BEGIN IF @disable_test_doc_pos_trg = 1 THEN LEAVE this_proc; END IF; CREATE TEMPORARY TABLE IF NOT EXISTS test_doc_pos_tmp_trg ( time VARCHAR(1) , type VARCHAR(1) , col_changed VARCHAR(1000) , new_id int(11) , old_id int(11) , new_doc_id int(11) , old_doc_id int(11) , new_mat_id int(11) , old_mat_id int(11) , new_kol_orig decimal(10,3) , old_kol_orig decimal(10,3) , new_kol decimal(10,3) , old_kol decimal(10,3) , new_price decimal(17,7) , old_price decimal(17,7) , new_delivery_date date , old_delivery_date date , new_comment varchar(255) , old_comment varchar(255) , new_old_mat_id int(11) , old_old_mat_id int(11)) ENGINE=MEMORY; SET @new_id := NEW.id; SET @old_id := NULL; SET @new_doc_id := NEW.doc_id; SET @old_doc_id := NULL; SET @new_mat_id := NEW.mat_id; SET @old_mat_id := NULL; SET @new_kol_orig := NEW.kol_orig; SET @old_kol_orig := NULL; SET @new_kol := NEW.kol; SET @old_kol := NULL; SET @new_price := NEW.price; SET @old_price := NULL; SET @new_delivery_date := NEW.delivery_date; SET @old_delivery_date := NULL; SET @new_comment := NEW.comment; SET @old_comment := NULL; SET @new_old_mat_id := NEW.old_mat_id; SET @old_old_mat_id := NULL; INSERT INTO test_doc_pos_tmp_trg VALUES ("B", "I", NULL, @new_id, @old_id, @new_doc_id, @old_doc_id, @new_mat_id, @old_mat_id, @new_kol_orig, @old_kol_orig, @new_kol, @old_kol, @new_price, @old_price, @new_delivery_date, @old_delivery_date, @new_comment, @old_comment, @new_old_mat_id, @old_old_mat_id); CALL test_doc_pos_trg_proc; SELECT new_id, new_doc_id, new_mat_id, new_kol_orig, new_kol, new_price, new_delivery_date, new_comment, new_old_mat_id INTO @new_id, @new_doc_id, @new_mat_id, @new_kol_orig, @new_kol, @new_price, @new_delivery_date, @new_comment, @new_old_mat_id FROM test_doc_pos_tmp_trg; SET NEW.id := @new_id; SET NEW.doc_id := @new_doc_id; SET NEW.mat_id := @new_mat_id; SET NEW.kol_orig := @new_kol_orig; SET NEW.kol := @new_kol; SET NEW.price := @new_price; SET NEW.delivery_date := @new_delivery_date; SET NEW.comment := @new_comment; SET NEW.old_mat_id := @new_old_mat_id; DELETE FROM test_doc_pos_tmp_trg; END$ DROP TRIGGER IF EXISTS test_doc_pos_aft_ins_trg$ CREATE TRIGGER test_doc_pos_aft_ins_trg AFTER INSERT ON test_doc_pos FOR EACH ROW this_proc:BEGIN IF @disable_test_doc_pos_trg = 1 THEN LEAVE this_proc; END IF; INSERT INTO test_doc_pos_tmp_trg VALUES ("A", "I", NULL, NEW.id, NULL, NEW.doc_id, NULL, NEW.mat_id, NULL, NEW.kol_orig, NULL, NEW.kol, NULL, NEW.price, NULL, NEW.delivery_date, NULL, NEW.comment, NULL, NEW.old_mat_id, NULL); CALL test_doc_pos_trg_proc; DROP TEMPORARY TABLE test_doc_pos_tmp_trg; END$ CALL speed_test_doc_pos(5000)$ -- Query OK, 1 row affected (5.91 sec) -- Вариант 3 - оптимизированная - 5000 - 3.6c: DROP PROCEDURE IF EXISTS test_doc_pos_trg_proc$ CREATE PROCEDURE test_doc_pos_trg_proc() BEGIN SET @db_mode = (SELECT IF(time = 'B', 'edit', 'show') FROM test_doc_pos_tmp_trg); UPDATE test_doc_pos_tmp_trg SET new_price = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE()); END$ SELECT generate_triggers('test_doc_pos')$ DROP TABLE IF EXISTS test_doc_pos_tmp_trg$ CREATE TABLE IF NOT EXISTS test_doc_pos_tmp_trg ( time VARCHAR(1) , type VARCHAR(1) , col_changed VARCHAR(1000) , new_id int(11) , old_id int(11) , new_doc_id int(11) , old_doc_id int(11) , new_mat_id int(11) , old_mat_id int(11) , new_kol_orig decimal(10,3) , old_kol_orig decimal(10,3) , new_kol decimal(10,3) , old_kol decimal(10,3) , new_price decimal(17,7) , old_price decimal(17,7) , new_delivery_date date , old_delivery_date date , new_comment varchar(255) , old_comment varchar(255) , new_old_mat_id int(11) , old_old_mat_id int(11)) ENGINE=MEMORY $ DROP TRIGGER IF EXISTS test_doc_pos_bef_ins_trg$ CREATE TRIGGER test_doc_pos_bef_ins_trg BEFORE INSERT ON test_doc_pos FOR EACH ROW this_proc:BEGIN IF @disable_test_doc_pos_trg = 1 THEN LEAVE this_proc; END IF; DELETE FROM test_doc_pos_tmp_trg; INSERT INTO test_doc_pos_tmp_trg VALUES ("B", "I", NULL, @new_id, NULL , NEW.doc_id, NULL , NEW.mat_id, NULL , NEW.kol_orig, NULL , NEW.kol, NULL , NEW.price, NULL , NEW.delivery_date, NULL , NEW.comment, NULL , NEW.old_mat_id, NULL ); CALL test_doc_pos_trg_proc; SELECT new_id, new_doc_id, new_mat_id, new_kol_orig, new_kol, new_price, new_delivery_date, new_comment, new_old_mat_id INTO @new_id, @new_doc_id, @new_mat_id, @new_kol_orig, @new_kol, @new_price, @new_delivery_date, @new_comment, @new_old_mat_id FROM test_doc_pos_tmp_trg; SET NEW.id := @new_id , NEW.doc_id := @new_doc_id , NEW.mat_id := @new_mat_id , NEW.kol_orig := @new_kol_orig , NEW.kol := @new_kol , NEW.price := @new_price , NEW.delivery_date := @new_delivery_date , NEW.comment := @new_comment , NEW.old_mat_id := @new_old_mat_id; DELETE FROM test_doc_pos_tmp_trg; END$ DROP TRIGGER IF EXISTS test_doc_pos_aft_ins_trg$ CREATE TRIGGER test_doc_pos_aft_ins_trg AFTER INSERT ON test_doc_pos FOR EACH ROW this_proc:BEGIN IF @disable_test_doc_pos_trg = 1 THEN LEAVE this_proc; END IF; INSERT INTO test_doc_pos_tmp_trg VALUES ("A", "I", NULL, NEW.id, NULL, NEW.doc_id, NULL, NEW.mat_id, NULL, NEW.kol_orig, NULL, NEW.kol, NULL, NEW.price, NULL, NEW.delivery_date, NULL, NEW.comment, NULL, NEW.old_mat_id, NULL); CALL test_doc_pos_trg_proc; DELETE FROM test_doc_pos_tmp_trg; -- DROP TEMPORARY TABLE test_doc_pos_tmp_trg; END$ CALL speed_test_doc_pos(5000)$ -- Query OK, 1 row affected (3.63 sec) -- Удаляем за собой DROP TABLE IF EXISTS test_doc_pos$ DROP PROCEDURE IF EXISTS speed_test_doc_pos$ - таблица должна быть именно MEMORY, с не MEMORY таблицами потери будут довольно ощутимыми. И т.к. таблица MEMORY, то в ней мы не обрабатываем поля типа TEXT
- если необходимо отключить триггер, например, при импорте данных, то можно поднять флаг @disable_<имя_таблицы>_trg
SET @disable_test_doc_pos_trg = 1;Отсутствие AFTER STATEMENT TRIGGER
Необходимость изменить таблицу при событии в этой же таблице может возникнуть в многих случаях.
Например, при изменении статуса (атрибута) документа, необходимо создать один или цепочку дочерних документов. При изменении ветки nested sets деревьев, необходимо пересчитать left и right.
Приведу пример. Задача, если есть дочерний документ и у дочернего документа меняется позиция, то необходимо у главного документа уменьшить количество соответствующего материала. Т.е. имеется План производства в котором много товарных позиций, при Списании в производство создаётся документ привязанный к Плану и план уменьшается на соответствующую величину.
В идеале хотелось бы написать такой код:CREATE PROCEDURE doc_pos_trg_proc() BEGIN -- ... UPDATE doc_pos_tmp_trg INNER JOIN docs ON docs.id = doc_pos_tmp_trg.doc_id INNER JOIN doc_pos ON doc_pos.doc_id = docs.parent_doc_id AND doc_pos.material_id = doc_pos_tmp_trg.material_id SET doc_pos.kol = doc_pos.kol - IFNULL(doc_pos_tmp_trg.new_kol, 0) + IFNULL(doc_pos_tmp_trg.old_kol, 0) WHERE doc_pos_tmp_trg.time = 'A' ; END$
Но в триггере запрещено менять ту же таблицу. Я решил эту проблему так:- создал таблицу
CREATE TABLE `recursive_sql` ( `id` int(11) NOT NULL AUTO_INCREMENT, `sql_text` text NOT NULL, `pid` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `pid` (`pid`) ) - создал процедуру
DELIMITER $ DROP PROCEDURE IF EXISTS recursive_sql$ CREATE PROCEDURE recursive_sql() BEGIN DECLARE p_sql_text TEXT; DECLARE p_id INT; DECLARE p_cn INT; DECLARE CONTINUE HANDLER FOR NOT FOUND SET @no_data_found = 1; SET @no_data_found = NULL; cursor_loop: LOOP SET @reсursive_sql_sql_text := NULL, p_id := NULL, p_sql_text := NULL; SELECT id, sql_text INTO p_id, p_sql_text FROM recursive_sql LIMIT 1 FOR UPDATE; IF @no_data_found = 1 OR p_id IS NULL THEN LEAVE cursor_loop; END IF; DELETE FROM recursive_sql WHERE id = p_id; SET @reсursive_sql_sql_text := p_sql_text; PREPARE c_sql FROM @reсursive_sql_sql_text; EXECUTE c_sql; DEALLOCATE PREPARE c_sql; END LOOP; -- Проверим ещё раз SELECT COUNT(*) INTO p_cn FROM recursive_sql; IF p_cn > 0 THEN CALL recursive_sql(); END IF; END$ - на уровне PDO после каждого DML запроса вызываю
CALL recursive_sql()
Лишние вызовы не дают практически никакой дополнительной нагрузки.Вот тесты recursive_sqlDELIMITER $ DROP PROCEDURE IF EXISTS recursive_sql_speed_test$ CREATE PROCEDURE recursive_sql_speed_test() BEGIN declare x int unsigned default 0; WHILE x <= 100000 DO CALL recursive_sql(); SET x = x + 1; END WHILE; END$ CALL recursive_sql_speed_test()$ -- Query OK, 0 rows affected (9.24 sec) DROP PROCEDURE IF EXISTS recursive_sql_speed_test$
Каждый вызов ~0.1 мс - в триггере при необходимости изменить текущую таблицу, формирую SQL-команду и вставляю её в таблицу recursive_sql. Т.е наш код будет выглядеть так:
DROP PROCEDURE IF EXISTS doc_pos_trg_proc$ CREATE PROCEDURE doc_pos_trg_proc() BEGIN -- ... INSERT INTO recursive_sql (sql_text) SELECT CONCAT('UPDATE doc_pos SET kol = ' , (doc_pos.kol - IFNULL(doc_pos_tmp_trg.new_kol, 0) + IFNULL(doc_pos_tmp_trg.old_kol, 0)) , ' WHERE id = ', doc_pos.id) sql_text FROM doc_pos_tmp_trg INNER JOIN docs ON docs.id = doc_pos_tmp_trg.doc_id INNER JOIN doc_pos ON doc_pos.doc_id = docs.parent_doc_id AND doc_pos.material_id = doc_pos_tmp_trg.material_id WHERE doc_pos_tmp_trg.time = 'A' ; END$
Итого
Получившийся инструментарий позволяет описывать БЛ на уровне БД наименьшим количеством кода, с максимальной производительностью и эффективностью. - создал таблицу
Комментарии (5)
9 октября 2016 в 23:58 (комментарий был изменён)
+1↑
↓
Тоже люблю пооптимизировать запросы.
Однако, жутко не люблю логику в БД. Всегда, когда сталкивался с ней, ругался, так как это был недокументированный код, который проводил неявные операции с бд. Приходилось изучать все триггеры, процедуры и функции, документировать и составлять карту, чтобы внести ясность в логику системы.10 октября 2016 в 01:33
+1↑
↓
А чем такой вариант прерывания по ошибке не устраивает?10 октября 2016 в 05:48
0↑
↓
Версия mysql 5.1
AlexAkhremenko
10 октября 2016 в 05:47
+1↑
↓
Как обычно вопрос состоит не в том, что лучше, а в том, что целесообразнее.
Всегда найдутся как плюсы, так и минусы. В случае с хранением логики в БЖ мы можем получить, например, большую производительность. Но тогда мы привязываемся к данной конкретной БД.
А за статью спасибо! Именно такого рода статьи должны быть на хабре.10 октября 2016 в 05:54
+1↑
↓
Бизнес логика в БД это конечно круто, но как быть с программистами, которые приходят в команду и не то что с MySQL, а просто с SQL на «вы»? Зато джавист он добротный, например. Не брать такого парня?
Плюс, как правило, этой логикой владеет только тот, кто ее реализовал. И, как выше написали, никому не рассказывает что это за логика, и почему именно так написана.
А как быть с тестированием этой логики? Есть какие-то инструменты для unit-тестирования Ваших процедур и функций? А что если придется менять СУБД? В общем, больше вопросов, чем ответов. По сему, я сомневаюсь, что реализация бизнес логики на уровне хранимых процедур в БД — это хорошая идея.
