# simpleSQL.py
#
# simple demo of using the parsing library to do simple-minded SQL parsing
# could be extended to include where clauses etc.
#
# Copyright (c) 2003,2016, Paul McGuire
#
from pyparsing import Literal, CaselessLiteral, Word, delimitedList, Optional, \
    Combine, Group, alphas, nums, alphanums, ParseException, Forward, oneOf, quotedString, \
    ZeroOrMore, restOfLine, CaselessKeyword, pyparsing_common

# define SQL tokens

ident          = Word( alphas, alphanums + "_$" ).setName("identifier")
columnName     = delimitedList(ident, ".", combine=True).setName("column name")
columnName.addParseAction(pyparsing_common.upcaseTokens)
columnNameList = Group( delimitedList(columnName))
tableName      = delimitedList(ident, ".", combine=True).setName("table name")
tableName.addParseAction(pyparsing_common.upcaseTokens)
tableNameList  = Group(delimitedList(tableName))

whereExpression = Forward()
and_, or_, in_ = map(CaselessKeyword, "and or in".split())

binop = oneOf("= != < > >= <= eq ne lt le gt ge", caseless=True)
realNum = pyparsing_common.real()
intNum = pyparsing_common.signed_integer()

columnRval = realNum | intNum | quotedString | columnName # need to add support for alg expressions
whereCondition = Group(
    ( columnName + binop + columnRval ) |
    ( columnName + in_ + "(" + delimitedList( columnRval ) + ")" ) |
    ( "(" + whereExpression + ")" )
    )
whereExpression << whereCondition + ZeroOrMore( ( and_ | or_ ) + whereExpression )
whereExpression.setDebug(True)


fails=("x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and "
         "x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and "
         "x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and "
         "x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and "
         "x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and "
         "x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and "
         "x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and "
         "x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and "
         "x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and "
         "x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and "
         "x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and "
         "x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and "
         "x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and "
         "x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and "
         "x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and "
         "x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and "
         "x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and "
         "x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and "
         "x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and "
         "x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and "
         "x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and "
         "x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and "
         "x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and "
         "x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and "
         "x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and "
         "x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and "
         "x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and "
         "x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and "
         "x=a and x=b and x=c and x=a and x=b and x=c and x=a and x=b and x=c and x=a")

whereExpression.parseString(fails)