Adding Shared Contacts to RoundCube Email

After using Roundcube for some time I thought it would be neat to expand the Addressbook functionality to include things like actual physical addresses and phone numbers to each contact record. Additionally, since Roundcube is a hosted email solution, I thought it would be nice to be able to share contacts amongst other users on the same system. By adding a flag to each record, users can share their contacts amongst one another. This is nice because it cuts down on duplicate records and allows people to look up contact information with less work.

To modify RoundCube Email to allow for expanded contacts you first need to create new fields in the database. Here are the MySQL commands, but I’m sure you can replicate them through another interface:

 
ALTER TABLE contacts ADD COLUMN phone VARCHAR(20) DEFAULT NULL;
ALTER TABLE contacts ADD COLUMN mobile VARCHAR(20) DEFAULT NULL;
ALTER TABLE contacts ADD COLUMN work VARCHAR(20) DEFAULT NULL;
ALTER TABLE contacts ADD COLUMN fax VARCHAR(20) DEFAULT NULL;
ALTER TABLE contacts ADD COLUMN address1 VARCHAR(50) DEFAULT NULL;
ALTER TABLE contacts ADD COLUMN address2 VARCHAR(50) DEFAULT NULL;
ALTER TABLE contacts ADD COLUMN city VARCHAR(50) DEFAULT NULL;
ALTER TABLE contacts ADD COLUMN state VARCHAR(50) DEFAULT NULL;
ALTER TABLE contacts ADD COLUMN zip VARCHAR(11) DEFAULT NULL;
ALTER TABLE contacts ADD COLUMN is_shared TINYINT(4) DEFAULT 0;

in roundcube/program/localization/en_US/labels.inc after the line:

 
$labesl['email'] = 'E-Mail';

$labels['phone']     = 'Phone';
$labels['mobile']     = 'Mobile';
$labels['fax']         = 'Fax';
$labels['work']         = 'Work';
$labels['address1']     = 'Address';
$labels['address2']     = ' ';
$labels['city']         = 'City';
$labels['state']     = 'State';
$labels['zip']         = 'Zip';
$labels['is_shared'] = 'Shared Contact?'; 

Next:

In the file roundcube/program/steps/addressbook/edit.inc

change the line that reads:

$DB->query("SELECT * FROM ".get_table_name('contacts')."
WHERE  contact_id=?
AND    user_id=?
AND    del<>1″,
$cid,
$_SESSION[’user_id’]);

 
$DB->query("SELECT * FROM ".get_table_name('contacts')."
WHERE  contact_id=?
AND    ( user_id=? OR is_shared = '1')
AND    del<>1″,
$cid,
$_SESSION[’user_id’]);

Change the line that reads:

$a_show_cols = array('name', 'firstname', 'surname', 'email');

to:

$a_show_cols = array('name', 'firstname', 'surname', 'email', 'phone', 'mobile', 'work', 'fax', 'address1', 'address2', 'city', 'state', 'zip', 'is_shared'); 

After the line:

 $title = rcube_label($col); 

if ($col == 'is_shared')
$type = 'checkbox';
else
$type = ''; 

Change the line:

 
$value = rcmail_get_edit_field($col, $CONTACT_RECORD[$col], $attrib);

to:

 
$value = rcmail_get_edit_field($col, $CONTACT_RECORD[$col], $attrib, $type);

In the file roundcube/program/steps/addressbook/save.inc change the line that reads:

 
$a_save_cols = array('name', 'firstname', 'surname', 'email');

to:

 
$a_save_cols = array('name', 'firstname', 'surname', 'email', 'phone', 'mobile', 'work', 'fax', 'address1', 'address2', 'city', 'state', 'zip',
'is_shared');

Change the line:

 
$DB->query("UPDATE $contacts_table
SET    changed=now(), ".join(', ', $a_write_sql)."
WHERE  contact_id=?
AND    user_id=?
AND    del<>1″,
$_POST[’_cid’],
$_SESSION[’user_id’]);

to:

 
$DB->query("UPDATE $contacts_table
SET    changed=now(), ".join(', ', $a_write_sql)."
WHERE  contact_id=?
AND    ( user_id=? OR is_shared = '1')
AND    del<>1″,
$_POST[’_cid’],
$_SESSION[’user_id’]);

change the line:

 
$sql_result = $DB->query("SELECT * FROM $contacts_table
WHERE  contact_id=?
AND    user_id=?
AND    del<>1″,
$_POST[’_cid’],
$_SESSION[’user_id’]);

to:

 
$sql_result = $DB->query("SELECT * FROM $contacts_table
WHERE  contact_id=?
AND    ( user_id=? OR is_shared = '1')
AND    del<>1″,
$_POST[’_cid’],
$_SESSION[’user_id’]);

change the line:

 
$sql = "SELECT 1 FROM $contacts_table
WHERE  user_id = {$_SESSION['user_id']}
AND del <> ‘1′ “;

to:

$sql = "SELECT 1 FROM $contacts_table
WHERE  (user_id = {$_SESSION['user_id']} OR is_shared = 1)
AND del <> ‘1′ “;

change the line:

 
$sql_result = $DB->query("SELECT * FROM $contacts_table
WHERE  contact_id=?
AND user_id=?",
$insert_id,
$_SESSION['user_id']);

to:

 
$sql_result = $DB->query("SELECT * FROM $contacts_table
WHERE  contact_id=?
AND    ( user_id=? OR is_shared = '1')",
$insert_id,
$_SESSION['user_id']);

change the line:

 $sql_result = $DB->query("SELECT * FROM $contacts_table
WHERE  contact_id = $id
AND user_id = {$_SESSION['user_id']}");

to:

 
$sql_result = $DB->query("SELECT * FROM $contacts_table
WHERE  contact_id = $id
AND    (user_id    = {$_SESSION['user_id']} or is_shared = 1)");

In the file roundcube/program/steps/addressbook/show.inc

change the line:

 
$DB->query("SELECT * FROM ".get_table_name('contacts')."
WHERE  contact_id=?
AND    user_id=?
AND    del<>1″,
$cid,
$_SESSION[’user_id’]);

to:

 
$DB->query("SELECT * FROM ".get_table_name('contacts')."
WHERE  contact_id=?
AND    ( user_id=? OR is_shared = '1')
AND    del<>1″,
$cid,
$_SESSION[’user_id’]);

change the line:

 
$a_show_cols = array('name', 'firstname', 'surname', 'email');

to:

 
$a_show_cols = array('name', 'firstname', 'surname', 'email', 'phone', 'mobile', 'work', 'fax', 'address1', 'address2', 'city', 'state', 'zip', 'is_shared');

change the line that reads:

 
else
	$value = rep_specialchars_output($CONTACT_RECORD[$col]);

to:

 
elseif ($col=='is_shared')
	$value = ($CONTACT_RECORD[$col] == '1') ? 'Yes' : 'No';
else
	$value = rep_specialchars_output($CONTACT_RECORD[$col]);

In the file roundcube/program/steps/addressbook/list.inc change the line:

 
$sql_result = $DB->query("SELECT COUNT(contact_id) AS rows
FROM ".get_table_name('contacts')."
WHERE  del<>1
AND    user_id=?”,
$_SESSION[’user_id’]);

to:

 
$sql_result = $DB->query("SELECT COUNT(contact_id) AS rows
FROM ".get_table_name('contacts')."
WHERE  del<>1
AND    (user_id=? OR is_shared = 1)”,
$_SESSION[’user_id’]);

 $sql_result = $DB->limitquery("SELECT * FROM ".get_table_name('contacts')."
WHERE  del<>1
AND    user_id=?
ORDER BY name”,
$start_row,
$CONFIG[’pagesize’],
$_SESSION[’user_id’]);

 $sql_result = $DB->limitquery("SELECT * FROM ".get_table_name('contacts')."
WHERE  del<>1
AND    (user_id=? OR is_shared = 1)
ORDER BY name”,
$start_row,
$CONFIG[’pagesize’],
$_SESSION[’user_id’]);

In the file roundcube/program/steps/addressbook/func.inc

change the line:

 $sql_result = $DB->query("SELECT COUNT(contact_id) AS rows
FROM ".get_table_name('contacts')."
WHERE  del<>1
AND    user_id=?”,
$_SESSION[’user_id’]);

 $sql_result = $DB->query("SELECT COUNT(contact_id) AS rows
FROM ".get_table_name('contacts')."
WHERE  del<>1
AND    ( user_id=? OR is_shared = ‘1′)”,
$_SESSION[’user_id’]);

 $sql_result = $DB->limitquery("SELECT * FROM ".get_table_name('contacts')."
WHERE  del<>1
AND    user_id=?
ORDER BY name”,
$start_row,
$CONFIG[’pagesize’],
$_SESSION[’user_id’]);

 $sql_result = $DB->limitquery("SELECT * FROM ".get_table_name('contacts')."
WHERE  del<>1
AND    ( user_id=? OR is_shared = ‘1′)
ORDER BY name”,
$start_row,
$CONFIG[’pagesize’],
$_SESSION[’user_id’]);

 $sql_result = $DB->query("SELECT 1 FROM ".get_table_name('contacts')."
WHERE  del<>1
AND    user_id=?”,
$_SESSION[’user_id’]);

 $sql_result = $DB->query("SELECT 1 FROM ".get_table_name('contacts')."
WHERE  del<>1
AND    ( user_id=? OR is_shared = ‘1′)”,
$_SESSION[’user_id’]);

In the file roundcube/program/steps/addressbook/addcontact.inc

change the line:

 $sql_result = $DB->query("SELECT 1 FROM ".get_table_name('contacts')."
WHERE  user_id=?
AND    email=?
AND    del<>1″,
$_SESSION[’user_id’],$contact[’mailto’]);

 $sql_result = $DB->query("SELECT 1 FROM ".get_table_name('contacts')."
WHERE  ( user_id=? OR is_shared = '1')
AND    email=?
AND    del<>1″,
$_SESSION[’user_id’],$contact[’mailto’]);

In the file roundcube/program/steps/mail/compose.inc

change the line:

 $sql_result = $DB->query("SELECT name, email
FROM ".get_table_name('contacts')."
WHERE user_id=?
AND    del<>1
AND    contact_id IN (”.$_GET[’_to’].”)”,
$_SESSION[’user_id’]);

 $sql_result = $DB->query("SELECT name, email
FROM ".get_table_name('contacts')."
WHERE ( user_id=? OR is_shared = '1')
AND    del<>1
AND    contact_id IN (”.$_GET[’_to’].”)”,
$_SESSION[’user_id’]);

 $sql_result = $DB->query("SELECT name, email
FROM ".get_table_name('contacts')." WHERE  user_id=?
AND  del<>1″,$_SESSION[’user_id’]);

 $sql_result = $DB->query("SELECT name, email
FROM ".get_table_name('contacts')." WHERE  ( user_id=? OR is_shared = '1')
AND  del<>1″,$_SESSION[’user_id’]);

And you’re done :) I’m sure you see that most of this is simply to include the shared contacts feature.