Table constructor

Tables are defined in the DAL via define_table.

define_table signature

The signature for define_table method is:

define_table(tablename, *fields, **kwargs)

It accepts a mandatory table name and an optional number of Field instances (even none). You can also pass a Table (or subclass) object instead of a Field one, this clones and adds all the fields (but the “id”) to the defining table. Other optional keyword args are: rname, redefine, common_filter, fake_migrate, fields, format, migrate, on_define, plural, polymodel, primarykey, sequence_name, singular, table_class, and trigger_name, which are discussed below.

For example:

>>> db.define_table('person', Field('name'))
<Table person (id, name)>

It defines, stores and returns a Table object called “person” containing a field (column) “name”. This object can also be accessed via db.person, so you do not need to catch the value returned by define_table.

id: Notes about the primary key

Do not declare a field called “id”, because one is created by web2py anyway. Every table has a field called “id” by default. It is an auto-increment integer field (usually starting at 1) used for cross-reference and for making every record unique, so “id” is a primary key. (Note: the id counter starting at 1 is back-end specific. For example, this does not apply to the Google App Engine NoSQL.)

Optionally you can define a field of type='id' and web2py will use this field as auto-increment id field. This is not recommended except when accessing legacy database tables which have a primary key under a different name. With some limitation, you can also use different primary keys using the primarykey parameter.

plural and singular

Smartgrid objects may need to know the singular and plural name of the table. The defaults are smart but these parameters allow you to be specific. Smartgrid is described in Chapter 7.

redefine

Tables can be defined only once but you can force web2py to redefine an existing table:

db.define_table('person', Field('name'))
db.define_table('person', Field('name'), redefine=True)

The redefinition may trigger a migration if table definition changes.

format: Record representation

It is optional but recommended to specify a format representation for records with the format parameter.

db.define_table('person', Field('name'), format='%(name)s')

or

db.define_table('person', Field('name'), format='%(name)s %(id)s')

or even more complex ones using a function:

db.define_table('person', Field('name'),
                format=lambda r: r.name or 'anonymous')

The format attribute will be used for two purposes:

• To represent referenced records in select/option drop-downs.
• To set the db.othertable.otherfield.represent attribute for all fields referencing this table. This means that SQLTABLE will not show references by id but will use the format preferred representation instead. (Look at Serializing Rows in views section in this chapter to learn more about SQLTABLE.)

rname: Real name

rname sets a database backend name for the table. This makes the web2py table name an alias, and rname is the real name used when constructing the query for the backend. To illustrate just one use, rname can be used to provide MSSQL fully qualified table names accessing tables belonging to other databases on the server:

rname = 'db1.dbo.table1'

primarykey: Support for legacy tables

primarykey helps support legacy tables with existing primary keys, even multi-part. See Legacy databases and keyed tables section in this chapter.

migrate, fake_migrate

migrate sets migration options for the table. Refer to Migrations section in this chapter for details.

table_class

If you define your own Table class as a sub-class of pydal.objects.Table, you can provide it here; this allows you to extend and override methods. Example:

from pydal.objects import Table
class MyTable(Table):
    ...
db.define_table(..., table_class=MyTable)

sequence_name

The name of a custom table sequence (if supported by the database). Can create a SEQUENCE (starting at 1 and incrementing by 1) or use this for legacy tables with custom sequences.

Note that when necessary, web2py will create sequences automatically by default.

trigger_name

Relates to sequence_name. Relevant for some backends which do not support auto-increment numeric fields.

polymodel

For Google App Engine

on_define

on_define is a callback triggered when a lazy_table is instantiated, although it is called anyway if the table is not lazy. This allows dynamic changes to the table without losing the advantages of delayed instantiation.

Example:

db = DAL(lazy_tables=True)
db.define_table('person',
    Field('name'),
    Field('age', 'integer'),
    on_define=lambda table: [
        table.name.set_attributes(requires=IS_NOT_EMPTY(), default=''),
        table.age.set_attributes(requires=IS_INT_IN_RANGE(0, 120), default=30) ])

Note this example shows how to use on_define but it is not actually necessary. The simple requires values could be added to the Field definitions and the table would still be lazy. However, requires which take a Set object as the first argument, such as IS_IN_DB, will make a query like

db.sometable.somefield == some_value

which would cause sometable to be defined early. This is the situation saved by on_define.

Lazy Tables, a major performance boost

web2py models are executed before controllers, so all tables are defined at every request. Not all tables are needed to handle each request, so it is possible that some of the time spent defining tables is wasted. Conditional models (see Chapter 4) can help, but web2py offers a big performance boost via lazy_tables. This feature means that table creation is deferred until the table is actually referenced. Enabling lazy tables is made when initialising a database via the DAL constructor. It requires setting the lazy_tables parameter:

DAL(..., lazy_tables=True)

This is one of the most significant response-time performance boosts in web2py.

Adding attributes to fields and tables

If you need to add custom attributes to fields, you can simply do this:

db.table.field.extra = {}

“extra” is not a keyword ; it’s a custom attributes now attached to the field object. You can do it with tables too but they must be preceded by an underscore to avoid naming conflicts with fields:

db.table._extra = {}