Antwort: [OT] Inspired by closing comments from the UBB thread.
Hi, our code contains exactly one SQL-Statement: our $getSQL = 'select SQLSTMT, INPARAM, OUTPARAM from SQLSTMT where ID=?'; but I think it would even be cleaner to use a PerlSetVar for that... cu Michael Datum: 01.08.2001 17:14 An:[EMAIL PROTECTED] Betreff: [OT] Inspired by closing comments from the UBB thread. Nachrichtentext: 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. Thanks in advance. --Joe Breeden --
Re: [OT] Inspired by closing comments from the UBB thread.
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). -dave /*== www.urth.org We await the New Sun ==*/
[OT] Inspired by closing comments from the UBB thread.
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. Thanks in advance. --Joe Breeden --
Re: [OT] Inspired by closing comments from the UBB thread.
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='[EMAIL PROTECTED]'); $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. On Wed, Aug 01, 2001 at 10:12:45AM -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?). ... It would be interesting to know how other people have solved that problem. Currently, we are essentially using embedded SQL in our apps. -- Barry Hoggard
Not embedding SQL in perl (was RE: [OT] Inspired by closing comments from the UBB thread.)
Joe Breeden [mailto:[EMAIL PROTECTED]] 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. Michael
Re: [OT] Inspired by closing comments from the UBB thread.
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. Rob -- A good magician never reveals his secret; the unbelievable trick becomes simple and obvious once it is explained. So too with UNIX.
Re: [OT] Inspired by closing comments from the UBB thread.
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='[EMAIL PROTECTED]'); $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. - Perrin
Re: [OT] Inspired by closing comments from the UBB thread.
* Joe Breeden ([EMAIL PROTECTED]) [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. plugSPOPS (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 Chris -- Chris Winters ([EMAIL PROTECTED]) Building enterprise-capable snack solutions since 1988.
Re: [OT] Inspired by closing comments from the UBB thread.
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? On Wed, 1 Aug 2001, Barry Hoggard wrote: 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='[EMAIL PROTECTED]'); $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. On Wed, Aug 01, 2001 at 10:12:45AM -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?). ... It would be interesting to know how other people have solved that problem. Currently, we are essentially using embedded SQL in our apps. -- Barry Hoggard
Re[2]: [OT] Inspired by closing comments from the UBB thread.
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. Best Regards, Mike e-mail: [EMAIL PROTECTED]
Re: [OT] Inspired by closing comments from the UBB thread.
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. - Perrin
Re: [OT] Inspired by closing comments from the UBB thread.
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. Jay 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. - Perrin
Re: [OT] Inspired by closing comments from the UBB thread.
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. --Alex
Re: [OT] Inspired by closing comments from the UBB thread.
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 gd 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. Cheers to all kyle Software Engineer Central Park Software http://www.centralparksoftware.com
Re: [OT] Inspired by closing comments from the UBB thread.
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. - Perrin
Re: [VERY OT] Inspired by closing comments from the UBB thread.
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. Jay
Re: [OT] Inspired by closing comments from the UBB thread.
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? kyle Software Engineer Central Park Software http://www.centralparksoftware.com
Apple not yukky aymore: was [OT] Inspired by closing comments from the UBB thread.
On Wednesday, August 1, 2001, at 09:27 AM, Kyle Dawkins wrote: Fine, it's Apple (yuk). But it used to be *NeXT* and it used to be *Obj-C*, both very very fine things indeed. Hey now! Those are fighting words! :-) OS X Mach + FreeBSD Project Builder + GCC (Including Objective-C) in EVERY OS BOX CVS, SSH, Apache, Perl, etc. in EVERY OS BOX Nothing yuk about Apple anymore, at least on the software/OS side of the house! Apple = NeXT ! Thank God! Hell, in 15 or 20 years, this OS could be as enlightened as Linux. :-) -- -- Tom Mornini -- ICQ 113526784
Re: [VERY OT] Inspired by closing comments from the UBB thread.
On Wednesday, August 1, 2001, at 10:01 AM, Jay Jacobs wrote: On Wed, 1 Aug 2001, Kyle Dawkins wrote: Mixing HTML with Perl with SQL is bad and evil on every single possible level. 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. 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. :-) 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. Assuming they're paying you anywhere near a living wage, their money would be better spent on modestly upgraded hardware than having you fumbling around with inefficient to maintain code. 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! :-) -- -- Tom Mornini -- ICQ 113526784
Re: [OT] Inspired by closing comments from the UBB thread.
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.2content-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 -- Ray Zimmerman / e-mail: [EMAIL PROTECTED] / 428-B Phillips Hall Sr Research / phone: (607) 255-9645 / Cornell University Associate / FAX: (815) 377-3932 / Ithaca, NY 14853
Re: [VERY OT] Inspired by closing comments from the UBB thread.
Tom et al. Mixing HTML with Perl with SQL is bad and evil on every single possible level. 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. 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. :-) H AH AH AH AH HA HAHAHAHAH brilliant 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. Assuming they're paying you anywhere near a living wage, their money would be better spent on modestly upgraded hardware than having you fumbling around with inefficient to maintain code. Tom, I couldn't have said it better myself. BTW. The project I am working on right now *is* for a small non-profit. We don't have a P200 but we have a single P3 machine doing all the work. We don't have huge fault-tolerant systems or UML models or Java Class Hierarchy posters on our walls, or a coding team in Bangalore working on our project. All this notwithstanding, I have time to go to the bathroom. I can even take reading material with me. I have been in the two-person startup before... and let me tell you, if you think that you should cut corners now, it's just going to bite you in the arse later. 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. Cheers Kyle Software Engineer Central Park Software http://www.centralparksoftware.com
RE: [OT] Inspired by closing comments from the UBB thread.
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.
Thanks,
--Joe Breeden
--
Re: [OT] Inspired by closing comments from the UBB thread.
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 -- Matt/ /||** Founder and CTO ** ** http://axkit.com/ ** //||** AxKit.com Ltd ** ** XML Application Serving ** // ||** http://axkit.org ** ** XSLT, XPathScript, XSP ** // \\| // ** mod_perl news and resources: http://take23.org ** \\// //\\ // \\
RE: [OT] Inspired by closing comments from the UBB thread.
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. L8r, Rob #!/usr/bin/perl -w use Disclaimer qw/:standard/;
Re: [OT] Inspired by closing comments from the UBB thread.
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 ;) -dave /*== www.urth.org We await the New Sun ==*/
Re: [OT] Inspired by closing comments from the UBB thread.
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. -dave /*== www.urth.org We await the New Sun ==*/
RE: [OT] Inspired by closing comments from the UBB thread.
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 :-)
My $.02.
L8r,
Rob
#!/usr/bin/perl -w
use Disclaimer qw/:standard/;
Re: [VERY OT] Inspired by closing comments from the UBB thread.
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.
Re: [OT] Inspired by closing comments from the UBB thread.
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...)
RE: [OT] Inspired by closing comments from the UBB thread.
I have to agree here. Is this just a hash of SQL statements or is there more to it than that? --Joe Breeden -- -Original Message- From: Perrin Harkins [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 01, 2001 1:29 PM To: Matt Sergeant Cc: [EMAIL PROTECTED] Subject: Re: [OT] Inspired by closing comments from the UBB thread. 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...)
Re: [OT] Inspired by closing comments from the UBB thread.
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. -- Matt/ /||** Founder and CTO ** ** http://axkit.com/ ** //||** AxKit.com Ltd ** ** XML Application Serving ** // ||** http://axkit.org ** ** XSLT, XPathScript, XSP ** // \\| // ** mod_perl news and resources: http://take23.org ** \\// //\\ // \\
RE: [OT] Inspired by closing comments from the UBB thread. (fwd)
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
On Wed, 1 Aug 2001, Joe Breeden wrote:
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.
Thanks,
--Joe Breeden
--
RE: [OT] Inspired by closing comments from the UBB thread.
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.
--Joe Breeden
--
Sent from my Outlook 2000 Wired Deskheld (www.microsoft.com)
-Original Message-
From: Nick Tonkin [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 01, 2001 4:15 PM
To: Joe Breeden
Cc: [EMAIL PROTECTED]
Subject: RE: [OT] Inspired by closing comments from the UBB thread.
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
On Wed, 1 Aug 2001, Joe Breeden wrote:
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.
Thanks,
--Joe Breeden
--
Re: Not embedding SQL in perl (was RE: [OT] Inspired by closing comments from the UBB thread.)
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.
Re: [OT] Inspired by closing comments from the UBB thread. (fwd)
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;
).
--
Daniel Bohling
NewsFactor Network
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
On Wed, 1 Aug 2001, Joe Breeden wrote:
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 =
$@);
Re: [OT] Inspired by closing comments from the UBB thread.
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. Tim.
Re: [OT] Inspired by closing comments from the UBB thread. (fwd)
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.
Tim.
