--- a/src/main/scala/SQL.scala Mon Dec 28 21:53:32 2009 -0500
+++ b/src/main/scala/SQL.scala Tue Dec 29 17:26:31 2009 -0500
@@ -3,57 +3,64 @@
import scala.util.parsing.combinator._
import java.net.URI
+case class Union(disjoints:Set[Select]) {
+ // override def toString = (disjoints mkString ("\nUNION\n"))
+}
case class Select(attributelist:AttributeList, tablelist:TableList, expression:Expression) {
- override def toString = attributelist+"\n"+tablelist+"\n"+expression
+ // override def toString = attributelist+"\n"+tablelist+"\n"+expression
}
case class AttributeList(attributes:Set[NamedAttribute]) {
// foo, bar
- override def toString = "SELECT "+(attributes mkString (",\n "))
+ // override def toString = "SELECT "+(attributes mkString (",\n "))
}
case class NamedAttribute(fqattribute:RelAliasAttribute, attralias:AttrAlias) {
- override def toString = fqattribute + " AS " + attralias
+ // override def toString = fqattribute + " AS " + attralias
}
//case class RelAttribute(relation:Relation, attribute:Attribute) c.f. ForeignKey
case class RelAliasAttribute(relalias:RelAlias, attribute:Attribute) {
- override def toString = relalias + "." + attribute
+ // override def toString = relalias + "." + attribute
}
case class Attribute(n:Name) {
- override def toString = n.s /* "'" + n.s + "'" */
+ // override def toString = n.s /* "'" + n.s + "'" */
}
case class AttrAlias(n:Name) {
- override def toString = n.s /* "'" + n.s + "'" */
+ // override def toString = n.s /* "'" + n.s + "'" */
}
-case class Relation(n:Name) {
- override def toString = n.s /* "'" + n.s + "'" */
+sealed abstract class RelationORSubselect
+case class Relation(n:Name) extends RelationORSubselect {
+ // override def toString = n.s /* "'" + n.s + "'" */
+}
+case class Subselect(union:Union) extends RelationORSubselect {
+ // override def toString = "(\n" + union + "\n)"
}
case class RelAlias(n:Name) {
- override def toString = n.s /* "'" + n.s + "'" */
+ // override def toString = n.s /* "'" + n.s + "'" */
}
case class TableList(joins:Set[AliasedResource]) {
- override def toString = " FROM " + (joins mkString ("\n INNER JOIN "))
+ // override def toString = " FROM " + (joins mkString ("\n INNER JOIN "))
}
-case class AliasedResource(rel:Relation, as:RelAlias) {
- override def toString = rel + " AS " + as
+case class AliasedResource(rel:RelationORSubselect, as:RelAlias) {
+ // override def toString = rel + " AS " + as
}
case class Expression(conjuncts:Set[PrimaryExpression]) {
- override def toString = " WHERE " + (conjuncts mkString ("\n AND "))
+ // override def toString = " WHERE " + (conjuncts mkString ("\n AND "))
}
sealed abstract class PrimaryExpression
case class PrimaryExpressionEq(l:RelAliasAttribute, r:RValue) extends PrimaryExpression {
- override def toString = l + "=" + r
+ // override def toString = l + "=" + r
}
case class PrimaryExpressionLt(l:RelAliasAttribute, r:RValue) extends PrimaryExpression {
- override def toString = l + "<" + r
+ // override def toString = l + "<" + r
}
case class PrimaryExpressionNotNull(l:RelAliasAttribute) extends PrimaryExpression {
- override def toString = l + " IS NOT NULL"
+ // override def toString = l + " IS NOT NULL"
}
sealed abstract class RValue
case class RValueAttr(fqattribute:RelAliasAttribute) extends RValue {
- override def toString = "" + fqattribute
+ // override def toString = "" + fqattribute
}
case class RValueTyped(datatype:SQLDatatype, i:Name) extends RValue {
- override def toString = i.s /* "'" + i.s + "'" */ /* + datatype */
+ // override def toString = i.s /* "'" + i.s + "'" */ /* + datatype */
}
case class Name(s:String)
@@ -62,7 +69,7 @@
}
case class SQLDatatype(name:String) {
- override def toString = "/* " + name + " */"
+ // override def toString = "/* " + name + " */"
}
object SQLDatatype {
val STRING = SQLDatatype("String")
@@ -78,6 +85,9 @@
case class Sql() extends JavaTokenParsers {
+ def union:Parser[Union] =
+ repsep(select, "UNION") ^^ { l => Union(l.toSet) }
+
def select:Parser[Select] =
"SELECT" ~ attributelist ~ "FROM" ~ tablelist ~ opt(where) ^^
{
@@ -111,8 +121,10 @@
def attralias:Parser[AttrAlias] =
"""[a-zA-Z_]\w*""".r ^^ { x => AttrAlias(Name(x)) }
- def relation:Parser[Relation] =
- """[a-zA-Z_]\w*""".r ^^ { x => Relation(Name(x)) }
+ def relationORsubselect:Parser[RelationORSubselect] = (
+ """[a-zA-Z_]\w*""".r ^^ { x => Relation(Name(x)) }
+ | "(" ~ union ~ ")" ^^ { case "("~s~")" => Subselect(s) }
+ )
def relalias:Parser[RelAlias] =
"""[a-zA-Z_]\w*""".r ^^ { x => RelAlias(Name(x)) }
@@ -121,7 +133,7 @@
repsep(aliasedjoin, "INNER" ~ "JOIN") ^^ { m => TableList(m.toSet) }
def aliasedjoin:Parser[AliasedResource] =
- relation ~ "AS" ~ relalias ^^
+ relationORsubselect ~ "AS" ~ relalias ^^
{ case rel1 ~ "AS" ~ rel2 => AliasedResource(rel1, rel2) }
def expression:Parser[Expression] =
--- a/src/test/scala/SQLTest.scala Mon Dec 28 21:53:32 2009 -0500
+++ b/src/test/scala/SQLTest.scala Tue Dec 29 17:26:31 2009 -0500
@@ -6,6 +6,7 @@
class SQLTest extends FunSuite {
test("parse SQLbgp") {
+ // AliasedResource(Relation(Name("Employee")),RelAlias(Name("R_emp")))
val a = Sql()
val e = """
SELECT R_emp.lastName AS A_empName, R_manager.lastName AS A_managName
@@ -83,30 +84,81 @@
AND R_emp.lastName IS NOT NULL AND R_grandManager.lastName IS NOT NULL
"""
val expected = Select(AttributeList(Set(NamedAttribute(RelAliasAttribute(RelAlias(Name("R_emp")), Attribute(Name("lastName"))),
- AttrAlias(Name("A_empName"))),
- NamedAttribute(RelAliasAttribute(RelAlias(Name("R_grandManager")),Attribute(Name("lastName"))),
- AttrAlias(Name("A_grandManagName"))))),
+ AttrAlias(Name("A_empName"))),
+ NamedAttribute(RelAliasAttribute(RelAlias(Name("R_grandManager")),Attribute(Name("lastName"))),
+ AttrAlias(Name("A_grandManagName"))))),
TableList(Set(AliasedResource(Relation(Name("Employee")),RelAlias(Name("R_emp"))),
- AliasedResource(Relation(Name("Manage")),RelAlias(Name("R_lower"))),
- AliasedResource(Relation(Name("Employee")),RelAlias(Name("R_manager"))),
- AliasedResource(Relation(Name("Manage")),RelAlias(Name("R_upper"))),
- AliasedResource(Relation(Name("Employee")),RelAlias(Name("R_grandManager"))))),
+ AliasedResource(Relation(Name("Manage")),RelAlias(Name("R_lower"))),
+ AliasedResource(Relation(Name("Employee")),RelAlias(Name("R_manager"))),
+ AliasedResource(Relation(Name("Manage")),RelAlias(Name("R_upper"))),
+ AliasedResource(Relation(Name("Employee")),RelAlias(Name("R_grandManager"))))),
Expression(Set(PrimaryExpressionEq(RelAliasAttribute(RelAlias(Name("R_lower")),Attribute(Name("manages"))),
- RValueAttr(RelAliasAttribute(RelAlias(Name("R_emp")),Attribute(Name("id"))))),
- PrimaryExpressionEq(RelAliasAttribute(RelAlias(Name("R_manager")),Attribute(Name("id"))),
- RValueAttr(RelAliasAttribute(RelAlias(Name("R_lower")),Attribute(Name("manager"))))),
- PrimaryExpressionLt(RelAliasAttribute(RelAlias(Name("R_manager")),Attribute(Name("birthday"))),
- RValueAttr(RelAliasAttribute(RelAlias(Name("R_emp")),Attribute(Name("birthday"))))),
- PrimaryExpressionEq(RelAliasAttribute(RelAlias(Name("R_upper")),Attribute(Name("manages"))),
- RValueAttr(RelAliasAttribute(RelAlias(Name("R_manager")),Attribute(Name("id"))))),
- PrimaryExpressionEq(RelAliasAttribute(RelAlias(Name("R_grandManager")),Attribute(Name("id"))),
- RValueAttr(RelAliasAttribute(RelAlias(Name("R_upper")),Attribute(Name("manager"))))),
- PrimaryExpressionLt(RelAliasAttribute(RelAlias(Name("R_grandManager")),Attribute(Name("birthday"))),
- RValueAttr(RelAliasAttribute(RelAlias(Name("R_manager")),Attribute(Name("birthday"))))),
- PrimaryExpressionNotNull(RelAliasAttribute(RelAlias(Name("R_emp")),Attribute(Name("lastName")))),
- PrimaryExpressionNotNull(RelAliasAttribute(RelAlias(Name("R_grandManager")),Attribute(Name("lastName")))))))
+ RValueAttr(RelAliasAttribute(RelAlias(Name("R_emp")),Attribute(Name("id"))))),
+ PrimaryExpressionEq(RelAliasAttribute(RelAlias(Name("R_manager")),Attribute(Name("id"))),
+ RValueAttr(RelAliasAttribute(RelAlias(Name("R_lower")),Attribute(Name("manager"))))),
+ PrimaryExpressionLt(RelAliasAttribute(RelAlias(Name("R_manager")),Attribute(Name("birthday"))),
+ RValueAttr(RelAliasAttribute(RelAlias(Name("R_emp")),Attribute(Name("birthday"))))),
+ PrimaryExpressionEq(RelAliasAttribute(RelAlias(Name("R_upper")),Attribute(Name("manages"))),
+ RValueAttr(RelAliasAttribute(RelAlias(Name("R_manager")),Attribute(Name("id"))))),
+ PrimaryExpressionEq(RelAliasAttribute(RelAlias(Name("R_grandManager")),Attribute(Name("id"))),
+ RValueAttr(RelAliasAttribute(RelAlias(Name("R_upper")),Attribute(Name("manager"))))),
+ PrimaryExpressionLt(RelAliasAttribute(RelAlias(Name("R_grandManager")),Attribute(Name("birthday"))),
+ RValueAttr(RelAliasAttribute(RelAlias(Name("R_manager")),Attribute(Name("birthday"))))),
+ PrimaryExpressionNotNull(RelAliasAttribute(RelAlias(Name("R_emp")),Attribute(Name("lastName")))),
+ PrimaryExpressionNotNull(RelAliasAttribute(RelAlias(Name("R_grandManager")),Attribute(Name("lastName")))))))
assert(expected === (a.parseAll(a.select, e).get))
}
+ test("parse disj1") {
+ val a = Sql()
+ 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(AttributeList(Set(NamedAttribute(RelAliasAttribute(RelAlias(Name("R_union1")), Attribute(Name("name"))),
+ AttrAlias(Name("A_name"))))),
+ TableList(Set(AliasedResource(Relation(Name("Employee")),RelAlias(Name("R_who"))),
+ AliasedResource(Subselect(Union(Set(
+ Select(AttributeList(Set(NamedAttribute(RelAliasAttribute(RelAlias(Name("R_manager")), Attribute(Name("lastName"))),
+ AttrAlias(Name("A_name"))),
+ NamedAttribute(RelAliasAttribute(RelAlias(Name("R_above")), Attribute(Name("manages"))),
+ AttrAlias(Name("A_who"))))),
+ TableList(Set(
+ AliasedResource(Relation(Name("Manage")),RelAlias(Name("R_above"))),
+ AliasedResource(Relation(Name("Employee")),RelAlias(Name("R_manager")))
+ )),
+ Expression(Set(PrimaryExpressionEq(RelAliasAttribute(RelAlias(Name("R_above")),Attribute(Name("manager"))),
+ RValueAttr(RelAliasAttribute(RelAlias(Name("R_manager")),Attribute(Name("id"))))),
+ PrimaryExpressionNotNull(RelAliasAttribute(RelAlias(Name("R_manager")),Attribute(Name("lastName"))))))),
+ Select(AttributeList(Set(NamedAttribute(RelAliasAttribute(RelAlias(Name("R_managed")), Attribute(Name("lastName"))),
+ AttrAlias(Name("A_name"))),
+ NamedAttribute(RelAliasAttribute(RelAlias(Name("R_below")), Attribute(Name("manager"))),
+ AttrAlias(Name("A_who"))))),
+ TableList(Set(
+ AliasedResource(Relation(Name("Manage")),RelAlias(Name("R_below"))),
+ AliasedResource(Relation(Name("Employee")),RelAlias(Name("R_managed")))
+ )),
+ Expression(Set(PrimaryExpressionEq(RelAliasAttribute(RelAlias(Name("R_below")),Attribute(Name("manages"))),
+ RValueAttr(RelAliasAttribute(RelAlias(Name("R_managed")),Attribute(Name("id"))))),
+ PrimaryExpressionNotNull(RelAliasAttribute(RelAlias(Name("R_managed")),Attribute(Name("lastName")))))))))),
+ RelAlias(Name("R_union1"))))),
+ Expression(Set(PrimaryExpressionEq(RelAliasAttribute(RelAlias(Name("R_union1")),Attribute(Name("A_who"))),
+ RValueAttr(RelAliasAttribute(RelAlias(Name("R_who")),Attribute(Name("id"))))),
+ PrimaryExpressionEq(RelAliasAttribute(RelAlias(Name("R_who")),Attribute(Name("lastName"))),
+ RValueTyped(SQLDatatype.STRING,Name("Smith"))))))
+ assert(expected === (a.parseAll(a.select, e).get))
+ }
}