agile coding

Es gibt immer wieder die Anforderung eine Datenselektion als XML Struktur zu verwenden. Hierbei kann man entweder die Daten aus der Datenbankabfragen nehmen und im eigenen Programmcode zu XML konvertieren oder man macht die Formatierung der Ausgabe direkt im SQL Statement. MySQL biete eine menge an Funktionen zur Formatierung von Ausgaben. In diesem Beispiel nutze ich die MySQL Funktion Concat und formatiere das Ergebnis der Datenselektion direkt als XML.

Damit die ganze Anleitung etwas griffiger wird, nehmen wir in diesem Beispiel zur Grundlage eine Tabelle products. Hierfür erstellen wir zunächst die Tabelle mit ein paar Testdatensätzen:

CREATE TABLE IF NOT EXISTS `product` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `user_id` INT(10) DEFAULT NULL,
  `short_desc` VARCHAR(50) DEFAULT NULL,
  `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` TIMESTAMP NULL DEFAULT NULL,
  `deleted` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
 
INSERT INTO `product` (`id`, `user_id`, `short_desc`, `created`, `modified`, `deleted`) VALUES
	(1, 10, 'test product', '2013-12-06 18:40:29', '2013-12-11 09:15:36', NULL),
	(2, 20, 'special product', '2013-12-10 23:32:53', '2013-12-11 09:15:18', NULL),
	(3, 23, 'new product', '2013-12-11 09:14:48', '2013-12-11 09:15:05', NULL);

Jetzt schauen wir uns das SQL Statement mit den CONCAT Befehlen an:

Im Grunde wird eine „übliche“ Datenselektion mit SELECT ausgeführt. Der Befehl CONCAT verpackt lediglich die Daten mit einer frei definierbaren Zeichenkette. In diesem Fall wird einfach der Name der Spalte als XML Tag formuliert und ausgegeben. Die GROUP_CONCAT Funktion sorgt dafür das zu jedem Produkt alle Attribute gruppiert werden, bevor ein neue Zeile in der Ausgabe erreicht wird.

An dieser Stelle muss noch auf Spalten hingewiesen werden, die evtl. den Wert NULL enthalten können. Der Befehl CONCAT verschluckt einfach mal ganze Zeilen, wenn diese einen NULL Wert enthalten. Falls das beim angewendeten Datenbestand der Fall ist, muss man die Abfrage mit der Funktion IFNULL absichern. Am obengenannten Abfragebeispiel würde das dann bedeuten:

Führen wir die Abfrage aus, erhält man nicht wie gewohnt, mehre Spalten und mehrere Zeilen vom MySQL Server zurück, sondern es wird nur eine Zeile mit einer Spalte mit der Bezeichnung xml zurückgegeben. In dieser einen Spalte befindet sich die fertige XML Struktur:

In manchen Fällen ist es notwendig noch verschachtelte Daten in die XML Struktur zu integrieren. Nehmen wir mal den Fall, dass man zu jedem Produkt noch Optionen aus einer anderen Tabelle hinzufügen muss. Die geforderte XML Struktur soll das folgende ergeben:

Erstellen wir zunächst die Tabelle options:

CREATE TABLE `options` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`user_id` INT(10) NULL DEFAULT NULL,
	`short_desc` VARCHAR(50) NULL DEFAULT NULL,
	`product_id` INT(11) NULL DEFAULT NULL,
	`created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
	`modified` TIMESTAMP NULL DEFAULT NULL,
	`deleted` TIMESTAMP NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=6;

Damit wir die XML Daten so verschachtelt erhalten, müssen wir eine Subquery formulieren. Subqueries werden im allgemeinen nur genommen um einfache Rückgaben zu erhalten. MySQL unterbindet sogar die Rückgabe von mehreren Zeilen in einer Subquery und wirft den Fehler „Subquery returns more than 1 row“ . D.h. wir müssen das Ergebnis der Subquery bereits in die fertige XML Struktur verpacken und wiederum auf eine einzelne Zeile verdichten, um die Fehlerkonstellation zu umgehen. Zusätzlich müssen wir natürlich in die Subquery die id des aktuell abgefragten Produktes übergeben, damit wir nur die Optionen erhalten, die auch zu dem jeweiligen Produkt gehören. Für diese Anforderung definiert man eine lokale Variable und übergeben den aktuellen Wert der Spalte id in die lokale Variable, in diesem Beispiel varid:

SELECT
CONCAT('<?xml version="1.0" standalone="yes"?>\n<products>\n', 
GROUP_CONCAT('\t<product>\n',
CONCAT('\t\t<id>', @varid:=id , '</id>\n'), 
IFNULL(CONCAT('\t\t<user_id>', IFNULL(user_id, '') , '</user_id>\n' ), ''), 
IFNULL(CONCAT('\t\t<short_desc>', IFNULL(short_desc, '') , '</short_desc>\n' ), ''), 
IFNULL(CONCAT('\t\t<created>', IFNULL(created, '') , '</created>\n' ), ''), 
IFNULL(CONCAT('\t\t<modified>', IFNULL(modified, '') ,'</modified>\n'), ''), 
IFNULL(CONCAT('\t\t<options>\n',(
	SELECT 
	GROUP_CONCAT('\t\t\t<option>\n',
	CONCAT('\t\t\t\t<id>', id , '</id>\n'), 
	CONCAT('\t\t\t\t<user_id>', IFNULL(user_id, '') , '</user_id>\n' ), 
	CONCAT('\t\t\t\t<short_desc>', IFNULL(short_desc, '') , '</short_desc>\n' ), 
	CONCAT('\t\t\t\t<created>', IFNULL(created, '') , '</created>\n' ), 
	CONCAT('\t\t\t\t<modified>', IFNULL(modified, '') , '</modified>\n' ), 
	'\t\t\t</option>\n' SEPARATOR '')
	FROM options
	WHERE product_id = @varid
	AND deleted is NULL
	), '\t\t</options>\n' ),''
), 
'\t</product>\n' SEPARATOR ''),
'</products>\n') AS xml
FROM product
WHERE deleted is NULL;

Das gesamte Beispiel, mit allen Tabellen und Daten, kann man sich auf SQLFiddle gerne genau anschauen.

Die Daten als XML direkt von der Datenbank ausgeben zu lassen bedeutet im Grunde, dass man die Verarbeitung seiner Anwendung damit beschleunigen kann. Klar, wenn ich die Daten in einer Anwendung als SQL Ergebnis selektiere und anschliessen in das XML Format konvertieren muss, hat man sich bei dieser Variante ganz klar Zeit und Programmcode gespart.

Ein kleiner Hinweis bzgl. große Datenselektion muss noch angemerkt werden. Der MySQL Server definiert die maximale Paketgröße (die Größe der Rückgabe einer Anfrage z.B.) für GROUP_CONCAT Befehle auf eine bestimmt Größe. Die Globale Variable für die Größe der Pakete wird in group_concat_max_len definiert.

Man kann sich die aktuell eingestellt größe über den folgenden Befehl ansehen:

show variables like 'group_concat_max_len'

So und sollte der Wert für die Daten zu klein sein, kann mit dem folgenden Befehl die Größe des Paketes anpassen:

SET GLOBAL group_concat_max_len=1073741824;

Alternativ kann man die Globale Variable auch in der Konfigurationsdatei mysql.ini den für den Server generell anpassen. Bei Änderung der mysql.ini Datei muss der Server aber neugestartet werden, damit die Globalen Variablen neugeladen werden.