Archetypes using mysql and postgresql

1. Install MySQL-python and ZMySQLDA
   http://sourceforge.net/project/showfiles.php?group_id=22307
   Make a mysql-database connection in the zmi at your Plonesite's root (or somewhere else where Plone automatically finds it).
   Test the connection.
   
2. Make a simple archetype. For the sake of simplicity, start with store/reading just one value: I call it testvalue in the example below. Install your product in the Products folder and in your plone site. Test that the archetype works. You may use ArgoUMl:
   http://plone.org/documentation/tutorial/anonymously-adding-custom-content-types-with-argouml-and-archgenxml/?searchterm=argouml
   
3. In your archetype main script add:
   

   from Products.Archetypes.SQLStorage import MySQLSQLStorage

   In the fields that you want to store in mysql you add:
   

   storage=MySQLSQLStorage()

   Restart zope.
   
4. Select the right connection. In the zmi, goto your Plone site root, and select "archetype_tool". Hit the Connections tab and ensure that your achetype uses the right mysqldatabase connection.
5. Make the mysql table. Go to the mysqldatabase connection and select the test tab. There you create a table with exactly the same name as your archetype main script, except ".py". In my example, the table is called "insertNews". The table shall have three columns: UID, PARENTUID, testvalue. (The table may have additional columns, which have no corresponding field in your archetype. Such fields are left empty by your archetype - except autoincrement and timestamp fields.)
   
6. Go to your plonesite and add your archetype. Now you should be able to store, read and update testvalue.

When your type works, you may change the base_view template (see the example below) to a template af your taste. In that case, you must go to the Plone site's root and click on portal_types. Then, click on your archetype (in the example, it is insertNews). There, and in the actions tab you may change base_view to your own template where this is appropriate.
 

Postgresql

If you want to use Postgresql, you can do the same as described above with the following modifications:

Under point 1, you should use the product ZpsycopgDA for making connections to the database:

 
Under point 3 you should use:

from Products.Archetypes.SQLStorage import PostSQLStorage

and

storage=PostSQLStorage()

Point 5 may be ignored since the archetype makes it itself.

Please, notice that the name of your archetype main script should be in lower case. Else you may get problems with your postgresql table.

Wysiwyg and default fields

Textfields may corrupt your Archetype if the field uses storage=MySQLSQLStorage(). Moreover, richwidgets seem neither to work in mysql nor postgresql so kupu wysiwyg is not a right forward option. If you want to use a wysiwig-editor, you may install the product epoz 

You may find that a StringField with an EpozWidget and postgresql storage work properly. However,  for at least one reason epoz is not an optimal choice: When the user insert information in an archetype and makes an insert error, she may loose all information in the epoz-field.

There are problems with default values which do not turn up in MySQLSQLStrorage fields.

You can make an ugly work around for these weaknesses by not using storage=MySQLSQLStorage() in kupu and default fields. Then, you can customize base_view from the portal_skins/archetypes and call it: <yourType>_view.  In this file, you can store the values from the kupu and default fields in your sql database when the user comes from base_edit. Absolutely ugly, but it works.

Example with mysql storage

# File: insertnews.py
#
__author__ = """unknown <unknown>"""
__docformat__ = 'plaintext'

from AccessControl import ClassSecurityInfo
from Products.Archetypes.atapi import *
from Products.Insertnews.config import *
from Products.Archetypes.SQLStorage import MySQLSQLStorage

schema = Schema((
    StringField(
        name='testvalue',
        widget=StringWidget(
            label='Store this value in your mysql database',
            label_msgid='Insertnews_label_responsible',
            i18n_domain='Insertnews',
        ),
        required=True,
        storage=MySQLSQLStorage()
    ),
),
)

insertnews_schema = BaseSchema.copy() + \
    schema.copy()

class insertnews(BaseContent):
    """
    """
    security = ClassSecurityInfo()
    __implements__ = (getattr(BaseContent,'__implements__',()),)

    archetype_name = 'insertnews'
    meta_type = 'insertnews'
    portal_type = 'insertnews'
#    allowed_content_types = []
#    filter_content_types = 0
    global_allow = 1
    #content_icon = 'insertnews.gif'
    immediate_view = 'base_view'
    default_view = 'base_view'
    suppl_views = ()
    typeDescription = "insertnews"
    typeDescMsgId = 'description_edit_insertnews'

    _at_rename_after_creation = True

    schema = insertnews_schema

registerType(insertnews, PROJECTNAME)