Slashdot Mirror
MySQL Stored Procedure Programming
Michael J. Ross writes "MySQL may be the most popular open source relational database management system (RDBMS) in the world, but during the first decade of its existence, it lacked support for stored programs, i.e., store procedures, functions, and triggers. The major commercial RDBMS vendors — including Oracle, IBM, and Microsoft — could point to this deficiency as reason enough to choose their proprietary systems over MySQL or any other open source system, such as PostgreSQL. But with the release of MySQL version 5.0, in October 2005, the "little database engine that could" dramatically improved its position against the competition. The most comprehensive discussion of these new capabilities is in the book MySQL Stored Procedure Programming." Read below for the rest of Michael's review
MySQL Stored Procedure Programming
author
Guy Harrison and Steven Feuerstein
pages
636
publisher
O'Reilly Media
rating
9
reviewer
Michael J. Ross
ISBN
0596100892
summary
A comprehensive guide to developing MySQL stored procedures, functions, and triggers.
Written by Guy Harrison and Steven Feuerstein, and published by O'Reilly Media in March 2006 under the ISBNs 0596100892 and 978-0596100896, this book is the first one to offer database programmers a full discussion of the syntax, usage, and optimization of MySQL stored procedures, stored functions, and triggers — which the authors wisely refer to collectively as "stored programs," to simplify the manuscript. Even a year after the introduction of these new capabilities in MySQL, they have received remarkably little coverage by book publishers. Admittedly, there are three such chapters in MySQL Administrator's Guide and Language Reference (2nd Edition), written by some of the developers of MySQL, and published by MySQL Press. Yet this latter book — even though published a month after O'Reilly's — devotes fewer than 50 pages to stored programs, and the material is not in the printed book itself, but in the "MySQL Language Reference" part, on the accompanying CD. That material, in conjunction with the online reference documentation, may be sufficient for the more simple stored program development needs. But for any MySQL developer who wishes to understand in-depth how to make the most of this new functionality in version 5.0, they will likely need a much more substantial treatment — and that's exactly what Harrison and Feuerstein have created.
The authors are generous in both the technical information and development advice that they offer. The book's material spans 636 pages, organized into 23 chapters, grouped into four parts, followed by an index. The first part, "Stored Programming Fundamentals," provides an introduction and then a tutorial, both taking a broad view of MySQL stored programs. The remaining four chapters cover language fundamentals; blocks, conditional statements, and iterative programming; SQL; and error handling. The book's second part, "Stored Program Construction," may be considered the heart of the book, because its five chapters present the details of creating stored programs in general, using transaction management, using MySQL's built-in functions, and creating one's own stored functions, as well as triggers. The third part, "Using MySQL Stored Programs and Applications," explains some of the advantages and disadvantages of stored programs, and then illustrates how to call those stored programs from source code written in any one of five different programming languages: PHP, Java, Perl, Python, and Microsoft.NET. In the fourth and final part, "Optimizing Stored Programs," the authors focus on the security and tuning of stored programs, tuning SQL, optimizing the code, and optimizing the development process itself.
This is a substantial book, encompassing a great deal of technical as well as advisory information. Consequently, no review such as this can hope to describe or critically comment upon every section of every chapter of every part. Yet the overall quality and utility of the manuscript can be discerned simply by choosing just one of the aforesaid Web programming languages, and writing some code in that language to call some MySQL stored procedures and functions, to get results from a test database — and developing all of this code while relying solely upon the book under review. Creating some simple stored procedures, and calling them from some PHP and Perl scripts, demonstrated to me that MySQL Stored Procedure Programming contains more than enough coverage of the topics to be an invaluable guide in developing the most common functionality that a programmer would need to implement.
The book appears to have very few aspects or specific sections in need of improvement. The discussion of variable scoping, in Chapter 4, is too cursory (no database pun intended). In terms of the book's sample code, I found countless cases of inconsistency of formatting — specifically, operators such as "||" and "=" being jammed up against their adjacent elements, without any whitespace to improve readability. These minor flaws could be easily remedied in the next edition. Some programming books make similar mistakes, but throughout their text, which is even worse. Fortunately, most of the code in this book is neatly formatted, and the variable and program names are generally descriptive enough.
Some of the book's material could have been left out without great loss — thereby reducing the book's size, weight, and presumably price. The two chapters on basic and advanced SQL tuning contain techniques and recommendations covered with equal skill in other MySQL books, and were not needed in this one. On the other hand, sloppy developers who churn out lamentable code might argue that the last chapter, which focuses on best programming practices, could also be excised; but those are the very individuals who need those recommendations the most.
Fortunately, the few weaknesses in the book are completely overwhelmed by its positive qualities, of which there are many. The coverage of the topics is quite extensive, but without the repetition often seen in many other technical books of this size. The explanations are written with clarity, and provide enough detail for any experienced database programmer to understand the general concepts, as well as the specific details. The sample code effectively illustrates the ideas presented in the narration. The font, layout, organization, and fold-flat binding of this book, all make it a joy to read — as is characteristic of many of O'Reilly's titles.
Moreover, any programming book that manages to lighten the load of the reader by offering a touch of humor here and there, cannot be all bad. Steven Feuerstein is the author of several well-regarded books on Oracle, and it was nice to see him poke some fun at the database heavyweight, in his choice of sample code to demonstrate the my_replace() function: my_replace( 'We love the Oracle server', 'Oracle', 'MySQL').
The prospective reader who would like to learn more about this book, can consult its Web page on O'Reilly's site. There they will find both short and full descriptions, confirmed and unconfirmed errata, a link for writing a reader review, an online table of contents and index, and a sample chapter (number 6, "Error Handling"), in PDF format. In addition, the visitor can download all of the sample code in the book (562 files) and the sample database, as a mysqldump file.
Overall, MySQL Stored Procedure Programming is adeptly written, neatly organized, and exhaustive in its coverage of the topics. It is and likely will remain the premier printed resource for Web and database developers who want to learn how to create and optimize stored procedures, functions, and triggers within MySQL.
Michael J. Ross is a Web programmer, freelance writer, and the editor of PristinePlanet.com's free newsletter. He can be reached at www.ross.ws, hosted by SiteGround.
You can purchase MySQL Stored Procedure Programming from amazon.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.
Written by Guy Harrison and Steven Feuerstein, and published by O'Reilly Media in March 2006 under the ISBNs 0596100892 and 978-0596100896, this book is the first one to offer database programmers a full discussion of the syntax, usage, and optimization of MySQL stored procedures, stored functions, and triggers — which the authors wisely refer to collectively as "stored programs," to simplify the manuscript. Even a year after the introduction of these new capabilities in MySQL, they have received remarkably little coverage by book publishers. Admittedly, there are three such chapters in MySQL Administrator's Guide and Language Reference (2nd Edition), written by some of the developers of MySQL, and published by MySQL Press. Yet this latter book — even though published a month after O'Reilly's — devotes fewer than 50 pages to stored programs, and the material is not in the printed book itself, but in the "MySQL Language Reference" part, on the accompanying CD. That material, in conjunction with the online reference documentation, may be sufficient for the more simple stored program development needs. But for any MySQL developer who wishes to understand in-depth how to make the most of this new functionality in version 5.0, they will likely need a much more substantial treatment — and that's exactly what Harrison and Feuerstein have created.
The authors are generous in both the technical information and development advice that they offer. The book's material spans 636 pages, organized into 23 chapters, grouped into four parts, followed by an index. The first part, "Stored Programming Fundamentals," provides an introduction and then a tutorial, both taking a broad view of MySQL stored programs. The remaining four chapters cover language fundamentals; blocks, conditional statements, and iterative programming; SQL; and error handling. The book's second part, "Stored Program Construction," may be considered the heart of the book, because its five chapters present the details of creating stored programs in general, using transaction management, using MySQL's built-in functions, and creating one's own stored functions, as well as triggers. The third part, "Using MySQL Stored Programs and Applications," explains some of the advantages and disadvantages of stored programs, and then illustrates how to call those stored programs from source code written in any one of five different programming languages: PHP, Java, Perl, Python, and Microsoft.NET. In the fourth and final part, "Optimizing Stored Programs," the authors focus on the security and tuning of stored programs, tuning SQL, optimizing the code, and optimizing the development process itself.
This is a substantial book, encompassing a great deal of technical as well as advisory information. Consequently, no review such as this can hope to describe or critically comment upon every section of every chapter of every part. Yet the overall quality and utility of the manuscript can be discerned simply by choosing just one of the aforesaid Web programming languages, and writing some code in that language to call some MySQL stored procedures and functions, to get results from a test database — and developing all of this code while relying solely upon the book under review. Creating some simple stored procedures, and calling them from some PHP and Perl scripts, demonstrated to me that MySQL Stored Procedure Programming contains more than enough coverage of the topics to be an invaluable guide in developing the most common functionality that a programmer would need to implement.
The book appears to have very few aspects or specific sections in need of improvement. The discussion of variable scoping, in Chapter 4, is too cursory (no database pun intended). In terms of the book's sample code, I found countless cases of inconsistency of formatting — specifically, operators such as "||" and "=" being jammed up against their adjacent elements, without any whitespace to improve readability. These minor flaws could be easily remedied in the next edition. Some programming books make similar mistakes, but throughout their text, which is even worse. Fortunately, most of the code in this book is neatly formatted, and the variable and program names are generally descriptive enough.
Some of the book's material could have been left out without great loss — thereby reducing the book's size, weight, and presumably price. The two chapters on basic and advanced SQL tuning contain techniques and recommendations covered with equal skill in other MySQL books, and were not needed in this one. On the other hand, sloppy developers who churn out lamentable code might argue that the last chapter, which focuses on best programming practices, could also be excised; but those are the very individuals who need those recommendations the most.
Fortunately, the few weaknesses in the book are completely overwhelmed by its positive qualities, of which there are many. The coverage of the topics is quite extensive, but without the repetition often seen in many other technical books of this size. The explanations are written with clarity, and provide enough detail for any experienced database programmer to understand the general concepts, as well as the specific details. The sample code effectively illustrates the ideas presented in the narration. The font, layout, organization, and fold-flat binding of this book, all make it a joy to read — as is characteristic of many of O'Reilly's titles.
Moreover, any programming book that manages to lighten the load of the reader by offering a touch of humor here and there, cannot be all bad. Steven Feuerstein is the author of several well-regarded books on Oracle, and it was nice to see him poke some fun at the database heavyweight, in his choice of sample code to demonstrate the my_replace() function: my_replace( 'We love the Oracle server', 'Oracle', 'MySQL').
The prospective reader who would like to learn more about this book, can consult its Web page on O'Reilly's site. There they will find both short and full descriptions, confirmed and unconfirmed errata, a link for writing a reader review, an online table of contents and index, and a sample chapter (number 6, "Error Handling"), in PDF format. In addition, the visitor can download all of the sample code in the book (562 files) and the sample database, as a mysqldump file.
Overall, MySQL Stored Procedure Programming is adeptly written, neatly organized, and exhaustive in its coverage of the topics. It is and likely will remain the premier printed resource for Web and database developers who want to learn how to create and optimize stored procedures, functions, and triggers within MySQL.
Michael J. Ross is a Web programmer, freelance writer, and the editor of PristinePlanet.com's free newsletter. He can be reached at www.ross.ws, hosted by SiteGround.
You can purchase MySQL Stored Procedure Programming from amazon.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.
"The major commercial RDBMS vendors -- including Oracle, IBM, and Microsoft -- could point to this deficiency as reason enough to choose their proprietary sstems over MySQL or any other open source system, such as PostgreSQL..." They could not point this because PostgreSQL has stored procedures for decades... fully tested, stable, offering e bunch of languages for writing functions/triggers, including Perl, Python, Tcl and their own PlSQL that is very close to Oracle's variant.
My understanding is that Firebird is by any measure more sophisticated than MySQL, but it lacks the "critical mass" of users that makes it attractive to people who need to be sure they can get ready support for their software. MySQL is available on just about every cheap hosting provider around, too, which means that a lot of ready-built open source Web apps target MySQL as their database of choice.
Put it to you this way: If the features of MySQL are "good enough" for the application you want -- which arguably goes for most of the Web apps out there -- why would you not choose MySQL? There are times when swimming upstream is a noble effort, but generally all it gets you is tired.
If, on the other hand, you have a specific application for which you need a relational data store and you need higher-end RDBMS features, by all means, choose Firebird. Only I think in those cases most people still choose PostgreSQL, for more or less the same reasons as mentioned above.
Breakfast served all day!
How about...
return (estimated_rows_in_table($table) < 100000);
Rest assured that if you were one of my developers you'd be out the door in no time. The application you're describing here cannot be anything north of trivial if you were able to just switch a connection string and all your messy inline SQL statements continued to run without any changes whatsoever (which I seriously doubt). Not everyone writes trivial applications.
There are reasons other than "fast" to using stored procedures. I've seen enough misuse of SPs, functions and triggers to fill up a book, but when done correctly they are simply superior to the alternative in just about every way.
Web2.0: I love when people Flickr my cuil and digg my boingboing until my google is reddit and I start to yahoo
I'm glad I don't work for you, then. Stored procedures are a tool, like any other, that come with their own set of pros and cons. In some situations, the ability to quickly migrate to an alternate database outweighs the benefits that stored procedures may provide. In other situations, it doesn't. The decision to use them or not should be based solely on business requirements, rather than the irrational hatred you seem to be using as a guide.
"The invisible and the non-existent look very much alike." -- Delos B. McKown
The purpose behind stored procedures is not speed. It is about data integrity. Trigger functions are used to maintain additional coherence rules that are not easily expressed by unique, not null, referential integrity, etc. You place these rules in the database so that every consumer application of the database goes through the same logic--receives the benefits of the logic and enforces a coherent logic.
Stored procedures should be used to enforce data integrity and data access. The only "business logic" that should be there is the logic that applies to keeping your data whole, complete, and coherent.
Middle-tier application layers are great to help pull data together, present model-specific views of data (say, OO/R mapping, if that is what gives you a chubby), and provide update interfaces. They can even do a first pass at making sure the database will accept the data.
But.
The data logic should be stored firmly in the database logic itself, using stored procedures and triggers and rules. RDBMS engines are *designed* to keep your data in good shape. Use them in that fashion, and you will be plenty happy. Don't, and you risk losing data integrity, and you sacrifice security.
Microsoft is to software what Budweiser is to beer.
I think part of postgresql's problem is its awful name. Just about anything would have been better (e.g. TurboSQL, AgileSQL, FastSQL, UglySQL, FatDumbAndHappySQL, etc).
Oh man, talk about a can of worms.
On the one hand, folks who are trained to think in terms of data relationships and database development are liable to want to put as much as possible right in the database.
On the other hand... well, forget it. There is no other hand, it's more like a shifting mass of appendages, like Shiva on an acid binge. The other extreme are people like David Heinemeier Hansson, who want to use database to store information and otherwise stay the hell out of the way - as I believe he put it once, to be as dumb as possible.
That may be a minority view, but the minority isn't exactly vanishingly small. eBay, for example, not only skips out on transactions (you know, the thing people like to beat up on MySQL for not having years ago), but doesn't even enforce referential integrity in the database. Stored procedures are right out. (warning: reference is a PDF.)
My own personal, under-informed and probably worthless view: I like enforce referential integrity where I can in the DB, and transactions are a good thing, but stored procedures are better left to maintenance and administration functions than anything involving business logic.
It does speak volumes about MySQL's history, though. The things he lists are are the reasons why MySQL is still compared with M$ Access. They are valid points, because MySQL as a company has a sordid past, as far as presenting something that resembles modern database theory. I'm not saying I don't appreciate MySQL, I'm just say that I thoroughly understand the frustrations. MySQL has been presented as an off the shelf enterprise contendor for years and that's only begining to resemble fact.
Under the influence of Post-Cyberpunk Gonzo Journalism
And if I were a manager, I'd fire ALL OF YOU for wasting time on a /. DBA pissing match instead of working.
Even that is generous. If you want to use a database as a simple store (and if they've fixed the tendency to hiccup and corrupt the database), MySQL may be acceptable.
If you want a DATABASE it's not even on the table, no pun intended. The whole point of a RDBMS is that it isn't a simple store, it's a mechanism for ensuring that your collection of data is always in a sane state even if you've brought in some clueless interns for the summer or you have a disgruntled employee. Or even if you just have two developers (or one developer with a poor memory).
ACID and referential integrity are two items that are absolute requirements. Calling something without these features a 'relational database' puts you in the same pointy-hair territory as Dilbert's boss saying that he heard the 'mauve' databases were best.
Attribute constraints, triggers and stored procedures aren't as necessary, but they're still extremely powerful ways of ensuring the sanity of your data. Use a trigger to update a 'updated_on' field, don't just trust the developer to always update it. Use an attribute constraint to ensure that you color field is always 'R', 'G' or 'B' (or just use referential integrity to point to a color table).
Finally, to address a question asked elsewhere stored procedures are extremely powerful security tools. By now everyone should know that using string concatenation to prepare queries is a Very Bad Idea. A potentially "don't let the door hit you in the butt on your way out" Bad Idea. Prepared statements are better, but how can you enforce it?
Stored procedures give you an alternative. Drop INSERT and UPDATE rights and force everything to go through a corresponding stored procedure. It's a little more work but it should eliminate any risk of SQL exploits. (It's not a 100% guarantee since you can't eliminate the risk that the database itself can be compromised by carefully selected parameters.)
For every complex problem there is an answer that is clear, simple, and wrong. -- H L Mencken
I'm currently looking at a table on a production MySQL db with 176 million rows in it. It has no problems at all, other than being a huge amount of data. Your ideas about MySQL's limitations are not based on fact.
Yep it was really irritating. A lot like the Postgres users who kept claiming the next version of their server was going to be really fast.
In the end the postgres crowd have a right to be upset. They could have been the major player for open databases but their devs were too arrogant to listen to their users and design it to be friendly.
For all its faults mysql is trivial to install and works out of the box for most applications. Last time I looked the default install for postgresql still seemed to be tuned as though it would never have to handle more than a handful of users. Stored procedures and TCP/IP are off by default and the auto vacuum thing needs to be set up manually. Then don't forget that while the postgres query parser does more error checking that mysql its error messages are incredibly cryptic so its MUCH hard to trouble shoot.
Your average newbie takes one looks at it, gives up and moves to mysql. When that newbie finally grows up he has too much time invested in mysql to be bothered learning the intricacies of postgres. Every extra feature that mysql adds is one less reason for anyone to try postgres.
Postgres is a better DB once its setup and tuned properly but considering its usability issues and that mysql users probably out number postgres users a thousand to 1 or more I think mysql is always going to be the number one opensource DB.
Amen, brother.
For people not familiar with MySQL's history, I would suggest a little reading from previous versions of MySQL's manual:
How to cope without COMMIT/ROLLBACK: For the moment, we are much more for implementing the SQL server language (something like stored procedures). With this you would very seldom really need COMMIT-ROLLBACK. This would also give much better performance.
Reasons not to use foreign keys. There are so many problems with FOREIGN KEYs that we don't know where to start.
I decided long ago that the MySQL guys are clowns. MySQL's lack of features was never as big a problem as the fact that I just couldn't take these guys seriously (and the above is only a small subset of the reasons for that).
- Sw Usr
the Postgres speed was true until the release of Version 8. There it might such a huge jump, that was just unbelievable. You should give it a try.
"Freiheit ist immer auch die Freiheit des Andersdenkenden" - Rosa Luxemburg, 1871 - 1919