|
Posted by sebastian on July 2, 2005, 1:23 am
Please log in for more thread options
One of the problems I frequently face in development for
database-driven applications is the inability to efficiently reuse data
retrieval logic. That is, how do I reuse a SQL statement, but slightly
modify a single part of it? How do I efficiently abstract the database
as much as possible?
I've tried a number of "solutions" to these problems, but my methods
usually fell short. With DBIx::Abstract, DBIx::SQLEngine, or
SQL::Interpolate you're forcses to use vague data structures. At times
SQL can be quite complex, and remembering tiny little differences in
data structures used in those modules can be a pain. In some of these
modules you might also have to write a bit of SQL. The problem with
that is, as soon as you use a string constant, you lose flexibility and
most likely the ability to modify your query at a later time.
Class::DBI does a nice job hiding the database layer, but at some point
you have to start writing SQL because it doesn't support mechanisms to
efficiently hide and reuse query logic.
There are some philosphies which assert that SQL should not be mixed
with Perl (think phrasebooks), but this can't hold up for long in a
system that generates dynamic queries -- for example, dynamic reports
or searches. Other philosophies assert that programmers should never
write SQL and this should be left to a DBA -- this might be plausible,
but potentially very expensive or encounter a number of other problems.
I'm not here to argue any of this -- the problem at hand relates to a
programmer writing SQL, which is how many systems are written.
I have come up with a solution of my own that sounds good, but I
haven't tested it in a realworld application yet. What I'd really like
to know is if anyone else encounters similar problems and is looking
for a clean, simple, and lightweight solution to the above problems, or
have you already found a solution?
As for my solution, you can find a bit about it at
http://unf.be/~sili/projects/sqlbuilder/sql.html. This is a very rough
draft and I expect to rewrite and refine at some point, its only
purpose is to relay the concept. I am suggesting objects for everything
that is SQL. If this is accomplished, we can truly stop writing SQL and
start writing Perl. Objects can provide interfaces to the functionality
specified by SQL. It opens up possibilities because you no longer rely
on vague data structures, inflexible query strings, or the inability to
elagantly write SQL. At
http://unf.be/~sili/projects/sqlbuilder/SQL-Builder/lib/SQL/Builder/
you can view the work I've done so far. I don't have any documentation
yet, but all of the objects are pretty simple and should be easy to
grasp.
What do you think of such a solution?
- sebastian
|
|
Posted by Terrence Brannon on September 4, 2005, 4:06 pm
Please log in for more thread options
> There are some philosphies which assert that SQL should not be mixed
> with Perl (think phrasebooks), but this can't hold up for long in a
> system that generates dynamic queries -- for example, dynamic reports
> or searches.
Think hard about this assertion. It may be more time-consuming to
build such a system and it will be more time-consuming should anything
change about the data model. But it is not impossible and perhaps the
speed boost from mapping the entire front-end to static queries is
worth the time-consumption.
I know of one major shopping comparison website that does this.
--
Carter's Compass: I know I'm on the right track when,
by deleting something, I'm adding functionality.
|
| Similar Threads | Posted | | use of xml in perl application to describe command line logic | December 20, 2005, 11:03 am |
| replacing nonprintable characters in a file | June 3, 2005, 4:21 pm |
| replacing characters with their ASCII codes | August 20, 2005, 8:50 pm |
| Weird Module Installation Problem on Solaris 10 | November 4, 2005, 8:36 am |
| ISO: deep voodoo debugging tools for tracking down seriously weird module behavior. | November 9, 2007, 1:28 pm |
| Class::DBI::Schema2Code V 1.01 | July 29, 2004, 7:14 am |
| Class::Tree V 1.24 | July 29, 2004, 7:15 am |
| [RFC] Class::DataStore | May 13, 2005, 2:45 pm |
| RFC: new module Class::MakeIntrospecMethods | October 18, 2004, 4:30 pm |
| Perl Class::Struct | October 13, 2005, 3:18 am |
|
XML Sitemap