Illustration by Brendan Davis

Transactions by example:

This article is the second of two articles describing how to use PostgreSQL transactions with Zope and Python. In the first article in Issue 3 of ZopeMag we gave an overview of some basic elements of database design followed by a explanation of the need for transactions and how they are implemented. In this second article we will describe how we use the database structure in Zope. A basic online registration desk for a fictitious college is used as a sample application. The whole GUI will be implemented using Zope. Further, a basic authentication mechanism is described.

	Sidebar - App Server Requirements Today Application Servers must provide a minimum set of advanced capabilities. These capabilities are essential for providing the level of Quality of Service (QoS) required by e-commerce. Some examples of these advanced capabilities are: Clustering: means transparent interconnection of several systems in such a way that they behave like a single system from the end-user’s point of view; Session-level fail-over: this term refers to a back-up operation that automatically switches to a stand-by system if the primary one fails or is temporarily shut down. It redirects users from the failure or down system to a backup system that mimics the operation of the primary one transparently—users should never notice what happened; Connection pooling: refers to a cache of database connections kept in memory so that any connection can be reused if later needed; Load balancing: this refers to a way of providing the same level of processing or communication load to members of a distributed system so that none of them is overwhelmed. Zope is a good example of an Open Source AS that can provide all these advanced characteristics with a very attractive ROI.

In the first article, we introduced the basic concepts for a database application enabling on-line registration for a Virtual College. We described—in particular—the use of transactions for ensuring data integrity. However, at this point in our discussion it is a good time to present the reasons why transactional system development with Zope is so relevant. Application Servers (AS) like Zope evolved from the somewhat sophisticated Web-based, collaborative, content management framework of the mid '90s to the more complex and useful integration development tool we have today. In the late '90s, the term ''middle-ware'' was used to mean a three-tier integration application that tied together a Web-based graphic user interface, business logic, and a database (see sidebar). Nowadays, the expression middle-ware is less common and AS is the generally accepted term used for such an application .

Application Servers (AS) like Zope consist of several important components (see „Further Resources“ at the bottom of this article). The first component or front-end deals with the presentation or look-and-feel of the information; it provides an amicable Graphic User Interface (GUI)—most of the time Web-based—available both to developers and end-users.

A second component (middle-tier) provides the Business Logic; it consists of that part of the AS that carries out all tasks not related with presentation; for example, database access, transactional management, and/or data processing.

Finally, the back-end is the third and last component: it provides data or content storage. The back-end can be either a stand-alone database server like PostgreSQL or an integrated object database system like Gadfly.

The importance of AS technology is unquestionable if we consider the impact e-commerce is having on more and more aspects of our lives. Despite the so-called ''Dot.Com Crash'' of April 2000, the e-commerce business model has emerged as the ''biggest transition for business, the economy, and competition since the Industrial Revolution'' (see “Further Resources” at the bottom of this article). A substantial part of this new business model rests upon having the capability ''to create new channels to communicate and exchange transactions both internally and with customers and suppliers.'' Today, ASs are at the forefront of this battle for marshalling new capabilities such as clustering, session-level fail-over, connection pooling, load balancing, and fault-tolerance, among others (see sidebar). Right now, ASs are meant to provide the infrastructure for developing a new generation of enterprise-level services and products such as Intranets and portals and, of course, e-commerce transactional systems.

Although most application servers available nowadays have made the transition to these more advanced capabilities, just a few of them offer the advantages of Zope. One of the most important advantages is based on the fact that Zope is ''open source'' (OS). Of course, there are other ASs which are also OS. However, most of them are based on complex and obese architectural components--for instance, many of them are based on Java. Zope, on the other hand, is based on Python--a lightweight, latest-generation OS scripting language. By using Python as its foundation Zope is highly suited to deal efficiently and inexpensively with the challenges arising from modern-day e-commerce.

In the present article we will give some idea of the basic problems that remain behind the complexity of transactional systems and of how well Zope and Python deal with them. Earlier, in the first part, we already laid some foundations, describing the database design and sketching our business logic; it is now time to build our graphic user interface with Zope.

We use several Zope DTML methods to implement a very simple Web-based graphic user interface. Basically, registering new classes consists of several steps. The USE CASE diagram shown in Figure 1 illustrates these steps from the user's point of view. A USE CASE is a simple task or sequence of tasks performed by end-users, which has a useful outcome. It describes on a high-level what a system does, but from the end-user's point of view. In our case, it describes how an end-user uses the on-line registration desk to reserve a seat in up to four different classes.

Figure 1 - Use Case Diagram

First, the student must log in to the system and use their Social Security Number as a user ID and an alphanumeric PIN as a password. We do not use Zope's user management capabilities to handle this information—mainly because doing to would require a large amount of code that would undermine the simplicity of our design. Instead, I define a new hierarchy of objects that starts with studentsand ends with security_pins. This hierarchy uses a ONE-TO-ONE (1:1) relationship to associate each instance of students with only one instance of security_pins. Figure 2 below illustrates this relationship graphically. To authenticate, the user enters their ID and PIN, and then Zope calls the Python External Method authenticate() located in vc_authenticate.py shown in Listing 1

Listing 1: Download External Method vc_authenticate.py

to validate this information against the back-end. The External Method vc_authenticate.py uses a Python PostgreSQL module to connect to our database VIRTUALCOLLEGE. Once the connection has been established, the script reads the PIN stored in the back-end and compares it with the one entered by the user. If they are the same, the user is authenticated; otherwise, an error message is returned. Of course, this authenticating method lacks robustness but it solves the problem with just a few lines of code.

Figure 2 - E-R Diagram

Figure 3 shows the College's home page. Our fictitious college is located in Mayberry, North Carolina, the fictitious town of Andy Griffith's infamous show (we all know how fond Python users are of TV sitcoms). The only link available is ''Admissions'' which goes directly to the Admissions' Home Page as depicted in Figure 4.

Figure 3 - College's Home Page

Figure 4 - Admissions' Home Page

Clicking on “Online Registration“ takes us to the screen shown in Figure 5. students must fill in a form, submitting their Social Security Number and PIN code. We assume that each student has a PIN previously assigned by the College's administration. The DTML code that generates this screen is the same one that executes the External Method authenticate() mentioned above. It is available in the downloadable source package (see “Further Resources“).

Figure 5 - Sign-in Page

After the user has been authenticated, a session is established. We keep track of the information about this session through the standard cookie-based mechanism.

Listing 2: shows the authenticate_user DTML method and how the userid is stored in a cookie as current_user

Figure 6 - Class Selection

After the session has been initiated, the user is redirected to [Zope-Home]/virtualcollege/admissions/online_registration/step_1_html. At this point, the user may select up to 4 classes for registration. Figure 6 above shows the Web-form used to select these classes. Notice that the current user ID or Social Security Number is shown at the middle-left of the page. It is by means of this number--after the student has logged in—that the system keeps track of the user's identity to guarantee session-level fail-over as explained above. As shown, the class' alphanumeric code is used to identify each available class listed.

Listing 3: shows the DTML code for this method

Listing 4: shows confirm_registration

-- here the user is prompted to confirm their selection before proceeding with the actual registration.

Figure 7 shows the confirmation page, with a table listing all the classes selected. If the student accepts his/her selection, the Confirm button takes him to the next step. Otherwise, if he decides to cancel the registration, the Cancel button takes him back to the previous step. Unfortunately, in our protoype, stiudents can only cancel the whole registration, rolling over the whole transaction—an alternative to our approach could be using stored procedures as explained in Part 1 to invoque individual transactions for each class so that we could roll them over individually. A sequence of <dtml-if> statements is used to hide unnecessary options--or unselected class codes--when the page is rendered by the browser (actually, the source code or IF statement passes through all options but it considers only those that were selected) . Although this method is rather inelegant (it does not validate any data — for example, it does not check if the selected classes are actually open or available for a particular semester or term), it works well for our rapid-prototype example.

Figure 7 - Confirmation Page

An interesting component of the user interface is the proceed_registration method which is invoked by confirm_registration as shown in Listing 4 .

Listing 5: shows the code for this method

<dtml-var expr="python_scripts.process

(userID = REQUEST.get('current_user',None), classList=code_name)">.

This <dtml-var> statement calls the External Method process() located in vc_process.py .

Listing 6: Download External Method vc_process.py

This External Method-- shown in Listing 6 -- implements the functional model (and the business logic as well) described in the first part of this article.

The approach used for this method is simple and straightforward. It implements all the SQL queries listed in Figure 4 in the first part of this article [LINK or show it here? This is fig_4.png], as well as some additional stuff. Before starting the BEGIN WORK...COMMIT WORK sequence, the script translates the Social Security Number and course codes to the corresponding student_id and class_id identifiers. Then, the script checks how many students have been registered in each class selected before proceeding to add a new registrant. If the seat limit has been reached for any class, the script returns an error message telling the student that there is no more room available in that particular class.

After the initial translation and validation stage, a transaction begins with the BEGIN WORK statement. The BEGIN WORK statement is executed followed by a LOCK TABLE statement to prevent concurrent access to sit_limit as described in the functional model in the first article [LINK]. After that, the necessary steps are followed until the COMMIT WORK statement is executed, ending the transaction. At any point, a sequence of try...except statements ensures that if anything untoward happens in between, the transaction is rolled back to the status prior to initiation of the transaction and the corresponding error message is returned.

Finally, I used several ZSQL methods to extract certain information from the database; for instance, the information necessary to generate a basic online catalog that lists the available classes for the current semester, and to generate a final report that summarizes the registration process.

Listing 7: shows one of these ZQSL methods

In these two articles we have explored the use of PostgreSQL transactions by implementing an online registration desk developed using Zope and Python. We went into some detail about what transactions are, why they are used, and how they can be implemented with Zope and Python. We explained how to develop both the data and functional models required to specify the structure and behavior of our design.

In this second part we elaborated a little more on our functional model, describing the business logic behind it but incorporating the perspective of the end-user. We used USE CASE diagrams to illustrate the interface between end-user and our system. Finally, we described the graphic user interface, explaining the logic behind each DTML or Python method used.

As a final comment, it should be mentioned that our application is a rapid prototype, a partial solution to the problem that deals with an extremely reduced scenario. No data validation was implemented and the error handling scheme was kept as simple as possible for the sake of clarity. We invite our readers to elaborate a more refined version of this program.

Information about PostgreSQL may be found at this URL:
http://www.postgresql.org

An interesting discussion about the impact of transactional systems on e-commerce may be found at:
http://www.firstmonday.dk/issues/issue3_10/bambury/

Information about the necessary modules and adapters to access PostgreSQL from Python and Zope may be found at:
http://www.zope.org/Members/aho/oio_utils/ZPyGreSQL_to_ZPsycopg

The following URL links to the chapter in the PostgreSQL manual that describes transactions and locks:
http://www.guides.sk/pgsql/chapters/transactions/