Re: Not embedding SQL in perl (was RE: [OT] Inspired by closing comments from the UBB thread.)
[EMAIL PROTECTED] (Jeffrey W. Baker) wrote: >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. Don't hold back Jeff, tell us how you really feel. =) I know I've worked with some people who I'd want to say the same thing about, but so far nobody's posted any of their treatises on the list. I find this thread kind of interesting, because it's really about how different people have solved complicated problems. It's clear that some of the disagreement happens because people haven't adequately experimented with the various approaches (who has time for that, anyway?), but undoubtedly some of the disagreement happens because people genuinely have very different problems to solve. ------ Ken Williams Last Bastion of Euclidity [EMAIL PROTECTED]The Math Forum
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
Re: Not embedding SQL in perl (was RE: [OT] Inspired by closing comments from the UBB thread.)
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. kyle Software Engineer Central Park Software http://www.centralparksoftware.com
Re: Not embedding SQL in perl (was RE: [OT] Inspired by closingcomments from the UBB thread.)
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. -jwb
Re: Not embedding SQL in perl
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. Regards, Henrik -- Henrik Edlund <[EMAIL PROTECTED]> http://www.edlund.org/ "You're young, you're drunk, you're in bed, you have knives; shit happens." -- Angelina Jolie
Re: Not embedding SQL in perl
> 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* MySQL has its place in the database world, otherwise it would not be so widely deployed. Some tasks do not require a huge full featured DBMS to get the job done, so why should they put that requirement on the end user? Are you under the impression that Oracle is the best db server to use for a web based voting application? Probably not... Using MySQL is not cutting corners, its a design decision... if MySQL suits the needs of the developers and their application, spending time switching to a "real DBMS" is a total waste. Ryan
Re: Not embedding SQL in perl
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. Cheers Kyle Software Engineer Central Park Software http://www.centralparksoftware.com
Re: Not embedding SQL in perl
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. [EMAIL PROTECTED] wrote: > > > Original Message -------- > Subject: Re: Not embedding SQL in perl > Date: Wed, 01 Aug 2001 15:56:00 -0400 > From: kyle dawkins <[EMAIL PROTECTED]> > To: Henrik Edlund <[EMAIL PROTECTED]> > References: <[EMAIL PROTECTED]> > > > > 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_. >> >> *sigh* >> > > Henrik > > 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). > > Kyle > Software Engineer > Central Park Software > http://www.centralparksoftware.com
Re: Not embedding SQL in perl
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. Regards, Henrik -- Henrik Edlund <[EMAIL PROTECTED]> http://www.edlund.org/ "You're young, you're drunk, you're in bed, you have knives; shit happens." -- Angelina Jolie
Re: Not embedding SQL in perl
Original Message Subject: Re: Not embedding SQL in perl Date: Wed, 01 Aug 2001 15:56:00 -0400 From: kyle dawkins <[EMAIL PROTECTED]> To: Henrik Edlund <[EMAIL PROTECTED]> References: <[EMAIL PROTECTED]> 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_. > >*sigh* > Henrik 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). Kyle Software Engineer Central Park Software http://www.centralparksoftware.com
Re: Not embedding SQL in perl
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* -- Henrik Edlund <[EMAIL PROTECTED]> http://www.edlund.org/ "You're young, you're drunk, you're in bed, you have knives; shit happens." -- Angelina Jolie
Re: Not embedding SQL in perl
>> >>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 >behavior 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). Interesting, I will be working w/MySQL in a few days on a side project of my own. We'll see how my outlook changes ;) Any recommendations? Regards, Dave "Language shapes the way we think, and determines what we can think about." -- B. L. Whorf
Re: Not embedding SQL in perl
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. Michael -- Michael Peppler - Data Migrations Inc. - http://www.mbay.net/~mpeppler [EMAIL PROTECTED] - [EMAIL PROTECTED] International Sybase User Group - http://www.isug.com
Re: Not embedding SQL in perl
> > 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. - Perrin
Re: Not embedding SQL in perl
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; sotred 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. Sigh. Kyle Software Engineer Central Park Software http://www.centralparksoftware.com
RE: Not embedding SQL in perl
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. Michael -- Michael Peppler - Data Migrations Inc. - http://www.mbay.net/~mpeppler [EMAIL PROTECTED] - [EMAIL PROTECTED] International Sybase User Group - http://www.isug.com
RE: Not embedding SQL in perl
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)? Regards, Dave "Language shapes the way we think, and determines what we can think about." -- B. L. Whorf
