usage-guide.txt

===========
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.
* :mod:`~fdb.schema` - Driver code to work with Firebird database schema (metadata).
* :mod:`~fdb.utils` - Various classes and functions used by driver that are generally useful.

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')

Since version 1.2 FDB supports additional `Connection` class(es) that extend :class:`Connection` functionality
in optional (opt-in) way. For example :class:`ConnectionWithSchema` extends Connection interface with methods
and attributes provided by :class:`~fdb.schema.Schema`. New `connection_class` parameter was introduced to
`connect` and `create_database` to connect to/create database using different class than descends from 
`Connection`.

**Example:**

.. code-block:: python
   
   # Connecting through ConnectionWithSchema
   #
   con = fdb.connect(dsn='/path/database.fdb', user='sysdba', password='pass',
                     connection_class=fdb.ConnectionWithSchema)


.. 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'")

.. note::

   Since version 1.2 FDB supports additional method for database creation. Instead `CREATE DATABASE`
   SQL statement you can use number of optional keyword parameters introduced to :func:`create_database`.

**Example:**

.. code-block:: python
   
   con = fdb.create_database(dsn='/temp/db.fdb',user='sysdba',password='pass',page_size=8192)

.. 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`.
* `Transanction 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 Firebird version`_: attributes :attr:`~Connection.server_version`,
  :attr:`~Connection.firebird_version`, :attr:`~Connection.version` and :attr:`~Connection.engine_version`.
* `Getting information about database`_: methods :meth:`~Connection.db_info` and 
  :meth:`~Connection.database_info`.
* :ref:`Getting information about database metadata <working-with-schema>`: attribute :attr:`~Connection.schema` and :attr:`~Connection.ods`.


.. index::
   pair: Firebird; information about

Getting information about Firebird version
------------------------------------------

Because functionality and some features depends on actual Firebird version, it could be important for FDB
users to check it. This (otherwise) simple task could be confusing for new Firebird users, because Firebird 
uses two different version lineages. This abomination was introduced to Firebird thanks to its InterBase legacy
(Firebird 1.0 is a fork of InterBase 6.0), as applications designed to work with InterBase can often work
with Firebird without problems (and vice versa). However, legacy applications designed to work with InterBase
may stop working properly if they would detect unexpectedly low server version, so default version number
returned by Firebird (and FDB) is based on InterBase version number. For example this version for Firebird 
2.5.2 is 6.3.2, so condition for legacy applications that require at least IB 6.0 is met.

FDB provides these version strings as two `Connection` properties:

- :attr:`~Connection.server_version` - Legacy InterBase-friendly version string.
- :attr:`~Connection.firebird_version` - Firebird's own version string.

However, this version string contains more information than version number. For example for Linux Firebird 
2.5.2 it's 'LI-V2.5.2.26540 Firebird 2.5'. So FDB provides two more properties for your convenience:

- :attr:`~Connection.version` - Only Firebird version number. It's a string with format: 
  major.minor.subrelease.build
- :attr:`~Connection.engine_version` - Engine (major.minor) version as (float) number.

FDB also provides convenient constants for supported engine versions: `ODS_FB_20`,`ODS_FB_21` and `ODS_FB_25`.

Database On-Disk Structure
--------------------------

Particular Firebird features may also depend on specific support in database (for example monitoring 
tables introduced in Firebird 2.1). These required structures are present automatically when database
is created by particular engine verison that needs them, but Firebird engine may typically work with 
databases created by older versions and thus with older structure, so it could be necessary to consult
also On-Disk Structure (ODS for short) version. FDB provides this number as :attr:`Connection.ods` (float) 
property.

**Example**:

.. code-block:: python

   con = fdb.connect(dsn='/path/database.fdb', user='sysdba', password='pass')
   print 'Firebird version:',con.version
   print 'ODS version:',con.ods

::

   Firebird version: 2.5.2.26540
   ODS version: 11.1

In abover example although connected Firebird engine is version 2.5, connected database has ODS 11.1 which
came with Firebird 2.1, and some Firebird 2.5 features will not be available on this database.

.. 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)
   # or alternatively you can take an advantage of cur.execute returning self.
   for (name, year_released) in cur.execute(SELECT):
       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

.. tip::

   :meth:`Cursor.execute` and :meth:`Cursor.executemany` return self, so you can use calls to them
   as iterators (see example above).

.. 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 result
in 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. 

.. 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.

Beside SQL command string, `Cursor` also allows to aquire and use `PreparedStatement` instances explicitly. 
`PreparedStatement` are 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)

Prepared statements are 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 normal execution that prepares statements on each execution.

.. 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

   # A new SQL string containing the inputs is submitted every time.  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.
   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.

.. 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

Transanction 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**.

        Internaly FDB uses such transaction to read metadata from connected database. This internal
        transaction is also available to developers for convenience as :attr:`Connection.query_transaction`.

* **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 information 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. 

.. important::

   To start listening for events it's necessary (starting from FDB version 1.4.2) to call :meth:`EventConduit.begin`
   method or use EventConduit's context manager interface.

Immediately when :meth:`~EventConduit.begin` method is called, EventConduit 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`.

.. important::

   `EventConduit` can act as context manager that ensures execution of :meth:`~EventConduit.begin` and :meth:`~EventConduit.close` methods.
   It's strongly advised to use the `EventConduit` with the `with` statement.

**Example:**

.. code-block:: python

   with connection.event_conduit( ('event_a', 'event_b') ) as conduit:
       events = conduit.wait()
       process_events(events)

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'])
   events.begin()
   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'])
   events.begin()
   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'])
   events.begin()
   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'])
   events.begin()
   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 


.. index::
   pair: Database; schema
   pair: Database schema; working with

.. _working-with-schema:

Working with database schema
============================

Description of database objects like tables, views, stored procedures, triggers or UDF functions that 
represent database schema is stored in set of system tables present in every database. Firebird users 
can query these tables to get information about these objects and their relations. But querying system
tables is inconvenient, as it requires good knowledge how this information is structured and requires 
significant amount of Python code. Changes in system tables between Firebird versions further add to this
complexity. Hence FDB provides set of classes (isolated in separate module :mod:`fdb.schema`) that 
transform information stored in system tables into set of Python objects that surface the vital information
in meaningful way, and additionaly provide set of methods for most commonly used operations or checks.

Database schema could be accessed in three different ways, each suitable for different use case:

- By direct creation of :class:`fdb.schema.Schema` instances that are then :meth:`binded <fdb.schema.Schema.bind>` 
  to particular :class:`Connection` instance. This method is best if you want to work with schema only 
  occasionally, or you want to keep connections as lightweight as possible.
- Accessing :attr:`fdb.Connection.schema` property. This method is more convenient than previous one, and 
  represents a compromise between convenience and resource consumption because :class:`~fdb.schem.Schema` instance is not created until first reference and is managed by connection itself. Individual metadata
  objects are not loaded from system tables until first reference.
- Using :class:`~fdb.ConnectionWithSchema` instead :class:`Connection` by specifying 
  `connection_class=ConnectionWithSchema` parameter to :func:`~fdb.connect` or :func:`~fdb.create_database`.
  This `Connection` descendant loads all database metadata immediately and provides directly all attributes 
  and methods provided by :class:`~fdb.schema.Schema` class. This method is most suitable in case you want
  to work with database metadata extensively.

**Examples:**

1. Using Schema instance:

.. code-block:: python

   >>> import fdb
   >>> con = fdb.connect(dsn='employee',user='sysdba', password='masterkey')
   >>> schema = fdb.schema.Schema()
   >>> schema.bind(con)
   >>> [t.name for t in schema.tables]
   ['COUNTRY', 'JOB', 'DEPARTMENT', 'EMPLOYEE', 'SALES', 'PROJECT', 'EMPLOYEE_PROJECT', 'PROJ_DEPT_BUDGET', 
    'SALARY_HISTORY', 'CUSTOMER']

2. Using Connection.schema:

.. code-block:: python

   >>> import fdb
   >>> con = fdb.connect(dsn='employee',user='sysdba', password='masterkey')
   >>> [t.name for t in con.schema.tables]
   ['COUNTRY', 'JOB', 'DEPARTMENT', 'EMPLOYEE', 'SALES', 'PROJECT', 'EMPLOYEE_PROJECT', 'PROJ_DEPT_BUDGET', 
    'SALARY_HISTORY', 'CUSTOMER']

3. Using ConnectionWithSchema:

.. code-block:: python

   >>> import fdb
   >>> con = fdb.connect(dsn='employee',user='sysdba', password='masterkey',
       connection_class=fdb.ConnectionWithSchema)
   >>> [t.name for t in con.tables]
   ['COUNTRY', 'JOB', 'DEPARTMENT', 'EMPLOYEE', 'SALES', 'PROJECT', 'EMPLOYEE_PROJECT', 'PROJ_DEPT_BUDGET', 
    'SALARY_HISTORY', 'CUSTOMER']

.. note::

   Individual metadata information (i.e. information about :attr:`domains <fdb.schema.Schema.domains>`, :attr:`tables <fdb.schema.Schema.tables>` etc.) is loaded on first access and cached for further reference until it's :meth:`clared <fdb.schema.Schema.clear>` or :meth:`reload <fdb.schema.Schema.reload>` is
   requested.

   Because once loaded information is cached, it's good to :meth:`clar <fdb.schema.Schema.clear>` it
   when it's no longer needed to conserve memory.



Available information
---------------------

The :class:`~fdb.schema.Schema` provides information about:

- **Database:** :attr:`Owner name <fdb.schema.Schema.owner_name>`, 
  :attr:`default character set <fdb.schema.Schema.default_character_set>`, 
  :attr:`description <fdb.schema.Schema.description>`, 
  :attr:`security class <fdb.schema.Schema.security_class>` and whether database consist 
  from :meth:`single or multiple files <fdb.schema.Schema.ismultifile>`.
- **Facilities:** Available :attr:`character sets <fdb.schema.Schema.character_sets>`, 
  :attr:`collations <fdb.schema.Schema.collations>`, database :attr:`files <fdb.schema.Schema.files>` and 
  :attr:`shadows <fdb.schema.Schema.shadows>`.
- **User database objects:** :attr:`exceptions <fdb.schema.Schema.exceptions>`, 
  :attr:`generators <fdb.schema.Schema.generators>`, :attr:`domains <fdb.schema.Schema.domains>`, 
  :attr:`tables <fdb.schema.Schema.tables>` and their :attr:`constraints <fdb.schema.Schema.constraints>`, 
  :attr:`indices <fdb.schema.Schema.indices>`, :attr:`views <fdb.schema.Schema.views>`, 
  :attr:`triggers <fdb.schema.Schema.triggers>`, :attr:`procedures <fdb.schema.Schema.procedures>`, 
  user :attr:`roles <fdb.schema.Schema.roles>` and :attr:`user defined functions <fdb.schema.Schema.functions>`.
- **System database objects:** :attr:`generators <fdb.schema.Schema.sysgenerators>`, 
  :attr:`domains <fdb.schema.Schema.sysdomains>`, :attr:`tables <fdb.schema.Schema.systables>` and their 
  constraints,  :attr:`indices <fdb.schema.Schema.sysindices>`, :attr:`views <fdb.schema.Schema.sysviews>`, 
  :attr:`triggers <fdb.schema.Schema.systriggers>`, :attr:`procedures <fdb.schema.Schema.sysprocedures>` 
  and :attr:`functions <fdb.schema.Schema.sysfunctions>`.
- **Relations between objects:** Through direct links between metadata objects and 
  :attr:`dependencies <fdb.schema.Schema.dependencies>`.
- **Privileges:** :attr:`All <fdb.schema.Schema.privileges>` privileges, or privileges granted for specific 
  :attr:`table <fdb.schema.Table.privileges>`, :attr:`table column <fdb.schema.TableColumn.privileges>`, 
  :attr:`view <fdb.schema.View.privileges>`, :attr:`view column <fdb.schema.ViewColumn.privileges>`, 
  :attr:`procedure <fdb.schema.Procedure.privileges>` or :attr:`role <fdb.schema.Role.privileges>`. It's also
  possible to get all privileges :meth:`granted to <fdb.schema.Schema.get_privileges_of>` specific user, role, 
  procedure, trigger or view.

Metadata objects
----------------

Schema information is presented as Python objects of various classes with common parent class 
:class:`~fdb.schema.BaseSchemaItem` (except :class:`~fdb.schema.Schema` itself), that defines several
common attributes and methods:

Attributes:

- :attr:`~fdb.schema.BaseSchemaItem.name`: Name of database object or None if object doesn't have a name.
- :attr:`~fdb.schema.BaseSchemaItem.description`: Description (documentation text) for object or None if 
  object doesn't have a description.
- :attr:`~fdb.schema.BaseSchemaItem.actions`: List of supported SQL operations on schema object instance.

Methods:

- :meth:`~fdb.schema.BaseSchemaItem.accept_visitor`: :ref:`Visitor Pattern support <visitor-pattern-support>`.
- :meth:`~fdb.schema.BaseSchemaItem.issystemobject`: Returns True if this database object is system object.
- :meth:`~fdb.schema.BaseSchemaItem.get_quoted_name`: Returns quoted (if necessary) name of database object.
- :meth:`~fdb.schema.BaseSchemaItem.get_dependents`: Returns list of all database objects that 
  :ref:`depend <object-dependencies>` on this one.
- :meth:`~fdb.schema.BaseSchemaItem.get_dependencies`: Returns list of database objects that this object 
  :ref:`depend <object-dependencies>` on.
- :meth:`~fdb.schema.BaseSchemaItem.get_sql_for`: Returns :ref:`SQL command string <sql-oprations>` for 
  specified action on database object.

There are next schema objects: :class:`~fdb.schema.Collation`, :class:`~fdb.schema.CharacterSet`,
:class:`~fdb.schema.DatabaseException`, :class:`~fdb.schema.Sequence` (Generator), :class:`~fdb.schema.Domain`,
:class:`~fdb.schema.Index`, :class:`~fdb.schema.Table`, :class:`~fdb.schema.TableColumn`,
:class:`~fdb.schema.Constraint`, :class:`~fdb.schema.View`, :class:`~fdb.schema.ViewColumn`, 
:class:`~fdb.schema.Trigger`, :class:`~fdb.schema.Procedure`, :class:`~fdb.schema.ProcedureParameter`,
:class:`~fdb.schema.Function`, :class:`~fdb.schema.FunctionArgument`, :class:`~fdb.schema.Role`,
:class:`~fdb.schema.Dependency`, :class:`~fdb.schema.DatabaseFile` and :class:`~fdb.schema.Shadow`.

.. _visitor-pattern-support:

Visitor Pattern support
-----------------------

`Visitor Pattern`_ is particularly useful when you need to process various objects that need special handling
in common algorithm (for example display information about them or generate SQL commands to create them in new 
database). Each metadata object (including :class:`~fdb.schema.Schema`) supports 
:meth:`~fdb.schema.BaseSchemaItem.accept_visitor` method that calls `class-specific` method on object passed 
to it as parameter. This method name always starts with 'visit'. For example :class:`~fdb.schema.Table` object 
calls `visitTable`. This 'visit*' method has one parameter - object instance that calls it.

FDB provides skeleton implementation for :class:`schema Visitor object <fdb.schema.SchemaVisitor>` that
implements all `visit*` methods called by schema classes as call to method `default_action` that does 
nothing.

Next code uses visitor pattern to print all DROP SQL statements necessary to drop database object, 
taking its dependencies into account, i.e. it could be necessary to first drop other - dependant objects 
before it could be dropped.

.. code-block:: python

   import fdb
   # Object dropper
   class ObjectDropper(fdb.schema.SchemaVisitor):
       def __init__(self):
           self.seen = []
       def drop(self,obj):
           self.seen = []
           obj.accept_visitor(self)
       def default_action(self,obj):
           if not obj.issystemobject() and 'drop' in obj.actions:
              for dependency in obj.get_dependents():
                   d = dependency.dependent
                   if d and d not in self.seen:
                       d.accept_visitor(self)
              if obj not in self.seen:
                   print obj.get_sql_for('drop')
                   self.seen.append(obj)
       def visitSchema(self,schema):
           pass
       def visitMetadataItem(self,item):
           pass
       def visitTableColumn(self,column):
           column.table.accept_visitor(self)
       def visitViewColumn(self,column):
           column.view.accept_visitor(self)
       def visitDependency(self,dependency):
           pass
       def visitConstraint(self,constraint):
           pass
       def visitProcedureParameter(self,param):
           param.procedure.accept_visitor(self)
       def visitFunctionArgument(self,arg):
           arg.function.accept_visitor(self)
       def visitDatabaseFile(self,dbfile):
           pass
       def visitShadow(self,shadow):
           pass
 
   # Sample use:

   con = fdb.connect(dsn='employee',user='sysdba', password='masterkey')
   table = con.schema.get_table('JOB')
   dropper = ObjectDropper()
   dropper.drop(table)

Will produce next result::

   DROP PROCEDURE ALL_LANGS
   DROP PROCEDURE SHOW_LANGS
   DROP TABLE JOB

.. _object-dependencies:

Object dependencies
-------------------

Close relations between metadata object like `ownership` (Table vs. TableColumn, Index or Trigger) or
`cooperation` (like FK Index vs. partner UQ/PK Index) are defined directly using properties of particular
schema objects. Besides close releations Firebird also uses `dependencies`, that describe functional
dependency between otherwise independent metadata objects. For example stored procedure can call other stored
procedures, define its parameters using domains or work with tables or views. Removing or changing these
objects may/will cause the procedure to stop working correctly, so Firebird tracks these dependencies.
FDB surfaces these dependencies as :class:`~fdb.schema.Dependency` schema objects, and all schema objects 
have :meth:`~fdb.schema.BaseSchemaItem.get_dependents` and :meth:`~fdb.schema.BaseSchemaItem.get_dependencies` 
methods to get list of :class:`~fdb.schema.Dependency` instances that describe these dependencies.

:class:`~fdb.schema.Dependency` object provides names and types of dependent/depended on database objects,
and access to their respective schema Python objects as well.

.. _sql-oprations:

SQL operations
--------------

FDB doesn't allow you to change database metadata directly using schema objects. Instead it supports
generation of DDL SQL commands from schema objects using :meth:`~fdb.schema.BaseSchemaItem.get_sql_for` method
present on all schema objects except Schema itself. DDL commands that could be generated depend on object
type and context (for example it's not possible to generate DDL commands for system database objects), and 
list of DDL commands that could be generated for particular schema object could be obtained from its 
:attr:`~fdb.schema.BaseSchemaItem.actions` attribute.

Possible `actions` could be: create, recreate, create_or_alter, alter, drop, activate, deactivate, recompute
and declare. Some actions require/allow additional parameters.

.. list-table:: SQL actions
   :widths: 20 15 15 5 45
   :header-rows: 1

   * - Schema class
     - Action
     - Parameter
     - Required
     - Description
   * - :class:`~fdb.schema.Collation`
     - create
     - 
     - 
     - 
   * - 
     - drop
     - 
     -
     - 
   * - :class:`~fdb.schema.CharacterSet`
     - alter
     - collation
     - Yes
     - :class:`~fdb.schema.Collation` instance or collation name
   * - :class:`~fdb.schema.DatabaseException`
     - create
     - 
     - 
     -
   * - 
     - recreate
     - 
     -
     - 
   * - 
     - alter
     - message
     - Yes
     - string.
   * - 
     - create_or_alter
     - 
     - 
     - 
   * - 
     - drop
     - 
     -
     -
   * - :class:`~fdb.schema.Sequence`
     - create
     - 
     -
     -
   * - 
     - alter
     - value
     - Yes
     - integer
   * - 
     - drop
     - 
     -
     -
   * - :class:`~fdb.schema.Domain`
     - create
     - 
     -
     -
   * - 
     - alter
     - 
     - 
     - One from next parameters required
   * - 
     - 
     - name
     - No
     - string
   * - 
     - 
     - default
     - No
     - string definition or None to drop default
   * - 
     - 
     - check
     - No
     - string definition or None to drop check
   * - 
     - 
     - datatype
     - No
     - string SQL datatype definition
   * - 
     - drop
     - 
     -
     -
   * - :class:`~fdb.schema.Constraint`
     - create
     - 
     -
     -
   * - 
     - drop
     - 
     -
     -
   * - :class:`~fdb.schema.Index`
     - create
     - 
     -
     -
   * - 
     - activate
     - 
     -
     -
   * - 
     - deactivate
     - 
     -
     -
   * - 
     - recompute
     - 
     -
     -
   * - 
     - drop
     - 
     -
     -
   * - :class:`~fdb.schema.Table`
     - create
     - 
     -
     -
   * - 
     - recreate
     - 
     -
     -
   * - 
     - drop
     - 
     -
     -
   * - :class:`~fdb.schema.TableColumn`
     - alter
     - 
     - 
     - One from next parameters required
   * - 
     - 
     - name
     - No
     - string
   * - 
     - 
     - datatype
     - No
     - string SQL type definition
   * - 
     - 
     - position
     - No
     - integer
   * - 
     - 
     - expression
     - No
     - string with COMPUTED BY expression
   * - :class:`~fdb.schema.View`
     - create
     - 
     -
     -
   * - 
     - recreate
     - 
     -
     -
   * - 
     - alter
     - columns
     - No
     - string or list of strings
   * - 
     - 
     - query
     - Yes
     - string
   * - 
     - 
     - check
     - No
     - True for WITH CHECK OPTION clause
   * - 
     - create_or_alter
     - 
     - 
     - 
   * - 
     - drop
     - 
     -
     -
   * - :class:`~fdb.schema.Trigger`
     - create
     - 
     -
     -
   * - 
     - recreate
     - 
     -
     -
   * - 
     - create_or_alter
     - 
     -
     -
   * - 
     - alter
     - 
     -
     - Requires parameters for either header or body definition.
   * - 
     - 
     - fire_on
     - No
     - string
   * - 
     - 
     - active
     - No
     - bool
   * - 
     - 
     - sequence
     - No
     - integer
   * - 
     - 
     - declare
     - No
     - string or list of strings
   * - 
     - 
     - code
     - No
     - string or list of strings
   * - 
     - drop
     - 
     -
     -
   * - :class:`~fdb.schema.Procedure`
     - create
     - no_code
     - No
     - True to supress procedure body from output
   * - 
     - recreate
     - no_code
     - No
     - True to supress procedure body from output
   * - 
     - create_or_alter
     - no_code
     - No
     - True to supress procedure body from output
   * - 
     - drop
     - 
     -
     -
   * - :class:`~fdb.schema.Role`
     - create
     - 
     -
     -
   * - 
     - drop
     - 
     -
     -
   * - :class:`~fdb.schema.Function`
     - declare
     - 
     -
     -
   * - 
     - drop
     - 
     -
     -
   * - :class:`~fdb.schema.DatabaseFile`
     - create
     - 
     -
     -
   * - :class:`~fdb.schema.Shadow`
     - create
     - 
     -
     -
   * - 
     - drop
     - 
     -
     -
   * - :class:`~fdb.schema.Privilege`
     - grant
     - grantors
     - No
     - List of grantor names. Generates GRANTED BY clause if grantor is not in list.
   * - 
     - revoke
     - grantors
     - No
     - List of grantor names. Generates GRANTED BY clause if grantor is not in list.
   * - 
     - 
     - grant_option
     - No
     - True to get REVOKE of GRANT/ADMIN OPTION only. Raises ProgrammingError if privilege
       doesn't have such option.

**Examples:**

.. code-block:: python

   >>> import fdb
   >>> con = fdb.connect(dsn='employee',user='sysdba', password='masterkey')
   >>> t = con.schema.get_table('EMPLOYEE')
   >>> print t.get_sql_for('create')
   CREATE TABLE EMPLOYEE
   (
     EMP_NO EMPNO NOT NULL,
     FIRST_NAME "FIRSTNAME" NOT NULL,
     LAST_NAME "LASTNAME" NOT NULL,
     PHONE_EXT VARCHAR(4),
     HIRE_DATE TIMESTAMP DEFAULT 'NOW' NOT NULL,
     DEPT_NO DEPTNO NOT NULL,
     JOB_CODE JOBCODE NOT NULL,
     JOB_GRADE JOBGRADE NOT NULL,
     JOB_COUNTRY COUNTRYNAME NOT NULL,
     SALARY SALARY NOT NULL,
     FULL_NAME COMPUTED BY (last_name || ', ' || first_name),
     PRIMARY KEY (EMP_NO)
   )
   >>> for i in t.indices:
   ...    if 'create' in i.actions:
   ...        print i.get_sql_for('create')
   ...      
   CREATE ASCENDING INDEX NAMEX
      ON EMPLOYEE (LAST_NAME,FIRST_NAME)
   >>> for c in [x for x in t.constraints if x.ischeck() or x.isfkey()]:
   ...    print c.get_sql_for('create')
   ... 
   ALTER TABLE EMPLOYEE ADD FOREIGN KEY (DEPT_NO)
     REFERENCES DEPARTMENT (DEPT_NO)
   ALTER TABLE EMPLOYEE ADD FOREIGN KEY (JOB_CODE,JOB_GRADE,JOB_COUNTRY)
     REFERENCES JOB (JOB_CODE,JOB_GRADE,JOB_COUNTRY)
   ALTER TABLE EMPLOYEE ADD CHECK ( salary >= (SELECT min_salary FROM job WHERE
                           job.job_code = employee.job_code AND
                           job.job_grade = employee.job_grade AND
                           job.job_country = employee.job_country) AND
               salary <= (SELECT max_salary FROM job WHERE
                           job.job_code = employee.job_code AND
                           job.job_grade = employee.job_grade AND
                           job.job_country = employee.job_country))
   >>> p = con.schema.get_procedure('GET_EMP_PROJ')
   >>> print p.get_sql_for('recreate',no_code=True)
   RECREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
   RETURNS (PROJ_ID CHAR(5))
   AS
   BEGIN
   END
   >>> print p.get_sql_for('create_or_alter')
   CREATE OR ALTER PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
   RETURNS (PROJ_ID CHAR(5))
   AS
   BEGIN
	FOR SELECT proj_id
		FROM employee_project
		WHERE emp_no = :emp_no
		INTO :proj_id
	DO
		SUSPEND;
   END 
   >>> print p.get_sql_for('alter',input=['In1 INTEGER','In2 VARCHAR(5)'],
   ... output='Out1 INETEGER,\nOut2 VARCHAR(10)',declare=['declare variable i integer = 1;'],
   ... code=['/* body */','Out1 = i',"Out2 = 'Value'"])
   ALTER PROCEDURE GET_EMP_PROJ (
     In1 INTEGER,
     In2 VARCHAR(5)
   )
   RETURNS (Out1 INETEGER,
   Out2 VARCHAR(10))
   AS
     declare variable i integer = 1;
   BEGIN
     /* body */
     Out1 = i
     Out2 = 'Value'
   END

Working with user privileges
============================

User or database object privileges are part of database metadata accesible through FDB 
:class:`~fdb.schema.Schema` support.
Each discrete privilege is represented by :class:`~fdb.schema.Privilege` instance. You can access either 
:attr:`all <fdb.schema.Schema.privileges>` privileges, or privileges granted for specific 
:attr:`table <fdb.schema.Table.privileges>`, :attr:`table column <fdb.schema.TableColumn.privileges>`, 
:attr:`view <fdb.schema.View.privileges>`, :attr:`view column <fdb.schema.ViewColumn.privileges>`, 
:attr:`procedure <fdb.schema.Procedure.privileges>` or :attr:`role <fdb.schema.Role.privileges>`. It's also
possible to get all privileges :meth:`granted to <fdb.schema.Schema.get_privileges_of>` specific user, role, 
procedure, trigger or view.

:class:`~fdb.schema.Privilege` class supports :meth:`~fdb.schema.Privilege.get_sql_for` method to generate
GRANT and REVOKE SQL statements for given privilege. If you want to generate grant/revoke statements for
set of privileges (for example all privileges granted on specific object or grated to specific user), it's
more convenient to use function :func:`~fdb.schema.get_grants` that returns list of minimal set of SQL 
commands required to task.

**Examples:**

.. code-block:: python

   >>> import fdb
   >>> con = fdb.connect(dsn='employee',user='sysdba', password='masterkey')
   >>> t = con.schema.get_table('EMPLOYEE')
   >>> for p in t.privileges:
   ...    print p.get_sql_for('grant')
   ... 
   GRANT SELECT ON EMPLOYEE TO SYSDBA WITH GRANT OPTION
   GRANT INSERT ON EMPLOYEE TO SYSDBA WITH GRANT OPTION
   GRANT UPDATE ON EMPLOYEE TO SYSDBA WITH GRANT OPTION
   GRANT DELETE ON EMPLOYEE TO SYSDBA WITH GRANT OPTION
   GRANT REFERENCES ON EMPLOYEE TO SYSDBA WITH GRANT OPTION
   GRANT SELECT ON EMPLOYEE TO PUBLIC WITH GRANT OPTION
   GRANT INSERT ON EMPLOYEE TO PUBLIC WITH GRANT OPTION
   GRANT UPDATE ON EMPLOYEE TO PUBLIC WITH GRANT OPTION
   GRANT DELETE ON EMPLOYEE TO PUBLIC WITH GRANT OPTION
   GRANT REFERENCES ON EMPLOYEE TO PUBLIC WITH GRANT OPTION
   >>> for p in fdb.schema.get_grants(t.privileges):
   ...    print p
   ...    
   GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON EMPLOYEE TO PUBLIC WITH GRANT OPTION
   GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON EMPLOYEE TO SYSDBA WITH GRANT OPTION

Normally generated GRANT/REVOKE statements don't contain grantor's name, because GRANTED BY clause
is supported only since Firebird 2.5. If you want to get GRANT/REVOKE statements including this clause,
use `grantors` parameter for `get_sql_for` and `get_grants`. This parameter is a list of grantor names,
and GRANTED BY clause is generated only for privileges not granted by user from this list. It's useful
to suppress GRANTED BY clause for SYSDBA or database owner.

Working with monitoring tables
==============================

Starting from Firebird 2.1 (ODS 11.1) its possible to monitor server-side activity happening inside a particular database. The engine offers a set of “virtual” tables (so-called "monitoring tables") that 
provides the user with a snapshot of the current activity within the given database. FDB provides access
to this information through set of classes (isolated in separate module :mod:`fdb.monitor`) that transform information stored in monitoring tables into set of Python objects that surface the information in meaningful way, and additionaly provide set of methods for available operations or checks.

Like database schema, monitoring tables could be accessed in two different ways, each suitable for different use case:

- By direct creation of :class:`fdb.monitor.Monitor` instances that are then binded to particular 
  :class:`~fdb.Connection` instance. This method is best if you want to work with monitoring data only occasionally, or you want to keep connections as lightweight as possible.
- Accessing :attr:`fdb.Connection.monitor` property. This method is more convenient than previous one, and
  represents a compromise between convenience and resource consumption because :class:`~fdb.monitor.Monitor` instance is not created until first reference and is managed by connection itself.

**Examples:**

1. Using Monitor instance:

.. code-block:: python

   >>> import fdb
   >>> con = fdb.connect(dsn='employee',user='sysdba', password='masterkey')
   >>> monitor = fdb.monitor.Monitor()
   >>> monitor.bind(con)
   >>> monitor.db.name
   '/opt/firebird/examples/empbuild/employee.fdb'

2. Using Connection.monitor:

.. code-block:: python

   >>> import fdb
   >>> con = fdb.connect(dsn='employee',user='sysdba', password='masterkey')
   >>> con.monitor.db.name
   '/opt/firebird/examples/empbuild/employee.fdb'

Available information
---------------------

The :class:`~fdb.monitor.Monitor` provides information about: 

- :attr:`Database <fdb.monitor.Monitor.db>`.
- :attr:`Connections <fdb.monitor.Monitor.attachments>` to database and :attr:`current <fdb.monitor.Monitor.this_attachment>` connection.
- :attr:`Transactions <fdb.monitor.Monitor.transactions>`.
- Executed :attr:`SQL statements <fdb.monitor.Monitor.statements>`.
- PSQL :attr:`callstack <fdb.monitor.Monitor.callstack>`.
- Page and row :attr:`I/O statistics <fdb.monitor.Monitor.iostats>`, including memory usage.
- :attr:`Context variables <fdb.monitor.Monitor.variables>`.

Activity snapshot
-----------------

The key term of the monitoring feature is an `activity snapshot`. It represents the current state of the
database, comprising a variety of information about the database itself, active attachments and users,
transactions, prepared and running statements, and more.

A snapshot is created the first time any of the monitoring information is being accessed from in the given
`Monitor` instance and it is preserved until :attr:`~fdb.monitor.Monitor.closed`, 
:meth:`clared <fdb.monitor.Monitor.clear>` or :meth:`refreshed <fdb.monitor.Monitor.refresh>`, in order 
that accessed information is always consistent.

There are two ways to refresh the snapshot:

1. Call :meth:`Monitor.clear() <fdb.monitor.Monitor.clear>` method. New snapshot will be taken on next 
   access to monitoring information.
2. Call :meth:`Monitor.refresh() <fdb.monitor.Monitor.refresh>` method to take the new snapshot immediately.

.. important::

   In both cases, any instances of information objects your application may hold would be obsolete. Using 
   them may result in error, or (more likely) provide outdated information.

.. note::

   Individual monitoring information (i.e. information about :attr:`connections <fdb.monitor.Monitor.attachments>`, :attr:`transactions <fdb.monitor.Monitor.transactions>` etc.) is 
   loaded from activity snapshot on first access and cached for further reference until it's 
   :meth:`clared <fdb.monitor.Monitor.clear>` or snapshot is :meth:`refreshed <fdb.monitor.Monitor.refresh>`.

   Because once loaded information is cached, it's good to :meth:`clar <fdb.monitor.Monitor.clear>` it
   when it's no longer needed to conserve memory.




.. _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
.. _Visitor Pattern: http://en.wikipedia.org/wiki/Visitor_pattern