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_queryfunction calls and replace them with the ADOdb
Executemethod, 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
- Use the ADOdb
qstrfor escaping strings in queries.
- Use ADOdb’s date functions for escaping date strings in queries.
- Use ADOdb’s version of the MySQL
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.