Commits

Olemis Lang committed 31cc1b6

GViz QL : Sort dataset according to order by clause

Comments (0)

Files changed (3)

trac-dev/gviz/TODO

 
 X GViz API QL: Implement `format` clause using PyICU
 
+X GViz API QL: Implement `order by` clause.
+
 - Add `limit` parameter for history methods in VersionControlRPC.
 
 - Implement data source provider for Ticket->Query
 
 - GViz API QL: Add tasks to support `group by`, 
-  `pivot`, `order by`, and `options` clauses.
+  `pivot`, and `options` clauses.
 
 - Allow data source providers to control the details needed to handle 
   queries.

trac-dev/gviz/tracgviz/gvizql.py

     get_col_value = self._column_accessor(schema)
     new_data = ( row for row in data \
         if bool(self._eval_expr(row, self.expr, get_col_value)) )
-    return (schema, new_data)
+    return schema, new_data
 
 class GVizGroupByClause(GVizQLClauseHandler):
   _PROPS = {'idx_syntax' : 3, 'idx_eval': 1, 'keyw' : 'group by'}
   def transform(self, schema, data):
     r"""Sort rows in base result set by evaluating target expressions.
     """
-    self.unsupported()
+    get_col_value = self._column_accessor(schema)
+    new_data = list(data)
+    _key = lambda row : tuple(self._eval_expr(row, expr, get_col_value) \
+        for expr, _ in self.order_def)
+
+    first = self.order_def[0][1]
+    if all(x[1] == first for x in self.order_def):
+      # Same kind of order for all columns
+      _cmp = None
+      _reverse = False if first else True
+    else:
+      # Different kinds of orders for columns thus custom comparator needed
+      _cmp = lambda k1, k2 : cmp([cmp(x1, x2) if x[1] else -cmp(x1,x2) \
+          for x1,x2,x in izip(k1, k2, self.order_def)], [0] * len(k1))
+      _reverse = False
+    new_data.sort(cmp=_cmp, key=_key, reverse=_reverse)
+    return schema, new_data
 
 class GVizLimitClause(GVizQLClauseHandler):
   _PROPS = {'idx_syntax' : 6, 'idx_eval': 6, 'keyw' : 'limit'}

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

 
       """,
   'Parsing ORDER BY (simple)' : r"""
+      >>> parse("order by   dept , salary    ", 'sort')
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved order by
+      Token.Name.Variable dept
+      Token.Punctuation ,
+      Token.Name.Variable salary
+      *****
+      * Parsing
+      *****
+      [('dept', True), ('salary', True)]
+      *****
+      * Result
+      *****
+      = Columns =
+      name string
+      dept string
+      lunchTime timeofday
+      salary number
+      hireDate date
+      age number
+      isSenior boolean
+      seniorityStartTime datetime
+      = Row =
+         name = Dave
+         dept = Eng
+         lunchTime = 12:00:00
+         salary = 500
+         hireDate = 2006-04-19
+         age = 27
+         isSenior = False
+         seniorityStartTime = None
+      = Row =
+         name = Sally
+         dept = Eng
+         lunchTime = 13:00:00
+         salary = 600
+         hireDate = 2005-10-10
+         age = 30
+         isSenior = False
+         seniorityStartTime = None
+      = Row =
+         name = John
+         dept = Eng
+         lunchTime = 12:00:00
+         salary = 1000
+         hireDate = 2005-03-19
+         age = 35
+         isSenior = True
+         seniorityStartTime = 2007-12-02 15:56:00
+      = Row =
+         name = Mike
+         dept = Marketing
+         lunchTime = 13:00:00
+         salary = 800
+         hireDate = 2005-01-10
+         age = 24
+         isSenior = True
+         seniorityStartTime = 2007-12-30 14:40:00
+      = Row =
+         name = Dana
+         dept = Sales
+         lunchTime = 12:00:00
+         salary = 350
+         hireDate = 2004-09-08
+         age = 25
+         isSenior = False
+         seniorityStartTime = None
+      = Row =
+         name = Ben
+         dept = Sales
+         lunchTime = 12:00:00
+         salary = 400
+         hireDate = 2002-10-10
+         age = 32
+         isSenior = True
+         seniorityStartTime = 2005-03-09 12:30:00
+
+      >>> parse("order by   dept desc , salary desc   ", 'sort')
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved order by
+      Token.Name.Variable dept
+      Token.Keyword desc
+      Token.Punctuation ,
+      Token.Name.Variable salary
+      Token.Keyword desc
+      *****
+      * Parsing
+      *****
+      [('dept', False), ('salary', False)]
+      *****
+      * Result
+      *****
+      = Columns =
+      name string
+      dept string
+      lunchTime timeofday
+      salary number
+      hireDate date
+      age number
+      isSenior boolean
+      seniorityStartTime datetime
+      = Row =
+         name = Ben
+         dept = Sales
+         lunchTime = 12:00:00
+         salary = 400
+         hireDate = 2002-10-10
+         age = 32
+         isSenior = True
+         seniorityStartTime = 2005-03-09 12:30:00
+      = Row =
+         name = Dana
+         dept = Sales
+         lunchTime = 12:00:00
+         salary = 350
+         hireDate = 2004-09-08
+         age = 25
+         isSenior = False
+         seniorityStartTime = None
+      = Row =
+         name = Mike
+         dept = Marketing
+         lunchTime = 13:00:00
+         salary = 800
+         hireDate = 2005-01-10
+         age = 24
+         isSenior = True
+         seniorityStartTime = 2007-12-30 14:40:00
+      = Row =
+         name = John
+         dept = Eng
+         lunchTime = 12:00:00
+         salary = 1000
+         hireDate = 2005-03-19
+         age = 35
+         isSenior = True
+         seniorityStartTime = 2007-12-02 15:56:00
+      = Row =
+         name = Sally
+         dept = Eng
+         lunchTime = 13:00:00
+         salary = 600
+         hireDate = 2005-10-10
+         age = 30
+         isSenior = False
+         seniorityStartTime = None
+      = Row =
+         name = Dave
+         dept = Eng
+         lunchTime = 12:00:00
+         salary = 500
+         hireDate = 2006-04-19
+         age = 27
+         isSenior = False
+         seniorityStartTime = None
+
       >>> parse("order by   dept , salary desc   ", 'sort')
       *****
       * Tokens
       *****
       * Result
       *****
-      GVizUnsupportedQueryOp  :  Unable to evaluate ORDER BY clause. Either the whole clause or an specific feature is not supported yet.
-
+      = Columns =
+      name string
+      dept string
+      lunchTime timeofday
+      salary number
+      hireDate date
+      age number
+      isSenior boolean
+      seniorityStartTime datetime
+      = Row =
+         name = John
+         dept = Eng
+         lunchTime = 12:00:00
+         salary = 1000
+         hireDate = 2005-03-19
+         age = 35
+         isSenior = True
+         seniorityStartTime = 2007-12-02 15:56:00
+      = Row =
+         name = Sally
+         dept = Eng
+         lunchTime = 13:00:00
+         salary = 600
+         hireDate = 2005-10-10
+         age = 30
+         isSenior = False
+         seniorityStartTime = None
+      = Row =
+         name = Dave
+         dept = Eng
+         lunchTime = 12:00:00
+         salary = 500
+         hireDate = 2006-04-19
+         age = 27
+         isSenior = False
+         seniorityStartTime = None
+      = Row =
+         name = Mike
+         dept = Marketing
+         lunchTime = 13:00:00
+         salary = 800
+         hireDate = 2005-01-10
+         age = 24
+         isSenior = True
+         seniorityStartTime = 2007-12-30 14:40:00
+      = Row =
+         name = Ben
+         dept = Sales
+         lunchTime = 12:00:00
+         salary = 400
+         hireDate = 2002-10-10
+         age = 32
+         isSenior = True
+         seniorityStartTime = 2005-03-09 12:30:00
+      = Row =
+         name = Dana
+         dept = Sales
+         lunchTime = 12:00:00
+         salary = 350
+         hireDate = 2004-09-08
+         age = 25
+         isSenior = False
+         seniorityStartTime = None
 
       >>> parse("  select dept , lunchTime   group by dept "
       ...       "   order by dept", 'sort', 'aggregate', 'cols')