Copyright (c) 2009/10 ETH-ID, Benno Luthiger. All rights reserved.
See also LICENSE.txt
Valid for: Form2DBSource 1.x Author: Benno Luthiger Email: benno.luthiger at id.ethz.ch Update: 05.02.2010, new download location
Form2DBSource is a Silva Code Source designed to store and manage user input through web forms in a MySQL database.
The latest version is always available here.
This code source is specially aimed at supporting the interaction between a web form and a database table. The code source offers easy connection and form handling. To start with this code source, create an instance of CS Form to Database in the Zope Management Interface (ZMI) as you do for a normal Silva code source by providing object ID and title. After object creation, you find an empy Pyhton Script with the id cs_view on the Contents tab. This script is the entry point for the logic you implement to be processed by the code source when it is called on the Silva document.
On the Edit tab, you find in the lower half the configuration fields provided by the Silva code source, i.e. the fields Title, Script Id, Data encoding, Description and Source is cacheable.
On the upper half, you find the links to forms to configure this code source's abilities.
Manage contents is the link to the Contents tab. Manage parameters will display the tab to manage the form that will displayed to Silva authors when they embed the code source instance in a Silva document.
Manage form is similar to manage parameters insofar as you will be led to a tab where you can manage a form. This form, however, will be displayed at the place where the code source is embedded in the Silva document. Therefore, this form is the web form for the public users of the site.
To make the form multilingual, you can provide language variants of the public form (created with manage form). To achieve this, you have to create Formulator forms on the contents tab with field labels (and messages) in other languages. Probably the easiest way is to copy the form's XML (see the Formulator XML tab) from the default public form and paste it into the new form you've created as language variant. After you've done this, you have to register the forms as (language) variants to the Code Source. Click on the button Register variants and select the variant form's id and an arbitrary key. Note 1: If you work with (language) variants, you'll have to provide a parameter to the author using the Code Source for that she can set the form's variant that should be displayed on the document where the Code Source is embedded (best is to provide a list of possible options). You then have to pass this parameter (the variant's key) to the code source's render methods for that the correct form can be selected and processed (see the API Form2DBSource). Note 2: You can proceed in the same way if you don't intend to offer language variants but variant of a different manner (e.g. registration form for event A and B) with the same logic for input processing.
With manage db connection you can provide the connection string, user name and password to connect to the database you've configured to interact with.
Underneath, you find the list of ZSQL methods you've defined to interact with the database. You can configure these methods on the contents tab. To delete a ZSQL method from the list, you have to delete the method object in the contents tab.
For that the code source does anything, you have to provide logic in the source's main script (e.g. cs_view). Because this code source is aimed at form display and database interaction, the code source provides some convenience methods to make this work easier. (See the API Form2DBSource too).
A typical use case for this code source is to display a registration form and to insert the user input as entry in a database table. Therefore, the code (in the script cs_view) to implement this could look as follows:
msg = ''
if context.hasUserInput():
result = context.insert('tblRegistration')
msg = u"<p>Input successfully saved.</p>"
if result:
msg = result
return msg + context.display_form()
You can use the code source's insert() method only if every input field on the public form corresponds to a field in the specified database table (i.e. exact match of the form's field name with the table's field name). However, the database table can have more fields then the form. In addition, there must be a correspondence of form and field types for that an SQL insert statement can be created that works without errors.
As a consequence, you can't use the names name, title or id as field names in the database table as such field names are not allowed in the form.
However, you can evaluate the user input and create a new entry in a database table without using the insert() method. You can do this by creating an appropriate ZSQL method and using the result object returned by the get_result_obj() method.
You can use the result object to access and process the values entered by the user. An example of the script cs_view using this technique could look as follows (we assume having a form field with the id mail_from):
if not context.hasUserInput():
return context.display_form()
resultObj = context.get_result_obj()
#if the input has been filled by a bot, we silently leave
if resultObj.isBotSuspect():
return ''
fields = resultObj.fields()
mail_from = fields.get('mail_from', {'value':u'', 'title':u''})['value']
mail_to = u'receiver@my.site.org'
mail_subject = u'Mail subject'
mail_text = u'<p>Mail intro:</p>'
mail_text += resultObj.get_mailbody_html()
out_html = ''
for id, title, value, insertable in resultObj.get_fields():
if value:
out_html += u'<i>%s</i>: %s<br />' %(title, value)
#send mail
mailer = context.get_mailer(context.mailhost)
mailer.send(mail_to, mail_from, mail_subject, mail_text)
out = u'<p>The mail has been sent with the following input:</p><p>%s</p>'
return out %out_html
A common use case is to display the user input on a confirmation page and, thus, give the user the opportunity to either confirm his or her input or adjust the input. An example of the script cs_view displaying a confirmation page could look as follows:
if context.hasUserInput():
resultObj = context.get_result_obj()
errors = resultObj.get_errors()
if errors:
return ('<div style="color:red;">%s</div>' %errors) + context.display_form()
if not context.isConfirmed():
return context.display_confirmation(resultObj)
#process input ...
return u'<p>Thank you for filling the form.</p>'
return context.display_form()