Richard Dallaway's Blog Avatar
Richard Dallaway's Blog

Diarium schema

Diarium is a nicely thought out journal app for lots of platforms. It’s commercial, with great support. The key feature for me is that the data storage is SQLite, making the data portable and very hackable (but also: danger! don’t screw up your database).

Here’s the schema as I can figure it out as of June 2024:

Uploaded image
Schema diagram of the Diarium journaling application.

Entries

The DiaryEntryId represents the data and time of the entry in .Net Ticks. The Text is HTML, typically a sequence of <p> tags.

Rating is a 1-5 star rating, which I don’t use, so all the values are zero for me.

Tags

Entries can have multiple tags, and a tag is a label. I’ve not seen the Icon value used, but I’m in the macOS/iOS world and perhaps it’s not used there.

The Type of the tag is: 

  • 0 for a tag 
  • 1 for a person
  • 2 for a tracker

These differences (between tag, person, tracker) correspond to different categories in the user interface.

Media

Entries can have media attachments. Type is:

  • 0 — an image
  • 1 — an audio recording
  • 2  — don’t know as I do not have any media with this type.
  • 3 — a document
  • 4 — an audio/video attachment

Index is the ordering as you can manually re-order media on an entry.

The Name and FileEnding columns give you filename elements, such as IMG_3631 and .jpg.

Events

These are additional attributes of an entry, for things like the weather.  In fact, I think it’s just the weather at the moment.

I do have weather logging turned on, which is Type 0, and it automatically logs Content such as “25 / 16 °C" with a Tag of “mostly-clear-day”. I presume that tag maps to an icon.

Other integrations

Possibly of interest, Diarium integrates with calendars to show events in the journal. As far as I can tell, this is an integration with the calendar system directly, rather than copying data into Diarium as events. That’s a great choice, as calendar syncing is a nasty problem.

Diarium has integration with a bunch of products like Strava, Instagram, Github, and many others. I don’t use these integrations. As of 2021 these are treated in the same way as calendars, i.e., not recorded as events.

software writing