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:
•Verification that the embedded Firebird Server is in use.
•Setup of the FIREBIRD, FIREBIRD_TMP and FIREBIRD_LOCK environment variables suitable 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.
•Database: reference to the TIBDatabase component for the local database
•DatabaseName: filename (no path) to use for the Firebird Database file.
•EmptyDBArchive: filename (optional path) holding the database initialisation archive. May either be absolute path or relative to shared data directory.
•Enabled: when false component does nothing
•FirebirdDirectory: Full path to directory holding firebird.conf. May either be absolute path or relative to the shared data directory. If empty, defaults to shared data directory.
•Name: Component Name
•Options:
◦iblAutoUpgrade: Automatically apply upgrade when database schema version is lower than required.
◦IblAllowDowngrade: Automatically apply downgrade when available to schema version compatible with the required version.
◦iblQuiet: true then no database overwrite warnings
•RequiredVersionNo: The schema version number required by the application. TIBLocalDBSupport will normally try to upgrade/downgrade the schema to satisfy this requirement.
•UpgradeConfFile: Path to upgrade configuration file. May either be absolute path or relative to the shared data directory.
•VendorName: Used to construct path to Database Directory.
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.
•OnGetDatabaseName: The database path name is normally computed automatically. However, this event allows an application to inspect and override the result.
•OnNewDatabaseOpen: called after the successful initialisation of an empty local database.
•OnGetDBVersionNo: called to get the current database schema version number. If this event is not handled then schema upgrade/downgrade is never performed.
•OnGetSharedDataDir: The shared data directory is normally computed automatically. However, this event allows an application to inspect and override the result.
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.
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.
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.
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.
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.
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:
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.
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:
•Debian/Ubuntu/Mint: run "sudo apt-get install libfbembed2.5" to install the server
•Fedora/Red hat/Centos: su -c "yum install firebird-libfbembed"
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
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:
•Linux: $HOME/.MWA Software/employee.fdb
•Windows: <User Application Data Folder>\MWA Software\employee.fdb
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.
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.