X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=servers%2Fslapd%2Fback-sql%2Fdocs%2Fconcept;h=ed2904762f06496a5d1f41e3c1dcc8a1c541f384;hb=3463da548b91cc21d2a26bea404edb48bf0cbc48;hp=b4366dfa14dea74a6d2eded25ea846752bbc5836;hpb=04939b7acab38389062c5699c76133018d43c363;p=openldap diff --git a/servers/slapd/back-sql/docs/concept b/servers/slapd/back-sql/docs/concept index b4366dfa14..ed2904762f 100644 --- a/servers/slapd/back-sql/docs/concept +++ b/servers/slapd/back-sql/docs/concept @@ -1,242 +1 @@ -CONTENT -1. Purpose -2. Metainformation used -3. Typical back-sql operation -4. Several important common techniques - -1. Purpose -Primary purpose of this backend is to PRESENT information stored in some RDBMS -as an LDAP subtree without any programming (some SQL and maybe stored -procedures can't be considered programming, anyway ;). - -That is, for example, when you (some ISP) have account -information you use in RDBMS, and want to use modern solutions that expect such -information in LDAP (to authenticate users, make email lookups etc.). -Or you want to syncronize or distribute information between different -sites/applications that use RDBMSes and/or LDAP. Or whatever else... - -It is NOT designed as general-purpose backend that uses RDBMS instead of -BerkeleyDB (as standard back-ldbm does), though it can be used as such -with several limitations. You can take a look at -http://www.openldap.org/faq/index.cgi?file=378 -(OpenLDAP FAQ-O-Matic/General LDAP FAQ/Directories vs. conventional databases) -to find out more on this point. - -The idea (detailed below) is to use some metainformation to translate -LDAP queries to SQL queries, leaving relational schema untouched, so that -old applications can continue using it without any modifications. This allows -SQL and LDAP applications to interoperate without replication, and exchange -data as needed. - -Back-sql is designed to be tunable to virtually any relational schema without -having to change source (through that metainformation mentioned). -Also, it uses ODBC to connect to RDBMSes, and is highly configurable for -SQL dialects RDBMSes may use, so it may be used for integration and -distribution of data on different RDBMSes, OSes, hosts etc., in other words, -in highly heterogeneous environment. - -2. Metainformation used -*** -Almost everything mentioned later is illustrated in example, which is located -in backsql/rdbms_depend 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 LDAP -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. -Any relational schema, no matter how normalized it is, was designed after -some model of your applications domain (for instance, accounts, services etc. -in ISP), and is used in terms of its entities, not just tables of normalized -schema. -It means that for every attribute of every such instance there is an effective -SQL query that loads it's values. - -Also you might want your objectclasses to conform to some of standard schemas -like inetOrgPerson etc.. - -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 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 WHERE clauses. - -To store such information, we add three more tables to our schema, so that - and fill it with data (see samples): - -ldap_oc_mappings (some columns are not listed for clarity) ---------------- -id=1 -name="person" -keytbl="persons" -keycol="id" - -This table defines mapping between objectclass (its name held in "name" column), -and table that holds primary key for corresponding entities. For instance, -in our example, the person entity, which we are trying to present as "person" -objectclass, resides in two tables (persons and phones), and is identified -by persons.id column (that we will call primary key for this entity). -keytbl and keycol thus contain "persons" (name of the table), and "id" (name -of the column). - -ldap_attr_mappings (some columns are not listed for clarity) ------------ -id=1 -oc_id=1 -name="cn" -sel_expr="CONCAT(persons.first_name,' ',persons.last_name)" -from_tbls="persons" -join_where=NULL -************ -id= -oc_map_id=1 -name="telephoneNumber" -sel_expr="phones.phone" -from_tbls="persons,phones" -join_where="phones.pers_id=persons.id" - -This table defines mappings between LDAP attributes and SQL queries that -load their values. Note that, unlike LDAP schema, these are not *attribute types* -- attribute "cn" for "person" objectclass can well have it's values in different -table than "cn" for other objectclass, so attribute mappings depend on -objectclass mappings (unlike attribute types in LDAP schema, which are -indifferent to objectclasses). Thus, we have oc_map_id column with link to -oc_mappings table. -Now we cut the SQL query that loads values for given attribute into 3 parts. -First goes into sel_expr column - this is the expression we had between -SELECT and FROM keywords, which defines WHAT to load. -Next is table list - text between FROM and WHERE keywords. It may contain -aliases for convenience (see exapmles). -The last is part of where clause, which (if exists at all) express the -condition for joining the table containing values wich table containing -primary key (foreign key equality and such). If values are in the same table -with primary key, then this column is left NULL (as for cn attribute above). - -Having this information in parts, we are able to not only construct queries -that load attribute values by id of entry (for this we could store SQL query -as a whole), but to construct queries that load id's of objects that -correspond to given search filter (or at least part of it). -See below for examples. - -ldap_entries ------------- -id=1 -dn= -oc_map_id=... -parent= -keyval= - -This table defines mappings between DNs of entries in your LDAP tree, -and values of primary keys for corresponding relational data. It has -recursive structure (parent column references id column of the same table), -which allows you to add any tree structure(s) to your flat relational data. -Having id of objectclass mapping, we can determine table and column for -primary key, and keyval stores value of it, thus defining exact tuple -corresponding to LDAP entry with this DN. - -Note that such design (see exact SQL table creation query) implies one -important constraint - the key must be integer. But all that I know about -well-designed schemas makes me think that it s not very narrow ;) -If anyone needs support for different types for keys - he may want to write -a patch, and submit it to OpenLDAP ITS, then I'll include it. - -Also, several people complained that they don't really need very structured -tree, and they don't want to update one more table every time they add or -delete instance in relational schema. Those can use a view instead of real -table, something like this: - - - -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 generated (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() from frontend API 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). - -We just add more columns to oc_m,appings and attr_mappings, holding statements -to execute (like create_proc, add_proc, del_proc etc.), and flags governing -order of parameters passed to those statements. -Please see samples to find out what are the parameters passed, and other -information on this matter - they are self-explanatory for those familiar -with concept expressed above. - -4. Several common techniques -First of all, lets remember that among other major differences to complete -LDAP data model, the concept above does not directly support such things -as multiple objectclasses for entry, and referrals. -Fortunately, they are easy to adopt in this scheme. Back-sql suggests two more -tables being added to schema - -ldap_entry_objectclasses(entry_id, oc_name), and ldap_referrals (entry_id,url). - -First contains any number of objectclass names that corresponding entries -will be found by, in addition to that mentioned in mapping. Back-sql -automatically adds attribute mapping for "objectclass" attribute to each objectclass -mapping, that loads values from this table. So, you may, for instance, have -mapping for inetOrgPerson, and use it for queries for "person" objectclass... - -Second table contains any number of referrals associated with given entry. -Back-sql automatically adds attribute mapping for "ref" attribute to each -objectclass mapping, that loads values from this table. -So, if you add objectclass "referral" to this entry, and make one or more -tuples in ldap_referrals for this entry (they will be seen as values of "ref" -attribute), you will have slapd return referral, as described in Administrators -Guide. +The SQL backend is described in the slapd-sql(5) manual page.