Data access layer

Having decided for the Firebird database I had to choose a data access layer.
There are the usual standard drivers, like ODBC, but they would add more dependencies, so I decided to use IBPP. It is a lightweight library that wraps the native Firebird API in some C++ classes.

IBPP is a very thin layer, so it adds mainly the ability to execute a query and optionally retrieve row values. It lacks a simple way to add or update rows, so I had to write one by myself.

At first I tried the most obvious approach for an OOP project: define a class for each table, with a member for each column. An instance of the class will represent a single row.
Add code to read values from the database, and add code to write the values to the database. This means to automatically create an INSERT or UPDATE statement that stores the member values into the database, with appropriate type conversions. This code would interface to IBPP for all data access.

I wrote my first open source application using this approach to test it, and I discovered that it was far from optimal.
This solution requires to write a substantial amount of code for each table and it is difficult to maintain (remember to edit the code each time you add a new column...).
Moreover it was difficult to fit in this approach the need to execute queries with many joined tables. You need to write code to extract data from each query and an accounting program is likely to use a lot of them.

Clearly this was not a viable solution, so I had to think something better. Having worked a lot in Visual Basic I decided to implement the concept of [updatable] recordset, which is much simpler to use.
Simply create a recordset from a table name or from a SQL query. The recordset will give access to all the referenced columns, and it will have methods to move from row to row. Store something in the columns and a simple command will update the database, creating the needed SQL statements.
This required a good amount of work, but now I can access data from any query in no time.
There are different kinds of recordsets, arranged in a hierarchy: in simpler recordsets you can only move forwards for maximum efficiency, more complex objects can also move backwards.
Recordsets can be read-only or read-write.

I wrote the second test program using this code and it worked well, so I will use it for the accounting program.

Database

Of course an accounting program will use a database, so I had to choose one.

The program is designed for small customers, so most of the time there will be a single user configuration. Other customers will use the program in a LAN, but the number of users will be small. For this reason raw performance is not important, much more important is the ease of installation of the database software. It should also require no administration, since most end users will not be able to do it.
Another requirement is full support of the relational model, to make developing simpler.

Having decided to use open source software I had three choices: Firebird, MySQL and PostgresSQL. I decided for Firebird, mainly for two reasons:

• Firebird is known to be simple to install and not requiring administration, at least for small installations.
• Firebird is also available in an embedded version. Just drop some files in your program's folder and you have a working single user database. No need to separately install the database, no problem with existing database installations. This greatly simplifies setup for single user configurations. The embedded version is officially supported only for Windows, but it is possible to use it also for Linux and OS X.
  

I tested Firebird in a couple of open source programs and I am satisfied, so I keep using it.
At the moment I am still using version 2.0.x, even if version 2.1.x is available. The main reason is that the new version is compiled for Windows with Visual Studio 2005, so it requires a correct setup of the compiler runtime even for the embedded version. I could solve the problem compiling by myself with Visual Studio 2003 but, since version 2.0 is good enough for my needs, I prefer not to waste time. I will probably switch to version 2.5 that will have an embedded server that allows more than one connection.

The server install is available in two different choices: Classic and Superserver. This often causes some confusion for new users. Here are the main differences.

• Classic uses a different process for each user connection. This uses more resources but each process can run on a different core/processor, so this version scales well with multiprocessor servers under heavy load. The different processes cannot share a single memory cache, so each one has its own.
• Superserver uses a new thread for each connection. This uses less resources and it is possible to use a single cache, but the whole server runs on a single core/cpu and it does not use other ones even if they are available.

For normal usage, when the server load is small, the usual advice is: run Classic in Linux and Superserver in Windows. The upcoming version 2.5 will have another configuration better suited for heavy loads.

Framework

Having decided to use C++, the next step is choosing a framework to make it easy to write the program once and compile it under different operating systems.
The main choices are QT and wxWidgets: some time ago I decided to give wxWidgets a try and I am satisfies of it, so I will keep using it.
Before deciding which tools to use I developed a couple of open source programs that used the same tools. I needed them, so they were a good test.

wxWidgets is a large library, so the learning curve is rather steep at first. I can recommend Cross-Platform GUI Programming with wxWidgets by Julian Smart, one of the core designers. It gives a good starting point. Then you can look at the samples: there are many of them. At first they look rather obscure, but this only means that you still have to learn. After some time the core structure becomes clear, and you can concentrate on the details.

You can create the user interface by hand, but in practice you will need a program to help you design it in a simpler way. There are many of them and most are free, but I think that for professional use you should try DialogBlocks. The price is low, it works well and it is much more than an interface designer: it can create the full code of a simple program, and it can handle compilation in different platforms so you can build your program without having to create a Visual Studio project, makefiles and much more. It can also compile wxWidgets for you, saving some time and headaches.

wxWidgets is not Visual Basic. It takes time to really master it and it requires better programmers, but after learning it I think that you can create a program in about the same time as using some RAD tool.

Programming language

First I needed to decide which programming language I will use for this project. Since I want to create a multi-platform program there are not many choices.

The program will be used mostly by very small customers, so I need something very simple, without installation problems. The ideal should be having no dependencies on other components.

I could have used Java, but this requires a big runtime and the user interface does not look very native.

Another option was C#, but that is a Windows/Microsoft based language. I know that there is Mono, but using .NET in Windows and Mono in other OSes would add maintenance problems. Moreover it also requires a large runtime.

To me, the natural choice was C++. That language can be compiled almost everywhere, and it it will probably be the same for a very long time. I will probably do most of the development under Windows, and the Visual Studio IDE is really great.

This leads to the next question: how to create the user interface? The answer in the next post.

First post

Writing the first post of a blog is rather strange: nobody will read it for long time, and maybe nobody will ever read it. Anyway, let's go.

The purpose of this blog is to describe the process of creation of a new accounting program. I already wrote other accounting programs, but this time I have different requirements.

• Multi platform. The program must run under Windows (of course), Linux and OS X. It will even be possible to mix clients with different operating systems in a network.
• Long life. This program must last a very long time, so that I will not have to rewrite it again. This means that I do not want to use proprietary tools, which are subject to change. I will use mainly open source software for this project.

As you can image, this requirements will reduce a lot the available choices.

I am working at this project since some time, so I learned a lot of interesting things. I do not want to forget them, and other people might find them useful, so I will use this blog to save those information.