= Dynamic Tables =
[[TOC]]

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:

{{{#!python
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:

{{{#!python
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):

{{{#!python
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:

{{{#!python
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:

{{{#!python
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:

{{{#!python
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:

{{{#!python
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.:

{{{#!python

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):
{{{#!python
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:

{{{#!python
    # 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:

{{{#!python
    # 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:

{{{#!python
    # 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,
        },
     },
}}}