Oversimplification: MySQL Limits in ActiveRecord

Thu 22 December 2005

Filed under Politics

Tags Lamer Moments Retarded

The Ruby on Rails community seems like street preachers these days.

Talk to real-world programmers who run businesses and you are likely to come across strong sentiments about 'over-engineering'. These feeling will run deep and often are driven by a personal whimsy. It does not reduce their validity, it merely colors the judgment of the affected party. Nowhere does this become more easily prevalent than with databases and the applications that use them

Ruby on Rails is an up and coming web framework that has put a lot of common sense into web application development. The front page says it all. David Hansson is the man behind Rails. He is a stalwart MySQL advocate. MySQL gets lots of press both good and bad. It is very common, especially useful for simple stuff, and has long implemented a subset of SQL that you find in various commercial Enterprise Databases. I.e. Oracle, DB2 etc. In the Open Source world we now have at least two databases which are on par with the SQL support seen in Oracle and DB2. PostgreSQL and Firebird are both seen as examples of good SQL compliant RDBMSs. Five years ago, people using MySQL really preached about speed on low-end hardware. That gap has been closed and now we come to things like ACID and SQL support.

I don't want to dwell on features, since I am not really arguing about that. A few things that PostgreSQL has had for log time: real transactions, MVCC, triggers, and stored procedures. MySQL advocates will either scratch their head when you mention these things, start selling the 'less complexity is better' or want to argue about so-called 'business logic' in your database. Note that MySQL has picked up many more of these so-called 'business logic' features in the new 5.0 release.

David Heinemeier Hansson has made the no business logic argument. Please RTFA before taking my word for anything. The wider part of the debate (as I understand it) is whether Rails, specifically "Active Record" can/should determine more about your data structure from the database. It's not possible (currently) to do this in MySQL (version 4). A small excerpt:

Unlike Christopher, I consider stored procedures and constraints vileand reckless destroyers of coherence. No, Mr. Database, you can not havemy business logic. Your procedural ambitions will bear no fruit andyou'll have to pry that logic from my dead, cold object-oriented hands.

David is being hypocritical about this. He is confusing business logic with relational logic for the database. Yet he gets it spot-on in the templating system. Some web-templating systems have failed to distinguish business logic from presentation logic. David has suitable logic in templates, allowing for a simpler and more elegant display layer. For some reason (MySQLism) he doesn't see the same thing when applied to the database. So people using their database wisely will lose in DRY terms. He preaches that Active Records should be the authoritative source for describing your database, but that doesn't make any sense at all.

The more your application and database grow, the less sense it makes to avoid using tools like constraints, triggers and stored procedures. When you are storing and processing hundreds of thousands of records, your database should be able to pear down and return only what you need. As your use grows, your database should have enough information to understand how your data should be stored. In a few cases it is desirable for it to understand how your data is retrieved. Reporting, large joins and compiling statistics are examples of where a good RDBMS can excel. All of the above tend to be well applied when used as extensions of the basic concept of an index.

I have seen the MySQL approach from a large number of people. The laughable extreme of this viewpoint is seen with SQLlite, where some consider types a misfeature of SQL. I have been there myself, and made similar flawed arguments against better databases. I have also participated in the other side, over-engineering and business logic constrained databases. I've seen developers go from the database understanding how to store the data, to the RDBMS actually understanding the data. I don't know how long David has been working with SQL, but one large project in MySQL cured me. I am a little surprised given the templating used in Rails. The templates and the MVC approach are what makes Ruby interesting in the first place. It's disappointing to see a less pragmatic approach to storage.

Putting all your eggs in the ActiveRecord basket will only work for so long. Eventually you will be forced to do multiple queries, or suffer from massive amounts of returned records in order to accomplish your purpose. People end up building 'report logic' and 'trigger logic', etc,etc, into the application. There is nothing hugely wrong with that, but it just makes sense in the database. The database is good at processing data. I agree, keep the actual business logic in an application. However, let the database do what it's good at. DRY.

Software with a political agenda has a harder time surviving. Software should not necessarily keep you from doing stupid things. The other extreme is clearly possible, allowing stupid people to do too many things. ActiveRecord should be grown up enough to live in the Real World(tm), not a dream land where things are clean and neat and so nicely segmented.

There is a universe of space between 'ActiveRecord is your normalization and enforcement' and 'You are the RDBMSs bitch'. Somebody should extend ActiveRecord to pull schema information from capable databases. MySQL 5 has lots of these abilities, and would benefit from that work as well.


Up To Something © Joshua M Schmidlkofer Powered by Pelican and Twitter Bootstrap. Icons by Font Awesome and Font Awesome More