Генерація 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 UNSIGNED INTEGER DEFAULT 0;
 DECLARE _rowsLimit UNSIGNED INTEGER 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, передають лише один параметр.

Висновок

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

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

Опубліковано: 04/10/19 @ 10:00
Розділ Хостинг

SEO Блог