www.destructor.de
This is a compilation of new features and issues in Firebird 1.5. The complete list (which is much longer and contains every single bugfix) can be found in the "WhatsNew" file in the source package's doc directory.
↓ Other
Any database can be attached to using an "alias" name instead of its physical pathname. The list of known database aliases is stored in the aliases.conf file under the server installation root.
Example:
| Entry in aliases.conf |
my_database = d:\dbs\my\database.gdb |
|---|---|
| Connection string |
my_server:my_database |
The On-Disk Structure version number has changed to 10.1
The filenames of various server files have changed to "Firebird" names (fbserver, fbclient, firebird.msg etc.)
The client library is fbclient now (fbclient.dll on Windows) and it should be used in all new FB-based projects. gds32 (gds32.dll on Windows) contains nothing but redirected exports and is provided for compatibility only.
ibserver.exe → fbserver.exe (SuperServer architecture)
ibguard.exe → fbguard.exe
interbase.msg → firebird.msg
interbase.log → firebird.log
isc4.gdb → security.fdb
gds32.dll → fbclient.dll
Now the recommended extension for database files is ".fdb" to avoid possible conflicts with "system restore" feature of Windows ME/XP operating systems.
| General |
LOCAL_MACHINE\SOFTWARE\Firebird Project\Firebird Server\ |
|---|---|
| Instances |
HKLM\SOFTWARE\Firebird Project\Firebird Server\Instances |
| Default Instance |
HKLM\SOFTWARE\Firebird Project\Firebird Server\Instances\DefaultInstance |
Some platforms are no longer supported by the current source code: DELTA, IMP, DG_X86, M88K, UNIXWARE, Ultrix, NeXT, ALPHA_NT, DGUX, MPE/XL, DecOSF, SGI, HP700, Netware, MSDOS, SUN3_3.
IPX/SPX network protocol is no longer supported.
Classic Server (CS) architecture is now supported on Win32 as well, but it still cannot be considered stable, so any feedback is welcome.
All transport protocol names (INET port and service, WNET pipe, IPC map) are now configurable via firebird.conf.
Map name for local (IPC) protocol is changed, so v1.5 client library is no longer compatible with the previous versions via IPC.
Connection to the security database security.fdb is now cached, thus allowing to decrease time of subsequent database attachments.
The server will exit when there is no or a wrong firebird.conf.
RootDirectory string SortMemBlockSize integer default 1048576 (1 MB) SortMemUpperLimit integer Defaults: Superserver (SS), 67108864 (64 MB). Classic server (CS), 0. RemoteFileOpenAbility boolean default false. (experimental?) Allow DB files used over NFS mounts. GuardianOption integer default 1 Affects the operation of the guardian: 0 - only start the Firebird service once. 1 - restart the Firebird service if it should terminate. CpuAffinityMask integer default 1 A bit-field defining what CPU's the process should be allowed to run on. Only meaningful for Windows SS on SMP systems. OldParameterOrdering boolean default false TcpRemoteBufferSize integer default 8192 (bytes) TcpNoNagle boolean default false IpcMapSize integer default 4096 (bytes) DefaultDbCachePages integer default SS: 2048. CS: 75 ConnectionTimeout integer default 180 (seconds) DummyPacketInterval integer default 60 (seconds) LockMemSize integer default 262144 (256 KB) LockSemCount integer default SINIX-Z: 25. All others: 32 LockSignal integer default 16 (signal # - (only used on POSIX CS?)) LockGrantOrder boolean default true LockHashSlots integer default 101 LockRequireSpins boolean default false EventMemSize integer default 65536 DeadlockTimeout integer default 10 (seconds) SolarisStallValue integer default 60 (seconds) TraceMemoryPools boolean default false PrioritySwitchDelay integer default 100 (milliseconds) DeadThreadsCollection integer default 50 PriorityBoost integer default 5 RemoteServiceName string default gds_db RemoteServicePort integer default 3050 (TCP port number) RemotePipeName string default "interbas" (Windows only?) IpcName string default "FirebirdIPI" (Windows only) MaxUnflushedWrites integer # of writes before file writes are forcibly synched. Windows default: 100. All else -1 (i.e. don't forcibly synch) MaxUnflushedWriteTime integer Windows default: 5. All else -1 (i.e. don't forcibly synch) ProcessPriorityLevel integer default 0 - meaning depends on system. CreateInternalWindow boolean default true Windows only. Tells if the Firebird server should create a (hidden) window (used for IPC?) or not. CompleteBooleanEvaluation boolean default off
Controls whether full evaluation of OR and AND statements is performed. If your SQL code depends on side-effects of full evaluation of right-hand-side terms, even if the expressions final result could be determined by just examining the value of the first term, you might need to turn this on.
On Linux systems there is a new file named SYSDBA.password which now contains the SYSDBA password. The SYSDBA password is created by the installation script, so it no longer defaults to "masterkey".
You can then overwrite the password, using the bin/changeDBAPassword.sh shell script. For simplicity, I chose "masterkey" again ... ;-)
When you install a SuperServer, you will have to manually adapt the SYSDBA password in the /etc/rc.d/init.d/firebird file (/etc/init.d/firebird for SuSE Linux systems).
SYSDBA.password:
# Firebird generated password for user SYSDBA is:
ISC_USER=sysdba
ISC_PASSWD=masterkey
# for install on valentin at time Tue Mar 2 14:37:26 CET 2004
# You should change this password at the earliest oportunity
# Your password can be changed to a more suitable one using the
# /opt/firebird/bin/changeDBAPassword.sh script
# For superserver you will also want to check the password in the
# daemon init routine in the file /etc/rc.d/init.d/firebird
The security database has been renamed to security.fdb
Now, if FW=off (disabled), you can control how often dirty pages are flushed on disk. This allows you to use disabled FW mode with more reliablity than before (mostly it affects win32 platforms).
Implemented configurable access for databases, external tables and UDF libraries.
Allows native SQL storage of 64-bit exact numerics. Only available in Dialect 3.
Single-line comments (--) are now allowed in any position of the SQL statement
Allow user-defined ordering of NULLs. Default behaviour is NULLS LAST.
Syntax:
[ORDER BY <order_list>]
<order_list> = {col | int} [COLLATE collation]
[ASC[ENDING] | DESC[ENDING]] [NULLS {FIRST | LAST}]
[, <order_list> ...]
Example:
ORDER BY NAME DESCENDING NULLS FIRST
Allow either creating or altering a database object depending on its existance. Only applicable to Stored Procedures and Triggers. Same meaning as CREATE OR REPLACE in Oracle
Syntax:
CREATE OR ALTER name <object_definition>;
Allow execution of dynamic SQL statements in Stored Procedures and Triggers. Cannot return values, so no SELECT statements allowed. Recursion level limited to 50.
Syntax:
EXECUTE STATEMENT value;
Example:
my_var = "DELETE FROM USERS WHERE USER_ID = " || my_user_id;
EXECUTE STATEMENT my_var;
It's also possible to return values from the dynamic SQL.
Syntax:
EXECUTE STATEMENT <value> INTO <var_list>; (singleton form)
or
FOR EXECUTE STATEMENT <value> INTO <var_list> DO <stmt_list>;
Allow the result of a column to be determined by a the results of a case expression.
Syntax:
<case_specification> = <simple_case> | <searched_case>
<simple_case> = CASE value <simple_when_clause> ... [ELSE value] END
<simple_when_clause> = WHEN value THEN value
<searched_case> = CASE <searched_when_clause> ... [ELSE value] END
<searched_when_clause> = WHEN <search_condition> THEN value
Note: The function has the same meaning as DECODE in Oracle.
1. Simple case:
SELECT
o.ID,
o.Description,
CASE o.Status
WHEN 1 THEN 'confirmed'
WHEN 2 THEN 'in production'
WHEN 3 THEN 'ready'
WHEN 4 THEN 'shipped'
ELSE 'unknown status ''' || o.Status || ''''
END
FROM
Orders o
2. Searched case:
SELECT
o.ID,
o.Description,
CASE
WHEN (o.Status IS NULL) THEN 'new'
WHEN (o.Status = 1) THEN 'confirmed'
WHEN (o.Status = 3) THEN 'in production'
WHEN (o.Status = 4) THEN 'ready'
WHEN (o.Status = 5) THEN 'shipped'
ELSE 'unknown status ''' || o.Status || ''''
END
FROM
Orders o
Return a NULL value for a sub-expression if it has a specific value, otherwise return the value of the sub-expression.
Syntax:
NULLIF (value, value)
Note: NULLIF (V1, V2) is equivalent to the following case specification:
CASE WHEN V1 = V2 THEN NULL ELSE V1 END
Example:
UPDATE PRODUCTS
SET STOCK = NULLIF(STOCK, 0)
Allow a column value to be calculated by a number of expressions, the first expression returning a non NULL value is returned as the column value. The function has the same meaning as NVL in Oracle.
Syntax:
COALESCE (value {, value} ... )
Notes:
Example:
SELECT
PROJ_NAME AS Projectname,
COALESCE(e.FULL_NAME, '[> not assigned <]') AS Employeename
FROM
PROJECT p LEFT JOIN EMPLOYEE e ON (e.EMP_NO = p.TEAM_LEADER)
Allow one trigger to be fired for a number of action types (e.g. INSERT or UPDATE). Access to to both (OLD and NEW) contexts is allowed.
Syntax:
CREATE TRIGGER name FOR table
[ACTIVE | INACTIVE]
<trigger_action_prefix>
<trigger_action_suffix> [OR <trigger_action_suffix>] [OR <trigger_action_suffix>]
[POSITION number]
AS <trigger_body>
<trigger_action_prefix> = {BEFORE | AFTER}
<trigger_action_suffix> = {DELETE | INSERT | UPDATE}
Example:
CREATE TRIGGER my_trigger FOR my_table BEFORE INSERT OR UPDATE
AS BEGIN
IF (NEW.DOC_ID IS NULL) THEN
EXCEPTION my_exception;
END
Added runtime action checks (INSERTING/UPDATING/DELETING predicates)
Example:
if (INSERTING) then
new.OPER_TYPE = 'I';
else
new.OPER_TYPE = 'U';
Support for empty BEGIN / END blocks has been enabled
Allow to GROUP BY internal functions and subqueries. Also allow to GROUP BY ordinal (i.e. column position).
Disabled BREAK statement for triggers (like EXIT) due to known internal limitations.
Simplify syntax and allow declaring and defining variable at the same time.
Syntax:
DECLARE [VARIABLE] name <variable_type> [{'=' | DEFAULT} value];
Example:
DECLARE my_var INTEGER = 123;
Disallowed PLAN subclause.
A shorthand for DROP VIEW / CREATE VIEW couple of statements.
Syntax:
RECREATE VIEW name <view_definition>;
Allow an index name to be either constraint name or user-defined name.
Syntax:
<col_constraint> = [CONSTRAINT constraint]
{UNIQUE [<constraint_index>]
| PRIMARY KEY [<constraint_index>]
| REFERENCES other_table [( other_col [, other_col ...])]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[<constraint_index>]
| CHECK ( <search_condition>)}
<tconstraint> = [CONSTRAINT constraint]
{{PRIMARY KEY | UNIQUE} ( col [, col ...]) [<constraint_index>]
| FOREIGN KEY ( col [, col ...]) REFERENCES other_table
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[<constraint_index>]
| CHECK ( <search_condition>)}
<constraint_index> = USING [ASC[ENDING] | DESC[ENDING]] INDEX name
Note: Index is named the same way as a constraint by default.
Allow an already catched exception to be re-thrown from the WHEN-block. Makes sense in WHEN-block only, in other places evaluates to no-op.
Syntax:
EXCEPTION;
Allow to throw an exception with another message than this exception has been created with.
Syntax:
EXCEPTION name [value];
Added support for explicit locking.
Syntax:
SELECT <...> [FOR UPDATE [OF col [, col ...]] [WITH LOCK]]
Added support for explicit savepoint management. Not available in PSQL.
Syntax:
SAVEPOINT savepoint_name; (create savepoint)
ROLLBACK [WORK] TO [SAVEPOINT] savepoint_name; (rollback to previously created savepoint)
Implemented ORDER BY clause in subqueries. Disallowed FIRST/SKIP for views. Allowed zero as valid argument for FIRST. Make FIRST/SKIP keywords non-reserved.
Allowed arbitrary expressions in the ORDER BY clause.
DOS737, DOS775, DOS858, DOS862, DOS864, DOS866, DOS869
WIN1255, WIN1256, WIN1257
ISO8859_3, ISO8859_4, ISO8859_5, ISO8859_6, ISO8859_7, ISO8859_8, ISO8859_9, ISO8859_13
Collations for the above charsets are not available yet.
WIN1251_UA for both Ukrainian and Russian languages.
LPAD and RPAD
Allowed NULLs in unique constraints and indices (SQL-99 spec)
Features of GSTAT/GSEC are not available via Services API in win32 CS (until v1.6 release).
Old maximum number of indices was 64, new max value is (DB_PAGE_SIZE/16)-2.
When actual number of indices for a particular table exceeds 256 engine behavior may be unstable
CURRENT_CONNECTION and CURRENT_TRANSACTION: Return appropriate internal identifier stored on the database header page. The values are reset after a database is restored.
SQLCODE and GDSCODE: Provide an access to a code of the catched error within the WHEN-block. Available in PSQL only. Makes sense in WHEN-block only, in other places returns 0 (success).
ROW_COUNT: Returns number of rows affected by the last INSERT/UPDATE/DELETE statement. Available in PSQL only. For any other statement than INSERT/UPDATE/DELETE, result is always zero.
Command-line switches "-r" and "-t" are now supported via the Services API.
IB7-compliant functions to return version of the client library: isc_get_client_version(), isc_get_client_major_version(), isc_get_client_minor_version()
Enhanced isc_database_info abilities: List of currently active transactions is now available via isc_database_info call.
(This is a dump of improvements I have found in the WhatsNew changelog)
Firebird 1.5.1 was released 2004-07-14. You can find a complete list of changes in the Release Notes. Here is a compressed list:
Firebird 1.5.2 was released 2004-12-26. You can find a complete list of changes in the Release Notes
Firebird 1.5.3 was released 2006-01-24. You can find a complete list of changes in the Release Notes
The most important ones:
Firebird 1.5.4 was released in January 2007. No no features were introduced, it contains a number of bugfixes that were backported from the Firebird 2.0 development.
Firebird 1.5.5 was released 2007-12-12. No no features were introduced, it contains a number of bugfixes that were backported from the Firebird 2.0 development, especially a few security vulnerabilities that have been published recently.
Compiled by Stefan Heymann, 2004-03-03
2004-03-04: Added new SYSDBA Password handling on Linux systems
2004-07-21: Added links at top and chapter about Firebird 1.5.1
2005-01-10: Added chapter about Firebird 1.5.2
2006-12-06: Renamed ROWS_AFFECTED to ROW_COUNT
2007-12-17: Added Firebird 1.5.4 and 1.5.5 chapters
This documentation is licensed under (choose your favorite): GPL, LGPL, CC, IDPL, GFDL, BSD, (did I forget one?)