ZPsycopgDA and no commits

January 17th, 2013

I discovered the solution to this problem a couple of months ago. Goodness knows how I happened upon it but today the same thing occurred. Of course, I had forgotten about the solution, fretting for several hours wondering what was wrong with my code. Best if I write it down this time, eh.

The Problem

Are your updates and inserts in Postgres not being committed?

Your are using a modern Postgres database (8.4 in my case) using ZSQL Methods from Zope or Plone to target the database through ZPsycopgDA and psycopg2. Everything seems to be fine; all your complex reads and queries work great. But when you try to update the database using either update or insert commands, nothing seems to happen. Perhaps you have even been able to verify that the update or insert commands are actually getting through OK.

It just seems as if nothing is getting committed. What is going on?

The Solution

And yes indeed, this is the problem - your transactions are not being committed.

The reason for this is to do with isolation levels provided by psycopg2. In the adaptor (ZPsycopgDA) not all levels were supported and those that were defined were mapped incorrectly. A double whammy.

The problem should have been resolved in psycopg by now (according to this thread from August 2011) but if you still have a broken version of psycopg 2.4.2, you can make the correction by editing the dtml files add.dtml and edit.dtml in the database adaptor. The selector for the Transaction Isolation Level needs modified.

In old, broken versions, it looked like this:

<select name="tilevel:int">
 <option value="1">Read committed</option>
 <option value="2" selected="YES">Serializable</option>
</select>

Instead, the selector should read something like this

<select name="tilevel:int">
 <option value="0" <dtml-if expr="tilevel==0">selected="YES"</dtml-if>>Autocommit</option>
 <option value="1" <dtml-if expr="tilevel==1">selected="YES"</dtml-if>>Read Uncommitted</option>
 <option value="2" <dtml-if expr="tilevel==2">selected="YES"</dtml-if>>Read Committed</option>
 <option value="3" <dtml-if expr="tilevel==3">selected="YES"</dtml-if>>Repeatable Read</option>
 <option value="4" <dtml-if expr="tilevel==4">selected="YES"</dtml-if>>Serializable</option>
</select>

Once you have restarted your Zope instance you can then access the ZMI and edit the database connector. Choosing “Read Committed” now will set the correct value and your transactions will be committed.

Update

Good news! As of psycopg2 v2.4.6, the distributed ZPsycopgDA has been amended and will now work out-of-the-box and this fix is no longer required. And there is more: the Zope product, ZPsycopgDA, is now available in its own package from github and as an egg on pypi; the availability on pypi should mean it can be included as a requirement in a zope buildout.conf.

 

Storing Plone users in an SQL database

January 10th, 2012

In the past, a couple of <b>Plone</b> sites we have built had large numbers of users for whom it was easier to store their details in an SQL Backend rather than Plone itself. When it came to bringing one of those system up-to-date it was appropriate to review the process and perhaps use a different approach such as a full-on LDAP deployment. During this review process, we learned how to deploy a new product and figured a solution to a possible use case.

Deploying pas.plugins.sqlalchemy

Unfortunately the help documents on the web did not help us install this product and it took some research to discover much simpler instructions buried deep in the plone.users mailinglist ; so deep it remains hidden. Here is what we did:
  1. add pas.plugins.sqlalchemy to the eggs section in buildout.cfg
  2. add pas.plugins.sqlalchemy to the zcml section in buildout.cfg
  3. adding collective.saconnect did not seem to work for us or at least was not helpful so do not install this product
  4. add a definition to the instance section in your buildout.cfg appropriately modified to point to the database you want to use; best to make sure you can get this to connect first
    zcml-additional =
      <configure xmlns="http://namespaces.zope.org/zope"
                 xmlns:db="http://namespaces.zope.org/db">
        <include package="z3c.saconfig" file="meta.zcml" />
        <db:engine xmlns="http://namespaces.zope.org/db"
                   name="pas"
                   url="mysql://user:password <at> host/database" />
        <db:session xmlns="http://namespaces.zope.org/db"
                    name="pas.plugins.sqlalchemy"
                    engine="pas" />
      </configure>
    
  5. you can now startup your Plone instance
  6. from the Plone control panel, add the PAS SQL Plugin product: this step should both connect to the database and create the database schema with empty tables
  7. to activate the plugin, access the ZMI and navigate to the acl_users folder and the plugins sub-folder. Review each plugin-type and if an sql option is available, change its precedence to suit your purposes.
Provided you have set the precedence of sql in the User Adder plugins (step 7) you can add new users and they will be stored in your SQL database Notice that the settings in step 4 apply throughout an instance. If you have several Plone instances within a single Zope instance, then each Plone instance with the PAS SQL Plugin activated will share the same user SQL database - as in the Use Case below.

Use Case for multiple sites

Note: the following notes only apply for a group of Plone sites; although the use case may be general, this solution is specific to Plone

Suppose you require a number of related sites all of which relate broadly to the same group of users:
  1. Paid-up members or Active members: assigned as Members and possibly Contributors
  2. Lapsed members: having a login but no role assignment
  3. Various organisational sub-sets of members: assigned the various management roles of Editor, Reviewer, Manager, etc
  4. Anonymous visitors: obviously have no login
In your sites, you want to allow members to be able to logon and authenticate themselves. Then depending on the site and the specific user, various roles can be assigned to allow access to different types of content. When deploying this plugin, user information is stored in the database but user and group permissions remains in Plone. Using this behaviour we can use group definitions to control who can do what in specific sites; all with a minimum of tweaking the Plone instances. First the minimal changes:
  1. set sql to the top of all its relevant plugin types except for Group Management - we want Plone instances to drive this part of user management
  2. set Intranet/Extranet Workflow as the default for each site
  3. for the internally_published state, under the Permissions tab, switch on Authenticated permission for both view and access options (this is optional and depends what you want Lapsed Members to see)
  4. activate the changes for the workflow
  5. in each site, add a membership Group with a roles you want all Active members to have
  6. for each site, add additional groups for any special subsets you may want setting roles as required
  7. add members in one site assigning each to appropriate groups for that site
  8. in additional sites, add members to any groups peculiar to these individual sites
Having done all this, you should now get the following behaviour
  1. Anonymous users: can only see Externally Published content
  2. Lapsed Members: can also see Internally Published material if they are logged in
  3. Active Members: can see all content except items marked as private and whatever additional roles you have assigned
  4. Special Members: certain members will have greater access depending on the roles they have been assigned in each site
These behaviours are easily managed simply by adding users to one or more groups. Further, a new user only needs added in one site and usually Group membership can be assigned at that time (except for Groups which are specific to individual sites). Active Membership can also be controlled by an external application updating the SQL database independent of any Plone instance i.e. for lapsed members, remove their appropriate Group membership via the database. This procedure will affect all related sites immediately without having to do anything else.
 
 
© 2013 Andy Ferguson