YapfHoofdpagina | Info | Hulp | FAQ (veelgestelde vragen) | Speciale pagina's | Aanmelden


Printervriendelijke versie | Voorbehoud | Privacybeleid

PostgreSQL en xslt

Uit Yapf

XML is een syntax om op een gestructureerde manier om gegevens door te geven. Meestal wordt dit gebruikt om gegevens uit te wisselen tussen verschillende servers. Een voordeel van XML boven andere formaten is dat het formaat voorspelbaar is. Let wel; de structuur van de data is niet voorspelbaar, maar hoe de structuur wordt opgebouwd is ligt vast is syntaxregels.

XSLT, XML Stylesheet Transformations. Met XSLT kun je de data uit een XML in welk willekeurig formaat laten gieten, gewoon *elk*, het is niet gebonden aan welke standaard uitvoer dan ook. Je kunt er HTML mee genereren, maar ook platte tekst en via PHP kun je er zelfs andere PHPscripts mee laten maken.

De leut van dit is dat PostgreSQL zowel functies heeft om XML te genereren als functies om XSLT te verwerken. Dat geeft de mogelijkheid om binnen in de database XML te laten genereren en dat binnen in de database via XSLT om te zetten naar b.v. HTML.

Waarom zou je dat willen? Performance en controle. Door alle data in de database op te halen en één XML uit te leveren voorkom je ene hoop overhead tussen de applicatie en de database. Het omzetten van de XML naar HTML in de database is een prettig bijkomstigheid voor de niet al te ingewikkelde XSLT sheets. Bij complexere sheets met subsheets wordt het al snel eenvoudiger om in PHP te doen, maar dat neemt niet weg dat je delen van de transformatie door de database zou kunnen laten doen.


Inhoud

Een XMLDocument genereren

PostgreSQL heeft een aantal simpele maar doeltreffende functies om XML mee te genereren. Zoals de handleiding aangeeft zijn het niet veel meer dan opgewerkte stringfuncties, maar XML is niets meer dan opgewerkte tekst.

Al deze functies zijn gedocumenteerd in de handleiding op www.postgresql.org


Een XML Document beginnen: XMLROOT()

Met XMLROOT() kan een XMLfragment van een rootnode worden voorzien om er een compleet document van te maken.

In dit voorbeeld is het frament een lege node genaamd mijnnode: 

SELECT xmlroot('<mijnnode></mijnnode>'), version '1.0', standalone yes);


en dat levert op: 


<?xml version="1.0" standalone="yes"?>
<mijnnode></mijnnode>

XML Nodes genereren: XMLELEMENT()

De nodes kun je genereren met XMLELEMENT(). Het neemt één, twee of drie argumenten, waarvan de eerste altijd een de naam doorgeeft, voorafgegaan door het keyword name:

Alles bij elkaar kun je nu al een compleet document neerzetten. Let op hoe de indenting van de query vrijwel identiek is aan die van de XML 

SELECT xmlroot(
  XMLELEMENT(name berichten,
    XMLATTRIBUTES(2 AS aantal),
    XMLELEMENT(name bericht,
      XMLELEMENT(name titel, 'Werken met XML'),
      XMLELEMENT(name body,'Lees hoe het moet.')
    ),
    XMLELEMENT(name bericht,
      XMLELEMENT(name titel, 'Wij lachen om MySQL'),
      XMLELEMENT(name body,'MySQL zuigt dikke naat')
    )
), version '1.0', standalone yes);


En dat geeft: 


<?xml version="1.0" standalone="yes"?>
<berichten aantal="2">
  <bericht>
    <titel>Werken met XML</titel>
    <body>Lees hoe het moet.</body>
  </bericht>
  <bericht>
    <titel>Wij lachen om MySQL</titel>
    <body>MySQL zuigt dikke naat</body>
  </bericht>
</berichten>

Data uit tabellen

Uiteraard kan ook data uit tabelen worden gebruikt voor de XML. Dit kan door de boel te koppelen aan een tabel:

Voor hen die thuis meedoen staat de data voor deze voorbeelden onderaan deze pagina.


Binnen de XMLELEMENT() kan de inhoud van het element worden aangegeven met de naam van de kolom waar de data in staat: 

SELECT 
  XMLELEMENT(name bericht,
    XMLELEMENT(name berichttitel, titel),
    XMLELEMENT(name inhoud, body)
  )
FROM
  nieuws;


En dat geeft twee records: 


<bericht><titel>Wij lachen om MySQL</titel><body>het is simpel en onbetrouwbaar</body></bericht>
 
<bericht><titel>Oracle is koel</titel><body>Het orakel is diep onder nul</body></bericht>

Records samenvoegen: XMLAGG()

Om meerdere records samen te voegen tot één fragment is er een aggregaatfunctie XMLAGG(): (de naam van de tag en de kolom zijn hier puur uit toeval hetzelfde!) 

SELECT
  XMLAGG(
    XMLELEMENT(name bericht,
      XMLELEMENT(name titel, titel),
      XMLELEMENT(name body, body)
    )
  )
FROM
  nieuws;


En dat geeft weer één record: 

<bericht>
  <titel>Wij lachen om MySQL</titel>
  <body>het is simpel en onbetrouwbaar</body>
</bericht>
<bericht>
  <titel>Oracle is koel</titel>
  <body>Het orakel is diep onder nul</body>
</bericht>

NOOT bij XMLAGG()

De uitkomst van de XMLAGG kan weer in een XMLELEMENT worden gezet, en omdat het een query is kan het aantal nu ook rechtstreeks uit een COUNT(*) komen. met een XMLROOT() er omheen heb je weer een compleet document: 

SELECT 
  XMLROOT(
    XMLELEMENT(name berichten,
      XMLATTRIBUTES(COUNT(*) AS aantal),
      XMLAGG(
        XMLELEMENT(name bericht,
          XMLELEMENT(name titel, titel),
          XMLELEMENT(name body, body)
        )
      )
    ), version '1.0', standalone yes)
FROM
  nieuws;


En dat geeft: 


<?xml version="1.0" standalone="yes"?>
  <berichten aantal="2">
    <bericht>
      <titel>Wij lachen om MySQL</titel>
      <body>het is simpel en onbetrouwbaar</body>
    </bericht>
    <bericht>
      <titel>Oracle is koel</titel>
      <body>Het orakel is diep onder nul</body>
    </bericht>
  </berichten>

Dit werkt omdat het een aggregaat is en er verder niets bijzonders wordt geselecteerd. Als je hier nu een LIMIT op probeert te zetten als er maar 1 bericht mag worden opgehaald dan mislukt dat, omdat de LIMIT werkt op de uitkomst van de aggregate, niet op de invoer.

Als het aantal resultaten in de XMLAGG() veranderd moet worden dan moet de data waar de aggregate mee werkt veranderd worden, en dat kun je ondere andere doen met een subquery: 

SELECT 
  XMLROOT(
    XMLELEMENT(name berichten,
      XMLATTRIBUTES(COUNT(*) AS aantal),
      XMLAGG(
        XMLELEMENT(name bericht,
          XMLELEMENT(name titel, titel),
          XMLELEMENT(name body, body)
        )
      )
    ), version '1.0', standalone yes)
FROM
  (SELECT * FROM nieuws LIMIT 1) AS brondata

XMLFOREST()

Met XMLFOREST() kan een willekeurig aantal waarden snel worden omgezet in een stel nodes. 


SELECT XMLFOREST('Hans' AS firstname, 'Kazan' AS lastname);

Geeft: 


<firstname>Hans</firstname><lastname>Kazan</lastname>


Je kunt ook verwijzen naar kolomnamen uit een tabel: 


SELECT XMLFOREST(titel,body) FROM nieuws


Dat geeft twee records: 


<titel>Wij lachen om MySQL</titel><body>het is simpel en onbetrouwbaar</body>
<titel>Oracle is koel</titel><body>Het orakel is diep onder nul</body>

Uiteraard kunnen de kolomnamen worden veranderd via een alias.

Noot dat er bij XMLFOREST() geen mogelijkheid is om attributen aan de nodes toe te voegen, het is echt bedoeld om snel veel simpele nodes te maken, zoals b.v. van een aantal kolommen uit een record.

XML transformeren: XSLT_PROCESS()

Een XML document transformeren gebeurt door het document samen met een XSLT sheet te geven aan XSLT_PROCESS().

XSLT_PROCESS(text XML, text XSLT)

Noot dat zowel de XML als de XSLT worden aangeleverd als type TEXT, niet als type XML.


Voorbeeld 1

Dit voorbeeld transformeert het XML fragment <mijnnode>hoi!</mijnnode> door de inhoud van mijnnnode te printen. 

SELECT 
  XSLT_PROCESS('
    <mijnnode>
      hoi!
    </mijnnode>'
  , 
    '<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version=''1.0''>
      <xsl:output method="html"/>
      <xsl:template match="/">
        <xsl:value-of select="mijnnode"/>
      </xsl:template>
    </xsl:stylesheet>'::text
  );

Geeft: 

hoi!

Voorbeeld 2

Met de nieuws-document query van hierboven en een stukje XSL kan PostgreSQL helemaal zelf een stuk HTML genereren: 

SELECT 
  XSLT_PROCESS(
    XMLROOT(
      XMLELEMENT(name berichten,
        XMLATTRIBUTES(COUNT(*) AS aantal),
        XMLAGG(
          XMLELEMENT(name bericht,
            XMLELEMENT(name titel, titel),
            XMLELEMENT(name body, body)
          )
        )
      ), version '1.0', standalone yes)::text
  ,
  '<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version=''1.0''>
    <xsl:output method="html"/>
      <xsl:template match="/">
        <html>
          <head>
            <title>Mijn nieuws</title></head>
          <body>
          <div>
	    <xsl:apply-templates/>
          </div>
        </body>
      </html>
    </xsl:template>
 
    <xsl:template match="berichten">
      <ul>
        <xsl:apply-templates select="bericht"/>
      </ul>
    </xsl:template>
 
    <xsl:template match="bericht">
      <li>
        <xsl:value-of select="titel"/>
      </li>
    </xsl:template>
 
  </xsl:stylesheet>'::text
)
FROM
  nieuws


Voorbeeld 3

Hetzelfde maar iets anders geschreven zodat de nieuws query een subquery is geworden: 

SELECT 
  XSLT_PROCESS(
    (SELECT
      XMLROOT(
        XMLELEMENT(name berichten,
          XMLATTRIBUTES(COUNT(*) AS aantal),
          XMLAGG(
            XMLELEMENT(name bericht,
              XMLELEMENT(name titel, titel),
              XMLELEMENT(name body, body)
            )
          )
        ), version '1.0', standalone yes)::text
      FROM
        nieuws
    )
  ,
  '<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version=''1.0''>
    <xsl:output method="html"/>
      <xsl:template match="/">
        <html>
          <head>
            <title>Mijn nieuws</title></head>
          <body>
          <div>
	    <xsl:apply-templates/>
          </div>
        </body>
      </html>
    </xsl:template>
 
    <xsl:template match="berichten">
      <ul>
        <xsl:apply-templates select="bericht"/>
      </ul>
    </xsl:template>
 
    <xsl:template match="bericht">
      <li>
        <xsl:value-of select="titel"/>
      </li>
    </xsl:template>
  </xsl:stylesheet>'::text
)

Refactoring naar Stored Functions en Views

Het opmaken van een XML document valt goed te refactoren naar stored functions die elk hun deel doen. Op die manier krijg je herbruikbare brokjes die je naar wens aan elkaar kunt knopen tot een werkend geheel. 


CREATE OR REPLACE FUNCTION "public"."xml_geefberichten" (p_limit integer, out out_xml text) RETURNS text AS
$body$
BEGIN
  SELECT
  	 INTO out_xml XMLELEMENT(name berichten,
          XMLAGG(XMLELEMENT(name bericht,
            XMLELEMENT(name titel, titel),
            XMLELEMENT(name body, body)
          )))
  FROM
    ( SELECT
        *
      FROM
        nieuws
      LIMIT
   	p_limit) AS temptable;   
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;


Dit geeft een XML fragment met een berichten node erin.


Samen met de functie xml_laatsteartikelen() kan hiermee weer een compleet document worden samengesteld: 

SELECT
  XMLROOT(
    xmlelement(name document,
    xml_laatstenieuws(2),
    xml_laatsteartikelen(1)
  )
  , version '1.0', standalone yes)


Dat document kan weer de bron zijn voor een transformatie: 


SELECT 
XSLT_PROCESS((SELECT
  XMLROOT(
    xmlelement(name document,
    xml_laatstenieuws(2),
    xml_laatsteartikelen(1)
  )
  , version '1.0', standalone yes))
::text
 ,
  '<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version=''1.0''>
    <xsl:output method="html"/>
      <xsl:template match="/">
        <html>
          <head>
            <title>Artikelen en nieuws</title></head>
          <body>
          <div>
	        <xsl:apply-templates/>
          </div>
        </body>
      </html>
    </xsl:template>
 
    <xsl:template match="berichten">
      <h1>Berichten</h1>
      <ul>
        <xsl:apply-templates select="bericht"/>
      </ul>
    </xsl:template>
 
    <xsl:template match="bericht">
      <li>
        <xsl:value-of select="titel"/>
      </li>
    </xsl:template>
 
    <xsl:template match="artikelen">
      <h1>Artikelen</h1>
      <ul>
        <xsl:apply-templates select="artikel"/>
      </ul>
    </xsl:template>
 
    <xsl:template match="artikel">
      <li>
        <xsl:value-of select="titel"/>
      </li>
    </xsl:template>
 
  </xsl:stylesheet>'::text
)


Door de sheet in een tabel te zetten wordt het geheel erg compact: 


SELECT 
  XSLT_PROCESS(
    xml_geefberichten(2)::text
   ,(SELECT
      xsltdata
     FROM
      xslt_sheets
     WHERE
      sheetname='berichten')
  )


En daar komt in principe dus een compleet HTML document uit.

Voorbeeld met vertalingen

Het volgende is een niet bijzonder goed voorbeeld van hoe data uit een tabel automatisch in een tekst verwerkt kan worden. Het gaat uit van een tabel met teksten in meerdere talen, die via een naam en een taalcode worden opgehaald.

Het principe is simpel: genereer een XML met daarin de vertalingen van de benodigde teksten en geef dat samen met de template aan een XSLT parser.

In dit voorbeeld worden simpeweg alle teksten opgehaald, in een praktische toepassing zou je uiteraard alleen die teksten ophalen die je ook daadwerkelijk nodig hebt.


Het eerste deel is een functie die een XML document maakt met de teksten in de gewenste taal. 


CREATE OR REPLACE FUNCTION "public"."xml_geefvertalingen" (p_taal char, out out_xml xml) RETURNS xml AS
$body$
BEGIN
  SELECT
  	 INTO out_xml XMLELEMENT(name vertalingen,
          XMLAGG(XMLELEMENT(name vertaling, XMLATTRIBUTES(naam AS naam), inhoud)))
  FROM
   vertalingen
  WHERE
  	taal=p_taal;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;


Het tweede deel is een XSLT sheet die vertaalde teksten gebruikt. Let op dat de teksten worden opgehaald via een attribuut naam. De taal wordt gekozen bij het genereren van de XML. 


<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="html"/>
      <xsl:template match="/">
        <html>
          <head>
            <title>
               <xsl:value-of select="//vertaling[@naam='groet']"/>
            </title>
          </head>
          <body>
          <p>
            <xsl:value-of select="//vertaling[@naam='koptekst']"/>
          </p>
          <p>
            <xsl:value-of select="//vertaling[@naam='hoofdtekst']"/>
          </p>
	 </body>
      </html>
    </xsl:template>
</xsl:stylesheet>


Met deze XSLT in de sheetstabel en de functie van hierboven komt dat neer op het volgende en daar komt weer HTML uit, het Nederlands voor 'nl' of Engels voor 'en'. 


SELECT 
  XSLT_PROCESS(
    xml_geefvertalingen('nl')::text
   ,(SELECT
      xsltdata
     FROM
      xslt_sheets
     WHERE
      sheetname='vertaalpagina')
  )


En nogmaals, dit is geen geweldig voorbeeld maar het is een illustratie van de gedachte.

Performance

Het genereren van de XML is in veel gevallen al vrij snel, millisecondenwerk. Echter als de brondata niet verandert zal de XML ook niet veranderen en is het dus niet nodig om het opnieuw te genereren. Wanneer de XML fragmenten worden aangemaakt via stored functions dan is het een kleine moeite om daar een cache in te bouwen. Genereer de XML en zet het in een cachetabel. Zet een trigger op de brontabellen die na elke update de XML cache leegmaken. Simpel en doeltreffend.

Afhankelijk van hoeveel performance je nodig hebt en hoe vaak de data wordt bijgewerkt kun je de gecachte XML fragmenten groter maken, tot complete pagina's aan toe en dan kun je nog overgaan op het cachen van de HTML zelf zodat je de XSLT ook over kunt slaan. Qua performance is alleen een statisch HTML bestand nog echt sneller. Heb ik al een wiki over het schrijven van bestanden vanuit PostgreSQL?

Uiteraard kun je de XML ook via PHP's XSLT processor laten verwerken, of via een willekeurige andere XSLT processor.

Installatie

De installatie procedure voor de XML functis staat beschreven in de handleiding van PostgreSQL. Het vereist libxml en libxsl, welke beiden voor vrijwel elk OS beschikbaar zijn via de standaard installatiemethoden. (yum, apt etc)

De voorbeelddata 

CREATE TABLE "public"."nieuws" (
  "berichtid" INTEGER DEFAULT NEXTVAL('nieuws_berichtid_seq'::regclass) NOT NULL, 
  "titel" TEXT, 
  "body" TEXT, 
  "datum" TIMESTAMP WITHOUT TIME ZONE
);
 
 
INSERT INTO "public"."nieuws" ("berichtid", "titel", "body", "datum")
VALUES (1, 'Wij lachen om MySQL', 'het is simpel en onbetrouwbaar', '2010-02-22 12:47:11');
 
INSERT INTO "public"."nieuws" ("berichtid", "titel", "body", "datum")
VALUES (2, 'Oracle is koel', 'Het orakel is diep onder nul', '2010-02-11 00:00:00');
 
 
CREATE TABLE "public"."artikelen" (
  "artikelid" INTEGER, 
  "titel" TEXT, 
  "body" TEXT
) WITH OIDS;
 
 
INSERT INTO "public"."artikelen" ("artikelid", "titel", "body")
VALUES (1, 'xslt en pgsql', NULL);
 
INSERT INTO "public"."artikelen" ("artikelid", "titel", "body")
VALUES (2, 'Migreren van MySQL naar PgSQL', NULL);
 
 
 
 
 
CREATE TABLE "public"."xslt_sheets" (
  "sheetname" TEXT, 
  "xsltdata" TEXT
) WITH OIDS;
 
 
 
INSERT INTO "public"."xslt_sheets" ("sheetname", "xsltdata")
VALUES ('berichten', '<xsl:stylesheet xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\" version=''''1.0''''>\r\n    <xsl:output method=\"html\"/>\r\n      <xsl:template match=\"/\">\r\n        <html>\r\n          <head>\r\n            <title>Artikelen en nieuws</title></head>\r\n          <body>\r\n          <div>\r\n\t        <xsl:apply-templates/>\r\n          </div>\r\n        </body>\r\n      </html>\r\n    </xsl:template>\r\n    \r\n    <xsl:template match=\"berichten\">\r\n      <h1>Berichten</h1>\r\n      <ul>\r\n        <xsl:apply-templates select=\"bericht\"/>\r\n      </ul>\r\n    </xsl:template>\r\n \r\n    <xsl:template match=\"bericht\">\r\n      <li>\r\n        <xsl:value-of select=\"titel\"/>\r\n      </li>\r\n    </xsl:template>\r\n\r\n    <xsl:template match=\"artikelen\">\r\n      <h1>Artikelen</h1>\r\n      <ul>\r\n        <xsl:apply-templates select=\"artikel\"/>\r\n      </ul>\r\n    </xsl:template>\r\n \r\n    <xsl:template match=\"artikel\">\r\n      <li>\r\n        <xsl:value-of select=\"titel\"/>\r\n      </li>\r\n    </xsl:template>\r\n\r\n  </xsl:stylesheet>');
 
INSERT INTO "public"."xslt_sheets" ("sheetname", "xsltdata")
VALUES ('vertaalpagina', '<xsl:stylesheet xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\" version=\"1.0\">\r\n    <xsl:output method=\"html\"/>\r\n      <xsl:template match=\"/\">\r\n        <html>\r\n          <head>\r\n            <title><xsl:value-of select=\"//vertaling[@naam=''groet'']\"/></title></head>\r\n          <body>\r\n          <p>\r\n          <xsl:value-of select=\"//vertaling[@naam=''koptekst'']\"/>\r\n          </p>\r\n          <p>\r\n          <xsl:value-of select=\"//vertaling[@naam=''hoofdtekst'']\"/>\r\n          </p>\r\n\t </body>\r\n      </html>\r\n    </xsl:template>\r\n</xsl:stylesheet>');
 
 
 
 
 
CREATE TABLE "public"."vertalingen" (
  "naam" TEXT, 
  "inhoud" TEXT, 
  "taal" CHAR(2)
) WITH OIDS;
 
 
 
INSERT INTO "public"."vertalingen" ("naam", "inhoud", "taal")
VALUES ('groet', 'Hallo daar!', 'nl');
 
INSERT INTO "public"."vertalingen" ("naam", "inhoud", "taal")
VALUES ('groet', 'Hello there', 'en');
 
INSERT INTO "public"."vertalingen" ("naam", "inhoud", "taal")
VALUES ('koptekst', 'Dit is de inhoud van de koptekst, het stelt niet veel voor maar we doen het er maar mee.', 'nl');
 
INSERT INTO "public"."vertalingen" ("naam", "inhoud", "taal")
VALUES ('koptekst', 'This is the headertext. It''s not much but it''ll have to do.', 'en');
 
INSERT INTO "public"."vertalingen" ("naam", "inhoud", "taal")
VALUES ('hoofdtekst', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras hendrerit eleifend lacus, ut blandit turpis mattis vitae. Sed vulputate, risus et convallis vulputate, lorem sapien commodo dolor, ac scelerisque elit augue at eros. ', 'nl');
 
INSERT INTO "public"."vertalingen" ("naam", "inhoud", "taal")
VALUES ('hoofdtekst', 'Nullam luctus ipsum non enim laoreet viverra. Sed vulputate facilisis scelerisque.  ', 'en');

Ontvangen van "http://www.yapf.net/index.php/PostgreSQL_en_xslt"

Deze pagina is 4.584 maal bekeken. Deze pagina is het laatst bewerkt op 28 feb 2010 om 13:13.


Zoeken

Bladeren
Hoofdpagina
Gebruikersportaal
In het nieuws
Recente wijzigingen
Willekeurige pagina
Hulp
Bewerken
Brontekst bekijken
Hulp bij bewerken
Paginaopties
Overlegpagina
Nieuw kopje
Printervriendelijke versie
Pagina-informatie
Paginageschiedenis
Verwijzingen naar deze pagina
Verwante wijzigingen
Mijn pagina's
Aanmelden / registreren
Speciale pagina’s
Nieuwe pagina's
Bestandslijst
Statistieken
Meer…