SPDO: Simplified (PHP|Python) Database Object

Overview

SPDO is two PDOs. One PHP Database Object and one Python Database Object. The goal of SPDO is to have tools that work as alike as possible in Python and PHP with PostgreSQL, MySQL and sqlite.

Advantages/Disadvantages

The Advantages:

• Since the method signatures for both the PHP and Python versions are as alike as possible it makes moving from one language to another quite simple.
• The MySQL and PostgreSQL versions make use of Prepared Statements, which means that recurring calls to the same query signature are faster than having to plan the entire query every time.
• As of 1.0bRC-1 SPDO now has a DbBuilder? class that allows tables to be built programmatically with indexed columns and foreign key relations enforced either directly (in PostgreSQL) or through the use of triggers (in MySQL and sqlite).

The Disadvantages:

• The MySQL and PostgreSQL versions make use of Prepared Statements, which means that one-off queries have a little more overhead than needed. There is a work-around by calling query() ($db->query() or db.query() depending on PHP/Python) rather than insert, update, delete or select, but dealing with returned data requires that you treat it as if making the raw calls in your code; which of course means that any code that uses that is **not** portable between databases.
• Even though the sqlite C drivers have a prepare() function I have (as yet) found no documentation on using Prepared Statements in sqlite. So, until I find the needed documentation for that sqlite in SPDO does not support Prepared Statements. Since sqlite is not my first choice for a busy database that would benefit from Prepared Statements, however, I am willing to let this go for now.

Changelog

version	revision	date	changes
1.0	108	2009-07-10	Comment clean-up. Tagged for release
1.0bRC-1	103	2009-03-08	1.0 beta Release Candidate 1. Added DbBuilder class to create tables, indexes and foreign key relations. Added Exception classes !SPDOError, ConnectionFailedError and ForeignKeyViolationError.
0.9	98	2009-02-02	First tag release: version 0.9.
0.9	97	2009-02-02	Modified Python comments to work better with Python documenters.
0.9	94	2009-01-30	Added query() and ps_query() Python version. Changed hard tabs in PHP to 4 spaces.
0.9	91	2009-01-29	Added !MySQL support to the Python version. Changed the class names to SPDO, and changed file names to reflect the change. First version in SVN
0.8		2009-01-28	First version released as pyDB and phpDB.

Tested Environments

SPDO	OS	Lang	DB	Notes
1.0bRC-1	OSX 10.5.6	Python 2.5	PostgreSQL 8.3	uses PyGreSQL from easy_install
1.0bRC-1	OSX 10.5.6	Python 2.5	MySQL 5.0.41	unable to build MySQLdb
1.0bRC-1	OSX 10.5.6	Python 2.5	sqlite 3.4.0	uses built-in sqlite3 from Python 2.5
1.0bRC-1	OSX 10.5.6	PHP 5.2.3	PostgreSQL 8.3
1.0bRC-1	OSX 10.5.6	PHP 5.2.3	MySQL 5.0.41
1.0bRC-1	OSX 10.5.6	PHP 5.2.3	sqlite 3.4.0
1.0bRC-1	Linux (Gentoo 2.6.27-gentoo-r7)	Python 2.5	PostgreSQL 8.3	uses PyGreSQL from emerge pygresql
1.0bRC-1	Linux (Gentoo 2.6.27-gentoo-r7)	Python 2.5	MySQL 5.0.70	uses MySQLdb from emerge mysql-python
1.0bRC-1	Linux (Gentoo 2.6.27-gentoo-r7)	Python 2.5	sqlite 3.4.0	uses built-in sqlite3 from Python 2.5
1.0bRC-1	Linux (Gentoo 2.6.27-gentoo-r7)	PHP 5.2.3	PostgreSQL 8.0.15
1.0bRC-1	Linux (Gentoo 2.6.27-gentoo-r7)	PHP 5.2.3	MySQL 5.0.70
1.0bRC-1	Linux (Gentoo 2.6.27-gentoo-r7)	PHP 5.2.3	sqlite 3.6.6.2
0.9	OSX 10.5.6	Python 2.5	PostgreSQL 8.3	uses PyGreSQL from easy_install
0.9	OSX 10.5.6	Python 2.5	MySQL 5.0.41	unable to build MySQLdb
0.9	OSX 10.5.6	Python 2.5	sqlite 3.4.0	uses built-in sqlite3 from Python 2.5
0.9	OSX 10.5.6	PHP 5.2.3	PostgreSQL 8.3
0.9	OSX 10.5.6	PHP 5.2.3	MySQL 5.0.41
0.9	OSX 10.5.6	PHP 5.2.3	sqlite 3.4.0
0.9	Linux (Gentoo 2.6.27-gentoo-r7)	Python 2.5	PostgreSQL 8.3	uses PyGreSQL from emerge pygresql
0.9	Linux (Gentoo 2.6.27-gentoo-r7)	Python 2.5	MySQL 5.0.70	uses MySQLdb from emerge mysql-python
0.9	Linux (Gentoo 2.6.27-gentoo-r7)	Python 2.5	sqlite 3.4.0	uses built-in sqlite3 from Python 2.5
0.9	Linux (Gentoo 2.6.27-gentoo-r7)	PHP 5.2.3	PostgreSQL 8.0.15
0.9	Linux (Gentoo 2.6.27-gentoo-r7)	PHP 5.2.3	MySQL 5.0.70
0.9	Linux (Gentoo 2.6.27-gentoo-r7)	PHP 5.2.3	sqlite 3.6.6.2

Documentation

Notes about portability

When developing applications against a single RDBMS there is no need to worry about portability. However, when you are developing with the idea that the application may be deployed against any of a range of RDBMSes then you need to start thinking about how you formulate your queries, table structures, etc. While it is common practice to have separate classes for working with different databases, there are some things you can do to make that even easier.

ENUMs are EVIL

Developers who spend a lot of time developing against MySQL get into the habit of creating ENUM fields. (I will admit a certain amount of guilt here, too). This is not only not portable, but it doesn't work quite the way you would expect. Even though the values in the ENUM are meant to be the only values you can insert into that field, MySQL does not enforce this quite the way you might think it should. As an example:

CREATE TABLE enumevil (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    evil ENUM('foo','bar','baz') NOT NULL DEFAULT 'foo'
);
Query OK, 0 rows affected (0.01 sec)
INSERT INTO enumevil (evil) VALUES ('bip');
Query OK, 1 row affected, 1 warning (0.02 sec)
SELECT * FROM enumevil;
+----+------+
| id | evil |
+----+------+
|  1 |      |
+----+------+
1 row IN SET (0.00 sec)

Rather than spitting back an error, it quietly sets the value of the ENUM field to the empty string. That is not in the list of allowed values nor is it the default. So you are left with checking the values in code before inserting (which you should do anyway - see the next section on referential integrity.)
Instead of using an ENUM field consider this:

CREATE TABLE noevil_enum_choice (
    choice VARCHAR(3) NOT NULL PRIMARY KEY
);
INSERT INTO noevil_enum_choice VALUES ('foo');
INSERT INTO noevil_enum_choice VALUES ('bar');
INSERT INTO noevil_enum_choice VALUES ('baz');
CREATE TABLE noevil (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    choice VARCHAR(3) NOT NULL REFERENCES noevil_enum_choice.choice
);

Although MySQL does not enforce referential integrity, this is now something can be easily converted to PostgreSQL as:

CREATE TABLE noevil_enum_choice (
    choice VARCHAR(3) NOT NULL PRIMARY KEY
);
INSERT INTO noevil_enum_choice VALUES ('foo');
INSERT INTO noevil_enum_choice VALUES ('bar');
INSERT INTO noevil_enum_choice VALUES ('baz');
CREATE TABLE noevil (
    id SERIAL PRIMARY KEY,
    choice VARCHAR(3) NOT NULL REFERENCES noevil_enum_choice(choice)
);

And PostgreSQL does enforce referential integrity. This also makes it easier to expand your choice list by just inserting a new row.

Referential Integrity

On the flip-side of the coin, developers who spend a good deal of time developing against PostgreSQL come to rely on the referential integrity built in to that RDBMS. While there is nothing wrong with that, your code should enforce this as well, if you want to be able to move from one RDBMS to another. In the example above we could rely on PostgreSQL's REFERENCES statement to kick back an error whenever a bad value (like 'bip') is inserted into the choice field of the noevil table. However, as soon as you move your application to MySQL or sqlite it will happily insert anything you like into that field (with MySQL truncating it to three characters). This is why it is important for your applications to take into consideration their own referential integrity. Here's some python to illustrate:

import string
from spdo import *

class noevil(object):
    __init__(self, choice):
        self.db = SPDO('test')
        self.choices = []
        mydict = self.db.select("SELECT * FROM noevil_enum_choice")
        for c in mydict:
            self.choices.append(c['choice'])
        if choice not in choices:
            ex[] = ["Invalid value for choice: ", choice, " Valid options are: "]
            for c in self.choices:
                ex.append[c]
                ex.append[" "]
            raise Exception(string.join(ex, ''))
        # continue with normal processing here

spdo-py

The documentation for spdo-py is at SPDO-PY

spdo-php

The documentation for spdo-php is at SPDO-PHP

Trac Help

• TracGuide -- Built-in Documentation
• The Trac project -- Trac Open Source Project
• Trac FAQ -- Frequently Asked Questions
• TracSupport -- Trac Support

For a complete list of local wiki pages, see TitleIndex.