0007608: Add support for iCAL4OL client

[Gloirin]

Reported by shochdoerfer on 29 Dec 2012 09:13

Version: Joey (2012.10.2)

Since Outlook 2010 does not support CalDav and CardDav sync I found iCAL4OL to provide a good way of syncing the data from our Outlook users with Tine 2.0. While for CalDav iCAL4OL works out of the box we had to make some changes for CardDAV to work fine.

1. Since iCAL4OL does not work with the conversions done in the Addressbook_Convert_Contact_VCard_Generic class it was needed to use the Addressbook_Convert_Contact_VCard_IOS implementation for the sync process. Thus we modified the Addressbook_Convert_Contact_VCard_Factory::parseUserAgent() and tried to detect the iCAL4OL client which sends a hint in the userAgent. We just added an else block like this: elseif(false !== strpos($_userAgent, 'iCal4OL/')) { $backend = Addressbook_Convert_Contact_VCard_Factory::CLIENT_IOS; $version = '1.0';
   }

2. Unfortunately this was not enough. The UIDs generated by Outlook seem to be 48 chars and not 40 chars as used by Tine 2.0. Since our first approach of dumping the database and replacing varchar(40) with varchar(48) resulted in some wired errors we had to do some trial and error to find the "right" fields to change which are as follows:

   • In table tine20_addressbook_list_members change the field contact_id to varchar(48)
   • In table tine20_addressbook change the field id to varchar(48)
   • In table tine20_addressbook_image change the field contact_id to varchar(48)
   • In table tine20_container_content change the field record_id to varchar(48)
   • In table tine20_container_content change the field record_id to varchar(48)
   • In table tine20_notes change the field record_id to varchar(48) Although it might be better to change all uids in the Tine 2.0 database to the same length, guess you guys know what to change to achieve that goal.

How to configure iCAL4OL to be able to sync the data with Tine 2.0 can be read in the forum: http://ical.gutentag.ch/forum/viewtopic.php?id=459

Additional information: http://www.tine20.org/forum/viewtopic.php?f=12&t=12216

[Gloirin]

Comment posted by pschuele on 23 Jan 2013 12:02

hi Stephan,

do you know why iCAL4OL needs 48 char IDs?

[Gloirin]

Comment posted by shochdoerfer on 23 Jan 2013 12:18

It`s not iCAL4OL fault but a requirement from Outlook as it seems. In our setup (Outlook 2010) 48 char ids were generated for appointments and got synched to Tine 2.0 via iCal4OL. In the forum of iCal4OL the author wrote that "GlobalAppointmentID can have up to 232 characters" thus 48 chars might be too short. However up to now we did not encounter that. I even wonder what happens when Outlook 2013 comes with native ActiveSync support that might be a general problem then.

If you need some more help to investigate in the problem feel free to contact me. Having native support for iCAL4OL sync is pretty important for us and if I can get rid of patching the Tine 2.0 sources I am happy to contribute our changes back to the community.

[Gloirin]

Comment posted by pschuele on 23 Jan 2013 12:34

hm, now you are talking about the appointments (=events) where we already have an uid field of 255 chars.

but you suggested to change the contact ids to 48 chars. do the contacts have 48 char ids in outlook? maybe this isn't required? what errors appeared before?

[Gloirin]

Comment posted by shochdoerfer on 24 Jan 2013 07:26

I am sorry I mixed things up. Since we use iCal4OL to sync both contacts and appointments I was slightly confused in my last comment.

Recap:

1. Syncing appointments works fine for us. No change needed as far as I remember.

2. Syncing contacts was a problem due to the "long" id`s generated by Outlook to identify each contact. Without modification we got SQL errors stating that each of the fields I mentioned above was too short for the data. So I had to change te length of the first mentioned field, run the sync again, fixed the second field length and so on. So the changes outlined are least minium changes that needs to be done to make the sync work. I am not sure (since I am not aware of the Tine 2.0 datamodel) if the changes are "correct" or if I missed to change another field. If I recall correctly I had some problems afterwards adding users to Tine 2.0 thus a few more fields needed to be changed. As I pointed out all the fields mentioned have been changed and I did not run into any issues so far. And to be able to store accounts correctly I had to make another change in the code in file Tinebase/Backend/Sql/Abstract.php - the _hasHashId() method needs to be implemented like this: protected function _hasHashId() { $identifier = $this->_getRecordIdentifier(); $result = (in_array($this->_schema[$identifier]['DATA_TYPE'], array('varchar', 'VARCHAR2')) && $this->_schema[$identifier]['LENGTH'] == 40) || (in_array($this->_schema[$identifier]['DATA_TYPE'], array('varchar', 'VARCHAR2')) && $this->_schema[$identifier]['LENGTH'] == 48);

   return $result; }

This was needed since some "primary fields" are now longer than the expected 40 chars Tine 2.0 default setting which threw an error when I tried to add a new user. The fix above takes care of that. That also explains why my first approach of changing all varchar(40) fields to varchar(48) failed with so many errors in Tine 2.0

An to answer your question "Do the contacts have 48 char ids in outlook?": In our case yes they do. Before I started changing the database fields I dumped the sync`ed ids on the server side that iCal4OL sent to Tine 2.0 and figured out that all of them were 48 chars. I am not sure if this is Outlook 2010 specific as we only use that version.

Hope this helps to clarify the situation. In case you need more info come back to me.

[Gloirin]

Comment posted by shochdoerfer on 24 Jan 2013 07:28

Maybe you want to talk to the author of iCAL4OL as it seems he knows quite much about all the different problems with Outlook. He might help you to get things done if that helps to sell his tool I guess ;)

[Gloirin]

Comment posted by pschuele on 24 Jan 2013 09:13

ok, thanks for the clarification :)

i'll pass this to Lars as he knows much more about the outlook sync.

[Gloirin]

Comment posted by shochdoerfer on 10 Feb 2013 09:00

Just one more thing ;) If you guys integrate support for iCal4OL could you please check why the attendee status is not updated in Tine 2.0. This is the only thing that currently does not work with iCal4OL. Not sure if the attendee status is not passed to Tine 2.0 or if it is passed in a wrong way.