--- a/src/test/scala/SparqlToSparqlToSqlTest.scala Wed Jun 16 12:08:16 2010 -0700
+++ b/src/test/scala/SparqlToSparqlToSqlTest.scala Wed Jun 16 12:10:41 2010 -0700
@@ -366,6 +366,7 @@
deliveryDays INT,
offerWebpage STRING,
publisher INT,
+ FOREIGN KEY (publisher) REFERENCES producer(nr),
publishDate DATE,
PRIMARY KEY (nr)
);
@@ -399,6 +400,7 @@
label STRING,
comment STRING,
producer INT,
+ FOREIGN KEY (producer) REFERENCES producer(nr),
propertyNum1 INT,
propertyNum2 INT,
propertyNum3 INT,
@@ -520,37 +522,74 @@
?product bsbm:productFeature ?pfp .
?product rdf:type ?ptp .
?product rdfs:label ?p_label .
+ ?product rdfs:comment ?p_comment .
?product bsbm:productPropertyNumeric1 ?lit_propertyNum1 .
+ ?product bsbm:productPropertyNumeric2 ?lit_propertyNum2 .
+ ?product bsbm:productPropertyNumeric3 ?lit_propertyNum3 .
+ ?product bsbm:productPropertyNumeric4 ?lit_propertyNum4 .
+ ?product bsbm:productPropertyNumeric5 ?lit_propertyNum5 .
+ ?product bsbm:productPropertyTextual1 ?ptex1 .
+ ?product bsbm:productPropertyTextual2 ?ptex2 .
+ ?product bsbm:productPropertyTextual3 ?ptex3 .
+ ?product bsbm:productPropertyTextual4 ?ptex4 .
+ ?product bsbm:productPropertyTextual5 ?ptex5 .
+ ?product bsbm:producer ?producer .
+ ?product dc:publisher ?publisher .
+ ?producer rdfs:label ?producer_label .
+ ?publisher rdfs:label ?publisher_label .
} WHERE {
?product product:label ?p_label .
+ ?product product:comment ?p_comment .
?product product:propertyNum1 ?lit_propertyNum1 .
+ ?product product:propertyNum2 ?lit_propertyNum2 .
+ ?product product:propertyNum3 ?lit_propertyNum3 .
+ ?product product:propertyNum4 ?lit_propertyNum4 .
+ ?product product:propertyNum5 ?lit_propertyNum5 .
+ ?product product:producer ?producer .
+ ?product product:propertyTex1 ?ptex1 .
+ ?product product:propertyTex2 ?ptex2 .
+ ?product product:propertyTex3 ?ptex3 .
+ ?product product:propertyTex4 ?ptex4 .
+ ?product product:propertyTex5 ?ptex5 .
?pfp productfeatureproduct:product ?product .
?ptp producttypeproduct:product ?product .
+ ?producer producer:label ?producer_label .
+ ?producer producer:comment ?producer_comment .
+ ?publisher producer:label ?publisher_label .
+ ?publisher producer:comment ?publisher_comment .
}""" //"
).get
val bsbmPatternMap = NodePatternMap(Map(
sparql.Var("pfp") ->
NodePattern("http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/ProductFeature([0-9]+)",
- "http://bsbm.example/db/productfeatureproduct/productFeature.$1#record"),
+ "http://bsbm.example/db/productfeatureproduct/productFeature.$1#record"),
sparql.Var("ptp") ->
NodePattern("http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/ProductType([0-9]+)",
- "http://bsbm.example/db/producttypeproduct/productType.$1#record")
+ "http://bsbm.example/db/producttypeproduct/productType.$1#record"),
+ sparql.Var("product") ->
+ NodePattern("http://dataFromProducer([0-9]+).example/Product([0-9]+)",
+ "http://bsbm.example/db/product/nr.$2#record"),
+ sparql.Var("producer") ->
+ NodePattern("http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/Producer([0-9]+)", // guess
+ "http://bsbm.example/db/producer/nr.$1#record")
+
))
- test("bsbm1") {
+ test("bsbm1-fixed") {
val sparqlParser = Sparql()
val queryStr = """
PREFIX bsbm-inst: <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/>
PREFIX bsbm: <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/vocabulary/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
+PREFIX product: <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/Product>
SELECT DISTINCT ?product ?label
WHERE {
?product rdfs:label ?label .
- ?product a <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/ProductType59> .
- ?product bsbm:productFeature <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/ProductFeature5> .
- ?product bsbm:productFeature <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/ProductFeature7> .
+ ?product a product:Type59 .
+ ?product bsbm:productFeature product:Feature5 .
+ ?product bsbm:productFeature product:Feature7 .
?product bsbm:productPropertyNumeric1 ?value1 .
FILTER (?value1 > 578)
}
@@ -604,4 +643,232 @@
"""
}
+
+ test("bsbm1-variable-feature") {
+ val sparqlParser = Sparql()
+ val queryStr = """
+PREFIX bsbm-inst: <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/>
+PREFIX bsbm: <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/vocabulary/>
+PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
+PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
+
+SELECT DISTINCT ?product ?label ?feature
+WHERE {
+ ?product rdfs:label ?label .
+ ?product a <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/ProductType59> .
+ ?product bsbm:productFeature ?feature .
+ ?product bsbm:productFeature <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/ProductFeature7> .
+?product bsbm:productPropertyNumeric1 ?value1 .
+ FILTER (?value1 > 578)
+ }
+ORDER BY ?label
+LIMIT 10
+"""
+
+ val bsbmQuery = sparqlParser.parseAll(sparqlParser.select, queryStr).get
+
+ val stemExpected = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX product: <http://bsbm.example/db/product#>
+PREFIX pfp: <http://bsbm.example/db/productfeatureproduct#>
+PREFIX ptp: <http://bsbm.example/db/producttypeproduct#>
+
+SELECT DISTINCT ?product ?label ?feature WHERE {
+ ?product product:label ?label .
+ ?product product:propertyNum1 ?value1 .
+ <http://bsbm.example/db/producttypeproduct/productType.59#record> ptp:product ?product .
+ ?feature pfp:product ?product .
+ <http://bsbm.example/db/productfeatureproduct/productFeature.7#record> pfp:product ?product .
+ FILTER (?value1 > 578)
}
+ORDER BY ?label
+LIMIT 10
+""").get
+
+ val sqlParser = Sql()
+ val sqlExpected = sqlParser.parseAll(sqlParser.select, """
+SELECT DISTINCT R_product.label AS label, R_product.nr AS product, R_feature.productFeature AS feature
+ FROM product AS R_product
+ INNER JOIN producttypeproduct AS R_productType59
+ ON R_productType59.product=R_product.nr
+ AND R_productType59.productType=59
+ INNER JOIN productfeatureproduct AS R_feature
+ ON R_feature.product=R_product.nr
+ AND R_feature.productFeature IS NOT NULL
+ INNER JOIN productfeatureproduct AS R_productFeature7
+ ON R_productFeature7.product=R_product.nr
+ AND R_productFeature7.productFeature=7
+ WHERE R_product.propertyNum1 > 578
+ AND R_product.label IS NOT NULL
+ORDER BY R_product.label
+LIMIT 10
+""").get
+
+ val asStem = SparqlToSparql(bsbmQuery, List(SparqlMap(db2bsbm, bsbmPatternMap)))
+ assert(stemExpected === asStem)
+ val (asSql, _) = SparqlToSql(bsbmDb, asStem, StemURI("http://bsbm.example/db/"), false, false)
+ assert(sqlExpected === asSql)
+ val output = """
+"""
+ }
+
+
+/*
+PREFIX dataFromProducer001411: <http://dataFromProducer001411.example/>
+PREFIX dataFromRatingSite0014: <http://dataFromRatingSite0014.example/>
+PREFIX dataFromVendor001400: <http://dataFromVendor001400.example>
+
+┌───────────────────┬───────────────────────────────────────────┬─────────────┐
+│ Parameter │ SPARQL │ SQL │
+├───────────────────┼───────────────────────────────────────────┼─────────────┤
+│ %ProductType% │ bsbm-inst:ProductType001342 │ 001342 │
+│ %ProductXYZ% │ dataFromProducer001411:Product00001435443 │ 00001435443 │
+│ %ProductFeature1% │ bsbm-inst:ProductFeature003432 │ 003432 │
+│ %ProductFeature2% │ bsbm-inst:ProductFeature003433 │ 003433 │
+│ %ProductFeature3% │ bsbm-inst:ProductFeature003434 │ 003434 │
+│ %ReviewXYZ% │ dataFromRatingSite0014:Review022343 │ 022343 │
+│ %OfferXYZ% │ dataFromVendor001400:Offer2413 │ 2413 │
+├───────────────────┼───────────────────────────────────────────┼─────────────┤
+│ %x% │ 44 │ 44 │
+│ %y% │ 77 │ 77 │
+│ %word1% │ camera │ camera │
+│ %currentDate% │ 2010-06-16 │ 2010-06-16 │
+└───────────────────┴───────────────────────────────────────────┴─────────────┘
+*/
+
+case class Subst(from:String, sparql:String, sql:String)
+
+val Subs =
+List(Subst("%ProductType%" ,"bsbm-inst:ProductType001342" ,"001342" ),
+ Subst("%ProductXYZ%" ,"dataFromProducer001411:Product00001435443","00001435443"),
+ Subst("%ProductFeature1%","bsbm-inst:ProductFeature003432" ,"003432" ),
+ Subst("%ProductFeature2%","bsbm-inst:ProductFeature003433" ,"003433" ),
+ Subst("%ProductFeature3%","bsbm-inst:ProductFeature003434" ,"003434" ),
+ Subst("%ReviewXYZ%" ,"dataFromRatingSite0014:Review022343" ,"022343" ),
+ Subst("%OfferXYZ%" ,"dataFromVendor001400:Offer2413" ,"2413" ),
+ Subst("%x%" ,"44" ,"44" ),
+ Subst("%y%" ,"77" ,"77" ),
+ Subst("%word1%" ,"camera" ,"camera" ),
+ Subst("%currentDate%" ,"2010-06-16" ,"2010-06-16" ))
+
+object SubstSet extends Enumeration {
+ type SubstSet = Value
+ val SPARQL, SQL = Value
+}
+import SubstSet._
+
+def subst (in:String, d:SubstSet) : String =
+ {
+ if (d == SPARQL) """PREFIX dataFromProducer001411: <http://dataFromProducer001411.example/>
+PREFIX dataFromRatingSite0014: <http://dataFromRatingSite0014.example/>
+PREFIX dataFromVendor001400: <http://dataFromVendor001400.example>
+""" else ""
+ } + Subs.foldLeft(in)((str, subst) =>
+ str.replaceAll(subst.from, {
+ if (d == SPARQL) subst.sparql else subst.sql
+ }))
+
+ test("bsbm1") {
+ val sparql = """
+PREFIX bsbm-inst: <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/>
+PREFIX bsbm: <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/vocabulary/>
+PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
+PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
+
+SELECT DISTINCT ?product ?label
+WHERE {
+ ?product rdfs:label ?label .
+ ?product a %ProductType% .
+ ?product bsbm:productFeature %ProductFeature1% .
+ ?product bsbm:productFeature %ProductFeature2% .
+?product bsbm:productPropertyNumeric1 ?value1 .
+ FILTER (?value1 > %x%)
+ }
+ORDER BY ?label
+LIMIT 10"""
+ val sql = """
+SELECT DISTINCT R_product.label AS label, R_product.nr AS product
+ FROM product AS R_product
+ INNER JOIN producttypeproduct AS R_productType001342
+ ON R_productType001342.product=R_product.nr
+ AND R_productType001342.productType=001342
+ INNER JOIN productfeatureproduct AS R_productFeature003432
+ ON R_productFeature003432.product=R_product.nr
+ AND R_productFeature003432.productFeature=003432
+ INNER JOIN productfeatureproduct AS R_productFeature003433
+ ON R_productFeature003433.product=R_product.nr
+ AND R_productFeature003433.productFeature=003433
+ WHERE R_product.propertyNum1 > 44
+ AND R_product.label IS NOT NULL
+ORDER BY R_product.label
+LIMIT 10
+"""
+
+ val sparqlParser = Sparql()
+ val bsbmQuery = sparqlParser.parseAll(sparqlParser.select, subst(sparql, SPARQL)).get
+ val sqlParser = Sql()
+ val sqlExpected = sqlParser.parseAll(sqlParser.select, subst(sql, SQL)).get
+
+ val asStem = SparqlToSparql(bsbmQuery, List(SparqlMap(db2bsbm, bsbmPatternMap)))
+ val (asSql, _) = SparqlToSql(bsbmDb, asStem, StemURI("http://bsbm.example/db/"), false, false)
+ assert(sqlExpected === asSql)
+ }
+
+
+ test("bsbm2") {
+ val sparql = """
+PREFIX bsbm-inst: <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/>
+PREFIX bsbm: <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/vocabulary/>
+PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
+PREFIX dc: <http://purl.org/dc/elements/1.1/>
+
+SELECT ?label ?comment ?producer ?productFeature ?propertyTextual1 ?propertyTextual2 ?propertyTextual3
+ ?propertyNumeric1 ?propertyNumeric2 ?propertyTextual4 ?propertyTextual5 ?propertyNumeric4
+WHERE {
+ %ProductXYZ% rdfs:label ?label .
+ %ProductXYZ% rdfs:comment ?comment .
+ %ProductXYZ% bsbm:producer ?p .
+ ?p rdfs:label ?producer .
+ %ProductXYZ% dc:publisher ?p .
+ %ProductXYZ% bsbm:productFeature ?f .
+ ?f rdfs:label ?productFeature .
+ %ProductXYZ% bsbm:productPropertyTextual1 ?propertyTextual1 .
+ %ProductXYZ% bsbm:productPropertyTextual2 ?propertyTextual2 .
+ %ProductXYZ% bsbm:productPropertyTextual3 ?propertyTextual3 .
+ %ProductXYZ% bsbm:productPropertyNumeric1 ?propertyNumeric1 .
+ %ProductXYZ% bsbm:productPropertyNumeric2 ?propertyNumeric2 .
+ OPTIONAL { %ProductXYZ% bsbm:productPropertyTextual4 ?propertyTextual4 }
+ OPTIONAL { %ProductXYZ% bsbm:productPropertyTextual5 ?propertyTextual5 }
+ OPTIONAL { %ProductXYZ% bsbm:productPropertyNumeric4 ?propertyNumeric4 }
+}
+"""
+ val sql = """
+SELECT DISTINCT R_product.label AS label, R_product.nr AS product
+ FROM product AS R_product
+ INNER JOIN producttypeproduct AS R_productType001342
+ ON R_productType001342.product=R_product.nr
+ AND R_productType001342.productType=001342
+ INNER JOIN productfeatureproduct AS R_productFeature003432
+ ON R_productFeature003432.product=R_product.nr
+ AND R_productFeature003432.productFeature=003432
+ INNER JOIN productfeatureproduct AS R_productFeature003433
+ ON R_productFeature003433.product=R_product.nr
+ AND R_productFeature003433.productFeature=003433
+ WHERE R_product.propertyNum1 > 44
+ AND R_product.label IS NOT NULL
+ORDER BY R_product.label
+LIMIT 10
+"""
+
+ val sparqlParser = Sparql()
+ val bsbmQuery = sparqlParser.parseAll(sparqlParser.select, subst(sparql, SPARQL)).get
+ val sqlParser = Sql()
+ val sqlExpected = sqlParser.parseAll(sqlParser.select, subst(sql, SQL)).get
+
+ val asDirect = SparqlToSparql(bsbmQuery, List(SparqlMap(db2bsbm, bsbmPatternMap)))
+ // println("asDirect: " + asDirect)
+ // val (asSql, _) = SparqlToSql(bsbmDb, asDirect, StemURI("http://bsbm.example/db/"), false, false)
+ // assert(sqlExpected === asSql)
+ }
+
+
+}