Acq: improve General Search's ability to find invoices

Invoices in Evergreen have complex relationships with other items.  They
can be related to lineitems (and ultimately POs and selection lists) by
invoice *entries*, or to PO items (and ultimately etc etc) by invoice
*items*, or directly to purchase orders by either of the above mentioned
acq.invoice_{entry,item} objects.

This should make general search more able to find invoices related to
other objects whose fields you might search against in Acquistions
General search.

Here's a diagram that I created to help me think about how to write
these joins:

https://docs.google.com/drawings/d/15ExkiYvq0skfobbocvPWxwdZkb7aykEZpLGfbP9PL04/edit

At Bill Erickson's suggestion, I wound up putting the joins into an IDL view
("acqus") rather than trying to express them in JSON query, which was turning
into a real time sink.

To prevent this change from making Acq General Search slower than it
was, I've also added indices on all the foreign keys used in the IDL
view, so that the joins should be as fast as possible.

Signed-off-by: Lebbeous Fogle-Weekley <lebbeous@esilibrary.com>
Signed-off-by: Bill Erickson <berick@esilibrary.com>
Signed-off-by: Mike Rylander <mrylander@gmail.com>

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index 1c92c2e..21236c3 100644 (file)
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -9479,6 +9479,37 @@ SELECT  usr,
                        </actions>
                </permacrud>
        </class>
+       <class id="acqus" controller="open-ils.cstore" oils_obj:fieldmapper="acq::unified_search" reporter:label="Acq Unified Search View" oils_persist:readonly="true">
+               <oils_persist:source_definition><![CDATA[
+               SELECT
+                       jub.id AS lineitem,
+                       po.id AS purchase_order,
+                       pl.id AS picklist,
+                       inv.id AS invoice
+               FROM acq.purchase_order po
+               FULL JOIN acq.lineitem jub ON (jub.purchase_order = po.id)
+               FULL JOIN acq.picklist pl ON (pl.id = jub.picklist)
+               LEFT JOIN acq.po_item poi ON (poi.purchase_order = po.id)
+               LEFT JOIN acq.invoice_item ii
+                       ON (ii.po_item = poi.id OR ii.purchase_order = po.id)
+               LEFT JOIN acq.invoice_entry ie
+                       ON (ie.lineitem = jub.id OR ie.purchase_order = po.id)
+               LEFT JOIN acq.invoice inv
+                       ON (ie.invoice = inv.id OR ii.invoice = inv.id)
+               ]]></oils_persist:source_definition>
+               <fields>
+                       <field reporter:label="Lineitem ID" name="lineitem" reporter:datatype="link"/>
+                       <field reporter:label="Purchase Order ID" name="purchase_order" reporter:datatype="link"/>
+                       <field reporter:label="Picklist ID" name="picklist" reporter:datatype="link"/>
+                       <field reporter:label="Invoice" name="invoice" reporter:datatype="link"/>
+               </fields>
+               <links>
+                       <link field="lineitem" reltype="has_a" key="id" map="" class="jub" />
+                       <link field="purchase_order" reltype="has_a" key="id" map="" class="acqpo" />
+                       <link field="picklist" reltype="has_a" key="id" map="" class="acqpl" />
+                       <link field="invoice" reltype="has_a" key="id" map="" class="acqinv" />
+               </links>
+       </class>
        <class id="cbc" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="config::barcode_completion" oils_persist:tablename="config.barcode_completion" reporter:label="Barcode Completions">
                <fields oils_persist:primary="id" oils_persist:sequence="config.barcode_completion_id_seq">
                        <field reporter:label="ID" name="id" reporter:datatype="id"/>

diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/Search.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/Search.pm
index 6e2d854..fdad64f 100644 (file)
--- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/Search.pm
+++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/Search.pm
@@ -395,30 +395,12 @@ q/order_by clause must be of the long form, like:
 
     my $query = {
         "select" => $select_clause,
-        "from" => {
-            "jub" => {
-                "acqpo" => {
-                    "type" => "full",
-                    "field" => "id",
-                    "fkey" => "purchase_order"
-                },
-                "acqpl" => {
-                    "type" => "full",
-                    "field" => "id",
-                    "fkey" => "picklist"
-                },
-                "acqie" => {
-                    "type" => "full",
-                    "field" => "lineitem",
-                    "fkey" => "id",
-                    "join" => {
-                        "acqinv" => {
-                            "type" => "full",
-                            "fkey" => "invoice",
-                            "field" => "id"
-                        }
-                    }
-                }
+        from => {
+            acqus => {
+                jub => {type => "full"},
+                acqpo => {type => "full"},
+                acqpl => {type => "full"},
+                acqinv => {type => "full"}
             }
         },
         "order_by" => ($options->{"order_by"} || {$hint => {"id" => {}}}),

diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql
index 2263898..10e29ef 100644 (file)
--- a/Open-ILS/src/sql/Pg/200.schema.acq.sql
+++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql
@@ -842,6 +842,10 @@ CREATE TABLE acq.invoice_entry (
        amount_paid     NUMERIC (8,2)
 );
 
+CREATE INDEX ie_inv_idx on acq.invoice_entry (invoice);
+CREATE INDEX ie_po_idx on acq.invoice_entry (purchase_order);
+CREATE INDEX ie_li_idx on acq.invoice_entry (lineitem);
+
 CREATE TABLE acq.invoice_item_type (
     code    TEXT    PRIMARY KEY,
     name    TEXT    NOT NULL,  -- i18n-ize
@@ -867,6 +871,8 @@ CREATE TABLE acq.po_item (
     target          BIGINT
 );
 
+CREATE INDEX poi_po_idx ON acq.po_item (purchase_order);
+
 CREATE TABLE acq.invoice_item ( -- for invoice-only debits: taxes/fees/non-bib items/etc
     id              SERIAL      PRIMARY KEY,
     invoice         INT         NOT NULL REFERENCES acq.invoice (id) ON UPDATE CASCADE ON DELETE CASCADE,
@@ -886,6 +892,10 @@ CREATE TABLE acq.invoice_item ( -- for invoice-only debits: taxes/fees/non-bib i
     target          BIGINT
 );
 
+CREATE INDEX ii_inv_idx on acq.invoice_item (invoice);
+CREATE INDEX ii_po_idx on acq.invoice_item (purchase_order);
+CREATE INDEX ii_poi_idx on acq.invoice_item (po_item);
+
 -- Patron requests
 CREATE TABLE acq.user_request_type (
     id      SERIAL  PRIMARY KEY,

diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_fk_indices.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_fk_indices.sql
new file mode 100644 (file)
index 0000000..4efa452
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq_fk_indices.sql
@@ -0,0 +1,15 @@
+BEGIN;
+
+SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
+
+CREATE INDEX poi_po_idx ON acq.po_item (purchase_order);
+
+CREATE INDEX ie_inv_idx on acq.invoice_entry (invoice);
+CREATE INDEX ie_po_idx on acq.invoice_entry (purchase_order);
+CREATE INDEX ie_li_idx on acq.invoice_entry (lineitem);
+
+CREATE INDEX ii_inv_idx on acq.invoice_item (invoice);
+CREATE INDEX ii_po_idx on acq.invoice_item (purchase_order);
+CREATE INDEX ii_poi_idx on acq.invoice_item (po_item);
+
+COMMIT;