Data Access Objects - or- Why CRUD really gets on my nerves

Why I hate CRUD.

BTW: I’m in a bit of, as my husband says "pissy" mood (to which I protest I’m NOT PISSY!!! so it turns into sort of a game). So I’m being a bit sarcastic in this post.

No, not the piles of mouse crud and newspaper shredding you find under a china cabinet when you move your grandparents after having lived in the same house 46+ years EGADS!!!!!!!!!! I’m talking about:

C reate

R replace

U pdate

D elete

When you have an application with sql statements scattered here and there, and then you need to change a fieldname, or table name, or something. What do you have to do? Make sure you update all sql statements that reference that field/table through out the masses of code. What’s more, if you typo’ed a field name, or forgot to add a comma (I’ve done that) then you have to find it and fix it. Wouldn’t it be nice to have all sql queries in one place?

Whats a DAO??

As described by Martin Fowler:
"An object that wraps a row in a database table or view, encapsulates
the database access, and adds domain logic on that data."

This comes up so often in web development, get input from a form, validate, save in database. Select data from database, display. Blah blah blah. This is a “common task” so there is a “common solution” called a Design Pattern, for those of you new to the concept. This particular one is called Data Access Objects (DAOs). There are a number of DAO packages out there or if you like, or you can make your own.

Typically they look like this (the is psudeo-code, only looks suspiciously like php because I think in php!):

Class Person {
var $id;
var $name;
var $age;
var $companyId;

function create($name, $age) {
// sql to create new database entry

function update($name, $age) {
// sql to update entry

function getCompany() {
// retrieves company information, possibly returning a company DAO

function getById($id) {
// sql to retrieve record with id= $id and load values into members

function getByName($name) {
// sql to retrieve record with name=$name and load values into members name and age

function getAll() {
// retrieve all records

PEAR’s DB_DataObject
Works in conjuction with PearDB, probably one of the most popular database abstraction packages out there. (some confuse DAO with Database Abstraction, they are different!) Allows linking with multiple tables. Pretty nice.

DotProject has a nice DAO system, using a class CDpObject. They use ADODB as their db abstraction, but enclose it in an easy to use wrapper.

SugarCRM has a “Sugar Bean” that looks like a DAO system, but I haven’t taken more than a casual glance at it and said “wow..cooool beans”

(TIP: Good way to learn programming – look at Open Source projects and see how they do it! then evaluate to see if its good)

The most annoying is when, you find you need a DAO and are unable (reasons I can’t explain even if I wanted to) to use a DAO package at my job and it just drives you NUTS to write your own when some already exist (hence the reason for my frustration). Some people may enjoy reinventing the wheel, not me. I have more important things to do… I will delvuge somethings in a few days that I've learned about Ruby and DAOs... very cool stuff indeed.

Meanwhile, you can read more about this topic:

Any other links you know of that might be helpful? add a comment!

Thanks, I feel better now. :)

Design Patterns

The nice thing about a good solid DAO is that it begins to cleanly separate the storage mechanism (database vendor AND format) and allows for a bit more flexibility in HOW things happen.

For example, on a recent project, we were having all kinds of concurrency issues with numerous threads having different work queues that were being processed. By moving the various work queues into a single work queue with a simple DAO, the system speed is up and collisions happen a maximum of a few times a day as opposed to every few minutes.

And the best thing is that when a collision happens, we can detect it that much more easily and recover more smoothly.

hey, I still do PHP!

Active Record is cool.. but thats on Ruby. I still do PHP and would love something handy for DAO's in php. I'll check out your link, thanks.