Category Archives: WordPress

SQLite Integration 1.1 released

Japanese version is available in SQLite Integration 1.1 リリース

I updated SQLite Integration. One of the users posted an error report to the support forum and I looked into it.

The cause was this: FeedWordPress plugin adds a function to shutdown hook, and when the script finishes executing, that function is called. SQLite Integration adds destruct() function to the same hook, which causes the database connection is terminated everytime a page is loaded. I fixed this problem.

While I was reading the codes of FeedWordPress plugin, I found this plugin create and drop index to a table. I came across an idea long forgotten that there is a difference about DROP INDEX query between MySQL and SQLite. Reviewing my plugin, I noticed the query for MySQL is not rewritten and passed as is to SQLite. I also fixed this procedure. The document of FeedWordPress says this index will make faster the behaviour of this plugin, but probably you’ll never have such an effect, I’m afraid.

Further reading of the codes showed me that this plugin uses LOCATE() function in the query, about which I added a user defined function.

The last two are rather minor problems. But the first is more important a matter, so I recommend you to update your plugin right away.

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 WordPress.com. 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.

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

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

With quote.

We get the result, empty array.

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.

2 / 212