Re: Efficiency vs. code bloat for SELECT wrappers

--- Colin Wetherbee <cww@denterprises.org> wrote:

> Sam Mason wrote:
> > On Sun, Dec 16, 2007 at 06:31:56PM -0500, Colin
> Wetherbee wrote:
> >> If I write one Perl sub for each operation on the
> table (e.g. one that
> >> gets the username and password hash, another that
> gets the last name and
> >> first name, etc.), there will be a whole lot of
> subs, each of which
> >> performs one very specific task.
> >>
Right. First rule of software engineering is keep
functions as small as possible, focussed on one thing
wherever practicable.  It doesn't matter if the
language is Perl or C++ or Java, or a stored procedure
in an RDBMS.  One can always create additional driver
functions that use the elemental simple functions to
do more complex tasks (bearing in mind the
complexities that will inevitably arise in multiple
user situations).

> >> If I write one larger Perl sub that grabs the
> whole row, and then I deal
> >> with the contents of the row in Perl, ignoring
> columns as I please, it
> >> will require fewer subs and, in turn, imply
> cleaner code.
> >
Define "cleaner code."  The more data, and the more
complex that data, the more code you have to write,
regardless of whether that is in one function or
several.  Either way, done badly, can be a problem for
both maintenance and performance.

> > It sounds as though you're just treating the
> database as a relatively
> > dumb datastore.  They can be used as this, and
> Toms comments are as
> > always good, but relational databases come into
> their own when you're
> > writing more complex queries.  When I'm writing my
> code I tend to put
> > the SQL statements directly in with the rest of
> the code, abstracting
> > away from the database tends to make things more
> complicated than they
> > need to be.
>
> Because I know Perl a whole lot better than SQL,
> PostgreSQL, and even
> the Perl DBI, I'm always inclined to wrap the
> database stuff in a nice
> little package and forget about it.  This method has
> worked well for me
> in the past, but the project I'm starting is much
> bigger in terms of
> database use than anything else I've written.
>
I routinely keep my SQL code distinct from my Perl,
java or C++ code.  When a client program needs to do
something with the database, then either a child
process executes a script I have written, if the
client program doesn't need to do anything with data
drawn from the database, or I have all the SQL code in
one or more stored procedures, and use the appropriate
client interface to invoke the stored procedure(s).
Whether the SQL is in a specific script or in a stored
procedure, my SQL code is kept distinct from the
client code, regardles of the language I have used for
that.  I find this even MORE useful as my projects get
bigger.

> You say you write SQL directly in your application
> code, rather than
> writing task-oriented wrappers.  I like that idea,
> but I can see that
> getting really messy.  What is your opinion on how
> it affects code
> maintenance and things like that?
>
From what I have seen, even in small, almost trivial,
client programs, I find this gets messy real quick.
I, therefore, hate the idea of mixing SQL in with
client code (it is all, in my view, application code).
 I like the use of middleware objects since, if well
designed, they can make developing the code required
all the simpler.  It is only when badly done that an
abstraction leads to complicated code that is a
nightmare to maintain; worse if it is inadequately
documented.  The whole purpose of abstraction, whether
one is using objected oriented development of
middleware, or generic programming, or structured
programming, or functional programming, is to analyze
a complex problem into more manageable parts.  The
parts themselves become easier to code (witness java
beans of various kinds, such as backing beans - my
beans get their data either from the database or from
the user interface - in either case, they make
interaction between a web based interface and the
database back end MUCH simpler to code), and the
relationships among the parts are easier to
understand.  Each kind of abstraction has its place.
It is up to the analyst or architect to figure out how
many layers and what abstractions are appropriate for
a given project.

HTH

Ted