Turba Configuration suitable for SyncML

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);