SQLite Integration

Last updated May 02, 2014 JST
Japanese version is available in SQLite Integration(ja)

For a Small or Medium Size Website Developing

Thank you for visiting SQLite Integration Site! SQLite Integration is a WordPress plugin that enables WordPress to run with SQLite database. It’s best fit for small or medium size websites.

I have tested on a multisite for some months and found no serious problems. But I have no information about mega size traffic environment. On a multisite, you must have capability of manage_network_options to see the utilities page. Try activating this plugin.

Index

click to hide index

Current Status

Version 1.6.1 is released. Download from WordPress Plugin page. Installation tested under these environments for WordPress 3.9

If you come across the different behaviour from the documented one, either the documentation or the implementation is wrong. In that case, please let me know about it.

OS Server PHP version PHP mode SQLite Extra
Debian 7.1 (wheezy) Apache 2.2.22 PHP 5.4.4 CGI/FastCGI, suPHP 3.7.13 Local server
FreeBSD 9.1-RELEASE-p9 Apache 2.2.25 PHP 5.4.11 CGI/FastCGI 3.7.7.1 Sakura Internet Hosting Service
Windows 7 Apache 2.4.4 PHP 5.5.3 Apache module 3.7.7.1 XAMPP

Disclaimer

Please read Term of use.

WordPress.org doesn’t support WordPress with SQLite. So if you post the questions to the general Forum, you might have few chances to get the answers. Please use Support Forum. If you don’t understand the contents here, I strongly recommend you to change your server to the one with MySQL(*1). Or else you can do other ways like:

  1. This is the easiest way.
  2. This is the second easiest way.
  3. I don’t know if this is the third easiest way, bu you can use SQLite for the database.

System Requirements

Your server must have:

  • PHP 5.2.4 or newer. 5.3 or newer is recommended.
  • PDO library
  • PDO sqlite driver

Install

Things to be needed

Preparations(Basic Settings)

If you access your sever via FTP

Follow these steps.

  1. Download WordPress archive and SQLite Integration archive and expand both to your local machine.
  2. Move sqlite-integration folder to wordpress/wp-content/plugins/ directory.
  3. Copy db.php file in the sqlite-integration folder to wp-conent folder. Your directory tree will look like this.
    • wordpress
      • wp-admin
      • wp-content
        • languages
        • plugins
          • akismet
          • sqlite-integration
        • themes
        • upgrade
        • uploads
        • db.php
        • index.php
      • wp-includes
      • wp-config-sample.php
  4. Rename wordpress/wp-config-sample.php to wp-config.php and some editing. Editing point is below. You can get Authentication Unique Keys from https://api.wordpress/secret-key/1.1/salt as the comment says. Access this address with your browser and copy what you get here. You are recommended but not required to change the table prefix.

When you finish editing, upload all the directory to your sever via FTP client. You might change the name of wordpress folder to what you like. If you want to install to your root directory, upload all the contents in the wordpress folder to your web root.

If you have shell access

You can finish preparations on the server. Below is the example for installing to "userdir/www/wordpress" or "userdir/public_html/wordpress". You might change the wordpress directory to what you like or to root directory.

$ curl -O http://wordpress.org/latest-ja.tar.gz
$ curl -O http://downloads.wordpress.org/plugin/sqlite-integration.1.5.zip
$ cd [www|public_html|etc...]
$ tar zxvf ../latest-ja.tar.gz
$ cd wordpress/wp-content/plugins
$ gunzip ../../../../sqlite-integration.1.5.zip
$ mv sqlite-integration/db.php ../
$ cd ../../
$ mv wp-config-sample.php wp-config.php
 [vim|emacs] wp-config.php

Editing point is the same mentioned above. It will be easy if you use the functionality of vim or emacs to add the command output to the buffer screen.

How to install in 5 minutes

I don’t know from where to count. Next installation process will not take as much as 5 minutes.

  1. Open your browser and access the server directory that WordPress is in.
  2. Edit your blog name etc. on the install screen.
  3. Click install button.

That’s all. Enjoy your blog.

Optional Settings

You can try some optional settings.

If you use both SQLite and MySQL

You can use MySQL without uninstalling SQLite Integration by adding the next line to wp-config.php.

Of course, this isn’t enough. You must set your database server, user name, password and all. See ordinary Installing WordPress.

When you add the line above and access your site with your browser for the first time, the install process will begin. You have to install MySQL database. As you know, the data in SQLite is not migrated automatically. And patched plugins won’t work properly with MySQL, so you can’t use them on both MySQL and SQLite.

If you change the database to SQLite again, change the line as below or remove the line from wp-config.php.

If you change your database file name or directory

SQLite Integration creates "wp-content/database/" directory and puts the database file ".ht.sqlite" in that directory. You can change this directory and file name. To change the directory, add this line in your wp-config.php file.

To change the database file name, add this line.

You can change either of them or both of them.

Installation Notice

You will have hardly any problem. I’m testing the installation only on Apache web server, so I have no information about the other server softwares. If you have a problem , or if you succeed or fail, please let me know about your environments.

The program sets the database directory permission to 0707 in the install process, so, even if your PHP is running under the web server’s user privilege, the installation will succeed. But it depends on the parent directory permission setting. If you come across the error from PHP mode or permissions, let me know. Your PHP is running suEXECed, you can change that directory permission to 0600 manually.

*
*  *

Overview

This part is a little technical. Don’t mind if you understand.

Basic ideas and limitations

SQLite Integration is a wrapper program that works as follows:

  1. Intercepts the SQL statements for MySQL from WordPress
  2. Rewrites it as SQLite can execute
  3. Gives it to SQLite
  4. Gets the results and change it into the format MySQL returns if necessary
  5. Gives back the result to WordPress

This plugin repeats them for every requests. WordPress will work with the returned result.

Basically both MySQL and SQLite have the same functionality. But MySQL is a very big system and has many extended features, and SQLite is a very small and has much less features. SQLite can’t do all that MySQL can do. So sometimes the SQL statement needs rewriting and sometimes not.

SQLite Integration doesn’t provide another layer for database access to conceal this difference. If users were able to override the methods of WordPress to create SQL statements, this plugin would work much safer and faster. Unfortunately, WordPress’ methods are not abstracted enough to provide such APIs. So SQLite Integration only do the job by rewriting the already created SQL statements with regular expressions and substituting functions. Although PHP doesn’t allow users to rewrite the native functions, WordPress doesn’t use such native functions much and this plugin can rewrite almost all the functions. But not a few plugins prefer such functions to APIs provided by WordPress, which means that they might not work well with SQLite Integration.

As for plugins, I’ll mention later. If your plugins have problems, please post to Support Forum or leave a message on this site. You couldn’t be sure I’ll respond right away or suggest the fixes, but it’s 1 that SQLite Integration is improved by the users’ reports and suggestions. I’ll be glad to receive your message.

About WordPress database

PHP 5.5.0 made MySQL library deprecated. Current substitue candidates are MySQLi or PDO library.

As far as I know from Subversion repository, WordPress will use MySQL library. It suppress the E_DEPRECATED error output, so even if you use PHP 5.5.x, you won’t have error log about the library. But in near future, they will have to use MySQLi or PDO. I don’t know which. Some plugins experiment these library: WP DB Driver or MySQLi database layer. The former uses PDO, and the latter MySQLi.

"Why doesn’t WordPress use MySQLi, which is more modern and provides much more features?" Sometimes such posts are seen in the forum. MySQLi is not faster than MySQL as some people say, but it’s 1 that it enables users to use available new features of MySQL. It’s worth while to migrate to MySQLi library. But such an easy thing, I’m sure, must core WordPress developers know well enough. There must be some reasons for them not to migrate right away.

WordPress is a rather conservative system. Its maintenace policy is like this: even if they add a new feature, users don’t have to worry about it and keep using without care. For example, when they added a multisite blog support, which contained the change of the database schema, users who updated didn’t care about that and kept using without a big problem. But the library change will affect a large number of plugins and themes, because many of them do not use WordPress APIs but PHP native functions. Just changing the library in the core WordPress program will be easy, but they care for other plugins and themes.

SQLite Integration might not work properly in the future, which partly depends on the WordPress team’s decision. I will try to follow the latest changes of the core WordPress, but there might be a case I couldn’t catch up with it. In that case, I’ll announce it on this site.

Plugin Compatibility

Currently you can’t use these plugins or need the rewriting of the code.

Plugin Name Compatibility Reasons Workaround
Optimize Database after Deleting Revisions No MySQL function rewriting code(*1)
W3 Total Cache No db.php file conflict None
DB Cache Reloaded Fix No db.php file conflict None
HyperDB No db.php file conflict None
Google XML Sitemaps Yes MySQL function rewriting code(*2)
Camera slideshow Yes MySQL function rewriting code
MailPoet Newsletters (formerly Wysija) Yes MySQL function/column name/SQL rewriting code
Yet Another Related Posts Plugin No FULLTEXT index None
Better Related Posts No FULLTEXT index None
NextGen Gallery Yes MySQL function rewriting code
NewStatPress Yes MySQL function rewriting code
WordPress Popular Posts Yes SQL statement rewriting code
  1. I don’t think you will need this plugin, because SQLite Integration has some utilities of the same kind. I don’t provide the patch file.
  2. Beta version (current version: 4.0 beta11) works fine without rewriting codes. This version also supports multisite and multilingual web sites. See also Google (XML) Sitemaps Generator for WordPress.

Patch Files

A patch file is a collection of the differences between original source codes and rewritten ones. If you apply it to the original codes with "patch" command, you can automatically rewrite the codes.

I created patch files for some plugins that you can’t use with SQLite Integration. Please download them from Plugin Page. You must accept these limitations.

  1. Once your plugin patched, it is very unlikely that you should have support from the plugin author(s). Even if you asked in the support forum, no one could answer, because they never know what changes you applied to the plugins.
  2. I do not provide any warranty of the patch files and the patched plugins.
  3. I will not be liable to make the patch file up-to-date or compatible with the newest version of the plugin.

Microsoft Windows doesn’t have "patch" command. You can get one from Patch for Windows, but this binary is checked by User Account Control (UAC) of the operating system from Windows Vista to Windows 8. To avoid this, you’ve got to embed the manifest to the patch.exe. See also Using “patch” from the GnuWin32 project on Windows 7.

But you need mt.exe included in the Windows Development Kit (SDK) for that. Installing SDK is too heavy a job, so I made a manifest embedded binary. If you need, download from here. This patch.exe terminates abnormally when it uses patch files with UNIX newline(LF) character. Please use them after converting LF to CR+LF.

Patch files are applied as follows:

patch -p1 < path/file.patch

If you have git, you can use it.

git apply path/file.patch

If you have Eclipse, you can use it.

  1. context menu » Team » Apply Patch
  2. Select File, click Browse... button, select the patch file and click Next>
  3. Select the plugin folder and click Next>
  4. Check the file contents and click Finish

As it is a little difficult for newbies to use a character based command, I included in the plugin archive the utility for patching plugins on the server. After you finish installing, try activating the plugin and visit the plugin setting page on the admin panel.

How to make a patch file

You can make a patch file yourself. Check if your rewritten plugin works without error or notice (set error_reporting(E_ALL) and check). If you use the utility in SQLite Integration, you must follow the naming convention below.

  • The name must begin with the plugin's directory name(not the plugin name).
  • One underscore and the version of the plugin must follow.
  • The extension must be .patch.
  • When using diff, use "-Naur" option.

For example, if you make a patch file to "Debug Bar", "debug-bar_0.8.patch" is the file name. The utility script takes "debug-bar" for the target directory and "0.8" for the target version. If the version number doesn't match the installed plugin, the script will give out the error message and stop executing. The different patch file name will not be executed, either.

Notice about patched file: If you don't know where the patch file comes from or who made it, make sure to check the contents of the file. Chances are a malicious codes created by the malicious user will be executed on the server and may damage your site. WordPress plugin can do anything your PHP can do. I give no warranty at all.

Substitutions

This table lists the plugins that give you the similar functionality of the incompatible plugins. Note that this is not my recommendation. I don't use most of them in fact. Please try yourself. If you know better choices, please let me and other users know.

Plugin Substitution
Yet Another Related Posts Plugin Related Posts, WordPress Related Posts, etc.
Better Related Posts Related Posts, WordPress Related Posts, etc.
W3 Total Cache WP Super Cache, Quick Cache, etc.
DB Cache Reloaded Fix WP Super Cache, Quick Cache, etc.
HyperDB WP Super Cache, Quick Cache, etc.

For the PDO for WordPress users

If you are using PDO for WordPress, your database doesn't have wp_commentmeta table. It causes Akismet plugin not to work properly. First, you must create this table. But it requires you to manipulate the database directly, which is a hard job for an ordinary user. I wrote a PHP script to create this table. Save the code below to a file create_table.php, and put it in the WordPress directory. Accessing this file with your browser will do the job. This script doesn't use WordPress functions but only needs PDO library, so your server must execute it without problem.

If your database already has wp_commentmeta, this script does nothing and finish. After you see success message, remove this file from your server.

Now you have all the tables WordPress requires. Follow the next steps.

  1. Export the data from the old WordPress
  2. Install latest WordPress with SQLite Integration
  3. Install WordPress Importer and import the data

If this fails for some reasons (exporting or importing fails), try the next steps.

  1. Back up wp-content/database/MyBlog.sqlite and wp-content/db.php file
  2. Install SQLite Integration via FTP or from your admin panel
  3. Open wp-config.php and add the next line
  4. Overwrite wp-content/db.php with db.php included in SQLite Integration

API reference

In preparation. I'll publish along with the next release.

Appendix1: Short History of the Predecessor

There has been a WordPress plugin called "PDO for WordPress", thanks to which we could use SQLite for the database of WordPress while WordPress itself doesn't support any other database system than MySQL. You can still download the archive file and see the program codes, which will let you know the intentions or ambitions of the author, Justin Adie.

Justin hoped everyone could use any database system s/he likes to create a website with WordPress. He knew the PDO library of PHP (experimental implementation at that time) and it seemed to give him a chance to manpulate many of the database systems with one interface. He read the documents(*1), began to write some codes and published it. And for some reason we don't know —unfortunately enough— he abandoned the project and his plugin hasn't been maintained for more than two years. Of course everyone has freedom to deveope softwares or stop developing softwares.

And now, a bunch of codes is left behind, a modest but 1 gift.

Web Searching gives you some information about the idea of using PDO for WordPress. For example, there was a site (in Japanese) SQLite orientated PDOnizing of WordPress (a challenging project)(2006-03-14), which article a person named jpadie commented saying: I wrote a driver (2007-11-13). This jpadie was Justin himself. When PHP began to support PDO library, some of the programmers might feel the hopeful future of the PDO library. The situations, as Justin wanted them to come, was fulfilled by some other CMS's like Drupal, which based on the PDO library and supports SQLite, Oracle, MS SQL Server or some others as well as MySQL. As for WordPress, after the discussion I mentioned in the note 1, it has been developed only with MySQL.

I came to know Justin's project in 2012. When my hosting service provider Sakura Internet began to support PHP for the lowest price hosting server users, I searched on the web which CMS is a best choice for my server which doesn't have a database server system like MySQL or PostgreSQL. I already knew WordPress and had used to create some websites with it. It's 1, WordPress is much easier to install and maintain than Drupal or Joomla!. But one of the weakest point of WordPress is that it too much depends on MySQL.

First I tried to use PDO for WordPress, but it couldn't be installed with the latest version of WordPress. You can install with much older version and upgrade to the newest. But even so, I checked the database schema and found a table (prefix_commentmeta) wasn't created. And some plugins that create tables in the database couldn't be activated or work fine. Some emitted error messages and others didn't. Not a few bloggers reported the same malfunctionings and the same workarounds. Some are correct and useful, and others are incorrect and misunderstood. But no one ever explained the cause of the failure of the installation with the latest WordPress.

So I read the documents and codes of PDO for WordPress myself. At the same time I installed Drupal on the server —needed some fixing of the code but worked perfect—, and read its database manipulating codes because Drupal uses PDO library for the database manipulating abstract layer.

As far as I know, there are good reasons for the malfunctions of PDO for WordPress. Simply put, it has been outdated. It hasn't been maintained for more than two years, during which period WordPress has been greatly changed.

  1. First, the installation process was changed.
  2. Secondly, WordPress newly began to support Multisite. It caused the change of the definition of table schema and manipulating functions.
  3. And at the same time more MySQL specific SQL statements came to be required.

PDO for WordPress couldn't catch up with these changes. This is the way many of the no-more-maintained softwares took. It's almost inevitable(*3). In a way, the prevailing softwares are all survivors. But as every schoolboy knows, to survive is not the aim of the free software but to bring some benefits to the whole community.

So I began to fix the PDO for WordPress. Along the way I decided to omit the codes for supporting the other databases than SQLite. For MySQL, we don't need a wrapper program. For PostgreSQL, there's another plugin. Oracle or MS SQL Server is much more akin to MySQL than SQLite is, but many of the WordPress users don't have such a server as those databases are running on. All this means that I give up Justin's first intention, so I changed the plugin's name to another one, and newly published.

  1. PHP manual said: this is an experimental implementation. Now the word "experimental" was removed. PHP manual keeps saying it provides a data-access abstraction layer, not a database abstraction. At the time of writing this, MySQL library is deprecated from version 5.5.0 and MySQLi or PDO library is recommended instead.
  2. Using Alternative Databases is mentioned in readme.txt. And there's a discussion PostgreSQL in the Forum. It is not certain whether or not a preson named "rather curious" is Justin.
    That discussion dated 9 years ago, when PHP 5.0.0 was about to be released or just released. Database abstraction was one of the topics. Looking back now, this discussion is out of date. PHP 5.0.0 was released in July, 2004, PDO for WordPress was first released in November, 2007.
  3. Some of the bloggers call this "bug", but as I say for the honor of the author, it is not from the programming errors but from the absence of the maintenance. If you say it is foolish to do the thing unsupported by WordPress.org, I'll agree this is it.

Appendix2: About donation

Readme Validator advises me that there's no donation link. Donation from person to person by using Paypal or similar service is prohibited by the law in Japan. According to that law, to receive money from the users, we've got to sell something: material objects, source codes or some kind of service...

I'm not selling a software, so no donation button. If you do want to donate, send me a check or give me a job instead :-) Thank you.

22 thoughts on “SQLite Integration

  1. Pingback: Anonymous

  2. Cyprien

    Hi,

    thanks for the plugin. I tried to use NewStatPress but it failed to create the table. I found that a patch was required, applied it manually, but it was still not working. Looking at the code/debugging I found that the call to strip_backticks() is done before the one to rewrite_field_types(), which defeats the purpose of the backticks ;-) I put it right after and it worked :)

    Cheers

    Reply
    1. charlus Post author

      Hi, Cyprien.

      Thank you for reporting.

      NewStatPress doesn’t use backticks for the query string to create table. I think the table name ‘time’ is misinterpreted and I should change the index to KEY which is required by dbDelta() function. So I made a patch file. Will you try http://dogwood.skr.jp/tmp/newstatpress_0.8.4.patch?

      Reply
  3. Pingback: WordPress на SQlite Код это поэзия.

  4. Pingback: Blogのソフトウェアを変更しました | Kawauso's Blog

  5. Pingback: Una de enlaces (III) | Bitácora de Javier Gutiérrez Chamorro (Guti)

  6. Killian Grant

    This plugin has been working flawlessly, but I’ve run into a bit of an integration problem with Gravity Forms. Gravity Forms checks if a MySQL database exists and is in use. If it doesn’t find one, you cannot save any forms. Is there a workaround for this?

    Reply
    1. charlus Post author

      Thank you for reporting, Grant. Will you try the development version? You can download it from Plugin Directory. Overwrite you installed plugin with the development version via FTP.

      I’m afraid you may need to rewrite some SQL statements in Gravity Forms. If you have still problems, please let me know again.

      Good luck.

      Reply
  7. Pingback: Alien Pastures » New SQLite driver for the blog

  8. naillik

    Is there a way to toggle the use of the plugin in wp-config.php? For instance, if I am testing locally, use this plugin; else, use MYSQL?

    Reply
    1. charlus Post author

      No problem, you can do what you want.
      Open wp-content/db.php with your favorite editor, and copy and paste the next line at the top of the file.


      if ( defined('USE_MYSQL') && USE_MYSQL == true ) return;

      And add the next line in wp-config.php.


      define('USE_MYSQL', true);

      If you want to use the plugin again, just comment out the line or change ‘true’ to ‘false’.
      Good luck.

      Reply
  9. cryptonomikon

    This worked for me. Thanks!

    I think the installation instructions could be clearer (had to figure it out a bit). I could help rewrite the instructions. Please contact me.

    Reply
    1. charlus Post author

      Thank you for your suggestion.

      Which part do you think is ambiguous or hard to read?
      My Japanese is also said to be hard to read. I’ll be
      glad you should check my Japanese as well. :)

      Reply
  10. Pingback: WordPress: instead of MySQL, why not SQLite? : NightlyArt

  11. Chris_Edwards

    Great plugin I want to try it out. But I have a few questions.

    How well does this scale let say if my medium size blog becomes much larger? Could it support 5k or even 10k in posts, etc…?

    How about security? WP has a lot of issues with that at times but I don’t know about SQLite

    Reply
    1. charlus Post author

      Thank you for your comment, Chris.

      If you want SQLite to store 10K articles, it will be no problem, I guess. I experimented on 100K articles’ insertion by the program using WordPress database API. Database file size became 106MB. It worked without problem. Theoritically, the maximum size of SQLite database file is 140TB (see http://www.sqlite.org/fileformat2.html). Practically, I imagine, some hundreds MB will be OK.

      But please remember that SQLite is one of the fastest in reading data from database but rather slow to write data into database. So I don’t recommend that you use it as a site where a lot of users write articles at the same time.

      As for security, robustness from SQL injection attacks is the same as MySQL because it’s from the WordPress database API. If security matters in SQLite, it will come from the fact that SQLite database is just an ordinary file on the disk system.

      On default setting, your database file is put into the directory like below:

      /home/user/public_html/wordpress/wp-content/database/.ht.sqlite

      Users’ browsers can access this directory. Apache’s default setting prevents visitors from accessing the file whose name begins with .ht, and SQLite Integration put the .htaccess file that contains “DENY FROM ALL” directive while installation process and prevent visitors from directly accessing this database directory. If you don’t use Apache as a web server but some others like Nginx, they may not support the .htaccess file for access restriction. So I think you’d better set the server to restrict access to the database directory. Without that, you may let the visitors freely download database file!

      I recommend the settings as below (if your server allows you to do this):

      WordPress => /home/user/public_html/wordpress
      SQLite DB => /home/user/some_other_directory/your_database_file

      This will keep the visitors from accessing your database file on any Apache settings.
      Generally speaking, attacks to WordPress site target the files below:

      1. old timthumb.php
      2. login.php
      3. xmlrpc.php

      Always update to the newest version of WordPress, set the strongest password and don’t use the login name that one can easily imagine will save your site.

      Reply
      1. Chris

        Thank you for your detailed answer. I wanted to ask if you have seen any issues with this running on the WordPress 3.8 and future updates they have coming up.

        Reply
        1. charlus Post author

          Thank you for your comment, Chris.

          When using 3.8, no problem. When WordPress 3.9 is released, I’ll update this plugin soon after that to make it full compatible.

          Reply
    1. charlus Post author

      Thank you for your comment, Norbert.

      I downloaded and checked NextGen Gallery.
      If you can, will you try the development version? (from http://wordpress.org/plugins/sqlite-integration/developers/)

      Changed files are “pdoengine.class.php” and “query.class.php”, so replacing these two files will let you check if it works or not. I’ll be glad if you let me know the result.

      Your comment and report is useful and encouraging for me. Thank you again.

      Reply

Leave a Reply