XML_Query2XML::getXML()
XML_Query2XML::getXML($sql, $options)
This method is the most powerful transformation method. It returns an instance of
DOMDocument (part of PHP5's built-in DOM API). The records returned by the query/queries will be processed
one after another. The $options argument is a rather complex, associative,
multi dimensional array. The $sql argument can be a string or as well an associative array.
$sql
This option is almost exactly like $options['sql']: you
can specify the query with a Simple Query Specification
or a Complex Query Specification.
What is different from $options['sql'] is that
you can also specify a boolean value of false.
Here is an example of a simple query specification (WARNING: to prevent SQL injection
vulerabilities you should use a complex query specification when dealing with non-static queries like this one):
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
if (isset
($_REQUEST['artistid']) &&
is_numeric($_REQUEST['artistid'])) { $artistid = $_REQUEST['artistid'];
} else {
$artistid = 1;
}
$dom = $query2xml->getXML(
"SELECT * FROM artist WHERE artistid = $artistid",
array(
'rootTag' => 'favorite_artist',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'name',
'birth_year',
'music_genre' => 'genre'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
Next we use a Complex Query Specification
and prevent SQL injections by using PDO's/MDB2's/DB's/ADOdb's prepare() and execute() methods.
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$artistid = $_REQUEST['artistid'];
$dom = $query2xml->getXML(
array(
'data' => array(
":$artistid"
),
'query' => 'SELECT * FROM artist WHERE artistid = ?'
),
array(
'rootTag' => 'favorite_artist',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'name',
'birth_year',
'music_genre' => 'genre'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artist>
<artist>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<music_genre>Soul</music_genre>
</artist>
</favorite_artist>
As stated above $sql can also be a boolean value of false. This will only be useful in
scenarios where you want to combine the results of multiple unrelated queries into
a single XML document. XML_Query2XML will deal with an $sql argument that has a value
of false as if it executed a query that returned a single record with no colunns.
If you simpy wanted all the records of the table "album" and all
the records of the table "artist" you could write code like this:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
false,
array(
'idColumn' => false,
'rowTag' => '__tables',
'rootTag' => 'music_store',
'elements' => array(
'artists' => array(
'rootTag' => 'artists',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'sql' => 'SELECT * FROM artist',
'elements' => array(
'*'
)
),
'albums' => array(
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'sql' => 'SELECT * FROM album',
'elements' => array(
'*'
)
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
The resulting XML looks like this:
<?xml version="1.0" encoding="UTF-8"?>
<music_store>
<artists>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
</artist>
<artist>
<artistid>3</artistid>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<birth_place>Mississippi</birth_place>
<genre>Country and Soul</genre>
</artist>
</artists>
<albums>
<album>
<albumid>1</albumid>
<artist_id>1</artist_id>
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ever!</comment>
</album>
<album>
<albumid>2</albumid>
<artist_id>1</artist_id>
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man's got somthin' to say</comment>
</album>
<album>
<albumid>3</albumid>
<artist_id>2</artist_id>
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
</album>
</albums>
</music_store>
Want to dump not just two but all of your table? Have a look at
Using dynamic $options to dump all data of your database.
$options['elements']
This option is an array that basically holds column names to include in the XML data as child
elements. There are two types of element specifications:
Simple Element Specifications
These allow you to use an array to specify elements that have only two properties: a name and a value.
The array values are used to specify the XML element values whereas the array keys are used to specify the
XML element names. For all array elements that are defined without a key, the array values will be used for
the XML element names. If no prefix is used (see below) the contents of the array values are interpreted
as column names. The following example illustrates the most basic usage of a simple element specification:
array(
'COLUMN1',
'COLUMN2'
);
<COLUMN1>this was the contents of COLUMN1</COLUMN1>
<COLUMN2>this was the contents of COLUMN2</COLUMN2>
If you do not want your XML elements named after your database columns you have to work with array keys
(ELEMENT1 and ELEMENT2 in our example):
while the element specification
array(
'ELEMENT1' => 'COLUMN1',
'ELEMENT2' => 'COLUMN2'
);
<ELEMENT1>this was the contents of COLUMN1</ELEMENT1>
<ELEMENT2>this was the contents of COLUMN2</ELEMENT2>
If you use both, the array key and the array value to specify an XML element, the array value can be of the following types:
- COLUMN NAME (string): this is the default if not preceeded by ':' or '#'.
If the column does not exist, an XML_Query2XML_ConfigException will be thrown.
- STATIC TEXT with a : prefix (string): if the value is preceeded by a colon (':'), it is
interpreted as static text.
- CALLBACK FUNCTION with a # prefix (string): if the value is preceeded by a pound sign ('#'), it
is interpreted as a callback function. You can use a regular function (e.g. '#myFunction()') or a static
method (e.g. '#MyClass::myFunction()') - for how to use a non-static method, see the type COMMAND OBJECT.
The current record will be passed to the callback function
as an associative array. You can also pass additional string arguments to the callback function by specifing
them within the opening and closing brace; e.g. '#Utils::limit(12)' will result in Util::limit() being
called with the current record as the first and '12' as the second argument. If you do not want
to pass additional arguments to the callback function, the opening and closing brace are optional.
The callback function's return value will be converted to a string and used as the child text node
if it is anything but an object or an array. If you do return an object or an array from a callback
function it has to be an instance of DOMNode or
an array of DOMNode instances. Please see Integrating other XML data sources for examples
and further details. If an instances of any other class is returned, a XML_Query2XML_XMLException will be thrown.
- COMMAND OBJECT (object): If you want to use a non-static method as a
callback function, you can do so by specifying the value as an instance of a class that
implements the XML_Query2XML_Callback interface. This implementation of the
command pattern gives you all the
flexibility. The disadvantage ist that you cannot use the XML UNSERIALIZATION prefix
or the CONDITIONAL prefix. Note: you have to require_once 'XML/Query2XML/Callback.php'
before using the XML_Query2XML_Callback interface. The return value of a COMMAND OBJECT's
execute() method is treated exactly the same as the return value of a CALLBACK FUNCTION.
There are four more prefixes available that can be used in conjunction with all the prifixes described above:
- XML UNSERIALIZATION prefix &: the ampersand (&) prefix allows you to
automatically unserialize string data, i.e. transform a string into a DOMDocument. DOMDocument's
loadXML() method will be
used for this purpose. You can combine all three types with this prefix: '&COLUMN_NAME',
'&#function()' or '&:<name>John</name>' will all work. You can even use the CONDITIONAL prefix
which has to preceed all other prefixes. If the data cannot be unserialized i.e.
DOMDocument::loadXML()
returns false, a XML_Query2XML_XMLException will be thrown. Please see
Integrating other XML data sources for examples and further
details.
- BASE64 ENCODING prefix ^: if the specification starts with a carrat sign ('^'),
the element value will be passed to base64_encode().
The BASE64 ENCODING prefix can be used with all the prefixes described above (just put the BASE64 ENCODING prefix first):
e.g. '^#', '^:' or '^COLUMN_NAME'.
- CDATA SECTION prefix =: if the specification starts with an equal sign ('='),
the element value will be enclosed in a CDATA section. A CDATA section starts with
"<![CDATA[" and ends with "]]>".
The CDATA SECTION prefix can be used with all the prefixes described above (just put the CDATA SECTION prefix first):
e.g. '=#', '=:', '=COLUMN_NAME' or '=^'.
- CONDITIONAL prefix ?: if the specification starts with a question mark ('?'),
the whole element will be skipped if the value equals (==) an empty string. The CONDITIONAL prefix
can be combined with all types described above: if you do this you have to write the CONDITIONAL
prefix first e.g. '?#', '?:', '?&', '?=', '?^', or '?COLUMN_NAME'.
Note: for ovious reasons, the prefix cannot be combined with a COMMAND OBJECT.
Basically, the same syntax can be use for
$options['value'],
$options['attributes'],
Complex Query Specification and
$options['idColumn'] because the private method
XML_Query2XML::_applyColumnStringToRecord() is used in all cases.
Let's start out with a very simple example. It will use
the column name as the XML element name for the first two columns but the
custom element name 'music_genre' for the column 'genre':
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'name',
'birth_year',
'music_genre' => 'genre'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artists>
<artist>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<music_genre>Soul</music_genre>
</artist>
<artist>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<music_genre>Soul</music_genre>
</artist>
<artist>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<music_genre>Country and Soul</music_genre>
</artist>
</favorite_artists>
The following example demonstrates the usage of all different types:
<?php
require_once 'XML/Query2XML.php';
require_once 'XML/Query2XML/Callback.php';
require_once 'MDB2.php';
class Utils
{
function trim($record, $columnName)
{
return trim($record[$columnName]); }
function getPublishedYearCentury($record)
{
return floor($record['published_year']/
100); }
}
class ToLowerCallback implements XML_Query2XML_Callback
{
private $_columnName = '';
public function __construct($columnName)
{
$this->_columnName = $columnName;
}
public function execute(array $record)
{
return strtolower($record[$this->_columnName]);
}
}
$dom = $query2xml->getXML(
"SELECT
*
FROM
sale,
store,
album
WHERE
sale.store_id = store.storeid
AND
sale.album_id = album.albumid
AND
sale.timestamp < '2005-06-01'",
array(
'rootTag' => 'sales',
'idColumn' => 'saleid',
'rowTag' => 'sale',
'elements' => array(
'saleid',
'sale_timestamp' => 'timestamp',
'static' => ':some static text',
'album_century' => '#Utils::getPublishedYearCentury()',
'album_title' => '?#Utils::trim(title)',
'album_comment' => new ToLowerCallback('comment'),
'storeid',
'store_building1' => '?&building_xmldata',
'store_building2' => '?=building_xmldata',
'store_building3' => '?^building_xmldata'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
- 'saleid': this is as simple as it can get. The value of the column saleid will be used for an element named saleid.
- 'sale_timestamp' => 'timestamp': here we want to place the value of the
column timestamp in an element named sale_timestamp; we therefore use sale_timestamp as the array key.
- 'static' => ':some static text': the STATIC TEXT (note the ":" prefix) "some static text" will be placed inside an element named static.
- 'now' => ':' . time(): here the static text is computed at run time; however it will be the same for all "now" elements.
- 'album_century' => '#Utils::getPublishedYearCentury()': here we use
a CALLBACK FUNCTION with a "#" prefix; the return value of Utils::getPublishedYearCentury() is used as the XML element value.
Note that the callback function will automatically be called with the current $record as the first argument.
- 'album_title' => '?#Utils::trim(title)': we also use a CALLBACK FUNCTION with a "#" prefix, but
this time we pass an additional string argument to our callback function by specifing it within the opening and
closing brace. Also, we use the CONDITIONAL prefix ? which means that the album_title element will only appear in
the generated XML data if Utils::trim() returned a non-empty string (to be precise a string that != "").
- 'album_comment' => new ToLowerCallback('comment'): here we use a COMMAND OBJECT implementing
the XML_Query2XML_Callback interface. This is the object oriented way to use callbacks! Note how we pass the
column name to the callback class constructor, so that it's execute() method will now what column to work on.
- 'storeid': plain an simple again
- 'store_building1' => '?&building_xmldata': here we use the XML UNSERIALIZATION prefix "&"
to transform the value of the building_xmldata column into a DOMDocument. Using the CONDITIONAL prefix ? means
that store_building1 will only appear if building_xmldata is non-empty (!= "" to be precise).
- 'store_building2' => '?=building_xmldata': CDATA SECTION prefix "=" is another way
incorporate XML data; the contents of the column building_xmldata will be surrounded by
"<![CDATA[" and "]]>". Using the CONDITIONAL prefix ? means
that store_building2 will only appear if building_xmldata is non-empty (!= "" to be precise).
- 'store_building3' => '?^building_xmldata': here we use the BASE64 ENCODING prefix "^"
to first base64-encode the contents of the building_xmldata column. We again use the CONDITIONAL prefix "?".
The resulting XML data looks like this:
<?xml version="1.0" encoding="UTF-8"?>
<sales>
<sale>
<saleid>1</saleid>
<sale_timestamp>2005-05-25 07:32:00</sale_timestamp>
<static>some static text</static>
<now>1187498966</now>
<album_century>19</album_century>
<album_title>New World Order</album_title>
<album_comment>the best ever!</album_comment>
<storeid>1</storeid>
<store_building1>
<building>
<floors>4</floors>
<elevators>2</elevators>
<square_meters>3200</square_meters>
</building>
</store_building1>
<store_building2>< ![CDATA[<building><floors>4</floors><elevators>2</elevators><square_meters>3200</square_meters></building>]] ></store_building2>
<store_building3>PGJ1aWxkaW5nPjxmbG9vcnM+NDwvZmxvb3JzPjxlbGV2YXRvcnM+MjwvZWxldmF0b3JzPjxzcXVhcmVfbWV0ZXJzPjMyMDA8L3NxdWFyZV9tZXRlcnM+PC9idWlsZGluZz4=</store_building3>
</sale>
<sale>
<saleid>11</saleid>
<sale_timestamp>2005-05-25 07:23:00</sale_timestamp>
<static>some static text</static>
<now>1187498966</now>
<album_century>19</album_century>
<album_title>Curtis</album_title>
<album_comment>that man's got somthin' to say</album_comment>
<storeid>2</storeid>
<store_building1>
<building>
<floors>2</floors>
<elevators>1</elevators>
<square_meters>400</square_meters>
</building>
</store_building1>
<store_building2>< ![CDATA[<building><floors>2</floors><elevators>1</elevators><square_meters>400</square_meters></building>]] ></store_building2>
<store_building3>PGJ1aWxkaW5nPjxmbG9vcnM+MjwvZmxvb3JzPjxlbGV2YXRvcnM+MTwvZWxldmF0b3JzPjxzcXVhcmVfbWV0ZXJzPjQwMDwvc3F1YXJlX21ldGVycz48L2J1aWxkaW5nPg==</store_building3>
</sale>
</sales>
Note: due to a bug in phpDocumentor I had to cheat a little bit in the above XML; as you might have noticed
there was a space between "<" and "![CDATA[".
Complex Element Specifications
A complex element specification consists of an array that can have all options
that can be present on the root level plus $options['sql']
and $options['sql_options'].
This allows for complete (and theoretically infinite) nesting. You will need to use it if the
child element should have attributes or child elements.
The following example is like the first one in
Simple Element Specifications
with one difference: the XML element 'name' should have the attribute 'type' set to the
static value 'full_name'. As attributes are not supported by simple elements specifications,
we have to use a complex element specification for the element 'name':
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'name' => array(
'value' => 'name',
'attributes' => array(
'type' => ':full_name'
)
),
'birth_year',
'music_genre' => 'genre'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artists>
<artist>
<name type="full_name">Curtis Mayfield</name>
<birth_year>1920</birth_year>
<music_genre>Soul</music_genre>
</artist>
<artist>
<name type="full_name">Isaac Hayes</name>
<birth_year>1942</birth_year>
<music_genre>Soul</music_genre>
</artist>
<artist>
<name type="full_name">Ray Charles</name>
<birth_year>1930</birth_year>
<music_genre>Country and Soul</music_genre>
</artist>
</favorite_artists>
Here is another little example:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist LEFT JOIN album ON album.artist_id = artist.artistid",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre',
'albums' => array(
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array('albumid', 'title', 'published_year')
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
<?xml version="1.0" encoding="UTF-8"?>
<music_library>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
<albums>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
</album>
<album>
<albumid>2</albumid>
<title>Curtis</title>
<published_year>1970</published_year>
</album>
</albums>
</artist>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
<albums>
<album>
<albumid>3</albumid>
<title>Shaft</title>
<published_year>1972</published_year>
</album>
</albums>
</artist>
<artist>
<artistid>3</artistid>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<birth_place>Mississippi</birth_place>
<genre>Country and Soul</genre>
<albums />
</artist>
</music_library>
As we want for every artist only a single tag we need to identify each artist by the primary
key of the table artist. Note that there is a second record for Curtis Mayfield (related to
the album Curtis), but we don't want something like
<artist>
<name>Curtis Mayfield</name>
<album>
<name>New World Order</name>
</album>
</artist>
<artist>
<name>Curtis Mayfield</name>
<album>
<name>Curits</name>
</album>
</artist>
but rather
<artist>
<name>Curtis Mayfield</name>
<albums>
<album>
<name>New World Order</name>
</album>
<albums>
<name>Curtis</name>
</albums>
</albums>
</artist>
This is achieved by telling XML_Query2XML which entity to focus on (on this level): the artist, as it
is identified by the artist table's primary key. Once XML_Query2XML get's to the second Curtis Mayfield
record, it can tell by the artistid 1 that an XML element was already created for this artist.
For a one more example and a detailed explanation of complex child elements that have
child elements themselves, see Case 02: LEFT OUTER JOIN.
For an advanced example, see Case 05: three LEFT OUTER JOINs.
Using the Asterisk Shortcut
The asterisk shortcut only works with Simple Element Specifications
(and Simple Attribute Specifications).
In some scenarios you will just want to use all columns found in the result set
for Simple Element Specifications.
This is where the asterisk shortcut can come in very handy. An element specification that
contains an asterisk (an "asterisk element specification") will be duplicated for each
column present in the result set ($record). The simplest way of using the asterisk shortcut is this:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'*'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artists>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
</artist>
<artist>
<artistid>3</artistid>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<birth_place>Mississippi</birth_place>
<genre>Country and Soul</genre>
</artist>
</favorite_artists>
This is because internally, the array
'elements' => array(
'*'
)
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre'
)
Think of the asterisk as a variable that will get replaced with each column name found in the result set:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'TAG_*' => '#padWithHyphens(*)'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
function padWithHyphens($record, $columnName)
{
return '--' . $record[$columnName] . '--';
}
?>
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artists>
<artist>
<TAG_artistid>--1--</TAG_artistid>
<TAG_name>--Curtis Mayfield--</TAG_name>
<TAG_birth_year>--1920--</TAG_birth_year>
<TAG_birth_place>--Chicago--</TAG_birth_place>
<TAG_genre>--Soul--</TAG_genre>
</artist>
<artist>
<TAG_artistid>--2--</TAG_artistid>
<TAG_name>--Isaac Hayes--</TAG_name>
<TAG_birth_year>--1942--</TAG_birth_year>
<TAG_birth_place>--Tennessee--</TAG_birth_place>
<TAG_genre>--Soul--</TAG_genre>
</artist>
<artist>
<TAG_artistid>--3--</TAG_artistid>
<TAG_name>--Ray Charles--</TAG_name>
<TAG_birth_year>--1930--</TAG_birth_year>
<TAG_birth_place>--Mississippi--</TAG_birth_place>
<TAG_genre>--Country and Soul--</TAG_genre>
</artist>
</favorite_artists>
You can also combine a simple element specification containing an asterisk shortcut with other (simple and complex) element specifications.
The additional element specifications will be treated as an exception to the general rule set up by the asterisk element specification.
The following code will produce a tag for each column in the result set containing the column's value. The only exeption is the column
"genre" which we want to be different: the value should be all uppercase:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'*' => '*',
'genre' => '#genre2uppercase()'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
function genre2uppercase($record)
{
}
?>
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artists>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>SOUL</genre>
</artist>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>SOUL</genre>
</artist>
<artist>
<artistid>3</artistid>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<birth_place>Mississippi</birth_place>
<genre>COUNTRY AND SOUL</genre>
</artist>
</favorite_artists>
This is because internally, the array
'elements' => array(
'*' => '*',
'genre' => '#genre2uppercase()'
)
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre' => '#genre2uppercase()'
)
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'*' => '*',
'genre' => array(
'value' => '#genre2uppercase()'
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
function genre2uppercase($record)
{
}
?>
If we wanted to include all columns in the XML output except "genre" we could use a little trick:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'*' => '*',
'genre' => '?:'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artists>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
</artist>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
</artist>
<artist>
<artistid>3</artistid>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<birth_place>Mississippi</birth_place>
</artist>
</favorite_artists>
The exact same result could of course also be achieved using the "condition" option:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'*' => '*',
'genre' => array(
'condition' => '#returnFalse()'
//this would also work: 'condition' => ':'
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
function returnFalse()
{
return false;
}
?>
Another example of how to use the asterisk shortcut can be found in
Case 07: Case 03 with Asterisk Shortcuts.
One final note on the asterisk shortcut: if you explicitly specify a tag name (an array element key) it has to contain an asterisk. The following code
would cause a XML_Query2XML_ConfigException to be thrown:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'tag' => '*'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
'elements' => array(
'tag' => '*'
)
'elements' => array(
'tag' => 'artistid',
'tag' => 'name',
'tag' => 'birth_year',
'tag' => 'birth_place',
'tag' => 'genre'
)
$options['idColumn']
In most cases this will be the name of the column by which a record is identified as unique, aka the
primary key. This is especially important within a
Complex Element Specification.
See there for an example. This option is obligatory at the root level! The idColumn specification can be
of the following types:
- COLUMN NAME: this is the default if not preceeded by ':' or '#'.
If the column does not exist, an XML_Query2XML_ConfigException will be thrown.
The current record (not the one of the parent level) will be used.
- STATIC TEXT with a : prefix: if the value is preceeded by a colon (':'), it is
interpreted as static text.
- CALLBACK FUNCTION with a # prefix: if the value is preceeded by a pound sign ('#'), it
is interpreted as a callback function. You can use a regular function (e.g. '#myFunction()') or a static
method (e.g. '#MyClass::myFunction()') - for how to use a non-static method, see the type COMMAND OBJECT.
The current record will be passed to the callback function
as an associative array. You can also pass additional string arguments to the callback function by specifing
them within the opening and closing brace; e.g. '#Utils::limit(12)' will result in Util::limit() being
called with the current record as the first and '12' as the second argument. If you do not want
to pass additional arguments to the callback function, the opening and closing brace are optional.
- COMMAND OBJECT (object): If you want to use a non-static method as a
callback function, you can do so by specifying the value as an instance of a class that
implements the XML_Query2XML_Callback interface. This implementation of the
command pattern gives you all the
flexibility. Note: you have to require_once 'XML/Query2XML/Callback.php'
before using the XML_Query2XML_Callback interface. The return value of a COMMAND OBJECT's
execute() method is treated exactly the same as the return value of a CALLBACK FUNCTION.
- FALSE (boolean): Only use this if you don't have a primary key
(which is a very bad idea) or you have a very simple tasks at hand like retrieving all
records from a table. Using the value FALSE will make XML_Query2XML treat every record
as unique. WARNING: it is considered very bad practice to use a value of
FALSE if you have a way to specify your primar key. This is because your code might
change over time and having your primary key specified will just make your more stable.
For a legitimate use of the value FALSE for the option idColumn, please see
Using dynamic $options to dump all data of your database.
The same syntax (with the additional '?' prefix but without the boolean value FALSE) can
be use for
$options['value'],
Simple Attribute Specifications,
Complex Query Specification and
Simple Element Specifications because the private method
XML_Query2XML::_applyColumnStringToRecord() is used in all cases.
For example and further discussion of $options['idColumn']
please see Case 02: LEFT OUTER JOIN.
Handling Multi-Column Primary Keys
Sometimes your primary key will consist of multiple columns. For example, this might
be the case when you implement a many-to-many relationship using an intersection table.
But as you know by now,
$options['idColumn'] has to evaluate to one unique ID for every record.
Depending on the type of the primary key columns you will want to choose a different strategy to
compute that unique ID for every record. To begin with, you have to choose whether
you want to compute that unique ID within the database or using PHP. To do it within the
database you will have to define an alias using the "... AS alias_name" syntax. Using
PHP you have to use a callback function to generate the ID. When generating the ID, you
again have different options.
If your primary key columns are of a numeric type, you can
If your primary key columns are of a character type (e.g. CHAR, VARCHAR) you
have to come up with something else. Before you read on, I strongly urge
you to reconsider your choice for the primary key (does it really meet the
requirements of minimality and stability, i.e. is immutable?).
SECURITY WARNING:
Do not simply concatenate your character type columns (with or without a separator).
The following example shows why:
record1: column1='a_b' column2='c'
record2: column1='a' column2='b_c'
When using the separator '_' both records would have an ID of 'a_b_c'.
A malicious attacker could use your separator within
one of the column values to force ID collisions, which potentially
lead to an exploitable security vulnerability. Great care should therefore
be taken when choosing a separator - and relying on its confidentiality is
not a good strategy. What you might do is to use a separator that is longer
than the maximum character length of your primary key columns. But this only
makes sense if that maximum is rather low. For example, if you have two CHAR(2)
columns, it is reasonable to use the separator '---' which is three characters long.
Another thing one might think of is to use a hash function like sha1() or md5().
But that's not really an option as it would really kill the performance of your
application.
The most bullet proof solution to the problem of generating a unique ID from
two character type columns is to use a callback function that works with
an array. The following function can be used as a callback whenever
you need to generate an ID from two character type columns.
<?php
/**Returns a unique ID base on the values stored in
* $record[$columnName1] and $record[$columnName2].
*
* @param array $record An associative array.
* @param string $columnName1 The name of the first column.
* @param string $columnName2 The name of the second column.
* @return int The ID.
*/
function generateIdFromMultiKeyPK($record, $columnName1, $columnName2)
{
static $ids = array();
static $idCounter = 0;
$column1 = $record[$columnName1];
$column2 = $record[$columnName2];
if (!isset($ids[$column1])) {
$ids[$column1] = array();
}
if (!isset($ids[$column1][$column2])) {
$ids[$column1][$column2] = $idCounter++;
}
return $ids[$column1][$column2];
}
?>
All you have to do is to specify $options['idColumn'] as:
'#generateIdFromMultiKeyPK(name_of_column1, name_of_column2)'
Remember: $record is automatically passed as the first argument
to the callback function.
$options['attributes']
This option is an array that holds columns to include in the XML data as
attributes. Simple
and complex attribute
specifications are supported.
If you want to add attributes to the root element (i.e. the first child of the DOMDocument instance returned by getXML()),
please see Modifying the returned DOMDocument instance.
Simple Attribute Specifications
It works like
Simple Element Specifications:
the column names are the array values. By default the column's value
will be put into an attribute named after the column. If you're
unhappy with the default you can specify an other attribute name by using
it as the array key. As documented for Simple Element Specifications
the prefixes "?", "#", "^" and ":" or a COMMAND OBJECT can be used. Only the UNSERIALIZATION prefix & and the CDATA SECTION prefix ^ which are valid for
a Simple Element Specification
cannot be used for a Simple Attribute Specification.
The follwing example will use
the column name as the attribute name for the first two columns but the
custom attribute name 'music_genre' for the column 'genre':
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(),
'attributes' => array(
'name',
'birth_year',
'music_genre' => 'genre'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artists>
<artist name="Curtis Mayfield" birth_year="1920" music_genre="Soul"/>
<artist name="Isaac Hayes" birth_year="1942" music_genre="Soul"/>
<artist name="Ray Charles" birth_year="1930" music_genre="Country and Soul"/>
</favorite_artists>
Complex Attribute Specifications
A complex attribute specification consists of an array that must contain
- $options['value']: the attribute's value
(note: you cannot use the UNSERIALIZATION prefix & or the the CDATA SECTION prefix ^ for an attribute specification)
and optionally can contain
The array key used to store the complex attribute specification is always used as the attribute's name.
Unlike Complex Element Specifications
complex attribute specifications cannot be nested for obvious reasons. Complex attribute specifications
should only be used for the following reasons:
- the attribute is only to be included under a condition that cannot be expressed using the '?' prefix
within a simple attribute specification
- additional data is needed from the database
In all other cases Simple Attribute Specifications
should be used as they will make your code run faster.
To add a "bornBefore1940" attribute only to those artists that were born before 1940 we could write:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'attributes' => array(
'name',
'birth_year',
'bornBefore1940' => array(
'value' => ':true',
'condition' => '#lessThan(birth_year, 1940)'
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
function lessThan($record, $columnName, $num)
{
return $record[$columnName] < $num;
}
?>
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artists>
<artist birth_year="1920" bornBefore1940="true" name="Curtis Mayfield" />
<artist birth_year="1942" name="Isaac Hayes" />
<artist birth_year="1930" bornBefore1940="true" name="Ray Charles" />
</favorite_artists>
In the next example we want a "firstAlbumTitle" attribute for each artist.
For the purpose of the example we will not use a single left outer join but a complex attribute specification with the "sql" option.
As retrieving more than one record for a single attribute makes no sense
$options['sql_options']['single_record'] is always automatically set to true when fetching records
for attributes.
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'attributes' => array(
'name',
'birth_year',
'firstAlbumTitle' => array(
'value' => 'title',
'sql' => array(
'data' => array(
'artistid'
),
'query' => "SELECT * FROM album WHERE artist_id = ? ORDER BY published_year"
)
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artists>
<artist birth_year="1920" firstAlbumTitle="Curtis" name="Curtis Mayfield" />
<artist birth_year="1942" firstAlbumTitle="Shaft" name="Isaac Hayes" />
<artist birth_year="1930" name="Ray Charles" />
</favorite_artists>
As you can see, the firstAlbumTitle attribute is missing for Ray Charles.
This is because he does not have any albums in our test database and processing
the "value" option without any records just makes no sense.
In the last example I'd like to demonstrate the use of $options['sql_options'] within
a complex attribute specification. As stated before, $options['sql_options']['single_record']
is always automatically set to true - no matter what you assign to it.
This time, we want a "firstAlbum" attribute that has a value of "TITLE (GENRE)" - remember that
"genre" is a colum of the artist table while "title" is a column of the album table.
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'attributes' => array(
'name',
'birth_year',
'firstAlbum' => array(
'value' => '#combineTitleAndGenre()',
'sql' => array(
'data' => array(
'artistid'
),
'query' => "SELECT * FROM album WHERE artist_id = ? ORDER BY published_year"
),
'sql_options' => array(
'merge_selective' => array('genre')
)
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
function combineTitleAndGenre($record)
{
return $record['title'] . ' (' . $record['genre'] . ')';
}
?>
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artists>
<artist name="Curtis Mayfield" birth_year="1920" firstAlbum="Curtis (Soul)"/>
<artist name="Isaac Hayes" birth_year="1942" firstAlbum="Shaft (Soul)"/>
<artist name="Ray Charles" birth_year="1930"/>
</favorite_artists>
Using the Asterisk Shortcut
The asterisk shortcut only works with Simple Attribute Specifications
(and Simple Element Specifications).
Everything said about
Using the Asterisk Shortcut with simple element specifications
applies here to!
The simplest example of using the asterisk shortcut with the attributes option is as follows:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'attributes' => array(
'*'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artists>
<artist artistid="1" birth_place="Chicago" birth_year="1920" genre="Soul" name="Curtis Mayfield" />
<artist artistid="2" birth_place="Tennessee" birth_year="1942" genre="Soul" name="Isaac Hayes" />
<artist artistid="3" birth_place="Mississippi" birth_year="1930" genre="Country and Soul" name="Ray Charles" />
</favorite_artists>
$options['rowTag']
The name of the tag that encloses each record. The default is 'row'.
Here goes an example of 'rowTag' being used at the root level:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'name',
'birth_year',
'genre'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artists>
<artist>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<genre>Soul</genre>
</artist>
<artist>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<genre>Soul</genre>
</artist>
<artist>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<genre>Country and Soul</genre>
</artist>
</favorite_artists>
Now let's have a look at a more advanced example:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist
LEFT JOIN album ON album.artist_id = artist.artistid",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre',
'albums' => array(
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array(
'albumid',
'title',
'published_year',
'comment'
)
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
In some situations, 'rowTag' can be omitted all together:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'name' => array(
'value' => 'name',
'attributes' => array(
'type' => ':full_name'
)
),
'birth_year',
'music_genre' => 'genre'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
$options['dynamicRowTag']
Use this option if you want the name of an XML element determined at run time (e.g. you want to pull the
XML element name from the database). Note: if this option is present,
$options['rowTag'] will be ignored.
What you can assign to $options['dynamicRowTag'] is very similar as what you can use for
$options['value']
or a Simple Element Specification.
$options['dynamicRowTag'] can be of the following types:
- COLUMN NAME: this is the default if not preceeded by ':' or '#'.
If the column does not exist, an XML_Query2XML_ConfigException will be thrown.
- STATIC TEXT with a : prefix: if the value is preceeded by a colon (':'), it is
interpreted as static text.
- CALLBACK FUNCTION with a # prefix: if the value is preceeded by a pound sign ('#'), it
is interpreted as a callback function. You can use a regular function (e.g. '#myFunction()') or a static
method (e.g. '#MyClass::myFunction()') - for how to use a non-static method, see the type COMMAND OBJECT.
The current record will be passed to the callback function
as an associative array. You can also pass additional string arguments to the callback function by specifing
them within the opening and closing brace; e.g. '#Utils::limit(12)' will result in Util::limit() being
called with the current record as the first and '12' as the second argument. If you do not want
to pass additional arguments to the callback function, the opening and closing brace are optional.
The callback function's return value obviously has to be a string that is a valid XML element name.
- COMMAND OBJECT (object): If you want to use a non-static method as a
callback function, you can do so by specifying the value as an instance of a class that
implements the XML_Query2XML_Callback interface. This implementation of the
command pattern gives you all the
flexibility. The disadvantage ist that you cannot use the XML UNSERIALIZATION prefix
or the CONDITIONAL prefix. Note: you have to require_once 'XML/Query2XML/Callback.php'
before using the XML_Query2XML_Callback interface. The return value of a COMMAND OBJECT's
execute() method is treated exactly the same as the return value of a CALLBACK FUNCTION.
Let's have a look at a straightforward example: we want our customer's email addresses inside a tag named
after the customer's first name, e.g. <John>john.doe@example.com</John>:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM customer",
array(
'rootTag' => 'customers',
'idColumn' => 'customerid',
'rowTag' => 'customer',
'elements' => array(
'customerid',
'name_and_email' => array(
'dynamicRowTag' => 'first_name',
'value' => 'email'
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
<?xml version="1.0" encoding="UTF-8"?>
<customers>
<customer>
<customerid>1</customerid>
<Jane>jane.doe@example.com</Jane>
</customer>
<customer>
<customerid>2</customerid>
<John>john.doe@example.com</John>
</customer>
<customer>
<customerid>3</customerid>
<Susan>susan.green@example.com</Susan>
</customer>
<customer>
<customerid>4</customerid>
<Victoria>victory.alt@example.com</Victoria>
</customer>
<customer>
<customerid>5</customerid>
<Will>will.wippy@example.com</Will>
</customer>
<customer>
<customerid>6</customerid>
<Tim>tim.raw@example.com</Tim>
</customer>
<customer>
<customerid>7</customerid>
<Nick>nick.fallow@example.com</Nick>
</customer>
<customer>
<customerid>8</customerid>
<Ed>ed.burton@example.com</Ed>
</customer>
<customer>
<customerid>9</customerid>
<Jack>jack.woo@example.com</Jack>
</customer>
<customer>
<customerid>10</customerid>
<Maria>maria.gonzales@example.com</Maria>
</customer>
</customers>
$options['rootTag']
The name of the root tag that encloses all other tags. On the root level, the default is 'root'.
On all other levels omitting the rootTag option means that the row tags will not be enclosed by
a root tag but will directly be placed inside the parent tag.
Here goes an example of 'rootTag' being used at the root level:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'name',
'birth_year',
'genre'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artists>
<artist>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<genre>Soul</genre>
</artist>
<artist>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<genre>Soul</genre>
</artist>
<artist>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<genre>Country and Soul</genre>
</artist>
</favorite_artists>
Here goes an example with the rootTag being used at a lower level:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist
LEFT JOIN album ON album.artist_id = artist.artistid",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre',
'albums' => array(
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array(
'albumid',
'title',
'published_year',
'comment'
)
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
As shown in Case 04: Case 03 with custom tag names, attributes, merge_selective and more is is also possible to assign
an empty string to the rootTag option or to omit it at all. In our case this results in
all the album tags not being surrounded by a single 'albums' tag but being directly placed
inside the 'artist' tag:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist
LEFT JOIN album ON album.artist_id = artist.artistid",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre',
'albums' => array(
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array(
'albumid',
'title',
'published_year',
'comment'
)
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
<?xml version="1.0" encoding="UTF-8"?>
<music_library>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ever!</comment>
</album>
<album>
<albumid>2</albumid>
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man's got somthin' to say</comment>
</album>
</artist>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
<album>
<albumid>3</albumid>
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
</album>
</artist>
<artist>
<artistid>3</artistid>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<birth_place>Mississippi</birth_place>
<genre>Country and Soul</genre>
</artist>
</music_library>
Note however that a hidden child element is used as a container
to ensure the order of the generated XML elements. Internally all elements with a name
that starts with '__' are hidden. An explicit definition of the hidden complex element would look
like this:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist
LEFT JOIN album ON album.artist_id = artist.artistid",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre',
'albums' => array(
'rootTag' => '__albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array(
'albumid',
'title',
'published_year',
'comment'
)
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
$options['value']
The value of an XML element's child text node. The specification can be of the following types:
- COLUMN NAME: this is the default if not preceeded by ':' or '#'.
If the column does not exist, an XML_Query2XML_ConfigException will be thrown.
- STATIC TEXT with a : prefix: if the value is preceeded by a colon (':'), it is
interpreted as static text.
- CALLBACK FUNCTION with a # prefix: if the value is preceeded by a pound sign ('#'), it
is interpreted as a callback function. You can use a regular function (e.g. '#myFunction()') or a static
method (e.g. '#MyClass::myFunction()') - for how to use a non-static method, see the type COMMAND OBJECT.
The current record will be passed to the callback function
as an associative array. You can also pass additional string arguments to the callback function by specifing
them within the opening and closing brace; e.g. '#Utils::limit(12)' will result in Util::limit() being
called with the current record as the first and '12' as the second argument. If you do not want
to pass additional arguments to the callback function, the opening and closing brace are optional.
The callback function's return value will be converted to a string and used as the child text node
if it is anything but an object or an array. If you do return an object or an array from a callback
function it has to be an instance of DOMNode or
an array of DOMNode instances. Please see Integrating other XML data sources for examples
and further details. If an instances of any other class is returned, a XML_Query2XML_XMLException will be thrown.
- COMMAND OBJECT (object): If you want to use a non-static method as a
callback function, you can do so by specifying the value as an instance of a class that
implements the XML_Query2XML_Callback interface. This implementation of the
command pattern gives you all the
flexibility. The disadvantage ist that you cannot use the XML UNSERIALIZATION prefix
or the CONDITIONAL prefix. Note: you have to require_once 'XML/Query2XML/Callback.php'
before using the XML_Query2XML_Callback interface. The return value of a COMMAND OBJECT's
execute() method is treated exactly the same as the return value of a CALLBACK FUNCTION.
There are four more prefixes available that can be used in conjunction with all the prifixes described above:
- XML UNSERIALIZATION prefix &: the ampersand (&) prefix allows you to
automatically unserialize string data, i.e. transform a string into a
DOMDocument. DOMDocument's
loadXML() method will be
used for this purpose. You can combine all three types with this prefix: '&COLUMN_NAME',
'&#function()' or '&:<name>John</name>' will all work. You can even use the CONDITIONAL prefix
which has to preceed all other prefixes. If the data cannot be unserialized i.e.
DOMDocument::loadXML()
returns false, a XML_Query2XML_XMLException will be thrown. Please see
Integrating other XML data sources for examples and further
details.
- BASE64 ENCODING prefix ^: if the specification starts with a carrat sign ('^'),
the element value will be passed to base64_encode().
The BASE64 ENCODING prefix can be used with all the prefixes described above (just put the BASE64 ENCODING prefix first):
e.g. '^#', '^:' or '^COLUMN_NAME'.
- CDATA SECTION prefix =: if the specification starts with an equal sign ('='),
the element value will be enclosed in a CDATA section. A CDATA section starts with
"<![CDATA[" and ends with "]]>".
The CDATA SECTION prefix can be used with all the prefixes described above (just put the CDATA SECTION prefix first):
e.g. '=#', '=:', '=COLUMN_NAME' or '=^'.
- CONDITIONAL prefix ?: if the specification starts with a question mark ('?'),
the whole element will be skipped if the value equals (==) an empty string. The CONDITIONAL prefix
can be combined with all types described above: if you do this you have to write the CONDITIONAL
prefix first e.g. '?#', '?:', '?&', '?=', '?^', or '?COLUMN_NAME'.
Note: for ovious reasons, the XML UNSERIALIZATION prefix and the CONDITIONAL prefix cannot be
combined with a COMMAND OBJECT.
Basically, the same syntax can be use for
Simple Element Specifications,
Simple Attribute Specifications,
Complex Query Specification and
$options['idColumn'] because the private method
XML_Query2XML::_applyColumnStringToRecord() is used in all cases.
The following example demonstrates the usage of some of the types (for a full demonstration of all types
see the second example under Simple Element Specifications).
The comment element will be skipped if its value == "".
Same holds true for the genre element which uses the trim'ed version of the value stored in the genre column. The comment
tag has an attribute named type with a static value of "short text". The published_century element gets the century
calculated using floor and has the attribute digitCount with a static value of 2.
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
album al,
artist ar
WHERE
al.artist_id = ar.artistid",
array(
'rootTag' => 'albums',
'idColumn' => 'albumid',
'rowTag' => 'album',
'elements' => array(
'albumid',
'title',
'published_year',
'published_century' => array(
'value' => "#Utils::getPublishedYearCentury()",
'attributes' => array(
'digitCount' => ':2'
)
),
'comment' => array(
'value' => '?comment',
'attributes' => array(
'type' => ':short text'
)
),
'genre' => array(
'value' => "?#Utils::trimGenre()"
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
class Utils
{
function trimGenre($record)
{
return trim($record['genre']); }
function getPublishedYearCentury($record)
{
return floor($record['published_year']/
100); }
}
?>
<?xml version="1.0" encoding="UTF-8"?>
<albums>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
<published_century digitCount="2">19</published_century>
<comment type="short text">the best ever!</comment>
<genre>Soul</genre>
</album>
<album>
<albumid>2</albumid>
<title>Curtis</title>
<published_year>1970</published_year>
<published_century digitCount="2">19</published_century>
<comment type="short text">that man's got somthin' to say</comment>
<genre>Soul</genre>
</album>
<album>
<albumid>3</albumid>
<title>Shaft</title>
<published_year>1972</published_year>
<published_century digitCount="2">19</published_century>
<comment type="short text">he's the man</comment>
<genre>Soul</genre>
</album>
</albums>
$options['condition']
This option allows you to specify a condition for the element to be included.
The string assigned to the condition option can be of the following types:
- COLUMN NAME: this is the default if not preceeded by ':' or '#'.
If the column does not exist, an XML_Query2XML_ConfigException will be thrown.
Remember that the string '0' or '' will both evaluate to false which
means that the element would be skipped. Note: in most cases you will be much
better off changing your WHERE clause than using this type of condition.
- STATIC TEXT with a : prefix: if the value is preceeded by a colon (':'), it is
interpreted as static text. Remember that the string '0' or '' will both evaluate to false which
means that the element would be skipped.
- CALLBACK FUNCTION with a # prefix: if the value is preceeded by a pound sign ('#'), it
is interpreted as a callback function. You can use a regular function (e.g. '#myFunction()') or a static
method (e.g. '#MyClass::myFunction()') - for how to use a non-static method, see the type COMMAND OBJECT.
The current record will be passed to the callback function
as an associative array. You can also pass additional string arguments to the callback function by specifing
them within the opening and closing brace; e.g. '#Utils::limit(12)' will result in Util::limit() being
called with the current record as the first and '12' as the second argument. If you do not want
to pass additional arguments to the callback function, the opening and closing brace are optional.
- COMMAND OBJECT (object): If you want to use a non-static method as a
callback function, you can do so by specifying the value as an instance of a class that
implements the XML_Query2XML_Callback interface. This implementation of the
command pattern gives you all the
flexibility. Note: you have to require_once 'XML/Query2XML/Callback.php'
before using the XML_Query2XML_Callback interface. The return value of a COMMAND OBJECT's
execute() method is treated exactly the same as the return value of a CALLBACK FUNCTION.
This option provides a similar function as the "?" prefix for column specifications - see
Simple Element Specifications,
Simple Attribute Specifications and $options['value'].
The difference is that $options['condition'] is more powerful: you can call any external function you like
to determin whether the element shall be included.
Here goes a little example:
<?php
if (isset($_REQUEST['includeCondition'])) {
$includeCondition = ($_REQUEST['includeCondition'] == '1');
} else {
$includeCondition = false;
}
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom =& $query2xml->getXML(
"SELECT
*
FROM
artist
LEFT JOIN album ON album.artist_id = artist.artistid",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre',
'albums' => array(
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'condition' => '#isSpecialPublishedYear()',
'elements' => array(
'albumid',
'title',
'published_year',
'comment' => array(
'value' => 'comment',
'condition' => ':' . ($includeCondition ? '1' : '0')
)
)
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
/**Returns whether $year is 1970 or 1972.
*/
function isSpecialPublishedYear($record)
{
//do some highly complex calculations ...
return $record['published_year'] == 1970 || $record['published_year'] == 1972;
}
?>
<?xml version="1.0" encoding="UTF-8"?>
<music_library>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
<albums>
<album>
<albumid>2</albumid>
<title>Curtis</title>
<published_year>1970</published_year>
</album>
</albums>
</artist>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
<albums>
<album>
<albumid>3</albumid>
<title>Shaft</title>
<published_year>1972</published_year>
</album>
</albums>
</artist>
<artist>
<artistid>3</artistid>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<birth_place>Mississippi</birth_place>
<genre>Country and Soul</genre>
<albums />
</artist>
</music_library>
Note that (if present) $options['sql'] will
get processed *before* evaluating the condition. This allows you to wirte code
like the following:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist",
array(
'rootTag' => 'artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'artistid',
'name',
'albums' => array(
'idColumn' => 'albumid',
'sql' => array(
'data' => array(
'artistid'
),
'query' => "SELECT * FROM album WHERE artist_id = ?",
),
'condition' => '#isGT1980()',
'elements' => array(
'title',
'published_year'
)
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
function isGT1980($record)
{
return $record['published_year'] > 1980;
}
?>
<?xml version="1.0" encoding="UTF-8"?>
<artists>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<albums>
<title>New World Order</title>
<published_year>1990</published_year>
</albums>
</artist>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
</artist>
<artist>
<artistid>3</artistid>
<name>Ray Charles</name>
</artist>
</artists>
$options['sql']
Note: This option is driver-specific. The following discussion is limited
to the database-related drivers.
This and $options['sql_options'] are the only options
that can only be present within
Complex Element Specifications.
If given at the root level, it would be just ignored. $options['sql'] allows you to split
up one huge JOIN into multiple smaller queries. You might want (or have) to do this in
several scenarios:
- Your RDBMS has a maximum number of fields it can return in a single query and you've reached it.
- You are short on memory: let's say your big JOIN returns 100 fields and you have 10 000 records.
It might turn out that the memory consumption is lower if you split up the single big JOIN into
multiple quieres that have smaller result sets. As all the data won't be in memory at once,
it might even run faster.
- You are too lazy to think about how to best join these 8 tables :)
You will definitively want to do some Profiling and Performance Tuning before deciding whether
or not to split up one big JOIN into multiple smaller JOINs.
There are two ways of specifying $options['sql']:
- Simple Query Specification: uses the query() method provided by the database abstraction layer (PDO/MDB2/DB/ADOdb) - use it with care
- Complex Query Specification: uses
the prepare() and execute() methods provided by the database abstraction layer and
can therefore prevent SQL injection and is also faster in most scenarios
Simple Query Specification
Since v0.8.0 a simple query specifications are purely static strings (in most cases
you will want to use a Complex Query Specification):
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist
WHERE
artistid = 1",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre',
'albums' => array(
'sql' => 'SELECT * FROM album WHERE artist_id = 1',
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array(
'albumid',
'title',
'published_year',
'comment'
)
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
To understand how $options['sql'] really works, some knowledge of XML_Query2XML's internals might be helpful:
XML_Query2XML::getXML() calls the private method XML_Query2XML::_getNestedXMLRecord() for
every record retrieved from the database using the SQL statement passed to getXML() as first
argument. XML_Query2XML::_getNestedXMLRecord() will then process the current record according
to the settings specified in $options. The processing of all
Complex Element Specifications
is handed off to the private method XML_Query2XML::_processComplexElementSpecification().
XML_Query2XML::_processComplexElementSpecification() in turn will call the private method
XML_Query2XML::_applySqlOptionsToRecord() to interpret $options['sql'] and $options['sql_options'].
XML_Query2XML::_processComplexElementSpecification() will then call again XML_Query2XML::_getNestedXMLRecord() for
every record retrieved using the query specified in the 'sql' option.
Complex Query Specification
A Complex Query Specification uses the database abstraction layer's prepare() and execute() methods
and therefore prevents SQL injection and is also faster
than a Simple Query Specification in most scenarios.
It can consist of multiple parts (only $options['sql']['query'] is mandatory):
- $options['sql']['query']: the SQL query as a string that contains a placeholder
for each element of $options['sql']['data'].
- $options['sql']['driver']: allows you to use a different XML_Query2XML_Driver
for this complex query than the one passed to
XML_Query2XML::factory(). Please see
Using Multiple Drivers for details. $options['sql']['driver'] is optional.
- $options['sql']['limit']: allows you to limit the number of records returned from the
query. It has to be a numeric value. Please note that a value of 0 (or '0') is equivalent to not
setting $options['sql']['limit'] at all. $options['sql']['limit'] and $options['sql']['offset'] are
only interpreted by the drivers for PEAR MDB2 and PEAR DB. All other drivers simply ignore these two
options.
- $options['sql']['offset']: allows you to set the number of the first record
to retrieve. This has to be a numeric value. The default is 0. Please note that this option will
be ignored unless $options['sql']['limit'] is set. $options['sql']['offset'] and
$options['sql']['limit'] are only interpreted by the drivers for PEAR MDB2 and PEAR DB.
All other drivers simply ignore these two options.
- $options['sql']['data']: an indexed array of values. This is optional.
The specification can be of the following types:
- COLUMN NAME: this is the default if not preceeded by ':' or '#'.
If the column does not exist, an XML_Query2XML_ConfigException will be thrown. Note
that the parent record will be used! This is quite logic as this SQL statement has not been executed yet :)
- STATIC TEXT with a : prefix: if the value is preceeded by a colon (':'), it is
interpreted as static text.
- CALLBACK FUNCTION with a # prefix: if the value is preceeded by a pound sign ('#'), it
is interpreted as a callback function. You can use a regular function (e.g. '#myFunction()') or a static
method (e.g. '#MyClass::myFunction()') - for how to use a non-static method, see the type COMMAND OBJECT.
The current record will be passed to the callback function
as an associative array. You can also pass additional string arguments to the callback function by specifing
them within the opening and closing brace; e.g. '#Utils::limit(12)' will result in Util::limit() being
called with the current record as the first and '12' as the second argument. If you do not want
to pass additional arguments to the callback function, the opening and closing brace are optional.
- COMMAND OBJECT (object): If you want to use a non-static method as a
callback function, you can do so by specifying the value as an instance of a class that
implements the XML_Query2XML_Callback interface. This implementation of the
command pattern gives you all the
flexibility. Note: you have to require_once 'XML/Query2XML/Callback.php'
before using the XML_Query2XML_Callback interface. The return value of a COMMAND OBJECT's
execute() method is treated exactly the same as the return value of a CALLBACK FUNCTION.
The same syntax (with the additional '?' prefix) can be use for
Simple Element Specifications,
$options['value'],
Simple Attribute Specifications and
$options['idColumn'] because the private method
XML_Query2XML::_applyColumnStringToRecord() is used in all cases.
Note: $options['sql']['data'] is optional!
Here is a simple example similar to Case 03: Two SELECTs instead of a LEFT OUTER JOIN:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre',
'albums' => array(
'sql' => array(
'data' => array(
'artistid'
),
'query' => "SELECT * FROM album WHERE artist_id = ?"
),
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array(
'albumid',
'title',
'published_year',
'comment'
)
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
$options['sql_options']
This allows you to specify how $options['sql'] is
handled. $options['sql_options'] is an associative array that can have the following fileds:
Per default all options are set to the boolean value false.
$options['sql_options']['cached']
Since 1.5.0RC1 Caching is deactivated by default.
If caching is activated the result of a query is stored in the private associative array
XML_Query2XML::$_recordCache using the SQL query string as key. If the exact same
query needs to be executed a second time, its results can be retrieved from cache.
Before setting $options['sql_options']['cached'] to true, do some
Profiling and Performance Tuning. As documented in
XML_Query2XML::getProfile() the CACHED column in the profile output will
show 'true!' if caching is performed without being necessary.
Caching only makes sense, if you have to run exactly the same query multiple times.
$options['sql_options']['single_record']
Use this option to make sure that the SQL query you specified in
$options['sql'] returns only a single record.
This option is in fact of limited use. Do not use it to fetch only the first record
from a large result set. (SQL is your friend: use a better WHERE clause!)
$options['sql_options']['merge']
By default no merging is done so that less memory is used. This means that the data of the record
present on the parent level will not be available at this level. Only the data returned by
$options['sql'] will be available (and therefore
use up memory). If you also need the data of the record present on the parent level the two arrays
have to be merged using array_merge(). If
$options['sql'] returned multiple records, each of them
has to be merged with the one of the parent level separatly:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist",
array(
'rootTag' => 'MUSIC_LIBRARY',
'rowTag' => 'ARTIST',
'idColumn' => 'artistid',
'elements' => array(
'NAME' => 'name',
'BIRTH_YEAR' => 'birth_year',
'GENRE' => 'genre',
'albums' => array(
'sql' => array(
'data' => array('artistid'),
'query' => "SELECT * FROM album WHERE artist_id = ?"
),
'sql_options' => array(
'merge' => true
),
'rootTag' => '',
'rowTag' => 'ALBUM',
'idColumn' => 'albumid',
'elements' => array(
'TITLE' => 'title',
'PUBLISHED_YEAR' => 'published_year',
'COMMENT' => 'comment',
'GENRE' => 'genre'
)
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
$options['sql_options']['merge_selective']
As a full merge with the parent record might severly affect the performance, the sql option
merge_selective allows you to only merge the current record with specific columns of the
parent record. Just place the names of all columns of the parent record you want to be
available in the current record in an array and assign it to the merge_selective option.
Here goes an example:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist",
array(
'rootTag' => 'MUSIC_LIBRARY',
'rowTag' => 'ARTIST',
'idColumn' => 'artistid',
'elements' => array(
'NAME' => 'name',
'BIRTH_YEAR' => 'birth_year',
'GENRE' => 'genre',
'albums' => array(
'sql' => array(
'data' => array('artistid'),
'query' => "SELECT * FROM album WHERE artist_id = ?"
),
'sql_options' => array(
'merge_selective' => array('genre')
),
'rootTag' => '',
'rowTag' => 'ALBUM',
'idColumn' => 'albumid',
'elements' => array(
'TITLE' => 'title',
'PUBLISHED_YEAR' => 'published_year',
'COMMENT' => 'comment',
'GENRE' => 'genre'
)
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
$options['sql_options']['merge_master']
If (selective) merging is performed, it might become important which record overwrites the data of the other.
As soon as both result sets have a column with the same name, there is a confilict that has to
be resolved. By default, the record of the parent level is the master and overwrites the
record(s) returned by $options['sql']. If you want
the new records to overwrite the record of the parent level, set
$options['sql_options']['merge_master'] to true. Note that this option only has an effect if
$options['sql_options']['merge'] is set to true or
$options['sql_options']['merge_selective'] is used.
$options['mapper']
This option allows you to specifiy a function for mapping SQL identifiers to XML names.
Whenever you use a Simple Element Specification
or a Simple Attribute Specification
only with a column name and without a tag/attribute name, the specified column name will be used for the
tag/attribute name. Please note that mapping is also performed when the
Using the Asterisk Shortcut is used.
Per default $options['mapper'] is set to false which means that no special mapping is used.
$options['mapper'] can have one of the following formats:
- 'CLASS::STATIC_METHOD': this syntax allows you to use a static method
for mapping:
'mapper' => 'MyMapper::map'
- array('CLASS', 'STATIC_METHOD'): this syntax also allows you to use a static
method for mapping:
'mapper' => array('MyMapper', 'map')
- array($instance, 'METHOD'): this syntax allows you to use a non-static
method for mapping:
'mapper' => array($myMap, 'map')
- 'FUNCTION': this syntax allows you to use a regular function
for mapping:
'mapper' => 'myUppercaseMapper'
- false: use the boolean value false (or any other value that == false) to
deactivate any special mapping:
Remember that the mapping only applies to
Simple Element Specifications
and Simple Attribute Specifications
that do not explicitly have a tag/attribute name or those that have a tag/attribute name that contains
an asterisk shortcut.
The following example will also show that a mapper defined at the root level is also used at all lower levels
(unless it gets overwritten, see
Using multiple mappers):
<?php
class SomeMapper
{
public function map($str)
{
//do something with $str
return $str;
}
}
require_once 'XML/Query2XML.php';
require_once 'XML/Query2XML/ISO9075Mapper.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML( //
"SELECT * FROM artist", //
array( //
'rootTag' => 'favorite_artists', //no mapping
'idColumn' => 'artistid', //nothing to map
'rowTag' => 'artist', //no mapping
'mapper' => 'SomeMapper::map', //
'elements' => array( //
'artistid', //mapping
'NAME' => 'name', //no mapping as the tag name is specified
'*', //mapping
'TAG_*' => '*', //does a mapping too!
'albums' => array( //nothing to map
'sql' => array( //
'data' => array( //
'artistid' //nothing to map
), //
'query' => 'SELECT * FROM album WHERE artist_id = ?' //
), //
'rootTag' => 'albums', //no mapping
'rowTag' => 'album', //no mapping
'idColumn' => 'albumid',//nothing to map
'elements' => array( //
'albumid', //mapping using the mapper specified at the root level
'title', //mapping using the mapper specified at the root level
'published_year', //mapping using the mapper specified at the root level
'comment' //mapping using the mapper specified at the root level
) //
) //
), //
'attributes' => array( //
'artistid', //mapping
'NAME' => 'name', //no mapping as the tag name is specified
'*', //mapping
'TAG_*' => '*' //does a mapping too!
) //
) //
); //
header('Content-Type: application/xml'); // print $dom->saveXML(); //
?>
Mapping SQL identifiers to XML names in accordance with ISO/IEC 9075-14:2005
The package XML_Query2XML also implements the Final Committee Draft for ISO/IEC 9075-14:2005,
section "9.1 Mapping SQL <identifier>s to XML Names". ISO/IEC 9075-14:2005 is available
online at http://www.sqlx.org/SQL-XML-documents/5FCD-14-XML-2004-07.pdf.
A lot of characters are legal in SQL identifiers but cannot be used within
XML names. To begin with, SQL identifiers can contain any Unicode character
while XML names are limited to a certain set of characters. E.g the
SQL identifier "<21yrs in age" obviously is not a valid XML name.
'#', '{', and '}' are also not allowed. Fully escaped SQL identifiers
also must not contain a column (':') or start with "xml" (in any case
combination). Illegal characters are mapped to a string of the form
_xUUUU_ where UUUU is the Unicode value of the character.
The following is a table of example mappings:
+----------------+------------------------+------------------------------------+
| SQL-Identifier | Fully escaped XML name | Comment |
+----------------+------------------------+------------------------------------+
| dept:id | dept_x003A_id | ":" is illegal |
| xml_name | _x0078_ml_name | must not start with [Xx][Mm][Ll] |
| XML_name | _x0058_ML_name | must not start with [Xx][Mm][Ll] |
| hire date | hire_x0020_date | space is illegal too |
| Works@home | Works_x0040_home | "@" is illegal |
| file_xls | file_x005F_xls | "_" gets mapped if followed by "x" |
| FIRST_NAME | FIRST_NAME | no problem here |
+----------------+------------------------+------------------------------------+
The ISO 9075-mapping does produce some overhead which might not be needed in
a lot of situations. Therefore it is not the default mapper. In most cases
it will be sufficient to validate your XML schema once using tools like the free
XMLSpy Home Edition.
To use the ISO 9075-mapper that comes with XML_Query2XML you have to:
Here goes an example:
<?php
require_once 'XML/Query2XML.php';
require_once 'XML/Query2XML/ISO9075Mapper.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'mapper' => 'XML_Query2XML_ISO9075Mapper::map',
'elements' => array(
'*'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
Building your own mappers
There are cases when you will want the tag and attribute names to be somehow different from
the column names. Let's say you want to use the column names as tag and attribute names
but make them all uppercase. Certainly you could write code like this:
<?php
require_once 'XML/Query2XML.php';
require_once 'XML/Query2XML/ISO9075Mapper.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'elements' => array(
'NAME' => 'name',
'BIRTH_YEAR' => 'birth_year',
'BIRTH_PLACE' => 'birth_place',
'GENRE' => 'genre',
),
'attributes' => array(
'ARTISTID' => 'artistid'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
<?php
class UppercaseMapper
{
public function map($str)
{
}
}
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'mapper' => 'UppercaseMapper::map',
'elements' => array(
'name',
'birth_year',
'birth_place',
'genre',
),
'attributes' => array(
'artistid'
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
<?xml version="1.0" encoding="UTF-8"?>
<favorite_artists>
<artist ARTISTID="1">
<NAME>Curtis Mayfield</NAME>
<BIRTH_YEAR>1920</BIRTH_YEAR>
<BIRTH_PLACE>Chicago</BIRTH_PLACE>
<GENRE>Soul</GENRE>
</artist>
<artist ARTISTID="2">
<NAME>Isaac Hayes</NAME>
<BIRTH_YEAR>1942</BIRTH_YEAR>
<BIRTH_PLACE>Tennessee</BIRTH_PLACE>
<GENRE>Soul</GENRE>
</artist>
<artist ARTISTID="3">
<NAME>Ray Charles</NAME>
<BIRTH_YEAR>1930</BIRTH_YEAR>
<BIRTH_PLACE>Mississippi</BIRTH_PLACE>
<GENRE>Country and Soul</GENRE>
</artist>
</favorite_artists>
Using multiple mappers
Let's say we want to force all tags corresponding to columns of the artist table to be uppercase
and all tags corresponding to columns of the album table to be lowercase. This can be done
using two mappers:
<?php
class MyMappers
{
public function uppercaseMapper($str)
{
}
public function lowercaseMapper($str)
{
}
}
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'mapper' => 'MyMappers::uppercaseMapper',
'elements' => array(
'*',
'albums' => array(
'sql' => array(
'data' => array(
'artistid'
),
'query' => 'SELECT * FROM album WHERE artist_id = ?'
),
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'mapper' => 'MyMappers::lowercaseMapper',
'elements' => array(
'*',
'artist_id' => '?:'
)
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
<?php
class MyMappers
{
public function uppercaseMapper($str)
{
}
}
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'mapper' => 'MyMappers::uppercaseMapper',
'elements' => array(
'*',
'albums' => array(
'sql' => array(
'data' => array(
'artistid'
),
'query' => 'SELECT * FROM album WHERE artist_id = ?'
),
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'mapper' => false,
'elements' => array(
'*',
'artist_id' => '?:'
)
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
<?xml version="1.0" encoding="UTF-8"?>
<music_library>
<artist>
<ARTISTID>1</ARTISTID>
<NAME>Curtis Mayfield</NAME>
<BIRTH_YEAR>1920</BIRTH_YEAR>
<BIRTH_PLACE>Chicago</BIRTH_PLACE>
<GENRE>Soul</GENRE>
<albums>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ever!</comment>
</album>
<album>
<albumid>2</albumid>
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man's got somthin' to say</comment>
</album>
</albums>
</artist>
<artist>
<ARTISTID>2</ARTISTID>
<NAME>Isaac Hayes</NAME>
<BIRTH_YEAR>1942</BIRTH_YEAR>
<BIRTH_PLACE>Tennessee</BIRTH_PLACE>
<GENRE>Soul</GENRE>
<albums>
<album>
<albumid>3</albumid>
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
</album>
</albums>
</artist>
<artist>
<ARTISTID>3</ARTISTID>
<NAME>Ray Charles</NAME>
<BIRTH_YEAR>1930</BIRTH_YEAR>
<BIRTH_PLACE>Mississippi</BIRTH_PLACE>
<GENRE>Country and Soul</GENRE>
<albums />
</artist>
</music_library>
$options['encoder']
This option allows you to specifiy a function/method that performs the
XML encoding for node and attribute values. Per default it is assumed
that all data is in ISO-8859-1 (Latin-1) and will be encoded to UTF-8 using
mb_convert_encoding() or if not available
using utf8_encode().
For some introduction to XML encoding please see
http://www.w3schools.com/xml/xml_encoding.asp and
http://www.opentag.com/xfaq_enc.htm.
Note: I highly recommend to use UTF-8 for XML if you don't have a compelling reason
to use an other encoding standard.
The default encoding mechanism (ISO-8859-1 to UTF-8) will be just fine in most cases
but sometimes your data might already be in in UTF-8 or you might not want
your XML to be UTF-8 encoded at all.
Please see XML encoding for how to change the encoding
standard used in the XML declaration.
$options['encoder'] can have one of the following formats:
- 'CLASS::STATIC_METHOD': this syntax allows you to use a static method
for encoding:
'encoder' => 'MyEncoder::encode'
- array('CLASS', 'STATIC_METHOD'): this syntax also allows you to use a static
method for encoding:
'encoder' => array('MyEncoder', 'encode')
- array($instance, 'METHOD'): this syntax allows you to use a non-static
method for encoding:
'encoder' => array($myEncoder, 'encode')
- 'FUNCTION': this syntax allows you to use a regular function
for encoding:
'encoder' => 'myUTF8toISO88591Encoder'
- false: use the boolean value false to deactivate encoding:
- null: use NULL to reset encoding to the built-in default encoding.
This default assumes that all data is in ISO-8859-1 (Latin-1) and will encode it to
UTF-8 using mb_convert_encoding()
or if not available using utf8_encode().
One thing you should keep in mind when writing your own encoding (wrapper) functions is
that the encoder will only be called if the current record has a string value for
that column; i.e. the encoder will not be called if the column value is NULL.
The following example will show that an encoder defined at the root level is also used
at all lower levels (unless it gets overwritten, see
Using multiple encoders):
<?php
class SomeEncoder
{
public function encode($str)
{
//do something with $str
return $str;
}
}
require_once 'XML/Query2XML.php';
require_once 'XML/Query2XML/ISO9075Mapper.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT * FROM artist",
array(
'rootTag' => 'favorite_artists',
'idColumn' => 'artistid',
'rowTag' => 'artist',
'encoder' => 'SomeEncoder::encode', /* we define an encoder at the root level */
'elements' => array(
'artistid', // encoding will be
'name', // performed on these
'albums' => array(
'sql' => array(
'data' => array(
'artistid'
),
'query' => 'SELECT * FROM album WHERE artist_id = ?'
),
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array(
'albumid', // encoder setting is affective on all lower
'title' // levels
),
'attributes' => array(
'comment' // note: encoding is also performed for attributes
)
)
)
)
);
header('Content-Type: application/xml'); $dom->formatOutput = true;
print $dom->saveXML();
?>
ISO-8859-1 to UTF-8 encoding (default)
This is what will automatically be performed if you do not use $options['encoder']
at all. This is because most databases use ISO-8859-1 (aka Latin-1) by default.
As previously stated, XML_Query2XML will use
mb_convert_encoding()
or if that is not available utf8_encode() for the
actual encoding.
If you have set $options['encoder'] on the root level but wish to switch back to
the default on a lower level all you have to do is to use the NULL value:
UTF-8 to ISO-8859-1 encoding
If your data is in UTF-8 but you would like your XML to be in ISO-8859-1 (Latin-1),
you can use utf8_decode():
'encoder' => 'utf8_decode'
function utf8ToLatin1($str)
{
//hint: mb_convert_encoding (str, to_encoding, from_encoding)
return mb_convert_encoding($str, 'iso-8859-1', 'UTF-8');
}
'encoder' => 'utf8ToLatin1'
Disabling encoding
If you data already is in the character set you wish to use for the XML, all you
have to do is to disable the encoding by using a boolean value of false:
Using multiple encoders
It might happen to you that some of your data sources are in one character set
while others are in another. This means that you need different encoding procedures
to convert them all to the same character set you wish to use for the XML (usually UTF-8).
In the first example we will assume that all columns of the table artist are in
ISO-8859-1 (Latin-1) while all columns of the table album are in UTF-8.
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist
LEFT JOIN album ON album.artist_id = artist.artistid",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array( // all columns of the table artist are in
'artistid', // ISO-8859-1; the default conversion therefore
'name', // is just fine
'birth_year',
'birth_place',
'genre',
'albums' => array(
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'encoder' => false, // the columns of the album table already are in UTF-8;
'elements' => array( // we therefore have to disable encoding
'albumid',
'title',
'published_year',
'comment'
)
)
)
)
);
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
?>
For our second example, let's assume that the following columns use the following
character sets:
+----------------------------------------+
| Column | Character Set |
+----------------------------------------+
| artist.name | ISO-8859-1 (Latin-1) |
| artist.genre | UTF-8 |
| album.title | UTF-16 |
| album.comment | Windows-1252 |
+----------------+-----------------------+
As our XML output shall be in UTF-8 we have to use multiple encoders on a
per-column basis:
<?php
function latin1ToUTF8($str)
{
// alternatively we could have used
// return mb_convert_encoding($str, 'UTF-8', 'iso-8859-1');
}
function utf16ToUTF8($str)
{
return mb_convert_encoding($str, 'UTF-8', 'UTF-16');
}
function windows1252ToUTF8($str)
{
return mb_convert_encoding($str, 'UTF-8', 'windows-1252');
}
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist
LEFT JOIN album ON album.artist_id = artist.artistid",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'artistid',
'name', // name is in ISO-8859-1 and therefore will be handled by the default conversion
'birth_year',
'birth_place',
'genre' => array(
'value' => 'genre',
'encoder' => false // genre already is in UTF-8
),
'albums' => array(
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array(
'albumid',
'title' => array(
'value' => 'title',
'encoder' => 'utf16ToUTF8' // title is in UTF-16 and therefore needs
), // special treatment
'published_year'
),
'attributes' => array(
'comment' => array(
'value' => 'comment',
'encoder' => 'windows1252ToUTF8' // comment is in Windows-1252
)
)
)
)
)
);
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
?>
Modifying the returned DOMDocument instance
XML_Query2XML::getXML() returns an instance of
DOMDocument.
I recommend that you do some reading about
PHP5's DOM extension.
Let's see how we can add attributes to the root element
(i.e. the first child of the DOMDocument instance returned by getXML()):
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$doc = $query2xml->getXML(
"SELECT
*
FROM
artist",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre'
)
)
);
$root = $doc->firstChild;
$root->setAttribute('copyright', 'John Doe 2007');
header('Content-Type: application/xml'); $doc->formatOutput = true;
print $doc->saveXML();
?>
This adds an attribute named 'copyright' with a value of 'John Doe 2007'
to the root element <music_library>:
<?xml version="1.0" encoding="UTF-8"?>
<music_library copyright="John Doe 2007">
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
</artist>
<artist>
<artistid>3</artistid>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<birth_place>Mississippi</birth_place>
<genre>Country and Soul</genre>
</artist>
</music_library>
Final Notes on XML_Query2XML::getXML()
You might also want to read the API docs: XML_Query2XML.
Case Studies
Now let's have a look at some of XML_Query2XML's features in action. We'll start out with simple cases.
We'll turn to rather complex ones as we proceed. All cases are included in the source distribution.
Each case has its own directory cases/caseXX and will consist of 5 files (Case 01 contains only
the first 2):
- caseXX.php: generates the XML data.
- caseXX.xml: the generated the XML data saved to a file.
- caseXX_debug.php: does debugging and profiling and generates
caseXX.log and caseXX.profile.
- caseXX.log: the generated debug log
- caseXX.profile: the generated profile
The SQL DDL used in all cases can be found in tests/Query2XML_Tests.sql and
SQL DDL used in all examples.
Case 01: simple SELECT with getFlatXML
Case 01 will teach you:
Case 01 is as simple as it can get. We use XML_Query2XML::getFlatXML() to generate
flat XML data.
case01.php
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getFlatXML(
"SELECT
*
FROM
artist",
'music_library',
'artist');
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
?>
case01.xml
The result looks like this:
<?xml version="1.0" encoding="UTF-8"?>
<music_library>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
</artist>
<artist>
<artistid>3</artistid>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<birth_place>Mississippi</birth_place>
<genre>Country and Soul</genre>
</artist>
</music_library>
Case 02: LEFT OUTER JOIN
Case 02 will teach you:
Once you have to deal with LEFT JOINs and similar "complex" SQL queries, you have to use
XML_Query2XML::getXML(). The challenge is to get the $options array
(getXML's second argument) right:
case02.php
case02.php looks like this:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist
LEFT JOIN album ON album.artist_id = artist.artistid",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre',
'albums' => array(
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array(
'albumid',
'title',
'published_year',
'comment'
)
)
)
)
);
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
?>
case02.xml
The resulting XML data looks like this:
<?xml version="1.0" encoding="UTF-8"?>
<music_library>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
<albums>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ever!</comment>
</album>
<album>
<albumid>2</albumid>
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man's got somthin' to say</comment>
</album>
</albums>
</artist>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
<albums>
<album>
<albumid>3</albumid>
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
</album>
</albums>
</artist>
<artist>
<artistid>3</artistid>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<birth_place>Mississippi</birth_place>
<genre>Country and Soul</genre>
<albums />
</artist>
</music_library>
case02_debug.php
XML_Query2XML::getXML() allows us to debug and to profile the operation.
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
require_once 'Log.php';
$debugLogger = Log::factory('file', 'case02.log', 'Query2XML');
$query2xml->enableDebugLog($debugLogger);
$query2xml->startProfiling();
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist
LEFT JOIN album ON album.artist_id = artist.artistid",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre',
'albums' => array(
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array(
'albumid',
'title',
'published_year',
'comment'
)
)
)
)
);
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
require_once 'File.php';
$fp = new File();
$fp->write('case02.profile', $query2xml->getProfile(), FILE_MODE_WRITE);
?>
case02.log
The format of a debug log file is documented at Logging and Debugging XML_Query2XML.
Our debug log shows that the query runs once.
Feb 11 16:10:36 Query2XML [info] QUERY: SELECT
*
FROM
artist
LEFT JOIN album ON album.artist_id = artist.artistid
Feb 11 16:10:36 Query2XML [info] DONE
case02.profile
Profiling is essential for performance tuning. The format of the output is documented under
XML_Query2XML::getProfile(). Our profile looks like this:
FROM_DB FROM_CACHE CACHED AVG_DURATION DURATION_SUM SQL
1 0 false 0.0056409835 0.0056409835 SELECT
*
FROM
artist
LEFT JOIN album ON album.artist_id = artist.artistid
TOTAL_DURATION: 0.06843900680542
DB_DURATION: 0.015194892883301
The value "false" in the CACHED column tells us that no caching was performed. As we can see
in the FROM_DB column, the query ran once.
Case 03: Two SELECTs instead of a LEFT OUTER JOIN
Case 03 will teach you:
When your query is getting bigger and bigger (say, 6 or more JOINs) you might want to (or have
to, if the maximum number of fields your RDBMS will return has been reached) split up the big join into multiple
smaller joins. Here we will just do exactly the same as in
Case 02: LEFT OUTER JOIN, but with two separate SELECT queries.
case03.php
case03.php looks like this:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre',
'albums' => array(
'sql' => array(
'data' => array(
'artistid'
),
'query' => 'SELECT * FROM album WHERE artist_id = ?'
),
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array(
'albumid',
'title',
'published_year',
'comment'
)
)
)
)
);
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
?>
- 'sql': ['sql']['query'] will be executed for every record retrieved with
the first SELECT query. In our case, we want all albums for the current artist record.
We use a Complex Query Specification here:
['sql']['data'] contains an array of values that will ultimately be passed to the database abstraction
layer's execute() method. As we do not prefix 'artistid' with anything it is interpreted as a column
name (of the parent record) - which is just what we want. This completely prevents SQL injection attacks.
case03.xml
The resulting XML data looks exactly like case02.xml:
<?xml version="1.0" encoding="UTF-8"?>
<music_library>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
<albums>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ever!</comment>
</album>
<album>
<albumid>2</albumid>
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man's got somthin' to say</comment>
</album>
</albums>
</artist>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
<albums>
<album>
<albumid>3</albumid>
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
</album>
</albums>
</artist>
<artist>
<artistid>3</artistid>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<birth_place>Mississippi</birth_place>
<genre>Country and Soul</genre>
<albums />
</artist>
</music_library>
case03_debug.php
case03_debug.php is similar to case02_debug.php:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
require_once 'Log.php';
$debugLogger = Log::factory('file', 'case03.log', 'Query2XML');
$query2xml->enableDebugLog($debugLogger);
$query2xml->startProfiling();
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre',
'albums' => array(
'sql' => array(
'data' => array(
'artistid'
),
'query' => 'SELECT * FROM album WHERE artist_id = ?'
),
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array(
'albumid',
'title',
'published_year',
'comment'
)
)
)
)
);
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
require_once 'File.php';
$fp = new File();
$fp->write('case03.profile', $query2xml->getProfile(), FILE_MODE_WRITE);
?>
case03.log
The format of a debug log file is documented at Logging and Debugging XML_Query2XML.
Our debug log now contains 4 queries:
Apr 18 19:00:20 Query2XML [info] QUERY: SELECT
*
FROM
artist
ORDER BY
artistid
Apr 18 19:00:20 Query2XML [info] DONE
Apr 18 19:00:20 Query2XML [info] QUERY: SELECT * FROM album WHERE artist_id = ?; DATA:1
Apr 18 19:00:20 Query2XML [info] DONE
Apr 18 19:00:20 Query2XML [info] QUERY: SELECT * FROM album WHERE artist_id = ?; DATA:2
Apr 18 19:00:20 Query2XML [info] DONE
Apr 18 19:00:20 Query2XML [info] QUERY: SELECT * FROM album WHERE artist_id = ?; DATA:3
Apr 18 19:00:20 Query2XML [info] DONE
The debug log shows what we expected: the first SELECT over the artist table runs once
and the SELECT over the album table runs three times (once for every record found in
the artist table). As the log shows no 'CACHING' entries we also know that no cashing
was performed ($options['sql_options']['cached']
was not set to true).
case03.profile
Profiling is essential for performance tuning. The format of the output is documented under
XML_Query2XML::getProfile(). Our profile looks like this:
FROM_DB FROM_CACHE CACHED AVG_DURATION DURATION_SUM SQL
1 0 false 0.0030851364 0.0030851364 SELECT
*
FROM
artist
3 0 false 0.0035093625 0.0105280876 SELECT * FROM album WHERE artist_id = ?
TOTAL_DURATION: 0.090610980987549
DB_DURATION: 0.024358034133911
If you compare our DB_DURATION value to the one in
case02.profile you will see that the single LEFT JOIN
was faster than the four separate queries.
Case 04: Case 03 with custom tag names, attributes, merge_selective and more
Case 04 will teach you:
- How to use alternative tag names.
- How to use callbacks with the '#' prefix.
- How to define static node and attribute values using the ':' prefix.
- How to prevent the creation of a root tag, using $options['rootTag'].
This is very much like Case 03: Two SELECTs instead of a LEFT OUTER JOIN, but with a demonstration
of some splecial features.
In contrast to Case 03 we want:
- all tag names should be uppercase
- an additional child tag for ARTIST: BIRTH_YEAR_TWO_DIGIT that will contain
only the last two digets of BIRTH_YERAR
- the ARTIST tag should have two attributes: ARTISTID and MAINTAINER set to the static value
'Lukas Feiler'.
- the ALBUM tags should not be contained in an ALBUMS tag but should be directly within the
ARTIST tag, e.g.
<artist>
...
<album>...</album>
<album>...</album>
</artist>
instead of
<artist>
...
<album>
<album>...</album>
<album>...</album>
</albums>
</artist>
- the ALBUM tag should have one attribute: ALBUMID
- the ALBUM tag should have an additional child tag: GENRE; note that this is a column of
the table artist!
case04.php
case04.php looks like this:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist",
array(
'rootTag' => 'MUSIC_LIBRARY',
'rowTag' => 'ARTIST',
'idColumn' => 'artistid',
'elements' => array(
'NAME' => 'name',
'BIRTH_YEAR' => 'birth_year',
'BIRTH_YEAR_TWO_DIGIT' => "#firstTwoChars()",
'BIRTH_PLACE' => 'birth_place',
'GENRE' => 'genre',
'albums' => array(
'sql' => array(
'data' => array(
'artistid'
),
'query' => 'SELECT * FROM album WHERE artist_id = ?'
),
'sql_options' => array(
'merge_selective' => array('genre')
),
'rootTag' => '',
'rowTag' => 'ALBUM',
'idColumn' => 'albumid',
'elements' => array(
'TITLE' => 'title',
'PUBLISHED_YEAR' => 'published_year',
'COMMENT' => 'comment',
'GENRE' => 'genre'
),
'attributes' => array(
'ALBUMID' => 'albumid'
)
)
),
'attributes' => array(
'ARTISTID' => 'artistid',
'MAINTAINER' => ':Lukas Feiler'
)
)
);
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
function firstTwoChars($record)
{
return substr($record['birth_year'], 2); }
?>
- as we wanted all tag names uppercased, all elements were specified like
'TAG_NAME' => 'column_name'
This is because XML_Query2XML will use the array key as the tag name if it is not
numeric.
- BIRTH_YEAR_TWO_DIGIT was specified as
'BIRTH_YEAR_TWO_DIGIT' => "#firstTwoChars()",
The prefix '#' tells XML_Query2XML that the following string is a function to call.
The current record is passed as argument to that function. firstTwoChars in our case
returns the first two characters of the string stored in $record['birth_year'].
- the ARTIST tag now has two attributes: they are specified in an array using
the 'attribute' option.
Both use a Simple Attribute Specifications.
The ARTISTID attribute simply uses the column name 'artistid'. In the MAINTAINER attribute
we specify a static value. This is done by prefixing it by a colon (':'). Without the
colon, XML_Query2XML would treat it as a column name.
- the ALBUM tags are now not contained in an ALBUMS tag anymore but directly within the
ARTIST tag; this is done by setting 'rootTag' to an empty string. Alternatively we just could
have omitted the rootTag option.
- ALBUM's new attribute ALBUMID is specified using
the 'attribute' option.
- ALBUM's new child tag GENRE contains the value of a column of the table artist.
If we had used the sql default options we would have seen a
XML_Query2XML_ConfigException with the following message:
[elements][albums][elements][GENRE]: The column "genre" was not found in the result set.
This is because the result of the first SQL query is not available at this level. As far as
this level is concerned, it got
overwritten with the result of our second query. But as we need both to be present, we
selectively merger them using array_merge().
This is achieved by setting
the sql_option 'merge_selective'
to an array that contains
all columns of the parent record that should also be available on the current level.
As we do not have any confilicting column names, we just leave
the sql_option 'merge_master'
set to false which means that the results of the parent level's query is the 'master', i.e. overwrite
the results from the query on this level.
case04.xml
The resulting XML data looks like this:
<?xml version="1.0" encoding="UTF-8"?>
<MUSIC_LIBRARY>
<ARTIST ARTISTID="1" MAINTAINER="Lukas Feiler">
<NAME>Curtis Mayfield</NAME>
<BIRTH_YEAR>1920</BIRTH_YEAR>
<BIRTH_YEAR_TWO_DIGIT>20</BIRTH_YEAR_TWO_DIGIT>
<BIRTH_PLACE>Chicago</BIRTH_PLACE>
<GENRE>Soul</GENRE>
<ALBUM ALBUMID="1">
<TITLE>New World Order</TITLE>
<PUBLISHED_YEAR>1990</PUBLISHED_YEAR>
<COMMENT>the best ever!</COMMENT>
<GENRE>Soul</GENRE>
</ALBUM>
<ALBUM ALBUMID="2">
<TITLE>Curtis</TITLE>
<PUBLISHED_YEAR>1970</PUBLISHED_YEAR>
<COMMENT>that man's got somthin' to say</COMMENT>
<GENRE>Soul</GENRE>
</ALBUM>
</ARTIST>
<ARTIST ARTISTID="2" MAINTAINER="Lukas Feiler">
<NAME>Isaac Hayes</NAME>
<BIRTH_YEAR>1942</BIRTH_YEAR>
<BIRTH_YEAR_TWO_DIGIT>42</BIRTH_YEAR_TWO_DIGIT>
<BIRTH_PLACE>Tennessee</BIRTH_PLACE>
<GENRE>Soul</GENRE>
<ALBUM ALBUMID="3">
<TITLE>Shaft</TITLE>
<PUBLISHED_YEAR>1972</PUBLISHED_YEAR>
<COMMENT>he's the man</COMMENT>
<GENRE>Soul</GENRE>
</ALBUM>
</ARTIST>
<ARTIST ARTISTID="3" MAINTAINER="Lukas Feiler">
<NAME>Ray Charles</NAME>
<BIRTH_YEAR>1930</BIRTH_YEAR>
<BIRTH_YEAR_TWO_DIGIT>30</BIRTH_YEAR_TWO_DIGIT>
<BIRTH_PLACE>Mississippi</BIRTH_PLACE>
<GENRE>Country and Soul</GENRE>
</ARTIST>
</MUSIC_LIBRARY>
case04_debug.php
case04_debug.php reveals nothing new compared to
case03_debug.php but it's included for
completeness.
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
require_once 'Log.php';
$debugLogger = Log::factory('file', 'case04.log', 'Query2XML');
$query2xml->enableDebugLog($debugLogger);
$query2xml->startProfiling();
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist",
array(
'rootTag' => 'MUSIC_LIBRARY',
'rowTag' => 'ARTIST',
'idColumn' => 'artistid',
'elements' => array(
'NAME' => 'name',
'BIRTH_YEAR' => 'birth_year',
'BIRTH_YEAR_TWO_DIGIT' => "#firstTwoChars()",
'BIRTH_PLACE' => 'birth_place',
'GENRE' => 'genre',
'albums' => array(
'sql' => array(
'data' => array(
'artistid'
),
'query' => 'SELECT * FROM album WHERE artist_id = ?'
),
'sql_options' => array(
'merge_selective' => array('genre')
),
'rootTag' => '',
'rowTag' => 'ALBUM',
'idColumn' => 'albumid',
'elements' => array(
'TITLE' => 'title',
'PUBLISHED_YEAR' => 'published_year',
'COMMENT' => 'comment',
'GENRE' => 'genre'
),
'attributes' => array(
'ALBUMID' => 'albumid'
)
)
),
'attributes' => array(
'ARTISTID' => 'artistid',
'MAINTAINER' => ':Lukas Feiler'
)
)
);
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
require_once 'File.php';
$fp = new File();
$fp->write('case04.profile', $query2xml->getProfile(), FILE_MODE_WRITE);
function firstTwoChars($record)
{
return substr($record['birth_year'], 2); }
?>
case04.log
The format of a debug log file is documented at Logging and Debugging XML_Query2XML.
Our debug log now contains 4 queries and is exactly the same as
case03.log:
Apr 18 19:01:25 Query2XML [info] QUERY: SELECT
*
FROM
artist
ORDER BY
artistid
Apr 18 19:01:25 Query2XML [info] DONE
Apr 18 19:01:25 Query2XML [info] QUERY: SELECT * FROM album WHERE artist_id = ?; DATA:1
Apr 18 19:01:25 Query2XML [info] DONE
Apr 18 19:01:25 Query2XML [info] QUERY: SELECT * FROM album WHERE artist_id = ?; DATA:2
Apr 18 19:01:25 Query2XML [info] DONE
Apr 18 19:01:25 Query2XML [info] QUERY: SELECT * FROM album WHERE artist_id = ?; DATA:3
Apr 18 19:01:25 Query2XML [info] DONE
case04.profile
Profiling is essential for performance tuning. The format of the output is documented under
XML_Query2XML::getProfile(). Our profile looks exactly like
case03.profile:
FROM_DB FROM_CACHE CACHED AVG_DURATION DURATION_SUM SQL
1 0 false 0.0034000873 0.0034000873 SELECT
*
FROM
artist
3 0 false 0.0035278797 0.0105836391 SELECT * FROM album WHERE artist_id = ?
TOTAL_DURATION: 0.081415891647339
DB_DURATION: 0.026465892791748
Case 05: three LEFT OUTER JOINs
Case 05 will teach you:
Case 05 is a demonstration of complex element specifications.
case05.php
case05.php looks like this:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
customer c
LEFT JOIN sale s ON c.customerid = s.customer_id
LEFT JOIN album al ON s.album_id = al.albumid
LEFT JOIN artist ar ON al.artist_id = ar.artistid",
array(
'rootTag' => 'music_store',
'rowTag' => 'customer',
'idColumn' => 'customerid',
'elements' => array(
'customerid',
'first_name',
'last_name',
'email',
'sales' => array(
'rootTag' => 'sales',
'rowTag' => 'sale',
'idColumn' => 'saleid',
'elements' => array(
'saleid',
'timestamp',
'date' => '#Callbacks::getFirstWord()',
'time' => '#Callbacks::getSecondWord()',
'album' => array(
'rootTag' => '',
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array(
'albumid',
'title',
'published_year',
'comment',
'artist' => array(
'rootTag' => '',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre'
) //artist elements
) //artist array
) //album elements
) //album array
) //sales elements
) //sales array
) //root elements
) //root
); //getXML method call
$root = $dom->firstChild;
$root->setAttribute('date_generated', date("Y-m-d\TH:i:s", 1124801570));
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
class Callbacks
{
function getFirstWord($record)
{
return substr($record['timestamp'], 0, strpos($record['timestamp'], ' ')); }
function getSecondWord($record)
{
return substr($record['timestamp'], strpos($record['timestamp'], ' ') +
1); }
}
?>
case05.xml
The resulting XML data looks like this:
<?xml version="1.0" encoding="UTF-8"?>
<music_store date_generated="2005-08-23T14:52:50">
<customer>
<customerid>1</customerid>
<first_name>Jane</first_name>
<last_name>Doe</last_name>
<email>jane.doe@example.com</email>
<sales>
<sale>
<saleid>1</saleid>
<timestamp>2005-05-25 16:32:00</timestamp>
<date>2005-05-25</date>
<time>16:32:00</time>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ever!</comment>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale>
<saleid>11</saleid>
<timestamp>2005-05-25 16:23:00</timestamp>
<date>2005-05-25</date>
<time>16:23:00</time>
<album>
<albumid>2</albumid>
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man's got somthin' to say</comment>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</customer>
<customer>
<customerid>2</customerid>
<first_name>John</first_name>
<last_name>Doe</last_name>
<email>john.doe@example.com</email>
<sales>
<sale>
<saleid>2</saleid>
<timestamp>2005-06-05 12:56:00</timestamp>
<date>2005-06-05</date>
<time>12:56:00</time>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ever!</comment>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale>
<saleid>16</saleid>
<timestamp>2005-06-05 12:56:12</timestamp>
<date>2005-06-05</date>
<time>12:56:12</time>
<album>
<albumid>3</albumid>
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</customer>
<customer>
<customerid>3</customerid>
<first_name>Susan</first_name>
<last_name>Green</last_name>
<email>susan.green@example.com</email>
<sales>
<sale>
<saleid>3</saleid>
<timestamp>2005-07-10 11:03:00</timestamp>
<date>2005-07-10</date>
<time>11:03:00</time>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ever!</comment>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale>
<saleid>12</saleid>
<timestamp>2005-07-10 11:56:00</timestamp>
<date>2005-07-10</date>
<time>11:56:00</time>
<album>
<albumid>2</albumid>
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man's got somthin' to say</comment>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</customer>
<customer>
<customerid>4</customerid>
<first_name>Victoria</first_name>
<last_name>Alt</last_name>
<email>victory.alt@example.com</email>
<sales>
<sale>
<saleid>4</saleid>
<timestamp>2005-07-10 10:03:00</timestamp>
<date>2005-07-10</date>
<time>10:03:00</time>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ever!</comment>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale>
<saleid>17</saleid>
<timestamp>2005-07-10 10:03:32</timestamp>
<date>2005-07-10</date>
<time>10:03:32</time>
<album>
<albumid>3</albumid>
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</customer>
<customer>
<customerid>5</customerid>
<first_name>Will</first_name>
<last_name>Rippy</last_name>
<email>will.wippy@example.com</email>
<sales>
<sale>
<saleid>5</saleid>
<timestamp>2005-07-10 13:03:00</timestamp>
<date>2005-07-10</date>
<time>13:03:00</time>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ever!</comment>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale>
<saleid>13</saleid>
<timestamp>2005-07-10 13:12:00</timestamp>
<date>2005-07-10</date>
<time>13:12:00</time>
<album>
<albumid>2</albumid>
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man's got somthin' to say</comment>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</customer>
<customer>
<customerid>6</customerid>
<first_name>Tim</first_name>
<last_name>Raw</last_name>
<email>tim.raw@example.com</email>
<sales>
<sale>
<saleid>6</saleid>
<timestamp>2005-07-10 14:03:00</timestamp>
<date>2005-07-10</date>
<time>14:03:00</time>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ever!</comment>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale>
<saleid>18</saleid>
<timestamp>2005-07-10 14:03:52</timestamp>
<date>2005-07-10</date>
<time>14:03:52</time>
<album>
<albumid>3</albumid>
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</customer>
<customer>
<customerid>7</customerid>
<first_name>Nick</first_name>
<last_name>Fallow</last_name>
<email>nick.fallow@example.com</email>
<sales>
<sale>
<saleid>7</saleid>
<timestamp>2005-07-10 15:03:00</timestamp>
<date>2005-07-10</date>
<time>15:03:00</time>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ever!</comment>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale>
<saleid>14</saleid>
<timestamp>2005-07-10 15:09:00</timestamp>
<date>2005-07-10</date>
<time>15:09:00</time>
<album>
<albumid>2</albumid>
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man's got somthin' to say</comment>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</customer>
<customer>
<customerid>8</customerid>
<first_name>Ed</first_name>
<last_name>Burton</last_name>
<email>ed.burton@example.com</email>
<sales>
<sale>
<saleid>8</saleid>
<timestamp>2005-07-10 16:03:00</timestamp>
<date>2005-07-10</date>
<time>16:03:00</time>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ever!</comment>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale>
<saleid>19</saleid>
<timestamp>2005-07-10 16:03:01</timestamp>
<date>2005-07-10</date>
<time>16:03:01</time>
<album>
<albumid>3</albumid>
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</customer>
<customer>
<customerid>9</customerid>
<first_name>Jack</first_name>
<last_name>Woo</last_name>
<email>jack.woo@example.com</email>
<sales>
<sale>
<saleid>9</saleid>
<timestamp>2005-07-10 18:03:00</timestamp>
<date>2005-07-10</date>
<time>18:03:00</time>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ever!</comment>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale>
<saleid>15</saleid>
<timestamp>2005-07-10 18:49:00</timestamp>
<date>2005-07-10</date>
<time>18:49:00</time>
<album>
<albumid>2</albumid>
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man's got somthin' to say</comment>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</customer>
<customer>
<customerid>10</customerid>
<first_name>Maria</first_name>
<last_name>Gonzales</last_name>
<email>maria.gonzales@example.com</email>
<sales>
<sale>
<saleid>10</saleid>
<timestamp>2005-07-10 19:03:00</timestamp>
<date>2005-07-10</date>
<time>19:03:00</time>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ever!</comment>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale>
<saleid>20</saleid>
<timestamp>2005-07-10 19:03:50</timestamp>
<date>2005-07-10</date>
<time>19:03:50</time>
<album>
<albumid>3</albumid>
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</customer>
</music_store>
case05_debug.php
case05_debug.php:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
require_once 'Log.php';
$debugLogger = Log::factory('file', 'case05.log', 'Query2XML');
$query2xml->enableDebugLog($debugLogger);
$query2xml->startProfiling();
$dom = $query2xml->getXML(
"SELECT
*
FROM
customer c
LEFT JOIN sale s ON c.customerid = s.customer_id
LEFT JOIN album al ON s.album_id = al.albumid
LEFT JOIN artist ar ON al.artist_id = ar.artistid",
array(
'rootTag' => 'music_store',
'rowTag' => 'customer',
'idColumn' => 'customerid',
'elements' => array(
'customerid',
'first_name',
'last_name',
'email',
'sales' => array(
'rootTag' => 'sales',
'rowTag' => 'sale',
'idColumn' => 'saleid',
'elements' => array(
'saleid',
'timestamp',
'date' => '#Callbacks::getFirstWord()',
'time' => '#Callbacks::getSecondWord()',
'album' => array(
'rootTag' => '',
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array(
'albumid',
'title',
'published_year',
'comment',
'artist' => array(
'rootTag' => '',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'artistid',
'name',
'birth_year',
'birth_place',
'genre'
) //artist elements
) //artist array
) //album elements
) //album array
) //sales elements
) //sales array
) //root elements
) //root
); //getXML method call
$root = $dom->firstChild;
$root->setAttribute('date_generated', date("Y-m-d\TH:i:s", 1124801570));
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
require_once 'File.php';
$fp = new File();
$fp->write('case05.profile', $query2xml->getProfile(), FILE_MODE_WRITE);
class Callbacks
{
function getFirstWord($record)
{
return substr($record['timestamp'], 0, strpos($record['timestamp'], ' ')); }
function getSecondWord($record)
{
return substr($record['timestamp'], strpos($record['timestamp'], ' ') +
1); }
}
?>
case05.log
The format of a debug log file is documented at Logging and Debugging XML_Query2XML.
Our debug log now contains a single query:
Feb 11 17:27:19 Query2XML [info] QUERY: SELECT
*
FROM
customer c
LEFT JOIN sale s ON c.customerid = s.customer_id
LEFT JOIN album al ON s.album_id = al.albumid
LEFT JOIN artist ar ON al.artist_id = ar.artistid
Feb 11 17:27:19 Query2XML [info] DONE
case05.profile
The format of the output is documented under
XML_Query2XML::getProfile():
FROM_DB FROM_CACHE CACHED AVG_DURATION DURATION_SUM SQL
1 0 false 0.0074028968 0.0074028968 SELECT
*
FROM
customer c
LEFT JOIN sale s ON c.customerid = s.customer_id
LEFT JOIN album al ON s.album_id = al.albumid
LEFT JOIN artist ar ON al.artist_id = ar.artistid
TOTAL_DURATION: 0.22688508033752
DB_DURATION: 0.050441980361938
Case 06: BIG join over 10 tables
Case 06 will teach you:
Case 06 demonstrates how complex things can get :) First have a look at the
ER diagram.
It shows a company that sells records. The basic structure of the generated
XML document is as follows:
- for each store we want a list of all departments that are located in this store
- for each department we want a list of all employees that work in this department
- for each employee we want a list of all his sales
- for each sale we want to know the customer and the album sold
- for each album we want to know the artist that performed the music
case06.php
case06.php looks like this:
<?php
require_once 'XML/Query2XML.php';
require_once 'XML/Query2XML/Callback.php';
require_once 'MDB2.php';
/**Static class that provides validation and parsing methods for
* generating XML.
*
* It is static so that we can easyly call its methods from inside
* Query2XML using eval'd code.
*/
class Helper
{
/**Associative array of US postal state codes*/
public static $statePostalCodes = array(
'ALABAMA' => 'AL', 'ALASKA' => 'AK', 'AMERICAN SAMOA' => 'AS', 'ARIZONA' => 'AZ', 'ARKANSAS' => 'AR', 'CALIFORNIA' => 'CA',
'COLORADO' => 'CO', 'CONNECTICUT' => 'CT', 'DELAWARE' => 'DE', 'DISTRICT OF COLUMBIA' => 'DC', 'FEDERATED STATES OF MICRONESIA' => 'FM',
'FLORIDA' => 'FL', 'GEORGIA' => 'GA', 'GUAM' => 'GU', 'HAWAII' => 'HI', 'IDAHO' => 'ID', 'ILLINOIS' => 'IL', 'INDIANA' => 'IN',
'IOWA' => 'IA', 'KANSAS' => 'KS', 'KENTUCKY' => 'KY', 'LOUISIANA' => 'LA', 'MAINE' => 'ME', 'MARSHALL ISLANDS' => 'MH', 'MARYLAND' => 'MD',
'MASSACHUSETTS' => 'MA', 'MICHIGAN' => 'MI', 'MINNESOTA' => 'MN', 'MISSISSIPPI' => 'MS', 'MISSOURI' => 'MO', 'MONTANA' => 'MT',
'NEBRASKA' => 'NE', 'NEVADA' => 'NV', 'NEW HAMPSHIRE' => 'NH', 'NEW JERSEY' => 'NJ', 'NEW JESEY' => 'NJ', 'NEW MEXICO' => 'NM', 'NEW YORK' => 'NY',
'NORTH CAROLINA' => 'NC', 'NORTH DAKOTA' => 'ND', 'NORTHERN MARIANA ISLANDS' => 'MP', 'OHIO' => 'OH', 'OKLAHOMA' => 'OK', 'OREGON' => 'OR',
'PALAU' => 'PW', 'PENNSYLVANIA' => 'PA', 'PUERTO RICO' => 'PR', 'RHODE ISLAND' => 'RI', 'SOUTH CAROLINA' => 'SC', 'SOUTH DAKOTA' => 'SD',
'TENNESSEE' => 'TN', 'TEXAS' => 'TX', 'UTAH' => 'UT', 'VERMONT' => 'VT', 'VIRGIN ISLANDS' => 'VI', 'VIRGINIA' => 'VA', 'WASHINGTON' => 'WA',
'WEST VIRGINIA' => 'WV', 'WISCONSIN' => 'WI', 'WYOMING' => 'WY'
);
/**Translates a US state name into its two-letter postal code.
* If the translation fails, $state is returned unchanged
* @param $record The record
*/
public static function getStatePostalCode($record)
{
$state = $record["state"];
if (isset(self::$statePostalCodes[$s])) {
return self::$statePostalCodes[$s];
} else {
return $state;
}}
function summarize($str, $limit=50, $appendString=' ...')
{
if (strlen($str) > $limit) {
$str =
substr($str, 0, $limit -
strlen($appendString)) .
$appendString; }
return $str;
}
function summarizeComment($record, $limit)
{
return self::summarize($record["comment"], $limit);
}
}
/**Command class that implements the command pattern.
* It implements the XML_Query2XML_Callback interface
* and therefore has to provide the public non-static
* method execute(array $record).
*/
class UppercaseColumnCommand implements XML_Query2XML_Callback
{
public function __construct($columnName)
{
$this->_columnName = $columnName;
}
public function execute(array $record)
{
return strtoupper($record[$this->_columnName]);
}
}
$dom = $query2xml->getXML(
"SELECT
s.*,
manager.employeeid AS manager_employeeid,
manager.employeename AS manager_employeename,
d.*,
department_head.employeeid AS department_head_employeeid,
department_head.employeename AS department_head_employeename,
e.*,
sa.*,
c.*,
al.*,
ar.*,
(SELECT COUNT(*) FROM sale WHERE sale.store_id = s.storeid) AS store_sales,
(SELECT
COUNT(*)
FROM
sale, employee, employee_department
WHERE
sale.employee_id = employee.employeeid
AND
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = d.departmentid
) AS department_sales,
(SELECT
COUNT(*)
FROM
employee, employee_department, department
WHERE
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = department.departmentid
AND
department.store_id = s.storeid
) AS store_employees,
(SELECT
COUNT(*)
FROM
employee, employee_department
WHERE
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = d.departmentid
) AS department_employees
FROM
store s
LEFT JOIN employee manager ON s.manager = manager.employeeid
LEFT JOIN department d ON d.store_id = s.storeid
LEFT JOIN employee department_head ON department_head.employeeid = d.department_head
LEFT JOIN employee_department ed ON ed.department_id = d.departmentid
LEFT JOIN employee e ON e.employeeid = ed.employee_id
LEFT JOIN sale sa ON sa.employee_id = e.employeeid
LEFT JOIN customer c ON c.customerid = sa.customer_id
LEFT JOIN album al ON al.albumid = sa.album_id
LEFT JOIN artist ar ON ar.artistid = al.artist_id",
array(
'rootTag' => 'music_company',
'rowTag' => 'store',
'idColumn' => 'storeid',
'attributes' => array(
'storeid'
),
'elements' => array(
'store_sales',
'store_employees',
'manager' => array(
'idColumn' => 'manager_employeeid',
'attributes' => array(
'manager_employeeid'
),
'elements' => array(
'manager_employeename'
)
),
'address' => array(
'elements' => array(
'country',
'state' => '#Helper::getStatePostalCode()',
'city' => new UppercaseColumnCommand('city'),
'street',
'phone'
)
),
'department' => array(
'idColumn' => 'departmentid',
'attributes' => array(
'departmentid'
),
'elements' => array(
'department_sales',
'department_employees',
'departmentname',
'department_head' => array(
'idColumn' => 'department_head_employeeid',
'attributes' => array(
'department_head_employeeid'
),
'elements' => array(
'department_head_employeename'
)
),
'employees' => array(
'rootTag' => 'employees',
'rowTag' => 'employee',
'idColumn' => 'employeeid',
'attributes' => array(
'employeeid'
),
'elements' => array(
'employeename',
'sales' => array(
'rootTag' => 'sales',
'rowTag' => 'sale',
'idColumn' => 'saleid',
'attributes' => array(
'saleid'
),
'elements' => array(
'timestamp',
'customer' => array(
'idColumn' => 'customerid',
'attributes' => array(
'customerid'
),
'elements' => array(
'first_name',
'last_name',
'email'
)
),
'album' => array(
'idColumn' => 'albumid',
'attributes' => array(
'albumid'
),
'elements' => array(
'title',
'published_year',
'comment' => '?#Helper::summarizeComment(12)',
'artist' => array(
'idColumn' => 'artistid',
'attributes' => array(
'artistid'
),
'elements' => array(
'name',
'birth_year',
'birth_place',
'genre'
)
)
) // album elements
) //album array
) //sales elements
) //sales array
) //employees elements
) //employees array
) //department elements
) // department array
) //root elements
) //root
); //getXML method call
$root = $dom->firstChild;
$root->setAttribute('date_generated', date("Y-m-d\TH:i:s", 1124801570));
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
?>
Note how $options['idColumn'] is used at the different
levels:
- on the root level we want a record for each store and therefore use the primary key of the table 'store' as
the idColumn:
- [elements][address] does not use $options['idColumn']
at all because all columns used at this level ('country',
'state', 'city', 'street' and 'phone') are all columns of the table 'store'.
- on the level [elements][department] we want a record for each department (within the current store)
and therefore use the primary key of the table 'department' as the idColumn:
'idColumn' => 'departmentid',
- on the level [elements][department][elements][employees] we want a record for each employee (within
the current department) and therefore use the primary key of the table 'employee' as the idColumn:
'idColumn' => 'employeeid',
- on the level [elements][department][elements][employees][elements][sales] we want a record for each sale
(perfmormed by the current employee) and therefore use the primary key of the table 'sale' as the idColumn:
The reason we can use sale.saleid without listing all sales underneath all employees is of course that
our LEFT JOIN has the condition "LEFT JOIN sale ON sale.employee_id = employee.employeeid".
- on the level [elements][department][elements][employees][elements][sales][elements][customer] we want a
record for each customer (which was a party to the current sale) and therefore use the primary key of
the table 'customer' as the idColumn:
'idColumn' => 'customerid',
- on the level [elements][department][elements][employees][elements][sales][elements][album] we want a
record for each album (which was subject to the current sale) and therefore use the primary key of
the table 'album' as the idColumn:
Logically speaking we are of course using the foreign key sale.album_id, but as the equality of
sale.album_id and album.albumid is the condition for our LEFT JOIN, both are the same.
- on the level [elements][department][elements][employees][elements][sales][elements][album][elements][artist]
we want a record for each artist (who permormed the current album) and therefore use the primary key of
the table 'artist' as the idColumn:
'idColumn' => 'artistid',
We also use a static class called Helper here. Note how Helper::summarizeComment() is called with a second argument.
The current record is always passed as first argument to the callback function. So we specify the "comment"
element as
'comment' => '?#Helper::summarizeComment(12)',
In addition to the CALLBACK FUNCTION we also use a COMMAND OBJECT. In our case it is
an instance of the class UppercaseColumnCommand which implements the XML_Query2XML_Callback
interface. We pass the column's name as constructor argument, so that UppercaseColumnCommand::execute()
knows which column (i.e. which element of the $record array) to pass to strtoupper().
case06.xml
The resulting XML data looks like this:
<?xml version="1.0" encoding="UTF-8"?>
<music_company date_generated="2005-08-23T14:52:50">
<store storeid="1">
<store_sales>10</store_sales>
<store_employees>6</store_employees>
<manager manager_employeeid="1">
<manager_employeename>Michael Jones</manager_employeename>
</manager>
<address>
<country>US</country>
<state>NY</state>
<city>NEW YORK</city>
<street>Broadway & 72nd Str</street>
<phone>123 456 7890</phone>
</address>
<department departmentid="1">
<department_sales>10</department_sales>
<department_employees>3</department_employees>
<departmentname>Sales</departmentname>
<department_head department_head_employeeid="1">
<department_head_employeename>Michael Jones</department_head_employeename>
</department_head>
<employees>
<employee employeeid="1">
<employeename>Michael Jones</employeename>
<sales>
<sale saleid="1">
<timestamp>2005-05-25 16:32:00</timestamp>
<customer customerid="1">
<first_name>Jane</first_name>
<last_name>Doe</last_name>
<email>jane.doe@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale saleid="7">
<timestamp>2005-07-10 15:03:00</timestamp>
<customer customerid="7">
<first_name>Nick</first_name>
<last_name>Fallow</last_name>
<email>nick.fallow@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale saleid="16">
<timestamp>2005-06-05 12:56:12</timestamp>
<customer customerid="2">
<first_name>John</first_name>
<last_name>Doe</last_name>
<email>john.doe@example.com</email>
</customer>
<album albumid="3">
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
<artist artistid="2">
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale saleid="19">
<timestamp>2005-07-10 16:03:01</timestamp>
<customer customerid="8">
<first_name>Ed</first_name>
<last_name>Burton</last_name>
<email>ed.burton@example.com</email>
</customer>
<album albumid="3">
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
<artist artistid="2">
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</employee>
<employee employeeid="2">
<employeename>Susi Weintraub</employeename>
<sales>
<sale saleid="3">
<timestamp>2005-07-10 11:03:00</timestamp>
<customer customerid="3">
<first_name>Susan</first_name>
<last_name>Green</last_name>
<email>susan.green@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale saleid="9">
<timestamp>2005-07-10 18:03:00</timestamp>
<customer customerid="9">
<first_name>Jack</first_name>
<last_name>Woo</last_name>
<email>jack.woo@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale saleid="17">
<timestamp>2005-07-10 10:03:32</timestamp>
<customer customerid="4">
<first_name>Victoria</first_name>
<last_name>Alt</last_name>
<email>victory.alt@example.com</email>
</customer>
<album albumid="3">
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
<artist artistid="2">
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale saleid="20">
<timestamp>2005-07-10 19:03:50</timestamp>
<customer customerid="10">
<first_name>Maria</first_name>
<last_name>Gonzales</last_name>
<email>maria.gonzales@example.com</email>
</customer>
<album albumid="3">
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
<artist artistid="2">
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</employee>
<employee employeeid="3">
<employeename>Steve Hack</employeename>
<sales>
<sale saleid="5">
<timestamp>2005-07-10 13:03:00</timestamp>
<customer customerid="5">
<first_name>Will</first_name>
<last_name>Rippy</last_name>
<email>will.wippy@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale saleid="18">
<timestamp>2005-07-10 14:03:52</timestamp>
<customer customerid="6">
<first_name>Tim</first_name>
<last_name>Raw</last_name>
<email>tim.raw@example.com</email>
</customer>
<album albumid="3">
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
<artist artistid="2">
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</employee>
</employees>
</department>
<department departmentid="2">
<department_sales>0</department_sales>
<department_employees>3</department_employees>
<departmentname>Marketing</departmentname>
<department_head department_head_employeeid="4">
<department_head_employeename>Joan Kerr</department_head_employeename>
</department_head>
<employees>
<employee employeeid="4">
<employeename>Joan Kerr</employeename>
<sales/>
</employee>
<employee employeeid="5">
<employeename>Marcus Roth</employeename>
<sales/>
</employee>
<employee employeeid="6">
<employeename>Jack Mack</employeename>
<sales/>
</employee>
</employees>
</department>
</store>
<store storeid="2">
<store_sales>10</store_sales>
<store_employees>6</store_employees>
<manager manager_employeeid="2">
<manager_employeename>Susi Weintraub</manager_employeename>
</manager>
<address>
<country>US</country>
<state>NY</state>
<city>LARCHMONT</city>
<street>Palmer Ave 71</street>
<phone>456 7890</phone>
</address>
<department departmentid="3">
<department_sales>10</department_sales>
<department_employees>3</department_employees>
<departmentname>Sales</departmentname>
<department_head department_head_employeeid="7">
<department_head_employeename>Rita Doktor</department_head_employeename>
</department_head>
<employees>
<employee employeeid="7">
<employeename>Rita Doktor</employeename>
<sales>
<sale saleid="2">
<timestamp>2005-06-05 12:56:00</timestamp>
<customer customerid="2">
<first_name>John</first_name>
<last_name>Doe</last_name>
<email>john.doe@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale saleid="8">
<timestamp>2005-07-10 16:03:00</timestamp>
<customer customerid="8">
<first_name>Ed</first_name>
<last_name>Burton</last_name>
<email>ed.burton@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale saleid="11">
<timestamp>2005-05-25 16:23:00</timestamp>
<customer customerid="1">
<first_name>Jane</first_name>
<last_name>Doe</last_name>
<email>jane.doe@example.com</email>
</customer>
<album albumid="2">
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale saleid="14">
<timestamp>2005-07-10 15:09:00</timestamp>
<customer customerid="7">
<first_name>Nick</first_name>
<last_name>Fallow</last_name>
<email>nick.fallow@example.com</email>
</customer>
<album albumid="2">
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</employee>
<employee employeeid="8">
<employeename>David Til</employeename>
<sales>
<sale saleid="4">
<timestamp>2005-07-10 10:03:00</timestamp>
<customer customerid="4">
<first_name>Victoria</first_name>
<last_name>Alt</last_name>
<email>victory.alt@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale saleid="10">
<timestamp>2005-07-10 19:03:00</timestamp>
<customer customerid="10">
<first_name>Maria</first_name>
<last_name>Gonzales</last_name>
<email>maria.gonzales@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale saleid="12">
<timestamp>2005-07-10 11:56:00</timestamp>
<customer customerid="3">
<first_name>Susan</first_name>
<last_name>Green</last_name>
<email>susan.green@example.com</email>
</customer>
<album albumid="2">
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale saleid="15">
<timestamp>2005-07-10 18:49:00</timestamp>
<customer customerid="9">
<first_name>Jack</first_name>
<last_name>Woo</last_name>
<email>jack.woo@example.com</email>
</customer>
<album albumid="2">
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</employee>
<employee employeeid="9">
<employeename>Pia Eist</employeename>
<sales>
<sale saleid="6">
<timestamp>2005-07-10 14:03:00</timestamp>
<customer customerid="6">
<first_name>Tim</first_name>
<last_name>Raw</last_name>
<email>tim.raw@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale saleid="13">
<timestamp>2005-07-10 13:12:00</timestamp>
<customer customerid="5">
<first_name>Will</first_name>
<last_name>Rippy</last_name>
<email>will.wippy@example.com</email>
</customer>
<album albumid="2">
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</employee>
</employees>
</department>
<department departmentid="4">
<department_sales>0</department_sales>
<department_employees>3</department_employees>
<departmentname>Marketing</departmentname>
<department_head department_head_employeeid="10">
<department_head_employeename>Hanna Poll</department_head_employeename>
</department_head>
<employees>
<employee employeeid="10">
<employeename>Hanna Poll</employeename>
<sales/>
</employee>
<employee employeeid="11">
<employeename>Jim Wells</employeename>
<sales/>
</employee>
<employee employeeid="12">
<employeename>Sandra Wilson</employeename>
<sales/>
</employee>
</employees>
</department>
</store>
</music_company>
case06_debug.php
case06_debug.php:
<?php
require_once 'XML/Query2XML.php';
require_once 'XML/Query2XML/Callback.php';
require_once 'MDB2.php';
/**Static class that provides validation and parsing methods for
* generating XML.
*
* It is static so that we can easyly call its methods from inside
* Query2XML using eval'd code.
*/
class Helper
{
/**Associative array of US postal state codes*/
public static $statePostalCodes = array(
'ALABAMA' => 'AL', 'ALASKA' => 'AK', 'AMERICAN SAMOA' => 'AS', 'ARIZONA' => 'AZ', 'ARKANSAS' => 'AR', 'CALIFORNIA' => 'CA',
'COLORADO' => 'CO', 'CONNECTICUT' => 'CT', 'DELAWARE' => 'DE', 'DISTRICT OF COLUMBIA' => 'DC', 'FEDERATED STATES OF MICRONESIA' => 'FM',
'FLORIDA' => 'FL', 'GEORGIA' => 'GA', 'GUAM' => 'GU', 'HAWAII' => 'HI', 'IDAHO' => 'ID', 'ILLINOIS' => 'IL', 'INDIANA' => 'IN',
'IOWA' => 'IA', 'KANSAS' => 'KS', 'KENTUCKY' => 'KY', 'LOUISIANA' => 'LA', 'MAINE' => 'ME', 'MARSHALL ISLANDS' => 'MH', 'MARYLAND' => 'MD',
'MASSACHUSETTS' => 'MA', 'MICHIGAN' => 'MI', 'MINNESOTA' => 'MN', 'MISSISSIPPI' => 'MS', 'MISSOURI' => 'MO', 'MONTANA' => 'MT',
'NEBRASKA' => 'NE', 'NEVADA' => 'NV', 'NEW HAMPSHIRE' => 'NH', 'NEW JERSEY' => 'NJ', 'NEW JESEY' => 'NJ', 'NEW MEXICO' => 'NM', 'NEW YORK' => 'NY',
'NORTH CAROLINA' => 'NC', 'NORTH DAKOTA' => 'ND', 'NORTHERN MARIANA ISLANDS' => 'MP', 'OHIO' => 'OH', 'OKLAHOMA' => 'OK', 'OREGON' => 'OR',
'PALAU' => 'PW', 'PENNSYLVANIA' => 'PA', 'PUERTO RICO' => 'PR', 'RHODE ISLAND' => 'RI', 'SOUTH CAROLINA' => 'SC', 'SOUTH DAKOTA' => 'SD',
'TENNESSEE' => 'TN', 'TEXAS' => 'TX', 'UTAH' => 'UT', 'VERMONT' => 'VT', 'VIRGIN ISLANDS' => 'VI', 'VIRGINIA' => 'VA', 'WASHINGTON' => 'WA',
'WEST VIRGINIA' => 'WV', 'WISCONSIN' => 'WI', 'WYOMING' => 'WY'
);
/**Translates a US state name into its two-letter postal code.
* If the translation fails, $state is returned unchanged
* @param $record The record
*/
public static function getStatePostalCode($record)
{
$state = $record["state"];
if (isset(self::$statePostalCodes[$s])) {
return self::$statePostalCodes[$s];
} else {
return $state;
}}
function summarize($str, $limit=50, $appendString=' ...')
{
if (strlen($str) > $limit) {
$str =
substr($str, 0, $limit -
strlen($appendString)) .
$appendString; }
return $str;
}
function summarizeComment($record, $limit)
{
return self::summarize($record["comment"], $limit);
}
}
/**Command class that implements the command pattern.
* It implements the XML_Query2XML_Callback interface
* and therefore has to provide the public non-static
* method execute(array $record).
*/
class UppercaseColumnCommand implements XML_Query2XML_Callback
{
public function __construct($columnName)
{
$this->_columnName = $columnName;
}
public function execute(array $record)
{
return strtoupper($record[$this->_columnName]);
}
}
require_once 'Log.php';
$debugLogger = Log::factory('file', 'case06.log', 'XML_Query2XML');
$query2xml->enableDebugLog($debugLogger);
$query2xml->startProfiling();
$dom = $query2xml->getXML(
"SELECT
s.*,
manager.employeeid AS manager_employeeid,
manager.employeename AS manager_employeename,
d.*,
department_head.employeeid AS department_head_employeeid,
department_head.employeename AS department_head_employeename,
e.*,
sa.*,
c.*,
al.*,
ar.*,
(SELECT COUNT(*) FROM sale WHERE sale.store_id = s.storeid) AS store_sales,
(SELECT
COUNT(*)
FROM
sale, employee, employee_department
WHERE
sale.employee_id = employee.employeeid
AND
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = d.departmentid
) AS department_sales,
(SELECT
COUNT(*)
FROM
employee, employee_department, department
WHERE
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = department.departmentid
AND
department.store_id = s.storeid
) AS store_employees,
(SELECT
COUNT(*)
FROM
employee, employee_department
WHERE
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = d.departmentid
) AS department_employees
FROM
store s
LEFT JOIN employee manager ON s.manager = manager.employeeid
LEFT JOIN department d ON d.store_id = s.storeid
LEFT JOIN employee department_head ON department_head.employeeid = d.department_head
LEFT JOIN employee_department ed ON ed.department_id = d.departmentid
LEFT JOIN employee e ON e.employeeid = ed.employee_id
LEFT JOIN sale sa ON sa.employee_id = e.employeeid
LEFT JOIN customer c ON c.customerid = sa.customer_id
LEFT JOIN album al ON al.albumid = sa.album_id
LEFT JOIN artist ar ON ar.artistid = al.artist_id",
array(
'rootTag' => 'music_company',
'rowTag' => 'store',
'idColumn' => 'storeid',
'attributes' => array(
'storeid'
),
'elements' => array(
'store_sales',
'store_employees',
'manager' => array(
'idColumn' => 'manager_employeeid',
'attributes' => array(
'manager_employeeid'
),
'elements' => array(
'manager_employeename'
)
),
'address' => array(
'elements' => array(
'country',
'state' => '#Helper::getStatePostalCode()',
'city' => new UppercaseColumnCommand('city'),
'street',
'phone'
)
),
'department' => array(
'idColumn' => 'departmentid',
'attributes' => array(
'departmentid'
),
'elements' => array(
'department_sales',
'department_employees',
'departmentname',
'department_head' => array(
'idColumn' => 'department_head_employeeid',
'attributes' => array(
'department_head_employeeid'
),
'elements' => array(
'department_head_employeename'
)
),
'employees' => array(
'rootTag' => 'employees',
'rowTag' => 'employee',
'idColumn' => 'employeeid',
'attributes' => array(
'employeeid'
),
'elements' => array(
'employeename',
'sales' => array(
'rootTag' => 'sales',
'rowTag' => 'sale',
'idColumn' => 'saleid',
'attributes' => array(
'saleid'
),
'elements' => array(
'timestamp',
'customer' => array(
'idColumn' => 'customerid',
'attributes' => array(
'customerid'
),
'elements' => array(
'first_name',
'last_name',
'email'
)
),
'album' => array(
'idColumn' => 'albumid',
'attributes' => array(
'albumid'
),
'elements' => array(
'title',
'published_year',
'comment' => '?#Helper::summarizeComment(12)',
'artist' => array(
'idColumn' => 'artistid',
'attributes' => array(
'artistid'
),
'elements' => array(
'name',
'birth_year',
'birth_place',
'genre'
)
)
) // album elements
) //album array
) //sales elements
) //sales array
) //employees elements
) //employees array
) //department elements
) // department array
) //root elements
) //root
); //getXML method call
$root = $dom->firstChild;
$root->setAttribute('date_generated', date("Y-m-d\TH:i:s", 1124801570));
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
require_once 'File.php';
$fp = new File();
$fp->write('case06.profile', $query2xml->getProfile(), FILE_MODE_WRITE);
?>
case06.log
The format of a debug log file is documented at Logging and Debugging XML_Query2XML.
Our debug log contains just a single query:
Feb 11 17:39:46 XML_Query2XML [info] QUERY: SELECT
s.*,
manager.employeeid AS manager_employeeid,
manager.employeename AS manager_employeename,
d.*,
department_head.employeeid AS department_head_employeeid,
department_head.employeename AS department_head_employeename,
e.*,
sa.*,
c.*,
al.*,
ar.*,
(SELECT COUNT(*) FROM sale WHERE sale.store_id = s.storeid) AS store_sales,
(SELECT
COUNT(*)
FROM
sale, employee, employee_department
WHERE
sale.employee_id = employee.employeeid
AND
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = d.departmentid
) AS department_sales,
(SELECT
COUNT(*)
FROM
employee, employee_department, department
WHERE
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = department.departmentid
AND
department.store_id = s.storeid
) AS store_employees,
(SELECT
COUNT(*)
FROM
employee, employee_department
WHERE
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = d.departmentid
) AS department_employees
FROM
store s
LEFT JOIN employee manager ON s.manager = manager.employeeid
LEFT JOIN department d ON d.store_id = s.storeid
LEFT JOIN employee department_head ON department_head.employeeid = d.department_head
LEFT JOIN employee_department ed ON ed.department_id = d.departmentid
LEFT JOIN employee e ON e.employeeid = ed.employee_id
LEFT JOIN sale sa ON sa.employee_id = e.employeeid
LEFT JOIN customer c ON c.customerid = sa.customer_id
LEFT JOIN album al ON al.albumid = sa.album_id
LEFT JOIN artist ar ON ar.artistid = al.artist_id
Feb 11 17:39:46 XML_Query2XML [info] DONE
case06.profile
The format of the output is documented under
XML_Query2XML::getProfile():
FROM_DB FROM_CACHE CACHED AVG_DURATION DURATION_SUM SQL
1 0 false 0.0172939300 0.0172939300 SELECT
s.*,
manager.employeeid AS manager_employeeid,
manager.employeename AS manager_employeename,
d.*,
department_head.employeeid AS department_head_employeeid,
department_head.employeename AS department_head_employeename,
e.*,
sa.*,
c.*,
al.*,
ar.*,
(SELECT COUNT(*) FROM sale WHERE sale.store_id = s.storeid) AS store_sales,
(SELECT
COUNT(*)
FROM
sale, employee, employee_department
WHERE
sale.employee_id = employee.employeeid
AND
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = d.departmentid
) AS department_sales,
(SELECT
COUNT(*)
FROM
employee, employee_department, department
WHERE
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = department.departmentid
AND
department.store_id = s.storeid
) AS store_employees,
(SELECT
COUNT(*)
FROM
employee, employee_department
WHERE
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = d.departmentid
) AS department_employees
FROM
store s
LEFT JOIN employee manager ON s.manager = manager.employeeid
LEFT JOIN department d ON d.store_id = s.storeid
LEFT JOIN employee department_head ON department_head.employeeid = d.department_head
LEFT JOIN employee_department ed ON ed.department_id = d.departmentid
LEFT JOIN employee e ON e.employeeid = ed.employee_id
LEFT JOIN sale sa ON sa.employee_id = e.employeeid
LEFT JOIN customer c ON c.customerid = sa.customer_id
LEFT JOIN album al ON al.albumid = sa.album_id
LEFT JOIN artist ar ON ar.artistid = al.artist_id
TOTAL_DURATION: 0.29014992713928
DB_DURATION: 0.1554069519043
Case 07: Case 03 with Asterisk Shortcuts
Case 07 will teach you:
As documented under Using the Asterisk Shortcut
an asterisk can be used to treat all columns found in the result set the same.
case07.php
case07.php looks like this:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'*',
'albums' => array(
'sql' => array(
'data' => array(
'artistid'
),
'query' => 'SELECT * FROM album WHERE artist_id = ?'
),
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array(
'*',
'artist_id' => '?:'
)
)
)
)
);
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
?>
case07.xml
The resulting XML data is identical with case03.xml:
<?xml version="1.0" encoding="UTF-8"?>
<music_library>
<artist>
<artistid>1</artistid>
<name>Curtis Mayfield</name>
<birth_year>1920</birth_year>
<birth_place>Chicago</birth_place>
<genre>Soul</genre>
<albums>
<album>
<albumid>1</albumid>
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ever!</comment>
</album>
<album>
<albumid>2</albumid>
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man's got somthin' to say</comment>
</album>
</albums>
</artist>
<artist>
<artistid>2</artistid>
<name>Isaac Hayes</name>
<birth_year>1942</birth_year>
<birth_place>Tennessee</birth_place>
<genre>Soul</genre>
<albums>
<album>
<albumid>3</albumid>
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
</album>
</albums>
</artist>
<artist>
<artistid>3</artistid>
<name>Ray Charles</name>
<birth_year>1930</birth_year>
<birth_place>Mississippi</birth_place>
<genre>Country and Soul</genre>
<albums />
</artist>
</music_library>
case07_debug.php
case07_debug.php is very similar to case03_debug.php:
<?php
require_once 'XML/Query2XML.php';
require_once 'MDB2.php';
require_once 'Log.php';
$debugLogger = Log::factory('file', 'case07.log', 'Query2XML');
$query2xml->enableDebugLog($debugLogger);
$query2xml->startProfiling();
$dom = $query2xml->getXML(
"SELECT
*
FROM
artist",
array(
'rootTag' => 'music_library',
'rowTag' => 'artist',
'idColumn' => 'artistid',
'elements' => array(
'*',
'albums' => array(
'sql' => array(
'data' => array(
'artistid'
),
'query' => 'SELECT * FROM album WHERE artist_id = ?'
),
'rootTag' => 'albums',
'rowTag' => 'album',
'idColumn' => 'albumid',
'elements' => array(
'*',
'artist_id' => '?:'
)
)
)
)
);
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
require_once 'File.php';
$fp = new File();
$fp->write('case07.profile', $query2xml->getProfile(), FILE_MODE_WRITE);
?>
case07.log
The format of a debug log file is documented at Logging and Debugging XML_Query2XML.
Our debug log now contains 4 queries:
Apr 18 19:02:48 Query2XML [info] QUERY: SELECT
*
FROM
artist
ORDER BY
artistid
Apr 18 19:02:48 Query2XML [info] DONE
Apr 18 19:02:48 Query2XML [info] QUERY: SELECT * FROM album WHERE artist_id = ?; DATA:1
Apr 18 19:02:48 Query2XML [info] DONE
Apr 18 19:02:48 Query2XML [info] QUERY: SELECT * FROM album WHERE artist_id = ?; DATA:2
Apr 18 19:02:48 Query2XML [info] DONE
Apr 18 19:02:48 Query2XML [info] QUERY: SELECT * FROM album WHERE artist_id = ?; DATA:3
Apr 18 19:02:48 Query2XML [info] DONE
The debug log shows what we expected: the first SELECT over the artist table runs once
and the SELECT over the album table runs three times (once for every record found in
the artist table). As the log shows no 'CACHING' entries we also know that no cashing
was performed ($options['sql_options']['cached']
was not set to true).
case07.profile
Profiling is essential for performance tuning. The format of the output is documented under
XML_Query2XML::getProfile(). Our profile looks like this:
FROM_DB FROM_CACHE CACHED AVG_DURATION DURATION_SUM SQL
1 0 false 0.0018889904 0.0018889904 SELECT
*
FROM
artist
3 0 false 0.0021993319 0.0065979957 SELECT * FROM album WHERE artist_id = ?
TOTAL_DURATION: 0.052389144897461
DB_DURATION: 0.016173124313354
Case 08: Case 06 revisited: Making use of Mappers
Case 08 will teach you:
- How to do mapping of SQL identifiers to XML element names in accordenace with
ISO/IEC 9075-14:2005.
- How to write your own mappers.
- How to use different mappers at different levels.
Make sure you are familiar with Case 06: BIG join over 10 tables
before proceeding. Case 08 is different from Case 06 in the following aspects:
- All tags and attributes directly related to columns of the table "store" shall be uppercase;
we'll directly use the PHP function strtoupper() and the 'FUNCTION' syntax for this purpose.
- All tags and attributes directly related to columns of the table "department" shall use
a naming convention like "oneTwoThree" instead of "one_two_three";
we'll use Mappers::departmentMapper() and the 'CLASS::STATIC_METHOD' syntax for this purpose.
- All tags and attributes directly related to columns of the table "employee" shall use
a naming convention like "OneTwoThree" instead of "one_two_three";
we'll use Mappers::employeeMapper() and the array('CLASS', 'STATIC_METHOD') syntax for this purpose.
- All tags and attributes directly related to columns of the table "sale" shall use
a naming convention like "ONETWOTHREE" instead of "one_two_three";
we'll use the non-static Mappers::saleMapper() and the array($instance, 'METHOD') syntax for this purpose.
- All tags and attributes directly related to columns of the table "customer" shall use
the column names as they are defined in the database;
we'll set the mapper option to false for this purpose.
- All tags and attributes directly related to columns of the table "album" shall use
the ISO 9075-mapper; we'll use XML_Query2XML_ISO9075Mapper::map() and the 'CLASS::STATIC_METHOD'
syntax for this purpose.
- All tags and attributes directly related to columns of the table "artist" shall use
a naming convention like "onetwothree" instead of "one_two_three";
we'll the function mapArtist as the mapper and the 'FUNCTION' syntax.
- Due to the fact that SQLite prior to 3.1 does not support
a subselect backreferencing to a field in its parent select
(see http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql) Case 08 will not work
with SQLite < 3.1.
This certainly is not a very realistic scenario but it was chosen to demonstrate the
different syntaxes for specifying a mapper function and how to use multiple
mappers. This case also demonstrates that all XML names explicitly specified are
not subject to any special mapping! For example the tag names "music_company" and
"store" are left untouched because they are explicitly specified using the options
"rootTag" and "rowTag". An other case is the tag "manager" which also stays untouched.
This is because the array key ("manager" in this case) of a complex element specification
is used for the option "rowTag"
per default. XML_Query2XML just does not pretend to be smart than you, the programmer. If you don't
like the tag name "manager", use another array key for the complex element specification or
use the "rowTag" option within the complex element specification.
case08.php
case08.php looks like this:
<?php
class Mappers
{
public static function departmentMapper($str)
{
//maps 'one_two_three' to 'oneTwoThree'
return preg_replace("/(_)([a-z])/e", "strtoupper('\\2')", $str); }
public static function employeeMapper($str)
{
//maps 'one_two_three' to 'OneTwoThree'
}
public function saleMapper($str)
{
//maps 'one_two_three' to 'ONETWOTHREE'
}
}
function mapArtist($str)
{
//maps 'one_two_three' to 'onetwothree'
}
$myMappers = new Mappers();
require_once 'XML/Query2XML.php';
require_once 'XML/Query2XML/ISO9075Mapper.php';
require_once 'MDB2.php';
$dom = $query2xml->getXML(
"SELECT
s.*,
manager.employeeid AS manager_employeeid,
manager.employeename AS manager_employeename,
d.*,
department_head.employeeid AS department_head_employeeid,
department_head.employeename AS department_head_employeename,
e.*,
sa.*,
c.*,
al.*,
ar.*,
(SELECT COUNT(*) FROM sale WHERE sale.store_id = s.storeid) AS store_sales,
(SELECT
COUNT(*)
FROM
sale, employee, employee_department
WHERE
sale.employee_id = employee.employeeid
AND
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = d.departmentid
) AS department_sales,
(SELECT
COUNT(*)
FROM
employee, employee_department, department
WHERE
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = department.departmentid
AND
department.store_id = s.storeid
) AS store_employees,
(SELECT
COUNT(*)
FROM
employee, employee_department
WHERE
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = d.departmentid
) AS department_employees
FROM
store s
LEFT JOIN employee manager ON s.manager = manager.employeeid
LEFT JOIN department d ON d.store_id = s.storeid
LEFT JOIN employee department_head ON department_head.employeeid = d.department_head
LEFT JOIN employee_department ed ON ed.department_id = d.departmentid
LEFT JOIN employee e ON e.employeeid = ed.employee_id
LEFT JOIN sale sa ON sa.employee_id = e.employeeid
LEFT JOIN customer c ON c.customerid = sa.customer_id
LEFT JOIN album al ON al.albumid = sa.album_id
LEFT JOIN artist ar ON ar.artistid = al.artist_id",
array(
'rootTag' => 'music_company',
'rowTag' => 'store',
'idColumn' => 'storeid',
'mapper' => 'strtoupper',
'attributes' => array(
'storeid'
),
'elements' => array(
'store_sales',
'store_employees',
'manager' => array(
'idColumn' => 'manager_employeeid',
'attributes' => array(
'manager_employeeid'
),
'elements' => array(
'manager_employeename'
)
),
'address' => array(
'elements' => array(
'country',
'state' => '#Helper::getStatePostalCode()',
'city',
'street',
'phone'
)
),
'department' => array(
'idColumn' => 'departmentid',
'mapper' => 'Mappers::departmentMapper',
'attributes' => array(
'departmentid'
),
'elements' => array(
'department_sales',
'department_employees',
'departmentname',
'department_head' => array(
'idColumn' => 'department_head_employeeid',
'attributes' => array(
'department_head_employeeid'
),
'elements' => array(
'department_head_employeename'
)
),
'employees' => array(
'rootTag' => 'employees',
'rowTag' => 'employee',
'idColumn' => 'employeeid',
'mapper' => array('Mappers', 'employeeMapper'),
'attributes' => array(
'employeeid'
),
'elements' => array(
'employeename',
'sales' => array(
'rootTag' => 'sales',
'rowTag' => 'sale',
'idColumn' => 'saleid',
'mapper' => array($myMappers, 'saleMapper'),
'attributes' => array(
'saleid'
),
'elements' => array(
'timestamp',
'customer' => array(
'idColumn' => 'customerid',
'mapper' => false,
'attributes' => array(
'customerid'
),
'elements' => array(
'first_name',
'last_name',
'email'
)
),
'album' => array(
'idColumn' => 'albumid',
'mapper' => 'XML_Query2XML_ISO9075Mapper::map',
'attributes' => array(
'albumid'
),
'elements' => array(
'title',
'published_year',
'comment' => '?#Helper::summarizeComment(12)',
'artist' => array(
'idColumn' => 'artistid',
'mapper' => 'mapArtist',
'attributes' => array(
'artistid'
),
'elements' => array(
'name',
'birth_year',
'birth_place',
'genre'
)
)
) // album elements
) //album array
) //sales elements
) //sales array
) //employees elements
) //employees array
) //department elements
) // department array
) //root elements
) //root
); //getXML method call
$root = $dom->firstChild;
$root->setAttribute('date_generated', date("Y-m-d\TH:i:s", 1124801570));
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
/**Static class that provides validation and parsing methods for
* generating XML.
*
* It is static so that we can easyly call its methods from inside
* Query2XML using eval'd code.
*/
class Helper
{
/**Associative array of US postal state codes*/
public static $statePostalCodes = array(
'ALABAMA' => 'AL', 'ALASKA' => 'AK', 'AMERICAN SAMOA' => 'AS', 'ARIZONA' => 'AZ', 'ARKANSAS' => 'AR', 'CALIFORNIA' => 'CA',
'COLORADO' => 'CO', 'CONNECTICUT' => 'CT', 'DELAWARE' => 'DE', 'DISTRICT OF COLUMBIA' => 'DC', 'FEDERATED STATES OF MICRONESIA' => 'FM',
'FLORIDA' => 'FL', 'GEORGIA' => 'GA', 'GUAM' => 'GU', 'HAWAII' => 'HI', 'IDAHO' => 'ID', 'ILLINOIS' => 'IL', 'INDIANA' => 'IN',
'IOWA' => 'IA', 'KANSAS' => 'KS', 'KENTUCKY' => 'KY', 'LOUISIANA' => 'LA', 'MAINE' => 'ME', 'MARSHALL ISLANDS' => 'MH', 'MARYLAND' => 'MD',
'MASSACHUSETTS' => 'MA', 'MICHIGAN' => 'MI', 'MINNESOTA' => 'MN', 'MISSISSIPPI' => 'MS', 'MISSOURI' => 'MO', 'MONTANA' => 'MT',
'NEBRASKA' => 'NE', 'NEVADA' => 'NV', 'NEW HAMPSHIRE' => 'NH', 'NEW JERSEY' => 'NJ', 'NEW JESEY' => 'NJ', 'NEW MEXICO' => 'NM', 'NEW YORK' => 'NY',
'NORTH CAROLINA' => 'NC', 'NORTH DAKOTA' => 'ND', 'NORTHERN MARIANA ISLANDS' => 'MP', 'OHIO' => 'OH', 'OKLAHOMA' => 'OK', 'OREGON' => 'OR',
'PALAU' => 'PW', 'PENNSYLVANIA' => 'PA', 'PUERTO RICO' => 'PR', 'RHODE ISLAND' => 'RI', 'SOUTH CAROLINA' => 'SC', 'SOUTH DAKOTA' => 'SD',
'TENNESSEE' => 'TN', 'TEXAS' => 'TX', 'UTAH' => 'UT', 'VERMONT' => 'VT', 'VIRGIN ISLANDS' => 'VI', 'VIRGINIA' => 'VA', 'WASHINGTON' => 'WA',
'WEST VIRGINIA' => 'WV', 'WISCONSIN' => 'WI', 'WYOMING' => 'WY'
);
/**Translates a US state name into its two-letter postal code.
* If the translation fails, $state is returned unchanged
* @param $record The record
*/
public static function getStatePostalCode($record)
{
$state = $record["state"];
if (isset(self::$statePostalCodes[$s])) {
return self::$statePostalCodes[$s];
} else {
return $state;
}}
function summarize($str, $limit=50, $appendString=' ...')
{
if (strlen($str) > $limit) {
$str =
substr($str, 0, $limit -
strlen($appendString)) .
$appendString; }
return $str;
}
function summarizeComment($record, $limit)
{
return self::summarize($record["comment"], $limit);
}
}
?>
case08.xml
The resulting XML data looks like this:
<?xml version="1.0" encoding="UTF-8"?>
<music_company date_generated="2005-08-23T14:52:50">
<store STOREID="1">
<STORE_SALES>10</STORE_SALES>
<STORE_EMPLOYEES>6</STORE_EMPLOYEES>
<manager MANAGER_EMPLOYEEID="1">
<MANAGER_EMPLOYEENAME>Michael Jones</MANAGER_EMPLOYEENAME>
</manager>
<address>
<COUNTRY>US</COUNTRY>
<STATE>NY</STATE>
<CITY>New York</CITY>
<STREET>Broadway & 72nd Str</STREET>
<PHONE>123 456 7890</PHONE>
</address>
<department departmentid="1">
<departmentSales>10</departmentSales>
<departmentEmployees>3</departmentEmployees>
<departmentname>Sales</departmentname>
<department_head departmentHeadEmployeeid="1">
<departmentHeadEmployeename>Michael Jones</departmentHeadEmployeename>
</department_head>
<employees>
<employee Employeeid="1">
<Employeename>Michael Jones</Employeename>
<sales>
<sale SALEID="1">
<TIMESTAMP>2005-05-25 16:32:00</TIMESTAMP>
<customer customerid="1">
<first_name>Jane</first_name>
<last_name>Doe</last_name>
<email>jane.doe@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birthyear>1920</birthyear>
<birthplace>Chicago</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale SALEID="7">
<TIMESTAMP>2005-07-10 15:03:00</TIMESTAMP>
<customer customerid="7">
<first_name>Nick</first_name>
<last_name>Fallow</last_name>
<email>nick.fallow@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birthyear>1920</birthyear>
<birthplace>Chicago</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale SALEID="16">
<TIMESTAMP>2005-06-05 12:56:12</TIMESTAMP>
<customer customerid="2">
<first_name>John</first_name>
<last_name>Doe</last_name>
<email>john.doe@example.com</email>
</customer>
<album albumid="3">
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
<artist artistid="2">
<name>Isaac Hayes</name>
<birthyear>1942</birthyear>
<birthplace>Tennessee</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale SALEID="19">
<TIMESTAMP>2005-07-10 16:03:01</TIMESTAMP>
<customer customerid="8">
<first_name>Ed</first_name>
<last_name>Burton</last_name>
<email>ed.burton@example.com</email>
</customer>
<album albumid="3">
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
<artist artistid="2">
<name>Isaac Hayes</name>
<birthyear>1942</birthyear>
<birthplace>Tennessee</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</employee>
<employee Employeeid="2">
<Employeename>Susi Weintraub</Employeename>
<sales>
<sale SALEID="3">
<TIMESTAMP>2005-07-10 11:03:00</TIMESTAMP>
<customer customerid="3">
<first_name>Susan</first_name>
<last_name>Green</last_name>
<email>susan.green@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birthyear>1920</birthyear>
<birthplace>Chicago</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale SALEID="9">
<TIMESTAMP>2005-07-10 18:03:00</TIMESTAMP>
<customer customerid="9">
<first_name>Jack</first_name>
<last_name>Woo</last_name>
<email>jack.woo@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birthyear>1920</birthyear>
<birthplace>Chicago</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale SALEID="17">
<TIMESTAMP>2005-07-10 10:03:32</TIMESTAMP>
<customer customerid="4">
<first_name>Victoria</first_name>
<last_name>Alt</last_name>
<email>victory.alt@example.com</email>
</customer>
<album albumid="3">
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
<artist artistid="2">
<name>Isaac Hayes</name>
<birthyear>1942</birthyear>
<birthplace>Tennessee</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale SALEID="20">
<TIMESTAMP>2005-07-10 19:03:50</TIMESTAMP>
<customer customerid="10">
<first_name>Maria</first_name>
<last_name>Gonzales</last_name>
<email>maria.gonzales@example.com</email>
</customer>
<album albumid="3">
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
<artist artistid="2">
<name>Isaac Hayes</name>
<birthyear>1942</birthyear>
<birthplace>Tennessee</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</employee>
<employee Employeeid="3">
<Employeename>Steve Hack</Employeename>
<sales>
<sale SALEID="5">
<TIMESTAMP>2005-07-10 13:03:00</TIMESTAMP>
<customer customerid="5">
<first_name>Will</first_name>
<last_name>Rippy</last_name>
<email>will.wippy@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birthyear>1920</birthyear>
<birthplace>Chicago</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale SALEID="18">
<TIMESTAMP>2005-07-10 14:03:52</TIMESTAMP>
<customer customerid="6">
<first_name>Tim</first_name>
<last_name>Raw</last_name>
<email>tim.raw@example.com</email>
</customer>
<album albumid="3">
<title>Shaft</title>
<published_year>1972</published_year>
<comment>he's the man</comment>
<artist artistid="2">
<name>Isaac Hayes</name>
<birthyear>1942</birthyear>
<birthplace>Tennessee</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</employee>
</employees>
</department>
<department departmentid="2">
<departmentSales>0</departmentSales>
<departmentEmployees>3</departmentEmployees>
<departmentname>Marketing</departmentname>
<department_head departmentHeadEmployeeid="4">
<departmentHeadEmployeename>Joan Kerr</departmentHeadEmployeename>
</department_head>
<employees>
<employee Employeeid="4">
<Employeename>Joan Kerr</Employeename>
<sales />
</employee>
<employee Employeeid="5">
<Employeename>Marcus Roth</Employeename>
<sales />
</employee>
<employee Employeeid="6">
<Employeename>Jack Mack</Employeename>
<sales />
</employee>
</employees>
</department>
</store>
<store STOREID="2">
<STORE_SALES>10</STORE_SALES>
<STORE_EMPLOYEES>6</STORE_EMPLOYEES>
<manager MANAGER_EMPLOYEEID="2">
<MANAGER_EMPLOYEENAME>Susi Weintraub</MANAGER_EMPLOYEENAME>
</manager>
<address>
<COUNTRY>US</COUNTRY>
<STATE>NY</STATE>
<CITY>Larchmont</CITY>
<STREET>Palmer Ave 71</STREET>
<PHONE>456 7890</PHONE>
</address>
<department departmentid="3">
<departmentSales>10</departmentSales>
<departmentEmployees>3</departmentEmployees>
<departmentname>Sales</departmentname>
<department_head departmentHeadEmployeeid="7">
<departmentHeadEmployeename>Rita Doktor</departmentHeadEmployeename>
</department_head>
<employees>
<employee Employeeid="7">
<Employeename>Rita Doktor</Employeename>
<sales>
<sale SALEID="2">
<TIMESTAMP>2005-06-05 12:56:00</TIMESTAMP>
<customer customerid="2">
<first_name>John</first_name>
<last_name>Doe</last_name>
<email>john.doe@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birthyear>1920</birthyear>
<birthplace>Chicago</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale SALEID="8">
<TIMESTAMP>2005-07-10 16:03:00</TIMESTAMP>
<customer customerid="8">
<first_name>Ed</first_name>
<last_name>Burton</last_name>
<email>ed.burton@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birthyear>1920</birthyear>
<birthplace>Chicago</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale SALEID="11">
<TIMESTAMP>2005-05-25 16:23:00</TIMESTAMP>
<customer customerid="1">
<first_name>Jane</first_name>
<last_name>Doe</last_name>
<email>jane.doe@example.com</email>
</customer>
<album albumid="2">
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birthyear>1920</birthyear>
<birthplace>Chicago</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale SALEID="14">
<TIMESTAMP>2005-07-10 15:09:00</TIMESTAMP>
<customer customerid="7">
<first_name>Nick</first_name>
<last_name>Fallow</last_name>
<email>nick.fallow@example.com</email>
</customer>
<album albumid="2">
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birthyear>1920</birthyear>
<birthplace>Chicago</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</employee>
<employee Employeeid="8">
<Employeename>David Til</Employeename>
<sales>
<sale SALEID="4">
<TIMESTAMP>2005-07-10 10:03:00</TIMESTAMP>
<customer customerid="4">
<first_name>Victoria</first_name>
<last_name>Alt</last_name>
<email>victory.alt@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birthyear>1920</birthyear>
<birthplace>Chicago</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale SALEID="10">
<TIMESTAMP>2005-07-10 19:03:00</TIMESTAMP>
<customer customerid="10">
<first_name>Maria</first_name>
<last_name>Gonzales</last_name>
<email>maria.gonzales@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birthyear>1920</birthyear>
<birthplace>Chicago</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale SALEID="12">
<TIMESTAMP>2005-07-10 11:56:00</TIMESTAMP>
<customer customerid="3">
<first_name>Susan</first_name>
<last_name>Green</last_name>
<email>susan.green@example.com</email>
</customer>
<album albumid="2">
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birthyear>1920</birthyear>
<birthplace>Chicago</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale SALEID="15">
<TIMESTAMP>2005-07-10 18:49:00</TIMESTAMP>
<customer customerid="9">
<first_name>Jack</first_name>
<last_name>Woo</last_name>
<email>jack.woo@example.com</email>
</customer>
<album albumid="2">
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birthyear>1920</birthyear>
<birthplace>Chicago</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</employee>
<employee Employeeid="9">
<Employeename>Pia Eist</Employeename>
<sales>
<sale SALEID="6">
<TIMESTAMP>2005-07-10 14:03:00</TIMESTAMP>
<customer customerid="6">
<first_name>Tim</first_name>
<last_name>Raw</last_name>
<email>tim.raw@example.com</email>
</customer>
<album albumid="1">
<title>New World Order</title>
<published_year>1990</published_year>
<comment>the best ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birthyear>1920</birthyear>
<birthplace>Chicago</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
<sale SALEID="13">
<TIMESTAMP>2005-07-10 13:12:00</TIMESTAMP>
<customer customerid="5">
<first_name>Will</first_name>
<last_name>Rippy</last_name>
<email>will.wippy@example.com</email>
</customer>
<album albumid="2">
<title>Curtis</title>
<published_year>1970</published_year>
<comment>that man ...</comment>
<artist artistid="1">
<name>Curtis Mayfield</name>
<birthyear>1920</birthyear>
<birthplace>Chicago</birthplace>
<genre>Soul</genre>
</artist>
</album>
</sale>
</sales>
</employee>
</employees>
</department>
<department departmentid="4">
<departmentSales>0</departmentSales>
<departmentEmployees>3</departmentEmployees>
<departmentname>Marketing</departmentname>
<department_head departmentHeadEmployeeid="10">
<departmentHeadEmployeename>Hanna Poll</departmentHeadEmployeename>
</department_head>
<employees>
<employee Employeeid="10">
<Employeename>Hanna Poll</Employeename>
<sales />
</employee>
<employee Employeeid="11">
<Employeename>Jim Wells</Employeename>
<sales />
</employee>
<employee Employeeid="12">
<Employeename>Sandra Wilson</Employeename>
<sales />
</employee>
</employees>
</department>
</store>
</music_company>
case08_debug.php
case08_debug.php:
<?php
class Mappers
{
public static function departmentMapper($str)
{
//maps 'one_two_three' to 'oneTwoThree'
return preg_replace("/(_)([a-z])/e", "strtoupper('\\2')", $str); }
public static function employeeMapper($str)
{
//maps 'one_two_three' to 'OneTwoThree'
}
public function saleMapper($str)
{
//maps 'one_two_three' to 'ONETWOTHREE'
}
}
function mapArtist($str)
{
//maps 'one_two_three' to 'onetwothree'
}
$myMappers = new Mappers();
require_once 'XML/Query2XML.php';
require_once 'XML/Query2XML/ISO9075Mapper.php';
require_once 'MDB2.php';
require_once 'Log.php';
$debugLogger = Log::factory('file', 'case08.log', 'XML_Query2XML');
$query2xml->enableDebugLog($debugLogger);
$query2xml->startProfiling();
$dom = $query2xml->getXML(
"SELECT
s.*,
manager.employeeid AS manager_employeeid,
manager.employeename AS manager_employeename,
d.*,
department_head.employeeid AS department_head_employeeid,
department_head.employeename AS department_head_employeename,
e.*,
sa.*,
c.*,
al.*,
ar.*,
(SELECT COUNT(*) FROM sale WHERE sale.store_id = s.storeid) AS store_sales,
(SELECT
COUNT(*)
FROM
sale, employee, employee_department
WHERE
sale.employee_id = employee.employeeid
AND
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = d.departmentid
) AS department_sales,
(SELECT
COUNT(*)
FROM
employee, employee_department, department
WHERE
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = department.departmentid
AND
department.store_id = s.storeid
) AS store_employees,
(SELECT
COUNT(*)
FROM
employee, employee_department
WHERE
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = d.departmentid
) AS department_employees
FROM
store s
LEFT JOIN employee manager ON s.manager = manager.employeeid
LEFT JOIN department d ON d.store_id = s.storeid
LEFT JOIN employee department_head ON department_head.employeeid = d.department_head
LEFT JOIN employee_department ed ON ed.department_id = d.departmentid
LEFT JOIN employee e ON e.employeeid = ed.employee_id
LEFT JOIN sale sa ON sa.employee_id = e.employeeid
LEFT JOIN customer c ON c.customerid = sa.customer_id
LEFT JOIN album al ON al.albumid = sa.album_id
LEFT JOIN artist ar ON ar.artistid = al.artist_id",
array(
'rootTag' => 'music_company',
'rowTag' => 'store',
'idColumn' => 'storeid',
'mapper' => 'strtoupper',
'attributes' => array(
'storeid'
),
'elements' => array(
'store_sales',
'store_employees',
'manager' => array(
'idColumn' => 'manager_employeeid',
'attributes' => array(
'manager_employeeid'
),
'elements' => array(
'manager_employeename'
)
),
'address' => array(
'elements' => array(
'country',
'state' => '#Helper::getStatePostalCode()',
'city',
'street',
'phone'
)
),
'department' => array(
'idColumn' => 'departmentid',
'mapper' => 'Mappers::departmentMapper',
'attributes' => array(
'departmentid'
),
'elements' => array(
'department_sales',
'department_employees',
'departmentname',
'department_head' => array(
'idColumn' => 'department_head_employeeid',
'attributes' => array(
'department_head_employeeid'
),
'elements' => array(
'department_head_employeename'
)
),
'employees' => array(
'rootTag' => 'employees',
'rowTag' => 'employee',
'idColumn' => 'employeeid',
'mapper' => array('Mappers', 'employeeMapper'),
'attributes' => array(
'employeeid'
),
'elements' => array(
'employeename',
'sales' => array(
'rootTag' => 'sales',
'rowTag' => 'sale',
'idColumn' => 'saleid',
'mapper' => array($myMappers, 'saleMapper'),
'attributes' => array(
'saleid'
),
'elements' => array(
'timestamp',
'customer' => array(
'idColumn' => 'customerid',
'mapper' => false,
'attributes' => array(
'customerid'
),
'elements' => array(
'first_name',
'last_name',
'email'
)
),
'album' => array(
'idColumn' => 'albumid',
'mapper' => 'XML_Query2XML_ISO9075Mapper::map',
'attributes' => array(
'albumid'
),
'elements' => array(
'title',
'published_year',
'comment' => '?#Helper::summarizeComment(12)',
'artist' => array(
'idColumn' => 'artistid',
'mapper' => 'mapArtist',
'attributes' => array(
'artistid'
),
'elements' => array(
'name',
'birth_year',
'birth_place',
'genre'
)
)
) // album elements
) //album array
) //sales elements
) //sales array
) //employees elements
) //employees array
) //department elements
) // department array
) //root elements
) //root
); //getXML method call
$root = $dom->firstChild;
$root->setAttribute('date_generated', date("Y-m-d\TH:i:s", 1124801570));
header('Content-Type: application/xml');
$dom->formatOutput = true;
print $dom->saveXML();
require_once 'File.php';
$fp = new File();
$fp->write('case08.profile', $query2xml->getProfile(), FILE_MODE_WRITE);
/**Static class that provides validation and parsing methods for
* generating XML.
*
* It is static so that we can easyly call its methods from inside
* Query2XML using eval'd code.
*/
class Helper
{
/**Associative array of US postal state codes*/
public static $statePostalCodes = array(
'ALABAMA' => 'AL', 'ALASKA' => 'AK', 'AMERICAN SAMOA' => 'AS', 'ARIZONA' => 'AZ', 'ARKANSAS' => 'AR', 'CALIFORNIA' => 'CA',
'COLORADO' => 'CO', 'CONNECTICUT' => 'CT', 'DELAWARE' => 'DE', 'DISTRICT OF COLUMBIA' => 'DC', 'FEDERATED STATES OF MICRONESIA' => 'FM',
'FLORIDA' => 'FL', 'GEORGIA' => 'GA', 'GUAM' => 'GU', 'HAWAII' => 'HI', 'IDAHO' => 'ID', 'ILLINOIS' => 'IL', 'INDIANA' => 'IN',
'IOWA' => 'IA', 'KANSAS' => 'KS', 'KENTUCKY' => 'KY', 'LOUISIANA' => 'LA', 'MAINE' => 'ME', 'MARSHALL ISLANDS' => 'MH', 'MARYLAND' => 'MD',
'MASSACHUSETTS' => 'MA', 'MICHIGAN' => 'MI', 'MINNESOTA' => 'MN', 'MISSISSIPPI' => 'MS', 'MISSOURI' => 'MO', 'MONTANA' => 'MT',
'NEBRASKA' => 'NE', 'NEVADA' => 'NV', 'NEW HAMPSHIRE' => 'NH', 'NEW JERSEY' => 'NJ', 'NEW JESEY' => 'NJ', 'NEW MEXICO' => 'NM', 'NEW YORK' => 'NY',
'NORTH CAROLINA' => 'NC', 'NORTH DAKOTA' => 'ND', 'NORTHERN MARIANA ISLANDS' => 'MP', 'OHIO' => 'OH', 'OKLAHOMA' => 'OK', 'OREGON' => 'OR',
'PALAU' => 'PW', 'PENNSYLVANIA' => 'PA', 'PUERTO RICO' => 'PR', 'RHODE ISLAND' => 'RI', 'SOUTH CAROLINA' => 'SC', 'SOUTH DAKOTA' => 'SD',
'TENNESSEE' => 'TN', 'TEXAS' => 'TX', 'UTAH' => 'UT', 'VERMONT' => 'VT', 'VIRGIN ISLANDS' => 'VI', 'VIRGINIA' => 'VA', 'WASHINGTON' => 'WA',
'WEST VIRGINIA' => 'WV', 'WISCONSIN' => 'WI', 'WYOMING' => 'WY'
);
/**Translates a US state name into its two-letter postal code.
* If the translation fails, $state is returned unchanged
* @param $record The record
*/
public static function getStatePostalCode($record)
{
$state = $record["state"];
if (isset(self::$statePostalCodes[$s])) {
return self::$statePostalCodes[$s];
} else {
return $state;
}}
function summarize($str, $limit=50, $appendString=' ...')
{
if (strlen($str) > $limit) {
$str =
substr($str, 0, $limit -
strlen($appendString)) .
$appendString; }
return $str;
}
function summarizeComment($record, $limit)
{
return self::summarize($record["comment"], $limit);
}
}
?>
case08.log
The format of a debug log file is documented at Logging and Debugging XML_Query2XML.
Our debug log contains just a single query:
Apr 20 13:33:47 XML_Query2XML [info] QUERY: SELECT
s.*,
manager.employeeid AS manager_employeeid,
manager.employeename AS manager_employeename,
d.*,
department_head.employeeid AS department_head_employeeid,
department_head.employeename AS department_head_employeename,
e.*,
sa.*,
c.*,
al.*,
ar.*,
(SELECT COUNT(*) FROM sale WHERE sale.store_id = s.storeid) AS store_sales,
(SELECT
COUNT(*)
FROM
sale, employee, employee_department
WHERE
sale.employee_id = employee.employeeid
AND
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = d.departmentid
) AS department_sales,
(SELECT
COUNT(*)
FROM
employee, employee_department, department
WHERE
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = department.departmentid
AND
department.store_id = s.storeid
) AS store_employees,
(SELECT
COUNT(*)
FROM
employee, employee_department
WHERE
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = d.departmentid
) AS department_employees
FROM
store s
LEFT JOIN employee manager ON s.manager = manager.employeeid
LEFT JOIN department d ON d.store_id = s.storeid
LEFT JOIN employee department_head ON department_head.employeeid = d.department_head
LEFT JOIN employee_department ed ON ed.department_id = d.departmentid
LEFT JOIN employee e ON e.employeeid = ed.employee_id
LEFT JOIN sale sa ON sa.employee_id = e.employeeid
LEFT JOIN customer c ON c.customerid = sa.customer_id
LEFT JOIN album al ON al.albumid = sa.album_id
LEFT JOIN artist ar ON ar.artistid = al.artist_id
Apr 20 13:33:47 XML_Query2XML [info] DONE
case08.profile
The format of the output is documented under
XML_Query2XML::getProfile():
FROM_DB FROM_CACHE CACHED AVG_DURATION DURATION_SUM SQL
1 0 false 0.0101430416 0.0101430416 SELECT
s.*,
manager.employeeid AS manager_employeeid,
manager.employeename AS manager_employeename,
d.*,
department_head.employeeid AS department_head_employeeid,
department_head.employeename AS department_head_employeename,
e.*,
sa.*,
c.*,
al.*,
ar.*,
(SELECT COUNT(*) FROM sale WHERE sale.store_id = s.storeid) AS store_sales,
(SELECT
COUNT(*)
FROM
sale, employee, employee_department
WHERE
sale.employee_id = employee.employeeid
AND
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = d.departmentid
) AS department_sales,
(SELECT
COUNT(*)
FROM
employee, employee_department, department
WHERE
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = department.departmentid
AND
department.store_id = s.storeid
) AS store_employees,
(SELECT
COUNT(*)
FROM
employee, employee_department
WHERE
employee_department.employee_id = employee.employeeid
AND
employee_department.department_id = d.departmentid
) AS department_employees
FROM
store s
LEFT JOIN employee manager ON s.manager = manager.employeeid
LEFT JOIN department d ON d.store_id = s.storeid
LEFT JOIN employee department_head ON department_head.employeeid = d.department_head
LEFT JOIN employee_department ed ON ed.department_id = d.departmentid
LEFT JOIN employee e ON e.employeeid = ed.employee_id
LEFT JOIN sale sa ON sa.employee_id = e.employeeid
LEFT JOIN customer c ON c.customerid = sa.customer_id
LEFT JOIN album al ON al.albumid = sa.album_id
LEFT JOIN artist ar ON ar.artistid = al.artist_id
TOTAL_DURATION: 0.36341714859009
DB_DURATION: 0.20340394973755
Final Notes on the case studies
The files of all cases are installed in $PHP_PEAR_DOC_DIR/XML_Query2XML/cases/.
SQL DDL used in all examples
In all the examples a MySQL database created with the SQL DDL shown below is used.
You might also want to have a look at the
ER diagram.
Note: the unit tests by default use the SQLite 2 database found at
$PHP_PEAR_TEST_DIR/XML_Query2XML/tests/Query2XML_Tests. If you want to run
all unit tests you have to install
the MySQL DDL $PHP_PEAR_TEST_DIR/XML_Query2XML/tests/Query2XML_Tests.sql
or the PostgreSQL DDL $PHP_PEAR_TEST_DIR/XML_Query2XML/tests/Query2XML_Tests.psql.
Please we use a certain naming convention for primary and foreign keys:
- For primary keys we use "<table_name>id".
- For foreign keys we use "<foreign_table_name>_id".
CREATE DATABASE Query2XML_Tests;
USE Query2XML_Tests;
CREATE TABLE artist (
artistid INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
birth_year Int,
birth_place VARCHAR(255),
genre VARCHAR(255),
UNIQUE (artistid),
PRIMARY KEY (artistid)
);
CREATE TABLE customer (
customerid INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(255),
last_name VARCHAR(255),
email VARCHAR(255),
UNIQUE (customerid),
PRIMARY KEY (customerid)
);
CREATE TABLE album (
albumid INT NOT NULL AUTO_INCREMENT,
artist_id INT NOT NULL,
title VARCHAR(255),
published_year Int,
comment VARCHAR(255),
UNIQUE (albumid),
PRIMARY KEY (albumid),
FOREIGN KEY (artist_id) REFERENCES artist (artistid)
);
CREATE TABLE employee (
employeeid INT NOT NULL AUTO_INCREMENT,
employeename VARCHAR(255),
UNIQUE (employeeid),
PRIMARY KEY (employeeid)
);
CREATE TABLE store (
storeid INT NOT NULL AUTO_INCREMENT,
manager INT NOT NULL,
country VARCHAR(255),
state VARCHAR(255),
city VARCHAR(255),
street VARCHAR(255),
phone VARCHAR(255),
building_xmldata TEXT,
UNIQUE (storeid),
PRIMARY KEY (storeid),
FOREIGN KEY (manager) REFERENCES employee (employeeid)
);
CREATE TABLE department (
departmentid INT NOT NULL AUTO_INCREMENT,
store_id INT NOT NULL,
department_head INT NOT NULL,
departmentname VARCHAR(255),
UNIQUE (departmentid),
PRIMARY KEY (departmentid),
FOREIGN KEY (department_head) REFERENCES employee (employeeid),
FOREIGN KEY (store_id) REFERENCES store (storeid)
);
CREATE TABLE employee_department (
employee_id INT NOT NULL,
department_id INT NOT NULL,
PRIMARY KEY (employee_id,department_id),
FOREIGN KEY (employee_id) REFERENCES employee (employeeid),
FOREIGN KEY (department_id) REFERENCES department (departmentid)
);
CREATE TABLE sale (
saleid INT NOT NULL AUTO_INCREMENT,
album_id INT NOT NULL,
customer_id INT NOT NULL,
employee_id INT NOT NULL,
store_id INT NOT NULL,
timestamp Timestamp(14),
UNIQUE (saleid),
PRIMARY KEY (saleid),
FOREIGN KEY (employee_id) REFERENCES employee (employeeid),
FOREIGN KEY (album_id) REFERENCES album (albumid),
FOREIGN KEY (customer_id) REFERENCES customer (customerid),
FOREIGN KEY (store_id) REFERENCES store (storeid)
);
INSERT INTO artist (artistid, name, birth_year, birth_place, genre) VALUES(1, 'Curtis Mayfield', 1920, 'Chicago', 'Soul');
INSERT INTO artist (artistid, name, birth_year, birth_place, genre) VALUES(2, 'Isaac Hayes', 1942, 'Tennessee', 'Soul');
INSERT INTO artist (artistid, name, birth_year, birth_place, genre) VALUES(3, 'Ray Charles', 1930, 'Mississippi', 'Country and Soul');
INSERT INTO album (albumid, artist_id, title, published_year, comment) VALUES(1, 1, 'New World Order', 1990, 'the best ever!');
INSERT INTO album (albumid, artist_id, title, published_year, comment) VALUES(2, 1, 'Curtis', 1970, 'that man\'s got somthin\' to say');
INSERT INTO album (albumid, artist_id, title, published_year, comment) VALUES(3, 2, 'Shaft', 1972, 'he\'s the man');
INSERT INTO customer (customerid, first_name, last_name, email) VALUES(1, 'Jane', 'Doe', 'jane.doe@example.com');
INSERT INTO customer (customerid, first_name, last_name, email) VALUES(2, 'John', 'Doe', 'john.doe@example.com');
INSERT INTO customer (customerid, first_name, last_name, email) VALUES(3, 'Susan', 'Green', 'susan.green@example.com');
INSERT INTO customer (customerid, first_name, last_name, email) VALUES(4, 'Victoria', 'Alt', 'victory.alt@example.com');
INSERT INTO customer (customerid, first_name, last_name, email) VALUES(5, 'Will', 'Rippy', 'will.wippy@example.com');
INSERT INTO customer (customerid, first_name, last_name, email) VALUES(6, 'Tim', 'Raw', 'tim.raw@example.com');
INSERT INTO customer (customerid, first_name, last_name, email) VALUES(7, 'Nick', 'Fallow', 'nick.fallow@example.com');
INSERT INTO customer (customerid, first_name, last_name, email) VALUES(8, 'Ed', 'Burton', 'ed.burton@example.com');
INSERT INTO customer (customerid, first_name, last_name, email) VALUES(9, 'Jack', 'Woo', 'jack.woo@example.com');
INSERT INTO customer (customerid, first_name, last_name, email) VALUES(10, 'Maria', 'Gonzales', 'maria.gonzales@example.com');
INSERT INTO employee (employeeid, employeename) VALUES(1, 'Michael Jones');
INSERT INTO employee (employeeid, employeename) VALUES(2, 'Susi Weintraub');
INSERT INTO employee (employeeid, employeename) VALUES(3, 'Steve Hack');
INSERT INTO employee (employeeid, employeename) VALUES(4, 'Joan Kerr');
INSERT INTO employee (employeeid, employeename) VALUES(5, 'Marcus Roth');
INSERT INTO employee (employeeid, employeename) VALUES(6, 'Jack Mack');
INSERT INTO employee (employeeid, employeename) VALUES(7, 'Rita Doktor');
INSERT INTO employee (employeeid, employeename) VALUES(8, 'David Til');
INSERT INTO employee (employeeid, employeename) VALUES(9, 'Pia Eist');
INSERT INTO employee (employeeid, employeename) VALUES(10, 'Hanna Poll');
INSERT INTO employee (employeeid, employeename) VALUES(11, 'Jim Wells');
INSERT INTO employee (employeeid, employeename) VALUES(12, 'Sandra Wilson');
INSERT INTO store (storeid, manager, country, state, city, street, phone, building_xmldata) VALUES(1, 1, 'US', 'New York', 'New York', 'Broadway & 72nd Str', '123 456 7890', '<building><floors>4</floors><elevators>2</elevators><square_meters>3200</square_meters></building>');
INSERT INTO store (storeid, manager, country, state, city, street, phone, building_xmldata) VALUES(2, 2, 'US', 'New York', 'Larchmont', 'Palmer Ave 71', '456 7890', '<building><floors>2</floors><elevators>1</elevators><square_meters>400</square_meters></building>');
INSERT INTO department (departmentid, store_id, department_head, departmentname) VALUES(1, 1, 1, 'Sales');
INSERT INTO department (departmentid, store_id, department_head, departmentname) VALUES(2, 1, 4, 'Marketing');
INSERT INTO department (departmentid, store_id, department_head, departmentname) VALUES(3, 2, 7, 'Sales');
INSERT INTO department (departmentid, store_id, department_head, departmentname) VALUES(4, 2, 10, 'Marketing');
INSERT INTO employee_department (employee_id, department_id) VALUES(1, 1);
INSERT INTO employee_department (employee_id, department_id) VALUES(2, 1);
INSERT INTO employee_department (employee_id, department_id) VALUES(3, 1);
INSERT INTO employee_department (employee_id, department_id) VALUES(4, 2);
INSERT INTO employee_department (employee_id, department_id) VALUES(5, 2);
INSERT INTO employee_department (employee_id, department_id) VALUES(6, 2);
INSERT INTO employee_department (employee_id, department_id) VALUES(7, 3);
INSERT INTO employee_department (employee_id, department_id) VALUES(8, 3);
INSERT INTO employee_department (employee_id, department_id) VALUES(9, 3);
INSERT INTO employee_department (employee_id, department_id) VALUES(10, 4);
INSERT INTO employee_department (employee_id, department_id) VALUES(11, 4);
INSERT INTO employee_department (employee_id, department_id) VALUES(12, 4);
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (1, 1, 1, 1, 1, '2005-05-25 16:32:00');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (2, 2, 1, 7, 2, '2005-06-05 12:56:00');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (3, 3, 1, 2, 1, '2005-07-10 11:03:00');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (4, 4, 1, 8, 2, '2005-07-10 10:03:00');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (5, 5, 1, 3, 1, '2005-07-10 13:03:00');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (6, 6, 1, 9, 2, '2005-07-10 14:03:00');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (7, 7, 1, 1, 1, '2005-07-10 15:03:00');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (8, 8, 1, 7, 2, '2005-07-10 16:03:00');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (9, 9, 1, 2, 1, '2005-07-10 18:03:00');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (10, 10, 1, 8, 2, '2005-07-10 19:03:00');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (11, 1, 2, 7, 2, '2005-05-25 16:23:00');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (12, 3, 2, 8, 2, '2005-07-10 11:56:00');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (13, 5, 2, 9, 2, '2005-07-10 13:12:00');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (14, 7, 2, 7, 2, '2005-07-10 15:09:00');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (15, 9, 2, 8, 2, '2005-07-10 18:49:00');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (16, 2, 3, 1, 1, '2005-06-05 12:56:12');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (17, 4, 3, 2, 1, '2005-07-10 10:03:32');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (18, 6, 3, 3, 1, '2005-07-10 14:03:52');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (19, 8, 3, 1, 1, '2005-07-10 16:03:01');
INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (20, 10, 3, 2, 1, '2005-07-10 19:03:50');
