--- a/project/build/RDB2RDF.scala Sun Oct 31 17:10:02 2010 -0400
+++ b/project/build/RDB2RDF.scala Sun Oct 31 17:22:21 2010 -0400
@@ -18,7 +18,7 @@
lazy val sparql2sparql = project("sparql2sparql", "sparql2sparql", new SPARQL2SPARQL(_), sparql)
lazy val sql2sql = project("sql2sql", "sql2sql", new SQL2SQL(_), sql)
lazy val sparql2sqlendpoint = project("sparql2sqlendpoint", "sparql2sqlendpoint", new SPARQL2SQLEndPoint(_), sparql2sql)
- lazy val sparql2sparql2sql = project("sparql2sparql2sql", "sparql2sparql2sql", new SPARQL2SPARQL2SQL(_), sparql2sql)
+ lazy val sparql2sparql2sql = project("sparql2sparql2sql", "sparql2sparql2sql", new SPARQL2SPARQL2SQL(_), sparql2sparql, sparql2sql)
class RDB(info: ProjectInfo) extends DefaultProject(info) with Common
class SQL(info: ProjectInfo) extends DefaultProject(info) with Common
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sparql2sparql2sql/src/main/scala/SparqlToSparqlToSql.scala Sun Oct 31 17:22:21 2010 -0400
@@ -0,0 +1,73 @@
+/* SparqlToSparqlToSql: convert SPARQL queries to sound SQL queries.
+ * $Id$
+ */
+
+package org.w3.sw.sparql2sparql2sql
+
+import org.w3.sw.rdb
+import org.w3.sw.sql
+import org.w3.sw.rdb.RDB.{RelName}
+import org.w3.sw.sparql
+import org.w3.sw.sparql2sparql
+import org.w3.sw.sparql2sql
+
+object SparqlToSparqlToSql {
+
+ def toView (rules:List[sparql.Construct], schema:rdb.RDB.Database, stemUri:sparql2sql.StemURI) = {
+
+ /* For each rule... */
+ val disjoints = rules.foldLeft(List[sql.Select]())((allgps, rule) => {
+
+ /* ... turn each triple pattern in the rule heads into a SQL pattern. */
+ rule.head.triplepatterns.foldLeft(allgps)((gps, triple) => {
+ def substituteVar (term:sparql.Term, label:String, triple:sparql.TriplePattern):(sparql.Term, List[sparql.Var], List[sql.ProjectAttribute]) = {
+ term match {
+ case sparql.TermVar(v) => (sparql.TermVar(sparql.Var(label)), List(sparql.Var(label)), List[sql.ProjectAttribute]())
+ case sparql.TermUri(u) => (term, List[sparql.Var](), List(
+ sql.ProjectAttribute(sql.PrimaryExpressionTyped(rdb.RDB.Datatype.STRING,sql.Name(u.toString)),
+ sql.AttrAlias(sql.Name(label)))))
+ case sparql.TermBNode(b) => (term, List[sparql.Var](), List(
+ sql.ProjectAttribute(sql.PrimaryExpressionTyped(rdb.RDB.Datatype.STRING,sql.Name(b.toString + "(" + triple + ")")),
+ sql.AttrAlias(sql.Name(label)))))
+ case sparql.TermLit(l) => (term, List[sparql.Var](), List(
+ sql.ProjectAttribute(sql.PrimaryExpressionTyped(rdb.RDB.Datatype.STRING,sql.Name(l.toString)),
+ sql.AttrAlias(sql.Name(label)))))
+ }
+ }
+
+ /* Add constant for predicate into selects. */
+
+ /* Substituted SPARQL ?S and ?O into the triple pattern. */
+ val (s, sselects, sconsts) = substituteVar(triple.s, "S", triple);
+ val (p, pselects, pconsts) = substituteVar(triple.p, "P", triple);
+ val (o, oselects, oconsts) = substituteVar(triple.o, "O", triple);
+ val select = sparql.Select(
+ false,
+ sparql.SparqlAttributeList(sselects ++ pselects ++ oselects),
+ sparql.TriplesBlock(List(sparql.TriplePattern(s, p, o))),
+ List[sparql.OrderElt](), None, None
+ )
+
+ /* Convert to equivalent SQL SELECT. */
+ val emptyPatternMap = sparql2sparql.NodePatternMap(Map[sparql.Var, sparql2sparql.NodePattern]())
+ val asStem = sparql2sparql.SparqlToSparql(select, List(sparql2sparql.SparqlMap(rule, emptyPatternMap)))
+ // println("triple: "+triple)
+ // println("asStem: "+asStem)
+ val sql.Select(distinct, sql.Projection(attributes), tablelist, expression, order, offset, limit) = sparql2sql.SparqlToSql(schema, asStem, stemUri, false, true)._1
+ // println("SQL: "+sql.Select(sql.Projection(attributes ++ sconsts ++ pconsts ++ oconsts), tablelist, expression))
+
+ /* Add resulting SELECT to list of graph patterns. */
+ gps :+ sql.Select(distinct, sql.Projection(attributes ++ sconsts ++ pconsts ++ oconsts), tablelist, expression, order, offset, limit)
+ })
+ })
+
+ /* Create a view from the disjoints.*/
+ val body:sql.SelectORUnion = disjoints.size match {
+ case 0 => error("not sure why you want a view with no rows...")
+ case 1 => disjoints.toList(0)
+ case _ => sql.Union(disjoints.toSet)
+ }
+ sql.View(rdb.RDB.RelName("triples"), body)
+ }
+}
+
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sparql2sparql2sql/src/test/scala/SparqlToSparqlToSqlTest.scala Sun Oct 31 17:22:21 2010 -0400
@@ -0,0 +1,875 @@
+/* SparqlToSparqlToSqlTest: transform SPARQL to Stem to SQL and compare against a reference query.
+ * $Id$
+ */
+
+package org.w3.sw.sparql2sparql2sql
+
+import org.scalatest.FunSuite
+import scala.util.matching.Regex
+import java.net.URI
+import org.w3.sw.sparql.{Sparql, Var}
+import org.w3.sw.sparql2sparql.{SparqlToSparql,NodePatternMap,NodePattern,SparqlMap}
+import org.w3.sw.rdb.RDB.{Database,Relation,RelName,AttrName}
+import org.w3.sw.sql.SqlParser
+import org.w3.sw.sparql2sql.{SparqlToSql,StemURI}
+import org.w3.sw.sparql2sparql2sql.SparqlToSparqlToSql.toView
+
+/* The SparqlToSparqlTest class transforms SPARQL queries to a relational data
+ * structure and compares them to a structure parsed from SQL.
+ */
+class SparqlToSparqlToSqlTest extends FunSuite {
+
+ val DDLParser = SqlParser()
+
+ val dbDdl = """
+CREATE TABLE Employee (empid INT, PRIMARY KEY (empid), firstName STRING, lastName STRING, birthday DATE, manager INT, FOREIGN KEY (manager) REFERENCES Employee(empid));
+CREATE TABLE Tasks (taskid INT, PRIMARY KEY (taskid), name STRING, lead INT, FOREIGN KEY (lead) REFERENCES Employee(empid));
+CREATE TABLE TaskAssignments (id INT PRIMARY KEY, PRIMARY KEY (id), task INT, FOREIGN KEY (task) REFERENCES Tasks(taskid), employee INT, FOREIGN KEY (employee) REFERENCES Employee(empid));
+"""
+ val HR:Database = DDLParser.parseAll(DDLParser.ddl, dbDdl).get
+
+ test("foaf:last_name FILTER") {
+ val sparqlParser = Sparql()
+ val foafQuery = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX foaf : <http://xmlns.com/foaf/0.1/>
+PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
+SELECT ?emp {
+?emp foaf:last_name ?name
+FILTER (?name = "Smith"^^xsd:string)
+}
+""").get
+ val hr2foaf = sparqlParser.parseAll(sparqlParser.construct, """
+PREFIX foaf : <http://xmlns.com/foaf/0.1/>
+PREFIX empP : <http://hr.example/DB/Employee#>
+CONSTRUCT { ?who foaf:first_name ?fname .
+ ?who foaf:last_name ?lname }
+ WHERE { ?who empP:firstName ?fname .
+ ?who empP:lastName ?lname }
+""").get
+ val hrPatternMap = NodePatternMap(Map[Var, NodePattern]())
+
+ val asStem = SparqlToSparql(foafQuery, List(SparqlMap(hr2foaf, hrPatternMap)))
+ val stemQuery = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX empP : <http://hr.example/DB/Employee#>
+PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
+SELECT ?emp {
+?emp empP:lastName ?name
+FILTER (?name = "Smith"^^xsd:string)
+}
+""").get
+ assert(stemQuery == asStem)
+ val sqlParser = SqlParser()
+ val sqlQuery = sqlParser.parseAll(sqlParser.select, """
+SELECT R_emp.empid AS emp
+ FROM Employee AS R_emp
+ WHERE R_emp.empid IS NOT NULL AND R_emp.lastName="Smith"
+""").get
+ val asSql = SparqlToSql(HR, asStem, StemURI("http://hr.example/DB/"), false, false)._1
+ assert(asSql === sqlQuery)
+ val output = """
+"""
+ }
+
+ test("foaf2HR as view") {
+ val sparqlParser = Sparql()
+ val hr2foaf = sparqlParser.parseAll(sparqlParser.construct, """
+PREFIX foaf : <http://xmlns.com/foaf/0.1/>
+PREFIX empP : <http://hr.example/DB/Employee#>
+CONSTRUCT { ?who a foaf:Person .
+ ?who foaf:first_name ?fname .
+ ?who foaf:last_name ?lname }
+ WHERE { ?who empP:firstName ?fname .
+ ?who empP:lastName ?lname }
+""").get
+ val view = toView(List(hr2foaf), HR, StemURI("http://hr.example/DB/"))
+ val sqlParser = SqlParser()
+ val expected = sqlParser.parseAll(sqlParser.createview, """
+CREATE VIEW triples AS
+ SELECT "<http://xmlns.com/foaf/0.1/Person>" AS O, "<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>" AS P, CONCAT("http://hr.example/DB/", "Employee", "/", "empid", ".", R_S.empid, "#record") AS S
+ FROM Employee AS R_S
+ WHERE (R_S.firstName IS NOT NULL)
+ AND (R_S.lastName IS NOT NULL)
+UNION
+ SELECT CONCAT("'", R_S.firstName, "'^^<http://www.w3.org/2001/XMLSchema#string>") AS O, "<http://xmlns.com/foaf/0.1/first_name>" AS P, CONCAT("http://hr.example/DB/", "Employee", "/", "empid", ".", R_S.empid, "#record") AS S
+ FROM Employee AS R_S
+ WHERE (R_S.empid IS NOT NULL)
+ AND (R_S.firstName IS NOT NULL)
+UNION
+ SELECT CONCAT("'", R_S.lastName, "'^^<http://www.w3.org/2001/XMLSchema#string>") AS O, "<http://xmlns.com/foaf/0.1/last_name>" AS P, CONCAT("http://hr.example/DB/", "Employee", "/", "empid", ".", R_S.empid, "#record") AS S
+ FROM Employee AS R_S
+ WHERE (R_S.empid IS NOT NULL)
+ AND (R_S.lastName IS NOT NULL)
+""" //"
+ ).get
+ assert(expected === view)
+ }
+
+ /* ========== DiabeticPatient database tests ==========
+ *
+ */
+ val hosp1Ddl = """
+CREATE TABLE Person (ID INT PRIMARY KEY, MiddleName STRING, DateOfBirth DATE, SexDE INT, PRIMARY KEY (ID), FOREIGN KEY (SexDE) REFERENCES Sex_DE(ID));
+CREATE TABLE Sex_DE (ID INT PRIMARY KEY, EntryName STRING);
+CREATE TABLE Item_Medication (ID INT PRIMARY KEY, PRIMARY KEY (ID), PatientID INT, FOREIGN KEY (PatientID) REFERENCES Person(ID), PerformedDTTM DATE, EntryName STRING);
+CREATE TABLE Medication (ID INT PRIMARY KEY, PRIMARY KEY (ID), ItemID INT, FOREIGN KEY (ItemID) REFERENCES Item_Medication(ID), MedDictDE INT, FOREIGN KEY (MedDictDE) REFERENCES Medication_DE(ID), DaysToTake INT);
+CREATE TABLE Medication_DE (ID INT PRIMARY KEY, NDC INT);
+CREATE TABLE NDCcodes (ID INT PRIMARY KEY, NDC INT, ingredient INT);
+"""
+ val hosp1:Database = DDLParser.parseAll(DDLParser.ddl, hosp1Ddl).get
+ val ConstructParser = Sparql()
+ val db2hl7 = ConstructParser.parseAll(ConstructParser.construct, """
+PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
+PREFIX Person: <http://hospital.example/DB/Person#>
+PREFIX Sex_DE: <http://hospital.example/DB/Sex_DE#>
+PREFIX Item_Medication: <http://hospital.example/DB/Item_Medication#>
+PREFIX Medication: <http://hospital.example/DB/Medication#>
+PREFIX Medication_DE: <http://hospital.example/DB/Medication_DE#>
+PREFIX NDCcodes: <http://hospital.example/DB/NDCcodes#>
+
+PREFIX hl7: <http://www.hl7.org/v3ballot/xml/infrastructure/vocabulary/vocabulary#>
+PREFIX spl: <http://www.hl7.org/v3ballot/xml/infrastructure/vocabulary/vocabulary#>
+
+CONSTRUCT {
+?person a hl7:Person .
+?person hl7:entityName ?middleName .
+?person hl7:livingSubjectBirthTime ?dob .
+?person hl7:administrativeGenderCodePrintName ?sex .
+?person hl7:substanceAdministration _:subst .
+_:subst a hl7:SubstanceAdministration .
+_:subst hl7:consumable _:cons .
+_:cons hl7:displayName ?takes .
+_:cons spl:activeIngredient ?ingred .
+?ingred spl:classCode ?ingredCode .
+_:subst hl7:effectiveTime _:interval .
+_:interval hl7:start ?indicDate
+} WHERE {
+ ?person Person:MiddleName ?middleName .
+ ?person Person:DateOfBirth ?dob .
+ ?person Person:SexDE ?sexEntry .
+
+ ?sexEntry Sex_DE:EntryName ?sex .
+
+ ?indicItem Item_Medication:PatientID ?person .
+ ?indicItem Item_Medication:PerformedDTTM ?indicDate .
+ ?indicItem Item_Medication:EntryName ?takes .
+ ?indicMed Medication:ItemID ?indicItem .
+ ?indicMed Medication:DaysToTake ?indicDuration .
+ ?indicMed Medication:MedDictDE ?indicDE .
+ ?indicDE Medication_DE:NDC ?indicNDC .
+ ?indicCode NDCcodes:NDC ?indicNDC .
+ ?indicCode NDCcodes:ingredient ?ingredCode }
+""").get
+
+ val hl7PatternMap = NodePatternMap(Map[Var, NodePattern]())
+
+ test("~/swobjects/tests/healthCare/lists-notBound/hl7.rq short") {
+ val sparqlParser = Sparql()
+ val hl7Query = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX hl7: <http://www.hl7.org/v3ballot/xml/infrastructure/vocabulary/vocabulary#>
+SELECT ?patient ?dob ?sex
+WHERE
+{
+ ?patient a hl7:Person .
+ ?patient hl7:entityName ?middleName .
+ ?patient hl7:livingSubjectBirthTime ?dob .
+ ?patient hl7:administrativeGenderCodePrintName ?sex .
+ ?patient hl7:substanceAdministration ?subs_admin .
+ ?subs_admin a hl7:SubstanceAdministration .
+ ?subs_admin hl7:consumable ?consumable .
+ ?consumable hl7:displayName ?takes .
+ ?consumable hl7:activeIngredient ?ingredient .
+ ?ingredient hl7:classCode 6809 .
+ ?subs_admin hl7:effectiveTime ?indic_span .
+}""").get
+
+ val stemQuery = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX Person: <http://hospital.example/DB/Person#>
+PREFIX Sex_DE: <http://hospital.example/DB/Sex_DE#>
+PREFIX Item_Medication: <http://hospital.example/DB/Item_Medication#>
+PREFIX Medication: <http://hospital.example/DB/Medication#>
+PREFIX Medication_DE: <http://hospital.example/DB/Medication_DE#>
+PREFIX NDCcodes: <http://hospital.example/DB/NDCcodes#>
+PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
+
+SELECT ?patient ?dob ?sex
+ WHERE {
+ ?patient Person:MiddleName ?middleName .
+ ?patient Person:DateOfBirth ?dob .
+ ?patient Person:SexDE ?_0_sexEntry .
+ ?_0_sexEntry Sex_DE:EntryName ?sex .
+
+ ?_0_indicItem Item_Medication:PatientID ?patient .
+ ?_0_indicItem Item_Medication:EntryName ?takes .
+ ?_0_indicMed Medication:ItemID ?_0_indicItem .
+ ?_0_indicMed Medication:MedDictDE ?_0_indicDE .
+ ?_0_indicDE Medication_DE:NDC ?_0_indicNDC .
+ ?_0_indicCode NDCcodes:NDC ?_0_indicNDC .
+ ?_0_indicCode NDCcodes:ingredient 6809
+}
+""").get
+ val asStem = SparqlToSparql(hl7Query, List(SparqlMap(db2hl7, hl7PatternMap)))
+ assert(asStem === stemQuery)
+ val sqlParser = SqlParser()
+ val sqlQuery = sqlParser.parseAll(sqlParser.select, """
+SELECT R_patient.ID AS patient, R_patient.DateOfBirth AS dob, R__0_sexEntry.EntryName AS sex
+ FROM Person AS R_patient
+ INNER JOIN Sex_DE AS R__0_sexEntry ON R__0_sexEntry.ID=R_patient.SexDE
+ INNER JOIN Item_Medication AS R__0_indicItem ON R__0_indicItem.PatientID=R_patient.ID
+ INNER JOIN Medication AS R__0_indicMed ON R__0_indicMed.ItemID=R__0_indicItem.ID
+ INNER JOIN Medication_DE AS R__0_indicDE ON R__0_indicDE.ID=R__0_indicMed.MedDictDE
+ INNER JOIN NDCcodes AS R__0_indicCode ON R__0_indicCode.NDC=R__0_indicDE.NDC
+ WHERE R__0_indicCode.ingredient=6809
+ AND R__0_sexEntry.ID=R_patient.SexDE
+ AND R__0_indicItem.EntryName IS NOT NULL
+ AND R_patient.DateOfBirth IS NOT NULL
+ AND R_patient.MiddleName IS NOT NULL
+ AND R__0_sexEntry.EntryName IS NOT NULL
+""").get
+ val asSql = SparqlToSql(hosp1, stemQuery, StemURI("http://hospital.example/DB/"), false, false)._1
+ assert(asSql === sqlQuery)
+ val output = """
+"""
+ }
+
+ test("~/swobjects/tests/healthCare/lists-notBound/hl7.rq MINUS") {
+ val sparqlParser = Sparql()
+ val hl7Query = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX hl7: <http://www.hl7.org/v3ballot/xml/infrastructure/vocabulary/vocabulary#>
+SELECT ?patient ?dob ?sex ?inclu_name
+WHERE
+{
+ ?patient a hl7:Person .
+ ?patient hl7:entityName ?middleName .
+ ?patient hl7:livingSubjectBirthTime ?dob .
+ ?patient hl7:administrativeGenderCodePrintName ?sex .
+ ?patient hl7:substanceAdministration ?inclu_subs .
+ ?inclu_subs a hl7:SubstanceAdministration .
+ ?inclu_subs hl7:consumable ?inclu_consu .
+ ?inclu_consu hl7:displayName ?inclu_name .
+ ?inclu_consu hl7:activeIngredient ?inclu_intred .
+ ?inclu_intred hl7:classCode ?incluCode .
+ ?inclu_subs hl7:effectiveTime ?inclu_span .
+ FILTER (?incluCode = 6809)
+ MINUS {
+ ?patient hl7:substanceAdministration ?exclu_subs .
+ ?exclu_subs a hl7:SubstanceAdministration .
+ ?exclu_subs hl7:consumable ?exclu_consu .
+ ?exclu_consu hl7:displayName ?exclu_name .
+ ?exclu_consu hl7:activeIngredient ?exclu_intred .
+ ?exclu_intred hl7:classCode 11209 .
+ ?exclu_subs hl7:effectiveTime ?exclu_span .
+ }
+}""").get
+
+ val stemQuery = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX Person: <http://hospital.example/DB/Person#>
+PREFIX Sex_DE: <http://hospital.example/DB/Sex_DE#>
+PREFIX Item_Medication: <http://hospital.example/DB/Item_Medication#>
+PREFIX Medication: <http://hospital.example/DB/Medication#>
+PREFIX Medication_DE: <http://hospital.example/DB/Medication_DE#>
+PREFIX NDCcodes: <http://hospital.example/DB/NDCcodes#>
+PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
+
+SELECT ?patient ?dob ?sex ?inclu_name
+ WHERE {
+ ?patient Person:MiddleName ?middleName .
+ ?patient Person:DateOfBirth ?dob .
+ ?patient Person:SexDE ?_0_0_sexEntry .
+ ?_0_0_sexEntry Sex_DE:EntryName ?sex .
+
+ ?_0_0_indicItem Item_Medication:PatientID ?patient .
+ ?_0_0_indicItem Item_Medication:EntryName ?inclu_name .
+ ?_0_0_indicMed Medication:ItemID ?_0_0_indicItem .
+ ?_0_0_indicMed Medication:MedDictDE ?_0_0_indicDE .
+ ?_0_0_indicDE Medication_DE:NDC ?_0_0_indicNDC .
+ ?_0_0_indicCode NDCcodes:NDC ?_0_0_indicNDC .
+ ?_0_0_indicCode NDCcodes:ingredient ?incluCode
+ FILTER (?incluCode = 6809)
+
+ MINUS {
+ ?_1_0_indicItem Item_Medication:PatientID ?patient .
+ ?_1_0_indicItem Item_Medication:EntryName ?exclu_name .
+ ?_1_0_indicMed Medication:ItemID ?_1_0_indicItem .
+ ?_1_0_indicMed Medication:MedDictDE ?_1_0_indicDE .
+ ?_1_0_indicDE Medication_DE:NDC ?_1_0_indicNDC .
+ ?_1_0_indicCode NDCcodes:NDC ?_1_0_indicNDC .
+ ?_1_0_indicCode NDCcodes:ingredient 11209
+ }
+ }
+""").get
+ val sqlParser = SqlParser()
+ val sqlQuery = sqlParser.parseAll(sqlParser.select, """
+SELECT R_patient.ID AS patient, R_patient.DateOfBirth AS dob, R__0_0_indicItem.EntryName AS inclu_name, R__0_0_sexEntry.EntryName AS sex
+ FROM Person AS R_patient
+ INNER JOIN Sex_DE AS R__0_0_sexEntry ON R__0_0_sexEntry.ID=R_patient.SexDE
+ INNER JOIN Item_Medication AS R__0_0_indicItem ON R__0_0_indicItem.PatientID=R_patient.ID
+ INNER JOIN Medication AS R__0_0_indicMed ON R__0_0_indicMed.ItemID=R__0_0_indicItem.ID
+ INNER JOIN Medication_DE AS R__0_0_indicDE ON R__0_0_indicMed.MedDictDE=R__0_0_indicDE.ID
+ INNER JOIN NDCcodes AS R__0_0_indicCode ON R__0_0_indicDE.NDC=R__0_0_indicCode.NDC
+ LEFT OUTER JOIN (
+ SELECT 6 AS _DISJOINT_, R__1_0_indicMed.ID AS _1_0_indicMed,
+ R__1_0_indicMed.MedDictDE AS _1_0_indicDE, R__1_0_indicItem.ID AS _1_0_indicItem,
+ R__1_0_indicItem.EntryName AS exclu_name, R__1_0_indicItem.PatientID AS patient,
+ R__1_0_indicCode.ID AS _1_0_indicCode, R__1_0_indicCode.NDC AS _1_0_indicNDC
+ FROM Medication AS R__1_0_indicMed
+ INNER JOIN Item_Medication AS R__1_0_indicItem ON R__1_0_indicMed.ItemID=R__1_0_indicItem.ID
+ INNER JOIN Medication_DE AS R__1_0_indicDE ON R__1_0_indicDE.ID=R__1_0_indicMed.MedDictDE
+ INNER JOIN NDCcodes AS R__1_0_indicCode ON R__1_0_indicDE.NDC=R__1_0_indicCode.NDC
+ WHERE R__1_0_indicCode.ingredient=11209
+ AND R__1_0_indicItem.EntryName IS NOT NULL
+ AND R__1_0_indicItem.PatientID IS NOT NULL
+ ) AS G_opt6 ON G_opt6.patient=R_patient.ID
+ WHERE G_opt6._DISJOINT_ IS NULL
+ AND R__0_0_indicCode.ingredient=6809
+ AND R__0_0_indicItem.EntryName IS NOT NULL
+ AND R_patient.DateOfBirth IS NOT NULL
+ AND R_patient.MiddleName IS NOT NULL
+ AND R__0_0_sexEntry.EntryName IS NOT NULL
+""").get
+ val asStem = SparqlToSparql(hl7Query, List(SparqlMap(db2hl7, hl7PatternMap)))
+ if (!(asStem == stemQuery)) {
+ println(asStem.toString())
+ println("---")
+ println(stemQuery.toString())
+ }
+ assert(asStem == stemQuery)
+ val asSql = SparqlToSql(hosp1, asStem, StemURI("http://hospital.example/DB/"), false, false)._1
+ assert(asSql === sqlQuery)
+ val output = """
+"""
+ }
+
+/*
+ test("hospital as view") {
+ val sparqlParser = Sparql()
+ println("hospital view...")
+ val view = toView(List(db2hl7), hosp1, StemURI("http://hospital.example/DB/"))
+ println("hospital view: " + view)
+ val sqlParser = SqlParser()
+ val expected = sqlParser.parseAll(sqlParser.createview, """
+""").get
+ assert(expected === view)
+ }
+ */
+
+ /* ========== BSBM database tests ==========
+ *
+ */
+ val bsbmDdl = """
+CREATE TABLE offer (
+ nr INT,
+ product INT,
+ producer INT,
+ vendor INT,
+ price DOUBLE,
+ validFrom DATETIME,
+ validTo DATETIME,
+ deliveryDays INT,
+ offerWebpage STRING,
+ publisher INT,
+ FOREIGN KEY (publisher) REFERENCES producer(nr),
+ publishDate DATE,
+ PRIMARY KEY (nr)
+);
+
+
+CREATE TABLE person (
+ nr INT,
+ name STRING,
+ mbox_sha1sum STRING,
+ country STRING,
+ publisher INT,
+ publishDate DATE,
+ PRIMARY KEY (nr)
+);
+
+
+CREATE TABLE producer (
+ nr INT,
+ label STRING,
+ comment STRING,
+ homepage STRING,
+ country STRING,
+ publisher INT,
+ publishDate DATE,
+ PRIMARY KEY (nr)
+);
+
+
+CREATE TABLE product (
+ nr INT,
+ label STRING,
+ comment STRING,
+ producer INT,
+ FOREIGN KEY (producer) REFERENCES producer(nr),
+ propertyNum1 INT,
+ propertyNum2 INT,
+ propertyNum3 INT,
+ propertyNum4 INT,
+ propertyNum5 INT,
+ propertyNum6 INT,
+ propertyTex1 STRING,
+ propertyTex2 STRING,
+ propertyTex3 STRING,
+ propertyTex4 STRING,
+ propertyTex5 STRING,
+ propertyTex6 STRING,
+ publisher INT,
+ publishDate DATE,
+ PRIMARY KEY (nr)
+);
+
+
+CREATE TABLE productfeature (
+ nr INT,
+ label STRING,
+ comment STRING,
+ publisher INT,
+ publishDate DATE,
+ PRIMARY KEY (nr)
+);
+
+
+CREATE TABLE productfeatureproduct (
+ product INT,
+ productFeature INT,
+ PRIMARY KEY (productFeature)
+);
+
+
+CREATE TABLE producttype (
+ nr INT,
+ label STRING,
+ comment STRING,
+ parent INT,
+ publisher INT,
+ publishDate DATE,
+ PRIMARY KEY (nr)
+);
+
+
+CREATE TABLE producttypeproduct (
+ product INT,
+ productType INT,
+ PRIMARY KEY (productType)
+);
+
+
+CREATE TABLE review (
+ nr INT,
+ product INT,
+ producer INT,
+ person INT,
+ reviewDate DATETIME,
+ title STRING,
+ text STRING,
+ language STRING,
+ rating1 INT,
+ rating2 INT,
+ rating3 INT,
+ rating4 INT,
+ publisher INT,
+ publishDate DATE,
+ PRIMARY KEY (nr)
+);
+
+
+CREATE TABLE vendor (
+ nr INT,
+ label STRING,
+ comment STRING,
+ homepage STRING,
+ country STRING,
+ publisher INT,
+ publishDate DATE,
+ PRIMARY KEY (nr)
+);
+"""
+ /*
+ productfeatureproduct ( PRIMARY KEY (product,productFeature) );
+ producttypeproduct ( PRIMARY KEY (product,productType) );
+ */
+ val bsbmDb:Database = DDLParser.parseAll(DDLParser.ddl, bsbmDdl).get
+
+ val db2bsbmP = Sparql() // re-use ConstructParser ?
+ val db2bsbm = db2bsbmP.parseAll(db2bsbmP.construct, """
+PREFIX map: <file:/E:/code/d2r-server-0.4/d2r-mapping.n3#>
+PREFIX vocab: <vocab/>
+PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
+PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
+PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
+PREFIX d2rq: <http://www.wiwiss.fu-berlin.de/suhl/bizer/D2RQ/0.1#>
+PREFIX dc: <http://purl.org/dc/elements/1.1/>
+PREFIX bsbm: <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/vocabulary/>
+PREFIX bsbm-inst: <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/>
+PREFIX foaf: <http://xmlns.com/foaf/0.1/>
+PREFIX rev: <http://purl.org/stuff/rev#>
+
+PREFIX offer: <http://bsbm.example/db/offer#>
+PREFIX person: <http://bsbm.example/db/person#>
+PREFIX producer: <http://bsbm.example/db/producer#>
+PREFIX product: <http://bsbm.example/db/product#>
+PREFIX productfeatureproduct: <http://bsbm.example/db/productfeatureproduct#>
+PREFIX producttypeproduct: <http://bsbm.example/db/producttypeproduct#>
+PREFIX productfeature: <http://bsbm.example/db/productfeature#>
+PREFIX producttype: <http://bsbm.example/db/producttype#>
+PREFIX review: <http://bsbm.example/db/review#>
+PREFIX vendor: <http://bsbm.example/db/vendor#>
+
+PREFIX tr: <http://www.w3.org/2008/04/SPARQLfed/#>
+
+CONSTRUCT {
+ ?product a bsbm:Product .
+ ?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(
+ Var("pfp") ->
+ NodePattern("http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/ProductFeature([0-9]+)",
+ "http://bsbm.example/db/productfeatureproduct/productFeature.$1#record"),
+ Var("ptp") ->
+ NodePattern("http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/ProductType([0-9]+)",
+ "http://bsbm.example/db/producttypeproduct/productType.$1#record"),
+ Var("product") ->
+ NodePattern("http://dataFromProducer([0-9]+).example/Product([0-9]+)",
+ "http://bsbm.example/db/product/nr.$2#record"),
+ 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-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 product:Type59 .
+ ?product bsbm:productFeature product:Feature5 .
+ ?product bsbm:productFeature product:Feature7 .
+?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 WHERE {
+ ?product product:label ?label .
+ ?product product:propertyNum1 ?value1 .
+ <http://bsbm.example/db/producttypeproduct/productType.59#record> ptp:product ?product .
+ <http://bsbm.example/db/productfeatureproduct/productFeature.5#record> 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 = SqlParser()
+ val sqlExpected = sqlParser.parseAll(sqlParser.select, """
+SELECT DISTINCT R_product.label AS label, R_product.nr AS product
+ 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_productFeature5
+ ON R_productFeature5.product=R_product.nr
+ AND R_productFeature5.productFeature=5
+ 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 = """
+"""
+ }
+
+
+ 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 = SqlParser()
+ 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 = SqlParser()
+ 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 = SqlParser()
+ 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)
+ }
+
+
+}
--- a/src/main/scala/SparqlToSparqlToSql.scala Sun Oct 31 17:10:02 2010 -0400
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,72 +0,0 @@
-/* SparqlToSparqlToSql: convert SPARQL queries to sound SQL queries.
- * $Id$
- */
-
-package w3c.sw.sparql2sparql2sql
-
-import w3c.sw.sql
-import w3c.sw.sql.RDB.{RelName}
-import w3c.sw.sparql
-import w3c.sw.sparql2sparql
-import w3c.sw.sparql2sql
-
-object SparqlToSparqlToSql {
-
- def toView (rules:List[sparql.Construct], schema:sql.RDB.Database, stemUri:sparql2sql.StemURI) = {
-
- /* For each rule... */
- val disjoints = rules.foldLeft(List[sql.Select]())((allgps, rule) => {
-
- /* ... turn each triple pattern in the rule heads into a SQL pattern. */
- rule.head.triplepatterns.foldLeft(allgps)((gps, triple) => {
- def substituteVar (term:sparql.Term, label:String, triple:sparql.TriplePattern):(sparql.Term, List[sparql.Var], List[sql.ProjectAttribute]) = {
- term match {
- case sparql.TermVar(v) => (sparql.TermVar(sparql.Var(label)), List(sparql.Var(label)), List[sql.ProjectAttribute]())
- case sparql.TermUri(u) => (term, List[sparql.Var](), List(
- sql.ProjectAttribute(sql.PrimaryExpressionTyped(sql.RDB.Datatype.STRING,sql.Name(u.toString)),
- sql.AttrAlias(sql.Name(label)))))
- case sparql.TermBNode(b) => (term, List[sparql.Var](), List(
- sql.ProjectAttribute(sql.PrimaryExpressionTyped(sql.RDB.Datatype.STRING,sql.Name(b.toString + "(" + triple + ")")),
- sql.AttrAlias(sql.Name(label)))))
- case sparql.TermLit(l) => (term, List[sparql.Var](), List(
- sql.ProjectAttribute(sql.PrimaryExpressionTyped(sql.RDB.Datatype.STRING,sql.Name(l.toString)),
- sql.AttrAlias(sql.Name(label)))))
- }
- }
-
- /* Add constant for predicate into selects. */
-
- /* Substituted SPARQL ?S and ?O into the triple pattern. */
- val (s, sselects, sconsts) = substituteVar(triple.s, "S", triple);
- val (p, pselects, pconsts) = substituteVar(triple.p, "P", triple);
- val (o, oselects, oconsts) = substituteVar(triple.o, "O", triple);
- val select = sparql.Select(
- false,
- sparql.SparqlAttributeList(sselects ++ pselects ++ oselects),
- sparql.TriplesBlock(List(sparql.TriplePattern(s, p, o))),
- List[sparql.OrderElt](), None, None
- )
-
- /* Convert to equivalent SQL SELECT. */
- val emptyPatternMap = sparql2sparql.NodePatternMap(Map[sparql.Var, sparql2sparql.NodePattern]())
- val asStem = sparql2sparql.SparqlToSparql(select, List(sparql2sparql.SparqlMap(rule, emptyPatternMap)))
- // println("triple: "+triple)
- // println("asStem: "+asStem)
- val sql.Select(distinct, sql.Projection(attributes), tablelist, expression, order, offset, limit) = sparql2sql.SparqlToSql(schema, asStem, stemUri, false, true)._1
- // println("SQL: "+sql.Select(sql.Projection(attributes ++ sconsts ++ pconsts ++ oconsts), tablelist, expression))
-
- /* Add resulting SELECT to list of graph patterns. */
- gps :+ sql.Select(distinct, sql.Projection(attributes ++ sconsts ++ pconsts ++ oconsts), tablelist, expression, order, offset, limit)
- })
- })
-
- /* Create a view from the disjoints.*/
- val body:sql.SelectORUnion = disjoints.size match {
- case 0 => error("not sure why you want a view with no rows...")
- case 1 => disjoints.toList(0)
- case _ => sql.Union(disjoints.toSet)
- }
- sql.View(sql.RDB.RelName("triples"), body)
- }
-}
-
--- a/src/test/scala/SparqlToSparqlToSqlTest.scala Sun Oct 31 17:10:02 2010 -0400
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,875 +0,0 @@
-/* SparqlToSparqlToSqlTest: transform SPARQL to Stem to SQL and compare against a reference query.
- * $Id$
- */
-
-package w3c.sw
-
-import org.scalatest.FunSuite
-import scala.util.matching.Regex
-import java.net.URI
-import w3c.sw.sparql.Sparql
-import w3c.sw.sparql2sparql.{SparqlToSparql,NodePatternMap,NodePattern,SparqlMap}
-import w3c.sw.sql.RDB.{Database,Relation,RelName,AttrName}
-import w3c.sw.sql.SqlParser
-import w3c.sw.sparql2sql.{SparqlToSql,StemURI}
-import w3c.sw.sparql2sparql2sql.SparqlToSparqlToSql.toView
-
-/* The SparqlToSparqlTest class transforms SPARQL queries to a relational data
- * structure and compares them to a structure parsed from SQL.
- */
-class SparqlToSparqlToSqlTest extends FunSuite {
-
- val DDLParser = SqlParser()
-
- val dbDdl = """
-CREATE TABLE Employee (empid INT, PRIMARY KEY (empid), firstName STRING, lastName STRING, birthday DATE, manager INT, FOREIGN KEY (manager) REFERENCES Employee(empid));
-CREATE TABLE Tasks (taskid INT, PRIMARY KEY (taskid), name STRING, lead INT, FOREIGN KEY (lead) REFERENCES Employee(empid));
-CREATE TABLE TaskAssignments (id INT PRIMARY KEY, PRIMARY KEY (id), task INT, FOREIGN KEY (task) REFERENCES Tasks(taskid), employee INT, FOREIGN KEY (employee) REFERENCES Employee(empid));
-"""
- val HR:Database = DDLParser.parseAll(DDLParser.ddl, dbDdl).get
-
- test("foaf:last_name FILTER") {
- val sparqlParser = Sparql()
- val foafQuery = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX foaf : <http://xmlns.com/foaf/0.1/>
-PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
-SELECT ?emp {
-?emp foaf:last_name ?name
-FILTER (?name = "Smith"^^xsd:string)
-}
-""").get
- val hr2foaf = sparqlParser.parseAll(sparqlParser.construct, """
-PREFIX foaf : <http://xmlns.com/foaf/0.1/>
-PREFIX empP : <http://hr.example/DB/Employee#>
-CONSTRUCT { ?who foaf:first_name ?fname .
- ?who foaf:last_name ?lname }
- WHERE { ?who empP:firstName ?fname .
- ?who empP:lastName ?lname }
-""").get
- val hrPatternMap = NodePatternMap(Map[sparql.Var, NodePattern]())
-
- val asStem = SparqlToSparql(foafQuery, List(SparqlMap(hr2foaf, hrPatternMap)))
- val stemQuery = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX empP : <http://hr.example/DB/Employee#>
-PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
-SELECT ?emp {
-?emp empP:lastName ?name
-FILTER (?name = "Smith"^^xsd:string)
-}
-""").get
- assert(stemQuery == asStem)
- val sqlParser = SqlParser()
- val sqlQuery = sqlParser.parseAll(sqlParser.select, """
-SELECT R_emp.empid AS emp
- FROM Employee AS R_emp
- WHERE R_emp.empid IS NOT NULL AND R_emp.lastName="Smith"
-""").get
- val asSql = SparqlToSql(HR, asStem, StemURI("http://hr.example/DB/"), false, false)._1
- assert(asSql === sqlQuery)
- val output = """
-"""
- }
-
- test("foaf2HR as view") {
- val sparqlParser = Sparql()
- val hr2foaf = sparqlParser.parseAll(sparqlParser.construct, """
-PREFIX foaf : <http://xmlns.com/foaf/0.1/>
-PREFIX empP : <http://hr.example/DB/Employee#>
-CONSTRUCT { ?who a foaf:Person .
- ?who foaf:first_name ?fname .
- ?who foaf:last_name ?lname }
- WHERE { ?who empP:firstName ?fname .
- ?who empP:lastName ?lname }
-""").get
- val view = toView(List(hr2foaf), HR, StemURI("http://hr.example/DB/"))
- val sqlParser = SqlParser()
- val expected = sqlParser.parseAll(sqlParser.createview, """
-CREATE VIEW triples AS
- SELECT "<http://xmlns.com/foaf/0.1/Person>" AS O, "<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>" AS P, CONCAT("http://hr.example/DB/", "Employee", "/", "empid", ".", R_S.empid, "#record") AS S
- FROM Employee AS R_S
- WHERE (R_S.firstName IS NOT NULL)
- AND (R_S.lastName IS NOT NULL)
-UNION
- SELECT CONCAT("'", R_S.firstName, "'^^<http://www.w3.org/2001/XMLSchema#string>") AS O, "<http://xmlns.com/foaf/0.1/first_name>" AS P, CONCAT("http://hr.example/DB/", "Employee", "/", "empid", ".", R_S.empid, "#record") AS S
- FROM Employee AS R_S
- WHERE (R_S.empid IS NOT NULL)
- AND (R_S.firstName IS NOT NULL)
-UNION
- SELECT CONCAT("'", R_S.lastName, "'^^<http://www.w3.org/2001/XMLSchema#string>") AS O, "<http://xmlns.com/foaf/0.1/last_name>" AS P, CONCAT("http://hr.example/DB/", "Employee", "/", "empid", ".", R_S.empid, "#record") AS S
- FROM Employee AS R_S
- WHERE (R_S.empid IS NOT NULL)
- AND (R_S.lastName IS NOT NULL)
-""" //"
- ).get
- assert(expected === view)
- }
-
- /* ========== DiabeticPatient database tests ==========
- *
- */
- val hosp1Ddl = """
-CREATE TABLE Person (ID INT PRIMARY KEY, MiddleName STRING, DateOfBirth DATE, SexDE INT, PRIMARY KEY (ID), FOREIGN KEY (SexDE) REFERENCES Sex_DE(ID));
-CREATE TABLE Sex_DE (ID INT PRIMARY KEY, EntryName STRING);
-CREATE TABLE Item_Medication (ID INT PRIMARY KEY, PRIMARY KEY (ID), PatientID INT, FOREIGN KEY (PatientID) REFERENCES Person(ID), PerformedDTTM DATE, EntryName STRING);
-CREATE TABLE Medication (ID INT PRIMARY KEY, PRIMARY KEY (ID), ItemID INT, FOREIGN KEY (ItemID) REFERENCES Item_Medication(ID), MedDictDE INT, FOREIGN KEY (MedDictDE) REFERENCES Medication_DE(ID), DaysToTake INT);
-CREATE TABLE Medication_DE (ID INT PRIMARY KEY, NDC INT);
-CREATE TABLE NDCcodes (ID INT PRIMARY KEY, NDC INT, ingredient INT);
-"""
- val hosp1:Database = DDLParser.parseAll(DDLParser.ddl, hosp1Ddl).get
- val ConstructParser = Sparql()
- val db2hl7 = ConstructParser.parseAll(ConstructParser.construct, """
-PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
-PREFIX Person: <http://hospital.example/DB/Person#>
-PREFIX Sex_DE: <http://hospital.example/DB/Sex_DE#>
-PREFIX Item_Medication: <http://hospital.example/DB/Item_Medication#>
-PREFIX Medication: <http://hospital.example/DB/Medication#>
-PREFIX Medication_DE: <http://hospital.example/DB/Medication_DE#>
-PREFIX NDCcodes: <http://hospital.example/DB/NDCcodes#>
-
-PREFIX hl7: <http://www.hl7.org/v3ballot/xml/infrastructure/vocabulary/vocabulary#>
-PREFIX spl: <http://www.hl7.org/v3ballot/xml/infrastructure/vocabulary/vocabulary#>
-
-CONSTRUCT {
-?person a hl7:Person .
-?person hl7:entityName ?middleName .
-?person hl7:livingSubjectBirthTime ?dob .
-?person hl7:administrativeGenderCodePrintName ?sex .
-?person hl7:substanceAdministration _:subst .
-_:subst a hl7:SubstanceAdministration .
-_:subst hl7:consumable _:cons .
-_:cons hl7:displayName ?takes .
-_:cons spl:activeIngredient ?ingred .
-?ingred spl:classCode ?ingredCode .
-_:subst hl7:effectiveTime _:interval .
-_:interval hl7:start ?indicDate
-} WHERE {
- ?person Person:MiddleName ?middleName .
- ?person Person:DateOfBirth ?dob .
- ?person Person:SexDE ?sexEntry .
-
- ?sexEntry Sex_DE:EntryName ?sex .
-
- ?indicItem Item_Medication:PatientID ?person .
- ?indicItem Item_Medication:PerformedDTTM ?indicDate .
- ?indicItem Item_Medication:EntryName ?takes .
- ?indicMed Medication:ItemID ?indicItem .
- ?indicMed Medication:DaysToTake ?indicDuration .
- ?indicMed Medication:MedDictDE ?indicDE .
- ?indicDE Medication_DE:NDC ?indicNDC .
- ?indicCode NDCcodes:NDC ?indicNDC .
- ?indicCode NDCcodes:ingredient ?ingredCode }
-""").get
-
- val hl7PatternMap = NodePatternMap(Map[sparql.Var, NodePattern]())
-
- test("~/swobjects/tests/healthCare/lists-notBound/hl7.rq short") {
- val sparqlParser = Sparql()
- val hl7Query = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX hl7: <http://www.hl7.org/v3ballot/xml/infrastructure/vocabulary/vocabulary#>
-SELECT ?patient ?dob ?sex
-WHERE
-{
- ?patient a hl7:Person .
- ?patient hl7:entityName ?middleName .
- ?patient hl7:livingSubjectBirthTime ?dob .
- ?patient hl7:administrativeGenderCodePrintName ?sex .
- ?patient hl7:substanceAdministration ?subs_admin .
- ?subs_admin a hl7:SubstanceAdministration .
- ?subs_admin hl7:consumable ?consumable .
- ?consumable hl7:displayName ?takes .
- ?consumable hl7:activeIngredient ?ingredient .
- ?ingredient hl7:classCode 6809 .
- ?subs_admin hl7:effectiveTime ?indic_span .
-}""").get
-
- val stemQuery = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX Person: <http://hospital.example/DB/Person#>
-PREFIX Sex_DE: <http://hospital.example/DB/Sex_DE#>
-PREFIX Item_Medication: <http://hospital.example/DB/Item_Medication#>
-PREFIX Medication: <http://hospital.example/DB/Medication#>
-PREFIX Medication_DE: <http://hospital.example/DB/Medication_DE#>
-PREFIX NDCcodes: <http://hospital.example/DB/NDCcodes#>
-PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
-
-SELECT ?patient ?dob ?sex
- WHERE {
- ?patient Person:MiddleName ?middleName .
- ?patient Person:DateOfBirth ?dob .
- ?patient Person:SexDE ?_0_sexEntry .
- ?_0_sexEntry Sex_DE:EntryName ?sex .
-
- ?_0_indicItem Item_Medication:PatientID ?patient .
- ?_0_indicItem Item_Medication:EntryName ?takes .
- ?_0_indicMed Medication:ItemID ?_0_indicItem .
- ?_0_indicMed Medication:MedDictDE ?_0_indicDE .
- ?_0_indicDE Medication_DE:NDC ?_0_indicNDC .
- ?_0_indicCode NDCcodes:NDC ?_0_indicNDC .
- ?_0_indicCode NDCcodes:ingredient 6809
-}
-""").get
- val asStem = SparqlToSparql(hl7Query, List(SparqlMap(db2hl7, hl7PatternMap)))
- assert(asStem === stemQuery)
- val sqlParser = SqlParser()
- val sqlQuery = sqlParser.parseAll(sqlParser.select, """
-SELECT R_patient.ID AS patient, R_patient.DateOfBirth AS dob, R__0_sexEntry.EntryName AS sex
- FROM Person AS R_patient
- INNER JOIN Sex_DE AS R__0_sexEntry ON R__0_sexEntry.ID=R_patient.SexDE
- INNER JOIN Item_Medication AS R__0_indicItem ON R__0_indicItem.PatientID=R_patient.ID
- INNER JOIN Medication AS R__0_indicMed ON R__0_indicMed.ItemID=R__0_indicItem.ID
- INNER JOIN Medication_DE AS R__0_indicDE ON R__0_indicDE.ID=R__0_indicMed.MedDictDE
- INNER JOIN NDCcodes AS R__0_indicCode ON R__0_indicCode.NDC=R__0_indicDE.NDC
- WHERE R__0_indicCode.ingredient=6809
- AND R__0_sexEntry.ID=R_patient.SexDE
- AND R__0_indicItem.EntryName IS NOT NULL
- AND R_patient.DateOfBirth IS NOT NULL
- AND R_patient.MiddleName IS NOT NULL
- AND R__0_sexEntry.EntryName IS NOT NULL
-""").get
- val asSql = SparqlToSql(hosp1, stemQuery, StemURI("http://hospital.example/DB/"), false, false)._1
- assert(asSql === sqlQuery)
- val output = """
-"""
- }
-
- test("~/swobjects/tests/healthCare/lists-notBound/hl7.rq MINUS") {
- val sparqlParser = Sparql()
- val hl7Query = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX hl7: <http://www.hl7.org/v3ballot/xml/infrastructure/vocabulary/vocabulary#>
-SELECT ?patient ?dob ?sex ?inclu_name
-WHERE
-{
- ?patient a hl7:Person .
- ?patient hl7:entityName ?middleName .
- ?patient hl7:livingSubjectBirthTime ?dob .
- ?patient hl7:administrativeGenderCodePrintName ?sex .
- ?patient hl7:substanceAdministration ?inclu_subs .
- ?inclu_subs a hl7:SubstanceAdministration .
- ?inclu_subs hl7:consumable ?inclu_consu .
- ?inclu_consu hl7:displayName ?inclu_name .
- ?inclu_consu hl7:activeIngredient ?inclu_intred .
- ?inclu_intred hl7:classCode ?incluCode .
- ?inclu_subs hl7:effectiveTime ?inclu_span .
- FILTER (?incluCode = 6809)
- MINUS {
- ?patient hl7:substanceAdministration ?exclu_subs .
- ?exclu_subs a hl7:SubstanceAdministration .
- ?exclu_subs hl7:consumable ?exclu_consu .
- ?exclu_consu hl7:displayName ?exclu_name .
- ?exclu_consu hl7:activeIngredient ?exclu_intred .
- ?exclu_intred hl7:classCode 11209 .
- ?exclu_subs hl7:effectiveTime ?exclu_span .
- }
-}""").get
-
- val stemQuery = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX Person: <http://hospital.example/DB/Person#>
-PREFIX Sex_DE: <http://hospital.example/DB/Sex_DE#>
-PREFIX Item_Medication: <http://hospital.example/DB/Item_Medication#>
-PREFIX Medication: <http://hospital.example/DB/Medication#>
-PREFIX Medication_DE: <http://hospital.example/DB/Medication_DE#>
-PREFIX NDCcodes: <http://hospital.example/DB/NDCcodes#>
-PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
-
-SELECT ?patient ?dob ?sex ?inclu_name
- WHERE {
- ?patient Person:MiddleName ?middleName .
- ?patient Person:DateOfBirth ?dob .
- ?patient Person:SexDE ?_0_0_sexEntry .
- ?_0_0_sexEntry Sex_DE:EntryName ?sex .
-
- ?_0_0_indicItem Item_Medication:PatientID ?patient .
- ?_0_0_indicItem Item_Medication:EntryName ?inclu_name .
- ?_0_0_indicMed Medication:ItemID ?_0_0_indicItem .
- ?_0_0_indicMed Medication:MedDictDE ?_0_0_indicDE .
- ?_0_0_indicDE Medication_DE:NDC ?_0_0_indicNDC .
- ?_0_0_indicCode NDCcodes:NDC ?_0_0_indicNDC .
- ?_0_0_indicCode NDCcodes:ingredient ?incluCode
- FILTER (?incluCode = 6809)
-
- MINUS {
- ?_1_0_indicItem Item_Medication:PatientID ?patient .
- ?_1_0_indicItem Item_Medication:EntryName ?exclu_name .
- ?_1_0_indicMed Medication:ItemID ?_1_0_indicItem .
- ?_1_0_indicMed Medication:MedDictDE ?_1_0_indicDE .
- ?_1_0_indicDE Medication_DE:NDC ?_1_0_indicNDC .
- ?_1_0_indicCode NDCcodes:NDC ?_1_0_indicNDC .
- ?_1_0_indicCode NDCcodes:ingredient 11209
- }
- }
-""").get
- val sqlParser = SqlParser()
- val sqlQuery = sqlParser.parseAll(sqlParser.select, """
-SELECT R_patient.ID AS patient, R_patient.DateOfBirth AS dob, R__0_0_indicItem.EntryName AS inclu_name, R__0_0_sexEntry.EntryName AS sex
- FROM Person AS R_patient
- INNER JOIN Sex_DE AS R__0_0_sexEntry ON R__0_0_sexEntry.ID=R_patient.SexDE
- INNER JOIN Item_Medication AS R__0_0_indicItem ON R__0_0_indicItem.PatientID=R_patient.ID
- INNER JOIN Medication AS R__0_0_indicMed ON R__0_0_indicMed.ItemID=R__0_0_indicItem.ID
- INNER JOIN Medication_DE AS R__0_0_indicDE ON R__0_0_indicMed.MedDictDE=R__0_0_indicDE.ID
- INNER JOIN NDCcodes AS R__0_0_indicCode ON R__0_0_indicDE.NDC=R__0_0_indicCode.NDC
- LEFT OUTER JOIN (
- SELECT 6 AS _DISJOINT_, R__1_0_indicMed.ID AS _1_0_indicMed,
- R__1_0_indicMed.MedDictDE AS _1_0_indicDE, R__1_0_indicItem.ID AS _1_0_indicItem,
- R__1_0_indicItem.EntryName AS exclu_name, R__1_0_indicItem.PatientID AS patient,
- R__1_0_indicCode.ID AS _1_0_indicCode, R__1_0_indicCode.NDC AS _1_0_indicNDC
- FROM Medication AS R__1_0_indicMed
- INNER JOIN Item_Medication AS R__1_0_indicItem ON R__1_0_indicMed.ItemID=R__1_0_indicItem.ID
- INNER JOIN Medication_DE AS R__1_0_indicDE ON R__1_0_indicDE.ID=R__1_0_indicMed.MedDictDE
- INNER JOIN NDCcodes AS R__1_0_indicCode ON R__1_0_indicDE.NDC=R__1_0_indicCode.NDC
- WHERE R__1_0_indicCode.ingredient=11209
- AND R__1_0_indicItem.EntryName IS NOT NULL
- AND R__1_0_indicItem.PatientID IS NOT NULL
- ) AS G_opt6 ON G_opt6.patient=R_patient.ID
- WHERE G_opt6._DISJOINT_ IS NULL
- AND R__0_0_indicCode.ingredient=6809
- AND R__0_0_indicItem.EntryName IS NOT NULL
- AND R_patient.DateOfBirth IS NOT NULL
- AND R_patient.MiddleName IS NOT NULL
- AND R__0_0_sexEntry.EntryName IS NOT NULL
-""").get
- val asStem = SparqlToSparql(hl7Query, List(SparqlMap(db2hl7, hl7PatternMap)))
- if (!(asStem == stemQuery)) {
- println(asStem.toString())
- println("---")
- println(stemQuery.toString())
- }
- assert(asStem == stemQuery)
- val asSql = SparqlToSql(hosp1, asStem, StemURI("http://hospital.example/DB/"), false, false)._1
- assert(asSql === sqlQuery)
- val output = """
-"""
- }
-
-/*
- test("hospital as view") {
- val sparqlParser = Sparql()
- println("hospital view...")
- val view = toView(List(db2hl7), hosp1, StemURI("http://hospital.example/DB/"))
- println("hospital view: " + view)
- val sqlParser = SqlParser()
- val expected = sqlParser.parseAll(sqlParser.createview, """
-""").get
- assert(expected === view)
- }
- */
-
- /* ========== BSBM database tests ==========
- *
- */
- val bsbmDdl = """
-CREATE TABLE offer (
- nr INT,
- product INT,
- producer INT,
- vendor INT,
- price DOUBLE,
- validFrom DATETIME,
- validTo DATETIME,
- deliveryDays INT,
- offerWebpage STRING,
- publisher INT,
- FOREIGN KEY (publisher) REFERENCES producer(nr),
- publishDate DATE,
- PRIMARY KEY (nr)
-);
-
-
-CREATE TABLE person (
- nr INT,
- name STRING,
- mbox_sha1sum STRING,
- country STRING,
- publisher INT,
- publishDate DATE,
- PRIMARY KEY (nr)
-);
-
-
-CREATE TABLE producer (
- nr INT,
- label STRING,
- comment STRING,
- homepage STRING,
- country STRING,
- publisher INT,
- publishDate DATE,
- PRIMARY KEY (nr)
-);
-
-
-CREATE TABLE product (
- nr INT,
- label STRING,
- comment STRING,
- producer INT,
- FOREIGN KEY (producer) REFERENCES producer(nr),
- propertyNum1 INT,
- propertyNum2 INT,
- propertyNum3 INT,
- propertyNum4 INT,
- propertyNum5 INT,
- propertyNum6 INT,
- propertyTex1 STRING,
- propertyTex2 STRING,
- propertyTex3 STRING,
- propertyTex4 STRING,
- propertyTex5 STRING,
- propertyTex6 STRING,
- publisher INT,
- publishDate DATE,
- PRIMARY KEY (nr)
-);
-
-
-CREATE TABLE productfeature (
- nr INT,
- label STRING,
- comment STRING,
- publisher INT,
- publishDate DATE,
- PRIMARY KEY (nr)
-);
-
-
-CREATE TABLE productfeatureproduct (
- product INT,
- productFeature INT,
- PRIMARY KEY (productFeature)
-);
-
-
-CREATE TABLE producttype (
- nr INT,
- label STRING,
- comment STRING,
- parent INT,
- publisher INT,
- publishDate DATE,
- PRIMARY KEY (nr)
-);
-
-
-CREATE TABLE producttypeproduct (
- product INT,
- productType INT,
- PRIMARY KEY (productType)
-);
-
-
-CREATE TABLE review (
- nr INT,
- product INT,
- producer INT,
- person INT,
- reviewDate DATETIME,
- title STRING,
- text STRING,
- language STRING,
- rating1 INT,
- rating2 INT,
- rating3 INT,
- rating4 INT,
- publisher INT,
- publishDate DATE,
- PRIMARY KEY (nr)
-);
-
-
-CREATE TABLE vendor (
- nr INT,
- label STRING,
- comment STRING,
- homepage STRING,
- country STRING,
- publisher INT,
- publishDate DATE,
- PRIMARY KEY (nr)
-);
-"""
- /*
- productfeatureproduct ( PRIMARY KEY (product,productFeature) );
- producttypeproduct ( PRIMARY KEY (product,productType) );
- */
- val bsbmDb:Database = DDLParser.parseAll(DDLParser.ddl, bsbmDdl).get
-
- val db2bsbmP = Sparql() // re-use ConstructParser ?
- val db2bsbm = db2bsbmP.parseAll(db2bsbmP.construct, """
-PREFIX map: <file:/E:/code/d2r-server-0.4/d2r-mapping.n3#>
-PREFIX vocab: <vocab/>
-PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
-PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
-PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
-PREFIX d2rq: <http://www.wiwiss.fu-berlin.de/suhl/bizer/D2RQ/0.1#>
-PREFIX dc: <http://purl.org/dc/elements/1.1/>
-PREFIX bsbm: <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/vocabulary/>
-PREFIX bsbm-inst: <http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/>
-PREFIX foaf: <http://xmlns.com/foaf/0.1/>
-PREFIX rev: <http://purl.org/stuff/rev#>
-
-PREFIX offer: <http://bsbm.example/db/offer#>
-PREFIX person: <http://bsbm.example/db/person#>
-PREFIX producer: <http://bsbm.example/db/producer#>
-PREFIX product: <http://bsbm.example/db/product#>
-PREFIX productfeatureproduct: <http://bsbm.example/db/productfeatureproduct#>
-PREFIX producttypeproduct: <http://bsbm.example/db/producttypeproduct#>
-PREFIX productfeature: <http://bsbm.example/db/productfeature#>
-PREFIX producttype: <http://bsbm.example/db/producttype#>
-PREFIX review: <http://bsbm.example/db/review#>
-PREFIX vendor: <http://bsbm.example/db/vendor#>
-
-PREFIX tr: <http://www.w3.org/2008/04/SPARQLfed/#>
-
-CONSTRUCT {
- ?product a bsbm:Product .
- ?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"),
- 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"),
- 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-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 product:Type59 .
- ?product bsbm:productFeature product:Feature5 .
- ?product bsbm:productFeature product:Feature7 .
-?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 WHERE {
- ?product product:label ?label .
- ?product product:propertyNum1 ?value1 .
- <http://bsbm.example/db/producttypeproduct/productType.59#record> ptp:product ?product .
- <http://bsbm.example/db/productfeatureproduct/productFeature.5#record> 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 = SqlParser()
- val sqlExpected = sqlParser.parseAll(sqlParser.select, """
-SELECT DISTINCT R_product.label AS label, R_product.nr AS product
- 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_productFeature5
- ON R_productFeature5.product=R_product.nr
- AND R_productFeature5.productFeature=5
- 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 = """
-"""
- }
-
-
- 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 = SqlParser()
- 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 = SqlParser()
- 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 = SqlParser()
- 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)
- }
-
-
-}