Сучасна диджитал-освіта для дітей — безоплатне заняття в GoITeens ×
Mazda CX 5
×

Генерація SQL-запиту засобами MySQL-сервера

Ще далекого 2015 року в СКБД MySQL, починаючи з версії 5.7.8, додали підтримання нового типу даних JSON. Насамперед це створило нові можливості для роботи з даними, які з тих чи інших причин не потребують нормалізації. Однак мене це нововведення більше зацікавило суттєвим розширенням можливостей взаємодії зі збереженими процедурами.

Зазвичай, при роботі з БД за класичною архітектурою «клієнт-сервер», створення запиту відбувається на стороні клієнта. А коли він вже остаточно сформований — відправляється на сервер для виконання. Такий підхід надає клієнту максимальну гнучкість для роботи з базою даних, але він має і недоліки.

Один з недоліків такого підходу в тому, що парадигма програмування в середовищі клієнта дуже сильно відрізняється від тієї, що використовуються в СКБД. Звичайно ж, з технічної точки зору, сформувати в клієнті текстовий рядок, навіть з дуже складним SQL-запитом, не є непосильним завданням. Та зі сторони це виглядає доволі неприродно, особливо коли доводиться використовувати умовні конструкції в клієнті, наприклад, з об’єктно-орієнтованою парадигмою. А вам ще до того ж необхідно в цей запит додати безліч різноманітних даних, як то значення поля чи фільтра для відбору.

Архітектура клієнт-сервер

Частково цю проблему намагаються розв’язати за допомогою спеціалізованих засобів генерування SQL-запитів на кшталт QueryBuilder. У них можна конструювати запит за допомогою методів об’єкта, і після завершення автоматично перетворити його в рядок для передавання на сервер. Проте подібні рішення не розв’язують проблему, а тільки її приховують — виносять за межі розроблення проекту клієнта. Окрім цього, вони додатково навантажують клієнтську програму без нагальної на це потреби.

А ще такі застосунки не допомагають з перевірянням і налагодженням запиту перед відправленням на сервер, адже їм бракує інформації про схему БД. Хоча деякі сучасні великі IDE, що використовують для розроблення клієнтських проектів, можна під’єднати до БД проекту для перевіряння запитів під час їх створення. Погодьтеся, що рідне середовище IDE СКБД ліпше пристосоване для розв’язання таких завдань.

Щоб точніше пояснити суть проблеми з формуванням запитів на боці клієнта, уявіть, що ми до звичайного легкового автомобіля спробуємо причепити крила, гвинт і хвостове оперення. Хоч скільки б ми намагалися, навіть якщо такий автомобіль колись і полетить, йому буде доволі важко конкурувати з літаками. Бо літаки від початку конструюють для польотів, а автомобіль — для їзди твердою поверхнею.

І тоді я подумав: а чому б нам не перенести всі запити проекту з клієнта в збережені процедури на сервері й взаємодіяти з БД через них? У такому разі, замість того щоб схрещувати автомобіль з літаком у клієнті, можна буде працювати із запитами до БД в рідному для нього середовищі СКБД. Тільки реально виявилося, що не все так просто — є певне обмеження, вузьке горлечко, яке заважає повноцінно використовувати такий підхід.

Річ у тім, що для виконання більшості SQL-запитів потрібні дані з клієнта, які вставляють у тіло запиту. І якщо таких даних небагато, наприклад ідентифікатор запису таблиці для його отримання чи видалення, то з цим проблем немає. Їх цілком зручно передавати на сервер у збережені процедури як вхідні параметри.

А що робити, коли вам потрібно зберегти чи оновити значення великої кількості полів запису в таблиці? Чи передати велику кількість параметрів відбору для фільтрування вибірки з БД? У таких випадках кількість даних клієнта може деколи становити 20―30 штук чи навіть більше. Відповідно передавати ці дані через вхідні параметри процедури стає, м’яко кажучи, не дуже зручно.

І як саме в таких типових ситуаціях нам стане в пригоді новий тип даних JSON? З його допомогою ми зможемо в дуже зручному вигляді передавати в збережені процедури велику кількість даних з клієнта. До того ж на боці клієнта можна компактно опрацьовувати велику кількість змінних одним пакетом за допомогою, наприклад, асоціативного масиву чи об’єкта (Entity, Collection). А потім автоматично конвертувати їх у JSON-формат для передавання на сервер без потреби прописувати кожен параметр окремо.

Тип даних JSON

JSON — здавалось би, що може бути простіше? Хіба що CSV і, можливо, YAML. Але ж ні — для роботи з ним в СКБД MySQL довелося додавати цілий зоопарк нових функцій.

Найпоширеніші функції, з якими нам сьогодні доведеться мати справу це: ->, ->> та JSON_TYPE(). Хоча для виконання складніших завдань їх явно бракуватиме. А оскільки невідомо, які функції вам знадобляться в майбутньому проекті — пропоную весь перелік з коротким описом.

->Повертає значення JSON-запису за вказаним шляхом
->>Повертає значення JSON-запису за вказаним шляхом з видаленим обрамленням з подвійних лапок
JSON_ARRAY()Створює JSON-масив
JSON_ARRAY_APPEND()Додає дані до JSON-документа
JSON_ARRAY_INSERT()Вставляє в JSON-масив
JSON_CONTAINS()Визначає наявність вказаного об’єкта в JSON-документі згідно зі шляхом
JSON_CONTAINS_PATH()Визначає наявність будь-яких даних у JSON-документі згідно зі шляхом
JSON_DEPTH()Визначає максимальну глибину JSON-документа
JSON_EXTRACT()Повертає дані з JSON-документа
JSON_INSERT()Вставляє дані в JSON-документ
JSON_KEYS()Повертає перелік ключів JSON-документа
JSON_LENGTH()Визначає кількість елементів у JSON-документі
JSON_MERGE_PATCH()Об’єднує JSON-документи, замінюючи значення однойменних ключів
JSON_MERGE_PRESERVE()Об’єднує JSON-документи, зберігаючи значення однойменних ключів
JSON_OBJECT()Створює JSON-об’єкт
JSON_OVERLAPS()Порівнює два JSON-документи
JSON_PRETTY()Друкує JSON-документ у зручному для читання вигляді
JSON_QUOTE()Обрамляє рядок подвійними лапками для JSON-документа
JSON_REMOVE()Видаляє дані з JSON-документа
JSON_REPLACE()Замінює значення в JSON-документі
JSON_SCHEMA_VALID()Перевіряє JSON-документ відповідно до JSON-схеми
JSON_SCHEMA_VALIDATION_REPORT()Повертає звіт перевіряння JSON-документа на відповідність JSON-схемі
JSON_SEARCH()Повертає шлях до значення в JSON-документі
JSON_SET()Вставляє дані в JSON-документ
JSON_STORAGE_FREE()Визначає кількість вивільненого місця в бінарному представленні JSON-запису після часткового оновлення його значення
JSON_STORAGE_SIZE()Визначає кількість використаного місця для зберігання бінарного представлення JSON-документа
JSON_TABLE()Повертає дані з JSON-виразу у формі реляційної таблиці
JSON_TYPE()Визначає тип JSON-значення
JSON_UNQUOTE()Видаляє обрамлені подвійні лапки JSON-значення
JSON_VALID()Перевіряє JSON-значення на дійсність
MEMBER OF()Визначає наявність конкретного значення в JSON-масиві

Крім цих функцій, є ще дві для агрегації: JSON_ARRAYAGG() та JSON_OBJECTAGG(). А також я не вказав функцію JSON_MERGE(), бо вона призначена для видалення в наступних версіях (deprecated 8.0.3).

З докладнішим описом наведених вище функцій для роботи з типом даних JSON можна ознайомитися в розділі «Довідник функцій JSON» офіційної документації MySQL.

Збережені процедури

Мені не вдалося відшукати в Інтернеті хоч якусь корисну інформацію про використання JSON для передавання параметрів у збережені процедури. Тому, не довго думаючи, вирішив перевірити свої ідеї на експериментальному, але водночас цілком працездатному проекті. Клієнтом БД мало стати простеньке інтернет-видання, написане мовою PHP, по змозі наближене до реального. Однак у результаті вийшло аж два незалежних клієнти — сам сайт для виведення статей та окремо адміністративний сайт для їх редагування.

В СКБД MySQL я створив гетери/сетери — процедури виконання типових операцій CRUD за аналогією з шаблоном проектуванням «Сховище» (Repository/Storage). У підсумку вийшло щось схоже на API для роботи з БД, що створило ще як мінімум три додаткові переваги порівняно з класичним підходом.

По-перше, під час використання декількох різних клієнтів запити не дублюються в кожному з них, а зберігаються централізовано в одному місці, що неодмінно полегшує їх супроводження. По-друге, такий підхід інкапсулює від інших розробників частину коду, який їм не потрібно редагувати, а це, відповідно, робить проект надійнішим. По-третє, саме для створення запитів до проекту можна залучити, по змозі або в перспективі, окремого вузькоспеціалізованого працівника, що може значно підвищити швидкість та якість розробляння.

CREATE PROCEDURE ArticleGet (IN `_id` INT UNSIGNED) ...;
CREATE PROCEDURE ArticleGetByAlias (IN `_alias` VARCHAR(128)) ...;
CREATE PROCEDURE ArticleGetIndex (IN `_params` JSON) ...;
CREATE PROCEDURE ArticleSet (IN `_params` JSON) ...;
CREATE PROCEDURE ArticleUnset (IN `_id` INT UNSIGNED) ...;
CREATE PROCEDURE CategoryGet (IN `_id` TINYINT UNSIGNED) ...;
CREATE PROCEDURE CategoryGetAll () ...;
CREATE PROCEDURE CategoryGetByAlias (IN `_alias` VARCHAR(128)) ...;
CREATE PROCEDURE CategoryGetIndex (IN `_params` JSON) ...;
CREATE PROCEDURE CategorySet (IN `_params` JSON) ...;
CREATE PROCEDURE CategoryUnset (IN `_id` TINYINT(3) UNSIGNED) ...;
CREATE PROCEDURE CommentGet (IN `_id` INT UNSIGNED) ...;
CREATE PROCEDURE CommentGetIndex (IN `_params` JSON) ...;
CREATE PROCEDURE CommentSet (IN `_params` JSON) ...;
CREATE PROCEDURE CommentUnset (IN `_id` INT UNSIGNED) ...;
CREATE PROCEDURE PageGet (IN `_id` TINYINT UNSIGNED) ...;
CREATE PROCEDURE PageGetByAlias (IN `_alias` VARCHAR(32)) ...;
CREATE PROCEDURE PageGetIndex (IN `_params` JSON) ...;
CREATE PROCEDURE PageSet (IN `_params` JSON) ...;
CREATE PROCEDURE PageUnset (IN `_id` TINYINT UNSIGNED) ...;
CREATE PROCEDURE RoleGetIndex () ...;
CREATE PROCEDURE TagAutocomplete (IN `_title` VARCHAR(32), IN `_exclude` VARCHAR(32)) ...;
CREATE PROCEDURE TagGet (IN `_id` SMALLINT(5) UNSIGNED) ...;
CREATE PROCEDURE TagGetByAlias (IN `_alias` VARCHAR(32)) ...;
CREATE PROCEDURE TagGetByIDs (IN `_ids` VARCHAR(32)) ...;
CREATE PROCEDURE TagGetIndex (IN `_params` JSON) ...;
CREATE PROCEDURE TagSet (IN `_params` JSON) ...;
CREATE PROCEDURE TagUnset (IN `_id` SMALLINT UNSIGNED) ...;
CREATE PROCEDURE UserAuthorize (IN `_email` VARCHAR(32), IN `_password` VARCHAR(32)) ...;
CREATE PROCEDURE UserGet (IN `_id` TINYINT UNSIGNED) ...;
CREATE PROCEDURE UserGetByAlias (IN `_alias` VARCHAR(32)) ...;
CREATE PROCEDURE UserGetIndex (IN `_params` JSON) ...;
CREATE PROCEDURE UserSet (IN `_params` JSON) ...;
CREATE PROCEDURE UserUnset (IN `_id` TINYINT UNSIGNED) ...;
CREATE PROCEDURE _GetFoundRows () ...;

Тут, гадаю, усе для всіх зрозуміло, і пояснювати нічого не варто, окрім деяких моментів. Перша незручність, яка впадає в око — неможливість створювати об’єкти, хоча б заради групування збережених процедур. Гадаю, було б набагато зручніше й цікавіше, якби в MySQL можна було б створювати об’єкти, наприклад, так:

DELIMITER $$
CREATE OBJECT Article
BEGIN
    CREATE PROCEDURE Gеt (IN `_id` INT UNSIGNED) ...;
    CREATE PROCEDURE GetByAlias (IN `_alias` VARCHAR(128)) ...;
    CREATE PROCEDURE GetIndex (IN `_params` JSON) ...;
    CREATE PROCEDURE Sеt (IN `_params` JSON) ...;
    CREATE PROCEDURE Unset (IN `_id` INT UNSIGNED) ...;
END$$

Друге, на що ви могли звернути увагу — використання знака підкреслення _ перед назвами змінних. Річ у тім, що в MySQL є чимало службових і зарезервованих слів, і подекуди назви змінних з ними збігаються. Тому під час використання змінних без знака підкреслення вони спеціально виокремлюють в IDE й це дуже збиває з пантелику.

Спочатку, щоб виокремити змінні, я планував використати знак «равлик» @ (визначена користувачем змінна). Проте мені не хочеться зайвий раз без нагальної потреби розширювати поле видимості змінної та збільшувати тривалість її життя навіть у межах сесії. Потім я згадав, що в документації дозволено в назвах використовувати знак долара $, який в PHP виконує ту ж саму функцію. Тільки він не допоміг — змінні зі знаком долара на початку однаково підсвічувалися як службові чи зарезервовані.

Тому я вирішив використовувати знак підкреслення, передусім через його гарантовану ефективність і сумісність. Перед ним ще можна ставити додаткову літеру, наприклад v (v_id, v_time, v_text). А втім я не став мудрувати й поки що задовольнився застосуванням лише цього знака без ніяких додаткових літер.

А тепер перейдімо до змісту самих процедур. Описувати їх усіх рації немає — вони функціонально подібні в межах своєї таблиці (об’єкта). Я виберу для опису тільки перші п’ять, ті, що призначено для роботи з таблицею Article. Але спочатку, про всяк випадок, я наведу структуру цієї таблиці.

CREATE TABLE `article` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `time` datetime NOT NULL,
    `title` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
    `description` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL,
    `text` text COLLATE utf8mb4_unicode_ci NOT NULL,
    `image` varchar(48) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `alias` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
    `category` tinyint(3) unsigned NOT NULL,
    `user` tinyint(3) unsigned NOT NULL,
    `status` tinyint(1) unsigned NOT NULL DEFAULT '1',
    PRIMARY KEY (`id`),
    KEY `category` (`category`),
    KEY `user` (`user`),
    CONSTRAINT `article_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category` (`id`),
    CONSTRAINT `article_ibfk_3` FOREIGN KEY (`user`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

І почнемо з першої збереженої процедури, яку використовують найчастіше — ArticleGet.

DELIMITER $$
CREATE PROCEDURE `ArticleGet`(IN `_id` INT UNSIGNED)
BEGIN
    SELECT      `a`.*, `u`.`id` AS `userID`, `u`.`title` AS `userTitle`,
                GROUP_CONCAT(DISTINCT `at`.`tag` SEPARATOR ',') AS 'tags'
    FROM        `article` AS `a`
    INNER JOIN  `user` AS `u` ON `u`.`id` = `a`.`user`
    INNER JOIN  `article_tag` AS `at` ON `at`.`article` = `a`.`id`
    WHERE       `a`.`id` = _id
    GROUP BY    `a`.`id`;
END$$

Призначення в неї дуже просте: знайти запис у таблиці за його ідентифікатором, скомпонувати з іншими відповідними записами з інших таблиць і повернути клієнту. Щоб виконати це завдання, від клієнта потрібно отримати лише єдине значення, тому JSON тут використовувати недоцільно — досить передати це значення в процедуру як вхідний параметр. Зверніть особливу увагу на те, який простий, елегантний і гармонійний вигляд мають запити у своєму рідному середовищі.

Звісно ж, у MySQL є команда EXECUTE, за допомогою якої можна виконувати запити, попередньо скомпоновані у формі рядка. Та це все матиме набагато заплутаніший вигляд, особливо якщо його застосувати до великого й складного запиту з декількома вкладеними запитами. До того ж так само запити формують у клієнтах, і ми, відповідно, втрачаємо одну з переваг від створення запитів на боці сервера.

DELIMITER $$
CREATE PROCEDURE `ArticleGetByAlias`(IN `_alias` VARCHAR(128))
BEGIN
    SELECT      `a`.*, `u`.`title` AS `userTitle`, `u`.`alias` AS `userAlias`,
                GROUP_CONCAT(
                    DISTINCT CONCAT(`t`.`title`, '/', `t`.`alias`) SEPARATOR ','
                ) AS 'tags'
    FROM        `article` AS `a`
    INNER JOIN  `article_tag` AS `at` ON `at`.`article` = `a`.`id`
    INNER JOIN  `tag` AS `t` ON `t`.`id` = `at`.`tag`
    INNER JOIN  `user` AS `u` ON `u`.`id` = `a`.`user`
    WHERE       `a`.`alias` = _alias
    GROUP BY    `a`.`id`;
END$$

Процедура ArticleGetByAlias аналогійна до попередньої, за винятком вхідного параметра пошуку запису й метода компонування міток. Я навів її просто як приклад використання двох подібних процедур для двох різних клієнтів. Попередню процедуру використовує адміністративний сайт, де звернення до об’єкта редагування відбувається за його ідентифікатором. А другу процедуру використовує сайт для виведення статей, де звернення до статті відбувається за його адресою в посиланні (alias).

DELIMITER $$
CREATE PROCEDURE `ArticleGetIndex`(IN `_params` JSON)
BEGIN
  DECLARE _dateBegin DATETIME;
  DECLARE _dateEnd DATETIME;
  DECLARE _title VARCHAR(32);
  DECLARE _categoryID TINYINT(3);
  DECLARE _categoryTitle VARCHAR(32);
  DECLARE _tagID SMALLINT(5);
  DECLARE _tagTitle VARCHAR(32);
  DECLARE _userID TINYINT(3);
  DECLARE _userTitle VARCHAR(32);
  DECLARE _status TINYINT(1) UNSIGNED;
  DECLARE _orderField VARCHAR(32) DEFAULT 'id';
  DECLARE _orderDirection INTEGER DEFAULT 1;
  DECLARE _rowsOffset INTEGER UNSIGNED DEFAULT 0;
  DECLARE _rowsLimit INTEGER UNSIGNED DEFAULT 100;

  IF (JSON_TYPE(_params->'$.dateBegin') <> 'NULL') THEN
    SET _dateBegin = CONCAT(_params->>'$.dateBegin', ' 00:00:00'); END IF;
  IF (JSON_TYPE(_params->'$.dateEnd') <> 'NULL') THEN
    SET _dateEnd = CONCAT(_params->>'$.dateEnd', ' 23:59:59'); END IF;
  IF (JSON_TYPE(_params->'$.title') <> 'NULL') THEN
    SET _title = _params->>'$.title'; END IF;
  IF (JSON_TYPE(_params->'$.categoryID') <> 'NULL') THEN
    SET _categoryID = _params->'$.categoryID'; END IF;
  IF (JSON_TYPE(_params->'$.categoryTitle') <> 'NULL') THEN
    SET _categoryTitle = _params->>'$.categoryTitle'; END IF;
  IF (JSON_TYPE(_params->'$.tagID') <> 'NULL') THEN
    SET _tagID = _params->'$.tagID'; END IF;
  IF (JSON_TYPE(_params->'$.tagTitle') <> 'NULL') THEN
    SET _tagTitle = _params->>'$.tagTitle'; END IF;
  IF (JSON_TYPE(_params->'$.userID') <> 'NULL') THEN
    SET _userID = _params->'$.userID'; END IF;
  IF (JSON_TYPE(_params->'$.userTitle') <> 'NULL') THEN
    SET _userTitle = _params->>'$.userTitle'; END IF;
  IF (JSON_TYPE(_params->'$._status') <> 'NULL') THEN
    SET _status = _params->'$._status'; END IF;
  IF (JSON_TYPE(_params->'$._orderField') <> 'NULL') THEN
    SET _orderField = _params->>'$._orderField'; END IF;
  IF (JSON_TYPE(_params->'$._orderDirection') <> 'NULL') THEN
    SET _orderDirection = _params->'$._orderDirection'; END IF;
  IF (JSON_TYPE(_params->'$._offset') <> 'NULL') THEN
    SET _rowsOffset = _params->'$._offset'; END IF;
  IF (JSON_TYPE(_params->'$._limit') <> 'NULL') THEN
    SET _rowsLimit = _params->'$._limit'; END IF;

  SELECT SQL_CALC_FOUND_ROWS
         `a`.`id`, `a`.`time`, `a`.`title`, `a`.`description`, `a`.`image`, `a`.`alias`,
         `a`.`status`, `c`.`title` AS 'categoryTitle', `c`.`alias` AS 'categoryAlias',
         `u`.`title` AS 'userTitle', `u`.`alias` AS 'userAlias',
         GROUP_CONCAT(DISTINCT `t`.`title` ORDER BY `t`.`title` ASC SEPARATOR ', ') AS 'tags'
    FROM `article` AS `a`
    INNER JOIN `category` AS `c` ON `c`.`id` = `a`.`category`
    LEFT JOIN `article_tag` AS `at` ON `at`.`article` = `a`.`id`
    LEFT JOIN `tag` AS `t` ON `t`.`id` = `at`.`tag`
    INNER JOIN `user` AS `u` ON `u`.`id` = `a`.`user`
    WHERE `a`.`id` > 0
      AND (_dateBegin     IS NULL OR `a`.`time`     >= _dateBegin)
      AND (_dateEnd       IS NULL OR `a`.`time`     <= _dateEnd)
      AND (_title         IS NULL OR `a`.`title`    LIKE CONCAT('%', _title, '%'))
      AND (_categoryID    IS NULL OR `a`.`category` = _categoryID)
      AND (_categoryTitle IS NULL OR `c`.`title`    LIKE CONCAT('%', _categoryTitle, '%'))
      AND (_tagID         IS NULL OR `at`.`tag`     = _tagID)
      AND (_tagTitle      IS NULL OR `t`.`title`    LIKE CONCAT('%', _tagTitle, '%'))
      AND (_userID        IS NULL OR `a`.`user`     = _userID)
      AND (_userTitle     IS NULL OR `u`.`title`    LIKE CONCAT('%', _userTitle, '%'))
      AND (_status        IS NULL OR `a`.`status`   = _status)
    GROUP BY `a`.`id`
    ORDER BY
      (CASE WHEN _orderField = 'time'     AND _orderDirection = 1 THEN `a`.`time`     END) ASC,
      (CASE WHEN _orderField = 'time'     AND _orderDirection = 0 THEN `a`.`time`     END) DESC,
      (CASE WHEN _orderField = 'title'    AND _orderDirection = 1 THEN `a`.`title`    END) ASC,
      (CASE WHEN _orderField = 'title'    AND _orderDirection = 0 THEN `a`.`title`    END) DESC,
      (CASE WHEN _orderField = 'category' AND _orderDirection = 1 THEN `a`.`category` END) ASC,
      (CASE WHEN _orderField = 'category' AND _orderDirection = 0 THEN `a`.`category` END) DESC,
      (CASE WHEN _orderField = 'user'     AND _orderDirection = 1 THEN `a`.`user`     END) ASC,
      (CASE WHEN _orderField = 'user'     AND _orderDirection = 0 THEN `a`.`user`     END) DESC
    LIMIT _rowsOffset, _rowsLimit;
END$$

А от саме процедуру ArticleGetIndex описувати варто, бо в ній розкривається весь потенціал використання типу даних JSON для вхідного параметра. І нехай вас не лякає її розмір — це найбільша процедура в проекті через велику кількість змінних відбору записів. Решта подібних збережених процедур, які формують список записів таблиці згідно з певними умовами, суттєво менші.

Зміст цієї процедури можна умовно поділити на три частини: декларування внутрішніх змінних, перенесення даних з JSON у внутрішні змінні й саме формування запиту. Перші дві частини реалізують функціонал валідації вхідних даних, від якого, звичайно ж, можна відмовитися і вставляти змінні в тіло запиту безпосередньо з вхідного параметра _params. Але я вирішив підстрахуватися, і це підвищення надійності роботи процедури відразу збільшило її розмір майже вдвічі.

У третій частині відбувається найцікавіше: генерація SQL-запиту за допомогою вхідних даних, де варто звернути увагу на способи формування умов фільтрації записів і їхнього сортування надалі. Кожен параметр у конструкції WHERE перед застосуванням перевіряють на наявність, щоб не навантажувати запит зайвими умовами відбору. Відповідно, умову відбору в тілі запиту створюють тільки за наявності значення параметра, який передали з клієнта.

На превеликий жаль, з динамічним сортуванням записів за допомогою змінних мені поталанило набагато менше. В ідеалі, у конструкцію ORDER потрібно було б підставити дві клієнтські змінні: назва поля й напрямок сортування. Однак, хоч скільки я намагався, у мене нічого з цього не вийшло й довелося розв’язувати цю проблему в такий незграбний спосіб. Маю надію, що в наступних версіях MySQL розробники додадуть якийсь функціонал для можливості реалізації елегантнішого рішення.

DELIMITER $$
CREATE PROCEDURE `ArticleSet`(IN `_params` JSON)
BEGIN
    DECLARE _i TINYINT(3) DEFAULT 0;
    DECLARE _id TINYINT(3) UNSIGNED;
    DECLARE _time DATETIME;
    DECLARE _title VARCHAR(128);
    DECLARE _description VARCHAR(256);
    DECLARE _text TEXT;
    DECLARE _image VARCHAR(48);
    DECLARE _alias VARCHAR(128);
    DECLARE _category TINYINT(3) UNSIGNED;
    DECLARE _tag SMALLINT(5) UNSIGNED;
    DECLARE _tags JSON;
    DECLARE _tags2 JSON DEFAULT '[]';
    DECLARE _user TINYINT(3) UNSIGNED;

    IF (JSON_TYPE(_params->'$.id') <> 'NULL') THEN
        SET _id = _params->'$.id';
    END IF;
    SET _time = _params->>'$.time';
    SET _title = _params->>'$.title';
    IF (JSON_TYPE(_params->'$.description') <> 'NULL') THEN
        SET _description = _params->>'$.description';
    END IF;
    IF (JSON_TYPE(_params->'$.text') <> 'NULL') THEN
        SET _text = _params->>'$.text';
    END IF;
    IF (JSON_TYPE(_params->'$.image') <> 'NULL') THEN
        SET _image = _params->>'$.image';
    END IF;
    SET _alias = _params->>'$.alias';
    SET _category = _params->'$.category';
    SET _user = _params->'$.user';

    IF (_id IS NOT NULL) THEN
        UPDATE  `article`
        SET   `time` = _time, `title` = _title, `description` = _description,
              `text` = _text, `image` = _image, `alias` = _alias, `category` = _category
        WHERE `id` = _id AND `status` = 1;
        DELETE FROM `article_tag` WHERE `article` = _id;
    ELSE
        INSERT INTO `article`
            (`time`, `title`, `description`, `text`, `image`, `alias`, `category`, `user`)
        VALUES (_time, _title, _description, _text, _image, _alias, _category, _user);
        SELECT LAST_INSERT_ID() INTO _id;
    END IF;

    IF (JSON_TYPE(_params->'$.tags') <> 'NULL') THEN
        SET _tags = _params->'$.tags';
        WHILE _i < JSON_LENGTH(_tags) DO
            SELECT JSON_EXTRACT(_tags, CONCAT('$[',_i,']')) INTO _tag;
            IF (JSON_CONTAINS(_tags2, CAST(_tag AS CHAR)) = 0) THEN
                INSERT INTO `article_tag` (`article`, `tag`) VALUES (_id, _tag);
                SELECT JSON_ARRAY_APPEND(_tags2, '$', _tag) INTO _tags2;
            END IF;
            SELECT _i + 1 INTO _i;
        END WHILE;
    END IF;
END$$

Як ви вже, мабуть, зрозуміли з назви, основна мета процедури ArticleSet — збереження даних статті. У неї за допомогою JSON-параметру легко й зручно передають значення всіх полів запису таблиці. Загалом вона схожа на попередню і складається з тих же трьох частин: декларування змінних, перенесення даних і формування запиту. Лише зверну увагу на те, що я об’єднав створення (INSERT) й оновлення (UPDATE) запису таблиці в одну процедуру, в якій потрібну дію визначають наявністю ідентифікатора у вхідних параметрах. У кінці процедури ви можете подивитися приклад використання інших функцій для роботи з типом даних JSON, за допомогою яких я зреалізував потрібний додатковий функціонал.

DELIMITER $$
CREATE PROCEDURE `ArticleUnset`(IN `_id` INT UNSIGNED)
BEGIN
    DELETE FROM `article_tag` WHERE `article` = _id;
    DELETE FROM `article` WHERE `id` = _id;
END$$

Ну й нарешті остання та водночас найпростіша процедура ArticleUnset ― видалення запису таблиці за його ідентифікатором. У реальному проекті вона може бути трохи складніша через додавання всіляких перевірянь перед видаленням, або взагалі не видаляти запис, а змінювати його ознаку на «видалений».

Виклики з боку клієнта

Наостанок я наведу декілька прикладів взаємодії клієнта з БД за допомогою викликів збережених процедур.

CALL ArticleGet(123456789);

Ось такий простий вигляд з боку клієнта має виклик процедури отримання даних статті за його ідентифікатором.

CALL CategoryGetIndex('[]');
CALL ArticleGet(3);
CALL TagGetByIDs('14,15,18');

А такі три запити виконують під час відкриття сторінки редагування статті в адміністративному сайті. Окрім даних самої статті, ще потрібно отримати для форми редактора перелік категорій і вибраних міток.

CALL ArticleGetIndex('{"_status":"1", "_orderField":"time", "_orderDirection":"0", "_offset":0,"_limit":"15", "dateBegin":"2018-01-01", "dateEnd":"2019-09-24", "title":"львів", "categoryTitle":"пол", "tagTitle":"львів", "userTitle":"стус"}');

Такий вигляд має запит на отримання списку статей згідно з набором користувача даних для фільтрування.

CALL ArticleSet('{"title":"Заголовок ...", "description":"Опис ...", "text":"<p>Текст ...<\/p>", "image":"some_image.jpg", "category":"5", "tags":[14,15,18],"time":"2019-04-06T16:57", "alias":"заголовок...", "user":"1", "id":"3"}');

За допомогою подібних викликів ці статті в БД оновлюють. Якби поля id не було серед даних JSON, тоді б цей запит не оновлював наявний запис, а створював новий.

CALL ArticleUnset(3);

І нарешті — процедура видалення, в яку, так само як і в процедуру ArticleGet, передають лише один параметр.

Висновок

Як ви самі могли переконатися на реальному прикладі, взаємодія з БД через збережені процедури цілком можлива й працює доволі непогано. Звісно, це аж ніяк не означає, що такий підхід не має недоліків і згодиться на всі випадки життя. Я припускаю, що тут ще може бути підводне каміння, про яке ми зможемо дізнатися лише в процесі здобуття практичного досвіду.

Проте особисто мені такий вид взаємодії клієнта із сервером дуже сподобався і дав змогу ліпше оптимізувати й значно зменшити код. Якщо у вас є якийсь практичний досвід щодо питань, які я описав у статті, як позитивний так і негативний, — обов’язково напишіть про це в коментарях.

Все про українське ІТ в телеграмі — підписуйтеся на канал DOU

👍ПодобаєтьсяСподобалось1
До обраногоВ обраному0
LinkedIn

Схожі статті




88 коментарів

Підписатись на коментаріВідписатись від коментарів Коментарі можуть залишати тільки користувачі з підтвердженими акаунтами.
CREATE PROCEDURE `ArticleSet`(IN `_params` JSON)

Где транзакции ?

WHILE _i < JSON_LENGTH(_tags) DO

это не реляционно как-то

Где транзакции ?

у mysql DDL без транзакцій

код процедуры исходной — DML.

у mysql DDL без транзакцій

Жирный «-»

код процедуры исходной — DML.

ну, процитовано було визначення, а не тіло, тому я зрозумів, що претензія до DDL :)
зрештою, промахавшись з транзакціями, я свого часу прийшов до висновку, шо транзакції в mysql краще робити з клієнта:

start transaction;
-- all DML here 
call proc_name();
commit;
Жирный «-»

жиза
і тому свого часу, накатавши купу коду на mysql, я зарікся так робити з ним — доведеться ще раз писати логіку, буду робити це у postgres :)

Где транзакции ?

Транзакції додам, дякую.

это не реляционной как-то

Та ніби реляційно:

INSERT INTO `article_tag` (`article`, `tag`) VALUES (_id, _tag);

А що саме ви вбачаєте не реляційне?

Дякую за пораду, спробую переписати.

З гарного в цьому підході створення додатків, (який ще й дуже популярний в окремих індустріях), це те що ви з полиці отримуєте досить зручний API який можна використовувати з простої консолі, особливо коли ви маєте можливість групування команд з допомогою схем або Object як в статті та створювати алиаси та readline completions для не критичних часто вживанних команд.
Але той камамбер не є безкоштовним :)

Ще один недолік який дуже доконував у проектах з збереженими процедурами — вони не не зовсім структурні процедури.
Тобто, використовуючи ті процедури можливо сказати — ось тобі строка, дай мені день року відносно початку фіскального року компанії, а ось передати в процедуру рядок або кілька рядків, та підрахувати суму з урахуванням предикативних обмежень — вже не дають. Те саме з поверненням наборів строк, наприклад для генерації матричних данних. Воно іноді можливо... у деяких версіях... з використанням тимчасових таблиць... не більше одної за раз... і знову через .упу..
Я вже казав що мені подобається php?

Ее, а можна запитати, з якими саме СУБД у Вас були проблеми?)

З якими версіями яких саме СУБД.
Меньш за все нарікань було на postgres, MS SQL якщо не хачіти assembly досить продвинута до деяких меж, MySQL — краще без процедур :Є

Ну про MySQL я згоден, хоча ТС доводить зворотнє)
Про інші бази я не зовсім згоден — обидві мають досить розвинені процедурні рішення, і фактично можна вирішити будь-яку проблему. Звісно питання тільки в тому чи це має сенс робити саме в базі, чи вже в апплікейшені.

Ключове тут версії...
Останній раз процедури в MS SQL розробляв на версіях 2008/2012 обидві були жіві та без намірів апгрейду.
Posgresq — там також досить різкі зміни були, і є чудовий seized-source RDS

Основні недоліки роботи з даними через збережені процедури полягають у створенні додаткового «технічного» (не обумовленого бізнес вимогами) прошарку в доволі специфічному середовищі на специфічній мові програмування. В обмін на отримання mysql RPC API «з коробки», ви отримаєте наступні проблеми:
— версіонування. У випадку збережених процедур не достатньо зробити git pull + compile + deploy. Потрібно робити окремі скрипти на кожну процедуру для оновлення, надання прав і т.і. Дуже важко робити canary deploy, тому що на відміну від прикладних серверів, сервер бази один (міграція данних — процедура дуже коштовна), а функціоналу підтримки різних версій та міграцій «з коробки» нема. Тому у збережених процедур свій життєвий цикл, і дуже важко використовувати той самий цикл розробки та розгортання на основі системи контролю версій як з основним кодом.
— SQL injection over JSON :) Збережені процедури полюбляють DBA, яким база надає стандартні методи влади — дозволити/заборонити. Можливість обходу таких обмежень в коді процедури, в купі з не самими ефективними функціями санітізації, роблять можливість взлому даних більш імовірною.
— міграція на нову версію мови хранимих процедур дуже коштовна, тому що потребує оновлення серверу бази даних. Ніякого смузі, один COBOL SQL! І це не в окремому (мікро/web/SOA)-сервісі на кшталт «розрахунок сніданку таргана _ спитати Васю що на пенсії _ тел 467544654», а в базовому прошарку вашого додатку. «Всім джуніорам потрібно негайно вивчити мову програмування MySQL процедур версії 4.3.67»

SQL injection over JSON :) Збережені процедури полюбляють DBA, яким база надає стандартні методи влади — дозволити/заборонити. Можливість обходу таких обмежень в коді процедури, в купі з не самими ефективними функціями санітізації, роблять можливість взлому даних більш імовірною.

Збережені процедури в разі правильного використання мінімізують вірогідність SQL injection або унеможливлюють взагалі. Це ж очевидно, ні?

Ключове тут — в разі правильного і до поки ви уважно обробляєте строки які передаються через json

роблять можливість взлому даних більш імовірною.

Я сперечався з цим. Це не так, хоча б тому що в процедурі вже є завершений шаблон запиту, який буде виконуватись, і передаються тільки параметри, це означає що вже відпадають варіанти ін’єкцій типу «=something union arbitrary query» or «; another arbitrary query», тобто залишаються типу намагання засунути неіснуючі фільтри, але як?) Там вже є готовий набір фільтрів, то для того щоб засунути «or 1=1» треба знати послідовність параметрів і все одно результат не гарантований. Тобто використання збережених процедур саме по собі зменшує вірогідність SQL injection.

JSON injection + «Можливість обходу обмежень доступу».
Я не проти процедур, я проти хаків з параметрами та динамічними запросами в процедурах, що виникали у таких проектах.
DBA начеб-то зробив все правильно, але спочатку з`являється така собі процедурука с правами більш ніж необхідно (бо дуже треба), потім робиться необачна передача параметру в дінамічний запрос, потім json протягується з браузера і бум..

SQL inception возможен если запрос сформирован клиентом. В случае процедур, если, еще раз если, процедура не формирует динамического SQL в процессе выполнения, то она (процедура) может считаться сейв по отнощению к SQL Injection. Это правило простое, правда ?

Гарна стаття... було б круто щось таке прочитати, коли самому треба було написати пару процедур/трігерів. Пам’ятаю, ще тоді було проблемою знайти нормальну документацію по цих речах.

Все, наче, круто... якби не одне «але». Читаючи цю статтю, довелося декілька разів скривитися. І причин цьому декілька.

Я бачив, умовно, три «види» БД.
1. Малі бази. Це бази, які наврядчи доростуть до гігабайта. В таких базах можна робити, умовно, все, що заманеться. Якось, кінець-кінцем запрацює. Таких баз більшість і до них входять мільйони різних веб ресурсів. Тут можна розважатись як хочеться: і тригери і прцедури :)

2. Бази з великою кількістю логіки. Тут сотні таблиць, близько до нормалізованої форми, проставлені вторинні ключі, купка процедур, можливо і бібліотек процедур, а може і з тригерами. Це, буває, схоже не на БД, а на невеликий мікросервіс з своїм API у вигляді процедур. Такі академічно зроблені бази я бачив в банківських системах.

Все, наче, круто .... але така БД їсть багато ресурсів .... і, що біда, багато ресурсів одного сервера. Горизонтально масштабувати — майже неможливо (хто б що не говорив про реплікацію і подібні речі).

Вишньою на торті може бути процедура, як хтось тут згадував, формування певного звіту ... твір явно не для людських очей (так, мови програмування придумали саме для людей, а не для компів ... компам і бінарів вистачає)

3. Бази з великою кількістю даних. Зазвичай в таких базах все на стільки просто на скільки це взагалі можливо. Кожен крок продуманий так, щоб не дай боже щось не затормозило. Якщо десь і є процедура, то цей крок явно прорахований і ця процедура робить рівно те, що ефективно робить БД. Тут, часто використовують і NoSQL і якісь спеціалізовані речі.

В таких проектах, бази типу MySql використовуються лише із-за підтримки транзакцій між таблицями. Ну і для зберігання купи даних з «реляційною» структурою, але яких не багато по об’єму.

Для великих колекцій та ж mongoDb буде значно швидша на вставку та вибірку по ключу, наприклад. Та й кластеризація там з коробки яка не яка. Тому тут будуть всі колекції, які помірно ростуть.

Для логів/журналів/аудит-логів: kafka та/або elastic search.

----

Можливо, все згадане буде схиляти до думки, що я один з тих, для кого SQL-бази не в авторитеті. Але це не так.... оснона думка полягає в тому, що сховищ даних в сьогоднішніх проектах запросто може бути декілька. І працювати з логікою над даними в коді — це нормально. І не варто виносити це в, умовно, хранимі процедури, так як під навантаженням такий підхід створить проблеми.

Дякую за розгорнутий коментар, але я не розумію чому саме вам довелось кривитись?

Адже по прикладах в статті відразу видно, що я описую на практиці БД, яка ближче до першого виду, згідно з наведеною вами класифікацією. Другий та третій види не моя спеціалізація тому, як ви й написали, можу дозволити собі розважатись досхочу :)

До того ж в кінці статті я однозначно написав, що такий підхід може мати недоліки та не призначений на всі випадки життя.

я не розумію чому саме вам довелось кривитись?

Вважайте, що це суб’єктивне враження :)
Просто, згадувавлися не дуже приємні епізоди колупання з БД, що пов’язані з надмірною вірою в те, що оптимізація запитів в СУБД знає що робить і обирає дійсно найкращий спосіб виконати запит чи процедуру.

не дуже приємні епізоди колупання з БД

Там колупать должны DBE/DBA тогда и порядок будет

Ееее... то історія з іншого світу ... в нас тут стартапи і все таке ))

1. Це принципово нічого не міняє. Райський простір ACID операцій кінець-кінцем або тріщить по швам, або стає занадто ресурсоємним і дорогим.

1.1. (сарказм) Це призводить до дорогих БД на дорогих сертифікованих серверах з дорогими сертифікованими обов’язково рейдами в дорогих сертифікованих датацентрах, які обслуговують дорогі сертифіковані девопси/адміни/мережеві інженери, і якими управляють дорогі сертиіфковані DBA.

2. DBA на базі до одного гігабайта (якщо ви подивитесь вище по дискусії — там йшлося про такі випадки) — не занадто?

3. Те, що віддати розстановку індексів та написання купи процедур на сотні стрічок окремій людині, по суті мало що змінить. Задачі — ті ж. Інструменти — ті ж. Проблеми — ті ж.

4. Напевно, основне ... не завжди, але часто, великі проблеми з БД вирішуються не на рівні БД. Тому окремий DBA не допоможе, а навіть зашкодить, так як потрібна буде людина, яка знає «що там в БД» і знає «що там поза БД».

Проблема рынка, как я вижу, в том базами занимаются те кто не должен ими заниматься.
Приведенные пункты — производная. Под таким углом и прокомментирую
1. Пока приведенное правило работает — ничего не изменится, «виноваты сами»
1.1. Так и должно быть бай дизайн. Не сарказм
2. Большие базы/данные начинаются не с размера, а с дизайна
3. Да ну ?
4. Большие проблемы надо не решать а предотвращать. Если нет DBA см описание правила выше

Схоже, що ви пишите ці тези базуючись не лише на тезах університетського курсу по БД.
Тому, хотів би почути вашу думку з приводу наступного ....

Великі і потужні БД (той ж oracle) — це дійсно витвір мистецтва. І щоб там відверто не накосячити на великих схемах там дійсно вистачить роботи, щоб цим займалась окрема людина, інколи навіть команда. Це я до того, що я не заперечую те, що в таких випадках DBA є сенс сприймати як повноцінний напрямок роботи.

З того, що я знаю, серйозні БД круто працюють, коли працюють на одному сервері. Тому, з ростом, цей сервер розганяють дисками, процами, пам’яттю стільки, скільки це взагалі можливо (хоч це не пропорційно дорого). З якогось моменту, а може й самого початку додають кілька реплік ... для всяких бекапів, аналітики та read-only запитів чи навіть fail-over (ще один дорогущий сервер). До цього моменту «все нормально». А далі починаються проблеми, проблеми з multi-master рішеннями, проблеми з шардуванням на рівні БД, тощо. Це все складно та працює не так швидко, як хотілося б. Перехід з «одного сервера» на якийсь варіант з кластером, наврядчи можна нормально зробити без змін в підходах як до архітектури самої БД так і до логіки роботи з нею самих додатків.

З іншої сторони, можливим варіантом є відмова від централізації БД. Коли вимоги до масштабування звалюються не на плечі БД, а закладаються в архітектуру самого додатку. В цьому випадку, не виключено, що кожна «нода» такого додатку сама по собі буде мати «свою» БД. Не виключено, що якісь дані (що рідко міняються) і будуть лежати в multi-master кластері. Не виключено, що для певних операцій (наприклад, лог транзакцій) і будуть використовуватися специфічні рішення (наприклад, kafka). Можливо, що навіть в фінансових сферах класичний ACID не такий вже і важливий (подивіться як працюють еквайрингові системи ... там операції по банківських картках точно не ACID ... той самий eventual consistency може досягатися навіть через кілька днів).

Я не кажу, що другий шлях дешевший за перший. І я не кажу, що другий шлях потрібен всім. Але коли припече, перейти від першого до другого рівноцінно «переписати все заново». А це дорого ... дорого не переписувати і дорого переписувати.

Знову ж, суб’єктивно, я більше схильний використовувати прості open source бази, ставити до них прості вимоги, і якщо навіть колись якась фігня станеться і треба буде мігрувати, ... це не буде проблемою, так як «не сильно то й ми від бази багато хотіли, те що нам треба вміють всі». А якщо такі рішення не тримають навантаження, значить треба думати як зробити навантаження меншим, чи винести його в іншу форму, чи обробляти іншим чином, чи розподілити його якимось способом...

Тому, хотів би почути вашу думку з приводу наступного ....

Говорить не мешки ворочать, отчегож не нет ?

Великі і потужні БД (той ж oracle) — це дійсно витвір мистецтва. І щоб там відверто не накосячити на великих схемах там дійсно вистачить роботи, щоб цим займалась окрема людина, інколи навіть команда. Це я до того, що я не заперечую те, що в таких випадках DBA є сенс сприймати як повноцінний напрямок роботи.

Мой университетский опыт говорит что чаще работает команда со стороны Data.

З того, що я знаю, серйозні БД круто працюють, коли працюють на одному сервері. Тому, з ростом, цей сервер розганяють дисками, процами, пам’яттю стільки, скільки це взагалі можливо (хоч це не пропорційно дорого). З якогось моменту, а може й самого початку додають кілька реплік ... для всяких бекапів, аналітики та read-only запитів чи навіть fail-over (ще один дорогущий сервер). До цього моменту «все нормально». А далі починаються проблеми, проблеми з multi-master рішеннями, проблеми з шардуванням на рівні БД, тощо. Це все складно та працює не так швидко, як хотілося б. Перехід з «одного сервера» на якийсь варіант з кластером, наврядчи можна нормально зробити без змін в підходах як до архітектури самої БД так і до логіки роботи з нею самих додатків.

«Разгонять» можно, потолок есть но он постоянно растет. Только на вскидку из последнего AMD Epic — up 64cores/socket, Optane, Persisted RAM, SSD 16tb/unit, 100GB ethernet, etc. А в 2002 довольствовались серваком 2*PII-333. С учетом cloud миграция с сервера на сервер не вызывает головной боли, совсем. Если сингл сервера недостаточно — нагрузку распределяешь по RO репликам, OLAP/DWH выносить отдельно. Классика. Если данных много и они cold, перейти к DataLake.
Страдать шардингом и реализацией — трудоемко.

З іншої сторони, можливим варіантом є відмова від централізації БД. Коли вимоги до масштабування звалюються не на плечі БД, а закладаються в архітектуру самого додатку. В цьому випадку, не виключено, що кожна «нода» такого додатку сама по собі буде мати «свою» БД. Не виключено, що якісь дані (що рідко міняються) і будуть лежати в multi-master кластері. Не виключено, що для певних операцій (наприклад, лог транзакцій) і будуть використовуватися специфічні рішення (наприклад, kafka). Можливо, що навіть в фінансових сферах класичний ACID не такий вже і важливий (подивіться як працюють еквайрингові системи ... там операції по банківських картках точно не ACID ... той самий eventual consistency може досягатися навіть через кілька днів).

Можливо. Кто как видит архитектура так и имплементит

Знову ж, суб’єктивно, я більше схильний використовувати прості open source бази, ставити до них прості вимоги, і якщо навіть колись якась фігня станеться і треба буде мігрувати, ... це не буде проблемою, так як «не сильно то й ми від бази багато хотіли, те що нам треба вміють всі». А якщо такі рішення не тримають навантаження, значить треба думати як зробити навантаження меншим, чи винести його в іншу форму, чи обробляти іншим чином, чи розподілити його якимось способом...

Звучит нормально — простые задачи -простые речения. Зачем вся вышеприведенная дискуссия если все у вас просто и опен сорс покрывает запросы ?

Поліз в Гугл — побачив те, на що і сподівався — для PHP вже є не тільки фреймворки, але й ORM-и.
Може, варто розважатись в даному напрямку (ІМНО, користі буде більше)?

оснона думка полягає в тому, що сховищ даних в сьогоднішніх проектах запросто може бути декілька. І працювати з логікою над даними в коді — це нормально.

А яка практична різниця між викликом процедури чи запитом з клієнта при декількох сховищах?Адже дані з БД вони повертають однаково. До того ж таке буває не часто, на відміну від декількох клієнтів з однією БД, де збережені процедури матимуть перевагу.

З свого досвіду, різниця є ... і їх декілька:

1. поняття ціліснісності БД часто виходить за межі самої БД і все-рівно треба про це думати на рівні додатку, незалежно від того є там процедури чи немає.

2. Як не як, а код на «нормальній мові» виглядає краще, ніж на «процедурній». (Якщо є нормальні інструменти для роботи з БД)

3. Якщо потрібен таки query builder, то не факт, що в процедурах вийде лаконічніше (наприклад, у випадку типу «а чи треба join-ити отой підзапит»)

4. В додатку, при потребі, можна організувати кешування різного роду, об’єднання insert-ів, додаткову синхронізацію, тощо

на відміну від декількох клієнтів з однією БД, де збережені процедури матимуть перевагу.

Це саме той випадок, коли я з вами погоджуся. А саме, коли ці клієнти писані різними командами, в різний час та на різних мовах. Це саме той випадок, коли БД перетворюється в такий собі «мікросервіс», який і доступ контролює і правила роботи з даними задає. Питання чи повинна цим займатися БД є відкритим та реторичним.

Прикольна стаття — дяки, що не побоявся влізти в це :)

Тим не менше, деякі речі впали в око:
1 — чому не використати view для вибірки даних?
міняти — процедурами/функціями, вибирати — через view

2 — у тексті була згадка про @змінні: ми колись провели бенчмарки — прості локальні змінні, оголошені через declare, у порівнянні зі @змінними працювали раза в 2 швидше

3 — деякі процедури можна зробити функціями з «not deterministic» — може це чуть і крамольно, але дозволяє робити декілька змін одним запитом

4 — по тексту є купа одноманітних дій виду

IF (JSON_TYPE(_params->’$.text’) <> ’NULL’) THEN
SET _text = _params->>’$.text’;
END IF;

чи не можна їх завернути у функцію — все ж менше писанини буде?

5 — чи проводились бенчмарки по швидкодії JSON проти простих параметрів?
JSON, по ідеї, буде повільнішим, питання тільки — на скільки

Я вліз в це, бо поняття не маю куди влажу, а потім відгрібаю у хвіст та в гриву :)
В мене так часто буває, мабуть, це невіддільна складова розвитку.

1, 3, 4 — я спочатку зробив експеримент щоб перевірити чи взагалі такий підхід буде працювати, наскільки зручно, виявити недоліки й т.д. А вже після перевірки розвивати його та вдосконалювати. В любому випадку дуже вдячний за поради.

2 — дякую за інформацію, для цього і пишу статті, щоб обмінюватись досвідом.

5 — бенчмарки не робив, бо на моїх проєктах ця різниця не критична.
Звичайно додатковий час іде на конвертацію JSON, але, в теорії, може економитись час внаслідок відсутності необхідності розбору рядка з SQL-запитом від клієнта.

але, в теорії, може економитись час внаслідок відсутності необхідності розбору рядка з SQL-запитом від клієнта.

Ні, для того щоб не було розбору, потрібно щоб запит був статичним, для всіх запитів, текст яких формується динамічно, розбір буде кожного разу і не матиме значення, звідки прийшов текст.

Когда-то, когда деревья были большими, а я был джуном, который считал себя миддлом, получил хороший урок. Банковская сфера, есть хранимая процедура, возвращающая выписку клиента. Дергается ОЧЕНЬ часто, поэтому требования к скорости отработки были основными. На вход передавалось что-то около 100500 отдельных параметров (часть из которых опциональна). И нужно было добавить 100501. Решил вопрос кардинальным способом: была создана overloaded процедура, принимающая на вход XML. Парсинг XML, естественно, средствами БД (в данном случае Oracle 10). Все тесты прошли на ура, рефакторинг не привел к изменению бизнес-логики. Двигаем на ЛАЙВ. И минут через 10 звонят ДБА, и не особо цензурными словами объясняют, что 99% времени процессоров БД занято парсингом этих XML. И вообще БД стало очень нехорошо. Урок был усвоен, и мой дальнейший опыт работы показал, что парсинг xml/json — это не самая оптимальная операция для БД. Хоть она и может это делать...

Какое же решение по передаче кучи параметров на вход процедуры?

Для Oracle — это массивы ключей-значений либо отдельный пользовательская коллекция типа с необходимыми аттрибутами (но это менее гибко, т.к. не позволяет добавить новый аттрибут «на лету», без инвалидации типа и зависимых объектов, хотя и там есть возможности, но нужно заморачиваться с обратной совместимостью).

Сейчас в основном используем 2 массива: массив ключей и значений, которые представляют из себя пользовательскую коллекцию table of varchar2. Клиент (PHP/JAVA) накачивает эти коллекции со своей стороны, процедура БД матчит массивы по порядковому номеру ключа. Плюсы подхода: при добавлении нового ключа-значения спецификация процедуры не изменяется, поэтому избегаем инвалидации зависимостей при выкладке в рабочее время; служебные процедуры по работе с такими массивами ключей-значений пишутся 1 раз и потом тиражируются в коде. Минусы подхода: подходит только для передачи одномерных массивов. Если на вход нужно получить коллекцию, то каждый из входных параметров должен быть коллекцией. Хотя для этих целей я предпочитаю использовать пользовательский тип.

Дякую за приклад, але мені здається передача в процедуру 100500 параметрів не зовсім типовий випадок.

Як ви вважаєте, чи будуть перевантаження процесора при перетворенні 20-30 вхідних параметрів з json/xml формату?

Относительно СУБД Oracle могу с уверенностью говорить — однозначно процессор будет загружен больше. Т.к. ни JSON, ни XML не поддерживается БД нативно. Пакеты и операторы для работы с ними появились в более поздних версиях: JSON — в 12, XML — в 10 (ЕМНИП). Для JSON лучшим вариантом (до 12 версии) — писать самописный парсер и строго ограничивать спецификацию JSON.

Относительно MySQL нужно проверять на практике. Тем более что документация утверждает, что все будет ОК. Но MySQL и заточен больше на WEB-использование, поэтому такой подход имеет право на жизнь и стоит попробовать...

питання слушне, але mysql клянеться, що працювати буде нормально
dev.mysql.com/...​c/refman/5.7/en/json.html

Optimized storage format. JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later must read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document.

Ох ты ж, видел много вариантов использования процедурок, но создавать процедурки для сервинга сущности, мдэ, такое себе удовольствие. Требования BA каждый день меняются и при каждом чихе апдейтить процедурку, ну его нафиг. Еще и юзать json в процедурках, бррр. Легче в коде подправить ифчик в query builder.

P.S. Как говорится, мсье знает толк в извращениях

Все це дуже добре — на маленьких базах з маленькими датасетами, з маленькими вимогами до продуктивності. Аби застерегти від майбутніх факапів, викладу чисто тезово:

1. Не буває БД, які здатні обробити за певний гарантований час будь-який запит. Іншими словами, завжди можливо написати запит, який залочить все, що тільки можна, на безконечний час, переповнить /tmp, вижре всі доступні іопси і покладе базу, а якщо пощастить — то й хост.

2. Для прийнятної продуктивності БД з ростом проекту, доводиться пізнавати таїнство фізичної структури — індекси, партиції, кластери, шарди етц. Біда-бідося в тому, що навіть у фундаментальній праці К.Дж.Дейта цьому питанню з 1200 сторінок присвячено рівно один абзац. В голій реляційній теорії всі операції виконуються за О(0). В житті ж, зазвичай, все не так, як воно буває насправді. Як воно буває насправді можна дізнатися лише фейсом об тейбл без належного індекса.

3. Для гарантованої продуктивності проекту в цілому, рано чи пізно доводиться обмежувати перелік дозволених запитів в БД, а все недозволене — забороняти. Як казали в радянській арміі — «Пусть бєзобразно, зато єдінообразно».

4. З часом навіть найпростіший круд загортається у виклики процедур, фронту залишається тільки грант на ехесute, а на все інше — болт. Особливо — на динамічно генеровані запити.

5. Ну, і згодом з’ясовується, що всі ігрища з JSON та інші порушення 1NF, є наслідками кривого дизайну моделі даних, після чого настає логічний момент для рефакторингу. Як правило, в цей момент ліди та сеньйори тікають з проекту на +500.

Але, повторюю, все це чекає на вас лише у випадку, коли проект переростає зародковий стан.

Як вже писали, бізнес-логіка на SP — це більше геморою, ніж вигоди.
Ну і 1NF — брр, це занадто. Тут на ентерпрайз-системі достатньо порушень 3NF, як результат куча коду з validate / revalidate / recalculate. Десь виграли на кількості таблиць в базі, складності джойнів — але значно ускладнили собі життя в іншому місці.
P.S.
Дейт в даному випадку, це не зовсім про фізичні структури.
Є «Database systems.The Complete Book» Hector Garcia-Molina , Jeffrey D. Ullman , Jennifer Widom
Яка, втім, також більше 1000 сторінок.

Як вже писали, бізнес-логіка на SP — це більше геморою, ніж вигоди.

Есть один нюанс, не в пользу противников сервер сайд логики.
Базы переживают не один фреймворк/поколение клиентов.
Написанное раз работает десятками лет.

На этот пример была история от Девида Веста: «когда вы писали программы с реляционной логикой в 70, вы обрекали бизнес на статичность, поскольку создание и изменение таких программ было очень дорогим. Схема данных не менялась 3,5-10 лет.»
Знаете сколько стоят шикарные водонепроницаемые смарт часы с gps, симкой и картой памяти для ребенка и сколько заработала Alibaba group в этом незавершеном году?

С удовольствием послушаю!
?

Звичайно переживають. Робити зміни в структурі бази/мігрувати на інший сервер складніше, ніж робити зміни в софті.
І варіанти типу SP_do_something_V1, ..., SP_do_something_V10 також бачив. Добре, коли до цих V1 .. V10 є хоч якісь коментарі в коді.

Хоспаді, я думав така рахітектура вмерла вже 100500 років тому...

СУБД они ж не развиваются, правда ?

Я про формування запитів на клієнті.

а что насчет безопасности? разве любой, кому не лень не сможет сгенерировать свой json запрос, как происходит валидация данных? Ведь, дело не только в получении данных (а можно получить и чисто персоналные данные), но и в добавлении новых...?

Виклик процедур з клієнта виконуються так само як і виконання запитів з клієнта, відповідно і можливості доступу в них абсолютно однакові. Валідація відбувається в середині процедури при перенесенні даних з json у внутрішні змінні, про що я вказав в статті на прикладах.

а не надо вытаскивать запросы на клиента. запросы должны бекендом выполняься.
Что касается хранимыъ процедур то это програмирование в стиле 70х
неудобно в отладке нечитабельно а в случае mysql еще и неэфективно.

Не варто вказувати іншим що і як вони повинні робити — нехай кожен це вирішує самостійно. Збережені процедури це інструмент, який має як сильні так і слабкі сторони. Розробники додали його в СКБД разом з тригерами, переглядами та багато чого іншим щоб полегшити роботу з БД. А от доцільність використання того чи іншого інструменту залежить від конкретної ситуації. Мені дуже шкода що ви у 21-му столітті так цього і не зрозуміли.

Я конечно понимаю что писать на PHP качественный бекенд это очень сложная задача, и бекенд написанный на SQL(кое когда мне довелось поколупать приложение с фронтендом на делфи и беком на MSSQL) будет даже выглядеть понятнее и качественнее чем это, но всё же есть альтернативы получше.

JSON — здавалось би, що може бути простіше? Хіба що ... можливо, YAML

Резануло глаз слегка. В реале YAML can therefore be viewed as a natural superset of JSON... every JSON file is also a valid YAML file

Саме тому я використав слово «можливо» ;-)

2019 рік. Свіжа інфа про сторед процедури 8-)

А яким саме чином ви передавали багато даних в процедури до впровадження типу даних JSON?

Строка. Например, задача — сума прописью. Одна строка массив индексов, вторая — массив данных. И таких пар из строк несколько. И весь алгоритм и данные в теле процедуры. Смещение по формулам, чтоб взять индекс и длину. Хотя там просто оперирование строками и преобразование типов. Строка все стерпит. JSON — разметка, а не тип. Хотя, тип/вид разметки текста.

Жахіття, аж сльози навертаються :( Після 2015 ви почали використовувати JSON? Якщо так, то напишіть яку ви відчули різницю.

Равно и точки с запятой с головой хватает, по аналогии с передачей параметров как в адресной строке.

Логичнее было бы вести речь о сериализации/десериализации свойств объекта. Когда из объекта получаем форматированую текстовую информацию, а на стороне базы данных с помощью системных или своих функции вытягиваем наборы переменных из одного передаваемого параметра — строки. Виды форматов — это уже дело вкуса. Это удобно, когда набор данных, а точнее заполняемость значениями всего возможного набора параметров, не постоянно.

При чому тут JSON? Сторед процедури самі по собі зло і пекло.
Не було потреби передавати.

Якби ви прочитали статтю перед тим як коментувати ви б зрозуміли при чому тут JSON.

уж куда меньшее зло чем триггеры (а на моей предыдущей работе их было миллион :-( )

Коли у вас бізнес-логіка в middle tier, вам не потрібно «передавати багато даних в процедури»

Хехе, а як щодо «передачі багато даних в middle tier»?) Ну тобто якщо бізнес-логіка там, то перш ніж щось обробляти, треба ці дані зареквестити, ні?)

І? Ми про передачу параметрів говоримо, а ви про що? Про обсяг обміну даними між бекендом і базою?

Коли у вас бізнес-логіка в middle tier,

А коли у вас бізнес-логіка в базі, можливо що і взагалі не потрібно передавати даних в процедури.
До того ж, передача параметрів в процедури — який тут оверхед взагалі? Якщо хтось пакує мегабайти даних в XML і ганяє потім по сітці щоб отримати від процедури єдиний респонс або взагалі без респонсу, то хто доктур такій парадігмі? З іншого боку, коли бізнес-логіка в middle tier, то питання, а наскільки та логіка зав’язана на дані з бази? Якщо дуже зав’язана, то чи не логічніш було б перемістити частину логіки, яка трансформує дані, ближче до даних, тобто в базу? Бо бувають і інші варіанти «дизайну», коли наприклад, вимоги для сервера бази даних — ХХГ, а для сервера middleware — 2ХХГ, тобто якщо база стоїть на сервері з 64Г, то апп сервер — 128Г.

В ідеалі, у конструкцію ORDER потрібно було б підставити дві клієнтські змінні: назва поля й напрямок сортування.

Это можно решить собиранием запроса в строку и выполнением:

set @Query = CONCAT('select * from ... order by ...', '...');
prepare stmt from @Query;
execute stmt;
DEALLOCATE PREPARE stmt;

такое себе решение, но в своё время другого не нашёл.

Главная проблема хранимых продцедур в mysql — их нельзя атомарно заменить, нужно дропать и пересоздавать. Что при хоть какой-то конкурентности равносильно стрелять себе в ногу, гарантированно получая ошибки в коде «PROCEDURE does not exist» в момент деплоя.

Главная проблема хранимых продцедур в mysql, нужно дропать и пересоздавать, что при хоть какой-то конкурентности равносильно стрелять себе в ногу

Мне кажется там целый ворох проблем помимо этого одна другой краше
1) Они не версионируются и отслеживать изменения можно только через костыли с дополнительным таблицами в которых хранить код процедуры. Плюс надо тянуть за собой шлейф создания базы. Т.е. помимо миграций должен быть базовый файл в котором все процедуры описаны, чтоб видеть кто и когда, а главное зачем их менял
2) Дебажа код нельзя понять, что происходит внутри процедуры. Как клиент ты просто получаешь результат, но почему он к примеру не такой как ожидаешь это только методом проб и ошибок.
3) Так как SQL заметно беднее в инструментарии чем язык программирования на котором обращаются к базе то реализовывая сложные процедуры очень быстро можно потерять суть происходящего, а через пол года это вообще темный лес. Дебаг сложный и подчас проще выкинуть и обработать данные уже после выполнения запросов, а не пытаться понять почему процедура не фурычит как раньше или почему сбоит в конкретном случае.

В общем я б десять раз подумал бы прежде чем применять на проекте хранимые процедуры которые отвечают за бизнес логику.

Он же через сравнение объектов схемы с репозетарием позволяет организовать ведение версионности.

Конкретно с этой тулзой не работал, а остальные были глючным говном. Название которых уже и не вспомню. Другой вопрос стоит ли решать проблемы которые вообще говоря можно и не решать.

А еще оно может ошибку не выдавать, а выдавать warnings, которые никто естественно даже не видит.

и в чем проблема если все хранимки создаются из yaml-а, а ямлы находятся в системе сорс контроля и эволюционируют вместе с версией основного продукта?

Тем, что если есть хранимка, например, saveUserPayment() и вызывается она 100+ раз в секунду. То при обновлении кода этой хранимки, часть запросов упадёт с ошибкой, так как попадёт в интервал между drop и create процедуры. Для production кода это как бы не очень гуд :) Не умеет mysql атомарно это сделать.

Можно выкрутиться, не удалять старую, а создавать новую с суффиксом версии — saveUserPayment_v2, например, с соответствующими изменениями в коде приложения. Но это выглядит скорее как костыль.

обновление делается во время деплоймента только, когда приложение полностью гасится

Гасить на время деплоя — жесть какая, но уговаривать делать по-другому не буду.

Конечно гасить это ж веблоджик

А, тогда да, но бывают и приложения где этого делать нельзя по бизнес-требованиям

это не проблема, есть решения на разные случаи , например динамическая подмена инстансов через лоад балансер

да, с учётом разных способов проводить деплой без downtime, вот и вызвало удивление тушить приложение.

Древние легаси технологии 😢

тогда печаль-беда, да и клиент уже наверное свыкся

Цікаво, а якщо по аналогії оновлюються скрипти сайту на активному production-сервері та в цей момент робляться запити сторінок, які використовують ці файли, вони ж теж мають впасти з помилкою?

Если обновлять файлы сразу в рабочей директории, то хорошего ничего не будет.

Но так же не делают, если без докеров и т.д., файлы нового релиза заливаются в отдельную директорию и меняется симлинк на неё (наиболее распространённый подход для скриптовых языков).

Для цього є canary та blue-green deployment.
Ви створюєте копію скриптів і окремий віртуальний сервер та потроху перемикаєте трафік на нього. Коли всі запити мігрували ви видаляєте стару копію, або використовуваєте її як кешований пул для наступної міграції

А резон?
Изначальная проблема была — генерировать SQL код из PHP сложно. Как решение предложено использовать промежуточный стек API. Итерация номер два — давайте генерить хранимки из какого-то DSL описания.
Итого вместо php c объекными плюшками и простым но кастомизированным SQL, имеем более сложную систему с yaml, json, парсерами и код-генераторами на psql, php, nodejs, etc. и курей ограничений и зависимостей

Підписатись на коментарі