Day: January 28, 2009

Development

New Project: SPDO

I have just posted the (embarrassingly empty) page for my new pet project: SPDO (Sjan’s PDO) – in two flavors: PHP and Python.

There are only about a thousand PDOs out there, and perhaps a dozen or so of them are really functional and (in a few cases) very polished pieces of work. So why am I messing with writing my own? A couple of reasons:

  1. I like to have coding signatures that are consistent, whether I am working with PostgreSQL, MySQL or (heaven help us all) SQLite.
  2. I like to have coding signatures that are (reasonably) consistent across different languages – in this case Python and PHP.
  3. I wanted to take advantage of Prepared Statements where I could, even though the PHP implementations of those are pretty weak (especially in the case of MySQL).

Currently implemented in

  • Python:
    • PostgreSQL (with prepared statements)
    • SQLite (no prepared statements).
  • PHP
    • PostgreSQL (with prepared statements)
    • MySQL (with prepared statements)
    • SQLite (no prepared statements)

Here’s an example of how they work (in most simplistic terms):

#in Python
from pyDB import *
db = pyDB('mysite')
newid = db.insert('INSERT INTO test (name, value) VALUES (?,?)',['foo','bar'])
update_count = db.update('UPDATE test SET value=? WHERE id=?',['baz',newid])
results = db.select('SELECT * FROM test')
for row in results:
    for i in row:
        print "\t", i,"\t", row[i]
delete_count = db.delete('DELETE FROM test WHERE id=?',[newid])
//in PHP
require_once('phpdb.php');
$db = new phpDB('test');
$newid = $db->insert('INSERT INTO test (name, value) VALUES (?,?)',array('foo','bar'));
$update_count = $db->update('UPDATE test SET value=? WHERE id=?',array('baz',newid));
$results = db->select('SELECT * FROM test');
foreach($results as $row)
{
    foreach ($row as $key=>$val)
    {
        print "\t$key\t$val";
    }
}
$delete_count = $db->delete('DELETE FROM test WHERE id=?',array($newid));

The page with links to the code is in the list up top, and everything is MIT license. Enjoy.