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
mobile_form	boolean	default True	indicates that a mobile form shall be exposed for this table

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

# 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	any other field settings and constraints, e.g. validation options (like "min" and "max" for integer/double/date/datetime fields), widget options, component tab behavior, value representation options
component_key	boolean	default False	indicates that this field is a foreign key to link to the master table (see dynamic components), field must be a reference
component_alias	string	no	the alias to access the table as component
component_tab	boolean	default False	expose the component on a tab

Field settings

Key	Type	Field Type(s)	Explanation
min	depends on field type	numeric and date/time	Minimum value (form validation)
max	depends on field type	numeric and date/time	Maximum value (form validation)
tab_label	string	reference	Label for component tab
tab_position	integer	reference	Position for component tab, 1..n or None for last
component_multiple	boolean	reference	Indicate that there can be multiple component records per master master record (default True)

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 (like regular, 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")

If the table name is known (rather than a URL argument), a normal static REST controller can be used instead:

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

    return s3_rest_controller("s3dt", "example")

Dynamic Components

Component key definition

Dynamic tables can be components of static tables.

To achieve this, the dynamic model must have a foreign key field that links to the static table, e.g.:

s3db = current.s3db

ttable = s3db.s3_table
ftable = s3db.s3_field

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

# The dynamic data model
model = (
    # Component key
    {"name": "organisation_id",
     "field_type": "reference org_organisation",

     # Indicate that this field is the foreign key that links to the master table (including component alias):
     "component_key": True,
     "component_alias": "rating",

     # Setting to indicate whether there can be multiple component records per master record (default: True):
     "settings" : {"component_multiple": False,
                   },
     },
    # Other field
    {"name": "value",
     "field_type": "integer",
     "options": {1: "very good",
                 2: "good",
                 3: "average",
                 4: "acceptable",
                 5: "poor",
                 },
     },
    )

for field_def in model:

    # Add 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)

Further, dynamic components must be enabled for the master table (they are disabled by default for performance reasons):

s3db.configure("org_organisation", dynamic_components=True)

With the settings, the dynamic component can be accessed from the master table like this:

# Access the dynamic table as component
GET http://localhost:8000/eden/org/organisation/8/rating

Exposing Dynamic Components on Tabs

To expose a dynamic component as a tab in the master record perspective, set component_tab=True for the component key:

    # Component key
    {"name": "organisation_id",
     "field_type": "reference org_organisation",
     "component_key": True,
     "component_alias": "rating",

     # Expose a component tab:
     "component_tab": True,

     # Field setting to set component to single record:
     "settings": {
        "component_multiple": False,
        },
     },

The component alias will be used to generate a tab label. To override this, configure a tab_label in the field settings:

    # Component key
    {"name": "organisation_id",
     "field_type": "reference org_organisation",
     "component_key": True,
     "component_alias": "rating",
     "component_tab": True,

     # Field settings to control the component tab:
     "settings": {
        "component_multiple": False,
        "tab_label": "Performance Rating",
        },
     },

The tab will be appended to the existing component tabs. To determine a tab position add a tab_position setting:

    # Component key
    {"name": "organisation_id",
     "field_type": "reference org_organisation",
     "component_key": True,
     "component_alias": "rating",
     "component_tab": True,

     # Field settings to control the component tab:
     "settings": {
        "component_multiple": False,
        "tab_label": "Performance Rating",
        "tab_position": 2,
        },
     },