--- a/src/main/scala/SQL.scala Sun Feb 14 12:49:28 2010 -0500
+++ b/src/main/scala/SQL.scala Fri Feb 26 19:17:02 2010 -0500
@@ -16,45 +16,45 @@
sealed abstract class RelationORSubselect
case class Subselect(sel:SelectORUnion) extends RelationORSubselect {
- override def toString = "(\n " + sel.toString.replace("\n", "\n ") + "\n )"
+ def toString999 = "(\n " + sel.toString.replace("\n", "\n ") + "\n )"
}
sealed abstract class SelectORUnion
case class Select(attributelist:AttributeList, tablelist:TableList, expression:Option[Expression]) extends SelectORUnion {
- override def toString = expression match {
+ def toString999 = expression match {
case Some(expr) => attributelist+"\n"+tablelist+"\n WHERE "+expr
case None => attributelist+"\n"+tablelist
}
}
case class Relation(n:Name) extends RelationORSubselect {
- override def toString = n.s /* "'" + n.s + "'" */
+ def toString999 = n.s /* "'" + n.s + "'" */
}
case class Union(disjoints:Set[Select]) extends SelectORUnion {
- override def toString = " " + (disjoints.toList.map(s => s.toString.replace("\n", "\n ")).mkString("\nUNION\n "))
+ def toString999 = " " + (disjoints.toList.map(s => s.toString.replace("\n", "\n ")).mkString("\nUNION\n "))
}
case class AttributeList(attributes:Set[NamedAttribute]) {
// foo, bar
- override def toString = "SELECT "+(attributes.toList.sortWith((l, r) => l.attralias.toString < r.attralias.toString).mkString(", "))
+ def toString999 = "SELECT "+(attributes.toList.sortWith((l, r) => l.attralias.toString < r.attralias.toString).mkString(", "))
}
case class NamedAttribute(value:RelVarAttrORExpression, attralias:AttrAlias) {
- override def toString = value + " AS " + attralias
+ def toString999 = value + " AS " + attralias
}
//case class RelAttribute(relation:Relation, attribute:Attribute) c.f. ForeignKey
sealed abstract class RelVarAttrORExpression
case class RelVarAttr(relvar:RelVar, attribute:Attribute) extends RelVarAttrORExpression {
- override def toString = relvar + "." + attribute
+ def toString999 = relvar + "." + attribute
}
case class Attribute(n:Name) {
- override def toString = n.s /* "'" + n.s + "'" */
+ def toString999 = n.s /* "'" + n.s + "'" */
}
case class AttrAlias(n:Name) {
- override def toString = n.s /* "'" + n.s + "'" */
+ def toString999 = n.s /* "'" + n.s + "'" */
}
case class RelVar(n:Name) {
- override def toString = n.s /* "'" + n.s + "'" */
+ def toString999 = n.s /* "'" + n.s + "'" */
}
case class TableList(joins:AddOrderedSet[Join]) {
- override def toString =
+ def toString999 =
if (joins.size == 0) ""
else {
" FROM " + joins.foldLeft(("", 0))(
@@ -67,25 +67,25 @@
sealed abstract class Join(res:AliasedResource)
case class InnerJoin(res:AliasedResource, optOn:Option[Expression]) extends Join(res) {
- override def toString = "\n INNER JOIN " + res
+ def toString999 = "\n INNER JOIN " + res
}
case class LeftOuterJoin(res:AliasedResource, on:Expression) extends Join(res) {
- override def toString = "\n LEFT OUTER JOIN " + res + " ON " + on
+ def toString999 = "\n LEFT OUTER JOIN " + res + " ON " + on
}
case class AliasedResource(rel:RelationORSubselect, as:RelVar) {
- override def toString = rel + " AS " + as
+ def toString999 = rel + " AS " + as
}
sealed abstract class Expression extends RelVarAttrORExpression
case class ExprConjunction(exprs:Set[Expression]) extends Expression {
- override def toString = "(" + (exprs.toList.sortWith((l, r) => l.toString < r.toString).mkString (")\n AND (")) + ")"
+ def toString999 = "(" + (exprs.toList.sortWith((l, r) => l.toString < r.toString).mkString (")\n AND (")) + ")"
}
case class ExprDisjunction(exprs:Set[Expression]) extends Expression {
- override def toString = "(" + (exprs mkString (") OR (")) + ")"
+ def toString999 = "(" + (exprs mkString (") OR (")) + ")"
}
sealed abstract class RelationalExpression extends Expression
case class RelationalExpressionEq(l:Expression, r:Expression) extends RelationalExpression {
- override def toString = l + "=" + r
+ def toString999 = l + "=" + r
/* safer operator== , but doesn't quite work yet. */
// override def hashCode = 41 * l.hashCode + r.hashCode
// override def equals(other: Any) = other match {
@@ -111,36 +111,36 @@
l.hashCode + r.hashCode
}
case class RelationalExpressionNe(l:Expression, r:Expression) extends RelationalExpression {
- override def toString = l + "!=" + r
+ def toString999 = l + "!=" + r
}
case class RelationalExpressionLt(l:Expression, r:Expression) extends RelationalExpression {
- override def toString = l + "<" + r
+ def toString999 = l + "<" + r
}
case class RelationalExpressionNull(l:Expression) extends RelationalExpression { // Expression?
- override def toString = l + " IS NULL"
+ def toString999 = l + " IS NULL"
}
case class RelationalExpressionNotNull(l:Expression) extends RelationalExpression { // Expression?
- override def toString = l + " IS NOT NULL"
+ def toString999 = l + " IS NOT NULL"
}
sealed abstract class PrimaryExpression extends Expression
case class PrimaryExpressionAttr(fqattribute:RelVarAttr) extends PrimaryExpression {
- override def toString = "" + fqattribute
+ def toString999 = "" + fqattribute
}
case class PrimaryExpressionTyped(datatype:Datatype, i:Name) extends PrimaryExpression {
- override def toString = /* "'" + i.s + "'" */ /* + datatype */
+ def toString999 = /* "'" + i.s + "'" */ /* + datatype */
datatype match {
case Datatype("Int") => i.s
case _ => "\"" + i.s + "\""
}
}
case class ConstNULL() extends PrimaryExpression {
- override def toString = "NULL"
+ def toString999 = "NULL"
}
case class Concat(args:List[Expression]) extends PrimaryExpression {
- override def toString = args.mkString("CONCAT(", ", ", ")")
+ def toString999 = args.mkString("CONCAT(", ", ", ")")
}
case class IfElse(cond:Expression, pass:Expression, fail:Expression) extends PrimaryExpression {
- override def toString = "CONCAT(" + cond + ", " + pass + ", " + fail + ")"
+ def toString999 = "CONCAT(" + cond + ", " + pass + ", " + fail + ")"
}
case class Name(s:String)
@@ -150,7 +150,7 @@
}
case class Datatype(name:String) {
- override def toString = "/* " + name + " */"
+ def toString999 = "/* " + name + " */"
}
object Datatype {
val STRING = Datatype("String")
@@ -164,9 +164,69 @@
case class DatabaseDesc(relationdescs:Map[Relation,RelationDesc])
case class RelationDesc(primarykey:Option[Attribute], attributes:Map[Attribute, ValueDescription])
+sealed abstract class FieldDescOrKeyDeclaration
+case class FieldDesc(attr:Attribute, value:Value, pkness:Boolean) extends FieldDescOrKeyDeclaration
+sealed abstract class KeyDeclaration extends FieldDescOrKeyDeclaration
+case class PrimaryKeyDeclaration(attr:Attribute) extends KeyDeclaration
+case class ForeignKeyDeclaration(fk:Attribute, rel:Relation, pk:Attribute) extends KeyDeclaration
case class Sql() extends JavaTokenParsers {
+ def ddl:Parser[DatabaseDesc] =
+ rep1sep(create, ";") ~ opt(";") ^^
+ {
+ case l~x => DatabaseDesc(l.foldLeft(Map[Relation, RelationDesc]())((m, p) => {
+ val (rel:Relation, desc:RelationDesc) = p
+ m + (rel -> desc)
+ }))
+ }
+
+ def create:Parser[(Relation, RelationDesc)] =
+ "CREATE" ~ "TABLE" ~ relation ~ "(" ~ rep1sep(fielddescorkeydef, ",") ~ ")" ^^
+ {
+ case "CREATE"~"TABLE"~relation~"("~reldesc~")" => {
+ val pk0:Option[Attribute] = None
+ val attrs0 = Map[Attribute, ValueDescription]()
+ val fks0 = Map[Attribute, ForeignKey]()
+ val (pk, attrs, fks) =
+ reldesc.foldLeft((pk0, attrs0, fks0))((p, rd) => {
+ val (pkopt, attrs, fks) = p
+ rd match {
+ case FieldDesc(attr, value, pkness) => {
+ val pkNew =
+ if (pkness) Some(attr)
+ else pkopt
+ (pkNew, attrs + (attr -> value), fks)
+ }
+ case PrimaryKeyDeclaration(attr) => (Some(attr), attrs, fks)
+ case ForeignKeyDeclaration(fk, rel, pk) => (pkopt, attrs, fks + (fk -> ForeignKey(rel, pk)))
+ }
+ })
+ val attrs2 = attrs.map(x => {
+ val (attr:Attribute, value:Value) = x
+ if (fks.contains(attr)) (attr -> fks(attr))
+ else (attr -> value)
+ })
+ val rd = RelationDesc(pk, attrs2)
+ (relation -> rd)
+ }
+ }
+
+ def fielddescorkeydef:Parser[FieldDescOrKeyDeclaration] = (
+ attribute ~ typpe ~ opt("PRIMARY" ~ "KEY") ^^
+ { case attribute~typpe~pkness => FieldDesc(attribute, typpe, pkness.isDefined) }
+ | "PRIMARY" ~ "KEY" ~ "(" ~ attribute ~ ")" ^^
+ { case "PRIMARY"~"KEY"~"("~attribute~")" => PrimaryKeyDeclaration(attribute) }
+ | "FOREIGN" ~ "KEY" ~ "(" ~ attribute ~ ")" ~ "REFERENCES" ~ relation ~ "(" ~ attribute ~ ")" ^^
+ { case "FOREIGN"~"KEY"~"("~fk~")"~"REFERENCES"~relation~"("~pk~")" => ForeignKeyDeclaration(fk, relation, pk) }
+ )
+
+ def typpe:Parser[Value] = (
+ "INT" ^^ { case _ => Value(Datatype.INTEGER) }
+ | "STRING" ^^ { case _ => Value(Datatype.STRING) }
+ | "DATE" ^^ { case _ => Value(Datatype.DATE) }
+ )
+
def selectORunion:Parser[SelectORUnion] =
rep1sep(select, "UNION") ^^ { l => if (l.size == 1) l(0) else Union(l.toSet) }
@@ -224,10 +284,13 @@
"""[a-zA-Z_]\w*""".r ^^ { x => AttrAlias(Name(x)) }
def relationORsubselect:Parser[RelationORSubselect] = (
- """[a-zA-Z_]\w*""".r ^^ { x => Relation(Name(x)) }
+ relation ^^ { x => x }
| "(" ~ selectORunion ~ ")" ^^ { case "("~s~")" => Subselect(s) }
)
+ def relation:Parser[Relation] =
+ """[a-zA-Z_]\w*""".r ^^ { x => Relation(Name(x)) }
+
def relvar:Parser[RelVar] =
"""[a-zA-Z_]\w*""".r ^^ { x => RelVar(Name(x)) }
--- a/src/test/scala/SQLTest.scala Sun Feb 14 12:49:28 2010 -0500
+++ b/src/test/scala/SQLTest.scala Fri Feb 26 19:17:02 2010 -0500
@@ -371,5 +371,119 @@
assert(expected === (a.parseAll(a.select, e).get))
}
+ test("x") {
+ val a = Sql()
+ val e = """
+ID INT PRIMARY KEY
+"""
+ val expected = FieldDesc(Attribute("ID"), Value(Datatype.INTEGER), true)
+ assert(expected === (a.parseAll(a.fielddescorkeydef, e).get))
+ }
+
+ test("y") {
+ val a = Sql()
+ val e = """
+CREATE TABLE Sex_DE (ID INT PRIMARY KEY)
+"""
+ val expected =
+ (Relation("Sex_DE") ->
+ RelationDesc(Option(Attribute("ID")),
+ Map(Attribute("ID") -> Value(Datatype.INTEGER))))
+ assert(expected === (a.parseAll(a.create, e).get))
+ }
+
+ test("integrated PK") {
+ val a = Sql()
+ val e = """
+CREATE TABLE Sex_DE (ID INT PRIMARY KEY, EnterpriseEntryID INT)
+"""
+ val expected = DatabaseDesc(
+ Map(Relation("Sex_DE") ->
+ RelationDesc(Option(Attribute("ID")),
+ Map(Attribute("ID") -> Value(Datatype.INTEGER),
+ Attribute("EnterpriseEntryID") -> Value(Datatype.INTEGER)))))
+
+ assert(expected === (a.parseAll(a.ddl, e).get))
+ }
+
+ test("post-facto PK") {
+ val a = Sql()
+ val e = """
+CREATE TABLE Sex_DE (ID INT, EnterpriseEntryID INT, PRIMARY KEY (ID))
+"""
+ val expected = DatabaseDesc(
+ Map(Relation("Sex_DE") ->
+ RelationDesc(Option(Attribute("ID")),
+ Map(Attribute("ID") -> Value(Datatype.INTEGER),
+ Attribute("EnterpriseEntryID") -> Value(Datatype.INTEGER)))))
+
+ assert(expected === (a.parseAll(a.ddl, e).get))
+ }
+
+ test("foreign key") {
+ val a = Sql()
+ val e = """
+CREATE TABLE Person (ID INT PRIMARY KEY, MiddleName STRING, DateOfBirth DATE, SexDE INT, PRIMARY KEY (ID), FOREIGN KEY (SexDE) REFERENCES Sex_DE(ID));
+CREATE TABLE Sex_DE (ID INT PRIMARY KEY, EntryName STRING)
+"""
+ val expected:DatabaseDesc = DatabaseDesc(
+ Map(Relation("Person") ->
+ RelationDesc(Option(Attribute("ID")),
+ Map(Attribute("ID") -> Value(Datatype.INTEGER),
+ Attribute("MiddleName") -> Value(Datatype.STRING),
+ Attribute("DateOfBirth") -> Value(Datatype.DATE),
+ Attribute("SexDE") -> ForeignKey(Relation("Sex_DE"), Attribute("ID")))),
+ Relation("Sex_DE") ->
+ RelationDesc(Option(Attribute("ID")),
+ Map(Attribute("ID") -> Value(Datatype.INTEGER),
+ Attribute("EntryName") -> Value(Datatype.STRING)))
+ ))
+ assert(expected === (a.parseAll(a.ddl, e).get))
+ }
+
+ test("create db") {
+ val a = Sql()
+ val e = """
+CREATE TABLE Person (ID INT PRIMARY KEY, MiddleName STRING, DateOfBirth DATE, SexDE INT, PRIMARY KEY (ID), FOREIGN KEY (SexDE) REFERENCES Sex_DE(ID));
+CREATE TABLE Sex_DE (ID INT PRIMARY KEY, EntryName STRING);
+CREATE TABLE Item_Medication (ID INT PRIMARY KEY, PRIMARY KEY (ID), PatientID INT, FOREIGN KEY (PatientID) REFERENCES Person(ID), PerformedDTTM DATE, EntryName STRING);
+CREATE TABLE Medication (ID INT PRIMARY KEY, PRIMARY KEY (ID), ItemID INT, FOREIGN KEY (ItemID) REFERENCES Item_Medication(ID), MedDictDE INT, FOREIGN KEY (MedDictDE) REFERENCES Medication_DE(ID));
+CREATE TABLE Medication_DE (ID INT PRIMARY KEY, NDC INT);
+CREATE TABLE NDCcodes (ID INT PRIMARY KEY, NDC INT, ingredient INT);
+"""
+ val expected:DatabaseDesc = DatabaseDesc(
+ Map(Relation("Person") ->
+ RelationDesc(Option(Attribute("ID")),
+ Map(Attribute("ID") -> Value(Datatype.INTEGER),
+ Attribute("MiddleName") -> Value(Datatype.STRING),
+ Attribute("DateOfBirth") -> Value(Datatype.DATE),
+ Attribute("SexDE") -> ForeignKey(Relation("Sex_DE"), Attribute("ID")))),
+ Relation("Sex_DE") ->
+ RelationDesc(Option(Attribute("ID")),
+ Map(Attribute("ID") -> Value(Datatype.INTEGER),
+ Attribute("EntryName") -> Value(Datatype.STRING))),
+ Relation("Item_Medication") ->
+ RelationDesc(Option(Attribute("ID")),
+ Map(Attribute("ID") -> Value(Datatype.INTEGER),
+ Attribute("PatientID") -> ForeignKey(Relation("Person"), Attribute("ID")),
+ Attribute("PerformedDTTM") -> Value(Datatype.DATE),
+ Attribute("EntryName") -> Value(Datatype.STRING))),
+ Relation("Medication") ->
+ RelationDesc(Option(Attribute("ID")),
+ Map(Attribute("ID") -> Value(Datatype.INTEGER),
+ Attribute("ItemID") -> ForeignKey(Relation("Item_Medication"), Attribute("ID")),
+ Attribute("MedDictDE") -> ForeignKey(Relation("Medication_DE"), Attribute("ID")))),
+ Relation("Medication_DE") ->
+ RelationDesc(Option(Attribute("ID")),
+ Map(Attribute("ID") -> Value(Datatype.INTEGER),
+ Attribute("NDC") -> Value(Datatype.INTEGER))),
+ Relation("NDCcodes") ->
+ RelationDesc(Option(Attribute("ID")),
+ Map(Attribute("ID") -> Value(Datatype.INTEGER),
+ Attribute("NDC") -> Value(Datatype.INTEGER),
+ Attribute("ingredient") -> Value(Datatype.INTEGER)))
+ ))
+ assert(expected === (a.parseAll(a.ddl, e).get))
+ }
}
--- a/src/test/scala/SparqlToSparqlToSqlTest.scala Sun Feb 14 12:49:28 2010 -0500
+++ b/src/test/scala/SparqlToSparqlToSqlTest.scala Fri Feb 26 19:17:02 2010 -0500
@@ -16,38 +16,16 @@
*/
class SparqlToSparqlToSqlTest extends FunSuite {
- val hosp1:DatabaseDesc = DatabaseDesc(
- Map(Relation("Person") ->
- RelationDesc(Option(Attribute("ID")),
- Map(Attribute("ID") -> Value(Datatype.INTEGER),
- Attribute("MiddleName") -> Value(Datatype.STRING),
- Attribute("DateOfBirth") -> Value(Datatype.DATE),
- Attribute("SexDE") -> ForeignKey(Relation("Sex_DE"), Attribute("ID")))),
- Relation("Sex_DE") ->
- RelationDesc(Option(Attribute("ID")),
- Map(Attribute("ID") -> Value(Datatype.INTEGER),
- Attribute("EntryName") -> Value(Datatype.STRING))),
- Relation("Item_Medication") ->
- RelationDesc(Option(Attribute("ID")),
- Map(Attribute("ID") -> Value(Datatype.INTEGER),
- Attribute("PatientID") -> ForeignKey(Relation("Person"), Attribute("ID")),
- Attribute("PerformedDTTM") -> Value(Datatype.DATE),
- Attribute("EntryName") -> Value(Datatype.STRING))),
- Relation("Medication") ->
- RelationDesc(Option(Attribute("ID")),
- Map(Attribute("ID") -> Value(Datatype.INTEGER),
- Attribute("ItemID") -> ForeignKey(Relation("Item_Medication"), Attribute("ID")),
- Attribute("MedDictDE") -> ForeignKey(Relation("Medication_DE"), Attribute("ID")))),
- Relation("Medication_DE") ->
- RelationDesc(Option(Attribute("ID")),
- Map(Attribute("ID") -> Value(Datatype.INTEGER),
- Attribute("NDC") -> Value(Datatype.INTEGER))),
- Relation("NDCcodes") ->
- RelationDesc(Option(Attribute("ID")),
- Map(Attribute("ID") -> Value(Datatype.INTEGER),
- Attribute("NDC") -> Value(Datatype.INTEGER),
- Attribute("ingredient") -> Value(Datatype.INTEGER)))
- ))
+ val DDLParser = Sql()
+ val ddl = """
+CREATE TABLE Person (ID INT PRIMARY KEY, MiddleName STRING, DateOfBirth DATE, SexDE INT, PRIMARY KEY (ID), FOREIGN KEY (SexDE) REFERENCES Sex_DE(ID));
+CREATE TABLE Sex_DE (ID INT PRIMARY KEY, EntryName STRING);
+CREATE TABLE Item_Medication (ID INT PRIMARY KEY, PRIMARY KEY (ID), PatientID INT, FOREIGN KEY (PatientID) REFERENCES Person(ID), PerformedDTTM DATE, EntryName STRING);
+CREATE TABLE Medication (ID INT PRIMARY KEY, PRIMARY KEY (ID), ItemID INT, FOREIGN KEY (ItemID) REFERENCES Item_Medication(ID), MedDictDE INT, FOREIGN KEY (MedDictDE) REFERENCES Medication_DE(ID));
+CREATE TABLE Medication_DE (ID INT PRIMARY KEY, NDC INT);
+CREATE TABLE NDCcodes (ID INT PRIMARY KEY, NDC INT, ingredient INT);
+"""
+ val hosp1:DatabaseDesc = DDLParser.parseAll(DDLParser.ddl, ddl).get
test("~/swobjects/tests/healthCare/lists-notBound/hl7.rq short") {
val sparqlParser = Sparql()