Thunderbird Printer

Posted on September 8, 2017 in hacks

The problem: I wanted to be able to print a monthly view of my calendar, which I manage on my iPhone and on my laptop via Thunderbird. Thunderbird has some very crude printing functionality, and I could not find any decent extension that would improve the situation.

The idea: find out where Thunderbird stores its calendar, read the data, generate a PDF.

Ignoring typical StackOverflow answers ("I can't believe you're still using Thunderbird", "PDF is evil", "You don't want to do this"...) let's see if there is a way to do this in quick, pure Google + StackOverflow binge-coding style.

Thunderbird Calendar Data

Finding out the calendar is relatively easy. Thunderbird stores everything in profiles, and profiles are stored in %USERPROFILE%\AppData\Roaming\Thunderbird\Profiles. On a default installation, there will be only one directory here, e.g. a7xfqdpa.default. From this directory, it's easy to navigate to calendar-default\local.sqlite. So: it looks like the calendar data is stored in a SQLite file.

Let's verify this. We need to check what's in that file. Google points us to SqLite Browser which is OSS and maintained. Cool. It opens Thunderbird's calendar file, and finds table such as cal_events, cal_alarms... good. We found our data.

Since I spend most of my days coding in C#, I decide to implement the tool in C#. How do you read a SQLite database in C#? NuGet to the rescue, there seems to be a System.Data.SQLite package, which seems to be supported and OSS (interestingly, using the Fossil SCM). From there, it's fairly common grounds:

using (var conn = new SQLiteConnection(@"DataSource=path\to\local.sqlite;Version=3;"))
{
  conn.Open();

  using (var cmd = conn.CreateCommant())
  {
    cmd.CommandText = @"SELECT cal_id, cal_title FROM cal_events";

    using (var reader = cmd.ExecuteReader())
    {
      ...
    }
  }

  conn.Close();    
}

Finding the proper tables and columns is not too complex. Some columns require a bit of work though.

Some date/time columns use the Unix Epoch format, i.e. they contain the number of (micro?) seconds elapsed since the Unix Epoch, which is 1970/01/01. This requires a bit of conversion:

var epoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
var dateTime = epoch.AddSeconds(reader.GetInt64(i) / 1000000);

Time Zones

Good. Now there's the problem of time zones. Every interesting program has a problem with time zones. Whenever the database contains a date/time column in the Unix Epoch format, e.g. start, it also contains a time zone column, e.g. start_tz, which can contain three sorts of strings:

• The value "floating" - not sure what this means. We ignore it for now.
• A value beginning with "BEGIN:VTIMEZONE" - this is iCal format. We ignore it for now.
• A value similar to "Europe/Paris" - we want to parse these.

But, how do you turn "Europe/Paris" into some sort of time zone info? NuGet again! The excellent Jon Skeet frequently mentions the Noda Time libray which is on NuGet and is OSS. It enables us to do something similar to:

var tzp = DateTimeZoneProviders.Tzdb;
var instantNow = Instant.FromDateTimeUtc(dateTime);
var tz = tzp.GetZoneOrNull(timeZoneName);
if (tz != null)
{
  dateTime += tz.GetUtcOffset(instantNow).ToTimeSpan();
}

iCalendar

And there there is recurrence. Recurrence is maintained in the database as an iCalendar string, e.g.

RRULE:FREQ=YEARLY;INTERVAL=1;BYMONTH=2;BYMONTHDAY=12

We certainly don't want to write a parser for these strings. NuGet? Yes! The iCal.Net can turn such a string into a RecurrenceRule object with, essentially, one line of code:

var rule = new RecurrencePattern(icalString);

It turns out that this package does much more, including putting all the events we read from the database into a Calendar instance:

var calendar = new Calendar();
foreach (var e in GetEvents())
{
  calendar.Add(e);
}

And then retrieve every Occurrence of events within a given period (e.g., each day):

var occurrences = calendar.GetOccurences(day, day.AddDays(1).AddSeconds(-1));

As a quick test, occuring over occurences for each day of a given month, I can print out the various events. We are almost there! Now, we need to format it all into a nice PDF file. How does one generate PDFs from C#? NuGet maybe...?

PDF

Indeed! The iText project provides the iText7 package which is OSS, though with a mixed license (a license is required for commercial usage). And then creating a simple document is fairly easy:

var pdfDoc = new PdfDocument(new PdfWriter("calendar.pdf"));
var doc = new Document(pdfDoc, PageSize.A4.Rotate());
doc.SetMargins(20, 20, 20, 20);
doc.Add(new Paragraph("Hello, world!").SetBold());
doc.Close();

Using iText is fairly obvious once you have figured it out, but it can be a bit challenging to begin with. Also, be careful of the old iText5 version, which is different (so, not all examples Google will give you will work with iText7).

Once done, let's trigger the opening of the file:

System.Diagnostics.Process.Start("calendar.pdf");

It works!

Conclusion

Long time ago we dreamed about creating software by merely assembling "components", and then there were countless articles about how the whole IT industry had failed to accomplish this. Well... maybe it's time to revise this conclusion?

The whole code is available on GitHub.