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.

Several versions ago the SQLite data layer stopped working so I stopped making releases with this data layer. When I first implemented WebParts in the MyPage feature I never could get the SQLite version to work correctly because I was having trouble getting the blob data in and out of the db due to some issue in the Mono.Data.SqlitClient library which we were using for data access with SQLite.

Not long ago the Mono team revamped a new library, Mono.Data.SQLite, to support 2.0 .NET data access with SQLite and the old library is no longer recommended for use. Yesterday I was able to repair the mojoPortal SQLite data layer by switching to the newer library and doing just a little re-working of code in a few places. The best part is that blobs work well using the new library so I was even able to get WebParts working with SQLite.

The fix is in svn trunk now for anyone interested and next release we will include a package for SQLite. The interesting thing about SQLite is that it only requires a tiny dll and file acess to work. So for instance it should be possible for me to distribute a db already populated with default data with no need for any database setup. Pretty cool! Of course for a real web site I would still recommend to use one of the more robust platforms to get best performance.

Hey mojoPortal community, please nominate mojoPortal for the PackT Publishing 2007 Open Source CMS Award.

Nominations begin July 16, 2007

Just made a post in my personal blog that is probably of interest to mojoPortal fans.

It has a screen shot of the SiteOffice UI showing a little google gears client database admin tool I threw together.

UPDATE 2007-06-24:

I've updated this site with the latest code, so anyone logged in can try the google gears query tool. After you login, click the Site Office link at the top then go to My Stuff > SQL

You can create tables, insert, update delete, select etc. Pretty cool huh?

As I mentioned in a previous post, I'm back to focusing my efforts on a set of features that will fall under an umbrella concept called Site Office.

Those of you who are registered on this site can have a look at the UI protype by clicking the SiteOffice link at the top of the page (after logging in). I think the UI is looking pretty cool. Using dojo and Rico, I've got an Outlook like accordian navigation section as well as window resizing by dragging the divider. Have a look and let me know what you think.

Some of the features I plan under this umbrella are:

External email access

you will be able to check any pop3 mail accounts including Yahoo and Google. It could also work with your company email system if it exposes a pop3 entry point.

One of the things I'm thinking about with this external email feature is that I don't wan't to store any messages on the web server database since they are already stored in the mail server and since this would be huge amounts of data. But I do want to be able to keep messages available to the user without having to go get them again for every request. So this seems like an excellent opportunity to use google gears. The messages can be retained in a client side SQLite database. It should also even be possible for users to read messages that have already been retrieved even when offline.

Contacts

which I would like to store on the server and also using google gears to make them available offline to support travelling workers.

Site Mail

which will look and feel like email but just be for communication with users in the site.

More...

Personal File Storage
Email Newsletters and Campaigns
Project Management/Issue Tracking/Time Tracking

Most of these features are Intranet type features that I envision only making available to certain roles.