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