Package web2py :: Package gluon :: Module sqlhtml

[hide private]
[frames] | no frames]

# Source Code for Module web2py.gluon.sqlhtml

       1  #!/usr/bin/env python
       2  # -*- coding: utf-8 -*-
       3
       4  """
       5  This file is part of the web2py Web Framework
       6  Copyrighted by Massimo Di Pierro <mdipierro@cs.depaul.edu>
       7  License: LGPLv3 (http://www.gnu.org/licenses/lgpl.html)
       8
       9  Holds:
      10
      11  - SQLFORM: provide a form for a table (with/without record)
      12  - SQLTABLE: provides a table for a set of records
      13  - form_factory: provides a SQLFORM for an non-db backed table
      14
      15  """
      16
      17  from http import HTTP
      18  from html import XML, SPAN, TAG, A, DIV, CAT, UL, LI, TEXTAREA, BR, IMG, SCRIPT
      19  from html import FORM, INPUT, LABEL, OPTION, SELECT, MENU
      20  from html import TABLE, THEAD, TBODY, TR, TD, TH, STYLE
      21  from html import URL, truncate_string
      22  from dal import DAL, Table, Row, CALLABLETYPES, smart_query
      23  from storage import Storage
      24  from utils import md5_hash
      25  from validators import IS_EMPTY_OR, IS_NOT_EMPTY, IS_LIST_OF
      26
      27  import urllib
      28  import re
      29  import cStringIO
      30
      31  table_field = re.compile('[\w_]+\.[\w_]+')
      32  widget_class = re.compile('^\w*')
      33
      34 -def trap_class(_class=None,trap=True):
      35      return (trap and 'w2p_trap' or '')+(_class and ' '+_class or '')
      36
      37 -def represent(field,value,record):
      38      f = field.represent
      39      if not callable(f):
      40          return str(value)
      41      n = f.func_code.co_argcount-len(f.func_defaults or [])
      42      if getattr(f, 'im_self', None): n -= 1
      43      if n==1:
      44          return f(value)
      45      elif n==2:
      46          return f(value,record)
      47      else:
      48          raise RuntimeError, "field representation must take 1 or 2 args"
      49
      50 -def safe_int(x):
      51      try:
      52          return int(x)
      53      except ValueError:
      54          return 0
      55
      56 -def safe_float(x):
      57      try:
      58          return float(x)
      59      except ValueError:
      60          return 0
      61
      62 -class FormWidget(object):
      63      """
      64      helper for SQLFORM to generate form input fields
      65      (widget), related to the fieldtype
      66      """
      67
      68      _class = 'generic-widget'
      69
      70      @classmethod
      71 -    def _attributes(cls, field,
      72                      widget_attributes, **attributes):
      73          """
      74          helper to build a common set of attributes
      75
      76          :param field: the field involved,
      77                        some attributes are derived from this
      78          :param widget_attributes:  widget related attributes
      79          :param attributes: any other supplied attributes
      80          """
      81          attr = dict(
      82              _id = '%s_%s' % (field._tablename, field.name),
      83              _class = cls._class or \
      84                  widget_class.match(str(field.type)).group(),
      85              _name = field.name,
      86              requires = field.requires,
      87              )
      88          attr.update(widget_attributes)
      89          attr.update(attributes)
      90          return attr
      91
      92      @classmethod
      93 -    def widget(cls, field, value, **attributes):
      94          """
      95          generates the widget for the field.
      96
      97          When serialized, will provide an INPUT tag:
      98
      99          - id = tablename_fieldname
     100          - class = field.type
     101          - name = fieldname
     102
     103          :param field: the field needing the widget
     104          :param value: value
     105          :param attributes: any other attributes to be applied
     106          """
     107
     108          raise NotImplementedError
     109
     110 -class StringWidget(FormWidget):
     111      _class = 'string'
     112
     113      @classmethod
     114 -    def widget(cls, field, value, **attributes):
     115          """
     116          generates an INPUT text tag.
     117
     118          see also: :meth:`FormWidget.widget`
     119          """
     120
     121          default = dict(
     122              _type = 'text',
     123              value = (not value is None and str(value)) or '',
     124              )
     125          attr = cls._attributes(field, default, **attributes)
     126
     127          return INPUT(**attr)
     128
     129
     130 -class IntegerWidget(StringWidget):
     131      _class = 'integer'
     132
     133
     134 -class DoubleWidget(StringWidget):
     135      _class = 'double'
     136
     137
     138 -class DecimalWidget(StringWidget):
     139      _class = 'decimal'
     140
     141
     142 -class TimeWidget(StringWidget):
     143      _class = 'time'
     144
     145
     146 -class DateWidget(StringWidget):
     147      _class = 'date'
     148
     149
     150 -class DatetimeWidget(StringWidget):
     151      _class = 'datetime'
     152
     153
     154 -class TextWidget(FormWidget):
     155      _class = 'text'
     156
     157      @classmethod
     158 -    def widget(cls, field, value, **attributes):
     159          """
     160          generates a TEXTAREA tag.
     161
     162          see also: :meth:`FormWidget.widget`
     163          """
     164
     165          default = dict(value = value)
     166          attr = cls._attributes(field, default,
     167                                 **attributes)
     168          return TEXTAREA(**attr)
     169
     170
     171 -class BooleanWidget(FormWidget):
     172      _class = 'boolean'
     173
     174      @classmethod
     175 -    def widget(cls, field, value, **attributes):
     176          """
     177          generates an INPUT checkbox tag.
     178
     179          see also: :meth:`FormWidget.widget`
     180          """
     181
     182          default=dict(_type='checkbox', value=value)
     183          attr = cls._attributes(field, default,
     184                                 **attributes)
     185          return INPUT(**attr)
     186
     187
     188 -class OptionsWidget(FormWidget):
     189
     190      @staticmethod
     191 -    def has_options(field):
     192          """
     193          checks if the field has selectable options
     194
     195          :param field: the field needing checking
     196          :returns: True if the field has options
     197          """
     198
     199          return hasattr(field.requires, 'options')
     200
     201      @classmethod
     202 -    def widget(cls, field, value, **attributes):
     203          """
     204          generates a SELECT tag, including OPTIONs (only 1 option allowed)
     205
     206          see also: :meth:`FormWidget.widget`
     207          """
     208          default = dict(value=value)
     209          attr = cls._attributes(field, default,
     210                                 **attributes)
     211          requires = field.requires
     212          if not isinstance(requires, (list, tuple)):
     213              requires = [requires]
     214          if requires:
     215              if hasattr(requires[0], 'options'):
     216                  options = requires[0].options()
     217              else:
     218                  raise SyntaxError, 'widget cannot determine options of %s' % field
     219          opts = [OPTION(v, _value=k) for (k, v) in options]
     220
     221          return SELECT(*opts, **attr)
     222
     223 -class ListWidget(StringWidget):
     224
     225      @classmethod
     226 -    def widget(cls, field, value, **attributes):
     227          _id = '%s_%s' % (field._tablename, field.name)
     228          _name = field.name
     229          if field.type=='list:integer': _class = 'integer'
     230          else: _class = 'string'
     231          requires = field.requires if isinstance(field.requires, (IS_NOT_EMPTY, IS_LIST_OF)) else None
     232          items=[LI(INPUT(_id=_id, _class=_class, _name=_name, value=v, hideerror=True, requires=requires)) \
     233                     for v in value or ['']]
     234          script=SCRIPT("""
     235  // from http://refactormycode.com/codes/694-expanding-input-list-using-jquery
     236  (function(){
     237  jQuery.fn.grow_input = function() {
     238    return this.each(function() {
     239      var ul = this;
     240      jQuery(ul).find(":text").after('<a href="javascript:void(0)>+</a>').keypress(function (e) { return (e.which == 13) ? pe(ul) : true; }).next().click(function(){ pe(ul) });
     241    });
     242  };
     243  function pe(ul) {
     244    var new_line = ml(ul);
     245    rel(ul);
     246    new_line.appendTo(ul);
     247    new_line.find(":text").focus();
     248    return false;
     249  }
     250  function ml(ul) {
     251    var line = jQuery(ul).find("li:first").clone(true);
     252    line.find(':text').val('');
     253    return line;
     254  }
     255  function rel(ul) {
     256    jQuery(ul).find("li").each(function() {
     257      var trimmed = jQuery.trim(jQuery(this.firstChild).val());
     258      if (trimmed=='') jQuery(this).remove(); else jQuery(this.firstChild).val(trimmed);
     259    });
     260  }
     261  })();
     262  jQuery(document).ready(function(){jQuery('#%s_grow_input').grow_input();});
     263  """ % _id)
     264          attributes['_id']=_id+'_grow_input'
     265          return TAG[''](UL(*items,**attributes),script)
     266
     267
     268 -class MultipleOptionsWidget(OptionsWidget):
     269
     270      @classmethod
     271 -    def widget(cls, field, value, size=5, **attributes):
     272          """
     273          generates a SELECT tag, including OPTIONs (multiple options allowed)
     274
     275          see also: :meth:`FormWidget.widget`
     276
     277          :param size: optional param (default=5) to indicate how many rows must
     278              be shown
     279          """
     280
     281          attributes.update(dict(_size=size, _multiple=True))
     282
     283          return OptionsWidget.widget(field, value, **attributes)
     284
     285
     286 -class RadioWidget(OptionsWidget):
     287
     288      @classmethod
     289 -    def widget(cls, field, value, **attributes):
     290          """
     291          generates a TABLE tag, including INPUT radios (only 1 option allowed)
     292
     293          see also: :meth:`FormWidget.widget`
     294          """
     295
     296          attr = cls._attributes(field, {}, **attributes)
     297          attr['_class'] = attr.get('_class','web2py_radiowidget')
     298
     299          requires = field.requires
     300          if not isinstance(requires, (list, tuple)):
     301              requires = [requires]
     302          if requires:
     303              if hasattr(requires[0], 'options'):
     304                  options = requires[0].options()
     305              else:
     306                  raise SyntaxError, 'widget cannot determine options of %s' \
     307                      % field
     308          options = [(k, v) for k, v in options if str(v)]
     309          opts = []
     310          cols = attributes.get('cols',1)
     311          totals = len(options)
     312          mods = totals%cols
     313          rows = totals/cols
     314          if mods:
     315              rows += 1
     316
     317          #widget style
     318          wrappers = dict(
     319              table=(TABLE,TR,TD),
     320              ul=(DIV,UL,LI),
     321              divs=(CAT,DIV,DIV)
     322              )
     323          parent, child, inner = wrappers[attributes.get('style','table')]
     324
     325          for r_index in range(rows):
     326              tds = []
     327              for k, v in options[r_index*cols:(r_index+1)*cols]:
     328                  checked={'_checked':'checked'} if k==value else {}
     329                  tds.append(inner(INPUT(_type='radio',
     330                                         _id='%s%s' % (field.name,k),
     331                                         _name=field.name,
     332                                         requires=attr.get('requires',None),
     333                                         hideerror=True, _value=k,
     334                                         value=value,
     335                                         **checked),
     336                                         LABEL(v,_for='%s%s' % (field.name,k))))
     337              opts.append(child(tds))
     338
     339          if opts:
     340              opts[-1][0][0]['hideerror'] = False
     341          return parent(*opts, **attr)
     342
     343
     344 -class CheckboxesWidget(OptionsWidget):
     345
     346      @classmethod
     347 -    def widget(cls, field, value, **attributes):
     348          """
     349          generates a TABLE tag, including INPUT checkboxes (multiple allowed)
     350
     351          see also: :meth:`FormWidget.widget`
     352          """
     353
     354          # was values = re.compile('[\w\-:]+').findall(str(value))
     355          if isinstance(value, (list, tuple)):
     356              values = [str(v) for v in value]
     357          else:
     358              values = [str(value)]
     359
     360          attr = cls._attributes(field, {}, **attributes)
     361          attr['_class'] = attr.get('_class','web2py_checkboxeswidget')
     362
     363          requires = field.requires
     364          if not isinstance(requires, (list, tuple)):
     365              requires = [requires]
     366          if requires:
     367              if hasattr(requires[0], 'options'):
     368                  options = requires[0].options()
     369              else:
     370                  raise SyntaxError, 'widget cannot determine options of %s' \
     371                      % field
     372
     373          options = [(k, v) for k, v in options if k != '']
     374          opts = []
     375          cols = attributes.get('cols', 1)
     376          totals = len(options)
     377          mods = totals % cols
     378          rows = totals / cols
     379          if mods:
     380              rows += 1
     381
     382          #widget style
     383          wrappers = dict(
     384              table=(TABLE,TR,TD),
     385              ul=(DIV,UL,LI),
     386              divs=(CAT,DIV,DIV)
     387              )
     388          parent, child, inner = wrappers[attributes.get('style','table')]
     389
     390          for r_index in range(rows):
     391              tds = []
     392              for k, v in options[r_index*cols:(r_index+1)*cols]:
     393                  if k in values:
     394                      r_value = k
     395                  else:
     396                      r_value = []
     397                  tds.append(inner(INPUT(_type='checkbox',
     398                                         _id='%s%s' % (field.name,k),
     399                                         _name=field.name,
     400                                         requires=attr.get('requires', None),
     401                                         hideerror=True, _value=k,
     402                                         value=r_value),
     403                                         LABEL(v,_for='%s%s' % (field.name,k))))
     404              opts.append(child(tds))
     405
     406          if opts:
     407              opts[-1][0][0]['hideerror'] = False
     408          return parent(*opts, **attr)
     409
     410
     411 -class PasswordWidget(FormWidget):
     412      _class = 'password'
     413
     414      DEFAULT_PASSWORD_DISPLAY = 8*('*')
     415
     416      @classmethod
     417 -    def widget(cls, field, value, **attributes):
     418          """
     419          generates a INPUT password tag.
     420          If a value is present it will be shown as a number of '*', not related
     421          to the length of the actual value.
     422
     423          see also: :meth:`FormWidget.widget`
     424          """
     425
     426          default=dict(
     427              _type='password',
     428              _value=(value and cls.DEFAULT_PASSWORD_DISPLAY) or '',
     429              )
     430          attr = cls._attributes(field, default, **attributes)
     431
     432          return INPUT(**attr)
     433
     434
     435 -class UploadWidget(FormWidget):
     436      _class = 'upload'
     437
     438      DEFAULT_WIDTH = '150px'
     439      ID_DELETE_SUFFIX = '__delete'
     440      GENERIC_DESCRIPTION = 'file'
     441      DELETE_FILE = 'delete'
     442
     443      @classmethod
     444 -    def widget(cls, field, value, download_url=None, **attributes):
     445          """
     446          generates a INPUT file tag.
     447
     448          Optionally provides an A link to the file, including a checkbox so
     449          the file can be deleted.
     450          All is wrapped in a DIV.
     451
     452          see also: :meth:`FormWidget.widget`
     453
     454          :param download_url: Optional URL to link to the file (default = None)
     455          """
     456
     457          default=dict(_type='file',)
     458          attr = cls._attributes(field, default, **attributes)
     459
     460          inp = INPUT(**attr)
     461
     462          if download_url and value:
     463              if callable(download_url):
     464                  url = download_url(value)
     465              else:
     466                  url = download_url + '/' + value
     467              (br, image) = ('', '')
     468              if UploadWidget.is_image(value):
     469                  br = BR()
     470                  image = IMG(_src = url, _width = cls.DEFAULT_WIDTH)
     471
     472              requires = attr["requires"]
     473              if requires == [] or isinstance(requires, IS_EMPTY_OR):
     474                  inp = DIV(inp, '[',
     475                            A(UploadWidget.GENERIC_DESCRIPTION, _href = url),
     476                            '|',
     477                            INPUT(_type='checkbox',
     478                                  _name=field.name + cls.ID_DELETE_SUFFIX,
     479                                  _id=field.name + cls.ID_DELETE_SUFFIX),
     480                                  LABEL(cls.DELETE_FILE,
     481                                       _for=field.name + cls.ID_DELETE_SUFFIX),
     482                                  ']', br, image)
     483              else:
     484                  inp = DIV(inp, '[',
     485                            A(cls.GENERIC_DESCRIPTION, _href = url),
     486                            ']', br, image)
     487          return inp
     488
     489      @classmethod
     490 -    def represent(cls, field, value, download_url=None):
     491          """
     492          how to represent the file:
     493
     494          - with download url and if it is an image: <A href=...><IMG ...></A>
     495          - otherwise with download url: <A href=...>file</A>
     496          - otherwise: file
     497
     498          :param field: the field
     499          :param value: the field value
     500          :param download_url: url for the file download (default = None)
     501          """
     502
     503          inp = cls.GENERIC_DESCRIPTION
     504
     505          if download_url and value:
     506              if callable(download_url):
     507                  url = download_url(value)
     508              else:
     509                  url = download_url + '/' + value
     510              if cls.is_image(value):
     511                  inp = IMG(_src = url, _width = cls.DEFAULT_WIDTH)
     512              inp = A(inp, _href = url)
     513
     514          return inp
     515
     516      @staticmethod
     517 -    def is_image(value):
     518          """
     519          Tries to check if the filename provided references to an image
     520
     521          Checking is based on filename extension. Currently recognized:
     522             gif, png, jp(e)g, bmp
     523
     524          :param value: filename
     525          """
     526
     527          extension = value.split('.')[-1].lower()
     528          if extension in ['gif', 'png', 'jpg', 'jpeg', 'bmp']:
     529              return True
     530          return False
     531
     532
     533 -class AutocompleteWidget(object):
     534      _class = 'string'
     535
     536 -    def __init__(self, request, field, id_field=None, db=None,
     537                   orderby=None, limitby=(0,10),
     538                   keyword='_autocomplete_%(fieldname)s',
     539                   min_length=2):
     540          self.request = request
     541          self.keyword = keyword % dict(fieldname=field.name)
     542          self.db = db or field._db
     543          self.orderby = orderby
     544          self.limitby = limitby
     545          self.min_length = min_length
     546          self.fields=[field]
     547          if id_field:
     548              self.is_reference = True
     549              self.fields.append(id_field)
     550          else:
     551              self.is_reference = False
     552          if hasattr(request,'application'):
     553              self.url = URL(args=request.args)
     554              self.callback()
     555          else:
     556              self.url = request
     557 -    def callback(self):
     558          if self.keyword in self.request.vars:
     559              field = self.fields[0]
     560              rows = self.db(field.like(self.request.vars[self.keyword]+'%'))\
     561                  .select(orderby=self.orderby,limitby=self.limitby,*self.fields)
     562              if rows:
     563                  if self.is_reference:
     564                      id_field = self.fields[1]
     565                      raise HTTP(200,SELECT(_id=self.keyword,_class='autocomplete',
     566                                            _size=len(rows),_multiple=(len(rows)==1),
     567                                            *[OPTION(s[field.name],_value=s[id_field.name],
     568                                                     _selected=(k==0)) \
     569                                                  for k,s in enumerate(rows)]).xml())
     570                  else:
     571                      raise HTTP(200,SELECT(_id=self.keyword,_class='autocomplete',
     572                                            _size=len(rows),_multiple=(len(rows)==1),
     573                                            *[OPTION(s[field.name],
     574                                                     _selected=(k==0)) \
     575                                                  for k,s in enumerate(rows)]).xml())
     576              else:
     577
     578                  raise HTTP(200,'')
     579 -    def __call__(self,field,value,**attributes):
     580          default = dict(
     581              _type = 'text',
     582              value = (not value is None and str(value)) or '',
     583              )
     584          attr = StringWidget._attributes(field, default, **attributes)
     585          div_id = self.keyword+'_div'
     586          attr['_autocomplete']='off'
     587          if self.is_reference:
     588              key2 = self.keyword+'_aux'
     589              key3 = self.keyword+'_auto'
     590              attr['_class']='string'
     591              name = attr['_name']
     592              if 'requires' in attr: del attr['requires']
     593              attr['_name'] = key2
     594              value = attr['value']
     595              record = self.db(self.fields[1]==value).select(self.fields[0]).first()
     596              attr['value'] = record and record[self.fields[0].name]
     597              attr['_onblur']="jQuery('#%(div_id)s').delay(3000).fadeOut('slow');" % \
     598                  dict(div_id=div_id,u='F'+self.keyword)
     599              attr['_onkeyup'] = "jQuery('#%(key3)s').val('');var e=event.which?event.which:event.keyCode; function %(u)s(){jQuery('#%(id)s').val(jQuery('#%(key)s :selected').text());jQuery('#%(key3)s').val(jQuery('#%(key)s').val())}; if(e==39) %(u)s(); else if(e==40) {if(jQuery('#%(key)s option:selected').next().length)jQuery('#%(key)s option:selected').attr('selected',null).next().attr('selected','selected'); %(u)s();} else if(e==38) {if(jQuery('#%(key)s option:selected').prev().length)jQuery('#%(key)s option:selected').attr('selected',null).prev().attr('selected','selected'); %(u)s();} else if(jQuery('#%(id)s').val().length>=%(min_length)s) jQuery.get('%(url)s?%(key)s='+escape(jQuery('#%(id)s').val()),function(data){if(data=='')jQuery('#%(key3)s').val('');else{jQuery('#%(id)s').next('.error').hide();jQuery('#%(div_id)s').html(data).show().focus();jQuery('#%(div_id)s select').css('width',jQuery('#%(id)s').css('width'));jQuery('#%(key3)s').val(jQuery('#%(key)s').val());jQuery('#%(key)s').change(%(u)s);jQuery('#%(key)s').click(%(u)s);};}); else jQuery('#%(div_id)s').fadeOut('slow');" % \
     600                  dict(url=self.url,min_length=self.min_length,
     601                       key=self.keyword,id=attr['_id'],key2=key2,key3=key3,
     602                       name=name,div_id=div_id,u='F'+self.keyword)
     603              if self.min_length==0:
     604                  attr['_onfocus'] = attr['_onkeyup']
     605              return TAG[''](INPUT(**attr),INPUT(_type='hidden',_id=key3,_value=value,
     606                                                 _name=name,requires=field.requires),
     607                             DIV(_id=div_id,_style='position:absolute;'))
     608          else:
     609              attr['_name']=field.name
     610              attr['_onblur']="jQuery('#%(div_id)s').delay(3000).fadeOut('slow');" % \
     611                  dict(div_id=div_id,u='F'+self.keyword)
     612              attr['_onkeyup'] = "var e=event.which?event.which:event.keyCode; function %(u)s(){jQuery('#%(id)s').val(jQuery('#%(key)s').val())}; if(e==39) %(u)s(); else if(e==40) {if(jQuery('#%(key)s option:selected').next().length)jQuery('#%(key)s option:selected').attr('selected',null).next().attr('selected','selected'); %(u)s();} else if(e==38) {if(jQuery('#%(key)s option:selected').prev().length)jQuery('#%(key)s option:selected').attr('selected',null).prev().attr('selected','selected'); %(u)s();} else if(jQuery('#%(id)s').val().length>=%(min_length)s) jQuery.get('%(url)s?%(key)s='+escape(jQuery('#%(id)s').val()),function(data){jQuery('#%(id)s').next('.error').hide();jQuery('#%(div_id)s').html(data).show().focus();jQuery('#%(div_id)s select').css('width',jQuery('#%(id)s').css('width'));jQuery('#%(key)s').change(%(u)s);jQuery('#%(key)s').click(%(u)s);}); else jQuery('#%(div_id)s').fadeOut('slow');" % \
     613                  dict(url=self.url,min_length=self.min_length,
     614                       key=self.keyword,id=attr['_id'],div_id=div_id,u='F'+self.keyword)
     615              if self.min_length==0:
     616                  attr['_onfocus'] = attr['_onkeyup']
     617              return TAG[''](INPUT(**attr),DIV(_id=div_id,_style='position:absolute;'))
     618
     619
     620 -class SQLFORM(FORM):
     621
     622      """
     623      SQLFORM is used to map a table (and a current record) into an HTML form
     624
     625      given a SQLTable stored in db.table
     626
     627      generates an insert form::
     628
     629          SQLFORM(db.table)
     630
     631      generates an update form::
     632
     633          record=db.table[some_id]
     634          SQLFORM(db.table, record)
     635
     636      generates an update with a delete button::
     637
     638          SQLFORM(db.table, record, deletable=True)
     639
     640      if record is an int::
     641
     642          record=db.table[record]
     643
     644      optional arguments:
     645
     646      :param fields: a list of fields that should be placed in the form,
     647          default is all.
     648      :param labels: a dictionary with labels for each field, keys are the field
     649          names.
     650      :param col3: a dictionary with content for an optional third column
     651              (right of each field). keys are field names.
     652      :param linkto: the URL of a controller/function to access referencedby
     653          records
     654              see controller appadmin.py for examples
     655      :param upload: the URL of a controller/function to download an uploaded file
     656              see controller appadmin.py for examples
     657
     658      any named optional attribute is passed to the <form> tag
     659              for example _class, _id, _style, _action, _method, etc.
     660
     661      """
     662
     663      # usability improvements proposal by fpp - 4 May 2008 :
     664      # - correct labels (for points to field id, not field name)
     665      # - add label for delete checkbox
     666      # - add translatable label for record ID
     667      # - add third column to right of fields, populated from the col3 dict
     668
     669      widgets = Storage(dict(
     670          string = StringWidget,
     671          text = TextWidget,
     672          password = PasswordWidget,
     673          integer = IntegerWidget,
     674          double = DoubleWidget,
     675          decimal = DecimalWidget,
     676          time = TimeWidget,
     677          date = DateWidget,
     678          datetime = DatetimeWidget,
     679          upload = UploadWidget,
     680          boolean = BooleanWidget,
     681          blob = None,
     682          options = OptionsWidget,
     683          multiple = MultipleOptionsWidget,
     684          radio = RadioWidget,
     685          checkboxes = CheckboxesWidget,
     686          autocomplete = AutocompleteWidget,
     687          list = ListWidget,
     688          ))
     689
     690      FIELDNAME_REQUEST_DELETE = 'delete_this_record'
     691      FIELDKEY_DELETE_RECORD = 'delete_record'
     692      ID_LABEL_SUFFIX = '__label'
     693      ID_ROW_SUFFIX = '__row'
     694
     695 -    def __init__(
     696          self,
     697          table,
     698          record = None,
     699          deletable = False,
     700          linkto = None,
     701          upload = None,
     702          fields = None,
     703          labels = None,
     704          col3 = {},
     705          submit_button = 'Submit',
     706          delete_label = 'Check to delete',
     707          showid = True,
     708          readonly = False,
     709          comments = True,
     710          keepopts = [],
     711          ignore_rw = False,
     712          record_id = None,
     713          formstyle = 'table3cols',
     714          buttons = ['submit'],
     715          separator = ': ',
     716          **attributes
     717          ):
     718          """
     719          SQLFORM(db.table,
     720                 record=None,
     721                 fields=['name'],
     722                 labels={'name': 'Your name'},
     723                 linkto=URL(f='table/db/')
     724          """
     725
     726          self.ignore_rw = ignore_rw
     727          self.formstyle = formstyle
     728          nbsp = XML('&nbsp;') # Firefox2 does not display fields with blanks
     729          FORM.__init__(self, *[], **attributes)
     730          ofields = fields
     731          keyed = hasattr(table,'_primarykey')
     732
     733          # if no fields are provided, build it from the provided table
     734          # will only use writable or readable fields, unless forced to ignore
     735          if fields is None:
     736              fields = [f.name for f in table if (ignore_rw or f.writable or f.readable) and not f.compute]
     737          self.fields = fields
     738
     739          # make sure we have an id
     740          if self.fields[0] != table.fields[0] and \
     741                  isinstance(table,Table) and not keyed:
     742              self.fields.insert(0, table.fields[0])
     743
     744          self.table = table
     745
     746          # try to retrieve the indicated record using its id
     747          # otherwise ignore it
     748          if record and isinstance(record, (int, long, str, unicode)):
     749              if not str(record).isdigit():
     750                  raise HTTP(404, "Object not found")
     751              record = table._db(table._id == record).select().first()
     752              if not record:
     753                  raise HTTP(404, "Object not found")
     754          self.record = record
     755
     756          self.record_id = record_id
     757          if keyed:
     758              self.record_id = dict([(k,record and str(record[k]) or None) \
     759                                         for k in table._primarykey])
     760          self.field_parent = {}
     761          xfields = []
     762          self.fields = fields
     763          self.custom = Storage()
     764          self.custom.dspval = Storage()
     765          self.custom.inpval = Storage()
     766          self.custom.label = Storage()
     767          self.custom.comment = Storage()
     768          self.custom.widget = Storage()
     769          self.custom.linkto = Storage()
     770
     771          # default id field name
     772          self.id_field_name = table._id.name
     773
     774          sep = separator or ''
     775
     776          for fieldname in self.fields:
     777              if fieldname.find('.') >= 0:
     778                  continue
     779
     780              field = self.table[fieldname]
     781              comment = None
     782
     783              if comments:
     784                  comment = col3.get(fieldname, field.comment)
     785              if comment is None:
     786                  comment = ''
     787              self.custom.comment[fieldname] = comment
     788
     789              if not labels is None and fieldname in labels:
     790                  label = labels[fieldname]
     791              else:
     792                  label = field.label
     793              self.custom.label[fieldname] = label
     794
     795              field_id = '%s_%s' % (table._tablename, fieldname)
     796
     797              label = LABEL(label, label and sep, _for=field_id,
     798                            _id=field_id+SQLFORM.ID_LABEL_SUFFIX)
     799
     800              row_id = field_id+SQLFORM.ID_ROW_SUFFIX
     801              if field.type == 'id':
     802                  self.custom.dspval.id = nbsp
     803                  self.custom.inpval.id = ''
     804                  widget = ''
     805
     806                  # store the id field name (for legacy databases)
     807                  self.id_field_name = field.name
     808
     809                  if record:
     810                      if showid and field.name in record and field.readable:
     811                          v = record[field.name]
     812                          widget = SPAN(v, _id=field_id)
     813                          self.custom.dspval.id = str(v)
     814                          xfields.append((row_id,label, widget,comment))
     815                      self.record_id = str(record[field.name])
     816                  self.custom.widget.id = widget
     817                  continue
     818
     819              if readonly and not ignore_rw and not field.readable:
     820                  continue
     821
     822              if record:
     823                  default = record[fieldname]
     824              else:
     825                  default = field.default
     826                  if isinstance(default,CALLABLETYPES):
     827                      default=default()
     828
     829              cond = readonly or \
     830                  (not ignore_rw and not field.writable and field.readable)
     831
     832              if default and not cond:
     833                  default = field.formatter(default)
     834              dspval = default
     835              inpval = default
     836
     837              if cond:
     838
     839                  # ## if field.represent is available else
     840                  # ## ignore blob and preview uploaded images
     841                  # ## format everything else
     842
     843                  if field.represent:
     844                      inp = represent(field,default,record)
     845                  elif field.type in ['blob']:
     846                      continue
     847                  elif field.type == 'upload':
     848                      inp = UploadWidget.represent(field, default, upload)
     849                  elif field.type == 'boolean':
     850                      inp = self.widgets.boolean.widget(field, default, _disabled=True)
     851                  else:
     852                      inp = field.formatter(default)
     853              elif field.type == 'upload':
     854                  if hasattr(field, 'widget') and field.widget:
     855                      inp = field.widget(field, default, upload)
     856                  else:
     857                      inp = self.widgets.upload.widget(field, default, upload)
     858              elif hasattr(field, 'widget') and field.widget:
     859                  inp = field.widget(field, default)
     860              elif field.type == 'boolean':
     861                  inp = self.widgets.boolean.widget(field, default)
     862                  if default:
     863                      inpval = 'checked'
     864                  else:
     865                      inpval = ''
     866              elif OptionsWidget.has_options(field):
     867                  if not field.requires.multiple:
     868                      inp = self.widgets.options.widget(field, default)
     869                  else:
     870                      inp = self.widgets.multiple.widget(field, default)
     871                  if fieldname in keepopts:
     872                      inpval = TAG[''](*inp.components)
     873              elif field.type.startswith('list:'):
     874                  inp = self.widgets.list.widget(field,default)
     875              elif field.type == 'text':
     876                  inp = self.widgets.text.widget(field, default)
     877              elif field.type == 'password':
     878                  inp = self.widgets.password.widget(field, default)
     879                  if self.record:
     880                      dspval = PasswordWidget.DEFAULT_PASSWORD_DISPLAY
     881                  else:
     882                      dspval = ''
     883              elif field.type == 'blob':
     884                  continue
     885              else:
     886                  field_type = widget_class.match(str(field.type)).group()
     887                  field_type = field_type in self.widgets and field_type or 'string'
     888                  inp = self.widgets[field_type].widget(field, default)
     889
     890              xfields.append((row_id,label,inp,comment))
     891              self.custom.dspval[fieldname] = dspval or nbsp
     892              self.custom.inpval[fieldname] = inpval or ''
     893              self.custom.widget[fieldname] = inp
     894
     895          # if a record is provided and found, as is linkto
     896          # build a link
     897          if record and linkto:
     898              db = linkto.split('/')[-1]
     899              for (rtable, rfield) in table._referenced_by:
     900                  if keyed:
     901                      rfld = table._db[rtable][rfield]
     902                      query = urllib.quote('%s.%s==%s' % (db,rfld,record[rfld.type[10:].split('.')[1]]))
     903                  else:
     904                      query = urllib.quote('%s.%s==%s' % (db,table._db[rtable][rfield],record[self.id_field_name]))
     905                  lname = olname = '%s.%s' % (rtable, rfield)
     906                  if ofields and not olname in ofields:
     907                      continue
     908                  if labels and lname in labels:
     909                      lname = labels[lname]
     910                  widget = A(lname,
     911                             _class='reference',
     912                             _href='%s/%s?query=%s' % (linkto, rtable, query))
     913                  xfields.append((olname.replace('.', '__')+SQLFORM.ID_ROW_SUFFIX,
     914                                  '',widget,col3.get(olname,'')))
     915                  self.custom.linkto[olname.replace('.', '__')] = widget
     916  #                 </block>
     917
     918          # when deletable, add delete? checkbox
     919          self.custom.deletable = ''
     920          if record and deletable:
     921              widget = INPUT(_type='checkbox',
     922                              _class='delete',
     923                              _id=self.FIELDKEY_DELETE_RECORD,
     924                              _name=self.FIELDNAME_REQUEST_DELETE,
     925                              )
     926              xfields.append((self.FIELDKEY_DELETE_RECORD+SQLFORM.ID_ROW_SUFFIX,
     927                              LABEL(
     928                                  delete_label,separator,
     929                                  _for=self.FIELDKEY_DELETE_RECORD,
     930                                  _id=self.FIELDKEY_DELETE_RECORD+SQLFORM.ID_LABEL_SUFFIX),
     931                              widget,
     932                              col3.get(self.FIELDKEY_DELETE_RECORD, '')))
     933              self.custom.deletable = widget
     934
     935          # when writable, add submit button
     936          self.custom.submit = ''
     937          if not readonly:
     938              if 'submit' in buttons:
     939                  widget = self.custom.submit = INPUT(_type='submit',
     940                                 _value=submit_button)
     941              elif buttons:
     942                  widget = self.custom.submit = DIV(*buttons)
     943              if self.custom.submit:
     944                  xfields.append(('submit_record' + SQLFORM.ID_ROW_SUFFIX,
     945                                  '', widget, col3.get('submit_button', '')))
     946
     947          # if a record is provided and found
     948          # make sure it's id is stored in the form
     949          if record:
     950              if not self['hidden']:
     951                  self['hidden'] = {}
     952              if not keyed:
     953                  self['hidden']['id'] = record[table._id.name]
     954
     955          (begin, end) = self._xml()
     956          self.custom.begin = XML("<%s %s>" % (self.tag, begin))
     957          self.custom.end = XML("%s</%s>" % (end, self.tag))
     958          table = self.createform(xfields)
     959          self.components = [table]
     960
     961 -    def createform(self, xfields):
     962          if self.formstyle == 'table3cols':
     963              table = TABLE()
     964              for id,a,b,c in xfields:
     965                  td_b = self.field_parent[id] = TD(b,_class='w2p_fw')
     966                  table.append(TR(TD(a,_class='w2p_fl'),
     967                                  td_b,
     968                                  TD(c,_class='w2p_fc'),_id=id))
     969          elif self.formstyle == 'table2cols':
     970              table = TABLE()
     971              for id,a,b,c in xfields:
     972                  td_b = self.field_parent[id] = TD(b,_class='w2p_fw',_colspan="2")
     973                  table.append(TR(TD(a,_class='w2p_fl'),
     974                                  TD(c,_class='w2p_fc'),_id=id
     975                                  +'1',_class='even'))
     976                  table.append(TR(td_b,_id=id+'2',_class='odd'))
     977          elif self.formstyle == 'divs':
     978              table = TAG['']()
     979              for id,a,b,c in xfields:
     980                  div_b = self.field_parent[id] = DIV(b,_class='w2p_fw')
     981                  table.append(DIV(DIV(a,_class='w2p_fl'),
     982                                   div_b,
     983                                   DIV(c,_class='w2p_fc'),_id=id))
     984          elif self.formstyle == 'ul':
     985              table = UL()
     986              for id,a,b,c in xfields:
     987                  div_b = self.field_parent[id] = DIV(b,_class='w2p_fw')
     988                  table.append(LI(DIV(a,_class='w2p_fl'),
     989                                  div_b,
     990                                  DIV(c,_class='w2p_fc'),_id=id))
     991          elif callable(self.formstyle):
     992              table = TABLE()
     993              for id,a,b,c in xfields:
     994                  raw_b = self.field_parent[id] = b
     995                  newrows = self.formstyle(id,a,raw_b,c)
     996                  if type(newrows).__name__ != "tuple":
     997                      newrows = [newrows]
     998                  for newrow in newrows:
     999                      table.append(newrow)
    1000          else:
    1001              raise RuntimeError, 'formstyle not supported'
    1002          return table
    1003
    1004
    1005 -    def accepts(
    1006          self,
    1007          request_vars,
    1008          session=None,
    1009          formname='%(tablename)s/%(record_id)s',
    1010          keepvalues=False,
    1011          onvalidation=None,
    1012          dbio=True,
    1013          hideerror=False,
    1014          detect_record_change=False,
    1015          ):
    1016
    1017          """
    1018          similar FORM.accepts but also does insert, update or delete in DAL.
    1019          but if detect_record_change == True than:
    1020            form.record_changed = False (record is properly validated/submitted)
    1021            form.record_changed = True (record cannot be submitted because changed)
    1022          elseif detect_record_change == False than:
    1023            form.record_changed = None
    1024          """
    1025
    1026          if request_vars.__class__.__name__ == 'Request':
    1027              request_vars = request_vars.post_vars
    1028
    1029          keyed = hasattr(self.table, '_primarykey')
    1030
    1031          # implement logic to detect whether record exist but has been modified
    1032          # server side
    1033          self.record_changed = None
    1034          if detect_record_change:
    1035              if self.record:
    1036                  self.record_changed = False
    1037                  serialized = '|'.join(str(self.record[k]) for k in self.table.fields())
    1038                  self.record_hash = md5_hash(serialized)
    1039
    1040          # logic to deal with record_id for keyed tables
    1041          if self.record:
    1042              if keyed:
    1043                  formname_id = '.'.join(str(self.record[k])
    1044                                         for k in self.table._primarykey
    1045                                         if hasattr(self.record,k))
    1046                  record_id = dict((k, request_vars.get(k,None)) \
    1047                                       for k in self.table._primarykey)
    1048              else:
    1049                  (formname_id, record_id) = (self.record[self.id_field_name],
    1050                                              request_vars.get('id', None))
    1051              keepvalues = True
    1052          else:
    1053              if keyed:
    1054                  formname_id = 'create'
    1055                  record_id = dict([(k, None) for k in self.table._primarykey])
    1056              else:
    1057                  (formname_id, record_id) = ('create', None)
    1058
    1059          if not keyed and isinstance(record_id, (list, tuple)):
    1060              record_id = record_id[0]
    1061
    1062          if formname:
    1063              formname = formname % dict(tablename = self.table._tablename,
    1064                                         record_id = formname_id)
    1065
    1066          # ## THIS IS FOR UNIQUE RECORDS, read IS_NOT_IN_DB
    1067
    1068          for fieldname in self.fields:
    1069              field = self.table[fieldname]
    1070              requires = field.requires or []
    1071              if not isinstance(requires, (list, tuple)):
    1072                  requires = [requires]
    1073              [item.set_self_id(self.record_id) for item in requires
    1074              if hasattr(item, 'set_self_id') and self.record_id]
    1075
    1076          # ## END
    1077
    1078          fields = {}
    1079          for key in self.vars:
    1080              fields[key] = self.vars[key]
    1081
    1082          ret = FORM.accepts(
    1083              self,
    1084              request_vars,
    1085              session,
    1086              formname,
    1087              keepvalues,
    1088              onvalidation,
    1089              hideerror=hideerror,
    1090              )
    1091
    1092          if not ret and self.record and self.errors:
    1093              ### if there are errors in update mode
    1094              # and some errors refers to an already uploaded file
    1095              # delete error if
    1096              # - user not trying to upload a new file
    1097              # - there is existing file and user is not trying to delete it
    1098              # this is because removing the file may not pass validation
    1099              for key in self.errors.keys():
    1100                  if key in self.table \
    1101                          and self.table[key].type == 'upload' \
    1102                          and request_vars.get(key, None) in (None, '') \
    1103                          and self.record[key] \
    1104                          and not key + UploadWidget.ID_DELETE_SUFFIX in request_vars:
    1105                      del self.errors[key]
    1106              if not self.errors:
    1107                  ret = True
    1108
    1109          self.deleted = \
    1110              request_vars.get(self.FIELDNAME_REQUEST_DELETE, False)
    1111
    1112          self.custom.end = TAG[''](self.hidden_fields(), self.custom.end)
    1113
    1114          auch = record_id and self.errors and self.deleted
    1115
    1116          # auch is true when user tries to delete a record
    1117          # that does not pass validation, yet it should be deleted
    1118
    1119          if not ret and not auch:
    1120              for fieldname in self.fields:
    1121                  field = self.table[fieldname]
    1122                  ### this is a workaround! widgets should always have default not None!
    1123                  if not field.widget and field.type.startswith('list:') and \
    1124                          not OptionsWidget.has_options(field):
    1125                      field.widget = self.widgets.list.widget
    1126                  if hasattr(field, 'widget') and field.widget and fieldname in request_vars:
    1127                      if fieldname in self.vars:
    1128                          value = self.vars[fieldname]
    1129                      elif self.record:
    1130                          value = self.record[fieldname]
    1131                      else:
    1132                          value = self.table[fieldname].default
    1133                      if field.type.startswith('list:') and \
    1134                              isinstance(value, str):
    1135                          value = [value]
    1136                      row_id = '%s_%s%s' % (self.table, fieldname, SQLFORM.ID_ROW_SUFFIX)
    1137                      widget = field.widget(field, value)
    1138                      self.field_parent[row_id].components = [ widget ]
    1139                      if not field.type.startswith('list:'):
    1140                          self.field_parent[row_id]._traverse(False, hideerror)
    1141                      self.custom.widget[ fieldname ] = widget
    1142              self.accepted = ret
    1143              return ret
    1144
    1145          if record_id and str(record_id) != str(self.record_id):
    1146              raise SyntaxError, 'user is tampering with form\'s record_id: ' \
    1147                  '%s != %s' % (record_id, self.record_id)
    1148
    1149          if record_id and dbio and not keyed:
    1150              self.vars.id = self.record[self.id_field_name]
    1151
    1152          if self.deleted and self.custom.deletable:
    1153              if dbio:
    1154                  if keyed:
    1155                      qry = reduce(lambda x, y: x & y,
    1156                                   [self.table[k] == record_id[k] \
    1157                                        for k in self.table._primarykey])
    1158                  else:
    1159                      qry = self.table._id == self.record[self.id_field_name]
    1160                  self.table._db(qry).delete()
    1161              self.errors.clear()
    1162              for component in self.elements('input, select, textarea'):
    1163                  component['_disabled'] = True
    1164              self.accepted = True
    1165              return True
    1166
    1167          for fieldname in self.fields:
    1168              if not fieldname in self.table.fields:
    1169                  continue
    1170
    1171              if not self.ignore_rw and not self.table[fieldname].writable:
    1172                  ### this happens because FORM has no knowledge of writable
    1173                  ### and thinks that a missing boolean field is a None
    1174                  if self.table[fieldname].type == 'boolean' and \
    1175                      self.vars.get(fieldname, True) is None:
    1176                      del self.vars[fieldname]
    1177                  continue
    1178
    1179              field = self.table[fieldname]
    1180              if field.type == 'id':
    1181                  continue
    1182              if field.type == 'boolean':
    1183                  if self.vars.get(fieldname, False):
    1184                      self.vars[fieldname] = fields[fieldname] = True
    1185                  else:
    1186                      self.vars[fieldname] = fields[fieldname] = False
    1187              elif field.type == 'password' and self.record\
    1188                  and request_vars.get(fieldname, None) == \
    1189                      PasswordWidget.DEFAULT_PASSWORD_DISPLAY:
    1190                  continue  # do not update if password was not changed
    1191              elif field.type == 'upload':
    1192                  f = self.vars[fieldname]
    1193                  fd = '%s__delete' % fieldname
    1194                  if f == '' or f is None:
    1195                      if self.vars.get(fd, False) or not self.record:
    1196                          fields[fieldname] = ''
    1197                      else:
    1198                          fields[fieldname] = self.record[fieldname]
    1199                      self.vars[fieldname] = fields[fieldname]
    1200                      continue
    1201                  elif hasattr(f, 'file'):
    1202                      (source_file, original_filename) = (f.file, f.filename)
    1203                  elif isinstance(f, (str, unicode)):
    1204                      ### do not know why this happens, it should not
    1205                      (source_file, original_filename) = \
    1206                          (cStringIO.StringIO(f), 'file.txt')
    1207                  newfilename = field.store(source_file, original_filename, field.uploadfolder)
    1208                  # this line is for backward compatibility only
    1209                  self.vars['%s_newfilename' % fieldname] = newfilename
    1210                  fields[fieldname] = newfilename
    1211                  if isinstance(field.uploadfield, str):
    1212                      fields[field.uploadfield] = source_file.read()
    1213                  # proposed by Hamdy (accept?) do we need fields at this point?
    1214                  self.vars[fieldname] = fields[fieldname]
    1215                  continue
    1216              elif fieldname in self.vars:
    1217                  fields[fieldname] = self.vars[fieldname]
    1218              elif field.default is None and field.type != 'blob':
    1219                  self.errors[fieldname] = 'no data'
    1220                  self.accepted = False
    1221                  return False
    1222              value = fields.get(fieldname,None)
    1223              if field.type == 'list:string':
    1224                  if not isinstance(value, (tuple, list)):
    1225                      fields[fieldname] = value and [value] or []
    1226              elif isinstance(field.type,str) and field.type.startswith('list:'):
    1227                  if not isinstance(value, list):
    1228                      fields[fieldname] = [safe_int(x) for x in (value and [value] or [])]
    1229              elif field.type == 'integer':
    1230                  if not value is None:
    1231                      fields[fieldname] = safe_int(value)
    1232              elif field.type.startswith('reference'):
    1233                  if not value is None and isinstance(self.table, Table) and not keyed:
    1234                      fields[fieldname] = safe_int(value)
    1235              elif field.type == 'double':
    1236                  if not value is None:
    1237                      fields[fieldname] = safe_float(value)
    1238
    1239          for fieldname in self.vars:
    1240              if fieldname != 'id' and fieldname in self.table.fields\
    1241                   and not fieldname in fields and not fieldname\
    1242                   in request_vars:
    1243                  fields[fieldname] = self.vars[fieldname]
    1244
    1245          if dbio:
    1246              if 'delete_this_record' in fields:
    1247                  # this should never happen but seems to happen to some
    1248                  del fields['delete_this_record']
    1249              for field in self.table:
    1250                  if not field.name in fields and field.writable==False \
    1251                          and field.update is None:
    1252                      if record_id and self.record:
    1253                          fields[field.name] = self.record[field.name]
    1254                      elif not self.table[field.name].default is None:
    1255                          fields[field.name] = self.table[field.name].default
    1256              if keyed:
    1257                  if reduce(lambda x, y: x and y, record_id.values()): # if record_id
    1258                      if fields:
    1259                          qry = reduce(lambda x, y: x & y,
    1260                              [self.table[k] == self.record[k] for k in self.table._primarykey])
    1261                          self.table._db(qry).update(**fields)
    1262                  else:
    1263                      pk = self.table.insert(**fields)
    1264                      if pk:
    1265                          self.vars.update(pk)
    1266                      else:
    1267                          ret = False
    1268              else:
    1269                  if record_id:
    1270                      self.vars.id = self.record[self.id_field_name]
    1271                      if fields:
    1272                          self.table._db(self.table._id == self.record[self.id_field_name]).update(**fields)
    1273                  else:
    1274                      self.vars.id = self.table.insert(**fields)
    1275          self.accepted = ret
    1276          return ret
    1277
    1278      @staticmethod
    1279 -    def factory(*fields, **attributes):
    1280          """
    1281          generates a SQLFORM for the given fields.
    1282
    1283          Internally will build a non-database based data model
    1284          to hold the fields.
    1285          """
    1286          # Define a table name, this way it can be logical to our CSS.
    1287          # And if you switch from using SQLFORM to SQLFORM.factory
    1288          # your same css definitions will still apply.
    1289
    1290          table_name = attributes.get('table_name', 'no_table')
    1291
    1292          # So it won't interfear with SQLDB.define_table
    1293          if 'table_name' in attributes:
    1294              del attributes['table_name']
    1295
    1296          return SQLFORM(DAL(None).define_table(table_name, *fields),
    1297                         **attributes)
    1298
    1299      @staticmethod
    1300 -    def build_query(fields,keywords):
    1301          from gluon import current
    1302          request = current.request
    1303          if isinstance(keywords,(tuple,list)):
    1304             keywords = keywords[0]
    1305             request.vars.keywords = keywords
    1306          key = keywords.strip()
    1307          if key and not ' ' in key and not '"' in key and not "'" in key:
    1308              SEARCHABLE_TYPES = ('string','text','list:string')
    1309              parts = [field.contains(key) for field in fields if field.type in SEARCHABLE_TYPES]
    1310          else:
    1311              parts = None
    1312          if parts:
    1313              return reduce(lambda a,b: a|b,parts)
    1314          else:
    1315              return smart_query(fields,key)
    1316
    1317      @staticmethod
    1387
    1388
    1389      @staticmethod
    1390 -    def grid(query,
    1391               fields=None,
    1392               field_id=None,
    1393               left=None,
    1394               headers={},
    1395               orderby=None,
    1396               searchable=True,
    1397               sortable=True,
    1398               paginate=20,
    1399               deletable=True,
    1400               editable=True,
    1401               details=True,
    1402               selectable=None,
    1403               create=True,
    1404               csv=True,
    1405               links=None,
    1406               links_in_grid=True,
    1407               upload = '<default>',
    1408               args=[],
    1409               user_signature = True,
    1410               maxtextlengths={},
    1411               maxtextlength=20,
    1412               onvalidation=None,
    1413               oncreate=None,
    1414               onupdate=None,
    1415               ondelete=None,
    1416               sorter_icons=(XML('&#x2191;'),XML('&#x2193;')),
    1417               ui = 'web2py',
    1418               showbuttontext=True,
    1419               _class="web2py_grid",
    1420               formname='web2py_grid',
    1421               search_widget='default',
    1422               ignore_rw = False,
    1423               formstyle = 'table3cols',
    1424              ):
    1425
    1426          # jQuery UI ThemeRoller classes (empty if ui is disabled)
    1427          if ui == 'jquery-ui':
    1428              ui = dict(widget='ui-widget',
    1429                        header='ui-widget-header',
    1430                        content='ui-widget-content',
    1431                        default='ui-state-default',
    1432                        cornerall='ui-corner-all',
    1433                        cornertop='ui-corner-top',
    1434                        cornerbottom='ui-corner-bottom',
    1435                        button='ui-button-text-icon-primary',
    1436                        buttontext='ui-button-text',
    1437                        buttonadd='ui-icon ui-icon-plusthick',
    1438                        buttonback='ui-icon ui-icon-arrowreturnthick-1-w',
    1439                        buttonexport='ui-icon ui-icon-transferthick-e-w',
    1440                        buttondelete='ui-icon ui-icon-trash',
    1441                        buttonedit='ui-icon ui-icon-pencil',
    1442                        buttontable='ui-icon ui-icon-triangle-1-e',
    1443                        buttonview='ui-icon ui-icon-zoomin',
    1444                        )
    1445          elif ui == 'web2py':
    1446              ui = dict(widget='',
    1447                        header='',
    1448                        content='',
    1449                        default='',
    1450                        cornerall='',
    1451                        cornertop='',
    1452                        cornerbottom='',
    1453                        button='button',
    1454                        buttontext='buttontext button',
    1455                        buttonadd='icon plus',
    1456                        buttonback='icon leftarrow',
    1457                        buttonexport='icon downarrow',
    1458                        buttondelete='icon trash',
    1459                        buttonedit='icon pen',
    1460                        buttontable='icon rightarrow',
    1461                        buttonview='icon magnifier',
    1462                        )
    1463          elif not isinstance(ui,dict):
    1464              raise RuntimeError,'SQLFORM.grid ui argument must be a dictionary'
    1465
    1466          from gluon import current, redirect
    1467          db = query._db
    1468          T = current.T
    1469          request = current.request
    1470          session = current.session
    1471          response = current.response
    1472          wenabled = (not user_signature or (session.auth and session.auth.user))
    1473          create = wenabled and create
    1474          editable = wenabled and editable
    1475          deletable = wenabled and deletable
    1476
    1477          def url(**b):
    1478              b['args'] = args+b.get('args',[])
    1479              b['user_signature'] = user_signature
    1480              return URL(**b)
    1481
    1482          def gridbutton(buttonclass='buttonadd',buttontext='Add',
    1483                         buttonurl=url(args=[]),callback=None,delete=None,trap=True):
    1484              if showbuttontext:
    1485                  if callback:
    1486                      return A(SPAN(_class=ui.get(buttonclass,'')),
    1487                               SPAN(T(buttontext),_title=buttontext,
    1488                                    _class=ui.get('buttontext','')),
    1489                               callback=callback,delete=delete,
    1490                               _class=trap_class(ui.get('button',''),trap))
    1491                  else:
    1492                      return A(SPAN(_class=ui.get(buttonclass,'')),
    1493                               SPAN(T(buttontext),_title=buttontext,
    1494                                    _class=ui.get('buttontext','')),
    1495                               _href=buttonurl,
    1496                               _class=trap_class(ui.get('button',''),trap))
    1497              else:
    1498                  if callback:
    1499                      return A(SPAN(_class=ui.get(buttonclass,'')),
    1500                               callback=callback,delete=delete,
    1501                               _title=buttontext,
    1502                               _class=trap_class(ui.get('buttontext',''),trap))
    1503                  else:
    1504                      return A(SPAN(_class=ui.get(buttonclass,'')),
    1505                               _href=buttonurl,_title=buttontext,
    1506                               _class=trap_class(ui.get('buttontext',''),trap))
    1507          dbset = db(query)
    1508          tablenames = db._adapter.tables(dbset.query)
    1509          if left!=None: tablenames+=db._adapter.tables(left)
    1510          tables = [db[tablename] for tablename in tablenames]
    1511          if not fields:
    1512              fields = reduce(lambda a,b:a+b,
    1513                              [[field for field in table] for table in tables])
    1514          if not field_id:
    1515              field_id = tables[0]._id
    1516          columns = [str(field) for field in fields \
    1517                         if field._tablename in tablenames]
    1518          if not str(field_id) in [str(f) for f in fields]:
    1519              fields.append(field_id)
    1520          table = field_id.table
    1521          tablename = table._tablename
    1522          referrer = session.get('_web2py_grid_referrer_'+formname, url())
    1523          def check_authorization():
    1524              if user_signature:
    1525                  if not URL.verify(request,user_signature=user_signature):
    1526                      session.flash = T('not authorized')
    1527                      redirect(referrer)
    1528          if upload=='<default>':
    1529              upload = lambda filename: url(args=['download',filename])
    1530              if len(request.args)>1 and request.args[-2]=='download':
    1531                  check_authorization()
    1532                  stream = response.download(request,db)
    1533                  raise HTTP(200,stream,**response.headers)
    1534
    1535          def buttons(edit=False,view=False,record=None):
    1536              buttons = DIV(gridbutton('buttonback', 'Back', referrer),
    1537                            _class='form_header row_buttons %(header)s %(cornertop)s' % ui)
    1538              if edit:
    1539                  args = ['edit',table._tablename,request.args[-1]]
    1540                  buttons.append(gridbutton('buttonedit', 'Edit',
    1541                                            url(args=args)))
    1542              if view:
    1543                  args = ['view',table._tablename,request.args[-1]]
    1544                  buttons.append(gridbutton('buttonview', 'View',
    1545                                            url(args=args)))
    1546              if record and links:
    1547                  for link in links:
    1548                      if isinstance(link,dict):
    1549                           buttons.append(link['body'](record))
    1550                      elif link(record):
    1551                           buttons.append(link(record))
    1552              return buttons
    1553
    1554          formfooter = DIV(
    1555              _class='form_footer row_buttons %(header)s %(cornerbottom)s' % ui)
    1556
    1557          create_form = edit_form = None
    1558
    1559          if create and len(request.args)>1 and request.args[-2]=='new':
    1560              check_authorization()
    1561              table = db[request.args[-1]]
    1562              create_form = SQLFORM(
    1563                  table, ignore_rw = ignore_rw, formstyle = formstyle,
    1564                  _class='web2py_form').process(
    1565                  next=referrer,
    1566                  onvalidation=onvalidation,
    1567                  onsuccess=oncreate,
    1568                  formname=formname)
    1569              res = DIV(buttons(),create_form,formfooter,_class=_class)
    1570              res.create_form = create_form
    1571              res.edit_form = None
    1572              res.update_form = None
    1573              return res
    1574          elif details and len(request.args)>2 and request.args[-3]=='view':
    1575              check_authorization()
    1576              table = db[request.args[-2]]
    1577              record = table(request.args[-1]) or redirect(URL('error'))
    1578              form = SQLFORM(table,record,upload=upload,ignore_rw=ignore_rw,
    1579                             formstyle=formstyle, readonly=True,_class='web2py_form')
    1580              res = DIV(buttons(edit=editable,record=record),form,
    1581                        formfooter,_class=_class)
    1582              res.create_form = None
    1583              res.edit_form = None
    1584              res.update_form = None
    1585              return res
    1586          elif editable and len(request.args)>2 and request.args[-3]=='edit':
    1587              check_authorization()
    1588              table = db[request.args[-2]]
    1589              record = table(request.args[-1]) or redirect(URL('error'))
    1590              edit_form = SQLFORM(table,record,upload=upload,ignore_rw=ignore_rw,
    1591                                  formstyle=formstyle,deletable=deletable,
    1592                                  _class='web2py_form',
    1593                                  submit_button = T('Submit'),
    1594                                  delete_label = T('Check to delete'))
    1595              edit_form.process(formname=formname,
    1596                                onvalidation=onvalidation,
    1597                                onsuccess=onupdate,
    1598                                next=referrer)
    1599              res = DIV(buttons(view=details,record=record),
    1600                        edit_form,formfooter,_class=_class)
    1601              res.create_form = None
    1602              res.edit_form = edit_form
    1603              res.update_form = None
    1604              return res
    1605          elif deletable and len(request.args)>2 and request.args[-3]=='delete':
    1606              check_authorization()
    1607              table = db[request.args[-2]]
    1608              if ondelete:
    1609                  ondelete(table,request.args[-1])
    1610              ret = db(table[table._id.name]==request.args[-1]).delete()
    1611              return ret
    1612          elif csv and len(request.args)>0 and request.args[-1]=='csv':
    1613              if request.vars.keywords:
    1614                  try:
    1615                      dbset=dbset(SQLFORM.build_query(
    1616                              fields,
    1617                              request.vars.get('keywords','')))
    1618                  except:
    1619                      raise HTTP(400)
    1620              check_authorization()
    1621              response.headers['Content-Type'] = 'text/csv'
    1622              response.headers['Content-Disposition'] = \
    1623                  'attachment;filename=rows.csv;'
    1624              raise HTTP(200,str(dbset.select()),
    1625                         **{'Content-Type':'text/csv',
    1626                            'Content-Disposition':'attachment;filename=rows.csv;'})
    1627          elif request.vars.records and not isinstance(
    1628              request.vars.records,list):
    1629              request.vars.records=[request.vars.records]
    1630          elif not request.vars.records:
    1631              request.vars.records=[]
    1632
    1633          session['_web2py_grid_referrer_'+formname] = \
    1634              URL(args=request.args,vars=request.vars,
    1635                  user_signature=user_signature)
    1636          console = DIV(_class='web2py_console %(header)s %(cornertop)s' % ui)
    1637          error = None
    1638          search_form = None
    1639          if searchable:
    1640              if search_widget=='default':
    1641                  search_widget = lambda sfield, url: FORM(
    1642                      SQLFORM.search_menu(sfields),
    1643                      INPUT(_name='keywords',_value=request.vars.keywords,
    1644                            _id='web2py_keywords'),
    1645                      INPUT(_type='submit',_value=T('Search')),
    1646                      INPUT(_type='submit',_value=T('Clear'),
    1647                            _onclick="jQuery('#web2py_keywords').val('');"),
    1648                      _method="GET",_action=url)
    1649              sfields = reduce(lambda a,b:a+b,
    1650                               [[f for f in t if f.readable] for t in tables])
    1651              form = search_widget and search_widget(sfields,url()) or ''
    1652              search_form = form
    1653              console.append(form)
    1654              keywords = request.vars.get('keywords','')
    1655              try:
    1656                  if callable(searchable):
    1657                      subquery = searchable(sfields, keywords)
    1658                  else:
    1659                      subquery = SQLFORM.build_query(sfields, keywords)
    1660              except RuntimeError:
    1661                  subquery = None
    1662                  error = T('Invalid query')
    1663          else:
    1664              subquery = None
    1665          if subquery:
    1666              dbset = dbset(subquery)
    1667          try:
    1668              if left:
    1669                  nrows = dbset.select('count(*)',left=left).first()['count(*)']
    1670              else:
    1671                  nrows = dbset.count()
    1672          except:
    1673              nrows = 0
    1674              error = T('Unsupported query')
    1675
    1676          search_actions = DIV(_class='web2py_search_actions')
    1677          if create:
    1678              search_actions.append(gridbutton(
    1679                      buttonclass='buttonadd',
    1680                      buttontext=T('Add'),
    1681                      buttonurl=url(args=['new',tablename])))
    1682          if csv:
    1683              search_actions.append(gridbutton(
    1684                      buttonclass='buttonexport',
    1685                      buttontext=T('Export'),
    1686                      trap = False,
    1687                      buttonurl=url(args=['csv'],
    1688                                    vars=dict(keywords=request.vars.keywords or ''))))
    1689
    1690          console.append(search_actions)
    1691
    1692          order = request.vars.order or ''
    1693          if sortable:
    1694              if order and not order=='None':
    1695                  tablename,fieldname = order.split('~')[-1].split('.',1)
    1696                  sort_field = db[tablename][fieldname]
    1697                  exception = sort_field.type in ('date','datetime','time')
    1698                  if exception:
    1699                      orderby = (order[:1]=='~' and sort_field) or ~sort_field
    1700                  else:
    1701                      orderby = (order[:1]=='~' and ~sort_field) or sort_field
    1702
    1703          head = TR(_class=ui.get('header',''))
    1704          if selectable:
    1705              head.append(TH(_class=ui.get('default','')))
    1706          for field in fields:
    1707              if columns and not str(field) in columns: continue
    1708              if not field.readable: continue
    1709              key = str(field)
    1710              header = headers.get(str(field),
    1711                                   hasattr(field,'label') and field.label or key)
    1712              if sortable:
    1713                  if key == order:
    1714                      key, marker = '~'+order, sorter_icons[0]
    1715                  elif key == order[1:]:
    1716                      marker = sorter_icons[1]
    1717                  else:
    1718                      marker = ''
    1719                  header = A(header,marker,_href=url(vars=dict(
    1720                              keywords=request.vars.keywords or '',
    1721                              order=key)),_class=trap_class())
    1722              head.append(TH(header, _class=ui.get('default','')))
    1723
    1724          if links and links_in_grid:
    1725              for link in links:
    1726                  if isinstance(link,dict):
    1727                      head.append(TH(link['header'], _class=ui.get('default','')))
    1728
    1729          # Include extra column for buttons if needed.
    1730          include_buttons_column = (details or editable or deletable or
    1731              (links and links_in_grid and
    1732               not all([isinstance(link, dict) for link in links])))
    1733          if include_buttons_column:
    1734              head.append(TH(_class=ui.get('default','')))
    1735
    1736          paginator = UL()
    1737          if paginate and paginate<nrows:
    1738              npages,reminder = divmod(nrows,paginate)
    1739              if reminder: npages+=1
    1740              try: page = int(request.vars.page or 1)-1
    1741              except ValueError: page = 0
    1742              limitby = (paginate*page,paginate*(page+1))
    1743              def self_link(name,p):
    1744                  d = dict(page=p+1)
    1745                  if order: d['order']=order
    1746                  if request.vars.keywords: d['keywords']=request.vars.keywords
    1747                  return A(name,_href=url(vars=d),_class=trap_class())
    1748              NPAGES = 5 # window is 2*NPAGES
    1749              if page>NPAGES+1:
    1750                  paginator.append(LI(self_link('<<',0)))
    1751              if page>NPAGES:
    1752                  paginator.append(LI(self_link('<',page-1)))
    1753              pages = range(max(0,page-NPAGES),min(page+NPAGES,npages))
    1754              for p in pages:
    1755                  if p == page:
    1756                      paginator.append(LI(A(p+1,_onclick='return false'),
    1757                                          _class=trap_class('current')))
    1758                  else:
    1759                      paginator.append(LI(self_link(p+1,p)))
    1760              if page<npages-NPAGES:
    1761                  paginator.append(LI(self_link('>',page+1)))
    1762              if page<npages-NPAGES-1:
    1763                  paginator.append(LI(self_link('>>',npages-1)))
    1764          else:
    1765              limitby = None
    1766
    1767          try:
    1768              table_fields = [f for f in fields if f._tablename in tablenames]
    1769              rows = dbset.select(left=left,orderby=orderby,limitby=limitby,*table_fields)
    1770          except SyntaxError:
    1771              rows = None
    1772              error = T("Query Not Supported")
    1773          message = error or T('%(nrows)s records found') % dict(nrows=nrows)
    1774          console.append(DIV(message,_class='web2py_counter'))
    1775
    1776          if rows:
    1777              htmltable = TABLE(THEAD(head))
    1778              tbody = TBODY()
    1779              numrec=0
    1780              for row in rows:
    1781                  if numrec % 2 == 0:
    1782                      classtr = 'even'
    1783                  else:
    1784                      classtr = 'odd'
    1785                  numrec+=1
    1786                  id = row[field_id]
    1787                  if id:
    1788                      rid = id
    1789                      if callable(rid): ### can this ever be callable?
    1790                          rid = rid(row)
    1791                      tr = TR(_id=rid, _class='%s %s' % (classtr, 'with_id'))
    1792                  else:
    1793                      tr = TR(_class=classtr)
    1794                  if selectable:
    1795                      tr.append(INPUT(_type="checkbox",_name="records",_value=id,
    1796                                      value=request.vars.records))
    1797                  for field in fields:
    1798                      if not str(field) in columns: continue
    1799                      if not field.readable: continue
    1800                      if field.type=='blob': continue
    1801                      value = row[field]
    1802                      maxlength = maxtextlengths.get(str(field),maxtextlength)
    1803                      if field.represent:
    1804                          try:
    1805                              value=field.represent(value,row)
    1806                          except KeyError:
    1807                              try:
    1808                                  value=field.represent(value,row[field._tablename])
    1809                              except KeyError:
    1810                                  pass
    1811                      elif field.type=='boolean':
    1812                          value = INPUT(_type="checkbox",_checked = value,
    1813                                        _disabled=True)
    1814                      elif field.type=='upload':
    1815                          if value:
    1816                              if callable(upload):
    1817                                  value = A('File', _href=upload(value))
    1818                              elif upload:
    1819                                  value = A('File',
    1820                                            _href='%s/%s' % (upload, value))
    1821                          else:
    1822                              value = ''
    1823                      elif isinstance(value,str):
    1824                          value = truncate_string(value,maxlength)
    1825                      else:
    1826                          value = field.formatter(value)
    1827                      tr.append(TD(value))
    1828                  row_buttons = TD(_class='row_buttons')
    1829                  if links and links_in_grid:
    1830                      for link in links:
    1831                          if isinstance(link, dict):
    1832                              tr.append(TD(link['body'](row)))
    1833                          else:
    1834                              if link(row):
    1835                                  row_buttons.append(link(row))
    1836                  if include_buttons_column:
    1837                      if details and (not callable(details) or details(row)):
    1838                          row_buttons.append(gridbutton(
    1839                                  'buttonview', 'View',
    1840                                  url(args=['view',tablename,id])))
    1841                      if editable and (not callable(editable) or editable(row)):
    1842                          row_buttons.append(gridbutton(
    1843                                  'buttonedit', 'Edit',
    1844                                  url(args=['edit',tablename,id])))
    1845                      if deletable and (not callable(deletable) or deletable(row)):
    1846                          row_buttons.append(gridbutton(
    1847                                  'buttondelete', 'Delete',
    1848                                  callback=url(args=['delete',tablename,id]),
    1849                                  delete='tr'))
    1850                      tr.append(row_buttons)
    1851                  tbody.append(tr)
    1852              htmltable.append(tbody)
    1853              htmltable = DIV(htmltable,_style='width:100%;overflow-x:auto')
    1854              if selectable:
    1855                  htmltable = FORM(htmltable,INPUT(_type="submit"))
    1856                  if htmltable.process(formname=formname).accepted:#
    1857                      htmltable.vars.records = htmltable.vars.records or []
    1858                      htmltable.vars.records = htmltable.vars.records if type(htmltable.vars.records) == list else [htmltable.vars.records]
    1859                      records = [int(r) for r in htmltable.vars.records]
    1860                      selectable(records)
    1861                      redirect(referrer)
    1862          else:
    1863              htmltable = DIV(T('No records found'))
    1864          res = DIV(console,
    1865                    DIV(htmltable,_class="web2py_table"),
    1866                    DIV(paginator,_class=\
    1867                            "web2py_paginator %(header)s %(cornerbottom)s" % ui),
    1868                    _class='%s %s' % (_class, ui.get('widget','')))
    1869          res.create_form = create_form
    1870          res.edit_form = edit_form
    1871          res.search_form = search_form
    1872          return res
    1873
    1874      @staticmethod
    1875 -    def smartgrid(table, constraints=None, linked_tables=None,
    1876                    links=None, links_in_grid=True,
    1877                    args=None, user_signature=True,
    1878                    **kwargs):
    1879          """
    1880          @auth.requires_login()
    1881          def index():
    1882              db.define_table('person',Field('name'),format='%(name)s')
    1883              db.define_table('dog',
    1884                  Field('name'),Field('owner',db.person),format='%(name)s')
    1885              db.define_table('comment',Field('body'),Field('dog',db.dog))
    1886              if db(db.person).isempty():
    1887                  from gluon.contrib.populate import populate
    1888                  populate(db.person,300)
    1889                  populate(db.dog,300)
    1890                  populate(db.comment,1000)
    1891                  db.commit()
    1892          form=SQLFORM.smartgrid(db[request.args(0) or 'person']) #***
    1893          return dict(form=form)
    1894
    1895          *** builds a complete interface to navigate all tables links
    1896              to the request.args(0)
    1897              table: pagination, search, view, edit, delete,
    1898                     children, parent, etc.
    1899
    1900          constraints is a dict {'table',query} that limits which
    1901          records can be accessible
    1902          links is a dict like
    1903             {'tablename':[lambda row: A(....), ...]}
    1904          that will add buttons when table tablename is displayed
    1905          linked_tables is a optional list of tablenames of tables
    1906          to be linked
    1907          """
    1908          from gluon import current, A, URL, DIV, H3, redirect
    1909          request, T = current.request, current.T
    1910          if args is None: args = []
    1911          db = table._db
    1912          breadcrumbs = []
    1913          if request.args(len(args)) != table._tablename:
    1914              request.args[:]=args+[table._tablename]
    1915          if links is None: links = {}
    1916          if constraints is None: constraints = {}
    1917          try:
    1918              nargs = len(args)+1
    1919              previous_tablename,previous_fieldname,previous_id = \
    1920                  table._tablename,None,None
    1921              while len(request.args)>nargs:
    1922                  key = request.args(nargs)
    1923                  if '.' in key:
    1924                      id = request.args(nargs+1)
    1925                      tablename,fieldname = key.split('.',1)
    1926                      table = db[tablename]
    1927                      field = table[fieldname]
    1928                      field.default = id
    1929                      referee = field.type[10:]
    1930                      if referee!=previous_tablename:
    1931                          raise HTTP(400)
    1932                      cond = constraints.get(referee,None)
    1933                      if cond:
    1934                          record = db(db[referee].id==id)(cond).select().first()
    1935                      else:
    1936                          record = db[referee](id)
    1937                      if previous_id:
    1938                          if record[previous_fieldname] != int(previous_id):
    1939                              raise HTTP(400)
    1940                      previous_tablename,previous_fieldname,previous_id = \
    1941                          tablename,fieldname,id
    1942                      try:
    1943                          format = db[referee]._format
    1944                          if callable(format): name = format(record)
    1945                          else: name = format % record
    1946                      except TypeError:
    1947                          name = id
    1948                      breadcrumbs += [A(T(db[referee]._plural),
    1949                                        _class=trap_class(),
    1950                                        _href=URL(args=request.args[:nargs])),
    1951                                      ' > ',
    1952                                      A(name,_class=trap_class(),
    1953                                        _href=URL(args=request.args[:nargs]+[
    1954                                      'view',referee,id],user_signature=True)),
    1955                                      ' > ']
    1956                      nargs+=2
    1957                  else:
    1958                      break
    1959              if nargs>len(args)+1:
    1960                  query = (field == id)
    1961                  if linked_tables is None or referee in linked_tables:
    1962                      field.represent = lambda id,r=None,referee=referee,rep=field.represent: A(callable(rep) and rep(id) or id,_class=trap_class(),_href=URL(args=request.args[:nargs]+['view',referee,id], user_signature=user_signature))
    1963          except (KeyError,ValueError,TypeError):
    1964              redirect(URL(args=table._tablename))
    1965          if nargs==len(args)+1:
    1966              query = table.id>0
    1967
    1968          # filter out data info for displayed table
    1969          if table._tablename in constraints:
    1970              query = query & constraints[table._tablename]
    1971          if isinstance(links,dict):
    1972              links = links.get(table._tablename,[])
    1973          for key in 'columns,orderby,searchable,sortable,paginate,deletable,editable,details,selectable,create'.split(','):
    1974              if isinstance(kwargs.get(key,None),dict):
    1975                  if table._tablename in kwargs[key]:
    1976                      kwargs[key] = kwargs[key][table._tablename]
    1977                  else:
    1978                      del kwargs[key]
    1979          for tablename,fieldname in table._referenced_by:
    1980              id_field_name = table._id.name
    1981              if linked_tables is None or tablename in linked_tables:
    1982                  args0 = tablename+'.'+fieldname
    1983                  links.append(
    1984                      lambda row,t=T(db[tablename]._plural),nargs=nargs,args0=args0:\
    1985                          A(SPAN(t),_class=trap_class(),_href=URL(
    1986                              args=request.args[:nargs]+[args0,row[id_field_name]])))
    1987          grid=SQLFORM.grid(query,args=request.args[:nargs],links=links,
    1988                            links_in_grid=links_in_grid,
    1989                            user_signature=user_signature,**kwargs)
    1990          if isinstance(grid,DIV):
    1991              breadcrumbs.append(A(T(table._plural),_class=trap_class(),
    1992                                   _href=URL(args=request.args[:nargs])))
    1993              grid.insert(0,DIV(H3(*breadcrumbs),_class='web2py_breadcrumbs'))
    1994          return grid
    1995
    1996
    1997 -class SQLTABLE(TABLE):
    1998
    1999      """
    2000      given a Rows object, as returned by a db().select(), generates
    2001      an html table with the rows.
    2002
    2003      optional arguments:
    2004
    2005      :param linkto: URL (or lambda to generate a URL) to edit individual records
    2006      :param upload: URL to download uploaded files
    2007      :param orderby: Add an orderby link to column headers.
    2008      :param headers: dictionary of headers to headers redefinions
    2009                      headers can also be a string to gerenare the headers from data
    2010                      for now only headers="fieldname:capitalize",
    2011                      headers="labels" and headers=None are supported
    2012      :param truncate: length at which to truncate text in table cells.
    2013          Defaults to 16 characters.
    2014      :param columns: a list or dict contaning the names of the columns to be shown
    2015          Defaults to all
    2016
    2017      Optional names attributes for passed to the <table> tag
    2018
    2019      The keys of headers and columns must be of the form "tablename.fieldname"
    2020
    2021      Simple linkto example::
    2022
    2023          rows = db.select(db.sometable.ALL)
    2024          table = SQLTABLE(rows, linkto='someurl')
    2025
    2026      This will link rows[id] to .../sometable/value_of_id
    2027
    2028
    2029      More advanced linkto example::
    2030
    2031          def mylink(field, type, ref):
    2032              return URL(args=[field])
    2033
    2034          rows = db.select(db.sometable.ALL)
    2035          table = SQLTABLE(rows, linkto=mylink)
    2036
    2037      This will link rows[id] to
    2038          current_app/current_controlle/current_function/value_of_id
    2039
    2040      New Implements: 24 June 2011:
    2041      -----------------------------
    2042
    2043      :param selectid: The id you want to select
    2044      :param renderstyle: Boolean render the style with the table
    2045
    2046      :param extracolums = [{'label':A('Extra',_href='#'),
    2047                      'class': '', #class name of the header
    2048                      'width':'', #width in pixels or %
    2049                      'content':lambda row, rc: A('Edit',_href='edit/%s'%row.id),
    2050                      'selected': False #agregate class selected to this column
    2051                      }]
    2052
    2053
    2054      :param headers = {'table.id':{'label':'Id',
    2055                             'class':'', #class name of the header
    2056                             'width':'', #width in pixels or %
    2057                             'truncate': 16, #truncate the content to...
    2058                             'selected': False #agregate class selected to this column
    2059                             },
    2060                 'table.myfield':{'label':'My field',
    2061                                  'class':'', #class name of the header
    2062                                  'width':'', #width in pixels or %
    2063                                  'truncate': 16, #truncate the content to...
    2064                                  'selected': False #agregate class selected to this column
    2065                                  },
    2066                 }
    2067
    2068      table = SQLTABLE(rows, headers=headers, extracolums=extracolums)
    2069
    2070
    2071      """
    2072
    2073 -    def __init__(
    2074          self,
    2075          sqlrows,
    2076          linkto=None,
    2077          upload=None,
    2078          orderby=None,
    2079          headers={},
    2080          truncate=16,
    2081          columns=None,
    2082          th_link='',
    2083          extracolumns=None,
    2084          selectid=None,
    2085          renderstyle=False,
    2086          **attributes
    2087          ):
    2088
    2089          TABLE.__init__(self, **attributes)
    2090
    2091          self.components = []
    2092          self.attributes = attributes
    2093          self.sqlrows = sqlrows
    2094          (components, row) = (self.components, [])
    2095          if not sqlrows:
    2096              return
    2097          if not columns:
    2098              columns = sqlrows.colnames
    2099          if headers=='fieldname:capitalize':
    2100              headers = {}
    2101              for c in columns:
    2102                  headers[c] = c.split('.')[-1].replace('_',' ').title()
    2103          elif headers=='labels':
    2104              headers = {}
    2105              for c in columns:
    2106                  (t,f) = c.split('.')
    2107                  field = sqlrows.db[t][f]
    2108                  headers[c] = field.label
    2109          if headers is None:
    2110              headers = {}
    2111          else:
    2112              for c in columns:#new implement dict
    2113                  if isinstance(headers.get(c, c), dict):
    2114                      coldict = headers.get(c, c)
    2115                      attrcol = dict()
    2116                      if coldict['width']!="":
    2117                          attrcol.update(_width=coldict['width'])
    2118                      if coldict['class']!="":
    2119                          attrcol.update(_class=coldict['class'])
    2120                      row.append(TH(coldict['label'],**attrcol))
    2121                  elif orderby:
    2122                      row.append(TH(A(headers.get(c, c),
    2123                                      _href=th_link+'?orderby=' + c)))
    2124                  else:
    2125                      row.append(TH(headers.get(c, c)))
    2126
    2127              if extracolumns:#new implement dict
    2128                  for c in extracolumns:
    2129                      attrcol = dict()
    2130                      if c['width']!="":
    2131                          attrcol.update(_width=c['width'])
    2132                      if c['class']!="":
    2133                          attrcol.update(_class=c['class'])
    2134                      row.append(TH(c['label'],**attrcol))
    2135
    2136              components.append(THEAD(TR(*row)))
    2137
    2138
    2139          tbody = []
    2140          for (rc, record) in enumerate(sqlrows):
    2141              row = []
    2142              if rc % 2 == 0:
    2143                  _class = 'even'
    2144              else:
    2145                  _class = 'odd'
    2146
    2147              if not selectid is None: #new implement
    2148                  if record[self.id_field_name]==selectid:
    2149                      _class += ' rowselected'
    2150
    2151              for colname in columns:
    2152                  if not table_field.match(colname):
    2153                      if "_extra" in record and colname in record._extra:
    2154                          r = record._extra[colname]
    2155                          row.append(TD(r))
    2156                          continue
    2157                      else:
    2158                          raise KeyError("Column %s not found (SQLTABLE)" % colname)
    2159                  (tablename, fieldname) = colname.split('.')
    2160                  try:
    2161                      field = sqlrows.db[tablename][fieldname]
    2162                  except KeyError:
    2163                      field = None
    2164                  if tablename in record \
    2165                          and isinstance(record,Row) \
    2166                          and isinstance(record[tablename],Row):
    2167                      r = record[tablename][fieldname]
    2168                  elif fieldname in record:
    2169                      r = record[fieldname]
    2170                  else:
    2171                      raise SyntaxError, 'something wrong in Rows object'
    2172                  r_old = r
    2173                  if not field:
    2174                      pass
    2175                  elif linkto and field.type == 'id':
    2176                      try:
    2177                          href = linkto(r, 'table', tablename)
    2178                      except TypeError:
    2179                          href = '%s/%s/%s' % (linkto, tablename, r_old)
    2180                      r = A(r, _href=href)
    2181                  elif field.type.startswith('reference'):
    2182                      if linkto:
    2183                          ref = field.type[10:]
    2184                          try:
    2185                              href = linkto(r, 'reference', ref)
    2186                          except TypeError:
    2187                              href = '%s/%s/%s' % (linkto, ref, r_old)
    2188                              if ref.find('.') >= 0:
    2189                                  tref,fref = ref.split('.')
    2190                                  if hasattr(sqlrows.db[tref],'_primarykey'):
    2191                                      href = '%s/%s?%s' % (linkto, tref, urllib.urlencode({fref:r}))
    2192                          r = A(represent(field,r,record), _href=str(href))
    2193                      elif field.represent:
    2194                          r = represent(field,r,record)
    2195                  elif linkto and hasattr(field._table,'_primarykey') and fieldname in field._table._primarykey:
    2196                      # have to test this with multi-key tables
    2197                      key = urllib.urlencode(dict( [ \
    2198                                  ((tablename in record \
    2199                                        and isinstance(record, Row) \
    2200                                        and isinstance(record[tablename], Row)) and
    2201                                   (k, record[tablename][k])) or (k, record[k]) \
    2202                                      for k in field._table._primarykey ] ))
    2203                      r = A(r, _href='%s/%s?%s' % (linkto, tablename, key))
    2204                  elif field.type.startswith('list:'):
    2205                      r = represent(field,r or [],record)
    2206                  elif field.represent:
    2207                      r = represent(field,r,record)
    2208                  elif field.type == 'blob' and r:
    2209                      r = 'DATA'
    2210                  elif field.type == 'upload':
    2211                      if upload and r:
    2212                          r = A('file', _href='%s/%s' % (upload, r))
    2213                      elif r:
    2214                          r = 'file'
    2215                      else:
    2216                          r = ''
    2217                  elif field.type in ['string','text']:
    2218                      r = str(field.formatter(r))
    2219                      if headers!={}: #new implement dict
    2220                          if isinstance(headers[colname],dict):
    2221                              if isinstance(headers[colname]['truncate'], int):
    2222                                  r = truncate_string(r, headers[colname]['truncate'])
    2223                      elif not truncate is None:
    2224                          r = truncate_string(r, truncate)
    2225                  attrcol = dict()#new implement dict
    2226                  if headers!={}:
    2227                      if isinstance(headers[colname],dict):
    2228                          colclass=headers[colname]['class']
    2229                          if headers[colname]['selected']:
    2230                              colclass= str(headers[colname]['class'] + " colselected").strip()
    2231                          if colclass!="":
    2232                              attrcol.update(_class=colclass)
    2233
    2234                  row.append(TD(r,**attrcol))
    2235
    2236              if extracolumns:#new implement dict
    2237                  for c in extracolumns:
    2238                      attrcol = dict()
    2239                      colclass=c['class']
    2240                      if c['selected']:
    2241                          colclass= str(c['class'] + " colselected").strip()
    2242                      if colclass!="":
    2243                          attrcol.update(_class=colclass)
    2244                      contentfunc = c['content']
    2245                      row.append(TD(contentfunc(record, rc),**attrcol))
    2246
    2247              tbody.append(TR(_class=_class, *row))
    2248
    2249          if renderstyle:
    2250              components.append(STYLE(self.style()))
    2251
    2252          components.append(TBODY(*tbody))
    2253
    2254
    2255 -    def style(self):
    2256
    2257          css = '''
    2258          table tbody tr.odd {
    2259              background-color: #DFD;
    2260          }
    2261          table tbody tr.even {
    2262              background-color: #EFE;
    2263          }
    2264          table tbody tr.rowselected {
    2265              background-color: #FDD;
    2266          }
    2267          table tbody tr td.colselected {
    2268              background-color: #FDD;
    2269          }
    2270          table tbody tr:hover {
    2271              background: #DDF;
    2272          }
    2273          '''
    2274
    2275          return css
    2276
    2277  form_factory = SQLFORM.factory # for backward compatibility, deprecated
    2278