| OpenLDAP-POSTGRESQL HOWTO | ||
|---|---|---|
| Prev | ||
This is what we are running here in production state and all the things I've done to have a full working SQL backend. I hope it is complex enougth to handle most cases.
We have 20 running OpenLDAP servers with one master that received all change to the directory and replicate them to other servers. That we needed is to extract list of data very quickly to be used in our Intranet applications like email adresses book. So the best solution was to have an SQL database with all needed informations and of course without writing replication tools. A SQL backend to OpenLDAP that can be replicated from master like others LDAP servers is the solution.
This directory has been build with six majors objectives:
- Apache / Intranet authentication - Linux Pam-LDAP authentication - Sendmail / Cyrus Imap mailbox - Mail address book - Employees directory - Companies / agencies directory |
The resulting LDAP structure is build as follow:
--------------- dc=samse, dc=fr ------------- / | | | \ / | | | \ ou=company ou=region ou=agency ou=people ou=group DN: uid=... uid=... uid=... employeeNumber= cn=... |
Ldiff dump of the LDAP structure and some dn:
# extended LDIF # # LDAPv3 # base <dc=samse,dc=fr> with scope sub # filter: objectClass=* # requesting: objectClass # # samse.fr dn: dc=samse,dc=fr objectClass: top objectClass: dcObject objectClass: organization # people, samse.fr dn: ou=people,dc=samse,dc=fr objectClass: organizationalUnit # group, samse.fr dn: ou=group,dc=samse,dc=fr objectClass: organizationalUnit # agency, samse.fr dn: ou=agency,dc=samse,dc=fr objectClass: organizationalUnit # region, samse.fr dn: ou=region,dc=samse,dc=fr objectClass: organizationalUnit # company, samse.fr dn: ou=company,dc=samse,dc=fr objectClass: organizationalUnit # 123456789, company, samse.fr dn: uid=123456789,ou=company,dc=samse,dc=fr objectClass: samseOu # R7, region, samse.fr dn: uid=R7,ou=region,dc=samse,dc=fr objectClass: samseOu # 123, agency, samse.fr dn: uid=123,ou=agency,dc=samse,dc=fr objectClass: samseOu # communication, group, samse.fr dn: cn=users,ou=group,dc=samse,dc=fr objectClass: top objectClass: posixGroup # 001234, people, samse.fr dn: employeeNumber=001234,ou=people,dc=samse,dc=fr objectClass: top objectClass: person objectClass: organizationalPerson objectClass: inetOrgPerson objectClass: samsePerson objectClass: posixAccount objectClass: shadowAccount |
We also have specials attributes that are not handle by any OpenLDAP standard schema. So we have made two personnal objectClass allowing the use of these attributes. As you see above these objectClass are: 'samseOu' and 'samsePerson'. They are build as follow:
# # SAMSE directory schema items # # Date d'arrivee dans le groupe de la personne attributetype ( 1.3.6.1.4.1.3814.4.1 NAME ( 'arrivalDate' ) SUP name) # Date de depart de la personne attributetype ( 1.3.6.1.4.1.3814.4.2 NAME ( 'leavingDate' ) SUP name) # Groupe cotisant de la personne attributetype ( 1.3.6.1.4.1.3814.4.3 NAME ( 'contributionGroup' ) SUP name) # Sexe de la personne attributetype ( 1.3.6.1.4.1.3814.4.4 NAME ( 'sexe' ) SUP name) # Date de naissance de la personne attributetype ( 1.3.6.1.4.1.3814.4.5 NAME ( 'birthDate' ) SUP name) # Raccourci telephone fixe attributetype ( 1.3.6.1.4.1.3814.4.6 NAME ( 'fixRPVNumber' ) SUP name) # Raccourci portable attributetype ( 1.3.6.1.4.1.3814.4.7 NAME ( 'mobileRPVNumber' ) SUP name) # Forme jurique attributetype ( 1.3.6.1.4.1.3814.4.8 NAME ( 'legalForm' ) SUP name) # Capital attributetype ( 1.3.6.1.4.1.3814.4.9 NAME ( 'capital' ) SUP name) # Convention Collective attributetype ( 1.3.6.1.4.1.3814.4.10 NAME ( 'collectiveConvention' ) SUP name) # Responsable attributetype ( 1.3.6.1.4.1.3814.4.11 NAME ( 'responsable' ) SUP name) # Qualite du Responsable attributetype ( 1.3.6.1.4.1.3814.4.12 NAME ( 'quality' ) SUP name) # Assistant(e) attributetype ( 1.3.6.1.4.1.3814.4.13 NAME ( 'assistant' ) SUP name) # Region d'une agence attributetype ( 1.3.6.1.4.1.3814.4.14 NAME ( 'region' ) SUP name) # Nom de la mailbox d'un user attributetype ( 1.3.6.1.4.1.3814.4.15 NAME ( 'mailboxName' ) SUP name) # Abrege agence attributetype ( 1.3.6.1.4.1.3814.4.16 NAME ( 'rpvNumber' ) SUP name) # Numero de societe attributetype ( 1.3.6.1.4.1.3814.4.17 NAME ( 'companyNumber' ) SUP name) # Enseigne de l'agence attributetype ( 1.3.6.1.4.1.3814.4.18 NAME ( 'agencyShield' ) SUP name) # Domaine Internet de l'agence attributetype ( 1.3.6.1.4.1.3814.4.19 NAME ( 'mailDomain' ) SUP name) # Domaine d'activite de l'employe attributetype ( 1.3.6.1.4.1.3814.4.20 NAME ( 'activite' ) SUP name) # Fonction de l'employe attributetype ( 1.3.6.1.4.1.3814.4.21 NAME ( 'fonction' ) SUP name) # Code LIDIS de l'agence de l'employe attributetype ( 1.3.6.1.4.1.3814.4.22 NAME ( 'codeAgence' ) SUP name) # Code APE d'une agence attributetype ( 1.3.6.1.4.1.3814.4.23 NAME ( 'codeAPE' ) SUP name) # Site physique d'une agence attributetype ( 1.3.6.1.4.1.3814.4.24 NAME ( 'realSite' ) SUP name) # Code emploi d'un employe BAO attributetype ( 1.3.6.1.4.1.3814.4.25 NAME ( 'codeEmploi' ) SUP name) # Code Lidis d'une agence attributetype ( 1.3.6.1.4.1.3814.4.26 NAME ( 'codeLidis' ) SUP name) # Service auquel est associe une personne attributetype ( 1.3.6.1.4.1.3814.4.27 NAME ( 'service' ) SUP name) # Type de contrat RTT attributetype ( 1.3.6.1.4.1.3814.4.28 NAME ( 'typeRTT' ) SUP name) objectClass ( 1.3.6.1.4.1.3814.3.4 NAME 'samseOu' DESC 'SAMSE Organizational Unit Object' SUP organizationalUnit MUST ( uid ) MAY ( legalForm $ capital $ collectiveConvention $ quality $ responsable $ region $ rpvNumber $ companyNumber $ agencyShield $ mailDomain $ codeAPE $ realSite $ codeLidis) ) objectClass ( 1.3.6.1.4.1.3814.3.5 NAME 'samsePerson' DESC 'SAMSE Person' SUP ( inetOrgPerson ) MUST ( uid $ cn ) MAY ( arrivalDate $ leavingDate $ contributionGroup $ sexe $ birthDate $ fixRPVNumber $ mobileRPVNumber $ assistant $ mailboxName $ activite $ fonction $ codeAgence $ codeEmploi $ service $ typeRTT) ) |
Note that OIDs used here are not registered and that there's no constraint defined for these attributes to simplify things.
Here is the slapd.conf file we use:
# # 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 include /usr/local/etc/openldap/schema/nis.schema # Our own schema include /usr/local/etc/openldap/schema/samse.schema # Define global ACLs to disable default read access. # Define global ACLs to disable default read access. access to * by self write by * read access to * by dn="cn=root,dc=samse,dc=fr" write allow bind_v2 # 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 "dc=samse,dc=fr" rootdn "cn=root,dc=samse,dc=fr" rootpw secret dbname PgSQL dbuser test dbpasswd test insentry_stmt "insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values ((select max(id)+1 from ldap_entries),?,?,?,?)" upper_func "upper" strcast_func "text" concat_pattern "?||?" has_ldapinfo_dn_ru no schemacheck on lastmod off |
The last two lines are uncommented to allow direct loading of initial datas. After that they are removed to allow replication from the master.
The lastmod option is set to off to disable last modification timestamp recording.
The creation of the PostgreSQL backend is done as above in the howto. The only change is that we have now our own database schema.
First of all we have to erase any previous test with the following commands and recreate the OpenLDAP base backend with the rdbms_depend/backsql_create.sql file. Si as postgres user run the following commands:
dropdb pg_ldap createdb pg_ldap psql pg_ldap < rdbms_depend/backsql_create.sql psql -d pg_ldap -c "GRANT ALL ON ldap_attr_mappings,ldap_entries,ldap_entry_objclasses,ldap_oc_mappings TO test;" psql -d pg_ldap -c "GRANT ALL ON ldap_attr_mappings_id_seq,ldap_entries_id_seq,ldap_oc_mappings_id_seq TO test;" |
BEGIN TRANSACTION; CREATE TABLE dc_object ( id serial NOT NULL PRIMARY KEY, dc varchar(80) NOT NULL UNIQUE, description varchar(255) ); CREATE TABLE organizational_unit ( id serial NOT NULL PRIMARY KEY, ou varchar(40) NOT NULL UNIQUE, description varchar(255) ); CREATE TABLE posix_group ( id serial NOT NULL PRIMARY KEY, cn varchar(255) NOT NULL UNIQUE, gidnumber int NOT NULL UNIQUE ); CREATE TABLE samse_ou ( id serial NOT NULL PRIMARY KEY, ou varchar(255) NOT NULL, uid varchar(40) NOT NULL UNIQUE, legalform varchar(255), capital varchar(255), collectiveconvention varchar(255), description varchar(255), responsable varchar(1024), quality varchar(255), internationalisdnnumber varchar(25), businesscategory varchar(80), telephonenumber varchar(25), facsimiletelephonenumber varchar(25), street varchar(255), postofficebox varchar(10), postalcode varchar(6), l varchar(255), region varchar(80), rpvnumber varchar(15), companynumber int, agencyshield varchar(80), maildomain varchar(80), codeape varchar(15), realsite varchar(40), codelidis int ); CREATE TABLE group_member ( group_id int NOT NULL references posix_group(id), memberuid varchar(6), UNIQUE(group_id,memberuid) ); CREATE TABLE samse_person ( id serial NOT NULL PRIMARY KEY, ou varchar(255) NOT NULL, employeenumber varchar(6) NOT NULL UNIQUE, uid varchar(10) NOT NULL, cn varchar(128), sn varchar(80), givenname varchar(40), displayname varchar(128), mail varchar(255), mailboxname varchar(80), title varchar(255), employeetype char(1), departmentnumber int, arrivaldate varchar(10), birthdate varchar(10), leavingdate varchar(10), contributiongroup char(2), sexe char(1), userpassword varchar(80), loginshell varchar(80), homedirectory varchar(80), uidnumber int, gidnumber int, shadowlastchange int, shadowmin int, shadowmax int, shadowwarning int, shadowexpire int, telephonenumber varchar(25), fixrpvnumber varchar(10), mobile varchar(25), mobilerpvnumber varchar(10), assistant varchar(255), codeemploi varchar(10), codeagence int, fonction varchar(1), service varchar(255), typeRTT varchar(10) ); CREATE TABLE samse_activite ( person_id int NOT NULL references samse_person(id), activite varchar(10) ); END TRANSACTION; |
Copy this transaction into a file named testcreate.sql and execute the following command as postgres user:
psql pg_ldap < testcreate.sql |
As you can see we have two attributes that can handle multiple value. This is 'memberuid' part of the posixAccount objectClass and 'activite' part of the samsePerson objectClass. So we need to create two separate table to received these value (n to n). These tables are 'group_member' and 'samse_activite'.
This is the longest part of the process with the SQL function definition. I give them all so that you can copy and past what you want to match your own needs. Don't forget to thanks me a lot :-))
BEGIN TRANSACTION;
------------------------------------------------
-- objectClass mappings
------------------------------------------------
-- The root directory dcObject
insert into ldap_oc_mappings (id,name,keytbl,keycol,create_proc,delete_proc,expect_return) values (1,'dcObject','dc_object','id',NULL,NULL,0);
-- The organizationalUnit objectClass
insert into ldap_oc_mappings (id,name,keytbl,keycol,create_proc,delete_proc,expect_return) values (2,'organizationalUnit','organizational_unit','id',NULL,NULL,0);
-- The samseOu objectClass
insert into ldap_oc_mappings (id,name,keytbl,keycol,create_proc,delete_proc,expect_return) values (3,'samseOu','samse_ou','id','select create_samse_ou()','select delete_samse_ou(?)',0);
-- The posixGroup objectClass
insert into ldap_oc_mappings (id,name,keytbl,keycol,create_proc,delete_proc,expect_return) values (4,'posixGroup','posix_group','id','select create_posix_group()','select delete_posix_group(?)',0);
-- The samsePerson objectClass
insert into ldap_oc_mappings (id,name,keytbl,keycol,create_proc,delete_proc,expect_return)
values (5,'samsePerson','samse_person','id','SELECT create_samse_person()','SELECT delete_samse_person(?)',0);
SELECT setval ('ldap_oc_mappings_id_seq', 5, false);
SELECT setval ('samse_ou_id_seq', 1, false);
SELECT setval ('posix_group_id_seq', 1, false);
------------------------------------------------
-- attributes mappings
------------------------------------------------
-- dcObject's attributes
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,'dc','dc_object.dc','dc_object',NULL,NULL,NULL,3,0);
-- organizationalUnit's attributes
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,2,'ou','organizational_unit.ou','organizational_unit',NULL,NULL,NULL,3,0);
-- samseOu's attributes
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,3,'uid','samse_ou.uid','samse_ou',NULL,'{ call set_samse_ou_uid(?,?) }',NULL,3,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,3,'ou','samse_ou.ou','samse_ou',NULL,'{ call set_samse_ou_ou(?,?) }',NULL,3,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,3,'capital','samse_ou.capital','samse_ou',NULL,'{ call set_samse_ou_capital(?,?) }','{ call del_samse_ou_capital(?,?) }',3,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,3,'legalForm','samse_ou.legalform','samse_ou',NULL,'{ call set_samse_ou_legalform(?,?) }','{ call del_samse_ou_legalform(?,?) }',3,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,'collectiveConvention','samse_ou.collectiveconvention','samse_ou',NULL,'{ call set_samse_ou_collectiveconv(?,?) }','{ call del_samse_ou_collectiveconv(?,?) }',3,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,3,'description','samse_ou.description','samse_ou',NULL,'{ call set_samse_ou_description(?,?) }','{ call del_samse_ou_description(?,?) }',3,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 (9,3,'responsable','samse_ou.responsable','samse_ou',NULL,'{ call set_samse_ou_responsable(?,?) }','{ call del_samse_ou_responsable(?,?) }',3,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 (10,3,'quality','samse_ou.quality','samse_ou',NULL,'{ call set_samse_ou_quality(?,?) }','{ call del_samse_ou_quality(?,?) }',3,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 (11,3,'internationaliSDNNumber','samse_ou.internationalisdnnumber','samse_ou',NULL,'{ call set_samse_ou_internationalisdn(?,?) }','{ call del_samse_ou_internationalisdn(?,?) }',3,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 (12,3,'businessCategory','samse_ou.businesscategory','samse_ou',NULL,'{ call set_samse_ou_businesscategory(?,?) }','{ call del_samse_ou_businesscategory(?,?) }',3,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 (13,3,'telephoneNumber','samse_ou.telephonenumber','samse_ou',NULL,'{ call set_samse_ou_telephonenumber(?,?) }','{ call del_samse_ou_telephonenumber(?,?) }',3,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 (14,3,'facsimileTelephoneNumber','samse_ou.facsimiletelephonenumber','samse_ou',NULL,'{ call set_samse_ou_fax(?,?) }','{ call del_samse_ou_fax(?,?) }',3,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 (15,3,'street','samse_ou.street','samse_ou',NULL,'{ call set_samse_ou_street(?,?) }','{ call del_samse_ou_street(?,?) }',3,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 (16,3,'postOfficeBox','samse_ou.postofficebox','samse_ou',NULL,'{ call set_samse_ou_postofficebox(?,?) }','{ call del_samse_ou_postofficebox(?,?) }',3,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 (17,3,'postalCode','samse_ou.postalcode','samse_ou',NULL,'{ call set_samse_ou_postalcode(?,?) }','{ call del_samse_ou_postalcode(?,?) }',3,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 (18,3,'l','samse_ou.l','samse_ou',NULL,'{ call set_samse_ou_l(?,?) }','{ call del_samse_ou_l(?,?) }',3,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 (19,3,'region','samse_ou.region','samse_ou',NULL,'{ call set_samse_ou_region(?,?) }','{ call del_samse_ou_region(?,?) }',3,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 (20,3,'rpvNumber','samse_ou.rpvnumber','samse_ou',NULL,'{ call set_samse_ou_rpvnumber(?,?) }','{ call del_samse_ou_rpvnumber(?,?) }',3,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 (21,3,'companyNumber','samse_ou.companynumber','samse_ou',NULL,'{ call set_samse_ou_companynumber(?,?) }','{ call del_samse_ou_companynumber(?,?) }',3,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 (22,3,'agencyShield','samse_ou.agencyshield','samse_ou',NULL,'{ call set_samse_ou_agencyshield(?,?) }','{ call del_samse_ou_agencyshield(?,?) }',3,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 (23,3,'mailDomain','samse_ou.maildomain','samse_ou',NULL,'{ call set_samse_ou_maildomain(?,?) }','{ call del_samse_ou_maildomain(?,?) }',3,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 (24,3,'codeApe','samse_ou.codeape','samse_ou',NULL,'{ call set_samse_ou_codeape(?,?) }','{ call del_samse_ou_codeape(?,?) }',3,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 (25,3,'realSite','samse_ou.realsite','samse_ou',NULL,'{ call set_samse_ou_realsite(?,?) }','{ call del_samse_ou_realsite(?,?) }',3,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 (26,3,'codeLidis','samse_ou.codelidis','samse_ou',NULL,'{ call set_samse_ou_codelidis(?,?) }','{ call del_samse_ou_codelidis(?,?) }',3,0);
SELECT setval ('ldap_attr_mappings_id_seq', 26, false);
-- posixGroup's attributes
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 (27,4,'cn','posix_group.cn','posix_group',NULL,'{ call set_posix_group_cn(?,?) }',NULL,3,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 (28,4,'gidnumber','posix_group.gidnumber','posix_group',NULL,'{ call set_posix_group_gidnumber(?,?) }',NULL,3,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 (29,4,'memberUid','group_member.memberuid','group_member,posix_group','group_member.group_id=posix_group.id','{ call set_group_member_memberuid(?,?) }','{ call del_group_member_memberuid(?,?) }',3,0);
-- samsePerson's attributes
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 (30,5,'ou','samse_person.ou','samse_person',NULL,'{ call set_samse_person_ou(?,?) }',NULL,3,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 (31,5,'employeeNumber','samse_person.employeenumber','samse_person',NULL,'{ call set_samse_person_employeenumber(?,?) }',NULL,3,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 (32,5,'uid','samse_person.uid','samse_person',NULL,'{ call set_samse_person_uid(?,?) }',NULL,3,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 (33,5,'cn','samse_person.cn','samse_person',NULL,'{ call set_samse_person_cn(?,?) }','{ call del_samse_person_cn(?,?) }',3,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 (34,5,'sn','samse_person.sn','samse_person',NULL,'{ call set_samse_person_sn(?,?) }','{ call del_samse_person_sn(?,?) }',3,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 (36,5,'givenName','samse_person.givenname','samse_person',NULL,'{ call set_samse_person_givenname(?,?) }','{ call del_samse_person_givenname(?,?) }',3,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 (37,5,'displayName','samse_person.displayname','samse_person',NULL,'{ call set_samse_person_displayname(?,?) }','{ call del_samse_person_displayname(?,?) }',3,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 (38,5,'mail','samse_person.mail','samse_person',NULL,'{ call set_samse_person_mail(?,?) }','{ call del_samse_person_mail(?,?) }',3,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 (39,5,'mailboxName','samse_person.mailboxname','samse_person',NULL,'{ call set_samse_person_mailboxname(?,?) }','{ call del_samse_person_mailboxname(?,?) }',3,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 (40,5,'title','samse_person.title','samse_person',NULL,'{ call set_samse_person_title(?,?) }','{ call del_samse_person_title(?,?) }',3,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 (41,5,'employeeType','samse_person.employeetype','samse_person',NULL,'{ call set_samse_person_employeetype(?,?) }','{ call del_samse_person_employeetype(?,?) }',3,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 (42,5,'departmentNumber','samse_person.departmentnumber','samse_person',NULL,'{ call set_samse_person_departmentnum(?,?) }','{ call del_samse_person_departmentnum(?,?) }',3,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 (43,5,'arrivalDate','samse_person.arrivaldate','samse_person',NULL,'{ call set_samse_person_arrivaldate(?,?) }','{ call del_samse_person_arrivaldate(?,?) }',3,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 (44,5,'birthDate','samse_person.birthdate','samse_person',NULL,'{ call set_samse_person_birthdate(?,?) }','{ call del_samse_person_birthdate(?,?) }',3,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 (46,5,'leavingDate','samse_person.leavingdate','samse_person',NULL,'{ call set_samse_person_leavingdate(?,?) }','{ call del_samse_person_leavingdate(?,?) }',3,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 (47,5,'contributionGroup','samse_person.contributiongroup','samse_person',NULL,'{ call set_samse_person_contribgroup(?,?) }','{ call del_samse_person_contribgroup(?,?) }',3,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 (48,5,'userPassword','samse_person.userpassword','samse_person',NULL,'{ call set_samse_person_userpassword(?,?) }','{ call del_samse_person_userpassword(?,?) }',3,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 (49,5,'loginShell','samse_person.loginshell','samse_person',NULL,'{ call set_samse_person_loginshell(?,?) }','{ call del_samse_person_loginshell(?,?) }',3,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 (50,5,'uidNumber','samse_person.uidnumber','samse_person',NULL,'{ call set_samse_person_uidnumber(?,?) }','{ call del_samse_person_uidnumber(?,?) }',3,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 (51,5,'gidNumber','samse_person.gidnumber','samse_person',NULL,'{ call set_samse_person_gidnumber(?,?) }','{ call del_samse_person_gidnumber(?,?) }',3,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 (52,5,'homeDirectory','samse_person.homedirectory','samse_person',NULL,'{ call set_samse_person_homedirectory(?,?) }','{ call del_samse_person_homedirectory(?,?) }',3,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 (53,5,'shadowLastChange','samse_person.shadowlastchange','samse_person',NULL,'{ call set_samse_person_shadowlastchg(?,?) }','{ call del_samse_person_shadowlastchg(?,?) }',3,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 (54,5,'shadowMin','samse_person.shadowmin','samse_person',NULL,'{ call set_samse_person_shadowmin(?,?) }','{ call del_samse_person_shadowmin(?,?) }',3,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 (55,5,'shadowMax','samse_person.shadowmax','samse_person',NULL,'{ call set_samse_person_shadowmax(?,?) }','{ call del_samse_person_shadowmax(?,?) }',3,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 (56,5,'shadowWarning','samse_person.shadowwarning','samse_person',NULL,'{ call set_samse_person_shadowwarning(?,?) }','{ call del_samse_person_shadowwarning(?,?) }',3,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 (57,5,'telephoneNumber','samse_person.telephonenumber','samse_person',NULL,'{ call set_samse_person_phonenumber(?,?) }','{ call del_samse_person_telephonenumber(?,?) }',3,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 (58,5,'fixRPVNumber','samse_person.fixrpvnumber','samse_person',NULL,'{ call set_samse_person_fixrpvnumber(?,?) }','{ call del_samse_person_fixrpvnumber(?,?) }',3,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 (59,5,'mobile','samse_person.mobile','samse_person',NULL,'{ call set_samse_person_mobile(?,?) }','{ call del_samse_person_mobile(?,?) }',3,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 (60,5,'mobileRPVNumber','samse_person.mobilerpvnumber','samse_person',NULL,'{ call set_samse_person_mobilerpvnum(?,?) }','{ call del_samse_person_mobilerpvnum(?,?) }',3,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 (61,5,'assistant','samse_person.assistant','samse_person',NULL,'{ call set_samse_person_assistant(?,?) }','{ call del_samse_person_assistant(?,?) }',3,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 (62,5,'sexe','samse_person.sexe','samse_person',NULL,'{ call set_samse_person_sexe(?,?) }','{ call del_samse_person_sexe(?,?) }',3,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 (63,5,'codeEmploi','samse_person.codeemploi','samse_person',NULL,'{ call set_samse_person_codeemploi(?,?) }','{ call del_samse_person_codeemploi(?,?) }',3,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 (64,5,'codeAgence','samse_person.codeagence','samse_person',NULL,'{ call set_samse_person_codeagence(?,?) }','{ call del_samse_person_codeagence(?,?) }',3,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 (65,5,'fonction','samse_person.fonction','samse_person',NULL,'{ call set_samse_person_fonction(?,?) }','{ call del_samse_person_fonction(?,?) }',3,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 (66,5,'activite','samse_activite.activite','samse_activite,samse_person','samse_activite.person_id=samse_person.id','{ call set_samse_activite_activite(?,?) }','{ call del_samse_activite_activite(?,?) }',3,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 (67,5,'shadowExpire','samse_person.shadowexpire','samse_person',NULL,'{ call set_samse_person_shadowexpire(?,?) }','{ call del_samse_person_shadowexpire(?,?) }',3,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 (68,5,'service','samse_person.service','samse_person',NULL,'{ call set_samse_person_service(?,?) }','{ call del_samse_person_service(?,?) }',3,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 (69,5,'typeRTT','samse_person.typertt','samse_person',NULL,'{ call set_samse_person_typertt(?,?) }','{ call del_samse_person_typertt(?,?) }',3,0);
-- Entries
-- Insert the root directory dcObject
insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values (1, 'dc=samse,dc=fr',1,0,1);
insert into dc_object (id,dc) values (1,'samse');
SELECT setval ('dc_object_id_seq', 1, false);
-- Insert all organizationalUnit objects
insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values (2, 'ou=company,dc=samse,dc=fr',2,1,1);
insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values (3, 'ou=region,dc=samse,dc=fr',2,1,2);
insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values (4, 'ou=agency,dc=samse,dc=fr',2,1,3);
insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values (5, 'ou=people,dc=samse,dc=fr',2,1,4);
insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values (6, 'ou=group,dc=samse,dc=fr',2,1,5);
SELECT setval ('ldap_entries_id_seq', 6, false);
END TRANSACTION;
|
Copy this transaction into a file named testmetadata.sql and execute the following command as postgres user:
psql pg_ldap < testmetadata.sql |
-- If you don't have PL/PGSQL activated uncomment the following lines
DROP FUNCTION plpgsql_call_handler();
DROP PROCEDURAL LANGUAGE 'plpgsql';
CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';
BEGIN TRANSACTION;
---------------------------------------------------------------------------------
-- Create internal procedures for samseOu objectClass
-- SAMSE: samseOu objectClass
---------------------------------------------------------------------------------
CREATE FUNCTION create_samse_ou () RETURNS int
AS '
SELECT setval (''samse_ou_id_seq'', (select max(id) FROM samse_ou));
INSERT INTO samse_ou (id,ou,uid)
VALUES (nextval(''samse_ou_id_seq''),'''','''');
SELECT max(id) FROM samse_ou
'
LANGUAGE 'sql';
CREATE FUNCTION delete_samse_ou (int) RETURNS int
AS '
DELETE FROM samse_ou WHERE id=CAST($1 AS INT);
SELECT $1 AS RETURN
'
LANGUAGE 'sql';
---------------------------------------------------------------------------------
-- Create internal procedures for modifying samseOu attributes
-- SAMSE: ou=company,dc=samse,dc=fr
-- SAMSE: ou=region,dc=samse,dc=fr
-- SAMSE: ou=agency,dc=samse,dc=fr
---------------------------------------------------------------------------------
CREATE FUNCTION set_samse_ou_uid (varchar,int) RETURNS int
AS '
UPDATE samse_ou SET uid=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_ou(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET ou=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_legalform(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET legalform=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_legalform(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET legalform=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_capital(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET capital=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_capital(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET capital=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_collectiveconv(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET collectiveconvention=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_collectiveconv(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET collectiveconvention=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_description(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET description=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_description(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET description=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_responsable(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET responsable=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_responsable(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET responsable=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_quality(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET quality=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_quality(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET quality=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_internationalisdn(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET internationalisdnnumber=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_internationalisdn(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET internationalisdnnumber=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_businesscategory(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET businesscategory=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_businesscategory(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET businesscategory=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_telephonenumber(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET telephonenumber=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_telephonenumber(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET telephonenumber=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_fax(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET facsimiletelephonenumber=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_fax(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET facsimiletelephonenumber=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_street(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET street=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_street(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET street=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_postofficebox(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET postofficebox=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_postofficebox(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET postofficebox=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_postalcode(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET postalcode=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_postalcode(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET postalcode=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_l(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET l=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_l(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET l=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_region(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET region=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_region(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET region=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_rpvnumber(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET rpvnumber=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_rpvnumber(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET rpvnumber=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_companynumber(int, int) RETURNS int
AS '
UPDATE samse_ou SET companynumber=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_companynumber(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET companynumber=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_agencyshield(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET agencyshield=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_agencyshield(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET agencyshield=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_maildomain(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET maildomain=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_maildomain(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET maildomain=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_codeape(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET codeape=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_codeape(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET codeape=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_realsite(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET realsite=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_realsite(varchar, int) RETURNS int
AS '
UPDATE samse_ou SET realsite=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_ou_codelidis(int, int) RETURNS int
AS '
UPDATE samse_ou SET codelidis=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_ou_codelidis(varchar, int)
RETURNS int4 AS '
UPDATE samse_ou SET codelidis=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
---------------------------------------------------------------------------------
-- Create internal procedures for posixGroup objectClass
-- SAMSE: posixGroup objectClass
---------------------------------------------------------------------------------
CREATE FUNCTION create_posix_group () RETURNS int
AS '
SELECT setval (''posix_group_id_seq'', (select max(id) FROM posix_group));
INSERT INTO posix_group (id,cn,gidnumber)
VALUES (nextval(''posix_group_id_seq''),'''',100000);
SELECT max(id) FROM posix_group
'
LANGUAGE 'sql';
CREATE FUNCTION delete_posix_group (int) RETURNS int
AS '
DELETE FROM group_member WHERE group_id=CAST($1 AS INT);
DELETE FROM posix_group WHERE id=CAST($1 AS INT);
SELECT $1 AS RETURN
'
LANGUAGE 'sql';
---------------------------------------------------------------------------------
-- Create internal procedures for modifying posixGroup attributes
-- SAMSE: ou=group,dc=samse,dc=fr
---------------------------------------------------------------------------------
CREATE FUNCTION set_posix_group_cn(varchar, int) RETURNS int
AS '
UPDATE posix_group SET cn=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_posix_group_gidnumber(int, int) RETURNS int
AS '
UPDATE posix_group SET gidnumber=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_group_member_memberuid(varchar, int) RETURNS int
AS '
INSERT INTO group_member VALUES ($2, $1);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_group_member_memberuid(varchar, int) RETURNS int
AS '
DELETE FROM group_member WHERE group_id=CAST($2 AS INT) AND memberuid=CAST($1 AS VARCHAR);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
---------------------------------------------------------------------------------
-- Create internal procedures for samsePerson objectClass
-- SAMSE: samsePerson objectClass
---------------------------------------------------------------------------------
CREATE FUNCTION create_samse_person () RETURNS int
AS '
SELECT setval (''samse_person_id_seq'', (select max(id) FROM samse_person));
INSERT INTO samse_person (id,ou,employeenumber,uid,cn)
VALUES (nextval(''samse_person_id_seq''),'''','''','''','''');
SELECT max(id) FROM samse_person
'
LANGUAGE 'sql';
CREATE FUNCTION delete_samse_person (int) RETURNS int
AS '
DELETE FROM samse_activite WHERE person_id=CAST($1 AS INT);
DELETE FROM samse_person WHERE id=CAST($1 AS INT);
SELECT $1 AS RETURN
'
LANGUAGE 'sql';
---------------------------------------------------------------------------------
-- Create internal procedures for modifying samsePerson attributes
-- SAMSE: ou=people,dc=samse,dc=fr
---------------------------------------------------------------------------------
CREATE FUNCTION set_samse_person_ou(varchar, int) RETURNS int
AS '
UPDATE samse_person SET ou=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_employeenumber(varchar, int) RETURNS int
AS '
UPDATE samse_person SET employeenumber=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_uid(varchar, int) RETURNS int
AS '
UPDATE samse_person SET uid=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_uid(varchar, int) RETURNS int
AS '
UPDATE samse_person SET uid=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_cn(varchar, int) RETURNS int
AS '
UPDATE samse_person SET cn=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_cn(varchar, int) RETURNS int
AS '
UPDATE samse_person SET cn=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_sn(varchar, int) RETURNS int
AS '
UPDATE samse_person SET sn=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_sn(varchar, int) RETURNS int
AS '
UPDATE samse_person SET sn=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_givenname(varchar, int) RETURNS int
AS '
UPDATE samse_person SET givenname=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_givenname(varchar, int) RETURNS int
AS '
UPDATE samse_person SET givenname=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_displayname(varchar, int) RETURNS int
AS '
UPDATE samse_person SET displayname=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_displayname(varchar, int) RETURNS int
AS '
UPDATE samse_person SET displayname=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_mail(varchar, int) RETURNS int
AS '
UPDATE samse_person SET mail=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_mail(varchar, int) RETURNS int
AS '
UPDATE samse_person SET mail=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_mailboxname(varchar, int) RETURNS int
AS '
UPDATE samse_person SET mailboxname=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_mailboxname(varchar, int) RETURNS int
AS '
UPDATE samse_person SET mailboxname=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_title(varchar, int) RETURNS int
AS '
UPDATE samse_person SET title=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_title(varchar, int) RETURNS int
AS '
UPDATE samse_person SET title=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_employeetype(char(1), int) RETURNS int
AS '
UPDATE samse_person SET employeetype=CAST($1 AS CHAR(1)) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_employeetype(char(1), int) RETURNS int
AS '
UPDATE samse_person SET employeetype=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_departmentnum(int, int) RETURNS int
AS '
UPDATE samse_person SET departmentnumber=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_departmentnum(int, int) RETURNS int
AS '
UPDATE samse_person SET departmentnumber=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_arrivaldate(varchar, int) RETURNS int
AS '
UPDATE samse_person SET arrivaldate=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_arrivaldate(varchar, int) RETURNS int
AS '
UPDATE samse_person SET arrivaldate=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_birthdate(varchar, int) RETURNS int
AS '
UPDATE samse_person SET birthdate=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_birthdate(varchar, int) RETURNS int
AS '
UPDATE samse_person SET birthdate=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_leavingdate(varchar, int) RETURNS int
AS '
UPDATE samse_person SET leavingdate=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_leavingdate(varchar, int) RETURNS int
AS '
UPDATE samse_person SET leavingdate=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_contribgroup(char(2), int) RETURNS int
AS '
UPDATE samse_person SET contributiongroup=CAST($1 AS CHAR(2)) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_contribgroup(char(2), int) RETURNS int
AS '
UPDATE samse_person SET contributiongroup=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_userpassword(varchar, int) RETURNS int
AS '
UPDATE samse_person SET userpassword=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_userpassword(varchar, int) RETURNS int
AS '
UPDATE samse_person SET userpassword=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_loginshell(varchar, int) RETURNS int
AS '
UPDATE samse_person SET loginshell=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_loginshell(varchar, int) RETURNS int
AS '
UPDATE samse_person SET loginshell=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_uidnumber(int, int) RETURNS int
AS '
UPDATE samse_person SET uidnumber=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_uidnumber(int, int) RETURNS int
AS '
UPDATE samse_person SET uidnumber=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_gidnumber(int, int) RETURNS int
AS '
UPDATE samse_person SET gidnumber=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_gidnumber(int, int) RETURNS int
AS '
UPDATE samse_person SET gidnumber=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_shadowlastchg(int, int) RETURNS int
AS '
UPDATE samse_person SET shadowlastchange=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_shadowlastchg(int, int) RETURNS int
AS '
UPDATE samse_person SET shadowlastchange=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_shadowmin(int, int) RETURNS int
AS '
UPDATE samse_person SET shadowmin=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_shadowmin(int, int) RETURNS int
AS '
UPDATE samse_person SET shadowmin=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_shadowmax(int, int) RETURNS int
AS '
UPDATE samse_person SET shadowmax=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_shadowmax(int, int) RETURNS int
AS '
UPDATE samse_person SET shadowmax=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_shadowwarning(int, int) RETURNS int
AS '
UPDATE samse_person SET shadowwarning=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_shadowwarning(int, int) RETURNS int
AS '
UPDATE samse_person SET shadowwarning=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_fixrpvnumber(varchar, int) RETURNS int
AS '
UPDATE samse_person SET fixrpvnumber=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_fixrpvnumber(varchar, int) RETURNS int
AS '
UPDATE samse_person SET fixrpvnumber=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_mobile(varchar, int) RETURNS int
AS '
UPDATE samse_person SET mobile=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_mobile(varchar, int) RETURNS int
AS '
UPDATE samse_person SET mobile=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_mobilerpvnum(varchar, int) RETURNS int
AS '
UPDATE samse_person SET mobilerpvnumber=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_mobilerpvnum(varchar, int) RETURNS int
AS '
UPDATE samse_person SET mobilerpvnumber=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_assistant(varchar, int) RETURNS int
AS '
UPDATE samse_person SET assistant=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_assistant(varchar, int) RETURNS int
AS '
UPDATE samse_person SET assistant=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_phonenumber(varchar, int) RETURNS int
AS '
UPDATE samse_person SET telephonenumber=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_phonenumber(varchar, int) RETURNS int
AS '
UPDATE samse_person SET telephonenumber=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_sexe(char(1), int) RETURNS int
AS '
UPDATE samse_person SET sexe=CAST($1 AS CHAR(1)) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_sexe(char(1), int) RETURNS int
AS '
UPDATE samse_person SET sexe=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_homedirectory(varchar, int) RETURNS int
AS '
UPDATE samse_person SET homedirectory=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_homedirectory(varchar, int) RETURNS int
AS '
UPDATE samse_person SET homedirectory=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_codeemploi(varchar, int) RETURNS int
AS '
UPDATE samse_person SET codeemploi=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_codeemploi(varchar, int) RETURNS int
AS '
UPDATE samse_person SET codeemploi=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_codeagence(int, int) RETURNS int
AS '
UPDATE samse_person SET codeagence=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_codeagence(int, int) RETURNS int
AS '
UPDATE samse_person SET codeagence=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_fonction(varchar, int) RETURNS int
AS '
UPDATE samse_person SET fonction=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_fonction(varchar, int) RETURNS int
AS '
UPDATE samse_person SET fonction=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_activite_activite(varchar, int) RETURNS int
AS '
INSERT INTO samse_activite VALUES ($2, $1);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_activite_activite(varchar, int) RETURNS int
AS '
DELETE FROM samse_activite WHERE person_id=CAST($2 AS INT) AND activite=CAST($1 AS VARCHAR);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_shadowexpire(int, int) RETURNS int
AS '
UPDATE samse_person SET shadowexpire=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_shadowexpire(int, int) RETURNS int
AS '
UPDATE samse_person SET shadowexpire=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_service(varchar, int) RETURNS int
AS '
UPDATE samse_person SET service=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_service(varchar, int) RETURNS int
AS '
UPDATE samse_person SET service=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION set_samse_person_typertt(varchar, int) RETURNS int
AS '
UPDATE samse_person SET typertt=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
CREATE FUNCTION del_samse_person_typertt(varchar, int) RETURNS int
AS '
UPDATE samse_person SET typertt=NULL WHERE id=CAST($2 AS INT);
SELECT $2 AS RETURN
'
LANGUAGE 'sql';
END TRANSACTION;
|
Copy this transaction into a file named testfunction.sql and execute the following command as postgres user:
psql pg_ldap < testfunction.sql |
grant all on dc_object, dc_object_id_seq, samse_ou, samse_ou_id_seq, posix_group, posix_group_id_seq, group_member, samse_person, samse_person_id_seq, samse_activite, organizational_unit, organizational_unit_id_seq to test; |
Copy this transaction into a file named testgrant.sql and execute the following command as postgres user:
psql pg_ldap < testgrant.sql |
That's all, now we have to load initial data by dumping ldiff record from the LDAP master directory. If you don't have this and for example start from an existing SQL database you still have the work to create a script to create all ldap entries. If so take a look at the testdb_data.sql and the entries part of the testdb_metadata.sql in the rdbms_depend/pgsql directory this will help you to understand howto to map your LDAP entries with the SQL datas.