* Thomas Vander Stichele <thomas@apestaart.org> [2006-06-08 12:05:22 +0200]:

- Axiom seems like a nice Object layer with a database backend. However, the part I don't understand is that suddenly the description claims that Axiom is doing blocking SQLite calls, and that this isn't really a problem. Since Axiom is a Divmod project, I tend to trust their opinion when it comes to Twisted. But why is it suddenly OK to have blocking code being used ? Does this only apply to SQLite, or would it apply to any database being used ?

One thing that's "special" about SQLite is that in runs in-process, so there are no network latency issues involved; like any other "blocking" code, the idea is to only do small bits of work at a time. If you need to run long queries, you would either break them up into smaller chunks, or run them in a separate process/thread/whatever to avoid blocking the main server.

- how do persistent object systems typically deal with transactions ? Suppose you want to do three operations on three different objects as a transaction, is that possible at all ?

I'm only really familiar with Axiom, so I can't comment on SQLObject and friends; in Axiom, the Store (which is the application-level object you use to interact with the database) has a transact() method which you invoke like store.transact(someFunc, arg1, arg2, ...). Within the transaction, any object manipulations/queries/etc. you perform obviously turn into SQL queries in the database layer, and these are run in an SQL transaction. -- mithrandi, i Ainil en-Balandor, a faer Ambar

Quoting Thomas Vander Stichele <thomas@apestaart.org>:

Hi everyone,

Hi,

- Twisted is, of course, async, so twisted.enterprise.adbapi wraps 2.0 DB API's using threads, which makes sense. Are people using adbapi much in production though ?

Here we use adbapi a lot, against Postgresql, MySQL and SQLite. Note that adapi is mainly a wrapper around a threadpool, there's very little magic in it (which explains it works great).

From scanning the mailing list it seems like people prefer layering stuff on top of adbapi to implement object models.

We did that too :). I just modeled a simple ORM to construct objects from SQL results (it just put the values into attributes of a dict or an object). We also use a simple wrapper around ConnectionPool to handle specific databases problems (concurrent updates in Postgres or timeouts in MySQL), and add other methods (for example executemany of pysqlite2 is very useful).

- SQLObject seems to come very close to what I would want to use to abstract away the database backend, and just pretend that I only deal with persistently stored python objects. Has anyone managed to use SQLObject in a twisted project ? If not, what's the closest I can get that gives me the advantage of having a python object backend ?

I strongly discourage you from using SQLObject. It's really a pain to use, and there's implicit behaviours that are nasty (cache, attributes retrieval...). It's not very hard to use with a thread pool in Twisted, but I think it's really designed for simple threaded (web) apps. SQLAlchemy seems much cleaner (but still in early stage) and as advised by Ed Suominen you should take a look at sAsync. -- Thomas ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program.

* Thomas Vander Stichele <thomas@apestaart.org> [2006-06-08 12:05:22 +0200]:

- Axiom seems like a nice Object layer with a database backend. However, the part I don't understand is that suddenly the description claims that Axiom is doing blocking SQLite calls, and that this isn't really a problem. Since Axiom is a Divmod project, I tend to trust their opinion when it comes to Twisted. But why is it suddenly OK to have blocking code being used ? Does this only apply to SQLite, or would it apply to any database being used ?

One thing that's "special" about SQLite is that in runs in-process, so there are no network latency issues involved; like any other "blocking" code, the idea is to only do small bits of work at a time. If you need to run long queries, you would either break them up into smaller chunks, or run them in a separate process/thread/whatever to avoid blocking the main server.

- how do persistent object systems typically deal with transactions ? Suppose you want to do three operations on three different objects as a transaction, is that possible at all ?

I'm only really familiar with Axiom, so I can't comment on SQLObject and friends; in Axiom, the Store (which is the application-level object you use to interact with the database) has a transact() method which you invoke like store.transact(someFunc, arg1, arg2, ...). Within the transaction, any object manipulations/queries/etc. you perform obviously turn into SQL queries in the database layer, and these are run in an SQL transaction. -- mithrandi, i Ainil en-Balandor, a faer Ambar

Quoting Thomas Vander Stichele <thomas@apestaart.org>:

Hi everyone,

Hi,

- Twisted is, of course, async, so twisted.enterprise.adbapi wraps 2.0 DB API's using threads, which makes sense. Are people using adbapi much in production though ?

Here we use adbapi a lot, against Postgresql, MySQL and SQLite. Note that adapi is mainly a wrapper around a threadpool, there's very little magic in it (which explains it works great).

From scanning the mailing list it seems like people prefer layering stuff on top of adbapi to implement object models.

We did that too :). I just modeled a simple ORM to construct objects from SQL results (it just put the values into attributes of a dict or an object). We also use a simple wrapper around ConnectionPool to handle specific databases problems (concurrent updates in Postgres or timeouts in MySQL), and add other methods (for example executemany of pysqlite2 is very useful).

- SQLObject seems to come very close to what I would want to use to abstract away the database backend, and just pretend that I only deal with persistently stored python objects. Has anyone managed to use SQLObject in a twisted project ? If not, what's the closest I can get that gives me the advantage of having a python object backend ?

I strongly discourage you from using SQLObject. It's really a pain to use, and there's implicit behaviours that are nasty (cache, attributes retrieval...). It's not very hard to use with a thread pool in Twisted, but I think it's really designed for simple threaded (web) apps. SQLAlchemy seems much cleaner (but still in early stage) and as advised by Ed Suominen you should take a look at sAsync. -- Thomas ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program.