Personal Databases

TIBLocalDBSupport 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:

• Verification that the embedded Firebird Server is in use.

• Setup of the FIREBIRD environment variable for the embedded server.

• DatabaseName, and login parameters management.

• Use of the Firebird Services API to initialise an empty local database from a gbak format Firebird archive.

• Use of the Firebird Services API to save the current local database to a gbak format Firebird archive.

• Use of the Firebird Services API to replace the contents of the current local database from a gbak format Firebird archive.

• Use of the TIBXScript Engine for automated field upgrade of the local database.

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:

• Windows: the application executable's location.

• Unix: the application executable's location unless this is /usr/bin, /usr/local/bin, /usr/sbin or /usr/local/sbin, when the shared data directory is set to /usr/share/<application name> or /usr/local/share/<application name> depending on whether the application is in /usr or /usr/local.

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:

• Windows: "User Application Directory"\VendorName\DatabaseName

• Unix: "User Home Directory"/."VendorName"/DatabaseName

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 areinterpreted 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).

• If iblAutoUpgrade is given in the Options property and the current schema is less than the Required Version no., then TIBLocalDBSupport will attempt to apply the upgrade rules to raise the version number to that required.

• If iblAllowDowngrade is given in the Options property and the current schema is greater than the Required Version no., then TIBLocalDBSupport will attempt to locate a suitable backup archive and restore this as the current database. This case is usually only found in the unlikely event of a failed upgrade and the user has installed an older version of the software in order to recover from the problem.

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:

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.