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 import os 17 from gluon.http import HTTP 18 from gluon.html import XmlComponent 19 from gluon.html import XML, SPAN, TAG, A, DIV, CAT, UL, LI, TEXTAREA, BR, IMG, SCRIPT 20 from gluon.html import FORM, INPUT, LABEL, OPTION, SELECT 21 from gluon.html import TABLE, THEAD, TBODY, TR, TD, TH, STYLE 22 from gluon.html import URL, truncate_string, FIELDSET 23 from gluon.dal import DAL, Field, Table, Row, CALLABLETYPES, smart_query, \ 24 bar_encode, Reference, REGEX_TABLE_DOT_FIELD, Expression 25 from gluon.storage import Storage 26 from gluon.utils import md5_hash 27 from gluon.validators import IS_EMPTY_OR, IS_NOT_EMPTY, IS_LIST_OF, IS_DATE, \ 28 IS_DATETIME, IS_INT_IN_RANGE, IS_FLOAT_IN_RANGE, IS_STRONG 29 30 import gluon.serializers as serializers 31 import datetime 32 import urllib 33 import re 34 import cStringIO 35 from gluon.globals import current 36 from gluon.http import redirect 37 import inspect 38 39 try: 40 import gluon.settings as settings 41 is_gae = settings.global_settings.web2py_runtime_gae 42 except ImportError: 43 is_gae = False # this is an assumption (if settings missing) 44 45 table_field = re.compile('[\w_]+\.[\w_]+') 46 widget_class = re.compile('^\w*')

47 48 -def represent(field, value, record):

49 f = field.represent 50 if not callable(f): 51 return str(value) 52 n = f.func_code.co_argcount - len(f.func_defaults or []) 53 if getattr(f, 'im_self', None): 54 n -= 1 55 if n == 1: 56 return f(value) 57 elif n == 2: 58 return f(value, record) 59 else: 60 raise RuntimeError("field representation must take 1 or 2 args")

61

62 63 -def safe_int(x):

64 try: 65 return int(x) 66 except ValueError: 67 return 0

68

69 70 -def safe_float(x):

71 try: 72 return float(x) 73 except ValueError: 74 return 0

75

76 77 -def show_if(cond):

78 if not cond: 79 return None 80 base = "%s_%s" % (cond.first.tablename, cond.first.name) 81 if ((cond.op.__name__ == 'EQ' and cond.second == True) or 82 (cond.op.__name__ == 'NE' and cond.second == False)): 83 return base,":checked" 84 if ((cond.op.__name__ == 'EQ' and cond.second == False) or 85 (cond.op.__name__ == 'NE' and cond.second == True)): 86 return base,":not(:checked)" 87 if cond.op.__name__ == 'EQ': 88 return base,"[value='%s']" % cond.second 89 if cond.op.__name__ == 'NE': 90 return base,"[value!='%s']" % cond.second 91 if cond.op.__name__ == 'CONTAINS': 92 return base,"[value~='%s']" % cond.second 93 if cond.op.__name__ == 'BELONGS' and isinstance(cond.second,(list,tuple)): 94 return base,','.join("[value='%s']" % (v) for v in cond.second) 95 raise RuntimeError("Not Implemented Error")

96

97 98 -class FormWidget(object):

99 """ 100 helper for SQLFORM to generate form input fields 101 (widget), related to the fieldtype 102 """ 103 104 _class = 'generic-widget' 105 106 @classmethod

107 - def _attributes(cls, field, 108 widget_attributes, **attributes):

109 """ 110 helper to build a common set of attributes 111 112 :param field: the field involved, 113 some attributes are derived from this 114 :param widget_attributes: widget related attributes 115 :param attributes: any other supplied attributes 116 """ 117 attr = dict( 118 _id='%s_%s' % (field.tablename, field.name), 119 _class=cls._class or 120 widget_class.match(str(field.type)).group(), 121 _name=field.name, 122 requires=field.requires, 123 ) 124 if getattr(field,'show_if',None): 125 trigger, cond = show_if(field.show_if) 126 attr['_data-show-trigger'] = trigger 127 attr['_data-show-if'] = cond 128 attr.update(widget_attributes) 129 attr.update(attributes) 130 return attr

131 132 @classmethod

149

150 151 -class StringWidget(FormWidget):

152 _class = 'string' 153 154 @classmethod

169

170 171 -class IntegerWidget(StringWidget):

172 _class = 'integer'

173

174 175 -class DoubleWidget(StringWidget):

176 _class = 'double'

177

178 179 -class DecimalWidget(StringWidget):

180 _class = 'decimal'

181

182 183 -class TimeWidget(StringWidget):

184 _class = 'time'

185

186 187 -class DateWidget(StringWidget):

188 _class = 'date'

189

190 -class DatetimeWidget(StringWidget):

191 _class = 'datetime'

192

193 -class TextWidget(FormWidget):

194 _class = 'text' 195 196 @classmethod

207

208 -class JSONWidget(FormWidget):

209 _class = 'json' 210 211 @classmethod

224

225 -class BooleanWidget(FormWidget):

226 _class = 'boolean' 227 228 @classmethod

240

241 242 -class OptionsWidget(FormWidget):

243 244 @staticmethod

245 - def has_options(field):

246 """ 247 checks if the field has selectable options 248 249 :param field: the field needing checking 250 :returns: True if the field has options 251 """ 252 253 return hasattr(field.requires, 'options')

254 255 @classmethod

257 """ 258 generates a SELECT tag, including OPTIONs (only 1 option allowed) 259 260 see also: :meth:`FormWidget.widget` 261 """ 262 default = dict(value=value) 263 attr = cls._attributes(field, default, 264 **attributes) 265 requires = field.requires 266 if not isinstance(requires, (list, tuple)): 267 requires = [requires] 268 if requires: 269 if hasattr(requires[0], 'options'): 270 options = requires[0].options() 271 else: 272 raise SyntaxError( 273 'widget cannot determine options of %s' % field) 274 opts = [OPTION(v, _value=k) for (k, v) in options] 275 return SELECT(*opts, **attr)

276

277 278 -class ListWidget(StringWidget):

279 280 @classmethod

282 _id = '%s_%s' % (field.tablename, field.name) 283 _name = field.name 284 if field.type == 'list:integer': 285 _class = 'integer' 286 else: 287 _class = 'string' 288 requires = field.requires if isinstance( 289 field.requires, (IS_NOT_EMPTY, IS_LIST_OF)) else None 290 if isinstance(value,str): value = [value] 291 nvalue = value or [''] 292 items = [LI(INPUT(_id=_id, _class=_class, _name=_name, 293 value=v, hideerror=k < len(nvalue) - 1, 294 requires=requires), 295 **attributes) for (k, v) in enumerate(nvalue)] 296 attributes['_id'] = _id + '_grow_input' 297 attributes['_style'] = 'list-style:none' 298 attributes['_class'] = 'w2p_list' 299 return TAG[''](UL(*items, **attributes))

300

301 302 -class MultipleOptionsWidget(OptionsWidget):

303 304 @classmethod

318

319 320 -class RadioWidget(OptionsWidget):

321 322 @classmethod

324 """ 325 generates a TABLE tag, including INPUT radios (only 1 option allowed) 326 327 see also: :meth:`FormWidget.widget` 328 """ 329 330 if isinstance(value, (list,tuple)): 331 value = str(value[0]) 332 else: 333 value = str(value) 334 335 336 attr = cls._attributes(field, {}, **attributes) 337 attr['_class'] = attr.get('_class', 'web2py_radiowidget') 338 339 requires = field.requires 340 if not isinstance(requires, (list, tuple)): 341 requires = [requires] 342 if requires: 343 if hasattr(requires[0], 'options'): 344 options = requires[0].options() 345 else: 346 raise SyntaxError('widget cannot determine options of %s' 347 % field) 348 options = [(k, v) for k, v in options if str(v)] 349 opts = [] 350 cols = attributes.get('cols', 1) 351 totals = len(options) 352 mods = totals % cols 353 rows = totals / cols 354 if mods: 355 rows += 1 356 357 #widget style 358 wrappers = dict( 359 table=(TABLE, TR, TD), 360 ul=(DIV, UL, LI), 361 divs=(CAT, DIV, DIV) 362 ) 363 parent, child, inner = wrappers[attributes.get('style', 'table')] 364 365 for r_index in range(rows): 366 tds = [] 367 for k, v in options[r_index * cols:(r_index + 1) * cols]: 368 checked = {'_checked': 'checked'} if k == value else {} 369 tds.append(inner(INPUT(_type='radio', 370 _id='%s%s' % (field.name, k), 371 _name=field.name, 372 requires=attr.get('requires', None), 373 hideerror=True, _value=k, 374 value=value, 375 **checked), 376 LABEL(v, _for='%s%s' % (field.name, k)))) 377 opts.append(child(tds)) 378 379 if opts: 380 opts[-1][0][0]['hideerror'] = False 381 return parent(*opts, **attr)

382

383 384 -class CheckboxesWidget(OptionsWidget):

385 386 @classmethod

388 """ 389 generates a TABLE tag, including INPUT checkboxes (multiple allowed) 390 391 see also: :meth:`FormWidget.widget` 392 """ 393 394 # was values = re.compile('[\w\-:]+').findall(str(value)) 395 if isinstance(value, (list, tuple)): 396 values = [str(v) for v in value] 397 else: 398 values = [str(value)] 399 400 attr = cls._attributes(field, {}, **attributes) 401 attr['_class'] = attr.get('_class', 'web2py_checkboxeswidget') 402 403 requires = field.requires 404 if not isinstance(requires, (list, tuple)): 405 requires = [requires] 406 if requires and hasattr(requires[0], 'options'): 407 options = requires[0].options() 408 else: 409 raise SyntaxError('widget cannot determine options of %s' 410 % field) 411 412 options = [(k, v) for k, v in options if k != ''] 413 opts = [] 414 cols = attributes.get('cols', 1) 415 totals = len(options) 416 mods = totals % cols 417 rows = totals / cols 418 if mods: 419 rows += 1 420 421 #widget style 422 wrappers = dict( 423 table=(TABLE, TR, TD), 424 ul=(DIV, UL, LI), 425 divs=(CAT, DIV, DIV) 426 ) 427 parent, child, inner = wrappers[attributes.get('style', 'table')] 428 429 for r_index in range(rows): 430 tds = [] 431 for k, v in options[r_index * cols:(r_index + 1) * cols]: 432 if k in values: 433 r_value = k 434 else: 435 r_value = [] 436 tds.append(inner(INPUT(_type='checkbox', 437 _id='%s%s' % (field.name, k), 438 _name=field.name, 439 requires=attr.get('requires', None), 440 hideerror=True, _value=k, 441 value=r_value), 442 LABEL(v, _for='%s%s' % (field.name, k)))) 443 opts.append(child(tds)) 444 445 if opts: 446 opts.append( 447 INPUT(requires=attr.get('requires', None), 448 _style="display:none;", 449 _disabled="disabled", 450 _name=field.name, 451 hideerror=False)) 452 return parent(*opts, **attr)

453

454 455 -class PasswordWidget(FormWidget):

456 _class = 'password' 457 458 DEFAULT_PASSWORD_DISPLAY = 8 * ('*') 459 460 @classmethod

462 """ 463 generates a INPUT password tag. 464 If a value is present it will be shown as a number of '*', not related 465 to the length of the actual value. 466 467 see also: :meth:`FormWidget.widget` 468 """ 469 # detect if attached a IS_STRONG with entropy 470 default = dict( 471 _type='password', 472 _value=(value and cls.DEFAULT_PASSWORD_DISPLAY) or '', 473 ) 474 attr = cls._attributes(field, default, **attributes) 475 476 # deal with entropy check! 477 requires = field.requires 478 if not isinstance(requires, (list, tuple)): 479 requires = [requires] 480 is_strong = [r for r in requires if isinstance(r, IS_STRONG)] 481 if is_strong: 482 attr['_data-w2p_entropy'] = is_strong[0].entropy if is_strong[0].entropy else "null" 483 # end entropy check 484 output = INPUT(**attr) 485 return output

486

487 488 -class UploadWidget(FormWidget):

489 _class = 'upload' 490 491 DEFAULT_WIDTH = '150px' 492 ID_DELETE_SUFFIX = '__delete' 493 GENERIC_DESCRIPTION = 'file' 494 DELETE_FILE = 'delete' 495 496 @classmethod

498 """ 499 generates a INPUT file tag. 500 501 Optionally provides an A link to the file, including a checkbox so 502 the file can be deleted. 503 All is wrapped in a DIV. 504 505 see also: :meth:`FormWidget.widget` 506 507 :param download_url: Optional URL to link to the file (default = None) 508 """ 509 510 default = dict(_type='file',) 511 attr = cls._attributes(field, default, **attributes) 512 513 inp = INPUT(**attr) 514 515 if download_url and value: 516 if callable(download_url): 517 url = download_url(value) 518 else: 519 url = download_url + '/' + value 520 (br, image) = ('', '') 521 if UploadWidget.is_image(value): 522 br = BR() 523 image = IMG(_src=url, _width=cls.DEFAULT_WIDTH) 524 525 requires = attr["requires"] 526 if requires == [] or isinstance(requires, IS_EMPTY_OR): 527 inp = DIV(inp, 528 SPAN('[', 529 A(current.T( 530 UploadWidget.GENERIC_DESCRIPTION), _href=url), 531 '|', 532 INPUT(_type='checkbox', 533 _name=field.name + cls.ID_DELETE_SUFFIX, 534 _id=field.name + cls.ID_DELETE_SUFFIX), 535 LABEL(current.T(cls.DELETE_FILE), 536 _for=field.name + cls.ID_DELETE_SUFFIX, 537 _style='display:inline'), 538 ']', _style='white-space:nowrap'), 539 br, image) 540 else: 541 inp = DIV(inp, 542 SPAN('[', 543 A(cls.GENERIC_DESCRIPTION, _href=url), 544 ']', _style='white-space:nowrap'), 545 br, image) 546 return inp

547 548 @classmethod

549 - def represent(cls, field, value, download_url=None):

550 """ 551 how to represent the file: 552 553 - with download url and if it is an image: <A href=...><IMG ...></A> 554 - otherwise with download url: <A href=...>file</A> 555 - otherwise: file 556 557 :param field: the field 558 :param value: the field value 559 :param download_url: url for the file download (default = None) 560 """ 561 562 inp = cls.GENERIC_DESCRIPTION 563 564 if download_url and value: 565 if callable(download_url): 566 url = download_url(value) 567 else: 568 url = download_url + '/' + value 569 if cls.is_image(value): 570 inp = IMG(_src=url, _width=cls.DEFAULT_WIDTH) 571 inp = A(inp, _href=url) 572 573 return inp

574 575 @staticmethod

576 - def is_image(value):

577 """ 578 Tries to check if the filename provided references to an image 579 580 Checking is based on filename extension. Currently recognized: 581 gif, png, jp(e)g, bmp 582 583 :param value: filename 584 """ 585 586 extension = value.split('.')[-1].lower() 587 if extension in ['gif', 'png', 'jpg', 'jpeg', 'bmp']: 588 return True 589 return False

590

591 592 -class AutocompleteWidget(object):

593 _class = 'string' 594

595 - def __init__(self, request, field, id_field=None, db=None, 596 orderby=None, limitby=(0, 10), distinct=False, 597 keyword='_autocomplete_%(tablename)s_%(fieldname)s', 598 min_length=2, help_fields=None, help_string=None):

599 600 self.help_fields = help_fields or [] 601 self.help_string = help_string 602 if self.help_fields and not self.help_string: 603 self.help_string = ' '.join('%%(%s)s'%f.name 604 for f in self.help_fields) 605 606 self.request = request 607 self.keyword = keyword % dict(tablename=field.tablename, 608 fieldname=field.name) 609 self.db = db or field._db 610 self.orderby = orderby 611 self.limitby = limitby 612 self.distinct = distinct 613 self.min_length = min_length 614 self.fields = [field] 615 if id_field: 616 self.is_reference = True 617 self.fields.append(id_field) 618 else: 619 self.is_reference = False 620 if hasattr(request, 'application'): 621 self.url = URL(args=request.args) 622 self.callback() 623 else: 624 self.url = request

625

626 - def callback(self):

627 if self.keyword in self.request.vars: 628 field = self.fields[0] 629 if is_gae: 630 rows = self.db(field.__ge__(self.request.vars[self.keyword]) & field.__lt__(self.request.vars[self.keyword] + u'\ufffd')).select(orderby=self.orderby, limitby=self.limitby, *(self.fields+self.help_fields)) 631 else: 632 rows = self.db(field.like(self.request.vars[self.keyword] + '%')).select(orderby=self.orderby, limitby=self.limitby, distinct=self.distinct, *(self.fields+self.help_fields)) 633 if rows: 634 if self.is_reference: 635 id_field = self.fields[1] 636 if self.help_fields: 637 options = [OPTION( 638 self.help_string % dict([(h.name, s[h.name]) for h in self.fields[:1] + self.help_fields]), 639 _value=s[id_field.name], _selected=(k == 0)) for k, s in enumerate(rows)] 640 else: 641 options = [OPTION( 642 s[field.name], _value=s[id_field.name], 643 _selected=(k == 0)) for k, s in enumerate(rows)] 644 raise HTTP( 645 200, SELECT(_id=self.keyword, _class='autocomplete', 646 _size=len(rows), _multiple=(len(rows) == 1), 647 *options).xml()) 648 else: 649 raise HTTP( 650 200, SELECT(_id=self.keyword, _class='autocomplete', 651 _size=len(rows), _multiple=(len(rows) == 1), 652 *[OPTION(s[field.name], 653 _selected=(k == 0)) 654 for k, s in enumerate(rows)]).xml()) 655 else: 656 raise HTTP(200, '')

657

658 - def __call__(self, field, value, **attributes):

659 default = dict( 660 _type='text', 661 value=(not value is None and str(value)) or '', 662 ) 663 attr = StringWidget._attributes(field, default, **attributes) 664 div_id = self.keyword + '_div' 665 attr['_autocomplete'] = 'off' 666 if self.is_reference: 667 key2 = self.keyword + '_aux' 668 key3 = self.keyword + '_auto' 669 attr['_class'] = 'string' 670 name = attr['_name'] 671 if 'requires' in attr: 672 del attr['requires'] 673 attr['_name'] = key2 674 value = attr['value'] 675 record = self.db( 676 self.fields[1] == value).select(self.fields[0]).first() 677 attr['value'] = record and record[self.fields[0].name] 678 attr['_onblur'] = "jQuery('#%(div_id)s').delay(1000).fadeOut('slow');" % \ 679 dict(div_id=div_id, u='F' + self.keyword) 680 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='+encodeURIComponent(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');" % \ 681 dict(url=self.url, min_length=self.min_length, 682 key=self.keyword, id=attr['_id'], key2=key2, key3=key3, 683 name=name, div_id=div_id, u='F' + self.keyword) 684 if self.min_length == 0: 685 attr['_onfocus'] = attr['_onkeyup'] 686 return TAG[''](INPUT(**attr), INPUT(_type='hidden', _id=key3, _value=value, 687 _name=name, requires=field.requires), 688 DIV(_id=div_id, _style='position:absolute;')) 689 else: 690 attr['_name'] = field.name 691 attr['_onblur'] = "jQuery('#%(div_id)s').delay(1000).fadeOut('slow');" % \ 692 dict(div_id=div_id, u='F' + self.keyword) 693 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='+encodeURIComponent(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');" % \ 694 dict(url=self.url, min_length=self.min_length, 695 key=self.keyword, id=attr['_id'], div_id=div_id, u='F' + self.keyword) 696 if self.min_length == 0: 697 attr['_onfocus'] = attr['_onkeyup'] 698 return TAG[''](INPUT(**attr), DIV(_id=div_id, _style='position:absolute;'))

699

700 701 -def formstyle_table3cols(form, fields):

702 ''' 3 column table - default ''' 703 table = TABLE() 704 for id, label, controls, help in fields: 705 _help = TD(help, _class='w2p_fc') 706 _controls = TD(controls, _class='w2p_fw') 707 _label = TD(label, _class='w2p_fl') 708 table.append(TR(_label, _controls, _help, _id=id)) 709 return table

710

711 712 -def formstyle_table2cols(form, fields):

713 ''' 2 column table ''' 714 table = TABLE() 715 for id, label, controls, help in fields: 716 _help = TD(help, _class='w2p_fc', _width='50%') 717 _controls = TD(controls, _class='w2p_fw', _colspan='2') 718 _label = TD(label, _class='w2p_fl', _width='50%') 719 table.append(TR(_label, _help, _id=id + '1', _class='even')) 720 table.append(TR(_controls, _id=id + '2', _class='odd')) 721 return table

722

723 724 -def formstyle_divs(form, fields):

725 ''' divs only ''' 726 table = FIELDSET() 727 for id, label, controls, help in fields: 728 _help = DIV(help, _class='w2p_fc') 729 _controls = DIV(controls, _class='w2p_fw') 730 _label = DIV(label, _class='w2p_fl') 731 table.append(DIV(_label, _controls, _help, _id=id)) 732 return table

733

734 735 -def formstyle_inline(form, fields):

736 ''' divs only ''' 737 if len(fields) != 2: 738 raise RuntimeError("Not possible") 739 id, label, controls, help = fields[0] 740 submit_button = fields[1][2] 741 return CAT(DIV(controls, _style='display:inline'), 742 submit_button)

743

744 745 -def formstyle_ul(form, fields):

746 ''' unordered list ''' 747 table = UL() 748 for id, label, controls, help in fields: 749 _help = DIV(help, _class='w2p_fc') 750 _controls = DIV(controls, _class='w2p_fw') 751 _label = DIV(label, _class='w2p_fl') 752 table.append(LI(_label, _controls, _help, _id=id)) 753 return table

754

755 756 -def formstyle_bootstrap(form, fields):

757 ''' bootstrap format form layout ''' 758 form.add_class('form-horizontal') 759 parent = FIELDSET() 760 for id, label, controls, help in fields: 761 # wrappers 762 _help = SPAN(help, _class='help-block') 763 # embed _help into _controls 764 _controls = DIV(controls, _help, _class='controls') 765 # submit unflag by default 766 _submit = False 767 768 if isinstance(controls, INPUT): 769 controls.add_class('span4') 770 if controls['_type'] == 'submit': 771 # flag submit button 772 _submit = True 773 controls['_class'] = 'btn btn-primary' 774 if controls['_type'] == 'file': 775 controls['_class'] = 'input-file' 776 777 # For password fields, which are wrapped in a CAT object. 778 if isinstance(controls, CAT) and isinstance(controls[0], INPUT): 779 controls[0].add_class('span4') 780 781 if isinstance(controls, SELECT): 782 controls.add_class('span4') 783 784 if isinstance(controls, TEXTAREA): 785 controls.add_class('span4') 786 787 if isinstance(label, LABEL): 788 label['_class'] = 'control-label' 789 790 if _submit: 791 # submit button has unwrapped label and controls, different class 792 parent.append(DIV(label, controls, _class='form-actions', _id=id)) 793 # unflag submit (possible side effect) 794 _submit = False 795 else: 796 # unwrapped label 797 parent.append(DIV(label, _controls, _class='control-group', _id=id)) 798 return parent

799

800 801 -class SQLFORM(FORM):

802 803 """ 804 SQLFORM is used to map a table (and a current record) into an HTML form 805 806 given a SQLTable stored in db.table 807 808 generates an insert form:: 809 810 SQLFORM(db.table) 811 812 generates an update form:: 813 814 record=db.table[some_id] 815 SQLFORM(db.table, record) 816 817 generates an update with a delete button:: 818 819 SQLFORM(db.table, record, deletable=True) 820 821 if record is an int:: 822 823 record=db.table[record] 824 825 optional arguments: 826 827 :param fields: a list of fields that should be placed in the form, 828 default is all. 829 :param labels: a dictionary with labels for each field, keys are the field 830 names. 831 :param col3: a dictionary with content for an optional third column 832 (right of each field). keys are field names. 833 :param linkto: the URL of a controller/function to access referencedby 834 records 835 see controller appadmin.py for examples 836 :param upload: the URL of a controller/function to download an uploaded file 837 see controller appadmin.py for examples 838 839 any named optional attribute is passed to the <form> tag 840 for example _class, _id, _style, _action, _method, etc. 841 842 """ 843 844 # usability improvements proposal by fpp - 4 May 2008 : 845 # - correct labels (for points to field id, not field name) 846 # - add label for delete checkbox 847 # - add translatable label for record ID 848 # - add third column to right of fields, populated from the col3 dict 849 850 widgets = Storage(dict( 851 string=StringWidget, 852 text=TextWidget, 853 json=JSONWidget, 854 password=PasswordWidget, 855 integer=IntegerWidget, 856 double=DoubleWidget, 857 decimal=DecimalWidget, 858 time=TimeWidget, 859 date=DateWidget, 860 datetime=DatetimeWidget, 861 upload=UploadWidget, 862 boolean=BooleanWidget, 863 blob=None, 864 options=OptionsWidget, 865 multiple=MultipleOptionsWidget, 866 radio=RadioWidget, 867 checkboxes=CheckboxesWidget, 868 autocomplete=AutocompleteWidget, 869 list=ListWidget, 870 )) 871 872 formstyles = Storage(dict( 873 table3cols=formstyle_table3cols, 874 table2cols=formstyle_table2cols, 875 divs=formstyle_divs, 876 ul=formstyle_ul, 877 bootstrap=formstyle_bootstrap, 878 inline=formstyle_inline, 879 )) 880 881 FIELDNAME_REQUEST_DELETE = 'delete_this_record' 882 FIELDKEY_DELETE_RECORD = 'delete_record' 883 ID_LABEL_SUFFIX = '__label' 884 ID_ROW_SUFFIX = '__row' 885

886 - def assert_status(self, status, request_vars):

887 if not status and self.record and self.errors: 888 ### if there are errors in update mode 889 # and some errors refers to an already uploaded file 890 # delete error if 891 # - user not trying to upload a new file 892 # - there is existing file and user is not trying to delete it 893 # this is because removing the file may not pass validation 894 for key in self.errors.keys(): 895 if key in self.table \ 896 and self.table[key].type == 'upload' \ 897 and request_vars.get(key, None) in (None, '') \ 898 and self.record[key] \ 899 and not key + UploadWidget.ID_DELETE_SUFFIX in request_vars: 900 del self.errors[key] 901 if not self.errors: 902 status = True 903 return status

904

905 - def __init__( 906 self, 907 table, 908 record=None, 909 deletable=False, 910 linkto=None, 911 upload=None, 912 fields=None, 913 labels=None, 914 col3={}, 915 submit_button='Submit', 916 delete_label='Check to delete', 917 showid=True, 918 readonly=False, 919 comments=True, 920 keepopts=[], 921 ignore_rw=False, 922 record_id=None, 923 formstyle='table3cols', 924 buttons=['submit'], 925 separator=': ', 926 **attributes 927 ):

928 """ 929 SQLFORM(db.table, 930 record=None, 931 fields=['name'], 932 labels={'name': 'Your name'}, 933 linkto=URL(f='table/db/') 934 """ 935 T = current.T 936 937 self.ignore_rw = ignore_rw 938 self.formstyle = formstyle 939 self.readonly = readonly 940 # Default dbio setting 941 self.detect_record_change = None 942 943 nbsp = XML(' ') # Firefox2 does not display fields with blanks 944 FORM.__init__(self, *[], **attributes) 945 ofields = fields 946 keyed = hasattr(table, '_primarykey') # for backward compatibility 947 948 # if no fields are provided, build it from the provided table 949 # will only use writable or readable fields, unless forced to ignore 950 if fields is None: 951 fields = [f.name for f in table if 952 (ignore_rw or f.writable or f.readable) and 953 (readonly or not f.compute)] 954 self.fields = fields 955 956 # make sure we have an id 957 if self.fields[0] != table.fields[0] and \ 958 isinstance(table, Table) and not keyed: 959 self.fields.insert(0, table.fields[0]) 960 961 self.table = table 962 963 # try to retrieve the indicated record using its id 964 # otherwise ignore it 965 if record and isinstance(record, (int, long, str, unicode)): 966 if not str(record).isdigit(): 967 raise HTTP(404, "Object not found") 968 record = table._db(table._id == record).select().first() 969 if not record: 970 raise HTTP(404, "Object not found") 971 self.record = record 972 973 self.record_id = record_id 974 if keyed: 975 self.record_id = dict([(k, record and str(record[k]) or None) 976 for k in table._primarykey]) 977 self.field_parent = {} 978 xfields = [] 979 self.fields = fields 980 self.custom = Storage() 981 self.custom.dspval = Storage() 982 self.custom.inpval = Storage() 983 self.custom.label = Storage() 984 self.custom.comment = Storage() 985 self.custom.widget = Storage() 986 self.custom.linkto = Storage() 987 988 # default id field name 989 if not keyed: 990 self.id_field_name = table._id.name 991 else: 992 self.id_field_name = table._primarykey[0] # only works if one key 993 994 sep = separator or '' 995 996 for fieldname in self.fields: 997 if fieldname.find('.') >= 0: 998 continue 999 1000 field = self.table[fieldname] 1001 comment = None 1002 1003 if comments: 1004 comment = col3.get(fieldname, field.comment) 1005 if comment is None: 1006 comment = '' 1007 self.custom.comment[fieldname] = comment 1008 1009 if not labels is None and fieldname in labels: 1010 label = labels[fieldname] 1011 else: 1012 label = field.label 1013 self.custom.label[fieldname] = label 1014 1015 field_id = '%s_%s' % (table._tablename, fieldname) 1016 1017 label = LABEL(label, label and sep, _for=field_id, 1018 _id=field_id + SQLFORM.ID_LABEL_SUFFIX) 1019 1020 row_id = field_id + SQLFORM.ID_ROW_SUFFIX 1021 if field.type == 'id': 1022 self.custom.dspval.id = nbsp 1023 self.custom.inpval.id = '' 1024 widget = '' 1025 1026 # store the id field name (for legacy databases) 1027 self.id_field_name = field.name 1028 1029 if record: 1030 if showid and field.name in record and field.readable: 1031 v = record[field.name] 1032 widget = SPAN(v, _id=field_id) 1033 self.custom.dspval.id = str(v) 1034 xfields.append((row_id, label, widget, comment)) 1035 self.record_id = str(record[field.name]) 1036 self.custom.widget.id = widget 1037 continue 1038 1039 if readonly and not ignore_rw and not field.readable: 1040 continue 1041 1042 if record: 1043 default = record[fieldname] 1044 else: 1045 default = field.default 1046 if isinstance(default, CALLABLETYPES): 1047 default = default() 1048 1049 cond = readonly or \ 1050 (not ignore_rw and not field.writable and field.readable) 1051 1052 if default is not None and not cond: 1053 default = field.formatter(default) 1054 dspval = default 1055 inpval = default 1056 1057 if cond: 1058 1059 # ## if field.represent is available else 1060 # ## ignore blob and preview uploaded images 1061 # ## format everything else 1062 1063 if field.represent: 1064 inp = represent(field, default, record) 1065 elif field.type in ['blob']: 1066 continue 1067 elif field.type == 'upload': 1068 inp = UploadWidget.represent(field, default, upload) 1069 elif field.type == 'boolean': 1070 inp = self.widgets.boolean.widget( 1071 field, default, _disabled=True) 1072 else: 1073 inp = field.formatter(default) 1074 elif field.type == 'upload': 1075 if field.widget: 1076 inp = field.widget(field, default, upload) 1077 else: 1078 inp = self.widgets.upload.widget(field, default, upload) 1079 elif field.widget: 1080 inp = field.widget(field, default) 1081 elif field.type == 'boolean': 1082 inp = self.widgets.boolean.widget(field, default) 1083 if default: 1084 inpval = 'checked' 1085 else: 1086 inpval = '' 1087 elif OptionsWidget.has_options(field): 1088 if not field.requires.multiple: 1089 inp = self.widgets.options.widget(field, default) 1090 else: 1091 inp = self.widgets.multiple.widget(field, default) 1092 if fieldname in keepopts: 1093 inpval = TAG[''](*inp.components) 1094 elif field.type.startswith('list:'): 1095 inp = self.widgets.list.widget(field, default) 1096 elif field.type == 'text': 1097 inp = self.widgets.text.widget(field, default) 1098 elif field.type == 'password': 1099 inp = self.widgets.password.widget(field, default) 1100 if self.record: 1101 dspval = PasswordWidget.DEFAULT_PASSWORD_DISPLAY 1102 else: 1103 dspval = '' 1104 elif field.type == 'blob': 1105 continue 1106 else: 1107 field_type = widget_class.match(str(field.type)).group() 1108 field_type = field_type in self.widgets and field_type or 'string' 1109 inp = self.widgets[field_type].widget(field, default) 1110 1111 xfields.append((row_id, label, inp, comment)) 1112 self.custom.dspval[fieldname] = dspval if (dspval is not None) else nbsp 1113 self.custom.inpval[ 1114 fieldname] = inpval if not inpval is None else '' 1115 self.custom.widget[fieldname] = inp 1116 1117 # if a record is provided and found, as is linkto 1118 # build a link 1119 if record and linkto: 1120 db = linkto.split('/')[-1] 1121 for rfld in table._referenced_by: 1122 if keyed: 1123 query = urllib.quote('%s.%s==%s' % ( 1124 db, rfld, record[rfld.type[10:].split('.')[1]])) 1125 else: 1126 query = urllib.quote( 1127 '%s.%s==%s' % (db, rfld, record[self.id_field_name])) 1128 lname = olname = '%s.%s' % (rfld.tablename, rfld.name) 1129 if ofields and not olname in ofields: 1130 continue 1131 if labels and lname in labels: 1132 lname = labels[lname] 1133 widget = A(lname, 1134 _class='reference', 1135 _href='%s/%s?query=%s' % (linkto, rfld.tablename, query)) 1136 xfields.append( 1137 (olname.replace('.', '__') + SQLFORM.ID_ROW_SUFFIX, 1138 '', widget, col3.get(olname, ''))) 1139 self.custom.linkto[olname.replace('.', '__')] = widget 1140 # </block> 1141 1142 # when deletable, add delete? checkbox 1143 self.custom.delete = self.custom.deletable = '' 1144 if record and deletable: 1145 #add secondary css class for cascade delete warning 1146 css = 'delete' 1147 for f in self.table.fields: 1148 on_del = self.table[f].ondelete 1149 if isinstance(on_del,str) and 'cascade' in on_del.lower(): 1150 css += ' cascade_delete' 1151 break 1152 widget = INPUT(_type='checkbox', 1153 _class=css, 1154 _id=self.FIELDKEY_DELETE_RECORD, 1155 _name=self.FIELDNAME_REQUEST_DELETE, 1156 ) 1157 xfields.append( 1158 (self.FIELDKEY_DELETE_RECORD + SQLFORM.ID_ROW_SUFFIX, 1159 LABEL( 1160 T(delete_label), separator, 1161 _for=self.FIELDKEY_DELETE_RECORD, 1162 _id=self.FIELDKEY_DELETE_RECORD + \ 1163 SQLFORM.ID_LABEL_SUFFIX), 1164 widget, 1165 col3.get(self.FIELDKEY_DELETE_RECORD, ''))) 1166 self.custom.delete = self.custom.deletable = widget 1167 1168 1169 # when writable, add submit button 1170 self.custom.submit = '' 1171 if not readonly: 1172 if 'submit' in buttons: 1173 widget = self.custom.submit = INPUT(_type='submit', 1174 _value=T(submit_button)) 1175 elif buttons: 1176 widget = self.custom.submit = DIV(*buttons) 1177 if self.custom.submit: 1178 xfields.append(('submit_record' + SQLFORM.ID_ROW_SUFFIX, 1179 '', widget, col3.get('submit_button', ''))) 1180 1181 # if a record is provided and found 1182 # make sure it's id is stored in the form 1183 if record: 1184 if not self['hidden']: 1185 self['hidden'] = {} 1186 if not keyed: 1187 self['hidden']['id'] = record[table._id.name] 1188 1189 (begin, end) = self._xml() 1190 self.custom.begin = XML("<%s %s>" % (self.tag, begin)) 1191 self.custom.end = XML("%s</%s>" % (end, self.tag)) 1192 table = self.createform(xfields) 1193 self.components = [table]

1194

1195 - def createform(self, xfields):

1196 formstyle = self.formstyle 1197 if isinstance(formstyle, basestring): 1198 if formstyle in SQLFORM.formstyles: 1199 formstyle = SQLFORM.formstyles[formstyle] 1200 else: 1201 raise RuntimeError('formstyle not found') 1202 1203 if callable(formstyle): 1204 # backward compatibility, 4 argument function is the old style 1205 args, varargs, keywords, defaults = inspect.getargspec(formstyle) 1206 if defaults and len(args) - len(defaults) == 4 or len(args) == 4: 1207 table = TABLE() 1208 for id, a, b, c in xfields: 1209 newrows = formstyle(id, a, b, c) 1210 self.field_parent[id] = getattr(b, 'parent', None) \ 1211 if isinstance(b,XmlComponent) else None 1212 if type(newrows).__name__ != "tuple": 1213 newrows = [newrows] 1214 for newrow in newrows: 1215 table.append(newrow) 1216 else: 1217 table = formstyle(self, xfields) 1218 for id, a, b, c in xfields: 1219 self.field_parent[id] = getattr(b, 'parent', None) \ 1220 if isinstance(b,XmlComponent) else None 1221 else: 1222 raise RuntimeError('formstyle not supported') 1223 return table

1224

1225 - def accepts( 1226 self, 1227 request_vars, 1228 session=None, 1229 formname='%(tablename)s/%(record_id)s', 1230 keepvalues=None, 1231 onvalidation=None, 1232 dbio=True, 1233 hideerror=False, 1234 detect_record_change=False, 1235 **kwargs 1236 ):

1237 1238 """ 1239 similar FORM.accepts but also does insert, update or delete in DAL. 1240 but if detect_record_change == True than: 1241 form.record_changed = False (record is properly validated/submitted) 1242 form.record_changed = True (record cannot be submitted because changed) 1243 elseif detect_record_change == False than: 1244 form.record_changed = None 1245 """ 1246 1247 if keepvalues is None: 1248 keepvalues = True if self.record else False 1249 1250 if self.readonly: 1251 return False 1252 1253 if request_vars.__class__.__name__ == 'Request': 1254 request_vars = request_vars.post_vars 1255 1256 keyed = hasattr(self.table, '_primarykey') 1257 1258 # implement logic to detect whether record exist but has been modified 1259 # server side 1260 self.record_changed = None 1261 self.detect_record_change = detect_record_change 1262 if self.detect_record_change: 1263 if self.record: 1264 self.record_changed = False 1265 serialized = '|'.join( 1266 str(self.record[k]) for k in self.table.fields()) 1267 self.record_hash = md5_hash(serialized) 1268 1269 # logic to deal with record_id for keyed tables 1270 if self.record: 1271 if keyed: 1272 formname_id = '.'.join(str(self.record[k]) 1273 for k in self.table._primarykey 1274 if hasattr(self.record, k)) 1275 record_id = dict((k, request_vars.get(k, None)) 1276 for k in self.table._primarykey) 1277 else: 1278 (formname_id, record_id) = (self.record[self.id_field_name], 1279 request_vars.get('id', None)) 1280 keepvalues = True 1281 else: 1282 if keyed: 1283 formname_id = 'create' 1284 record_id = dict([(k, None) for k in self.table._primarykey]) 1285 else: 1286 (formname_id, record_id) = ('create', None) 1287 1288 if not keyed and isinstance(record_id, (list, tuple)): 1289 record_id = record_id[0] 1290 1291 if formname: 1292 formname = formname % dict(tablename=self.table._tablename, 1293 record_id=formname_id) 1294 1295 # ## THIS IS FOR UNIQUE RECORDS, read IS_NOT_IN_DB 1296 1297 for fieldname in self.fields: 1298 field = self.table[fieldname] 1299 requires = field.requires or [] 1300 if not isinstance(requires, (list, tuple)): 1301 requires = [requires] 1302 [item.set_self_id(self.record_id) for item in requires 1303 if hasattr(item, 'set_self_id') and self.record_id] 1304 1305 # ## END 1306 1307 fields = {} 1308 for key in self.vars: 1309 fields[key] = self.vars[key] 1310 1311 ret = FORM.accepts( 1312 self, 1313 request_vars, 1314 session, 1315 formname, 1316 keepvalues, 1317 onvalidation, 1318 hideerror=hideerror, 1319 **kwargs 1320 ) 1321 1322 self.deleted = \ 1323 request_vars.get(self.FIELDNAME_REQUEST_DELETE, False) 1324 1325 self.custom.end = TAG[''](self.hidden_fields(), self.custom.end) 1326 1327 auch = record_id and self.errors and self.deleted 1328 1329 if self.record_changed and self.detect_record_change: 1330 message_onchange = \ 1331 kwargs.setdefault("message_onchange", 1332 current.T("A record change was detected. " + 1333 "Consecutive update self-submissions " + 1334 "are not allowed. Try re-submitting or " + 1335 "refreshing the form page.")) 1336 if message_onchange is not None: 1337 current.response.flash = message_onchange 1338 return ret 1339 elif (not ret) and (not auch): 1340 # auch is true when user tries to delete a record 1341 # that does not pass validation, yet it should be deleted 1342 for fieldname in self.fields: 1343 field = self.table[fieldname] 1344 ### this is a workaround! widgets should always have default not None! 1345 if not field.widget and field.type.startswith('list:') and \ 1346 not OptionsWidget.has_options(field): 1347 field.widget = self.widgets.list.widget 1348 if field.widget and fieldname in request_vars: 1349 if fieldname in self.request_vars: 1350 value = self.request_vars[fieldname] 1351 elif self.record: 1352 value = self.record[fieldname] 1353 else: 1354 value = self.table[fieldname].default 1355 row_id = '%s_%s%s' % ( 1356 self.table, fieldname, SQLFORM.ID_ROW_SUFFIX) 1357 widget = field.widget(field, value) 1358 parent = self.field_parent[row_id] 1359 if parent: 1360 parent.components = [widget] 1361 if self.errors.get(fieldname): 1362 parent._traverse(False, hideerror) 1363 self.custom.widget[fieldname] = widget 1364 self.accepted = ret 1365 return ret 1366 1367 if record_id and str(record_id) != str(self.record_id): 1368 raise SyntaxError('user is tampering with form\'s record_id: ' 1369 '%s != %s' % (record_id, self.record_id)) 1370 1371 if record_id and dbio and not keyed: 1372 self.vars.id = self.record[self.id_field_name] 1373 1374 if self.deleted and self.custom.deletable: 1375 if dbio: 1376 if keyed: 1377 qry = reduce(lambda x, y: x & y, 1378 [self.table[k] == record_id[k] 1379 for k in self.table._primarykey]) 1380 else: 1381 qry = self.table._id == self.record[self.id_field_name] 1382 self.table._db(qry).delete() 1383 self.errors.clear() 1384 for component in self.elements('input, select, textarea'): 1385 component['_disabled'] = True 1386 self.accepted = True 1387 return True 1388 1389 for fieldname in self.fields: 1390 if not fieldname in self.table.fields: 1391 continue 1392 1393 if not self.ignore_rw and not self.table[fieldname].writable: 1394 ### this happens because FORM has no knowledge of writable 1395 ### and thinks that a missing boolean field is a None 1396 if self.table[fieldname].type == 'boolean' and \ 1397 self.vars.get(fieldname, True) is None: 1398 del self.vars[fieldname] 1399 continue 1400 1401 field = self.table[fieldname] 1402 if field.type == 'id': 1403 continue 1404 if field.type == 'boolean': 1405 if self.vars.get(fieldname, False): 1406 self.vars[fieldname] = fields[fieldname] = True 1407 else: 1408 self.vars[fieldname] = fields[fieldname] = False 1409 elif field.type == 'password' and self.record\ 1410 and request_vars.get(fieldname, None) == \ 1411 PasswordWidget.DEFAULT_PASSWORD_DISPLAY: 1412 continue # do not update if password was not changed 1413 elif field.type == 'upload': 1414 f = self.vars[fieldname] 1415 fd = '%s__delete' % fieldname 1416 if f == '' or f is None: 1417 if self.vars.get(fd, False): 1418 f = self.table[fieldname].default or '' 1419 fields[fieldname] = f 1420 elif self.record: 1421 if self.record[fieldname]: 1422 fields[fieldname] = self.record[fieldname] 1423 else: 1424 f = self.table[fieldname].default or '' 1425 fields[fieldname] = f 1426 else: 1427 f = self.table[fieldname].default or '' 1428 fields[fieldname] = f 1429 self.vars[fieldname] = fields[fieldname] 1430 if not f: 1431 continue 1432 else: 1433 f = os.path.join( 1434 current.request.folder, 1435 os.path.normpath(f)) 1436 source_file = open(f, 'rb') 1437 original_filename = os.path.split(f)[1] 1438 elif hasattr(f, 'file'): 1439 (source_file, original_filename) = (f.file, f.filename) 1440 elif isinstance(f, (str, unicode)): 1441 ### do not know why this happens, it should not 1442 (source_file, original_filename) = \ 1443 (cStringIO.StringIO(f), 'file.txt') 1444 else: 1445 # this should never happen, why does it happen? 1446 #print 'f=',repr(f) 1447 continue 1448 newfilename = field.store(source_file, original_filename, 1449 field.uploadfolder) 1450 # this line was for backward compatibility but problematic 1451 # self.vars['%s_newfilename' % fieldname] = newfilename 1452 fields[fieldname] = newfilename 1453 if isinstance(field.uploadfield, str): 1454 fields[field.uploadfield] = source_file.read() 1455 # proposed by Hamdy (accept?) do we need fields at this point? 1456 self.vars[fieldname] = fields[fieldname] 1457 continue 1458 elif fieldname in self.vars: 1459 fields[fieldname] = self.vars[fieldname] 1460 elif field.default is None and field.type != 'blob': 1461 self.errors[fieldname] = 'no data' 1462 self.accepted = False 1463 return False 1464 value = fields.get(fieldname, None) 1465 if field.type == 'list:string': 1466 if not isinstance(value, (tuple, list)): 1467 fields[fieldname] = value and [value] or [] 1468 elif isinstance(field.type, str) and field.type.startswith('list:'): 1469 if not isinstance(value, list): 1470 fields[fieldname] = [safe_int( 1471 x) for x in (value and [value] or [])] 1472 elif field.type == 'integer': 1473 if not value is None: 1474 fields[fieldname] = safe_int(value) 1475 elif field.type.startswith('reference'): 1476 if not value is None and isinstance(self.table, Table) and not keyed: 1477 fields[fieldname] = safe_int(value) 1478 elif field.type == 'double': 1479 if not value is None: 1480 fields[fieldname] = safe_float(value) 1481 1482 for fieldname in self.vars: 1483 if fieldname != 'id' and fieldname in self.table.fields\ 1484 and not fieldname in fields and not fieldname\ 1485 in request_vars: 1486 fields[fieldname] = self.vars[fieldname] 1487 1488 if dbio: 1489 if 'delete_this_record' in fields: 1490 # this should never happen but seems to happen to some 1491 del fields['delete_this_record'] 1492 for field in self.table: 1493 if not field.name in fields and field.writable is False \ 1494 and field.update is None and field.compute is None: 1495 if record_id and self.record: 1496 fields[field.name] = self.record[field.name] 1497 elif not self.table[field.name].default is None: 1498 fields[field.name] = self.table[field.name].default 1499 if keyed: 1500 if reduce(lambda x, y: x and y, record_id.values()): # if record_id 1501 if fields: 1502 qry = reduce(lambda x, y: x & y, 1503 [self.table[k] == self.record[k] for k in self.table._primarykey]) 1504 self.table._db(qry).update(**fields) 1505 else: 1506 pk = self.table.insert(**fields) 1507 if pk: 1508 self.vars.update(pk) 1509 else: 1510 ret = False 1511 else: 1512 if record_id: 1513 self.vars.id = self.record[self.id_field_name] 1514 if fields: 1515 self.table._db(self.table._id == self.record[ 1516 self.id_field_name]).update(**fields) 1517 else: 1518 self.vars.id = self.table.insert(**fields) 1519 self.accepted = ret 1520 return ret

1521 1522 AUTOTYPES = { 1523 type(''): ('string', None), 1524 type(True): ('boolean', None), 1525 type(1): ('integer', IS_INT_IN_RANGE(-1e12, +1e12)), 1526 type(1.0): ('double', IS_FLOAT_IN_RANGE()), 1527 type([]): ('list:string', None), 1528 type(datetime.date.today()): ('date', IS_DATE()), 1529 type(datetime.datetime.today()): ('datetime', IS_DATETIME()) 1530 } 1531 1532 @staticmethod

1533 - def dictform(dictionary, **kwargs):

1534 fields = [] 1535 for key, value in sorted(dictionary.items()): 1536 t, requires = SQLFORM.AUTOTYPES.get(type(value), (None, None)) 1537 if t: 1538 fields.append(Field(key, t, requires=requires, 1539 default=value)) 1540 return SQLFORM.factory(*fields, **kwargs)

1541 1542 @staticmethod

1543 - def smartdictform(session, name, filename=None, query=None, **kwargs):

1544 import os 1545 if query: 1546 session[name] = query.db(query).select().first().as_dict() 1547 elif os.path.exists(filename): 1548 env = {'datetime': datetime} 1549 session[name] = eval(open(filename).read(), {}, env) 1550 form = SQLFORM.dictform(session[name]) 1551 if form.process().accepted: 1552 session[name].update(form.vars) 1553 if query: 1554 query.db(query).update(**form.vars) 1555 else: 1556 open(filename, 'w').write(repr(session[name])) 1557 return form

1558 1559 @staticmethod

1560 - def factory(*fields, **attributes):

1561 """ 1562 generates a SQLFORM for the given fields. 1563 1564 Internally will build a non-database based data model 1565 to hold the fields. 1566 """ 1567 # Define a table name, this way it can be logical to our CSS. 1568 # And if you switch from using SQLFORM to SQLFORM.factory 1569 # your same css definitions will still apply. 1570 1571 table_name = attributes.get('table_name', 'no_table') 1572 1573 # So it won't interfere with SQLDB.define_table 1574 if 'table_name' in attributes: 1575 del attributes['table_name'] 1576 1577 return SQLFORM(DAL(None).define_table(table_name, *fields), 1578 **attributes)

1579 1580 @staticmethod

1581 - def build_query(fields, keywords):

1582 request = current.request 1583 if isinstance(keywords, (tuple, list)): 1584 keywords = keywords[0] 1585 request.vars.keywords = keywords 1586 key = keywords.strip() 1587 if key and not ' ' in key and not '"' in key and not "'" in key: 1588 SEARCHABLE_TYPES = ('string', 'text', 'list:string') 1589 parts = [field.contains( 1590 key) for field in fields if field.type in SEARCHABLE_TYPES] 1591 else: 1592 parts = None 1593 if parts: 1594 return reduce(lambda a, b: a | b, parts) 1595 else: 1596 return smart_query(fields, key)

1597 1598 @staticmethod

1599 - def search_menu(fields, 1600 search_options=None, 1601 prefix='w2p' 1602 ):

1603 T = current.T 1604 panel_id='%s_query_panel' % prefix 1605 fields_id='%s_query_fields' % prefix 1606 keywords_id='%s_keywords' % prefix 1607 field_id='%s_field' % prefix 1608 value_id='%s_value' % prefix 1609 search_options = search_options or { 1610 'string': ['=', '!=', '<', '>', '<=', '>=', 'starts with', 'contains', 'in', 'not in'], 1611 'text': ['=', '!=', '<', '>', '<=', '>=', 'starts with', 'contains', 'in', 'not in'], 1612 'date': ['=', '!=', '<', '>', '<=', '>='], 1613 'time': ['=', '!=', '<', '>', '<=', '>='], 1614 'datetime': ['=', '!=', '<', '>', '<=', '>='], 1615 'integer': ['=', '!=', '<', '>', '<=', '>=', 'in', 'not in'], 1616 'double': ['=', '!=', '<', '>', '<=', '>='], 1617 'id': ['=', '!=', '<', '>', '<=', '>=', 'in', 'not in'], 1618 'reference': ['=', '!='], 1619 'boolean': ['=', '!=']} 1620 if fields[0]._db._adapter.dbengine == 'google:datastore': 1621 search_options['string'] = ['=', '!=', '<', '>', '<=', '>='] 1622 search_options['text'] = ['=', '!=', '<', '>', '<=', '>='] 1623 search_options['list:string'] = ['contains'] 1624 search_options['list:integer'] = ['contains'] 1625 search_options['list:reference'] = ['contains'] 1626 criteria = [] 1627 selectfields = [] 1628 for field in fields: 1629 name = str(field).replace('.', '-') 1630 # treat ftype 'decimal' as 'double' 1631 # (this fixes problems but needs refactoring! 1632 ftype = field.type.split(' ')[0] 1633 if ftype.startswith('decimal'): ftype = 'double' 1634 elif ftype=='bigint': ftype = 'integer' 1635 elif ftype.startswith('big-'): ftype = ftype[4:] 1636 # end 1637 options = search_options.get(ftype, None) 1638 if options: 1639 label = isinstance( 1640 field.label, str) and T(field.label) or field.label 1641 selectfields.append(OPTION(label, _value=str(field))) 1642 operators = SELECT(*[OPTION(T(option), _value=option) for option in options]) 1643 _id = "%s_%s" % (value_id,name) 1644 if field.type == 'boolean': 1645 value_input = SQLFORM.widgets.boolean.widget(field,field.default,_id=_id) 1646 elif field.type == 'double': 1647 value_input = SQLFORM.widgets.double.widget(field,field.default,_id=_id) 1648 elif field.type == 'time': 1649 value_input = SQLFORM.widgets.time.widget(field,field.default,_id=_id) 1650 elif field.type == 'date': 1651 iso_format = {'_data-w2p_date_format' : '%Y-%m-%d'} 1652 value_input = SQLFORM.widgets.date.widget(field,field.default,_id=_id, **iso_format) 1653 elif field.type == 'datetime': 1654 iso_format = iso_format = {'_data-w2p_datetime_format' : '%Y-%m-%d %H:%M:%S'} 1655 value_input = SQLFORM.widgets.datetime.widget(field,field.default,_id=_id, **iso_format) 1656 elif (field.type.startswith('reference ') or 1657 field.type.startswith('list:reference ')) and \ 1658 hasattr(field.requires,'options'): 1659 value_input = SELECT( 1660 *[OPTION(v, _value=k) 1661 for k,v in field.requires.options()], 1662 **dict(_id=_id)) 1663 elif field.type == 'integer' or \ 1664 field.type.startswith('reference ') or \ 1665 field.type.startswith('list:integer') or \ 1666 field.type.startswith('list:reference '): 1667 value_input = SQLFORM.widgets.integer.widget(field,field.default,_id=_id) 1668 else: 1669 value_input = INPUT( 1670 _type='text', _id=_id, _class=field.type) 1671 1672 new_button = INPUT( 1673 _type="button", _value=T('New'), _class="btn", 1674 _onclick="%s_build_query('new','%s')" % (prefix,field)) 1675 and_button = INPUT( 1676 _type="button", _value=T('And'), _class="btn", 1677 _onclick="%s_build_query('and','%s')" % (prefix, field)) 1678 or_button = INPUT( 1679 _type="button", _value=T('Or'), _class="btn", 1680 _onclick="%s_build_query('or','%s')" % (prefix, field)) 1681 close_button = INPUT( 1682 _type="button", _value=T('Close'), _class="btn", 1683 _onclick="jQuery('#%s').slideUp()" % panel_id) 1684 1685 criteria.append(DIV( 1686 operators, value_input, new_button, 1687 and_button, or_button, close_button, 1688 _id='%s_%s' % (field_id, name), 1689 _class='w2p_query_row hidden', 1690 _style='display:inline')) 1691 1692 criteria.insert(0, SELECT( 1693 _id=fields_id, 1694 _onchange="jQuery('.w2p_query_row').hide();jQuery('#%s_'+jQuery('#%s').val().replace('.','-')).show();" % (field_id,fields_id), 1695 _style='float:left', 1696 *selectfields)) 1697 1698 fadd = SCRIPT(""" 1699 jQuery('#%(fields_id)s input,#%(fields_id)s select').css( 1700 'width','auto'); 1701 jQuery(function(){web2py_ajax_fields('#%(fields_id)s');}); 1702 function %(prefix)s_build_query(aggregator,a) { 1703 var b=a.replace('.','-'); 1704 var option = jQuery('#%(field_id)s_'+b+' select').val(); 1705 var value = jQuery('#%(value_id)s_'+b).val().replace('"','\\\\"'); 1706 var s=a+' '+option+' "'+value+'"'; 1707 var k=jQuery('#%(keywords_id)s'); 1708 var v=k.val(); 1709 if(aggregator=='new') k.val(s); else k.val((v?(v+' '+ aggregator +' '):'')+s); 1710 } 1711 """ % dict( 1712 prefix=prefix,fields_id=fields_id,keywords_id=keywords_id, 1713 field_id=field_id,value_id=value_id 1714 ) 1715 ) 1716 return CAT( 1717 DIV(_id=panel_id, _style="display:none;", *criteria), fadd)

1718 1719 1720 @staticmethod

1721 - def grid(query, 1722 fields=None, 1723 field_id=None, 1724 left=None, 1725 headers={}, 1726 orderby=None, 1727 groupby=None, 1728 searchable=True, 1729 sortable=True, 1730 paginate=20, 1731 deletable=True, 1732 editable=True, 1733 details=True, 1734 selectable=None, 1735 create=True, 1736 csv=True, 1737 links=None, 1738 links_in_grid=True, 1739 upload='<default>', 1740 args=[], 1741 user_signature=True, 1742 maxtextlengths={}, 1743 maxtextlength=20, 1744 onvalidation=None, 1745 onfailure=None, 1746 oncreate=None, 1747 onupdate=None, 1748 ondelete=None, 1749 sorter_icons=(XML('▲'), XML('▼')), 1750 ui = 'web2py', 1751 showbuttontext=True, 1752 _class="web2py_grid", 1753 formname='web2py_grid', 1754 search_widget='default', 1755 ignore_rw = False, 1756 formstyle = 'table3cols', 1757 exportclasses = None, 1758 formargs={}, 1759 createargs={}, 1760 editargs={}, 1761 viewargs={}, 1762 selectable_submit_button='Submit', 1763 buttons_placement = 'right', 1764 links_placement = 'right', 1765 noconfirm=False, 1766 cache_count=None, 1767 client_side_delete=False, 1768 ignore_common_filters=None, 1769 ):

1770 1771 # jQuery UI ThemeRoller classes (empty if ui is disabled) 1772 if ui == 'jquery-ui': 1773 ui = dict(widget='ui-widget', 1774 header='ui-widget-header', 1775 content='ui-widget-content', 1776 default='ui-state-default', 1777 cornerall='ui-corner-all', 1778 cornertop='ui-corner-top', 1779 cornerbottom='ui-corner-bottom', 1780 button='ui-button-text-icon-primary', 1781 buttontext='ui-button-text', 1782 buttonadd='ui-icon ui-icon-plusthick', 1783 buttonback='ui-icon ui-icon-arrowreturnthick-1-w', 1784 buttonexport='ui-icon ui-icon-transferthick-e-w', 1785 buttondelete='ui-icon ui-icon-trash', 1786 buttonedit='ui-icon ui-icon-pencil', 1787 buttontable='ui-icon ui-icon-triangle-1-e', 1788 buttonview='ui-icon ui-icon-zoomin', 1789 ) 1790 elif ui == 'web2py': 1791 ui = dict(widget='', 1792 header='', 1793 content='', 1794 default='', 1795 cornerall='', 1796 cornertop='', 1797 cornerbottom='', 1798 button='button btn', 1799 buttontext='buttontext button', 1800 buttonadd='icon plus icon-plus', 1801 buttonback='icon leftarrow icon-arrow-left', 1802 buttonexport='icon downarrow icon-download', 1803 buttondelete='icon trash icon-trash', 1804 buttonedit='icon pen icon-pencil', 1805 buttontable='icon rightarrow icon-arrow-right', 1806 buttonview='icon magnifier icon-zoom-in', 1807 ) 1808 elif not isinstance(ui, dict): 1809 raise RuntimeError('SQLFORM.grid ui argument must be a dictionary') 1810 1811 db = query._db 1812 T = current.T 1813 request = current.request 1814 session = current.session 1815 response = current.response 1816 logged = session.auth and session.auth.user 1817 wenabled = (not user_signature or logged) and not groupby 1818 create = wenabled and create 1819 editable = wenabled and editable 1820 deletable = wenabled and deletable 1821 details = details and not groupby 1822 rows = None 1823 1824 def fetch_count(dbset): 1825 ##FIXME for google:datastore cache_count is ignored 1826 ## if it's not an integer 1827 if cache_count is None or isinstance(cache_count, tuple): 1828 if groupby: 1829 c = 'count(*)' 1830 nrows = db.executesql( 1831 'select count(*) from (%s) _tmp;' % 1832 dbset._select(c, left=left, cacheable=True, 1833 groupby=groupby, 1834 cache=cache_count)[:-1])[0][0] 1835 elif left: 1836 c = 'count(*)' 1837 nrows = dbset.select(c, left=left, cacheable=True, cache=cache_count).first()[c] 1838 elif dbset._db._adapter.dbengine=='google:datastore': 1839 #if we don't set a limit, this can timeout for a large table 1840 nrows = dbset.db._adapter.count(dbset.query, limit=1000) 1841 else: 1842 nrows = dbset.count(cache=cache_count) 1843 elif isinstance(cache_count, (int, long)): 1844 nrows = cache_count 1845 elif callable(cache_count): 1846 nrows = cache_count(dbset, request.vars) 1847 else: 1848 nrows = 0 1849 return nrows

1850 1851 def url(**b): 1852 b['args'] = args + b.get('args', []) 1853 localvars = request.get_vars.copy() 1854 localvars.update(b.get('vars', {})) 1855 b['vars'] = localvars 1856 b['hash_vars'] = False 1857 b['user_signature'] = user_signature 1858 return URL(**b)

1859 1860 def url2(**b): 1861 b['args'] = request.args + b.get('args', []) 1862 localvars = request.get_vars.copy() 1863 localvars.update(b.get('vars', {})) 1864 b['vars'] = localvars 1865 b['hash_vars'] = False 1866 b['user_signature'] = user_signature 1867 return URL(**b) 1868 1869 referrer = session.get('_web2py_grid_referrer_' + formname, url()) 1870 # if not user_signature every action is accessible 1871 # else forbid access unless 1872 # - url is based url 1873 # - url has valid signature (vars are not signed, only path_info) 1874 # = url does not contain 'create','delete','edit' (readonly) 1875 if user_signature: 1876 if not ( 1877 '/'.join(str(a) for a in args) == '/'.join(request.args) or 1878 URL.verify(request,user_signature=user_signature, 1879 hash_vars=False) or 1880 (request.args(len(args))=='view' and not logged)): 1881 session.flash = T('not authorized') 1882 redirect(referrer) 1883 1884 def gridbutton(buttonclass='buttonadd', buttontext=T('Add'), 1885 buttonurl=url(args=[]), callback=None, 1886 delete=None, trap=True, noconfirm=None): 1887 if showbuttontext: 1888 return A(SPAN(_class=ui.get(buttonclass)), 1889 SPAN(T(buttontext), _title=T(buttontext), 1890 _class=ui.get('buttontext')), 1891 _href=buttonurl, 1892 callback=callback, 1893 delete=delete, 1894 noconfirm=noconfirm, 1895 _class=ui.get('button'), 1896 cid=request.cid) 1897 else: 1898 return A(SPAN(_class=ui.get(buttonclass)), 1899 _href=buttonurl, 1900 callback=callback, 1901 delete=delete, 1902 noconfirm=noconfirm, 1903 _title=T(buttontext), 1904 _class=ui.get('buttontext'), 1905 cid=request.cid) 1906 1907 dbset = db(query,ignore_common_filters=ignore_common_filters) 1908 tablenames = db._adapter.tables(dbset.query) 1909 if left is not None: 1910 if not isinstance(left, (list, tuple)): 1911 left = [left] 1912 for join in left: 1913 tablenames += db._adapter.tables(join) 1914 tables = [db[tablename] for tablename in tablenames] 1915 if fields: 1916 #add missing tablename to virtual fields 1917 for table in tables: 1918 for k,f in table.iteritems(): 1919 if isinstance(f,Field.Virtual): 1920 f.tablename = table._tablename 1921 columns = [f for f in fields if f.tablename in tablenames] 1922 else: 1923 fields = [] 1924 columns = [] 1925 filter1 = lambda f:isinstance(f,Field) 1926 filter2 = lambda f:isinstance(f,Field) and f.readable 1927 for table in tables: 1928 fields += filter(filter1, table) 1929 columns += filter(filter2, table) 1930 for k,f in table.iteritems(): 1931 if not k.startswith('_'): 1932 if isinstance(f,Field.Virtual) and f.readable: 1933 f.tablename = table._tablename 1934 fields.append(f) 1935 columns.append(f) 1936 if not field_id: 1937 if groupby is None: 1938 field_id = tables[0]._id 1939 elif groupby and isinstance(groupby, Field): 1940 field_id = groupby #take the field passed as groupby 1941 elif groupby and isinstance(groupby, Expression): 1942 field_id = groupby.first #take the first groupby field 1943 table = field_id.table 1944 tablename = table._tablename 1945 if not any(str(f)==str(field_id) for f in fields): 1946 fields = [f for f in fields]+[field_id] 1947 if upload == '<default>': 1948 upload = lambda filename: url(args=['download', filename]) 1949 if request.args(-2) == 'download': 1950 stream = response.download(request, db) 1951 raise HTTP(200, stream, **response.headers) 1952 1953 def buttons(edit=False, view=False, record=None): 1954 buttons = DIV(gridbutton('buttonback', 'Back', referrer), 1955 _class='form_header row_buttons %(header)s %(cornertop)s' % ui) 1956 if edit and (not callable(edit) or edit(record)): 1957 args = ['edit', table._tablename, request.args[-1]] 1958 buttons.append(gridbutton('buttonedit', 'Edit', 1959 url(args=args))) 1960 if view: 1961 args = ['view', table._tablename, request.args[-1]] 1962 buttons.append(gridbutton('buttonview', 'View', 1963 url(args=args))) 1964 if record and links: 1965 for link in links: 1966 if isinstance(link, dict): 1967 buttons.append(link['body'](record)) 1968 elif link(record): 1969 buttons.append(link(record)) 1970 return buttons 1971 1972 def linsert(lst, i, x): 1973 """ 1974 a = [1,2] 1975 linsert(a, 1, [0,3]) 1976 a = [1, 0, 3, 2] 1977 """ 1978 lst[i:i] = x 1979 1980 formfooter = DIV( 1981 _class='form_footer row_buttons %(header)s %(cornerbottom)s' % ui) 1982 1983 create_form = update_form = view_form = search_form = None 1984 sqlformargs = dict(formargs) 1985 1986 if create and request.args(-2) == 'new': 1987 table = db[request.args[-1]] 1988 sqlformargs.update(createargs) 1989 create_form = SQLFORM( 1990 table, ignore_rw=ignore_rw, formstyle=formstyle, 1991 _class='web2py_form', 1992 **sqlformargs) 1993 create_form.process(formname=formname, 1994 next=referrer, 1995 onvalidation=onvalidation, 1996 onfailure=onfailure, 1997 onsuccess=oncreate) 1998 res = DIV(buttons(), create_form, formfooter, _class=_class) 1999 res.create_form = create_form 2000 res.update_form = update_form 2001 res.view_form = view_form 2002 res.search_form = search_form 2003 res.rows = None 2004 return res 2005 2006 elif details and request.args(-3) == 'view': 2007 table = db[request.args[-2]] 2008 record = table(request.args[-1]) or redirect(referrer) 2009 sqlformargs.update(viewargs) 2010 view_form = SQLFORM( 2011 table, record, upload=upload, ignore_rw=ignore_rw, 2012 formstyle=formstyle, readonly=True, _class='web2py_form', 2013 **sqlformargs) 2014 res = DIV(buttons(edit=editable, record=record), view_form, 2015 formfooter, _class=_class) 2016 res.create_form = create_form 2017 res.update_form = update_form 2018 res.view_form = view_form 2019 res.search_form = search_form 2020 res.rows = None 2021 return res 2022 elif editable and request.args(-3) == 'edit': 2023 table = db[request.args[-2]] 2024 record = table(request.args[-1]) or redirect(URL('error')) 2025 sqlformargs.update(editargs) 2026 deletable_ = deletable(record) if callable(deletable) else deletable 2027 update_form = SQLFORM( 2028 table, 2029 record, upload=upload, ignore_rw=ignore_rw, 2030 formstyle=formstyle, deletable=deletable_, 2031 _class='web2py_form', 2032 submit_button=T('Submit'), 2033 delete_label=T('Check to delete'), 2034 **sqlformargs) 2035 update_form.process( 2036 formname=formname, 2037 onvalidation=onvalidation, 2038 onfailure=onfailure, 2039 onsuccess=onupdate, 2040 next=referrer) 2041 res = DIV(buttons(view=details, record=record), 2042 update_form, formfooter, _class=_class) 2043 res.create_form = create_form 2044 res.update_form = update_form 2045 res.view_form = view_form 2046 res.search_form = search_form 2047 res.rows = None 2048 return res 2049 elif deletable and request.args(-3) == 'delete': 2050 table = db[request.args[-2]] 2051 if not callable(deletable): 2052 if ondelete: 2053 ondelete(table, request.args[-1]) 2054 db(table[table._id.name] == request.args[-1]).delete() 2055 else: 2056 record = table(request.args[-1]) or redirect(URL('error')) 2057 if deletable(record): 2058 if ondelete: 2059 ondelete(table, request.args[-1]) 2060 record.delete_record() 2061 if request.ajax: 2062 #this means javascript is enabled, so we don't need to do 2063 #a redirect 2064 if not client_side_delete: 2065 #if it's an ajax request and we don't need to reload the 2066 #entire page, let's just inform that there have been no 2067 #exceptions and don't regenerate the grid 2068 raise HTTP(200) 2069 else: 2070 #if it's requested that the grid gets reloaded on delete 2071 #on ajax, the redirect should be on the original location 2072 newloc = request.env.http_web2py_component_location 2073 redirect(newloc, client_side=client_side_delete) 2074 else: 2075 #we need to do a redirect because javascript is not enabled 2076 redirect(referrer, client_side=client_side_delete) 2077 2078 exportManager = dict( 2079 csv_with_hidden_cols=(ExporterCSV, 'CSV (hidden cols)'), 2080 csv=(ExporterCSV, 'CSV'), 2081 xml=(ExporterXML, 'XML'), 2082 html=(ExporterHTML, 'HTML'), 2083 json=(ExporterJSON, 'JSON'), 2084 tsv_with_hidden_cols= 2085 (ExporterTSV, 'TSV (Excel compatible, hidden cols)'), 2086 tsv=(ExporterTSV, 'TSV (Excel compatible)')) 2087 if not exportclasses is None: 2088 """ 2089 remember: allow to set exportclasses=dict(csv=False) to disable the csv format 2090 """ 2091 exportManager.update(exportclasses) 2092 2093 export_type = request.vars._export_type 2094 if export_type: 2095 order = request.vars.order or '' 2096 if sortable: 2097 if order and not order == 'None': 2098 otablename, ofieldname = order.split('~')[-1].split('.', 1) 2099 sort_field = db[otablename][ofieldname] 2100 exception = sort_field.type in ('date', 'datetime', 'time') 2101 if exception: 2102 orderby = (order[:1] == '~' and sort_field) or ~sort_field 2103 else: 2104 orderby = (order[:1] == '~' and ~sort_field) or sort_field 2105 2106 expcolumns = [str(f) for f in columns] 2107 if export_type.endswith('with_hidden_cols'): 2108 expcolumns = [] 2109 for table in tables: 2110 for field in table: 2111 if field.readable and field.tablename in tablenames: 2112 expcolumns.append(field) 2113 2114 if export_type in exportManager and exportManager[export_type]: 2115 if request.vars.keywords: 2116 try: 2117 dbset = dbset(SQLFORM.build_query( 2118 fields, request.vars.get('keywords', ''))) 2119 rows = dbset.select(left=left, orderby=orderby, 2120 cacheable=True, *expcolumns) 2121 except Exception, e: 2122 response.flash = T('Internal Error') 2123 rows = [] 2124 else: 2125 rows = dbset.select(left=left, orderby=orderby, 2126 cacheable=True, *expcolumns) 2127 2128 value = exportManager[export_type] 2129 clazz = value[0] if hasattr(value, '__getitem__') else value 2130 oExp = clazz(rows) 2131 filename = '.'.join(('rows', oExp.file_ext)) 2132 response.headers['Content-Type'] = oExp.content_type 2133 response.headers['Content-Disposition'] = \ 2134 'attachment;filename=' + filename + ';' 2135 raise HTTP(200, oExp.export(), **response.headers) 2136 2137 elif request.vars.records and not isinstance( 2138 request.vars.records, list): 2139 request.vars.records = [request.vars.records] 2140 elif not request.vars.records: 2141 request.vars.records = [] 2142 2143 session['_web2py_grid_referrer_' + formname] = \ 2144 url2(vars=request.get_vars) 2145 console = DIV(_class='web2py_console %(header)s %(cornertop)s' % ui) 2146 error = None 2147 if create: 2148 add = gridbutton( 2149 buttonclass='buttonadd', 2150 buttontext=T('Add'), 2151 buttonurl=url(args=['new', tablename])) 2152 if not searchable: 2153 console.append(add) 2154 else: 2155 add = '' 2156 2157 if searchable: 2158 sfields = reduce(lambda a, b: a + b, 2159 [[f for f in t if f.readable] for t in tables]) 2160 if isinstance(search_widget, dict): 2161 search_widget = search_widget[tablename] 2162 if search_widget == 'default': 2163 prefix = formname == 'web2py_grid' and 'w2p' or 'w2p_%s' % formname 2164 search_menu = SQLFORM.search_menu(sfields, prefix=prefix) 2165 spanel_id = '%s_query_fields' % prefix 2166 sfields_id = '%s_query_panel' % prefix 2167 skeywords_id = '%s_keywords' % prefix 2168 search_widget = lambda sfield, url: CAT(FORM( 2169 INPUT(_name='keywords', _value=request.vars.keywords, 2170 _id=skeywords_id, 2171 _onfocus="jQuery('#%s').change();jQuery('#%s').slideDown();" % (spanel_id, sfields_id)), 2172 INPUT(_type='submit', _value=T('Search'), _class="btn"), 2173 INPUT(_type='submit', _value=T('Clear'), _class="btn", 2174 _onclick="jQuery('#%s').val('');" % skeywords_id), 2175 _method="GET", _action=url), search_menu) 2176 form = search_widget and search_widget(sfields, url()) or '' 2177 console.append(add) 2178 console.append(form) 2179 keywords = request.vars.get('keywords', '') 2180 try: 2181 if callable(searchable): 2182 subquery = searchable(sfields, keywords) 2183 else: 2184 subquery = SQLFORM.build_query(sfields, keywords) 2185 except RuntimeError: 2186 subquery = None 2187 error = T('Invalid query') 2188 else: 2189 subquery = None 2190 2191 if subquery: 2192 dbset = dbset(subquery) 2193 try: 2194 nrows = fetch_count(dbset) 2195 except: 2196 nrows = 0 2197 error = T('Unsupported query') 2198 2199 order = request.vars.order or '' 2200 if sortable: 2201 if order and not order == 'None': 2202 otablename, ofieldname = order.split('~')[-1].split('.', 1) 2203 sort_field = db[otablename][ofieldname] 2204 exception = sort_field.type in ('date', 'datetime', 'time') 2205 if exception: 2206 orderby = (order[:1] == '~' and sort_field) or ~sort_field 2207 else: 2208 orderby = (order[:1] == '~' and ~sort_field) or sort_field 2209 2210 headcols = [] 2211 if selectable: 2212 headcols.append(TH(_class=ui.get('default'))) 2213 2214 ordermatch, marker = orderby, '' 2215 if orderby: 2216 #if orderby is a single column, remember to put the marker 2217 if isinstance(orderby, Expression): 2218 if orderby.first and not orderby.second: 2219 ordermatch, marker = orderby.first, '~' 2220 ordermatch = marker + str(ordermatch) 2221 for field in columns: 2222 if not field.readable: 2223 continue 2224 key = str(field) 2225 header = headers.get(str(field), field.label or key) 2226 if sortable and not isinstance(field, Field.Virtual): 2227 marker = '' 2228 if order: 2229 if key == order: 2230 key, marker = '~' + order, sorter_icons[0] 2231 elif key == order[1:]: 2232 marker = sorter_icons[1] 2233 else: 2234 if key == ordermatch: 2235 key, marker = '~' + ordermatch, sorter_icons[0] 2236 elif key == ordermatch[1:]: 2237 marker = sorter_icons[1] 2238 header = A(header, marker, _href=url(vars=dict( 2239 keywords=request.vars.keywords or '', 2240 order=key)), cid=request.cid) 2241 headcols.append(TH(header, _class=ui.get('default'))) 2242 2243 toadd = [] 2244 if links and links_in_grid: 2245 for link in links: 2246 if isinstance(link, dict): 2247 toadd.append(TH(link['header'], _class=ui.get('default'))) 2248 if links_placement in ['right', 'both']: 2249 headcols.extend(toadd) 2250 if links_placement in ['left', 'both']: 2251 linsert(headcols, 0, toadd) 2252 2253 # Include extra column for buttons if needed. 2254 include_buttons_column = (details or editable or deletable or 2255 (links and links_in_grid and 2256 not all([isinstance(link, dict) for link in links]))) 2257 if include_buttons_column: 2258 if buttons_placement in ['right', 'both']: 2259 headcols.append(TH(_class=ui.get('default',''))) 2260 if buttons_placement in ['left', 'both']: 2261 headcols.insert(0, TH(_class=ui.get('default',''))) 2262 2263 head = TR(*headcols, **dict(_class=ui.get('header'))) 2264 2265 cursor = True 2266 #figure out what page we are one to setup the limitby 2267 if paginate and dbset._db._adapter.dbengine=='google:datastore': 2268 cursor = request.vars.cursor or True 2269 limitby = (0, paginate) 2270 try: page = int(request.vars.page or 1)-1 2271 except ValueError: page = 0 2272 elif paginate and paginate<nrows: 2273 try: page = int(request.vars.page or 1)-1 2274 except ValueError: page = 0 2275 limitby = (paginate*page,paginate*(page+1)) 2276 else: 2277 limitby = None 2278 try: 2279 table_fields = [field for field in fields 2280 if (field.tablename in tablenames and not(isinstance(field,Field.Virtual)))] 2281 if dbset._db._adapter.dbengine=='google:datastore': 2282 rows = dbset.select(left=left,orderby=orderby, 2283 groupby=groupby,limitby=limitby, 2284 reusecursor=cursor, 2285 cacheable=True,*table_fields) 2286 next_cursor = dbset._db.get('_lastcursor', None) 2287 else: 2288 rows = dbset.select(left=left,orderby=orderby, 2289 groupby=groupby,limitby=limitby, 2290 cacheable=True,*table_fields) 2291 except SyntaxError: 2292 rows = None 2293 next_cursor = None 2294 error = T("Query Not Supported") 2295 except Exception, e: 2296 rows = None 2297 next_cursor = None 2298 error = T("Query Not Supported: %s")%e 2299 2300 message = error 2301 if not message and nrows: 2302 if dbset._db._adapter.dbengine=='google:datastore' and nrows>=1000: 2303 message = T('at least %(nrows)s records found') % dict(nrows=nrows) 2304 else: 2305 message = T('%(nrows)s records found') % dict(nrows=nrows) 2306 console.append(DIV(message or T('None'),_class='web2py_counter')) 2307 2308 paginator = UL() 2309 if paginate and dbset._db._adapter.dbengine=='google:datastore': 2310 #this means we may have a large table with an unknown number of rows. 2311 try: 2312 page = int(request.vars.page or 1)-1 2313 except ValueError: 2314 page = 0 2315 paginator.append(LI('page %s'%(page+1))) 2316 if next_cursor: 2317 d = dict(page=page+2, cursor=next_cursor) 2318 if order: d['order']=order 2319 if request.vars.keywords: d['keywords']=request.vars.keywords 2320 paginator.append(LI( 2321 A('next',_href=url(vars=d),cid=request.cid))) 2322 elif paginate and paginate<nrows: 2323 npages, reminder = divmod(nrows, paginate) 2324 if reminder: 2325 npages += 1 2326 try: 2327 page = int(request.vars.page or 1) - 1 2328 except ValueError: 2329 page = 0 2330 2331 def self_link(name, p): 2332 d = dict(page=p + 1) 2333 if order: 2334 d['order'] = order 2335 if request.vars.keywords: 2336 d['keywords'] = request.vars.keywords 2337 return A(name, _href=url(vars=d), cid=request.cid) 2338 NPAGES = 5 # window is 2*NPAGES 2339 if page > NPAGES + 1: 2340 paginator.append(LI(self_link('<<', 0))) 2341 if page > NPAGES: 2342 paginator.append(LI(self_link('<', page - 1))) 2343 pages = range(max(0, page - NPAGES), min(page + NPAGES, npages)) 2344 for p in pages: 2345 if p == page: 2346 paginator.append(LI(A(p + 1, _onclick='return false'), 2347 _class='current')) 2348 else: 2349 paginator.append(LI(self_link(p + 1, p))) 2350 if page < npages - NPAGES: 2351 paginator.append(LI(self_link('>', page + 1))) 2352 if page < npages - NPAGES - 1: 2353 paginator.append(LI(self_link('>>', npages - 1))) 2354 else: 2355 limitby = None 2356 2357 if rows: 2358 htmltable = TABLE(THEAD(head)) 2359 tbody = TBODY() 2360 numrec = 0 2361 for row in rows: 2362 trcols = [] 2363 id = row[field_id] 2364 if selectable: 2365 trcols.append( 2366 INPUT(_type="checkbox", _name="records", _value=id, 2367 value=request.vars.records)) 2368 for field in columns: 2369 if not field.readable: 2370 continue 2371 if field.type == 'blob': 2372 continue 2373 print row 2374 value = row[str(field)] 2375 maxlength = maxtextlengths.get(str(field), maxtextlength) 2376 if field.represent: 2377 try: 2378 value = field.represent(value, row) 2379 except KeyError: 2380 try: 2381 value = field.represent( 2382 value, row[field.tablename]) 2383 except KeyError: 2384 pass 2385 elif field.type == 'boolean': 2386 value = INPUT(_type="checkbox", _checked=value, 2387 _disabled=True) 2388 elif field.type == 'upload': 2389 if value: 2390 if callable(upload): 2391 value = A( 2392 T('file'), _href=upload(value)) 2393 elif upload: 2394 value = A(T('file'), 2395 _href='%s/%s' % (upload, value)) 2396 else: 2397 value = '' 2398 if isinstance(value, str): 2399 value = truncate_string(value, maxlength) 2400 elif not isinstance(value, DIV): 2401 value = field.formatter(value) 2402 trcols.append(TD(value)) 2403 row_buttons = TD(_class='row_buttons',_nowrap=True) 2404 if links and links_in_grid: 2405 toadd = [] 2406 for link in links: 2407 if isinstance(link, dict): 2408 toadd.append(TD(link['body'](row))) 2409 else: 2410 if link(row): 2411 row_buttons.append(link(row)) 2412 if links_placement in ['right', 'both']: 2413 trcols.extend(toadd) 2414 if links_placement in ['left', 'both']: 2415 linsert(trcols, 0, toadd) 2416 2417 if include_buttons_column: 2418 if details and (not callable(details) or details(row)): 2419 row_buttons.append(gridbutton( 2420 'buttonview', 'View', 2421 url(args=['view', tablename, id]))) 2422 if editable and (not callable(editable) or editable(row)): 2423 row_buttons.append(gridbutton( 2424 'buttonedit', 'Edit', 2425 url(args=['edit', tablename, id]))) 2426 if deletable and (not callable(deletable) or deletable(row)): 2427 row_buttons.append(gridbutton( 2428 'buttondelete', 'Delete', 2429 url(args=['delete', tablename, id]), 2430 callback=url(args=['delete', tablename, id]), 2431 noconfirm=noconfirm, 2432 delete='tr')) 2433 if buttons_placement in ['right', 'both']: 2434 trcols.append(row_buttons) 2435 if buttons_placement in ['left', 'both']: 2436 trcols.insert(0, row_buttons) 2437 if numrec % 2 == 0: 2438 classtr = 'even' 2439 else: 2440 classtr = 'odd' 2441 numrec += 1 2442 if id: 2443 rid = id 2444 if callable(rid): # can this ever be callable? 2445 rid = rid(row) 2446 tr = TR(*trcols, **dict( 2447 _id=rid, 2448 _class='%s %s' % (classtr, 'with_id'))) 2449 else: 2450 tr = TR(*trcols, **dict(_class=classtr)) 2451 tbody.append(tr) 2452 htmltable.append(tbody) 2453 htmltable = DIV( 2454 htmltable, _class='web2py_htmltable', 2455 _style='width:100%;overflow-x:auto;-ms-overflow-x:scroll') 2456 if selectable: 2457 if not callable(selectable): 2458 #now expect that selectable and related parameters are iterator (list, tuple, etc) 2459 inputs = [] 2460 for i, submit_info in enumerate(selectable): 2461 submit_text = submit_info[0] 2462 submit_class = submit_info[2] if len(submit_info) > 2 else '' 2463 2464 input_ctrl = INPUT(_type="submit", _name='submit_%d' % i, _value=T(submit_text)) 2465 input_ctrl.add_class(submit_class) 2466 inputs.append(input_ctrl) 2467 else: 2468 inputs = [INPUT(_type="submit", _value=T(selectable_submit_button))] 2469 2470 if formstyle == 'bootstrap': 2471 # add space between buttons 2472 #inputs = sum([[inp, ' '] for inp in inputs], [])[:-1] 2473 htmltable = FORM(htmltable, DIV(_class='form-actions', *inputs)) 2474 else: 2475 htmltable = FORM(htmltable, *inputs) 2476 2477 if htmltable.process(formname=formname).accepted: 2478 htmltable.vars.records = htmltable.vars.records or [] 2479 htmltable.vars.records = htmltable.vars.records if type(htmltable.vars.records) == list else [htmltable.vars.records] 2480 records = [int(r) for r in htmltable.vars.records] 2481 if not callable(selectable): 2482 for i, submit_info in enumerate(selectable): 2483 submit_callback = submit_info[1] 2484 if htmltable.vars.get('submit_%d' % i, False): 2485 submit_callback(records) 2486 break 2487 else: 2488 selectable(records) 2489 redirect(referrer) 2490 else: 2491 htmltable = DIV(T('No records found')) 2492 2493 if csv and nrows: 2494 export_links = [] 2495 for k, v in sorted(exportManager.items()): 2496 if not v: 2497 continue 2498 label = v[1] if hasattr(v, "__getitem__") else k 2499 link = url2(vars=dict( 2500 order=request.vars.order or '', 2501 _export_type=k, 2502 keywords=request.vars.keywords or '')) 2503 export_links.append(A(T(label), _href=link)) 2504 export_menu = \ 2505 DIV(T('Export:'), _class="w2p_export_menu", *export_links) 2506 else: 2507 export_menu = None 2508 2509 res = DIV(console, DIV(htmltable, _class="web2py_table"), 2510 _class='%s %s' % (_class, ui.get('widget'))) 2511 if paginator.components: 2512 res.append( 2513 DIV(paginator, 2514 _class="web2py_paginator %(header)s %(cornerbottom)s" % ui)) 2515 if export_menu: 2516 res.append(export_menu) 2517 res.create_form = create_form 2518 res.update_form = update_form 2519 res.view_form = view_form 2520 res.search_form = search_form 2521 res.rows = rows 2522 return res 2523 2524 @staticmethod

2525 - def smartgrid(table, constraints=None, linked_tables=None, 2526 links=None, links_in_grid=True, 2527 args=None, user_signature=True, 2528 divider='>', breadcrumbs_class='', 2529 **kwargs):

2530 """ 2531 @auth.requires_login() 2532 def index(): 2533 db.define_table('person',Field('name'),format='%(name)s') 2534 db.define_table('dog', 2535 Field('name'),Field('owner',db.person),format='%(name)s') 2536 db.define_table('comment',Field('body'),Field('dog',db.dog)) 2537 if db(db.person).isempty(): 2538 from gluon.contrib.populate import populate 2539 populate(db.person,300) 2540 populate(db.dog,300) 2541 populate(db.comment,1000) 2542 db.commit() 2543 form=SQLFORM.smartgrid(db[request.args(0) or 'person']) #*** 2544 return dict(form=form) 2545 2546 *** builds a complete interface to navigate all tables links 2547 to the request.args(0) 2548 table: pagination, search, view, edit, delete, 2549 children, parent, etc. 2550 2551 constraints is a dict {'table':query} that limits which 2552 records can be accessible 2553 links is a dict like 2554 {'tablename':[lambda row: A(....), ...]} 2555 that will add buttons when table tablename is displayed 2556 linked_tables is a optional list of tablenames of tables 2557 to be linked 2558 """ 2559 request, T = current.request, current.T 2560 if args is None: 2561 args = [] 2562 2563 def url(**b): 2564 b['args'] = request.args[:nargs] + b.get('args', []) 2565 b['hash_vars'] = False 2566 b['user_signature'] = user_signature 2567 return URL(**b)

2568 2569 db = table._db 2570 breadcrumbs = [] 2571 if request.args(len(args)) != table._tablename: 2572 request.args[:] = args + [table._tablename] 2573 if links is None: 2574 links = {} 2575 if constraints is None: 2576 constraints = {} 2577 field = None 2578 name = None 2579 def format(table,row): 2580 if not row: 2581 return T('Unknown') 2582 elif isinstance(table._format,str): 2583 return table._format % row 2584 elif callable(table._format): 2585 return table._format(row) 2586 else: 2587 return '#'+str(row.id) 2588 try: 2589 nargs = len(args) + 1 2590 previous_tablename, previous_fieldname, previous_id = \ 2591 table._tablename, None, None 2592 while len(request.args) > nargs: 2593 key = request.args(nargs) 2594 if '.' in key: 2595 id = request.args(nargs + 1) 2596 tablename, fieldname = key.split('.', 1) 2597 table = db[tablename] 2598 field = table[fieldname] 2599 field.default = id 2600 referee = field.type[10:] 2601 if referee != previous_tablename: 2602 raise HTTP(400) 2603 cond = constraints.get(referee, None) 2604 if cond: 2605 record = db( 2606 db[referee]._id == id)(cond).select().first() 2607 else: 2608 record = db[referee](id) 2609 if previous_id: 2610 if record[previous_fieldname] != int(previous_id): 2611 raise HTTP(400) 2612 previous_tablename, previous_fieldname, previous_id = \ 2613 tablename, fieldname, id 2614 name = format(db[referee],record) 2615 breadcrumbs.append( 2616 LI(A(T(db[referee]._plural), 2617 cid=request.cid, 2618 _href=url()), 2619 SPAN(divider, _class='divider'), 2620 _class='w2p_grid_breadcrumb_elem')) 2621 if kwargs.get('details', True): 2622 breadcrumbs.append( 2623 LI(A(name, cid=request.cid, 2624 _href=url(args=['view', referee, id])), 2625 SPAN(divider, _class='divider'), 2626 _class='w2p_grid_breadcrumb_elem')) 2627 nargs += 2 2628 else: 2629 break 2630 if nargs > len(args) + 1: 2631 query = (field == id) 2632 # cjk 2633 # if isinstance(linked_tables, dict): 2634 # linked_tables = linked_tables.get(table._tablename, []) 2635 if linked_tables is None or referee in linked_tables: 2636 field.represent = lambda id, r=None, referee=referee, rep=field.represent: A(callable(rep) and rep(id) or id, cid=request.cid, _href=url(args=['view', referee, id])) 2637 except (KeyError, ValueError, TypeError): 2638 redirect(URL(args=table._tablename)) 2639 if nargs == len(args) + 1: 2640 query = table._db._adapter.id_query(table) 2641 2642 # filter out data info for displayed table 2643 if table._tablename in constraints: 2644 query = query & constraints[table._tablename] 2645 if isinstance(links, dict): 2646 links = links.get(table._tablename, []) 2647 for key in 'columns,orderby,searchable,sortable,paginate,deletable,editable,details,selectable,create,fields'.split(','): 2648 if isinstance(kwargs.get(key, None), dict): 2649 if table._tablename in kwargs[key]: 2650 kwargs[key] = kwargs[key][table._tablename] 2651 else: 2652 del kwargs[key] 2653 check = {} 2654 id_field_name = table._id.name 2655 for rfield in table._referenced_by: 2656 check[rfield.tablename] = \ 2657 check.get(rfield.tablename, []) + [rfield.name] 2658 if linked_tables is None: 2659 linked_tables = db.tables() 2660 if isinstance(linked_tables, dict): 2661 linked_tables = linked_tables.get(table._tablename,[]) 2662 if linked_tables: 2663 for item in linked_tables: 2664 tb = None 2665 if isinstance(item,Table) and item._tablename in check: 2666 tablename = item._tablename 2667 linked_fieldnames = check[tablename] 2668 td = item 2669 elif isinstance(item,str) and item in check: 2670 tablename = item 2671 linked_fieldnames = check[item] 2672 tb = db[item] 2673 elif isinstance(item,Field) and item.name in check.get(item._tablename,[]): 2674 tablename = item._tablename 2675 linked_fieldnames = [item.name] 2676 tb = item.table 2677 else: 2678 linked_fieldnames = [] 2679 if tb: 2680 multiple_links = len(linked_fieldnames) > 1 2681 for fieldname in linked_fieldnames: 2682 t = T(tb._plural) if not multiple_links else \ 2683 T(tb._plural + '(' + fieldname + ')') 2684 args0 = tablename + '.' + fieldname 2685 links.append( 2686 lambda row, t=t, nargs=nargs, args0=args0: 2687 A(SPAN(t), cid=request.cid, _href=url( 2688 args=[args0, row[id_field_name]]))) 2689 2690 grid = SQLFORM.grid(query, args=request.args[:nargs], links=links, 2691 links_in_grid=links_in_grid, 2692 user_signature=user_signature, **kwargs) 2693 2694 if isinstance(grid, DIV): 2695 header = table._plural 2696 next = grid.create_form or grid.update_form or grid.view_form 2697 breadcrumbs.append(LI( 2698 A(T(header), cid=request.cid,_href=url()), 2699 SPAN(divider, _class='divider') if next else '', 2700 _class='active w2p_grid_breadcrumb_elem')) 2701 if grid.create_form: 2702 header = T('New %(entity)s') % dict(entity=table._singular) 2703 elif grid.update_form: 2704 header = T('Edit %(entity)s') % dict( 2705 entity=format(grid.update_form.table, 2706 grid.update_form.record)) 2707 elif grid.view_form: 2708 header = T('View %(entity)s') % dict( 2709 entity=format(grid.view_form.table, 2710 grid.view_form.record)) 2711 if next: 2712 breadcrumbs.append(LI( 2713 A(T(header), cid=request.cid,_href=url()), 2714 _class='active w2p_grid_breadcrumb_elem')) 2715 grid.insert( 2716 0, DIV(UL(*breadcrumbs, **{'_class': breadcrumbs_class}), 2717 _class='web2py_breadcrumbs')) 2718 return grid 2719

2720 2721 -class SQLTABLE(TABLE):

2722 2723 """ 2724 given a Rows object, as returned by a db().select(), generates 2725 an html table with the rows. 2726 2727 optional arguments: 2728 2729 :param linkto: URL (or lambda to generate a URL) to edit individual records 2730 :param upload: URL to download uploaded files 2731 :param orderby: Add an orderby link to column headers. 2732 :param headers: dictionary of headers to headers redefinions 2733 headers can also be a string to gerenare the headers from data 2734 for now only headers="fieldname:capitalize", 2735 headers="labels" and headers=None are supported 2736 :param truncate: length at which to truncate text in table cells. 2737 Defaults to 16 characters. 2738 :param columns: a list or dict contaning the names of the columns to be shown 2739 Defaults to all 2740 2741 Optional names attributes for passed to the <table> tag 2742 2743 The keys of headers and columns must be of the form "tablename.fieldname" 2744 2745 Simple linkto example:: 2746 2747 rows = db.select(db.sometable.ALL) 2748 table = SQLTABLE(rows, linkto='someurl') 2749 2750 This will link rows[id] to .../sometable/value_of_id 2751 2752 2753 More advanced linkto example:: 2754 2755 def mylink(field, type, ref): 2756 return URL(args=[field]) 2757 2758 rows = db.select(db.sometable.ALL) 2759 table = SQLTABLE(rows, linkto=mylink) 2760 2761 This will link rows[id] to 2762 current_app/current_controlle/current_function/value_of_id 2763 2764 New Implements: 24 June 2011: 2765 ----------------------------- 2766 2767 :param selectid: The id you want to select 2768 :param renderstyle: Boolean render the style with the table 2769 2770 :param extracolumns = [{'label':A('Extra',_href='#'), 2771 'class': '', #class name of the header 2772 'width':'', #width in pixels or % 2773 'content':lambda row, rc: A('Edit',_href='edit/%s'%row.id), 2774 'selected': False #agregate class selected to this column 2775 }] 2776 2777 2778 :param headers = {'table.id':{'label':'Id', 2779 'class':'', #class name of the header 2780 'width':'', #width in pixels or % 2781 'truncate': 16, #truncate the content to... 2782 'selected': False #agregate class selected to this column 2783 }, 2784 'table.myfield':{'label':'My field', 2785 'class':'', #class name of the header 2786 'width':'', #width in pixels or % 2787 'truncate': 16, #truncate the content to... 2788 'selected': False #agregate class selected to this column 2789 }, 2790 } 2791 2792 table = SQLTABLE(rows, headers=headers, extracolumns=extracolumns) 2793 `< 2794 2795 """ 2796

2797 - def __init__( 2798 self, 2799 sqlrows, 2800 linkto=None, 2801 upload=None, 2802 orderby=None, 2803 headers={}, 2804 truncate=16, 2805 columns=None, 2806 th_link='', 2807 extracolumns=None, 2808 selectid=None, 2809 renderstyle=False, 2810 cid=None, 2811 **attributes 2812 ):

2813 2814 TABLE.__init__(self, **attributes) 2815 2816 self.components = [] 2817 self.attributes = attributes 2818 self.sqlrows = sqlrows 2819 (components, row) = (self.components, []) 2820 if not sqlrows: 2821 return 2822 if not columns: 2823 columns = sqlrows.colnames 2824 if headers == 'fieldname:capitalize': 2825 headers = {} 2826 for c in columns: 2827 headers[c] = c.split('.')[-1].replace('_', ' ').title() 2828 elif headers == 'labels': 2829 headers = {} 2830 for c in columns: 2831 (t, f) = c.split('.') 2832 field = sqlrows.db[t][f] 2833 headers[c] = field.label 2834 if headers is None: 2835 headers = {} 2836 else: 2837 for c in columns: # new implement dict 2838 if isinstance(headers.get(c, c), dict): 2839 coldict = headers.get(c, c) 2840 attrcol = dict() 2841 if coldict['width'] != "": 2842 attrcol.update(_width=coldict['width']) 2843 if coldict['class'] != "": 2844 attrcol.update(_class=coldict['class']) 2845 row.append(TH(coldict['label'], **attrcol)) 2846 elif orderby: 2847 row.append(TH(A(headers.get(c, c), 2848 _href=th_link + '?orderby=' + c, cid=cid))) 2849 else: 2850 row.append(TH(headers.get(c, c))) 2851 2852 if extracolumns: # new implement dict 2853 for c in extracolumns: 2854 attrcol = dict() 2855 if c['width'] != "": 2856 attrcol.update(_width=c['width']) 2857 if c['class'] != "": 2858 attrcol.update(_class=c['class']) 2859 row.append(TH(c['label'], **attrcol)) 2860 2861 components.append(THEAD(TR(*row))) 2862 2863 tbody = [] 2864 for (rc, record) in enumerate(sqlrows): 2865 row = [] 2866 if rc % 2 == 0: 2867 _class = 'even' 2868 else: 2869 _class = 'odd' 2870 2871 if not selectid is None: # new implement 2872 if record.get('id') == selectid: 2873 _class += ' rowselected' 2874 2875 for colname in columns: 2876 if not table_field.match(colname): 2877 if "_extra" in record and colname in record._extra: 2878 r = record._extra[colname] 2879 row.append(TD(r)) 2880 continue 2881 else: 2882 raise KeyError( 2883 "Column %s not found (SQLTABLE)" % colname) 2884 (tablename, fieldname) = colname.split('.') 2885 try: 2886 field = sqlrows.db[tablename][fieldname] 2887 except (KeyError, AttributeError): 2888 field = None 2889 if tablename in record \ 2890 and isinstance(record, Row) \ 2891 and isinstance(record[tablename], Row): 2892 r = record[tablename][fieldname] 2893 elif fieldname in record: 2894 r = record[fieldname] 2895 else: 2896 raise SyntaxError('something wrong in Rows object') 2897 r_old = r 2898 if not field or isinstance(field, (Field.Virtual, Field.Lazy)): 2899 pass 2900 elif linkto and field.type == 'id': 2901 try: 2902 href = linkto(r, 'table', tablename) 2903 except TypeError: 2904 href = '%s/%s/%s' % (linkto, tablename, r_old) 2905 r = A(r, _href=href) 2906 elif isinstance(field.type, str) and field.type.startswith('reference'): 2907 if linkto: 2908 ref = field.type[10:] 2909 try: 2910 href = linkto(r, 'reference', ref) 2911 except TypeError: 2912 href = '%s/%s/%s' % (linkto, ref, r_old) 2913 if ref.find('.') >= 0: 2914 tref, fref = ref.split('.') 2915 if hasattr(sqlrows.db[tref], '_primarykey'): 2916 href = '%s/%s?%s' % (linkto, tref, urllib.urlencode({fref: r})) 2917 r = A(represent(field, r, record), _href=str(href)) 2918 elif field.represent: 2919 r = represent(field, r, record) 2920 elif linkto and hasattr(field._table, '_primarykey')\ 2921 and fieldname in field._table._primarykey: 2922 # have to test this with multi-key tables 2923 key = urllib.urlencode(dict([ 2924 ((tablename in record 2925 and isinstance(record, Row) 2926 and isinstance(record[tablename], Row)) and 2927 (k, record[tablename][k])) or (k, record[k]) 2928 for k in field._table._primarykey])) 2929 r = A(r, _href='%s/%s?%s' % (linkto, tablename, key)) 2930 elif isinstance(field.type, str) and field.type.startswith('list:'): 2931 r = represent(field, r or [], record) 2932 elif field.represent: 2933 r = represent(field, r, record) 2934 elif field.type == 'blob' and r: 2935 r = 'DATA' 2936 elif field.type == 'upload': 2937 if upload and r: 2938 r = A(current.T('file'), _href='%s/%s' % (upload, r)) 2939 elif r: 2940 r = current.T('file') 2941 else: 2942 r = '' 2943 elif field.type in ['string', 'text']: 2944 r = str(field.formatter(r)) 2945 if headers != {}: # new implement dict 2946 if isinstance(headers[colname], dict): 2947 if isinstance(headers[colname]['truncate'], int): 2948 r = truncate_string( 2949 r, headers[colname]['truncate']) 2950 elif not truncate is None: 2951 r = truncate_string(r, truncate) 2952 attrcol = dict() # new implement dict 2953 if headers != {}: 2954 if isinstance(headers[colname], dict): 2955 colclass = headers[colname]['class'] 2956 if headers[colname]['selected']: 2957 colclass = str(headers[colname] 2958 ['class'] + " colselected").strip() 2959 if colclass != "": 2960 attrcol.update(_class=colclass) 2961 2962 row.append(TD(r, **attrcol)) 2963 2964 if extracolumns: # new implement dict 2965 for c in extracolumns: 2966 attrcol = dict() 2967 colclass = c['class'] 2968 if c['selected']: 2969 colclass = str(c['class'] + " colselected").strip() 2970 if colclass != "": 2971 attrcol.update(_class=colclass) 2972 contentfunc = c['content'] 2973 row.append(TD(contentfunc(record, rc), **attrcol)) 2974 2975 tbody.append(TR(_class=_class, *row)) 2976 2977 if renderstyle: 2978 components.append(STYLE(self.style())) 2979 2980 components.append(TBODY(*tbody))

2981

2982 - def style(self):

2983 2984 css = ''' 2985 table tbody tr.odd { 2986 background-color: #DFD; 2987 } 2988 table tbody tr.even { 2989 background-color: #EFE; 2990 } 2991 table tbody tr.rowselected { 2992 background-color: #FDD; 2993 } 2994 table tbody tr td.colselected { 2995 background-color: #FDD; 2996 } 2997 table tbody tr:hover { 2998 background: #DDF; 2999 } 3000 ''' 3001 3002 return css

3003 3004 form_factory = SQLFORM.factory # for backward compatibility, deprecated

3005 3006 3007 -class ExportClass(object):

3008 label = None 3009 file_ext = None 3010 content_type = None 3011

3012 - def __init__(self, rows):

3013 self.rows = rows

3014

3015 - def represented(self):

3016 def none_exception(value): 3017 """ 3018 returns a cleaned up value that can be used for csv export: 3019 - unicode text is encoded as such 3020 - None values are replaced with the given representation (default <NULL>) 3021 """ 3022 if value is None: 3023 return '<NULL>' 3024 elif isinstance(value, unicode): 3025 return value.encode('utf8') 3026 elif isinstance(value, Reference): 3027 return int(value) 3028 elif hasattr(value, 'isoformat'): 3029 return value.isoformat()[:19].replace('T', ' ') 3030 elif isinstance(value, (list, tuple)): # for type='list:..' 3031 return bar_encode(value) 3032 return value

3033 3034 represented = [] 3035 for record in self.rows: 3036 row = [] 3037 for col in self.rows.colnames: 3038 if not REGEX_TABLE_DOT_FIELD.match(col): 3039 row.append(record._extra[col]) 3040 else: 3041 (t, f) = col.split('.') 3042 field = self.rows.db[t][f] 3043 if isinstance(record.get(t, None), (Row, dict)): 3044 value = record[t][f] 3045 else: 3046 value = record[f] 3047 if field.type == 'blob' and not value is None: 3048 value = '' 3049 elif field.represent: 3050 value = field.represent(value, record) 3051 row.append(none_exception(value)) 3052 3053 represented.append(row) 3054 return represented

3055

3056 - def export(self):

3057 raise NotImplementedError

3058

3059 3060 -class ExporterTSV(ExportClass):

3061 3062 label = 'TSV' 3063 file_ext = "csv" 3064 content_type = "text/tab-separated-values" 3065

3066 - def __init__(self, rows):

3067 ExportClass.__init__(self, rows)

3068

3069 - def export(self):

3070 3071 out = cStringIO.StringIO() 3072 final = cStringIO.StringIO() 3073 import csv 3074 writer = csv.writer(out, delimiter='\t') 3075 if self.rows: 3076 import codecs 3077 final.write(codecs.BOM_UTF16) 3078 writer.writerow( 3079 [unicode(col).encode("utf8") for col in self.rows.colnames]) 3080 data = out.getvalue().decode("utf8") 3081 data = data.encode("utf-16") 3082 data = data[2:] 3083 final.write(data) 3084 out.truncate(0) 3085 records = self.represented() 3086 for row in records: 3087 writer.writerow( 3088 [str(col).decode('utf8').encode("utf-8") for col in row]) 3089 data = out.getvalue().decode("utf8") 3090 data = data.encode("utf-16") 3091 data = data[2:] 3092 final.write(data) 3093 out.truncate(0) 3094 return str(final.getvalue())

3095

3096 3097 -class ExporterCSV(ExportClass):

3098 label = 'CSV' 3099 file_ext = "csv" 3100 content_type = "text/csv" 3101

3102 - def __init__(self, rows):

3103 ExportClass.__init__(self, rows)

3104

3105 - def export(self):

3106 if self.rows: 3107 return self.rows.as_csv() 3108 else: 3109 return ''

3110

3111 -class ExporterHTML(ExportClass):

3112 label = 'HTML' 3113 file_ext = "html" 3114 content_type = "text/html" 3115

3116 - def __init__(self, rows):

3117 ExportClass.__init__(self, rows)

3118

3119 - def export(self):

3120 return '<html>\n<head>\n<meta http-equiv="content-type" content="text/html; charset=UTF-8" />\n</head>\n<body>\n%s\n</body>\n</html>' % (self.rows.xml() or '')

3121

3122 -class ExporterXML(ExportClass):

3123 label = 'XML' 3124 file_ext = "xml" 3125 content_type = "text/xml" 3126

3127 - def __init__(self, rows):

3128 ExportClass.__init__(self, rows)

3129

3130 - def export(self):

3131 if self.rows: 3132 return self.rows.as_xml() 3133 else: 3134 return '<rows></rows>'

3135

3136 -class ExporterJSON(ExportClass):

3137 label = 'JSON' 3138 file_ext = "json" 3139 content_type = "application/json" 3140

3141 - def __init__(self, rows):

3142 ExportClass.__init__(self, rows)

3143

3144 - def export(self):

3145 if self.rows: 3146 return self.rows.as_json() 3147 else: 3148 return 'null'

3149

Source Code for Module gluon.sqlhtml