Реализация бизнес-логики в MySQL

    Привет Хабр! Хочу рассказать в статье мой опыт реализации бизнес логики (БЛ) в 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 для профессионалов.
    Прежде чем начать, хотелось бы объяснить вам мой подход к разработке. Я предпочитаю решать большинство проблем на уровне СУБД. Если что-то можно сделать в СУБД, я так и сделаю. [...] Мой подход состоит в том, чтобы делать в СУБД все, что возможно. [...]
    При разработке приложений баз данных я использую очень простую мантру:

    • если можно, сделай это с помощью одного оператора 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_sql
      DELIMITER $
      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$

    Итого


    Получившийся инструментарий позволяет описывать БЛ на уровне БД наименьшим количеством кода, с максимальной производительностью и эффективностью.
    Поделиться публикацией

    Комментарии 46

      +3
      Тоже люблю пооптимизировать запросы.
      Однако, жутко не люблю логику в БД. Всегда, когда сталкивался с ней, ругался, так как это был недокументированный код, который проводил неявные операции с бд. Приходилось изучать все триггеры, процедуры и функции, документировать и составлять карту, чтобы внести ясность в логику системы.
        –1
        Добавлю, что декомпозиция логики — сама по себе плохая идея.
        Когда часть бизнес-правил описана в базе, а другая в приложении, то где-нибудь когда-нибудь обязательно произойдет сбой или несоответствие. И понять полную цепочку правил очень тяжело.

        Когда одни данные меняются логикой БД, а другие — логикой приложения, то может появится путаница. Еще хуже, когда одни и те же данные меняются одновременно и логикой БД, и логикой приложения. В такой ситуации на разбор полетов уходит на порядок больше времени.
        +1
        А чем такой вариант прерывания по ошибке не устраивает?
          0
          Версия mysql 5.1
          +1
          Как обычно вопрос состоит не в том, что лучше, а в том, что целесообразнее.
          Всегда найдутся как плюсы, так и минусы. В случае с хранением логики в БЖ мы можем получить, например, большую производительность. Но тогда мы привязываемся к данной конкретной БД.
          А за статью спасибо! Именно такого рода статьи должны быть на хабре.
            +3
            Бизнес логика в БД это конечно круто, но как быть с программистами, которые приходят в команду и не то что с MySQL, а просто с SQL на «вы»? Зато джавист он добротный, например. Не брать такого парня?
            Плюс, как правило, этой логикой владеет только тот, кто ее реализовал. И, как выше написали, никому не рассказывает что это за логика, и почему именно так написана.
            А как быть с тестированием этой логики? Есть какие-то инструменты для unit-тестирования Ваших процедур и функций? А что если придется менять СУБД? В общем, больше вопросов, чем ответов. По сему, я сомневаюсь, что реализация бизнес логики на уровне хранимых процедур в БД — это хорошая идея.
            • НЛО прилетело и опубликовало эту надпись здесь
                +5
                По поводу Ваших сомнений очень хорошо сказано опять же у Кайта
                По поводу хорошего джависта не знающего SQL
                Наиболее типичной причиной неудачи является нехватка практических знаний по используемой СУБД — элементарное непонимание основ работы используемого инструментального средства. Подход по принципу «черного ящика» требует осознанного решения: оградить разработчиков от СУБД. Их заставляют не вникать ни в какие особенности ее функционирования. Причины использования этого подхода связаны с опасениями, незнанием и неуверенностью. Разработчики слышали, что СУБД — это «сложно», язык SQL, транзакции и целостность данных — не менее «сложно». Решение: не заставлять никого делать что-либо «сложное». Будем относиться к СУБД, как к черному ящику, и найдем инструментальное средство, которое сгенерирует необходимый код. Изолируем себя несколькими промежуточными уровнями, чтобы не пришлось сталкиваться непосредственно с этой «сложной» СУБД.
                [...]
                Вот типичный сценарий такого рода разработки.
                • Разработчики были полностью обучены графической среде разработки или соответствующему языку программирования (например, Java), использованных для создания клиентской части приложения. Во многих случаях они обучались несколько недель, если не месяцев.
                • Команда разработчиков ни одного часа не изучала СУБД Oracle и не имела никакого опыта работы с ней. Многие разработчики вообще впервые сталкивались с СУБД.
                • В результате разработчики столкнулись с огромными проблемами, связанными с производительностью, обеспечением целостности данных, зависанием приложений и т.д. (но пользовательский интерфейс выглядел отлично).

                [...]
                Странная идея о том, что разработчик приложения баз данных должен быть огражден от СУБД, чрезвычайно живуча. Многие почему-то считают, что разработчикам не следует тратить время на изучение СУБД. Неоднократно приходилось слышать: «СУБД Oracle — самая масштабируемая в мире, моим сотрудникам не нужно ее изучать, потому что СУБД со всеми проблемами справится сама». Действительно, СУБД Oracle — самая масштабируемая. Однако написать плохой код, который масштабироваться не будет, в Oracle намного проще, чем написать хороший, масштабируемый код. Можно заменить СУБД Oracle любой другой СУБД — это утверждение останется верным. Это факт: проще писать приложения с низкой производительностью, чем высокопроизводительные приложения. Иногда очень легко создать однопользовательскую систему на базе самой масштабируемой СУБД в мире, если не знать, что делаешь. СУБД — это инструмент, а неправильное применение любого инструмента может привести к катастрофе. Вы будете щипцами колоть орехи так же, как молотком? Можно, конечно, и так, но это неправильное использование инструмента, и результат вас не порадует. Аналогичные результаты будут и при игнорировании особенностей используемой СУБД.

                Независимость от СУБД
                Вы, наверное, уже поняли направление моей мысли. Я ссылался на другие СУБД и описывал различия реализации одних и тех же возможностей в каждой из них. Я убежден: за исключением некоторых приложений, исключительно читающих из базы данных, создать полностью независимое от СУБД и при этом масштабируемое приложение крайне сложно и даже практически невозможно, не зная особенностей работы всех СУБД.

                Вообще очень рекомендую прочитать хотя бы первую главу книги, которую я цитирую.
                Oracle для профессионалов. Глава 1
                Уверен что после прочтения у Вас развеются сомнения и предрассудки по поводу реализации БЛ в БД.

                Плюс, как правило, этой логикой владеет только тот, кто ее реализовал. И, как выше написали, никому не рассказывает что это за логика, и почему именно так написана.
                Очень странное утверждение. И почему это справедливо только для БЛ в БД?

                По поводу тестирования нет никаких проблем. В Oracle использую utPLSQL:
                • всё можно хранить в базе и, следовательно, запускать все тесты скриптом из SQLPlus
                • много разных assert'ов: запросы, коллекции, таблицы, курсоры
                • возможность тестить не публичные функции/процедуры пакетов

                В MySQL тесты на php. Тестируется практически идентично, если бы логика была не в БД. Для тестирования моего примера из статьи:
                • Подготовка: INSERT тестового документа
                • INSERT позиции с NULL ценой
                • SELECT цены и assert с ожидаемым результатом
                • SELECT суммы документа и assert с ожидаемым результатом
                • UPDATE кол-ва / цены, DELETE позиции
                • SELECT суммы документа и assert с ожидаемым результатом
                • Закрываем документ
                • INSERT/UPDATE/DELETE позиции
                • assert на SQL ошибку
                • Удаляем за собой тестовый документ или делаем ROLLBACK

                Но можно и вообще сваять аналог utPLSQL и тестировать всё в БД

                2 xxvy если Вы пропустили статью «TDD для хранимых процедур Oracle», то вот Вам ссылка
                  +1
                  Спасибо за ссылки, изучу.

                  Полностью согласен с утверждениями Тома Кайта о том, что восприятие СУБД как сложного черного ящика есть абсолютное зло. Но реальность заключается в том, что именно с такими разработчиками чаще всего приходится иметь дело. И приходится тратить время на то, чтобы научить разработчика мыслить иначе.

                  Я ни в коем случае не пытаюсь оградить разработчиков от СУБД, наоборот, я, при любом удобном случае, пытаюсь «окунуть с головой» разработчика в Oracle. И считаю, что хороший разработчик должен быть хорош во всем, и в СУБД, и во фреймворках, используемых на проекте, и в предметной области, в которой работает приложение. По сему, пойду изучать Oracle по приведенным ссылкам, еще раз спасибо.
                  +3
                  Java developer, причем как вы пишете добротный, и не знает SQL? Звучит мягко говоря странно. Кроме того, а разве это проблема — посидеть пару вечеров и вникнуть? Когда-то я сам сталкнулся с этим. И ничего, просто нужно выйти из зоны комфорта и поднять MySQL storage proc. Есть даже замечательная книга «MySQL Stored Procedure Programming». Джавистам будет полезно из нее узнать, к примеру, как в хранимых процедурах организовать оптимистичные блокировки и т.д.
                    +1
                    А что если придется менять СУБД?


                    Было бы интересно узнать, какая серьезная причина может привести к смене СУБД посреди проекта. С ходу можно придумать только одну: «У нас было множество инсталляций оракла, но человек, получавший откаты с их внедрения был вынужден покинуть нашу компанию, а с новым они не договорились. Так что теперь мы спешно ставим постгрес». А какие еще могут быть сценарии? «Мы писали-писали на .Net-стеке с MS SQL Server, а потом ВНЕЗАПНО осознали, что нам нужен LAMP», так что ли?
                    Пока что такое впечатление, что смена СУБД в середине проекта — это либо баловство, либо перезапуск проекта.
                      0
                      Мы писали-писали на .Net-стеке с MS SQL Server, а потом ВНЕЗАПНО осознали, что нам нужен LAMP

                      Увы, так бывает, к примеру на одной из предыдущих работ внезапно захотели хранить кредитные карты пользователей, что влечет за собой сертификацию по PCI DSS и отдельную изолированную базу данных.
                      Т.к. все писали на Net-стеке с MS SQL Server то и выбор был соответствующий. Но к моменту релиза внезапно! оказалось, что лицензий нет и необходимо переехать на PostgreSQL. И вот черт его знает баловство это или нет =(
                        0
                        Согласен, вероятность этого не велика. Просто я все чаще слышу от менеджеров вопросы, по типу «а можем наше приложение не на Oracle поставить?». А связан такой вопрос со стоимостью лицензии на СУБД от Oracle. Т.е. приложение, допустим, стоит 500 килорублей в базовой комплектации, но для его работы нужно прикупить лицензию на СУБД еще за 500 килорублей. И на фоне этого привлекательность Вашего приложения по цене уже не такая, как заявлено в рекламе. А проект Ваш с многолетней историей (читай legacy), львиная доля бизнес логики лежит в СУБД. И Вы как разработчик вынуждены сообщить менеджеру, что это не реально сделать даже за год. Соответственно, ни а каком перезапуске проекта речи не идет. Но и Вы, как компания, начинаете терять конкурентные преимущества.

                        Было бы интересно узнать, какая серьезная причина может привести к смене СУБД посреди проекта.


                        Посреди проекта сменить СУБД может заставить банально очередной Федеральный Закон, запрещающий использовать зарубежное ПО, если есть отечественный аналог в реестре отечественного ПО. А софт Ваш, например документооборот, и бюджетные или государственные учреждения Ваш целевой клиент. А не будь бизнес логики в БД, глядишь и за пару месяцев можно поддержать работу приложения на другой СУБД, отличной от Oracle.

                        Из всего этого, у меня нарисовалась другая картинка: Вы разрабатываете приложение, которое может в качестве СУБД использовать Oracle, PostgreSQL и MSSQL Server. Как Jira, например, делает. Тогда Вам уже нужно поддерживать бизнес логику во всех поддерживаемых СУБД? Как с этим быть?
                          +1
                          Если мы с самого начала понимаем, что приложение должно поддерживать широкий спектр СУБД, то тут вопросов нет, бизнес-логика в СУБД должна быть минимизирована.

                          Кейс «спрыгиваем с оракла» действительно распространен, но тут палка о двух концах:
                          — Писать сразу под N поддерживаемых СУБД банально дороже ( и в деньгах и в инженерных усилиях по разработке и тестированию ). Причем платим мы сразу, когда проект еще только начали и доходов еще нет и нужно ли это, в общем-то неизвестно.
                          — А вот ситуация, когда «У нас уже есть приложение под оракл, если вы хотите поддержку <другой платформы> заплатите денежку» она и клиенту в общем-то понятна и разработчика устраивает — если труд точно будет оплачен, почему бы не попереписывать логику для другой СУБД.

                          Итого: банально, но вопрос допустимости бизнес-логики внутри СУБД зависит от бизнес-стратегии проекта. Не забываем, однако, что вот логика данных обязана лежать рядом с данными, будь у вас хоть Oracle, хоть Firebird, хоть собственное хранилище на файлах
                            0
                            Если мы с самого начала понимаем...

                            Вот в такое всегда и упираются все подобные дискуссии. А заказчик/начальство не хочет об этом думать в самом начале, а потом вдруг "а давайте перенесем!". Грустно...

                          +1
                          Было бы интересно узнать, какая серьезная причина может привести к смене СУБД посреди проекта.

                          Сталкивался с ситуацией когда бизнесу продали другую СУБД.
                          +2
                          Исходя из своего небольшого опыта переползания с Oracle на PostgreSQL, могу сказать, что пакеты, процедуры, функции, да пусть даже и триггеры (хотя мы ими почти не пользуемся) — это наименьшая часть проблемы. Больше всего крови портят мелкие гадские различия, вроде того, что в одной СУБД надо писать with recursive, а в другой просто with, ключевое слово table при обращении к табличным функциям, отличия в синтаксисе при работе с последовательностями и прочее, обойтись без которого никак не получится. Нельзя просто поменять JDBC-драйвер и подключится к другой СУБД. В Java-код, в любом случае, придётся вносить изменения. Независимость от СУБД — миф. А если это так, то глупо не пользоваться теми преимуществами, которые даёт конкретная СУБД. Хотя и с таким подходом приходилось сталкиваться (даже в случае Oracle). Что касается кадрового вопроса — программистов просто нужно учить. Если Java-программист узнает SQL чуть лучше — это его не испортит.
                          +1
                          ИМХО, все сводится к соотношению между ценой/качеством/скоростью реализации
                          пилить БЛ в СУБД так, чтобы это не стало адом, может лишь разработчик с очень хорошими знаниями особенностей СУБД. Он будет дорогим. С другой стороны, напилить всен а условной пыхе и сгенерировав схему доктриной может и типичный мид, которых много и коотрые стоят дешевле. Да и не факт что это займет больше времени.

                          зы. что делать, если БЛ задевает больше одной бд и это не оракл?)
                            –3
                            хоть пишите за что минусуете ОО
                            +4
                            Томас (Том) Кайт (англ. Thomas Kyte) — американский специалист по информационным технологиям, вице-президент корпорации Oracle (работает в компании с 1993 года).
                            Понятно, почему им рекомендуется всё реализовывать в БД… Фиг ты потом к конкурентам перейдешь задёшево…
                              +1
                              MySQL бесплатная БД, PostgreSQL тоже. Кайт же призывает разбираться в инструменте который используешь.
                                +1
                                Не спорю про инструменты…
                                Но, попробуй, задёшево перейди с пусть даже и бесплатной PostgresSQL со всеми наворотами, что в ней возможны, на другую БД, да пусть и бесплатную…
                                  0
                                  Аналогичный аргумент: попробуй переписать программу с одного языка на другой
                                    0
                                    Учитывая то, что по сути дела БД — это хранилище данных и SQL — это как бы стандарт, то при реализации БЛ в коде приложения (сервиса и т.п.), перенос будет стоить меньше, чем если бы при реализации БЛ фичами конкретной СУБД. Я бы не сказал, что при этом происходит переписывание программы на другой язык. Если только не влезать в дебри хранимок на Java или C (а также python, C# и прочее вендороБДзависимое :)…

                                    как я понимаю, вот похожее мнение
                              +4
                              Бизнес логика в БД имеет право на жизнь в крайне редких ситуациях и намного проще написать ее плюч, чем минусы.

                              + Enterprise система с обработкой миллионов данных и применения на них ACL
                              + Единое место хранения/изменения конфигураций, кода, ACL
                              + Можно написать статью про то как круто реализовывать бизнес логику в БД

                              И теперь парочка минусов из сотни возможных:

                              — Сложность скейлинга. Вот уперлась БД по памяти (а такое не редкость в кровавом энтерпрайзе) и что делать? Правильно, добавлять памяти! Когда можно просто развернуть еще один AS и размазать нагрузку как вертикально так и горизонтально. Вы можете возразить и сказать, а как же кластер? Да вот в энтерпрайзе не любят postgre и mysql, а любят Оракл и DB2 где кластеризация мало того что стоит как самолет, так еще и слабо поддерживаемое, неуправляемое решение, к тому же это требует допиливания для поддержания ACID.
                              — Неуправляемый код (триггеры, шмиггеры — это вообще «до свидание»)
                              — Тестирование БД требует написания отдельного приклада (то есть, штат разработчиков БД, штат разработчик тестов для БД — беда с точки зрения финансов и количество ч/с для проекта) — да и зачем это нужно?!
                              — Непереносимость решения — нельзя поменять СУБД, железо и прочее никогда!
                              — Режим Создателя для разработчика(ов) БД — тот кто спроектировал БД и заработал ее большую часть — на нем все держится, без него система не получит развития, архитектор БД имеет полную власть над всем. Другой вопрос, что если изначально составить карту, все описать и так далее — жизнь будет проще, но все мы живем в реальном мире, где такое бывает редко и чаще, если даже и описаны ключевые моменты, то все 65535 триггеров и процедур не будут описаны никогда.

                              Все вышеописанное это 2-х летний опыт архитектора на проекте где бизнес логика была в БД. Не советую никому браться за подобные системы. А другие вице-президенты пусть и дальше пишут рекламные статейки-проспекты про отличную идею реализации бизнес логики в БД продвигая свой продукт.
                                +1
                                Вы можете возразить и сказать, а как же кластер?
                                … требует допиливания для поддержания ACID.


                                А какая альтернатива? Ок, понятно, что Oracle-кластер это сложно и дорого, а какое решение дешевое и простое «из коробки»? Если нам нужен строго ACID-кластер на «интерпрайз-левел» технологиях, то нам определенно придется платить и мучаться, не с Ораклом, так с чем-то похожим. Вы же не своими руками кластер пишете?
                                  –3
                                  Я не реализую бизнес логику в БД и избавляю себя от этих мучительных глаз сейлов и мучительных ошибок БД.
                                  0
                                  Абсолютно с вами согласен. Был в такой же ситуации. В добавок, я попал в новую команду, т.к. старая ушла, оставив систему в плачевном состоянии и без документации. Так что нам скучно не было…
                                  0
                                  Как насчет отладки?
                                    0
                                    http://mydebugger.com/
                                    Ещё вот
                                    http://dev.mysql.com/doc/visual-studio/en/visual-studio-debugger.html
                                      0
                                      О да. Good luck & Have fun!
                                    +2
                                    Моя основная позиция против бизнес логики в базе потому что:
                                    1. Сложнее дебажить
                                    2. Логика размазана
                                    3. Если база начнет захлебываться, то это будет на порядки сложнее скалировать, нежели если бы это было логикой со стороны кода.

                                    Как с точки зрения последующего мейнтенанса, так и с точки зрения скалирования это плохо. Да, возможно вы сможете написать быстрее что-то там, но потом поддерживать вам это будет стоить 10x по сравнению если бы у вас логика была просто на стороне приложения. Я это говорю основываясь на своем опыте работы как над кровавыми оракловскими энтерпрпайзами, где 70% всей логики в базе — кстати очень здорово расхлебывать им теперь перформанс проблемы так как бОльшая часть всей логики на стороне БД ;) Так и имея опыт разработки где база — просто хранилище данных, в котором проблемы как сопровождения так и перформанса решаются на порядки проще и дешевле.

                                    Вопрос — зачем вы все еще пишите логику на стороне базы?
                                      0
                                      В целом поддерживаю. Есть некоторые соображение правда. Для пользователей Оракла (именно его) — логика в базе имеет свои преимущества: не завязанность бинарников на ОС/железо (ну это и про другие СУБД верно), работа с большими, очень большими объемами данных все же пишется за меньше строк и работает с данными напрямую без оверхеда (как на получение и обработку, так и на интерпретацию).

                                      Для сайтов на LAMP или вообще приложений, где БД нужна только для хранения человеко-читаемого контента, это всё не нужно. Он (контент) так и так выйдет за пределы базы, «перерабатывать» его внутри базы нет нужды.

                                      Тоже работал с системами, где 70% это PL/SQL ;) В принципе, даже те десять лет назад оракл очень много мог по межпроцессному взаимодействию в *nix, а таскать для разных клиентов бинарники на сумеречные платформы стоило куда дороже в те времена (и Кайт был тогда царь и бох). Сейчас другие архитектурные подходы и инструменты, возможно концепции стоит и переосмыслить, да.
                                      +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();
                                      

                                        0
                                        A почему бы, если есть выбор, каждому не заниматься своим делом? БД — работа с данными. Она для этого придумана. Зачем какие-то костыли с debug, с тестами,… если есть инструменты, которые сами под это заточены?

                                        У нас на данный момент большинство логики в БД ибо начальник базист. Вот и все «аргументы» из моего богатого опыта.

                                        Я лично строго за бизнес логику там где ей место. В 99% это точно не ДБ. Натерпелся этой красоты… Простихоссподи. Имхо.
                                          +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 ...
                                          

                                            0
                                            А теперь давайте, применяем паттерн переиспользования кода, пишем CORE процедуру, используем ее в 50 других процедурах, далее, делаем перегруженные процедуры (по 2-3 на эти 50 зависящих от CORE) для тонких клиентов, API, и обратной совместимости всего этого. А теперь… меняем CORE процедуру (например, количество предикатов) и начинаем методично перекомпилировать все ~150 процедур зависящих от нее, соблюдаем порядок компилирования, пишем дополнительный код для совместимости всего прочего, пишем еще 100500 тестов на этот дополнительный код.
                                              0
                                              Вижу тут как минимум один лишний запрос из 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. И мне кажется, это не такая частая операция, чтобы из-за нее переносить всю логику в базу.

                                                0
                                                Нет. При загрузке данных сущности из БД устанавливаются свойства в $this и те же самые в $this->oldAttributes. При обработке меняются данные в $this, а если надо, можно прочитать прежние значения из $this->oldAttributes.
                                                для PL/SQL и PL/PgSQL давно можно использовать пользовательские переменные.
                                                  +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. Поэтому такую систему будет гораздо проще масштабировать.
                                                    +1
                                                    это как раз в варианте с БЛ на сервере нет этих проблем

                                                    И поэтому вы написали целую статью, как сделать безопасность на уроне строк?) В приложении в простом случае это делается тривиально if ($entity->user_id == $currentUser->id), в сложном через RBAC if ($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);
                                                    


                                                    Вы мне описали имплементацию обработки одной(!) строки позиции

                                                    Мне показалось, что слова «в один или несколько групповых инсертов» намекают на множественное число записей) Я привел пример для массовой обработки. Насколько он будет менее производительным, надо проверять на практике. У вас тоже на каждую строку будет дергаться триггер с несколькими селектами. Дело не в переключении контекстов, процессы обычно работают параллельно на разных ядрах, задержки в основном связаны не с процессором, а с вводом-выводом — сеть и диск. Кстати, база будет доступна для чтения/записи в вашем варианте, и можно ли это контролировать?
                                                      0
                                                      Попробовал прописать БЛ в программе для примера с get_price. На довольно простой логике просадка производительности в 2-4 раза. Завтра вечером постараюсь выложить тестовый пример. Надеюсь это Вас убедит. Тем не менее, мне все равно приятно, что Вы искренне пытаетесь разобраться и оппонируйте аргументируя свою позицию.
                                                        0
                                                        Сори за небольшую задержку. Вот пример, ваял на скорую руку.
                                                        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';
                                                        }
                                                        

                                                        generate.php
                                                        <?php
                                                        require_once 'config.php';
                                                        
                                                        $config = new Config();
                                                        $db = new PDO("mysql:host=" . $config->db_host.";dbname=".$config->db_name, $config->db_username, $config->db_password);
                                                        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                                                        $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
                                                        $db->exec('SET names utf8');
                                                        $db->exec('SET storage_engine=innoDB');
                                                        $db->exec("SET sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY'");
                                                        
                                                        //$cn = isset($_REQUEST['cn'])?$_REQUEST['cn'] * 1:10000;
                                                        $material_cn = 10000;
                                                        $client_cn = 1000;
                                                        
                                                        $db->exec("SET FOREIGN_KEY_CHECKS=0");
                                                        // Документы
                                                        $db->exec("DROP TABLE IF EXISTS `docs`");
                                                        $db->exec("CREATE TABLE `docs` (
                                                          `id` int(11) NOT NULL AUTO_INCREMENT,
                                                          `date` date NOT NULL,
                                                          `org_id_addr` int(11) NOT NULL, -- Склад
                                                          `org_id_client` int(11) NOT NULL,
                                                          PRIMARY KEY (`id`)
                                                        )");
                                                        $db->exec("INSERT `docs` (`id`, `date`, `org_id_addr`, `org_id_client`) VALUES (20515, '2016-10-12', 1, 500)");
                                                        
                                                        // Материалы
                                                        $db->exec("DROP TABLE IF EXISTS `materials`");
                                                        $db->exec("CREATE TABLE `materials` (
                                                          `id` int(11) NOT NULL AUTO_INCREMENT,
                                                          `name` varchar(255) NOT NULL,
                                                          `manufact_id` int(11) NOT NULL, -- Производитель
                                                          PRIMARY KEY (`id`),
                                                          UNIQUE KEY `name` (`name`)
                                                        )");
                                                        $q = $db->prepare("INSERT materials (name, manufact_id) VALUES (CONCAT('Материал товар №', LPAD(:name, 5, '0')), :manufact_id)");
                                                        for ($i = 1; $i <= $material_cn; $i++) {
                                                        	$q->execute(['name' => $i, 'manufact_id' => (($i % 10) + 1)]);
                                                        }
                                                        
                                                        // Остатки
                                                        $db->exec("DROP TABLE IF EXISTS `cur_stock`");
                                                        $db->exec("CREATE TABLE `cur_stock` (
                                                          `mat_id` int(11) NOT NULL,
                                                          `org_id` int(11) NOT NULL, -- Склад
                                                          `kol` decimal(10,3) NOT NULL,
                                                          PRIMARY KEY (`org_id`,`mat_id`),
                                                          KEY `mat_id` (`mat_id`),
                                                          CONSTRAINT `cur_stock_ibfk_1` FOREIGN KEY (`mat_id`) REFERENCES `materials` (`id`)
                                                        )");
                                                        //echo PHP_EOL.'<br>INSERT INTO cur_stock = '.
                                                        $db->exec("INSERT INTO cur_stock (mat_id, org_id, kol)
                                                        SELECT m.id mat_id, o.org_id, FLOOR(RAND() * 500) kol
                                                        FROM
                                                        (SELECT id FROM materials ORDER BY RAND() LIMIT ".floor($material_cn / 1.5).") m
                                                        , (SELECT 1 org_id UNION ALL SELECT 2 UNION ALL SELECT 3) o");
                                                        
                                                        
                                                        // Прайсы клиентов
                                                        $db->exec("DROP TABLE IF EXISTS `client_price`");
                                                        $db->exec("CREATE TABLE `client_price` (
                                                          `id` int(11) NOT NULL AUTO_INCREMENT,
                                                          `org_id` int(11) NOT NULL, -- Клиент
                                                          `price_type_id` int(11) NOT NULL, -- Тип прайса
                                                          `org_id_manufact` int(11) DEFAULT NULL, -- Производитель
                                                          `mat_id` int(11) DEFAULT NULL, -- Материал
                                                          `discount` decimal(5,2) DEFAULT NULL, -- Скидка
                                                          PRIMARY KEY (`id`),
                                                          UNIQUE KEY `org_id` (`org_id`,`price_type_id`,`org_id_manufact`,`mat_id`,`discount`),
                                                          KEY `price_type_id` (`price_type_id`),
                                                          KEY `mat_id` (`mat_id`),
                                                          KEY `org_id_manufact` (`org_id_manufact`),
                                                          CONSTRAINT `client_price_ibfk_1` FOREIGN KEY (`mat_id`) REFERENCES `materials` (`id`)
                                                        )");
                                                        // Общие прайсы
                                                        $q = $db->prepare("INSERT client_price (org_id, price_type_id) VALUES (:org_id, :price_type_id)");
                                                        for ($i = 1; $i <= $client_cn; $i++) {
                                                        	$q->execute(['org_id' => $i, 'price_type_id' => (($i % 2) + 1)]);
                                                        }
                                                        // Скидки по производителю
                                                        $db->exec("INSERT INTO client_price (org_id, price_type_id, org_id_manufact)
                                                        SELECT o.org_id
                                                        , FLOOR(1 + RAND() * 3) price_type_id
                                                        , m.manufact_id
                                                        FROM
                                                        (SELECT DISTINCT manufact_id FROM materials) m
                                                        , (SELECT DISTINCT org_id FROM client_price ORDER BY CASE WHEN org_id = 500 THEN 1 ELSE 2 END, RAND() LIMIT ".floor($client_cn / 10).") o
                                                        ORDER BY RAND()
                                                        LIMIT ".floor($client_cn / 2));
                                                        // Скидки по материалам
                                                        $db->exec("INSERT INTO client_price (org_id, price_type_id, mat_id)
                                                        SELECT o.org_id
                                                        , FLOOR(1 + RAND() * 3) price_type_id
                                                        , m.id
                                                        FROM
                                                        (SELECT id FROM materials ORDER BY RAND() LIMIT ".floor($material_cn / 10).") m
                                                        , (SELECT DISTINCT org_id FROM client_price ORDER BY CASE WHEN org_id = 500 THEN 1 ELSE 2 END, RAND() LIMIT ".floor($client_cn / 10).") o
                                                        ORDER BY RAND()
                                                        LIMIT ".(floor($client_cn / 10) * floor($material_cn / 10) / 10));
                                                        
                                                        // Курсы валют
                                                        $db->exec("DROP TABLE IF EXISTS `exchange_rate`");
                                                        $db->exec("CREATE TABLE IF NOT EXISTS `exchange_rate` (
                                                          `currency_id` int(11) NOT NULL,
                                                          `date` date NOT NULL,
                                                          `value` decimal(29,15) NOT NULL,
                                                          PRIMARY KEY (`currency_id`,`date`)
                                                        )");
                                                        $q = $db->prepare("INSERT exchange_rate (currency_id, date, value) VALUES (:currency_id, DATE(NOW()) - INTERVAL :i DAY, :value)");
                                                        for ($i = 100; $i >= 0; $i--) {
                                                        	$q->execute(['currency_id' => 2, 'i' => ($i + 1), 'value' => 100 - ($i / 100)]);
                                                        }
                                                        
                                                        // Прайсы
                                                        $db->exec("DROP TABLE IF EXISTS prices");
                                                        $db->exec("CREATE TABLE `prices` (
                                                          `id` int(11) NOT NULL AUTO_INCREMENT,
                                                          `price_type_id` int(11) NOT NULL,
                                                          `tmc_id` int(11) NOT NULL,
                                                          `date` date NOT NULL,
                                                          `price` decimal(29,15) NOT NULL,
                                                          `currency_id` int(11) NOT NULL DEFAULT '1',
                                                          PRIMARY KEY (`id`),
                                                          UNIQUE KEY `tmc_id` (`tmc_id`,`price_type_id`,`date`),
                                                          KEY `price_type_id` (`price_type_id`),
                                                          CONSTRAINT `prices_ibfk_2` FOREIGN KEY (`tmc_id`) REFERENCES `materials` (`id`)
                                                        )");
                                                        $db->exec("INSERT INTO prices (price_type_id, tmc_id, date, price, currency_id)
                                                        SELECT pt.price_type_id, m.id
                                                        , d.date
                                                        , FLOOR(RAND() * 1000) price
                                                        , FLOOR(1 + RAND() * 2) currency_id
                                                        FROM materials m
                                                        , (SELECT DATE(NOW()) - INTERVAL dd.id MONTH AS date FROM materials dd ORDER BY id LIMIT 10) d -- Даты
                                                        , (SELECT 1 price_type_id UNION ALL SELECT 2 UNION ALL SELECT 3) pt -- Типы прайсов
                                                        ");
                                                        
                                                        // Функция get_price
                                                        $db->exec("DROP FUNCTION IF EXISTS get_client_price");
                                                        $db->exec("CREATE FUNCTION get_client_price(v_org_id INT, v_mat_id INT, v_date DATE)
                                                          RETURNS decimal(29,15) READS SQL DATA
                                                        BEGIN
                                                          /* версия 00002 */
                                                          DECLARE v_price_date date;
                                                          DECLARE v_price decimal(29,15);
                                                          DECLARE EXIT HANDLER FOR NOT FOUND BEGIN
                                                            RETURN NULL;
                                                          END;
                                                          SET @client_price_type_id := NULL;
                                                          SET @client_price_discount := NULL;
                                                          SET @client_price_date := NULL;
                                                          -- Определяем тип прайса
                                                          SELECT price_type_id, discount
                                                           INTO @client_price_type_id, @client_price_discount
                                                           FROM (
                                                           SELECT cp.price_type_id, cp.discount, 10 rule
                                                           FROM client_price cp
                                                           WHERE cp.org_id = v_org_id
                                                            AND cp.mat_id = v_mat_id
                                                            AND cp.org_id_manufact IS NULL
                                                           UNION ALL
                                                           SELECT cp.price_type_id, cp.discount, 50 rule
                                                           FROM client_price cp
                                                           INNER JOIN materials m ON cp.org_id_manufact = m.manufact_id
                                                           WHERE cp.org_id = v_org_id
                                                            AND m.id = v_mat_id
                                                            AND cp.mat_id IS NULL
                                                           UNION ALL
                                                           SELECT cp.price_type_id, cp.discount, 100 rule
                                                           FROM client_price cp
                                                           WHERE cp.org_id = v_org_id
                                                            AND cp.mat_id IS NULL
                                                            AND cp.org_id_manufact IS NULL
                                                           ) t
                                                           ORDER BY rule
                                                           LIMIT 1;
                                                          -- Определяем цену
                                                          SELECT p.price
                                                          * CASE WHEN p.currency_id = 1 THEN 1
                                                            ELSE (SELECT value FROM exchange_rate r WHERE p.currency_id = r.currency_id AND r.date <= v_date ORDER BY r.date DESC LIMIT 1)
                                                           END
                                                          * (100 - IFNULL(@client_price_discount, 0)) / 100 price
                                                          , p.date
                                                           INTO v_price, @client_price_date
                                                           FROM prices p
                                                           WHERE p.price_type_id = @client_price_type_id
                                                            AND p.tmc_id = v_mat_id
                                                            AND p.date <= v_date
                                                          ORDER BY p.date DESC
                                                          LIMIT 1;
                                                        
                                                          RETURN v_price;
                                                        END;
                                                        ");
                                                        
                                                        $db->exec("SET FOREIGN_KEY_CHECKS=1");
                                                        
                                                        
                                                        echo sprintf("%.6f", microtime(true) - $_SERVER["REQUEST_TIME_FLOAT"]);
                                                        

                                                        get_db_list.php
                                                        <?php
                                                        require_once 'config.php';
                                                        
                                                        $config = new Config();
                                                        $db = new PDO("mysql:host=" . $config->db_host.";dbname=".$config->db_name, $config->db_username, $config->db_password);
                                                        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                                                        $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
                                                        $db->exec('SET names utf8');
                                                        $db->exec('SET storage_engine=innoDB');
                                                        $db->exec("SET sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY'");
                                                        
                                                        $term = isset($_REQUEST['term'])?$_REQUEST['term']:'';
                                                        $doc_id = isset($_REQUEST['doc_id'])?$_REQUEST['doc_id']:null;
                                                        $page = @$_REQUEST['page']*1?$_REQUEST['page']:1;
                                                        //$per_page = 1000;
                                                        $per_page = @$_REQUEST['page']*1?$_REQUEST['per_page']:50;
                                                        
                                                        $sql = "SELECT SQL_NO_CACHE CONCAT(m.name
                                                        , ' - ', IFNULL(TRIM(get_client_price(d.org_id_client, m.id, d.date)) + 0, '')
                                                        , ' - ', IFNULL(cs.kol, '')
                                                        ) name
                                                        FROM materials m
                                                        LEFT JOIN docs d ON d.id = :doc_id
                                                        LEFT JOIN cur_stock cs ON cs.org_id = d.org_id_addr AND m.id = cs.mat_id
                                                        WHERE m.name LIKE CONCAT('%', IFNULL(:term, ''), '%')
                                                         AND NOW() = NOW()
                                                        ORDER BY m.name
                                                        ";
                                                        $sql .= PHP_EOL.'LIMIT '.(($page - 1) * $per_page).', '.$per_page;
                                                        $q = $db->prepare($sql);
                                                        $q->execute(['doc_id' => $doc_id, 'term' => $term]);
                                                        $q->setFetchMode(PDO::FETCH_ASSOC);
                                                        $data = $q->fetchAll();
                                                        $return = [];
                                                        $return['total_count'] = ($page - 1) * $per_page + (sizeof($data) < $per_page?sizeof($data):($per_page+1));
                                                        $return['items'] = $data;
                                                        $return['time'] = sprintf("%.6f", microtime(true) - $_SERVER["REQUEST_TIME_FLOAT"]);
                                                        echo json_encode($return);
                                                        

                                                        get_app_list.php
                                                        <?php
                                                        require_once 'config.php';
                                                        
                                                        $config = new Config();
                                                        $db = new PDO("mysql:host=" . $config->db_host.";dbname=".$config->db_name, $config->db_username, $config->db_password);
                                                        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                                                        $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
                                                        $db->exec('SET names utf8');
                                                        $db->exec('SET storage_engine=innoDB');
                                                        $db->exec("SET sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY'");
                                                        
                                                        $term = isset($_REQUEST['term'])?$_REQUEST['term']:'';
                                                        $doc_id = isset($_REQUEST['doc_id'])?$_REQUEST['doc_id']:null;
                                                        $page = @$_REQUEST['page']*1?$_REQUEST['page']:1;
                                                        //$per_page = 1000;
                                                        $per_page = @$_REQUEST['page']*1?$_REQUEST['per_page']:50;
                                                        
                                                        // Построением sql запросов будет заниматься ОРМ, но для простоты пишу итоговые запросы который сгенерит ОРМ
                                                        // Документ
                                                        $sql = "SELECT SQL_NO_CACHE * FROM docs WHERE id = :doc_id AND NOW() = NOW()";
                                                        $q = $db->prepare($sql);
                                                        $q->execute(['doc_id' => $doc_id]);
                                                        $doc = $q->fetch();
                                                        
                                                        // Материалы
                                                        $sql = "SELECT SQL_NO_CACHE m.id, m.name
                                                        , cs.kol
                                                        FROM materials m
                                                        LEFT JOIN cur_stock cs ON cs.org_id = :org_id AND m.id = cs.mat_id
                                                        WHERE m.name LIKE CONCAT('%', :term, '%')
                                                         AND NOW() = NOW()";
                                                        $sql .= PHP_EOL.'LIMIT '.(($page - 1) * $per_page).', '.$per_page;
                                                        $q = $db->prepare($sql);
                                                        $q->execute(['org_id' => $doc['org_id_addr'], 'term' => $term]);
                                                        $materials = $q->fetchAll();
                                                        
                                                        function get_client_price($mat, $doc){
                                                        	global $db;
                                                        	// Определяем скидку по клиенту
                                                        	// Скидка по товару
                                                        	$sql = "SELECT SQL_NO_CACHE cp.price_type_id, cp.discount
                                                           FROM client_price cp
                                                           WHERE cp.org_id = :org_id
                                                            AND cp.mat_id = :mat_id
                                                            AND cp.org_id_manufact IS NULL
                                                            AND NOW() = NOW()";
                                                        	$q = $db->prepare($sql);
                                                        	$q->execute(['org_id' => $doc['org_id_client'], 'mat_id' => $mat['id']]);
                                                        	$r = $q->fetch();
                                                        	if ($r === false) {
                                                        		// Скидка по производителю
                                                        		$sql = "SELECT SQL_NO_CACHE cp.price_type_id, cp.discount
                                                           FROM client_price cp
                                                           INNER JOIN materials m ON cp.org_id_manufact = m.manufact_id
                                                           WHERE cp.org_id = :org_id
                                                            AND m.id = :mat_id
                                                            AND cp.mat_id IS NULL
                                                            AND NOW() = NOW()";
                                                        		$q = $db->prepare($sql);
                                                        		$q->execute(['org_id' => $doc['org_id_client'], 'mat_id' => $mat['id']]);
                                                        		$r = $q->fetch();
                                                        		if ($r === false) {
                                                        			// Общая скидка
                                                        			$sql = "SELECT SQL_NO_CACHE cp.price_type_id, cp.discount
                                                           FROM client_price cp
                                                           WHERE cp.org_id = :org_id
                                                            AND cp.mat_id IS NULL
                                                            AND cp.org_id_manufact IS NULL
                                                            AND NOW() = NOW()";
                                                        			$q = $db->prepare($sql);
                                                        			$q->execute(['org_id' => $doc['org_id_client']]);
                                                        			$r = $q->fetch();
                                                        			if ($r === false) {
                                                        				$r = ['price_type_id' => null, 'discount' => 0];
                                                        			}
                                                        		}
                                                        	}
                                                        	$client_price_type_id = $r['price_type_id'];
                                                        	$client_price_discount = $r['discount'];
                                                        	//exit($doc['org_id_client'].' - '.$client_price_type_id.' - '.$client_price_discount);
                                                        	// Определяем цену
                                                        	$sql = "SELECT SQL_NO_CACHE p.price, p.date, p.currency_id
                                                           FROM prices p
                                                           WHERE p.price_type_id = :client_price_type_id
                                                            AND p.tmc_id = :mat_id
                                                            AND p.date <= :date
                                                            AND NOW() = NOW()
                                                          ORDER BY p.date DESC
                                                          LIMIT 1";
                                                        	$q = $db->prepare($sql);
                                                        	$q->execute(['client_price_type_id' => $client_price_type_id, 'mat_id' => $mat['id'], 'date' => $doc['date']]);
                                                        	$r = $q->fetch();
                                                        	if ($r === false) {
                                                        		return null;
                                                        	}
                                                        	$price = (100 - ($client_price_discount?$client_price_discount:0)) * $r['price'] / 100;
                                                        	// Если Валюта не Рубль, то пересчитаем по курсу на дату
                                                        	if ($r['currency_id'] != 1) {
                                                        		$sql = "SELECT SQL_NO_CACHE value FROM exchange_rate r WHERE :currency_id = r.currency_id AND r.date <= :date AND NOW() = NOW() ORDER BY r.date DESC LIMIT 100";
                                                        		$q = $db->prepare($sql);
                                                        		$q->execute(['currency_id' => $r['currency_id'], 'date' => $doc['date']]);
                                                        		//$r = $q->fetch(); $value = $r['value'];
                                                        		$value = $q->fetchColumn();
                                                        		$price = $price * $value;
                                                        	}
                                                        	return $price;
                                                        }
                                                        
                                                        $data = [];
                                                        foreach ($materials as $material) {
                                                        	$price = get_client_price($material, $doc);
                                                        	$data[] = ['name' => $material['name'].' - '.$price.' - '.$material['kol']];
                                                        }
                                                        $return = [];
                                                        $return['total_count'] = ($page - 1) * $per_page + (sizeof($data) < $per_page?sizeof($data):($per_page+1));
                                                        $return['items'] = $data;
                                                        $return['time'] = sprintf("%.6f", microtime(true) - $_SERVER["REQUEST_TIME_FLOAT"]);
                                                        echo json_encode($return);
                                                        

                                                        selectlist.html
                                                        <!DOCTYPE html>
                                                        <html lang="en">
                                                        <head>
                                                        <meta charset="UTF-8">
                                                        <title></title>
                                                        <link href="//cdnjs.cloudflare.com/ajax/libs/select2/4.0.3/css/select2.min.css" rel="stylesheet" />
                                                        <script src="//cdnjs.cloudflare.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
                                                        </head>
                                                        <body>
                                                        <div>Per page <input type="number" value="500" id="per_page"></div>
                                                        <div>Номер документа <input type="number" value="20515" id="doc_id"></div>
                                                        <div>Список из app <select id="sl_app" style="width: 400px;"></select> <span id="time_sl_app"></span></div>
                                                        <div>Список из DB <select id="sl_db" style="width: 400px;"></select> <span id="time_sl_db"></span></div>
                                                        <script>
                                                        $(function(){
                                                            $('#sl_db').select2({allowClear: true
                                                                , placeholder: "Выберите значение"
                                                                , language: 'ru'
                                                                , ajax: {url: "get_db_list.php"
                                                                    , dataType: 'json'
                                                                    , delay: 250
                                                                    , data: function (params) {
                                                                        return {
                                                                            term: params.term
                                                                            , page: params.page
                                                                            , doc_id: $('#doc_id').val()
                                                                            , per_page: $('#per_page').val()
                                                                        };
                                                                    }
                                                                    , processResults: function (data, params) {
                                                                        params.page = params.page || 1;
                                                                        $('#time_sl_db').text(data.time);
                                                                        return {
                                                                            results: data.items
                                                                            , pagination: {
                                                                                more: (params.page * 50) < data.total_count
                                                                            }
                                                                        };
                                                                    }
                                                                }
                                                                , templateResult: function(repo) {
                                                                    if (repo.loading) return repo.text;
                                                                    return repo.name;
                                                                }
                                                                , templateSelection: function(repo) {
                                                                    return repo.name || repo.text;
                                                                }
                                                                , escapeMarkup: function(markup) {
                                                                    return markup;
                                                                }
                                                        
                                                            });
                                                            $('#sl_app').select2({allowClear: true
                                                                , placeholder: "Выберите значение"
                                                                , language: 'ru'
                                                                , ajax: {url: "get_app_list.php"
                                                                    , dataType: 'json'
                                                                    , delay: 250
                                                                    , data: function (params) {
                                                                        return {
                                                                            term: params.term
                                                                            , page: params.page
                                                                            , doc_id: $('#doc_id').val()
                                                                            , per_page: $('#per_page').val()
                                                                        };
                                                                    }
                                                                    , processResults: function (data, params) {
                                                                        params.page = params.page || 1;
                                                                        $('#time_sl_app').text(data.time);
                                                                        return {
                                                                            results: data.items
                                                                            , pagination: {
                                                                                more: (params.page * 50) < data.total_count
                                                                            }
                                                                        };
                                                                    }
                                                                }
                                                                , templateResult: function(repo) {
                                                                    if (repo.loading) return repo.text;
                                                                    return repo.name;
                                                                }
                                                                , templateSelection: function(repo) {
                                                                    return repo.name || repo.text;
                                                                }
                                                                , escapeMarkup: function(markup) {
                                                                    return markup;
                                                                }
                                                            });
                                                        })
                                                        </script>
                                                        </body>
                                                        <script src="//cdnjs.cloudflare.com/ajax/libs/select2/4.0.3/js/select2.full.js"></script>
                                                        <script src="//cdnjs.cloudflare.com/ajax/libs/select2/4.0.3/js/i18n/ru.js"></script>
                                                        </html>
                                                        
                                                          +1
                                                          Тоже извиняюсь за задержку.
                                                          Вижу, вы уже сами сделали алгоритм и в БД и в приложении. Так как тут нас интересует скорость отдачи контента, а не читаемость, то пожалуй тогда не буду переписывать код на ORM. Просто покажу, как можно ускорить показ результатов — добавить внешнюю систему кэширования Memcached. Понятно, что в базе тоже можно настроить кэширование, но кэшироваться будут результаты отдельных запросов, а в случае с get_client_price() можно запоминать готовый результат всех расчетов.

                                                          Итак, вначале на моей машине были такие показатели:
                                                          Per page: 500
                                                          Номер документа: 20515
                                                          Список из app: 0.96 — 1.02
                                                          Список из DB: 0.34 — 0.36

                                                          После добавления кэширования:
                                                          Список из app: 1.16 — 1.20 (первый вызов)
                                                          Список из app: 0.04 — 0.08 (последующие вызовы)

                                                          Это потребовало добавления нескольких строчек без особого изменения остального кода (коммит на github). Там кеширование на 10 секунд, в зависимости от характера изменений в таблицах можно сделать на большее время, или постоянно с инвалидацией кеша при внесении изменений.

                                                          Размещение логики в коде дает больше возможностей, как по описанию этой логики, так и по управлению данными.
                                                            0
                                                            Вы же понимаете что любое кеширование и денормализация это усложнение логики, которую надо поддерживать и отслеживать + можно нарываться на очень неприятные ошибки, которые ни юнит тестами не покроешь, ни в дебагере не поймаешь. Например, при добавлении зависимой сущности забыли закодить сбрасывание кеша.

                                                            Если уж применять кеширование, то тут эталон для меня это реализация в Oracle через MATERIALIZED_VIEW + QUERY_REWRITE.
                                                            1. Код кеширования находится отдельно от кода модели данных и никак с ним не связан
                                                            2. При изменении модели данных максимальный ущерб это запрос не будет использовать кеш, но мы не получим несогласованных данных.

                                                            Но я не хотел обсуждать кеширование, мой посыл в другом: разрабатывая приложение в БД Вы имеете серьёзную фору по производительности, поэтому Вам гораздо реже потребуются различные костыли в виде кеширования и т.д. Соответственно код будет более лаконичный и простой, кодить проще, поддерживать проще и дешевле. Приложение более масштабируемое.

                                                            Кстати, в коде получения данных для БЛ в приложении я забыл начать транзакцию, а это залочит на запись ВСЕ таблицы из которых мы читаем данные. Не сделав это мы можем получить несогласованные данные
                                                  0
                                                  БЛ в БД только на стандартных SQL, предоставляемых БД — сущее зло.
                                                  Как насчет плагинов (MySQL/MariaDB) или модулей (Postgres)?

                                                  Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                                  Самое читаемое