Building from the Simple SQL CRUD tutorial by Steve McMahon, this tutorial will demonstrate some of the techniques that can be used to work with relational databases.

Most of the work here will be in developing Python Scripts that will do the logic and ZSQL Methods to communicate to the database. Hopefully, in some time we will all be using the Alchemist product to help us in that area.

Note that this tutorial will show just one of the possible ways of working with forms and relational databases in Plone, and should be adapted as necessary. Take this as a starting guide into this realm :)

Skills necessary for this tutorial:

1. Have already followed the Simple SQL CRUD tutorial. You should already have a basic idea on how to work with PloneFormGen and databases, and have setup Zope correctly for this.
2. Database SQL knowledge, to create and/or adapt the examples to your database. It's also expected you have already connected the database into Zope and know how to use ZSQL Methods. The examples are based on mySQL using InnoDB, so you should know how to write correct equivalent SQL for your preferred backend.
   
3. Python scripting knowledge is necessary to adapt the general examples given here. Note that you don't need to be a master programmer, but you must know your way around with Python.
4. DataGridField for PloneFormGen, that was introduced in version 1.1. For this you need to install the DataGridField product, then copy the folder PFGDataGrid from the PloneFormGen/examples to your Plone Products folder and install it.
   

What we will do is:

1. Create a form to insert data into related tables.
2. Do the wiring work in Python + ZSQL Methods to insert the form data.
3. Create a search interface to retrieve the data.
4. Create an advanced search to filter by fields.
5. Adapt the insert form to use for updating data.
6. Delete data.
   

The following table relations will be covered by our form: 1:1, 1:n and m:n.

Let's use an example database to go through in this tutorial. It is a contact keeping database. Here are the tables and their relations:

Now on to create the form.

PloneFormGen makes it easy for us. You can add the fields any way you prefer, I created FieldSet Folders to group related fields, like the fields from the Person and Info tables. Add a hidden field for the IDperson (the main table) with a default value of -1, so we can use the same form for updating the data later. Try to keep all field names the same as the names of your database fields, so the scripting is easier. You can change their presented titles to whatever you want tho.

Birthday is a Date/Time field and InfoText is a Text Field. For the Telephone, we'll use a DataGrid Field with 2 columns, one for phone type and another for the number. You could set a default value for the phone type to say residencial, or office, for instance.

The Donors will also use a DataGrid Field with 2 columns: one for the donor name and the other for the amount donated.

We could use a DataGrid Field for the Keywords aswell, but let's have it different: add a simple String field to it, and instruct the users to separate the keywords with commas on the description of the field.

You should now have something similar to this.

Adapt it to your own preferred layout style at will.

click the image to enlarge

So we now have to wire this to actually do what we want, ie. add the input data to the database.

It's here that we face actual code, even if not much of it, since Python makes it easy and fun for us :)

First we look what is that we need to do:

• Process the input fields to conform to MySQL format (mainly the Date field).
• Separate the keywords string to insert them into separate fields on the database.
• If you used different names on the fields than the database you'll have to script through it as well.
• Write the ZSQL methods to insert the data.
  

Now to convert this to code:

1. Start by writing a Python script that will receive the form data and process it according to what we need. We'll call it py_form_process:

''' Process the form data to send to the database '''

request = container.Request
form = request.form
data = {}    # the dictionary that will hold our formated fields

''' Copy all form input data '''
for key in form.keys():
	data[key] = form[key]

''' Format the Date field to MySQL [YYYYMMDD] '''
data['Birthday'] = form['Birthday_year']  + \
		   form['Birthday_month'] + \
		   form['Birthday_day']

''' Format the Keywords string '''
data['keywords'] = form['Keyword'].replace(';',',').split(',')    # Replace ';' by ',' and then split on the commas
data['keywords'] = [ key.strip() for key in data['keywords'] ]    # Cleanup residual white-spaces

''' Format the Phone fields '''
# this is an example if you have different field names on the form
# than on what you'll need on the database. We are getting rid of
# phones that have only type but no number defined as well.

data['Telephone'] = []
for phone in form['Telephone']:
	if phone['column2']:
		data['Telephone'].append({ 'Phonetype' : phone['column1'],
					   'PhoneNumber' : phone['column2'] })

''' Forward our formatted data to a database dealing script '''
# receive the new ID for the person we create on the database
# so the ploneformgen success page shows the new ID.

form['IDperson'] = context.py_insert_data(data)

    2.  Next is our py_insert_data. Type data on the Parameter List to receive the data from our previous script.

''' Insert the formatted data in the database '''

''' Inserts the person data and returns the created ID '''
# we need the new ID to define the relations of the other tables.
# see the zsql method on how we return this.

newID = context.zsql_add_person(data)
data['IDperson'] = newID[0][0]

# now insert the other tables:

''' Info table '''
if data['InfoText']:
	context.zsql_add_info(data)

''' Keyword table '''
for key in data['keywords']
	if not key == '':
		data['Keyword'] = key
		context.zsql_add_keyword(data)

''' Donor table '''
for donor in data['Donor']:
	if donor['DonorName']:
		for key in donor.keys():
			data[key] = donor[key]
		context.zsql_add_donor(data)

''' Telephone table '''
for phone in data['Telephone']:
	if phone['PhoneNumber']:
		for key in phone.keys():
			data[key] = phone[key]
		context.zsql_add_telephone(data)

return data['IDperson']

Notice that we are using several ZSQL methods, that is, one for each table of our database. Defining them is our next step.

    3.  The ZSQL methods require knowledge of SQL and DTML ( unfortunately ;), I hope it is simple enough to follow along. These are the methods we'll need:

• zsql_add_person

• zsql_add_info
• zsql_add_keyword
• zsql_add_donor
• zsql_add_telephone

zsql_add_person

Arguments: Name, Birthday

INSERT INTO Person
VALUES (0,
    <dtml-sqlvar Name type=string>,
    <dtml-sqlvar Birthday type=int>
)

<dtml-var sql_delimiter>

SELECT MAX(IDperson) from Person

The SELECT MAX will return the last ID inserted on the Person table. We rely on this ID for all other inserts.

zsql_add_info

Arguments: IDperson, InfoText

INSERT INTO Info
VALUES (0,
    <dtml-sqlvar InfoText type=string>,
    <dtml-sqlvar IDperson type=int>
)

zsql_add_keyword

Arguments: IDperson, Keyword

INSERT INTO Keyword (Keyword)
VALUES (
    <dtml-sqlvar Keyword type=string>
)

ON DUPLICATE KEY UPDATE
   IDkey = LAST_INSERT_ID(IDkey)

<dtml-var sql_delimiter>

INSERT INTO Person_has_Keyword
VALUES (
    <dtml-sqlvar IDperson type=string>,
    LAST_INSERT_ID()
)

This one is a little more involved. The ON DUPLICATE is if we receive a duplicate Keyword, it will just return us the existing ID. Then we insert this ID and the Person ID on the pivot table.

zsql_add_donor

Arguments: IDperson, DonorName, Amount

INSERT INTO Donor (DonorName)
VALUES (
    <dtml-sqlvar DonorName type=string>
)
ON DUPLICATE KEY UPDATE
   IDdonor = LAST_INSERT_ID(IDdonor)

<dtml-var sql_delimiter>

INSERT INTO Person_has_Donor
VALUES (
    <dtml-sqlvar IDperson type=string>,
    LAST_INSERT_ID(),
    <dtml-sqlvar Amount type=string>
)

zsql_add_telephone

Arguments: IDperson, PhoneType, PhoneNumber

INSERT INTO Telephone
VALUES (0,
    <dtml-sqlvar PhoneType type=string>,
    <dtml-sqlvar PhoneNumber type=string>,
    <dtml-sqlvar IDperson type=int>
)

Back to the form, click the Edit link, access the overrides page and add in the After Validation Script field the call to the process form script:

here/py_form_process

Save it, and try it out. Check if it inserting the entered data correctly. Hopefully it works! :)

Check out the treasure map below, of how our system is currently structured:

Our search requires:

• a new Form Folder with a single String Field
• a ZSQL Method to retrieve the data
• a DTML Method to list the results that match the query
  
• another DTML Method do display all data from one record
• a small army of ZSQL Methods to retrieve all data related to one person
  

If somehow we could use Plone for this (ZCatalog?), maybe it could be made simpler. This way it works fine, but you have to code the page templates for the results yourself. If anyone is interested, a Plone Product to take care of this would be welcome :)