Firebird

Firebird backend

This module implements the Firebird backend, thru the kinterbasdb DBAPI module.

Firebird dialects

Firebird offers two distinct dialects (not to be confused with the SA Dialect thing):

dialect 1

This is the old syntax and behaviour, inherited from Interbase pre-6.0.

dialect 3

This is the newer and supported syntax, introduced in Interbase 6.0.

From the user point of view, the biggest change is in date/time handling: under dialect 1, there’s a single kind of field, DATE with a synonim DATETIME, that holds a timestamp value, that is a date with hour, minute, second. Under dialect 3 there are three kinds, a DATE that holds a date, a TIME that holds a time of the day value and a TIMESTAMP, equivalent to the old DATE.

The problem is that the dialect of a Firebird database is a property of the database itself [1] (that is, any single database has been created with one dialect or the other: there is no way to change the after creation). SQLAlchemy has a single instance of the class that controls all the connections to a particular kind of database, so it cannot easily differentiate between the two modes, and in particular it cannot simultaneously talk with two distinct Firebird databases with different dialects.

By default this module is biased toward dialect 3, but you can easily tweak it to handle dialect 1 if needed:

from sqlalchemy import types as sqltypes
from sqlalchemy.databases.firebird import FBDate, colspecs, ischema_names

# Adjust the mapping of the timestamp kind
ischema_names['TIMESTAMP'] = FBDate
colspecs[sqltypes.DateTime] = FBDate,

Other aspects may be version-specific. You can use the server_version_info() method on the FBDialect class to do whatever is needed:

from sqlalchemy.databases.firebird import FBCompiler

if engine.dialect.server_version_info(connection) < (2,0):
    # Change the name of the function ``length`` to use the UDF version
    # instead of ``char_length``
    FBCompiler.LENGTH_FUNCTION_NAME = 'strlen'

Pooling connections

The default strategy used by SQLAlchemy to pool the database connections in particular cases may raise an OperationalError with a message “object XYZ is in use”. This happens on Firebird when there are two connections to the database, one is using, or has used, a particular table and the other tries to drop or alter the same table. To garantee DDL operations success Firebird recommend doing them as the single connected user.

In case your SA application effectively needs to do DDL operations while other connections are active, the following setting may alleviate the problem:

from sqlalchemy import pool
from sqlalchemy.databases.firebird import dialect

# Force SA to use a single connection per thread
dialect.poolclass = pool.SingletonThreadPool

RETURNING support

Firebird 2.0 supports returning a result set from inserts, and 2.1 extends that to deletes and updates.

To use this pass the column/expression list to the firebird_returning parameter when creating the queries:

raises = tbl.update(empl.c.sales > 100, values=dict(salary=empl.c.salary * 1.1),
                    firebird_returning=[empl.c.id, empl.c.salary]).execute().fetchall()

[1]	Well, that is not the whole story, as the client may still ask a different (lower) dialect...