Re: What databases have taught me

> >>> The main problem in OO enterprise applications is the desire to
> >>> decouple SQL statements. This causes code bloats.
> >>
> >> In those cases where it causes code bloat, it should not be done. In
> >> most cases that I have experience with, decoupling SQL causes a
> >> *reduction* in code.
> >
> > Lets say I want to find all the names of employees older than a given
> > age living in a given city. Using embedded SQL I just call:
> >
> > select name from employees where today()-date_of_birth > ? and city = ?
>
> The ?'s get loaded from somewhere. You might want to show that code too.

select name from employees where today()-date_of_birth > :age and city = :city

> > Using a decoupled approach I have to do this
> >
> > findAllEmployeesOlderThanLivingIn(age, city);
> >
> > [Interface definition]
> > findAllEmployeesOlderThanLivingIn(age, city);
> >
> > [Implementation]
> > findAllEmployeesOlderThanLivingIn(age, city)
> > {
> > select name, and all other columns
> > from employees where today()-date_of_birth > ? and city = ?
> > result = new Array();
> > foreach (row) {
> > emp = new Employee(row.name, row.aaa, row.bbb, ....);
> > result.add(emp);
> > }
> > return emp;
> > }
>
> The two stretches of code don't do the same thing. the first simply
> returns the employee name. The second tries to build an employee
> object.

That's my point, most OO people claim that the query result should be mapped to a "domain objects". This causes code bloat. After looking at your Payroll application, you seem to do like this too.

> The first stage of decoupling your SQL statement might be:
>
> RowSet findAllEmployeesOlderThanLivingIn(age, city) {
> return execute_sql(
> "select name from employees where today()-date_of_birth > ? and
> city = ?", age, city);
> }

Many OO evangelists (including yourself) would argue against this solution, because the caller of findAllEmployeesOlderThanLivingIn need to know about database column names.

> I think you'd need this code anyway if the above query was issued from
> more than one place. I doubt you'd want to duplicate the string.

If I need to call exactly the same query from more than one place in the application, obviously I would create a function (like I would do anytime the same fragment of code is written multiple times). But the probability of this query being called from more than one point is rather low, don't you think so? The query is tailored for the problem in hand, and you should only make the same implementation once. Otherwise you have duplication on a higher level.

If you want select statements that are likely to be called from multiple places, you probably need to select more columns and join more tables than the problem in hand needs.

> > Which is most bloated?
>
> That depends on how many places the query is issued from. If many
> different modules make that query then the first could be more bloated
> than the second.

Did I say that I would not use a function if exactly the same SQL statements was called from multiple places? It also depends on how complex the SQL statement is. Calling
select name from employee where id=?
is not be more bloated than
employee = dao.findEmployeeById(id)

> > A very typical scenario is the save method in a DAO. In OO, one
> > normally write one save method with one update statement, updating all
> > columns regardless they should be updated or not.
>
> If that's a problem, then don't do that. Update only those columns
> that have changed. One would use a special method in the
> EmployeeGateway for that purpose.

This will give me a lot of update statements that are only used once.

> >>> The fact that the
> >>> result from the SQL query has to be mapped to a "domain object" also
> >>> introduces numerous problems.
> >>
> >> It can also solve some. Indeed, there is no rule that the result of a
> >> query must be mapped to an domain object. We do it when it makes sense.
> >
> > No rule? Anyway this is all what Hibernate, EJB, JDO is about. Mapping
> > SQL results to domain objects.
>
> Yes, but there is no rule that says that there is a 1:1 correspondence
> between tables and domain objects.

Neither Hibernate, EJB nor JDO demands you to have a 1:1 correspondence between tables and domain objects, but they still map to domain objects.

> One can create small domain objects
> for limited updates, and largers ones for more comprehsensive udates.
> Indeed, you can have a domain object for each different kind of
> query/update transaction if you wish.

Yes, there are many workarounds.

Fredrik Bertilsson
http://frebe.php0h.com Received on Mon Jul 03 2006 - 10:01:03 CEST