Миграция с 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('<a><b>Brown Seal</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 (N'Property'), ROOT (N'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 (то есть до 1Mb)
• Во-вторых, текстовые поля могут содержать симоволы &<>”’ которые могут сделать ваш XML невалидным. Их нужно экспейтить. Например, так:
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tagvalue,'&','&amp;'),'<','&lt;'),'>','&gt;'),'"','&quot;'),''','&apos;');
  Но, конечно же, лучше написать функцию вроде

  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.

Автор: andreylartsev

Источник