новости
статьи
.программирование

преобразование SQL в XML с помощью PHP

введение

Возможно, вы слышали о PEAR - архиве приложений и расширений PHP (PHP Extension and Application Repository). Этот проект, поддерживаемый сообществом пользователей, нацелен на создание большой библиотеки высококачественного открытого кода, который поможет программистам на PHP ускорить разработку приложений. Уже давно PEAR, схожий по идее с архивом CPAN для Perl, является первым местом, где я ищу интересные и полезные виджеты PHP+XML. Некоторые из них используют класс XML_Serializer, очень удобный для преобразования структур данных PHP в последовательную форму объектов XML; класс XML_XUL, предоставляющий интерфейс API для разработки приложений Mozilla XUL; и класс XML_SVG, реализующий методы программного построения векторной графики в формате SVG.

В этой статье я расскажу вам еще об одном элементе раздела XML архива PEAR – классе XML_Query2XML. Этот класс реализует программный интерфейс для быстрого и эффективного преобразования результирующего множества данных SQL в правильно оформленный XML-код. Используя немного фантазии, можно преобразовать эти данные в другие форматы с помощью XSLT или интегрировать их с другими приложениями, использующими XML.

установка необходимого программного обеспечения

Пакет XML_Query2XML активно разрабатывается и поддерживается Лукасом Фейлером (Lukas Feiler) и выпускается для сообщества PHP под лицензией LGPL. Для него требуется PHP 5.0 (или более поздней версии). Проще всего установить этот пакет с помощью автоматической установочной программы PEAR, которая должна по умолчанию присутствовать в вашей сборке PHP. Чтобы установить этот пакет, просто выполните следующую команду в командной строке shell:

shell>pear install XML_Query2XML

Программа установки PEAR соединяется с сервером пакетов PEAR, загружает пакет и устанавливает его в соответствующую папку в вашей системе. Чтобы установить пакет вручную, зайдите на веб-сайт PEAR, загрузите архив с пакетом и разархивируйте файлы в нужную директорию. Учтите, что для процесса ручной установки необходимо знание организационной структуры пакета PEAR.

На этом этапе надо также помнить о некоторых зависимостях пакета. Для связи с необходимой СУБД пакет XML_Query2XML использует один из уровней абстракции базы данных DB, MDB2 или ADOdb, поэтому в системе должен быть установлен один из этих уровней абстракции и драйвер соответствующей базы данных. В примерах, приведенных в этой статье, используется уровень абстракции MDB2, который вместе с драйвером MySQL MDB2_Driver_mysql также можно найти в архиве пакетов PEAR. Как было описано ранее, для установки обоих пакетов вы можете воспользоваться программой автоматической установки PEAR или загрузить с сайта PEAR.

В примерах, приведенных в этой статье, используется демонстрационная база данных MySQL world, уже заполненная и содержащая связанные таблицы данных о городах и странах.

Для примеров, приведенных в этой статье, требуется, чтобы сборка PHP поддерживала функции PHP DOM, XSL и SimpleXML. Эти функции включены по умолчанию в PHP 5.x.

Предполагается, что вы знаете функции PHP DOM и SimpleXML, а также технологии XML, XPath и XSL.

Все примеры, приведенные в этой статье, были проверены на XML_Query2XML версии 1.2.1.

преобразование SQL в XML

Если установлены все необходимые компоненты, вы можете начать изучение XML_Query2XML со следующего простого сценария PHP:

Листинг 1. Простое преобразование SQL в XML.

<?php
// включаем необходимые файлы
include 'XML/Query2XML.php';
include 'MDB2.php';

try {
// инициализируем объект Query2XML
$q2x = XML_Query2XML::factory(MDB2::factory('mysql://root:pass@localhost/world'));

// формируем запрос SQL
// получаем результат в XML
$sql = "SELECT * FROM Country";
$xml = $q2x->getFlatXML($sql);

// отправляем результат в браузер
header('Content-Type: text/xml');
$xml->formatOutput = true;
echo $xml->saveXML();
} catch (Exception $e) {
echo $e->getMessage();
}
?>


Этот сценарий демонстрирует пример использования класса XML_Query2XML. Сначала сценарий включает файлы классов XML_Query2XML и MDB2, а затем инициализирует экземпляр уровня абстракции MDB2 с помощью метода factory(). На вход этого метода передается строка DSN, содержащая информацию о типе СУБД, имени пользователя и пароле, а также названии базы данных. Получившийся экземпляр MDB2 используется для инициализации экземпляра XML_Query2XML, представленного объектом $q2x.

Когда вы сформировали строку DSN и создали экземпляр объекта XML_Query2XML, приходит время выполнить запрос SQL к СУБД и преобразовать результат в XML. Это действие реализуется в методе getFlatXML() класса XML_Query2XML, который используется в основном для простых запросов типа SELECT. На выходе этот метод возвращает правильно оформленный документ XML, содержащий результирующее множество SQL. Он будет выглядеть примерно так:

Листинг 2. Документ XML, сформированный в результате работы Листинга 1 (сокращенный).

<?xml version="1.0" encoding="UTF-8"?>
<root>
<row>
<code>AFG</code>
<name>Afghanistan</name>
<continent>Asia</continent>
<region>Southern and Central Asia</region>
<surfacearea>652090.00</surfacearea>
<indepyear>1919</indepyear>
<population>22720000</population>
<lifeexpectancy>45.9</lifeexpectancy>
<gnp>5976.00</gnp>
<gnpold></gnpold>
<localname>Afganistan/Afqanestan</localname>
<governmentform>Islamic Emirate</governmentform>
<headofstate>Mohammad Omar</headofstate>
<capital>1</capital>
<code2>AF</code2>
</row>
<row>
<code>NLD</code>
<name>Netherlands</name>
<continent>Europe</continent>
<region>Western Europe</region>
<surfacearea>41526.00</surfacearea>
<indepyear>1581</indepyear>
<population>15864000</population>
<lifeexpectancy>78.3</lifeexpectancy>
<gnp>371362.00</gnp>
<gnpold>360478.00</gnpold>
<localname>Nederland</localname>
<governmentform>Constitutional Monarchy</governmentform>
<headofstate>Beatrix</headofstate>
<capital>5</capital>
<code2>NL</code2>
</row>
</row>
...
</root>


Если внимательно изучить приведенный выше документ XML, становится видна четкая структура. Каждая запись из результирующего множества SQL представлена элементом <row>, а отдельные поля каждой записи расположены внутри соответствующего <row>. Названия вложенных элементов соответствуют именам полей таблицы, к которой выполняется запрос, а элемент документа – корень дерева XML – называется, соответственно, <root>.

преобразование выходного XML с помощью XSL

Конечно же, создание XML из запроса SQL - это, как правило, только половина работы; после этого с ним нужно что-нибудь сделать. С документом XML можно сделать множество вещей, однако чаще всего его преобразуют с помощью XSLT в какой-либо другой формат, например, HTML или RSS. Принимая это во внимание, составим небольшую таблицу стилей XSL для преобразования вывода XML из Листинга 2 в простую страницу HTML.

Листинг 3. Таблица стилей XSL.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match="/root">
<html>
<head>
<style type="text/css">
td { text-align: center; padding: 3px; }
.head { font-style: italic; }
</style>
</head>
<body>
<table border="1">
<thead>
<tr>
<xsl:for-each select="row[1]/*">
<td class="head">
<xsl:value-of select="local-name(.)"/>
</td>
</xsl:for-each>
</tr>
</thead>
<tbody>
<xsl:apply-templates/>
</tbody>
</table>
</body>
</html>
</xsl:template>

<xsl:template match="row">
<tr>
<xsl:apply-templates/>
</tr>
</xsl:template>

<xsl:template match="row/*">
<td>
<xsl:value-of select="."/>
</td>
</xsl:template>
</xsl:stylesheet>


В Листинге 4 приведен исправленный сценарий PHP, в котором теперь используются функции XSL PHP для преобразования вывода XML_Query2XML.
Листинг 4. Преобразование вывода SQL в XML с помощью XSL.

<?php
// включаем необходимые файлы
include 'XML/Query2XML.php';
include 'MDB2.php';

try {
// инициализируем объект Query2XML
$q2x = XML_Query2XML::factory(MDB2::factory('mysql://root:pass@localhost/world'));

// формируем запрос SQL
// получаем результат в XML
$sql = "SELECT * FROM Country";
$xml = $q2x->getFlatXML($sql);

// считываем данные таблицы стилей XSL
$xsl = new DOMDocument;
$xsl->load('country.xsl');

// инициализируем механизм XSLT
$xslp = new XSLTProcessor;

// подключаем объект таблицы стилей XSL
$xslp->importStyleSheet($xsl);

// выполняем преобразование
header('Content-Type: text/html');
echo $xslp->transformToXML($xml);
} catch (Exception $e) {
echo $e->getMessage();
}
?>


Первая часть этого сценария такая же, как и в Листинге 1; она формирует документ XML, содержащий результаты выполнения запроса SQL и записывает его в переменную $xml как экземпляр DOMDocument. Затем инициализируется экземпляр класса XSLTProcessor и с помощью метода importStyleSheet() импортируется таблица стилей XSL. После этого вызывается метод transformToXML(), получающий в качестве аргумента исходные данные XML, который преобразует документ XML в страницу HTML по правилам, описанным в таблице стилей XSL.

На рисунке 1 показано, как будет выглядеть результат.



Рис. 1. Документ HTML, сформированный программой из листинга 4.

настройка вывода XML

Показанный в предыдущих примерах метод getFlatXML() хорош только в тех случаях, когда вам необходимо быстро выполнить преобразование SQL в XML. Если же вам нужно выполнить более сложную задачу – например, вывести определенные поля результирующего множества в качестве атрибутов, а не элементов, или определить собственные названия элементов, – вам следует воспользоваться методом getXML() класса XML_Query2XML. Этот метод позволяет очень точно настроить выходной документ XML, включая его структуру и стиль.

Рассмотрим пример, приведенный в Листинге 5.

Листинг 5. Настройка вывода SQL в XML.

<?php
// включаем необходимые файлы
include 'XML/Query2XML.php';
include 'MDB2.php';

try {
// инициализируем объект Query2XML
$q2x = XML_Query2XML::factory(MDB2::factory('mysql://root:pass@localhost/world'));

// формируем запрос SQL
// получаем результат в XML
$sql = "SELECT * FROM Country";
$xml = $q2x->getXML($sql, array(
'idColumn' => 'code',
'rootTag' => 'countries',
'rowTag' => 'country',
'attributes' => array('code'),
'elements' => array('name', 'continent', 'area' => 'surfacearea')
)
);

// отправляем результат в браузер
header('Content-Type: text/xml');
$xml->formatOutput = true;
echo $xml->saveXML();
} catch (Exception $e) {
echo $e->getMessage();
}
?>


Метод getXML() принимает два аргумента: запрос SQL, который необходимо выполнить, и массив параметров, определяющих формат создаваемого документа XML. Вот описание каждого из параметров, упомянутых в приведенном выше листинге:

- rootTag - название элемента документа (по умолчанию: root);

- rowTag - название элемента, представляющего каждую строку (по умолчанию: row);

- idColumn - первичный ключ результирующего массива данных;

- attributes - перечень полей, которые должны выводиться как атрибуты XML;

- elements - перечень полей, которые должны выводиться как элементы XML .

В Листинге 6 показано, на что будет похож вывод этого сценария.

Листинг 6. Документ XML, сформированный программой из листинга 5 (сокращенный).

<?xml version="1.0" encoding="UTF-8"?>
<countries>
<country code="AFG">
<name>Afghanistan</name>
<continent>Asia</continent>
<area>652090.00</area>
</country>
<country code="NLD">
<name>Netherlands</name>
<continent>Europe</continent>
<area>41526.00</area>
</country>
...
</countries>


Обратите внимание, что в этом документе XML содержатся не все поля множества данных, а только указанные в массивах elements и attributes, а поля, указанные в массиве attributes, появляются в качестве атрибутов элемента <country>, а не дочерних узлов.

Вы также помните, что по умолчанию названия атрибутов и элементов в документе XML соответствуют названиям полей запроса. Однако при использовании метода getXML() вы можете изменить эти названия, указав иные значения в массивах attributes и elements в виде пар ключ-значение. Пример: поле, называемое surfacearea в результатах SQL, в документ XML выводится просто как элемент <area>.

Дополнительные примеры настройки метода getXML() можно найти в руководстве по XML_Query2XML.

работа с соединениями SQL

Пакет XML_Query2XML также предоставляет средства добавления содержимого результата одного запроса в другой с помощью XML. Эта функция чаще всего используется при работе с соединениями или запросами, связанными каким-либо иным образом; кроме того, она полезна в случаях, когда вы желаете разбить один большой запрос на несколько меньших с целью повышения производительности.

Чтобы разобраться в этом, вернемся к базе данных world и рассмотрим две ее таблицы, Country и City, которые связаны друг с другом внешним ключом code.

Теперь давайте предположим, что вы желаете создать дерево документа XML, в котором элементы <city> будут вложены в элементы <country>. Предположим также, что вы желаете ограничить вывод пятью городами каждой страны, имеющими наибольшее население, а также хотите, чтобы значения полей отображались не элементами, а атрибутами. Короче, нам нужен документ XML, похожий на следующий пример из листинга 7.

Листинг 7. Ожидаемая структура XML после SQL-соединения (сокращенная).

<?xml version="1.0" encoding="UTF-8"?>
<countries>
<country code="IND" name="India" region="Southern and Central Asia">
<cities>
<city name="Mumbai (Bombay)" district="Maharashtra" population="10500000"/>
<city .../>
<city .../>
<city .../>
<city .../>
</cities>
</country>
<country ...>
...
</country>
...
</countries>


В Листинге 8 приведен код, необходимый для формирования такого документа XML.

Листинг 8. Создание документа XML, соответствующего требованиям, по данным соединения SQL.

<?php
// включаем необходимые файлы
include 'XML/Query2XML.php';
include 'MDB2.php';

try {
// инициализируем объект Query2XML
$q2x = XML_Query2XML::factory(MDB2::factory('mysql://root:pass@localhost/world'));

// формируем запрос SQL
// получаем результат в XML
$sql_1 = "SELECT * FROM Country";
$sql_2 = "SELECT * FROM City WHERE CountryCode = ? ORDER BY Population DESC LIMIT 5";
$xml = $q2x->getXML($sql_1, array(
'idColumn' => 'code',
'rootTag' => 'countries',
'rowTag' => 'country',
'attributes' => array('code', 'name', 'continent'),
'elements' => array('cities' => array(
'sql' => array('data' => array('code'), 'query' => $sql_2),
'idColumn' => 'id',
'rootTag' => 'cities',
'rowTag' => 'city',
'attributes' => array('name','district','population'))
)
)
);

// отправляем результат в браузер
header('Content-Type: text/xml');
$xml->formatOutput = true;
echo $xml->saveXML();
} catch (Exception $e) {
echo $e->getMessage();
}
?>


Основной момент, на который стоит обратить внимание в этом примере – это массив elements. В отличие от кода в Листинге 5, где в этом массиве содержался лишь перечень полей результата, отображаемых в виде элементов, здесь выполняется значительно более сложная функция. Сначала в нем определяется новый элемент, <cities>, который связывается с массивом параметров, содержащим пары ключ-значение. Единственным новым ключом в этом массиве параметров является ключ sql, определяющий запрос SQL, который будет использован для наполнения элемента <cities>.

Стоит потратить несколько минут на то, чтобы изучить ключ sql. Этот ключ связан с ассоциативным массивом, который, в свою очередь, содержит два ключа:

- data, определяющий поля, которые должны быть импортированы из внешнего запроса SQL;

- query, определяющий вложенный запрос SQL, который должен быть запущен для наполнения элемента <cities>.

Обратите внимание, что во втором запросе содержится знак вопроса (?), определяющий поле для подстановки – во время выполнения запроса он заменяется текущим значением полей, указанных в массиве data. Или, приводя конкретный пример, если запись, которую вернул внешний запрос для поля code, имеет значение 'IND', то это значение 'IND' будет подставлено во внутренний вопрос вместо знака «?».

Теперь вам должны быть понятны способности XML_Query2XML. Вы можете заполнить каждый массив elements отдельным запросом SQL, что позволит создавать неограниченно вложенные результаты запросов. Кроме того, поскольку каждый массив elements может ссылаться на поля из родительского запроса, можно создавать последовательности вложенных запросов (аналог соединений SQL), которые будут связаны между собой по определенным полям. Результат работы сценария будет выглядеть, как показано в листинге 9.

Листинг 9. Документ XML, созданный в результате работы сценария из листинга 8 (сокращенный).

<?xml version="1.0" encoding="UTF-8"?>
<countries>
<country code="AFG" name="Afghanistan" continent="Asia">
<cities>
<city name="Kabul" district="Kabol" population="1780000"/>
<city name="Qandahar" district="Qandahar" population="237500"/>
...
</cities>
</country>
<country code="NLD" name="Netherlands" continent="Europe">
<cities>
<city name="Amsterdam" district="Noord-Holland" population="731200"/>
<city name="Rotterdam" district="Zuid-Holland" population="593321"/>
<city name="Haag" district="Zuid-Holland" population="440900"/>
...
</cities>
</country>
...
</countries>


Настал момент создать новую таблицу стилей XSL, которая будет учитывать новую структуру XML:

Листинг 10. Таблица стилей XSL для преобразования Листинга 9.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match="/countries">
<html>
<head>
<style type="text/css">
td { text-align: center; padding: 3px; }
head { font-style: italic; }
</style>
</head>
<body>
<xsl:for-each select="country">
<h2><xsl:value-of select="@name"/> - <xsl:value-of
select="@continent"/></h2>
<table border="1">
<thead>
<tr>
<xsl:for-each select="cities/city[1]/@*">
<td class="head">
<xsl:value-of select="name(.)"/>
</td>
</xsl:for-each>
</tr>
</thead>
<tbody>
<xsl:apply-templates/>
</tbody>
</table>
</xsl:for-each>
</body>
</html>
</xsl:template>

<xsl:template match="cities/city">
<tr>
<xsl:for-each select="@*">
<td>
<xsl:value-of select="."/>
</td>
</xsl:for-each>
</tr>
</xsl:template>
</xsl:stylesheet>


Конечно же, вы также должны изменить исходный сценарий PHP для преобразования сформированного XML с помощью этой таблицы стилей. Изменение, впрочем, будут небольшими.

Листинг 11. Преобразование документа XML, сформированного сценарием из Листинга 8.

<?php
// включаем необходимые файлы
include 'XML/Query2XML.php';
include 'MDB2.php';

try {
// инициализируем объект Query2XML
$q2x = XML_Query2XML::factory(MDB2::factory('mysql://root:pass@localhost/world'));

// формируем запрос SQL
// получаем результат в XML
$sql_1 = "SELECT * FROM Country";
$sql_2 = "SELECT * FROM City WHERE CountryCode = ? ORDER BY Population DESC LIMIT 5";
$xml = $q2x->getXML($sql_1, array(
'idColumn' => 'code',
'rootTag' => 'countries',
'rowTag' => 'country',
'attributes' => array('code', 'name', 'continent'),
'elements' => array('cities' => array(
'sql' => array('data' => array('code'), 'query' => $sql_2),
'idColumn' => 'id',
'rootTag' => 'cities',
'rowTag' => 'city',
'attributes' => array('name','district','population'))
)
)
);

// считываем данные таблицы стилей XSL
$xsl = new DOMDocument;
$xsl->load('countries.xsl');

// инициализируем механизм XSLT
$xslp = new XSLTProcessor;

// подключаем объект таблицы стилей XSL
$xslp->importStyleSheet($xsl);

// выполняем преобразование
header('Content-Type: text/html');
echo $xslp->transformToXML($xml);
} catch (Exception $e) {
echo $e->getMessage();
}
?>


На Рисунке 2 показано, как выглядит преобразованный XML.



Рис. 2. Документ HTML, созданный в Листинге 11.

Существует множество вариантов использования возможностей организации вложенности, кроме того, XML_Query2XML предлагает множество вариантов дополнительной настройки вывода документов XML. Подробные примеры можно найти в руководстве XML_Query2XML.



Викрам Васвани, основатель компании, Melonfire
обсудить статью
© сетевые решения
.
.