1 --Upgrade Script for 2.3 to 2.4.alpha1
2 \set eg_version '''2.4.alpha1'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.4.alpha1', :eg_version);
5 -- remove the Bypass hold capture during clear shelf process setting
7 SELECT evergreen.upgrade_deps_block_check('0739', :eg_version);
10 DELETE FROM actor.org_unit_setting WHERE name = 'circ.holds.clear_shelf.no_capture_holds';
11 DELETE FROM config.org_unit_setting_type_log WHERE field_name = 'circ.holds.clear_shelf.no_capture_holds';
14 DELETE FROM config.org_unit_setting_type WHERE name = 'circ.holds.clear_shelf.no_capture_holds';
17 SELECT evergreen.upgrade_deps_block_check('0741', :eg_version);
19 INSERT INTO permission.perm_list ( id, code, description ) VALUES (
21 'ADMIN_TOOLBAR_FOR_ORG',
24 'Allows a user to create, edit, and delete custom toolbars for org units',
30 'ADMIN_TOOLBAR_FOR_WORKSTATION',
33 'Allows a user to create, edit, and delete custom toolbars for workstations',
39 'ADMIN_TOOLBAR_FOR_USER',
42 'Allows a user to create, edit, and delete custom toolbars for users',
49 -- Evergreen DB patch 0743.schema.remove_tsearch2.sql
51 -- Enable native full-text search to be used, and drop TSearch2 extension
54 -- check whether patch can be applied
55 SELECT evergreen.upgrade_deps_block_check('0743', :eg_version);
57 -- FIXME: add/check SQL statements to perform the upgrade
58 -- First up, these functions depend on metabib.full_rec. They have to go for now.
59 DROP FUNCTION IF EXISTS biblio.flatten_marc(bigint);
60 DROP FUNCTION IF EXISTS biblio.flatten_marc(text);
62 -- These views depend on metabib.full_rec as well. Bye-bye!
63 DROP VIEW IF EXISTS reporter.old_super_simple_record;
64 DROP VIEW IF EXISTS reporter.simple_record;
66 -- Now we can drop metabib.full_rec.
67 DROP VIEW IF EXISTS metabib.full_rec;
69 -- These indexes have to go. BEFORE we alter the tables, otherwise things take extra time when we alter the tables.
70 DROP INDEX metabib.metabib_author_field_entry_value_idx;
71 DROP INDEX metabib.metabib_identifier_field_entry_value_idx;
72 DROP INDEX metabib.metabib_keyword_field_entry_value_idx;
73 DROP INDEX metabib.metabib_series_field_entry_value_idx;
74 DROP INDEX metabib.metabib_subject_field_entry_value_idx;
75 DROP INDEX metabib.metabib_title_field_entry_value_idx;
77 -- Now grab all of the tsvector-enabled columns and switch them to the non-wrapper version of the type.
78 ALTER TABLE authority.full_rec ALTER COLUMN index_vector TYPE pg_catalog.tsvector;
79 ALTER TABLE authority.simple_heading ALTER COLUMN index_vector TYPE pg_catalog.tsvector;
80 ALTER TABLE metabib.real_full_rec ALTER COLUMN index_vector TYPE pg_catalog.tsvector;
81 ALTER TABLE metabib.author_field_entry ALTER COLUMN index_vector TYPE pg_catalog.tsvector;
82 ALTER TABLE metabib.browse_entry ALTER COLUMN index_vector TYPE pg_catalog.tsvector;
83 ALTER TABLE metabib.identifier_field_entry ALTER COLUMN index_vector TYPE pg_catalog.tsvector;
84 ALTER TABLE metabib.keyword_field_entry ALTER COLUMN index_vector TYPE pg_catalog.tsvector;
85 ALTER TABLE metabib.series_field_entry ALTER COLUMN index_vector TYPE pg_catalog.tsvector;
86 ALTER TABLE metabib.subject_field_entry ALTER COLUMN index_vector TYPE pg_catalog.tsvector;
87 ALTER TABLE metabib.title_field_entry ALTER COLUMN index_vector TYPE pg_catalog.tsvector;
89 -- Halfway there! Goodbye tsearch2 extension!
90 DROP EXTENSION tsearch2;
92 -- Next up, re-creating all of the stuff we just dropped.
94 -- Indexes! Note to whomever: Do we even need these anymore?
95 CREATE INDEX metabib_author_field_entry_value_idx ON metabib.author_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;
96 CREATE INDEX metabib_identifier_field_entry_value_idx ON metabib.identifier_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;
97 CREATE INDEX metabib_keyword_field_entry_value_idx ON metabib.keyword_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;
98 CREATE INDEX metabib_series_field_entry_value_idx ON metabib.series_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;
99 CREATE INDEX metabib_subject_field_entry_value_idx ON metabib.subject_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;
100 CREATE INDEX metabib_title_field_entry_value_idx ON metabib.title_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;
102 -- metabib.full_rec, with insert/update/delete rules
103 CREATE OR REPLACE VIEW metabib.full_rec AS
110 SUBSTRING(value,1,1024) AS value,
112 FROM metabib.real_full_rec;
114 CREATE OR REPLACE RULE metabib_full_rec_insert_rule
115 AS ON INSERT TO metabib.full_rec
117 INSERT INTO metabib.real_full_rec VALUES (
118 COALESCE(NEW.id, NEXTVAL('metabib.full_rec_id_seq'::REGCLASS)),
128 CREATE OR REPLACE RULE metabib_full_rec_update_rule
129 AS ON UPDATE TO metabib.full_rec
131 UPDATE metabib.real_full_rec SET
137 subfield = NEW.subfield,
139 index_vector = NEW.index_vector
142 CREATE OR REPLACE RULE metabib_full_rec_delete_rule
143 AS ON DELETE TO metabib.full_rec
145 DELETE FROM metabib.real_full_rec WHERE id = OLD.id;
147 -- reporter views that depended on metabib.full_rec are up next
148 CREATE OR REPLACE VIEW reporter.simple_record AS
155 title.value AS title,
156 uniform_title.value AS uniform_title,
157 author.value AS author,
158 publisher.value AS publisher,
159 SUBSTRING(pubdate.value FROM $$\d+$$) AS pubdate,
160 series_title.value AS series_title,
161 series_statement.value AS series_statement,
162 summary.value AS summary,
163 ARRAY_ACCUM( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn,
164 ARRAY_ACCUM( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn,
165 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '650' AND subfield = 'a' AND record = r.id)) AS topic_subject,
166 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '651' AND subfield = 'a' AND record = r.id)) AS geographic_subject,
167 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '655' AND subfield = 'a' AND record = r.id)) AS genre,
168 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '600' AND subfield = 'a' AND record = r.id)) AS name_subject,
169 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '610' AND subfield = 'a' AND record = r.id)) AS corporate_subject,
170 ARRAY((SELECT value FROM metabib.full_rec WHERE tag = '856' AND subfield IN ('3','y','u') AND record = r.id ORDER BY CASE WHEN subfield IN ('3','y') THEN 0 ELSE 1 END)) AS external_uri
171 FROM biblio.record_entry r
172 JOIN metabib.metarecord_source_map s ON (s.source = r.id)
173 LEFT JOIN metabib.full_rec uniform_title ON (r.id = uniform_title.record AND uniform_title.tag = '240' AND uniform_title.subfield = 'a')
174 LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
175 LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag = '100' AND author.subfield = 'a')
176 LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND publisher.tag = '260' AND publisher.subfield = 'b')
177 LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND pubdate.tag = '260' AND pubdate.subfield = 'c')
178 LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
179 LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
180 LEFT JOIN metabib.full_rec series_title ON (r.id = series_title.record AND series_title.tag IN ('830','440') AND series_title.subfield = 'a')
181 LEFT JOIN metabib.full_rec series_statement ON (r.id = series_statement.record AND series_statement.tag = '490' AND series_statement.subfield = 'a')
182 LEFT JOIN metabib.full_rec summary ON (r.id = summary.record AND summary.tag = '520' AND summary.subfield = 'a')
183 GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;
185 CREATE OR REPLACE VIEW reporter.old_super_simple_record AS
191 FIRST(title.value) AS title,
192 FIRST(author.value) AS author,
193 ARRAY_TO_STRING(ARRAY_ACCUM( DISTINCT publisher.value), ', ') AS publisher,
194 ARRAY_TO_STRING(ARRAY_ACCUM( DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$) ), ', ') AS pubdate,
195 ARRAY_ACCUM( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn,
196 ARRAY_ACCUM( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn
197 FROM biblio.record_entry r
198 LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
199 LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag IN ('100','110','111') AND author.subfield = 'a')
200 LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND publisher.tag = '260' AND publisher.subfield = 'b')
201 LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND pubdate.tag = '260' AND pubdate.subfield = 'c')
202 LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
203 LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
206 -- And finally, the biblio functions. NOTE: I can't find the original source of the second one, so I skipped it as old cruft that was in our production DB.
207 CREATE OR REPLACE FUNCTION biblio.flatten_marc ( rid BIGINT ) RETURNS SETOF metabib.full_rec AS $func$
209 bib biblio.record_entry%ROWTYPE;
210 output metabib.full_rec%ROWTYPE;
213 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
215 FOR field IN SELECT * FROM vandelay.flatten_marc( bib.marc ) LOOP
216 output.record := rid;
217 output.ind1 := field.ind1;
218 output.ind2 := field.ind2;
219 output.tag := field.tag;
220 output.subfield := field.subfield;
221 output.value := field.value;
226 $func$ LANGUAGE PLPGSQL;
228 -- Evergreen DB patch 0745.data.prewarn_expire_setting.sql
230 -- Configuration setting to warn staff when an account is about to expire
233 -- check whether patch can be applied
234 SELECT evergreen.upgrade_deps_block_check('0745', :eg_version);
236 INSERT INTO config.org_unit_setting_type
237 (name, grp, label, description, datatype)
239 'circ.patron_expires_soon_warning',
242 'circ.patron_expires_soon_warning',
243 'Warn when patron account is about to expire',
248 'circ.patron_expires_soon_warning',
249 'Warn when patron account is about to expire. If set, the staff client displays a warning this many days before the expiry of a patron account. Value is in number of days, for example: 3 for 3 days.',
256 -- LP1076399: Prevent reactivated holds from canceling immediately.
257 -- Set the expire_time to NULL on all frozen/suspended holds.
259 SELECT evergreen.upgrade_deps_block_check('0747', :eg_version);
261 UPDATE action.hold_request
262 SET expire_time = NULL
266 SELECT evergreen.upgrade_deps_block_check('0752', :eg_version);
268 INSERT INTO container.biblio_record_entry_bucket_type (code, label) VALUES ('url_verify', 'URL Verification Queue');
270 DROP SCHEMA IF EXISTS url_verify CASCADE;
272 CREATE SCHEMA url_verify;
274 CREATE TABLE url_verify.session (
275 id SERIAL PRIMARY KEY,
277 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
278 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
279 container INT NOT NULL REFERENCES container.biblio_record_entry_bucket (id) DEFERRABLE INITIALLY DEFERRED,
280 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
281 search TEXT NOT NULL,
282 CONSTRAINT uvs_name_once_per_lib UNIQUE (name, owning_lib)
285 CREATE TABLE url_verify.url_selector (
286 id SERIAL PRIMARY KEY,
288 session INT NOT NULL REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
289 CONSTRAINT tag_once_per_sess UNIQUE (xpath, session)
292 CREATE TABLE url_verify.url (
293 id SERIAL PRIMARY KEY,
294 redirect_from INT REFERENCES url_verify.url(id) DEFERRABLE INITIALLY DEFERRED,
295 item INT REFERENCES container.biblio_record_entry_bucket_item (id) DEFERRABLE INITIALLY DEFERRED,
296 url_selector INT REFERENCES url_verify.url_selector (id) DEFERRABLE INITIALLY DEFERRED,
297 session INT REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
301 full_url TEXT NOT NULL,
313 CONSTRAINT redirect_or_from_item CHECK (
314 redirect_from IS NOT NULL OR (
316 url_selector IS NOT NULL AND
318 subfield IS NOT NULL AND
324 CREATE TABLE url_verify.verification_attempt (
325 id SERIAL PRIMARY KEY,
326 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
327 session INT NOT NULL REFERENCES url_verify.session (id) DEFERRABLE INITIALLY DEFERRED,
328 start_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
329 finish_time TIMESTAMP WITH TIME ZONE
332 CREATE TABLE url_verify.url_verification (
333 id SERIAL PRIMARY KEY,
334 url INT NOT NULL REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED,
335 attempt INT NOT NULL REFERENCES url_verify.verification_attempt (id) DEFERRABLE INITIALLY DEFERRED,
336 req_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
337 res_time TIMESTAMP WITH TIME ZONE,
338 res_code INT CHECK (res_code BETWEEN 100 AND 999), -- we know > 599 will never be valid HTTP code, but we use 9XX for other stuff
340 redirect_to INT REFERENCES url_verify.url (id) DEFERRABLE INITIALLY DEFERRED -- if redirected
343 CREATE TABLE config.filter_dialog_interface (
344 key TEXT PRIMARY KEY,
348 CREATE TABLE config.filter_dialog_filter_set (
349 id SERIAL PRIMARY KEY,
351 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
352 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
353 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
354 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
355 filters TEXT NOT NULL CHECK (is_json(filters)),
356 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
360 SELECT evergreen.upgrade_deps_block_check('0753', :eg_version);
362 CREATE OR REPLACE FUNCTION url_verify.parse_url (url_in TEXT) RETURNS url_verify.url AS $$
367 my $url = Rose::URI->new($url_in);
369 my %parts = map { $_ => $url->$_ } qw/scheme username password host port path query fragment/;
371 $parts{full_url} = $url_in;
372 ($parts{domain} = $parts{host}) =~ s/^[^.]+\.//;
373 ($parts{tld} = $parts{domain}) =~ s/(?:[^.]+\.)+//;
374 ($parts{page} = $parts{path}) =~ s#(?:[^/]*/)+##;
380 CREATE OR REPLACE FUNCTION url_verify.ingest_url () RETURNS TRIGGER AS $$
382 tmp_row url_verify.url%ROWTYPE;
384 SELECT * INTO tmp_row FROM url_verify.parse_url(NEW.full_url);
386 NEW.scheme := tmp_row.scheme;
387 NEW.username := tmp_row.username;
388 NEW.password := tmp_row.password;
389 NEW.host := tmp_row.host;
390 NEW.domain := tmp_row.domain;
391 NEW.tld := tmp_row.tld;
392 NEW.port := tmp_row.port;
393 NEW.path := tmp_row.path;
394 NEW.page := tmp_row.page;
395 NEW.query := tmp_row.query;
396 NEW.fragment := tmp_row.fragment;
402 CREATE TRIGGER ingest_url_tgr
403 BEFORE INSERT ON url_verify.url
404 FOR EACH ROW EXECUTE PROCEDURE url_verify.ingest_url();
406 CREATE OR REPLACE FUNCTION url_verify.extract_urls ( session_id INT, item_id INT ) RETURNS INT AS $$
414 current_selector url_verify.url_selector%ROWTYPE;
418 FOR current_selector IN SELECT * FROM url_verify.url_selector s WHERE s.session = session_id LOOP
419 current_url_pos := 1;
421 SELECT (XPATH(current_selector.xpath || '/text()', b.marc::XML))[current_url_pos]::TEXT INTO current_url
422 FROM biblio.record_entry b
423 JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id)
424 WHERE c.id = item_id;
426 EXIT WHEN current_url IS NULL;
428 SELECT (XPATH(current_selector.xpath || '/../@tag', b.marc::XML))[current_url_pos]::TEXT INTO current_tag
429 FROM biblio.record_entry b
430 JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id)
431 WHERE c.id = item_id;
433 IF current_tag IS NULL THEN
434 current_tag := last_seen_tag;
436 last_seen_tag := current_tag;
439 SELECT (XPATH(current_selector.xpath || '/@code', b.marc::XML))[current_url_pos]::TEXT INTO current_sf
440 FROM biblio.record_entry b
441 JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id)
442 WHERE c.id = item_id;
444 INSERT INTO url_verify.url (session, item, url_selector, tag, subfield, ord, full_url)
445 VALUES ( session_id, item_id, current_selector.id, current_tag, current_sf, current_ord, current_url);
447 current_url_pos := current_url_pos + 1;
448 current_ord := current_ord + 1;
452 RETURN current_ord - 1;
458 -- NOTE: beware the use of bare perm IDs in the update_perm's below and in
459 -- the 950 seed data file. Update before merge to match current perm IDs! XXX
462 SELECT evergreen.upgrade_deps_block_check('0754', :eg_version);
464 INSERT INTO permission.perm_list (id, code, description)
470 'Allows a user to process and verify ULSs',
477 INSERT INTO permission.perm_list (id, code, description)
480 'URL_VERIFY_UPDATE_SETTINGS',
483 'Allows a user to configure URL verification org unit settings',
490 INSERT INTO permission.perm_list (id, code, description)
493 'SAVED_FILTER_DIALOG_FILTERS',
496 'Allows users to save and load sets of filters for filter dialogs, available in certain staff interfaces',
503 INSERT INTO config.settings_group (name, label)
514 INSERT INTO config.org_unit_setting_type
515 (name, grp, label, description, datatype, update_perm)
517 'url_verify.url_verification_delay',
520 'url_verify.url_verification_delay',
521 'Number of seconds to wait between URL test attempts.',
526 'url_verify.url_verification_delay',
527 'Throttling mechanism for batch URL verification runs. Each running process will wait this number of seconds after a URL test before performing the next.',
535 INSERT INTO config.org_unit_setting_type
536 (name, grp, label, description, datatype, update_perm)
538 'url_verify.url_verification_max_redirects',
541 'url_verify.url_verification_max_redirects',
542 'Maximum redirect lookups',
547 'url_verify.url_verification_max_redirects',
548 'For URLs returning 3XX redirects, this is the maximum number of redirects we will follow before giving up.',
556 INSERT INTO config.org_unit_setting_type
557 (name, grp, label, description, datatype, update_perm)
559 'url_verify.url_verification_max_wait',
562 'url_verify.url_verification_max_wait',
563 'Maximum wait time (in seconds) for a URL to lookup',
568 'url_verify.url_verification_max_wait',
569 'If we exceed the wait time, the URL is marked as a "timeout" and the system moves on to the next URL',
578 INSERT INTO config.org_unit_setting_type
579 (name, grp, label, description, datatype, update_perm)
581 'url_verify.verification_batch_size',
584 'url_verify.verification_batch_size',
585 'Number of URLs to test in parallel',
590 'url_verify.verification_batch_size',
591 'URLs are tested in batches. This number defines the size of each batch and it directly relates to the number of back-end processes performing URL verification.',
600 INSERT INTO config.filter_dialog_interface (key, description) VALUES (
604 'All Link Checker filter dialogs',
611 INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype) VALUES (
612 'ui.grid_columns.url_verify.select_urls',
616 'ui.grid_columns.url_verify.select_urls',
617 'Link Checker''s URL Selection interface''s saved columns',
622 'ui.grid_columns.url_verify.select_urls',
623 'Link Checker''s URL Selection interface''s saved columns',
630 INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype) VALUES (
631 'ui.grid_columns.url_verify.review_attempt',
635 'ui.grid_columns.url_verify.review_attempt',
636 'Link Checker''s Review Attempt interface''s saved columns',
641 'ui.grid_columns.url_verify.review_attempt',
642 'Link Checker''s Review Attempt interface''s saved columns',
652 SELECT evergreen.upgrade_deps_block_check('0755', :eg_version);
654 INSERT INTO config.org_unit_setting_type
655 (name, label, description, grp, datatype, fm_class) VALUES
657 'acq.upload.default.create_po',
659 'acq.upload.default.create_po',
665 'acq.upload.default.create_po',
666 'Create a purchase order by default during ACQ file upload',
674 'acq.upload.default.activate_po',
676 'acq.upload.default.activate_po',
677 'Upload Activate PO',
682 'acq.upload.default.activate_po',
683 'Activate the purchase order by default during ACQ file upload',
691 'acq.upload.default.provider',
693 'acq.upload.default.provider',
694 'Upload Default Provider',
699 'acq.upload.default.provider',
700 'Default provider to use during ACQ file upload',
708 'acq.upload.default.vandelay.match_set',
710 'acq.upload.default.vandelay.match_set',
711 'Upload Default Match Set',
716 'acq.upload.default.vandelay.match_set',
717 'Default match set to use during ACQ file upload',
725 'acq.upload.default.vandelay.merge_profile',
727 'acq.upload.default.vandelay.merge_profile',
728 'Upload Default Merge Profile',
733 'acq.upload.default.vandelay.merge_profile',
734 'Default merge profile to use during ACQ file upload',
742 'acq.upload.default.vandelay.import_non_matching',
744 'acq.upload.default.vandelay.import_non_matching',
745 'Upload Import Non Matching by Default',
750 'acq.upload.default.vandelay.import_non_matching',
751 'Import non-matching records by default during ACQ file upload',
759 'acq.upload.default.vandelay.merge_on_exact',
761 'acq.upload.default.vandelay.merge_on_exact',
762 'Upload Merge on Exact Match by Default',
767 'acq.upload.default.vandelay.merge_on_exact',
768 'Merge records on exact match by default during ACQ file upload',
776 'acq.upload.default.vandelay.merge_on_best',
778 'acq.upload.default.vandelay.merge_on_best',
779 'Upload Merge on Best Match by Default',
784 'acq.upload.default.vandelay.merge_on_best',
785 'Merge records on best match by default during ACQ file upload',
793 'acq.upload.default.vandelay.merge_on_single',
795 'acq.upload.default.vandelay.merge_on_single',
796 'Upload Merge on Single Match by Default',
801 'acq.upload.default.vandelay.merge_on_single',
802 'Merge records on single match by default during ACQ file upload',
810 'acq.upload.default.vandelay.quality_ratio',
812 'acq.upload.default.vandelay.quality_ratio',
813 'Upload Default Min. Quality Ratio',
818 'acq.upload.default.vandelay.quality_ratio',
819 'Default minimum quality ratio used during ACQ file upload',
827 'acq.upload.default.vandelay.low_quality_fall_thru_profile',
829 'acq.upload.default.vandelay.low_quality_fall_thru_profile',
830 'Upload Default Insufficient Quality Fall-Thru Profile',
835 'acq.upload.default.vandelay.low_quality_fall_thru_profile',
836 'Default low-quality fall through profile used during ACQ file upload',
844 'acq.upload.default.vandelay.load_item_for_imported',
846 'acq.upload.default.vandelay.load_item_for_imported',
847 'Upload Load Items for Imported Records by Default',
852 'acq.upload.default.vandelay.load_item_for_imported',
853 'Load items for imported records by default during ACQ file upload',
863 SELECT evergreen.upgrade_deps_block_check('0756', :eg_version);
865 -- Drop some lingering old functions in search schema
866 DROP FUNCTION IF EXISTS search.staged_fts(INT,INT,TEXT,INT[],INT[],TEXT[],TEXT[],TEXT[],TEXT[],TEXT[],TEXT[],TEXT[],TEXT,TEXT,TEXT,TEXT[],TEXT,REAL,TEXT,BOOL,BOOL,BOOL,INT,INT,INT);
867 DROP FUNCTION IF EXISTS search.parse_search_args(TEXT);
868 DROP FUNCTION IF EXISTS search.explode_array(ANYARRAY);
869 DROP FUNCTION IF EXISTS search.pick_table(TEXT);
871 -- Now drop query_parser_fts and related
872 DROP FUNCTION IF EXISTS search.query_parser_fts(INT,INT,TEXT,INT[],INT[],INT,INT,INT,BOOL,BOOL,INT);
873 DROP TYPE IF EXISTS search.search_result;
874 DROP TYPE IF EXISTS search.search_args;
877 SELECT evergreen.upgrade_deps_block_check('0757', :eg_version);
879 SET search_path = public, pg_catalog;
885 FOR lang IN SELECT substring(pptsd.dictname from '(.*)_stem$') AS lang FROM pg_catalog.pg_ts_dict pptsd JOIN pg_catalog.pg_namespace ppn ON ppn.oid = pptsd.dictnamespace
886 WHERE ppn.nspname = 'pg_catalog' AND pptsd.dictname LIKE '%_stem' LOOP
887 RAISE NOTICE 'FOUND LANGUAGE %', lang;
889 EXECUTE 'DROP TEXT SEARCH DICTIONARY IF EXISTS ' || lang || '_nostop CASCADE;
890 CREATE TEXT SEARCH DICTIONARY ' || lang || '_nostop (TEMPLATE=pg_catalog.snowball, language=''' || lang || ''');
891 COMMENT ON TEXT SEARCH DICTIONARY ' || lang || '_nostop IS ''' ||lang || ' snowball stemmer with no stopwords for ASCII words only.'';
892 CREATE TEXT SEARCH CONFIGURATION ' || lang || '_nostop ( COPY = pg_catalog.' || lang || ' );
893 ALTER TEXT SEARCH CONFIGURATION ' || lang || '_nostop ALTER MAPPING FOR word, hword, hword_part WITH pg_catalog.simple;
894 ALTER TEXT SEARCH CONFIGURATION ' || lang || '_nostop ALTER MAPPING FOR asciiword, asciihword, hword_asciipart WITH ' || lang || '_nostop;';
899 CREATE TEXT SEARCH CONFIGURATION keyword ( COPY = english_nostop );
900 CREATE TEXT SEARCH CONFIGURATION "default" ( COPY = english_nostop );
902 SET search_path = evergreen, public, pg_catalog;
904 ALTER TABLE config.metabib_class
905 ADD COLUMN a_weight NUMERIC DEFAULT 1.0 NOT NULL,
906 ADD COLUMN b_weight NUMERIC DEFAULT 0.4 NOT NULL,
907 ADD COLUMN c_weight NUMERIC DEFAULT 0.2 NOT NULL,
908 ADD COLUMN d_weight NUMERIC DEFAULT 0.1 NOT NULL;
910 CREATE TABLE config.ts_config_list (
914 COMMENT ON TABLE config.ts_config_list IS $$
917 A list of full text configs with names and descriptions.
920 CREATE TABLE config.metabib_class_ts_map (
921 id SERIAL PRIMARY KEY,
922 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
923 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
924 active BOOL NOT NULL DEFAULT TRUE,
925 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
926 index_lang TEXT NULL,
927 search_lang TEXT NULL,
928 always BOOL NOT NULL DEFAULT true
930 COMMENT ON TABLE config.metabib_class_ts_map IS $$
931 Text Search Configs for metabib class indexing
933 This table contains text search config definitions for
934 storing index_vector values.
937 CREATE TABLE config.metabib_field_ts_map (
938 id SERIAL PRIMARY KEY,
939 metabib_field INT NOT NULL REFERENCES config.metabib_field (id),
940 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
941 active BOOL NOT NULL DEFAULT TRUE,
942 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
943 index_lang TEXT NULL,
944 search_lang TEXT NULL
946 COMMENT ON TABLE config.metabib_field_ts_map IS $$
947 Text Search Configs for metabib field indexing
949 This table contains text search config definitions for
950 storing index_vector values.
953 CREATE TABLE metabib.combined_identifier_field_entry (
954 record BIGINT NOT NULL,
955 metabib_field INT NULL,
956 index_vector tsvector NOT NULL
958 CREATE UNIQUE INDEX metabib_combined_identifier_field_entry_fakepk_idx ON metabib.combined_identifier_field_entry (record, COALESCE(metabib_field::TEXT,''));
959 CREATE INDEX metabib_combined_identifier_field_entry_index_vector_idx ON metabib.combined_identifier_field_entry USING GIST (index_vector);
960 CREATE INDEX metabib_combined_identifier_field_source_idx ON metabib.combined_identifier_field_entry (metabib_field);
962 CREATE TABLE metabib.combined_title_field_entry (
963 record BIGINT NOT NULL,
964 metabib_field INT NULL,
965 index_vector tsvector NOT NULL
967 CREATE UNIQUE INDEX metabib_combined_title_field_entry_fakepk_idx ON metabib.combined_title_field_entry (record, COALESCE(metabib_field::TEXT,''));
968 CREATE INDEX metabib_combined_title_field_entry_index_vector_idx ON metabib.combined_title_field_entry USING GIST (index_vector);
969 CREATE INDEX metabib_combined_title_field_source_idx ON metabib.combined_title_field_entry (metabib_field);
971 CREATE TABLE metabib.combined_author_field_entry (
972 record BIGINT NOT NULL,
973 metabib_field INT NULL,
974 index_vector tsvector NOT NULL
976 CREATE UNIQUE INDEX metabib_combined_author_field_entry_fakepk_idx ON metabib.combined_author_field_entry (record, COALESCE(metabib_field::TEXT,''));
977 CREATE INDEX metabib_combined_author_field_entry_index_vector_idx ON metabib.combined_author_field_entry USING GIST (index_vector);
978 CREATE INDEX metabib_combined_author_field_source_idx ON metabib.combined_author_field_entry (metabib_field);
980 CREATE TABLE metabib.combined_subject_field_entry (
981 record BIGINT NOT NULL,
982 metabib_field INT NULL,
983 index_vector tsvector NOT NULL
985 CREATE UNIQUE INDEX metabib_combined_subject_field_entry_fakepk_idx ON metabib.combined_subject_field_entry (record, COALESCE(metabib_field::TEXT,''));
986 CREATE INDEX metabib_combined_subject_field_entry_index_vector_idx ON metabib.combined_subject_field_entry USING GIST (index_vector);
987 CREATE INDEX metabib_combined_subject_field_source_idx ON metabib.combined_subject_field_entry (metabib_field);
989 CREATE TABLE metabib.combined_keyword_field_entry (
990 record BIGINT NOT NULL,
991 metabib_field INT NULL,
992 index_vector tsvector NOT NULL
994 CREATE UNIQUE INDEX metabib_combined_keyword_field_entry_fakepk_idx ON metabib.combined_keyword_field_entry (record, COALESCE(metabib_field::TEXT,''));
995 CREATE INDEX metabib_combined_keyword_field_entry_index_vector_idx ON metabib.combined_keyword_field_entry USING GIST (index_vector);
996 CREATE INDEX metabib_combined_keyword_field_source_idx ON metabib.combined_keyword_field_entry (metabib_field);
998 CREATE TABLE metabib.combined_series_field_entry (
999 record BIGINT NOT NULL,
1000 metabib_field INT NULL,
1001 index_vector tsvector NOT NULL
1003 CREATE UNIQUE INDEX metabib_combined_series_field_entry_fakepk_idx ON metabib.combined_series_field_entry (record, COALESCE(metabib_field::TEXT,''));
1004 CREATE INDEX metabib_combined_series_field_entry_index_vector_idx ON metabib.combined_series_field_entry USING GIST (index_vector);
1005 CREATE INDEX metabib_combined_series_field_source_idx ON metabib.combined_series_field_entry (metabib_field);
1007 CREATE OR REPLACE FUNCTION metabib.update_combined_index_vectors(bib_id BIGINT) RETURNS VOID AS $func$
1009 DELETE FROM metabib.combined_keyword_field_entry WHERE record = bib_id;
1010 INSERT INTO metabib.combined_keyword_field_entry(record, metabib_field, index_vector)
1011 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1012 FROM metabib.keyword_field_entry WHERE source = bib_id GROUP BY field;
1013 INSERT INTO metabib.combined_keyword_field_entry(record, metabib_field, index_vector)
1014 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1015 FROM metabib.keyword_field_entry WHERE source = bib_id;
1017 DELETE FROM metabib.combined_title_field_entry WHERE record = bib_id;
1018 INSERT INTO metabib.combined_title_field_entry(record, metabib_field, index_vector)
1019 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1020 FROM metabib.title_field_entry WHERE source = bib_id GROUP BY field;
1021 INSERT INTO metabib.combined_title_field_entry(record, metabib_field, index_vector)
1022 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1023 FROM metabib.title_field_entry WHERE source = bib_id;
1025 DELETE FROM metabib.combined_author_field_entry WHERE record = bib_id;
1026 INSERT INTO metabib.combined_author_field_entry(record, metabib_field, index_vector)
1027 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1028 FROM metabib.author_field_entry WHERE source = bib_id GROUP BY field;
1029 INSERT INTO metabib.combined_author_field_entry(record, metabib_field, index_vector)
1030 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1031 FROM metabib.author_field_entry WHERE source = bib_id;
1033 DELETE FROM metabib.combined_subject_field_entry WHERE record = bib_id;
1034 INSERT INTO metabib.combined_subject_field_entry(record, metabib_field, index_vector)
1035 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1036 FROM metabib.subject_field_entry WHERE source = bib_id GROUP BY field;
1037 INSERT INTO metabib.combined_subject_field_entry(record, metabib_field, index_vector)
1038 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1039 FROM metabib.subject_field_entry WHERE source = bib_id;
1041 DELETE FROM metabib.combined_series_field_entry WHERE record = bib_id;
1042 INSERT INTO metabib.combined_series_field_entry(record, metabib_field, index_vector)
1043 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1044 FROM metabib.series_field_entry WHERE source = bib_id GROUP BY field;
1045 INSERT INTO metabib.combined_series_field_entry(record, metabib_field, index_vector)
1046 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1047 FROM metabib.series_field_entry WHERE source = bib_id;
1049 DELETE FROM metabib.combined_identifier_field_entry WHERE record = bib_id;
1050 INSERT INTO metabib.combined_identifier_field_entry(record, metabib_field, index_vector)
1051 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1052 FROM metabib.identifier_field_entry WHERE source = bib_id GROUP BY field;
1053 INSERT INTO metabib.combined_identifier_field_entry(record, metabib_field, index_vector)
1054 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1055 FROM metabib.identifier_field_entry WHERE source = bib_id;
1058 $func$ LANGUAGE PLPGSQL;
1060 CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries( bib_id BIGINT, skip_facet BOOL DEFAULT FALSE, skip_browse BOOL DEFAULT FALSE, skip_search BOOL DEFAULT FALSE ) RETURNS VOID AS $func$
1063 ind_data metabib.field_entry_template%ROWTYPE;
1064 mbe_row metabib.browse_entry%ROWTYPE;
1067 PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled;
1069 IF NOT skip_search THEN
1070 FOR fclass IN SELECT * FROM config.metabib_class LOOP
1071 -- RAISE NOTICE 'Emptying out %', fclass.name;
1072 EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id;
1075 IF NOT skip_facet THEN
1076 DELETE FROM metabib.facet_entry WHERE source = bib_id;
1078 IF NOT skip_browse THEN
1079 DELETE FROM metabib.browse_entry_def_map WHERE source = bib_id;
1083 FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id ) LOOP
1084 IF ind_data.field < 0 THEN
1085 ind_data.field = -1 * ind_data.field;
1088 IF ind_data.facet_field AND NOT skip_facet THEN
1089 INSERT INTO metabib.facet_entry (field, source, value)
1090 VALUES (ind_data.field, ind_data.source, ind_data.value);
1093 IF ind_data.browse_field AND NOT skip_browse THEN
1094 -- A caveat about this SELECT: this should take care of replacing
1095 -- old mbe rows when data changes, but not if normalization (by
1096 -- which I mean specifically the output of
1097 -- evergreen.oils_tsearch2()) changes. It may or may not be
1098 -- expensive to add a comparison of index_vector to index_vector
1099 -- to the WHERE clause below.
1100 SELECT INTO mbe_row * FROM metabib.browse_entry WHERE value = ind_data.value;
1102 mbe_id := mbe_row.id;
1104 INSERT INTO metabib.browse_entry (value) VALUES
1105 (metabib.browse_normalize(ind_data.value, ind_data.field));
1106 mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
1109 INSERT INTO metabib.browse_entry_def_map (entry, def, source)
1110 VALUES (mbe_id, ind_data.field, ind_data.source);
1113 IF ind_data.search_field AND NOT skip_search THEN
1115 INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value)
1117 quote_literal(ind_data.field) || $$, $$ ||
1118 quote_literal(ind_data.source) || $$, $$ ||
1119 quote_literal(ind_data.value) ||
1125 IF NOT skip_search THEN
1126 PERFORM metabib.update_combined_index_vectors(bib_id);
1131 $func$ LANGUAGE PLPGSQL;
1133 DROP FUNCTION IF EXISTS evergreen.oils_tsearch2() CASCADE;
1134 DROP FUNCTION IF EXISTS public.oils_tsearch2() CASCADE;
1136 CREATE OR REPLACE FUNCTION public.oils_tsearch2 () RETURNS TRIGGER AS $$
1140 temp_vector TEXT := '';
1145 NEW.index_vector = ''::tsvector;
1147 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
1149 SELECT n.func AS func,
1150 n.param_count AS param_count,
1152 FROM config.index_normalizer n
1153 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
1154 WHERE field = NEW.field
1156 EXECUTE 'SELECT ' || normalizer.func || '(' ||
1157 quote_literal( value ) ||
1159 WHEN normalizer.param_count > 0
1160 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
1169 IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN
1170 value := ARRAY_TO_STRING(
1171 evergreen.regexp_split_to_array(value, E'\\W+'), ' '
1173 value := public.search_normalize(value);
1174 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
1175 ELSIF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
1177 SELECT ts_config, index_weight
1178 FROM config.metabib_class_ts_map
1179 WHERE field_class = TG_ARGV[0]
1180 AND index_lang IS NULL OR EXISTS (SELECT 1 FROM metabib.record_attr WHERE id = NEW.source AND index_lang IN(attrs->'item_lang',attrs->'language'))
1181 AND always OR NOT EXISTS (SELECT 1 FROM config.metabib_field_ts_map WHERE metabib_field = NEW.field)
1183 SELECT ts_config, index_weight
1184 FROM config.metabib_field_ts_map
1185 WHERE metabib_field = NEW.field
1186 AND index_lang IS NULL OR EXISTS (SELECT 1 FROM metabib.record_attr WHERE id = NEW.source AND index_lang IN(attrs->'item_lang',attrs->'language'))
1187 ORDER BY index_weight ASC
1189 IF cur_weight IS NOT NULL AND cur_weight != ts_rec.index_weight THEN
1190 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
1193 cur_weight = ts_rec.index_weight;
1194 SELECT INTO temp_vector temp_vector || ' ' || to_tsvector(ts_rec.ts_config::regconfig, value)::TEXT;
1196 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
1198 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
1203 $$ LANGUAGE PLPGSQL;
1205 CREATE TRIGGER authority_full_rec_fti_trigger
1206 BEFORE UPDATE OR INSERT ON authority.full_rec
1207 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
1209 CREATE TRIGGER authority_simple_heading_fti_trigger
1210 BEFORE UPDATE OR INSERT ON authority.simple_heading
1211 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
1213 CREATE TRIGGER metabib_identifier_field_entry_fti_trigger
1214 BEFORE UPDATE OR INSERT ON metabib.identifier_field_entry
1215 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('identifier');
1217 CREATE TRIGGER metabib_title_field_entry_fti_trigger
1218 BEFORE UPDATE OR INSERT ON metabib.title_field_entry
1219 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('title');
1221 CREATE TRIGGER metabib_author_field_entry_fti_trigger
1222 BEFORE UPDATE OR INSERT ON metabib.author_field_entry
1223 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('author');
1225 CREATE TRIGGER metabib_subject_field_entry_fti_trigger
1226 BEFORE UPDATE OR INSERT ON metabib.subject_field_entry
1227 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('subject');
1229 CREATE TRIGGER metabib_keyword_field_entry_fti_trigger
1230 BEFORE UPDATE OR INSERT ON metabib.keyword_field_entry
1231 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
1233 CREATE TRIGGER metabib_series_field_entry_fti_trigger
1234 BEFORE UPDATE OR INSERT ON metabib.series_field_entry
1235 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('series');
1237 CREATE TRIGGER metabib_browse_entry_fti_trigger
1238 BEFORE INSERT OR UPDATE ON metabib.browse_entry
1239 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
1241 CREATE TRIGGER metabib_full_rec_fti_trigger
1242 BEFORE UPDATE OR INSERT ON metabib.real_full_rec
1243 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('default');
1245 INSERT INTO config.ts_config_list(id, name) VALUES
1246 ('simple','Non-Stemmed Simple'),
1247 ('danish_nostop','Danish Stemmed'),
1248 ('dutch_nostop','Dutch Stemmed'),
1249 ('english_nostop','English Stemmed'),
1250 ('finnish_nostop','Finnish Stemmed'),
1251 ('french_nostop','French Stemmed'),
1252 ('german_nostop','German Stemmed'),
1253 ('hungarian_nostop','Hungarian Stemmed'),
1254 ('italian_nostop','Italian Stemmed'),
1255 ('norwegian_nostop','Norwegian Stemmed'),
1256 ('portuguese_nostop','Portuguese Stemmed'),
1257 ('romanian_nostop','Romanian Stemmed'),
1258 ('russian_nostop','Russian Stemmed'),
1259 ('spanish_nostop','Spanish Stemmed'),
1260 ('swedish_nostop','Swedish Stemmed'),
1261 ('turkish_nostop','Turkish Stemmed');
1263 INSERT INTO config.metabib_class_ts_map(field_class, ts_config, index_weight, always) VALUES
1264 ('keyword','simple','A',true),
1265 ('keyword','english_nostop','C',true),
1266 ('title','simple','A',true),
1267 ('title','english_nostop','C',true),
1268 ('author','simple','A',true),
1269 ('author','english_nostop','C',true),
1270 ('series','simple','A',true),
1271 ('series','english_nostop','C',true),
1272 ('subject','simple','A',true),
1273 ('subject','english_nostop','C',true),
1274 ('identifier','simple','A',true);
1276 CREATE OR REPLACE FUNCTION evergreen.rel_bump(terms TEXT[], value TEXT, bumps TEXT[], mults NUMERIC[]) RETURNS NUMERIC AS
1279 my ($terms,$value,$bumps,$mults) = @_;
1283 for (my $id = 0; $id < @$bumps; $id++) {
1284 if ($bumps->[$id] eq 'first_word') {
1285 $retval *= $mults->[$id] if ($value =~ /^$terms->[0]/);
1286 } elsif ($bumps->[$id] eq 'full_match') {
1287 my $fullmatch = join(' ', @$terms);
1288 $retval *= $mults->[$id] if ($value =~ /^$fullmatch$/);
1289 } elsif ($bumps->[$id] eq 'word_order') {
1290 my $wordorder = join('.*', @$terms);
1291 $retval *= $mults->[$id] if ($value =~ /$wordorder/);
1295 $BODY$ LANGUAGE plperlu IMMUTABLE STRICT COST 100;
1297 UPDATE metabib.identifier_field_entry set value = value;
1298 UPDATE metabib.title_field_entry set value = value;
1299 UPDATE metabib.author_field_entry set value = value;
1300 UPDATE metabib.subject_field_entry set value = value;
1301 UPDATE metabib.keyword_field_entry set value = value;
1302 UPDATE metabib.series_field_entry set value = value;
1304 SELECT metabib.update_combined_index_vectors(id)
1305 FROM biblio.record_entry
1309 SELECT evergreen.upgrade_deps_block_check('0758', :eg_version);
1311 INSERT INTO config.settings_group (name, label) VALUES
1312 ('vandelay', 'Vandelay');
1314 INSERT INTO config.org_unit_setting_type (name, grp, label, datatype, fm_class) VALUES
1315 ('vandelay.default_match_set', 'vandelay', 'Default Record Match Set', 'link', 'vms');
1318 SELECT evergreen.upgrade_deps_block_check('0759', :eg_version);
1320 CREATE TABLE actor.org_unit_proximity_adjustment (
1321 id SERIAL PRIMARY KEY,
1322 item_circ_lib INT REFERENCES actor.org_unit (id),
1323 item_owning_lib INT REFERENCES actor.org_unit (id),
1324 copy_location INT REFERENCES asset.copy_location (id),
1325 hold_pickup_lib INT REFERENCES actor.org_unit (id),
1326 hold_request_lib INT REFERENCES actor.org_unit (id),
1327 pos INT NOT NULL DEFAULT 0,
1328 absolute_adjustment BOOL NOT NULL DEFAULT FALSE,
1329 prox_adjustment NUMERIC,
1330 circ_mod TEXT, -- REFERENCES config.circ_modifier (code),
1331 CONSTRAINT prox_adj_criterium CHECK (COALESCE(item_circ_lib::TEXT,item_owning_lib::TEXT,copy_location::TEXT,hold_pickup_lib::TEXT,hold_request_lib::TEXT,circ_mod) IS NOT NULL)
1333 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (item_circ_lib,item_owning_lib,copy_location,hold_pickup_lib,hold_request_lib,circ_mod);
1334 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
1335 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
1336 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
1337 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
1338 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
1339 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
1341 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
1342 WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS (
1345 SELECT ou.parent_ou, ouad.distance+1
1346 FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id)
1347 WHERE ou.parent_ou IS NOT NULL
1349 SELECT * FROM org_unit_ancestors_distance;
1350 $$ LANGUAGE SQL STABLE ROWS 1;
1352 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
1355 copy_context_ou INT DEFAULT NULL
1356 -- TODO maybe? hold_context_ou INT DEFAULT NULL. This would optionally
1357 -- support an "ahprox" measurement: adjust prox between copy circ lib and
1358 -- hold request lib, but I'm unsure whether to use this theoretical
1359 -- argument only in the baseline calculation or later in the other
1360 -- queries in this function.
1361 ) RETURNS NUMERIC AS $f$
1363 aoupa actor.org_unit_proximity_adjustment%ROWTYPE;
1364 ahr action.hold_request%ROWTYPE;
1365 acp asset.copy%ROWTYPE;
1366 acn asset.call_number%ROWTYPE;
1367 acl asset.copy_location%ROWTYPE;
1368 baseline_prox NUMERIC;
1378 SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
1379 SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
1380 SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
1381 SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
1383 IF copy_context_ou IS NULL THEN
1384 copy_context_ou := acp.circ_lib;
1387 -- First, gather the baseline proximity of "here" to pickup lib
1388 SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib;
1390 -- Find any absolute adjustments, and set the baseline prox to that
1391 SELECT adj.* INTO aoupa
1392 FROM actor.org_unit_proximity_adjustment adj
1393 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1394 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1395 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
1396 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1397 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1398 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1399 absolute_adjustment AND
1400 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1402 COALESCE(acp_cl.distance,999)
1403 + COALESCE(acn_ol.distance,999)
1404 + COALESCE(acl_ol.distance,999)
1405 + COALESCE(ahr_pl.distance,999)
1406 + COALESCE(ahr_rl.distance,999),
1411 baseline_prox := aoupa.prox_adjustment;
1414 -- Now find any relative adjustments, and change the baseline prox based on them
1417 FROM actor.org_unit_proximity_adjustment adj
1418 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1419 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1420 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
1421 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1422 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1423 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1424 NOT absolute_adjustment AND
1425 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1427 baseline_prox := baseline_prox + aoupa.prox_adjustment;
1430 RETURN baseline_prox;
1432 $f$ LANGUAGE PLPGSQL;
1434 ALTER TABLE actor.org_unit_proximity_adjustment
1435 ADD CONSTRAINT actor_org_unit_proximity_adjustment_circ_mod_fkey
1436 FOREIGN KEY (circ_mod) REFERENCES config.circ_modifier (code)
1437 DEFERRABLE INITIALLY DEFERRED;
1439 ALTER TABLE action.hold_copy_map ADD COLUMN proximity NUMERIC;
1442 SELECT evergreen.upgrade_deps_block_check('0760', :eg_version);
1444 CREATE TABLE config.best_hold_order(
1445 id SERIAL PRIMARY KEY, -- (metadata)
1446 name TEXT UNIQUE, -- i18n (metadata)
1447 pprox INT, -- copy capture <-> pickup lib prox
1448 hprox INT, -- copy circ lib <-> request lib prox
1449 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1450 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1451 priority INT, -- group hold priority
1452 cut INT, -- cut-in-line
1453 depth INT, -- selection depth
1454 htime INT, -- time since last home-lib circ exceeds org-unit setting
1455 rtime INT, -- request time
1456 shtime INT -- time since copy last trip home exceeds org-unit setting
1459 -- At least one of these columns must contain a non-null value
1460 ALTER TABLE config.best_hold_order ADD CHECK ((
1461 pprox IS NOT NULL OR
1462 hprox IS NOT NULL OR
1463 aprox IS NOT NULL OR
1464 priority IS NOT NULL OR
1466 depth IS NOT NULL OR
1467 htime IS NOT NULL OR
1471 INSERT INTO config.best_hold_order (
1473 pprox, aprox, priority, cut, depth, rtime, htime, hprox
1476 1, 2, 3, 4, 5, 6, 7, 8
1479 INSERT INTO config.best_hold_order (
1481 hprox, pprox, aprox, priority, cut, depth, rtime, htime
1483 'Traditional with Holds-always-go-home',
1484 1, 2, 3, 4, 5, 6, 7, 8
1487 INSERT INTO config.best_hold_order (
1489 htime, hprox, pprox, aprox, priority, cut, depth, rtime
1491 'Traditional with Holds-go-home',
1492 1, 2, 3, 4, 5, 6, 7, 8
1495 INSERT INTO config.best_hold_order (
1497 priority, cut, rtime, depth, pprox, hprox, aprox, htime
1500 1, 2, 3, 4, 5, 6, 7, 8
1503 INSERT INTO config.best_hold_order (
1505 hprox, priority, cut, rtime, depth, pprox, aprox, htime
1507 'FIFO with Holds-always-go-home',
1508 1, 2, 3, 4, 5, 6, 7, 8
1511 INSERT INTO config.best_hold_order (
1513 htime, priority, cut, rtime, depth, pprox, aprox, hprox
1515 'FIFO with Holds-go-home',
1516 1, 2, 3, 4, 5, 6, 7, 8
1519 INSERT INTO permission.perm_list (
1520 id, code, description
1523 'ADMIN_HOLD_CAPTURE_SORT',
1526 'Allows a user to make changes to best-hold selection sort order',
1532 INSERT INTO config.org_unit_setting_type (
1533 name, label, description, datatype, fm_class, update_perm, grp
1535 'circ.hold_capture_order',
1537 'circ.hold_capture_order',
1538 'Best-hold selection sort order',
1543 'circ.hold_capture_order',
1544 'Defines the sort order of holds when selecting a hold to fill using a given copy at capture time',
1554 INSERT INTO config.org_unit_setting_type (
1555 name, label, description, datatype, update_perm, grp
1557 'circ.hold_go_home_interval',
1559 'circ.hold_go_home_interval',
1560 'Max foreign-circulation time',
1565 'circ.hold_go_home_interval',
1566 'Time a copy can spend circulating away from its circ lib before returning there to fill a hold (if one exists there)',
1575 INSERT INTO actor.org_unit_setting (
1576 org_unit, name, value
1578 (SELECT id FROM actor.org_unit WHERE parent_ou IS NULL),
1579 'circ.hold_go_home_interval',
1583 UPDATE actor.org_unit_setting SET
1584 name = 'circ.hold_capture_order',
1585 value = (SELECT id FROM config.best_hold_order WHERE name = 'FIFO')
1587 name = 'circ.holds_fifo' AND value ILIKE '%true%';
1590 SELECT evergreen.upgrade_deps_block_check('0762', :eg_version);
1592 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_browse_indexing');
1593 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_search_indexing');
1594 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_facet_indexing');
1596 CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries( bib_id BIGINT, skip_facet BOOL DEFAULT FALSE, skip_browse BOOL DEFAULT FALSE, skip_search BOOL DEFAULT FALSE ) RETURNS VOID AS $func$
1599 ind_data metabib.field_entry_template%ROWTYPE;
1600 mbe_row metabib.browse_entry%ROWTYPE;
1607 SELECT COALESCE(NULLIF(skip_facet, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_facet_indexing' AND enabled)) INTO b_skip_facet;
1608 SELECT COALESCE(NULLIF(skip_browse, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_browse_indexing' AND enabled)) INTO b_skip_browse;
1609 SELECT COALESCE(NULLIF(skip_search, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_search_indexing' AND enabled)) INTO b_skip_search;
1611 PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled;
1613 IF NOT b_skip_search THEN
1614 FOR fclass IN SELECT * FROM config.metabib_class LOOP
1615 -- RAISE NOTICE 'Emptying out %', fclass.name;
1616 EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id;
1619 IF NOT b_skip_facet THEN
1620 DELETE FROM metabib.facet_entry WHERE source = bib_id;
1622 IF NOT b_skip_browse THEN
1623 DELETE FROM metabib.browse_entry_def_map WHERE source = bib_id;
1627 FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id ) LOOP
1628 IF ind_data.field < 0 THEN
1629 ind_data.field = -1 * ind_data.field;
1632 IF ind_data.facet_field AND NOT b_skip_facet THEN
1633 INSERT INTO metabib.facet_entry (field, source, value)
1634 VALUES (ind_data.field, ind_data.source, ind_data.value);
1637 IF ind_data.browse_field AND NOT b_skip_browse THEN
1638 -- A caveat about this SELECT: this should take care of replacing
1639 -- old mbe rows when data changes, but not if normalization (by
1640 -- which I mean specifically the output of
1641 -- evergreen.oils_tsearch2()) changes. It may or may not be
1642 -- expensive to add a comparison of index_vector to index_vector
1643 -- to the WHERE clause below.
1644 SELECT INTO mbe_row * FROM metabib.browse_entry WHERE value = ind_data.value;
1646 mbe_id := mbe_row.id;
1648 INSERT INTO metabib.browse_entry (value) VALUES
1649 (metabib.browse_normalize(ind_data.value, ind_data.field));
1650 mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
1653 INSERT INTO metabib.browse_entry_def_map (entry, def, source)
1654 VALUES (mbe_id, ind_data.field, ind_data.source);
1657 IF ind_data.search_field AND NOT b_skip_search THEN
1659 INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value)
1661 quote_literal(ind_data.field) || $$, $$ ||
1662 quote_literal(ind_data.source) || $$, $$ ||
1663 quote_literal(ind_data.value) ||
1669 IF NOT b_skip_search THEN
1670 PERFORM metabib.update_combined_index_vectors(bib_id);
1675 $func$ LANGUAGE PLPGSQL;