--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sparql2sql/src/test/scala/SparqlToSqlTest.scala Sun Oct 31 16:10:19 2010 -0400
@@ -0,0 +1,1070 @@
+/* SparqlToSqlTest: transform SPARQL to SQL and compare against a reference query.
+ * $Id$
+ */
+
+package org.w3c.sw.sparql2sql
+
+import org.scalatest.FunSuite
+import java.net.URI
+import org.w3.sw.rdb.RDB.{RelName,AttrName,Database,Relation}
+import org.w3.sw.sql.SqlParser
+import org.w3.sw.sparql.{Sparql, Uri}
+import org.w3.sw.sparql2sql.{SparqlToSql,StemURI,SqlToXMLRes}
+
+/* The SparqlToSqlTest class transforms SPARQL queries to a relational data
+ * structure and compares them to a structure parsed from SQL.
+ */
+class SparqlToSqlTest extends FunSuite {
+
+ /* These tests use a schema and queries designed to work with this
+ * example database:
+ Employee+----------+------------+---------+
+ | empid | lastName | birthday | manager |
+ +-------+----------+------------+---------+
+ | 18 | Johnson | 1969-11-08 | NULL | TaskAssignments--------+
+ | 253 | Smith | 1979-01-18 | 18 | | id | task | employee |
+ | 255 | Jones | 1981-03-24 | 253 | +----+------+----------+
+ | 19 | Xu | 1966-11-08 | NULL | | 1 | 1 | 18 |
+ | 254 | Ishita | 1971-10-31 | 253 | | 2 | 2 | 253 |
+ +-------+----------+------------+---------+ | 3 | 3 | 19 |
+ | 4 | 4 | 253 |
+ Tasks----+--------+------+ | 5 | 1 | 253 |
+ | taskid | name | lead | | 6 | 2 | 255 |
+ +--------+--------+------+ | 7 | 3 | 255 |
+ | 1 | widget | 18 | | 8 | 4 | 254 |
+ | 2 | dingus | 253 | +----+------+----------+
+ | 3 | thingy | 18 |
+ | 4 | toy | 253 |
+ +--------+--------+------+
+
+ INSERT INTO Employee (empid, lastName, birthday, manager) VALUES ( 18, "Johnson" , 1969-11-08, NULL);
+ INSERT INTO Employee (empid, lastName, birthday, manager) VALUES (253, "Smith" , 1979-01-18, 18);
+ INSERT INTO Employee (empid, lastName, birthday, manager) VALUES (255, "Jones" , 1981-03-24, 253);
+ INSERT INTO Employee (empid, lastName, birthday, manager) VALUES ( 19, "Xu" , 1966-11-08, NULL);
+ INSERT INTO Employee (empid, lastName, birthday, manager) VALUES (254, "Ishita" , 1971-10-31, 253);
+
+ INSERT INTO Tasks (taskid, name, lead) VALUES (1, "widget", 18);
+ INSERT INTO Tasks (taskid, name, lead) VALUES (2, "dingus", 253);
+ INSERT INTO Tasks (taskid, name, lead) VALUES (3, "thingy", 18);
+ INSERT INTO Tasks (taskid, name, lead) VALUES (4, "toy" , 253);
+
+ INSERT INTO TaskAssignments (id, task, employee) VALUES (1, 1, 18);
+ INSERT INTO TaskAssignments (id, task, employee) VALUES (2, 2, 253);
+ INSERT INTO TaskAssignments (id, task, employee) VALUES (3, 3, 19);
+ INSERT INTO TaskAssignments (id, task, employee) VALUES (4, 4, 253);
+ INSERT INTO TaskAssignments (id, task, employee) VALUES (5, 1, 253);
+ INSERT INTO TaskAssignments (id, task, employee) VALUES (6, 2, 255);
+ INSERT INTO TaskAssignments (id, task, employee) VALUES (7, 3, 255);
+ INSERT INTO TaskAssignments (id, task, employee) VALUES (8, 4, 254);
+ */
+
+ val DDLParser = SqlParser()
+ val dbDdl = """
+CREATE TABLE Employee (empid INT, PRIMARY KEY (empid), 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 db:Database = DDLParser.parseAll(DDLParser.ddl, dbDdl).get
+
+/* The reference RDF representation (queriable with the SPARQL in the tests) is:
+ */
+
+ val dbAsTurtle = """
+PREFIX empP : <http://hr.example/DB/Employee#>
+PREFIX task : <http://hr.example/DB/Tasks#>
+PREFIX tass : <http://hr.example/DB/TaskAssignments#>
+PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
+
+<http://hr.example/DB/Employee/empid.18#record>
+ empP:lastName "Johnson"^^xsd:string ; empP:birthday "1969-11-08"^^xsd:date .
+<http://hr.example/DB/Employee/empid.253#record>
+ empP:lastName "Smith"^^xsd:string ; empP:birthday "1969-11-08"^^xsd:date ;
+ empP:manager <http://hr.example/DB/Employee/empid.18#record> .
+<http://hr.example/DB/Employee/empid.255#record>
+ empP:lastName "Jones"^^xsd:string ; empP:birthday "1981-03-24"^^xsd:date ;
+ empP:manager <http://hr.example/DB/Employee/empid.253#record> .
+<http://hr.example/DB/Employee/empid.19#record>
+ empP:lastName "Xu"^^xsd:string ; empP:birthday "1966-11-08"^^xsd:date .
+<http://hr.example/DB/Employee/empid.254#record>
+ empP:lastName "Ishita"^^xsd:string ; empP:birthday "1971-10-31"^^xsd:date ;
+ empP:manager <http://hr.example/DB/Employee/empid.253#record> .
+
+<http://hr.example/DB/Tasks/taskid.1#record>
+ task:lastName "widget"^^xsd:string ;
+ task:manager <http://hr.example/DB/Employee/empid.18#record> .
+<http://hr.example/DB/Tasks/taskid.2#record>
+ task:lastName "dingus"^^xsd:string ;
+ task:manager <http://hr.example/DB/Employee/empid.253#record> .
+<http://hr.example/DB/Tasks/taskid.3#record>
+ task:lastName "thingy"^^xsd:string ;
+ task:manager <http://hr.example/DB/Employee/empid.18#record> .
+<http://hr.example/DB/Tasks/taskid.4#record>
+ task:lastName "toy"^^xsd:string ;
+ task:manager <http://hr.example/DB/Employee/empid.253#record> .
+
+<http://hr.example/DB/TaskAssignment/id.1#record>
+ tass:task <http://hr.example/DB/Tasks/taskid.1#record>
+ tass:employee <http://hr.example/DB/Employee/empid.18#record> .
+<http://hr.example/DB/TaskAssignment/id.2#record>
+ tass:task <http://hr.example/DB/Tasks/taskid.2#record>
+ tass:employee <http://hr.example/DB/Employee/empid.253#record> .
+<http://hr.example/DB/TaskAssignment/id.3#record>
+ tass:task <http://hr.example/DB/Tasks/taskid.3#record>
+ tass:employee <http://hr.example/DB/Employee/empid.19#record> .
+<http://hr.example/DB/TaskAssignment/id.4#record>
+ tass:task <http://hr.example/DB/Tasks/taskid.4#record>
+ tass:employee <http://hr.example/DB/Employee/empid.253#record> .
+<http://hr.example/DB/TaskAssignment/id.5#record>
+ tass:task <http://hr.example/DB/Tasks/taskid.1#record>
+ tass:employee <http://hr.example/DB/Employee/empid.253#record> .
+<http://hr.example/DB/TaskAssignment/id.6#record>
+ tass:task <http://hr.example/DB/Tasks/taskid.2#record>
+ tass:employee <http://hr.example/DB/Employee/empid.255#record> .
+<http://hr.example/DB/TaskAssignment/id.7#record>
+ tass:task <http://hr.example/DB/Tasks/taskid.3#record>
+ tass:employee <http://hr.example/DB/Employee/empid.255#record> .
+<http://hr.example/DB/TaskAssignment/id.8#record>
+ tass:task <http://hr.example/DB/Tasks/taskid.4#record>
+ tass:employee <http://hr.example/DB/Employee/empid.254#record> .
+"""
+ /* The obvious test is that the results from the SPARQL query and the
+ * relational query match.
+ *
+ * Data can be converted to turtle strings, or left as native formats for
+ * mapping the the querier. The first examples constrast queries relying
+ * on a post-query transformation against those returing turtle atoms.
+ */
+
+ test("decompose a predicate uri in stem, rel and attr") {
+ val uri = Uri("http://hr.example/our/favorite/DB/Employee#lastName")
+ val puri:SparqlToSql.PUri = SparqlToSql.parsePredicateURI(uri)
+ assert(puri === SparqlToSql.PUri(SparqlToSql.Stem("http://hr.example/our/favorite/DB"),
+ SparqlToSql.Rel("Employee"),
+ SparqlToSql.Attr("lastName")))
+ }
+
+ test("decompose a object uri in stem, rel and attr") {
+ val uri = Uri("http://hr.example/our/favorite/DB/Employee/id.18#record")
+ val objuri:SparqlToSql.NodeUri = SparqlToSql.parseObjectURI(uri)
+ assert(objuri === SparqlToSql.NodeUri(SparqlToSql.Stem("http://hr.example/our/favorite/DB"),
+ SparqlToSql.Rel("Employee"),
+ SparqlToSql.Attr("id"),
+ SparqlToSql.CellValue("18")))
+ }
+
+ /* Disable turtle string-izing (SparqlToSql parm 5) and return native format: */
+ test("?s <p> <x>") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX empP : <http://hr.example/DB/Employee#>
+SELECT ?emp {
+?emp empP:manager <http://hr.example/DB/Employee/empid.18#record>
+}
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT R_emp.empid AS emp
+ FROM Employee AS R_emp
+ INNER JOIN Employee AS R_empid18
+ WHERE R_empid18.empid=R_emp.manager AND R_empid18.empid=18 AND R_emp.empid IS NOT NULL
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, false)._1
+ assert(generated === parsed)
+ val output = """
++-----+
+| emp |
++-----+
+| 253 |
++-----+
+"""
+ }
+
+ /* Re-interpret native format: */
+ test("RDF(?s)") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX empP : <http://hr.example/DB/Employee#>
+SELECT ?emp {
+?emp empP:manager <http://hr.example/DB/Employee/empid.18#record>
+}
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT R_emp.empid AS emp
+ FROM Employee AS R_emp
+ INNER JOIN Employee AS R_empid18
+ WHERE R_empid18.empid=R_emp.manager AND R_empid18.empid=18 AND R_emp.empid IS NOT NULL
+""").get
+ val xmlres = """<?xml version="1.0"?>
+<sparql xmlns="http://www.w3.org/2005/sparql-results#">
+ <head>
+ <variable name="emp"/>
+ </head>
+
+ <results>
+ <result>
+ <binding name="emp">
+ <uri>http://hr.example/DB/Employee/empid.253#record</uri>
+ </binding>
+ </result>
+ </results>
+</sparql>
+""" //"//
+ val stem = StemURI("http://hr.example/DB/")
+ val (generated, rdfmap) = SparqlToSql(db, sparqlSelect, stem, true, false)
+ val head = SqlToXMLRes.head(List[String]("emp"))
+ val body = SqlToXMLRes.startresult + SqlToXMLRes.binding("emp", "253", rdfmap, stem) + SqlToXMLRes.endresult
+ val foot = SqlToXMLRes.foot
+ assert(generated === parsed)
+ assert(head + body + foot === xmlres)
+ val output = """
++-----+
+| emp |
++-----+
+| 253 |
++-----+
+"""
+ }
+
+ /* Enable turtle string-izing and test URI generation: */
+ test("SELECT <x> { ?sf <p> <x>} (in-SQL Nodizer)") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX empP : <http://hr.example/DB/Employee#>
+SELECT ?emp {
+?emp empP:manager <http://hr.example/DB/Employee/empid.18#record>
+}
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT CONCAT("http://hr.example/DB/", "Employee", "/", "empid", ".", R_emp.empid, "#record") AS emp
+ FROM Employee AS R_emp
+ INNER JOIN Employee AS R_empid18
+ WHERE R_empid18.empid=R_emp.manager AND R_empid18.empid=18 AND R_emp.empid IS NOT NULL
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, true)._1
+ assert(generated === parsed)
+ val output = """
++------------------------------------------------+
+| emp |
++------------------------------------------------+
+| http://hr.example/DB/Employee/empid.253#record |
++------------------------------------------------+
+"""
+ }
+
+ /* Enable turtle string-izing and test RDFLiteral generation: */
+ test("SELECT <x> { ?sf <p> \"asdf\"} (in-SQL Nodizer)") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX empP : <http://hr.example/DB/Employee#>
+SELECT ?name {
+?emp empP:lastName ?name
+}
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT CONCAT("'", R_emp.lastName, "'^^<http://www.w3.org/2001/XMLSchema#string>") AS name
+ FROM Employee AS R_emp
+ WHERE R_emp.empid IS NOT NULL AND R_emp.lastName IS NOT NULL
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, true)._1
+ assert(generated === parsed)
+ val output = """
++----------------------------------------------------+
+| name |
++----------------------------------------------------+
+| Johnson^^<http://www.w3.org/2001/XMLSchema#string> |
+| Smith^^<http://www.w3.org/2001/XMLSchema#string> |
+| Jones^^<http://www.w3.org/2001/XMLSchema#string> |
+| Xu^^<http://www.w3.org/2001/XMLSchema#string> |
+| Ishita^^<http://www.w3.org/2001/XMLSchema#string> |
++----------------------------------------------------+
+"""
+ }
+
+ test("<s> <p> ?x") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX empP : <http://hr.example/DB/Employee#>
+SELECT ?manager {
+<http://hr.example/DB/Employee/empid.253#record> empP:manager ?manager
+}
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT R_manager.empid AS manager
+ FROM Employee AS R_empid253
+ INNER JOIN Employee AS R_manager
+ WHERE R_manager.empid=R_empid253.manager AND R_empid253.empid=253
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, false)._1
+ assert(generated === parsed)
+ val output = """
++---------+
+| manager |
++---------+
+| 18 |
++---------+
+"""
+ }
+
+ test("?s <p> 18") {
+ /* Literal foreign keys should probably throw an error,
+ * instead does what user meant. */
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX empP : <http://hr.example/DB/Employee#>
+PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
+SELECT ?emp {
+?emp empP:manager "18"^^xsd:integer
+}
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT R_emp.empid AS emp
+ FROM Employee AS R_emp
+ INNER JOIN Employee AS R_18
+ WHERE R_18.empid=R_emp.manager AND R_18.empid=18 AND R_emp.empid IS NOT NULL
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, false)._1
+ assert(generated === parsed)
+ val output = """
++-----+
+| emp |
++-----+
+| 253 |
++-----+
+"""
+ }
+
+ test("?s1 <p> ?x . ?s2 <p> ?x") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX tass : <http://hr.example/DB/TaskAssignments#>
+SELECT ?task1 ?task2 {
+ ?task1 tass:employee ?who .
+ ?task2 tass:employee ?who
+ FILTER(?task1 < ?task2)
+}
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT R_task1.id AS task1, R_task2.id AS task2
+ FROM TaskAssignments AS R_task1
+ INNER JOIN Employee AS R_who ON R_who.empid=R_task1.employee
+ INNER JOIN TaskAssignments AS R_task2 ON R_who.empid=R_task2.employee
+ WHERE R_task1.id<R_task2.id
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, false)._1
+ assert(generated === parsed)
+ val output = """
++-------+-------+
+| task1 | task2 |
++-------+-------+
+| 2 | 4 |
+| 2 | 5 |
+| 4 | 5 |
+| 6 | 7 |
++-------+-------+
+"""
+ }
+
+ test("transform SQLbgp") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX empP : <http://hr.example/DB/Employee#>
+SELECT ?empName ?manageName {
+?emp empP:lastName ?empName .
+?emp empP:manager ?manager .
+?manager empP:lastName ?manageName
+}
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT R_emp.lastName AS empName, R_manager.lastName AS manageName
+ FROM Employee AS R_emp
+ INNER JOIN Employee AS R_manager ON R_manager.empid=R_emp.manager
+ WHERE R_emp .lastName IS NOT NULL AND R_manager.lastName IS NOT NULL
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, false)._1
+ assert(generated === parsed)
+ val output = """
++---------+------------+
+| empName | manageName |
++---------+------------+
+| Smith | Johnson |
+| Jones | Smith |
+| Ishita | Smith |
++---------+------------+
+"""
+ }
+
+ test("transform tup1 no-enforce") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX empP : <http://hr.example/DB/Employee#>
+SELECT ?empName {
+ ?emp empP:lastName ?empName .
+ ?emp empP:manager <http://hr.example/DB/Employee/empid.18#record>
+ }
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT R_emp.lastName AS empName
+ FROM Employee AS R_emp
+ WHERE R_emp.manager=18 AND R_emp.lastName IS NOT NULL
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
+ assert(generated === parsed)
+ val output = """
++---------+
+| empName |
++---------+
+| Smith |
++---------+
+"""
+ }
+
+ test("transform tup1 enforce") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX empP : <http://hr.example/DB/Employee#>
+SELECT ?empName {
+ ?emp empP:lastName ?empName .
+ ?emp empP:manager <http://hr.example/DB/Employee/empid.18#record>
+ }
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT R_emp.lastName AS empName
+ FROM Employee AS R_emp
+ INNER JOIN Employee AS R_empid18
+ WHERE R_empid18.empid=R_emp.manager AND R_empid18.empid=18 AND R_emp.lastName IS NOT NULL
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, false)._1
+ assert(generated === parsed)
+ val output = """
++---------+
+| empName |
++---------+
+| Smith |
++---------+
+"""
+ }
+
+
+ test("transform litConst1") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX empP : <http://hr.example/DB/Employee#>
+PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
+SELECT ?empName {
+ ?emp empP:lastName ?empName .
+ ?emp empP:manager ?manager .
+ ?manager empP:lastName "Johnson"^^xsd:string
+ }
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT R_emp.lastName AS empName
+ FROM Employee AS R_emp
+ INNER JOIN Employee AS R_manager
+WHERE R_manager.empid=R_emp.manager AND R_manager.lastName="Johnson" AND R_emp.lastName IS NOT NULL
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, false)._1
+ assert(generated === parsed)
+ val output = """
++---------+
+| empName |
++---------+
+| Smith |
++---------+
+"""
+ }
+
+ test("transform filter1") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX empP : <http://hr.example/DB/Employee#>
+PREFIX task : <http://hr.example/DB/Tasks#>
+PREFIX tass : <http://hr.example/DB/TaskAssignments#>
+SELECT ?empName ?grandManagName {
+ ?emp empP:lastName ?empName .
+ ?emp empP:birthday ?empBday .
+ ?lower tass:employee ?emp .
+ ?lower tass:task ?ltask .
+ ?ltask task:lead ?taskLead .
+ ?taskLead empP:birthday ?manBday .
+ ?upper tass:employee ?taskLead .
+ ?upper tass:task ?utask .
+ ?utask task:lead ?grandManager .
+ ?grandManager empP:birthday ?grandManBday .
+ ?grandManager empP:lastName ?grandManagName
+ FILTER (?manBday < ?empBday && ?grandManBday < ?manBday)
+}
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT R_emp.lastName AS empName,
+ R_grandManager.lastName AS grandManagName
+ FROM Employee AS R_emp
+ INNER JOIN TaskAssignments AS R_lower ON R_emp.empid=R_lower.employee
+ INNER JOIN Tasks AS R_ltask ON R_ltask.taskid=R_lower.task
+ INNER JOIN Employee AS R_taskLead ON R_taskLead.empid=R_ltask.lead
+ INNER JOIN TaskAssignments AS R_upper ON R_taskLead.empid=R_upper.employee
+ INNER JOIN Tasks AS R_utask ON R_utask.taskid=R_upper.task
+ INNER JOIN Employee AS R_grandManager ON R_grandManager.empid=R_utask.lead
+ WHERE R_taskLead.birthday<R_emp.birthday AND R_grandManager.birthday<R_taskLead.birthday
+ AND R_emp.lastName IS NOT NULL AND R_grandManager.lastName IS NOT NULL
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, false)._1
+ assert(generated === parsed)
+ val output = """
++---------+----------------+
+| empName | grandManagName |
++---------+----------------+
+| Jones | Johnson |
++---------+----------------+
+"""
+ }
+
+ /* Employess above and below Smith */
+ test("transform disj1") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX empP : <http://hr.example/DB/Employee#>
+PREFIX task : <http://hr.example/DB/Tasks#>
+PREFIX tass : <http://hr.example/DB/TaskAssignments#>
+SELECT ?name
+ WHERE { ?who empP:lastName "Smith"^^xsd:string
+ { ?above tass:employee ?who .
+ ?above tass:task ?atask .
+ ?atask task:lead ?taskLead .
+ ?taskLead empP:lastName ?name }
+ UNION
+ { ?below tass:task ?btask .
+ ?btask task:lead ?who .
+ ?below tass:employee ?managed .
+ ?managed empP:lastName ?name } }
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT G_union1.name AS name
+ FROM Employee AS R_who
+ INNER JOIN (
+ SELECT 0 AS _DISJOINT_, R_above.id AS above, R_above.task AS atask,
+ R_above.employee AS who, R_taskLead.lastName AS name,
+ R_atask.lead AS taskLead,
+ NULL AS below, NULL AS btask, NULL AS managed
+ FROM TaskAssignments AS R_above
+ INNER JOIN Tasks AS R_atask ON R_atask.taskid=R_above.task
+ INNER JOIN Employee AS R_taskLead ON R_taskLead.empid=R_atask.lead
+ WHERE R_above.employee IS NOT NULL
+ AND R_taskLead.lastName IS NOT NULL
+ UNION
+ SELECT 1 AS _DISJOINT_, NULL AS above, NULL AS atask,
+ R_btask.lead AS who,R_managed.lastName AS name,
+ NULL AS taskLead,
+ R_below.id AS below, R_below.task AS btask,
+ R_below.employee AS managed
+ FROM TaskAssignments AS R_below
+ INNER JOIN Tasks AS R_btask ON R_btask.taskid=R_below.task
+ INNER JOIN Employee AS R_managed ON R_managed.empid=R_below.employee
+ WHERE R_managed.lastName IS NOT NULL
+ AND R_btask.lead IS NOT NULL
+ ) AS G_union1
+ WHERE R_who.lastName="Smith"
+ AND R_who.empid IS NOT NULL
+ AND (G_union1._DISJOINT_!=0 OR G_union1.who=R_who.empid)
+ AND (G_union1._DISJOINT_!=1 OR G_union1.who=R_who.empid)
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
+ assert(generated === parsed)
+ val output = """
++---------+
+| name |
++---------+
+| Johnson |
+| Smith |
+| Smith |
+| Smith |
+| Jones |
+| Smith |
+| Ishita |
++---------+
+"""
+ }
+
+ test("transform assymDisj1") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX empP : <http://hr.example/DB/Employee#>
+PREFIX task : <http://hr.example/DB/Tasks#>
+PREFIX tass : <http://hr.example/DB/TaskAssignments#>
+PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
+SELECT ?name ?bday
+ WHERE { { ?above tass:employee ?who .
+ ?above tass:task ?atask .
+ ?atask task:lead ?taskLead .
+ ?taskLead empP:lastName ?name }
+ UNION
+ { ?below tass:task ?btask .
+ ?btask task:lead ?who .
+ ?below tass:employee ?managed .
+ ?managed empP:lastName ?name .
+ ?managed empP:birthday ?bday }
+ ?who empP:lastName "Smith"^^xsd:string .
+ ?who empP:birthday ?bday }
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT G_union0.name AS name, G_union0.bday AS bday
+ FROM (
+ SELECT R_above.id AS above, R_above.task AS atask, NULL AS bday, NULL AS below,
+ NULL AS btask, NULL AS managed, R_taskLead.lastName AS name,
+ R_atask.lead AS taskLead, R_above.employee AS who, 0 AS _DISJOINT_
+ FROM TaskAssignments AS R_above
+ INNER JOIN Tasks AS R_atask ON R_atask.taskid=R_above.task
+ INNER JOIN Employee AS R_taskLead ON R_taskLead.empid=R_atask.lead
+ WHERE R_above.employee IS NOT NULL AND R_taskLead.lastName IS NOT NULL
+ UNION
+ SELECT NULL AS above, NULL AS atask, R_managed.birthday AS bday, R_below.id AS below,
+ R_below.task AS btask, R_below.employee AS managed, R_managed.lastName AS name,
+ NULL AS taskLead, R_btask.lead AS who, 1 AS _DISJOINT_
+ FROM TaskAssignments AS R_below
+ INNER JOIN Tasks AS R_btask ON R_btask.taskid=R_below.task
+ INNER JOIN Employee AS R_managed ON R_managed.empid=R_below.employee
+ WHERE R_btask.lead IS NOT NULL AND R_managed.birthday IS NOT NULL AND R_managed.lastName IS NOT NULL
+ ) AS G_union0
+ INNER JOIN Employee AS R_who
+ WHERE (G_union0._DISJOINT_!=0 OR R_who.empid=G_union0.who)
+ AND (G_union0._DISJOINT_!=1 OR R_who.birthday=G_union0.bday)
+ AND (G_union0._DISJOINT_!=1 OR R_who.empid=G_union0.who)
+ AND R_who.lastName="Smith"
+""").get // !!! AND (G_union0.bday IS NOT NULL) AND (G_union0.who IS NOT NULL)
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
+ assert(generated === parsed)
+ val output = """
+"""
+ }
+
+ test("transform assymDisj1 reversed") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX empP : <http://hr.example/DB/Employee#>
+PREFIX task : <http://hr.example/DB/Tasks#>
+PREFIX tass : <http://hr.example/DB/TaskAssignments#>
+PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
+SELECT ?name
+ WHERE {
+ ?who empP:lastName "Smith"^^xsd:string .
+ ?who empP:birthday ?bday
+ { ?above tass:employee ?who .
+ ?above tass:task ?atask .
+ ?atask task:lead ?taskLead .
+ ?taskLead empP:lastName ?name }
+ UNION
+ { ?below tass:task ?btask .
+ ?btask task:lead ?who .
+ ?below tass:employee ?managed .
+ ?managed empP:lastName ?name .
+ ?managed empP:birthday ?bday }
+ }
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT G_union1.name AS name
+ FROM Employee AS R_who
+ INNER JOIN (
+ SELECT R_above.id AS above, R_above.task AS atask, NULL AS bday, NULL AS below,
+ NULL AS btask, NULL AS managed, R_taskLead.lastName AS name,
+ R_atask.lead AS taskLead, R_above.employee AS who, 0 AS _DISJOINT_
+ FROM TaskAssignments AS R_above
+ INNER JOIN Tasks AS R_atask ON R_atask.taskid=R_above.task
+ INNER JOIN Employee AS R_taskLead ON R_taskLead.empid=R_atask.lead
+ WHERE R_above.employee IS NOT NULL AND R_taskLead.lastName IS NOT NULL
+ UNION
+ SELECT NULL AS above, NULL AS atask, R_managed.birthday AS bday, R_below.id AS below,
+ R_below.task AS btask, R_below.employee AS managed, R_managed.lastName AS name,
+ NULL AS taskLead, R_btask.lead AS who, 1 AS _DISJOINT_
+ FROM TaskAssignments AS R_below
+ INNER JOIN Tasks AS R_btask ON R_btask.taskid=R_below.task
+ INNER JOIN Employee AS R_managed ON R_managed.empid=R_below.employee
+ WHERE R_btask.lead IS NOT NULL AND R_managed.birthday IS NOT NULL AND R_managed.lastName IS NOT NULL
+ ) AS G_union1
+ WHERE (G_union1._DISJOINT_!=0 OR G_union1.who=R_who.empid)
+ AND (G_union1._DISJOINT_!=1 OR G_union1.bday=R_who.birthday)
+ AND (G_union1._DISJOINT_!=1 OR G_union1.who=R_who.empid)
+ AND R_who.birthday IS NOT NULL AND R_who.lastName="Smith"
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
+ assert(generated === parsed)
+ val output = """
+"""
+ }
+
+ test("transform assymDisj1 interspersed") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX empP : <http://hr.example/DB/Employee#>
+PREFIX task : <http://hr.example/DB/Tasks#>
+PREFIX tass : <http://hr.example/DB/TaskAssignments#>
+PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
+SELECT ?name
+ WHERE {
+ ?who empP:lastName "Smith"^^xsd:string
+ { ?above tass:employee ?who .
+ ?above tass:task ?atask .
+ ?atask task:lead ?taskLead .
+ ?taskLead empP:lastName ?name }
+ UNION
+ { ?below tass:task ?btask .
+ ?btask task:lead ?who .
+ ?below tass:employee ?managed .
+ ?managed empP:lastName ?name .
+ ?managed empP:birthday ?bday }
+ ?who empP:birthday ?bday
+ }
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT G_union1.name AS name
+ FROM Employee AS R_who
+ INNER JOIN (
+ SELECT R_above.id AS above, R_above.task AS atask, NULL AS bday, NULL AS below,
+ NULL AS btask, NULL AS managed, R_taskLead.lastName AS name,
+ R_atask.lead AS taskLead, R_above.employee AS who, 0 AS _DISJOINT_
+ FROM TaskAssignments AS R_above
+ INNER JOIN Tasks AS R_atask ON R_atask.taskid=R_above.task
+ INNER JOIN Employee AS R_taskLead ON R_taskLead.empid=R_atask.lead
+ WHERE R_above.employee IS NOT NULL AND R_taskLead.lastName IS NOT NULL
+ UNION
+ SELECT NULL AS above, NULL AS atask, R_managed.birthday AS bday, R_below.id AS below,
+ R_below.task AS btask, R_below.employee AS managed, R_managed.lastName AS name,
+ NULL AS taskLead, R_btask.lead AS who, 1 AS _DISJOINT_
+ FROM TaskAssignments AS R_below
+ INNER JOIN Tasks AS R_btask ON R_btask.taskid=R_below.task
+ INNER JOIN Employee AS R_managed ON R_managed.empid=R_below.employee
+ WHERE R_btask.lead IS NOT NULL AND R_managed.birthday IS NOT NULL AND R_managed.lastName IS NOT NULL
+ ) AS G_union1
+ WHERE (G_union1._DISJOINT_!=0 OR G_union1.who=R_who.empid)
+ AND (G_union1._DISJOINT_!=1 OR G_union1.who=R_who.empid)
+ AND (G_union1._DISJOINT_!=1 OR R_who.birthday=G_union1.bday)
+ AND R_who.lastName="Smith"
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
+ assert(generated === parsed)
+ val output = """
+"""
+ }
+
+ test("transform optJoin1") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX emplP: <http://hr.example/DB/Employee#>
+
+SELECT ?empName ?managName ?grandManagName
+ WHERE { ?emp emplP:lastName ?empName
+ OPTIONAL { ?emp emplP:manager ?manager .
+ ?manager emplP:lastName ?managName .
+ ?manager emplP:manager ?grandManager .
+ ?grandManager emplP:lastName ?grandManagName } }
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT R_emp.lastName AS empName, G_opt1.grandManagName AS grandManagName, G_opt1.managName AS managName
+ FROM Employee AS R_emp
+ LEFT OUTER JOIN (
+ SELECT R_emp.empid AS emp, R_grandManager.lastName AS grandManagName, R_manager.manager AS grandManager, R_manager.lastName AS managName, R_emp.manager AS manager, 1 AS _DISJOINT_
+ FROM Employee AS R_emp
+ INNER JOIN Employee AS R_manager ON R_manager.empid=R_emp.manager
+ INNER JOIN Employee AS R_grandManager ON R_grandManager.empid=R_manager.manager
+ WHERE R_emp.empid IS NOT NULL AND R_grandManager.lastName IS NOT NULL AND R_manager.lastName IS NOT NULL
+ ) AS G_opt1 ON G_opt1.emp=R_emp.empid
+ WHERE R_emp.empid IS NOT NULL AND R_emp.lastName IS NOT NULL
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
+ assert(generated === parsed)
+ val output = """
+"""
+ }
+
+ test("transform leadOpt1") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX emplP: <http://hr.example/DB/Employee#>
+
+SELECT ?empName ?grandManagName
+ WHERE { OPTIONAL { ?taskLead emplP:manager ?emp .
+ ?taskLead emplP:manager ?grandManager .
+ ?grandManager emplP:lastName ?grandManagName }
+ ?emp emplP:lastName ?empName }
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT R_emp.lastName AS empName, G_opt1.grandManagName AS grandManagName
+ FROM ( SELECT 1 AS _EMPTY_ ) AS _EMPTY_
+ LEFT OUTER JOIN (
+ SELECT R_taskLead.manager AS emp, R_grandManager.lastName AS grandManagName, R_taskLead.manager AS grandManager, R_taskLead.empid AS taskLead, 1 AS _DISJOINT_
+ FROM Employee AS R_taskLead
+ INNER JOIN Employee AS R_grandManager ON R_grandManager.empid=R_taskLead.manager
+ WHERE R_grandManager.lastName IS NOT NULL
+ ) AS G_opt1 ON 1=1
+ INNER JOIN Employee AS R_emp
+ WHERE (G_opt1._DISJOINT_ IS NULL OR R_emp.empid=G_opt1.emp)
+ AND R_emp.lastName IS NOT NULL
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
+ assert(generated === parsed)
+ val output = """
+"""
+ }
+
+ test("transform nestOpt") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX emplP: <http://hr.example/DB/Employee#>
+
+SELECT ?empName ?managName ?grandManagName
+ WHERE { ?emp emplP:lastName ?empName
+ OPTIONAL { ?emp emplP:manager ?manager .
+ ?manager emplP:lastName ?managName
+ OPTIONAL { ?manager emplP:manager ?grandManager .
+ ?grandManager emplP:lastName ?grandManagName } }
+ }
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT R_emp.lastName AS empName, G_opt1.grandManagName AS grandManagName, G_opt1.managName AS managName
+ FROM Employee AS R_emp
+ LEFT OUTER JOIN (
+ SELECT R_emp.empid AS emp, G_opt2.grandManagName AS grandManagName, G_opt2.grandManager AS grandManager, R_manager.lastName AS managName, R_emp.manager AS manager, 1 AS _DISJOINT_
+ FROM Employee AS R_emp
+ INNER JOIN Employee AS R_manager ON R_manager.empid=R_emp.manager
+ LEFT OUTER JOIN (
+ SELECT R_grandManager.lastName AS grandManagName, R_manager.manager AS grandManager, R_manager.empid AS manager, 2 AS _DISJOINT_
+ FROM Employee AS R_manager
+ INNER JOIN Employee AS R_grandManager ON R_grandManager.empid=R_manager.manager
+ WHERE R_grandManager.lastName IS NOT NULL AND R_manager.empid IS NOT NULL
+ ) AS G_opt2 ON G_opt2.manager=R_emp.manager
+ WHERE R_emp.empid IS NOT NULL
+ AND R_manager.lastName IS NOT NULL
+ ) AS G_opt1 ON G_opt1.emp=R_emp.empid
+ WHERE R_emp.empid IS NOT NULL AND R_emp.lastName IS NOT NULL
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
+ assert(generated === parsed)
+ val output = """
+"""
+ }
+
+ test("transform equivOpt1") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
+PREFIX emplP: <http://hr.example/DB/Employee#>
+
+SELECT ?emp1Name ?emp2Name ?emp3Name
+ WHERE { ?emp1 emplP:lastName ?emp1Name
+ OPTIONAL { ?emp1 emplP:birthday ?birthday }
+ ?emp2 emplP:lastName ?emp2Name
+ OPTIONAL { ?emp2 emplP:birthday ?birthday }
+ ?emp3 emplP:lastName ?emp3Name .
+ ?emp3 emplP:birthday ?birthday .
+ ?emp4 emplP:lastName ?emp4Name .
+ ?emp4 emplP:birthday ?birthday
+ FILTER ( ?emp1Name < ?emp2Name && ?emp2Name < ?emp3Name && ?emp3Name < ?emp4Name) }
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT R_emp1.lastName AS emp1Name, R_emp2.lastName AS emp2Name, R_emp3.lastName AS emp3Name
+ FROM Employee AS R_emp1
+ LEFT OUTER JOIN (
+ SELECT R_emp1.birthday AS birthday, R_emp1.empid AS emp1, 1 AS _DISJOINT_
+ FROM Employee AS R_emp1
+ WHERE R_emp1.birthday IS NOT NULL AND R_emp1.empid IS NOT NULL
+ ) AS G_opt1 ON G_opt1.emp1=R_emp1.empid
+ INNER JOIN Employee AS R_emp2
+ LEFT OUTER JOIN (
+ SELECT R_emp2.birthday AS birthday, R_emp2.empid AS emp2, 3 AS _DISJOINT_
+ FROM Employee AS R_emp2
+ WHERE R_emp2.birthday IS NOT NULL AND R_emp2.empid IS NOT NULL
+ ) AS G_opt3 ON (G_opt1._DISJOINT_ IS NULL OR G_opt3.birthday=G_opt1.birthday) AND G_opt3.emp2=R_emp2.empid
+ INNER JOIN Employee AS R_emp3
+ INNER JOIN Employee AS R_emp4
+ WHERE (G_opt1._DISJOINT_ IS NULL OR R_emp3.birthday=G_opt1.birthday)
+ AND (G_opt1._DISJOINT_ IS NULL OR R_emp4.birthday=G_opt1.birthday)
+ AND R_emp1.lastName<R_emp2.lastName
+ AND R_emp2.empid IS NOT NULL AND R_emp2.lastName<R_emp3.lastName
+ AND R_emp3.lastName<R_emp4.lastName AND R_emp1.empid IS NOT NULL
+""").get
+ val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
+ assert(generated === parsed)
+ val output = """
+"""
+ }
+
+ 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));
+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
+
+ test("swobjects/tests/healthCare/lists-notBound/db.rq AS OPTIONAL") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = 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
+ WHERE {
+ ?patient Person:MiddleName ?middleName .
+ ?patient Person:DateOfBirth ?dob .
+ ?patient Person:SexDE ?sexEntry .
+ ?sexEntry Sex_DE:EntryName ?sex .
+
+ ?indicItem Item_Medication:PatientID ?patient .
+ ?indicItem Item_Medication:PerformedDTTM ?indicDate .
+ ?indicItem Item_Medication:EntryName ?takes .
+ ?indicMed Medication:ItemID ?indicItem .
+ ?indicMed Medication:MedDictDE ?indicDE .
+ ?indicDE Medication_DE:NDC ?indicNDC .
+ ?indicCode NDCcodes:NDC ?indicNDC .
+ ?indicCode NDCcodes:ingredient "6809"^^xsd:integer
+
+ OPTIONAL {
+ ?disqualItem Item_Medication:PatientID ?patient .
+ ?disqualItem Item_Medication:PerformedDTTM ?disqualDate .
+ ?disqualItem Item_Medication:EntryName ?takes .
+ ?disqualMed Medication:ItemID ?disqualItem .
+ ?disqualMed Medication:MedDictDE ?disqualDE .
+ ?disqualDE Medication_DE:NDC ?disqualNDC .
+ ?disqualCode NDCcodes:NDC ?disqualNDC .
+ ?disqualCode NDCcodes:ingredient "11289"^^xsd:integer
+ }
+ }
+""").get // FILTER (!BOUND(?disqualItem))
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT R_patient.ID AS patient
+ FROM Person AS R_patient
+ INNER JOIN Sex_DE AS R_sexEntry ON R_sexEntry.ID=R_patient.SexDE
+ INNER JOIN Item_Medication AS R_indicItem ON R_indicItem.PatientID=R_patient.ID
+ INNER JOIN Medication AS R_indicMed ON R_indicMed.ItemID=R_indicItem.ID
+ INNER JOIN Medication_DE AS R_indicDE ON R_indicDE.ID=R_indicMed.MedDictDE
+ INNER JOIN NDCcodes AS R_indicCode ON R_indicCode.NDC=R_indicDE.NDC
+ LEFT OUTER JOIN (
+ SELECT R_disqualCode.ID AS disqualCode, R_disqualMed.MedDictDE AS disqualDE,
+ R_disqualItem.PerformedDTTM AS disqualDate, R_disqualItem.ID AS disqualItem,
+ R_disqualMed.ID AS disqualMed, R_disqualDE.NDC AS disqualNDC,
+ R_disqualItem.PatientID AS patient, R_disqualItem.EntryName AS takes, 6 AS _DISJOINT_
+ FROM Item_Medication AS R_disqualItem
+ INNER JOIN Medication AS R_disqualMed ON R_disqualMed.ItemID=R_disqualItem.ID
+ INNER JOIN Medication_DE AS R_disqualDE ON R_disqualDE.ID=R_disqualMed.MedDictDE
+ INNER JOIN NDCcodes AS R_disqualCode ON R_disqualCode.NDC=R_disqualDE.NDC
+ WHERE R_disqualCode.ingredient=11289
+ AND R_disqualItem.EntryName IS NOT NULL
+ AND R_disqualItem.PatientID IS NOT NULL
+ AND R_disqualItem.PerformedDTTM IS NOT NULL
+ ) AS G_opt6 ON G_opt6.patient=R_patient.ID
+ AND G_opt6.takes=R_indicItem.EntryName
+ WHERE R_indicCode.ingredient=6809
+ AND R_indicItem.EntryName IS NOT NULL
+ AND R_indicItem.PerformedDTTM IS NOT NULL
+ AND R_patient.DateOfBirth IS NOT NULL
+ AND R_patient.MiddleName IS NOT NULL
+ AND R_sexEntry.EntryName IS NOT NULL
+""").get // AND G_opt6.patient IS NULL
+ val generated = SparqlToSql(hosp1, sparqlSelect, StemURI("http://hospital.example/DB/"), false, false)._1
+ assert(generated === parsed)
+ val output = """
+"""
+ }
+
+ test("swobjects/tests/healthCare/lists-notBound/db.rq") {
+ val sparqlParser = Sparql()
+ val sparqlSelect = 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
+ WHERE {
+ ?patient Person:MiddleName ?middleName .
+ ?patient Person:DateOfBirth ?dob .
+ ?patient Person:SexDE ?sexEntry .
+ ?sexEntry Sex_DE:EntryName ?sex .
+
+ ?indicItem Item_Medication:PatientID ?patient .
+ ?indicItem Item_Medication:PerformedDTTM ?indicDate .
+ ?indicItem Item_Medication:EntryName ?takes .
+ ?indicMed Medication:ItemID ?indicItem .
+ ?indicMed Medication:MedDictDE ?indicDE .
+ ?indicDE Medication_DE:NDC ?indicNDC .
+ ?indicCode NDCcodes:NDC ?indicNDC .
+ ?indicCode NDCcodes:ingredient "6809"^^xsd:integer
+
+ MINUS {
+ ?disqualItem Item_Medication:PatientID ?patient .
+ ?disqualItem Item_Medication:PerformedDTTM ?disqualDate .
+ ?disqualItem Item_Medication:EntryName ?takes .
+ ?disqualMed Medication:ItemID ?disqualItem .
+ ?disqualMed Medication:MedDictDE ?disqualDE .
+ ?disqualDE Medication_DE:NDC ?disqualNDC .
+ ?disqualCode NDCcodes:NDC ?disqualNDC .
+ ?disqualCode NDCcodes:ingredient "11289"^^xsd:integer
+ }
+ }
+""").get
+ val sqlParser = SqlParser()
+ val parsed = sqlParser.parseAll(sqlParser.select, """
+SELECT R_patient.ID AS patient
+ FROM Person AS R_patient
+ INNER JOIN Sex_DE AS R_sexEntry ON R_sexEntry.ID=R_patient.SexDE
+ INNER JOIN Item_Medication AS R_indicItem ON R_indicItem.PatientID=R_patient.ID
+ INNER JOIN Medication AS R_indicMed ON R_indicMed.ItemID=R_indicItem.ID
+ INNER JOIN Medication_DE AS R_indicDE ON R_indicDE.ID=R_indicMed.MedDictDE
+ INNER JOIN NDCcodes AS R_indicCode ON R_indicCode.NDC=R_indicDE.NDC
+ LEFT OUTER JOIN (
+ SELECT R_disqualCode.ID AS disqualCode, R_disqualMed.MedDictDE AS disqualDE,
+ R_disqualItem.PerformedDTTM AS disqualDate, R_disqualItem.ID AS disqualItem,
+ R_disqualMed.ID AS disqualMed, R_disqualDE.NDC AS disqualNDC,
+ R_disqualItem.PatientID AS patient, R_disqualItem.EntryName AS takes, 6 AS _DISJOINT_
+ FROM Item_Medication AS R_disqualItem
+ INNER JOIN Medication AS R_disqualMed ON R_disqualMed.ItemID=R_disqualItem.ID
+ INNER JOIN Medication_DE AS R_disqualDE ON R_disqualDE.ID=R_disqualMed.MedDictDE
+ INNER JOIN NDCcodes AS R_disqualCode ON R_disqualCode.NDC=R_disqualDE.NDC
+ WHERE R_disqualCode.ingredient=11289
+ AND R_disqualItem.EntryName IS NOT NULL
+ AND R_disqualItem.PatientID IS NOT NULL
+ AND R_disqualItem.PerformedDTTM IS NOT NULL
+ ) AS G_opt6 ON G_opt6.patient=R_patient.ID
+ AND G_opt6.takes=R_indicItem.EntryName
+ WHERE G_opt6._DISJOINT_ IS NULL
+ AND R_indicCode.ingredient=6809
+ AND R_indicItem.EntryName IS NOT NULL
+ AND R_indicItem.PerformedDTTM IS NOT NULL
+ AND R_patient.DateOfBirth IS NOT NULL
+ AND R_patient.MiddleName IS NOT NULL
+ AND R_sexEntry.EntryName IS NOT NULL
+""").get
+ val generated = SparqlToSql(hosp1, sparqlSelect, StemURI("http://hospital.example/DB/"), false, false)._1
+ assert(generated === parsed)
+ val output = """
+"""
+ }
+
+}
--- a/src/test/scala/SparqlToSqlTest.scala Sun Oct 31 16:06:05 2010 -0400
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,1070 +0,0 @@
-/* SparqlToSqlTest: transform SPARQL to SQL and compare against a reference query.
- * $Id$
- */
-
-package w3c.sw
-
-import org.scalatest.FunSuite
-import java.net.URI
-import w3c.sw.sql.RDB.{RelName,AttrName,Database,Relation}
-import w3c.sw.sql.SqlParser
-import w3c.sw.sparql.Sparql
-import w3c.sw.sparql2sql.{SparqlToSql,StemURI,SqlToXMLRes}
-
-/* The SparqlToSqlTest class transforms SPARQL queries to a relational data
- * structure and compares them to a structure parsed from SQL.
- */
-class SparqlToSqlTest extends FunSuite {
-
- /* These tests use a schema and queries designed to work with this
- * example database:
- Employee+----------+------------+---------+
- | empid | lastName | birthday | manager |
- +-------+----------+------------+---------+
- | 18 | Johnson | 1969-11-08 | NULL | TaskAssignments--------+
- | 253 | Smith | 1979-01-18 | 18 | | id | task | employee |
- | 255 | Jones | 1981-03-24 | 253 | +----+------+----------+
- | 19 | Xu | 1966-11-08 | NULL | | 1 | 1 | 18 |
- | 254 | Ishita | 1971-10-31 | 253 | | 2 | 2 | 253 |
- +-------+----------+------------+---------+ | 3 | 3 | 19 |
- | 4 | 4 | 253 |
- Tasks----+--------+------+ | 5 | 1 | 253 |
- | taskid | name | lead | | 6 | 2 | 255 |
- +--------+--------+------+ | 7 | 3 | 255 |
- | 1 | widget | 18 | | 8 | 4 | 254 |
- | 2 | dingus | 253 | +----+------+----------+
- | 3 | thingy | 18 |
- | 4 | toy | 253 |
- +--------+--------+------+
-
- INSERT INTO Employee (empid, lastName, birthday, manager) VALUES ( 18, "Johnson" , 1969-11-08, NULL);
- INSERT INTO Employee (empid, lastName, birthday, manager) VALUES (253, "Smith" , 1979-01-18, 18);
- INSERT INTO Employee (empid, lastName, birthday, manager) VALUES (255, "Jones" , 1981-03-24, 253);
- INSERT INTO Employee (empid, lastName, birthday, manager) VALUES ( 19, "Xu" , 1966-11-08, NULL);
- INSERT INTO Employee (empid, lastName, birthday, manager) VALUES (254, "Ishita" , 1971-10-31, 253);
-
- INSERT INTO Tasks (taskid, name, lead) VALUES (1, "widget", 18);
- INSERT INTO Tasks (taskid, name, lead) VALUES (2, "dingus", 253);
- INSERT INTO Tasks (taskid, name, lead) VALUES (3, "thingy", 18);
- INSERT INTO Tasks (taskid, name, lead) VALUES (4, "toy" , 253);
-
- INSERT INTO TaskAssignments (id, task, employee) VALUES (1, 1, 18);
- INSERT INTO TaskAssignments (id, task, employee) VALUES (2, 2, 253);
- INSERT INTO TaskAssignments (id, task, employee) VALUES (3, 3, 19);
- INSERT INTO TaskAssignments (id, task, employee) VALUES (4, 4, 253);
- INSERT INTO TaskAssignments (id, task, employee) VALUES (5, 1, 253);
- INSERT INTO TaskAssignments (id, task, employee) VALUES (6, 2, 255);
- INSERT INTO TaskAssignments (id, task, employee) VALUES (7, 3, 255);
- INSERT INTO TaskAssignments (id, task, employee) VALUES (8, 4, 254);
- */
-
- val DDLParser = SqlParser()
- val dbDdl = """
-CREATE TABLE Employee (empid INT, PRIMARY KEY (empid), 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 db:Database = DDLParser.parseAll(DDLParser.ddl, dbDdl).get
-
-/* The reference RDF representation (queriable with the SPARQL in the tests) is:
- */
-
- val dbAsTurtle = """
-PREFIX empP : <http://hr.example/DB/Employee#>
-PREFIX task : <http://hr.example/DB/Tasks#>
-PREFIX tass : <http://hr.example/DB/TaskAssignments#>
-PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
-
-<http://hr.example/DB/Employee/empid.18#record>
- empP:lastName "Johnson"^^xsd:string ; empP:birthday "1969-11-08"^^xsd:date .
-<http://hr.example/DB/Employee/empid.253#record>
- empP:lastName "Smith"^^xsd:string ; empP:birthday "1969-11-08"^^xsd:date ;
- empP:manager <http://hr.example/DB/Employee/empid.18#record> .
-<http://hr.example/DB/Employee/empid.255#record>
- empP:lastName "Jones"^^xsd:string ; empP:birthday "1981-03-24"^^xsd:date ;
- empP:manager <http://hr.example/DB/Employee/empid.253#record> .
-<http://hr.example/DB/Employee/empid.19#record>
- empP:lastName "Xu"^^xsd:string ; empP:birthday "1966-11-08"^^xsd:date .
-<http://hr.example/DB/Employee/empid.254#record>
- empP:lastName "Ishita"^^xsd:string ; empP:birthday "1971-10-31"^^xsd:date ;
- empP:manager <http://hr.example/DB/Employee/empid.253#record> .
-
-<http://hr.example/DB/Tasks/taskid.1#record>
- task:lastName "widget"^^xsd:string ;
- task:manager <http://hr.example/DB/Employee/empid.18#record> .
-<http://hr.example/DB/Tasks/taskid.2#record>
- task:lastName "dingus"^^xsd:string ;
- task:manager <http://hr.example/DB/Employee/empid.253#record> .
-<http://hr.example/DB/Tasks/taskid.3#record>
- task:lastName "thingy"^^xsd:string ;
- task:manager <http://hr.example/DB/Employee/empid.18#record> .
-<http://hr.example/DB/Tasks/taskid.4#record>
- task:lastName "toy"^^xsd:string ;
- task:manager <http://hr.example/DB/Employee/empid.253#record> .
-
-<http://hr.example/DB/TaskAssignment/id.1#record>
- tass:task <http://hr.example/DB/Tasks/taskid.1#record>
- tass:employee <http://hr.example/DB/Employee/empid.18#record> .
-<http://hr.example/DB/TaskAssignment/id.2#record>
- tass:task <http://hr.example/DB/Tasks/taskid.2#record>
- tass:employee <http://hr.example/DB/Employee/empid.253#record> .
-<http://hr.example/DB/TaskAssignment/id.3#record>
- tass:task <http://hr.example/DB/Tasks/taskid.3#record>
- tass:employee <http://hr.example/DB/Employee/empid.19#record> .
-<http://hr.example/DB/TaskAssignment/id.4#record>
- tass:task <http://hr.example/DB/Tasks/taskid.4#record>
- tass:employee <http://hr.example/DB/Employee/empid.253#record> .
-<http://hr.example/DB/TaskAssignment/id.5#record>
- tass:task <http://hr.example/DB/Tasks/taskid.1#record>
- tass:employee <http://hr.example/DB/Employee/empid.253#record> .
-<http://hr.example/DB/TaskAssignment/id.6#record>
- tass:task <http://hr.example/DB/Tasks/taskid.2#record>
- tass:employee <http://hr.example/DB/Employee/empid.255#record> .
-<http://hr.example/DB/TaskAssignment/id.7#record>
- tass:task <http://hr.example/DB/Tasks/taskid.3#record>
- tass:employee <http://hr.example/DB/Employee/empid.255#record> .
-<http://hr.example/DB/TaskAssignment/id.8#record>
- tass:task <http://hr.example/DB/Tasks/taskid.4#record>
- tass:employee <http://hr.example/DB/Employee/empid.254#record> .
-"""
- /* The obvious test is that the results from the SPARQL query and the
- * relational query match.
- *
- * Data can be converted to turtle strings, or left as native formats for
- * mapping the the querier. The first examples constrast queries relying
- * on a post-query transformation against those returing turtle atoms.
- */
-
- test("decompose a predicate uri in stem, rel and attr") {
- val uri = sparql.Uri("http://hr.example/our/favorite/DB/Employee#lastName")
- val puri:SparqlToSql.PUri = SparqlToSql.parsePredicateURI(uri)
- assert(puri === SparqlToSql.PUri(SparqlToSql.Stem("http://hr.example/our/favorite/DB"),
- SparqlToSql.Rel("Employee"),
- SparqlToSql.Attr("lastName")))
- }
-
- test("decompose a object uri in stem, rel and attr") {
- val uri = sparql.Uri("http://hr.example/our/favorite/DB/Employee/id.18#record")
- val objuri:SparqlToSql.NodeUri = SparqlToSql.parseObjectURI(uri)
- assert(objuri === SparqlToSql.NodeUri(SparqlToSql.Stem("http://hr.example/our/favorite/DB"),
- SparqlToSql.Rel("Employee"),
- SparqlToSql.Attr("id"),
- SparqlToSql.CellValue("18")))
- }
-
- /* Disable turtle string-izing (SparqlToSql parm 5) and return native format: */
- test("?s <p> <x>") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX empP : <http://hr.example/DB/Employee#>
-SELECT ?emp {
-?emp empP:manager <http://hr.example/DB/Employee/empid.18#record>
-}
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT R_emp.empid AS emp
- FROM Employee AS R_emp
- INNER JOIN Employee AS R_empid18
- WHERE R_empid18.empid=R_emp.manager AND R_empid18.empid=18 AND R_emp.empid IS NOT NULL
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, false)._1
- assert(generated === parsed)
- val output = """
-+-----+
-| emp |
-+-----+
-| 253 |
-+-----+
-"""
- }
-
- /* Re-interpret native format: */
- test("RDF(?s)") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX empP : <http://hr.example/DB/Employee#>
-SELECT ?emp {
-?emp empP:manager <http://hr.example/DB/Employee/empid.18#record>
-}
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT R_emp.empid AS emp
- FROM Employee AS R_emp
- INNER JOIN Employee AS R_empid18
- WHERE R_empid18.empid=R_emp.manager AND R_empid18.empid=18 AND R_emp.empid IS NOT NULL
-""").get
- val xmlres = """<?xml version="1.0"?>
-<sparql xmlns="http://www.w3.org/2005/sparql-results#">
- <head>
- <variable name="emp"/>
- </head>
-
- <results>
- <result>
- <binding name="emp">
- <uri>http://hr.example/DB/Employee/empid.253#record</uri>
- </binding>
- </result>
- </results>
-</sparql>
-""" //"//
- val stem = StemURI("http://hr.example/DB/")
- val (generated, rdfmap) = SparqlToSql(db, sparqlSelect, stem, true, false)
- val head = SqlToXMLRes.head(List[String]("emp"))
- val body = SqlToXMLRes.startresult + SqlToXMLRes.binding("emp", "253", rdfmap, stem) + SqlToXMLRes.endresult
- val foot = SqlToXMLRes.foot
- assert(generated === parsed)
- assert(head + body + foot === xmlres)
- val output = """
-+-----+
-| emp |
-+-----+
-| 253 |
-+-----+
-"""
- }
-
- /* Enable turtle string-izing and test URI generation: */
- test("SELECT <x> { ?sf <p> <x>} (in-SQL Nodizer)") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX empP : <http://hr.example/DB/Employee#>
-SELECT ?emp {
-?emp empP:manager <http://hr.example/DB/Employee/empid.18#record>
-}
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT CONCAT("http://hr.example/DB/", "Employee", "/", "empid", ".", R_emp.empid, "#record") AS emp
- FROM Employee AS R_emp
- INNER JOIN Employee AS R_empid18
- WHERE R_empid18.empid=R_emp.manager AND R_empid18.empid=18 AND R_emp.empid IS NOT NULL
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, true)._1
- assert(generated === parsed)
- val output = """
-+------------------------------------------------+
-| emp |
-+------------------------------------------------+
-| http://hr.example/DB/Employee/empid.253#record |
-+------------------------------------------------+
-"""
- }
-
- /* Enable turtle string-izing and test RDFLiteral generation: */
- test("SELECT <x> { ?sf <p> \"asdf\"} (in-SQL Nodizer)") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX empP : <http://hr.example/DB/Employee#>
-SELECT ?name {
-?emp empP:lastName ?name
-}
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT CONCAT("'", R_emp.lastName, "'^^<http://www.w3.org/2001/XMLSchema#string>") AS name
- FROM Employee AS R_emp
- WHERE R_emp.empid IS NOT NULL AND R_emp.lastName IS NOT NULL
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, true)._1
- assert(generated === parsed)
- val output = """
-+----------------------------------------------------+
-| name |
-+----------------------------------------------------+
-| Johnson^^<http://www.w3.org/2001/XMLSchema#string> |
-| Smith^^<http://www.w3.org/2001/XMLSchema#string> |
-| Jones^^<http://www.w3.org/2001/XMLSchema#string> |
-| Xu^^<http://www.w3.org/2001/XMLSchema#string> |
-| Ishita^^<http://www.w3.org/2001/XMLSchema#string> |
-+----------------------------------------------------+
-"""
- }
-
- test("<s> <p> ?x") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX empP : <http://hr.example/DB/Employee#>
-SELECT ?manager {
-<http://hr.example/DB/Employee/empid.253#record> empP:manager ?manager
-}
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT R_manager.empid AS manager
- FROM Employee AS R_empid253
- INNER JOIN Employee AS R_manager
- WHERE R_manager.empid=R_empid253.manager AND R_empid253.empid=253
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, false)._1
- assert(generated === parsed)
- val output = """
-+---------+
-| manager |
-+---------+
-| 18 |
-+---------+
-"""
- }
-
- test("?s <p> 18") {
- /* Literal foreign keys should probably throw an error,
- * instead does what user meant. */
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX empP : <http://hr.example/DB/Employee#>
-PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
-SELECT ?emp {
-?emp empP:manager "18"^^xsd:integer
-}
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT R_emp.empid AS emp
- FROM Employee AS R_emp
- INNER JOIN Employee AS R_18
- WHERE R_18.empid=R_emp.manager AND R_18.empid=18 AND R_emp.empid IS NOT NULL
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, false)._1
- assert(generated === parsed)
- val output = """
-+-----+
-| emp |
-+-----+
-| 253 |
-+-----+
-"""
- }
-
- test("?s1 <p> ?x . ?s2 <p> ?x") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX tass : <http://hr.example/DB/TaskAssignments#>
-SELECT ?task1 ?task2 {
- ?task1 tass:employee ?who .
- ?task2 tass:employee ?who
- FILTER(?task1 < ?task2)
-}
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT R_task1.id AS task1, R_task2.id AS task2
- FROM TaskAssignments AS R_task1
- INNER JOIN Employee AS R_who ON R_who.empid=R_task1.employee
- INNER JOIN TaskAssignments AS R_task2 ON R_who.empid=R_task2.employee
- WHERE R_task1.id<R_task2.id
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, false)._1
- assert(generated === parsed)
- val output = """
-+-------+-------+
-| task1 | task2 |
-+-------+-------+
-| 2 | 4 |
-| 2 | 5 |
-| 4 | 5 |
-| 6 | 7 |
-+-------+-------+
-"""
- }
-
- test("transform SQLbgp") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX empP : <http://hr.example/DB/Employee#>
-SELECT ?empName ?manageName {
-?emp empP:lastName ?empName .
-?emp empP:manager ?manager .
-?manager empP:lastName ?manageName
-}
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT R_emp.lastName AS empName, R_manager.lastName AS manageName
- FROM Employee AS R_emp
- INNER JOIN Employee AS R_manager ON R_manager.empid=R_emp.manager
- WHERE R_emp .lastName IS NOT NULL AND R_manager.lastName IS NOT NULL
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, false)._1
- assert(generated === parsed)
- val output = """
-+---------+------------+
-| empName | manageName |
-+---------+------------+
-| Smith | Johnson |
-| Jones | Smith |
-| Ishita | Smith |
-+---------+------------+
-"""
- }
-
- test("transform tup1 no-enforce") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX empP : <http://hr.example/DB/Employee#>
-SELECT ?empName {
- ?emp empP:lastName ?empName .
- ?emp empP:manager <http://hr.example/DB/Employee/empid.18#record>
- }
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT R_emp.lastName AS empName
- FROM Employee AS R_emp
- WHERE R_emp.manager=18 AND R_emp.lastName IS NOT NULL
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
- assert(generated === parsed)
- val output = """
-+---------+
-| empName |
-+---------+
-| Smith |
-+---------+
-"""
- }
-
- test("transform tup1 enforce") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX empP : <http://hr.example/DB/Employee#>
-SELECT ?empName {
- ?emp empP:lastName ?empName .
- ?emp empP:manager <http://hr.example/DB/Employee/empid.18#record>
- }
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT R_emp.lastName AS empName
- FROM Employee AS R_emp
- INNER JOIN Employee AS R_empid18
- WHERE R_empid18.empid=R_emp.manager AND R_empid18.empid=18 AND R_emp.lastName IS NOT NULL
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, false)._1
- assert(generated === parsed)
- val output = """
-+---------+
-| empName |
-+---------+
-| Smith |
-+---------+
-"""
- }
-
-
- test("transform litConst1") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX empP : <http://hr.example/DB/Employee#>
-PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
-SELECT ?empName {
- ?emp empP:lastName ?empName .
- ?emp empP:manager ?manager .
- ?manager empP:lastName "Johnson"^^xsd:string
- }
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT R_emp.lastName AS empName
- FROM Employee AS R_emp
- INNER JOIN Employee AS R_manager
-WHERE R_manager.empid=R_emp.manager AND R_manager.lastName="Johnson" AND R_emp.lastName IS NOT NULL
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, false)._1
- assert(generated === parsed)
- val output = """
-+---------+
-| empName |
-+---------+
-| Smith |
-+---------+
-"""
- }
-
- test("transform filter1") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX empP : <http://hr.example/DB/Employee#>
-PREFIX task : <http://hr.example/DB/Tasks#>
-PREFIX tass : <http://hr.example/DB/TaskAssignments#>
-SELECT ?empName ?grandManagName {
- ?emp empP:lastName ?empName .
- ?emp empP:birthday ?empBday .
- ?lower tass:employee ?emp .
- ?lower tass:task ?ltask .
- ?ltask task:lead ?taskLead .
- ?taskLead empP:birthday ?manBday .
- ?upper tass:employee ?taskLead .
- ?upper tass:task ?utask .
- ?utask task:lead ?grandManager .
- ?grandManager empP:birthday ?grandManBday .
- ?grandManager empP:lastName ?grandManagName
- FILTER (?manBday < ?empBday && ?grandManBday < ?manBday)
-}
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT R_emp.lastName AS empName,
- R_grandManager.lastName AS grandManagName
- FROM Employee AS R_emp
- INNER JOIN TaskAssignments AS R_lower ON R_emp.empid=R_lower.employee
- INNER JOIN Tasks AS R_ltask ON R_ltask.taskid=R_lower.task
- INNER JOIN Employee AS R_taskLead ON R_taskLead.empid=R_ltask.lead
- INNER JOIN TaskAssignments AS R_upper ON R_taskLead.empid=R_upper.employee
- INNER JOIN Tasks AS R_utask ON R_utask.taskid=R_upper.task
- INNER JOIN Employee AS R_grandManager ON R_grandManager.empid=R_utask.lead
- WHERE R_taskLead.birthday<R_emp.birthday AND R_grandManager.birthday<R_taskLead.birthday
- AND R_emp.lastName IS NOT NULL AND R_grandManager.lastName IS NOT NULL
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), true, false)._1
- assert(generated === parsed)
- val output = """
-+---------+----------------+
-| empName | grandManagName |
-+---------+----------------+
-| Jones | Johnson |
-+---------+----------------+
-"""
- }
-
- /* Employess above and below Smith */
- test("transform disj1") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX empP : <http://hr.example/DB/Employee#>
-PREFIX task : <http://hr.example/DB/Tasks#>
-PREFIX tass : <http://hr.example/DB/TaskAssignments#>
-SELECT ?name
- WHERE { ?who empP:lastName "Smith"^^xsd:string
- { ?above tass:employee ?who .
- ?above tass:task ?atask .
- ?atask task:lead ?taskLead .
- ?taskLead empP:lastName ?name }
- UNION
- { ?below tass:task ?btask .
- ?btask task:lead ?who .
- ?below tass:employee ?managed .
- ?managed empP:lastName ?name } }
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT G_union1.name AS name
- FROM Employee AS R_who
- INNER JOIN (
- SELECT 0 AS _DISJOINT_, R_above.id AS above, R_above.task AS atask,
- R_above.employee AS who, R_taskLead.lastName AS name,
- R_atask.lead AS taskLead,
- NULL AS below, NULL AS btask, NULL AS managed
- FROM TaskAssignments AS R_above
- INNER JOIN Tasks AS R_atask ON R_atask.taskid=R_above.task
- INNER JOIN Employee AS R_taskLead ON R_taskLead.empid=R_atask.lead
- WHERE R_above.employee IS NOT NULL
- AND R_taskLead.lastName IS NOT NULL
- UNION
- SELECT 1 AS _DISJOINT_, NULL AS above, NULL AS atask,
- R_btask.lead AS who,R_managed.lastName AS name,
- NULL AS taskLead,
- R_below.id AS below, R_below.task AS btask,
- R_below.employee AS managed
- FROM TaskAssignments AS R_below
- INNER JOIN Tasks AS R_btask ON R_btask.taskid=R_below.task
- INNER JOIN Employee AS R_managed ON R_managed.empid=R_below.employee
- WHERE R_managed.lastName IS NOT NULL
- AND R_btask.lead IS NOT NULL
- ) AS G_union1
- WHERE R_who.lastName="Smith"
- AND R_who.empid IS NOT NULL
- AND (G_union1._DISJOINT_!=0 OR G_union1.who=R_who.empid)
- AND (G_union1._DISJOINT_!=1 OR G_union1.who=R_who.empid)
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
- assert(generated === parsed)
- val output = """
-+---------+
-| name |
-+---------+
-| Johnson |
-| Smith |
-| Smith |
-| Smith |
-| Jones |
-| Smith |
-| Ishita |
-+---------+
-"""
- }
-
- test("transform assymDisj1") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX empP : <http://hr.example/DB/Employee#>
-PREFIX task : <http://hr.example/DB/Tasks#>
-PREFIX tass : <http://hr.example/DB/TaskAssignments#>
-PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
-SELECT ?name ?bday
- WHERE { { ?above tass:employee ?who .
- ?above tass:task ?atask .
- ?atask task:lead ?taskLead .
- ?taskLead empP:lastName ?name }
- UNION
- { ?below tass:task ?btask .
- ?btask task:lead ?who .
- ?below tass:employee ?managed .
- ?managed empP:lastName ?name .
- ?managed empP:birthday ?bday }
- ?who empP:lastName "Smith"^^xsd:string .
- ?who empP:birthday ?bday }
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT G_union0.name AS name, G_union0.bday AS bday
- FROM (
- SELECT R_above.id AS above, R_above.task AS atask, NULL AS bday, NULL AS below,
- NULL AS btask, NULL AS managed, R_taskLead.lastName AS name,
- R_atask.lead AS taskLead, R_above.employee AS who, 0 AS _DISJOINT_
- FROM TaskAssignments AS R_above
- INNER JOIN Tasks AS R_atask ON R_atask.taskid=R_above.task
- INNER JOIN Employee AS R_taskLead ON R_taskLead.empid=R_atask.lead
- WHERE R_above.employee IS NOT NULL AND R_taskLead.lastName IS NOT NULL
- UNION
- SELECT NULL AS above, NULL AS atask, R_managed.birthday AS bday, R_below.id AS below,
- R_below.task AS btask, R_below.employee AS managed, R_managed.lastName AS name,
- NULL AS taskLead, R_btask.lead AS who, 1 AS _DISJOINT_
- FROM TaskAssignments AS R_below
- INNER JOIN Tasks AS R_btask ON R_btask.taskid=R_below.task
- INNER JOIN Employee AS R_managed ON R_managed.empid=R_below.employee
- WHERE R_btask.lead IS NOT NULL AND R_managed.birthday IS NOT NULL AND R_managed.lastName IS NOT NULL
- ) AS G_union0
- INNER JOIN Employee AS R_who
- WHERE (G_union0._DISJOINT_!=0 OR R_who.empid=G_union0.who)
- AND (G_union0._DISJOINT_!=1 OR R_who.birthday=G_union0.bday)
- AND (G_union0._DISJOINT_!=1 OR R_who.empid=G_union0.who)
- AND R_who.lastName="Smith"
-""").get // !!! AND (G_union0.bday IS NOT NULL) AND (G_union0.who IS NOT NULL)
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
- assert(generated === parsed)
- val output = """
-"""
- }
-
- test("transform assymDisj1 reversed") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX empP : <http://hr.example/DB/Employee#>
-PREFIX task : <http://hr.example/DB/Tasks#>
-PREFIX tass : <http://hr.example/DB/TaskAssignments#>
-PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
-SELECT ?name
- WHERE {
- ?who empP:lastName "Smith"^^xsd:string .
- ?who empP:birthday ?bday
- { ?above tass:employee ?who .
- ?above tass:task ?atask .
- ?atask task:lead ?taskLead .
- ?taskLead empP:lastName ?name }
- UNION
- { ?below tass:task ?btask .
- ?btask task:lead ?who .
- ?below tass:employee ?managed .
- ?managed empP:lastName ?name .
- ?managed empP:birthday ?bday }
- }
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT G_union1.name AS name
- FROM Employee AS R_who
- INNER JOIN (
- SELECT R_above.id AS above, R_above.task AS atask, NULL AS bday, NULL AS below,
- NULL AS btask, NULL AS managed, R_taskLead.lastName AS name,
- R_atask.lead AS taskLead, R_above.employee AS who, 0 AS _DISJOINT_
- FROM TaskAssignments AS R_above
- INNER JOIN Tasks AS R_atask ON R_atask.taskid=R_above.task
- INNER JOIN Employee AS R_taskLead ON R_taskLead.empid=R_atask.lead
- WHERE R_above.employee IS NOT NULL AND R_taskLead.lastName IS NOT NULL
- UNION
- SELECT NULL AS above, NULL AS atask, R_managed.birthday AS bday, R_below.id AS below,
- R_below.task AS btask, R_below.employee AS managed, R_managed.lastName AS name,
- NULL AS taskLead, R_btask.lead AS who, 1 AS _DISJOINT_
- FROM TaskAssignments AS R_below
- INNER JOIN Tasks AS R_btask ON R_btask.taskid=R_below.task
- INNER JOIN Employee AS R_managed ON R_managed.empid=R_below.employee
- WHERE R_btask.lead IS NOT NULL AND R_managed.birthday IS NOT NULL AND R_managed.lastName IS NOT NULL
- ) AS G_union1
- WHERE (G_union1._DISJOINT_!=0 OR G_union1.who=R_who.empid)
- AND (G_union1._DISJOINT_!=1 OR G_union1.bday=R_who.birthday)
- AND (G_union1._DISJOINT_!=1 OR G_union1.who=R_who.empid)
- AND R_who.birthday IS NOT NULL AND R_who.lastName="Smith"
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
- assert(generated === parsed)
- val output = """
-"""
- }
-
- test("transform assymDisj1 interspersed") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX empP : <http://hr.example/DB/Employee#>
-PREFIX task : <http://hr.example/DB/Tasks#>
-PREFIX tass : <http://hr.example/DB/TaskAssignments#>
-PREFIX xsd : <http://www.w3.org/2001/XMLSchema#>
-SELECT ?name
- WHERE {
- ?who empP:lastName "Smith"^^xsd:string
- { ?above tass:employee ?who .
- ?above tass:task ?atask .
- ?atask task:lead ?taskLead .
- ?taskLead empP:lastName ?name }
- UNION
- { ?below tass:task ?btask .
- ?btask task:lead ?who .
- ?below tass:employee ?managed .
- ?managed empP:lastName ?name .
- ?managed empP:birthday ?bday }
- ?who empP:birthday ?bday
- }
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT G_union1.name AS name
- FROM Employee AS R_who
- INNER JOIN (
- SELECT R_above.id AS above, R_above.task AS atask, NULL AS bday, NULL AS below,
- NULL AS btask, NULL AS managed, R_taskLead.lastName AS name,
- R_atask.lead AS taskLead, R_above.employee AS who, 0 AS _DISJOINT_
- FROM TaskAssignments AS R_above
- INNER JOIN Tasks AS R_atask ON R_atask.taskid=R_above.task
- INNER JOIN Employee AS R_taskLead ON R_taskLead.empid=R_atask.lead
- WHERE R_above.employee IS NOT NULL AND R_taskLead.lastName IS NOT NULL
- UNION
- SELECT NULL AS above, NULL AS atask, R_managed.birthday AS bday, R_below.id AS below,
- R_below.task AS btask, R_below.employee AS managed, R_managed.lastName AS name,
- NULL AS taskLead, R_btask.lead AS who, 1 AS _DISJOINT_
- FROM TaskAssignments AS R_below
- INNER JOIN Tasks AS R_btask ON R_btask.taskid=R_below.task
- INNER JOIN Employee AS R_managed ON R_managed.empid=R_below.employee
- WHERE R_btask.lead IS NOT NULL AND R_managed.birthday IS NOT NULL AND R_managed.lastName IS NOT NULL
- ) AS G_union1
- WHERE (G_union1._DISJOINT_!=0 OR G_union1.who=R_who.empid)
- AND (G_union1._DISJOINT_!=1 OR G_union1.who=R_who.empid)
- AND (G_union1._DISJOINT_!=1 OR R_who.birthday=G_union1.bday)
- AND R_who.lastName="Smith"
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
- assert(generated === parsed)
- val output = """
-"""
- }
-
- test("transform optJoin1") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX emplP: <http://hr.example/DB/Employee#>
-
-SELECT ?empName ?managName ?grandManagName
- WHERE { ?emp emplP:lastName ?empName
- OPTIONAL { ?emp emplP:manager ?manager .
- ?manager emplP:lastName ?managName .
- ?manager emplP:manager ?grandManager .
- ?grandManager emplP:lastName ?grandManagName } }
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT R_emp.lastName AS empName, G_opt1.grandManagName AS grandManagName, G_opt1.managName AS managName
- FROM Employee AS R_emp
- LEFT OUTER JOIN (
- SELECT R_emp.empid AS emp, R_grandManager.lastName AS grandManagName, R_manager.manager AS grandManager, R_manager.lastName AS managName, R_emp.manager AS manager, 1 AS _DISJOINT_
- FROM Employee AS R_emp
- INNER JOIN Employee AS R_manager ON R_manager.empid=R_emp.manager
- INNER JOIN Employee AS R_grandManager ON R_grandManager.empid=R_manager.manager
- WHERE R_emp.empid IS NOT NULL AND R_grandManager.lastName IS NOT NULL AND R_manager.lastName IS NOT NULL
- ) AS G_opt1 ON G_opt1.emp=R_emp.empid
- WHERE R_emp.empid IS NOT NULL AND R_emp.lastName IS NOT NULL
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
- assert(generated === parsed)
- val output = """
-"""
- }
-
- test("transform leadOpt1") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX emplP: <http://hr.example/DB/Employee#>
-
-SELECT ?empName ?grandManagName
- WHERE { OPTIONAL { ?taskLead emplP:manager ?emp .
- ?taskLead emplP:manager ?grandManager .
- ?grandManager emplP:lastName ?grandManagName }
- ?emp emplP:lastName ?empName }
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT R_emp.lastName AS empName, G_opt1.grandManagName AS grandManagName
- FROM ( SELECT 1 AS _EMPTY_ ) AS _EMPTY_
- LEFT OUTER JOIN (
- SELECT R_taskLead.manager AS emp, R_grandManager.lastName AS grandManagName, R_taskLead.manager AS grandManager, R_taskLead.empid AS taskLead, 1 AS _DISJOINT_
- FROM Employee AS R_taskLead
- INNER JOIN Employee AS R_grandManager ON R_grandManager.empid=R_taskLead.manager
- WHERE R_grandManager.lastName IS NOT NULL
- ) AS G_opt1 ON 1=1
- INNER JOIN Employee AS R_emp
- WHERE (G_opt1._DISJOINT_ IS NULL OR R_emp.empid=G_opt1.emp)
- AND R_emp.lastName IS NOT NULL
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
- assert(generated === parsed)
- val output = """
-"""
- }
-
- test("transform nestOpt") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX emplP: <http://hr.example/DB/Employee#>
-
-SELECT ?empName ?managName ?grandManagName
- WHERE { ?emp emplP:lastName ?empName
- OPTIONAL { ?emp emplP:manager ?manager .
- ?manager emplP:lastName ?managName
- OPTIONAL { ?manager emplP:manager ?grandManager .
- ?grandManager emplP:lastName ?grandManagName } }
- }
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT R_emp.lastName AS empName, G_opt1.grandManagName AS grandManagName, G_opt1.managName AS managName
- FROM Employee AS R_emp
- LEFT OUTER JOIN (
- SELECT R_emp.empid AS emp, G_opt2.grandManagName AS grandManagName, G_opt2.grandManager AS grandManager, R_manager.lastName AS managName, R_emp.manager AS manager, 1 AS _DISJOINT_
- FROM Employee AS R_emp
- INNER JOIN Employee AS R_manager ON R_manager.empid=R_emp.manager
- LEFT OUTER JOIN (
- SELECT R_grandManager.lastName AS grandManagName, R_manager.manager AS grandManager, R_manager.empid AS manager, 2 AS _DISJOINT_
- FROM Employee AS R_manager
- INNER JOIN Employee AS R_grandManager ON R_grandManager.empid=R_manager.manager
- WHERE R_grandManager.lastName IS NOT NULL AND R_manager.empid IS NOT NULL
- ) AS G_opt2 ON G_opt2.manager=R_emp.manager
- WHERE R_emp.empid IS NOT NULL
- AND R_manager.lastName IS NOT NULL
- ) AS G_opt1 ON G_opt1.emp=R_emp.empid
- WHERE R_emp.empid IS NOT NULL AND R_emp.lastName IS NOT NULL
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
- assert(generated === parsed)
- val output = """
-"""
- }
-
- test("transform equivOpt1") {
- val sparqlParser = Sparql()
- val sparqlSelect = sparqlParser.parseAll(sparqlParser.select, """
-PREFIX emplP: <http://hr.example/DB/Employee#>
-
-SELECT ?emp1Name ?emp2Name ?emp3Name
- WHERE { ?emp1 emplP:lastName ?emp1Name
- OPTIONAL { ?emp1 emplP:birthday ?birthday }
- ?emp2 emplP:lastName ?emp2Name
- OPTIONAL { ?emp2 emplP:birthday ?birthday }
- ?emp3 emplP:lastName ?emp3Name .
- ?emp3 emplP:birthday ?birthday .
- ?emp4 emplP:lastName ?emp4Name .
- ?emp4 emplP:birthday ?birthday
- FILTER ( ?emp1Name < ?emp2Name && ?emp2Name < ?emp3Name && ?emp3Name < ?emp4Name) }
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT R_emp1.lastName AS emp1Name, R_emp2.lastName AS emp2Name, R_emp3.lastName AS emp3Name
- FROM Employee AS R_emp1
- LEFT OUTER JOIN (
- SELECT R_emp1.birthday AS birthday, R_emp1.empid AS emp1, 1 AS _DISJOINT_
- FROM Employee AS R_emp1
- WHERE R_emp1.birthday IS NOT NULL AND R_emp1.empid IS NOT NULL
- ) AS G_opt1 ON G_opt1.emp1=R_emp1.empid
- INNER JOIN Employee AS R_emp2
- LEFT OUTER JOIN (
- SELECT R_emp2.birthday AS birthday, R_emp2.empid AS emp2, 3 AS _DISJOINT_
- FROM Employee AS R_emp2
- WHERE R_emp2.birthday IS NOT NULL AND R_emp2.empid IS NOT NULL
- ) AS G_opt3 ON (G_opt1._DISJOINT_ IS NULL OR G_opt3.birthday=G_opt1.birthday) AND G_opt3.emp2=R_emp2.empid
- INNER JOIN Employee AS R_emp3
- INNER JOIN Employee AS R_emp4
- WHERE (G_opt1._DISJOINT_ IS NULL OR R_emp3.birthday=G_opt1.birthday)
- AND (G_opt1._DISJOINT_ IS NULL OR R_emp4.birthday=G_opt1.birthday)
- AND R_emp1.lastName<R_emp2.lastName
- AND R_emp2.empid IS NOT NULL AND R_emp2.lastName<R_emp3.lastName
- AND R_emp3.lastName<R_emp4.lastName AND R_emp1.empid IS NOT NULL
-""").get
- val generated = SparqlToSql(db, sparqlSelect, StemURI("http://hr.example/DB/"), false, false)._1
- assert(generated === parsed)
- val output = """
-"""
- }
-
- 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));
-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
-
- test("swobjects/tests/healthCare/lists-notBound/db.rq AS OPTIONAL") {
- val sparqlParser = Sparql()
- val sparqlSelect = 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
- WHERE {
- ?patient Person:MiddleName ?middleName .
- ?patient Person:DateOfBirth ?dob .
- ?patient Person:SexDE ?sexEntry .
- ?sexEntry Sex_DE:EntryName ?sex .
-
- ?indicItem Item_Medication:PatientID ?patient .
- ?indicItem Item_Medication:PerformedDTTM ?indicDate .
- ?indicItem Item_Medication:EntryName ?takes .
- ?indicMed Medication:ItemID ?indicItem .
- ?indicMed Medication:MedDictDE ?indicDE .
- ?indicDE Medication_DE:NDC ?indicNDC .
- ?indicCode NDCcodes:NDC ?indicNDC .
- ?indicCode NDCcodes:ingredient "6809"^^xsd:integer
-
- OPTIONAL {
- ?disqualItem Item_Medication:PatientID ?patient .
- ?disqualItem Item_Medication:PerformedDTTM ?disqualDate .
- ?disqualItem Item_Medication:EntryName ?takes .
- ?disqualMed Medication:ItemID ?disqualItem .
- ?disqualMed Medication:MedDictDE ?disqualDE .
- ?disqualDE Medication_DE:NDC ?disqualNDC .
- ?disqualCode NDCcodes:NDC ?disqualNDC .
- ?disqualCode NDCcodes:ingredient "11289"^^xsd:integer
- }
- }
-""").get // FILTER (!BOUND(?disqualItem))
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT R_patient.ID AS patient
- FROM Person AS R_patient
- INNER JOIN Sex_DE AS R_sexEntry ON R_sexEntry.ID=R_patient.SexDE
- INNER JOIN Item_Medication AS R_indicItem ON R_indicItem.PatientID=R_patient.ID
- INNER JOIN Medication AS R_indicMed ON R_indicMed.ItemID=R_indicItem.ID
- INNER JOIN Medication_DE AS R_indicDE ON R_indicDE.ID=R_indicMed.MedDictDE
- INNER JOIN NDCcodes AS R_indicCode ON R_indicCode.NDC=R_indicDE.NDC
- LEFT OUTER JOIN (
- SELECT R_disqualCode.ID AS disqualCode, R_disqualMed.MedDictDE AS disqualDE,
- R_disqualItem.PerformedDTTM AS disqualDate, R_disqualItem.ID AS disqualItem,
- R_disqualMed.ID AS disqualMed, R_disqualDE.NDC AS disqualNDC,
- R_disqualItem.PatientID AS patient, R_disqualItem.EntryName AS takes, 6 AS _DISJOINT_
- FROM Item_Medication AS R_disqualItem
- INNER JOIN Medication AS R_disqualMed ON R_disqualMed.ItemID=R_disqualItem.ID
- INNER JOIN Medication_DE AS R_disqualDE ON R_disqualDE.ID=R_disqualMed.MedDictDE
- INNER JOIN NDCcodes AS R_disqualCode ON R_disqualCode.NDC=R_disqualDE.NDC
- WHERE R_disqualCode.ingredient=11289
- AND R_disqualItem.EntryName IS NOT NULL
- AND R_disqualItem.PatientID IS NOT NULL
- AND R_disqualItem.PerformedDTTM IS NOT NULL
- ) AS G_opt6 ON G_opt6.patient=R_patient.ID
- AND G_opt6.takes=R_indicItem.EntryName
- WHERE R_indicCode.ingredient=6809
- AND R_indicItem.EntryName IS NOT NULL
- AND R_indicItem.PerformedDTTM IS NOT NULL
- AND R_patient.DateOfBirth IS NOT NULL
- AND R_patient.MiddleName IS NOT NULL
- AND R_sexEntry.EntryName IS NOT NULL
-""").get // AND G_opt6.patient IS NULL
- val generated = SparqlToSql(hosp1, sparqlSelect, StemURI("http://hospital.example/DB/"), false, false)._1
- assert(generated === parsed)
- val output = """
-"""
- }
-
- test("swobjects/tests/healthCare/lists-notBound/db.rq") {
- val sparqlParser = Sparql()
- val sparqlSelect = 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
- WHERE {
- ?patient Person:MiddleName ?middleName .
- ?patient Person:DateOfBirth ?dob .
- ?patient Person:SexDE ?sexEntry .
- ?sexEntry Sex_DE:EntryName ?sex .
-
- ?indicItem Item_Medication:PatientID ?patient .
- ?indicItem Item_Medication:PerformedDTTM ?indicDate .
- ?indicItem Item_Medication:EntryName ?takes .
- ?indicMed Medication:ItemID ?indicItem .
- ?indicMed Medication:MedDictDE ?indicDE .
- ?indicDE Medication_DE:NDC ?indicNDC .
- ?indicCode NDCcodes:NDC ?indicNDC .
- ?indicCode NDCcodes:ingredient "6809"^^xsd:integer
-
- MINUS {
- ?disqualItem Item_Medication:PatientID ?patient .
- ?disqualItem Item_Medication:PerformedDTTM ?disqualDate .
- ?disqualItem Item_Medication:EntryName ?takes .
- ?disqualMed Medication:ItemID ?disqualItem .
- ?disqualMed Medication:MedDictDE ?disqualDE .
- ?disqualDE Medication_DE:NDC ?disqualNDC .
- ?disqualCode NDCcodes:NDC ?disqualNDC .
- ?disqualCode NDCcodes:ingredient "11289"^^xsd:integer
- }
- }
-""").get
- val sqlParser = SqlParser()
- val parsed = sqlParser.parseAll(sqlParser.select, """
-SELECT R_patient.ID AS patient
- FROM Person AS R_patient
- INNER JOIN Sex_DE AS R_sexEntry ON R_sexEntry.ID=R_patient.SexDE
- INNER JOIN Item_Medication AS R_indicItem ON R_indicItem.PatientID=R_patient.ID
- INNER JOIN Medication AS R_indicMed ON R_indicMed.ItemID=R_indicItem.ID
- INNER JOIN Medication_DE AS R_indicDE ON R_indicDE.ID=R_indicMed.MedDictDE
- INNER JOIN NDCcodes AS R_indicCode ON R_indicCode.NDC=R_indicDE.NDC
- LEFT OUTER JOIN (
- SELECT R_disqualCode.ID AS disqualCode, R_disqualMed.MedDictDE AS disqualDE,
- R_disqualItem.PerformedDTTM AS disqualDate, R_disqualItem.ID AS disqualItem,
- R_disqualMed.ID AS disqualMed, R_disqualDE.NDC AS disqualNDC,
- R_disqualItem.PatientID AS patient, R_disqualItem.EntryName AS takes, 6 AS _DISJOINT_
- FROM Item_Medication AS R_disqualItem
- INNER JOIN Medication AS R_disqualMed ON R_disqualMed.ItemID=R_disqualItem.ID
- INNER JOIN Medication_DE AS R_disqualDE ON R_disqualDE.ID=R_disqualMed.MedDictDE
- INNER JOIN NDCcodes AS R_disqualCode ON R_disqualCode.NDC=R_disqualDE.NDC
- WHERE R_disqualCode.ingredient=11289
- AND R_disqualItem.EntryName IS NOT NULL
- AND R_disqualItem.PatientID IS NOT NULL
- AND R_disqualItem.PerformedDTTM IS NOT NULL
- ) AS G_opt6 ON G_opt6.patient=R_patient.ID
- AND G_opt6.takes=R_indicItem.EntryName
- WHERE G_opt6._DISJOINT_ IS NULL
- AND R_indicCode.ingredient=6809
- AND R_indicItem.EntryName IS NOT NULL
- AND R_indicItem.PerformedDTTM IS NOT NULL
- AND R_patient.DateOfBirth IS NOT NULL
- AND R_patient.MiddleName IS NOT NULL
- AND R_sexEntry.EntryName IS NOT NULL
-""").get
- val generated = SparqlToSql(hosp1, sparqlSelect, StemURI("http://hospital.example/DB/"), false, false)._1
- assert(generated === parsed)
- val output = """
-"""
- }
-
-}