X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=servers%2Fslapd%2Fback-sql%2Fdocs%2Fconcept;h=ed2904762f06496a5d1f41e3c1dcc8a1c541f384;hb=0ea05ad5f28052268be08b324a1e09c907fc88c6;hp=357c0f88e5b617cf9ea45a76c826181110a78062;hpb=d63b3dc2fd267235dc837f4e16514df2f866f95e;p=openldap diff --git a/servers/slapd/back-sql/docs/concept b/servers/slapd/back-sql/docs/concept index 357c0f88e5..ed2904762f 100644 --- a/servers/slapd/back-sql/docs/concept +++ b/servers/slapd/back-sql/docs/concept @@ -1,164 +1 @@ -CONTENT -1. Purpose -2. Metainformation used -3. Typical back-sql operation -4. Perspectives on back-sql as effective storage backend (not MAPPER) - - -1. Purpose -Primary purpose of this backend is to PRESENT information stored in some RDBMS -as an LDAP subtree. It is being designed to be tunable to virtually any -relational schema without having to change source. It is NOT designed as backend -that uses RDBMS to store LDAP data (though you can use it for this purpose, it -will definitely be not the most effective way). -But THIS backend primarily targets the situation when you ALREADY HAVE some -data in one or more RDBMSes of one or more different vendors on one or more -different hosts and operating systems, having one or more different -relational schemas. These could be data used by different software, which you -want to integrate, or just local parts of bigger information project. Using -LDAP standalone server with back-sql as middleware, you can integrate this -heterogeneous information as a single distributed LDAP tree, and even organize -data exchange between nodes, not having to worry about unique id's, different -schemas etc (****see authordn attribute in samples, and dts_ldap utility). -Or, you could simply want to export some information like ISP database to LDAP, -to authenticate users, make email lookups or whatever... - -2. Metainformation used -*** -Almost everything mentioned later is illustrated in example, which is located -in backsql/RDBMS_DEPENDENT directory, and contains scripts for generating sample -database for Oracle,MS SQL Server and mySQL. -*** -First thing that one must arrange for himself is what set of objectclasses -can present your RDBMS information. The easiest way is to create objectclass -for each entity you had in ER-diagram when designing your relational schema. -Or you could choose some other way... -Nevertheless, when you think it out, we must define a way to translate LDAP -operation requests to (series of) SQL queries. Let us deal with SEARCH -operation. - -Example: -Lets suppose that we store information about persons working in our -organization in two tables: - -PERSONS PHONES ----------- ------------- -id integer id integer -first_name varchar pers_id integer references persons(id) -last_name varchar phone -middle_name varchar -... - -(PHONES contains telephone numbers associated with persons). A person can have -several numbers, then PHONES contains several records with corresponding -pers_id, or no numbers (and no records in PHONES with such pers_id). LDAP -objectclass to present such information could look like this: -person -------- -MUST cn -MAY telephoneNumber -MAY firstName -MAY lastName -... - -To fetch all values for cn attribute given person ID, we construct the query: -SELECT CONCAT(persons.first_name,' ',persons.last_name) as cn FROM persons WHERE persons.id=? - -for telephoneNumber we can use: -SELECT phones.phone as telephoneNumber FROM persons,phones WHERE persons.id=phones.pers.id and persons.id=? - -if we wanted to service LDAP request with filter like (telephoneNumber=123*), -we would construct something like: -SELECT ... FROM persons,phones WHERE persons.id=phones.pers.id and persons.id=? and phones.phone like '123%' - -So, if we had information about what tables contain values for given each -attribute, how to join this tables and arrange these values, we could try -to automatically generate such statements, and translate search filters -to SQL clauses - -To store such information, we add three more tables to our schema, so that - and fill it with data (see samples): - -ldap_objclasses ---------------- -id=1 -name="person" -keytbl="persons" -keycol="id" -create_proc="{call create_person(?)}" -delete_proc="{call delete_person(?)}" - -ldap_attrs ------------ -id=1 -oc_id=1 -name="cn" -sel_expr="CONCAT(persons.first_name,' ',persons.last_name)" -from_tbls="persons" -join_where=NULL -add_proc=... -delete_proc=... -************ -id= -oc_id=1 -name="telephoneNumber" -expr="phones.phone" -from_tbls="persons,phones" -join_where="phones.pers_id=persons.id" -add_proc=... -delete_proc=... - - -ldap_entries ------------- -id=1 -dn= -parent= -keyval= - -First two tables contain structured information about constructing queries like -those we made in example. The latter (ldap_entries), contains information about -structure of LDAP tree, referencing actual information by key value. Having -objectclass id, we can determine table and column which contain primary keys, -and load data for the entry attributes using our queries. - -3. Typical back-sql operation -Having metainformation loaded, back-sql uses these tables to determine a set -of primary keys of candidates (depending on search scope and filter). It tries -to do it for each objectclass registered in ldap_objclasses. -Exapmle: -for our query with filter (telephoneNumber=123*) we would get following -query (which loads candidate IDs) -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%') -(for ONELEVEL search) -or "... AND dn=?" (for BASE search) -or "... AND dn LIKE '%?'" (for SUBTREE) - -Then, for each candidate, we load attributes requested using per-attribute queries -like - -SELECT phones.phone AS telephoneNumber FROM persons,phones WHERE persons.id=? AND phones.pers_id=persons.id - -Then, we use test_filter() to test entry for full LDAP search filter match (since -we cannot effectively make sense of SYNTAX of corresponding LDAP schema attribute, -we translate the filter into most relaxed SQL condition to filter candidates), -and send it to user. - -ADD,DELETE,MODIFY operations also performed on per-attribute metainformation -(add_proc etc.). In those fields one can specify an SQL statement or stored procedure -call which can add, or delete given value of given attribute, using given entry -keyval (see examples -- mostly ORACLE and MSSQL - since there're no stored procs in mySQL). - - -4. Perspectives on back-sql as effective storage backend (not MAPPER) -Though as I said, back-sql is intended for presenting existing databases to LDAP, -and as such is not most effective in presenting LDAP data to RDBMS, I have a couple -of ideas on this point, and going to implement this in back-sql using -#ifdefs (one that wants to do RDBMS->LDAP, defines one flag, one that wants -LDAP->RDBMS, defines another). -These tasks have much in common (RDBMS access,connection handling etc), but -latter does not need so much additional metainformation. -For instance, it may have one table for each attribute type in LDAP schema, -and use ldap_entries analog to present tree structure... Later this functionality -will be described more closely... - +The SQL backend is described in the slapd-sql(5) manual page.