def to_json_operator(tokens):
# ARRANGE INTO {op: params} FORMAT
length = len(tokens.tokens)
if length == 2:
op = tokens.tokens[0].type.parser_name
if op == "neg" and is_number(tokens[1]):
return -tokens[1]
return {op: tokens[1]}
elif length == 5:
return {
tokens.tokens[1].type.parser_name:
[tokens[0], tokens[2], tokens[4]]
}
op = tokens[1]
if not isinstance(op, text):
op = op.type.parser_name
op = binary_ops.get(op, op)
if op == "eq":
if tokens[2] == None:
return {"missing": tokens[0]}
elif tokens[0] == "null":
return {"missing": tokens[2]}
elif op == "neq":
if tokens[2] == None:
return {"exists": tokens[0]}
elif tokens[0] == "null":
return {"exists": tokens[2]}
elif op == "is":
if tokens[2] == None:
return {"missing": tokens[0]}
else:
return {"exists": tokens[0]}
elif op == "is_not":
if tokens[2] == None:
return {"exists": tokens[0]}
else:
return {"missing": tokens[0]}
operands = [tokens[0], tokens[2]]
binary_op = {op: operands}
if op in {"add", "mul", "and", "or"}:
# ASSOCIATIVE OPERATORS
acc = []
for operand in operands:
if isinstance(operand, ParseResults):
# if operand[0][0] and operand[0][0][0] and operand[0][0][0]['and']:
# prefix = operand[0].get(op)
prefix = operand[0].get(op)
if prefix:
acc.extend(prefix)
continue
acc.append(operand)
return {op: acc}
return binary_op
def to_json_operator(instring, tokensStart, retTokens):
# ARRANGE INTO {op: params} FORMAT
tok = retTokens[0]
op = tok[1]
clean_op = op.lower()
clean_op = binary_ops.get(clean_op, clean_op)
for o in KNOWN_OPS:
if isinstance(o, tuple):
# TRINARY OPS
if o[0].matches(op):
return {clean_op: [tok[0], tok[2], tok[4]]}
elif o.matches(op):
break
else:
if op == COLLATE_NOCASE.match:
op = COLLATE_NOCASE.name
return {op: tok[0]}
else:
raise Exception("not found")
if clean_op == "eq":
if tok[2] == "null":
return {"missing": tok[0]}
elif tok[0] == "null":
return {"missing": tok[2]}
elif clean_op == "neq":
if tok[2] == "null":
return {"exists": tok[0]}
elif tok[0] == "null":
return {"exists": tok[2]}
elif clean_op == "is":
if tok[2] == 'null':
return {"missing": tok[0]}
else:
return {"exists": tok[0]}
operands = [tok[0], tok[2]]
simple = {clean_op: operands}
if len(tok) <= 3:
return simple
if clean_op in {"add", "mul", "and", "or"}:
# ACCUMULATE SUBSEQUENT, IDENTICAL OPS
for i in range(3, len(tok), 2):
if tok[i] != op:
return to_json_operator(None, None, [[simple] + tok[i:]])
else:
operands.append(tok[i+1])
return simple
else:
# SIMPLE BINARY
return to_json_operator(None, None, [[simple] + tok[3:]])
def to_json_call(tokens):
# ARRANGE INTO {op: params} FORMAT
op = tokens["op"].lower()
op = binary_ops.get(op, op)
params = tokens["params"]
if not params:
params = {}
elif isinstance(params, list) and len(params) == 1:
params = params[0]
elif isinstance(params, ParseResults) and params.length() == 1:
params = params[0]
return {op: params}
def filter(self, where=None, limit=None, get_col_map=False):
try:
parsed_query = moz_sql_parser.parse(
self._query.replace('FORMAT JSON', ''))
modified_columns = []
for col, op, value in where or []:
past_where_clause = parsed_query.get('where', {})
op = op.lower()
op_json = binary_ops.get(op, None)
if op_json is None:
print(
f"Operator: {op} not found in the sql parser operator list\n Using it anyway."
)
op_json = op
if op == 'like':
value = '%' + value.strip('%') + '%'
if 'clickhouse' in self.name().lower():
col = f'toString({col})'
elif 'postgres' in self.name().lower():
col = f'{col}::text'
elif 'mariadb' in self.name().lower(
) or 'mysql' in self.name().lower(
) or 'mssql' in self.name().lower():
col = f'CAST({col} AS TEXT)'
modified_columns.append(col)
where_clause = {op_json: [col, value]}
if len(past_where_clause) > 0:
where_clause = {'and': [where_clause, past_where_clause]}
parsed_query['where'] = where_clause
if limit is not None:
parsed_query['limit'] = limit
query = moz_sql_parser.format(parsed_query)
query = query.replace('"', "'")
query = query.replace("'.'", ".")
for col in modified_columns:
if f"'{col}'" in query:
query = query.replace(f"'{col}'", col)
df, col_map = self.query(query)
df, col_map = unnest(df, col_map)
if get_col_map:
return df, col_map
else:
return df
except Exception as e:
print(traceback.format_exc())
print('Failed to filter using SQL: ', e)
return super().filter(where=where,
limit=limit,
get_col_map=get_col_map)