[Date Prev][Date Next]
[Chronological]
[Thread]
[Top]
Back-SQL PostgreSQL support (ITS#961)
Full_Name: Erik Hofman
Version: 2.0.7
OS: Irix
URL: ftp://ftp.openldap.org/incoming/
Submission from: (NULL) (213.171.64.35)
Hi,
I've done some testing with openldap and postgresql.
There need to be a small number of patches (actually only two in the
source) to get it working.
Since i'm mostly busy doing other things right now, i'll send you the
patch so you could do anything you want with it.
Regards,
Erik Hofmandiff -Nuar openldap-2.0.7.orig/configure.in
openldap-2.0.7/configure.in
--- openldap-2.0.7.orig/configure.in Mon Oct 30 18:46:17 2000
+++ openldap-2.0.7/configure.in Thu Dec 21 18:38:36 2000
@@ -1741,6 +1741,11 @@
AC_CHECK_LIB(odbc,SQLDriverConnect,[have_odbc=yes],[have_odbc=no])
if test $have_odbc = yes ; then
ol_link_sql="-lodbc"
+ else
+
AC_CHECK_LIB(odbcpsql,SQLDriverConnect,[have_odbcpsql=yes],[have_odbcpsql=no])
+ if test $have_odbcpsql = yes ; then
+ ol_link_sql="-lodbcpsql"
+ fi
fi
fi
diff -Nuar openldap-2.0.7.orig/servers/slapd/back-sql/rdbms_depend/postgres/backsql_create.sql
openldap-2.0.7/servers/slapd/back-sql/rdbms_depend/postgres/backsql_create.sql
--- openldap-2.0.7.orig/servers/slapd/back-sql/rdbms_depend/postgres/backsql_create.sql
+++ openldap-2.0.7/servers/slapd/back-sql/rdbms_depend/postgres/backsql_create.sql
Wed Dec 20 09:52:36 2000
@@ -0,0 +1,53 @@
+create sequence ldap_oc_mappings_seq;
+create table ldap_oc_mappings
+ (
+ id int4 not null primary key default nextval('ldap_oc_mappings_eq'),
+ name varchar(64) not null,
+ keytbl varchar(64) not null,
+ keycol varchar(64) not null,
+ create_proc varchar(255),
+ delete_proc varchar(255),
+ expect_return int not null
+);
+
+create sequence ldap_attr_mappings_seq;
+create table ldap_attr_mappings
+ (
+ id int4 not null primary key default nextval('ldap_attr_mappings_seq'),
+ oc_map_id int4 not null references ldap_oc_mappings(id),
+ name varchar(255) not null,
+ sel_expr varchar(255) not null,
+ from_tbls varchar(255) not null,
+ join_where varchar(255),
+ add_proc varchar(255),
+ delete_proc varchar(255),
+ param_order int not null,
+ expect_return int not null
+);
+
+create sequence ldap_entries_seq;
+create table ldap_entries
+(
+ id int4 not null primary key default nextval('ldap_entries_seq'),
+ dn varchar(255) not null,
+ oc_map_id int4 not null references ldap_oc_mappings(id),
+ parent int not null,
+ keyval int not null
+);
+create unique index unq1_ldap_entries on ldap_entries (oc_map_id,keyval);
+create unique index unq2_ldap_entries on ldap_entries (dn);
+
+create table ldap_referrals
+ (
+ entry_id integer not null references ldap_entries(id),
+ url text not null
+);
+
+create table ldap_entry_objclasses
+ (
+ entry_id integer not null references ldap_entries(id),
+ oc_name varchar(64)
+ );
+
+
+
diff -Nuar openldap-2.0.7.orig/servers/slapd/back-sql/rdbms_depend/postgres/backsql_drop.sql
openldap-2.0.7/servers/slapd/back-sql/rdbms_depend/postgres/backsql_drop.sql
--- openldap-2.0.7.orig/servers/slapd/back-sql/rdbms_depend/postgres/backsql_drop.sql
+++ openldap-2.0.7/servers/slapd/back-sql/rdbms_depend/postgres/backsql_drop.sql
Tue Dec 19 14:49:25 2000
@@ -0,0 +1,9 @@
+DROP TABLE ldap_referrals;
+
+DROP TABLE ldap_entry_objclasses;
+
+DROP TABLE ldap_attr_mappings;
+
+DROP TABLE ldap_entries;
+
+DROP TABLE ldap_oc_mappings;
diff -Nuar openldap-2.0.7.orig/servers/slapd/back-sql/rdbms_depend/postgres/slapd.conf
openldap-2.0.7/servers/slapd/back-sql/rdbms_depend/postgres/slapd.conf
--- openldap-2.0.7.orig/servers/slapd/back-sql/rdbms_depend/postgres/slapd.conf
+++ openldap-2.0.7/servers/slapd/back-sql/rdbms_depend/postgres/slapd.conf
Thu Dec 28 12:07:22 2000
@@ -0,0 +1,31 @@
+# $OpenLDAP: pkg/ldap/servers/slapd/back-sql/rdbms_depend/mysql/slapd.conf,v
1.2.2.1 2000/07/06 16:45:24 kurt Exp $
+#
+# See slapd.conf(5) for details on configuration options.
+# This file should NOT be world readable.
+#
+include /usr/local/etc/openldap/schema/core.schema
+include /usr/local/etc/openldap/schema/cosine.schema
+include /usr/local/etc/openldap/schema/inetorgperson.schema
+
+# Define global ACLs to disable default read access.
+
+# Do not enable referrals until AFTER you have a working directory
+# service AND an understanding of referrals.
+#referral ldap://root.openldap.org
+
+pidfile /usr/local/var/slapd.pid
+argsfile /usr/local/var/slapd.args
+
+#######################################################################
+# sql database definitions
+#######################################################################
+
+database sql
+suffix "o=sql,c=RU"
+rootdn "cn=root,o=sql,c=RU"
+rootpw secret
+dbname ldap_postgres
+dbuser postgres
+dbpasswd ldap
+subtree_cond "ldap_entries.dn LIKE '%'||?"
+insentry_query "INSERT INTO ldap_entries (dn,oc_map_id,parent,keyval) VALUES
(?,?,?,?)"
diff -Nuar openldap-2.0.7.orig/servers/slapd/back-sql/rdbms_depend/postgres/testdb_create.sql
openldap-2.0.7/servers/slapd/back-sql/rdbms_depend/postgres/testdb_create.sql
--- openldap-2.0.7.orig/servers/slapd/back-sql/rdbms_depend/postgres/testdb_create.sql
+++ openldap-2.0.7/servers/slapd/back-sql/rdbms_depend/postgres/testdb_create.sql
Tue Dec 19 18:37:00 2000
@@ -0,0 +1,27 @@
+CREATE TABLE persons (
+ id int NOT NULL PRIMARY KEY,
+ name varchar(255) NOT NULL
+);
+
+CREATE TABLE institutes (
+ id int NOT NULL PRIMARY KEY,
+ name varchar(255)
+);
+
+CREATE TABLE documents (
+ id int NOT NULL PRIMARY KEY,
+ title varchar(255) NOT NULL,
+ abstract varchar(255)
+);
+
+CREATE TABLE authors_docs (
+ pers_id int NOT NULL,
+ doc_id int NOT NULL
+);
+CREATE UNIQUE INDEX PK_authors_docs ON authors_docs (pers_id,doc_id);
+
+CREATE TABLE phones (
+ id int NOT NULL PRIMARY KEY,
+ phone varchar(255) NOT NULL ,
+ pers_id int NOT NULL
+);
diff -Nuar openldap-2.0.7.orig/servers/slapd/back-sql/rdbms_depend/postgres/testdb_data.sql
openldap-2.0.7/servers/slapd/back-sql/rdbms_depend/postgres/testdb_data.sql
--- openldap-2.0.7.orig/servers/slapd/back-sql/rdbms_depend/postgres/testdb_data.sql
+++ openldap-2.0.7/servers/slapd/back-sql/rdbms_depend/postgres/testdb_data.sql
Tue Dec 19 14:13:47 2000
@@ -0,0 +1,16 @@
+insert into institutes (id,name) values (1,'sql');
+
+insert into persons (id,name) values (1,'Mitya Kovalev');
+insert into persons (id,name) values (2,'Torvlobnor Puzdoy');
+insert into persons (id,name) values (3,'Akakiy Zinberstein');
+
+insert into phones (id,phone,pers_id) values (1,'332-2334',1);
+insert into phones (id,phone,pers_id) values (2,'222-3234',1);
+insert into phones (id,phone,pers_id) values (3,'545-4563',2);
+
+insert into documents (id,abstract,title) values (1,'abstract1','book1');
+insert into documents (id,abstract,title) values (2,'abstract2','book2');
+
+insert into authors_docs (pers_id,doc_id) values (1,1);
+insert into authors_docs (pers_id,doc_id) values (1,2);
+insert into authors_docs (pers_id,doc_id) values (2,1);
diff -Nuar openldap-2.0.7.orig/servers/slapd/back-sql/rdbms_depend/postgres/testdb_drop.sql
openldap-2.0.7/servers/slapd/back-sql/rdbms_depend/postgres/testdb_drop.sql
--- openldap-2.0.7.orig/servers/slapd/back-sql/rdbms_depend/postgres/testdb_drop.sql
+++ openldap-2.0.7/servers/slapd/back-sql/rdbms_depend/postgres/testdb_drop.sql
Tue Dec 19 16:55:01 2000
@@ -0,0 +1,5 @@
+DROP TABLE persons;
+DROP TABLE institutes;
+DROP TABLE documents;
+DROP TABLE authors_docs;
+DROP TABLE phones;
diff -Nuar openldap-2.0.7.orig/servers/slapd/back-sql/rdbms_depend/postgres/testdb_metadata.sql
openldap-2.0.7/servers/slapd/back-sql/rdbms_depend/postgres/testdb_metadata.sql
--- openldap-2.0.7.orig/servers/slapd/back-sql/rdbms_depend/postgres/testdb_metadata.sql
+++ openldap-2.0.7/servers/slapd/back-sql/rdbms_depend/postgres/testdb_metadata.sql
Fri Dec 22 13:13:43 2000
@@ -0,0 +1,224 @@
+-- mappings
+
+insert into ldap_oc_mappings
(id,name,keytbl,keycol,create_proc,delete_proc,expect_return)
+values (1,'person','persons','id','{call create_person(?)}','{call
delete_person(?)}',0);
+
+insert into ldap_oc_mappings
(id,name,keytbl,keycol,create_proc,delete_proc,expect_return)
+values (2,'document','documents','id','{call create_document(?)}','{call
delete_document(?)}',0);
+
+insert into ldap_oc_mappings
(id,name,keytbl,keycol,create_proc,delete_proc,expect_return)
+values (3,'organization','institutes','id','{call create_org(?)}','{call
delete_org(?)}',0);
+
+
+
+
+insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
+values (1,1,'cn','persons.name','persons',NULL,'{call set_person_name(?,?)}',
+ NULL,0,0);
+
+insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
+values (2,1,'telephoneNumber','phones.phone','persons,phones',
+ 'phones.pers_id=persons.id','{call add_phone(?,?)}',
+ '{call delete_phone(?,?)}',0,0);
+
+insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
+values (3,1,'sn','persons.name','persons',NULL,'{call set_person_name(?,?)}',
+ NULL,0,0);
+
+insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
+values (4,2,'abstract','documents.abstract','documents',NULL,'{call
set_doc_abstract(?,?)}',
+ NULL,0,0);
+
+insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
+values (5,2,'documentTitle','documents.title','documents',NULL,'{call
set_doc_title(?,?)}',
+ NULL,0,0);
+
+-- insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
+-- values (6,2,'documentAuthor','persons.name','persons,documents,authors_docs',
+-- 'persons.id=authors_docs.pers_id AND
documents.id=authors_docs.doc_id',
+-- NULL,NULL,0,0);
+
+insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
+values (7,3,'o','institutes.name','institutes',NULL,'{call
set_org_name(?,?)}',
+ NULL,0,0);
+
+insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
+values (8,1,'documentDN','ldap_entries.dn','ldap_entries,documents,authors_docs,persons',
+ 'ldap_entries.keyval=documents.id AND ldap_entries.oc_map_id=2 AND
authors_docs.doc_id=documents.id AND authors_docs.pers_id=persons.id',
+ '{?=call make_doc_link(?,?)}','{?=call del_doc_link(?,?)}',0,3);
+
+insert into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
+values (9,2,'documentAuthor','ldap_entries.dn','ldap_entries,documents,authors_docs,persons',
+ 'ldap_entries.keyval=persons.id AND ldap_entries.oc_map_id=1 AND
authors_docs.doc_id=documents.id AND authors_docs.pers_id=persons.id',
+ '{?=call make_author_link(?,?)}','{?=call del_author_link(?,?)}',0,3);
+
+
+-- entries
+
+insert into ldap_entries (dn,oc_map_id,parent,keyval)
+values ('o=sql,c=RU',3,0,1);
+
+insert into ldap_entries (dn,oc_map_id,parent,keyval)
+values ('cn=Mitya Kovalev,o=sql,c=RU',1,1,1);
+
+insert into ldap_entries (dn,oc_map_id,parent,keyval)
+values ('cn=Torvlobnor Puzdoy,o=sql,c=RU',1,1,2);
+
+insert into ldap_entries (dn,oc_map_id,parent,keyval)
+values ('cn=Akakiy Zinberstein,o=sql,c=RU',1,1,3);
+
+insert into ldap_entries (dn,oc_map_id,parent,keyval)
+values ('documentTitle=book1,o=sql,c=RU',2,1,1);
+
+insert into ldap_entries (dn,oc_map_id,parent,keyval)
+values ('documentTitle=book2,o=sql,c=RU',2,1,2);
+
+-- referrals
+
+insert into ldap_entry_objclasses (entry_id,oc_name)
+values (4,'referral');
+
+insert into ldap_referrals (entry_id,url)
+values (4,'http://localhost');
+
+
+-- procedures
+
+CREATE FUNCTION create_person()
+RETURNS int
+AS 'INSERT INTO persons (name) VALUES ('' '');'
+LANGUAGE 'sql';
+
+CREATE FUNCTION delete_person(int)
+RETURNS int
+AS 'DELETE FROM phones WHERE pers_id=$1;
+ DELETE FROM authors_docs WHERE pers_id=$1;
+ DELETE FROM persons WHERE id=$1;'
+LANGUAGE 'sql';
+
+CREATE FUNCTION create_org()
+RETRUNS int
+AS 'INSERT INTO institutes (name) VALUES ('' '');'
+LANGUAGE 'sql';
+
+CREATE FUNCTION delete_org(int)
+RETURNS int
+AS 'DELETE FROM institutes WHERE id=$1;'
+LANGUAGE 'sql';
+
+CREATE FUNCTION create_document()
+RETURNS int
+AS 'INSERT INTO documents (title) VALUES ('' '');'
+LANGUAGE 'sql';
+
+CREATE FUNCTION delete_document (int)
+RETURNS int
+AS 'DELETE FROM authors_docs WHERE doc_id=$1;
+ DELETE FROM documents WHERE id=$1;'
+LANGUAGE 'sql';
+
+CREATE FUNCTION add_phone(int, varchar)
+RETURNS int
+AS 'INSERT INTO phones ($1,$2) VALUES ($1,$2);'
+LANGUAGE 'sql';
+
+CREATE FUNCTION delete_phone(int, varchar)
+RETURNS int
+AS 'DELETE FROM phones WHERE pers_id=$1 AND phone=$2;'
+LANGUAGE 'sql';
+
+CREATE FUNCTION set_person_name(int, varchar)
+RETURNS int
+AS 'UPDATE persons SET name=$2 WHERE id=$1;'
+LANGUAGE 'sql';
+
+CREATE FUNCTION set_org_name(int, varchar)
+RETURNS int
+AS 'UPDATE institutes SET name=$2 WHERE id=$1;'
+LANGUAGE 'sql';
+
+CREATE FUNCTION set_doc_title (int, varchar)
+RETURNS int
+AS 'UPDATE documents SET title=$2 WHERE id=$1;'
+LANGUAGE 'sql';
+
+CREATE FUNCTION set_doc_abstract (int, varchar)
+RETURNS int
+AS 'UPDATE documents SET abstract=$2 WHERE id=$1;'
+LANGUAGE 'sql';
+
+CREATE FUNCTION make_author_link (int, varchar)
+RETURNS int
+AS 'DECLARE per_id int;
+ BEGIN
+ SELECT $1 INTO per_id
+ FROM ldap_entries
+ WHERE oc_map_id=1 AND dn=$2;
+
+ IF NOT (per_id IS NULL)
+ THEN
+ INSERT INTO authors_docs (doc_id,pers_id)
+ VALUES ($1,per_id);
+ RETURN 1;
+ END IF;
+
+ RETURN 0;
+ END;'
+LANGUAGE 'plpgsql';
+
+CREATE FUNCTION make_doc_link (int, varchar)
+RETURNS int
+AS 'DECLARE docid int;
+ BEGIN
+ SELECT $1 INTO docid
+ FROM ldap_entries
+ WHERE oc_map_id=2 AND dn=$2;
+
+ IF NOT (docid IS NULL)
+ THEN
+ INSERT INTO authors_docs (pers_id,doc_id)
+ VALUES ($1,docid);
+ RETURN 1;
+ END IF;
+
+ RETURN 0;
+ END;'
+LANGUAGE 'plpgsql';
+
+CREATE FUNCTION del_doc_link (int, varchar)
+RETURNS int
+AS 'DECLARE docid int;
+ BEGIN
+ SELECT $1 INTO $2
+ FROM ldap_entries
+ WHERE oc_map_id=2 AND dn=$2;
+
+ IF NOT (docid IS NULL)
+ THEN
+ DELETE FROM authors_docs
+ WHERE pers_id=$1 AND doc_id=docid;
+ RETURN 1;
+ END IF;
+
+ RETURN 0;
+ END;'
+LANGUAGE 'plpgsql';
+
+CREATE FUNCTION del_author_link (int, varchar)
+RETURNS int
+AS 'DECLARE per_id int;
+ BEGIN
+ SELECT $1 INTO per_id
+ FROM ldap_entries
+ WHERE oc_map_id=1 AND dn=$2;
+
+ IF NOT (per_id IS NULL)
+ THEN
+ DELETE FROM authors_docs
+ WHERE doc_id=$1 AND pers_id=per_id;
+ RETURN 1;
+ END IF;
+
+ RETURN 0;
+ END;'
+LANGUAGE 'plpgsql';
diff -Nuar openldap-2.0.7.orig/servers/slapd/back-sql/search.c
openldap-2.0.7/servers/slapd/back-sql/search.c
--- openldap-2.0.7.orig/servers/slapd/back-sql/search.c Sat Nov 4 22:33:33
2000
+++ openldap-2.0.7/servers/slapd/back-sql/search.c Thu Dec 28 11:42:27
2000
@@ -210,7 +210,7 @@
else
{
at=&oc_attr;
- at->sel_expr=backsql_strcat(at->sel_expr,&len,"'",bsi->oc->name,"'",NULL);
+ at->sel_expr=backsql_strcat(at->sel_expr,&len,"cast('",bsi->oc->name,"' as
varchar)",NULL);
}
if (at==NULL)
{
@@ -285,7 +285,7 @@
bsi->sel=backsql_strcat(bsi->sel,&bsi->sel_len,
"SELECT DISTINCT
ldap_entries.id,",bsi->oc->keytbl,".",bsi->oc->keycol,
- ", '",bsi->oc->name,"' AS objectClass",
+ ", cast('",bsi->oc->name,"' as varchar) AS
objectClass",
", ldap_entries.dn AS dn",
NULL);
bsi->from=backsql_strcat(bsi->from,&bsi->from_len," FROM
ldap_entries,",bsi->oc->keytbl,NULL);