summaryrefslogtreecommitdiffstats
path: root/bijoe/engine.py
diff options
context:
space:
mode:
Diffstat (limited to 'bijoe/engine.py')
-rw-r--r--bijoe/engine.py144
1 files changed, 82 insertions, 62 deletions
diff --git a/bijoe/engine.py b/bijoe/engine.py
index 80f21d2..6a2ad7f 100644
--- a/bijoe/engine.py
+++ b/bijoe/engine.py
@@ -60,24 +60,38 @@ class EngineDimension(object):
@property
def members(self):
assert self.type != 'date'
+ value = self.value
+ value_label = self.value_label or value
+ order_by = self.order_by
+
with self.engine.get_cursor() as cursor:
sql = self.members_query
if not sql:
- if self.dimension.join:
- join = self.engine_cube.get_join(self.dimension.join[-1])
- sql = ('SELECT %s AS value, %s::text AS label FROM %s AS "%s" '
- 'GROUP BY %s, %s ORDER BY %s' % (
- self.value, self.value_label or self.value, join.table, join.name,
- self.value, self.value_label or self.value, self.order_by or self.value))
+ table_expression = self.engine_cube.fact_table
+ if self.join:
+ table_expression = self.engine_cube.build_table_expression(
+ self.join, self.engine_cube.fact_table)
+ sql = 'SELECT %s AS value, %s::text AS label ' % (value, value_label)
+ sql += 'FROM %s ' % table_expression
+ if order_by:
+ if not isinstance(order_by, list):
+ order_by = [order_by]
else:
- sql = ('SELECT %s AS value, %s::text AS label FROM {fact_table} '
- 'GROUP BY %s, %s ORDER BY %s' % (
- self.value, self.value_label or self.value, self.value,
- self.value_label or self.value, self.order_by or self.value))
+ order_by = [value]
+ group_by = [value]
+ if value_label not in group_by:
+ group_by.append(value_label)
+ for order_value in order_by:
+ if order_value not in group_by:
+ group_by.append(order_value)
+ sql += 'GROUP BY %s ' % ', '.join(group_by)
+ sql += 'ORDER BY (%s) ' % ', '.join(order_by)
sql = sql.format(fact_table=self.engine_cube.fact_table)
self.engine.log.debug('SQL: %s', sql)
cursor.execute(sql)
for row in cursor.fetchall():
+ if row[0] is None:
+ continue
yield Member(*row)
@@ -196,48 +210,6 @@ class ProxyListDescriptor(object):
return ProxyList(obj.engine, obj, self.attribute, self.cls, chain=self.chain)
-def build_table_expression(join_tree, table_name, alias=None, top=True, other_conditions=None):
- '''Recursively build the table expression from the join tree,
- starting from the fact table'''
-
- sql = table_name
- if alias:
- sql += ' AS "%s"' % alias
- add_paren = False
- for kind in ['left', 'inner', 'right', 'full']:
- joins = join_tree.get(table_name, {}).get(kind)
- if not joins:
- continue
- add_paren = True
- join_kinds = {
- 'inner': 'INNER JOIN',
- 'left': 'LEFT OUTER JOIN',
- 'right': 'RIGHT OUTER JOIN',
- 'full': 'FULL OUTER JOIN',
- }
- sql += ' %s ' % join_kinds[kind]
- sub_joins = []
- conditions = []
- if other_conditions:
- conditions = other_conditions
- other_conditions = None
- for join_name, join in joins.iteritems():
- sub_joins.append(
- build_table_expression(join_tree, join.table, join.name, top=False))
- conditions.append('"%s".%s = "%s"."%s"' % (
- alias or table_name,
- join.master.split('.')[-1],
- join.name, join.detail))
- sub_join = ' CROSS JOIN '.join(sub_joins)
- if len(sub_joins) > 1:
- sub_join = '(%s)' % sub_join
- sql += sub_join
- sql += ' ON %s' % ' AND '.join(conditions)
- if not top and add_paren:
- sql = '(%s)' % sql
- return sql
-
-
class EngineCube(object):
dimensions = ProxyListDescriptor('all_dimensions', EngineDimension, chain=JSONDimensions)
measures = ProxyListDescriptor('measures', EngineMeasure)
@@ -293,7 +265,11 @@ class EngineCube(object):
projections.append('%s AS %s' % (dimension.value_label or dimension.value,
dimension.name))
group_by.append(dimension.group_by or dimension.value)
- order_by.append(dimension.order_by or dimension.value)
+ order_by.extend(dimension.order_by or [dimension.value])
+
+ for order_value in order_by:
+ if order_value not in group_by:
+ group_by.append(order_value)
for measure_name in measures:
measure = self.get_measure(measure_name)
@@ -302,15 +278,8 @@ class EngineCube(object):
sql = 'SELECT ' + ', '.join(projections)
table_expression = ' %s' % self.cube.fact_table
if joins:
- join_tree = {}
- # Build join tree
- for join_name in joins:
- join = self.get_join(join_name)
- master_table = join.master_table or self.fact_table
- join_tree.setdefault(master_table, {}).setdefault(join.kind, {})[join.name] = join
- table_expression = build_table_expression(join_tree,
- self.fact_table,
- other_conditions=join_conditions)
+ table_expression = self.build_table_expression(
+ joins, self.fact_table, other_conditions=join_conditions)
sql += ' FROM %s' % table_expression
where_conditions = 'true'
if where:
@@ -349,6 +318,57 @@ class EngineCube(object):
'value': value,
} for cell, value in zip(cells, row)]
+ def build_table_expression(self, joins, table_name, other_conditions=None):
+ '''Recursively build the table expression from the join tree,
+ starting from the fact table'''
+
+ join_tree = {}
+ # Build join tree
+ for join_name in joins:
+ join = self.get_join(join_name)
+ master_table = join.master_table or self.fact_table
+ join_tree.setdefault(master_table, {}).setdefault(join.kind, {})[join.name] = join
+
+ def build_table_expression_helper(join_tree, table_name, alias=None, top=True, other_conditions=None):
+ sql = table_name
+ if alias:
+ sql += ' AS "%s"' % alias
+ add_paren = False
+ for kind in ['left', 'inner', 'right', 'full']:
+ joins = join_tree.get(alias or table_name, {}).get(kind)
+ if not joins:
+ continue
+ add_paren = True
+ join_kinds = {
+ 'inner': 'INNER JOIN',
+ 'left': 'LEFT OUTER JOIN',
+ 'right': 'RIGHT OUTER JOIN',
+ 'full': 'FULL OUTER JOIN',
+ }
+ sql += ' %s ' % join_kinds[kind]
+ sub_joins = []
+ conditions = []
+ if other_conditions:
+ conditions = other_conditions
+ other_conditions = None
+ for join_name, join in joins.iteritems():
+ sub_joins.append(
+ build_table_expression_helper(join_tree, join.table, alias=join.name, top=False))
+ conditions.append('"%s".%s = "%s"."%s"' % (
+ alias or table_name,
+ join.master.split('.')[-1],
+ join.name, join.detail))
+ sub_join = ' CROSS JOIN '.join(sub_joins)
+ if len(sub_joins) > 1:
+ sub_join = '(%s)' % sub_join
+ sql += sub_join
+ sql += ' ON %s' % ' AND '.join(conditions)
+ if not top and add_paren:
+ sql = '(%s)' % sql
+ return sql
+ return build_table_expression_helper(
+ join_tree, table_name, other_conditions=other_conditions)
+
class Engine(object):
def __init__(self, warehouse):