#!/usr/bin/python

"""
This quick&dirty script pumps the eGroupware contacts (address book)
to a LDAP-based repository.

Written by michael@stroeder.com, published under GPL v2

Not thoroughly tested! Use it at your own risk!

Tested with:
- Python 2.5
- eGroupware 1.4.002
- mySQL database with UTF-8 charset!
- python-ldap 2.3.1
- python-mysql 1.2.1_p2

Additional remarks:
- Security warning: Access control of eGroupware is completely circumvented!
- AUXILIARY object class 'msPerson' required for setting attributes
  personalTitle, dateOfBirth
  (set them to None if not required and remove 'msPerson' from oc_list)
- eGroupware's contact_id is mapped to attribute 'uid' which is used
  for forming the entry's DN
"""

import pprint,ldap,ldap.modlist,MySQLdb
from types import IntType,LongType,UnicodeType,StringType

# trace_level for python-ldap
pyldap_trace_level = 1

# Search base of addressbook entries
ldap_basedn = 'ou=EGAddressBook,ou=Intranet,dc=example,dc=local'

# the list of object classes to be used for address book entries
# Note that msPerson is proprietary AUXILIARY object class defined by stroeder.com
oc_list = ['person','organizationalPerson','inetOrgPerson','msPerson']

# The order of table columns and their mapping to LDAP attribute types
# (your mileage may vary...)
# If an attribute type is used for more than one DB column multi-valued attrs
# are generated (check attribute type declaration in LDAP subschema yourself!)
all_db_columns = (
   ('contact_id','uid'),
   ('contact_tid',None),
   ('contact_owner',None),
   ('contact_private',None),
   ('cat_id',None),
   ('n_family','sn'),
   ('n_given','givenName'),
   ('n_middle',None),
   ('n_prefix','personalTitle'),
   ('n_suffix',None),
   ('n_fn','cn'),
   ('n_fileas','displayName'),
   ('contact_bday','dateOfBirth'),
   ('org_name','o'),
   ('org_unit','ou'),
   ('contact_title','title'),
   ('contact_role',None),
   ('contact_assistent',None),
   ('contact_room','roomNumber'),
   ('adr_one_street','street'),
   ('adr_one_street2',None),
   ('adr_one_locality','l'),
   ('adr_one_region',None),
   ('adr_one_postalcode','postalCode'),
   ('adr_one_countryname','c'),
   ('contact_label',None),
   ('adr_two_street',None),
   ('adr_two_street2',None),
   ('adr_two_locality',None),
   ('adr_two_region',None),
   ('adr_two_postalcode',None),
   ('adr_two_countryname',None),
   ('tel_work','telephoneNumber'),
   ('tel_cell','mobile'),
   ('tel_fax','facsimileTelephoneNumber'),
   ('tel_assistent',None),
   ('tel_car',None),
   ('tel_pager',None),
   ('tel_home','homePhone'),
   ('tel_fax_home',None),
   ('tel_cell_private',None),
   ('tel_other',None),
   ('tel_prefer',None),
   ('contact_email','mail'),
   ('contact_email_home','mail'),
   ('contact_url','labeledURI'),
   ('contact_url_home',None),
   ('contact_freebusy_uri',None),
   ('contact_calendar_uri',None),
   ('contact_note','description'),
   ('contact_tz',None),
   ('contact_geo',None),
   ('contact_pubkey',None),
   ('contact_created',None),
   ('contact_creator',None),
   ('contact_modified',None),
   ('contact_modifier',None),
   ('contact_jpegphoto','jpegPhoto'),
   ('account_id',None),
)

# Generate a dictionary of the mapping
dbcol2ldapattr = dict(all_db_columns)

# Filter out the DB columns really used
db_columns = [ db_key for db_key,attr_type in all_db_columns if attr_type]
# Filter out the LDAP attributes really used
ldap_attr_types = [ attr_type for db_key,attr_type in all_db_columns if attr_type]
ldap_attr_types.append('objectClass')

class EGAddressBookEntry:
  def __init__(self,row):
    for i in range(0,len(db_columns)):
      setattr(self,db_columns[i],row[i])

  def ldap_entry(self):
    entry = {'objectClass':oc_list}
    for db_key,attr_type in dbcol2ldapattr.items():
      if attr_type:
	attr_value = getattr(self,db_key)
	if attr_value:
	  if type(attr_value)==UnicodeType:
	    attr_value = attr_value.encode('utf-8')
	  elif type(attr_value)==LongType:
	    attr_value = str(attr_value)
	  elif type(attr_value)!=StringType:
	    attr_value = attr_value.tostring()
          try:
  	    entry[attr_type].append(attr_value)
          except KeyError:
  	    entry[attr_type]=[attr_value]
    return entry


##############################################################################
# Main
##############################################################################

##############################################################################
# Connect to mySQL and retrieve whole eGroupware address book

db_conn = MySQLdb.connect(host="localhost",user="root",passwd="",db="egroupware",charset='utf8')
db_conn.query("SELECT %s FROM egroupware.egw_addressbook WHERE account_id IS NULL;" % (','.join(db_columns)))
db_result=db_conn.store_result()
all_rows = db_result.fetch_row(maxrows=0)
del db_result
db_conn.close()

##############################################################################
# Connect to LDAP server and retrieve all person entries to be synchronized

ldap_conn = ldap.initialize('ldap://localhost:9721',trace_level=pyldap_trace_level)
ldap_conn.simple_bind_s('uid=diradm,ou=Intranet,dc=example,dc=local','testsecret')

# Search all existing LDAP addressbook entries
ldap_result = ldap_conn.search_s(
  ldap_basedn,
  ldap.SCOPE_ONELEVEL,
  '(&(objectClass=inetOrgPerson)(uid=*))',
  attrlist=ldap_attr_types
)
# Fill a dict-cache with all uid attribute values found on LDAP server
ldap_uid_dict = {}
for dn,entry in ldap_result:
  ldap_uid_dict[entry['uid'][0]] = entry

##############################################################################
# Add/modify addressbook entries if needed

db_uid_dict = {}

for row in all_rows:
  eg_addressbook_entry = EGAddressBookEntry(row)

  # Maintain a dict-cache of all uid's found in mySQL addressbook DB
  db_uid_dict[str(eg_addressbook_entry.contact_id)] = None

  ldap_new_entry = eg_addressbook_entry.ldap_entry()
  uid = ldap_new_entry['uid'][0]
  dn = 'uid=%s,%s' % (uid,ldap_basedn)

  try:
    if uid in ldap_uid_dict:
      ldap_modlist = ldap.modlist.modifyModlist(ldap_uid_dict[uid],ldap_new_entry)
      if ldap_modlist:
        ldap_conn.modify_s(dn,ldap_modlist)
    else:
      ldap_modlist = ldap.modlist.addModlist(ldap_new_entry)
      ldap_conn.add_s(dn,ldap_modlist)
  except ldap.OBJECT_CLASS_VIOLATION,e:
    print e
    pprint.pprint(ldap_new_entry)

##############################################################################
# Delete obsolete entries (uid not found in mySQL DB) from LDAP

for uid in ldap_uid_dict.keys():
  if not uid in db_uid_dict:
    dn = 'uid=%s,%s' % (uid,ldap_basedn)
    ldap_conn.delete_s(dn)

ldap_conn.unbind_s()