Recently I've been writing about detecting Firebird server installations. Well, the registry structure supports multiple instances but the Firebird tools not, so far. There are two utilities in the Firebird installation:

• instreg (installs the DefaultInstance reference to the registry)
• instsvc (installs the Windows service that runs the DefaultInstance)

At the moment there is no way to install another instance using these tools - they have "DefaultInstance" hardcoded. Read more in README.Win32LibraryInstallation.txt (from Firebird 2).

New features:

• derived tables
• support for Execute Block
• increased table sizes
• new improved index code (the 252-byte index length limit is no longer applicable)
• expression indices
• numerous optimiser improvements
• enhanced security features
• support for on-line incremental backups
• numerous other improvements and bug fixes

Go to the downloads.

The Firebird query optimizer uses index "selectivity" or "statistics" for selecting the most effective query execution plan. Index selectivity is a number that describes how variable the indexed field values are, it is something like:

1/(DISTINCT COUNT(*))

That means the lower selectivity the better index. Values close to 1 (e.g. 0.5 for values like MALE/FEMALE) mean that the index is not effective and should be dropped. You can get the indices selectivity by calling:

SELECT RDB$INDEX_NAME, RDB$STATISTICS FROM RDB$INDICES;

Warning: In Firebird, the selectivity is calculated only in some cases:

1. When the index is created
2. When the database is restored from a backup

That means: In case you create the index before there are any data in the table the selectivity value will be 1. For getting the best performance (i.e. giving the optimizer correct information) you should recalculate the selectivity after major data changes. You can do this using this command for a single index:

SET STATISTICS INDEX index_name; 

It is also possible to recalculate all indices selectivity using this stored procedure:

SET TERM ^ ;

CREATE PROCEDURE MAINTENANCE_SELECTIVITY 
AS
DECLARE VARIABLE S VARCHAR(200);
BEGIN
FOR select RDB$INDEX_NAME FROM RDB$INDICES INTO :S DO
BEGIN
S = 'SET statistics INDEX ' || s || ';';
EXECUTE STATEMENT :s;
END
SUSPEND;
END
^

SET TERM ; ^

Execute this stored procedure by calling:

EXECUTE PROCEDURE MAINTENANCE_SELECTIVITY;

See my latest CodeProject article: Fulltext Search for Firebird SQL.

The FirebirdSql.Data.Firebird.Isql namespace provides classes for batch SQL/DDL execution. This allows you to make such operations as:

• creating the database structure programmatically
• updating the database structure automatically
• automating the maintenance procedure
• etc.

Running a batch script is not difficult. First you need to parse the script using FbScript class:

FbScript script = new FbScript("employee.sql");
script.Parse();

The result is stored in script.Results, one item per command. Now we can use FbBatchExecution class to run the script:

FbConnection c = new FbConnection(@"Database=employee.fdb;User=SYSDBA;Password=masterkey");
c.Open();

FbBatchExecution fbe = new FbBatchExecution(c);
foreach (string cmd in script.Results) {
   fbe.SqlStatements.Add(cmd);
}

fbe.Execute();
c.Close();

By default, the transaction is committed after each command. You can disable this by calling fbe.Execute(false).

Here is a complete example that uses an embedded Firebird to create a new employee.fdb database and initialize the structure using a DDL script. The DDL script is generated from the example employee.fdb (it's modified on two places because of the current bugs in the provider, see the Known Issues below).

On my Athlon XP 2000+ the execution of this batch script takes 0.921 seconds - which is very good I guess. 

Known Issues

• The current 1.7 RC2 version of ADO.NET provider has some bugs that prevent the example from runnin. It's fixed in the CVS. The example download contains a compiled current CVS snapshot. Recognition of 'CREATE DESCENDING INDEX' is added.
• Even the current CVS version has problems with '--' inside a string (it thinks that it's a line comment). For now I've changed all '--' occurences in the strings to '-'.

Update

The issues are already fixed because my changes were incorporated into the provider.

Download

• Example source - 1.6 MB (includes also the VS.NET project, compiled binary, fixed Firebird ADO.NET provider binary, employee.sql DDL script and embedded Firebird runtime with error messages file)

Related

• Documentation: Create a new database from an SQL script

A lot of people are asking for Firebird versus PostgreSQL comparison. This is a difficult task - both databases have much in common. For the beginning, let's start with Firebird's advantages:

• Mature Windows support. Firebird is supported on Windows for a very long time and it's well tested. PostgreSQL supports Windows natively since 8.0. Still only a few months...
• Mature ADO.NET provider. Npqsql (PostgreSQL ADO.NET provider) is still in beta. Firebird ADO.NET provider supports the embedded Firebird, services API (backup, restore, statistics, batch SQL execution...).
• Embedded version. Embedded version (with 2 MB runtime and easy switching to a standalone server) seems to be one of the biggest advantages of Firebird.
• Windows 98 support. PostgreSQL runs natively only on Windows NT-based versions.
• Licensing. LGPL (Npgsql is covered by LGPL) seems to be less commercial-friendly than IPL (at least it is a much less clear language).
• This blog ;-)

See also

• Firebird versus Microsoft SQL 2005 Express
• Firebird versus MSDE 2000
• Firebird versus Microsoft Jet

The Firebird Project will soon be releasing the first public "alpha" release of Firebird 2.0. Version 2.0 is a long-awaited important major release of Firebird with many new features, enhancements and bugfixes (see alpha Release Notes for details). In number of changes, the jump in this release is equivalent if not greater than the transition from version 1.0 to version 1.5.

Read more: Firebird 2.0 call for testers.

Firebird ADO.NET provider supports a new class (FbConnectionStringBuilder) that makes the work with connection strings much easier:

1. Parsing a connection string

Create a new instance using public FbConnectionStringBuilder(string) constructor (it takes an existing connection string as a parameter). Then you can read the connection string values by using the FbConnectionStringBuilder properties. Example of reading the DataSource (server address) property:

FbConnectionStringBuilder csb = new FbConnectionStringBuilder("User=SYSDBA;Password=masterkey;Database=SampleDatabase.fdb;DataSource=localhost;Charset=NONE;");
Console.WriteLine(csb.DataSource);

2. Creating a connection string programmatically

You can create a connection string by specifying the properties item by item:

FbConnectionStringBuilder csb = new FbConnectionString();
csb.UserID = "SYSDBA";
csb.Password = "masterkey";
csb.Database = "mydb.fdb";
csb.ServerType = 1; // embedded Firebird

FbConnection c = new FbConnection(csb.ToString());

3. Modifying an existing connection string

You can also modify an existing connection string without complicated parsing. This example switches the type of server to embedded Firebird:

FbConnectionStringBuilder csb = new FbConnectionString(existingConnectionString);
csb.ServerType = 1;

FbConnection c = new FbConnection(csb.ToString());

The ADO.NET provider 1.7 is currently in RC2. This is the list of main new features (compared to 1.6.3):

• New FbConnectionStringBuilder class that allows to build and change the connection string programmatically.
• Design-time support for FbDataAdapter (DataSet generator, configuration wizard).
• Initial support for Guid data type (Mapped to a CHAR(16) CHARACTER OCTETS field in firebird).
• The documentation package now contains preliminary documentation about how the Firebird wire protocol is implemented in the ADO.NET provider.
• Internal rework of GDS's implementations.
• Added the ability of create and drop databases using connection strings.
• The Firebird Services implementation now works using connection strings.
• Reimplementation of the Firebird Events System.
• Database schema support improvements.
• Added new "Isolation Level" parameter for the connection string that allows to set the default IsolationLevel for implicit transactions.
• A new FetchSize parameter in the connection string and a new FetchSize property in the FbCommand class.
• A better connection pooling implementation.
• Support for the OCTETS character set
• Array data type support has been improved.
• The FbDataAdapter class now executes insert, update and delete commands using always ExecuteNonQuery calls (by override the Update(DataRow[], DataTableMapping) method, this allows to use sp that didn't return values with the FbDataAdapter)

I will come back soon with practical examples of new features.