Firebird Book (2nd Edition)

Book summary

Part I Firing Up with Firebird

• 1 Firebird Servers and Clients
• 2 Installation
• 3 Network Setup and Initial Configuration
• 4 Operating Basics
• 5 Migration Notes

Part II Firebird Data Types & Domains

• 6 About Firebird Data Types
• 7 Number Types
• 8 Date and Time Types
• 9 Character Types
• 10 BLOBS and Arrays
• 11 Domains

Part III A Database & Its Objects

• 12 Designing and Defining a Database
• 13 Data Definition Language—DDL
• 14 Creating and Maintaining a Database
• 15 Tables
• 16 Indexes
• 17 Referential Integrity

Part IV Working With Data

• 18 Data Manipulation Language—DML
• 19 DML Queries
• 20 Expressions and Predicates
• 21 Querying Multiple Tables
• 22 Ordered and Aggregated Sets
• 23 Views and Other Run-time Set Objects
• 24 Interactive SQL Utility (isql)

Part V Transactions

• 25 Overview of Firebird Transactions
• 26 Configuring Transactions
• 27 Programming with Transactions

Part VI Programming on the Server

• 28 Procedural SQL—PSQL
• 29 Stored Procedures and Executable Blocks
• 30 Triggers
• 31 Accessing Other Databases from PSQL
• 32 Error Handling and Events

Part VII Configuring Firebird

• 33 Configuring Firebird and Its Environment
• 34 Configuration Parameters in Detail
• 35 Configuring and Managing Databases

Part VIII Administering & Securing Firebird

• 36 Protecting the Server and its Environment
• 37 Database-Level Security
• 38 Monitoring and Logging Features
• 39 Backing Up Databases
• 40 The Services Manager

Part IX Appendices

• Appendix I - Internal and External Functions
• Appendix II - Reserved and Non-reserverd Keywords
• Appendix III - Context Variables
• Appendix IV - Firebird Limits
• Appendix V - System Tables and Views
• Appendix VI - Character Sets and Collations
• Appendix VII - Error Codes
• Appendix VIII - SQLSTATE Codes
• Appendix IX - Database Repair How-to
• Appendix X - Default Disk Locations
• Appendix XI - Healthcare for Databases
• Appendix XII - Upgrade Scripts
• Appendix XIII - Application Interfaces
• Appendix XIV - Resources

Firebird 3.0 Supplement

Firebird 3 has brought a fully unified server architecture and improved support for multi-core hardware platforms. Threading of engine processes has been improved, bringing the ability for Superserver to share page cache across thread and connection boundaries.

New strategies inside the engine improve performance, query optimization, monitoring and scalability. Many important features that are globally configurable can now be overridden and applied on a "per-database" basis. Above all, Firebird 3 provides more options for server and database security. An authentication database can be dedicated to one or more specific databases, or embedded within a user database.

A number of sought-after features were introduced into the SQL language, including a fully-implemented Boolean data type, a Microsoft-style IDENTITY type, SQL packages, stored user-defined functions and SQL-driven management of user accounts and access privileges.

Despite the advances, Firebird 3 is still the Firebird that we know and love. This Supplement provides an updated reference for everyone who uses, or wants to use, the Firebird relational database management system as a back-end component to software ranging from bulk data collectors to enterprise-wide systems. It should be treated as an adjunct to the companion volumes, certainly not as a substitute—so don’t throw away those doorstoppers that you bought for Firebird 2.

The original "The Firebird Book: a Reference for Database Developers", also written by Helen Borrie, covered Firebird versions 1.0 and 1.5 and was published by Apress in 2004, to rave reviews. Since then, as first Firebird V2.0 and later V2.1 emerged, IBPhoenix was publishing an updated Supplement in PDF format, also authored by Helen, that followed the structure of the original book.

The new and revised edition largely follows the organisation of the original, packaging the Firebird essentials and know-how in eight parts, each consisting of groups of chapters reflecting a typical workflow for developers learning a database management system in new territory. More than a dozen appendices provide quick reference to facts, error codes, internal and external functions and more. This new edition provides a similarly useful desktop reference to its predecessor for most levels of professional user.

Because there's a lot more to be documented now, some of the theoretical stuff has been moved out to make way for the practical. This decision wasn't entirely due to pressure on the "real estate": during the past six years, as the capabilities of Firebird have been growing, the volume and quality of materials on the Web on these broader subjects has increased to the point where it no longer makes sense to try to cram them into a desktop companion.

The "Firing Up" section at the front of the book now has a chapter on migrating databases from older versions to new. The original "Tools" part, covering the command-line utilities, has been split up and redistributed into appropriate chapters. Much of that material will now be found in the large, new section for database administrators, which covers the tools and facilities that didn't exist in V1.5: database monitoring, tracing and incremental backups, to name the obvious ones.

In the new edition, the SQL parts, with updated content, are more distinctly distributed according to function (definition, manipulation, procedural programming). The need for this slight change of approach arose from the variety of significant changes and improvements in the language areas over the time since the first Firebird Book edition was published. In Firebird 2+ you can run blocks of procedural SQL in dynamic queries and even (from V2.5 on) write a procedure that can connect to another database in its own transaction and behave, with limitations, a lot like a client application. Data Manipulation Language (DML) became a lot richer, too, supporting not just new operations but also new ways to do things with expressions and subsets.

This new version is a comprehensive, practical guide to building highly stable, scalable database back-ends for your client/server and n-tier applications (including Web applications) using Firebird 2.5 and 3.0. Older versions of Firebird are not ignored, though. Where it matters, it is made clear at which point in Firebird's evolution the previously "undoable" becomes "doable" and how the same thing might be achieved with an older engine version.

About the Author

Helen Borrie is a database and application developer, a member of the Firebird project team, a support consultant with IBPhoenix and Secretary and general bottle-washer for the Firebird Foundation Inc. A former journalist, she has often found herself doubling as a technical writer. She is responsible for maintaining the comprehensive release notes that come with all Firebird releases and sub-releases. Over the years since Firebird came into existence in 2000, she has been a frequent contributor to the support lists for Firebird application developers and database administrators.