import sys
DEFAULT_EXPRESSION = r"""
while select TransactionLine
group by TransDate
where TransactionLine.TransDate >= fromDate
&& TransactionLine.TransDate <= toDate
&& TransactionLine.Dimension[SysDimension::Department+1] == 'XXX'
join inventTable
where inventTable.ItemId == TransactionLine.ItemId
&& inventTable.ItemGroupId == itemGroupId
"""
def capitalize(name):
return name[0].upper() + name[1:]
def has_affix(s, affix):
return s.endswith(affix) or s.startswith(affix)
class Convert:
FORUPDATE = "forupdate"
COMPARISON_OPERATORS=["==","!=", ">=", "<=", ">", "<", "like"]
OPERATORS=["{", "}","(",")",",",".", '"', "'", ":", "!"]
INDENT = "\t"
NON_EMPTY_VALUE = "<non-empty-value>"
EMPTY_VALUE = "<empty-value>"
# Table Info
TI_TABLE_PAIR = 0
TI_FIELD_LIST = 1
TI_CONDITIONS = 2
TI_ORDERING = 3
TI_FLAGS = 4
TP_NAME = 0
TP_TYPE = 1
CND_FIELD = 0
CND_OP = 1
CND_RHS = 2
def __init__(self):
self.tables = {}
self.output_text = ""
self.collapse_qbr = True
self.qbds_prefix= "qbds"
self.qbr_name = "qbr"
self.query_name = "query"
self.queryRun_name = "queryRun"
self.split_qbds = False
self.need_query_run = True
def expect(self, word):
""" Throw error if next word does not match with given """
w = self.fetch_word()
if w != word:
self.error("expecting `%s`, not `%s`" % (word, w))
return w
def error(self, msg):
""" Throw error and stop conversion """
raise Exception("%d:%d:error:%s" % (self.line, self.col, msg))
def warning(self, msg):
""" Print warning and continue conversion """
sys.stderr.write("%d:%d:warning:%s\n" % (self.line, self.col, msg))
def skip_match(self, st):
""" If next word matches to given, it'll be fetched. Otherwise text position will remain the same"""
pos = self.save_pos()
next = self.fetch_word()
if next != st:
self.load_pos(pos)
return None
return st
def fetch_id(self):
""" Fetch identifier, rewind stream forward """
self.skip_ws()
res = self.skip_ch()
if not res.isalpha() and not res == "_":
self.error("expecting id, got `%s`" % res)
while self.peek_ch().isalnum() or self.peek_ch() == "_":
res += self.skip_ch()
return res
def peek_operator(self, ops=OPERATORS, keep_pos=True):
""" If next word matches to given operators, it will be returned. Otherwise will be returned None.
In any case, stream position will not be changed. """
if keep_pos:
pos = self.save_pos()
self.skip_ws()
for op in ops:
if self.input_text[self.i : self.i + len(op)] == op:
self.i += len(op)
if keep_pos:
self.load_pos(pos)
return op
if keep_pos:
self.load_pos(pos)
return None
def fetch_operator(self,ops=OPERATORS):
""" Fetch identifier, rewind stream forward """
ret = self.peek_operator(ops, False)
if not ret:
self.error("expecting operator from %s, got %s" %( ops, self.peek_word()))
return ret
def peek_word(self):
""" Return next word on stream without rewinding it forward """
pos = self.save_pos()
res = self.fetch_word()
self.load_pos(pos)
return res
def save_pos(self):
""" Return stream position """
return (self.i, self.line, self.col)
def load_pos(self, pos):
""" Set stream position """
(self.i, self.line, self.col) = pos
def fetch_word(self):
""" Get word from stream, rewind it forward """
self.skip_ws()
s=""
op = self.peek_operator()
if op:
self.skip_ch(len(op))
return op
while self.has_more() and not self.peek_ch().isspace() and not self.peek_operator():
s = s + self.skip_ch()
self.skip_ws()
return s
def skip_ws(self):
""" Rewind whitespace characters """
while self.has_more() and self.peek_ch().isspace():
self.skip_ch()
def has_more(self):
""" Return if stream has more data to read """
return self.i < len(self.input_text)
def skip_ch(self, count=1):
""" Skip character(one or several), return first skipped character """
ret = self.input_text[self.i]
while count > 0:
if self.input_text[self.i] == "\n":
self.line += 1
self.col = 0
else:
self.col += 1
self.i += 1
count-=1
return ret
def peek_ch(self):
""" Return next character from stream with no rewinding """
return self.input_text[self.i:self.i+1]
def fetch_table(self):
""" Fetch table name in form name{type} """
table_id = self.fetch_id()
if self.skip_match("{"):
if self.skip_match("}"):
table_type = capitalize(table_id)
else:
table_type = self.fetch_id()
self.expect("}")
else:
table_type = capitalize(table_id)
return (table_id, table_type)
def fetch_select_field(self):
""" Fetch single selection field """
SELECTION_TYPES = {"maxof":', SelectionField::Max',
"minof":', SelectionField::Min',
"sum":', SelectionField::Sum'}
selection_type = ""
field_name = ""
if self.peek_word() in SELECTION_TYPES:
selection_type = SELECTION_TYPES[self.fetch_word()]
self.expect("(")
field_name = self.fetch_id()
self.expect(")")
else:
field_name = self.fetch_id()
return (field_name, selection_type)
def fetch_select_field_list(self):
""" Fetch all selection fields """
if self.skip_match("*"):
self.expect("from")
return []
res = []
while not self.peek_if_table():
res.append(self.fetch_select_field())
if not self.skip_match(","):
break
if res:
self.expect("from")
return res
def peek_if_table(self):
""" Return true if table id/type pair is next on stream """
pos = self.save_pos()
self.fetch_word()
res = self.skip_match("{") != None or self.fetch_word() in ["group", "order", "where", "join"]
self.load_pos(pos)
return res
def parse_dim_id(self):
""" Parse identifier[some value] """
res = self.fetch_id()
self.skip_ws()
if self.peek_ch() != '[':
return res
while self.peek_ch() != ']':
assert self.has_more()
res += self.skip_ch()
res += self.skip_ch()
return res
def parse_full_id(self):
""" Fetch (table, field) id pair"""
id1 = self.fetch_id()
if self.skip_match("."):
if self.skip_match('('):
id1 += ".(" + self.parse_expr()
id1 += self.expect(")")
return (id1, '')
return (id1, self.parse_dim_id())
return (id1,"")
def parse_expr_id(self, expect_table_field = True):
""" Fetch identifier used in where-expression """
res = self.parse_full_id()
if res[1] == "":
str_res = res[0]
res = str_res
else:
str_res = "%s.%s" % res
if not expect_table_field:
res = str_res
if self.peek_ch() == ':':
self.expect(':')
self.expect(':')
res = str_res + "::"
res += self.fetch_id()
return res
def parse_expr_str(self):
""" Fetch string literal """
if self.skip_match('"'):
q = '"'
elif self.skip_match("'"):
q = "'"
else:
self.error("String expected")
res = q
while not self.skip_match(q):
assert self.has_more() and "Not terminated string"
ch = self.skip_ch()
res += ch
if ch == '\\':
assert self.has_more() and "Not terminated string"
res += self.skip_ch()
res += q
return res
def parse_expr_num_date(self):
res = self.skip_ch()
while self.peek_ch().isdigit() or self.peek_ch() in ".\\" :
res += self.skip_ch()
return res
def parse_expr(self, expect_table_field=True):
""" Fetch expression used in where """
self.skip_ws()
if self.peek_ch().isdigit():
res = self.parse_expr_num_date()
elif self.peek_ch() in "\"\'":
res = self.parse_expr_str()
else:
res = self.parse_expr_id(expect_table_field)
# funciton call
if self.skip_match("("):
expr_start = self.i-1
if isinstance(res, tuple):
res = "%s.%s" % res
while not self.skip_match(")"):
assert self.has_more() and "Not terminated expr"
arg = self.parse_expr(False)
self.skip_match(",")
res += self.input_text[expr_start:self.i]
return res
def parse_where_clause(self, main_table):
""" Parse where clause """
table_name = main_table[0]
conditions = []
while True:
if self.skip_match("!"):
table_field = self.parse_full_id()
expr = ""
op = Convert.EMPTY_VALUE
else:
table_field = self.parse_full_id()
if table_field[0] != table_name:
self.error("Expecting table name `%s`, not `%s` in front of where-clause expression" % (table_name, table_field[0]))
self.skip_ws()
if self.peek_ch() == "" or self.peek_ch() == "&":
op = Convert.NON_EMPTY_VALUE
expr = ""
else:
op = self.fetch_operator(Convert.COMPARISON_OPERATORS)
expr = self.parse_expr()
conditions.append((table_field, op, expr))
if not self.skip_match("&&"):
break
return conditions
def parse_sort_field_list(self):
""" Fetch list of fields for group/order by clause """
res = []
while self.has_more():
field_name = self.fetch_id()
mode = ""
if self.skip_match("desc"):
mode = ", SortOrder::Descending"
elif self.skip_match("asc"):
mode = ", SortOrder::Ascending"
res.append((field_name, mode))
if not self.skip_match(","):
break
return res
def skip_explicit_index(self):
if self.skip_match("index"):
self.skip_match("hint")
index_name = self.fetch_word()
self.warning("explicit index/index hint %s is ignored for %s" % (index_name, table[1]))
def parse_single_table_query(self):
""" Parse entire single table variable: selection fields, where clause, sort clause """
flags = []
if self.skip_match("forupdate"):
flags.append(Convert.FORUPDATE)
field_list = self.fetch_select_field_list()
table = self.fetch_table()
self.skip_explicit_index()
order = ("",[])
if self.skip_match("group"):
self.skip_match("by")
order = ("group", self.parse_sort_field_list())
elif self.skip_match("order"):
self.skip_match("by")
order = ("order", self.parse_sort_field_list())
conditions = []
if self.skip_match("where"):
conditions = self.parse_where_clause(table)
return (table, field_list, conditions, order, flags)
def out(self, txt="", skip_indent=False, skip_nl = False):
""" Write text to output stream """
if txt and not skip_indent:
self.output_text += Convert.INDENT
self.output_text += txt
if not skip_nl:
self.output_text += "\n"
def make_field_num(self, table_name, field_name):
""" Generate field num. Use fieldId2ext id if filed_name is too fishy """
if '[' not in field_name:
return "fieldnum(%s, %s)" % (table_name, field_name)
sq_pos = field_name.find('[')
array_index = field_name[sq_pos + 1:]
assert ']' in array_index and "invalid array index"
assert '[' not in array_index and "nested [] not supported"
array_index = array_index[:array_index.find(']')]
field_name = field_name[:sq_pos]
return "fieldId2Ext(fieldnum(%s, %s), %s)" % (table_name, field_name, array_index)
@staticmethod
def normalize_field_name(field):
""" Remove project/country affixes and return in lower case """
field = field.lower()
PREFIX_LENGTH=5
SUFFIX_LENGTH=3
if "_" in field[:PREFIX_LENGTH]:
field = field[field.find("_")+1:]
if "_" in field[-SUFFIX_LENGTH:]:
field = field[field.rfind("_")+1:]
return field.lower()
@staticmethod
def field_looks_like_num(field):
s = Convert.normalize_field_name(field)
return has_affix(s, "qty") or \
has_affix(s, "amount") or \
s.endswith("cur") or \
s.endswith("mst") or \
has_affix(s, "pct") or \
has_affix(s, "percent")
@staticmethod
def field_looks_like_bool(field):
s = Convert.normalize_field_name(field)
return s.startswith("is") or s.startswith("can") or s.endswith("ed")
def get_table_name(self, table_info):
return table_info[Convert.TI_TABLE_PAIR][Convert.TP_NAME]
def get_table_type(self, table_info):
return table_info[Convert.TI_TABLE_PAIR][Convert.TP_TYPE]
def generate_data_source(self, table_info):
is_main_table = self.tables[0] == table_info
table_type = self.get_table_type(table_info)
if is_main_table:
source = self.query_name
else:
if self.split_qbds:
conds = table_info[Convert.TI_CONDITIONS]
if len(conds) > 0 and isinstance(conds[0][Convert.CND_RHS], tuple):
parent_var = conds[0][Convert.CND_RHS][0]
source = self.qbds_prefix + capitalize(parent_var)
else:
error("no link for %s provide" % (self.get_table_name(table_info)))
else:
source = self.qbds_name
self.out("%s = %s.addDataSource(tablenum(%s));" % (self.qbds_name, source, table_type))
if not is_main_table:
self.out("%s.joinMode(JoinMode::InnerJoin);" % self.qbds_name);
self.out("%s.fetchMode(QueryFetchmode::One2One);" % self.qbds_name);
if Convert.FORUPDATE in table_info[Convert.TI_FLAGS]:
self.out("%s.update(true);" % (self.qbds_name))
return table_type
def generate_selection_fields(self, table_info):
select_fields = table_info[Convert.TI_FIELD_LIST]
table_type = self.get_table_type(table_info)
for (field_name, select_mode) in select_fields:
self.out("%s.addSelectionField(%s%s);" % (self.qbds_name, self.make_field_num(table_type, field_name), select_mode))
def generate_ordering(self, table_info):
sort_mode, sort_fields = table_info[Convert.TI_ORDERING]
table_type = self.get_table_type(table_info)
if sort_mode:
if sort_mode == "group":
self.out("%s.orderMode(OrderMode::GroupBy);" % (self.qbds_name))
for (field_name, order) in sort_fields:
self.out("%s.addSortField(%s%s);" % (self.qbds_name, self.make_field_num(table_type, field_name), order))
def generate_neq(self, qbr_name, where, i):
((table_var, field_name), op, expr) = where[i]
exprs = [expr]
this_var = where[i][Convert.CND_FIELD]
while i != len(where) - 1 and where[i+1][Convert.CND_FIELD] == this_var and where[i+1][Convert.CND_OP] == "!=":
i+=1
exprs.append(where[i][Convert.CND_RHS])
if len(exprs) == 1:
if exprs[0] != '""':
self.out("%s.value(queryNotValue(%s));" % (qbr_name, exprs[0]), self.collapse_qbr)
else:
self.out("%s.value(SysQuery::valueNotEmptyString());" % (qbr_name), self.collapse_qbr)
else:
txt = '%s.value(strfmt("' % qbr_name
etxt = ""
for j in xrange(len(exprs)):
if j != 0:
txt += ", "
etxt += ", "
txt += "%%%d" % (j+1)
etxt += "queryNotValue(%s)" % exprs[j]
txt += '", %s));' % etxt
self.out(txt)
return i
def generate_empty_non_empty(self, empty, qbr_name, field_name):
num_str_suffix = "Not" if not empty else ""
bool_value = "NoYes::Yes" if not empty else "NoYes::No"
if self.field_looks_like_num(field_name):
self.out("%s.value(queryValue%s(0));" % (qbr_name, num_str_suffix), self.collapse_qbr)
elif self.field_looks_like_bool(field_name):
self.out("%s.value(queryValue(%s));" % (qbr_name, bool_value), self.collapse_qbr)
else:
self.out("%s.value(SysQuery::value%sEmptyString());" % (qbr_name, num_str_suffix), self.collapse_qbr)
def generate_greater_less(self, op, qbr_name, expr):
if expr.isdigit():
self.out('%s.value("%s%s");' % (qbr_name, op, expr), self.collapse_qbr)
else:
self.out('%s.value(strfmt("%s%%1", queryValue(%s)));' % (qbr_name, op, expr), self.collapse_qbr)
def generate_qbds_names(self):
self.qbds_name = self.qbds_prefix
if not self.split_qbds:
self.out("QueryBuildDataSource %s;" % (self.qbds_name))
return
for table_info in self.tables:
table_name = self.get_table_name(table_info)
self.qbds_name = self.qbds_prefix + capitalize(table_name)
self.out("QueryBuildDataSource %s;" % (self.qbds_name))
NEED_QBR_LITERAL="{{{NEED_QUERY_BUILD_RANGE}}}"
def generate(self):
""" Generate QBDS """
self.out("Query %s;" % (self.query_name))
if self.need_query_run:
self.out("QueryRun %s;" % (self.queryRun_name))
self.generate_qbds_names()
self.output_text+=Convert.NEED_QBR_LITERAL
self.out(";")
self.out("%s = new Query();" % self.query_name)
need_qbr = False
for table_info in self.tables:
self.out()
if self.split_qbds:
self.qbds_name = self.qbds_prefix + capitalize(self.get_table_name(table_info))
self.generate_data_source(table_info)
self.generate_selection_fields(table_info)
self.generate_ordering(table_info)
need_qbr = self.generate_conditions(table_info) or need_qbr
self.output_text = self.output_text.replace(Convert.NEED_QBR_LITERAL,
"%sQueryBuildRange %s;\n" % (Convert.INDENT, self.qbr_name)
if need_qbr
else "")
if self.need_query_run:
self.out()
self.out("%s = new QueryRun(%s);" % (self.queryRun_name, self.query_name))
def make_table_types_map(self):
table_types={}
for (table, select_fields, where, sorting, flags) in self.tables:
table_types[table[0]] = table[1]
return table_types
def generate_conditions(self, table_info):
table_type = self.get_table_type(table_info)
where = table_info[Convert.TI_CONDITIONS]
need_qbr = False
i = 0
table_types = self.make_table_types_map()
while i < len(where):
((table_var, field_name), op, expr) = where[i]
if isinstance(expr, tuple):
(ptable_name, ptable_field) = expr
if ptable_name in table_types and op == "==":
ptable_type = table_types[ptable_name]
self.out("%s.addLink(%s, %s);" % (self.qbds_name,
self.make_field_num(ptable_type, ptable_field),
self.make_field_num(table_type, field_name)))
i += 1
continue
else:
expr = "%s.%s" % expr
need_qbr = True and not self.collapse_qbr
qbr_name = self.qbr_name if not self.collapse_qbr else ""
suffix = ""
if not self.collapse_qbr:
self.out("%s = " % self.qbr_name, False, True)
suffix = ";"
self.out("%s.addRange(%s)%s" % (self.qbds_name, self.make_field_num(table_type, field_name), suffix),
not self.collapse_qbr,
self.collapse_qbr)
if op == "==":
if expr == '""':
self.out("%s.value(SysQuery::valueEmptyString());" % (qbr_name), self.collapse_qbr)
else:
self.out("%s.value(queryValue(%s));" % (qbr_name, expr), self.collapse_qbr)
elif op == "!=":
i = self.generate_neq(qbr_name, where, i)
elif op == "like":
self.out("%s.value(%s);" % (qbr_name, expr), self.collapse_qbr)
elif op == ">":
self.generate_greater_less(">", qbr_name, expr)
elif op == "<":
self.generate_greater_less("<", qbr_name, expr)
elif op == ">=":
printed = False
if i != len(where) - 1 and where[i+1][0] == (table_var, field_name) and where[i+1][1] == "<=":
expr2 = where[i+1][2]
if isinstance(expr2, tuple):
expr2 = "%s.%s" % expr2
self.out("%s.value(queryRange(%s, %s));" % (qbr_name, expr, expr2), self.collapse_qbr)
i += 1
else:
self.out("%s.value(queryRange(%s, ''));" % (qbr_name, expr), self.collapse_qbr)
elif op == "<=":
self.out("%s.value(queryRange('', %s));" % (qbr_name, expr), self.collapse_qbr)
elif op == Convert.NON_EMPTY_VALUE:
self.generate_empty_non_empty(False, qbr_name, field_name)
elif op == Convert.EMPTY_VALUE:
self.generate_empty_non_empty(True, qbr_name, field_name)
else:
self.error("Operator %s NYI in ranges" % op)
i += 1
return need_qbr
def run (self, input_text):
self.input_text = input_text
self.line = 1
self.col = 1
self.i = 0
self.skip_match("while")
self.skip_match("select")
main = self.parse_single_table_query()
tables = [main]
while self.skip_match("join"):
tables.append(self.parse_single_table_query())
self.skip_match(';')
self.expect("")
self.tables = tables
self.generate()
print(self.output_text)
return self.output_text
if __name__ == "__main__":
Convert().run(DEFAULT_EXPRESSION)