ldap
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;
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;
Mozilla LDIF fixer
Mozilla Address Book exports pretty terrible LDIF. To add these LDIF files to an LDAP directory, you must first run it through this utility. It will turn the LDIF into schematically correct LDIF. If will (hopefully) not maul data that is already in the correct format.
