The sql script below provides a sql database schema and source.php snippet to make SyncML work better with Turba address books. Run the sql script to create the table. You have to rename/delete/backup your original turba_objects table first.
You also have to use the sources.php below or paste it into your existing sources.php (in the turba/conf directory)
Here's the sql script:
CREATE TABLE `turba_objects` ( `object_id` varchar(32) NOT NULL, `owner_id` varchar(255) NOT NULL, `object_type` varchar(255) NOT NULL default 'Object', `object_uid` varchar(255), `object_members` blob, `object_lastname` varchar(255) NOT NULL default '', `object_firstname` varchar(255), `object_alias` varchar(32), `object_nameprefix` varchar(255), `object_email` varchar(255), `object_homestreet` varchar(255), `object_homecity` varchar(255), `object_homeprovince` varchar(255), `object_homepostalcode` varchar(255), `object_homecountry` varchar(255), `object_workstreet` varchar(255), `object_workcity` varchar(255), `object_workprovince` varchar(255), `object_workpostalcode` varchar(255), `object_workcountry` varchar(255), `object_homephone` varchar(25), `object_workphone` varchar(25), `object_cellphone` varchar(25), `object_fax` varchar(25), `object_pager` varchar(25), `object_title` varchar(255), `object_company` varchar(255), `object_notes` text, `object_url` varchar(255), `object_pgppublickey` text, `object_smimepublickey` text, `object_freebusyurl` varchar(255), `object_role` varchar(255), `object_category` varchar(80), `object_photo` blob, `object_blobtype` varchar(10), `object_bday` varchar(10), PRIMARY KEY(object_id) ); CREATE INDEX turba_owner_idx ON turba_objects (owner_id); GRANT SELECT, INSERT, UPDATE, DELETE ON turba_objects TO horde@localhost;
And here's the php code for turba/conf/sources.php. Replaces the existing $cfgSources['localsql'].
$cfgSources['localsql'] = array( 'title' => _("My Addressbook"), 'type' => 'sql', // The default connection details are pulled from the Horde-wide SQL // connection configuration. // // The old example illustrates how to use an alternate database // configuration. // // New Example: 'params' => array_merge($GLOBALS['conf']['sql'], array('table' => 'turba_objects')), 'map' => array( '__key' => 'object_id', '__owner' => 'owner_id', '__type' => 'object_type', '__members' => 'object_members', '__uid' => 'object_uid', 'name' => array('fields' => array('firstname', 'lastname'), 'format' => '%s %s'), 'firstname' => 'object_firstname', 'lastname' => 'object_lastname', 'name_prefix' => 'object_nameprefix', 'email' => 'object_email', 'alias' => 'object_alias', 'homeStreet' => 'object_homestreet', 'homeCity' => 'object_homecity', 'homeProvince' => 'object_homeprovince', 'homePostalCode' => 'object_homepostalcode', 'homeCountry' => 'object_homecountry', 'workStreet' => 'object_workstreet', 'workCity' => 'object_workcity', 'workProvince' => 'object_workprovince', 'workPostalCode' => 'object_workpostalcode', 'workCountry' => 'object_workcountry', 'homePhone' => 'object_homephone', 'workPhone' => 'object_workphone', 'cellPhone' => 'object_cellphone', 'fax' => 'object_fax', 'pager' => 'object_pager', 'title' => 'object_title', 'company' => 'object_company', 'birthday' => 'object_bday', 'website' => 'object_url', 'notes' => 'object_notes', 'pgpPublicKey' => 'object_pgppublickey', 'smimePublicKey' => 'object_smimepublickey', 'freebusyUrl' => 'object_freebusyurl' ), 'search' => array( 'name', 'email' ), 'strict' => array( 'object_id', 'owner_id', 'object_type', ), 'export' => true, 'browse' => true, 'use_shares' => true, );
Here is a first migration script, nothing spectacular nor perfect but better than nothing :
ALTER TABLE turba_objects add object_firstname VARCHAR(255); ALTER TABLE turba_objects add object_lastname VARCHAR(255); ALTER TABLE turba_objects add object_nameprefix VARCHAR(255); ALTER TABLE turba_objects add object_homestreet VARCHAR(255); ALTER TABLE turba_objects add object_homecity VARCHAR(255); ALTER TABLE turba_objects add object_homeprovince VARCHAR(255); ALTER TABLE turba_objects add object_homepostalcode VARCHAR(255); ALTER TABLE turba_objects add object_homecountry VARCHAR(255); ALTER TABLE turba_objects add object_workstreet VARCHAR(255); ALTER TABLE turba_objects add object_workcity VARCHAR(255); ALTER TABLE turba_objects add object_workprovince VARCHAR(255); ALTER TABLE turba_objects add object_workpostalcode VARCHAR(255); ALTER TABLE turba_objects add object_workcountry VARCHAR(255); ALTER TABLE turba_objects add object_pager VARCHAR(25); ALTER TABLE turba_objects add object_role varchar(255); ALTER TABLE turba_objects add object_category varchar(80); ALTER TABLE turba_objects add object_photo blob; ALTER TABLE turba_objects add object_blobtype varchar(10); ALTER TABLE turba_objects add object_bday varchar(10); ALTER TABLE turba_objects add object_url varchar(255); //Replace name of the "full" field (definitively not perfect) ALTER TABLE turba_objects CHANGE object_homeaddress object_homestreet VARCHAR(255); ALTER TABLE turba_objects CHANGE object_workaddress object_workstreet VARCHAR(255); ALTER TABLE turba_objects CHANGE object_name object_lastname VARCHAR(255);