PostgreSQL support ( patch included )

[usleepless]

this is a patch to make WP 2.2.1 PostgreSQL compatible.

to make it work:

• edit wp-includes/wp-db.php add "require_once('mysql2pgsql.php') at the top
• save the attached mysql2pgsql.php to wp-includes
• edit wp-settings.php and remove the MySQL not included warning/die check
• make sure your php doesn't load the mysql client/module
• create a new database ( createdb wordpress ), edit your wp-config.php and complete the wp-installion process

afaik, there are no problems.

this is "a" solution to the multiple database support "problem".

• this patch redefines the mysql_* calls: it would be better to use the PEAR db-wrapper class for example
• this patch manipulates the SQL. stuff can be learned from this though:

• using "SHOW TABLES" to check if WP is already installed is MySQL specific: trying to "SELECT * FROM wp_posts LIMIT 1" is not.
• a couple of queries use the "`" character for no reason whatsoever
• datatypes: some translation will always be necessary ( text/memo fields for example ). but is using "longtext & mediumtext & tinytext" really sensible? or int(11), int(4), bigint(20) and tinyint(1) ?
• almost all of the schema is lowercase: there are some table using capitals for fields containing "ID". let's get rid of them ( this is probably really pg-speficic )
• there are MySQL specific "IGNORE" qualifiers in INSERT statements. what are they good for? let's get rid of them
• in the db-installation procedure, inserts are performed on tables with auto_increment fields, specifying this very field ( wp_categories, wp_post2cat ). trust the auto_increment, remove the values for the auto_increment fields. this confuses pgsql
• SQL_FOUND_ROWS & FOUND_ROWS() is MySQL specific. i have replaced them with dummy's and didn't notice any difference. let's work around this
• MySQL specific LIMIT syntax is used. let's use ISO "LIMIT x OFFSET y".
• there is seemingly useless use of "GROUP BY" in some statements. let's get rid of them (pgsql complains)
• using "!=" is not ISO afaik. let's write "<>" instead

if these suggestions are implemented, WP can be made multiple DB compatible quite easily ( mysql, pgsql, mssql, db2 etc. whatever PHP supports )

i am willing to contribute time & patches to make this work.

regards,

usleep

[Otto42]

Wouldn't it be better to simply create a wpdb class of your own and put it in wp-content/db.php ? This will then prevent the wp-db.php from loading at all, and it will load your class instead. All you have to do then is to make all the wpdb functions work for whatever database you prefer.

Doing it this way also bypasses the mysql check in settings.php. It's a simpler way to go, seems to me. No core hacks or patches required.

[westi]

I have to agree with Otto42 here.

This can't go into the core as-is due to its dependancy on the php mysql not being loaded. It at least needs some function-exists checks.

It would be much better implemented as a wp-db.php plugin as that is the way in which WordPress supports alternative db access.

[usleepless]

Hi,

it is/was not my intention to create a PostgreSQL solution for Wordpress that directly fits into your architecture. this is more a "proof of concept" to find out what needs to be changed to support different DBMSs ( in my case PostgreSQL, because i won't run mysql (period) ).

i just wanted to record this solution into Trac for other PostgreSQL users to find, and raise the issue. i understand your suggestions about the architecture of the code. but a serious solution shouldn't include preg_replacing as much as this code does.

this code shows what needs to be done about the SQL in Wordpress, while staying compatible with MySQL.

i would be more interested in comments on my initial comment, than on comments on the code itself.

as that is the way in which WordPress supports alternative db access.

could you please point me to some examples of alternative db access implemented this way?

thanks for your feedback and regards,

usleep

[Otto42]

Trac is for reporting and tracking defects and enhancements, not for discussion and debate (although it happens from time to time)

I would suggest that you take your code to the wp-hackers mailing list instead and discuss it there: ​http://lists.automattic.com/mailman/listinfo/wp-hackers