Speaker: Philippe Makowski

Firebird
Present Futur
July 2008

Thanks to Paul Beach and Vladyslav Khorsun for their help
IBPhoenix
Slide 1

Speaker: Philippe Makowski

Development
Engine Development
Firebird 2.0.4 third bugfix sub-release
Firebird 2.1.1 released.
Firebird 2.5 is now alphaFirebird 1.5.5 was also released

IBPhoenix
Slide 2

Speaker: Philippe Makowski

All Downloads
2.1.0
2.0.3
2.0.1
2.0.0
1.5.4
1.5.3
1.5.2
1.5.1
1.5.0
1.0.3
1.0.2
1.0.0

Windows 32
Windows 64
Linux 32
Linux 64
MacOS
Sinix
Solaris Sparc
Solaris IntelAIX
FreeBSD
HPUX
0200,000

400,000

600,000800,000
IBPhoenix
Slide 3

Speaker: Philippe Makowski

Main Downloads
2.1.0
2.0.3
2.0.1
2.0.0
1.5.4
1.5.3
1.5.2
1.5.1
1.5.0
1.0.3
1.0.2
1.0.0

Windows 32
Windows 64
Linux 32
Linux 64

0200,000

400,000

600,000

800,000
IBPhoenix
Slide 4

Speaker: Philippe Makowski

Other Platforms
2.1.0
2.0.3
Linux 64
MacOS
Sinix
Solaris Sparc
Solaris IntelAIXFreeBSD
HPUX

2.0.1
2.0.0
1.5.3
1.5.2
1.5.0
1.0.3
1.0.2
1.0.0
02,000

4,000

6,000

8,000

10,00012,000
IBPhoenix
Slide 5

Speaker: Philippe Makowski

V2.1•

Global temporary tables•

Common table expressions•

Monitoring tables•

Database triggers•

New built-in functions•

Win64 build•

Trusted Windows authentication•

Improved performance (network, sort, ...)•

Domains everywhere•

Insert or update, merge, list
IBPhoenix
Slide 6

Speaker: Philippe Makowski

Monitoring tables• Activity snapshot retained till the end of transaction• Works with both Super and Classic Server• Regular users restricted to see own attachment• Kill long running query
MON$DATABASE
MON$DATABASE_NAME...

Databases and objects
MON$STATEMENTS

MON$ATTACHMENTS
MON$ATTACHMENT_ID
MON$ATTACHMENT_NAME...

MON$STATEMENT_ID
MON$ATTACHMENT_ID
MON$TRANSACTION_ID...

MON$CALL_STACK
MON$TRANSACTIONSMON$TRANSACTION_ID
MON$ATTACHMENT_ID...

MON$CALL_ID
MON$STATEMENT_ID
MON$CALLER_ID...

IBPhoenix
Slide 7

Speaker: Philippe Makowski

Database triggers• Database-wide triggers fired on :• CONNECT• DISCONNECT• TRANSACTION START• TRANSACTION COMMIT• TRANSACTION ROLLBACK• Only SYSDBA or database owner can :• define database triggers• switch it off for new connection by :● new isc_dpb_no_db_triggers tag● new -no_dbtriggers switch in utilities

IBPhoenix
Slide 8

Speaker: Philippe Makowski

Database triggers example
Example of ON CONNECT trigger

isql temp.fdb -user SYSDBA -pass masterkey
Database: temp.fdb, User: SYSDBASQL> SET TERM ^ ;SQL> CREATE EXCEPTION EX_CONNECT 'Forbidden !' ^SQL> CREATE OR ALTER TRIGGER TRG_CONN ON CONNECT
CON> AS
CON> BEGINCON>
IF (USER != 'GOOD_USER')CON>
THEN EXCEPTION EX_CONNECT USER || ' not allowed !';CON> END ^SQL> EXIT ^isql temp.fdb -user BAD_USER -pass ...
Statement failed, SQLCODE = -836
exception 217-EX_CONNECT-BAD_USER not allowed !
-At trigger 'TRG_CONN' line: 5, col: 3
Use CONNECT or CREATE DATABASE to specify a database
SQL> EXIT;

IBPhoenix
Slide 9Speaker: Philippe Makowski

Database triggers example
Help ! My ON CONNECT trigger deny all users and even
SYSDBA ! Nobody can log in anymore :(What can i do ???
Don't panic :) Use -no_dbtriggers switch in utilities :isql temp.fdb -user SYSDBA -pass masterkey -nodbtriggers
Database: temp.fdb, User: SYSDBASQL> ALTER TRIGGER TRG_CONN INACTIVE;
SQL> EXIT;

IBPhoenix
Slide 10

Speaker: Philippe Makowski

Global temporary tables• Standard properties• Common metadata and private data
• Indices, triggers, uniqueness check and referential
integrity• Lifetime : attachment or transaction• DELETE triggers are not fired on cleanup• Firebird’s specific• No problem with garbage collection• Storage is in temporary files, forced writes off• Instant cleanup

IBPhoenix
Slide 11

Speaker: Philippe Makowski

Global temporary tables• Syntax• CREATE GLOBAL TEMPORARY TABLE…
[ON COMMIT <DELETE | PRESERVE> ROWS]• ON COMMIT DELETE ROWS
data lifetime - transaction• ON COMMIT PRESERVE ROWS
data lifetime - attachment• By default - DELETE ROWS

IBPhoenix
Slide 12

Speaker: Philippe Makowski

Global temporary tables
Common metadata

Private dataattachment 1
INSERT INTO T1

SELECT FROM T1

disconnect

CREATE GLOBALTEMPORARY TABLE
T1
pages not allocatedpages allocated(temporary file1, FW =OFF)

pages allocatedpages freed(temporary filedeleted)attachment 2
SELECT FROM T1

INSERT INTO T1

disconnect

database
pages not allocatedpages not allocatedpages allocated(temporary file2, FW =OFF)

pages freed(temporary filedeleted)

IBPhoenix
Slide 13

Speaker: Philippe Makowski

Common table expressions•Syntax• WITH [RECURSIVE]• CTE_A-- new keywords-- first table expression’s name[(a1, a2, …)]-- fields aliases, optional

AS ( SELECT … ),-- table expression’s definition• CTE_B[(b1, b2, …)]-- second table expression

AS ( SELECT … ),…

• SELECT …
FROM CTE_A, CTE_B,
TAB1, TAB2
WHERE …

-- main query, used both-- table expressions-- and regular tables

IBPhoenix
Slide 14

Speaker: Philippe Makowski

Common table expressions
Example quartile (non recursive)
SET TERM !!;EXECUTE BLOCKASDECLARE VMESURE DOUBLE PRECISION;
DECLARE VMYRANK BIGINT = 0;
BEGIN
FOR SELECT MESURE FROM TNODE_VAL WHERE ID=100 ORDER BY MESURE ASC INTO :VMESURE
DO
BEGIN
VMYRANK = VMYRANK+1;
INSERT INTO TNODE_TEMP (MYRANK, MESURE) VALUES (:VMYRANK,:VMESURE);
END
END !!
WITH C (MAXI) AS (SELECT MAX(MYRANK) FROM TNODE_TEMP)SELECT E1.MESURE Q1, E2.MESURE Q2, E3.MESURE Q3, E4.MESURE Q4
FROM TNODE_TEMP E1
JOIN C ON E1.MYRANK=CEIL(C.MAXI/4)
CROSS JOINTNODE_TEMP E2
CROSS JOINTNODE_TEMP E3
CROSS JOINTNODE_TEMP E4
WHERE E2.MYRANK=CEIL(C.MAXI/2)
AND E3.MYRANK=CEIL(C.MAXI*3/4)
AND E4.MYRANK=CEIL(C.MAXI)

IBPhoenix
Slide 15

Speaker: Philippe Makowski

Common table expressions
Example chronological serie (recursive)
WITH RECURSIVE CTETemps (madate) AS(SELECT date '2004-02-01' maDate FROM RDB$DATABASEUNION ALL
SELECT maDate + 1
FROM CTETemps
WHERE maDate + 1 < '2004-03-01'),
CTEVAL (madate,moyenne,nombre) AS(SELECT CAST(TNODE_VAL.DATE_MESURE AS DATE), Avg(TNODE_VAL.Mesure) , COUNT(*)
FROM TNODE_VAL
WHERE TNODE_VAL.ID=100
AND CAST(TNODE_VAL.DATE_MESURE AS DATE) BETWEEN '2004-02-01' AND '2004-02-29'
GROUP BY 1)
SELECT CTETemps.madate, COALESCE(CTEVAL.Moyenne,'N/A') Moyenne ,CTEVAL.nombre
FROM CTETemps
LEFT JOIN CTEVAL ON CTETemps.madate=CTEVAL.madate

IBPhoenix
Slide 16

Speaker: Philippe Makowski

Domains everywhere•Syntax
data_type ::= <builtin_data_type>| <domain_name>| TYPE OF <domain_name>•Using plain <domain_name> data type inherits domain constraints(CHECK, NOT NULL) and default value•Using new keyword TYPE OF only data type from domain definition isinherited.
•Engine track dependency between domain and stored procedure ortrigger where its used and not allows to drop such domains•When a domain is altered engine don’t recompile dependent objects•Dependent objects will take up new domain definition only after reload
into metadata cacheIBPhoenix
Slide 17

Speaker: Philippe Makowski

Update or insert•Syntax• UPDATE OR INSERT INTO <table> [(col1, …, colN)]
VALUES (val1, …, valN)-- new keyword MATCHING, optional• [MATCHING (mc1, …, mcM)]• [RETURNING (ret1, …, retR) -- optional RETURNING ... INTO
[INTO :var1, …, :varR]]-- clause (introduced in FB 2.0)

IBPhoenix
Slide 18

Speaker: Philippe Makowski

Update or insert rules•If optional clause MATCHING is omitted then primary key is used
to match table rows against replaced values•If RETURNING clause is used then no more than one row mustcorrespond to MATCHING criteria•“IF (ROWCOUNT = 0) …” construction can be used in PSQL only
while UPDATE OR INSERT can be used directly as it is usual SQL
statement•User must have both INSERT and UPDATE privileges on table•If row was inserted then INSERT trigger fired else fired UPDATE
trigger

IBPhoenix
Slide 19

Speaker: Philippe Makowski

Merge•Syntax• MERGE INTO <table>• USING <table_or_join>
ON <search_condition>• [WHEN MATCHED THEN-- new keyword MERGE-- source of data for merging-- how to find target row-- UPDATE query specification

UPDATE SET col1 = val1, …, colN = valN]-- INSERT query specification• [WHEN NOT MATCHED THEN
INSERT [(col1, …, colN)]
VALUES (val1, …, valN)]•Both INSERT and UPDATE query specifications are optional but at least oneof them must be defined

IBPhoenix
Slide 20

Speaker: Philippe Makowski

Merge example
CREATE TABLE AMOUNTS(GOODID INT,SUMMA NUMERIC(18, 4),CONSTRAINT PK_AMOUNTSPRIMARY KEY (GOODID)

MERGE INTO AMOUNTS USING ENTRIES EON GOODID = E.GOODID
WHEN MATCHED
THEN UPDATE SET SUMMA = SUMMA + E.DBT - E.CRD
WHEN NOT MATCHED
THEN INSERT (GOODID, SUMMA)
VALUES (E.GOODID, E.DBT - E.CRD));
CREATE TABLE ENTRIES(GOODID INT,DT
DATE,DBT
NUMERIC(18, 4),CRD
NUMERIC(18, 4),);

CONSTRAINT PK_ENTRIESPRIMARY KEY (GOODID, DT)

GOODID
12221

ENTRIESDT
DBT
01.01.2006 10.00
03.01.2006
0.00
02.01.2006 10.00
01.01.2006 15.00
05.01.2006
0.00

CRD
0.00
4.00
0.00
0.00
3.00

AMOUNTSGOODID SUMMA
17.00
221.00

IBPhoenix
Slide 21

Speaker: Philippe Makowski

New built-in functions• Mathematical
ABS, MOD, SIGN, CEIL, CEILING, FLOOR, ROUND, TRUNC, PI●

LOG, LOG10, LN, POWER, EXP, SQRT

●COS, COSH, ACOS, SIN, SINH, ASIN, TAN, TANH, ATAN, ATAN2, COT●

• Bit logic
BIN_AND, BIN_OR, BIN_SHL, BIN_SHR, BIN_XOR●• Datetime arithmetics
DATEADD, DATEDIFF●• String manipulations
ASCII_CHAR, ASCII_VAL●

LEFT, RIGHT, LPAD, RPAD, POSITION, REPLACE, OVERLAY, REVERSE●• Other
DECODE, MAXVALUE, MINVALUE●

GEN_UUID, HASH, RAND●

LIST (String aggregating function)●IBPhoenix
Slide 22

Speaker: Philippe Makowski

Engine Development
Core Development
Dimitry Yemanov(Project Co-ordinator)
Claudio Valderrama
Vlad Horsun
Alex Peshkov
Adriano dos SantosFernandes
Arno Brinkman
Alex Krutyakov

Assisted byPaul Reeves
Helen BorriePavel Cisar
Philippe Makowski
Paul Beach

IBPhoenix
Slide 23

Speaker: Philippe Makowski

JaybirdDevelopers
Roman Rokytskyy (Project Admin)
Evgeny Putilin
Gabriel Reid•

Development
Latest V2.1.5 June 2008
V2.2.2 in development with test builds

IBPhoenix
Slide 24

Speaker: Philippe Makowski.NET Provider
Developers
Jiri Cincura (Project Admin)
Alessandro Petrelli
Le Roy Arnaud
Rick Hoover•Development
ADO .Net Provider, Win32 & Mono
Nov 2007 - V2.1 Released
Mar 2008 V2.5 Alpha 2 - towards support for New
Entity Framework (.NET 3.5 & 2.0)

IBPhoenix
Slide 25

Speaker: Philippe Makowski

Python driver
Developers
Pavel Cisar (Project Admin)•Development
KinterbasDB 3.2.1 release July 2008 (maintenancerelease)
Future : rewrite in pure Python using the wire
protocol instead using client library

IBPhoenix
Slide 26

Speaker: Philippe Makowski

Documentation
Developers•Paul Vinkenoog, Helen Borrie, Norman Dunbar,
Geoff Worboys, Frank Ingermann, Sergey Mereutsa
and translators

Development

New doc : Firebird 1.5 Language reference.
Other volumes will follow, each encompassing amacro release. Translations are under way.

IBPhoenix
Slide 27

Speaker: Philippe Makowski

Organisational
Releasing “Often”
Regular Snapshot builds for Linux x86, x84,
WindowsHigh usage of Jira bug tracker
Increase in commitments of developer hours
One new developer joined the Core teamIBPhoenix
Slide 28

Speaker: Philippe Makowski

Bug Tracker

IBPhoenix
Slide 29

Speaker: Philippe Makowski

Porting Hall of Fame
Firebird 2.x
Linux AMD64 - Alex Peshkov
Linux MIPSEL - Fabrice Aeschbacher
Linux PPC - Damyan Ivanov
Win64 (AMD & EM64T) - Dimitry Yemanov
FreeBSD - Sergey Skvortsov
MacOS - Paul BeachSolaris Intel and Sparc - Paul Beach & Alex Peshkov
HPUX (ongoing) - Paul Beach & Alex Peshkov

IBPhoenix
Slide 30

Speaker: Philippe Makowski

Other Highlights
SourceForge Community Choice award in 2
categories (Best Project for Enterprise andBest User Support) August 2007
Actively maintained Firebird FAQ site started
by David Richmond at www.firebirdfaq.org
Dec 2007 Sourceforge project of the month

IBPhoenix
Slide 31

Speaker: Philippe Makowski

Other Highlights
Firebird 2.x fully ported to Mac Intel andnative platforms. 64bit for Intel, 64bit for PPC
coming soon
Firebird 2.0.x Solaris Classic (Intel & Sparc)
ports available. Work continues on 64bit
Firebird 2.0.x HPUX Classic 64bit

IBPhoenix
Slide 32

Speaker: Philippe Makowski

Resource Donations
Donation of a Mac for builds and testing
Broadview Systems continues to provide
hosting and on-site management of web,tracker and mail servers
Can you help?

IBPhoenix
Slide 33

Speaker: Philippe Makowski

Firebird's Future
Current situation
3 versions under active development
Pressure to continue support for V.1.5
Relatively small teamMore funding and assistance is always
neededIBPhoenix
Slide 34

Speaker: Philippe Makowski

V2.5•

Scalable multi-threading with per-connection caches•

Optimised lock management•

Monitoring extensions•

ALTER VIEW statement•

Support for regular expressions in SQL•

Autonomous transactions•

User account maintenance via SQL•

New built-in functions•

Queries against external databases•

Audit/logging facilities
IBPhoenix
Slide 35

Speaker: Philippe Makowski

New threading architecture•

For superserver :•For Classic :•threads distributed evenly to available
processors according the database clients
attach toNow multi-threaded, even on Posix

Superclassic :Multi threaded mode with connection pool
performs better than Classic• use fewer kernel ressources• does not require [x]inted• but still cache per connexions•IBPhoenix
Slide 36

Speaker: Philippe Makowski

Regex Search using SIMILAR TO•

Syntax :•<similar predicate> ::=<value> [ NOT ] SIMILAR TO <similar pattern>[ ESCAPE <escape character> ]•create table department (number numeric(3) not null,name varchar(25) not null,phone varchar(14)check (phone similar to '\([0-9]{3}\) [0-9]{3}\-[0-9]{4}' escape '\'));
insert into department
values ('000', 'Corporate Headquarters', '(408) 555-1234');
insert into department
values ('100', 'Sales and Marketing', '(415) 555-1234');
insert into department
values ('140', 'Field Office: Canada', '(416) 677-1000');
insert into department
values ('600', 'Engineering', '(408) 555-123'); -- check constraint violation
select * from department
where phone not similar to '\([0-9]{3}\) 555\-%' escape '\';
IBPhoenix
Slide 37

Speaker: Philippe Makowski

Autonomous transaction•Syntax :IN AUTONOMOUS TRANSACTION DO<simple statement | compound statement>
SET TERM ^ ;CREATE OR ALTER TRIGGER TRG_CONN ON CONNECTAS
BEGIN
IF (USER != 'GOOD_USER') THENBEGIN
IN AUTONOMOUS TRANSACTION
DOBEGIN
INSERT INTO LOG (LOGDATE,MSG) VALUES (CURRENT_TIMESTAMP, 'Connection rejected');END
EXCEPTION EX_CONNECT USER || ' not allowed !';END ^IBPhoenix
Slide 38

Speaker: Philippe Makowski

External queries•••••••

Syntax :[FOR] EXECUTE STATEMENT <query_text> [(<input_parameters>)][ON EXTERNAL [DATA SOURCE] <connection_string>][WITH {AUTONOMOUS | COMMON} TRANSACTION][AS USER <user_name>][PASSWORD <password>][INTO <variables>]

IBPhoenix
Slide 39

Speaker: Philippe Makowski

Monitoring extension•Two more monitoring tables :MON$MEMORY_USAGE
 MON$CONTEXT_VARIABLES
Top 10 statements ranked according to their memory usage
SELECT FIRST 10
STMT.MON$ATTACHMENT_ID,
STMT.MON$SQL_TEXT,
MEM.MON$MEMORY_USEDFROM MON$MEMORY_USAGE MEM
NATURAL JOIN MON$STATEMENTS STMTORDER BY MEM.MON$MEMORY_USED DESC

IBPhoenix
Slide 40Speaker: Philippe Makowski

Terminating a client•

Now you can also terminate client session
To cancel all clients except the current connection

DELETE FROM MON$ATTACHMENTS
WHERE MON$ATTACHEMENT_ID <> CURRENT_CONNECTION

IBPhoenix
Slide 41

Speaker: Philippe Makowski

Roadmap•

July 2008
•

October 2008•

2.1.1, 2.5 alpha2.5 RC1

Q4 2008

2.5, 2.0.5, 2.1.2, 3.0 alpha

IBPhoenix
Slide 42Speaker: Philippe Makowski

Firebird 3.0
External (e.g. Java) procedures
 Effective multi-treading with shared cache Compiled statement cache Per-database configuration
 Cross-database queries
 External (e.g. Java) procedures New SQL features
IBPhoenix
Slide 43

Speaker: Philippe Makowski

Thank You
IBPhoenix
Slide 44