My Dad and I went to the premier last night and it was awesome. Daniel Craig makes an excellent bond. The story was much more believable then some previous Bond movies. Not just the usual goofy villian with giant death laser trying to blow up the world. Definately one of my favorite Bond movies if not THE favorite.

I’ve been playing with Firebird 2.0 a bit the last couple days. I had played with it in the past (version 1.5) and loved it. Version 2.0 is even better. Firebird is the open source off-shoot of Borland’s once popular Interbase.

Last time I worked with Firebird I think I eventually got frustrated and gave up because the lack of easy to find documentation (especially on simple getting started with Firebird). Luckily they now have a nice quickstart guide available on their website. It guides you through the installation choices, security setup and some of the differences with other RDBMs.

Firebird comes with a bunch of command line tools for manipulating databases however I’m rather used to TOAD for Oracle and miss my GUI. Luckily there is a company, HKSoftware, that makes an incredible package called IBExpert that is a very feature rich and super slick interface for doing pretty much everything from creating databases, building objects, grants, backups, data comparisons, etc. The commercial version with all the good stuff is about $200 US but they offer a free personal version that includes pretty much everything I need day to day.

I thought I’d try and list out some of the really interesting stuff with Firebird.

The whole package is tiny. The server and client utilities is only about a 4 MB download which is pretty awesome. It’s cross platform and they provide binaries for Windows and Linux (32/64). They also provide source code for the rest of the Unix world.

The database follows the SQL-92 spec fairly rigorously. It supports complex triggers, and stored procedures, foreign key constraints, sequence type objects (called generators).

Firebird has a fairly minimal collection of built in functions but Firebird makes it easy to add more using UDFs which are basically C functions callable from the database. Firebird comes with a couple UDF libraries that provide the usual math and string manipulations. The only weird thing is you have to look through the definitions file and add them to your databases yourself. For example I needed to use the “mod” function which is not built in by default so I looked in “C:\Program Files\Firebird\Firebird_2_0\UDF\ip_udf2.sql”, found the definition for “mod” and ran that in my database and it made that UDF available.

DECLARE EXTERNAL FUNCTION mod
INTEGER, INTEGER
RETURNS DOUBLE PRECISION BY VALUE
ENTRY_POINT ‘IB_UDF_mod’ MODULE_NAME ‘ib_udf’;

Firebird supports an interesting object type called “Domains”. Domains are basically user defined types. Say for example I wanted a boolean type. Firebird, like many RDBMs, doesn’t have a built in boolean type. I can use a short integer and make it convention that 0 is false and 1 is true but I’ve had bad experiences with that. Over time it seems that you end up with a huge range of values in there that aren’t 0 or 1. What I can do is create a Domain where the basic type is “integer” and then add a check constraint which ensures the value is either 0 or 1. Now whenever I create boolean columns I just declare the column of type “Boolean”.

CREATE DOMAIN BOOLEAN AS
SMALLINT
CHECK (value in (0,1))

CREATE TABLE TEST (value BOOLEAN);
insert into TEST values (0);
insert into TEST values (1);
insert into TEST values (2); — throws exception because check constraint fails

Another rather nice feature is you can define Exceptions as objects in your database. You can then throw these exceptions from triggers and stored procedures without having to clone the message all over the place.

CREATE EXCEPTION BID_EXISTS ‘The bid can not be placed because another bid for that amount already exists.’;

CREATE PROCEDURE BID (
p_auction_id varchar(50),
p_lot_id varchar(50),
p_user_id varchar(50),
p_amount integer)
as
begin
exception BID_EXISTS;
end;

Running the above procedure would return the nice human readable message back to the database client.

A really sweet feature in FireBird is that it supports database events. The basic idea is that you can have your client application sit and listen for specific events (user defined). Your procedures and triggers all have the ability to raise events when they want. So for example, I could have a lot monitor application that waits for log_changed events. I could then put a trigger on my log table that throw the log_changed event on inserts. This way my application can respond to updates immediately and isn’t placing wear and tear on the database from polling it.

Oh oh. And DDL is done in transactions. You can actually roll back operations like creating a table or updating a stored procedure. It’s a bit odd but rather handy.

Another interesting feature is that reads are also done in transactions. When you open a transaction all subsequent reads are done on the snapshot at the moment the transaction began. If you want to read current data you have to commit/rollback the transaction.

As far as the server side, each database is usually just a single file. The connection string contains either an alias (defined in aliases.conf) or the full path to the database file on the server machine. There are security settings to limit connections to databases in a certain directory only or to limit connections to databases defined in the aliases file. The combination of these is quite powerful. For production environments I assume you’d typically lock things down so only databases defined by the system admin in aliases.conf are accessable. For development environments where you have trusted users and lots of databases it probably makes more sense to just leave things wide open and let people connect to which ever databases they have on the server.

Firebird also supports splitting databases into multiple files (I assume for large databases to get by filesystem constraints or to split a database across multiple disks for performance) and database shadowing (as Kyle put it, very high level RAID). There are also two utilities which allow live backups.

Anyways I’ll try and continue posting useful tidbits as I play.

I’ve been working on a project requiring a database and been playing between SQLite, MySQL and Firebird 2.0. I think I’ve pretty much settled on Firebird because it’s fast, small, very feature rich and just plain awesome.

However while I was developing a view for my app I came across this weird little thing:

(please note this is a contrived example. the real code had a good reason for doing what it’s doing)

create table a (id integer);
create table b (id integer);
insert into a values (1);
insert into a values (2);
insert into a values (3);
insert into b values (1);
insert into b values (2);

Nothing too exciting. However this query is very very odd.

select
a.id,
balias.id,
balias.bexists
from
a
left join (select id, 1 bexists from b) balias on (a.id = balias.id);

What I expect for results is this:

However what I get is this:

At first I was convinced I found a bug in Firebird. However after playing around I found that SQLite, Firebird, MSAccess all exhibit this odd behaviour while Oracle and MySQL do what I would expect to be right.

Very odd. If anyone has any idea what this is and why it is doing it I’d be very curious to know.

Dresser (20061103–072039-01)

Originally uploaded by charsplat.

Last week I finally finished up the dresser for our bedroom. There are definately lots of little things I would have like to improve but it’s not too bad for my first thing of this scale.

It was pretty much done just winging it in the style of the bedside tables I’d already done.

I did some rough measurements as to how big we wanted it. Then I scaled things down a bit to make it do-able with a single sheet of oak plywood.

The basic box is all out of oak plywood (on retrospect the interior / hidden sides could have been cheap stuff). The face frame is all 1 1/2″ oak stock from Home Depot and the drawer fronts are glued together 6″ strips. The top surface is a sheet of 5/8″ MDF edged in oak and covered with a cream arborite. I’m not sure if we’ll keep it that way. We both like the wood look but on the otherhand this is pretty nice and extremely durable. We’ll see.