MYSQL UDF функция для возврата XML
Ситуация:
Я хочу создать функцию mysql с именем XMLify, которая принимает строку и выражение, которое будет возвращать набор
XMLify(string, expr)
Функция должна обернуть каждое возвращенное поле каждой возвращаемой строки в наборе в свой собственный тег XML. Имя тега должно быть именем поля.
Небольшой пример:
select XMLify('foo', (SELECT 1 as `a`, 2 as `b` UNION SELECT 3 as `a`, 4 as `b`));
должен вернуть:
<foo><a>1</a><b>2</b></foo><foo><a>3</a><b>4</b></foo>
Я хочу иметь это, потому что это позволит мне выполнить сложный запрос со многими объединениями и / или зависимыми подзапросами, без необходимости возвращать избыточные данные клиенту.
У меня уже есть обходной путь без функции, которую я хочу построить. Но это включает в себя написание сложных запросов, которые нелегко поддерживать. Смотрите мои примеры ниже.
Убедиться в том, что имена полей являются допустимыми, имя узла XML вызывает беспокойство позже. Когда функция остановится, я подумаю о некотором алгоритме, который возьмет имя поля и превратит его в какое-то допустимое имя узла XML.
Кроме того, экранирование данных XML вызывает беспокойство. Это будет сделано с другой функцией с именем CDATAify, что просто обернет все данные в <![CDATA[ а также ]]>, и избежит любого предшествующего вхождения ]]> в данных в ]]]]><![CDATA[>,
Я не смог сделать это с помощью хранимых функций в MySQL, потому что они не принимают наборов результатов. Кроме того, даже если вы передадите SQL как строку, а затем подготовите оператор и выполните его, вы не сможете получить доступ к полям, если вы еще не знаете имен полей.
Так что теперь мне интересно, можно ли сделать трюк с помощью пользовательских функций (UDF). Это то, с чем я еще не работал, и я бы хотел, чтобы вы посоветовали здесь, прежде чем приступить к работе.
ВОПРОСЫ:
Итак, мои вопросы сейчас:
- Напомним, что я хотел бы иметь функцию MySQL, в которой я мог бы передавать выражение или набор результатов, и где я также мог бы использовать имена полей набора результатов.
- Правильно ли я полагаю, что это не будет возможно в хранимых функциях?
- Будет ли UDF принимать выражения / их результаты в качестве аргумента?
- Позволит ли UDF мне обращаться к именам полей результирующего набора, чтобы я мог использовать их в качестве имен тегов XML
- Будет ли это работать и на Windows? Я читал, что у UDF есть некоторые ограничения
- Есть ли лучший способ, о котором я еще не думал?
- Смогу ли я иметь UDF .dll, которую я могу создать на своем собственном компьютере для разработки, а затем скопировать файл.dll на свой сервер и использовать его там?
- Как я могу получить это шоу в рулоне? Пожалуйста, будьте внимательны и учтите, что у меня установлена 64-битная версия MySQL 5.5 на компьютере с Windows.
ПРИМЕР:
Представьте себе следующие 3 таблицы:
users: grades: toys:
+----+------+ +--------+-------+ +--------+--------------+
| id | name | | userid | grade | | userid | toy |
+----+------+ +--------+-------+ +--------+--------------+
| 1 | Bart | | 1 | E | | 1 | slingshot |
| 2 | Lisa | | 1 | E | | 1 | Krusty |
| .. | ... | | 2 | A | | 2 | Malibu Stacy |
| .. | ... | | 2 | B | | 2 | calculator |
+----+------+ +--------+-------+ +--------+--------------+
Мой желаемый результат будет ограничен Бартом и Лизой:
<users>
<user>
<id><![CDATA[1]]></id>
<name><![CDATA[Bart]]></name>
<grades>
<grade><![CDATA[E]]></grade>
<grade><![CDATA[E]]></grade>
</grades>
<toys>
<toy><![CDATA[slingshot]]></toy>
<toy><![CDATA[Krusty]]></toy>
</toys>
</user>
<user>
<id><![CDATA[1]]></id>
<name><![CDATA[Lisa]]></name>
<grades>
<grade><![CDATA[A]]></grade>
<grade><![CDATA[B]]></grade>
</grades>
<toys>
<toy><![CDATA[Malibu Stacey]]></toy>
<toy><![CDATA[calculator]]></toy>
</toys>
</user>
</users>
Рассмотрение:
- Я не хочу, чтобы в PHP или C# приходилось сначала запрашивать пользовательскую таблицу, а затем для каждого пользователя запускать два дополнительных запроса для оценок и игрушек. Потому что для 1000 пользователей я буду выполнять 2001 запросов.
- Я также не хочу запускать запрос со всеми объединениями и просматривать набор результатов в PHP или C#, потому что имя пользователя будет отправлено столько раз, сколько оценок умножено на количество игрушек. Представьте, что у вас есть пользовательское поле, содержащее огромный блоб!
- Я не могу просто использовать GROUP_CONCAT на соединенных столах, так как оценки / игрушки все равно будут отображаться дважды.
- И если бы я использовал GROUP_CONCAT с DISTINCT, я потерял бы оценки с одинаковыми, такими как две E Барта.
Поэтому в настоящее время я бы использовал следующий оператор, чтобы получить этот результат, включающий два зависимых подзапроса. Это прекрасно работает:
SELECT
CONCAT(
'<users>',
IFNULL(
GROUP_CONCAT(
'<user>',
'<id><![CDATA[',
REPLACE(u.id,']]>',']]]]><![CDATA[>'),
']]></id>',
'<name><![CDATA[',
REPLACE(u.name,']]>',']]]]><![CDATA[>'),
']]></name>',
'<grades>',
(
SELECT
IFNULL(
GROUP_CONCAT(
'<grade><![CDATA[',
REPLACE(g.grade,']]>',']]]]><![CDATA[>'),
']]></grade>'
SEPARATOR ''
),
'')
FROM
grades g
WHERE
g.userid = u.id
),
'</grades>',
'<toys>',
(
SELECT
IFNULL(
GROUP_CONCAT(
'<toys><![CDATA[',
REPLACE(t.toy,']]>',']]]]><![CDATA[>'),
']]></toys>'
SEPARATOR ''
),
'')
FROM
toys t
WHERE
t.userid = u.id
),
'</toys>',
'</user>'
SEPARATOR ''
),
''
),
'</users>'
)
FROM
users u
WHERE
u.name = 'Bart' or u.name = 'Lisa'
;
Теперь, как вы могли заметить, это довольно большой и уродливый запрос, который ранит глаза при чтении. Поддерживать такой запрос сложно. Если бы у меня были функции XMLify и CDATAify, я мог бы просто написать вместо этого:
SELECT
XMLify('users',(
XMLify('user',(
SELECT
CDATAify(u.id) as id,
CDATAify(u.name) as name,
XMLify('grade',(
SELECT
CDATAify(g.grade) as grade
FROM
grades g
where
g.userid = u.id
)) AS grades,
XMLify('toys',(
SELECT
CDATAify(t.toy) as toy
FROM
toys t
where
t.userid = u.id
)) AS grades
FROM
users u
WHERE
u.name = 'Bart' or u.name = 'Lisa'
))
))
;
РЕДАКТИРОВАТЬ:
Как упоминалось в комментариях NB, на Github есть репозиторий, возможно, содержащий все, что мне нужно. Однако я провел несколько дней, пытаясь заставить это работать в моей системе, но безуспешно. Любой ответ, содержащий пошаговые инструкции о том, как установить его на моем 64-битном сервере MySQL 5.5, работающем под Windows, также приемлем.
Пожалуйста, примите во внимание, что у меня нет опыта работы с make-файлами, make-файлами и т. Д.
1 ответ
Х, только сегодня я нашел этот вопрос, я просто надеюсь ответить на этот вопрос не (слишком) поздно, и если будет слишком поздно, может быть, это поможет кому-то еще.
Причина. MySql не позволяет реализовывать динамические запросы к функциям или триггерам. Я просто выбираю реализацию хранимой процедуры.
DELIMITER //
DROP PROCEDURE IF EXISTS XMLify//
CREATE PROCEDURE XMLify(IN wraper VARCHAR(100), IN expr VARCHAR(1000))
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA SQL SECURITY INVOKER
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE col_name VARCHAR(255);
DECLARE cur1 CURSOR FOR
SELECT
column_name
FROM
information_schema.columns
WHERE
table_schema = 'test' AND /*Name of the database (schema)*/
table_name = 'temp' AND
column_name <> 'c4l5mn';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DROP TABLE IF EXISTS temp;
SET @SQL = CONCAT('CREATE TABLE temp (c4l5mn TINYINT NOT NULL DEFAULT ''1'') AS ', expr);
PREPARE stmt1 FROM @SQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
OPEN cur1;
SET col_name = '';
SET @SQL = '';
read_loop: LOOP
FETCH cur1 INTO col_name;
IF done THEN
LEAVE read_loop;
END IF;
SET @SQL = CONCAT(@SQL, '<', col_name, '>'', ', col_name, ', ''</', col_name, '>');
END LOOP;
CLOSE cur1;
SET @SQl = CONCAT('SELECT GROUP_CONCAT(CONCAT(''<', wraper, '>', @SQL, '</', wraper, '>'') SEPARATOR '''') row FROM temp GROUP BY c4l5mn');
PREPARE stmt1 FROM @SQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
DROP TABLE IF EXISTS temp;
END//
DELIMITER ;
вот и все, теперь вы можете назвать это так же, как
CALL XMLify('foo', 'SELECT 1 as `a`, 2 as `b` UNION SELECT 3, 4');
И это вернется
<foo><a>1</a><b>2</b></foo><foo><a>3</a><b>4</b></foo>
Вызов
CALL XMLify('foo', 'SELECT 1 as a, 2 as b, 3 as c UNION SELECT 4, 5, 6');
Вернусь
<foo><a>1</a><b>2</b><c>3</c></foo><foo><a>4</a><b>5</b><c>6</c></foo>
Я просто надеюсь, что это поможет Привет