Illustration by Brendan Davis

Storing User Data in a DB

The first part of Customized User Folder - published in ZopeMag Issue 1 - explained the Zope Security Structure and how you can use its existing classes to create your own User Folders. It described a way to store information about your users which is not included in the Zope user structure, e.g. an e-mail address. The user data, however, was still stored in the Zope Object Database (ZODB). For smaller businesses and websites, this may be sufficient, but if you already have a large user database or would like to start using a relational database to store future users, you will need a different kind of User Folder.

In this article, I explain various aspects of how to connect a relational database to Zope and how to store user data in and retrieve user data from this database. I used the downloadable Zope product included in this article for managing the user data.

The first decision you will need to make is which database you want to use (unless you already have a user database - then the decision has obviously already been made). For this article I used a Microsoft Access database. Depending on the database you use, you then need to install the respective database adapter (DA) for Zope. For Microsoft Access, I needed the Zope ODBC database adapter (ZODBCDA). After installing the DA, I created a user database in Access with the following specifics:

1. Table name: UserTable
2. Table fields:

The next step was to create a System DSN (Data Source Name), which is a database identifier that enables the ZODBCDA to connect to the database. The System DSN is created via the ODBC Data Source Administrator (Win98) or the Data Source ODBC (Win2000, System Tools) in your Windows Control Panel. It describes the configuration of a specific database and stores it under an alias name. For my database I used the alias name ‘UserDB’. Note: This step is only necessary if you use an ODBC data source.

The database is now accessible for any source that knows how to read the System DSN. Here is where the DA comes into play. In Zope, I can now create a Z ODBC database connection.

Figure 1 - Adding the database connection

Figure 1 shows the Zope add form for the database connection. The System DSN for my database (this is the alias name chosen above) shows up in the data source field and I can select it there. I could also enter the connection string for the database, which in this case would just be ‘UserDB’ because I did not specify a username and password for the DB. As you can see at the bottom of the screenshot in Figure 1, a usual connection string has the following format: dsn username password

I have now set up a user table in our database and made the table accessible to Zope. I will now describe how to manage the data in the database over Zope by means of a Zope product I call DBUserFolder.

Note: It is best to have the database connection in the Zope root folder so that it can be accessed from anywhere within Zope.

The user folder product consists of two classes: A class for user objects (here: DBUser class) and a class for the user folder objects (here: DBUserFolder class).

The DBUser class contains the constructor method for a user object, i.e. the user’s attributes - such as login, fname, lname, etc. - are set when a user is created. The Class also contains methods that return these attributes - such as getLName(). The class uses the BasicUser class as a base class and therefore inherits all the methods from this class (see "Customized User Folders Part I" in Zope Mag Issue 1). The only methods that need to be implemented by the programmer in the DBUser class, therefore, are the ones that deal with the special attributes unique to the DBUser. These additional methods all look like the method in Listing 1. You can see that the DBUser class methods do not interact with the database at all.

Listing 1 - getUserName()

1.	def getUserName( self ):
2.	     "return the username of a user"
3.	     return self._login

The DBUserFolder class is much more complex. Since it deals with adding, editing and deleting of DBUsers, it has to interact with the database. This interaction is carried out via Z SQL Queries. In order to be able to call the queries from the DBUserFolder class, they have to be defined as attributes of this class. This is described in the next section.

First, let’s see what queries are needed for the DBUserFolder product. Obviously, there have to be queries that add, edit/update and delete users. But there also need to be queries which return all entries in UserTable or only part of the entries.

The queries I use in the DBUserFolder product are:

The queries are stored as text files in a subdirectory of the DBUserFolder product directory called ‘DBMethods’. Listing 2 shows the query for adding a user, addUserQuery.

Listing 2 - addUserQuery

1.	add a new user to the database

2.	login lname fname email password roles domains

3.	insert into UserTable (
4.	login, lname, fname, email, password, roles, domains) 
5.	values (
6.	<dtml-sqlvar login type="string">,
7.	<dtml-sqlvar lname type="string">,
8.	<dtml-sqlvar fname type="string">,
9.	<dtml-sqlvar email type="string">,
10.	<dtml-sqlvar password type="string">,
11.	<dtml-sqlvar roles type="string">,
12.	<dtml-sqlvar domains type="string">
13.	)

Line 1 will later become the title of the query in Zope, line 2 contains the arguments for the query and lines 3 to 13 are the query statement. The filename will be the id of the query.

How do the queries become attributes of the DBUserFolder object? This is done by the manage_addDBUserFolder() method, which creates the DBUserFolder object and manages everything else that needs to be carried out to ensure that the DBUserFolder is fully functional in Zope.

Listing 3 shows a snippet from the manage_addDBUserFolder() method. The for-loop iterates through the files (addUserQuery, etc.) in the DBMethods directory and calls the _loadMethod() method to add them as queries to the DBUserFolder object.

Listing 3 - snippet from manage_addDBUserFolder() in DBUserFolder.py

Click Here for the code example

The method _loadMethod() is defined in the DBUserFolder class and can therefore be called via a DBUserFolder object. The method reads the content of a file and divides it into three parts: the title, the arguments for the query and the query statement (see Listing 2).

The queries are now a part of every DBUserFolder object and can be called like other attributes. To add new queries and edit or delete existing ones, the DBUserFolder class contains additional methods, such as manage_addMethod().

Figure 2 - Queries view of the DBUserFolder object

For the queries tab to be integrated into the views of a DBUserFolder object, it has to be added to the manage_options attribute of the class DBUserFolder (see line 3, Listing 4).

Listing 4 - snippet from class DBUserFolder

1.	manage_options = (
2.	{ 'label': 'Users', 'action': 'manage_UsersForm', },
3.	{ 'label': 'Queries', 'action': 'manage_methodsForm', },
4.	) + BasicUserFolder.manage_options[ 1: ]

One needs two Python methods each for adding, editing and deleting users. The first method (for example, manage_addUser() ), is called from a form which provides all the necessary data for a new user: login, first name, last name, password (and confirmation), roles, domains and e-mail address. This method confirms that all data was submitted, that password and password confirmation matched and that the login chosen was long enough. If this is not the case, the method returns a MessageDialog stating the type of error. If everything is in order, this method calls the second method (for example, _doAddUser() (see Listing 5) ).

Listing 5 - snippet from manage_addUser() in DBUserFolder.py

1.	...
2.	# call backend method
3.	self._doAddUser
( login, lname, fname, email, password, roles, domains )
4.	...

The second method interacts with the database and performs the respective action: adds, edits/updates or deletes a user from the database table UserTable. Line 9 of Listing 6 shows how a query (addUserQuery) is called from a method. In contrast with a method call, it is necessary here to state each argument and then the argument’s value, e.g. login = login.

Note: Depending on your database and language, the data you send to the query may need to be formatted before being sent to the database (see the method _formatText() in DBUserFolder.py for a German version).

Listing 6 - snippet from _doAddUser() in DBUserFolder.py

Click Here for the code example

Because the values in the UserTable database are normal text and therefore readable by anyone with access to the database, you may want to encode the password. This can be done easily with the help of the modules md5 and base64.

The method digest() of the module md5 converts a normal text string into the string’s digest, a 16-byte string which may contain non-ASCII characters. This digest can then be encoded using the encodestring() method of the module base64.

Listing 7 - snippet from _doAddUser() in DBUserFolder.py

...
password = base64.encodestring( md5.new( password ).digest() )
...

When a user is edited one often only needs to change one or two of the user's attributes. In this case, after the user form is filled out some of the variables will have no value. Since it is always necessary when calling a query to state an argument’s name and then its value, the editUserQuery can be called only giving all the names and values of those arguments that were entered into the edit form.

The easiest way to program this without knowing which arguments exist at this point is to create a dictionary with the argument values that exist (see lines 3 to 22, Listing 8). To use this dictionary with the query, call the apply() method giving the query and the dictionary as arguments (line 24, Listing 8).

Listing 8 - _doChangeUser() in DBUserFolder.py

Click Here for the code example

So far, the DBUserFolder class consists of methods to manage new or existing queries and users. Other methods are needed to display, for example, the list of users in the Users view of a DBUserFolder object or to display the data for one user in an edit form. Listing 9 shows one of these additional methods. Again, this method uses one of the queries to receive data from the database table (line 6).

Listing 9 - Method getUserNames()

1.	def getUserNames( self ):
2.	     "get list of users' names"
3.	     # initialize
4.	     result = []
5.	     # query user table
6.	     tuples = self.getUserNamesQuery()
7.	     # format, sort and return list names
8.	     result = map( lambda x: x[0], tuples )
9.	     result.sort()
10.	     return result

Further methods used in the DBUserFolder class are:

	getUser()
	getUserByNr()
	getUsers()

Although you could probably do without these methods and just use DTML and the SQL queries, most of the the time it's easier to do things in Python than in DTML. Therefore, to get clean code, it is probably better to include the additional methods required as Python methods in your classes, rather than writing DTML code.

I hope this article and the DBUserFolder product - which you can download here -- have given you an understandable introduction to how a relational database can be used to store user information.