SugarCRM to Mozilla LDAP contact slurper

Seeing as SugarCRM is now truly Open Source, I decided to support them buy using SugarCRM as a contact database for a client.

This script extracts contact data from Sugar, and imports it into an LDAP tree, so that Thunderbird clients can use it as an address book.

It’s written for Sugar 5.0, and a suitable LDAP installation with the Mozilla Schema

A suitable VIEW for printing out a contact directory would look like:

51&q=CREATE&lr=lang_en">CREATE 5.1/en/non-typed-operators.html">OR 51&q=REPLACE&lr=lang_en">REPLACE 51&q=VIEW&lr=lang_en">VIEW sugarab 51&q=AS&lr=lang_en">AS

51&q=SELECT&lr=lang_en">SELECT c.id, c.description, salutation, first_name, last_name, title, department,

do_not_call, phone_home, phone_mobile, phone_work, phone_other, c.phone_fax,

primary_address_street, primary_address_city, primary_address_state,

primary_address_postalcode, primary_address_country, alt_address_street,

alt_address_city, alt_address_state, alt_address_postalcode, alt_address_country,

assistant, assistant_phone, lead_source, birthdate,

a.name 51&q=AS&lr=lang_en">AS account_name,

e1.email_address 51&q=AS&lr=lang_en">AS primary_email, e2.email_address 51&q=AS&lr=lang_en">AS secondary_email

51&q=FROM&lr=lang_en">FROM contacts 51&q=AS&lr=lang_en">AS c

5.1/en/string-functions.html">LEFT 51&q=OUTER&lr=lang_en">OUTER 51&q=JOIN&lr=lang_en">JOIN accounts_contacts 51&q=AS&lr=lang_en">AS j 51&q=ON&lr=lang_en">ON (c.id = j.contact_id 5.1/en/non-typed-operators.html">AND j.deleted = 0)

5.1/en/string-functions.html">LEFT 51&q=OUTER&lr=lang_en">OUTER 51&q=JOIN&lr=lang_en">JOIN accounts 51&q=AS&lr=lang_en">AS a 51&q=ON&lr=lang_en">ON (j.account_id = a.id 5.1/en/non-typed-operators.html">AND a.deleted = 0)

5.1/en/string-functions.html">LEFT 51&q=OUTER&lr=lang_en">OUTER 51&q=JOIN&lr=lang_en">JOIN email_addr_bean_rel 51&q=AS&lr=lang_en">as eb 51&q=ON&lr=lang_en">ON (eb.bean_id = c.id 5.1/en/non-typed-operators.html">AND eb.deleted = 0)

5.1/en/string-functions.html">LEFT 51&q=OUTER&lr=lang_en">OUTER 51&q=JOIN&lr=lang_en">JOIN email_addresses 51&q=AS&lr=lang_en">as e1 51&q=ON&lr=lang_en">ON (eb.email_address_id = e1.id 5.1/en/non-typed-operators.html">AND eb.primary_address = 1 5.1/en/non-typed-operators.html">AND e1.deleted = 0)

5.1/en/string-functions.html">LEFT 51&q=OUTER&lr=lang_en">OUTER 51&q=JOIN&lr=lang_en">JOIN email_addresses 51&q=AS&lr=lang_en">as e2 51&q=ON&lr=lang_en">ON (eb.email_address_id = e2.id 5.1/en/non-typed-operators.html">AND e1.id != e2.id 5.1/en/non-typed-operators.html">AND e2.deleted = 0)

51&q=WHERE&lr=lang_en">WHERE c.deleted = 0;

Attachment	Size
sugarslurp.py.txt	6.25 KB

Comments

Wed, 30/01/2008 - 9:53am — tumbleweed

Erk, there is already a plugin

Jonathan Carter has pointed out to me that there already is a Thunderbird Plugin for Sugar. Why the stupid idiots haven’t registered it on add-ons.mozilla.com, I don’t know… I’ll have to try it out :-)

Sat, 16/08/2008 - 4:42pm — Anonymous (not verified)

Fix for utf-8

# Connect to MySQL
db=MySQLdb.connect(user=MYSQL_USER, passwd=MYSQL_PASS, db=MYSQL_DB)
c = db.cursor()

# set utf-8 code
c.execute("""SET NAMES utf8;""")
c.execute("""SET CHARACTER SET utf8;""")

# Retrieve contacts

Tue, 15/09/2009 - 1:32pm — Anonymous (not verified)

Problem with this script

I filled in ldap and mysql data, added mozilla.scheme to openldap, but when i run this script i get this error:

File "sugarslurp.py", line 174, in
l.add_s(dn, ldap.modlist.addModlist(entry))
File "/usr/lib/python2.5/site-packages/ldap/ldapobject.py", line 185, in add_s
return self.result(msgid,all=1,timeout=self.timeout)
File "/usr/lib/python2.5/site-packages/ldap/ldapobject.py", line 427, in result
res_type,res_data,res_msgid = self.result2(msgid,all,timeout)
File "/usr/lib/python2.5/site-packages/ldap/ldapobject.py", line 431, in result2
res_type, res_data, res_msgid, srv_ctrls = self.result3(msgid,all,timeout)
File "/usr/lib/python2.5/site-packages/ldap/ldapobject.py", line 437, in result3
ldap_result = self._ldap_call(self._l.result3,msgid,all,timeout)
File "/usr/lib/python2.5/site-packages/ldap/ldapobject.py", line 96, in _ldap_call
result = func(*args,**kwargs)
ldap.INVALID_SYNTAX: {'info': 'c: value #0 invalid per syntax', 'desc': 'Invalid syntax'}

Any clues?
Thank you

Wed, 16/09/2009 - 9:06am — tumbleweed

Re: INVALID SYNTAX

Looks like it’s setting something as ‘c’ (Country) which it shouldn’t be.

Print out entry before storing it, and try to work out what’s going on.

Wed, 16/09/2009 - 6:18pm — Anonymous (not verified)

I tweeked the script (removed

I tweeked the script (removed Mozilla scheme) and now i've managet to import data from Sugar to LDAP(phonebook).
Great stuff....thanks fro the code.

Mon, 21/09/2009 - 2:42pm — Aaron (not verified)

thanks for the code Stefano

awesome job!

This little snippet of python had us syncing SugarCRM contacts to eDirectory in a few hours. Really appreciate you giving this away for us to take advantage of.
I've tweaked your script somewhat to comply with eDirectory LDAP schema. Anyone too lazy to customise their own can contact us at http://loyfam.com.au/index.php?page=contact-us for a copy of our version of this great bit of code.

thanks again

Aaron.

Fri, 28/05/2010 - 11:31am — Régie Leroy (not verified)

Bigger version of such task

We've made something closely related to such task.
We have published on sugarForge some python scripts using triggers from MySQL to detect sync needed between Sugar and an LDAP branch.
Theses scripts handle much more things (custom tables, fields and custom fields mappings, surgar users sync, etc.)
People reading this page might be interedted in such scripts.
Feedback welcome!
http://www.sugarforge.org/projects/ldapcontactsync/

Navigation