Half Cooked Solutions

Browsing Interface

To provide an adaptor for a new database create a new module which should go in the same directory as the rest of the application code. The convention for naming these modules is <em>database name</em>Browse.py, (e.g. mySQLBrowse.py). The name is not crucial, but the module must contain a class called browse

This class must support the following methods;

__init__(self, connection)

The connection parameter is a dictionary. This dictionary can contain either the keys username, password and database or databasename and directory The first variation is for traditional relational databases that support named user access such as Oracle, MySQL and PostgreSQL. The second option is for the more simple types of data store like SQLite which just allow us to specify a file which contains our data. The purpose of this method is to establish our connection to the database and, if successful, to establish two attributes (_db and _cursor) which are used by the other methods.

getTables(self)

This method should return a list of all of the available tables in the database. Where the database supports secured access this list should only contain those tables to which the current user has access. In the Oracle module, for instance, this is achieved by querying user_tables rather than all_tables. The returned list probably doesn't want to include system or data dictionary tables. In the MySQL module, for instance, the system tables (whose names are stored in the _system_tables attribute) are explicitly exluded.

getColumns(self, tableName)

This method returns a list containing the details of all of the columns in tableName. Each item in the list is a tuple detailing a single column. These tuples contain (in order), the column's name, the data type, the precision (or more usually the maximum number of characters or digits the column can contain), whether the column is nullable ('YES' or 'NO'), whether it forms part of a key ('PRI' or 'UNI'), lastly the default value, if one is defined.

getQueryString(self, tableName)

This method must return a string which contains a valid query string for tableName. It is primarily used by the 'get' functions and isn't necessary for dbBrowser to function. In those modules which use Python DB-API compliant modules it should be possible to take this string and pass it to the execute method of a cursor object directly.

getRow(self, tableName)

This method should return a single row from tableName. As per the Python DB-API this will be a sequence (either a tuple or a list depending on how the database module implements the fetchone method).

getRows(self, tableName)

This method should return all of the rows from tableName. As per the Python DB-API this will be a sequence of sequences. This method will use the fetchall method as opposed to getRow's use of fetchone.

Possible improvements to the code that I've spotted whilst writing this documentation are;

• Normalise the keys used in the connection dictionary. We should have a consistent database name, at present it uses database or databaseName.

• Raise an exception when the __init__ method fails to establish a connection to our selected database. At the moment the calling module checks to see if self._cursor contains a valid cursor object.

• The tuples returned from the getColumns method should be more generic. At the moment every backend attempts to mimic the MySQL describe function.

• The getRow method should be turned into an iterator that when called repeatedly will return sequential rows. At the moment it will always return the first row that the database supplies.

• The getRow method could be moved into a super class and then only overriden for non RDBMS backends

• The getRows method could be moved into a super class and then only overriden for non RDBMS backends

Last updated: 2nd September, 2003. Author: Andy Todd <andy47@halfcooked.com>