This module is Copyright (C) 2000-2009 by
Jim Turner
Email: turnerjw784 .att. yahoo.com
All rights reserved Without Prejudice.
You may distribute this module under the same terms as Perl itself.
Convert::ANS1 (required by Net::LDAP) Net::LDAP DBI - an LDAP database to connect to.
use DBI; $dbh = DBI->connect("DBI:LDAP:ldapdb",'user','password') #USER LOGIN. or die "Cannot connect as user: " . $DBI::errstr;
$dbh = DBI->connect("DBI:LDAP:ldapdb") #ANONYMOUS LOGIN (Read-only). or die "Cannot connect as guest (readonly): " . $DBI::errstr;
$sth = $dbh->prepare("select * from people where (cn like 'Smith%')") or die "Cannot prepare: " . $dbh->errstr(); $sth->execute() or die "Cannot execute: " . $sth->errstr(); while ((@results) = $sth->fetchrow_array) { print "--------------------------------------------------------\n"; ++$cnt; while (@results) { print "------>".join('|',split(/\0/, shift(@results)))."\n"; } } $sth->finish(); $dbh->disconnect();
\s-1DBD::LDAP\s0 is a \s-1DBI\s0 extension module adding an \s-1SQL\s0 database interface to standard \s-1LDAP\s0 databases to Perl's database-independent database interface. You will need access to an existing \s-1LDAP\s0 database or set up your own using an \s-1LDAP\s0 server, ie. \*(L"OpenLDAP\*(R", see (http://www.openldap.org).
The main advantage of \s-1DBD::LDAP\s0 is the ability to query \s-1LDAP\s0 databases via standard \s-1SQL\s0 queries in leu of cryptic \s-1LDAP\s0 \*(L"filters\*(R". \s-1LDAP\s0 is optimized for quick lookup of existing data, but \s-1DBD::LDAP\s0 does support entry inserts, updates, and deletes with commit/rollback via the standard \s-1SQL\s0 commands!
\s-1LDAP\s0 databases are \*(L"heirarchical\*(R" in structure, whereas other DBD-supported databases are \*(L"relational\*(R" and there is no LDAP-equivalent to \s-1SQL\s0 \*(L"tables\*(R", so \s-1DBD::LDAP\s0 maps a \*(L"table\*(R" interface over the \s-1LDAP\s0 \*(L"tree\*(R" via a configuration file you must set up. Each \*(L"table\*(R" is mapped to a common \*(L"base \s-1DN\s0\*(R". For example, consider a typical \s-1LDAP\s0 database of employees within different departments within a company. You might have a \*(L"company\*(R" names \*(L"Acme\*(R" and the root \*(L"dn\*(R" of \*(L"dc=Acme, dc=com\*(R" (Acme.com). Below the company level, are divisions, ie. \*(L"Widgets\*(R", and \*(L"Blivets\*(R". Each division would have an entry with a \*(L"dn\*(R" of \*(L"ou=Widgets, dc=Acme, dc=com\*(R". Employees within each division could have a \*(L"dn\*(R" of \*(L"cn=John Doe, ou=Widgets, dc=Acme, dc=com\*(R". With \s-1DBD::LDAP\s0, we could create tables to access these different levels, ie. \*(L"top\*(R", which would have a \*(L"\s-1DN\s0\*(R" of \*(L"dc=Acme, dc=com\*(R", \*(L"WidgetDivision\*(R" for \*(L"dc=Acme, dc=com\*(R". Tables can also be constained by additional attribute specifications (filters), ie constraining by \*(L"objectclass\*(R", ie. \*(L"(objectclass=person)\*(R". Then, doing a \*(L"select * from WidgetDivision\*(R" would display all \*(L"person\*(R"s with a \*(L"dn\*(R" containing "\*(L"ou=Widgets, dc=Acme, dc=com\*(R".
Installing this module (and the prerequisites from above) is quite simple. You just fetch the archive, extract it with
gzip -cd DBD-LDAP-####.tar.gz | tar xf -
-or-
tar -xzvf DBD-LDAP-####.tar.gz
(this is for Unix users, Windows users would prefer WinZip or something similar) and then enter the following:
cd DBD-LDAP-#.### perl Makefile.PL make make test
If any tests fail, let me know. Otherwise go on with
make install
Note that you almost definitely need root or administrator permissions. If you don't have them, read the ExtUtils::MakeMaker man page for details on installing in your own directories.
1) Create a "database", ie. "foo" by creating a text file "foo.ldb". The general format of this file is:
---------------------------------------------------------- hostname[;port][:[root-dn][:[loginrule]]] tablename1:[basedn]:[basefilter]:dnattrs:[visableattrs]:[insertattrs]: \\ [ldap_options] tablename2:[basedn]:[basefilter]:dnattrs:[visableattrs]:[insertattrs]: \\ [ldap_options] ... ----------------------------------------------------------
<hostname> represents the ldap server host name. <port> represents the server's port, default is 389. <root-dn> if specified, is appended to the end of each tablename's base-dn. <loginrule> if specified, converts single word "usernames" to the appropriate DN, ie:
"cn=*,<ROOT>" would convert user name "foo" to "cn=foo, " and append the "<root-dn>" onto that. The asterisk is converted to the user-name specified in the "connect" method. If not specified, the username specified in the "connect" method must be a full DN. If the "<root-dn>" is not specified, then the "<loginrule>" would need to be a full DN.
tablename - represents the name to be used in SQL statements for a given set of entries which make up a virtual "table". basedn - if specified, is appended to the "<root-dn>" to make up the common base DN for all entries in this table. If "<root-dn>" is not specified, then a full DN must be specified; otherwise, the default is the root-dn. basefilter - if specified, specifies a filter to be used if no "where"- clause is specified in SQL queries. If a "where"-clause is specified, the resulting filter is "and"-ed with this one. The default is "(objectclass=*)". dnattrs - specifies which attributes that values for which are to be appended to the left of the basedn to create DNs for new entries being inserted into the table. visableattrs - if specified, one or more attributes separated by commas which will be sought when the SQL statement does not specify attributes, ie. "select * from tablename". If not specified, the attributes of the first matching entry are returned and used for all entries matching a given query. insertattrs - if specified, one or more attribute/value combinations to be added to any new entry inserted into the table, usually needed for objectclass values. The attributes and values usually correspond to those specivied in the "<basefilter>". The general format is: attr1=value1[|value2...],attr2=value1...,... These attributes and values will be joined with any user-specified values for these attributes. ldap_options - if specified, can be any one or more of the following:
ldap_sizelimit - Limit the number of entries fetch by a query to this number (0 = no limit) - default: 0. ldap_timelimit - Limit the search to this number of seconds per query. (0 = no limit) - default: 0. ldap_scope - specify the "scope" of the search. Values are: "base", "one", and "sub", see Net::LDAP docs. Default is "one", meaning the set of records one level below the basedn. "base" means search only the basedn, and "sub" means the union of entries at the "base" level and "one" level below. ldap_inseparator - specify the separator character/string to be used in queries to separate multiple values being specified for a given attribute. Default is "|". ldap_outseparator - specify the separator character/string to be used in queryies to separate multiple values displayed as a result of a query. Default is "|". ldap_firstonly - only display the 1st value fetched for each attribute per entry. This makes "ldap_outseparator" unnecessary.
2) write your script to use DBI, ie:
#!/usr/bin/perl use DBI;
$dbh = DBI->connect('DBD:LDAP:mydb','me','mypassword') || die "Could not connect (".$DBI->err.':'.$DBI->errstr.")!"; ... #CREATE A TABLE, INSERT SOME RECORDS, HAVE SOME FUN!
3) get your application working.
1st, we'll create a database called "ldapdb" with the tables previously mentioned in the example in the DESCRIPTION section:
----------------- file \*(L"ldapdb.ldb\*(R" ---------------- ldapserver:dc=Acme, dc=com:cn=*,<\s-1ROOT\s0> top:::dc WidgetDivision:ou=Widgets, :&(objectclass=top)(objectclass=person): \\ cn:cn,sn,ou,title,telephonenumber,description,objectclass,dn: \\ objectclass=top|person|organizationalPerson:ldap_outseparator => \*(L":\*(R" ----------------------------------------------------
The following examples insert some data in a table and fetch it back: First all data in the string:
$dbh->do(<<END_SQL); INSERT INTO top (ou, cn, objectclass) VALUES ('Widgets', 'WidgetDivision', 'top|organizationalUnit') END_SQL
Next an example using parameters:
$dbh->do("INSERT INTO WidgetDivision (cn,sn,title,telephonenumber) VALUES (?, ?, ?, ?)", 'John Doe','DoeJ','Manager','123-1111'); END_SQL $dbh->commit();
NOTE: Unlike most other DBD modules which support transactions, changes made do NOT show up until the "commit" function is called, unless "AutoCommit" is set. This is due to the fact that fetches are done from the LDAP server and changes do not take effect there until the Net::LDAP "update" function is called, which is called by "commit".
NOTE: The "dn" field is generated automatically from the base "dn" and the dn component fields specified by "dnattrs", If you try to insert a value directly into it, it will be ignored. Also, if not specified, any attribute/value combinations specified in the "insertattrs" option will be added automatically.
To retrieve data, you can use the following:
my($query) = "SELECT * FROM WidgetDivision WHERE cn like 'John%' ORDER BY cn"; my($sth) = $dbh->prepare($query); $sth->execute(); while (my $entry = $sth->fetchrow_hashref) { print("Found result record: cn = ", $entry->{'cn'}, ", phone = ", $row->{'telephonenumber'}); } $sth->finish();
The SQL "SELECT" statement above (combined with the table information in the "ldapdb.ldb" database file would generate and execute the following equivalent LDAP Search:
base => 'ou=Widgets, dc=Acme, dc=com', filter => '(&(&(objectclass=top)(objectclass=person))(cn=John*))', scope => 'one', attrs => 'cn,sn,ou,title,telephonenumber,description,objectclass,dn'
See the DBI(3) manpage for details on these methods. See the
Data rows are modified with the UPDATE statement:
$dbh->do("UPDATE WidgetDivision SET description = 'Outstanding Employee' WHERE cn = 'John Doe'");
NOTE: You can NOT change the "dn" field directly - direct changes will be ignored. You change the "rdn" component of the "dn" field by changing the value of the other field(s) which are appended to the base "dn". Also, if not specified, any attribute/value combinations specified in the "insertattrs" option will be added automatically.
Likewise you use the DELETE statement for removing entries:
$dbh->do("DELETE FROM WidgetDivision WHERE description = 'Outstanding Employee'");
The following attributes are handled by DBI itself and not by DBD::LDAP, thus they should all work as expected.
PrintError RaiseError Warn
The following DBI attributes are handled by DBD::LDAP:
AutoCommit Works
NUM_OF_FIELDS Valid after '$sth->execute'
NUM_OF_PARAMS Valid after '$sth->prepare'
NAME Valid after '$sth->execute'; undef for Non-Select statements.
NULLABLE Not really working. Always returns an array ref of one's, as DBD::LDAP always allows NULL (handled as an empty string). Valid after `$sth->execute'.
LongReadLen Should work
LongTruncOk Should work
These attributes and methods are not supported:
bind_param_inout CursorName
In addition to the DBI attributes, you can use the following dbh attributes. These attributes are read-only after "connect".
ldap_dbuser Current database user.
ldap_HOME Environment variable specifying a path to search for LDAP databases (*.sdb) files.
DBI->data_sources() The `data_sources' method returns a list of "databases" (.ldb files) found in the current directory and, if specified, the path in the ldap_HOME environment variable.
$dbh->tables() This method returns a list of table names specified in the current database. Example:
my($dbh) = DBI->connect("DBI:LDAP:mydatabase",'me','mypswd'); my(@list) = $dbh->func('tables');
DBD::LDAP currently treats all data as strings and all fields as VARCHAR(255).
Currently, you must define tables manually in the "<database>.ldb" file using your favorite text editor. I hope to add support for the SQL "Create Table", "Alter Table", and "Drop Table" functions to handle this eventually.
"Create Table", "Alter Table", and "Drop Table" SQL functions for creating, altering, and deleting the tables defined in the "<database>.ldb" file.
Some kind of datatype support, ie. numeric (for sorting), CHAR for padding, Long/Blob - for >255 chars per field, etc.
none - (yet).
Net::LDAP(3), DBI(3), perl(1)