def run(self, metadata):
table = common.table_get(metadata, self.object.cube_id.table_id)
if self.object.measure_type == "fact_column":
col = common.col_get(sqlalchemy.Table(self.object.table_name, metadata), self.object.value_column)
col_agregated = agregator[self.object.agregator](col)
else:
scalar = Word(alphanums + "_" + " " + ".")
sql_func = ["sum", "max", "min", "count", "avg"]
arith_operator = ["-", "*", "/", "+"]
sql_function = oneOf(" ".join(sql_func))
leftRdBr = Literal("(").suppress()
rightRdBr = Literal(")").suppress()
operator_arith = oneOf(" ".join(arith_operator))
sqlexpression = (
sql_function.setResultsName("sql_func")
+ leftRdBr
+ delimitedList(scalar, ",", combine=False)
+ rightRdBr
| sql_function
+ leftRdBr
+ scalar
+ ZeroOrMore(operator_arith.setResultsName("arithmetic") + scalar)
+ rightRdBr
)
res = sqlexpression.parseString(self.object.value_sql)
operators = []
cols = []
function = None
for item in res:
if str(item) == res.sql_func:
function = str(item)
elif str(item) == res.arithmetic or str(item) in ["+", "-", "/", "*"]:
operators.append(str(item))
else:
cols.append(common.measure_sql_exp_col(metadata, str(item)))
operations = {"+": operator.add, "-": operator.sub, "/": operator.div, "%": operator.mod, "*": operator.mul}
operators = [operations[name] for name in operators]
ops_cols = zip(operators, cols[1:])
col = reduce(lambda expr, op_col: op_col[0](expr, op_col[1]), ops_cols, cols[0])
if function:
col_agregated = agregator[function](col)
else:
col_agregated = col
return [
{
"value": [(["measures", self.name], self.name, False)],
"query": {"column": [col_agregated]},
"axis_mapping": axis_map.column_fixed(0),
"delta": 0,
"format": self.object.formatstring,
}
]
def run(self, metadata):
table = common.table_get(metadata, self.object.cube_id.table_id)
if self.object.measure_type == 'fact_column':
col = common.col_get(sqlalchemy.Table(self.object.table_name,metadata), self.object.value_column)
col_agregated = agregator[self.object.agregator](col)
else:
scalar = Word(alphanums+"_"+" "+".")
sql_func = ["sum","max","min","count","avg"]
arith_operator = ["-","*","/","+"]
sql_function = oneOf(' '.join(sql_func))
leftRdBr = Literal("(").suppress()
rightRdBr = Literal(")").suppress()
operator_arith = oneOf(' '.join(arith_operator))
sqlexpression = sql_function.setResultsName('sql_func') + leftRdBr + delimitedList(scalar,",",combine=False) + rightRdBr | sql_function + leftRdBr + scalar + ZeroOrMore(operator_arith.setResultsName('arithmetic') + scalar) + rightRdBr
res = sqlexpression.parseString(self.object.value_sql)
operators = []
cols = []
function = None
for item in res:
if str(item) == res.sql_func:
function = str(item)
elif str(item) == res.arithmetic or str(item) in ["+","-","/","*"]:
operators.append(str(item))
else:
cols.append(common.measure_sql_exp_col(metadata,str(item)))
operations = {
'+':operator.add,
'-':operator.sub,
'/':operator.div,
'%':operator.mod,
'*':operator.mul,
}
operators = [operations[name] for name in operators]
ops_cols = zip(operators, cols[1:])
col = reduce(lambda expr, op_col: op_col[0](expr, op_col[1]), ops_cols,cols[0])
if function:
col_agregated = agregator[function](col)
else:
col_agregated = col
return [ {
'value': [(['measures',self.name], self.name, False)],
'query': {
'column': [col_agregated]
},
'axis_mapping': axis_map.column_fixed(0),
'delta': 0,
'format':self.object.formatstring
} ]
def _run_transform(self, metadata):
"""
Compute axis values, and transform the main query according to axis
values for this subset. The main query slicer is transformed to a
single condition like: foreign_key in (...) and reapplied to axis
values.
"""
table, axis, mapping, result_axis = self._compute_axis(metadata, True)
table2 = common.table_get(metadata, self.object.dimension_id.cube_id.table_id)
sql_table = sqlalchemy.Table(self.object.table_id.column_link_id.table_id.table_db_name,metadata)
col = common.col_get(sql_table, self.object.table_id.column_link_id)
#
# The result to be applied to the main query object
#
result = []
for a in axis:
k = tuple(list(a)[0][1:])
mapping_axis = filter(lambda m: tuple(list(m)[:-1])==k, mapping)
primary_keys = map(lambda x:list(x)[-1], mapping_axis)
# To convert everything in to the string so that no conversion needed at later stage
# This is for the elements to be displayed in the rows and columns
for i in range(len(a[0])):
if a[0][i]:
if isinstance(a[0][i],int):
a[0][i] = str(a[0][i])
elif isinstance(a[0][i],float):
a[0][i] = str(int(a[0][i]))
else:
a[0][i] = '/'
a = list(a)
if isinstance(a[-1],int):
a[-1] = str(a[-1])
elif isinstance(a[-1],float):
a[-1] = str(int(a[-1]))
a = tuple(a)
primary_keys = map(lambda x: str(x),primary_keys)
result.append( {
'value': [a],
'query': {
'whereclause': [col.in_(primary_keys)],#.extend(result_axis['where_clause']),
'column': [sqlalchemy.literal('transform')],
},
'axis_mapping': axis_map.column_static(False),
'delta':0,
})
return result
def run(self, metadata):
table = common.table_get(metadata, self.object.table_id)
return sqlalchemy.select(from_obj=[table], columns=[]), table
def _compute_axis(self, metadata, primarykey=False):
"""
Compute axis values, and transform the main query in the same way
to add this branch of the star for this subset. The mappers
reassign to values.
"""
#
# Build a query on all tables of the hierarchy
# It passes the hierarchy table_id browse object (olap_cube_table )
#
table = common.table_get(metadata, self.object.table_id)
#
# Implement the query for all sublevels
# Return:
# [ (ID1, ID2, ID3, NAME, [PRIMARYKEY]) ]
#
result_axis = {}
for slevel in self.sublevels:
res = slevel.run(metadata, table)
for key in res.keys():
result_axis.setdefault(key, [])
result_axis[key] += res[key]
cols = []
axis_select = sqlalchemy.select(from_obj=[table], columns=[], distinct=True)
for where in result_axis.get('where_clause', []):
axis_select.append_whereclause(where)
for col in result_axis.get('column', []):
axis_select.append_column(col)
for group in result_axis.get('group_by', []):
axis_select.append_group_by(group)
# TODO: find a way to do: axis_select2 = axis_select.copy()
axis_select2 = sqlalchemy.select(from_obj=[table], columns=[], distinct=True)
for where in result_axis.get('where_clause', []):
axis_select2.append_whereclause(where)
for col in result_axis.get('column', []):
axis_select2.append_column(col)
for group in result_axis.get('group_by', []):
axis_select2.append_group_by(group)
# TODO: end
#metadata.bind.echo = True
axis_select2.append_column(result_axis['column_name'][-1].label('axis_name'))
query = axis_select2.execute()
result = query.fetchall()
def _tuple_define(x):
y=list(x)
if y[-1] == None:
y[-1] = '/'
elif isinstance(y[-1],float):
y[-1] = str (int(y[-1]))
else:
y[-1] = self._to_unicode(y[-1])
y[-1] = tools.ustr(y[-1])
return ([self.level]+y[:-1]),y[-1]
axis = map(_tuple_define, result)
# Gives the mapping
primary_key = ''
if primarykey:
if self.object.table_id.column_link_id.related_to:
primary_key = self.object.table_id.column_link_id.related_to
else:
primary_key = self.object.table_id.column_link_id.table_id
tableprim = sqlalchemy.Table(primary_key.table_db_name, metadata)
pk = common.get_primary_key(primary_key)
col = common.col_get(tableprim,pk)
axis_select.append_column(col.label('axis_primarykey'))
query = axis_select.execute()
result = query.fetchall()
else:
# To find the primary key that suits best as per the given criteria
raise 'Primary key table not made in the hierarchy'
maps = []
position =0
for mapping in result_axis['axis_mapping']:
mapinst = mapping(result, position)
maps.append(mapinst)
position += mapinst.position_get()
result_axis['axis_mapping'] = maps
#
# #
# # Apply the filters on the main query object due to this block
# #
#
# maps = []
# position =0
# for mapping in result_axis['axis_mapping']:
# mapinst = mapping(result, position)
# maps.append(mapinst)
# position += mapinst.position_get()
# result_axis['axis_mapping'] = maps
# print '*'*50
# print table, result, result_axis
return table, axis, result ,result_axis
def run(self, metadata):
table = common.table_get(metadata, self.object.table_id)
return sqlalchemy.select(from_obj = [table], columns=[]),table