--- a/sql/src/main/scala/SQL.scala Tue Feb 01 20:34:05 2011 -0500
+++ b/sql/src/main/scala/SQL.scala Tue Feb 01 21:24:36 2011 -0500
@@ -8,6 +8,7 @@
object SQLParsers extends RegexParsers {
val int = """[0-9]+""".r
+ val float = """([0-9]+)?\.([0-9]+)?""".r
val dquote = "\"([^\"\\\\\n\r]|\\\\[tbnrf\\\"'])*\"".r
val squote = "\'([^\'\\\\\n\r]|\\\\[tbnrf\\\''])*\'".r
}
@@ -182,8 +183,8 @@
}
def createview:Parser[View] = // @@@ could stick under ddl
- "CREATE" ~ "VIEW" ~ relation ~ "AS" ~ selectORunion ^^
- { case "CREATE"~"VIEW"~relation~"AS"~defn => View(relation, defn) }
+ "(?i)CREATE".r ~ "(?i)VIEW".r ~ relation ~ "(?i)AS".r ~ selectORunion ^^
+ { case _~_~relation~_~defn => View(relation, defn) }
sealed abstract class DataDefinition
case class Create(rn:RDB.RelName, relation:RDB.Relation) extends DataDefinition
@@ -206,9 +207,9 @@
)
def createtable:Parser[Create] =
- "CREATE" ~ "TABLE" ~ relation ~ "(" ~ rep1sep(fielddescorkeydef, ",") ~ ")" ^^
+ "(?i)CREATE".r ~ "(?i)TABLE".r ~ relation ~ "(" ~ rep1sep(fielddescorkeydef, ",") ~ ")" ^^
{
- case "CREATE"~"TABLE"~relation~"("~reldesc~")" => {
+ case _~_~relation~"("~reldesc~")" => {
val pk0:Option[RDB.CandidateKey] = None
val attrs0 = Map[RDB.AttrName, RDB.Datatype]()
val candidates0 = List[RDB.CandidateKey]()
@@ -243,9 +244,9 @@
}
def inserttable:Parser[Insert] =
- "INSERT" ~ "INTO" ~ relation ~ "(" ~ rep1sep(attribute, ",") ~ ")" ~ "VALUES" ~ "(" ~ rep1sep(value, ",") ~ ")" ^^
+ "(?i)INSERT".r ~ "(?i)INTO".r ~ relation ~ "(" ~ rep1sep(attribute, ",") ~ ")" ~ "(?i)VALUES".r ~ "(" ~ rep1sep(value, ",") ~ ")" ^^
{
- case "INSERT"~"INTO"~relation~"("~relnames~")"~"VALUES"~"("~values~")" => {
+ case _~_~relation~"("~relnames~")"~_~"("~values~")" => {
if (relnames.size != values.size)
error("different numbers of elements in names " + relnames + " vs. values " + values)
Map.empty ++ (relnames zip values)
@@ -254,40 +255,38 @@
}
def fielddescorkeydef:Parser[FieldDescOrKeyDeclaration] = (
- attribute ~ typpe ~ opt("PRIMARY" ~ "KEY") ^^
+ attribute ~ typpe ~ opt("(?i)PRIMARY".r ~ "(?i)KEY".r) ^^
{ case attribute~typpe~pkness => FieldDesc(attribute, typpe, pkness.isDefined) }
- | "PRIMARY" ~ "KEY" ~ "(" ~ rep1sep(attribute, ",") ~ ")" ^^
- { case "PRIMARY"~"KEY"~"("~attributes~")" => PrimaryKeyDeclaration(RDB.CandidateKey(attributes)) }
- | "UNIQUE" ~ "(" ~ rep1sep(attribute, ",") ~ ")" ^^
- { case "UNIQUE"~"("~attributes~")" => CandidateKeyDeclaration(RDB.CandidateKey(attributes)) }
- | "FOREIGN" ~ "KEY" ~ "(" ~ rep1sep(attribute, ",") ~ ")" ~ "REFERENCES" ~ relation ~ "(" ~ rep1sep(attribute, ",") ~ ")" ^^
- { case "FOREIGN"~"KEY"~"("~fk~")"~"REFERENCES"~relation~"("~pk~")" => ForeignKeyDeclaration(RDB.ForeignKeyKey(fk), relation, RDB.CandidateKey(pk)) }
+ | "(?i)PRIMARY".r ~ "(?i)KEY".r ~ "(" ~ rep1sep(attribute, ",") ~ ")" ^^
+ { case _~_~"("~attributes~")" => PrimaryKeyDeclaration(RDB.CandidateKey(attributes)) }
+ | "(?i)UNIQUE".r ~ "(" ~ rep1sep(attribute, ",") ~ ")" ^^
+ { case _~"("~attributes~")" => CandidateKeyDeclaration(RDB.CandidateKey(attributes)) }
+ | "(?i)FOREIGN".r ~ "(?i)KEY".r ~ "(" ~ rep1sep(attribute, ",") ~ ")" ~ "(?i)REFERENCES".r ~ relation ~ "(" ~ rep1sep(attribute, ",") ~ ")" ^^
+ { case _~_~"("~fk~")"~_~relation~"("~pk~")" => ForeignKeyDeclaration(RDB.ForeignKeyKey(fk), relation, RDB.CandidateKey(pk)) }
)
def typpe:Parser[RDB.Datatype] = (
- "INT" ~ opt(size) ^^ { case _ => RDB.Datatype.INTEGER } // alias for INTEGER
- | "INTEGER" ~ opt(size)^^ { case _ => RDB.Datatype.INTEGER }
- | "integer" ~ opt(size)^^ { case _ => RDB.Datatype.INTEGER } // @@ handle case-insensitivity
- | "DOUBLE" ^^ { case _ => RDB.Datatype.DOUBLE }
- | "FLOAT" ^^ { case _ => RDB.Datatype.FLOAT }
- | "STRING" ^^ { case _ => RDB.Datatype.STRING }
- | "CHAR" ~ opt(size) ^^ { case _ => RDB.Datatype.STRING }
- | "VARCHAR" ~ opt(size) ^^ { case _ => RDB.Datatype.STRING }
- | "varchar" ~ opt(size) ^^ { case _ => RDB.Datatype.STRING }
- | "DATETIME" ^^ { case _ => RDB.Datatype.DATETIME }
- | "DATE" ^^ { case _ => RDB.Datatype.DATE }
+ "(?i)INT".r ~ opt(size) ^^ { case _ => RDB.Datatype.INTEGER } // alias for INTEGER
+ | "(?i)INTEGER".r ~ opt(size)^^ { case _ => RDB.Datatype.INTEGER }
+ | "(?i)DOUBLE".r ^^ { case _ => RDB.Datatype.DOUBLE }
+ | "(?i)FLOAT".r ^^ { case _ => RDB.Datatype.FLOAT }
+ | "(?i)STRING".r ^^ { case _ => RDB.Datatype.STRING }
+ | "(?i)CHAR".r ~ opt(size) ^^ { case _ => RDB.Datatype.STRING }
+ | "(?i)VARCHAR".r ~ opt(size) ^^ { case _ => RDB.Datatype.STRING }
+ | "(?i)DATETIME".r ^^ { case _ => RDB.Datatype.DATETIME }
+ | "(?i)DATE".r ^^ { case _ => RDB.Datatype.DATE }
)
def size:Parser[Int] =
"(" ~ int ~ ")" ^^ { case l ~ s ~ r => s.toInt }
def selectORunion:Parser[SelectORUnion] =
- rep1sep(select, "UNION") ^^ { l => if (l.size == 1) l(0) else Union(l.toSet) }
+ rep1sep(select, "(?i)UNION".r) ^^ { l => if (l.size == 1) l(0) else Union(l.toSet) }
def select:Parser[Select] =
- "SELECT" ~ opt("DISTINCT") ~ projection ~ opt(tablelist) ~ opt(where) ~ opt(order) ~ opt(offset) ~ opt(limit) ^^
+ "(?i)SELECT".r ~ opt("(?i)DISTINCT".r) ~ projection ~ opt(tablelist) ~ opt(where) ~ opt(order) ~ opt(offset) ~ opt(limit) ^^
{
- case "SELECT" ~ distinct ~ attributes ~ tablesANDons ~ whereexpr ~ order ~ offset ~ limit => {
+ case _ ~ distinct ~ attributes ~ tablesANDons ~ whereexpr ~ order ~ offset ~ limit => {
val (tables, onExpressions) =
tablesANDons.getOrElse((TableList(AddOrderedSet[Join]()), Set[Expression]()))
val t:Set[Expression] = onExpressions
@@ -312,29 +311,29 @@
}
def order:Parser[List[OrderElt]] =
- "ORDER" ~ "BY" ~ rep(orderelt) ^^ { case o~b~elts => elts }
+ "(?i)ORDER".r ~ "(?i)BY".r ~ rep(orderelt) ^^ { case o~b~elts => elts }
def orderelt:Parser[OrderElt] = (
- "ASC" ~ "(" ~ expression ~ ")" ^^ { case a~o~expr~c => OrderElt(false, expr) }
- | "DESC" ~ "(" ~ expression ~ ")" ^^ { case a~o~expr~c => OrderElt(true, expr) }
+ "(?i)ASC".r ~ "(" ~ expression ~ ")" ^^ { case a~o~expr~c => OrderElt(false, expr) }
+ | "(?i)DESC".r ~ "(" ~ expression ~ ")" ^^ { case a~o~expr~c => OrderElt(true, expr) }
| fqattribute ^^ { case v => OrderElt(false, PrimaryExpressionAttr(v)) }
)
def offset:Parser[Int] =
- "OFFSET" ~ int ^^ { case o~i => i.toInt }
+ "(?i)OFFSET".r ~ int ^^ { case o~i => i.toInt }
def limit:Parser[Int] =
- "LIMIT" ~ int ^^ { case o~i => i.toInt }
+ "(?i)LIMIT".r ~ int ^^ { case o~i => i.toInt }
def where:Parser[Expression] =
- "WHERE" ~ expression ^^ { case "WHERE" ~ expression => expression }
+ "(?i)WHERE".r ~ expression ^^ { case _ ~ expression => expression }
def projection:Parser[Projection] =
repsep(namedattribute, ",") ^^ { l => Projection(l.toSet) }
def namedattribute:Parser[ProjectAttribute] =
- fqattributeORprimaryexpression ~ "AS" ~ attralias ^^
- { case fqattributeORprimaryexpression ~ "AS" ~ attralias =>
+ fqattributeORprimaryexpression ~ "(?i)AS".r ~ attralias ^^
+ { case fqattributeORprimaryexpression ~ _ ~ attralias =>
ProjectAttribute(fqattributeORprimaryexpression, attralias) }
def fqattributeORprimaryexpression:Parser[RelVarAttrORExpression] = (
@@ -364,30 +363,30 @@
"""[a-zA-Z_]\w*""".r ^^ { x => RelVar(Name(x)) }
def tablelist:Parser[(TableList, Set[Expression])] =
- "FROM" ~ aliasedjoin ~ rep(innerORouter) ^^
- { case "FROM"~aj~l => (TableList(AddOrderedSet(InnerJoin(aj, None) :: l.map((one) => one._1))),
+ "(?i)FROM".r ~ aliasedjoin ~ rep(innerORouter) ^^
+ { case _~aj~l => (TableList(AddOrderedSet(InnerJoin(aj, None) :: l.map((one) => one._1))),
l.foldLeft(Set[Expression]())((all, one) => all ++ one._2)) }
def innerORouter:Parser[(Join, Set[Expression])] = (
- "INNER" ~ "JOIN" ~ aliasedjoin ~ opt("ON" ~ expression) ^^
- { case "INNER"~"JOIN"~a~o => (InnerJoin(a, None), { if (o.isDefined) Set(o.get._2) else Set[Expression]() } ) }
- | "LEFT" ~ "OUTER" ~ "JOIN" ~ aliasedjoin ~ "ON" ~ expression ^^
+ "(?i)INNER".r ~ "(?i)JOIN".r ~ aliasedjoin ~ opt("(?i)ON".r ~ expression) ^^
+ { case _~_~a~o => (InnerJoin(a, None), { if (o.isDefined) Set(o.get._2) else Set[Expression]() } ) }
+ | "(?i)LEFT".r ~ "(?i)OUTER".r ~ "(?i)JOIN".r ~ aliasedjoin ~ "(?i)ON".r ~ expression ^^
{ case l~o~j~alijoin~on~expr => (LeftOuterJoin(alijoin, expr), Set[Expression]()) }
)
def aliasedjoin:Parser[AliasedResource] =
- relationORsubselect ~ "AS" ~ relvar ^^
- { case rel1 ~ "AS" ~ rel2 => AliasedResource(rel1, rel2) }
+ relationORsubselect ~ "(?i)AS".r ~ relvar ^^
+ { case rel1 ~ _ ~ rel2 => AliasedResource(rel1, rel2) }
def expression:Parser[Expression] =
ORexpression ^^ { x => x }
def ORexpression:Parser[Expression] =
- rep1sep (ANDexpression, "OR") ^^
+ rep1sep (ANDexpression, "(?i)OR".r) ^^
{ xs => if (xs.size > 1) ExprDisjunction(xs.toSet) else xs(0) }
def ANDexpression:Parser[Expression] =
- rep1sep (relationalexpression, "AND") ^^
+ rep1sep (relationalexpression, "(?i)AND".r) ^^
{ xs => if (xs.size > 1) ExprConjunction(xs.toSet) else xs(0) }
def relationalexpression:Parser[Expression] = (
@@ -399,30 +398,32 @@
{ case primaryexpression ~ "<" ~ rvalue => RelationalExpressionLt(primaryexpression, rvalue) }
| primaryexpression ~ ">" ~ primaryexpression ^^
{ case primaryexpression ~ ">" ~ rvalue => RelationalExpressionGt(primaryexpression, rvalue) }
- | primaryexpression ~ "IS" ~ "NULL" ^^
- { case primaryexpression ~ "IS" ~ "NULL" => RelationalExpressionNull(primaryexpression) }
- | primaryexpression ~ "IS" ~ "NOT" ~ "NULL" ^^
- { case primaryexpression ~ "IS" ~ "NOT" ~ "NULL" => RelationalExpressionNotNull(primaryexpression) }
+ | primaryexpression ~ "(?i)IS".r ~ "(?i)NULL".r ^^
+ { case primaryexpression ~ _ ~ _ => RelationalExpressionNull(primaryexpression) }
+ | primaryexpression ~ "(?i)IS".r ~ "(?i)NOT".r ~ "(?i)NULL".r ^^
+ { case primaryexpression ~ _ ~ _ ~ _ => RelationalExpressionNotNull(primaryexpression) }
| primaryexpression ^^
{ case primaryexpression => primaryexpression }
)
def primaryexpression:Parser[Expression] = (
fqattribute ^^ { PrimaryExpressionAttr(_) }
+ | float ^^ { i => PrimaryExpressionTyped(RDB.Datatype.FLOAT, Name(i)) }
| int ^^ { i => PrimaryExpressionTyped(RDB.Datatype.INTEGER, Name(i)) }
| dquote ^^ { x => PrimaryExpressionTyped(RDB.Datatype.STRING, Name(x.substring(1, x.size - 1))) }
| squote ^^ { x => PrimaryExpressionTyped(RDB.Datatype.STRING, Name(x.substring(1, x.size - 1))) }
- | "NULL" ^^ { case "NULL" => ConstNULL() }
- | "CONCAT" ~ "(" ~ rep1sep(expression, ",") ~ ")" ^^ { case "CONCAT"~"("~expressions~")" => Concat(expressions) }
- | "IF" ~ "(" ~ expression ~ "," ~ expression ~ "," ~ expression ~ ")" ^^ { case "IF"~"("~c~","~p~","~f~")" => IfElse(c, p, f) }
+ | "(?i)NULL".r ^^ { case _ => ConstNULL() }
+ | "(?i)CONCAT".r ~ "(" ~ rep1sep(expression, ",") ~ ")" ^^ { case _~"("~expressions~")" => Concat(expressions) }
+ | "(?i)IF".r ~ "(" ~ expression ~ "," ~ expression ~ "," ~ expression ~ ")" ^^ { case _~"("~c~","~p~","~f~")" => IfElse(c, p, f) }
| "(" ~ expression ~ ")" ^^ { case "("~x~")" => x }
)
def value:Parser[RDB.CellValue] = (
- int ^^ { i => RDB.LexicalValue(i) }
+ float ^^ { i => RDB.LexicalValue(i) }
+ | int ^^ { i => RDB.LexicalValue(i) }
| dquote ^^ { x => RDB.LexicalValue(x.substring(1, x.size - 1)) }
| squote ^^ { x => RDB.LexicalValue(x.substring(1, x.size - 1)) }
- | "NULL" ^^ { case "NULL" => RDB.␀ }
+ | "(?i)NULL".r ^^ { case _ => RDB.␀ }
)
}
--- a/sql/src/test/scala/SQLTest.scala Tue Feb 01 20:34:05 2011 -0500
+++ b/sql/src/test/scala/SQLTest.scala Tue Feb 01 21:24:36 2011 -0500
@@ -1014,6 +1014,22 @@
}
+ 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
*/