Ситуация:
Я хочу создать функцию 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 node имя для более позднего беспокойства. Как только функция будет стоять, я подумаю о некотором алгоритме, который примет имя поля и превратит его в некоторое юридическое имя XML node.
Кроме того, избежание данных XML для более позднего беспокойства. Это будет выполняться с помощью другой функции с именем CDATAify, которая просто переносит все данные в <![CDATA[ и ]]> и будет избегать любого предыдущего события ]]> в данных в ]]]]><![CDATA[>.
Я не смог выполнить это с помощью хранимых функций в MySQL, потому что они не принимают в результатах. Кроме того, даже если вы должны были передать SQL в виде строки, а затем подготовить оператор и выполнить его, вы не сможете получить доступ к полям, если вы еще не знаете имена полей.
Итак, теперь мне интересно, можно ли сделать трюк с пользовательскими функциями (UDF). Это то, с чем я еще не работал, и я бы хотел, чтобы вы посоветовали здесь, прежде чем открывать.
ВОПРОСЫ:
Итак, мои вопросы:
- Чтобы повторить, я хотел бы иметь функцию MySQL, которую я могу передать выражению или набору результатов, и где я также могу использовать имена полей набора результатов.
- Я полагаю правильным, что это будет невозможно в сохраненных функциях?
- Будет ли UDF брать в откуп/их результат в качестве аргумента?
- Будет ли UDF разрешать мне доступ к именам полей результирующего набора, поэтому я могу использовать их в качестве имен тегов XML
- Будет ли он работать и с Windows? Я читал, что UDF имеет некоторые ограничения.
- Есть ли лучший способ, о котором я еще не думал?
- Могу ли я иметь UDF.dll, который я могу создать на своем собственном компьютере разработки, а затем скопировать DLL файл на свой сервер и использовать его там?
- Как мне получить это шоу в рулоне? Пожалуйста, будьте внимательны и учтите, что у меня есть MySQL 5.5 64-бит на компьютере под управлением 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 или С# сначала запрашивали пользовательскую таблицу, а затем каждый пользователь запускал два дополнительных запроса для классов и игрушек. Потому что для 1000 пользователей я буду запускать запросы 2001 года.
- Я также не хочу запускать запрос со всеми объединениями и проходить через набор результатов в PHP или С#, потому что имя пользователя будет отправлено столько раз, сколько количество раз превышает количество игрушек. Представьте себе, что пользовательское поле содержит огромный кадр!
- Я не могу просто использовать GROUP_CONCAT для объединенных таблиц, так как классы/игрушки все равно будут выглядеть двойными.
- И если бы я использовал GROUP_CONCAT с DISTINCT, я бы потерял оценки одинаковыми, например, Bart two 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'
))
))
;
EDIT:
Как упоминалось в комментариях N.B., существует хранилище на Github, возможно, все, что мне нужно. Тем не менее, я потратил несколько дней, просто пытаясь заставить это работать в моей системе без успеха. Любой ответ, который содержит пошаговое руководство по установке этого на моем 64-разрядном сервере MySQL 5.5, работающем в Windows, также является приемлемым.
Пожалуйста, учтите, что у меня нет опыта работы с make, make файлами и т.д. Поэтому, пожалуйста, тщательно объясните.
