[Из песочницы] Миграция с SQL Server на MariaDB / MySQL. Что делать с XML
Коллеги, хочу предложить вам описание решений которые помогли в проекте по миграции большого приложения с SQL Server на MariaDB / MySQL.Возможно, это кому то поможет в аналогичном проекте.
Чего мы НЕ делалиФункциональность MariaDb / MySQL, скажем так… меньше, чем у SQL Server, но в этом проекте мне не пришлось сталкиваться с такими аспектами, как: миграция аналитики построенной поверх SSAS; или 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… INTO…) и даже не обязательно в конце процедуры.
Ни и разумеется, помогли написанные ранее интеграционные тесты. Если у вас их нет, есть повод задуматься о написании их по ходу проекта. Чем больше будет покрытие тестами функциональности 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 ('Brown Seal', '/a/b/text ()') Другой вопрос, как быть, если тебе передали XML документ, в котором находится некоторая коллекция значений и размера ты ее не знаешь? Как определить, сколько элементов в коллекции? И как вынуть определенный элемент коллекции? Для подсчета элементов в XPath есть функция count (), которая умеет возвращать количество XML элементов соответствующих определенному XPath выражению. (http://www.w3schools.com/xpath/xpath_functions.asp)
SELECT ExtractValue ('BrownSeal', 'count (/a/b)') Определив количество элементов коллекции, мы можем написать XPath для получения конкретного элемента SELECT ExtractValue ('BrownSeal', '/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 (N’Property'), ROOT (N’Properties')
(https://msdn.microsoft.com/ru-ru/library/ms178107.aspx)В результате запроса получается один 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 ('
Если эта статья кому-то покажется полезной, можно будет продолжить описание других проблем и решени, связанных с миграцией на MariaDb с SQL Server.
