After installing Synthesis SyncML Server and using it for a bit, I decided to look into its innards to see if I could use some of the data. You may recall I'm using this server in a three-way combination, and this adds another piece to the mosaic. I don't use Microsoft Outlook but Lotus Notes. My calendar entries and tasks are replicated onto my BlackBerry, and from there, they are synchronized via SyncML to my server. It is from here, that I wanted to attempt to utilize the calendar and task entries in order to feed other programs, notably Apple's iCal. I am using the "PRO" version of Synthesis AG's product which stores all data received via SyncML into a backend database via ODBC which, in my case, lands in a MySQL datbase on the server. One of the great advantages of this setup is that I have access to my data. It occurred to me to have a closer look at the tasks and events tables, which are respectively named SYNC_TASKS and SYNC_EVENTS (it is a bit unfortunate that in the default configuration, the table names are capitalized; I usually reserve caps for reserved words in SQL statements, but I'm forced to capitalize the table names here, or the examples will not work). Although I describe tasks in a bit more details, the schema for the calendar (events) and for contacts is also easy to determine. Let me first have a look at a task I created on my BlackBerry, and which I then synchronized to the server with the Nexthaus SyncJE for BlackBerry SyncML client. I have filled out all of the fields available on the BlackBerry. One task When pushing this task down to the SyncML server, it receives the task in iCalendar format (RFC 2445).

BEGIN:VCALENDAR
    VERSION:1.0
    PRODID:Blackberry
    BEGIN:VTODO
    DESCRIPTION:Test iCal integration
    DUE;VALUE=DATE-TIME:20070121T160000Z
    PRIORITY:5
    UID:1806671339
    SUMMARY:Todos in Synthesis SyncML
    STATUS:NEEDS ACTION
    END:VTODO
    END:VCALENDAR

I note that not all of the fields I entered on the BlackBerry are contained in the vCard; the categories are missing, and possibly more important, so is the time zone of the entry. Nexthaus have documented this, and it is probably due to my version 4.0 of BlackBerry OS. Their readme clearly says:

There are some limitations in syncing certain fields depending on BlackBerry OS Version. Limitations prior to BlackBerry OS version 4.1: Contacts, will only sync one address either home or work; Calendar, All-day events flag will not be set; Tasks, Time zone setting in task is ignored, the timezone set in device settings will be used instead.

and their documentation also lists further limitations:

There are some fields in the Blackberry PIM that can either not be accessed or is outside the scope of the standard formats used. Here is a list of some fields that are not supported in the vCard, vCal standard or not accessible for sync. Contacts (vCard): Birthday, PIN, Categories, Web Page and User fields 1-4 in Contacts are not supported. Calendar (vCal): Recurring events: Monthly by Position and Yearly by Date not supported. Sensitivity (Private/Public) not supported. Tasks (vCal): Categories not supported.

Upon receiving the vCard, the SyncML server converts that card into SQL statements and inserts or updates the task in the database. For the event we are looking at, the resulting row in the database is

mysql> SELECT * FROM SYNC_TASKS WHERE summary LIKE '%Synthe%';
    *************************** 1. row ***************************
       TASKS_KEY: 60
       FOLDERKEY: 6
         SYNCVIS: 10
      CATEGORIES:
           CLASS:
        MODIFIED: 2007-01-16 23:36:49
         CREATED: NULL
         SUMMARY: Todos in Synthesis SyncML
     DESCRIPTION: Test iCal integration
        LOCATION:
        TIMEZONE: NULL
             DUE: 2007-01-21 17:00:00
        PRIORITY:
          STATUS: NEEDS ACTION
        ATTENDEE:
      ALARM_TIME: NULL
    ALARM_SNOOZE:
    ALARM_REPEAT:
       ALARM_MSG:
    1 row in set (0.00 sec)

What can I do with this information? Well I can further process it. In fact I'm going to create iCalendar files on the fly for import into Apple's iCal or Mozilla Sunbird. Here is a couple of screen shots of the result. iCal from SyncML data with Mozilla Sunbird I was originally inspired by the article Using PHP to Make Basic vCalendar/iCalendar Events; it seemed so easy, that I took up the task of creating something similar. The program itself is quite simple and supports both the calendar with recurring events as well as tasks or todos. I haven't tested all possible combinations of recurring events, but it works for me. You are bound to have to change the timezone data.

<?php
    # fupps-sycal.php (C)2007 by Jan-Piet Mens (jpmens at gmail.com)
    # This program produces iCal subscriptions to the calendar
    # and tasks databases on a Synthesis SyncML Server installation.
    #
    # Use at your own risk. Timezone tested only at my location;
    # Your Mileage Will Vary!
    #
    # Documentation is at
    # http://jpmens.net/2007/01/17/utilizing-syncml-data-to-feed-ical/
    #
    # Bugs:
    #  1. Only one folder per user. Synthesis SyncML server
    #     supports multiple folders per user, but I don't
    #  2. Error-handling needs improvement!
    #  3. Timezone handling is a mess if it works at all.
    #

    # $Header: /home/jpm/src/syncml/fupps-sycal/RCS/fupps-sycal.php,v 1.5 2007/02/05 12:22:29 jpm Exp jpm $
    #
    # $Id: fupps-sycal.php,v 1.5 2007/02/05 12:22:29 jpm Exp jpm $
    #
    # $Log: fupps-sycal.php,v $
    # Revision 1.5  2007/02/05 12:22:29  jpm
    # Completed tasks get completion date = modification date if due date not set.
    #
    # Revision 1.4  2007/02/05 12:03:13  jpm
    # Small fix for events spanning more than one day: show date only
    #
    # Revision 1.3  2007/01/18 17:17:47  jpm
    # Added UTF-8 output to silence Mozilla Sunbird 0.3
    # Small cleanups.
    #
    # Revision 1.2  2007/01/14 18:42:34  jpm
    # *** empty log message ***
    #
    # Revision 1.1  2007/01/14 11:18:24  jpm
    # Initial revision
    #
    #


    $dbhost = 'localhost';
    $dbdatabase = 'syncml';
    $dbuser = 'somebody';
    $dbpass = 'totallysecret';

    mysql_connect($dbhost, $dbuser, $dbpass);
    mysql_select_db($dbdatabase);

    $username = (isset($_SERVER['PHP_AUTH_USER'])) ? $_SERVER['PHP_AUTH_USER'] : "";
    $password = (isset($_SERVER['PHP_AUTH_PW'])) ? $_SERVER['PHP_AUTH_PW'] : "";

    # Sanitize input
    $username = preg_replace('/[\W]/', '//', $username);
    $password = preg_replace('/[\W]/', '//', $password);

    if (strlen($username) < 1 || strlen($password) < 1) {
        header('WWW-Authenticate: Basic realm="My Realm"');
        header('HTTP/1.0 401 Unauthorized');
        echo 'Unauthorized';
        exit;
    } else {
        $sql = "SELECT COUNT(*) AS nr FROM SYNC_USERS WHERE userid = '$username' AND passwd = '$password'";

        $res = mysql_query($sql);
        $data = mysql_fetch_object($res);

        if ($data->nr != 1) {
            header('WWW-Authenticate: Basic realm="My Realm"');
            header('HTTP/1.0 401 Unauthorized');
            echo 'Forget it! Unauthorized';
            exit;
        }

        $sql = "SELECT folder_key
           FROM SYNC_USERS u,
                SYNC_FOLDERS f,
                SYNC_PERM p
           WHERE u.user_key = p.userkey
           AND   p.folderkey = f.folder_key
           AND   u.userid = '$username'";

        $res = mysql_query($sql);
        $data = mysql_fetch_object($res);

        $folderkey = $data->folder_key;
    }

    $filename = "$username.ics";
    header("Content-Type: text/x-vCalendar; charset=UTF-8");
    header("Content-Disposition: inline; filename=$filename");

    print <<<ENDHEAD
BEGIN:VCALENDAR
VERSION:2.0
METHOD:PUBLISH
X-WR-CALNAME;VALUE=TEXT:$username Calendar
X-WR-TIMEZONE;VALUE=TEXT:Europe/Berlin
PRODID:-//Jan-Piet Mens//fupps-sycal 1.0//EN

ENDHEAD;

    timezone();

    ###################################################
    # Events

    $sql = "SELECT events_key as id, 
       DATE_FORMAT(dtstart, '%Y%m%dT%H%i%s') AS dtstart, 
       DATE_FORMAT(dtend, '%Y%m%dT%H%i%s') AS dtend, 
       summary, 
       description, 
       location, 
       rr_freq, rr_interval, DATE_FORMAT(rr_end, '%Y%m%dT%H%i%s') AS rr_end 
       FROM SYNC_EVENTS 
       WHERE folderkey = $folderkey ";

    $res = mysql_query($sql);

    $tz = ';TZID=Europe/Berlin';
    $year_now = date('Y', time());

    while ($data = mysql_fetch_object($res)) {
        $id     = 'Fupps-SyCal' . $data->id;
        $dtstart    = $data->dtstart;
        $dtend      = $data->dtend;
        $subj       = preg_replace('/\s+/', ' ', $data->summary);
        $description    = $data->description;
        $description    = str_replace("\r", '\n', $description);
        $description    = str_replace("\n", '', $description);
        $location   = $data->location;
        $rr_freq    = $data->rr_freq;
        $rr_interval    = $data->rr_interval;
        $rr_end     = $data->rr_end;
        
        $subj = utf8_encode($subj);
        $description = utf8_encode($description);
        $location = utf8_encode($location);

        print "BEGIN:VEVENT\n";
        print "SUMMARY:$subj\n";
        if (strlen($description)) {
            print "DESCRIPTION:$description\n";
        }
        if (strlen($location)) {
            print "LOCATION:$location\n";
        }
        
        if (strcmp(substr($dtstart, 9, 4), "0000") == 0) {
            # event spans multiple days
            print "DTSTART;VALUE=DATE:" . substr($dtstart, 0, 8) . "\n";
            if ($dtend) {
                print "DTEND;VALUE=DATE:" . substr($dtend, 0, 8) . "\n";
            } else {
                print "DTEND;VALUE=DATE:" . substr($dtstart, 0, 8) . "\n";
            }
        } else {
            print "DTSTART:$dtstart\n";
            if ($dtend) {
                print "DTEND:$dtend\n";
            } else {
                print "DTEND:$dtstart\n";
            }
        }

        if ($rr_freq) {
            $freq = 'UNKNOWN';
            switch ($rr_freq) {
                case 'YM': $freq = 'YEARLY'; break;
                case 'WW': $freq = 'WEEKLY'; break;
                case 'D':  $freq = 'DAILY'; break;
            }
            print "RRULE:FREQ=${freq};INTERVAL=${rr_interval};UNTIL=${rr_end}\n";
        }
        print "UID:$id\n";
        print "END:VEVENT\n";
    }

    ###################################################
    # Tasks (Todos)

    $sql = "SELECT tasks_key as id,
       DATE_FORMAT(due, '%Y%m%dT%H%i%s') AS due,
       DATE_FORMAT(modified, '%Y%m%dT%H%i%s') as modif,
       summary,
       description,
       status,
       priority
       FROM SYNC_TASKS WHERE folderkey = $folderkey";

    $res = mysql_query($sql);

    $seq = 0;
    while ($data = mysql_fetch_object($res)) {
        $id     = 'Fupps-SyCal-Todo-' . $data->id;
        $due        = $data->due;
        $priority   = $data->priority;
        $modif      = $data->modif;
        $subj       = preg_replace('/\s+/', ' ', $data->summary);
        $description    = $data->description;
        $description    = str_replace("\r", '\n', $description);
        $description    = str_replace("\n", '', $description);
        $status     = $data->status;
        
        $priority = ($priority == 'HIGH') ? 1 : 9;

        $seq++;

        $subj = utf8_encode($subj);
        $description = utf8_encode($description);

        print "BEGIN:VTODO\n";
        print "DTSTART;TZID=Europe/Berlin:20070116T000000\n";
        print "SUMMARY:$subj\n";
        print "DESCRIPTION:$description\n";
        print "UID:FPS-TODO-$id\n";
        print "SEQUENCE:$seq\n";
        print "DTSTAMP:20070116T214010Z\n";
        print "PRIORITY:$priority\n";
        if ($due) {
            print "DUE:$due\n";
        }
        switch ($status) {
            case 'COMPLETED';
                $due = (strlen($due) < 8) ? $modif : $due;
                print "STATUS:COMPLETED\n";
                print "COMPLETED:$due\n";   # fixme
                break;
        }
        print "END:VTODO\n";


}
print "END:VCALENDAR\n";
exit;

function timezone() {
    print <<<TZEND
BEGIN:VTIMEZONE
TZID:Europe/Berlin
LAST-MODIFIED:20050910T165907Z
BEGIN:DAYLIGHT
DTSTART:20050327T010000
TZOFFSETTO:+0200
TZOFFSETFROM:+0000
TZNAME:CEST
END:DAYLIGHT
BEGIN:STANDARD
DTSTART:20051030T030000
TZOFFSETTO:+0100
TZOFFSETFROM:+0200
TZNAME:CET
END:STANDARD
BEGIN:DAYLIGHT
DTSTART:20060326T030000
TZOFFSETTO:+0200
TZOFFSETFROM:+0100
TZNAME:CEST
END:DAYLIGHT
END:VTIMEZONE

TZEND;
}

?>

How do I use this program? I have it running under an Apache web server in its own directory. Since iCal complains when the subscription file is not called .ics, the program itself is named thus, but I have to tell Apache how to handle such files.

$ cat .htaccess
    AddType application/x-httpd-php .ics
    
    Order Deny,Allow
    Deny from all
    Allow from 127.0.0.1
    Allow from 192.168

This forces Apache to use PHP when processing files ending in .ics. You might like to, as I have, protect the resource from prying eyes with Apache's Access Control directives. The program itself uses Synthesis' SYNC_USER table to perform authentication, although that only works if I use the clear-text password in the database table (the default configuration file for Synthesis SyncML server defines <cleartextpw> true). In iCal, use the Calendar menu to Subscribe to a new calendar and enter the URL to your installation in the Subscribe to field. After clicking on Subscribe, iCal will attempt to retrieve the calendar from our PHP program, but it will be requested to authenticate. Use your SyncML username and password (the same values to use in your mobile phone) to access your calendar. At this point you can chose a new name for the subscription and request that iCal refreshes your calendar periodically. Note that calendars subscribed to thusly are read-only. This might or might no work with Microsoft Outlook; I haven't tested it. If you do succeed, drop me a note.

Flattr this
MySQL, Apache, Database, SyncML, Mobile, and MacOSX :: 17 Jan 2007 :: e-mail

Comments

blog comments powered by Disqus