Buoyed by my success in reviving the SQLite data layer I decided to try and get Firebird Sql working. After only 4 days I have it all working pretty well. Its available now in svn trunk and I will be packaging precompiled deployment files for Firebird in the coming release which will ship very soon.
Gareth Goslett had implemented Firebird in the 1.x version of mojoPortal but it has been sitting unused and un-usable for the 2.x version of mojoPortal for a good while. My motivation for taking interest in Firebird was because back when I blogged a little about Gareth's work before I noticed a pretty good uptick in web traffic due to links from sites in the Firebird community. It seems to me they have a pretty thriving community and yet not much out there in the portal/web framework space that does support Firebird so it just seemed like it would be worthwhile to get this working in mojoPortal.
Rather than try to update the old version, I started from scratch figuring that if I'm going to maintain this data layer I want it to be as similar as possible to the other ones. I wrote some Codesmith templates to generate Fiebird tables from MS SQL tables and to generate stored procedures and data access code for standard crud operations. These will allow me to maintain this data layer going forward fairly easily. Then I implemented a FBSqlHelper based on the one we have for PostgreSql. Then I copied the dbPortal.cs from the MySql data layer and with some quick find and replace I was able to compile it against the new FBSqlHelper. I chose the MySql data layer because its has embedded sql statements rather than stored procedures and I figured that was the shortest path to a working implementation, I would just have to make adjustments here and there for differences in sql syntax. As it turned out I did have to implement stored procedures for all methods where we do an insert to an autogenerated integer id column. This is implemented using sequences (aka generators) in Firebird but the only way I could return the new id to the calling code was using stored procedures so I went through and found those methods where this was needed, generated the procedures and data access code for those methods with a few adjustments as needed and voila thigns were starting to work. Next it was just a matter of finding the bugs where the sql syntax wasn't right. For example where MySql and PostgreSql use syntax like LIMIT 10 OFFSET 10 , Firebird uses SELECT FIRST 10 SKIP 10 * FROM TABLENAME. The most difficult challenge was due to the lack of support for temporary tables but with some creativity I found a way not to need them.
My efforts were guided by the Firebird book, online documentation and Gareth's previous implementation code, so Gareth deserves some credit too. I hope the Firebird community will find mojoPortal useful.
