Привет Хабр! Хочу рассказать в статье мой опыт реализации бизнес логики (БЛ) в MySQL.
Есть разные мнения насчёт вопроса стоит ли хранить БЛ в базе. Я много лет работаю с Oracle и философия Oracle подразумевает, что БЛ в БД это Best Practices. Приведу пару цитат Тома Кайта:
В то же время в среде web-разработчиков приходится слышать мнения, что БЛ в БД это чуть ли не антипаттерн. Но я не буду останавливаться на вопросе стоит ли реализовывать БЛ в БД. Пусть каждый решает сам. Тем, кто хочет посмотреть, что у меня получилось в свете не столь обширного (по сравнению с Oracle) инструментария MySQL, добро пожаловать под кат.
Реализация предполагает нативный вызов SQL-команд (INSERT/UPDATE/DELETE) на клиенте с описанием логики в триггерах. Всё дальнейшее описание будет справедливо для MySQL 5.1.73. Вот основные моменты, с которыми я столкнулся при разработке:
При обработке SQL-команды требуется прервать её выполнение с ошибкой. Например, если сумма документа превышает лимит, то прервать операцию INSERT/UPDATE и сообщить об ошибке:
Поискав в интернете и слегка подправив решение, появился такой код:
И чтобы в php пользовательские SQL ошибки были с кодом -20000 и человеческим текстом ошибки:
Итоговый код триггера будет выглядеть так:
Или более красивый вариант с использованием функции:
Например, для позиций документа нам необходимо:
Вот как это могло быть написано:
В итоге имеем много кода, код однотипный, дублирующийся и размазанный в 6х местах. Такой код невозможно поддерживать.
Как я решил эту проблему? Я создал триггеры, которые:
Т.к. динамический SQL в триггерах запрещён, то я написал генератор триггеров.
Вот какой код нам выдаст генератор:
И вот удобный запрос, который проверяет, что версия триггера самая актуальная и после добавления столбца мы не забыли перегенерить триггеры, его можно вставить в unit тесты или вообще при сборке приложения автоматом перегенеривать все триггеры
Что в итоге получаем? Единую точку входа для всех изменений, которые делают триггеры — <table_name>_trg_proc
Теперь перепишем наш код под новую систему:
Кода стало меньше, он весь в одном месте и он не дублируется! Такой код поддерживать очень легко.
Хочу пояснить несколько моментов по реализации:
Необходимость изменить таблицу при событии в этой же таблице может возникнуть в многих случаях.
Например, при изменении статуса (атрибута) документа, необходимо создать один или цепочку дочерних документов. При изменении ветки nested sets деревьев, необходимо пересчитать left и right.
Приведу пример. Задача, если есть дочерний документ и у дочернего документа меняется позиция, то необходимо у главного документа уменьшить количество соответствующего материала. Т.е. имеется План производства в котором много товарных позиций, при Списании в производство создаётся документ привязанный к Плану и план уменьшается на соответствующую величину.
В идеале хотелось бы написать такой код:
Но в триггере запрещено менять ту же таблицу. Я решил эту проблему так:
Получившийся инструментарий позволяет описывать БЛ на уровне БД наименьшим количеством кода, с максимальной производительностью и эффективностью.
Есть разные мнения насчёт вопроса стоит ли хранить БЛ в базе. Я много лет работаю с 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 для профессионалов.
Прежде чем начать, хотелось бы объяснить вам мой подход к разработке. Я предпочитаю решать большинство проблем на уровне СУБД. Если что-то можно сделать в СУБД, я так и сделаю. [...] Мой подход состоит в том, чтобы делать в СУБД все, что возможно. [...]
При разработке приложений баз данных я использую очень простую мантру:
- если можно, сделай это с помощью одного оператора SQL;
- если это нельзя сделать с помощью одного оператора SQL, сделай это в PL/SQL;
- если это нельзя сделать в PL/SQL, попытайся использовать хранимую процедуру на языке Java;
- если это нельзя сделать в Java, сделай это в виде внешней процедуры на языке C;
- если это нельзя реализовать в виде внешней процедуры на языке C, надо серьезно подумать, зачем это вообще делать...
В то же время в среде web-разработчиков приходится слышать мнения, что БЛ в БД это чуть ли не антипаттерн. Но я не буду останавливаться на вопросе стоит ли реализовывать БЛ в БД. Пусть каждый решает сам. Тем, кто хочет посмотреть, что у меня получилось в свете не столь обширного (по сравнению с Oracle) инструментария MySQL, добро пожаловать под кат.
Реализация предполагает нативный вызов 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 с предопределённым именем <table_name>_tmp_trg с одноимёнными столбцами и префиксами new_, old_ и полями time и type
- поле time — время выполнения триггера B — BEFORE, A — AFTER
- поле type — DML операция, I — INSERT, U — UPDATE, D — DELETE
- вставляем текущие значения в триггере NEW. и OLD. в соответствующие поля
- вызывается процедура <table_name>_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
Что в итоге получаем? Единую точку входа для всех изменений, которые делают триггеры — <table_name>_trg_proc
Теперь перепишем наш код под новую систему:
-- Триггер для 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$
Итого
Получившийся инструментарий позволяет описывать БЛ на уровне БД наименьшим количеством кода, с максимальной производительностью и эффективностью.
Поделиться с друзьями
Комментарии (44)
AlexAkhremenko
10.10.2016 05:47+1Как обычно вопрос состоит не в том, что лучше, а в том, что целесообразнее.
Всегда найдутся как плюсы, так и минусы. В случае с хранением логики в БЖ мы можем получить, например, большую производительность. Но тогда мы привязываемся к данной конкретной БД.
А за статью спасибо! Именно такого рода статьи должны быть на хабре.
zolt85
10.10.2016 05:54+3Бизнес логика в БД это конечно круто, но как быть с программистами, которые приходят в команду и не то что с MySQL, а просто с SQL на «вы»? Зато джавист он добротный, например. Не брать такого парня?
Плюс, как правило, этой логикой владеет только тот, кто ее реализовал. И, как выше написали, никому не рассказывает что это за логика, и почему именно так написана.
А как быть с тестированием этой логики? Есть какие-то инструменты для unit-тестирования Ваших процедур и функций? А что если придется менять СУБД? В общем, больше вопросов, чем ответов. По сему, я сомневаюсь, что реализация бизнес логики на уровне хранимых процедур в БД — это хорошая идея.xxvy
10.10.2016 07:27Сидим на Oracle. Бизнес логика вся в БД. Вопрос про Unit-testing поддерживаю. Существуют разные решения по Unit-testing в БД. Например utplsql, но хотелось бы отзывов от тех, кто им пользуется (ну или не им)
asmm
10.10.2016 08:22+5По поводу Ваших сомнений очень хорошо сказано опять же у Кайта
По поводу хорошего джависта не знающего SQLНаиболее типичной причиной неудачи является нехватка практических знаний по используемой СУБД — элементарное непонимание основ работы используемого инструментального средства. Подход по принципу «черного ящика» требует осознанного решения: оградить разработчиков от СУБД. Их заставляют не вникать ни в какие особенности ее функционирования. Причины использования этого подхода связаны с опасениями, незнанием и неуверенностью. Разработчики слышали, что СУБД — это «сложно», язык SQL, транзакции и целостность данных — не менее «сложно». Решение: не заставлять никого делать что-либо «сложное». Будем относиться к СУБД, как к черному ящику, и найдем инструментальное средство, которое сгенерирует необходимый код. Изолируем себя несколькими промежуточными уровнями, чтобы не пришлось сталкиваться непосредственно с этой «сложной» СУБД.
[...]
Вот типичный сценарий такого рода разработки.
- Разработчики были полностью обучены графической среде разработки или соответствующему языку программирования (например, Java), использованных для создания клиентской части приложения. Во многих случаях они обучались несколько недель, если не месяцев.
- Команда разработчиков ни одного часа не изучала СУБД Oracle и не имела никакого опыта работы с ней. Многие разработчики вообще впервые сталкивались с СУБД.
- В результате разработчики столкнулись с огромными проблемами, связанными с производительностью, обеспечением целостности данных, зависанием приложений и т.д. (но пользовательский интерфейс выглядел отлично).
[...]
Странная идея о том, что разработчик приложения баз данных должен быть огражден от СУБД, чрезвычайно живуча. Многие почему-то считают, что разработчикам не следует тратить время на изучение СУБД. Неоднократно приходилось слышать: «СУБД Oracle — самая масштабируемая в мире, моим сотрудникам не нужно ее изучать, потому что СУБД со всеми проблемами справится сама». Действительно, СУБД Oracle — самая масштабируемая. Однако написать плохой код, который масштабироваться не будет, в Oracle намного проще, чем написать хороший, масштабируемый код. Можно заменить СУБД Oracle любой другой СУБД — это утверждение останется верным. Это факт: проще писать приложения с низкой производительностью, чем высокопроизводительные приложения. Иногда очень легко создать однопользовательскую систему на базе самой масштабируемой СУБД в мире, если не знать, что делаешь. СУБД — это инструмент, а неправильное применение любого инструмента может привести к катастрофе. Вы будете щипцами колоть орехи так же, как молотком? Можно, конечно, и так, но это неправильное использование инструмента, и результат вас не порадует. Аналогичные результаты будут и при игнорировании особенностей используемой СУБД.
zolt85
10.10.2016 09:46+1Спасибо за ссылки, изучу.
Полностью согласен с утверждениями Тома Кайта о том, что восприятие СУБД как сложного черного ящика есть абсолютное зло. Но реальность заключается в том, что именно с такими разработчиками чаще всего приходится иметь дело. И приходится тратить время на то, чтобы научить разработчика мыслить иначе.
Я ни в коем случае не пытаюсь оградить разработчиков от СУБД, наоборот, я, при любом удобном случае, пытаюсь «окунуть с головой» разработчика в Oracle. И считаю, что хороший разработчик должен быть хорош во всем, и в СУБД, и во фреймворках, используемых на проекте, и в предметной области, в которой работает приложение. По сему, пойду изучать Oracle по приведенным ссылкам, еще раз спасибо.
Alex_x86
10.10.2016 09:50+3Java developer, причем как вы пишете добротный, и не знает SQL? Звучит мягко говоря странно. Кроме того, а разве это проблема — посидеть пару вечеров и вникнуть? Когда-то я сам сталкнулся с этим. И ничего, просто нужно выйти из зоны комфорта и поднять MySQL storage proc. Есть даже замечательная книга «MySQL Stored Procedure Programming». Джавистам будет полезно из нее узнать, к примеру, как в хранимых процедурах организовать оптимистичные блокировки и т.д.
terrier
10.10.2016 10:42+1А что если придется менять СУБД?
Было бы интересно узнать, какая серьезная причина может привести к смене СУБД посреди проекта. С ходу можно придумать только одну: «У нас было множество инсталляций оракла, но человек, получавший откаты с их внедрения был вынужден покинуть нашу компанию, а с новым они не договорились. Так что теперь мы спешно ставим постгрес». А какие еще могут быть сценарии? «Мы писали-писали на .Net-стеке с MS SQL Server, а потом ВНЕЗАПНО осознали, что нам нужен LAMP», так что ли?
Пока что такое впечатление, что смена СУБД в середине проекта — это либо баловство, либо перезапуск проекта.whirl
10.10.2016 11:12Мы писали-писали на .Net-стеке с MS SQL Server, а потом ВНЕЗАПНО осознали, что нам нужен LAMP
Увы, так бывает, к примеру на одной из предыдущих работ внезапно захотели хранить кредитные карты пользователей, что влечет за собой сертификацию по PCI DSS и отдельную изолированную базу данных.
Т.к. все писали на Net-стеке с MS SQL Server то и выбор был соответствующий. Но к моменту релиза внезапно! оказалось, что лицензий нет и необходимо переехать на PostgreSQL. И вот черт его знает баловство это или нет =(
zolt85
10.10.2016 11:31Согласен, вероятность этого не велика. Просто я все чаще слышу от менеджеров вопросы, по типу «а можем наше приложение не на Oracle поставить?». А связан такой вопрос со стоимостью лицензии на СУБД от Oracle. Т.е. приложение, допустим, стоит 500 килорублей в базовой комплектации, но для его работы нужно прикупить лицензию на СУБД еще за 500 килорублей. И на фоне этого привлекательность Вашего приложения по цене уже не такая, как заявлено в рекламе. А проект Ваш с многолетней историей (читай legacy), львиная доля бизнес логики лежит в СУБД. И Вы как разработчик вынуждены сообщить менеджеру, что это не реально сделать даже за год. Соответственно, ни а каком перезапуске проекта речи не идет. Но и Вы, как компания, начинаете терять конкурентные преимущества.
Было бы интересно узнать, какая серьезная причина может привести к смене СУБД посреди проекта.
Посреди проекта сменить СУБД может заставить банально очередной Федеральный Закон, запрещающий использовать зарубежное ПО, если есть отечественный аналог в реестре отечественного ПО. А софт Ваш, например документооборот, и бюджетные или государственные учреждения Ваш целевой клиент. А не будь бизнес логики в БД, глядишь и за пару месяцев можно поддержать работу приложения на другой СУБД, отличной от Oracle.
Из всего этого, у меня нарисовалась другая картинка: Вы разрабатываете приложение, которое может в качестве СУБД использовать Oracle, PostgreSQL и MSSQL Server. Как Jira, например, делает. Тогда Вам уже нужно поддерживать бизнес логику во всех поддерживаемых СУБД? Как с этим быть?terrier
10.10.2016 11:53+1Если мы с самого начала понимаем, что приложение должно поддерживать широкий спектр СУБД, то тут вопросов нет, бизнес-логика в СУБД должна быть минимизирована.
Кейс «спрыгиваем с оракла» действительно распространен, но тут палка о двух концах:
— Писать сразу под N поддерживаемых СУБД банально дороже ( и в деньгах и в инженерных усилиях по разработке и тестированию ). Причем платим мы сразу, когда проект еще только начали и доходов еще нет и нужно ли это, в общем-то неизвестно.
— А вот ситуация, когда «У нас уже есть приложение под оракл, если вы хотите поддержку <другой платформы> заплатите денежку» она и клиенту в общем-то понятна и разработчика устраивает — если труд точно будет оплачен, почему бы не попереписывать логику для другой СУБД.
Итого: банально, но вопрос допустимости бизнес-логики внутри СУБД зависит от бизнес-стратегии проекта. Не забываем, однако, что вот логика данных обязана лежать рядом с данными, будь у вас хоть Oracle, хоть Firebird, хоть собственное хранилище на файлахDolbe
10.10.2016 13:50Если мы с самого начала понимаем...
Вот в такое всегда и упираются все подобные дискуссии. А заказчик/начальство не хочет об этом думать в самом начале, а потом вдруг "а давайте перенесем!". Грустно...
sentyaev
10.10.2016 12:07+1Было бы интересно узнать, какая серьезная причина может привести к смене СУБД посреди проекта.
Сталкивался с ситуацией когда бизнесу продали другую СУБД.
GlukKazan
11.10.2016 09:33+2Исходя из своего небольшого опыта переползания с Oracle на PostgreSQL, могу сказать, что пакеты, процедуры, функции, да пусть даже и триггеры (хотя мы ими почти не пользуемся) — это наименьшая часть проблемы. Больше всего крови портят мелкие гадские различия, вроде того, что в одной СУБД надо писать with recursive, а в другой просто with, ключевое слово table при обращении к табличным функциям, отличия в синтаксисе при работе с последовательностями и прочее, обойтись без которого никак не получится. Нельзя просто поменять JDBC-драйвер и подключится к другой СУБД. В Java-код, в любом случае, придётся вносить изменения. Независимость от СУБД — миф. А если это так, то глупо не пользоваться теми преимуществами, которые даёт конкретная СУБД. Хотя и с таким подходом приходилось сталкиваться (даже в случае Oracle). Что касается кадрового вопроса — программистов просто нужно учить. Если Java-программист узнает SQL чуть лучше — это его не испортит.
oxidmod
10.10.2016 09:29+1ИМХО, все сводится к соотношению между ценой/качеством/скоростью реализации
пилить БЛ в СУБД так, чтобы это не стало адом, может лишь разработчик с очень хорошими знаниями особенностей СУБД. Он будет дорогим. С другой стороны, напилить всен а условной пыхе и сгенерировав схему доктриной может и типичный мид, которых много и коотрые стоят дешевле. Да и не факт что это займет больше времени.
зы. что делать, если БЛ задевает больше одной бд и это не оракл?)
kxl
10.10.2016 11:50+4Томас (Том) Кайт (англ. Thomas Kyte) — американский специалист по информационным технологиям, вице-президент корпорации Oracle (работает в компании с 1993 года).
Понятно, почему им рекомендуется всё реализовывать в БД… Фиг ты потом к конкурентам перейдешь задёшево…asmm
10.10.2016 11:54+1MySQL бесплатная БД, PostgreSQL тоже. Кайт же призывает разбираться в инструменте который используешь.
kxl
10.10.2016 12:02+1Не спорю про инструменты…
Но, попробуй, задёшево перейди с пусть даже и бесплатной PostgresSQL со всеми наворотами, что в ней возможны, на другую БД, да пусть и бесплатную…asmm
10.10.2016 12:11Аналогичный аргумент: попробуй переписать программу с одного языка на другой
kxl
10.10.2016 12:31Учитывая то, что по сути дела БД — это хранилище данных и SQL — это как бы стандарт, то при реализации БЛ в коде приложения (сервиса и т.п.), перенос будет стоить меньше, чем если бы при реализации БЛ фичами конкретной СУБД. Я бы не сказал, что при этом происходит переписывание программы на другой язык. Если только не влезать в дебри хранимок на Java или C (а также python, C# и прочее вендороБДзависимое :)…
как я понимаю, вот похожее мнение
dikkini
10.10.2016 12:18+4Бизнес логика в БД имеет право на жизнь в крайне редких ситуациях и намного проще написать ее плюч, чем минусы.
+ Enterprise система с обработкой миллионов данных и применения на них ACL
+ Единое место хранения/изменения конфигураций, кода, ACL
+ Можно написать статью про то как круто реализовывать бизнес логику в БД
И теперь парочка минусов из сотни возможных:
— Сложность скейлинга. Вот уперлась БД по памяти (а такое не редкость в кровавом энтерпрайзе) и что делать? Правильно, добавлять памяти! Когда можно просто развернуть еще один AS и размазать нагрузку как вертикально так и горизонтально. Вы можете возразить и сказать, а как же кластер? Да вот в энтерпрайзе не любят postgre и mysql, а любят Оракл и DB2 где кластеризация мало того что стоит как самолет, так еще и слабо поддерживаемое, неуправляемое решение, к тому же это требует допиливания для поддержания ACID.
— Неуправляемый код (триггеры, шмиггеры — это вообще «до свидание»)
— Тестирование БД требует написания отдельного приклада (то есть, штат разработчиков БД, штат разработчик тестов для БД — беда с точки зрения финансов и количество ч/с для проекта) — да и зачем это нужно?!
— Непереносимость решения — нельзя поменять СУБД, железо и прочее никогда!
— Режим Создателя для разработчика(ов) БД — тот кто спроектировал БД и заработал ее большую часть — на нем все держится, без него система не получит развития, архитектор БД имеет полную власть над всем. Другой вопрос, что если изначально составить карту, все описать и так далее — жизнь будет проще, но все мы живем в реальном мире, где такое бывает редко и чаще, если даже и описаны ключевые моменты, то все 65535 триггеров и процедур не будут описаны никогда.
Все вышеописанное это 2-х летний опыт архитектора на проекте где бизнес логика была в БД. Не советую никому браться за подобные системы. А другие вице-президенты пусть и дальше пишут рекламные статейки-проспекты про отличную идею реализации бизнес логики в БД продвигая свой продукт.terrier
10.10.2016 14:52+1Вы можете возразить и сказать, а как же кластер?
… требует допиливания для поддержания ACID.
А какая альтернатива? Ок, понятно, что Oracle-кластер это сложно и дорого, а какое решение дешевое и простое «из коробки»? Если нам нужен строго ACID-кластер на «интерпрайз-левел» технологиях, то нам определенно придется платить и мучаться, не с Ораклом, так с чем-то похожим. Вы же не своими руками кластер пишете?dikkini
10.10.2016 14:58-3Я не реализую бизнес логику в БД и избавляю себя от этих мучительных глаз сейлов и мучительных ошибок БД.
vKreker
12.10.2016 01:57Абсолютно с вами согласен. Был в такой же ситуации. В добавок, я попал в новую команду, т.к. старая ушла, оставив систему в плачевном состоянии и без документации. Так что нам скучно не было…
fspare
10.10.2016 12:51+2Моя основная позиция против бизнес логики в базе потому что:
1. Сложнее дебажить
2. Логика размазана
3. Если база начнет захлебываться, то это будет на порядки сложнее скалировать, нежели если бы это было логикой со стороны кода.
Как с точки зрения последующего мейнтенанса, так и с точки зрения скалирования это плохо. Да, возможно вы сможете написать быстрее что-то там, но потом поддерживать вам это будет стоить 10x по сравнению если бы у вас логика была просто на стороне приложения. Я это говорю основываясь на своем опыте работы как над кровавыми оракловскими энтерпрпайзами, где 70% всей логики в базе — кстати очень здорово расхлебывать им теперь перформанс проблемы так как бОльшая часть всей логики на стороне БД ;) Так и имея опыт разработки где база — просто хранилище данных, в котором проблемы как сопровождения так и перформанса решаются на порядки проще и дешевле.
Вопрос — зачем вы все еще пишите логику на стороне базы?S_A
10.10.2016 13:50В целом поддерживаю. Есть некоторые соображение правда. Для пользователей Оракла (именно его) — логика в базе имеет свои преимущества: не завязанность бинарников на ОС/железо (ну это и про другие СУБД верно), работа с большими, очень большими объемами данных все же пишется за меньше строк и работает с данными напрямую без оверхеда (как на получение и обработку, так и на интерпретацию).
Для сайтов на LAMP или вообще приложений, где БД нужна только для хранения человеко-читаемого контента, это всё не нужно. Он (контент) так и так выйдет за пределы базы, «перерабатывать» его внутри базы нет нужды.
Тоже работал с системами, где 70% это PL/SQL ;) В принципе, даже те десять лет назад оракл очень много мог по межпроцессному взаимодействию в *nix, а таскать для разных клиентов бинарники на сумеречные платформы стоило куда дороже в те времена (и Кайт был тогда царь и бох). Сейчас другие архитектурные подходы и инструменты, возможно концепции стоит и переосмыслить, да.
michael_vostrikov
10.10.2016 13:22+1Итоговый код триггера будет выглядеть так:
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('Сумма ... не может превышать лимит ...')); END IF;
В коде будет проще и понятнее:
if ($this->sum > $this->org->max_limit) { throw new MaxLimitException('Сумма ... не может превышать лимит ...'); }
Результат поиска org по id может быть закеширован и использован при обращении из любых других сущностей.
Или более красивый вариант с использованием функции
SET msg := (SELECT raise_error(CONCAT('Сумма ... не может превышать лимит ...')) FROM org o WHERE o.id = NEW.org_id_client AND NEW.sum > o.max_limit );
А что тут красивого? Неочевидный код, напоминает какую-нибудь хитрую ассемблерную конструкцию. Одно сравнение относится к бизнес-логике, второе нужно для связи по ключу, а находятся они вместе в одном выражении WHERE. И «SET msg» в зависимости от них может и не случиться, хотя на первый взляд это просто присваивание.
Я создал триггеры, которые в каждом BEFORE триггере создают MEMORY TEMPORARY TABLE с одноимёнными столбцами
Аналог загрузки данных из БД в переменную. Вы придумали то, что при бизнес-логике в приложении появляется само собой.
Кода стало меньше, он весь в одном месте и он не дублируется! Такой код поддерживать очень легко.
В приложении код еще проще, и поддерживать легче.
Будет что-то типа этого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';
// function DocPos::beforeInsert() $this->price = getPrice($this->material_id, $this->doc->org_id_client);
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';
// function DocPos::beforeInsert() $oldAttributes = $this->oldAttributes; $docId = ($this->doc_id ?: $oldAttributes['doc_id']); $doc = Doc::find($docId); if ($doc->sum === null) { $doc->sum = 0; } if ($oldAttributes['doc_id'] !== null) { $doc->sum -= $oldAttributes['kol'] * $oldAttributes['price']; } if ($this->doc_id !== null) { $doc->sum += $this->kol * $this->price; } $doc->save();
vdasus
10.10.2016 13:27A почему бы, если есть выбор, каждому не заниматься своим делом? БД — работа с данными. Она для этого придумана. Зачем какие-то костыли с debug, с тестами,… если есть инструменты, которые сами под это заточены?
У нас на данный момент большинство логики в БД ибо начальник базист. Вот и все «аргументы» из моего богатого опыта.
Я лично строго за бизнес логику там где ей место. В 99% это точно не ДБ. Натерпелся этой красоты… Простихоссподи. Имхо.
asmm
10.10.2016 14:09+1В коде будет проще и понятнее:
if ($this->sum > $this->org->max_limit) { throw new MaxLimitException('Сумма ... не может превышать лимит ...'); }
Вижу тут как минимум один лишний запрос из php к mysql серверу для извлечения max_limit и client_name.
Во-вторых, а что если есть ещё Python скрипт который инсертит в позиции? На Питоне логику повторять?
Внешний php сервис писать который Питон будет дёргать?
Чувствуйте как мы обрастаем кучей кода.
По поводу почему функцией красивее — «если можно, сделай это с помощью одного оператора SQL»
Не создаются лишние переменные, нет переключения контекста, меньше логических операций, тупо меньше кода, следовательно, будет работать быстрее и поддерживать легче.
По реализации в коде.
1. $oldAttributes = $this->oldAttributes;
Видимо придётся дёргать SELECT'ом — доп. нагрузка
2. У Вас будет генериться много запросов которые гоняются между php <=> mysql. Ещё нагрузка.
3. Как бы Вы реализовали, например, сервис копирования документов за период. С логикой в коде, будет сгенерированно 100500 запросов к БД, которые просто положат сервер. Отсюда похоже и растут ноги байки, что БД с логикой сложнее скалить.
Я же просто написал бы 3-4 SQL команды вида
INSERT INTO docs ... SELECT .. FROM docs d ... WHERE d.date BETWEEN ... INSERT INTO doc_pos ... SELECT .. FROM doc_pos .. docs ... WHERE d.date BETWEEN ...dikkini
10.10.2016 15:03А теперь давайте, применяем паттерн переиспользования кода, пишем CORE процедуру, используем ее в 50 других процедурах, далее, делаем перегруженные процедуры (по 2-3 на эти 50 зависящих от CORE) для тонких клиентов, API, и обратной совместимости всего этого. А теперь… меняем CORE процедуру (например, количество предикатов) и начинаем методично перекомпилировать все ~150 процедур зависящих от нее, соблюдаем порядок компилирования, пишем дополнительный код для совместимости всего прочего, пишем еще 100500 тестов на этот дополнительный код.
michael_vostrikov
10.10.2016 15:18Вижу тут как минимум один лишний запрос из php к mysql серверу
Это будет один запрос на все время работы скрипта, а кроме того, его можно брать из кеша, а не из БД. То есть один запрос к БД на N пользовательских запросов на чтение.
Внешний php сервис писать который Питон будет дёргать?
Да, сервис называется API. Написание API не просто так стало популярным. Никакой кучи кода нет. Либо вы пишете код в БД, либо в PHP. Да, в клиентах API кода будет побольше, чем если бы они коннектились напрямую к базе, зато нет проблем с контролем доступа к таблицам, авторизацией, и прочими инфраструктурными вещами.
Не создаются лишние переменные, нет переключения контекста, меньше логических операций, тупо меньше кода
Я даже количество символов сравнил. В варианте на PHP кода меньше. То, что не создаются переменные с понятными именами, это скорее минус. Не совсем понял, что вы имели в виду насчет контекста, но то, что в рамках одного условия логические операторы служат разным целям, это тоже понятности не добавляет.
1. $oldAttributes = $this->oldAttributes;
Видимо придётся дёргать SELECT'ом — доп. нагрузка
Нет. При загрузке данных сущности из БД устанавливаются свойства в $this и те же самые в $this->oldAttributes. При обработке меняются данные в $this, а если надо, можно прочитать прежние значения из $this->oldAttributes.
У Вас будет генериться много запросов которые гоняются между php <=> mysql. Ещё нагрузка.
Селекты такие же, как и в вашем коде.
Как бы Вы реализовали, например, сервис копирования документов за период.
Не очень понятно, зачем копировать сущности, но это ладно. Скорее всего, я бы дернул их таким же селектом, обработал, и вставил новые данные в один или несколько групповых инсертов. Связанные сущности загружаются через дополнительный запрос с IN. В сложных случаях никто не мешает вызывать из кода запросы с INNER JOIN. То есть, счет запросов к базе идет на единицы или в крайнем случае на десятки, но никак не 100500. И мне кажется, это не такая частая операция, чтобы из-за нее переносить всю логику в базу.
VJean
10.10.2016 15:24Нет. При загрузке данных сущности из БД устанавливаются свойства в $this и те же самые в $this->oldAttributes. При обработке меняются данные в $this, а если надо, можно прочитать прежние значения из $this->oldAttributes.
для PL/SQL и PL/PgSQL давно можно использовать пользовательские переменные.
asmm
10.10.2016 16:30+1зато нет проблем с контролем доступа к таблицам, авторизацией
это как раз в варианте с БЛ на сервере нет этих проблем, т.к. всё имплементировано в триггерах и VIEW, а API-интерфейсом к манипуляции этими данных являются всем знакомые команды INSERT/UPDATE/DELETE
Я даже количество символов сравнил. В варианте на PHP кода меньше. То, что не создаются переменные с понятными именами, это скорее минус.
Это я про оба своих варианта.
Не совсем понял, что вы имели в виду насчет контекста, но то, что в рамках одного условия логические операторы служат разным целям, это тоже понятности не добавляет.
Переключение контекста между SQL и SQL/PSM
При загрузке данных сущности из БД устанавливаются свойства в $this и те же самые в $this->oldAttributes. При обработке меняются данные в $this, а если надо, можно прочитать прежние значения из $this->oldAttributes.
Когда при загрузке? Если в момент загрузки страницы, то так не пойдёт, т.к. пока пользователь на странице oldAttributes могут измениться. Так что придётся считать перед изменением и ещё заблокировать строку пока вы на клиенте вычисляете остальные значения полей, чтобы в этот период никто не изменил строку.
Селекты такие же, как и в вашем коде.
Селекты-то такие же, НО у меня переключение контекста между SQL и SQL/PSM, а в Вашем случае между php и mysql, а это существенная просадка производительности.
Далее, у меня функция get_price серверная, у Вас я так понимаю, php-шная. Предположим get_price в простом варианте рассчитывается из 5 сущностей: прайс, скидка клиента общая, скидка клиента по производителю, скидка клиента по товару, курс валюты.
В итоге на вставку одной позиции документа надо поочерёдно сгенерить 5 запросов и обменяться ими php и mysql'ю.
Я уж боюсь предстваить как бы Вы реализовали selectList для выбора товара с ценой и остатком без использования логики в БД.
В моём случае это был бы примерно такой запрос:
SELECT m.name , get_price(m.id, d.org_id_client, d.date) price , cs.stock FROM materials m LEFT JOIN docs d ON d.id = @doc_id LEFT JOIN cur_stock cs ON cs.warehouse_id = d.warehouse_id AND m.id = cs.material_id WHERE m.name LIKE CONCAT('%', IFNULL(@term, ''), '%') LIMIT 50
Не очень понятно, зачем копировать сущности, но это ладно.
Просто пример массовой обработки данных.
Скорее всего, я бы дернул их таким же селектом, обработал, и вставил новые данные в один или несколько групповых инсертов. Связанные сущности загружаются через дополнительный запрос с IN. В сложных случаях никто не мешает вызывать из кода запросы с INNER JOIN. То есть, счет запросов к базе идет на единицы или в крайнем случае на десятки, но никак не 100500. И мне кажется, это не такая частая операция, чтобы из-за нее переносить всю логику в базу.
Возможно Вы не поняли. Вы мне описали имплементацию обработки одной(!) строки позиции, для массовой обработки Вам придётся либо мириться с провалом производительности, либо допиливать код руками. В моём случае переключением контекста между SQL и SQL/PSM можно пренебречь по сравнению с php и mysql. Поэтому такую систему будет гораздо проще масштабировать.michael_vostrikov
11.10.2016 09:19+1это как раз в варианте с БЛ на сервере нет этих проблем
И поэтому вы написали целую статью, как сделать безопасность на уроне строк?) В приложении в простом случае это делается тривиальноif ($entity->user_id == $currentUser->id), в сложном через RBACif ($user->can('viewEntity', ['entity' => $entity]))
Когда при загрузке? Если в момент загрузки страницы, то так не пойдёт, т.к. пока пользователь на странице oldAttributes могут измениться. Так что придётся считать перед изменением и ещё заблокировать строку пока вы на клиенте вычисляете остальные значения полей, чтобы в этот период никто не изменил строку.
В момент загрузки данных сущности при обработке запроса. Да, это обеспечивается блокировками и транзакциями, они для того и придуманы, это не очень сложно сделать. MySQL делает примерно то же самое, чтобы обеспечить атомарность и последовательность запросов.
а это существенная просадка производительности
А вы проверяли? Давайте проверим. Насколько я понимаю, у вас есть возможность сделать тестовую базу с данными и триггерами. Допустим, вы бы могли выложить такую базу на github, и привести пару примеров, на которых можно проверить производительность. А я бы попробовал написать приложение, которое делает то же самое. Можно будет проверить разницу и решить, стоит ли она того, чтобы переносить бизнес-логику в базу.
прайс, скидка клиента общая, скидка клиента по производителю, скидка клиента по товару, курс валюты. В итоге на вставку одной позиции документа надо поочерёдно сгенерить 5 запросов и обменяться ими php и mysql'ю.
Редко меняющиеся сущности, для них как раз можно использовать кеширование и не дергать базу по пустякам.
Я уж боюсь представить как бы Вы реализовали selectList для выбора товара с ценой и остатком без использования логики в БД.
Из запроса не очень понятно, по каким полям связаны docs и остальные 2 сущности, текущий документ джойнится ко всем записям. Допустим, мы смотрим страницу документа и нам надо вывести выпадающий список материалов с ценой. Я бы сделал примерно так:
$term = 'Material'; $query = Materials::find(); $query->joinWith('curStocks'); $query->where(['warehouse_id' => $document->warehouse_id]); $query->andWhere(['like', 'name', $term]); $query->limit(50); $materials = $query->all(); // foreach по результатам будет в обоих вариантах, независимо от местонахождения бизнес-логики $data = []; foreach ($materials as $material) { $price = getPrice($material, $document); $data[$material->id] = $material->name . ' - ' . $price; } renderSelectList($data);
Вы мне описали имплементацию обработки одной(!) строки позиции
Мне показалось, что слова «в один или несколько групповых инсертов» намекают на множественное число записей) Я привел пример для массовой обработки. Насколько он будет менее производительным, надо проверять на практике. У вас тоже на каждую строку будет дергаться триггер с несколькими селектами. Дело не в переключении контекстов, процессы обычно работают параллельно на разных ядрах, задержки в основном связаны не с процессором, а с вводом-выводом — сеть и диск. Кстати, база будет доступна для чтения/записи в вашем варианте, и можно ли это контролировать?asmm
11.10.2016 22:54Попробовал прописать БЛ в программе для примера с get_price. На довольно простой логике просадка производительности в 2-4 раза. Завтра вечером постараюсь выложить тестовый пример. Надеюсь это Вас убедит. Тем не менее, мне все равно приятно, что Вы искренне пытаетесь разобраться и оппонируйте аргументируя свою позицию.
asmm
17.10.2016 16:00Сори за небольшую задержку. Вот пример, ваял на скорую руку.
1. Настроить config.php
2. Запустить generate.php — создаст и заполнит базу
3. Запустить selectlist.html
config.php<?php class Config { public $db_host = '127.0.0.1'; public $db_username = 'username'; public $db_password = 'password'; public $db_name = 'habr'; }
VJean
10.10.2016 15:17БЛ в БД только на стандартных SQL, предоставляемых БД — сущее зло.
Как насчет плагинов (MySQL/MariaDB) или модулей (Postgres)?
vKreker
Тоже люблю пооптимизировать запросы.
Однако, жутко не люблю логику в БД. Всегда, когда сталкивался с ней, ругался, так как это был недокументированный код, который проводил неявные операции с бд. Приходилось изучать все триггеры, процедуры и функции, документировать и составлять карту, чтобы внести ясность в логику системы.
vKreker
Добавлю, что декомпозиция логики — сама по себе плохая идея.
Когда часть бизнес-правил описана в базе, а другая в приложении, то где-нибудь когда-нибудь обязательно произойдет сбой или несоответствие. И понять полную цепочку правил очень тяжело.
Когда одни данные меняются логикой БД, а другие — логикой приложения, то может появится путаница. Еще хуже, когда одни и те же данные меняются одновременно и логикой БД, и логикой приложения. В такой ситуации на разбор полетов уходит на порядок больше времени.