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:
sqlite3 /path/to/local.sqlite
From now on, I’ll assume you only have one calendar.
SQLite Tables
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:
pragma table_info(table_name);
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:
pragma table_info(cal_events)
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:
begin transaction
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:
COMMIT;
To learn more about SQLite, visit https://sqlite.org/index.html
