Using alternative databases: solutions
Currently, the official WordPress distribution only supports the MySQL database engine. A number of people have requested support for other database engines, particularly the open-source PostgreSQL. This page is an effort to summarize the previous discussion on the topic, and to get a solid roadmap for supporting more databases in WordPress.
[divider]Port vs. Integration [/divider]
The most common way of adding support for another database engine to an existing project involves completely overhauling the source code to replace references to one engine to another – creating a port with support for the database engine of your choice.
The drawback to porting WordPress is the difficulty of keeping it in sync with the original codebase and up to date with new features and security enhancements. This can be seen in Keenan Tims’s PostgreSQL port of WordPress 1.2 – now insecure and out of date. There is also a more recent port by Erwin Wolff available at Source Forge.
While this is a great first step and sufficient for some needs, a port is not desirable to most users who wish to have the latest security and features available from main WordPress branch. A better solution would be to integrate support for alternative databases into WordPress. This, however, will take a concerted effort from the developers to write queries which port easily, and to get together a good abstraction layer for the database.
[divider]Database Challenges [/divider]
- Current codebase is very MySQL-centric. While WordPress does use the ezSQL class to implement database calls, this cannot properly be called an abstraction layer. Differences in SQL syntax implementations among different databases (whether literals are surrounded by quotes, limit query syntax, case sensitivity, etc.) are not cared for by ezSQL, which simply offers a generic “query” call. This means that a large number of queries would have to be rewritten, a large task.
- Database driven plugins depend on the current implementation, and also use MySQL-centric code. Even if the wordpress code were to be rewritten to support a full database abstraction layer, any sudden shift in the database implementation will likely break other plugins that rely on database access.
- Current standard database abstraction layers (ADOdb, Pear DB) are very large and complex, they would represent dependencies as large as or larger than wordpress itself. This could reduce WordPress’ portability and ease of installation.
Maintain the current development direction. Very little importance is placed on database independence by the WordPress core developers at the moment, so a fork is necessary to support other databases.
- This approach is already in place for (at least) PostgreSQL support, existing code needs only maintenance
- Cooperation from the WordPress development team is a non-issue
- Performance optimizations can be done with no concern for other databases
- This approach is very costly for the port developers. Each release of WordPress must be scoured for incompatibilities, ported, and tested independently.
- No user-support for users of alternative databases from the WordPress team
Extend to support PostgreSQL
Extend ezSQL to include any necessary abstractions to make MySQL and PostgreSQL work equally well.
This would be a matter of including both ezSQL’s Postgres abstraction and their MySQL abstractions in the installation, as well as adding necessary methods to generalize between the two (i.e., a limitQuery method, perhaps others). Rewrite SQL queries throughout to be PostgreSQL/MySQL compatible, and to use custom extensions to ezSQL to handle incompatibilities where they exist. Users would set which database they were using in wp-config.php and not have to think about it further.
- This solution is probably the minimal solution to properly support both PostgreSQL and MySQL, and keeps a low footprint and no large dependencies.
- Legacy plugins dependent on MySQL could still function so long as they are used on a MySQL installation.
- Use of the existing WordPress-Pg port code may make this approach even easier.
- The solution doesn’t easily allow support for other databases, and is generally substantially less clean than using a full abstraction layer of some sort.
- Some queries simply cannot be expressed in a database agnostic way (e.g. getting the next value for auto_increment/sequence), which will result in a potentially nasty mix of abstraction and “direct access”.
- Abstracting error handling will be hard (will it? –KT).
- Some developers (both of the core project and of plugins) may not keep their changes in sync for both databases.
- Database queries would continue to be scattered all over the code.
WordPress-specific Database Abstraction Layer
Convert all queries (and blocks of queries) in WordPress to function calls, or more appropriately, object methods, to get that relevant information out of the database.
Support for new databases can be added by adding code for that database to these functions. Optimizations for specific databases when getting certain blocks of information can be added to the relevant functions.
For example, fetching the front page posts and their comment count can be done in one query on databases that support subqueries vs. requiring at least the number of posts shown + 1 queries with legacy MySQL. Legacy plugins should still function when MySQL is being used.
- All the code and queries that would need tweaking to support a new database would be in one file rather than being shotgunned all over the codebase as is presently the case.
- Optimizations for specific databases would be easier to implement and maintain (using, for example, subselects); this could offset any overhead from the functional calls. Optimization benefits may exist for newer versions of MySQL as well.
- Future schema changes and cleanup wouldn’t require changing as many files.
- No new dependencies besides database support for the database the user is utilizing.
- Schema improvements that could benefit all databases would be easier to implement.
- Ability to support arbitrary storage backends like RSS feeds, text files, or an install of a different piece of blogging software (not just databases). This will also make the upgrade and migration code a lot cleaner and easier.
- The extra function calls and their packaging of data will create a small amount of additional overhead.
- This option will be a fair amount of work and will require developers of the main project and plugins to become familiar with it.
- Code for the various databases may fall out of sync without some diligence on the part of developers.
- Accessing the database data in new ways in the future will require additional functions to be written.
- Additional forethought is required in figuring out what database accesses to group together to reach the full optimization potential.
Full Database Abstraction Layer
Convert all queries in WordPress to function with a database abstraction layer such as ADOdb or Pear DB.
- Cleanly implements database usage in the most portable means possible, allowing use of WordPress with any database supported by the chosen abstraction layer.
- These tools create large dependencies and break legacy plugins that depend on MySQL specific functions.
- Large generic database (and persistent object) abstraction implementations tend not to be exceedingly fast and may not use optimizations that could benefit specific databases.
- This option will be a lot more work (likely less than implementing a WP-specific layer, however) and will require developers of the main project and plugins to be familiar with the abstraction layer chosen.
- Much diligence will be necessary for developers to ensure that database-specific code doesn’t make it into queries despite the abstraction layer, and this goes for plugin developers too.
- Some functionality can’t be abstracted easily with ADODb or (to a much greater extent) PEAR; if WordPress uses this functionality, it may have to be reimplemented application-side, which would be a performance hit
A number of people (see the previous discussion thread) had proposed donations to pay for development of one of these choices for alternative database support in WordPress.
PostgreSQL for WordPress (PG4WP)
PostgreSQL for WordPress is a special ‘plugin’ enabling WordPress to be used with a PostgreSQL database.
WordPress has always been locked into using mysql as its database storage engine. But some people would like to use some other databases such as PostgreSQL. There are many different motivations behind this, sometimes people already have PostgreSQL on their server and don’t want to install MySQL along PostgreSQL, or simply don’t like MySQL and prefer using alternatives.
PostgreSQL for WordPress (PG4WP) gives you the possibility tu install and use WordPress with a PostgreSQL database as a backend.It works by replacing calls to MySQL specific functions with generic calls that maps them to another database functions.
When needed, the original SQL queries are rewritten on the fly so that MySQL specific queries work fine with the backend database.
Currently, support is focused on PostgreSQL, but other databases can be added quite easily by providing the appropriate ‘driver’.
MySQL driver is enclosed, which just does “nothing”.If you need/wish support for another database, please feel free to contact the author, writing a driver is not really hard if you know a bit about SQL and the database you want support for.