1. Olemis Lang
  2. trac-gviz

Commits

Olemis Lang  committed 0bccb29 Merge

TracGViz : Merge 56bed13e79c17edf12ccb95f incorporating GROUP BY clause (GViz QL)

Status :

tracgviz.gvizql ... [ok]
tracgviz.testing.test_rpc ... [ok]
tracgviz.testing.test_gviz ... (failures=7, errors=79)

  • Participants
  • Parent commits 56bed13, 7edf12c
  • Branches default

Comments (0)

Files changed (6)

File trac-dev/gviz/TODO

View file
  • Ignore whitespace
 
 X GViz API QL: Implement `order by` clause.
 
+X GViz API QL: Implement `group by` clause.
+
+X GViz API QL: Match types for column supplied in to aggregate functions.
+
 - Add `limit` parameter for history methods in VersionControlRPC.
 
 - Implement data source provider for Ticket->Query
 
 - Implement `create`, `update`, and `delete` methods in ReportRPC
 
-- GViz API QL: Add tasks to support `group by`, 
-  `pivot`, and `options` clauses.
+- GViz API QL: Add tasks to support `pivot`, and `options` clauses.
+
 
 - Add an option in GViz milestones in order to only show data about 
   active milestones.

File trac-dev/gviz/tracgviz/aggregate.py

View file
  • Ignore whitespace
 Licensed under the Apache License, Version 2.0 
 """
 __author__ = 'Olemis Lang'
-__all__ = 'avg count max min sum'.split()
+__all__ = ['agg_' + nm for nm in 'avg count max min sum'.split()]
+
+from itertools import count, ifilter, izip
+from math import fsum
+
+from tracgviz.api import GVizRuntimeError
 
 def notimpl(*args, **kwds):
   raise NotImplementedError('Aggregation not supported ... yet')
 
-avg = lambda *args, **kwds: notimpl(*args, **kwds)
-avg.return_type = 'number'
+def _iter_col(rows, getvalue, colnm):
+  # FIXME: Always wrap using ifilter ?
+  return ifilter(lambda x: x is not None, (getvalue(r, colnm) for r in rows))
 
-count = lambda *args, **kwds: notimpl(*args, **kwds)
-count.return_type = 'number'
+def agg_avg(rows, getvalue, colnm):
+  # Ignore null values
+  dataset = _iter_col(rows, getvalue, colnm)
+  c = [0]
+  try:
+    s = fsum(c.__setitem__(0, idx) or value \
+        for idx, value in izip(count(1), dataset))
+  except TypeError:
+    raise GVizRuntimeError('Numeric column is required')
+  c = c[0]
+  return None if c == 0 else s/c
 
-max = lambda *args, **kwds: notimpl(*args, **kwds)
+agg_avg.return_type = 'number'
+agg_avg.types = ('number',)
 
-min = lambda *args, **kwds: notimpl(*args, **kwds)
+def agg_count(rows, getvalue, colnm):
+  idx = 0
+  dataset = _iter_col(rows, getvalue, colnm)
+  for idx, _ in izip(count(1), dataset):
+    pass
+  return idx
 
-sum = lambda *args, **kwds: notimpl(*args, **kwds)
-sum.return_type = 'number'
+agg_count.return_type = 'number'
 
+def agg_max(rows, getvalue, colnm):
+  dataset = _iter_col(rows, getvalue, colnm)
+  try:
+    return max(dataset)
+  except ValueError:
+    # max() arg is an empty sequence
+    return None
 
+def agg_min(rows, getvalue, colnm):
+  dataset = _iter_col(rows, getvalue, colnm)
+  try:
+    return min(dataset)
+  except ValueError:
+    # max() arg is an empty sequence
+    return None
+
+def agg_sum(rows, getvalue, colnm):
+  dataset = _iter_col(rows, getvalue, colnm)
+  try:
+    return fsum(dataset)
+  except TypeError:
+    raise GVizRuntimeError('Numeric column is required')
+
+agg_sum.return_type = 'number'
+agg_sum.types = ('number',)
+
+

File trac-dev/gviz/tracgviz/api.py

View file
  • Ignore whitespace
     GVizQL expression.
     """
 
+class GVizUnknownColumn(GVizInvalidQuery):
+    r"""Unknown column name specified in query.
+    """
+
 class GVizIllegalPattern(GVizException):
     r"""Exception raised when an invalid, or incorrect formatting 
     pattern has been specified by the end-user.

File trac-dev/gviz/tracgviz/gvizql.py

View file
  • Ignore whitespace
 from weakref import proxy
 
 from tracgviz.api import DataTable
+from tracgviz.util import groupby
 from tracgviz.util.parsing import OperatorPrecedenceParser,  \
     InvalidParserConfiguration
 
   ... as well as evaluation order.
 
   >>> ','.join(ct.get_props('keyw') for ct in GVizQLClauseType.itereval())
-  'from,group by,pivot,where,order by,offset,limit,select,format,options,label'
+  'from,where,group by,pivot,order by,offset,limit,select,format,options,label'
   """
   CLAUSE_CACHE = dict()
   SYNTAX_ORDER = list()
       try:
         return row[schcols[colnm]]
       except KeyError:
-        raise GVizRuntimeError("Unknown column " + colnm + ".")
+        if isinstance(row, Tuple) and \
+            any(c[0] == colnm for c in getattr(row, '__schema__', [])):
+          # Selecting column in original schema but not in GROUP BY clause
+          raise GVizRuntimeError("Column [%s] should be added to GROUP BY, "
+              "removed from SELECT, or aggregated in SELECT." % (colnm,) )
+        else:
+          raise GVizUnknownColumn("Unknown column " + colnm + ".")
 
     return get_col_value
 
     r"""Functor returning a callable providing access to columns schema 
     by column name.
     """
+    if schema is None:
+      return lambda colnm: (str(colnm), None)
+
     schcols = dict([col[0], i] for i, col in enumerate(schema))
 
     def get_col_schema(colnm):
       try:
         return schema[schcols[colnm]]
       except KeyError:
-        raise GVizRuntimeError("Unknown column " + colnm + ".")
+        if isinstance(schema, Sequence) and \
+            any(c[0] == colnm for c in getattr(schema, '__schema__', [])):
+          # Selecting column in original schema but not in GROUP BY clause
+          raise GVizRuntimeError("Column [%s] should be added to GROUP BY, "
+              "removed from SELECT, or aggregated in SELECT." % (colnm,) )
+        else:
+          raise GVizUnknownColumn("Unknown column " + colnm + ".")
 
+    get_col_schema.__schema__ = schema
     return get_col_schema
 
   @staticmethod
     elif not isinstance(seq, Sequence):
       seq = Sequence([seq])
     self.cols_mapping = seq
-    logging.debug("Select list %s", seq)
     self.cols = [self._resolve_schema(c, lambda colnm: (str(colnm), None))[0] 
         for c in self.cols_mapping ] if seq is not None else None
 
         " from the language.")
 
 class GVizWhereClause(GVizQLClauseHandler):
-  _PROPS = {'idx_syntax' : 2, 'idx_eval': 3, 'keyw' : 'where'}
+  _PROPS = {'idx_syntax' : 2, 'idx_eval': 1, 'keyw' : 'where'}
   KEYWORDS = ('where',)
 
   def __init__(self, expr):
     return schema, new_data
 
 class GVizGroupByClause(GVizQLClauseHandler):
-  _PROPS = {'idx_syntax' : 3, 'idx_eval': 1, 'keyw' : 'group by'}
+  _PROPS = {'idx_syntax' : 3, 'idx_eval': 2, 'keyw' : 'group by'}
   KEYWORDS = ('group', 'by')
 
   def __init__(self, seq):
   def transform(self, schema, data):
     r"""Create groups.
     """
-    self.unsupported()
+    get_col_value = self._column_accessor(schema)
+    get_col_schema = self._schema_accessor(schema)
+    new_schema = Sequence(self._resolve_schema(c, get_col_schema) \
+        for c in self.groups_def)
+    new_schema.__schema__ = schema
+    groups = groupby(data, 
+        key=lambda row: Tuple(self._eval_expr(row, c, get_col_value) \
+            for c in self.groups_def)
+      )
+    new_data = (setattr(newrow, '__schema__', schema) or \
+        setattr(newrow, '__group__', group) or newrow \
+            for newrow, group in groups)
+    return new_schema, new_data
 
 class GVizPivotClause(GVizQLClauseHandler):
-  _PROPS = {'idx_syntax' : 4, 'idx_eval': 2, 'keyw' : 'pivot'}
+  _PROPS = {'idx_syntax' : 4, 'idx_eval': 3, 'keyw' : 'pivot'}
   KEYWORDS = ('pivot',)
 
   def __init__(self, seq):
 
   def _formatter(self, colnm, col, pattern):
     if col is None:
-      raise LookupError("Unknown column '%s'" % (colnm,))
+      raise GVizUnknownColumn("Unknown column '%s'" % (colnm,))
     coltype = col[1]
     if coltype == 'boolean':
       pattern = pattern.encode('utf-8').rsplit(':', 1)
 class Sequence(list):
   pass
 
+class Tuple(tuple):
+  pass
+
 class GVizQLExpressionBuilder:
   r"""Stub class responsible for handling parser callback invocations 
   in order to build instances of `GVizQLExpression`.
   # Special markers 
 
   Sequence = Sequence
+  Tuple = Tuple
 
   @property
   def SyntaxError(self):
     colnm = colnm[1]
     col_schema = colnm['schema'](lambda colnm: (str(colnm), None) )
     try:
-      f = self.agg_funcs[funcnm]
+      f = self.agg_funcs['agg_' + funcnm]
     except KeyError:
       raise LookupError('Unknown aggregation function %s' % (funcnm,))
     return_type = getattr(f, 'return_type', None)
     func_label = '%s(%s)' % (funcnm, col_schema[0])
+
+    def eval_aggregate(row, getvalue):
+      if not isinstance(row, self.Tuple):
+        raise GVizRuntimeError("%s invoked without GROUP BY, PIVOT" % (funcnm,))
+      else:
+        get_group_value = GVizQLClauseHandler._column_accessor(row.__schema__)
+        try:
+          return f(row.__group__, get_group_value, col_schema[0])
+        except GVizUnknownColumn:
+          # Target column is not available
+          raise GVizUnknownColumn('%s(%s) : No such column in GROUP BY, PIVOT'% \
+              (funcnm, col_schema[0]) )
+        except GVizRuntimeError, exc:
+          raise GVizRuntimeError(func_label + ' : ' + str(exc))
+
+    if not return_type:
+      def agg_schema(get_col_schema):
+        schema = getattr(get_col_schema, '__schema__', None)
+        if isinstance(schema, self.Sequence):
+          # In case of aggregate function with GROUP BY, PIVOT use base schema
+          # Otherwise avoid failing to expose schema even if statement is wrong
+          schema = getattr(schema, '__schema__', None)
+        get_group_col_schema = GVizQLClauseHandler._schema_accessor(schema)
+            
+        try:
+          return (func_label, get_group_col_schema(col_schema[0])[1])
+        except GVizUnknownColumn:
+          # Target column is not available
+          raise GVizRuntimeError('%s(%s) : No such column in GROUP BY, PIVOT'% \
+              (funcnm, col_schema[0]) )
+        except GVizRuntimeError, exc:
+          raise GVizRuntimeError(func_label + ' : ' + str(exc))
+
     return dict(
-        eval=self._unsupported, 
-        schema=(func_label, return_type) if return_type else 
-            lambda get_col_schema: (func_label, get_col_schema(col_schema[0])[1]),
+        eval=eval_aggregate,
+        schema=(func_label, return_type) if return_type else agg_schema,
         is_eval_callable=True,
         is_schema_callable=not bool(return_type)
       )
 # Imports introducing circular reference with tracgviz.api
 
 from tracgviz.api import GVizUnsupportedQueryOp, GVizInvalidQuery, \
-    GVizRuntimeError
+    GVizRuntimeError, GVizUnknownColumn
 

File trac-dev/gviz/tracgviz/testing/test_gvizql.py

View file
  • Ignore whitespace
       *****
       * Result
       *****
-      GVizRuntimeError  :  Unknown column group.
+      GVizUnknownColumn  :  Unknown column group.
 
       >>> parse("select dept, salary  ", 'cols')
       *****
       *****
       * Result
       *****
-      GVizRuntimeError  :  Unknown column email address.
+      GVizUnknownColumn  :  Unknown column email address.
 
       >>> parse("select lunchTime , name", 'cols')
       *****
       = Columns =
       max(salary) number
       <BLANKLINE>
-      NotImplementedError  :  Feature not supported ... yet
+      GVizRuntimeError  :  max invoked without GROUP BY, PIVOT
 
       """,
   'Parsing FROM' : r"""
       isSenior boolean
       seniorityStartTime datetime
       <BLANKLINE>
-      GVizRuntimeError  :  Unknown column suffix.
+      GVizUnknownColumn  :  Unknown column suffix.
 
 
       >>> parse("where country matches '.*ia'", 'filter')
       isSenior boolean
       seniorityStartTime datetime
       <BLANKLINE>
-      GVizRuntimeError  :  Unknown column country.
+      GVizUnknownColumn  :  Unknown column country.
 
       >>> parse("where name like 'fre%'", 'filter')
       ...
       *****
       * Result
       *****
-      GVizUnsupportedQueryOp  :  Unable to evaluate GROUP BY clause. Either the whole clause or an specific feature is not supported yet.
-
+      = Columns =
+      dept string
+      = Row =
+         dept = Eng
+      = Row =
+         dept = Sales
+      = Row =
+         dept = Marketing
 
       >>> parse("select dept, max(salary) group by   dept   ", \
       ...           'aggregate', 'cols')
       *****
       * Result
       *****
-      GVizUnsupportedQueryOp  :  Unable to evaluate GROUP BY clause. Either the whole clause or an specific feature is not supported yet.
-
+      = Columns =
+      dept string
+      max(salary) number
+      = Row =
+         dept = Eng
+         max(salary) = 1000
+      = Row =
+         dept = Sales
+         max(salary) = 400
+      = Row =
+         dept = Marketing
+         max(salary) = 800
 
       >>> parse("select lunchTime, avg(salary), count(age) " \
       ...       "group by    isSenior,lunchTime", \
       *****
       * Result
       *****
-      GVizUnsupportedQueryOp  :  Unable to evaluate GROUP BY clause. Either the whole clause or an specific feature is not supported yet.
+      = Columns =
+      lunchTime timeofday
+      avg(salary) number
+      count(age) number
+      = Row =
+         lunchTime = 12:00:00
+         avg(salary) = 700.0
+         count(age) = 2
+      = Row =
+         lunchTime = 13:00:00
+         avg(salary) = 600.0
+         count(age) = 1
+      = Row =
+         lunchTime = 12:00:00
+         avg(salary) = 425.0
+         count(age) = 2
+      = Row =
+         lunchTime = 13:00:00
+         avg(salary) = 800.0
+         count(age) = 1
 
       """,
   'Parsing GROUP BY (complex)' : r"""
+      >>> parse("select isSenior, max(salary) group by isSenior", 
+      ...       'aggregate', 'cols')
+      ...
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved select
+      Token.Name.Variable isSenior
+      Token.Punctuation ,
+      Token.Name.Builtin max
+      Token.Punctuation (
+      Token.Name.Variable salary
+      Token.Punctuation )
+      Token.Keyword.Reserved group by
+      Token.Name.Variable isSenior
+      *****
+      * Parsing
+      *****
+      ['isSenior']
+      ['isSenior', 'max(salary)']
+      *****
+      * Result
+      *****
+      = Columns =
+      isSenior boolean
+      max(salary) number
+      = Row =
+         isSenior = False
+         max(salary) = 600
+      = Row =
+         isSenior = True
+         max(salary) = 1000
+
+      >>> parse("select dept, avg(salary) group by dept",
+      ...       'aggregate', 'cols')
+      ...
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved select
+      Token.Name.Variable dept
+      Token.Punctuation ,
+      Token.Name.Builtin avg
+      Token.Punctuation (
+      Token.Name.Variable salary
+      Token.Punctuation )
+      Token.Keyword.Reserved group by
+      Token.Name.Variable dept
+      *****
+      * Parsing
+      *****
+      ['dept']
+      ['dept', 'avg(salary)']
+      *****
+      * Result
+      *****
+      = Columns =
+      dept string
+      avg(salary) number
+      = Row =
+         dept = Eng
+         avg(salary) = 700.0
+      = Row =
+         dept = Sales
+         avg(salary) = 375.0
+      = Row =
+         dept = Marketing
+         avg(salary) = 800.0
+
+      >>> parse("select dept, sum(salary) group by dept",
+      ...       'aggregate', 'cols')
+      ...
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved select
+      Token.Name.Variable dept
+      Token.Punctuation ,
+      Token.Name.Builtin sum
+      Token.Punctuation (
+      Token.Name.Variable salary
+      Token.Punctuation )
+      Token.Keyword.Reserved group by
+      Token.Name.Variable dept
+      *****
+      * Parsing
+      *****
+      ['dept']
+      ['dept', 'sum(salary)']
+      *****
+      * Result
+      *****
+      = Columns =
+      dept string
+      sum(salary) number
+      = Row =
+         dept = Eng
+         sum(salary) = 2100.0
+      = Row =
+         dept = Sales
+         sum(salary) = 750.0
+      = Row =
+         dept = Marketing
+         sum(salary) = 800.0
+
+      >>> parse(r'''select dept, count(isSenior) group by dept '''
+      ...       '''where isSenior = true''',
+      ...       'aggregate', 'filter', 'cols')
+      ...
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved select
+      Token.Name.Variable dept
+      Token.Punctuation ,
+      Token.Name.Builtin count
+      Token.Punctuation (
+      Token.Name.Variable isSenior
+      Token.Punctuation )
+      Token.Keyword.Reserved group by
+      Token.Name.Variable dept
+      Token.Keyword.Reserved where
+      Token.Name.Variable isSenior
+      Token.Operator.Comparison =
+      Token.Keyword.Constant true
+      *****
+      * Parsing
+      *****
+      ['dept']
+      isSenior = true
+      ['dept', 'count(isSenior)']
+      *****
+      * Result
+      *****
+      = Columns =
+      dept string
+      count(isSenior) number
+      = Row =
+         dept = Eng
+         count(isSenior) = 1
+      = Row =
+         dept = Sales
+         count(isSenior) = 1
+      = Row =
+         dept = Marketing
+         count(isSenior) = 1
+
+      >>> parse("select isSenior, min(salary) group by isSenior",
+      ...       'aggregate', 'cols')
+      ...
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved select
+      Token.Name.Variable isSenior
+      Token.Punctuation ,
+      Token.Name.Builtin min
+      Token.Punctuation (
+      Token.Name.Variable salary
+      Token.Punctuation )
+      Token.Keyword.Reserved group by
+      Token.Name.Variable isSenior
+      *****
+      * Parsing
+      *****
+      ['isSenior']
+      ['isSenior', 'min(salary)']
+      *****
+      * Result
+      *****
+      = Columns =
+      isSenior boolean
+      min(salary) number
+      = Row =
+         isSenior = False
+         min(salary) = 350
+      = Row =
+         isSenior = True
+         min(salary) = 400
+
+      """,
+  'Parsing GROUP BY (type check for aggregation functions)' : r"""
+      >>> parse("  select isSenior, sum(dept) group by isSenior",
+      ...       'aggregate', 'cols')
+      ... 
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved select
+      Token.Name.Variable isSenior
+      Token.Punctuation ,
+      Token.Name.Builtin sum
+      Token.Punctuation (
+      Token.Name.Variable dept
+      Token.Punctuation )
+      Token.Keyword.Reserved group by
+      Token.Name.Variable isSenior
+      *****
+      * Parsing
+      *****
+      ['isSenior']
+      ['isSenior', 'sum(dept)']
+      *****
+      * Result
+      *****
+      = Columns =
+      isSenior boolean
+      sum(dept) number
+      <BLANKLINE>
+      GVizRuntimeError  :  sum(dept) : Numeric column is required
+
+      >>> parse("  select isSenior, avg(name) group by isSenior",
+      ...       'aggregate', 'cols')
+      ... 
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved select
+      Token.Name.Variable isSenior
+      Token.Punctuation ,
+      Token.Name.Builtin avg
+      Token.Punctuation (
+      Token.Name.Variable name
+      Token.Punctuation )
+      Token.Keyword.Reserved group by
+      Token.Name.Variable isSenior
+      *****
+      * Parsing
+      *****
+      ['isSenior']
+      ['isSenior', 'avg(name)']
+      *****
+      * Result
+      *****
+      = Columns =
+      isSenior boolean
+      avg(name) number
+      <BLANKLINE>
+      GVizRuntimeError  :  avg(name) : Numeric column is required
+
+      """,
+  'Parsing GROUP BY (failures)' : r"""
+      >>> parse("  select dept , lunchTime   group by dept "
+      ...       "   order by dept", 'sort', 'aggregate', 'cols')
+      ... 
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved select
+      Token.Name.Variable dept
+      Token.Punctuation ,
+      Token.Name.Variable lunchTime
+      Token.Keyword.Reserved group by
+      Token.Name.Variable dept
+      Token.Keyword.Reserved order by
+      Token.Name.Variable dept
+      *****
+      * Parsing
+      *****
+      [('dept', True)]
+      ['dept']
+      ['dept', 'lunchTime']
+      *****
+      * Result
+      *****
+      GVizRuntimeError  :  Column [lunchTime] should be added to GROUP BY, removed from SELECT, or aggregated in SELECT.
+
+      >>> parse("  select max(salary)", 'cols')
+      ... 
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved select
+      Token.Name.Builtin max
+      Token.Punctuation (
+      Token.Name.Variable salary
+      Token.Punctuation )
+      *****
+      * Parsing
+      *****
+      ['max(salary)']
+      *****
+      * Result
+      *****
+      = Columns =
+      max(salary) number
+      <BLANKLINE>
+      GVizRuntimeError  :  max invoked without GROUP BY, PIVOT
+
       """,
   'Parsing PIVOT (simple)' : r"""
       >>> parse("select dept  pivot    dept, salary , lunchTime", 
       *****
       * Result
       *****
-      GVizUnsupportedQueryOp  :  Unable to evaluate GROUP BY clause. Either the whole clause or an specific feature is not supported yet.
+      GVizUnsupportedQueryOp  :  Unable to evaluate PIVOT clause. Either the whole clause or an specific feature is not supported yet.
 
 
       >>> parse("select lunchTime, sum(salary) group by lunchTime " \
       *****
       * Result
       *****
-      GVizUnsupportedQueryOp  :  Unable to evaluate GROUP BY clause. Either the whole clause or an specific feature is not supported yet.
+      GVizUnsupportedQueryOp  :  Unable to evaluate PIVOT clause. Either the whole clause or an specific feature is not supported yet.
 
       """,
   'Parsing ORDER BY (simple)' : r"""
          isSenior = False
          seniorityStartTime = None
 
-      >>> parse("  select dept , lunchTime   group by dept "
-      ...       "   order by dept", 'sort', 'aggregate', 'cols')
+      >>> parse("  select dept , lunchTime   group by dept, lunchTime "
+      ...       "   order by dept desc", 'sort', 'aggregate', 'cols')
       ... 
       *****
       * Tokens
       Token.Name.Variable lunchTime
       Token.Keyword.Reserved group by
       Token.Name.Variable dept
+      Token.Punctuation ,
+      Token.Name.Variable lunchTime
       Token.Keyword.Reserved order by
       Token.Name.Variable dept
+      Token.Keyword desc
       *****
       * Parsing
       *****
-      [('dept', True)]
-      ['dept']
+      [('dept', False)]
       ['dept', 'lunchTime']
+      ['dept', 'lunchTime']
       *****
       * Result
       *****
-      GVizUnsupportedQueryOp  :  Unable to evaluate GROUP BY clause. Either the whole clause or an specific feature is not supported yet.
+      = Columns =
+      dept string
+      lunchTime timeofday
+      = Row =
+         dept = Sales
+         lunchTime = 12:00:00
+      = Row =
+         dept = Marketing
+         lunchTime = 13:00:00
+      = Row =
+         dept = Eng
+         lunchTime = 12:00:00
+      = Row =
+         dept = Eng
+         lunchTime = 13:00:00
 
       """,
   'Parsing ORDER BY (complex)' : r"""
       *****
       * Result
       *****
-      GVizUnsupportedQueryOp  :  Unable to evaluate GROUP BY clause. Either the whole clause or an specific feature is not supported yet.
-
+      = Columns =
+      dept string
+      max(salary) number
+      = Row =
+         dept = Sales
+         max(salary) = 400
+      = Row =
+         dept = Marketing
+         max(salary) = 800
+      = Row =
+         dept = Eng
+         max(salary) = 1000
 
       >>> parse("  select dept , min(lunchTime)   group by dept "
       ...       "   order by max(salary)", 'sort', 'aggregate', 'cols')
       *****
       * Result
       *****
-      GVizUnsupportedQueryOp  :  Unable to evaluate GROUP BY clause. Either the whole clause or an specific feature is not supported yet.
+      = Columns =
+      dept string
+      min(lunchTime) timeofday
+      = Row =
+         dept = Sales
+         min(lunchTime) = 12:00:00
+      = Row =
+         dept = Marketing
+         min(lunchTime) = 13:00:00
+      = Row =
+         dept = Eng
+         min(lunchTime) = 12:00:00
 
       """,
   'Parsing LIMIT' : r"""
       = Row =
          name = Dave
 
-      >>> parse("  select dept , lunchTime   group by dept "
-      ...       "   order by dept limit 100", 'cnt', 'cols', 'aggregate', 
-      ...                                     'sort')
+      >>> parse("  select dept , lunchTime   group by dept, lunchTime "
+      ...       "   order by dept , lunchTime desc limit 100", 
+      ...       'cnt', 'cols', 'aggregate', 'sort')
       ... 
       *****
       * Tokens
       Token.Name.Variable lunchTime
       Token.Keyword.Reserved group by
       Token.Name.Variable dept
+      Token.Punctuation ,
+      Token.Name.Variable lunchTime
       Token.Keyword.Reserved order by
       Token.Name.Variable dept
+      Token.Punctuation ,
+      Token.Name.Variable lunchTime
+      Token.Keyword desc
       Token.Keyword.Reserved limit
       Token.Literal.Number.Integer 100
       *****
       *****
       100
       ['dept', 'lunchTime']
-      ['dept']
-      [('dept', True)]
+      ['dept', 'lunchTime']
+      [('dept', True), ('lunchTime', False)]
       *****
       * Result
       *****
-      GVizUnsupportedQueryOp  :  Unable to evaluate GROUP BY clause. Either the whole clause or an specific feature is not supported yet.
+      = Columns =
+      dept string
+      lunchTime timeofday
+      = Row =
+         dept = Eng
+         lunchTime = 13:00:00
+      = Row =
+         dept = Eng
+         lunchTime = 12:00:00
+      = Row =
+         dept = Marketing
+         lunchTime = 13:00:00
+      = Row =
+         dept = Sales
+         lunchTime = 12:00:00
 
       """,
   'Parsing OFFSET' : r"""
          isSenior = False
          seniorityStartTime = None
 
-      >>> parse("  select dept , lunchTime   group by dept "
-      ...       " order by dept limit 25  offset 100 ", 'skip', 'cols', \
-      ...           'aggregate', 'cnt', 'sort')
+      >>> parse("  select dept , lunchTime   group by dept, lunchTime "
+      ...       " order by dept , lunchTime desc limit 2  offset 1 ", 
+      ...       'skip', 'cols', 'aggregate', 'cnt', 'sort')
       ... 
       *****
       * Tokens
       Token.Name.Variable lunchTime
       Token.Keyword.Reserved group by
       Token.Name.Variable dept
+      Token.Punctuation ,
+      Token.Name.Variable lunchTime
       Token.Keyword.Reserved order by
       Token.Name.Variable dept
+      Token.Punctuation ,
+      Token.Name.Variable lunchTime
+      Token.Keyword desc
       Token.Keyword.Reserved limit
-      Token.Literal.Number.Integer 25
+      Token.Literal.Number.Integer 2
       Token.Keyword.Reserved offset
-      Token.Literal.Number.Integer 100
+      Token.Literal.Number.Integer 1
       *****
       * Parsing
       *****
-      100
+      1
       ['dept', 'lunchTime']
-      ['dept']
-      25
-      [('dept', True)]
+      ['dept', 'lunchTime']
+      2
+      [('dept', True), ('lunchTime', False)]
       *****
       * Result
       *****
-      GVizUnsupportedQueryOp  :  Unable to evaluate GROUP BY clause. Either the whole clause or an specific feature is not supported yet.
+      = Columns =
+      dept string
+      lunchTime timeofday
+      = Row =
+         dept = Eng
+         lunchTime = 12:00:00
+      = Row =
+         dept = Marketing
+         lunchTime = 13:00:00
 
       """,
   'Parsing LABEL (simple)' : r"""
       = Columns =
       max(salary) number Better paid
       <BLANKLINE>
-      NotImplementedError  :  Feature not supported ... yet
+      GVizRuntimeError  :  max invoked without GROUP BY, PIVOT
 
       >>> parse(r'''select lower(name) + '@example.com', upper(dept) 
       ...       label `lower(name) + '@example.com'` 'E-mail', 
       *****
       * Result
       *****
-      LookupError  :  Unknown column 'done'
+      GVizUnknownColumn  :  Unknown column 'done'
 
       >>> parse(r'''select name, done, total - done '''
       ...       '''format `total - done` '#,##0.0' ''', 'fmt')
       *****
       * Result
       *****
-      GVizRuntimeError  :  Unknown column done.
+      GVizUnknownColumn  :  Unknown column done.
 
       """,
   'Parsing FORMAT (simple)' : r"""
       = Columns =
       max(salary) number Better paid
       <BLANKLINE>
-      NotImplementedError  :  Feature not supported ... yet
+      GVizRuntimeError  :  max invoked without GROUP BY, PIVOT
 
       >>> parse(r'''select lower(name) + '@example.com', upper(dept), 
       ...       isSenior, seniorityStartTime, salary * 0.05
       *****
       * Result
       *****
-      GVizRuntimeError  :  Unknown column email address.
+      GVizUnknownColumn  :  Unknown column email address.
 
       """,
     'Parsing (failures)' : r"""
       = Columns =
       empSalary - empTax number
       <BLANKLINE>
-      GVizRuntimeError  :  Unknown column empSalary.
+      GVizUnknownColumn  :  Unknown column empSalary.
 
 
       >>> parse("select 2 * (max(empSalary) / max(empTax))", 'cols')
       = Columns =
       2 * (max(empSalary) / max(empTax)) number
       <BLANKLINE>
-      NotImplementedError  :  Feature not supported ... yet
+      GVizRuntimeError  :  max invoked without GROUP BY, PIVOT
 
       >>> parse("select 12 * (year(seniorityStartTime) - year(hireDate)) + "
       ...       "month(seniorityStartTime) - month(hireDate)", 'cols')

File trac-dev/gviz/tracgviz/util/__init__.py

View file
  • Ignore whitespace
           'get_column_desc', 'TYPES_2_GVIZ', 'get_column_desc', \
           'rpc_opt_sigs', 'REQFIELDS_DESC', 'REQFIELDS_DEFAULTS', \
           'is_dyn_schema', 'compile_pattern', 'ObjectIntercept', \
-          'send_response', 'iter_table_data', 'StringIO', 'UTF8Recoder'
+          'send_response', 'iter_table_data', 'StringIO', 'UTF8Recoder', \
+          'groupby'
 
 from trac.core import Component, ExtensionPoint, implements, TracError
 from trac.config import Option
       'datefmt'  : "%Y-%m-%d %H:%M:%S"
     }
 
+class groupby(dict):
+    r"""Guido inspired SQL-like GROUPBY class that also encapsulates the logic 
+    in a Unix-like "sort | uniq".
+
+    http://code.activestate.com/recipes/259173-groupby/
+
+    PS: This version is slightly modified.
+    """
+    def __init__(self, seq, key=None, value=None):
+        samekey = key is None
+        samevalue = value is None
+        for item in seq:
+            k = val if samekey else key(item)
+            self.setdefault(k, []).append(item if samevalue else value(item))
+
+    __iter__ = dict.iteritems
+
 def is_dyn_schema(provider):
   r"""Determine whether the schema defined by `provider` is static 
   or dynamic (i.e may change at run-time).