This multi-threaded mode for Classic has been dubbed “Superclassic” for its capability to handle multiple worker threads—dedicated or pooled—inside a single server process. It shares all the usual Classic features, with a few differences:

instsvc install -multithreaded
            

fb_inet_server -a -m
            

The Classic server now detects immediately when a Classic process has been broken by a client disconnection. Its response is to terminate any pending activity, roll back the active transaction and close the network connection.

Tracker reference CORE-818.

Important optimizations include:

Tracker reference CORE-1687

Previous 64-bit Firebird server versions could not benefit from 64-bit address space and be configured for more than 2 GB (16K * 128 K)of database cache. The problem has been rectified in this version. On 64-bit Firebird, if the resources are available, it is now possible to configure cache large enough to accommodate a database of 5-10 GB completely in RAM .

Although Firebird's caching can get a lot of help from the filesystem cache, it is a feature that could be important for high-throughput systems whose load is mainly reads. The theoretical upper limit for caches on x64 Firebird servers is now 2^31 -1 (2,147,483,647) pages.

Tracker reference CORE-1643

The configuration parameter DatabaseAccess now has more “meaning” attached to it. In the absence of any other indication, the first location defined in the “Restrict” list for DatabaseAccess is taken by the engine as the default location for creating a new database and for locating a database where the connection parameters do not specify either an alias or the full path specification.

The seek logic is similar to that use for finding external tables from the Restrict list supplied to the ExternalFileAccess parameter, viz.,

The root determination mechanism for the Windows embedded engine has been changed to avoid common problems that occur when an installation of the application structure encounters “DLL Hell”. Previously, the implicit root directory was the directory containing the user application's main executable file. Now it is the directory where the renamed fbembed.dll library is located.

Tracker reference CORE-1814.

Previous Firebird versions used 32-bit I/O when working with external tables, limiting the size of the external file to < 2 GB. The mechanism has been enhanced to use 64-bit I/O on filesystems that support it, effectively eliminating the 2 GB limit.

Tracker reference CORE-2492.

Tracker reference CORE-2619

Memory and other statistics did not work properly 64-bit values in older Firebird versions. The issue had two parts:

Tracker reference CORE-1937.

When a string UDF is written to return a pointer not allocated by the same runtime as the Firebird server is accessing, the presence of the FREE_IT keyword in its declaration corrupts memory and crashes the server. As a safeguard against such dysfunctional UDFs, the engine now

Firebird 2.5 creates databases with an ODS (On-Disk Structure) version of 11.2

The maximum page size remains 16 KB (16384 bytes).

The maximum number of pages that can be configured for the database cache depends on whether the database is running under 64-bit or 32-bit Firebird:

Tracker reference CORE-3446.

Conversion either way between BLOBs and other types are enabled in the API functions (XSQLVAR or blr messages).

Previous versions had no way to interoperate with the character set(s) used by the operating system and its filesystem. Firebird 2.5 has been made “environmentally aware” with regard to the file names of databases, other files and string parameters generally, when accessed through and/or passed in API connection requests. This change significantly improves Firebird's ability to accept and work with file names and other parameters containing characters that are not in the ASCII subset.

Tracker reference CORE-1761.

A new client-side API function, fb_sqlstate() is available to convert the status vector item for an error into the corresponding SQL-2003 standard 5-alphanumeric SQLSTATE.

Appendix A: SQLSTATE provides a list of all SQLSTATE codes in use in this release, along with the corresponding message texts.

Tracker reference CORE-1741.

The new option DSQL_unprepare (numeric value 4) for the API routine isc_dsql_free_statement() allows the DSQL statement handle to survive the “unpreparing” of the statement.

Previously, the isc_dsql_free_statement() function supported only DSQL_close (for closing a named cursor) and DSQL_drop (which frees the statement handle).

The API addition is:

#define DSQL_close 1
#define DSQL_drop 2
#define DSQL_unprepare 4
        

New fb_cancel_operation() API call, allowing cancellation of the current activity being performed by some kind of blocking API call in the given connection.

Syntax

   ISC_STATUS fb_cancel_operation(ISC_STATUS* status_vector,
                                  isc_db_handle* db_handle,
                                  ISC_USHORT option);
        

Parameters

Thread A:
fb_cancel_operation(isc_status, &DB, fb_cancel_enable);
isc_dsql_execute_immediate(isc_status, &DB, &TR, 0, "long running statement", 3, NULL);
// waits for API call to finish...

		Thread B:
		fb_cancel_operation(local_status, &DB, fb_cancel_raise);

Thread A:
if (isc_status[1])
	isc_print_status(isc_status); // will print "operation was cancelled"
          

This release exposes a variety of API functions for instigating server shutdowns of various types from client applications.

   typedef int (*FB_SHUTDOWN_CALLBACK)(const int reason, const int mask, void* arg);

   int fb_shutdown(unsigned int timeout,
                   const int reason);

   ISC_STATUS fb_shutdown_callback(ISC_STATUS* status_vector,
                                   FB_SHUTDOWN_CALLBACK callback_function,
                                   const int mask,
                                   void* arg);
          

#include <ibase.h>

// callback function for shutdown
static int ignoreCtrlC(const int reason, const int, void*)
{
	return reason == fb_shutrsn_signal ? 1 : 0;
}

int main(int argc, char *argv[])
{
        ISC_STATUS_ARRAY status;
        if (fb_shutdown_callback(status, ignoreCtrlC, fb_shut_confirmation, 0))
	{
		isc_print_status(status);
		return 1;
	}
	// your code continues ...
}
            

Several DPB parameters have been made inaccessible to ordinary users, closing some dangerous loopholes. In some cases, they are settings that would alter the database header settings and potentially cause corruptions if not performed under administrator control; in others, they initiate operations that are otherwise restricted to the SYSDBA. They are.-

The parameter isc_dpb_set_page_buffers can still be used by ordinary users on Classic and it will set the buffer size temporarily for that user and that session only. When used by the SYSDBA on either Superserver or Classic, it will change the buffer count in the database header, i.e., make a permanent change to the default buffer size.

Five new services relating to the management of the new user trace sessions have been added to the Services Manager, each with its corresponding Services API action function.

  isc_spb_trc_name : trace session name, string, optional
  isc_spb_trc_cfg  : trace session configuration, string, mandatory
          

  isc_spb_trc_id : trace session ID, integer, mandatory
          

  isc_spb_trc_id : trace session ID, integer, mandatory
          

  isc_spb_trc_id : trace session ID, integer, mandatory
          

Introduced in v.2.5.2 was the ability to run gbak at the server side reading from or writing to a gbak backup file located at a remote client. This is an especially efficient way to do backups and restores across an Internet connection.

The simplest way to use this feature is with the command-line tool fbsvcmgr which provides a quick-and-dirty interface for Services API calls without the need to write your own client application.

fbsvcmgr remotehost:service_mgr -user sysdba -password XXX \
  action_backup -dbname some.fdb -bkp_file stdout >some.fbk
          

fbsvcmgr remotehost:service_mgr -user sysdba -password XXX \
  action_restore -dbname some.fdb -bkp_file stdin <some.fbk
          

Database validation enables low-level checks of the consistency of on-disk structures and even to fix some minor corruptions. The recommended procedure for any valuable database is for the DBA to validate a database periodically to ensure it is healthy.

Exclusive access to the database is required: any kind of concurrent access is forbidden during validation. Sometimes, blocking user access could be a major hold-up, especially if the database is large and complex.

Online validation is a new feature that allows some consistency checks to be performed without exclusive access.

Once the locks are acquired, the table and its indexes are validated in the same way as a full validation does it. The locks are released when it completes and the whole procedure is repeated for the next table.

Action:
	isc_action_svc_validate

Parameters:
	isc_spb_dbname :
		database file name, string, mandatory

	isc_spb_val_tab_incl, isc_spb_val_tab_excl,
	isc_spb_val_idx_incl, isc_spb_val_idx_excl :
		patterns for tables\indices names, string, optional

	isc_spb_val_lock_timeout :
		lock timeout, integer, optional

Output:
	text messages with progress of online validation process
           

fbsvcmgr [host:]service_mgr [user <...>] [password <...>]
	action_validate dbname <filename>
	[val_tab_incl <pattern>]
	[val_tab_excl <pattern>]
	[val_idx_incl <pattern>]
	[val_idx_excl <pattern>]
	[val_lock_timeout <number>]
           

Other additions to the Services API include:

A new Trace API is under construction, providing a set of hooks which can be implemented as an external plug-in module to be called by the engine when any traced event happens. It is as yet undocumented, since it is subject to change in forthcoming sub-releases.

For a little more information about it, refer to the topic Trace Plug-in Facilities in the Administrative Features chapter.

New in Firebird 2.5, FileSystemCacheSize controls the maximum amount of RAM used by a Windows file system cache on 64-bit Windows XP or a Microsoft Server 2003 host with Service Pack 1 or higher.

At the V.2.5 initial release, it has no effect on POSIX host systems.

The setting for this parameter is an integer expressing the percentage of the total physical RAM that is available to the OS. To be valid, settings must be within the range 10 (per cent) to 95 (per cent), or explicitly set to 0 to enforce the host caching settings. Numbers outside that range will assume the default, which is 30 (per cent).

As with any firebird.conf setting, changes will not take effect until the server process is restarted.

This parameter was introduced in V.2.1 as MaxFileSystemCache. Because its name has been changed, its description is repeated here to alert upgraders.

FileSystemCacheThreshold sets a threshold determining whether Firebird will allow the page cache to be duplicated to the filesystem cache or not. If this parameter is set to any (integer) value greater than zero, its effect depends on the current default size of the page cache: if the default page cache (in pages) is less than the value of MaxFileSystemCache (in pages) then filesystem caching is enabled, otherwise it is disabled.

Thus,

MaxFileSystemCache, introduced in Firebird 2.1, is no longer a valid parameter.

It should be understood that acquiring the RDB$ADMIN role does not make a regular user into SYSDBA. Rather, it gives that user the same privileges as SYSDBA over the objects in the database in which the role is granted to that user.

On POSIX hosts, the root user always had SYSDBA privileges, but the same was not possible for a domain administrator on Windows until Firebird 2.1. In V.2.1, a configuration parameter, Authentication, was introduced, whereby a user logged in as a Windows domain administrator could automatically gain server access with SYSDBA privileges through trusted user authentication. On POSIX, the mechanism has not changed but on Windows, the introduction of the RDB$ADMIN role has changed the way Windows administrators acquire SYSDBA privileges.

ALTER ROLE RDB$ADMIN
  SET AUTO ADMIN MAPPING;
            

ALTER ROLE RDB$ADMIN
  DROP AUTO ADMIN MAPPING;
            

The new DDL command ALTER USER enables an “ordinary” user (a regular Firebird user, a non-root user on POSIX or a trusted user on a Windows system where trusted authentication is enabled) the ability to change his or her password and/or personal name elements, while logged in to any database. Superusers can also use the same command to create and drop users. For more information about this new command, refer to the topic CREATE/ALTER/DROP USER in the chapter Data Definition Language.

Because security2.fdb is created as (or should be upgraded to) an ODS 11.2 database, it has the pre-defined RDB$ADMIN role, too. Since no user—not even SYSDBA—can log into the security database, alternative means have been provided to enable the SYSDBA or a Superuser to apply the RDB$ADMIN role in security2.fdb to an ordinary user that needs the ability to create or drop users. There are three ways, each having the equivalent effect:

The new trace and audit facilities enable various events performed inside the engine, such as statement execution, connections, disconnections, etc., to be logged and collated for real-time analysis of the corresponding performance characteristics.

A trace takes place in the context of a trace session. Each trace session has its own configuration, state and output.

The Firebird engine has a fixed list of events it can trace. It can perform two different sort of traces: a system audit and a user trace. How the engine forms the list of events for a session depends on which sort of trace is requested.

  Trace session ID nnnn started
          

A system audit session is started by the engine itself. To determine which events the session is “interested in”, it reads the contents of a trace configuration file as it goes to create the session.

A new parameter in firebird.conf, AuditTraceConfigFile points to the name and location of the file. There can be at most one system audit trace in progress. By default, the value of this parameter is empty, indicating that no system audit tracing is configured.

A user trace session is managed by user, using some new calls to the Services API. There are five new service functions for this purpose:

The syntax for the Services API calls are discussed in the topic New Trace Functions for Applications in the chapter entitled Changes to the Firebird API and ODS.

Tracker reference CORE-2588

On Windows, the trace tools exhibit shared memory conflicts if multiple engine instances in different Windows sessions are allowed to run trace in global namespace. Tracing scope has therefore been restricted to only those processes that are accessible from the current Windows session.

There are three general use cases:

A new Trace API will provide a set of hooks which can be implemented as an external plug-in module to be called by the engine when any traced event happens. A plug-in will assume responsibility for logging such events in some custom way.

This Trace API exists in Firebird 2.5 and is in use. However, since it will be changed in forthcoming sub-releases, it is not officially published and must be regarded as unstable.

The implemented “standard” trace plug-in, fbtrace.dll (.so), resides in the \plugins folder of your Firebird 2.5 installation.

The original design allowed non-privileged database users to see monitoring information pertaining only to their CURRENT_CONNECTION. Now they can request information for any attachment that was authenticated using the same user name.

Tracker reference CORE-2233.

Examples

“Top 10” statements ranked according to their memory usage:

SELECT FIRST 10
  STMT.MON$ATTACHMENT_ID,
  STMT.MON$SQL_TEXT,
  MEM.MON$MEMORY_USED
FROM MON$MEMORY_USAGE MEM
  NATURAL JOIN MON$STATEMENTS STMT
  ORDER BY MEM.MON$MEMORY_USED DESC
        

To enumerate all session-level context variables for the current connection:

SELECT
  VAR.MON$VARIABLE_NAME,
  VAR.MON$VARIABLE_VALUE
FROM MON$CONTEXT_VARIABLES VAR
  WHERE VAR.MON$ATTACHMENT_ID = CURRENT_CONNECTION
        

DELETE FROM MON$STATEMENTS
  WHERE MON$ATTACHMENT_ID = 32
          

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

In V.2.1, members of administrative Windows groups were mapped to SYSDBA by default. From V.2.5 forward, automatic SYSDBA mapping is controlled on per-database basis using the new SQL command

ALTER ROLE RDB$ADMIN SET/DROP AUTO ADMIN MAPPING

Tracker reference CORE-2052.

One such change addressed the problem of the visibility of altered stored procedures to other connections to the Classic server. Now, such changes are made visible to the entire server as soon as the modifying transaction has completed its commit.

Tracker reference CORE-696.

In v.2.5, Firebird finally has syntax to enable user accounts on the server to be managed by submitting SQL statements when logged in to a regular database.

The CREATE USER and ALTER USER statements also include the parameters GRANT ADMIN ROLE and REVOKE ADMIN ROLE to enable a user with SYSDBA privileges user to grant the RDB$ADMIN role in the security database to an ordinary user. For the usage description and a full overview of the RDB$ADMIN role, refer to the topic New RDB$ADMIN System Role in the Administrative Features chapter.

Syntax Patterns

The SYSDBA, or a user with SYSDBA privileges in both the current database and the security database, can add a new user:

CREATE USER <username> {PASSWORD 'password'}
  [FIRSTNAME 'firstname']
  [MIDDLENAME 'middlename']
  [LASTNAME 'lastname']
  [GRANT ADMIN ROLE];
      

The SYSDBA, or a user with SYSDBA privileges in both the current database and the security database, can change one or more of the password and proper name attributes of an existing user. Non-privileged users can use this statement to alter only their own attributes.

ALTER USER <username>
  [PASSWORD 'password']
  [FIRSTNAME 'firstname']
  [MIDDLENAME 'middlename']
  [LASTNAME 'lastname']
  [{GRANT | REVOKE} ADMIN ROLE];
      

The SYSDBA, or a user with SYSDBA privileges in both the current database and the security database, can delete a user:

DROP USER <username>;
      

Restrictions

CREATE USER and DROP USER statements and GRANT | REVOKE ADMIN ROLE are available only for the SYSDBA, or a user that has acquired the RDB$ADMIN role in both the current database and the security database.

An ordinary user can ALTER his own password and elements of his proper name. An attempt to modify another user will fail.

Examples

SYSDBA or a user with equivalent privileges in both the current database and the security database, can do:

CREATE USER alex PASSWORD 'test';

ALTER USER alex FIRSTNAME 'Alex' LASTNAME 'Peshkov';

ALTER USER alex PASSWORD 'IdQfA';
      

Previously, in order to alter a view definition, it was necessary to save the view definition off-line somewhere and drop the view, before recreating it with its changes. This made things very cumbersome, especially if there were dependencies. V.2.5 introduces syntaxes for ALTER VIEW and CREATE OR ALTER VIEW.

Tracker references are CORE-770 and CORE-1640.

Syntax Pattern

{create [ or alter ] | alter } view <view_name>
  [ ( <field list> ) ]
as <select statement>
      

Example

create table users (
    id integer,
    name varchar(20),
    passwd varchar(20)
);

create view v_users as
    select name from users;

alter view v_users (id, name) as
    select id, name from users;
      

The following extensions have been added for CREATE VIEW.

    create view a_view as
    select * from a_procedure(current_date);
        

 recreate view V1 as
   select d.rdb$relation_id from rdb$database d
   union all
   select d.rdb$relation_id from rdb$database d

 recreate view V2 as
   select d.rdb$relation_id as q from rdb$database d
   union all
   select d.rdb$relation_id as w from rdb$database d
        

 create view V as 
   select d.rdb$relation_id from rdb$database d 
     group by d.rdb$relation_id

 create view V as 
    select a from (select 1 a from rdb$database);
        

Tracker reference CORE-1454.

A column defined as COMPUTED BY <expression> can now be altered using the ALTER TABLE...ALTER COLUMN syntax. This feature can be used only to change the <expression> element of the column definition to a different expression. It cannot convert a computed column to non-computed or vice versa.

Syntax Pattern

alter table <table-name>
  alter <computed-column-name>
  [type <data-type>]
  COMPUTED BY (<expression>);
      

Examples

create table test (
  n integer,
  dn computed by (n * 2)
);
commit;
alter table test
  alter dn computed by (n + n);
      

The following extensions have been implemented in the area of SQL permissions (privileges).

grant <right> to <object>
  [ { granted by | as } [ user ] <username> ]
--
revoke <right> from <object>
  [ { granted by | as } [ user ] <username> ]
        

create role r1; -- SYSDBA owns the role
  /* next, SYSDBA grants the role to user1
     with the power to grant it to others */
grant r1 to user1 with admin option;
  /* SYSDBA uses GRANTED BY to exercise
     user1's ADMIN OPTION */
grant r1 to public granted by user1;
      

REVOKE ALL ON ALL FROM { <user list> | <role list> }
        

# gsec -del guest
# isql employee
fbs bin # ./isql employee
Database:  employee
SQL> REVOKE ALL ON ALL FROM USER guest;
SQL>
        

Tracker references CORE-1737 and CORE-1803.

An ODS 11.2 or higher database can now have a default COLLATION attribute associated with the default character set, enabling all text column, domain and variable definitions to be created with the same collation order unless a COLLATE clause for a different collation is specified.

The COLLATION clause is optional. If it is omitted, the default collation order for the character set is used.

Syntax Pattern

create database <file name>
    [ page_size <page size> ]
    [ length = <length> ]
    [ user <user name> ]
    [ password <user password> ]
    [ set names <charset name> ]
    [ default character set <charset name>
       [ [ collation <collation name> ] ]
    [ difference file <file name> ]
      

Example

create database 'test.fdb'
    default character set win1252
    collation win_ptbr;
      

Tracker reference CORE-1803.

New syntax introduced in this version, enabling the default collation for a character set to be set for a database.

The default collation is used when table columns are created with a given character set (explicitly, through a CHARACTER SET clause in the column or domain definition, or implicitly, through the default character set attribute of the database) and no COLLATION clause is specified.

Syntax Pattern

ALTER CHARACTER SET <charset_name>
    SET DEFAULT COLLATION <collation_name>
      

Example

create database 'people.fdb'
  default character set win1252;

alter character set win1252
  set default collation win_ptbr;

create table person (
  id integer,
  name varchar(50)  /* will use the database default
                       character set and the win1252
                       default collation */
);

insert into person
  values (1, 'adriano');
insert into person
  values (2, 'ADRIANO');

/* will retrieve both records
   because win_ptbr is case insensitive */
select * from person where name like 'A%';
      

DDL support for the database header attributes introduced to register and change the nBackup states has been evolving since Firebird 2.0. Users of nBackup will be familiar with the ALTER DATABASE statements to begin and end the storage of the “delta” data, in a file apart from the main database, during a full nBackup.

alter database
  add difference file 'path-and-filename'
        

alter database
  drop difference file
        

  ]..\bin> isql -user sysdba -pass masterke

 Use CONNECT or CREATE DATABASE to specify a database
 SQL> create database 'ticks' difference file 'jaguar';
 SQL> shell dir jaguar;
 Volume in drive F is Firebird
 Volume Serial Number is BCD9-4211

 Directory of ..\bin

 File Not Found
        

 SQL> alter database begin backup;
 SQL> shell dir jaguar;
 Volume in drive F is Firebird
 Volume Serial Number is BCD9-4211

 Directory of ..\bin

 10-11-2009  00:59             8.192 jaguar
                1 File(s)          8.192 bytes
                0 Dir(s)  16.617.979.904 bytes free

 SQL> alter database end backup;
 SQL> shell dir jaguar;
 Volume in drive F is Firebird
 Volume Serial Number is BCD9-4211

 Directory of ..\bin

 SQL> drop database;
 SQL> ˆZ
        

 ]..\bin> isql -user sysdba -pass masterke

 Use CONNECT or CREATE DATABASE to specify a database
 SQL> create database 'ticks' DIFFERENCE FILE 'jaguar';
 SQL> alter database add difference file 'leopard';

 Statement failed, SQLCODE = -607
 unsuccessful metadata update
 -Difference file is already defined
        

 SQL> alter database drop difference file;
 SQL> alter database begin backup;
         

 SQL> alter database add difference file 'leopard';
 SQL> alter database begin backup;
 SQL> alter database drop difference file;

 Statement failed, SQLCODE = -607
 unsuccessful metadata update
 -Cannot change difference file name while database is in backup mode
        

 SQL> alter database end backup;
 SQL> drop database;
 SQL> ˆZ
        

Tracker reference CORE-769.

A new SIMILAR TO predicate is introduced to support regular expressions. The predicate's function is to verify whether a given SQL-standard regular expression matches a string argument. It is valid in any context that accepts Boolean expressions, such as WHERE clauses, CHECK constraints and PSQL IF() tests.

Syntax Patterns

<similar predicate> ::=
	<value> [ NOT ] SIMILAR TO <similar pattern> [ ESCAPE <escape character> ]

<similar pattern> ::= <character value expression: regular expression>

<regular expression> ::=
      <regular term>
    | <regular expression> <vertical bar> <regular term>

<regular term> ::=
      <regular factor>
    | <regular term> <regular factor>

<regular factor> ::=
      <regular primary>
    | <regular primary> <asterisk>
    | <regular primary> <plus sign>
    | <regular primary> <question mark>
    | <regular primary> <repeat factor>

<repeat factor> ::=
    <left brace> <low value> [ <upper limit> ] <right brace>

<upper limit> ::= <comma> [ <high value> ]

<low value> ::= <unsigned integer>

<high value> ::= <unsigned integer>

<regular primary> ::=
      <character specifier>
    | <percent>
    | <regular character set>
    | <left paren> <regular expression> <right paren>

<character specifier> ::=
      <non-escaped character>
    | <escaped character>

<regular character set> ::=
      <underscore>
    | <left bracket> <character enumeration>... <right bracket>
    | <left bracket> <circumflex> <character enumeration>... <right bracket>
    | <left bracket> <character enumeration include>... <circumflex> <character enumeration exclude>... 
        <right bracket>

<character enumeration include> ::= <character enumeration>

<character enumeration exclude> ::= <character enumeration>

<character enumeration> ::=
      <character specifier>
    | <character specifier> <minus sign> <character specifier>
    | <left bracket> <colon> <character class identifier> <colon> <right bracket>

<character specifier> ::=
      <non-escaped character>
    | <escaped character>

<character class identifier> ::=
      ALPHA
    | UPPER
    | LOWER
    | DIGIT
    | SPACE
    | WHITESPACE
    | ALNUM
      

Usage Guide

Return true for a string that matches <regular expression> or <regular term>:

      <regular expression> <vertical bar> <regular term>
       

    'ab' SIMILAR TO 'ab|cd|efg'   -- true
    'efg' SIMILAR TO 'ab|cd|efg'  -- true
    'a' SIMILAR TO 'ab|cd|efg'    -- false
       

Match zero or more occurrences of <regular primary>: <regular primary> <asterisk>

    '' SIMILAR TO 'a*'     -- true
    'a' SIMILAR TO 'a*'    -- true
    'aaa' SIMILAR TO 'a*'  -- true
       

Match one or more occurrences of <regular primary>: <regular primary> <plus sign>

    '' SIMILAR TO 'a+'     -- false
    'a' SIMILAR TO 'a+'    -- true
    'aaa' SIMILAR TO 'a+'  -- true
       

Match zero or one occurrence of <regular primary>: <regular primary> <question mark>

    '' SIMILAR TO 'a?'     -- true
    'a' SIMILAR TO 'a?'    -- true
    'aaa' SIMILAR TO 'a?'  -- false
       

Match exact <low value> occurrences of <regular primary>: <regular primary> <left brace> <low value> <right brace>

    '' SIMILAR TO 'a{2}'     -- false
    'a' SIMILAR TO 'a{2}'    -- false
    'aa' SIMILAR TO 'a{2}'   -- true
    'aaa' SIMILAR TO 'a{2}'  -- false
       

Match <low value> or more occurrences of <regular primary>: <regular primary> <left brace> <low value> <comma> <right brace>:

    '' SIMILAR TO 'a{2,}'     -- false
    'a' SIMILAR TO 'a{2,}'    -- false
    'aa' SIMILAR TO 'a{2,}'   -- true
    'aaa' SIMILAR TO 'a{2,}'  -- true
       

Match <low value> to <high value> occurrences of <regular primary> <regular primary> <left brace> <low value> <comma> <high value> <right brace>:

    '' SIMILAR TO 'a{2,4}'       -- false
    'a' SIMILAR TO 'a{2,4}'      -- false
    'aa' SIMILAR TO 'a{2,4}'     -- true
    'aaa' SIMILAR TO 'a{2,4}'    -- true
    'aaaa' SIMILAR TO 'a{2,4}'   -- true
    'aaaaa' SIMILAR TO 'a{2,4}'  -- false
       

Match any (non-empty) character: <underscore>

    '' SIMILAR TO '_'    -- false
    'a' SIMILAR TO '_'   -- true
    '1' SIMILAR TO '_'   -- true
    'a1' SIMILAR TO '_'  -- false
       

Match a string of any length (including empty strings): <percent>

    '' SIMILAR TO '%'         -- true
    'az' SIMILAR TO 'a%z'     -- true
    'a123z' SIMILAR TO 'a%z'  -- true
    'azx' SIMILAR TO 'a%z'    -- false
       

Group a complete <regular expression> to use as one single <regular primary> as a sub-expression: <left paren> <regular expression> <right paren>

    'ab' SIMILAR TO '(ab){2}'    -- false
    'aabb' SIMILAR TO '(ab){2}'  -- false
    'abab' SIMILAR TO '(ab){2}'  -- true
       

Match a character identical to one of <character enumeration>: <left bracket> <character enumeration>... <right bracket>

    'b' SIMILAR TO '[abc]'    -- true
    'd' SIMILAR TO '[abc]'    -- false
    '9' SIMILAR TO '[0-9]'    -- true
    '9' SIMILAR TO '[0-8]'    -- false
       

Match a character not identical to one of <character enumeration>: <left bracket> <circumflex> <character enumeration>... <right bracket>

    'b' SIMILAR TO '[^abc]'    -- false
    'd' SIMILAR TO '[^abc]'    -- true
       

Match a character identical to one of <character enumeration include> but not identical to one of <character enumeration exclude>: <left bracket> <character enumeration include>... <circumflex> <character enumeration exclude>...

    '3' SIMILAR TO '[[:DIGIT:]^3]'    -- false
    '4' SIMILAR TO '[[:DIGIT:]^3]'    -- true
       

Match a character identical to one character included in <character class identifier>. Refer to the table of Character Class Identifiers, above. May be used with <circumflex> to invert the logic (see above): <left bracket> <colon> <character class identifier> <colon> <right bracket>

    '4' SIMILAR TO '[[:DIGIT:]]'  -- true
    'a' SIMILAR TO '[[:DIGIT:]]'  -- false
    '4' SIMILAR TO '[^[:DIGIT:]]' -- false
    'a' SIMILAR TO '[^[:DIGIT:]]' -- true
       

Examples

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 '\';
      

Tracker reference CORE-1760.

Support for hexadecimal numeric and binary string literals has been introduced.

Syntax Patterns

<numeric hex literal> ::=
    { 0x | 0X } <hexit> [ <hexit>... ]

<binary string literal> ::=
    { x | X } <quote> [ { <hexit> <hexit> }... ] <quote>

<digit> ::=
    0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

<hexit> ::=
    <digit> | A | B | C | D | E | F | a | b | c | d | e | f
      

Examples

select 0x10, cast('0x0F0000000' as bigint)
  from rdb$database;
select x'deadbeef'
  from rdb$database;
      

Prior to Firebird 2.5.2, the built-in function GEN_UUID() was returning completely random strings, making it non-compliant with the RFC-4122 (UUID specification). From Firebird 2.5.2 forward, GEN_UUID() returns a compliant UUID version 4 string, where some bits are reserved and the others are random.

See Tracker item CORE-3238.

CHAR_TO_UUID( <string> )
        

select char_to_uuid('93519227-8D50-4E47-81AA-8F6678C096A1')
  from rdb$database;
        

UUID_TO_CHAR( <string> )
        

select uuid_to_char(gen_uuid())
  from rdb$database;
        

Tracker reference CORE-2298

By popular request, particularly from Delphi programmers, support has been implemented for a predication that is able to “OR” test both the equivalence between a column and a parameter and whether the value passed to the parameter is NULL. This construct is often desired as a way to avoid the need to prepare one query to request a filtered result set and another for the same query without the filter.

Users of Delphi and other programming interfaces that apply client-side object names to parameters wanted the ability for the DSQL engine to recognise a usage like the following:

  WHERE col1 = :param1 OR :param1 IS NULL
      

At the API level, the language interface translates the query to

  WHERE col1 = ? OR ? IS NULL
      

That presented two problems:

What was needed to solve this problem was to introduce a new data type to handle the “? is NULL” condition and teach Firebird to do the right thing when it received such a request.

The implementation works like this. To resolve the first problem, the request must supply two parameters (for our Delphi example):

  WHERE col1 = :param1 OR :param2 IS NULL
      

In other words, for the parameter ( ? ) in ? IS NULL

Tracker reference CORE-1453

A string expression is now allowed as the delimiter argument of the LIST() function.

Example

SELECT
  DISCUSSION_ID,
  LIST(COMMMENT, ASCII_CHAR(13))
FROM COMMENTS
  GROUP BY DISCUSSION_ID;
      

The WEEK unit was introduced for functions DATEADD and DATEDIFF.

MILLISECOND, SECOND, MINUTE and HOUR units are no longer invalid units to use with DATE arguments.

Completing the set of built-in binary functions added in v.2.1, new function BIN_NOT() returns the result of a bitwise NOT operation performed on its argument.

Syntax Pattern

  BIN_NOT( <number> )
      

Example

select bin_not(flags) from x;
      

(V.2.5.1) Write operations to global temporary tables in a read-only database are enabled.

Tracker reference CORE-3399.

Changes in optimizer logic that address recognised problems include:

Other changes to smooth out the little annoyances include:

If there is no ON EXTERNAL DATA SOURCE clause present, EXECUTE STATEMENT is normally executed within the CURRENT_CONNECTION context. This will be the case if the AS USER clause is omitted, or it is present with its <user_name> argument equal to CURRENT_USER.

However, if <user_name> is not equal to CURRENT_USER, then the statement is executed in a separate connection, established without Y-Valve and remote layers, inside the same engine instance.

Tracker reference CORE-1853.

EXECUTE STATEMENT now supports queries against external databases by inclusion of the ON EXTERNAL DATA SOURCE clause with its <connection_string> argument.

   [<host_name><protocol_delimiter>]database_path
        

Tracker reference CORE-1221.

The new extensions provide the ability to prepare a statement with dynamic input parameters (placeholders) in a manner similar to a parameterised DSQL statement. The actual text of the query itself can also be passed as a parameter.

    <input_parameters> ::=
      <named_parameter> | <input_parameters>, <named_parameter>

    <named_parameter> ::=
      <parameter name> := <expression>
          

EXECUTE BLOCK AS
  DECLARE S VARCHAR(255);
  DECLARE N INT = 100000;
  BEGIN
  /* Normal PSQL string assignment of <query_text> */
    S = 'INSERT INTO TTT VALUES (:a, :b, :a)';

    WHILE (N > 0) DO
    BEGIN
    /* Each loop execution applies both the string value
       and the values to be bound to the input parameters */

      EXECUTE STATEMENT (:S) (a := CURRENT_TRANSACTION, b := CURRENT_CONNECTION)
      WITH COMMON TRANSACTION;
      N = N - 1;
    END
  END
          

EXECUTE BLOCK AS
  DECLARE S VARCHAR(255);
  DECLARE N INT = 100000;
  BEGIN
    S = 'INSERT INTO TTT VALUES (?, ?, ?)';

    WHILE (N > 0) DO
    BEGIN
      EXECUTE STATEMENT (:S) (CURRENT_TRANSACTION, CURRENT_CONNECTION, CURRENT_TRANSACTION);
      N = N - 1;
    END
  END
          

    EXECUTE STATEMENT (:sql) (p1 := 'abc', p2 := :second_param) ...
            

The handling of exceptions depends on whether the ON EXTERNAL DATA SOURCE is present.

  WHEN GDSCODE eds_statement
        

  WHEN GDSCODE lock_conflict
        

The following examples offer a sampler of ways that the EXECUTE STATEMENT extensions might be applied in your applications.

EXECUTE BLOCK
  RETURNS (CONN INT, TRAN INT, DB VARCHAR(255))
AS
  DECLARE I INT = 0;
  DECLARE N INT = 3;
  DECLARE S VARCHAR(255);
BEGIN
  SELECT A.MON$ATTACHMENT_NAME FROM MON$ATTACHMENTS A
   WHERE A.MON$ATTACHMENT_ID = CURRENT_CONNECTION
    INTO :S;

  WHILE (i < N) DO
  BEGIN
    DB = TRIM(CASE i - 3 * (I / 3)
      WHEN 0 THEN '\\.\' WHEN 1 THEN 'localhost:' ELSE '' END) || :S;

    FOR EXECUTE STATEMENT
      'SELECT CURRENT_CONNECTION, CURRENT_TRANSACTION
      FROM RDB$DATABASE'
      ON EXTERNAL :DB
      AS USER CURRENT_USER PASSWORD 'masterkey' -- just for example
      WITH COMMON TRANSACTION
      INTO :CONN, :TRAN
    DO SUSPEND;

    i = i + 1;
  END
END
          

EXECUTE BLOCK
  RETURNS (CONN INT, TRAN INT, DB VARCHAR(255))
AS
  DECLARE I INT = 0;
  DECLARE N INT = 3;
  DECLARE S VARCHAR(255);
BEGIN
  SELECT A.MON$ATTACHMENT_NAME
    FROM MON$ATTACHMENTS A
  WHERE A.MON$ATTACHMENT_ID = CURRENT_CONNECTION
   INTO :S;

  WHILE (i < N) DO
  BEGIN
    DB = TRIM(CASE i - 3 * (I / 3)
      WHEN 0 THEN '\\.\'
      WHEN 1 THEN 'localhost:'
      ELSE '' END) || :S;

    FOR EXECUTE STATEMENT
    'SELECT CURRENT_CONNECTION, CURRENT_TRANSACTION FROM RDB$DATABASE'
      ON EXTERNAL :DB
      WITH AUTONOMOUS TRANSACTION -- note autonomous transaction
      INTO :CONN, :TRAN
    DO SUSPEND;

    i = i + 1;
  END
END
          

EXECUTE BLOCK
  RETURNS (A INT, B INT, C INT)
AS
BEGIN
  EXECUTE STATEMENT (
    'SELECT CAST(:X AS INT),
            CAST(:X AS INT),
            CAST(:X AS INT)
       FROM RDB$DATABASE')
      (x := GEN_ID(G, 1))
    INTO :A, :B, :C;

  SUSPEND;
END
        

RECREATE TABLE TTT (
  TRAN INT,
  CONN INT,
  ID INT);

-- Direct inserts:

EXECUTE BLOCK AS
  DECLARE N INT = 100000;
BEGIN
  WHILE (N > 0) DO
  BEGIN
    INSERT INTO TTT VALUES (CURRENT_TRANSACTION, CURRENT_CONNECTION, CURRENT_TRANSACTION);
    N = N - 1;
  END
END

-- Inserts via prepared dynamic statement
-- using named input parameters:

EXECUTE BLOCK AS
  DECLARE S VARCHAR(255);
  DECLARE N INT = 100000;
BEGIN
  S = 'INSERT INTO TTT VALUES (:a, :b, :a)';

  WHILE (N > 0) DO
  BEGIN
    EXECUTE STATEMENT (:S)
      (a := CURRENT_TRANSACTION, b := CURRENT_CONNECTION)
    WITH COMMON TRANSACTION;
    N = N - 1;
  END
END

-- Inserts via prepared dynamic statement
-- using unnamed input parameters:

EXECUTE BLOCK AS
DECLARE S VARCHAR(255);
DECLARE N INT = 100000;
BEGIN
  S = 'INSERT INTO TTT VALUES (?, ?, ?)';

  WHILE (N > 0) DO
  BEGIN
    EXECUTE STATEMENT (:S) (CURRENT_TRANSACTION, CURRENT_CONNECTION, CURRENT_TRANSACTION);
    N = N - 1;
  END
END
        

Tracker reference CORE-2580

Previously, a subquery used as a PSQL expression would return an exception, even though it was logically valid in SQL terms. For example, the following constructions would all return errors:

var = (select ... from ...);
if ((select ... from ...) = 1) then
if (1 = any (select ... from ...)) then
if (1 in (select ... from ...)) then
      

Now, such potentially valid expressions are allowed, removing the need to jump through hoops to fetch the output of a scalar subquery into an intermediate variable using SELECT...INTO.

Tracker reference CORE-2890

(v.2.5.1) SQLSTATE is made available as a PSQL context variable, to be used with WHEN in an exception block, like GDSCODE and SQLCODE.

The usage of introducer syntax, i.e., prefixing an underscore to a character set name, to force the succeeding text literal to transliterated to that character set, has caused some problems in situations where a single SQL statement entails usage of more than one character set. The actual problems differ from version to version, showing up as transliteration errors, malformed string errors or just as some kind of unexpected behaviour.

Problems could occur in two different usage scenarios.-

To enable a workaround for such problems, it is now possible to transform the literal string into the hex representation of the ASCII characters being submitted by the introducer. For example:

    select _dos850 '123áé456' from rdb$database
      

may be transformed to

    select _dos850 X'313233A082343536' from rdb$database
      

Tracker reference CORE-1600.

Malformed characters are no longer allowed in data for UNICODE_FSS columns.

New restore switches were added to the gbak utility code for the purpose of repairing malformed UNICODE_FSS data and metadata by restoring a backup of the affected database. Details are in the gbak section of the Utilities chapter.

Tracker reference: CORE-1945)

For UNICODE collations only, a custom attribute NUMERIC-SORT has been enabled for specifying the order by which to sort numerals.

Format & Usage

The default, 0, sorts numerals in alphabetical order. For example:

    1
    10
    100
    2
    20
      

1 sorts numerals in numerical order. For example:

    1
    2
    10
    20
    100
      

Example

create collation unicode_num for utf8
from unicode 'NUMERIC-SORT=1';
      

   -STA[RT]     Start trace session
   -STO[P]      Stop trace session
   -SU[SPEND]   Suspend trace session
   -R[ESUME]    Resume trace session
   -L[IST]      List existing trace sessions
      

Action parameters

 -N[AME]     <string>  Session name
 -I[D]       <number>  Session ID
 -C[ONFIG]   <string>  Trace configuration file name
      

Connection parameters

 -SE[RVICE]  <string>  Service name
 -U[SER]     <string>  User name
 -P[ASSWORD] <string>  Password
 -FE[TCH]    <string>  Fetch password from file
 -T[RUSTED]  <string>  Force trusted authentication
      

 fbtracemgr -SE remote_host:service_mgr -USER SYSDBA -PASS masterkey -LIST
 fbtracemgr -SE service_mgr -START -NAME my_trace -CONFIG my_cfg.txt
 fbtracemgr -SE service_mgr -SUSPEND -ID 2
 fbtracemgr -SE service_mgr -RESUME -ID 2
 fbtracemgr -SE service_mgr -STOP -ID 4
      

The diagnostics of internal trace errors were improved. (Tracker reference CORE-3413).

Output is now flushed periodically. (Tracker reference CORE-3324).

Firebird 2.5 introduces the new switch -fet[ch_password] as an optional replacement for -pa[ssword] for all command-line utilities that take a password for authentication purposes. The switch may be progressively abbreviated from the right, conforming to the established rules.

  isql -user sysdba -fet passfile server:employee
        

  isql -user sysdba -fet stdin server:employee
        

  Enter password:
        

    -fetch /dev/tty
          

  bunzip2 -c emp.fbk.bz2 | gbak -c stdin /db/new.fdb
     -user sysdba -fetch /dev/tty
          

Since v.2.1, Windows domain administrators have had full access to the user management functions. In v.2.5 they do not get these privileges automatically unless the SYSDBA has configured the security database to make it happen automatically.

In the Administrative Features chapter is a detailed overview of the new system role RDB$ADMIN. There, you will find descriptions of the new ALTER ROLE syntax that can be used by the SYSDBA to enable or disable the automatic mapping of Windows administrators to the RDB$ADMIN role in databases, including the security database which they access when creating, altering and dropping users.

This automatic mapping can also be done in a gsec command-line call, using the new -mapping switch.

  -mapping {set | drop}
        

  -admin {YES | NO}
        

Tracker reference: CORE-756)

Parameter help has been implemented for gsec, accessible by using the -help or -? switches.

Tracker reference CORE-1831.

The gbak utility has two new restore switches intended to repair malformed UNICODE_FSS character data and metadata, respectively, when restoring the backup of an affected database.

Switch Syntax

  -FIX_FSS_D(ATA) <charset> -- fix malformed UNICODE_FSS data
  -FIX_FSS_M(ETADATA) <charset> -- fix malformed UNICODE_FSS metadata
      

An improvement allows the current value of RDB$DEFAULT_COLLATE_NAME in the system table RDB$CHARACTER_SETS to survive the backup/restore cycle.

Tracker reference CORE-789.

(v.2.5.1) Improved performance in the records insertion phase of restore.

Tracker reference CORE-3433.

Four switches were added to NBackup:

  -FE <filename>   Fetch password from file
  -Z               Print version information
  -?               Help
  -D ON|OFF        Force direct I/O on or off
  
      

An improvement has been done for POSIX versions to address a problem whereby the full backup tool of the nBackup incremental backup utility would hog I/O resources when backing up large databases, bringing production work to a standstill. Now, nBackup tries to read from the operating system cache before attempting to read from disk, thus reducing the I/O load substantially.

Tracker reference CORE-2316.

isql now returns the SQLSTATE completion code in diagnostics, instead of the now deprecated SQLCODE. For more information, see the topic Support for SQLSTATE Completion Codes in the chapter Changes to the Firebird API and ODS.

Tracker reference CORE-1171.

isql has always output different formatting of numbers on Windows and POSIX for two-digit exponents. The default behaviour of Microsoft and Intel compilers is to zero-pad the exponent to three digits regardless. For example,

  select cast ('-2.488355210669293e+39' as double precision)
    from rdb$database;
      

The isql output has been modified so that the Windows output now conforms with that on other platforms.

Help for SHOW COLLATIONS has been added to the command-line help for isql. (Tracker reference CORE-2432, A. dos Santos Fernandes).

Added mainly to assist testers, this command enables a limit to be set on the number of rows a query returns to the interactive isql shell.

Example of Use

The following isql statement will stop returning rows after the 10,000th row has been output:

  SQL>set rowcount 10000;
      

Tracker reference CORE-1527.

GPRE now supports the IS NOT DISTINCT predicate and CASE/NULLIF/COALESCE/SUBSTRING functions, as well as the whole set of CURRENT_* context variables.

A lot of the standard configure switches for fine-tuning the installation directories on POSIX platforms do not work for Firebird.

It was close to impossible to make the standard GNU switches work without changing the defaults for them, a rigmarole that is far from obvious or easy. Instead, a set of new switches for the configure has been added to enable fine-level configuration of the locations of Firebird's files.

The ib_util loader was also improved to make the engine work correctly with the configured layout.

  --with-fbbin executables DIR (PREFIX/bin)
  --with-fbsbin system admin executables DIR (PREFIX/bin)
  --with-fbconf config files DIR (PREFIX)
  --with-fblib object code libraries DIR (PREFIX/lib)
  --with-fbinclude C/C++ header files DIR (PREFIXinclude)
  --with-fbdoc documentation root DIR (PREFIX/doc)
  --with-fbudf UDF DIR (PREFIX/UDF)
  --with-fbsample examples DIR (PREFIX/examples)
  --with-fbsample-db examples database DIR (PREFIX/examples/empbuild)
  --with-fbhelp QLI help DIR (PREFIX/help)
  --with-fbintl international DIR (PREFIX/intl)
  --with-fbmisc misc DIR (PREFIX/misc)
  --with-fbsecure-db security database DIR (PREFIX)
  --with-fbmsg message files DIR (PREFIX)
  --with-fblog log files DIR (PREFIX)
  --with-fbglock guardian lock DIR (PREFIX)
  --with-fbplugins plugins DIR (PREFIX)
          

Tracker reference: CORE-2398)

A feature has been implemented for POSIX builds, to have Firebird correctly detect the path where its binaries are installed.

Tracker entry: CORE-1814

In this release it is possible to be a little more flexible about the location of your embedded application components than previously. Changes made here addressed a handful of interrelated issues that had been problemmatical for application developers, viz.

Determination of the <root> for the embedded v.2.5 engine has been changed so that it no longer has to be the application's path. It is by the location of fbembed.dll, wherever it might be located. This means that, with the v.2.5 engine, you can set up a standard, self-contained structure for the emulated Firebird <root> that is available to any of your embedded applications, to third-party UDF libraries and to local copies of the command-line tools, once they have loaded fbembed.dll.

If you separate the emulated Firebird tree from your application[s], be sure to set the RootDirectory parameter in the structure's firebird.conf file to point to the absolute path location that is its <root>.

Tracker entry: CORE-2243

Firebird 2.5 is built by the Microsoft MSVC8 compiler in Visual Studio 2005. Because all the Firebird binaries are built to use dynamic linking, they all require run-time libraries.

To avoid the dll-hell issue Microsoft introduced new rules for the distribution of components that may be shared by multiple applications. From Windows XP forward, shared libraries—such as the Visual C++ and Visual C runtimes msvcp80.dll, msvcr80.dll and mscvcm80.dll—must be distributed as shared or as private assemblies.

While some new reserved keywords are introduced, generally the overall list of reserved words has been reduced dramatically by making Firebird's parser grammar allow most of the previously reserved non-standard keywords as non-reserved. The list of those still reserved, and of SQL standard keywords newly reserved, is available in the chapter Reserved Words and Changes.

Some changes will now cause exceptions during run-time execution of queries, including those that are run during the execution of the gbak utility code (backups and restores).

Because v.2.5 maintains separate lock structures for each database on the server, fb_lock_print now requires a database path name in order to print the lock table. Include the new switch -d <path name> in the command line to specify the filesystem path to the database you wish to analyse.

The API functions isc_start_transaction() and isc_start_multiple() will now reject combinations of transaction parameter buffer (TPB) items that do not “belong together”.

For example, a non-zero wait timeout is inconsistent with the no wait option; and no record version is inconsistent with any transaction isolation mode other than ReadCommitted. Now, instead of making some arbitrary (and possibly incorrect) assumption about the inherent ambiguities, the engine will reject such combinations as invalid.

For more information, see the topic Transaction Diagnostics in the chapter Changes in the Firebird Engine.

New SQL_NULL constant was introduced to enable the predication OR ? IS NULL to be recognised and processed with the expected outcome and without engendering the “Data type unknown” exception. This affects how the XSQLVAR structures are populated for such queries. For information, refer to the topic SOME_COL = ? OR ? IS NULL Predication in the DML chapter.

In V.2.1, members of administrative Windows groups were mapped to SYSDBA by default. From V.2.5 forward, SYSDBA mapping is controlled on per-database basis using the new SQL command

ALTER ROLE RDB$ADMIN SET/DROP AUTO ADMIN MAPPING

For more details, refer to the chapter on Security.

In V.2.1, where support for Windows trusted authentication was introduced, the default authentication method applied was mixed, i.e., the DPB or SPB would accept either native Firebird logins or trusted user logins. Thus, he Authentication parameter in firebird.conf showed mixed as the default.

From V.2.5 forward, the default is native. To have mixed or trusted, it is now necessary to configure this parameter specifically.

Tracker reference CORE-2376)

To make the new engine work properly under multi-threaded conditions we had to use Grand Central Dispatch, which was first released in MacOSX 10.6 (Snow Leopard). Thus, MacOSX users should be aware that Firebird 2.5 will run only on MacOSX 10.6 or higher versions.

Port of Firebird 2.5.1 to Linux on HPPA.

Tracker reference CORE-3184

Port of Firebird 2.5.1 to Linux on Alpha.

Tracker reference CORE-3184

Tracker reference CORE-2625

Port for Linux/s390 (32-bit) platform, built on s390x architecture.

The patch for this port removes the -DS390X define from prefix.linux_s390x and replaces it with checks for __s390__ and __s390x__ defines which are presented by gcc. This way, both ports can use the one prefix file.

Tracker reference CORE-2559

Port for Linux/s390x (64-bit) platform.

Tracker reference CORE-2644

All of the POSIX platform ports except HP-UX enable the lock table to grow when the space initially allocated for it becomes exhausted. For the HP-UX port, the hardware limitations of the PA-RISC platform prevented the same implementation of dynamic resizing because it does not support remapping the same file region to a different virtual address in the same process. ISC_remap_file therefore does not work and the lock table could not grow.

The SAS team had developed a solution in their edition of the Vulcan code to make the lock table expansible on this platform. It is this solution that has been imported to the Firebird 2.5 port for HP-UX.

(CORE-4713)     A “BLOB not found” error would occur on rollback after inserting into a table with an expression index.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4700)     There was a mistake in testing for shadows when executing GDS_DROP_DATABASE. Fix has been backported from Firebird 3.0.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4690)     DISTINCT vs non-DISTINCT could affect the result of a subquery involving the IN() criterium.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4678)     A “BLOB not found” error could be thrown (wrongly) in uncommitted transactions on Classic or SuperClassic.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4673)     Computed indexes based on a computed column were storing NULL for all keys.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4670)     A constraint violation error could be swallowed in some cases.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4644)     Open database errors could occur under heavy concurrent load.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-4631)     When the shared memory region backing the lock table could not be remapped, the status vector would return the error message “Lock manager out of room” with no low-level details (e.g. OS level error), obscuring the actual cause of the problem.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4627)     A deadlock could occur between the TIP page lock and the monitoring lock when an ON CONNECT trigger accessed the monitoring tables.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4618)     Rollback would fail to undo changes when a MERGE statement updated the same target rows multiple times and PLAN MERGE was used.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4615)     The Classic server could hang with (near) 100% CPU load, caused by looping of an AST process under some conditions.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4599)     The REPLACE() function was working incorrectly with multi-byte character sets.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-4530)     DB_KEY based join of two tables could suffer in performance in a way that was traced to the order of the expressions in the statement.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4475)     After fb_shutdown(), end-of-process cleanup should not do anything. A situation was reported where it seemed possible to create a file during cleanup. This was corrected.

fixed by A. Person

    ~ ~ ~     

(CORE-4384)     Problems would occur when a table grew beyond 65535 pointer pages.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4383)     Garbage collection on indexes and BLOBs was not working for update_in_place().

fixed by D. Yemanov, D. Sibiryakov

    ~ ~ ~     

(CORE-4382)     User savepoints were not being released on commit.

fixed by D. Sibiryakov

    ~ ~ ~     

(CORE-4616)     The server could crash while the same context variable was being accessed concurrently by separate threads.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4588)     Superserver would die when client was disconnected abnormally during an index navigational scan.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4500)     Firebird was crashing after an unsuccessful remapping of the lock table's shared memory.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4075)     Server bugchecks or crashes would occur when an exception was raised in a calculated index.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4604)     During EXECUTE STATEMENT, the char_length() size of a UTF8 varchar would increase unexpectedly.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-4566)     The output parameter or argument would have an incorrect size when an executable block, a procedure or a function used a system field in the metadata character set.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-4624)     Firebird was mishandling colon characters in POSIX mount table entries.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-4460)     Expressions containing some built-in functions might not have been optimized well.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4444)     The engine could hang and block all attachments in an out-of-disk-space condition during physical backup.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2648)     Writes to nBackup's delta file were ignoring the Forced Writes database setting.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4433)     The global read/write lock (GlobalRWLock) could not downgrade EX lock to SH if readers were present.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4372)     Deadlock was possible when two data pages contained record fragments pointing to each other.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4353)     Sorting records were unnecessarily large.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4302)     Lookup (or scan) in a descending index could be very inefficient for some keys.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4299)     Error “Inappropriate self-reference of column” when using WITH CHECK OPTION with the EXTRACT() function.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-4283)     “Resource temporarily unavailable” errors while registering events simultaneously.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-4251)     The Guardian service could write garbage after the end of the message in the Event Log.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4250)     An access violation could occur in the Guardian at process shutdown.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4216)     Memory leak with TRIGGER ON TRANSACTION COMMIT.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4214)     A global temporary table (GTT) could refer to a permanent relation, which it should not be able to do.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4211)     The embedded engine could hang for 5 seconds while closing, with errors about timeout in shutdown process and invalid mutex placed in firebird.log.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-4198)     An incorrect “token unknown” error was raised when an SQL string ended with a hex number literal.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-4145)     Memory would leak during preparation of an EXECUTE BLOCK that used domains.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-4144)     The error “context already in use (BLR error)” was raised when preparing a query with UNION.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4143)     Memory was leaking in fbembed.dll.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4139)     The error “invalid stream” could be raised in some cases while matching a computed index.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4135)     Sweep was blocking establishment of concurrent attachments in Superserver.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4134)     Race conditions could arise when auto-sweep was started.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4118)     An expression index might not be used for derived fields or view fields.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4117)     A COMPUTED BY field was being evaluated as NULL if used as an exception parameter directly.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4113)     EXECUTE BLOCK prepare was failing.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4102)     Optimization of OR predicates was poor when applied to unions.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4101)     Invalid “I/O error during write operation” entries would appear in the firebird.log when no such database error existed.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4100)     Automatic sweep could be run when there was no need for it.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4083)     Full outer join in derived table with COALESCE (or IIF) was not returning NULLs correctly.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-4058)     Remote stack buffer overflow in the server

fixed by A. Peshkov and released in the special release Firebird 2.5.2 Update 1 (details below).

    ~ ~ ~     

(CORE-4050)     SQL Dialect was not being set in internal connections to the security database.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-4051)     Memory was leaked when sorting records larger than 32 KB.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4038)     Optimization for stored DBKEYs was broken.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4036)     Bugcheck or database corruption could occur when attempting to store long, incompressible data into a table.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4005)     A failing recursive CTE would return the wrong error message.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-4004)     Sometimes long-running operations could not be interrupted by asynchronous shutdown / cancellation requests.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-4002)     The error message “index unexpectedly deleted” could appear in a database trigger ON COMMIT TRANSACTION.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3989)     Bad performance or slow response were exhibited when many concurrent sorts were being executed.

fixed by D. Yemanov, V. Khorsun

    ~ ~ ~     

(CORE-3981)     Predicate checking was sub-optimal while selecting from a view.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-3978)     Invalid transaction counters could be reported in firebird.log during a sweep.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-3940)     I/O error during GetFileSize operation for file XXXX

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3924)     Bugcheck 291 (cannot find record back version) if a global temporary table (GTT) was modified concurrently with at least one read-committed read-only transaction.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-3921)     Error “record disappeared (186), file: vio.cpp line: 408”, in company with CPU at 100 per cent (when bugcheckabort=1 and sweep starts at gap ~21000).

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3916)     A false validation error could occur for large tables, viz., “Index x is corrupt (missing entries) in table ...”

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3902)     Derived fields might not be optimized by use of an index.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-3874)     Computed column was appearing in non-existent rows of a left-joined set.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-3817)     Forced database shutdown did not work if a non-zero delay was specified.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-3360)     UPDATE ... RETURNING ... would raise error -551 (no permission to update) for a column present only in the RETURNING clause.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-3305)     “BLOB not found” error was being raised after creating or altering an invalid trigger.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2848)     “lock conversion denied” or “lock denied” error was being raised when contention was relatively high.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2648)     Writes to nBackup's delta file were ignoring the Forced Writes database setting.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2165)     An unnecessary index read could occur when using a strict inequality condition.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-1604)     An error would be raised at database creation if the user had a non-ascii name.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-1475)     A database which had active attachments could not be replaced from a gbak backup file even after the database was shut down.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-1295)     Queries with DB_KEY were poorly optimized.

fixed by D. Yemanov

    ~ ~ ~     

Improvements

(CORE-4445)     Physical backup (using ALTER DATABASE BEGIN/END BACKUP or the nBackup utility) was improved to speed up extension of the main database file when backup state changes from stalled to merge.

implemented by V. Khorsun

    ~ ~ ~     

(CORE-4443)     Fast file growth has been enabled on Linux systems that support fallocate().

implemented by V. Khorsun

    ~ ~ ~     

(CORE-4432)     Attachments no longer block others when the allocation table is being read for the first time.

implemented by V. Khorsun

    ~ ~ ~     

(CORE-4431)     Contention for the allocation table lock while a database is in the stalled physical backup state has been reduced.

implemented by V. Khorsun

    ~ ~ ~     

(CORE-4386)     More details are now reported for “object in use” errors.

implemented by D. Yemanov

    ~ ~ ~     

(CORE-4252)     The relation name is now added to the text of validation contraint error messages, to help identify the error context.

implemented by V. Khorsun

    ~ ~ ~     

(CORE-4215)     Execution of a SET STATISTICS INDEX statement no longer blocks or slows down concurrent attachments.

implemented by V. Khorsun

    ~ ~ ~     

(CORE-3994)     The scan for limbo transactions scan at the end of a sweep has been improved.

implemented by D. Yemanov

    ~ ~ ~     

(CORE-3881)     Error reporting for index and constraint violations has been extended to include the problematic key value.

implemented by D. Yemanov

    ~ ~ ~     

(CORE-3704)     New context variables have been added to the SYSTEM namespace to retrieve more information about the current connection and current transaction.

The added variables: SYSTEM::CLIENT_PID and SYSTEM::CLIENT_PROCESS for the current connection, SYSTEM::LOCK_TIMEOUT and SYSTEM::READ_ONLY for the current transaction.

implemented by D. Yemanov

    ~ ~ ~     

(CORE-4438)     Support for the UPDATE OR INSERT statement has been implemented for Embedded SQL (ESQL).

implemented by D. Yemanov

    ~ ~ ~     

(CORE-4437)     Support for the RETURNING clause has been implemented for Embedded SQL (ESQL).

implemented by D. Yemanov