PostgreSQL: XML co s ním 1. část

XML datové struktúry byly původně vyvinuty, jako výměnný formát dat mezi systémy. Taky jsou důležité jako datová uložiště pro aplikace, které mají data hodně strukturovaná. Bohužel někdy jsme nuceny i když neradi nacpat je do relačních databázích.

Jak na to v PostgreSQL ?

Zde pár věcí, které je nutné si všimnout při práci s XML daty v PostgreSQL.

Verifikace XML

Ačkoliv PostgreSQL umí pracovat s XML, vezte že je to kompositní datový typ a proto jak na sestavení, tak na interpretaci dat potřebuje funkce, které spomalují přístup k datům. Jde to dotáhnout brutálním nákupem hardwaru.

Obecně pro XML v PostreSQL se používá datový typ XML. Lze data ukládat i do datového typu TEXT, ale pak není jistota, jestli data jsou uložena v správném formátu XML. PostgreSQL vždy při každém ukládání nebo práci s daty je verifikuje, jestli jsou XML data ve správnem formátu. Asi nejednoduchším způsobem jak ověřit správnost XML dat je použít SQL příkaz, který se pokusí převést data v text na XML (<data>::XML) :

select
'<gc:data_basic xsi:schemalocation="http:// cesta .xsd" xmlns:gc="http://../xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <gc:data>158.00</gc:data>
    <gc:data1>5</gc:data1>

</gc:data_basic>'::xml 

V případě, že je vše OK, SQL vám vrátí :

     xml
------------------------- ...
 <gc:data_basic xsi:schemaLocation= ... 

V opačném případě Vám vrátí :

ŘÁDKA 2: '<gc:data_basic xsi:schemaLocation="http://...
         ^
DETAIL:  line 8: Opening and ending tag mismatch: data_basic line 1 and data_basic
</gc:data_basic>
                        ^
line 8: chunk is not well balanced
</gc:data_basic>
                        ^ 

Pak nezůstane nic jiného než ladit XML obsah. (Zde je zahajovací a koncový tag odlišný.)

Namísto <data>::XML lze použít i funkci cast( <data> as xml).

Způsob uložení dat XML v tabulkách

V tabulkách se XML uloží jako datový typ sloupce. Je důležité jaká data se pak do XML sloupce ukádají. Buď všechno jsou to XML stajného typu, nebo ne.

Nejlépe se to pozná jak je definovaný správný XSD (XML Schema definition) v záhlaví XML dokumentu. Ale mohou být i bez něj a pak je to sázka do loterie.

Když jsou stejné pro všechny řádky tabulky, tak se jedná o homogenní XML data a je možné přistupovat k datům tabulky jako k celku. Když ne jsou to nehomogénní XML data a pak musíte přistupovat ke každému řádku individuálně.

Jako příklad mohu uvést

1. Nehomogénní množina – Číselníky

Obvykle v praxi jsem se setkal s podobou, kdy se takto uložili číselníky, které se pak používali k verifikaci hodnot v externích XML souborech. Byla to dichtiotomie, protože stejné číselníky se museli použít i samotné db pro zabespečení referenční integrity i jako zdroj dat. Ale o tom jindy.

Takže v tabulce každý řádek v sloupci hodnota obsahuje jiná schémata dle kterých byl definovaný  typ dat.

SQL dotaz pro vytvoření tabulky a příklad dat:

create table jednotka
(id serial primary key,
 typ varchar(80) not null unique,
 hodnota xml not null);

-- Priklad dat
insert into jednotka
(typ,hodnota)
 values
 (
 'cas',
 '<cas>
   <jednotka>
    <nazev>sekunda</nazev><zkr>s</zkr><koeficient>1</koeficient>
   </jednotka>
   <jednotka>
    <nazev>minuta</nazev><zkr>min</zkr><koeficient>60</koeficient>
   </jednotka>
   <jednotka>
    <nazev>den (střední sluneční)</nazev><zkr>d</zkr><koeficient>86400</koeficient>
   </jednotka>
   <jednotka>
    <nazev>sol (střední Mars)</nazev><zkr>sol</zkr><koeficient>88775</koeficient>
   </jednotka>
  </cas>':: XML
  ); 

Po vložení dat je data potřeba i z SQL vytáhnout. Na to musíme udělat několik věcí :

1. Separovat řádky XML do samostatních řádků.
2. Separovat obsah tagů do sloupců a vrátit je v správnem datovém typu.

Jde to použitím funkcí :

• xpath(<cesta>,<hodnota>) – z XML dostane obsah učitého tagu v dané formě. Výsledek je uložený v poli hodnot.
• unnset(<pole>) – Rozloží pole hodnot na jednotlivé elementy do řádků
• array_to_string(<pole>) – Převede pole na textový řetězec

select
array_to_string(xpath('/jednotka/nazev/text()'::text, radek),',') as nazev,
array_to_string(xpath('/jednotka/zkr/text()'::text, radek),',') as zkr,
array_to_string(xpath('/jednotka/koeficient/text()'::text, radek),',')::numeric as koeficient
from
(select unnest(xpath('/cas/jednotka'::text,hodnota)) as radek
 from jednotka where typ = 'cas') a; 

Výsledek :

2. Homogenní množina

Zde je situace, že data chodí z externího zdroje v csv formátu, ale poslední sloupec je z důvodu struktury dat ve formátu xml. Otázka na místě by byla proč veškerá data nejsou ve formátu XML ? Zde je to pouze z důvodu demostrace technologie.

SQL příkaz a příklad vložení dat

-- Vytvoreni tabulky
create table weather_day
(id serial primary key,
 place varchar(80) not null,
 meteo_date date not null,
 meteo_vals xml not null,
 unique(place,meteo_date));

-- Vložení dat za Vevěří a Prahu 21.3.2016
insert into weather_day
(place,meteo_date,meteo_vals)
values
('Veveri',
to_date('21.3.2016','DD.MM.YYYY'),
'<meteo>
<hourly><time>9</time><temp>6</temp><feels>3</feels><humidity>61</humidity></hourly>
<hourly><time>10</time><temp>7</temp><feels>5</feels><humidity>55</humidity></hourly>
<hourly><time>11</time><temp>8</temp><feels>5</feels><humidity>52</humidity></hourly>
<hourly><time>12</time><temp>8</temp><feels>5</feels><humidity>53</humidity></hourly>
<hourly><time>13</time><temp>8</temp><feels>6</feels><humidity>52</humidity></hourly>
<hourly><time>14</time><temp>9</temp><feels>6</feels><humidity>52</humidity></hourly>
<hourly><time>15</time><temp>9</temp><feels>6</feels><humidity>52</humidity></hourly>
<hourly><time>16</time><temp>8</temp><feels>6</feels><humidity>55</humidity></hourly>
</meteo>'::XML),
('Prague',
to_date('21.3.2016','DD.MM.YYYY'),
'<meteo>
<hourly><time>9</time><temp>5</temp><feels>2</feels><humidity>73</humidity></hourly>
<hourly><time>10</time><temp>6</temp><feels>3</feels><humidity>69</humidity></hourly>
<hourly><time>11</time><temp>7</temp><feels>4</feels><humidity>65</humidity></hourly>
<hourly><time>12</time><temp>7</temp><feels>5</feels><humidity>61</humidity></hourly>
<hourly><time>13</time><temp>8</temp><feels>6</feels><humidity>59</humidity></hourly>
<hourly><time>14</time><temp>8</temp><feels>6</feels><humidity>59</humidity></hourly>
<hourly><time>15</time><temp>8</temp><feels>6</feels><humidity>58</humidity></hourly>
<hourly><time>16</time><temp>8</temp><feels>6</feels><humidity>59</humidity></hourly>
</meteo>'::XML); 

Pro výběr dat použijeme stejne SQL příkazy + navíc kombinaci s klasickými sloupci.

select
place,
meteo_date +(array_to_string(xpath('/hourly/time/text()'::text, radek),',')::text||' hours')::interval as hour,
array_to_string(xpath('/hourly/temp/text()'::text, radek),',')::integer as temp,
array_to_string(xpath('/hourly/feels/text()'::text, radek),',')::integer as feels,
array_to_string(xpath('/hourly/humidity/text()'::text, radek),',')::integer as humidity
from
(select unnest(xpath('/meteo/hourly'::text,meteo_vals)) as radek,meteo_date,place
 from weather_day ) a; 

Tak tolik v 1. části. Příště si řekneme jak xpath pro s definovaným XSD a jako zakomponovat tento typ dat do relačného světa.