This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: modperl@apache.org
From: Joe Breeden <jbreeden@ena.com>
Subject: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 10:12:45 -0500
All,
In his closing comments about UBB Kyle Dawkins made a statement that got me
wondering. He said "there's SQL embedded all throughout the Perl everywhere
(who does this?! oh my god, are they on crack?)". This comment got me
wondering about alternatives to embedding SQL in to the code of a program.
Alternatives I see are to use stored procedures which would limit one to
using a certain DB server (or to be proficient in many servers and write
stored procedures for all server flavors which would mean one is a very busy
Perl and SQL guru) or possibly storing the embedded SQL in some sort of
external file structure accessible via storable, XML::Simple or some other
means.
It would be interesting to know how other people have solved that problem.
Currently, we are essentially using embedded SQL in our apps.
===
To: Joe Breeden <jbreeden@ena.com>
From: Barry Hoggard <barry@hoggard.org>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 11:29:06 -0400
I think a lot of people's approach, including mine, is to have OO Perl
modules for all database access. In my code (I use Mason), a web page
only gets its data through calls like this:
my $obj = NAIC::User->(DBH=>$dbh, EMAIL=>'barry@hoggard.org');
$obj->load;
my $groups_list = $obj->groups();
That way any needed SQL changes, or even ports to a new database,
don't have to be done everywhere in my code.
===
To: jbreeden@ena.com, modperl@apache.org
From: mgraham@circadence.ca
Subject: Not embedding SQL in perl (was RE: [OT] Inspired by
closing comme
Date: Wed, 1 Aug 2001 11:31:57 -0400
Joe Breeden [mailto:jbreeden@ena.com] wrote:
...
> wondering about alternatives to embedding SQL in to the code
> of a program.
...
> It would be interesting to know how other people have solved
> that problem.
One approach is to use something like Ima::DBI, which I'm currently toying
with. With Ima::DBI, you still embed your SQL in your perl code, but at
least you put all of your SQL into a single module somewhere and you do so
in a very structured way.
To access the database from the rest of your program, you call methods of
your database query object. This is a lot cleaner than whipping up a query
string every time you want to hit the database. It's also a lot more
flexible. You could, for instance, create different database classes for
different database backends, and still keep the programming interface the
same.
Of course you could do all this without Ima::DBI; roll up your own custom
database wrapper classes. But Ima::DBI also handles some mod_perl DBI
issues such as guaranteeing one DBI statement handle per process.
===
To: modperl@apache.org
From: Robert Landrum <rlandrum@capitoladvantage.com>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 11:33:00 -0400
>All,
>
>In his closing comments about UBB Kyle Dawkins made a statement that got me
>wondering. He said "there's SQL embedded all throughout the Perl everywhere
>(who does this?! oh my god, are they on crack?)". This comment got me
>wondering about alternatives to embedding SQL in to the code of a program.
>Alternatives I see are to use stored procedures which would limit one to
>using a certain DB server (or to be proficient in many servers and write
>stored procedures for all server flavors which would mean one is a very busy
>Perl and SQL guru) or possibly storing the embedded SQL in some sort of
>external file structure accessible via storable, XML::Simple or some other
>means.
I, as a crackhead, do embed my SQL into my modules. I've never liked
the idea of a central SQL library... Too many dependencies. If I
change one query in the library, I could end up breaking lots of
modules using that query.
I have, on occasion placed all the SQL into a %SQL global (since it's
static). Then it get's shared by all the apache processes when the
module loads.
===
To: "Barry Hoggard" <barry@hoggard.org>, "Joe Breeden"
<jbreeden@ena.com>
From: "Perrin Harkins" <perrin@elem.com>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 11:38:50 -0400
> I think a lot of people's approach, including mine, is to have OO Perl
> modules for all database access. In my code (I use Mason), a web page
> only gets its data through calls like this:
>
> my $obj = NAIC::User->(DBH=>$dbh, EMAIL=>'barry@hoggard.org');
> $obj->load;
> my $groups_list = $obj->groups();
>
> That way any needed SQL changes, or even ports to a new database,
> don't have to be done everywhere in my code.
That's what I do too. I suppose this could still be called "embedded SQL"
though.
You could put your SQL in a separate file, but I don't like that approach
because it doesn't seem like you would be changing SQL without changing the
other code very often. Having your SQL right next to where it's being used
is convenient, and a HERE doc makes it easy to read.
===
To: Joe Breeden <jbreeden@ena.com>
From: Chris Winters <chris@cwinters.com>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 10:52:36 -0400
* Joe Breeden (jbreeden@ena.com) [010801 10:25]:
> All,
>
> In his closing comments about UBB Kyle Dawkins made a statement that got me
> wondering. He said "there's SQL embedded all throughout the Perl everywhere
> (who does this?! oh my god, are they on crack?)". This comment got me
> wondering about alternatives to embedding SQL in to the code of a program.
> Alternatives I see are to use stored procedures which would limit one to
> using a certain DB server (or to be proficient in many servers and write
> stored procedures for all server flavors which would mean one is a very busy
> Perl and SQL guru) or possibly storing the embedded SQL in some sort of
> external file structure accessible via storable, XML::Simple or some other
> means.
>
> It would be interesting to know how other people have solved that problem.
> Currently, we are essentially using embedded SQL in our apps.
As others have mentioned, one way would be to wrap your records in
objects and have access, queries, etc. be centralized
there. <plug>SPOPS (Simple Perl Object Persistence with Security) does
this for you and gives you object linking and high-level database
independence for free. It's on CPAN.</plug>
===
To: Barry Hoggard <barry@hoggard.org>
From: Jay Jacobs <jay@lach.net>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 10:46:20 -0500 (CDT)
I'd second the original question, I've always embedded the SQL (what's the
"S" for?) in the code, isn't that the point of the wonderful DBD::*
packages? As far as modularizing database calls, there are a couple
reasons I've had problems with that. I found the methods being rewritten
to handle about as many options as sql itself. (what if I want to sort
differently? what if I need a slightly different statement?). My solution
is to embed SQL most of the time, modularize basic calls (get_user,
get_group type stuff).
In addition, I'd like to rebut the original statement:
"not to mention the HTML embedded all throughout the perl
(are they on glue?)"
What's the alternative there? Embed perl in the HTML?
===
To: modperl@apache.org
From: Mike Miller <mmiller@crusoe.net>
Subject: Re[2]: [OT] Inspired by closing comments from the
UBB thread.
Date: Wed, 1 Aug 2001 11:55:10 -0400
On Wednesday, August 01, 2001, Perrin Harkins wrote the
following about "[OT] Inspired by closing comments from the UBB thread."
ph> Having your SQL right next to where it's being used is convenient,
ph> and a HERE doc makes it easy to read.
Agreed. IMHO, it also makes it easier to maintain months/years down
the road, when you have forgotten what the sql (or the entire program)
was supposed to do anyway, and have turned the module over to a junior
staff member who has never seen it before, etc, etc.
But it seems to me its a bit of a style thing, with pro's and con's on each
side.
===
To: "Jay Jacobs" <jay@lach.net>
From: "Perrin Harkins" <perrin@elem.com>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 11:57:59 -0400
> "not to mention the HTML embedded all throughout the perl (are they on
> glue?)"
>
> What's the alternative there? Embed perl in the HTML?
You could do that (Text::Template), or you could use a tool like Template
Toolkit or HTML::Template. See
http://perl.apache.org/features/tmpl-cmp.html for a description of the
available options.
===
To: Perrin Harkins <perrin@elem.com>
From: Jay Jacobs <jay@lach.net>
Subject: Re: [OT] Inspired by closing comments from the UBB thread.
Date: Wed, 1 Aug 2001 11:05:13 -0500 (CDT)
On Wed, 1 Aug 2001, Perrin Harkins wrote:
> > "not to mention the HTML embedded all throughout the perl (are they on
> > glue?)"
> >
> > What's the alternative there? Embed perl in the HTML?
>
> You could do that (Text::Template), or you could use a tool like Template
> Toolkit or HTML::Template. See
> http://perl.apache.org/features/tmpl-cmp.html for a description of the
> available options.
I wasn't clear enough... My point was more "six one way, half dozen the
other". For a public package, keeping dependancies down to a minimum is a
bonus, as well as keeping performance up by not having to pre-process html
looking for perl code. It can come down to a choice between
maintainability and better performance (to whatever degree). I don't see
any glue-sniffing symptoms from choosing embedded html in perl over
embedded perl in html.
===
To: Jay Jacobs <jay@lach.net>
From: Alex Porras <aporras@ena.com>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 01 Aug 2001 11:17:41 -0500
Jay Jacobs wrote:
>
> I don't see any glue-sniffing symptoms from choosing
> embedded html in perl over embedded perl in html.
>
Unless, of course, you're the graphic artist and you've been
tasked with changing the look and feel of the application
using embedded perl (which you, as the graphics person,
probably don't know anything about), while the perl
developer works on the perl portions of the code, then you
might be sniffing some glue. This the motivation for some
(if not most) of the templating solutions Perrin mentioned.
===
To: <modperl@apache.org>
From: "Kyle Dawkins" <kyle@centralparksoftware.com>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 12:27:02 -0400
Guys guys guys
Mixing HTML with Perl with SQL is bad and evil on every single possible
level. For those who don't know how to split apart your perl from your HTML
I suggest you read some of Perrin's recent posts. There are so many ways to
do it, I won't even bother with talking about them here.
As for SQL, I just wish people would expand their horizons a little and
start doing a bit of reading. There are so many different ways to avoid
embedding SQL in application code and I sincerely wish programmers would
THINK before just coding... it's what differentiates scripters from
engineers and I suggest everyone who embeds SQL in their perl for anything
other than quick-and-dirty hacks start considering other options for the
good of the programming community AND THE SANITY OF WHOMEVER HAS TO MAINTAIN
OR ALTER YOUR CODE.
If you wish to see one enlightened approach, please read this:
http://developer.apple.com/techpubs/webobjects/DiscoveringWO/EOFArchitecture
/index.html
Fine, it's Java (yuk). Fine, it's Apple (yuk). But it used to be *NeXT*
and it used to be *Obj-C*, both very very fine things indeed.
One of the projects I am working on right now, for example, involves an
awful lot of DB access. There is not a single line of SQL in our
application code. It's 100% mod_perl. This is a gooood thing.
To be fair, if you want to talk to DB at all, you will need SQL somewhere;
what I mean by "embedding SQL in perl" is embedding it *application* logic.
It has no purpose there and you might as well be using some dumbass
technology like CF or PHP because your code will be just as maintainable.
I just implore readers of this list to start thinking more as engineers and
less as script kiddies. We all love mod_perl and its power and we want it
to succeed. We'll only get somewhere with it if we actually make the effort
to write better code. Mixing SQL and perl is not better code.
===
To: "Kyle Dawkins" <kyle@centralparksoftware.com>,
<modperl@apache.org>
From: "Perrin Harkins" <perrin@elem.com>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 12:50:07 -0400
> As for SQL, I just wish people would expand their horizons a little and
> start doing a bit of reading. There are so many different ways to avoid
> embedding SQL in application code and I sincerely wish programmers would
> THINK before just coding... it's what differentiates scripters from
> engineers and I suggest everyone who embeds SQL in their perl for anything
> other than quick-and-dirty hacks start considering other options for the
> good of the programming community AND THE SANITY OF WHOMEVER HAS TO
MAINTAIN
> OR ALTER YOUR CODE.
>
> If you wish to see one enlightened approach, please read this:
>
>
http://developer.apple.com/techpubs/webobjects/DiscoveringWO/EOFArchitecture
> /index.html
I appreciate your kind words about my templating posts, but I don't agree
that an object-relational mapper is always the right answer for database
integration. Using objects to model your data, and having the objects
manage their own persistence through SQL calls is faster and easier for many
things, and it allows you to do things that can't be done with an O/R
mapper, like advanced SQL tuning (optimizer hints), aggregation of commonly
fetched data into one query, etc. You still get encapsulation of the SQL
behind the object interface, and your high-level logic doesn't need to use
any SQL directly.
It would really be nice if someone could write an overview of the O/R
mapping tools for Perl. I know Dave Rolsky was working on one, but it's a
big job and he's busy with Mason.
===
To: Kyle Dawkins <kyle@centralparksoftware.com>
From: Jay Jacobs <jay@lach.net>
Subject: Re: [VERY OT] Inspired by closing comments from the
UBB thread.
Date: Wed, 1 Aug 2001 12:01:00 -0500 (CDT)
On Wed, 1 Aug 2001, Kyle Dawkins wrote:
> Mixing HTML with Perl with SQL is bad and evil on every single possible
> level.
This bugged me... "TMTOWTDI" applies on so many levels.
The "right" way to do something is not always the technically "best" way
to do something. If you work in a large corporate enviroment with many
hands in the development pot, then hey, I agree, and there should
probably be a corporate document stating the guidelines and restrictions
of developement.
If however you work in a two person company where you have barely enough
time to go to the bathroom let alone think about creating your own
database abstraction layer for a custom application and "maintaining" code
means changing a link once a month. Then by all means embed away, and
take the quick development path over performance or maintainability.
On the other hand, if you are completely broke and work on a non-profit
project and the only system you have is a P200 with 64M of Memory, then
you may want to think about avoiding templating systems, and doing nothing
but a single module with embedded SQL with Perl and HTML.
There is always more then one way to do it, and there's usually more then
one right way to do it. Let's keep that in mind.
===
To: <modperl@apache.org>
From: "Kyle Dawkins" <kyle@centralparksoftware.com>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 13:02:07 -0400
All (and Perrin)
> > If you wish to see one enlightened approach, please read this:
> >
>
http://developer.apple.com/techpubs/webobjects/DiscoveringWO/EOFArchitecture
> > /index.html
as I said... *ONE* enlightened approach :-)
I think you'd find that EOF (the persistence framework in that example) does
exactly what you speak of below. Nevertheless, I absolutely agree that the
implementation is very much dependent on circumstances. I just wanted to
give an example of an object-layer that doesn't require any SQL... and like
a said in my previous post, there are many ways to do this. Our current
persistence layer uses a combination of an O/R mapper and objects that
manage their own persistence.
> I appreciate your kind words about my templating posts, but I don't agree
> that an object-relational mapper is always the right answer for database
> integration. Using objects to model your data, and having the objects
> manage their own persistence through SQL calls is faster and easier for
many
> things, and it allows you to do things that can't be done with an O/R
> mapper, like advanced SQL tuning (optimizer hints), aggregation of
commonly
> fetched data into one query, etc. You still get encapsulation of the SQL
> behind the object interface, and your high-level logic doesn't need to use
> any SQL directly.
Concur, see above.
> It would really be nice if someone could write an overview of the O/R
> mapping tools for Perl. I know Dave Rolsky was working on one, but it's a
> big job and he's busy with Mason.
I've taken a look at many of them (Tangram? a few others) and haven't been
impressed with any of them. I think part of the problem is that they're all
being developed in a bit of a vacuum. But let's capitalise on the interest
that this thread has generated to start a push for something that we can all
use. I think even the dudes who embed their SQL in perl could be made to
realise the benefits if we all started using a common framework. Thoughts?
===
To: <modperl@apache.org>
From: Ray Zimmerman <rz10@cornell.edu>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 13:25:33 -0400
At 12:50 PM -0400 8/1/01, Perrin Harkins wrote:
>It would really be nice if someone could write an overview of the O/R
>mapping tools for Perl. I know Dave Rolsky was working on one, but it's a
>big job and he's busy with Mason.
I agree. There was a bit of discussion on this topic on this list
around May 10th of this year. Dave mentioned that you could have a
look at what he'd started writing a long time ago at ...
http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/poop/documents/poop-comparison.pod?rev=1.2&content-type=text/vnd.viewcvs-markup
One of the tools that is not mentioned in Dave's write-up (probably
because it didn't exist then) is SPOPS, mentioned earlier in this
thread.
There is also a related mailing list at ...
http://lists.sourceforge.net/lists/listinfo/poop-group
===
To: "'modperl@apache.org'" <modperl@apache.org>
From: "Homsher, Dave V." <DVHomsher@bemis.com>
Subject: RE: Not embedding SQL in perl
Date: Wed, 1 Aug 2001 13:31:15 -0400
Joe Breeden queried:
>>It would be interesting to know how other people have solved
>>that problem.
>>Currently, we are essentially using embedded SQL in our apps.
I have found that stored procedures + perl module wrapper around the procs.
is a nice, balanced approach.
The procs. give a nice performance boost as they are precompiled into the
server (we use Sybase). I believe that they are more secure, in that you
aren't dynamically generating sql that might be 'hijack-able'. You are
providing a discrete amount of functionality. Placing the stored procedure
execution code in a perl module makes for easy/clean perl access from the
rest of the app. Moving to a new db isn't too terribly difficult in that the
proc names will probably remain as well as the parameters that you pass.
Also, how often do you move to another database in the life of a web app
anyway (at least in our corporate environment)?
===
To: modperl@apache.org
From: Michael Peppler <mpeppler@peppler.org>
Subject: RE: Not embedding SQL in perl
Date: Wed, 1 Aug 2001 10:39:37 -0700
Homsher, Dave V. writes:
> Joe Breeden queried:
>
> >>It would be interesting to know how other people have solved
> >>that problem.
> >>Currently, we are essentially using embedded SQL in our apps.
>
> I have found that stored procedures + perl module wrapper around the procs.
> is a nice, balanced approach.
>
> The procs. give a nice performance boost as they are precompiled into the
> server (we use Sybase).
They are definitely faster, and significantly so.
> I believe that they are more secure, in that you
> aren't dynamically generating sql that might be 'hijack-able'.
Using RPC calls instead of language commands also improves speed, and
solves the "quoting" problem, too.
> Placing the stored procedure
> execution code in a perl module makes for easy/clean perl access from the
> rest of the app.
Absolutely. I've actually created configuration files for logical
database requests (essentially a hash that describes the input and
output of each proc) which lets me use a generic module (about 400
lines) of Sybase::CTlib code for *all* database access.
Works very well, and abstracts the database layer quite nicely.
===
To: <modperl@apache.org>
From: "Kyle Dawkins" <kyle@centralparksoftware.com>
Subject: Re: Not embedding SQL in perl
Date: Wed, 1 Aug 2001 13:40:04 -0400
All
> Joe Breeden queried:
>
> >>It would be interesting to know how other people have solved
> >>that problem.
> >>Currently, we are essentially using embedded SQL in our apps.
>
> I have found that stored procedures + perl module wrapper around the
procs.
> is a nice, balanced approach.
Definitely; stored procedures are hit-and-miss in a lot of environments.
Remember that a large number of people in the mod_perl world can't use 'em
because they (we) use MySQL. If one wanted to emulate this behaviour with
MySQL, you would essentially clone the functionality of your stored
procedures using Perl + DBI inside your persistence layer. That is a
perfectly viable approach too, but a lot less efficient than stored
procedures (many roundtrips versus one).
> The procs. give a nice performance boost as they are precompiled into the
> server (we use Sybase). I believe that they are more secure, in that you
> aren't dynamically generating sql that might be 'hijack-able'. You are
> providing a discrete amount of functionality. Placing the stored procedure
> execution code in a perl module makes for easy/clean perl access from the
> rest of the app. Moving to a new db isn't too terribly difficult in that
the
> proc names will probably remain as well as the parameters that you pass.
> Also, how often do you move to another database in the life of a web app
> anyway (at least in our corporate environment)?
True, although I don't think it's uncommon to want to move from MySQL to
Postgres, for example. I have also seen a lot of places move away from
MySQL up to something like DB2 or Oracle when they get their
"it-all-has-to-be-spent" venture capital infusion.
===
To: "Michael Peppler" <mpeppler@peppler.org>,
<modperl@apache.org>
From: "Perrin Harkins" <perrin@elem.com>
Subject: Re: Not embedding SQL in perl
Date: Wed, 1 Aug 2001 13:49:32 -0400
> > I have found that stored procedures + perl module wrapper around the
procs.
> > is a nice, balanced approach.
> >
> > The procs. give a nice performance boost as they are precompiled into
the
> > server (we use Sybase).
>
> They are definitely faster, and significantly so.
Maybe so for Sybase. In Oracle, your SQL statements get cached anyway, as
long as you're using bind variables instead of just dynamically building the
SQL strings. (They get cached even if you don't use bind variables, but
they'll quickly overflow the cache if you keep changing them with each new
value in the WHERE clause.)
> Using RPC calls instead of language commands also improves speed, and
> solves the "quoting" problem, too.
The same goes for bind variables.
===
To: modperl@apache.org
From: Joe Breeden <jbreeden@ena.com>
Subject: RE: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 12:52:08 -0500
Woooie!?!
I didn't expect the firestorm this post would generate. From what I hear
people are either embedding SQL or writing their own utility module to
essentially do something along the line of:
$s->StartDBI ( DSN => 'somedsn_pointer') ;
eval {
$s->SelectSQL ( NAME => 'sql_select',
TABLE => 'sometable',
FIELDS => ['field1', 'field2', 'field3'],
WHERE => 'field1=?',
VALUES => $some_value_for_field1);
while ( my $return = $s->SQLGetArray( NAME => 'sql_select')) {
#do something $return - maybe complete a template object?
}
};
$s->EndDBI ( DSN => 'somedsn_pointer', QUERIES => 'sql_select', RESULTS =>
$@);
Where the different calls do the things hinted at in their name (i.e.
StartDBI opens the DSN and connects to the database in question, SelectSQL
would prepare the SQL select statement and execute it via DBI). This allows
the us to pass a native Perl structure which is reformatted to work with
DBI. We also get back scalars, arrays, or hashes that are easy to work with.
This is what we do here where I work. I still consider this embedded SQL
because a change to the table or even to the server could cause the program
to break in a lot of places. I think what I had in mind was some way to put
this type of processing into a layer where all the SQL related items are
essentially in a template file somewhere maybe a SQL::Template type thingy.
If this is something that people feel would be a worthwhile endeavor, let me
know and maybe when there's have a little free time in the Fall one could
write a CPAN module that has this functionality.
We had the conversation awhile back about adding redundant and unnecessary
crap to CPAN and I want to make sure something like this would be a good
thing or not.
===
To: modperl@apache.org
From: Michael Peppler <mpeppler@peppler.org>
Subject: Re: Not embedding SQL in perl
Date: Wed, 1 Aug 2001 10:51:45 -0700
Perrin Harkins writes:
> > > I have found that stored procedures + perl module wrapper around the
> procs.
> > > is a nice, balanced approach.
> > >
> > > The procs. give a nice performance boost as they are precompiled into
> the
> > > server (we use Sybase).
> >
> > They are definitely faster, and significantly so.
>
> Maybe so for Sybase. In Oracle, your SQL statements get cached anyway, as
> long as you're using bind variables instead of just dynamically building the
> SQL strings. (They get cached even if you don't use bind variables, but
> they'll quickly overflow the cache if you keep changing them with each new
> value in the WHERE clause.)
Actually I did benchmark this for Sybase, both with stored procs and
with SQL statements with bind variables.
The stored procs are still faster, and make it easier in a non-trivial
organization (where SQL code and perl code may be worked on by
different people) to separate the database logic somewhat, and give
SQL developpers and/or DBAs an easy way to tune SQL requests without
having to touch the application code.
>
> > Using RPC calls instead of language commands also improves speed, and
> > solves the "quoting" problem, too.
>
> The same goes for bind variables.
Agreed.
===
To: Joe Breeden <jbreeden@ena.com>
From: Matt Sergeant <matt@sergeant.org>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: 01 Aug 2001 18:59:59 +0000
On 01 Aug 2001 10:12:45 -0500, Joe Breeden wrote:
> All,
>
> In his closing comments about UBB Kyle Dawkins made a statement that got me
> wondering. He said "there's SQL embedded all throughout the Perl everywhere
> (who does this?! oh my god, are they on crack?)". This comment got me
> wondering about alternatives to embedding SQL in to the code of a program.
> Alternatives I see are to use stored procedures which would limit one to
> using a certain DB server (or to be proficient in many servers and write
> stored procedures for all server flavors which would mean one is a very busy
> Perl and SQL guru) or possibly storing the embedded SQL in some sort of
> external file structure accessible via storable, XML::Simple or some other
> means.
http://axkit.org/docs/presentations/tpc2001/anydbd.axp
===
To: "Alex Porras" <aporras@ena.com>
From: "Rob Bloodgood" <robb@empire2.com>
Subject: RE: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 11:02:50 -0700
> Jay Jacobs wrote:
> >
> > I don't see any glue-sniffing symptoms from choosing
> > embedded html in perl over embedded perl in html.
> >
>
> Unless, of course, you're the graphic artist and you've been tasked
> with changing the look and feel of the application using embedded
> perl (which you, as the graphics person, probably don't know
> anything about), while the perl developer works on the perl portions
> of the code, then you might be sniffing some glue. This the
> motivation for some (if not most) of the templating solutions Perrin
> mentioned.
Hmmm... Mason makes this *possible*, for me:
I tell my guys, make it look ANY way you like. I don't care. I don't WANT
to care. Just leave me ONE <td></td>. Since I have all of my components
called by a single dispatch component, all that td has to have is one line
of markup.
Then I tell them, here's the list of styles I'll be using in my markup. You
have access to the stylesheet, make them look however you want but don't
add/remove/rename any of them.
Using this method, I've been able to extend the SAME CODE on two different
sites w/ radically different themes.
Of course, at this point, some would say XML / XSL! Try AxKiT!
But to be honest, I haven't gone there yet. XML, no matter how pretty the
tools, is still a pain and a bother, IMHO. Dropping a couple of lines of
perl in a (mostly) static HTML table/form/chart is FAR simpler than learning
a new language (for the stylesheets) to implement a new paradigm (XML) that
in spite of its buzzword compliance is still a hit-and-miss crapshoot
against current browsers.
===
To: Rob Bloodgood <robb@empire2.com>
From: clayton cottingham <drfrog@smartt.com>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 01 Aug 2001 11:16:25 -0700
Rob Bloodgood wrote:
>
> > Jay Jacobs wrote:
> > >
> > > I don't see any glue-sniffing symptoms from choosing
> > > embedded html in perl over embedded perl in html.
> > >
> >
> > Unless, of course, you're the graphic artist and you've been tasked
> > with changing the look and feel of the application using embedded
> > perl (which you, as the graphics person, probably don't know
> > anything about), while the perl developer works on the perl portions
> > of the code, then you might be sniffing some glue. This the
> > motivation for some (if not most) of the templating solutions Perrin
> > mentioned.
>
> Hmmm... Mason makes this *possible*, for me:
> I tell my guys, make it look ANY way you like. I don't care. I don't WANT
> to care. Just leave me ONE <td></td>. Since I have all of my components
> called by a single dispatch component, all that td has to have is one line
> of markup.
>
> Then I tell them, here's the list of styles I'll be using in my markup. You
> have access to the stylesheet, make them look however you want but don't
> add/remove/rename any of them.
>
> Using this method, I've been able to extend the SAME CODE on two different
> sites w/ radically different themes.
>
> Of course, at this point, some would say XML / XSL! Try AxKiT!
>
> But to be honest, I haven't gone there yet. XML, no matter how pretty the
> tools, is still a pain and a bother, IMHO. Dropping a couple of lines of
> perl in a (mostly) static HTML table/form/chart is FAR simpler than learning
> a new language (for the stylesheets) to implement a new paradigm (XML) that
> in spite of its buzzword compliance is still a hit-and-miss crapshoot
> against current browsers.
>
i would say using the libxml and libxslt have greatly improved xml .
using matts XML:libxml and XML::LibXSLT have made thing possible
that none of the others have been able to achieve
i really dont know any xml or xslt to any degree other than
beginner-intermeditate
and although i use html::template and html::Page for most of my work i
do
want to make a move to xml
the prob is , as you say, no good tool for designing xslt templates
usuable by designers, not progammers.
of course the whole schema approach is
coming in to, to cause added confusion
:)
===
To: Ray Zimmerman <rz10@cornell.edu>
From: Dave Rolsky <autarch@urth.org>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 13:18:27 -0500 (CDT)
On Wed, 1 Aug 2001, Ray Zimmerman wrote:
> One of the tools that is not mentioned in Dave's write-up (probably
> because it didn't exist then) is SPOPS, mentioned earlier in this
> thread.
No, I just hadn't had a chance to get around to it yet. I really need to
finish that thing someday. Of course, if people want to write up their
favorite system (along the lines of the ones I've already done) I could
just use that and it'd be done much quicker ;)
===
To: Kyle Dawkins <kyle@centralparksoftware.com>
From: Dave Rolsky <autarch@urth.org>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 13:19:58 -0500 (CDT)
On Wed, 1 Aug 2001, Kyle Dawkins wrote:
> I've taken a look at many of them (Tangram? a few others) and haven't been
> impressed with any of them. I think part of the problem is that they're all
> being developed in a bit of a vacuum. But let's capitalise on the interest
> that this thread has generated to start a push for something that we can all
> use. I think even the dudes who embed their SQL in perl could be made to
> realise the benefits if we all started using a common framework. Thoughts?
Well, people are starting to use my tool, Alzabo (alzabo.sourceforge.net)
and I'm getting feedback. More feedback about what people want it always
welcome. FWIW, Alzabo gives you a reasonable amount of control over the
SQL that is generated, if you need it. It doesn't yet allow optimizer
hints but that will change in a future version.
OTOH, if you really _need_ to get into the nitty gritty details of SQL its
hard to imagine that any abstraction layer would ever be satisfactory.
===
To: "Kyle Dawkins" <kyle@centralparksoftware.com>
From: "Rob Bloodgood" <robb@empire2.com>
Subject: RE: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 11:18:07 -0700
> As for SQL, I just wish people would expand their horizons a little
> and start doing a bit of reading. There are so many different ways
> to avoid embedding SQL in application code and I sincerely wish
> programmers would THINK before just coding... it's what
> differentiates scripters from engineers and I suggest everyone who
> embeds SQL in their perl for anything other than quick-and-dirty
> hacks start considering other options for the good of the
> programming community AND THE SANITY OF WHOMEVER HAS TO MAINTAIN OR
> ALTER YOUR CODE.
> I just implore readers of this list to start thinking more as
> engineers and less as script kiddies. We all love mod_perl and its
> power and we want it to succeed. We'll only get somewhere with it
> if we actually make the effort to write better code. Mixing SQL and
> perl is not better code.>
WHY? WHY WHY WHY WHY???? Tell me why it's this horrible, glue-sniffing,
script-kiddie badness to do something in a clear and simple fashion????
Below is a pseudo-code handler. It talks to the database:
use strict;
use vars qw/$dbh/;
sub handler {
my $r = shift;
lookup_info($r);
# ... blah...
return OK;
}
sub lookup_info {
my $r = shift;
# ||= allows an already connected $dbh to skip reconnect
$dbh ||= DBI->connect(My::dbi_connect_string(), My::dbi_pwd_fetch())
or die DBI->errstr;
# WARNING! "amateur" code ahead!!!
my $sql_lookup_password = $dbh->prepare_cached( <<SQL );
SELECT passwrd, pageid
FROM siteinfo si, pages pg
WHERE si.acctid = pg.acctid
AND si.acctid = ?
AND pageno = 0
SQL
($c_pass, $c_pid) =
$dbh->selectrow_array( $sql_lookup_password, undef, $acctid );
return undef unless defined $c_pass and $pass eq $c_pass;
# We've confirmed the password.
return $c_pid if !$pid or $pid eq $c_pid;
# some more logic, maybe even another query
return $pid;
}
Now. Tell me ONE thing that's wrong with this? The statement handle is
clearly named ($sql_lookup_password), the query is either A) really simple
or B) commented w/ SQL comments, C) if I change my schema, the query is
RIGHT THERE in the only place that acually USES it.
OO is an idea for "cleaning up" and "packaging" functionality. Fine. If I
need it that bad, I'll code my handler as an object. But let's not forget
that the underlying mechanism, no matter how fancily layered, is still a
list of FUNCTION CALLS. OO has its place. ABSOLUTELY. In perl I can
create an FTP connection _object_ and tell it what to do, and trust that it
knows how to handle it. But in the REAL WORLD, my script is its own
"object", with its own guts and implementation, and the "interface" is:
MyModule::handler. Apache knows what function to call. I can mess with the
guts and the interface doesn't change.
So what do I gain by adding 6 layers of indirection to something this
simple? OO has its PLACE as a TOOL. It should not be a jail with LOCKED
DOORS and ARMED ESCORT. (and come to think of it, any objects I use aren't
cons :-)
===
To: Kyle Dawkins <kyle@centralparksoftware.com>
From: Jay Jacobs <jay@lach.net>
Subject: Re: [VERY OT] Inspired by closing comments from the
UBB thread.
Date: Wed, 1 Aug 2001 13:18:41 -0500 (CDT)
My apologies for beating this dead horse...
I am just unable to get my point across at all today.
On Wed, 1 Aug 2001, Kyle Dawkins wrote:
> Tom et al.
>
> > This is, in my opinion, circular logic. Perhaps the reason that you
> > barely have enough time to go to the bathroom is that you're
> > writing the code the wrong way. :-)
>
...my point with that scenario was that there is just too
much work to spend the time writing highly maintainable code
that has only the simplest of maintance tasks.
> Just because we use free and/or open source tools to build our code, doesn't
> mean we can write crap. We have an obligation to do our duty to whomever we
> work for, and LEARN and apply that learning to our work.
>
> > > There is always more then one way to do it, and there's usually more
> > > then one right way to do it. Let's keep that in mind.
> >
> > Agreed. However, Perl + HTML + SQL isn't one of the right ways! :-)
>
> Couldn't agree more. Just because TMTOWDI doesn't mean that all of those
> ways are equal. Most ways suck, in fact.
Granted, the world is full of incompetance, but if you spent
your time coding for a perfect world in every situation, you
could still be working on the write-up while the next guy is
collecting the check for a finished project and bidding on
the next project, might not be bad code, might be really
good code, might really suck, who cares, it works, the
customer is happy and both businesses do well, the down side
is some geek may have to maintain it but they'll get to
complain about crappy code and show their rightousness on a
public mailing list.
Don't get me wrong here, I agree with the perfect
code... I'd absolutely love to see a clean solution to
embedded html/perl/sql that has fast performance, fast
development and easy maintainability. I wish that the
technically best way always matched the right way. And us
righteous developers decided how the world was run. But my
misintrepreted point is that there are situations in which
this version of "perfect code" has no place, even if I can't
write them up in an email.
===
To: "Matt Sergeant" <matt@sergeant.org>
From: "Perrin Harkins" <perrin@elem.com>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 14:29:10 -0400
> http://axkit.org/docs/presentations/tpc2001/anydbd.axp
Is this basically a hash of SQL statements, indexed by DBD type? Or is
there something more that I'm missing? (I should have gone to your TPC
talk...)
===
To: modperl@apache.org
From: Joe Breeden <jbreeden@ena.com>
Subject: RE: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 13:46:14 -0500
I have to agree here. Is this just a hash of SQL statements
or is there more to it than that?
===
To: Perrin Harkins <perrin@elem.com>
From: Matt Sergeant <matt@sergeant.org>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: 01 Aug 2001 20:01:54 +0000
On 01 Aug 2001 14:29:10 -0400, Perrin Harkins wrote:
> > http://axkit.org/docs/presentations/tpc2001/anydbd.axp
>
> Is this basically a hash of SQL statements, indexed by DBD type? Or is
> there something more that I'm missing? (I should have gone to your TPC
> talk...)
All AnyDBD does is create a class hierarchy in the namespace of your
choice, based on the type of database you're connecting to. The idea
being that you can create a cross database application that makes use of
all database features (such as optimisations, hints, stored procs) where
appropriate. You can abstract stuff away behind methods, and build up a
nice layer of cross-database methods.
(note I'm not saying this is the best way to do it, but the original
question was what do people use, and this is what I use).
It's a shame you don't have access to the code we wrote (for WebBoard
Unix), as it would be a nice example to look at.
===
To: Kyle Dawkins <kyle@centralparksoftware.com>
From: Henrik Edlund <henrik@edlund.org>
Subject: Re: Not embedding SQL in perl
Date: Wed, 1 Aug 2001 21:34:58 +0200 (CEST)
On Wed, 1 Aug 2001, Kyle Dawkins wrote:
KD> Definitely; sotred procedures are hit-and-miss in a lot of
KD> environments. Remember that a large number of people in the
KD> mod_perl world can't use 'em because they (we) use MySQL. If one
KD> wanted to emulate this behaviour with MySQL, you would essentially
KD> clone the functionality of your stored procedures using Perl + DBI
KD> inside your persistence layer. That is a perfectly viable
KD> approach too, but a lot less efficient than stored procedures
KD> (many roundtrips versus one).
And while we are discussing "not cutting corners", those who still use
MySQL should switch to a real DBMS before they even think of abstracting
the SQL away from their Perl code.
That people still use MySQL really shows how many lusers there are with
computers that try to develop real software. I said _try_.
*sigh*
===
To: modperl@apache.org
From: kyle dawkins <kyle@centralparksoftware.com>
Subject: Re: Not embedding SQL in perl
Date: Wed, 01 Aug 2001 15:56:29 -0400
Henrik Edlund wrote:
>And while we are discussing "not cutting corners", those who still use
>MySQL should switch to a real DBMS before they even think of abstracting
>the SQL away from their Perl code.
>
>That people still use MySQL really shows how many lusers there are with
>computers that try to develop real software. I said _try_.
Not sure if you're aware of it, but that argument is pretty
old. We're onto a much more interesting, new argument
now. :-)
Seriously though, you're right, MySQL is not a "real" RDBMS.
No transactions, no foreign key constraints, no stored
procedures. It is, however, free, and in use in a lot of
places. And interestingly enough, in a way that makes the
current argument even MORE important; writing SQL into your
code (as per the current thread of discussion) will make it
exponentially more difficult for you to move to a "real"
RDBMS as Henrik urges you to. If you abstract DB access
into a middleware layer, you will have a much, much easier
time. By placing SQL into your application code, you are
removing the flexibility of changing your persistence
mechanism at a later date. And believe it or not, that's
not as uncommon as you might think.
I cite the example of wwwthreads here... it's a great BBS,
runs under mod_perl, is fast, and has a DB backend.
However, the source is LITTERED with SQL, and everywhere
there's a line of SQL, the dude has to put an "if"
conditional around it to check if the installation is using
MySQL or something else, because MySQL has numerous features
that are not found elsewhere (last inserted id, REPLACE
command, LIMIT m,n)... so, twice the number of SQL
statements in code that (in my opinion) should not have any
SQL in it at all...
It's all food for thought (I hope).
===
To: kyle dawkins <kyle@centralparksoftware.com>
From: Henrik Edlund <henrik@edlund.org>
Subject: Re: Not embedding SQL in perl
Date: Wed, 1 Aug 2001 22:14:38 +0200 (CEST)
On Wed, 1 Aug 2001, kyle dawkins wrote:
kd> Not sure if you're aware of it, but that argument is pretty old.
kd> We're onto a much more interesting, new argument now. :-)
All old arguments eventually becomes new again, once in a while... :-)
kd> Seriously though, you're right, MySQL is not a "real" RDBMS. No
kd> transactions, no foreign key constraints, no stored procedures.
kd> It is, however, free, and in use in a lot of places. And
kd> interestingly enough, in a way that makes the current argument
kd> even MORE important; writing SQL into your code (as per the
kd> current thread of discussion) will make it exponentially more
kd> difficult for you to move to a "real" RDBMS as Henrik urges you
kd> to. If you abstract DB access into a middleware layer, you will
kd> have a much, much easier time. By placing SQL into your
kd> application code, you are removing the flexibility of changing
kd> your persistence mechanism at a later date. And believe it or
kd> not, that's not as uncommon as you might think.
Or you can make sure you do not use any of those features and write pure
SQL92. I have managed so far to write one SQL statement (no if's) for what
I want to do, and it works with PostgreSQL, Oracle, (those two I use) and
even with MySQL and others. You have to be careful and have a SQL92
definition handy, and it doesn't take much extra time. Then you get easy
portability to other DBMS with DBI/DBD. (And yes, I do seperate code and
content, Perl and HTML, with the excellent Template Toolkit.)
There are times when abstracting your SQL has a use, and times when it is
overkill. If you can't write clean SQL92 (or what you are aiming at)
then you do need to abstract yourself even more than DBI already does. I
am though very anti the use of DBMS-specific SQL.
====
To: kyle@centralparksoftware.com
From: "Jonathon M. Robison" <jon7882@netscape.net>
Subject: Re: Not embedding SQL in perl
Date: Wed, 01 Aug 2001 16:18:19 -0400
I can see your arguement regarding SQL within one's code, but doesn't
your arguement fail to hold up if we assume that the SQL is fully
"compliant"?
In other words, if the makers of WWWThreads had stuck with standard SQL,
rather than using any non-standard features of MySQL like last inserted
ID, wouldn't their code be useable on Oracle, for example (assuming we
changed the correct var to tell DBI we are using Oracle now) ?
Just trying to make sure I understand what all the fuss is about.
Jon R.
===
To: modperl@apache.org
From: kyle dawkins <kyle@centralparksoftware.com>
Subject: Re: Not embedding SQL in perl
Date: Wed, 01 Aug 2001 16:37:27 -0400
Jon
> I can see your arguement regarding SQL within one's code, but doesn't
> your arguement fail to hold up if we assume that the SQL is fully
> "compliant"?
Well, yes and no. I was citing that example as *another* reason to keep
SQL out of your application-level code.
If you do, as Henrik suggests, write pure SQL92, then obviously you
wouldn't need to wrap all your SQL in "ifs" like they did with
wwwthreads... you could just switch out MySQL and switch in Filemaker
Pro if it supported SQL92 and had a DBD module :-). I maintain,
however, that SQL embedded in application logic is evil in all but the
simplest of scripts. Putting it in middleware is mandatory; I don't take
issue with that.
> In other words, if the makers of WWWThreads had stuck with standard
> SQL, rather than using any non-standard features of MySQL like last
> inserted ID, wouldn't their code be useable on Oracle, for example
> (assuming we changed the correct var to tell DBI we are using Oracle
> now) ?
Sure thing.
===
To: modperl@apache.org
From: Nick Tonkin <nick@rlnt.net>
Subject: RE: [OT] Inspired by closing comments from the UBB
thread. (fwd)
Date: Wed, 1 Aug 2001 14:25:55 -0700 (PDT)
Since you asked, my opinion is that what you describe would not be
useful. Primarily for the reason pointed out already by a number of people
-- lack of flexibility. Most, if not all, database servers accept highly
customizable performance params to a query, and most even moderately
evolved applications make use of SQL queries that are significantly
more complex than a single-where-clause select.
At ValueClick we built a wrapper module (DB.pm :) that delivered a $dbh
into the API, handling everything up to that point with minimal
fuss. From that point on, some standard things were collected in a utility
class, but most modules created their own $sth, usually with bind
variables, with SQL statements nicely formatted in the source using a here
doc ... it was highly manageable and functional, and most of all it was
flexible. Not all applications are fast-developing, but my experience is
that it pays to develop as if yours were ... rapid access to tweak the SQL
fetching data into the application is very desirable, IMHO.
The point is not that you can't abstract it all away as you show in your
code below, it's that by the time you have covered all eventualities
(sorts, groups, selects from multiple tables, et al.), your interface is
so complicated you are basically paraphrasing the SQL in some new language
of your invention. And that, if I am not mistaken, is the purpose of SQL
in the first place!
There is such a thing as over-abstraction, IMHO, and having played with
this a lot, I have found that this type of effort would be such.
Hope this helps,
~~~~~~~~~~~
Nick Tonkin
===
To: clayton cottingham <drfrog@smartt.com>,
mgraham@circadence.ca
From: Gunther Birznieks <gunther@extropia.com>
Subject: Re: Not embedding SQL in perl (was RE: [OT]
Inspired by
Date: Thu, 02 Aug 2001 05:31:44 +0800
When you've had your fill of wrestling over mySQL vs PostGres and stored
procs versus inline SQL.... (I know I have long ago)
You guys should definitely read the following:
http://www.ambysoft.com/persistenceLayer.html
One of my current coworkers turned me on to this. I have found it to be one
of the best series of articles related towards what it takes to abstract
database away from your object layer and the various levels at which it
makes sense to do so.
You may find the design a little complex, but Scott pretty explicitly
states that this is what is necessary for a *large* system. You can always
go down a less complex path by choice if you feel your programs aren't
complex enough to need the full Persistence Layer structure he advocates.
Oh another warning is that this is Java. Unfortunately, for better or
worse, I tend to find some of the best articles on OO design abstractions
to be Java ones these days.
===
To: kyle dawkins <kyle@centralparksoftware.com>
From: Henrik Edlund <henrik@edlund.org>
Subject: Re: Not embedding SQL in perl
Date: Wed, 1 Aug 2001 23:39:31 +0200 (CEST)
On Wed, 1 Aug 2001, kyle dawkins wrote:
kd> Well, yes and no. I was citing that example as *another* reason to keep
kd> SQL out of your application-level code.
kd> If you do, as Henrik suggests, write pure SQL92, then obviously you
kd> wouldn't need to wrap all your SQL in "ifs" like they did with
kd> wwwthreads... you could just switch out MySQL and switch in Filemaker
kd> Pro if it supported SQL92 and had a DBD module :-). I maintain,
kd> however, that SQL embedded in application logic is evil in all but the
kd> simplest of scripts. Putting it in middleware is mandatory; I don't take
kd> issue with that.
I am not against removing redudancy and creating function/methods of code
that is used more than once so that you don't do the same SQL query at
several places in your code. But that is good programming practices within
your own classes/modules.
But to abstract everything to a SQL class only moves your SQL there and
probably causes severe limitations when wanting to do something advanced
Maybe if you were writing a data abstraction layer and API for some other
programmers, but if you have a database that you know only your script
will use, writing an extra abstraction seems very overkill.
I could see a use for abstraction if we were going to support several
different query languages, but as long as we only use SQL my belief is
that DBI is abstraction enough to maintain DMBS interoperability. And of
course only use SQL92.
Someone once said that more abstraction levels than four (4) is counter
productive.
I can see both sides in real life. It all comes down to what kind of
application development you are doing. And writing your SQL in your main
Perl code now does not make it impossible in the future to abstract it to
it's own class. But I have seen whole applications go under because they
have been so heavily abstracted that in the end no one is even sure what
happens anymore - and then of course - class/object operations in Perl 5
are not the fastest either.
===
To: <modperl@apache.org>
From: "Jeffrey W. Baker" <jwbaker@acm.org>
Subject: Re: Not embedding SQL in perl (was RE: [OT]
Inspired by closing
Date: Wed, 1 Aug 2001 14:44:14 -0700 (PDT)
On Thu, 2 Aug 2001, Gunther Birznieks wrote:
> When you've had your fill of wrestling over mySQL vs PostGres and stored
> procs versus inline SQL.... (I know I have long ago)
>
> You guys should definitely read the following:
>
> http://www.ambysoft.com/persistenceLayer.html
>
> One of my current coworkers turned me on to this. I have found it to be one
> of the best series of articles related towards what it takes to abstract
> database away from your object layer and the various levels at which it
> makes sense to do so.
>
> You may find the design a little complex, but Scott pretty explicitly
> states that this is what is necessary for a *large* system. You can always
> go down a less complex path by choice if you feel your programs aren't
> complex enough to need the full Persistence Layer structure he advocates.
I've worked with Scott Ambler, and I could record everything Scott Ambler
knows about actually devleloping large systems on the head of a pin, using
a magic marker. That guy is a hopeless academic without the slightest
clue of how to actually make software happen.
Here's the brutal truth about persistance "abstractions" using an RDBMS
backing store. At some point, your DBA is going to come up to you and
tell you that you code is too slow. You need to rewrite some SQL queries
to use a different index, or some sorting hints, or whatever. You will
realize that you need to pass some extra information down through your
abstraction layers to make it all happen. After that happens twice or
thrice, you will slowly come to realize that your abstraction is really no
abstraction at all: every time the schema changes, the top level interface
needs to change as well.
===
To: modperl@apache.org
From: Joe Breeden <jbreeden@ena.com>
Subject: RE: [OT] Inspired by closing comments from the UBB
thread.
Date: Wed, 1 Aug 2001 16:46:20 -0500
Nick,
Thanks for the comments. Actually, we use something like the example code
now and can do select from multiple tables (TABLES => ['table1', 'table2',
'table2 as someAlias']), can do inner and outer joins, order by clauses,
binding values, just about anything we want with straight SQL. Essentially,
our Database.pm delivers $dbh and the modules create their own $sth so what
we do and what you do probably isn't very far apart.
I was shocked at how much response the thread generated so I thought that
maybe a solution was warranted and just want to give something back. I still
think the solution I've outlined is not the best, but it may a good solution
for a lot of people.
Thanks everyone for the comments. I can see from the responses this
something everyone deals with everyday and that I not alone out here
wondering if my solution is the right one or not.
===
To: modperl@apache.org
From: kyle dawkins <kyle@centralparksoftware.com>
Subject: Re: Not embedding SQL in perl (was RE: [OT]
Inspired by closing comments from the UBB thread.)
Date: Wed, 01 Aug 2001 17:57:29 -0400
Yeah!
Go Gunther. This is one of the better articles on persistence... Scott
Ambler has tons of good stuff on his site and I highly recommend it as a
source for finding out about interesting techniques. What he describes
is quite similar to the EOF link I put in one of my earlier posts.
> You guys should definitely read the following:
>
> http://www.ambysoft.com/persistenceLayer.html
>
>
> Oh another warning is that this is Java. Unfortunately, for better or
> worse, I tend to find some of the best articles on OO design
> abstractions to be Java ones these days.
yeah, this is (unfortunately) true too. but it brings a point home:
that it doesn't matter what language you use. doesn't matter if it's
Java+JDBC or Perl+DBI. In the 10,000 foot view, it's the same shit,
different pile. you still have to solve all the same problems using a
different coloured (and perhaps slightly heavier) toolbox.
===
To: "Jeffrey W. Baker" <jwbaker@acm.org>,
<modperl@apache.org>
From: Gunther Birznieks <gunther@extropia.com>
Subject: Re: Not embedding SQL in perl (was RE: [OT]
Inspired by
Date: Thu, 02 Aug 2001 06:30:01 +0800
At 02:44 PM 8/1/2001 -0700, Jeffrey W. Baker wrote:
>On Thu, 2 Aug 2001, Gunther Birznieks wrote:
>
> > When you've had your fill of wrestling over mySQL vs PostGres and stored
> > procs versus inline SQL.... (I know I have long ago)
> >
> > You guys should definitely read the following:
> >
> > http://www.ambysoft.com/persistenceLayer.html
> >
> > One of my current coworkers turned me on to this. I have found it to be one
> > of the best series of articles related towards what it takes to abstract
> > database away from your object layer and the various levels at which it
> > makes sense to do so.
> >
> > You may find the design a little complex, but Scott pretty explicitly
> > states that this is what is necessary for a *large* system. You can always
> > go down a less complex path by choice if you feel your programs aren't
> > complex enough to need the full Persistence Layer structure he advocates.
>
>I've worked with Scott Ambler, and I could record everything Scott Ambler
>knows about actually devleloping large systems on the head of a pin, using
>a magic marker. That guy is a hopeless academic without the slightest
>clue of how to actually make software happen.
I suppose I can't comment on your opinion as I do not personally know him.
But I find his statements to be worthy (as explained further below)
regardless of what you say about his real-world knowledge.
So I can only imagine that he has taken in many comments from users over
the years and made up his articles based on feedback since I think this one
is particular is reasonable. Although I've never had to implement all 6 or
so object abstractions in the ultimate persistence layer he recommends. :)
>Here's the brutal truth about persistance "abstractions" using an RDBMS
>backing store. At some point, your DBA is going to come up to you and
>tell you that you code is too slow. You need to rewrite some SQL queries
>to use a different index, or some sorting hints, or whatever. You will
>realize that you need to pass some extra information down through your
>abstraction layers to make it all happen. After that happens twice or
>thrice, you will slowly come to realize that your abstraction is really no
>abstraction at all: every time the schema changes, the top level interface
>needs to change as well.
I can't say that I agree.
It depends on what you are coding for. Are you coding for performance or
are you coding for getting a product out there that is easy to maintain?
In many cases, these two requirements are quite at odds. This thread was
originally sparked by someone getting annoyed that SQL was embedded
throughout the code and finding it hard to grasp how to deal with this.
While it's true that the best performance comes from hand-coding the SQL,
and if you hand-code the SQL, it should arguably be close to the section of
code that requires this SQL, not all programs require this. In fact, very
few in my experience. Those that have required speed have required it for a
small subset of operations in a larger project.
I strongly believe many apps can get away without having SQL embedded. I've
been doing it for the last several years and definitely coding and
maintenance time improves with some persistence layer abstraction. But yes,
you run the risk of having to go back and code a SQL statement or two, and
you run the risk of somewhat lower performance, but as Scott mentions in
his article, these should be the well-documented exception, not the rule.
Nick Tonkin posted a very clear and well written post a few minutes ago
about embedding SQL close to the code which may demonstrate the opposite of
what I am trying to say. But on the other hand, I could understand that a
company such as ValueClick really have to make sure their databases and the
SQL that accesses them are completely tweaked.
So I think given speed requirements, making a HERE document and using other
clean-coding techniques to keep the SQL close to the code that needs it is
quite reasonable.
However, in my experience...
Of the things that are harder to duplicate in a persistence layer to one
degree or another...
Not all applications require transactions
Not all applications require aggregation beyond count
Not all applications require blinding speed (just decent speed)
Not all applications require joins
Not all applications require unions
Not all applications require subselects
And even if you would argue that taking into account a union of
probabilities an application may need at least one of the above, I have
found it simply is not true. Usually when an application has a fairly
complex data model then they need more than one of the above and that's
when you have to move to SQL.
In other words, if the probability that an app needs each of the features
above is 5%, then rather than the union of the probabilities being 5 + 5 +
5 + 5 + 5 + 5, it is really more like 8% where the majority of the 5% is
really in applications that needs more than one of the above advanced SQL
list. Note that these percentages are not actual percentages, I just am
throwing out a number to demonstrate the point.
In simpler terms, most applications really need some place to just dump a
set of data and if it were flat files, the application and it's users would
be perfectly happy (robustness and speed of flat file searching not
withstanding).
It's also telling that mySQL is so successful and before that miniSQL had
been. Simply put, the features Sybase and Oracle offers about sub-selects
etc are just not frequently needed. I do think they are useful. I was a
Sybase DBA for 5 years from Sybase 4.2 on OS/2 and VMS and then evolved to
Sybase ASE when it came out a 3 years ago (and I quit doing DBA work).
Although I had to spend some of my time helping people performance tune
their queries, most of the applications I saw accessing "my dbms systems"
really could have been on mySQL for all the fanciness of their SQL and what
their applications actually required.
The one thing I would say, is that those of you that have been burned by
persistence layers are perhaps burned for a parallel reason people have
been burned by writing their own templating system. They start off simple
and then they try to accommodate complexities in an automatic way. The best
persistence layers for SQL that I Have worked with start out simple and
STAY simple. If you need a complexity, you expose $dbh and then run with it
and document that anomaly.
Following this rule, few of my programs have the equivalent of $dbh exposed
and those that do are the exception. This allows me to teach a new
programmer the persistence layer in half a day and then that's it and the
programs tend to be easier to maintain and write. It's really not that
different from utility functions that I see DBI programmers being used to.
It's just an argument of where you place this code in the program.
===
From: Daniel <dbohling@newsfactor.com>
Subject: Re: [OT] Inspired by closing comments from the UBB thread. (fwd)
Date: Wed, 01 Aug 2001 05:29:10 -0700
Nick Tonkin <nick@rlnt.net> wrote:
> The point is not that you can't abstract it all away as you show in your
> code below, it's that by the time you have covered all eventualities
> (sorts, groups, selects from multiple tables, et al.), your interface is
> so complicated you are basically paraphrasing the SQL in some new language
> of your invention. And that, if I am not mistaken, is the purpose of SQL
> in the first place!
>
> There is such a thing as over-abstraction, IMHO, and having played with
> this a lot, I have found that this type of effort would be such.
>
Nicely put Nick. There's already a Structured Query Language,
And there's an easy to use abstraction called DBI up on CPAN.
Feel free to use in application code thusly:
my $statement = qq~
SELECT field1, field2
FROM table
WHERE id = ?
~;
my $ref;
my $sth = $dbh->prepare($statement);
foreach my $question (@questions) {
$sth->execute($question);
$ref = $sth->fetchrow_hashref;
$sth->finish;
&display_data($ref);
}
At the end of the day you're gonna have a $dbh somewhere and it's gotta
receive some SQL to be useful. Hide it where you want to, I'll put it
real close to where the data is going to be used (unless the data needs
to be used from many different access points in which case all that
nasty :-) SQL goes into a OO module that understands how to provide:
my $handle = new foobar $dbh;
my $arrayref = $handle->gimme_foobar_data;
).
===
To: Dave Rolsky <autarch@urth.org>
From: Tim Bunce <Tim.Bunce@pobox.com>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: Thu, 2 Aug 2001 02:26:05 +0100
On Wed, Aug 01, 2001 at 01:19:58PM -0500, Dave Rolsky wrote:
> On Wed, 1 Aug 2001, Kyle Dawkins wrote:
>
> > I've taken a look at many of them (Tangram? a few others) and haven't been
> > impressed with any of them. I think part of the problem is that they're all
> > being developed in a bit of a vacuum. But let's capitalise on the interest
> > that this thread has generated to start a push for something that we can all
> > use. I think even the dudes who embed their SQL in perl could be made to
> > realise the benefits if we all started using a common framework. Thoughts?
>
> Well, people are starting to use my tool, Alzabo (alzabo.sourceforge.net)
> and I'm getting feedback. More feedback about what people want it always
> welcome. FWIW, Alzabo gives you a reasonable amount of control over the
> SQL that is generated, if you need it. It doesn't yet allow optimizer
> hints but that will change in a future version.
>
> OTOH, if you really _need_ to get into the nitty gritty details of SQL its
> hard to imagine that any abstraction layer would ever be satisfactory.
I think DBIx::AnyDBD is a pretty good compromise.
===
To: Daniel <dbohling@newsfactor.com>
From: Tim Bunce <Tim.Bunce@pobox.com>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread. (fwd)
Date: Thu, 2 Aug 2001 02:44:24 +0100
On Wed, Aug 01, 2001 at 05:29:10AM -0700, Daniel wrote:
> Nicely put Nick. There's already a Structured Query Language,
> And there's an easy to use abstraction called DBI up on CPAN.
> Feel free to use in application code thusly:
>
> my $statement = qq~
> SELECT field1, field2
> FROM table
> WHERE id = ?
> ~;
> my $ref;
> my $sth = $dbh->prepare($statement);
> foreach my $question (@questions) {
> $sth->execute($question);
> $ref = $sth->fetchrow_hashref;
> $sth->finish;
> &display_data($ref);
> }
Umm, these days I'd write loop that as:
foreach my $question (@questions) {
&display_data( $dbh->selectrow_arrayref($sth, undef, $question) );
}
:-)
Since ValueClick's been mentioned I'll point out that I now have the
task of exploring how to migrate all the embedded SQL code that Nick
mentioned from MySQL over to Oracle :-) [Hi Nick!]
I'm not a big fan of heavy abstractions and I'm pretty comfortable
with how much of the code is structured, in general.
I'm hoping that a mixture of new DBD::Oracle and DBI features, possibly
a DBD::Oracle::mysql subclass, and a sprinkling of DBIx::AnyDBD will
prove sufficient.
Combining that with using Oracle's ODBC gateway to make MySQL tables
appear live within Oracle should enable a smooth migration without a
sharp 'big bang' transition.
Of course, all this is just theory at the moment.
===
To: modperl@apache.org
From: Nigel Hamilton <nigel@e1mail.com>
Subject: [OT] Layers/interfaces/tears, was: Not embedding
SQL in perl
Date: Thu, 2 Aug 2001 07:10:24 +0000 (GMT)
Here Here ....
I think calling for an abstraction layer for the sake of it is
crazy design. I always thought modelling was about getting as close to the
subject as possible - distilling the essential - nothing more, nothing
less.
The more layers/interfaces/tears involved the more shaky a
foundation - and abstract a solution. Building software is not like
building a bridge --- over-engineering by pouring more cement doesn't
help.
The costs for this extra abstraction can include: extra debugging,
extra complexity, hobbling functionality (e.g., no group by clause etc.)
and extra maintenance (of the abstraction layers).
I still think a well designed relational model is the most solid
foundation a system can have - and the less layers/interfaces/tears on top
of that the better.
===
To: Tim Bunce <Tim.Bunce@pobox.com>
From: Dave Rolsky <autarch@urth.org>
Subject: Re: [OT] Inspired by closing comments from the UBB
thread.
Date: Thu, 2 Aug 2001 21:45:49 -0500 (CDT)
On Thu, 2 Aug 2001, Tim Bunce wrote:
> I think DBIx::AnyDBD is a pretty good compromise.
Well, I worked with Matt on the project for which it was developed
(WebBoard for Unix) and I still felt like there was just way too much
stuff to deal with. Just too much SQL. I wanted a more abstract way to
do things like outer joins, which are different in syntax across multiple
platforms.
It's definitely better than nothing, but I think for a larger project
you'll still end up with a huge amount of very similar SQL statements in
your modules.
I guess that's why I've been working on Alzabo (though I had actually
started that well before working on WBUX).
===