wiki:S3/DynamicTables

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
namestringyes, defaults to random namethe table name, must be unique and must start with s3dt_ prefix
titlestringnoa title for the table, will be shown e.g. in mobile forms selection
mobile_formbooleandefault Trueindicates 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
namestringyesthe field name, must be unique within the table
field_typestringyesthe field type, must be a valid web2py field type, list-types are not supported
labelstringnothe field label in forms, will be translated automatically
optionsJSONnoselectable options for the field
commentstextnocomments to be shown as help text for the field (Field.comment)
require_not_emptybooleandefault Falseindicates that the field must not be empty
require_uniquebooleandefault Falseindicates that the field value must be unique
settingsJSONnoany 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_keybooleandefault Falseindicates that this field is a foreign key to link to the master table (see dynamic components), field must be a reference
component_aliasstringnothe alias to access the table as component
component_tabbooleandefault Falseexpose the component on a tab

Field settings

Key Type Field Type(s) Explanation
mindepends on field typenumeric and date/timeMinimum value (form validation)
maxdepends on field typenumeric and date/timeMaximum value (form validation)
tab_labelstringreferenceLabel for component tab
tab_positionintegerreferencePosition for component tab, 1..n or None for last
component_multiplebooleanreferenceIndicate 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,
        },
     },
Last modified 5 years ago Last modified on 02/27/17 09:22:16
Note: See TracWiki for help on using the wiki.