From 2759e4e9b3604379be518e7ae34962d6a4be5846 Mon Sep 17 00:00:00 2001 From: Lebbeous Fogle-Weekley Date: Tue, 14 Feb 2012 17:49:54 -0500 Subject: [PATCH] Acq: General Search refactor This is a combination of the following three commits from master: 3149433d354889fb 56121886c72b2d41 35fe5b9cbe10b132 The first two were meant for backport to rel_2_0 and rel_2_1 at the time, but the backport wasn't done. They were buggy anyway, and now it makes the most sense (I hope) to backport them together with the corrective commit all at once. Signed-off-by: Lebbeous Fogle-Weekley Signed-off-by: Dan Scott --- Open-ILS/examples/fm_IDL.xml | 136 +++++++++++++++++++++ .../perlmods/lib/OpenILS/Application/Acq/Search.pm | 121 ++++++++++-------- Open-ILS/src/sql/Pg/200.schema.acq.sql | 10 ++ .../sql/Pg/upgrade/0691.schema.acq_fk_indices.sql | 15 +++ 4 files changed, 228 insertions(+), 54 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0691.schema.acq_fk_indices.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 602ee6b874..d0942dc14c 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -8811,6 +8811,142 @@ SELECT usr, + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + 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 6e2d8546ce..cbf4c73c5c 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/Search.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/Search.pm @@ -156,12 +156,16 @@ sub get_fm_links_by_hint { sub gen_au_term { my ($value, $n) = @_; + my $lc_value = { + "=" => { transform => "lowercase", value => lc($value) } + }; + +{ "-or" => [ {"+au$n" => {"usrname" => $value}}, - {"+au$n" => {"first_given_name" => $value}}, - {"+au$n" => {"second_given_name" => $value}}, - {"+au$n" => {"family_name" => $value}}, + {"+au$n" => {"first_given_name" => $lc_value}}, + {"+au$n" => {"second_given_name" => $lc_value}}, + {"+au$n" => {"family_name" => $lc_value}}, {"+ac$n" => {"barcode" => $value}} ] }; @@ -262,19 +266,13 @@ sub prepare_terms { } sub add_au_joins { - my ($from) = shift; + my $graft_map = shift; + my $core_hint = shift; my $n = 0; foreach my $join (@_) { my ($hint, $attr, $num) = @$join; - my $start; - if ($hint eq "jub") { - $start = $from->{$hint}; - } elsif ($hint eq "acqinv") { - $start = $from->{"jub"}->{"acqie"}->{"join"}->{$hint}; - } else { - $start = $from->{"jub"}->{$hint}; - } + my $start = $graft_map->{$hint}; my $clause = { "class" => "au", "type" => "left", @@ -289,17 +287,64 @@ sub add_au_joins { } } }; - if ($hint eq "jub") { + + if ($hint eq $core_hint) { $start->{"au$num"} = $clause; } else { $start->{"join"} ||= {}; $start->{"join"}->{"au$num"} = $clause; } + $n++; } $n; } +sub build_from_clause_and_joins { + my ($query, $core, $and_terms, $or_terms) = @_; + + my %graft_map = (); + + $graft_map{$core} = $query->{from}{$core} = {}; + + my $join_type = keys(%$or_terms) ? "left" : "inner"; + + my @classes = grep { $core ne $_ } (keys(%$and_terms), keys(%$or_terms)); + my %classes_uniq = map { $_ => 1 } @classes; + @classes = keys(%classes_uniq); + + my $acqlia_join = sub { + return {"type" => "left", "field" => "lineitem", "fkey" => "id"}; + }; + + foreach my $class (@classes) { + if ($class eq 'acqlia') { + if ($core eq 'acqinv') { + $graft_map{acqlia} = + $query->{from}{$core}{acqmapinv}{join}{jub}{join}{acqlia} = + $acqlia_join->(); + } elsif ($core eq 'jub') { + $graft_map{acqlia} = + $query->{from}{$core}{acqlia} = + $acqlia_join->(); + } else { + $graft_map{acqlia} = + $query->{from}{$core}{jub}{join}{acqlia} = + $acqlia_join->(); + } + } elsif ($class eq 'acqinv' or $core eq 'acqinv') { + $graft_map{$class} = + $query->{from}{$core}{acqmapinv}{join}{$class} ||= {}; + $graft_map{$class}{type} = $join_type; + } else { + $graft_map{$class} = $query->{from}{$core}{$class} ||= {}; + $graft_map{$class}{type} = $join_type; + } + } + + return \%graft_map; +} + __PACKAGE__->register_method( method => "unified_search", api_name => "open-ils.acq.lineitem.unified_search", @@ -394,54 +439,22 @@ 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" - } - } - } - } - }, - "order_by" => ($options->{"order_by"} || {$hint => {"id" => {}}}), - "offset" => ($options->{"offset"} || 0) + select => $select_clause, + order_by => ($options->{order_by} || {$hint => {id => {}}}), + offset => ($options->{offset} || 0) }; $query->{"limit"} = $options->{"limit"} if $options->{"limit"}; - # XXX for the future? but it doesn't quite work as is. -# # Remove anything in temporary picklists from search results. -# $and_terms ||= {}; -# $and_terms->{"acqpl"} ||= []; -# push @{$and_terms->{"acqpl"}}, {"name" => "", "__not" => 1}; + my $graft_map = build_from_clause_and_joins( + $query, $hint, $and_terms, $or_terms + ); $and_terms = prepare_terms($and_terms, 1); - $or_terms = prepare_terms($or_terms, 0) and do { - $query->{"from"}->{"jub"}->{"acqlia"} = { - "type" => "left", "field" => "lineitem", "fkey" => "id", - }; - }; + $or_terms = prepare_terms($or_terms, 0); - my $offset = add_au_joins($query->{"from"}, prepare_au_terms($and_terms)); - add_au_joins($query->{"from"}, prepare_au_terms($or_terms, $offset)); + my $offset = add_au_joins($graft_map, $hint, prepare_au_terms($and_terms)); + add_au_joins($graft_map, $hint, prepare_au_terms($or_terms, $offset)); if ($and_terms and $or_terms) { $query->{"where"} = { diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index 92cb0bf50c..7c03d84a62 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -839,6 +839,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 @@ -864,6 +868,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, @@ -883,6 +889,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/0691.schema.acq_fk_indices.sql b/Open-ILS/src/sql/Pg/upgrade/0691.schema.acq_fk_indices.sql new file mode 100644 index 0000000000..6a04874252 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0691.schema.acq_fk_indices.sql @@ -0,0 +1,15 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0691'); + +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; -- 2.11.0