4. SQLEDITABLE Document

4.2. Acknowledgement

This software is using the code of editable-table:http://mindmup.github.io/editable-table/ .

4.3. SQLEDITABLE - Basic usage

First, download SQLEDITABLE plugin file from link below , and setup to your application.

https://github.com/hiho-/SQLEDITABLE/releases

  • web2py.plugin.sqleditable.w2p - plugin file
  • web2py.app.demo.w2p - demo app

In model (ex.db.py) or controller (ex.default.py) , need to add as following.

from plugin_sqleditable.editable import SQLEDITABLE
SQLEDITABLE.init()

After, you write same as SQLFORM .

The following is a sample.

Model

db.define_table('employee_sheet',
    Field('employee_number','integer',length=5,label='Emp.no.'),
    Field('name','string',length=50,notnull=True),
    Field('date_employment','date',label='Enter',requires=IS_DATE()),
    Field('employee_section','string',label='Section',
    requires=IS_IN_SET({'s':'sales','p':'production','d':'development',
                                                'c':'control'})),
    Field('employee_comment','string',label='Comment'),
    Field('date_resignation','date',label='Exit',readable=False,
                                            requires=IS_EMPTY_OR(IS_DATE())),
    Field('resigned','boolean', writable=False, default=False),
    Field('remuneration','decimal(12,2)', readable=False),
    Field('currency','string',readable=False,
                                requires=IS_IN_SET(['USD','EUR','GBP','JPY'])))

Controller

# coding: utf8
from plugin_sqleditable.editable import SQLEDITABLE
SQLEDITABLE.init()

def demo010():
    response.title = 'demo010'
    response.view = 'plugin_sqleditable/sample.html'
    editable = SQLEDITABLE(db.employee_sheet, showid=False, maxrow=5).process()
    return dict(editable=editable)

At the beginning of the controller file, you should describe SQLEDITABLE.init() .

Following field types that currently are supported.

interger , double , float , decimal , string , boolean , date , datetime , time

Also, IS_IN_SET validator are described, the listbox will be displayed.

4.4. Features of SQLEDITABLE

4.4.1. Notation similar to SQLFORM

You can use process method.

def demo010():
    response.title = 'demo010'
    response.view = 'plugin_sqleditable/sample.html'
    editable = SQLEDITABLE(db.employee_sheet, showid=False, maxrow=5).process()
    return dict(editable=editable)

Excluding onvalidation, keepvalues and detect_record_change, same parameters of the process method is available in SQLEDITABLE also.

For example, I will rewrite the Flash message.

def demo015():
    response.title = 'demo015'
    response.view = 'plugin_sqleditable/sample.html'
    editable = SQLEDITABLE(db.employee_sheet, showid=False, maxrow=5).process(
                        message_onsuccess='success', message_onfailure='error')
    return dict(editable=editable)

In addition. Because there is no parameter detect_record_change, it does not appear the message even if you set message_onchange parameter.

You can use accepts method.

def demo013():
    response.title = 'demo013'
    response.view = 'plugin_sqleditable/sample.html'
    editable = SQLEDITABLE(db.employee_sheet, showid=False, maxrow=5)
    if editable.accepts(request.vars, session):
        response.flash = T('editable accepted')
    elif editable.errors:
        response.flash = T('editable has errors')
    else:
        response.flash = T('please fill out the editable')
    return dict(editable=editable)

You can use accepted attribute.

def demo014():
    response.title = 'demo014'
    response.view = 'plugin_sqleditable/sample.html'
    editable = SQLEDITABLE(db.employee_sheet, showid=False, maxrow=5)
    if editable.process().accepted:
        response.flash = T('editable accepted')
    elif editable.errors:
        response.flash = T('editable has errors')
    else:
        response.flash = T('please fill out the editable')
    return dict(editable=editable)

You can describe the logic in accepts method and accepted attribute. But redirect does not work in the processing of ajax. please use the next parameter.

4.4.2. Key operation

SQLEDITABLE is capable of cell movement of the focus with the arrow keys. The focus will also move by TAB key.

In the cell that has focus, you can either enter a value directly, switch to input mode by pressing the ENTER key.

Again, pressing the ENTER key, the focus will be returned.

In the cell there is a Select box, move the focus by the ENTER key, it is possible to change the value by the SPACE key and arrow keys. In the case of vertical=False, depending on the browser, key operation of the Select box does not work.

Calendar does not appear on the key operation. You need to click.

4.4.3. 2 types of validator

There are 2 types of validator in SQLEDITABLE.

  • Javascript validator

    The field which type is number or there is a specified number of digits, javascript validate will work. Input values that fall outside the conditions does not accept input. If validated javascript is not required, you specify the validate_js=False.

  • web2py validator

    When you press the OK button, validate each field in ajax within.

4.4.4. 3 types of hash

SQLEDITABLE is used 3 types of the hash value, to update DB and prevent tampering.

  1. Input hash
    A hash value of initial value. This will detect the input.
  2. Record hash
    A hash value of a record in the DB. This will detect the change of DB record. If the DB record and record hash does not match, update and delete does not take place.
  3. Table hash
    A hash value of the hash key and record. This prevents tampering of the table. Table hash value is stored in the session. If the hash table does not match, an error will occur.

4.5. Class and Method

class editable.SQLEDITABLE

This will generate a SQLEDITABLE object.

editable = SQLEDITABLE(db.employee_sheet, [1,2,3], showid=False, maxrow=5)
Parameters:
  • table – Table object
  • record – Specifying the record id to display (list,tuple,Rows object that contains keys). Primary key is specified in the list of the list, if more than one. All the records will select, if not specified.
  • deletable – Display setting for deleting record checkbox. Default is False.
  • header – Set of header information. Default is None. If this is None, collect automatic from Table object.
  • maxrow – Display number of rows. Default is None. If this is None, display all the relevant record.
  • lineno – Display line number. Default is True.
  • showid – Display key value of a record. Default is True.
  • editid – Edit key value of a record. Default is False.
  • validate_js – javascript validate. Default is True.
  • oninit – Specified function to be called during initialization. Default is None.
  • touch_device – The HTML5 widget use determination to the calendar and time selection. Default is Auto. True is to HTML5 widget use, False is to do not use, Auto is automatically determined.

Show some sample of the parameters.

record

Use as filter

def demo031():
    def record():
        rows = db(db.employee_sheet.resigned==True).select()
        return [row.id for row in rows]

    response.title = 'demo031'
    response.view = 'plugin_sqleditable/sample.html'
    editable = SQLEDITABLE(db.employee_sheet, record=record).process()
    return dict(editable=editable)

Use as filter (Rows object)

def demo033():
    def record():
        rows = db(db.employee_sheet.resigned==True).select()
        return rows

    response.title = 'demo031'
    response.view = 'plugin_sqleditable/sample.html'
    editable = SQLEDITABLE(db.employee_sheet, record=record).process()
    return dict(editable=editable)

Use as sort

def demo032():
    def record():
        rows = db(db.employee_sheet).select(orderby=db.employee_sheet.name)
        return [row.id for row in rows]

    response.title = 'demo032'
    response.view = 'plugin_sqleditable/sample.html'
    editable = SQLEDITABLE(db.employee_sheet, record=record).process()
    return dict(editable=editable)

Primary key for multiple

db.define_table('scadule_sheet2',
    Field('scadule_date','date',label='date'),
    Field('scadule_time','time',label='time'),
    Field('term','integer'),
    Field('term_unit','string',
    requires=IS_IN_SET({'min':'minute','hr':'hour','d':'day','wk':'week',
                                                                'mo':'month'})),
    Field('detail','string',length=30,requires=IS_NOT_EMPTY()),
    primarykey=['scadule_date','scadule_time'])

def demo080():
    response.title = 'demo080'
    response.view = 'plugin_sqleditable/sample.html'
    record = [['2014-07-01','00:00:00'], ['2014-07-02','00:01:00']]
    editable = SQLEDITABLE(db.scadule_sheet2, record=record, editid=True, maxrow=8).process()
    return dict(editable=editable)
oninit
def demo016():
    def oninit(editable):
        editable.table_class = 'table table-condensed'
        editable.ajax_button_value = T('Save')
        editable.lineno_label = T('Line')
        editable.deleteable_label = T('Delete')

    response.title = 'demo016'
    response.view = 'plugin_sqleditable/sample.html'
    editable = SQLEDITABLE(db.employee_sheet, showid=False, maxrow=5,
                                        deletable=True, oninit=oninit).process()
    return dict(editable=editable)

It will change class of table, text of ajax button, label of line number, label of delete flag.

def demo017():
    def oninit(editable):
        editable.msg_process_dialog = T('please wait', lazy=False)

    response.title = 'demo017'
    response.view = 'plugin_sqleditable/sample.html'
    editable = SQLEDITABLE(db.employee_sheet, showid=False, maxrow=5,
                                                        oninit=oninit).process()
    return dict(editable=editable)

To change the dialog message (If you specify the dialog object, can be changed in the dialog design).

SQLEDITABLE.init()

Make the environment definition of SQLEDITABLE. It is a static method. Before the action is called, you need to write.

from plugin_sqleditable.editable import SQLEDITABLE
SQLEDITABLE.init()
SQLEDITABLE.process()
SQLEDITABLE.accepts()

In these methods, Most of SQLFORM parameter and attributes are available. However, there are exceptions as follows.

  • onvalidation (onsuccess, onfailure, onchange) - Not available.
  • keepvalues - Not available. The new record, the default value of the model will be used.
  • dbio - Not available.
  • detect_record_change - Not available. In standard, it detect to change.
  • next - next parameter is also available accepts, not only the process method.
SQLEDITABLE.as_dict()

It passed as a dictionary object SQLEDITABLE. Dictionary data is as follow.

  • editable - Table
  • button - ajax button
  • script - javascript
def demo040():
    response.title = 'demo040'
    response.view = 'plugin_sqleditable/sample_as_dict.html'
    editable = SQLEDITABLE(db.employee_sheet, showid=False, maxrow=5).process()
    return editable.as_dict()