Username: (Neptun-kód)
Password: 123456
Host: eszakigrid106.inf.elte.hu
Port: 1521
Service name: orcl.inf.elte.hu

Feladatok:

1. Az SWP-ben a wine modellből SPARQL segítségével gyűjtsük ki a vörös, száraz borok nevét (wine) és ízét (flavor) és az eredményt mentsük le RDF/XML fájlba.

 (A wine ontológia: http://www.w3.org/TR/owl-guide/wine.rdf )

# Hosszú megoldás:
PREFIX wine: <http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
SELECT
?wine ?flavor
WHERE {
	?type rdfs:subClassOf ?c .
	?c owl:onProperty wine:hasColor .
	?c owl:hasValue wine:Red .
	?type rdfs:subClassOf ?f .
	?f owl:onProperty wine:hasFlavor .
	?f owl:hasValue ?flavor .
	?type rdfs:subClassOf ?d .
	?d owl:onProperty wine:hasSugar .
	?d owl:hasValue wine:Dry .
	?wine a ?type .
}
# Ez csak azokat találja meg, amelyeknek csak 1 ízük van.
# Nem lenne egyszerűbb az, hogy "?wine wine:hasColor wine:Red" ?
# OWLSIF -> lehet!

# Rövid, de semmiből ki nem található megoldás:
# Még jó, hogy volt egy minta lekérdezés erről!
PREFIX wine: <http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#>
SELECT
?wine ?flavor
WHERE {
	?wine wine:hasColor wine:Red .
	?wine wine:hasFlavor ?flavor .
	?wine wine:hasSugar wine:Dry .
}
# Default Rulebase: OWLSIF
# Execute Query (NEM a Without Inference!)


2. Oracle-ben hozzunk létre egy pelda(id,xm,xst,res) táblát, ahol id egész értékű, xm és xst XML típusú oszlop, a res pedig szöveget (HTML kódot) fog majd tárolni.

create table pelda (
	id integer,
	xm xmltype,
	xst xmltype,
	res varchar2(4000)
);


3. Az 1. feladat RDF/XML eredményét töltsük be az id=1 sorba, az xm oszlopba.

Az 1. feladat eredménye az alábbi:

<rdf:RDF
    xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#"
    xmlns:rs="http://www.w3.org/2001/sw/DataAccess/tests/result-set#" > 
  <rdf:Description rdf:nodeID="A0">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Moderate"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A1">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#KathrynKennedyLateral"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A2">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#SantaCruzMountainVineyardCabernetSauvignon"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A3">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#GaryFarrellMerlot"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A4">
    <rs:solution rdf:nodeID="A5"/>
    <rs:solution rdf:nodeID="A6"/>
    <rdf:type rdf:resource="http://www.w3.org/2001/sw/DataAccess/tests/result-set#ResultSet"/>
    <rs:solution rdf:nodeID="A7"/>
    <rs:resultVariable>wine</rs:resultVariable>
    <rs:solution rdf:nodeID="A8"/>
    <rs:solution rdf:nodeID="A9"/>
    <rs:solution rdf:nodeID="A10"/>
    <rs:solution rdf:nodeID="A11"/>
    <rs:solution rdf:nodeID="A12"/>
    <rs:solution rdf:nodeID="A13"/>
    <rs:solution rdf:nodeID="A14"/>
    <rs:solution rdf:nodeID="A15"/>
    <rs:solution rdf:nodeID="A16"/>
    <rs:solution rdf:nodeID="A17"/>
    <rs:solution rdf:nodeID="A18"/>
    <rs:resultVariable>flavor</rs:resultVariable>
    <rs:solution rdf:nodeID="A19"/>
    <rs:solution rdf:nodeID="A20"/>
    <rs:solution rdf:nodeID="A21"/>
    <rs:solution rdf:nodeID="A22"/>
    <rs:solution rdf:nodeID="A23"/>
    <rs:solution rdf:nodeID="A24"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A25">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#ChiantiClassico"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A26">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Delicate"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A17">
    <rs:binding rdf:nodeID="A27"/>
    <rs:binding rdf:nodeID="A28"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A29">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Moderate"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A10">
    <rs:binding rdf:nodeID="A30"/>
    <rs:binding rdf:nodeID="A31"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A12">
    <rs:binding rdf:nodeID="A32"/>
    <rs:binding rdf:nodeID="A33"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A34">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Moderate"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A35">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Moderate"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A36">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Delicate"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A14">
    <rs:binding rdf:nodeID="A37"/>
    <rs:binding rdf:nodeID="A38"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A33">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#FormanCabernetSauvignon"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A39">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Moderate"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A40">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Strong"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A19">
    <rs:binding rdf:nodeID="A41"/>
    <rs:binding rdf:nodeID="A1"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A24">
    <rs:binding rdf:nodeID="A40"/>
    <rs:binding rdf:nodeID="A2"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A42">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Moderate"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A8">
    <rs:binding rdf:nodeID="A43"/>
    <rs:binding rdf:nodeID="A44"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A20">
    <rs:binding rdf:nodeID="A45"/>
    <rs:binding rdf:nodeID="A3"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A31">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#SeanThackreySiriusPetiteSyrah"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A46">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#MariettaZinfandel"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A47">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Moderate"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A11">
    <rs:binding rdf:nodeID="A48"/>
    <rs:binding rdf:nodeID="A49"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A7">
    <rs:binding rdf:nodeID="A50"/>
    <rs:binding rdf:nodeID="A51"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A52">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#LaneTannerPinotNoir"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A38">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#MariettaCabernetSauvignon"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A32">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Strong"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A53">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#SaucelitoCanyonZinfandel1998"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A13">
    <rs:binding rdf:nodeID="A29"/>
    <rs:binding rdf:nodeID="A46"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A16">
    <rs:binding rdf:nodeID="A0"/>
    <rs:binding rdf:nodeID="A54"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A27">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Strong"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A55">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#LongridgeMerlot"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A30">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Strong"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A37">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Moderate"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A15">
    <rs:binding rdf:nodeID="A35"/>
    <rs:binding rdf:nodeID="A56"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A51">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#MountEdenVineyardEstatePinotNoir"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A9">
    <rs:binding rdf:nodeID="A39"/>
    <rs:binding rdf:nodeID="A53"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A57">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#ElyseZinfandel"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A58">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Moderate"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A5">
    <rs:binding rdf:nodeID="A36"/>
    <rs:binding rdf:nodeID="A59"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A48">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Moderate"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A43">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Moderate"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A28">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#CotturiZinfandel"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A54">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#PageMillWineryCabernetSauvignon"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A41">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Delicate"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A22">
    <rs:binding rdf:nodeID="A47"/>
    <rs:binding rdf:nodeID="A55"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A21">
    <rs:binding rdf:nodeID="A34"/>
    <rs:binding rdf:nodeID="A60"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A6">
    <rs:binding rdf:nodeID="A26"/>
    <rs:binding rdf:nodeID="A52"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A23">
    <rs:binding rdf:nodeID="A42"/>
    <rs:binding rdf:nodeID="A25"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A49">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#SaucelitoCanyonZinfandel"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A56">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#MountadamPinotNoir"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A45">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Moderate"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A60">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#MariettaPetiteSyrah"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A50">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#Strong"/>
    <rs:variable>flavor</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A18">
    <rs:binding rdf:nodeID="A58"/>
    <rs:binding rdf:nodeID="A57"/>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A59">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#ChateauMorgonBeaujolais"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
  <rdf:Description rdf:nodeID="A44">
    <rs:value rdf:resource="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#WhitehallLaneCabernetFranc"/>
    <rs:variable>wine</rs:variable>
  </rdf:Description>
</rdf:RDF>

Ezt töltöttem fel a /tmp/D2SOFR/ könyvtárba, red_dry_wine.rdf néven.

CREATE OR REPLACE DIRECTORY TMP AS '/tmp/D2SOFR/';
insert into pelda(id,xm) values (1,XMLType(bfilename('TMP','red_dry_wine.rdf'), nls_charset_id('EE8ISO8859P2')));


4. Oracle-ben kérdezzük le, hogy a ChateauMorgonBeaujolais bornak milyen az íze (flavor)?

SELECT p.xm.EXTRACT('/*[name()="rdf:RDF"]/*[name()="rdf:Description"][@*[name()="rdf:nodeID"]=(/*[name()="rdf:RDF"]/*[name()="rdf:Description"][*[name()="rs:binding"]/@*[name()="rdf:nodeID"]=(//*[name()="rdf:Description"][*[name()="rs:value"]/@*[name()="rdf:resource"]="http://www.w3.org/TR/2003/PR-owl-guide-20031209/wine#ChateauMorgonBeaujolais"]/@*[name()="rdf:nodeID"])]/*[name()="rs:binding"][1]/@*[name()="rdf:nodeID"])]/*[name()="rs:value"]/@*[name()="rdf:resource"] ') FROM pelda p WHERE ID=1;


5. Készítsünk egy XSLT-t, amely egy HTML lapot készít az 1. feladat eredményéből, amelyen az adatok wine,flavor fejlécű táblázatban szerepelnek, és mentsük el az XSLT-t.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns:rs="http://www.w3.org/2001/sw/DataAccess/tests/result-set#">

<xsl:template match="/">
<html>
<body>
<h1>Red, Dry Wine</h1>
<table border="1">
<tr><th>Wine</th><th>Flavor</th></tr>
<xsl:apply-templates select="rdf:RDF/rdf:Description/rs:solution" />
</table>
</body>
</html>
</xsl:template>

<xsl:template match="rs:solution">
<xsl:variable name="id" select="@rdf:nodeID" />
<tr>
<xsl:apply-templates select="//rdf:Description[@rdf:nodeID=$id]/rs:binding">
<xsl:with-param name="var" select="'wine'" />
</xsl:apply-templates>
<xsl:apply-templates select="//rdf:Description[@rdf:nodeID=$id]/rs:binding">
<xsl:with-param name="var" select="'flavor'" />
</xsl:apply-templates>
</tr>
</xsl:template>

<xsl:template match="rdf:Description/rs:binding">
<xsl:param name="var" />
<xsl:variable name="id" select="@rdf:nodeID" />
<xsl:apply-templates select="//rdf:Description[@rdf:nodeID=$id][rs:variable/text()=$var]" />
</xsl:template>

<xsl:template match="rdf:Description[rs:variable]">
<td>
<xsl:value-of select="rs:value/@rdf:resource" />
</td>
</xsl:template>

</xsl:stylesheet>


6. Töltsük be az XSLT-t, mint XML fájlt az id=1 sor, xst oszlopába.

update pelda set xst='<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns:rs="http://www.w3.org/2001/sw/DataAccess/tests/result-set#">

<xsl:template match="/">
<html>
<body>
<h1>Red, Dry Wine</h1>
<table border="1">
<tr><th>Wine</th><th>Flavor</th></tr>
<xsl:apply-templates select="rdf:RDF/rdf:Description/rs:solution" />
</table>
</body>
</html>
</xsl:template>

<xsl:template match="rs:solution">
<xsl:variable name="id" select="@rdf:nodeID" />
<tr>
<xsl:apply-templates select="//rdf:Description[@rdf:nodeID=$id]/rs:binding">
<xsl:with-param name="var" select="''wine''" />
</xsl:apply-templates>
<xsl:apply-templates select="//rdf:Description[@rdf:nodeID=$id]/rs:binding">
<xsl:with-param name="var" select="''flavor''" />
</xsl:apply-templates>
</tr>
</xsl:template>

<xsl:template match="rdf:Description/rs:binding">
<xsl:param name="var" />
<xsl:variable name="id" select="@rdf:nodeID" />
<xsl:apply-templates select="//rdf:Description[@rdf:nodeID=$id][rs:variable/text()=$var]" />
</xsl:template>

<xsl:template match="rdf:Description[rs:variable]">
<td>
<xsl:value-of select="rs:value/@rdf:resource" />
</td>
</xsl:template>

</xsl:stylesheet>' where id=1;


7. Írjunk egy Oracle eljárást, amely a pelda tábla minden sorára az xm oszlopában tárolt XML-re alkalmazza az xst oszlopában tárolt XSLT-t és az eredményt a res oszlopba teszi.

CREATE OR REPLACE PROCEDURE pelda_XSLT(id0 integer) is
BEGIN
UPDATE pelda SET res = XMLTRANSFORM(xm,xst) WHERE ID = id0;
end;
/


8. Teszteljük az eljárást az id=1 sorral.

CALL pelda_XSLT(1);
SELECT res FROM pelda;