SyncML Synchronization of the iPhone Calendar

Apple did not provide access to the build-in calendar of the iPhone with the early versions of their SDK. Therefore no AppStore application could use the native calendar of the iPhone running with iOS 3.x.

 

SyncML Synchronization of the iPhone Calendar with the Synthesis Todo+Cal+Sync

 The herein documented method does only work for OS 3.0.


The Synthesis Todo+Cal+Sync application is not only a powerful calendar and task management tool but does also provide SyncML OTA synchronization for your data. The SyncML part is highly customizable and does even allow you to synchronize external data — like the iPhone Calendar.
All you have to do is to make the SQLite Database file accessible to the application and extent the tables a little bit. Since the modifications for OS3.0 require nsdate support by the SyncML Engine you have to use 3.2.0.31++.
Here is a step by step procedure to make things work:

1. Access to the Calendar Database File

Each AppStore application is restricted to its own sandbox when it comes to file system access. The build-in Calendar on the other hand can access the full user partition. So the easiest way to grant Todo+Cal+Sync access to the Calendar.sqlitedb file is to move it to its sandbox directory. For the iPhone Calendar application we leave a link at the original place.
First lets find the sandbox:
ssh mobile@iphone
find ~/Applications -name "TodoSync.app"

will tell you the sandbox directory e.g. /var/mobile/Applications/5150512B-0803-44F5-8998-F1B6F9DD6F56  
Within the sandbox you will find a Documents directory. This contains the e.g. todoz.sqlitesdb database file of the application.
Copy ~/Library/Calendar/Calendar.sqlitedb to this place and make a link from the original path to here (It is a good idea to keep a backup copy if something goes wrong).

2. Extend the Database to Support Synchronization.

I added two fields to the Event  table (modified and uid) and two TRIGGERs. The modifications do not harm the Calendar application:
sqlite3 Calendar.sqlitedb

 

ALTER TABLE Event ADD modified INTEGER;

ALTER TABLE Event ADD uid TEXT;

CREATE TRIGGER update_modified_after_update AFTER UPDATE ON Event

BEGIN

UPDATE Event SET modified = strftime('%s','now', 'localtime') WHERE ROWID = OLD.ROWID;

END;

CREATE TRIGGER update_modified_after_alarm AFTER UPDATE ON Alarm

BEGIN

UPDATE Event SET modified = strftime('%s','now', 'localtime') WHERE ROWID = OLD.entity_id;

END;

CREATE TRIGGER update_modified_after_rrule AFTER UPDATE ON Recurrence

BEGIN

UPDATE Event SET modified = strftime('%s','now', 'localtime') WHERE ROWID = OLD.event_id;

END;

 

3. Add the Apple Calendar Datastore to the Todo+Cal+Sync Configuration

I'm synchronizing my calendar with my company's Exchange, too. To avoid the synchronisation of the business entries via SyncML I skip calendar_id=29 (the Exchange calendar) items. Append the following code after the existing datastores in you Todo+Cal+Sync configuration file iphone_client.xml. You can find this configuration underneath the TodoSync.app directory in you application sandbox. To get a  calendar icon for this datastore you may use a copy of licon_1002.png as licon_1032.png. These icons stay in the TodoSync.app diectory as well.

 

    <!-- Native Apple Calendar Syncronization -->

    <datastore name="apple_cal" type="sql" ifdef="cal">

      <dbtypeid>1032</dbtypeid> <!-- This is the ID used to identify the datastore in progress events. Must be unique among all datastores -->
      <displayname>AppleCalendar</displayname>
      <dsavailflag>2</dsavailflag>  <!-- Used to remote provision availability and control settings: contacts = 1, events = 2, tasks = 4, memos = 8, emails = 16 -->

      <sqlitefile expand="yes">$(binfilespath)/Calendar.sqlitedb</sqlitefile>

      <alertprepscript><![CDATA[
        // pass on configured filter expressions
        setrecordfilter(targetsetting("remoteFilters"),false); // exclusive
        // If a day range is selected and enabled in the settigs, apply it for the current sync
        if (targetsetting("extras") & 1 == 1) {
          setdaysrange(
            targetsetting("limit1"),
            targetsetting("limit2")
          );
        }
      ]]></alertprepscript>

      <datastoreinitscript><![CDATA[
      ]]></datastoreinitscript>


      <!-- causes item changes to be committed immediately after the change. Usually set to yes -->
      <commititems>yes</commititems>

      <!-- The following information defines how the data table is accessed -->

      <datacharset>UTF-8</datacharset>
      <datalineends>unix</datalineends>
      <quotingmode>singlequote</quotingmode> <!-- SQLite uses standard SQL here! -->
      <!-- datatimezone>UTC</datatimezone -->

      <!-- SQLite DB has modification timestamps -->
      <modtimestamp>yes</modtimestamp>
      <lastmodfieldtype>unixtime_s</lastmodfieldtype>

      <synctimestampatend>yes</synctimestampatend>
      <storesyncidentifiers>no</storesyncidentifiers>

      <!-- if set to yes, all timestamps read from the DB are converted to user time zone (from DB time zone) before sending to remote -->
      <userzoneoutput>yes</userzoneoutput>

      <updateallfields>yes</updateallfields>
      <dbcanfilter>no</dbcanfilter>

      <!-- SQLite3 has autoincrementing ROWID
           ID is implicitly returned -->
      <obtainidafterinsert>yes</obtainidafterinsert>
      <insertreturnsid>no</insertreturnsid>

      <!-- statement that retrieves KEY and MODIFIED timestamp for all records to be synced.
           Note that %AF or %WF must be included to make filtering work
      -->
      <selectidandmodifiedsql>SELECT ROWID,modified FROM Event WHERE NOT calendar_id = 29</selectidandmodifiedsql> <!-- calendar_id=29 is Exchange -->

      <!-- statements to read, insert, update and delete single records in the database
           %N represents the name list of all mapped fields
           %v represents the value list of all mapped fields
           %V represents the name=value list of all mapped fields
           %k represents the record key (CONTACTS_KEY, EVENTS_KEY...)
           %f represents the folder key (see <folderkeysql> above)
      -->
      <selectdatasql>SELECT %N FROM Event WHERE ROWID=%k</selectdatasql>
      <insertdatasql>INSERT INTO Event (modified,%N) VALUES (%M,%v)</insertdatasql>
      // We can't use UPDATE since this would trigger a change of modified afterwards
      <updatedatasql>DELETE FROM Event WHERE ROWID=%k %GO INSERT INTO Event (ROWID,modified,%N) VALUES (%k,%M,%v)</updatedatasql>
      <deletedatasql>DELETE FROM Event WHERE ROWID=%k</deletedatasql>
      <zapdatasql>DELETE FROM Event WHERE NOT calendar_id = 29 %GO DELETE FROM OccurrenceCacheDays WHERE NOT calendar_id = 29 %GO DELETE FROM OccurrenceCache WHERE NOT calendar_id = 29</zapdatasql>

      <!-- if set, SyncML DS 1.2 filtering is possible and will be indicated in server devInf -->
      <ds12filters>yes</ds12filters>


      <fieldmap fieldlist="todoz">

        <initscript><![CDATA[
         timestamp epoch;
         timestamp event_day;
         integer   const_zero;
         integer   ALL_DAY;
         integer   ORIG_EVENT_ID;
         integer   INTERVAL;
         integer   event_id;
         integer   calendarid;
         integer   exchangeid;
         string    EVENT_TZ;
         integer   RR_FREQUENCY;
         integer   Recurrences;
         integer   Alarms;

         const_zero = 0;

        ]]></initscript>

        <!-- this script is called after reading one record from the database. -->
        <afterreadscript><![CDATA[
          // all these are events
          ISEVENT=1;

          // override own UID with UID from parent, if there is one
          if (ORIG_EVENT_ID!=0) {
            SQLEXECUTE("SELECT uid FROM Event WHERE ROWID=" + DBLITERAL(ORIG_EVENT_ID,"numeric"));
            if (SQLFETCHROW()) {
              // inherit UID
              SQLGETCOLUMN(1,UID,"string");
            }
            SQLCOMMIT();
          }

          // make sure we have an UID if none is stored in the event record
          if (UID==EMPTY) {
              UID=newuid();

              // save OccurrenceCache entry
              SQLEXECUTE("SELECT day FROM OccurrenceCache WHERE event_id=%k");
              if (SQLFETCHROW()) {
                  SQLGETCOLUMN(1,event_day,"nsdate");
              } else {
                  event_day = DATEONLY(DTSTART);
              }
              SQLCOMMIT();

              // UPDATE would trigger a new modification timestamp
              SQLEXECUTE("DELETE FROM Event WHERE ROWID=%k");
              SQLCOMMIT();
              if (INTERVAL != EMPTY) {
                  // Insert the alarm again (deleted with the event)
                  SQLEXECUTE("INSERT INTO Alarm (trigger_date,trigger_interval,type,entity_id,entity_type)"
                      + " VALUES (-2147483648,%d(INTERVAL,numeric),0,%k,2)");
              }
              if (RR_FREQUENCY > 0) {
                  // Insert the RRULE again (deleted with the event)
                  SQLEXECUTE("INSERT INTO Recurrence (frequency,interval,end_date,event_id,"
                      + "week_start,count,cached_end_date,by_month_months)"
                      + " VALUES (%d(RR_FREQUENCY,numeric),%d(RR_INTERVAL,numeric),%d(RR_END,nsdate),%k,"
                      + "1,0,0,0)");
              }
              // Restore original entry with the old modification time
              SQLEXECUTE("INSERT INTO Event (ROWID,calendar_id,uid,modified,summary,location,description,"
                  + "start_date,start_tz,end_date,all_day,orig_event_id,orig_start_date,organizer_id,organizer_is_self,status,external_status) VALUES (%k,%d(calendarid,numeric),"
                  + DBLITERAL(UID,"string") + ","
                  + DBLITERAL(DMODIFIED,"unixtime_s") + ","
                  + DBLITERAL(SUMMARY,"string") + ","
                  + DBLITERAL(LOCATION,"string")+ ","
                  + DBLITERAL(DESCRIPTION,"string") + ","
                  + DBLITERAL(DTSTART,"nsdate") + ","
                  + DBLITERAL(EVENT_TZ,"string") + ","
                  + DBLITERAL(DTEND,"nsdate") + ","
                  + DBLITERAL(ALL_DAY,"numeric") + ","
                  + DBLITERAL(ORIG_EVENT_ID,"numeric") + ","
                  + DBLITERAL(ORIGSTART,"nsdate") +",0,0,0,0)");
              SQLCOMMIT();

              SQLEXECUTE("REPLACE INTO OccurrenceCache (event_id,day,calendar_id,store_id,occurrence_date) " +
                         "VALUES (%k,%d(event_day,nsdate),%d(calendarid,numeric),1,%d(DTSTART,nsdate))");
              SQLCOMMIT();
          }

          SETTIMEZONE(DTSTART, "UTC");
          SETTIMEZONE(DTEND, "UTC");

          if (INTERVAL!=EMPTY) {
                  ALARM_TIME = DTSTART + TIMEUNITS(INTERVAL);
          }

          if (ALL_DAY == 1) {
             Recurrences = ALLDAYCOUNT(DTSTART, DTEND, TRUE);
             MAKEALLDAY(DTSTART,DTEND, Recurrences);
          }

          if (RR_FREQUENCY == 1) {
              TARGET.RR_FREQ = "D ";
          } else if (RR_FREQUENCY == 2) {
              TARGET.RR_FREQ = "WW";
          } else if (RR_FREQUENCY == 3) {
              TARGET.RR_FREQ = "MD";
          } else if (RR_FREQUENCY == 4) {
              TARGET.RR_FREQ = "YM";
          } else {
              TARGET.RR_FREQ = "";
          }

          if (RR_END != 0) {
              RR_END = DATEONLY(RR_END);
              SETTIMEZONE(RR_END, "UTC");
          }

          // look up CATEGORY
          CATEGORIES = UNASSIGNED;
          if (calendarid!=EMPTY) {
            SQLEXECUTE("SELECT title FROM Calendar WHERE ROWID=" + DBLITERAL(calendarid,"numeric"));
            if (SQLFETCHROW()) {
              SQLGETCOLUMN(1,CATEGORIES[0],"string");
            }
            SQLCOMMIT();
          }
        ]]></afterreadscript>

        <!-- this script is called before writing one record to the
             database. PRIORITY_TEXT must be assigned the value to be
             written to the database -->
        <beforewritescript><![CDATA[
          ORIG_EVENT_ID = 0;
          Alarms = 0;

          if (ALARM_TIME!=EMPTY) {
              Alarms = 1;
              INTERVAL = - SECONDS(DTSTART - ALARM_TIME);
          }

         // Adjust date_time values
          if (ALLDAYCOUNT(DTSTART,DTEND,TRUE) > 0) {
              ALL_DAY = 1;
              DTSTART = DATEONLY(DTSTART);
              DTEND = DATEONLY(DTEND);
              EVENT_TZ = "_float";
          } else {
              ALL_DAY = 0;
              EVENT_TZ =  USERTIMEZONE();
              SETTIMEZONE(DTSTART, USERTIMEZONE());
              SETTIMEZONE(DTEND, USERTIMEZONE());
          }

          Recurrences = 0;

          if (RR_INTERVAL != EMPTY) {
              Recurrences = 1;
          }

          if (RR_FREQ == "D ") {
              RR_FREQUENCY = 1;
          } else if (RR_FREQ == "W " || RR_FREQ == "WW") {
              RR_FREQUENCY = 2;
          } else if (RR_FREQ == "MD") {
              RR_FREQUENCY = 3;
          } else if (RR_FREQ == "YM") {
              RR_FREQUENCY = 4;
          } else {
              Recurrences = 0;
          }

          if (RR_END == EMPTY) {
              RR_END = 0;
          } else {
              RR_END = DATEONLY(RR_END) + TIMEUNITS(86399); //23:59:59;
              SETTIMEZONE(RR_END, USERTIMEZONE());
          }

          // - look up Calendar id if CATEGORIES is supported
          calendarid = 1; // Default
          if (CATEGORIES != UNASSIGNED) {
            if (CATEGORIES[0]!=EMPTY) {
              SQLEXECUTE("SELECT ROWID FROM Calendar WHERE title=" + DBLITERAL(CATEGORIES[0],"string"));
              if (SQLFETCHROW()) {
                SQLGETCOLUMN(1,calendarid,"numeric");
              } else { // insert a new calendar
                SQLEXECUTE("INSERT INTO Calendar (title,store_id,read_only,hidden,color_r,color_g,color_b,color_is_display)"
                           + "VALUES (" + DBLITERAL(CATEGORIES[0],"string") + ",1,0,0,127,127,127,1)");
                calendarid = SQLITELASTID();
              }
            }
            SQLCOMMIT();
          }

          if (ORIGSTART) {
            SQLEXECUTE("SELECT ROWID FROM Event WHERE orig_event_id=0 AND uid=" + DBLITERAL(UID,"string"));
            if (SQLFETCHROW()) {
              // link existing exception record to recurrence event
              SQLGETCOLUMN(1,ORIG_EVENT_ID,"numeric");
            }
            SQLCOMMIT();
          }

        ]]></beforewritescript>


    <!--finishscript><![CDATA[

        ]]></finishscript -->

        <finalisationscript><![CDATA[
          integer count;

          DEBUGSHOWITEM();

          event_day = DATEONLY(DTSTART);

          SQLEXECUTE("REPLACE INTO OccurrenceCache (event_id,day,calendar_id,store_id,occurrence_date) " +
                     "VALUES (%k,%d(event_day,nsdate),%d(calendarid,numeric),1,%d(DTSTART,nsdate))");
          SQLCOMMIT();

        ]]></finalisationscript>

        <map name="modified"           references="DMODIFIED" type="unixtime_s" mode="r"/>
        <map name="uid"                references="UID" type="string" mode="rw"/>
        <map name="summary"            references="SUMMARY" type="string" mode="rw"/>
        <map name="location"           references="LOCATION" type="string" mode="rw"/>
        <map name="description"        references="DESCRIPTION" type="string" mode="rw"/>
        <map name="calendar_id"        references="calendarid" type="numeric" mode="rw"/>
        <map name="calendar_id"        references="CATEGORIES" type="numeric" mode="x"/>
        <map name="start_date"         references="DTSTART" type="nsdate" mode="rw"/>
        <map name="start_tz"           references="DTSTART" type="zonename" mode="r"/>
        <map name="start_tz"           references="EVENT_TZ" type="string" mode="rw"/>
        <map name="end_date"           references="DTEND" type="nsdate" mode="rw"/>
        <map name="start_tz"           references="DTEND" type="zonename" mode="r"/>
        <map name="all_day"            references="ALL_DAY" type="numeric" mode="rw"/>
        <map name="orig_event_id"      references="ORIG_EVENT_ID" type="numeric" mode="rw"/>
        <map name="orig_start_date"    references="ORIGSTART" type="nsdate" mode="rw"/>
        <map name="start_tz"           references="ORIGSTART" type="zonename" mode="r"/>
        <map name="organizer_id"       references="const_zero" type="numeric" mode="w"/>
        <map name="organizer_is_self"  references="const_zero" type="numeric" mode="w"/>
        <map name="status"             references="const_zero" type="numeric" mode="w"/>
        <map name="external_status"    references="const_zero" type="numeric" mode="w"/>

        <array sizefrom="Alarms">
          <alwaysclean>yes</alwaysclean>

          <selectarraysql>SELECT %N FROM Alarm WHERE entity_id=%k</selectarraysql>
          <insertelementsql>INSERT INTO Alarm (trigger_date,trigger_interval,type,entity_id,entity_type) VALUES (-2147483648,%d(INTERVAL,numeric),0,%k,2)</insertelementsql>
          <deletearraysql>DELETE FROM Alarm WHERE entity_id=%k</deletearraysql>

          <maxrepeat>1</maxrepeat> <!-- we currently support only one alarm per event -->
          <repeatinc>1</repeatinc>
          <storeempty>no</storeempty>

          <map name="trigger_interval" references="INTERVAL" type="numeric" mode="rw"/>
        </array>
   
    <array sizefrom="Recurrences">
          <alwaysclean>yes</alwaysclean>

          <selectarraysql>SELECT %N FROM Recurrence WHERE event_id=%k</selectarraysql>
          <insertelementsql>INSERT INTO Recurrence (%N,event_id,week_start,count,cached_end_date,by_month_months) VALUES (%v,%k,1,0,0,0)</insertelementsql>
          <deletearraysql>DELETE FROM Recurrence WHERE event_id=%k</deletearraysql>

          <maxrepeat>1</maxrepeat> <!-- we support only one RRULE per event -->
          <repeatinc>1</repeatinc>
          <storeempty>no</storeempty>


          <map name="interval" references="RR_INTERVAL" type="numeric" mode="rw"/>
          <map name="frequency" references="RR_FREQUENCY" type="numeric" mode="rw"/>
          <map name="end_date" references="RR_END" type="nsdate" mode="rw"/>
        </array>
        <array sizefrom="EXDATES">
          <alwaysclean>yes</alwaysclean>
          <selectarraysql>SELECT %N FROM EventExceptionDate WHERE event_id=%k</selectarraysql>
          <insertelementsql>INSERT INTO EventExceptionDate (event_id,%N) VALUES (%k,%v)</insertelementsql>
          <deletearraysql>DELETE FROM EventExceptionDate WHERE event_id=%k</deletearraysql>

          <!-- exdates will be stored in array field -->
          <maxrepeat>0</maxrepeat> <!-- no limit, we are using dynamic array fields -->
          <repeatinc>1</repeatinc>
          <storeempty>no</storeempty>

          <beforewritescript><![CDATA[
              SETTIMEZONE(EXDATES[ARRAYINDEX()], USERTIMEZONE());
          ]]></beforewritescript>

          <afterreadscript><![CDATA[
             SETTIMEZONE(EXDATES[ARRAYINDEX()], "UTC");             
          ]]></afterreadscript>

          <!-- exdates are the origial start dates (RECURRENCE-ID) of the exception records -->
          <map name="date" references="EXDATES" type="nsdate" mode="rw"/>
        </array>
      </fieldmap>


      <!-- datatypes supported by this datastore -->
      <typesupport>
        <use datatype="vtodoz20" variant="VEVENT" mode="rw" preferred="yes"/>
        <use datatype="vtodoz10" variant="VEVENT" mode="rw" preferred="no"/>
      </typesupport>
    </datastore>

4. Happy Synchronizing!

Next time you start Todo+Cal+Sync you will find the new datastore underneath the settings and can configure the server parameters. Be carefull because not all SyncML server implementations may work properly with multiple calendars synchronizing simultaneously, though.