The web application I’m working on (PHP 5.3, zf2, Doctrine2, on MySQL/Apache) has recently raised interest from the clients and it now needs to be installed on their premises, often a Windows server( arghhh!). That meant we had to make the application compatible with both MySQL and SQL-Server.
Here I summarize the changes and the solutions we adopted.
Tables and fields named “user” or “public” are not accepted in SQL server. Neither backticks or square brackets around table names, therefore we adjusted the doctrine mapping to the use different names. Fortunately, we didn’t need a migration script for that since the client was happy to have the db reset. Otherwise, you’ll need one, doctrine upgrade won’t work as expected when renaming tables.
Mysql type conversion is of course different from the SQL server conversion, e.g. empty strings are not converted to integer or decimal zero-values.
That was easy to solve, thanks to our webservice elegantly covered by unit testing (developers’ pride :D).
Working with PHP means not worrying about type conversion. MySQL works well with PHP for that reasons, it saves empty strings as a zero whereas SQL Server stops and throw an exception forcing us to convert it manually at the model level. No problems though, definitely a no-brainer with well-written doctrine models.
Oddly, we didn’t experience any DateTime conversion issue, specifically mentioned in the doctrine documentation.
That took quite a lot of time to solve. We had cascade delete operations for MySQL (onDelete=”cascade”) that beautifully worked for MySQL, until I had to face the different SQL server behaviour (rather unexpected to be honest) related to cascade operations, where any multiple cascade was not supported, and I ended up implementing them at the application levels. Doctrine cascade application-level mapping didn’t work, and because of PHP SQL server extension constraints, I couldn’t even update Doctrine 2 to the last version hoping to have it perhaps solved.
Due to complex cascade operations of our application, I found the best maintainable solution by using a Doctrine Event listener. Again, thanks to unit testing that took relatively little time to develop. Here is a code example:
We used to use a SQL file to insert the initial needed data. Making one compatible with both resulted to be nightmare, due to the need of SQL server to specifically enable/disable KEY id values for each table, the quote/double quote incompatibilities and reserved keywords.
We had to constantly update the fixtures, so the best solution was relying – again – on something at the application level, a kind of doctrine fixture system, that doesn’t seem to be implemented at the time of writing. Well, there is something, but it was much easier to make a simple class to populate and persist the entities to effortlessly solve all the data relationships.
Here a skeleton to reuse.
If you expect to have Doctrine 2 automatically solving all the database server differences, you area dreamer :D, estimate the work in at least 5 days of work including testing