Author Topic: Comments in addressbook  (Read 2367 times)

adaniels

  • Guest
Comments in addressbook
« on: November 02, 2007, 12:05:25 PM »
We're currently making the move of switching from squirrelmail to roundcube. The first reactions have been positive. However there were some remarks. The lack of (searchable) comments for contacts, was the most pressing.

I've written a patch to add a comments field and allow to configure (system level only) which fields are searchable. It only contains labels for en_US, en_GB and nl_NL. I would like to see this patch implemented in future versions.

Offline Paul

  • Jr. Member
  • **
  • Posts: 12
Re: Comments in addressbook
« Reply #1 on: November 05, 2007, 05:29:37 AM »
Thanks. We're also switching from Squirrelmail, and two of our users have complained about this.
I wonder how you added the comments from Squirrelmail to the addressbook records that were already imported? Right now I have this query:

alter table `address` add index (email), add index (owner), add index (nickname);
alter table `users` add index (username);
update contacts, address, users set contacts.comments = address.label where contacts.user_id = users.user_id and users.username = address.owner and contacts.email = address.email and contacts.name = address.nickname and char_length(label) > 0;
alter table `users` drop index `username`;

adaniels

  • Guest
Re: Comments in addressbook
« Reply #2 on: November 08, 2007, 01:08:10 PM »
Hi Paul,

The squirrelmail setting weren't stored in a database, but on file in the 'data' directory. I fetched all the settings with a small script, directing the output to a file.

Code: [Select]
#!/bin/bash
for FILE in *.abook ; do
    EMAIL=`basename "$FILE" .abook`
    cat "$FILE" | awk '{print "'$EMAIL'|"$0}'
done

Than I imported that file as CVS (with '|' as separator) in a new table and ran 2 queries, to import the users and the contacts.
Code: [Select]
CREATE TABLE `contacts_import` (
 `user_email` varchar(255) NOT NULL,
 `name` varchar(255) NOT NULL,
 `firstname` varchar(255) default NULL,
 `lastname` varchar(255) default NULL,
 `email` varchar(255) NOT NULL,
 `remarks` text,
 KEY `user_email` (`user_email`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO users SELECT NULL, user_email, "10.0.0.100", NULL, NOW(), 0, 'en', NULL FROM contacts_import LEFT JOIN users ON contacts_import.user_email = users.username WHERE users.user_id IS NULL
INSERT INTO contacts SELECT NULL, NOW(), 0, contacts_import.name, contacts_import.email, contacts_import.firstname, contacts_import.lastname, NULL, users.user_id, remarks FROM `contacts_import` INNER JOIN users ON contacts_import.user_email = users.username LEFT JOIN contacts ON users.user_id = contacts.user_id AND contacts_import.email = contacts.email WHERE contacts.contact_id IS NULL

I hope this helps, even if you have the data in a DB instead of in files.

Best regards,
Arnold