From 5688a20cef68a5eae883b38a9ad85e6ce12c239b Mon Sep 17 00:00:00 2001
From: erickson <erickson@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Mon, 17 Nov 2008 23:30:05 +0000
Subject: [PATCH] Added a stored procedure to find the first occurrence of an
 org-unit setting for a specified org unit. This is an optimization over the
 existing process of going back/forth from the middle-layer apps and cstore.
 Plugged stored proc into utility code.

* The stored proc is the product of my non-SQL brain.  It's very linear and could probably be optimized...




git-svn-id: svn://svn.open-ils.org/ILS/trunk@11250 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 .../perlmods/OpenILS/Application/AppUtils.pm  | 55 +++++++++++++------
 Open-ILS/src/sql/Pg/020.schema.functions.sql  | 25 +++++++++
 2 files changed, 64 insertions(+), 16 deletions(-)

diff --git a/Open-ILS/src/perlmods/OpenILS/Application/AppUtils.pm b/Open-ILS/src/perlmods/OpenILS/Application/AppUtils.pm
index 4e5acf6ccc..d8a5537157 100644
--- a/Open-ILS/src/perlmods/OpenILS/Application/AppUtils.pm
+++ b/Open-ILS/src/perlmods/OpenILS/Application/AppUtils.pm
@@ -1244,28 +1244,51 @@ sub make_mbts {
 		
 		
 sub ou_ancestor_setting_value {
-    my $obj = ou_ancestor_setting(@_);
-    return ($obj) ? $obj->{value} : undef;
+    my($self, $org_id, $name, $e) = @_;
+    $e = $e || OpenILS::Utils::CStoreEditor->new;
+    my $query = {
+        select => {
+            aous => [ {
+                transform => 'actor.org_unit_ancestor_setting',
+                params => [$org_id],
+                column => 'name',
+                result_field => 'value',
+                alias => 'value'
+            } ]
+        },
+        from => 'aous',
+        where => {name => $name},
+        limit => 1 # since name is not required to be unique, this approach could return duplicate rows
+    };
+
+    my $obj = $e->json_query($query);
+    return OpenSRF::Utils::JSON->JSON2perl($obj->[0]->{value}) if @$obj;
+    return undef;
 }
 
 sub ou_ancestor_setting {
     my( $self, $orgid, $name, $e ) = @_;
     $e = $e || OpenILS::Utils::CStoreEditor->new;
 
-    do {
-        my $setting = $e->search_actor_org_unit_setting({org_unit=>$orgid, name=>$name})->[0];
-
-        if( $setting ) {
-            $logger->info("found org_setting $name at org $orgid : " . $setting->value);
-            return { org => $orgid, value => OpenSRF::Utils::JSON->JSON2perl($setting->value) };
-        }
-
-        my $org = $e->retrieve_actor_org_unit($orgid) or return $e->event;
-        $orgid = $org->parent_ou or return undef;
-
-    } while(1);
-
-    return undef;
+    my $query = {
+        select => {
+            aous => [ {
+                transform => 'actor.org_unit_ancestor_setting',
+                params => [$orgid],
+                column => 'name',
+                result_field => 'id',
+                alias => 'id'
+            } ]
+        },
+        from => 'aous',
+        where => {name => $name},
+        limit => 1 # since name is not required to be unique, this approach could return duplicate rows
+    };
+
+    my $obj = $e->json_query($query);
+    return undef unless @$obj;
+    my $setting = $e->retrieve_actor_org_unit_setting($obj->[0]->{id});
+    return { org => $setting->org_unit, value => OpenSRF::Utils::JSON->JSON2perl($setting->value) };
 }	
 		
 
diff --git a/Open-ILS/src/sql/Pg/020.schema.functions.sql b/Open-ILS/src/sql/Pg/020.schema.functions.sql
index 9528e1de17..b7f6a11afe 100644
--- a/Open-ILS/src/sql/Pg/020.schema.functions.sql
+++ b/Open-ILS/src/sql/Pg/020.schema.functions.sql
@@ -206,4 +206,29 @@ CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS
 	) z;
 $$ LANGUAGE SQL STABLE;
 
+CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting( setting_name TEXT, org_id INT ) RETURNS actor.org_unit_setting AS $$
+DECLARE
+    setting RECORD;
+    cur_org INT;
+BEGIN
+    cur_org := org_id;
+    LOOP
+        SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
+        IF FOUND THEN
+            RETURN setting;
+        END IF;
+        SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
+        IF cur_org IS NULL THEN
+            RETURN NULL;
+        END IF;
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+COMMENT ON FUNCTION actor.org_unit_ancestor_setting( TEXT, INT) IS $$
+/**
+* Search "up" the org_unit tree until we find the first occurrence of an 
+* org_unit_setting with the given name.
+*/
+$$;
 
-- 
2.43.2