- Toby Inkster
- sql; mysql; demiblog
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.
varchardatatype requires a maximum length to be specified. If you do not want to specify a length, then you must instead use MySQL's
textcannot be used as a primary key column; and
textcannot take a default value.
- Seemingly, only one
timestampcolumn 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 zonemodifier for
timestampcolumns 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.)