Оновлення сайту власними SQL-запитами через Elbuz Tunnel

Коли потрібні власні запити

Тунель оновлює сайт сам: обираєте CMS, натискаєте «Підключити» — і каталог їде в базу сайту. Але буває два випадки, коли готового сценарію не вистачає.

Вашої CMS немає в списку. У виборі рушіїв вісім пунктів, і останній — «MySQL (SQL custom query)». Це режим для саморобного сайту або рідкісної системи: тунель доставить дані в базу вашого сайту, а куди їх покласти далі — опишете ви самі запитами. Обов’язкова умова одна: сайт працює на MySQL і ви маєте доступ до його бази.

CMS у списку є, але оновлення треба доповнити. Типовий сценарій закриває каталог, ціни, залишки й фото. А от переписати ЧПУ наявних товарів, підставити свою формулу ціни, заповнити нестандартне поле, яке ваш програміст колись додав у таблицю сайту, — цього в сценарії немає. Такі доповнення теж пишуть запитами, у тому самому вікні.

Як це влаштовано: тимчасові таблиці

Ключ до всього — розуміти, що тунель не пише у ваші таблиці навпростець. Прогін іде у два такти.

Такт перший. Elbuz створює в базі вашого сайту власні тимчасові таблиці з іменами виду etrade_product_temp, etrade_category_temp, etrade_manufacturer_temp і заливає в них те, що ви відзначили до вивантаження. Це повна копія даних Elbuz у зручному для SQL вигляді: один рядок — один товар, категорія, атрибут.

Такт другий. Дані з тимчасових таблиць переносяться у бойові таблиці сайту — oc_product, b_iblock_element, як вони у вас називаються. Саме цей такт і робить драйвер CMS. У режимі «MySQL (SQL custom query)» такого драйвера немає, і другий такт пишете ви.

Звідси й головний принцип роботи: ваші запити з’єднують тимчасову таблицю з бойовою. Майже кожен запит у цій статті має вигляд «взяти etrade_*_temp, знайти відповідний рядок у таблиці сайту, оновити його».

Вікно «Налаштування SQL запитів»

Відкривається з картки тунельного шаблону: секція «Додатково», кнопка «Налаштування SQL запитів» з іконкою бази даних. У шаблоні вивантаження всередині три вкладки.

У шаблоні завантаження (коли ви навпаки забираєте дані із сайту) вкладка одна — «SQL запити для отримання даних із сайту». Там пишуть SELECT-и, якими тунель дістає з вашої бази категорії, товари й решту.

1

Вкладки «Після оновлення» / «Перед оновленням»

2

Розділювач «;;;» в кінці запиту

1. Вкладки перемикають, коли саме виконується SQL-запит — після або перед оновленням даних.
2. Розділювач «;;;» позначає кінець одного запиту перед наступним у списку.

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

Перед кожною вашою пачкою програма сама виконує службові налаштування сеансу: знімає SQL_MODE, дозволяє великі вибірки, піднімає group_concat_max_len, дає тимчасовим таблицям до 2 ГБ пам’яті й розтягує innodb_lock_wait_timeout до 300 секунд. Дописувати це у своє поле не треба.

Куди у прогоні вбудовуються ваші запити

Порядок кроків видно в журналі прогону, у Центрі операцій. Для сайту на підтримуваній CMS він такий:

1. дані заливаються у тимчасові таблиці etrade_*_temp;
2. «Виконуємо SQL запити перед оновленням» — ваша вкладка «Перед оновленням»;
3. «Оновлення сайту» — драйвер CMS переносить дані у бойові таблиці;
4. фотографії викачуються з хмари на хостинг;
5. «Виконуємо SQL запити після оновлення» — ваша вкладка «Після оновлення».

У режимі «MySQL (SQL custom query)» кроку 3 немає взагалі. Прогін виглядає так: залили тимчасові таблиці → виконали «перед оновленням» → викачали фото → виконали «після оновлення». Уся робота з бойовими таблицями сайту — у ваших двох полях.

Сайт, якого немає в списку: підготовка

Один раз, до першого прогону, сайту треба дати колонку uuid — у ній зберігатиметься ідентифікатор запису з Elbuz. Приклад для OpenCart; замініть oc_ на префікс своїх таблиць і додайте ті таблиці, які вам потрібні.

ALTER TABLE oc_category        ADD uuid VARCHAR(36) NOT NULL, ADD INDEX (uuid);;;
ALTER TABLE oc_product         ADD uuid VARCHAR(36) NOT NULL, ADD INDEX (uuid);;;
ALTER TABLE oc_manufacturer    ADD uuid VARCHAR(36) NOT NULL, ADD INDEX (uuid);;;
ALTER TABLE oc_attribute       ADD uuid VARCHAR(36) NOT NULL, ADD INDEX (uuid);;;
ALTER TABLE oc_attribute_group ADD uuid VARCHAR(36) NOT NULL, ADD INDEX (uuid);;;

Далі — заповнити нову колонку для того, що на сайті вже лежить. Якщо каталог на сайті з’явився раніше за Elbuz, найпростіше взяти за uuid власний числовий ідентифікатор сайту:

UPDATE oc_category     SET uuid = category_id     WHERE uuid = '';;;
UPDATE oc_product      SET uuid = product_id      WHERE uuid = '';;;
UPDATE oc_manufacturer SET uuid = manufacturer_id WHERE uuid = '';;;
UPDATE oc_attribute    SET uuid = attribute_id    WHERE uuid = '';;;

Ці два набори виконують разово — хоч у phpMyAdmin, хоч через вкладку «Перед оновленням» на першому прогоні. Індекс по uuid обов’язковий: без нього кожне з’єднання тимчасової таблиці з бойовою перетворюється на повний перебір, і прогін на десятках тисяч товарів стає нестерпно довгим.

Крок 1. Опізнати те, що вже є на сайті

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

row_exist = 1 означає «такий запис на сайті вже є», row_exist = 0 — «його треба створити». Запити зручно тримати у вкладці «Перед оновленням».

Спершу категорії — знаходимо запис на сайті за uuid, потім батьківську категорію за uuid_parent, і аж тоді ставимо прапорець:

UPDATE etrade_category_temp t, oc_category c
   SET t.category_id = c.category_id WHERE t.uuid = c.uuid;;;
UPDATE etrade_category_temp t, oc_category c
   SET t.parent_id = c.category_id WHERE t.uuid_parent = c.uuid;;;
UPDATE etrade_category_temp SET row_exist = 1 WHERE category_id > 0;;;

Далі товари. Крім власного uuid, їм проставляють категорію й виробника — вони знадобляться при вставці:

UPDATE etrade_product_temp t, oc_product p
   SET t.product_id = p.product_id WHERE t.uuid = p.uuid;;;
UPDATE etrade_product_temp t, oc_category c
   SET t.category_id = c.category_id WHERE t.category_uuid = c.uuid;;;
UPDATE etrade_product_temp t, oc_manufacturer m
   SET t.manufacturer_id = m.manufacturer_id WHERE t.manufacturer_uuid = m.uuid;;;
UPDATE etrade_product_temp SET row_exist = 1 WHERE product_id > 0;;;

І виробники — так само за uuid:

UPDATE etrade_manufacturer_temp t, oc_manufacturer m
   SET t.manufacturer_id = m.manufacturer_id WHERE t.uuid = m.uuid;;;
UPDATE etrade_manufacturer_temp SET row_exist = 1 WHERE manufacturer_id > 0;;;

Той самий підхід — для зв’язок і атрибутів: etrade_product_to_category_temp опізнають за product_uuid і category_uuid, etrade_attribute_temp — за uuid і block_uuid, etrade_product_attribute_temp — за product_uuid і attribute_uuid (власний ідентифікатор рядка там називається row_uuid, а не uuid).

Крок 2. Оновити наявні записи

Усе, що позначене row_exist = 1, оновлюють звичайним UPDATE із з’єднанням по щойно проставленому числовому id. Ці запити вже кладуть у вкладку «Після оновлення».

UPDATE oc_product p, etrade_product_temp t
SET p.date_modified = NOW(),
    p.manufacturer_id = t.manufacturer_id,
    p.model = t.model,
    p.sku = t.mpn,
    p.price = t.price,
    p.quantity = t.quantity,
    p.status = t.status,
    p.weight = t.weight
WHERE t.row_exist = 1 AND t.product_id = p.product_id AND t.type_id = 1;;;

UPDATE oc_product_description d, etrade_product_temp t
SET d.name = t.name, d.description = t.description_full
WHERE t.row_exist = 1 AND t.product_id = d.product_id
  AND t.type_id = 1 AND d.language_id = 1;;;

Умова type_id = 1 тут не випадкова: у etrade_product_temp поряд зі звичайними товарами лежать товари-опції з type_id = 2. Якщо їх не відсікти, кожен варіант поїде на сайт окремою карткою.

Крок 3. Створити нові записи

Те, що лишилося з row_exist = 0, на сайті треба завести. Порядок важливий: спершу створюємо запис, потім одразу читаємо назад його новий числовий id у тимчасову таблицю — інакше наступні запити не знатимуть, до чого прив’язувати опис, фото й зв’язки.

INSERT INTO oc_category (date_added, sort_order, status, uuid)
SELECT NOW(), sort_order, status, uuid
FROM etrade_category_temp WHERE row_exist = 0 GROUP BY uuid;;;

UPDATE etrade_category_temp t, oc_category c
   SET t.category_id = c.category_id
 WHERE t.row_exist = 0 AND t.uuid = c.uuid;;;

INSERT INTO oc_category_description (category_id, description, language_id, meta_description, meta_keyword, name)
SELECT category_id, description_short, 1, meta_description, meta_keyword, name
FROM etrade_category_temp WHERE row_exist = 0;;;

Далі відновлюють ієрархію категорій — батька шукають за uuid_parent, — і тим самим способом заводять виробників і товари. Для фотографій у etrade_image_temp проставляють item_id — за item_uuid і row_type, який каже, чиє це фото: product, category, manufacturer, attribute, attribute_block або article. І вже потім записують імʼя файла в таблицю сайту.

Довідник тимчасових таблиць

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

Колонки, які є майже скрізь і потрібні в кожному запиті:

Крім типових полів, у чотирьох таблицях — товари, категорії, виробники й контрагенти — з’являються ваші власні поля базового каталогу, ті, що ви завели самі. Їхні імена складаються з типу запису й номера поля: product_f451, category_f120, manufacturer_f88. Підглянути потрібне ім’я можна в налаштуваннях сітки базового каталогу. У решті таблиць, зокрема в атрибутах і фотографіях, власних полів немає.

Доповнити оновлення типової CMS

Коли CMS у списку є, опізнання й вставку робить драйвер, а ваші запити лише додають те, чого в сценарії немає. Ось випадки, по які звертаються найчастіше.

Переписати ЧПУ наявних товарів. Для OpenCart адреси прописуються тільки новим записам; щоб оновити наявні, потрібен окремий запит. Для OpenCart 3:

UPDATE oc_seo_url su
INNER JOIN etrade_product_temp e
        ON e.product_id = CAST(SUBSTRING(su.query FROM 12) AS UNSIGNED)
SET su.keyword = e.seo_url
WHERE e.row_exist = 1 AND e.seo_url != '' AND su.query LIKE 'product_id=%';;;

Для категорій у тому самому запиті міняють таблицю-джерело на etrade_category_temp, умову — на category_id=%, а зсув у SUBSTRING — на 13. Для OpenCart 2 замість oc_seo_url використовують oc_url_alias.

Підставити своє значення замість штатного. Наприклад записати в поле «Модель» на сайті внутрішній артикул Elbuz, а в штрихкод — gtin:

UPDATE oc_product p, etrade_product_temp t
SET p.model = t.sku, p.sort_order = t.sort_order, p.upc = t.gtin
WHERE p.product_id = t.product_id;;;

Дотягнути те, що сценарій не переносить. Повний опис і опис-інструкцію в нестандартні колонки, мінімальну кількість для замовлення, округлення ціни вгору:

UPDATE oc_product SET price = CEIL(price);;;
UPDATE etrade_product_temp t, oc_product p
   SET p.minimum = t.order_minimum WHERE t.uuid = p.uuid;;;
UPDATE oc_product SET stock_status_id = 5 WHERE quantity = 0;;;

1C-Bitrix: заповнити властивість товару. Властивості в Бітриксі зберігаються окремими рядками, тому спершу знаходять id властивості за її кодом, потім дописують значення тим елементам, у яких його ще немає, і тільки після цього оновлюють решту. Зв’язка з Elbuz тут іде через b_iblock_element.xml_id, куди тунель кладе uuid.

Чого цей режим не робить

• Не читає конфігурацію сайту. Для «MySQL (SQL custom query)» кнопка автоматичного підключення нічого не дізнається: адресу сервера, базу, користувача, пароль і префікс вписують руками.
• Не створює колонку uuid. Для підтримуваних CMS це робить драйвер, для своєї — ви, запитом ALTER TABLE.
• Не знімає архівну копію бази сайту. Прапорець є, дії немає.
• Не перевіряє ваші запити. Ні синтаксису, ні складу: заборонених команд для цього поля немає, DROP TABLE збережеться так само спокійно, як UPDATE. Помилка спливе вже під час прогону, у журналі, а виконані до неї запити залишаться виконаними.
• Не синхронізує ідентифікатори сам. Для CMS зі списку є окремий крок «Синхронізуємо ID позицій з БД сайту», який зіставляє записи за uuid. У режимі «MySQL (SQL custom query)» цього кроку немає — його замінюють ваші запити опізнання.
• Не має попереднього перегляду. Кнопка «Перевірити», яка у фідів показує фрагмент для одного товару, у тунелю не працює.

Що робити, коли щось пішло не так

Часті запитання

Чому запити розділяються трьома крапками з комою, а не однією?

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

Де мені взяти список полів тимчасової таблиці?

Найпростіше — подивитися саму таблицю в базі сайту одразу після прогону командою SHOW COLUMNS FROM etrade_product_temp. Склад полів залежить від того, що відзначено у вивантаженні, і в нього входять ваші власні поля базового каталогу з іменами виду product_f451.

Чи видаляються тимчасові таблиці після прогону?

Залежить від режиму. Для CMS зі списку останнім кроком прогону йде «Видаляємо тимчасові таблиці та файли», і таблиці зникають. А ось у режимі «MySQL (SQL custom query)» вони навмисно залишаються в базі сайту: ваші запити можуть виконуватися й після прогону, та й розбирати помилки зручніше, коли видно, що саме приїхало з Elbuz. Наступний прогін кожну таблицю все одно перестворює заново, тому даних із минулого разу там не накопичується.

Мої запити виконуються в базі Elbuz чи в базі сайту?

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

Чи можна відкотити помилковий запит?

Ні. Запити виконуються у бойовій базі без транзакції, і скасування в інтерфейсі немає. Єдиний шлях назад — архівна копія бази сайту. Для підтримуваних CMS її знімає сам тунель, для режиму «MySQL (SQL custom query)» копію треба робити засобами хостингу самостійно.

Навіщо потрібні поля product_id_jumper і category_id_jumper?

У них лежать числові ідентифікатори записів у самому Elbuz. Вони потрібні, коли на сайті хочеться зберегти посилання назад у програму — наприклад записати ідентифікатор Elbuz у поле «Модель», щоб потім зіставляти замовлення. Для звичайного оновлення каталогу вони не потрібні: зіставлення тримається на uuid.

Я поставив CMS зі списку. Чи можу я все одно дописати свої запити?

Так, і це найчастіший спосіб застосування вікна. Драйвер зробить свою частину, а ваші запити з вкладки «Після оновлення» виконаються наприкінці — коли дані на сайті вже оновлені. Переписувати весь сценарій для цього не потрібно.

Суміжні теми

• Свій сайт через Elbuz Tunnel — як влаштоване звичайне вивантаження: підключення, вибір даних, архівна копія, розклад.
• Імпорт каталогу через Elbuz Tunnel — зворотний бік тунелю: забрати каталог і замовлення із сайту.
• Вивантаження товарів на маркетплейс, сайт і у файл — загальна механіка шаблонів вивантаження.
• Вивантаження та маркетплейси — розділ керівництва, до якого належить ця стаття.