Skip to content

Instantly share code, notes, and snippets.

@kputnam
Created October 19, 2010 19:11
Show Gist options
  • Save kputnam/634848 to your computer and use it in GitHub Desktop.
Save kputnam/634848 to your computer and use it in GitHub Desktop.
Parses a trivial subset of SQL
trait SqlParser extends scala.util.parsing.combinator.RegexParsers {
// ignore ordinary whitespace, line comments, and inline comments between combinators
override val whiteSpace = "(?sm)(\\s*(?:--.*?$|/\\*((?!\\*/).)*\\*/)\\s*|\\s+)+".r
def sqlStatement: Parser[Statement] =
opt(whiteSpace) ~> positioned( procedureCallStatement
| insertStatementCustom
| insertStatement
| updateStatement
| deleteStatement )
// parses SELECT sqlId([sqlLiteral [, sqlLiteral ...]]);
def procedureCallStatement: Parser[ProcedureCallStatement] = {
val params = "(" ~> repsep(sqlLiteral, ",") <~ ")" ^^ (xs => xs)
"(?i)SELECT[ \t\n]".r ~> sqlId ~ params <~ ";" ^^
{ case procedure ~ parameters => new ProcedureCallStatement(procedure, parameters) }
}
// Parses non-standard syntax INSERT INTO sqlId SET sqlId = sqlLiteral [, ...]
def insertStatementCustom: Parser[InsertStatement] = {
val assignment = (sqlId <~ "=") ~ sqlLiteral ^^ { case field ~ value => Assignment(field, value) }
((("(?i)INSERT[ \t\n]+INTO[ \t\n]".r ~> sqlId) <~ "(?i)SET".r) ~ rep1sep(assignment, ",")) <~ ";" ^^
{ case table ~ assignments => new InsertStatement(table, assignments) }
}
// Parses standard INSERT INTO (sqlId [, ...]) VALUES (sqlLiteral [, ...]);
def insertStatement: Parser[InsertStatement] = {
val fields = "(" ~> repsep(sqlId, ",") <~ ")" ^^ (xs => xs)
val source = "(" ~> repsep(sqlLiteral, ",") <~ ")" ^^ (xs => xs)
(((("(?i)INSERT[ \t\n]+INTO[ \t\n]".r ~> sqlId) ~ fields) <~ "(?i)VALUES".r) ~ source) <~ ";" ^^
{ case table ~ columns ~ values => new InsertStatement(table, columns.zip(values).map(p => Assignment(p._1, p._2))) }
}
// parses UPDATE sqlId SET sqlId = sqlLiteral [, ...] [filter];
def updateStatement: Parser[UpdateStatement] = {
val assignment = (sqlId <~ "=") ~ sqlLiteral ^^ { case field ~ value => Assignment(field, value) }
((("(?i)UPDATE[ \t\n]".r ~> sqlId) <~ "(?i)SET[ \t\n]".r) ~ rep1sep(assignment, ",")) ~ opt(filter) <~ ";" ^^
{ case table ~ assignments ~ filter => new UpdateStatement(table, assignments, filter) }
}
// parses DELETE FROM sqlId [filter];
def deleteStatement: Parser[DeleteStatement] =
"(?i)DELETE[ \t\n]+FROM[ \t\n]".r ~> sqlId ~ opt(filter) <~ ";" ^^
{ case table ~ filter => new DeleteStatement(table, filter) }
// parses WHERE sqlId = sqlLiteral
private def filter: Parser[Where] =
positioned((("(?i)WHERE[ \t\n]".r ~> sqlId) <~ "=") ~ sqlLiteral ^^
{ case field ~ value => new Where(field, value) })
private def sqlId: Parser[Identifier] =
positioned(rep1sep("\"[^\"]+\"".r | "(?i)[a-z_][0-9a-z_]*".r, ".") ^^
{ case xs => Identifier.fromComponents(xs) })
// extends SQL syntax to support pattern-matching literals '?' and '*'
private def sqlLiteral: Parser[Literal] = {
val escStringLit = "E'(?:\\\\'|[^'])*'".r ^^ (x => StringLit.fromString(x))
val stringLit = "(?:'[^']*')+".r ^^ (x => StringLit.fromString(x))
val requiredWildcard = "*" ^^^ ArbitraryLit()
val optionalWildcard = "?" ^^^ OptionalLit()
val numericLit = "[+-]?[0-9]+(?:\\.[0-9]*)?".r ^^ (NumericLit.fromString(_))
val nullLit = "(?i)null".r ^^^ NullLit()
val trueLit = "(?i)true|t|yes|y".r ^^^ TrueLit()
val falseLit = "(?i)false|f|no|n".r ^^^ FalseLit()
positioned( escStringLit
| stringLit
| numericLit ^? ({ case Some(y) => y }, _ => "literal.Numeric format exception")
| nullLit
| trueLit
| falseLit
| requiredWildcard
| optionalWildcard )
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment