Ever since this blog’s inception I have used a plugin called “PDO for WordPress” which allowed me to have a SQLite (file-based) database instead of the default MySQL database used by WordPress.
There are a couple of bugs in PDO for WordPress that I was getting tired of, and perhaps some of the people writing comments have been annoyed too, when their post failed to show up on the blog. Most notorious bug was that any text containing the two characters ) and ‘ immediately after eachother would silently be discarded by the driver… the only way to get your precious text back was to hit your browser’s “Back” button and try to find out what was wrong with the text.
The PDO for WordPress plugin has not been maintained for a while, mostly because the development pace of the WordPress code was faster than the PDO for WordPress author could manage. Some of the bugs were addressed with patches, but these were never incorporated into a new release.
I was finally so irritated with this that I was going to attempt to apply these patches to my own blog. Luckily, I did my research properly and I ran into a new plugin that can replace the old PDO for WordPress plugin: it is called “SQLite Integration“. This plugin uses PDO just like the old one.
Remember, PDO stands for PHP Data Objects. Quoting the PHP manual: “PDO provides a data-access abstraction layer, which means that, regardless of which database you’re using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn’t rewrite SQL or emulate missing features“. This is what allows the use of a different database than MySQL and at the same time is the cause of the bugs in my blog – there are incompatibilities in the way WordPress creates SQL queries (targeted at MySQL syntax which does not always work for SQLite).
Interesting enough, the discussion among the WordPress developers about the possible use of PDO has been revived now that the mysql_* functions are officially deprecated in PHP 5.4.
This new SQLite Integration plugin will work with versions of WordPress starting with 3.3. My blog is at the latest 3.8 release so that is OK. Thus I proceeded to do the installation of the plugin and re-configuration of the blog, following the instructions (I always RTFM before applying irreversible changes – and make a full backup too 😉
As you can see, the blog is still here. I have not checked whether the text entry bugs have gone now, but at least I am running on a well-maintained database driver again.
Eric
It is not surprising that the query syntax for MySQL and SQLite differ, as SQLite tries to adhere to a subset of standard ANSI SQL, while the MySQL developers (neither the original ones nor Oracle) had in mind to strive for compatibility or standard adherence.
One reason why I like PostgreSQL, and the Java databases H2 and HSQLDB is, that they mostly stick to SQL-92 ANSI standard syntax. This means, you can grab an SQL textbook and most of what you read in it will work the way as described or not, at all. It will not behave just differently.
So let’s see, what the new driver is capable to achieve. Here is DANGER )’. 🙂
And, of course: HAPPY NEW YEAR, ERIC!