[Tutor] Exceptions while dealing with MySQL

[Alan Gauld]

"John Clark" <clajo04 at mac.com> wrote

>>> MySQL database. The logic of the program is unavoidably coupled 
>>> with
>>> query results I get from various tables.
>>That's pretty unusual, it normally indicates a non OO design.
>
> Database persistance has always been a frustration of mine in OO 
> design -

Pesistence on its own is not too dificult, what is more difficult
is trying to use a relational database to actually manage objects.
For persistence you only need to fetch data from the database
when you create/initialise the instance and save it back when
the object is deleted. You rely on using the in-memory copy
while it is in memory.

For searches etc you can use  meta classes and class methods
to do the intelligent management of the instances so that access
is either steered to an existing instance or a new instance is
created. Searches and Lists can be done in class methods.

So if you are happy that you will never instantiate all objects,
or can afford to load it all in RAM, its not too hard. The problems
start when you have big data sets or complex, cross object
queries - but you need to ask why? Usually if your objects
are right the queries will be relatively simple since the objects
reflect your usage.

> does anyone have pointers to patterns that can be used to decouple 
> the
> database connection/transaction/cursor/cache management with the 
> logic of
> the objects in the domain of the solution?

I usually use a meta-class for that and manage these things
at a class level. If you have complex cross-instance queries going
on, first ask why?, if that doesn't help then consider building 
relationship
objects to manage the relations including the data access.

Recall from Information Engineering that interseection tables
(ie many to many relatinships) are actually data representations
of management processes. In OOP we normally build managemernt
processes as objects (controllers on Jacobsen speak) or as class
methods (possibly paired across the related objects) whichever
is most appropriate.

Or use an existing ORM such as SQLObject or Alchemy.

Or better still an OODB if you have control of that decision.

> out there called "Database Access Patterns", can anyone provide a
> recommendation or a critique of the book?  Are there other (better)

Sorry, hadn't heard of it till now.

HTH,

-- 
Alan Gauld
Author of the Learn to Program web site
http://www.freenetpages.co.uk/hp/alan.gauld