Plugins compatibility checked

Japanese version is available in Pluginの互換性をまとめて検証した

I happened to read an article called "The reason I don’t recommend you to run WordPress with SQLite"(written in Japanese) reporting the results from using PDO for WordPress, which is very precise and impresses me favorably. Some people may say it’s selfish to complain about the plugin not maintained for more than two years. We, however, can easily find quite a few sites that recommend that plugin and give plausible instructions about the correction of the codes, so it’s very likely that one is inclined to try installing and using it. When he/she finds it useless, no one or nothing prevents him/her from throwing it away and trying another way. He/She is not to blame. That report written by Mr/Ms YHR is far from selfish and its compact description and clear logic shows the author is very intelligent and smart. It’s far more cogent than the articles that copy and paste the incomplete instructions.

BTW, the article "Using WordPress3.5 with SQLite"(written in Japanese) Mr/Ms YHR cites gives the list of the plugins that didn’t work with PDO for WordPress. I checked them with SQLite Integration. The list is below:

  • WordPress Popular Posts
  • Broken Link Checker
  • WP-PostViews
  • Better Delete Revision
  • Redirection
  • DB Cache Reloaded Fix

As for WordPress Popular Posts, see my post WordPress Popular Posts, and for DB Cache Reloaded Fix, I wrote in the documentation of SQLite Integration. I checked the other four.


Seeing the latest version(1.65), I can’t find the reason of the malfunction with PDO for WordPress. PHP puts out some notice messages: use of unrecommended function, undefined index of an array and undefined variable. They are not concerned with the database access methods.

I only installed, activated and checked the basic behavior(no problem). There may be a flaw or two I couldn’t specify. If you find one, please let me know. I count this plugin in the list of the plugin that works fine with SQLite Integration.

Broken Link Checker

PDO for WordPress can’t create the required tables. Even if you create them manually, it uses ALTER TABLE statements and SHOW query which PDO for WordPress can’t manipulate.

UPDATE query with JOIN statement is used only once in the upgrade function from 0.9.5, but I think this function will never be executed because current version is 1.8.2. So you don’t have to rewrite the code.

As for other queries, SQLite Integration can manipulate. I also count this plugin in the list of the plugin that works fine with it.

Better Delete Revision

It’s last updated 2010-10-22. I checked if it can be used with SQLite Integration, but I recommend you not to use it. You need to change some codes like below:

There’s DELETE command with JOIN statement. Not only PDO for WordPress but also SQLite Integration can’t execute it.

This statement will remove all the revisions without notice. Decisive and resolute, but if you want the latest revision to remain, don’t use it. Autosaved data will not be removed. SQLite doesn’t support DELETE with JOIN and table aliases, and can’t delete from multiple tables at a time. If you rewrite this statement, you need four separate statements like below. get_results() is meaningless, so I changed it with query() and without error trapping.

This plugin gives the utility to check the integrity of the database and optimize it with CHECK TABLE command, which is meaningless to SQLite. SQLite Integration gives the integrity information on the dashboard and can also do optimization there. You don’t have to rewrite the codes but removing them is enough.

In my personal opinion, when you use SQLite, you’ll do fine without this plugin. If you don’t want the revision, you will add one line in wp-config.php like below:

So, I don’t make a patch file for this plugin, and I count it in the list of the plugins that doesn’t work with SQLite Integration.


PDO for WordPress can’t create the required tables and manipulate ALTER TABLE statement. I came across one query below and checked what it does.

This statement creates INDEX to module_id column in redirection_groups table but it doesn’t specify the index name.

MySQL document ALTER TABLE Syntax doesn’t say that index_name is optional. We only see an example below in the users’ comments.

The documents doesn’t say what the index_name is in this case. So I tried it.

mysql> create table test (id int);

mysql> show columns from test;
| Field  | Type    | Null | Key | Default | Extra |
| id     | int(11) | YES  |     | NULL    |       |

mysql> alter table test add index(id);

mysql> show index from test;
| Table | Non_unique | Key_name | ...
| test  |          1 | id       | ...

The index_name is the same as the column name.

SQLite Integration doesn’t execute this type of statement properly. I changed the procedure to manipulate it as above.

Reading the codes further, I came across the index hinting in modules/log.php.

This index hinting is meaningless to SQLite, so SQLite Integration simply removes USE INDEX hinting. But not IGNORE or FORCE. I changed the regular expression to remove (USE|IGNORE|FORCE) INDEX(index_name).

Now, here we are. You can use Redirection without changing the code. Notice current released version(1.2.1) of SQLite Integration doesn’t have the modifications above. If you want to use Redirection immediately, please download Development Version from Developers page.

Google XML Sitemaps(3.2.9) patch

Japanese version is available in Google XML Sitemaps(3.2.9)パッチ

You’ve got to rewrite mysql_query() and some others in order to use it with SQLite Integration.

PDO for WordPress と Google Sitemaps Generator for WordPress(2010-05-23, written only in Japanese) reports workaround for PDO for SQLite plugin. But this patch is rejected for the newest version of Google XML Sitemap. I changed a little and newly created a patch file for SQLite Integration.->google-sitemap-generator_3.2.9.patch

Google (XML) Sitemaps Generator for WordPress Beta version (4.0b11 is the latest) works without patch file. And it doesn’t need static files and supports multisite installation and multilanguage. See the ChangeLog. I recommend this beta version.

Camera slideshow patch for SQLite Integration

Japanese version is available in Camera slideshow パッチ・国際化

When activated, database error is output in the PHP log file. MySQL or SQLite, the same message is seen. On MySQL we can finish activating, but on SQLite we can’t. And this plugin uses MySQL specific functions. So we’ve got to rewrite some codes to use it with SQLite Integration.

camera-slideshow/index.php and camera-slideshow/lib/camera_functions.php are our target files. First we rewrite index.php as follows:

We have no means to override PHP built-in functions like mysql_get_server_info() at least under standard server settings. We replace this function with $wpdb->db_version(), which returns the version number required by WordPress.

Next we rewrite camera_add_option() function in camera_functions.php whose definition is below.

I rewrote the line 19 and those that follow as follows:

This is the same as above, MySQL specific function. In this case, the return value of mysql_query() is not the same as $wpdb->query() and simple replacement is not enough. mysql_query() return resource when connection succeeds, false when connection fails, while $wpdb->query() returns the number of data (if there’s none, it returns 0), and false when error occurred. If the table exists and $wpdb->query() returns the number other than zero (more than one data is stored), this script execute die(). So I changed the if statement that follows.

And another change:

This global variable $current_user is empty, so I added get_currentuserinfo() function.

I also included the correction posted in the Forums Possible Bug With Pattern Overlay. This plugin is hard-coded in English language. I also made gettext work and pot file.

Patch file can be used in the dashboard of SQLite Integration. Languages folder in the archive must be placed in the folder WP_PLUGIN_DIR/camera-slideshow/. If you use WordPress with MySQL, this archive may help.

WordPress Popular Posts

Japanese version is available in WordPress Popular Posts(ja)

There are plugins that tells the visitors which posts or pages are most often viewed. Long time ago, every web page had access counter programs and showed the number of visits, which programs satisfied the vanity of the author. The WordPress plugins of this kind is the similar functionality, but they added a new feature which enables the number of veiws or comments in the list format. Visitors of your page can see which posts are frequently viewed and they will have more chance to be read. Surely the using of databases in the web sites made this feature possible. Now we can use the services like Google Analytics or Jetpack plugin provided by Automattic Inc. that is hosting Google provides the API and Jetpack provides the widget, which enable WordPress to show the page views and others. So we don’t have to use our own database to store them.

We have the plugins like Google Analytics Popular Posts, which uses Google API. But this time we check WordPress Popular Posts(aka WPP). WPP has the advantage of simple and easy use without other web site services.

  1. It is very likely that this plugin is no more maintenanced. We can’t see the Japanese support page and it has not been updated for more than two years.

Some users report that this plugis doesn’t work with Super Cache. This kind of plugins updates the data stored in the database, triggered by the dynamic screen transition. This feature makes it difficult for the plugin to function with the caching plugins. WPP has the two mode of updating database, one is to update the data triggered by the_content() function, which is normal mode, and the other to update with AJAX through $_POST[‘ID’] global variable, which is optional mode. This feature enables WPP to be compatible with caching plugins. But seeing My Thumbnails keep dissapearing after upgrade, there are some problems concerning caching plugins. Furthermore, WPP seems to work with qTranslate.

When you are using SQLite Integration, you can install and activate WPP without problem. But it doesn’t work as you expect. The reason is that WPP uses single quote for the column aliases just like below.

All the aliases of the column are quoted with single quotation. There are no back ticks(*1).

  1. You can use double quotation with MySQL in the ANSI mode and with Oracle. You can use square blacket with MS SQL Server or MS Access.

I made an experiment, using commandline utility sqlite3 (version 3.7.14).

sqlite> SELECT p.ID AS id, p.post_title AS title FROM wp_posts AS p;
id          title
----------  ----------
1           Hello world!
2           Sample Page

sqlite> SELECT p.ID AS 'id', p.post_title AS 'title' FROM wp_posts AS 'p';
id          title
----------  ----------
1           Hello world!
2           Sample Page

The same output. We get the same with double quotation. You can use square blacket, which is compatible with MS SQL Server, and back tick, which is compatible with MySQL. SQL As Understood By SQLite, SQLite Keywords reads: when used with single quote, SQLite interprets it as a string literal and when used in the place where the string literal is not permitted, it interprets as an identifier.

I tried on MySQL, using the commandline utility mysql.

mysql> SELECT p.ID AS id, p.post_title AS title FROM wp_posts AS p;
| id | title                 |
|  1 | Hello world!          |
|  2 | Sample Page           |

mysql> SELECT p.ID AS 'id', p.post_title AS 'title' FROM wp_posts AS 'p';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''p'' at line 1

mysql> SELECT p.ID AS 'id', p.post_title AS 'title' FROM wp_posts AS p;
| id | title                 |
|  1 | Hello world!          |
|  2 | Sample Page           |

We can’t use single quote to the table aliases. The same output when using double quote. You can use back tick to the table aliases.

The results shows that the code of WordPress Popular Post seems to be no problem. It IS no problem, indeed. But when using SQLite Integration, the results are not the same. First try: without quote.

The result is like below, as you expected.

    [0] => Array
            [id] => 1
            [title] => Hello world!

    [1] => Array
            [id] => 2
            [title] => Sample Page

With quote.

We get the result, empty array.


This is from the manipulation of the single and double quote of SQLite Integration. SQLite Integration emulates PDO::bindValue, replace the quoted strings with placeholder ":param_number" and stores those strings in an array. After that, it execute PDO::prepare(). When the quoted strings are in the proper place, it returns PDOStatement object. And you can execute PDOStatement::execute() with that array and get the proper results. See more details in PHP Data Objects. I’ll give one example.

In the place :param_0 and :param_1 was a string value like ‘Hello world!’. This feature seems to be implemented originally for that reason below.

  1. It can avoid dangerous input data from the users, manipulating them before executing SQL statement(*1).
  2. PHP can cache the SQL statement and execute faster only with the replacement of the data.
  1. $wpdb::prepare() method is the similar feature. It escapes the data with addslashes() or mysql_real_escape_string()[not recommended in PHP5.5.0] functions.

The bad example has :param_0, :param_1 in the wrong places, which causes PDO::prepare() to throw an exception. SQLite Integration catches this exception and handles it. If you set $wpdb->supress_errors to false and $wpdb->show_errors to true, you can see the error messages, which isn’t enabled by default.

There’s no workaround way of avoiding this. So if you want to use WordPress Popular Posts with SQLite Integration, you have to rewrite it. There’s a patch file that includes minor fix(*1).

  1. I reported to the author Héctor. He will fix it in the next release.

As you see in readme.txt, WPP can collaborate with WP-PostRatings, which can be installed and activated normal way.