tienshiao.org

As I mentioned previously, I’ve been working on a port of WordPress 1.5.2 to PostgreSQL. I had wanted to be the guinea pig and migrate my blog first but time constraints and shuffling priorities prevent me from doing so at this time. I’ve created many test installs and I think I’ve pretty much covered all admin functions, but I’m sure I’ve missed somethings. Consider this a first draft or “beta” even. Regardless, I’m sure this will be useful for someone. I’ll probably start porting the 2.0 branch once bugs are fleshed out, installation works smoothly, and there is a MySQL to PostgreSQL migration script. For right now, you guys are own your own for blog migrations. Really shouldn’t be too hard though.

I’ll fix bugs related to the porting to PostgreSQL, but I’m probably going to skip on fixing bugs that are also found in the official branches unless they’re simple one line/typo type fixes. I don’t want spend too much more time fixing stuff that has been replaced. So, if the bug can be found in the MySQL version, then I’m probably not going to fix it.

Download WordPress 1.5.2 for PostgreSQL (unofficial).

Requirements

• PHP - version 4.1 or greater (so says the official site)
• PostgreSQL - Tested against version 8.x, not sure what the minimum is

Installation

Installation currently requires a little more technical savvy than the standard WordPress install. Users interested in using PostgreSQL will hopefully be able to handle it. Here is a modified version of the standard WordPress installation:

1. Download and unzip the WordPress package, if you haven’t already.
2. Create a database for WordPress on your web server, as well as a PostgreSQL user who has all privileges for accessing and modifying it. The character set for the database should probably match the setting under Option->Reading. WordPress defaults to UTF-8 (PostgeSQL calls this UTF8).
3. Rename the wp-config-sample.php file to wp-config.php.
4. Open wp-config.php in your favorite text editor and fill in your database details.
5. Load the PostgreSQL schema from wp-admin/schema.sql. This can be done with a third party client or (I usually use) the psql commandline tool. The command line for psql is “psql -U username database -f schema.sql”. Note: if you’ve modified the $table_prefix in wp-config.php, you will need to update schema.sql to reflect those changes. Simply do a search and replace on “wp_”
6. Place the WordPress files in the desired location on your web server.
7. Run the WordPress installation script by accessing wp-admin/install.php in your favorite web browser.

Changes from the official branch

• wp-db.php replaced with PostgreSQL version. Includes function to massage return data, so column names have the expected case.
• LIMIT X, Y replaced with LIMIT Y OFFSET X.
• DATE_FORMAT replaced with TO_CHAR.
• YEAR/MONTH/DAY/HOUR/MINUTE/SECOND replaced with EXTRACT.
• UNIX_TIMESTAMP replaced with EXTRACT.
• Replaced DATE_ADD/DATE_SUB.
• Removed unnecessary ` and ‘ in queries.
• Modified queries so that ORDER BY columns were in the selected result set.
• LIKE replaced with ILIKE.
• Removed LIMIT 1 from UPDATE/DELETE queries.
• Fixed areas where behavior depended on MySQL treating ” as zero.
• Replaced IF with CASE.
• Disabled bits of the installer.

Things that could be done to take better advantage of PostgreSQL

• Add some foreign key constraints.
• MySQL ENUMS are implemented as checked strings. These could be normalized into another table.

Things that don’t work

• That first day of the week setting. This option is not used with PostgreSQL. PostgreSQL appears to use the ISO-8601 standard of Monday.

This entry was posted on Wednesday, December 28th, 2005 at 3:10 pm and is filed under Computers. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.