CRUD

The Create/Read/Update/Delete (CRUD) API is an experimental interface on top of SQLFORM. It is now deprecated in favor of SQLFORM.grid() and SQLFORM.smartgrid(), but is described here because some applications have been built with it.

CRUD creates an SQLFORM, but it simplifies the coding because it incorporates the creation of the form, the processing of the form, the notification, and the redirection, all in one single function. The first thing to notice is that CRUD differs from the other web2py APIs we have used so far because it is not already exposed. It must be imported. It also must be linked to a specific database. For example:

  1. from gluon.tools import Crud
  2. crud = Crud(db)

The crud object defined above provides the following API:

.

  • crud.tables() returns a list of tables defined in the database.
  • crud.create(db.tablename) returns a create form for table tablename.
  • crud.read(db.tablename, id) returns a readonly form for tablename and record id.
  • crud.update(db.tablename, id) returns an update form for tablename and record id.
  • crud.delete(db.tablename, id) deletes the record.
  • crud.select(db.tablename, query) returns a list of records selected from the table.
  • crud.search(db.tablename) returns a tuple (form, records) where form is a search form and records is a list of records based on the submitted search form.
  • crud() returns one of the above based on the request.args().

For example, the following action:

  1. def data(): return dict(form=crud())

would expose the following URLs:

  1. http://.../[app]/[controller]/data/tables
  2. http://.../[app]/[controller]/data/create/[tablename]
  3. http://.../[app]/[controller]/data/read/[tablename]/[id]
  4. http://.../[app]/[controller]/data/update/[tablename]/[id]
  5. http://.../[app]/[controller]/data/delete/[tablename]/[id]
  6. http://.../[app]/[controller]/data/select/[tablename]
  7. http://.../[app]/[controller]/data/search/[tablename]

However, the following action:

  1. def create_tablename():
  2.     return dict(form=crud.create(db.tablename))

would only expose the create method

  1. http://.../[app]/[controller]/create_tablename

While the following action:

  1. def update_tablename():
  2.     return dict(form=crud.update(db.tablename, request.args(0)))

would only expose the update method

  1. http://.../[app]/[controller]/update_tablename/[id]

and so on.

The behavior of CRUD can be customized in two ways: by setting some attributes of the crud object or by passing extra parameters to each of its methods.

Settings

Here is a complete list of current CRUD attributes, their default values, and meaning:

To enforce authentication on all crud forms:

  1. crud.settings.auth = auth

This is explained in Chapter 9.

To specify the controller that defines the data function which returns the crud object

  1. crud.settings.controller = 'default'

To specify the URL to redirect to after a successful “create” record:

  1. crud.settings.create_next = URL('index')

To specify the URL to redirect to after a successful “update” record:

  1. crud.settings.update_next = URL('index')

To specify the URL to redirect to after a successful “delete” record:

  1. crud.settings.delete_next = URL('index')

To specify the URL to be used for linking uploaded files:

  1. crud.settings.download_url = URL('download')

To specify extra functions to be executed after standard validation procedures for crud.create forms:

  1. crud.settings.create_onvalidation = StorageList()

StorageList is the same as a Storage object, they are both defined in the file “gluon/storage.py”, but it defaults to [] as opposed to None. It allows the following syntax:

  1. crud.settings.create_onvalidation.mytablename.append(lambda form: ...)

To specify extra functions to be executed after standard validation procedures for crud.update forms:

  1. crud.settings.update_onvalidation = StorageList()

To specify extra functions to be executed after completion of crud.create forms:

  1. crud.settings.create_onaccept = StorageList()

To specify extra functions to be executed after completion of crud.update forms:

  1. crud.settings.update_onaccept = StorageList()

To specify extra functions to be executed after completion of crud.update if record is deleted:

  1. crud.settings.update_ondelete = StorageList()

To specify extra functions to be executed after completion of crud.delete:

  1. crud.settings.delete_onaccept = StorageList()

To determine whether the “update” forms should have a “delete” button:

  1. crud.settings.update_deletable = True

To determine whether the “update” forms should show the id of the edited record:

  1. crud.settings.showid = False

To determine whether forms should keep the previously inserted values or reset to default after successful submission:

  1. crud.settings.keepvalues = False

Crud always detects whether a record being edited has been modified by a third party in the time between the time when the form is displayed and the time when it is submitted. This behavior is equivalent to

form.process(detect_record_change=True)

and it is set in:

  1. crud.settings.detect_record_change = True

and it can be changed/disabled by setting the variable to False.

You can change the form style by

  1. crud.settings.formstyle = 'table3cols' or 'table2cols' or 'divs' or 'ul'

You can set the separator in all crud forms:

  1. crud.settings.label_separator = ':'

captcha

You can add captcha to forms, using the same convention explained for auth.settings (see Chapter 9), with:

  1. crud.settings.create_captcha = None
  2. crud.settings.update_captcha = None
  3. crud.settings.captcha = None

Messages

Here is a list of customizable messages:

  1. crud.messages.submit_button = 'Submit'

sets the text of the “submit” button for both create and update forms.

  1. crud.messages.delete_label = 'Check to delete'

sets the label of the “delete” checkbox in “update” forms.

  1. crud.messages.record_created = 'Record Created'

sets the flash message on successful record creation.

  1. crud.messages.record_updated = 'Record Updated'

sets the flash message on successful record update.

  1. crud.messages.record_deleted = 'Record Deleted'

sets the flash message on successful record deletion.

  1. crud.messages.update_log = 'Record %(id)s updated'

sets the log message on successful record update.

  1. crud.messages.create_log = 'Record %(id)s created'

sets the log message on successful record creation.

  1. crud.messages.read_log = 'Record %(id)s read'

sets the log message on successful record read access.

  1. crud.messages.delete_log = 'Record %(id)s deleted'

sets the log message on successful record deletion.

Notice that crud.messages belongs to the class gluon.storage.Message which is similar to gluon.storage.Storage but it automatically translates its values, without need for the T operator.

Log messages are used if and only if CRUD is connected to Auth (as discussed in Chapter 9) and CRUD logger is attached too:

  1. crud.settings.logger = crud.settings.auth = auth

The events are logged in the Auth table auth_event.

Methods

The behavior of CRUD methods can also be customized on a per call basis. Here are their signatures:

  1. crud.tables()
  2. crud.create(table, next, onvalidation, onaccept, log, message, formname, **attributes)
  3. crud.read(table, record)
  4. crud.update(table, record, next, onvalidation, onaccept, ondelete, log, message, deletable)
  5. crud.delete(table, record_id, next, message)
  6. crud.select(table, query, fields, orderby, limitby, headers, **attr)
  7. crud.search(table, query, queries, query_labels, fields, field_labels, zero, showall, chkall)
  • table is a DAL table or a tablename the method should act on.
  • record and record_id are the id of the record the method should act on.
  • next is the URL to redirect to after success. If the URL contains the substring “[id]“ this will be replaced by the id of the record currently created/updated (use URL(..., url_encode=False) to prevent the brackets from being escaped).
  • onvalidation has the same function as in FORM and SQLFORM (see onvalidation section)
  • onaccept is a function to be called after the form submission is accepted and acted upon, but before redirection. This will be called with the form passed as argument.
  • log is the log message. Log messages in CRUD see variables in the form.vars dictionary such as “%(id)s” (the message actually logged is log % form.vars).
  • message is the flash message upon form acceptance.
  • formname is the name to be used for the genarated SQLFORM, this may be needed when you want multiple form on the same page (see Multiple forms per page section for additional details).
  • **attributes additional crud.create and crud.update keyword arguments to be passed to the SQLFORM constructor.
  • ondelete is called in place of onaccept when a record is deleted via an “update” form.
  • deletable determines whether the “update” form should have a delete option.
  • query is the query to be used to select records.
  • fields is a list of fields to be selected.
  • orderby determines the order in which records should be selected (see Chapter 6).
  • limitby determines the range of selected records that should be displayed (see Chapter 6).
  • headers is a dictionary mapping field names into header names to be passed to the SQLTABLE constructor (see Chapter 6).
  • queries a list like ['equals', 'not equal', 'contains'] containing the allowed methods in the search form.
  • query_labels a dictionary like query_labels=dict(equals='Equals') giving names to search methods.
  • fields a list of fields to be listed in the search widget.
  • field_labels a dictionary mapping field names into labels.
  • zero defaults to “choose one” is used as default option for the drop-down in the search widget.
  • showall set it to True if you want rows returned as per the query in the first call (added after 1.98.2).
  • chkall set it to True to turn on all the checkboxes in the search form (added after 1.98.2).
  • **attr additional crud.select keyword arguments to be passed to the SQLTABLE constructor (see Chapter 6).

Here is an example of usage in a single controller function:

  1. ## assuming db.define_table('person', Field('name'))
  2. def people():
  3.     form = crud.create(db.person, next=URL('index'),
  4.                        message=T("record created"))
  5.     persons = crud.select(db.person, fields=['name'],
  6.                           headers={'person.name': 'Name'})
  7.     return dict(form=form, persons=persons)

Here is another very generic controller function that lets you search, create and edit any records from any table where the tablename is passed in request.args(0):

  1. def manage():
  2.     table = db[request.args(0)]
  3.     form = crud.update(table, request.args(1))
  4.     table.id.represent = lambda id, row: \
  5.        A('edit:', id, _href=URL(args=(request.args(0), id)))
  6.     search, rows = crud.search(table)
  7.     return dict(form=form, search=search, rows=rows)

Notice the line table.id.represent=... that tells web2py to change the representation of the id field and display a link instead to the page itself and passes the id as request.args(1) which turns the create page into an update page.

Record versioning

Both SQLFORM and CRUD provides a utility to version database records:

If you have a table (db.mytable) that needs full revision history you can just do:

  1. form = SQLFORM(db.mytable, myrecord).process(onsuccess=auth.archive)
  1. form = crud.update(db.mytable, myrecord, onaccept=auth.archive)

auth.archive defines a new table called db.mytable_archive (the name is derived from the name of the table to which it refers) and on updating, it stores a copy of the record (as it was before the update) in the created archive table, including a reference to the current record.

Because the record is actually updated (only its previous state is archived), references are never broken.

This is all done under the hood. Should you wish to access the archive table you should define it in a model:

  1. db.define_table('mytable_archive',
  2.    Field('current_record', 'reference mytable'),
  3.    db.mytable)

Notice the table extends db.mytable (including all its fields), and adds a reference to the current_record.

auth.archive does not timestamp the stored record unless your original table has timestamp fields, for example:

  1. db.define_table('mytable',
  2.     Field('created_on', 'datetime',
  3.           default=request.now, update=request.now, writable=False),
  4.     Field('created_by', 'reference auth_user',
  5.           default=auth.user_id, update=auth.user_id, writable=False),

There is nothing special about these fields and you may give them any name you like. They are filled before the record is archived and are archived with each copy of the record. The archive table name and/or reference field name can be changed like this:

  1. db.define_table('myhistory',
  2.     Field('parent_record', 'reference mytable'),
  3.     db.mytable)
  4. ## ...
  5. form = SQLFORM(db.mytable, myrecord)
  6. form.process(onsuccess = lambda form:auth.archive(form,
  7.              archive_table=db.myhistory,
  8.              current_record='parent_record'))