From c924ea4250046fb74e360c454961cab6f9f18f6c 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: Jason Stephenson --- Open-ILS/examples/fm_IDL.xml | 136 +++++++++++++++++++++ .../src/perlmods/OpenILS/Application/Acq/Search.pm | 121 ++++++++++-------- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/200.schema.acq.sql | 10 ++ .../sql/Pg/upgrade/0691.schema.acq_fk_indices.sql | 15 +++ 5 files changed, 229 insertions(+), 55 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 8088e35eaf..c91bbd7d77 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -8454,6 +8454,142 @@ SELECT usr, + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Acq/Search.pm b/Open-ILS/src/perlmods/OpenILS/Application/Acq/Search.pm index 6e2d8546ce..cbf4c73c5c 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Acq/Search.pm +++ b/Open-ILS/src/perlmods/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/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 9aa25b7649..371f0cb7e7 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -57,7 +57,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0665'); -- phasefx/miker +INSERT INTO config.upgrade_log (version) VALUES ('0691'); -- senator/miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index e8869a33c5..a00c1d3023 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