PDO Powered

 

Introduction:

WordPress supports only one database type as its backend: MySQL. But using MySQL is not always possible or desirable – some people will not have access to a MySQL database account, or they prefer to have a fully portable blog which is self-contained in a single directory (easy to backup!).

A plugin called “PDO for WordPress” allows you to use a SQLite (file-based) database instead. Unfortunately this sqlite plugin (and it is the only one of its kind) is not 100% compatible with some of the WordPress routines. As a consequence, versions of WordPress 3.0 and newer make it difficult to create a new blog with sqlite database.

Difficult, but not impossible. This page shows you how to setup a new blog with a SQLite database.

You must have access to (preferably) an Apache webserver. This webserver must have PHP script support enabled. You will need to ensure that SQLite has been installed on your server as well. Shell access to your server is preferred but not required.

For generic installation questions, please visit the WordPress support pages http://codex.wordpress.org/Installing_WordPress . Note that you can skip all the steps that deal with setting up the MySQL database and user. My own article describes the additional instructions required for your SQLite backend.

Instructions:

The following installation procedure has been tested for WordPress Version 2.9.2. You will be able to upgrade to the most recent version of WordPress after the initial configuration of your sqlite-powered blog has completed.

  • Extract the two downloaded archives:
$ tar xvf wordpress-2.9.2.tar.gz
$ unzip pdo-for-wordpress.2.7.0.zip
  • You should now have a ‘wordpress’ directory and a ‘pdo-for-wordpress’ directory. In the ‘pdo-for-wordpress’ directory you will find a ‘pdo’ subdirectory and a ‘db.php’ file. Copy those into the ‘wp-content’ subdirectory of the ‘wordpress’ directory:
$ cp -a pdo-for-wordpress/pdo wordpress/wp-content/
$ cp -a pdo-for-wordpress/db.php wordpress/wp-content/
  • Create a new directory named ‘database’ in the ‘wp-content’ subdirectory of the ‘wordpress’ directory. Set the file permissions of the ‘database’ directory to 777. The PDO plugin will create the database in there with the hard-coded name ‘MyBlog.sqlite‘. The mode “777” will ensure that your web server will be able to create and update the database.
$ mkdir wordpress/wp-content/database
$ chmod 777 wordpress/wp-content/database
  • Rename the ‘wp-config-sample.php’ file located in the ‘wordpress’ directory to ‘wp-config.php’.
$ mv wordpress/wp-config-sample.php wordpress/wp-config.php
  • Open the ‘wp-config.php’ file renamed above in an text editor.
  • Add the following line
define('DB_TYPE', 'sqlite'); // use sqlite

right after the lines

/** The Database Collate type. Don't change this if in doubt. */
define('DB_COLLATE', '');
  • A bit further down in the same file, replace the lines
define('AUTH_KEY', 'put your unique phrase here');
define('SECURE_AUTH_KEY', 'put your unique phrase here');
define('LOGGED_IN_KEY', 'put your unique phrase here');
define('NONCE_KEY', 'put your unique phrase here');
define('AUTH_SALT', 'put your unique phrase here');
define('SECURE_AUTH_SALT', 'put your unique phrase here');
define('LOGGED_IN_SALT', 'put your unique phrase here');
define('NONCE_SALT', 'put your unique phrase here');

with your own unique set of keys. You can generate a set of random keys online: browse to the URL https://api.wordpress.org/secret-key/1.1/salt/ and copy the page content into wp-config.php.

  • First stage is complete! Now we are going to make the blog files accessible through a webserver. Copy the ‘wordpress’ directory structure into the DocumentRoot of your webserver, or upload it to a remote server. See the WordPress installation guide for more help about that. You can also rename the ‘wordpress’ directory to something like ‘blog’ if your WordPress blog is not going to be the root directory of your webserver.
  • Open the URL http://yourdomain/wp-admin/install.php in a web browser (substituting your server name and possibly a directory name for “yourdomain” of course) and kick off the WordPress installation procedure.
  • Enter a friendly name for your new blog, and the email address which will be associated with the “admin” user account. Then click the ‘Install WordPress‘ button
  • You might see a lot of PHP code being written to your browser window. You can safely ignore this. Scroll to the bottom of the page and wait patiently.
  • Write down the randomly generated password for user ‘admin’ which will be printed at the bottom of the page below the line that says “Success!” when the WordPress installation has completed. Then click the ‘login’ link located at the bottom of the page.
  • Enter the “admin” username and the password you just wrote down. You should then be taken to the WordPress Configuration Panel.

Congratulations! You have a working WordPress blog which is PDO Powered. Now comes the next step: upgrading to the latest version of WordPress. The upgrade process will be painless. Just foillow the instructions on http://codex.wordpress.org/Upgrading_WordPress.

When that is done, you can dress up your blog the way you like it.

Fast track:

If you want to be up and running in no time, you can grab this tarball of an empty WordPress blog, which was setup using the above instructions and containing only one published article… this one!

  • Extract the tarball to your webserver’s DocumentRoot and change ownership of the “pdopowered” directory structure so that your webserver account (apache or httpd) can at least write to “./pdopowered/wp-content/database” and everything below.
  • Access to the blog will initially only be possible through the localhost in order to prevent external visitors to mess it up. You need to visit http://localhost/pdopowered/ with a webbrowser running on the server. Login with user “admin”, password “pdopowered”.
  • Set a new password for user “admin” and define a proper URL for your blog in the “Settings” module. This will allow external access to the blog pages.
  • If needed, upgrade to the latest version of WordPress, and start dressing up your new blog!

Good luck! Eric

27 thoughts on “PDO Powered

  1. Pingback: Alien Pastures » Using SQLite for this blog

  2. Pingback: Wordpress ve Sqlite3 Veritabanı » Çaylaks Blog



  3. Hi,

    I have modified the code mentioned here so that it works with a new install of wordpress 3.4.1

    I would appreciate it if people could test it.


  4. Hi Tino,

    Do you have these updated instructions / code somewhere public so I can link to them, or do you want me to try it out and add it to this blog page (with due credits)?

    Eric



  5. Pingback: Install Wordpress dengan SQLite « Kurnia Ramadhan

  6. Pingback: Install WordPress dengan SQLite | Hanya Karangan

  7. Hi everyone =)
    I tried what Tino posted, using wp 3.4.2 and works perfectly. Just be careful if you use plugins like “better wp security”, it has a function that modifies the content folder and it can lock you out immediatly. Also, if you change the table prefix, you will have to go through the install process again and you will have extra unused tables in your database.
    A question, if I want to move my site, do i have to change the url in the settings module to the new site’s, previous to the back up?


  8. works fine for me http://hussam.eu.org

    but I get a wp-content/database/debug.txt file full of

    [code]Line 431, Function: prepareQuery, Message: Problem preparing the PDO SQL Statement. Error was near “7”: syntax error
    Line 431, Function: prepareQuery, Message: Problem preparing the PDO SQL Statement. Error was near “15”: syntax error
    Line 431, Function: prepareQuery, Message: Problem preparing the PDO SQL Statement. Error was near “15”: syntax error[/code]





  9. Pingback: Wordpress ve Sqlite3 Veritabanı « ÇaylakS Blog

  10. Hi,

    The code should be available again, if not as Andy has kindly provided there is a diff of the changes above. I use tab spacing set to 4 if that helps, Andy.



  11. hello again, thanks for your code. However as I expected for some bumps along the way I got in a problem. I used wordpress on my raspberry pi and set it up successfully. But after restarting my pi i have an error that an connection to the cannot be established and found the debug.txt in the database folder full of errors saying tables are missing. I am using latest version of wordpress. Any ideas?


  12. Pingback: Rsync ile yansılama | ÇaylakS BloG

  13. Pingback: PDO Powered By Sqlite - How to setup your sqlite powered blog


  14. Pingback: tapion.it » Blog Archive » Server con un RaspberryPi: si può fare! (parte 2) – nginx, php-fpm, Wordpress e SQLite

  15. Pingback: Raspberry server | Applicazioni interessanti sul Web - InternetPost

  16. Pingback: Raspberry server

  17. Hi Eric,

    I followed your instructions but now i am getting the following error:

    Line 431, Function: prepareQuery, Message: Problem preparing the PDO SQL Statement. Error was no such table: modTimes
    Line 431, Function: prepareQuery, Message: Problem preparing the PDO SQL Statement. Error was no such table: modTimes
    Line 431, Function: prepareQuery, Message: Problem preparing the PDO SQL Statement. Error was no such table: modTimes
    Line 431, Function: prepareQuery, Message: Problem preparing the PDO SQL Statement. Error was no such table: modTimes
    Line 431,

    in the debug.txt?

    How to resolve this? thx




Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.