def createUser(query):
userStmt = Forward()
CREATE, USER, IDENTIFIED, BY = map(CaselessKeyword,
"create user identified by".split())
ident = Word(alphas).setName("identifier")
userName = delimitedList(ident).setName("user")
userName.addParseAction(ppc.upcaseTokens)
#tableNameList = Group(delimitedList(tableName))
intValue = ppc.signed_integer()
pwdValue = Group("passer" + intValue)
# define the grammar
userStmt <<= (CREATE + USER + userName("user") + IDENTIFIED + BY +
pwdValue("pwd") + Literal(';'))
simpleSQL = userStmt
# define Oracle comment format, and ignore them
oracleSqlComment = "--" + restOfLine
simpleSQL.ignore(oracleSqlComment)
return simpleSQL.parseString(query)
def datetime_parser() -> ParseResults: # pylint: disable=too-many-locals
( # pylint: disable=invalid-name
DATETIME,
DATEADD,
DATETRUNC,
LASTDAY,
HOLIDAY,
YEAR,
QUARTER,
MONTH,
WEEK,
DAY,
HOUR,
MINUTE,
SECOND,
) = map(
CaselessKeyword,
"datetime dateadd datetrunc lastday holiday "
"year quarter month week day hour minute second".split(),
)
lparen, rparen, comma = map(Suppress, "(),")
int_operand = pyparsing_common.signed_integer().setName("int_operand")
text_operand = quotedString.setName("text_operand").setParseAction(
EvalText)
# allow expression to be used recursively
datetime_func = Forward().setName("datetime")
dateadd_func = Forward().setName("dateadd")
datetrunc_func = Forward().setName("datetrunc")
lastday_func = Forward().setName("lastday")
holiday_func = Forward().setName("holiday")
date_expr = (datetime_func | dateadd_func | datetrunc_func | lastday_func
| holiday_func)
datetime_func <<= (DATETIME + lparen + text_operand +
rparen).setParseAction(EvalDateTimeFunc)
dateadd_func <<= (
DATEADD + lparen +
Group(date_expr + comma + int_operand + comma +
(YEAR | QUARTER | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND) +
ppOptional(comma)) + rparen).setParseAction(EvalDateAddFunc)
datetrunc_func <<= (
DATETRUNC + lparen +
Group(date_expr + comma +
(YEAR | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND) +
ppOptional(comma)) + rparen).setParseAction(EvalDateTruncFunc)
lastday_func <<= (LASTDAY + lparen +
Group(date_expr + comma +
(YEAR | MONTH | WEEK) + ppOptional(comma)) +
rparen).setParseAction(EvalLastDayFunc)
holiday_func <<= (HOLIDAY + lparen +
Group(text_operand + ppOptional(comma) +
ppOptional(date_expr) + ppOptional(comma) +
ppOptional(text_operand) + ppOptional(comma)) +
rparen).setParseAction(EvalHolidayFunc)
return date_expr
def _prepare_sql_parser():
"""
Подготовка SQL-like парсера, для разбора условий в фильтрах
Returns
-------
simple_sql : obj
Объект класса отвечающего за парсинг
"""
# define SQL tokens
select_stmt = Forward()
AND, OR, IN, NIN = map(CaselessKeyword, "and or in nin".split())
ident = Word(alphas, alphanums + "_$").setName("identifier")
column_name = delimitedList(ident, ".",
combine=True).setName("column name")
column_name.addParseAction()
binop = oneOf("= != > < <= >=", caseless=True)
real_num = ppc.real()
int_num = ppc.signed_integer()
column_rval = (real_num | int_num | quotedString | column_name
) # need to add support for alg expressions
where_condition = Group(
(column_name + binop + column_rval)
| (column_name + IN +
Group("(" + delimitedList(column_rval) + ")"))
| (column_name + IN + Group("(" + select_stmt + ")")))
where_expression = infixNotation(
where_condition,
[
(AND, 2, opAssoc.LEFT),
(OR, 2, opAssoc.LEFT),
],
)
# define the grammar
select_stmt <<= where_expression
simple_sql = select_stmt
# define Oracle comment format, and ignore them
oracle_sql_comment = "--" + restOfLine
simple_sql.ignore(oracle_sql_comment)
return simple_sql
def connexion(query):
# define SQL tokens
stmt = Forward()
MYSQL, U, P = map(CaselessKeyword, "mysql u p".split())
ident = Word(alphas).setName("identifier")
userName = delimitedList(ident).setName("user")
userName.addParseAction(ppc.upcaseTokens)
intValue = ppc.signed_integer()
pwdValue = quotedString()
# define the grammar
stmt <<= (MYSQL + Literal('-') + U + userName("user") + Literal('-') + P +
pwdValue("pwd") + Literal(';'))
simpleSQL = stmt
# define Oracle comment format, and ignore them
oracleSqlComment = "--" + restOfLine
simpleSQL.ignore(oracleSqlComment)
return simpleSQL.parseString(query)
def getLiteral(self):
'''
get the literal sub Grammar
'''
uri=Regex(SiDIFParser.getUriRegexp())('uri')
booleanLiteral=oneOf(["true","false"]).setParseAction(self.convertToBoolean)('boolean')
hexLiteral=(Suppress("0x")+(Word(hexnums).setParseAction(tokenMap(int, 16))))('hexLiteral')
integerLiteral=pyparsing_common.signed_integer('integerLiteral')
floatingPointLiteral=Group(
pyparsing_common.sci_real|pyparsing_common.real
).setParseAction(self.handleGroup)('floatingPointLiteral')
timeLiteral=Regex(r"[0-9]{2}:[0-9]{2}(:[0-9]{2})?").setParseAction(self.convertToTime)('timeLiteral')
dateLiteral=pyparsing_common.iso8601_date.copy().setParseAction(pyparsing_common.convertToDate())('dateLiteral')
dateTimeLiteral=Group(
dateLiteral+Optional(timeLiteral)
).setParseAction(self.handleDateTimeLiteral)('dateTimeLiteral')
stringLiteral=Group(
Suppress('"')+ZeroOrMore(CharsNotIn('"')|LineEnd())+Suppress('"')
).setParseAction(self.handleStringLiteral)('stringLiteral')
literal=Group(
uri | stringLiteral | booleanLiteral | hexLiteral | dateTimeLiteral | timeLiteral | floatingPointLiteral| integerLiteral
).setParseAction(self.handleGroup)("literal")
return literal
def createTable(query):
# define SQL tokens
createStmt = Forward()
CREATE, TABLE, INT, DATE, TIME, CHAR, VARCHAR, FLOAT = map(
CaselessKeyword,
"create table int date time char varchar float".split())
ident = Word(alphas).setName("identifier")
#columnName = delimitedList(column)
#columnName.addParseAction(ppc.upcaseTokens)
#columnNameList = Group(delimitedList(columnName))
tableName = delimitedList(ident, ".", combine=True).setName("table")
tableName.addParseAction(ppc.upcaseTokens)
#tableNameList = Group(delimitedList(tableName))
intValue = ppc.signed_integer()
INTEGER = INT
varcharType = Combine(VARCHAR + "(" + intValue + ")")
columnType = (INT | INTEGER | TIME | DATE | varcharType | CHAR | FLOAT)
columnValue = Group(ident + columnType)
columnNameList = Group(delimitedList(columnValue, ","))
# define the grammar
createStmt <<= (CREATE + TABLE("create") + tableName("table") +
Literal("(") + columnNameList("column") + Literal(")") +
Literal(';'))
simpleSQL = createStmt
# define Oracle comment format, and ignore them
oracleSqlComment = "--" + restOfLine
simpleSQL.ignore(oracleSqlComment)
return simpleSQL.parseString(query)
SELECT, FROM, WHERE = map(CaselessKeyword, "select from where".split())
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 ) + ")" ) |
( columnName + in_ + "(" + selectStmt + ")" ) |
( "(" + whereExpression + ")" )
)
whereExpression << whereCondition + ZeroOrMore( ( and_ | or_ ) + whereExpression )
# define the grammar
selectStmt <<= (SELECT + ('*' | columnNameList)("columns") +
FROM + tableNameList( "tables" ) +
Optional(Group(WHERE + whereExpression), "")("where"))
from pyparsing import (alphanums, alphas, CaselessKeyword, delimitedList,
Group, infixNotation, oneOf, opAssoc, pyparsing_common
as ppc, quotedString, Word)
_logger = logging.getLogger(__name__)
AND, OR, IN, IS, NOT, NULL, BETWEEN = map(
CaselessKeyword, "and or in is not null between".split())
NOT_NULL = NOT + NULL
ident = Word(alphas, alphanums + "_$").setName("identifier")
columnName = delimitedList(ident, ".", combine=True).setName("column name")
binop = oneOf("= == != < > >= <= eq ne lt le gt ge <>", caseless=False)
realNum = ppc.real()
intNum = ppc.signed_integer()
columnRval = (realNum
| intNum
| quotedString
| columnName) # need to add support for alg expressions
whereCondition = Group(
(columnName + binop + columnRval)
| (columnName + IN + Group("(" + delimitedList(columnRval) + ")"))
| (columnName + IS + (NULL | NOT_NULL))
| (columnName + BETWEEN + columnRval + AND + columnRval))
whereExpression = infixNotation(
Group(whereCondition
| NOT + whereCondition
| NOT + Group('(' + whereCondition + ')')
def create_sql(self):
selectStmt = Forward()
SELECT, FROM, WHERE, AND, OR, IN, IS, NOT, NULL, COUNT, AVG, MIN, MAX, SUM, AS = map(
CaselessKeyword,
"select from where and or in is not null count avg min max sum as".
split())
NOT_NULL = NOT + NULL
ident = Word(alphas, alphanums + "_$").setName("identifier")
alias = delimitedList(ident, ".", combine=True).setName("alias")
alias.addParseAction(ppc.upcaseTokens)
columnName = delimitedList(ident, ".",
combine=True).setName("column name")
columnName.addParseAction(ppc.upcaseTokens)
columnNameList = Group(delimitedList(columnName))
tableName = delimitedList(ident, ".",
combine=True).setName("table name")
tableName.addParseAction(ppc.upcaseTokens)
tableNameRalias = Group((tableName("table") + AS + alias("alias"))
| (tableName("table")))
tableNameList = Group(delimitedList(tableNameRalias))
binop = oneOf("= != < > >= <= eq ne lt le gt ge", caseless=True)
realNum = ppc.real()
intNum = ppc.signed_integer()
columnRval = realNum | intNum | quotedString | columnName # need to add support for alg expressions
val = realNum | intNum | quotedString
columnRstar = '*' | columnName # need to add support for alg expressions
EquiJoin = (columnName('col1') + '=' + columnName('col2'))
equalityPredicate = columnName('col1') + '=' + columnRval('val')
Predicates = Group((columnName('col1') + binop + columnRval)
| (columnName('col1') + IN +
Group("(" + delimitedList(columnRval) + ")"))
| (columnName('col1') + IN +
Group("(" + selectStmt + ")"))
| (columnName + IS + (NULL | NOT_NULL)))
whereCondition = Group(
EquiJoin('equijoin') | equalityPredicate('equalitypredicate')
| Predicates('otherPredicates'))
whereCondition_sketch = Group(
EquiJoin('equijoin') | equalityPredicate('equalitypredicate'))
Aggregates = Group(((COUNT | AVG | MIN | MAX | SUM)("operator") +
(Group("(" + columnName + ")"))("operand"))
| (COUNT("operator") +
Group("(" + "*" + ")")("operand")))
AggregateExpression = delimitedList(Aggregates)
whereExpression_predicates = infixNotation(whereCondition_sketch, [
(AND, 2, opAssoc.LEFT),
])
whereExpression = infixNotation(whereCondition, [
(NOT, 1, opAssoc.RIGHT),
(AND, 2, opAssoc.LEFT),
(OR, 2, opAssoc.LEFT),
])
# define the grammar
selectStmt <<= (SELECT + ((AggregateExpression)("aggregates") |
('*' | columnNameList)("columns")) + FROM +
tableNameList("tables") + WHERE +
(whereExpression_predicates)("sketch_predicates") +
Optional(AND +
(whereExpression)("ignored_predicates")))
simpleSQL = selectStmt
# define Oracle comment format, and ignore them
oracleSqlComment = "--" + restOfLine
simpleSQL.ignore(oracleSqlComment)
return simpleSQL
SELECT, FROM, WHERE = map(CaselessKeyword, "select from where".split())
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 ) + ")" ) |
( columnName + in_ + "(" + selectStmt + ")" ) |
( "(" + whereExpression + ")" )
)
whereExpression << whereCondition + ZeroOrMore( ( and_ | or_ ) + whereExpression )
# define the grammar
selectStmt <<= (SELECT + ('*' | columnNameList)("columns") +
FROM + tableNameList( "tables" ) +
Optional(Group(WHERE + whereExpression), "")("where"))
"programa car int retorne".split())
ESCREVA, NOVALINHA, SE, ENTAO = map(CaselessKeyword,
"escreva novalinha se entao".split())
SENAO, ENQUANTO, EXECUTE, LEIA, TERMINATOR = map(
CaselessKeyword, "senao enquanto execute leia ;".split())
keywords = MatchFirst(
(PROGRAMA, CAR, INT, RETORNE, ESCREVA, NOVALINHA, SE, ENTAO, SENAO,
ENQUANTO, EXECUTE, LEIA)).setName("Reserved Words")
#Define the Terminator character
TERMINATOR = Word(";").setName("Terminator")
#Define the numbers
realNum = ppc.real().setName("Real Number")
intNum = ppc.signed_integer().setName("Integer Number")
#Define the identificator
identifier = Word(alphas, alphanums + "_$").setName("Identifier")
#Types Definition
Type = (INT | CAR).setName("Type")
#<<<<<<<<<<<<<<< BASICS DEFINITIONS<<<<<<<<<<<<<<<<<<<<<<<<
#>>>>>>>>>>>>>>> EXPRESSIONS DECLARATIONS>>>>>>>>>>>>>>>>>>
#Assigning the recursive expressions
Command = Forward()
Expr = Forward()
AssignExpr = Forward()
def __init__(self):
""" A program is a list of statements.
Statements can be 'set' or 'select' statements.
"""
statement = Forward()
SELECT, FROM, WHERE, SET, AS = map(CaselessKeyword, "select from where set as".split())
ident = Word( "$" + alphas, alphanums + "_$" ).setName("identifier")
columnName = delimitedList(ident, ".", combine=True).setName("column name")
columnNameList = Group( delimitedList(columnName))
tableName = delimitedList(ident, ".", combine=True).setName("column name")
tableNameList = Group(delimitedList(tableName))
SEMI,COLON,LPAR,RPAR,LBRACE,RBRACE,LBRACK,RBRACK,DOT,COMMA,EQ = map(Literal,";:(){}[].,=")
arrow = Literal ("->")
t_expr = Group(ident + LPAR + Word("$" + alphas, alphanums + "_$") + RPAR + ZeroOrMore(LineEnd())).setName("t_expr") | \
Word(alphas, alphanums + "_$") + ZeroOrMore(LineEnd())
t_expr_chain = t_expr + ZeroOrMore(arrow + t_expr)
whereExpression = Forward()
and_, or_, in_ = map(CaselessKeyword, "and or in".split())
binop = oneOf("= != < > >= <= eq ne lt le gt ge", caseless=True)
realNum = ppc.real()
intNum = ppc.signed_integer()
columnRval = realNum | intNum | quotedString | columnName # need to add support for alg expressions
whereCondition = Group(
( columnName + binop + (columnRval | Word(printables) ) ) |
( columnName + in_ + "(" + delimitedList( columnRval ) + ")" ) |
( columnName + in_ + "(" + statement + ")" ) |
( "(" + whereExpression + ")" )
)
whereExpression << whereCondition + ZeroOrMore( ( and_ | or_ ) + whereExpression )
'''
Assignment for handoff.
'''
setExpression = Forward ()
setStatement = Group(
( ident ) |
( quotedString("json_path") + AS + ident("name") ) |
( "(" + setExpression + ")" )
)
setExpression << setStatement + ZeroOrMore( ( and_ | or_ ) + setExpression )
optWhite = ZeroOrMore(LineEnd() | White())
""" Define the statement grammar. """
statement <<= (
Group(
Group(SELECT + t_expr_chain)("concepts") + optWhite +
Group(FROM + tableNameList) + optWhite +
Group(Optional(WHERE + whereExpression("where"), "")) + optWhite +
Group(Optional(SET + setExpression("set"), ""))("select")
)
|
Group(
SET + (columnName + EQ + ( quotedString | intNum | realNum ))
)("set")
)("statement")
""" Make a program a series of statements. """
self.program = statement + ZeroOrMore(statement)
""" Make rest-of-line comments. """
comment = "--" + restOfLine
self.program.ignore (comment)
