Usage Guide¶
Driver structure¶
Source code is currently divided into next submodules:
ibase
- Python ctypes interface to InterBase client library.ibcore
- Main driver source code.services
- Driver code to work with InterBase Services.schema
- Driver code to work with InterBase database schema (metadata).utils
- Various classes and functions used by driver that are generally useful.
All important data, functions, classes and constants are available directly in interbase namespace,
so there is not need to import or use ibcore
and ibase
submodules directly.
Exception is the interbase.services
submodule that contains functions and classes for work with
InterBase Services. Because Services are optional, not so frequently used InterBase facility, all
service-related code was isolated in separate submodule rather than exposed directly through
main module namespace. Because services
submodule contains names also used by main
driver (connect()
, Connection
), it’s advised to use
fully qualified names when referring to them instead importing them via from interbase.services import ….
Databases¶
Access to the database is made available through Connection
objects. Interbase provides two
constructors for these:
connect()
- Returns Connection to database that already exists.create_database()
- Returns Connection to newly created database.
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 or host 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 omitted), it’s recommended practice to use them. Parameter role is needed only when you use InterBase roles.
Connection options are optional (see InterBase 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 Interbase driver (see Data handling and conversions for details).
Examples:
# Connecting via 'dsn'
#
# Local database (local protocol, if supported)
con = interbase.connect(dsn='/path/database.interbase', user='sysdba', password='pass')
# Local database (TCP/IP)
con = interbase.connect(dsn='localhost:/path/database.interbase', user='sysdba', password='pass')
# Local database (TCP/IP with port specification)
con = interbase.connect(dsn='localhost/3050:/path/database.interbase', user='sysdba', password='pass')
# Remote database
con = interbase.connect(dsn='host:/path/database.db', user='sysdba', password='pass')
# Remote database with port specification
con = interbase.connect(dsn='host/3050:/path/database.db', user='sysdba', password='pass')
#
# Connecting via 'database', 'host' and 'port'
#
# Local database (local protocol, if supported)
con = interbase.connect(database='/path/database.db', user='sysdba', password='pass')
# Local database (TCP/IP)
con = interbase.connect(host='localhost', database='/path/database.db', user='sysdba', password='pass')
# Local database (TCP/IP with port specification)
con = interbase.connect(host='localhost/3050', database='/path/database.db', user='sysdba', password='pass')
# Remote database
con = interbase.connect(host='myhost', database='/path/database.db', user='sysdba', password='pass')
Interbase supports additional Connection class(es) that extend Connection
functionality
in optional (opt-in) way. For example ConnectionWithSchema
extends Connection interface with methods
and attributes provided by 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:
# Connecting through ConnectionWithSchema
#
con = interbase.connect(dsn='/path/database.interbase', user='sysdba', password='pass',
connection_class=interbase.ConnectionWithSchema)
Using create_database¶
The InterBase engine supports dynamic database creation via the SQL statement CREATE DATABASE.
Interbase wraps it into create_database()
, that returns Connection instance attached to newly
created database.
Example:
con = interbase.create_database("create database 'host:/temp/db.db' user 'sysdba' password 'pass'")
Note
Instead CREATE DATABASE SQL statement you can use number of optional keyword parameters
introduced to create_database()
.
Example:
con = interbase.create_database(dsn='/temp/db.interbase',user='sysdba',password='pass',page_size=8192)
Deleting databases¶
The InterBase 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
Interbase exposes as drop_database()
method in Connection
class. So, to drop
a database you need to connect to it first.
Examples:
import interbase
con = interbase.create_database("create database '/temp/db.db' user 'sysdba' password 'pass'")
con.drop_database()
con = interbase.connect(dsn='/path/database.interbase', user='sysdba', password='pass')
con.drop_database()
Connection object¶
Connection
object represents a direct link to database, and works as gateway for next operations
with it:
Executing SQL Statements: methods
execute_immediate()
andcursor()
.Dropping database: method
drop_database()
.Transanction management: methods
begin()
,commit()
,rollback()
,savepoint()
,trans()
,trans_info()
andtransaction_info()
, and attributesmain_transaction
,transactions
,default_tpb
andgroup
.Work with Database Events: method
event_conduit()
.Getting information about database: methods
db_info()
anddatabase_info()
.Getting information about database metadata: attribute
schema
andods
.
Getting information about InterBase version¶
Interbase provides version strings as Connection properties:
server_version
- Legacy InterBase-friendly version string.version
- Only InterBase version number. It’s a string with format: major.minor.subrelease.buildengine_version
- Engine (major.minor) version as (float) number.
Database On-Disk Structure¶
Particular InterBase features may also depend on specific support in database.
These required structures are present automatically when database is created by particular
engine version that needs them, but InterBase 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. Interbase provides this number as Connection.ods
(float)
property.
Example:
con = interbase.connect(dsn='/path/database.interbase', user='sysdba', password='pass')
print 'InterBase version:',con.version
print 'ODS version:',con.ods
Getting information about database¶
InterBase provides various information about server and connected database via database_info API call.
Interbase surfaces this API through methods db_info()
and database_info()
on Connection object.
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(interbase.isc_info_user_names, 's')
will return a binary string containing a raw succession of length-name pairs.
Example program:
import interbase
con = interbase.connect(dsn='localhost:/temp/test.db', user='sysdba', password='pass')
# Retrieving an integer info item is quite simple.
bytesInUse = con.database_info(interbase.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 interbase.ibase.ord2 is provided to convert a raw
# byte to a Python integer (see examples below).
buf = con.database_info(interbase.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 = interbase.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 = interbase.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 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(interbase.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:
import interbase
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 = interbase.connect(dsn='localhost:/temp/test.db', user='TEST_USER_1', password='pass')
testUserCons.append(tcon)
con = interbase.connect(dsn='localhost:/temp/test.db', user='sysdba', password='pass')
print 'Open connections to this database:'
print con.db_info(interbase.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([interbase.isc_info_page_size, interbase.isc_info_allocation])
pagesAllocated = res[interbase.isc_info_allocation]
pageSize = res[interbase.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
Executing SQL Statements¶
Interbase implements two ways for execution of SQL commands against connected database:
execute_immediate()
- for execution of SQL commands that don’t return any result.Cursor
objects that offer rich interface for execution of SQL commands and fetching their results.
Cursor object¶
Because Cursor objects always operate in context of single Connection
(and 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 cursor()
(and other transaction-related methods) as part
of Connection
interface. However, InterBase supports multiple independent transactions per
connection. To conform to Python DB API, Interbase uses concept of internal main_transaction
and secondary transactions
. Cursor constructor is primarily defined by
Transaction
, and Cursor constructor on Connection is therefore a shortcut for main_transaction.cursor().
Cursor objects are used for next operations:
Execution of SQL Statements: methods
execute()
,executemany()
andcallproc()
.Creating
PreparedStatement
objects for efficient repeated execution of SQL statements, and to obtain additional information about SQL statements (like executionplan
): methodprep()
.Fetching results: methods
fetchone()
,fetchmany()
,fetchall()
,fetchonemap()
,fetchmanymap()
,fetchallmap()
,iter()
,itermap()
andnext()
.
SQL Execution Basics¶
There are three methods how to execute SQL commands:
Connection.execute_immediate()
orTransaction.execute_immediate()
for SQL commands that don’t return any result, and are not executed frequently. This method also doesn’t support either parameterized statements or prepared statements.Tip
This method is efficient for administrative and DDL SQL commands, like DROP, CREATE or ALTER commands, SET STATISTICS etc.
Cursor.execute()
orCursor.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 parameterized form.
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.
Parameterized 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.:
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:
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 parameterized) has several important advantages:
You don’t need to handle conversions from Python data types to strings.
Interbase will handle all data type conversions (if necessary) from Python data types to InterBase 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 Interbase will handle the transfer of BLOB value.
If you’ll pass exactly the same command string again to particular
Cursor
instance, it will be executed more efficiently (see section about Prepared Statements for details).
Parameterized statements also have some limitations. Currently:
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. Cursor
object offer number of different
methods for fetching these rows, that should satisfy all your specific needs:
fetchone()
- Returns the next row of a query result set, or None when no more data is available.Tip
Cursor supports the iterator protocol, yielding tuples of values like
fetchone()
.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
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
arraysize
is 1, so without parameter it’s equivalent tofetchone()
, but returns list of rows, instead actual row directly.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
fetchone()
, Cursor’s built-in support for iterator protocol or call toiter()
instead this method.fetchonemap()
- Returns the next row likefetchone()
, but returns a mapping of field name to field value, rather than a tuple.fetchmanymap()
- Returns the next set of rows of a query result likefetchmany()
, but returns a list of mapping of field name to field value, rather than a tuple.fetchallmap()
- Returns all (remaining) rows of a query result likefetchall()
, 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
itermap()
instead this method.iter()
- Equivalent to thefetchall()
, except that it returns iterator rather than materialized list.itermap()
- Equivalent to thefetchallmap()
, except that it returns iterator rather than materialized list.Call to
execute()
returns self (Cursor instance) that itself supports the iterator protocol, yielding tuples of values likefetchone()
.
Important
Interbase makes absolutely no guarantees about the return value of the fetchone / fetchmany / fetchall methods except that it is a sequence indexed by field position. Interbase 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:
import interbase
con = interbase.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
Cursor.execute()
and Cursor.executemany()
return self, so you can use calls to them
as iterators (see example above).
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 transferred 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. InterBase (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.
Interbase builds on this by encapsulating all statement-related code into separate PreparedStatement
class, and implementing Cursor
class as a wrapper around it.
Warning
Interbase’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 acquire and use PreparedStatement instances explicitly.
PreparedStatement are acquired by calling prep()
method could be then passed to execute()
or executemany()
instead command string.
Example:
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 description
, execution plan
or
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.
import time
import interbase
con = interbase.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 == interbase.isc_info_sql_stmt_insert:', (
psIns.statement_type == interbase.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 == interbase.isc_info_sql_stmt_select:', (
psSel.statement_type == interbase.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 == interbase.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 == interbase.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/pyinterbase/interbase/interbase/ibcore.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.
Named Cursors¶
To allow the Python programmer to perform scrolling UPDATE or DELETE via the “SELECT … FOR UPDATE”
syntax, Interbase provides the read/write property Cursor.name
.
Example Program:
import interbase
con = interbase.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()
Working with stored procedures¶
InterBase 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; InterBase 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.
InterBase’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 Interbase, use code such as this:
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:
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 Cursor.callproc()
. The Python DB API specification requires the current behavior,
however.
Data handling and conversions¶
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 Interbase 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:
create table test_table (i integer, t timestamp)
i = 1
t = 'now'
sqlWithMagicValues = "insert into test_table (i, t) values (?, '%s')" % t
cur.execute( sqlWithMagicValues, (i,) )
i = 1
t = 'now'
cur.execute( "insert into test_table (i, t) values (?, ?)", (i, t) )
If Interbase 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 Interbase 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 Interbase 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.
Automatic conversion from/to unicode¶
In InterBase, 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 manipulation, 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 connection
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.
Interbase 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 Interbase makes no assumption 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, Interbase uses
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, Interbase 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 InterBase 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 InterBase Service and info calls etc. When connection charset is not specified, Interbase uses
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.
Working with BLOBs¶
Interbase 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 parameterized statement and pass any file-like object in place of BLOB parameter. The file-like object must implement only the
read()
method, as no other method is used.For output values, you have to call
Cursor.set_stream_blob()
(orPreparedStatement.set_stream_blob()
) method with specification of column name(s) that should be returned as file-like objects. Interbase then returnsBlobReader
instance instead string in place of returned BLOB value for these column(s).
The 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 additional 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 next()
, read()
,
readline()
, readlines()
or seek()
. However, it’s
good practice to 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 BlobReader.seek()
method will raise
DatabaseError
exception. This constraint is set by InterBase.
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 impact on performance too, as new BlobReader instance is created for each value fetched.
Example program:
import os.path
from cStringIO import StringIO
import interbase
con = interbase.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; interbase.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 interbase.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 interbase.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
InterBase ARRAY type¶
Interbase supports InterBase 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 InterBase 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:
>>> import interbase
>>> con = interbase.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:
import interbase
con = interbase.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]]
Transanction management¶
For the sake of simplicity, Interbase 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 InterBase 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 Interbase establishes a transaction implicitly by using any SQL
execution method, such as execute_immediate()
, Cursor.execute()
, or
Cursor.callproc()
.
Although Interbase 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 Connection
object has to
respond to the following methods:
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.
(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, Interbase allows the programmer
to start transactions explicitly via the Connection.begin()
method. Also Connection.savepoint()
method was added to provide support for InterBase SAVEPOINTs.
But Python Database API 2.0 was created with assumption that connection can support only one transactions per single connection. However, InterBase 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 opened simultaneously to perform various tasks, which would require to open multiple connections and thus consume more resources than necessary.
Interbase surfaces this InterBase feature by separating transaction management out from Connection
into
separate Transaction
objects. To comply with Python DB API 2.0 requirements, Connection object
uses one Transaction instance as main transaction
, and delegates
begin()
, savepoint()
, commit()
,
rollback()
, trans_info()
and transaction_info()
calls to it.
See also
More about using multiple transactions with the same connection in separate section.
Example:
import interbase
con = interbase.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()
Auto-commit¶
Interbase doesn’t support auto-commit feature directly, but developers may achieve the similar result
using explicit transaction start, taking advantage of default_action
and its
default value (commit).
Example:
import interbase
con = interbase.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()
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 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
begin()
method and pass a TPB for that single transaction.
For details about TPB construction, see the InterBase API documentation. In particular, the ibase.h supplied with InterBase contains all possible TPB elements – single bytes that the C API defines as constants whose names begin with isc_tpb_. Interbase 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; Interbase requires that TPBs be constructed as Python strings (or bytes for Python 3). Since the constants in the interbase.isc_tpb_* family are numbers, they can’t be simply concatenated to create a TPB, but you may use utility function interbase.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:
from interbase 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 InterBase 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).
Interbase 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 InterBase.
- ISOLATION_LEVEL_READ_COMMITED_RO
Like ISOLATION_LEVEL_READ_COMMITED, but Read Only.
Tip
InterBase threats these transactions as pre-committed, so they are best option for long running transactions that only read data.
Internally Interbase uses such transaction to read metadata from connected database. This internal transaction is also available to developers for convenience as
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:
import interbase
con = interbase.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 = interbase.ISOLATION_LEVEL_REPEATABLE_READ
con.begin()
cur.execute('select * from JOB')
com.commit()
# Start THIS transaction as R/O READ COMMITTED
con.begin(interbase.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, Interbase offers utility
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 rendered
binary
tpb block or TPB instance itself to set default_tpb or as parameter to begin().
Example:
import interbase
con = interbase.connect(dsn='localhost:employee',user='sysdba', password='masterkey')
# Use TPB to construct valid transaction parameter block
# from the interbase.isc_tpb_* family.
customTPB = interbase.TPB()
customTPB.isolation_level = interbase.isc_tpb_consistency # SERIALIZABLE
customTPB.table_reservation["MY_TABLE"] = (interbase.isc_tpb_protected, interbase.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 = interbase.TPB()
customTPB.access_mode = interbase.isc_tpb_read # read only
customTPB.isolation_level = interbase.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 TableReservation
instead TPB.
Getting information about transaction¶
Transaction
object exposes two methods that return information about currently managed
active transaction (the same methods are exposed also by Connection
object for
main_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 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:
import interbase
con = interbase.connect(dsn='localhost:employee',user='sysdba', password='masterkey')
# Start transaction, so we can get information about it
con.begin()
info = con.trans_info([interbase.isc_info_tra_id, interbase.isc_info_tra_oldest_interesting,
interbase.isc_info_tra_oldest_snapshot, interbase.isc_info_tra_oldest_active,
interbase.isc_info_tra_isolation, interbase.isc_info_tra_access,
interbase.isc_info_tra_lock_timeout])
print info
print "TransactionID:", info[interbase.isc_info_tra_id]
print "Oldest Interesting (OIT):",info[interbase.isc_info_tra_oldest_interesting]
print "Oldest Snapshot:",info[interbase.isc_info_tra_oldest_snapshot]
print "Oldest Active (OAT):",info[interbase.isc_info_tra_oldest_active]
print "Isolation Level:",info[interbase.isc_info_tra_isolation]
print "Access Mode:",info[interbase.isc_info_tra_access]
print "Lock Timeout:",info[interbase.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 Interbase 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 Interbase constants isc_info_tra_readonly and isc_info_tra_readwrite.
Retaining transactions¶
The commit()
and 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 by Ann Harrison.
For more information about retaining transactions, see InterBase documentation.
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 InterBase savepoints are fully supported from SQL alone via the SAVEPOINT ‘name’ and ROLLBACK TO ‘name’ statements, Interbase also exposes savepoints at the Python API level for the sake of convenience.
Call to method savepoint()
establishes a savepoint with the specified name. To roll back
to a specific savepoint, call the 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 Interbase API, rather than raw SQL.
import interbase
con = interbase.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:
[]
Using multiple transactions with the same connection¶
To use additional transactions that could run simultaneously with
main transaction
managed by Connection, create new
Transaction
object calling Connection.trans()
method. If you don’t specify the optional
default_tpb parameter, this new Transaction inherits the 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 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 cursor()
method, or call
Transaction.execute_immediate()
method.
Example:
import interbase
con = interbase.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(interbase.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()
Distributed Transactions¶
Distributed transactions are transactions that span multiple databases. Interbase provides this InterBase feature
through ConnectionGroup
class. Instances of this class act as managers for Transaction
object that is bound to multiple connections, and to cursors
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 Connection
instances to
ConnectionGroup
constructor, or add connections latter calling ConnectionGroup.add()
method.
Any 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 closed
before it’s removed
or whole group is
disbanded
.
Warning
Never add more than one connection to the same database to the same ConnectionGroup!
Similarly to Transaction
, distributed transactions are managed through ConnectionGroup.begin()
,
ConnectionGroup.savepoint()
. ConnectionGroup.commit()
and ConnectionGroup.rollback()
methods.
Additionally, ConnectionGroup exposes method prepare()
that explicitly initiates
the first phase of Two-Phase Commit Protocol. Transaction parameters are defined similarly to
Transaction
using ConnectionGroup.default_tpb
or as optional parameter to
begin()
call.
SQL statements that should belong to context of distributed transaction are executed via Cursor
instances acquired through ConnectionGroup.cursor()
method, or calling
ConnectionGroup.execute_immediate()
method.
Note
Because Cursor
instances can belong to only one Connection
, the cursor()
method has mandatory parameter connection, to specify to which member connection cursor should belong.
Example program:
import interbase
# First database
con1 = interbase.create_database("CREATE DATABASE 'testdb-1.interbase' USER 'SYSDBA' PASSWORD 'masterkey'")
con1.execute_immediate("recreate table T (PK integer, C1 integer)")
con1.commit()
# Second database
con2 = interbase.create_database("CREATE DATABASE 'testdb-2,interbase' USER 'SYSDBA' PASSWORD 'masterkey'")
con2.execute_immediate("recreate table T (PK integer, C1 integer)")
con2.commit()
# Create connection group
cg = interbase.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¶
Interbase provides context manager TransactionContext
that allows automatic transaction
management using The with statement. It can work with any object that supports begin(), commit() and
rollback() methods, i.e. Connection
, ConnectionGroup
or Transaction
.
It starts transaction when WITH block is entered and commits it if no exception occurs within it, or calls rollback() otherwise. Exceptions raised in WITH block are never suppressed.
Examples:
con = interbase.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 = interbase.connect(dsn='remote:employee',user='sysdba',password='masterkey')
cg = interbase.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')")
Database Events¶
What they are¶
The InterBase 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 InterBase 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:
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 Interbase’s Python-level event handling API.
The InterBase C client library offers two forms of event notification. The first form is synchronous notification, by way of the function
isc_wait_for_event()
. This form is admirably simple for a C programmer to use, but is inappropriate as a basis for Interbase’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 functionsisc_que_events()
(note that the name of that function is misspelled),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
isc_event_block()
to create a formatted binary buffer that will tell the server which events the client wants to listen for.Call
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
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
isc_event_counts()
function to determine how many times each of the registered events has occurred since the last call toisc_event_counts()
(if any).[The callback thread should now “do its thing”, which may include communicating with the thread that called
isc_que_events()
.]When the callback thread is finished handling an event notification, it must call
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 Interbase database event API is comprised of the following: the method Connection.event_conduit()
and the class EventConduit
.
The 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 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).
Connection.event_conduit()
takes a sequence of string event names as parameter, and returns
EventConduit
instance.
Important
To start listening for events it’s necessary to call EventConduit.begin()
method or use EventConduit’s context manager interface.
Immediately when 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
close()
method) or implicitly (via garbage collection).
Notifications about events are acquired through call to 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 begin()
and close()
methods.
It’s strongly advised to use the EventConduit with the with statement.
Example:
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 EventConduit.flush()
method.
Example program:
import interbase
import threading
import time
# Prepare database
con = interbase.create_database("CREATE DATABASE 'event_test.interbase' 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}
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 gbak, gfix, gsec, and 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, InterBase 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, Interbase presents its own abstraction over the native API
via the interbase.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 interbase.services.Connection
class. Similarly to database connections, Interbase provides
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 interbase.services.connect()
in the following program are equivalent:
from interbase 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 close()
them when you don’t need them
anymore.
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 InterBase 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 iterator protocol support on Connection.
Server Configuration and State¶
To help client programs adapt to version changes, the service manager exposes its version number as an integer.
>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') >>> print con.get_service_manager_version() 2interbase.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.
Returns the server’s version string
>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') >>> print con.get_server_version()At first glance, this method appears to duplicate the functionality of the
interbase.Connection.server_version
property, but when working with InterBase, there is a difference.interbase.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 InterBase 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.InterBase addresses this problem by making isc_database_info() return a “pseudo-InterBase” version number, whereas the Services API returns the true InterBase version, as shown:
import interbase con = interbase.connect(dsn='employee', user='sysdba', password='masterkey') print 'Interbase-compatible version string:', con.server_version svcCon = interbase.services.connect(password='masterkey') print 'Actual InterBase version string: ', svcCon.get_server_version()
Returns platform information for the server, including hardware architecture and operating system family.
>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') >>> print con.get_architecture() InterBase/linux AMD64
Returns the equivalent of the RootDirectory setting from
interbase.conf
.>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') >>> print con.get_home_directory() /opt/interbase/
Returns the location of the server’s core security database, which contains user definitions and such. Name of this database is
admin.ib
.>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') >>> print con.get_security_database_path() /opt/interbase/security2.interbase
Returns the directory location for InterBase lock files.
>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') >>> print con.get_lock_file_directory() /tmp/interbase/
Returns tuple of capability info codes for each capability reported by InterBase server. Following constants are defined in
interbase.services
for convenience:>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') >>> print con.get_server_capabilities() (2L, 4L, 512L, 256L) >>> interbase.services.CAPABILITY_MULTI_CLIENT in con.get_server_capabilities() True >>> interbase.services.CAPABILITY_QUOTED_FILENAME in con.get_server_capabilities() False
To support internationalized error messages/prompts, the database engine stores its messages in a file named
interbase.msg
. The directory in which this file resides can be determined with this method.>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') >>> print con.get_message_file_directory() /opt/interbase/
Returns the number of active connections to databases managed by the server. This count only includes database connections (such as open instances of
interbase.Connection
), not services manager connections (such as open instances ofinterbase.services.Connection
).>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') >>> db1 = interbase.connect(dsn='employee',user='sysdba',password='masterkey') >>> db2 = interbase.connect(dsn='employee',user='sysdba',password='masterkey') >>> print con.get_connection_count() 2
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.
>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') >>> db1 = interbase.connect(dsn='employee',user='sysdba',password='masterkey') >>> db2 = interbase.connect(dsn='employee',user='sysdba',password='masterkey') >>> print con.get_attached_database_names() ['/opt/interbase/examples/empbuild/employee.interbase']
Request the contents of the server’s log file (
interbase.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 .
>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') >>> con.get_log() >>> log = con.readlines()
Database options¶
Sets individual page cache size for Database.
>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') >>> con.set_default_page_buffers('employee',100)
Sets threshold for automatic sweep.
>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') >>> con.set_sweep_interval('employee',100000)
Sets data page space reservation policy.
>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') # Use all space >>> con.set_reserve_page_space('employee',False)
Sets Disk Write Mode: Sync (forced writes) or Async (buffered). Following constants are defined in
interbase.services
for convenience:>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') # Disable Forced Writes >>> con.set_write_mode('employee',services.WRITE_BUFFERED)
Sets Database Access mode: Read Only or Read/Write. Following constants are defined in
interbase.services
for convenience:>>> from interbase 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)
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.
>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') # Use SQL dialect 1 >>> con.set_sql_dialect('employee',1)
Database maintenance¶
Returns list of transactions in limbo.
Resolves limbo transaction with commit.
Resolves limbo transaction with rollback.
Request database statistics. 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
wait()
), any attempt to start another asynchronous service will fail with exception.>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') >>> con.get_statistics('employee') >>> stat_report = con.readlines()
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
wait()
), any attempt to start another asynchronous service will fail with exception.>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') >>> con.backup('employee', '/home/data/employee.ibk', metadata_only=True, collect_garbage=False) >>> backup_report = con.readlines()
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
wait()
), any attempt to start another asynchronous service will fail with exception.>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') >>> con.restore('/home/data/employee.ibk', '/home/data/empcopy.interbase') >>> restore_report = con.readlines()
Bring previously shut down database back online. Following constants are defined in
interbase.services
for convenience:For on-line mode:
>>> from interbase 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')
Activates Database Shadow(s).
Performs Database Sweep.
Note
Method call will not return until sweep is finished.
>>> from interbase import services >>> con = services.connect(host='localhost', user='sysdba', password='masterkey') >>> con.sweep('empoyee')
Database Validation and Repair.
Note
Method call will not return until action is finished.
>>> from interbase 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 maintenance¶
Returns information about specified user or all users as a list of
User
instances.>>> from interbase 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
Adds new user. Requires instance of
User
with at least itsname
andpassword
attributes specified as non-empty values. All other attributes are optional.>>> from interbase 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)
Modification of user information. Requires instance of
User
with at least itsname
attribute specified as non-empty value.Note
Sets
first_name
,middle_name
andlast_name
to their actual values, and ignores theuser_id
andgroup_id
attributes regardless of their values.password
is set only when it has value.>>> from interbase 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)
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, Interbase isolated the transfer process to separate methods:
readline()
- Similar tofile.readline()
, returns next line of output from Service.readlines()
- Likefile.readlines()
, returns list of output lines.Iteration over Connection object, because
Connection
has built-in support for iterator protocol.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.
wait()
- Waits for Service 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 InterBase Service API.
You may check the status of asynchronous Services using Connection.fetching
attribute or
Connection.isrunning()
method.
In cases when you’re not interested in output produced by Service, call wait()
to
wait for service to complete.
Examples:
import interbase
svc = interbase.services.connect(password='masterkey')
print "Fetch materialized"
print "=================="
print "Start backup"
svc.backup('employee', 'employee.ibk')
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.ibk')
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.ibk', 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.ibk
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
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. InterBase 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 InterBase versions further add to this
complexity. Hence Interbase provides set of classes (isolated in separate module interbase.schema
) that
transform information stored in system tables into set of Python objects that surface the vital information
in meaningful way, and additionally 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
interbase.schema.Schema
instances that are thenbinded
to particularConnection
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
interbase.Connection.schema
property. This method is more convenient than previous one, and represents a compromise between convenience and resource consumption becauseSchema
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
ConnectionWithSchema
insteadConnection
by specifying connection_class=ConnectionWithSchema parameter toconnect()
orcreate_database()
. This Connection descendant loads all database metadata immediately and provides directly all attributes and methods provided bySchema
class. This method is most suitable in case you want to work with database metadata extensively.
Examples:
Using Schema instance:
>>> import interbase
>>> con = interbase.connect(dsn='employee',user='sysdba', password='masterkey')
>>> schema = interbase.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']
Using Connection.schema:
>>> import interbase
>>> con = interbase.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']
Using ConnectionWithSchema:
>>> import interbase
>>> con = interbase.connect(dsn='employee',user='sysdba', password='masterkey',
connection_class=interbase.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 domains
, tables
etc.) is loaded on first access and cached for further reference until it’s cleared
or reload
is
requested.
Because once loaded information is cached, it’s good to clear
it
when it’s no longer needed to conserve memory.
Available information¶
The Schema
provides information about:
Database:
Owner name
,default character set
,description
,security class
and whether database consist fromsingle or multiple files
.Facilities: Available
character sets
,collations
, databasefiles
andshadows
.User database objects:
exceptions
,generators
,domains
,tables
and theirconstraints
,indices
,views
,triggers
,procedures
, userroles
anduser defined functions
.System database objects:
generators
,domains
,tables
and their constraints,indices
,views
,triggers
,procedures
andfunctions
.Relations between objects: Through direct links between metadata objects and
dependencies
.Privileges:
All
privileges, or privileges granted for specifictable
,table column
,view
,view column
,procedure
orrole
. It’s also possible to get all privilegesgranted to
specific user, role, procedure, trigger or view.
Metadata objects¶
Schema information is presented as Python objects of various classes with common parent class
BaseSchemaItem
(except Schema
itself), that defines several
common attributes and methods:
Attributes:
name
: Name of database object or None if object doesn’t have a name.description
: Description (documentation text) for object or None if object doesn’t have a description.actions
: List of supported SQL operations on schema object instance.
Methods:
issystemobject()
: Returns True if this database object is system object.get_quoted_name()
: Returns quoted (if necessary) name of database object.get_dependents()
: Returns list of all database objects that depend on this one.get_dependencies()
: Returns list of database objects that this object depend on.get_sql_for()
: Returns SQL command string for specified action on database object.
There are next schema objects: Collation
, CharacterSet
,
DatabaseException
, Domain
,
Index
, Table
, TableColumn
,
Constraint
, View
, ViewColumn
,
Trigger
, Procedure
, ProcedureParameter
,
Function
, FunctionArgument
, Role
,
Dependency
, DatabaseFile
and Shadow
.
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 Schema
) supports
accept_visitor()
method that calls class-specific method on object passed
to it as parameter. This method name always starts with ‘visit’. For example Table
object
calls visitTable. This ‘visit*’ method has one parameter - object instance that calls it.
Interbase provides skeleton implementation for schema Visitor object
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 - dependent objects before it could be dropped.
import interbase
# Object dropper
class ObjectDropper(interbase.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 = interbase.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¶
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 relations InterBase 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 InterBase tracks these dependencies.
Interbase surfaces these dependencies as Dependency
schema objects, and all schema objects
have get_dependents()
and get_dependencies()
methods to get list of Dependency
instances that describe these dependencies.
Dependency
object provides names and types of dependent/depended on database objects,
and access to their respective schema Python objects as well.
SQL operations¶
Interbase doesn’t allow you to change database metadata directly using schema objects. Instead it supports
generation of DDL SQL commands from schema objects using 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
actions
attribute.
Possible actions could be: create, recreate, create_or_alter, alter, drop, activate, deactivate, recompute and declare. Some actions require/allow additional parameters.
Schema class |
Action |
Parameter |
Required |
Description |
---|---|---|---|---|
create |
||||
drop |
||||
alter |
collation |
Yes |
|
|
create |
||||
recreate |
||||
alter |
message |
Yes |
string. |
|
create_or_alter |
||||
drop |
||||
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 |
||||
create |
||||
drop |
||||
create |
||||
activate |
||||
deactivate |
||||
recompute |
||||
drop |
||||
create |
||||
recreate |
||||
drop |
||||
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 |
||
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 |
||||
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 |
||||
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 |
||||
create |
||||
drop |
||||
declare |
||||
drop |
||||
create |
||||
create |
||||
drop |
||||
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:
>>> import interbase
>>> con = interbase.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 accessible through Interbase
Schema
support.
Each discrete privilege is represented by Privilege
instance. You can access either
all
privileges, or privileges granted for specific
table
, table column
,
view
, view column
,
procedure
or role
. It’s also
possible to get all privileges granted to
specific user, role,
procedure, trigger or view.
Privilege
class supports 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 get_grants()
that returns list of minimal set of SQL
commands required to task.
Examples:
>>> import interbase
>>> con = interbase.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 interbase.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 InterBase 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.