The thought to create a copy of my Thunderbird profile did not cross my mind until the last power failure. After each power failure I found that I have to setup my mail account, and that all my events were “lost”.
Wellm the events were not exactly lost, but a new calendar id was created, which is to be used to find all the events and their relevant properties in the calendar extension’s database. The database itself is an SQLite file, and in my system is located under ‘~/.thunderbird/default-dir/calendar-data/local.sqlite’
(Change the default-dir name to your local name, it is by default the one that ends with ‘.default*.
Finding the Current Calendar Id
The calendar id is the value of the Thunderbird’s user preference ‘calendar.list.sortOrder’. You can view this variables by choosing from the menu:
preferences->preferences->preferences*, and then from the window opend, choosing the Advanced tab, and then clicking the “Config Editor* button.
Continue at your own risk…
Now, if you haven’t imported your calendar using Events And Tasks->Export.
You can connect to the database using:
From now on, I’ll assume you only have one calendar.
The tables, indexes, trigges and other database entities are stored in a table named ‘sqlite_master’. To find tables, run the query
select name from sqlite_master where type='table';
For each table column, run the query:
The result will be:
cal_calendar_schema_version cal_attendees cal_recurrence cal_properties cal_events cal_todos cal_tz_version cal_metadata cal_alarms cal_relations cal_attachments
Replace table_name by a table name, for example:
The result will be:
0|cal_id|TEXT|0||0 1|id|TEXT|0||0 2|time_created|INTEGER|0||0 3|last_modified|INTEGER|0||0 4|title|TEXT|0||0 5|priority|INTEGER|0||0 6|privacy|TEXT|0||0 7|ical_status|TEXT|0||0 8|flags|INTEGER|0||0 9|event_start|INTEGER|0||0 10|event_end|INTEGER|0||0 11|event_stamp|INTEGER|0||0 12|event_start_tz|TEXT|0||0 13|event_end_tz|TEXT|0||0 14|recurrence_id|INTEGER|0||0 15|recurrence_id_tz|TEXT|0||0 16|alarm_last_ack|INTEGER|0||0 17|offline_journal|INTEGER|0||0
Finding the Last Calendar Id
Now, I guess that an event in the last calendar has the latest event start date.
A good query to find that event can be:
select cal_id, title, event_start from cal_events order by event_start;
Let us call the most recent value calendar id “old-cal-id”, and the new calendar id “new-cal-id*
Updating and Deleting
I suggest that you perform update and delete queries within transaction, so if something goes wrong you can rollback.
Begin a transaction by running the command:
Now, for each table that has the column cal_id, run the query:
update table_name set cal_id="new-cal-id" where cal_id="old-cal-id"
Replace table_name by a name of a table that has the column cal_id, for example:
update cal_event set cal_id="new-cal-id" where cal_id="old-cal-id";
Now, to delete the rest, run:
delete from table_name where cal_id != "old-cal-id";
If everything’s fine, it’s time to commit your transaction by running:
To learn more about SQLite, visit https://sqlite.org/index.html