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!
- Server Hardening
- EnterpriseDB's EDB Postgres Advanced Server and EDB Postgres Enterprise Manager
- The Death of RoboVM
- BitTorrent Inc.'s Sync
- The US Government and Open-Source Software
- The Humble Hacker?
- Open-Source Project Secretly Funded by CIA
- New Container Image Standard Promises More Portable Apps
- AdaCore's SPARK Pro
- ACI Worldwide's UP Retail Payments
In modern computer systems, privacy and security are mandatory. However, connections from the outside over public networks automatically imply risks. One easily available solution to avoid eavesdroppers’ attempts is SSH. But, its wide adoption during the past 21 years has made it a target for attackers, so hardening your system properly is a must.
Additionally, in highly regulated markets, you must comply with specific operational requirements, proving that you conform to standards and even that you have included new mandatory authentication methods, such as two-factor authentication. In this ebook, I discuss SSH and how to configure and manage it to guarantee that your network is safe, your data is secure and that you comply with relevant regulations.Get the Guide