I do love a good diagram, and I noticed that I have a number of Bongo-related diagrams stuck on my hard-drive. Most of them have seen action in a past blog post of mine, but my blog isn’t really a Bongo development resource, and the ones which haven’t yet been seen really ought to be a bit more widely available.

In general, though, programmers aren’t great at drawing things and worse, updating diagrams in drawing programs is often tedious and time-consuming. So, I’ve committed some graphviz support into our build system which runs when you build the development documentation.

The first – and only – fruit of this endeavour is a UML-like diagram I’ve done to try to figure out what the Store’s SQLite database structure should be. Behold (clicky for bigger):

I should at this point take a little bit of time to explain why I did this. I’ve decided that the SQLite usage in the store needs to be pretty much refactored, for a number of reasons. Amongst those is the fact that we store a binary blob within the tables which represents much of this data: some, like “mime_lines” in mail documents is completely hidden with this structure, other, “time_sent” appears in both this blob and as a column in a table.

So, job number one has basically been getting rid of this evil binary blob. This schema, then, isn’t me really redesigning it per se – I’m simply pulling the structure out of this blob and putting it in tables, where it belongs.

I’ve also slightly denormalised the table a little. In our current database, event and mail attributes exist for all documents: and if the document isn’t an event or mail, those fields are just empty. That’s a bit of a waste, hence the one-to-one relations between “StoreObject” (which represents any object in a store – a mail item, a collection, etc.) and “MailDocument” et al.

The one big change I have made is the addition on the many-to-many “links” table. The purpose of this is simply to group together store objects. In our current store, we have a concept of “calendars” and “events”, and events are linked into calendars rather than calendars being collections which hold events. Rather than having something specific to calendars, I’ve generalised this out. I’m not totally sure I like this as a way of representing calendars – I presume that it was done this way because collections can’t be “typed” like a calendar document can – but it would also allow us to do stuff like linking conversations with events, so you could associate a meeting with the invitation conversation or something.