Преобразование 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, загружает пакет и устанавливает его в соответствующую папку в вашей системе.

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

На этом этапе надо также помнить о некоторых зависимостях пакета:

Для связи с необходимой СУБД пакет XML_Query2XML использует один из уровней абстракции базы данных DB, MDB2 или ADOdb, поэтому в системе должен быть установлен один из этих уровней абстракции и драйвер соответствующей базы данных. В примерах, приведенных в этой статье, используется уровень абстракции MDB2, который вместе с драйвером MySQL MDB2_Driver_mysql также можно найти в архиве пакетов PEAR. Как было описано ранее, для установки обоих пакетов вы можете воспользоваться программой автоматической установки PEAR или загрузить из с Web-сайта PEAR.
В примерах, приведенных в этой статье, используется демонстрационная база данных MySQL world, уже заполненная и содержащая связанные таблицы данных о городах и странах. Ссылка на загрузку базы данных 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>
    <code>ANT</code>
    <name>Netherlands Antilles</name>
    <continent>North America</continent>
    <region>Caribbean</region>
    <surfacearea>800.00</surfacearea>
    <indepyear></indepyear>
    <population>217000</population>
    <lifeexpectancy>74.7</lifeexpectancy>
    <gnp>1941.00</gnp>
    <gnpold></gnpold>
    <localname>Nederlandse Antillen</localname>
    <governmentform>Nonmetropolitan Territory of 
    The Netherlands</governmentform>
    <headofstate>Beatrix</headofstate>
    <capital>33</capital>
    <code2>AN</code2>
  </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 class=borderall>
                    <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
CNhfybwf HTML, сформированного после преобразования XSL

Настройка вывода 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. В Таблице 1 приводится описание каждого из параметров, упомянутых в приведенном выше листинге:

Таблица 1. Параметры метода getXML()

Параметр	Чем управляет
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>
  <country code="ANT">
    <name>Netherlands Antilles</name>
    <continent>North America</continent>
    <area>800.00</area>
  </country>
  ...
</countries>

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

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

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

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

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

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

Листинг 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. Документ 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"/>
      <city name="Herat" district="Herat" population="186800"/>
      <city name="Mazar-e-Sharif" district="Balkh" population="127800"/>
    </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"/>
      <city name="Utrecht" district="Utrecht" population="234323"/>
      <city name="Eindhoven" district="Noord-Brabant" population="201843"/>
    </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 class=borderall>
                        <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
Страница HTML, сформированная после преобразования XSL

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

Фильтрация записей SQL с помощью XPath

Как можно предположить, ограничить количество записей, выводимых методом getXML(), достаточно легко. Достаточно просто добавить соответствующее условие WHERE к запросу SQL. Другим способом является использование конструкций XPath для создания ограниченных подмножеств дерева XML и его возвращение вызывающему клиенту.

В Листинге 12 приведен простой пример реализации этого способа путем модификации Листинга 11 и ограничения выводимых в XML данных только странами и городами, расположенными в Европе, с помощью условий XPath:

Листинг 12. Ограничение данных, выводимых из SQL в XML с помощью XPath

                
<?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'))
            )
        )    
    );

    // теперь фильтруем XML еще раз с помощью XPath
    // возвращаем в DOMNodeList только те узлы <country>,    
    // у которых есть атрибут 'continent=Europe'
    $xpath = new DOMXPath($xml);
    $nodelist = $xpath->query("/countries/country[@continent='Europe']");
    
    // создаем новое дерево DOM с помощью множества XPath 
    // создаем корневой элемент
    // импортируем все узлы из списка и вставляем в новое дерево DOM 
    $dom = new DOMDocument;
    $root = $dom->createElement('countries');
    $dom->appendChild($root);
    $x = 0;
    while ($node = $nodelist->item($x)) {
        $node = $dom->importNode($node, true);
        $root->appendChild($node);
        $x++;
    }
    
    // выполняем печать XML
    header('Content-Type: text/xml');
    $dom->formatOutput = true;
    echo $dom->saveXML();
} catch (Exception $e) {
    echo $e->getMessage();
}
?>

Первая часть сценария осталась неизменной - два вложенных запроса SQL, для формирования списка стран и городов внутренний запрос использует данные из внешнего. Однако на этот раз вместо того, чтобы выводить XML напрямую на печать или передавать его обработчику XSLT, инициализируется объект DOMXPath и из исходного дерева XML создается новый список DOMNodeList. Чтобы вывести в этот список только элементы <country>, содержащие атрибут continent со значением Europe, используется запрос XPath. После создания списка DOMNodeList инициализируется новый объект DOMDocument и в него импортируется этот DOMNodeList, формируя новый документ XML.

В Листинге 13 показан фрагмент результата:

Листинг 13. Результат XML, сформированный программой в листинге 12 (сокращенный)

                
<?xml version="1.0"?>
<countries>
  <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"/>
      <city name="Utrecht" district="Utrecht" population="234323"/>
      <city name="Eindhoven" district="Noord-Brabant" population="201843"/>
    </cities>
  </country>
  <country code="ALB" name="Albania" continent="Europe">
    <cities>
      <city name="Tirana" district="Tirana" population="270000"/>
    </cities>
  </country>
  <country code="AND" name="Andorra" continent="Europe">
    <cities>
      <city name="Andorra la Vella" district="Andorra la Vella" population="21189"/>
    </cities>
  </country>
  <country code="BEL" name="Belgium" continent="Europe">
    <cities>
      <city name="Antwerpen" district="Antwerpen" population="446525"/>
      <city name="Gent" district="East Flanderi" population="224180"/>
      <city name="Charleroi" district="Hainaut" population="200827"/>
      <city name="Liège" district="Liège" population="185639"/>
      <city name="Bruxelles [Brussel]" district="Bryssel" population="133859"/>
    </cities>
  </country>
  ...
<countries>

Слияние данных из нескольких источников

При разработке реальных приложений на базе XML маловероятно, что документ XML будет содержать информацию только из одного источника. В дополнение к одному или нескольким запросам SQL он может также включать в себя данные из дисковых файлов, от внешних Web-сервисов или из системной таблицы процессов. Для учета этой ситуации XML_Query2XML предоставляет способ интеграции в документ XML, возвращаемый методом getXML(), данных из источников, отличных от SQL.

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

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

Код представлен в Листинге 14:

Листинг 14. Интеграция данных Web-сервисов с результатами преобразования SQL в XML

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

try {
    // инициализируем объект Query2XML
    $q2x = XML_Query2XML::factory(MDB2::factory('mysql://root:pass@localhost/world'));
    
    // формируем запрос SQL
    // получаем результат в XML
    $sql = "SELECT Country.Code2 AS code, Country.Name AS country, City.Name AS city, 
        City.Population AS population FROM Country, City 
        WHERE Country.Code = City.CountryCode GROUP BY City.CountryCode 
        HAVING City.Population = MAX(City.Population) ORDER BY City.Population 
        DESC LIMIT 15";
    $xml = $q2x->getXML($sql, array(
            'idColumn' => 'code',
            'rootTag' => 'countries',
            'rowTag' => 'country',
            'attributes' => array('code', 'name' => 'country'),
            'elements' => array('city' => array (
                'elements' => array(
                    'name' => 'city',
                    'population',
                    'location' => '#getLocation'),
                )
            ),
        )    
    );
    
    // выводим XML
    header('Content-Type: text/html');
    $xml->formatOutput = true;
    print $xml->saveXML();
} catch (Exception $e) {
    echo $e->getMessage();
}

// функция получения данных Web-сервиса GeoNames
// вызываем GeoNames и передаем название страны и города
// создаем фрагмент документа XML с возвращенными значениями 
function getLocation($record) {
    // получаем данные и формат в объект SimpleXML
    $sxml = simplexml_load_string(file_get_contents(
        "http://ws.geonames.org/search?maxRows=1&name=" . 
            urlencode(utf8_encode($record['city'])) . "&country=" . 
            urlencode(utf8_encode($record['code']))));
    
    // извлекаем данные из объекта SimpleXML
    // преобразуем их в фрагменты DOMNode 
    $dom = new DOMDocument();
    // формируем узел <lat>
    $lat = $dom->createElement('lat');
    $lat->appendChild($dom->createTextNode($sxml->geoname{0}->lat));
    // формируем узел <long>
    $long = $dom->createElement('long');
    $long->appendChild($dom->createTextNode($sxml->geoname{0}->lng));
    return array($lat, $long);
}
?>

Вызов getXML() в Листинге 14 выполняет запрос SELECT, который группирует города по странам и выбирает города с наибольшим населением. После этого данные преобразуются в документ XML следующего вида (Листинг 15):

Листинг 15. Документ XML, сформированный кодом из Листинга 14 на первом этапе (сокращенный)

                
<?xml version="1.0" encoding="UTF-8"?>
<countries>
  <country code="AW" name="Aruba">
    <city>
      <name>Oranjestad</name>
      <population>29034</population>
    </city>
  </country>
  ...
</countries>

Теперь нужно получить координаты широты и долготы каждого из городов и добавить их в сформированное ранее дерево документа (см. Листинг 14). Эта информация запрашивается на Web-сервисе GeoNames, доступном в стиле REST, у которого открыт метод search(), возвращающий географическую информацию указанного места.

В Листинге 16 показан пример пакета, который высылает GeoNames в ответ на запрос 'Berlin, Germany':

Листинг 16. Пример ответного пакета GeoNames

                
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<geonames>
 <totalResultsCount>807</totalResultsCount>
 <geoname>
  <name>Berlin</name>
  <lat>52.5166667</lat>
  <lng>13.4</lng>
  <geonameId>2950159</geonameId>
  <countryCode>DE</countryCode>
  <countryName>Germany</countryName>
  <fcl>P</fcl>
  <fcode>PPLC</fcode>
 </geoname>
</geonames>

Как видите, в ответном пакете содержится различная информация об указанном месте, в том числе и данные, которые нас интересуют - координаты широты и долготы.

Давайте теперь внимательно посмотрим на вызов getXML() в Листинге 13. Обратите внимание, что ключ location массива параметров является связанным, но не с полем из результата запроса, а с функцией внешнего вызова getLocation(). Это означает, что всякий раз, когда getXML() обрабатывает запись из результирующего множества SQL, он вызывает getLocation() для этой записи как ассоциативный массив пар поле-значение. Метод getLocation(), в свою очередь, с помощью REST вызывает метод search() Web-сервиса GeoNames, передает ему в качестве параметра название города и страны из записи SQL и получает ответ как объект SimpleXML. После этого для получения элементов <lat> и <lng> из ответного пакета, их преобразования в два отдельных экземпляра DOMNode и их передачи getXML() в виде массива для вставки в дерево можно использовать нотацию SimpleXML.

В конце обработки выходной документ будет выглядеть так, как показано в Листинге 17:

Листинг 17. Окончательный выходной документ XML, формируемый в Листинге 14 (сокращенный)

                
<?xml version="1.0" encoding="UTF-8"?>
<countries>
  <country code="IN" name="India">
    <city>
      <name>Mumbai (Bombay)</name>
      <population>10500000</population>
      <location>
        <lat>18.975</lat>
        <long>72.8258333</long>
      </location>
    </city>
  </country>
  <country code="KR" name="South Korea">
    <city>
      <name>Seoul</name>
      <population>9981619</population>
      <location>
        <lat>37.5663889</lat>
        <long>126.9997222</long>
      </location>
    </city>
  </country>
  ...
</countries>

Как видно из этого примера, использование собственных функций внешнего вызова - это простейший путь преобразования данных из различных источников в документ XML, формируемый методом getXML(). Сценарий из Листинга 14 подключается к внешнему Web-сервису; так же просто можно импортировать в конечное дерево документа XML внешний файл или результат выполнения вызова XML-RPC.

Создание резервной копии базы данных

Еще одним полезным приложением пакета XML_Query2XML является копирование содержимого таблиц базы данных в формат XML для последующего хранения и резервирования. Логика сценария резервного копирования очень проста: получить перечень таблиц базы данных, последовательно пройти по этому списку и выполнить запросы DESC ? и SELECT * FROM ? SQL для извлечения соответственно схемы и записей каждой из таблиц. Если вы внимательно изучали статью, вы, наверное, уже представляете себе, как должен выглядеть выполняющий эту функцию метод getXML().

Решить эту задачу кажется на первый взгляд не так просто, главным образом из-за ограничений уровня абстракции MDB2, а именно, из-за того, что он не может обрабатывать символы подстановки для названий таблиц и колонок в подготовленных запросах. Это усложняет использование упомянутых ранее запросов DESC ? и SELECT * FROM ?, так как уровень MDB2 будет просто выдавать ошибку, если встретит такие запросы.

Что делать в этой ситуации? Проявить немного фантазии, как в Листинге 18:

Листинг 18. Формирование дампа структуры и содержимого базы данных в формате XML

                
<?php
ini_set('max_execution_time', 120);

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

// устанавливаем название базы данных
$db = 'world';

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

    // выполняем запрос SQL для получения списка таблиц
    // примечания: этот запрос будет разным для разных баз данных
    $sql = "SHOW TABLES";
    $xml = $q2x->getXML($sql, array(
                'idColumn' => false,
                'rootTag' => 'database',
                'rowTag' => 'table',
                'attributes' => array('name' => 'tables_in_' . $db))    
    );

    // получаем список всех узлов <table>
    $nodelist = $xml->getElementsByTagName("table");    
    
    // проходим по каждому из узлов
    $x = 0;
    while ($node = $nodelist->item($x)) {
        // извлекаем название таблицы
        $table = $node->attributes->getNamedItem('name')->nodeValue;
        
        // получаем описание таблицы
        // в виде документа DOM 
        // примечание: этот запрос будет разным для разных баз данных
        $sql_1 = 'DESC ' . $table;
        $schema = $q2x->getXML($sql_1, array (
            'idColumn' => 'field',
            'rowTag' => 'define',
            'rootTag' => 'schema',
            'elements' => array('*'))
        );
        
        // получаем содержимое таблицы
        // в виде другого документа DOM 
        $sql_2 = 'SELECT * FROM ' . $table;
        $data = $q2x->getXML($sql_2, array (
            'idColumn' => false,
            'rowTag' => 'record',
            'rootTag' => 'data',
            'elements' => array('*'))
        );
        
        // проходим по документу DOM $schema 
        // используем XPath для получения узла <schema> и всех дочерних узлов
        // импортируем его в основное дерево XML, в соответствующий элемент <table>
        // автор идеи: Игорь Краус (Igor Kraus), http://www.php.net/simplexml
        $xpath = new DOMXPath($schema);
        $query = $xpath->query('//schema');
        for ($i = 0; $i < $query->length; $i++) {
            $xml->documentElement->childNodes->item($x)->appendChild(
                $xml->importNode($query->item($i), true));
        }
        
        // выполняем ту же операцию для документа DOM $data 
        $xpath = new DOMXPath($data);
        $query = $xpath->query('//data');
        for ($i = 0; $i < $query->length; $i++) {
            $xml->documentElement->childNodes->item($x)->appendChild(
                $xml->importNode($query->item($i), true));
        }
        
        // увеличиваем счетчик для следующего прохода
        $x++;
    }
    
    // записываем результат на диск
    // выводим сообщение об успешном завершении или ошибке
    $xml->formatOutput = true;
    if ($xml->save('/tmp/dump.xml')) {
        echo 'Data successfully saved!';
    } else {    
        echo 'Data could not be saved!';
    }
} catch (Exception $e) {
        echo $e->getMessage();    
}
?>

Выглядит достаточно сложно, но на самом деле все просто:

Во-первых, получаем название всех таблиц текущей базы данных. Вид запроса SQL для получения этого списка будет различным для разных баз данных. В сценарии, приведенном в Листинге 18, используется запрос для MySQL SHOW TABLES, но он не будет работать в других СУБД. Если вы используете другую систему баз данных, этот запрос нужно изменить. Результат выполнения этой команды в виде документа XML хранится в переменной $xml и имеет вид, представленный в Листинге 19:
Листинг 19. Результат XML, формируемый программной в листинге 18 на первом этапе
<?xml version="1.0" encoding="UTF-8"?> <database> <table name="City"/> <table name="Country"/> <table name="CountryLanguage"/> </database>

После этого получаем набор всех элементов <table>, созданных на предыдущем шаге, вызывая в цикле метод getElementsByTagName(). На каждом шаге цикла создаются два новых документа XML: $schema, в котором содержится информация о структуре полей таблицы (смотри Листинг 20), и $data, в котором хранятся все записи таблицы (смотри Листинг 21):

Листинг 20. Документ XML, содержащий схему таблицы

                        
<?xml version="1.0" encoding="UTF-8"?>
<schema>
  <define>
    <field>ID</field>
    <type>int(11)</type>
    <null>NO</null>
    <key>PRI</key>
    <default/>
    <extra>auto_increment</extra>
  </define>
  <define>
    <field>Name</field>
    <type>char(35)</type>
    <null>NO</null>
    <key/>
    <default/>
    <extra/>
  </define>
  <define>
  ...
  </define>
</schema>

Листинг 21. Документ XML, содержащий записи таблицы

                        
<?xml version="1.0" encoding="UTF-8"?>
<data>
  <record>
    <id>1</id>
    <name>Kabul</name>
    <countrycode>AFG</countrycode>
    <district>Kabol</district>
    <population>1780000</population>
  </record>
  <record>
    <id>2</id>
    <name>Qandahar</name>
    <countrycode>AFG</countrycode>
    <district>Qandahar</district>
    <population>237500</population>
  </record>
  <record>
  ...
  </record>
</data>

Продолжая ту же итерацию цикла, импортируем два независимых документа XML, $schema и $data, в родительский документ $xml. Как вы уже видели из предыдущих примеров, XPath предоставляет простой способ извлечения фрагментов узла XML из $schema и $xml; остальные обрабатывает метод importNode() расширения DOM, вставляя эти фрагменты в соответствующую точку основного дерева XML.

В Листинге 22 приведен фрагмент конечного документа:

Листинг 22. Конечный документ XML, формируемый программой в листинге 18

                        
<?xml version="1.0" encoding="UTF-8"?>
<database>
  <table name="City">
    <schema>
      <define>
        <field>ID</field>
        <type>int(11)</type>
        <null>NO</null>
        <key>PRI</key>
        <default/>
        <extra>auto_increment</extra>
      </define>
      <define>
        <field>Name</field>
        <type>char(35)</type>
        <null>NO</null>
        <key/>
        <default/>
        <extra/>
      </define>
      ...
    </schema>
    <data>
      <record>
        <id>1</id>
        <name>Kabul</name>
        <countrycode>AFG</countrycode>
        <district>Kabol</district>
        <population>1780000</population>
      </record>
      <record>
        <id>2</id>
        <name>Qandahar</name>
        <countrycode>AFG</countrycode>
        <district>Qandahar</district>
        <population>237500</population>
      </record>
      ...
    </data>
  </table>
  <table>
  ...
  </table>
</database>

В Листинге 23 представлено другое, более элегантное решение, предложенное Лукасом Фейлером (Lukas Feiler), разработчиком класса XML_Query2XML:

Листинг 23. Альтернативный способ формирования дампа структуры и содержимого базы данных в XML

                
<?php
ini_set('max_execution_time', 120);

// автор: Lukas Feiler, http://www.lukasfeiler.com
// включаем файлы
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';

// инициализируем уровень абстракции MDB 
// загружаем диспетчер MDB
$mdb2 = MDB2::factory('mysql://root:pass@localhost/world');
$mdb2->loadModule('Manager');

// инициализируем объект Query2XML
$q2x = XML_Query2XML::factory($mdb2);

// получаем перечень таблиц
$tables = $mdb2->listTables();

// динамически создаем массив $options
// для каждой таблицы
$elements = array();
for ($i=0; $i<count($tables); $i++) {
    $elements['table' . $i] = array(
        'rowTag' => 'table',
        'attributes' => array(
            'name' => ':' . $tables[$i]
        ),
        'elements' => array(
            'record' => array(
                'idColumn' => false,
                'sql' => 'SELECT * FROM ' . $tables[$i],
                'elements' => array(
                    '*'
                )
            )
        )
    );
}

// получаем данные из таблиц в XML
$xml = $q2x->getXML(
    false,
    array(
        'idColumn' => false,
        'rowTag' => '__tables',
        'rootTag' => 'database',
        'elements' => $elements 
    )
);

// записываем результат на диск
// выводим сообщение об успешном завершении или ошибке
$xml->formatOutput = true;
if ($xml->save('/tmp/dump.xml')) {
    echo 'Data successfully saved!';
} else {    
    echo 'Data could not be saved!';
}
?>

Это решение сначала загружает модуль MDB2 Manager и с помощью его метода listTables() получает список всех таблиц базы данных, независимо от типа базы. После этого оно проходит по списку таблиц и на каждой итерации динамически формирует новый массив elements. После обработки всех таблиц вызов метода getXML() с динамически созданным массивом elements, создает дамп всей базы данных в формате XML, который записывается на диск. В Листинге 24 показан фрагмент выходного файла:

Листинг 24. Документ XML, полученный в результате работы программы из листинга 23

                
<?xml version="1.0" encoding="UTF-8"?>
<database>
  <table name="city">
    <record>
      <id>1</id>
      <name>Kabul</name>
      <countrycode>AFG</countrycode>
      <district>Kabol</district>
      <population>1780000</population>
    </record>
    <record>
      <id>2</id>
      <name>Qandahar</name>
      <countrycode>AFG</countrycode>
      <district>Qandahar</district>
      <population>237500</population>
    </record>
    ...
  </table>
  <table name="country">
    <record>
      <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/>
      <localname>Afganistan/Afqanestan</localname>
      <governmentform>Islamic Emirate</governmentform>
      <headofstate>Mohammad Omar</headofstate>
      <capital>1</capital>
      <code2>AF</code2>
    </record>
    ...
  </table>
  <table>
  ...
  </table>
</database>

Заключение

Как видно из приведенных ранее листингов, возможности пакета XML_Query2XML значительно шире простого форматирования множества данных SQL в XML. Он может служить основой для широкого спектра приложений, от простых конвертеров SQL в HTML до инструментов, которые создают сложные документы XML из множества источников данных, включая Web-сервисы, дисковые файлы и различные базы данных. Поэтому этот пакет будет ценным дополнением к инструментарию любого разработчика PHP. Попробуйте использовать его в следующий раз, когда вам понадобится интерфейс между приложением PHP/XML и базой данных SQL, и убедитесь в этом сами!

Файлы для загрузки

Пример базы данных world MySQL

Преобразование SQL в XML с помощью PHP (исходники)