tw2.sqla

Introduction

tw2.sqla is a database layer for ToscaWidgets 2 and SQLAlchemy. It allows common database tasks to be achieved with minimal code. There are four main features:

• Session and transaction management
• Loading and saving data
• Populating selection fields
• Generating widget definitions

See the Design document for a more detailed description of these.

tw2.sqla is designed to work fully however you define your model objects - traditional, declarative base, or Elixir.

Getting started

If you are using tw2.sqla with another framework (e.g. Pyramid), the framework will already be providing session management. You do not need to use the session management within tw2.sqla. However, database objects used with tw2.sqla must have a query property.

For standalone tw2.sqla, the repoze.tm middleware needs to be installed in the stack. This can be done by passing repoze_tm=True to tw2.core.make_middleware or tw2.devtools.dev_server. For example:

tw2.devtools.dev_server(host='127.0.0.1', repoze_tm=True)

To set the query property to use ZopeTransactionExtension, appropriate code must be added to your model. The examples below are for standalone tw2.sqla.

For declarative base:

from sqlalchemy.ext.declarative import declarative_base
import tw2.sqla as tws
Base = declarative_base()
Base.query = tws.transactional_session().query_property()

For Elixir:

import elixir as el, tw2.sqla as tws
el.session = tws.transactional_session()

Once this is setup, the application does not need to explicitly deal with sessions.

TBD Provide further examples for other frameworks.

Loading and Saving Data

There are several Page subclasses that automatically load and save data. Each have an entity property that must be set to an SQLAlchemy object.

tw2.sqla.DbListPage

This presents a list of items.

tw2.sqla.DbFormPage

This allows editing of a single item. The item is loaded based on primary key columns in the query string. When the form is posted, the data is saved back to the database. The user is redirected to the URL specified by the redirect parameter.

tw2.sqla.DbListForm

This allows editing of a multiple items, e.g. allow you to edit a whole list of users. This may be removed in future, if a way is found to incorporate this functionality with DbFormPage.

In addition, tw2.sqla.DbLinkField can be used to generate a link to a DbFormPage. It adds all the primary key columns from an object to the query string.

TBD There is no way to filter what is displayed in the list - although a partial workaround is to map the underlying SQLAlchemy object to a select statement, which performs the filtering. Also, DbFormPage has no protection against parameter tampering.

Populating selection fields

DbSelectionField automatically loads it’s contents from a database table. It has an entity property that must be set to an SQLAlchemy object. The subclasses are:

• DbSingleSelectField
• DbRadioList
• DbCheckBoxList
• DbCheckBoxTable
• DbSingleSelectLink - LinkContainer with an inner DbSingleSelectField (experimental)

Note: composite primary keys are not supported by these fields.

Internally it uses tw2.sqla.RelatedValidator which converts ID values to and from objects. You must always apply the widget to a relation, not the underlying column. For example:

class User(Base):
    group_name = sa.Column(sa.String(), sa.ForeignKey('group'))
    group = sao.relationship('Group')

class UserForm(twf.TableForm):
    group = tws.DbSingleSelectField(entity=Group)

TBD There is no way to filter what is displayed in the list - although a partial workaround is to map the underlying SQLAlchemy object to a select statement, which performs the filtering. Also, there is no protection against parameter tampering.

Automatic widgets

WidgetPolicy generates widgets from SQLAlchemy property objects. It uses the column type, name, and attributes such as nullable. Two subclasses are provided: ViewPolicy and EditPolicy. For example, EditPolicy generates SQLAlchemy Date columns as CalendarDataPicker widgets. Users can further subclass these policies to suit their own needs.

AutoContainer is a widget that generates its own children automatically, using an SQLAlchemy model object, and a WidgetPolicy. Several subclasses are provided:

• AutoTableForm
• AutoGrowingGrid
• AutoViewGrid
• AutoViewFieldSet
• AutoEditFieldSet

For example:

class MyForm(tws.AutoTableForm):
    entity = model.MyObject

Individual fields can be overridden. For example, if address is automatically generated as a TextField but you need a TextArea, do this:

class MyForm(tws.AutoTableForm):
    entity = model.MyObject
    address = twf.TextArea()

To suppress a field, use tws.NoWidget.

class tw2.sqla.WidgetPolicy

A policy object is used to generate widgets from SQLAlchemy properties.

In general, the widget’s id is set to the name of the property, and if the property is not nullable, the validator is set as required. If the desired widget is None, then no widget is used for that property.

Several parameters can be overridden to select the widget to use:

pkey_widget

For primary key properties

onetomany_widget

For foreign key properties. In this case the widget’s id is set to the name of the relation, and its entity is set to the target class.

manytoone_widget

For foreign key properties. In this case the widget’s id is set to the name of the relation, and its entity is set to the target class.

onetoone_widget

For foreign key properties. In this case the widget’s id is set to the name of the relation, and its entity is set to the target class. The relation is just one entity object, not a list like in onetomany.

name_widgets

A dictionary mapping property names to the desired widget. This can be used for names like “password” or “email”.

type_widgets

A dictionary mapping SQLAlchemy property types to the desired widget.

default_widget

If the property does not match any of the other selectors, this is used. If this is None then an error is raised for properties that do not match.

Alternatively, the factory method can be overriden to provide completely customised widget selection.

TBD

• Sometimes you want a way to say “only include these fields”
• Hints on the model, using the info attribute - experimental; needs tests & doc
• There are experimental widgets for AutoListPage and AutoListPageEdit. The biggest issue is linking between them.

Contents

• Design