After two and a half years of development, the release of the Firebird 5.0 relational database management system has arrived. Firebird continues the development of the InterBase 6.0 database code, which was opened in 2000 by Borland. Firebird is distributed under the MPL open license and supports ANSI SQL standards, including features such as triggers, stored procedures, and replication. Binary builds are prepared for Linux, Windows, macOS, and Android.
Key innovations:
- The ability to perform operations in a multithreaded mode has been implemented. Parallel processing is applicable when creating indexes, garbage collection (both automatic and manual sweep operations), creating backups, and restoring from them. gfix -sweep -parallel 4 dbname gbak -b -par 4 -user username -pass password dbname backupname
- Partial index support has been added, which includes only selective records that meet the specified conditions at the time of index creation. CREATE INDEX IT1_COL ON T1 (COL) WHERE COL < 100;
- The SELECT WITH LOCK, UPDATE, and DELETE statements have implemented the 'SKIP LOCKED' syntax, allowing the exclusion of records that are locked at the time of the request being sent (for instance, to avoid waiting for a lock to be released when intersecting with another transaction).
- The ability to update the database to the current intermediate version of the storage (ODS — On-Disk Structure) inline, without creating and restoring from a backup, has been added. For example, it is now possible to convert a Firebird 4.0 database (ODS 13.0) to Firebird 5.0 format (ODS 13.1) on the fly.
- A cache for compiled SQL expressions is implemented, automatically managed (obsolete records are cleared as needed). The cache size is set through the MaxStatementCacheSize parameter in firebird.conf.
- An interface for profiling SQL and PSQL has been added, allowing the assessment of the execution time of each query, accumulation of statistics on the number of queries, and identification of performance issues.
- The MERGE statement has added support for the conditional block 'WHEN NOT MATCHED BY SOURCE', which triggers when the original record does not match any record in the target set. MERGE INTO customers c USING new_customers nc ON (c.id = nc.id) WHEN MATCHED THEN UPDATE SET name = nc.name WHEN NOT MATCHED BY SOURCE THEN DELETE
- The ability to return multiple rows using the RETURNING statement has been provided if the specified DML statement covers multiple rows (previously in such cases an error was displayed: 'multiple rows in singleton select').
- New built-in functions have been added: UNICODE_CHAR to return the unicode character corresponding to a given code, and UNICODE_VAL to return the code for a given character. The EXTRACT, FIRST_DAY, and LAST_DAY functions now include the QUARTER parameter to determine the quarter number.
- A new system package RDB$BLOB_UTIL has been added, providing a set of operations for BLOBs for PSQL modules.
- The record compression algorithm has been improved, now utilizing a variable-length counter for more efficient packing of repeating data sequences. This optimization has significantly increased the compression ratio of sparsely filled long VARCHAR fields containing UTF-8 encoded data.
- Support for bi-directional cursors (scrollable) has been added for remote network access to the database.
- Performance optimization for copying blobs has been completed.
- Full support for string definition syntax described in the SQL standard has been added. For example, strings can now be broken by comments or spaces: select 'ab' /* comment */ 'cd' from RDB$DATABASE; select 'ab' 'cd' from RDB$DATABASE;
Source: opennet.ru
