Commits

Olemis Lang committed 79ee38c Merge

TracGViz : Merge a87c1b50fbc9279c2ca435d0 incorporating FORMAT clause (GViz QL)

Comments (0)

Files changed (3)

trac-dev/gviz/TODO

 
 X GViz API QL: Support arithmetic expressions in label and format clauses
 
+X GViz API QL: Implement `format` clause using PyICU
+
 - 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`, `order by`, and `options` clauses.
 - Add an option in GViz milestones in order to only show data about 
   active milestones.
 
 - Optimize the calls to TracRPC's Method objects by preventing 
   them from building lists out of generators.
 
-- GViz API QL: Add tasks to support `from`, `where`, `group by`, 
-  `pivot`, `order by`, `format` and `options` clauses.
+
 
 - Allow data source providers to control the details needed to handle 
   queries.
   those columns that appear in data tables only when some run-time 
   conditions are met e.g. ticket custom fields in 
   `ticket.GVizTicketReport`).
+  
 - Execute reports defined using URLs pointing to saved custom queries.
 
 - Introduce support for GViz warnings (specially `data_truncated` 
   warnings).
 
 - Implement architecture in order to export data.
+
 - Export data to Google Spreadsheets.
 
 - Provide default border images for gadgets.

trac-dev/gviz/tracgviz/gvizql.py

 __metaclass__ = type
 
 from ast import literal_eval
-from datetime import datetime
+from datetime import date, datetime, time
 from itertools import islice, izip
 import logging
 import operator
   _PROPS = {'idx_syntax' : 9, 'idx_eval': 8, 'keyw' : 'format'}
   KEYWORDS = ('format',)
 
+  @property
+  def PyICU(self):
+    r"""Load PyICU module. Dynamically detect if installed at run time.
+    """
+    try:
+      return __import__('PyICU')
+    except ImportError:
+      return None
+
   def __init__(self, seq):
     r"""Initialize this clause with a sequence of formatting expressions
     """
       seq = Sequence([seq])
     self.fmt = seq
 
+  DATETIME_CONVERTER = {
+      'date' : lambda x : datetime.combine(x, time()),
+      'timeofday' : lambda x : datetime.combine(date.min, x),
+      'datetime' : lambda x : x,
+    }
+
+  @classmethod
+  def format_value(cls, value, coltype, formatter):
+    r"""Format value of given data type according to some formatting pattern.
+    """
+    if coltype == 'boolean':
+      # Formatter is a binary tuple
+      idx = 0 if bool(value) else 1
+      return formatter[idx]
+    elif coltype in cls.DATETIME_CONVERTER:
+      # Formatter is an instance of PyICU.SimpleDateFormat
+      return formatter.format(cls.DATETIME_CONVERTER[coltype](value))
+    elif coltype == 'number':
+      # Formatter is an instance of PyICU.DecimalFormat
+      return formatter.format(value)
+    else:
+      raise GVizRuntimeError("Format not supported for %s type" % (coltype,))
+
+  def _formatter(self, col, pattern):
+    if col is None:
+      raise LookupError("Unknown column '%s'" % (colnm,))
+    colnm, coltype = col[:2]
+    if coltype == 'boolean':
+      pattern = pattern.rsplit(':', 1)
+      if len(pattern) != 2:
+        raise GVizRuntimeError("Invalid formatting pattern '%s'" % (pattern,))
+      else:
+        return pattern
+    elif coltype in ('date', 'timeofday', 'datetime'):
+      if self.PyICU is None:
+        raise GVizRuntimeError("PyICU needed to format '%s' '%s'" % \
+            (colnm, coltype))
+      else:
+        return self.PyICU.SimpleDateFormat(pattern)
+    elif coltype == 'number':
+      if self.PyICU is None:
+        raise GVizRuntimeError("PyICU needed to format '%s' '%s'" % \
+            (colnm, coltype))
+      else:
+        return self.PyICU.DecimalFormat(pattern)
+    else:
+      raise GVizRuntimeError("Format not supported for %s type" % (coltype,))
+
   def transform(self, schema, data):
-    r"""Annotate result set with formatted values.
+    r"""Annotate result set with formatted values. In practice this means
+    to transform data set into (value, formatted_value) tuples.
     """
-    self.unsupported()
+    cols = dict([col[0], col] for col in schema)
+    patterns = dict([colnm, self._formatter(cols.get(colnm), p)] \
+        for colnm, p in self.fmt)
+
+    #raise ValueError('\n'.join([str(patterns), str(schema), str(list(data))]))
+    new_data = ([val if val is None or col[0] not in patterns else \
+                        (val, self.format_value(val, col[1], patterns[col[0]]))\
+                    for val, col in izip(row, schema)] for row in data)
+    return schema, new_data
 
 class GVizOptionsClause(GVizQLClauseHandler):
   _PROPS = {'idx_syntax' : 10, 'idx_eval': 9, 'keyw' : 'options'}

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

          upper(dept) = MARKETING
 
       """,
-  'Parsing FORMAT' : r"""
+  'Parsing FORMAT (no ICU)' : r"""
+
+      Hack to ensure PyICU won't be imported
+      >>> import sys
+      >>> sys.modules['PyICU'] = None
+
+      Boolean format patterns should still work
+      >>> parse("format isSenior 'Yes!:Not yet'", 'fmt')
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved format
+      Token.Name.Variable isSenior
+      Token.Literal.String.Single 'Yes!:Not yet'
+      *****
+      * Parsing
+      *****
+      [('isSenior', 'Yes!:Not yet')]
+      *****
+      * Result
+      *****
+      = 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, 'Yes!')
+         seniorityStartTime = 2007-12-02 15:56:00
+      = Row =
+         name = Dave
+         dept = Eng
+         lunchTime = 12:00:00
+         salary = 500
+         hireDate = 2006-04-19
+         age = 27
+         isSenior = (False, 'Not yet')
+         seniorityStartTime = None
+      = Row =
+         name = Sally
+         dept = Eng
+         lunchTime = 13:00:00
+         salary = 600
+         hireDate = 2005-10-10
+         age = 30
+         isSenior = (False, 'Not yet')
+         seniorityStartTime = None
+      = Row =
+         name = Ben
+         dept = Sales
+         lunchTime = 12:00:00
+         salary = 400
+         hireDate = 2002-10-10
+         age = 32
+         isSenior = (True, 'Yes!')
+         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, 'Not yet')
+         seniorityStartTime = None
+      = Row =
+         name = Mike
+         dept = Marketing
+         lunchTime = 13:00:00
+         salary = 800
+         hireDate = 2005-01-10
+         age = 24
+         isSenior = (True, 'Yes!')
+         seniorityStartTime = 2007-12-30 14:40:00
+
+      Number format patterns require PyICU
       >>> parse("  format  salary   '#,##0.00' ", 'fmt')
       *****
       * Tokens
       *****
       * Result
       *****
-      GVizUnsupportedQueryOp  :  Unable to evaluate FORMAT clause. Either the whole clause or an specific feature is not supported yet.
+      GVizRuntimeError  :  PyICU needed to format 'salary' 'number'
 
+      Date format patterns require PyICU
+      >>> parse(r'''format hireDate "EEE, MMM d, ''yy" ''', 'fmt')
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved format
+      Token.Name.Variable hireDate
+      Token.Literal.String.Double "EEE, MMM d, ''yy"
+      *****
+      * Parsing
+      *****
+      [('hireDate', "EEE, MMM d, ''yy")]
+      *****
+      * Result
+      *****
+      GVizRuntimeError  :  PyICU needed to format 'hireDate' 'date'
+
+      Datetime format patterns require PyICU
+      >>> parse(r'''format seniorityStartTime "yyyy.MM.dd G 'at' HH:mm:ss" ''',
+      ...       'fmt')
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved format
+      Token.Name.Variable seniorityStartTime
+      Token.Literal.String.Double "yyyy.MM.dd G 'at' HH:mm:ss"
+      *****
+      * Parsing
+      *****
+      [('seniorityStartTime', "yyyy.MM.dd G 'at' HH:mm:ss")]
+      *****
+      * Result
+      *****
+      GVizRuntimeError  :  PyICU needed to format 'seniorityStartTime' 'datetime'
+
+      Time format patterns require PyICU
+      >>> parse("  format lunchTime 'K:mm a' ", 'fmt')
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved format
+      Token.Name.Variable lunchTime
+      Token.Literal.String.Single 'K:mm a'
+      *****
+      * Parsing
+      *****
+      [('lunchTime', 'K:mm a')]
+      *****
+      * Result
+      *****
+      GVizRuntimeError  :  PyICU needed to format 'lunchTime' 'timeofday'
+
+      Import hack not needed any more . Back to normal
+      >>> del sys.modules['PyICU']
+      """,
+  'Parsing FORMAT (simple)' : r"""
+      >>> parse("  format  salary   '#,##0.00' ", 'fmt')
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved format
+      Token.Name.Variable salary
+      Token.Literal.String.Single '#,##0.00'
+      *****
+      * Parsing
+      *****
+      [('salary', '#,##0.00')]
+      *****
+      * Result
+      *****
+      = 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, u'1,000.00')
+         hireDate = 2005-03-19
+         age = 35
+         isSenior = True
+         seniorityStartTime = 2007-12-02 15:56:00
+      = Row =
+         name = Dave
+         dept = Eng
+         lunchTime = 12:00:00
+         salary = (500, u'500.00')
+         hireDate = 2006-04-19
+         age = 27
+         isSenior = False
+         seniorityStartTime = None
+      = Row =
+         name = Sally
+         dept = Eng
+         lunchTime = 13:00:00
+         salary = (600, u'600.00')
+         hireDate = 2005-10-10
+         age = 30
+         isSenior = False
+         seniorityStartTime = None
+      = Row =
+         name = Ben
+         dept = Sales
+         lunchTime = 12:00:00
+         salary = (400, u'400.00')
+         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, u'350.00')
+         hireDate = 2004-09-08
+         age = 25
+         isSenior = False
+         seniorityStartTime = None
+      = Row =
+         name = Mike
+         dept = Marketing
+         lunchTime = 13:00:00
+         salary = (800, u'800.00')
+         hireDate = 2005-01-10
+         age = 24
+         isSenior = True
+         seniorityStartTime = 2007-12-30 14:40:00
 
       >>> parse("  format  salary   '#,##0.00' ," \
       ...       "hireDate 'dd-MMM-yyyy',      " \
       *****
       * Result
       *****
-      GVizUnsupportedQueryOp  :  Unable to evaluate FORMAT 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, u'1,000.00')
+         hireDate = (datetime.date(2005, 3, 19), u'19-Mar-2005')
+         age = 35
+         isSenior = (True, 'not yet')
+         seniorityStartTime = 2007-12-02 15:56:00
+      = Row =
+         name = Dave
+         dept = Eng
+         lunchTime = 12:00:00
+         salary = (500, u'500.00')
+         hireDate = (datetime.date(2006, 4, 19), u'19-Apr-2006')
+         age = 27
+         isSenior = (False, 'of course!')
+         seniorityStartTime = None
+      = Row =
+         name = Sally
+         dept = Eng
+         lunchTime = 13:00:00
+         salary = (600, u'600.00')
+         hireDate = (datetime.date(2005, 10, 10), u'10-Oct-2005')
+         age = 30
+         isSenior = (False, 'of course!')
+         seniorityStartTime = None
+      = Row =
+         name = Ben
+         dept = Sales
+         lunchTime = 12:00:00
+         salary = (400, u'400.00')
+         hireDate = (datetime.date(2002, 10, 10), u'10-Oct-2002')
+         age = 32
+         isSenior = (True, 'not yet')
+         seniorityStartTime = 2005-03-09 12:30:00
+      = Row =
+         name = Dana
+         dept = Sales
+         lunchTime = 12:00:00
+         salary = (350, u'350.00')
+         hireDate = (datetime.date(2004, 9, 8), u'08-Sep-2004')
+         age = 25
+         isSenior = (False, 'of course!')
+         seniorityStartTime = None
+      = Row =
+         name = Mike
+         dept = Marketing
+         lunchTime = 13:00:00
+         salary = (800, u'800.00')
+         hireDate = (datetime.date(2005, 1, 10), u'10-Jan-2005')
+         age = 24
+         isSenior = (True, 'not yet')
+         seniorityStartTime = 2007-12-30 14:40:00
 
       >>> parse("format salary '#,##0.00', hireDate 'dd-MMM-yyyy', "
       ...       "isSenior 'Yes!:Not yet'", 'fmt')
       *****
       * Result
       *****
-      GVizUnsupportedQueryOp  :  Unable to evaluate FORMAT 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, u'1,000.00')
+         hireDate = (datetime.date(2005, 3, 19), u'19-Mar-2005')
+         age = 35
+         isSenior = (True, 'Yes!')
+         seniorityStartTime = 2007-12-02 15:56:00
+      = Row =
+         name = Dave
+         dept = Eng
+         lunchTime = 12:00:00
+         salary = (500, u'500.00')
+         hireDate = (datetime.date(2006, 4, 19), u'19-Apr-2006')
+         age = 27
+         isSenior = (False, 'Not yet')
+         seniorityStartTime = None
+      = Row =
+         name = Sally
+         dept = Eng
+         lunchTime = 13:00:00
+         salary = (600, u'600.00')
+         hireDate = (datetime.date(2005, 10, 10), u'10-Oct-2005')
+         age = 30
+         isSenior = (False, 'Not yet')
+         seniorityStartTime = None
+      = Row =
+         name = Ben
+         dept = Sales
+         lunchTime = 12:00:00
+         salary = (400, u'400.00')
+         hireDate = (datetime.date(2002, 10, 10), u'10-Oct-2002')
+         age = 32
+         isSenior = (True, 'Yes!')
+         seniorityStartTime = 2005-03-09 12:30:00
+      = Row =
+         name = Dana
+         dept = Sales
+         lunchTime = 12:00:00
+         salary = (350, u'350.00')
+         hireDate = (datetime.date(2004, 9, 8), u'08-Sep-2004')
+         age = 25
+         isSenior = (False, 'Not yet')
+         seniorityStartTime = None
+      = Row =
+         name = Mike
+         dept = Marketing
+         lunchTime = 13:00:00
+         salary = (800, u'800.00')
+         hireDate = (datetime.date(2005, 1, 10), u'10-Jan-2005')
+         age = 24
+         isSenior = (True, 'Yes!')
+         seniorityStartTime = 2007-12-30 14:40:00
 
       >>> parse("select salary, hireDate, isSenior   , lunchTime "
       ...       "format salary '#,##0.00', hireDate 'dd-MMM-yyyy', "
       *****
       * Result
       *****
-      GVizUnsupportedQueryOp  :  Unable to evaluate FORMAT clause. Either the whole clause or an specific feature is not supported yet.
+      = Columns =
+      salary number
+      hireDate date
+      isSenior boolean
+      lunchTime timeofday
+      = Row =
+         salary = (1000, u'1,000.00')
+         hireDate = (datetime.date(2005, 3, 19), u'19-Mar-2005')
+         isSenior = (True, 'Yes!')
+         lunchTime = 12:00:00
+      = Row =
+         salary = (500, u'500.00')
+         hireDate = (datetime.date(2006, 4, 19), u'19-Apr-2006')
+         isSenior = (False, 'Not yet')
+         lunchTime = 12:00:00
+      = Row =
+         salary = (600, u'600.00')
+         hireDate = (datetime.date(2005, 10, 10), u'10-Oct-2005')
+         isSenior = (False, 'Not yet')
+         lunchTime = 13:00:00
+      = Row =
+         salary = (400, u'400.00')
+         hireDate = (datetime.date(2002, 10, 10), u'10-Oct-2002')
+         isSenior = (True, 'Yes!')
+         lunchTime = 12:00:00
+      = Row =
+         salary = (350, u'350.00')
+         hireDate = (datetime.date(2004, 9, 8), u'08-Sep-2004')
+         isSenior = (False, 'Not yet')
+         lunchTime = 12:00:00
+      = Row =
+         salary = (800, u'800.00')
+         hireDate = (datetime.date(2005, 1, 10), u'10-Jan-2005')
+         isSenior = (True, 'Yes!')
+         lunchTime = 13:00:00
+
+      """,
+  'Parsing FORMAT (complex)' : r"""
+      >>> parse(r'''select max(salary) label `max(salary)` 'Better paid' 
+      ...       format `max(salary)` "#,##0.00" ''',
+      ...       'fmt', 'cols', 'labels')
+      ...
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved select
+      Token.Name.Builtin max
+      Token.Punctuation (
+      Token.Name.Variable salary
+      Token.Punctuation )
+      Token.Keyword.Reserved label
+      Token.Name.Variable `max(salary)`
+      Token.Literal.String.Single 'Better paid'
+      Token.Keyword.Reserved format
+      Token.Name.Variable `max(salary)`
+      Token.Literal.String.Double "#,##0.00"
+      *****
+      * Parsing
+      *****
+      [('max(salary)', '#,##0.00')]
+      ['max(salary)']
+      [('max(salary)', 'Better paid')]
+      *****
+      * Result
+      *****
+      = Columns =
+      max(salary) number Better paid
+      <BLANKLINE>
+      NotImplementedError  :  Feature not supported ... yet
+
+      >>> parse(r'''select lower(name) + '@example.com', upper(dept), 
+      ...       isSenior, seniorityStartTime, salary * 0.05
+      ...       label `lower(name) + '@example.com'` 'E-mail', 
+      ...       `upper(dept)` "DEPT" 
+      ...       format isSenior "SKILLED:beginner", `salary * 0.05` '#,##0.00',
+      ...       seniorityStartTime "EEE, MMM d, ''yy" ''',
+      ...       'fmt', 'cols', 'labels')
+      ...
+      *****
+      * Tokens
+      *****
+      Token.Keyword.Reserved select
+      Token.Name.Function lower
+      Token.Punctuation (
+      Token.Name.Variable name
+      Token.Punctuation )
+      Token.Operator.Arithmetic +
+      Token.Literal.String.Single '@example.com'
+      Token.Punctuation ,
+      Token.Name.Function upper
+      Token.Punctuation (
+      Token.Name.Variable dept
+      Token.Punctuation )
+      Token.Punctuation ,
+      Token.Name.Variable isSenior
+      Token.Punctuation ,
+      Token.Name.Variable seniorityStartTime
+      Token.Punctuation ,
+      Token.Name.Variable salary
+      Token.Operator.Arithmetic *
+      Token.Literal.Number 0.05
+      Token.Keyword.Reserved label
+      Token.Name.Variable `lower(name) + '@example.com'`
+      Token.Literal.String.Single 'E-mail'
+      Token.Punctuation ,
+      Token.Name.Variable `upper(dept)`
+      Token.Literal.String.Double "DEPT"
+      Token.Keyword.Reserved format
+      Token.Name.Variable isSenior
+      Token.Literal.String.Double "SKILLED:beginner"
+      Token.Punctuation ,
+      Token.Name.Variable `salary * 0.05`
+      Token.Literal.String.Single '#,##0.00'
+      Token.Punctuation ,
+      Token.Name.Variable seniorityStartTime
+      Token.Literal.String.Double "EEE, MMM d, ''yy"
+      *****
+      * Parsing
+      *****
+      [('isSenior', 'SKILLED:beginner'), ('salary * 0.05', '#,##0.00'), ('seniorityStartTime', "EEE, MMM d, ''yy")]
+      ["lower(name) + '@example.com'", 'upper(dept)', 'isSenior', 'seniorityStartTime', 'salary * 0.05']
+      [("lower(name) + '@example.com'", 'E-mail'), ('upper(dept)', 'DEPT')]
+      *****
+      * Result
+      *****
+      = Columns =
+      lower(name) + '@example.com' number E-mail
+      upper(dept) string DEPT
+      isSenior boolean
+      seniorityStartTime datetime
+      salary * 0.05 number
+      = Row =
+         lower(name) + '@example.com' = john@example.com
+         upper(dept) = ENG
+         isSenior = (True, 'SKILLED')
+         seniorityStartTime = (datetime.datetime(2007, 12, 2, 15, 56), u"Sun, Dec 2, '07")
+         salary * 0.05 = (50.0, u'50.00')
+      = Row =
+         lower(name) + '@example.com' = dave@example.com
+         upper(dept) = ENG
+         isSenior = (False, 'beginner')
+         seniorityStartTime = None
+         salary * 0.05 = (25.0, u'25.00')
+      = Row =
+         lower(name) + '@example.com' = sally@example.com
+         upper(dept) = ENG
+         isSenior = (False, 'beginner')
+         seniorityStartTime = None
+         salary * 0.05 = (30.0, u'30.00')
+      = Row =
+         lower(name) + '@example.com' = ben@example.com
+         upper(dept) = SALES
+         isSenior = (True, 'SKILLED')
+         seniorityStartTime = (datetime.datetime(2005, 3, 9, 12, 30), u"Wed, Mar 9, '05")
+         salary * 0.05 = (20.0, u'20.00')
+      = Row =
+         lower(name) + '@example.com' = dana@example.com
+         upper(dept) = SALES
+         isSenior = (False, 'beginner')
+         seniorityStartTime = None
+         salary * 0.05 = (17.5, u'17.50')
+      = Row =
+         lower(name) + '@example.com' = mike@example.com
+         upper(dept) = MARKETING
+         isSenior = (True, 'SKILLED')
+         seniorityStartTime = (datetime.datetime(2007, 12, 30, 14, 40), u"Sun, Dec 30, '07")
+         salary * 0.05 = (40.0, u'40.00')
+
 
       """,
   'Parsing OPTIONS' : r"""