Wt::Dbo: an ORM, C++ style

  • Posted by koen
  • Thursday, November 26, 2009 @ 14:11

When we developed Wt we followed the Qt model since we had experience while programming several desktop applications. Because those were mainly for engineering, we didn’t really need a database. Therefore, we never considered a database layer a must-have for Wt but mostly as a SEP.

Today however, many Wt applications talk to databases, and recommendations on the Wt mailing list for existing solutions were confined only to low level database API layers.

Compared to languages popular for web development, such as Java, Python or Ruby, no decent Object Relation Mapping (ORM) framework exists for C++. “Good” ORM solutions (at least measured by popularity) are Java’s Hibernate library (and now JPA) and Ruby’s ActiveRecord library. These tools do not just map single database tables onto classes, but also map relations between tables onto native collections and datastructures. In this way you can navigate an object and its related objects without explicitly defining SQL queries.

Strangely, while the flexibility of C++'s templating system rightfully deserves its share of praise (and criticism) to describe new Domain Specific Languages (for example Boost.Spirit), some developers are suggesting that the lack of reflection excludes a nice ORM layer in C++.

To be fair, it was not until we discovered the hiberlite library, that we realized that the C++ template system can make up for what it lacks in terms of reflection.

So we started implementing Wt::Dbo (database objects), which is now working out very well. Similar to hiberlite, Wt::Dbo uses a single template function to define the correspondence between the fields in a class and columns in a table, but provides a far more conventional relational mapping than hiberlite does, with support for 1-N and M-N relations.

The following is an actual code fragment that shows how the comment class in the code of this blog is mapped to a database table :

#include <Wt/WDateTime>

#include <Wt/Dbo/Types>
#include <Wt/Dbo/WtSqlTraits>

class Comment;
class Post;
class User;

namespace dbo = Wt::Dbo;

typedef dbo::collection<dbo::ptr<Comment> > Comments;

class Comment {
public:
  dbo::ptr<User>    author;
  dbo::ptr<Post>    post;
  dbo::ptr<Comment> parent;

  Wt::WDateTime     date;
  Wt::WString       textSrc;
  Wt::WString       textHtml;

  Comments          children;

  template<class Action>
  void persist(Action& a)
  {
    // normal fields
    dbo::field(a, date, "date");
    dbo::field(a, textSrc, "text_source");
    dbo::field(a, textHtml, "text_html");

    // N-1 relations
    dbo::belongsTo(a, post, "post");
    dbo::belongsTo(a, author, "author");
    dbo::belongsTo(a, parent, "parent");

    // 1-N relations
    dbo::hasMany(a, children, dbo::ManyToOne, "parent");
  }
};

For clarity our data are public member fields, but you can use accessor methods to your own taste. The persist() method can also be defined as a free standing function outside of your class definition.

The trick behind this mapping solution is that the library defines several actions: to create the schema, to prepare statements, to insert, update and load objects and to propagate transaction results. These actions are applied to an object, by visiting the object and hence its data members using the action parameter of the persist() function.

This results in an ORM solution with low runtime overhead (no virtual method calls or reflection), low developer overhead (no need to manage a separate mapping file) and maximum flexibility (everything is defined in C++ as opposed to a separate XML file).

A key class is ptr<C> : it is a smart pointer class that also implements book keeping for persisting the referenced object. There are two reasons why a smart pointer is a natural solution in an ORM framework. First ownership of records is not always clearly defined in a database (does a user own a comment, or does the post own a comment ?). Secondly, we want to make sure that we load only a single transient copy of a persisted object. Explicitly managing this in your application (keeping things independent) is burdensome and smart pointers are, well, smart.

db::ptr<C> is mostly compatible with any other smart pointers (such as std::shared_ptr<C>) except for one big difference : dereferencing the pointer returns a const reference to the pointed object. To obtain a non-const reference, you need to call the modify() method. This allows the library to keep track of object modifications (dirty checking).

Let’s look at the code that adds a new Comment to a Post:

  // A session manages persist objects
  dbo::Session session;

  // Currentely we only have an Sqlite3 backend
  dbo::backend::Sqlite3 connection("blog.db");
  session.setConnection(connection);

  // Map classes to tables
  session.mapClass<Post>("post");
  session.mapClass<Comment>("comment");
  session.mapClass<User>("user");
  session.mapClass<Tag>("tag");

  // Everything will happen within a transaction
  dbo::Transaction transaction(session);

  // Load by id
  dbo::ptr<Post> post = session.load<Post>(42);

  // Create a new comment and add it to the session
  dbo::ptr<Comment> comment = session.add(new Comment());

  // ptr-> returns a const ref. Use modify() to obtained a non-const ref.
  comment.modify()->post = post;
  comment.modify()->author = post->author;
  comment.modify()->textSrc = "Good stuff";

  // Flush and commit to the database
  transaction.commit();

Obviously, there is much more to be said about Wt::Dbo than just these basics. The library is pretty young but already very well useable. Now is an opportune time to reflect on its form and perhaps still change things.

Currently, Wt:dbo contains the following functionality:

  • Automatic schema creation

  • Mapping of 1-N and M-N relations

  • Lazy loading of objects and collections

  • Uses prepared statements throughout

  • Basic query support

  • Automatic dirty checking and database synchronization

  • Built-in optimistic locking (using a version field)

  • Transactional integrity, even when a transaction fails: dirty objects remain dirty and may later be saved in a new transaction, or may be reverted to their persisted state (unlike Hibernate where you are forced to discard the whole session)

  • Transaction write-behind for changes, with support for manual flushing

  • Forces use of surrogate keys

  • Does not depend on Wt (can be used independently)

  • Simple backend system: at the moment only SQLite3. We are looking at how we can perhaps can leverage SOCI if it makes its way into Boost.

We are at the moment busy with documenting the whole API, before merging the library into the public branch, which we expect to do over the next few weeks.

Tags:
22 comments
  • Posted by anonymous
  • 2 years ago
[[Comment deleted]]
  • Posted by anonymous
  • 4 years ago
Can you describe a way to use DBO along with WT toolkit against Oracle RDBMS?
  • Posted by anonymous
  • 7 years ago
Wt::dbo seems to be a great library.
You can also try a new C++ library : QxOrm.
This library is based on QtSql module to communicate with database and boost::serialization to serialize your data in xml and binary format.
The web site is in french but all samples and source code are in english : http://www.qxorm.com
  • Posted by anonymous
  • 7 years ago
[[Comment deleted]]
  • Posted by anonymous
  • 7 years ago
I would be interested specifically in using the Virtuoso database system (http://virtuoso.openlinksw.com), which has an ODBC interface amongst others. It would be a powerful combination with Wt. Does anyone else have an interest in this or know anything about it?
  • Posted by koen
  • 7 years ago
I had heard about virtuoso database system before, and it does indeed sound very useful. But so far I do not think anyone has requested this before. Given it has an ODBC interface, it should indeed be possible to have it as a backend for Wt::Dbo. Do you have an immediate need/use for it ?
  • Posted by anonymous
  • 7 years ago
BTW, the ODBC for Virtuoso is in their case cross platform, since open link makes cross platform ODBC drivers.
  • Posted by anonymous
  • 7 years ago
I'm using OTL (http://otl.sourceforge.net/) for my DB needs. It is a simple header file, the entire library is collection of templates. It uses the stream iterators to access the DB. I'm using it and I'm very happy with the results.
  • Posted by anonymous
  • 7 years ago
[[Comment deleted]]
  • Posted by anonymous
  • 7 years ago
Heyho,
I am trying to write another backend, using the official MySQL C++ Connector from mysql themselves. You need to change some things of your "interface" classes (SqlConnection.h, DbAction.C, Transaction.C, ...) because some statements (start transaction, commit transaction, rollback transaction, autoincrement) differ between RMDBs implementations. In MySql i.e. they are called "start transaction", "commit", "rollback" and "auto_increment" and your hard-coded sql breaks any other implementations other than Sqlite3 :-(
  • Posted by koen
  • 7 years ago
Hey ho,
You are entirely right: the interfaces will need a bit more generalization to allow integration of new back-ends. If you feel like it, we can discuss this better on the mailing list or you can email me (koen@emweb.be). transactionStart(), transactionCommit(), transactionRollback() are the easy ones; the type system will probably require a bit more thinking ?
  • Posted by anonymous
  • 7 years ago
Info for the rest of you (who don't receive the email for koen ^^): it is quiet finished, the dbo-tutorial works :-) so don't worry, there is mysql-support at the horizon :-)
  • Posted by anonymous
  • 7 years ago
Can't wait!
  • Posted by anonymous
  • 7 years ago
I hope that this library gets all better. I am looking at the Wt development for quite sometime now. And I have Wt 3.0 installed already and I am simply loving it. I know about Poco; never really looked at it. SOCI and Boost.RDb are worth looking at. I have full faith in what Boost does and same with Wt as well. Hiberlite seems to be gr8 as well. Too many choices spoil it for me. So I will stick with Wt::Dbo at the moment. Having said that, it is still in early stages, I really appreciate the speed and efficiency with which it comes up with new releases and add-ons. Didn't know that Boost was working on Rdb.
Keep up with the good work please....
  • Posted by anonymous
  • 8 years ago
That's a great extension to be awaited! You may want to look at Poco C++ library who has Data part which handles database in almost the same way as SOCI does with much improvement to SQLite, ODBC and MySQL. Moreover, this library is mature and has commercial support.
http://pocoproject.org/

Probably you could go ahead with Poco/Data without waiting for SOCI to be adopted to Boost. Just my 2 cents.

Viet.
  • Posted by koen
  • 7 years ago
Hey,

To wait for a library to be adopted by boost is to avoid the need for an extra dependency for Wt(::Dbo). Thus no matter how suitable poco may be (and I believe you), we really hope that boost adopts one or other library that deals with the backend-specifics.
  • Posted by anonymous
  • 7 years ago
I hope that too. But beware that Boost does not care about binary compatibility of versions. A good library must preserve binary compatibility across minor version. Qt, Poco, APR, GTK+ are good examples.
  • Posted by anonymous
  • 7 years ago
I don't think SOCI will be adopted to boost. there is Boost.Rdb under development, which already looks superior to SOCI by far.
  • Posted by anonymous
  • 8 years ago
I am sure looking forward to this!
  • Posted by anonymous
  • 7 years ago
[[Comment deleted]]
  • Posted by anonymous
  • 7 years ago
[[Comment deleted]]
  • Posted by anonymous
  • 6 years ago
[[Comment deleted]]

Contact us for more information
or a personalised quotation