Генерація 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_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, передають лише один параметр.

Висновок

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

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

Похожие статьи:

"Леша, за тобой пришли, убегай". Белорусский программист – о жизни на родине и в Киеве

Алексей работает фулстек-разработчиком в аутсорсинговой компании, офис которой находится в одном из районных центров Беларуси. Как...

«Связной»: бренд Lenovo впервые стал лидером по продажам планшетов в России

Компания «Связной» сообщила, что в октябре 2015 года лидером в сегменте планшетных компьютеров по доле рынка в рублях стал бренд...

ВКонтакте» внедряет в работу двухступенчатую систему авторизации пользоват

В российской социальной сети ВКонтакте участились случаи взлома страниц, особенно у администраторов больших сообществ с целью...

DOU Проектор: «Каратєль» — достукатися до влади через мобільний додаток

У рубриці DOU Проектор всі охочі можуть презентувати свій продукт (як стартап, так і ламповий pet-проект). Якщо вам є про...

У 2023 році найм у найприбутковіші IT-компанії зріс на 24%. Аналітика BRDO

Організація BRDO, на основі даних Державної служби статистики, опублікувала звіт про український IT-ринок за 2023 рік. DOU...