wiki:S3/DynamicTables

Version 2 (modified by Dominic König, 5 years ago) ( diff )

--

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

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
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
settingsJSONnoother 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 can be added easily in other modules, using s3_rest_controller with the dynamic-option.

def dynamic_table_controller():
    """ REST Controller for the s3dt_example dynamic table """

    return s3_rest_controller(dynamic = "example")

Dynamic Components

tbw

Note: See TracWiki for help on using the wiki.