SIL LSDev Linux Development

David asked, “I’m curious to know why Firebird has been chosen as a database engine [for FieldWorks] out of the many other RDBMSs out there, such as MySQL.”

I get this question from time to time, especially when I go to our Computer Technical Conference (CTC), which comes around every two years.  It is a fair question, and since CTC is just a month away, I’m taking the opportunity to remember why we did what we did. I had to ask the people that made the decision, because I had a hard time remembering. It’s been a few years.

FieldWorks started out as a Smalltalk project. When the Smalltalk vendor went under (as I have heard), someone mapped the object data to SQL Server. This was SQL Server 7.0, I think, back before the turn of the century. (Literally.) We have since upgraded to SQL Server 2000, then 2005. (We don’t have any plans currently to move to 2008.) The application code was ported to C++, then newer code was done in C#. You’ve seen various entries from Mark and others about porting that.

I have made at least two studies over the years about which RDBMS to port to. I’m not sure MySQL even existed during the first study, but I know it wasn’t mature enough to do what we wanted at the second study. By the time MySQL had matured–how much may still be a matter for debate–we had already had work underway porting to Firebird.

At one time I had recommended we go with PostgreSQL, and we did head in that direction for a while. However, PostgreSQL needed Cygwin at the time, and Firebird already had a native Windows implementation. Also in Firebird’s favor was the fact that it could be embedded into the application, and this has always had a strong pull for some on the team. Also, it was thought that it would be easier to port the SQL that SQL Server uses to Firebird than it would be to port it to PostgreSQL. Finally, we had a volunteer who knew Firebird and was able to give a significant amount of time. We are in fact indebted to him for laying the foundation of the database port to Firebird.

So the team dropped PostrgeSQL in favor of Firebird.

We have looked at object-oriented datababses as well. None were open source during most of time we made our decisions. We have looked db4objects, but have decided to continue with Firebird for now. Perhaps the biggest reason is that the jump to another SQL RDBMS has already shown to be difficult. Jumping to an object-oriented database would be compound the difficulty.

We have tens of thousands of lines of SQL code for FieldWorks now. Most of it is now ported to Firebird. We still need to put it through unit tests and regression testing.

Since we’re porting from SQL Server to Firebird, code that works on both RDBMSs is highly valuable. We want to connect to either using C#.

Microsoft’s ADO.NET Entity Framework does just that. One of the components is LINQ. Here is a beginner’s application to show how to do it with Firebird.

Download and run the latest Firebird Data Provider for .NET and Mono.

Open Visual Studio 2008 (VS). You can try using 2005, but I couldn’t get it to work. Create a new project. (File/New/Project…) Under “Project types” select Windows. Under Templates select Console Application. Give it a name. (I used LinqToFirebird.) Give the project a location. I prefer to leave the checkbox “Create directory for solution” unchecked, to avoid a deeper directory tree than I want.

In VS, go to the Solution Explorer. We’re going to add a reference to the Firebird .NET Data Provider. Right-click on References. A dialog will appear. Select the Browse tab. Go to the directory where you installed the provider and select FirebirdSqlData.FirebirdClient.dll

Now we get to code. In Program.cs, add the following above namespace at the top:

using System.Data;
using FirebirdSql.Data.FirebirdClient;

In the Main method, use the following code. You will have to change the connection string to your own, of course. (The best site for database connection strings is ConnectionStrings.com.) Microsoft has a number of examples for LINQ.

FbConnection con = new FbConnection(
“ServerType=0;User=SYSDBA;Password=masterkey;Dialect=3;” +
“Database=C:\\Firebird\\Data\\FieldWorks\\FW01.FDB;”);

FbDataAdapter fbadapt = new FbDataAdapter(
“SELECT * FROM RDB$CHARACTER_SETS;”, con);

// Table mappings
fbadapt.TableMappings.Add(”Table0″, “CharSets”);
DataSet dataset = new DataSet();

// Fill the dataset with a call to the database
fbadapt.Fill(dataset);

// Query
DataTable CharSets = dataset.Tables[0];
var queryCharSets =
from rCharSets in CharSets.AsEnumerable()
select rCharSets;

Console.WriteLine(”RDB$CHARACTER_SETS:”);
foreach (DataRow rCharSets in queryCharSets)
{
Console.WriteLine(rCharSets.Field(”RDB$CHARACTER_SET_NAME”));
}

// pause
Console.ReadLine();

CloseConnection(con);

I’m a Michigander working in Texas. I’ve been working on databases for FieldWorks since 2001. Currently FieldWorks uses SQL Server. My role in the Linux team is to help port from SQL Server to Firebird.