--- a/src/test/scala/SparqlToSparqlToSqlTest.scala Sun Jun 13 07:19:19 2010 -0400
+++ b/src/test/scala/SparqlToSparqlToSqlTest.scala Sun Jun 13 07:39:00 2010 -0400
@@ -20,333 +20,6 @@
val DDLParser = Sql()
- 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:DatabaseDesc = 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 asStem = SparqlToSparql(foafQuery, List(hr2foaf))
- 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 = Sql()
- 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 = Sql()
- 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:DatabaseDesc = 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
-
- 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(db2hl7))
- assert(asStem === stemQuery)
- val sqlParser = Sql()
- 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 = Sql()
- 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(db2hl7))
- 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 = Sql()
- val expected = sqlParser.parseAll(sqlParser.createview, """
-""").get
- assert(expected === view)
- }
- */
-
/* ========== BSBM database tests ==========
*
*/
@@ -517,9 +190,9 @@
?product rdf:type ?ptp .
?product rdfs:label ?p_label .
?product bsbm:productPropertyNumeric1 ?lit_propertyNum1 .
- ?product product:propertyNum1 ?lit_propertyNum1 .
} WHERE {
?product product:label ?p_label .
+ ?product product:propertyNum1 ?lit_propertyNum1 .
?pfp productfeatureproduct:product ?product .
?ptp producttypeproduct:product ?product .
}""" //"
@@ -557,7 +230,8 @@
PREFIX ptp: <http://bsbm.example/db/producttypeproduct#>
SELECT ?product ?label WHERE {
- ?product product:label ?label .
+ ?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 .
@@ -567,14 +241,24 @@
val sqlParser = Sql()
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"
+SELECT R_product.label AS label, R_product.nr AS product
+ FROM product AS R_product
+ 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
+ INNER JOIN producttypeproduct AS R_productType59
+ ON R_productType59.product=R_product.nr
+ AND R_productType59.productType=59
+ WHERE R_product.propertyNum1 > 578
+ AND R_product.label IS NOT NULL
""").get
val asStem = SparqlToSparql(bsbmQuery, List(db2bsbm))
assert(asStem === stemQuery)
- // val (asSql, _) = SparqlToSql(bsbmDb, asStem, StemURI("http://bsbm.example/db/"), false, false)
- // assert(asSql === sqlQuery)
+ val (asSql, _) = SparqlToSql(bsbmDb, asStem, StemURI("http://bsbm.example/db/"), false, false)
+ assert(asSql === sqlQuery)
val output = """
"""
}