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 $$
24 FROM actor.org_unit_descendants(
25 CASE WHEN $2 IS NOT NULL THEN
26 (actor.org_unit_ancestor_at_depth($1,$2)).id
31 $$ LANGUAGE SQL STABLE;
33 CREATE OR REPLACE FUNCTION actor.noncached_org_unit_descendants ( org INT ) RETURNS SETOF actor.org_unit AS $$
35 kid actor.org_unit%ROWTYPE;
36 curr_org actor.org_unit%ROWTYPE;
39 SELECT * INTO curr_org FROM actor.org_unit WHERE id = org;
42 FOR kid IN SELECT * FROM actor.org_unit WHERE parent_ou = org LOOP
43 FOR curr_org IN SELECT * FROM actor.noncached_org_unit_descendants(kid.id) LOOP
52 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( org INT ) RETURNS SETOF actor.org_unit AS $func$
54 kid actor.org_unit%ROWTYPE;
55 curr_org actor.org_unit%ROWTYPE;
56 idlist INT[] := '{}'::INT[];
64 IF permission.mc_init() THEN
65 -- RAISE NOTICE 'Getting perm from cache';
66 EXECUTE $$SELECT memcache_get('oils_orgcache_$$ || org || $$') AS x;$$ INTO cached_value;
68 IF cached_value.x IS NOT NULL AND cached_value.x <> '' THEN
72 WHERE id IN ( SELECT * FROM explode_array( STRING_TO_ARRAY( cached_value.x, ',' ) ) )
82 SELECT * INTO curr_org FROM actor.org_unit WHERE id = org;
85 idlist := ARRAY_APPEND( idlist, curr_org.id );
87 FOR kid IN SELECT * FROM actor.org_unit WHERE parent_ou = org LOOP
88 FOR curr_org IN SELECT * FROM actor.noncached_org_unit_descendants(kid.id) LOOP
90 idlist := ARRAY_APPEND( idlist, curr_org.id );
94 IF permission.mc_init() THEN
97 'oils_orgcache_$$ || org || $$',
98 $$ || QUOTE_LITERAL(ARRAY_TO_STRING(idlist,',')) || $$,
99 '10 minutes'::INTERVAL
106 $func$ LANGUAGE PLPGSQL;