4 CREATE TABLE config.z3950_source_credentials (
6 owner INTEGER NOT NULL REFERENCES actor.org_unit(id),
7 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
8 -- do some Z servers require a username but no password or vice versa?
11 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
14 -- find the most relevant set of credentials for the Z source and org
15 CREATE OR REPLACE FUNCTION config.z3950_source_credentials_lookup
16 (source TEXT, owner INTEGER)
17 RETURNS config.z3950_source_credentials AS $$
20 FROM config.z3950_source_credentials creds
21 JOIN actor.org_unit aou ON (aou.id = creds.owner)
22 JOIN actor.org_unit_type aout ON (aout.id = aou.ou_type)
23 WHERE creds.source = $1 AND creds.owner IN (
24 SELECT id FROM actor.org_unit_ancestors($2)
26 ORDER BY aout.depth DESC LIMIT 1;
28 $$ LANGUAGE SQL STABLE;
30 -- since we are not exposing config.z3950_source_credentials
31 -- via the IDL, providing a stored proc gives us a way to
32 -- set values in the table via cstore
33 CREATE OR REPLACE FUNCTION config.z3950_source_credentials_apply
34 (src TEXT, org INTEGER, uname TEXT, passwd TEXT)
37 PERFORM 1 FROM config.z3950_source_credentials
38 WHERE owner = org AND source = src;
41 IF COALESCE(uname, '') = '' AND COALESCE(passwd, '') = '' THEN
42 DELETE FROM config.z3950_source_credentials
43 WHERE owner = org AND source = src;
45 UPDATE config.z3950_source_credentials
46 SET username = uname, password = passwd
47 WHERE owner = org AND source = src;
50 IF COALESCE(uname, '') <> '' OR COALESCE(passwd, '') <> '' THEN
51 INSERT INTO config.z3950_source_credentials
52 (source, owner, username, password)
53 VALUES (src, org, uname, passwd);