PDO For WordPress – All change …
Wp-Plugin.Org
rather surprisingly, after about eight weeks of waiting, i’ve been granted svn commit privileges for this project as a plugin on wp-plugins.org. So shortly I shall be migrating the hosting of the code to Sr Mullenweg & Co and will be using their svn server rather than my own, rather clunky, implementation!
To Do’s
Preparing for this migration has caused me to consider what changes, if any, should be made to the sqlite driver. I think these are the main to do’s but I’m willing to listen to anyone else’s wish list too:
- parameterise the location of the sqlite database
- parameterise the name of the sqlite database (this will allow users to have multiple WP blogs being managed from the same installation)
- translate as many of the preg based query manipulations as possible to user defined sqlite functions. This should result in a performance increase (although I have not noticed this app being slow)
- and … as ever … to make the update functionality (dbdelta) within WP work with sqlite. this is not straightforward as sqlite does not fully support the ALTER syntax.
Versioning
I’ve had nearly 50 downloads with only one bug reported (fixed). I have had no other feedback about whether this plugin works for people or does not, and, of course, i have not included any beacons or similar in the code to let me know whether this plugin is actually being used. So I can’t really tell whether this app is release quality yet. Nevertheless I’ll take the plunge and release it as version 1.0.0 when I first upload to wp-plugin.org.
Thanks for creating this WordPress extension. I’ve wanted the option of using Sqlite for WordPress for a while. I use it in the desktop applications I create and like it. Making it a plugin is an excellent idea.
I’m able to use it locally. I did have to make a change in wp-config.php. I couldn’t get the extension to work until I put the database format label in its own php block before the other defines:
<?php define(‘DB_TYPE’, ‘sqlite’); ?>
Then the standard define block. The extension wouldn’t work if I put the db_type define anywhere in the standard define block.
Unfortunately, I can’t use the extension in a live blog. My ISP, godaddy, doesn’t have the php pdo extension for Sqlite3 active. It may be a good idea to list this as a requirement to run it, rather than just listing PHP 5 as a requirement.
Hi Rick
thanks for your comments. I’m surprised by your report of the define giving you trouble. My wp-config.php file looks exactly like this
< ?php // ** MySQL settings ** // define('DB_NAME', 'putyourdbnamehere'); // The name of the database define('DB_USER', 'usernamehere'); // Your MySQL username define('DB_PASSWORD', 'yourpasswordhere'); // ...and password define('DB_HOST', 'localhost'); // 99% chance you won't need to change this value define('DB_CHARSET', 'utf8'); define('DB_COLLATE', ''); define('DB_TYPE', 'sqlite'); define("PDO_DEBUG", true);
The plugin thing is just a word. the applet will be hosted as a plugin but I cannot make it a true plugin as the database gets loaded before the plugins do (i think - will check).
For more flexible ISP's take a look at 1&1 and BlueHost. The former is the grand-daddy of prosumer hosting providers. I've recently discovered BlueHost, their offering is awesom and so far their support responsiveness has been beyond belief.
i've noted your comments on PDO + sqlite. good thoughts!
I tried putting the define at the end of the block like you suggested in the documentation for the extension. Here’s the error message I get:
//
Warning: Cannot modify header information – headers already sent by (output started at F:\xampplite\htdocs\cockrum_publishing\wp-config.php:1) in F:\xampplite\htdocs\cockrum_publishing\wp-content\pdo\db.php on line 441
WordPress
Queries made or created this session were
1. Raw query: SELECT option_value FROM options WHERE option_name = ‘siteurl’
2. Rewritten: SELECT option_value FROM options WHERE option_name = ‘siteurl’
3. With Placeholders: SELECT option_value FROM options WHERE option_name = ?
4. Prepare: SELECT option_value FROM options WHERE option_name = ?
Error occurred at line 313 in Function prepareQuery.
Error message was: Problem preparing the PDO SQL Statement. Error was Array ( [0] => HY000 [1] => 1 [2] => no such table: options )
PDO_Engine Object
(
[isError] =>
[foundRowsResult] =>
[initialQuery:private] => SELECT option_value FROM options WHERE option_name = ‘siteurl’
[rewrittenQuery:private] => SELECT option_value FROM options WHERE option_name = ‘siteurl’
[queryType:private] => select
[rewriteEngine:private] => pdo_sqlite_driver Object
(
[ifStatements:private] => Array
(
)
[startingQuery:private] => SELECT option_value FROM options WHERE option_name = ‘siteurl’
[_query] => SELECT option_value FROM options WHERE option_name = ‘siteurl’
[dateRewrites:private] => Array
(
)
[queryType] => select
)
[needsPostProcessing:private] => 1
[results:private] => Array
(
)
[pdo:private] => PDO Object
(
)
[preparedQuery:private] => SELECT option_value FROM options WHERE option_name = ?
[extractedVariables:private] => Array
(
[0] => siteurl
)
[errorMessages:private] => Array
(
)
[errors:private] => Array
(
[0] => Array
(
[line] => 313
[function] => prepareQuery
)
)
[queries:private] => Array
(
[0] => Raw query: SELECT option_value FROM options WHERE option_name = ‘siteurl’
[1] => Rewritten: SELECT option_value FROM options WHERE option_name = ‘siteurl’
[2] => With Placeholders: SELECT option_value FROM options WHERE option_name = ?
[3] => Prepare: SELECT option_value FROM options WHERE option_name = ?
)
[dbType:private] => sqlite
[lastInsertID:private] =>
[affectedRows:private] =>
[columnNames:private] => Array
(
)
[numRows:private] =>
[returnValue:private] =>
[startTime:private] =>
[stopTime:private] =>
[_results] => Array
(
)
Thanks for the work you have been doing on this. I have been watching this for a while and will be trying it soon as I have plans for a MaxDB extension.
Rick
glad you’re all sorted now – enjoy.
Justin
Nigel
thanks for your comments. I’ve never used maxDB but do let me know if I can help.
Justin
There are still issues with some plugins. I will go into detail about that later. Nevertheless i soon want to launch a wordpress based cms with your sqlite solution. I tested it thoroughly and it’s rather stable with the basic wordpress ABCD. I hope it will survive the next wordpress update.
Thanks fnumatic. I’m glad to hear it’s working for you. There are plugin incompats – usually because the plugin does not use the WP database API. Do let me have the details when you have a mo (altho i’m on vacation in India for a fortnight).
The plugin was developed on 2.4.1, so it should survive the change to the next point version. The WP upgrade feature will not work yet (for sqlite, it will for mysql using PDO). The dbDelta functionality really relies on core mysql functionality. I’ve thought of ways to work around the problem: but they are all a bit clunky. if I have not thought of a better solution by the time my holidays are over, i’ll implement a kludge and see what happens.
As i posted recently, i’m also working on some optimisations which should translate to a good speed increase. There is some overhead in instantiating the regex engine (it will be preloaded in php 6) and also in the parsing I do for variables to that they can be properly used in a prepare-execute pairing.
by the way, I’m developing another extension at the moment, that will add to the core CMS functionality of WP. Basically it’s a versioning tool so that all edits to pages will be stored and can be individually made ‘live’. To me this is the core difference between a normal CMS and WP.
Justin i am facing the same problem as Rick. I can access the install page but when i start the installation process i get the same error as Rick described. I gave all the plugin files write permission , but this error still occurs.
Queries made or created this session were
1. Raw query: SELECT option_value FROM wp_options WHERE option_name = ‘siteurl’
2. Rewritten: SELECT option_value FROM wp_options WHERE option_name = ‘siteurl’
3. With Placeholders: SELECT option_value FROM wp_options WHERE option_name = ?
4. Prepare: SELECT option_value FROM wp_options WHERE option_name = ?
Error occurred at line 335 in Function prepareQuery.
Error message was: Problem preparing the PDO SQL Statement. Error was Array ( [0] => HY000 [1] => 1 [2] => no such table: wp_options )
PDO_Engine Object
(
[isError] =>
[foundRowsResult] =>
[initialQuery:private] => SELECT option_value FROM wp_options WHERE option_name = ‘siteurl’
[rewrittenQuery:private] => SELECT option_value FROM wp_options WHERE option_name = ‘siteurl’
[queryType:private] => select
[rewriteEngine:private] => pdo_sqlite_driver Object
(
[ifStatements:private] => Array
(
)
[startingQuery:private] => SELECT option_value FROM wp_options WHERE option_name = ‘siteurl’
[_query] => SELECT option_value FROM wp_options WHERE option_name = ‘siteurl’
[dateRewrites:private] => Array
(
)
[queryType] => select
)
[needsPostProcessing:private] => 1
[results:private] => Array
(
)
[pdo:private] => PDO Object
(
)
[preparedQuery:private] => SELECT option_value FROM wp_options WHERE option_name = ?
[extractedVariables:private] => Array
(
[0] => siteurl
)
[errorMessages:private] => Array
(
)
[errors:private] => Array
(
[0] => Array
(
[line] => 335
[function] => prepareQuery
)
[1] => Array
(
[line] => 335
[function] => prepareQuery
)
[2] => Array
(
[line] => 335
[function] => prepareQuery
)
[3] => Array
(
[line] => 335
[function] => prepareQuery
)
[4] => Array
(
[line] => 335
[function] => prepareQuery
)
[5] => Array
(
[line] => 335
[function] => prepareQuery
)
[6] => Array
(
[line] => 335
[function] => prepareQuery
)
[7] => Array
(
[line] => 335
[function] => prepareQuery
)
[8] => Array
(
[line] => 335
[function] => prepareQuery
)
[9] => Array
(
[line] => 335
[function] => prepareQuery
)
[10] => Array
(
[line] => 335
[function] => prepareQuery
)
[11] => Array
(
[line] => 335
[function] => prepareQuery
)
[12] => Array
(
[line] => 335
[function] => prepareQuery
)
[13] => Array
(
[line] => 335
[function] => prepareQuery
)
[14] => Array
(
[line] => 335
[function] => prepareQuery
)
[15] => Array
(
[line] => 335
[function] => prepareQuery
)
[16] => Array
(
[line] => 335
[function] => prepareQuery
)
[17] => Array
(
[line] => 335
[function] => prepareQuery
)
[18] => Array
(
[line] => 335
[function] => prepareQuery
)
)
[queries] => Array
(
[0] => Raw query: SELECT option_value FROM wp_options WHERE option_name = ‘siteurl’
[1] => Rewritten: SELECT option_value FROM wp_options WHERE option_name = ‘siteurl’
[2] => With Placeholders: SELECT option_value FROM wp_options WHERE option_name = ?
[3] => Prepare: SELECT option_value FROM wp_options WHERE option_name = ?
)
[dbType:private] => sqlite
[lastInsertID:private] =>
[affectedRows:private] =>
[columnNames:private] => Array
(
)
[numRows:private] =>
[returnValue:private] =>
[startTime:private] =>
[stopTime:private] =>
[_results] => Array
(
)
)
@Shev: Unfortunately all that this tells me is that the sql creates, for some reason, have not taken during the installation process.
so some early debugging is the database created? check in wp-content/database to see whether MyBlog.sqlite is present.
if it is, rename it to MyBlog.sqlite.old and run the install again.
if that doesn’t work, can you post back with the version of WP you are using and which version of my plugin?
Thanks for the reply Justin. Seems like the plugin is having trouble writing to the MyBlog.sqlite file. I have given our phpscripts server write permissions for this file, however the file is never written to and stays empty after i run the install script. I doubt there is anything wrong with your plugin here, so i will have to go talk to the php script server helpdesk and figure this out. I’ll get back to you when i hear from them. Also i am using WordPress 2.6.1 and Plugin version 1.0.2
@Shev: what permissions do you have on the database directory and the sqlite file?
Got it all worked out. The problem was the lack of write permissions for the sqlite file. The phpscripts server only had read permissions so whenever it tried to create the sqlite file and write to it, the script failed.
that’s great news Shev. Congrats and enjoy!
Hello,
I’m trying to get round that the server hosting my website does not support MySQL so i thought i’d give your plugin a try.
Its worked to the point that i’m getting the error message “Invalid or missing PDO driver” when i try to load the website in a browser.
Unfortunately i am not able to personally assign permissions to files, but am hoping to have it done by the servers admin team. However ideally i would rather not have to do that as there’s a mile or so of beurocratic red tape to wade through before they’ll do anything.
Your plugin’s page on wordpress suggested I leave you a comment here if experiencing difficulties with permissions, is there by any chance another way I can go about the problem?
Thanks