|Welcome to the Monastery|
I do agree though that there shouldn't be SQL mixed with Perl (C, Java, Python, whatever).
So far, so good. I don't think anyone will disagree with that statement.
All SQL code should live in the database - and all the code is allowed to do is to call stored procedures.
In other words, there should be an API mapping business logic to the datamodel. Your statement goes a little farther in that you actually call for a specific architecture to do that. However, I am in agreement with you, on principle.
And the code calling the stored procedures should be in a layer itself as well.
So, what's the difference between having calls to stored procedures (which is SQL) and the actual SQL statements in this layer? You're just adding an additional layer between the code calling the business-logic DB functions and the actual SQL.
Now, it can make sense to do this. mpeppler is a big fan of this solution and he has a good reason for it - the data model is owned by the DBA, not just the database instance. APIs exist to allow people in one group to call functions owned by another group. Creating APIs for their own sake is a good way to get lost in a maze of Java-like passages, all alike.
Hence, I wouldn't use MySQL, as that doesn't have stored procedures. (Or triggers. Or subselects.)
Oh, really? 4.1.4 has subselects - I'm using them right now in a production web application. The 5.x line (currently under development) will have stored procedures, triggers, and updateable views. It'll also have usage of multiple indices per table in the same query. (A feature, I might add, that neither Oracle, SQL*Server, DB2, or Sybase have.) There is also work being done on clustered MySQL databases, using an architecture with zero single points of failure (unlike Oracle RAC, which has a single point of failure). NDB integration should be complete by Q2, 2005, AFAIK.
I would challenge you (or anyone else, for that matter) to actually find the facts before promulgating FUD about any opensource project. Do your projects stay stagnant for 3 years in terms of features? Mine don't, and neither does MySQL.
Update: added "single" when discussing the number of failure points in MySQL/NDB clustering. Thanks, sandfly!
Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose
I shouldn't have to say this, but any code, unless otherwise stated, is untested