3. SQLEDITABLE Demo¶

Table of Contents

Information
SQLEDITABLE - Basic usage

3.1. Information ¶

Repository : https://github.com/hiho-/SQLEDITABLE
Issues : https://github.com/hiho-/SQLEDITABLE/issues
Document : SQLEDITABLE Document
Checked in : Chrome35, Firefox30, InternetExplorer11(document-mode edge,10,9,8,7), Silk
License : LGPLv3

3.2. 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 .

3.2.1. Demo1 ¶

Demo010 - Click!

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

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)

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. Now, IS_IN_DB is supported(Demo13 - IS_IN_DB).

3.2.2. Demo2 - vertical ¶

Demo011 - Click!

controller

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

vertical=False is displayed in the horizontal direction.

3.2.3. Demo3 - validate ¶

Demo012 - Click!

controller

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

validate_js=False is disabled to javascript validate.

3.2.4. Demo4 - deletable ¶

Demo020 - Click!

controller

def demo020():
    db.employee_sheet.date_resignation.readable = True
    db.employee_sheet.resigned.writable = True
    db.employee_sheet.remuneration.readable = True
    db.employee_sheet.currency.readable = True

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

It changed to some attribute of the table before SQLEDITABLE setting. And, deletable=True is to display the deletable checkbox.

3.2.5. Demo5 - record ¶

Demo030 - Click!

controller

def demo030():
    def record():
        rows = db(db.employee_sheet).select(
                    orderby=~db.employee_sheet.employee_number, limitby=(0,3))
        return [row.id for row in rows.sort(lambda row:row.employee_number)]

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

record is to specify for record id. Usually list value is specified, but this parameter is callable, so that it is possible to write some DAL code.

3.2.6. Demo6 - as_dict ¶

Demo040 - Click!
controller
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()
as_dict() is to pass dict value for the view. In the sample view, it have processed of each value editable , button , script . {{=button}} will work even if many same code placed on the view. (changed: 2015-02)
<table class='col-md-12'>
<tr><td> {{=button}}   </td></tr>
<tr><td> {{=editable}} </td></tr>
</table>
{{block head}}
{{super}}
{{=script}}
{{end}}

3.2.7. Demo7 - page transition ¶

Demo050 - Click!

controller

def demo050():
    response.title = 'demo050'
    response.view = 'plugin_sqleditable/sample.html'
    editable = SQLEDITABLE(db.employee_sheet, showid=False, maxrow=5,
                deletable=True).process(next=URL('static', 'success.html'))
    return dict(editable=editable)

next_js=URL(‘static’, ‘success.html’) next=URL('static', 'success.html') redirect to the success page.(changed:2014-07-02)

3.2.9. Demo9 - touch device ¶

Demo070  - Click!
Demo071  - Click!
Demo072  - Click!
Demo073  - Click!

controller

def demo070():
    response.title = 'demo070'
    response.view = 'plugin_sqleditable/sample.html'
    editable = SQLEDITABLE(db.scadule_sheet, showid=False, maxrow=8,
                                    deletable=True, touch_device=True).process()
    return dict(editable=editable)

def demo071():
    response.title = 'demo071'
    response.view = 'plugin_sqleditable/sample.html'
    editable = SQLEDITABLE(db.scadule_sheet, showid=False, maxrow=8,
                                deletable=True, touch_device=False).process()
    return dict(editable=editable)

def demo072():
    response.title = 'demo072'
    response.view = 'plugin_sqleditable/sample.html'
    editable = SQLEDITABLE(db.scadule_sheet, showid=False, maxrow=8,
                                deletable=True, touch_device='Auto').process()
    return dict(editable=editable)

def demo073():
    response.title = 'demo073'
    response.view = 'plugin_sqleditable/sample.html'
    editable = SQLEDITABLE(db.scadule_sheet, showid=False, maxrow=8,
                                                    deletable=True).process()
    return dict(editable=editable)

In the field of date , time and datetime , will change the input method for touch devices. If you do not specify or Auto to touch_device parameter, will check request.user_agent . You can use the browser widget HTML5 supported, rather than the web2py widget.

3.2.10. Demo10 - virtual field ¶

Demo090 - Click!

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'])))

class san(object):
    def san(self):
        return self.employee_sheet.name + '-san'
db.employee_sheet.virtualfields.append(san())
db.employee_sheet.san2 = Field.Virtual(lambda row: row.employee_sheet.name + '-SAN')

controller

def demo090():
    response.title = 'demo090'
    response.view = 'plugin_sqleditable/sample.html'
    header = ['employee_number','name','san','san2']
    editable = SQLEDITABLE(db.employee_sheet, header=header, showid=False, maxrow=5, deletable=True).process()
    return dict(editable=editable)

It will display the virtual field. This need specify what you want to display some fields in header parameter.

To display labels with virtual fields, use field and label .

def demo090():
        response.title = 'demo090'
        response.view = 'plugin_sqleditable/sample.html'
        header = ['employee_number','name',{'field':'san2','label':'xxxx'}]
        editable = SQLEDITABLE(db.employee_sheet, header=header, showid=False, maxrow=5,
                                deletable=True).process()
        return dict(editable=editable)

Use a dictionary type, specify a virtual field for field , and specify label text for label .

It does not work with the old type virtual field ( san of the sample is an old type).

3.2.11. Demo11 - anchor link ¶

Demo091 - Click!

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'])))

db.employee_sheet.history = Field.Virtual(lambda row: A('History',
                                _href=URL(f='demo092', args=row.employee_sheet.id)))

db.define_table('history_in_house',
    Field('employee',db.employee_sheet),
    Field('date_history','date',label='Enter',requires=IS_DATE()),
    Field('employee_section','string',label='Section',
    requires=IS_IN_SET({'s':'sales','p':'production','d':'development',
                                                'c':'control'})))

controller

def demo091():
    response.title = 'demo091'
    response.view = 'plugin_sqleditable/sample.html'
    header = ['employee_number','name','history']
    editable = SQLEDITABLE(db.employee_sheet, header=header, showid=False, maxrow=5,
                                                            deletable=True).process()
    return dict(editable=editable)

def demo092():
    def record():
        session.employee = request.args(0)
        rows = db(db.history_in_house.employee==request.args(0)).select(
                    orderby=~db.history_in_house.date_history, limitby=(0,4))
        return [row.id for row in rows.sort(lambda row:row.date_history)]

    def onvalidation(form):
        form.vars.employee = session.employee

    db.history_in_house.employee.readable = False
    response.title = 'demo092'
    response.view = 'plugin_sqleditable/sample.html'
    editable = SQLEDITABLE(db.history_in_house, record=record, showid=False, maxrow=5,
                                     deletable=True).process(onvalidation=onvalidation)
    return dict(editable=editable)

Use the virtual field for display the anchor link. This need specify what you want to display some fields in header parameter. In the linked table, it need to display some relevant records only. So, it is specified a onvalidation parameter in the process method. There are specified the URL parameter as the employee field value in the onvalidation-function. (In the record-function, there are passed value to the session)

In addition, if you change the virtual field as follows, it is also possible to use a button parts.

db.employee_sheet.history = Field.Virtual(lambda row: BUTTON('Hisotry',
_onClick="location.href='%s'" % URL(f='demo092', args=row.employee_sheet.id)))

If you do not use the onvalidation parameter, it is also possible to describe the demo092 controller as follows. Sets the default value for the field (line 9).

def demo092():
    def record():
        session.employee = request.args(0)
        rows = db(db.history_in_house.employee==request.args(0)).select(
                    orderby=~db.history_in_house.date_history, limitby=(0,4))
        return [row.id for row in rows.sort(lambda row:row.date_history)]

    db.history_in_house.employee.readable = False
    db.history_in_house.employee.default = session.employee
    response.title = 'demo092'
    response.view = 'plugin_sqleditable/sample.html'
    editable = SQLEDITABLE(db.history_in_house, record=record, showid=False, maxrow=5,
                                                               deletable=True).process()
    return dict(editable=editable)

3.2.12. Demo12 - onvalidation ¶

Demo100 - Click!

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

def demo100():
    def onvalidation(form):
        if len(form.vars.name) < 5:
            form.errors.name = 'Enter 5 characters or more'
        else:
            form.vars.name = form.vars.name.title()

    response.title = 'demo100'
    response.view = 'plugin_sqleditable/sample.html'
    header = ['employee_number','name']
    editable = SQLEDITABLE(db.employee_sheet, header=header, showid=False, maxrow=5,
         deletable=True, update_display_record=True).process(onvalidation=onvalidation)
    return dict(editable=editable)

It is a demonstration of the onvalidation parameter. This specify onvalidation parameter in the process method. As with Form of web2py, you can use form.vars.xxx and form.errors.xxx variables. If you change field value in the onvalidation function, usually, the display of the table does not change. For this reason, when you set True to update_display_record parameter in SQLEDITABLE, will update display of the table to changed value.

3.2.13. Demo13 - IS_IN_DB ¶

Demo093 - Click!

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'])),

    format='%(name)s')

db.define_table('history_in_house',
    Field('employee',db.employee_sheet),
    Field('date_history','date',label='Enter',requires=IS_DATE()),
    Field('employee_section','string',label='Section',
    requires=IS_IN_SET({'s':'sales','p':'production','d':'development',
                                                'c':'control'})))

controller

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

IS_IN_DB validator is supported now. It will display the data of another table in select box. You can set the IS_IN_DB validator in the field, or you can use the linked table by implicitly set by web2py (In this case, it must have format set in the linked table).

3.2.14. Demo14 - self-reference field ¶

Demo110 - Click!

Demo111 - Click!

model

db.define_table('node',
    Field('parent_id','reference node'),
    Field('name'),
    Field('node_comment'))

controller

def demo110():
    def record():
        session.parent_id = request.args(0) if request.args(0) else None
        rows = db(db.node.parent_id==request.args(0)).select()
        rec = [row.id for row in rows]
        return rec

    def parent():
        parent = []
        if session.parent_id:
            row = db.node(session.parent_id)
            if row:
                parent.insert(0, LI(row.name, _class='active'))
                while row:
                    if row.parent_id:
                        row = db.node(row.parent_id)
                        if row:
                            parent.insert(0, LI(A(row.name,
                                _href=URL(f='demo110', args=row.id))))
                    else:
                        break
        parent.insert(0, LI(A('Top', _href=URL(f='demo110'))))
        return OL(parent, _class='breadcrumb')

    db.node.children = Field.Virtual(lambda row: A(I(_class='glyphicon glyphicon-arrow-right'),
        ' Children', _class='btn btn-default btn-sm', _href=URL(f='demo110', args=row.node.id)))

    response.title = 'demo110'
    db.node.parent_id.default = session.parent_id
    header = ['name', 'node_comment', 'children']
    editable = SQLEDITABLE(db.node, record=record, maxrow=10, showid=False, deletable=True,
                                                                    header=header).process()
    return dict(editable=editable, parent=parent())

view

<table class='col-md-12'>
<tr><td> {{=parent}}   </td></tr>
<tr><td> {{=editable}} </td></tr>
</table>

This is an example of apps using a self-reference type field. It have added a parent dictionary variable.

If you want to use as_dict() method, you must pass as a parameter of as_dict(). You can not add a variable directly to editable .

controller

def demo111():
    def record():
        session.parent_id = request.args(0) if request.args(0) else None
        rows = db(db.node.parent_id==request.args(0)).select()
        rec = [row.id for row in rows]
        return rec

    def parent():
        parent = []
        if session.parent_id:
            row = db.node(session.parent_id)
            if row:
                parent.insert(0, LI(row.name, _class='active'))
                while row:
                    if row.parent_id:
                        row = db.node(row.parent_id)
                        if row:
                            parent.insert(0, LI(A(row.name,
                                _href=URL(f='demo111', args=row.id))))
                    else:
                        break
        parent.insert(0, LI(A('Top', _href=URL(f='demo111'))))
        return OL(parent, _class='breadcrumb')

    db.node.children = Field.Virtual(lambda row: A(I(_class='glyphicon glyphicon-arrow-right'),
        ' Children', _class='btn btn-default btn-sm', _href=URL(f='demo111', args=row.node.id)))

    response.title = 'demo111'
    db.node.parent_id.default = session.parent_id
    header = ['name', 'node_comment', 'children']
    editable = SQLEDITABLE(db.node, record=record, maxrow=10, showid=False, deletable=True,
                                                                    header=header).process()
    return editable.as_dict(parent=parent())

view

<table class='col-md-12'>
<tr><td> {{=parent}}   </td></tr>
<tr><td> {{=button}}
         {{=editable}}
         {{=button}}  </td></tr>
</table>
{{block head}}
{{super}}
{{=script}}
{{end}}

3.2.15. Demo15 - Field.Method ¶

Demo120 - Click!

controller

def demo120():
    def record():
        session.parent_id = request.args(0) if request.args(0) else None
        rows = db(db.node.parent_id==request.args(0)).select()
        rec = [row.id for row in rows]
        if request.args(0):
            rec.insert(0, request.args(0))
        return rec

    def parent():
        parent = []
        if session.parent_id:
            row = db.node(session.parent_id)
            if row:
                parent.insert(0, LI(row.name, _class='active'))
                while row:
                    if row.parent_id:
                        row = db.node(row.parent_id)
                        if row:
                            parent.insert(0, LI(A(row.name,
                                _href=URL(f='demo120', args=row.id))))
                    else:
                        break
        parent.insert(0, LI(A('Top', _href=URL(f='demo120'))))
        return OL(parent, _class='breadcrumb')

    def onvalidation(form):
        if form.delete and form.vars.id == session.parent_id:
            form.errors.delete = 'This record is a parent and you can not be removed.'

    def argument():
        return int(session.parent_id) if session.parent_id else None

    db.node.children = Field.Method(lambda row, parent_id: A(I(_class='glyphicon glyphicon-arrow-right'),
                ' Children', _class='btn btn-default btn-sm', _href=URL(f='demo120', args=row.node.id)) \
                if row.node.id!=parent_id else SPAN(I(_class='glyphicon glyphicon-home'), ' Parent'))

    response.title = 'demo120'
    db.node.parent_id.default = session.parent_id
    header = ['name', 'node_comment', {'field':'children','argument':argument}]
    editable = SQLEDITABLE(db.node, record=record, maxrow=10, showid=False, deletable=True,
                                                header=header).process(onvalidation=onvalidation)
    return dict(editable=editable, parent=parent())

Demo15 is uses Field.Method . You need to set the info of the Field.Method field in the header parameter as below.

{'field':'children','argument':argument}

The value of field key is field name, and the value of argument key is parameter of the FieldMethod. Parameter of Field.Method, it must be a callable object for lazy evaluation.

3.2.16. Demo16 - pagination ¶

Demo140 - Click!

controller

def demo140():
    # page
    limit = 5
    dbset = db(db.employee_sheet)
    rows_count = dbset.count()
    total_page = rows_count // limit + 1 if rows_count % limit < limit else rows_count // limit
    page = int(request.vars.page) if request.vars.page and int(request.vars.page) <= total_page else 0
    vars = request.get_vars
    vars['page'] = page-1
    previous = LI(A('prev', _href=URL(vars=vars, user_signature=True))) if page else ''
    vars['page'] = page+1
    if request.post_vars.keywords:
        vars['keywords'] = request.post_vars.keywords
    next = LI(A('next', _href=URL(vars=vars, user_signature=True))) if page*limit+limit <= rows_count else ''
    page_no = LI('{0}/{1}'.format(page+1, total_page), _style='margin-right:10px;')
    pagination = TAG.nav(UL(CAT(page_no, previous, next)), _class='pager')

    response.title = 'demo140'
    rows = dbset.select(orderby=db.employee_sheet.employee_number, limitby=(page*limit, page*limit+limit))
    editable = SQLEDITABLE(db.employee_sheet, rows, showid=False, maxrow=limit, deletable=True).process()
    return dict(editable=editable, pagination=pagination)

view

<table class='col-md-12'>
<tr><td>
    {{=editable}}
</td></tr>
<tr><td>
    {{=pagination}}
</td></tr>
</table>

In a large table, you may take a long time to update, or receive an error.

In this case, you will need to split display the table.

Table Of Contents

Previous topic

Next topic

This Page

3. SQLEDITABLE Demo¶

3.1. Information ¶

3.2. SQLEDITABLE - Basic usage ¶

3.2.1. Demo1 ¶

3.2.2. Demo2 - vertical ¶

3.2.3. Demo3 - validate ¶

3.2.4. Demo4 - deletable ¶

3.2.5. Demo5 - record ¶

3.2.6. Demo6 - as_dict ¶

3.2.7. Demo7 - page transition ¶

3.2.8. Demo8 ¶

3.2.9. Demo9 - touch device ¶

3.2.10. Demo10 - virtual field ¶

3.2.11. Demo11 - anchor link ¶

3.2.12. Demo12 - onvalidation ¶

3.2.13. Demo13 - IS_IN_DB ¶

3.2.14. Demo14 - self-reference field ¶

3.2.15. Demo15 - Field.Method ¶