db4o: Tools For Adhoc Querying And Modification

Remember my very first post about db4o? There I’ve made the statement that the tool-support is very bad. Well I don’t revise my opinion here. The situation is still bad compared to the relational database world. Mostly that’s because relational databases have a giant user base, are well known and have some common interfaces (JDBC, ODBC, etc).

(All posts of this series: the basics, activation, object-identity, transactions, persistent classes, single container concurrency, Queries in Java, C# 2.0, client-server concurrency, transparent persistence, adhoc query tools)

Now enough complaining. Only complaining doesn’t help anyway!  In this post I’ll introduce two tools and show briefly, what you can do with them. Let’s start:

ObjectManager Enterprise (OME)

This is a tool which comes bundled with db4o. In the Java-Version it’s a Eclipse-plugin. In the .NET-version it’s a Visual Studio add-on.

For the .NET-version there are installers in the ‘ome2005’ and ‘ome2008’-directory of the db4o-distribution. Pick the one which corresponds to your Visual Studio version and install it. After restarting Visual Studio, you’ve a new buttons in you’re toolbar and a new menu-point ‘Tools’->’ObjectManager Enterprise’.

For the Java-Version there’s a zip-file in the ‘ome’-directory of the db4o-distribution. Unzip it somewhere. Open Eclipse, go to ‘Help’->’Install New Software’. Click on ‘Add’, then ‘Local’ and choose the directory you’ve unzipped the zip-file before. The click just through all the confirm-dialogs etc. Then restart Eclipse. Now there’s a new menu-point ‘OME’ in Eclipse.

Now you can connect to a db4o-database. Either you can connect to a running db4o-server or open a db4o-database-file directly.

Queries with OME

Now in the OME you don’t use a query-language for querying the database. This is quite a surprise. So how do you build queries then? You build then by drag and drop! On the left the OME shows a list of all known classes. When you expand such a class, you see the fields. Fields which are no primitives can be extended further. To build a query you choose a object-type, lets say ‘Person’. Then you expand the ‘Person’-class on the left, choose the fields you want to query and drag them into the ‘Query-Builder’-window. After sticking the criterions together, you can run the query. Furthermore you also can update field of persisted data.

Here a little screen-cast, which shows this:

Loading the screen-cast …

To get an overview of the OME-Features, you also might take a look at this screen-cast.

LINQPad

Now I’m not a fan of this visual query-building. I prefer a query-language. But db4o doesn’t provide a language like SQL, but rather utilized the language of the platform. Now .NET brings a wonderful query-language, LINQ. And together with the excellent LINQPad you can use it like a traditional SQL-console. However some preparation is required:

  1. We need to add the db4o-assemblies. LINQPad has no idea about db4o, therefore we need to include the db4o database engine. To achieve this, go to the ‘Query’->’Advanced Query Properties’. On the dialog, click on ‘Add…’, then on ‘Browse…’. Browse to the db4o-assemblies. Add the ‘Db4objects.Db4o.dll’, ‘Db4objects.Db4o.Linq.dll’, ‘Cecil.FlowAnalysis.dll’ and the ‘Mono.Cecil.dll’-assemblies. Keep the ‘Advanced Query Properties’-dialog open for the next step.
  2. In the ‘Advanced Query Properties’-dialog open again the ‘Ad…’-dialog. Add you’re assembly which contains the stored classes. We need the classes in order to write LINQ-Queries.
  3. In the ‘Advanced Query Properties’-dialog go the tab ‘Additional Namespace Imports’. Add there the db4o-namespaces:
    Db4objects.Db4o.Linq
    Db4objects.Db4o
    Additional add the namespace of your stored classes, for example:
    MyProject.DomainModel
  4. Click on ‘Set as default for new queries’ so that you don’t need to repeat the three steps above next time. Then we’re done.

Queries with LINQPad

Now LINQPad is ready for db4o. Choose “C# statements” or “VB statement” in the language-chooser. Then you can write you’re query like this:

using(var db = Db4oEmbedded.OpenFile(@"C:\file-path\database-file.db4o")){
    var result = // query!!!
    result.Dump();
}

For example:

using(var db = Db4oEmbedded.OpenFile(@"C:\Users\Gamlor\Desktop\test.db4o")){
    var result = from Person p in db
                    where p.FirstName.Contains("Roman")
                    select new {p.FirstName, p.LivesAt.Street, p.LivesAt.City.Name};
    result.Dump();
}

As you see, you just use normal C# code. So you can run any LINQ-Query on you’re database.

Update with LINQPad

Well updating is also possible. LINQPad is nothing else than a C#-Console. So you just get the objects, change then and store the changes. For example:

using(var db = Db4oEmbedded.OpenFile(@"C:\Users\Gamlor\Desktop\test.db4o")){
    var update = (from Person p in db
                    where p.FirstName.Contains("Roman")
                    select p).First();
    update.FirstName = "Romanovski";
    db.Store(update);
    update.Dump();
}

Some LINQPad-Tips

Since LINQPad doesn’t know anything about db4o, but is only a interactive C#-console, you are responsible for db4o specific behavior. For example the activation-depth, update-depth etc also apply within LINQPad. So for example when all properties of a nested object are null, it is might not activated yet.

You might also write a little helper class library which you can use within LINQPad together with db4o.

Since LINQPad is a popular tool, you certainly find more resources on the internet.

Conclusion

I’ve introduced two tools for ad-hoc querying and modification of the db4o-database. The Object Manager with it’s graphical query builder and LINQPad. Both are useful for inspecting the database.

However I would like to see more powerful tools. When you know one, please tell me.

Tagged on: ,

5 thoughts on “db4o: Tools For Adhoc Querying And Modification

  1. Tairone Nunes Magalhaes

    I have already installed OME, but I don’t know how to connect to my db4o database running on my Android phone. Is it possible to do it?

  2. gamlerhart Post author

    I don’t know. Maybe you can have a debug mode in your app, which runs db4o as a server and then connect to the device directly. Hmm.

  3. jimbobmcgee

    Is there a way to query the contents of an Java ActivatableArrayList (or at least determine the IDs of the elements), using a GenericObject in LINQPad?

    I’ve got so far with my inspection of a Java-based database, but have hit a brick wall here.

  4. gamlerhart Post author

    Hmm, this will be incredible hard / close to impossible to properly query a db4o Java db file with the .NET version.

    My LinqPAD driver generates types, so that you can use LINQ. However, it assumes .NET conventions. So it almost certainly fails. If you use raw db4o.NET, you have still the issue that it has no idea how to deserialize some specific Java types (collections, built in types)

    I played with the idea of supporting JAVA db’s in my driver: By embedding the JAVA version of db4o. (via IKVM.NET). However, that would be quite some effort. Especially since db4o seems dead.

  5. jimbobmcgee

    This is pretty much what I figured.

    It is especially frustrating given I had gotten so far with my LINQPad wrapper, as well!! — handled the type aliasing from com.db4o.ext.Db4oDatabase to Db4objects.Db4o.Ext.Db4oDatabase; derived enum values from their eventual location in java.lang.Enuml; and so on.

    But, every time I tried to activate the ActivatableArrayList, I got an ArgumentNullException from somewhere deep in the db4o.NET code. I couldn’t cast from GenericObject to ActivatableList, nor to a GenericArray of the GenericObjectClass I knew would be stored in it.

    In the end, I had to resort to manually copying the data from the screens of the app that was using db4o, into the document I was preparing. Painfully manual work.

    Still, might get me another Tumbleweed badge on StackOverflow — how I love getting those!!

Leave a Reply

Your email address will not be published. Required fields are marked *