From: canfield
Date: April 16 1999 2:45pm
Subject: Re: MySQL and LDAP
List-Archive: http://lists.mysql.com/mysql/1956
Message-Id: <37174D03.FBD1633A@uindy.edu>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

I've been reading through the OpenLDAP-devel archives, and there are
some very interesting threads regarding the use of SQL as a backend.  
Unfortunately, the common theme there seems to be that:

1) It would be really great to use SQL as a repostory that would feed
into the LDAP server.
    and
2) It would be best to use the "slow" SQL-based SLAPD to feed into a
"fast" SLURPD which would be used by real clients.
    but
3) The structure of SQL is not easily adaptable to the needs of LDAP.

As I sit here and really think about it, even if we could figure out a
great way to make some SQL tables work as LDAP backends, that doesn't 
really accomplish what I need (and what I'm assuming others are going
for).  In my case, I have a nice campus-wide directory (white pages) 
system based on MySQL, and I want to serve the entries via LDAP without
a lot of scripting and updating hocus-pocus.   Once the MySQL 
backend for LDAP is written though, I *still* have to find a way to move
my existing data into the LDAP-compatible tables which are not going to 
be anything like my existing tables.

So, perhaps what would be a better use of our time is finding a good
(possibly real-time?) method for moving data between the two independent 
systems.  Here's what I'm envisioning now...

Say I have a two tables in MySQL.  One contains names and another
contains addresses.  They look something like this:

Table "Names",
IDNum 		int(8) unsigned,
Username 	varchar(16),
Last		varchar(20),
First		varchar(15)

Table "Addresses",
IDNum		int(8) unsigned,
Street		varchar(30),
City		varchar (20),
State		char(2),
Zip		varchar(10)

Now, I want to get this data into LDAP entries where the distinguished
name is the username for example (I'm a little bit new to LDAP, so if
that is a ridiculous choice as a DN, please forgive & bear with me). 
So, with our new system, you might write a config file that looks
something like:

table	'Names'
	dn: IDNum
	Username=mail
	Last=surname
	First=givenname
table 	'Addresses'
	dn: IDNum
	City=locality
	State
	Zip


In other words, you list all the tables that contain data you want
converted to LDAP.  Under each, list the distinguished name that you
want to use for each record followed by the fields that you want
imported.  In the fields list you could define a SQL to LDAP field
mapping.  You could get really carried away and allow complex aliases. 
For example, if my dn is the First and Last name of each user, and I
wanted to enter address info for that person from the above database, I
might be able to do:

table	join 'Names','Addresses' on IDNum [or use the real SQL syntax]
	dn: Last First
	Username=mail
	Last=surname
	First=givenname
	City=locality
	State
	Zip


Would something like this meet the needs of the people who want the SQL
backend for LDAP?  If so, I think the biggest obstacle is figuring out
the best way to implement it.  It would be ideal to find some way to
trigger the importing system when certain tables are changed. 
Alternatively, we would probably need some kind of time stamping system
so we don't have to re-import the entire data set every time the process
runs.

Let me know what you guys think.  I may be thinking in a completely
different direction than what people had in mind, and if so, please let
me know what *your* intended goals are. 

Thanks

Dana



Ed Carp wrote:
> 
> We're in the planning stages.  The steps to be taken are:
> 
> 1. Design the database and tables.
> 2. Write the back-end code.
> 
> I'll probably write the back-end code, as it looks to be fairly easy to do -
> I'll adapt some of the code from MyC - but the database has to be designed
> first.  Any ideas on how to lay this beast out?
>
> >
> > I just wondered if anyone has tried to implemented MySQL as a backend
> > for LDAP?  http://www.openldap.org
>