Of MySQL 5.1 and Joomla part 1

Some of you might know that I run a little website on my few spare hours (actually, I have several sites, but I have one that takes up some 95% of all the time I spend on these sites). The site is called PapaBlues and if you pop by some time, and you have seen it before, then you realize that there has been a very major restructuring. The old site is all gone, and the thing is now built using the Joomla CMS as the framework, whereas the old site was using homebuild PHP, HTML and SQL in a bit of a mess, like so many other sites.

I took the new site live just before Christmas, and as MySQL 5.1 had just been declared GA, I decided to use this for my site. I have to say that I am very happy with it so far, stable and performant, and with a some new useful features, I will write more about these in a later blogpost.

If you are currently using Joomla and want to upgrade to MySQL 5.x, then I can tell you that this works fine. To an extent at least, but the limitations are by far more in Joomla than in MySQL, sadly.

What I mean when I say that I use Joomla as a framework, is that I use the Joomla basic libraries, database components etc, and some base components also, but very few of the Joomla modules, components or templates. Except the articles, all the content is provided by my own home-built components that keep track of blues bands, gigs, festivals etc, all running in my own designed template (this I am a bit proud of, as I am a very bad GUI and grphic designer, but I think the look of this turned out OK).

One thing that bugs me with Joomla is that the developers of it seems set in MySQL 4.0 days, actually, in the database mdules, there is a specific "SET SQL_MODE = 'MYSQL40'" command. Come on now, Joomly guys, MySQL 4.0 is about to become extinct! In the comment to this command in the database module, there is even a comment that says that this is to overcome the issues with "STRICT" settings. Yeah right, I want it STRICT so that I know things are rights, just as I want NO WARNINGS when I compile a piece of code. No, this isn't good enough.

In theory, Joomla is database agnostic, at least on paper. In the real world, it is firmly set not only with MySQL but also with MySQL 4.x syntax and behaviour. Try to use the Joomla installer to install a stored procedure or function. It will not work, the SQL installer doesn't understand the delimiter command. This is annoying, but frankly, the stuff still works, even with MySQL 5.1.

You have complete control of your SQL in your own modules, which makes life easier. In the database tables that I use for my modules, not the tables that Joomle use interally, I use InnoDB with aggressive foreign keys all over the place, just the way I like it. I want to be able to mock around a bit with the data, even with interactive SQL, knowing that my relations are still intact.

NULL values is another area where the Joomla database developers has gone all wrong. So very wrong. In essence, they assume that NULL is the same as 0 (for a numeric values) or the empty string for a string value. This I can to an extent understand, as an HTML form, which is the base for much of the administration of a Joomla site at least, doesn't know about NULL either. This makes things a mess when it comes to FOREIGN KEYS. A NULL in a referencing key is allowed, without a matching references key, but not so a 0 or an empty string.

But why would you want that anyway? A refernce to any value is useless, right? Well, if we are talking single column keys, yes, that is probably true, but for a multi column key, this is a very useful feature. I will explain why in a later blogpost, and how this works.

All in all then, am I happy with my Joomla and MySQL 5.1 combination? Yes, sure, it works as expected. I do have quite a few issues with Joomla, the docs are no good, there are few really good books on it (some half-good are avilable now), the database support is not as good as it should be, and the database independence features that Joomla provides aren't good enough, and aren't used much even in the basic Joomla modules. Fact is, I tried running without MYSQL40 mode, and failed. And with STRICT mode, it was even worse. I gave up trying to fix this.

But it does work, it comes for free, the framework seems reasonably stable and once you get the hang of it, it is easy to use. The issue is that it just takes too much work to get the hang of it. If you have a simple site with some article you want to publish, then Joomla is just fine. If you are picky with the database interface and has some special needs, prepare to spend some time with it.

One sign, if you ask me, that the docs are not that good, is that fact that even developing an application with Joomla, not developing Joomla itself, I constantly go back to read the source.

But Joomla is helping me, the site is finally up and running, I have developed quite a bunch of useful Joomla utilities and components (one utility is a PapaTable class, derived from the JTable class, that fixes one big NULL value issue with Joomla), and people have told me they like the site, so maybe I shouldn't complain. And instead of complaining, I plan to write a MySQL 5.0 database module for Joomla, based on the MySQL 4.0 assuming ones that already exist. And then we'll see.

And BTW, don't try running Joomla with the Joomla system tables in InnoDB, it is error prone and not even that useful.

/Karlsson