3 2. Metainformation used
4 3. Typical back-sql operation
5 4. Perspectives on back-sql as effective storage backend (not MAPPER)
9 Primary purpose of this backend is to PRESENT information stored in some RDBMS
10 as an LDAP subtree. It is being designed to be tunable to virtually any
11 relational schema without having to change source. It is NOT designed as backend
12 that uses RDBMS to store LDAP data (though you can use it for this purpose, it
13 will definitely be not the most effective way).
14 But THIS backend primarily targets the situation when you ALREADY HAVE some
15 data in one or more RDBMSes of one or more different vendors on one or more
16 different hosts and operating systems, having one or more different
17 relational schemas. These could be data used by different software, which you
18 want to integrate, or just local parts of bigger information project. Using
19 LDAP standalone server with back-sql as middleware, you can integrate this
20 heterogeneous information as a single distributed LDAP tree, and even organize
21 data exchange between nodes, not having to worry about unique id's, different
22 schemas etc (****see authordn attribute in samples, and dts_ldap utility).
23 Or, you could simply want to export some information like ISP database to LDAP,
24 to authenticate users, make email lookups or whatever...
26 2. Metainformation used
28 Almost everything mentioned later is illustrated in example, which is located
29 in backsql/RDBMS_DEPENDENT directory, and contains scripts for generating sample
30 database for Oracle,MS SQL Server and mySQL.
32 First thing that one must arrange for himself is what set of objectclasses
33 can present your RDBMS information. The easiest way is to create objectclass
34 for each entity you had in ER-diagram when designing your relational schema.
35 Or you could choose some other way...
36 Nevertheless, when you think it out, we must define a way to translate LDAP
37 operation requests to (series of) SQL queries. Let us deal with SEARCH
41 Lets suppose that we store information about persons working in our
42 organization in two tables:
45 ---------- -------------
47 first_name varchar pers_id integer references persons(id)
48 last_name varchar phone
52 (PHONES contains telephone numbers associated with persons). A person can have
53 several numbers, then PHONES contains several records with corresponding
54 pers_id, or no numbers (and no records in PHONES with such pers_id). LDAP
55 objectclass to present such information could look like this:
64 To fetch all values for cn attribute given person ID, we construct the query:
65 SELECT CONCAT(persons.first_name,' ',persons.last_name) as cn FROM persons WHERE persons.id=?
67 for telephoneNumber we can use:
68 SELECT phones.phone as telephoneNumber FROM persons,phones WHERE persons.id=phones.pers.id and persons.id=?
70 if we wanted to service LDAP request with filter like (telephoneNumber=123*),
71 we would construct something like:
72 SELECT ... FROM persons,phones WHERE persons.id=phones.pers.id and persons.id=? and phones.phone like '123%'
74 So, if we had information about what tables contain values for given each
75 attribute, how to join this tables and arrange these values, we could try
76 to automatically generate such statements, and translate search filters
79 To store such information, we add three more tables to our schema, so that
80 and fill it with data (see samples):
88 create_proc="{call create_person(?)}"
89 delete_proc="{call delete_person(?)}"
96 sel_expr="CONCAT(persons.first_name,' ',persons.last_name)"
104 name="telephoneNumber"
106 from_tbls="persons,phones"
107 join_where="phones.pers_id=persons.id"
116 parent=<parent record id>
117 keyval=<value of primary key>
119 First two tables contain structured information about constructing queries like
120 those we made in example. The latter (ldap_entries), contains information about
121 structure of LDAP tree, referencing actual information by key value. Having
122 objectclass id, we can determine table and column which contain primary keys,
123 and load data for the entry attributes using our queries.
125 3. Typical back-sql operation
126 Having metainformation loaded, back-sql uses these tables to determine a set
127 of primary keys of candidates (depending on search scope and filter). It tries
128 to do it for each objectclass registered in ldap_objclasses.
130 for our query with filter (telephoneNumber=123*) we would get following
131 query (which loads candidate IDs)
132 SELECT ldap_entries.id,persons.id, 'person' AS objectClass, ldap_entries.dn AS dn FROM ldap_entries,persons,phones WHERE persons.id=ldap_entries.keyval AND ldap_entries.objclass=? AND ldap_entries.parent=? AND phones.pers_id=persons.id AND (phones.phone LIKE '123%')
133 (for ONELEVEL search)
134 or "... AND dn=?" (for BASE search)
135 or "... AND dn LIKE '%?'" (for SUBTREE)
137 Then, for each candidate, we load attributes requested using per-attribute queries
140 SELECT phones.phone AS telephoneNumber FROM persons,phones WHERE persons.id=? AND phones.pers_id=persons.id
142 Then, we use test_filter() to test entry for full LDAP search filter match (since
143 we cannot effectively make sense of SYNTAX of corresponding LDAP schema attribute,
144 we translate the filter into most relaxed SQL condition to filter candidates),
147 ADD,DELETE,MODIFY operations also performed on per-attribute metainformation
148 (add_proc etc.). In those fields one can specify an SQL statement or stored procedure
149 call which can add, or delete given value of given attribute, using given entry
150 keyval (see examples -- mostly ORACLE and MSSQL - since there're no stored procs in mySQL).
153 4. Perspectives on back-sql as effective storage backend (not MAPPER)
154 Though as I said, back-sql is intended for presenting existing databases to LDAP,
155 and as such is not most effective in presenting LDAP data to RDBMS, I have a couple
156 of ideas on this point, and going to implement this in back-sql using
157 #ifdefs (one that wants to do RDBMS->LDAP, defines one flag, one that wants
158 LDAP->RDBMS, defines another).
159 These tasks have much in common (RDBMS access,connection handling etc), but
160 latter does not need so much additional metainformation.
161 For instance, it may have one table for each attribute type in LDAP schema,
162 and use ldap_entries analog to present tree structure... Later this functionality
163 will be described more closely...