Clio Tables

The first step to integrate a database with Clio is to use tables that have the special Clio columns. We will call this kind of table a Clio table. All tables in the database should be Clio tables. The only exception to this are pivot (or secondary) tables used to define a many to many relation between two other tables. These tables should be normal non-Clio tables.

A Clio table is a table that has a notion of workflow versions. Multiple versions of the same abstract object (published, editable, archived) can exist in this table at the same time as different versions of this object undergoing a workflow. Each version is represented by a record.

Note that Clio does not (yet) support the SQLAlchemy declarative extension that allows one to define ORM-mapped class and table columns in step. At this point, Clio only supports the explicit mechanisms where tables and ORM-mapped classes are defined in separate steps.

A Clio table defines the following special columns:

id

An auto-incrementing id column. This uniquely identifies the record in the table. If multiple workflow versions of the same record exist, they each will have a unique id. The id column is used in foreign key relationships that may be created to this table.

code

Another identifying column. All workflow versions of the record will have the same code. It can therefore be used to find out all versions of a record in a query. The code can also be used to refer to this record externally, from outside the database.

The database does not supply the code automatically; the developer must supply a unique code when the record is first created and is therefore new to the workflow system. When using the ORM this is typically when the ORM-mapped object is first created.

status

The workflow status that the record is in. Clio manages this column automatically. It can also be used in queries. The status is a symbolic value encoded as an integer number. Clio defines the meaning of these numbers.

workflow_timestamp

The timestamp (date and time) of the last workflow event that happened to this record. A workflow event includes publication or being archived. Clio automatically manages this column.

creation_timestamp

The timestamp (date and time) of the creation time of this record. Clio automatically manages this column.

In addition to the special columns, Clio tables also have a uniqueness constraint: there never may be two records which have the same code and the same status.

Clio offers a convenience clio.Table constructor which allows you to construct tables with these columns and the constraint already available. Use clio.Table just like you would use sqlalchemy.Table:

import clio

address_table = clio.Table(
  'address', metadata,
  Column('street', Unicode(50), nullable=False),
  Column('city', Unicode(50), nullable=False),
  Column('country', Unicode(50), nullable=False))

The address_table table will now automatically gain the columns described above (id, code, status, etc).