Saturday 14 April 2012

Getting vTiger to work with Postgresql

Recently while looking at vTiger CRM, decided to spend some time getting it to work on Postgresql 9 database. It turned out to be a lot more work than I have initially anticipated. Someone has make quite a significant contribution to make vTiger working with Postgresql database. However, there is still a lot of work to be done. Most of the the problems are due to SQL syntax. I'm quite surprise at the syntax that works with MySQL. The list below contains the SQL syntax that are broken in Postgresql but works in MySQL.
  • Joining character field to integer field
  • Using '0000-00-00 00:00:00' as default date
  • Using limit min, max
  • Using duplicate index name
  • Using non standard GROUP BY syntax
  • Using double quote to enclose character data
  • Inserting NULL into integer field
  • Updating character field without enclosing it in single quote
  • Using MYSQL specific build-in functions
  • Comparing true/false on integer field
IMHO, some of these syntax should never be allowed in the first place. If only the developers were using standard SQL syntax, most of this problems would have been avoided. For more details on the work done, checkout  vTiger Postgresql Patch

1 comment: