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, COL, COLGROUP 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, Expression, SQLCustomType 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 add_class(a,b):

49 return a+' '+b if a else b

50

51 -def represent(field, value, record):

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

64

65 66 -def safe_int(x):

67 try: 68 return int(x) 69 except ValueError: 70 return 0

71

72 73 -def safe_float(x):

74 try: 75 return float(x) 76 except ValueError: 77 return 0

78

79 80 -def show_if(cond):

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

99

100 101 -class FormWidget(object):

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

110 - def _attributes(cls, field, 111 widget_attributes, **attributes):

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

134 135 @classmethod

152

153 154 -class StringWidget(FormWidget):

155 _class = 'string' 156 157 @classmethod

172

173 174 -class IntegerWidget(StringWidget):

175 _class = 'integer'

176

177 178 -class DoubleWidget(StringWidget):

179 _class = 'double'

180

181 182 -class DecimalWidget(StringWidget):

183 _class = 'decimal'

184

185 186 -class TimeWidget(StringWidget):

187 _class = 'time'

188

189 190 -class DateWidget(StringWidget):

191 _class = 'date'

192

193 -class DatetimeWidget(StringWidget):

194 _class = 'datetime'

195

196 -class TextWidget(FormWidget):

197 _class = 'text' 198 199 @classmethod

210

211 -class JSONWidget(FormWidget):

212 _class = 'json' 213 214 @classmethod

227

228 -class BooleanWidget(FormWidget):

229 _class = 'boolean' 230 231 @classmethod

243

244 245 -class OptionsWidget(FormWidget):

246 247 @staticmethod

248 - def has_options(field):

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

257 258 @classmethod

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

279

280 281 -class ListWidget(StringWidget):

282 283 @classmethod

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

303

304 305 -class MultipleOptionsWidget(OptionsWidget):

306 307 @classmethod

321

322 323 -class RadioWidget(OptionsWidget):

324 325 @classmethod

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

385

386 387 -class CheckboxesWidget(OptionsWidget):

388 389 @classmethod

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

459

460 461 -class PasswordWidget(FormWidget):

462 _class = 'password' 463 464 DEFAULT_PASSWORD_DISPLAY = 8 * ('*') 465 466 @classmethod

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

492

493 494 -class UploadWidget(FormWidget):

495 _class = 'upload' 496 497 DEFAULT_WIDTH = '150px' 498 ID_DELETE_SUFFIX = '__delete' 499 GENERIC_DESCRIPTION = 'file ## download' 500 DELETE_FILE = 'delete' 501 502 @classmethod

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

553 554 @classmethod

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

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

580 581 @staticmethod

582 - def is_image(value):

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

596

597 598 -class AutocompleteWidget(object):

599 _class = 'string' 600

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

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

631

632 - def callback(self):

633 if self.keyword in self.request.vars: 634 field = self.fields[0] 635 if is_gae: 636 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)) 637 else: 638 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)) 639 if rows: 640 if self.is_reference: 641 id_field = self.fields[1] 642 if self.help_fields: 643 options = [OPTION( 644 self.help_string % dict([(h.name, s[h.name]) for h in self.fields[:1] + self.help_fields]), 645 _value=s[id_field.name], _selected=(k == 0)) for k, s in enumerate(rows)] 646 else: 647 options = [OPTION( 648 s[field.name], _value=s[id_field.name], 649 _selected=(k == 0)) for k, s in enumerate(rows)] 650 raise HTTP( 651 200, SELECT(_id=self.keyword, _class='autocomplete', 652 _size=len(rows), _multiple=(len(rows) == 1), 653 *options).xml()) 654 else: 655 raise HTTP( 656 200, SELECT(_id=self.keyword, _class='autocomplete', 657 _size=len(rows), _multiple=(len(rows) == 1), 658 *[OPTION(s[field.name], 659 _selected=(k == 0)) 660 for k, s in enumerate(rows)]).xml()) 661 else: 662 raise HTTP(200, '')

663

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

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

705

706 707 -def formstyle_table3cols(form, fields):

708 ''' 3 column table - default ''' 709 table = TABLE() 710 for id, label, controls, help in fields: 711 _help = TD(help, _class='w2p_fc') 712 _controls = TD(controls, _class='w2p_fw') 713 _label = TD(label, _class='w2p_fl') 714 table.append(TR(_label, _controls, _help, _id=id)) 715 return table

716

717 718 -def formstyle_table2cols(form, fields):

719 ''' 2 column table ''' 720 table = TABLE() 721 for id, label, controls, help in fields: 722 _help = TD(help, _class='w2p_fc', _width='50%') 723 _controls = TD(controls, _class='w2p_fw', _colspan='2') 724 _label = TD(label, _class='w2p_fl', _width='50%') 725 table.append(TR(_label, _help, _id=id + '1', _class='w2p_even even')) 726 table.append(TR(_controls, _id=id + '2', _class='w2p_even odd')) 727 return table

728

729 730 -def formstyle_divs(form, fields):

731 ''' divs only ''' 732 table = FIELDSET() 733 for id, label, controls, help in fields: 734 _help = DIV(help, _class='w2p_fc') 735 _controls = DIV(controls, _class='w2p_fw') 736 _label = DIV(label, _class='w2p_fl') 737 table.append(DIV(_label, _controls, _help, _id=id)) 738 return table

739

740 741 -def formstyle_inline(form, fields):

742 ''' divs only ''' 743 if len(fields) != 2: 744 raise RuntimeError("Not possible") 745 id, label, controls, help = fields[0] 746 submit_button = fields[1][2] 747 return CAT(DIV(controls, _style='display:inline'), 748 submit_button)

749

750 751 -def formstyle_ul(form, fields):

752 ''' unordered list ''' 753 table = UL() 754 for id, label, controls, help in fields: 755 _help = DIV(help, _class='w2p_fc') 756 _controls = DIV(controls, _class='w2p_fw') 757 _label = DIV(label, _class='w2p_fl') 758 table.append(LI(_label, _controls, _help, _id=id)) 759 return table

760

761 762 -def formstyle_bootstrap(form, fields):

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

805

806 -def formstyle_bootstrap3(form, fields):

807 ''' bootstrap 3 format form layout ''' 808 form.add_class('form-horizontal') 809 parent = FIELDSET() 810 for id, label, controls, help in fields: 811 # wrappers 812 _help = SPAN(help, _class='help-block') 813 # embed _help into _controls 814 _controls = DIV(controls, _help, _class='col-lg-4') 815 # submit unflag by default 816 _submit = False 817 if isinstance(controls, INPUT): 818 controls.add_class('col-lg-4') 819 820 if controls['_type'] == 'submit': 821 # flag submit button 822 _submit = True 823 controls['_class'] = 'btn btn-primary' 824 if controls['_type'] == 'button': 825 controls['_class'] = 'btn btn-default' 826 elif controls['_type'] == 'file': 827 controls['_class'] = 'input-file' 828 elif controls['_type'] == 'text': 829 controls['_class'] = 'form-control' 830 elif controls['_type'] == 'password': 831 controls['_class'] = 'form-control' 832 elif controls['_type'] == 'checkbox': 833 controls['_class'] = 'checkbox' 834 835 836 837 # For password fields, which are wrapped in a CAT object. 838 if isinstance(controls, CAT) and isinstance(controls[0], INPUT): 839 controls[0].add_class('col-lg-2') 840 841 if isinstance(controls, SELECT): 842 controls.add_class('form-control') 843 844 if isinstance(controls, TEXTAREA): 845 controls.add_class('form-control') 846 847 if isinstance(label, LABEL): 848 label['_class'] = 'col-lg-2 control-label' 849 850 851 if _submit: 852 # submit button has unwrapped label and controls, different class 853 parent.append(DIV(label, DIV(controls,_class="col-lg-4 col-lg-offset-2"), _class='form-group', _id=id)) 854 # unflag submit (possible side effect) 855 _submit = False 856 else: 857 # unwrapped label 858 parent.append(DIV(label, _controls, _class='form-group', _id=id)) 859 return parent

860

861 862 -class SQLFORM(FORM):

863 864 """ 865 SQLFORM is used to map a table (and a current record) into an HTML form 866 867 given a SQLTable stored in db.table 868 869 generates an insert form:: 870 871 SQLFORM(db.table) 872 873 generates an update form:: 874 875 record=db.table[some_id] 876 SQLFORM(db.table, record) 877 878 generates an update with a delete button:: 879 880 SQLFORM(db.table, record, deletable=True) 881 882 if record is an int:: 883 884 record=db.table[record] 885 886 optional arguments: 887 888 :param fields: a list of fields that should be placed in the form, 889 default is all. 890 :param labels: a dictionary with labels for each field, keys are the field 891 names. 892 :param col3: a dictionary with content for an optional third column 893 (right of each field). keys are field names. 894 :param linkto: the URL of a controller/function to access referencedby 895 records 896 see controller appadmin.py for examples 897 :param upload: the URL of a controller/function to download an uploaded file 898 see controller appadmin.py for examples 899 900 any named optional attribute is passed to the <form> tag 901 for example _class, _id, _style, _action, _method, etc. 902 903 """ 904 905 # usability improvements proposal by fpp - 4 May 2008 : 906 # - correct labels (for points to field id, not field name) 907 # - add label for delete checkbox 908 # - add translatable label for record ID 909 # - add third column to right of fields, populated from the col3 dict 910 911 widgets = Storage(dict( 912 string=StringWidget, 913 text=TextWidget, 914 json=JSONWidget, 915 password=PasswordWidget, 916 integer=IntegerWidget, 917 double=DoubleWidget, 918 decimal=DecimalWidget, 919 time=TimeWidget, 920 date=DateWidget, 921 datetime=DatetimeWidget, 922 upload=UploadWidget, 923 boolean=BooleanWidget, 924 blob=None, 925 options=OptionsWidget, 926 multiple=MultipleOptionsWidget, 927 radio=RadioWidget, 928 checkboxes=CheckboxesWidget, 929 autocomplete=AutocompleteWidget, 930 list=ListWidget, 931 )) 932 933 formstyles = Storage(dict( 934 table3cols=formstyle_table3cols, 935 table2cols=formstyle_table2cols, 936 divs=formstyle_divs, 937 ul=formstyle_ul, 938 bootstrap=formstyle_bootstrap, 939 bootstrap3=formstyle_bootstrap3, 940 inline=formstyle_inline, 941 )) 942 943 FIELDNAME_REQUEST_DELETE = 'delete_this_record' 944 FIELDKEY_DELETE_RECORD = 'delete_record' 945 ID_LABEL_SUFFIX = '__label' 946 ID_ROW_SUFFIX = '__row' 947

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

949 if not status and self.record and self.errors: 950 ### if there are errors in update mode 951 # and some errors refers to an already uploaded file 952 # delete error if 953 # - user not trying to upload a new file 954 # - there is existing file and user is not trying to delete it 955 # this is because removing the file may not pass validation 956 for key in self.errors.keys(): 957 if key in self.table \ 958 and self.table[key].type == 'upload' \ 959 and request_vars.get(key, None) in (None, '') \ 960 and self.record[key] \ 961 and not key + UploadWidget.ID_DELETE_SUFFIX in request_vars: 962 del self.errors[key] 963 if not self.errors: 964 status = True 965 return status

966

967 - def __init__( 968 self, 969 table, 970 record=None, 971 deletable=False, 972 linkto=None, 973 upload=None, 974 fields=None, 975 labels=None, 976 col3={}, 977 submit_button='Submit', 978 delete_label='Check to delete', 979 showid=True, 980 readonly=False, 981 comments=True, 982 keepopts=[], 983 ignore_rw=False, 984 record_id=None, 985 formstyle='table3cols', 986 buttons=['submit'], 987 separator=': ', 988 **attributes 989 ):

990 """ 991 SQLFORM(db.table, 992 record=None, 993 fields=['name'], 994 labels={'name': 'Your name'}, 995 linkto=URL(f='table/db/') 996 """ 997 T = current.T 998 999 self.ignore_rw = ignore_rw 1000 self.formstyle = formstyle 1001 self.readonly = readonly 1002 # Default dbio setting 1003 self.detect_record_change = None 1004 1005 nbsp = XML(' ') # Firefox2 does not display fields with blanks 1006 FORM.__init__(self, *[], **attributes) 1007 ofields = fields 1008 keyed = hasattr(table, '_primarykey') # for backward compatibility 1009 1010 # if no fields are provided, build it from the provided table 1011 # will only use writable or readable fields, unless forced to ignore 1012 if fields is None: 1013 fields = [f.name for f in table if 1014 (ignore_rw or f.writable or f.readable) and 1015 (readonly or not f.compute)] 1016 self.fields = fields 1017 1018 # make sure we have an id 1019 if self.fields[0] != table.fields[0] and \ 1020 isinstance(table, Table) and not keyed: 1021 self.fields.insert(0, table.fields[0]) 1022 1023 self.table = table 1024 1025 # try to retrieve the indicated record using its id 1026 # otherwise ignore it 1027 if record and isinstance(record, (int, long, str, unicode)): 1028 if not str(record).isdigit(): 1029 raise HTTP(404, "Object not found") 1030 record = table._db(table._id == record).select().first() 1031 if not record: 1032 raise HTTP(404, "Object not found") 1033 self.record = record 1034 1035 self.record_id = record_id 1036 if keyed: 1037 self.record_id = dict([(k, record and str(record[k]) or None) 1038 for k in table._primarykey]) 1039 self.field_parent = {} 1040 xfields = [] 1041 self.fields = fields 1042 self.custom = Storage() 1043 self.custom.dspval = Storage() 1044 self.custom.inpval = Storage() 1045 self.custom.label = Storage() 1046 self.custom.comment = Storage() 1047 self.custom.widget = Storage() 1048 self.custom.linkto = Storage() 1049 1050 # default id field name 1051 if not keyed: 1052 self.id_field_name = table._id.name 1053 else: 1054 self.id_field_name = table._primarykey[0] # only works if one key 1055 1056 sep = separator or '' 1057 1058 for fieldname in self.fields: 1059 if fieldname.find('.') >= 0: 1060 continue 1061 1062 field = self.table[fieldname] 1063 comment = None 1064 1065 if comments: 1066 comment = col3.get(fieldname, field.comment) 1067 if comment is None: 1068 comment = '' 1069 self.custom.comment[fieldname] = comment 1070 1071 if not labels is None and fieldname in labels: 1072 label = labels[fieldname] 1073 else: 1074 label = field.label 1075 self.custom.label[fieldname] = label 1076 1077 field_id = '%s_%s' % (table._tablename, fieldname) 1078 1079 label = LABEL(label, label and sep, _for=field_id, 1080 _id=field_id + SQLFORM.ID_LABEL_SUFFIX) 1081 1082 row_id = field_id + SQLFORM.ID_ROW_SUFFIX 1083 if field.type == 'id': 1084 self.custom.dspval.id = nbsp 1085 self.custom.inpval.id = '' 1086 widget = '' 1087 1088 # store the id field name (for legacy databases) 1089 self.id_field_name = field.name 1090 1091 if record: 1092 if showid and field.name in record and field.readable: 1093 v = record[field.name] 1094 widget = SPAN(v, _id=field_id) 1095 self.custom.dspval.id = str(v) 1096 xfields.append((row_id, label, widget, comment)) 1097 self.record_id = str(record[field.name]) 1098 self.custom.widget.id = widget 1099 continue 1100 1101 if readonly and not ignore_rw and not field.readable: 1102 continue 1103 1104 if record: 1105 default = record[fieldname] 1106 else: 1107 default = field.default 1108 if isinstance(default, CALLABLETYPES): 1109 default = default() 1110 1111 cond = readonly or \ 1112 (not ignore_rw and not field.writable and field.readable) 1113 1114 if default is not None and not cond: 1115 default = field.formatter(default) 1116 dspval = default 1117 inpval = default 1118 1119 if cond: 1120 1121 # ## if field.represent is available else 1122 # ## ignore blob and preview uploaded images 1123 # ## format everything else 1124 1125 if field.represent: 1126 inp = represent(field, default, record) 1127 elif field.type in ['blob']: 1128 continue 1129 elif field.type == 'upload': 1130 inp = UploadWidget.represent(field, default, upload) 1131 elif field.type == 'boolean': 1132 inp = self.widgets.boolean.widget( 1133 field, default, _disabled=True) 1134 else: 1135 inp = field.formatter(default) 1136 elif field.type == 'upload': 1137 if field.widget: 1138 inp = field.widget(field, default, upload) 1139 else: 1140 inp = self.widgets.upload.widget(field, default, upload) 1141 elif field.widget: 1142 inp = field.widget(field, default) 1143 elif field.type == 'boolean': 1144 inp = self.widgets.boolean.widget(field, default) 1145 if default: 1146 inpval = 'checked' 1147 else: 1148 inpval = '' 1149 elif OptionsWidget.has_options(field): 1150 if not field.requires.multiple: 1151 inp = self.widgets.options.widget(field, default) 1152 else: 1153 inp = self.widgets.multiple.widget(field, default) 1154 if fieldname in keepopts: 1155 inpval = TAG[''](*inp.components) 1156 elif field.type.startswith('list:'): 1157 inp = self.widgets.list.widget(field, default) 1158 elif field.type == 'text': 1159 inp = self.widgets.text.widget(field, default) 1160 elif field.type == 'password': 1161 inp = self.widgets.password.widget(field, default) 1162 if self.record: 1163 dspval = PasswordWidget.DEFAULT_PASSWORD_DISPLAY 1164 else: 1165 dspval = '' 1166 elif field.type == 'blob': 1167 continue 1168 else: 1169 field_type = widget_class.match(str(field.type)).group() 1170 field_type = field_type in self.widgets and field_type or 'string' 1171 inp = self.widgets[field_type].widget(field, default) 1172 1173 xfields.append((row_id, label, inp, comment)) 1174 self.custom.dspval[fieldname] = dspval if (dspval is not None) else nbsp 1175 self.custom.inpval[ 1176 fieldname] = inpval if not inpval is None else '' 1177 self.custom.widget[fieldname] = inp 1178 1179 # if a record is provided and found, as is linkto 1180 # build a link 1181 if record and linkto: 1182 db = linkto.split('/')[-1] 1183 for rfld in table._referenced_by: 1184 if keyed: 1185 query = urllib.quote('%s.%s==%s' % ( 1186 db, rfld, record[rfld.type[10:].split('.')[1]])) 1187 else: 1188 query = urllib.quote( 1189 '%s.%s==%s' % (db, rfld, record[self.id_field_name])) 1190 lname = olname = '%s.%s' % (rfld.tablename, rfld.name) 1191 if ofields and not olname in ofields: 1192 continue 1193 if labels and lname in labels: 1194 lname = labels[lname] 1195 widget = A(lname, 1196 _class='reference', 1197 _href='%s/%s?query=%s' % (linkto, rfld.tablename, query)) 1198 xfields.append( 1199 (olname.replace('.', '__') + SQLFORM.ID_ROW_SUFFIX, 1200 '', widget, col3.get(olname, ''))) 1201 self.custom.linkto[olname.replace('.', '__')] = widget 1202 # </block> 1203 1204 # when deletable, add delete? checkbox 1205 self.custom.delete = self.custom.deletable = '' 1206 if record and deletable: 1207 #add secondary css class for cascade delete warning 1208 css = 'delete' 1209 for f in self.table.fields: 1210 on_del = self.table[f].ondelete 1211 if isinstance(on_del,str) and 'cascade' in on_del.lower(): 1212 css += ' cascade_delete' 1213 break 1214 widget = INPUT(_type='checkbox', 1215 _class=css, 1216 _id=self.FIELDKEY_DELETE_RECORD, 1217 _name=self.FIELDNAME_REQUEST_DELETE, 1218 ) 1219 xfields.append( 1220 (self.FIELDKEY_DELETE_RECORD + SQLFORM.ID_ROW_SUFFIX, 1221 LABEL( 1222 T(delete_label), separator, 1223 _for=self.FIELDKEY_DELETE_RECORD, 1224 _id=self.FIELDKEY_DELETE_RECORD + \ 1225 SQLFORM.ID_LABEL_SUFFIX), 1226 widget, 1227 col3.get(self.FIELDKEY_DELETE_RECORD, ''))) 1228 self.custom.delete = self.custom.deletable = widget 1229 1230 1231 # when writable, add submit button 1232 self.custom.submit = '' 1233 if not readonly: 1234 if 'submit' in buttons: 1235 widget = self.custom.submit = INPUT(_type='submit', 1236 _value=T(submit_button)) 1237 elif buttons: 1238 widget = self.custom.submit = DIV(*buttons) 1239 if self.custom.submit: 1240 xfields.append(('submit_record' + SQLFORM.ID_ROW_SUFFIX, 1241 '', widget, col3.get('submit_button', ''))) 1242 1243 # if a record is provided and found 1244 # make sure it's id is stored in the form 1245 if record: 1246 if not self['hidden']: 1247 self['hidden'] = {} 1248 if not keyed: 1249 self['hidden']['id'] = record[table._id.name] 1250 1251 (begin, end) = self._xml() 1252 self.custom.begin = XML("<%s %s>" % (self.tag, begin)) 1253 self.custom.end = XML("%s</%s>" % (end, self.tag)) 1254 table = self.createform(xfields) 1255 self.components = [table]

1256

1257 - def createform(self, xfields):

1258 formstyle = self.formstyle 1259 if isinstance(formstyle, basestring): 1260 if formstyle in SQLFORM.formstyles: 1261 formstyle = SQLFORM.formstyles[formstyle] 1262 else: 1263 raise RuntimeError('formstyle not found') 1264 1265 if callable(formstyle): 1266 # backward compatibility, 4 argument function is the old style 1267 args, varargs, keywords, defaults = inspect.getargspec(formstyle) 1268 if defaults and len(args) - len(defaults) == 4 or len(args) == 4: 1269 table = TABLE() 1270 for id, a, b, c in xfields: 1271 newrows = formstyle(id, a, b, c) 1272 self.field_parent[id] = getattr(b, 'parent', None) \ 1273 if isinstance(b,XmlComponent) else None 1274 if type(newrows).__name__ != "tuple": 1275 newrows = [newrows] 1276 for newrow in newrows: 1277 table.append(newrow) 1278 else: 1279 table = formstyle(self, xfields) 1280 for id, a, b, c in xfields: 1281 self.field_parent[id] = getattr(b, 'parent', None) \ 1282 if isinstance(b,XmlComponent) else None 1283 else: 1284 raise RuntimeError('formstyle not supported') 1285 return table

1286

1287 - def accepts( 1288 self, 1289 request_vars, 1290 session=None, 1291 formname='%(tablename)s/%(record_id)s', 1292 keepvalues=None, 1293 onvalidation=None, 1294 dbio=True, 1295 hideerror=False, 1296 detect_record_change=False, 1297 **kwargs 1298 ):

1299 1300 """ 1301 similar FORM.accepts but also does insert, update or delete in DAL. 1302 but if detect_record_change == True than: 1303 form.record_changed = False (record is properly validated/submitted) 1304 form.record_changed = True (record cannot be submitted because changed) 1305 elseif detect_record_change == False than: 1306 form.record_changed = None 1307 """ 1308 1309 if keepvalues is None: 1310 keepvalues = True if self.record else False 1311 1312 if self.readonly: 1313 return False 1314 1315 if request_vars.__class__.__name__ == 'Request': 1316 request_vars = request_vars.post_vars 1317 1318 keyed = hasattr(self.table, '_primarykey') 1319 1320 # implement logic to detect whether record exist but has been modified 1321 # server side 1322 self.record_changed = None 1323 self.detect_record_change = detect_record_change 1324 if self.detect_record_change: 1325 if self.record: 1326 self.record_changed = False 1327 serialized = '|'.join( 1328 str(self.record[k]) for k in self.table.fields()) 1329 self.record_hash = md5_hash(serialized) 1330 1331 # logic to deal with record_id for keyed tables 1332 if self.record: 1333 if keyed: 1334 formname_id = '.'.join(str(self.record[k]) 1335 for k in self.table._primarykey 1336 if hasattr(self.record, k)) 1337 record_id = dict((k, request_vars.get(k, None)) 1338 for k in self.table._primarykey) 1339 else: 1340 (formname_id, record_id) = (self.record[self.id_field_name], 1341 request_vars.get('id', None)) 1342 keepvalues = True 1343 else: 1344 if keyed: 1345 formname_id = 'create' 1346 record_id = dict([(k, None) for k in self.table._primarykey]) 1347 else: 1348 (formname_id, record_id) = ('create', None) 1349 1350 if not keyed and isinstance(record_id, (list, tuple)): 1351 record_id = record_id[0] 1352 1353 if formname: 1354 formname = formname % dict(tablename=self.table._tablename, 1355 record_id=formname_id) 1356 1357 # ## THIS IS FOR UNIQUE RECORDS, read IS_NOT_IN_DB 1358 1359 for fieldname in self.fields: 1360 field = self.table[fieldname] 1361 requires = field.requires or [] 1362 if not isinstance(requires, (list, tuple)): 1363 requires = [requires] 1364 [item.set_self_id(self.record_id) for item in requires 1365 if hasattr(item, 'set_self_id') and self.record_id] 1366 1367 # ## END 1368 1369 fields = {} 1370 for key in self.vars: 1371 fields[key] = self.vars[key] 1372 1373 ret = FORM.accepts( 1374 self, 1375 request_vars, 1376 session, 1377 formname, 1378 keepvalues, 1379 onvalidation, 1380 hideerror=hideerror, 1381 **kwargs 1382 ) 1383 1384 self.deleted = \ 1385 request_vars.get(self.FIELDNAME_REQUEST_DELETE, False) 1386 1387 self.custom.end = TAG[''](self.hidden_fields(), self.custom.end) 1388 1389 auch = record_id and self.errors and self.deleted 1390 1391 if self.record_changed and self.detect_record_change: 1392 message_onchange = \ 1393 kwargs.setdefault("message_onchange", 1394 current.T("A record change was detected. " + 1395 "Consecutive update self-submissions " + 1396 "are not allowed. Try re-submitting or " + 1397 "refreshing the form page.")) 1398 if message_onchange is not None: 1399 current.response.flash = message_onchange 1400 return ret 1401 elif (not ret) and (not auch): 1402 # auch is true when user tries to delete a record 1403 # that does not pass validation, yet it should be deleted 1404 for fieldname in self.fields: 1405 field = self.table[fieldname] 1406 ### this is a workaround! widgets should always have default not None! 1407 if not field.widget and field.type.startswith('list:') and \ 1408 not OptionsWidget.has_options(field): 1409 field.widget = self.widgets.list.widget 1410 if field.widget and fieldname in request_vars: 1411 if fieldname in self.request_vars: 1412 value = self.request_vars[fieldname] 1413 elif self.record: 1414 value = self.record[fieldname] 1415 else: 1416 value = self.table[fieldname].default 1417 row_id = '%s_%s%s' % ( 1418 self.table, fieldname, SQLFORM.ID_ROW_SUFFIX) 1419 widget = field.widget(field, value) 1420 parent = self.field_parent[row_id] 1421 if parent: 1422 parent.components = [widget] 1423 if self.errors.get(fieldname): 1424 parent._traverse(False, hideerror) 1425 self.custom.widget[fieldname] = widget 1426 self.accepted = ret 1427 return ret 1428 1429 if record_id and str(record_id) != str(self.record_id): 1430 raise SyntaxError('user is tampering with form\'s record_id: ' 1431 '%s != %s' % (record_id, self.record_id)) 1432 1433 if record_id and dbio and not keyed: 1434 self.vars.id = self.record[self.id_field_name] 1435 1436 if self.deleted and self.custom.deletable: 1437 if dbio: 1438 if keyed: 1439 qry = reduce(lambda x, y: x & y, 1440 [self.table[k] == record_id[k] 1441 for k in self.table._primarykey]) 1442 else: 1443 qry = self.table._id == self.record[self.id_field_name] 1444 self.table._db(qry).delete() 1445 self.errors.clear() 1446 for component in self.elements('input, select, textarea'): 1447 component['_disabled'] = True 1448 self.accepted = True 1449 return True 1450 1451 for fieldname in self.fields: 1452 if not fieldname in self.table.fields: 1453 continue 1454 1455 if not self.ignore_rw and not self.table[fieldname].writable: 1456 ### this happens because FORM has no knowledge of writable 1457 ### and thinks that a missing boolean field is a None 1458 if self.table[fieldname].type == 'boolean' and \ 1459 self.vars.get(fieldname, True) is None: 1460 del self.vars[fieldname] 1461 continue 1462 1463 field = self.table[fieldname] 1464 if field.type == 'id': 1465 continue 1466 if field.type == 'boolean': 1467 if self.vars.get(fieldname, False): 1468 self.vars[fieldname] = fields[fieldname] = True 1469 else: 1470 self.vars[fieldname] = fields[fieldname] = False 1471 elif field.type == 'password' and self.record\ 1472 and request_vars.get(fieldname, None) == \ 1473 PasswordWidget.DEFAULT_PASSWORD_DISPLAY: 1474 continue # do not update if password was not changed 1475 elif field.type == 'upload': 1476 f = self.vars[fieldname] 1477 fd = '%s__delete' % fieldname 1478 if f == '' or f is None: 1479 if self.vars.get(fd, False): 1480 f = self.table[fieldname].default or '' 1481 fields[fieldname] = f 1482 elif self.record: 1483 if self.record[fieldname]: 1484 fields[fieldname] = self.record[fieldname] 1485 else: 1486 f = self.table[fieldname].default or '' 1487 fields[fieldname] = f 1488 else: 1489 f = self.table[fieldname].default or '' 1490 fields[fieldname] = f 1491 self.vars[fieldname] = fields[fieldname] 1492 if not f: 1493 continue 1494 else: 1495 f = os.path.join( 1496 current.request.folder, 1497 os.path.normpath(f)) 1498 source_file = open(f, 'rb') 1499 original_filename = os.path.split(f)[1] 1500 elif hasattr(f, 'file'): 1501 (source_file, original_filename) = (f.file, f.filename) 1502 elif isinstance(f, (str, unicode)): 1503 ### do not know why this happens, it should not 1504 (source_file, original_filename) = \ 1505 (cStringIO.StringIO(f), 'file.txt') 1506 else: 1507 # this should never happen, why does it happen? 1508 #print 'f=',repr(f) 1509 continue 1510 newfilename = field.store(source_file, original_filename, 1511 field.uploadfolder) 1512 # this line was for backward compatibility but problematic 1513 # self.vars['%s_newfilename' % fieldname] = newfilename 1514 fields[fieldname] = newfilename 1515 if isinstance(field.uploadfield, str): 1516 fields[field.uploadfield] = source_file.read() 1517 # proposed by Hamdy (accept?) do we need fields at this point? 1518 self.vars[fieldname] = fields[fieldname] 1519 continue 1520 elif fieldname in self.vars: 1521 fields[fieldname] = self.vars[fieldname] 1522 elif field.default is None and field.type != 'blob': 1523 self.errors[fieldname] = 'no data' 1524 self.accepted = False 1525 return False 1526 value = fields.get(fieldname, None) 1527 if field.type == 'list:string': 1528 if not isinstance(value, (tuple, list)): 1529 fields[fieldname] = value and [value] or [] 1530 elif isinstance(field.type, str) and field.type.startswith('list:'): 1531 if not isinstance(value, list): 1532 fields[fieldname] = [safe_int( 1533 x) for x in (value and [value] or [])] 1534 elif field.type == 'integer': 1535 if not value is None: 1536 fields[fieldname] = safe_int(value) 1537 elif field.type.startswith('reference'): 1538 ## Avoid "constraint violation" exception when you have a 1539 ## optional reference field without the dropdown in form. I.e., 1540 ## a field with code to be typed, in a data-entry form. 1541 ## 1542 ## When your reference field doesn't have IS_EMPTY_OR() 1543 ## validator, "value" will come here as a string. So, 1544 ## safe_int() will return 0. In this case, insert will raise 1545 ## the constraint violation because there's no id=0 in 1546 ## referenced table. 1547 if isinstance(self.table, Table) and not keyed: 1548 if not value: 1549 fields[fieldname] = None 1550 else: 1551 fields[fieldname] = safe_int(value) 1552 elif field.type == 'double': 1553 if not value is None: 1554 fields[fieldname] = safe_float(value) 1555 1556 for fieldname in self.vars: 1557 if fieldname != 'id' and fieldname in self.table.fields\ 1558 and not fieldname in fields and not fieldname\ 1559 in request_vars: 1560 fields[fieldname] = self.vars[fieldname] 1561 1562 if dbio: 1563 if 'delete_this_record' in fields: 1564 # this should never happen but seems to happen to some 1565 del fields['delete_this_record'] 1566 for field in self.table: 1567 if not field.name in fields and field.writable is False \ 1568 and field.update is None and field.compute is None: 1569 if record_id and self.record: 1570 fields[field.name] = self.record[field.name] 1571 elif not self.table[field.name].default is None: 1572 fields[field.name] = self.table[field.name].default 1573 if keyed: 1574 if reduce(lambda x, y: x and y, record_id.values()): # if record_id 1575 if fields: 1576 qry = reduce(lambda x, y: x & y, 1577 [self.table[k] == self.record[k] for k in self.table._primarykey]) 1578 self.table._db(qry).update(**fields) 1579 else: 1580 pk = self.table.insert(**fields) 1581 if pk: 1582 self.vars.update(pk) 1583 else: 1584 ret = False 1585 else: 1586 if record_id: 1587 self.vars.id = self.record[self.id_field_name] 1588 if fields: 1589 self.table._db(self.table._id == self.record[ 1590 self.id_field_name]).update(**fields) 1591 else: 1592 self.vars.id = self.table.insert(**fields) 1593 self.accepted = ret 1594 return ret

1595 1596 AUTOTYPES = { 1597 type(''): ('string', None), 1598 type(True): ('boolean', None), 1599 type(1): ('integer', IS_INT_IN_RANGE(-1e12, +1e12)), 1600 type(1.0): ('double', IS_FLOAT_IN_RANGE()), 1601 type([]): ('list:string', None), 1602 type(datetime.date.today()): ('date', IS_DATE()), 1603 type(datetime.datetime.today()): ('datetime', IS_DATETIME()) 1604 } 1605 1606 @staticmethod

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

1608 fields = [] 1609 for key, value in sorted(dictionary.items()): 1610 t, requires = SQLFORM.AUTOTYPES.get(type(value), (None, None)) 1611 if t: 1612 fields.append(Field(key, t, requires=requires, 1613 default=value)) 1614 return SQLFORM.factory(*fields, **kwargs)

1615 1616 @staticmethod

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

1618 import os 1619 if query: 1620 session[name] = query.db(query).select().first().as_dict() 1621 elif os.path.exists(filename): 1622 env = {'datetime': datetime} 1623 session[name] = eval(open(filename).read(), {}, env) 1624 form = SQLFORM.dictform(session[name]) 1625 if form.process().accepted: 1626 session[name].update(form.vars) 1627 if query: 1628 query.db(query).update(**form.vars) 1629 else: 1630 open(filename, 'w').write(repr(session[name])) 1631 return form

1632 1633 @staticmethod

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

1635 """ 1636 generates a SQLFORM for the given fields. 1637 1638 Internally will build a non-database based data model 1639 to hold the fields. 1640 """ 1641 # Define a table name, this way it can be logical to our CSS. 1642 # And if you switch from using SQLFORM to SQLFORM.factory 1643 # your same css definitions will still apply. 1644 1645 table_name = attributes.get('table_name', 'no_table') 1646 1647 # So it won't interfere with SQLDB.define_table 1648 if 'table_name' in attributes: 1649 del attributes['table_name'] 1650 1651 return SQLFORM(DAL(None).define_table(table_name, *fields), 1652 **attributes)

1653 1654 @staticmethod

1655 - def build_query(fields, keywords):

1656 request = current.request 1657 if isinstance(keywords, (tuple, list)): 1658 keywords = keywords[0] 1659 request.vars.keywords = keywords 1660 key = keywords.strip() 1661 if key and not ' ' in key and not '"' in key and not "'" in key: 1662 SEARCHABLE_TYPES = ('string', 'text', 'list:string') 1663 parts = [field.contains( 1664 key) for field in fields if field.type in SEARCHABLE_TYPES] 1665 else: 1666 parts = None 1667 if parts: 1668 return reduce(lambda a, b: a | b, parts) 1669 else: 1670 return smart_query(fields, key)

1671 1672 @staticmethod

1673 - def search_menu(fields, 1674 search_options=None, 1675 prefix='w2p' 1676 ):

1677 T = current.T 1678 panel_id='%s_query_panel' % prefix 1679 fields_id='%s_query_fields' % prefix 1680 keywords_id='%s_keywords' % prefix 1681 field_id='%s_field' % prefix 1682 value_id='%s_value' % prefix 1683 search_options = search_options or { 1684 'string': ['=', '!=', '<', '>', '<=', '>=', 'starts with', 'contains', 'in', 'not in'], 1685 'text': ['=', '!=', '<', '>', '<=', '>=', 'starts with', 'contains', 'in', 'not in'], 1686 'date': ['=', '!=', '<', '>', '<=', '>='], 1687 'time': ['=', '!=', '<', '>', '<=', '>='], 1688 'datetime': ['=', '!=', '<', '>', '<=', '>='], 1689 'integer': ['=', '!=', '<', '>', '<=', '>=', 'in', 'not in'], 1690 'double': ['=', '!=', '<', '>', '<=', '>='], 1691 'id': ['=', '!=', '<', '>', '<=', '>=', 'in', 'not in'], 1692 'reference': ['=', '!='], 1693 'boolean': ['=', '!=']} 1694 if fields[0]._db._adapter.dbengine == 'google:datastore': 1695 search_options['string'] = ['=', '!=', '<', '>', '<=', '>='] 1696 search_options['text'] = ['=', '!=', '<', '>', '<=', '>='] 1697 search_options['list:string'] = ['contains'] 1698 search_options['list:integer'] = ['contains'] 1699 search_options['list:reference'] = ['contains'] 1700 criteria = [] 1701 selectfields = [] 1702 for field in fields: 1703 name = str(field).replace('.', '-') 1704 # treat ftype 'decimal' as 'double' 1705 # (this fixes problems but needs refactoring! 1706 if isinstance(field.type, SQLCustomType): 1707 ftype = field.type.type.split(' ')[0] 1708 else: 1709 ftype = field.type.split(' ')[0] 1710 if ftype.startswith('decimal'): ftype = 'double' 1711 elif ftype=='bigint': ftype = 'integer' 1712 elif ftype.startswith('big-'): ftype = ftype[4:] 1713 # end 1714 options = search_options.get(ftype, None) 1715 if options: 1716 label = isinstance( 1717 field.label, str) and T(field.label) or field.label 1718 selectfields.append(OPTION(label, _value=str(field))) 1719 operators = SELECT(*[OPTION(T(option), _value=option) for option in options]) 1720 _id = "%s_%s" % (value_id,name) 1721 if field.type == 'boolean': 1722 value_input = SQLFORM.widgets.boolean.widget(field,field.default,_id=_id) 1723 elif field.type == 'double': 1724 value_input = SQLFORM.widgets.double.widget(field,field.default,_id=_id) 1725 elif field.type == 'time': 1726 value_input = SQLFORM.widgets.time.widget(field,field.default,_id=_id) 1727 elif field.type == 'date': 1728 iso_format = {'_data-w2p_date_format' : '%Y-%m-%d'} 1729 value_input = SQLFORM.widgets.date.widget(field,field.default,_id=_id, **iso_format) 1730 elif field.type == 'datetime': 1731 iso_format = iso_format = {'_data-w2p_datetime_format' : '%Y-%m-%d %H:%M:%S'} 1732 value_input = SQLFORM.widgets.datetime.widget(field,field.default,_id=_id, **iso_format) 1733 elif (field.type.startswith('reference ') or 1734 field.type.startswith('list:reference ')) and \ 1735 hasattr(field.requires,'options'): 1736 value_input = SELECT( 1737 *[OPTION(v, _value=k) 1738 for k,v in field.requires.options()], 1739 **dict(_id=_id)) 1740 elif field.type == 'integer' or \ 1741 field.type.startswith('reference ') or \ 1742 field.type.startswith('list:integer') or \ 1743 field.type.startswith('list:reference '): 1744 value_input = SQLFORM.widgets.integer.widget(field,field.default,_id=_id) 1745 else: 1746 value_input = INPUT( 1747 _type='text', _id=_id, _class=field.type) 1748 1749 new_button = INPUT( 1750 _type="button", _value=T('New Search'), _class="btn btn-default", _title = T('Start building a new search'), 1751 _onclick="%s_build_query('new','%s')" % (prefix,field)) 1752 and_button = INPUT( 1753 _type="button", _value=T('+ And'), _class="btn btn-default", _title = T('Add this to the search as an AND term'), 1754 _onclick="%s_build_query('and','%s')" % (prefix, field)) 1755 or_button = INPUT( 1756 _type="button", _value=T('+ Or'), _class="btn btn-default",_title = T('Add this to the search as an OR term'), 1757 _onclick="%s_build_query('or','%s')" % (prefix, field)) 1758 close_button = INPUT( 1759 _type="button", _value=T('Close'), _class="btn btn-default", 1760 _onclick="jQuery('#%s').slideUp()" % panel_id) 1761 1762 criteria.append(DIV( 1763 operators, value_input, new_button, 1764 and_button, or_button, close_button, 1765 _id='%s_%s' % (field_id, name), 1766 _class='w2p_query_row hidden', 1767 _style='display:inline')) 1768 1769 criteria.insert(0, SELECT( 1770 _id=fields_id, 1771 _onchange="jQuery('.w2p_query_row').hide();jQuery('#%s_'+jQuery('#%s').val().replace('.','-')).show();" % (field_id,fields_id), 1772 _style='float:left', 1773 *selectfields)) 1774 1775 fadd = SCRIPT(""" 1776 jQuery('#%(fields_id)s input,#%(fields_id)s select').css( 1777 'width','auto'); 1778 jQuery(function(){web2py_ajax_fields('#%(fields_id)s');}); 1779 function %(prefix)s_build_query(aggregator,a) { 1780 var b=a.replace('.','-'); 1781 var option = jQuery('#%(field_id)s_'+b+' select').val(); 1782 var value = jQuery('#%(value_id)s_'+b).val().replace('"','\\\\"'); 1783 var s=a+' '+option+' "'+value+'"'; 1784 var k=jQuery('#%(keywords_id)s'); 1785 var v=k.val(); 1786 if(aggregator=='new') k.val(s); else k.val((v?(v+' '+ aggregator +' '):'')+s); 1787 } 1788 """ % dict( 1789 prefix=prefix,fields_id=fields_id,keywords_id=keywords_id, 1790 field_id=field_id,value_id=value_id 1791 ) 1792 ) 1793 return CAT( 1794 DIV(_id=panel_id, _style="display:none;", *criteria), fadd)

1795 1796 1797 @staticmethod

1798 - def grid(query, 1799 fields=None, 1800 field_id=None, 1801 left=None, 1802 headers={}, 1803 orderby=None, 1804 groupby=None, 1805 searchable=True, 1806 sortable=True, 1807 paginate=20, 1808 deletable=True, 1809 editable=True, 1810 details=True, 1811 selectable=None, 1812 create=True, 1813 csv=True, 1814 links=None, 1815 links_in_grid=True, 1816 upload='<default>', 1817 args=[], 1818 user_signature=True, 1819 maxtextlengths={}, 1820 maxtextlength=20, 1821 onvalidation=None, 1822 onfailure=None, 1823 oncreate=None, 1824 onupdate=None, 1825 ondelete=None, 1826 sorter_icons=(XML('▲'), XML('▼')), 1827 ui = 'web2py', 1828 showbuttontext=True, 1829 _class="web2py_grid", 1830 formname='web2py_grid', 1831 search_widget='default', 1832 ignore_rw = False, 1833 formstyle = 'table3cols', 1834 exportclasses = None, 1835 formargs={}, 1836 createargs={}, 1837 editargs={}, 1838 viewargs={}, 1839 selectable_submit_button='Submit', 1840 buttons_placement = 'right', 1841 links_placement = 'right', 1842 noconfirm=False, 1843 cache_count=None, 1844 client_side_delete=False, 1845 ignore_common_filters=None, 1846 ):

1847 1848 # jQuery UI ThemeRoller classes (empty if ui is disabled) 1849 if ui == 'jquery-ui': 1850 ui = dict(widget='ui-widget', 1851 header='ui-widget-header', 1852 content='ui-widget-content', 1853 default='ui-state-default', 1854 cornerall='ui-corner-all', 1855 cornertop='ui-corner-top', 1856 cornerbottom='ui-corner-bottom', 1857 button='ui-button-text-icon-primary', 1858 buttontext='ui-button-text', 1859 buttonadd='ui-icon ui-icon-plusthick', 1860 buttonback='ui-icon ui-icon-arrowreturnthick-1-w', 1861 buttonexport='ui-icon ui-icon-transferthick-e-w', 1862 buttondelete='ui-icon ui-icon-trash', 1863 buttonedit='ui-icon ui-icon-pencil', 1864 buttontable='ui-icon ui-icon-triangle-1-e', 1865 buttonview='ui-icon ui-icon-zoomin', 1866 ) 1867 elif ui == 'web2py': 1868 ui = dict(widget='', 1869 header='', 1870 content='', 1871 default='', 1872 cornerall='', 1873 cornertop='', 1874 cornerbottom='', 1875 button='button btn btn-default', 1876 buttontext='buttontext button', 1877 buttonadd='icon plus icon-plus glyphicon glyphicon-plus', 1878 buttonback='icon leftarrow icon-arrow-left glyphicon glyphicon-arrow-left', 1879 buttonexport='icon downarrow icon-download glyphicon glyphicon-download', 1880 buttondelete='icon trash icon-trash glyphicon glyphicon-trash', 1881 buttonedit='icon pen icon-pencil glyphicon glyphicon-arrow-pencil', 1882 buttontable='icon rightarrow icon-arrow-right glyphicon glyphicon-arrow-right', 1883 buttonview='icon magnifier icon-zoom-in glyphicon glyphicon-arrow-zoom-in', 1884 ) 1885 elif not isinstance(ui, dict): 1886 raise RuntimeError('SQLFORM.grid ui argument must be a dictionary') 1887 1888 db = query._db 1889 T = current.T 1890 request = current.request 1891 session = current.session 1892 response = current.response 1893 logged = session.auth and session.auth.user 1894 wenabled = (not user_signature or logged) and not groupby 1895 create = wenabled and create 1896 editable = wenabled and editable 1897 deletable = wenabled and deletable 1898 details = details and not groupby 1899 rows = None 1900 1901 def fetch_count(dbset): 1902 ##FIXME for google:datastore cache_count is ignored 1903 ## if it's not an integer 1904 if cache_count is None or isinstance(cache_count, tuple): 1905 if groupby: 1906 c = 'count(*)' 1907 nrows = db.executesql( 1908 'select count(*) from (%s) _tmp;' % 1909 dbset._select(c, left=left, cacheable=True, 1910 groupby=groupby, 1911 cache=cache_count)[:-1])[0][0] 1912 elif left: 1913 c = 'count(*)' 1914 nrows = dbset.select(c, left=left, cacheable=True, cache=cache_count).first()[c] 1915 elif dbset._db._adapter.dbengine=='google:datastore': 1916 #if we don't set a limit, this can timeout for a large table 1917 nrows = dbset.db._adapter.count(dbset.query, limit=1000) 1918 else: 1919 nrows = dbset.count(cache=cache_count) 1920 elif isinstance(cache_count, (int, long)): 1921 nrows = cache_count 1922 elif callable(cache_count): 1923 nrows = cache_count(dbset, request.vars) 1924 else: 1925 nrows = 0 1926 return nrows

1927 1928 def url(**b): 1929 b['args'] = args + b.get('args', []) 1930 localvars = request.get_vars.copy() 1931 localvars.update(b.get('vars', {})) 1932 b['vars'] = localvars 1933 b['hash_vars'] = False 1934 b['user_signature'] = user_signature 1935 return URL(**b)

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

2633 - def smartgrid(table, constraints=None, linked_tables=None, 2634 links=None, links_in_grid=True, 2635 args=None, user_signature=True, 2636 divider='>', breadcrumbs_class='', 2637 **kwargs):

2638 """ 2639 @auth.requires_login() 2640 def index(): 2641 db.define_table('person',Field('name'),format='%(name)s') 2642 db.define_table('dog', 2643 Field('name'),Field('owner',db.person),format='%(name)s') 2644 db.define_table('comment',Field('body'),Field('dog',db.dog)) 2645 if db(db.person).isempty(): 2646 from gluon.contrib.populate import populate 2647 populate(db.person,300) 2648 populate(db.dog,300) 2649 populate(db.comment,1000) 2650 db.commit() 2651 form=SQLFORM.smartgrid(db[request.args(0) or 'person']) #*** 2652 return dict(form=form) 2653 2654 *** builds a complete interface to navigate all tables links 2655 to the request.args(0) 2656 table: pagination, search, view, edit, delete, 2657 children, parent, etc. 2658 2659 constraints is a dict {'table':query} that limits which 2660 records can be accessible 2661 links is a dict like 2662 {'tablename':[lambda row: A(....), ...]} 2663 that will add buttons when table tablename is displayed 2664 linked_tables is a optional list of tablenames of tables 2665 to be linked 2666 """ 2667 request, T = current.request, current.T 2668 if args is None: 2669 args = [] 2670 2671 def url(**b): 2672 b['args'] = request.args[:nargs] + b.get('args', []) 2673 b['hash_vars'] = False 2674 b['user_signature'] = user_signature 2675 return URL(**b)

2676 2677 db = table._db 2678 breadcrumbs = [] 2679 if request.args(len(args)) != table._tablename: 2680 request.args[:] = args + [table._tablename] 2681 if links is None: 2682 links = {} 2683 if constraints is None: 2684 constraints = {} 2685 field = None 2686 name = None 2687 def format(table,row): 2688 if not row: 2689 return T('Unknown') 2690 elif isinstance(table._format,str): 2691 return table._format % row 2692 elif callable(table._format): 2693 return table._format(row) 2694 else: 2695 return '#'+str(row.id) 2696 try: 2697 nargs = len(args) + 1 2698 previous_tablename, previous_fieldname, previous_id = \ 2699 table._tablename, None, None 2700 while len(request.args) > nargs: 2701 key = request.args(nargs) 2702 if '.' in key: 2703 id = request.args(nargs + 1) 2704 tablename, fieldname = key.split('.', 1) 2705 table = db[tablename] 2706 field = table[fieldname] 2707 field.default = id 2708 referee = field.type[10:] 2709 if referee != previous_tablename: 2710 raise HTTP(400) 2711 cond = constraints.get(referee, None) 2712 if cond: 2713 record = db( 2714 db[referee]._id == id)(cond).select().first() 2715 else: 2716 record = db[referee](id) 2717 if previous_id: 2718 if record[previous_fieldname] != int(previous_id): 2719 raise HTTP(400) 2720 previous_tablename, previous_fieldname, previous_id = \ 2721 tablename, fieldname, id 2722 name = format(db[referee],record) 2723 breadcrumbs.append( 2724 LI(A(T(db[referee]._plural), 2725 cid=request.cid, 2726 _href=url()), 2727 SPAN(divider, _class='divider'), 2728 _class='w2p_grid_breadcrumb_elem')) 2729 if kwargs.get('details', True): 2730 breadcrumbs.append( 2731 LI(A(name, cid=request.cid, 2732 _href=url(args=['view', referee, id])), 2733 SPAN(divider, _class='divider'), 2734 _class='w2p_grid_breadcrumb_elem')) 2735 nargs += 2 2736 else: 2737 break 2738 if nargs > len(args) + 1: 2739 query = (field == id) 2740 # cjk 2741 # if isinstance(linked_tables, dict): 2742 # linked_tables = linked_tables.get(table._tablename, []) 2743 if linked_tables is None or referee in linked_tables: 2744 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])) 2745 except (KeyError, ValueError, TypeError): 2746 redirect(URL(args=table._tablename)) 2747 if nargs == len(args) + 1: 2748 query = table._db._adapter.id_query(table) 2749 2750 # filter out data info for displayed table 2751 if table._tablename in constraints: 2752 query = query & constraints[table._tablename] 2753 if isinstance(links, dict): 2754 links = links.get(table._tablename, []) 2755 for key in 'columns,orderby,searchable,sortable,paginate,deletable,editable,details,selectable,create,fields'.split(','): 2756 if isinstance(kwargs.get(key, None), dict): 2757 if table._tablename in kwargs[key]: 2758 kwargs[key] = kwargs[key][table._tablename] 2759 else: 2760 del kwargs[key] 2761 check = {} 2762 id_field_name = table._id.name 2763 for rfield in table._referenced_by: 2764 check[rfield.tablename] = \ 2765 check.get(rfield.tablename, []) + [rfield.name] 2766 if linked_tables is None: 2767 linked_tables = db.tables() 2768 if isinstance(linked_tables, dict): 2769 linked_tables = linked_tables.get(table._tablename,[]) 2770 if linked_tables: 2771 for item in linked_tables: 2772 tb = None 2773 if isinstance(item,Table) and item._tablename in check: 2774 tablename = item._tablename 2775 linked_fieldnames = check[tablename] 2776 tb = item 2777 elif isinstance(item,str) and item in check: 2778 tablename = item 2779 linked_fieldnames = check[item] 2780 tb = db[item] 2781 elif isinstance(item,Field) and item.name in check.get(item._tablename,[]): 2782 tablename = item._tablename 2783 linked_fieldnames = [item.name] 2784 tb = item.table 2785 else: 2786 linked_fieldnames = [] 2787 if tb: 2788 multiple_links = len(linked_fieldnames) > 1 2789 for fieldname in linked_fieldnames: 2790 t = T(tb._plural) if not multiple_links else \ 2791 T(tb._plural + '(' + fieldname + ')') 2792 args0 = tablename + '.' + fieldname 2793 links.append( 2794 lambda row, t=t, nargs=nargs, args0=args0: 2795 A(SPAN(t), cid=request.cid, _href=url( 2796 args=[args0, row[id_field_name]]))) 2797 2798 grid = SQLFORM.grid(query, args=request.args[:nargs], links=links, 2799 links_in_grid=links_in_grid, 2800 user_signature=user_signature, **kwargs) 2801 2802 if isinstance(grid, DIV): 2803 header = table._plural 2804 next = grid.create_form or grid.update_form or grid.view_form 2805 breadcrumbs.append(LI( 2806 A(T(header), cid=request.cid,_href=url()), 2807 SPAN(divider, _class='divider') if next else '', 2808 _class='active w2p_grid_breadcrumb_elem')) 2809 if grid.create_form: 2810 header = T('New %(entity)s') % dict(entity=table._singular) 2811 elif grid.update_form: 2812 header = T('Edit %(entity)s') % dict( 2813 entity=format(grid.update_form.table, 2814 grid.update_form.record)) 2815 elif grid.view_form: 2816 header = T('View %(entity)s') % dict( 2817 entity=format(grid.view_form.table, 2818 grid.view_form.record)) 2819 if next: 2820 breadcrumbs.append(LI( 2821 A(T(header), cid=request.cid,_href=url()), 2822 _class='active w2p_grid_breadcrumb_elem')) 2823 grid.insert( 2824 0, DIV(UL(*breadcrumbs, **{'_class': breadcrumbs_class}), 2825 _class='web2py_breadcrumbs')) 2826 return grid 2827

2828 2829 -class SQLTABLE(TABLE):

2830 2831 """ 2832 given a Rows object, as returned by a db().select(), generates 2833 an html table with the rows. 2834 2835 optional arguments: 2836 2837 :param linkto: URL (or lambda to generate a URL) to edit individual records 2838 :param upload: URL to download uploaded files 2839 :param orderby: Add an orderby link to column headers. 2840 :param headers: dictionary of headers to headers redefinions 2841 headers can also be a string to gerenare the headers from data 2842 for now only headers="fieldname:capitalize", 2843 headers="labels" and headers=None are supported 2844 :param truncate: length at which to truncate text in table cells. 2845 Defaults to 16 characters. 2846 :param columns: a list or dict contaning the names of the columns to be shown 2847 Defaults to all 2848 2849 Optional names attributes for passed to the <table> tag 2850 2851 The keys of headers and columns must be of the form "tablename.fieldname" 2852 2853 Simple linkto example:: 2854 2855 rows = db.select(db.sometable.ALL) 2856 table = SQLTABLE(rows, linkto='someurl') 2857 2858 This will link rows[id] to .../sometable/value_of_id 2859 2860 2861 More advanced linkto example:: 2862 2863 def mylink(field, type, ref): 2864 return URL(args=[field]) 2865 2866 rows = db.select(db.sometable.ALL) 2867 table = SQLTABLE(rows, linkto=mylink) 2868 2869 This will link rows[id] to 2870 current_app/current_controlle/current_function/value_of_id 2871 2872 New Implements: 24 June 2011: 2873 ----------------------------- 2874 2875 :param selectid: The id you want to select 2876 :param renderstyle: Boolean render the style with the table 2877 2878 :param extracolumns = [{'label':A('Extra',_href='#'), 2879 'class': '', #class name of the header 2880 'width':'', #width in pixels or % 2881 'content':lambda row, rc: A('Edit',_href='edit/%s'%row.id), 2882 'selected': False #agregate class selected to this column 2883 }] 2884 2885 2886 :param headers = {'table.id':{'label':'Id', 2887 'class':'', #class name of the header 2888 'width':'', #width in pixels or % 2889 'truncate': 16, #truncate the content to... 2890 'selected': False #agregate class selected to this column 2891 }, 2892 'table.myfield':{'label':'My field', 2893 'class':'', #class name of the header 2894 'width':'', #width in pixels or % 2895 'truncate': 16, #truncate the content to... 2896 'selected': False #agregate class selected to this column 2897 }, 2898 } 2899 2900 table = SQLTABLE(rows, headers=headers, extracolumns=extracolumns) 2901 `< 2902 2903 """ 2904

2905 - def __init__( 2906 self, 2907 sqlrows, 2908 linkto=None, 2909 upload=None, 2910 orderby=None, 2911 headers={}, 2912 truncate=16, 2913 columns=None, 2914 th_link='', 2915 extracolumns=None, 2916 selectid=None, 2917 renderstyle=False, 2918 cid=None, 2919 colgroup=False, 2920 **attributes 2921 ):

2922 2923 TABLE.__init__(self, **attributes) 2924 2925 self.components = [] 2926 self.attributes = attributes 2927 self.sqlrows = sqlrows 2928 (components, row) = (self.components, []) 2929 if not sqlrows: 2930 return 2931 if not columns: 2932 REGEX_TABLE_DOT_FIELD = sqlrows.db._adapter.REGEX_TABLE_DOT_FIELD 2933 columns = [c for c in sqlrows.colnames if REGEX_TABLE_DOT_FIELD.match(c)] 2934 if headers == 'fieldname:capitalize': 2935 headers = {} 2936 for c in columns: 2937 headers[c] = c.split('.')[-1].replace('_', ' ').title() 2938 elif headers == 'labels': 2939 headers = {} 2940 for c in columns: 2941 (t, f) = c.split('.') 2942 field = sqlrows.db[t][f] 2943 headers[c] = field.label 2944 if colgroup: 2945 cols = [COL(_id=c.replace('.', '-'), data={'column': i + 1}) 2946 for i, c in enumerate(columns)] 2947 if extracolumns: 2948 cols += [COL(data={'column': len(cols) + i + 1}) 2949 for i, c in enumerate(extracolumns)] 2950 components.append(COLGROUP(*cols)) 2951 2952 if headers is None: 2953 headers = {} 2954 else: 2955 for c in columns: # new implement dict 2956 if isinstance(headers.get(c, c), dict): 2957 coldict = headers.get(c, c) 2958 attrcol = dict() 2959 if coldict['width'] != "": 2960 attrcol.update(_width=coldict['width']) 2961 if coldict['class'] != "": 2962 attrcol.update(_class=coldict['class']) 2963 row.append(TH(coldict['label'], **attrcol)) 2964 elif orderby: 2965 row.append(TH(A(headers.get(c, c), 2966 _href=th_link + '?orderby=' + c, cid=cid))) 2967 else: 2968 row.append(TH(headers.get(c, c))) 2969 2970 if extracolumns: # new implement dict 2971 for c in extracolumns: 2972 attrcol = dict() 2973 if c['width'] != "": 2974 attrcol.update(_width=c['width']) 2975 if c['class'] != "": 2976 attrcol.update(_class=c['class']) 2977 row.append(TH(c['label'], **attrcol)) 2978 2979 components.append(THEAD(TR(*row))) 2980 2981 tbody = [] 2982 for (rc, record) in enumerate(sqlrows): 2983 row = [] 2984 if rc % 2 == 0: 2985 _class = 'w2p_even even' 2986 else: 2987 _class = 'w2p_odd odd' 2988 2989 if not selectid is None: # new implement 2990 if record.get('id') == selectid: 2991 _class += ' rowselected' 2992 2993 for colname in columns: 2994 if not table_field.match(colname): 2995 if "_extra" in record and colname in record._extra: 2996 r = record._extra[colname] 2997 row.append(TD(r)) 2998 continue 2999 else: 3000 raise KeyError( 3001 "Column %s not found (SQLTABLE)" % colname) 3002 (tablename, fieldname) = colname.split('.') 3003 try: 3004 field = sqlrows.db[tablename][fieldname] 3005 except (KeyError, AttributeError): 3006 field = None 3007 if tablename in record \ 3008 and isinstance(record, Row) \ 3009 and isinstance(record[tablename], Row): 3010 r = record[tablename][fieldname] 3011 elif fieldname in record: 3012 r = record[fieldname] 3013 else: 3014 raise SyntaxError('something wrong in Rows object') 3015 r_old = r 3016 if not field or isinstance(field, (Field.Virtual, Field.Lazy)): 3017 pass 3018 elif linkto and field.type == 'id': 3019 try: 3020 href = linkto(r, 'table', tablename) 3021 except TypeError: 3022 href = '%s/%s/%s' % (linkto, tablename, r_old) 3023 r = A(r, _href=href) 3024 elif isinstance(field.type, str) and field.type.startswith('reference'): 3025 if linkto: 3026 ref = field.type[10:] 3027 try: 3028 href = linkto(r, 'reference', ref) 3029 except TypeError: 3030 href = '%s/%s/%s' % (linkto, ref, r_old) 3031 if ref.find('.') >= 0: 3032 tref, fref = ref.split('.') 3033 if hasattr(sqlrows.db[tref], '_primarykey'): 3034 href = '%s/%s?%s' % (linkto, tref, urllib.urlencode({fref: r})) 3035 r = A(represent(field, r, record), _href=str(href)) 3036 elif field.represent: 3037 r = represent(field, r, record) 3038 elif linkto and hasattr(field._table, '_primarykey')\ 3039 and fieldname in field._table._primarykey: 3040 # have to test this with multi-key tables 3041 key = urllib.urlencode(dict([ 3042 ((tablename in record 3043 and isinstance(record, Row) 3044 and isinstance(record[tablename], Row)) and 3045 (k, record[tablename][k])) or (k, record[k]) 3046 for k in field._table._primarykey])) 3047 r = A(r, _href='%s/%s?%s' % (linkto, tablename, key)) 3048 elif isinstance(field.type, str) and field.type.startswith('list:'): 3049 r = represent(field, r or [], record) 3050 elif field.represent: 3051 r = represent(field, r, record) 3052 elif field.type == 'blob' and r: 3053 r = 'DATA' 3054 elif field.type == 'upload': 3055 if upload and r: 3056 r = A(current.T('file'), _href='%s/%s' % (upload, r)) 3057 elif r: 3058 r = current.T('file') 3059 else: 3060 r = '' 3061 elif field.type in ['string', 'text']: 3062 r = str(field.formatter(r)) 3063 if headers != {}: # new implement dict 3064 if isinstance(headers[colname], dict): 3065 if isinstance(headers[colname]['truncate'], int): 3066 r = truncate_string( 3067 r, headers[colname]['truncate']) 3068 elif not truncate is None: 3069 r = truncate_string(r, truncate) 3070 attrcol = dict() # new implement dict 3071 if headers != {}: 3072 if isinstance(headers[colname], dict): 3073 colclass = headers[colname]['class'] 3074 if headers[colname]['selected']: 3075 colclass = str(headers[colname] 3076 ['class'] + " colselected").strip() 3077 if colclass != "": 3078 attrcol.update(_class=colclass) 3079 3080 row.append(TD(r, **attrcol)) 3081 3082 if extracolumns: # new implement dict 3083 for c in extracolumns: 3084 attrcol = dict() 3085 colclass = c['class'] 3086 if c['selected']: 3087 colclass = str(c['class'] + " colselected").strip() 3088 if colclass != "": 3089 attrcol.update(_class=colclass) 3090 contentfunc = c['content'] 3091 row.append(TD(contentfunc(record, rc), **attrcol)) 3092 3093 tbody.append(TR(_class=_class, *row)) 3094 3095 if renderstyle: 3096 components.append(STYLE(self.style())) 3097 3098 components.append(TBODY(*tbody))

3099

3100 - def style(self):

3101 3102 css = ''' 3103 table tbody tr.w2p_odd { 3104 background-color: #DFD; 3105 } 3106 table tbody tr.w2p_even { 3107 background-color: #EFE; 3108 } 3109 table tbody tr.rowselected { 3110 background-color: #FDD; 3111 } 3112 table tbody tr td.colselected { 3113 background-color: #FDD; 3114 } 3115 table tbody tr:hover { 3116 background: #DDF; 3117 } 3118 ''' 3119 3120 return css

3121 3122 form_factory = SQLFORM.factory # for backward compatibility, deprecated

3123 3124 3125 -class ExportClass(object):

3126 label = None 3127 file_ext = None 3128 content_type = None 3129

3130 - def __init__(self, rows):

3131 self.rows = rows

3132

3133 - def represented(self):

3134 def none_exception(value): 3135 """ 3136 returns a cleaned up value that can be used for csv export: 3137 - unicode text is encoded as such 3138 - None values are replaced with the given representation (default <NULL>) 3139 """ 3140 if value is None: 3141 return '<NULL>' 3142 elif isinstance(value, unicode): 3143 return value.encode('utf8') 3144 elif isinstance(value, Reference): 3145 return int(value) 3146 elif hasattr(value, 'isoformat'): 3147 return value.isoformat()[:19].replace('T', ' ') 3148 elif isinstance(value, (list, tuple)): # for type='list:..' 3149 return bar_encode(value) 3150 return value

3151 3152 represented = [] 3153 for record in self.rows: 3154 row = [] 3155 for col in self.rows.colnames: 3156 if not self.rows.db._adapter.REGEX_TABLE_DOT_FIELD.match(col): 3157 row.append(record._extra[col]) 3158 else: 3159 (t, f) = col.split('.') 3160 field = self.rows.db[t][f] 3161 if isinstance(record.get(t, None), (Row, dict)): 3162 value = record[t][f] 3163 else: 3164 value = record[f] 3165 if field.type == 'blob' and not value is None: 3166 value = '' 3167 elif field.represent: 3168 value = field.represent(value, record) 3169 row.append(none_exception(value)) 3170 3171 represented.append(row) 3172 return represented

3173

3174 - def export(self):

3175 raise NotImplementedError

3176

3177 -class ExporterTSV(ExportClass):

3178 label = 'TSV' 3179 file_ext = "csv" 3180 content_type = "text/tab-separated-values" 3181

3182 - def __init__(self, rows):

3183 ExportClass.__init__(self,rows)

3184

3185 - def export(self):

3186 out = cStringIO.StringIO() 3187 final = cStringIO.StringIO() 3188 import csv 3189 writer = csv.writer(out, delimiter='\t') 3190 if self.rows: 3191 import codecs 3192 final.write(codecs.BOM_UTF16) 3193 writer.writerow( 3194 [unicode(col).encode("utf8") for col in self.rows.colnames]) 3195 data = out.getvalue().decode("utf8") 3196 data = data.encode("utf-16") 3197 data = data[2:] 3198 final.write(data) 3199 out.truncate(0) 3200 3201 records = self.represented() 3202 for row in records: 3203 writer.writerow( 3204 [str(col).decode('utf8').encode("utf-8") for col in row]) 3205 data = out.getvalue().decode("utf8") 3206 data = data.encode("utf-16") 3207 data = data[2:] 3208 final.write(data) 3209 3210 out.truncate(0) 3211 return str(final.getvalue())

3212

3213 -class ExporterCSV(ExportClass):

3214 #CSV, represent == True 3215 label = 'CSV' 3216 file_ext = "csv" 3217 content_type = "text/csv" 3218

3219 - def __init__(self, rows):

3220 ExportClass.__init__(self, rows)

3221

3222 - def export(self): #export CSV with rows.represent

3223 if self.rows: 3224 s = cStringIO.StringIO() 3225 self.rows.export_to_csv_file(s,represent=True) 3226 return s.getvalue() 3227 else: 3228 return None

3229

3230 -class ExporterCSV_hidden(ExportClass):

3231 #pure csv, no represent. 3232 label = 'CSV' 3233 file_ext = "csv" 3234 content_type = "text/csv" 3235

3236 - def __init__(self, rows):

3237 ExportClass.__init__(self,rows)

3238

3239 - def export(self):

3240 if self.rows: 3241 return self.rows.as_csv() 3242 else: 3243 return ''

3244

3245 -class ExporterHTML(ExportClass):

3246 label = 'HTML' 3247 file_ext = "html" 3248 content_type = "text/html" 3249

3250 - def __init__(self, rows):

3251 ExportClass.__init__(self, rows)

3252

3253 - def export(self):

3254 xml = self.rows.xml() if self.rows else '' 3255 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>' % (xml or '')

3256

3257 -class ExporterXML(ExportClass):

3258 label = 'XML' 3259 file_ext = "xml" 3260 content_type = "text/xml" 3261

3262 - def __init__(self, rows):

3263 ExportClass.__init__(self, rows)

3264

3265 - def export(self):

3266 if self.rows: 3267 return self.rows.as_xml() 3268 else: 3269 return '<rows></rows>'

3270

3271 -class ExporterJSON(ExportClass):

3272 label = 'JSON' 3273 file_ext = "json" 3274 content_type = "application/json" 3275

3276 - def __init__(self, rows):

3277 ExportClass.__init__(self, rows)

3278

3279 - def export(self):

3280 if self.rows: 3281 return self.rows.as_json() 3282 else: 3283 return 'null'

3284

Source Code for Module gluon.sqlhtml