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.

Comprehensive documentation is available for both the IBX2 package and the Firebird Pascal API. These are made available as PDF documents that can be downloaded and viewed in your web browser or saved for local use. The documents are fully indexed and searchable. Hint: in the Chrome PDF viewer use "bookmarks" to navigate the document, while in Firefox, select "Toggle Slidebar" and select "show document outline".

• View the IBX For Lazarus User Guide

• View the Firebird Pascal API Guide

• View the Firebird Server Management Using IBX User Guide

TIBArrayGrid is a visual control that can be linked to a TIBArrayField and used to display/edit the contents of a one or two dimensional Firebird array. It may be found in the “Firebird Data Controls” palette.

To use a TIBArrayGrid, simply drop it onto a form and set the DataSource property to the source dataset and the DataField property to the name of an array field. The grid should then be automatically sized to match the dimensions of the array.

Note that the array bounds can be refreshed at any time in the IDE, by right clicking on the control and selecting "Update Layout" from the pop up menu.

At runtime, the TIBArrayGrid will always display/edit the value of the array element in the current row. If this element is null then the array is empty. However, data can be inserted into an empty array. When the row is posted, the field will be set to the new/updated array.

Properties

Most TIBArrayGrid properties are the same as for TStringGrid. The following are specific to TIBArrayGrid. Note that you cannot set the Row or column counts directly as these are always set to match the array field.

Public Properties

ArrayIntf	Provides direct access to the array itself.
DataSet	The DataSet provided by the DataSource (read only).
Field	The source field

Published:

DataField	The name of the array column.
DataSource	The data source providing the source table.
ReadOnly	Set to true to prevent editing
ColumnLabels	A string list that provides the labels for each column in the grid. Provide one line per column. If non empty then a column label row is created as a fixed row at the top of the grid.
ColumnLabelAlignment	Sets the text alignment for column Labels
ColumnLabelFont	Sets the font used for column labels
RowLabels	A string list that provides the labels for each row in the grid. Provide one line per row. If non empty then a row label column is created as a fixed column to the left of the grid.
RowLabelAlignment	Sets the text alignment for row Labels
RowLabelFont	Sets the font used for row labels
RowLabelColumnWidth	Width of the Fixed Column used for row labels.
TextAlignment	Alignment of all cells other that those containing labels.

Examples

Example applications are provided for both one and two dimensional arrays. In each case, the example applications create their own database and populate it with test data when first run. Note that you will typically need to run the application before accessing database properties in the IDE. This is in order to create the database referenced by the IDE.

The TIBDatabase property “CreateIfNotExists” is set to true in both examples. This means that if the database does not exist when an attempt is made to connect to it then the database is created. After it is created, the “OnCreateDatabase” event handler is used to add a table to the newly created database and to populate it with test data. The application then continues as if the database already existed.

By default, the database is created in the defined temporary directory. This behaviour can be overridden by editing the example's “unit1” unit to remove the “{$DEFINE LOCALDATABASE}” directive and setting the const “sDatabaseName” to the required path e.g.

const

sDatabaseName = 'myserver:/databases/test.fdb';

1D Array Example

 A screenshot from this example program is illustrated below.

In this case, the test data table is defined as

Create Table TestData (
RowID Integer not null,
Title VarChar(32) Character Set UTF8,
MyArray Double Precision [1:12],
Primary Key(RowID)
);

Each row includes a floating point array with twelve elements. In the example application, the table is displayed and edited using a DBControlGrid. The title field is interpreted as a “Department” and displayed using a TDBEdit control. The array field is interpreted as sales by month and displayed as a one dimensional TIBArrayGrid with column labels. The example allows both the Department Name and monthly sales values to be updated and changes saved. New rows can be inserted and existing rows deleted.

2D Example

A screenshot from this example program is illustrated below.

In this case, the test data table is defined as

Create Table TestData (
RowID Integer not null,
Title VarChar(32) Character Set UTF8,
MyArray VarChar(16) [0:16, -1:7] Character Set UTF8,
Primary Key(RowID)
);

Each row includes a two dimensional string array with indices 0..16 and -1 to 7. The grid interprets the first index as a column index and the second as a row index (i.e. x,y Cartesian co-ordinates).

The example program displays a row at a time with a navigation bar providing the means to scroll through the dataset, as well as saving or cancelling changes, inserting and deleting rows.

This example illustrates the use of both column and row labels.