[odb-users] Handling Multiple Databases/Threads in ODB

[Boris Kolpackov]

Hi Eric,

Sum, Eric B <eric.b.sum at lmco.com> writes:

> 1. I was wondering if there is documentation or examples in the odb manual
> for dealing with multiple databases.  I may have missed this section in the
> manual.  The only example/mention that I found was in the transaction
> section, in which it describes how to switch between current active
> transactions within the same thread.  I assume that you just create two
> database instances and work with each instance individually:
> 
> auto_ptr<database> db1 (new odb::sqlite::database("DB1.sql", SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE));
> auto_ptr<database> db2 (new odb::sqlite::database("DB2.sql", SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE));
> 
> //start a transaction and do stuff with d1
> //start a transaction and do stuff with db2
> 
> I'm just asking to make sure that I'm not missing out on some extra 
> support that could be useful.

Yes, you can have multiple databases open simultaneously as you have
shown above. The only limitation of this approach is that you cannot
perform operations on these two databases as part of the same
transaction.

The other alternative, which is possible with SQLite, is to attach
multiple databases to the same sqlite::database instance. This will
allow you to perform operations on multiple databases as part of the
same transaction. Plus queries can span multiple databases. The
limitation here is that you have to decide in advance in which
database each persistent class will be stored. For example:

#pragma db object schema("main")
class employee
{
  ...
};

#pragma db object schema("extra")
class employer
{
  ...
};

In the above example the employee class will be stored in the 'main'
database (this is the name given by SQLite to the first opened database).
The employer class will be stored in the 'extra' database. Here is how
we can attach the 'extra' database:

odb::sqlite::database db ("main.db"); // main -> main.db
db.execute ("ATTACH DATABASE extra.db AS extra");

employee ee;
employer er;

transaction t (db.begin ());
db.persist (ee);  // Goes to main.db.
db.persist (er);  // Goes to extra.db.
t.commit ();

For more information on attaching multiple databases in SQLite, see
this page:

http://www.sqlite.org/lang_attach.html


> 2. In a previous email, it was mentioned to me that sqlite now supports
> multiple thread access to an in-memory database.  Is there some
> documentation in the odb manual describing how odb supports multiple-thread
> access to sqlite databases and/or some sort of locking scheme for database
> access to prevent data corruption when using multiple threads?

In ODB multi-threaded access to an SQLite database is implemented using
the SQLite shared cache/unlock notification mechanism. Generally, an
application developer doesn't need to be concerned with how this is
done. But if you are interested, here is page that describes this in
more detail:

http://www.sqlite.org/unlock_notify.html

Boris