Introduction to the Firebird Database
Right off the bat, it is important to answer the questions that have cropped up around other open-source databases, especially MySQL. The primary concerns in their forms are answered below.
Does it support transactions?
Yes. A transaction can be committed or rolled back. In fact, Firebird even supports the concept of savepoints and rollback to savepoints, similar to what is found in Oracle. This feature is useful when, in a stored procedure or trigger, one wants to roll back to a certain point in logic. In Firebird, one can make a savepoint and then roll back to that specific point. If one is running a script of SQL commands, one can put the savepoints and rollbacks appropriately.
Does it offer foreign key support?
Yes. Firebird uses a standard SQL syntax to enforce foreign key support. For example, the following SQL text enables foreign key support for the child table:
SQL> create table parent (parent_id int not null primary key, CON> parent_val varchar(10)); SQL> create table child (child_id int not null, parent_id CON> int not null, child_val varchar(10), CON> primary key (child_id), CON> foreign key (parent_id) references parent(parent_id));
Does it support row level locks?
Yes. By default, Firebird uses a multi-version concurrency system. This means all the sessions see old data until the new data is committed. An alternative locking technique involves making a lock exclusive with the select... for update with lock SQL command. In either case, the lock granularity lies on the row level. Here is an example: assume two sessions are accessing the abovementioned parent table:
Session # 1: SQL> select * from parent; PARENT_ID PARENT_VAL ============ ========== 1 Preimage SQL> update parent set parent_val = 'Postimage' where parent_id = 1; SQL> select * from parent; PARENT_ID PARENT_VAL ============ ========== 1 Postimage Session #2: SQL> select * from parent; PARENT_ID PARENT_VAL ============ ========== 1 Preimage Session #1: SQL> commit; Session #2: SQL> commit; SQL> select * from parent; PARENT_ID PARENT_VAL ============ ========== 1 Postimage
As one can see, the non-committed transactions in session #1 did not stop the second session from accessing the data. Session #2, however, accessed the old value and not the new one.
The other important locking mechanism is "select for update" locking. This is convenient as it allows a session to have exclusive locks on a row; it guarantees that no other session can change the data until the session is done processing it. This way the session is assured the data does not change from the time it did a select to when it does an update.
Session #1: SQL> select * from parent where parent_id = 1 for update with lock; PARENT_ID PARENT_VAL ============ ========== 1 Postimage Session #2: SQL> update parent set parent_val = 'Postimage2' where parent_id = 1; (Hangs)
As you can see, session #2 cannot update the parent_val until session #1 issues either a commit or a rollback. This sophisticated level of locking is not offered in MySQL, even with innoDB tables.
Does it support stored procedures and triggers?
Yes. In this area Firebird really shines. The advantage of having a standardized stored procedure language is developers that have come from other database background can be up and running in no time. In fact, it took me less than 10 minutes to learn to write productive stored procedures in Firebird. Even though PostgreSQL offers a stored procedure language, there are many different languages (Perl, Tcl, Python) in which one can write the stored procedures. This variation could lead to some problems when one developer leaves and other joins the project. Using a standard stored procedure language, however, avoids this problem. The triggers in Firebird are more like the ones in Informix/Oracle, with the option of having the before and after insert/update or delete, rather than Sybase's virtual INSERTED/UPDATED/DELETED table type.
Let's create a simple stored proc that shows the power of the language. The following example puts a row in the child table for a parent called os. If the os is Linux, it puts another row in the child table and puts the entry LinuxRocks in it as well.
SET TERM !! ; CREATE PROCEDURE insert_into_child (os_type varchar(10)) AS BEGIN insert into child (child_id, parent_id, child_val) values (gen_id(gen_child_id, 1), (select parent_id from parent where parent_val='os'), :os_type); if (:os_type = 'linux') then begin insert into child (child_id, parent_id, child_val) values (gen_id(gen_child_id, 1), (select parent_id from parent where parent_val='os'), 'LinuxRocks'); end else begin insert into child (child_id, parent_id, child_val) values (gen_id(gen_child_id, 1), (select parent_id from parent where parent_val='os'), 'Boo!'); end EXIT; END !! SET TERM ; !!
Here is an example of a trigger that checks for inserts and updates in the child table to see if the entry is LinuxRocks; if true, it puts another entry in the table called YouSaidIt!
SET TERM !! ; CREATE trigger you_said_it for child after insert or update AS BEGIN if (new.child_val = 'LinuxRocks') then insert into child (child_id, parent_id, child_val) values (gen_id(gen_child_id, 1), (select parent_id from parent where parent_val='os'), 'YouSaidIt!'); EXIT; END !! SET TERM ; !!
Now that we have looked at the questions most frequently asked of an open-source database, we should discuss the other great features this database offers.