Database Independence - WikkiTikkiTavi

Rationale:

This page will track that status of any work I do to improve the DatabaseIndependence of WikkiTikkiTavi. At the moment, ChristopherCashell is showing interest in this (mostly because he prefers PostgreSQL over MySQL, and would like to use Tavi with it;-), but he would love to have help with it.

Current State:

I've written a patch. You can get it [here]. The patch is experimental and feedback is welcome. See the link for more information. -- Jmz

Tavi has an abstraction layer built into it to allow for the creation of new db.php files for new databases. However, it is looking like PHP's handling of different databases, for PostgreSQL at least, might be just different enough that the database abstraction built into Tavi won't work with it (Without requiring nearly as much work as porting the whole thing to the PEAR DB class would).

Notes:

Assuming a full port of Tavi to the PHP PEAR DB class occurs, we might be best off treating it as an optional development branch, at least for a while. The PHP PEAR classes, while really cool, are poorly documented, and exactly how well they're supported in each version of PHP that they've been released with is even more poorly documented.

The PEAR developers are currently strongly recommending that anyone using the PEAR classes use a recent CVS snapshot. Making that a requirement for Tavi seems like a very bad thing.

I'm just not entirely sure how well the PEAR DB class included with early 4.0.x releases of PHP works.

Possible Issues:

The database creation script, as well as 'Tavi, currently makes use of the MySQL specific set data type for the mutable column. From what I've seen, it is basically used as a boolean (which MySQL doesn't directly support). In order for 'Tavi to work with other databases, we might need to change this data type to something more portable (a single byte or short int, set to 0 for false, and 1 for true, comes to mind).

That would definitely be best. But if you're lazy and don't want to change the code, you could also make the column a char(3). We never try to set it to anything other than 'off' and 'on', and we test it against those same values. That would, at least, be the cheap and easy way out. (But it wouldn't be the cowboy way. ;-) -- ScottMoonen

You need to use varchar(3) for that.

'Tavi uses MySQL specific functions, like TO_DAYS, TIME_TO_SEC, and LOCK TABLES. The SQL queries must be made dynamic to allow database-specific function calls to be used, or the calculations must be made in PHP.

To Do:

Create a PostgreSQL db.php file so that Tavi can be used with PostgreSQL.
- This is in progress, but might be shelved in favor of porting Tavi to the PEAR DB class.
Add variables to config.php to support choosing desired database.
Maybe rewrite create-db.pl as a PHP script, to be called on first run, or as part of an interactive, web based, setup?
Rewrite all the database interface code for Tavi to make use of PHP's new Database Independant interface, included since PHP 4.0.
- Although the basic support for the PHP database independence is included with PHP > 4.0, it sounds like the whole PEAR project is under very active development, and the CVS version is currently "prefered".

References:

http://cvs.php.net/cvs.php/php4/pear

The PHP CVS repository for PEAR, where the DB.php and DB/* files can be viewed.

http://vulcanonet.com/soft/pear_tut/

This is a Quick Start Guide to Pear DB, by Tomas V.V. Cox. As near as I can tell, Mr. Cox is one of the primary people behind the Pear DB class.

http://www.phpbuilder.com/columns/allan20010115.php3

An article on Database abstraction, why it's good, and some examples on achieving it using PEAR.

http://www.onlamp.com/pub/a/php/2001/05/24/pear.html

http://www.onlamp.com/pub/a/php/2001/07/19/pear.html

A couple of articles providing an introduction to PEAR in general, with an extra bit about the PEAR::DB class.

http://phpdocu.sourceforge.net/peardocs/

A function reference generated from PHPDoc, including the PEAR classes.

http://www.phpdoc.de/pear/DB.html

A (possibly out of date) function reference for the PEAR DB classes.

Have you thought about other DB abstraction libraries. A particularly good one is ADODB at /http://php.weblogs.com/ADODB

It is a lot faster than PEAR DB, and supports a wider number of databases. It even has PEAR DB emulation!

Yes, I have considered it. My main reason for wanting to go with the PEAR DB API is that the PEAR DB class looks to be the "official" standard for PHP. It's commonly included with a default PHP installation, thus reducing the likelihood that people will be forced to install additional software to use WikkiTikkiTavi. --ChristopherCashell

Chris, do you know how widespread PEAR support is? Both in terms of which PHP4 versions have it and how often it is included in PHP installations? It seems to me that ADODB would work on any PHP installation, whereas it sounds like PEAR might take more work on those installations that are missing PEAR support or which have a buggy, out-of-date version of PEAR. Is that true?

From what I've seen, I don't think PEAR should be any harder to install than ADODB, and I do think it'll be more common.

For example, I believe the Red Hat 7.2 PHP RPM includes the full PEAR installation. I know for a fact that NetBSD's PHP port also includes PEAR with it. On Debian, PEAR is split off into the php-pear package, so it isn't included automatically, but it is very easy to add. It seems that these three setups tend to bundle the current CVS snapshot of PEAR with whatever release of PHP they're building. I think that anything bundled with PHP versions around 4.04 or later should have sufficient database support that it'll work for 'Tavi.

Essentially, I want to mitigate the cost to those who can't blithely re-build Apache (the position I'm in). If most people wouldn't have to do so, then PEAR might be an option. But otherwise, ADODB is looking pretty attractive, since I can install it and use it without needing any administrative access. What're your thoughts?

From what I've seen, neither PEAR (especially the database specific stuff) nor ADODB should be all that terribly difficult to install, even without administrative privileges.

Or am I wrong in assuming PEAR is compiled in to PHP? Stuff at http://pear.php.net/ seems to suggest as much ("written in C"), but then some of the tutorial sites seem to suggest it's vanilla PHP code. -- ScottMoonen

My understanding is that PEAR (PHP Extension and Application Repository) is intended to eventually serve a purpose similar to what CPAN does for perl. It allows for extension modules to be written for PHP, which can be written in C or PHP, and easily installed and utilized. I don't konw that any PEAR modules are actually written in C yet, though (I think they're planning to initially do things in PHP, and then rewrite the stuff that is performance critical in C), and from everything I've seen, at least all of the DB specific stuff for PEAR is just vanilla PHP code.

If you'd like, you can brose the PEAR files at: http://cvs.php.net/cvs.php/php4/pear. The specific ones we're interested are DB.php and the DB/ directory.

I haven't tried it myself, but I would imagine that in a worst case scenario, you could just grab the DB.php and DB/ files, dump them in a convenient location, and if your PHP installation supports the standard PHP access routines for the database you want to use, you'd be good to go.

My biggest gripe with regards to PEAR right now, is poor (or non-existent!) documentation. Browsing through the CVS repository, there looks to be a lot of useful and interesting code there. . . too bad there's so little information on putting it to use, or explaining it. ;-)

Metabase by manuel lemos provides a better abstraction, methods of access and retrieval are uniform for all db's. Basically the database is described in xml format and parsed for each db from there.

Another suggestion of a database abstraction library: [ezSQL]

From personal experience, it is fast (both in implementation and performance) and easy.