用postgresql作后台的openldap

5day

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';

5day

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';

5day

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';

5day

---------------------------------------------------------------------------------
-- 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';

5day

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';

5day

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';

5day

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;

5day

6.3.4. Defining grant to the database


grant all on
        dc_object,
        dc_object_id_seq,
        ldap_attr_mappings,
        ldap_attr_mappings_id_seq,
        ldap_entries,
        ldap_entries_id_seq,
        ldap_entry_objclasses,
        ldap_oc_mappings,
        ldap_oc_mappings_id_seq,
        ldap_referrals,
        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;

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.
6.3.5. Load live data



# extended LDIF
#
# LDAPv3
# base  with scope sub
#

# 001234, people, samse.fr
dn: employeeNumber=001234,ou=people,dc=samse,dc=fr
ou: people
objectClass: top
objectClass: person
objectClass: organizationalPerson
objectClass: inetOrgPerson
objectClass: samsePerson
objectClass: posixAccount
objectClass: shadowAccount
employeeNumber: 001234
fonction: F
codeEmploi: 1304
displayName: Jean FRANCOIS
mailboxName: jfrancois
shadowWarning: 7
shadowMax: 99999
uidNumber: 3826
gidNumber: 100
title: LDAP TESTING ACCOUNT
birthDate: 20/12/66
shadowMin: 0
shadowInactive: -1
homeDirectory: /home/francois
cn: FRANCOIS Jean
sn: FRANCOIS
givenName: Jean
loginShell: /bin/sh
sexe: M
employeeType: I
uid: toto
activite: DEV
activite: ADM
typeRTT: JRR098
arrivalDate: 15/02/2000
contributionGroup: CA
mail: jfrancois@chez.fr
service: INFORMATIQUE
departmentNumber: 122
codeAgence: 991
telephoneNumber: 01 23 45 67 89
facsimileTelephoneNumber: 01 23 45 67 89
fixRPVNumber: 172


Now you may load this data as follow: ldapadd -D "cn=root,dc=samse,dc=fr" -w secret -f data.ldiff

5day

开始以为只有2页，后来发现例子太厉害，累死我了。
原文出处：
http://www.samse.fr/GPL/ldap_pg/HOWTO/
精益求精

[ 本帖最后由 5day 于 2005-11-4 11:41 编辑 ]