===========
Usage Guide
===========
.. currentModule:: fdb
Driver structure
================
Source code is currently divided into next submodules:
* :mod:`~fdb.ibase` - Python :ref:`ctypes <python:module-ctypes>` interface to Firebird client library.
* :mod:`~fdb.fbcore` - Main driver source code.
* :mod:`~fdb.services` - Driver code to work with Firebird Services.
All important data, functions, classes and constants are available directly in fdb namespace,
so there is not need to import or use :mod:`~fdb.fbcore` and :mod:`~fdb.ibase` submodules directly.
Exception is the :mod:`fdb.services` submodule that contains functions and classes for work with
Firebird Services. Because Services are optional, not so frequently used Firebird facility, all
service-related code was isolated in separate submodule rather than exposed directly through
main module namespace. Because :mod:`~fdb.services` submodule contains names also used by main
driver (:func:`~fdb.services.connect`, :class:`~fdb.services.Connection`), it's advised to use
fully qualified names when refering to them instead importing them via `from fdb.services import ...`.
.. index:: Database
Databases
=========
Access to the database is made available through :class:`Connection` objects. FDB provides two
constructors for these:
* :func:`connect` - Returns `Connection` to database that already exists.
* :func:`create_database` - Returns `Connection` to newly created database.
.. index::
pair: Database; connect
Using `connect`
---------------
This constructor has number of keyword parameters that could be divided into several groups:
* Database specification (parameters `dsn`, `host`, `database` and `port`)
* User specification (parameters `user`, `password` and `role`)
* Connection options (parameters `sql_dialect`, `charset`, `isolation_level`, `buffers`,
`force_writes`, `no_reserve` and `db_key_scope`)
To establish a connection to database, you always must specify the database, either as `connection
string` parameter `dsn`, or as required combination of parameters `host`, `database` and `port`.
.. important::
Current driver version ignores the value of `port` parameter. If you need to specify the port
number, you have to use `dsn` parameter instead.
Although specification of `user` and `password` parameters is optional (if environment variables
`ISC_USER` and `ISC_PASSWORD` are set, their values are used if these parameters are ommited),
it's recommended practice to use them. Parameter `role` is needed only when you use Firebird roles.
Connection options are optional (see `Firebird Documentation`_ for details). However you may often
want to specify `charset`, as it directs automatic conversions of string data between client and
server, and automatic conversions from/to unicode performed by FDB driver (see `Data handling
and conversions`_ for details).
**Examples:**
.. code-block:: python
# Connecting via 'dsn'
#
# Local database (local protocol, if supported)
con = fdb.connect(dsn='/path/database.fdb', user='sysdba', password='pass')
# Local database (TCP/IP)
con = fdb.connect(dsn='localhost:/path/database.fdb', user='sysdba', password='pass')
# Local database (TCP/IP with port specification)
con = fdb.connect(dsn='localhost/3050:/path/database.fdb', user='sysdba', password='pass')
# Remote database
con = fdb.connect(dsn='host:/path/database.db', user='sysdba', password='pass')
# Remote database with port specification
con = fdb.connect(dsn='host/3050:/path/database.db', user='sysdba', password='pass')
#
# Connecting via 'database', 'host' and 'port'
#
# Local database (local protocol, if supported)
con = fdb.connect(database='/path/database.db', user='sysdba', password='pass')
# Local database (TCP/IP)
con = fdb.connect(host='localhost', database='/path/database.db', user='sysdba', password='pass')
# Local database (TCP/IP with port specification)
con = fdb.connect(host='localhost', port=3050, database='/path/database.db', user='sysdba', password='pass')
# Remote database
con = fdb.connect(host='myhost', database='/path/database.db', user='sysdba', password='pass')
.. index::
pair: Database; create
Using `create_database`
-----------------------
The Firebird engine supports dynamic database creation via the SQL statement `CREATE DATABASE`.
FDB wraps it into :func:`create_database`, that returns `Connection` instance attached to newly
created database.
**Example:**
.. code-block:: python
con = fdb.create_database("create database 'host:/temp/db.db' user 'sysdba' password 'pass'")
.. index::
pair: Database; delete
Deleting databases
------------------
The Firebird engine also supports dropping (deleting) databases dynamically, but dropping is a more
complicated operation than creating, for several reasons: an existing database may be in use by users
other than the one who requests the deletion, it may have supporting objects such as temporary sort
files, and it may even have dependent shadow databases. Although the database engine recognizes a
`DROP DATABASE` SQL statement, support for that statement is limited to the `isql` command-line
administration utility. However, the engine supports the deletion of databases via an API call, which
FDB exposes as :meth:`~Connection.drop_database` method in :class:`Connection` class. So, to drop
a database you need to connect to it first.
**Examples:**
.. code-block:: python
import fdb
con = fdb.create_database("create database '/temp/db.db' user 'sysdba' password 'pass'")
con.drop_database()
con = fdb.connect(dsn='/path/database.fdb', user='sysdba', password='pass')
con.drop_database()
.. index::
pair: Connection; usage
Connection object
-----------------
:class:`Connection` object represents a direct link to database, and works as gateway for next operations
with it:
* `Executing SQL Statements`_: methods :meth:`~Connection.execute_immediate` and :meth:`~Connection.cursor`.
* Dropping database: method :meth:`~Connection.drop_database`.
* `Trasanction management`_: methods :meth:`~Connection.begin`, :meth:`~Connection.commit`,
:meth:`~Connection.rollback`, :meth:`~Connection.savepoint`, :meth:`~Connection.trans`,
:meth:`~Connection.trans_info` and :meth:`~Connection.transaction_info`, and attributes
:attr:`~Connection.main_transaction`, :attr:`~Connection.transactions`, :attr:`~Connection.default_tpb`
and :attr:`~Connection.group`.
* Work with `Database Events`_: method :meth:`~Connection.event_conduit`.
* Getting information about database: methods :meth:`~Connection.db_info` and
:meth:`~Connection.database_info`.
* Getting information about Firebird version: attribute :attr:`~Connection.server_version`.
.. index::
pair: Database; information about
Getting information about database
----------------------------------
Firebird provides various informations about server and connected database via `database_info` API call.
FDB surfaces this API through methods :meth:`~Connection.db_info` and :meth:`~Connection.database_info`
on Connection object.
:meth:`Connection.database_info` is a `very thin` wrapper around function `isc_database_info()`.
This method does not attempt to interpret its results except with regard to whether they are a string
or an integer. For example, requesting `isc_info_user_names` with the call::
con.database_info(fdb.isc_info_user_names, 's')
will return a binary string containing a raw succession of length-name pairs.
**Example program:**
.. code-block:: python
import fdb
con = fdb.connect(dsn='localhost:/temp/test.db', user='sysdba', password='pass')
# Retrieving an integer info item is quite simple.
bytesInUse = con.database_info(fdb.isc_info_current_memory, 'i')
print 'The server is currently using %d bytes of memory.' % bytesInUse
# Retrieving a string info item is somewhat more involved, because the
# information is returned in a raw binary buffer that must be parsed
# according to the rules defined in the Interbase® 6 API Guide section
# entitled "Requesting buffer items and result buffer values" (page 51).
#
# Often, the buffer contains a succession of length-string pairs
# (one byte telling the length of s, followed by s itself).
# Function fdb.ibase.ord2 is provided to convert a raw
# byte to a Python integer (see examples below).
buf = con.database_info(fdb.isc_info_db_id, 's')
# Parse the filename from the buffer.
beginningOfFilename = 2
# The second byte in the buffer contains the size of the database filename
# in bytes.
lengthOfFilename = fdb.ibase.ord2(buf[1])
filename = buf[beginningOfFilename:beginningOfFilename + lengthOfFilename]
# Parse the host name from the buffer.
beginningOfHostName = (beginningOfFilename + lengthOfFilename) + 1
# The first byte after the end of the database filename contains the size
# of the host name in bytes.
lengthOfHostName = fdb.ibase.ord2(buf[beginningOfHostName - 1])
host = buf[beginningOfHostName:beginningOfHostName + lengthOfHostName]
print 'We are connected to the database at %s on host %s.' % (filename, host)
Sample output::
The server is currently using 8931328 bytes of memory.
We are connected to the database at C:\TEMP\TEST.DB on host WEASEL.
A more convenient way to access the same functionality is via the :meth:`~Connection.db_info` method,
which is high-level convenience wrapper around the `database_info()` method that parses the output of
database_info into Python-friendly objects instead of returning raw binary buffers in the case of complex
result types. For example, requesting `isc_info_user_names` with the call::
con.db_info(fdb.isc_info_user_names)
returns a dictionary that maps (username -> number of open connections). If SYSDBA has one open connection
to the database to which `con` is connected, and TEST_USER_1 has three open connections to that same database,
the return value would be::
{âSYSDBAâ: 1, âTEST_USER_1â: 3}
**Example program:**
.. code-block:: python
import fdb
import os.path
###############################################################################
# Querying an isc_info_* item that has a complex result:
###############################################################################
# Establish three connections to the test database as TEST_USER_1, and one
# connection as SYSDBA. Then use the Connection.db_info method to query the
# number of attachments by each user to the test database.
testUserCons = []
for i in range(3):
tcon = fdb.connect(dsn='localhost:/temp/test.db', user='TEST_USER_1', password='pass')
testUserCons.append(tcon)
con = fdb.connect(dsn='localhost:/temp/test.db', user='sysdba', password='pass')
print 'Open connections to this database:'
print con.db_info(fdb.isc_info_user_names)
###############################################################################
# Querying multiple isc_info_* items at once:
###############################################################################
# Request multiple db_info items at once, specifically the page size of the
# database and the number of pages currently allocated. Compare the size
# computed by that method with the size reported by the file system.
# The advantages of using db_info instead of the file system to compute
# database size are:
# - db_info works seamlessly on connections to remote databases that reside
# in file systems to which the client program lacks access.
# - If the database is split across multiple files, db_info includes all of
# them.
res = con.db_info([fdb.isc_info_page_size, fdb.isc_info_allocation])
pagesAllocated = res[fdb.isc_info_allocation]
pageSize = res[fdb.isc_info_page_size]
print '\ndb_info indicates database size is', pageSize * pagesAllocated, 'bytes'
print 'os.path.getsize indicates size is ', os.path.getsize(DB_FILENAME), 'bytes'
Sample output::
Open connections to this database:
{'SYSDBA': 1, 'TEST_USER_1': 3}
db_info indicates database size is 20684800 bytes
os.path.getsize indicates size is 20684800 bytes
.. index:: SQL Statement
Executing SQL Statements
========================
FDB implements two ways for execution of SQL commands against connected database:
* :meth:`~Connection.execute_immediate` - for execution of SQL commands that don't
return any result.
* :class:`Cursor` objects that offer rich interface for execution of SQL commands and
fetching their results.
.. index::
pair: Cursor; usage
Cursor object
-------------
Because `Cursor` objects always operate in context of single :class:`Connection` (and :class:`Transaction`),
`Cursor` instances are not created directly, but by constructor method. Python DB API 2.0 assume that
if database engine supports transactions, it supports only one transaction per connection, hence it
defines constructor method :meth:`~Connection.cursor` (and other transaction-related methods) as part
of :class:`Connection` interface. However, Firebird supports multiple independent transactions per
connection. To conform to Python DB API, FDB uses concept of internal :attr:`~Connection.main_transaction`
and secondary :attr:`~Connection.transactions`. Cursor constructor is primarily defined by
:class:`Transaction`, and Cursor constructor on `Connection` is therefore a shortcut for `main_transaction.cursor()`.
`Cursor` objects are used for next operations:
* Execution of SQL Statemets: methods :meth:`~Cursor.execute`, :meth:`~Cursor.executemany` and
:meth:`~Cursor.callproc`.
* Creating :class:`PreparedStatement` objects for efficient repeated execution of SQL statements, and
to obtain additional information about SQL statements (like execution :attr:`~PreparedStatement.plan`):
method :meth:`~Cursor.prep`.
* `Fetching results <Fetching data from server>`_: methods :meth:`~Cursor.fetchone`, :meth:`~Cursor.fetchmany`, :meth:`~Cursor.fetchall`,
:meth:`~Cursor.fetchonemap`, :meth:`~Cursor.fetchmanymap`, :meth:`~Cursor.fetchallmap`,
:meth:`~Cursor.iter`, :meth:`~Cursor.itermap` and :meth:`~Cursor.next`.
.. index::
pair: SQL Statement; execution
SQL Execution Basics
--------------------
There are three methods how to execute SQL commands:
* :meth:`Connection.execute_immediate` or :meth:`Transaction.execute_immediate` for SQL commands that
don't return any result, and are not executed frequently. This method also **doesn't** support either
`parametrized statements`_ or `prepared statements`_.
.. tip::
This method is efficient for `administrative` and `DDL`_ SQL commands, like `DROP`, `CREATE` or `ALTER`
commands, `SET STATISTICS` etc.
* :meth:`Cursor.execute` or :meth:`Cursor.executemany` for commands that return result sets, i.e. sequence
of `rows` of the same structure, and sequence has unknown number of `rows` (including zero).
.. tip::
This method is preferred for all `SELECT` and other `DML`_ statements, or any statement that is executed
frequently, either `as is` or in `parametrized` form.
* :meth:`Cursor.callproc` for execution of `Stored procedures` that always return exactly one set of values.
.. note::
This method of SP invocation is equivalent to `"EXECUTE PROCEDURE ..."` SQL statement.
.. index::
pair: SQL Statement; parametrized
.. _parametrized-statements:
Parametrized statements
-----------------------
When SQL command you want to execute contains data `values`, you can either:
* Embed them `directly` or via `string formatting` into command `string`, e.g.:
.. code-block:: python
cur.execute("insert into the_table (a,b,c) values ('aardvark', 1, 0.1)")
# or
cur.execute("select * from the_table where col == 'aardvark'")
# or
cur.execute("insert into the_table (a,b,c) values ('%s', %i, %f)" % ('aardvark',1,0.1))
# or
cur.execute("select * from the_table where col == '%s'" % 'aardvark')
* Use parameter marker (`?`) in command `string` in the slots where values are expected, then supply
those values as Python list or tuple:
.. code-block:: python
cur.execute("insert into the_table (a,b,c) values (?,?,?)", ('aardvark', 1, 0.1))
# or
cur.execute("select * from the_table where col == ?",('aardvark',))
While both methods have the same results, the second one (called `parametrized`) has several important
advantages:
* You don't need to handle conversions from Python data types to strings.
* FDB will handle all data type conversions (if necessary) from Python data types to Firebird ones,
including `None/NULL` conversion and conversion from `unicode` to `byte strings` in encoding expected
by server.
* You may pass BLOB values as open `file-like` objects, and FDB will handle the transfer of BLOB value.
* If you'll pass exactly the same command `string` again to particular :class:`Cursor` instance,
it will be executed more efficiently (see section about `Prepared Statements`_ for details).
Parametrized statemets also have some limitations. Currently:
* `DATE`, `TIME` and `DATETIME` values must be relevant :mod:`datetime` objects.
* `NUMERIC` and `DECIMAL` values must be :mod:`decimal` objects.
.. index::
pair: Cursor; fetching data
Fetching data from server
-------------------------
Result of SQL statement execution consists from sequence of zero to unknown number of `rows`, where each
`row` is a set of exactly the same number of values. :class:`Cursor` object offer number of different
methods for fetching these `rows`, that should satisfy all your specific needs:
* :meth:`~Cursor.fetchone` - Returns the next row of a query result set, or `None` when no more data is
available.
.. tip::
Cursor supports the :ref:`iterator protocol <python:typeiter>`, yielding tuples of values like
:meth:`~Cursor.fetchone`.
* :meth:`~Cursor.fetchmany` - Returns the next set of rows of a query result, returning a sequence of
sequences (e.g. a list of tuples). An empty sequence is returned when no more rows are available.
The number of rows to fetch per call is specified by the parameter. If it is not given, the cursorâs
:attr:`~Cursor.arraysize` determines the number of rows to be fetched. The method does try to fetch
as many rows as indicated by the size parameter. If this is not possible due to the specified number
of rows not being available, fewer rows may be returned.
.. note::
The default value of :attr:`~Cursor.arraysize` is `1`, so without paremeter it's equivalent to
:meth:`~Cursor.fetchone`, but returns list of `rows`, instead actual `row` directly.
* :meth:`~Cursor.fetchall` - Returns all (remaining) rows of a query result as list of tuples, where
each tuple is one row of returned values.
.. tip::
This method can potentially return huge amount of data, that may exhaust available memory.
If you need just `iteration` over potentially big result set, use loops with :meth:`~Cursor.fetchone`,
Cursor's built-in support for :ref:`iterator protocol <python:typeiter>` or call to :meth:`~Cursor.iter`
instead this method.
* :meth:`~Cursor.fetchonemap` - Returns the next row like :meth:`~Cursor.fetchone`, but returns a mapping
of `field name` to `field value`, rather than a tuple.
* :meth:`~Cursor.fetchmanymap` - Returns the next set of rows of a query result like :meth:`~Cursor.fetchmany`,
but returns a list of mapping of `field name` to `field value`, rather than a tuple.
* :meth:`~Cursor.fetchallmap` - Returns all (remaining) rows of a query result like :meth:`~Cursor.fetchall`,
returns a list of mappings of `field name` to `field value`, rather than a tuple.
.. tip::
This method can potentially return huge amount of data, that may exhaust available memory.
If you need just `iteration` over potentially big result set with mapping support, use
:meth:`~Cursor.itermap` instead this method.
* :meth:`~Cursor.iter` - Equivalent to the :meth:`~Cursor.fetchall`, except that it returns :ref:`iterator
<python:typeiter>` rather than materialized list.
* :meth:`~Cursor.itermap` - Equivalent to the :meth:`~Cursor.fetchallmap`, except that it returns
:ref:`iterator <python:typeiter>` rather than materialized list.
* Call to :meth:`~Cursor.execute` returns self (Cursor instance) that itself supports
the :ref:`iterator protocol <python:typeiter>`, yielding tuples of values like :meth:`~Cursor.fetchone`.
.. important::
FDB makes absolutely no guarantees about the return value of the `fetchone` / `fetchmany` / `fetchall`
methods except that it is a sequence indexed by field position. FDB makes absolutely no guarantees about
the return value of the `fetchonemap` / `fetchmanymap` / `fetchallmap` methods except that it is a mapping
of field name to field value. Therefore, client programmers should not rely on the return value being
an instance of a particular class or type.
**Examples:**
.. code-block:: python
import fdb
con = fdb.connect(dsn='/temp/test.db', user='sysdba', password='masterkey')
cur = con.cursor()
SELECT = "select name, year_released from languages order by year_released"
# 1. Using built-in support for iteration protocol to iterate over the rows available from the cursor,
# unpacking the resulting sequences to yield their elements (name, year_released):
cur.execute(SELECT)
for (name, year_released) in cur:
print '%s has been publicly available since %d.' % (name, year_released)
# 2. Equivalently using fetchall():
# This is potentially dangerous if result set is huge, as the whole result set is first materialized
# as list and then used for iteration.
cur.execute(SELECT)
for row in cur.fetchall():
print '%s has been publicly available since %d.' % (row[0], row[1])
# 3. Using mapping-iteration rather than sequence-iteration:
cur.execute(SELECT)
for row in cur.itermap():
print '%(name)s has been publicly available since %(year_released)d.' % row
.. index::
pair: SQL Statement; prepared
.. _prepared-statements:
Prepared Statements
-------------------
Execution of any SQL statement has three phases:
* *Preparation*: command is analyzed, validated, execution plan is determined by optimizer and all
necessary data structures (for example for input and output parameters) are initialized.
* *Execution*: input parameters (if any) are passed to server and previously prepared statement is
actually executed by database engine.
* *Fetching*: result of execution and data (if any) are transfered from server to client, and
allocated resources are then released.
The preparation phase consumes some amount of server resources (memory and CPU). Although preparation
and release of resources typically takes only small amount of CPU time, it builds up as number
of executed statements grows. Firebird (like most database engines) allows to spare this time for
subsequent execution if particular statement should be executed repeatedly - by reusing once prepared
statement for repeated execution. This may save significant amount of server processing time, and better
overall performance.
FDB builds on this by encapsulating all statement-related code into separate :class:`PreparedStatement`
class, and implementing :class:`Cursor` class as a wrapper around it. Once executed statement has to
be disposed in favour of new one, the `PreparedStatement` instance is stored into `Cursor's` internal cache.
Whenever SQL statement execution is requested, `Cursor` checks whether appropriate `PreparedStatement`
isn't in this cache, and if it is, uses it instead creating new one. Because `identity` of SQL statement
is defined by its `command string`, this mechanism works best for SQL commands that either are `static`
or `parametrized <Parametrized statements>`_.
**Example:**
.. code-block:: python
insertStatement = "insert into the_table (a,b,c) values (?,?,?)"
# First execution initializes the PreparedStatement
cur.execute(insertStatement, ('aardvark', 1, 0.1))
# This execution reuses PreparedStatement from cache
cur.execute(insertStatement, ('zymurgy', 2147483647, 99999.999))
# And this one too
cur.execute("insert into the_table (a,b,c) values (?,?,?)", ('foobar', 2000, 9.9))
Additionally to automatic, implicit reuse of prepared statements, `Cursor` also allows to aquire and use
`PreparedStatement` instances explicitly. `PreparedStatement` aquired by calling :meth:`~Cursor.prep`
method could be then passed to :meth:`~Cursor.execute` or :meth:`~Cursor.executemany` instead `command
string`.
**Example:**
.. code-block:: python
insertStatement = cur.prep("insert into the_table (a,b,c) values (?,?,?)")
inputRows = [
('aardvark', 1, 0.1),
('zymurgy', 2147483647, 99999.999),
('foobar', 2000, 9.9)
]
for row in inputRows:
cur.execute(insertStatement,row)
#
# or you can use executemany
#
cur.executemany(insertStatement, inputRows)
While both implicit and explicit prepared statements could be used to the same effect, there are
significant differences between both methods:
* **Implicit** prepared statements remain in the cache until `Cursor` instance is disposed,
preventing server-side resources allocated for the statement to be fully released. If it may
become a problem, it's necessary to release the `Cursor` instance, or call its :meth:`~Cursor.clear_cache`
method.
.. note::
Because `Cursor` is just a wrapper around :class:`PreparedStatement` instance(s), calling
:meth:`~Cursor.close` doesn't render the `Cursor` instance unusable (unlike other objects like
:class:`Connection` or :class:`Transaction`). It just closes the current prepared statement, but
all prepared statements (current and all in internal cache) are still available for use, and `Cursor`
itself is still bound to :class:`Connection` and :class:`Transaction`, and could be still used
to execute SQL statements.
.. warning::
FDB's implementation of Cursor somewhat violates the Python DB API 2.0, which requires that cursor
will be unusable after call to `close`; and an Error (or subclass) exception should be raised if
any operation is attempted with the cursor.
If you'll take advantage of this `anomaly`, your code would be less portable to other Python DB
API 2.0 compliant drivers.
* **Explicit** prepared statements are never stored in `Cursor's` cache, but they are still bound to
`Cursor` instance that created them, and can't be used with any other `Cursor` instance. Beside
repeated execution they are also useful to get information about statement (like its output
:attr:`~PreparedStatement.description`, execution :attr:`~PreparedStatement.plan` or
:attr:`~PreparedStatement.statement_type`) before its execution.
**Example Program:**
The following program demonstrates the explicit use of `PreparedStatements`. It also benchmarks explicit
`PreparedStatement` reuse against FDBâs automatic `PreparedStatement` reuse, and against an input strategy
that prevents `PreparedStatement` reuse.
.. code-block:: python
import time
import fdb
con = fdb.connect(dsn='localhost:employee',
user='sysdba', password='masterkey'
)
cur = con.cursor()
# Create supporting database entities:
cur.execute("recreate table t (a int, b varchar(50))")
con.commit()
cur.execute("create unique index unique_t_a on t(a)")
con.commit()
# Explicitly prepare the insert statement:
psIns = cur.prep("insert into t (a,b) values (?,?)")
print 'psIns.sql: "%s"' % psIns.sql
print 'psIns.statement_type == fdb.isc_info_sql_stmt_insert:', (
psIns.statement_type == fdb.isc_info_sql_stmt_insert
)
print 'psIns.n_input_params: %d' % psIns.n_input_params
print 'psIns.n_output_params: %d' % psIns.n_output_params
print 'psIns.plan: %s' % psIns.plan
print
N = 50000
iStart = 0
# The client programmer uses a PreparedStatement explicitly:
startTime = time.time()
for i in xrange(iStart, iStart + N):
cur.execute(psIns, (i, str(i)))
print (
'With explicit prepared statement, performed'
'\n %0.2f insertions per second.' % (N / (time.time() - startTime))
)
con.commit()
iStart += N
# FDB automatically uses a PreparedStatement "under the hood":
startTime = time.time()
for i in xrange(iStart, iStart + N):
cur.execute("insert into t (a,b) values (?,?)", (i, str(i)))
print (
'With implicit prepared statement, performed'
'\n %0.2f insertions per second.' % (N / (time.time() - startTime))
)
con.commit()
iStart += N
# A new SQL string containing the inputs is submitted every time, so
# FDB is not able to implicitly reuse a PreparedStatement. Also, in a
# more complicated scenario where the end user supplied the string input
# values, the program would risk SQL injection attacks:
startTime = time.time()
for i in xrange(iStart, iStart + N):
cur.execute("insert into t (a,b) values (%d,'%s')" % (i, str(i)))
print (
'When unable to reuse prepared statement, performed'
'\n %0.2f insertions per second.' % (N / (time.time() - startTime))
)
con.commit()
# Prepare a SELECT statement and examine its properties. The optimizer's plan
# should use the unique index that we created at the beginning of this program.
print
psSel = cur.prep("select * from t where a = ?")
print 'psSel.sql: "%s"' % psSel.sql
print 'psSel.statement_type == fdb.isc_info_sql_stmt_select:', (
psSel.statement_type == fdb.isc_info_sql_stmt_select
)
print 'psSel.n_input_params: %d' % psSel.n_input_params
print 'psSel.n_output_params: %d' % psSel.n_output_params
print 'psSel.plan: %s' % psSel.plan
# The current implementation does not allow PreparedStatements to be prepared
# on one Cursor and executed on another:
print
print 'Note that PreparedStatements are not transferrable from one cursor to another:'
cur2 = con.cursor()
cur2.execute(psSel)
Sample output::
psIns.sql: "insert into t (a,b) values (?,?)"
psIns.statement_type == fdb.isc_info_sql_stmt_insert: True
psIns.n_input_params: 2
psIns.n_output_params: 0
psIns.plan: None
With explicit prepared statement, performed
4276.00 insertions per second.
With implicit prepared statement, performed
4185.09 insertions per second.
When unable to reuse prepared statement, performed
2037.70 insertions per second.
psSel.sql: "select * from t where a = ?"
psSel.statement_type == fdb.isc_info_sql_stmt_select: True
psSel.n_input_params: 1
psSel.n_output_params: 2
psSel.plan: PLAN (T INDEX (UNIQUE_T_A))
Note that PreparedStatements are not transferrable from one cursor to another:
Traceback (most recent call last):
File "pstest.py", line 85, in <module>
cur2.execute(psSel)
File "/home/job/python/envs/pyfirebird/fdb/fdb/fbcore.py", line 2623, in execute
raise ValueError("PreparedStatement was created by different Cursor.")
ValueError: PreparedStatement was created by different Cursor.
As you can see, the version that prevents the reuse of prepared statements is about two times slower
â *for a trivial statement*. In a real application, SQL statements are likely to be far more complicated,
so the speed advantage of using prepared statements would only increase.
As the timings indicate, FDB does a good job of reusing prepared statements even if the client program
is written in a style strictly compatible with the Python DB API 2.0 (which accepts only `strings` â
not :class:`PreparedStatement` objects â to the :meth:`Cursor.execute` method). The performance loss
in this case is about one percent.
.. index::
pair: Cursor; named
Named Cursors
-------------
To allow the Python programmer to perform scrolling `UPDATE` or `DELETE` via the `âSELECT ... FOR UPDATEâ`
syntax, FDB provides the read/write property :attr:`Cursor.name`.
**Example Program:**
.. code-block:: python
import fdb
con = fdb.connect(dsn='localhost:/temp/test.db', user='sysdba', password='pass')
curScroll = con.cursor()
curUpdate = con.cursor()
curScroll.execute("select city from addresses for update")
curScroll.name = 'city_scroller'
update = "update addresses set city=? where current of " + curScroll.name
for (city,) in curScroll:
city = ... # make some changes to city
curUpdate.execute( update, (city,) )
con.commit()
.. index::
pair: Stored procedure; execution
Working with stored procedures
------------------------------
Firebird stored procedures can have `input` parameters and/or `output` parameters. Some databases support
`input/output` parameters, where the same parameter is used for both input and output; Firebird does not
support this.
It is important to distinguish between procedures that `return a result set` and procedures that `populate
and return their output parameters` exactly once. Conceptually, the latter âreturn their output parametersâ
like a Python function, whereas the former âyield result rowsâ like a Python generator.
Firebirdâs `server-side` procedural SQL syntax makes no such distinction, but client-side SQL code (and C
API code) must. A result set is retrieved from a stored procedure by `SELECT'ing from the procedure,
whereas output parameters are retrieved with an 'EXECUTE PROCEDURE' statement`.
To **retrieve a result set** from a stored procedure with FDB, use code such as this:
.. code-block:: python
cur.execute("select output1, output2 from the_proc(?, ?)", (input1, input2))
# Ordinary fetch code here, such as:
for row in cur:
... # process row
con.commit() # If the procedure had any side effects, commit them.
To **execute** a stored procedure and **access its output parameters**, use code such as this:
.. code-block:: python
cur.callproc("the_proc", (input1, input2))
# If there are output parameters, retrieve them as though they were the
# first row of a result set. For example:
outputParams = cur.fetchone()
con.commit() # If the procedure had any side effects, commit them.
This latter is not very elegant; it would be preferable to access the procedureâs output parameters as
the return value of :meth:`Cursor.callproc`. The Python DB API specification requires the current behavior,
however.
.. index::
pair: Data; conversion
Data handling and conversions
=============================
.. index::
pair: parameter; conversion
Implicit Conversion of Input Parameters from Strings
----------------------------------------------------
The database engine treats most SQL data types in a weakly typed fashion: the engine may attempt to convert
the raw value to a different type, as appropriate for the current context. For instance, the SQL expressions
`123` (integer) and `â123â` (string) are treated equivalently when the value is to be inserted into
an `integer` field; the same applies when `â123â` and `123` are to be inserted into a `varchar` field.
This weak typing model is quite unlike Pythonâs dynamic yet strong typing. Although weak typing is regarded
with suspicion by most experienced Python programmers, the database engine is in certain situations so
aggressive about its typing model that KInterbasDB must compromise in order to remain an elegant means
of programming the database engine.
An example is the handling of âmagic valuesâ for date and time fields. The database engine interprets certain
string values such as `âyesterdayâ` and `ânowâ` as having special meaning in a date/time context. If FDB did
not accept strings as the values of parameters destined for storage in date/time fields, the resulting code
would be awkward. Consider the difference between the two Python snippets below, which insert a row containing
an integer and a timestamp into a table defined with the following DDL statement:
.. code-block:: sql
create table test_table (i integer, t timestamp)
.. code-block:: python
i = 1
t = 'now'
sqlWithMagicValues = "insert into test_table (i, t) values (?, '%s')" % t
cur.execute( sqlWithMagicValues, (i,) )
.. code-block:: python
i = 1
t = 'now'
cur.execute( "insert into test_table (i, t) values (?, ?)", (i, t) )
If FDB did not support weak parameter typing, string parameters that the database engine is to interpret
as âmagic valuesâ would have to be rolled into the SQL statement in a separate operation from the binding
of the rest of the parameters, as in the first Python snippet above. Implicit conversion of parameter values
from strings allows the consistency evident in the second snippet, which is both more readable and more
general.
It should be noted that FDB does not perform the conversion from string itself. Instead, it passes that
responsibility to the database engine by changing the parameter metadata structure dynamically at the last
moment, then restoring the original state of the metadata structure after the database engine has performed
the conversion.
A secondary benefit is that when one uses FDB to import large amounts of data from flat files into
the database, the incoming values need not necessarily be converted to their proper Python types before
being passed to the database engine. Eliminating this intermediate step may accelerate the import process
considerably, although other factors such as the chosen connection protocol and the deactivation of indexes
during the import are more consequential. For bulk import tasks, the database engineâs external tables also
deserve consideration. External tables can be used to suck semi-structured data from flat files directly
into the relational database without the intervention of an ad hoc conversion program.
.. index::
pair: unicode; conversion
Automatic conversion from/to unicode
------------------------------------
In Firebird, every `CHAR`, `VARCHAR` or textual `BLOB` field can (or, better: must) have a `character set`
assigned. While it's possible to define single character set for whole database, it's also possible to
define different character set for each textual field. This information is used to correctly store the bytes
that make up the character string, and together with collation information (that defines the sort ordering
and uppercase conversions for a string) is vital for correct data manupulation, including automatic
transliteration between character sets when necessary.
.. important::
Because data also flow between server and client application, it's vital that client will send data
encoded only in character set(s) that server expects. While it's possible to leave this responsibility
completely on client application, it's better when client and server settle on single character set
they would use for communication, especially when database operates with multiple character sets, or
uses character set that is not `native` for client application.
Character set for communication is specified using `charset` parameter in :func:`connection <connect>` call.
When `connection charset` is defined, all textual data returned from server are encoded in this charset,
and client application must ensure that all textual data sent to server are encoded only in this charset
as well.
FDB helps with client side of this character set bargain by automatically converting `unicode` strings into
`bytes/strings` encoded in connection character set, and vice versa. However, developers are still
responsible that `non-unicode` strings passed to server are in correct encoding (because FDB makes no
assuption about encoding of non-unicode strings, so it can't recode them to connection charset).
.. important::
In case that `connection charset` is NOT defined at all, or `NONE` charset is specified, FDB uses
:func:`locale.getpreferredencoding` to determine encoding for conversions from/to `unicode`.
.. important::
There is one exception to automatic conversion: when character set OCTETS is defined for data column.
Values assigned to OCTETS columns are always passed `as is`, because they're basically binary streams.
This has specific implications regarding Python version you use. Python 2.x `native strings` are `bytes`,
suitable for such binary streams, but Python 3 native strings are `unicode`, and you would probably
want to use `bytes` type instead. However, FDB in this case doesn't check the value type at all, so
you'll not be warned if you'll make a mistake and pass `unicode` to OCTETS column (unless you'll pass
more bytes than column may hold, or you intend to store unicode that way).
Rules for automatic conversion depend on Python version you use:
* Native Python 2.x `strings` are passed to server as is, and developers must explicitly use `unicode`
strings to take advantage of automatic conversion. String values coming from server are converted to
`unicode` **only**:
* for data stored in database (i.e. not for string values returned by Firebird Service and `info`
calls etc.).
* when `connection charset` is specified.
* Native Python 3 strings are `unicode`, so conversion is fully automatic in both directions for all
textual data, i.e. including for string values returned by Firebird Service and `info` calls etc.
When `connection charset` is not specified, FDB uses :func:`locale.getpreferredencoding` to determine
encoding for conversions from/to `unicode`.
.. tip::
Except for legacy databases that doesn't have `character set` defined, **always** define character
set for your databases and specify `connection charset`. It will make your life much easier.
.. index:: BLOB
pair: Data; BLOB
.. _working_with_blobs:
Working with BLOBs
------------------
FDB uses two types of BLOB values:
* **Materialized** BLOB values are Python strings. This is the **default** type.
* **Streamed** BLOB values are `file-like` objects.
Materialized BLOBs are easy to work with, but are not suitable for:
* **deferred loading** of BLOBs. They're called `materialized` because they're always fetched from server
as part of row fetch. Fetching BLOB value means separate API calls (and network roundtrips), which may
slow down you application considerably.
* **large values**, as they are always stored in memory in full size.
These drawbacks are addressed by `stream` BLOBs. Using BLOBs in `stream` mode is easy:
* For **input** values, simply use :ref:`parametrized statement <parametrized-statements>` and pass any
`file-like` object in place of BLOB parameter. The `file-like` object must implement only the
:meth:`~file.read` method, as no other metod is used.
* For **output** values, you have to call :meth:`Cursor.set_stream_blob` (or :meth:`PreparedStatement.set_stream_blob`)
method with specification of column name(s) that should be returned as `file-like` objects. FDB then
returns :class:`BlobReader` instance instead string in place of returned BLOB value for these column(s).
The :class:`BlobReader` instance is bound to particular BLOB value returned by server, so its life time
is limited. The actual BLOB value is not opened initially, so no additonal API calls to server are made
if you'll decide to ignore the value completely. You also don't need to open the BLOB value explicitly,
as BLOB is opened automatically on first call to :meth:`~BlobReader.next`, :meth:`~BlobReader.read`,
:meth:`~BlobReader.readline`, :meth:`~BlobReader.readlines` or :meth:`~BlobReader.seek`. However, it's
good practice to :meth:`~BlobReader.close` the reader once you're finished reading, as it's likely that
Python's garbage collector would call the `__del__` method too late, when fetch context is already gone,
and closing the reader would cause an error.
.. warning::
If BLOB was NOT CREATED as `stream` BLOB, calling :meth:`BlobReader.seek` method will raise
:exc:`DatabaseError` exception. **This constraint is set by Firebird.**
.. important::
When working with BLOB values, always have memory efficiency in mind, especially when you're
processing huge quantity of rows with BLOB values at once. Materialized BLOB values may exhaust
your memory quickly, but using stream BLOBs may have inpact on performance too, as new `BlobReader`
instance is created for each value fetched.
**Example program:**
.. code-block:: python
import os.path
from cStringIO import StringIO
import fdb
con = fdb.connect(dsn='localhost:employee',user='sysdba', password='masterkey')
cur = con.cursor()
cur.execute("recreate table blob_test (a blob)")
con.commit()
# --- Materialized mode (str objects for both input and output) ---
# Insertion:
cur.execute("insert into blob_test values (?)", ('abcdef',))
cur.execute("insert into blob_test values (?)", ('ghijklmnop',))
# Retrieval:
cur.execute("select * from blob_test")
print 'Materialized retrieval (as str):'
print cur.fetchall()
cur.execute("delete from blob_test")
# --- Streaming mode (file-like objects for input; fdb.BlobReader objects for output) ---
# Insertion:
cur.execute("insert into blob_test values (?)", (StringIO('abcdef'),))
cur.execute("insert into blob_test values (?)", (StringIO('ghijklmnop'),))
f = file(os.path.abspath(__file__), 'rb')
cur.execute("insert into blob_test values (?)", (f,))
f.close()
# Retrieval using the "file-like" methods of BlobReader:
cur.execute("select * from blob_test")
cur.set_stream_blob('A') # Note the capital letter
readerA = cur.fetchone()[0]
print '\nStreaming retrieval (via fdb.BlobReader):'
# Python "file-like" interface:
print 'readerA.mode: "%s"' % readerA.mode
print 'readerA.closed: %s' % readerA.closed
print 'readerA.tell(): %d' % readerA.tell()
print 'readerA.read(2): "%s"' % readerA.read(2)
print 'readerA.tell(): %d' % readerA.tell()
print 'readerA.read(): "%s"' % readerA.read()
print 'readerA.tell(): %d' % readerA.tell()
print 'readerA.read(): "%s"' % readerA.read()
readerA.close()
print 'readerA.closed: %s' % readerA.closed
Output::
Materialized retrieval (as str):
[('abcdef',), ('ghijklmnop',)]
Streaming retrieval (via fdb.BlobReader):
readerA.mode: "rb"
readerA.closed: False
readerA.tell(): 0
readerA.read(2): "ab"
readerA.tell(): 2
readerA.read(): "cdef"
readerA.tell(): 6
readerA.read(): ""
readerA.closed: True
.. index:: ARRAY
pair: Data; ARRAY
Firebird ARRAY type
-------------------
FDB supports Firebird ARRAY data type. ARRAY values are represented as Python lists. On input, the Python
sequence (list or tuple) must be nested appropriately if the array field is multi-dimensional, and
the incoming sequence must not fall short of its maximum possible length (it will not be âpaddedâ
implicitlyâsee below). On output, the lists will be nested if the database array has multiple dimensions.
.. note::
Database arrays have no place in a purely relational data model, which requires that data values be
atomized (that is, every value stored in the database must be reduced to elementary, non-decomposable
parts). The Firebird implementation of database arrays, like that of most relational database engines
that support this data type, is fraught with limitations.
Database arrays are of fixed size, with a predeclared number of dimensions (max. 16) and number of elements
per dimension. Individual array elements cannot be set to NULL / None, so the mapping between Python lists
(which have dynamic length and are therefore not normally âpaddedâ with dummy values) and non-trivial
database arrays is clumsy.
Stored procedures cannot have array parameters.
Finally, many interface libraries, GUIs, and even the isql command line utility do not support database
arrays.
In general, it is preferable to avoid using database arrays unless you have a compelling reason.
**Example:**
.. code-block:: python
>>> import fdb
>>> con = fdb.connect(dsn='localhost:employee',user='sysdba', password='masterkey')
>>> cur = con.cursor()
>>> cur.execute("select LANGUAGE_REQ from job where job_code='Eng' and job_grade=3 and job_country='Japan'")
>>> cur.fetchone()
(['Japanese\n', 'Mandarin\n', 'English\n', '\n', '\n'],)
**Example program:**
.. code-block:: python
import fdb
con = fdb.connect(dsn='localhost:/temp/test.db', user='sysdba', password='pass')
con.execute_immediate("recreate table array_table (a int[3,4])")
con.commit()
cur = con.cursor()
arrayIn = [
[1, 2, 3, 4],
[5, 6, 7, 8],
[9,10,11,12]
]
print 'arrayIn: %s' % arrayIn
cur.execute("insert into array_table values (?)", (arrayIn,))
cur.execute("select a from array_table")
arrayOut = cur.fetchone()[0]
print 'arrayOut: %s' % arrayOut
con.commit()
Output::
arrayIn: [[1, 2, 3, 4], [5, 6, 7, 8], [9, 10, 11, 12]]
arrayOut: [[1, 2, 3, 4], [5, 6, 7, 8], [9, 10, 11, 12]]
.. index::
pair: Transaction; management
Trasanction management
======================
For the sake of simplicity, FDB lets the Python programmer ignore transaction management to the greatest
extent allowed by the Python Database API Specification 2.0. The specification says, âif the database
supports an auto-commit feature, this must be initially offâ. At a minimum, therefore, it is necessary to
call the commit method of the connection in order to persist any changes made to the database.
Remember that because of ACID, every data manipulation operation in the Firebird database engine takes
place in the context of a transaction, including operations that are conceptually âread-onlyâ, such as
a typical SELECT. The client programmer of FDB establishes a transaction implicitly by using any SQL
execution method, such as :meth:`~Connection.execute_immediate`, :meth:`Cursor.execute()`, or
:meth:`Cursor.callproc`.
Although FDB allows the programmer to pay little attention to transactions, it also exposes the full
complement of the database engineâs advanced transaction control features: `transaction parameters`_,
`retaining transactions`_, `savepoints`_, and `distributed transactions`_.
Basics
------
When it comes to transactions, Python Database API 2.0 specify that :class:`Connection` object has to
respond to the following methods:
:meth:`Connection.commit()`
Commit any pending transaction to the database. Note that if the database supports an auto-commit
feature, this must be initially off. An interface method may be provided to turn it back on. Database
modules that do not support transactions should implement this method with void functionality.
:meth:`Connection.rollback()`
(optional) In case a database does provide transactions this method causes the the database to roll
back to the start of any pending transaction. **Closing a connection without committing the changes
first will cause an implicit rollback to be performed.**
In addition to the implicit transaction initiation required by Python Database API, FDB allows the programmer
to start transactions explicitly via the :meth:`Connection.begin` method. Also :meth:`Connection.savepoint`
method was added to provide support for `Firebird SAVEPOINTs`_.
But Python Database API 2.0 was created with assumption that connection can support only one transactions
per single connection. However, Firebird can support multiple independent transactions that can run
simultaneously within single connection / attachment to the database. This feature is very important,
as applications may require multiple transaction openned simultaneously to perform various tasks,
which would require to open multiple connections and thus consume more resources than necessary.
FDB surfaces this Firebird feature by separating transaction management out from :class:`Connection` into
separate :class:`Transaction` objects. To comply with Python DB API 2.0 requirements, `Connection` object
uses one `Transaction` instance as :attr:`main transaction <Connection.main_transaction>`, and delegates
:meth:`~Connection.begin`, :meth:`~Connection.savepoint`, :meth:`~Connection.commit`,
:meth:`~Connection.rollback`, :meth:`~Connection.trans_info` and :meth:`~Connection.transaction_info`
calls to it.
.. seealso::
More about using multiple transactions with the same connection in separate
:ref:`section <multiple_transactions>`.
**Example:**
.. code-block:: python
import fdb
con = fdb.connect(dsn='localhost:employee',user='sysdba', password='masterkey')
cur = con.cursor()
# Most minimalistic transaction management -> implicit start, only commit() and rollback()
# ========================================================================================
#
# Transaction is started implicitly
cur.execute('insert into country values ('Oz','Crowns')
con.commit() # commits active transaction
# Again, transaction is started implicitly
cur.execute('insert into country values ('Barsoom','XXX')
con.rollback() # rolls back active transaction
cur.execute('insert into country values ('Pellucidar','Shells')
# This will roll back the transaction
# because Python DB API 2.0 requires that closing connection
# with pending transaction must cause an implicit rollback
con.close()
.. index::
pair: Transaction; auto-commit
Auto-commit
-----------
FDB doesn't support `auto-commit` feature directly, but developers may achieve the similar result
using `explicit` transaction start, taking advantage of :attr:`~Transaction.default_action` and its
default value (`commit`).
**Example:**
.. code-block:: python
import fdb
con = fdb.connect(dsn='localhost:employee',user='sysdba', password='masterkey')
cur = con.cursor()
con.begin()
cur.execute('insert into country values ('Oz','Crowns')
con.begin() # commits active transaction and starts new one
cur.execute('insert into country values ('Barsoom','XXX')
con.begin() # commits active transaction and starts new one
cur.execute('insert into country values ('Pellucidar','Shells')
# However, commit is required before connection is closed,
# because Python DB API 2.0 requires that closing connection
# with pending transaction must cause an implicit rollback
con.commit()
con.close()
.. index::
pair: Transaction; parameters
Transaction parameters
----------------------
The database engine offers the client programmer an optional facility called `transaction parameter
buffers` (TPBs) for tweaking the operating characteristics of the transactions he initiates. These
include characteristics such as whether the transaction has read and write access to tables, or
read-only access, and whether or not other simultaneously active transactions can share table access
with the transaction.
Connections have a :attr:`~Connection.default_tpb` attribute that can be changed to set the default
TPB for all transactions subsequently started on the connection. Alternatively, if the programmer
only wants to set the TPB for a single transaction, he can start a transaction explicitly via the
:meth:`~Connection.begin()` method and pass a TPB for that single transaction.
For details about TPB construction, see the `Firebird API documentation`_. In particular, the `ibase.h`
supplied with Firebird contains all possible TPB elements â single bytes that the C API defines as
constants whose names begin with `isc_tpb_`. FDB makes all of those TPB constants available (under
the same names) as module-level constants. A transaction parameter buffer is handled in C as a character
array; FDB requires that TPBs be constructed as Python `strings` (or `bytes` for Python 3). Since
the constants in the `fdb.isc_tpb_*` family are numbers, they can't be simply concatenated to create
a TPB, but you may use utility function **fdb.bs(byte_array)** that accepts sequence of numbers and
returns `string` (P2) or `bytes` (P3).
For example next call returns TPB for typical `READ COMMITED` transaction:
.. code-block:: python
from fdb import *
TPB = bs([isc_tpb_version3,
isc_tpb_write,
isc_tpb_wait,
isc_tpb_read_committed,
isc_tpb_rec_version])
.. warning::
This method requires good knowledge of `tpc_block` structure and proper order of various parameters,
as Firebird engine will raise an error when badly structured block would be used. Also definition of
`table reservation` parameters is uncomfortable as youâll need to mix binary codes with table names
passed as Pascal strings (characters preceded by string length).
FDB provides several predefined TPB's for convenience:
* **ISOLATION_LEVEL_READ_COMMITED**
Read/Write READ COMMITED with `record version` and WAIT option. Isolation level with greatest
concurrent throughput. This is **Default** TPB.
.. tip::
This isolation level is optimal for transactions that write data and doesn't require stable
snapshot of database for their operations (i.e. most operations are limited to individual rows).
* **ISOLATION_LEVEL_READ_COMMITED_LEGACY**
Read/Write READ COMMITED with `NO record version` and WAIT option.
.. warning::
This isolation level emulates RDBMS that use locks instead multiversion control (MVC).
It's not recommended to use it at all, except for legacy applications lazily ported from such
RDBMS to Firebird.
* **ISOLATION_LEVEL_READ_COMMITED_RO**
Like `ISOLATION_LEVEL_READ_COMMITED`, but **Read Only**.
.. tip::
Firebird threats these transactions as `pre-committed`, so they are best option **for long
running transactions that only read data**.
* **ISOLATION_LEVEL_REPEATABLE_READ** or **ISOLATION_LEVEL_SNAPSHOT**
Read/Write SNAPSHOT (concurrency) with WAIT option.
.. tip::
This isolation level is necessary for transactions that process data in bulk, like reporting,
recalculations etc.
* **ISOLATION_LEVEL_SERIALIZABLE** or **ISOLATION_LEVEL_SNAPSHOT_TABLE_STABILITY**
Read/Write SNAPSHOT TABLE STABILITY (consistency) with WAIT option. Like REPEATABLE_READ/SNAPSHOT,
but locks whole tables for writes from other transactions. Isolation level with lowest concurrent
throughput.
.. warning::
Because tables are locked for `protected write` (i.e. no other transaction can write until lock
is released) **at time of first access**, there is a great risk of `deadlock` between transactions.
.. tip::
To prevent deadlocks and increase concurrent throughput it's recommended to use custom TPB's with
`fine-grained table access reservation`.
**Example:**
.. code-block:: python
import fdb
con = fdb.connect(dsn='localhost:employee',user='sysdba', password='masterkey')
cur = con.cursor()
# Start transaction with default_tpb (ISOLATION_LEVEL_READ_COMMITED)
con.begin()
cur.execute('select * from JOB')
com.commit()
# Start using transactions in REPEATABLE READ (SNAPSHOT) isolation
con.default_tpb = fdb.ISOLATION_LEVEL_REPEATABLE_READ
con.begin()
cur.execute('select * from JOB')
com.commit()
# Start THIS transaction as R/O READ COMMITTED
con.begin(fdb.ISOLATION_LEVEL_READ_COMMITED_RO)
cur.execute('select * from JOB')
com.commit()
For cases when predefined transaction parameter blocks are not suitable for your needs, FDB offers utility
class :class:`TPB` for convenient and safe construction of custom `tpb blocks`. Simply create instance
of this class, set member attributes to required values and use either :meth:`rendered <TPB.render>` binary
tpb block or `TPB` instance itself to set `default_tpb` or as paraneter to `begin()`.
**Example:**
.. code-block:: python
import fdb
con = fdb.connect(dsn='localhost:employee',user='sysdba', password='masterkey')
# Use TPB to construct valid transaction parameter block
# from the fdb.isc_tpb_* family.
customTPB = fdb.TPB()
customTPB.isolation_level = fdb.isc_tpb_consistency # SERIALIZABLE
customTPB.table_reservation["MY_TABLE"] = (fdb.isc_tpb_protected, fdb.isc_tpb_lock_write)
# Explicitly start a transaction with the custom TPB:
con.begin(tpb=customTPB)
# For frequent use, it's better to use already assembled version of TPB
customTPB = fdb.TPB()
customTPB.access_mode = fdb.isc_tpb_read # read only
customTPB.isolation_level = fdb.isc_tpb_concurrency # SNAPSHOT
customTPB = customTPB.render() # Create valid block according to current values of member attributes.
for x in range(1000):
con.begin(tpb=customTPB)
If you want to build only `table reservation` part of `tpb` (for example to add to various custom built
parameter blocks), you can use class :class:`TableReservation` instead `TPB`.
.. index::
pair: Transaction; information about
Getting information about transaction
-------------------------------------
:class:`Transaction` object exposes two methods that return infromation about currently managed
active transaction (the same methods are exposed also by :class:`Connection` object for
:attr:`~Connection.main_transaction`):
:meth:`~Transaction.transaction_info` is a very thin wrapper around function `isc_transaction_info()`.
This method does not attempt to interpret its results except with regard to whether they are a string
or an integer.
A more convenient way to access the same functionality is via the :meth:`~Transaction.trans_info` method,
which is high-level convenience wrapper around the `transaction_info` method that parses the output
of `transaction_info` into Python-friendly objects instead of returning raw binary buffers in the case
of complex result types.
**Example program:**
.. code-block:: python
import fdb
con = fdb.connect(dsn='localhost:employee',user='sysdba', password='masterkey')
# Start transaction, so we can get information about it
con.begin()
info = con.trans_info([fdb.isc_info_tra_id, fdb.isc_info_tra_oldest_interesting,
fdb.isc_info_tra_oldest_snapshot, fdb.isc_info_tra_oldest_active,
fdb.isc_info_tra_isolation, fdb.isc_info_tra_access,
fdb.isc_info_tra_lock_timeout])
print info
print "TransactionID:", info[fdb.isc_info_tra_id]
print "Oldest Interesting (OIT):",info[fdb.isc_info_tra_oldest_interesting]
print "Oldest Snapshot:",info[fdb.isc_info_tra_oldest_snapshot]
print "Oldest Active (OAT):",info[fdb.isc_info_tra_oldest_active]
print "Isolation Level:",info[fdb.isc_info_tra_isolation]
print "Access Mode:",info[fdb.isc_info_tra_access]
print "Lock Timeout:",info[fdb.isc_info_tra_lock_timeout]
Output::
{4: 459, 5: 430, 6: 459, 7: 459, 8: (3, 1), 9: 1, 10: -1}
TransactionID: 459
Oldest Interesting (OIT): 430
Oldest Snapshot: 459
Oldest Active (OAT): 459
Isolation Level: (3, 1)
Access Mode: 1
Lock Timeout: -1
.. note::
Isolation level info values are available as FDB constants `isc_info_tra_consistency`,
`isc_info_tra_concurrency` and `isc_info_tra_read_committed`. For `read committed`, a tuple
of two values is returned instead single value, where the second value is record version flag
`isc_info_tra_no_rec_version` or `isc_info_tra_rec_version`.
Access mode values are available as FDB constants `isc_info_tra_readonly` and `isc_info_tra_readwrite`.
.. index::
pair: Transaction; retaining
Retaining transactions
----------------------
The :meth:`~Transaction.commit` and :meth:`~Transaction.rollback` methods accept an optional boolean
parameter `retaining` (**default False**) to indicate whether to recycle the transactional context of
the transaction being resolved by the method call.
If retaining is `True`, the infrastructural support for the transaction active at the time of the method
call will be âretainedâ (efficiently and transparently recycled) after the database server has committed
or rolled back the conceptual transaction.
.. important::
In code that commits or rolls back frequently, âretainingâ the transaction yields considerably better
performance. However, retaining transactions must be used cautiously because they can interfere with
the serverâs ability to garbage collect old record versions. For details about this issue, read the
âGarbageâ section of `this document <http://www.ibphoenix.com/resources/documents/search/doc_21>`_
by Ann Harrison.
For more information about retaining transactions, see `Firebird documentation`.
.. index::
pair: Transaction; SAVEPOINT
Savepoints
----------
Savepoints are named, intermediate control points within an open transaction that can later be rolled
back to, without affecting the preceding work. Multiple savepoints can exist within a single unresolved
transaction, providing âmulti-level undoâ functionality.
Although Firebird savepoints are fully supported from SQL alone via the `SAVEPOINT ânameâ` and
`ROLLBACK TO ânameâ` statements, FDB also exposes savepoints at the Python API level for the sake
of convenience.
Call to method :meth:`~Transaction.savepoint` establishes a savepoint with the specified `name`. To roll back
to a specific savepoint, call the :meth:`~Transaction.rollback` method and provide a value (the name of the
savepoint) for the optional `savepoint` parameter. If the savepoint parameter of `rollback()` is not
specified, the active transaction is cancelled in its entirety, as required by the Python Database API
Specification.
The following program demonstrates savepoint manipulation via the FDB API, rather than raw SQL.
.. code-block:: python
import fdb
con = fdb.connect(dsn='employee', user='sysdba', password='pass')
cur = con.cursor()
cur.execute("recreate table test_savepoints (a integer)")
con.commit()
print 'Before the first savepoint, the contents of the table are:'
cur.execute("select * from test_savepoints")
print ' ', cur.fetchall()
cur.execute("insert into test_savepoints values (?)", [1])
con.savepoint('A')
print 'After savepoint A, the contents of the table are:'
cur.execute("select * from test_savepoints")
print ' ', cur.fetchall()
cur.execute("insert into test_savepoints values (?)", [2])
con.savepoint('B')
print 'After savepoint B, the contents of the table are:'
cur.execute("select * from test_savepoints")
print ' ', cur.fetchall()
cur.execute("insert into test_savepoints values (?)", [3])
con.savepoint('C')
print 'After savepoint C, the contents of the table are:'
cur.execute("select * from test_savepoints")
print ' ', cur.fetchall()
con.rollback(savepoint='A')
print 'After rolling back to savepoint A, the contents of the table are:'
cur.execute("select * from test_savepoints")
print ' ', cur.fetchall()
con.rollback()
print 'After rolling back entirely, the contents of the table are:'
cur.execute("select * from test_savepoints")
print ' ', cur.fetchall()
The output of the example program is shown below::
Before the first savepoint, the contents of the table are:
[]
After savepoint A, the contents of the table are:
[(1,)]
After savepoint B, the contents of the table are:
[(1,), (2,)]
After savepoint C, the contents of the table are:
[(1,), (2,), (3,)]
After rolling back to savepoint A, the contents of the table are:
[(1,)]
After rolling back entirely, the contents of the table are:
[]
.. index::
pair: Transaction; multiple
.. _multiple_transactions:
Using multiple transactions with the same connection
----------------------------------------------------
To use additional transactions that could run simultaneously with
:attr:`main transaction <Connection.main_transaction>` managed by `Connection`, create new
:class:`Transaction` object calling :meth:`Connection.trans` method. If you don't specify the optional
`default_tpb` parameter, this new `Transaction` inherits the :attr:`~Connection.default_tpb` from
`Connection`. Physical transaction is not started when `Transaction` instance is created, but implicitly
when first SQL statement is executed, or explicitly via :meth:`Transaction.begin` call.
To execute statements in context of this additional transaction you have to use `cursors` obtained directly
from this `Transaction` instance calling its :meth:`~Transaction.cursor` method, or call
:meth:`Transaction.execute_immediate` method.
**Example:**
.. code-block:: python
import fdb
con = fdb.connect(dsn='employee', user='sysdba', password='pass')
# Cursor for main_transaction context
cur = con.cursor()
# Create new READ ONLY READ COMMITTED transaction
ro_transaction = con.trans(fdb.ISOLATION_LEVEL_READ_COMMITED_RO)
# and cursor
ro_cur = ro_transaction.cursor()
cur.execute('insert into country values ('Oz','Crowns')
con.commit() # commits main transaction
# Read data created by main transaction from second one
ro_cur.execute("select * from COUNTRY where COUNTRY = `Oz`")
print ro_cur.fetchall()
# Insert more data, but don't commit
cur.execute('insert into country values ('Barsoom','XXX')
# Read data created by main transaction from second one
ro_cur.execute("select * from COUNTRY where COUNTRY = `Barsoom`")
print ro_cur.fetchall()
.. index::
pair: Transaction; distributed
.. _distributed_transactions:
Distributed Transactions
------------------------
Distributed transactions are transactions that span multiple databases. FDB provides this Firebird feature
through :class:`ConnectionGroup` class. Instances of this class act as managers for :class:`Transaction`
object that is bound to multiple connections, and to :class:`cursors <Cursor>` bound to it and connections
participated in group. That's it, distributed transaction is fully independent from all other transactions,
main or secondary, of member connections.
To assemble a group of connections, you can either pass the sequence of :class:`Connection` instances to
:class:`ConnectionGroup` constructor, or add connections latter calling :meth:`ConnectionGroup.add` method.
Any :class:`Connection` could be a member of only one group, and attempt to add it to another one would
raise an exception. Also, `Connection` participating in group cannot be :meth:`closed <Connection.close>`
before it's :meth:`removed <ConnectionGroup.remove>` or whole group is
:meth:`disbanded <ConnectionGroup.disband>`.
.. warning::
Never add more than one connection to the same database to the same `ConnectionGroup`!
Similarly to :class:`Transaction`, distributed transactions are managed through :meth:`ConnectionGroup.begin`,
:meth:`ConnectionGroup.savepoint`. :meth:`ConnectionGroup.commit` and :meth:`ConnectionGroup.rollback` methods.
Additionally, `ConnectionGroup` exposes method :meth:`~ConnectionGroup.prepare` that explicitly initiates
the first phase of `Two-Phase Commit Protocol`. Transaction parameters are defined similarly to
:class:`Transaction` using :attr:`ConnectionGroup.default_tpb` or as optional parameter to
:meth:`~ConnectionGroup.begin` call.
SQL statements that should belong to context of distributed transaction are executed via :class:`Cursor`
instances aquired through :meth:`ConnectionGroup.cursor` method, or calling
:meth:`ConnectionGroup.execute_immediate` method.
.. note::
Because :class:`Cursor` instances can belong to only one :class:`Connection`, the :meth:`~ConnectionGroup.cursor`
method has manadory parameter `connection`, to specify to which member connection cursor should belong.
**Example program:**
.. code-block:: python
import fdb
# First database
con1 = fdb.create_database("CREATE DATABASE 'testdb-1.fdb' USER 'SYSDBA' PASSWORD 'masterkey'")
con1.execute_immediate("recreate table T (PK integer, C1 integer)")
con1.commit()
# Second database
con2 = fdb.create_database("CREATE DATABASE 'testdb-2,fdb' USER 'SYSDBA' PASSWORD 'masterkey'")
con2.execute_immediate("recreate table T (PK integer, C1 integer)")
con2.commit()
# Create connection group
cg = fdb.ConnectionGroup((con1,con2))
# Prepare Group cursors for each connection
gc1 = cg.cursor(con1)
gc2 = cg.cursor(con2)
# Connection cursors to check content of databases
q = 'select * from T order by pk'
cc1 = con1.cursor()
p1 = cc1.prep(q)
cc2 = con2.cursor()
p2 = cc2.prep(q)
print "Distributed transaction: COMMIT"
# ===============================
gc1.execute('insert into t (pk) values (1)')
gc2.execute('insert into t (pk) values (1)')
cg.commit()
# check it
con1.commit()
cc1.execute(p1)
print 'db1:',cc1.fetchall()
con2.commit()
cc2.execute(p2)
print 'db2:',cc2.fetchall()
print "Distributed transaction: PREPARE + COMMIT"
# =========================================
gc1.execute('insert into t (pk) values (2)')
gc2.execute('insert into t (pk) values (2)')
cg.prepare()
cg.commit()
# check it
con1.commit()
cc1.execute(p1)
print 'db1:',cc1.fetchall()
con2.commit()
cc2.execute(p2)
print 'db2:',cc2.fetchall()
print "Distributed transaction: SAVEPOINT + ROLLBACK to it"
# ===================================================
gc1.execute('insert into t (pk) values (3)')
cg.savepoint('CG_SAVEPOINT')
gc2.execute('insert into t (pk) values (3)')
cg.rollback(savepoint='CG_SAVEPOINT')
# check it - via group cursors, as transaction is still active
gc1.execute(q)
print 'db1:',gc1.fetchall()
gc2.execute(q)
print 'db2:',gc2.fetchall()
print "Distributed transaction: ROLLBACK"
# =================================
cg.rollback()
# check it
con1.commit()
cc1.execute(p1)
print 'db1:',cc1.fetchall()
con2.commit()
cc2.execute(p2)
print 'db2:',cc2.fetchall()
print "Distributed transaction: EXECUTE_IMMEDIATE"
# ==========================================
cg.execute_immediate('insert into t (pk) values (3)')
cg.commit()
# check it
con1.commit()
cc1.execute(p1)
print 'db1:',cc1.fetchall()
con2.commit()
cc2.execute(p2)
print 'db2:',cc2.fetchall()
# Finalize
con1.drop_database()
con1.close()
con2.drop_database()
con2.close()
Output::
Distributed transaction: COMMIT
db1: [(1, None)]
db2: [(1, None)]
Distributed transaction: PREPARE + COMMIT
db1: [(1, None), (2, None)]
db2: [(1, None), (2, None)]
Distributed transaction: SAVEPOINT + ROLLBACK to it
db1: [(1, None), (2, None), (3, None)]
db2: [(1, None), (2, None)]
Distributed transaction: ROLLBACK
db1: [(1, None), (2, None)]
db2: [(1, None), (2, None)]
Distributed transaction: EXECUTE_IMMEDIATE
db1: [(1, None), (2, None), (3, None)]
db2: [(1, None), (2, None), (3, None)]
.. _transaction-context-manager:
Transaction Context Manager
---------------------------
FDB provides context manager :class:`TransactionContext` that allows automatic transaction
management using :ref:`WITH`. It can work with any object that supports `begin()`, `commit()` and
`rollback()` methods, i.e. :class:`Connection`, :class:`ConnectionGroup` or :class:`Transaction`.
It starts transaction when WITH block is entered and commits it if no exception occurst within it, or
calls `rollback()` otherwise. Exceptions raised in WITH block are never suppressed.
Examples:
.. sourcecode:: python
con = fdb.connect(dsn='employee',user='sysdba',password='masterkey')
# Uses default main transaction
with TransactionContext(con):
cur = con.cursor()
cur.execute("insert into T (PK,C1) values (1,'TXT')")
# Uses separate transaction
with TransactionContext(con.trans()) as tr:
cur = tr.cursor()
cur.execute("insert into T (PK,C1) values (2,'AAA')")
# Uses connection group (distributed transaction)
con2 = fdb.connect(dsn='remote:employee',user='sysdba',password='masterkey')
cg = fdb.ConnectionGroup((con,con2))
with TransactionContext(cg):
cur1 = cg.cursor(con)
cur2 = cg.cursor(con2)
cur1.execute("insert into T (PK,C1) values (3,'Local')")
cur2.execute("insert into T (PK,C1) values (3,'Remote')")
.. index::
pair: Database; events
Database Events
===============
What they are
-------------
The Firebird engine features a distributed, interprocess communication mechanism based on messages
called `database events`. A database event is a message passed from a trigger or stored procedure
to an application to announce the occurrence of a specified condition or action, usually a database
change such as an insertion, modification, or deletion of a record. The Firebird event mechanism enables
applications to respond to actions and database changes made by other, concurrently running applications
without the need for those applications to communicate directly with one another, and without incurring
the expense of CPU time required for periodic polling to determine if an event has occurred.
Why use them
------------
Anything that can be accomplished with database events can also be implemented using other techniques,
so why bother with events? Since youâve chosen to write database-centric programs in Python rather than
assembly language, you probably already know the answer to this question, but letâs illustrate.
A typical application for database events is the handling of administrative messages. Suppose you have
an administrative message database with a `message's` table, into which various applications insert
timestamped status reports. It may be desirable to react to these messages in diverse ways, depending
on the status they indicate: to ignore them, to initiate the update of dependent databases upon their
arrival, to forward them by e-mail to a remote administrator, or even to set off an alarm so that on-site
administrators will know a problem has occurred.
It is undesirable to tightly couple the program whose status is being reported (the `message producer`) to
the program that handles the status reports (the `message handler`). There are obvious losses of flexibility
in doing so. For example, the message producer may run on a separate machine from the administrative
message database and may lack access rights to the downstream reporting facilities (e.g., network access
to the SMTP server, in the case of forwarded e-mail notifications). Additionally, the actions required
to handle status reports may themselves be time-consuming and error-prone, as in accessing a remote network
to transmit e-mail.
In the absence of database event support, the message handler would probably be implemented via `polling`.
Polling is simply the repetition of a check for a condition at a specified interval. In this case,
the message handler would check in an infinite loop to see whether the most recent record in the `messages`
table was more recent than the last message it had handled. If so, it would handle the fresh message(s);
if not, it would go to sleep for a specified interval, then loop.
The `polling-based` implementation of the message handler is fundamentally flawed. Polling is a form
of busy-wait_; the check for new messages is performed at the specified interval, regardless of the actual
activity level of the message producers. If the polling interval is lengthy, messages might not be handled
within a reasonable time period after their arrival; if the polling interval is brief, the message handler
program (and there may be many such programs) will waste a large amount of CPU time on unnecessary checks.
The database server is necessarily aware of the exact moment when a new message arrives. Why not let
the message handler program request that the database server send it a notification when a new message
arrives? The message handler can then efficiently sleep until the moment its services are needed. Under
this `event-based` scheme, the message handler becomes aware of new messages at the instant they arrive,
yet it does not waste CPU time checking in vain for new messages when there are none available.
How events are exposed
----------------------
#. Server Process ("An event just occurred!")
To notify any interested listeners that a specific event has
occurred, issue the `POST_EVENT` statement from Stored Procedure
or Trigger. The `POST_EVENT` statement has one parameter: the name
of the event to post. In the preceding example of the administrative
message database, `POST_EVENT` might be used from an `after insert`
trigger on the `messages` table, like this:
.. code-block:: s
create trigger trig_messages_handle_insert
for messages
after insert
as
begin
POST_EVENT 'new_message';
end
.. note:: The physical notification of the client process does not
occur until the transaction in which the `POST_EVENT` took place is
actually committed. Therefore, multiple events may *conceptually*
occur before the client process is *physically* informed of even one
occurrence. Furthermore, the database engine makes no guarantee that
clients will be informed of events in the same groupings in which they
conceptually occurred. If, within a single transaction, an event named
`event_a` is posted once and an event named `event_b` is posted once,
the client may receive those posts in separate "batches", despite the
fact that they occurred in the same conceptual unit (a single
transaction). This also applies to multiple occurrences of *the same*
event within a single conceptual unit: the physical notifications may
arrive at the client separately.
#. Client Process ("Send me a message when an event occurs.")
.. note:: If you don't care about the gory details of event notification,
skip to the section that describes FDB's Python-level event handling
API.
The Firebird C client library offers two forms of event notification.
The first form is *synchronous* notification, by way of the function
:c:func:`isc_wait_for_event()`. This form is admirably simple for a C programmer
to use, but is inappropriate as a basis for FDB's event support,
chiefly because it's not sophisticated enough to serve as the basis for
a comfortable Python-level API. The other form of event notification
offered by the database client library is *asynchronous*, by way of the
functions :c:func:`isc_que_events()` (note that the name of that function
is misspelled), :c:func:`isc_cancel_events()`, and others. The details are
as nasty as they are numerous, but the essence of using asynchronous
notification from C is as follows:
#. Call :c:func:`isc_event_block()` to create a formatted binary buffer that
will tell the server which events the client wants to listen for.
#. Call :c:func:`isc_que_events()` (passing the buffer created in the previous
step) to inform the server that the client is ready to receive event
notifications, and provide a callback that will be asynchronously
invoked when one or more of the registered events occurs.
#. [The thread that called :c:func:`isc_que_events()` to initiate event
listening must now do something else.]
#. When the callback is invoked (the database client library starts a
thread dedicated to this purpose), it can use the :c:func:`isc_event_counts()`
function to determine how many times each of the registered events has
occurred since the last call to :c:func:`isc_event_counts()` (if any).
#. [The callback thread should now "do its thing", which may include
communicating with the thread that called :c:func:`isc_que_events()`.]
#. When the callback thread is finished handling an event
notification, it must call :c:func:`isc_que_events()` again in order to receive
future notifications. Future notifications will invoke the callback
again, effectively "looping" the callback thread back to Step 4.
API for Python developers
-------------------------
The FDB database event API is comprised of the following: the method :meth:`Connection.event_conduit`
and the class :class:`EventConduit`.
The :class:`EventConduit` class serve as "conduit" through which database event notifications will flow
into the Python program. It's not designed to be instantiated directly by the Python programmer. Instead,
use the :meth:`Connection.event_conduit` method to create `EventConduit` instances. `event_conduit` is
a method of `Connection` rather than a module-level function or a class constructor because the database
engine deals with events in the context of a particular database (after all, `POST_EVENT` must be issued
by a stored procedure or a trigger).
:meth:`Connection.event_conduit` takes a sequence of string event names as parameter, and returns
:class:`EventConduit` instance that immediately starts to accumulate notifications of any events that occur
within the conduitâs internal queue until the conduit is closed either explicitly (via the
:meth:`~EventConduit.close` method) or implicitly (via garbage collection).
Notifications about events are aquired through call to :meth:`EventConduit.wait` method, that blocks
the calling thread until at least one of the events occurs, or the specified `timeout` (if any) expires,
and returns `None` if the wait timed out, or a dictionary that maps `event_name -> event_occurrence_count`.
**Example:**
.. code-block:: python
>>> conduit = connection.event_conduit( ('event_a', 'event_b') )
>>> conduit.wait()
{
'event_a': 1,
'event_b': 0
}
If you want to drop notifications accumulated so far by conduit, call :meth:`EventConduit.flush` method.
**Example program:**
.. code-block:: python
import fdb
import threading
import time
# Prepare database
con = fdb.create_database("CREATE DATABASE 'event_test.fdb' USER 'SYSDBA' PASSWORD 'masterkey'")
con.execute_immediate("CREATE TABLE T (PK Integer, C1 Integer)")
con.execute_immediate("""CREATE TRIGGER EVENTS_AU FOR T ACTIVE
BEFORE UPDATE POSITION 0
AS
BEGIN
if (old.C1 <> new.C1) then
post_event 'c1_updated' ;
END""")
con.execute_immediate("""CREATE TRIGGER EVENTS_AI FOR T ACTIVE
AFTER INSERT POSITION 0
AS
BEGIN
if (new.c1 = 1) then
post_event 'insert_1' ;
else if (new.c1 = 2) then
post_event 'insert_2' ;
else if (new.c1 = 3) then
post_event 'insert_3' ;
else
post_event 'insert_other' ;
END""")
con.commit()
cur = con.cursor()
# Utility function
def send_events(command_list):
for cmd in command_list:
cur.execute(cmd)
con.commit()
print "One event"
# =========
timed_event = threading.Timer(3.0,send_events,args=[["insert into T (PK,C1) values (1,1)",]])
events = con.event_conduit(['insert_1'])
timed_event.start()
e = events.wait()
events.close()
print e
print "Multiple events"
# ===============
cmds = ["insert into T (PK,C1) values (1,1)",
"insert into T (PK,C1) values (1,2)",
"insert into T (PK,C1) values (1,3)",
"insert into T (PK,C1) values (1,1)",
"insert into T (PK,C1) values (1,2)",]
timed_event = threading.Timer(3.0,send_events,args=[cmds])
events = self.con.event_conduit(['insert_1','insert_3'])
timed_event.start()
e = events.wait()
events.close()
print e
print "20 events"
# =========
cmds = ["insert into T (PK,C1) values (1,1)",
"insert into T (PK,C1) values (1,2)",
"insert into T (PK,C1) values (1,3)",
"insert into T (PK,C1) values (1,1)",
"insert into T (PK,C1) values (1,2)",]
timed_event = threading.Timer(1.0,send_events,args=[cmds])
events = con.event_conduit(['insert_1','A','B','C','D',
'E','F','G','H','I','J','K','L','M',
'N','O','P','Q','R','insert_3'])
timed_event.start()
time.sleep(3)
e = events.wait()
events.close()
print e
print "Flush events"
# ============
timed_event = threading.Timer(3.0,send_events,args=[["insert into T (PK,C1) values (1,1)",]])
events = con.event_conduit(['insert_1'])
send_events(["insert into T (PK,C1) values (1,1)",
"insert into T (PK,C1) values (1,1)"])
time.sleep(2)
events.flush()
timed_event.start()
e = events.wait()
events.close()
print e
# Finalize
con.drop_database()
con.close()
Output::
One event
{'insert_1': 1}
Multiple events
{'insert_3': 1, 'insert_1': 2}
20 events
{'A': 0, 'C': 0, 'B': 0, 'E': 0, 'D': 0, 'G': 0, 'insert_1': 2, 'I': 0, 'H': 0, 'K': 0, 'J': 0, 'M': 0,
'L': 0, 'O': 0, 'N': 0, 'Q': 0, 'P': 0, 'R': 0, 'insert_3': 1, 'F': 0}
Flush events
{'insert_1': 1}
.. currentModule:: fdb.services
.. index:: Services
.. _working_with_services:
Working with Services
=====================
Database server maintenance tasks such as user management, load monitoring, and database backup have
traditionally been automated by scripting the command-line tools :program:`gbak`, :program:`gfix`,
:program:`gsec`, and :program:`gstat`.
The API presented to the client programmer by these utilities is inelegant because they are, after all,
command-line tools rather than native components of the client language. To address this problem, Firebird
has a facility called the `Services API`, which exposes a uniform interface to the administrative
functionality of the traditional command-line tools.
The native Services API, though consistent, is much lower-level than a Pythonic API. If the native version
were exposed directly, accomplishing a given task would probably require more Python code than scripting
the traditional command-line tools. For this reason, FDB presents its own abstraction over the native API
via the :mod:`fdb.services` module.
Services API Connections
------------------------
All Services API operations are performed in the context of a `connection` to a specific database server,
represented by the :class:`fdb.services.Connection` class. Similarly to database connections, FDB provides
:func:`~fdb.services.connect()` constructor function to create such connections.
This constructor has three keyword parameters:
:host: The network name of the computer on which the database server is running.
:user: The name of the database user under whose authority the maintenance tasks are to be performed.
:password: Userâs password.
Since maintenance operations are most often initiated by an administrative user on the same computer as
the database server, `host` defaults to the local computer, and `user` defaults to `SYSDBA`.
The three calls to :func:`fdb.services.connect()` in the following program are equivalent:
.. code-block:: python
from fdb import services
con = services.connect(password='masterkey')
con = services.connect(user='sysdba', password='masterkey')
con = services.connect(host='localhost', user='sysdba', password='masterkey')
.. note::
Like database connections, it's good practice to :meth:`~Connection.close` them when you don't need them
anymore.
:class:`~fdb.services.Connection` object provides number of methods that could be divided into several groups:
* `Server Configuration and State`_: To get information about server configuration, active attachments or
users, or to get content of server log.
* `Database options`_: To set various database parameters like size of page cache, access mode or SQL dialect.
* `Database maintenance`_: To perform backup, restore, validation or other database maintenance tasks.
* `User maintanance`_: To get or change information about users defined in security database, to create new
or remove users.
* `Trace service`_: To start, stop, pause/resume or list Firebird `trace sessions`.
* `Text ouput from Services`_: Some services like `backup` or `trace` may return significant amount of text.
This output is not returned directly by method that starts the service, but through separate methods that
emulate read from text file, or provide :ref:`iterator protocol <python:typeiter>` support on `Connection`.
Server Configuration and State
------------------------------
:meth:`~Connection.get_service_manager_version()`
To help client programs adapt to version changes, the service manager exposes its version number as
an integer.
.. code-block:: python
# 64-bit Linux Firebird 2.5.1 SuperServer
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> print con.get_service_manager_version()
2
`fdb.services` is a thick wrapper of the Services API that can shield its users from changes in
the underlying C API, so this method is unlikely to be useful to the typical Python client programmer.
:meth:`~Connection.get_server_version()`
Returns the serverâs version string
.. code-block:: python
# 64-bit Linux Firebird 2.5.1 SuperServer
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> print con.get_server_version()
LI-V2.5.2.26536 Firebird 2.5
At first glance, this method appears to duplicate the functionality of the
:attr:`fdb.Connection.server_version` property, but when working with Firebird, there is a difference.
:attr:`fdb.Connection.server_version` is based on a C API call (`isc_database_info()`) that existed long
before the introduction of the Services API. Some programs written before the advent of Firebird test
the version number in the return value of `isc_database_info()`, and refuse to work if it indicates that
the server is too old. Since the first stable version of Firebird was labeled 1.0, this pre-Firebird
version testing scheme incorrectly concludes that (e.g.) Firebird 1.0 is older than Interbase 5.0.
Firebird addresses this problem by making `isc_database_info()` return a âpseudo-InterBaseâ version
number, whereas the Services API returns the true Firebird version, as shown:
.. code-block:: python
# 64-bit Linux Firebird 2.5.1 SuperServer
import fdb
con = fdb.connect(dsn='employee', user='sysdba', password='masterkey')
print 'Interbase-compatible version string:', con.server_version
svcCon = fdb.services.connect(password='masterkey')
print 'Actual Firebird version string: ', svcCon.get_server_version()
Output (on Firebird 2.5.1/Linux64)::
Interbase-compatible version string: LI-V6.3.1.26351 Firebird 2.5
Actual Firebird version string: LI-V2.5.1.26351 Firebird 2.5
:meth:`~Connection.get_architecture()`
Returns platform information for the server, including hardware architecture and operating system family.
.. code-block:: python
# 64-bit Linux Firebird 2.5.1 SuperServer
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> print con.get_architecture()
Firebird/linux AMD64
:meth:`~Connection.get_home_directory()`
Returns the equivalent of the RootDirectory setting from :file:`firebird.conf`.
.. code-block:: python
# 64-bit Linux Firebird 2.5.1 SuperServer
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> print con.get_home_directory()
/opt/firebird/
:meth:`~Connection.get_security_database_path()`
Returns the location of the serverâs core security database, which contains user definitions and such.
Name of this database is :file:`security2.fdb` (Firebird 2.0 and later) or :file:`security.fdb`
(Firebird 1.5).
.. code-block:: python
# 64-bit Linux Firebird 2.5.1 SuperServer
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> print con.get_security_database_path()
/opt/firebird/security2.fdb
:meth:`~Connection.get_lock_file_directory()`
Returns the directory location for Firebird lock files.
.. code-block:: python
# 64-bit Linux Firebird 2.5.1 SuperServer
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> print con.get_lock_file_directory()
/tmp/firebird/
:meth:`~Connection.get_server_capabilities()`
Returns tuple of capability info codes for each capability reported by Firebird server. Following
constants are defined in :mod:`fdb.services` for convenience:
* :data:`CAPABILITY_MULTI_CLIENT`
* :data:`CAPABILITY_REMOTE_HOP`
* :data:`CAPABILITY_SERVER_CONFIG`
* :data:`CAPABILITY_QUOTED_FILENAME`
* :data:`CAPABILITY_NO_SERVER_SHUTDOWN`
.. code-block:: python
# 64-bit Linux Firebird 2.5.1 SuperServer
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> print con.get_server_capabilities()
(2L, 4L, 512L, 256L)
>>> fdb.services.CAPABILITY_MULTI_CLIENT in con.get_server_capabilities()
True
>>> fdb.services.CAPABILITY_QUOTED_FILENAME in con.get_server_capabilities()
False
:meth:`~Connection.get_message_file_directory()`
To support internationalized error messages/prompts, the database engine stores its messages in a file
named :file:`firebird.msg`. The directory in which this file resides can be determined with this method.
.. code-block:: python
# 64-bit Linux Firebird 2.5.1 SuperServer
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> print con.get_message_file_directory()
/opt/firebird/
:meth:`~Connection.get_connection_count()`
Returns the number of active connections to databases managed by the server. This count only includes
database connections (such as open instances of :class:`fdb.Connection`), not services manager connections
(such as open instances of :class:`fdb.services.Connection`).
.. code-block:: python
# 64-bit Linux Firebird 2.5.1 SuperServer
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> db1 = fdb.connect(dsn='employee',user='sysdba',password='masterkey')
>>> db2 = fdb.connect(dsn='employee',user='sysdba',password='masterkey')
>>> print con.get_connection_count()
2
:meth:`~Connection.get_attached_database_names()`
Returns a list of the names of all databases to which the server is maintaining at least one connection.
The database names are not guaranteed to be in any particular order.
.. code-block:: python
# 64-bit Linux Firebird 2.5.1 SuperServer
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> db1 = fdb.connect(dsn='employee',user='sysdba',password='masterkey')
>>> db2 = fdb.connect(dsn='employee',user='sysdba',password='masterkey')
>>> print con.get_attached_database_names()
['/opt/firebird/examples/empbuild/employee.fdb']
:meth:`~Connection.get_log()`
Request the contents of the serverâs log file (:file:`firebird.log`).
This method is so-called `Async method` that only initiates log transfer. Actual log content could be
read by one from many methods for `text ouput from Services`_ that `Connection` provides .
.. code-block:: python
# 64-bit Linux Firebird 2.5.1 SuperServer
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> con.get_log()
>>> log = con.readlines()
Database options
----------------
:meth:`~Connection.set_default_page_buffers`
Sets individual page cache size for Database.
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> con.set_default_page_buffers('employee',100)
:meth:`~Connection.set_sweep_interval`
Sets treshold for automatic sweep.
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> con.set_sweep_interval('employee',100000)
:meth:`~Connection.set_reserve_page_space`
Sets data page space reservation policy.
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
# Use all space
>>> con.set_reserve_page_space('employee',False)
:meth:`~Connection.set_write_mode`
Sets Disk Write Mode: Sync (forced writes) or Async (buffered). Following constants are defined
in :mod:`fdb.services` for convenience:
* :data:`~fdb.services.WRITE_FORCED`
* :data:`~fdb.services.WRITE_BUFFERED`
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
# Disable Forced Writes
>>> con.set_write_mode('employee',services.WRITE_BUFFERED)
:meth:`~Connection.set_access_mode`
Sets Database Access mode: Read Only or Read/Write. Following constants are defined
in :mod:`fdb.services` for convenience:
* :data:`~fdb.services.ACCESS_READ_WRITE`
* :data:`~fdb.services.ACCESS_READ_ONLY`
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
# Set database to R/O mode
>>> con.set_access_mode('employee',services.ACCESS_READ_ONLY)
:meth:`~Connection.set_sql_dialect`
Sets SQL Dialect for Database.
.. warning::
Changing SQL dialect on existing database is not recommended. Only newly created database
objects would respect new dialect setting, while objects created with previous dialect remain
unchanged. That may have dire consequences.
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
# Use SQL dialect 1
>>> con.set_sql_dialect('employee',1)
Database maintenance
--------------------
:meth:`~Connection.get_limbo_transaction_ids`
Returns list of transactions in limbo.
:meth:`~Connection.commit_limbo_transaction`
Resolves limbo transaction with commit.
:meth:`~Connection.rollback_limbo_transaction`
Resolves limbo transaction with rollback.
:meth:`~Connection.get_statistics`
Request database statisctics. Report is in the same format as the output of the gstat command-line
utility. This method has one required parameter, the location of the database on which to compute
statistics, and six optional boolean parameters for controlling the domain of the statistics.
This method is so-called `Async method` that only initiates report processing. Actual report could be
read by one from many methods for `text ouput from Services`_ that `Connection` provides .
.. note::
Until statistical report is not fully fetched from service (or ignored via
:meth:`~Connection.wait`), any attempt to start another asynchronous service will
fail with exception.
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> con.get_statistics('employee')
>>> stat_report = con.readlines()
:meth:`~Connection.backup`
Request logical (GBAK) database backup. Produces report about backup process.
This method is so-called `Async method` that only initiates backup process. Actual report could be
read by one from many methods for `text ouput from Services`_ that `Connection` provides .
.. note::
Until backup report is not fully fetched from service (or ignored via
:meth:`~Connection.wait`), any attempt to start another asynchronous service will
fail with exception.
.. code-block:: python
# 64-bit Linux Firebird 2.5.1 SuperServer
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> con.backup('employee', '/home/data/employee.fbk', metadata_only=True, collect_garbage=False)
>>> backup_report = con.readlines()
:meth:`~Connection.restore`
Request database restore from logical (GBAK) backup. Produces report about restore process.
This method is so-called `Async method` that only initiates restore process. Actual report could be
read by one from many methods for `text ouput from Services`_ that `Connection` provides .
.. note::
Until restore report is not fully fetched from service (or ignored via
:meth:`~Connection.wait`), any attempt to start another asynchronous service will
fail with exception.
.. code-block:: python
# 64-bit Linux Firebird 2.5.1 SuperServer
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> con.restore('/home/data/employee.fbk', '/home/data/empcopy.fdb')
>>> restore_report = con.readlines()
:meth:`~Connection.nbackup`
Perform physical (NBACKUP) database backup.
.. note:: Method call will not return until sweep is finished.
:meth:`~Connection.nrestore`
Perform restore from physical (NBACKUP) database backup.
.. note:: Method call will not return until sweep is finished.
:meth:`~Connection.shutdown`
Database shutdown. Following constants are defined in :mod:`fdb.services` for convenience:
For shutdow mode:
* :data:`~fdb.services.SHUT_SINGLE`
* :data:`~fdb.services.SHUT_MULTI`
* :data:`~fdb.services.SHUT_FULL`
For shutdown method:
* :data:`~fdb.services.SHUT_FORCE`
* :data:`~fdb.services.SHUT_DENY_NEW_TRANSACTIONS`
* :data:`~fdb.services.SHUT_DENY_NEW_ATTACHMENTS`
.. code-block:: python
# 64-bit Linux Firebird 2.5.1 SuperServer
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
# Shutdown database to single-user maintenance mode
>>> con.shutdown('empoyee', services.SHUT_SINGLE, services.SHUT_FORCE, 0)
# Go to full shutdown mode, disabling new attachments during 5 seconds
>>> con.shutdown('empoyee', services.SHUT_FULL, services.SHUT_DENY_NEW_ATTACHMENTS, 5)
:meth:`~Connection.bring_online`
Bring previously shut down database back online. Following constants are defined in
:mod:`fdb.services` for convenience:
For on-line mode:
* :data:`~fdb.services.SHUT_NORMAL`
* :data:`~fdb.services.SHUT_SINGLE`
* :data:`~fdb.services.SHUT_MULTI`
.. code-block:: python
# 64-bit Linux Firebird 2.5.1 SuperServer
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
# Enable multi-user maintenance
>>> con.bring_online('employee', services.SHUT_MULTI)
# Enable single-user maintenance
>>> con.bring_online('employee', services.SHUT_SINGLE)
# Return to normal state
>>> con.bring_online('employee')
:meth:`~Connection.activate_shadow`
Activates Database Shadow(s).
:meth:`~Connection.sweep`
Performs Database Sweep.
.. note:: Method call will not return until sweep is finished.
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> con.sweep('empoyee')
:meth:`~Connection.repair`
Database Validation and Repair.
.. note:: Method call will not return until action is finished.
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
# Just validate
>>> con.repair('empoyee', ignore_checksums=True, read_only_validation=True)
# Mend the database
>>> con.repair('empoyee', ignore_checksums=True, mend_database=True)
User maintanance
----------------
:meth:`~Connection.get_users`
Returns information about specified user or all users as a list of :class:`User` instances.
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> users = con.get_users()
>>> for user in users:
... print user.name
... print user.first_name, user.middle_name, user.last_name
... print user.user_id, user.group_id
SYSDBA
Sql Server Administrator
0 0
:meth:`~Connection.add_user`
Adds new user. Requires instance of :class:`User` with **at least** its :attr:`~User.name`
and :attr:`~User.password` attributes specified as non-empty values. All other attributes
are optional.
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> user = services.User('NewUser')
>>> user.password = 'secret'
>>> user.first_name = 'John'
>>> user.last_name = 'Doe'
>>> con.add_users(User)
:meth:`~Connection.modify_user`
Modifycation of user information. Requires instance of :class:`User` with **at least** its
:attr:`~User.name` attribute specified as non-empty value.
.. note::
Sets :attr:`~User.first_name`, :attr:`~User.middle_name` and :attr:`~User.last_name` to
their actual values, and ignores the :attr:`~User.user_id` and :attr:`~User.group_id` attributes
regardless of their values. :attr:`~User.password` is set **only** when it has value.
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> user = services.User('SYSDBA')
# Change pasword
>>> user.password = 'Pa$$w0rd'
>>> con.modify_user(User)
:meth:`~Connection.remove_user`
Removes user. Requires User name or instance of :class:`User` with **at least** its
:attr:`~User.name` attribute specified as non-empty value.
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> con.remove_user('NewUser')
:meth:`~Connection.user_exists`
Checks for user's existence. Requires User name or instance of :class:`User` with **at least** its
:attr:`~User.name` attribute specified as non-empty value.
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> con.user_exists('NewUser')
False
Trace service
-------------
:meth:`~Connection.trace_start`
Starts new trace session. Requires trace `configuration` and returns `Session ID`.
Trace session output could be retrieved through :meth:`~Connection.readline`,
:meth:`~Connection.readlines`, iteration over `Connection` or ignored via call to
:meth:`~Connection.wait`.
.. note::
Until session output is not fully fetched from service (or ignored via :meth:`~Connection.wait`),
any attempt to start another asynchronous service including call to any `trace_` method will fail
with exception.
.. code-block:: python
import fdb
svc = fdb.services.connect(password='masterkey')
# Because trace session blocks the connection, we need another one to stop trace session!
svc_aux = fdb.services.connect(password='masterkey')
trace_config = """<database 'employee'>
enabled true
log_statement_finish true
print_plan true
include_filter %%SELECT%%
exclude_filter %%RDB$%%
time_threshold 0
max_sql_length 2048
</database>
"""
trace_id = svc.trace_start(trace_config,'test_trace_1')
trace_log = []
# Get first 10 lines of trace output
for i in range(10):
trace_log.append(svc.readline())
# Stop trace session
svc_aux.stop_trace(trace_id)
:meth:`~Connection.trace_stop`
Stops trace session specified by ID.
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> con.trace_stop(15)
Trace session ID 15 stopped
:meth:`~Connection.trace_suspend`
Suspends trace session specified by ID.
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> con.trace_suspend(15)
Trace session ID 15 paused
:meth:`~Connection.trace_resume`
resumes trace session specified by ID.
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> con.trace_resume(15)
Trace session ID 15 resumed
:meth:`~Connection.trace_list`
Returns information about existing trace sessions as dictionary mapping `SESSION_ID -> SESSION_PARAMS`.
Session parameters is another dictionary with next keys:
:name: (string) (optional) Session name if specified.
:date: (datetime.datetime) Session start date and time.
:user: (string) Trace user name.
:flags: (list of strings) Session flags.
.. code-block:: python
>>> from fdb import services
>>> con = services.connect(host='localhost', user='sysdba', password='masterkey')
>>> con.trace_list()
{53: {'date': datetime.datetime(2012, 10, 5, 10, 45, 4),
'flags': ['active', ' admin', ' trace'],
'user': 'SYSDBA'}}
Text ouput from Services
------------------------
Some services like `backup` or `trace` may return significant amount of text. Rather than return the whole
text as single string value by methods that provide access to these services, FDB isolated the transfer
process to separate methods:
* :meth:`~Connection.readline` - Similar to :meth:`file.readline`, returns next line of output from Service.
* :meth:`~Connection.readlines` - Like :meth:`file.readlines`, returns list of output lines.
* Iteration over `Connection` object, because :class:`Connection` has built-in support for :ref:`iterator
protocol <python:typeiter>`.
* Using `callback` method provided by developer. Each `Connection` method that returns its result
asynchronously accepts an optional parameter `callback`, which must be a function that accepts one string
parameter. This method is then called with each output line coming from service.
* :meth:`~Connection.wait` - Waits for Sevice to finish, ignoring rest of the output it may produce.
.. warning::
Until output is not fully fetched from service, any attempt to start another asynchronous service will
fail with exception! This constraint is set by Firebird Service API.
You may check the status of asynchronous Services using :attr:`Connection.fetching` attribute or
:meth:`Connection.isrunning` method.
In cases when you're not interested in output produced by Service, call :meth:`~Connection.wait` to
wait for service to complete.
**Examples:**
.. code-block:: python
import fdb
svc = fdb.services.connect(password='masterkey')
print "Fetch materialized"
print "=================="
print "Start backup"
svc.backup('employee', 'employee.fbk')
print "svc.fetching is", svc.fetching
print "svc.running is", svc.isrunning()
report = svc.readlines()
print "%i lines returned" % len(report)
print "First 5 lines from output:"
for i in xrange(5):
print i,report[i]
print "svc.fetching is", svc.fetching
print "svc.running is", svc.isrunning()
print
print "Iterate over result"
print "==================="
svc.backup('employee', 'employee.fbk')
output = []
for line in svc:
output.append(line)
print "%i lines returned" % len(output)
print "Last 5 lines from output:"
for line in output[-5:]:
print line
print
print "Callback"
print "========"
output = []
# Callback function
def fetchline(line):
output.append(line)
svc.backup('employee', 'employee.fbk', callback=fetchline)
print "%i lines returned" % len(output)
print "Last 5 lines from output:"
for line in output[-5:]:
print line
Output::
Fetch materialized
==================
Start backup
svc.fetching is True
svc.running is True
558 lines returned
First 5 lines from output:
0 gbak:readied database employee for backup
1 gbak:creating file employee.fbk
2 gbak:starting transaction
3 gbak:database employee has a page size of 4096 bytes.
4 gbak:writing domains
svc.fetching is False
svc.running is False
Iterate over result
===================
558 lines returned
Last 5 lines from output:
gbak:writing referential constraints
gbak:writing check constraints
gbak:writing SQL roles
gbak:writing names mapping
gbak:closing file, committing, and finishing. 74752 bytes written
Callback
========
558 lines returned
Last 5 lines from output:
gbak:writing referential constraints
gbak:writing check constraints
gbak:writing SQL roles
gbak:writing names mapping
gbak:closing file, committing, and finishing. 74752 bytes written
.. _Firebird Documentation: http://www.firebirdsql.org/en/reference-manuals/
.. _DDL: http://en.wikipedia.org/wiki/Data_Definition_Language
.. _DML: http://en.wikipedia.org/wiki/Data_Manipulation_Language
.. _Firebird SAVEPOINTs: http://www.firebirdsql.org/refdocs/langrefupd15-savepoint.html
.. _Firebird API documentation: http://www.ibphoenix.com/files/60ApiGuide.zip
.. _busy-wait: http://www.catb.org/jargon/html/B/busy-wait.html
