report_objects.py 35.8 KB
Newer Older
LE GAC Renaud's avatar
LE GAC Renaud committed
1
# -*- coding: utf-8 -*-
2 3 4
"""report_objects module

"""
5
import json
LE GAC Renaud's avatar
LE GAC Renaud committed
6
import matplotlib
7 8 9 10 11
import re


from gluon import current
from gluon.storage import Storage
12
from pandas import DataFrame, MultiIndex, to_datetime
LE GAC Renaud's avatar
LE GAC Renaud committed
13
from plugin_dbui import get_id, Store
14 15
from pydal.helpers.methods import smart_query
from pydal.objects import FieldVirtual
LE GAC Renaud's avatar
LE GAC Renaud committed
16
from StringIO import StringIO
17

18

19 20
MSG_NO_DATAINDEX = "The property dataIndex is required when eval is used."
MSG_NO_XTYPE = "The property xtype is missing."
21

22
REG_DBFIELD = re.compile("\w+\.\w+(?:\.\w+)?", re.UNICODE)
23
REG_PYQUERY = re.compile("[\( ]*\w+\.\w+\.\w+")
24 25 26
REG_SINGLE_DBFIELD = re.compile("^ *\w+\.\w+(\.\w+)? *$", re.UNICODE)


27 28
def do_title(config, selector):
    """Build the report title.
29

LE GAC Renaud's avatar
LE GAC Renaud committed
30 31
    Args:
        config (gluon.dal.Row): the list configuration parameter.
32
        selector (EvtSelector): selector handling period of time.
LE GAC Renaud's avatar
LE GAC Renaud committed
33 34 35 36

    Returns:
        str:

37
    """
38
    db = current.globalenv["db"]
39
    T = current.T
40

41 42
    # from the configuration
    title = (config.title if config.title else config.name)
43

44 45 46 47
    # add meta data
    metadata = []
    if selector.id_teams:
        metadata.append(db.teams[selector.id_teams].team)
48

49 50
    if selector.id_projects:
        metadata.append(db.projects[selector.id_projects].project)
51

52 53
    if selector.category:
        metadata.append(selector.category)
54

55 56 57 58 59 60 61
    if selector.id_people_categories:
        code = db.people_categories[selector.id_people_categories].code
        metadata.append(code)

    # add period
    if selector.year_start and not selector.year_end:
        metadata.append(str(selector.year_start))
62

63 64
    elif selector.year_start and selector.year_end:
        years = (str(selector.year_start), str(selector.year_end))
65
        metadata.append(T("from %s to %s", lazy=False) % years)
66

67
    return "%s: %s" % (title, " / ".join(metadata))
68 69


70
def get_value(row, tablename, fieldname, keyname="", **kwargs):
LE GAC Renaud's avatar
LE GAC Renaud committed
71 72 73 74 75
    """Helper function returning the value of a database field.

    The method is designed to handle standard and JSON-type database field.
    The field is identified by its ``tablename``, ``fieldname`` and
    ``keyname``.
76

LE GAC Renaud's avatar
LE GAC Renaud committed
77 78 79 80 81
    Args:
        row (gluon.dal.Row): one row of the tablename table.
        tablename (str): name of a database table.
        fieldname (str): name of database field.
        keyname (str): key for the JSON type field.
82

LE GAC Renaud's avatar
LE GAC Renaud committed
83 84
    Returns:
        * ``row[tablename][fieldname]`` or ``row[fieldname]``
85
          when tablename and fieldname are defined
LE GAC Renaud's avatar
LE GAC Renaud committed
86 87 88
        * ``row[tablename][fieldname][keyname]`` for JSON type field
        * ``kwargs[tablename]`` when fieldname and keyname are not defined
        * ``None`` when the field address does not exist in the row
89

90
    """
LE GAC Renaud's avatar
LE GAC Renaud committed
91
    undefined = None
92

93 94
    # force value
    if tablename and (not fieldname) and (tablename in kwargs):
95
        return kwargs[tablename]
96 97

    # field is addressed in the row by tablename and by fieldname
98
    if tablename in row:
99
        value = row[tablename][fieldname]
100

101 102 103
    # field is addressed in the row by its fieldname only
    elif fieldname in row:
        value = row[fieldname]
104

105 106 107
    else:
        return undefined

LE GAC Renaud's avatar
LE GAC Renaud committed
108 109
    # deal with the keyname
    # it has been design for JSON-type field containing a dictionary
110 111
    if not keyname:
        return value
112

113 114
    elif keyname and keyname in value:
        return value[keyname]
115

116
    return undefined
117 118 119


def split_dbfield(value):
LE GAC Renaud's avatar
LE GAC Renaud committed
120 121 122 123 124 125
    """Helper function to decode database field name as 3-elements tuple.

    The name of a database field is encoded as ``table.field`` or
    ``table.field.key``. The latter syntax is used for JSON type field.
    The function decodes as a 3-elements tuple (``tablename``,
    ``fieldname``, ``keyname``).
126

LE GAC Renaud's avatar
LE GAC Renaud committed
127 128
    Args:
        value (string):
LE GAC Renaud's avatar
LE GAC Renaud committed
129 130
            the name of the database field, either
            ``tablename.fieldname`` or ``tablename.fieldname.key``
131

LE GAC Renaud's avatar
LE GAC Renaud committed
132 133 134
    Returns:
        tuple: ``(tablename, fieldname, keyname)`` where the ``keyname``
            is either a string or an empty string.
135

136
    """
137
    li = value.split(".")
138
    if len(li) == 1:
139
        li.extend(["", ""])
140
    elif len(li) == 2:
141
        li.append("")
142 143 144
    return tuple(li)


145 146
class ReportException(BaseException):
    pass
147 148 149


class BaseReport(object):
LE GAC Renaud's avatar
LE GAC Renaud committed
150
    """Base class to build list, metric or graph reports.
151

LE GAC Renaud's avatar
LE GAC Renaud committed
152
    Args:
LE GAC Renaud's avatar
LE GAC Renaud committed
153
        config (gluon.dal.Row): the configuration parameter for the report.
154
        selector (EvtSelector): the selector handling user criteria.
155

156
    """
157
    def __init__(self, config, selector):
158

159
        db = current.globalenv["db"]
160

161 162 163 164 165
        self.db = db
        self.df = None
        self.config = config
        self.rows = None
        self.selector = selector
166

167
        # apply the condition criteria used to filter the history records
168 169
        # condition can be written as a smart query: history.id_events == 7
        # or like a python query: db.events.event == "People"
LE GAC Renaud's avatar
LE GAC Renaud committed
170
        if "conditions" in config:
171 172 173 174 175 176 177 178 179 180 181 182 183 184 185
            condition = config.conditions

            # minimal protection to avoid injection flow
            # the beginning of the python query should be like:
            #    db.table.field
            #    (db.table.field
            #    ((db.table.field
            #    ( ( db.table.field
            #
            if REG_PYQUERY.match(condition):
                q_conditions = eval(condition, None, {"db": db})

            else:
                q_conditions = smart_query(db.history, config.conditions)

186
            selector.append_query(q_conditions)
187

188 189 190 191
    def _do_data(self, maps):
        """Build a temporarily list with the raw data for each series.
        This method handle the "year" database field.

LE GAC Renaud's avatar
LE GAC Renaud committed
192 193 194 195
        Args:
            maps (list):
                the database field map (tablename, fieldname, keyname).
                One per series.
196

LE GAC Renaud's avatar
LE GAC Renaud committed
197 198
        Returns:
            list:
199

200 201
        """
        data = []
202

203 204 205
        db = self.db
        selector = self.selector

206 207 208 209 210
        query = selector.query

        # limit the list of database fields to speed up processing:
        # - keep those required by the user
        # - remove virtual field
211
        # - add standard fields require to compute virtual fields
212 213
        dbfields = [db[el[0]][el[1]] for el in maps if el[1]]
        dbfields = [el for el in dbfields if not isinstance(el, FieldVirtual)]
214 215

        dbfields.extend([db.history.end_date,
216 217 218
                         db.history.id_domains,
                         db.history.id_people,
                         db.history.id_teams,
219
                         db.history.percentage,
220
                         db.history.start_date,
221
                         db.people.birth_date])
222

223
        # the year axis is on
224
        # scan the database and compute virtual field on the year basis
225 226 227 228 229
        if self._is_year(maps):

            # get the year range
            for year in selector.get_years():
                selector.set_year(year)
230

231
                for row in db(query(db.history)).select(*dbfields):
232
                    values = [get_value(row, *elt, year=year) for elt in maps]
233
                    data.append(values)
234

235 236
        # standard scan
        else:
237
            for row in db(query(db.history)).select(*dbfields):
238
                values = [get_value(row, *elt) for elt in maps]
239
                data.append(values)
240

241
        return data
242

243 244
    def _is_year(self, maps):
        """
LE GAC Renaud's avatar
LE GAC Renaud committed
245 246 247 248
        Args:
            maps (list):
                the database field map (tablename, fieldname, keyname).
                One per series.
249

LE GAC Renaud's avatar
LE GAC Renaud committed
250 251
        Returns:
            bool: ``True`` is the pseudo field ``year`` is in maps
252

253
        """
254
        li = [True for el in maps if el[0] == "year"]
255 256
        return (True if li else False)

257
    def to_df(self):
258
        """Return the pandas DataFrame.
259

LE GAC Renaud's avatar
LE GAC Renaud committed
260 261
        Returns:
            pandas.DataFrame:
262

263 264
        """
        return self.df
265 266


LE GAC Renaud's avatar
LE GAC Renaud committed
267
class Graph(BaseReport):
LE GAC Renaud's avatar
LE GAC Renaud committed
268 269 270
    """Build a report as a graph.

    Any data encapsulated in list, 1-dim or 2-dim metrics
271 272
    can be displayed as a graph. The rendering is performed by
    the matplotlib library. Therefore, many representations of
LE GAC Renaud's avatar
LE GAC Renaud committed
273 274
    the data are possible: plot, histogram, bar charts, error charts,
    scater plots, *etc*.
LE GAC Renaud's avatar
LE GAC Renaud committed
275

LE GAC Renaud's avatar
LE GAC Renaud committed
276
    Args:
LE GAC Renaud's avatar
LE GAC Renaud committed
277 278 279
        config (gluon.dal.Row):
            the configuration parameter for the graph.

280
        selector (EvtSelector):
LE GAC Renaud's avatar
LE GAC Renaud committed
281 282
            selector handling period of time.

LE GAC Renaud's avatar
LE GAC Renaud committed
283 284 285
        backend (str):
            the name of the matplotlib backend uses to produce figure.

LE GAC Renaud's avatar
LE GAC Renaud committed
286 287 288 289 290 291 292 293 294
    """
    def __init__(self, config, selector, backend="Agg"):

        BaseReport.__init__(self, config, selector)

        # set the matplotlib back end
        #
        # NOTE: the X11 back end is not needed on the server side. In addition
        # Tkinter crash with the message "out of stack space" when the 2nd plot
295
        # is generated.
LE GAC Renaud's avatar
LE GAC Renaud committed
296 297 298
        # The documentation recommend to limit the matplotlib back end to Agg
        # which is tuned to render high quality PNG image. But, it is also
        # design to render PDF and SVG image without the X11 interface.
299
        #
LE GAC Renaud's avatar
LE GAC Renaud committed
300
        matplotlib.use(backend)
301

LE GAC Renaud's avatar
LE GAC Renaud committed
302 303 304 305 306
        # split the plot configuration in two parts:
        # 1) keywords for the DataFrame.plot method
        # 2) steering parameter for this class
        config.plot = json.loads(config.plot)
        config.steer = Storage()
307

308
        for k in ("index", "transpose", "xlabel", "ylabel"):
LE GAC Renaud's avatar
LE GAC Renaud committed
309 310
            v = config.plot.pop(k, None)
            config.steer[k] = v
311

LE GAC Renaud's avatar
LE GAC Renaud committed
312 313 314 315 316 317 318 319
        # instantiate the DataFrame for the report
        db = self.db

        report_type = config.report_type
        report_name = config.report_name
        report_id = get_id(db[report_type], name=report_name)
        report_config = db[report_type][report_id]

320
        if report_type == "lists":
LE GAC Renaud's avatar
LE GAC Renaud committed
321
            report = List(report_config, selector)
322

LE GAC Renaud's avatar
LE GAC Renaud committed
323 324
        elif report_type == "metrics1d":
            report = Metric1D(report_config, selector)
325

LE GAC Renaud's avatar
LE GAC Renaud committed
326 327 328 329
        elif report_type == "metrics2d":
            report = Metric2D(report_config, selector)

        self.df = report.to_df()
330

LE GAC Renaud's avatar
LE GAC Renaud committed
331 332 333 334 335
        # build the graph from the DataFrame
        self._do_graph()
        self._do_labels()
        self._do_legend()
        self._do_tick()
336

LE GAC Renaud's avatar
LE GAC Renaud committed
337
    def _do_graph(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
338
        """Build the graph from the ``DataFrame`` structure.
339

LE GAC Renaud's avatar
LE GAC Renaud committed
340 341 342 343
        """
        config = self.config
        df = self.df
        plot, steer = config.plot, config.steer
344

LE GAC Renaud's avatar
LE GAC Renaud committed
345 346 347
        # transpose
        if steer.transpose:
            df = df.T
348

LE GAC Renaud's avatar
LE GAC Renaud committed
349 350
        # generate the plot using a specific set of columns
        if steer.index and len(steer.index) <= len(df.columns):
351
            ax = df.ix[:, steer.index].plot(**plot)
352

LE GAC Renaud's avatar
LE GAC Renaud committed
353 354
        # generate the plot using all columns
        else:
355
            ax = df.ix[:, :].plot(**plot)
356

LE GAC Renaud's avatar
LE GAC Renaud committed
357 358 359 360 361
        # persistence
        self.ax = ax

    def _do_labels(self):
        """Deal with axes label.
362

LE GAC Renaud's avatar
LE GAC Renaud committed
363 364 365
        """
        ax = self.ax
        steer = self.config.steer
366

LE GAC Renaud's avatar
LE GAC Renaud committed
367
        if steer.xlabel:
368
            ax.set_xlabel(steer.xlabel, x=1, horizontalalignment="right")
369

LE GAC Renaud's avatar
LE GAC Renaud committed
370
        if steer.ylabel:
371
            ax.set_ylabel(steer.ylabel, y=1, horizontalalignment="right")
LE GAC Renaud's avatar
LE GAC Renaud committed
372 373 374

    def _do_legend(self):
        """Deal with legend.
375

LE GAC Renaud's avatar
LE GAC Renaud committed
376 377 378 379 380 381
        """
        ax = self.ax

        if ax.get_legend():
            box = ax.get_position()
            ax.set_position([box.x0, box.y0, box.width, box.height * 0.9])
382
            ax.legend(loc="lower right",
383
                      bbox_to_anchor=(1.01, 1.),
LE GAC Renaud's avatar
LE GAC Renaud committed
384 385
                      fontsize=10,
                      ncol=3)
386

LE GAC Renaud's avatar
LE GAC Renaud committed
387 388
    def _do_tick(self):
        """Polish the tick mark
389

LE GAC Renaud's avatar
LE GAC Renaud committed
390 391
        """
        ax = self.ax
392

LE GAC Renaud's avatar
LE GAC Renaud committed
393
        ax.minorticks_on()
394 395
        ax.tick_params(which="major", length=8)
        ax.tick_params(which="minor", length=4)
LE GAC Renaud's avatar
LE GAC Renaud committed
396

397
    def _savefig(self, fmt):
398
        """Save the figure as a string.
399

LE GAC Renaud's avatar
LE GAC Renaud committed
400 401
        Args:
            fmt (str): possible values are pdf, png and svg.
402

403 404
        """
        fig = self.ax.get_figure()
405

406
        fi = StringIO()
407
        fig.savefig(fi, format=fmt)
408 409
        data = fi.getvalue()
        fi.close()
410

411 412
        fig.clear()
        matplotlib.pyplot.close(fig)
413

414
        return data
415

LE GAC Renaud's avatar
LE GAC Renaud committed
416
    def to_pdf(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
417 418
        """Encode the graph using the PDF format

LE GAC Renaud's avatar
LE GAC Renaud committed
419
        Returns:
LE GAC Renaud's avatar
LE GAC Renaud committed
420
            str:
421

LE GAC Renaud's avatar
LE GAC Renaud committed
422
        """
423
        return self._savefig("pdf")
424

LE GAC Renaud's avatar
LE GAC Renaud committed
425
    def to_png(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
426 427
        """Encode the graph using the PNG format.

LE GAC Renaud's avatar
LE GAC Renaud committed
428
        Returns:
LE GAC Renaud's avatar
LE GAC Renaud committed
429
            str:
430

LE GAC Renaud's avatar
LE GAC Renaud committed
431
        """
432
        return self._savefig("png")
433

LE GAC Renaud's avatar
LE GAC Renaud committed
434
    def to_svg(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
435 436
        """Encode the graph using the SVG format.

LE GAC Renaud's avatar
LE GAC Renaud committed
437
        Returns:
LE GAC Renaud's avatar
LE GAC Renaud committed
438
            str:
439

LE GAC Renaud's avatar
LE GAC Renaud committed
440
        """
441
        return self._savefig("svg")
LE GAC Renaud's avatar
LE GAC Renaud committed
442 443


444
class List(BaseReport):
LE GAC Renaud's avatar
LE GAC Renaud committed
445 446 447
    """Build a report as a list.

    A list is a table in which each column contains the values of
448 449 450 451
    one database field. The rows can be grouped per value of a given column.
    Summary information can be computed for each group as well as for
    the whole table.

452
    The list is displayed as the ``Dbui.grid.Panel`` widget.
453
    The configuration of the list columns is the configuration of
454
    the ``Dbui.grid.Panel`` object.
455

456
    More technically, this class interfaces the database and the
457
    ``Dbui.grid.Panel`` thought the underlying ``Ext.data.Store``.
LE GAC Renaud's avatar
LE GAC Renaud committed
458
    Its configuration is returned by the method *to_store*.
LE GAC Renaud's avatar
LE GAC Renaud committed
459 460

    Args:
LE GAC Renaud's avatar
LE GAC Renaud committed
461
        config (gluon.dal.Row): the configuration parameter for the list.
462
        selector (EvtSelector): selector handling period of time.
463

464
    """
465 466 467
    def __init__(self, config, selector):

        BaseReport.__init__(self, config, selector)
468

469
        # decode column configuration
470
        columns = [Storage(el) for el in json.loads(config.columns)]
471

472
        # check column configuration
473
        # add database field map (tablename, fieldname, keyname)
474
        # add the dataIndex (DataFrame, Ext.data.Store, Ext.grid.Panel)
475
        map(self._check_column, columns)
476

477
        # columns are persistent
478
        self._columns = columns
479

480 481
        # instantiate and fill the DataFrame
        self._do_metric()
482

483
    def _cast_type(self, column, dbfield, xtype):
484 485
        """Cast the type of a dataframe column to the database field type
        or to the grid column xtype.
486

LE GAC Renaud's avatar
LE GAC Renaud committed
487
        The type of the column determine by the pandas might be wrong.
488
        This append when events are merged with different user block.
489

490 491
        This is fine in most of the case but not with computed column.
        In that case the eval computation crashed.
492 493

        This method avoid this problem. It also convert properly
494
        datetime column allowing computation with them.
495

LE GAC Renaud's avatar
LE GAC Renaud committed
496 497 498
        Args:
            column (str):
                the index of the column in the DataFrame
499

LE GAC Renaud's avatar
LE GAC Renaud committed
500 501 502
            dbfield (tuple):
                address of the database field encoded as
                (tablename, fieldname, keyname).
503

LE GAC Renaud's avatar
LE GAC Renaud committed
504 505 506 507
            xtype (str):
                the xtype of the grid column.
                Possible values are ``booleancolumn``, ``datecolumn``,
                ``gridcolumn`` and ``numbercolumn``.
508

LE GAC Renaud's avatar
LE GAC Renaud committed
509 510
        """
        df = self.df
511
        tablename, fieldname = dbfield[0:2]
LE GAC Renaud's avatar
LE GAC Renaud committed
512

513
        # the dtype of column containing a mixture of type is object.
514
        if (tablename == "year") or (df[column].dtype != "object"):
515
            return
516

517 518 519
        dbtype = self.db[tablename][fieldname].type

        # the dtype for column containing string is also object
520
        if dbtype in ("string", "text"):
521
            return
522

523 524
        elif dbtype == "boolean":
            df[column] = df[column].astype("bool")
525

526
        elif dbtype in ("date", "datetime", "time"):
527
            df[column] = to_datetime(df[column])
528

529 530
        elif dbtype in ("double", "integer"):
            df[column] = df[column].astype("float64")
531

532 533 534
        # database field containing JSON-type dictionary
        # The type of the key is defined in the event model but it is
        # not accessible at this stage. Instead we use the grid column xtype.
535
        elif dbtype == "json":
536

537
            if xtype == "gridcolumn":
538
                pass
LE GAC Renaud's avatar
LE GAC Renaud committed
539

540 541
            elif xtype == "booleancolumn":
                df[column] = df[column].astype("bool")
542

543
            elif xtype == "datecolumn":
544
                df[column] = to_datetime(df[column])
545

546 547
            elif xtype == "numbercolumn":
                df[column] = df[column].astype("float64")
548

549 550
    def _check_column(self, column):
        """Check column configuration:
551

552 553
            - Raise an exception if xtype is not defined
            - Raise an exception when eval is defined but not the dataIndex
554
            - Add the database field map
555
            - Add the dataIndex if not defined
556

LE GAC Renaud's avatar
LE GAC Renaud committed
557 558
        Args:
            column (gluon.storage.Storage):
559

560 561 562 563 564 565
        """
        T = current.T

        xtype = column.xtype
        if not xtype:
            raise ReportException(T(MSG_NO_XTYPE))
566

567 568 569
        if column.eval and not column.dataIndex:
            raise ReportException(T(MSG_NO_DATAINDEX))

570
        dbfield = column.dbfield
571 572 573 574
        if dbfield:
            column.map = split_dbfield(dbfield)

        if not (column.dataIndex or xtype == "rownumberer"):
575
            column.dataIndex = column.dbfield.replace(".", "")
576

577 578
    def _do_metric(self):
        """Interface the database with the DataFrame structure.
LE GAC Renaud's avatar
LE GAC Renaud committed
579
        This method handle the ``year`` database field.
580

581 582

        """
583
        columns = self._columns
584

LE GAC Renaud's avatar
LE GAC Renaud committed
585 586 587 588 589 590 591
        # extract columns associated to database fields
        maps, index, xtypes = [], [], []
        for column in columns:
            if column.dbfield:
                maps.append(column.map)
                index.append(column.dataIndex)
                xtypes.append(column.xtype)
592

593 594
        # extract data from the database
        data = self._do_data(maps)
595

596
        # protection
597
        if not data:
598
            self.df = DataFrame(columns=index)
599
            return
600

601
        # fill the DataFrame
602
        df = DataFrame(data, columns=index)
603 604

        # make the data frame persistent
LE GAC Renaud's avatar
LE GAC Renaud committed
605 606
        self.df = df

607 608
        # cast dataframe column type to database type or grid column xtype
        map(self._cast_type, index, maps, xtypes)
609

LE GAC Renaud's avatar
LE GAC Renaud committed
610
        # add computed columns
611 612 613 614 615 616 617 618
        for el in columns:
            if el.eval:
                df[el.dataIndex] = df.eval(el.eval)

        # re-order the column to follow user requirement
        # skip rownumberer column (None index)
        index = [el.dataIndex for el in columns if el.dataIndex]
        df = df[index]
619

620
    def _set_store_data(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
621
        """Generate the ``Ext.data.Store.data`` property.
622
        It is a list of dictionaries. Each of them contains the data
LE GAC Renaud's avatar
LE GAC Renaud committed
623 624
        for one row. One key, value pair for each ``Ext.data.Field`` where
        the key is the name of the ``Ext.data.Field``.
625 626

        """
627 628
        # extract the list of records as a JSON-string
        # at this stage date/time are converted as an ISO8601 string
629
        data = self.df.to_json(orient="records", date_format="iso")
630

631 632
        # convert the JSON-string into a list
        self._store.data = json.loads(data)
633

634
    def _set_store_fields(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
635 636
        """Generate the ``Ext.data.Store.fields`` property.
        It is a list of ``Ext.data.Field`` configuration.
637

LE GAC Renaud's avatar
LE GAC Renaud committed
638 639 640 641 642
        Note:
            The name of the ``Ext.data.Field`` is derived from the
            address of the database field. The former can not contains dot.
            Therefore, it is obtained by removing dot in the database
            field address.
643

644 645
        """
        db = self.db
646
        columns = self._columns
647
        store = self._store
648

649 650
        # convert the columns into the configuration of an Ext.data.Field
        for el in columns:
651

652 653 654
            # protection against rownumberer column
            if not el.dataIndex:
                continue
655

656 657
            tablename, fieldname, keyname = el.map

658
            cfg = Storage(name=el.dataIndex)
659

660
            # the pseudo field year
661 662
            if el.dbfield == "year":
                cfg.type = "int"
663

664 665
            # the computed column
            elif el.eval:
666
                cfg.type = "float"
667

668 669 670 671
            # json type database field
            elif keyname:
                xtype = el.xtype

672 673
                if xtype == "gridcolumn":
                    cfg.type = "string"
674

675 676
                elif xtype == "booleancolumn":
                    cfg.type = "boolean"
677

678 679
                elif xtype == "datecolumn":
                    cfg.type = "date"
680

681 682
                elif xtype == "numbercolumn":
                    cfg.type = "float"
683

684 685 686
            # standard database field, extract the type from the database field
            else:
                dbfield = db[tablename][fieldname]
687

688
                cfg.type = dbfield.type
689 690
                if dbfield.type in ("blob", "string", "text", "json"):
                    cfg.type = "string"
691

692 693
                elif dbfield.type == "boolean":
                    cfg.type = "boolean"
694

695 696 697
                elif dbfield.type in ("date", "datetime", "time"):
                    cfg.type = "date"
                    cfg.dateFormat = "c"
698

699 700
                elif dbfield.type == "double":
                    cfg.type = "float"
701

702 703
                elif dbfield.type == "integer":
                    cfg.type = "int"
704

705
            store.fields.append(cfg)
706

707
    def to_grid(self):
708
        """Build the configuration for the ``Dbui.grid.Panel``.
709

LE GAC Renaud's avatar
LE GAC Renaud committed
710
        Returns:
711 712 713
            gluon.storage.Storage:
                the keys are ``columns`` and ``features`` and the
                corresponding values are list of dictionary.
714

715 716
        """
        config = self.config
717
        grid = Storage(columns=[], features=[])
718 719

        # column from the configuration
720
        # remove non Ext JS property
721
        for cfg in self._columns:
722