www.mwasoftware.co.uk

TIBDynamicGrid1

The TIBDynamicGrid is illustrated above using Firebird's example “employee” databases.

In use, it looks just like a TDBGrid and is a TDBGrid descendent. Any project that uses IBX and TDBGrid can thus be quickly converted to using TIBDynamicGrid. The control uses SQL Manipulation to manage column sorting.

The above example can be found in “ibx/examples/employee” and illustrates most of the benefits of TIBDynamicGrid.

  • Resize the form and you will see how the “Dept” column automatically grows/shrinks to ensure that the grid always fills the available space and how the Salary “Total” control (TDBText) moves so that it is always aligned with the grid. Column resizing is controlled at design time by setting the AutoSizeColumn property for each column that it is to be dynamically resized, with its design time width interpreted as the minimum column width. All other column widths remain unchanged.

  • Click on the “Started” column header (or any other column header) and the table will be resorted by that column. A second click on the same header reverses the sort order.

  • Select a row and press “F2”, click on “Edit” or the left hand indicator column and the Editor Panel is revealed (See Illustration 2). This allows the row to be edited free of the constraints imposed by a simple column editor.

  • After reopening the dataset (e.g. after a re-sort or change of filters) the previously selected row is automatically reselected.

  • The filters, such a “salary range”, also illustrate how the new IB SQL Parser works with the TIBDynamicGrid. For example, where a salary range is selected, the dataset is re-opened and the filters are applied in the BeforeOpen event handler.

  • Each row can still be edited without having to open the panel editor. The column “located” is an example of the use of TIBLookupComboEditBox as a column editor. Note that the country list is dynamically generated and varies according to Job Code (an Employee Database constraint).

 TIBDynamicGrid2

Column Properties

Most of TIBDynamicGrid's new features are accessed via the column editor and are properties of each column in the grid. The new column properties are given below.

AutoSizeColumn

Boolean

If true then the column is automatically resized to fill the grid. More than one column can have this property set to true.

ColumnTotalsControl

TControl

Optional. Used to identity a control (typically a TDBEdit or TDBText) to be kept in vertical alignment with the column, and to have the same width. Note that the horizontal positioning is unaffected by grid resize, and hence the total can be placed either above or below the grid.

InitialSortColumn

Boolean

Identifies the column used to sort the grid when the dataset is first opened.

DBLookupProperties

TDBLookupProperties

These properties are copied to a TIBLookupComboBox when it is used as a column editor. Setting TDBLookupProperties.ListSource implictly requests this as the column editor instead of a normal pick list.

If the TDBLookupProperties.DataFieldName is not set then the control works as a “pick list” with its values taken from the List Source DataSet.

If the TDBLookupProperties.DataFieldName is set then it works as full lookup list. The DataFieldName identifies a field in the parent TIBDynamicGrid.DataSource.DataSet. This field does not have to be visible in the grid. When the editor completes, the identified field is set to the value of the List Source field identified by TDBLookupProperties.KeyField.

TIBDynamicGrid New Properties

EditorPanel

TControl

When set, this control (typically a TPanel or TFrame) is used as the Editor Panel (see below).

ExpandEditorPanelBelowRow

Boolean

When set and an editor panel is displayed, the row height is set to the current row height plus the panel height and the Editor Panel placed under the row. That is, the original row is still displayed with the editor panel beneath it. The default is that the editor panel appears to replace the row.

AllowColumnSort

Boolean

Enables column sorting by column header click (default true).

Descending

Boolean

Determines the initial sort order. Default is false i.e. ascending sort order.

DefaultPositionAtEnd

Boolean

Determines the initially selected row when the dataset is first opened. If true then the last row is selected, otherwise the first row. Default: false.

IndexFieldNames

String

This is a semi-colon separated list of one or more dataset fieldnames. Typically this is the primary key for the dataset. Used for automatic reselection of rows after the dataset is reopened.

A property editor is available for design time field name selection.

TIBDynamicGrid new Events

OnBeforeEditorHide

This event is called before the Editor Panel is hidden. Can be used to validate changes.

OnEditorPanelShow

This event is called after the Editor Panel is made visible

OnEditorPanelHide

This event is called after the Editor Panel is hidden. Can be used to do any additional tidying up needed.

OnKeyDownHander

The TIBDynamicGrid uses a KeyDown handler to intercept edit keys while the Editor Panel is active. For example, to process an “escape” key as a cancel edit. You can write your own keydown handler to modify this behaviour.

OnColumnHeaderClick

Called when a column header is clicked and before the dataset is re-sorted. Can be used to modify the column index for the sort.

OnUpdateSortOrder

Called when the dataset select SQL is being modified prior to resorting the dataset. Can be used to modified the SQL “Order by” clause. e.g. to add a subsort column. For example, useful when one column has a “year” and the next column is the “month”. Clicking on “year” can then made to subsort on “month”.

The Editor Panel

An Editor Panel may be any TControl available on the form. However, in practice, it is typically either a TPanel or a Tframe. The example shows a TPanel being used as an Editor Panel.

You can create an Editor Panel by simply dropping it on to the same form as the TIBDynamicGrid and then selecting it as the value of the TIBDynamicGrid.EditorPanel property.

To be useful, the Editor Panel should be populated with data aware controls that use the same DataSource as the grid and are individually used to edit fields in the same row. The height of the panel should be the minimum necessary as this will determine the row height when it is visible.

At run time, the Editor Panel is automatically hidden until called into use by either:

  1. Pressing “F2” when the Dynamic Grid has the focus.

  2. Clicking on the left hand indicator column, or

  3. Calling the TIBDynamicGrid.ShowEditorPanel method.

In order to show the editor panel, the following actions are performed by the TIBDynamicGrid:

  • The current row is resized to the height of the Editor Panel.

  • The Editor Panel is resized and repositioned so that it fits exactly over the current row.

  • The Editor Panel is made visible.

The current row can now be edited using the child controls on the Editor Panel – that is as long as their DataSource is the same as the grid's.

The Editor Panel is hidden (and any changes Posted to the DataSet) when:

  1. A different row is selected by the mouse or up/down arrow keys

  2. The Escape Key is Pressed (cancels the changes)

  3. “F2” is pressed.

  4. The TIBDynamicGrid.HideEditorPanel method is called.

Once the Editor Panel is hiddent, the current row is re-sized back to its correct height.

IBX 1.2 introduces a new Component Palette entry "Firebird Data Controls". This has five new data aware controls dependent on IBX and which make use of the SQL Parser also introduced in IBX 1.2.

These controls are available as part of the IBX2 download and have a minimum requirement of Lazarus 1.6.0.

These are:

TIBLookupComboEditBox is a TDBLookupComboBox descendent that implements "autocomplete" of typed in text and "autoinsert" of new entries. Autocomplete uses SQL manipulation to revise the available list and restrict it to items that are prefixed by the typed text (either case sensitive or case insenstive). Autoinsert allows a newly typed entry to be added to the list dataset and included in the available list items.

TIBDynamicGrid is a TDBGrid descendent that provides for:

      • automatic resizing of selected columns to fill the available row length

      • automatic positioning and sizing of a "totals" control, typically at the column footer, on a per column basis.

      • DataSet resorting on header row click, sorting the dataset by the selected column. A second click on the same header cell reversed the sort order.

      • Support for a "Panel Editor". That is on clicking the indicator column, the row is automatically expanded and a panel superimposed on it. The panel can have any number of child controls, typically data aware controls with the same datasource as the grid allowing for editing of additional fields and more complex editors.

      • Reselection of the same row following resorting.

      • A new cell editor that provides the same functionality as TIBLookupComboEditBox. Its properties are specified on a per column basis and allows for one or more columns to have their values selected from a list provided by a dataset. Autocomplete and autoinsert are also available. The existing picklist editor is unaffected by the extension.

TIBTreeView is a data aware TCustomTreeView.

TDBControlGrid is a lookalike rather than a clone for the Delphi TDBCrtlGrid. TDBControlGrid is a single column grid that replicates a TWinControl - typically a TPanel or a TFrame in each row. Each row corresponds to a row of the linked DataSource. Any data aware control on the replicated (e.g.) TPanel will then appear to have the appropriate value for the row.

TIBArrayGrid is a data aware control derived from TCustomStringGrid and which may be used to display/edit the contents of a one or two dimensional Firebird array Field.

Examples are provided to illustrate the use of the new controls

Alternatively, why should you use IBX rather than the alternatives available for using a Firebird Database under Lazarus and, indeed, why should you use Lazarus in the first place. Perhaps, more to the point – why do I use Lazarus and why did I go to the bother of porting IBX to the Lazarus environment?

In the last Millennium

When Delphi first appeared, it was a great leap forward in rapid application development for GUI based programs. Windows 3.1 had revolutionised the PC Desktop (remember DOS?) and Delphi 1.0 made applications development easy. The 32-bit Delphi 2 quickly followed and kept pace with the new 32-bit Windows 95, and I started developing applications for this new market.

One of the most important applications I worked on was a Database Client application for a large database of training materials . This started out using Paradox tables but rapidly outgrew their limitations and the obvious step up was to move to InterBase – obvious because it was packaged with Delphi and the support was there through the Borland Database Engine (BDE).

But the BDE was never an ideal way to access an InterBase Database. Rather clunky and slow, its limitations were also rather obvious and, in the nick of time, along came InterBase Express (IBX), again packaged with Delphi

Based on a licensed copy of “Free IB Components”, this seemed to provide almost the perfect marriage between Delphi and InterBase. Without all the overhead of the BDE, IBX still provided the TDataSet data access model (and thus avoided to serious a rewrite) while also giving direct access to the database, allowing the full use of InterBase rather than being limited to the BDE's abstraction.

The programming model was thus established of Delphi, IBX and InterBase.

In the Last Decade

In 2000, Borland/Inprise, released both InterBase and IBX codebases as Open Source Products. The InterBase code was quickly picked up, polished and reborn as the Firebird Project – although no one seemed to pick up on IBX in the same way, and perhaps this itself was due to the mixed signals that Borland/Inprise was sending out.

The general assumption was that the release of the codebase was a way of closing down InterBase without causing too much of a stir amongst its loyal user base. However, this never happened. Borland continued to release new versions of InterBase as a licensed product and continued to develop IBX as part of Delphi – and with no commitment to support the Firebird API! I still don't understand what they were trying to achieve.

For those of us working now with Delphi/IBX/Firebird this created a fair amount of uncertainly about the future.

What's more, Borland had also seemed to take its eye off the ball with regard to multi-platform support. Kylix was a first attempt at providing a cross-platform version of Delphi for Linux, but after version 3, Borland seemed to lose faith in it and there have been no more versions. For those of us who see Linux as an increasingly important market in itself, along with a desire to get into the Apple Mac area – and what about all those smartphones and touchpads – being limited to Windows is a little bit frustrating

With further changes of ownership of the product, first to Codegear and then to Embarcadero, you also start to wonder about what would happen if Delphi development just ceased and what would happen to all the software development investment that I had made in Delphi based software.

The search for an Alternative

There was thus a need for a Plan B. That is to look for a development suite that was truly multi-platform and which would preserve the existing investment in the Delphi/IBX/Firebird software.

I first came across Lazarus in early 2010. It seemed to meet many of the objectives of Plan B. Multi-platform, Delphi compatible (mostly), but did not have IBX. The then version (0.9.28) with Free Pascal 2.2.4, I also felt was not quite there yet. Worth keeping an eye on but not yet ready to commit to.

This view changed in late 2010 with the 2.4.2 compiler and the Lazarus SVN 0.9.29 version. This had the right feel to it and it looked like it was worth putting in some investment to see if it really was Plan B.

The big problem was no IBX, but at least there was candidate software in the shape of the original 2000 Open Source version. The choice I had was either to port t this version and bring it up-to-date, or to migrate the existing Delphi software to another piece of database middleware. As the first project I wanted to port had 65,510 lines of Pascal source (with a COCOMO estimated development cost of $2M), with IBX used in just about every unit, a change to another database middleware was not an easy option to justify.

And, let's face it, I chose to port IBX because I rather like it. If you search internet discussion groups, and especially those with a Firebird link, you will find some acerbic comments about IBX. However, you should also remember that the lack of commitment to Firebird does not exactly help endear IBX to Firebird users . (Hopefully with IBX for Lazarus now fully committed to the Firebird API that should now change).

I like IBX because it does not attempt to hide the Database API. Instead, it encapsulates the API in an object oriented framework (TIBSQL) which it then uses to support the TDataset model. Some may prefer a Database Middleware that tries to hide SQL and Database transactions from the user. I don't. I want full control over the Database API and that's why I like IBX.

Anyhow, porting the 2000 Open Source release of IBX to was itself an interesting project and would help me get to know Lazarus in detail and assess whether it really was a viable alternative to Delphi.

Creating IBX for Lazarus

In the end, the porting of IBX probably took about two man months of effort. There are differences between the Delphi VCL and the Lazarus LCL that needed to be reflected in the code. Work was also needed on the API to ensure that the 64-bit Firebird API was also supported. I am also not convinced that the original 2000 IBX ever worked properly in respect of Firebird events. It needed almost a complete re-write to get it working with the Firebird Event API – and I must say that here studying the C++ IBPP code helped enormously.

As regards to the core code, the biggest challenge was to port the ISQLMonitor unit. This used Windows IPC to allow one process to monitor the SQL Events in another. Totally platform dependent and would never work on Linux as it stood. I did think about dropping it from the package – and indeed, I did exclude it from the first development version. However, it was also a challenge that I could not resist: to analyse the algorithm, separate out the platform dependent code and write an alternative module using Unix IPC for Linux and OSX. ISQLMonitor was thus back in the package.

Otherwise, the main body of new work was for the property editors. Lazarus IDE property editors are similar to Delphi – but not that similar – and anyway, if there was one thing I did not like about IBX, it was the very limited property editors that came with it. I thus completely re-wrote this part of the package.

The IBX for Lazarus Property Editors now include a simple query builder capability but, to me, the really important addition is the “Test” button now on every property editor. To me this represents a real productivity gain.

The “Test” button passes the SQL statement in the property editor to the Firebird RDMS and executes a “prepare” statement on it. This gives a proper syntax check at the Firebird level, and ensures that at least the SQL syntax is correct before the property editor is closed. Before, with IBX for Delphi, I had to compile a program and then test the program to the point at which the statement was executed before I could be sure that the syntax was correct. Although, I did also have Flame Robin open at the same time, and used this for syntax checking, Flame Robin does not recognise the IBX parameter syntax and hence could not be used to completely guarantee correct syntax.

As a result of this work, I now had a functioning version of IBX for Lazarus, but perhaps more importantly had got to know Lazarus in detail and the more I used it the more impressed I became, and this was not withstanding the several LCL bugs I found (and fixed) while doing the conversion.

Lazarus is not the same as Delphi but it is similar and easy to use when you are familiar with Delphi – but the really impressive bit is the platform independence aspect.

I developed IBX for Lazarus on my own Desktop which is a Ubuntu 10.4 64-bit system. Under Linux I  run various versions of Windows for development and test in individual Virtual Machines. Having developed and tested the software under Linux, I then moved it to 32-bit Windows VM, recompiled it, and, apart from a few syntax errors in the platform dependent code of ISQLMonitor, it ran with hardly any effort. And, 32-bit Linux needed no extra work at all.

Perhaps what is even more impressive is that with no MAC OSX experience, I never even tried to make it OSX compatible. However, having released the source code back into the community, the reports came back that with only minor changes (most notable being the name of the Firebird library), IBX for Lazarus compiled and ran on MAC OSX. Now, if that is not a superb testimonial to Lazarus and Free Pascal, I don't know what is.

Back to My Project

With the positive experience of IBX I now moved on to the real job of porting my 65K lines of code to Lazarus. This again took place in an impressively short time of around two man months. Here the main complication was the need to move from Quick Reports to LazReports and where much of time went – which included finding and removing the many bugs in LazReports. There was also some self-inflicted increases in the effort by deciding to, at the same time, integrate the use of ImageMagick to improve image handling, and to develop a common abstraction for both Sane and Twain Scanner interfaces.

The results though were as impressive as with IBX for Lazarus. What worked on Linux compiled and ran on Windows without further effort. The GTK2 interface on Linux looks great and the performance of compiled software is very impressive. I'm hooked.

Plan B is no longer Plan B. It is Plan A and the future is Lazarus.

Releasing IBX for Lazarus

Having ported IBX to Lazarus, I wanted to give something back to community that gave me a great product and so I released the updated software as the Open Source IBX for Lazarus. However, there is more than just altruism here.

I want to see Lazarus and Firebird continue to go on and improve. It is my interest that they do as they are now Plan A. Giving IBX back to the community should help achieve that goal. Lazarus/IBX/Firebird is now a great combination for multi-platform Rapid Application Development, and I hope that as many Software Developers as possible pick it up and use it.

And also..

I couldn't finish without mentioning yet another impressive Lazarus/Free Pascal feature.

I have for some time now used chroot environments, each set up with the appropriate libraries to build for multi-versions of Linux (both 32 and 64-bit) on the same computer. The Free Pascal Compiler can also be compiled as a cross-compiler so that it can, for example, build a Windows executable on a Linux platform. I've done that and added it to the list of chroot environments. Moreover, the Microsoft WIX toolkit also runs under WINE on Linux and can be used to build Windows Installer packages under Linux. Thus with a little bit of shell scripting, I can literally just press a button and have my application suite compiled and packaged as both .deb packages for Linux and as a Windows Installer self-expanding .exe, all from the same source on the same system. Impressed – I was.

As for Delphi

I don't begrudge the many thousands of pounds I have spent over the years in Licence Fees for Delphi with Borland/Inprise/Codegear/Embarcadero or what they call themselves now. It was a great product. But, by failing to develop a Multi-platform strategy it is just too limited for me. The future belongs to Lazarus.

 
Tony Whyman, June 2011