PL/Perl, PostgreSQL (2) aneb jak na aplikační server

Tímto článkem navazuji na svůj článek Krátce o PL/Perlu, který vyšel na Root.cz v loni v březnu. Stejně jako posledně se nezaměřím na samotný Perl (nepřišel bych s ničím novým) ani na techniku psaní a používání uložených procedur. K obojímu je dostatek dokumentace na Internetu. Zatímco vloni mne inspirovalo k sepsání článku úspěšné použití regulárních výrazů, inspirací k tomuto článku byl modul dbilink. Tento modul umožňuje přímý přístup z PostgreSQL do všech databází, ke kterým existuje DBI rozhraní. A i když i s tímto modulem má PostgreSQL daleko ke konfortu připojených databází MsSQL nebo Oraclu, nepochybuji o jeho užitečnosti.

Zdrojové kódy: clean.sql, nic.sql, trigger.sql. Příklady jsou určeny pro PostgreSQL verze 8.0.0.

Je to otázka do diskuse, zdali má mít databáze přístup do externích zdrojů, ať už na úrovni pouhých souborů, nebo na úrovni aplikačních služeb. Říkám si proč ne. Od této chvíle pro mne PostgreSQL přestává být RDBMS a stává se v podstatě aplikačním serverem. O co jde? Jinými slovy: přesouvám část kódu z „bussines vrstvy“ (neinteraktivní) ještě níže do databáze. O co zjednoduším bussines vrstvu, o to zkomplikuji db vrstvu. Tímto přesunem důrazu na jednu nebo druhou vrstvu se nezmění pracnost vývoje aplikace. Určité množství kódu řešící danou problematiku je třeba napsat a toto množství není obyčejně závislé na architektuře aplikace.

Na chvíli odbočím. Co si představuji pod pojmem aplikační server? Aplikační server (dále AS) je kombinace sw a hw prostředků umožňujících aktivaci a organizaci informačních služeb. AS zprostředkovává IO jednotným způsobem mezi zvenčí dostupnými službami, umožňuje vzájemnou komunikaci mezi informačními službami, jejich jednotnou parametrizaci, automatizaci spouštění, plánování, dohled atd. Když se zamyslíte nad touto definicí, zjistíte, že se do ní vejde prakticky cokoliv z IT, a není to nic překvapujícího. Nikoliv nejnovější myšlenkou je použití konceptu AS i pro realizaci zákaznických aplikací, tj. nevytvářím monolit, ale sadu aplikací (služeb), které budou jednotně komunikovat mezi sebou navzájem i s uživateli. Aplikační kontejner je pro mne synonymem aplikačního serveru. Příkladů ze života je více: čistě akedemický projekt Oberon, EJB, Zope na bázi Pythonu, do jisté míry třeba i Apache a PHP.

Pokud akceptuji ideu „nejdůležitější jsou data a jejich toky“, mohu se smířit i s myšlenkou AS na úrovni databáze. Kdo viděl, co se všechno dokáže např. s Data transformation services firma Microsoft, mi dá jistě za pravdu. U zmiňovaných DTS se nedá přímo mluvit o AS: MsSQL nemá sám o sobě přístup k externím zdrojům (musí se použít vbscript a COM), v databázi je ale uložena definice služeb, navrhovat DTS lze v administračním rozhraní databáze, databáze se stará o spouštění a monitorování DTS. Firma Microsoft jeden čas prosazovala myšlenku „vše je datový zdroj“ – prostřednictvím ADODB lze k mnoha údajům přistupovat jako k databázi, např. pro Ms Exchange existovala jak nativní COM knihovna, tak ADODB ovladač, takže bylo možné zpracovávat maily prostřednictvím aplikací s ADODB rozhraním atd. Obdoba ADODB v Linuxu není. Do jisté míry jej může nahradit (v prostředí PostgreSQL) SPI v PL/Perlu. Perl v podstatě degraduji jen na lepidlo (analogie vbscriptu), kterým spojuji CPAN moduly a kterým konvertuji vstup a výstup modulů.

Jak vytvořím AS z PostgreSQL? Jednoduše. Uložené procedury prohlásím za jednotlivé aplikace (služby). Službu aktivuji spuštěním příkazu SELECT. Parametry volané funkce parametrizují aktivovanou službu. Výstup je ve formátu tabulky, pole nebo skaláru. Co mne vede k tomu, že přenáším funkcionalitu z aplikace do uložených procedur? Předně: všude, kde se dostanu k databázi (ať nativním protokolem, ODBC, ADODB atd.), mohu aktivovat službu. Dále na úrovni databáze mohu nastavit přístupová práva (kdo může spouštět službu, k jakým zdrojům má služba přístup). Navíc ve většině případů kompatibilita datových typů mezi databázemi i mezi platformami dnes již není problém – nemusím se starat o formát dat. Překódování, správné zobrazení, přizpůsobení národním zvyklostem mi tiše a neviditelně zajistí databázová vrstva. Konečně, pro filtrování, formátování výstupů mohu použít jazyk SQL. Jaké mohu čekat nevýhody: Předně komplikovanější ladění, dále určitou režii navíc způsobenou nutností překódování výstupních hodnot pro SQL (různých od nativních typů Perlu, C). Také větší zatížení serveru, menší distribuci zatížení na klienty nebo server s bussines vrstvou. Konečně větší závislosti na dané databázové platformě.  

Kombinace Perl a PostgreSQL s sebou nese i určitá omezení z větší části daná určitou nevyspělostí PL/Perlu v PostgreSQL. Rozhraní pro tento jazyk se dlouho nevyvíjelo, teprve v poslední verzi 8.0. se na něm opět začalo pracovat. PL/Perl není vhodný pro přenos velkých tabulek. Pokud se vrací SETOF record, pak funkce v Perlu nejdříve sestaví pole hashů obsahující celou výslednou množinu záznamů, a toto pole se předá PostgreSQL (naproti tomu PL/plPGSQL předává hodnoty po jednom záznamu). I když v dokumentaci je toto omezení zmíněno, nebyl problem přenést tabulku kontaktů (pro názornost) o pěti sloupcích (jméno, příjmení, adresa, …) a třech tisících řádcích. Dále PL/Perl nepodporuje přímo pole. Je třeba sestavovat specifický řetězec z hodnot v poli.

Základem každého aplikačního serveru jsou služby. Přiznám se, že popisované a implementované služby nemají jednotný charakter. Nanavrhoval jsem je, abych řešil určitý konkrétní úkol, ale abych si vyzkoušel možnosti PL/Perlu nebo si zjednodušil život a administraci serveru. Rád se nechám inspirovat vašimi návrhy. CPAN je pro mne hlubinou vědění.

1. zpřístupnění seznamu uživatelů o.s.
2. přístup k libovolnému souboru (např. seznamu procesů)
3. zpracování logu
4. získání uuid
5. univerzalní notifikace o změne emailem
6. přístup k informacím o procesoru
7. přístup k údajům o procesu PostgreSQL
8. dohledání údajů v externí bázi (SOAP, báze Amazon)
9. připojení tabulky z jiné databáze (mysql via DBI)
10. získání informací z externí báze (http, HTML)
11. univerzální generování XML na základě dotazu
12. univerzální import XML

Většina služeb jsem napsal, abych si vyzkoušel a demonstroval jeden určitý druh komunikace v Perlu, a jejich užitek je diskutabilní, pokud je vůbec nějaké. Za užitečné považuji: zpřístupnění seznamu uživatelů (administrace), přístup k libovolnému souboru (import dat), univerzální notifikaci (vývoj), připojení tabulek z jiné databáze (zatím pomalé, ale i tak zeefektivní aktualizace), generování XML.

Asi jste poznali, že pro architekturu AS hlasuji všemi deseti u všech byť jen trochu složitějších projektů, a jestli bude použita technologie Java (Tomcat), nebo SQL (PostgreSQL, Firebird), není podstatné. Na druhou stranu rozumím pouze PostgreSQL, takže pro hraní používám tuto RDB. Navíc Javě nerozumím. Psát pro Tomcat mi přijde výrazně složitější – samozřejmě, že možnosti AS v Javě jsou někde úplně jinde. Podstatná je změna v návrhu aplikace, resp. její dekompozice na interaktivní a neinteraktivní bloky a uplatnění pravidla maximálního re-use při implementaci neinteraktivní čás­ti.

1. kategorie: služby vycházející z parsování souboru

Jedná se o nejjednodušší skupinu služeb. Po otevření souboru se zpracovává každý jeho řádek. Výsledkem může být tabulka záznamů (log, passwd, cpuinfo, status) nebo hodnota (uuid). Vstupem může být skutečný soubor (log, passwd), roura (parametr „ps |“) nebo virtuální soubory z /proc/ jmenného prostoru (status, uuid, cpuinfo). Pokud stojí na kódu něco za povšimnutí, pak jen způsob generování výstupního pole SETOF funkcí. Dále je třeba zmínit bezpečnost, resp. nebezpečnost těchto služeb. Pro přístup k souborovému systému je nezbytné použít untrusted perl. Ten může použít pouze db superuser. Funkcí lines dáváme uživatelům možnost otevřít si libovolný soubor s právy superusera!

CREATE OR REPLACE FUNCTION ext.rtab(varchar, varchar) RETURNS SETOF RECORD AS $$
   open FILE, $_[0]; my @cntn = ();
   while (<FILE>) { chop;
      my @items = map {s/^\s+|\s+$//g; $_;}  split ($_[1]);
      my %iitems; @iitems{map 'col'.$_, 0..$#items} = @items;
      push @cntn, \%iitems; }
   close FILE;
   return \@cntn;
$$ LANGUAGE plperlu;

CREATE VIEW ext.passwd AS SELECT col0 AS account, col1 AS passwd, col2 AS uid,
       col3 AS gid, col4 AS description, col5 AS home,
       col6 AS shell FROM ext.rtab('/etc/passwd',':')
  AS (col0  varchar, col1 varchar, col2 integer,
      col3 integer, col4 varchar, col5 varchar,
      col6 varchar);

2. kategorie: Připojení tabulky z jiné databáze

I přes zmiňovanou neefektivnost nepochybuji o užitečnosti. Alespoň v tomto okamžiku uživatelé PostgreSQL žádnou jinou možnost, jak si zpřístupnit jiné ne-postgresql databáze, nemají. Díky perlu a jeho rozhraní je snadné se připojit k Oraclu, MsSQL, mySQL, Informix, Firebirdu (stačí jen změnit DNS). Alelujá. Ostatní si mohou jen nechat zdát. Použití této procedury může znamenat konec cyklům dump a import jen proto, abych dostal data z non pg rdbms do PostgreSQL (nehledě na to, že dump ve vhodném formátu může být pro některé databáze určitý oříšek). Pro zajímavost DBI podporuje: ADO, CSV, DB2, Informix, Oracle, ODBC, Sybase, XBase (ADO pouze na win32), JDBC, LDAP, MySQL, MaxDB, SQLLite, Solid. Jasnou nevýhodou je rychlost, nehodí se pro velké výsledné množiny (roadmap DBI na třetím místě optimalizace fetchrow_hashref). Ještě kdyby někdo dodělal workflow.

CREATE OR REPLACE FUNCTION ext.rmysql(varchar, varchar, varchar,
  varchar) RETURNS SETOF RECORD AS $$
   use DBI;
   my $dbh = DBI->connect('dbi:mysql:'.$_[0],$_[1],$_[2],
      { RaiseError => 1, AutoCommit = > });
   $dbh->do("set character_set_results='latin2'");
   my $sth = $dbh->prepare($_[3]);
   $sth->execute(); my $myref;
   while ($dat = $sth->fetchrow_hashref) {push @$myref, $dat; }
   $sth->finish(); $dbh->disconnect();
   return $myref;
$$ LANGUAGE plperlu;

CREATE VIEW ext.jmena AS SELECT * FROM ext.rmysql('instalace:smudla','dbuser','****',
  'SELECT ixkontakt, jmeno, prijmeni, titul, email, tel1 FROM kontakt') AS
  (ixkontakt integer, jmeno varchar, prijmeni varchar,
   titul varchar, email varchar, tel1 varchar);

3. kategorie: dohledání údajů v externí bázi

Napsal jsem dvě služby. První dokáže z webu NIC získat údaje o doméně, druhá dohledává publikace Amazonu na základě klíčových slov. Výsledkem obou služeb je tabulka. Všimněte si složitosti první služby. Získat data z klasického html formuláře je podstatně komplikovanější než jedno SOAP volání (a to se pro zjednodušení zpracování formuláře používá knihovna WWW::Mechanize (primárně pro automatizaci vyplňování web formulářů – testování vlastních, automatizace ostatních) a knihovna HTML::TreeBuilder pro převedení html tabulky do 2D pole. SOAP je opticky delší, přenáší se více parametrů. Modul WWW::Mechanize bych použil hlavně pro zápis do externích bází (např. trigger, vyplnění registračního formuláře).

CREATE OR REPLACE FUNCTION ext.amazon_search(varchar) RETURNS SETOF ext.amazon_lst AS $$
   my $dev_token='insert developer token'; my $af_tag='insert associate tag';
   my $amazon_wdsl = "http://soap.amazon.com/schemas2/AmazonWebServices.wsdl";
   use strict; use SOAP::Lite; my @listbooks = ();

   my $amazon_search = SOAP::Lite->service("$amazon_wdsl");
   my $results = $amazon_search ->

      KeywordSearchRequest(SOAP::Data->name("KeywordSearchRequest")
         ->type("KeywordRequest")
            ->value(\SOAP::Data->value(
                SOAP::Data->name("keyword" => $_[0]), SOAP::Data->name("page" => "1"),
                SOAP::Data->name("mode" => "books"), SOAP::Data->name("tag" => $af_tag),
                SOAP::Data->name("type" => "lite"), SOAP::Data->name("devtag" => $dev_token),))
   );

   foreach my $result (@{$results->{Details}}){
      $result->{UsedPrice} =~ s/\$//g;
      push @listbooks, {
         productname => $result->{ProductName}|| "no title",
         authors => "{".join (', ', @{$result->{Authors}}) . "}",
         price => $result->{UsedPrice},asin => $result->{Asin}}}
   return \@listbooks;
$$ LANGUAGE plperlu;

SELECT * FROM ext.amazon_search('xml perl');

plperlu=# SELECT productname, authors FROM ext.amazon_search('postgresql');
                      productname                      |                    authors                    

-------------------------------------------------------+------------------------------------------------
 Beginning Databases with PostgreSQL                   | {"Richard Stones","Neil Matthew"}
 PostgreSQL                                            | {"Korry Douglas","Susan Douglas"}
 PHP and PostgreSQL Advanced Web Programming           | {"Ewald Geschwinde","Hans-Juergen Schoenig"}
 PostgreSQL Developer's Handbook (Developer's Library) | {"Ewald Geschwinde","Hans-JĂźergen SchĂśenig"}
 PostgreSQL Essential Reference                        | {"Barry  Stinson","Barry Stinson"}
 PostgreSQL: Introduction and Concepts                 | {"Bruce Momjian"}
 PostgreSQL                                            | {"Jeff Perkins"}
(7 rows)

plperlu=# SELECT * FROM ext.read_nic('jetspeed.cz');
-[ RECORD 1 ]+-----------------------------------------------------------------
domena       | jetspeed.cz
popis        |
stav         | Doména na zaplacena a je v zn CZ
glue         | {"A - ns.inway.cz","A - ns.inway.net"}
klic         |
registrovano | 2004-03-09
expirace     | 2005-03-09
registrator  | {{REG-GENERAL-REGISTRY,"GENERAL REGISTRY, s.r.o.",2004-11-13, },
             |  {REG-INWAY,"InWay, a.s.",2004-03-09,"2004-11-13 10:25"}}
drzitel      | {{IWS-PAVEL_STEHULE,"Ing. Pavel Stehule",2004-03-09, }}
techspr      | {{INWAY-TECH,"InWay, a. s.",2004-03-09, }}

4. univerzální notifikace o změně v datach emailem

Nevím, nakolik je tato služba smysluplná. Stará se o notifikaci změn emailem. Změněné položky (hodnoty) jsou ve formátu xml poslány v příloze notifikačního emailu. Berte ji jen jako příklad univerzálního triggeru v PL/Perlu, který dokáže pracovat s libovolnou tabulkou (není problém iterovat skrz hash tabulku),

if ($_TD->{event} eq 'INSERT') {
   foreach $key (keys %{$_TD->{new}}) {
      my $knode = $doc->createElement($key);
      $row->appendChild($knode);
      if (defined($_TD->{new}{$key})) {
        $knode->appendChild($doc->createTextNode($_TD->{new}{$key}));
      } else {
         $knode->setAttribute('null','true');
      }
   }
}

a příklad sestavení mailu a jeho odeslání:

$msg = MIME::Lite->new (From => $_TD->{args}[0], To => $_TD->{args}[1],
   Subject => $_TD->{args}[2], Type =>'multipart/mixed');
$msg->attach (Encoding => 'binary', Type => 'TEXT; charset=iso-8859-2',
   Data => "V databázi: $dbname došlo v $activated k změně údajů.\n" .
           "Detailní popis změn je v příloze dopisu ve formátu XML");
$msg->attach(Type => "text/xml; charset=$encoding", Data => $doc->toString,
   Disposition => 'attachment', Filename => 'change.xml');  

$msg->send_by_sendmail();

5. generování, zpracování XML

XML je jedním z řady formátů, který by měl být bezpochyby podporován každou databází. Specifikaci SQL/XML PostgreSQL nesplňuje (vím pouze o DB2, která ji plně podporuje) a pravděpodobně nějaký čas podporovat nebude (jistá podpora – zpřístupnění libXML2 – je v doplňcích (contrib)). Perl má podstatně větší možnosti a je celkém přirozené jej použít. V prvé řadě jde o generování XML. Pokud je databáze schopna univerzálně sestavit XML, může se zjednodušit aplikační vrstva. Tvorbu tabulek (a to je majoritní úkol db aplikací) se omezí na odeslání SQL příkazů a následnou transformaci XML do tabulky pomocí XSLT (jednodušší varianta na straně klienta, nebo složitější na straně serveru). Aktuální CPAN XML::generator::DBI nepodporuje styly, je nutné použít patch:

55a56,59
>     if (defined($proxy->{Stylesheet})) {
>        $proxy->SUPER::processing_instruction( {Target => 'xml-stylesheet',

>      Data => "type='text/xsl' href='$proxy->{Stylesheet}'"});
>     }

CREATE OR REPLACE FUNCTION ext.xml(varchar, varchar) RETURNS text AS $$
   use DBI;
   use XML::Generator::DBI;
   use XML::Handler::YAWriter;
   my $rv = spi_exec_query("SELECT current_setting('client_encoding'), " .
                           "current_database()", 1);
   my $encoding = lc($rv->{rows}[0]->{current_setting[5~});
   my $cdb = $rv->{rows}[0]->{current_database};
   my $dbh = DBI->connect ("DBI:Pg:dbname=$cdb", "", "", { RaiseError => 1, PrintError => 0});
   my $ya = XML::Handler::YAWriter->new (Encoding=>$encoding,
        Pretty => {PrettyWhiteNewline => 1, PrettyWhiteIndent => 1,});
   my $gen = XML::Generator::DBI->new (Handler => $ya, dbh => $dbh,
                                       ByColumnName => 1, ShowColumns => 1,
                                       Stylesheet => $_[1],);
   $gen->execute ($_[0]); $dbh->disconnect ();
   return join('',@{$ya->{Strings}});
$$ LANGUAGE plperlu;
SELECT ext.xml('SELECT * FROM ext.jmena LIMIT 10','x01.xsl');

Php skript:

require("common.inc");
header('Content-type: text/xml');
header('');
echo getSkalar("SELECT ext.xml('SELECT * FROM ext.cpu','x01.xsl')");
pg_close($cnh);

x01.xsl je univerzální XSL skript, tj. pro libovolný xml generovaný funkcí ext.xml. Výsledek transformace můžete posoudit na screenshotu.

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html>
<head>
<style type="text/css">
BODY      {font-family:Arial,sans-serif; font-size:8pt; font-weight:100; margin-top:0; margin-left:6; margin-right:0}
TD        {font-family:Arial,sans-serif; font-size:8pt; font-weight:100;}
TH        {font-family:Arial,sans-serif; font-size:12px; font-weight:bold; text-align:left}
.r1      {background-color:#E9F2F2; cursor:hand; xline-height:17px;}
.r0      {background-color:#C9E5E7; cursor:hand; xline-height:17px;}
.colhead {color:white; padding-right:0; cursor:hand;
          background-color:#003366; height:12;
          text-align:left; font-weight:bold; border-top:1px solid white; border-right:1px solid white;}

</style>
</head>
<body>
<table cellspacing="1" cellpadding="2">
  <tr class="colhead">
  <xsl:for-each select="database/select/columns/column/name">
    <th><xsl:value-of select="text()"/></th>

  </xsl:for-each>
  </tr>
  <xsl:for-each select="database/select/row">
    <tr class="r{position() mod 2}">
        <xsl:for-each select="*">

      <td><xsl:value-of select="text()"/></td>
        </xsl:for-each>
    </tr>

  </xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet></pre>

V opačném směru (parsování) můžeme XML dokument brát buďto jako jednu hodnotu (obsahem jsou strukturovaná data), např. evidence SOAP komunikace, nebo jako tabulku. V každém případě si můžeme hodně ulehčit práci. Validaci struktury a hodnot přehodíme na DTD (nebo XML schéma) a řešíme pouze vlastní načtení hodnot. Použitý XML Parser nepodporuje jiná kódování než iso-8859–2 nebo windows-1250, resp. neuznává synonyma názvů. Pokud můžeme získat dokument s jiným kódováním, je nutné vytvořit novou mapu – příkazy make_encmap a compile_encoding. Nebo použít jiný parser.

CREATE OR REPLACE FUNCTION ext.rxml(varchar) RETURNS SETOF RECORD AS $$
   use XML::Parser; use Encode;
   my %rec; my @tref = ();
   my $parser = new XML::Parser (
      Handlers => {
         Start => sub {
        my ($p, $tag) = @_;
            if ($tag eq "row" && $p->depth==2) { %rec = ();}},
         End   => sub {
            my ($p, $tag) = @_;
            if ($tag eq 'row') {push @tref, +{ %rec }; }},
         Char  => sub {
            my ($p, $data) = @_;
            my $tag = $p->current_element ();
            if ($p->depth == 4) {
               chomp $data; $data =~ s/\^s+|\s+$//g;
               $rec{$tag} .= encode("iso-8859-2", $data) if $data ne "";}},
      });
   $parser->parsefile ($_[0]);
   return \@tref;
$$ LANGUAGE plperlu;

CREATE VIEW ext.xmlj AS SELECT * FROM ext.rxml('/tmp/jmena.xml') AS (
  ixkontakt integer, jmeno varchar, prijmeni varchar,
  titul varchar, email varchar, tel1 varchar);