Saturday, February 25, 2006

iPod enabled tie




Look at this great tie!! I want it.

TOra alive and well, releases v. 1.3.21

TOra 1.3.21 is out, release notes:

Notes:
1.3.21:

2006-02-08: Applied patch from Pawe=B3 Kucharczyk
to fix storage queries used against Ora 10gR2.
2006-02-18: Readded Check Syntax to SQL Worksheet
(many thanks to Sergei Kuchin of otl)
2006-02-18: minor changes to PL/SQL Debugger.
Solved a bug where the debugger stops on every call of a stored function,
now when compiling the current position of the cursor is mantained
2006-02-18: Now when you open the search and replace dialog,
the focus is always on the search field

After the olympics ... ski mountaineering championships!

Friday, February 17, 2006

The case for function based indexes

Since a lot of codes are "speaking codes" (...) meaning that they can be split into fixed meaningful substrings I often see queries like this:

select
*
from
t_table
where
c_code like 'DD6%'


which is highly inefficient, look at it's explain plan:

Seq Scan on t_table (cost=100000000.00..100000001.06 rows=1 width=33)
Filter: (((c_code)::text ~~ 'DD6%'::text)


Since the first 3 chars only are meaningful, you can exploit one of the great features of PostgreSQL, function based indexes, create an index like this:

CREATE INDEX t_table_c_code_part_idx
ON t_table (substring(c_code, 1, 3));


and change the query to:

select
*
from
t_table
where
substring(postcode, 1, 3) = 'DD6'


Now look at the explain plan again:

Index Scan using t_table_postcode_part_idx on t_table (cost=0.00..4.69 rows=1 width=33)
Index Cond: ("substring"((postcode)::text, 1, 3) = 'DD6'::text)


WOW, the index is correctly picked up and query efficiency is greately enhanced!!!

Saturday, February 11, 2006

Borland to dismiss Interbase (again)?

An email from the Italian branch of Borland Developer News announced that Borland is to dismiss a lot of it's more traditional business, including Interbase (no mention of it in Tod Nielsen's letter) .
Time to switch the remaining Interbase customers to Firebird?

Tuesday, February 07, 2006

TOra 1.3.20 is out

Great news for all Oracle users, after the release of a free version of Oracle 10g (named Oracle Database 10g Express Edition) which is free to develop, deploy and distribute, we now have a new version of the great opensource management tool for Oracle, TOra 1.3.20, which nicely complements the free database and will hopefully take the place of the free version of TOAD on my PC.
More on this later, but the connection test to Oracle 10g Express has been successful ;-)

Friday, February 03, 2006

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

A more interesting example:

SET TERM ^ ;
CREATE PROCEDURE ADD_EMP_PROJ (
EMP_NO Smallint,
PROJ_ID Char(5) )
ASBEGIN
BEGIN
INSERT INTO employee_project (emp_no, proj_id) VALUES (:emp_no, :proj_id);
WHEN SQLCODE -530 DO
EXCEPTION unknown_emp_id;
END
SUSPEND;
END^
SET TERM ; ^



This procedure makes use of exceptions to manage errors, MySQL currently doesn't support them but equivalent can be achieved with condition handlers
and variables, see below:

DELIMITER $$

DROP PROCEDURE IF EXISTS `employee`.`add_emp_proj` $$
CREATE PROCEDURE `employee`.`add_emp_proj` (IN empno smallint, IN projid char(5), OUT error char(5))
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error = 'error';

-- DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK
SET @add_emp_proj = concat('INSERT INTO employee_project (emp_no, proj_id) VALUES (', empno, ',', projid, ')');
PREPARE `add_emp_proj` FROM @add_emp_proj;
EXECUTE `add_emp_proj`;
DEALLOCATE PREPARE `add_emp_proj`;
END $$

DELIMITER ;


there is also a more radical handler commented out.
This procedure shows usage of prepared statements in sps and also usage of a variable to check procedure
execution status, see it in action:

mysql> set @error = 'none';
Query OK, 0 rows affected (0.00 sec)

mysql> select @error from dual;
+--------+
| @error |
+--------+
| none |
+--------+
1 row in set (0.00 sec)

mysql> CALL add_emp_proj(2, '5', @error);
Query OK, 0 rows affected (0.00 sec)

mysql> select @error from dual;
+--------+
| @error |
+--------+
| error |
+--------+
1 row in set (0.00 sec)

mysql>

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

Trying to keep pace with work and porting Firebird stored procs to MySQL can be really hard ... anyway here are two sp's translated:

Firebird procedure to list some stats for departments by department head:

SET TERM ^ ;
CREATE PROCEDURE SUB_TOT_BUDGET (
HEAD_DEPT Char(3) )
RETURNS (
TOT_BUDGET Decimal(12,2),
AVG_BUDGET Decimal(12,2),
MIN_BUDGET Decimal(12,2),
MAX_BUDGET Decimal(12,2) )
ASBEGIN
SELECT SUM(budget), AVG(budget), MIN(budget), MAX(budget)
FROM department
WHERE head_dept = :head_dept
INTO :tot_budget, :avg_budget, :min_budget, :max_budget;
SUSPEND;
END^
SET TERM ; ^

So Firebird seems to be a bit more verbose, with complete declaration of parameters.
Here is the MySQL translation:

DELIMITER $

DROP PROCEDURE IF EXISTS `employee`.`sub_tot_budget` $$
CREATE PROCEDURE `employee`.`sub_tot_budget` (IN head_dept char(3))
BEGIN
SELECT SUM(d.budget), AVG(d.budget), MIN(d.budget), MAX(d.budget)
FROM department d
WHERE head_dept = head_dept;
END $$

DELIMITER ;


As you can see syntax is quite similar but more compact.

Now another one, a procedure to list the first five languages required for a job:

SET TERM ^ ;
CREATE PROCEDURE SHOW_LANGS (
CODE Varchar(5),
GRADE Smallint,
CTY Varchar(15) )
RETURNS (
LANGUAGES Varchar(15) )
ASDECLARE VARIABLE i INTEGER;
BEGIN
i = 1;
WHILE (i <= 5) DO BEGIN SELECT language_req[:i] FROM joB WHERE ((job_code = :code) AND (job_grade = :grade) AND (job_country = :cty) AND (language_req IS NOT NULL)) INTO :languages; IF (languages = ' ') THEN /* Prints 'NULL' instead of blanks */ languages = 'NULL'; i = i +1; SUSPEND; END END^ SET TERM ; ^


Probably the employee.fdb database hasn't changed from the old Firebird 1 (and InterBase) days, note the workaround used to get the first 5 languages, it's now superseded by the FIRST ... SKIP clause introduced in Firebird 1.5 which is equivalent to MySQL's LIMIT clause.

The MySQL version is:

DELIMITER $

DROP PROCEDURE IF EXISTS `employee`.`show_langs` $$
CREATE PROCEDURE `employee`.`show_langs` (IN code varchar(5), IN grade SMALLINT, IN cty varchar(15))
BEGIN
SELECT j.language_req FROM job j
WHERE j.job_code = code AND j.job_grade = grade AND j.job_country = cty
LIMIT 5;
END $$

DELIMITER ;

The sintax is similar again, still I find Firebird procs a bit more readable ...

Another easy take:


SET TERM ^ ;
CREATE PROCEDURE GET_EMP_PROJ (
EMP_NO Smallint )
RETURNS (
PROJ_ID Char(5) )
ASBEGIN
FOR SELECT proj_id
FROM employee_project
WHERE emp_no = :emp_no
INTO :proj_id
DO
SUSPEND;
END^
SET TERM ; ^


Becomes:

DELIMITER $$

DROP PROCEDURE IF EXISTS `employee`.`get_emp_proj` $$
CREATE PROCEDURE `employee`.`get_emp_proj` (IN empno SMALLINT)
BEGIN
SELECT proj_id FROM employee_project WHERE emp_no = empno;
END $$

DELIMITER ;

Wednesday, February 01, 2006

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

More on foreign keys in MySQL:

After all those FK added I can only recommend to check that columns have the same datatype, if this is not true MySQL will spit strange error messages like "can't create table ..." when trying to add foreign keys.

Foreign keys are not deferrable in MySQL so the circular reference between employee and department tables makes impossible to load employee before dept and impossible to load dept before loading employee.
As those tables are innodb i can't issue an "alter table disable keys" so I'll
have to resolve the circular fk relationship between those tables and use triggers to
enforce referential integrity (keys INTEG_61 and INTEG_52 will be deleted and substituded with triggers).
A simpler workaround useful for massive data loads is to issue a "set foreign_key_checks=0" before loading and then a "set foreign_key_checks=1" after load.