> > Generally I try to minimise the layers/tiers/abstraction between
> > the front-end and the database - for me OO/SQL abstraction is something
> > akin to 'GOTO considered harmful'.
>
HI Mark,
I'm not sure about a monolithic SQL factory module like the one
you describe. Generally, each SQL statement is unique so I've never been
able to find re-use benefits here. Also passing parameters to SQL Factory
methods involves another change of scope/layer which I think is not
necessary.
In our system, a programmer maintaining the system will look to
the object first and find the SQL bang in the middle - which I think has
better re-use/maintenance implications than hidden away in a back closet
that's packed full of stuff (i.e., all the SQL).
One way I minimise the layers between DB and the front-end is to
make sure all fields share the same name as in the database - someone has
already mentioned this technique.
This follows the design tenet - a unit of data should have only
one name throughout the entire system.
Perl has a great feature: 'slippery typing' (as opposed to Java's
strong). This enables me to pass a database row hash -> to an object
constructor -> to a controller -> to a view.
In my case the view is typically a HTML template holding tokens of
the form: {{database-field-name}}.
For example, when a search occurs on Turbo10 the following happens:
* Engine Object - contains SQL - returns a DBI row hash
* Hit Object - is constructed with this DBI row hash
* BrowserMessenger Object - formats Hit in Javascript for tranmission to
browser using a Template
* Template Object - uses the same field names from the DBI row
hash to replace tokens
In this way, I try to minimise the interfaces going from the
database to the browser - and the SQL stays inside the objects that use
it.
Does anybody else want to confess to something similar? (-;
Nigel
> I think there's room for middle ground here between mapping OO directly
> to SQL and having SQL in heredocs in your code. What we tend to use is a
> object that works almost as a SQL factory. We never have SQL directly in
> our code, but rather all this is placed inside a seperate module.that
> knows a few things about itself - e,g, what database and table we're
> using for this run. This can be then used to spit out SQL or run the
> query and return the results.
>
> The key aspect is that anytime you feel you need to write some SQL you
> should feel free to add more SQL to this module - not limit yourself to
> waht's already avalible.
>
> The advantage of this is that we get better reuse in out of our SQL when
> we need the same function called from many places, and we can reuse the
> same SQL on similar tables/databases for different runs. Another
> advantage is that should we ever want to change the database all our SQL
> is in a few modules and we can make sure that we change all our SQL.
>
> Mark.
>
>
--
Nigel Hamilton
Turbo10 Metasearch Engine
email: [EMAIL PROTECTED]
tel: +44 (0) 207 987 5460
fax: +44 (0) 207 987 5468
________________________________________________________________________________
http://turbo10.com Search Deeper. Browse Faster.
