Feb 3, 2005 / MS SQL Server to FireBird migration. That's a Pain!
3 comments
Recently I read article Embedded Firebird at Code Project and decided to try FireBird. I like the idea of embedded database. FireBird allows .Net developer to put a dll into bin directory, create database file and use usual database access. It sounds very attractive and simple. In theory. So I decided to migrate from SQL Server to FireBird.
Database Creation and Tools
Embedded version of FireBird is very light, just 1.5 Mb. FireBird Data Provider for .NET is also not so big – 0.7 Mb. I downloaded all these stuffs and put required dlls into bin directory. Then I started database creation. However, FireBird Embedded distributive does not include any tool for database administration. Well, I downloaded standard distributive (4 Mb) and installed full featured SuperServer on my workstation. I dug into Program Files in the hope of find DB administration tool and find one. It was ISQL console tool. “OK, no GUI” – I thought. – “This database is for real geeks”. I opened QuickStart guide, found a section about database creation and tried to create one. But, as you might guess, without success. There was strange behavior, the ISQL refused to believe me that I really creating new database and told that database is unavailable.
Fighting with console for awhile, I decided to find GUI tool. There were several free toll at IBPhoenix web site. First I downloaded IBSQL (1Mb). It ran, but, when I tried to create new database, showed an error “gds32.dll was not found”. I hunted around and find out that original dll could be renamed and putted into the System32 directory. I started IBSQL and it worked! A new database was created!
Do you think the migration is close to completion now? Not at all. Schema importing became a really tough action. At the very beginning I discovered that FireBird does not support IDENTITY keyword. That’s not a problem, I removed it. Then things became weird. IBSQL tool did not execute query popping up the error in the line with second’s table CREATE statement. I tried to correct statements and understood that the tool unable to run several statements! There were about 40 statements to execute, and I was not very enthusiastic about doing that one by one. “Fine, let’s find something more user friendly”. I found FeniSQL - very light (0.5Mb) tool. The same bug! I was slightly tired of that. I found several advices is newsgroups about IBOConsole tool and got them (2Mb). Yes, this tool is really great, has appealing GUI and useful features. And has no silly restrictions on statements execution.
SQL compatibility
However, SQL was still wrong for FireBird. I used columns named “password”, “value” and “time”. These are reserved keywords. MSSQL Server eats them, but not FireBird. As you understand, renaming several columns is an issue, but I want to set up database at least. So I renamed fields in schema and ran SQL script again. The database schema was created successfully.
Do you think the migration is over? I thought so in that moment, but FireBird converted all tables and columns names in upper case. And the application refused to query database correctly. Ok. Google newsgroups are helpful and I found out that names should be quoted:
CREATE TABLE "releases"
(
"goal" VARCHAR(500),
"finish_date" DATE,
"start_date" DATE,
"name" VARCHAR(150) NOT NULL,
"project_plan_id" INTEGER NOT NULL,
"release_id" INTEGER NOT NULL,
CONSTRAINT "pk_releases" PRIMARY KEY ( "release_id" )
);
I changed SQL and… Yes! I got it! I run the application and all screens seem to be working. Then I tried to add new Project and… Yes, I got an error (I was not surprised in fact). FireBird does not support autoincremental columns. There is a workaround using Triggers and Generators. The main idea is to create generator and invoke it before insertion to increment primary key. Something like that:
CREATE GENERATOR "project_id_gen";
CREATE TRIGGER SET_ID FOR "projects";
BEFORE INSERT
AS BEGIN
"projects"."project_id" = GEN_ID ("project_id_gen", 1);
END
As I understand, this had to work properly. But it wasn’t.
Conclusion
I spent 4 hours, downloaded 6 distributives, several times queried Google and almost resolve all problems (the keyword is almost). Well, maybe I am not DBA guru, but have a good experience with several major databases and never encountered such difficulties with them. FireBirds behavior is illogical for me in some cases, but I am going to complete migration today. No, I am not a stubborn donkey, just because of the embedding. This way is agile and should be simple, but it is hard. That’s sad.
3 Comments:
I am pretty sure that some of your problems are not caused by Firebird but by MSSQL that allows some non SQL standard constructs (a bit like non standard HTML support in InternetExplorer).
I guess the trigger "problem" is solved now.
Firebird does not support auto increment fields in the interest of enforcing strong programming and design principles. I'm sure it feels difficult coming from a different background (any time one loses a relied upon feature it is painful). However, the advantage of using GENERATORs is that client code can have foreknowledge of the ID which makes tying detail rows to a master very easy. This minimizes back and forther "chatter" between the client and server and allows a transaction to be assembled more completely on the client side before posting to the server. Another advantage of which being that the transaction is open for a shorter time period, which is more efficient.
I found out that names should be quoted ONLY if required in lower case. BUT if you create Table & Field names in UPPER case, DML can be in upper or lower case (select,insert,update) :
CREATE TABLE "releases"
(
"goal" VARCHAR(500),
"finish_date" DATE,
"start_date" DATE,
"name" VARCHAR(150) NOT NULL,
"project_plan_id" INTEGER NOT NULL,
"release_id" INTEGER NOT NULL,
CONSTRAINT "pk_releases" PRIMARY KEY ( "release_id" )
);
Post a Comment
<< Home