What are you trying to achieve in the first place?

If no one has already done this, I'd like to create a script to read the .sdb file and create an .ics file. See my related question: https://support.mozilla.org/en-US/questions/1104023

Why do I want to do this? Because I want to publish my lightning calendar to a WebDAV server and have Outlook subscribers see my updates. Apparently Lightning does no update published calendars after the initial publication. See another related question: https://support.mozilla.org/en-US/questions/1103384

If I can generate my own .ics files then I can accomplish this task myself.

have you looked at the SQLite files in the calendar-data folder in your profile? I suggest you start there.

Per http://kb.mozillazine.org/Files_and_folders_in_the_profile_-_Thunderbird

storage.sdb Internal calendar data (in SQLite format) if you use the Lightning extension. Its been replaced by local.sqlite but is still maintained for compatibility in case you switch between different Thunderbird+Lightning versions.

Wandering around earlier today I had someone with issues related to this add-on. https://addons.mozilla.org/en-US/thunderbird/addon/automatic-export/?src=ss but it looks like it might do what you want. Or the source might be a good starting point for a new add-on

Thanks for that info. My web searches did not turn up the .sqlite information, but your link pointed me to local.sqlite which has a lot more tables. I will peruse these as I have time.

Your referenced Auto Export add-on would have been exactly what I need, but it does not work with Thunderbird version 38.4.x -- I tried it. Too bad. The download page does say up to version 31.x. I've written to the developer to beg him to update it.

I assume when you say, "the source might be a good starting point for a new add-on", you are referring to Lightning source, not the Auto Export add-on source, right?

Nope, the auto export add-on source.

If you have not worked it out yet... XPI files as nothing but ZIp files with an new extension. The zip has unobfuscated JavaScript source so would make an idea source of sample code.

My guess based on what I heard in the other support thread today is in V38 there is a run away loop somewhere as the person was experiencing 100%CPU with it enabled. But that to might have only been his experience.

It is a simple matter to edit the install.rdf file of an add-on (IN the ZIP) to change the max version number and make it install. There are people on this forum still running add-ons that have not been updated since V3. They just bump up the max version in the file and away they go again. If it works for you then go.. if not you wasted about 3 minutes extracting, and zipping the file back into the archive.

Well, I looked as the sources for Lightning (didn't see your "nope the auto export add-on source" comment in time). And I decided to try interpreting the database file myself and creating a script. I have done so and will post here in case others find it useful. The script must be executed by an account that has access both to the published calendar (e.g. /srv/httpd/htdocs/calendars/mark.ics) and the local.sqlite database file. It assumes only one calendar resides in the database, since that's all I have, but probably easy enough to identify distinct calendars. For the sake of some simplicity, this script is not actually looping through all published calendars. It is simply generating an .ics file which a real cron script could use to compare for changes with the published calendars and, if changed, copy the updated version over. I have done this manually and new events do now show up in Outlook -- which was my original goal. Now I can move workstations from Windows to Ubuntu one-by-one!

Here you go, this looks a little messy, but I don't see an option on this forum to post "code". Let me know if there is a better mechanism and I'll repost:

#!/bin/bash
#
# script: updateiCal
# author: Mark Foley - mfoley(at)novatec-inc.com - Novatec Software Engineering, Inc.
#   date: 2016-01-12
#    $Id: $

#This bash script scans the Thunderbird 38.4.0, Lightning 4.0.4.1
#calendar database for events and creates an .ics file.

# $log: $

DATABASE="/redirectedFolders/Users/mark/.thunderbird/28ngbwvw.default/calendar-data/local.sqlite"
FMT="%Y%m%dT%H%M%SZ"
FMT2="%Y%m%dT%H%M%S"
TZADJ=18000

# The original published .ics file seems to not have lines longer than 70 character.
# This function chop lines into 70 characters or less. The tilde (~) character is
# assumed to be a newline substitute and is replaced by a litteral "\n" after splitting.

function emit70
{
    t=`echo "$1"`
    while [ ${#t} -gt 0 ]
    do
       if [ ${#1} -ne ${#t} ]; then echo -n " "; fi
       echo "$t" | cut -c1-70 | sed 's/~/\\n/g'
       t=`echo "$t" | cut -c71-`
    done
}

# The following is copied directly from the originally published .ics file.

cat <<EOF
BEGIN:VCALENDAR
PRODID:-//Mozilla.org/NONSGML Mozilla Calendar V1.1//EN
VERSION:2.0
METHOD:PUBLISH
BEGIN:VTIMEZONE
TZID:America/New_York
BEGIN:DAYLIGHT
TZOFFSETFROM:-0500
TZOFFSETTO:-0400
TZNAME:EDT
DTSTART:19700308T020000
RRULE:FREQ=YEARLY;BYDAY=2SU;BYMONTH=3
END:DAYLIGHT
BEGIN:STANDARD
TZOFFSETFROM:-0400
TZOFFSETTO:-0500
TZNAME:EST
DTSTART:19701101T020000
RRULE:FREQ=YEARLY;BYDAY=1SU;BYMONTH=11
END:STANDARD
END:VTIMEZONE
EOF
</pre>
# GET EVENTS
# Note that this query does not take into account the possibility of multiple
# calendars stored in the local.sqlite database. Possibly need to first
# 'select distinct cal_id' then loop through those.

sqlite3 "$DATABASE"  \
"select cal_id, id, time_created, last_modified, title, priority, privacy, event_start, event_end,
 event_stamp, event_start_tz, event_end_tz
 from cal_events" | \
while read event
do
   x=`echo $event`
   if [ -z "$x" ]; then continue; fi   # skip blank lines, if any

   # icalString is multi-line. Get separately to make parsing easier

   cal_id=`echo "$event" | cut "-d|" -f1`
   id=`echo "$event" | cut "-d|" -f2`

   icalString=`sqlite3 "$DATABASE" \
   "select icalString from cal_alarms where cal_id = '$cal_id' and item_id = '$id';"`

   time_created=`echo $event | cut "-d|" -f3 | cut -c1-10`
   time_created=$[ $time_created + $TZADJ ]
   time_created=`date -d@$time_created +$FMT`

   last_modified=`echo "$event" | cut "-d|" -f4 | cut -c1-10`
   last_modified=$[ $last_modified + $TZADJ ]
   last_modified=`date -d@$last_modified +$FMT`

   title=`echo "$event" | cut "-d|" -f5`
   priority=`echo "$event" | cut "-d|" -f6`
   privacy=`echo "$event" | cut "-d|" -f7`
   event_start=`echo "$event" | cut "-d|" -f8 | cut -c1-10`
   event_start=`date -d@$event_start +$FMT2`
   event_end=`echo "$event" | cut "-d|" -f9 | cut -c1-10`
   event_end=`date -d@$event_end +$FMT2`

   event_stamp=`echo "$event" | cut "-d|" -f10 | cut -c1-10`
   event_stamp=$[ $event_stamp + $TZADJ ]
   event_stamp=`date -d@$event_stamp +$FMT`

   event_start_tz=`echo "$event" | cut "-d|" -f11`
   event_end_tz=`echo "$event" | cut "-d|" -f12`

   cat << EOF
BEGIN:VEVENT
CREATED:$time_created
LAST_MODIFIED:$last_modified
DTSTAMP:$event_stamp
EOF

   # Split up UIDs longer than 70 characters (could have used emit70() function)

   echo UID:`echo $id | cut -c1-70`
   if [ ${#id} -gt 70 ]; then echo " "`echo $id | cut -c71-`; fi

   echo "SUMMARY:$title"

   if [ -n "$priority" ]; then echo "PRIORITY:$priority"; fi
   echo "DTSTART;TZID=$event_start_tz:$event_start"
   echo "DTEND;TZID=$event_end_tz:$event_end"

   # Get additional calendar properties (LOCATION, PRIVACY, etc.)
   # Some have multi-line entries such as X-ALT-DESC. Substitute
   # tilde (~) for newline (char(10)) to get everything on one
   # line. emit70() will put litteral "\n" back in.

   sqlite3 "$DATABASE" "select key, replace(value,char(10),'~')
       from cal_properties where cal_id = '$cal_id' and item_id = '$id';" | \
   while read xtra
   do
       k=`echo $xtra | cut "-d|" -f1`
       v=`echo $xtra | cut "-d|" -f2-`
       x=`echo $v`

       if [ -n "$x" ]; then emit70 "$k:$v"; fi
   done

   x=`echo $icalString`
   if [ -n "$x" ]; then echo "$icalString"; fi

   echo END:VEVENT
done

echo END:VCALENDAR
exit

Your referenced Auto Export add-on would have been exactly what I need, but it does not work with Thunderbird version 38.4.x -- I tried it.

I do use the extension Automatic Export 0.5.2 for manually backing up my calendars. It works fine for me. What doesn't work for you?

It is a simple matter to edit the install.rdf file of an add-on (IN the ZIP) to change the max version number and make it install.

I don't think the 'max version' bit is still relevant in the way it used to be. As long as an extension has been marked as compatible on AMO, the 'max version' is largely irrelevant. Mine is still set to '14', and as said before, the extension work in both, Thunderbird 38.5.1, and 43 beta1.

christ1 said

I do use the extension Automatic Export 0.5.2 for manually backing up my calendars. It works fine for me. What doesn't work for you?

I installed the add-on, but it never updated my server .ics file after I made changes to the published calendar. You say you use it for "manually backing up ...". Do you mean that literally in that you do it manually and the add-on does not do it automatically? If that's the case, then you haven't demonstrated that the "Automatic Export" (emphasis on "Automatic") does work for you.

In any case, my script to search the local.sqlite calendar database works just great for me, so I don't think I'll need an add-on.