Creating IBX2

IBX for Lazarus has grown significantly from its first release in 2011. An SQL Parser, an SQL Script Engine and enhanced controls have all been added as incremental improvements. Firebird has also had a new API added with the intention of this being a long term replacement for the legacy API; the legacy API whilst still available in Firebird 3, will not support new features.

There was thus a need to update IBX to support the new API and it was also recognised that this would also be a good time to review all the changes that had been made to IBX, as well as creating improved documentation. The result of this is version 2 of IBX for Lazarus (IBX2).

Support for the new API

The new Firebird API needed to be introduced in such a way that it would not break existing implementations nor compromise IBX's well proven reliability and performance. It was also important that versions of IBX would continue to be available to support the legacy API as operational use of Firebird 2.x will not go away in the short term.

• If existing applications were not to be broken then the changes to the public properties and methods of IBX components needed to be minimal.

• The need to ensure continuing reliability demanded that a comprehensive test plan be available and which could be used to ensure that the use of the new API was no different to that of the old API, and

• The continuing need to support the legacy API meant that the codebase would need to support both versions.

The Evolving Firebird API

The Firebird legacy API has been around for a very long time. It follows the classic structure of a 'C' based API and is based on function calls with “opaque handles” used to identify context information, such as the database connection being used, or the SQL statement being executed. The SQLDA - a shared data structure (shared between the API user and the API provider) is used to pass input and output SQL data in formats that are well defined but not necessarily the same as those expected for Pascal Data types. Most API calls return an integer value that is used to report an error with a “Status Vector” used to give detailed diagnostics.

The legacy API also makes extensive use of “parameter blocks”. These are data structures that pack parameters into parameter buffers in an approximate type length value format, but with many variations.

On the other hand, the new API follows a sort of object oriented design. The “sort of” comes from the observation that while the API has been designed in C++ and looks like a set of C++ object classes, you can't simply publish a C++ class structure and expect it to be directly usable from any programming language. Instead, the published API ends up as a set of tables of function pointers (c.f virtual method tables) with each such table relating to a C++ class. Each function is then called with an extra data item (in C++ the value of the this variable). Error handling is more complex as interface calls often return a reference to a new object instead of an error code, with error reporting provided by a separate interface call.

The new API also takes a very different approach to passing SQL statement input and output parameters. Gone is the SQLDA and, instead, input and output parameters are passed in packed data buffers, with a supporting set of API functions to pack and unpack those buffers.

In contrast, the various parameter block formats are unchanged.

An exported C++ interface is more difficult to use in another programming language than an exported 'C' interface. However, the Firebird developers have published a Pascal unit which defines this interface, which at least kick starts the work.

Introducing the new API into IBX

The perceived need to continue supporting the old API meant that some strategy had to be evolved to allow the two APIs to be implemented alongside each other in the IBX codebase.

The original IBX code makes direct use of the legacy Firebird API. Much of this is concentrated in the IBSQL unit which manages statement execution and data transfer. The IBDatabase unit also accesses the database connection and transaction management parts of the API, whilst other units such as IBEvents, IBBlob and IBServices use the Event Management, Blob and Service Manager parts of the API respectively. Occasional use of the Firebird API can also be found in other units.

Conditional compilation is one strategy for introducing the new API alongside the old. However, there are considerable differences in style and use between the two APIs and the result could easily be a hard to understand mess that would not aid the goal of reliability or even good coding.

The alternative approach was to create an abstract interface to the Firebird API that could be fulfilled either by the use of the legacy API or by use of the new API. IBX could then use this abstraction rather than the actual API which would at least give clarity to the code. It would then also be possible to let the user select which API would be in use or to even make the choice dynamically. For example, using the new Firebird API when available and falling back to the old API when not.

Two approaches to achieving this were considered. Once approach is to define an abstract set of classes for which implementations could be available for either API. The other is to use Pascal Interface types. That is to defined the API abstraction as a set of Pascal interfaces which could then be separately provided for each API by an appropriate set of classes for each API.

The Free Pascal Compiler allows such interfaces to be defined using either the CORBA or COM models. In the former case, the interface user is responsible for disposing of interfaces once they are no longer needed while, in the COM model, the interfaces are reference counted (like large strings or dynamic arrays); they are disposed of automatically once they go out of scope.

In practice, there is little real difference between an abstract class definition and a CORBA interface. The definitions are very similar, while the CORBA interface approach is arguably cleaner as it avoids repetitive use of the “abstract” key word. However, the automatic reference counting of the COM interface adds a new dimension to their use. The implementation of a COM interface can be more challenging for the programmer as circular references between the classes implementing the interface must be avoided if the interfaces are to be disposed of at the appropriate time. On the other hand, for the user, they can be easy to use and allow for some very elegant code constructs.

After some experimentation, an early decision was made to base the abstract interface to the Firebird API on the Pascal COM interface model.

The Firebird Pascal API Interface

The following gives an example of the power of Pascal COM interfaces and how they, for example, enable embedded SQL statements, such as:

MyAttachment.ExecuteSQL([isc_tpb_write, isc_tpb_nowait,isc_tpb_concurrency],
   'Execute Procedure DELETE_EMPLOYEE ?', [8]);

and

writeln('Employee Count = ',
   MyAttachment.OpenCursorAtStart('Select count(*) from EMPLOYEE')[0].AsInteger);

In the first case, “ExecuteSQL” is a function provided by the IAttachment interface (which provides access to a Firebird Database Connection). This is used to execute a stored procedure using a transaction created solely for this purpose with the given transaction parameters. The value “8” is given as the value of the positional variable. A constant is shown here for clarity, however, in practice this could also be a variable.

The second example is used to report the result of an SQL Query which returns a single integer value.

The value of reference counted interfaces is particularly pertinent to the second example. Here, the “OpenCursorAtStart” function is used to execute a query and returns a Pascal Interface (IResults) which, as the name implies, gives access to the query results. This interface has a default array property which allows access to each of the output parameters by position. With only a single output parameters the “[0]” index selects the first and only output parameter. This is also returned as an interface (ISQLData). The result type is known a priori to be an integer and hence this interface's “AsInteger” method is used to return the result.

As the IResults and ISQLData interfaces are not used after this statement they are automatically disposed of after use. On the other hand, if they were CORBA interfaces, the programmer would have to place each in a temporary variable so that they could then be explicitly disposed of afterwards. A much more “clunky” approach.

Interface Definition

When defining the interface, much of it writes itself. There has to a root interface (IFirebirdAPI) that provides access to the interface as a whole. There are also interfaces to a database connection (IAttachment), transactions (ITransaction), events (IEvents) and the Service Manager (IServiceManager). These interfaces provide functions that correspond closely with functions provided by both the legacy API and the new Firebird 3 API. There is very little real variation between the two APIs here and the parameter blocks are identical.

The parameter block format used by Firebird is nothing but inconsistent between the different API calls. However, the abstract API hides all of this by presenting a parameter block API that uses Pascal types to define the parameters and hides, behind the scenes, the encoding differences between the different API calls.

A similar approach is taken to resolving the major differences between the two Firebird APIs when it comes to dealing with SQL input and output parameters. The abstract API provides the IStatement interface in order to provide access to a prepared SQL Statement. The IResults interface to access the results of a query; the ISQLParam interface to access each input parameter; and the ISQLData interface to access each output parameter. This abstraction is then mapped on to the SQLDA for the legacy API and on to the packed parameter buffer for the new API. The Pascal interfaces also present the input and output parameters as Pascal typed data.

The result is a common abstract API for both Firebird APIs. This interface provides its added value by:

• Presenting a non-native (Pascal) API as a Pascal API with strongly typed data.

• Decoupling the user from the Firebird API and hence enabling a common programming model independent of which Firebird client API is in use at any one time.

• Future proofing against further changes to the Firebird API. Any such changes would only affect the interface implementation and not its use.

Exposing the Pascal Interface

The original intention was that the Firebird Pascal API would be an internal interface used only by IBX and to enable IBX's use of both legacy and new Firebird APIs. However, it quickly became clear that this was a powerful interface in its own right providing a common set of Pascal Language Bindings for Firebird. As indicated by the above example, it also enables a form of embedded SQL that is much simpler and more elegant that using the TDataset model.

The Firebird Pascal API (fbintf) thus became a separate package. An integral part of IBX but also available as a standalone component. It is also fully documented with its own comprehensive user guide.

Confidence Testing

IBX has a well deserved reputation for reliability and the introduction of the Firebird Pascal API should not undermine this.

A comprehensive test plan was thus prepared for the package. This is designed to test 104 identified features with sixteen individual tests. The tests are run in console mode and as a single batch with automated pass/fail results comparison. This both provides a basis for regression testing and to ensure that the two implementations (legacy and new Firebird APIs) deliver the same results under test conditions.

Memory leaks are also a major issue with reference counted interfaces as the user is relying on the underlying software to ensure that interfaces are correctly disposed of when no longer required. The test plan also includes features to ensure against memory leaks. Two approaches are taken:

1. The FPC heaptrc unit is active during the tests to provide a diagnostic check against memory leaks. The test plan is only considered passed if no memory leaks are reported.

2. A compile time directive is used to enable a built-in debug mode that counts and reports all interface creation and disposal. This provides both a diagnostic trace that can identify the reason for any memory leak and a confidence check that all created interfaces have been disposed of before the program ends (interface count = 0).

Use by IBX

The IBX codebase has been modified to make sole use of the Firebird Pascal API replacing use of the legacy API and any dependencies on it. From some units (e.g. IBDatabase) this was little more than replacing the legacy API call with the fbintf equivalent. However, in the case of the IBSQL unit the change was much more radical. IBSQL was very dependent on the old API and the shared SQLDA data structure and this had to change. In practice, much of the old IBSQL code migrated to the legacy API implementation of the fbintf with the TIBSQL class now largely becoming a wrapper for the new API.

The interfaces provided by the fbintf are also exposed by IBX classes. For example, the IAttachment interface is available as a public property of the TIBDatabase class, and the ITransaction interface is similarly available as a public property of TIBTransaction. This allows the IBX programmer to make direct use of the fbintf interfaces alongside traditional use of IBX, using the same transactions and database connections.

Performance Optimisation

One of the identified risks of abstracting the Firebird API to a separate Pascal interface instead of direct use was that the additional overhead would reduce performance. Particular attention was thus paid to minimising path lengths within fbintf for common operations and hence to minimise the impact. The implementation also includes the means to cache interfaces (e.g. ISQLData) in order to avoid the overhead of creating and disposing of these interfaces each time a row is processed. This part of the implementation needed very careful design to guard against failure to dispose of the interfaces once (e.g.) an SQL Statement has closed and is where the testing to ensure against memory leaks became so important.

Performance testing also revealed some long standing inefficiencies within the original IBX code, especially as regards buffer management. With a unidirectional cursor, the TDataset model requires that a TDataset subclass, such as TIBDataset, caches all rows in memory as otherwise, controls such as a Data Grid will not be able to pass backwards and forwards through the dataset. The way that the caching was working resulted in two performance issues:

• The importance of the “Buffer Chunks” property was never properly highlighted before. This determines the increment by which the buffer is increased every time it is exhausted. Set it too small then for a very large dataset, the overhead of reallocating the buffer pool dominates the performance overhead. On the other hand, set it too large then small datasets each claim too much memory. The documentation now makes clear how important this parameter can be.

• IBX used to use its buffers to hold both dynamic information about each row and some static information that was common to all rows. For large datasets this created extra overhead per record both in terms of memory use and data copying. This has now changed to hold static information separately, resulting in both a reduced memory footprint and in avoiding the overhead of data copying.

Other Changes

A general code review was also planned for IBX2. This resulted in:

1. The backup and restore services offered by the Service Manager now include backup and restore using files held on the client system or the server. Previously the files had to be on the server system. This capability had been offered by Firebird for some time but had never previously been available through IBX.

2. Firebird arrays are now supported. The Firebird Pascal API was developed as a full implementation of the Firebird API including arrays and this capability was made available through IBX. This resulted in the specification of an array field class (TIBArrayField) so that arrays can be manipulated in the same way that any TDataset field can be manipulated. A new IBControl (TIBArrayGrid) has also been developed from a TCustomStringGrid. This is a visual component that supports both one and two dimensional Firebird arrays allowing use to be almost as easy as using a string field.

3. The IBExtract unit was reviewed and brought up-to-date and now supports all Firebird SQL extensions through to Firebird 3. The order in which database metadata is exported was also modified to ensure that metadata dependencies are always satisfied when extracted metadata is used to recreate a database. This is particularly important for stored procedures that update writeable views.

4. A similar review was performed for the IBX Script Engine ensuring that it can handle all Firebird SQL extensions.

5. A data export capability was added to IBExtract for both sequences (generators) and table data. It also supports the export of binary blob data and arrays using an XML like format for exporting such data. The IBX script engine also supports these data formats when embedded in (e.g.) INSERT statements and hence it is now possible to use TIBExtract to dump an entire Firebird database including both metadata and table data to a text file and then recreate the database using the IBX Script Engine.