[OT] Layers/interfaces/tears, was: Not embedding SQL in perl
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. Nige 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
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: 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
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
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
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
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
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
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
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
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
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
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
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 (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 (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.
