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
          

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-3727)     Improvement :: Support was added for C preprocessor flags in the Firebird build system.

implemented by A. Peshkov

    ~ ~ ~     

(CORE-3656)     Improvement :: Support for sweep information in Trace API.

implemented by V. Khorsun

    ~ ~ ~     

(CORE-3598)     Improvement :: TRACE now produces statistics of actions that happen after a transaction has finished.

implemented by V. Khorsun

    ~ ~ ~     

(CORE-3539)     Improvement :: TRACE now provides the ability to log errors that occur in runtime (lock conflicts, key violations, et al.)

implemented by V. Khorsun

    ~ ~ ~     

(CORE-2668)     Improvement :: A note is now written into firebird.log when an automatic sweep is started.

implemented by V. Khorsun

    ~ ~ ~     

(CORE-2666)     Improvement :: It is now possible to use the API to do a remote backup/restore.

See also /doc/README.services_extension in your Firebird installation.

implemented by A. Peshkov

    ~ ~ ~     

(CORE-3877)     The functions CHAR_TO_UUID and UUID_TO_CHAR worked wrongly on big-Endian platforms.

Fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-3855)     Blobs inserted into GLOBAL TEMPORARY TABLE ON COMMIT DELETE ROWS could be placed into newly allocated pages even if there was enough free space on an existing data page.

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3853)     A database created in Firebird 2.5.1 could cause the “IS NULL” predicate to be evaluated differently if that database was used with Firebird 2.5.0.

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-3844)     Validation was failing to detect a certain case of index corruption.

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3841)     A database could be left corrupted when rows were inserted.

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3825)     When a transaction started with the isc_tpb_autocommit option) tried to run DDL using EXECUTE STATEMENT, it would produce a bugcheck 287 (“too many savepoints”).

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3799)     The WITH CALLER PRIVILEGES option would not work with the AUTONOMOUS TRANSACTION option.

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3792)     Performance of batch inserts was degraded.

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3791)     Performance would degrade when the engine was actively working with databases bigger than the available amount of RAM.

Fixed by N. Samofatov, D. Yemanov

    ~ ~ ~     

(CORE-3761)     A conversion error could occur when using a BLOB as an argument for the EXCEPTION statement.

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-3730)     The function isc_dsql_exec_immed2() would lose an input parameter value when passing it to the RETURNING clause.

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-3722)     IS NOT DISTINCT FROM NULL was not using an index when it could have done so.

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-3697)     A string truncation error could occur when selecting from a VIEW with UNION inside.

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-3692)     It was impossible to drop a NOT NULL constraint on a column participating in a UNIQUE constraint.

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-3690)     Wrong warning messages were being returned for some ambiguous queries.

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3677)     Utilities had to be prevented from exporting entry points due to bugs that can occur when they were used with some external functions in embedded mode.

Fixed by A. Peshkov

    ~ ~ ~     

(CORE-3675)     CREATE INDEX was treating NULL and empty string in compound indices as though they were the same.

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-3631)     Duplicate records with NULLs were being checked incorrectly.

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3610)     It was possible to insert duplicate keys into a unique index.

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3601)     Incorrect TEXT BLOB charset transliteration was occurring on VIEWs with triggers.

Fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-3599)     Dropping the system role RDB$ADMIN was allowed.

Fixed by A. Peshkov

    ~ ~ ~     

(CORE-3579)     A table could not be dropped when a computed column depended on another column that was created after it.

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3557)     The server would crash while preparing a query against a table that was in the process of being dropped.

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3490)     Concurrency problems could occur when named cursors were being used.

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-3238)     Make GEN_UUID() return a compliant RFC-4122 binary UUID.

Fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-3092)     ROW_COUNT was not being cleared before the singleton INSERT statement.

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-2457)     UNICODE_CI collation was causing an internal gds software consistency check.

Fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-1997)     Foreign key handling for multi-segmented index using multi-level collations was broken.

Fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-1992)     Error “bad BLR -- invalid stream for union select” was being thrown inappropriately.

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-927)     Grants would not work for procedures used inside views.

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-3884)     The server could crash on preparing an empty query when trace was enabled.

Fixed by D. Starodubov, V. Khorsun

    ~ ~ ~     

(CORE-3873)     The server could crash while switching to the shadow if a disk I/O fault happened while flushing the cache.

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-3834)     Using a NATURAL JOIN with a derived table could crash the server.

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-3814)     The SuperClassic server could crash when performing a database shutdown with Forced Writes off.

Fixed by V. Khorsun, D. Yemanov

    ~ ~ ~     

(CORE-3636)     The Trace API was causing the v.2.5.1 server to crash.

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3627)     The server could crash with an access violation when inserting a row into a table with a unique index.

Fixed by A. Peshkov

    ~ ~ ~     

(CORE-3807)     Error “Invalid expression in the select list” could be raised unexpectedly if a string literal were used inside a GROUP BY clause in a multi-byte connection.

Fixed by D. Yemanov, A. dos Santos Fernandes

    ~ ~ ~     

(CORE-3806)     Wrong data was being returned when a subquery or a computed field referred to the base table in the ORDER BY clause.

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-3777)     Unexpected “conversion error from string” errors could occur when using GROUP BY.

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-3683)     Wrong results were being produced if a recursive query contained an embedded GROUP BY clause.

Fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-3736)     The WITH LOCK clause was being allowed for users with read-only rights on some table, thus blocking others from updating that table.

Fixed by A. Peshkov

    ~ ~ ~     

(CORE-3611)     Wrong results could occur while retrieving from CTEs or derived tables where the same column names were repeated.

Fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-3625)     MON$IO_STATS was not reporting page writes performed asynchronously (at the AST level).

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-2286)     Selecting from MON$CALL_STACK within a PSQL module would sometimes return zero rows.

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-3860)     A faulty database filter in the Trace API could crash the server.

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3845)     When a “heavy” query was interrupted, its duration would be logged as zero milliseconds in trace statistics.

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3612)     gfix-related services could lose an error value in the status vector in isc_service_start().

Fixed by A. Peshkov

    ~ ~ ~     

(CORE-3912)     SuperClassic was exhibiting a segmentation fault because the GlobalPtr<> for variable INET_select was missing.

Fixed by A. Peshkov

    ~ ~ ~     

(CORE-3750)     Increasing limits on POSIX could give rise to errors.

Fixed by A. Peshkov

    ~ ~ ~     

(CORE-3721)     The multi-user server startup script (/etc/init.d) was picking up the ISC_ variables if they were set.

Fixed by A. Peshkov

    ~ ~ ~     

(CORE-3646)     Segmentation faults were occurring in FreePascal multi-threaded programs when using the v.2.5.x client library on Linux.

Fixed by A. Peshkov

    ~ ~ ~     

(CORE-3609)     Option -t was being displayed twice by fb_inet_server -h.

Fixed by A. Peshkov

    ~ ~ ~     

(CORE-3607)     Solaris does not define the RLIMIT_NPROC limit, thus causing compilation of src/remote/inet_server.cpp to fail on that platform.

Fixed by A. Peshkov

    ~ ~ ~     

(CORE-3606)     Linker commands that use a C or C++ compiler should apply CFLAGS and CXXFLAGS respectively, to avoid compilation problems on Solaris.

Fixed by A. Peshkov

    ~ ~ ~     

(CORE-3605)     GLOB_OPTIONS mixed CFLAGS and CXXFLAGS as though they were interchangeable across platforms; yet they could have exclusive differences, due to differences in compiler implementations.

Fixed by A. Peshkov

    ~ ~ ~     

(CORE-3819)     Resolution of service name to port address in the database connection string could be performed wrongly.

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3812)     A client could lose its connection to the database during a session where huge numbers of primary keys were being dropped and/or altered.

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3801)     Warnings could appear twice in the status vector.

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3778)     An access violation could occur at connection shutdown.

Fixed by V. Khorsun

    ~ ~ ~     

(CORE-3732)     Closing an attachment to a database could cause a segmentation fault.

Fixed by A. Peshkov

    ~ ~ ~     

(CORE-3569)     CHAR(32767) was presented in the XSQLVAR with length 32765.

Fixed by D. Yemanov

    ~ ~ ~     

(CORE-3537)     Improvement :: The “undoing” on transaction rollback of changes made to global temporary tables created with the ON COMMIT DELETE ROWS option was unnecessary and was removed.

implemented by V. Khorsun

    ~ ~ ~     

(CORE-3536)     Improvement :: Garbage collection in global temporary tables was being delayed unnecessarily by active transactions in other attachments.

implemented by V. Khorsun

    ~ ~ ~     

(CORE-3457)     Improvement :: The temporary space manager has been made more optimal with regard to small chunk allocations.

implemented by D. Yemanov

    ~ ~ ~     

(CORE-3323)      Improvement :: Lock Manager was provided with the capability to cancel waiting.

Considering the following example:

  tx1: update table t ... where id = 1
  tx2: update table t ... where id = 1
        

If transaction tx2 is in WAIT mode, it will wait for the end of tx1 forever and this wait cannot be broken using either a DELETE FROM MON$xxx or a fb_cancel_operation request.

The improvement provides Lock Manager with the ability to break such interminable waits.

implemented by V. Khorsun

    ~ ~ ~     

(CORE-3295)      Improvement :: Estimate the actual record compression ratio in the optimizer, thus allowing a more precise guess about table cardinalities (number of stored records).

implemented by D. Yemanov

    ~ ~ ~     

(CORE-3560)     The V.2.5 Classic Server was using more memory than V.2.1.5 when caching metadata.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-3549)     Database corruption could occur at the end of a session, throwing the error “page xxx is of wrong type expected 4 found 7”

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3547)     A floating-point negative zero was not the same as a positive zero in indexes.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-3535)     The write target of a dirty page could be undefined if an error occurred when the nbackup state was changed.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3533)     On SuperServer, a memory leak would occur if a connection was terminated without explicitly releasing the handles for all the statements prepared and cached by the client application.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3532)     The server would hang on starting a new session when a trace was running.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-3525)     Autonomous transactions were wrongly inheriting the run-time flags of the "parent" transaction.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3515)     Index corruption could occur under a complex set of conditions that was causing index updates to get out of synch and lose entries. Validation would pick it up and write the "missing entries" message into the firebird.log

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3512)     

fixed by A. PeshkovThe server could hang when a trace was running.

    ~ ~ ~     

(CORE-3509)     ALTER PROCEDURE was permitting the addition of a parameter with the same name as an existing one.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3502)     DROP VIEW was ignoring the existing non-column dependencies.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3494)     Attach would fail anyway, after a shutdown had been rejected by the handler installed in fb_shutdown_callback().

fixed by A. Peshkov

    ~ ~ ~     

(CORE-3491)     Altering a TYPE OF COLUMN parameter in a PSQL module would affect the original column.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-3461)     DDL operations would fail after a backup and restore.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-3443)     Race conditions could occur during UDF library lookup.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-3418)     Database trigger created as INACTIVE was coming up active.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3398)     GRANT ADMIN ROLE was not being accepted.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-3397)     Unresolved symbols were found in intl and trace libraries.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-3394)     Failed attempt to violate a unique constraint could leave an unnecessary “lock conflict” error in the status vector.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3341)     Events posted from inside an autonomous transaction could get lost and never be delivered to the listening client.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-3340)     In an autonomous transaction with an empty exception handler, duplicate values could be inserted into a primary of unique key column, leading to an unrestorable backup.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-3327)     The thread pool in a network server could create more threads than were necessary.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3326)     A fast mutex could be left in locked state by a dead process.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3325)     Under high load it was possible that a new process would fail to map shared memory.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3315)     The audit plugin would record a second *FAILED* EXECUTE_STATEMENT_FINISH after a “normal” one.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3314)     Dependencies were not being removed after a procedure and the table it depended on were dropped in the same transaction.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-3312)     The join plan was sub-optimal when the slave table depended on the master one by means of the OR predicate.

fixed by D. Yemanov