It turns out that Daily Notes, Day Events, and Holidays all get the same treatment from the CAPI export process, so I need to rewrite the iCalendar output for all of them. Instead of using a DURATION to these events, I ended up just removing DTEND. Thus we end up with an event with DTSTART but no DTEND, which iCalendar defines as an event that takes up no time. That’s pretty much accurate, except in the case of Day Events, which technically take up all day. Unfortunately, in Oracle Calendar, some people put entries in as Day Events when they really should be Daily Notes. For that reason I’m not quite decided yet as to whether I should put DURATION in for Day Events. In any case, I’ve fixed the problem, and everything shows up in PHP iCalendar. For now I’ll just leave DURATION out, until I change my mind.

One thing I might consider, is splitting the four Oracle Calendar categories (appointment, daily note, day event, holiday) into separate calendars, so I can differentiate the various events more easily in iCal and on the Palm. It seems like a good idea, but will require some extra work.

Well, I found the first problem with my exported iCalendar data. In iCal, I turned off everything except my two exported Oracle Calendar views (one done via export/import, the other extracted and published), so I could compare the two. I noticed that my published calendar was not showing recurring events properly. Only one instance of each event was showing up.

Now, I already knew that the CAPI is supposed to export multiple VEVENT records for recurring events, instead of adding RRULE attributes. But I didn’t expect this to be a problem, as I just need the stuff to show up, and I’m not worried about editing the exported data, adding new recurrences, etc. I’m doing all that kind of manipulation via the Oracle Calendar client.

So, why am I only seeing one event? First thing I checked was the .ics file. Maybe the docs are wrong, and it’s exporting RRULEs after all, and I’m ignoring them? Nope… each recurrence does have a separate VEVENT in the iCalendar file. So why aren’t they all showing up? Because they all have the same UID.

So, CAPI does export separate VEVENTs, but it doesn’t make each one a “real” separate event by assigning it a new UID. Kind of annoying. It’s one thing to cheap out and not support RRULE, but it’s entirely another thing when the result doesn’t comply with the iCalendar spec.

Interestingly, the vCalendar export does assign unique IDs to the recurrences. Too bad they couldn’t do it with the iCalendar export. Looks like I’m going to have to do it myself. For my first stab, I’ll just build a hash of UIDs as I’m reading the iCalendar file, and if I find a duplicate, I’ll append an ascending number to the end. Hopefully that’ll work OK. It’s dependent on Oracle Calendar exporting its data in the same order each time. Dunno if it does or not. If not, it means that the recurring events’ UIDs may not be consistent across multiple exports. That may or may not present a problem with the Palm export. I guess time will tell.

Well, off I go to make this happen.

Today I began work on a Perl script to massage the exported Oracle Calendar data before publishing. I exported a full 3-year date range (the same date range I’m currently loading into iCal via export/import), and began addressing some of the issues I noted in my previous post.

• Times showing up incorrectly in PHP iCalendar: This was just a config thing. I hadn’t configured PHP iCalendar with my time zone, so it was defaulting to UTC. The iCal-exported stuff was showing up correctly because Apple adds explicit time zone info to the .ics file.
• I did a few things to fix up the display of DESCRIPTION fields. First, I used MIME::QuotedPrint to strip out some of the ‘=20’s and other MIME artifacts that were lying around. Then, I stripped blank lines out to keep entries from getting truncated. I was originally un-escaping commas (by stripping out leading backslashes), but apparently they need to be escaped to comply with the iCalendar specification. iCal displays the commas correctly (without backslashes), but PHP iCalendar leaves the backslashes in.
• I added an Apple-specific field, X-WR-CALNAME. iCal uses this as the default calendar name to use when importing or subscribing to the file. Not totally necessary, but saves typing.

With those changes, everything shows up nicely now and the descriptions look good. The end product is a usable .ics file that I can subscribe to with iCal, and it includes everything I want except alarm and attendee data. I’ll tackle alarms first.

Alarms: The vCalendar export includes alarm data. The strange thing is, I have no idea where the alarms in the vCalendar file are coming from. I’ve never defined any alarms within Oracle Calendar, yet somehow they’re showing up in the vCalendar export file. Certain events will show up with alarms in the vCalendar file, but when I go into Oracle Calendar and bring up that event, it will say there is no alarm defined. I almost wonder if the vCalendar export process is picking up other peoples’ alarms or something. Very strange.

Looks like the iCalendar export gets this right. I went into Oracle Calendar and defined an alarm (Oracle Calendar actually calls them reminders), then re-exported the data in iCalendar format, and I got a VALARM section added to that event. I’ll have to see if it shows up properly in iCal. I’d like to be able to set alarms and have them show up on my Palm, as I’m prone to get sidetracked and forget meetings otherwise.

OK, looks like iCal picks up the VALARMs properly. Just remains to be seen how they’ll show up on the Palm. It looks like I can set different alarm types (message, email, audio etc.) using the ACTION attribute. As exported, they show up as ACTION:DISPLAY which gets translated to ‘Message’ in iCal. At some point I’ll have to see how different actions will affect behavior on iCal and the Palm. Then if necessary, I can tweak the ACTION attribute with my Perl script.

With that, I think this gives me all the functionality I was getting with the export/import process, so there’s no reason I can’t make this my “official” method now, and sync this data to my Palm. If that works OK, I’ll work on automating it and bringing in attendee data.

Made a first stab at publishing exported output from Oracle Calendar tonight. I ran my CAPI program for a one-week time window, deleted the MIME headers at the beginning and end of the output, and slapped it up on my web server without any further mods. It actually sorta worked. Observations:

• The iCalendar data appears to come through in DOS format, although neither iCal nor PHP iCalendar seem to have problems reading it. So it appears there’s no immediate need to convert.
• PHP iCalendar shows the times incorrectly. The exported data has the time in GMT (or “Zulu” time). iCal correctly converts things to EST, but PHP iCalendar does not. I probably need to add timezone data to the file.
• One of my meetings has a long DESCRIPTION property. The text is kind of screwed up. There are a lot of backslashed characters, a few ‘=20’s thrown in, and most importantly, the exported data has an extra carriage return thrown in. Both apps truncate the description at this spot.
• Attendees are missing (which was expected) as well as alarms. Hopefully, getting alarms is just a matter of adding the appropriate alarm stuff to my list of properties to export. We’ll see.

So it’s obvious that I’ll need to write some Perl code to massage the exported data a bit, but I already expected that. The important thing is, we’re definitely making progress here.

See subject. I checked the API documentation, and it has a complete list of iCalendar attributes that the server returns. So, in my downloader code I just listed out each attribute except ATTENDEE. With that list of attributes, it took about 1 minute to download a year’s worth of data. When I added ATTENDEE in, the download pretty much ground to a halt.

So at any rate, it looks like I want to leave ATTENDEE out when doing my bulk downloads. That’s a bit of a bummer, though, because it means I won’t get attendee lists for meetings etc. It’d be useful to have that. What about this compromise: for a small window, say today through two weeks from today, I’ll export events with attendees. Then, for events outside that window, I’ll export events without attendees. I think that gives me the best compromise between performance and function.

The only other possible issue I can think of is, the UID field. Each event has a UID field that uniquely identifies that event. I’m hoping the UID stays consistent across multiple exports of the same data. I’m pretty sure the Palm sync stuff keys off the UID, so keeping the UID consistent should make the Palm sync process faster and more reliable. I did try exporting the same date range (all of 2005) twice, saving the results to a file, and comparing the files. The UIDs were the same both times, so that’s a good (although not conclusive) sign.

Next up: I’ll massage the data as necessary and try turning it into an iCal subscription.

I used the Oracle Calendar GUI client to create iCalendar and vCalendar exports for the same date range. The formats are obviously different, but the data extracted is similar. Exceptions: The vCalendar export includes “TODO” items from Oracle Calendar, which I’ve never used and don’t care about. And, the iCalendar export includes email addresses of attendees. I wonder if that’s what’s slowing it down. If the email lookup process is time consuming, it will REALLY drag things down, because our calendar server includes a LOT of entries with many attendees.

Also, earlier I tried importing one of these iCalendar files into Apple’s iCal app, and had problems. It would import one entry and then stop. Upon closer examination, I think I see why this is happening. The exported iCalendar file encloses every single event inside a separate BEGIN:VCALENDAR / END:VCALENDAR block, complete with all the vCalendar headers, etc.! In other words, it’s not a valid iCalendar-format file at all. iCal probably aborts when it sees the first END:VCALENDAR, thinking it’s done.

Anyhow, back to my earlier point, I decided to test my theory that the email lookups are dragging down the iCalendar export. The API allows me to explicitly specify which iCalendar properties I want included in the exported file. So, I told it to include just a small set of properties (start date, end date, summary), to see how it would do. Yep, the export was much faster. Now, I just need to build up the full set of properties I need, omit the attendee info, and see how it does then. Unfortunately, there’s no way to say “I want every property except attendee info.” I either have to request every property, or request a specific list. So, I’ll need to look at the iCalendar spec, get a full list of properties, and explicitly list out the ones I want. Kind of an annoying limitation if you ask me.

The other question is, is there a way to get the vCalendar format through the API? Or, assuming the email lookup is the culprit, can I pull attendee info without doing the email lookup? Dunno, but I’m getting a little ahead of myself here.

Tonight I wrote my first app with the Calendar API. Basically I used the calendar.c demo code as a reference, and pulled out the pieces I needed to do a quick iCalendar download app. It worked without a hitch. With just a little more tweaking, I’ll have a command-line app I can use to fetch arbitrary date ranges in iCalendar format.

The caveat: It’s very slow with date ranges of more than just a few days. I guess that’s to be expected, given the performance I’ve seen with iCalendar downloads from the GUI client. It does eventually come back, though. A one-month range took a few minutes to complete; I hate to think how long three years would take!

Of course, I plan to run this unattended from cron, so in theory I don’t care how long it takes, even if it’s a couple hours. The question is, if I try to suck down a three-year range in one big chunk, will it fubar the calendar server? Obviously that would not be good. The other option would be to pick a manageable chunk, say a few months, and only download that much at a time. I could just run it in a loop until I get the full 3 years. Will have to play with this.

The other puzzler here is, the Oracle Calendar GUI calendar lets me download in either of 2 formats: iCalendar or something it calls “vCalendar”. I’m not sure of the exact difference, but vCalendar is a lot faster to download. What’s the difference between the two, and is there a way of doing the vCalendar download from the API? I wonder if there are certain iCalendar fields that take significantly longer to process. If the vCalendar download omits those, that could speed things up significantly.

I suppose the thing to do is generate both iCalendar and vCalendar dumps for a specific date range, and compare the differences between the two. That may shed some light on things.