The TIBLocalDB Support Component

TIBLocalDB is non-visual component supporting a TIBDatabase and intended to simplify the use of the embedded firebird server for Personal Database Applications, on both Linux and Windows platforms. The TIBLocalDBSupport component supports GUI programs, while the TIBCMLocalDBSupport provides the same support for console mode programs. Example applications are provided for both GUI and console mode in the ibx/examples/local-employeedb directory.

When enabled, TIBLocalDBSupport provides:

To use the component, simply drop it onto a form or data module and link it to the TIBDatabase.

Properties

Note that at design time paths may use '/' or '\' as directory separator. At run time, they must be specified using the appropriate Directory Separator for the current platform.

Events:

Shared Data Directory

The shared data directory is the base directory for all static data files used by TIBLocalDBSupport. This is determined as follows:

Note: that the <application name> is taken from sysutils.ApplicationName and defaults to the filename of the application executable less any extension.

DatabaseName, and login parameters management

When TIBLocalDBSupport is in use, the TIBDatabase.DatabaseName property is ignored and instead, it is generated algorithmically as:

The “DatabaseName” comes from the TIBLocalDBSupport.DatabaseName property.

The “VendorName” comes from the  TIBLocalDBSupport.VendorName property. If the TIBLocalDBSupport .VendorName property is left empty then Sysutils.VendorName is used. If this is empty then no VendorName component is present in the path.

Note the use of a hidden directory under Unix.

If the generated DatabaseName is not appropriate then the TIBLocalDB.OnGetDatabaseName event handler gives a chance to inspect it and change it to something different.

The Database Params are copied from the TIBDatabase component except that the “user_name” and “password” parameters are removed if present. When running under Windows, the “user_name” is then set to “SYSDBA” and the “password” to “masterkey”. Under Unix, these parameters are omitted.

Database Initialisation

When the linked TIBDatabase connected property is set to “true”, TIBLocalDB generates the DatabaseName (as described above) and then if it does not correspond to an existing file, TIBLocalDBSupport uses the Firebird Services API to create the database file from an “empty database” archive in gbak format. In practice, the archive can contain both the database metadata and initial table data.

The “empty database” archive is given by the TIBLocalDBSupport, EmptyDBArchive property. This should be a filename (usually with the .gbk extension) and may include an optional path.  Relative paths are interpreted as relative to the shared data directory.

The Services API is then used to create the initial database from this archive. An error is raised if the archive is not present.

The local database can be re-initialised at any time by calling the TIBLocalDBSupport.NewDatabase method.

Saving the Current Database

The current database contents can be saved at any time by a call to TIBLocalDBSupport.SaveDatabase. The filename for the archive can be provided in the method call. If empty, then the user is prompted to enter a filename (default extension .gbk).

The Services API is then called to archive the database to the specified file in gbak format.

Restoring the Database from an Archive

The local database can be overwritten (restored) from any archive in gbak format (including those saved using the SaveDatabase method) by calling the TIBLocalDBSupport.RestoreDatabase method. The filename for the source archive can be provide in the method call.  If empty, then the user is prompted to locate the file.

The Services API is then called to restore the local database from the archive.

Database Schema Upgrade

A Software Application Update can also require a corresponding update to the database schema. With embedded Firebird server applications where the user may not even be aware that a database server is in use, it is important to have a means to field upgrade the database schema in as seamless and automatic a manner as possible. TIBLocalDBSupport supports a suitable mechanism using the TIBXScript engine.

The underlying idea is that the database schema comes with a version number given as a single integer. The first version to be released is version 1, the second is version 2 and so on. The current schema version number must be saved as data somewhere in the database. As this is database schema dependent,  TIBLocalDBSupport does not know how to determine the current database schema number and instead relies upon the application responding to the OnGetDBVersionNo event.

Each version of an application will have a maximum and minimum version of the database schema that it can support, and it is expected to check that the schema version is acceptable in its TIBDatabase OnConnect handler. However, before this handler is called,  TIBLocalDBSupport will itself check the current schema version against its RequiredVersionNo property (which should be set to the maximum supported schema version no).

The schema upgrade rules are read from the upgrade configuration file. This is a text file in “ini” file format with the following sections:

[status]

This should have a single named value “current” giving the current database schema number as in integer e.g.

current = 2

This should normally be set to the same value as the RequiredVersionNo property and acts as a check to ensure that both are in sync.

[Version.nnn]

Where nnn is an integer with leaving zeroes. For example, “Version.002” is the section read to upgrade the database schema from version 1 to version 2. This section can contain the following named values:

Name

Type

Use

Upgrade

string

Name and optional path to the SQL script used to perform the upgrade. May either be absolute path or relative to the upgrade configuration file. Either forwards or back slashes may be used as the path delimiter.

Msg

string

Text message displayed in progress dialog while script is active.  Defaults to “Upgrading Database Schema to Version nnn”.

BackupDatabase

yes/no

If present and set to “yes” then a database backup in gbak format is made before the upgrade is performed. The backup file is located in the same directory as the database file and is given the same name as the database file with the extension replaced with “.nnn.gbak”. Where “nnn” is the current schema version number (i.e. prior to running the upgrade script).

<Parameter Name>

string

Name and optional path to binary data file. May either be absolute path or relative to the upgrade configuration file. Either forwards or back slashes may be used as the path delimiter.

 

For example:

[Version.002]

Msg = Upgrading to Version 2

BackupDatabase = yes

Upgrade = patches/02-patch.sql

mugshot = images/man.png.gz  

Note that in the above, “mugshot” is intended to be used to resolve an Update, Insert or Delete query parameter in the 02-patch.sql file. E.g.

Update EMPLOYEE Set Photo =:MUGSHOT Where Emp_no = 2;

This is only applicable to BLOB columns and the above is interpreted as update the EMPLOYEE table where the Emp_no is “2” and set the value of the Photo column to the binary data contained in the file “images/man.png.gz”. The “.gz” extension is recognised as a gzip compressed file and decompressed before updating the table.

When the current database schema is more than one version number less than that required, the upgrade rules are applied iteratively to upgrade the database to the required schema version.

Local EmployeeDB Example

The purpose of this example is to demonstrate the use of the TIBLocalDBSupport component. This component is used with a TIBDatabase when the database is accessed using the Firebird Embedded Server. TIBLocalDBSupport takes care of checking the environment and setting up FIREBIRD environment variables and DB parameters. It also supports initialisation of the local database from an archive in gbak format, plus save and restore of the local database. It can also run SQL scripts to upgrade the database schema when a new software version is released.

The example can be found under: ibx/examples/local-employeedb/project1.lpi

See also console mode.

Before compiling and running the example, the Firebird embedded server must be installed:

Under Linux:

Under Windows:

Download the Firebird Embedded Server from http://www.firebirdsql.org/en/firebird-2-5-5/ and extract the contents of the archive into the example directory i.e. ibx\examples\local-employeedb

Running the application

The example should just compile and run. An archive of the Firebird example employee database is provided with the example. This will be used to create the initial database. It should then be automatically upgraded to "version 2" using the scripts provided in the "patches" directory. (see also the file upgrade.conf).

Note that you will not be prompted for a username/password. The embedded server uses normal file permissions to control access. Otherwise you can edit the employee database as in the client/server version.

The local database will be created in:

The File menu provides actions to save the current database to a gbak format archive, restore it again (replacing the current database) or to restore the database to its initial state.

Console Mode

A console mode version of the example application is also provided under ibx/examples/local-employeedb/ConsoleModeExample.lpi.

Like all IBX console mode applications, this uses the ibexpressconsolemode package. The IBCMLocalDBSupport unit is used to provide the TIBCMLocalDBSupport compoent.

The application is similar to the above and uses the same archive database and upgrade scripts. Instead of displaying the employee table, when run it will print out the first two rows.