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 ADOdbExecute
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
andCONCAT
functions.
I was quite surprised that the changes I needed to make to support M$ SQL Server were rather minimal. In fact, once I1 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.
This post is part of the project: Tasks Pro™. View the project timeline for more context on this post.
The ADOdb library is very good, I’ve used it for several years now. The help desk software I’m working on is built with it from the ground up. Postgres is such a pain that I’m actually not going to support it, at least in v1. MySQL and M$ SQL Server use very similar dialects of SQL so supporting both of them is easy.
It’s also been my experience the you can get away with supporting MySQL OR Postgres and you’re ok as most shops that use one are OK with the other as long as they don’t have to buy an MS product.
The majority of alternate database requests I get are for Postgres support. Also, with the uncertainty around the MySQL licensing stuff, I thought supporting another free database was a good hedge.
ADOdb
Alex is porting Tasks to use ADOdb from straight MySQL. I’ve built HelpSpot from the ground up using it. I’ve been using it for several years and I’m really happy with it.
He mentions the trouble Postgres can be if your moving from a MySQL/SQL Se…
Yeah, I’m hoping to roll Postgres and Oracle in to the product i’m working on during the second round of development. Since you moved to PHP5 you may also want to check out the integrated SQLite database.
Sorry for posting and trackbacking, I didn’t think the comment went through so then I decided just to post on my blog.
What was the problem with the group by on MS SQL? I know SQL Server supports it (I use it all the time). Is it just a difference in syntax between MySQL and MS SQL?
Email me if you would like another pair of eyes to look at the syntax for you.