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!!!