--- a/src/test/scala/SparqlToSparqlToSqlTest.scala Sun Jun 13 07:39:00 2010 -0400
+++ b/src/test/scala/SparqlToSparqlToSqlTest.scala Sun Jun 13 07:42:37 2010 -0400
@@ -20,6 +20,333 @@
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 ==========
*
*/