Over the last few weeks I’ve ported Tasks Pro™ to ADOdb. At the moment, I’m successfully running on MySQL, M$ SQL Server and PostgreSQL. I still have a ways to go before I’d be comfortable releasing this code (mainly with regard to installation error handling) but assuming all goes well, version 1.6 of Tasks Pro™ will (at least unofficially) support M$ SQL Server and PostgreSQL in addition to MySQL.

The initial conversion to be up and running on MySQL wasn’t really that hard, just time consuming. Here are some of the things that needed to be done:

• Find all mysql_query function calls and replace them with the ADOdb Execute method, and the matching result handling code – there were a few hundred of these.
• Move LIMIT statements out of the SQL and instead use ADOdb’s SelectLimit method.
• Use the ADOdb qstr for escaping strings in queries.
• Use ADOdb’s date functions for escaping date strings in queries.
• Use ADOdb’s version of the MySQL DATE_ADD and CONCAT functions.

I was quite surprised that the changes I needed to make to support M$ SQL Server were rather minimal. In fact, once I¹ made a couple of changes (patch, patch), very little else had to be done.

The remaining change, however, was a painful one. M$ SQL Server didn’t like the GROUP BY clause in my standard SELECT query, where I get tasks, joining to the task groups table to restrict access and grouping by task id. The easiest solution was unfortunately a work-around in PHP after the SQL has been executed – not elegant.

Getting things up and running on PostgreSQL was a different matter entirely. I’ve heard only good things about PostgreSQL and admittedly I know very little about the database, but getting things working on PostgreSQL was not easy.

Not only did I have to change the way some data was stored for PostgreSQL, I actually had to change the default values and even the names of certain table columns. I also had to do something I really didn’t want to do, add in some code specifically for working on PostgreSQL. The point of using ADOdb was to avoid putting database specific switches into my code, but there was one situation where I just couldn’t avoid it.

Overall, my impression of ADOdb is positive. The documentation is a little sparse, but enough to get by. The performance hit isn’t noticable; probably in part because I work hard to minimize the number of queries needed for each screen. 😉

I’ve also learned that if you’re planning to use ADOdb or another library in one of your own products, you should be prepared going in to: dig into the source code to see what is going on, and fix things that seem broken.

1. With some help from Scott, thanks! [back]

This post is part of the project: Tasks Pro™. View the project timeline for more context on this post.