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

Having used InnoDB table handler in MySQL to be on par with Firebird capabilities I'm going to add foreign keys, unique constraints and check constraints and indexes to the tables migrated in MySQL.
Here is a screenshot of Flamerobin showing constraints for table CUSTOMER.

First of all I'll add the foreign key constraints, here is MySQL's syntax:
ALTER TABLE `employee`.`customer` ADD CONSTRAINT `INTEG_127` FOREIGN KEY `INTEG_127` (`country`)
REFERENCES `country` (`country`) ON DELETE RESTRICT ON UPDATE RESTRICT;

A nice graphical representation is in this screenshot of MySQLQueryBrowser showing the same constraint.


Another nice thing, foreign keys referencing the same table are supported in both databases, see this example:

ADD CONSTRAINT `INTEG_35` FOREIGN KEY `INTEG_35` (`head_dept`)
REFERENCES `department` (`dept_no`)
ON DELETE CASCADE
ON UPDATE RESTRICT;

Note that those constraints imply an index with the same name on the referencing field.
Of course unique constraints are supported, like:

ALTER TABLE `employee`.`department` ADD UNIQUE `INTEG_30`(`department`);

Multi-column constraints also work fine in both databases:

ALTER TABLE `employee`.`employee` ADD CONSTRAINT `INTEG_54` FOREIGN KEY `INTEG_54` (`job_code`, `job_grade`, `job_country`)
REFERENCES `job` (`job_code`, `job_grade`, `job_country`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

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

Now the next trigger, it will be an AFTER INSERT trigger, so to fire after the previous one has inserted a row, obviously an after insert trigger can reference old and new data but can't modify them.
So, on with the code:

DELIMITER $
-- DROP TRIGGER new_salary $$

CREATE TRIGGER new_salary AFTER INSERT ON salary_history
FOR EACH ROW BEGIN
UPDATE salary_history SET new_salary = (new.old_salary + new.old_salary * new.percent_change / 100) WHERE emp_no = new.emp_no;
END $$


The other trigger "POST_NEW_ORDER" posts an event named 'new_order', events are used by Firebird (and Interbase) server to notify clients about changes in database states (more on events in this whitepaper) as this feature is not present in MySQL and it's closely related to the application layer I won't even try to reproduce it.
So, right now I've reproduced table structures (whenever possible), views, triggers (whenever possible), in the next part I'll add foreign keys to the tables and start looking at stored procedures.

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

Ok, on with triggers, I'll start migrating the existing triggers then I'll look at triggers as a workaround for computed columns.
Firebird syntax for SAVE_SALARY_CHANGE trigger (active AFTER UPDATE) on table EMPLOYEE is:

...
BEGIN IF (old.salary <> new.salary) THEN
INSERT INTO salary_history
(emp_no, change_date, updater_id, old_salary, percent_change)
VALUES (
old.emp_no,
'NOW',
user,
old.salary,
(new.salary - old.salary) * 100 / old.salary);
END

Now in MySQL terms it becomes:

CREATE TRIGGER save_salary_change AFTER UPDATE ON employee
FOR EACH ROW BEGIN
IF (old.salary <> new.salary) THEN

INSERT INTO salary_history
(emp_no, change_date, updater_id, old_salary, percent_change)
VALUES ( old.emp_no,
now(),
user(),
old.salary,
(new.salary - old.salary) * 100 / old.salary);
END IF;
END $$

As you can see syntax is quite similar, the only differencies are highlighted in red.
Nice, isn't it?
In this specific case I see that the calculated column of table sales_history recomputes the new salary using the old one and the percent change just inserted by the procedure above, so, instead of implementing another trigger I could just modify this to insert the correct values, but I'll build the second trigger just to keep the cascading trigger sequence.

Now, not to be too picky on MySQL QueryBrowser, but it's lacking the appropriate "create trigger" option, so you'll have to create what follows by hand and it's also missing (al least in version 1.1.19) the appropriate infrastructure to graphically show triggers.

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

More on calculated columns, here is the create table statement for one of Firebird's Employee.fdb database:

CREATE TABLE SALARY_HISTORY (
EMP_NO EMPNO NOT NULL,
CHANGE_DATE DATE DEFAULT 'NOW' NOT NULL,
UPDATER_ID VARCHAR(20) NOT NULL,
OLD_SALARY SALARY NOT NULL,
PERCENT_CHANGE DOUBLE PRECISION DEFAULT 0 NOT NULL
CHECK (PERCENT_CHANGE BETWEEN -50 AND 50),

NEW_SALARY COMPUTED BY
(OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100),

PRIMARY KEY (EMP_NO, CHANGE_DATE, UPDATER_ID),
FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO));

I'd go for a trigger based implementation.

After this I'll look at implementing checks and foreign keys (color coding to highlight relevant code samples, I know it can be ugly ;-))

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).

Discordian Date for MySQL

Here is a MySQL version of a function to convert from gregorian to discordian dates.

DELIMITER $

DROP FUNCTION IF EXISTS `test`.`toDiscordian` $
CREATE FUNCTION `toDiscordian`(gdate DATE) RETURNS varchar(50)
DETERMINISTIC
COMMENT 'Discordian Date Converter'
BEGIN

SET @year = EXTRACT(YEAR FROM gdate);
SET @dyear = @year + 1166;
SET @month = EXTRACT(MONTH FROM gdate);
SET @mday = DAYOFMONTH(gdate);
SET @yday = DAYOFYEAR(gdate);
SET @dseason = round(@yday/73);

CASE @dseason
WHEN 0 THEN SET @a_holiday = 'Mungday';
WHEN 1 THEN SET @a_holiday = 'Mojoday';
WHEN 2 THEN SET @a_holiday = 'Syaday';
WHEN 3 THEN SET @a_holiday = 'Zaraday';
WHEN 4 THEN SET @a_holiday = 'Maladay';
ELSE SET @a_holiday = 'Error in a_holiday';
END CASE;

CASE @dseason
WHEN 0 THEN SET @s_holiday = 'Chaoflux';
WHEN 1 THEN SET @s_holiday = 'Discoflux';
WHEN 2 THEN SET @s_holiday = 'Confuflux';
WHEN 3 THEN SET @s_holiday = 'Bureflux';
WHEN 4 THEN SET @s_holiday = 'Afflux';
ELSE SET @s_holiday = 'Error in s_holiday';
END CASE;

CASE @dseason
WHEN 0 THEN SET @name_season = 'Chaos';
WHEN 1 THEN SET @name_season = 'Discord';
WHEN 2 THEN SET @name_season = 'Confusion';
WHEN 3 THEN SET @name_season = 'Bureaucracy';
WHEN 4 THEN SET @name_season = 'The Aftermath';
ELSE SET @name_season = 'Error in name_season';
END CASE;

SET @dday=round((@yday-(73*@dseason))+1);

CASE mod(@dday, 10)
WHEN 1 THEN SET @dday = concat(@dday, 'st');
WHEN 2 THEN SET @dday = concat(@dday, 'nd');
WHEN 3 THEN SET @dday = concat(@dday, 'rd');
ELSE SET @dday = concat(@dday, 'th');

END CASE;

CASE mod(@yday, 5)
WHEN 0 THEN SET @dweekday = 'Sweetmorn';
WHEN 1 THEN SET @dweekday = 'Boomtime';
WHEN 2 THEN SET @dweekday = 'Pungenday';
WHEN 3 THEN SET @dweekday = 'Prickle-Prickle';
WHEN 4 THEN SET @dweekday = 'Setting Orange';
ELSE SET @dweekday = 'Error in dweekday';
END CASE;

CASE
WHEN @dday = 5 THEN SET @celebrate = concat(' Celebrate ', @a_holiday, '.');
WHEN @dday = 50 THEN SET @celebrate = concat(' Celebrate ', @s_holiday, '.');
WHEN (@month = 2 and @mday = 29) THEN SET @celebrate = ' Celebrate St. Tib''s Day.';
ELSE SET @celebrate = '';
END CASE;

SET @ddate = concat(@dweekday, ', ', @dday, ' ', @name_season, ', ', @dyear, '.', @celebrate);

RETURN @ddate;

END $$

DELIMITER ;

You can test it with a simple

SQL>SELECT toDiscordian(now());

It is certainly far from perfect, feel free to improve it and report errors.

Changelog to the people

Right now two other guys added their voice to mine in this feature request to MySQL AB for a public changelog of MySQL QueryBrowser.
Please add your comments too, if interested in it.

Going wireless!!!!

If you live in Europe, as I do, don't think twice about it, go to eBay for your wi-fi gear.
Now I'm on a cheap used (??) Linksys BEFW1154 which works very well.

On with MySQL 5.1

Eager to experiment ... I installed MySQL 5.1.5 on my laptop, in place of an existing MySQL 5.0.x installation from which I borrowed the my.ini file, the outcome has been bug 16781 at MySQL AB, the new one doesn't seem to like this row in the ini file:

# The default storage engine that will be used when create new tables
default-storage-engine=InnoDB

A better PHPBB schema

I've just modified the phpBB schema for PostgreSQL by:

1. Taking it from phpBB 2.0.18
   
2. Placing everything in the "phpbb" schema
3. The schema is referred to a role (owner) named "phpbb"
4. There is some sample data in it (rubbish) you can login as "Admin" password "admin"
5. Now the most relevant changes:
1. Added foreign keys
2. Added unique constraints
3. Tested on PostgreSQL 8.1 win32

Downloadable here

Hope it helps!!
I'm looking forward to improve it from a performance point of view by proper indexing,
stay tuned for updates.

Select a random row from a table with Firebird

After reading this page I noticed that Firebird was missing, so I had to add this little bit of info.
In order to select a random row from a table in Firebird you need to first install an UDF that provides the rand() function, which is ib_udf, it comes for free with the server, but it's not installed by default.
So execute the appropriate sql script to install a bunch of UDFs (ib_udf.sql and fbudf.sql scripts are located in C:\Programmi\Firebird\Firebird_2_0\UDF on my PC along with corresponding ib_udf.dll and fbudf.dll) or install only rand with:

DECLARE EXTERNAL FUNCTION rand
RETURNS DOUBLE PRECISION BY VALUE
ENTRY_POINT 'IB_UDF_rand' MODULE_NAME 'ib_udf';

after installation you can finally select a random row:

SELECT
FIRST 1 *
FROM
COUNTRY
ORDER BY
rand();

Oracle 10g Express

Just installed and the usual question pops up ....

WHY THE HELL THEY CHOSE 8080 AS THE PORT FOR HtmlDB??
IT CONFLICTS WITH Tomcat!!

Firebird ODBC driver multithreaded/non multithreaded

You might have noticed that there are two dlls supplied with Firebird ODBC driver:

OdbcJdbc.dll - Firebird/InterBase(r) driver
OdbcJdbcMT.dll - Firebird/InterBase(r) driver MT

the second one, which will have a description of "Firebird/InterBase(r) driver MT" in the "create ODBC datasource" dialog is the multithreaded version.
If you are using DSN-less connections you should write down the specific driver name:
"DRIVER=Firebird/InterBase(r) driver" (one connection/one thread) "DRIVER=Firebird/InterBase(r) driver MT" (one connection/multiple threads).

Hope this helps!

Distance function for MySQL

Hey, MySQL is still missing the 'distance' function (distance between two points) so I wrote a little one for my convenience, hope it helps others, here it is:

DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`distance` $$
CREATE FUNCTION `distance`(a POINT, b POINT) RETURNS double
DETERMINISTIC
COMMENT 'distance function'
BEGIN
RETURN round(glength(linestringfromwkb(linestring(asbinary(a), asbinary(b)))));
END $$

DELIMITER ;

If you think that this kind of utility functions should ship with MySQL add your voice to this feature request