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

50 51 52 -def trap_class(_class=None, trap=True):

53 return (trap and 'w2p_trap' or '') + (_class and ' ' + _class or '')

54

55 56 -def represent(field, value, record):

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

69

70 71 -def safe_int(x):

72 try: 73 return int(x) 74 except ValueError: 75 return 0

76

77 78 -def safe_float(x):

79 try: 80 return float(x) 81 except ValueError: 82 return 0

83

84 85 -def show_if(cond):

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

104

105 106 -class FormWidget(object):

107 """ 108 helper for SQLFORM to generate form input fields 109 (widget), related to the fieldtype 110 """ 111 112 _class = 'generic-widget' 113 114 @classmethod

115 - def _attributes(cls, field, 116 widget_attributes, **attributes):

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

139 140 @classmethod

157

158 159 -class StringWidget(FormWidget):

160 _class = 'string' 161 162 @classmethod

177

178 179 -class IntegerWidget(StringWidget):

180 _class = 'integer'

181

182 183 -class DoubleWidget(StringWidget):

184 _class = 'double'

185

186 187 -class DecimalWidget(StringWidget):

188 _class = 'decimal'

189

190 191 -class TimeWidget(StringWidget):

192 _class = 'time'

193

194 195 -class DateWidget(StringWidget):

196 _class = 'date'

197

198 -class DatetimeWidget(StringWidget):

199 _class = 'datetime'

200

201 -class TextWidget(FormWidget):

202 _class = 'text' 203 204 @classmethod

215

216 -class JSONWidget(FormWidget):

217 _class = 'json' 218 219 @classmethod

232

233 -class BooleanWidget(FormWidget):

234 _class = 'boolean' 235 236 @classmethod

248

249 250 -class OptionsWidget(FormWidget):

251 252 @staticmethod

253 - def has_options(field):

254 """ 255 checks if the field has selectable options 256 257 :param field: the field needing checking 258 :returns: True if the field has options 259 """ 260 261 return hasattr(field.requires, 'options')

262 263 @classmethod

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

284

285 286 -class ListWidget(StringWidget):

287 288 @classmethod

290 _id = '%s_%s' % (field.tablename, field.name) 291 _name = field.name 292 if field.type == 'list:integer': 293 _class = 'integer' 294 else: 295 _class = 'string' 296 requires = field.requires if isinstance( 297 field.requires, (IS_NOT_EMPTY, IS_LIST_OF)) else None 298 attributes['_style'] = 'list-style:none' 299 nvalue = value or [''] 300 items = [LI(INPUT(_id=_id, _class=_class, _name=_name, 301 value=v, hideerror=k < len(nvalue) - 1, 302 requires=requires), 303 **attributes) for (k, v) in enumerate(nvalue)] 304 script = SCRIPT(""" 305 // from http://refactormycode.com/codes/694-expanding-input-list-using-jquery 306 (function(){ 307 jQuery.fn.grow_input = function() { 308 return this.each(function() { 309 var ul = this; 310 jQuery(ul).find(":text").after('<a href="javascript:void(0)">+</a> <a href="javascript:void(0)">-</a>').keypress(function (e) { return (e.which == 13) ? pe(ul, e) : true; }).next().click(function(e){ pe(ul, e) }).next().click(function(e){ rl(ul, e)}); 311 }); 312 }; 313 function pe(ul, e) { 314 var new_line = ml(ul); 315 rel(ul); 316 if (jQuery(e.target).parent().is(':visible')) { 317 //make sure we didn't delete the element before we insert after 318 new_line.insertAfter(jQuery(e.target).parent()); 319 } else { 320 //the line we clicked on was deleted, just add to end of list 321 new_line.appendTo(ul); 322 } 323 new_line.find(":text").focus(); 324 return false; 325 } 326 function rl(ul, e) { 327 if (jQuery(ul).children().length > 1) { 328 //only remove if we have more than 1 item so the list is never empty 329 jQuery(e.target).parent().remove(); 330 } 331 } 332 function ml(ul) { 333 var line = jQuery(ul).find("li:first").clone(true); 334 line.find(':text').val(''); 335 return line; 336 } 337 function rel(ul) { 338 jQuery(ul).find("li").each(function() { 339 var trimmed = jQuery.trim(jQuery(this.firstChild).val()); 340 if (trimmed=='') jQuery(this).remove(); else jQuery(this.firstChild).val(trimmed); 341 }); 342 } 343 })(); 344 jQuery(document).ready(function(){jQuery('#%s_grow_input').grow_input();}); 345 """ % _id) 346 attributes['_id'] = _id + '_grow_input' 347 attributes['_style'] = 'list-style:none' 348 return TAG[''](UL(*items, **attributes), script)

349

350 351 -class MultipleOptionsWidget(OptionsWidget):

352 353 @classmethod

367

368 369 -class RadioWidget(OptionsWidget):

370 371 @classmethod

373 """ 374 generates a TABLE tag, including INPUT radios (only 1 option allowed) 375 376 see also: :meth:`FormWidget.widget` 377 """ 378 379 if isinstance(value, (list,tuple)): 380 value = str(value[0]) 381 else: 382 value = str(value) 383 384 385 attr = cls._attributes(field, {}, **attributes) 386 attr['_class'] = attr.get('_class', 'web2py_radiowidget') 387 388 requires = field.requires 389 if not isinstance(requires, (list, tuple)): 390 requires = [requires] 391 if requires: 392 if hasattr(requires[0], 'options'): 393 options = requires[0].options() 394 else: 395 raise SyntaxError('widget cannot determine options of %s' 396 % field) 397 options = [(k, v) for k, v in options if str(v)] 398 opts = [] 399 cols = attributes.get('cols', 1) 400 totals = len(options) 401 mods = totals % cols 402 rows = totals / cols 403 if mods: 404 rows += 1 405 406 #widget style 407 wrappers = dict( 408 table=(TABLE, TR, TD), 409 ul=(DIV, UL, LI), 410 divs=(CAT, DIV, DIV) 411 ) 412 parent, child, inner = wrappers[attributes.get('style', 'table')] 413 414 for r_index in range(rows): 415 tds = [] 416 for k, v in options[r_index * cols:(r_index + 1) * cols]: 417 checked = {'_checked': 'checked'} if k == value else {} 418 tds.append(inner(INPUT(_type='radio', 419 _id='%s%s' % (field.name, k), 420 _name=field.name, 421 requires=attr.get('requires', None), 422 hideerror=True, _value=k, 423 value=value, 424 **checked), 425 LABEL(v, _for='%s%s' % (field.name, k)))) 426 opts.append(child(tds)) 427 428 if opts: 429 opts[-1][0][0]['hideerror'] = False 430 return parent(*opts, **attr)

431

432 433 -class CheckboxesWidget(OptionsWidget):

434 435 @classmethod

437 """ 438 generates a TABLE tag, including INPUT checkboxes (multiple allowed) 439 440 see also: :meth:`FormWidget.widget` 441 """ 442 443 # was values = re.compile('[\w\-:]+').findall(str(value)) 444 if isinstance(value, (list, tuple)): 445 values = [str(v) for v in value] 446 else: 447 values = [str(value)] 448 449 attr = cls._attributes(field, {}, **attributes) 450 attr['_class'] = attr.get('_class', 'web2py_checkboxeswidget') 451 452 requires = field.requires 453 if not isinstance(requires, (list, tuple)): 454 requires = [requires] 455 if requires and hasattr(requires[0], 'options'): 456 options = requires[0].options() 457 else: 458 raise SyntaxError('widget cannot determine options of %s' 459 % field) 460 461 options = [(k, v) for k, v in options if k != ''] 462 opts = [] 463 cols = attributes.get('cols', 1) 464 totals = len(options) 465 mods = totals % cols 466 rows = totals / cols 467 if mods: 468 rows += 1 469 470 #widget style 471 wrappers = dict( 472 table=(TABLE, TR, TD), 473 ul=(DIV, UL, LI), 474 divs=(CAT, DIV, DIV) 475 ) 476 parent, child, inner = wrappers[attributes.get('style', 'table')] 477 478 for r_index in range(rows): 479 tds = [] 480 for k, v in options[r_index * cols:(r_index + 1) * cols]: 481 if k in values: 482 r_value = k 483 else: 484 r_value = [] 485 tds.append(inner(INPUT(_type='checkbox', 486 _id='%s%s' % (field.name, k), 487 _name=field.name, 488 requires=attr.get('requires', None), 489 hideerror=True, _value=k, 490 value=r_value), 491 LABEL(v, _for='%s%s' % (field.name, k)))) 492 opts.append(child(tds)) 493 494 if opts: 495 opts.append( 496 INPUT(requires=attr.get('requires', None), 497 _style="display:none;", 498 _disabled="disabled", 499 _name=field.name, 500 hideerror=False)) 501 return parent(*opts, **attr)

502

503 504 -class PasswordWidget(FormWidget):

505 _class = 'password' 506 507 DEFAULT_PASSWORD_DISPLAY = 8 * ('*') 508 509 @classmethod

511 """ 512 generates a INPUT password tag. 513 If a value is present it will be shown as a number of '*', not related 514 to the length of the actual value. 515 516 see also: :meth:`FormWidget.widget` 517 """ 518 # detect if attached a IS_STRONG with entropy 519 default = dict( 520 _type='password', 521 _value=(value and cls.DEFAULT_PASSWORD_DISPLAY) or '', 522 ) 523 attr = cls._attributes(field, default, **attributes) 524 output = CAT(INPUT(**attr)) 525 526 # deal with entropy check! 527 requires = field.requires 528 if not isinstance(requires, (list, tuple)): 529 requires = [requires] 530 is_strong = [r for r in requires if isinstance(r, IS_STRONG)] 531 if is_strong: 532 output.append(SCRIPT("web2py_validate_entropy(jQuery('#%s'),%s);" % ( 533 attr['_id'], is_strong[0].entropy 534 if is_strong[0].entropy else "null"))) 535 # end entropy check 536 return output

537

538 539 -class UploadWidget(FormWidget):

540 _class = 'upload' 541 542 DEFAULT_WIDTH = '150px' 543 ID_DELETE_SUFFIX = '__delete' 544 GENERIC_DESCRIPTION = 'file' 545 DELETE_FILE = 'delete' 546 547 @classmethod

549 """ 550 generates a INPUT file tag. 551 552 Optionally provides an A link to the file, including a checkbox so 553 the file can be deleted. 554 All is wrapped in a DIV. 555 556 see also: :meth:`FormWidget.widget` 557 558 :param download_url: Optional URL to link to the file (default = None) 559 """ 560 561 default = dict(_type='file',) 562 attr = cls._attributes(field, default, **attributes) 563 564 inp = INPUT(**attr) 565 566 if download_url and value: 567 if callable(download_url): 568 url = download_url(value) 569 else: 570 url = download_url + '/' + value 571 (br, image) = ('', '') 572 if UploadWidget.is_image(value): 573 br = BR() 574 image = IMG(_src=url, _width=cls.DEFAULT_WIDTH) 575 576 requires = attr["requires"] 577 if requires == [] or isinstance(requires, IS_EMPTY_OR): 578 inp = DIV(inp, 579 SPAN('[', 580 A(current.T( 581 UploadWidget.GENERIC_DESCRIPTION), _href=url), 582 '|', 583 INPUT(_type='checkbox', 584 _name=field.name + cls.ID_DELETE_SUFFIX, 585 _id=field.name + cls.ID_DELETE_SUFFIX), 586 LABEL(current.T(cls.DELETE_FILE), 587 _for=field.name + cls.ID_DELETE_SUFFIX, 588 _style='display:inline'), 589 ']', _style='white-space:nowrap'), 590 br, image) 591 else: 592 inp = DIV(inp, 593 SPAN('[', 594 A(cls.GENERIC_DESCRIPTION, _href=url), 595 ']', _style='white-space:nowrap'), 596 br, image) 597 return inp

598 599 @classmethod

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

601 """ 602 how to represent the file: 603 604 - with download url and if it is an image: <A href=...><IMG ...></A> 605 - otherwise with download url: <A href=...>file</A> 606 - otherwise: file 607 608 :param field: the field 609 :param value: the field value 610 :param download_url: url for the file download (default = None) 611 """ 612 613 inp = cls.GENERIC_DESCRIPTION 614 615 if download_url and value: 616 if callable(download_url): 617 url = download_url(value) 618 else: 619 url = download_url + '/' + value 620 if cls.is_image(value): 621 inp = IMG(_src=url, _width=cls.DEFAULT_WIDTH) 622 inp = A(inp, _href=url) 623 624 return inp

625 626 @staticmethod

627 - def is_image(value):

628 """ 629 Tries to check if the filename provided references to an image 630 631 Checking is based on filename extension. Currently recognized: 632 gif, png, jp(e)g, bmp 633 634 :param value: filename 635 """ 636 637 extension = value.split('.')[-1].lower() 638 if extension in ['gif', 'png', 'jpg', 'jpeg', 'bmp']: 639 return True 640 return False

641

642 643 -class AutocompleteWidget(object):

644 _class = 'string' 645

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

650 651 self.help_fields = help_fields or [] 652 self.help_string = help_string 653 if self.help_fields and not self.help_string: 654 self.help_string = ' '.join('%%(%s)s'%f.name 655 for f in self.help_fields) 656 657 self.request = request 658 self.keyword = keyword % dict(tablename=field.tablename, 659 fieldname=field.name) 660 self.db = db or field._db 661 self.orderby = orderby 662 self.limitby = limitby 663 self.distinct = distinct 664 self.min_length = min_length 665 self.fields = [field] 666 if id_field: 667 self.is_reference = True 668 self.fields.append(id_field) 669 else: 670 self.is_reference = False 671 if hasattr(request, 'application'): 672 self.url = URL(args=request.args) 673 self.callback() 674 else: 675 self.url = request

676

677 - def callback(self):

678 if self.keyword in self.request.vars: 679 field = self.fields[0] 680 if is_gae: 681 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_field)) 682 else: 683 rows = self.db(field.like(self.request.vars[self.keyword] + '%')).select(orderby=self.orderby, limitby=self.limitby, distinct=self.distinct, *(self.fields+self.help_field)) 684 if rows: 685 if self.is_reference: 686 id_field = self.fields[1] 687 if self.help_fields: 688 options = [OPTION( 689 self.help_string % dict([(h.name, s[h.name]) for h in self.fields[:1] + self.help_fields]), 690 _value=s[id_field.name], _selected=(k == 0)) for k, s in enumerate(rows)] 691 else: 692 options = [OPTION( 693 s[field.name], _value=s[id_field.name], 694 _selected=(k == 0)) for k, s in enumerate(rows)] 695 raise HTTP( 696 200, SELECT(_id=self.keyword, _class='autocomplete', 697 _size=len(rows), _multiple=(len(rows) == 1), 698 *options).xml()) 699 else: 700 raise HTTP( 701 200, SELECT(_id=self.keyword, _class='autocomplete', 702 _size=len(rows), _multiple=(len(rows) == 1), 703 *[OPTION(s[field.name], 704 _selected=(k == 0)) 705 for k, s in enumerate(rows)]).xml()) 706 else: 707 raise HTTP(200, '')

708

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

710 default = dict( 711 _type='text', 712 value=(not value is None and str(value)) or '', 713 ) 714 attr = StringWidget._attributes(field, default, **attributes) 715 div_id = self.keyword + '_div' 716 attr['_autocomplete'] = 'off' 717 if self.is_reference: 718 key2 = self.keyword + '_aux' 719 key3 = self.keyword + '_auto' 720 attr['_class'] = 'string' 721 name = attr['_name'] 722 if 'requires' in attr: 723 del attr['requires'] 724 attr['_name'] = key2 725 value = attr['value'] 726 record = self.db( 727 self.fields[1] == value).select(self.fields[0]).first() 728 attr['value'] = record and record[self.fields[0].name] 729 attr['_onblur'] = "jQuery('#%(div_id)s').delay(1000).fadeOut('slow');" % \ 730 dict(div_id=div_id, u='F' + self.keyword) 731 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');" % \ 732 dict(url=self.url, min_length=self.min_length, 733 key=self.keyword, id=attr['_id'], key2=key2, key3=key3, 734 name=name, div_id=div_id, u='F' + self.keyword) 735 if self.min_length == 0: 736 attr['_onfocus'] = attr['_onkeyup'] 737 return TAG[''](INPUT(**attr), INPUT(_type='hidden', _id=key3, _value=value, 738 _name=name, requires=field.requires), 739 DIV(_id=div_id, _style='position:absolute;')) 740 else: 741 attr['_name'] = field.name 742 attr['_onblur'] = "jQuery('#%(div_id)s').delay(1000).fadeOut('slow');" % \ 743 dict(div_id=div_id, u='F' + self.keyword) 744 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');" % \ 745 dict(url=self.url, min_length=self.min_length, 746 key=self.keyword, id=attr['_id'], div_id=div_id, u='F' + self.keyword) 747 if self.min_length == 0: 748 attr['_onfocus'] = attr['_onkeyup'] 749 return TAG[''](INPUT(**attr), DIV(_id=div_id, _style='position:absolute;'))

750

751 752 -def formstyle_table3cols(form, fields):

753 ''' 3 column table - default ''' 754 table = TABLE() 755 for id, label, controls, help in fields: 756 _help = TD(help, _class='w2p_fc') 757 _controls = TD(controls, _class='w2p_fw') 758 _label = TD(label, _class='w2p_fl') 759 table.append(TR(_label, _controls, _help, _id=id)) 760 return table

761

762 763 -def formstyle_table2cols(form, fields):

764 ''' 2 column table ''' 765 table = TABLE() 766 for id, label, controls, help in fields: 767 _help = TD(help, _class='w2p_fc', _width='50%') 768 _controls = TD(controls, _class='w2p_fw', _colspan='2') 769 _label = TD(label, _class='w2p_fl', _width='50%') 770 table.append(TR(_label, _help, _id=id + '1', _class='even')) 771 table.append(TR(_controls, _id=id + '2', _class='odd')) 772 return table

773

774 775 -def formstyle_divs(form, fields):

776 ''' divs only ''' 777 table = FIELDSET() 778 for id, label, controls, help in fields: 779 _help = DIV(help, _class='w2p_fc') 780 _controls = DIV(controls, _class='w2p_fw') 781 _label = DIV(label, _class='w2p_fl') 782 table.append(DIV(_label, _controls, _help, _id=id)) 783 return table

784

785 786 -def formstyle_inline(form, fields):

787 ''' divs only ''' 788 if len(fields) != 2: 789 raise RuntimeError("Not possible") 790 id, label, controls, help = fields[0] 791 submit_button = fields[1][2] 792 return CAT(DIV(controls, _style='display:inline'), 793 submit_button)

794

795 796 -def formstyle_ul(form, fields):

797 ''' unordered list ''' 798 table = UL() 799 for id, label, controls, help in fields: 800 _help = DIV(help, _class='w2p_fc') 801 _controls = DIV(controls, _class='w2p_fw') 802 _label = DIV(label, _class='w2p_fl') 803 table.append(LI(_label, _controls, _help, _id=id)) 804 return table

805

806 807 -def formstyle_bootstrap(form, fields):

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

850

851 852 -class SQLFORM(FORM):

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

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

938 if not status and self.record and self.errors: 939 ### if there are errors in update mode 940 # and some errors refers to an already uploaded file 941 # delete error if 942 # - user not trying to upload a new file 943 # - there is existing file and user is not trying to delete it 944 # this is because removing the file may not pass validation 945 for key in self.errors.keys(): 946 if key in self.table \ 947 and self.table[key].type == 'upload' \ 948 and request_vars.get(key, None) in (None, '') \ 949 and self.record[key] \ 950 and not key + UploadWidget.ID_DELETE_SUFFIX in request_vars: 951 del self.errors[key] 952 if not self.errors: 953 status = True 954 return status

955

956 - def __init__( 957 self, 958 table, 959 record=None, 960 deletable=False, 961 linkto=None, 962 upload=None, 963 fields=None, 964 labels=None, 965 col3={}, 966 submit_button='Submit', 967 delete_label='Check to delete', 968 showid=True, 969 readonly=False, 970 comments=True, 971 keepopts=[], 972 ignore_rw=False, 973 record_id=None, 974 formstyle='table3cols', 975 buttons=['submit'], 976 separator=': ', 977 **attributes 978 ):

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

1245

1246 - def createform(self, xfields):

1247 formstyle = self.formstyle 1248 if isinstance(formstyle, basestring): 1249 if formstyle in SQLFORM.formstyles: 1250 formstyle = SQLFORM.formstyles[formstyle] 1251 else: 1252 raise RuntimeError('formstyle not found') 1253 1254 if callable(formstyle): 1255 # backward compatibility, 4 argument function is the old style 1256 args, varargs, keywords, defaults = inspect.getargspec(formstyle) 1257 if defaults and len(args) - len(defaults) == 4 or len(args) == 4: 1258 table = TABLE() 1259 for id, a, b, c in xfields: 1260 newrows = formstyle(id, a, b, c) 1261 self.field_parent[id] = getattr(b, 'parent', None) \ 1262 if isinstance(b,XmlComponent) else None 1263 if type(newrows).__name__ != "tuple": 1264 newrows = [newrows] 1265 for newrow in newrows: 1266 table.append(newrow) 1267 else: 1268 table = formstyle(self, xfields) 1269 for id, a, b, c in xfields: 1270 self.field_parent[id] = getattr(b, 'parent', None) \ 1271 if isinstance(b,XmlComponent) else None 1272 else: 1273 raise RuntimeError('formstyle not supported') 1274 return table

1275

1276 - def accepts( 1277 self, 1278 request_vars, 1279 session=None, 1280 formname='%(tablename)s/%(record_id)s', 1281 keepvalues=None, 1282 onvalidation=None, 1283 dbio=True, 1284 hideerror=False, 1285 detect_record_change=False, 1286 **kwargs 1287 ):

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

1574 1575 AUTOTYPES = { 1576 type(''): ('string', None), 1577 type(True): ('boolean', None), 1578 type(1): ('integer', IS_INT_IN_RANGE(-1e12, +1e12)), 1579 type(1.0): ('double', IS_FLOAT_IN_RANGE()), 1580 type([]): ('list:string', None), 1581 type(datetime.date.today()): ('date', IS_DATE()), 1582 type(datetime.datetime.today()): ('datetime', IS_DATETIME()) 1583 } 1584 1585 @staticmethod

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

1587 fields = [] 1588 for key, value in sorted(dictionary.items()): 1589 t, requires = SQLFORM.AUTOTYPES.get(type(value), (None, None)) 1590 if t: 1591 fields.append(Field(key, t, requires=requires, 1592 default=value)) 1593 return SQLFORM.factory(*fields, **kwargs)

1594 1595 @staticmethod

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

1597 import os 1598 if query: 1599 session[name] = query.db(query).select().first().as_dict() 1600 elif os.path.exists(filename): 1601 env = {'datetime': datetime} 1602 session[name] = eval(open(filename).read(), {}, env) 1603 form = SQLFORM.dictform(session[name]) 1604 if form.process().accepted: 1605 session[name].update(form.vars) 1606 if query: 1607 query.db(query).update(**form.vars) 1608 else: 1609 open(filename, 'w').write(repr(session[name])) 1610 return form

1611 1612 @staticmethod

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

1614 """ 1615 generates a SQLFORM for the given fields. 1616 1617 Internally will build a non-database based data model 1618 to hold the fields. 1619 """ 1620 # Define a table name, this way it can be logical to our CSS. 1621 # And if you switch from using SQLFORM to SQLFORM.factory 1622 # your same css definitions will still apply. 1623 1624 table_name = attributes.get('table_name', 'no_table') 1625 1626 # So it won't interfere with SQLDB.define_table 1627 if 'table_name' in attributes: 1628 del attributes['table_name'] 1629 1630 return SQLFORM(DAL(None).define_table(table_name, *fields), 1631 **attributes)

1632 1633 @staticmethod

1634 - def build_query(fields, keywords):

1635 request = current.request 1636 if isinstance(keywords, (tuple, list)): 1637 keywords = keywords[0] 1638 request.vars.keywords = keywords 1639 key = keywords.strip() 1640 if key and not ' ' in key and not '"' in key and not "'" in key: 1641 SEARCHABLE_TYPES = ('string', 'text', 'list:string') 1642 parts = [field.contains( 1643 key) for field in fields if field.type in SEARCHABLE_TYPES] 1644 else: 1645 parts = None 1646 if parts: 1647 return reduce(lambda a, b: a | b, parts) 1648 else: 1649 return smart_query(fields, key)

1650 1651 @staticmethod

1652 - def search_menu(fields, 1653 search_options=None, 1654 prefix='w2p' 1655 ):

1656 T = current.T 1657 panel_id='%s_query_panel' % prefix 1658 fields_id='%s_query_fields' % prefix 1659 keywords_id='%s_keywords' % prefix 1660 field_id='%s_field' % prefix 1661 value_id='%s_value' % prefix 1662 search_options = search_options or { 1663 'string': ['=', '!=', '<', '>', '<=', '>=', 'starts with', 'contains', 'in', 'not in'], 1664 'text': ['=', '!=', '<', '>', '<=', '>=', 'starts with', 'contains', 'in', 'not in'], 1665 'date': ['=', '!=', '<', '>', '<=', '>='], 1666 'time': ['=', '!=', '<', '>', '<=', '>='], 1667 'datetime': ['=', '!=', '<', '>', '<=', '>='], 1668 'integer': ['=', '!=', '<', '>', '<=', '>=', 'in', 'not in'], 1669 'double': ['=', '!=', '<', '>', '<=', '>='], 1670 'id': ['=', '!=', '<', '>', '<=', '>=', 'in', 'not in'], 1671 'reference': ['=', '!='], 1672 'boolean': ['=', '!=']} 1673 if fields[0]._db._adapter.dbengine == 'google:datastore': 1674 search_options['string'] = ['=', '!=', '<', '>', '<=', '>='] 1675 search_options['text'] = ['=', '!=', '<', '>', '<=', '>='] 1676 search_options['list:string'] = ['contains'] 1677 search_options['list:integer'] = ['contains'] 1678 search_options['list:reference'] = ['contains'] 1679 criteria = [] 1680 selectfields = [] 1681 for field in fields: 1682 name = str(field).replace('.', '-') 1683 # treat ftype 'decimal' as 'double' 1684 # (this fixes problems but needs refactoring! 1685 ftype = field.type.split(' ')[0] 1686 if ftype.startswith('decimal'): ftype = 'double' 1687 elif ftype=='bigint': ftype = 'integer' 1688 elif ftype.startswith('big-'): ftype = ftype[4:] 1689 # end 1690 options = search_options.get(ftype, None) 1691 if options: 1692 label = isinstance( 1693 field.label, str) and T(field.label) or field.label 1694 selectfields.append(OPTION(label, _value=str(field))) 1695 operators = SELECT(*[OPTION(T(option), _value=option) for option in options]) 1696 _id = "%s_%s" % (value_id,name) 1697 if field.type == 'boolean': 1698 value_input = SQLFORM.widgets.boolean.widget(field,field.default,_id=_id) 1699 elif field.type == 'double': 1700 value_input = SQLFORM.widgets.double.widget(field,field.default,_id=_id) 1701 elif field.type == 'time': 1702 value_input = SQLFORM.widgets.time.widget(field,field.default,_id=_id) 1703 elif field.type == 'date': 1704 value_input = SQLFORM.widgets.date.widget(field,field.default,_id=_id) 1705 elif field.type == 'datetime': 1706 value_input = SQLFORM.widgets.datetime.widget(field,field.default,_id=_id) 1707 elif (field.type.startswith('reference ') or 1708 field.type.startswith('list:reference ')) and \ 1709 hasattr(field.requires,'options'): 1710 value_input = SELECT( 1711 *[OPTION(v, _value=k) 1712 for k,v in field.requires.options()], 1713 **dict(_id=_id)) 1714 elif field.type == 'integer' or \ 1715 field.type.startswith('reference ') or \ 1716 field.type.startswith('list:integer') or \ 1717 field.type.startswith('list:reference '): 1718 value_input = SQLFORM.widgets.integer.widget(field,field.default,_id=_id) 1719 else: 1720 value_input = INPUT( 1721 _type='text', _id=_id, _class=field.type) 1722 1723 new_button = INPUT( 1724 _type="button", _value=T('New'), _class="btn", 1725 _onclick="%s_build_query('new','%s')" % (prefix,field)) 1726 and_button = INPUT( 1727 _type="button", _value=T('And'), _class="btn", 1728 _onclick="%s_build_query('and','%s')" % (prefix, field)) 1729 or_button = INPUT( 1730 _type="button", _value=T('Or'), _class="btn", 1731 _onclick="%s_build_query('or','%s')" % (prefix, field)) 1732 close_button = INPUT( 1733 _type="button", _value=T('Close'), _class="btn", 1734 _onclick="jQuery('#%s').slideUp()" % panel_id) 1735 1736 criteria.append(DIV( 1737 operators, value_input, new_button, 1738 and_button, or_button, close_button, 1739 _id='%s_%s' % (field_id, name), 1740 _class='w2p_query_row hidden', 1741 _style='display:inline')) 1742 1743 criteria.insert(0, SELECT( 1744 _id=fields_id, 1745 _onchange="jQuery('.w2p_query_row').hide();jQuery('#%s_'+jQuery('#%s').val().replace('.','-')).show();" % (field_id,fields_id), 1746 _style='float:left', 1747 *selectfields)) 1748 1749 fadd = SCRIPT(""" 1750 jQuery('#%(fields_id)s input,#%(fields_id)s select').css( 1751 'width','auto'); 1752 jQuery(function(){web2py_ajax_fields('#%(fields_id)s');}); 1753 function %(prefix)s_build_query(aggregator,a) { 1754 var b=a.replace('.','-'); 1755 var option = jQuery('#%(field_id)s_'+b+' select').val(); 1756 var value = jQuery('#%(value_id)s_'+b).val().replace('"','\\\\"'); 1757 var s=a+' '+option+' "'+value+'"'; 1758 var k=jQuery('#%(keywords_id)s'); 1759 var v=k.val(); 1760 if(aggregator=='new') k.val(s); else k.val((v?(v+' '+ aggregator +' '):'')+s); 1761 } 1762 """ % dict( 1763 prefix=prefix,fields_id=fields_id,keywords_id=keywords_id, 1764 field_id=field_id,value_id=value_id 1765 ) 1766 ) 1767 return CAT( 1768 DIV(_id=panel_id, _style="display:none;", *criteria), fadd)

1769 1770 1771 @staticmethod

1772 - def grid(query, 1773 fields=None, 1774 field_id=None, 1775 left=None, 1776 headers={}, 1777 orderby=None, 1778 groupby=None, 1779 searchable=True, 1780 sortable=True, 1781 paginate=20, 1782 deletable=True, 1783 editable=True, 1784 details=True, 1785 selectable=None, 1786 create=True, 1787 csv=True, 1788 links=None, 1789 links_in_grid=True, 1790 upload='<default>', 1791 args=[], 1792 user_signature=True, 1793 maxtextlengths={}, 1794 maxtextlength=20, 1795 onvalidation=None, 1796 onfailure=None, 1797 oncreate=None, 1798 onupdate=None, 1799 ondelete=None, 1800 sorter_icons=(XML('↑'), XML('↓')), 1801 ui = 'web2py', 1802 showbuttontext=True, 1803 _class="web2py_grid", 1804 formname='web2py_grid', 1805 search_widget='default', 1806 ignore_rw = False, 1807 formstyle = 'table3cols', 1808 exportclasses = None, 1809 formargs={}, 1810 createargs={}, 1811 editargs={}, 1812 viewargs={}, 1813 selectable_submit_button='Submit', 1814 buttons_placement = 'right', 1815 links_placement = 'right', 1816 noconfirm=False, 1817 cache_count=None, 1818 client_side_delete=False, 1819 ):

1820 1821 # jQuery UI ThemeRoller classes (empty if ui is disabled) 1822 if ui == 'jquery-ui': 1823 ui = dict(widget='ui-widget', 1824 header='ui-widget-header', 1825 content='ui-widget-content', 1826 default='ui-state-default', 1827 cornerall='ui-corner-all', 1828 cornertop='ui-corner-top', 1829 cornerbottom='ui-corner-bottom', 1830 button='ui-button-text-icon-primary', 1831 buttontext='ui-button-text', 1832 buttonadd='ui-icon ui-icon-plusthick', 1833 buttonback='ui-icon ui-icon-arrowreturnthick-1-w', 1834 buttonexport='ui-icon ui-icon-transferthick-e-w', 1835 buttondelete='ui-icon ui-icon-trash', 1836 buttonedit='ui-icon ui-icon-pencil', 1837 buttontable='ui-icon ui-icon-triangle-1-e', 1838 buttonview='ui-icon ui-icon-zoomin', 1839 ) 1840 elif ui == 'web2py': 1841 ui = dict(widget='', 1842 header='', 1843 content='', 1844 default='', 1845 cornerall='', 1846 cornertop='', 1847 cornerbottom='', 1848 button='button btn', 1849 buttontext='buttontext button', 1850 buttonadd='icon plus icon-plus', 1851 buttonback='icon leftarrow icon-arrow-left', 1852 buttonexport='icon downarrow icon-download', 1853 buttondelete='icon trash icon-trash', 1854 buttonedit='icon pen icon-pencil', 1855 buttontable='icon rightarrow icon-arrow-right', 1856 buttonview='icon magnifier icon-zoom-in', 1857 ) 1858 elif not isinstance(ui, dict): 1859 raise RuntimeError('SQLFORM.grid ui argument must be a dictionary') 1860 1861 db = query._db 1862 T = current.T 1863 request = current.request 1864 session = current.session 1865 response = current.response 1866 logged = session.auth and session.auth.user 1867 wenabled = (not user_signature or logged) 1868 create = wenabled and create 1869 editable = wenabled and editable 1870 deletable = wenabled and deletable 1871 rows = None 1872 1873 def fetch_count(dbset): 1874 ##FIXME for google:datastore cache_count is ignored 1875 ## if it's not an integer 1876 if cache_count is None or isinstance(cache_count, tuple): 1877 if groupby: 1878 c = 'count(*)' 1879 nrows = db.executesql( 1880 'select count(*) from (%s);' % 1881 dbset._select(c, left=left, cacheable=True, 1882 groupby=groupby, cache=cache_count)[:-1])[0][0] 1883 elif left: 1884 c = 'count(*)' 1885 nrows = dbset.select(c, left=left, cacheable=True, cache=cache_count).first()[c] 1886 elif dbset._db._adapter.dbengine=='google:datastore': 1887 #if we don't set a limit, this can timeout for a large table 1888 nrows = dbset.db._adapter.count(dbset.query, limit=1000) 1889 else: 1890 nrows = dbset.count(cache=cache_count) 1891 elif isinstance(cache_count, (int, long)): 1892 nrows = cache_count 1893 elif callable(cache_count): 1894 nrows = cache_count(dbset, request.vars) 1895 else: 1896 nrows = 0 1897 return nrows

1898 1899 def url(**b): 1900 b['args'] = args + b.get('args', []) 1901 localvars = request.get_vars.copy() 1902 localvars.update(b.get('vars', {})) 1903 b['vars'] = localvars 1904 b['hash_vars'] = False 1905 b['user_signature'] = user_signature 1906 return URL(**b)

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

2503 - def smartgrid(table, constraints=None, linked_tables=None, 2504 links=None, links_in_grid=True, 2505 args=None, user_signature=True, 2506 divider='>', breadcrumbs_class='', 2507 **kwargs):

2508 """ 2509 @auth.requires_login() 2510 def index(): 2511 db.define_table('person',Field('name'),format='%(name)s') 2512 db.define_table('dog', 2513 Field('name'),Field('owner',db.person),format='%(name)s') 2514 db.define_table('comment',Field('body'),Field('dog',db.dog)) 2515 if db(db.person).isempty(): 2516 from gluon.contrib.populate import populate 2517 populate(db.person,300) 2518 populate(db.dog,300) 2519 populate(db.comment,1000) 2520 db.commit() 2521 form=SQLFORM.smartgrid(db[request.args(0) or 'person']) #*** 2522 return dict(form=form) 2523 2524 *** builds a complete interface to navigate all tables links 2525 to the request.args(0) 2526 table: pagination, search, view, edit, delete, 2527 children, parent, etc. 2528 2529 constraints is a dict {'table':query} that limits which 2530 records can be accessible 2531 links is a dict like 2532 {'tablename':[lambda row: A(....), ...]} 2533 that will add buttons when table tablename is displayed 2534 linked_tables is a optional list of tablenames of tables 2535 to be linked 2536 """ 2537 request, T = current.request, current.T 2538 if args is None: 2539 args = [] 2540 2541 def url(**b): 2542 b['args'] = request.args[:nargs] + b.get('args', []) 2543 b['hash_vars'] = False 2544 b['user_signature'] = user_signature 2545 return URL(**b)

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

2703 2704 -class SQLTABLE(TABLE):

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

2780 - def __init__( 2781 self, 2782 sqlrows, 2783 linkto=None, 2784 upload=None, 2785 orderby=None, 2786 headers={}, 2787 truncate=16, 2788 columns=None, 2789 th_link='', 2790 extracolumns=None, 2791 selectid=None, 2792 renderstyle=False, 2793 cid=None, 2794 **attributes 2795 ):

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

2964

2965 - def style(self):

2966 2967 css = ''' 2968 table tbody tr.odd { 2969 background-color: #DFD; 2970 } 2971 table tbody tr.even { 2972 background-color: #EFE; 2973 } 2974 table tbody tr.rowselected { 2975 background-color: #FDD; 2976 } 2977 table tbody tr td.colselected { 2978 background-color: #FDD; 2979 } 2980 table tbody tr:hover { 2981 background: #DDF; 2982 } 2983 ''' 2984 2985 return css

2986 2987 form_factory = SQLFORM.factory # for backward compatibility, deprecated

2988 2989 2990 -class ExportClass(object):

2991 label = None 2992 file_ext = None 2993 content_type = None 2994

2995 - def __init__(self, rows):

2996 self.rows = rows

2997

2998 - def represented(self):

2999 def none_exception(value): 3000 """ 3001 returns a cleaned up value that can be used for csv export: 3002 - unicode text is encoded as such 3003 - None values are replaced with the given representation (default <NULL>) 3004 """ 3005 if value is None: 3006 return '<NULL>' 3007 elif isinstance(value, unicode): 3008 return value.encode('utf8') 3009 elif isinstance(value, Reference): 3010 return int(value) 3011 elif hasattr(value, 'isoformat'): 3012 return value.isoformat()[:19].replace('T', ' ') 3013 elif isinstance(value, (list, tuple)): # for type='list:..' 3014 return bar_encode(value) 3015 return value

3016 3017 represented = [] 3018 for record in self.rows: 3019 row = [] 3020 for col in self.rows.colnames: 3021 if not REGEX_TABLE_DOT_FIELD.match(col): 3022 row.append(record._extra[col]) 3023 else: 3024 (t, f) = col.split('.') 3025 field = self.rows.db[t][f] 3026 if isinstance(record.get(t, None), (Row, dict)): 3027 value = record[t][f] 3028 else: 3029 value = record[f] 3030 if field.type == 'blob' and not value is None: 3031 value = '' 3032 elif field.represent: 3033 value = field.represent(value, record) 3034 row.append(none_exception(value)) 3035 3036 represented.append(row) 3037 return represented

3038

3039 - def export(self):

3040 raise NotImplementedError

3041

3042 3043 -class ExporterTSV(ExportClass):

3044 3045 label = 'TSV' 3046 file_ext = "csv" 3047 content_type = "text/tab-separated-values" 3048

3049 - def __init__(self, rows):

3050 ExportClass.__init__(self, rows)

3051

3052 - def export(self):

3053 3054 out = cStringIO.StringIO() 3055 final = cStringIO.StringIO() 3056 import csv 3057 writer = csv.writer(out, delimiter='\t') 3058 if self.rows: 3059 import codecs 3060 final.write(codecs.BOM_UTF16) 3061 writer.writerow( 3062 [unicode(col).encode("utf8") for col in self.rows.colnames]) 3063 data = out.getvalue().decode("utf8") 3064 data = data.encode("utf-16") 3065 data = data[2:] 3066 final.write(data) 3067 out.truncate(0) 3068 records = self.represented() 3069 for row in records: 3070 writer.writerow( 3071 [str(col).decode('utf8').encode("utf-8") for col in row]) 3072 data = out.getvalue().decode("utf8") 3073 data = data.encode("utf-16") 3074 data = data[2:] 3075 final.write(data) 3076 out.truncate(0) 3077 return str(final.getvalue())

3078

3079 3080 -class ExporterCSV(ExportClass):

3081 label = 'CSV' 3082 file_ext = "csv" 3083 content_type = "text/csv" 3084

3085 - def __init__(self, rows):

3086 ExportClass.__init__(self, rows)

3087

3088 - def export(self):

3089 if self.rows: 3090 return self.rows.as_csv() 3091 else: 3092 return ''

3093

3094 -class ExporterHTML(ExportClass):

3095 label = 'HTML' 3096 file_ext = "html" 3097 content_type = "text/html" 3098

3099 - def __init__(self, rows):

3100 ExportClass.__init__(self, rows)

3101

3102 - def export(self):

3103 if self.rows: 3104 return self.rows.xml() 3105 else: 3106 return '<html>\n<body>\n<table>\n</table>\n</body>\n</html>'

3107

3108 -class ExporterXML(ExportClass):

3109 label = 'XML' 3110 file_ext = "xml" 3111 content_type = "text/xml" 3112

3113 - def __init__(self, rows):

3114 ExportClass.__init__(self, rows)

3115

3116 - def export(self):

3117 if self.rows: 3118 return self.rows.as_xml() 3119 else: 3120 return '<rows></rows>'

3121

3122 -class ExporterJSON(ExportClass):

3123 label = 'JSON' 3124 file_ext = "json" 3125 content_type = "application/json" 3126

3127 - def __init__(self, rows):

3128 ExportClass.__init__(self, rows)

3129

3130 - def export(self):

3131 if self.rows: 3132 return self.rows.as_json() 3133 else: 3134 return 'null'

3135

Source Code for Module gluon.sqlhtml