Update

This example is now out of date. The best example is now in the tests. Just remember not to copy the 'drop_all' from the test setuphandler!

As is often the case with such tutorials the example is a little contrived, a toolkit such as Django is likely more suitable for such a simple system. For purposes of demonstration it shall suffice. This toolkit was developed for a fairly complex workflow project, and it is best suited to these sort of tasks where the capabilities of Zope and Plone with their sophisticated security and workflow are useful.

RDBMS content is rooted in a TableFolder, so you may not place RDBMS content freely all over your site. Each row of the associated table is treated as a content item. These may be TableSubFolders which are further traversable over a foreign key to rows in other tables. With additional tables and a suitable structure versioning and workflow information may be stored in the database.

Software versions used

• Plone 3.0.4
• PostgreSQL 8.2 and psycopg2 (other databases supported by SQLAlchemy should work, but collective.mercury has extra support for using the Postgres comment fields for titles and descriptions of schema fields)
  
• SQLAlchemy 0.4.1
• collective.lead (svn trunk)
• collective.tin (svn trunk)
• collective.mercury (svn trunk)
  
• z3schemabackports

Assumptions

• This is an SQL first approach, so a working knowledge of SQL or database design through a graphical tool such as pgAdmin is required.
  

I created the schema through pgAdmin III. Most of the SQL that follows is generated by that tool.

User

CREATE ROLE plone
  WITH PASSWORD 'plone';

Database

CREATE DATABASE timesheets
  WITH OWNER = plone
       ENCODING = 'UTF8';

Users table

CREATE TABLE users
(
  userid character varying(255) NOT NULL, -- User ID...
  fullname character varying(255), -- Full name...
  CONSTRAINT users_pkey PRIMARY KEY (userid)
)
WITH (OIDS=FALSE);
ALTER TABLE users OWNER TO plone;
COMMENT ON COLUMN users.userid IS 'User ID

As this is the primary key column, it may not be modified';
COMMENT ON COLUMN users.fullname IS 'Full name

optional';

Comments on columns are structured so that the first line can be picked up by collective.mercury and used as the title, followed by a blank line, followed by a description.

The code to accompany this section is available in svn here: (view)
https://svn.plone.org/svn/collective/collective.mercury/examples/Timesheets-steps/1

In your Products directory, create a new folder named Timesheets. Make it a python package by adding an __init__.py (this can be blank, but put a comment in it in case WinZIP still gets confused by empty files).

We now need to run collective.mercury. From a zope debug prompt issue the following commands

from collective.mercury import codegen
codegen.create_files('Products/Timesheets', overwrite=True, annotate=True, drivername='postgres',
    database='timesheets', username='plone', password='plone', host='localhost')

(You may need to give a full path to your product's directory)

This will create three files.

db.py is the database definition for collective.lead:

'''
Database definition auto-generated by collective.mercury
'''
from collective.lead import Database as BaseDatabase
from sqlalchemy import orm, sql
import sqlalchemy as sa
import model

class Database(BaseDatabase):
    @property
    def _url(self):
        args = {'username': 'plone', 'host': 'localhost', 'password': 'plone', 'drivername': 'postgres', 'database': 'timesheets'}
        return sa.engine.url.URL(**args)

    def _setup_tables(self, metadata, tables):
        tables['users'] = sa.Table('users', metadata, autoload=True)

    def _setup_mappers(self, tables, mappers):
        mappers['users'] = orm.mapper(model.usersTable, tables['users'])

autointerfaces.py are the basic zope schema defnitions:

'''
Interface definitions auto-generated by collective.mercury
'''
from zope import schema
from zope.interface import Interface
try:
    from base import ITableSchema
except ImportError:
    class ITableSchema(Interface):
        pass



class IusersTable(ITableSchema):
    userid = schema.TextLine(
        min_length = 0,
        description = u'As this is the primary key column, it may not be modified',
        title = u'User ID',
        default = None,
        required = True,
        readonly = False,
        max_length = 255,
        )
    fullname = schema.TextLine(
        min_length = 0,
        description = u'optional',
        title = u'Full name',
        default = None,
        required = False,
        readonly = False,
        max_length = 255,
        )

automodel.py contains the (most) basic model classes:

'''
Table definitions auto-generated by collective.mercury
'''
from zope.interface import implements
import interfaces
try:
    from base import Table
except ImportError:
    class Table(object):
        pass



class usersTable(Table):
    implements(interfaces.IusersTable)

You'll notice that db.py imports from model and automodel.py imports from interfaces. This is to allow for customisation through subclassing without changing the auto-generated files. When we change the database later we will be able to run collective.mercury again, our customisations will be safe in the model.py and interfaces.py files.

We don't need to customise the interfaces yet, so interfaces.py is just:

from autointerfaces import *

Content classes

We must however do some work on model.py:

from automodel import *
from collective.tin.item import Table
from collective.tin.container import TableFolder
from collective.tin.factory import ItemFactory, FolderFactory
from collective.tin import interfaces as tin
from zope.interface import implements
import interfaces

DB_NAME = 'timesheets'

class usersTable(Table):
    '''
    User item class
    '''
    implements(
        interfaces.IusersTable,
        )
    portal_type = meta_type = 'TimesheetUser'
    
    @property # basic metadata
    def title(self):
        return self.fullname

UserFactory = ItemFactory(usersTable)


class UserFolder(TableFolder):
    '''
    Users folder class, the ZODB root for database user items
    '''
    implements(
        tin.IStringItemKey, # marker interface to indicate table has a string primary key
        )
    portal_type = meta_type = 'TimesheetUserFolder'
    database_name = DB_NAME
    item_class = usersTable
    
UserFolderFactory = FolderFactory(UserFolder)

Here we define the content class, a folder class and factories for both.

Forms

Now we must define the various add, edit and display forms in browser.py:

from collective.tin.form import ItemAddForm, ItemEditForm, ItemDisplayForm, FolderAddForm
from zope.formlib import form
import interfaces


class AddUserFolder(FolderAddForm):
    pass


class AddUser(ItemAddForm):
    form_fields = form.Fields(interfaces.IusersTable)


class DisplayUser(ItemDisplayForm):
    form_fields = form.Fields(interfaces.IusersTable, render_context=True)
    

class EditUSer(ItemEditForm):
    form_fields = form.Fields(interfaces.IusersTable, render_context=True)
    form_fields = form_fields.omit('userid') # cannot change the primary key

Configuration

We now need to tie this all together in configure.zcml:

<configure 
  xmlns="http://namespaces.zope.org/zope"
  xmlns:browser="http://namespaces.zope.org/browser"
  xmlns:five="http://namespaces.zope.org/five"
  >

<include package="collective.tin" />

<include file="profiles.zcml" />

<utility
   provides="collective.lead.interfaces.IDatabase"
   factory=".db.Database"
   name="timesheets.database"
   />

<!-- User Folder -->

<five:registerClass
    class=".model.UserFolder"
    meta_type="UserFolder"
    permission="cmf.ManagePortal"
    />

<utility
   component=".model.UserFolderFactory"
   name="timesheets.UserFolder"
   />

<browser:page
    for="zope.app.container.interfaces.IAdding"
    name="timesheets.UserFolder"
    class=".browser.AddUserFolder"
    permission="cmf.ManagePortal"
    />

<!-- User -->

<five:registerClass
    class=".model.usersTable"
    meta_type="User"
    permission="cmf.AddPortalContent"
    />

<utility
   component=".model.UserFactory"
   name="timesheets.User"
   />

<browser:page
    for="zope.app.container.interfaces.IAdding"
    name="timesheets.User"
    class=".browser.AddUser"
    permission="cmf.AddPortalContent"
    />

<browser:page
	  for=".interfaces.IusersTable"
	  name="view"
	  class=".browser.DisplayUser"
	  permission="zope2.View"
	  />

<browser:page
	  for=".interfaces.IusersTable"
	  name="edit"
	  class=".browser.EditUser"
	  permission="cmf.ModifyPortalContent"
	  />

</configure>

So the portal may find the correct add form for a type, make sure you name the types add form the same as it's factory, and supply this name in the factory type information (see below).

Factory type information

To make the content types addable, we need to create factory type information for them in portal_types. You can add that directly through the ZMI, but I find cutting and pasting generic setup xml to be quicker. Above we included a profiles.zcml file. In this we define a directory (profiles/default) to contain the generic setup xml files. Here it is:

<configure
    xmlns="http://namespaces.zope.org/zope"
    xmlns:genericsetup="http://namespaces.zope.org/genericsetup"
    i18n_domain="timesheets">

  <genericsetup:registerProfile
      name="timesheets"
      title="Timesheets"
      directory="profiles/default"
      description="Extension profile for Timesheets Product."
      provides="Products.GenericSetup.interfaces.EXTENSION"
      for="Products.CMFPlone.interfaces.IPloneSiteRoot"
      />

</configure>

We then need to create the profiles/default directory and add to it the type information xml files. This information is composed of a top level file types.xml:

<?xml version="1.0"?>
<object name="portal_types" meta_type="Plone Types Tool">
 <object name="TimesheetUser" meta_type="Factory-based Type Information"/>
 <object name="TimesheetUserFolder" meta_type="Factory-based Type Information"/>
</object>

and a directory 'types' containing further xml files for each portal type, TimesheetUser.xml:

<?xml version="1.0"?>
<object name="TimesheetUser" meta_type="Factory-based Type Information"
   i18n:domain="timesheets" xmlns:i18n="http://xml.zope.org/namespaces/i18n">
 <property name="title" i18n:translate="">User</property>
 <property name="description" i18n:translate="">A database stored user object</property>
 <property name="content_icon">user.gif</property>
 <property name="content_meta_type">User</property>
 <property name="product"></property>
 <property name="factory">timesheets.User</property>
 <property name="immediate_view"></property>
 <property name="global_allow">False</property>
 <property name="filter_content_types">True</property>
 <property name="allowed_content_types">
 </property>
 <property name="allow_discussion">False</property>
 <alias from="(Default)" to="@@view"/>
 <alias from="view" to="@@view"/>
 <alias from="edit" to="@@edit"/>
 <action title="View" action_id="view" category="object" condition_expr=""
    url_expr="string:${object_url}" visible="True">
  <permission value="View"/>
 </action>
 <action title="Edit" action_id="edit" category="object" condition_expr=""
    url_expr="string:${object_url}/edit" visible="True">
  <permission value="Modify portal content"/>
 </action>
</object>

and TimesheetUserFolder.xml:

<?xml version="1.0"?>
<object name="TimesheetUserFolder" meta_type="Factory-based Type Information"
   i18n:domain="timesheets" xmlns:i18n="http://xml.zope.org/namespaces/i18n">
 <property name="title" i18n:translate="">User Folder</property>
 <property name="description"
    i18n:translate="">Folder to store timesheet user objects in a database</property>
 <property name="content_icon">folder_icon.gif</property>
 <property name="content_meta_type">UserFolder</property>
 <property name="product"></property>
 <property name="factory">timesheets.UserFolder</property>
 <property name="immediate_view"></property>
 <property name="global_allow">True</property>
 <property name="filter_content_types">True</property>
 <property name="allowed_content_types">
  <element value="TimesheetUser"/>
 </property>
 <property name="allow_discussion">False</property>
 <alias from="(Default)" to="folder_listing"/>
 <alias from="view" to="folder_listing"/>
 <action title="View" action_id="view" category="object" condition_expr=""
    url_expr="string:${object_url}" visible="True">
  <permission value="View"/>
 </action>
</object>

 These factory type informations specify the content tabs and where a content type may be added.

Restart your zope instance. You can now add the Timesheets product to your site through site-setup and add a 'User Folder'. Inside your new user folder you can add 'User' objects.