Monday, December 26, 2005

DTuple for O/R mapping

Why use ActiveRecord or SQLObject when there is DTuple?

Both of these systems attempt to hide sql through an object oriented abstraction. (I thought that this was something that only the J2EE guys tried to do.) Long story short it won't perform nearly as well as hand coded SQL and it may be trying to solve one problem by introducing another. Why learn another DSL when SQL works so well?

It's not the mapping that I have issue with it's the fact that sql is code generated behind the scenes as if it were not meant for human consumption. Don't get me wrong I am a huge fan of O/R mapping and I am a huge fan of abstraction, however I think that attempting to talk to a database while speaking objects actually makes it harder to express what you actually want the database to do. The other disadvantage that this approach has is that it may make your code hard to debug.

What am I suggesting? Well for Ruby I am not quite sure but for python there is DTuple -- written by Greg Stein. I am not sure why DTuple gets no ink but it should. First off it has been around 4-ever since 1996 I think, and secondly it roks! It examines the result of your query and returns a collection of typed objects. This is done dynamically and preforms quite nicely. You basically get O/R Mapping for free and don't have to learn any object query syntax.

Example Data Access object:
(caution this code is almost 2 years old and may need a little tweaking with the latest version of MySQLdb)

Pay special attention to the get_records where DTuple is used...

import dtuple
import MySQLdb
from common.util import config

def _connect():
""" Login to the database return the connection
"""
conf = config.get_config()
return MySQLdb.connect(
host=conf.get('db', 'host'),
user=conf.get('db', 'user'),
passwd=conf.get('db', 'passwd'),
db=conf.get('db','database'))

def _get_conn(relogin = 0):
""" Returns a persistent connection to the database
attempts to reconnect if the connection is lost.
"""
global CONN

if relogin:
CONN = _connect()
return CONN
else:
try:
conn = CONN
return conn
except NameError:
CONN = _connect()
return CONN

def _exec_stmt(stmt):
""" Runs SQL statement and returns (count, curs)
"""
curs = None
try:
curs = _get_conn().cursor()
count = curs.execute(stmt)
except MySQLdb.OperationalError, e:
curs = _get_conn(relogin=1).cursor()
count = curs.execute(stmt)
return count, curs

def do_test(stmt):
count, curs = _exec_stmt(stmt)
try: return curs.fetchall()
finally: curs.close()

def get_first_record(stmt=None):
""" Runs select statement and returns 1st record
"""
rec = get_records(stmt, 0, 1)
if rec[0] == 1: return rec[1][0]
return None

def get_records(stmt=None, start=0, max=500):
""" Runs select statement and returns a list of the form:
(record count, result set)
"""
if stmt == None:
return None
limit_stmt = "%s limit %d, %d" % (stmt, start, max)
count, curs = _exec_stmt(limit_stmt)
try:
return count, map(
lambda x:
dtuple.DatabaseTuple(
dtuple.TupleDescriptor(curs.description),
x),
curs.fetchall())
finally: curs.close()

def do_insert(stmt=None):
""" Runs insert statement and returns the id of the newly
inserted record or -1 on error.
"""
if stmt == None:
return -1
count, curs = _exec_stmt(stmt)
if curs == None:
return -1
try: return curs.insert_id()
finally: curs.close()

def do_update(stmt=None):
""" Runs update statement and returns the number of records
updated or -1 on failure.
"""
if stmt == None:
return -1
count, curs = _exec_stmt(stmt)
curs.close()
return count

def do_delete(stmt=None):
""" Runs delete statement and returns the number of records
deleted or -1 on failure.
"""
return do_update(stmt) # same impl as update

def escape_string(str=""):
return MySQLdb.escape_string(str)

1 comment:

Anonymous said...

We've been using DTuple in all my data access code for my site and the other apps written in python with MySQL for a while now and its working great. I can't say that I've tried ActiveRecord and I do hear lots of good things about it, but it seems like a layer of auto generated confusion that I don't really want to have to deal with. If it was guaranteed to work perfectly no matter what I wanted it to do I guess I could go for it, but once I have to dig through something I didn't write myself I think I'd just take a pass and go back to doing it the old fashioned way.