New challenges, new synthax

This post wants to be:

1. A quick glance at the new "common table expression" (aka hierarchical queries) in Firebird 2.1
2. A call to action for other opensource databases

So, on with 1., Firebird recently added another great feature, common table expressions (CTE) which, to my eye at least, boils down to hierarchical queries.
This is basically the ability to efficiently and easily query hierarchical data stored in the most intuitive way, the adjacency list, because the nested set (see Celko's articles and book) might be the most efficient, but it misses one important thing, widespread adoption ...

A recursive CTE is basically made of two parts, a base query which gives you the starting point and a recursive query which is the action to be performed in a "loop".
You see that the structure is:

WITH RECURSIVE which tells us that this CTE will use recursion
n(....) AS ( which describes the output of the base query, n is just an alias, you can use whatever name you like
SELECT ... which is the base query and sets the starting point, this query will be merged with results of the recursive query through an union
UNION ALL which marks the merge described above
SELECT ... which is the recursive query, note that it's joined to the base query through it's alias
) which closes the recursive block
SELECT ... which ends up the CTE and builds it's output, note that in the second example I applied a further where condition to this last query, something that might be non optimal.

I'll use an example table which mimics a BOM (Bill Of Materials) taken from the web.

CREATE TABLE BILL_OF_MATERIAL(
  PART_NO Varchar(50) NOT NULL,
  PARENT_PART_NO Varchar(50),
  REVISION CHAR(1),
  QUANTITY Integer,
  UOM CHAR(2) NOT NULL,
  DESCRIPTION Varchar(255),
  MAKE_BUY CHAR(1) NOT NULL,
  CONSTRAINT PK_BOM PRIMARY KEY (PART_NO)
);
CREATE INDEX IDX_PARENT_PART_NO ON BILL_OF_MATERIAL (PARENT_PART_NO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON BILL_OF_MATERIAL TO  "SYSDBA" WITH GRANT OPTION; 

Then I'll populate it with some sample rows, the result should be


A query to retrieve BOM for a specific item:

WITH RECURSIVE n(lvl, part_no, description) AS (SELECT 1 lvl, part_no, description 
FROM BILL_OF_MATERIAL 
WHERE description = 'Field Adapter' 
UNION ALL 
SELECT n.lvl + 1, nplus1.part_no, nplus1.description 
FROM BILL_OF_MATERIAL AS nplus1, n 
WHERE n.part_no = nplus1.parent_part_no) 
SELECT lvl, part_no, description FROM n; 

A query to retrieve all buy parts for an item:

WITH RECURSIVE n(part_no, description, quantity, make_buy) AS (SELECT part_no, description, quantity, make_buy
FROM bill_of_material
WHERE description = 'Field Adapter'
UNION ALL
SELECT nplus1.part_no, nplus1.description, nplus1.quantity, nplus1.make_buy
FROM BILL_OF_MATERIAL AS nplus1, n
WHERE n.part_no = nplus1.parent_part_no)
SELECT part_no, description, quantity FROM n WHERE make_buy = 'B'; 

And the result is:



About 2., I'd love to see other mainstream opensource databases run at this, not because MsSQL (standard WITH ... synthax), Oracle (proprietary) and DB2 (standard WITH ... synthax) support it, but because it helps solving real life problems which are becoming more and more common.
I mean, PostgreSQL has a patch floating around which reproduces Oracle's non standard synthax, this patch has been somewhat maintained for a long time, this means that there is active interest, why don't they add it to the codebase? Just for the sake of standards?
And what about MySQL?

As always comments and corrections are more than welcome!

BTW: for comparison, you can find an example about BOM and nested sets here.

UPDATE: Looks like PostgreSQL users and developers felt the hitch, a patch to add CTE has been committed on 04-10-2008 see here.

Back to the Blog ... back to the bug (report)

Today I decide to put up a post about a common requirement which leads to an interesting SQL query (see an example here) using Firebird and Flamerobin, after a few seconds I was writing bug reports :-( see:

[ 1858394 ] Error selecting from mon$statements

and

[ 1858397 ] Error running SELECT query. (Not a Flamerobin bug)

Hope these will be fixed soon.

EDIT: the developers instantly responded to my bug reports saying that the first one is already fixed in SVN and that will be in the next release and the second is not a bug

Well actually bug 1858397 looks like a Firebird bug, unrelated to Flamerobin or IBPP :-( time to close that bug report and post to the right place, which is

[#CORE-1668] Error running SELECT query.

EDIT: looks like this is a duplicate of bug 94, a long standing one that won't be fixed anytime soon :-(

After the ordeal I ended up with

[ 1858592 ] Problem with LIST() function.
List() result is represented as a BLOB so it appears in the grid as a marker and you can't read it's content.
Even in this case the developers quickly clarified the situation:

There are plans to make textual blobs behave just like regular varchar
columns - if not sooner it will be available in version 0.9.0.

Milan.

BTW this query doesn't work:

SELECT
e.emp_no, e.FIRST_NAME, e.LAST_NAME,
CASE 
WHEN ep.emp_no || ep.PROJ_ID IS NULL THEN ' didn''t take part in ' 
ELSE ' took part in ' 
END,
p.proj_name
FROM
employee e, 
project p 
LEFT OUTER JOIN EMPLOYEE_PROJECT ep
ON e.EMP_NO = ep.EMP_NO AND p.proj_id = ep.PROJ_ID 

(If you change the above to a cross join it works fine).
While this does:

SELECT
a.emp_no, a.FIRST_NAME, a.LAST_NAME,
CASE 
WHEN ep.emp_no || ep.PROJ_ID IS NULL THEN ' didn''t take part in ' 
ELSE ' took part in ' 
END,
a.proj_name
FROM
(SELECT * FROM employee e, project p) a 
LEFT OUTER JOIN 
EMPLOYEE_PROJECT ep
ON a.EMP_NO = ep.EMP_NO AND a.proj_id = ep.PROJ_ID;

Lufthansa ships with ...

For those who are spending their miles at the Lufthansa Worldshop for the first time, they ship with UPS ... I haven't been able to find this out from their site and the tracking code remained rather obscure to me until I asked for clarification.
Their answer was quick and accurate, but, is it so difficult to put a link to the UPS site?

Gnuwin32, what about the docs????

Today I had to surrender and use grep on a pile of rubbish, er ..., on some Cognos files, those pesky .icr, so I went to the GNUWin32 site, downloaded the grep installer for windows and run, just like any good (windows) boy would do ... and the first surprise came, the gnuwin32/bin dir is not added to path ... anyway, I added it by hand and went on, in hope that a simple grep --help would give me all infos, here it is:

D:\FOLDER>grep --help
Usage: grep [OPTION]... PATTERN [FILE] ...
Search for PATTERN in each FILE or standard input.
Example: grep -i 'hello world' menu.h main.c

So I'd say single quotes are good, but ... this is what worked ... I'm just pissed by this

D:\FOLDER>grep -H "Table :" *.icr > tables_in_catalog.txt

NO single quotes, but DOUBLE quotes!

Opensource database comparison

"Which is the best opensource database?", "How does MySQL/PostgreSQL/Firebird ... (you name it) stand against Firebird/PostgreSQL/MySQL ... (you name it again)?" I'm shure we've all heard this questions many times, but now I have the definitive answer, by Jim Starkey himself, you can read the full post here, but in short is

"I think the short answer is that nobody with the experience to do this
is interested in doing it. I know that this isn't a particularly useful
answer, but I'm afraid it is the truth."

GREAT!!!

Automating mantenance tasks on MySQL

I'm trying to automate some trivial maintenance tasks for my own MySQL server, and trying also to minimize the effort, so ... Here is the recipe:

Take an excellent generalized stored procedure like the one by Konstantin Osipov, see "Dynamic SQL is Stored Procedures" on MySQLForge (example 4).
Tune it a bit so that it takes into account only non system tables (I'm trying to turn it into something similar to Microsoft's sp_MSforeachtable), here is the code:

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `test`.`sp_4_each_table` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_4_each_table`(db_name VARCHAR(64), template VARCHAR(21845))
BEGIN
#
DECLARE done INT DEFAULT 0;
#
DECLARE tname VARCHAR(64);
#
DECLARE c CURSOR FOR
#
SELECT table_name FROM information_schema.TABLES WHERE table_schema=db_name AND table_type = 'BASE TABLE';
#
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done= 1;
#
 
#
OPEN c;
#
REPEAT
#
FETCH c INTO tname;
#
SET @stmt_text=REPLACE(template, " ?", CONCAT(" ", tname));
#
BEGIN
#
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
#
SELECT CONCAT("'", @stmt_text, "' command failed") AS "error";
#
PREPARE stmt FROM @stmt_text;
#
EXECUTE stmt;
#
DEALLOCATE prepare stmt;
#
END;
#
UNTIL done END REPEAT;
#
END $$
 
DELIMITER ; 

Now with the procedure at hand I'm going on with the scheduling part, taking advantage of the great new "EVENT" feature of MySQL 5.1, see it in action:
First of all check if events are enabled;

Code:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.18-beta-community-nt-debug MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF               |
+-------------------+
1 row in set (0.00 sec)

Add the event to the database, we'll activate events later

Code:

mysql> create event analyze_all
-> on schedule every 1 day
-> starts timestamp '2007-05-27 23:59:59'
-> ends timestamp '2007-05-27 23:59:59' + interval 1 year
-> on completion preserve
-> comment 'updates stats by analyzing tables'
-> do call sp_4_each_table('test', 'analyze table ?');
Query OK, 0 rows affected (0.03 sec)

Check it's existance from the related information_schema table:

Code:

mysql> SELECT
-> event_name,
-> event_type,
-> status
-> FROM information_schema.`EVENTS` E;
+-------------+------------+---------+
| event_name  | event_type | status  |
+-------------+------------+---------+
| analyze_all | RECURRING  | ENABLED |
+-------------+------------+---------+
1 row in set (0.02 sec)

Ok, the event is here.
Now I'll have to enable events on the server and let it rip, it's very simple
just issue an:

Code:

SET GLOBAL event_scheduler=1;

Or start the server with the:

Code:

--event_scheduler=1

Stopping an event without deleting it's also very simple:

Code:

mysql> alter event analyze_all disable;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
-> event_name,
-> event_type,
-> status
-> FROM information_schema.`EVENTS` E;
+-------------+------------+----------+
| event_name  | event_type | status   |
+-------------+------------+----------+
| analyze_all | RECURRING  | DISABLED |
+-------------+------------+----------+
1 row in set (0.00 sec)

Sooner or later I'll show you how this, with "federated tables", "csv storage engine" and "upserts" can really ease and improve an ETL process.

Featured on MySQL Newsletter June 2007. WOW !!!

MYSQLDUMP, mind your options!!

In the process of upgrading my desktop installation of MySQL I noticed that, unlike triggers, stored procedures are not automatically dumped, that's a "not so nice" feature of mysqldump, sic, here is a snippet for those who don't have time to check the manual

C:\Documents and Settings\user>mysqldump -uroot --single-transaction --routines
--databases test remote_test > c:/backup_mysql_20_05_07.sql

-- routines does the trick.
--single-transaction is InnoDB specific for a consistent backup (doesn't apply to MyISAM and other table handlers)
--databases is used because I want to backup only a specific number of databases

Note that after MySQL 5.0.11 triggers are dumped automagically (option enabled by default), use --skip-triggers if downgrading your table structure.

Note also that before 5.0.11 triggers were not dumped and routines had to wait till 5.0.13 (well, 5.0.20 for routines with DEFINER ...) so act accordingly!!!

Another trick, you can use mysqldump to copy database structure from one db or server to another this way:

C:\Documents and Settings\pcuser>mysqldump -uusername -ppassword -d sourcedatabase -n | mysql -uusername -ppassword targetdatabase

This is common on *nix, but it works in windows too.
Key is the
-n no create database
parameter

SAP BW, IF for your BEx formulas

Have you ever felt the need for an IF in your BEx formulas? Didn't find it? That's because SAP expects you to know about boolean math ...
See, we are subtracting two quantities and we want our result to be shown as 0 if the actual result is negative, the trick is very simple, look at this example:

( ( 'GR quantity' - 'PO quantity' ) > 0 ) * ( 'GR quantity' - 'PO quantity' )

The first block will evaluate to 1 when GR quantity is greater than PO quantity and to 0 when it's smaller, so the result will be something like:

GR quantity: 6
PO quantity: 4

(( 6 - 1) > 0) * (6 - 4) will turn into 1 (true) * (6 - 4) = 2

The other case:

GR quantity: 4
PO quantity: 7

(( 4 - 7) > 0) * (4 - 7) will turn into 0 (false) * (4 - 7) = 0

Firebird 2.1 alpha, a quick glance at the new features

After reading about the new Firebird 2.1 alpha I was eager to test it, and here are some sample queries that show how the new features look like, all examples are based on the standard EMPLOYEE.FDB.
The first one is about the MERGE statement, which is useful in many ways (my favourite is synching tables).

MERGE
    INTO country c
    USING (
SELECT * FROM country
UNION ALL
SELECT 'Maroc' country, 'Dirham' currency FROM rdb$database
) cd
    ON (c.country = cd.country)
    WHEN MATCHED THEN
        UPDATE SET
            country = cd.country,
            currency = cd.currency
    WHEN NOT MATCHED THEN
        INSERT (country, currency)
            VALUES (cd.country, cd.currency); 

This will end adding a row to the "country" table, but hey, that's merging data and looks more flexible than ... ON DUPLICATE KEY UPDATE ;-)
A simpler, but less effective and non standard IMHO, syntax available is:

UPDATE OR INSERT INTO
COUNTRY (COUNTRY, CURRENCY)
VALUES ('Poland', 'Zloty')
MATCHING (COUNTRY); 

And a syntax like "update or insert ... select ... matching ..." doesn't seem to be supported.
Can't say I felt the need for this one, but, the more the merrier.

Now, did you ever feel the need for MySQL's GROUP_CONCAT? Here you have LIST(),
see it in action:

SELECT ep.emp_no, LIST(p.proj_name) 
FROM EMPLOYEE_PROJECT ep
RIGHT OUTER JOIN
project p ON ep.PROJ_ID = p.PROJ_ID 
GROUP BY emp_no; 

Guess what the result will be ...
(There seems to be a little bug in isql when showing results from this kind of queries, but it works as expected in Flamerobin).

You can also take advantage of the new database triggers to track connections with something like this:

A database table to hold connections:

CREATE TABLE connection_tracking
(
user_name varchar(50) NOT NULL,
session_id integer NOT NULL,
started_at timestamp NOT NULL,
ended_at timestamp
);

And a database trigger to record each connection:

SET TERM ^ ;
 
CREATE TRIGGER CONNTRACKER 
ACTIVE 
ON CONNECT 
 POSITION 1 
AS 
BEGIN 
    /* enter trigger code here */
INSERT INTO CONNECTION_TRACKING (USER_NAME, SESSION_ID, STARTED_AT)
SELECT
rdb$get_context('SYSTEM', 'CURRENT_USER'),
rdb$get_context('SYSTEM', 'SESSION_ID'),
current_timestamp
FROM rdb$database;
END^
 
SET TERM ; ^ 

You'll end up with something like:

SQL> CONNECT "c:\programmi\firebird\firebird_2_0\examples\empbuild\employee.fdb"
user 'SYSDBA' password 'masterkey';
Database:  "c:\programmi\firebird\firebird_2_0\examples\empbuild\employee.fdb",
User: SYSDBA
SQL> select * from connection_tracking;

USER_NAME                                            SESSION_ID                S
TARTED_AT                  ENDED_AT
================================================== ============ ================
========= =========================
SYSDBA                                                        8 2007-04-08 13:01
:04.8280                     

SQL>

You can also define an ON DISCONNECT trigger to update the row with the "ended_at" information.

SET TERM ^ ;
 
CREATE TRIGGER CONNTRACKER_END
ACTIVE
ON DISCONNECT
POSITION 1
AS
BEGIN
/* enter trigger code here */
UPDATE CONNECTION_TRACKING c
SET c.ended_at = CURRENT_TIMESTAMP
WHERE
c.user_name = rdb$get_context('SYSTEM', 'CURRENT_USER')
AND
c.SESSION_ID = rdb$get_context('SYSTEM', 'SESSION_ID')
AND
c.STARTED_AT = (
SELECT MAX(ct.started_at) FROM CONNECTION_TRACKING ct
WHERE
ct.USER_NAME = c.USER_NAME
AND
ct.SESSION_ID = c.SESSION_ID
);
 
END^ 

This is a powerful database auditing feature!

Now a feature that will make life of those who like mimicking autoincrement keys much easier, it's the great INSERT ... RETURNING ..., similar to what PostgreSQL already implemented with success.
Here is an example, it allows you to retrieve the generated value of the "autoincrementing" column in one pass (hope this will be quickly integrated in the php extension for Firebird).

SQL> select * from new_one;

      COL1 COL2
============ ==========
         1 pluto
         2 COL2
         3 COL2

SQL> insert into new_one(col2) values ('col3') returning col1;

      COL1
============
         5

SQL> select * from new_one;

      COL1 COL2
============ ==========
         1 pluto
         2 COL2
         3 COL2
         5 col3

Example is based on a previous post of mine, you can find it here.

Another nice little thing is the ability to know through SQL which server version are you on:

SELECT RDB$GET_CONTEXT('SYSTEM', 'ENGINE_VERSION')
FROM RDB$DATABASE; 

which will result in

2.1.0

Hooray!!!

Being a mod means

This time I turned the kind gift of the Devshed guys into:

"Financial Intelligence" an interesting book which I hope will help in my ever evolving job, I really need to understand what financial people are saying and what they really mean.

This one looks promising, it's a side of my professional life that I've never really dug into, but it's very interesting (and somehow fun) .

Another handy MySQL function

This time it's a quick function to validate email addresses, based on regexp.
It can be used in a trigger to add data validation, or to check data already in your database that needs a clean up ... really a simple wrapper around a simple regexp query, but it can be helpful.
Here it is:

DELIMITER $$
 
DROP FUNCTION IF EXISTS `test`.`is_valid_email` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `is_valid_email`(p_email varchar(64)) RETURNS tinyint(1)
BEGIN
CASE
WHEN NOT (SELECT p_email REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$')
THEN
-- bad data
RETURN FALSE;
ELSE
-- good email
RETURN TRUE;
END CASE;
END $$
 
DELIMITER ;

As per Mushu's comment, this is much cleaner, oops:

DELIMITER $$
 
DROP FUNCTION IF EXISTS `test`.`is_email_valid` $$
CREATE FUNCTION `test`.`is_email_valid` (p_email varchar(64)) RETURNS tinyint(1)
BEGIN
CASE
WHEN p_email REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$'
THEN
-- good email
RETURN TRUE;
ELSE
-- bad data
RETURN FALSE;
END CASE;
END $$
 
DELIMITER ; 

Also, if you can move such checks into the application code, it may be a wise thing to do as executing regular expressions can easly consume all CPU resources.

Of course doing checks in the application code is cleaner, but the ability to add them later at database level, or to run them in the database without interaction with external apps is also handy ;-)