ODB 2.2.0 was released today. As you might have noticed, for the past several months I’ve been neglecting this blog. This release will hopefully explain my lack of attention.

In case you are not familiar with ODB, it is an object-relational mapping (ORM) system for C++. It allows you to persist C++ objects to a relational database without having to deal with tables, columns, or SQL, and manually writing any of the mapping code. ODB natively supports SQLite, PostgreSQL, MySQL, Oracle, and Microsoft SQL Server. Pre-built packages are available for GNU/Linux, Windows, Mac OS X, and Solaris. Supported C++ compilers include GCC, MS Visual C++, Sun CC, and Clang.

This release packs quite a few major new features, including multi-database support, prepared queries, change-tracking containers, and automatic mapping for char[N]. As always, below I am going to examine these and other notable new features in more detail. For the complete list of changes, see the official ODB 2.2.0 announcement.

But before we go into detail on the new ODB features, let me mention a few other major improvements and additions. First, of all, there is support for Qt5 (in addition to Qt4). This includes VC++ project/solution files, autotools build system updates, code fixes, etc. We have also thoroughly tested Qt5 support on GNU/Linux, Windows (VC++ 10.0), and MinGW-W64.

Generally, a lot of testing goes into an ODB release. Just think of all the combinations: five databases, more than ten compilers (including versions), two C++ modes (98 and 11), and multiple architecture (x86, x86_64, arm, SPARC). Just building and running all the tests on Windows takes several days. The official announcement mentioned above has a cool table that lists all the combinations tested.

We have also upgraded the private copy of GCC that is used by the ODB compiler binary packages from 4.5.1 to 4.7.2 (actually, it is the 4.7.3 pre-release) on all the platforms. This should make a difference to projects wishing to use some of the C++11 features that weren’t yet implemented in GCC 4.5.

Another notable addition is the “official” support for mobile/embedded systems. We have always kept this class of applications in mind by making sure the footprint stays small and by providing fine-grained control over the optional features used. With this release we have added Raspberry Pi ARM GNU/Linux computer as one of the test targets. We’ve also written a detailed, step-by-step guide on using ODB on Mobile and Embedded Systems. At the end of this guide, there are some indicative footprint numbers. Check it out.

More good news, thanks to the debugging efforts of Hugo Mildenberger, ODB now works with MariaDB. While the MariaDB project claims that it is “a drop-in replacement for MySQL”, they have actually re-implemented the MySQL client library. As you can imagine, it is hard not to end up with some subtle differences in behavior.

Finally, I am planning to give a talk on ODB (and another one on my experience adding C++11 support to ODB) at the 2013 C++Now conference. Hope to see you all there.

Multi-Database Support

There is a pretty long list of features that we would like ODB to support. There is also a much shorter list of Big Features, things like database schema evolution or the SQL-to-C++ compiler. Well, with the release of 2.2.0, I am happy to say this latter list got one item shorter.

Multi-database support allows you to use multiple database systems (e.g., MySQL, SQLite, etc.) from the same application. It comes in two flavors: static and dynamic.

With static support you use the concrete database interfaces, that is odb::mysql::database, odb::sqlite::database, etc. Here is a quick example:

#include "person.hxx"
#include "person-odb-mysql.hxx"
#include "person-odb-sqlite.hxx"
 
odb::mysql::database my_db ("john", "secret", "test_db");
odb::sqlite::database sl_db ("/tmp/test_db");
 
person p ("John", "Doe");
 
{
  odb::mysql::transaction t (my_db.begin ());
  my_db.persist (p); // Store in MySQL.
  t.commit ();
 
{
  odb::sqlite::transaction t (sl_db.begin ());
  sl_db.persist (p); // Store in SQLite.
  t.commit ();
}

With dynamic support the same application code can access multiple databases via a common interface. Here is how we can change the above example to take advantage of the dynamic support:

#include "person.hxx"
#include "person-odb.hxx"
 
void
store (odb::database& db, const person& p)
{
  odb::transaction t (db.begin ());
  db.persist (p);
  t.commit ();
}
 
odb::mysql::database my_db ("john", "secret", "test_db");
odb::sqlite::database sl_db ("/tmp/test_db");
 
person p ("John", "Doe");
 
store (my_db, p); // Store in MySQL.
store (sl_db, p); // Store in SQLite.

As you may have noticed, the idea behind static and dynamic multi-database support is conceptually the same as that of ordinary and virtual functions. If the functions are not virtual, then we have to use the derived type to make sure the correct one is called. In contrast, with virtual functions, we can use the common base.

The advantages and disadvantages of static vs dynamic support are also similar to those of ordinary and virtual functions. In particular, the dynamic support adds a slight overhead and you cannot use database-specific features, extensions, etc. The main advantage of dynamic multi-database support is the database-independence of the code that we write. The same application code will work with any database system supported by ODB. Dynamic support also allows the application to dynamically load the database code for individual databases if and when necessary.

Note also that it is possible to mix and match static and dynamic support in the same application. In fact, dynamic support is built on top of static support so it is possible to use the same database both “statically” and “dynamically”. In particular, this ability to “drop down” from dynamic to static support can be used to access database specific features and extensions.

Well, this is just a quick, high-level overview of this feature. The ODB manual has a whole chapter dedicated to multi-database support.

Prepared Queries

When you call persist() or load(), ODB uses (and caches) prepared statements to perform the database operation. For queries, that is, the query() call, however, ODB has no knowledge about how many times the same query will be performed and so does not cache the statement. This can be a significant overhead if you are executing the same query many times. For example:

typedef odb::query<person> query;
typedef odb::result<person> result;
 
unsigned short age;
query q (query::age > query::_ref (age));
 
for (age = 1; age < 100; age++)
{
  // Prepare, execute, release.
  result r (db.query<person> (q));
  ...
}

To overcome this we have added support for prepared queries. They are a thin wrapper around the underlying database system’s prepared statements functionality and provide a way to perform potentially expensive query preparation tasks only once and then execute the query multiple times. Here is how we can improve the above example:

typedef odb::query<person> query;
typedef odb::prepared_query<person> prep_query;
typedef odb::result<person> result;
 
unsigned short age;
query q (query::age > query::_ref (age));
 
transaction t (db.begin ());
 
// Prepare.
prep_query pq (db.prepare_query<person> ("person-age-query", q));
 
for (age = 1; age < 100; age++)
{
  // Execute.
  result r (pq.execute ());
  ...
}
t.commit (); // Release (or when pq goes out of scope).

What if we wanted to use a prepared query at different times and/or different places in our application? To support this, ODB allows you to cache the prepared query on the connection. Alternatively, you can provide a factory (can be a C++11 lambda) that will prepare a query when/if necessary. This is especially useful for multi-threaded applications since prepared queries are database connection-specific.

For more information on this feature, see Section 4.5, “Prepared Queries” in the ODB manual. There is also the prepared example in the odb-examples package that has a nice run down of various scenarios.

Change-Tracking Containers

Let’s say we are working on an object model for a bug tracker. A bug report might be represented with the following persistent class:

#pragma db object
class bug
{
  ...
 
  #pragma db id auto
  unsigned int id_;
 
  std::string summary_;
  std::string description_;
 
  std::vector<std::string> comments_;
};

Let’s say someone added a comment and we need to update the bug object in the database. Let’s also assume the report already includes quite a few comments. How many database statements will ODB execute to synchronize the state?

ODB has no knowledge of which elements in the comments vector were added, removed, or modified (ODB does not store original versions of objects to perform change tracking though this can be supported with a custom session; see below). So ODB has no choice but to assume the worst case scenario (everything has changed) and completely re-create the state. This can be a significant overhead in our case, since all we did was add one element and a single INSERT statement would have sufficed.

To overcome this, ODB now supports change-tracking containers. They do exactly what the name says: they track changes you make and then use that information to minimize the number of database operations necessary to synchronize the container state with the database. In this release ODB provides change-tracking equivalents for std::vector and QList, called odb::vector and QOdbList, respectively. Equivalents for other containers are planned for future versions. It is also possible to add change tracking support for a custom container. In the scenario described above, if we replace std::vector with odb::vector, the update operation will result in exactly one INSERT.

A change-tracking equivalent can be used instead of the original with minimal changes. Underneath, the change-tracking version uses the original to store the elements. It is implicit-convertible to and implicit-constructible from the original. This means, for example, that you can pass odb::vector anywhere const odb::vector& is expected. The change state overhead is also minimized. For ordered containers it is just 2 bits per element.

In fact, 95% of the interface is exactly the same as the original. Probably the major difference is the removal of the non-const element access operators: operator[] in the container and operator* and operator-> in the non-const iterator (const versions are still available). Instead, they were replaced with the explicit modify() functions.

The ODB manual has a lot more on this feature (Section 5.4, “Change-Tracking Containers”). Sections 5.4.1, “Change-Tracking vector“ and 22.3.1, “Change-Tracking QList“ have more information on the interface differences.

Automatic char[N] Mapping

Another neat feature is automatic mapping for char[N] (and wchar_t[N] for SQLite on Windows and SQL Server). By default, the database type used is VARCHAR(N-1) (or equivalent; -1 is for the '\0' terminator), though you can change that to, say, CHAR(N) or VARCHAR(N) if you don’t need the terminator; ODB supports both ways. Here is an example:

#pragma db object
class address
{
  ...
 
  char street[64]; // VARCHAR(63)
 
  #pragma db type("CHAR(2)")
  char state[2];
};

This should be especially useful for mobile/embedded systems where you may want to avoid any dynamic allocations (for example, cause by std::string) in your objects.

You can also use C++11 std::array<char, N> instead of char[N], though you will need to provide the mapping explicitly. For example:

#pragma db object
class address
{
  ...
 
  #pragma db type("VARCHAR(63)")
  std::array<char, 64> street;
 
  #pragma db type("CHAR(2)")
  std::array<char, 2> state;
};

For more information on this new mapping, refer to the ODB manual “Type Mapping” sections for each database system.

Some other cool features in this release that I would like to mention briefly include support for automatically-derived SQL name transformations. You can now add prefixes/suffixes to table, column, index, and sequence names, convert them to upper/lower case, or do custom regex-based transformations.

Then there is support for custom sessions that I mentioned above. The common/session/custom test in the odb-tests package demonstrates how to implement a custom session that performs change tracking (by keeping the originals) and automatic flushing of the modified objects. Custom sessions are a very powerful extension mechanism if you are into those kind of things.