One of the relatively frequent requirements present while designing an IT system is to make it portable between several database systems. In this article, Marek will present Fyracle which is a modified version of the popular Firebird database system and the foremost difference between Fyracle and the commercial Oracle database system.

One of the relatively frequent requirements present while designing an IT system is to make it portable between several database systems. Unfortunately in order to achieve such portability it is necessary to place all the business logic in the application server layer and to use SQL syntax compliant with e.g. SQL’92; this usually results in a performance drop, as a large number of queries submitted from an application server is not as fast as stored procedures.

What is Fyracle?

Fyracle is a modified version of the popular database system Firebird. The foremost difference between the two databases is Fyracle's ability to understand syntax of the commercial database system Oracle.

Firebird has its roots in the InterBase database by Borland which in 2000 was released as Open Source. This database has been developed for over 20 years. It is available for both Windows, Linux and other flavours of Unix. FireBird is very popular, especially among Delphi programmers; it is estimated that there are about 2 million of Firebird installations worldwide. It is used for both embedded-type applications and databases of dozens of gigabytes in size and hundreds of users. The database features advanced capabilities such as nested transactions or creation of triggers and stored procedures. Furthermore, Firebird was one of the first databased based on MVCC architecture - that is, storing all versions of particular rows for all transactions remaining in progress; this allows one to do away with blocking read operations on a row which has been modified by another transaction and greatly reduces the risk of encountering deadlocks.

Figure 1. Registering a database

Fyracle is compatible with Oracle at the level of both extensions to SQL syntax and the language of stored procedures, PL/SQL. It is the only free database capable of interpreting Oracle syntax.

It is certainly an interesting proposal for companies and programmers which already have applications written for Oracle, who would like to have the possibility of selling them without licensing fees for the database itself - e.g. to smaller customers that do not gather large amounts of data. This way they could abandon expensive attempts of porting their applications to other database systems and use Fyracle instead.

Furthermore, Fyracle is a tempting option in case of a new application. Let us remember that while on one hand it is good to develop applications which are portable between various database systems, on the other it is hard to avoid database-specific solutions for high degree of complication of an application. A compromise under such circumstances might be exactly to use Fyracle and Oracle. We can make use of the free Fyracle DBMS wherever it is possible, at the same time always having the possibility of offering Oracle to a large customer.

Although there is an equivalent solution based on Microsoft SQL Server and its simplified version MSDE (whose licence allows its free distribution with one's own applications), in case of Fyracle we are not limited to a single operating system.

The creator of Fyracle is the Dutchman Paul Ruizendaal, the founder of a company called Janus Software whose speciality is Open Source software. The runtime version of the database is freely available, whereas the development version - allowing import of stored procedures and triggers - can be obtained for a fee.

Where to Place Business Logic

Database servers have been equipped with custom languages making it possible to create stored procedures, triggers and functions almost since the beginning. Placing all or a part of the business logic of an application in the database is justified: the time of execution of a query within the database is usually significantly shorter, as there are no network-related delays and it is possible to make use of precompiled cursors.

Unfortunately unlike SQL, languages used to develop stored procedures and triggers have not been standardised and every major database uses its own language here. As a result, it is not possible to migrate them from one database system to another.

Up till the middle of 1990s stored procedures were used very frequently for developing client-server applications. Such applications were usually constructed so that the form received the results from a stored procedure, similarly changes made to the form were saved using a stored procedure. Even now such approach can often be found e.g. in .NET applications which are to run only on SQL Server.

However, the new millennium brought with it a raise of popularity of three-layer solutions, especially of the J2EE technology. The client doesn't communicated with the database directly, instead contacting an application server which contains the business logic. It is the application server which executes database queries.

The grounds for adopting such an approach were greater capabilities of high-level languages (XML libraries, support for IP connections - at that time neither of which were available in stored procedures), an additional layer of caching and the very issue of portability of code between database systems.

In many cases it is possible and reasonable to develop fully portable database applications - not in all cases, though.

In practice it often turns out we are forced to make use of a certain database-specific function. We could use a stored procedure for fast importing of files, or an installed trigger. To maintain full portability requires frequent functionality testing on each of the databases, as it is very easy to e.g. use, even subconsciously, non-standard SQL syntax.

In other words, with portability it is very easy to get lost, plus we might be forced to pay for it with worse performance of our application.

Therefore, it is worth it to ask a question what we need portability for. In practice it often suffices to have one simple, free database for small customers and demonstration versions, as well as a big one for high-end applications. Under such circumstances, the Oracle - Fyracle duo is a perfect solution.

Installation and Start-up

On the CD enclosed with the magazine one can find the latest version of Fyracle. Both Windows and the Linux versions feature an installer. The Windows version automatically registers the database server as a service and is started up every time the computer is rebooted.

In order to create a new database one has to launch the program isql from the bin directory. At the command prompt we type in:

>CREATE DATABASE ‘c:\datafile.fdb’ USER ‘SYSDBA’ password ‘masterkey’

This will result in creation of a new data file, c:\datafile.fdb’. The user SYSDBA is the default administrator of the database.

Installed along with the database there is a GUI program for its administration. In order to run it one must have Java installed. Run the program run.bat from the studio directory.

From right-hand side tree we choose the server local, then select Register database from the context menu.

The alias can be arbitrary, e.g. a test database. The URL will in our case look like this:

jdbc:firebirdsql:oracle:localhost:c:\ datafile.fdb. In the field connection type we select Fyracle.

The user is SYSDBA, its password masterkey. Click Register. A new entry appears, a double-click on which connects us to the database. At the moment the program remains in an early phase of development and in addition to executing SQL statements it allows us to browse database objects.

In order to import tables, stored procedures and triggers from Oracle one uses the program called plsql. To import a file called tabele.sql into the database c:\datafile.fdb, we execute:

>plsql c:\datafile.fdb tabele.sql

Accelerating SQL

The database query language SQL is a commonly accepted standard. Its capabilities are very large - and yet it often happens that we need even more. How could we increase speed of our applications having decided upon Fyracle/Oracle? Here, only a few of many possible examples will be shown.

An issue that is encountered quite frequently in database applications is fetching a tree, e.g. the hierarchy of employees. The most common method of storing the subordinate - superior relations is to provide a field containing the id of the superior for each subordinate. From my own experience I know that in many cases, fetching a tree is done in an exceptionally inefficient way, by recursively fetching various branches of the tree. For a tree with several thousand nodes this might imply a thousand or so separate queries, taking all together tens or dozens of seconds.

Figure 2. A PL/SQL procedure imported into Fyracle

sum:

0 €

» Library of the Month
» Workshop
» Software Engineering
» Programming Tools
» Interview
» High quality and
secure programming
» Reverse Engineering
» Portable GUI
» Artificial intelligence