XML in relacijska baza
Nedavno smo govorili o strukturiranju podatkov XML, tokrat pa si bomo pogledali, kako je mogoče take podatke hraniti v relacijski bazi podatkov. Osredotočili se bomo predvsem na dvoje - kako napravimo dokument XML, ki vsebuje podatke iz relacijske baze, in na kakšne načine lahko podatke iz dokumentov XML shranimo v bazo.
Veliko razlogov je, zakaj želimo integrirati podatke XML z relacijsko bazo. Eden najpogostejših je, da želimo vsebino teh podatkov nekje objaviti (ponavadi prek spletne aplikacije). Recimo, da baza vsebuje podatke o trenutni vrednosti delnic na borzi. Želimo imeti spletno storitev (Web Service), ki je zadolžena za to, da vrača aktualne vrednosti odjemalcu v obliki dokumentov XML. Prednost tega pristopa je v tem, da spletna aplikacija dobi podatke v obliki XML in jih potem z ustreznimi transformacijami prilagodi želenemu formatu odjemalca, sama implementacija aplikacije pa ni odvisna od želenega formata. O takih transformacijah, ki jim pravimo XSLT - eXtensible Stylesheet Language Transformation, bomo govorili v eni od prihodnjih številk. Hkrati se doseže popolna ločitev predstavitve (HTML) in poslovne logike.
XML se torej v kombinaciji z bazo podatkov uporabi na dva načina: v smislu izmenjave podatkov med bazo in aplikacijo (ali med dvema bazama podatkov) ali pa v smislu hranjenja dokumentov XML v bazi.
Podatke XML lahko hranimo v bazi na dva načina. V prvem primeru so v bazi shranjeni goli podatki, iz njih pa se ustvari dokument XML (temu postopku pravimo kompozicija). Ta se pošlje aplikaciji ali drugi relacijski bazi (kjer se ta dokument spet oskubi - temu pravimo dekompozicija ali "schreding"). Zanimata nas torej kompozicija XML iz baze in dekompozicija v bazo. Zbirki golih podatkov, ki so shranjeni v bazi in so namenjeni za uporabo v dokumentih XML, pravimo zbirka XML oziroma "XML collection".
Drugi način je hranjenje celotnih dokumentov XML (torej takih, kakršni so, skupaj z oznakami). Za tako hranjenje podatkov mora imeti baza ustrezno podporo. Takemu hranjenju pravimo stolpec XML ali "XML Column" (ker je celoten dokument XML shranjen kot en velik podatek v običajnem stolpcu).
Vsi postopki, ki si jih bomo ogledali, so napisani za IBMovo relacijsko bazo DB2, a treba je poudariti, da so temeljni principi enaki, če le ima baza podporo XML (npr. Oracle, SQL Server ...).
Zbirka XML - kompozicija
Najprej si oglejmo, kako iz dokumenta XML izrežemo gole podatke in jih shranimo v bazi, ter obratno - kako iz teh podatkov nazaj zgradimo dokument XML. Še prej je dobro razmisliti, v kakšnih situacijah je taka tehnika priporočljiva. Zbirko XML naj bi uporabili, če:
Nadaljujmo z zgledom kompozicije (torej ustvarjanja dokumenta XML). Preden začnemo, je priporočljivo, da imamo definicijo DTD dokumenta XML, ki bo vseboval podatke, ki jih imamo v bazi. Recimo, da želimo zgraditi dokument XML, ki ima naslednjo definicijo:
<?xml version="1.0"?>
<!ELEMENT person (firstName, lastName, country, phone*)>
<!ATTLIST person id CDATA #REQUIRED>
<!ELEMENT firstName (#PCDATA)>
<!ELEMENT lastName (#PCDATA)>
<!ELEMENT country (#PCDATA)>
<!ELEMENT phone (type, number)>
<!ELEMENT type (#PCDATA)>
<!ELEMENT number (#PCDATA)>
Recimo, da tabeli s podatki že imamo, njuni definiciji pa sta taki:
CREATE TABLE CUSTOMER (
ID INTEGER NOT NULL PRIMARY KEY,
COUNTRY VARCHAR(30),
LNAME VARCHAR(30),
FNAME VARCHAR(30)
)
CREATE TABLE CUSTOMER_DETAILS (
ID INTEGER,
PHONE_NUMBER VARCHAR(20),
PHONE_TYPE VARCHAR(20),
CONSTRAINT CUSTOMER_DETAIL_FK
FOREIGN KEY (ID)
REFERENCES CUSTOMER (ID)
)
Imamo torej tabeli Customer in Customer_details, ki sta povezani prek primarnega ključa in tujega ključa ID. Zdaj je treba preslikati podatke iz stolpca v ustrezne elemente XML oziroma atribute. To počnemo s tako imenovano definicijo DAD (Document Access Definition), ki je sintaktično dokument XML. Pri pisanju dokumenta DAD imamo dve možnosti -SQL ali RDB_node (Relational DB node).
Preslikava DAD - opcija SQL
Pri metodi SQL se podatki, ki so v relacijski bazi in jih potrebujemo za vsebino dokumenta XML, dobijo iz le-te naenkrat s poizvedbo SQL, ki mora biti v definiciji DAD napisana znotraj elementa <SQL_stmt>. Oglejmo si začetek definicije DAD:
<?xml version="1.0"?>
<!DOCTYPE DAD SYSTEM "c:\dxx\dtd\dad.dtd">
<DAD>
<validation>NO</validation>
<Xcollection>
<SQL_stmt>
SELECT c.ID, COUNTRY, LNAME, FNAME, p.PHONE_ID,
PHONE_NUMBER, PHONE_TYPE
FROM CUSTOMER C,
TABLE (
SELECT substr(char(timestamp(generate_unique())),16)
AS PHONE_ID, ID, PHONE_NUMBER, PHONE_TYPE
FROM CUSTOMER_DETAILS) P
WHERE P.ID=C.ID
ORDER BY ID, PHONE_ID
</SQL_stmt>
Kot že rečeno, je definicija DAD dokument XML, zato ima lahko tudi svojo definicijo DTD (ali definicijo XML Schema). Nato obvezno sledi element <DAD>. Element <validation> označuje, ali naj se DAD preverja ali ne. Nato sledi bodisi <XCollection> bodisi <XColumn>. V našem primeru gre seveda za XML Collection. Celotna preslikava bo izdelana znotraj elementa <XCollection>, pri čemer je v primeru metode SQL prvi podelement obvezno <SQL_stmt>. Poglejmo nadaljevanje:
<prolog>?xml version="1.0"?</prolog>
<doctype>!DOCTYPE person
SYSTEM "f:\testxml\testxml.dtd"</doctype>
<root_node>
<element_node name="person">
<attribute_node name="id">
<column name="ID"/>
</attribute_node>
</element_node>
<element_node name="firstName">
<text_node>
<column name="FNAME"/>
</text_node>
</element_node>
<element_node name="lastName">
<text_node>
<column name="LNAME"/>
</text_node>
</element_node>
Element <prolog> napoveduje začetek dokumenta XML in njegova vsebina bo prepisana prav tja. <doctype> seveda označuje definicijo DTD, s katero lahko preverjamo dokument, ki se bo ustvaril. Sledi dejanska preslikava. Logika je zelo enostavna - sproti gradimo strukturo, pri čemer se znotraj elementov ali atributov sklicujemo na tisti stolpec, ki vsebuje podatke, s katerimi želimo napolniti tekoči element ali atribut. Tako bo na primer element <firstName> vseboval besedilo, ki je zapisano v stolpcu FNAME v tabeli Customer. Z atributom multi_occurence="Yes" označimo, naj se napravi toliko elementov <phone>, kolikor je različnih podatkov v tabeli. Človek ima pač lahko več telefonov.
Preslikava DAD - opcija RDB_node
Pri možnosti RDB_node je preslikava podobna (logika je seveda enaka), le da podatkov ne zberemo na začetku s poizvedbo SQL, temveč jih zahtevamo sproti z uporabo elementa <RDB_node>:
<?xml version="1.0"?>
<!DOCTYPE DAD SYSTEM "c:\apps\dxx\dtd\dad.dtd">
<DAD>
<validation>NO</validation>
<Xcollection>
<prolog>?xml version="1.0"?</prolog>
<doctype>!DOCTYPE person SYSTEM
"c:\testxml\testxml.dtd"</doctype>
<root_node>
<element_node name ="person">
<RDB_node>
<table name="customer" key="ID"/>
<table name="customer_details"
key="ID PHONE_NUMBER"/>
<condition>
customer.id=customer_details.id
</condition>
</RDB_node>
Ko prvič napišemo element <RDB_node>, je treba navesti tabele in ključe med njimi (ključe je sicer obvezno navesti le pri dekompoziciji). Zdaj lahko začnemo s pravo preslikavo:
<attribute_node name="id">
<RDB_node>
<table name="customer"/>
<column name="ID" type="integer/>
</RDB_node>
</attribute_node>
<element_node name="firstName">
<text_node>
<RDB_node>
<table name="customer"/>
<column name="FNAME"
type="varchar(30)"/>
</RDB_node>
</text_node>
</element_node>
Kot vidimo, se sproti sklicujemo na tabelo in stolpec, iz katerega želimo podatke. Atribut "type" pri kompoziciji ni obvezen. Nadaljevanje gre po enakem vzorcu:
<element_node name="lastName">
<text_node>
<RDB_node>
<table name="customer"/>
<column name="LNAME"
type="varchar(30)"/>
</RDB_node>
</text_node>
</element_node>
<element_node name="country">
<text_node>
<RDB_node>
<table name="customer"/>
<column name="COUNTRY"
type="varchar(30)"/>
</RDB_node>
</text_node>
</element_node>
<element_node name="phone"
multi_occurrence="YES">
<element_node name="type">
<text_node>
<RDB_node>
<table name="customer_details"/>
<column name="Phone_Type"
type="varchar(20)"/>
</RDB_node>
</text_node>
</element_node>
<element_node name="number">
<text_node>
<RDB_node>
<table name="customer_details"/>
<column name="Phone_Number"
type="varchar(20)"/>
</RDB_node>
</text_node>
</element_node><!-- number -->
</element_node> <!-- phone -->
</element_name> <!-- person -->
</root_node>
</Xcollection>
</DAD>
Ko je preslikava DAD narejena, je treba le še napraviti tabelo za izid, ki bo vsebovala dokument XML, in izvesti ustrezno proceduro v bazi, ki ta dokument dejansko ustvari. To je seveda popolnoma odvisno od proizvajalca. Pri bazi IBM DB2 pokličemo proceduro dxxGenXML(), še laže pa je izvesti javansko aplikacijo "Generate.java", ki jo je moč najti v paketu XML Extender znotraj baze DB2. Recimo, da je moja tabela za izid naslednja:
CREATE TABLE result_tab (
DOC VARCHAR(3000)
)
S priloženim programom v javi torej ustvarim dokument XML:
Java Generate imeBaze dadDokument result_tab
Zdaj je dokument XML v tabeli result_tab. Relacijska baza, ki podpira XML, ima lastne funkcije za izvoz takega dokumenta iz tabele. V našem primeru izvedemo naslednji stavek SQL, ki nam vrne celoten prvi element person:
SELECT db2xml.content (db2xml.XMLVarchar(doc), 'docDir\person1.xml' )
FROM result_tab
WHERE db2xml.extractVarchar(db2xml.XMLVarchar(doc), '/person/@id') = '1'
Uporabili smo uporabniške funkcije:
- content(), ki izvozi dokument (ali njegov del) iz tabele izida;
- xmlVarchar(), ki argument pretvori v tip XMLVarchar;
- extractVarchar(), ki izreže del tega dokumenta za primerjavo - glej stavek WHERE.
Zbirka XML - dekompozicija
Pri ustvarjanju dokumentov XML iz relacijske baze prav tako potrebujemo preslikavo DAD, le da imamo tu na izbiro le tehniko rdb_node. Tokrat so oznake za ključe in tipe podatkov obvezne. Poglejmo naš zgled (ne pozabimo, da je tokrat dokument XML že na voljo):
<?xml version="1.0"?>
...
<root_node>
<element_node name ="person">
<RDB_node>
<table name="customer" key="id"/>
<table name="customer_details"
key="id phone_number"/>
<condition>
customer.id=customer_details.id
</condition>
</RDB_node>
<attribute_node name="id">
<RDB_node>
<table name="customer"/>
<column name="id" type="integer"/>
<!-- type only required for decomp. -->
</RDB_node>
</attribute_node>
...
Začetni del je enak kot pri kompoziciji, zato je izpuščen. Spet je treba v prvem elementu <RDB_node> povezati obe tabeli prek ključev, nato pa začnemo sprehod po dokumentu XML. Vrednost atributa id elementa person naj se torej zapiše v tabelo Customer v stolpec id. Tip podatkov je integer (tip mora seveda ustrezati bazi).
Strukturo z več ponavljajočimi se elementi (npr. v našem primeru element <phone>) lahko rešimo na dva načina - s tako imenovanimi "atributskimi" namigi ali (kot že prej) z atributi multi_occurence (metoda wrapper-tags).
Pri prvi tehniki gre za to, da DB2, ko pride pri branju XML dokumenta do elementa z atributom, avtomatično generira novo stransko tabelo. Oglejmo si zgled dokumenta XML:
<person id="1234-12-123"> <!-- namig -->
<last_name>John</last_name>
<first_name>Doe</first_name>
<address zip="90210"> <!-- namig -->
<street_no>30</street_no>
</address>
...
</person>
Kakršenkoli atribut v dokumentu XML torej sproži ustvarjanje nove tabele. Poglejmo odgovarjajočo definicijo DAD:
<element_node name="person">
<attribute_node name="id"> <!-- Attribute hint -->
<RDB_node>
<table name="individuals"/>
<column name="SocSecNo" type="char(11)"/>
</RDB_node>
</attribute_node>
...
<element_node name="address">
<attribute_node name="zip"> <!-- Attribute hint -->
<RDB_node>
<table name="address"/>
<column name="ZIP" type="char(5)"/>
</RDB_node>
</attribute_node>
...
Tako se "id", "last_name" in "first_name" shranijo v tabelo "individuals".
Če dokument XML ne vsebuje atributov, lahko uporabimo metodo ovijalskih oznak (wrapper tags), s katero sami, ob pomoči atributa multi_occurence, v preslikavi DAD določimo, kdaj naj se napravi nova tabela. Poglejmo tak dokument XML:
<person>
<ssn>1234-12-123</ssn>
<last_name>Rabbit</last_name>
<first_name>Bunny</first_name>
<address>
<street_no>30</street_no>
<zip>90210</zip>
</address>
...
</person>
Ustrezna preslikava DAD:
<element_node name="person" multi_occurrence="YES"> <!-- Wrapper -->
<element_node name="last_name">
...
</element_node>
<element_node name="address" multi_occurrence="YES"> <!-- Wrapper -->
...
</element_node>
Če ima atribut multi_occurence vrednost NO (privzeta vrednost), to pomeni ponavljanje znotraj nadelementa. Vrednost YES pa pomeni ponavljanje skupaj s podelementi in torej ustvarjanje nove tabele.
Omeniti velja še, da je treba še posebej paziti, da se ujemata vsebina dokumenta XML in definicija DAD, sicer bo preslikava neuspešna. Ko je dokument DAD narejen, lahko znova uporabimo posebno funkcijo, ki je del podpore DB2 za XML. Uporabimo lahko proceduro dxxShredXML(), še bolje pa je, če uporabimo priloženi javanski program Shred.java:
java Shred imeBaze dadDokument xmlDokument
Stolpec XML
V nekaterih situacijah je primerneje shraniti celoten dokument XML (skupaj z vsemi oznakami) kot en sam objekt v bazi. Tej tehniki pravimo XML Column. Primerna je za naslednje primere:
Recimo, da želimo shraniti sledeči dokument XML:
<?xml version="1.0"?>
<!DOCTYPE person SYSTEM "c:\person.dtd">
<person id="1">
<firstName>Grega</firstName>
<lastName>Humar</lastName>
<country>SLO</country>
<phone>
<type>office</type>
<number>12345678</number>
</phone>
<phone>
<type>home</type>
<number>34567890</number>
</phone>
</person>
Najprej je treba bazo pripraviti za XML (odvisno od izdelovalca), ponavadi v ukazni vrstici. Za bazo DB2 je ukaz sledeč:
dxxadm enable_column imeBaze imeTabele imeStolpcaXml dadDatoteka -r id
-r id označuje, da želimo dodati primarni ključ id, ki bo omogočal povezave s stranskimi (side) tabelami (o tej malce pozneje).
Ko je baza pripravljena (enabled) za XML, to pomeni, da dobimo podporo za posebne tipe objektov (glej naprej) in uporabniške funkcije, prirejene za delo s predmeti XML. Treba je pripraviti tudi tabelo:
CREATE TABLE PERSON1 (
ID INTEGER NOT NULL PRIMARY KEY,
PERSON DB2XML.XMLCLOB NOT LOGGED
)
Prvi stolpec bo identitetni ključ (da lahko shranimo več dokumentov XML in jih iščemo). Drugi stolpec pa je tipa XMLCLOB - poseben tip objekta za velike (do 2 GB) predmete XML (izpeljanka običajnega CLOB tipa). Preostali možnosti sta še XMLVarchar (podatki do 3 KB) in XMLFile (sklici na mapo, do 512 bajtov).
Če želimo shraniti le dokument kot tak, sploh ne potrebujemo preslikave DAD. To potrebujemo le, če želimo napraviti še stranske (side) tabele, ki bodo nosile najpogosteje želene elemente oziroma atribute dokumenta XML. Te je seveda treba preslikati. Pri tem upoštevamo naslednji priporočili:
- elemente, ki se pojavljajo le enkrat in so na enaki ravni v XML strukturi, damo v skupno stransko tabelo;
- vsak ponavljajoči se element damo v svojo stransko tabelo.
Oglejmo si zgled preslikave DAD v stranske tabele:
<?xml version="1.0"?>
<!DOCTYPE DAD SYSTEM "c:\dxx\dtd\dad.dtd">
<DAD>
<dtdid>C:\person.dtd</dtdid>
<validation>YES</validation>
<Xcolumn>
<table name="person_names">
<column name="fname" type="varchar(50)"
path="/person/firstName"
multi_occurrence="NO"/>
<column name="lname" type="varchar(50)"
path="/person/lastName"
multi_occurrence="NO"/>
</table>
<table name="phone_number">
<column name="pnumber" type="varchar(20)"
path="/person/phone/number"
multi_occurrence="YES"/>
</table>
<table name="phone_type">
<column name="ptype" type="varchar(20)"
path="/person/phone/type"
multi_occurrence="YES"/>
</table>
</Xcolumn>
</DAD>
Zdaj imamo celoten dokument XML shranjen kot predmet XMLClob v tabeli Person1, hkrati pa imamo tri stranske tabele:
Kot vidimo, baza avtomatično doda identitetni stolpec (identity column), ki omogoča povezavo med stranskimi tabelami. Dxx_seqno je posledica zahteve multi_occurence="Yes", saj je v tabelah phone_type in phone_number več možnih vrstic (človek ima lahko pač več telefonskih številk - mobilna, stacionarna, domača, službena...).
Podatki so tako trajno shranjeni le v tabeli Person1, saj stranske tabele živijo le v času, ko ima baza omogočeno podporo XML. Po opravljenem delu (ali transakciji) se le-ta onemogoči, stranske tabele pa se uničijo. Ko bazo spet omogočimo, se seveda spet ustvarijo...
Kot že rečeno, je vse to potrebno le, če želite (zaradi hitrosti) imeti stranske tabele. V nasprotnem primeru vam baze sploh ni treba pripraviti za XML, temveč lahko podatke shranite kot predmete CLOB.
Zaenkrat smo le pripravili vse za vnos dokumenta XML, zdaj pa si oglejmo, kako lahko dokument dejansko vnesemo. Za ta namen uporabimo eno od sledečih funkcij:
Vnos izvedemo takole:
INSERT INTO person1 VALUES (1,db2xml.XmlClobFromFile ('c:\person.xml')
Kot prvo vrstico oziroma vnos smo torej vnesli dokument person.xml. Zdaj lahko izvajamo poizvedbe SQL, npr. z uporabo stranskih tabel:
SELECT pnumber
FROM person_names t1, person_phone_number t2, person_phone_type t3
WHERE t1.id = t3.id
and t2.dxx_seqno = t3.dxx_seqno
and t1.lname = 'Humar'
and t3.ptype = 'Office'
SELECT lname, fname
FROM person1 t1, person_names t2
WHERE t1.id = t2.id
and db2xml.extractVarchar ( person, '/person/country' ) = 'US'
SELECT db2xml.extractVarchar ( person, '/person/country' )
FROM person1 t1, person_names t2
WHERE t1.id = t2.id
and t2.name = 'Humar'
Seveda lahko izvajamo tudi posodobitve:
UPDATE person1 SET person = db2xml.XMLCLOBFromFile ( 'c:\person2.xml' )
S funkcijo db2xml.Update() pa lahko napravimo posodobitev samo določenega elementa ali atributa:
UPDATE person1
SET person = db2xml.Update ( person, '/person/country', 'D' )
Oglejmo si še, kako dokumente izvozimo iz baze. Uporabimo funkcijo db2xml.Content():
SELECT db2xml.Content ( person, 'c:\Exports\person.xml' )
FROM person1
WHERE id = 1
Na lokaciji c:\Exports tako dobimo dokument "person.xml". Ob koncu dela obvezno onemogočimo stolpec XML (XML Column), da se uničijo stranske tabele in tako ne zavzemajo sistemskih sredstev po nepotrebnem:
dxx disable_column imeBaze imeTabele.