This is a very old article. It has been imported from older blogging software, and the formatting, images, etc may have been lost. Some links may be broken. Some of the information may no longer be correct. Opinions expressed in this article may no longer be held.
I’m currently in the process of extending demiblog’s database support. Version 0.1.0 only supports PostgreSQL, but I plan on also supporting MySQL in the next release, and a few other databases further down the line.
Because this cross-database support had always been planned, I’ve been very careful in writing my SQL to make sure that only standards-compliant syntax is used, and there are no proprietary PostgreSQL extensions.
I always knew that I’d have to adjust my SQL slightly to get it to run on other databases, but the amount of changes needed to port a seemingly simple SQL script with less than 300 lines to MySQL was astounding! Although a few of the adjustments were needed to account for SQL features that MySQL simply doesn’t yet support, the majority were to take into account several of MySQL’s seemingly-gratuitous bastardisations of the standard SQL syntax.
MySQL, how do I hate you, let me count the ways…
SQL-standard double-quotes used to quote identifiers, must be replaces with non-standard backticks.
The varchar datatype requires a maximum length to be specified. If you do not want to specify a length, then you must instead use MySQL’s text datatype, however…
text cannot be used as a primary key column; and
text cannot take a default value.
Seemingly, only one timestamp column in any given table may take a default of CURRENT_TIMESTAMP(0). Who, in God’s name, thought this was a good idea?!
Oh, and SQL-standard CURRENT_TIMESTAMP(0) cannot be used — NOW() must be used instead.
Oh, and the with time zone modifier for timestamp columns cannot be used.
Seven, only seven? Oh well, I’m sure there will be many more to come — this is only the very early stages of the work — it’s the script used to create the various tables and indexes used by the software, and populate some of the tables with standard data, such as the list of article statuses. (Draft, Standard, Historical, Obsolete, Hidden, Deleted, etc. Why keep this information in the database? So that you can add to them with your own extra statuses. I don’t recommend removing the default statuses though, as some parts of demiblog infer special meanings to them — off the top of my head, the iCalendar and hCalendar feeds are the only parts to do so so far.)
Anyway, the SQL should now be checked in to demiblog’s subversion repository, so you can see for yourself the differences from the standard version.
And if in the future, anyone’s wondering why in MySQL the length of a user’s postal address is limited to 200 characters, whereas in other databases it’s unlimited, now they shall know why. (It’s part of a primary key field.)
