Міграція з SQL Server на MariaDB / MySQL. Що робити з XML

Колеги, хочу запропонувати вам опис рішень, які допомогли у проекті з міграції великої програми з SQL Server на MariaDB / MySQL.

Можливо, це комусь допоможе в аналогічному проекті.

Чого ми НЕ робили

Функціональність MariaDb / MySQL, скажімо так… менше, ніж у SQL Server, але в цьому проекті мені не довелося стикатися з такими аспектами, як:
  • міграція аналітики побудованої поверх AS;
  • ETL процесів з використанням SSIS;
  • Full text search вже був зроблений на Apache SOLR (як і треба робити).

Що ми робили

  • Була схема на 150 таблиць і ~ 20 в'ю;
  • > 900 процедури;
  • recursive CTE (Recursive Common Table Expressions);
  • робота з XML;
  • table valued parameters;
  • dynamic SQL;
  • аналітичні функції (наприклад — ROW_NUMBER() OVER ()), яких немає в MariaDb, але іноді все-таки можна щось зробити...;
  • і зрозуміло UNIQIDENTIFIER як кластерного індексу на значній частині таблиць (і чому цей тип даних так люблять деякі розробники?


Довелося вирішувати проблеми продуктивності та шукати причини deadlock-ів, з чим допоміг Percona Toolkit. А так само модифікувати DAC (Data Access Layer), щоб програма працювала і з SQL Server і MariaDb.

Основні проблеми

Найбільшою проблемою була конвертація процедур просто тому, що їх було багато. Можливість перейти на NHybernate або інший ORM ніхто всерйоз не розглядав, в процедурах було повно логіки і переносити її в бізнес шар ніхто не збирався (ви вже здогадалися, що додаток було .NET).

Що було добре

Принципова здійсненність проекту спиралося на те, що синтаксис T-SQL і синтаксис процедур MariaDb / MySQL схожі і була теоретична можливість автоматизації для конвертації процедур. Принаймні для тривіальних CRUD випадків.

Особливо мені сподобалося те, що з процедур в MariaDb / MySQL можна повертати кілька result sets так само як і в T-SQL. Мене завжди мучало, чому розробники Oracle або PostgreSQL не передбачили такої простої можливості. Тобто просте вираження SELECT * FROM users у кінці процедури призводить до того, що його результат можна прочитати в DAC на клієнтській стороні. Більш того, це може бути будь-яка кількість виразів SELECT не перенаправлених в таблицю (INSERT INTO… SELECT) або в змінну (SELECT… В ...) і навіть не обов'язково в кінці процедури.

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

До речі, MySQL і MariaDb можна ставити на Windows, але краще починати розробку відразу на Linux. На Linux краще інструментальне оточення (на зразок того ж Percona Toolkit) і ви зіткнетеся з проблемою правильного коллейшена для ідентифікаторів точніше для імен таблиць. Наприклад, на Linux запит SELECT * FROM users і SELECT * FROM Users зовсім не одне і теж. Тому що дані таблиць лежать у файлах, а на Unix / Linux файлова система case sensetive. І ще, як не дивно, MariaDb всередині вируалки з Linux працює швидше, ніж на Window хості як «рідний» Windows сервіс. Мабуть, у MySQL немає нічого спільного з цією прекрасною десктопної операційної системою.

Почнемо з самого простого — що робити з XML?

У додатку XML використовувався наступним чином:
  • Передавався в якості параметрів процедур і повертався в result sets. Рішення просте використовуємо тип даних LONGTEXT замість XML, c Oracle, MySQL NET Connector все працює відмінно.
  • Видобувалися з XML параметрів колекції даних. Такий був у коді підхід і використовувався він нарівні з coma separated lists і table valued parameters (https://msdn.microsoft.com/en-us/library/bb510489.aspx) де, історія продукту була довга...
  • Використовувалися конструкції виду SELECT… FOR XML
  • Змінювався XML в полях таблиць прямо в процедурі. Конструкції виду-UPDATE table1 SET field1.modify(...)
Перша проблема вирішується тривіально і варто згадки тільки для того, щоб в аудиторії не виник розрив шаблону, якщо немає типу даних XML, то звідки він взагалі в процедуру потрапить. Інші варті того, щоб зупинитися на них докладніше.

Колекції даних в XML параметрах

Взагалі, в MariaDb / MySQL є функція ExtractValue, яка вміє виконувати XPath для XML-документа передається в параметри, як BLOB і повертає результат як текст.
SELECT ExtractValue('<a><b>Seal Brown</b></a>', '/a/b/text()')
Інше питання, як бути, якщо тобі передали XML документ, в якому знаходиться деяка колекція значень і розміру ти її не знаєш? Як визначити, скільки елементів в колекції? І як вийняти певний елемент колекції?

Для підрахунку елементів в XPath є функція count(), яка вміє повертати кількість XML елементів відповідних певного виразу XPath. (http://www.w3schools.com/xpath/xpath_functions.asp)
SELECT ExtractValue('<a><b>Brown</b><b>Seal</b></a>', 'count(/a/b)')
Визначивши кількість елементів колекції, ми можемо написати XPath для отримання конкретного елемента
SELECT ExtractValue('<a><b>Brown</b><b>Seal</b></a>', '/a/b[1]/text()')
і цикл для отримання всіх елементів колекції.

CREATE PROCEDURE `sproc1` ( p1 LONGTEXT )
BEGIN

SET vCount = ExtractValue(p1, 'count(/ids/id)');
WHILE vCount > 0 DO
INSERT INTO __temptable1__ (att1)
VALUES (ExtractValue(p1, CONCAT('/ids/id[', CAST(vCount AS CHAR), ']/@att1')));
SET vCount = vCount — 1;
END WHILE;


Проти очікувань працює дуже швидко, так як XML документ не парсается в кожному виклику ExtractValue. У прикладі наведено лише один атрибут XML елемента id, але, зрозуміло, цих атрибутів може бути скільки завгодно.
Щоб не писати цикл WHILE раз за разом в кожній процедурі для кожного параметра з XML, можна написати процедуру(и), яка(і) будуть робити тимчасові таблиці і заповнення їх даними з XML. Ми так і зробили.

До негативних сторін такого рішення варто віднести накладні витрати на серіалізацію/десеріалізацію колекції даних в XML. Можна підготувати дані по тимчасової таблиці до виклику процедури та процедури їх просто використовувати. Але тоді, дивлячись в код процедури, буде неочевидно, звідки взялася та чи інша тимчасова таблиця. Погіршиться читабельність.

SELECT… FOR XML

Чим замінити такий синтаксис в T-SQL?

SELECT [PropertyName] AS [Name], [PropertyValue] AS [Value]
FROM [dbo].props1 AS [Property]
WHERE…
FOR XML PATH ('Property'), ROOT ('Properties')

(https://msdn.microsoft.com/ru-ru/library/ms178107.aspx)
В результаті запиту виходить один XML документ містить текст виду:
<Properties><Property Name=«abc» Value=«def» />… </Properties>
Тобто це агрегація result set-а і перетворення його в один структурований документ.
Чи можна повторити на MariaDb / MySQL?
Можна, і все, що потрібно — це функція агрегат GROUP_CONCAT. (https://mariadb.com/kb/en/mariadb/group_concat/)
Вона дозволяє конкатенувати рядки result set-а. Як то так:
SELECT GROUP_CONCAT(student_name) FROM student.
У результаті виходить одна рядок вигляду “Вася Петя Коля ...“
Але ж ми можемо конкатенувати не просто значення, але і обчислювані для кожного рядка вираження виду: CONCAT('<user name="', u.User,'" host="', u.Host,'" />')
Наприклад:

SELECT CONCAT('<users>',
GROUP_CONCAT(
CONCAT('<user name="', u.User,'" host="', u.Host,'" />')
SEPARATOR "), '</users>') FROM user u

Тобто, що ми отримали в результаті? Правильно — XML документ.
Але як і скрізь, безумовно, є пара нюансів:
  • По-перше, GROUP_CONCAT() за замовчуванням робить рядки довжиною не більше 1024 символів, але це регулюється параметром: group_concat_max_len. mariadb.com/kb/en/mariadb/server-system-variables/#group_concat_max_len. Цілком безпечне його збільшити до 1024 * 1024 (тобто до 1 mb)
  • По-друге, текстові поля можуть містити симоволы &<>"' які можуть зробити ваш XML невалидным. Їх потрібно экспейтить. Наприклад, так:
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tagvalue,'&','&'),'<','<'),'>','>'),'"','"'),'\",''');
    Але, звичайно ж, краще написати функцію зразок
    CREATE FUNCTION `xml_escape`(tagsvalue VARCHAR(2000))
    RETURNS varchar(2000). І скрізь її використовувати де потрібно.


Модифікація XML документів в БД

Мова про модифікації XML-документів, які зберігаються в полях таблиці в БД.

Така необхідність виникає, як правило, якщо ви десь помилилися в дизайні моделі даних і додали в XML якісь не self-containing дані і тоді періодично починає виникати потреба пройтися по збереженим XML документів і виробити в них зміни.

Але раз вже помилка здійснена, то питання, як правило, впирається в те, що буде коштувати все переробити (щоб було правильно) і що буде коштувати таки періодично обходити XML-ки і модифікувати їх за певними правилами.
У SQL Server можна зробити це так:

UPDATE user_profile up
SET Fields.modify('replace value of (/fields/field[key=sql:variable("Name")]/text())[1] with sql:variable("Value")')
WHERE Fields.value('data((/fields/field[key=sql:variable("Name")]/text())[1])', 'nvarchar(256)') = @OldValue

І як не дивно, хоч і не швидко, але все ж це можна зробити і в MariaDb. Для цього є функція UpdateXml.
Крім того, нам потрібно функція ExtractValue для пошуку потрібних рядків таблиці. Для того, щоб це працювало за прийнятний час, потрібно спочатку їх знайти і помістити в тимчасову таблицю:

INSERT INTO __ProfilesToUpdate__ (id)
SELECT id FROM user_profile up
WHERE ExtractValue(up.`Fields`, vValueXPath) = vOldValue;

Де SET vValueXPath = CONCAT('/fields/field[key="',XML_ESCAPE(vName),'"][1]/text()');

І потім вносимо оновлення:

UPDATE user_profile up
SET up.`Fields` = UpdateXML(up.`Fields`, vReplacementXPath, vReplacementXml)
WHERE id IN (SELECT id FROM __ProfilesToUpdate__);

Де SET vReplacementXPath = CONCAT('/fields/field[key="',XML_ESCAPE(vFieldName),'"][1]');
і SET vReplacementXml = CONCAT('<field key="',XML_ESCAPE(vFieldName),'">',XML_ESCAPE(pValue),'</field>');

Загалом, це все про XML MariaDB.

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

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.