4 * Use pgmemcache and memcached to increase the speed of org tree traversal
5 * ------------------------------------------------------------------------
7 * This set of functions allows the use of memcached as a caching mechanism for
8 * org tree traversal checks. It is transparent and optional. If memcache is
9 * not set up, either by not running or the lack of the pgmemcache postgres
10 * addon, then the default, existing behaviour is preserved and live database
11 * queries are used to test all org tree traversals.
13 * This Evergreen addon extention requires the pgmemcache-perm_cache.sql to be
14 * installed as well. See that extention script for details on pgmemcache
15 * setup and installation.
17 * TODO: Make the cache timeout configurable via a global setting for EG 2.0
22 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
23 SELECT * FROM actor.org_unit_descendants( (actor.org_unit_ancestor_at_depth($1,$2)).id );
24 $$ LANGUAGE SQL STABLE;
26 CREATE OR REPLACE FUNCTION actor.noncached_org_unit_descendants ( org INT ) RETURNS SETOF actor.org_unit AS $$
28 kid actor.org_unit%ROWTYPE;
29 curr_org actor.org_unit%ROWTYPE;
32 SELECT * INTO curr_org FROM actor.org_unit WHERE id = org;
35 FOR kid IN SELECT * FROM actor.org_unit WHERE parent_ou = org LOOP
36 FOR curr_org IN SELECT * FROM actor.noncached_org_unit_descendants(kid.id) LOOP
45 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( org INT ) RETURNS SETOF actor.org_unit AS $func$
47 kid actor.org_unit%ROWTYPE;
48 curr_org actor.org_unit%ROWTYPE;
49 idlist INT[] := '{}'::INT[];
53 IF permission.mc_init() THEN
54 -- RAISE NOTICE 'Getting perm from cache';
55 EXECUTE $$SELECT memcache_get('oils_orgcache_$$ || org || $$') AS x;$$ INTO cached_value;
57 IF cached_value.x IS NOT NULL THEN
61 WHERE id IN ( SELECT * FROM explode_array( STRING_TO_ARRAY( cached_value.x, ',' ) ) )
71 SELECT * INTO curr_org FROM actor.org_unit WHERE id = org;
74 idlist := ARRAY_APPEND( idlist, curr_org.id );
76 FOR kid IN SELECT * FROM actor.org_unit WHERE parent_ou = org LOOP
77 FOR curr_org IN SELECT * FROM actor.noncached_org_unit_descendants(kid.id) LOOP
79 idlist := ARRAY_APPEND( idlist, curr_org.id );
83 IF permission.mc_init() THEN
86 'oils_orgcache_$$ || org || $$',
87 $$ || QUOTE_LITERAL(ARRAY_TO_STRING(idlist,',')) || $$,
88 '10 minutes'::INTERVAL
95 $func$ LANGUAGE PLPGSQL;