Differences between revisions 15 and 16
Revision 15 as of 2007-09-03 15:24:17
Size: 7252
Comment:
Revision 16 as of 2007-09-03 16:48:06
Size: 7757
Comment: correct a few details about the database locked case with sqlite
Deletions are marked like this. Additions are marked like this.
Line 143: Line 143:
Stores using SQLite, in particular, can be difficult to work with in a multi-threaded environment. This is because SQLite has very limited support for concurrent connections. In particular, any call which attempts to modify the database can only succeed if there are no other connections open on the database. Roughly, this translates into Storm as a requirement that no other Store objects exist for that database. If such a call is made while there are other connections open, the call will wait for a little while for those connections to go away (and new connections will wait for the call to be completed before they can be opened). However, if the other connections don't go away within the timeout, the commit() call will raise an OperationalError, with the message "database is locked". Stores using SQLite, in particular, can be difficult to work with in a multi-threaded environment. This is because SQLite has very limited support for concurrent connections. In particular, any call which attempts to modify the database can only succeed if there are no other connections open on the database. Roughly, this translates into Storm as a requirement that no other Store objects exist for that database. If a call which attempts to modify the database is made while there are other connections open, the call check that there are no other connections, and if there are it will wait for a little while for those connections to go away (and new connections will be prevented from being opened while this wait is in progress). However, if the other connections don't go away within the timeout, the call will raise an OperationalError, with the message "database is locked".
Line 151: Line 151:
In most cases, if you get an exception from a call to the Store, SQLite will automatically roll back the current transaction. However, in the specific case of a commit() call which returns an OperationalError with the message "database is locked", SQLite will not roll back the transaction. You can then retry the commit() call (possibly after taking some action to get rid of the other open connections to the database). In most cases, if you get an exception from a call to the Store, you will need to call rollback() before continuing with use of the store. However, in the specific case of a call which returns an OperationalError with the message "database is locked", the store is left in a well defined state, such that you can simply retry the commit() call (possibly after taking some action to get rid of the other open connections to the database).

There is one exception to this: sqlite versions 3.3.0 to 3.3.17 inclusive have a bug which means that, if you get an OperationalError("database is locked") from any call other than commit(), further operations on the store can lead to database corruption unless you first call rollback(). More details of this bug are available at http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError

TableOfContents

Storm Manual

This is the detailed documentation for Storm. For a more hands-on approach, check out the [:Tutorial: Tutorial].

Databases

The create_database() function

The create_database() function has the following prototype:

database = create_database(uri)

The uri parameter may be either a URI string or a URI object. The following URIs are examples of acceptable syntax:

  • backend:database_name

  • backend://hostname/database_name

  • backend://hostname:port/database_name

  • backend://username:password@hostname/database_name

  • backend://hostname/database_name?option=value

  • backend://username@/database_name

MySQL

URIs

Dependencies

MySQL support depends on the [http://mysql-python.sf.net/ MySQLdb] module for Python.

PostgreSQL

URIs

Dependencies

PostgreSQL support depends on the [http://www.initd.org/tracker/psycopg/wiki/PsycopgTwo psycopg2] module for Python.

SQLite

Dependencies

SQLite support depends on the [http://www.initd.org/tracker/pysqlite/wiki/pysqlite pysqlite2] module for Python.

The Store interface

The ResultSet interface

Properties

What is a property in Storm

Common constructor parameters

Most properties accept the following properties. All of them are optional.

  • name: The column name in the database. Setting it allows the class attribute name and the column name in the database schema to differ. If unset, the name will be considered the same for the column and the class attribute.

  • primary: If true, this property is considered to map the primary key of the database table. Compound keys should be defined using the class-level __storm_primary__ attribute instead (see the section about compound keys for more information).

  • default: The default value for the property.

  • default_factory: A factory which returns default values for the property. This should be used when the default value is mutable.

Defining compound keys

To define a compound key in Storm, use the __storm_primary__ class-level attribute. It should be set to a tuple with the names of the properties composing the primary key. For example:

   1 >>> class FooBar(object):
   2 ...     __storm_table__ = "foo_bar"
   3 ...     __storm_primary__ = ("foo_id", "bar_id")
   4 ...     foo_id = Int()
   5 ...     bar_id = Int()

Table of properties vs. python vs. database types

Property

Python

PostgreSQL

MySQL

SQLite

Bool

bool

BOOL

TINYINT(1)

anything(*)

Int

int, long

SERIAL, BIGSERIAL, SMALLINT, INT, BIGINT

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT

anything(*)

Float

float

FLOAT, REAL, DOUBLE PRECISION

FLOAT, REAL, DOUBLE PRECISION

anything(*)

Decimal

Decimal

DECIMAL, NUMERIC, MONEY

DECIMAL, NUMERIC

anything(*)

Unicode

unicode

TEXT, VARCHAR, CHAR

TEXT, VARCHAR, CHAR

anything(*)

RawStr

str

BYTEA

BLOB, BINARY, VARBINARY

anything(*)

Pickle

any

BYTEA

BLOB, BINARY, VARBINARY

anything(*)

DateTime

datetime

TIMESTAMP

DATETIME, TIMESTAMP

anything(*)

Date

date

DATE

DATE

anything(*)

Time

time

TIME

TIME

anything(*)

TimeDelta

timedelta

INTERVAL

?

anything(*)

List

list

ARRAY[]

?

anything(*)

(*) SQLite won't enforce data types, so they just must be formatted properly.

References

One-to-one

Many-to-one

Many-to-many

Using on_remote

The Reference interface

The ReferenceSet interface

Expressions

Managing stores

Working with multiple threads

Working with multiple threads

Store objects are not threadsafe. This means that each Store must be accessed from only a single thread at a time. In fact, some databases (eg, SQLite) will require that the Store is only accessed by the thread in which it was created, which means that you can't keep a global pool of Stores and pass them to threads as needed, or simply implement a mutex around a single Store.

Therefore, the best policy is usually to create a Store object for each thread which needs one. One convenient way to manage this is to implement a "threadsafe" Store manager, which creates a Store for each thread when that thread first requests one, and then keeps the Store cached in a thread-local dictionary. This approach is taken by ZStorm to provide Store objects to each thread.

Note that Database objects are able to be shared between threads, and accessed concurrently, so you only need to create one of these for each underlying database.

SQLite and threads

Stores using SQLite, in particular, can be difficult to work with in a multi-threaded environment. This is because SQLite has very limited support for concurrent connections. In particular, any call which attempts to modify the database can only succeed if there are no other connections open on the database. Roughly, this translates into Storm as a requirement that no other Store objects exist for that database. If a call which attempts to modify the database is made while there are other connections open, the call check that there are no other connections, and if there are it will wait for a little while for those connections to go away (and new connections will be prevented from being opened while this wait is in progress). However, if the other connections don't go away within the timeout, the call will raise an OperationalError, with the message "database is locked".

By default, SQLite will wait for the other connections to go away for 5 seconds. However, this timeout can be changed by specifying a timeout option in the URI when opening the database. For example, the following command opens a database with a timeout of 0.3 seconds:

   1 >>> database = create_database("sqlite:foo?timeout=0.3")

In most cases, if you get an exception from a call to the Store, you will need to call rollback() before continuing with use of the store. However, in the specific case of a call which returns an OperationalError with the message "database is locked", the store is left in a well defined state, such that you can simply retry the commit() call (possibly after taking some action to get rid of the other open connections to the database).

There is one exception to this: sqlite versions 3.3.0 to 3.3.17 inclusive have a bug which means that, if you get an OperationalError("database is locked") from any call other than commit(), further operations on the store can lead to database corruption unless you first call rollback(). More details of this bug are available at http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError

Using a global function

Using a base class

Creating a backend

Testing

It's easy to test that Storm features are working with new backends. There are base test classes in tests/store/database.py and tests/store/base.py which may be inherited by database-specific test cases. Look at other backends to get a feeling of how it works.

If these tests pass with a new backend, you may be pretty sure that Storm is working fine with the created backend.

Manual (last edited 2009-12-05 20:36:43 by gabrielgrant)