sql-parser

Simple SQL lexer and recursive descent parser written in Kotlin.

Supports

• SELECT statement with most of its syntax (DB agnostic)

Doesn't support

• Anything except SELECT
• Union
• Common Table Expression
• Window functions

Usage

import com.drdaemos.sqlparser.lexer.SqlLexer
import com.drdaemos.sqlparser.parser.SqlParser

// ...
val sqlString = "SELECT * FROM table"
val tokens = SqlLexer().getTokens(testString)
val statementTree = SqlParser().compileTree(tokens)

// [0: SELECT [Keyword], 7: * [Operator], ...]
println(tokens.toString())
// Statement: [SelectQuery: [SelectList: [SelectReference: [ColumnIdentifier: "*"]], ...
println(statementTree.toString())

As a result, you receive a compiled AST of the whole statement. It is possible to iterate over children and gather various sorts of info via accept(Visitor) function. One example is SelectQueryInfo object, that can gather general info about the query in Text format.

val query = "SELECT author.name, count(book.id), sum(book.cost) " +
        "FROM author " +
        "LEFT JOIN book ON (author.id = book.author_id) " +
        "GROUP BY author.name " +
        "HAVING COUNT(*) > 1 AND SUM(book.cost) > 500 " +
        "LIMIT 10"
val tokens = SqlLexer().getTokens(query)
val statementTree = SqlParser().compileTree(tokens)
val queryInfo = SelectQueryInfo()
queryInfo.gather(statementTree)

Supported Grammar

<statement> ::= <select-query>
<select-query> ::= "SELECT" [<set-quantifier>] <select-list> <table-expression>
<set-quantifier> ::= "DISTINCT" | "ALL"
<select-list> ::= "*" | <select-reference> *["," <select-reference>]
<function-expression> ::= <function-identifier> "(" <function-body> ")"
<table-expression> ::= <from-clause> [<join-clause> <where-clause> <group-by-clause> <order-by-clause> <having-clause> <limit-clause>]
<from-clause> ::= "FROM" <table-reference> *["," <table-reference>]
<select-reference> ::= <column-identifier> [<alias>] | <function-expression> [<alias>] | <subquery> [<alias>]
<table-reference> ::= <table-identifier> [<alias>] | <subquery> <alias>
<alias> ::= ["AS"] <alias-identifier>
<subquery> ::= "(" <select-query> ")"
<join-clause> ::= <joined-table> *[<joined-table>]
<joined-table> ::= <join-type> "JOIN" <table-reference> [<join-condition>]
<join-type> ::= "INNER" | <outer-join-type> ["OUTER"] | "UNION"
<outer-join-type> ::= "LEFT" | "RIGHT" | "FULL"
<join-condition> ::= "ON" <filter-condition>
<filter-condition> ::= <predicate> | <predicate-group>  *[<boolean-operator> <predicate> | <predicate-group>]
<boolean-operator> ::= "AND" | "OR"
<predicate-group> ::= "(" <filter-condition> ")"
<predicate> ::= <left-hand-side> "IS" ["NOT"] "NULL" | <left-hand-side> <comparison-operator> <compared-reference> | <left-hand-side> <in-operator> <containing-reference> | <left-hand-side> "BETWEEN" <literal-value> "AND" <literal-value> |
<left-hand-side> ::= <column-identifier> | <function-expression>
<compared-reference> ::= <column-identifier> | <literal-value> | <subquery>
<containing-reference> ::= <literal-value-list> | <subquery>
<literal-value-list> ::= "(" <literal-value> *["," <literal-value>] ")"
<in-operator> ::= "IN"
<comparison-operator> ::= "=" | ">" | "<" | "<=" | ">=" | "LIKE"
<where-clause> ::= "WHERE" <filter-condition>
<group-by-clause> ::= "GROUP BY" <column-identifier> *["," <column-identifier>]
<order-by-clause> ::= "ORDER BY" <order-by-expression> *["," <order-by-expression>]
<order-by-expression> ::= <column-identifier> <order-direction>
<order-direction> ::= "ASC" | "DESC"
<having-clause> ::= "HAVING" <filter-condition>
<limit-clause> ::= "LIMIT" <literal-value> ["OFFSET" <literal-value>]

// terminals
<column-identifier> ::= string
<function-identifier> ::= string
<function-body> ::= string
<table-identifier> ::= string
<alias-identifier> ::= string
<literal-value> ::= string | numeric