--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/scala/SQLTest.scala Sun Oct 31 16:01:55 2010 -0400
@@ -0,0 +1,612 @@
+package org.w3.sw.sql
+
+import org.w3.sw.util._
+import org.w3.sw.rdb.RDB
+
+import org.scalatest.FunSuite
+import java.net.URI
+
+class SQLTest extends FunSuite {
+
+ implicit def string2relName (n:String) = RDB.RelName(n)
+ implicit def l2db (rs:List[RDB.Relation]):Map[RDB.RelName, RDB.Relation] =
+ rs.map(r => (r.name -> r)).toMap
+ // implicit def string2attrName (n:String) = RDB.AttrName(n)
+
+ test("parse ANDexpression") {
+ // AliasedResource(Relation(Name("Employee")),RelVar(Name("R_emp")))
+ val a = SqlParser()
+ val e = """
+R_manager.id=R_emp.manager AND R_emp.lastName IS NOT NULL AND R_manager.lastName IS NOT NULL
+"""
+ val expected = ExprConjunction(Set(
+ RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("manager")))),
+ RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName")))),
+ RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("lastName"))))))
+ assert(expected === (a.parseAll(a.expression, e).get))
+ }
+
+ test("parse ORexpression") {
+ // AliasedResource(Relation(Name("Employee")),RelVar(Name("R_emp")))
+ val a = SqlParser()
+ val e = """
+R_manager.id=R_emp.manager OR R_emp.lastName IS NOT NULL OR R_manager.lastName IS NOT NULL
+"""
+ val expected = ExprDisjunction(Set(
+ RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("manager")))),
+ RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName")))),
+ RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("lastName"))))))
+ assert(expected === (a.parseAll(a.expression, e).get))
+ }
+
+ test("parse nested expression") {
+ // AliasedResource(Relation(Name("Employee")),RelVar(Name("R_emp")))
+ val a = SqlParser()
+ val e = """
+( R_manager.id=R_emp.manager OR R_emp.lastName IS NOT NULL OR R_manager.lastName IS NOT NULL )
+"""
+ val expected = ExprDisjunction(Set(
+ RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("manager")))),
+ RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName")))),
+ RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("lastName"))))))
+ assert(expected === (a.parseAll(a.expression, e).get))
+ }
+
+ test("parse no FROM") {
+ // AliasedResource(Relation(Name("Employee")),RelVar(Name("R_emp")))
+ val a = SqlParser()
+ val e = """
+SELECT 1 AS _TEST_
+"""
+ val expected = Select(
+ false,
+ Projection(Set(ProjectAttribute(PrimaryExpressionTyped(RDB.Datatype.INTEGER,Name("1")),
+ AttrAlias(Name("_TEST_"))))),
+ TableList(AddOrderedSet()),
+ None, List[OrderElt](), None, None)
+ assert(expected === (a.parseAll(a.select, e).get))
+ }
+
+ test("parse WHERE") {
+ // AliasedResource(Relation(Name("Employee")),RelVar(Name("R_emp")))
+ val a = SqlParser()
+ val e = """
+SELECT R_emp.lastName AS A_empName
+ FROM Employee AS R_emp
+ INNER JOIN Employee AS R_manager
+ WHERE R_manager.id=R_emp.manager
+"""
+ val expected = Select(
+ false,
+ Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),
+ RDB.AttrName("lastName")),
+ AttrAlias(Name("A_empName"))))),
+ TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None),
+ InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_manager"))), None))),
+ Some(
+ RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("manager"))))),
+ List[OrderElt](), None, None)
+ assert(expected === (a.parseAll(a.select, e).get))
+ }
+
+ test("parse INNER JOIN ON") {
+ // AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp")))
+ val a = SqlParser()
+ val e = """
+SELECT R_emp.lastName AS A_empName
+ FROM Employee AS R_emp
+ INNER JOIN Employee AS R_manager ON R_manager.id=R_emp.manager
+"""
+ val expected = Select(
+ false,
+ Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),
+ RDB.AttrName("lastName")),
+ AttrAlias(Name("A_empName"))))),
+ TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None),
+ InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_manager"))), None))),
+ Some(
+ RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("manager"))))),
+ List[OrderElt](), None, None)
+ assert(expected === (a.parseAll(a.select, e).get))
+ }
+
+ test("parse SQLbgp") {
+ // AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp")))
+ val a = SqlParser()
+ val e = """
+SELECT R_emp.lastName AS A_empName, R_manager.lastName AS A_managName
+ FROM Employee AS R_emp
+ INNER JOIN Employee AS R_manager
+ WHERE R_manager.id=R_emp.manager AND R_emp.lastName IS NOT NULL AND R_manager.lastName IS NOT NULL
+"""
+ val expected = Select(
+ false,
+ Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),
+ RDB.AttrName("lastName")),
+ AttrAlias(Name("A_empName"))),
+ ProjectAttribute(RelVarAttr(RelVar(Name("R_manager")),
+ RDB.AttrName("lastName")),
+ AttrAlias(Name("A_managName"))))),
+ TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None),
+ InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_manager"))), None))),
+ Some(ExprConjunction(Set(
+ RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("manager")))),
+ RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName")))),
+ RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("lastName"))))))),
+ List[OrderElt](), None, None)
+ assert(expected === (a.parseAll(a.select, e).get))
+ }
+
+ test("parse tup1") {
+ val a = SqlParser()
+ val e = """
+SELECT R_emp.lastName AS A_empName
+ FROM Employee AS R_emp
+ WHERE R_emp.manager=18 AND R_emp.lastName IS NOT NULL
+"""
+ val expected = Select(
+ false,
+ Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),
+ RDB.AttrName("lastName")),
+ AttrAlias(Name("A_empName"))))),
+ TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None))),
+ Some(ExprConjunction(Set(RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("manager"))),
+ PrimaryExpressionTyped(RDB.Datatype.INTEGER,Name("18"))),
+ RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName"))))))),
+ List[OrderElt](), None, None)
+ assert(expected === (a.parseAll(a.select, e).get))
+ }
+
+ test("parse litConst1") {
+ val a = SqlParser()
+ val e = """
+SELECT R_emp.lastName AS A_empName
+ FROM Employee AS R_emp
+ INNER JOIN Employee AS R_manager
+WHERE R_emp.manager=R_manager.id AND R_manager.lastName="Johnson" AND R_emp.lastName IS NOT NULL
+"""
+ val expected = Select(
+ false,
+ Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),
+ RDB.AttrName("lastName")),
+ AttrAlias(Name("A_empName"))))),
+ TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None),
+ InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_manager"))), None))),
+ Some(ExprConjunction(Set(RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("manager"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id")))),
+ RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("lastName"))),
+ PrimaryExpressionTyped(RDB.Datatype.STRING,Name("Johnson"))),
+ RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName"))))))),
+ List[OrderElt](), None, None)
+ assert(expected === (a.parseAll(a.select, e).get))
+ }
+
+ test("parse filter1") {
+ val a = SqlParser()
+ val e = """
+SELECT R_emp.lastName AS A_empName, R_grandManager.lastName AS A_grandManagName
+ FROM Employee AS R_emp
+ INNER JOIN Manage AS R_lower
+ INNER JOIN Employee AS R_manager
+ INNER JOIN Manage AS R_upper
+ INNER JOIN Employee AS R_grandManager
+ WHERE R_lower.manages=R_emp.id AND R_manager.id=R_lower.manager
+ AND R_manager.birthday < R_emp.birthday
+ AND R_upper.manages=R_manager.id AND R_grandManager.id=R_upper.manager
+ AND R_grandManager.birthday < R_manager.birthday
+ AND R_emp.lastName IS NOT NULL AND R_grandManager.lastName IS NOT NULL
+"""
+ val expected = Select(
+ false,
+ Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")), RDB.AttrName("lastName")),
+ AttrAlias(Name("A_empName"))),
+ ProjectAttribute(RelVarAttr(RelVar(Name("R_grandManager")),RDB.AttrName("lastName")),
+ AttrAlias(Name("A_grandManagName"))))),
+ TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None),
+ InnerJoin(AliasedResource(RDB.RelName("Manage"),RelVar(Name("R_lower"))), None),
+ InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_manager"))), None),
+ InnerJoin(AliasedResource(RDB.RelName("Manage"),RelVar(Name("R_upper"))), None),
+ InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_grandManager"))), None))),
+ Some(ExprConjunction(Set(RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_lower")),RDB.AttrName("manages"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("id")))),
+ RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_lower")),RDB.AttrName("manager")))),
+ RelationalExpressionLt(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("birthday"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("birthday")))),
+ RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_upper")),RDB.AttrName("manages"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id")))),
+ RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_grandManager")),RDB.AttrName("id"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_upper")),RDB.AttrName("manager")))),
+ RelationalExpressionLt(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_grandManager")),RDB.AttrName("birthday"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("birthday")))),
+ RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName")))),
+ RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_grandManager")),RDB.AttrName("lastName"))))))),
+ List[OrderElt](), None, None)
+ assert(expected === (a.parseAll(a.select, e).get))
+ }
+
+ test("parse disj1") {
+ val a = SqlParser()
+ val e = """
+SELECT R_union1.name AS A_name
+ FROM Employee AS R_who
+ INNER JOIN (
+ SELECT R_manager.lastName AS A_name, R_above.manages AS A_who
+ FROM Manage AS R_above
+ INNER JOIN Employee AS R_manager
+ WHERE R_above.manager=R_manager.id AND R_manager.lastName IS NOT NULL
+ UNION
+ SELECT R_managed.lastName AS A_name, R_below.manager AS A_who
+ FROM Manage AS R_below
+ INNER JOIN Employee AS R_managed
+ WHERE R_below.manages=R_managed.id AND R_managed.lastName IS NOT NULL
+ ) AS R_union1
+ WHERE R_union1.A_who=R_who.id AND R_who.lastName="Smith"
+"""
+ val expected = Select(
+ false,
+ Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_union1")), RDB.AttrName("name")),
+ AttrAlias(Name("A_name"))))),
+ TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_who"))), None),
+ InnerJoin(AliasedResource(Subselect(Union(Set(
+ Select(
+ false,
+ Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_manager")), RDB.AttrName("lastName")),
+ AttrAlias(Name("A_name"))),
+ ProjectAttribute(RelVarAttr(RelVar(Name("R_above")), RDB.AttrName("manages")),
+ AttrAlias(Name("A_who"))))),
+ TableList(AddOrderedSet(
+ InnerJoin(AliasedResource(RDB.RelName("Manage"),RelVar(Name("R_above"))), None),
+ InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_manager"))), None)
+ )),
+ Some(ExprConjunction(Set(RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_above")),RDB.AttrName("manager"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id")))),
+ RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("lastName"))))))),
+ List[OrderElt](), None, None),
+ Select(
+ false,
+ Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_managed")), RDB.AttrName("lastName")),
+ AttrAlias(Name("A_name"))),
+ ProjectAttribute(RelVarAttr(RelVar(Name("R_below")), RDB.AttrName("manager")),
+ AttrAlias(Name("A_who"))))),
+ TableList(AddOrderedSet(
+ InnerJoin(AliasedResource(RDB.RelName("Manage"),RelVar(Name("R_below"))), None),
+ InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_managed"))), None)
+ )),
+ Some(ExprConjunction(Set(RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_below")),RDB.AttrName("manages"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_managed")),RDB.AttrName("id")))),
+ RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_managed")),RDB.AttrName("lastName"))))))),
+ List[OrderElt](), None, None)))),
+ RelVar(Name("R_union1"))), None))),
+ Some(ExprConjunction(Set(RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_union1")),RDB.AttrName("A_who"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_who")),RDB.AttrName("id")))),
+ RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_who")),RDB.AttrName("lastName"))),
+ PrimaryExpressionTyped(RDB.Datatype.STRING,Name("Smith")))))),
+ List[OrderElt](), None, None)
+ assert(expected === (a.parseAll(a.select, e).get))
+ }
+
+ test("parse NULL as A_foo") {
+ val a = SqlParser()
+ val e = """
+SELECT R_above.manages AS A_who, NULL AS A_bday
+ FROM Manage AS R_above
+ WHERE R_above.id IS NOT NULL
+"""
+ val expected = Select(
+ false,
+ Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_above")),
+ RDB.AttrName("manages")),
+ AttrAlias(Name("A_who"))),
+ ProjectAttribute(ConstNULL(),
+ AttrAlias(Name("A_bday"))))),
+ TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Manage"),RelVar(Name("R_above"))), None))),
+ Some(
+ RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_above")),RDB.AttrName("id"))))),
+ List[OrderElt](), None, None)
+ assert(expected === (a.parseAll(a.select, e).get))
+ }
+
+ test("parse CONCAT") {
+ val a = SqlParser()
+ val QuotedBaseURI = "\"http://hr.example/DB/\""
+ val e = """
+SELECT CONCAT(""" + QuotedBaseURI + """, "Employee", "/", "id", ".", R_emp.id, "#record") AS A_emp
+ FROM Employee AS R_emp
+"""
+ val expected = Select(
+ false, Projection(Set(ProjectAttribute(Concat(List(PrimaryExpressionTyped(RDB.Datatype("String"),Name("http://hr.example/DB/")),
+ PrimaryExpressionTyped(RDB.Datatype("String"),Name("Employee")),
+ PrimaryExpressionTyped(RDB.Datatype("String"),Name("/")),
+ PrimaryExpressionTyped(RDB.Datatype("String"),Name("id")),
+ PrimaryExpressionTyped(RDB.Datatype("String"),Name(".")),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("id"))),
+ PrimaryExpressionTyped(RDB.Datatype("String"),Name("#record")))),
+ AttrAlias(Name("A_emp"))))),
+ TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None))),
+ None, List[OrderElt](), None, None)
+ assert(expected === (a.parseAll(a.select, e).get))
+ }
+
+ test("parse expr disjunction") {
+ val a = SqlParser()
+ val e = """
+SELECT R_above.manages AS A_who, NULL AS A_bday
+ FROM Manage AS R_above
+ WHERE (R_above.id IS NOT NULL) OR (R_above.id < 5 AND R_above.id < 3)
+"""
+ val expected = Select(
+ false,
+ Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_above")),
+ RDB.AttrName("manages")),
+ AttrAlias(Name("A_who"))),
+ ProjectAttribute(ConstNULL(),
+ AttrAlias(Name("A_bday"))))),
+ TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Manage"),RelVar(Name("R_above"))), None))),
+ Some(
+ ExprDisjunction(Set(
+ RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_above")),RDB.AttrName("id")))),
+ ExprConjunction(Set(
+ RelationalExpressionLt(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_above")),RDB.AttrName("id"))),
+ PrimaryExpressionTyped(RDB.Datatype.INTEGER,Name("5"))),
+ RelationalExpressionLt(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_above")),RDB.AttrName("id"))),
+ PrimaryExpressionTyped(RDB.Datatype.INTEGER,Name("3")))
+ ))))),
+ List[OrderElt](), None, None)
+ assert(expected === (a.parseAll(a.select, e).get))
+ }
+
+ test("parse LEFT OUTER JOIN") {
+ val a = SqlParser()
+ val e = """
+SELECT R_emp.lastName AS A_empName, R_mang.manageName AS A_manageName
+ FROM Employee AS R_emp
+ LEFT OUTER JOIN Manage AS R_mang ON R_mang.emp=R_emp.id
+ WHERE R_emp.lastName IS NOT NULL
+"""
+ val expected = Select(
+ false,
+ Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),
+ RDB.AttrName("lastName")),
+ AttrAlias(Name("A_empName"))),
+ ProjectAttribute(RelVarAttr(RelVar(Name("R_mang")),
+ RDB.AttrName("manageName")),
+ AttrAlias(Name("A_manageName"))))),
+ TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None),
+ LeftOuterJoin(AliasedResource(RDB.RelName("Manage"),RelVar(Name("R_mang"))),
+ RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_mang")),RDB.AttrName("emp"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("id")))
+ )))),
+ Some(
+ RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName"))))
+ ),
+ List[OrderElt](), None, None)
+ assert(expected === (a.parseAll(a.select, e).get))
+ }
+
+ test("parse LEFT OUTER SELECT") {
+ val a = SqlParser()
+ val e = """
+SELECT R_emp.lastName AS A_empName, R_mang.manageName AS A_manageName
+ FROM Employee AS R_emp
+ LEFT OUTER JOIN (
+ SELECT R_emp.lastName AS A_empName, R_mang.manageName AS A_manageName
+ FROM Employee AS R_emp
+ ) AS R_mang ON R_mang.emp=R_emp.id
+ WHERE R_emp.lastName IS NOT NULL
+"""
+ val expected =
+ Select(
+ false,
+ Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName")),AttrAlias(Name("A_empName"))),
+ ProjectAttribute(RelVarAttr(RelVar(Name("R_mang")),RDB.AttrName("manageName")),AttrAlias(Name("A_manageName"))))),
+ TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None),
+ LeftOuterJoin(AliasedResource(
+ Subselect(Select(
+ false,
+ Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName")),AttrAlias(Name("A_empName"))),
+ ProjectAttribute(RelVarAttr(RelVar(Name("R_mang")),RDB.AttrName("manageName")),AttrAlias(Name("A_manageName"))))),
+ TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None))),
+ None, List[OrderElt](), None, None)),
+ RelVar(Name("R_mang"))),
+ RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_mang")),RDB.AttrName("emp"))),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("id"))))))),
+ Some(RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName"))))),
+ List[OrderElt](), None, None)
+ assert(expected === (a.parseAll(a.select, e).get))
+ }
+
+ test("x") {
+ val a = SqlParser()
+ val e = """
+ID INT PRIMARY KEY
+"""
+ val expected = FieldDesc(RDB.AttrName("ID"), RDB.Datatype.INTEGER, true)
+ assert(expected === (a.parseAll(a.fielddescorkeydef, e).get))
+ }
+
+ test("y") {
+ val a = SqlParser()
+ val e = """
+CREATE TABLE Sex_DE (ID INT PRIMARY KEY)
+"""
+ val expected = RDB.Database(
+ RDB.Relation("Sex_DE",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER),
+ List(),
+ List(RDB.CandidateKey("ID")),
+ Option(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys()))
+ assert(expected === (a.parseAll(a.ddl, e).get))
+ }
+
+ test("integrated PK") {
+
+ val a = SqlParser()
+ val e = """
+CREATE TABLE Sex_DE (ID INT PRIMARY KEY, EnterpriseEntryID INT)
+"""
+ val expected = RDB.Database(
+ RDB.Relation("Sex_DE",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "EnterpriseEntryID" -> RDB.Datatype.INTEGER),
+ List(),
+ List(RDB.CandidateKey("ID")),
+ Option(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys()))
+
+ assert(expected === (a.parseAll(a.ddl, e).get))
+ }
+
+ test("post-facto PK") {
+ val a = SqlParser()
+ val e = """
+CREATE TABLE Sex_DE (ID INT, EnterpriseEntryID INT, PRIMARY KEY (ID))
+"""
+ val expected = RDB.Database(
+ RDB.Relation("Sex_DE",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "EnterpriseEntryID" -> RDB.Datatype.INTEGER),
+ List(),
+ List(RDB.CandidateKey("ID")),
+ Option(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys()))
+
+ assert(expected === (a.parseAll(a.ddl, e).get))
+ }
+
+ test("foreign key") {
+ val a = SqlParser()
+ val e = """
+ CREATE TABLE Person (ID INT PRIMARY KEY, MiddleName STRING, DateOfBirth DATE, SexDE INT, FOREIGN KEY (SexDE) REFERENCES Sex_DE(ID));
+ CREATE TABLE Sex_DE (ID INT PRIMARY KEY, EntryName STRING)
+ """
+ val expected = RDB.Database(
+ RDB.Relation("Person",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "MiddleName" -> RDB.Datatype.STRING,
+ "DateOfBirth" -> RDB.Datatype.DATE,
+ "SexDE" -> RDB.Datatype.INTEGER),
+ List(),
+ List(RDB.CandidateKey("ID")),
+ Option(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys(List("SexDE") -> RDB.Target("Sex_DE", RDB.CandidateKey("ID")))),
+ RDB.Relation("Sex_DE",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "EntryName" -> RDB.Datatype.STRING),
+ List(),
+ List(RDB.CandidateKey("ID")),
+ Option(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys())
+ )
+ assert(expected === (a.parseAll(a.ddl, e).get))
+ }
+
+ test("create db") {
+ val a = SqlParser()
+ val e = """
+CREATE TABLE Person (ID INT PRIMARY KEY, MiddleName STRING, DateOfBirth DATE, SexDE INT, 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, PatientID INT, FOREIGN KEY (PatientID) REFERENCES Person(ID), PerformedDTTM DATE, EntryName STRING);
+CREATE TABLE Medication (ID INT PRIMARY KEY, 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 expected = RDB.Database(
+ RDB.Relation("Person",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "MiddleName" -> RDB.Datatype.STRING,
+ "DateOfBirth" -> RDB.Datatype.DATE,
+ "SexDE" -> RDB.Datatype.INTEGER),
+ List(),
+ List(RDB.CandidateKey("ID")),
+ Option(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys(List("SexDE") -> RDB.Target("Sex_DE", RDB.CandidateKey("ID")))),
+ RDB.Relation("Sex_DE",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "EntryName" -> RDB.Datatype.STRING),
+ List(),
+ List(RDB.CandidateKey("ID")),
+ Option(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys()),
+ RDB.Relation("Item_Medication",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "PatientID" -> RDB.Datatype.INTEGER,
+ "PerformedDTTM" -> RDB.Datatype.DATE,
+ "EntryName" -> RDB.Datatype.STRING),
+ List(),
+ List(RDB.CandidateKey("ID")),
+ Option(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys(List("PatientID") -> RDB.Target("Person", RDB.CandidateKey("ID")))),
+ RDB.Relation("Medication",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "ItemID" -> RDB.Datatype.INTEGER,
+ "MedDictDE" -> RDB.Datatype.INTEGER),
+ List(),
+ List(RDB.CandidateKey("ID")),
+ Option(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys(List("ItemID") -> RDB.Target("Item_Medication", RDB.CandidateKey("ID")),
+ List("MedDictDE") -> RDB.Target("Medication_DE", RDB.CandidateKey("ID")))),
+ RDB.Relation("Medication_DE",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "NDC" -> RDB.Datatype.INTEGER),
+ List(),
+ List(RDB.CandidateKey("ID")),
+ Option(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys()),
+ RDB.Relation("NDCcodes",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "NDC" -> RDB.Datatype.INTEGER,
+ "ingredient" -> RDB.Datatype.INTEGER),
+ List(),
+ List(RDB.CandidateKey("ID")),
+ Option(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys())
+ )
+ assert(expected === (a.parseAll(a.ddl, e).get))
+ }
+
+ test("CREATE VIEW") {
+ // AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp")))
+ val a = SqlParser()
+ val e = """
+CREATE VIEW triples AS SELECT
+ CONCAT("http://hr.example/DB/", "Employee", "/", "empid", ".", R_emp.id, "#record") AS S,
+ "<http://hr.example/DB/Employee#lastName>" AS P,
+ R_emp.lastName AS O
+ FROM Employee AS R_emp
+""" // "
+ val expected = View(RDB.RelName("triples"), Select(
+ false,
+ Projection(Set(
+ ProjectAttribute(Concat(List(PrimaryExpressionTyped(RDB.Datatype("String"),Name("http://hr.example/DB/")),
+ PrimaryExpressionTyped(RDB.Datatype("String"),Name("Employee")),
+ PrimaryExpressionTyped(RDB.Datatype("String"),Name("/")),
+ PrimaryExpressionTyped(RDB.Datatype("String"),Name("empid")),
+ PrimaryExpressionTyped(RDB.Datatype("String"),Name(".")),
+ PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("id"))),
+ PrimaryExpressionTyped(RDB.Datatype("String"),Name("#record")))),
+ AttrAlias(Name("S"))),
+ ProjectAttribute(PrimaryExpressionTyped(RDB.Datatype.STRING,Name("<http://hr.example/DB/Employee#lastName>")),
+ AttrAlias(Name("P"))),
+ ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),
+ RDB.AttrName("lastName")),
+ AttrAlias(Name("O"))))),
+ TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None))),
+ None, List[OrderElt](), None, None))
+ assert(expected === (a.parseAll(a.createview, e).get))
+ }
+
+ /**
+ * junk that should be elsewhere
+ */
+
+ implicit def relname2relresource (rn:RDB.RelName) : RelationResource = RelationResource(rn)
+
+}
--- a/src/test/scala/SQLTest.scala Sun Oct 31 15:58:49 2010 -0400
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,610 +0,0 @@
-package w3c.sw.sql
-import w3c.sw.util._
-
-import org.scalatest.FunSuite
-import java.net.URI
-
-class SQLTest extends FunSuite {
-
- implicit def string2relName (n:String) = RDB.RelName(n)
- implicit def l2db (rs:List[RDB.Relation]):Map[RDB.RelName, RDB.Relation] =
- rs.map(r => (r.name -> r)).toMap
- // implicit def string2attrName (n:String) = RDB.AttrName(n)
-
- test("parse ANDexpression") {
- // AliasedResource(Relation(Name("Employee")),RelVar(Name("R_emp")))
- val a = SqlParser()
- val e = """
-R_manager.id=R_emp.manager AND R_emp.lastName IS NOT NULL AND R_manager.lastName IS NOT NULL
-"""
- val expected = ExprConjunction(Set(
- RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("manager")))),
- RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName")))),
- RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("lastName"))))))
- assert(expected === (a.parseAll(a.expression, e).get))
- }
-
- test("parse ORexpression") {
- // AliasedResource(Relation(Name("Employee")),RelVar(Name("R_emp")))
- val a = SqlParser()
- val e = """
-R_manager.id=R_emp.manager OR R_emp.lastName IS NOT NULL OR R_manager.lastName IS NOT NULL
-"""
- val expected = ExprDisjunction(Set(
- RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("manager")))),
- RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName")))),
- RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("lastName"))))))
- assert(expected === (a.parseAll(a.expression, e).get))
- }
-
- test("parse nested expression") {
- // AliasedResource(Relation(Name("Employee")),RelVar(Name("R_emp")))
- val a = SqlParser()
- val e = """
-( R_manager.id=R_emp.manager OR R_emp.lastName IS NOT NULL OR R_manager.lastName IS NOT NULL )
-"""
- val expected = ExprDisjunction(Set(
- RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("manager")))),
- RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName")))),
- RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("lastName"))))))
- assert(expected === (a.parseAll(a.expression, e).get))
- }
-
- test("parse no FROM") {
- // AliasedResource(Relation(Name("Employee")),RelVar(Name("R_emp")))
- val a = SqlParser()
- val e = """
-SELECT 1 AS _TEST_
-"""
- val expected = Select(
- false,
- Projection(Set(ProjectAttribute(PrimaryExpressionTyped(RDB.Datatype.INTEGER,Name("1")),
- AttrAlias(Name("_TEST_"))))),
- TableList(AddOrderedSet()),
- None, List[OrderElt](), None, None)
- assert(expected === (a.parseAll(a.select, e).get))
- }
-
- test("parse WHERE") {
- // AliasedResource(Relation(Name("Employee")),RelVar(Name("R_emp")))
- val a = SqlParser()
- val e = """
-SELECT R_emp.lastName AS A_empName
- FROM Employee AS R_emp
- INNER JOIN Employee AS R_manager
- WHERE R_manager.id=R_emp.manager
-"""
- val expected = Select(
- false,
- Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),
- RDB.AttrName("lastName")),
- AttrAlias(Name("A_empName"))))),
- TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None),
- InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_manager"))), None))),
- Some(
- RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("manager"))))),
- List[OrderElt](), None, None)
- assert(expected === (a.parseAll(a.select, e).get))
- }
-
- test("parse INNER JOIN ON") {
- // AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp")))
- val a = SqlParser()
- val e = """
-SELECT R_emp.lastName AS A_empName
- FROM Employee AS R_emp
- INNER JOIN Employee AS R_manager ON R_manager.id=R_emp.manager
-"""
- val expected = Select(
- false,
- Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),
- RDB.AttrName("lastName")),
- AttrAlias(Name("A_empName"))))),
- TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None),
- InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_manager"))), None))),
- Some(
- RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("manager"))))),
- List[OrderElt](), None, None)
- assert(expected === (a.parseAll(a.select, e).get))
- }
-
- test("parse SQLbgp") {
- // AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp")))
- val a = SqlParser()
- val e = """
-SELECT R_emp.lastName AS A_empName, R_manager.lastName AS A_managName
- FROM Employee AS R_emp
- INNER JOIN Employee AS R_manager
- WHERE R_manager.id=R_emp.manager AND R_emp.lastName IS NOT NULL AND R_manager.lastName IS NOT NULL
-"""
- val expected = Select(
- false,
- Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),
- RDB.AttrName("lastName")),
- AttrAlias(Name("A_empName"))),
- ProjectAttribute(RelVarAttr(RelVar(Name("R_manager")),
- RDB.AttrName("lastName")),
- AttrAlias(Name("A_managName"))))),
- TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None),
- InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_manager"))), None))),
- Some(ExprConjunction(Set(
- RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("manager")))),
- RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName")))),
- RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("lastName"))))))),
- List[OrderElt](), None, None)
- assert(expected === (a.parseAll(a.select, e).get))
- }
-
- test("parse tup1") {
- val a = SqlParser()
- val e = """
-SELECT R_emp.lastName AS A_empName
- FROM Employee AS R_emp
- WHERE R_emp.manager=18 AND R_emp.lastName IS NOT NULL
-"""
- val expected = Select(
- false,
- Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),
- RDB.AttrName("lastName")),
- AttrAlias(Name("A_empName"))))),
- TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None))),
- Some(ExprConjunction(Set(RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("manager"))),
- PrimaryExpressionTyped(RDB.Datatype.INTEGER,Name("18"))),
- RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName"))))))),
- List[OrderElt](), None, None)
- assert(expected === (a.parseAll(a.select, e).get))
- }
-
- test("parse litConst1") {
- val a = SqlParser()
- val e = """
-SELECT R_emp.lastName AS A_empName
- FROM Employee AS R_emp
- INNER JOIN Employee AS R_manager
-WHERE R_emp.manager=R_manager.id AND R_manager.lastName="Johnson" AND R_emp.lastName IS NOT NULL
-"""
- val expected = Select(
- false,
- Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),
- RDB.AttrName("lastName")),
- AttrAlias(Name("A_empName"))))),
- TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None),
- InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_manager"))), None))),
- Some(ExprConjunction(Set(RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("manager"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id")))),
- RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("lastName"))),
- PrimaryExpressionTyped(RDB.Datatype.STRING,Name("Johnson"))),
- RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName"))))))),
- List[OrderElt](), None, None)
- assert(expected === (a.parseAll(a.select, e).get))
- }
-
- test("parse filter1") {
- val a = SqlParser()
- val e = """
-SELECT R_emp.lastName AS A_empName, R_grandManager.lastName AS A_grandManagName
- FROM Employee AS R_emp
- INNER JOIN Manage AS R_lower
- INNER JOIN Employee AS R_manager
- INNER JOIN Manage AS R_upper
- INNER JOIN Employee AS R_grandManager
- WHERE R_lower.manages=R_emp.id AND R_manager.id=R_lower.manager
- AND R_manager.birthday < R_emp.birthday
- AND R_upper.manages=R_manager.id AND R_grandManager.id=R_upper.manager
- AND R_grandManager.birthday < R_manager.birthday
- AND R_emp.lastName IS NOT NULL AND R_grandManager.lastName IS NOT NULL
-"""
- val expected = Select(
- false,
- Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")), RDB.AttrName("lastName")),
- AttrAlias(Name("A_empName"))),
- ProjectAttribute(RelVarAttr(RelVar(Name("R_grandManager")),RDB.AttrName("lastName")),
- AttrAlias(Name("A_grandManagName"))))),
- TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None),
- InnerJoin(AliasedResource(RDB.RelName("Manage"),RelVar(Name("R_lower"))), None),
- InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_manager"))), None),
- InnerJoin(AliasedResource(RDB.RelName("Manage"),RelVar(Name("R_upper"))), None),
- InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_grandManager"))), None))),
- Some(ExprConjunction(Set(RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_lower")),RDB.AttrName("manages"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("id")))),
- RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_lower")),RDB.AttrName("manager")))),
- RelationalExpressionLt(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("birthday"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("birthday")))),
- RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_upper")),RDB.AttrName("manages"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id")))),
- RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_grandManager")),RDB.AttrName("id"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_upper")),RDB.AttrName("manager")))),
- RelationalExpressionLt(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_grandManager")),RDB.AttrName("birthday"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("birthday")))),
- RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName")))),
- RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_grandManager")),RDB.AttrName("lastName"))))))),
- List[OrderElt](), None, None)
- assert(expected === (a.parseAll(a.select, e).get))
- }
-
- test("parse disj1") {
- val a = SqlParser()
- val e = """
-SELECT R_union1.name AS A_name
- FROM Employee AS R_who
- INNER JOIN (
- SELECT R_manager.lastName AS A_name, R_above.manages AS A_who
- FROM Manage AS R_above
- INNER JOIN Employee AS R_manager
- WHERE R_above.manager=R_manager.id AND R_manager.lastName IS NOT NULL
- UNION
- SELECT R_managed.lastName AS A_name, R_below.manager AS A_who
- FROM Manage AS R_below
- INNER JOIN Employee AS R_managed
- WHERE R_below.manages=R_managed.id AND R_managed.lastName IS NOT NULL
- ) AS R_union1
- WHERE R_union1.A_who=R_who.id AND R_who.lastName="Smith"
-"""
- val expected = Select(
- false,
- Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_union1")), RDB.AttrName("name")),
- AttrAlias(Name("A_name"))))),
- TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_who"))), None),
- InnerJoin(AliasedResource(Subselect(Union(Set(
- Select(
- false,
- Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_manager")), RDB.AttrName("lastName")),
- AttrAlias(Name("A_name"))),
- ProjectAttribute(RelVarAttr(RelVar(Name("R_above")), RDB.AttrName("manages")),
- AttrAlias(Name("A_who"))))),
- TableList(AddOrderedSet(
- InnerJoin(AliasedResource(RDB.RelName("Manage"),RelVar(Name("R_above"))), None),
- InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_manager"))), None)
- )),
- Some(ExprConjunction(Set(RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_above")),RDB.AttrName("manager"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("id")))),
- RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_manager")),RDB.AttrName("lastName"))))))),
- List[OrderElt](), None, None),
- Select(
- false,
- Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_managed")), RDB.AttrName("lastName")),
- AttrAlias(Name("A_name"))),
- ProjectAttribute(RelVarAttr(RelVar(Name("R_below")), RDB.AttrName("manager")),
- AttrAlias(Name("A_who"))))),
- TableList(AddOrderedSet(
- InnerJoin(AliasedResource(RDB.RelName("Manage"),RelVar(Name("R_below"))), None),
- InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_managed"))), None)
- )),
- Some(ExprConjunction(Set(RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_below")),RDB.AttrName("manages"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_managed")),RDB.AttrName("id")))),
- RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_managed")),RDB.AttrName("lastName"))))))),
- List[OrderElt](), None, None)))),
- RelVar(Name("R_union1"))), None))),
- Some(ExprConjunction(Set(RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_union1")),RDB.AttrName("A_who"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_who")),RDB.AttrName("id")))),
- RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_who")),RDB.AttrName("lastName"))),
- PrimaryExpressionTyped(RDB.Datatype.STRING,Name("Smith")))))),
- List[OrderElt](), None, None)
- assert(expected === (a.parseAll(a.select, e).get))
- }
-
- test("parse NULL as A_foo") {
- val a = SqlParser()
- val e = """
-SELECT R_above.manages AS A_who, NULL AS A_bday
- FROM Manage AS R_above
- WHERE R_above.id IS NOT NULL
-"""
- val expected = Select(
- false,
- Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_above")),
- RDB.AttrName("manages")),
- AttrAlias(Name("A_who"))),
- ProjectAttribute(ConstNULL(),
- AttrAlias(Name("A_bday"))))),
- TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Manage"),RelVar(Name("R_above"))), None))),
- Some(
- RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_above")),RDB.AttrName("id"))))),
- List[OrderElt](), None, None)
- assert(expected === (a.parseAll(a.select, e).get))
- }
-
- test("parse CONCAT") {
- val a = SqlParser()
- val QuotedBaseURI = "\"http://hr.example/DB/\""
- val e = """
-SELECT CONCAT(""" + QuotedBaseURI + """, "Employee", "/", "id", ".", R_emp.id, "#record") AS A_emp
- FROM Employee AS R_emp
-"""
- val expected = Select(
- false, Projection(Set(ProjectAttribute(Concat(List(PrimaryExpressionTyped(RDB.Datatype("String"),Name("http://hr.example/DB/")),
- PrimaryExpressionTyped(RDB.Datatype("String"),Name("Employee")),
- PrimaryExpressionTyped(RDB.Datatype("String"),Name("/")),
- PrimaryExpressionTyped(RDB.Datatype("String"),Name("id")),
- PrimaryExpressionTyped(RDB.Datatype("String"),Name(".")),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("id"))),
- PrimaryExpressionTyped(RDB.Datatype("String"),Name("#record")))),
- AttrAlias(Name("A_emp"))))),
- TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None))),
- None, List[OrderElt](), None, None)
- assert(expected === (a.parseAll(a.select, e).get))
- }
-
- test("parse expr disjunction") {
- val a = SqlParser()
- val e = """
-SELECT R_above.manages AS A_who, NULL AS A_bday
- FROM Manage AS R_above
- WHERE (R_above.id IS NOT NULL) OR (R_above.id < 5 AND R_above.id < 3)
-"""
- val expected = Select(
- false,
- Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_above")),
- RDB.AttrName("manages")),
- AttrAlias(Name("A_who"))),
- ProjectAttribute(ConstNULL(),
- AttrAlias(Name("A_bday"))))),
- TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Manage"),RelVar(Name("R_above"))), None))),
- Some(
- ExprDisjunction(Set(
- RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_above")),RDB.AttrName("id")))),
- ExprConjunction(Set(
- RelationalExpressionLt(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_above")),RDB.AttrName("id"))),
- PrimaryExpressionTyped(RDB.Datatype.INTEGER,Name("5"))),
- RelationalExpressionLt(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_above")),RDB.AttrName("id"))),
- PrimaryExpressionTyped(RDB.Datatype.INTEGER,Name("3")))
- ))))),
- List[OrderElt](), None, None)
- assert(expected === (a.parseAll(a.select, e).get))
- }
-
- test("parse LEFT OUTER JOIN") {
- val a = SqlParser()
- val e = """
-SELECT R_emp.lastName AS A_empName, R_mang.manageName AS A_manageName
- FROM Employee AS R_emp
- LEFT OUTER JOIN Manage AS R_mang ON R_mang.emp=R_emp.id
- WHERE R_emp.lastName IS NOT NULL
-"""
- val expected = Select(
- false,
- Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),
- RDB.AttrName("lastName")),
- AttrAlias(Name("A_empName"))),
- ProjectAttribute(RelVarAttr(RelVar(Name("R_mang")),
- RDB.AttrName("manageName")),
- AttrAlias(Name("A_manageName"))))),
- TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None),
- LeftOuterJoin(AliasedResource(RDB.RelName("Manage"),RelVar(Name("R_mang"))),
- RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_mang")),RDB.AttrName("emp"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("id")))
- )))),
- Some(
- RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName"))))
- ),
- List[OrderElt](), None, None)
- assert(expected === (a.parseAll(a.select, e).get))
- }
-
- test("parse LEFT OUTER SELECT") {
- val a = SqlParser()
- val e = """
-SELECT R_emp.lastName AS A_empName, R_mang.manageName AS A_manageName
- FROM Employee AS R_emp
- LEFT OUTER JOIN (
- SELECT R_emp.lastName AS A_empName, R_mang.manageName AS A_manageName
- FROM Employee AS R_emp
- ) AS R_mang ON R_mang.emp=R_emp.id
- WHERE R_emp.lastName IS NOT NULL
-"""
- val expected =
- Select(
- false,
- Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName")),AttrAlias(Name("A_empName"))),
- ProjectAttribute(RelVarAttr(RelVar(Name("R_mang")),RDB.AttrName("manageName")),AttrAlias(Name("A_manageName"))))),
- TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None),
- LeftOuterJoin(AliasedResource(
- Subselect(Select(
- false,
- Projection(Set(ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName")),AttrAlias(Name("A_empName"))),
- ProjectAttribute(RelVarAttr(RelVar(Name("R_mang")),RDB.AttrName("manageName")),AttrAlias(Name("A_manageName"))))),
- TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None))),
- None, List[OrderElt](), None, None)),
- RelVar(Name("R_mang"))),
- RelationalExpressionEq(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_mang")),RDB.AttrName("emp"))),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("id"))))))),
- Some(RelationalExpressionNotNull(PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("lastName"))))),
- List[OrderElt](), None, None)
- assert(expected === (a.parseAll(a.select, e).get))
- }
-
- test("x") {
- val a = SqlParser()
- val e = """
-ID INT PRIMARY KEY
-"""
- val expected = FieldDesc(RDB.AttrName("ID"), RDB.Datatype.INTEGER, true)
- assert(expected === (a.parseAll(a.fielddescorkeydef, e).get))
- }
-
- test("y") {
- val a = SqlParser()
- val e = """
-CREATE TABLE Sex_DE (ID INT PRIMARY KEY)
-"""
- val expected = RDB.Database(
- RDB.Relation("Sex_DE",
- RDB.Header("ID" -> RDB.Datatype.INTEGER),
- List(),
- List(RDB.CandidateKey("ID")),
- Option(RDB.CandidateKey("ID")),
- RDB.ForeignKeys()))
- assert(expected === (a.parseAll(a.ddl, e).get))
- }
-
- test("integrated PK") {
-
- val a = SqlParser()
- val e = """
-CREATE TABLE Sex_DE (ID INT PRIMARY KEY, EnterpriseEntryID INT)
-"""
- val expected = RDB.Database(
- RDB.Relation("Sex_DE",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "EnterpriseEntryID" -> RDB.Datatype.INTEGER),
- List(),
- List(RDB.CandidateKey("ID")),
- Option(RDB.CandidateKey("ID")),
- RDB.ForeignKeys()))
-
- assert(expected === (a.parseAll(a.ddl, e).get))
- }
-
- test("post-facto PK") {
- val a = SqlParser()
- val e = """
-CREATE TABLE Sex_DE (ID INT, EnterpriseEntryID INT, PRIMARY KEY (ID))
-"""
- val expected = RDB.Database(
- RDB.Relation("Sex_DE",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "EnterpriseEntryID" -> RDB.Datatype.INTEGER),
- List(),
- List(RDB.CandidateKey("ID")),
- Option(RDB.CandidateKey("ID")),
- RDB.ForeignKeys()))
-
- assert(expected === (a.parseAll(a.ddl, e).get))
- }
-
- test("foreign key") {
- val a = SqlParser()
- val e = """
- CREATE TABLE Person (ID INT PRIMARY KEY, MiddleName STRING, DateOfBirth DATE, SexDE INT, FOREIGN KEY (SexDE) REFERENCES Sex_DE(ID));
- CREATE TABLE Sex_DE (ID INT PRIMARY KEY, EntryName STRING)
- """
- val expected = RDB.Database(
- RDB.Relation("Person",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "MiddleName" -> RDB.Datatype.STRING,
- "DateOfBirth" -> RDB.Datatype.DATE,
- "SexDE" -> RDB.Datatype.INTEGER),
- List(),
- List(RDB.CandidateKey("ID")),
- Option(RDB.CandidateKey("ID")),
- RDB.ForeignKeys(List("SexDE") -> RDB.Target("Sex_DE", RDB.CandidateKey("ID")))),
- RDB.Relation("Sex_DE",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "EntryName" -> RDB.Datatype.STRING),
- List(),
- List(RDB.CandidateKey("ID")),
- Option(RDB.CandidateKey("ID")),
- RDB.ForeignKeys())
- )
- assert(expected === (a.parseAll(a.ddl, e).get))
- }
-
- test("create db") {
- val a = SqlParser()
- val e = """
-CREATE TABLE Person (ID INT PRIMARY KEY, MiddleName STRING, DateOfBirth DATE, SexDE INT, 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, PatientID INT, FOREIGN KEY (PatientID) REFERENCES Person(ID), PerformedDTTM DATE, EntryName STRING);
-CREATE TABLE Medication (ID INT PRIMARY KEY, 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 expected = RDB.Database(
- RDB.Relation("Person",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "MiddleName" -> RDB.Datatype.STRING,
- "DateOfBirth" -> RDB.Datatype.DATE,
- "SexDE" -> RDB.Datatype.INTEGER),
- List(),
- List(RDB.CandidateKey("ID")),
- Option(RDB.CandidateKey("ID")),
- RDB.ForeignKeys(List("SexDE") -> RDB.Target("Sex_DE", RDB.CandidateKey("ID")))),
- RDB.Relation("Sex_DE",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "EntryName" -> RDB.Datatype.STRING),
- List(),
- List(RDB.CandidateKey("ID")),
- Option(RDB.CandidateKey("ID")),
- RDB.ForeignKeys()),
- RDB.Relation("Item_Medication",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "PatientID" -> RDB.Datatype.INTEGER,
- "PerformedDTTM" -> RDB.Datatype.DATE,
- "EntryName" -> RDB.Datatype.STRING),
- List(),
- List(RDB.CandidateKey("ID")),
- Option(RDB.CandidateKey("ID")),
- RDB.ForeignKeys(List("PatientID") -> RDB.Target("Person", RDB.CandidateKey("ID")))),
- RDB.Relation("Medication",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "ItemID" -> RDB.Datatype.INTEGER,
- "MedDictDE" -> RDB.Datatype.INTEGER),
- List(),
- List(RDB.CandidateKey("ID")),
- Option(RDB.CandidateKey("ID")),
- RDB.ForeignKeys(List("ItemID") -> RDB.Target("Item_Medication", RDB.CandidateKey("ID")),
- List("MedDictDE") -> RDB.Target("Medication_DE", RDB.CandidateKey("ID")))),
- RDB.Relation("Medication_DE",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "NDC" -> RDB.Datatype.INTEGER),
- List(),
- List(RDB.CandidateKey("ID")),
- Option(RDB.CandidateKey("ID")),
- RDB.ForeignKeys()),
- RDB.Relation("NDCcodes",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "NDC" -> RDB.Datatype.INTEGER,
- "ingredient" -> RDB.Datatype.INTEGER),
- List(),
- List(RDB.CandidateKey("ID")),
- Option(RDB.CandidateKey("ID")),
- RDB.ForeignKeys())
- )
- assert(expected === (a.parseAll(a.ddl, e).get))
- }
-
- test("CREATE VIEW") {
- // AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp")))
- val a = SqlParser()
- val e = """
-CREATE VIEW triples AS SELECT
- CONCAT("http://hr.example/DB/", "Employee", "/", "empid", ".", R_emp.id, "#record") AS S,
- "<http://hr.example/DB/Employee#lastName>" AS P,
- R_emp.lastName AS O
- FROM Employee AS R_emp
-""" // "
- val expected = View(RDB.RelName("triples"), Select(
- false,
- Projection(Set(
- ProjectAttribute(Concat(List(PrimaryExpressionTyped(RDB.Datatype("String"),Name("http://hr.example/DB/")),
- PrimaryExpressionTyped(RDB.Datatype("String"),Name("Employee")),
- PrimaryExpressionTyped(RDB.Datatype("String"),Name("/")),
- PrimaryExpressionTyped(RDB.Datatype("String"),Name("empid")),
- PrimaryExpressionTyped(RDB.Datatype("String"),Name(".")),
- PrimaryExpressionAttr(RelVarAttr(RelVar(Name("R_emp")),RDB.AttrName("id"))),
- PrimaryExpressionTyped(RDB.Datatype("String"),Name("#record")))),
- AttrAlias(Name("S"))),
- ProjectAttribute(PrimaryExpressionTyped(RDB.Datatype.STRING,Name("<http://hr.example/DB/Employee#lastName>")),
- AttrAlias(Name("P"))),
- ProjectAttribute(RelVarAttr(RelVar(Name("R_emp")),
- RDB.AttrName("lastName")),
- AttrAlias(Name("O"))))),
- TableList(AddOrderedSet(InnerJoin(AliasedResource(RDB.RelName("Employee"),RelVar(Name("R_emp"))), None))),
- None, List[OrderElt](), None, None))
- assert(expected === (a.parseAll(a.createview, e).get))
- }
-
- /**
- * junk that should be elsewhere
- */
-
- implicit def relname2relresource (rn:RDB.RelName) : RelationResource = RelationResource(rn)
-
-}