LDAP connections

LDAP connections are different than other connections in a way that an LDAP database stores data in a hierarchical way, in the DIT (Directory Information Tree), whereas other databases accessible using the gda-browser application are relational databases.

As a consequence, LDAP connections are treaded specially: as normal connections with tables (see the table mapping explained next), and through the presence of the LDAP browser perspective

Note that LDAP connections may not be available is either the LDAP database provider is not installed or if the LDAP support was disabled during the compilation.

LDAP table's mapping

Within an LDAP connection, it is possible to declare virtual tables which are mapped to an LDAP search. These virtual tables can then later be used like any other table. The first column of each LDAP virtual table will always be the DN (Distinguished Name) of the entry represented in the row; the other columns depend on the table definition.

An LDAP virtual table is defined by the following attributes:

a table name
the base DN for the search: the LDAP entry at which the search begins (if not specified then the top level entry of the LDAP connection is used)
the search filter: a valid LDAP search filter (if none is provided then the default search filter is "(objectClass=*)", requesting any LDAP entry).
the attributes: the attributes to retrieve, each attribute will be mapped to a column of the table. The attributes must be a comma separated list of attributes, where each attribute can optionally be assigned a type and a multi value option (see below).
the scope: the search scope, "subtree" (to search the base DN and the entire sub tree below), "onelevel" (to search the immediate children of the base DN entry only), or "base" (to search the base DN only)

LDAP table's properties

For example in the figure above, the "users" table will "contain" all the LDAP entries from the top level LDAP entry of the connection, and have 3 columns: the DN, the "cn" and the "jpegPhoto".

Attributes to columns mapping

As mentioned in the previous section, each attribute will be mapped to a column. The column type is normally automatically determined (string, number, ...) but can be forced by appending to the attribute name the "::<type>" for the requested type.

Also, because some attributes can have multiple values, the table construction handles multi-valued attributes in different ways, depending on each attribute's options. An option can be specified by appending the "::<option>" to the attribute name. Valid options are:

"NULL" or "0": a NULL value will be returned for the attribute
"CSV": a comma separated value with all the values of the attribute will be returned. This only works for string attribute types.
"MULT" or "*": a row will be returned for each value of the attribute, effectively multiplying the number of returned rows
"1": only the first value of the attribute will be used, the other values ignored
"CONCAT": the attributes' values are concatenated (with a newline char between each value)
"ERROR": an error value will be returned (this is the default behaviour)

SQL usable with LDAP connections

You can use the SQL understood by SQLite in any LDAP connection. Be aware however that if you define database objects (outside of the extended SQL presented next section), they will be lost the next time the LDAP connection is opened.

So it is perfectly safe for example to create a table to store some LDAP data which may require a long operation to obtain, but after closing the LDAP connection, the table and its data will be lost.

See the SQL understood by LDAP connections and virtual connections section for more information.

SQL extension to handle LDAP tables

LDAP tables can be created using an extended set of SQL commands:

CREATE LDAP TABLE <table_name> [BASE='<base_dn>'] [FILTER='<filter>'] [ATTRIBUTES='<filter>'] [SCOPE='<filter>'] to declare a new LDAP virtual table
DESCRIBE LDAP TABLE <table_name> to show LDAP virtual table's definition
ALTER LDAP TABLE <table_name> [BASE='<base_dn>'] [FILTER='<filter>'] [ATTRIBUTES='<filter>'] [SCOPE='<filter>'] to modify an LDAP virtual table's definition (only the specified part is actually modified)
DROP LDAP TABLE <table_name> to remove an LDAP virtual table. Note that the usual DROP TABLE <table_name> can also be used instead.

For example the following SQL code:

      CREATE LDAP TABLE users FILTER='(objectClass=inetOrgPerson)'
              ATTRIBUTES='cn,sn,givenName,seeAlso::*' SCOPE='subtree';
      SELECT * FROM users WHERE cn like '%john%';
      ALTER LDAP TABLE users FILTER='(&(objectClass=inetOrgPerson)(cn=*john*))';
      SELECT * FROM users;
      DROP LDAP TABLE users;

should display twice the same results, which list all the LDAP entries of the "inetOrgPerson" class with a CommonName (cn) containing the "john" string.