Monday, January 30, 2006

Porting the EMPLOYEE database from Firebird 2.0 to MySQL 5.1 part 1


Hi, I've decided to make an in depth test of MySQL's new functionalities by porting Firebird's EMPLOYEE.FDB database to MySQL 5.1.5, here is what I found, I hope it will be useful for people porting apps from MySQL to Firebird and vice-versa.
First of all, a screenshot (taken from Firebird's excellent Flamerobin admin tool) showing the actual employee.fdb structure.
Note the presence of generators, (aka sequences) won't be available in MySQL, I'll mimic them with autoincrement fields.
Also, look at triggers, two of them ("set_cust_no" and "set_emp_no") are used to implement an autoincrement-like key in tables, again there is no need to even try to reimplement them in MySQL.
Also, at first I won't try to implement domains, exceptions and calculated columns as I'm not aware of a clean implementation for them in MySQL.
Here is a screenshot of the first migration phase, I reproduced table structures, and views.
First of all the good news, I've always found data types equivalent for every table, and actually the views are even easier to create in MySQL than in Firebird, as you don't have to list all columns:

Firebird: CREATE VIEW PHONE_LIST (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, LOCATION, PHONE_NO)
AS SELECT ...


MySQL: CREATE VIEW `employee`.`phone_list` AS select `employee`.`emp_no` ...

Bad news, I've not found an equivalent for calculated columns, I suspect the best workaround will be to mimic them using a trigger (updateable views should also do the trick).

3 comments:

vitus said...

Have you considered to use views to emulate calculated columns?

pabloj said...

Yes, as I wrote:

... (updateable views should also do the trick)

Anonymous said...

MySQL support domains SQL ?