Saturday, April 07, 2007

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

  1. MERGE
  2. INTO country c
  3. USING (
  4. SELECT * FROM country
  5. UNION ALL
  6. SELECT 'Maroc' country, 'Dirham' currency FROM rdb$database
  7. ) cd
  8. ON (c.country = cd.country)
  9. WHEN MATCHED THEN
  10. UPDATE SET
  11. country = cd.country,
  12. currency = cd.currency
  13. WHEN NOT MATCHED THEN
  14. INSERT (country, currency)
  15. 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:

  1. UPDATE OR INSERT INTO
  2. COUNTRY (COUNTRY, CURRENCY)
  3. VALUES ('Poland', 'Zloty')
  4. 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:

  1. SELECT ep.emp_no, LIST(p.proj_name)
  2. FROM EMPLOYEE_PROJECT ep
  3. RIGHT OUTER JOIN
  4. project p ON ep.PROJ_ID = p.PROJ_ID
  5. 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:

  1. CREATE TABLE connection_tracking
  2. (
  3. user_name varchar(50) NOT NULL,
  4. session_id integer NOT NULL,
  5. started_at timestamp NOT NULL,
  6. ended_at timestamp
  7. );

And a database trigger to record each connection:

  1. SET TERM ^ ;
  2. CREATE TRIGGER CONNTRACKER
  3. ACTIVE
  4. ON CONNECT
  5. POSITION 1
  6. AS
  7. BEGIN
  8. /* enter trigger code here */
  9. INSERT INTO CONNECTION_TRACKING (USER_NAME, SESSION_ID, STARTED_AT)
  10. SELECT
  11. rdb$get_context('SYSTEM', 'CURRENT_USER'),
  12. rdb$get_context('SYSTEM', 'SESSION_ID'),
  13. current_timestamp
  14. FROM rdb$database;
  15. END^
  16. 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.

  1. SET TERM ^ ;
  2. CREATE TRIGGER CONNTRACKER_END
  3. ACTIVE
  4. ON DISCONNECT
  5. POSITION 1
  6. AS
  7. BEGIN
  8. /* enter trigger code here */
  9. UPDATE CONNECTION_TRACKING c
  10. SET c.ended_at = CURRENT_TIMESTAMP
  11. WHERE
  12. c.user_name = rdb$get_context('SYSTEM', 'CURRENT_USER')
  13. AND
  14. c.SESSION_ID = rdb$get_context('SYSTEM', 'SESSION_ID')
  15. AND
  16. c.STARTED_AT = (
  17. SELECT MAX(ct.started_at) FROM CONNECTION_TRACKING ct
  18. WHERE
  19. ct.USER_NAME = c.USER_NAME
  20. AND
  21. ct.SESSION_ID = c.SESSION_ID
  22. );
  23. 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:

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

which will result in

2.1.0

Hooray!!!