Page 4 of 4
Posted: Thu Dec 28, 2006 2:27 am
by Christopher
johno wrote:Ok, guys. First of all, you don't need to choose one approach (Criteria/OQL/SQL) and go for it. You can have them all if you want and choose the most appropriate one for each case you will encounter. At least I would do it so and that is what I wanted to say in first place.
Thanks for the links. I think the main reason here in this code (which has the goal of simplicity) and in general that people stay away from OQL type solutions and full blown O/RM systems is the diminishing returns you get as you add features. You end up jumping through too many hoops that exist simply to support very complex constructs.
However since SQL statement generation is an obvious part of any of these systems, I would ask if anyone has a simple/elegant implementation to generate a reasonable subset of SELECT syntax. I believe UPDATE, INSERT and DELETE are very finite in their syntax -- it is SELECT that has the variety. So what to support?
- DISTINCT
- LIKE
- AND & OR
- IN
- BETWEEN
- ALIASES
- JOIN
- UNION
- ORDER BY
- GROUP BY
Posted: Thu Dec 28, 2006 3:23 am
by johno
I would go for simple WHERE with AND and equal condition in first place. Most of selects need them. ORDER BY and LIMIT come next. Then you may need some composite AND & OR conditions (definitely Specification and Composite patterns for this one). LIKE, IN, BETWEEN come next if you need them, I personally don't use them so much. JOINS are tricky and self referencing JOINS need ALIASes which are even trickier. I would leave them out except some trivial cases of inheritance mapping. Just if you need that.
I've never used DISTINCT nor UNION except in very rare cases, so leave them out. GROUP BY will cause you much trouble so leave it out too.
In first go I would take AND, OR (composites maybe), ORDER BY, LIMIT and equal conditions. Maybe IN, BETWEEN, <>, LIKE as sugar after that.
Oh yeah and watch out for NULL values they can be really odd.
Posted: Thu Dec 28, 2006 1:24 pm
by Christopher
I guess AND, OR, LIKE, IN, BETWEEN, = and !=(<>?) are all WHERE condition stuff. All but OR are pretty easy -- OR implies some kind of grouping or ordering of the conditions which sounds messy.
I am not sure how ORDER BY actually fits in because you are creating/fetching list of object which could be sorted if necessary. And LIMIT has variety once you get beyond Postgres and MySQL.
JOINS the code already does (though not very well). And I think the problem of ALIASs goes away if every field has an alias but it defaults to the field name if not provided. That way the system can always just deal with the alias.
Posted: Mon Jan 01, 2007 6:39 pm
by wei
is the goal still a Data Mapper or some sort of Active Record/Table Gateway/Row Gateway ? The idea of the data mapper is simple, just map relational data to Objects and vice versa (nothing about tables), the problem lies with how to define the mapping.
Posted: Mon Jan 01, 2007 10:02 pm
by Christopher
Yes, the goal is a Data Mapper. It is a unfamiliar concept to many, so I thought some code that is semi-simple and PHP4/PHP5 would give people something to experiment with. The Data Mapper is different from those other patterns mentioned because with those you directly load and save the data. With the Data Mapper you get objects that have been loaded with data and the Data Mapper tracks them to see if any changes occur to them and will write changed data back to the database as necessary.
As far as defining the mapping, I have created a class to do that so there can be a defined interface that we can refactor behind -- i am sure the design can be improved to better abstract the mapping.
At this point I think the next thing to add to the code I have posted (which only reads now) is to implement Unit of Work. I thought I would simple maintain a reference to all objects created and keep a copy of the last known value for each mapped property in each mapping object. Then it should be pretty easy to check for any changes and build UPDATE/INSERT statements.
Finally, this code will limit features to keep the code smaller. The idea is to make this the "manual transmission" version and then allow it to be wrapped in other classes to add automation. I showed an example of loading mappings using XML to demonstrate this idea.