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).