Monday, May 31, 2010

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.

No comments:

Post a Comment