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.
Fast/Flexible Linux OS Recovery
On Demand Now
In this live one-hour webinar, learn how to enhance your existing backup strategies for complete disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible full-system recovery solution for UNIX and Linux systems.
Join Linux Journal's Shawn Powers and David Huffman, President/CEO, Storix, Inc.
Free to Linux Journal readers.Register Now!
- Download "Linux Management with Red Hat Satellite: Measuring Business Impact and ROI"
- Profiles and RC Files
- Understanding Ceph and Its Place in the Market
- Astronomy for KDE
- Git 2.9 Released
- Maru OS Brings Debian to Your Phone
- OpenSwitch Finds a New Home
- What's Our Next Fight?
- The Giant Zero, Part 0.x
- Snappy Moves to New Platforms
With all the industry talk about the benefits of Linux on Power and all the performance advantages offered by its open architecture, you may be considering a move in that direction. If you are thinking about analytics, big data and cloud computing, you would be right to evaluate Power. The idea of using commodity x86 hardware and replacing it every three years is an outdated cost model. It doesn’t consider the total cost of ownership, and it doesn’t consider the advantage of real processing power, high-availability and multithreading like a demon.
This ebook takes a look at some of the practical applications of the Linux on Power platform and ways you might bring all the performance power of this open architecture to bear for your organization. There are no smoke and mirrors here—just hard, cold, empirical evidence provided by independent sources. I also consider some innovative ways Linux on Power will be used in the future.Get the Guide