Software-update: Firebird 2.0

Om grote hoeveelheden gegevens op te slaan worden vaak databases gebruikt. Aangezien deze beter te onderhouden en te benaderen zijn dan een simpel tekstdocument waarin de gegevens worden vastgelegd. Bekende namen zijn natuurlijk Oracle met zijn Oracle 9i database, IBM met de DB2 database en Microsoft met SQL Server.

Firebird heeft als oorsprong de InterBase database van Inprise Corp (tegenwoordig Borland Software Corp), de geschiedenis van InterBase en Firebird is op deze pagina na te lezen. Deze relationele database ondersteunt verschillende besturingssystemen waarop het geïnstalleerd kan worden, zoals het Linux- en Windows-platform waardoor het breed kan worden ingezet. De ontwikkelaars hebben versie 2.0 uitgebracht met de volgende opmerking:

Firebird 2.0

Firebird 2.0 brings a large collection of long-awaited enhancements that significantly improve performance, security and support for international languages and realise some desirable new SQL language features. Under the surface, it also provides a much more robust code platform from which the re-architecting planned for Firebird 3.0 is proceeding.

Several limitations inherited from the legacy code have now gone, such as the 252-byte limit on index size and sub-optimal usage of index statistics that could throttle performance. Table size is no longer limited to 30 Gb.

Indexing has undergone a thorough reworking to ramp up performance and reduce or eliminate the effects of poor selectivity.

An important feature is the introduction of a new interface for plugging in international character sets, including enhanced Unicode support, along with a number of new and corrected collations.

Firebird 2.0 now supports 64-bit platforms with released binaries available for AMD64 and Intel EM64T on Linux. Windows 64-bit builds are ready for testing and will follow in a sub-release.

Several aspects of server security have received an intense makeover, with a large number of known vulnerabilities in the API being closed along the way.

Porting of the Services API to Classic architecture is now complete. All Services API functions are now available on both Linux and Windows Classic servers, with no limitations.

Language Enhancements

Many new additions have been made to the SQL language, including support for derived tables (SELECT ... FROM ( SELECT ... FROM)) with multiple layers of nesting and the ability to join nested sets, as defined by SQL200X.

A new capability has been added to execute blocks of procedural SQL (PSQL) in dynamic SQL statements, by way of the new EXECUTE BLOCK syntax.

An optional RETAIN clause is added to the DSQL ROLLBACK statement to make it consistent with COMMIT [RETAIN].

All Firebird versions provide two transaction wait modes: NO WAIT (non-blocking, excepts immediately on conflict) and WAIT (blocking, waits until the conflicting transaction ends). A new feature extends the WAIT mode by allowing a finite interval of waiting and then reporting an error (isc_lock_timeout). Timeout intervals are specified per transaction and are available both in the API and via the LOCK TIMEOUT clause of the SET TRANSACTION statement.

A reworking has been done to resolve problems with views that are implicitly updatable, but still have update triggers. This is an important change that will affect systems written to take advantage of the undocumented [mis]behaviour in previous versions.

String search operators have been re-implemented and now work correctly with BLOBs of any size. Introduction of a single-pass Knuth-Morris-Pratt algorithm for pattern matching improves performance when complex patterns are used.

From Firebird 1.0 onward, concatenation operations were checked for potential overflow at prepare time and would except if an overflow was possible according to the defined or declared lengths of the operands. Thus, an expression like "CAST('qwe' AS VARCHAR(30000)) || CAST('rty' AS VARCHAR(30000))" would cause an exception because 60000 bytes exceeds the string limit. Now, it will cause only a warning at prepare time and will succeed if the introduced run-time check on actual data detects an overflow.

Some handy new extensions have been added to PSQL itself, including multiple explicit cursors which are also available within DSQL EXECUTE BLOCK statements.

Invariant tracking in PSQL and request cloning logic were reworked to fix a number of performance and accuracy issues with recursive procedures.

Security

Password encryption now uses a more secure password hash calculation algorithm (SHA-1), encryption becomes entirely server-based and password login is now required from any remote client, regardless of the user's platform privileges. Non-server access to security database is rejected: the server will refuse any access to the authentication database, even by the SYSDBA'S gsec utility, except through the Services API.

The SYSDBA remains the keeper of the security database but users can now modify their own passwords by way of a view over the (new) underlying, protected RDB$USERS table.

Attempts to get access to the server using brute-force techniques on accounts and passwords are now detected and locked out, with support for this protection built in to both the database and services API's.

Network and Connection

Server redirection ("multi-hop") capability, long disabled because of exposure to misuse, has been restored and made available via a configuration parameter for careful use by those who properly understand its implications.

Firebird 2.0 replaces the former implementation of the local transport protocol on Windows (often referred to as IPC or IPServer) with a more robust new one, named XNET. It serves exactly the same goal, to provide an efficient way to connect to server located on the same machine as the connecting client without a remote node name in the connection string. The XNET implementation does not suffer the instability inherent in its predecessor. It works with the Classic server, it works for non-interactive services and terminal sessions and it eliminates lockups when a number of simultaneous connections are attempted. Slightly faster performance can be expected also.

Under the WNET (a.k.a. NetBEUI) protocol, all previous Firebird versions performed remote requests in the context of the client security token. Since the server serves every connection according to its client security credentials, this means that, if the client machine is running some OS user from an NT domain, that user required appropriate permissions to access the physical database file, UDF libraries, etc., on the server filesystem. This situation-known as client impersonation-is contrary to proper protocol for a client-server setup with a protected database and is no longer supported. In Firebird 2.0, WNET connections behave the same way as TCP ones, presuming nothing with regard to the rights of OS users.

Connection handling on POSIX Superserver has been improved and will now handle SIGTERM and SIGINT to shutdown all connections gracefully.

Engine Internals

Firebird 2.0 introduces 40-bit (64-bit internally) record enumerators to overcome the ~30GB table size limit imposed by 32-bit record enumeration.

Garbage collection: Since Firebird 1.0 and earlier, the Superserver engine has performed background garbage collection, having abandoned the "cooperative" mechanism that drives GC in the Classic server environment. It has long been recognised that cooperative GC, if available, would be useful to Superserver for some conditions and configurations where waiting for background GC to finish a cycle causes peformance bottlenecks.

In Firebird 2.0 Superserver, both cooperative and background garbage collection are performed in combination, by default. To manage it, the new configuration parameter GCPolicy was introduced to make it possible to set GC to be only cooperative or only background.

Lock contention in the lock manager and in the SuperServer thread pool manager has been reduced significantly and lock manager memory dumps now provide better information.

Several debugging improvements have been added, including file name and line number being reported for BUGCHECK log messages, new thread-safe and signal-safe debug logging facilities and the ability to copy syslog messages to the user's tty.

The root directory lookup path has changed so that server processes on Windows no longer use the Registry, although the command-line utilities still do check the Registry.

UDFs, Utilities and Command-line Tools

External functions now have the ability to signal SQL NULL via a NULL pointer and the native external function library ib_udf is upgraded to allow the string functions ASCII_CHAR, LOWER, LPAD, LTRIM, RPAD, RTIM, SUBSTR and SUBSTRLEN to return NULL and have it interpreted correctly.

Many of the command-line tools have been refurbished and this release introduces the all-new incremental backup tools NBak and Nbackup.

Single-user and full shutdown modes are made possible by the introduction of new [state] parameters for the gfix -shut and gfix -online commands.

Known issues in previous versions of Firebird with gsec error reporting through the Services API are eliminated.