--- a/sql/src/test/scala/SQLTest.scala Mon Feb 14 18:35:46 2011 -0500
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,1038 +0,0 @@
-package org.w3.sql
-
-import org.w3.util._
-import org.w3.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("parse PRIMARY KEY") {
- 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("parse CREATE") {
- 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))
- }
-
-
- test("parse single quotes") {
- val p = SqlParser()
- val s = """
-CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
-INSERT INTO Addresses (ID, city, state) VALUES (18, 'Cambridge', 'MA');
-"""
- val addrs = RDB.Relation(
- "Addresses",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "city" -> RDB.Datatype.STRING,
- "state" -> RDB.Datatype.STRING),
- List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
- "city" -> RDB.LexicalValue("Cambridge"),
- "state" -> RDB.LexicalValue("MA"))),
- List(RDB.CandidateKey("ID")),
- Some(RDB.CandidateKey("ID")),
- RDB.ForeignKeys())
-
- val expected = RDB.Database(List(addrs))
- assert(expected === (p.parseAll(p.ddl, s).get))
- }
-
-
- test("parse size modifiers") {
- val p = SqlParser()
- val s = """
-CREATE TABLE Addresses (ID INT(5) PRIMARY KEY, city VARCHAR(10), state CHAR(2));
-INSERT INTO Addresses (ID, city, state) VALUES (18, 'Cambridge', 'MA');
-"""
- val addrs = RDB.Relation(
- "Addresses",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "city" -> RDB.Datatype.STRING,
- "state" -> RDB.Datatype.STRING),
- List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
- "city" -> RDB.LexicalValue("Cambridge"),
- "state" -> RDB.LexicalValue("MA"))),
- List(RDB.CandidateKey("ID")),
- Some(RDB.CandidateKey("ID")),
- RDB.ForeignKeys())
-
- val expected = RDB.Database(List(addrs))
- assert(expected === (p.parseAll(p.ddl, s).get))
- }
-
-
- test("parse 2 People 1 Addresses") {
- val p = SqlParser()
- val s = """
-CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
-INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");
-CREATE TABLE People (ID INT PRIMARY KEY, fname STRING, addr INT,
- FOREIGN KEY (addr) REFERENCES Addresses(ID));
-INSERT INTO People (ID, fname, addr) VALUES (7, "Bob", 18);
-INSERT INTO People (ID, fname, addr) VALUES (8, "Sue", NULL);
-"""
- val addrs = RDB.Relation(
- "Addresses",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "city" -> RDB.Datatype.STRING,
- "state" -> RDB.Datatype.STRING),
- List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
- "city" -> RDB.LexicalValue("Cambridge"),
- "state" -> RDB.LexicalValue("MA"))),
- List(RDB.CandidateKey("ID")),
- Some(RDB.CandidateKey("ID")),
- RDB.ForeignKeys())
-
- val people = RDB.Relation(
- "People",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "fname" -> RDB.Datatype.STRING,
- "addr" -> RDB.Datatype.INTEGER),
- List(RDB.Tuple("ID" -> RDB.LexicalValue("7"),
- "fname" -> RDB.LexicalValue("Bob"),
- "addr" -> RDB.LexicalValue("18")),
- RDB.Tuple("ID" -> RDB.LexicalValue("8"),
- "fname" -> RDB.LexicalValue("Sue"),
- "addr" -> RDB.␀)),
- List(RDB.CandidateKey("ID")),
- Some(RDB.CandidateKey("ID")),
- RDB.ForeignKeys(List("addr") -> RDB.Target("Addresses", RDB.CandidateKey("ID"))))
-
- val expected = RDB.Database(List(addrs, people))
- assert(expected === (p.parseAll(p.ddl, s).get))
- }
-
-
- test("parse 2 People 1 Addresses 1 Department") {
- val p = SqlParser()
- val s = """
-CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
-INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");
-CREATE TABLE Department (ID INT PRIMARY KEY, name STRING, city STRING, manager INT,
- FOREIGN KEY (manager) REFERENCES People(ID),
- UNIQUE (name, city));
-INSERT INTO Department (ID, name, city, manager) VALUES (23, "accounting", "Cambridge", 8);
-CREATE TABLE People (ID INT PRIMARY KEY, fname STRING,
- addr INT, FOREIGN KEY (addr) REFERENCES Addresses(ID),
- deptName STRING, deptCity STRING,
- FOREIGN KEY (deptName, deptCity) REFERENCES Department(name, city));
-INSERT INTO People (ID, fname, addr, deptName, deptCity) VALUES (7, "Bob", 18, "accounting", "Cambridge");
-INSERT INTO People (ID, fname, addr, deptName, deptCity) VALUES (8, "Sue", NULL, NULL, NULL);
-"""
-
- val addrs = RDB.Relation(
- "Addresses",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "city" -> RDB.Datatype.STRING,
- "state" -> RDB.Datatype.STRING),
- List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
- "city" -> RDB.LexicalValue("Cambridge"),
- "state" -> RDB.LexicalValue("MA"))),
- List(RDB.CandidateKey("ID")),
- Some(RDB.CandidateKey("ID")),
- RDB.ForeignKeys())
-
- val people = RDB.Relation(
- "People",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "fname" -> RDB.Datatype.STRING,
- "addr" -> RDB.Datatype.INTEGER,
- "deptName" -> RDB.Datatype.STRING,
- "deptCity" -> RDB.Datatype.STRING),
- List(RDB.Tuple("ID" -> RDB.LexicalValue("7"),
- "fname" -> RDB.LexicalValue("Bob"),
- "addr" -> RDB.LexicalValue("18"),
- "deptName" -> RDB.LexicalValue("accounting"),
- "deptCity" -> RDB.LexicalValue("Cambridge")),
- RDB.Tuple("ID" -> RDB.LexicalValue("8"),
- "fname" -> RDB.LexicalValue("Sue"),
- "addr" -> RDB.␀,
- "deptName" -> RDB.␀,
- "deptCity" -> RDB.␀)),
- List(RDB.CandidateKey("ID")),
- Some(RDB.CandidateKey("ID")),
- RDB.ForeignKeys(List("addr") -> RDB.Target("Addresses", RDB.CandidateKey("ID")),
- List("deptName", "deptCity") -> RDB.Target("Department", RDB.CandidateKey("name", "city"))))
-
- val department = RDB.Relation(
- "Department",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "name" -> RDB.Datatype.STRING,
- "city" -> RDB.Datatype.STRING,
- "manager" -> RDB.Datatype.INTEGER),
- List(RDB.Tuple("ID" -> RDB.LexicalValue("23"),
- "name" -> RDB.LexicalValue("accounting"),
- "city" -> RDB.LexicalValue("Cambridge"),
- "manager" -> RDB.LexicalValue("8"))),
- List(RDB.CandidateKey("ID"),
- RDB.CandidateKey("name", "city")),
- Some(RDB.CandidateKey("ID")),
- RDB.ForeignKeys(List("manager") -> RDB.Target("People", RDB.CandidateKey("ID"))))
-
- val db = RDB.Database(List(addrs, people, department))
-
- val expected = RDB.Database(List(addrs, people, department))
- assert(expected === (p.parseAll(p.ddl, s).get))
- }
-
-
- test("parse 2 People 1 Addresses 1 Department 2 Projects 1 Task") {
- val p = SqlParser()
- val s = """
-CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
-INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");
-CREATE TABLE Department (ID INT PRIMARY KEY, name STRING, city STRING, manager INT,
- FOREIGN KEY (manager) REFERENCES People(ID),
- UNIQUE (name, city));
-INSERT INTO Department (ID, name, city, manager) VALUES (23, "accounting", "Cambridge", 8);
-CREATE TABLE People (ID INT PRIMARY KEY, fname STRING, addr INT,
- FOREIGN KEY (addr) REFERENCES Addresses(ID),
- deptName STRING, deptCity STRING,
- FOREIGN KEY (deptName, deptCity) REFERENCES Department(name, city));
-INSERT INTO People (ID, fname, addr, deptName, deptCity) VALUES (7, "Bob", 18, "accounting", "Cambridge");
-INSERT INTO People (ID, fname, addr, deptName, deptCity) VALUES (8, "Sue", NULL, NULL, NULL);
-CREATE TABLE Projects (lead INT,
- FOREIGN KEY (lead) REFERENCES People(ID),
- name STRING, UNIQUE (lead, name),
- deptName STRING, deptCity STRING,
- UNIQUE (name, deptName, deptCity),
- FOREIGN KEY (deptName, deptCity) REFERENCES Department(name, city));
-INSERT INTO Projects (lead, name, deptName, deptCity) VALUES (8, "pencil survey", "accounting", "Cambridge");
-INSERT INTO Projects (lead, name, deptName, deptCity) VALUES (8, "eraser survey", "accounting", "Cambridge");
-CREATE TABLE TaskAssignments (worker INT,
- FOREIGN KEY (worker) REFERENCES People(ID),
- project STRING, PRIMARY KEY (worker, project),
- deptName STRING, deptCity STRING,
- FOREIGN KEY (project, deptName, deptCity) REFERENCES Projects(name, deptName, deptCity),
- FOREIGN KEY (deptName, deptCity) REFERENCES Department(name, city));
-INSERT INTO TaskAssignments (worker, project, deptName, deptCity) VALUES (7, "pencil survey", "accounting", "Cambridge");
-"""
-
- val addrs = RDB.Relation(
- "Addresses",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "city" -> RDB.Datatype.STRING,
- "state" -> RDB.Datatype.STRING),
- List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
- "city" -> RDB.LexicalValue("Cambridge"),
- "state" -> RDB.LexicalValue("MA"))),
- List(RDB.CandidateKey("ID")),
- Some(RDB.CandidateKey("ID")),
- RDB.ForeignKeys())
-
- val people = RDB.Relation(
- "People",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "fname" -> RDB.Datatype.STRING,
- "addr" -> RDB.Datatype.INTEGER,
- "deptName" -> RDB.Datatype.STRING,
- "deptCity" -> RDB.Datatype.STRING),
- List(RDB.Tuple("ID" -> RDB.LexicalValue("7"),
- "fname" -> RDB.LexicalValue("Bob"),
- "addr" -> RDB.LexicalValue("18"),
- "deptName" -> RDB.LexicalValue("accounting"),
- "deptCity" -> RDB.LexicalValue("Cambridge")),
- RDB.Tuple("ID" -> RDB.LexicalValue("8"),
- "fname" -> RDB.LexicalValue("Sue"),
- "addr" -> RDB.␀,
- "deptName" -> RDB.␀,
- "deptCity" -> RDB.␀)),
- List(RDB.CandidateKey("ID")),
- Some(RDB.CandidateKey("ID")),
- RDB.ForeignKeys(List("addr") -> RDB.Target("Addresses", RDB.CandidateKey("ID")),
- List("deptName", "deptCity") -> RDB.Target("Department", RDB.CandidateKey("name", "city"))))
-
- val department = RDB.Relation(
- "Department",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "name" -> RDB.Datatype.STRING,
- "city" -> RDB.Datatype.STRING,
- "manager" -> RDB.Datatype.INTEGER),
- List(RDB.Tuple("ID" -> RDB.LexicalValue("23"),
- "name" -> RDB.LexicalValue("accounting"),
- "city" -> RDB.LexicalValue("Cambridge"),
- "manager" -> RDB.LexicalValue("8"))),
- List(RDB.CandidateKey("ID"),
- RDB.CandidateKey("name", "city")),
- Some(RDB.CandidateKey("ID")),
- RDB.ForeignKeys(List("manager") -> RDB.Target("People", RDB.CandidateKey("ID"))))
-
- val projects = RDB.Relation(
- "Projects",
- RDB.Header("lead" -> RDB.Datatype.INTEGER,
- "name" -> RDB.Datatype.STRING,
- "deptName" -> RDB.Datatype.STRING,
- "deptCity" -> RDB.Datatype.STRING),
- List(RDB.Tuple("lead" -> RDB.LexicalValue("8"),
- "name" -> RDB.LexicalValue("pencil survey"),
- "deptName" -> RDB.LexicalValue("accounting"),
- "deptCity" -> RDB.LexicalValue("Cambridge")),
- RDB.Tuple("lead" -> RDB.LexicalValue("8"),
- "name" -> RDB.LexicalValue("eraser survey"),
- "deptName" -> RDB.LexicalValue("accounting"),
- "deptCity" -> RDB.LexicalValue("Cambridge"))),
- List(RDB.CandidateKey("lead", "name"),
- RDB.CandidateKey("name", "deptName", "deptCity")),
- None,
- /* List(List("name"), List("lead", "name"), List("name", "deptName", "deptCity")),
- List("name"), // !!! */
- RDB.ForeignKeys(List("lead") -> RDB.Target("People", RDB.CandidateKey("ID")),
- List("deptName", "deptCity") -> RDB.Target("Department", RDB.CandidateKey("name", "city"))))
-
- val tasks = RDB.Relation(
- "TaskAssignments",
- RDB.Header("worker" -> RDB.Datatype.INTEGER,
- "project" -> RDB.Datatype.STRING,
- "deptName" -> RDB.Datatype.STRING,
- "deptCity" -> RDB.Datatype.STRING),
- List(RDB.Tuple("worker" -> RDB.LexicalValue("7"),
- "project" -> RDB.LexicalValue("pencil survey"),
- "deptName" -> RDB.LexicalValue("accounting"),
- "deptCity" -> RDB.LexicalValue("Cambridge"))),
- List(RDB.CandidateKey("worker", "project")),
- Some(RDB.CandidateKey("worker", "project")),
- RDB.ForeignKeys(List("worker") -> RDB.Target("People", RDB.CandidateKey("ID")),
- List("project", "deptName", "deptCity") -> RDB.Target("Projects", RDB.CandidateKey("name", "deptName", "deptCity")),
- List("deptName", "deptCity") -> RDB.Target("Department", RDB.CandidateKey("name", "city"))))
- val db = RDB.Database(List(addrs, people, department, projects, tasks))
-
- val expected = RDB.Database(List(addrs, people, department, projects, tasks))
- assert(expected === (p.parseAll(p.ddl, s).get))
- }
-
-
- test("parse 1 People 1 Addresses 1 Offices") {
- val p = SqlParser()
- val s = """
-CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
-INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");
-CREATE TABLE People (ID INT PRIMARY KEY, fname STRING, addr INT,
- FOREIGN KEY (addr) REFERENCES Addresses(ID));
-INSERT INTO People (ID, fname, addr) VALUES (7, "Bob", 18);
-CREATE TABLE Offices (ID INT PRIMARY KEY,
- building INT, ofcNumber STRING,
- FOREIGN KEY (ID) REFERENCES Addresses(ID));
-INSERT INTO Offices (ID, building, ofcNumber) VALUES (18, 32, "G528");
-"""
-
- val addrs = RDB.Relation(
- "Addresses",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "city" -> RDB.Datatype.STRING,
- "state" -> RDB.Datatype.STRING),
- List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
- "city" -> RDB.LexicalValue("Cambridge"),
- "state" -> RDB.LexicalValue("MA"))),
- List(RDB.CandidateKey("ID")),
- Some(RDB.CandidateKey("ID")),
- RDB.ForeignKeys())
-
- val people = RDB.Relation(
- "People",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "fname" -> RDB.Datatype.STRING,
- "addr" -> RDB.Datatype.INTEGER),
- List(RDB.Tuple("ID" -> RDB.LexicalValue("7"),
- "fname" -> RDB.LexicalValue("Bob"),
- "addr" -> RDB.LexicalValue("18"))),
- List(RDB.CandidateKey("ID")),
- Some(RDB.CandidateKey("ID")),
- RDB.ForeignKeys(List("addr") -> RDB.Target("Addresses", RDB.CandidateKey("ID"))))
-
- val offices = RDB.Relation(
- "Offices",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "building" -> RDB.Datatype.INTEGER,
- "ofcNumber" -> RDB.Datatype.STRING),
- List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
- "building" -> RDB.LexicalValue("32"),
- "ofcNumber" -> RDB.LexicalValue("G528"))),
- List(RDB.CandidateKey("ID")),
- Some(RDB.CandidateKey("ID")),
- RDB.ForeignKeys(List("ID") -> RDB.Target("Addresses", RDB.CandidateKey("ID"))))
-
- val expected = RDB.Database(List(addrs, people, offices))
- assert(expected === (p.parseAll(p.ddl, s).get))
- }
-
-
- test("parse 1 People 1 Addresses 1 Offices 1 ExectutiveOffices") {
- val p = SqlParser()
- val s = """
-CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
-INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");
-CREATE TABLE People (ID INT PRIMARY KEY, fname STRING, addr INT,
- FOREIGN KEY (addr) REFERENCES Addresses(ID));
-INSERT INTO People (ID, fname, addr) VALUES (7, "Bob", 18);
-CREATE TABLE Offices (ID INT PRIMARY KEY,
- building INT, ofcNumber STRING,
- FOREIGN KEY (ID) REFERENCES Addresses(ID));
-INSERT INTO Offices (ID, building, ofcNumber) VALUES (18, 32, "G528");
-CREATE TABLE ExecutiveOffices (ID INT PRIMARY KEY,
- desk STRING,
- FOREIGN KEY (ID) REFERENCES Offices(ID));
-INSERT INTO ExecutiveOffices (ID, desk) VALUES (18, "oak");
-"""
-
- val addrs = RDB.Relation(
- "Addresses",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "city" -> RDB.Datatype.STRING,
- "state" -> RDB.Datatype.STRING),
- List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
- "city" -> RDB.LexicalValue("Cambridge"),
- "state" -> RDB.LexicalValue("MA"))),
- List(RDB.CandidateKey("ID")),
- Some(RDB.CandidateKey("ID")),
- RDB.ForeignKeys())
-
- val people = RDB.Relation(
- "People",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "fname" -> RDB.Datatype.STRING,
- "addr" -> RDB.Datatype.INTEGER),
- List(RDB.Tuple("ID" -> RDB.LexicalValue("7"),
- "fname" -> RDB.LexicalValue("Bob"),
- "addr" -> RDB.LexicalValue("18"))),
- List(RDB.CandidateKey("ID")),
- Some(RDB.CandidateKey("ID")),
- RDB.ForeignKeys(List("addr") -> RDB.Target("Addresses", RDB.CandidateKey("ID"))))
-
- val offices = RDB.Relation(
- "Offices",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "building" -> RDB.Datatype.INTEGER,
- "ofcNumber" -> RDB.Datatype.STRING),
- List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
- "building" -> RDB.LexicalValue("32"),
- "ofcNumber" -> RDB.LexicalValue("G528"))),
- List(RDB.CandidateKey("ID")),
- Some(RDB.CandidateKey("ID")),
- RDB.ForeignKeys(List("ID") -> RDB.Target("Addresses", RDB.CandidateKey("ID"))))
-
- val execoffices = RDB.Relation(
- "ExecutiveOffices",
- RDB.Header("ID" -> RDB.Datatype.INTEGER,
- "desk" -> RDB.Datatype.STRING),
- List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
- "desk" -> RDB.LexicalValue("oak"))),
- List(RDB.CandidateKey("ID")),
- Some(RDB.CandidateKey("ID")),
- RDB.ForeignKeys(List("ID") -> RDB.Target("Offices", RDB.CandidateKey("ID"))))
- val expected = RDB.Database(List(addrs, people, offices, execoffices))
- assert(expected === (p.parseAll(p.ddl, s).get))
- }
-
-
- test("case sensitivity") {
- val p1 = SqlParser()
- val upper = """
-CREATE TABLE Tab (ID INT PRIMARY KEY, s STRING, c2 CHAR(2), f FLOAT, r INT, FOREIGN KEY (r) REFERENCES Tab(ID));
-INSERT INTO Tab (ID, s, c2, f, r) VALUES (1, "s", "ME", 1.0, 1);
-"""
-
- val p2 = SqlParser()
- val lower = """
-create table Tab (ID int primary key, s string, c2 char(2), f float, r int, foreign key (r) references Tab(ID));
-insert into Tab (ID, s, c2, f, r) values (1, "s", "ME", 1.0, 1);
-"""
- assert((p1.parseAll(p1.ddl, upper).get) === (p2.parseAll(p2.ddl, lower).get))
- }
-
-
- /**
- * junk that should be elsewhere
- */
-
- implicit def relname2relresource (rn:RDB.RelName) : RelationResource = RelationResource(rn)
-
-}