Re^2: Code Conflation: Considered Harmful?

[dragonchild]

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!

[sandfly]

Zero points of failure?

I think you're decrementing when you should be incrementing. The question is: what's the minimum number of components that need to fail for the system to fail? You're saying that for Oracle RAC, it's one. I'm not clear what you're claiming for the MySQL cluster, but obviously if every CPU in the cluster burned out, that would do it, so there is an upper bound.

[dragonchild]

Zero single points of failure. Oracle RAC depends on a central storage area. So, if that storage area fails, the database is down, even if all the processing nodes are still up. (Obviously, you can make the storage area itself redundant with various strategies. But, that's an additional step that needs to be taken.)

With NDB clustering, every processing node is also a storage node. So, the failure of any single point will not bring the database down.

Every article I've ever read on the concept of clustering says that this is the most robust architectural model.