From 0a0b440d7e2608bdd6c873c1ae6b158a0a2bcb03 Mon Sep 17 00:00:00 2001 From: miker Date: Mon, 27 Sep 2010 19:11:21 +0000 Subject: [PATCH] make pubdate sorting on search faster by pre-munging date1 and date2 into an acceptable sortkey git-svn-id: svn://svn.open-ils.org/ILS/trunk@18039 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../Application/Storage/Driver/Pg/QueryParser.pm | 38 +++++--------------- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 4 +-- .../sql/Pg/upgrade/0420.schema.premunge_dates.sql | 40 ++++++++++++++++++++++ 3 files changed, 50 insertions(+), 32 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0420.schema.premunge_dates.sql diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm index ba596fbff6..157fb88709 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm @@ -449,44 +449,22 @@ sub toSQL { $desc = 'DESC' if ($self->find_modifier('descending')); if ($sort_filter eq 'rel') { # relevance ranking flips sort dir - if ($desc eq 'ASC') { + if ($desc eq 'ASC') { $desc = 'DESC'; } else { $desc = 'ASC'; } } else { if ($sort_filter eq 'title') { - my $default = $desc eq 'DESC' ? ' ' : 'zzzzzz'; - $rank = <<" SQL"; -( COALESCE( FIRST (( - SELECT frt.value - FROM metabib.full_rec frt - WHERE frt.record = m.source - AND frt.tag = 'tnf' - AND frt.subfield = 'a' - LIMIT 1 - )),'$default'))::TEXT - SQL + $rank = "FIRST((SELECT frt.value FROM metabib.full_rec frt WHERE frt.record = m.source AND frt.tag = 'tnf' AND frt.subfield = 'a' LIMIT 1))"; } elsif ($sort_filter eq 'pubdate') { - my $default = $desc eq 'DESC' ? '0' : '99999'; - $rank = "COALESCE( FIRST(NULLIF(LPAD(REGEXP_REPLACE(mrd.date1, E'\\\\D+', '0', 'g'),4,'0'),'0000')), '$default' )::INT"; + $rank = "FIRST(mrd.date1)"; } elsif ($sort_filter eq 'create_date') { - $rank = "( FIRST (( SELECT create_date FROM biblio.record_entry rbr WHERE rbr.id = m.source)) )::TIMESTAMPTZ"; + $rank = "FIRST((SELECT create_date FROM biblio.record_entry rbr WHERE rbr.id = m.source))"; } elsif ($sort_filter eq 'edit_date') { - $rank = "( FIRST (( SELECT edit_date FROM biblio.record_entry rbr WHERE rbr.id = m.source)) )::TIMESTAMPTZ"; + $rank = "FIRST((SELECT edit_date FROM biblio.record_entry rbr WHERE rbr.id = m.source))"; } elsif ($sort_filter eq 'author') { - my $default = $desc eq 'DESC' ? ' ' : 'zzzzzz'; - $rank = <<" SQL" -( COALESCE( FIRST (( - SELECT LTRIM(fra.value) - FROM metabib.full_rec fra - WHERE fra.record = m.source - AND fra.tag LIKE '1%' - AND fra.subfield = 'a' - ORDER BY fra.tag::text::int - LIMIT 1 - )),'$default'))::TEXT - SQL + $rank = "FIRST((SELECT fra.value FROM metabib.full_rec fra WHERE fra.record = m.source AND fra.tag LIKE '1%' AND fra.subfield = 'a' ORDER BY fra.tag LIMIT 1))"; } else { # default to rel ranking $rank = $rel; @@ -532,7 +510,7 @@ SELECT $key AS id, ARRAY_ACCUM(DISTINCT m.source) AS records, $rel AS rel, $rank AS rank, - COALESCE( FIRST(NULLIF(LPAD(REGEXP_REPLACE(mrd.date1, E'\\\\D+', '0', 'g'),4,'0'),'0000')), '0' )::INT AS tie_break + FIRST(mrd.date1) AS tie_break FROM metabib.metarecord_source_map m JOIN metabib.rec_descriptor mrd ON (m.source = mrd.record) $$flat_plan{from} @@ -550,7 +528,7 @@ SELECT $key AS id, $bib_level AND $$flat_plan{where} GROUP BY 1 - ORDER BY 4 $desc, 5 DESC, 3 DESC + ORDER BY 4 $desc NULLS LAST, 5 DESC NULLS LAST, 3 DESC LIMIT $core_limit SQL diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index 8efc3da1e5..35b6df9fac 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -729,8 +729,8 @@ BEGIN JOIN config.marc21_physical_characteristic_subfield_map s ON (s.id = p.subfield) JOIN config.marc21_physical_characteristic_value_map v ON (v.id = p.value) WHERE p.ptype = 'v' AND s.subfield = 'e' ), - biblio.marc21_extract_fixed_field( bib_id, 'Date1'), - biblio.marc21_extract_fixed_field( bib_id, 'Date2'); + LPAD(NULLIF(REGEXP_REPLACE(biblio.marc21_extract_fixed_field( bib_id, 'Date1'), E'\\D', '0', 'g')::INT,0)::TEXT,4,'0'), + LPAD(NULLIF(REGEXP_REPLACE(biblio.marc21_extract_fixed_field( bib_id, 'Date2'), E'\\D', '9', 'g')::INT,9999)::TEXT,4,'0'); RETURN; END; diff --git a/Open-ILS/src/sql/Pg/upgrade/0420.schema.premunge_dates.sql b/Open-ILS/src/sql/Pg/upgrade/0420.schema.premunge_dates.sql new file mode 100644 index 0000000000..56eee3e524 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0420.schema.premunge_dates.sql @@ -0,0 +1,40 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0420'); -- miker + +CREATE OR REPLACE FUNCTION metabib.reingest_metabib_rec_descriptor( bib_id BIGINT ) RETURNS VOID AS $func$ +BEGIN + PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled; + IF NOT FOUND THEN + DELETE FROM metabib.rec_descriptor WHERE record = bib_id; + END IF; + INSERT INTO metabib.rec_descriptor (record, item_type, item_form, bib_level, control_type, enc_level, audience, lit_form, type_mat, cat_form, pub_status, item_lang, vr_format, date1, date2) + SELECT bib_id, + biblio.marc21_extract_fixed_field( bib_id, 'Type' ), + biblio.marc21_extract_fixed_field( bib_id, 'Form' ), + biblio.marc21_extract_fixed_field( bib_id, 'BLvl' ), + biblio.marc21_extract_fixed_field( bib_id, 'Ctrl' ), + biblio.marc21_extract_fixed_field( bib_id, 'ELvl' ), + biblio.marc21_extract_fixed_field( bib_id, 'Audn' ), + biblio.marc21_extract_fixed_field( bib_id, 'LitF' ), + biblio.marc21_extract_fixed_field( bib_id, 'TMat' ), + biblio.marc21_extract_fixed_field( bib_id, 'Desc' ), + biblio.marc21_extract_fixed_field( bib_id, 'DtSt' ), + biblio.marc21_extract_fixed_field( bib_id, 'Lang' ), + ( SELECT v.value + FROM biblio.marc21_physical_characteristics( bib_id) p + JOIN config.marc21_physical_characteristic_subfield_map s ON (s.id = p.subfield) + JOIN config.marc21_physical_characteristic_value_map v ON (v.id = p.value) + WHERE p.ptype = 'v' AND s.subfield = 'e' ), + LPAD(NULLIF(REGEXP_REPLACE(biblio.marc21_extract_fixed_field( bib_id, 'Date1'), E'\\D', '0', 'g')::INT,0)::TEXT,4,'0'), + LPAD(NULLIF(REGEXP_REPLACE(biblio.marc21_extract_fixed_field( bib_id, 'Date2'), E'\\D', '9', 'g')::INT,9999)::TEXT,4,'0'); + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +UPDATE metabib.rec_descriptor + SET date1=LPAD(NULLIF(REGEXP_REPLACE(date1, E'\\D', '0', 'g')::INT,0)::TEXT,4,'0') + date2=LPAD(NULLIF(REGEXP_REPLACE(date2, E'\\D', '9', 'g')::INT,9999)::TEXT,4,'0'); + +COMMIT; -- 2.11.0