Introduction to the Firebird Database

Reasons why the Firebird database is a viable option for your next project.
Replication

Many solutions for replication have been developed by various entities. Most of these rely on trigger-based mechanisms that keep track of inserts, updates or deletes from a given table and then take those changes and propagate them to another database. As far as I can determine, all of the solutions are commercial in nature and can be used to administer databases on different platforms, including Window and UNIX/Linux. Additional information on this can be found on the IBPhoenix Web site (see Resources).

Multiple Data Files

A single database can span to the data files, which gives the administrator the flexibility to load balance the database from a disk perspective. It is not unusual for databases to have local hot spots where an inordinate amount of activity occurs. Having the database laid out on multiple data files, which could reside in turn on multiple disks, alleviates the problem to a certain extent. Additionally, a single table also can be put on a separate file, and in this way load can be distributed to an even finer granularity.

Conclusion

Some people might wonder why they should make the effort to learn a new database, especially if they already are familiar with MySQL or PostgreSQL. From my perspective, Firebird offers a comfortable migration path from closed-source, commercial databases to an open-source database. I have found the task of converting from Oracle or Sybase to MySQL or PostgreSQL to be a bit daunting, as the nature of these databases is quite different from the commercial offerings. If the reader already is familiar with any of the large popular RDBMSs, the concepts he or she has learned over the years in those databases can convert smoothly to Firebird It offers virtually every common feature available in high-end databases without any significant impact on performance, as compared to the speed demons of the Linux platform. If you are looking for a database for your next project, think about Firebird as a viable option.

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

connection strings

Anonymous's picture

Firebird connection strings http://connstr.net/firebird/

FireBird Triggers - Help needed

Anonymous's picture

Hi, I would like to know if it is possible to fire a trigger in Firebird (After Insert or Update or Delete) that keeps track of inserts, updates or deletes from a given table and then take those changes and propagate them to another database.

For Example (MSSQL)
After deleting a row from [dbo.table1] the same row from TestDB2.table1 is deleted also

CREATE TRIGGER [TR2] ON TestDB1.table1
FOR DELETE
AS

DELETE FROM TestDB2.dbo.table1
WHERE field1 = (SELECT field1 FROM DELETED)

My question is that if I can do the same on a FireBird DataBase.

Thank you,

Kostas

Firebird vs PostgreSQL

Shadowfax's picture

Some time ago, I began searching for a free database engine to distribute with my program. I was running Microsoft Access at the time, but I knew that it would not last for long. Access is not a true server engine, and imposes terrible constraints on size and user-connections.

I had read quite often that SQLServer was a great reliable engine; sadly, after acquiring the Developer edition, I found out that you are not permitted to distribute it AT ALL (so remember to always do your research first!). The MSDE Engine seemed like a good alternative, but it also imposed several forced constraints; it may have been more reliable than Access Jet, but it was still too limited for my liking.

Then I discovered the Open-Source Database market. Free powerful databases, wonderful!!!! My first thought was to check MySQL, but unfortunately, they do not permit distribution of their engine without first purchasing one of their commercial products.

So I had to look at the other two leading engines, PostgreSQL and Firebird. Although they are quite similar and so difficult to compare, here is my final result.

Windows 95/98/ME Supported
------------
PSQL - Not Without Middleware CYGWIN
FBird - Fully Supported

Windows NT Supported
------------
PSQL - Not Without Complications & Building a Custom Installer
FBird - Fully Supported

Windows 2000/XP/2003 Supported
------------
PSQL - Fully Supported
FBird - Fully Supported

Other Supported Open-Source OSs
------------
PSQL - 17 (Firebird's List + 12 More)
FBird - 5

Login Authentication Encryption
------------
PSQL - Yes
FBird - No

SSL
------------
PSQL - Yes
FBird - Requires Plug-In

Schemas
------------
PSQL - Yes
FBird - No

Temporary Tables
------------
PSQL - Yes
FBird - No

Documentation (incl. Online Community Size, Manuals, Tutorials, Add-Ons, etc)
------------
PSQL - Widespread
FBird - Few

Web-Hosting Support
------------
PSQL - Some
FBird - Very Little

Alternate Editions
------------
PSQL - None
FBird - Classic Server / Embedded Server (Single-User)

Routine Maintenance
------------
PSQL - Required Regularly
FBird - Little or None

In summary,

PostgreSQL has enterprise-level features, more open-source OS support, and a larger amount of documentation and extra online help.

Firebird has simpler Windows support, and far less maintenance requirements.

Both engines are very similar in their speed and reliability. They also share many essential features, including full transactional support, online backups, little hardware requirements, virtually unlimited database sizes and connections, royalty-free distribution rights, and a number of programming connectivity methods.

However, while PostgreSQL has a wider array of high-end features, Firebird's simplicity enables it to run with the least maintenance.

If I were developing a database for my own personal or company use, then I would lean toward PostgreSQL more. However, Firebird's ability to run completely automated without any administrative necessity makes it ideal for distributing databases and applications.

My Final Choice: Firebird.

Re: Introduction to the Firebird Database: replication

Anonymous's picture

FB offers shadowing. A database shadow functions as an exact duplicate of the original database, maintained in real time by the InterBase server. If the original database is lost through user error or a hardware problem, the shadow can take over as the primary database. problem is shadow has to be on the same machine as primary OR has to write to a networked map drive.

A product called IBreplicator is also available

Next gen database

Anonymous's picture

i do hope that a database will appear that will allow stored procedures and triggers etc to executed as cgi dso modules.
this means you could write a stored procedure in php.
people always seem to look at the database as the end of the process.

all this would require is to emulate the functionality of the apache webserver that allows for cgi script execution, within the database.

people please wake up.

Re: Next gen database

Mmike's picture

Why? Why would you want to write stored procedures for a database in PHP? Or any other procedural language? Because SQL is hard to learn, or you feel it's easier to code in procedural languages?

I've been using MSSQL for over 3 years now, and I'm perfetcly happy with the T-SQL. I learned to minimize use of WHILE loops, and abandon the use of CURSORS. I worte several accounting/stock-management applications with frontends in VisualBasic/FoxPro even PHP on Linux/Apache for web-clients to the system, and thinking of having ability to do for() loops and stuff inside the stored procedure makes me realy nervous. I'd die if one day I had to debug or continue to work on project that has stored procedures written in those procedural stuffs.

MSSQL2005 offers you the ability to write stored procedures in C# or VB.Net. Dunno how that looks, but I'm not the one who is shouting 'finaly' about that. I feel it's more MS marketing trick to sell .Net. In a way, for example 'dont worry about ubiqotious SQL any more, write your stored procedures in C#'.

T-SQL/PL-SQL or whatever variant/dialect of SQL the RDBMS is offering is the best language to be used for manipulating set-data. Procedural languages are just not good for that purpose. Period.

Re: Introduction to the Firebird Database

Anonymous's picture

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.

Doh, choice is _good_. PostgreSQL _does_ have the "standardized" (or whatever) procedure language, PL/PGSQL. Being able to write PL in Perl or Python or Tcl is _good_ especially if your app is also written using the same language (thus you can share libraries, reuse code, etc).

And if you can't control what language your developer writes the PL in, you're screwed anyway :-)

Re: Introduction to the Firebird Database

Anonymous's picture

A pretty decent article, however... there's a slight bias against PostgreSQL. Here are a few comments:

1. In many aspects PostgreSQL are closer to Oracle than FB/Interbase. Several examples: schema, regexp (both of which are not supported at all by FB/IB), or the various index types, or replication and full-text searching (no free solutions exist yet in FB). That should make it easier to migrate between PG Oracle. However, I admit that three important aspects are still missing in PG: native Windows port, nested transaction (savepoints), and 2-phase commit. All of these are being worked on and there are already working proof-of-concepts.

2. As noted above, no free solutions exist for full-text searching and replication.

3. For Unix developers (hey, this is Linux Journal right?), I really can't recommend FB right now. FB has always been more friendly to Windows environment. Try the command line utility 'isql' and tell me it doesn't suck (psql and even mysql command line utils are a far far cry from it). Or what command line options does fbserver.exe accept? It's not even documented! Or try connecting to FB from Perl or Ruby. There are only IB6 modules for those languages right now. PG however, has always been dear to Unix people; there are drivers for almost any languages out there, including Parrot! There are also the various Unix goodness in PG that are lacking in FB, such as regexp, the ease of working with command line and pipes, proper escaping in string literals, flexible authentication, rich metadata through catalog tables and 'transparency' & 'hackability', ease of securing for multiple users (for hosting environment, ASP), chroot-/jail- ability, ease of SSL connection setup, etc.

4. FB is very "black box"-y right now. When running, you can't find out who's connecting and what SQL commands are they emitting, who's hogging the server, which queries are taking too long, etc. You can't kick out a connection, impose a time or CPU limit, log SQL commands, etc. You can just watch it drain CPU and die :) For a server that's used by multiple users, it's very hard to pinpoint the cause of problems. This is supposed to be fixed in FB2, but we'll probably see FB2 in at least 2-3 years from now (judging from the current development speed).

Even MySQL is much better than FB at this, with "mysqladmin processlist" and all.

===

In short, I like FB, but I now prefer PG. PG development community is much larger, they are churning out new features much faster, and newer PG versions are more advanced and reliable than ever. The FB development community however, are still having problem releasing 1.5 final (it's been what, 2 years?), are still hung up on Mozilla using their name, etc. In the time between FB 1.0 and FB 1.5 RC8, PostgreSQL has released several new versions of PG. And with the future PG 7.5 (or 8.0; depending on how they will name it) this year which will sport a native Windows port, we will see a whole new level of competition.

PostgreSQL vs. Firebird

Anonymous's picture

One very important advantage Firebird has for me over PostgreSQL is that it allows me to move my databases between a Windows server and a Linux one effortlessly (i.e. just copy over the single - in most cases - database file). Interoperability between Windows and Linux Firebird installations is smooth and generally a no-brainer.

In contrast, Cygwin (which you need in order to get PostgreSQL to run on Windows), as cool as it is, can be a real PITA to deploy and has all the disadvantages of a non-native environment. A big minus when it comes to mission-critical situations.

I guess people have to weigh the heavier feature set of PostgreSQL versus Firebird's more friendly cross-platformness. Firebird is incredibly lean and easy to manage and deploy for something which has such a comprehensive feature set.

>Try the command line utility 'isql' and tell me it doesn't suck
> (psql and even mysql command line utils are a far far cry from
> it).

isql is a bit archaic and certainly not as friendly as mysql, but I can live with it. btw, isql works essentially identical under Linux and Windows, afaik.

> FB has always been more friendly to Windows environment.

I deployed a Linux-based Firebird app, and if we are talking about the DBMS proper, I really can't see a difference between the Linux and Windows versions of Firebird from the user's viewpoint. What you probably mean when you say that FB is 'more friendly to Windows' is actually the fact that there are a lot of good Windows-based Firebird/Interbase utilities. But the cool thing is that you can actually use these over TCP/IP to interact with a Linux-hosted FB database!

> Or what command line options does fbserver.exe accept? It's
> not even documented!

Firebird tends to be minimalistic when it comes to options. But that's why we love it...

> Or try connecting to FB from Perl or
> Ruby. There are only IB6 modules for those languages right
> now. PG however, has always been dear to Unix people; there
> are drivers for almost any languages out there, including
> Parrot!

kinterbasdb (Firebird drivers for the Python DB-API interface) rox! And there are OLE-DB, ODBC, ADO and ADO.NET drivers for Firebird. Windows still offers a better client environment over Linux and this fact is what makes me heavily favor Firebird over PostgreSQL (for my particular situation).

My judgement is that Firebird offers you more room to maneuever when planning deployment options. PostgreSQL is *nix-centric (read: not platform-agnostic) and that tends to limit your client deployment choices.

Re: Introduction to the Firebird Database

Anonymous's picture

Small comment:

To set things straight:

1) I like PostgreSQL
2) I like FireBird

Now, regarding isql vs psql. You are right. But, one can just as well say the same for the way c/c++ applications are written using the standard PostgreSQL API's. FireBird is MUCH easier to do if using the gpre pre-compiler.

This is not a war of who's best, as both products are free and wonderful to use thanks to the effort put into them by the maintainers.

So, what I'm trying to say is: These products and their maintainers deserve our applause, rather than our critisism.

Re: Introduction to the Firebird Database

Anonymous's picture

I think many of the features you point out are good. I for one never knock PostgresSQL. There is no need. If I didn't use Firebird, for sure I would use PostgresSQL. Both databases have different strengths and weaknesses, and it is good to learn from each other - something that is easier for us to do than it is for the closed-source competition.

The scary people are the ones who just blindly buy DB2, Oracle, etc. without doing their own analysis and stress-testing of the open source dbs.

I don't think you are right to cast aspersions on the FB developers. PostgresSQL and MySQL have had a much higher open-source profile than Firebird until this last year. Hell, I only found out about Firebird in a side note to a very lengthy (maybe 30 page) discussion comparing these other too. We have seen Firebird ignored on polls and in articles until this year. The developers have moved the entire code-base from c to c++. Also, the naming issue was significant - especially since the Firebird project was already relatively unknown compared to MySQL and PostgresSQL.

So come on, give us a break. One of the few articles in a Linux journal giving a brief intro to Firebird, and you start carping on about how much better PostgresSQL is. IME when the Firebird luminaries are asked to criticise other open source dbs, they refuse to do so. And I think you are over-senstivie if you think the writer was biased against PostgresSQL.

I am looking forward to seeing PostgresSQL on Windows. Hope you continue to enjoy PostgresSQL.

Re: Introduction to the Firebird Database

Anonymous's picture

What competition ? MSsql server ? oracle that is firebird target - not mysq , postgresql
1. fb is closer to oracle than you think , free replication is in the works (search it on sf.net)
full text search - the same - what else ?
2. the same - there is commercial replication (pgreplication was commercial isn't it until they made it os - who knows maybe the fb team will do the same)
3. Kidd me not - the Firebird sql console is called *isql* and have some options :)
as for perl there is : http://sourceforge.net/projects/dbi-interbase/ ,
python : http://kinterbasdb.sourceforge.net
ruby : http://raa.ruby-lang.org/list.rhtml?name=interbase

I can do the parrot port if you want (the perl 5.8 port of dbi-interbase should work )
What else ?
Hackability - is that a feature ? complex solutions ?
Firebird folows - kiss , maybe to much kiss but there will be
Java VM in the engine 2.5 3.0 ? also with plugin arhitecture
(like in mozilla ) other languges could be added : perl , php ,C$
Yep you can jail -it now (with chroot cmd line)
4. FB development is too fast
We got a AMD-64 port is booting now ! , clustering is on the way ,
Yep we gonna kill queries in year - maybe in 2.0 , security cleanup ....

=============================
Summing up
Firebird 1.5 it will be released in 1-2 weeks (watch the devel list)

(We follow os philosophy release it when is ready )

Re: Introduction to the Firebird Database

Anonymous's picture

I use firebird with PHP for work every day. Firebird has been fabulous.

The only issues I had was with PEAR not being tested/developed fully with firebird, but that was solvable.

It just runs and stays running. Almost no administration and tweaking neccasary.

Re: Introduction to the Firebird Database

Anonymous's picture

Pardon, but I need your help.
I have been trying to start using this FireBird thing but couldn't even understand how to START the actual database application !!??
Have the server 1.5 installed and now - what ?
There is no 'application' related to it ?
Could you please tell me what to do next and where to look ?
THe OS os WIN98
Thank you for any advice !!
Best regards
mstengl[at]email.com

Re: Introduction to the Firebird Database

Anonymous's picture

Use IBOConsole for Firebird administration needs.

Re: Introduction to the Firebird Database

Anonymous's picture

Yes, the information about multiple files is really quite confused and wrong.
On an OS that has upper limits on the size of a read-write file, or on a system that needs to spread the database across multiple disks, you can partition the database into multiple files.
Accessing a multi-file database is no different to accessing a single-file one. Regardless of how many files there are, clients access the first one - the "primary file". The server uses indirection to find requested data that are located in secondary files. Since the server does the accounting for the whole "logical bundle" it takes care of what goes where. You can't, for example, position a particular table in a particular file (which the author's article seemed to imply).
You can also access multiple databases within the same transaction, with XA-compliant 2-phase commit. So, while you might have a reason to share data between two databases on the same (or a different) server, it would be a very uncommon design choice to create a special database for one table if that table were part of the main schema.
People sometimes do it, though, typically to store a table that stores a very large amount of highly static blob data that they don't want to back up as often as the more volatile stuff. Under those conditions, the client application is responsible for coordination and integrity, using a 2PC transaction, since tables cannot be joined across database boundaries.

Re: Introduction to the Firebird Database

Anonymous's picture

"*Additionally*, a single table also can be put on a separate file"

You see, he does mean that this feature id apart form multiple database.
I guess he's about External Tables - but they are NOT tables in common sense at all!!!!

Also it is the only artivcle where it is told that single SP language is advantage.
At least this can only done by BOSS's order to use the single lanuguagge!
More so, for Win32 there is and UDF allows You to reuse JavaVM in Yaffil/Firebird
And FB2.0 is to have the built-in interface to JVM, Parrot, and maybe others, such as Portable.NET

The difference between SuperServer and Classic is simple for beginner: Single CPU - use SS, SMP - use classic.
There are more details, but for the beginner's it is so.

Re: Introduction to the Firebird Database

Anonymous's picture

Agree that this is one of the better summaries of Firebird.

However, as an introduction it might be better to leave any mention of the differences between classic or superserver to the end (ditto for dialects). No need to put off the user with such complexities until after they've been given a tour of the benefits. Also, I think it is a good idea to explain that there _are_ benefits to being able to choose between classic or superserver - established usesrs have good reasons for choosing one rather than the other (depending on data operations, client numbers, server hardware, server os). Whilst superserver is 'the future', there is no indication that classic is being phased out (I believe the 64 bit port is going to try to assimilate the best from both architectures.)

As to multiple-data files: the above respondent is correct that a firebird database is held within a single 'black box' file (no logs, etc); this 'black box' file can span multiple files (if necessary - and there are good reasons for doing this too).

A couple of benefits from Firebird that were not brought out in the article:
a) because of the architecture of the engine, should the server be brought down in an unclean way (os crash, power off, etc), Firebird can recover almost instantaneously on restart
b) the same Firebird engine can be used on servers or can be embedded in client-side applications
c) backups of the black-box data files are transportable across operating systems - you can develop and test on one system, and then just transport the database to one of the other systems for which there are ports of Firebird
d) the last time I checked the current (non-beta) version of Firebird (1.03) is available for Linux, Win32, FreeBSD, OS X, Darwin, Solaris (Sparch and x86), HPUX, and AIX (there was even a WinCE alpha at some point!)
e) Firebird is truly free (unlike e.g. the restrictions imposed by MySQL in certain environments)
f) the first book on Firebird in English is being published in the next few months - there are already multiple books in German. (I have no connection with the author - I can't wait to get my hands on this book).

The article was a good introduction - I just wanted to add a few more details about the benefits of Firebird. It recently ran MySQL a close second in the user's poll at linuxquestions.org ...

It's a database with a great pedigree, and a great future. Just as Linux is commoditizing the OS, I believe Firebird can commoditize the database. The increasing public awareness of the benefits of Firebird should give the providers of the main commercial rdbms food for thought.

Re: Introduction to the Firebird Database

Anonymous's picture

One important detail that was left out...

Is it ACID?

Re: Introduction to the Firebird Database

Anonymous's picture

Transaction support pretty much implies ACID now, doesn't it?

Re: Introduction to the Firebird Database

Delphian's picture

Yes.

Re: Introduction to the Firebird Database

Anonymous's picture

Of course. It wouldn't be compared to the commercial RDMS without it.

Re: Introduction to the Firebird Database

Anonymous's picture

Nice article and glad to see that Firebird on Linux Journal.

One thing though: I am a little puzzled by the paragraph mentioning "Multiple Data Files". Firebird does not use multiple data files at all. Well, at least not by default. It's just one file, and you don't to take my word for it. Create a database and you'll see.

There is an option to created a multpiple file database (kinda) but it's done mostly in situations where the data file is expected to grow beyond the size limitations imposed by the operating system where Firebird runs.

Re: Introduction to the Firebird Database

Anonymous's picture

Both (article's author and you) are rigth and wrong.
Firebird CAN spawn a database in multiple files but only when previous one is full. You create additional files at database create or with alter database statement. You cant assign an object to specific file like Oracle's tablespace option.

Re: Introduction to the Firebird Database

Anonymous's picture

You are wrong.

There is an option to create multiple file database, and you can use it when you want.

Re: Introduction to the Firebird Database

Anonymous's picture

No, he isn't wrong. You're repeating his statement :)

Jakub Hegenbart

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState