Dynamic Tables ¶

Dynamic tables allow the definition of database tables at run-time, e.g. through the web interface, and let Sahana automatically expose standard CRUD functions and reports for these tables.

Dynamic Models ¶

Dynamic models are stored in the database, in the s3_table and s3_field tables (modules/s3db/s3.py).

Creating a dynamic model ¶

Dynamic table names must have the s3dt_ prefix. This prefix will indicate to s3db that the model is to be loaded from the database.

A dynamic table can be created as follows:

ttable = current.s3db.s3_table

# Create dynamic table
table_id = ttable.insert(name = "s3dt_example",
                         title = "Example Table",
                         )

Fields in the s3_table table:

Field	Type	required?	Explanation
name	string	yes, defaults to random name	the table name, must be unique and must start with s3dt_ prefix
title	string	no	a title for the table, will be shown e.g. in mobile forms selection

Note: A dynamic table can not be accessed until at least one field has been defined for it.

Fields can be added to the dynamic table like this:

from gluon.storage import Storage

s3db = current.s3db
ftable = s3db.s3_field
onaccept = s3db.onaccept

# Define the model
model = (
    # String field
    {"name": "name",
     "field_type": "string",
     "label": "Name",
     "comments": "Explanation of the field",
     },
    # Numeric field
    {"name": "some_number",
     "field_type": "integer",
     },
    )

# Insert the fields into s3_field
for field_def in model:

    # Insert field definition
    record = Storage(field_def)
    record["table_id"] = table_id
    record_id = ftable.insert(**record)

    # Run onaccept (required!)
    record["id"] = record_id
    s3db.onaccept(ftable, record)

db.commit()

Fields in the s3_field table:

Field	Type	required?	Explanation
name	string	yes	the field name, must be unique within the table
field_type	string	yes	the field type, must be a valid web2py field type, list-types are not supported
label	string	no	the field label in forms, will be translated automatically
options	JSON	no	selectable options for the field
comments	text	no	comments to be shown as help text for the field (Field.comment)
require_not_empty	boolean	default False	indicates that the field must not be empty
require_unique	boolean	default False	indicates that the field value must be unique
settings	JSON	no	other field settings and constraints

Managing dynamic models through the web UI ¶

Obviously, dynamic table models can be managed through the web interface. A standard CRUD interface is available through the /default/tables controller.

Accessing dynamic tables ¶

Dynamic tables can be instantiated through s3db (equivalently to static tables):

table = current.s3db.s3dt_example

Note that this will raise an AttributeError if the dynamic table is not defined. In order to check for success, it is better to use the table() method:

table = current.s3db.table("s3dt_example")
if table:
    # Perform database operations
else:
    # Table not defined! => do something else

Similar, dynamic tables can also be instantiated as resources:

resource = current.s3db.resource("s3dt_example")

This too will raise an AttributeError if the dynamic table is not defined.

Dynamic table controllers ¶

In the web interface and for web services, dynamic tables can be accessed through the /default/table controller.

This controller behaves like a normal REST controller, except that the URL has the table name (without the s3dt_ prefix) as an additional first argument:

# Access record #1 in the s3dt_example table:
GET http://localhost:8000/eden/default/table/example/1

Dynamic table controllers like /default/table can easily be added in other modules, using s3_rest_controller with the dynamic-option. This option will construct the correct link URLs in e.g. CRUD action buttons:

def dynamic():
    """ REST Controller for dynamic tables """

    args = request.args
    if len(args):
        # Remember that the first argument can have a format-extension!
        return s3_rest_controller(dynamic = args[0].rsplit(".", 1)[0])
    else:
        raise HTTP(400, "No resource specified")

Obviously, if the table name is known, a normal static REST controller can be used:

def example():
    """ REST Controller for s3dt_example """

    return s3_rest_controller("s3dt", "example")

Dynamic Components ¶

tbw